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