private void SetLocationDetailIOB(LocationDetailIOB totalIob, LocationDetailIOB iob, CodeMaster.TransactionType transType, CodeMaster.QualityType qualityType, decimal qty, decimal csqty) { #region 库存事务 /* other LOC_INI 100 //库存初始化 issso ISS_SO 101 //销售出库 issso ISS_SO_VOID 102 //销售出库冲销 rctso RCT_SO 103 //销售退货入库 rctso RCT_SO_VOID 104 //销售退货入库冲销 rctpo RCT_PO 201 //采购入库 rctpo RCT_PO_VOID 202 //采购入库冲销 isspo ISS_PO 203 //采购退货 isspo ISS_PO_VOID 204 //采购退货冲销 rctpo RCT_SL 205 //计划协议入库 rctpo RCT_SL_VOID 206 //计划协议入库冲销 isspo ISS_SL 207 //计划协议退货 isspo ISS_SL_VOID 208 //计划协议退货冲销 isstr ISS_TR 301 //移库出库 isstr ISS_TR_VOID 302 //移库出库冲销 rcttr RCT_TR 303 //移库入库 rcttr RCT_TR_VOID 304 //移库入库冲销 isstr ISS_TR_RTN 311 //移库退货出库 isstr ISS_TR_RTN_VOID 312 //移库退货出库冲销 rcttr RCT_TR_RTN 313 //移库退货入库 rcttr RCT_TR_RTN_VOID 314 //移库退货入库冲销 issstr ISS_STR 305 //委外移库出库 issstr ISS_STR_VOID 306 //委外移库出库冲销 rctstr RCT_STR 307 //委外移库入库 rctstr RCT_STR_VOID 308 //委外移库入库冲销 issstr ISS_STR_RTN 315 //委外移库退货出库 issstr ISS_STR_RTN_VOID 316 //委外移库退货出库冲销 rctstr RCT_STR_RTN 317 //委外移库退货入库 rctstr RCT_STR_RTN_VOID 318 //委外移库退货入库冲销 isswo ISS_WO 401 //生产出库/原材料 isswo ISS_WO_VOID 402 //生产出库/原材料冲销 isswo ISS_WO_BF 403 //生产投料回冲出库/出生产线 isswo ISS_WO_BF_VOID 404 //生产投料回冲出库/出生产线冲销 rctwo RCT_WO 405 //生产入库/成品 rctwo RCT_WO_VOID 406 //生产入库/成品冲销 - ISS_MIN 407 //生产投料出库 - ISS_MIN_RTN 408 //生产投料退库出库 - RCT_MIN 409 //生产投料入库/入生产线 - RCT_MIN_RTN 410 //生产投料出库/出生产线 issswo ISS_SWO 411 //委外生产出库/原材料 issswo ISS_SWO_VOID 412 //委外生产出库/原材料冲销 issswo ISS_SWO_BF 413 //委外生产投料回冲出库/出生产线 issswo ISS_SWO_BF_VOID 414 //委外生产投料回冲出库/出生产线冲销 rctswo RCT_SWO 415 //委外生产入库/成品 rctswo RCT_SWO_VOID 416 //委外生产入库/成品冲销 - ISS_INP 501 //报验出库 inp RCT_INP 502 //报验入库 - ISS_ISL 503 //隔离出库 inp RCT_ISL 504 //隔离入库 - ISS_INP_QDII 505 //检验合格出库 qdii RCT_INP_QDII 506 //检验合格入库 - ISS_INP_REJ 507 //检验不合格出库 rej RCT_INP_REJ 508 //检验不合格入库 - ISS_INP_CCS 509 //让步使用出库 css RCT_INP_CCS 510 //让步使用入库 cyccnt CYC_CNT 601 //盘点差异出入库 cyccnt CYC_CNT_VOID 602 //盘点差异出入库 issunp ISS_UNP 603 //计划外出库 issunp ISS_UNP_VOID 604 //计划外出库冲销 rctunp RCT_UNP 605 //计划外入库 rctunp RCT_UNP_VOID 606 //计划外入库冲销 - ISS_REP 607 //翻箱出库 - RCT_REP 608 //翻箱入库 - ISS_PUT 609 //上架出库 - RCT_PUT 610 //上架入库 - ISS_PIK 611 //下架出库 - RCT_PIK 612 //下架入库 issiic ISS_IIC 613 //库存物料替换出库 issiic ISS_IIC_VOID 614 //库存物料替换出库冲销 rctiic RCT_IIC 615 //库存物料替换入库 rctiic RCT_IIC_VOID 616 //库存物料替换入库冲销 - ISS_AGE 901 //老化出库 - ISS_AGE_VOID 902 //老化出库冲销 - RCT_AGE 903 //老化入库 - RCT_AGE_VOID 904 //老化入库冲销 issiic ISS_FLT 905 //过滤出库 issiic ISS_FLT_VOID 906 //过滤出库冲销 rctiic RCT_FLT 907 //过滤入库 rctiic RCT_FLT_VOID 908 //过滤入库冲销 */ #endregion switch (transType) { case CodeMaster.TransactionType.CYC_CNT: case CodeMaster.TransactionType.CYC_CNT_VOID: iob.CycCnt += qty; totalIob.CycCnt++; break; case CodeMaster.TransactionType.ISS_IIC: case CodeMaster.TransactionType.ISS_IIC_VOID: iob.IssIic += qty; totalIob.IssIic++; break; case CodeMaster.TransactionType.ISS_PO: case CodeMaster.TransactionType.ISS_PO_VOID: case CodeMaster.TransactionType.ISS_SL: case CodeMaster.TransactionType.ISS_SL_VOID: iob.IssPo += qty; totalIob.IssPo++; break; case CodeMaster.TransactionType.ISS_SO: case CodeMaster.TransactionType.ISS_SO_VOID: iob.IssSo += qty; totalIob.IssSo++; break; case CodeMaster.TransactionType.ISS_STR: case CodeMaster.TransactionType.ISS_STR_VOID: case CodeMaster.TransactionType.ISS_STR_RTN: case CodeMaster.TransactionType.ISS_STR_RTN_VOID: iob.IssStr += qty; totalIob.IssStr++; break; case CodeMaster.TransactionType.ISS_SWO: case CodeMaster.TransactionType.ISS_SWO_VOID: case CodeMaster.TransactionType.ISS_SWO_BF: case CodeMaster.TransactionType.ISS_SWO_BF_VOID: case CodeMaster.TransactionType.ISS_SWO_RTN: case CodeMaster.TransactionType.ISS_SWO_RTN_VOID: iob.IssSwo += qty; totalIob.IssSwo++; break; case CodeMaster.TransactionType.ISS_TR: case CodeMaster.TransactionType.ISS_TR_VOID: case CodeMaster.TransactionType.ISS_TR_RTN: case CodeMaster.TransactionType.ISS_TR_RTN_VOID: iob.IssTr += qty; totalIob.IssTr++; break; case CodeMaster.TransactionType.ISS_UNP: case CodeMaster.TransactionType.ISS_UNP_VOID: iob.IssUnp += qty; totalIob.IssUnp++; break; case CodeMaster.TransactionType.ISS_WO: case CodeMaster.TransactionType.ISS_WO_VOID: case CodeMaster.TransactionType.ISS_WO_BF: case CodeMaster.TransactionType.ISS_WO_BF_VOID: case CodeMaster.TransactionType.ISS_WO_RTN: case CodeMaster.TransactionType.ISS_WO_RTN_VOID: iob.IssWo += qty; totalIob.IssWo++; break; case CodeMaster.TransactionType.RCT_IIC: case CodeMaster.TransactionType.RCT_IIC_VOID: iob.RctIic += qty; totalIob.RctIic++; break; case CodeMaster.TransactionType.RCT_PO: case CodeMaster.TransactionType.RCT_PO_VOID: case CodeMaster.TransactionType.RCT_SL: case CodeMaster.TransactionType.RCT_SL_VOID: iob.RctPo += qty; totalIob.RctPo++; break; case CodeMaster.TransactionType.RCT_SO: case CodeMaster.TransactionType.RCT_SO_VOID: iob.RctSo += qty; totalIob.RctSo++; break; case CodeMaster.TransactionType.RCT_STR: case CodeMaster.TransactionType.RCT_STR_VOID: case CodeMaster.TransactionType.RCT_STR_RTN: case CodeMaster.TransactionType.RCT_STR_RTN_VOID: iob.RctStr += qty; totalIob.RctStr++; break; case CodeMaster.TransactionType.RCT_SWO: case CodeMaster.TransactionType.RCT_SWO_VOID: case CodeMaster.TransactionType.RCT_SWO_RTN: case CodeMaster.TransactionType.RCT_SWO_RTN_VOID: iob.RctSwo += qty; totalIob.RctSwo++; break; case CodeMaster.TransactionType.RCT_TR: case CodeMaster.TransactionType.RCT_TR_VOID: case CodeMaster.TransactionType.RCT_TR_RTN: case CodeMaster.TransactionType.RCT_TR_RTN_VOID: iob.RctTr += qty; totalIob.RctTr++; break; case CodeMaster.TransactionType.RCT_UNP: case CodeMaster.TransactionType.RCT_UNP_VOID: iob.RctUnp += qty; totalIob.RctUnp++; break; case CodeMaster.TransactionType.RCT_WO: case CodeMaster.TransactionType.RCT_WO_VOID: case CodeMaster.TransactionType.RCT_WO_RTN: case CodeMaster.TransactionType.RCT_WO_RTN_VOID: iob.RctWo += qty; totalIob.RctWo++; break; //其他-库存初始化 case CodeMaster.TransactionType.LOC_INI: iob.Other += qty; totalIob.Other++; break; default: iob.Other += qty; totalIob.Other++; break; } switch (qualityType) { case CodeMaster.QualityType.Inspect: iob.StartInp -= qty; break; case CodeMaster.QualityType.Qualified: iob.StartNml -= qty; break; case CodeMaster.QualityType.Reject: iob.StartRej -= qty; break; } iob.Start -= qty; iob.StartCs -= csqty; }
public IList<LocationDetailIOB> GetLocationDetailIOB(string location, string item, DateTime startDate, DateTime endDate) { var iobList = new ConcurrentBag<LocationDetailIOB>(); string hqlInv = @"select l.Item,l.Location, sum(l.Qty) as Qty,sum(l.QualifyQty) as QualifyQty,sum(l.InspectQty) as InspectQty,sum(l.RejectQty) as RejectQty, i.Uom,i.Desc1,i.RefCode,Loc.Name,sum(CsQty) As CsQty from VIEW_LocationDet as l join MD_Item as i on l.Item = i.Code join MD_Location as loc on l.Location = loc.Code where 1=1 "; string hqlStart = @"select l.Item, case when l.IOType=1 then l.LocFrom else l.LocTo end as Location, l.TransType,sum(l.Qty*l.UnitQty) as Qty,QualityType, sum(Case when iscs=1 then 1 else 0 end*l.Qty*l.UnitQty) as CsQty from VIEW_LocTrans l where l.EffDate >= ? and l.EffDate < ? "; string hqlEnd = @" select l.Item, case when l.IOType=1 then l.LocFrom else l.LocTo end as Location, l.TransType,sum(l.Qty*l.UnitQty) as Qty,QualityType,sum(Case when iscs=1 then 1 else 0 end*l.Qty*l.UnitQty) as CsQty from VIEW_LocTrans l where l.EffDate >= ? "; var paramInv = new List<object>(); var paramStart = new List<object>(); paramStart.Add(startDate); paramStart.Add(endDate); var paramEnd = new List<object>(); paramEnd.Add(endDate); if (!string.IsNullOrWhiteSpace(item)) { hqlInv += " and l.Item =? "; paramInv.Add(item); hqlStart += " and l.Item =? "; paramStart.Add(item); hqlEnd += " and l.Item =? "; paramEnd.Add(item); } if (!string.IsNullOrWhiteSpace(location)) { hqlInv += " and l.Location =? "; paramInv.Add(location); hqlStart += "and ((l.IOType=1 and l.LocFrom =?) or (l.IOType=0 and l.LocTo =?)) "; paramStart.Add(location); paramStart.Add(location); hqlEnd += " and ((l.IOType=1 and l.LocFrom =?) or (l.IOType=0 and l.LocTo =?)) "; paramEnd.Add(location); paramEnd.Add(location); } hqlInv += " group by l.Item,l.Location,i.Uom,i.Desc1,i.RefCode,Loc.Name order by l.Location,l.Item "; hqlStart += " group by l.Item,l.TransType,l.LocFrom,l.LocTo,l.IOType,QualityType "; hqlEnd += " group by l.Item,l.TransType,l.LocFrom,l.LocTo,l.IOType,QualityType "; var invList = this.genericMgr.FindAllWithNativeSql<object[]>(hqlInv, paramInv.ToArray()) .Select(p => new { Item = (string)p[0], Location = (string)p[1], ItemDescription = (string)p[7] + (string.IsNullOrWhiteSpace((string)p[8]) ? string.Empty : "[" + (string)p[8] + "]"), LocationName = (string)p[9], Uom = (string)p[6], //当前库存 Qty = (decimal)p[2], QualifyQty = (decimal)p[3], InspectQty = (decimal)p[4], RejectQty = (decimal)p[5], CsQty = (decimal)p[10], }).ToList(); var invStartListObj = this.genericMgr.FindAllWithNativeSql<object[]>(hqlStart, paramStart.ToArray()) .Select(p => new { Item = (string)p[0], Location = (string)p[1], TransactionType = (object)p[2], Qty = (decimal)p[3], QualityType = (object)p[4], CSQty = (decimal)p[5] }); if (invStartListObj != null) { foreach (var obj in invStartListObj) { if (true) { } } } var invEndListObj = this.genericMgr.FindAllWithNativeSql<object[]>(hqlEnd, paramEnd.ToArray()) .Select(p => new { Item = (string)p[0], Location = (string)p[1], TransactionType = (object)p[2], Qty = (decimal)p[3], QualityType = (object)p[4], CSQty = (decimal)p[5] }); var invStartList = new List<LocTransaction>(); foreach (var invStart in invStartListObj) { LocTransaction invData = new LocTransaction(); invData.Item = invStart.Item; invData.Location = invStart.Location; invData.Qty = invStart.Qty; invData.CSQty = invStart.CSQty; invData.QualityType = (CodeMaster.QualityType)(int.Parse(invStart.QualityType.ToString())); invData.TransactionType = (CodeMaster.TransactionType)(int.Parse(invStart.TransactionType.ToString())); invStartList.Add(invData); } var invEndList = new List<LocTransaction>(); foreach (var invEnd in invEndListObj) { LocTransaction invData = new LocTransaction(); invData.Item = invEnd.Item; invData.Location = invEnd.Location; invData.Qty = invEnd.Qty; invData.CSQty = invEnd.CSQty; invData.QualityType = (CodeMaster.QualityType)(int.Parse(invEnd.QualityType.ToString())); invData.TransactionType = (CodeMaster.TransactionType)(int.Parse(invEnd.TransactionType.ToString())); invEndList.Add(invData); } var locationItem = invStartList.GroupBy(p => new { p.Item, p.Location }) .Union(invEndList.GroupBy(p => new { p.Item, p.Location })) .GroupBy(p => new { p.Key.Item, p.Key.Location }).Select(p => p.First()); var additionInv = from l in locationItem join k in invList on new { l.Key.Item, l.Key.Location } equals new { k.Item, k.Location } into result from p in result.DefaultIfEmpty() where p == null select new { Item = l.Key.Item, Location = l.Key.Location, ItemDescription = itemMgr.GetCacheItem(l.Key.Item).FullDescription, LocationName = genericMgr.FindById<Location>(l.Key.Location).Name, Uom = itemMgr.GetCacheItem(l.Key.Item).Uom, //当前库存 Qty = 0M, QualifyQty = 0M, InspectQty = 0M, RejectQty = 0M, CsQty = 0M }; invList.AddRange(additionInv); var totalIob = new LocationDetailIOB(); iobList.Add(totalIob); Parallel.ForEach(invList, inv => //foreach (var inv in invList) { var iob = new LocationDetailIOB(); iob.Item = inv.Item; iob.ItemDescription = inv.ItemDescription; iob.Location = inv.Location; iob.LocationName = inv.LocationName; iob.Uom = inv.Uom; //当前库存 iob.Start = inv.Qty; iob.StartNml = inv.QualifyQty; iob.StartInp = inv.InspectQty; iob.StartRej = inv.RejectQty; iob.StartCs = inv.CsQty; #region 倒推出期末库存 foreach (var invEnd in invEndList) { if (invEnd.Item == inv.Item && invEnd.Location == inv.Location) { //var transType = invEnd.TransactionType; //decimal qty = invEnd.Qty; //var qualityType = invEnd.QualityType; //this.SetLocationDetailIOB(totalIob, iob, transType, qualityType, qty); switch (invEnd.QualityType) { case CodeMaster.QualityType.Inspect: iob.StartInp -= invEnd.Qty; break; case CodeMaster.QualityType.Qualified: iob.StartNml -= invEnd.Qty; break; case CodeMaster.QualityType.Reject: iob.StartRej -= invEnd.Qty; break; } iob.Start -= invEnd.Qty; iob.StartCs -= invEnd.CSQty; } } //期末库存 iob.End = iob.Start; iob.EndCs = iob.StartCs; iob.EndNml = iob.StartNml; iob.EndInp = iob.StartInp; iob.EndRej = iob.StartRej; #endregion //继续倒推得出期初库存和收发量 foreach (var invStart in invStartList) { if (invStart.Item == inv.Item && invStart.Location == inv.Location) { var transType = invStart.TransactionType; decimal qty = invStart.Qty; decimal csqty = invStart.CSQty; var qualityType = invStart.QualityType; this.SetLocationDetailIOB(totalIob, iob, transType, qualityType, qty, csqty); } } iobList.Add(iob); } ); return iobList.OrderBy(p => p.Item).ToList(); }