public string Save材料出库单(DataTable dtData)
{
string s = "";
try
{
string sErr = "";
if (dtData == null || dtData.Rows.Count < 1)
{
throw new Exception("没有需要保存的数据");
}
SqlConnection conn = new SqlConnection(ClsBaseClass.ClsBaseDataInfo.sConnString);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSQL = "select getdate()";
DateTime d当前服务器时间 = Convert.ToDateTime(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
//1. 判断是否结账
sSQL = "select * from gl_mend where iyear=year(getdate()) and iperiod=month(getdate())";
DataTable dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp == null || dtTemp.Rows.Count < 1)
{
throw new Exception("判断模块结账失败");
}
int iR = ClsBaseDataInfo.ReturnObjectToInt(dtTemp.Rows[0]["bflag_ST"]);
if (iR == 1)
{
throw new Exception("当前月份已经结账");
}
//2. 获得单据ID
long lID = 1;
long lIDDetail = 1;
ClsU8基础档案 cls = new ClsU8基础档案();
cls.GetRdID(out lID, out lIDDetail, ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3));
//3. 获得单据号
long iCode = 0;
sSQL = "select * From VoucherHistory with (ROWLOCK) Where CardNumber='0412'";
dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp == null || dtTemp.Rows.Count < 1)
{
iCode = 1;
}
else
{
iCode = ClsBaseDataInfo.ReturnObjectToLong(dtTemp.Rows[0]["cNumber"]);
}
sSQL = "select * from mom_order a inner join mom_orderdetail b on a.moid = b.moid where a.mocode = '" + dtData.Rows[0]["单据号"].ToString().Trim() + "' and b.SortSeq = '" + dtData.Rows[0]["行号"].ToString().Trim() + "'";
DataTable dt生产订单表头 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt生产订单表头 == null || dt生产订单表头.Rows.Count < 1)
{
throw new Exception("获得生产订单信息失败");
}
//4. 组装表头
lID += 1;
string s仓库 = dtData.Rows[0]["仓库"].ToString().Trim();
string s订单号 = dtData.Rows[0]["单据号"].ToString().Trim();
string s行号 = dtData.Rows[0]["行号"].ToString().Trim();
string s制单人 = dtData.Rows[0]["制单人"].ToString().Trim();
string s产量 = dt生产订单表头.Rows[0]["Qty"].ToString().Trim();
string s产品编码 = dt生产订单表头.Rows[0]["InvCode"].ToString().Trim();
string s生产订单主表标志 = dt生产订单表头.Rows[0]["MoId"].ToString().Trim();
iCode += 1;
string s单据号 = sGetCode(iCode, 10);
sSQL = " insert into rdrecord11(id,brdflag,cvouchtype,cbustype,csource,cwhcode,ddate,ccode,crdcode,cdepcode " +
",cmaker,imquantity,vt_id,cpspcode,cmpocode,iproorderid,bomfirst,biscomplement,iswfcontrolled,dnmaketime " +
",dnmodifytime,dnverifytime,bmotran,cdefine1) " +
"values (N'" + lID + "',N'0',N'11',N'领料',N'生产订单',N'" + s仓库 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',N'" + s单据号 + "',N'21',N'05' " +
",N'" + s制单人 + "'," + s产量 + ",65,N'" + s产品编码 + "',N'" + s订单号 + "'," + s生产订单主表标志 + ",0,0,0, getdate()" +
", Null , Null ,N'0','" + s订单号 + "' )";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
for (int i = 0; i < dtData.Rows.Count; i++)
{
//判断条码是否已经使用
sSQL = "select * from rdrecords11 where BarCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + " ";
int iCou = ClsBaseDataInfo.ReturnObjectToInt(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
if (iCou > 0)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "已经使用\n";
continue;
}
sSQL = "select a.moid, b.MoDId ,c.AllocateId ,c.InvCode ,c.Qty ,c.IssQty from mom_order a inner join mom_orderdetail b on a.moid = b.moid inner join mom_moallocate c on c.MoDId = b.Modid where a.mocode = '" + s订单号 + "' and b.SortSeq = '" + s行号 + "' and c.InvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "' and isnull(LotNo,'') = '" + dtData.Rows[i]["批号"].ToString().Trim().Substring(0, dtData.Rows[i]["批号"].ToString().Trim().Length - 3) + "'";
DataTable dt生产订单信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt生产订单信息 == null || dt生产订单信息.Rows.Count < 1)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得生产订单材料信息失败，请核实材料或批号是否匹配\n";
continue;
}
decimal d应领数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单信息.Rows[0]["Qty"]);
decimal d已领数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单信息.Rows[0]["IssQty"]);
decimal d数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"], 6);
if (d应领数量 * (decimal)1.2 < d已领数量 + d数量)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + " 超订单领料\n";
continue;
}
sSQL = "select * from Inventory where cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "'";
DataTable dt存货信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt存货信息 == null || dt存货信息.Rows.Count < 1)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得存货信息失败\n";
continue;
}
int i是否批次 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bInvBatch"]);
if (i是否批次 != 0 && dtData.Rows[i]["批号"].ToString().Trim() == "")
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "是批次管理物料，必须输入批号\n";
continue;
}
int i是否自由项1 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bFree1"]);
if (i是否自由项1 != 0 && dtData.Rows[i]["长度"].ToString().Trim() == "")
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "必须输入长度\n";
continue;
}
//组装表体
sSQL = "select * from CurrentStock where cWhCode = '" + dtData.Rows[i]["仓库"].ToString().Trim() + "' and cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "' and isnull(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
DataTable dt现存量 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
lIDDetail += 1;
string s存货编码 = dtData.Rows[i]["存货编码"].ToString().Trim();
//decimal d数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"], 6);
decimal d件数 = 0;
decimal d现存数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt现存量.Rows[0]["iQuantity"], 6);
decimal d现存件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt现存量.Rows[0]["iNum"], 6);
if (d现存件数 > 0)
{
if (d数量 == d现存数量)
{
d件数 = d现存件数;
}
else
{
d件数 = ClsBaseDataInfo.ReturnObjectToDecimal(d数量 * d现存件数 / d现存数量, 6);
}
}
decimal d生产订单子件数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单信息.Rows[0]["Qty"], 6);
decimal d生产订单子件已领数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单信息.Rows[0]["IssQty"], 6);
decimal d生产订单待领数量 = d生产订单子件数量 - d生产订单子件已领数量;
//if (d生产订单待领数量 < d数量)
//{
// sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "超订单领料\n";
// continue;
//}
sSQL = "select * from Warehouse where cWhCode = '" + s仓库 + "'";
DataTable dtWH = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
sSQL = "Insert Into rdrecords11(autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,ipunitcost,ipprice,cbatch " +
" ,cobjcode,cvouchcode,cinvouchcode,cinvouchtype,isoutquantity,isoutnum,coutvouchid,coutvouchtype,isredoutquantity,isredoutnum "+
" ,cfree1,cfree2,isquantity,ifnum,ifquantity,dvdate,itrids,cposition,cdefine22,cdefine23 "+
" ,cdefine24,cdefine25,cdefine26,cdefine27,citem_class,citemcode,cname,citemcname,cfree3,cfree4 "+
" ,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,BarCode,inquantity,innum,cassunit "+
" ,dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35 "+
" ,cdefine36,cdefine37,impoids,icheckids,cbvencode,crejectcode,cmassunit,cmolotcode,imaterialfee,dmsdate "+
" ,ismaterialfee,iomodid,iomomid,cmworkcentercode,irsrowno,cbaccounter,dbkeepdate,bcosting,bvmiused,ivmisettlequantity "+
" ,ivmisettlenum,cvmivencode,iinvsncount,cwhpersoncode,cwhpersonname,imaids,iinvexchrate,corufts,comcode,cmocode "+
" ,invcode,imoseq,iopseq,copdesc,iexpiratdatecalcu,cexpirationdate,dexpirationdate,cciqbookcode,ibondedsumqty,productinids "+
" ,iorderdid,iordertype,iordercode,iorderseq,isodid,isotype,csocode,isoseq,ipesodid,ipesotype "+
" ,cpesocode,ipesoseq,cbatchproperty1,cbatchproperty2,cbatchproperty3,cbatchproperty4,cbatchproperty5,cbatchproperty6,cbatchproperty7,cbatchproperty8 "+
" ,cbatchproperty9,cbatchproperty10,cbmemo,applydid,applycode,irowno,strowguid,cservicecode) "+
"Values (" + lIDDetail + "," + lID + ",N'" + s存货编码 + "'," + d件数 + "," + d数量 + ",Null,Null,Null,Null,N'" + dtData.Rows[i]["批号"] + "' " +
" ,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null "+
" ,"+ ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString().Trim()) + ",Null,Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ",Null,Null " +
" ,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null "+
" ,Null,Null,Null,Null,Null,Null,'"+ dtData.Rows[i]["条形码"].ToString() + "'," + d数量 + "," + d件数 + ",N'" + dt存货信息.Rows[0]["cAssComUnitCode"] + "' " +
" ,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null "+
" ,Null,Null,"+ ClsBaseDataInfo.ReturnCol(dt生产订单信息.Rows[0]["AllocateId"].ToString()) + ",Null,Null,Null,Null,Null,Null,Null " +
" ,Null,Null,Null,Null,Null,Null,Null,1,0,Null "+
" ,Null,Null,Null,Null,Null,Null,1,N'13497.1105',Null,N'"+ s订单号 + "' " +
" ,N'"+ s产品编码 + "'," + dtData.Rows[i]["行号"].ToString().Trim() + ",N'0000',Null,0,Null,Null,Null,Null,Null " +
" ,"+ dt生产订单信息.Rows[0]["Modid"] + ",1,N'" + s订单号 + "',1,Null,0,Null,Null," + dt生产订单信息.Rows[0]["Modid"] + ",7 " +
" ,'"+ s订单号 + "'," + dtData.Rows[i]["行号"].ToString().Trim() + ",Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["炉号"].ToString().Trim()) + ",Null,Null " +
" ,Null,Null,Null,Null,Null,1,Null,Null)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//更新现存量
sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
DataTable dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
long lItemID = 0;
if (dtTemp2 == null || dtTemp2.Rows.Count == 0)
{
sSQL = "insert into SCM_Item(cInvCode,cFree1,PartId) " +
"values('" + s存货编码 + "','" + dtData.Rows[i]["长度"].ToString().Trim() + "',0)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
}
else
{
lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
}
sSQL = "declare @itmeid int " +
"select @itmeid = MAX(ItemId) + 1 from CurrentStock " +
"if exists(select * from CurrentStock where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = isnull(" + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",'') and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "') " +
" update CurrentStock set iQuantity = isnull(iQuantity,0) - " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = isnull(iNum,0) - " + ClsBaseDataInfo.ReturnCol(d件数) + " where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = isnull(" + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",'') and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' " +
"else " +
" insert into CurrentStock(cWhCode,cInvCode,cBatch,iSoType,iQuantity,iNum,ItemId,cFree1) " +
" values('" + s仓库 + "', '" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",0,-" + ClsBaseDataInfo.ReturnCol(d数量) + ",-" + ClsBaseDataInfo.ReturnCol(d件数) + "," + lItemID + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ")";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update mom_moallocate set IssQty = isnull(IssQty ,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + " where AllocateId = " + ClsBaseDataInfo.ReturnCol(dt生产订单信息.Rows[0]["AllocateId"].ToString());
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (Convert.ToInt32(dtWH.Rows[0]["bWhPos"]) != 0)
{
//货位登记
sSQL = "insert into InvPosition( RdsID, RdID, cWhCode, cPosCode, cInvCode, cBatch, cFree1, cFree2, dVDate, iQuantity" +
", iNum, cMemo, cHandler, dDate, bRdFlag, cSource, cFree3, cFree4, cFree5, cFree6" +
", cFree7, cFree8, cFree9, cFree10, cAssUnit, cBVencode, iTrackId, dMadeDate, iMassDate" +
", cMassUnit, cvmivencode, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cvouchtype, cInVouchType, cVerifier, dVeriDate, dVouchDate) " +
"values(" + lIDDetail + "," + lID + ",'" + s仓库 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ",'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",null,null," + ClsBaseDataInfo.ReturnCol(d数量) + " " +
"," + ClsBaseDataInfo.ReturnCol(d件数) + ",null,'" + s制单人 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',0,null,null,null,null,null" +
",null,null,null,null," + ClsBaseDataInfo.ReturnCol(dt存货信息.Rows[0]["cAssComUnitCode"].ToString()) + ",null,0,null,null" +
",null,null,0,null,null,'11','',null,null,'" + d当前服务器时间.ToString("yyyy-MM-dd") + "')";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "if exists( select * from InvPositionSum where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "') " +
" update InvPositionSum set iQuantity = iQuantity - " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = iNum - " + ClsBaseDataInfo.ReturnCol(d件数) + " where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().ToString() + "' " +
"else " +
"insert InvPositionSum(cWhCode, cPosCode, cInvCode, iQuantity, inum, cBatch, cFree1, cFree2, cFree3" +
" , cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrackid, cvmivencode, cMassUnit" +
", iMassDate, dMadeDate, dVDate, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cInVouchType) " +
"values( '" + s仓库 + "', " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ", '" + s存货编码 + "', -1 * " + ClsBaseDataInfo.ReturnCol(d数量) + ", -1 * isnull(" + ClsBaseDataInfo.ReturnCol(d件数) + ",0), " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ", null, null" +
" , null, null, null, null, null, null, null, 0, null, null" +
", null, null, null, 0, null, null, null)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
//7. 更新历史单据号表
sSQL = "update VoucherHistory set cNumber='" + iCode + "' Where CardNumber='0412' and cContent is NULL";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//8. 登记未记账
sSQL = @"exec IA_SP_WriteUnAccountVouchForST aaaaaa,N'11'";
sSQL = sSQL.Replace("aaaaaa", lID.ToString());
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//9. 更新单据ID号表
string s1 = lID.ToString().Trim();
string s2 = lIDDetail.ToString().Trim();
s1 = s1.Substring(1);
s2 = s2.Substring(1);
lID = Convert.ToInt64(s1);
lIDDetail = Convert.ToInt64(s2);
sSQL = "update UFSystem..UA_Identity set iFatherID = " + lID + ",iChildID = " + lIDDetail + " where cAcc_Id = '" + ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3) + "' and cVouchType = 'rd'";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (sErr.Trim().Length > 0)
{
throw new Exception(sErr);
}
tran.Commit();
s = "生成单据号：" + s单据号;
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
s = ee.Message;
}
return(s);
}
public string Save销售出库单(DataTable dtData)
{
string s = "";
try
{
string sErr = "";
if (dtData == null || dtData.Rows.Count < 1)
{
throw new Exception("没有需要保存的数据");
}
SqlConnection conn = new SqlConnection(ClsBaseClass.ClsBaseDataInfo.sConnString);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSQL = "select getdate()";
DateTime d当前服务器时间 = Convert.ToDateTime(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
//1. 判断是否结账
sSQL = "select * from gl_mend where iyear=year(getdate()) and iperiod=month(getdate())";
DataTable dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp == null || dtTemp.Rows.Count < 1)
{
throw new Exception("判断模块结账失败");
}
int iR = ClsBaseDataInfo.ReturnObjectToInt(dtTemp.Rows[0]["bflag_ST"]);
if (iR == 1)
{
throw new Exception("当前月份已经结账");
}
//2. 获得单据ID
long lID = 1;
long lIDDetail = 1;
ClsU8基础档案 cls = new ClsU8基础档案();
cls.GetRdID(out lID, out lIDDetail, ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3));
//3. 获得单据号
long iCode = 0;
sSQL = "select * From VoucherHistory with (ROWLOCK) Where CardNumber='0303'";
dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp == null || dtTemp.Rows.Count < 1)
{
iCode = 1;
}
else
{
iCode = ClsBaseDataInfo.ReturnObjectToLong(dtTemp.Rows[0]["cNumber"]);
}
//4. 组装表头
lID += 1;
string s仓库 = dtData.Rows[0]["仓库"].ToString().Trim();
string s订单号 = dtData.Rows[0]["单据号"].ToString().Trim();
string s制单人 = dtData.Rows[0]["制单人"].ToString().Trim();
//string s产量 = dt生产订单表头.Rows[0]["Qty"].ToString().Trim();
//string s产品编码 = dt生产订单表头.Rows[0]["InvCode"].ToString().Trim();
//string s生产订单主表标志 = dt生产订单表头.Rows[0]["MoId"].ToString().Trim();
iCode += 1;
string s单据号 = sGetCode(iCode, 10);
sSQL = "select * from DispatchList where cDLCode = '" + s订单号 + "' and isnull(bReturnFlag,0) = 0 ";
DataTable dt发货单 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt发货单 == null || dt发货单.Rows.Count < 1)
{
throw new Exception("获得发货单信息失败");
}
sSQL = "insert into rdrecord32(id,brdflag,cvouchtype,cbustype,csource,cbuscode,cwhcode,ddate,ccode,crdcode" +
",cdepcode,cpersoncode,cstcode,ccuscode,cdlcode,cmaker,vt_id,iswfcontrolled,dnmaketime,dnmodifytime" +
",dnverifytime) " +
"values (N'" + lID + "',N'0',N'32',N'普通销售',N'发货单',N'" + s订单号 + "',N'" + s仓库 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',N'" + s单据号 + "',N'24'" +
",N'" + dt发货单.Rows[0]["cDepCode"] + "',N'" + dt发货单.Rows[0]["cPersonCode"] + "',N'" + dt发货单.Rows[0]["cstcode"] + "',N'" + dt发货单.Rows[0]["cCusCode"] + "',N'" + dt发货单.Rows[0]["DLID"] + "',N'" + s制单人 + "',87,0, getdate(), Null " +
", Null )";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
for (int i = 0; i < dtData.Rows.Count; i++)
{
//判断条码是否已经使用
sSQL = "select * from rdrecords32 where cdefine28 = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + " ";
int iCou = ClsBaseDataInfo.ReturnObjectToInt(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
if (iCou > 0)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "已经使用\n";
continue;
}
sSQL = "select * from DispatchList a inner join DispatchLists b on a.DLID = b.DLID where cDLCode = '" + s订单号 + "' and isnull(a.bReturnFlag,0) = 0 and cinvcode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "' and isnull(b.cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "'";
DataTable dt发货单信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt发货单信息 == null || dt发货单信息.Rows.Count < 1)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得发货单信息失败\n";
continue;
}
sSQL = "select * from Inventory where cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "'";
DataTable dt存货信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt存货信息 == null || dt存货信息.Rows.Count < 1)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得存货信息失败\n";
continue;
}
int i是否批次 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bInvBatch"]);
if (i是否批次 != 0 && dtData.Rows[i]["批号"].ToString().Trim() == "")
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "是批次管理物料，必须输入批号\n";
continue;
}
int i是否自由项1 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bFree1"]);
if (i是否自由项1 != 0 && dtData.Rows[i]["长度"].ToString().Trim() == "")
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "必须输入长度\n";
continue;
}
//组装表体
sSQL = "select * from CurrentStock where cWhCode = '" + dtData.Rows[i]["仓库"].ToString().Trim() + "' and cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "' and isnull(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
DataTable dt现存量 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
lIDDetail += 1;
string s存货编码 = dtData.Rows[i]["存货编码"].ToString().Trim();
decimal d数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"], 6);
decimal d件数 = 0;
decimal d现存数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt现存量.Rows[0]["iQuantity"], 6);
decimal d现存件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt现存量.Rows[0]["iNum"], 6);
decimal d发货单数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[0]["iQuantity"]);
decimal d发货单件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[0]["iNum"]);
//if (d发货单件数 > 0)
//{
// d件数 = ClsBaseDataInfo.ReturnObjectToDecimal(d数量 * d发货单件数 / d发货单数量, 6);
//}
d件数 = 1;
decimal d累计出库数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[0]["fOutQuantity"]);
decimal d累计出库件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[0]["fOutNum"]);
//if (d发货单数量 - d累计出库数量 < d数量)
//{
// sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "超单据发货\n";
// continue;
//}
sSQL = "Insert Into rdrecords32(autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,ipunitcost,ipprice,cbatch" +
",cvouchcode,cinvouchcode,cinvouchtype,isoutquantity,isoutnum,coutvouchid,coutvouchtype,isredoutquantity,isredoutnum,cfree1" +
",cfree2,dvdate,cposition,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27,citem_class" +
",citemcode,idlsid,isbsid,isendquantity,isendnum,iensid,cname,citemcname,cfree3,cfree4" +
",cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,BarCode,inquantity,innum,cassunit" +
",dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35" +
",cdefine36,cdefine37,icheckids,cbvencode,bgsp,cgspstate,cmassunit,irefundinspectflag,strcontractid,strcode" +
",bchecked,cbaccounter,dbkeepdate,bcosting,bvmiused,ivmisettlequantity,ivmisettlenum,cvmivencode,iinvsncount,iinvexchrate" +
",cbdlcode,corufts,strcontractguid,iexpiratdatecalcu,cexpirationdate,dexpirationdate,cciqbookcode,ibondedsumqty,ccusinvcode,ccusinvname" +
",iorderdid,iordertype,iordercode,iorderseq,ipesodid,ipesotype,cpesocode,ipesoseq,isodid,isotype" +
",csocode,isoseq,cbatchproperty1,cbatchproperty2,cbatchproperty3,cbatchproperty4,cbatchproperty5,cbatchproperty6,cbatchproperty7,cbatchproperty8" +
",cbatchproperty9,cbatchproperty10,cbmemo,irowno,strowguid) " +
"Values (" + lIDDetail + "," + lID + ",N'" + s存货编码 + "'," + d件数 + "," + d数量 + ",Null,Null,Null,Null,N'" + dtData.Rows[i]["批号"] + "'" +
",Null,Null,Null,Null,Null,Null,Null,Null,Null,N'" + dtData.Rows[i]["长度"] + "'" +
",Null,Null,N'" + dtData.Rows[i]["货位"].ToString() + "',null,'" + dt发货单信息.Rows[0]["cdefine23"].ToString().Trim() + "','" + dt发货单信息.Rows[0]["cdefine24"].ToString().Trim() + "','" + dt发货单信息.Rows[0]["cdefine25"].ToString().Trim() + "',Null,Null,Null" +
",Null," + dt发货单信息.Rows[0]["iDLsID"] + ",Null,Null,Null,Null,Null,Null,Null,Null" +
",Null,Null,Null,Null,Null,Null,'" + dtData.Rows[i]["条形码"].ToString() + "'," + dt发货单信息.Rows[0]["iQuantity"] + "," + dt发货单信息.Rows[0]["iNum"] + ",N'" + dt存货信息.Rows[0]["cAssComUnitCode"] + "'" +
",Null,Null,'" + dtData.Rows[i]["条形码"].ToString() + "',Null,Null,Null,Null,Null,Null,Null" +
",Null,Null,Null,Null,Null,Null,Null,Null,Null,Null" +
",Null,Null,Null,1,0,Null,Null,Null,Null,0" +
",N'" + dt发货单信息.Rows[0]["cDLCode"] + "',N'13552.7479',Null,0,Null,Null,Null,Null,Null,Null" +
"," + dt发货单信息.Rows[0]["iSOsID"] + ",'1',N'" + dt发货单信息.Rows[0]["cSOCode"] + "'," + dt发货单信息.Rows[0]["iorderrowno"] + ",N'" + dt发货单信息.Rows[0]["iSOsID"] + "',1,N'" + dt发货单信息.Rows[0]["cSOCode"] + "'," + dt发货单信息.Rows[0]["iorderrowno"] + ",Null,0" +
",Null,Null,Null,Null,Null,Null,Null,Null,Null,Null" +
",Null,Null,Null," + dt发货单信息.Rows[0]["irowno"] + ",Null)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//更新现存量
sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
DataTable dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
long lItemID = 0;
if (dtTemp2 == null || dtTemp2.Rows.Count == 0)
{
sSQL = "insert into SCM_Item(cInvCode,cFree1,PartId) " +
"values('" + s存货编码 + "','" + dtData.Rows[i]["长度"].ToString().Trim() + "',0)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
}
else
{
lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
}
sSQL = "declare @itmeid int " +
"select @itmeid = MAX(ItemId) + 1 from CurrentStock " +
"if exists(select * from CurrentStock where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "') " +
" update CurrentStock set iQuantity = isnull(iQuantity,0) - " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = isnull(iNum,0) - " + ClsBaseDataInfo.ReturnCol(d件数) + " where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = isnull(" + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",'') and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' " +
"else " +
" insert into CurrentStock(cWhCode,cInvCode,cBatch,iSoType,iQuantity,iNum,ItemId,cFree1) " +
" values('" + s仓库 + "', '" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",0,-" + ClsBaseDataInfo.ReturnCol(d数量) + ",-" + ClsBaseDataInfo.ReturnCol(d件数) + "," + lItemID + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ")";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
for (int ii = 0; ii < dt发货单信息.Rows.Count; ii++)
{
decimal d累计出库 = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[ii]["fOutQuantity"]);
decimal d出库 = ClsBaseDataInfo.ReturnObjectToDecimal(dt发货单信息.Rows[ii]["iQuantity"]);
if (d出库 > d累计出库 || ii == dt发货单信息.Rows.Count - 1)
{
sSQL = "update DispatchLists set fOutQuantity = isnull(fOutQuantity,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + " ,fOutNum = isnull(fOutNum ,0) + " + ClsBaseDataInfo.ReturnCol(d件数) + " " +
"where AutoID = " + ClsBaseDataInfo.ReturnCol(dt发货单信息.Rows[ii]["AutoID"].ToString());
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update DispatchLists set iQuantity = fOutQuantity,iNum = fOutNum " +
"where AutoID = " + ClsBaseDataInfo.ReturnCol(dt发货单信息.Rows[ii]["AutoID"].ToString());
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = @"
update DispatchLists set iSum = cast(iTaxUnitPrice*iquantity as decimal(16,2)) ,iNatSum = cast(iTaxUnitPrice*iExchRate*iquantity as decimal(16,2))
,iMoney = cast(iTaxUnitPrice*iExchRate*iquantity as decimal(16,2)),iNatMoney = cast(cast(iTaxUnitPrice*iExchRate *iquantity as decimal(16,2)) / (1 + a.iTaxRate/100) as decimal(16,2))
,iTax = cast(iTaxUnitPrice*iquantity as decimal(16,2)) - cast(cast(iTaxUnitPrice * iquantity as decimal(16,2)) / (1 + a.iTaxRate/100) as decimal(16,2))
,iNatTax = cast(iTaxUnitPrice*iExchRate*iquantity as decimal(16,2)) - cast(cast(iTaxUnitPrice*iExchRate *iquantity as decimal(16,2)) / (1 + a.iTaxRate/100) as decimal(16,2))
from DispatchList a
where a.DLID = DispatchLists.DLID and DispatchLists.AutoID = 111111
";
sSQL = sSQL.Replace("111111", ClsBaseDataInfo.ReturnCol(dt发货单信息.Rows[ii]["AutoID"].ToString()));
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//如果不标记，销售出库单就不能手工删除，出现“此单据不是库存生成的，不能删除”的错误提示
sSQL = "update DispatchList set cSaleOut = 'ST' where cDLCode = '" + s订单号 + "'";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
break;
}
}
//货位登记
sSQL = "insert into InvPosition( RdsID, RdID, cWhCode, cPosCode, cInvCode, cBatch, cFree1, cFree2, dVDate, iQuantity" +
", iNum, cMemo, cHandler, dDate, bRdFlag, cSource, cFree3, cFree4, cFree5, cFree6" +
", cFree7, cFree8, cFree9, cFree10, cAssUnit, cBVencode, iTrackId, dMadeDate, iMassDate" +
", cMassUnit, cvmivencode, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cvouchtype, cInVouchType, cVerifier, dVeriDate, dVouchDate) " +
"values(" + lIDDetail + "," + lID + ",'" + s仓库 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ",'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",null,null," + ClsBaseDataInfo.ReturnCol(d数量) + " " +
"," + ClsBaseDataInfo.ReturnCol(d件数) + ",null,'" + s制单人 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',0,null,null,null,null,null" +
",null,null,null,null," + ClsBaseDataInfo.ReturnCol(dt存货信息.Rows[0]["cAssComUnitCode"].ToString()) + ",null,0,null,null" +
",null,null,0,null,null,'32','',null,null,'" + d当前服务器时间.ToString("yyyy-MM-dd") + "')";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "if exists( select * from InvPositionSum where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "') " +
" update InvPositionSum set iQuantity = iQuantity - " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = iNum - " + ClsBaseDataInfo.ReturnCol(d件数) + " where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().ToString() + "' " +
"else " +
"insert InvPositionSum(cWhCode, cPosCode, cInvCode, iQuantity, inum, cBatch, cFree1, cFree2, cFree3" +
" , cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrackid, cvmivencode, cMassUnit" +
", iMassDate, dMadeDate, dVDate, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cInVouchType) " +
"values( '" + s仓库 + "', " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ", '" + s存货编码 + "', -1 * " + ClsBaseDataInfo.ReturnCol(d数量) + ", -1 * isnull(" + ClsBaseDataInfo.ReturnCol(d件数) + ",0), " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ", null, null" +
" , null, null, null, null, null, null, null, 0, null, null" +
", null, null, null, 0, null, null, null)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
//7. 更新历史单据号表
sSQL = "update VoucherHistory set cNumber='" + iCode + "' Where CardNumber='0303' and cContent is NULL";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//8. 更新单据ID号表
string s1 = lID.ToString().Trim();
string s2 = lIDDetail.ToString().Trim();
s1 = s1.Substring(1);
s2 = s2.Substring(1);
lID = Convert.ToInt64(s1);
lIDDetail = Convert.ToInt64(s2);
sSQL = "update UFSystem..UA_Identity set iFatherID = " + lID + ",iChildID = " + lIDDetail + " where cAcc_Id = '" + ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3) + "' and cVouchType = 'rd'";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (sErr.Trim().Length > 0)
{
throw new Exception(sErr);
}
tran.Commit();
s = "生成单据号：" + s单据号;
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
s = ee.Message;
}
return(s);
}
public string Save产品入库单(DataTable dtData)
{
string s = "";
try
{
string sErr = "";
if (dtData == null || dtData.Rows.Count < 1)
{
throw new Exception("没有需要保存的数据");
}
SqlConnection conn = new SqlConnection(ClsBaseClass.ClsBaseDataInfo.sConnString);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSQL = "select getdate()";
DateTime d当前服务器时间 = Convert.ToDateTime(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
//1. 判断是否结账
sSQL = "select * from gl_mend where iyear=year(getdate()) and iperiod=month(getdate())";
DataTable dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp == null || dtTemp.Rows.Count < 1)
{
throw new Exception("判断模块结账失败");
}
int iR = ClsBaseDataInfo.ReturnObjectToInt(dtTemp.Rows[0]["bflag_ST"]);
if (iR == 1)
{
throw new Exception("当前月份已经结账");
}
//2. 获得单据ID
long lID = 1;
long lIDDetail = 1;
ClsU8基础档案 cls = new ClsU8基础档案();
cls.GetRdID(out lID, out lIDDetail, ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3));
//3. 获得单据号
long iCode = 0;
sSQL = "select * From VoucherHistory with (ROWLOCK) Where CardNumber='0411'";
dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp == null || dtTemp.Rows.Count < 1)
{
iCode = 1;
}
else
{
iCode = ClsBaseDataInfo.ReturnObjectToLong(dtTemp.Rows[0]["cNumber"]);
}
sSQL = "select * from mom_order a inner join mom_orderdetail b on a.moid = b.moid where a.mocode = '" + dtData.Rows[0]["单据号"].ToString().Trim() + "' ";
DataTable dt生产订单表头 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt生产订单表头 == null || dt生产订单表头.Rows.Count < 1)
{
throw new Exception("获得生产订单信息失败");
}
//4. 组装表头
lID += 1;
string s仓库 = dtData.Rows[0]["仓库"].ToString().Trim();
string s订单号 = dtData.Rows[0]["单据号"].ToString().Trim();
string s行号 = dtData.Rows[0]["行号"].ToString().Trim();
string s制单人 = dtData.Rows[0]["制单人"].ToString().Trim();
string s生产订单主表标志 = dt生产订单表头.Rows[0]["MOID"].ToString().Trim();
iCode += 1;
string s单据号 = sGetCode(iCode, 10);
sSQL = " insert into rdrecord10(id,brdflag,cvouchtype,cbustype,csource,cwhcode,ddate,ccode,cmaker,vt_id" +
",cmpocode,iproorderid,iswfcontrolled,dnmaketime,dnmodifytime,dnverifytime,cRdCode,cdefine1) " +
" values (N'" + lID + "',N'1',N'10',N'成品入库',N'生产订单',N'" + s仓库 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',N'" + s单据号 + "',N'" + s制单人 + "',63" +
",N'" + s订单号 + "',N'" + s生产订单主表标志 + "',0, getdate(), Null , Null,12 ,N'" + s订单号 + "')";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//5. [更新采购订单表头] 暂时未发现需要更新的内容
for (int i = 0; i < dtData.Rows.Count; i++)
{
//判断条码是否已经使用
sSQL = "select * from rdrecords10 where BarCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + " ";
int iCou = ClsBaseDataInfo.ReturnObjectToInt(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
if (iCou > 0)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "已经使用\n";
continue;
}
sSQL = @"
select * ,d.CompleteQty - b.QualifiedInQty AS QtyUnIn
from mom_order a inner join mom_orderdetail b on a.moid = b.moid
LEFT JOIN sfc_moroutingdetail d ON b.MoDId = d.MoDId AND d.LastFlag = 1
where a.mocode = '" + dtData.Rows[i]["单据号"].ToString().Trim() + "' and b.SortSeq = '" + dtData.Rows[i]["行号"].ToString().Trim() + "'";
DataTable dt生产订单 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt生产订单 == null || dt生产订单.Rows.Count < 1)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得生产订单信息失败\n";
continue;
}
if (ClsBaseClass.ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单.Rows[0]["CompleteQty"], 2) == 0 && ClsBaseClass.ClsBaseDataInfo.ReturnObjectToInt(dt生产订单.Rows[0]["MoClass"]) == 1)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "对应生产订单订单工序未完成\n";
continue;
}
sSQL = "select * from Inventory where cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "'";
DataTable dt存货信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt存货信息 == null || dt存货信息.Rows.Count < 1)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得存货信息失败\n";
continue;
}
int i是否批次 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bInvBatch"]);
if (i是否批次 != 0 && dtData.Rows[i]["批号"].ToString().Trim() == "")
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "是批次管理物料，必须输入批号\n";
continue;
}
int i是否自由项1 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bFree1"]);
if (i是否自由项1 != 0 && dtData.Rows[i]["长度"].ToString().Trim() == "")
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "必须输入长度\n";
continue;
}
//组装表体
lIDDetail += 1;
string s存货编码 = dtData.Rows[i]["存货编码"].ToString().Trim();
decimal d数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"], 6);
decimal d生产订单数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单.Rows[0]["Qty"], 6);
decimal d生产订单数件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单.Rows[0]["AuxQty"], 6);
decimal d累计入库数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt生产订单.Rows[0]["QualifiedInQty"], 6);
decimal d件数 = 1;
//if (d生产订单数件数 != 0)
//{
// d件数 = ClsBaseDataInfo.ReturnObjectToDecimal(d生产订单数件数 * d数量 / d生产订单数量, 6);
//}
decimal d换算率 = d数量;
decimal d入库超额上限 = ClsBaseDataInfo.ReturnObjectToDecimal(dt存货信息.Rows[0]["fInExcess"], 6);
//if (d生产订单数量 * (1 + d入库超额上限 / 100) < d累计入库数量 + d数量)
//{
// sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "累计入库数量超订单\n";
// continue;
//}
sSQL = "select * from Warehouse where cWhCode = '" + s仓库 + "'";
DataTable dtWH = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
string s销售订单号 = dt生产订单.Rows[0]["OrderCode"].ToString().Trim();
string s销售订单行号 = dt生产订单.Rows[0]["OrderSeq"].ToString().Trim();
sSQL = "Insert Into rdrecords10(autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,ipunitcost,ipprice,cbatch" +
",cvouchcode,cinvouchcode,cinvouchtype,isoutquantity,isoutnum,cfree1,cfree2,ifnum,ifquantity,dvdate" +
",cposition,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27,citem_class,citemcode,cname" +
",citemcname,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,BarCode" +
",inquantity,innum,cassunit,dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32" +
",cdefine33,cdefine34,cdefine35,cdefine36,cdefine37,impoids,icheckids,cbvencode,ccheckcode,icheckidbaks" +
",crejectcode,irejectids,ccheckpersoncode,dcheckdate,cmassunit,cmolotcode,brelated,cmworkcentercode,cbaccounter,dbkeepdate" +
",bcosting,bvmiused,ivmisettlequantity,ivmisettlenum,cvmivencode,iinvsncount,iinvexchrate,corufts,cmocode,imoseq" +
",iopseq,copdesc,iexpiratdatecalcu,cexpirationdate,dexpirationdate,cciqbookcode,ibondedsumqty,productinids,iorderdid,iordertype" +
",iordercode,iorderseq,isodid,isotype,csocode,isoseq,cbatchproperty1,cbatchproperty2,cbatchproperty3,cbatchproperty4" +
",cbatchproperty5,cbatchproperty6,cbatchproperty7,cbatchproperty8,cbatchproperty9,cbatchproperty10,cbmemo,irowno,strowguid,cservicecode) " +
"Values (" + lIDDetail + "," + lID + ",N'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(d件数) + "," + ClsBaseDataInfo.ReturnCol(d数量) + ",Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + "" +
",Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",Null,Null,Null,Null" +
"," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + ",null,null,null,Null,Null,Null,Null,Null" +
",Null,Null,Null,Null,Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + "" +
"," + d生产订单数量 + "," + d生产订单数件数 + ",N'" + dt存货信息.Rows[0]["cAssComUnitCode"].ToString().Trim() + "',Null,Null,Null,Null,Null,Null,Null" +
",Null,Null,Null,Null,Null," + dt生产订单.Rows[0]["Modid"] + ",Null,Null,Null,Null" +
",Null,Null,Null,Null,Null,Null,0,Null,Null,Null" +
",1,Null,Null,Null,Null,Null," + d换算率 + ",N'13548.9432',N'" + dtData.Rows[i]["单据号"].ToString() + "'," + dtData.Rows[i]["行号"].ToString() + "" +
",Null,Null,0,Null,Null,Null,Null,Null," + dt生产订单.Rows[0]["Modid"] + ",1" +
",N'" + s销售订单号 + "','" + s销售订单行号 + "',Null,0,Null,Null,Null,Null,Null,Null" +
",Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["炉号"].ToString()) + ",Null,Null,Null,Null,Null," + (i + 1).ToString() + ",Null,Null)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "Update 条形码信息 set 产成品入库单号 = '" + s单据号 + "',产成品入库单行号 = " + (i + 1).ToString() + " where 条形码 = '" + dtData.Rows[i]["条形码"].ToString().Trim() + "'";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//sSQL = "insert into IA_ST_UnAccountVouch10(idun,idsun,cvoutypeun,cbustypeun)values " +
// "('" + lID + "','" + lIDDetail + "','10','成品入库')";
//SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
DataTable dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
long lItemID = 0;
if (dtTemp2 == null || dtTemp2.Rows.Count == 0)
{
sSQL = "insert into SCM_Item(cInvCode,cFree1,PartId) " +
"values('" + s存货编码 + "','" + dtData.Rows[i]["长度"].ToString().Trim() + "',0)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
}
else
{
lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
}
//更新现存量
sSQL = "declare @itmeid int " +
"select @itmeid = MAX(ItemId) + 1 from CurrentStock " +
"if exists(select * from CurrentStock where cinvcode = '" + s存货编码 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' and isnull(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString() + "' and cWhCode = '" + s仓库 + "') " +
" update CurrentStock set iQuantity = isnull(iQuantity,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = isnull(iNum,0) + " + ClsBaseDataInfo.ReturnCol(d件数) + " where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString() + "' and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' " +
"else " +
" insert into CurrentStock(cWhCode,cInvCode,cBatch,iSoType,iQuantity,iNum,ItemId,cFree1) " +
" values('" + s仓库 + "', '" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",0," + ClsBaseDataInfo.ReturnCol(d数量) + "," + ClsBaseDataInfo.ReturnCol(d件数) + "," + lItemID + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ")";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update mom_orderdetail set QualifiedInQty = isnull(QualifiedInQty ,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + " where Modid = " + ClsBaseDataInfo.ReturnCol(dt生产订单.Rows[0]["Modid"].ToString());
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (Convert.ToInt32(dtWH.Rows[0]["bWhPos"]) != 0)
{
//货位登记
sSQL = "insert into InvPosition( RdsID, RdID, cWhCode, cPosCode, cInvCode, cBatch, cFree1, cFree2, dVDate, iQuantity" +
", iNum, cMemo, cHandler, dDate, bRdFlag, cSource, cFree3, cFree4, cFree5, cFree6" +
", cFree7, cFree8, cFree9, cFree10, cAssUnit, cBVencode, iTrackId, dMadeDate, iMassDate" +
", cMassUnit, cvmivencode, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cvouchtype, cInVouchType, cVerifier, dVeriDate, dVouchDate) " +
"values(" + lIDDetail + "," + lID + ",'" + s仓库 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ",'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",null,null," + ClsBaseDataInfo.ReturnCol(d数量) + " " +
"," + ClsBaseDataInfo.ReturnCol(d件数) + ",null,'" + s制单人 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',1,null,null,null,null,null" +
",null,null,null,null," + ClsBaseDataInfo.ReturnCol(dt存货信息.Rows[0]["cAssComUnitCode"].ToString()) + ",null,0,null,null" +
",null,null,0,null,null,'10','',null,null,'" + d当前服务器时间.ToString("yyyy-MM-dd") + "')";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "if exists( select * from InvPositionSum where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = " + dtData.Rows[i]["长度"].ToString().Trim() + ") " +
" update InvPositionSum set iQuantity = iQuantity + " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = iNum + " + ClsBaseDataInfo.ReturnCol(d件数) + " where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = " + dtData.Rows[i]["长度"].ToString().Trim() + " " +
"else " +
"insert InvPositionSum(cWhCode, cPosCode, cInvCode, iQuantity, inum, cBatch, cFree1, cFree2, cFree3" +
" , cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrackid, cvmivencode, cMassUnit" +
", iMassDate, dMadeDate, dVDate, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cInVouchType) " +
"values( '" + s仓库 + "', " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ", '" + s存货编码 + "', " + ClsBaseDataInfo.ReturnCol(d数量) + ", " + ClsBaseDataInfo.ReturnCol(d件数) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ", null, null" +
" , null, null, null, null, null, null, null, 0, null, null" +
", null, null, null, 0, null, null, null)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
//更新批次属性档案
sSQL = @"
IF Object_id('Tempdb..#STBatchArchief') IS NOT NULL
DROP TABLE #STBatchArchief
select distinct convert(int,1) as autoid , a.cinvcode as cinvcode , isnull(a.cbatch ,'') as cbatch, isnull(a.cfree1,'') as cfree1
, isnull(a.cfree2,'') as cfree2, isnull(a.cfree3,'') as cfree3, isnull(a.cfree4,'') as cfree4, isnull(a.cfree5,'') as cfree5
, isnull(a.cfree6,'') as cfree6, isnull(a.cfree7,'') as cfree7, isnull(a.cfree8,'') as cfree8, isnull(a.cfree9,'') as cfree9
, isnull(a.cfree10,'') as cfree10, a.cBatchProperty1 as cBatchProperty1, a.cBatchProperty2 as cBatchProperty2
, a.cBatchProperty3 as cBatchProperty3, a.cBatchProperty4 as cBatchProperty4, a.cBatchProperty5 as cBatchProperty5
, a.cBatchProperty6 as cBatchProperty6, a.cBatchProperty7 as cBatchProperty7, a.cBatchProperty8 as cBatchProperty8
, a.cBatchProperty9 as cBatchProperty9, a.cBatchProperty10 as cBatchProperty10
into #STBatchArchief
from rdrecords10 a with (nolock) inner join inventory_sub b with (nolock) on a.cinvcode = b.cinvsubcode and isnull(bbatchcreate,0)=1
where a.id =aaaaaaaaaa and isnull(a.cbatch ,'')<>''
exec Scm_SaveBatchProperty '1','autoid','#STBatchArchief',N'demo'
drop table #STBatchArchief
";
sSQL = sSQL.Replace("aaaaaaaaaa", lID.ToString());
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//7. 更新历史单据号表
sSQL = "update VoucherHistory set cNumber='" + iCode + "' Where CardNumber='0411' and cContent is NULL";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//8. 登记未记账
sSQL = @"exec IA_SP_WriteUnAccountVouchForST aaaaaa,N'10'";
sSQL = sSQL.Replace("aaaaaa", lID.ToString());
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//9. 更新单据ID号表
string s1 = lID.ToString().Trim();
string s2 = lIDDetail.ToString().Trim();
s1 = s1.Substring(1);
s2 = s2.Substring(1);
lID = Convert.ToInt64(s1);
lIDDetail = Convert.ToInt64(s2);
sSQL = "update UFSystem..UA_Identity set iFatherID = " + lID + ",iChildID = " + lIDDetail + " where cAcc_Id = '" + ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3) + "' and cVouchType = 'rd'";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (sErr.Trim().Length > 0)
{
throw new Exception(sErr);
}
tran.Commit();
s = "生成单据号：" + s单据号;
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
s = ee.Message;
}
return(s);
}
public string Save采购入库单(DataTable dtData)
{
string s = "";
try
{
string sErr = "";
if (dtData == null || dtData.Rows.Count < 1)
{
throw new Exception("没有需要保存的数据");
}
SqlConnection conn = new SqlConnection(ClsBaseClass.ClsBaseDataInfo.sConnString);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSQL = "select getdate()";
DateTime d当前服务器时间 = Convert.ToDateTime(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
//1. 判断是否结账
sSQL = "select * from gl_mend where iyear=year(getdate()) and iperiod=month(getdate())";
DataTable dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp == null || dtTemp.Rows.Count < 1)
{
throw new Exception("判断模块结账失败");
}
int iR = ClsBaseDataInfo.ReturnObjectToInt(dtTemp.Rows[0]["bflag_ST"]);
if (iR == 1)
{
throw new Exception("当前月份已经结账");
}
//2. 获得单据ID
long lID = 1;
long lIDDetail = 1;
ClsU8基础档案 cls = new ClsU8基础档案();
cls.GetRdID(out lID, out lIDDetail, ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3));
//3. 获得单据号
long iCode = 0;
sSQL = "select * From VoucherHistory with (ROWLOCK) Where CardNumber='24'";
dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp == null || dtTemp.Rows.Count < 1)
{
iCode = 1;
}
else
{
iCode = ClsBaseDataInfo.ReturnObjectToLong(dtTemp.Rows[0]["cNumber"]);
}
sSQL = "select * from PO_Pomain where cPOID = " + dtData.Rows[0]["单据号"] + " ";
DataTable dt采购订单 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt采购订单 == null || dt采购订单.Rows.Count < 1)
{
throw new Exception("获得采购订单信息失败");
}
//4. 组装表头
lID += 1;
string s仓库 = dtData.Rows[0]["仓库"].ToString().Trim();
string s供应商 = dtData.Rows[0]["供应商"].ToString().Trim();
string s订单号 = dtData.Rows[0]["单据号"].ToString().Trim();
string s制单人 = dtData.Rows[0]["制单人"].ToString().Trim();
string s采购订单主表标志 = dt采购订单.Rows[0]["POID"].ToString().Trim();
iCode += 1;
string s单据号 = sGetCode(iCode, 10);
sSQL = "insert into rdrecord01(id,brdflag,cvouchtype,cbustype,csource,cwhcode,ddate,ccode,crdcode,cptcode" +
",cvencode,cordercode,cmaker,bpufirst,vt_id,bisstqc,ipurorderid,itaxrate,iexchrate,cexch_name" +
",bomfirst,idiscounttaxtype,iswfcontrolled,dnmaketime,dnmodifytime,dnverifytime,bredvouch,bcredit) " +
"values (N'" + lID + "',N'1',N'01',N'普通采购',N'采购订单',N'" + s仓库 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',N'" + s单据号 + "',N'11',N'01'" +
",N'" + s供应商 + "',N'" + s订单号 + "',N'" + s制单人 + "',0,27,0,N'" + s采购订单主表标志 + "',N'17',1,N'人民币'" +
",0,N'0',0, getdate(), Null , Null ,0,N'0')";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//5. [更新采购订单表头] 暂时未发现需要更新的内容
for (int i = 0; i < dtData.Rows.Count; i++)
{
//判断条码是否已经使用
sSQL = "select * from rdrecords01 where BarCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + " ";
int iCou = ClsBaseDataInfo.ReturnObjectToInt(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
if (iCou > 0)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "已经使用\n";
continue;
}
sSQL = "select * from PO_Pomain a inner join PO_Podetails b on a.POID = b.POID where a.cPOID = '" + dtData.Rows[i]["单据号"] + "' and b.cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "' order by b.ID";
DataTable dt采购订单信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt采购订单信息 == null || dt采购订单信息.Rows.Count < 1)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得采购订单信息失败\n";
continue;
}
sSQL = "select * from Inventory where cInvCode = '" + dtData.Rows[i]["存货编码"].ToString().Trim() + "'";
DataTable dt存货信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt存货信息 == null || dt存货信息.Rows.Count < 1)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "获得存货信息失败\n";
continue;
}
int i是否批次 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bInvBatch"]);
if (i是否批次 != 0 && dtData.Rows[i]["批号"].ToString().Trim() == "")
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "是批次管理物料，必须输入批号\n";
continue;
}
int i是否自由项1 = ClsBaseDataInfo.ReturnObjectToInt(dt存货信息.Rows[0]["bFree1"]);
if (i是否自由项1 != 0 && dtData.Rows[i]["长度"].ToString().Trim() == "")
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "必须输入长度\n";
continue;
}
//组装表体
lIDDetail += 1;
string s存货编码 = dtData.Rows[i]["存货编码"].ToString().Trim();
decimal d数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"], 6);
decimal d税率 = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iPerTaxRate"], 6);
decimal d采购订单数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iQuantity"], 6);
decimal d采购订单件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iNum"], 6);
decimal d累计入库数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iReceivedQTY"], 6);
decimal d累计入库件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iReceivedNum"], 6);
decimal d件数 = 0;
if (d采购订单件数 != 0)
{
d件数 = ClsBaseDataInfo.ReturnObjectToDecimal(d采购订单件数 * d数量 / d采购订单数量, 6);
}
decimal d入库超额上限 = ClsBaseDataInfo.ReturnObjectToDecimal(dt存货信息.Rows[0]["fInExcess"], 6);
if (d采购订单数量 * (1 + d入库超额上限 / 100) < d累计入库数量 + d数量 || d采购订单件数 * (1 + d入库超额上限 / 100) < d累计入库件数 + d件数)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + ":" + dtData.Rows[i]["存货编码"].ToString().Trim() + "累计入库数量超订单\n";
continue;
}
decimal d含税单价 = ClsBaseDataInfo.ReturnObjectToDecimal(dt采购订单信息.Rows[0]["iTaxPrice"], 6);
decimal d含税金额 = ClsBaseDataInfo.ReturnObjectToDecimal(d含税单价 * d数量, 2);
decimal d无税金额 = ClsBaseDataInfo.ReturnObjectToDecimal(d含税金额 / (1 + d税率 / 100), 2);
decimal d税额 = d含税金额 - d无税金额;
decimal d无税单价 = ClsBaseDataInfo.ReturnObjectToDecimal(d无税金额 / d数量, 3);
sSQL = "Insert Into rdrecords01(autoid,id,cinvcode,inum,iquantity,iunitcost,iprice,iaprice,ipunitcost,ipprice" +
",cbatch,cvouchcode,cinvouchcode,cinvouchtype,isoutquantity,isoutnum,cfree1,cfree2,dsdate,itax" +
",isquantity,isnum,imoney,ifnum,ifquantity,dvdate,cposition,cdefine22,cdefine23,cdefine24" +
",cdefine25,cdefine26,cdefine27,citem_class,citemcode,iposid,facost,cname,citemcname,cfree3" +
",cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,BarCode,inquantity,innum" +
",cassunit,dmadedate,imassdate,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34" +
",cdefine35,cdefine36,cdefine37,icheckids,cbvencode,cgspstate,iarrsid,ccheckcode,icheckidbaks,crejectcode" +
",irejectids,ccheckpersoncode,dcheckdate,ioritaxcost,ioricost,iorimoney,ioritaxprice,iorisum,itaxrate,itaxprice" +
",isum,btaxcost,cpoid,cmassunit,imaterialfee,iprocesscost,iprocessfee,dmsdate,ismaterialfee,isprocessfee" +
",iomodid,strcontractid,strcode,cbaccounter,dbkeepdate,bcosting,isumbillquantity,bvmiused,ivmisettlequantity,ivmisettlenum" +
",cvmivencode,iinvsncount,impcost,iimosid,iimbsid,cbarvcode,dbarvdate,iinvexchrate,corufts,iexpiratdatecalcu" +
",cexpirationdate,dexpirationdate,cciqbookcode,ibondedsumqty,iordertype,iorderdid,iordercode,iorderseq,isodid,isotype" +
",csocode,isoseq,cbatchproperty1,cbatchproperty2,cbatchproperty3,cbatchproperty4,cbatchproperty5,cbatchproperty6,cbatchproperty7,cbatchproperty8" +
",cbatchproperty9,cbatchproperty10,cbmemo,ifaqty,istax,irowno,strowguid) " +
"Values (" + lIDDetail + "," + lID + ",N'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(d件数) + "," + ClsBaseDataInfo.ReturnCol(d数量) + "," + ClsBaseDataInfo.ReturnCol(d无税单价) + "," + ClsBaseDataInfo.ReturnCol(d无税金额) + "," + ClsBaseDataInfo.ReturnCol(d无税金额) + ",Null,Null" +
"," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",Null,Null,Null" +
",0,0,0,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " ," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + ",Null,Null" +
",Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dt采购订单信息.Rows[0]["ID"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(d无税单价) + ",Null,Null,Null" +
",Null,Null,Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["条形码"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(d数量) + "," + ClsBaseDataInfo.ReturnCol(d件数) + "" +
",Null,Null,Null,Null,Null,Null,Null,Null,Null,Null" +
",Null,Null,Null,Null,Null,Null,Null,Null,Null,Null" +
",Null,Null,Null," + ClsBaseDataInfo.ReturnCol(d含税单价) + "," + ClsBaseDataInfo.ReturnCol(d无税单价) + "," + ClsBaseDataInfo.ReturnCol(d无税金额) + "," + ClsBaseDataInfo.ReturnCol(d税额) + "," + ClsBaseDataInfo.ReturnCol(d含税金额) + "," + ClsBaseDataInfo.ReturnCol(d税率) + "," + ClsBaseDataInfo.ReturnCol(d税额) + "" +
"," + ClsBaseDataInfo.ReturnCol(d含税金额) + ",0," + dtData.Rows[i]["单据号"].ToString() + ",Null,Null,Null,Null,Null,Null,Null" +
",Null,Null,Null,Null,Null,1,Null,Null,Null,Null" +
",Null,Null,Null,Null,Null,Null,Null,Null,N'13502.0346',0" +
",Null,Null,Null,Null,Null,Null,Null,Null,Null,0" +
",Null,Null,Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["炉号"].ToString()) + ",Null,Null" +
",Null,Null,Null,Null,Null," + ClsBaseDataInfo.ReturnCol(dt采购订单信息.Rows[0]["irowno"].ToString()) + ",Null)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//更新现存量
sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
DataTable dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
long lItemID = 0;
if (dtTemp2 == null || dtTemp2.Rows.Count == 0)
{
sSQL = "insert into SCM_Item(cInvCode,cFree1,PartId) " +
"values('" + s存货编码 + "','" + dtData.Rows[i]["长度"].ToString().Trim() + "',0)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "select * from SCM_Item where cInvCode = '" + s存货编码 + "' and ISNULL(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' ";
dtTemp2 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
}
else
{
lItemID = Convert.ToInt64(dtTemp2.Rows[0]["id"]);
}
sSQL = "declare @itmeid int " +
"select @itmeid = MAX(ItemId) + 1 from CurrentStock " +
"if exists(select * from CurrentStock where cinvcode = '" + s存货编码 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' and isnull(cBatch,'') = isnull(" + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",'') and cWhCode = '" + s仓库 + "') " +
" update CurrentStock set iQuantity = isnull(iQuantity,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = isnull(iNum,0) + " + ClsBaseDataInfo.ReturnCol(d件数) + " where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = isnull(" + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ",'') and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' " +
"else " +
" insert into CurrentStock(cWhCode,cInvCode,cBatch,iSoType,iQuantity,iNum,ItemId,cFree1) " +
" values('" + s仓库 + "', '" + s存货编码 + "','" + dtData.Rows[i]["批号"].ToString() + "',0," + ClsBaseDataInfo.ReturnCol(d数量) + "," + ClsBaseDataInfo.ReturnCol(d件数) + "," + lItemID + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ")";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update Po_Podetails set iReceivedQTY = isnull(iReceivedQTY,0) + " + ClsBaseDataInfo.ReturnCol(d数量) + " ,iReceivedNum = isnull(iReceivedNum,0) + " + ClsBaseDataInfo.ReturnCol(d件数) + ",iReceivedMoney = isnull(iReceivedMoney,0) + " + ClsBaseDataInfo.ReturnCol(d含税金额) + " " +
"where ID = " + ClsBaseDataInfo.ReturnCol(dt采购订单信息.Rows[0]["ID"].ToString());
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//货位登记
sSQL = "insert into InvPosition( RdsID, RdID, cWhCode, cPosCode, cInvCode, cBatch, cFree1, cFree2, dVDate, iQuantity" +
", iNum, cMemo, cHandler, dDate, bRdFlag, cSource, cFree3, cFree4, cFree5, cFree6" +
", cFree7, cFree8, cFree9, cFree10, cAssUnit, cBVencode, iTrackId, dMadeDate, iMassDate" +
", cMassUnit, cvmivencode, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cvouchtype, cInVouchType, cVerifier, dVeriDate, dVouchDate) " +
"values(" + lIDDetail + "," + lID + ",'" + s仓库 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ",'" + s存货编码 + "'," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ",null,null," + ClsBaseDataInfo.ReturnCol(d数量) + " " +
"," + ClsBaseDataInfo.ReturnCol(d件数) + ",null,'" + s制单人 + "','" + d当前服务器时间.ToString("yyyy-MM-dd") + "',1,null,null,null,null,null" +
",null,null,null,null," + ClsBaseDataInfo.ReturnCol(dt存货信息.Rows[0]["cAssComUnitCode"].ToString()) + ",null,0,null,null" +
",null,null,0,null,null,'01','',null,null,'" + d当前服务器时间.ToString("yyyy-MM-dd") + "')";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "if exists( select * from InvPositionSum where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "') " +
" update InvPositionSum set iQuantity = iQuantity + " + ClsBaseDataInfo.ReturnCol(d数量) + ",iNum = iNum + " + ClsBaseDataInfo.ReturnCol(d件数) + " where cInvCode = '" + s存货编码 + "' and cWhCode = '" + s仓库 + "' and cPosCode = " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + " and ISNULL(cBatch,'') = '" + dtData.Rows[i]["批号"].ToString().Trim() + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "' " +
"else " +
"insert InvPositionSum(cWhCode, cPosCode, cInvCode, iQuantity, inum, cBatch, cFree1, cFree2, cFree3" +
" , cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrackid, cvmivencode, cMassUnit" +
", iMassDate, dMadeDate, dVDate, iExpiratDateCalcu, cExpirationdate, dExpirationdate, cInVouchType) " +
"values( '" + s仓库 + "', " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["货位"].ToString()) + ", '" + s存货编码 + "', " + ClsBaseDataInfo.ReturnCol(d数量) + ", " + ClsBaseDataInfo.ReturnCol(d件数) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["批号"].ToString()) + ", " + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ", null, null" +
" , null, null, null, null, null, null, null, 0, null, null" +
", null, null, null, 0, null, null, null)";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
//7. 更新历史单据号表
sSQL = "update VoucherHistory set cNumber='" + iCode + "' Where CardNumber='24' and cContent is NULL";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//8. 更新单据ID号表
string s1 = lID.ToString().Trim();
string s2 = lIDDetail.ToString().Trim();
s1 = s1.Substring(1);
s2 = s2.Substring(1);
lID = Convert.ToInt64(s1);
lIDDetail = Convert.ToInt64(s2);
sSQL = "update UFSystem..UA_Identity set iFatherID = " + lID + ",iChildID = " + lIDDetail + " where cAcc_Id = '" + ClsBaseDataInfo.sUFDataBaseName.Trim().Substring(7, 3) + "' and cVouchType = 'rd'";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (sErr.Trim().Length > 0)
{
throw new Exception(sErr);
}
tran.Commit();
s = "生成单据号：" + s单据号;
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
s = ee.Message;
}
return(s);
}