protected virtual IEnumerable supplierProducts() { PXSelectBase <SupplierProduct> query = new PXSelectReadonly3 <SupplierProduct, InnerJoin <Supplier, On <Supplier.supplierID, Equal <SupplierProduct.supplierID> > >, OrderBy <Asc <SupplierProduct.productID, Asc <SupplierProduct.supplierID> > > >(this); SupplierFilter filter = Filter.Current; if (filter.CountryCD != null) { query.WhereAnd <Where <Supplier.countryCD, Equal <Current <SupplierFilter.countryCD> > > >(); } if (filter.MinOrderQty != null) { query.WhereAnd <Where <SupplierProduct.minOrderQty, GreaterEqual <Current <SupplierFilter.minOrderQty> > > >(); } if (filter.GroupBySupplier != true) { return(query.Select()); } PXResultset <SupplierProduct, Supplier> result = new PXResultset <SupplierProduct, Supplier>(); SupplierProduct pendingProduct = null; Supplier pendingSupplier = null; int supplierCount = 0; List <string> countries = new List <string>(); foreach (PXResult <SupplierProduct, Supplier> record in query.Select()) { SupplierProduct supplierProduct = (SupplierProduct)record; Supplier supplier = (Supplier)record; if (pendingProduct != null && supplierProduct.ProductID != pendingProduct.ProductID) { CalcAggregates(ref pendingProduct, ref pendingSupplier, ref supplierCount, countries); result.Add(new PXResult <SupplierProduct, Supplier>(pendingProduct, pendingSupplier)); ClearTotals(ref pendingProduct, ref pendingSupplier, ref supplierCount, countries); } CalcTotals(supplierProduct, supplier, ref pendingProduct, ref pendingSupplier, ref supplierCount, countries); } if (pendingProduct != null && pendingSupplier != null) { CalcAggregates(ref pendingProduct, ref pendingSupplier, ref supplierCount, countries); result.Add(new PXResult <SupplierProduct, Supplier>(pendingProduct, pendingSupplier)); } return(result); }
protected virtual IEnumerable resultRecords() { InventoryTranHistEnqFilter filter = Filter.Current; bool summaryByDay = filter.SummaryByDay ?? false; bool includeUnreleased = filter.IncludeUnreleased ?? false; PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.inventoryID>(ResultRecords.Cache, null, false); PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.finPerNbr>(ResultRecords.Cache, null, false); //??? PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.tranPerNbr>(ResultRecords.Cache, null, false); //??? PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.tranType>(ResultRecords.Cache, null, !summaryByDay); PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.docRefNbr>(ResultRecords.Cache, null, !summaryByDay); PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.subItemID>(ResultRecords.Cache, null, !summaryByDay); PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.siteID>(ResultRecords.Cache, null, !summaryByDay); PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.locationID>(ResultRecords.Cache, null, !summaryByDay); PXUIFieldAttribute.SetVisible <InventoryTranHistEnqResult.lotSerialNbr>(ResultRecords.Cache, null, !summaryByDay); PXUIFieldAttribute.SetVisible(Tran.Cache, null, !summaryByDay); var resultList = new List <PXResult <InventoryTranHistEnqResult, INTran> >(); if (filter.InventoryID == null) { return(resultList); //empty } // it's better for perfomance to calc. BegQty in the separate GROUP BY query before main.. but currently as is PXSelectBase <INTranSplit> cmd = new PXSelectReadonly3 <INTranSplit, InnerJoin <INTran, On <INTran.tranType, Equal <INTranSplit.tranType>, And <INTran.refNbr, Equal <INTranSplit.refNbr>, And <INTran.lineNbr, Equal <INTranSplit.lineNbr> > > >, InnerJoin <INSubItem, On <INSubItem.subItemID, Equal <INTranSplit.subItemID> >, InnerJoin <INSite, On <INSite.siteID, Equal <INTran.siteID> > > > >, // OrderBy<Asc<INTranSplit.inventoryID, Asc<INTranSplit.tranDate>>>>(this); // additional order by LastModifiedDateTime ??? OrderBy <Asc <INTranSplit.tranDate, Asc <INTranSplit.lastModifiedDateTime> > > >(this); cmd.WhereAnd <Where <INTranSplit.inventoryID, Equal <Current <InventoryTranHistEnqFilter.inventoryID> >, And <Match <INSite, Current <AccessInfo.userName> > > > >(); if (!SubCDUtils.IsSubCDEmpty(filter.SubItemCD)) { cmd.WhereAnd <Where <INSubItem.subItemCD, Like <Current <InventoryTranHistEnqFilter.subItemCDWildcard> > > >(); } if (filter.SiteID != null) { cmd.WhereAnd <Where <INTranSplit.siteID, Equal <Current <InventoryTranHistEnqFilter.siteID> > > >(); } if ((filter.LocationID ?? -1) != -1) // there are cases when filter.LocationID = -1 { cmd.WhereAnd <Where <INTranSplit.locationID, Equal <Current <InventoryTranHistEnqFilter.locationID> > > >(); } if ((filter.LotSerialNbr ?? "") != "") { cmd.WhereAnd <Where <INTranSplit.lotSerialNbr, Like <Current <InventoryTranHistEnqFilter.lotSerialNbrWildcard> > > >(); } if (!includeUnreleased) { cmd.WhereAnd <Where <INTranSplit.released, Equal <boolTrue> > >(); } // commented because we need all the trans to calc BegQty // if (filter.StartDate != null) // { // cmd.WhereAnd<Where<INTranSplit.tranDate, GreaterEqual<Current<InventoryTranHistEnqFilter.startDate>>>>(); // } if (filter.EndDate != null) { cmd.WhereAnd <Where <INTranSplit.tranDate, LessEqual <Current <InventoryTranHistEnqFilter.endDate> > > >(); } PXResultset <INTranSplit> intermediateResult = (PXResultset <INTranSplit>)cmd.Select(); decimal cumulativeQty = 0m; int gridLineNbr = 0; foreach (PXResult <INTranSplit, INTran, INSubItem> it in intermediateResult) { INTranSplit ts_rec = (INTranSplit)it; INTran t_rec = (INTran)it; if (ts_rec.TranDate < filter.StartDate) { cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m; } else { if (summaryByDay) { if ((resultList.Count > 0) && (((InventoryTranHistEnqResult)resultList[resultList.Count - 1]).TranDate == ts_rec.TranDate)) { InventoryTranHistEnqResult lastItem = resultList[resultList.Count - 1]; if (ts_rec.InvtMult * ts_rec.BaseQty >= 0) { lastItem.QtyIn += ts_rec.InvtMult * ts_rec.BaseQty; } else { lastItem.QtyOut -= ts_rec.InvtMult * ts_rec.BaseQty; } lastItem.EndQty += ts_rec.InvtMult * ts_rec.BaseQty; } else { InventoryTranHistEnqResult item = new InventoryTranHistEnqResult(); item.BegQty = cumulativeQty; item.TranDate = ts_rec.TranDate; if (ts_rec.InvtMult * ts_rec.BaseQty >= 0) { item.QtyIn = ts_rec.InvtMult * ts_rec.BaseQty; item.QtyOut = 0m; } else { item.QtyIn = 0m; item.QtyOut = -ts_rec.InvtMult * ts_rec.BaseQty; } item.EndQty = item.BegQty + (ts_rec.InvtMult * ts_rec.BaseQty); item.GridLineNbr = ++gridLineNbr; resultList.Add(new PXResult <InventoryTranHistEnqResult, INTran>(item, null)); } cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m; } else { InventoryTranHistEnqResult item = new InventoryTranHistEnqResult(); item.BegQty = cumulativeQty; item.TranDate = ts_rec.TranDate; if (ts_rec.InvtMult * ts_rec.BaseQty >= 0) { item.QtyIn = ts_rec.InvtMult * ts_rec.BaseQty; item.QtyOut = 0m; } else { item.QtyIn = 0m; item.QtyOut = -ts_rec.InvtMult * ts_rec.BaseQty; } item.EndQty = item.BegQty + (ts_rec.InvtMult * ts_rec.BaseQty); item.InventoryID = ts_rec.InventoryID; item.TranType = ts_rec.TranType; item.DocType = ts_rec.DocType; item.DocRefNbr = ts_rec.RefNbr; item.SubItemID = ts_rec.SubItemID; item.SiteID = ts_rec.SiteID; item.LocationID = ts_rec.LocationID; item.LotSerialNbr = ts_rec.LotSerialNbr; item.FinPerNbr = t_rec.FinPeriodID; item.TranPerNbr = t_rec.TranPeriodID; item.Released = t_rec.Released; item.GridLineNbr = ++gridLineNbr; resultList.Add(new PXResult <InventoryTranHistEnqResult, INTran>(item, t_rec)); cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m; } } } return(resultList); }
protected virtual IEnumerable supplierProducts() { // Creating a dynamic query // Data is ordered by the productID (and supplierID). PXSelectBase <SupplierProduct> query = new PXSelectReadonly3 <SupplierProduct, InnerJoin <Supplier, On <Supplier.supplierID, Equal <SupplierProduct.supplierID> > >, OrderBy <Asc <SupplierProduct.productID, Asc <SupplierProduct.supplierID> > > >(this); // Adding filtering conditions to the query SupplierFilter filter = Filter.Current; if (filter.CountryCD != null) { query.WhereAnd <Where <Supplier.countryCD, Equal <Current <SupplierFilter.countryCD> > > >(); } if (filter.MinOrderQty != null) { query.WhereAnd <Where <SupplierProduct.minOrderQty, GreaterEqual <Current <SupplierFilter.minOrderQty> > > >(); } // Returning the result of the query for the non-aggregated mode if (filter.GroupBySupplier != true) { return(query.Select()); } PXResultset <SupplierProduct, Supplier> result = new PXResultset <SupplierProduct, Supplier>(); SupplierProduct pendingProduct = null; Supplier pendingSupplier = null; int supplierCount = 0; List <string> countries = new List <string>(); // Iterating over all records returned by the query foreach (PXResult <SupplierProduct, Supplier> record in query.Select()) { SupplierProduct supplierProduct = (SupplierProduct)record; Supplier supplier = (Supplier)record; // Comparing the current supplier product with the previous one if (pendingProduct != null && supplierProduct.ProductID != pendingProduct.ProductID) { CalcAggregates(ref pendingProduct, ref pendingSupplier, ref supplierCount, countries); result.Add(new PXResult <SupplierProduct, Supplier>( pendingProduct, pendingSupplier)); ClearTotals(ref pendingProduct, ref pendingSupplier, ref supplierCount, countries); } CalcTotals(supplierProduct, supplier, ref pendingProduct, ref pendingSupplier, ref supplierCount, countries); } if (pendingProduct != null && pendingSupplier != null) { CalcAggregates(ref pendingProduct, ref pendingSupplier, ref supplierCount, countries); result.Add(new PXResult <SupplierProduct, Supplier>( pendingProduct, pendingSupplier)); } return(result); }