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