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();
}
