protected virtual IEnumerable internalResultRecords()
{
const bool useItemCostHistoryToOptimizeBalanceCalculation = true; // false to debug and ensure that INItemCostHistory corresponds to INCostTran
InventoryTranByAcctEnqFilter filter = Filter.Current;
bool summaryByDay = filter.SummaryByDay ?? false;
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.tranType>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.docRefNbr>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.subItemCD>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.siteID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.locationID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.accountID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.subID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.inventoryID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.costAdj>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.finPerNbr>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.tranPerNbr>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.qty>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.unitCost>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible(Tran.Cache, null, !summaryByDay);
var resultList = new List <PXResult <InventoryTranByAcctEnqResult, INTran> >();
decimal cumulativeBalance = 0m;
if (filter.AccountID == null)
{
return(resultList); //empty
}
if (filter.FinPeriodID == null)
{
return(resultList); //empty
}
if (useItemCostHistoryToOptimizeBalanceCalculation)
{
PXSelectBase <INItemCostHist> cmd_CostHist = new PXSelectJoinGroupBy <INItemCostHist,
InnerJoin <Sub,
On <Sub.subID, Equal <INItemCostHist.subID> > >,
Where <INItemCostHist.finPeriodID, Less <Current <InventoryTranByAcctEnqFilter.finPeriodID> > >,
Aggregate <
Sum <INItemCostHist.tranYtdCost,
Sum <INItemCostHist.tranBegCost,
Sum <INItemCostHist.finYtdCost,
Sum <INItemCostHist.finBegCost> > > > > >(this);
//if (filter.AccountID != null) // checked above
{
cmd_CostHist.WhereAnd <Where <INItemCostHist.accountID, Equal <Current <InventoryTranByAcctEnqFilter.accountID> > > >();
}
if (!SubCDUtils.IsSubCDEmpty(filter.SubCD))
{
cmd_CostHist.WhereAnd <Where <Sub.subCD, Like <Current <InventoryTranByAcctEnqFilter.subCDWildcard> > > >();
}
PXResultset <INItemCostHist> costHistResult = (PXResultset <INItemCostHist>)cmd_CostHist.Select();
if (costHistResult.Count == 1) // 0 is possible too
{
if (filter.ByFinancialPeriod == true)
{
cumulativeBalance += (((INItemCostHist)costHistResult[0]).FinYtdCost ?? 0m) - (((INItemCostHist)costHistResult[0]).FinBegCost ?? 0m);
}
else
{
cumulativeBalance += (((INItemCostHist)costHistResult[0]).TranYtdCost ?? 0m) - (((INItemCostHist)costHistResult[0]).TranBegCost ?? 0m);
}
}
}
PXSelectBase <INTranCost> cmd = new PXSelectReadonly2 <INTranCost,
InnerJoin <INTran,
On <INTran.tranType, Equal <INTranCost.tranType>,
And <INTran.refNbr, Equal <INTranCost.refNbr>,
And <INTran.lineNbr, Equal <INTranCost.lineNbr> > > >,
InnerJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <INTranCost.inventoryID>,
And <Match <InventoryItem, Current <AccessInfo.userName> > > >,
InnerJoin <Sub, On <Sub.subID, Equal <INTranCost.invtSubID> >,
InnerJoin <INSubItem, On <INSubItem.subItemID, Equal <INTranCost.costSubItemID> >,
LeftJoin <INSite, On <INSite.siteID, Equal <INTranCost.costSiteID> >,
LeftJoin <INLocation, On <INLocation.locationID, Equal <INTranCost.costSiteID> >,
LeftJoin <INCostStatus, On <INTranCost.costID, Equal <INCostStatus.costID> > > > > > > > >,
Where <INSite.siteID, IsNull,
Or <Match <INSite, Current <AccessInfo.userName> > > >,
OrderBy <Asc <INTranCost.tranDate,
Asc <INTranCost.createdDateTime> > > >(this);
//if (filter.FinPeriodID != null) // checked above
if (filter.ByFinancialPeriod == true)
{
cmd.WhereAnd <Where <INTranCost.finPeriodID, Equal <Current <InventoryTranByAcctEnqFilter.finPeriodID> > > >();
}
else
{
cmd.WhereAnd <Where <INTranCost.tranPeriodID, Equal <Current <InventoryTranByAcctEnqFilter.finPeriodID> > > >();
}
//if (filter.AccountID != null) // checked above
{
cmd.WhereAnd <Where <INTranCost.invtAcctID, Equal <Current <InventoryTranByAcctEnqFilter.accountID> > > >();
}
if (!SubCDUtils.IsSubCDEmpty(filter.SubCD))
{
cmd.WhereAnd <Where <Sub.subCD, Like <Current <InventoryTranByAcctEnqFilter.subCDWildcard> > > >();
}
if (filter.EndDate != null)
{
cmd.WhereAnd <Where <INTranCost.tranDate, LessEqual <Current <InventoryTranByAcctEnqFilter.endDate> > > >();
}
if (filter.InventoryID != null)
{
cmd.WhereAnd <Where <INTranCost.inventoryID, Equal <Current <InventoryTranByAcctEnqFilter.inventoryID> > > >();
}
if (filter.SiteID != null)
{
cmd.WhereAnd <Where <INTranCost.costSiteID, Equal <Current <InventoryTranByAcctEnqFilter.siteID> > > >();
}
int gridLineNbr = 0;
foreach (PXResult <INTranCost, INTran, InventoryItem, Sub, INSubItem, INSite, INLocation, INCostStatus> it in cmd.Select())
{
INTranCost tc_rec = (INTranCost)it;
INTran t_rec = (INTran)it;
INSite s_rec = (INSite)it;
INLocation l_rec = (INLocation)it;
INSubItem si_rec = (INSubItem)it;
INCostStatus cs_rec = (INCostStatus)it;
decimal rowCost = (tc_rec.InvtMult * tc_rec.TranCost) ?? 0m;
if (tc_rec.TranDate < filter.StartDate)
{
cumulativeBalance += rowCost;
}
else
{
if (summaryByDay)
{
if ((resultList.Count > 0) && (((InventoryTranByAcctEnqResult)resultList[resultList.Count - 1]).TranDate == tc_rec.TranDate))
{
InventoryTranByAcctEnqResult lastItem = resultList[resultList.Count - 1];
if (rowCost >= 0)
{
lastItem.Debit += rowCost;
}
else
{
lastItem.Credit -= rowCost;
}
lastItem.EndBalance += rowCost;
resultList[resultList.Count - 1] = new PXResult <InventoryTranByAcctEnqResult, INTran>(lastItem, null);
}
else
{
InventoryTranByAcctEnqResult item = new InventoryTranByAcctEnqResult();
item.BegBalance = cumulativeBalance;
item.TranDate = tc_rec.TranDate;
if (rowCost >= 0)
{
item.Debit = rowCost;
item.Credit = 0m;
}
else
{
item.Debit = 0m;
item.Credit = -rowCost;
}
item.EndBalance = item.BegBalance + rowCost;
item.GridLineNbr = ++gridLineNbr;
item.CreatedDateTime = tc_rec.CreatedDateTime;
resultList.Add(new PXResult <InventoryTranByAcctEnqResult, INTran>(item, null));
}
cumulativeBalance += rowCost;
}
else
{
InventoryTranByAcctEnqResult item = new InventoryTranByAcctEnqResult();
item.BegBalance = cumulativeBalance;
item.TranDate = tc_rec.TranDate;
if (rowCost >= 0)
{
item.Debit = rowCost;
item.Credit = 0m;
}
else
{
item.Debit = 0m;
item.Credit = -rowCost;
}
item.EndBalance = item.BegBalance + rowCost;
item.AccountID = tc_rec.InvtAcctID;
item.SubID = tc_rec.InvtSubID;
item.TranType = tc_rec.TranType;
item.DocType = t_rec.DocType;
item.DocRefNbr = tc_rec.RefNbr;
item.ReceiptNbr = cs_rec.ReceiptNbr;
item.InventoryID = tc_rec.InventoryID;
item.SubItemCD = si_rec.SubItemCD;
if (s_rec.SiteID != null)
{
item.SiteID = s_rec.SiteID;
item.LocationID = null;
}
else
if (l_rec.LocationID != null) //though it's more or less guaranteed
{
item.SiteID = l_rec.SiteID;
item.LocationID = l_rec.LocationID;
}
item.TranDate = tc_rec.TranDate;
item.FinPerNbr = tc_rec.FinPeriodID;
item.TranPerNbr = tc_rec.TranPeriodID;
item.Qty = tc_rec.Qty * tc_rec.InvtMult;
item.UnitCost = (tc_rec.Qty ?? 0m) == 0m ? null : ((tc_rec.TranCost ?? 0m) + (tc_rec.VarCost ?? 0m)) / tc_rec.Qty;
item.CostAdj = tc_rec.CostRefNbr != tc_rec.RefNbr;
item.GridLineNbr = ++gridLineNbr;
item.CreatedDateTime = tc_rec.CreatedDateTime;
resultList.Add(new PXResult <InventoryTranByAcctEnqResult, INTran>(item, t_rec));
cumulativeBalance += rowCost;
}
}
}
return(resultList);
}
protected virtual IEnumerable internalResultRecords()
{
InventoryTranHistEnqFilter filter = Filter.Current;
bool summaryByDay = filter.SummaryByDay ?? false;
bool includeUnreleased = filter.IncludeUnreleased ?? false;
var resultList = new List <PXResult <InventoryTranHistEnqResult, INTran, INTranSplit> >();
if (filter.InventoryID == null)
{
PXDelegateResult emptyResult = new PXDelegateResult();
emptyResult.IsResultFiltered = true;
emptyResult.IsResultSorted = true;
emptyResult.IsResultTruncated = true;
return(emptyResult); //empty
}
PXSelectBase <INTranSplit> cmd = new PXSelectReadonly2 <INTranSplit,
InnerJoin <INTran, On <INTranSplit.FK.Tran>,
InnerJoin <INSubItem, On <INTranSplit.FK.SubItem>,
InnerJoin <INSite, On <INTran.FK.Site> > > >,
Where <INTranSplit.inventoryID, Equal <Current <InventoryTranHistEnqFilter.inventoryID> >, And <Match <INSite, Current <AccessInfo.userName> > > >,
OrderBy <Asc <INTranSplit.docType, Asc <INTranSplit.refNbr, Asc <INTranSplit.lineNbr, Asc <INTranSplit.splitLineNbr> > > > > >(this);
PXSelectBase <INItemSiteHistByDay> cmdBegBalanceNew = new PXSelectReadonly2 <INItemSiteHistByDay,
InnerJoin <INItemSiteHistDay,
On <INItemSiteHistDay.inventoryID, Equal <INItemSiteHistByDay.inventoryID>,
And <INItemSiteHistDay.siteID, Equal <INItemSiteHistByDay.siteID>,
And <INItemSiteHistDay.subItemID, Equal <INItemSiteHistByDay.subItemID>,
And <INItemSiteHistDay.locationID, Equal <INItemSiteHistByDay.locationID>,
And <INItemSiteHistDay.sDate, Equal <INItemSiteHistByDay.lastActivityDate> > > > > >,
InnerJoin <INSubItem,
On <INSubItem.subItemID, Equal <INItemSiteHistByDay.subItemID> >,
InnerJoin <INSite, On <INSite.siteID, Equal <INItemSiteHistByDay.siteID> > > > >,
Where <INItemSiteHistByDay.inventoryID, Equal <Current <InventoryTranHistEnqFilter.inventoryID> >,
And <INItemSiteHistByDay.date, Equal <Required <INItemSiteHistByDay.date> >,
And <Match <INSite, Current <AccessInfo.userName> > > > > >(this);
if (!SubCDUtils.IsSubCDEmpty(filter.SubItemCD) && PXAccess.FeatureInstalled <FeaturesSet.subItem>())
{
cmd.WhereAnd <Where <INSubItem.subItemCD, Like <Current <InventoryTranHistEnqFilter.subItemCDWildcard> > > >();
cmdBegBalanceNew.WhereAnd <Where <INSubItem.subItemCD, Like <Current <InventoryTranHistEnqFilter.subItemCDWildcard> > > >();
}
if (filter.SiteID != null && PXAccess.FeatureInstalled <FeaturesSet.warehouse>())
{
cmd.WhereAnd <Where <INTranSplit.siteID, Equal <Current <InventoryTranHistEnqFilter.siteID> > > >();
cmdBegBalanceNew.WhereAnd <Where <INItemSiteHistByDay.siteID, Equal <Current <InventoryTranHistEnqFilter.siteID> > > >();
}
if ((filter.LocationID ?? -1) != -1 && PXAccess.FeatureInstalled <FeaturesSet.warehouseLocation>()) // there are cases when filter.LocationID = -1
{
cmd.WhereAnd <Where <INTranSplit.locationID, Equal <Current <InventoryTranHistEnqFilter.locationID> > > >();
cmdBegBalanceNew.WhereAnd <Where <INItemSiteHistByDay.locationID, Equal <Current <InventoryTranHistEnqFilter.locationID> > > >();
}
if ((filter.LotSerialNbr ?? "") != "" && PXAccess.FeatureInstalled <FeaturesSet.lotSerialTracking>())
{
cmd.WhereAnd <Where <INTranSplit.lotSerialNbr, Like <Current <InventoryTranHistEnqFilter.lotSerialNbrWildcard> > > >();
}
if (!includeUnreleased)
{
cmd.WhereAnd <Where <INTranSplit.released, Equal <True> > >();
}
decimal cumulativeQty = 0m;
if (filter.StartDate != null)
{
foreach (PXResult <INItemSiteHistByDay, INItemSiteHistDay> res in cmdBegBalanceNew.Select(filter.StartDate))
{
INItemSiteHistByDay byday = res;
INItemSiteHistDay hist = res;
cumulativeQty +=
((byday.LastActivityDate != null && byday.Date != null &&
byday.Date.Value.Date == byday.LastActivityDate.Value.Date)
? hist.BegQty
: hist.EndQty) ?? 0m;
}
if (includeUnreleased)
{
INSite site = INSite.PK.Find(this, filter.SiteID);
int calendarOrganizationID = PXAccess.GetParentOrganizationID(site?.BranchID) ?? FinPeriod.organizationID.MasterValue;
string TranPeriodID;
DateTime?PeriodStartDate;
try
{
TranPeriodID = FinPeriodRepository.GetPeriodIDFromDate(filter.StartDate, calendarOrganizationID);
PeriodStartDate = FinPeriodRepository.PeriodStartDate(TranPeriodID, calendarOrganizationID);
}
catch (PXFinPeriodException)
{
TranPeriodID = null;
PeriodStartDate = filter.StartDate;
}
PXSelectBase <OrganizationFinPeriod> periodCmd =
new PXSelectGroupBy <OrganizationFinPeriod,
Where <OrganizationFinPeriod.finPeriodID, LessEqual <Required <OrganizationFinPeriod.finPeriodID> >,
And <OrganizationFinPeriod.iNClosed, Equal <False>,
Or <OrganizationFinPeriod.finPeriodID, Equal <Required <OrganizationFinPeriod.finPeriodID> > > > >,
Aggregate <GroupBy <OrganizationFinPeriod.finPeriodID> >,
OrderBy <Asc <OrganizationFinPeriod.finPeriodID> > >(this);
List <object> periodCmdParams = new List <object>()
{
TranPeriodID, TranPeriodID
};
if (calendarOrganizationID != FinPeriod.organizationID.MasterValue)
{
periodCmd.WhereAnd <Where <OrganizationFinPeriod.organizationID, Equal <Required <OrganizationFinPeriod.organizationID> > > >();
periodCmdParams.Add(calendarOrganizationID);
}
OrganizationFinPeriod firstOpenOrCurrentClosedPeriod = periodCmd.SelectWindowed(0, 1, periodCmdParams.ToArray());
if (firstOpenOrCurrentClosedPeriod != null)
{
TranPeriodID = firstOpenOrCurrentClosedPeriod.FinPeriodID;
PeriodStartDate = FinPeriodRepository.PeriodStartDate(firstOpenOrCurrentClosedPeriod.FinPeriodID, calendarOrganizationID);
PXView v2 = new PXView(this, true, cmd.View.BqlSelect
.WhereAnd <Where <INTranSplit.tranDate, GreaterEqual <Required <INTranSplit.tranDate> > > >()
.WhereAnd <Where <INTranSplit.tranDate, Less <Required <INTranSplit.tranDate> > > >()
.WhereAnd <Where <INTranSplit.released, Equal <False> > >()
.AggregateNew <Aggregate <
GroupBy <INTranSplit.inventoryID, GroupBy <INTranSplit.invtMult, Sum <INTranSplit.baseQty> > > > >());
int splitStartRow = 0;
int splitTotalRows = 0;
foreach (PXResult <INTranSplit> res in v2.Select(new object[0], new object[] { PeriodStartDate, filter.StartDate.Value }, new object[0],
new string[0], new bool[0], new PXFilterRow[0], ref splitStartRow, 0, ref splitTotalRows))
{
INTranSplit tsRec = res;
cumulativeQty += (tsRec.InvtMult * tsRec.BaseQty) ?? 0m;
}
}
}
}
if (filter.StartDate != null)
{
cmd.WhereAnd <Where <INTranSplit.tranDate, GreaterEqual <Current <InventoryTranHistEnqFilter.startDate> > > >();
}
if (filter.EndDate != null)
{
cmd.WhereAnd <Where <INTranSplit.tranDate, LessEqual <Current <InventoryTranHistEnqFilter.endDate> > > >();
}
AlterSortsAndFilters(out string[] newSortColumns, out bool[] newDescendings, out bool sortsChanged, out PXFilterRow[] newFilters, out bool filtersChanged);
//if user clicks last, sorts will be inverted
//as it is not possible to calculate beginning balance from the end
//we will select without top from the start and then apply reverse order and select top n records
//for next page we will ommit startrow to set beginning balance correctly
//top (n, m) will be set in the outer search results since we do not reset PXView.StartRow to 0
//Also, user can set a filter or sort by a column from result DAC (sortsChanged = true, filtersChanged = true) and need to get full result set.
int startRow = 0;
bool allowSelectWithTop = !sortsChanged && !filtersChanged && !PXView.ReverseOrder;
int maximumRows = allowSelectWithTop ? PXView.StartRow + PXView.MaximumRows : 0;
int totalRows = 0;
PXView selectView = !summaryByDay
? cmd.View
: new PXView(this, true,
cmd.View.BqlSelect
.AggregateNew <Aggregate <GroupBy <INTranSplit.tranDate, Sum <INTranSplit.qtyIn, Sum <INTranSplit.qtyOut> > > > >());
List <object> intermediateResult = selectView.Select(PXView.Currents, new object[] { filter.StartDate },
new string[newSortColumns.Length], newSortColumns, newDescendings, newFilters, ref startRow, maximumRows,
ref totalRows);
int gridLineNbr = 0;
foreach (PXResult <INTranSplit, INTran, INSubItem> it in intermediateResult)
{
INTranSplit ts_rec = (INTranSplit)it;
INTran t_rec = (INTran)it;
if (summaryByDay)
{
InventoryTranHistEnqResult item = new InventoryTranHistEnqResult();
item.BegQty = cumulativeQty;
item.TranDate = ts_rec.TranDate;
item.QtyIn = ts_rec.QtyIn;
item.QtyOut = ts_rec.QtyOut;
item.EndQty = item.BegQty + ts_rec.QtyIn - ts_rec.QtyOut;
item.GridLineNbr = ++gridLineNbr;
resultList.Add(new PXResult <InventoryTranHistEnqResult, INTran, INTranSplit>(item, null, null));
cumulativeQty += (ts_rec.QtyIn - ts_rec.QtyOut) ?? 0m;
}
else
{
InventoryTranHistEnqResult item = new InventoryTranHistEnqResult();
item.BegQty = cumulativeQty;
item.TranDate = ts_rec.TranDate;
item.QtyIn = ts_rec.QtyIn;
item.QtyOut = ts_rec.QtyOut;
item.EndQty = item.BegQty + ts_rec.QtyIn - ts_rec.QtyOut;
item.DocType = ts_rec.DocType;
item.RefNbr = ts_rec.RefNbr;
item.LineNbr = ts_rec.LineNbr;
item.SplitLineNbr = ts_rec.SplitLineNbr;
item.GridLineNbr = ++gridLineNbr;
decimal?unitcost;
if (filter.ShowAdjUnitCost ?? false)
{
unitcost = ts_rec.TotalQty != null && ts_rec.TotalQty != 0m ? (ts_rec.TotalCost + ts_rec.AdditionalCost) / ts_rec.TotalQty : 0m;
}
else
{
unitcost = ts_rec.TotalQty != null && ts_rec.TotalQty != 0m ? ts_rec.TotalCost / ts_rec.TotalQty : 0m;
}
item.UnitCost = unitcost;
resultList.Add(new PXResult <InventoryTranHistEnqResult, INTran, INTranSplit>(item, t_rec, ts_rec));
cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m;
}
}
PXDelegateResult delegateResult = new PXDelegateResult();
delegateResult.IsResultFiltered = !filtersChanged;
delegateResult.IsResultSorted = !sortsChanged;
delegateResult.IsResultTruncated = totalRows > resultList.Count;
if (!PXView.ReverseOrder)
{
delegateResult.AddRange(resultList);
}
else
{
var sortedList = PXView.Sort(resultList);
delegateResult.AddRange(sortedList.Cast <PXResult <InventoryTranHistEnqResult, INTran, INTranSplit> >());
delegateResult.IsResultSorted = true;
}
return(delegateResult);
}
protected virtual IEnumerable internalResultRecords()
{
InventoryTranHistEnqFilter filter = Filter.Current;
bool summaryByDay = filter.SummaryByDay ?? false;
bool includeUnreleased = filter.IncludeUnreleased ?? false;
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.inventoryID>(ResultRecords.Cache, null, false);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.finPerNbr>(ResultRecords.Cache, null, false); //???
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.tranPerNbr>(ResultRecords.Cache, null, false); //???
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.tranType>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.docRefNbr>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.subItemID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.siteID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.locationID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.lotSerialNbr>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible(Tran.Cache, null, !summaryByDay);
var resultList = new List <PXResult <InventoryTranHistEnqResult, INTran> >();
if (filter.InventoryID == null)
{
return(resultList); //empty
}
PXSelectBase <INTranSplit> cmd = new PXSelectReadonly2 <INTranSplit,
InnerJoin <INTran,
On <INTran.docType, Equal <INTranSplit.docType>,
And <INTran.refNbr, Equal <INTranSplit.refNbr>,
And <INTran.lineNbr, Equal <INTranSplit.lineNbr> > > >,
InnerJoin <INSubItem,
On <INSubItem.subItemID, Equal <INTranSplit.subItemID> >,
InnerJoin <INSite, On <INSite.siteID, Equal <INTran.siteID> > > > >,
Where <INTranSplit.inventoryID, Equal <Current <InventoryTranHistEnqFilter.inventoryID> >, And <Match <INSite, Current <AccessInfo.userName> > > >,
OrderBy <Asc <INTranSplit.docType, Asc <INTranSplit.refNbr, Asc <INTranSplit.lineNbr, Asc <INTranSplit.splitLineNbr> > > > > >(this);
PXSelectBase <INItemSiteHistByPeriod> cmdBegBalance = new PXSelectReadonly2 <INItemSiteHistByPeriod,
InnerJoin <INItemSiteHist,
On <INItemSiteHist.inventoryID, Equal <INItemSiteHistByPeriod.inventoryID>,
And <INItemSiteHist.siteID, Equal <INItemSiteHistByPeriod.siteID>,
And <INItemSiteHist.subItemID, Equal <INItemSiteHistByPeriod.subItemID>,
And <INItemSiteHist.locationID, Equal <INItemSiteHistByPeriod.locationID>,
And <INItemSiteHist.finPeriodID, Equal <INItemSiteHistByPeriod.lastActivityPeriod> > > > > >,
InnerJoin <INSubItem,
On <INSubItem.subItemID, Equal <INItemSiteHistByPeriod.subItemID> >,
InnerJoin <INSite, On <INSite.siteID, Equal <INItemSiteHistByPeriod.siteID> > > > >,
Where <INItemSiteHistByPeriod.inventoryID, Equal <Current <InventoryTranHistEnqFilter.inventoryID> >,
And <INItemSiteHistByPeriod.finPeriodID, Equal <Required <INItemSiteHistByPeriod.finPeriodID> >,
And <Match <INSite, Current <AccessInfo.userName> > > > > >(this);
if (!SubCDUtils.IsSubCDEmpty(filter.SubItemCD) && PXAccess.FeatureInstalled <FeaturesSet.subItem>())
{
cmdBegBalance.WhereAnd <Where <INSubItem.subItemCD, Like <Current <InventoryTranHistEnqFilter.subItemCDWildcard> > > >();
cmd.WhereAnd <Where <INSubItem.subItemCD, Like <Current <InventoryTranHistEnqFilter.subItemCDWildcard> > > >();
}
if (filter.SiteID != null && PXAccess.FeatureInstalled <FeaturesSet.warehouse>())
{
cmdBegBalance.WhereAnd <Where <INItemSiteHistByPeriod.siteID, Equal <Current <InventoryTranHistEnqFilter.siteID> > > >();
cmd.WhereAnd <Where <INTranSplit.siteID, Equal <Current <InventoryTranHistEnqFilter.siteID> > > >();
}
if ((filter.LocationID ?? -1) != -1 && PXAccess.FeatureInstalled <FeaturesSet.warehouseLocation>()) // there are cases when filter.LocationID = -1
{
cmdBegBalance.WhereAnd <Where <INItemSiteHistByPeriod.locationID, Equal <Current <InventoryTranHistEnqFilter.locationID> > > >();
cmd.WhereAnd <Where <INTranSplit.locationID, Equal <Current <InventoryTranHistEnqFilter.locationID> > > >();
}
if ((filter.LotSerialNbr ?? "") != "" && PXAccess.FeatureInstalled <FeaturesSet.lotSerialTracking>())
{
cmd.WhereAnd <Where <INTranSplit.lotSerialNbr, Like <Current <InventoryTranHistEnqFilter.lotSerialNbrWildcard> > > >();
}
if (!includeUnreleased)
{
cmd.WhereAnd <Where <INTranSplit.released, Equal <True> > >();
}
decimal cumulativeQty = 0m;
string TranPeriodID;
DateTime?PeriodStartDate;
bool AnyPeriod = false;
INSite site = PXSelect <INSite, Where <INSite.siteID, Equal <Required <INSite.siteID> > > > .Select(this, filter.SiteID);
int calendarOrganizationID = PXAccess.GetParentOrganizationID(site?.BranchID) ?? FinPeriod.organizationID.MasterValue;
try
{
TranPeriodID = FinPeriodRepository.GetPeriodIDFromDate(filter.StartDate, calendarOrganizationID);
PeriodStartDate = FinPeriodRepository.PeriodStartDate(TranPeriodID, calendarOrganizationID);
}
catch (PXFinPeriodException)
{
TranPeriodID = null;
PeriodStartDate = filter.StartDate;
}
int startRow = 0;
int totalRows = 0;
int maximumRows = 0;
if (includeUnreleased)
{
PXSelectBase <OrganizationFinPeriod> periodCmd =
new PXSelectGroupBy <OrganizationFinPeriod,
Where <OrganizationFinPeriod.finPeriodID, LessEqual <Required <OrganizationFinPeriod.finPeriodID> >,
And <OrganizationFinPeriod.iNClosed, Equal <False>,
Or <OrganizationFinPeriod.finPeriodID, Equal <Required <OrganizationFinPeriod.finPeriodID> > > > >,
Aggregate <GroupBy <OrganizationFinPeriod.finPeriodID> >,
OrderBy <Asc <OrganizationFinPeriod.finPeriodID> > >(this);
List <object> periodCmdParams = new List <object>()
{
TranPeriodID, TranPeriodID
};
if (calendarOrganizationID != FinPeriod.organizationID.MasterValue)
{
periodCmd.WhereAnd <Where <OrganizationFinPeriod.organizationID, Equal <Required <OrganizationFinPeriod.organizationID> > > >();
periodCmdParams.Add(calendarOrganizationID);
}
OrganizationFinPeriod firstOpenOrCurrentClosedPeriod = periodCmd.SelectWindowed(0, 1, periodCmdParams.ToArray());
if (firstOpenOrCurrentClosedPeriod != null)
{
TranPeriodID = firstOpenOrCurrentClosedPeriod.FinPeriodID;
PeriodStartDate = FinPeriodRepository.PeriodStartDate(firstOpenOrCurrentClosedPeriod.FinPeriodID, calendarOrganizationID);
}
foreach (PXResult <INItemSiteHistByPeriod, INItemSiteHist> res in cmdBegBalance.Select(TranPeriodID))
{
INItemSiteHistByPeriod byperiod = res;
INItemSiteHist hist = res;
cumulativeQty += string.Equals(byperiod.FinPeriodID, byperiod.LastActivityPeriod) ? (decimal)hist.TranBegQty : (decimal)hist.TranYtdQty;
AnyPeriod = true;
}
if (AnyPeriod)
{
//if StartDate is not on the Period border, make additional select with grouping
if (PeriodStartDate != filter.StartDate)
{
PXView v2 = new PXView(this, true, cmd.View.BqlSelect
.WhereAnd <Where <INTranSplit.tranDate, GreaterEqual <Required <INTranSplit.tranDate> > > >()
.WhereAnd <Where <INTranSplit.tranDate, Less <Required <INTranSplit.tranDate> > > >()
.AggregateNew <Aggregate <GroupBy <INTranSplit.inventoryID, GroupBy <INTranSplit.invtMult, Sum <INTranSplit.baseQty> > > > >());
foreach (PXResult <INTranSplit> res in v2.Select(new object[0], new object[] { PeriodStartDate, filter.StartDate }, new object[0], new string[0], new bool[0], new PXFilterRow[0], ref startRow, 0, ref totalRows))
{
INTranSplit ts_rec = res;
cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m;
}
}
}
else
{
PXView v2 = new PXView(this, true, cmd.View.BqlSelect
.WhereAnd <Where <INTranSplit.tranDate, Less <Required <INTranSplit.tranDate> > > >()
.AggregateNew <Aggregate <GroupBy <INTranSplit.inventoryID, GroupBy <INTranSplit.invtMult, Sum <INTranSplit.baseQty> > > > >());
foreach (PXResult <INTranSplit> res in v2.Select(new object[0], new object[] { filter.StartDate }, new object[0], new string[0], new bool[0], new PXFilterRow[0], ref startRow, 0, ref totalRows))
{
INTranSplit ts_rec = res;
cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m;
}
}
}
else
{
foreach (PXResult <INItemSiteHistByPeriod, INItemSiteHist> res in cmdBegBalance.Select(TranPeriodID))
{
INItemSiteHistByPeriod byperiod = res;
INItemSiteHist hist = res;
cumulativeQty += string.Equals(byperiod.FinPeriodID, byperiod.LastActivityPeriod) ? (decimal)hist.TranBegQty : (decimal)hist.TranYtdQty;
AnyPeriod = true;
}
if (AnyPeriod)
{
//if StartDate is not on the Period border, make additional select with grouping
if (PeriodStartDate != filter.StartDate)
{
PXView v2 = new PXView(this, true, cmd.View.BqlSelect
.WhereAnd <Where <INTranSplit.tranDate, GreaterEqual <Required <INTranSplit.tranDate> > > >()
.WhereAnd <Where <INTranSplit.tranDate, Less <Required <INTranSplit.tranDate> > > >()
.AggregateNew <Aggregate <GroupBy <INTranSplit.inventoryID, GroupBy <INTranSplit.invtMult, Sum <INTranSplit.baseQty> > > > >());
foreach (PXResult <INTranSplit> res in v2.Select(new object[0], new object[] { PeriodStartDate, filter.StartDate }, new object[0], new string[0], new bool[0], new PXFilterRow[0], ref startRow, 0, ref totalRows))
{
INTranSplit ts_rec = res;
cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m;
}
}
}
}
if (filter.StartDate != null)
{
cmd.WhereAnd <Where <INTranSplit.tranDate, GreaterEqual <Current <InventoryTranHistEnqFilter.startDate> > > >();
}
if (filter.EndDate != null)
{
cmd.WhereAnd <Where <INTranSplit.tranDate, LessEqual <Current <InventoryTranHistEnqFilter.endDate> > > >();
}
string[] newSortColumns;
bool[] newDescendings;
AlterSorts(out newSortColumns, out newDescendings);
PXFilterRow[] newFilters = AlterFilters();
//if user clicks last, sorts will be inverted
//as it is not possible to calculate beginning balance from the end
//we will select without top from the start and then apply reverse order and select top n records
//for next page we will ommit startrow to set beginning balance correctly
//top (n, m) will be set in the outer search results since we do not reset PXView.StartRow to 0
startRow = 0;
maximumRows = !PXView.ReverseOrder ? PXView.StartRow + PXView.MaximumRows : 0;
totalRows = 0;
PXView selectView = !summaryByDay ? cmd.View
: new PXView(this, true, cmd.View.BqlSelect.AggregateNew <Aggregate <GroupBy <INTranSplit.tranDate, Sum <INTranSplit.qtyIn, Sum <INTranSplit.qtyOut> > > > >());
List <object> intermediateResult = selectView.Select(PXView.Currents, new object[] { filter.StartDate }, new string[newSortColumns.Length], newSortColumns, PXView.Descendings, newFilters, ref startRow, maximumRows, ref totalRows);
int gridLineNbr = 0;
foreach (PXResult <INTranSplit, INTran, INSubItem> it in intermediateResult)
{
INTranSplit ts_rec = (INTranSplit)it;
INTran t_rec = (INTran)it;
if (ts_rec.TranDate < filter.StartDate)
{
cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m;
}
else
{
if (summaryByDay)
{
InventoryTranHistEnqResult item = new InventoryTranHistEnqResult();
item.BegQty = cumulativeQty;
item.TranDate = ts_rec.TranDate;
item.QtyIn = ts_rec.QtyIn;
item.QtyOut = ts_rec.QtyOut;
item.EndQty = item.BegQty + ts_rec.QtyIn - ts_rec.QtyOut;
item.GridLineNbr = ++gridLineNbr;
resultList.Add(new PXResult <InventoryTranHistEnqResult, INTran>(item, null));
cumulativeQty += (ts_rec.QtyIn - ts_rec.QtyOut) ?? 0m;
}
else
{
InventoryTranHistEnqResult item = new InventoryTranHistEnqResult();
item.BegQty = cumulativeQty;
item.TranDate = ts_rec.TranDate;
item.QtyIn = ts_rec.QtyIn;
item.QtyOut = ts_rec.QtyOut;
item.EndQty = item.BegQty + ts_rec.QtyIn - ts_rec.QtyOut;
item.InventoryID = ts_rec.InventoryID;
item.TranType = ts_rec.TranType;
item.DocType = ts_rec.DocType;
item.DocRefNbr = ts_rec.RefNbr;
item.SubItemID = ts_rec.SubItemID;
item.SiteID = ts_rec.SiteID;
item.LocationID = ts_rec.LocationID;
item.LotSerialNbr = ts_rec.LotSerialNbr;
item.FinPerNbr = t_rec.FinPeriodID;
item.TranPerNbr = t_rec.TranPeriodID;
item.Released = t_rec.Released;
item.GridLineNbr = ++gridLineNbr;
decimal?unitcost;
if (filter.ShowAdjUnitCost ?? false)
{
unitcost = ts_rec.TotalQty != null && ts_rec.TotalQty != 0m ? (ts_rec.TotalCost + ts_rec.AdditionalCost) / ts_rec.TotalQty : 0m;
}
else
{
unitcost = ts_rec.TotalQty != null && ts_rec.TotalQty != 0m ? ts_rec.TotalCost / ts_rec.TotalQty : 0m;
}
item.UnitCost = unitcost;
resultList.Add(new PXResult <InventoryTranHistEnqResult, INTran>(item, t_rec));
cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m;
}
}
}
if (!PXView.ReverseOrder)
{
return(resultList);
}
return(PXView.Sort(resultList));
}
protected virtual IEnumerable resultRecords()
{
if (Filter.Current == null || string.IsNullOrEmpty(Filter.Current.FinPeriodID))
{
yield break;
}
PXSelectBase <INItemSiteHistByPeriod> cmd = new PXSelectJoin <INItemSiteHistByPeriod,
InnerJoin <INItemSiteHist, On <INItemSiteHist.inventoryID, Equal <INItemSiteHistByPeriod.inventoryID>, And <INItemSiteHist.siteID, Equal <INItemSiteHistByPeriod.siteID>, And <INItemSiteHist.subItemID, Equal <INItemSiteHistByPeriod.subItemID>, And <INItemSiteHist.locationID, Equal <INItemSiteHistByPeriod.locationID>, And <INItemSiteHist.finPeriodID, Equal <INItemSiteHistByPeriod.lastActivityPeriod> > > > > >,
InnerJoin <INSubItem, On <INSubItem.subItemID, Equal <INItemSiteHistByPeriod.subItemID> >,
InnerJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <INItemSiteHistByPeriod.inventoryID> >,
InnerJoin <INSite, On <INSite.siteID, Equal <INItemSiteHistByPeriod.siteID> > > > > >,
Where <INItemSiteHistByPeriod.finPeriodID, Equal <Current <InventoryTranSumEnqFilter.finPeriodID> >,
And2 <Match <InventoryItem, Current <AccessInfo.userName> >, And <Match <INSite, Current <AccessInfo.userName> > > > > >(this);
bool SiteDetails = Filter.Current.SiteDetails == true || Filter.Current.LocationDetails == true;
bool LocationDetails = Filter.Current.LocationDetails == true;
bool SubItemDetails = Filter.Current.SubItemDetails == true;
if (Filter.Current.InventoryID != null)
{
cmd.WhereAnd <Where <INItemSiteHistByPeriod.inventoryID, Equal <Current <InventoryTranSumEnqFilter.inventoryID> > > >();
}
if (!SubCDUtils.IsSubCDEmpty(Filter.Current.SubItemCD))
{
cmd.WhereAnd <Where <INSubItem.subItemCD, Like <Current <InventoryTranSumEnqFilter.subItemCDWildcard> > > >();
}
if (Filter.Current.SiteID != null)
{
cmd.WhereAnd <Where <INItemSiteHistByPeriod.siteID, Equal <Current <InventoryTranSumEnqFilter.siteID> > > >();
}
if ((Filter.Current.LocationID ?? -1) != -1)
{
cmd.WhereAnd <Where <INItemSiteHistByPeriod.locationID, Equal <Current <InventoryTranSumEnqFilter.locationID> > > >();
}
var sortColumns = new List <string>();
sortColumns.Add(nameof(INItemSiteHistByPeriod.inventoryID));
if (SubItemDetails)
{
sortColumns.Add(nameof(INItemSiteHistByPeriod.subItemID));
}
if (SiteDetails)
{
sortColumns.Add(nameof(INItemSiteHistByPeriod.siteID));
}
if (LocationDetails)
{
sortColumns.Add(nameof(INItemSiteHistByPeriod.locationID));
}
int startRow = 0;
int totalRows = 0;
var list = cmd.View.Select(null, null, new object[sortColumns.Count], sortColumns.ToArray(), new bool[sortColumns.Count], PXView.Filters, ref startRow, 0, ref totalRows)
.Cast <PXResult <INItemSiteHistByPeriod, INItemSiteHist> >()
.ToList();
INItemSiteHist prev_hist = new INItemSiteHist();
foreach (PXResult <INItemSiteHistByPeriod, INItemSiteHist> res in list)
{
INItemSiteHistByPeriod byperiod = res;
INItemSiteHist hist = res;
if (string.Equals(byperiod.FinPeriodID, byperiod.LastActivityPeriod) == false)
{
hist.TranBegQty = hist.TranYtdQty;
hist.TranPtdQtyAdjusted = 0m;
hist.TranPtdQtyAssemblyIn = 0m;
hist.TranPtdQtyAssemblyOut = 0m;
hist.TranPtdQtyCreditMemos = 0m;
hist.TranPtdQtyDropShipSales = 0m;
hist.TranPtdQtyIssued = 0m;
hist.TranPtdQtyReceived = 0m;
hist.TranPtdQtySales = 0m;
hist.TranPtdQtyTransferIn = 0m;
hist.TranPtdQtyTransferOut = 0m;
hist.FinBegQty = hist.FinYtdQty;
hist.FinPtdQtyAdjusted = 0m;
hist.FinPtdQtyAssemblyIn = 0m;
hist.FinPtdQtyAssemblyOut = 0m;
hist.FinPtdQtyCreditMemos = 0m;
hist.FinPtdQtyDropShipSales = 0m;
hist.FinPtdQtyIssued = 0m;
hist.FinPtdQtyReceived = 0m;
hist.FinPtdQtySales = 0m;
hist.FinPtdQtyTransferIn = 0m;
hist.FinPtdQtyTransferOut = 0m;
}
if (object.Equals(prev_hist.InventoryID, hist.InventoryID) == false ||
SubItemDetails && object.Equals(prev_hist.SubItemID, hist.SubItemID) == false ||
SiteDetails && object.Equals(prev_hist.SiteID, hist.SiteID) == false ||
LocationDetails && object.Equals(prev_hist.LocationID, hist.LocationID) == false)
{
if (prev_hist.InventoryID != null)
{
if (Filter.Current.ShowItemsWithoutMovement == true || string.Equals(prev_hist.FinPeriodID, prev_hist.LastActivityPeriod))
{
yield return(prev_hist);
}
}
prev_hist = PXCache <INItemSiteHist> .CreateCopy(hist);
prev_hist.FinPeriodID = byperiod.FinPeriodID;
prev_hist.LastActivityPeriod = byperiod.LastActivityPeriod;
}
else
{
if (string.Compare(prev_hist.LastActivityPeriod, byperiod.LastActivityPeriod) < 0)
{
prev_hist.LastActivityPeriod = byperiod.LastActivityPeriod;
}
prev_hist.TranBegQty += hist.TranBegQty;
prev_hist.TranPtdQtyAdjusted += hist.TranPtdQtyAdjusted;
prev_hist.TranPtdQtyAssemblyIn += hist.TranPtdQtyAssemblyIn;
prev_hist.TranPtdQtyAssemblyOut += hist.TranPtdQtyAssemblyOut;
prev_hist.TranPtdQtyCreditMemos += hist.TranPtdQtyCreditMemos;
prev_hist.TranPtdQtyDropShipSales += hist.TranPtdQtyDropShipSales;
prev_hist.TranPtdQtyIssued += hist.TranPtdQtyIssued;
prev_hist.TranPtdQtyReceived += hist.TranPtdQtyReceived;
prev_hist.TranPtdQtySales += hist.TranPtdQtySales;
prev_hist.TranPtdQtyTransferIn += hist.TranPtdQtyTransferIn;
prev_hist.TranPtdQtyTransferOut += hist.TranPtdQtyTransferOut;
prev_hist.TranYtdQty += hist.TranYtdQty;
prev_hist.FinBegQty += hist.FinBegQty;
prev_hist.FinPtdQtyAdjusted += hist.FinPtdQtyAdjusted;
prev_hist.FinPtdQtyAssemblyIn += hist.FinPtdQtyAssemblyIn;
prev_hist.FinPtdQtyAssemblyOut += hist.FinPtdQtyAssemblyOut;
prev_hist.FinPtdQtyCreditMemos += hist.FinPtdQtyCreditMemos;
prev_hist.FinPtdQtyDropShipSales += hist.FinPtdQtyDropShipSales;
prev_hist.FinPtdQtyIssued += hist.FinPtdQtyIssued;
prev_hist.FinPtdQtyReceived += hist.FinPtdQtyReceived;
prev_hist.FinPtdQtySales += hist.FinPtdQtySales;
prev_hist.FinPtdQtyTransferIn += hist.FinPtdQtyTransferIn;
prev_hist.FinPtdQtyTransferOut += hist.FinPtdQtyTransferOut;
prev_hist.FinYtdQty += hist.FinYtdQty;
}
}
if (prev_hist.InventoryID != null)
{
if (Filter.Current.ShowItemsWithoutMovement == true || string.Equals(prev_hist.FinPeriodID, prev_hist.LastActivityPeriod))
{
yield return(prev_hist);
}
}
}
protected virtual IEnumerable resultRecords()
{
InventoryTranHistEnqFilter filter = Filter.Current;
bool summaryByDay = filter.SummaryByDay ?? false;
bool includeUnreleased = filter.IncludeUnreleased ?? false;
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.inventoryID>(ResultRecords.Cache, null, false);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.finPerNbr>(ResultRecords.Cache, null, false); //???
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.tranPerNbr>(ResultRecords.Cache, null, false); //???
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.tranType>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.docRefNbr>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.subItemID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.siteID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.locationID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.lotSerialNbr>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible(Tran.Cache, null, !summaryByDay);
var resultList = new List <PXResult <InventoryTranHistEnqResult, INTran> >();
if (filter.InventoryID == null)
{
return(resultList); //empty
}
// it's better for perfomance to calc. BegQty in the separate GROUP BY query before main.. but currently as is
PXSelectBase <INTranSplit> cmd = new PXSelectReadonly3 <INTranSplit,
InnerJoin <INTran,
On <INTran.tranType, Equal <INTranSplit.tranType>,
And <INTran.refNbr, Equal <INTranSplit.refNbr>,
And <INTran.lineNbr, Equal <INTranSplit.lineNbr> > > >,
InnerJoin <INSubItem,
On <INSubItem.subItemID, Equal <INTranSplit.subItemID> >,
InnerJoin <INSite, On <INSite.siteID, Equal <INTran.siteID> > > > >,
// OrderBy<Asc<INTranSplit.inventoryID, Asc<INTranSplit.tranDate>>>>(this); // additional order by LastModifiedDateTime ???
OrderBy <Asc <INTranSplit.tranDate, Asc <INTranSplit.lastModifiedDateTime> > > >(this);
cmd.WhereAnd <Where <INTranSplit.inventoryID, Equal <Current <InventoryTranHistEnqFilter.inventoryID> >, And <Match <INSite, Current <AccessInfo.userName> > > > >();
if (!SubCDUtils.IsSubCDEmpty(filter.SubItemCD))
{
cmd.WhereAnd <Where <INSubItem.subItemCD, Like <Current <InventoryTranHistEnqFilter.subItemCDWildcard> > > >();
}
if (filter.SiteID != null)
{
cmd.WhereAnd <Where <INTranSplit.siteID, Equal <Current <InventoryTranHistEnqFilter.siteID> > > >();
}
if ((filter.LocationID ?? -1) != -1) // there are cases when filter.LocationID = -1
{
cmd.WhereAnd <Where <INTranSplit.locationID, Equal <Current <InventoryTranHistEnqFilter.locationID> > > >();
}
if ((filter.LotSerialNbr ?? "") != "")
{
cmd.WhereAnd <Where <INTranSplit.lotSerialNbr, Like <Current <InventoryTranHistEnqFilter.lotSerialNbrWildcard> > > >();
}
if (!includeUnreleased)
{
cmd.WhereAnd <Where <INTranSplit.released, Equal <boolTrue> > >();
}
// commented because we need all the trans to calc BegQty
// if (filter.StartDate != null)
// {
// cmd.WhereAnd<Where<INTranSplit.tranDate, GreaterEqual<Current<InventoryTranHistEnqFilter.startDate>>>>();
// }
if (filter.EndDate != null)
{
cmd.WhereAnd <Where <INTranSplit.tranDate, LessEqual <Current <InventoryTranHistEnqFilter.endDate> > > >();
}
PXResultset <INTranSplit> intermediateResult = (PXResultset <INTranSplit>)cmd.Select();
decimal cumulativeQty = 0m;
int gridLineNbr = 0;
foreach (PXResult <INTranSplit, INTran, INSubItem> it in intermediateResult)
{
INTranSplit ts_rec = (INTranSplit)it;
INTran t_rec = (INTran)it;
if (ts_rec.TranDate < filter.StartDate)
{
cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m;
}
else
{
if (summaryByDay)
{
if ((resultList.Count > 0) && (((InventoryTranHistEnqResult)resultList[resultList.Count - 1]).TranDate == ts_rec.TranDate))
{
InventoryTranHistEnqResult lastItem = resultList[resultList.Count - 1];
if (ts_rec.InvtMult * ts_rec.BaseQty >= 0)
{
lastItem.QtyIn += ts_rec.InvtMult * ts_rec.BaseQty;
}
else
{
lastItem.QtyOut -= ts_rec.InvtMult * ts_rec.BaseQty;
}
lastItem.EndQty += ts_rec.InvtMult * ts_rec.BaseQty;
}
else
{
InventoryTranHistEnqResult item = new InventoryTranHistEnqResult();
item.BegQty = cumulativeQty;
item.TranDate = ts_rec.TranDate;
if (ts_rec.InvtMult * ts_rec.BaseQty >= 0)
{
item.QtyIn = ts_rec.InvtMult * ts_rec.BaseQty;
item.QtyOut = 0m;
}
else
{
item.QtyIn = 0m;
item.QtyOut = -ts_rec.InvtMult * ts_rec.BaseQty;
}
item.EndQty = item.BegQty + (ts_rec.InvtMult * ts_rec.BaseQty);
item.GridLineNbr = ++gridLineNbr;
resultList.Add(new PXResult <InventoryTranHistEnqResult, INTran>(item, null));
}
cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m;
}
else
{
InventoryTranHistEnqResult item = new InventoryTranHistEnqResult();
item.BegQty = cumulativeQty;
item.TranDate = ts_rec.TranDate;
if (ts_rec.InvtMult * ts_rec.BaseQty >= 0)
{
item.QtyIn = ts_rec.InvtMult * ts_rec.BaseQty;
item.QtyOut = 0m;
}
else
{
item.QtyIn = 0m;
item.QtyOut = -ts_rec.InvtMult * ts_rec.BaseQty;
}
item.EndQty = item.BegQty + (ts_rec.InvtMult * ts_rec.BaseQty);
item.InventoryID = ts_rec.InventoryID;
item.TranType = ts_rec.TranType;
item.DocType = ts_rec.DocType;
item.DocRefNbr = ts_rec.RefNbr;
item.SubItemID = ts_rec.SubItemID;
item.SiteID = ts_rec.SiteID;
item.LocationID = ts_rec.LocationID;
item.LotSerialNbr = ts_rec.LotSerialNbr;
item.FinPerNbr = t_rec.FinPeriodID;
item.TranPerNbr = t_rec.TranPeriodID;
item.Released = t_rec.Released;
item.GridLineNbr = ++gridLineNbr;
resultList.Add(new PXResult <InventoryTranHistEnqResult, INTran>(item, t_rec));
cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m;
}
}
}
return(resultList);
}
protected virtual IEnumerable resultRecords()
{
if (Filter.Current == null || string.IsNullOrEmpty(Filter.Current.FinPeriodID))
{
yield break;
}
PXSelectBase <INItemSiteHistByPeriod> cmd = new PXSelectJoin <INItemSiteHistByPeriod,
InnerJoin <INItemSiteHist, On <INItemSiteHist.inventoryID, Equal <INItemSiteHistByPeriod.inventoryID>, And <INItemSiteHist.siteID, Equal <INItemSiteHistByPeriod.siteID>, And <INItemSiteHist.subItemID, Equal <INItemSiteHistByPeriod.subItemID>, And <INItemSiteHist.locationID, Equal <INItemSiteHistByPeriod.locationID>, And <INItemSiteHist.finPeriodID, Equal <INItemSiteHistByPeriod.lastActivityPeriod> > > > > >,
InnerJoin <INSubItem, On <INSubItem.subItemID, Equal <INItemSiteHistByPeriod.subItemID> >,
InnerJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <INItemSiteHistByPeriod.inventoryID> >,
InnerJoin <INSite, On <INSite.siteID, Equal <INItemSiteHistByPeriod.siteID> > > > > >,
Where <INItemSiteHistByPeriod.finPeriodID, Equal <Current <InventoryTranSumEnqFilter.finPeriodID> >,
And2 <Match <InventoryItem, Current <AccessInfo.userName> >, And <Match <INSite, Current <AccessInfo.userName> > > > > >(this);
bool SiteDetails = Filter.Current.SiteDetails == true || Filter.Current.LocationDetails == true;
bool LocationDetails = Filter.Current.LocationDetails == true;
bool SubItemDetails = Filter.Current.SubItemDetails == true;
if (Filter.Current.InventoryID != null)
{
cmd.WhereAnd <Where <INItemSiteHistByPeriod.inventoryID, Equal <Current <InventoryTranSumEnqFilter.inventoryID> > > >();
}
if (!SubCDUtils.IsSubCDEmpty(Filter.Current.SubItemCD))
{
cmd.WhereAnd <Where <INSubItem.subItemCD, Like <Current <InventoryTranSumEnqFilter.subItemCDWildcard> > > >();
}
if (Filter.Current.SiteID != null)
{
cmd.WhereAnd <Where <INItemSiteHistByPeriod.siteID, Equal <Current <InventoryTranSumEnqFilter.siteID> > > >();
}
if ((Filter.Current.LocationID ?? -1) != -1)
{
cmd.WhereAnd <Where <INItemSiteHistByPeriod.locationID, Equal <Current <InventoryTranSumEnqFilter.locationID> > > >();
}
var orderByTypes = new List <Type>();
orderByTypes.Add(typeof(INItemSiteHistByPeriod.inventoryID));
if (SubItemDetails)
{
orderByTypes.Add(typeof(INItemSiteHistByPeriod.subItemID));
}
if (SiteDetails)
{
orderByTypes.Add(typeof(INItemSiteHistByPeriod.siteID));
}
if (LocationDetails)
{
orderByTypes.Add(typeof(INItemSiteHistByPeriod.locationID));
}
int last = orderByTypes.Count - 1;
Type newOrder = typeof(Asc <>).MakeGenericType(orderByTypes[last]);
for (int i = orderByTypes.Count - 2; i >= 0; i--)
{
newOrder = typeof(Asc <,>).MakeGenericType(orderByTypes[i], newOrder);
}
newOrder = typeof(OrderBy <>).MakeGenericType(newOrder);
cmd.View.OrderByNew(newOrder);
PXResultset <INItemSiteHistByPeriod> list = cmd.Select();
INItemSiteHist prev_hist = new INItemSiteHist();
foreach (PXResult <INItemSiteHistByPeriod, INItemSiteHist> res in list)
{
INItemSiteHistByPeriod byperiod = res;
INItemSiteHist hist = res;
if (string.Equals(byperiod.FinPeriodID, byperiod.LastActivityPeriod) == false)
{
hist.TranBegQty = hist.TranYtdQty;
hist.TranPtdQtyAdjusted = 0m;
hist.TranPtdQtyAssemblyIn = 0m;
hist.TranPtdQtyAssemblyOut = 0m;
hist.TranPtdQtyCreditMemos = 0m;
hist.TranPtdQtyDropShipSales = 0m;
hist.TranPtdQtyIssued = 0m;
hist.TranPtdQtyReceived = 0m;
hist.TranPtdQtySales = 0m;
hist.TranPtdQtyTransferIn = 0m;
hist.TranPtdQtyTransferOut = 0m;
hist.FinBegQty = hist.FinYtdQty;
hist.FinPtdQtyAdjusted = 0m;
hist.FinPtdQtyAssemblyIn = 0m;
hist.FinPtdQtyAssemblyOut = 0m;
hist.FinPtdQtyCreditMemos = 0m;
hist.FinPtdQtyDropShipSales = 0m;
hist.FinPtdQtyIssued = 0m;
hist.FinPtdQtyReceived = 0m;
hist.FinPtdQtySales = 0m;
hist.FinPtdQtyTransferIn = 0m;
hist.FinPtdQtyTransferOut = 0m;
}
if (object.Equals(prev_hist.InventoryID, hist.InventoryID) == false ||
SubItemDetails && object.Equals(prev_hist.SubItemID, hist.SubItemID) == false ||
SiteDetails && object.Equals(prev_hist.SiteID, hist.SiteID) == false ||
LocationDetails && object.Equals(prev_hist.LocationID, hist.LocationID) == false)
{
if (prev_hist.InventoryID != null)
{
if (Filter.Current.ShowItemsWithoutMovement == true || string.Equals(prev_hist.FinPeriodID, prev_hist.LastActivityPeriod))
{
yield return(prev_hist);
}
}
prev_hist = PXCache <INItemSiteHist> .CreateCopy(hist);
prev_hist.FinPeriodID = byperiod.FinPeriodID;
prev_hist.LastActivityPeriod = byperiod.LastActivityPeriod;
}
else
{
if (string.Compare(prev_hist.LastActivityPeriod, byperiod.LastActivityPeriod) < 0)
{
prev_hist.LastActivityPeriod = byperiod.LastActivityPeriod;
}
prev_hist.TranBegQty += hist.TranBegQty;
prev_hist.TranPtdQtyAdjusted += hist.TranPtdQtyAdjusted;
prev_hist.TranPtdQtyAssemblyIn += hist.TranPtdQtyAssemblyIn;
prev_hist.TranPtdQtyAssemblyOut += hist.TranPtdQtyAssemblyOut;
prev_hist.TranPtdQtyCreditMemos += hist.TranPtdQtyCreditMemos;
prev_hist.TranPtdQtyDropShipSales += hist.TranPtdQtyDropShipSales;
prev_hist.TranPtdQtyIssued += hist.TranPtdQtyIssued;
prev_hist.TranPtdQtyReceived += hist.TranPtdQtyReceived;
prev_hist.TranPtdQtySales += hist.TranPtdQtySales;
prev_hist.TranPtdQtyTransferIn += hist.TranPtdQtyTransferIn;
prev_hist.TranPtdQtyTransferOut += hist.TranPtdQtyTransferOut;
prev_hist.TranYtdQty += hist.TranYtdQty;
prev_hist.FinBegQty += hist.FinBegQty;
prev_hist.FinPtdQtyAdjusted += hist.FinPtdQtyAdjusted;
prev_hist.FinPtdQtyAssemblyIn += hist.FinPtdQtyAssemblyIn;
prev_hist.FinPtdQtyAssemblyOut += hist.FinPtdQtyAssemblyOut;
prev_hist.FinPtdQtyCreditMemos += hist.FinPtdQtyCreditMemos;
prev_hist.FinPtdQtyDropShipSales += hist.FinPtdQtyDropShipSales;
prev_hist.FinPtdQtyIssued += hist.FinPtdQtyIssued;
prev_hist.FinPtdQtyReceived += hist.FinPtdQtyReceived;
prev_hist.FinPtdQtySales += hist.FinPtdQtySales;
prev_hist.FinPtdQtyTransferIn += hist.FinPtdQtyTransferIn;
prev_hist.FinPtdQtyTransferOut += hist.FinPtdQtyTransferOut;
prev_hist.FinYtdQty += hist.FinYtdQty;
}
}
if (prev_hist.InventoryID != null)
{
if (Filter.Current.ShowItemsWithoutMovement == true || string.Equals(prev_hist.FinPeriodID, prev_hist.LastActivityPeriod))
{
yield return(prev_hist);
}
}
}
protected virtual IEnumerable resultRecords()
{
if (Filter.Current == null || string.IsNullOrEmpty(Filter.Current.FinPeriodID))
{
yield break;
}
PXSelectBase <INItemSiteHistByPeriod> cmd = new PXSelectJoin <INItemSiteHistByPeriod,
InnerJoin <INItemSiteHist, On <INItemSiteHist.inventoryID, Equal <INItemSiteHistByPeriod.inventoryID>, And <INItemSiteHist.siteID, Equal <INItemSiteHistByPeriod.siteID>, And <INItemSiteHist.subItemID, Equal <INItemSiteHistByPeriod.subItemID>, And <INItemSiteHist.locationID, Equal <INItemSiteHistByPeriod.locationID>, And <INItemSiteHist.finPeriodID, Equal <INItemSiteHistByPeriod.lastActivityPeriod> > > > > >,
InnerJoin <INSubItem, On <INSubItem.subItemID, Equal <INItemSiteHistByPeriod.subItemID> >,
InnerJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <INItemSiteHistByPeriod.inventoryID> >,
InnerJoin <INSite, On <INSite.siteID, Equal <INItemSiteHistByPeriod.siteID> > > > > >,
Where <INItemSiteHistByPeriod.finPeriodID, Equal <Current <InventoryTranSumEnqFilter.finPeriodID> >,
And2 <Match <InventoryItem, Current <AccessInfo.userName> >, And <Match <INSite, Current <AccessInfo.userName> > > > > >(this);
bool SiteDetails = Filter.Current.SiteDetails == true || Filter.Current.LocationDetails == true;
bool LocationDetails = Filter.Current.LocationDetails == true;
bool SubItemDetails = Filter.Current.SubItemDetails == true;
if (Filter.Current.InventoryID != null)
{
cmd.WhereAnd <Where <INItemSiteHistByPeriod.inventoryID, Equal <Current <InventoryTranSumEnqFilter.inventoryID> > > >();
}
if (!SubCDUtils.IsSubCDEmpty(Filter.Current.SubItemCD))
{
cmd.WhereAnd <Where <INSubItem.subItemCD, Like <Current <InventoryTranSumEnqFilter.subItemCDWildcard> > > >();
}
if (Filter.Current.SiteID != null)
{
cmd.WhereAnd <Where <INItemSiteHistByPeriod.siteID, Equal <Current <InventoryTranSumEnqFilter.siteID> > > >();
}
if ((Filter.Current.LocationID ?? -1) != -1)
{
cmd.WhereAnd <Where <INItemSiteHistByPeriod.locationID, Equal <Current <InventoryTranSumEnqFilter.locationID> > > >();
}
PXResultset <INItemSiteHistByPeriod> list = cmd.Select();
list.Sort(delegate(PXResult <INItemSiteHistByPeriod> ra, PXResult <INItemSiteHistByPeriod> rb)
{
INItemSiteHistByPeriod a = ra;
INItemSiteHistByPeriod b = rb;
int?aInventoryID = a.InventoryID;
int?bInventoryID = b.InventoryID;
int ret = ((IComparable)aInventoryID).CompareTo(bInventoryID);
if (ret != 0)
{
return(ret);
}
if (SubItemDetails)
{
int?aSubItemID = a.SubItemID;
int?bSubItemID = b.SubItemID;
ret = ((IComparable)aSubItemID).CompareTo(bSubItemID);
if (ret != 0)
{
return(ret);
}
}
if (SiteDetails)
{
int?aSiteID = a.SiteID;
int?bSiteID = b.SiteID;
ret = ((IComparable)aSiteID).CompareTo(bSiteID);
if (ret != 0)
{
return(ret);
}
}
if (LocationDetails)
{
int?aLocationID = a.LocationID;
int?bLocationID = b.LocationID;
ret = ((IComparable)aLocationID).CompareTo(bLocationID);
return(ret);
}
else
{
return(0);
}
});
INItemSiteHist prev_hist = new INItemSiteHist();
foreach (PXResult <INItemSiteHistByPeriod, INItemSiteHist> res in list)
{
INItemSiteHistByPeriod byperiod = res;
INItemSiteHist hist = res;
if (string.Equals(byperiod.FinPeriodID, byperiod.LastActivityPeriod) == false)
{
hist.TranBegQty = hist.TranYtdQty;
hist.TranPtdQtyAdjusted = 0m;
hist.TranPtdQtyAssemblyIn = 0m;
hist.TranPtdQtyAssemblyOut = 0m;
hist.TranPtdQtyCreditMemos = 0m;
hist.TranPtdQtyDropShipSales = 0m;
hist.TranPtdQtyIssued = 0m;
hist.TranPtdQtyReceived = 0m;
hist.TranPtdQtySales = 0m;
hist.TranPtdQtyTransferIn = 0m;
hist.TranPtdQtyTransferOut = 0m;
hist.FinBegQty = hist.FinYtdQty;
hist.FinPtdQtyAdjusted = 0m;
hist.FinPtdQtyAssemblyIn = 0m;
hist.FinPtdQtyAssemblyOut = 0m;
hist.FinPtdQtyCreditMemos = 0m;
hist.FinPtdQtyDropShipSales = 0m;
hist.FinPtdQtyIssued = 0m;
hist.FinPtdQtyReceived = 0m;
hist.FinPtdQtySales = 0m;
hist.FinPtdQtyTransferIn = 0m;
hist.FinPtdQtyTransferOut = 0m;
}
if (object.Equals(prev_hist.InventoryID, hist.InventoryID) == false ||
SubItemDetails && object.Equals(prev_hist.SubItemID, hist.SubItemID) == false ||
SiteDetails && object.Equals(prev_hist.SiteID, hist.SiteID) == false ||
LocationDetails && object.Equals(prev_hist.LocationID, hist.LocationID) == false)
{
if (prev_hist.InventoryID != null)
{
if (Filter.Current.ShowItemsWithoutMovement == true || string.Equals(prev_hist.FinPeriodID, prev_hist.LastActivityPeriod))
{
yield return(prev_hist);
}
}
prev_hist = PXCache <INItemSiteHist> .CreateCopy(hist);
prev_hist.FinPeriodID = byperiod.FinPeriodID;
prev_hist.LastActivityPeriod = byperiod.LastActivityPeriod;
}
else
{
if (string.Compare(prev_hist.LastActivityPeriod, byperiod.LastActivityPeriod) < 0)
{
prev_hist.LastActivityPeriod = byperiod.LastActivityPeriod;
}
prev_hist.TranBegQty += hist.TranBegQty;
prev_hist.TranPtdQtyAdjusted += hist.TranPtdQtyAdjusted;
prev_hist.TranPtdQtyAssemblyIn += hist.TranPtdQtyAssemblyIn;
prev_hist.TranPtdQtyAssemblyOut += hist.TranPtdQtyAssemblyOut;
prev_hist.TranPtdQtyCreditMemos += hist.TranPtdQtyCreditMemos;
prev_hist.TranPtdQtyDropShipSales += hist.TranPtdQtyDropShipSales;
prev_hist.TranPtdQtyIssued += hist.TranPtdQtyIssued;
prev_hist.TranPtdQtyReceived += hist.TranPtdQtyReceived;
prev_hist.TranPtdQtySales += hist.TranPtdQtySales;
prev_hist.TranPtdQtyTransferIn += hist.TranPtdQtyTransferIn;
prev_hist.TranPtdQtyTransferOut += hist.TranPtdQtyTransferOut;
prev_hist.TranYtdQty += hist.TranYtdQty;
prev_hist.FinBegQty += hist.FinBegQty;
prev_hist.FinPtdQtyAdjusted += hist.FinPtdQtyAdjusted;
prev_hist.FinPtdQtyAssemblyIn += hist.FinPtdQtyAssemblyIn;
prev_hist.FinPtdQtyAssemblyOut += hist.FinPtdQtyAssemblyOut;
prev_hist.FinPtdQtyCreditMemos += hist.FinPtdQtyCreditMemos;
prev_hist.FinPtdQtyDropShipSales += hist.FinPtdQtyDropShipSales;
prev_hist.FinPtdQtyIssued += hist.FinPtdQtyIssued;
prev_hist.FinPtdQtyReceived += hist.FinPtdQtyReceived;
prev_hist.FinPtdQtySales += hist.FinPtdQtySales;
prev_hist.FinPtdQtyTransferIn += hist.FinPtdQtyTransferIn;
prev_hist.FinPtdQtyTransferOut += hist.FinPtdQtyTransferOut;
prev_hist.FinYtdQty += hist.FinYtdQty;
}
}
if (prev_hist.InventoryID != null)
{
if (Filter.Current.ShowItemsWithoutMovement == true || string.Equals(prev_hist.FinPeriodID, prev_hist.LastActivityPeriod))
{
yield return(prev_hist);
}
}
}