private void BtnOk_Click(object sender, EventArgs e) { DataTable _dtnew = new DataTable(); BindingSource _bdSource = new BindingSource(); _bdSource = dataGridView1.DataSource as BindingSource; if (_bdSource != null) { try { _dtEwm = _bdSource.DataSource as DataTable; #region 表身二维码 StringBuilder strSqlBodyEwm = new StringBuilder(); if (_dtEwm != null) { for (int i = 0; i < _dtEwm.Rows.Count; i++) { strSqlBodyEwm.Append("insert into [" + _Body1_Tbl_Name + "] ("); strSqlBodyEwm.Append("Company_Id,Shipping_Id,Itm_Full_Id,Scan_Ewm_Id,Po_Itm,Xm_Id,Item_Desc,Shipping_Address,Qty_Shipping,Available_Qty_Serial_Id,Manufacturer_Id,Serial_Id,AQID,"); strSqlBodyEwm.Append(" PurType_Id,Qty_Po,Planning_Shipping_Date,Already_Shipping_Id,Shipping_Date,Planning_Arrive_Date,Carrier_Id,Lading_Shipping_Id,BarCode_Print_Qty,ItemPrpty_Id_Before,"); strSqlBodyEwm.Append(" ItemSerial_Id_Before,AQID_Before,CsvPo_Id,ItemPrpty_Id,V,APO)"); strSqlBodyEwm.Append("VALUES(@Company_Id,@Shipping_Id,'" + _dtEwm.Rows[i]["Itm_Full_Id"].ToString() + "','" + _dtEwm.Rows[i]["Scan_Ewm_Id"].ToString() + "',"); strSqlBodyEwm.Append(+CommomHelper.ToInt(_dtEwm.Rows[i]["Po_Itm"].ToString()) + ",'" + _dtEwm.Rows[i]["Xm_Id"].ToString() + "','" + _dtEwm.Rows[i]["Item_Desc"].ToString() + "',"); strSqlBodyEwm.Append("'" + _dtEwm.Rows[i]["Shipping_Address"].ToString() + "'," + CommomHelper.ToDecimal(_dtEwm.Rows[i]["Qty_Shipping"].ToString()) + ",'" + _dtEwm.Rows[i]["Available_Qty_Serial_Id"].ToString() + "',"); strSqlBodyEwm.Append("'" + _dtEwm.Rows[i]["Manufacturer_Id"].ToString() + "','" + _dtEwm.Rows[i]["Serial_Id"].ToString() + "','" + _dtEwm.Rows[i]["AQID"].ToString() + "',"); strSqlBodyEwm.Append("'" + _dtEwm.Rows[i]["PurType_Id"].ToString() + "'," + CommomHelper.ToDecimal(_dtEwm.Rows[i]["Qty_Po"].ToString()) + ",'" + _dtEwm.Rows[i]["Planning_Shipping_Date"].ToString() + "',"); strSqlBodyEwm.Append("'" + _dtEwm.Rows[i]["Already_Shipping_Id"].ToString() + "','" + _dtEwm.Rows[i]["Shipping_Date"].ToString() + "','" + _dtEwm.Rows[i]["Planning_Arrive_Date"].ToString() + "',"); strSqlBodyEwm.Append("'" + _dtEwm.Rows[i]["Carrier_Id"].ToString() + "','" + _dtEwm.Rows[i]["Lading_Shipping_Id"].ToString() + "'," + CommomHelper.ToDecimal(_dtEwm.Rows[i]["BarCode_Print_Qty"].ToString()) + ","); strSqlBodyEwm.Append("'" + _dtEwm.Rows[i]["ItemPrpty_Id_Before"].ToString() + "','" + _dtEwm.Rows[i]["ItemSerial_Id_Before"].ToString() + "','" + _dtEwm.Rows[i]["AQID_Before"].ToString() + "','" + _dtEwm.Rows[i]["CsvPo_Id"].ToString() + "',"); strSqlBodyEwm.Append("'" + _dtEwm.Rows[i]["ItemPrpty_Id"].ToString() + "','" + _dtEwm.Rows[i]["V"].ToString() + "','" + _dtEwm.Rows[i]["APO"].ToString() + "')"); } } SqlParameter[] parameters = { new SqlParameter("@Company_Id", SqlDbType.NVarChar, 50), new SqlParameter("@Shipping_Id", SqlDbType.NVarChar, 50) }; parameters[0].Value = _Company_Id; parameters[1].Value = _Shipping_Id; #endregion bool IsTrue = SqlHelper.ExecuteQueryTrans(strSqlBodyEwm.ToString(), parameters); if (IsTrue) { this.DialogResult = DialogResult.Yes; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } else { MessageBox.Show("表身二维码未生成!"); } }
private bool UpdShippingInfoConfirmOrder() { #region 表头 StringBuilder strSql = new StringBuilder(); strSql.Append(" Update ShippingInfoConfirmOrder "); strSql.Append("set Company_Id='" + _Company_Id + "',"); foreach (Control _ct in tableLayoutPanel1.Controls) { if (!(_ct is Label) && !(_ct is Button)) { if (_ct is TextBoxContainButton) { TextBoxContainButton _txtBtn = (TextBoxContainButton)_ct; string _id = string.IsNullOrEmpty(_txtBtn.ID) ? "" : _txtBtn.ID; strSql.Append(_ct.Name + "='" + _id + "'"); } else { if (Convert.ToString(_ct.Tag) == "Datatime") { strSql.Append(_ct.Name + "='" + Convert.ToDateTime(_ct.Text) + "'"); } else if (Convert.ToString(_ct.Tag) == "Decimal") { strSql.Append(_ct.Name + "='" + CommomHelper.ToDecimal(_ct.Text) + "'"); } else { strSql.Append(_ct.Name + "='" + _ct.Text + "'"); } } strSql.Append(","); } } strSql.Append(" Pgm_Tag_Id='SC' "); strSql.Append(" where Shipping_Id='" + this.Shipping_Id.Text + "' and Company_Id='" + _Company_Id + "' "); #endregion #region 表身 BindingSource _bdSource = new BindingSource(); _bdSource = dataGridView1.DataSource as BindingSource; DataTable _dtBody = _bdSource.DataSource as DataTable; StringBuilder strSqlBody = new StringBuilder(); if (_dtBody != null) { for (int i = 0; i < _dtBody.Rows.Count; i++) { if (string.IsNullOrEmpty(_dtBody.Rows[i]["Itm_Full_Id"].ToString())) { #region 表身新增列 string _ColumnValue = ""; strSqlBody.Append("If ((select 1 from ShippingInfoConfirmBody where Shipping_Id='" + this.Shipping_Id.Text + "' and Company_Id='" + _Company_Id + "') is null) "); strSqlBody.Append("insert into ShippingInfoConfirmBody(Company_Id,"); for (int j = 0; j < _dtBody.Columns.Count; j++) { string _ColumnName = _dtBody.Columns[j].ColumnName; if (_ColumnName == "Company_Id" || _ColumnName == "Ewm_Id" || _ColumnName == "Itm_Full_Id" || _ColumnName == "Shipping_Id" || _ColumnName == "Company__Id" || _ColumnName == "Itm_Full_Id" || _ColumnName == "Role__Id" || _ColumnName == "Usr__Id" || _ColumnName == "Create__Date" || _ColumnName == "Pgm_Id" || _ColumnName == "Last_Modify_Company_Id" || _ColumnName == "Last_Modify_Role_Id" || _ColumnName == "Last_Modify_Usr_Id" || _ColumnName == "Last_Modify_Date") { continue; } strSqlBody.Append(_dtBody.Columns[j].ColumnName); strSqlBody.Append(","); } strSqlBody.Append("Shipping_Id,Itm_Full_Id,Company__Id,Role__Id,Usr__Id,Create__Date)"); strSqlBody.Append("VALUES("); strSqlBody.Append("'" + LoginInfo._Usr_Company + "',"); for (int j = 0; j < _dtBody.Columns.Count; j++) { string _ColumnName = _dtBody.Columns[j].ColumnName; if (_ColumnName == "Company_Id" || _ColumnName == "Ewm_Id" || _ColumnName == "Itm_Full_Id" || _ColumnName == "Shipping_Id" || _ColumnName == "Company__Id" || _ColumnName == "Itm_Full_Id" || _ColumnName == "Role__Id" || _ColumnName == "Usr__Id" || _ColumnName == "Create__Date" || _ColumnName == "Pgm_Id" || _ColumnName == "Last_Modify_Company_Id" || _ColumnName == "Last_Modify_Role_Id" || _ColumnName == "Last_Modify_Usr_Id" || _ColumnName == "Last_Modify_Date") { continue; } _ColumnValue = _dtBody.Rows[i][j].ToString(); if (_dtBody.Columns[j].DataType == (new DateTime()).GetType()) { if (string.IsNullOrEmpty(_ColumnValue)) { _ColumnValue = System.DateTime.Now.ToString(); } strSqlBody.Append("'"); strSqlBody.Append(Convert.ToDateTime(_ColumnValue)); strSqlBody.Append("',"); } else if (_dtBody.Columns[j].DataType == (new Decimal()).GetType() || (_dtBody.Columns[j].DataType == (new int()).GetType())) { strSqlBody.Append(CommomHelper.ToDecimal(_ColumnValue)); strSqlBody.Append(","); } else { strSqlBody.Append("'"); strSqlBody.Append(_ColumnValue); strSqlBody.Append("',"); } } strSqlBody.Append("'" + this.Shipping_Id.Text + "',Replace(Cast(NewId() As nvarchar(50)),'-','')," + "'" + LoginInfo._Usr_Company + "','" + LoginInfo._Usr_Role + "','" + LoginInfo._Usr_id + "', '" + System.DateTime.Now.ToString() + "')"); #endregion } else { #region 修改表身列 string _ColumnValue = ""; strSqlBody.Append("Update ShippingInfoConfirmBody set Company_Id='" + LoginInfo._Usr_Company + "',"); for (int j = 0; j < _dtBody.Columns.Count; j++) { string _ColumnName = _dtBody.Columns[j].ColumnName; if (_ColumnName == "Company_Id" || _ColumnName == "Ewm_Id" || _ColumnName == "Itm_Full_Id" || _ColumnName == "Shipping_Id" || _ColumnName == "Company__Id" || _ColumnName == "Itm_Full_Id" || _ColumnName == "Role__Id" || _ColumnName == "Usr__Id" || _ColumnName == "Create__Date" || _ColumnName == "Pgm_Id" || _ColumnName == "Last_Modify_Company_Id" || _ColumnName == "Last_Modify_Role_Id" || _ColumnName == "Last_Modify_Usr_Id" || _ColumnName == "Last_Modify_Date") { continue; } _ColumnValue = _dtBody.Rows[i][j].ToString(); if (_dtBody.Columns[j].DataType == (new DateTime()).GetType()) { strSqlBody.Append(_dtBody.Columns[j].ColumnName + "='" + Convert.ToDateTime(_ColumnValue) + "'"); } else if (_dtBody.Columns[j].DataType == (new Decimal()).GetType() || (_dtBody.Columns[j].DataType == (new int()).GetType())) { strSqlBody.Append(_dtBody.Columns[j].ColumnName + "=" + CommomHelper.ToDecimal(_ColumnValue) + ""); } else { strSqlBody.Append(_dtBody.Columns[j].ColumnName + "='" + _ColumnValue + "'"); } strSqlBody.Append(","); } strSqlBody.Append(" Last_Modify_Company_Id='" + LoginInfo._Usr_Company + "',Last_Modify_Role_Id='" + LoginInfo._Usr_Role + "',Last_Modify_Usr_Id='" + LoginInfo._Usr_id + "',Last_Modify_Date='" + System.DateTime.Now.ToString() + "'"); strSqlBody.Append(" where Shipping_Id='" + this.Shipping_Id.Text + "' and Company_Id='" + LoginInfo._Usr_Company + "' "); strSqlBody.Append(" and Itm_Full_Id='" + _dtBody.Rows[i]["Itm_Full_Id"].ToString() + "' "); #endregion } } } #endregion #region Body1 StringBuilder _strSqlBody1 = new StringBuilder(); _strSqlBody1.Append(" Update TempBody1 Set Company_Id='" + LoginInfo._Usr_Company + "', Shipping_Id='" + this.Shipping_Id.Text + "' "); _strSqlBody1.Append(" From [" + Body1_Tbl_Name + "] TempBody1 "); _strSqlBody1.Append(" Delete From ShippingInfoConfirmBody1 "); _strSqlBody1.Append(" Where ShippingInfoConfirmBody1.Company_Id='" + LoginInfo._Usr_Company + "' "); _strSqlBody1.Append(" And ShippingInfoConfirmBody1.Shipping_Id='" + this.Shipping_Id.Text + "' "); _strSqlBody1.Append(" Insert Into ShippingInfoConfirmBody1 "); _strSqlBody1.Append(" Select * From [" + Body1_Tbl_Name + "] "); #endregion bool IsTrue = SqlHelper.ExecuteQueryTrans(strSql + strSqlBody.ToString() + _strSqlBody1.ToString()); return(IsTrue); }
private void btnInput_Click(object sender, EventArgs e) { decimal _qty = CommomHelper.ToDecimal(this.txtQty.Text); if (string.IsNullOrEmpty(this.txtQty.Text)) { MessageBox.Show("数量必须输入!"); } else { DataTable _dtnew = new DataTable(); BindingSource _bdSource = new BindingSource(); _bdSource = dataGridView1.DataSource as BindingSource; if (_bdSource != null) { DataTable _dtBody = _bdSource.DataSource as DataTable; if (_dtBody.Rows.Count > 0) { _dtnew = _dtBody.Copy(); } else { _dtnew = _dtBody.Clone(); } for (int i = 0; i < _qty; i++) { if (_dtnew == null) { return; } DataRow _dr = _dtnew.NewRow(); _dr["Company_Id"] = _Company_Id; _dr["Shipping_Id"] = _Shipping_Id; _dr["CsvPo_Id"] = _CsvPo_Id; _dr["ItemPrpty_Id"] = _ItemPrpty_Id; _dr["Itm_Full_Id"] = _itm_Full_Id; if (Edit == "ADD") { _dr["Itm_Full_Id"] = System.Guid.NewGuid().ToString("N"); } _dr["Scan_Ewm_Id"] = System.Guid.NewGuid().ToString("N"); _dr["ItemUnit_Id"] = _itemUt; _dr["Qty"] = 1; int _itm = 1; if (_dtnew.Rows.Count > 0) { _itm = _dtnew.AsEnumerable().Select(t => t.Field <int>("Po_itm")).Max(); _itm += 1; } _dr["Itm"] = _itm; _dr["Po_Itm"] = _itm; _dr["Qty_Shipping"] = 1; _dr["V"] = "V" + _itm; _dr["Planning_Shipping_Date"] = System.DateTime.Now; _dr["Shipping_Date"] = System.DateTime.Now; _dr["Planning_Arrive_Date"] = System.DateTime.Now; if (_dtnew.Select("Itm_Full_Id='" + _dr["Itm_Full_Id"].ToString() + "'").Length == 0) { _dtnew.Rows.Add(_dr); } else { MessageBox.Show("存在相同项!");// _dr["Itm_Full_Id"].ToString() return; } } } BindingSource bindingSource1 = new BindingSource(); bindingSource1.DataSource = _dtnew; this.dataGridView1.DataSource = bindingSource1; } }
private bool AddShippingInfoConfirmOrder() { #region 表头 int _Serial_Itm = 0; StringBuilder strGetIdSql = new StringBuilder(); strGetIdSql.Append(" Declare @Serial_Itm int, @Bil_Id nvarchar(60) "); strGetIdSql.Append(" Exec dbo.usp_SetBilId1 "); strGetIdSql.Append(" @Company_Id='" + LoginInfo._Usr_Company + "', "); strGetIdSql.Append(" @Pgm_Tag_Id='SC', "); strGetIdSql.Append(" @Bill_Date='" + System.DateTime.Now.ToShortDateString() + "', "); strGetIdSql.Append(" @Serial_Itm=@Serial_Itm Output, "); strGetIdSql.Append(" @Bil_Id=@Bil_Id OutPut "); strGetIdSql.Append(" Select Serial_Itm=@Serial_Itm, Bil_Id=@Bil_Id "); DataTable _dtRusult = SqlHelper.ExecuteDataTable(strGetIdSql.ToString()); if (_dtRusult != null && _dtRusult.Rows.Count > 0 && _dtRusult.Columns.Contains("Serial_Itm")) { _Serial_Itm = CommomHelper.ToInt(_dtRusult.Rows[0]["Bil_Id"].ToString()); } StringBuilder strSql = new StringBuilder(); strSql.Append("insert into ShippingInfoConfirmOrder("); strSql.Append("Company_Id,Zan_Save_Id,"); foreach (Control _ct in tableLayoutPanel1.Controls) { if (!(_ct is Label) && !(_ct is Button)) { strSql.Append(_ct.Name); strSql.Append(","); } } strSql.Append("Pgm_Tag_Id,Company__Id,Role__Id,Create__Date,Pgm_Id,Serial_Itm)"); strSql.Append("VALUES("); strSql.Append("'" + LoginInfo._Usr_Company + "',"); strSql.Append("'T',"); foreach (Control _ct in tableLayoutPanel1.Controls) { if (!(_ct is Label) && !(_ct is Button)) { if (_ct is TextBoxContainButton) { TextBoxContainButton _txtBtn = (TextBoxContainButton)_ct; string _id = string.IsNullOrEmpty(_txtBtn.ID) ? "" : _txtBtn.ID; strSql.Append("'"); strSql.Append(_id); strSql.Append("'"); } else { if (Convert.ToString(_ct.Tag) == "Datatime") { strSql.Append("'"); strSql.Append(Convert.ToDateTime(_ct.Text)); strSql.Append("'"); } else if (Convert.ToString(_ct.Tag) == "Decimal") { strSql.Append(CommomHelper.ToDecimal(_ct.Text)); } else { strSql.Append("'"); strSql.Append(_ct.Text); strSql.Append("'"); } } strSql.Append(","); } } strSql.Append(" 'SC',"); strSql.Append("'" + LoginInfo._Usr_Company + "','" + LoginInfo._Usr_Role + "', '" + System.DateTime.Now.ToString() + "'"); strSql.Append(",'ShippingInfoConfirmOrder'," + _Serial_Itm + ")"); #endregion #region 表身 BindingSource _bdSource = new BindingSource(); _bdSource = dataGridView1.DataSource as BindingSource; DataTable _dtBody = _bdSource.DataSource as DataTable; StringBuilder strSqlBody = new StringBuilder(); if (_dtBody != null) { for (int i = 0; i < _dtBody.Rows.Count; i++) { string _ColumnValue = ""; strSqlBody.Append("insert into ShippingInfoConfirmBody(Company_Id,"); for (int j = 0; j < _dtBody.Columns.Count; j++) { string _ColumnName = _dtBody.Columns[j].ColumnName; if (_ColumnName == "Company_Id" || _ColumnName == "Ewm_Id" || _ColumnName == "Itm_Full_Id" || _ColumnName == "Shipping_Id" || _ColumnName == "Company__Id" || _ColumnName == "Itm_Full_Id" || _ColumnName == "Role__Id" || _ColumnName == "Usr__Id" || _ColumnName == "Create__Date" || _ColumnName == "Pgm_Id" || _ColumnName == "Last_Modify_Company_Id" || _ColumnName == "Last_Modify_Role_Id" || _ColumnName == "Last_Modify_Usr_Id" || _ColumnName == "Last_Modify_Date") { continue; } strSqlBody.Append(_dtBody.Columns[j].ColumnName); strSqlBody.Append(","); } strSqlBody.Append("Shipping_Id,Itm_Full_Id,Company__Id,Role__Id,Usr__Id,Create__Date,Pgm_Id)"); strSqlBody.Append("VALUES("); strSqlBody.Append("'" + LoginInfo._Usr_Company + "',"); for (int j = 0; j < _dtBody.Columns.Count; j++) { string _ColumnName = _dtBody.Columns[j].ColumnName; if (_ColumnName == "Company_Id" || _ColumnName == "Ewm_Id" || _ColumnName == "Itm_Full_Id" || _ColumnName == "Shipping_Id" || _ColumnName == "Company__Id" || _ColumnName == "Itm_Full_Id" || _ColumnName == "Role__Id" || _ColumnName == "Usr__Id" || _ColumnName == "Create__Date" || _ColumnName == "Pgm_Id" || _ColumnName == "Last_Modify_Company_Id" || _ColumnName == "Last_Modify_Role_Id" || _ColumnName == "Last_Modify_Usr_Id" || _ColumnName == "Last_Modify_Date") { continue; } _ColumnValue = _dtBody.Rows[i][j].ToString(); if (_dtBody.Columns[j].DataType == (new DateTime()).GetType()) { if (string.IsNullOrEmpty(_ColumnValue)) { _ColumnValue = System.DateTime.Now.ToString(); } strSqlBody.Append("'"); strSqlBody.Append(Convert.ToDateTime(_ColumnValue)); strSqlBody.Append("',"); } else if (_dtBody.Columns[j].DataType == (new Decimal()).GetType() || (_dtBody.Columns[j].DataType == (new int()).GetType())) { strSqlBody.Append(CommomHelper.ToDecimal(_ColumnValue)); strSqlBody.Append(","); } else { strSqlBody.Append("'"); strSqlBody.Append(_ColumnValue); strSqlBody.Append("',"); } } strSqlBody.Append("'" + this.Shipping_Id.Text + "', Replace(Cast(NewId() As nvarchar(50)),'-','')," + "'" + LoginInfo._Usr_Company + "','" + LoginInfo._Usr_Role + "','" + LoginInfo._Usr_id + "', '" + System.DateTime.Now.ToString() + "','ShippingInfoConfirmOrder')"); } } #endregion StringBuilder _strSqlBody1 = new StringBuilder(); //新增Body1 Body1_Tbl_Name #region Body1 _strSqlBody1.Append(" Update TempBody1 Set Company_Id='" + LoginInfo._Usr_Company + "', Shipping_Id='" + this.Shipping_Id.Text + "' "); _strSqlBody1.Append(" From [" + Body1_Tbl_Name + "] TempBody1 "); _strSqlBody1.Append(" Delete From ShippingInfoConfirmBody1 "); _strSqlBody1.Append(" Where ShippingInfoConfirmBody1.Company_Id='" + LoginInfo._Usr_Company + "' "); _strSqlBody1.Append(" And ShippingInfoConfirmBody1.Shipping_Id='" + this.Shipping_Id.Text + "' "); _strSqlBody1.Append(" Insert Into ShippingInfoConfirmBody1 "); _strSqlBody1.Append(" Select * From [" + Body1_Tbl_Name + "] "); #endregion bool IsTrue = SqlHelper.ExecuteQueryTrans(strSql + strSqlBody.ToString() + _strSqlBody1.ToString()); return(IsTrue); }