private RowCell GetRowCell(ScheduleBody scheduleBody, ColumnCell columnCell, IList<RowCell> rowCellList)
{
RowCell rowCell = new RowCell();
var q = rowCellList.Where(r => r.OrderNo == scheduleBody.OrderNo && r.Sequence == scheduleBody.Sequence
&& r.ScheduleType == columnCell.ScheduleType && r.EndDate == columnCell.EndDate);
if (q.ToList() != null && q.ToList().Count > 0)
{
rowCell = q.ToList().First();
}
else
{
rowCell.OrderNo = scheduleBody.OrderNo;
rowCell.Sequence = scheduleBody.Sequence;
rowCell.ScheduleType = columnCell.ScheduleType;
rowCell.EndDate = columnCell.EndDate;
rowCell.OrderQty = 0;
rowCell.ShippedQty = 0;
}
return rowCell;
}
private RowCell GetRowCell(ScheduleBody scheduleBody, ColumnCell columnCell, IList<RowCell> rowCellList, DateTime dateTimeNow)
{
RowCell rowCell = new RowCell();
var q = rowCellList.Where(r => r.OrderNo == scheduleBody.OrderNo && r.Sequence == scheduleBody.Sequence
&& r.ScheduleType == columnCell.ScheduleType
&& (columnCell.EndDate < dateTimeNow ? r.EndDate < dateTimeNow : r.EndDate == columnCell.EndDate));
if (q != null && q.Count() > 0)
{
rowCell = q.First();
rowCell.OrderQty = q.Sum(oq => oq.OrderQty);
rowCell.ShippedQty = q.Sum(oq => oq.ShippedQty);
if (rowCell.EndDate < dateTimeNow && rowCell.OrderQty == rowCell.ShippedQty)
{
rowCell.OrderQty = 0;
rowCell.ShippedQty = 0;
}
}
else
{
rowCell.OrderNo = scheduleBody.OrderNo;
rowCell.Sequence = scheduleBody.Sequence;
rowCell.ScheduleType = columnCell.ScheduleType;
rowCell.EndDate = columnCell.EndDate;
rowCell.OrderQty = 0;
rowCell.ShippedQty = 0;
}
return rowCell;
}
private ScheduleView PrepareScheduleView(string PartyFrom, string item, DateTime dateTimeNow, int intervalDays, IList<com.Sconit.Web.SAPService.OrderDetail> scheduleList, bool notIncludeZeroShipQty)
{
ScheduleView scheduleView = new ScheduleView();
ScheduleHead scheduleHead = new ScheduleHead();
if (!string.IsNullOrWhiteSpace(PartyFrom) || !string.IsNullOrWhiteSpace(item))
{
#region webservice取到拼好的orderdet
var orderDetailList = from sl in scheduleList
select new
{
EndDate = sl.EndDate,
FreezeDays = sl.FreezeDays,
OrderNo = sl.OrderNo,
ExternalOrderNo = sl.ExternalOrderNo,
ExternalSequence = sl.ExternalSequence,
Item = sl.Item,
ItemDesc = sl.ItemDescription,
RefItemCode = sl.ReferenceItemCode,
Uom = sl.Uom,
UnitCount = sl.UnitCount,
OrderedQty = sl.OrderedQty,
ShipQty = sl.ShippedQty,
ReceiveQty = sl.ReceivedQty,
LocationTo = sl.LocationTo,
Flow = sl.Flow,
PartyFrom = sl.ManufactureParty
};
#endregion
#region head
IList<ColumnCell> columnCellList = new List<ColumnCell>();
for (int i = 0; i < intervalDays; i++)
{
ColumnCell c = new ColumnCell();
c.EndDate = dateTimeNow.Date.AddDays(i);
columnCellList.Add(c);
}
scheduleHead.ColumnCellList = columnCellList;
#endregion
#region body
IList<ScheduleBody> scheduleBodyList = (from p in orderDetailList
group p by new
{
OrderNo = p.OrderNo,
//Flow = p.Flow,
ExternalOrderNo = p.ExternalOrderNo,
ScheduleLineSeq = p.ExternalSequence,
Item = p.Item,
ItemDesc = p.ItemDesc,
RefItemCode = p.RefItemCode,
Uom = p.Uom,
UnitCount = p.UnitCount,
FreezeDays = p.FreezeDays,
//LocationTo = p.LocationTo,
PartyFrom = p.PartyFrom
} into g
//where g.Count(det => det.OrderedQty != det.ShipQty) > 0
select new ScheduleBody
{
OrderNo = g.Key.ExternalOrderNo,
Sequence = g.Key.ScheduleLineSeq,
LesOrderNo = g.Key.OrderNo,
Flow = string.Join(",", g.Select(s => s.Flow)),
Item = g.Key.Item,
ItemDescription = g.Key.ItemDesc,
ReferenceItemCode = g.Key.RefItemCode,
Uom = g.Key.Uom,
UnitCount = g.Key.UnitCount,
LocationTo = string.Join(",", g.Select(s => s.LocationTo)),
FreezeDays = g.Key.FreezeDays,
Supplier = g.Key.PartyFrom
}).ToList();
IList<RowCell> allRowCellList = (from p in
(from p in orderDetailList
select new
{
OrderNo = p.ExternalOrderNo,
ScheduleLineSeq = p.ExternalSequence,
LesOrderNo = p.OrderNo,
PartyFrom = p.PartyFrom,
EndDate = p.EndDate,
ScheduleType = dateTimeNow.AddDays(Convert.ToDouble(p.FreezeDays)) >= p.EndDate ? com.Sconit.CodeMaster.ScheduleType.Firm : com.Sconit.CodeMaster.ScheduleType.Forecast,
OrderQty = p.OrderedQty,
ShippedQty = p.ShipQty,
ReceivedQty = p.ReceiveQty
}).Distinct()
group p by new
{
OrderNo = p.OrderNo,
ScheduleLineSeq = p.ScheduleLineSeq,
LesOrderNo = p.OrderNo,
//Flow = p.Flow,
PartyFrom = p.PartyFrom,
EndDate = p.EndDate,
ScheduleType = p.ScheduleType
} into g
select new RowCell
{
OrderNo = g.Key.OrderNo,
Sequence = g.Key.ScheduleLineSeq,
LesOrderNo = g.Key.LesOrderNo,
//Flow = g.Key.Flow,
Supplier = g.Key.PartyFrom,
EndDate = g.Key.EndDate,
ScheduleType = (com.Sconit.CodeMaster.ScheduleType)g.Key.ScheduleType,
OrderQty = g.Sum(p => p.OrderQty),
ShippedQty = g.Sum(p => p.ShippedQty),
ReceivedQty = g.Sum(p => p.ReceivedQty)
}).ToList();
//查找IpDetailList
if (scheduleBodyList != null && scheduleBodyList.Count > 0)
{
StringBuilder selectIpDetailStatement = new StringBuilder();
IList<object> selectIpDetailParms = new List<object>();
foreach (string ebeln_ebelp in scheduleBodyList.Select(b => b.OrderNo + "&" + b.Sequence).Distinct())
{
if (selectIpDetailStatement.Length == 0)
{
selectIpDetailStatement.Append(@"select ipd.extno,ipd.extseq,ipd.qty,ipd.recqty,ipd.isclose
from ord_ipdet_8 ipd with(NOLOCK) inner join ord_ipmstr_8 ipm with(NOLOCK) on ipd.ipno = ipm.ipno
where ipd.EBELN_EBELP in (?");
selectIpDetailParms.Add(ebeln_ebelp);
}
else
{
selectIpDetailStatement.Append(",?");
selectIpDetailParms.Add(ebeln_ebelp);
}
}
selectIpDetailStatement.Append(") and ipm.status <> ? and ipd.type <> 1");
selectIpDetailParms.Add(CodeMaster.IpStatus.Cancel);
IList<object[]> ipDetailList = base.genericMgr.FindAllWithNativeSql<object[]>(selectIpDetailStatement.ToString(), selectIpDetailParms.ToArray());
foreach (ScheduleBody scheduleBody in scheduleBodyList)
{
//可发货数=在冻结期内的计划数-已处理数
//已处理数的计算逻辑如下：
//获取该计划协议号+行号相关的所有送货单明细（不包含差异类型和已冲销的送货单）
//明细未关闭时如果已收过货，则取收货数；如果未收过货则取发货数
//明细关闭时全部取收货数
//在冻结期内的计划数
decimal orderQtyInFreeze = allRowCellList.Where(q => q.OrderNo == scheduleBody.OrderNo && q.Sequence == scheduleBody.Sequence //&& q.Flow == scheduleBody.Flow
&& q.EndDate <= dateTimeNow.AddDays(Convert.ToDouble(scheduleBody.FreezeDays))).Sum(q => q.OrderQty);
scheduleBody.OrderQtyInFreeze = orderQtyInFreeze.ToString("0.########");
//获取与该计划协议号+行号相关的所有送货单明细
//IList<IpDetail> ipDetail = base.genericMgr.FindAll<IpDetail>("select i from IpDetail as i where ExternalOrderNo = '" + scheduleBody.OrderNo + "' and ExternalSequence = '" + scheduleBody.Sequence + "' and Type = " + (int)CodeMaster.IpDetailType.Normal + " and exists (select 1 from IpMaster as im where im.IpNo = i.IpNo and im.Status != " + (int)CodeMaster.IpStatus.Cancel + ")");
List<object[]> ipDetail = (from ipd in ipDetailList
where (string)ipd[0] == scheduleBody.OrderNo && (string)ipd[1] == scheduleBody.Sequence
select ipd).ToList();
//汇总已处理数
decimal handledQty = ipDetail.Select(o => (bool)o[4] ? (decimal)o[3] : ((decimal)o[3] == 0 ? (decimal)o[2] : (decimal)o[3])).Sum();
scheduleBody.HandledQty = handledQty.ToString("0.########");
//scheduleBody.ShipQty = allRowCellList.Where(q => q.OrderNo == scheduleBody.OrderNo && q.Sequence == scheduleBody.Sequence
// && q.OrderQty > q.ShippedQty && q.EndDate <= dateTimeNow.AddDays(Convert.ToDouble(scheduleBody.FreezeDays))).Sum(q => (q.OrderQty - q.ShippedQty)).ToString("0.########");
scheduleBody.ShipQty = (orderQtyInFreeze - handledQty).ToString("0.########");
//已发货数
//scheduleBody.ShippedQty = allRowCellList.Where(q => q.OrderNo == scheduleBody.OrderNo && q.Sequence == scheduleBody.Sequence
// && q.ShippedQty > 0).Sum(q => q.ShippedQty).ToString("0.########");
decimal shippedQty = ipDetail.Select(o => (bool)o[4] && (decimal)o[3] == 0 ? 0 : (decimal)o[2]).Sum();
scheduleBody.ShippedQty = shippedQty.ToString("0.########");
//已收货数
decimal receivedQty = ipDetail.Select(o => (decimal)o[3]).Sum();
scheduleBody.ReceivedQty = receivedQty.ToString("0.########");
//总计划数
scheduleBody.OrderQty = allRowCellList.Where(q => q.OrderNo == scheduleBody.OrderNo && q.Sequence == scheduleBody.Sequence //&& q.Flow == scheduleBody.Flow
&& q.OrderQty > 0).Sum(q => q.OrderQty).ToString("0.########");
//未来汇总
scheduleBody.ForecastQty = allRowCellList.Where(q => q.OrderNo == scheduleBody.OrderNo && q.Sequence == scheduleBody.Sequence //&& q.Flow == scheduleBody.Flow
&& q.EndDate >= dateTimeNow.Date.AddDays(Convert.ToDouble(intervalDays)) && q.OrderQty > 0).Sum(q => q.OrderQty).ToString("0.########");
//历史总计划数
scheduleBody.BackOrderQty = allRowCellList.Where(q => q.OrderNo == scheduleBody.OrderNo && q.Sequence == scheduleBody.Sequence //&& q.Flow == scheduleBody.Flow
&& q.EndDate < dateTimeNow.Date && q.OrderQty > 0).Sum(q => q.OrderQty).ToString("0.########");
if (scheduleHead.ColumnCellList != null && scheduleHead.ColumnCellList.Count > 0)
{
List<RowCell> rowCellList = new List<RowCell>();
foreach (ColumnCell columnCell in scheduleHead.ColumnCellList)
{
RowCell rowCell = GetRowCell(scheduleBody, columnCell, allRowCellList, dateTimeNow);
rowCellList.Add(rowCell);
}
scheduleBody.RowCellList = rowCellList;
}
}
}
//过滤可发货数为0的行
if (notIncludeZeroShipQty)
scheduleView.ScheduleBodyList = (from sl in scheduleBodyList
where Convert.ToDecimal(sl.ShipQty) > 0
select sl).ToList();
else
scheduleView.ScheduleBodyList = scheduleBodyList;
#endregion
}
scheduleView.ScheduleHead = scheduleHead;
return scheduleView;
}
