/// <summary> /// 增加一条数据 /// </summary> public string Add(UFIDA.U8.UAP.CustomApp.ControlForm.Model.PurBillVouchs_Import model) { StringBuilder strSql = new StringBuilder(); StringBuilder strSql1 = new StringBuilder(); StringBuilder strSql2 = new StringBuilder(); if (model.ImportTime != null) { strSql1.Append("ImportTime,"); strSql2.Append("'" + model.ImportTime + "',"); } if (model.InvoiceNo != null) { strSql1.Append("InvoiceNo,"); strSql2.Append("'" + model.InvoiceNo + "',"); } if (model.Date != null) { strSql1.Append("Date,"); strSql2.Append("'" + model.Date + "',"); } if (model.Companyname != null) { strSql1.Append("Companyname,"); strSql2.Append("'" + model.Companyname + "',"); } if (model.PONO != null) { strSql1.Append("PONO,"); strSql2.Append("'" + model.PONO + "',"); } if (model.PARTNO != null) { strSql1.Append("PARTNO,"); strSql2.Append("'" + model.PARTNO + "',"); } if (model.DESCRIPTIONENGLISH != null) { strSql1.Append("DESCRIPTIONENGLISH,"); strSql2.Append("'" + model.DESCRIPTIONENGLISH + "',"); } if (model.QUANTITY != null) { strSql1.Append("QUANTITY,"); strSql2.Append("" + model.QUANTITY + ","); } if (model.NW != null) { strSql1.Append("NW,"); strSql2.Append("" + model.NW + ","); } if (model.GW != null) { strSql1.Append("GW,"); strSql2.Append("" + model.GW + ","); } if (model.PRICEPERUNIT != null) { strSql1.Append("PRICEPERUNIT,"); strSql2.Append("" + model.PRICEPERUNIT + ","); } if (model.UNIT != null) { strSql1.Append("UNIT,"); strSql2.Append("'" + model.UNIT + "',"); } if (model.TOTALPRICE != null) { strSql1.Append("TOTALPRICE,"); strSql2.Append("" + model.TOTALPRICE + ","); } strSql.Append("insert into PurBillVouchs_Import("); strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1)); strSql.Append(")"); strSql.Append(" values ("); strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1)); strSql.Append(")"); strSql.Append(";select @@IDENTITY"); return(strSql.ToString()); }
private void btnImprot_Click(object sender, EventArgs e) { try { gridViewIN.FocusedRowHandle -= 1; gridViewIN.FocusedRowHandle += 1; gridViewPL.FocusedRowHandle -= 1; gridViewPL.FocusedRowHandle += 1; } catch { } try { if (dateEdit1.DateTime < BaseFunction.ReturnDate("2017-01-01")) { throw new Exception("Date is err"); } decimal dPLSum = BaseFunction.ReturnDecimal(gridViewPL.Columns["QUANTITY"].SummaryItem.SummaryValue); decimal dINSum = BaseFunction.ReturnDecimal(gridViewIN.Columns["QUANTITY"].SummaryItem.SummaryValue); if (dPLSum != dINSum) { throw new Exception("Please check the quantity"); } if (dtmBL.Text.Trim() == "") { dtmBL.Focus(); throw new Exception("Please set b/l date"); } //if (dtmBL.DateTime < BaseFunction.ReturnDate(sLogDate).AddMonths(-1)) //{ // dtmBL.Focus(); // throw new Exception("Please set b/l date"); //} if (txtBL.Text.Trim() == "") { txtBL.Focus(); throw new Exception("Please set b/l no"); } if (gridViewIN.RowCount == 0 || gridViewPL.RowCount == 0) { throw new Exception("No data"); } string sErr = ""; string sWarn = ""; int iCount = 0; SqlConnection conn = new SqlConnection(Conn); conn.Open(); //启用事务 SqlTransaction tran = conn.BeginTransaction(); try { string sSQL = "select getdate()"; DateTime dNow = BaseFunction.ReturnDate(DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0].Rows[0][0]); DateTime dNowDate = BaseFunction.ReturnDate(dNow.ToString("yyyy-MM-dd")); string sDateTxt = dNow.ToString("yyyyMMddHHmmss"); CheckData(tran); sSQL = @" select a.cPOID,b.*,inv.cInvAddCode from PO_Pomain a inner join PO_Podetails b on a.poid = b.poid inner join Inventory inv on inv.cInvCode = b.cInvCode where a.cPOID in (select PONO from RdRecords01_temp) "; DataTable dtPO = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; for (int i = 0; i < gridViewPL.RowCount; i++) { Model.RdRecords01_Import modRdsImport = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.RdRecords01_Import(); modRdsImport.ImportTime = sDateTxt; modRdsImport.InvoiceNo = txtInvoiceNO.Text.Trim(); modRdsImport.Date = dateEdit1.DateTime; modRdsImport.Companyname = txtCompany.Text.Trim(); modRdsImport.PONO = gridViewPL.GetRowCellValue(i, gridColPONO).ToString().Trim(); modRdsImport.CONTAINERNO = gridViewPL.GetRowCellValue(i, gridColCONTAINERNO).ToString().Trim(); modRdsImport.DESCRIPTIONOFPRODUCTSEN = gridViewPL.GetRowCellValue(i, gridColDESCRIPTIONOFPRODUCTSEN).ToString().Trim(); modRdsImport.CASENO = gridViewPL.GetRowCellValue(i, gridColCASENO).ToString().Trim(); modRdsImport.BOXNO = gridViewPL.GetRowCellValue(i, gridColBOXNO).ToString().Trim(); modRdsImport.BOXQTY = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColBOXQTY), 6); modRdsImport.QUANTITY = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColQUANTITY), 6); modRdsImport.UNIT = gridViewPL.GetRowCellValue(i, gridColUNIT).ToString().Trim(); modRdsImport.NWKGS = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColNWKGS), 6); modRdsImport.GWKGS = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColGWKGS), 6); modRdsImport.BOXCBM = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColBOXCBM), 6); modRdsImport.CONNO = gridViewPL.GetRowCellValue(i, gridColCONNO).ToString().Trim(); modRdsImport.BLDate = dtmBL.DateTime; modRdsImport.BLNo = txtBL.Text.Trim(); modRdsImport.Currency = txtCurrency.Text.Trim(); DataRow[] dr = dtPO.Select("cPOID = '" + modRdsImport.PONO + "' and (cInvAddCode = '" + gridViewPL.GetRowCellValue(i, gridColPARTNO).ToString().Trim() + "' or cInvCode = '" + gridViewPL.GetRowCellValue(i, gridColPARTNO).ToString().Trim() + "')"); if (dr.Length <= 0) { sErr = sErr + "Line " + (i + 1).ToString() + " PO No does not match\n"; continue; } modRdsImport.PARTNO = dr[0]["cInvCode"].ToString().Trim(); modRdsImport.POsID = BaseFunction.ReturnInt(dr[0]["ID"]); DAL.RdRecords01_Import dalRdImport = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.RdRecords01_Import(); sSQL = dalRdImport.Add(modRdsImport); iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } for (int i = 0; i < gridViewIN.RowCount; i++) { Model.PurBillVouchs_Import modINImport = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.PurBillVouchs_Import(); modINImport.ImportTime = sDateTxt; modINImport.InvoiceNo = txtInvoiceNO.Text.Trim(); modINImport.Date = dateEdit1.DateTime; modINImport.Companyname = txtCompany.Text.Trim(); modINImport.PONO = gridViewIN.GetRowCellValue(i, gridCol_PONO).ToString().Trim(); sSQL = @" select inv.cInvCode,inv.cInvAddCode from Inventory inv inner join ( select distinct b.cInvCode from PO_Pomain a inner join po_podetails b on a.POID = b.POID where a.cPOID = '{1}' ) po on inv.cInvCode = po.cInvCode where inv.cInvAddCode = '{0}' or inv.cInvCode = '{0}' "; sSQL = string.Format(sSQL, gridViewIN.GetRowCellValue(i, gridCol_PARTNO).ToString().Trim(), gridViewIN.GetRowCellValue(i, gridCol_PONO).ToString().Trim()); DataTable dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtInv == null || dtInv.Rows.Count == 0) { sErr = sErr + "Line " + (i + 1).ToString() + " Part No does not match\n"; continue; } modINImport.PARTNO = dtInv.Rows[0]["cInvCode"].ToString().Trim(); modINImport.DESCRIPTIONENGLISH = gridViewIN.GetRowCellValue(i, gridCol_DESCRIPTIONENGLISH).ToString().Trim(); modINImport.QUANTITY = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_QUANTITY), 6); modINImport.NW = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_NW), 6); modINImport.GW = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_GW), 6); modINImport.PRICEPERUNIT = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_PRICEPERUNIT), 6); modINImport.UNIT = gridViewIN.GetRowCellValue(i, gridCol_UNIT).ToString().Trim(); modINImport.TOTALPRICE = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_TOTALPRICE), 6); DAL.PurBillVouchs_Import dalInImprot = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.PurBillVouchs_Import(); sSQL = dalInImprot.Add(modINImport); iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } //按照发票刷新装箱单单价 sSQL = @" update a set a.UnitPrice = b.priceperunit from RdRecords01_Import a inner join PurBillVouchs_Import b on a.ImportTime = b.ImportTime and a.PONO = b.PONO and a.PARTNO = b.PARTNO "; DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); if (sWarn.Length > 0) { FrmMsgBoxYesOrNo frm = new FrmMsgBoxYesOrNo(); frm.richTextBox1.Text = sWarn; if (DialogResult.Yes != frm.ShowDialog()) { throw new Exception("User cancelled"); } } if (sErr.Length > 0) { throw new Exception(sErr); } if (iCount > 0) { tran.Commit(); MessageBox.Show("Success"); SetTxtNull(); } else { throw new Exception("Save failed"); } } catch (Exception error) { tran.Rollback(); throw new Exception(error.Message); } } catch (Exception ee) { FrmMsgBox frm = new FrmMsgBox(); frm.richTextBox1.Text = ee.Message; frm.ShowDialog(); } }