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 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); }
public static PXResultset <ARInvoice> GetCustDocs(PX.Objects.AR.ARPaymentEntry.LoadOptions opts, ARPayment currentARPayment, ARSetup currentARSetup, PXGraph graph) { PXSelectBase <ARInvoice> cmd = new PXSelectReadonly2 <ARInvoice, InnerJoin <Customer, On <Customer.bAccountID, Equal <ARInvoice.customerID> >, LeftJoin <ARAdjust, On <ARAdjust.adjdDocType, Equal <ARInvoice.docType>, And <ARAdjust.adjdRefNbr, Equal <ARInvoice.refNbr>, And <ARAdjust.released, Equal <boolFalse>, And <ARAdjust.voided, Equal <boolFalse>, And <Where <ARAdjust.adjgDocType, NotEqual <Required <ARPayment.docType> >, Or <ARAdjust.adjgRefNbr, NotEqual <Required <ARPayment.refNbr> > > > > > > > >, LeftJoin <ARAdjust2, On <ARAdjust2.adjgDocType, Equal <ARInvoice.docType>, And <ARAdjust2.adjgRefNbr, Equal <ARInvoice.refNbr>, And <ARAdjust2.released, Equal <boolFalse>, And <ARAdjust2.voided, Equal <boolFalse> > > > > > > >, Where <ARInvoice.docType, NotEqual <Required <ARPayment.docType> >, And <ARInvoice.released, Equal <boolTrue>, And <ARInvoice.openDoc, Equal <boolTrue>, And <ARAdjust.adjgRefNbr, IsNull, And <ARAdjust2.adjgRefNbr, IsNull> > > > >, OrderBy <Asc <ARInvoice.dueDate, Asc <ARInvoice.refNbr> > > >(graph); if (opts != null) { if (opts.FromDate != null) { cmd.WhereAnd <Where <ARInvoice.docDate, GreaterEqual <Required <PX.Objects.AR.ARPaymentEntry.LoadOptions.fromDate> > > >(); } if (opts.TillDate != null) { cmd.WhereAnd <Where <ARInvoice.docDate, LessEqual <Required <PX.Objects.AR.ARPaymentEntry.LoadOptions.tillDate> > > >(); } if (!string.IsNullOrEmpty(opts.StartRefNbr)) { cmd.WhereAnd <Where <ARInvoice.refNbr, GreaterEqual <Required <PX.Objects.AR.ARPaymentEntry.LoadOptions.startRefNbr> > > >(); } if (!string.IsNullOrEmpty(opts.EndRefNbr)) { cmd.WhereAnd <Where <ARInvoice.refNbr, LessEqual <Required <PX.Objects.AR.ARPaymentEntry.LoadOptions.endRefNbr> > > >(); } } var loadChildDocs = opts == null ? PX.Objects.AR.ARPaymentEntry.LoadOptions.loadChildDocuments.None : opts.LoadChildDocuments; switch (loadChildDocs) { case PX.Objects.AR.ARPaymentEntry.LoadOptions.loadChildDocuments.IncludeCRM: cmd.WhereAnd <Where <ARInvoice.customerID, Equal <Required <ARRegister.customerID> >, Or <Customer.consolidatingBAccountID, Equal <Required <ARRegister.customerID> > > > >(); break; case PX.Objects.AR.ARPaymentEntry.LoadOptions.loadChildDocuments.ExcludeCRM: cmd.WhereAnd <Where <ARInvoice.customerID, Equal <Required <ARRegister.customerID> >, Or <Customer.consolidatingBAccountID, Equal <Required <ARRegister.customerID> >, And <ARInvoice.docType, NotEqual <ARDocType.creditMemo> > > > >(); break; default: cmd.WhereAnd <Where <ARInvoice.customerID, Equal <Required <ARRegister.customerID> > > >(); break; } switch (currentARPayment.DocType) { case ARDocType.Payment: cmd.WhereAnd <Where <ARInvoice.docType, Equal <ARDocType.invoice>, Or <ARInvoice.docType, Equal <ARDocType.debitMemo>, Or <ARInvoice.docType, Equal <ARDocType.creditMemo>, Or <ARInvoice.docType, Equal <ARDocType.finCharge> > > > > >(); break; case ARDocType.Prepayment: case ARDocType.CreditMemo: cmd.WhereAnd <Where <ARInvoice.docType, Equal <ARDocType.invoice>, Or <ARInvoice.docType, Equal <ARDocType.debitMemo>, Or <ARInvoice.docType, Equal <ARDocType.finCharge> > > > >(); break; default: cmd.WhereAnd <Where <True, Equal <False> > >(); break; } List <object> parametrs = new List <object>(); parametrs.Add(currentARPayment.DocType); parametrs.Add(currentARPayment.RefNbr); parametrs.Add(currentARPayment.DocType); if (opts != null) { if (opts.FromDate != null) { parametrs.Add(opts.FromDate); } if (opts.TillDate != null) { parametrs.Add(opts.TillDate); } if (!string.IsNullOrEmpty(opts.StartRefNbr)) { parametrs.Add(opts.StartRefNbr); } if (!string.IsNullOrEmpty(opts.EndRefNbr)) { parametrs.Add(opts.EndRefNbr); } } switch (loadChildDocs) { case PX.Objects.AR.ARPaymentEntry.LoadOptions.loadChildDocuments.IncludeCRM: case PX.Objects.AR.ARPaymentEntry.LoadOptions.loadChildDocuments.ExcludeCRM: parametrs.Add(currentARPayment.CustomerID); parametrs.Add(currentARPayment.CustomerID); break; default: parametrs.Add(currentARPayment.CustomerID); break; } PXResultset <ARInvoice> custdocs = opts == null || opts.MaxDocs == null?cmd.Select(parametrs.ToArray()) : cmd.SelectWindowed(0, (int)opts.MaxDocs, parametrs.ToArray()); custdocs.Sort(new Comparison <PXResult <ARInvoice> >(delegate(PXResult <ARInvoice> a, PXResult <ARInvoice> b) { int aSortOrder = 0; int bSortOrder = 0; if (currentARPayment.CuryOrigDocAmt > 0m) { aSortOrder += (((ARInvoice)a).DocType == ARDocType.CreditMemo ? 0 : 1000); bSortOrder += (((ARInvoice)b).DocType == ARDocType.CreditMemo ? 0 : 1000); } else { aSortOrder += (((ARInvoice)a).DocType == ARDocType.CreditMemo ? 1000 : 0); bSortOrder += (((ARInvoice)b).DocType == ARDocType.CreditMemo ? 1000 : 0); } if (currentARSetup.FinChargeFirst == true) { aSortOrder += (((ARInvoice)a).DocType == ARDocType.FinCharge ? 0 : 100); bSortOrder += (((ARInvoice)b).DocType == ARDocType.FinCharge ? 0 : 100); } DateTime aDueDate = ((ARInvoice)a).DueDate ?? DateTime.MinValue; DateTime bDueDate = ((ARInvoice)b).DueDate ?? DateTime.MinValue; if (opts == null) { aSortOrder += (1 + aDueDate.CompareTo(bDueDate)) / 2; bSortOrder += (1 - aDueDate.CompareTo(bDueDate)) / 2; } else { object aObj; object bObj; switch (opts.OrderBy) { case PX.Objects.AR.ARPaymentEntry.LoadOptions.orderBy.RefNbr: aObj = ((ARInvoice)a).RefNbr; bObj = ((ARInvoice)b).RefNbr; aSortOrder += (1 + ((IComparable)aObj).CompareTo(bObj)) / 2; bSortOrder += (1 - ((IComparable)aObj).CompareTo(bObj)) / 2; break; case PX.Objects.AR.ARPaymentEntry.LoadOptions.orderBy.DocDateRefNbr: aObj = ((ARInvoice)a).DocDate; bObj = ((ARInvoice)b).DocDate; aSortOrder += (1 + ((IComparable)aObj).CompareTo(bObj)) / 2 * 10; bSortOrder += (1 - ((IComparable)aObj).CompareTo(bObj)) / 2 * 10; aObj = ((ARInvoice)a).RefNbr; bObj = ((ARInvoice)b).RefNbr; aSortOrder += (1 + ((IComparable)aObj).CompareTo(bObj)) / 2; bSortOrder += (1 - ((IComparable)aObj).CompareTo(bObj)) / 2; break; default: aSortOrder += (1 + aDueDate.CompareTo(bDueDate)) / 2 * 10; bSortOrder += (1 - aDueDate.CompareTo(bDueDate)) / 2 * 10; aObj = ((ARInvoice)a).RefNbr; bObj = ((ARInvoice)b).RefNbr; aSortOrder += (1 + ((IComparable)aObj).CompareTo(bObj)) / 2; bSortOrder += (1 - ((IComparable)aObj).CompareTo(bObj)) / 2; break; } } return(aSortOrder.CompareTo(bSortOrder)); })); return(custdocs); }