/// <summary>
/// 得到一个对象实体
/// </summary>
public TH.WebService.Model._BarCodeRD DataRowToModel(DataRow row)
{
TH.WebService.Model._BarCodeRD model = new TH.WebService.Model._BarCodeRD();
if (row != null)
{
if (row["iID"] != null && row["iID"].ToString() != "")
{
model.iID = int.Parse(row["iID"].ToString());
}
if (row["sCode"] != null)
{
model.sCode = row["sCode"].ToString();
}
if (row["BarCode"] != null)
{
model.BarCode = row["BarCode"].ToString();
}
if (row["XBarCode"] != null)
{
model.XBarCode = row["XBarCode"].ToString();
}
if (row["sType"] != null)
{
model.sType = row["sType"].ToString();
}
if (row["ExsID"] != null && row["ExsID"].ToString() != "")
{
model.ExsID = int.Parse(row["ExsID"].ToString());
}
if (row["ExCode"] != null)
{
model.ExCode = row["ExCode"].ToString();
}
if (row["ExRowNo"] != null && row["ExRowNo"].ToString() != "")
{
model.ExRowNo = int.Parse(row["ExRowNo"].ToString());
}
if (row["cInvCode"] != null)
{
model.cInvCode = row["cInvCode"].ToString();
}
if (row["Qty"] != null && row["Qty"].ToString() != "")
{
model.Qty = decimal.Parse(row["Qty"].ToString());
}
if (row["CreateUid"] != null)
{
model.CreateUid = row["CreateUid"].ToString();
}
if (row["CreateDate"] != null && row["CreateDate"].ToString() != "")
{
model.CreateDate = DateTime.Parse(row["CreateDate"].ToString());
}
if (row["RDType"] != null && row["RDType"].ToString() != "")
{
model.RDType = int.Parse(row["RDType"].ToString());
}
}
return(model);
}
/// <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);
}
/// <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);
}
/// <summary>
/// 增加一条数据
/// </summary>
public string Add(TH.WebService.Model._BarCodeRD model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.sCode != null)
{
strSql1.Append("sCode,");
strSql2.Append("'" + model.sCode + "',");
}
if (model.BarCode != null)
{
strSql1.Append("BarCode,");
strSql2.Append("'" + model.BarCode + "',");
}
if (model.BarCode != null)
{
strSql1.Append("XBarCode,");
strSql2.Append("'" + model.XBarCode + "',");
}
if (model.sType != null)
{
strSql1.Append("sType,");
strSql2.Append("'" + model.sType + "',");
}
if (model.ExsID != null)
{
strSql1.Append("ExsID,");
strSql2.Append("" + model.ExsID + ",");
}
if (model.ExCode != null)
{
strSql1.Append("ExCode,");
strSql2.Append("'" + model.ExCode + "',");
}
if (model.ExRowNo != null)
{
strSql1.Append("ExRowNo,");
strSql2.Append("" + model.ExRowNo + ",");
}
if (model.cInvCode != null)
{
strSql1.Append("cInvCode,");
strSql2.Append("'" + model.cInvCode + "',");
}
if (model.Qty != null)
{
strSql1.Append("Qty,");
strSql2.Append("" + model.Qty + ",");
}
if (model.CreateUid != null)
{
strSql1.Append("CreateUid,");
strSql2.Append("'" + model.CreateUid + "',");
}
if (model.CreateDate != null)
{
strSql1.Append("CreateDate,");
strSql2.Append("'" + model.CreateDate + "',");
}
if (model.RDType != null)
{
strSql1.Append("RDType,");
strSql2.Append("" + model.RDType + ",");
}
strSql.Append("insert into _BarCodeRD(");
strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
strSql.Append(")");
strSql.Append(" values (");
strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
strSql.Append(")");
strSql.Append(";select @@IDENTITY");
return(strSql.ToString());
}
/// <summary>
/// 标签数量调整
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public int iSaveBarCodeAdjust(string sBarCode, decimal dQty, decimal dQtyUse, string CreateUid)
{
int iCou = 0;
try
{
SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
string 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");
sSQL = "select * from _BarCodeList where BarCode = '" + sBarCode + "'";
DataTable dtBarCode = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
TH.WebService.Model._BarCodeRD model = new TH.WebService.Model._BarCodeRD();
model.sCode = s_BarCodeRDCode;
model.BarCode = sBarCode;
model.sType = "条码数量调整";
if (dQty < 0)
{
model.RDType = 1;
model.Qty = -1 * dQty;
}
else
{
model.RDType = 2;
model.Qty = dQty;
}
model.CreateUid = CreateUid;
model.CreateDate = dtmNow;
model.cInvCode = dtBarCode.Rows[0]["cInvCode"].ToString().Trim();
sSQL = Add(model);
iCou = iCou + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (dQty == dQtyUse)
{
sSQL = "update dbo._BarCodeList set valid = 0,Used = 1 where BarCode = '" + sBarCode + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
else
{
sSQL = "update dbo._BarCodeList set valid = 1,Used = 1 where BarCode = '" + sBarCode + "'";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
tran.Commit();
#region 发送邮件
_BarCodeList bar = new _BarCodeList();
decimal dQtyNow = bar.dBarCodeQty(model.BarCode);
sSQL = "select * from _MailAddress where sType = '标签数量调整'";
DataTable dtMailAddress = DbHelperSQL.Query(sSQL);
if (dtMailAddress != null && dtMailAddress.Rows.Count > 0)
{
string sMailAddress = dtMailAddress.Rows[0]["MailAddress"].ToString().Trim();
string sMail = "条形码 " + model.BarCode + " 物料 [" + model.cInvCode + "]" + dtBarCode.Rows[0]["cInvName"] + " 的数量调整为 " + dQtyNow.ToString();
sSQL = @"Exec msdb.dbo.sp_send_dbmail @profile_name='MailSet',
@recipients='111111',
@subject='222222',
@body='" + sMail + "'";
sSQL = sSQL.Replace("111111", sMailAddress);
sSQL = sSQL.Replace("222222", dtMailAddress.Rows[0]["Subject"].ToString().Trim());
DbHelperSQL.ExecuteSql(sSQL);
}
#endregion
}
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 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);
}
