private void btnImprot_Click(object sender, EventArgs e) { try { gridViewPL.FocusedRowHandle -= 1; gridViewPL.FocusedRowHandle += 1; } catch { } try { if (txtEntryNo.Text.Trim() == "") { txtEntryNo.Focus(); throw new Exception("Please set entry no"); } int iCou = gridViewPL.RowCount; if (iCou == 0) { throw new Exception("no data"); } string sMCode = ""; int iCodeCou = gridViewPL.RowCount; string sErr = ""; int iCount = 0; SqlConnection conn = new SqlConnection(Conn); conn.Open(); //启用事务 SqlTransaction tran = conn.BeginTransaction(); try { if (lookUpEditWarehouseIN == null || lookUpEditWarehouseIN.Text.Trim() == "") { lookUpEditWarehouseIN.Focus(); throw new Exception("Please choose transfer-in warehouse"); } if (lookUpEditWarehouseOUT == null || lookUpEditWarehouseOUT.Text.Trim() == "") { lookUpEditWarehouseOUT.Focus(); throw new Exception("Please choose transfer-out warehouse"); } string sWhCodeIn = lookUpEditWarehouseIN.EditValue.ToString().Trim(); string sWhCodeOut = lookUpEditWarehouseOUT.EditValue.ToString().Trim(); if (sWhCodeIn == sWhCodeOut) { throw new Exception("The warehouse can't be the same"); } string sRdCodeIn = ""; string sRdCodeOut = ""; string sDepCodeIn = ""; string sDepCodeOut = ""; string sSQL = @" select * from _Code where VouchType = 'TransVouch' "; DataTable dtCode = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtCode != null && dtCode.Rows.Count > 0) { sRdCodeIn = dtCode.Rows[0]["cRdCodeIn"].ToString().Trim(); sRdCodeOut = dtCode.Rows[0]["cRdCodeOut"].ToString().Trim(); sDepCodeIn = dtCode.Rows[0]["cDepCodeIn"].ToString().Trim(); sDepCodeOut = dtCode.Rows[0]["cDepCodeOut"].ToString().Trim(); } else { throw new Exception("Please set _code [TransVouch]"); } if (sWhCodeIn == "" || sWhCodeOut == "" || sRdCodeIn == "" || sRdCodeOut == "") { throw new Exception("Please set _code [TransVouchClaim]"); } DateTime dLogDate = BaseFunction.ReturnDate(sLogDate); //string sDate = dateEdit1.DateTime.ToString("yyyy-MM-dd"); string sDate = sLogDate; sSQL = @" select bflag_ST from gl_mend where iyear = aaaaaa and iperiod = bbbbbb "; sSQL = sSQL.Replace("aaaaaa", dLogDate.Year.ToString()); sSQL = sSQL.Replace("bbbbbb", dLogDate.Month.ToString()); DataTable dtTemp = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (BaseFunction.ReturnBool(dtTemp.Rows[0]["bflag_ST"])) { throw new Exception("The current date has been checked out"); } string sDateTime = dateEditQuery.DateTime.ToString("yyyy-MM-dd"); bool bNeedTR = false; //是否需要索赔调拨 #region 生成调拨单 long lID = -1; long lIDDetails = -1; sSQL = @" declare @p5 int set @p5=aaaaaa declare @p6 int set @p6=bbbbbb exec sp_GetId N'00',N'dddddd',N'tr',cccccc,@p5 output,@p6 output,default select @p5, @p6 "; sSQL = sSQL.Replace("aaaaaa", lID.ToString()); sSQL = sSQL.Replace("bbbbbb", lIDDetails.ToString()); sSQL = sSQL.Replace("cccccc", iCou.ToString()); sSQL = sSQL.Replace("dddddd", sAccID); DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; lID = BaseFunction.ReturnLong(dt.Rows[0][0]) - 1; lIDDetails = BaseFunction.ReturnLong(dt.Rows[0][1]) - iCodeCou; ////获得单据号 sSQL = "select * from VoucherHistory with (ROWLOCK) Where CardNumber='0304' AND cSeed = 'aaaaaa'"; sSQL = sSQL.Replace("aaaaaa", dLogDate.ToString("yyyyMMdd")); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; long iCode = 0; if (dt != null && dt.Rows.Count > 0) { iCode = BaseFunction.ReturnLong(dt.Rows[0]["cNumber"]) + 1; sSQL = @" update VoucherHistory set cNumber = aaaaaa where autoid = bbbbbb and CardNumber = '0304' "; sSQL = sSQL.Replace("aaaaaa", iCode.ToString()); sSQL = sSQL.Replace("bbbbbb", dt.Rows[0]["AutoId"].ToString().Trim()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } else { iCode = 1; sSQL = @" insert into VoucherHistory(CardNumber, cContent, cContentRule, cSeed, cNumber, bEmpty) values('0304','日期','日','aaaaaa',1,0) "; sSQL = sSQL.Replace("aaaaaa", dLogDate.ToString("yyyyMMdd")); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } iCode += 1; string sCode = iCode.ToString(); while (sCode.Length < 4) { sCode = "0" + sCode; } sCode = "TF" + dLogDate.ToString("yyyyMMdd") + sCode; sSQL = @" select distinct * from Rdrecord01 where id in (select id from Rdrecords01 where autoid = {0}) "; sSQL = string.Format(sSQL, gridViewPL.GetRowCellValue(0, gridColRds01ID)); DataTable dtRd = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; Model.TransVouch mod = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.TransVouch(); mod.cTVCode = sCode; mod.dTVDate = dLogDate; mod.cOWhCode = sWhCodeOut; mod.cIWhCode = sWhCodeIn; mod.cIRdCode = sRdCodeIn; mod.cORdCode = sRdCodeOut; if (sDepCodeIn != "") { mod.cIDepCode = sDepCodeIn; } if (sDepCodeOut != "") { mod.cODepCode = sDepCodeOut; } mod.cPersonCode = null; mod.cTVMemo = txtMemo.Text.Trim(); if (dtRd.Rows[0]["cDefine1"].ToString().Trim() != "") { mod.cDefine1 = dtRd.Rows[0]["cDefine1"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine2"].ToString().Trim() != "") { mod.cDefine2 = dtRd.Rows[0]["cDefine2"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine3"].ToString().Trim() != "") { mod.cDefine3 = dtRd.Rows[0]["cDefine3"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine4"].ToString().Trim() != "") { mod.cDefine4 = BaseFunction.ReturnDate(dtRd.Rows[0]["cDefine4"]); } if (dtRd.Rows[0]["cDefine5"].ToString().Trim() != "") { mod.cDefine5 = BaseFunction.ReturnLong(dtRd.Rows[0]["cDefine5"]); } if (dtRd.Rows[0]["cDefine6"].ToString().Trim() != "") { mod.cDefine6 = BaseFunction.ReturnDate(dtRd.Rows[0]["cDefine6"]); } if (dtRd.Rows[0]["cDefine7"].ToString().Trim() != "") { mod.cDefine7 = BaseFunction.ReturnDecimal(dtRd.Rows[0]["cDefine7"]); } if (dtRd.Rows[0]["cDefine8"].ToString().Trim() != "") { mod.cDefine8 = dtRd.Rows[0]["cDefine8"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine9"].ToString().Trim() != "") { mod.cDefine9 = dtRd.Rows[0]["cDefine9"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine10"].ToString().Trim() != "") { mod.cDefine10 = dtRd.Rows[0]["cDefine10"].ToString().Trim(); } mod.cAccounter = null; mod.iNetLock = 1; lID += 1; mod.ID = lID; mod.VT_ID = 89; mod.cMaker = sUserName; mod.dnmaketime = dLogDate; mod.cVerifyPerson = sUserName; mod.dVerifyDate = dLogDate; mod.cPSPCode = null; mod.cMPoCode = null; mod.iQuantity = 0; mod.bTransFlag = null; //if (dtRd.Rows[0]["cDefine11"].ToString().Trim() != "") //{ // mod.cDefine11 = dtRd.Rows[0]["cDefine11"].ToString().Trim(); //} mod.cDefine11 = txtEntryNo.Text.Trim(); if (dtRd.Rows[0]["cDefine12"].ToString().Trim() != "") { mod.cDefine12 = dtRd.Rows[0]["cDefine12"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine13"].ToString().Trim() != "") { mod.cDefine13 = dtRd.Rows[0]["cDefine13"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine14"].ToString().Trim() != "") { mod.cDefine14 = dtRd.Rows[0]["cDefine14"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine15"].ToString().Trim() != "") { mod.cDefine15 = BaseFunction.ReturnLong(dtRd.Rows[0]["cDefine5"]); } if (dtRd.Rows[0]["cDefine16"].ToString().Trim() != "") { mod.cDefine16 = BaseFunction.ReturnDecimal(dtRd.Rows[0]["cDefine16"]); } mod.iproorderid = null; mod.cTranRequestCode = null; mod.cVersion = null; mod.BomId = null; mod.cFree1 = null; mod.cFree2 = null; mod.cFree3 = null; mod.cFree4 = null; mod.cFree5 = null; mod.cFree6 = null; mod.cFree7 = null; mod.cFree8 = null; mod.cFree9 = null; mod.cFree10 = null; mod.cAppTVCode = null; mod.csource = "1"; mod.itransflag = "正向"; mod.cModifyPerson = null; mod.dModifyDate = null; mod.dnmaketime = DateTime.Now; mod.dnmodifytime = null; mod.ireturncount = null; mod.iverifystate = null; mod.iswfcontrolled = 0; mod.csourceguid = null; mod.csysbarcode = "||st12||" + mod.cTVCode; mod.cDefine1 = txtEntryNo.Text.Trim(); mod.cCurrentAuditor = null; DAL.TransVouch dalTR = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.TransVouch(); sSQL = dalTR.Add(mod); iCount += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sMCode = sMCode + mod.cTVCode + "\n"; int iRow = 0; for (int i = 0; i < gridViewPL.RowCount; i++) { if (!BaseFunction.ReturnBool(gridViewPL.GetRowCellValue(i, gridColSelected))) { continue; } long lRds01Autoid = BaseFunction.ReturnLong(gridViewPL.GetRowCellValue(i, gridColRds01ID)); if (lRds01Autoid == 0) { sErr = sErr + "Line " + (i + 1).ToString() + " purchase receipt warrant is not imported\n"; continue; } sSQL = @" select autoid from Rdrecords01 where autoid = aaaaaaaa "; sSQL = sSQL.Replace("aaaaaaaa", lRds01Autoid.ToString()); DataTable dtRds01 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtRds01 == null || dtRds01.Rows.Count == 0) { sErr = sErr + "Line " + (i + 1).ToString() + " purchase receipt warrant is not imported\n"; continue; } long lTRsID = BaseFunction.ReturnLong(gridViewPL.GetRowCellValue(i, gridColTrIDs)); if (lTRsID > 0) { sSQL = @" select autoid from TransVouchs where autoid = aaaaaaaa "; sSQL = sSQL.Replace("aaaaaaaa", lTRsID.ToString()); DataTable dtTRs01 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtTRs01 != null && dtTRs01.Rows.Count > 0) { sErr = sErr + "Line " + (i + 1).ToString() + " is imported\n"; continue; } } Model.TransVouchs mods = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.TransVouchs(); mods.cTVCode = mod.cTVCode; mods.cInvCode = gridViewPL.GetRowCellValue(i, gridColPARTNO).ToString().Trim(); mods.iTVNum = null; mods.iTVQuantity = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColQUANTITY)); lIDDetails += 1; mods.autoID = lIDDetails; mods.ID = mod.ID; mods.bCosting = true; iRow += 1; mods.irowno = iRow; mods.coutposcode = null; mods.cinposcode = null; //mods.cTVBatch = gridViewPL.GetRowCellValue(i, gridColcBatch).ToString().Trim(); mods.cDefine22 = gridViewPL.GetRowCellValue(i, gridColCONTAINERNO).ToString().Trim(); mods.cDefine23 = gridViewPL.GetRowCellValue(i, gridColBOXNO).ToString().Trim(); mods.cDefine24 = gridViewPL.GetRowCellValue(i, gridColCASENO).ToString().Trim(); mods.cDefine25 = gridViewPL.GetRowCellValue(i, gridColGWKGS).ToString().Trim(); mods.cDefine28 = gridViewPL.GetRowCellValue(i, gridColNWKGS).ToString().Trim(); mods.cbsysbarcode = "||st12|" + mod.cTVCode + "|" + iRow.ToString(); DAL.TransVouchs dalTRs = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.TransVouchs(); sSQL = dalTRs.Add(mods); iCount += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); //回写导入中间表 Rdrecords10_Import sSQL = @" update RdRecords01_Import set TrIDs = AAAAAA,EntryNO = 'CCCCCC' WHERE AUTOID = BBBBBB "; sSQL = sSQL.Replace("AAAAAA", mods.autoID.ToString()); sSQL = sSQL.Replace("BBBBBB", gridViewPL.GetRowCellValue(i, gridColAutoID).ToString()); sSQL = sSQL.Replace("CCCCCC", txtEntryNo.Text.Trim()); int iRet = DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); if (iRet != 1) { sErr = sErr + "Line " + (i + 1) + " update dl err\n"; } decimal dClaimQty = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColClaimQty)); if (dClaimQty > 0) { bNeedTR = true; } } if (iRow > 0) { clsU8 cls = new clsU8(); cls.TransVouch_Audit_U8V111(tran, mod.cTVCode, sAccID, sUserName); } #endregion #region 生成索赔调拨单 if (bNeedTR) { sRdCodeIn = ""; sRdCodeOut = ""; sDepCodeIn = ""; sDepCodeOut = ""; sSQL = @" select * from _Code where VouchType = 'TransVouchClaim' "; dtCode = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtCode != null && dtCode.Rows.Count > 0) { sRdCodeIn = dtCode.Rows[0]["cRdCodeIn"].ToString().Trim(); sRdCodeOut = dtCode.Rows[0]["cRdCodeOut"].ToString().Trim(); sDepCodeIn = dtCode.Rows[0]["cDepCodeIn"].ToString().Trim(); sDepCodeOut = dtCode.Rows[0]["cDepCodeOut"].ToString().Trim(); } else { throw new Exception("Please set _code [TransVouchClaim]"); } if (sRdCodeIn == "" || sRdCodeOut == "") { throw new Exception("Please set _code [TransVouchClaim]"); } lID = -1; lIDDetails = -1; sSQL = @" declare @p5 int set @p5=aaaaaa declare @p6 int set @p6=bbbbbb exec sp_GetId N'00',N'dddddd',N'tr',cccccc,@p5 output,@p6 output,default select @p5, @p6 "; sSQL = sSQL.Replace("aaaaaa", lID.ToString()); sSQL = sSQL.Replace("bbbbbb", lIDDetails.ToString()); sSQL = sSQL.Replace("cccccc", iCou.ToString()); sSQL = sSQL.Replace("dddddd", sAccID); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; lID = BaseFunction.ReturnLong(dt.Rows[0][0]) - 1; lIDDetails = BaseFunction.ReturnLong(dt.Rows[0][1]) - iCodeCou; ////获得单据号 sSQL = "select * from VoucherHistory with (ROWLOCK) Where CardNumber='0304' AND cSeed = 'aaaaaa'"; sSQL = sSQL.Replace("aaaaaa", dLogDate.ToString("yyyyMMdd")); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; iCode = 0; if (dt != null && dt.Rows.Count > 0) { iCode = BaseFunction.ReturnLong(dt.Rows[0]["cNumber"]) + 1; sSQL = @" update VoucherHistory set cNumber = aaaaaa where autoid = bbbbbb and CardNumber = '0304' "; sSQL = sSQL.Replace("aaaaaa", iCode.ToString()); sSQL = sSQL.Replace("bbbbbb", dt.Rows[0]["AutoId"].ToString().Trim()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } else { iCode = 1; sSQL = @" insert into VoucherHistory(CardNumber, cContent, cContentRule, cSeed, cNumber, bEmpty) values('0304','日期','日','aaaaaa',1,0) "; sSQL = sSQL.Replace("aaaaaa", dLogDate.ToString("yyyyMMdd")); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } iCode += 1; sCode = iCode.ToString(); while (sCode.Length < 4) { sCode = "0" + sCode; } sCode = "TF" + dLogDate.ToString("yyyyMMdd") + sCode; mod = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.TransVouch(); mod.cTVCode = sCode; mod.dTVDate = dLogDate; mod.cOWhCode = lookUpEditWarehouseIN.EditValue.ToString().Trim(); if (mod.cOWhCode.ToUpper().StartsWith("A")) { mod.cIWhCode = "A61"; } if (mod.cOWhCode.ToUpper().StartsWith("B")) { mod.cIWhCode = "B61"; } if (mod.cIWhCode == null || mod.cIWhCode.Trim() == "") { throw new Exception("Please set claim warehouse"); } mod.cIRdCode = sRdCodeOut; mod.cORdCode = sRdCodeIn; mod.cIDepCode = sDepCodeIn; mod.cODepCode = sDepCodeOut; mod.cPersonCode = null; mod.cTVMemo = txtMemo.Text.Trim(); if (dtRd.Rows[0]["cDefine1"].ToString().Trim() != "") { mod.cDefine1 = dtRd.Rows[0]["cDefine1"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine2"].ToString().Trim() != "") { mod.cDefine2 = dtRd.Rows[0]["cDefine2"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine3"].ToString().Trim() != "") { mod.cDefine3 = dtRd.Rows[0]["cDefine3"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine4"].ToString().Trim() != "") { mod.cDefine4 = BaseFunction.ReturnDate(dtRd.Rows[0]["cDefine4"]); } if (dtRd.Rows[0]["cDefine5"].ToString().Trim() != "") { mod.cDefine5 = BaseFunction.ReturnLong(dtRd.Rows[0]["cDefine5"]); } if (dtRd.Rows[0]["cDefine6"].ToString().Trim() != "") { mod.cDefine6 = BaseFunction.ReturnDate(dtRd.Rows[0]["cDefine6"]); } if (dtRd.Rows[0]["cDefine7"].ToString().Trim() != "") { mod.cDefine7 = BaseFunction.ReturnDecimal(dtRd.Rows[0]["cDefine7"]); } if (dtRd.Rows[0]["cDefine8"].ToString().Trim() != "") { mod.cDefine8 = dtRd.Rows[0]["cDefine8"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine9"].ToString().Trim() != "") { mod.cDefine9 = dtRd.Rows[0]["cDefine9"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine10"].ToString().Trim() != "") { mod.cDefine10 = dtRd.Rows[0]["cDefine10"].ToString().Trim(); } mod.cAccounter = null; mod.iNetLock = 1; lID += 1; mod.ID = lID; mod.VT_ID = 89; mod.cMaker = sUserName; mod.dnmaketime = dLogDate; mod.cVerifyPerson = sUserName; mod.dVerifyDate = dLogDate; mod.cPSPCode = null; mod.cMPoCode = null; mod.iQuantity = 0; mod.bTransFlag = null; if (dtRd.Rows[0]["cDefine11"].ToString().Trim() != "") { mod.cDefine11 = dtRd.Rows[0]["cDefine11"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine12"].ToString().Trim() != "") { mod.cDefine12 = dtRd.Rows[0]["cDefine12"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine13"].ToString().Trim() != "") { mod.cDefine13 = dtRd.Rows[0]["cDefine13"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine14"].ToString().Trim() != "") { mod.cDefine14 = dtRd.Rows[0]["cDefine14"].ToString().Trim(); } if (dtRd.Rows[0]["cDefine15"].ToString().Trim() != "") { mod.cDefine15 = BaseFunction.ReturnLong(dtRd.Rows[0]["cDefine5"]); } if (dtRd.Rows[0]["cDefine16"].ToString().Trim() != "") { mod.cDefine16 = BaseFunction.ReturnDecimal(dtRd.Rows[0]["cDefine16"]); } mod.iproorderid = null; mod.cTranRequestCode = null; mod.cVersion = null; mod.BomId = null; mod.cFree1 = null; mod.cFree2 = null; mod.cFree3 = null; mod.cFree4 = null; mod.cFree5 = null; mod.cFree6 = null; mod.cFree7 = null; mod.cFree8 = null; mod.cFree9 = null; mod.cFree10 = null; mod.cAppTVCode = null; mod.csource = "1"; mod.itransflag = "正向"; mod.cModifyPerson = null; mod.dModifyDate = null; mod.dnmaketime = DateTime.Now; mod.dnmodifytime = null; mod.ireturncount = null; mod.iverifystate = null; mod.iswfcontrolled = 0; mod.csourceguid = null; mod.csysbarcode = "||st12||" + mod.cTVCode; mod.cDefine1 = txtEntryNo.Text.Trim(); mod.cCurrentAuditor = null; dalTR = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.TransVouch(); sSQL = dalTR.Add(mod); iCount += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sMCode = sMCode + mod.cTVCode + "\n"; iRow = 0; for (int i = 0; i < gridViewPL.RowCount; i++) { if (!BaseFunction.ReturnBool(gridViewPL.GetRowCellValue(i, gridColSelected))) { continue; } decimal dQty = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColQUANTITY)); decimal dClaimQty = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColClaimQty)); if (dClaimQty < 0 || dClaimQty > dQty) { sErr = sErr + "Line " + (i + 1).ToString() + " the claim quantity is wrong\n"; continue; } if (dClaimQty == 0) { continue; } long lRds01Autoid = BaseFunction.ReturnLong(gridViewPL.GetRowCellValue(i, gridColRds01ID)); if (lRds01Autoid == 0) { sErr = sErr + "Line " + (i + 1).ToString() + " purchase receipt warrant is not imported\n"; continue; } sSQL = @" select autoid from Rdrecords01 where autoid = aaaaaaaa "; sSQL = sSQL.Replace("aaaaaaaa", lRds01Autoid.ToString()); DataTable dtRds01 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtRds01 == null || dtRds01.Rows.Count == 0) { sErr = sErr + "Line " + (i + 1).ToString() + " purchase receipt warrant is not imported\n"; continue; } long lTRsID = BaseFunction.ReturnLong(gridViewPL.GetRowCellValue(i, gridColTrIDs)); if (lTRsID > 0) { sSQL = @" select autoid from TransVouchs where autoid = aaaaaaaa "; sSQL = sSQL.Replace("aaaaaaaa", lTRsID.ToString()); DataTable dtTRs01 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtTRs01 != null && dtTRs01.Rows.Count > 0) { sErr = sErr + "Line " + (i + 1).ToString() + " is imported\n"; continue; } } Model.TransVouchs mods = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.TransVouchs(); mods.cTVCode = mod.cTVCode; mods.cInvCode = gridViewPL.GetRowCellValue(i, gridColPARTNO).ToString().Trim(); mods.iTVNum = null; mods.iTVQuantity = dClaimQty; lIDDetails += 1; mods.autoID = lIDDetails; mods.ID = mod.ID; mods.bCosting = true; iRow += 1; mods.irowno = iRow; mods.coutposcode = null; mods.cinposcode = null; //mods.cTVBatch = gridViewPL.GetRowCellValue(i, gridColcBatch).ToString().Trim(); mods.cDefine22 = gridViewPL.GetRowCellValue(i, gridColCONTAINERNO).ToString().Trim(); mods.cDefine23 = gridViewPL.GetRowCellValue(i, gridColBOXNO).ToString().Trim(); mods.cDefine24 = gridViewPL.GetRowCellValue(i, gridColCASENO).ToString().Trim(); mods.cDefine25 = gridViewPL.GetRowCellValue(i, gridColGWKGS).ToString().Trim(); mods.cDefine28 = gridViewPL.GetRowCellValue(i, gridColNWKGS).ToString().Trim(); mods.cbsysbarcode = "||st12|" + mod.cTVCode + "|" + iRow.ToString(); DAL.TransVouchs dalTRs = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.TransVouchs(); sSQL = dalTRs.Add(mods); iCount += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); //回写导入中间表 Rdrecords10_Import sSQL = @" update RdRecords01_Import set TrIDsClaim = AAAAAA,ClaimQty = DDDDDD WHERE AUTOID = BBBBBB "; sSQL = sSQL.Replace("AAAAAA", mods.autoID.ToString()); sSQL = sSQL.Replace("BBBBBB", gridViewPL.GetRowCellValue(i, gridColAutoID).ToString()); sSQL = sSQL.Replace("DDDDDD", BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColClaimQty)).ToString().Trim()); int iRet = DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } if (iRow > 0) { clsU8 cls = new clsU8(); cls.TransVouch_Audit_U8V111(tran, mod.cTVCode, sAccID, sUserName); } } #endregion if (sErr != "") { throw new Exception(sErr); } if (iCount > 0) { tran.Commit(); MessageBox.Show("Success:\n" + sMCode); 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(); } }
private void btnSave_Click(object sender, EventArgs e) { try { gridView1.FocusedRowHandle -= 1; gridView1.FocusedRowHandle += 1; } catch { } txtBarCode.Focus(); string sErr = ""; int iCount = 0; try { string sProcess = gridView1.GetRowCellValue(0, gridColProcess).ToString().Trim(); string sProcessNext = gridView1.GetRowCellValue(0, gridColProcessNext).ToString().Trim(); if (sProcess == sProcessNext) { throw new Exception("Process is err"); } 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")); int iYear = dNow.Year; int iPeriod = dNow.Month; string s期间 = BaseFunction.ReturnDate(iYear.ToString() + "-" + iPeriod.ToString() + "-01").ToString("yyyyMM"); sSQL = "select isnull(bflag_ST,0) as bflag from GL_mend where iYPeriod = '" + s期间 + "'"; DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dt == null || dt.Rows.Count == 0) { throw new Exception("Access module state failure"); } int i结账 = BaseFunction.ReturnInt(dt.Rows[0]["bflag"]); if (i结账 > 0) { throw new Exception(dNow.ToString("yyyy-MM") + " have checked out"); } //判断是否转入质检工序 bool bIQCIn = false; //材料入库检验 bool bOQCIn = false; //产品销售出库检验 sSQL = @" select cWhMemo from Warehouse where cWhCode = '{0}' "; sSQL = string.Format(sSQL, lookUpEditProcessNext.EditValue.ToString().Trim()); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dt.Rows[0]["cWhMemo"].ToString().Trim().ToLower().Contains("iqc")) { bIQCIn = true; } if (dt.Rows[0]["cWhMemo"].ToString().Trim().ToLower().Contains("oqc")) { bOQCIn = true; } //判断是否从质检工序转出 bool bIQCOut = false; //材料入库检验工序转出 bool bOQCOut = false; //产品销售出库检验工序转出 sSQL = @" select cWhMemo from Warehouse where cWhCode = '{0}' "; sSQL = string.Format(sSQL, lookUpEditProcess.EditValue.ToString().Trim()); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dt.Rows[0]["cWhMemo"].ToString().Trim().ToLower().Contains("iqc")) { bIQCOut = true; } if (dt.Rows[0]["cWhMemo"].ToString().Trim().ToLower().Contains("oqc")) { bOQCOut = true; } #region 流转 long lID = -1; long lIDDetails = -1; sSQL = @" declare @p5 int set @p5=aaaaaa declare @p6 int set @p6=bbbbbb exec sp_GetId N'00',N'dddddd',N'tr',1,@p5 output,@p6 output,default select @p5, @p6 "; sSQL = sSQL.Replace("aaaaaa", lID.ToString()); sSQL = sSQL.Replace("bbbbbb", lIDDetails.ToString()); sSQL = sSQL.Replace("dddddd", sAccID); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; lID = BaseFunction.ReturnLong(dt.Rows[0][0]) - 1; lIDDetails = BaseFunction.ReturnLong(dt.Rows[0][1]) - 1; ////获得单据号 sSQL = "select * from VoucherHistory with (ROWLOCK) Where CardNumber='0304' AND cContentRule = 'YYYY' AND cSeed = 'aaaaaa'"; sSQL = sSQL.Replace("aaaaaa", dNow.ToString("yyyy")); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; long lCode = 0; if (dt != null && dt.Rows.Count > 0) { lCode = BaseFunction.ReturnLong(dt.Rows[0]["cNumber"]); } else { lCode = 0; } lCode += 1; string sCode = lCode.ToString(); while (sCode.Length < 6) { sCode = "0" + sCode; } sCode = "TR" + dNow.ToString("yyyy") + sCode; Model.TransVouch mod = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.TransVouch(); mod.cTVCode = sCode; mod.dTVDate = dNowDate; mod.cOWhCode = lookUpEditProcess.EditValue.ToString().Trim(); mod.cIWhCode = lookUpEditProcessNext.EditValue.ToString().Trim(); mod.cODepCode = null; mod.cIDepCode = null; mod.cPersonCode = null; mod.cIRdCode = "TI"; mod.cORdCode = "TO"; mod.cTVMemo = null; mod.cDefine1 = null; mod.cDefine2 = null; mod.cDefine3 = null; mod.cDefine4 = null; mod.cDefine5 = null; mod.cDefine6 = null; mod.cDefine7 = null; mod.cDefine8 = null; mod.cDefine9 = null; mod.cDefine10 = null; mod.cAccounter = null; mod.iNetLock = 1; lID += 1; mod.ID = lID; mod.VT_ID = 89; mod.cMaker = sUserName; mod.dnmaketime = dNow; mod.cVerifyPerson = sUserName; mod.dVerifyDate = dNowDate; mod.cPSPCode = null; mod.cMPoCode = null; mod.iQuantity = 0; mod.bTransFlag = null; mod.cDefine11 = null; mod.cDefine12 = null; mod.cDefine13 = null; mod.cDefine14 = null; mod.cDefine15 = null; mod.cDefine16 = null; mod.iproorderid = null; mod.cTranRequestCode = null; mod.cVersion = null; mod.BomId = null; mod.cFree1 = null; mod.cFree2 = null; mod.cFree3 = null; mod.cFree4 = null; mod.cFree5 = null; mod.cFree6 = null; mod.cFree7 = null; mod.cFree8 = null; mod.cFree9 = null; mod.cFree10 = null; mod.cAppTVCode = null; mod.csource = "1"; mod.itransflag = "正向"; mod.cModifyPerson = null; mod.dModifyDate = null; mod.dnmaketime = dNow; mod.dnmodifytime = null; mod.ireturncount = null; mod.iverifystate = null; mod.iswfcontrolled = 0; mod.csourceguid = null; mod.csysbarcode = "||st12||" + mod.cTVCode; DAL.TransVouch dalTR = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.TransVouch(); sSQL = dalTR.Add(mod); iCount += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); mod.cCurrentAuditor = null; int iRow = 0; for (int i = 0; i < gridView1.RowCount; i++) { if (!BaseFunction.ReturnBool(gridView1.GetRowCellValue(i, gridColchoose))) { continue; } string sBarCode = gridView1.GetRowCellValue(i, gridColLotNO).ToString().Trim(); sSQL = @" select * from _BarCodeLabel where [BarCode] = '{0}' and iSOsID = {1}"; sSQL = string.Format(sSQL, sBarCode, gridView1.GetRowCellValue(i, gridColiSOsID).ToString()); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dt == null || dt.Rows.Count == 0) { sErr = sErr + "row " + (i + 1).ToString() + "is not exists \n"; continue; } if (dt.Rows[0]["CloseUid"].ToString().Trim() != "") { sErr = sErr + "row " + (i + 1).ToString() + " is closed \n"; continue; } if (dt.Rows[0]["Process"].ToString().Trim().ToLower() != lookUpEditProcess.EditValue.ToString().Trim().ToLower()) { sErr = sErr + "row " + (i + 1).ToString() + " process is changed \n"; continue; } if (BaseFunction.ReturnDecimal(dt.Rows[0]["LotQTY"]) != BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColLOTQTY))) { sErr = sErr + "row " + (i + 1).ToString() + " number is changed \n"; continue; } if (dt.Rows[0]["IQCStatus"].ToString() != gridView1.GetRowCellValue(i, gridColIQCStatus).ToString().Trim()) { sErr = sErr + "row " + (i + 1).ToString() + " iqcstatus is changed \n"; continue; } if (dt.Rows[0]["OQCStatus"].ToString() != gridView1.GetRowCellValue(i, gridColOQCStatus).ToString().Trim()) { sErr = sErr + "row " + (i + 1).ToString() + " iqcstatus is changed \n"; continue; } iRow += 1; Model.BarStatus model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.BarStatus(); model.BarCode = sBarCode; model.iSOsID = BaseFunction.ReturnLong(gridView1.GetRowCellValue(i, gridColiSOsID)); model.Type = "工序流转"; if (bIQCIn) { model.Type = "IQC"; sSQL = @" update _IQC_RMDF set ClosedUid = '{0}',dtmClose = getdate() where LotNo = '{1}' and iSOsID = '{2}' and isnull(ClosedUid,'') <> '' "; sSQL = string.Format(sSQL, sUserID, model.BarCode, model.iSOsID); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } if (bOQCIn) { model.Type = "OQC"; } model.RoutingFrom = lookUpEditProcess.EditValue.ToString().Trim(); model.RoutingTo = lookUpEditProcessNext.EditValue.ToString().Trim(); model.UpdateTime = dNow; model.QTY = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColLOTQTY)); model.CreateUid = sUserID; model.CreateDate = dNow; DAL.BarStatus dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.BarStatus(); sSQL = dal.Add(model); iCount += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); //回写上一道工序的结束时间 sSQL = @" declare @iID int select @iID = max(iID) from _BarStatus where [BarCode] = '{0}' and iSOsID = '{1}' and iID < ( select max(iID) as maxID from _BarStatus where [BarCode] = '{0}' and iSOsID = '{1}' ) update _BarStatus set EndTime = '{2}' where iID = @iID and [BarCode] = '{0}' and iSOsID = '{1}' "; sSQL = string.Format(sSQL, sBarCode, model.iSOsID, dNow); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); if (bIQCOut) { sSQL = @" select * from _BarCodeLabel where [BarCode] = '{0}' and iSOsID = '{1}' "; sSQL = string.Format(sSQL, sBarCode, model.iSOsID); DataTable dtQC = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; string sIQCStatus = dtQC.Rows[0]["IQCStatus"].ToString().Trim().ToLower(); sIQCStatus = sIQCStatus.Replace(" ", ""); if (!sIQCStatus.ToLower().StartsWith("iqc")) { sErr = sErr + "row " + (i + 1).ToString() + " IQC not passed \n"; continue; } } if (bOQCOut) { sSQL = @" select * from _BarCodeLabel where [BarCode] = '{0}' and iSOsID = '{1}' "; sSQL = string.Format(sSQL, sBarCode, model.iSOsID); DataTable dtQC = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; string sOQCStatus = dtQC.Rows[0]["OQCStatus"].ToString().Trim().ToLower(); sOQCStatus = sOQCStatus.Replace(sOQCStatus, " "); if (sOQCStatus == "oqc-ONHOLD".ToLower()) { sErr = sErr + "row " + (i + 1).ToString() + " OQC onhold \n"; continue; } } //回写 BarCodeLabel sSQL = "update [_BarCodeLabel] set process = '" + model.RoutingTo + "',Status = '流转' where [BarCode] = '" + sBarCode + "' and iSOsID = '" + model.iSOsID + "'"; DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); if (bIQCIn) { sSQL = "update [_BarCodeLabel] set IQCStatus = 'Pending IQC',Status = 'IQC' where [BarCode] = '" + sBarCode + "' and iSOsID = '" + model.iSOsID + "'"; int iRunCou = DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } if (bOQCIn) { sSQL = "update [_BarCodeLabel] set OQCStatus = 'Pending OQC',Status = 'OQC' where [BarCode] = '" + sBarCode + "' and iSOsID = '" + model.iSOsID + "'"; DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } Model.TransVouchs mods = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.TransVouchs(); mods.cTVCode = mod.cTVCode; mods.cInvCode = gridView1.GetRowCellValue(i, gridColItemNO).ToString().Trim(); mods.iTVNum = null; mods.iTVQuantity = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColLOTQTY)); lIDDetails += 1; mods.autoID = lIDDetails; mods.ID = mod.ID; mods.bCosting = true; mods.irowno = iRow; mods.coutposcode = null; mods.cinposcode = null; mods.cTVBatch = gridView1.GetRowCellValue(i, gridColcBatch).ToString().Trim(); mods.cbsysbarcode = "||st12|" + mod.cTVCode + "|" + iRow.ToString(); DAL.TransVouchs dalTRs = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.TransVouchs(); sSQL = dalTRs.Add(mods); iCount += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } if (sErr.Length > 0) { throw new Exception(sErr); } if (lID > 1000000000) { lID = lID - 1000000000; } if (lIDDetails > 1000000000) { lIDDetails = lIDDetails - 1000000000; } sSQL = "update UFSystem..UA_Identity set iFatherId = " + lID.ToString() + ",iChildId = " + lIDDetails + " where cAcc_Id = '" + sAccID + "' and cVouchType = 'tr'"; DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" if exists(select * from VoucherHistory where CardNumber='0304' AND cContentRule = 'YYYY' AND cSeed = 'bbbbbb') update VoucherHistory set cNumber = aaaaaa where CardNumber = '0304' AND cContentRule = 'YYYY' AND cSeed = 'bbbbbb' else insert into VoucherHistory(CardNumber,cContent,cContentRule,cSeed,cNumber,bEmpty) values('0304','日期','YYYY','bbbbbb','aaaaaa',0) "; sSQL = sSQL.Replace("aaaaaa", lCode.ToString()); sSQL = sSQL.Replace("bbbbbb", dNow.ToString("yyyy")); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); clsU8 cls = new clsU8(); cls.TransVouch_Audit_U8V111(tran, mod.cTVCode, sAccID, sUserName); #endregion if (iCount > 0) { tran.Commit(); MessageBox.Show("OK\n"); SetTxtNull(); gridControl1.DataSource = DbHelperSQL.Query(sSQLBar); txtBarCode.Focus(); } } catch (Exception ee) { tran.Rollback(); throw new Exception(ee.Message); } } catch (Exception ee) { FrmMsgBox frm = new FrmMsgBox(); frm.richTextBox1.Text = ee.Message; frm.ShowDialog(); } gridColScanTime.SortOrder = DevExpress.Data.ColumnSortOrder.Descending; }