using HH.WMS.Entitys; using HH.WMS.Entitys.Basic; using HH.WMS.Entitys.Entitys; using HH.WMS.Common.Algorithm; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; using HanHe.Utility.Data; using System.Data.Common; using HH.WMS.Entitys.Common; using HH.WMS.Common; using HH.WMS.Entitys.Dto; using HH.WMS.Common.External; using HH.WMS.Entitys.External; using HH.WMS.DAL.InStock; namespace HH.WMS.DAL.Basic { public class TN_WM_B_AREA_QTYDAL : DapperBaseDAL { /// /// 降库区量表并降分配量 /// /// 库区量实体集合 /// 暂存区 /// /// [HANHE(XDL)] CREATED BY 2018-11-14 public OperateResult ReduceAreaAllocQty(List AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (var model in AreaQtyList) { TN_WM_B_AREA_QTYEntity.CheckNullValue(model); StringBuilder strSql = new StringBuilder(); string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + "' and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + "' and CN_S_STOCK_AREA='" + areaCode + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + "' and CN_S_OWNER='" + model.CN_S_OWNER.Trim() + "' "; strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " and CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + ">=0)"); strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + " " + sqlWhere + " "); strSql.Append(" else "); strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY =0 " + sqlWhere + " "); Log.Detail(logPara, "降到货区库区量表sqlWhere:" + sqlWhere); Log.Detail(logPara, "降到货区库区量表strSql:" + strSql.ToString()); result = ExecuteTranSql(strSql.ToString(), null, trans); Log.Detail(logPara, "降到货区库区量表影响行数: " + result.AffectedRows.ToString()); if (result.Status == ResultStatus.Error) return result; } Log.Detail(logPara, "降低库区" + areaCode + "库存!结果:" + (result.Success ? "成功!" : ("失败!" + result.Msg))); return result; } /// /// 获得库区下每个物料的数量 /// /// /// /// public List GetAreaQty(string strWhere) { var strSql = string.Format(@" select CN_GUID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_PRODUCTION_BATCH,CN_S_LOT_NO,CN_S_STOCK_CODE, CN_S_STOCK_AREA, CAST(CN_F_QUANTITY AS INT ) AS CN_F_QUANTITY,CAST(CN_F_QUANTITY AS INT) AS CN_F_ALLOC_QTY,CN_S_MODEL, CN_S_FIGURE_NO,CN_S_MEASURE_UNIT from TN_WM_B_AREA_QTY where {0}", strWhere).Replace("\r\n", ""); DbCommand cmd = DataAccess.GetSqlStringCommand(strSql); List list = DataAccessExtensive.ExecuteListEntity(this.DataAccess, cmd, SetEntity); return list; } private void SetEntity(TN_WM_B_AREA_QTYEntity entity, IDataReader reader) { SetEntityUti(entity, "CN_GUID", "CN_GUID", reader); SetEntityUti(entity, "CN_S_OWNER", "CN_S_OWNER", reader); SetEntityUti(entity, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", reader); SetEntityUti(entity, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", reader); SetEntityUti(entity, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", reader); SetEntityUti(entity, "CN_S_LOT_NO", "CN_S_LOT_NO", reader); SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader); SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader); SetEntityUti(entity, "CN_F_QUANTITY", "CN_F_QUANTITY", reader); SetEntityUti(entity, "CN_F_ALLOC_QTY", "CN_F_ALLOC_QTY", reader); SetEntityUti(entity, "CN_S_MODEL", "CN_S_MODEL", reader); SetEntityUti(entity, "CN_S_FIGURE_NO", "CN_S_FIGURE_NO", reader); SetEntityUti(entity, "CN_S_MEASURE_UNIT", "CN_S_MEASURE_UNIT", reader); SetEntityUti(entity, "CN_S_PRODUCTION_BATCH", "CN_S_PRODUCTION_BATCH", reader); } /// /// 根据物料汇总当前库存 /// /// 物料编码 /// 条件 /// /// [HANHE(XDL)] CREATED BY 2018-11-16 public TN_WM_B_AREA_QTYEntity GetAreaSummaryQty(string itemCode, string strWhere) { TN_WM_B_AREA_QTYEntity areaQtyEntity = new TN_WM_B_AREA_QTYEntity(); StringBuilder sbStr = new StringBuilder(); sbStr.Append("SELECT CN_S_ITEM_CODE,SUM(CN_F_QUANTITY) CN_F_QUANTITY FROM TN_WM_B_AREA_QTY WHERE CN_S_ITEM_CODE='" + itemCode + "' "); if (!string.IsNullOrEmpty(strWhere)) { sbStr.Append(strWhere); } sbStr.Append("GROUP BY CN_S_ITEM_CODE"); return ExecuteSingleEntity(sbStr.ToString()); } /// /// 根据物料汇总当前库存 /// /// 物料编码 /// 条件 /// /// [HANHE(XDL)] CREATED BY 2018-11-16 public List GetStockQtyInfo(string strWhere) { List areaQtyEntity = new List(); StringBuilder sbStr = new StringBuilder(); sbStr.Append("SELECT CN_S_ITEM_CODE as itemCode ,CN_S_ITEM_NAME as itemName,CN_S_STOCK_CODE as stockCode,CN_S_STOCK_AREA as areaCode,(CN_F_QUANTITY - CN_F_ALLOC_QTY) as availableWeight FROM TN_WM_B_AREA_QTY WHERE 1=1 "); if (!string.IsNullOrEmpty(strWhere)) { sbStr.Append(strWhere); } // sbStr.Append("GROUP BY CN_S_ITEM_CODE"); return ExecuteQuery(sbStr.ToString()); } public List GetStockQtyDetailList(string strWhere) { List lstStockDetail = new List(); StringBuilder sbStr = new StringBuilder(); sbStr.Append(" select c.CN_S_STOCK_CODE as stockCode,c.CN_S_STOCK_AREA as areaCode,a.CN_S_TRAY_CODE as trayCode,a.CN_S_ITEM_CODE as itemCode, "); sbStr.Append(" a.CN_S_ITEM_NAME as itemName,a.CN_S_MODEL as itemModel,SUM(cast(b.CN_S_SERIAL_NO as decimal(10,4))) AS itemWeight from dbo.TN_WM_B_TRAY_ITEM_MST a "); sbStr.Append(" inner join dbo.TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID = b.CN_PARENT_GUID "); sbStr.Append(" inner join dbo.tn_wm_b_tray_location c on a.CN_S_TRAY_CODE = c.CN_S_TRAY_CODE "); sbStr.Append(" group by c.CN_S_STOCK_CODE ,c.CN_S_STOCK_AREA ,a.CN_S_TRAY_CODE ,a.CN_S_ITEM_CODE ,a.CN_S_ITEM_NAME ,a.CN_S_MODEL "); if (!string.IsNullOrEmpty(strWhere)) { sbStr.Append(strWhere); } return ExecuteQuery(sbStr.ToString()); } /// /// 获得库区下每个物料的数量 /// /// /// /// public List GetAreaQty(string strWhere, string stockCode) { string strSql = @"select SUM(CN_F_QUANTITY) - SUM(CN_F_ALLOC_QTY) as Qty,CN_S_ITEM_CODE as itemCode,CN_S_STOCK_AREA as areaCode from TN_WM_B_AREA_QTY WHERE 1=1 " + strWhere + " AND CN_S_STOCK_CODE='" + stockCode + "' GROUP by CN_S_STOCK_AREA,CN_S_ITEM_CODE "; List list = ExecuteQuery(strSql.ToString()); return list; } /// /// 根据货主获取库存 /// /// /// public List GetAreaQtyByOwner(string ownerCode) { string strSql = @"select * from (select distinct aq.CN_S_OWNER,aq.CN_S_ITEM_CODE,aq.CN_S_ITEM_NAME,aq.CN_S_ITEM_STATE,aq.CN_S_LOT_NO,aq.CN_S_STOCK_CODE,aq.CN_S_STOCK_AREA, aq.CN_F_QUANTITY-(SELECT ISNULL(SUM(CN_F_QUANTITY),0) FROM TN_WM_ITEMBACK_DTL DTL LEFT JOIN TN_WM_ITEMBACK_MST MST ON MST.CN_S_OP_NO=DTL.CN_S_OP_NO WHERE MST.CN_S_OWNER=aq.CN_S_OWNER and DTL.CN_S_ITEM_CODE=aq.CN_S_ITEM_CODE and dtl.CN_S_LOT_NO=aq.CN_S_LOT_NO and DTL.CN_S_PRODUCTION_BATCH=aq.CN_S_PRODUCTION_BATCH and CN_S_STATE='新建') CN_F_QUANTITY,aq.CN_F_PLANNED_QTY,aq.CN_F_ALLOC_QTY,aq.CN_S_NOTE,aq.CN_S_TIMESTAMP,aq.CN_S_PRODUCTION_BATCH from dbo.tn_wm_b_area_qty aq where aq.CN_S_OWNER='" + ownerCode + "') A where A.CN_F_QUANTITY>0"; List list = ExecuteQuery(strSql.ToString()); return list; } /// /// 入库单取消时,判断物料是否可取消 /// /// /// public List GetCancelData(string opNoList) { string strSql = @" select Q.* from TN_WM_B_AREA_QTY Q,TN_WM_INCREASE_INVENTORY_DTL IND,TN_WM_INCREASE_INVENTORY_MST MST WHERE Q.CN_S_ITEM_CODE=IND.CN_S_ITEM_CODE AND Q.CN_S_ITEM_STATE=IND.CN_S_ITEM_STATE AND Q.CN_S_LOT_NO=MST.CN_S_LOT_NO AND ISNULL(Q.CN_S_PRODUCTION_BATCH,'')=ISNULL(IND.CN_S_PRODUCTION_BATCH,'') AND Q.CN_S_OWNER=MST.CN_S_OWNER AND IND.CN_S_OP_NO=MST.CN_S_OP_NO AND IND.CN_S_OP_NO in (" + opNoList + ")"; List list = ExecuteQuery(strSql.ToString()); return list; } /// /// 升库区库存 /// /// /// 是否默认货主 /// /// [HANHE(XDL)] CREATED BY 2018-11-14 public OperateResult AddAreaQty(List AreaQtyList, IDbTransaction trans, bool isDefaultOwner = false) { OperateResult result = new OperateResult(); foreach (var model in AreaQtyList) { string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + "' and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + "' and CN_S_STOCK_AREA='" + model.CN_S_STOCK_AREA + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + "' and CN_S_OWNER='" + (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()) + "' "; StringBuilder strSql = new StringBuilder(); strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " )"); strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY+" + model.CN_F_QUANTITY + ",CN_F_ALLOC_QTY=CN_F_ALLOC_QTY+" + model.CN_F_ALLOC_QTY + " " + sqlWhere + " "); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_AREA_QTY("); strSql.Append("CN_GUID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_LOT_NO,CN_S_STOCK_CODE,CN_F_QUANTITY,CN_F_ALLOC_QTY,CN_F_PLANNED_QTY,CN_S_STOCK_AREA,CN_S_NOTE,CN_S_TIMESTAMP,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,CN_S_PRODUCTION_BATCH)"); strSql.Append(" values ("); strSql.Append("@CN_GUID,@CN_S_OWNER,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_ITEM_STATE,@CN_S_LOT_NO,@CN_S_STOCK_CODE,@CN_F_QUANTITY,@CN_F_ALLOC_QTY,@CN_F_PLANNED_QTY,@CN_S_STOCK_AREA,@CN_S_NOTE,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)"); Log.Info("===>影响库区量详细", strSql.ToString()); result = ExecuteTranSql(strSql.ToString(), new { CN_GUID = model.CN_GUID, CN_S_OWNER = (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()), CN_S_ITEM_CODE = model.CN_S_ITEM_CODE, CN_S_ITEM_NAME = model.CN_S_ITEM_NAME, CN_S_ITEM_STATE = model.CN_S_ITEM_STATE, CN_S_LOT_NO = model.CN_S_LOT_NO == null ? "" : model.CN_S_LOT_NO, CN_S_STOCK_CODE = model.CN_S_STOCK_CODE, CN_F_QUANTITY = model.CN_F_QUANTITY, CN_F_ALLOC_QTY = model.CN_F_ALLOC_QTY, CN_F_PLANNED_QTY = model.CN_F_PLANNED_QTY, CN_S_STOCK_AREA = model.CN_S_STOCK_AREA, CN_S_NOTE = model.CN_S_NOTE, CN_S_TIMESTAMP = model.CN_S_TIMESTAMP, CN_S_MODEL = model.CN_S_MODEL, CN_S_FIGURE_NO = model.CN_S_FIGURE_NO, CN_S_MEASURE_UNIT = model.CN_S_MEASURE_UNIT, CN_S_PRODUCTION_BATCH = model.CN_S_PRODUCTION_BATCH == null ? "" : model.CN_S_PRODUCTION_BATCH }, trans); if (result.Status == ResultStatus.Error) return result; } return result; } /// /// 升库区库存 /// /// /// /// /// /// /// public OperateResult AddAreaQty(List AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans, bool isDefaultOwner = false) { if (logPara == null) logPara = LogType.LogPara("升库区库存量表"); OperateResult result = new OperateResult(); foreach (var model in AreaQtyList) { TN_WM_B_AREA_QTYEntity.CheckNullValue(model); string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + "' and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + "' and CN_S_STOCK_AREA='" + areaCode + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + "' and CN_S_OWNER='" + (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()) + "' "; StringBuilder strSql = new StringBuilder(); strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " )"); strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY+" + model.CN_F_QUANTITY + sqlWhere + " "); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_AREA_QTY("); strSql.Append("CN_GUID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_LOT_NO,CN_S_STOCK_CODE,CN_F_QUANTITY,CN_F_ALLOC_QTY,CN_F_PLANNED_QTY,CN_S_STOCK_AREA,CN_S_NOTE,CN_S_TIMESTAMP,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,CN_S_PRODUCTION_BATCH)"); strSql.Append(" values ("); strSql.Append("@CN_GUID,@CN_S_OWNER,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_ITEM_STATE,@CN_S_LOT_NO,@CN_S_STOCK_CODE,@CN_F_QUANTITY,@CN_F_ALLOC_QTY,@CN_F_PLANNED_QTY,@CN_S_STOCK_AREA,@CN_S_NOTE,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)"); Log.Info("===>影响库区量详细", strSql.ToString()); result = ExecuteTranSql(strSql.ToString(), new { CN_GUID = System.Guid.NewGuid().ToString().ToUpper(), CN_S_OWNER = (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()), CN_S_ITEM_CODE = model.CN_S_ITEM_CODE, CN_S_ITEM_NAME = model.CN_S_ITEM_NAME, CN_S_ITEM_STATE = model.CN_S_ITEM_STATE, CN_S_LOT_NO = model.CN_S_LOT_NO == null ? "" : model.CN_S_LOT_NO, CN_S_STOCK_CODE = model.CN_S_STOCK_CODE, CN_F_QUANTITY = model.CN_F_QUANTITY, CN_F_ALLOC_QTY = 0, CN_F_PLANNED_QTY = model.CN_F_PLANNED_QTY, CN_S_STOCK_AREA = areaCode, CN_S_NOTE = "", CN_S_TIMESTAMP = "", CN_S_MODEL = model.CN_S_MODEL, CN_S_FIGURE_NO = "", CN_S_MEASURE_UNIT = "", CN_S_PRODUCTION_BATCH = model.CN_S_PRODUCTION_BATCH == null ? "" : model.CN_S_PRODUCTION_BATCH }, trans); if (result.Status == ResultStatus.Error) return result; } Log.Detail(logPara, "增加库区" + areaCode + "库存!结果:" + (result.Success ? "成功!" : ("失败!" + result.Msg))); return result; } /// /// 升库区库存 /// /// /// 是否默认货主 /// /// [HANHE(XDL)] CREATED BY 2018-11-14 public OperateResult AddAreaQtyAndAlloc(List AreaQtyList, IDbTransaction trans, bool isDefaultOwner = false) { OperateResult result = new OperateResult(); foreach (var model in AreaQtyList) { string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + "' and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + "' and CN_S_STOCK_AREA='" + model.CN_S_STOCK_AREA + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + "' and CN_S_OWNER='" + (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()) + "' "; StringBuilder strSql = new StringBuilder(); strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " )"); strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY+" + model.CN_F_QUANTITY + ",CN_F_ALLOC_QTY=CN_F_ALLOC_QTY+" + model.CN_F_ALLOC_QTY + " " + sqlWhere + " "); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_AREA_QTY("); strSql.Append("CN_GUID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_LOT_NO,CN_S_STOCK_CODE,CN_F_QUANTITY,CN_F_ALLOC_QTY,CN_F_PLANNED_QTY,CN_S_STOCK_AREA,CN_S_NOTE,CN_S_TIMESTAMP,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,CN_S_PRODUCTION_BATCH)"); strSql.Append(" values ("); strSql.Append("@CN_GUID,@CN_S_OWNER,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_ITEM_STATE,@CN_S_LOT_NO,@CN_S_STOCK_CODE,@CN_F_QUANTITY,@CN_F_ALLOC_QTY,@CN_F_PLANNED_QTY,@CN_S_STOCK_AREA,@CN_S_NOTE,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)"); Log.Info("===>影响库区量详细", strSql.ToString()); result = ExecuteTranSql(strSql.ToString(), new { CN_GUID = model.CN_GUID, CN_S_OWNER = (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()), CN_S_ITEM_CODE = model.CN_S_ITEM_CODE, CN_S_ITEM_NAME = model.CN_S_ITEM_NAME, CN_S_ITEM_STATE = model.CN_S_ITEM_STATE, CN_S_LOT_NO = model.CN_S_LOT_NO == null ? "" : model.CN_S_LOT_NO, CN_S_STOCK_CODE = model.CN_S_STOCK_CODE, CN_F_QUANTITY = model.CN_F_QUANTITY, CN_F_ALLOC_QTY = model.CN_F_ALLOC_QTY, CN_F_PLANNED_QTY = model.CN_F_PLANNED_QTY, CN_S_STOCK_AREA = model.CN_S_STOCK_AREA, CN_S_NOTE = model.CN_S_NOTE, CN_S_TIMESTAMP = model.CN_S_TIMESTAMP, CN_S_MODEL = model.CN_S_MODEL, CN_S_FIGURE_NO = model.CN_S_FIGURE_NO, CN_S_MEASURE_UNIT = model.CN_S_MEASURE_UNIT, CN_S_PRODUCTION_BATCH = model.CN_S_PRODUCTION_BATCH == null ? "" : model.CN_S_PRODUCTION_BATCH }, trans); if (result.Status == ResultStatus.Error) return result; } return result; } /// /// 升库区库存 /// /// /// /// /// /// /// public OperateResult AddAreaQtyAndAlloc(List AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans, bool isDefaultOwner = false) { if (logPara == null) logPara = LogType.LogPara("升库区库存量表"); OperateResult result = new OperateResult(); foreach (var model in AreaQtyList) { TN_WM_B_AREA_QTYEntity.CheckNullValue(model); string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + "' and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + "' and CN_S_STOCK_AREA='" + areaCode + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + "' and CN_S_OWNER='" + (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()) + "' "; StringBuilder strSql = new StringBuilder(); strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " )"); strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY+" + model.CN_F_QUANTITY + ",CN_F_ALLOC_QTY=CN_F_ALLOC_QTY+" + model.CN_F_QUANTITY + " " + sqlWhere + " "); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_AREA_QTY("); strSql.Append("CN_GUID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_LOT_NO,CN_S_STOCK_CODE,CN_F_QUANTITY,CN_F_ALLOC_QTY,CN_F_PLANNED_QTY,CN_S_STOCK_AREA,CN_S_NOTE,CN_S_TIMESTAMP,CN_S_MODEL,CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,CN_S_PRODUCTION_BATCH)"); strSql.Append(" values ("); strSql.Append("@CN_GUID,@CN_S_OWNER,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_ITEM_STATE,@CN_S_LOT_NO,@CN_S_STOCK_CODE,@CN_F_QUANTITY,@CN_F_ALLOC_QTY,@CN_F_PLANNED_QTY,@CN_S_STOCK_AREA,@CN_S_NOTE,@CN_S_TIMESTAMP,@CN_S_MODEL,@CN_S_FIGURE_NO,@CN_S_MEASURE_UNIT,@CN_S_PRODUCTION_BATCH)"); Log.Info("===>影响库区量详细", strSql.ToString()); result = ExecuteTranSql(strSql.ToString(), new { CN_GUID = System.Guid.NewGuid().ToString().ToUpper(), CN_S_OWNER = (isDefaultOwner == true ? Constants.DefaultOwner : model.CN_S_OWNER.Trim()), CN_S_ITEM_CODE = model.CN_S_ITEM_CODE, CN_S_ITEM_NAME = model.CN_S_ITEM_NAME, CN_S_ITEM_STATE = model.CN_S_ITEM_STATE, CN_S_LOT_NO = model.CN_S_LOT_NO == null ? "" : model.CN_S_LOT_NO, CN_S_STOCK_CODE = model.CN_S_STOCK_CODE, CN_F_QUANTITY = model.CN_F_QUANTITY, CN_F_ALLOC_QTY = 0, CN_F_PLANNED_QTY = model.CN_F_PLANNED_QTY, CN_S_STOCK_AREA = areaCode, CN_S_NOTE = "", CN_S_TIMESTAMP = "", CN_S_MODEL = "", CN_S_FIGURE_NO = "", CN_S_MEASURE_UNIT = "", CN_S_PRODUCTION_BATCH = model.CN_S_PRODUCTION_BATCH == null ? "" : model.CN_S_PRODUCTION_BATCH }, trans); if (result.Status == ResultStatus.Error) return result; } Log.Detail(logPara, "增加库区" + areaCode + "库存!结果:" + (result.Success ? "成功!" : ("失败!" + result.Msg))); return result; } /// /// 升分配量 /// /// /// /// /// public OperateResult AddAllocQty(TN_WM_B_AREA_QTYEntity areaQty, IDbTransaction trans, LogPara logPara) { TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty); string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY+" + areaQty.CN_F_QUANTITY + " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE + "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE + "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA + "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH + "' "; Log.Detail(logPara, "升库区分配量执行SQL语句:" + sql); var result = ExecuteTranSql(sql, null, trans); return result; } /// /// 降分配量 /// /// /// /// /// public OperateResult ReduceAllocQty(TN_WM_B_AREA_QTYEntity areaQty, IDbTransaction trans, LogPara logPara) { TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty); string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + areaQty.CN_F_QUANTITY + " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE + "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE + "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA + "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH + "' "; Log.Detail(logPara, "降库区分配量执行SQL语句:" + sql); var result = ExecuteTranSql(sql, null, trans); return result; } /// /// 降分配量 /// /// /// /// /// public OperateResult ReduceAllocQty(string areaCode, string trayCode, IDbTransaction trans, LogPara logPara) { var result = new OperateResult(); var trayItemMsts = DALCreator.Create>().GetList(new { CN_S_TRAY_CODE = trayCode }); foreach (var trayItemMst in trayItemMsts) { trayItemMst.CN_F_QUANTITY = 0; trayItemMst.TrayItemDtlList = DALCreator.Create>().GetList(new { CN_PARENT_GUID = trayItemMst.CN_GUID }); foreach (var trayItemDtl in trayItemMst.TrayItemDtlList) { trayItemMst.CN_F_QUANTITY += Convert.ToDecimal(trayItemDtl.CN_S_SERIAL_NO); } var areaQty = new TN_WM_B_AREA_QTYEntity { CN_F_QUANTITY = trayItemMst.CN_F_QUANTITY, CN_S_ITEM_CODE = trayItemMst.CN_S_ITEM_CODE, CN_S_ITEM_STATE = trayItemMst.CN_S_ITEM_STATE, CN_S_STOCK_AREA = areaCode, CN_S_LOT_NO = trayItemMst.CN_S_LOT_NO, CN_S_PRODUCTION_BATCH = trayItemMst.CN_S_PRODUCTION_BATCH }; TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty); string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + areaQty.CN_F_QUANTITY + " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE + "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE + "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA + "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH + "' "; Log.Detail(logPara, "降库区分配量执行SQL语句:" + sql); result = ExecuteTranSql(sql, null, trans); if (!result.Success) return result; } return result; } public OperateResult ReduceAreaAndStockAllocQty(string areaCode, string trayCode, IDbTransaction trans, LogPara logPara) { var result = new OperateResult(); var trayItemMsts = DALCreator.Create>().GetList(new { CN_S_TRAY_CODE = trayCode }); foreach (var trayItemMst in trayItemMsts) { trayItemMst.CN_F_QUANTITY = 0; trayItemMst.TrayItemDtlList = DALCreator.Create>().GetList(new { CN_PARENT_GUID = trayItemMst.CN_GUID }); foreach (var trayItemDtl in trayItemMst.TrayItemDtlList) { trayItemMst.CN_F_QUANTITY += Convert.ToDecimal(trayItemDtl.CN_S_SERIAL_NO); } var areaQty = new TN_WM_B_AREA_QTYEntity { CN_F_QUANTITY = trayItemMst.CN_F_QUANTITY, CN_S_ITEM_CODE = trayItemMst.CN_S_ITEM_CODE, CN_S_ITEM_STATE = trayItemMst.CN_S_ITEM_STATE, CN_S_STOCK_AREA = areaCode, CN_S_LOT_NO = trayItemMst.CN_S_LOT_NO, CN_S_PRODUCTION_BATCH = trayItemMst.CN_S_PRODUCTION_BATCH }; var stockQty = new TN_WM_B_STOCK_QTYEntity { CN_F_QUANTITY = trayItemMst.CN_F_QUANTITY, CN_S_ITEM_CODE = trayItemMst.CN_S_ITEM_CODE, CN_S_ITEM_STATE = trayItemMst.CN_S_ITEM_STATE, CN_S_STOCK_AREA = areaCode, CN_S_LOT_NO = trayItemMst.CN_S_LOT_NO, CN_S_PRODUCTION_BATCH = trayItemMst.CN_S_PRODUCTION_BATCH }; TN_WM_B_AREA_QTYEntity.CheckNullValue(areaQty); TN_WM_B_STOCK_QTYEntity.CheckNullValue(stockQty); string sql = " Update TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + areaQty.CN_F_QUANTITY + " Where CN_S_ITEM_CODE='" + areaQty.CN_S_ITEM_CODE + "' and CN_S_ITEM_STATE='" + areaQty.CN_S_ITEM_STATE + "' and CN_S_STOCK_AREA='" + areaQty.CN_S_STOCK_AREA + "' and CN_S_LOT_NO='" + areaQty.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + areaQty.CN_S_PRODUCTION_BATCH + "' "; Log.Detail(logPara, "降库区分配量执行SQL语句:" + sql); result = ExecuteTranSql(sql, null, trans); sql = " Update TN_WM_B_STOCK_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + stockQty.CN_F_QUANTITY + " Where CN_S_ITEM_CODE='" + stockQty.CN_S_ITEM_CODE + "' and CN_S_ITEM_STATE='" + stockQty.CN_S_ITEM_STATE + "' and CN_S_LOT_NO='" + stockQty.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + stockQty.CN_S_PRODUCTION_BATCH + "' "; Log.Detail(logPara, "降仓库分配量执行SQL语句:" + sql); result = ExecuteTranSql(sql, null, trans); if (!result.Success) return result; } return result; } /// /// 降暂存区库区量表 /// /// 库区量实体集合 /// 暂存区 /// /// [HANHE(XDL)] CREATED BY 2018-11-14 public OperateResult ReduceAreaQty(List AreaQtyList, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (TN_WM_B_AREA_QTYEntity model in AreaQtyList) { StringBuilder strSql = new StringBuilder(); string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + @"' and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + @"' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + @"' and CN_S_LOT_NO='" + model.CN_S_LOT_NO + @"' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + @"' and CN_S_OWNER='" + model.CN_S_OWNER + "' and CN_S_STOCK_AREA ='" + model.CN_S_STOCK_AREA.Trim() + "' "; strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " and CN_F_QUANTITY-" + model.CN_F_QUANTITY + ">0)"); strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-" + model.CN_F_QUANTITY + " " + sqlWhere + " "); strSql.Append(" else "); strSql.Append("DELETE FROM TN_WM_B_AREA_QTY " + sqlWhere + " "); Log.Info("降到货区库区量表sqlWhere", sqlWhere); Log.Info("降到货区库区量表strSql", strSql.ToString()); result = ExecuteTranSql(strSql.ToString(), null, trans); Log.Info("降到货区库区量表影响行数", result.AffectedRows.ToString()); if (result.Status == ResultStatus.Error) return result; } return result; } /// /// 降暂存区库区量表 /// /// 库区量实体集合 /// 暂存区 /// /// [HANHE(XDL)] CREATED BY 2018-11-14 public OperateResult ReduceAreaQty(List AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (var model in AreaQtyList) { TN_WM_B_AREA_QTYEntity.CheckNullValue(model); StringBuilder strSql = new StringBuilder(); string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + "' and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + "' and CN_S_STOCK_AREA='" + areaCode + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + "' and CN_S_OWNER='" + model.CN_S_OWNER.Trim() + "' "; strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " and CN_F_QUANTITY-" + model.CN_F_QUANTITY + ">0)"); strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-" + model.CN_F_QUANTITY + " " + sqlWhere + " "); strSql.Append(" else "); strSql.Append("DELETE FROM TN_WM_B_AREA_QTY " + sqlWhere + " "); Log.Detail(logPara, "降到货区库区量表sqlWhere:" + sqlWhere); Log.Detail(logPara, "降到货区库区量表strSql" + strSql.ToString()); result = ExecuteTranSql(strSql.ToString(), null, trans); Log.Detail(logPara, "降到货区库区量表影响行数:" + result.AffectedRows.ToString()); if (result.Status == ResultStatus.Error) return result; } Log.Detail(logPara, "降低库区" + areaCode + "库存!结果:" + (result.Success ? "成功!" : ("失败!" + result.Msg))); return result; } /// /// 降货主所在库区量 /// /// /// /// public OperateResult ReduceOwnerAreaQty(List AreaQtyList, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (TN_WM_B_AREA_QTYEntity model in AreaQtyList) { StringBuilder strSql = new StringBuilder(); string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + @"' and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + @"' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + @"' and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + "' and CN_S_OWNER='" + model.CN_S_OWNER.Trim() + "' "; strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " and CN_F_QUANTITY-" + model.CN_F_QUANTITY + ">0)"); strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-" + model.CN_F_QUANTITY + " " + sqlWhere + " "); strSql.Append(" else "); strSql.Append("DELETE FROM TN_WM_B_AREA_QTY " + sqlWhere + " "); Log.Info("降货主所在库区量sqlWhere", sqlWhere); Log.Info("降货主所在库区量strSql", strSql.ToString()); result = ExecuteTranSql(strSql.ToString(), null, trans); Log.Info("降货主所在库区量表影响行数", result.AffectedRows.ToString()); if (result.Status == ResultStatus.Error) return result; } return result; } /// /// 降库区量表并降分配量 /// /// 库区量实体集合 /// 暂存区 /// /// [HANHE(XDL)] CREATED BY 2018-11-14 public OperateResult ReduceAreaQtyAndAllocQty(List AreaQtyList, string areaCode, LogPara logPara, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (var model in AreaQtyList) { TN_WM_B_AREA_QTYEntity.CheckNullValue(model); StringBuilder strSql = new StringBuilder(); string sqlWhere = " Where CN_S_STOCK_CODE='" + model.CN_S_STOCK_CODE + "' and CN_S_ITEM_CODE='" + model.CN_S_ITEM_CODE + "' and CN_S_STOCK_AREA='" + areaCode + "' and CN_S_ITEM_STATE='" + model.CN_S_ITEM_STATE + "' and CN_S_LOT_NO='" + model.CN_S_LOT_NO + "' and CN_S_PRODUCTION_BATCH='" + model.CN_S_PRODUCTION_BATCH + "' and CN_S_OWNER='" + model.CN_S_OWNER.Trim() + "' "; strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY " + sqlWhere + " and CN_F_QUANTITY-" + model.CN_F_QUANTITY + ">0)"); strSql.Append("Update TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-" + model.CN_F_QUANTITY + ",CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-" + model.CN_F_QUANTITY + " " + sqlWhere + " "); strSql.Append(" else "); strSql.Append("DELETE FROM TN_WM_B_AREA_QTY " + sqlWhere + " "); Log.Detail(logPara, "降到货区库区量表sqlWhere:" + sqlWhere); Log.Detail(logPara, "降到货区库区量表strSql:" + strSql.ToString()); result = ExecuteTranSql(strSql.ToString(), null, trans); Log.Detail(logPara, "降到货区库区量表影响行数: " + result.AffectedRows.ToString()); if (result.Status == ResultStatus.Error) return result; } Log.Detail(logPara, "降低库区" + areaCode + "库存!结果:" + (result.Success ? "成功!" : ("失败!" + result.Msg))); return result; } /// /// 修改库区分配量 /// /// /// public OperateResult UpdateAreaAlloc(List areaQtyList, IDbTransaction trans) { string sql = @"UPDATE TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=@CN_F_ALLOC_QTY,CN_S_TIMESTAMP='{0}' WHERE CN_GUID=@CN_GUID AND (CN_S_TIMESTAMP = @CN_S_TIMESTAMP OR CN_S_TIMESTAMP IS NULL)"; sql = string.Format(sql, DateTime.Now.ToString("yyyyMMddHHmmssffff")); return ExecuteTranSql(sql, areaQtyList, trans); } /// /// 修改库区数量 /// /// /// /// public OperateResult UpdateAreaQty(List areaQtyList, IDbTransaction trans) { string sql = @"UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY=@CN_F_QUANTITY,CN_S_TIMESTAMP='{0}' WHERE CN_GUID=@CN_GUID AND (CN_S_TIMESTAMP = @CN_S_TIMESTAMP OR CN_S_TIMESTAMP IS NULL)"; sql = string.Format(sql, DateTime.Now.ToString("yyyyMMddHHmmssffff")); return ExecuteTranSql(sql, areaQtyList, trans); } /// /// 修改库存量和分配量 /// /// /// /// public OperateResult UpdateAreaQtyAndAlloc(List areaQtyList, IDbTransaction trans) { string sql = @"UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY=@CN_F_QUANTITY,CN_F_ALLOC_QTY=@CN_F_ALLOC_QTY,CN_S_TIMESTAMP='{0}' WHERE CN_GUID=@CN_GUID AND (CN_S_TIMESTAMP = @CN_S_TIMESTAMP OR CN_S_TIMESTAMP IS NULL)"; sql = string.Format(sql, DateTime.Now.ToString("yyyyMMddHHmmssffff")); return ExecuteTranSql(sql, areaQtyList, trans); } /// /// 修改多条数据库存量 /// /// /// /// public OperateResult UpdateAreaListAllocBysqlwhere(List list, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (TN_WM_B_AREA_QTYEntity entity in list) { string sql = @"UPDATE TN_WM_B_AREA_QTY SET CN_F_ALLOC_QTY=CN_F_ALLOC_QTY + " + entity.CN_F_ALLOC_QTY + " " + entity.SqlWhere + ""; result = ExecuteTranSql(sql, null, trans); if (result.Status == ResultStatus.Error) return result; } return result; } /// /// 根据移库单 修改库区数量 移出库区减去数量 移入库区加上数量 /// /// /// /// /// [HANHE(LT)] CREATED BY 2018-12-20 public SqlExecuteResult MoveInfoUpdateAreaQty(List lstModel, DbTransaction trans) { SqlExecuteResult result = new SqlExecuteResult(); List cmdlist = new List(); foreach (TN_WM_B_AREA_QTYEntity model in lstModel) { StringBuilder strSql = new StringBuilder(); //移出库区数量 //strSql.Append("UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY=CN_F_QUANTITY-:CN_F_QUANTITY,CN_F_ALLOC_QTY=CN_F_ALLOC_QTY-:CN_F_QUANTITY WHERE CN_S_STOCK_AREA='" + model.startArea + "' AND " + model.SqlWhere + ""); strSql.Append(" if exists(select * from TN_WM_B_AREA_QTY WHERE CN_S_STOCK_AREA='" + model.endArea + "' AND " + model.SqlWhere1 + " )"); strSql.Append("UPDATE TN_WM_B_AREA_QTY SET CN_S_OWNER='" + model.endBit + "',CN_F_QUANTITY=CN_F_QUANTITY+:CN_F_QUANTITY WHERE CN_S_STOCK_AREA='" + model.endArea + "' AND " + model.SqlWhere1 + ""); strSql.Append(" else "); strSql.Append("insert into TN_WM_B_AREA_QTY("); strSql.Append("CN_GUID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,CN_S_LOT_NO,CN_S_STOCK_CODE,CN_F_QUANTITY,CN_F_ALLOC_QTY,CN_F_PLANNED_QTY,CN_S_STOCK_AREA,CN_S_NOTE,CN_S_TIMESTAMP,CN_S_PRODUCTION_BATCH)"); strSql.Append(" values ("); strSql.Append(":CN_GUID,:CN_S_OWNER,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_ITEM_STATE,:CN_S_LOT_NO,:CN_S_STOCK_CODE,:CN_F_QUANTITY,:CN_F_ALLOC_QTY,:CN_F_PLANNED_QTY,:CN_S_STOCK_AREA,:CN_S_NOTE,:CN_S_TIMESTAMP,:CN_S_PRODUCTION_BATCH)"); DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString()); DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, model.CN_GUID); DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, model.CN_S_OWNER); DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, model.CN_S_ITEM_CODE); DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, model.CN_S_ITEM_NAME); DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, model.CN_S_ITEM_STATE); DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, model.CN_S_LOT_NO); DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, model.CN_S_STOCK_CODE); DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, model.CN_F_QUANTITY); DataAccess.AddInParameter(cmd, "CN_F_ALLOC_QTY", ComDbType.DECIMAL, 0); DataAccess.AddInParameter(cmd, "CN_F_PLANNED_QTY", ComDbType.DECIMAL, model.CN_F_PLANNED_QTY); DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, model.CN_S_STOCK_AREA); DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, model.CN_S_NOTE); DataAccess.AddInParameter(cmd, "CN_S_TIMESTAMP", ComDbType.STRING, model.CN_S_TIMESTAMP); DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, model.CN_S_PRODUCTION_BATCH); cmdlist.Add(cmd); } return ExecuteCommands(cmdlist, trans); } /// /// 降库区库存量表 /// /// /// /// /// [HANHE(XDL)] CREATED BY 2018-11-15 public OperateResult DeleteAreaQtys(List list, IDbTransaction trans) { OperateResult result = new OperateResult(); foreach (TN_WM_B_AREA_QTYEntity entity in list) { string sql = @" UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY =CN_F_QUANTITY-{1} {0}"; sql = string.Format(sql, entity.SqlWhere, entity.CN_F_QUANTITY); result = ExecuteTranSql(sql.ToString(), null, trans); if (result.Status == ResultStatus.Error) return result; } return result; } /// /// 降某个特定库区所在的库区量表 /// /// 到货单号 /// 物料编码 /// 库区编码 /// 补缺数量 /// public OperateResult DeleteAreaQtyByAreaCode(string arrivalCode, string itemCode, string areaCode, string offSetQuantity, IDbTransaction trans) { OperateResult result = new OperateResult(); string sql = @" UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-" + Convert.ToDecimal(offSetQuantity) + @" FROM TN_WM_B_AREA_QTY Q,TN_WM_INCREASE_INVENTORY_DTL IND,TN_WM_INCREASE_INVENTORY_MST INMST WHERE Q.CN_S_ITEM_CODE=IND.CN_S_ITEM_CODE AND Q.CN_S_ITEM_STATE=IND.CN_S_ITEM_STATE AND Q.CN_S_LOT_NO=IND.CN_S_LOT_NO AND INMST.CN_S_OP_NO=IND.CN_S_OP_NO AND Q.CN_S_OWNER=IND.CN_S_OWNER AND INMST.CN_S_ARRIVAL_NO='" + arrivalCode + @"' AND Q.CN_S_ITEM_CODE='" + itemCode + "' AND Q.CN_S_STOCK_AREA='" + areaCode + "' "; Log.Info("===>降某个特定库区所在的库区量表", sql.ToString()); result = ExecuteTranSql(sql.ToString(), null, trans); if (result.Success) { string sqlstr = @" DELETE FROM TN_WM_B_AREA_QTY where CN_F_QUANTITY<=0 "; result = ExecuteTranSql(sqlstr.ToString(), null, trans); } return result; } /// /// 入库单反审时降库区量表 /// /// /// /// /// [HANHE(XDL)] CREATED BY 2018-11-15 public OperateResult DeleteAreaQty(string opNoList, IDbTransaction trans) { OperateResult result = new OperateResult(); string sql = @" UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-IND.CN_F_QUANTITY FROM TN_WM_B_AREA_QTY Q,TN_WM_INCREASE_INVENTORY_DTL IND,TN_WM_INCREASE_INVENTORY_MST MST WHERE Q.CN_S_ITEM_CODE=IND.CN_S_ITEM_CODE AND Q.CN_S_ITEM_STATE=IND.CN_S_ITEM_STATE AND Q.CN_S_LOT_NO=MST.CN_S_LOT_NO AND ISNULL(Q.CN_S_PRODUCTION_BATCH,'')=ISNULL(IND.CN_S_PRODUCTION_BATCH,'') AND Q.CN_S_OWNER=MST.CN_S_OWNER AND IND.CN_S_OP_NO=MST.CN_S_OP_NO AND IND.CN_S_OP_NO in (" + opNoList + ")"; result = ExecuteTranSql(sql.ToString(), null, trans); if (result.Success) { string sqlstr = @" DELETE FROM TN_WM_B_AREA_QTY where CN_F_QUANTITY<=0 "; result = ExecuteTranSql(sqlstr.ToString(), null, trans); } return result; } /// /// 到货单反审时降库区量表 /// /// /// /// /// [HANHE(XDL)] CREATED BY 2019-03-04 public OperateResult DeleteAreaQtyArr(string opNoList, IDbTransaction trans) { OperateResult result = new OperateResult(); string sql = @" UPDATE TN_WM_B_AREA_QTY SET CN_F_QUANTITY =Q.CN_F_QUANTITY-IND.CN_F_QUANTITY FROM TN_WM_B_AREA_QTY Q,TN_WM_ARRIVAL_DTL IND,TN_WM_ARRIVAL_MST MST WHERE Q.CN_S_ITEM_CODE=IND.CN_S_ITEM_CODE AND Q.CN_S_ITEM_STATE=IND.CN_S_ITEM_STATE AND Q.CN_S_LOT_NO=MST.CN_S_LOT_NO AND ISNULL(Q.CN_S_PRODUCTION_BATCH,'')=ISNULL(IND.CN_S_PRODUCTION_BATCH,'') AND Q.CN_S_OWNER=MST.CN_S_OWNER AND IND.CN_S_OP_NO=MST.CN_S_OP_NO AND IND.CN_S_OP_NO in (" + opNoList + ")"; result = ExecuteTranSql(sql.ToString(), null, trans); if (result.Success) { string sqlstr = @" DELETE FROM TN_WM_B_AREA_QTY where CN_F_QUANTITY<=0 "; result = ExecuteTranSql(sqlstr.ToString(), null, trans); } return result; } /// /// 删除为0库区 /// /// public OperateResult DeleteAreaQty(IDbTransaction trans) { string sql = "DELETE dbo.tn_wm_b_area_qty WHERE CN_F_QUANTITY<=0"; return ExecuteTranSql(sql, null, trans); } } }