private List <UpdateABCAssignmentResult> CalcABCAssignments(bool updateDB) { UpdateABCAssignmentSettings us = UpdateSettings.Current; List <UpdateABCAssignmentResult> result_list = new List <UpdateABCAssignmentResult>(); if (us == null) { return(result_list); } //empty if ((us.SiteID == null) || (us.FinPeriodID == null)) { return(result_list); } //empty if (updateDB) { itemsite.Cache.Clear(); } CostHistoryBySiteByPeriod costHistory = CreateCostHistory(us.SiteID.Value, us.FinPeriodID); PXSelectBase <INItemSite> cmd = new PXSelectJoin <INItemSite, InnerJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <INItemSite.inventoryID>, And <InventoryItem.stkItem, NotEqual <boolFalse>, And <Match <InventoryItem, Current <AccessInfo.userName> > > > > >, Where <INItemSite.siteID, Equal <Current <UpdateABCAssignmentSettings.siteID> > > >(this); PXResultset <INItemSite> intermediateResult = (PXResultset <INItemSite>)cmd.Select(); // 1. set next non-fixed item position to [0] // 2. for each ABC code X starting from 'A' to 'Z' // { // 2.1 move items having X code to the result list, counting their cost until cumulative cost not greater than cumulative ABC cost // (fixed-ABC-code items do not change their code) // } // 0.1 PXResultset <INABCCode> abc_codes = PXSelectOrderBy <INABCCode, OrderBy <Asc <INABCCode.aBCCodeID> > > .Select(this); //0.2 decimal total_cost_on_site = costHistory.GetTotalCostOnSite(); // 0.3 if ((abc_codes.Count == 0) || (total_cost_on_site == 0)) { // nothing to change : foreach (PXResult <INItemSite, InventoryItem> it in intermediateResult) { INItemSite is_rec = (INItemSite)it; InventoryItem ii_rec = (InventoryItem)it; UpdateABCAssignmentResult r_rec = new UpdateABCAssignmentResult(); r_rec.ABCCodeFixed = is_rec.ABCCodeIsFixed; r_rec.Descr = ii_rec.Descr; r_rec.InventoryID = is_rec.InventoryID; r_rec.OldABCCode = is_rec.ABCCodeID; r_rec.NewABCCode = is_rec.ABCCodeID; // null ? result_list.Add(r_rec); } return(result_list); } intermediateResult.Sort((x, y) => { INItemSite a = y; INItemSite b = x; decimal tranYtdCost_X = costHistory.GetTranYTDCost(a.InventoryID.Value); decimal tranYtdCost_Y = costHistory.GetTranYTDCost(b.InventoryID.Value); return(tranYtdCost_X.CompareTo(tranYtdCost_Y)); }); // 1. set next item position to [0] int next_item_to_process = 0; decimal cumulative_cost = 0m; decimal cumulative_abc_pct = 0m; // 2. for each ABC code X starting from 'A' to 'Z' foreach (PXResult <INABCCode> abc_it in abc_codes) { INABCCode abc_rec = (INABCCode)abc_it; cumulative_abc_pct += (abc_rec.ABCPct ?? 0m); // 2.1 move items having X code to the result list, counting their cost until cumulative cost not greater than cumulative ABC cost // (fixed-ABC-code items do not change their code) while (next_item_to_process < intermediateResult.Count) { PXResult <INItemSite, InventoryItem> it = (PXResult <INItemSite, InventoryItem>)intermediateResult[next_item_to_process]; INItemSite is_rec = (INItemSite)it; InventoryItem ii_rec = (InventoryItem)it; decimal tranYtdCost = costHistory.GetTranYTDCost(ii_rec.InventoryID.Value); if (((cumulative_cost + tranYtdCost) / total_cost_on_site) <= (cumulative_abc_pct / 100m)) { cumulative_cost += tranYtdCost; UpdateABCAssignmentResult r_rec = new UpdateABCAssignmentResult(); r_rec.ABCCodeFixed = is_rec.ABCCodeIsFixed; r_rec.Descr = ii_rec.Descr; r_rec.InventoryID = is_rec.InventoryID; if (is_rec.ABCCodeIsFixed ?? false) { r_rec.NewABCCode = is_rec.ABCCodeID; } else { r_rec.NewABCCode = abc_rec.ABCCodeID; } r_rec.OldABCCode = is_rec.ABCCodeID; r_rec.YtdCost = tranYtdCost; r_rec.Ratio = r_rec.YtdCost / total_cost_on_site * 100; r_rec.CumulativeRatio = cumulative_cost / total_cost_on_site * 100; result_list.Add(r_rec); if (updateDB && (is_rec.ABCCodeID != r_rec.NewABCCode)) { is_rec.ABCCodeID = r_rec.NewABCCode; itemsite.Update(is_rec); } next_item_to_process++; } else { break; } } } if (updateDB) { this.Actions.PressSave(); } return(result_list); }
private List <UpdateABCAssignmentResult> CalcABCAssignments(bool updateDB) { UpdateABCAssignmentSettings us = UpdateSettings.Current; List <UpdateABCAssignmentResult> result_list = new List <UpdateABCAssignmentResult>(); if (us == null) { return(result_list); } //empty if ((us.SiteID == null) || (us.FinPeriodID == null)) { return(result_list); } //empty if (updateDB) { itemsite.Cache.Clear(); } PXSelectBase <INItemSite> cmd = new PXSelectJoin <INItemSite, InnerJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <INItemSite.inventoryID>, And <InventoryItem.stkItem, NotEqual <boolFalse>, And <Match <InventoryItem, Current <AccessInfo.userName> > > > >, LeftJoin <ItemCostHistByPeriodByItemSite, On <ItemCostHistByPeriodByItemSite.inventoryID, Equal <INItemSite.inventoryID>, And <ItemCostHistByPeriodByItemSite.siteID, Equal <INItemSite.siteID>, And <ItemCostHistByPeriodByItemSite.finPeriodID, Equal <Current <UpdateABCAssignmentSettings.finPeriodID> > > > > > >, Where <INItemSite.siteID, Equal <Current <UpdateABCAssignmentSettings.siteID> > >, OrderBy <Desc <ItemCostHistByPeriodByItemSite.tranYtdCost, Asc <INItemSite.inventoryID> > > >(this); // if 'By Fin. Period' option will be added - we'll need change OrderBy here PXResultset <INItemSite> intermediateResult = (PXResultset <INItemSite>)cmd.Select(); // 1. set next non-fixed item position to [0] // 2. for each ABC code X starting from 'A' to 'Z' // { // 2.1 move items having X code to the result list, counting their cost until cumulative cost not greater than cumulative ABC cost // (fixed-ABC-code items do not change their code) // } // 0.1 PXResultset <INABCCode> abc_codes = PXSelectOrderBy <INABCCode, //Where<INABCCode.aBCPct, NotEqual<decimal0>>, OrderBy <Asc <INABCCode.aBCCodeID> > > .Select(this); //0.2 decimal total_cost_on_site = 0m; foreach (PXResult <INItemSite, InventoryItem, ItemCostHistByPeriodByItemSite> it in intermediateResult) { total_cost_on_site += (((ItemCostHistByPeriodByItemSite)it).TranYtdCost ?? 0m); } // 0.3 if ((abc_codes.Count == 0) || (total_cost_on_site == 0)) { // nothing to change : foreach (PXResult <INItemSite, InventoryItem, ItemCostHistByPeriodByItemSite> it in intermediateResult) { INItemSite is_rec = (INItemSite)it; InventoryItem ii_rec = (InventoryItem)it; ItemCostHistByPeriodByItemSite ich_rec = (ItemCostHistByPeriodByItemSite)it; UpdateABCAssignmentResult r_rec = new UpdateABCAssignmentResult(); r_rec.ABCCodeFixed = is_rec.ABCCodeIsFixed; r_rec.Descr = ii_rec.Descr; r_rec.InventoryID = is_rec.InventoryID; r_rec.OldABCCode = is_rec.ABCCodeID; r_rec.NewABCCode = is_rec.ABCCodeID; // null ? result_list.Add(r_rec); } return(result_list); } // 1. set next item position to [0] int next_item_to_process = 0; decimal cumulative_cost = 0m; decimal cumulative_abc_pct = 0m; // 2. for each ABC code X starting from 'A' to 'Z' foreach (PXResult <INABCCode> abc_it in abc_codes) { INABCCode abc_rec = (INABCCode)abc_it; cumulative_abc_pct += (abc_rec.ABCPct ?? 0m); // 2.1 move items having X code to the result list, counting their cost until cumulative cost not greater than cumulative ABC cost // (fixed-ABC-code items do not change their code) while (next_item_to_process < intermediateResult.Count) { PXResult <INItemSite, InventoryItem, ItemCostHistByPeriodByItemSite> it = (PXResult <INItemSite, InventoryItem, ItemCostHistByPeriodByItemSite>)intermediateResult[next_item_to_process]; INItemSite is_rec = (INItemSite)it; InventoryItem ii_rec = (InventoryItem)it; ItemCostHistByPeriodByItemSite ich_rec = (ItemCostHistByPeriodByItemSite)it; if (((cumulative_cost + (ich_rec.TranYtdCost ?? 0m)) / total_cost_on_site) <= (cumulative_abc_pct / 100m)) { cumulative_cost += (ich_rec.TranYtdCost ?? 0m); UpdateABCAssignmentResult r_rec = new UpdateABCAssignmentResult(); r_rec.ABCCodeFixed = is_rec.ABCCodeIsFixed; r_rec.Descr = ii_rec.Descr; r_rec.InventoryID = is_rec.InventoryID; if (is_rec.ABCCodeIsFixed ?? false) { r_rec.NewABCCode = is_rec.ABCCodeID; } else { r_rec.NewABCCode = abc_rec.ABCCodeID; } r_rec.OldABCCode = is_rec.ABCCodeID; r_rec.YtdCost = (ich_rec.TranYtdCost ?? 0m); r_rec.Ratio = r_rec.YtdCost / total_cost_on_site * 100; r_rec.CumulativeRatio = cumulative_cost / total_cost_on_site * 100; result_list.Add(r_rec); if (updateDB && (is_rec.ABCCodeID != r_rec.NewABCCode)) { is_rec.ABCCodeID = r_rec.NewABCCode; itemsite.Update(is_rec); } next_item_to_process++; } else { break; } } } if (updateDB) { this.Actions.PressSave(); } return(result_list); }