public string GetNewName(string filePath)
{
int filenew = 0;
string[] filenames = Directory.GetFiles(filePath);
for (int j = 0; j < filenames.Length; j++)
{
int fn = ClsBaseDataInfo.ReturnObjectToInt(Path.GetFileNameWithoutExtension(filenames[j]));
if (fn > filenew)
{
filenew = fn;
}
}
return((filenew + 1).ToString());
}
/// <summary>
/// 保存材料出库单扫描数量
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public int iSaveRdrecord11ChkQty(string sCode, DataTable dtBarCode, string sUid)
{
int iCou = 0;
try
{
SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
decimal dQtyScanSum = 0; // 累计扫描数量
for (int i = 0; i < dtBarCode.Rows.Count; i++)
{
dQtyScanSum = dQtyScanSum + ClsBaseDataInfo.ReturnObjectToDecimal(dtBarCode.Rows[i]["数量"]);
}
string sSQL = "select a.cCode,b.iRowNo, b.cInvCode,b.iQuantity,b.cDefine29 as 已扫描数量,b.cDefine28 as 货位,b.cDefine30 as 批号,cast(null as decimal(16,6)) as 本次扫描数量,b.autoid from RdRecord11 a inner join rdrecords11 b on a.id = b.id where a.cCode = '" + sCode + "' order by b.cInvCode";
DataTable dtRdRecord11 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
bool bRed = false;
if (dtRdRecord11 != null && dtRdRecord11.Rows.Count > 0 && BaseClass.BaseFunction.ReturnDecimal(dtRdRecord11.Rows[0]["iQuantity"]) < 0)
{
bRed = true;
}
sSQL = "select getdate()";
DataTable dtTime = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
DateTime dtmNow = BaseFunction.ReturnDate(dtTime.Rows[0][0]);
string s_BarCodeRDCode = BaseFunction.ReturnDate(dtTime.Rows[0][0]).ToString("yyMMddHHmmss");
dtTime = null;
_BarCodeRD DAL_BarCodeRD = new _BarCodeRD();
_BarCodeList DAL_BarCodeList = new _BarCodeList();
#region 蓝字单据
if (!bRed)
{
for (int i = 0; i < dtBarCode.Rows.Count; i++)
{
string sInvCode = dtBarCode.Rows[i]["存货编码"].ToString().Trim();
decimal dQtyBarCode = BaseFunction.ReturnDecimal(dtBarCode.Rows[i]["数量"]);
//条形码中仅存货编码与入库单对应
DataRow[] drCode = dtRdRecord11.Select("cInvCode = '" + sInvCode + "'");
for (int j = 0; j < drCode.Length; j++)
{
if (dQtyBarCode <= 0)
{
break;
}
decimal diQuantity = BaseFunction.ReturnDecimal(drCode[j]["iQuantity"]);
decimal dQtyScaned = BaseFunction.ReturnDecimal(drCode[j]["已扫描数量"]);
decimal dQtyNow = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]);
decimal d = diQuantity - dQtyScaned - dQtyNow;
Model._BarCodeRD model_BarCodeRD = new TH.WebService.Model._BarCodeRD();
model_BarCodeRD.sCode = s_BarCodeRDCode;
model_BarCodeRD.BarCode = dtBarCode.Rows[i]["条形码"].ToString().Trim();
model_BarCodeRD.sType = "材料出库单";
model_BarCodeRD.ExsID = BaseFunction.ReturnLong(drCode[j]["autoid"]);
model_BarCodeRD.ExCode = drCode[j]["cCode"].ToString().Trim();
model_BarCodeRD.ExRowNo = ClsBaseDataInfo.ReturnObjectToLong(drCode[j]["iRowNo"]);
model_BarCodeRD.cInvCode = drCode[j]["cInvCode"].ToString().Trim();
model_BarCodeRD.CreateUid = dtBarCode.Rows[i]["制单人"].ToString().Trim();
model_BarCodeRD.CreateDate = dtmNow;
model_BarCodeRD.XBarCode = dtBarCode.Rows[i]["箱码"].ToString().Trim();
model_BarCodeRD.RDType = 2;
//条形码数量超出单据数量
if (dQtyBarCode >= d)
{
model_BarCodeRD.Qty = d;
dQtyScanSum = dQtyScanSum - d;
drCode[j]["本次扫描数量"] = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]) + d;
dQtyBarCode = dQtyBarCode - d;
}
else
{
model_BarCodeRD.Qty = dQtyBarCode;
dQtyScanSum = dQtyScanSum - dQtyBarCode;
drCode[j]["本次扫描数量"] = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]) + dQtyBarCode;
dQtyBarCode = 0;
}
sSQL = DAL_BarCodeRD.Add(model_BarCodeRD);
iCou = iCou + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
string sUpdate = "";
if (drCode[j]["货位"].ToString().Trim() == "" && drCode[j]["批号"].ToString().Trim() == "")
{
sUpdate = sUpdate + ",cDefine28 = '" + dtBarCode.Rows[i]["货位"].ToString().Trim() + "'";
sUpdate = sUpdate + ",cDefine30 = '" + dtBarCode.Rows[i]["批号"].ToString().Trim() + "'";
}
sSQL = "update rdrecords11 set cDefine29 = cast(isnull(cDefine29,0) as decimal(16,6)) + " + model_BarCodeRD.Qty.ToString() + " 111111 where autoid = " + model_BarCodeRD.ExsID.ToString();
if (sUpdate == "")
{
sSQL = sSQL.Replace("111111", "");
}
else
{
sSQL = sSQL.Replace("111111", sUpdate);
}
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//当可用量为0是，标记条形码失效
sSQL = DAL_BarCodeList.sBarCodeQty(model_BarCodeRD.BarCode);
DataTable dtQty = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtQty != null && dtQty.Rows.Count > 0)
{
decimal dQtyUsed = BaseFunction.ReturnDecimal(dtQty.Rows[0][0]);
if (dQtyUsed <= 0)
{
sSQL = "update _BarCodeList set valid = 0 where BarCode = '" + model_BarCodeRD.BarCode + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
}
}
}
#endregion
#region 红字
else
{
for (int i = 0; i < dtBarCode.Rows.Count; i++)
{
string sInvCode = dtBarCode.Rows[i]["存货编码"].ToString().Trim();
decimal dQtyBarCode = BaseFunction.ReturnDecimal(dtBarCode.Rows[i]["数量"]);
//条形码中仅存货编码与入库单对应
DataRow[] drCode = dtRdRecord11.Select("cInvCode = '" + sInvCode + "'");
for (int j = 0; j < drCode.Length; j++)
{
if (dQtyBarCode >= 0)
{
break;
}
decimal diQuantity = BaseFunction.ReturnDecimal(drCode[j]["iQuantity"]);
decimal dQtyScaned = BaseFunction.ReturnDecimal(drCode[j]["已扫描数量"]);
decimal dQtyNow = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]);
decimal d = diQuantity - dQtyScaned - dQtyNow;
Model._BarCodeRD model_BarCodeRD = new TH.WebService.Model._BarCodeRD();
model_BarCodeRD.sCode = s_BarCodeRDCode;
model_BarCodeRD.BarCode = dtBarCode.Rows[i]["条形码"].ToString().Trim();
model_BarCodeRD.sType = "材料出库单";
model_BarCodeRD.ExsID = BaseFunction.ReturnLong(drCode[j]["autoid"]);
model_BarCodeRD.ExCode = drCode[j]["cCode"].ToString().Trim();
model_BarCodeRD.ExRowNo = ClsBaseDataInfo.ReturnObjectToLong(drCode[j]["iRowNo"]);
model_BarCodeRD.cInvCode = drCode[j]["cInvCode"].ToString().Trim();
model_BarCodeRD.CreateUid = dtBarCode.Rows[i]["制单人"].ToString().Trim();
model_BarCodeRD.CreateDate = dtmNow;
model_BarCodeRD.XBarCode = dtBarCode.Rows[i]["箱码"].ToString().Trim();
model_BarCodeRD.RDType = 2;
//条形码数量超出单据数量
if (dQtyBarCode <= d)
{
if (d >= 0)
{
continue;
}
model_BarCodeRD.Qty = d;
dQtyScanSum = dQtyScanSum - d;
drCode[j]["本次扫描数量"] = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]) + d;
dQtyBarCode = dQtyBarCode - d;
}
else
{
model_BarCodeRD.Qty = dQtyBarCode;
dQtyScanSum = dQtyScanSum - dQtyBarCode;
drCode[j]["本次扫描数量"] = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]) + dQtyBarCode;
dQtyBarCode = 0;
}
sSQL = DAL_BarCodeRD.Add(model_BarCodeRD);
iCou = iCou + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
string sUpdate = "";
if (drCode[j]["货位"].ToString().Trim() == "" && drCode[j]["批号"].ToString().Trim() == "")
{
sUpdate = sUpdate + ",cDefine28 = '" + dtBarCode.Rows[i]["货位"].ToString().Trim() + "'";
sUpdate = sUpdate + ",cDefine30 = '" + dtBarCode.Rows[i]["批号"].ToString().Trim() + "'";
}
sSQL = "update rdrecords11 set cDefine29 = cast(isnull(cDefine29,0) as decimal(16,6)) + " + model_BarCodeRD.Qty.ToString() + " 111111 where autoid = " + model_BarCodeRD.ExsID.ToString();
if (sUpdate == "")
{
sSQL = sSQL.Replace("111111", "");
}
else
{
sSQL = sSQL.Replace("111111", sUpdate);
}
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update _BarCodeList set valid = 1 where BarCode = '" + model_BarCodeRD.BarCode + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
}
#endregion
if (dQtyScanSum != 0)
{
throw new Exception("扫描数量未完全分配");
}
if (iCou == 0)
{
throw new Exception("没有语句执行");
}
#region 判断所有数量扫描后审核单据
sSQL = "select getdate()";
DataTable dTime = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
DateTime dTimeNow = ClsBaseDataInfo.ReturnObjectToDatetime(dTime.Rows[0][0]);
sSQL = @"
select case when sum(cast(isnull(b.cDefine29,0) as decimal(16,6))) <> sum(b.iQuantity) then 1 else 0 end as iCou
from rdrecord11 a inner join rdrecords11 b on a.id = b.id
where a.cCode = '111111'
group by a.cCode
";
sSQL = sSQL.Replace("111111", sCode);
DataTable dtTemp2 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp2 != null && dtTemp2.Rows.Count == 1)
{
int iC = BaseFunction.ReturnInt(dtTemp2.Rows[0][0]);
if (iC == 0)
{
sSQL = "update rdrecord11 set cHandler = '222222',dVeriDate = '333333',dnverifytime = getdate() where cCode = '111111'";
sSQL = sSQL.Replace("111111", sCode);
sSQL = sSQL.Replace("222222", sUid);
sSQL = sSQL.Replace("333333", dTimeNow.ToString("yyyy-MM-dd"));
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//修改现存量
//sSQL = "exec SP_ClearCurrentStock_ST";
//DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
#endregion
tran.Commit();
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
return(iCou);
}
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);
}
/// <summary>
/// 保存发货单扫描数量
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public int iSaveDispatchListChkQty(string sCode, DataTable dtBarCode, string sUid)
{
int iCou = 0;
try
{
string sDLCode = sCode;
SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
decimal dQtyScanSum = 0; // 累计扫描数量
for (int i = 0; i < dtBarCode.Rows.Count; i++)
{
dQtyScanSum = dQtyScanSum + ClsBaseDataInfo.ReturnObjectToDecimal(dtBarCode.Rows[i]["数量"]);
}
string sSQL = "select a.cDLCode as cCode,b.iRowNo, b.cInvCode,b.iQuantity,b.cDefine31 as 已扫描数量,cast(null as decimal(16,6)) as 本次扫描数量,b.autoid,a.bReturnFlag as 红蓝字 from DispatchList a inner join DispatchLists b on a.DLID = b.DLID where a.cDLCode = '" + sCode + "' order by b.cInvCode";
DataTable dtDispatchList = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
bool bRed = false;
if (dtDispatchList != null && dtDispatchList.Rows.Count > 0 && BaseFunction.ReturnInt(dtDispatchList.Rows[0]["红蓝字"]) == 1)
{
bRed = true;
}
sSQL = "select getdate()";
DataTable dtTime = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
DateTime dtmNow = BaseFunction.ReturnDate(dtTime.Rows[0][0]);
string s_BarCodeRDCode = BaseFunction.ReturnDate(dtTime.Rows[0][0]).ToString("yyMMddHHmmss");
dtTime = null;
_BarCodeRD DAL_BarCodeRD = new _BarCodeRD();
_BarCodeList DAL_BarCodeList = new _BarCodeList();
if (!bRed)
{
for (int i = 0; i < dtBarCode.Rows.Count; i++)
{
string sInvCode = dtBarCode.Rows[i]["存货编码"].ToString().Trim();
decimal dQtyBarCode = BaseFunction.ReturnDecimal(dtBarCode.Rows[i]["数量"]);
//条形码中仅存货编码与入库单对应
DataRow[] drCode = dtDispatchList.Select("cInvCode = '" + sInvCode + "'");
for (int j = 0; j < drCode.Length; j++)
{
if (dQtyBarCode <= 0)
{
break;
}
decimal diQuantity = BaseFunction.ReturnDecimal(drCode[j]["iQuantity"]);
decimal dQtyScaned = BaseFunction.ReturnDecimal(drCode[j]["已扫描数量"]);
decimal dQtyNow = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]);
decimal d = diQuantity - dQtyScaned - dQtyNow;
Model._BarCodeRD model_BarCodeRD = new TH.WebService.Model._BarCodeRD();
model_BarCodeRD.sCode = s_BarCodeRDCode;
model_BarCodeRD.BarCode = dtBarCode.Rows[i]["条形码"].ToString().Trim();
model_BarCodeRD.sType = "销售发货单";
model_BarCodeRD.ExsID = BaseFunction.ReturnLong(drCode[j]["autoid"]);
model_BarCodeRD.ExCode = drCode[j]["cCode"].ToString().Trim();
model_BarCodeRD.ExRowNo = ClsBaseDataInfo.ReturnObjectToLong(drCode[j]["iRowNo"]);
model_BarCodeRD.cInvCode = drCode[j]["cInvCode"].ToString().Trim();
model_BarCodeRD.CreateUid = dtBarCode.Rows[i]["制单人"].ToString().Trim();
model_BarCodeRD.CreateDate = dtmNow;
model_BarCodeRD.XBarCode = dtBarCode.Rows[i]["箱码"].ToString().Trim();
model_BarCodeRD.RDType = 2;
//条形码数量超出单据数量
if (dQtyBarCode >= d)
{
model_BarCodeRD.Qty = d;
dQtyScanSum = dQtyScanSum - d;
drCode[j]["本次扫描数量"] = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]) + d;
dQtyBarCode = dQtyBarCode - d;
}
else
{
model_BarCodeRD.Qty = dQtyBarCode;
dQtyScanSum = dQtyScanSum - dQtyBarCode;
drCode[j]["本次扫描数量"] = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]) + dQtyBarCode;
dQtyBarCode = 0;
}
sSQL = DAL_BarCodeRD.Add(model_BarCodeRD);
iCou = iCou + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (model_BarCodeRD.XBarCode != "")
{
sSQL = @"select count(1) as iCou from _BarCodeRD where BarCode = '111111' and sType = '222222' and sCode = '333333'";
sSQL = sSQL.Replace("111111", model_BarCodeRD.XBarCode);
sSQL = sSQL.Replace("222222", model_BarCodeRD.sType);
sSQL = sSQL.Replace("333333", model_BarCodeRD.sCode);
DataTable dtCheckBox = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
int iCouBox = BaseFunction.ReturnInt(dtCheckBox.Rows[0][0]);
if (iCouBox == 0)
{
model_BarCodeRD.BarCode = dtBarCode.Rows[i]["箱码"].ToString().Trim();
sSQL = DAL_BarCodeRD.Add(model_BarCodeRD);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update _BarCodeList set valid = 0 where BarCode = '" + dtBarCode.Rows[i]["箱码"].ToString().Trim() + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
sSQL = "update DispatchLists set cDefine31 = cast(isnull(cDefine31,0) as decimal(16,6)) + " + model_BarCodeRD.Qty.ToString() + " where autoid = " + model_BarCodeRD.ExsID.ToString();
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update _BarCodeList set Used = 1 where BarCode = '" + model_BarCodeRD.BarCode + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//当可用量为0是，标记条形码失效
sSQL = DAL_BarCodeList.sBarCodeQty(model_BarCodeRD.BarCode);
DataTable dtQty = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtQty != null && dtQty.Rows.Count > 0)
{
decimal dQtyUsed = BaseFunction.ReturnDecimal(dtQty.Rows[0][0]);
if (dQtyUsed <= 0)
{
sSQL = "update _BarCodeList set valid = 0 where BarCode = '" + model_BarCodeRD.BarCode + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
}
}
}
else
{
for (int i = 0; i < dtBarCode.Rows.Count; i++)
{
string sInvCode = dtBarCode.Rows[i]["存货编码"].ToString().Trim();
decimal dQtyBarCode = BaseFunction.ReturnDecimal(dtBarCode.Rows[i]["数量"]);
//条形码中仅存货编码与入库单对应
DataRow[] drCode = dtDispatchList.Select("cInvCode = '" + sInvCode + "'");
for (int j = 0; j < drCode.Length; j++)
{
if (dQtyBarCode >= 0)
{
break;
}
decimal diQuantity = BaseFunction.ReturnDecimal(drCode[j]["iQuantity"]);
decimal dQtyScaned = BaseFunction.ReturnDecimal(drCode[j]["已扫描数量"]);
decimal dQtyNow = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]);
decimal d = diQuantity - dQtyScaned - dQtyNow;
Model._BarCodeRD model_BarCodeRD = new TH.WebService.Model._BarCodeRD();
model_BarCodeRD.sCode = s_BarCodeRDCode;
model_BarCodeRD.BarCode = dtBarCode.Rows[i]["条形码"].ToString().Trim();
model_BarCodeRD.sType = "销售发货单";
model_BarCodeRD.ExsID = BaseFunction.ReturnLong(drCode[j]["autoid"]);
model_BarCodeRD.ExCode = drCode[j]["cCode"].ToString().Trim();
model_BarCodeRD.ExRowNo = ClsBaseDataInfo.ReturnObjectToLong(drCode[j]["iRowNo"]);
model_BarCodeRD.cInvCode = drCode[j]["cInvCode"].ToString().Trim();
model_BarCodeRD.CreateUid = dtBarCode.Rows[i]["制单人"].ToString().Trim();
model_BarCodeRD.CreateDate = dtmNow;
model_BarCodeRD.XBarCode = dtBarCode.Rows[i]["箱码"].ToString().Trim();
model_BarCodeRD.RDType = 2;
//条形码数量超出单据数量
if (dQtyBarCode <= d)
{
model_BarCodeRD.Qty = d;
dQtyScanSum = dQtyScanSum - d;
drCode[j]["本次扫描数量"] = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]) + d;
dQtyBarCode = dQtyBarCode - d;
}
else
{
model_BarCodeRD.Qty = dQtyBarCode;
dQtyScanSum = dQtyScanSum - dQtyBarCode;
drCode[j]["本次扫描数量"] = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]) + dQtyBarCode;
dQtyBarCode = 0;
}
sSQL = DAL_BarCodeRD.Add(model_BarCodeRD);
iCou = iCou + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (model_BarCodeRD.XBarCode != "")
{
sSQL = @"select count(1) as iCou from _BarCodeRD where BarCode = '111111' and sType = '222222' and sCode = '333333'";
sSQL = sSQL.Replace("111111", model_BarCodeRD.XBarCode);
sSQL = sSQL.Replace("222222", model_BarCodeRD.sType);
sSQL = sSQL.Replace("333333", model_BarCodeRD.sCode);
DataTable dtCheckBox = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
int iCouBox = BaseFunction.ReturnInt(dtCheckBox.Rows[0][0]);
if (iCouBox == 0)
{
model_BarCodeRD.BarCode = dtBarCode.Rows[i]["箱码"].ToString().Trim();
sSQL = DAL_BarCodeRD.Add(model_BarCodeRD);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update _BarCodeList set valid = 0 where BarCode = '" + dtBarCode.Rows[i]["箱码"].ToString().Trim() + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
sSQL = "update DispatchLists set cDefine31 = cast(isnull(cDefine31,0) as decimal(16,6)) + " + model_BarCodeRD.Qty.ToString() + " where autoid = " + model_BarCodeRD.ExsID.ToString();
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update _BarCodeList set Used = 1 where BarCode = '" + model_BarCodeRD.BarCode + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//当可用量为0是，标记条形码失效
sSQL = DAL_BarCodeList.sBarCodeQty(model_BarCodeRD.BarCode);
DataTable dtQty = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtQty != null && dtQty.Rows.Count > 0)
{
decimal dQtyUsed = BaseFunction.ReturnDecimal(dtQty.Rows[0][0]);
if (dQtyUsed >= 0)
{
sSQL = "update _BarCodeList set valid = 1 where BarCode = '" + model_BarCodeRD.BarCode + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
}
}
}
if (dQtyScanSum != 0)
{
throw new Exception("扫描数量未完全分配");
}
if (iCou == 0)
{
throw new Exception("没有语句执行");
}
#region 判断所有数量扫描后审核单据
sSQL = "select getdate()";
DataTable dTime = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
DateTime dTimeNow = ClsBaseDataInfo.ReturnObjectToDatetime(dTime.Rows[0][0]);
sSQL = @"
select case when sum(cast(isnull(b.cDefine31,0) as decimal(16,6))) <> sum(b.iQuantity ) then 1 else 0 end as iCou
from DispatchList a inner join DispatchLists b on a.dlid = b.dlid
where a.cDLCode = '111111'
group by a.cDLCode
";
//仓库不同，分多张出库单
sSQL = sSQL.Replace("111111", sCode);
DataTable dtTemp2 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp2 != null && dtTemp2.Rows.Count == 1)
{
int iC = BaseFunction.ReturnInt(dtTemp2.Rows[0][0]);
if (iC == 0)
{
sSQL = @"
Update DispatchList SET cVerifier=N'222222',cChanger=null,dverifydate='333333' ,dverifysystime=getdate()
WHERE DispatchList.cDLCode = '111111'
";
//sSQL = "update TransVouch set cVerifyPerson = '222222',dVerifyDate = '333333',dnverifytime = getdate() where cTVCode = '111111'";
sSQL = sSQL.Replace("111111", sCode);
sSQL = sSQL.Replace("222222", sUid);
sSQL = sSQL.Replace("333333", dTimeNow.ToString("yyyy-MM-dd"));
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "select distinct b.cWhCode from DispatchList a inner join DispatchLists b on a.dlid = b.dlid where a.cDLCode = '111111'";
sSQL = sSQL.Replace("111111", sCode);
DataTable dtcWhCode = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
for (int ii = 0; ii < dtcWhCode.Rows.Count; ii++)
{
sSQL = @"
select *
from DispatchList a inner join DispatchLists b on a.dlid = b.dlid
where a.cDLCode = '111111' and b.cWhCode = '222222'
order by cWhCode ,autoid
";
sSQL = sSQL.Replace("111111", sDLCode);
sSQL = sSQL.Replace("222222", dtcWhCode.Rows[ii]["cWhCode"].ToString().Trim());
DataTable dtTran = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
int iCouRow = dtTran.Rows.Count;
#region 生成销售出库单据
//获得单据号
sSQL = "select cNumber from VoucherHistory Where CardNumber='0303' and cSeed = '" + dTimeNow.ToString("yyyyMM") + "'";
DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
long lCode = 0;
if (dt != null && dt.Rows.Count > 0)
{
lCode = BaseFunction.ReturnLong(dt.Rows[0]["cNumber"]);
lCode += 1;
sSQL = "update VoucherHistory set cNumber = '" + lCode.ToString() + "' Where CardNumber='0303' and cSeed = '" + dTimeNow.ToString("yyyyMM") + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
else
{
lCode = 1;
sSQL = "insert into VoucherHistory(CardNumber,cContent,cContentRule,cSeed,cNumber,bEmpty)" +
"values('0303','日期','月','" + dTimeNow.ToString("yyyyMM") + "','1',0)";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
sCode = lCode.ToString();
while (sCode.Length < 5)
{
sCode = "0" + sCode;
}
sCode = "XC" + dTimeNow.ToString("yyMM") + sCode;
long lID = 0;
long lIDDetails = 0;
sSQL = @"
declare @p5 int
set @p5=111111
declare @p6 int
set @p6=222222
exec sp_GetId N'',N'444444',N'rd',333333,@p5 output,@p6 output,default
select @p5, @p6
";
sSQL = sSQL.Replace("111111", lID.ToString());
sSQL = sSQL.Replace("222222", lIDDetails.ToString());
sSQL = sSQL.Replace("333333", iCouRow.ToString());
sSQL = sSQL.Replace("444444", sAccID);
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
lID = ClsBaseDataInfo.ReturnObjectToLong(dt.Rows[0][0]) - 1;
lIDDetails = ClsBaseDataInfo.ReturnObjectToLong(dt.Rows[0][1]) - iCouRow;
lID += 1;
Model.RdRecord32 modelr32 = new TH.WebService.Model.RdRecord32();
modelr32.ID = lID;
modelr32.bRdFlag = 1;
modelr32.cVouchType = "32";
modelr32.cBusType = "普通销售";
modelr32.cSource = "发货单";
modelr32.cBusCode = sDLCode;
modelr32.cWhCode = dtTran.Rows[0]["cWhCode"].ToString().Trim();
modelr32.dDate = BaseFunction.ReturnDate(dTimeNow.ToString("yyyy-MM-dd"));
modelr32.cCode = sCode;
modelr32.cRdCode = dtTran.Rows[0]["cRdCode"].ToString().Trim();
modelr32.cDepCode = dtTran.Rows[0]["cDepCode"].ToString().Trim();
modelr32.cCusCode = dtTran.Rows[0]["cCusCode"].ToString().Trim();
modelr32.cPersonCode = dtTran.Rows[0]["cPersonCode"].ToString().Trim();
modelr32.cSTCode = dtTran.Rows[0]["cSTCode"].ToString().Trim();
modelr32.cDLCode = TH.WebService.BaseClass.ClsBaseDataInfo.ReturnObjectToLong(dtTran.Rows[0]["DLID"]);
modelr32.bTransFlag = false;
modelr32.cMaker = sUid;
modelr32.bpufirst = false;
modelr32.biafirst = false;
modelr32.VT_ID = 327;
modelr32.bIsSTQc = false;
modelr32.bOMFirst = false;
modelr32.bFromPreYear = false;
modelr32.bIsComplement = 0;
modelr32.iDiscountTaxType = 0;
modelr32.ireturncount = 0;
modelr32.iverifystate = 0;
modelr32.iswfcontrolled = 0;
modelr32.dnmaketime = dTimeNow;
modelr32.bredvouch = 0;
DAL.rdrecord32 DALr32 = new rdrecord32();
sSQL = DALr32.Add(modelr32);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
for (int i = 0; i < dtTran.Rows.Count; i++)
{
Model.RdRecords32 modelr32s = new TH.WebService.Model.RdRecords32();
lIDDetails += 1;
modelr32s.AutoID = lIDDetails;
modelr32s.ID = lID;
modelr32s.cInvCode = dtTran.Rows[i]["cInvCode"].ToString().Trim();
modelr32s.iQuantity = BaseFunction.ReturnDecimal(dtTran.Rows[i]["iQuantity"]);
modelr32s.iNum = BaseFunction.ReturnDecimal(dtTran.Rows[i]["iNum"]);
modelr32s.irowno = BaseFunction.ReturnInt(dtTran.Rows[i]["irowno"]);
modelr32s.iDLsID = BaseFunction.ReturnLong(dtTran.Rows[i]["iDLsID"]);
DAL.rdrecords32 DALr32s = new rdrecords32();
sSQL = DALr32s.Add(modelr32s);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "insert into IA_ST_UnAccountVouch32(idun,idsun,cvoutypeun,cbustypeun)values " +
"('" + lID + "','" + lIDDetails + "','32','调拨入库')";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
#endregion
//回写发货数量
sSQL = "update dispatchlists with (UPDLOCK) set fOutQuantity=iQuantity, fOutNum=iNum where DLID = " + dtTran.Rows[0]["DLID"].ToString().Trim();
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//回写发货单表头
sSQL = " Update DispatchList SET cSaleOut=N'" + sCode + "' WHERE DispatchList.cDLCode ='" + sDLCode + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
//sSQL = "exec SP_ClearCurrentStock_ST";
//DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
#endregion
tran.Commit();
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
return(iCou);
}
/// <summary>
/// 保存盘点单扫描数量
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public int iSaveGetCheckVouchChkQty(string sCode, DataTable dtBarCode)
{
int iCou = 0;
try
{
SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSQL = "select a.cCVCode as cCode,b.irowno,b.cInvCode ,b.cDefine29 as iQuantity,b.cDefine29 as 已扫描数量,cast(null as decimal(16,6)) as 本次扫描数量,b.autoid from CheckVouch a inner join CheckVouchs b on a.ID = b.ID where a.cCVCode = '" + sCode + "' order by b.cInvCode";
DataTable dtRdRecord11 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
sSQL = "select getdate()";
DataTable dtTime = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
DateTime dtmNow = BaseFunction.ReturnDate(dtTime.Rows[0][0]);
string s_BarCodeRDCode = BaseFunction.ReturnDate(dtTime.Rows[0][0]).ToString("yyMMddHHmmss");
dtTime = null;
_BarCodeRD DAL_BarCodeRD = new _BarCodeRD();
_BarCodeList DAL_BarCodeList = new _BarCodeList();
for (int i = 0; i < dtBarCode.Rows.Count; i++)
{
string sInvCode = dtBarCode.Rows[i]["存货编码"].ToString().Trim();
decimal dQtyBarCode = BaseFunction.ReturnDecimal(dtBarCode.Rows[i]["数量"]);
//条形码中仅存货编码与入库单对应
DataRow[] drCode = dtRdRecord11.Select("cInvCode = '" + sInvCode + "'");
for (int j = 0; j < drCode.Length; j++)
{
if (dQtyBarCode <= 0)
{
break;
}
sSQL = "select count(1) from _BarCodeRD where BarCode = '" + dtBarCode.Rows[i]["条形码"].ToString().Trim() + "' and ExCode = '" + sCode + "' and sType = '盘点单' ";
DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
long iBarCou = BaseFunction.ReturnLong(dt.Rows[0][0]);
if (iBarCou > 0)
{
throw new Exception(dtBarCode.Rows[i]["条形码"].ToString().Trim() + "已经使用");
}
decimal diQuantity = BaseFunction.ReturnDecimal(drCode[j]["iQuantity"]);
decimal dQtyScaned = BaseFunction.ReturnDecimal(drCode[j]["已扫描数量"]);
decimal dQtyNow = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]);
decimal d = diQuantity - dQtyScaned - dQtyNow;
Model._BarCodeRD model_BarCodeRD = new TH.WebService.Model._BarCodeRD();
model_BarCodeRD.sCode = s_BarCodeRDCode;
model_BarCodeRD.BarCode = dtBarCode.Rows[i]["条形码"].ToString().Trim();
model_BarCodeRD.sType = "盘点单";
model_BarCodeRD.ExsID = BaseFunction.ReturnLong(drCode[j]["autoid"]);
model_BarCodeRD.ExCode = drCode[j]["cCode"].ToString().Trim();
model_BarCodeRD.ExRowNo = ClsBaseDataInfo.ReturnObjectToLong(drCode[j]["iRowNo"]);
model_BarCodeRD.cInvCode = drCode[j]["cInvCode"].ToString().Trim();
model_BarCodeRD.CreateUid = dtBarCode.Rows[i]["制单人"].ToString().Trim();
model_BarCodeRD.CreateDate = dtmNow;
model_BarCodeRD.XBarCode = dtBarCode.Rows[i]["箱码"].ToString().Trim();
model_BarCodeRD.RDType = 0;
model_BarCodeRD.Qty = dQtyBarCode;
drCode[j]["本次扫描数量"] = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]) + dQtyBarCode;
dQtyBarCode = 0;
sSQL = DAL_BarCodeRD.Add(model_BarCodeRD);
iCou = iCou + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update CheckVouchs set cDefine29 = cast(isnull(cDefine29,0) as decimal(16,6)) + " + model_BarCodeRD.Qty.ToString() + " where autoid = " + model_BarCodeRD.ExsID.ToString();
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
if (iCou == 0)
{
throw new Exception("没有语句执行");
}
sSQL = "update CheckVouchs set iCVCQuantity = cast(isnull(cDefine29,0) as decimal(16,6)) where cCVCode = '" + sCode + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
tran.Commit();
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
return(iCou);
}
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销售出库单2(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("当前月份已经结账");
}
for (int i = 0; i < dtData.Rows.Count; i++)
{
//判断条码是否已经使用
sSQL = "select * from rdrecords32 where BarCode = '" + 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;
}
long lIDsEx = ClsBaseClass.ClsBaseDataInfo.ReturnObjectToLong(dtData.Rows[i]["单据体ID"]);
sSQL = @"
select rd.cWhCode,rd.cCode,rd.id
,rds.iQuantity,rds.cBatch,rds.cFree1,rds.BarCode,rds.iDLsID,rds.autoid,rds.cInvCode
,wh.bWhPos,Pos.iQty
from rdrecord32 rd inner join rdrecords32 rds on rd.id = rds.id
inner join dbo.Warehouse wh ON rd.cWhCode = wh.cWhCode
left join (select rdid,rdsid,sum(iQuantity) as iQty from InvPosition group by rdid,rdsid) Pos on Pos.rdid = rd.id and Pos.rdsid = rds.autoid
where rds.Autoid = " + lIDsEx.ToString();
DataTable dtRds32ex = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtRds32ex == null || dtRds32ex.Rows.Count == 0)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + " 对应单据不存在\n";
continue;
}
if (ClsBaseDataInfo.ReturnObjectToDecimal(dtRds32ex.Rows[0]["iQuantity"]) == ClsBaseDataInfo.ReturnObjectToDecimal(dtRds32ex.Rows[0]["iQty"]))
{
sErr = sErr + "单据已扫描出库或手工出库\n";
continue;
}
//if (dtRds32ex.Rows[0]["cBatch"].ToString().Trim() == dtData.Rows[i]["批号"].ToString().Trim())
//{
// continue;
//}
string s仓库 = dtRds32ex.Rows[0]["cWhCode"].ToString().Trim();
string s批号ex = dtRds32ex.Rows[0]["cBatch"].ToString().Trim();
string s存货编码 = dtRds32ex.Rows[0]["cInvCode"].ToString().Trim();
string s长度 = dtData.Rows[i]["长度"].ToString().Trim();
decimal d数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"], 2);
decimal d数量ex = ClsBaseDataInfo.ReturnObjectToDecimal(dtRds32ex.Rows[0]["iQuantity"], 2);
string siDLsID = dtRds32ex.Rows[0]["iDLsID"].ToString().Trim();
long lID = ClsBaseClass.ClsBaseDataInfo.ReturnObjectToLong(dtRds32ex.Rows[0]["id"]);
long lIDDetail = ClsBaseClass.ClsBaseDataInfo.ReturnObjectToLong(dtRds32ex.Rows[0]["AutoID"]);
sSQL = "select * from Inventory where cInvCode = '" + s存货编码 + "'";
DataTable dt存货信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
decimal d件数 = 1;
sSQL = "update rdrecords32 set iQuantity = aaaaaa,cBatch = 'bbbbbb',BarCode = 'cccccc' where AutoID = " + lIDsEx.ToString().Trim();
sSQL = sSQL.Replace("aaaaaa", d数量.ToString());
sSQL = sSQL.Replace("bbbbbb", dtData.Rows[i]["批号"].ToString());
sSQL = sSQL.Replace("cccccc", dtData.Rows[i]["条形码"].ToString());
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//1. 更新现存量--当前出库信息
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);
//2. 更新现存量--历史出库信息改回
sSQL = "declare @itmeid int " +
"select @itmeid = MAX(ItemId) + 1 from CurrentStock " +
"if exists(select * from CurrentStock where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = '" + dtRds32ex.Rows[0]["cBatch"].ToString().Trim() + "' and cWhCode = '" + s仓库 + "' and isnull(cFree1,'') = '" + dtData.Rows[i]["长度"].ToString().Trim() + "') " +
" update CurrentStock set iQuantity = isnull(iQuantity,0) + " + ClsBaseDataInfo.ReturnCol(d数量ex) + ",iNum = isnull(iNum,0) + 1 where cinvcode = '" + s存货编码 + "' and isnull(cBatch,'') = isnull(" + ClsBaseDataInfo.ReturnCol(dtRds32ex.Rows[0]["cBatch"].ToString().Trim()) + ",'') 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(dtRds32ex.Rows[0]["cBatch"].ToString().Trim()) + ",0," + ClsBaseDataInfo.ReturnCol(d数量ex) + ",1," + lItemID + "," + ClsBaseDataInfo.ReturnCol(dtData.Rows[i]["长度"].ToString()) + ")";
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = @"UPDATE a SET a.fOutNum = b.iQty,a.fOutQuantity = b.iNum,iQuantity = b.iQty ,iNum = b.iNum
FROM DispatchLists a
INNER JOIN (
SELECT SUM(iQuantity) AS iQty,SUM(iNum) AS iNum,iDLsID
FROM dbo.rdrecords32
GROUP BY iDLsID
) b ON a.AutoID = b.iDLsID
WHERE b.iDLsID = aaaaaa
" ;
sSQL = sSQL.Replace("aaaaaa", siDLsID);
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", siDLsID);
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
//如果不标记，销售出库单就不能手工删除，出现“此单据不是库存生成的，不能删除”的错误提示
sSQL = "update DispatchList set cSaleOut = 'ST' where DLID in (select DLID from DispatchLists where Autoid = aaaaaa)";
sSQL = sSQL.Replace("aaaaaa", siDLsID);
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (ClsBaseClass.ClsBaseDataInfo.ReturnObjectToInt(dtRds32ex.Rows[0]["bWhPos"]) == 1)
{
//货位登记
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,'" + dtData.Rows[i]["制单人"].ToString() + "','" + 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);
}
}
if (sErr.Trim().Length > 0)
{
throw new Exception(sErr);
}
tran.Commit();
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='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);
}
/// <summary>
/// 条码验证
/// </summary>
/// <param name="dtData"></param>
/// <returns></returns>
public string Save采购入库单2(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("当前月份已经结账");
}
for (int i = 0; i < dtData.Rows.Count; i++)
{
string s条形码 = dtData.Rows[i]["条形码"].ToString().Trim();
if (s条形码.Length != 10)
{
sErr = sErr + "条形码：" + s条形码 + " 长度不正确\n";
continue;
}
sSQL = @"
SELECT *
FROM dbo.rdrecord01 a inner join rdrecords01 b on a.id = b.id
left join (select sum(iQuantity) as iQty,RdsID from InvPosition group by RdsID) rdsp on b.autoid = rdsp.RdsID
INNER JOIN dbo.条形码信息 c ON a.ccode = c.采购入库单号 AND b.irowno = c.采购入库单行号
WHERE c.条形码 = 'aaaaaa'
";
sSQL = sSQL.Replace("aaaaaa", s条形码);
dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp == null || dtTemp.Rows.Count == 0)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + " 对应单据" + dtTemp.Rows[0]["cCode"].ToString() + "不存在\r\n";
continue;
}
if (dtTemp.Rows[0]["cHandler"].ToString().Trim() != "")
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + " 对应单据" + dtTemp.Rows[0]["cCode"].ToString() + "已审核\r\n";
continue;
}
if (dtTemp.Rows[0]["cAccounter"].ToString().Trim() != "")
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + " 对应单据" + dtTemp.Rows[0]["cCode"].ToString() + "已记账\r\n";
continue;
}
decimal d单据未入货位数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dtTemp.Rows[0]["iQuantity"]) - ClsBaseDataInfo.ReturnObjectToDecimal(dtTemp.Rows[0]["iQty"]);
//判断条码是否已经使用
sSQL = "select * from rdrecords01 where BarCode = '" + s条形码 + "' and isnull(iQuantity,0) > 0 ";
int iCou = ClsBaseDataInfo.ReturnObjectToInt(SqlHelper.ExecuteScalar(tran, CommandType.Text, sSQL));
if (iCou > 0)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + "已经使用\n";
continue;
}
sSQL = @"
select a.*,b.cInvName,b.cInvStd,b.bInvBatch,e.cVenCode,e.iQuantity,e.iNum,e.cWhCode,isnull(g.bWhPos ,0) as bWhPos,RdsID,RdID,b.cInvCode,e.cBatch ,b.cAssComUnitCode
,f.cVenName
from 条形码信息 a inner join Inventory b on a.存货编码 = b.cInvCode
inner join (
select a.cCode,b.irowno, d.cVenCode,b.cInvCode,b.iQuantity,b.iNum,a.cWhCode,b.AutoID as RdsID,a.id as RdID,b.cBatch
FROM dbo.RdRecord01 a INNER JOIN dbo.rdrecords01 b ON a.id = b.id
INNER JOIN dbo.PO_Podetails c ON c.ID = b.iPOsID
INNER JOIN dbo.PO_Pomain d ON c.POID = d.POID
) e on e.cInvCode = a.存货编码 AND a.采购入库单号 = e.cCode AND a.采购入库单行号 = e.irowno
inner join Vendor f on e.cVenCode = f.cVenCode
inner join Warehouse g on g.cWhCode = e.cWhCode
where a.条形码 = '222222'
";
sSQL = sSQL.Replace("222222", s条形码);
DataTable dt条码信息 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt条码信息 == null || dt条码信息.Rows.Count == 0)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + " 信息获取失败\n";
continue;
}
if (Convert.ToInt32(dt条码信息.Rows[0]["bWhPos"]) == 0)
{
sSQL = "update rdrecords01 set BarCode = '" + s条形码 + "' where autoid = " + dt条码信息.Rows[0]["RdsID"].ToString().Trim();
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
else
{
sSQL = "update rdrecords01 set BarCode = '" + s条形码 + "',cPosition = '" + dtData.Rows[i]["货位"].ToString().Trim() + "' where autoid = " + dt条码信息.Rows[0]["RdsID"].ToString().Trim();
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
string s仓库 = dt条码信息.Rows[0]["cWhCode"].ToString().Trim();
string lID = dt条码信息.Rows[0]["RdID"].ToString().Trim();
string lIDDetail = dt条码信息.Rows[0]["RdsID"].ToString().Trim();
string s存货编码 = dt条码信息.Rows[0]["cInvCode"].ToString().Trim();
decimal d数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dt条码信息.Rows[0]["iQuantity"]);
decimal d件数 = ClsBaseDataInfo.ReturnObjectToDecimal(dt条码信息.Rows[0]["iNum"]);
string s制单人 = dtData.Rows[i]["制单人"].ToString().Trim();
if (d单据未入货位数量 != d数量)
{
sErr = sErr + "条形码:" + dtData.Rows[i]["条形码"].ToString() + " 数量与单据不匹配\n";
continue;
}
//货位登记
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);
}
}
if (sErr.Trim().Length > 0)
{
throw new Exception(sErr);
}
tran.Commit();
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 sCode)
{
string s = "";
try
{
string sErr = "";
string sInfo = "";
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.判断单据是否审核
sSQL = "select * from CheckVouch where cCVCode = '" + sCode + "' ";
dtTemp = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp == null || dtTemp.Rows.Count < 1)
{
throw new Exception("获取盘点单失败");
}
string scAccounter = dtTemp.Rows[0]["cAccounter"].ToString().Trim();
if (scAccounter != "")
{
throw new Exception("单据已经审核");
}
long lID = ClsBaseClass.ClsBaseDataInfo.ReturnObjectToLong(dtTemp.Rows[0]["ID"]);
for (int i = 0; i < dtData.Rows.Count; i++)
{
string s存货编码 = dtData.Rows[i]["存货编码"].ToString().Trim();
string s长度 = dtData.Rows[i]["长度"].ToString().Trim();
string s批号 = dtData.Rows[i]["批号"].ToString().Trim();
string s条形码 = dtData.Rows[i]["条形码"].ToString().Trim();
sSQL = @"
select b.*
from CheckVouch a inner join CheckVouchs b on a.id = b.id
where a.cCVCode = 'aaaaaa' AND b.cInvCode = 'bbbbbb' AND ISNULL(b.cCVBatch,'') = 'cccccc' AND ISNULL(b.cFree1,'') = 'dddddd'
";
sSQL = sSQL.Replace("aaaaaa", sCode);
sSQL = sSQL.Replace("bbbbbb", s存货编码);
sSQL = sSQL.Replace("cccccc", s批号);
sSQL = sSQL.Replace("dddddd", s长度);
DataTable dt盘点 = SqlHelper.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt盘点 == null)
{
sErr = sErr + "条形码 " + s条形码 + " 获得单据失败\n";
continue;
}
if (dt盘点.Rows.Count == 0)
{
sInfo = sInfo + "条形码 " + s条形码 + "\n";
continue;
}
decimal d数量 = ClsBaseDataInfo.ReturnObjectToDecimal(dtData.Rows[i]["数量"]);
decimal d件数 = 0;
long lIDs = ClsBaseDataInfo.ReturnObjectToLong(dt盘点.Rows[0]["autoid"]);
sSQL = "update CheckVouchs set iCVCQuantity = " + d数量 + ",iCVCNum = " + d件数 + " ,BarCode = '" + s条形码 + "' where autoid = " + lIDs.ToString();
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
sSQL = "update CheckVouchs set iCVCQuantity = 0,iCVCNum = 0 where isnull(BarCode,'') = '' and ID = " + lID.ToString();
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (sErr.Length > 0)
{
throw new Exception(sErr);
}
tran.Commit();
s = "保存" + sCode + "盘点信息成功";
if (sInfo.Length > 0)
{
s = s + "\n 以下条形码在盘点单没有信息，请手工输入:\n" + sInfo;
}
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
s = ee.Message;
}
return(s);
}
/// <summary>
/// 保存调拨单扫描数量
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public int iSaveGetTransVouchChkQty(string sCode, DataTable dtBarCode, string sUid)
{
int iCou = 0;
try
{
string sTrCode = sCode;
SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
decimal dQtyScanSum = 0; // 累计扫描数量
for (int i = 0; i < dtBarCode.Rows.Count; i++)
{
dQtyScanSum = dQtyScanSum + ClsBaseDataInfo.ReturnObjectToDecimal(dtBarCode.Rows[i]["数量"]);
}
string sSQL = "select a.cTVCode as cCode,b.irowno,b.cInvCode ,b.iTVQuantity as iQuantity,b.cDefine29 as 已扫描数量,cast(null as decimal(16,6)) as 本次扫描数量,b.autoid from TransVouch a inner join TransVouchs b on a.cTVCode = b.cTVCode where a.cTVCode = '" + sCode + "' order by b.cInvCode";
DataTable dtRdRecord11 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
sSQL = "select getdate()";
DataTable dtTime = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
DateTime dtmNow = BaseFunction.ReturnDate(dtTime.Rows[0][0]);
string s_BarCodeRDCode = BaseFunction.ReturnDate(dtTime.Rows[0][0]).ToString("yyMMddHHmmss");
dtTime = null;
_BarCodeRD DAL_BarCodeRD = new _BarCodeRD();
_BarCodeList DAL_BarCodeList = new _BarCodeList();
for (int i = 0; i < dtBarCode.Rows.Count; i++)
{
string sInvCode = dtBarCode.Rows[i]["存货编码"].ToString().Trim();
decimal dQtyBarCode = BaseFunction.ReturnDecimal(dtBarCode.Rows[i]["数量"]);
//条形码中仅存货编码与入库单对应
DataRow[] drCode = dtRdRecord11.Select("cInvCode = '" + sInvCode + "'");
for (int j = 0; j < drCode.Length; j++)
{
if (dQtyBarCode <= 0)
{
break;
}
decimal diQuantity = BaseFunction.ReturnDecimal(drCode[j]["iQuantity"]);
decimal dQtyScaned = BaseFunction.ReturnDecimal(drCode[j]["已扫描数量"]);
decimal dQtyNow = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]);
decimal d = diQuantity - dQtyScaned - dQtyNow;
Model._BarCodeRD model_BarCodeRD = new TH.WebService.Model._BarCodeRD();
model_BarCodeRD.sCode = s_BarCodeRDCode;
model_BarCodeRD.BarCode = dtBarCode.Rows[i]["条形码"].ToString().Trim();
model_BarCodeRD.sType = "调拨单";
model_BarCodeRD.ExsID = BaseFunction.ReturnLong(drCode[j]["autoid"]);
model_BarCodeRD.ExCode = drCode[j]["cCode"].ToString().Trim();
model_BarCodeRD.ExRowNo = ClsBaseDataInfo.ReturnObjectToLong(drCode[j]["iRowNo"]);
model_BarCodeRD.cInvCode = drCode[j]["cInvCode"].ToString().Trim();
model_BarCodeRD.CreateUid = dtBarCode.Rows[i]["制单人"].ToString().Trim();
model_BarCodeRD.CreateDate = dtmNow;
model_BarCodeRD.XBarCode = dtBarCode.Rows[i]["箱码"].ToString().Trim();
model_BarCodeRD.RDType = 0;
//条形码数量超出单据数量
if (dQtyBarCode >= d)
{
model_BarCodeRD.Qty = d;
dQtyScanSum = dQtyScanSum - d;
drCode[j]["本次扫描数量"] = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]) + d;
dQtyBarCode = dQtyBarCode - d;
}
else
{
model_BarCodeRD.Qty = dQtyBarCode;
dQtyScanSum = dQtyScanSum - dQtyBarCode;
drCode[j]["本次扫描数量"] = BaseFunction.ReturnDecimal(drCode[j]["本次扫描数量"]) + dQtyBarCode;
dQtyBarCode = 0;
}
sSQL = DAL_BarCodeRD.Add(model_BarCodeRD);
iCou = iCou + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "update TransVouchs set cDefine29 = cast(isnull(cDefine29,0) as decimal(16,6)) + " + model_BarCodeRD.Qty.ToString() + " where autoid = " + model_BarCodeRD.ExsID.ToString();
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
if (dQtyScanSum != 0)
{
throw new Exception("扫描数量未完全分配");
}
if (iCou == 0)
{
throw new Exception("没有语句执行");
}
#region 判断所有数量扫描后审核单据
sSQL = "select getdate()";
DataTable dTime = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
DateTime dTimeNow = ClsBaseDataInfo.ReturnObjectToDatetime(dTime.Rows[0][0]);
sSQL = @"
select case when sum(cast(isnull(b.cDefine29,0) as decimal(16,6))) <> sum(b.iTVQuantity) then 1 else 0 end as iCou
from TransVouch a inner join TransVouchs b on a.cTVCode = b.cTVCode
where a.cTVCode = '111111'
group by a.cTVCode
";
sSQL = sSQL.Replace("111111", sCode);
DataTable dtTemp2 = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp2 != null && dtTemp2.Rows.Count == 1)
{
int iC = BaseFunction.ReturnInt(dtTemp2.Rows[0][0]);
if (iC == 0)
{
sSQL = "update TransVouch set cVerifyPerson = '222222',dVerifyDate = '333333',dnverifytime = getdate() where cTVCode = '111111'";
sSQL = sSQL.Replace("111111", sCode);
sSQL = sSQL.Replace("222222", sUid);
sSQL = sSQL.Replace("333333", dTimeNow.ToString("yyyy-MM-dd"));
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = @"
select *
from TransVouch a inner join TransVouchs b on a.cTVCode = b.cTVCode
where a.cTVCode = '111111'
order by autoid
";
sSQL = sSQL.Replace("111111", sCode);
DataTable dtTran = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
int iCouRow = dtTran.Rows.Count;
#region 生成其他入库单据
//获得单据号
sSQL = "select cNumber from VoucherHistory Where CardNumber='0301' and cSeed = '" + dTimeNow.ToString("yyyyMM") + "'";
DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
long lCode = 0;
if (dt != null && dt.Rows.Count > 0)
{
lCode = BaseFunction.ReturnLong(dt.Rows[0]["cNumber"]);
lCode += 1;
sSQL = "update VoucherHistory set cNumber = '" + lCode.ToString() + "' Where CardNumber='0301' and cSeed = '" + dTimeNow.ToString("yyyyMM") + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
else
{
lCode = 1;
sSQL = "insert into VoucherHistory(CardNumber,cContent,cContentRule,cSeed,cNumber,bEmpty)" +
"values('0301','日期','月','" + dTimeNow.ToString("yyyyMM") + "','1',0)";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
sCode = lCode.ToString();
while (sCode.Length < 5)
{
sCode = "0" + sCode;
}
sCode = "QR" + dTimeNow.ToString("yyMM") + sCode;
long lID = 0;
long lIDDetails = 0;
sSQL = @"
declare @p5 int
set @p5=111111
declare @p6 int
set @p6=222222
exec sp_GetId N'',N'444444',N'rd',333333,@p5 output,@p6 output,default
select @p5, @p6
";
sSQL = sSQL.Replace("111111", lID.ToString());
sSQL = sSQL.Replace("222222", lIDDetails.ToString());
sSQL = sSQL.Replace("333333", iCouRow.ToString());
sSQL = sSQL.Replace("444444", sAccID);
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
lID = ClsBaseDataInfo.ReturnObjectToLong(dt.Rows[0][0]) - 1;
lIDDetails = ClsBaseDataInfo.ReturnObjectToLong(dt.Rows[0][1]) - iCouRow;
lID += 1;
Model.RdRecord08 modelr8 = new TH.WebService.Model.RdRecord08();
modelr8.ID = lID;
modelr8.bRdFlag = 1;
modelr8.cVouchType = "08";
modelr8.cBusType = "调拨入库";
modelr8.cSource = "调拨";
modelr8.cBusCode = sTrCode;
modelr8.cWhCode = dtTran.Rows[0]["cIWhCode"].ToString().Trim();
modelr8.dDate = BaseFunction.ReturnDate(dTimeNow.ToString("yyyy-MM-dd"));
modelr8.cCode = sCode;
modelr8.cRdCode = dtTran.Rows[0]["cIRdCode"].ToString().Trim();
modelr8.cDepCode = dtTran.Rows[0]["cIDepCode"].ToString().Trim();
modelr8.bTransFlag = false;
modelr8.cMaker = sUid;
modelr8.bpufirst = false;
modelr8.biafirst = false;
modelr8.VT_ID = 67;
modelr8.bIsSTQc = false;
modelr8.bOMFirst = false;
modelr8.bFromPreYear = false;
modelr8.bIsComplement = 0;
modelr8.iDiscountTaxType = 0;
modelr8.ireturncount = 0;
modelr8.iverifystate = 0;
modelr8.iswfcontrolled = 0;
modelr8.dnmaketime = dTimeNow;
modelr8.bredvouch = 0;
modelr8.iPrintCount = 0;
DAL.RdRecord08 DALr8 = new RdRecord08();
sSQL = DALr8.Add(modelr8);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
for (int i = 0; i < dtTran.Rows.Count; i++)
{
Model.rdrecords08 modelr8s = new TH.WebService.Model.rdrecords08();
lIDDetails += 1;
modelr8s.AutoID = lIDDetails;
modelr8s.ID = lID;
modelr8s.cInvCode = dtTran.Rows[i]["cInvCode"].ToString().Trim();
modelr8s.iQuantity = BaseFunction.ReturnDecimal(dtTran.Rows[i]["iTVQuantity"]);
modelr8s.iNum = BaseFunction.ReturnDecimal(dtTran.Rows[i]["iTVNum"]);
modelr8s.iTrIds = BaseFunction.ReturnLong(dtTran.Rows[i]["autoID"]);
modelr8s.iNQuantity = BaseFunction.ReturnDecimal(dtTran.Rows[i]["iTVQuantity"]);
modelr8s.irowno = BaseFunction.ReturnInt(dtTran.Rows[i]["irowno"]);
DAL.rdrecords08 DALr8s = new rdrecords08();
sSQL = DALr8s.Add(modelr8s);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "insert into IA_ST_UnAccountVouch08(idun,idsun,cvoutypeun,cbustypeun)values " +
"('" + lID + "','" + lIDDetails + "','08','调拨入库')";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
#endregion
#region 生成其他出库单据
//获得单据号
sSQL = "select cNumber from VoucherHistory Where CardNumber='0302' and cSeed = '" + dTimeNow.ToString("yyyyMM") + "'";
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
lCode = 0;
if (dt != null && dt.Rows.Count > 0)
{
lCode = BaseFunction.ReturnLong(dt.Rows[0]["cNumber"]);
lCode += 1;
sSQL = "update VoucherHistory set cNumber = '" + lCode.ToString() + "' Where CardNumber='0302' and cSeed = '" + dTimeNow.ToString("yyyyMM") + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
else
{
lCode = 1;
sSQL = "insert into VoucherHistory(CardNumber,cContent,cContentRule,cSeed,cNumber,bEmpty)" +
"values('0302','日期','月','" + dTimeNow.ToString("yyyyMM") + "','1',0)";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
sCode = lCode.ToString();
while (sCode.Length < 5)
{
sCode = "0" + sCode;
}
sCode = "QC" + dTimeNow.ToString("yyMM") + sCode;
lID = 0;
lIDDetails = 0;
sSQL = @"
declare @p5 int
set @p5=111111
declare @p6 int
set @p6=222222
exec sp_GetId N'',N'444444',N'rd',333333,@p5 output,@p6 output,default
select @p5, @p6
";
sSQL = sSQL.Replace("111111", lID.ToString());
sSQL = sSQL.Replace("222222", lIDDetails.ToString());
sSQL = sSQL.Replace("333333", iCouRow.ToString());
sSQL = sSQL.Replace("444444", sAccID);
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
lID = ClsBaseDataInfo.ReturnObjectToLong(dt.Rows[0][0]) - 1;
lIDDetails = ClsBaseDataInfo.ReturnObjectToLong(dt.Rows[0][1]) - iCouRow;
lID += 1;
Model.RdRecord09 modelr9 = new TH.WebService.Model.RdRecord09();
modelr9.ID = lID;
modelr9.bRdFlag = 0;
modelr9.cVouchType = "09";
modelr9.cBusType = "调拨出库";
modelr9.cSource = "调拨";
modelr9.cBusCode = sTrCode;
modelr9.cWhCode = dtTran.Rows[0]["cOWhCode"].ToString().Trim();
modelr9.dDate = BaseFunction.ReturnDate(dTimeNow.ToString("yyyy-MM-dd"));
modelr9.cCode = sCode;
modelr9.cRdCode = dtTran.Rows[0]["cORdCode"].ToString().Trim();
modelr9.cDepCode = dtTran.Rows[0]["cODepCode"].ToString().Trim();
modelr9.bTransFlag = false;
modelr9.cMaker = sUid;
modelr9.bpufirst = false;
modelr9.biafirst = false;
modelr9.VT_ID = 67;
modelr9.bIsSTQc = false;
modelr9.bOMFirst = false;
modelr9.bFromPreYear = false;
modelr9.bIsComplement = 0;
modelr9.iDiscountTaxType = 0;
modelr9.ireturncount = 0;
modelr9.iverifystate = 0;
modelr9.iswfcontrolled = 0;
modelr9.dnmaketime = dTimeNow;
modelr9.bredvouch = 0;
modelr9.iPrintCount = 0;
DAL.RdRecord09 DALr9 = new RdRecord09();
sSQL = DALr9.Add(modelr9);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
for (int i = 0; i < dtTran.Rows.Count; i++)
{
Model.rdrecords09 modelr9s = new TH.WebService.Model.rdrecords09();
lIDDetails += 1;
modelr9s.AutoID = lIDDetails;
modelr9s.ID = lID;
modelr9s.cInvCode = dtTran.Rows[i]["cInvCode"].ToString().Trim();
modelr9s.iQuantity = BaseFunction.ReturnDecimal(dtTran.Rows[i]["iTVQuantity"]);
modelr9s.iNum = BaseFunction.ReturnDecimal(dtTran.Rows[i]["iTVNum"]);
modelr9s.iTrIds = BaseFunction.ReturnLong(dtTran.Rows[i]["autoID"]);
modelr9s.iNQuantity = BaseFunction.ReturnDecimal(dtTran.Rows[i]["iTVQuantity"]);
modelr9s.irowno = BaseFunction.ReturnInt(dtTran.Rows[i]["irowno"]);
DAL.RdRecords09 DALr9s = new RdRecords09();
sSQL = DALr9s.Add(modelr9s);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
sSQL = "insert into IA_ST_UnAccountVouch09(idun,idsun,cvoutypeun,cbustypeun)values " +
"('" + lID + "','" + lIDDetails + "','09','调拨出库')";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
#endregion
}
//sSQL = "exec SP_ClearCurrentStock_ST";
//DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
#endregion
tran.Commit();
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
return(iCou);
}
