public virtual IEnumerable CreateWorksheet(PXAdapter adapter)
{
if (Filter.Current != null)
{
this.Save.Press();
APPriceWorksheetMaint graph = PXGraph.CreateInstance <APPriceWorksheetMaint>();
APPriceWorksheet worksheet = new APPriceWorksheet();
graph.Document.Insert(worksheet);
int startRow = PXView.StartRow;
int totalRows = 0;
object[] parameters =
{
Filter.Current.VendorID,
Filter.Current.VendorID,
Filter.Current.InventoryID,
Filter.Current.InventoryID,
Filter.Current.SiteID,
Filter.Current.SiteID,
Filter.Current.EffectiveAsOfDate,
Filter.Current.EffectiveAsOfDate,
Filter.Current.EffectiveAsOfDate,
Filter.Current.ItemClassCD,
Filter.Current.ItemClassCDWildcard,
Filter.Current.OwnerID,
Filter.Current.OwnerID,
Filter.Current.MyWorkGroup,
Filter.Current.WorkGroupID,
Filter.Current.WorkGroupID
};
Func <BqlCommand, List <Object> > performSelect = command =>
new PXView(this, false, command).Select(
PXView.Currents,
parameters,
PXView.Searches,
PXView.SortColumns,
PXView.Descendings,
Records.View.GetExternalFilters(),
ref startRow,
PXView.MaximumRows,
ref totalRows);
List <Object> allVendorPrices = performSelect(
PXSelectJoin <APVendorPrice,
LeftJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <APVendorPrice.inventoryID> >,
LeftJoin <INItemCost, On <INItemCost.inventoryID, Equal <InventoryItem.inventoryID> >,
LeftJoin <INItemClass, On <INItemClass.itemClassID, Equal <InventoryItem.itemClassID> > > > >,
Where <InventoryItem.itemStatus, NotEqual <INItemStatus.inactive>,
And <InventoryItem.itemStatus, NotEqual <INItemStatus.toDelete>,
And2 <Where <APVendorPrice.vendorID, Equal <Required <APVendorPriceFilter.vendorID> >, Or <Required <APVendorPriceFilter.vendorID>, IsNull> >,
And2 <Where <APVendorPrice.inventoryID, Equal <Required <APVendorPriceFilter.inventoryID> >, Or <Required <APVendorPriceFilter.inventoryID>, IsNull> >,
And2 <Where <APVendorPrice.siteID, Equal <Required <APVendorPriceFilter.siteID> >, Or <Required <APVendorPriceFilter.siteID>, IsNull> >,
And2 <Where2 <Where2 <Where <APVendorPrice.effectiveDate, LessEqual <Required <APVendorPriceFilter.effectiveAsOfDate> >, Or <APVendorPrice.effectiveDate, IsNull> >,
And <Where <APVendorPrice.expirationDate, GreaterEqual <Required <APVendorPriceFilter.effectiveAsOfDate> >, Or <APVendorPrice.expirationDate, IsNull> > > >,
Or <Required <APVendorPriceFilter.effectiveAsOfDate>, IsNull> >,
And <Where2 <Where <Required <APVendorPriceFilter.itemClassCD>, IsNull,
Or <INItemClass.itemClassCD, Like <Required <APVendorPriceFilter.itemClassCDWildcard> > > >,
And2 <Where <Required <APVendorPriceFilter.ownerID>, IsNull,
Or <Required <APVendorPriceFilter.ownerID>, Equal <InventoryItem.priceManagerID> > >,
And2 <Where <Required <APVendorPriceFilter.myWorkGroup>, Equal <False>,
Or <InventoryItem.priceWorkgroupID, InMember <CurrentValue <APVendorPriceFilter.currentOwnerID> > > >,
And <Where <Required <APVendorPriceFilter.workGroupID>, IsNull,
Or <Required <APVendorPriceFilter.workGroupID>, Equal <InventoryItem.priceWorkgroupID> > > > > > > > > > > > > >,
OrderBy <
Asc <APVendorPrice.inventoryID,
Asc <APVendorPrice.uOM,
Asc <APVendorPrice.breakQty,
Desc <APVendorPrice.effectiveDate> > > > > > .GetCommand());
List <Object> groupedVendorPrices = performSelect(
PXSelectJoinGroupBy <APVendorPrice,
LeftJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <APVendorPrice.inventoryID> >,
LeftJoin <INItemCost, On <INItemCost.inventoryID, Equal <InventoryItem.inventoryID> >,
LeftJoin <INItemClass, On <INItemClass.itemClassID, Equal <InventoryItem.itemClassID> > > > >,
Where <InventoryItem.itemStatus, NotEqual <INItemStatus.inactive>,
And <InventoryItem.itemStatus, NotEqual <INItemStatus.toDelete>,
And2 <Where <APVendorPrice.vendorID, Equal <Required <APVendorPriceFilter.vendorID> >, Or <Required <APVendorPriceFilter.vendorID>, IsNull> >,
And2 <Where <APVendorPrice.inventoryID, Equal <Required <APVendorPriceFilter.inventoryID> >, Or <Required <APVendorPriceFilter.inventoryID>, IsNull> >,
And2 <Where <APVendorPrice.siteID, Equal <Required <APVendorPriceFilter.siteID> >, Or <Required <APVendorPriceFilter.siteID>, IsNull> >,
And2 <Where2 <Where2 <Where <APVendorPrice.effectiveDate, LessEqual <Required <APVendorPriceFilter.effectiveAsOfDate> >, Or <APVendorPrice.effectiveDate, IsNull> >,
And <Where <APVendorPrice.expirationDate, GreaterEqual <Required <APVendorPriceFilter.effectiveAsOfDate> >, Or <APVendorPrice.expirationDate, IsNull> > > >,
Or <Required <APVendorPriceFilter.effectiveAsOfDate>, IsNull> >,
And <Where2 <Where <Required <APVendorPriceFilter.itemClassCD>, IsNull,
Or <INItemClass.itemClassCD, Like <Required <APVendorPriceFilter.itemClassCDWildcard> > > >,
And2 <Where <Required <APVendorPriceFilter.ownerID>, IsNull,
Or <Required <APVendorPriceFilter.ownerID>, Equal <InventoryItem.priceManagerID> > >,
And2 <Where <Required <APVendorPriceFilter.myWorkGroup>, Equal <False>,
Or <InventoryItem.priceWorkgroupID, InMember <CurrentValue <APVendorPriceFilter.currentOwnerID> > > >,
And <Where <Required <APVendorPriceFilter.workGroupID>, IsNull,
Or <Required <APVendorPriceFilter.workGroupID>, Equal <InventoryItem.priceWorkgroupID> > > > > > > > > > > > > >,
Aggregate <
GroupBy <APVendorPrice.vendorID,
GroupBy <APVendorPrice.inventoryID,
GroupBy <APVendorPrice.uOM,
GroupBy <APVendorPrice.breakQty,
GroupBy <APVendorPrice.curyID,
GroupBy <APVendorPrice.siteID> > > > > > >,
OrderBy <
Asc <APVendorPrice.inventoryID,
Asc <APVendorPrice.uOM,
Asc <APVendorPrice.breakQty,
Desc <APVendorPrice.effectiveDate> > > > > > .GetCommand());
if (allVendorPrices.Count > groupedVendorPrices.Count)
{
throw new PXException(AR.Messages.MultiplePriceRecords);
}
foreach (PXResult <APVendorPrice> res in groupedVendorPrices)
{
APVendorPrice price = res;
var detail = new APPriceWorksheetDetail
{
RefNbr = graph.Document.Current.RefNbr,
VendorID = price.VendorID
};
detail = graph.Details.Insert(detail);
detail.InventoryID = price.InventoryID;
detail.UOM = price.UOM;
detail.BreakQty = price.BreakQty;
detail.CurrentPrice = price.SalesPrice;
detail.CuryID = price.CuryID;
detail.SiteID = price.SiteID;
graph.Details.Update(detail);
}
throw new PXRedirectRequiredException(graph, AR.Messages.CreatePriceWorksheet);
}
return(adapter.Get());
}
