protected virtual IEnumerable ViewInventoryTranDet(PXAdapter adapter) { if ((this.ResultRecords.Current != null) && (this.Filter.Current != null)) { INItemSiteHist res = this.ResultRecords.Current; InventoryTranSumEnqFilter currentFilter = this.Filter.Current; InventoryTranDetEnq graph = PXGraph.CreateInstance <InventoryTranDetEnq>(); graph.Filter.Current.ByFinancialPeriod = currentFilter.ByFinancialPeriod; graph.Filter.Current.FinPeriodID = currentFilter.FinPeriodID; graph.Filter.Current.InventoryID = res.InventoryID; if (res.SubItemID != null) { INSubItem foundSubItem = INSubItem.PK.Find(graph, res.SubItemID); if (foundSubItem != null) { graph.Filter.Current.SubItemCD = foundSubItem.SubItemCD; } } graph.Filter.Current.SiteID = res.SiteID; // possibly null graph.Filter.Current.LocationID = res.LocationID; throw new PXRedirectRequiredException(graph, "Inventory Transaction Details"); } return(adapter.Get()); }
protected virtual void QtyFieldSelecting <TranQtyField, FinQtyField>(PXCache sender, PXFieldSelectingEventArgs e) where TranQtyField : IBqlField where FinQtyField : IBqlField { INItemSiteHist hist = (INItemSiteHist)e.Row; if (hist == null) { return; } e.ReturnValue = Filter.Current.ByFinancialPeriod == true?sender.GetValue <FinQtyField>(hist) : sender.GetValue <TranQtyField>(hist); }
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() { 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); } } }