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);
}
