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);
}