private List <UpdateMCAssignmentResult> CalcMCAssignments(bool updateDB) { UpdateMCAssignmentSettings us = UpdateSettings.Current; List <UpdateMCAssignmentResult> list = new List <UpdateMCAssignmentResult>(); if (us == null) { return(list); } //empty if ((us.SiteID == null) || (us.StartFinPeriodID == null) || (us.EndFinPeriodID == null)) { return(list); } //empty if (updateDB) { itemsite.Cache.Clear(); } PXSelectBase <INItemSite> cmd = new PXSelectJoin <INItemSite, InnerJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <INItemSite.inventoryID>, And <InventoryItem.stkItem, Equal <boolTrue> > > >, Where <INItemSite.siteID, Equal <Current <UpdateMCAssignmentSettings.siteID> > > >(this); foreach (PXResult <INItemSite, InventoryItem> resultset in cmd.Select()) { INItemSite currentItemSite = (INItemSite)resultset; InventoryItem inventoryItem = (InventoryItem)resultset; UpdateMCAssignmentResult updateMC = new UpdateMCAssignmentResult(); updateMC.MCFixed = currentItemSite.MovementClassIsFixed; updateMC.Descr = inventoryItem.Descr; updateMC.InventoryID = currentItemSite.InventoryID; updateMC.OldMC = currentItemSite.MovementClassID; if (updateMC.MCFixed == true) { updateMC.NewMC = currentItemSite.MovementClassID; } else { PXSelectBase <FinPeriod> cmd1 = new PXSelectJoinGroupBy <FinPeriod, InnerJoin <INItemCostHist, On <INItemCostHist.finPeriodID, LessEqual <FinPeriod.finPeriodID> >, InnerJoin <INLocation, On < Where2 < Where < INLocation.isCosted, Equal <boolFalse>, And <INItemCostHist.costSiteID, Equal <INLocation.siteID> > >, Or < Where < INLocation.isCosted, NotEqual <boolFalse>, And <INItemCostHist.costSiteID, Equal <INLocation.locationID> > > > > > > >, Where <INLocation.siteID, Equal <Current <UpdateMCAssignmentSettings.siteID> >, And <INItemCostHist.inventoryID, Equal <Required <INItemCostHist.inventoryID> >, And <FinPeriod.finPeriodID, GreaterEqual <Current <UpdateMCAssignmentSettings.startFinPeriodID> >, And <FinPeriod.finPeriodID, LessEqual <Current <UpdateMCAssignmentSettings.endFinPeriodID> > > > > >, Aggregate < Avg <INItemCostHist.finYtdCost, Avg <INItemCostHist.tranYtdCost, Avg <INItemCostHist.finBegQty, Avg <INItemCostHist.tranBegQty, Avg <INItemCostHist.finYtdQty, Avg <INItemCostHist.tranYtdQty> > > > > > > >(this); INItemCostHist itemHistTranYtdQty = null; decimal tranYtdQty = 0m; foreach (PXResult <FinPeriod, INItemCostHist> ih in cmd1.Select(currentItemSite.InventoryID)) { itemHistTranYtdQty = (INItemCostHist)ih; tranYtdQty += itemHistTranYtdQty.TranYtdQty ?? 0m; } PXSelectBase <INItemCostHist> cmd2 = new PXSelectJoinGroupBy <INItemCostHist, LeftJoin <INLocation, On < Where2 < Where < INLocation.isCosted, Equal <boolFalse>, And <INItemCostHist.costSiteID, Equal <INLocation.siteID> > >, Or < Where < INLocation.isCosted, NotEqual <boolFalse>, And <INItemCostHist.costSiteID, Equal <INLocation.siteID> > > > > > >, Where <INLocation.siteID, Equal <Current <UpdateMCAssignmentSettings.siteID> >, And <INItemCostHist.inventoryID, Equal <Required <INItemCostHist.inventoryID> >, And <INItemCostHist.finPeriodID, GreaterEqual <Current <UpdateMCAssignmentSettings.startFinPeriodID> >, And <INItemCostHist.finPeriodID, LessEqual <Current <UpdateMCAssignmentSettings.endFinPeriodID> > > > > >, Aggregate <Sum <INItemCostHist.tranPtdQtyReceived, Sum <INItemCostHist.tranPtdQtyIssued, Sum <INItemCostHist.tranPtdQtySales, Sum <INItemCostHist.tranPtdQtyCreditMemos, Sum <INItemCostHist.tranPtdQtyDropShipSales, Sum <INItemCostHist.tranPtdQtyTransferIn, Sum <INItemCostHist.tranPtdQtyTransferOut, Sum <INItemCostHist.tranPtdQtyAdjusted, Sum <INItemCostHist.finPtdQtyReceived, Sum <INItemCostHist.finPtdQtyIssued, Sum <INItemCostHist.finPtdQtySales, Sum <INItemCostHist.finPtdQtyCreditMemos, Sum <INItemCostHist.finPtdQtyDropShipSales, Sum <INItemCostHist.finPtdQtyTransferIn, Sum <INItemCostHist.finPtdQtyTransferOut, Sum <INItemCostHist.finPtdQtyAdjusted, Avg <INItemCostHist.tranYtdQty> > > > > > > > > > > > > > > > > > >(this); INItemCostHist itemHistTranPtdQtySales = null; decimal tranPtdQtySales = 0m; foreach (PXResult <INItemCostHist> ih in cmd2.Select(currentItemSite.InventoryID)) { itemHistTranPtdQtySales = (INItemCostHist)ih; tranPtdQtySales += (itemHistTranPtdQtySales.TranPtdQtySales ?? 0m); } if (tranYtdQty != 0m || tranPtdQtySales != 0m) { decimal radio = MovementToStockRatio(tranYtdQty, tranPtdQtySales); PXSelectBase <INMovementClass> cmd3 = new PXSelect <INMovementClass, Where <INMovementClass.maxTurnoverPct, GreaterEqual <Required <INMovementClass.maxTurnoverPct> > >, OrderBy <Asc <INMovementClass.maxTurnoverPct> > >(this); INMovementClass movementClass = (INMovementClass)cmd3.Select(radio); if (movementClass != null) { updateMC.NewMC = movementClass.MovementClassID; } else { updateMC.NewMC = null; } } } updateMC.OldMC = currentItemSite.MovementClassID; list.Add(updateMC); if (updateDB && (currentItemSite.MovementClassID != updateMC.NewMC)) { currentItemSite.MovementClassID = updateMC.NewMC; itemsite.Update(currentItemSite); } } if (updateDB) { this.Actions.PressSave(); } return(list); }
private List <UpdateMCAssignmentResult> CalcMCAssignments(bool updateDB) { UpdateMCAssignmentSettings us = UpdateSettings.Current; List <UpdateMCAssignmentResult> list = new List <UpdateMCAssignmentResult>(); if (us == null) { return(list); } //empty if ((us.SiteID == null) || (us.StartFinPeriodID == null) || (us.EndFinPeriodID == null)) { return(list); } //empty if (updateDB) { itemsite.Cache.Clear(); } PXSelectBase <INItemSite> cmd = new PXSelectJoin <INItemSite, InnerJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <INItemSite.inventoryID>, And <InventoryItem.stkItem, Equal <boolTrue> > > >, Where <INItemSite.siteID, Equal <Current <UpdateMCAssignmentSettings.siteID> > > >(this); foreach (PXResult <INItemSite, InventoryItem> resultset in cmd.Select()) { INItemSite currentItemSite = (INItemSite)resultset; InventoryItem inventoryItem = (InventoryItem)resultset; UpdateMCAssignmentResult updateMC = new UpdateMCAssignmentResult(); updateMC.MCFixed = currentItemSite.MovementClassIsFixed; updateMC.Descr = inventoryItem.Descr; updateMC.InventoryID = currentItemSite.InventoryID; updateMC.OldMC = currentItemSite.MovementClassID; if (updateMC.MCFixed == true) { updateMC.NewMC = currentItemSite.MovementClassID; } else { PXSelectBase <INItemSiteHistDay> cmd1 = new PXSelectJoinGroupBy <INItemSiteHistDay, InnerJoin <INItemSiteHistByLastDayInPeriod, On <INItemSiteHistDay.inventoryID, Equal <INItemSiteHistByLastDayInPeriod.inventoryID>, And <INItemSiteHistDay.siteID, Equal <INItemSiteHistByLastDayInPeriod.siteID>, And <INItemSiteHistDay.subItemID, Equal <INItemSiteHistByLastDayInPeriod.subItemID>, And <INItemSiteHistDay.locationID, Equal <INItemSiteHistByLastDayInPeriod.locationID>, And <INItemSiteHistDay.sDate, Equal <INItemSiteHistByLastDayInPeriod.lastActivityDate> > > > > > >, Where <INItemSiteHistDay.siteID, Equal <Current <UpdateMCAssignmentSettings.siteID> >, And <INItemSiteHistDay.inventoryID, Equal <Required <INItemSiteHistDay.inventoryID> >, And <INItemSiteHistDay.sDate, Greater <Current <UpdateMCAssignmentSettings.startDateForAvailableQuantity> >, And <INItemSiteHistByLastDayInPeriod.finPeriodID, LessEqual <Current <UpdateMCAssignmentSettings.endFinPeriodID> > > > > >, Aggregate < Sum <INItemSiteHistDay.endQty, Count <INItemSiteHistByLastDayInPeriod.finPeriodID> > > >(this); decimal tranYtdQty = 0m; PXResult <INItemSiteHistDay> ihYtdQty = cmd1.Select(currentItemSite.InventoryID); var itemHistTranYtdQty = (INItemSiteHistDay)ihYtdQty; if (ihYtdQty.RowCount != 0) { tranYtdQty += (itemHistTranYtdQty.EndQty ?? 0m) / ihYtdQty.RowCount.Value; } PXSelectBase <INItemSiteHistDay> cmd2 = new PXSelectJoinGroupBy <INItemSiteHistDay, InnerJoin <MasterFinPeriod, On <MasterFinPeriod.startDate, LessEqual <INItemSiteHistDay.sDate>, And <INItemSiteHistDay.sDate, Less <MasterFinPeriod.endDate> > > >, Where <INItemSiteHistDay.siteID, Equal <Current <UpdateMCAssignmentSettings.siteID> >, And <INItemSiteHistDay.inventoryID, Equal <Required <INItemCostHist.inventoryID> >, And <MasterFinPeriod.finPeriodID, GreaterEqual <Current <UpdateMCAssignmentSettings.startFinPeriodID> >, And <MasterFinPeriod.finPeriodID, LessEqual <Current <UpdateMCAssignmentSettings.endFinPeriodID> > > > > >, Aggregate < Sum <INItemSiteHistDay.qtySales, Count <MasterFinPeriod.finPeriodID> > > >(this); decimal tranPtdQtySales = 0m; PXResult <INItemSiteHistDay> ihPtdQtySales = cmd2.Select(currentItemSite.InventoryID); INItemSiteHistDay itemHistTranPtdQtySales = (INItemSiteHistDay)ihPtdQtySales; if (ihPtdQtySales.RowCount != 0) { tranPtdQtySales += (itemHistTranPtdQtySales.QtySales ?? 0m) / ihPtdQtySales.RowCount.Value; } if (tranYtdQty != 0m || tranPtdQtySales != 0m) { decimal radio = MovementToStockRatio(tranYtdQty, tranPtdQtySales); PXSelectBase <INMovementClass> cmd3 = new PXSelectReadonly <INMovementClass, Where <INMovementClass.maxTurnoverPct, GreaterEqual <Required <INMovementClass.maxTurnoverPct> > >, OrderBy <Asc <INMovementClass.maxTurnoverPct> > >(this); INMovementClass movementClass = (INMovementClass)cmd3.Select(radio); if (movementClass != null) { updateMC.NewMC = movementClass.MovementClassID; } else { updateMC.NewMC = null; } } } updateMC.OldMC = currentItemSite.MovementClassID; list.Add(updateMC); if (updateDB && (currentItemSite.MovementClassID != updateMC.NewMC)) { currentItemSite.MovementClassID = updateMC.NewMC; itemsite.Update(currentItemSite); } } if (updateDB) { this.Actions.PressSave(); } return(list); }