public override void Persist()
{
foreach (APVendorPrice price in Records.Cache.Inserted)
{
APVendorPrice lastPrice = FindLastPrice(this, price);
if (lastPrice != null)
{
if (lastPrice.EffectiveDate > price.EffectiveDate && price.ExpirationDate == null)
{
Records.Cache.RaiseExceptionHandling <APVendorPrice.expirationDate>(price, price.ExpirationDate, new PXSetPropertyException(ErrorMessages.FieldIsEmpty, PXUIFieldAttribute.GetDisplayName <APVendorPrice.expirationDate>(Records.Cache)));
throw new PXSetPropertyException(ErrorMessages.FieldIsEmpty, PXUIFieldAttribute.GetDisplayName <APVendorPrice.expirationDate>(Records.Cache));
}
}
ValidateDuplicate(this, Records.Cache, price);
}
foreach (APVendorPrice price in Records.Cache.Updated)
{
APVendorPrice lastPrice = FindLastPrice(this, price);
if (lastPrice != null)
{
if (lastPrice.EffectiveDate > price.EffectiveDate && price.ExpirationDate == null)
{
Records.Cache.RaiseExceptionHandling <APVendorPrice.expirationDate>(price, price.ExpirationDate, new PXSetPropertyException(ErrorMessages.FieldIsEmpty, PXUIFieldAttribute.GetDisplayName <APVendorPrice.expirationDate>(Records.Cache)));
throw new PXSetPropertyException(ErrorMessages.FieldIsEmpty, PXUIFieldAttribute.GetDisplayName <APVendorPrice.expirationDate>(Records.Cache));
}
}
ValidateDuplicate(this, Records.Cache, price);
}
base.Persist();
Records.Cache.Clear();
}
protected virtual void APVendorPrice_RowSelected(PXCache sender, PXRowSelectedEventArgs e)
{
APVendorPrice row = e.Row as APVendorPrice;
if (row != null)
{
PXUIFieldAttribute.SetEnabled<APVendorPrice.vendorID>(sender, row, Filter.Current.VendorID == null);
PXUIFieldAttribute.SetEnabled<APVendorPrice.inventoryID>(sender, row, Filter.Current.InventoryID == null);
}
}
public static bool IsOverlapping(APVendorPrice vendorPrice1, APVendorPrice vendorPrice2)
{
return((vendorPrice1.EffectiveDate != null && vendorPrice1.ExpirationDate != null && vendorPrice2.EffectiveDate != null && vendorPrice2.ExpirationDate != null && (vendorPrice1.EffectiveDate <= vendorPrice2.EffectiveDate && vendorPrice1.ExpirationDate >= vendorPrice2.EffectiveDate || vendorPrice1.EffectiveDate <= vendorPrice2.ExpirationDate && vendorPrice1.ExpirationDate >= vendorPrice2.ExpirationDate || vendorPrice1.EffectiveDate >= vendorPrice2.EffectiveDate && vendorPrice1.EffectiveDate <= vendorPrice2.ExpirationDate)) ||
(vendorPrice1.ExpirationDate != null && vendorPrice2.EffectiveDate != null && (vendorPrice2.ExpirationDate == null || vendorPrice1.EffectiveDate == null) && vendorPrice2.EffectiveDate <= vendorPrice1.ExpirationDate) ||
(vendorPrice1.EffectiveDate != null && vendorPrice2.ExpirationDate != null && (vendorPrice2.EffectiveDate == null || vendorPrice1.ExpirationDate == null) && vendorPrice2.ExpirationDate >= vendorPrice1.EffectiveDate) ||
(vendorPrice1.EffectiveDate != null && vendorPrice2.EffectiveDate != null && vendorPrice1.ExpirationDate == null && vendorPrice2.ExpirationDate == null) ||
(vendorPrice1.ExpirationDate != null && vendorPrice2.ExpirationDate != null && vendorPrice1.EffectiveDate == null && vendorPrice2.EffectiveDate == null) ||
(vendorPrice1.EffectiveDate == null && vendorPrice1.ExpirationDate == null) ||
(vendorPrice2.EffectiveDate == null && vendorPrice2.ExpirationDate == null));
}
//protected virtual void APVendorPrice_EffectiveDate_FieldUpdated(PXCache sender, PXFieldUpdatedEventArgs e)
//{
// APVendorPrice price = (APVendorPrice)e.Row;
// if (price.IsPromotionalPrice != true)
// {
// APVendorPrice lastPrice = FindLastPrice(this, price);
// if (lastPrice != null && price.EffectiveDate > lastPrice.EffectiveDate && lastPrice.ExpirationDate == null)
// {
// lastPrice.ExpirationDate = price.EffectiveDate;
// Records.Update(lastPrice);
// Records.View.RequestRefresh();
// }
// }
//}
protected virtual void APVendorPrice_RowPersisting(PXCache sender, PXRowPersistingEventArgs e)
{
APVendorPrice row = (APVendorPrice)e.Row;
if (row.IsPromotionalPrice == true && row.ExpirationDate == null)
{
sender.RaiseExceptionHandling<APVendorPrice.expirationDate>(row, null, new PXSetPropertyException(ErrorMessages.FieldIsEmpty, typeof(APVendorPrice.expirationDate).Name));
}
if (row.IsPromotionalPrice == true && row.EffectiveDate == null)
{
sender.RaiseExceptionHandling<APVendorPrice.effectiveDate>(row, null, new PXSetPropertyException(ErrorMessages.FieldIsEmpty, typeof(APVendorPrice.effectiveDate).Name));
}
if (row.ExpirationDate < row.EffectiveDate)
{
sender.RaiseExceptionHandling<APVendorPrice.effectiveDate>(row, row.ExpirationDate, new PXSetPropertyException(AR.Messages.EffectiveDateExpirationDate, PXErrorLevel.RowError));
}
}
public static void ValidateDuplicate(PXGraph graph, PXCache sender, APVendorPrice price)
{
PXSelectBase<APVendorPrice> selectDuplicate = new PXSelect<APVendorPrice, Where<APVendorPrice.vendorID, Equal<Required<APVendorPrice.vendorID>>,
And<APVendorPrice.inventoryID, Equal<Required<APVendorPrice.inventoryID>>,
And<APVendorPrice.uOM, Equal<Required<APVendorPrice.uOM>>,
And<APVendorPrice.isPromotionalPrice, Equal<Required<APVendorPrice.isPromotionalPrice>>,
And<APVendorPrice.breakQty, Equal<Required<APVendorPrice.breakQty>>,
And<APVendorPrice.curyID, Equal<Required<APVendorPrice.curyID>>,
And<APVendorPrice.recordID, NotEqual<Required<APVendorPrice.recordID>>>>>>>>>>(graph);
foreach (APVendorPrice apPrice in selectDuplicate.Select(price.VendorID, price.InventoryID, price.UOM, price.IsPromotionalPrice, price.BreakQty, price.CuryID, price.RecordID))
{
if (IsOverlapping(apPrice, price))
{
sender.RaiseExceptionHandling<APVendorPrice.uOM>(price, price.UOM, new PXSetPropertyException(AR.Messages.DuplicateSalesPrice, PXErrorLevel.RowError, apPrice.SalesPrice, apPrice.EffectiveDate.HasValue ? apPrice.EffectiveDate.Value.ToShortDateString() : string.Empty, apPrice.ExpirationDate.HasValue ? apPrice.ExpirationDate.Value.ToShortDateString() : string.Empty));
throw new PXSetPropertyException(AR.Messages.DuplicateSalesPrice, PXErrorLevel.RowError, apPrice.SalesPrice, apPrice.EffectiveDate.HasValue ? apPrice.EffectiveDate.Value.ToShortDateString() : string.Empty, apPrice.ExpirationDate.HasValue ? apPrice.ExpirationDate.Value.ToShortDateString() : string.Empty);
}
}
}
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());
}
public static UnitCostItem FindUnitCost(PXCache sender, int? vendorID, int? vendorLocationID, int? inventoryID, string baseCuryID, string curyID, decimal? quantity, string UOM, DateTime date)
{
PXSelectBase<APVendorPrice> unitCost = new PXSelect<APVendorPrice, Where<APVendorPrice.inventoryID, Equal<Required<APVendorPrice.inventoryID>>,
And<APVendorPrice.vendorID, Equal<Required<APVendorPrice.vendorID>>,
And<APVendorPrice.curyID, Equal<Required<APVendorPrice.curyID>>,
And<APVendorPrice.uOM, Equal<Required<APVendorPrice.uOM>>,
And<Where2<Where<APVendorPrice.breakQty, LessEqual<Required<APVendorPrice.breakQty>>>,
And<Where2<Where<APVendorPrice.effectiveDate, LessEqual<Required<APVendorPrice.effectiveDate>>,
And<APVendorPrice.expirationDate, GreaterEqual<Required<APVendorPrice.expirationDate>>>>,
Or2<Where<APVendorPrice.effectiveDate, LessEqual<Required<APVendorPrice.effectiveDate>>,
And<APVendorPrice.expirationDate, IsNull>>,
Or<Where<APVendorPrice.expirationDate, GreaterEqual<Required<APVendorPrice.expirationDate>>,
And<APVendorPrice.effectiveDate, IsNull,
Or<APVendorPrice.effectiveDate, IsNull, And<APVendorPrice.expirationDate, IsNull>>>>>>>>>>>>>>,
OrderBy<Desc<APVendorPrice.isPromotionalPrice, Desc<APVendorPrice.vendorID, Desc<APVendorPrice.breakQty>>>>>(sender.Graph);
PXSelectBase<APVendorPrice> unitCostBaseUOM = new PXSelectJoin<APVendorPrice, InnerJoin<InventoryItem, On<InventoryItem.inventoryID,
Equal<APVendorPrice.inventoryID>, And<InventoryItem.baseUnit, Equal<APVendorPrice.uOM>>>>,
Where<APVendorPrice.inventoryID, Equal<Required<APVendorPrice.inventoryID>>,
And<APVendorPrice.vendorID, Equal<Required<APVendorPrice.vendorID>>,
And<APVendorPrice.curyID, Equal<Required<APVendorPrice.curyID>>,
And<Where2<Where<APVendorPrice.breakQty, LessEqual<Required<APVendorPrice.breakQty>>>,
And<Where2<Where<APVendorPrice.effectiveDate, LessEqual<Required<APVendorPrice.effectiveDate>>,
And<APVendorPrice.expirationDate, GreaterEqual<Required<APVendorPrice.expirationDate>>>>,
Or2<Where<APVendorPrice.effectiveDate, LessEqual<Required<APVendorPrice.effectiveDate>>,
And<APVendorPrice.expirationDate, IsNull>>,
Or<Where<APVendorPrice.expirationDate, GreaterEqual<Required<APVendorPrice.expirationDate>>,
And<APVendorPrice.effectiveDate, IsNull,
Or<APVendorPrice.effectiveDate, IsNull, And<APVendorPrice.expirationDate, IsNull>>>>>>>>>>>>>,
OrderBy<Desc<APVendorPrice.isPromotionalPrice, Desc<APVendorPrice.vendorID, Desc<APVendorPrice.breakQty>>>>>(sender.Graph);
APVendorPrice item = unitCost.SelectWindowed(0, 1, inventoryID, vendorID, curyID, UOM, quantity, date, date, date, date);
string uomFound = null;
if (item == null)
{
decimal baseUnitQty = INUnitAttribute.ConvertToBase(sender, inventoryID, UOM, (decimal)quantity, INPrecision.QUANTITY);
item = unitCostBaseUOM.Select(inventoryID, vendorID, curyID, baseUnitQty, date, date, date, date);
if (item == null)
{
/*InventoryItem inventoryItem = PXSelect<InventoryItem, Where<InventoryItem.inventoryID, Equal<Required<InventoryItem.inventoryID>>>>.Select(sender.Graph, inventoryID);
if (inventoryItem != null && inventoryItem.BasePrice != null)
{
return new UnitCostItem(inventoryItem.BaseUnit, (inventoryItem.BasePrice ?? 0m), baseCuryID);
}
else*/
return null;
}
else
{
uomFound = item.UOM;
}
}
else
{
uomFound = UOM;
}
if (item == null)
{
return null;
}
return new UnitCostItem(uomFound, (item.SalesPrice ?? 0), item.CuryID);
}
public static APVendorPrice FindLastPrice(PXGraph graph, APVendorPrice price)
{
APVendorPrice lastPrice = new PXSelect<APVendorPrice, Where<APVendorPrice.vendorID, Equal<Required<APVendorPrice.vendorID>>, And<APVendorPrice.inventoryID, Equal<Required<APVendorPrice.inventoryID>>, And<APVendorPrice.uOM, Equal<Required<APVendorPrice.uOM>>, And<APVendorPrice.isPromotionalPrice, Equal<Required<APVendorPrice.isPromotionalPrice>>, And<APVendorPrice.breakQty, Equal<Required<APVendorPrice.breakQty>>, And<APVendorPrice.curyID, Equal<Required<APVendorPrice.curyID>>, And<APVendorPrice.recordID, NotEqual<Required<APVendorPrice.recordID>>>>>>>>>, OrderBy<Desc<APVendorPrice.effectiveDate>>>(graph).SelectSingle(price.VendorID, price.InventoryID, price.UOM, price.IsPromotionalPrice, price.BreakQty, price.CuryID, price.RecordID);
return lastPrice;
}
