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 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); }