...
Product Cost
Product extensions are suitable to store manufacturing or purchasing costs, which will vary over time. Moreover, you might want to convert the currency ahead of time, to increase the performance of your code.
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
@Field String TABLE_NAME_PRODUCT_COST = 'Food_ProductCost'
@Field String COLUMN_NAME_VALID_FROM = 'attribute1'
@Field String COLUMN_NAME_AVERAGE_COST = 'attribute2'
@Field String COLUMN_NAME_CURRENCY = 'attribute3'
BigDecimal findCost(
String sku,
String currency,
String date = new Date()
){
String dateAsString = date.format('yyyy-MM-dd')
Filter filter = Filter.and(
Filter.equal('name', TABLE_NAME_PRODUCT_COST), // ❶
Filter.equal('sku', sku),
Filter.equal(COLUMN_NAME_CURRENCY, currency),
Filter.lessOrEqual(COLUMN_NAME_VALID_FROM, dateAsString)// ❷
)
return api.find(
'PX',
0,
1, // ❸
COLUMN_NAME_VALID_FROM, // ❹
[COLUMN_NAME_AVERAGE_COST], // ❺
filter
).findgetAt(0)?.getAt(COLUMN_NAME_AVERAGE_COST) as BigDecimal // ❻
} |
❶ Only include those rows that belong to the specified product extension table.
❷ Only include costs that are valid from before the specified date.
❸ Only fetch a single result.
❹ Sort according to the valid date. The latest date will be first. Since the query only includes a single result, that result will be the most up-to-date cost.
❺ Only fetch the necessary data
❻ In the database, all values are stored as strings. Therefore, it can happen that a string representation is returned — rather than a decimal value.