/// <summary> /// 增加一条数据 /// </summary> public string Add(UFIDA.U8.UAP.CustomApp.ControlForm.Model.RdRecords01_Temp model) { StringBuilder strSql = new StringBuilder(); StringBuilder strSql1 = new StringBuilder(); StringBuilder strSql2 = new StringBuilder(); if (model.PONO != null) { strSql1.Append("PONO,"); strSql2.Append("'" + model.PONO + "',"); } if (model.cInvCode != null) { strSql1.Append("cInvCode,"); strSql2.Append("'" + model.cInvCode + "',"); } if (model.iQuantity != null) { strSql1.Append("iQuantity,"); strSql2.Append("" + model.iQuantity + ","); } strSql.Append("insert into RdRecords01_Temp("); strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1)); strSql.Append(")"); strSql.Append(" values ("); strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1)); strSql.Append(")"); return(strSql.ToString()); }
private void CheckData(SqlTransaction tran) { try { string sErr = ""; string sDate = dateEdit1.DateTime.ToString("yyyy-MM-dd"); string sInvoiceNo = txtInvoiceNO.Text.Trim(); string sSQL = @" SELECT InvoiceNo,[Date] FROM [dbo].[PurBillVouchs_Import] where InvoiceNo = 'aaaaaa' union SELECT InvoiceNo,[Date] FROM [dbo].[Rdrecords01_Import] where InvoiceNo = 'aaaaaa' "; sSQL = sSQL.Replace("aaaaaa", sInvoiceNo); DataTable dtExists = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtExists != null && dtExists.Rows.Count > 0) { if (MessageBox.Show("The invoice number has been imported and whether to continue?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) != DialogResult.Yes) { throw new Exception("User cancelled"); } } sSQL = "select * from foreigncurrency where cexch_code = 'aaaaaaaa' or cexch_name = 'aaaaaaaa'"; sSQL = sSQL.Replace("aaaaaaaa", txtCurrency.Text.Trim()); DataTable dtCurrency = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtCurrency == null || dtCurrency.Rows.Count == 0) { throw new Exception("Currency does not exist"); } 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"); sSQL = "truncate table RdRecords01_Temp"; DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); for (int i = 0; i < gridViewPL.RowCount; i++) { Model.RdRecords01_Temp modRdsTemp = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.RdRecords01_Temp(); modRdsTemp.cInvCode = gridViewPL.GetRowCellValue(i, gridColPARTNO).ToString().Trim(); modRdsTemp.PONO = gridViewPL.GetRowCellValue(i, gridColPONO).ToString().Trim(); modRdsTemp.iQuantity = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColQUANTITY), 6); DAL.RdRecords01_Temp dalRdsTemp = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.RdRecords01_Temp(); sSQL = dalRdsTemp.Add(modRdsTemp); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } sSQL = "truncate table PurBillVouchs_Temp"; DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); for (int i = 0; i < gridViewIN.RowCount; i++) { Model.PurBillVouchs_Temp modINTemp = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.PurBillVouchs_Temp(); modINTemp.cInvCode = gridViewIN.GetRowCellValue(i, gridCol_PARTNO).ToString().Trim(); modINTemp.PONO = gridViewIN.GetRowCellValue(i, gridCol_PONO).ToString().Trim(); modINTemp.iQuantity = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_QUANTITY), 6); DAL.PurBillVouchs_Temp dalINTemp = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.PurBillVouchs_Temp(); sSQL = dalINTemp.Add(modINTemp); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } for (int i = 0; i < gridViewPL.RowCount; i++) { string sInvAbbCode = gridViewPL.GetRowCellValue(i, gridColPARTNO).ToString().Trim(); if (sInvAbbCode.Trim() == "") { continue; } string sPONO = gridViewPL.GetRowCellDisplayText(i, gridColPONO).ToString().Trim(); //判断订单是否存在 sSQL = @" select cPOID from PO_Pomain where cPOID = 'aaaaaa' "; sSQL = sSQL.Replace("aaaaaa", sPONO); DataTable dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtCou == null || dtCou.Rows.Count == 0) { sErr = sErr + "PL: line " + (i + 1).ToString() + " PO No does not match\n"; continue; } sSQL = @" select * from Inventory where cInvCode = '{0}' or cInvAddCode = '{0}' "; sSQL = string.Format(sSQL, sInvAbbCode); DataTable dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtInv == null || dtInv.Rows.Count == 0) { sErr = sErr + "PL: Line " + (i + 1) + " cInvAddCode is not exists\n"; continue; } //判断订单是否存在物料 sSQL = @" select a.cPOID ,b.cInvCode from PO_Pomain a inner join PO_PODetails b on a.poid = b.poid inner join Inventory inv on b.cInvCode = inv.cInvCode where a.cPOID = 'aaaaaa' and (b.cInvCode = 'bbbbbb' or inv.cInvAddCode = 'bbbbbb') "; sSQL = sSQL.Replace("aaaaaa", gridViewPL.GetRowCellDisplayText(i, gridColPONO).ToString().Trim()); sSQL = sSQL.Replace("bbbbbb", sInvAbbCode); dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtCou == null || dtCou.Rows.Count == 0) { sErr = sErr + "PL: line " + (i + 1).ToString() + " PO No does not exist in this material\n"; continue; } sSQL = @" select inv.cInvCode,isnull(inv.bPurchase,0) as bPurchase 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, sInvAbbCode, gridViewPL.GetRowCellValue(i, gridColPONO).ToString().Trim()); dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtInv == null || dtInv.Rows.Count == 0) { sErr = sErr + "PL: Line " + (i + 1) + " cInvAddCode is not exists\n"; continue; } if (dtInv.Rows.Count > 1) { sSQL = @" select cInvCode,isnull(bPurchase,0) as bPurchase from Inventory where cInvAddCode = 'AAAAAAAA' "; sSQL = sSQL.Replace("AAAAAAAA", sInvAbbCode); dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtInv == null || dtInv.Rows.Count != 1) { sErr = sErr + "PL: Line " + (i + 1) + " cInvAddCode is non-unique and code is not exists,Excel line(PL) " + (i + 14) + "\n"; continue; } } string sInvCode = dtInv.Rows[0]["cInvCode"].ToString().Trim(); if (!Convert.ToBoolean(dtInv.Rows[0]["bPurchase"])) { sErr = sErr + "PL: line " + (i + 1).ToString() + " inventory has no purchase attributes\n"; continue; } //判断是否生成了采购入库单 string sRdsID = gridViewPL.GetRowCellValue(i, gridColRds01ID).ToString().Trim(); if (sRdsID != "") { sSQL = @" select Autoid from Rdrecords01 where autoid = aaaaaa "; sSQL = sSQL.Replace("aaaaaa", sRdsID); DataTable dtRds = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtRds != null && dtRds.Rows.Count > 0) { sErr = sErr + "PL: Line " + (i + 1) + " purchase receipt warrant is exists\n"; continue; } } // PONO ,containerNO ,PARTNO 相同的只允许导入一次 sSQL = @" select * from Rdrecords01_Import where PONO = 'aaaaaa' AND CONTAINERNO = 'bbbbbb' AND PARTNO = 'cccccc' "; sSQL = sSQL.Replace("aaaaaa", gridViewPL.GetRowCellDisplayText(i, gridColPONO).ToString().Trim()); sSQL = sSQL.Replace("bbbbbb", gridViewPL.GetRowCellDisplayText(i, gridColCONTAINERNO).ToString().Trim()); sSQL = sSQL.Replace("cccccc", sInvCode); dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtCou != null && dtCou.Rows.Count > 0) { sErr = sErr + "PL: line " + (i + 1).ToString() + " PONO CONTAINERNO PARTNO have been imported\n"; continue; } //判断入库单与发票是否存在不一致数据 sSQL = @" if object_id('tempdb..#_Temp') is not null drop table #_Temp select a.cInvCode,a.PONO,sum(a.iQuantity) as iINQty,cast(null as decimal(16,6)) as iRDQty into #_Temp from PurBillVouchs_Temp a where a.cInvCode = 'aaaaaa' group by a.cInvCode,a.PONO insert into #_Temp select a.cInvCode,a.PONO,cast(null as decimal(16,6)) as iINQty,sum(a.iQuantity) as iRDQty from Rdrecords01_Temp a where a.cInvCode = 'aaaaaa' group by a.cInvCode,a.PONO select count(1) as iCou,cInvCode,PONO,sum(iINQty) as iINQty, sum(iRDQty) as iRDQty from #_Temp group by cInvCode,PONO having sum(isnull(iINQty,0)) <> sum(isnull(iRDQty,0)) "; sSQL = sSQL.Replace("aaaaaa", sInvCode); dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtCou != null && dtCou.Rows.Count > 0) { sErr = sErr + "PL: Excel line " + (i + 1).ToString() + " IN & PL is not equal\n"; continue; } //判断入库单是否超出订单 sSQL = @" if object_id('tempdb..#_Temp2') is not null drop table #_Temp2 select a.cPOID,b.cInvCode,sum(b.iQuantity) as iQtyPO,cast(null as decimal(16,6)) as iRDQty into #_Temp2 from PO_Pomain a inner join po_podetails b on a.poid = b.poid where b.cInvCode = 'aaaaaa' and a.cPOID = 'bbbbbb' group by a.cPOID,b.cInvCode insert into #_Temp2 select a.PONO,a.cInvCode,cast(null as decimal(16,6)) as iQtyPO,sum(a.iQuantity) as iRDQty from Rdrecords01_Temp a where a.cInvCode = 'aaaaaa' and a.PONO = 'bbbbbb' group by a.cInvCode,a.PONO select count(1) as iCou,cInvCode,cPOID,sum(iQtyPO) as iINQty, sum(iRDQty) as iRDQty from #_Temp2 group by cInvCode,cPOID having sum(isnull(iQtyPO,0)) < sum(isnull(iRDQty,0)) "; sSQL = sSQL.Replace("aaaaaa", sInvCode); sSQL = sSQL.Replace("bbbbbb", gridViewPL.GetRowCellDisplayText(i, gridColPONO).ToString().Trim()); dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtCou != null && dtCou.Rows.Count > 0) { sErr = sErr + "PL: PO No line " + (i + 1).ToString() + " overorder entry\n"; continue; } } for (int i = 0; i < gridViewIN.RowCount; i++) { string sPONO = gridViewIN.GetRowCellValue(i, gridCol_PONO).ToString().Trim(); string sInvAbbCode = gridViewIN.GetRowCellValue(i, gridCol_PARTNO).ToString().Trim(); if (sInvAbbCode.Trim() == "") { continue; } //判断订单是否存在 sSQL = @" select cPOID from PO_Pomain where cPOID = 'aaaaaa' "; sSQL = sSQL.Replace("aaaaaa", sPONO); DataTable dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtCou == null || dtCou.Rows.Count == 0) { sErr = sErr + "IN: line " + (i + 1).ToString() + " PO No does not match\n"; continue; } sSQL = @" select * from Inventory where cInvCode = '{0}' or cInvAddCode = '{0}' "; sSQL = string.Format(sSQL, sInvAbbCode); DataTable dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtInv == null || dtInv.Rows.Count == 0) { sErr = sErr + "IN: Line " + (i + 1) + " cInvAddCode is not exists\n"; continue; } //判断订单是否存在物料 sSQL = @" select a.cPOID ,b.cInvCode from PO_Pomain a inner join PO_PODetails b on a.poid = b.poid inner join Inventory inv on b.cInvCode = inv.cInvCode where a.cPOID = 'aaaaaa' and (b.cInvCode = 'bbbbbb' or inv.cInvAddCode = 'bbbbbb') "; sSQL = sSQL.Replace("aaaaaa", gridViewIN.GetRowCellDisplayText(i, gridCol_PONO).ToString().Trim()); sSQL = sSQL.Replace("bbbbbb", sInvAbbCode); dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtCou == null || dtCou.Rows.Count == 0) { sErr = sErr + "IN: line " + (i + 1).ToString() + " PO No does not exist in this material\n"; continue; } sSQL = @" select inv.cInvCode,isnull(inv.bPurchase,0) as bPurchase 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, sInvAbbCode, gridViewIN.GetRowCellValue(i, gridCol_PONO).ToString().Trim()); dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtInv == null || dtInv.Rows.Count == 0) { sErr = sErr + "IN: Line " + (i + 1) + " cInvAddCode is not exists\n"; continue; } if (dtInv.Rows.Count > 1) { sSQL = @" select cInvCode,isnull(bPurchase,0) as bPurchase from Inventory where cInvAddCode = 'AAAAAAAA' "; sSQL = sSQL.Replace("AAAAAAAA", sInvAbbCode); dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtInv == null || dtInv.Rows.Count != 1) { sErr = sErr + "IN: Line " + (i + 1) + " cInvAddCode is non-unique and code is not exists,Excel line(PL) " + (i + 14) + "\n"; continue; } } string sInvCode = dtInv.Rows[0]["cInvCode"].ToString().Trim(); //防止有空数据 if (sInvCode != "") { decimal dPrice = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_PRICEPERUNIT)); decimal dSum = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_TOTALPRICE)); if (dPrice <= 0 || dSum <= 0) { sErr = sErr + "IN: Line " + (i + 1) + " unit price or amount is not correct\n"; continue; } } } if (sErr != "") { throw new Exception(sErr); } } catch (Exception ee) { throw new Exception(ee.Message); } }