protected virtual Dictionary <int, decimal> GetTranYTDCostTableByPeriod(string periodID)
{
if (string.IsNullOrEmpty(periodID))
{
throw new ArgumentNullException();
}
if (tranYTDCostTable != null)
{
return(tranYTDCostTable);
}
tranYTDCostTable = new Dictionary <int, decimal>();
var select = new PXSelectJoinGroupBy <INItemCostHist,
InnerJoin <INItemCostHistByPeriod,
On <INItemCostHist.inventoryID, Equal <INItemCostHistByPeriod.inventoryID>,
And <INItemCostHist.costSiteID, Equal <INItemCostHistByPeriod.costSiteID>,
And <INItemCostHist.costSubItemID, Equal <INItemCostHistByPeriod.costSubItemID>,
And <INItemCostHist.accountID, Equal <INItemCostHistByPeriod.accountID>,
And <INItemCostHist.subID, Equal <INItemCostHistByPeriod.subID>,
And <INItemCostHist.finPeriodID, Equal <INItemCostHistByPeriod.lastActivityPeriod> > > > > > >,
LeftJoin <INLocation, On <INLocation.locationID, Equal <INItemCostHist.costSiteID>, And <INLocation.isCosted, Equal <True> > > > >,
Where <INItemCostHistByPeriod.finPeriodID, Equal <Required <INItemCostHistByPeriod.finPeriodID> > >,
Aggregate <
GroupBy <INItemCostHist.costSiteID,
GroupBy <INItemCostHist.inventoryID,
GroupBy <INItemCostHist.finPeriodID,
Sum <INItemCostHist.tranYtdCost> > > > >
>(context);
foreach (PXResult <INItemCostHist, INItemCostHistByPeriod, INLocation> res in select.Select(periodID))
{
INItemCostHist cost = (INItemCostHist)res;
INLocation location = (INLocation)res;
int?costSiteID = location.SiteID ?? cost.CostSiteID;
if (costSiteID == siteID)
{
if (!tranYTDCostTable.ContainsKey(cost.InventoryID.Value))
{
tranYTDCostTable.Add(cost.InventoryID.Value, cost.FinYtdCost.GetValueOrDefault());
}
else
{
tranYTDCostTable[cost.InventoryID.Value] += cost.FinYtdCost.GetValueOrDefault();
}
}
}
return(tranYTDCostTable);
}
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);
}