public string _GetTransferPlan(MaterailPlanSearchModel searchModel)
{
var flowMaster = this.genericMgr.FindById<FlowMaster>(searchModel.Flow);
var flowStrategy = this.genericMgr.FindById<FlowStrategy>(flowMaster.Code);
var leadDay = Utility.DateTimeHelper.TimeTranfer(flowStrategy.LeadTime, flowStrategy.TimeUnit, CodeMaster.TimeUnit.Day);
if (!Utility.SecurityHelper.HasPermission(flowMaster))
{
return Resources.EXT.ControllerLan.Con_LackTheFlowPermission_1;
}
SearchCacheModel searchCacheModel = this.ProcessSearchModel(null, searchModel);
IList<object> param = new List<object>();
string hql = " from TransferPlan as r where r.Flow=? and r.PlanVersion=? ";
param.Add(searchModel.Flow);
param.Add(searchModel.PlanVersion);
if (!string.IsNullOrEmpty(searchModel.Item))
{
hql += " and r.Item=?";
param.Add(searchModel.Item);
}
if (!string.IsNullOrEmpty(searchModel.MaterialsGroup))
{
hql += " and exists (select 1 from Item as i where r.Item = i.Code and i.MaterialsGroup =? )";
param.Add(searchModel.MaterialsGroup);
}
IList<TransferPlan> transferPlanList = genericMgr.FindAll<TransferPlan>(hql, param.ToArray());
var flowDic = this.flowMgr.GetFlowDetailList(searchModel.Flow, true)
.GroupBy(g => g.Item, (Item, GroupItem) => new { Item, GroupItem.First().Sequence })
.ToDictionary(d => d.Item, d => d.Sequence);
var planList = from p in transferPlanList
select new Plan
{
Sequence = flowDic.ValueOrDefault(p.Item),
Flow = p.Flow,
Item = p.Item,
Qty = p.Qty,
PlanDate = searchModel.IsStartTime ? p.WindowTime.AddDays(-leadDay) : p.WindowTime,
WindowTime = p.WindowTime,
StartTime = p.WindowTime.AddDays(-leadDay)
};
searchModel.DateType = 4;
return GetPlanString(planList.ToList(), searchModel);
}
public ActionResult Export(MaterailPlanSearchModel searchModel)
{
var table = searchModel.DateType == 4 ? GetPurchasePlanDaily(searchModel) : GetPurchasePlan(searchModel);
return new DownloadFileActionResult(table, "PurchasePlanQuery.xls");
}
public string GetPurchasePlanDaily(MaterailPlanSearchModel searchModel)
{
string ProcedureName = "USP_Busi_MRP_PurchaseDailyPlan";
SqlParameter[] sqlParams = new SqlParameter[4];
sqlParams[0] = new SqlParameter("@Flow", searchModel.Flow);
sqlParams[1] = new SqlParameter("@Item", searchModel.Item);
sqlParams[2] = new SqlParameter("@ItemGroup", searchModel.MaterialsGroup);
//table returned from SP is a temporary table ,so colculate columns in SP.
return GetTableHtmlByStoredProcedure(ProcedureName, sqlParams);
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, MaterailPlanSearchModel searchModel)
{
string whereStatement = string.Empty;
DateTime fromTime = DateTime.Now;
DateTime toTime = DateTime.Now;
IList<object> param = new List<object>();
if (searchModel.DateType == 5)
{
var dateFrom = Utility.DateTimeHelper.GetWeekIndexDateFrom(searchModel.PlanDate);
var dateTo = Utility.DateTimeHelper.GetWeekIndexDateFrom(searchModel.PlanDateTo);
dateTo = dateTo.AddDays(6);
fromTime = DateTime.Parse(dateFrom.ToString());
toTime = DateTime.Parse(dateTo.ToString());
}
else if (searchModel.DateType == 6)
{
fromTime = DateTime.Parse(searchModel.PlanDate);
toTime = DateTime.Parse(searchModel.PlanDateTo).AddMonths(1).AddMinutes(-1);
}
//可以查到所有的fromTime以后的数据
//toTime = DateTime.MaxValue;
HqlStatementHelper.AddEqStatement("PlanVersion", searchModel.PlanVersion, "r", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Flow", searchModel.Flow, "r", ref whereStatement, param);
if (!string.IsNullOrWhiteSpace(searchModel.Item))
{
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "r", ref whereStatement, param);
}
if (searchModel.IsStartTime && !string.IsNullOrWhiteSpace(searchModel.PlanDate))
{
if (searchModel.DateType == 6 || searchModel.DateType == 5)
{
HqlStatementHelper.AddBetweenStatement("StartTime", fromTime, toTime, "r", ref whereStatement, param);
}
else
{
HqlStatementHelper.AddEqStatement("StartTime", DateTime.Parse(searchModel.PlanDate), "r", ref whereStatement, param);
}
}
else
{
if (searchModel.DateType == 6 || searchModel.DateType == 5)
{
HqlStatementHelper.AddBetweenStatement("WindowTime", fromTime, toTime, "r", ref whereStatement, param);
}
else
{
HqlStatementHelper.AddEqStatement("WindowTime", DateTime.Parse(searchModel.PlanDate), "r", ref whereStatement, param);
}
}
HqlStatementHelper.AddEqStatement("DateType", searchModel.DateType, "r", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("DateType", searchModel.DateType, "r", ref whereStatement, param);
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
if (command.SortDescriptors.Count == 0)
{
if (searchModel.IsStartTime)
{
sortingStatement = " order by Item ,StartTime";
}
else
{
sortingStatement = " order by Item ,WindowTime";
}
}
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = selectCountStatement;
searchStatementModel.SelectStatement = selectStatement;
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
public ActionResult _AjaxList(GridCommand command, MaterailPlanSearchModel searchModel)
{
if (searchModel.PlanVersion == null)
{
return PartialView(new GridModel(new List<PurchasePlan>()));
}
command.PageSize = 1000;
SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel);
GridModel<PurchasePlan> purchasePlanList = GetAjaxPageData<PurchasePlan>(searchStatementModel, command);
var flowDic = this.flowMgr.GetFlowDetailList(searchModel.Flow, true)
.ToDictionary(d=>d.Item,d=>d);
foreach (var purchasePlan in purchasePlanList.Data)
{
purchasePlan.CurrentQty =Math.Round( purchasePlan.Qty,2);
purchasePlan.ItemDescription = this.itemMgr.GetCacheItem(purchasePlan.Item).Description;
if (purchasePlan.DateType == CodeMaster.TimeUnit.Week)
{
purchasePlan.DateIndexValue = Utility.DateTimeHelper.GetWeekOfYear(purchasePlan.WindowTime);
}
else
{
purchasePlan.DateIndexValue = purchasePlan.WindowTime.ToString("yyyy-MM");
}
purchasePlan.Uom = flowDic.ValueOrDefault(purchasePlan.Item).Uom;
purchasePlan.UnitCount = flowDic.ValueOrDefault(purchasePlan.Item).UnitCount;
}
return PartialView(purchasePlanList);
}
public ActionResult _Update(GridCommand command, PurchasePlan purchasePlan,
DateTime? NplanVersion, string NplanDate, string Nflow, string Nitem, bool? NisStartTime, int NDateType)
{
MaterailPlanSearchModel searchModel = new MaterailPlanSearchModel();
searchModel.Flow = Nflow;
searchModel.IsStartTime = NisStartTime.HasValue ? NisStartTime.Value : false;
searchModel.Item = Nitem;
searchModel.PlanDate = NplanDate;
searchModel.PlanVersion = NplanVersion;
searchModel.DateType = NDateType;
PurchasePlan newPurchasePlan = genericMgr.FindById<PurchasePlan>(purchasePlan.Id);
newPurchasePlan.Qty = purchasePlan.CurrentQty;
genericMgr.Update(newPurchasePlan);
SearchStatementModel searchStatementModel = PrepareSearchStatement(command, searchModel);
GridModel<PurchasePlan> purchasePlanList = GetAjaxPageData<PurchasePlan>(searchStatementModel, command);
foreach (var purchasePlanEntity in purchasePlanList.Data)
{
purchasePlanEntity.CurrentQty = purchasePlanEntity.Qty;
purchasePlanEntity.ItemDescription = this.itemMgr.GetCacheItem(purchasePlanEntity.Item).Description;
}
return PartialView(purchasePlanList);
}
public ActionResult _GetPurchasePlanList(GridCommand command, MaterailPlanSearchModel searchModel)
{
SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
ViewBag.PageSize = base.ProcessPageSize(command.PageSize);
ViewBag.Flow = searchModel.Flow;
ViewBag.Item = searchModel.Item;
ViewBag.PlanVersion = searchModel.PlanVersion;
ViewBag.PlanDate = searchModel.PlanDate;
ViewBag.PlanDateTo = searchModel.PlanDateTo;
ViewBag.IsStartTime = searchModel.IsStartTime;
ViewBag.DateType = searchModel.DateType;
if (searchModel.PlanVersion == null)
{
SaveWarningMessage(Resources.EXT.ControllerLan.Con_VersionTimeCanNotBeEmpty);
}
return PartialView();
}
private string GetPlanString(List<Plan> planList, MaterailPlanSearchModel searchModel)
{
bool isPlanRelease = genericMgr.FindAllIn<PurchasePlanMaster>(@" from PurchasePlanMaster as r where r.Flow=? and r.PlanVersion=? ",
new object[] { searchModel.Flow, searchModel.PlanVersion }).FirstOrDefault().IsRelease;
DateTime planVersion = searchModel.PlanVersion.Value;
CodeMaster.TimeUnit dateType = (CodeMaster.TimeUnit)searchModel.DateType;
if (planList.Count == 0)
{
return Resources.EXT.ControllerLan.Con_NoRecord;
}
StringBuilder str = new StringBuilder("<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" class=\"display\" id=\"datatable\" width=\"100%\"><thead><tr>");
str.Append("<th>");
str.Append(Resources.EXT.ControllerLan.Con_Item);
str.Append("</th>");
str.Append("<th style=\"min-width: 120px;\">");
str.Append(Resources.EXT.ControllerLan.Con_ItemDescription);
str.Append("</th>");
str.Append("<th style=\"min-width: 30px;\">");
str.Append(Resources.EXT.ControllerLan.Con_Uom);
str.Append("</th>");
if (!(searchModel.IsSupplier.HasValue && searchModel.IsSupplier.Value))
{
str.Append("<th style=\"min-width: 80px;\">");
str.Append(Resources.EXT.ControllerLan.Con_Inventory);
str.Append("</th>");
str.Append("<th style=\"min-width: 80px;\">");
str.Append(Resources.EXT.ControllerLan.Con_Inventory1);
str.Append("</th>");
str.Append("<th style=\"min-width: 80px;\">");
str.Append(Resources.EXT.ControllerLan.Con_Inventory2);
str.Append("</th>");
str.Append("<th style=\"min-width: 80px;\">");
str.Append(Resources.EXT.ControllerLan.Con_Others);
str.Append("</th>");
//str.Append("<th style=\"min-width: 80px;\">");
//str.Append("正常库存");
//str.Append("</th>");
str.Append("<th style=\"min-width: 80px;\">");
str.Append(Resources.EXT.ControllerLan.Con_InqualifiedInventory);
str.Append("</th>");
str.Append("<th style=\"min-width: 80px;\">");
str.Append(Resources.EXT.ControllerLan.Con_FreezeInventory);
str.Append("</th>");
}
var dateTimeIndexs = planList
.GroupBy(p => p.PlanDate, (k, g) => new { PlanDate = k, WindowTime = g.First().WindowTime })
.OrderBy(r => r.PlanDate).Take(16);
foreach (var planDateIndex in dateTimeIndexs)
{
//todo 计划转订单 Url_Mrp_MaterialPlan_PlanToOrder
str.Append("<th style=\"min-width: 50px;\">");
string head_date = string.Empty;
if (dateType == CodeMaster.TimeUnit.Day)
{
head_date = planDateIndex.PlanDate.ToString("MM-dd");
}
else if (dateType == CodeMaster.TimeUnit.Week)
{
head_date = DateTimeHelper.GetWeekOfYear(planDateIndex.PlanDate);
}
else if (dateType == CodeMaster.TimeUnit.Month)
{
head_date = planDateIndex.PlanDate.ToString("yyyy-MM");
}
if (this.CurrentUser.UrlPermissions.Contains("Url_Mrp_MaterialPlan_PlanToOrder") && isPlanRelease && !searchModel.IsSupplier.HasValue)
{
head_date = string.Format("<a href='http://{0}{1}ProcurementOrder/NewFromPlan?Flow={2}&WindowTime={3}&PlanVersion={4}&BackUrl={5}&MaterialsGroup={6}&Item={7}'>{8}</a>",
HttpContext.Request.Url.Authority, HttpContext.Request.ApplicationPath, planList.First().Flow,
planDateIndex.WindowTime.ToString("yyyy-MM-dd"), planVersion.ToString(), searchModel.BackUrl, searchModel.MaterialsGroup, searchModel.Item, head_date);
}
str.Append(head_date);
str.Append("</th>");
}
str.Append("</tr></thead><tbody>");
var planGroupByItem = from p in planList
orderby p.Sequence, p.Item
group p by
new
{
Item = p.Item
} into g
select new
{
Item = g.Key.Item,
Dic = (from q in g
group q by q.PlanDate into g1
select new
{
PlanDate = g1.Key,
Qty = g1.Sum(r => r.Qty)
}).ToDictionary(d => d.PlanDate, d => d.Qty)
};
//实时库存
string sql = @"select l.* from VIEW_LocationDet as l inner join MD_Location as loc on l.Location = loc.Code where 1 = 1 and loc.isMrp=1 ";
if (planGroupByItem.Count()>0)
{
string itemsInSql = " and l.Item in( ";
foreach(var item in planGroupByItem)
{
itemsInSql += "'" + item.Item + "',";
}
sql += itemsInSql.Substring(0, itemsInSql.Length - 1) + ")";
}
IList<LocationDetailView> locationDetailViewList = this.genericMgr.FindEntityWithNativeSql<LocationDetailView>(sql);
var inventoryBalances = locationDetailViewList
.GroupBy(p => new { p.Item })
.ToDictionary(d => d.Key.Item, d => new { Qty = d.Sum(q => q.Qty), QualifyQty = d.Sum(q => q.QualifyQty), RejectQty = d.Sum(q => q.RejectQty), FreezeQty = d.Sum(q => q.FreezeQty)});
var inventoryBalancesByLocation = locationDetailViewList
.GroupBy(p => new { p.Location,p.Item })
.ToDictionary(d => new{d.Key.Location,d.Key.Item}, d => new { Qty = d.Sum(q => q.Qty)});
int l = 0;
foreach (var planItem in planGroupByItem)
{
Item newItem = itemMgr.GetCacheItem(planItem.Item);
var inventoryBalance = inventoryBalances.ValueOrDefault(planItem.Item);
l++;
if (l % 2 == 0)
{
str.Append("<tr class=\"t-alt\">");
}
else
{
str.Append("<tr>");
}
var inv9101 = inventoryBalancesByLocation.ValueOrDefault(new { Location = "9101", Item = planItem.Item });
var inv9102 = inventoryBalancesByLocation.ValueOrDefault(new { Location = "9102", Item = planItem.Item });
var inv9103 = inventoryBalancesByLocation.ValueOrDefault(new { Location = "9103", Item = planItem.Item });
str.Append("<td >");
str.Append(planItem.Item);
str.Append("</td>");
str.Append("<td >");
str.Append(newItem.Description);
str.Append("</td>");
str.Append("<td>");
str.Append(newItem.Uom);
str.Append("</td>");
if (!(searchModel.IsSupplier.HasValue && searchModel.IsSupplier.Value))
{
str.Append("<td>");
str.Append(inv9101==null?"0":inv9101.Qty.ToString("0.##"));
str.Append("</td>");
str.Append("<td>");
str.Append(inv9102 == null ? "0" : inv9102.Qty.ToString("0.##"));
str.Append("</td>");
str.Append("<td>");
str.Append(inv9103 == null ? "0" : inv9103.Qty.ToString("0.##"));
str.Append("</td>");
str.Append("<td>");
str.Append((inventoryBalance == null ? 0 : (inventoryBalance.Qty - inventoryBalance.RejectQty - inventoryBalance.FreezeQty)) - (inv9101 == null ? 0 : inv9101.Qty) - (inv9102 == null ? 0 : inv9102.Qty) - (inv9103 == null ? 0 : inv9103.Qty));
str.Append("</td>");
//str.Append("<td>");
//str.Append((inventoryBalance == null ? 0 : (inventoryBalance.Qty - inventoryBalance.RejectQty - inventoryBalance.FreezeQty)).ToString("0.##"));
//str.Append("</td>");
str.Append("<td>");
str.Append((inventoryBalance == null ? 0 : inventoryBalance.RejectQty).ToString("0.##"));
str.Append("</td>");
str.Append("<td>");
str.Append((inventoryBalance == null ? 0 : inventoryBalance.FreezeQty).ToString("0.##"));
str.Append("</td>");
}
#region
foreach (var dateTimeIndex in dateTimeIndexs)
{
var qty = planItem.Dic.ValueOrDefault(dateTimeIndex.PlanDate);
qty = qty > 0 ? qty : 0;
str.Append("<td>");
if (newItem.Uom == "PC" || newItem.Uom == "EA")
{
str.Append(qty.ToString("0"));
}
else
{
str.Append(qty.ToString("0.##"));
}
str.Append("</td>");
}
#endregion
str.Append("</tr>");
}
//表尾
str.Append("</tbody>");
str.Append("</table>");
return str.ToString();
}
