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