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(); 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"); } #region 必须销售类型是“WP”,状态非关闭。 回写销售订单数量,并新增其它出库单(数量增加红字出库单,数量减少蓝字出库单) for (int i = 0; i < gridView1.RowCount; i++) { long lSOsID = BaseFunction.ReturnLong(gridView1.GetRowCellValue(i, gridColiSOsID)); string sBarCode = gridView1.GetRowCellValue(i, gridColLotNO).ToString().Trim(); decimal dLotQTY = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColLOTQTY)); decimal dOrderQTY = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColcbdefine4)); string siSOsID = gridView1.GetRowCellValue(i, gridColiSOsID).ToString().Trim(); sSQL = @" select LOTQTY from [dbo].[_BarCodeLabel] where Barcode = '{0}' and iSOsID = {1} "; sSQL = string.Format(sSQL, sBarCode, lSOsID); DataTable dtBarCodeLabel = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dLotQTY == BaseFunction.ReturnDecimal(dtBarCodeLabel.Rows[0]["LOTQTY"])) { continue; } decimal dQTYtc = BaseFunction.ReturnDecimal(dtBarCodeLabel.Rows[0]["LOTQTY"]) - dLotQTY; if (dLotQTY > dOrderQTY) { sErr = sErr + "Row " + (i + 1).ToString() + " lotqty is err\n"; continue; } if (gridView1.GetRowCellValue(i, gridColLOTQTY).ToString().Trim() == "") { sErr = sErr + "Row " + (i + 1).ToString() + " lotqty is err\n"; continue; } if (BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColLOTQTY).ToString().Trim()) == 0) { sErr = sErr + "Row " + (i + 1).ToString() + " lotqty is 0 err\n"; continue; } //回写条码数量 sSQL = @"update [_BarCodeLabel] set [Status] = '调整', orderqty = aaaaaa, LOTQTY = aaaaaa where BarCode = 'bbbbbb' and iSOsID = " + gridView1.GetRowCellValue(i, gridColiSOsID).ToString().Trim(); sSQL = sSQL.Replace("aaaaaa", dLotQTY.ToString()); sSQL = sSQL.Replace("bbbbbb", sBarCode); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); Model.BarStatus model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.BarStatus(); model.BarCode = sBarCode; model.iSOsID = BaseFunction.ReturnLong(gridView1.GetRowCellValue(i, gridColiSOsID)); model.Type = "调整"; model.RoutingFrom = gridView1.GetRowCellValue(i, gridColProcess).ToString().Trim(); model.RoutingTo = gridView1.GetRowCellValue(i, gridColProcess).ToString().Trim(); model.UpdateTime = dNow; model.QTY = dLotQTY; 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, model.BarCode, model.iSOsID, dNow); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); //回写销售订单数量 sSQL = @"update SO_SODetails set iQuantity = aaaaaa where iSOsID = bbbbbb"; sSQL = sSQL.Replace("aaaaaa", dLotQTY.ToString()); sSQL = sSQL.Replace("bbbbbb", lSOsID.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); //回写销售订单金额 sSQL = @" update SO_SODetails set iMoney = iQuantity * iUnitPrice,iSum = iTaxUnitPrice * iQuantity,iTax = iTaxUnitPrice * iQuantity - iQuantity * iUnitPrice ,iNatMoney = iNatUnitPrice * iQuantity , iNatSum = iNatMoney * (1 + iTaxRate / 100),iNatTax = iNatMoney * (1 + iTaxRate / 100) - iNatUnitPrice * iQuantity where iSOsID = bbbbbb "; sSQL = sSQL.Replace("bbbbbb", lSOsID.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); //------------------------------------------------------------------------ #region 步生成其它出库单 sSQL = @" select * from SO_SOMain a inner join SO_SODetails b on a.ID = b.ID left join _SystemSet c on a.cSTCode = c.cSTCode inner join Inventory d on b.cInvCode = d.cInvCode where b.iSOsID = aaaaaa order by b.autoid "; sSQL = sSQL.Replace("aaaaaa", lSOsID.ToString().Trim()); DataTable dtSOMain = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtSOMain == null || dtSOMain.Rows.Count == 0) { throw new Exception("Sale Order not exists err"); } string sCTCode = dtSOMain.Rows[0]["cSTCode"].ToString().Trim(); //DateTime dDate = BaseFunction.ReturnDate(dtSOMain.Rows[0]["dDate"]); DateTime dDate = DateTime.Today; sSQL = "select isnull(bflag_ST,0) as bflag from GL_mend where iYPeriod = '" + dDate.ToString("yyyyMM") + "'"; 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结账RD = BaseFunction.ReturnInt(dt.Rows[0]["bflag"]); if (i结账RD > 0) { throw new Exception(dDate.ToString("yyyy-MM") + " have checked out"); } //获得单据号 sSQL = "select cNumber from VoucherHistory with (ROWLOCK) Where cSeed = 'aaaaaa' AND cContentRule = 'YYYY' AND CardNumber = '0302' ORDER BY cNumber DESC"; sSQL = sSQL.Replace("aaaaaa", dDate.ToString("yyyy")); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; long lCodeRD = 0; if (dt != null && dt.Rows.Count > 0) { lCodeRD = BaseFunction.ReturnLong(dt.Rows[0]["cNumber"]); } else { lCodeRD = 0; } long lIDRD = -1; long lIDRDDetails = -1; sSQL = @" declare @p5 int set @p5=aaaaaa declare @p6 int set @p6=bbbbbb exec sp_GetId N'00',N'dddddd',N'rd',cccccc,@p5 output,@p6 output,default select @p5, @p6 "; sSQL = sSQL.Replace("aaaaaa", lIDRD.ToString()); sSQL = sSQL.Replace("bbbbbb", lIDRDDetails.ToString()); sSQL = sSQL.Replace("cccccc", 1.ToString()); sSQL = sSQL.Replace("dddddd", sAccID); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; lIDRD = BaseFunction.ReturnLong(dt.Rows[0][0]) - 1; lIDRDDetails = BaseFunction.ReturnLong(dt.Rows[0][1]) - 1; //ArrayList aList = new ArrayList(); Model.RdRecord09 modelRD = new Model.RdRecord09(); lIDRD += 1; modelRD.ID = lIDRD; modelRD.bRdFlag = 0; modelRD.cVouchType = "09"; modelRD.cBusType = "其他出库"; modelRD.cSource = "库存"; //modelRD.cBusCode = modelRD.cWhCode = dtSOMain.Rows[0]["cWhCode"].ToString().Trim(); //需要仓库默认值 if (dtSOMain.Rows[0]["cWhCode"].ToString().Trim() == "") { throw new Exception("Please set default warehouse"); } modelRD.dDate = dDate; //需要默认值 lCodeRD += 1; string sCodeRD = lCodeRD.ToString(); while (sCodeRD.Length < 6) { sCodeRD = "0" + sCodeRD; } modelRD.cCode = "MR" + dNow.ToString("yyyy") + sCodeRD; modelRD.cRdCode = "AD"; //需要默认值 if (dtSOMain.Rows[0]["cRdCode"].ToString().Trim() == "") { throw new Exception("Please set RdCode"); } //modelRD.cDepCode = "" //modelRD.cPersonCode; //modelRD.cPTCode; //modelRD.cSTCode; //modelRD.cCusCode; //modelRD.cVenCode; //modelRD.cOrderCode; //modelRD.cARVCode; //modelRD.cBillCode; //modelRD.cDLCode; //modelRD.cProBatch; modelRD.cHandler = sUserName; //modelRD.cMemo; modelRD.bTransFlag = false; //modelRD.cAccounter; modelRD.cMaker = sUserName; modelRD.cDefine1 = dtSOMain.Rows[0]["cDefine1"].ToString(); modelRD.cDefine2 = dtSOMain.Rows[0]["cDefine2"].ToString(); modelRD.cDefine3 = dtSOMain.Rows[0]["cDefine3"].ToString(); if (dtSOMain.Rows[0]["cDefine4"].ToString().Trim() != "") { modelRD.cDefine4 = BaseFunction.ReturnDate(dtSOMain.Rows[0]["cDefine4"]); } if (dtSOMain.Rows[0]["cDefine5"].ToString().Trim() != "") { modelRD.cDefine5 = BaseFunction.ReturnLong(dtSOMain.Rows[0]["cDefine5"]); } if (dtSOMain.Rows[0]["cDefine6"].ToString().Trim() != "") { modelRD.cDefine6 = BaseFunction.ReturnDate(dtSOMain.Rows[0]["cDefine6"]); } if (dtSOMain.Rows[0]["cDefine7"].ToString().Trim() != "") { modelRD.cDefine7 = BaseFunction.ReturnDecimal(dtSOMain.Rows[0]["cDefine7"]); } modelRD.cDefine8 = dtSOMain.Rows[0]["cDefine8"].ToString(); modelRD.cDefine9 = dtSOMain.Rows[0]["cDefine9"].ToString(); modelRD.cDefine10 = dtSOMain.Rows[0]["cDefine10"].ToString(); //modelRD.dKeepDate; modelRD.dVeriDate = dDate; modelRD.bpufirst = false; modelRD.biafirst = false; //modelRD.iMQuantity; //modelRD.dARVDate; //modelRD.cChkCode; //modelRD.dChkDate; //modelRD.cChkPerson; modelRD.VT_ID = 85; modelRD.bIsSTQc = false; modelRD.cDefine11 = dtSOMain.Rows[0]["cDefine11"].ToString(); modelRD.cDefine12 = dtSOMain.Rows[0]["cDefine12"].ToString(); modelRD.cDefine13 = dtSOMain.Rows[0]["cDefine13"].ToString(); modelRD.cDefine14 = dtSOMain.Rows[0]["cDefine14"].ToString(); if (dtSOMain.Rows[0]["cDefine15"].ToString() != "") { modelRD.cDefine15 = BaseFunction.ReturnLong(dtSOMain.Rows[0]["cDefine15"]); } if (dtSOMain.Rows[0]["cDefine16"].ToString() != "") { modelRD.cDefine16 = BaseFunction.ReturnDecimal(dtSOMain.Rows[0]["cDefine16"]); } //modelRD.gspcheck; //modelRD.ufts; //modelRD.iExchRate; //modelRD.cExch_Name; modelRD.bOMFirst = false; modelRD.bFromPreYear = false; modelRD.bIsLsQuery = false; modelRD.bIsComplement = 0; modelRD.iDiscountTaxType = 0; modelRD.ireturncount = 0; modelRD.iverifystate = 0; modelRD.iswfcontrolled = 0; //modelRD.cModifyPerson; //modelRD.dModifyDate; //modelRD.dnmaketime; //modelRD.dnmodifytime; modelRD.dnverifytime = dNow; modelRD.bredvouch = 0; //modelRD.iFlowId; //modelRD.cPZID; //modelRD.cSourceLs; //modelRD.cSourceCodeLs; modelRD.iPrintCount = 0; //modelRD.ctransflag; //modelRD.csysbarcode; //modelRD.cCurrentAuditor; DAL.RdRecord09 dalRD = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.RdRecord09(); sSQL = dalRD.Add(modelRD); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); lIDRDDetails += 1; Model.rdrecords09 modelsRD = new Model.rdrecords09(); modelsRD.AutoID = lIDRDDetails; modelsRD.ID = modelRD.ID; modelsRD.cInvCode = dtSOMain.Rows[0]["cInvCode"].ToString().Trim(); //if (dtSOMain.Rows[0]["iNum"].ToString().Trim() != "") //{ // modelsRD.iNum = BaseFunction.ReturnDecimal(dtSOMain.Rows[0]["iNum"]); //} modelsRD.iQuantity = dQTYtc; //modelsRD.iUnitCost; //modelsRD.iPrice; //modelsRD.iAPrice; //modelsRD.iPUnitCost; //modelsRD.iPPrice; modelsRD.cBatch = gridView1.GetRowCellValue(i, gridColcBatch).ToString().Trim(); //modelsRD.cVouchCode; //modelsRD.cInVouchCode; //modelsRD.cinvouchtype; //modelsRD.iSOutQuantity; //modelsRD.iSOutNum; //modelsRD.cFree1; //modelsRD.cFree2; modelsRD.iFlag = 0; //modelsRD.iFNum; //modelsRD.iFQuantity; //modelsRD.dVDate; //modelsRD.iTrIds; //modelsRD.cPosition; //modelsRD.cDefine22; //modelsRD.cDefine23; //modelsRD.cDefine24; //modelsRD.cDefine25; //modelsRD.cDefine26; //modelsRD.cDefine27; //modelsRD.cItem_class; //modelsRD.cItemCode; //modelsRD.cName; //modelsRD.cItemCName; //modelsRD.cFree3; //modelsRD.cFree4; //modelsRD.cFree5; //modelsRD.cFree6; //modelsRD.cFree7; //modelsRD.cFree8; //modelsRD.cFree9; //modelsRD.cFree10; //modelsRD.cBarCode; //modelsRD.iNQuantity; //modelsRD.iNNum; //modelsRD.cAssUnit; //modelsRD.dMadeDate; //modelsRD.iMassDate; //modelsRD.cDefine28; //modelsRD.cDefine29; //modelsRD.cDefine30; //modelsRD.cDefine31; //modelsRD.cDefine32; //modelsRD.cDefine33; //modelsRD.cDefine34; //modelsRD.cDefine35; //modelsRD.cDefine36; //modelsRD.cDefine37; //modelsRD.iCheckIds; //modelsRD.cBVencode; //modelsRD.chVencode; //modelsRD.bGsp; //modelsRD.cGspState; //modelsRD.cCheckCode; //modelsRD.iCheckIdBaks; //modelsRD.cRejectCode; //modelsRD.iRejectIds; //modelsRD.cCheckPersonCode; //modelsRD.dCheckDate; //modelsRD.cMassUnit; //modelsRD.bChecked; modelsRD.bLPUseFree = false; //modelsRD.iRSRowNO; //modelsRD.iOriTrackID; //modelsRD.coritracktype; //modelsRD.cbaccounter; //modelsRD.dbKeepDate; modelsRD.bCosting = true; modelsRD.bVMIUsed = false; //modelsRD.iVMISettleQuantity; //modelsRD.iVMISettleNum; //modelsRD.cvmivencode; //modelsRD.iInvSNCount; //modelsRD.cwhpersoncode; //modelsRD.cwhpersonname; //modelsRD.cserviceoid; //modelsRD.cbserviceoid; //modelsRD.iinvexchrate; //modelsRD.corufts; //modelsRD.strContractGUID; modelsRD.iExpiratDateCalcu = 0; //modelsRD.cExpirationdate; //modelsRD.dExpirationdate; //modelsRD.cciqbookcode; //modelsRD.iBondedSumQty; //modelsRD.iorderdid; modelsRD.iordertype = 0; //modelsRD.iordercode; //modelsRD.iorderseq; //modelsRD.isodid; modelsRD.isotype = 0; modelsRD.csocode = dtSOMain.Rows[0]["cSOCode"].ToString().Trim(); //modelsRD.isoseq //modelsRD.cBatchProperty1; //modelsRD.cBatchProperty2; //modelsRD.cBatchProperty3; //modelsRD.cBatchProperty4; //modelsRD.cBatchProperty5; //modelsRD.cBatchProperty6; //modelsRD.cBatchProperty7; //modelsRD.cBatchProperty8; //modelsRD.cBatchProperty9; //modelsRD.cBatchProperty10; //modelsRD.cbMemo; modelsRD.irowno = 1; //modelsRD.strowguid; //modelsRD.rowufts; //modelsRD.ipreuseqty; //modelsRD.ipreuseinum; //modelsRD.cbsourcecodels; //modelsRD.iGroupNO; //modelsRD.iDebitIDs; //modelsRD.idebitchildids; //modelsRD.OutCopiedQuantity; //modelsRD.cbsysbarcode DAL.rdrecords09 dalsRD = new DAL.rdrecords09(); sSQL = dalsRD.Add(modelsRD); iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" if exists (select * from CurrentStock where cInvCode = '@cInvCode' and cWhCode = '@cWhCode' and isnull(cFree1,'') = @cFree1 and isnull(cFree2,'') = @cFree2 and isnull(cFree3,'') = @cFree3 and isnull(cFree4,'') = @cFree4 and isnull(cFree5,'') = @cFree5 and isnull(cFree6,'') = @cFree6 and isnull(cFree7,'') = @cFree7 and isnull(cFree8,'') = @cFree8 and isnull(cFree9,'') = @cFree9 and isnull(cFree10,'') = @cFree10 and isnull(cBatch,'') = @cBatch ) update CurrentStock set iQuantity = isnull(iQuantity,0) + @iQuantity where cInvCode = '@cInvCode' and cWhCode = '@cWhCode' and isnull(cFree1,'') = @cFree1 and isnull(cFree2,'') = @cFree2 and isnull(cFree3,'') = @cFree3 and isnull(cFree4,'') = @cFree4 and isnull(cFree5,'') = @cFree5 and isnull(cFree6,'') = @cFree6 and isnull(cFree7,'') = @cFree7 and isnull(cFree8,'') = @cFree8 and isnull(cFree9,'') = @cFree9 and isnull(cFree10,'') = @cFree10 and isnull(cBatch,'') = @cBatch else begin declare @itemid varchar(20); declare @iCount int; select @iCount=count(itemid) from CurrentStock where cInvCode = '@cInvCode'; if( @iCount > 0 ) select @itemid=itemid from CurrentStock where cInvCode = '@cInvCode'; else select @itemid=max(itemid+1) from CurrentStock insert into CurrentStock(cWhCode,cInvCode,iQuantity,itemid, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10,cBatch,iSoDid) values('@cWhCode','@cInvCode', @iQuantity,@itemid, @cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10,@cBatch,'') end "; sSQL = sSQL.Replace("@cInvCode", modelsRD.cInvCode); sSQL = sSQL.Replace("@cWhCode", modelRD.cWhCode); sSQL = sSQL.Replace("@iQuantity", (-1 * modelsRD.iQuantity).ToString()); sSQL = sSQL.Replace("@iNum", (-1 * modelsRD.iNum).ToString()); sSQL = sSQL.Replace("@cFree10", modelsRD.cFree10 == null ? "''" : "'" + modelsRD.cFree10 + "'"); sSQL = sSQL.Replace("@cFree1", modelsRD.cFree1 == null ? "''" : "'" + modelsRD.cFree1 + "'"); sSQL = sSQL.Replace("@cFree2", modelsRD.cFree2 == null ? "''" : "'" + modelsRD.cFree2 + "'"); sSQL = sSQL.Replace("@cFree3", modelsRD.cFree3 == null ? "''" : "'" + modelsRD.cFree3 + "'"); sSQL = sSQL.Replace("@cFree4", modelsRD.cFree4 == null ? "''" : "'" + modelsRD.cFree4 + "'"); sSQL = sSQL.Replace("@cFree5", modelsRD.cFree5 == null ? "''" : "'" + modelsRD.cFree5 + "'"); sSQL = sSQL.Replace("@cFree6", modelsRD.cFree6 == null ? "''" : "'" + modelsRD.cFree6 + "'"); sSQL = sSQL.Replace("@cFree7", modelsRD.cFree7 == null ? "''" : "'" + modelsRD.cFree7 + "'"); sSQL = sSQL.Replace("@cFree8", modelsRD.cFree8 == null ? "''" : "'" + modelsRD.cFree8 + "'"); sSQL = sSQL.Replace("@cFree9", modelsRD.cFree9 == null ? "''" : "'" + modelsRD.cFree9 + "'"); sSQL = sSQL.Replace("@cBatch", modelsRD.cBatch == null ? "''" : "'" + modelsRD.cBatch + "'"); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" exec ST_SaveForStock N'09',N'aaaaaa',1,0 ,1 "; sSQL = sSQL.Replace("aaaaaa", lIDRD.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" exec ST_SaveForTrackStock N'09',N'aaaaaa', 0 ,1 "; sSQL = sSQL.Replace("aaaaaa", lIDRD.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" exec IA_SP_WriteUnAccountVouchForST 'aaaaaa',N'09' "; sSQL = sSQL.Replace("aaaaaa", lIDRD.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); if (lIDRD > 1000000000) { lIDRD = lIDRD - 1000000000; } if (lIDRDDetails > 1000000000) { lIDRDDetails = lIDRDDetails - 1000000000; } sSQL = "update UFSystem..UA_Identity set iFatherId = " + lIDRD.ToString() + ",iChildId = " + lIDRDDetails + " where cAcc_Id = '" + sAccID + "' and cVouchType = 'rd'"; DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" if exists(select * from VoucherHistory where CardNumber = '0302' and cSeed = 'aaaaaa' AND cContentRule = 'YYYY') update VoucherHistory set cNumber = bbbbbb where CardNumber = '0302' and cSeed = 'aaaaaa' AND cContentRule = 'YYYY' else insert into VoucherHistory(CardNumber,cContent,cContentRule,cSeed,cNumber,bEmpty) values('0302','日期','YYYY','aaaaaa','bbbbbb',0) "; sSQL = sSQL.Replace("aaaaaa", dDate.ToString("yyyy")); sSQL = sSQL.Replace("bbbbbb", lCodeRD.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); #endregion //------------------------------------------------------------------------ } #endregion if (sErr.Length > 0) { throw new Exception(sErr); } 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) { MessageBox.Show(ee.Message); } }
//U8V11.1 public int TransVouch_Audit_U8V111(SqlTransaction tran, string sCode, string sAccID, string sUserName) { string sErr = ""; int iCou = 0; 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")); sSQL = @" SELECT * FROM dbo.TransVouch a INNER JOIN dbo.TransVouchs b ON a.ID = b.ID WHERE a.cTVCode = 'aaaaaaaa' "; sSQL = sSQL.Replace("aaaaaaaa", sCode); DataTable dtTr = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; if (dtTr == null || dtTr.Rows.Count == 0) { throw new Exception("Audit err"); } DateTime dtmCode = BaseFunction.ReturnDate(dtTr.Rows[0]["dVerifyDate"]); //审核调拨单 sSQL = @"UPDATE TransVouch SET cVerifyPerson = 'aaaaaa',dVerifyDate = 'bbbbbb',dnverifytime = 'cccccc' WHERE cTVCode = 'dddddd'"; sSQL = sSQL.Replace("aaaaaa", sUserName); sSQL = sSQL.Replace("bbbbbb", dNow.ToString("yyyy-MM-dd")); sSQL = sSQL.Replace("cccccc", dNow.ToString("yyyy-MM-dd HH:mm:ss")); sSQL = sSQL.Replace("dddddd", sCode); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); 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'rd',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); 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]) - 1; #region 其他入库单 //获得单据号 sSQL = "select cNumber from VoucherHistory with (ROWLOCK) WHERE (CardNumber = '0301') AND (cContent = '日期') AND (cContentRule = 'YYYY') AND (cSeed = 'aaaaaa') ORDER BY cNumber DESC"; sSQL = sSQL.Replace("aaaaaa", dtmCode.ToString("yyyy")); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; long lRd08Code = 0; if (dt != null && dt.Rows.Count > 0) { lRd08Code = BaseFunction.ReturnLong(dt.Rows[0]["cNumber"]); } else { lRd08Code = 0; } lRd08Code += 1; string sRd08Code = lRd08Code.ToString(); Model.RdRecord08 modRd08 = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.RdRecord08(); lID += 1; modRd08.ID = lID; modRd08.bRdFlag = 1; modRd08.cVouchType = "08"; modRd08.cBusType = "调拨入库"; modRd08.cSource = "调拨"; modRd08.cBusCode = sCode; if (dtTr.Rows[0]["cIWhCode"].ToString().Trim().ToString().Trim() == "") { throw new Exception("请设置转入仓库"); } modRd08.cWhCode = dtTr.Rows[0]["cIWhCode"].ToString().Trim(); modRd08.dDate = dNowDate; string sCodeRD = lRd08Code.ToString(); while (sCodeRD.Length < 6) { sCodeRD = "0" + sCodeRD; } modRd08.cCode = "MI" + dNowDate.Year.ToString() + sCodeRD; if (dtTr.Rows[0]["cIRdCode"].ToString().Trim() == "") { throw new Exception("请设置入库类别"); } modRd08.cRdCode = dtTr.Rows[0]["cIRdCode"].ToString().Trim(); if (dtTr.Rows[0]["cIDepCode"].ToString().Trim() != "") { modRd08.cDepCode = dtTr.Rows[0]["cIDepCode"].ToString().Trim(); } if (dtTr.Rows[0]["cPersonCode"].ToString().Trim() != "") { modRd08.cPersonCode = dtTr.Rows[0]["cPersonCode"].ToString().Trim(); } modRd08.bTransFlag = false; modRd08.cMaker = dtTr.Rows[0]["cVerifyPerson"].ToString().Trim(); modRd08.cHandler = dtTr.Rows[0]["cVerifyPerson"].ToString().Trim(); modRd08.dVeriDate = dNowDate; modRd08.bpufirst = false; modRd08.biafirst = false; modRd08.VT_ID = 67; modRd08.bIsSTQc = false; modRd08.bOMFirst = false; modRd08.bFromPreYear = false; modRd08.bIsComplement = 0; modRd08.iDiscountTaxType = 0; modRd08.ireturncount = 0; modRd08.iverifystate = 0; modRd08.iswfcontrolled = 0; modRd08.dnmaketime = dNow; modRd08.dnverifytime = dNow; modRd08.cSourceLs = "1"; modRd08.iPrintCount = 0; modRd08.csysbarcode = "||st08||" + modRd08.cCode; DAL.RdRecord08 dalrd08 = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.RdRecord08(); sSQL = dalrd08.Add(modRd08); iCou += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); for (int i = 0; i < dtTr.Rows.Count; i++) { lIDDetails += 1; Model.rdrecords08 modRds08 = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.rdrecords08(); modRds08.AutoID = lIDDetails; modRds08.ID = modRd08.ID; modRds08.cInvCode = dtTr.Rows[i]["cInvCode"].ToString().Trim(); if (BaseFunction.ReturnDecimal(dtTr.Rows[i]["iTVNum"]) != 0) { modRds08.iNum = BaseFunction.ReturnDecimal(dtTr.Rows[i]["iTVNum"]); } modRds08.iQuantity = BaseFunction.ReturnDecimal(dtTr.Rows[i]["iTVQuantity"]); modRds08.cBatch = dtTr.Rows[i]["cTVBatch"].ToString().Trim(); modRds08.cVouchCode = 0; modRds08.iSOutNum = 0; modRds08.iSOutQuantity = 0; modRds08.iFlag = 0; modRds08.iTrIds = BaseFunction.ReturnLong(dtTr.Rows[i]["autoID"]); modRds08.iNQuantity = modRds08.iQuantity; modRds08.iNNum = modRds08.iNNum; modRds08.cMassUnit = 0; modRds08.bCosting = true; modRds08.bVMIUsed = false; modRds08.iordertype = 0; modRds08.irowno = BaseFunction.ReturnInt(dtTr.Rows[i]["irowno"]); modRds08.cbsysbarcode = "||st08||" + modRd08.cCode + "|" + modRds08.irowno.ToString(); DAL.rdrecords08 dalRDs08 = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.rdrecords08(); sSQL = dalRDs08.Add(modRds08); iCou += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" if exists (select * from CurrentStock where cInvCode = '@cInvCode' and cWhCode = '@cWhCode' and isnull(cFree1,'') = @cFree1 and isnull(cFree2,'') = @cFree2 and isnull(cFree3,'') = @cFree3 and isnull(cFree4,'') = @cFree4 and isnull(cFree5,'') = @cFree5 and isnull(cFree6,'') = @cFree6 and isnull(cFree7,'') = @cFree7 and isnull(cFree8,'') = @cFree8 and isnull(cFree9,'') = @cFree9 and isnull(cFree10,'') = @cFree10 and isnull(cBatch,'') = @cBatch ) update CurrentStock set iQuantity = isnull(iQuantity,0) + @iQuantity where cInvCode = '@cInvCode' and cWhCode = '@cWhCode' and isnull(cFree1,'') = @cFree1 and isnull(cFree2,'') = @cFree2 and isnull(cFree3,'') = @cFree3 and isnull(cFree4,'') = @cFree4 and isnull(cFree5,'') = @cFree5 and isnull(cFree6,'') = @cFree6 and isnull(cFree7,'') = @cFree7 and isnull(cFree8,'') = @cFree8 and isnull(cFree9,'') = @cFree9 and isnull(cFree10,'') = @cFree10 and isnull(cBatch,'') = @cBatch else begin declare @itemid varchar(20); declare @iCount int; select @iCount=count(itemid) from CurrentStock where cInvCode = '@cInvCode'; if( @iCount > 0 ) select @itemid=itemid from CurrentStock where cInvCode = '@cInvCode'; else select @itemid=max(itemid+1) from CurrentStock insert into CurrentStock(cWhCode,cInvCode,iQuantity,itemid, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10,cBatch,iSoDid) values('@cWhCode','@cInvCode', @iQuantity,isnull(@itemid,1), @cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10,@cBatch,'') end "; sSQL = sSQL.Replace("@cInvCode", modRds08.cInvCode); sSQL = sSQL.Replace("@cWhCode", modRd08.cWhCode); sSQL = sSQL.Replace("@iQuantity", modRds08.iQuantity.ToString()); sSQL = sSQL.Replace("@iNum", modRds08.iNum.ToString()); sSQL = sSQL.Replace("@cFree10", modRds08.cFree10 == null ? "''" : "'" + modRds08.cFree10 + "'"); sSQL = sSQL.Replace("@cFree1", modRds08.cFree1 == null ? "''" : "'" + modRds08.cFree1 + "'"); sSQL = sSQL.Replace("@cFree2", modRds08.cFree2 == null ? "''" : "'" + modRds08.cFree2 + "'"); sSQL = sSQL.Replace("@cFree3", modRds08.cFree3 == null ? "''" : "'" + modRds08.cFree3 + "'"); sSQL = sSQL.Replace("@cFree4", modRds08.cFree4 == null ? "''" : "'" + modRds08.cFree4 + "'"); sSQL = sSQL.Replace("@cFree5", modRds08.cFree5 == null ? "''" : "'" + modRds08.cFree5 + "'"); sSQL = sSQL.Replace("@cFree6", modRds08.cFree6 == null ? "''" : "'" + modRds08.cFree6 + "'"); sSQL = sSQL.Replace("@cFree7", modRds08.cFree7 == null ? "''" : "'" + modRds08.cFree7 + "'"); sSQL = sSQL.Replace("@cFree8", modRds08.cFree8 == null ? "''" : "'" + modRds08.cFree8 + "'"); sSQL = sSQL.Replace("@cFree9", modRds08.cFree9 == null ? "''" : "'" + modRds08.cFree9 + "'"); sSQL = sSQL.Replace("@cBatch", modRds08.cBatch == null ? "''" : "'" + modRds08.cBatch + "'"); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } sSQL = @" exec ST_SaveForStock N'08',N'aaaaaa',1,0 ,1 "; sSQL = sSQL.Replace("aaaaaa", lID.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" exec ST_SaveForTrackStock N'08',N'aaaaaa', 0 ,1 "; sSQL = sSQL.Replace("aaaaaa", lID.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" exec IA_SP_WriteUnAccountVouchForST 'aaaaaa',N'08' "; sSQL = sSQL.Replace("aaaaaa", lID.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" if exists(select * from VoucherHistory where (CardNumber = '0301') AND (cContent = '日期') AND (cContentRule = 'YYYY') AND (cSeed = 'bbbbbb')) update VoucherHistory set cNumber = aaaaaa where (CardNumber = '0301') AND (cContent = '日期') AND (cContentRule = 'YYYY') AND (cSeed = 'bbbbbb') else insert into VoucherHistory(CardNumber,cContent,cContentRule,cSeed,cNumber,bEmpty) values('0301','日期','YYYY','bbbbbb','1',0) "; sSQL = sSQL.Replace("aaaaaa", lRd08Code.ToString()); sSQL = sSQL.Replace("bbbbbb", dNow.ToString("yyyy")); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); #endregion #region 其他出库单 //获得单据号 sSQL = "select cNumber from VoucherHistory with (ROWLOCK) WHERE (CardNumber = '0302') AND (cContent = '日期') AND (cContentRule = 'YYYY') AND (cSeed = 'aaaaaa') ORDER BY cNumber DESC"; sSQL = sSQL.Replace("aaaaaa", dtmCode.ToString("yyyy")); dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0]; long lRd09Code = 0; if (dt != null && dt.Rows.Count > 0) { lRd09Code = BaseFunction.ReturnLong(dt.Rows[0]["cNumber"]); } else { lRd09Code = 0; } lRd09Code += 1; string sRd09Code = lRd09Code.ToString(); Model.RdRecord09 modRd09 = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.RdRecord09(); lID += 1; modRd09.ID = lID; modRd09.bRdFlag = 0; modRd09.cVouchType = "09"; modRd09.cBusType = "调拨出库"; modRd09.cSource = "调拨"; modRd09.cBusCode = sCode; if (dtTr.Rows[0]["cOWhCode"].ToString().Trim().ToString().Trim() == "") { throw new Exception("请设置转出仓库"); } modRd09.cWhCode = dtTr.Rows[0]["cOWhCode"].ToString().Trim(); modRd09.dDate = dNowDate; string sCodeRD09 = lRd09Code.ToString(); while (sCodeRD09.Length < 6) { sCodeRD09 = "0" + sCodeRD09; } modRd09.cCode = "MR" + dNowDate.Year.ToString() + sCodeRD09; if (dtTr.Rows[0]["cORdCode"].ToString().Trim() == "") { throw new Exception("请设置出库类别"); } modRd09.cRdCode = dtTr.Rows[0]["cORdCode"].ToString().Trim(); if (dtTr.Rows[0]["cODepCode"].ToString().Trim() != "") { modRd09.cDepCode = dtTr.Rows[0]["cODepCode"].ToString().Trim(); } if (dtTr.Rows[0]["cPersonCode"].ToString().Trim() != "") { modRd09.cPersonCode = dtTr.Rows[0]["cPersonCode"].ToString().Trim(); } modRd09.bTransFlag = false; modRd09.cMaker = dtTr.Rows[0]["cVerifyPerson"].ToString().Trim(); modRd09.cHandler = dtTr.Rows[0]["cVerifyPerson"].ToString().Trim(); modRd09.dVeriDate = dNowDate; modRd09.bpufirst = false; modRd09.biafirst = false; modRd09.VT_ID = 85; modRd09.bIsSTQc = false; modRd09.bOMFirst = false; modRd09.bFromPreYear = false; modRd09.bIsComplement = 0; modRd09.iDiscountTaxType = 0; modRd09.ireturncount = 0; modRd09.iverifystate = 0; modRd09.iswfcontrolled = 0; modRd09.dnmaketime = dNow; modRd09.dnverifytime = dNow; modRd09.cSourceLs = "1"; modRd09.iPrintCount = 0; modRd09.csysbarcode = "||st09||" + modRd09.cCode; DAL.RdRecord09 dalrd09 = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.RdRecord09(); sSQL = dalrd09.Add(modRd09); iCou += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); for (int i = 0; i < dtTr.Rows.Count; i++) { lIDDetails += 1; Model.rdrecords09 modRds09 = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.rdrecords09(); modRds09.AutoID = lIDDetails; modRds09.ID = modRd09.ID; modRds09.cInvCode = dtTr.Rows[i]["cInvCode"].ToString().Trim(); if (BaseFunction.ReturnDecimal(dtTr.Rows[i]["iTVNum"]) != 0) { modRds09.iNum = BaseFunction.ReturnDecimal(dtTr.Rows[i]["iTVNum"]); } modRds09.iQuantity = BaseFunction.ReturnDecimal(dtTr.Rows[i]["iTVQuantity"]); modRds09.cBatch = dtTr.Rows[i]["cTVBatch"].ToString().Trim(); modRds09.cVouchCode = 0; modRds09.iSOutNum = 0; modRds09.iSOutQuantity = 0; modRds09.iFlag = 0; modRds09.iTrIds = BaseFunction.ReturnLong(dtTr.Rows[i]["autoID"]); modRds09.iNQuantity = modRds09.iQuantity; modRds09.iNNum = modRds09.iNNum; modRds09.cMassUnit = 0; modRds09.bCosting = true; modRds09.bVMIUsed = false; modRds09.iordertype = 0; modRds09.irowno = BaseFunction.ReturnInt(dtTr.Rows[i]["irowno"]); modRds09.cbsysbarcode = "||st09||" + modRd09.cCode + "|" + modRds09.irowno.ToString(); DAL.rdrecords09 dalRDs09 = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.rdrecords09(); sSQL = dalRDs09.Add(modRds09); iCou += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" if exists (select * from CurrentStock where cInvCode = '@cInvCode' and cWhCode = '@cWhCode' and isnull(cFree1,'') = @cFree1 and isnull(cFree2,'') = @cFree2 and isnull(cFree3,'') = @cFree3 and isnull(cFree4,'') = @cFree4 and isnull(cFree5,'') = @cFree5 and isnull(cFree6,'') = @cFree6 and isnull(cFree7,'') = @cFree7 and isnull(cFree8,'') = @cFree8 and isnull(cFree9,'') = @cFree9 and isnull(cFree10,'') = @cFree10 and isnull(cBatch,'') = @cBatch ) update CurrentStock set iQuantity = isnull(iQuantity,0) - @iQuantity where cInvCode = '@cInvCode' and cWhCode = '@cWhCode' and isnull(cFree1,'') = @cFree1 and isnull(cFree2,'') = @cFree2 and isnull(cFree3,'') = @cFree3 and isnull(cFree4,'') = @cFree4 and isnull(cFree5,'') = @cFree5 and isnull(cFree6,'') = @cFree6 and isnull(cFree7,'') = @cFree7 and isnull(cFree8,'') = @cFree8 and isnull(cFree9,'') = @cFree9 and isnull(cFree10,'') = @cFree10 and isnull(cBatch,'') = @cBatch else begin declare @itemid varchar(20); declare @iCount int; select @iCount=count(itemid) from CurrentStock where cInvCode = '@cInvCode'; if( @iCount > 0 ) select @itemid=itemid from CurrentStock where cInvCode = '@cInvCode'; else select @itemid=max(itemid+1) from CurrentStock insert into CurrentStock(cWhCode,cInvCode,iQuantity,itemid, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10,cBatch,iSoDid) values('@cWhCode','@cInvCode', @iQuantity,isnull(@itemid,1), @cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10,@cBatch,'') end "; sSQL = sSQL.Replace("@cInvCode", modRds09.cInvCode); sSQL = sSQL.Replace("@cWhCode", modRd09.cWhCode); sSQL = sSQL.Replace("@iQuantity", modRds09.iQuantity.ToString()); sSQL = sSQL.Replace("@iNum", modRds09.iNum.ToString()); sSQL = sSQL.Replace("@cFree10", modRds09.cFree10 == null ? "''" : "'" + modRds09.cFree10 + "'"); sSQL = sSQL.Replace("@cFree1", modRds09.cFree1 == null ? "''" : "'" + modRds09.cFree1 + "'"); sSQL = sSQL.Replace("@cFree2", modRds09.cFree2 == null ? "''" : "'" + modRds09.cFree2 + "'"); sSQL = sSQL.Replace("@cFree3", modRds09.cFree3 == null ? "''" : "'" + modRds09.cFree3 + "'"); sSQL = sSQL.Replace("@cFree4", modRds09.cFree4 == null ? "''" : "'" + modRds09.cFree4 + "'"); sSQL = sSQL.Replace("@cFree5", modRds09.cFree5 == null ? "''" : "'" + modRds09.cFree5 + "'"); sSQL = sSQL.Replace("@cFree6", modRds09.cFree6 == null ? "''" : "'" + modRds09.cFree6 + "'"); sSQL = sSQL.Replace("@cFree7", modRds09.cFree7 == null ? "''" : "'" + modRds09.cFree7 + "'"); sSQL = sSQL.Replace("@cFree8", modRds09.cFree8 == null ? "''" : "'" + modRds09.cFree8 + "'"); sSQL = sSQL.Replace("@cFree9", modRds09.cFree9 == null ? "''" : "'" + modRds09.cFree9 + "'"); sSQL = sSQL.Replace("@cBatch", modRds09.cBatch == null ? "''" : "'" + modRds09.cBatch + "'"); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); } sSQL = @" exec ST_SaveForStock N'09',N'aaaaaa',1,0 ,1 "; sSQL = sSQL.Replace("aaaaaa", lID.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" exec ST_SaveForTrackStock N'09',N'aaaaaa', 0 ,1 "; sSQL = sSQL.Replace("aaaaaa", lID.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" exec IA_SP_WriteUnAccountVouchForST 'aaaaaa',N'09' "; sSQL = sSQL.Replace("aaaaaa", lID.ToString()); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); 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 = 'rd'"; DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); sSQL = @" if exists(select * from VoucherHistory where (CardNumber = '0302') AND (cContent = '日期') AND (cContentRule = 'YYYY') AND (cSeed = 'bbbbbb')) update VoucherHistory set cNumber = aaaaaa where (CardNumber = '0302') AND (cContent = '日期') AND (cContentRule = 'YYYY') AND (cSeed = 'bbbbbb') else insert into VoucherHistory(CardNumber,cContent,cContentRule,cSeed,cNumber,bEmpty) values('0302','日期','YYYY','bbbbbb','1',0) "; sSQL = sSQL.Replace("aaaaaa", lRd09Code.ToString()); sSQL = sSQL.Replace("bbbbbb", dNow.ToString("yyyy")); DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL); #endregion return(iCou); }