/// <summary>
/// 执行扣减待入仓结存,若有之前的批量卡清单和数量则先写入再扣减
/// </summary>
/// <param name="tran">当前事务</param>
/// <param name="strLotCardList">当前扣减的批量卡清单和数量</param>
/// <param name="billNum">送货单号</param>
/// <param name="rowId">当前行号</param>
/// <param name="productNum">之前的生产编号</param>
private void DeductionWaitInStoreBalance(
SqlTransaction tran,
string strLotCardList,
string billNum,
byte rowId,
string productNum
)
{
//***扣减当前输入的批量卡和数量***
//检测输入的内容
string strReg = "[^>]+(?=</div>)";
var matchs = Regex.Matches(strLotCardList, strReg);
//查询或插入命令
using (var daLotCard = new t_delivery_bill_lot_cardTableAdapter())
using (var cmd = new SqlCommand())
{
//设置连接
daLotCard.Connection = cmd.Connection = tran.Connection;
//设置事务
daLotCard.Transaction = cmd.Transaction = tran;
//取出匹配到的数据
foreach (Match match in matchs)
{
//通过冒号分出批量卡号和数量
string[] strs = match.Value.Split(new char[] { ':' }, 2, StringSplitOptions.RemoveEmptyEntries);
string strLotId = strs[0];
int iQty = int.Parse(strs[1]);
//部门名称
string procName = "成品仓";
//待入仓成品结余pcs总数
int totalPnlQty;
int totalPcsQty;
//获取该批量卡在待入仓成品仓的结存数
ydOperateBalanceLotCrad.GetProcBalanceQty(
tran,
procName,
strLotId,
null,
out totalPnlQty,
out totalPcsQty
);
//检测pcs数量必须大于0
if (totalPcsQty < iQty)
{
if (totalPnlQty > 0)
{
throw new Exception(string.Format("批量卡 {0} 在待入仓成品的结存为pnl数!", strLotId));
}
else
{
throw new Exception(string.Format("批量卡 {0} 在待入仓成品的结存不足扣数 {1} pcs!", strLotId, iQty));
}
}
//保存当前批量卡出数清单
daLotCard.InsertData(billNum, rowId, strLotId, iQty, string.Empty);
//从待入仓结存中扣取数据
cmd.CommandText =
"UPDATE a " +
"SET [pcs_qty]=[pcs_qty]-@PcsQty OUTPUT [inserted].[pcs_qty] " +
"FROM (" +
"SELECT TOP 1 * " +
"FROM [t_complete_lot_card_wait] " +
"WHERE [proc_name]=@ProcName AND [lot_id]=@LotId AND [pcs_qty]>0 " +
"ORDER BY [id] DESC" +
") a";
//设置参数值
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@PcsQty", iQty);
cmd.Parameters.AddWithValue("@ProcName", procName);
cmd.Parameters.AddWithValue("@LotId", strLotId);
//取得扣减后的pcs数量
int iLastQty = Convert.ToInt32(cmd.ExecuteScalar());
while (iLastQty < 0)
{
cmd.Parameters["@PcsQty"].Value = -iLastQty;
iLastQty = Convert.ToInt32(cmd.ExecuteScalar());
}
//修改查询语句删除结存小于或等于0的项
cmd.CommandText =
"DELETE FROM [t_complete_lot_card_wait] " +
"WHERE [proc_name]=@ProcName AND [lot_id]=@LotId AND [pcs_qty]<=0";
//设置参数值
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@ProcName", procName);
cmd.Parameters.AddWithValue("@LotId", strLotId);
//执行删除
cmd.ExecuteNonQuery();
}
}
}