private void SetPeriods(UpdateMCAssignmentSettings settings) { INSetup insetup = INSetup.Current; PXCache cache = UpdateSettings.Cache; if (insetup != null && insetup.TurnoverPeriodsPerYear != null && insetup.TurnoverPeriodsPerYear != 0m && settings.PeriodNbr != null && settings.Year != null) { String periodString = (12 / insetup.TurnoverPeriodsPerYear * (settings.PeriodNbr - 1) + 1).ToString(); periodString = periodString.Length == 1 ? "0" + periodString : periodString; periodString = settings.Year + periodString; FinPeriod startPeriod = (FinPeriod)PXSelect <FinPeriod, Where <FinPeriod.finPeriodID, Equal <Required <FinPeriod.finPeriodID> > > > .Select(this, periodString); if (startPeriod != null) { //cache.SetValueExt<UpdateMCAssignmentSettings.startFinPeriodID>(settings, startPeriod.FinPeriodID); settings.StartFinPeriodID = startPeriod.FinPeriodID; cache.SetValueExt <UpdateMCAssignmentSettings.startDate>(settings, startPeriod.StartDate); } periodString = (12 / insetup.TurnoverPeriodsPerYear * settings.PeriodNbr).ToString(); periodString = periodString.Length == 1 ? "0" + periodString : periodString; periodString = settings.Year + periodString; FinPeriod endPeriod = (FinPeriod)PXSelect <FinPeriod, Where <FinPeriod.finPeriodID, Equal <Required <FinPeriod.finPeriodID> > > > .Select(this, periodString); if (endPeriod != null) { //cache.SetValueExt<UpdateMCAssignmentSettings.endFinPeriodID>(settings, endPeriod.FinPeriodID); settings.EndFinPeriodID = endPeriod.FinPeriodID; cache.SetValueExt <UpdateMCAssignmentSettings.endDate>(settings, endPeriod.EndDate); } } }
private void SetPeriods(UpdateMCAssignmentSettings settings) { INSetup insetup = INSetup.Current; PXCache cache = UpdateSettings.Cache; if (insetup != null && insetup.TurnoverPeriodsPerYear != null && insetup.TurnoverPeriodsPerYear != 0m && settings.PeriodNbr != null && settings.Year != null) { String periodString = (12 / insetup.TurnoverPeriodsPerYear * (settings.PeriodNbr - 1) + 1).ToString(); periodString = periodString.Length == 1 ? "0" + periodString : periodString; periodString = settings.Year + periodString; FinPeriod startPeriod = FinPeriodRepository.FindByID(FinPeriod.organizationID.MasterValue, periodString); if (startPeriod != null) { //cache.SetValueExt<UpdateMCAssignmentSettings.startFinPeriodID>(settings, startPeriod.FinPeriodID); settings.StartFinPeriodID = startPeriod.FinPeriodID; cache.SetValueExt <UpdateMCAssignmentSettings.startDate>(settings, startPeriod.StartDate); } periodString = (12 / insetup.TurnoverPeriodsPerYear * settings.PeriodNbr).ToString(); periodString = periodString.Length == 1 ? "0" + periodString : periodString; periodString = settings.Year + periodString; FinPeriod endPeriod = FinPeriodRepository.FindByID(FinPeriod.organizationID.MasterValue, periodString); if (endPeriod != null) { //cache.SetValueExt<UpdateMCAssignmentSettings.endFinPeriodID>(settings, endPeriod.FinPeriodID); settings.EndFinPeriodID = endPeriod.FinPeriodID; cache.SetValueExt <UpdateMCAssignmentSettings.endDate>(settings, endPeriod.EndDate); } } }
protected virtual void UpdateMCAssignmentSettings_Year_FieldUpdated(PXCache cache, PXFieldUpdatedEventArgs e) { UpdateMCAssignmentSettings settings = (UpdateMCAssignmentSettings)e.Row; if (settings == null) { return; } SetPeriods(settings); }
protected virtual void UpdateMCAssignmentSettings_PeriodNbr_FieldVerifying(PXCache cache, PXFieldVerifyingEventArgs e) { UpdateMCAssignmentSettings settings = (UpdateMCAssignmentSettings)e.Row; if (settings == null) { return; } INSetup insetup = INSetup.Current; if (((short)e.NewValue) > insetup.TurnoverPeriodsPerYear) { cache.RaiseExceptionHandling <UpdateMCAssignmentSettings.periodNbr>(settings, e.NewValue, new PXSetPropertyException(Messages.PeriofNbrCanNotBeGreaterThenInSetup)); } }
protected virtual void UpdateMCAssignmentSettings_Year_FieldDefaulting(PXCache cache, PXFieldDefaultingEventArgs e) { UpdateMCAssignmentSettings settings = (UpdateMCAssignmentSettings)e.Row; if (settings == null) { return; } DateTime date = (DateTime)Accessinfo.BusinessDate; e.NewValue = date.Year.ToString(); e.Cancel = true; settings.Year = date.Year.ToString(); settings.PeriodNbr = 1; SetPeriods(settings); }
private void SetPeriods(UpdateMCAssignmentSettings settings) { INSetup insetup = INSetup.Current; PXCache cache = UpdateSettings.Cache; if (insetup != null && insetup.TurnoverPeriodsPerYear != null && insetup.TurnoverPeriodsPerYear != 0m && settings.PeriodNbr != null && settings.Year != null) { String periodString = (12 / insetup.TurnoverPeriodsPerYear * (settings.PeriodNbr - 1) + 1).ToString(); periodString = periodString.Length == 1 ? "0" + periodString : periodString; periodString = settings.Year + periodString; FinPeriod startPeriod = FinPeriodRepository.FindByID(FinPeriod.organizationID.MasterValue, periodString); if (startPeriod != null) { //cache.SetValueExt<UpdateMCAssignmentSettings.startFinPeriodID>(settings, startPeriod.FinPeriodID); settings.StartFinPeriodID = startPeriod.FinPeriodID; cache.SetValueExt <UpdateMCAssignmentSettings.startDate>(settings, startPeriod.StartDate); } periodString = (12 / insetup.TurnoverPeriodsPerYear * settings.PeriodNbr).ToString(); periodString = periodString.Length == 1 ? "0" + periodString : periodString; periodString = settings.Year + periodString; FinPeriod endPeriod = FinPeriodRepository.FindByID(FinPeriod.organizationID.MasterValue, periodString); if (endPeriod != null) { //cache.SetValueExt<UpdateMCAssignmentSettings.endFinPeriodID>(settings, endPeriod.FinPeriodID); settings.EndFinPeriodID = endPeriod.FinPeriodID; cache.SetValueExt <UpdateMCAssignmentSettings.endDate>(settings, endPeriod.EndDate); const int NumberOfMonthsToCalcAvailableQuantity = 12; var startDateForAvailableQuantity = endPeriod.EndDate.HasValue ? (DateTime?)endPeriod.EndDate.Value.AddMonths(-NumberOfMonthsToCalcAvailableQuantity) : null; cache.SetValueExt <UpdateMCAssignmentSettings.startDateForAvailableQuantity>(settings, startDateForAvailableQuantity); } } }
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); }