private ScheduleView PrepareScheduleView(DateTime publishDate)
{
ScheduleView scheduleView = new ScheduleView();
ScheduleHead scheduleHead = new ScheduleHead();
var orderDetailList = siMgr.FindAll<Schedule>("from Schedule where Schedule.PublishDate = ?", publishDate);
#region head
var c = from p in orderDetailList
group p by new { p.EndDate, p.ScheduleType } into g
select new ColumnCell
{
EndDate = g.Key.EndDate,
ScheduleType = g.Key.ScheduleType
};
scheduleHead.ColumnCellList = c.OrderBy(p => p.StartDate).ThenBy(p => p.ScheduleType).ToList();
#endregion
#region body
var s = from p in orderDetailList
group p by new { p.ScheduleNo, p.Item } into g
select new ScheduleBody
{
OrderNo = g.Key.ScheduleNo,
Item = g.Key.Item
};
var r = from p in orderDetailList
group p by new { p.ScheduleNo, p.EndDate, p.ScheduleType, p.Qty } into g
select new RowCell
{
OrderNo = g.Key.ScheduleNo,
EndDate = g.Key.EndDate,
ScheduleType = g.Key.ScheduleType,
Qty = g.Key.Qty
};
IList<ScheduleBody> scheduleBodyList = s.ToList();
if (scheduleBodyList != null && scheduleBodyList.Count > 0)
{
foreach (ScheduleBody scheduleBody in scheduleBodyList)
{
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, r.ToList());
rowCellList.Add(rowCell);
}
scheduleBody.RowCellList = rowCellList;
}
}
}
scheduleView.ScheduleBodyList = scheduleBodyList;
#endregion
scheduleView.ScheduleHead = scheduleHead;
return scheduleView;
}
private ScheduleView PrepareScheduleView(string flow, string item, com.Sconit.CodeMaster.ScheduleType? schedeleType, DateTime? startDate, DateTime? endDate, DateTime dateTimeNow)
{
ScheduleView scheduleView = new ScheduleView();
ScheduleHead scheduleHead = new ScheduleHead();
if (!string.IsNullOrWhiteSpace(flow))
{
if (startDate == null)
{
#region 取能发货的最小日期
string dateSql = "select min(d.EndDate) as EndDate from Ord_OrderDet_8 as d where d.OrderType = ? and d.RecQty < d.OrderQty and exists (select 1 from Ord_OrderMstr_8 as o where o.Flow = ? and d.OrderNo = o.OrderNo) ";
IList<object> dateParam = new List<object>();
dateParam.Add((int)com.Sconit.CodeMaster.OrderType.ScheduleLine);
dateParam.Add(flow);
if (!string.IsNullOrEmpty(item))
{
dateSql += " and d.Item = ?";
dateParam.Add(item);
}
if (schedeleType != null)
{
dateSql += " and d.ScheduleType = ?";
dateParam.Add(schedeleType.Value);
}
IList<DateTime?> dateList = genericMgr.FindAllWithNativeSql<DateTime?>(dateSql, dateParam.ToArray());
if (dateList[0] != null)
{
scheduleView.MinDate = dateList[0];
if (startDate == null)
{
startDate = dateList[0];
}
}
#endregion
}
if (startDate != null)
{
string hql = "select d.EndDate,d.ScheduleType,d.OrderNo,d.ExtNo,left(extseq,charindex('-',extseq)-1),d.Item,d.Uom,d.UC,d.OrderQty,d.ShipQty from Ord_OrderDet_8 as d where d.EndDate >= ? and d.OrderType = ? and exists (select 1 from Ord_OrderMstr_8 as o where o.Flow = ? and d.OrderNo=o.OrderNo)";
IList<object> param = new List<object>();
param.Add(startDate.Value);
param.Add((int)com.Sconit.CodeMaster.OrderType.ScheduleLine);
param.Add(flow);
if (!string.IsNullOrEmpty(item))
{
hql += " and d.Item = ?";
param.Add(item);
}
if (schedeleType != null)
{
hql += " and d.ScheduleType = ?";
param.Add(schedeleType.Value);
}
if (endDate != null)
{
hql += " and d.EndDate <= ?";
param.Add(endDate.Value);
}
IList<object[]> orderDetailList = genericMgr.FindAllWithNativeSql<object[]>(hql, param.ToArray());
#region head
var c = from p in orderDetailList
group p by new
{
EndDate = (DateTime)p[0] < dateTimeNow ? dateTimeNow.AddDays(-1) : (DateTime)p[0],
ScheduleType = (byte)p[1]
} into g
select new ColumnCell
{
EndDate = g.Key.EndDate,
ScheduleType = (com.Sconit.CodeMaster.ScheduleType)g.Key.ScheduleType
};
scheduleHead.ColumnCellList = c.OrderBy(p => p.EndDate).ThenBy(p => p.ScheduleType).ToList();
#endregion
#region body
var s = from p in orderDetailList
group p by new
{
OrderNo = (string)p[2],
ExternalOrderNo = (string)p[3],
ScheduleLineSeq = (string)p[4],
Item = (string)p[5],
// ItemDescription = p.ItemDescription,
// ReferenceItemCode = !string.IsNullOrWhiteSpace(p.ReferenceItemCode) ? p.ReferenceItemCode :string.Empty,
Uom = (string)p[6],
UnitCount = (decimal)p[7]
} into g
where g.Count(det => (decimal)det[8] != (decimal)det[9]) > 0
select new ScheduleBody
{
OrderNo = g.Key.ExternalOrderNo,
Sequence = g.Key.ScheduleLineSeq,
Item = g.Key.Item,
ItemDescription = genericMgr.FindById<Item>(g.Key.Item).Description,
ReferenceItemCode = genericMgr.FindById<Item>(g.Key.Item).ReferenceCode,
Uom = g.Key.Uom,
UnitCount = g.Key.UnitCount,
LocationTo = genericMgr.FindById<OrderMaster>(g.Key.OrderNo).LocationTo
};
var r = from p in orderDetailList
group p by new
{
OrderNo = (string)p[3],
ScheduleLineSeq = (string)p[4],
EndDate = (DateTime)p[0],
ScheduleType = (byte)p[1]
} into g
select new RowCell
{
OrderNo = g.Key.OrderNo,
Sequence = g.Key.ScheduleLineSeq,
EndDate = g.Key.EndDate,
ScheduleType = (com.Sconit.CodeMaster.ScheduleType)g.Key.ScheduleType,
OrderQty = g.Sum(p => (decimal)p[8]),
ShippedQty = g.Sum(p => (decimal)p[9])
};
IList<ScheduleBody> scheduleBodyList = s.ToList();
IList<RowCell> allRowCellList = r.ToList();
if (scheduleBodyList != null && scheduleBodyList.Count > 0)
{
foreach (ScheduleBody scheduleBody in scheduleBodyList)
{
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;
}
}
}
scheduleView.ScheduleBodyList = scheduleBodyList;
#endregion
}
}
scheduleView.ScheduleHead = scheduleHead;
return scheduleView;
}
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;
}
