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 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); }
public virtual IEnumerable cATranListRecords() { CAEnqFilter filter = Filter.Current; List <PXResult <CATranExt, BAccountR> > result = new List <PXResult <CATranExt, BAccountR> >(); if (filter != null && filter.ShowSummary == true) { long?id = 0; int startRow = 0; int totalRows = 0; foreach (CADailySummary daily in CATranListSummarized.View.Select(null, null, new object[PXView.SortColumns.Length], PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRow, 0, ref totalRows)) { CATranExt tran = new CATranExt(); id++; tran.TranID = id; tran.CashAccountID = daily.CashAccountID; tran.TranDate = daily.TranDate; tran.CuryDebitAmt = daily.AmtReleasedClearedDr + daily.AmtReleasedUnclearedDr; tran.CuryCreditAmt = daily.AmtReleasedClearedCr + daily.AmtReleasedUnclearedCr; if (Filter.Current.IncludeUnreleased == true) { tran.CuryDebitAmt += daily.AmtUnreleasedClearedDr + daily.AmtUnreleasedUnclearedDr; tran.CuryCreditAmt += daily.AmtUnreleasedClearedCr + daily.AmtUnreleasedUnclearedCr; } tran.DayDesc = TM.EPCalendarFilter.CalendarTypeAttribute.GetDayName(((DateTime)tran.TranDate).DayOfWeek); result.Add(new PXResult <CATranExt, BAccountR>(tran, new BAccountR())); } } else { Dictionary <long, CAMessage> listMessages = PXLongOperation.GetCustomInfo(this.UID) as Dictionary <long, CAMessage>; PXSelectBase <CATranExt> cmd = new PXSelectJoin <CATranExt, LeftJoin <ARPayment, On <ARPayment.docType, Equal <CATranExt.origTranType>, And <ARPayment.refNbr, Equal <CATran.origRefNbr> > >, LeftJoin <BAccountR, On <BAccountR.bAccountID, Equal <CATranExt.referenceID> > > >, Where2 <Where <Current <CAEnqFilter.includeUnreleased>, Equal <boolTrue>, Or <CATran.released, Equal <boolTrue> > >, And <CATranExt.cashAccountID, Equal <Current <CAEnqFilter.accountID> >, And <CATranExt.tranDate, Between <Current <CAEnqFilter.startDate>, Current <CAEnqFilter.endDate> > > > >, OrderBy <Asc <CATranExt.tranDate, Asc <CATranExt.extRefNbr, Asc <CATranExt.tranID> > > > >(this); int startRow = 0; int totalRows = 0; foreach (PXResult <CATranExt, ARPayment, BAccountR> iRes in cmd.View.Select(null, null, new object[PXView.SortColumns.Length], PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRow, 0, ref totalRows)) { CATranExt tran = iRes; ARPayment payment = iRes; BAccountR baccount = iRes; tran.DayDesc = TM.EPCalendarFilter.CalendarTypeAttribute.GetDayName(((DateTime)tran.TranDate).DayOfWeek); tran.DepositNbr = payment.DepositNbr; tran.DepositType = payment.DepositType; if (listMessages != null) { CAMessage message; if (listMessages.TryGetValue(tran.TranID.Value, out message)) { if (message != null && message.Key == tran.TranID) { CATranListRecords.Cache.RaiseExceptionHandling <CATran.origRefNbr>(tran, tran.OrigRefNbr, new PXSetPropertyException(message.Message, message.ErrorLevel)); } } } result.Add(new PXResult <CATranExt, BAccountR>(tran, baccount)); } } decimal curBalance = 0; if (filter != null && filter.BegBal != null) { curBalance = (decimal)filter.BegBal; } PXView.ReverseOrder = false; foreach (PXResult <CATranExt, BAccountR> it in PXView.Sort(result)) { CATran tran = it; tran.BegBal = curBalance; tran.EndBal = tran.BegBal + tran.CuryDebitAmt - tran.CuryCreditAmt; curBalance = (decimal)tran.EndBal; CATranListRecords.Cache.Hold(tran); } return(result); }