Ecommercen Docs

Appearance

New Product Prices (ERP Price Import) ​

Flow ID: AD-43 | Module(s): eshop | Complexity: Medium Last Updated: 2026-04-04

Business Context ​

The "New Product Prices" feature manages incoming price updates from external ERP systems. When an ERP pushes new retail prices (with VAT) and wholesale prices for products, they are staged in a new_product_prices holding table. Administrators then review these pending prices and can selectively import them one at a time or all at once.

The import process recalculates the net price (without VAT) from the provided price-with-VAT using the product's VAT rate, then updates the main shop_product table. This ensures price accuracy through the ERP-to-eshop pipeline while giving administrators control over when prices go live.

API Reference ​

REST Endpoints ​

No REST API. Price imports are managed through the legacy admin panel.

Legacy Admin Routes ​

RouteControllerMethodHTTPDescription
eshop/new_product_pricesAdv_new_product_pricesindexGETList pending price updates
eshop/new_product_prices/updatePrice/{id}Adv_new_product_pricesupdatePriceGETImport a single price update
eshop/new_product_prices/updateAllPricesAdv_new_product_pricesupdateAllPricesGETImport all pending price updates
eshop/new_product_prices/delete/{id}Adv_new_product_pricesdeleteGETDelete a single pending record
eshop/new_product_prices/deleteAllAdv_new_product_pricesdeleteAllGETDelete all pending records

Code Flow ​

Listing Pending Prices ​

  1. Admin navigates to eshop/new_product_prices.
  2. index() calls new_product_prices_model::getNewProductPriceList() which queries new_product_prices ordered by insert_date DESC, excluding imported records (is_imported IS NULL OR is_imported = 0).
  3. For each record, loads the product's MUI names and barcodes for display.
  4. Renders the admin/products/new_product_prices_list view with records, MUI data, and barcodes.

Importing a Single Price ​

  1. Admin clicks "Import" on a specific record. GET to updatePrice/{id}.
  2. Calls new_product_prices_model::updateNewProductPriceFromErp($id, $successMsg, $errorMsg).
  3. The import process: a. Fetches the staged record from new_product_prices by ID. b. Fetches the corresponding shop_product master record. c. Loads VAT rates from vats table. d. Calculates net price: price = price_with_vat - (price_with_vat * vat_rate / (100 + vat_rate)), rounded to 2 decimals. e. Builds update data: price (net) and wholesale_price. Zero values are excluded. f. Duplicate check: If the calculated values already match the current product prices, skips the DB update (workaround for affected_rows() returning 0 on no-change updates). g. Calls update_master_record() to update shop_product. h. Marks the staged record as imported: is_imported = true, update_date = now().
  4. Calls afterUpdatePrice($id) hook (empty by default, overridable by client repos).
  5. Sets success/error flash messages and redirects back to the list.

Importing All Prices ​

  1. Admin clicks "Import All". GET to updateAllPrices.
  2. Fetches all non-imported records from new_product_prices.
  3. Passes all IDs to updateNewProductPriceFromErp() which processes each sequentially.
  4. Calls afterUpdateAllPrices($ids) hook.
  5. Summary messages: "X records updated successfully" / "Y records failed" with per-product error details.

Deleting Records ​

  • delete($id): Deletes a single staged record via new_product_prices_model::deleteRecord($id). Calls afterDelete($id) hook.
  • deleteAll(): Deletes all non-imported records directly via DB query. Calls afterDeleteAll() hook.

Domain Layer ​

No modern domain layer. The model extends Product_model (not Adv_base_model) to reuse product query methods like getMasterRecordsWhereIn() and update_master_record().

Architecture ​

ComponentPathPurpose
Adv_new_product_pricesecommercen/eshop/controllers/Adv_new_product_prices.phpAdmin controller (177 lines)
Adv_new_product_prices_modelecommercen/eshop/models/Adv_new_product_prices_model.phpModel extending Product_model (161 lines)
New_product_prices_modelapplication/modules/eshop/models/New_product_prices_model.phpApp-level wrapper
vats_modelecommercen/eshop/models/VAT rates for price calculation
product_modelecommercen/eshop/models/Parent model for product updates
Routesapplication/config/routes.php:409-412Route definitions

Data Model ​

new_product_prices ​

ColumnTypeDescription
idint (PK)Auto-increment primary key
product_idint (FK)Target product ID
price_with_vatdecimalNew retail price including VAT (from ERP)
wholesale_pricedecimalNew wholesale price (from ERP)
is_importedtinyint/boolWhether the price has been applied (null/0 = pending, 1 = imported)
insert_datedatetimeWhen the ERP pushed the record
update_datedatetimeWhen the price was imported to shop_product

Affected Table: shop_product ​

ColumnUpdated ByDescription
priceCalculatedNet price (without VAT), derived from price_with_vat
wholesale_priceDirect copyWholesale price from ERP

Configuration ​

Required roles: AUTH_ROLE_ADVISABLE, AUTH_ROLE_ADMIN, AUTH_ROLE_PRODUCTS.

No registry keys. The feature depends on external ERP integration to populate the new_product_prices table.

Client Extension Points ​

  • Override controller: Extend in application/modules/eshop/controllers/ to customize the admin interface.
  • Hook methods: Four protected hook methods can be overridden in client controllers:
    • afterUpdatePrice($id) -- after single price import
    • afterUpdateAllPrices($ids) -- after bulk price import
    • afterDelete($id) -- after single record deletion
    • afterDeleteAll() -- after bulk deletion
  • Override model: Extend New_product_prices_model to customize the price calculation formula or add additional fields to the update.
  • ERP integration: The new_product_prices table is populated by external processes (ERP sync jobs, API imports). Client repos typically have custom ERP integrations that write to this table.

Business Rules ​

  1. VAT recalculation: Net price is derived from price_with_vat using the formula: price = price_with_vat - (price_with_vat * vat / (100 + vat)).
  2. Zero exclusion: If the calculated net price or wholesale price is 0, that field is excluded from the update.
  3. Idempotent import: If the calculated values already match the current shop_product values, the record is still marked as imported without issuing an UPDATE query.
  4. Staging table pattern: Prices are staged in new_product_prices before going live, giving admins review and approval control.
  5. Per-record tracking: Each staged record tracks its import status and timestamp independently.
  6. Model inheritance: The model extends Product_model rather than Adv_base_model, giving it direct access to product table helpers.