public string _GetDemand(DateTime planVersion) { var planMaster = this.genericMgr.FindById<MrpPlanMaster>(planVersion); var exPlanGroup = (this.genericMgr.FindAll<MrpExPlan>(" from MrpExPlan where PlanVersion =? and DateIndex =? order by Section", new object[] { planVersion, planMaster.DateIndex }) ?? new List<MrpExPlan>()).GroupBy(p => p.Section); var prodLineExList = this.genericMgr.FindAll<ProdLineEx> (" from ProdLineEx where StartDate<=? and EndDate>?", new object[] { DateTime.Now, DateTime.Now }); var speedDic = prodLineExList.GroupBy(p => p.Item).ToDictionary(d => d.Key, d => d.Average(q => q.MrpSpeed)); var flowDic = prodLineExList.GroupBy(p => p.Item).ToDictionary(d => d.Key, d => string.Join(" ", d.Select(p => p.ProductLine))); #region 获取库存 在途 DateTime snapTime; //snapTime = this.genericMgr.FindAll<MrpSnapMaster> // (" from MrpSnapMaster where IsRelease = ? and Type=? Order by SnapTime desc", // new object[] { true, CodeMaster.SnapType.Mrp }, 0, 1).First().SnapTime; //取离挤出版本最近的快照 snapTime = genericMgr.FindAllWithNativeSql<DateTime>(@"Select dbo.GetStartInvSnapTimeByDate ('" + planVersion + "') As SnapTime").FirstOrDefault(); var inventoryBalances = this.genericMgr.FindAll<InventoryBalance> (@"from InventoryBalance as m where m.SnapTime = ?", new object[] { snapTime }); var transitOrderList = this.genericMgr.FindAll<TransitOrder> ("from TransitOrder as m where m.SnapTime = ?", new object[] { snapTime }); foreach (var transitOrder in transitOrderList) { var inventoryBalance = new InventoryBalance(); inventoryBalance.Item = transitOrder.Item; inventoryBalance.Qty = transitOrder.ShippedQty - transitOrder.ReceivedQty; inventoryBalances.Add(inventoryBalance); } inventoryBalances = inventoryBalances.GroupBy(p => new { p.Item, p.Location }) .Select(p => { var inventoryBalance = new InventoryBalance(); inventoryBalance.Item = p.Key.Item; inventoryBalance.Location = p.Key.Location; inventoryBalance.Qty = p.Where(q => q.Qty > 0).Sum(q => q.Qty); inventoryBalance.SafeStock = p.Sum(q => q.SafeStock); inventoryBalance.MaxStock = p.Sum(q => q.MaxStock); return inventoryBalance; }).ToList(); var inventoryBalanceDicOfWorkshop = inventoryBalances.Where(p => !string.IsNullOrWhiteSpace(p.Location) && p.Location.Substring(0, 2) == "92").GroupBy(p => p.Item) .ToDictionary(d => d.Key, d => d.Sum(b => b.Qty)); #endregion StringBuilder str = new StringBuilder("<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" class=\"display\" id=\"datatable\" width=\"100%\"><thead><tr>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_Section+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_Description+"</th>"); str.Append("<th style='max-width: 60px;'>"+Resources.EXT.ControllerLan.Con_ProductionLine+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_RequirementLength+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_RequirementShift+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_LatestStartTo+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_HalfFinishedGood+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_HalfFinishedGoodDescription+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_UomLength+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_GrossRequirement+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_SafeInventory+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_TotalInventory+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_WorkshopInventory+"</th>"); //str.Append("<th>仓库库存</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_WaitingForReceiving+"</th>"); str.Append("<th>"+Resources.EXT.ControllerLan.Con_WaitingForShipping+"</th>"); str.Append("<th>" + Resources.EXT.ControllerLan.Con_NetRequirement + "</th>"); //str.Append("<th>最晚开始</th>"); str.Append("</tr></thead><tbody>"); int l = 0; foreach (var exPlans in exPlanGroup) { l++; if (l % 2 == 0) { str.Append("<tr class=\"t-alt\">"); } else { str.Append("<tr>"); } int count = exPlans.Count(); str.Append(string.Format("<td rowspan='{0}'>{1}</td>", count, exPlans.Key)); str.Append(string.Format("<td rowspan='{0}'>{1}</td>", count, itemMgr.GetCacheItem(exPlans.Key).FullDescription)); str.Append(string.Format("<td rowspan='{0}' style='max-width: 60px;'>{1}</td>", count, flowDic.ValueOrDefault(exPlans.Key))); str.Append(string.Format("<td rowspan='{0}'>{1}</td>", count, exPlans.Sum(p => p.SectionQty).ToString("0"))); string shiftQty = "-"; var speed = speedDic.ValueOrDefault(exPlans.Key); if (speed > 0) { shiftQty = (exPlans.Sum(p => p.SectionQty) / speed / 8 / 60).ToString("0.#"); } str.Append(string.Format("<td rowspan='{0}'>{1}</td>", count, shiftQty)); string latestStartTime = "-"; if (exPlans.Min(p => p.LatestStartTime) < DateTime.Now.AddMonths(1)) { latestStartTime = exPlans.Min(p => p.LatestStartTime).ToString("yyyy-MM-dd"); } str.Append(string.Format("<td rowspan='{0}'>{1}</td>", count, latestStartTime)); for (int i = 0; i < exPlans.Count(); i++) { if (i > 0) { str.Append("<tr>"); } var exPlan = exPlans.ElementAt(i); str.Append(string.Format("<td>{0}</td>", exPlan.Item)); str.Append(string.Format("<td>{0}</td>", itemMgr.GetCacheItem(exPlan.Item).FullDescription)); str.Append(string.Format("<td>{0}</td>", exPlan.RateQty.ToString("0.##"))); str.Append(string.Format("<td>{0}</td>", exPlan.ItemQty.ToString("0.##"))); str.Append(string.Format("<td>{0}</td>", exPlan.SafeStock.ToString("0.##"))); str.Append(string.Format("<td>{0}</td>", inventoryBalances.Where(p => p.Item == exPlan.Item).Sum(p=>p.Qty).ToString("0.##"))); str.Append(string.Format("<td>{0}</td>", inventoryBalanceDicOfWorkshop.ValueOrDefault(exPlan.Item).ToString("0.##"))); //str.Append(string.Format("<td>{0}</td>", (exPlan.InvQty - inventoryBalanceDicOfWorkshop.ValueOrDefault(exPlan.Item)).ToString("0.##"))); str.Append(string.Format("<td>{0}</td>", exPlan.PlanInQty.ToString("0.##"))); str.Append(string.Format("<td>{0}</td>", exPlan.PlanOutQty.ToString("0.##"))); str.Append(string.Format("<td>{0}</td>", exPlan.NetQty.ToString("0.##"))); //str.Append(string.Format("<td>{0}</td>", exPlan.LatestStartTime.ToString("yyyy-MM-dd"))); str.Append("</tr>"); } } str.Append("</tbody></table>"); return str.ToString(); }
public ActionResult _Update(GridCommand command, InventoryBalance inventoryBalance, string locationTo, string itemTo, DateTime? snapTimeTo ) { InventoryBalanceSearchModel searchModel = new InventoryBalanceSearchModel(); searchModel.Location = locationTo; searchModel.Item = itemTo; searchModel.SnapTime = snapTimeTo.Value; InventoryBalance newInventoryBalance = genericMgr.FindAll<InventoryBalance>(" from InventoryBalance as i where i.Id=? ", new object[] { inventoryBalance.Id })[0]; if (inventoryBalance.Qty != newInventoryBalance.Qty) { newInventoryBalance.Qty = inventoryBalance.Qty; genericMgr.Update(newInventoryBalance); } SearchStatementModel searchStatementModel = PrepareInventoryBalanceSearchStatement(command, searchModel); return PartialView(GetAjaxPageData<InventoryBalance>(searchStatementModel, command)); }