Bulk Product Import

Flow ID: SY-17 | Module(s): job, eshop | Complexity: High Last Updated: 2026-06-04

Business Overview

The bulk product import system allows merchants to create and update products in batch via Excel spreadsheets (XLSX/XLS). Three distinct jobs handle different scenarios: inserting new products, updating existing products by barcode match, and updating existing products by SKU (product code) match. All three are triggered from the admin panel and run asynchronously as background jobs, with task notifications on completion.

Three import modes:

1. Insert new products -- creates products that do not yet exist (matched by barcode), set as inactive by default
2. Update by barcode -- updates existing products matched by their barcode value
3. Update by product code (SKU) -- updates existing products matched by their SKU in the product_codes table

What the user experiences:

• Upload an XLSX/XLS file via the admin product import interface
• File is persisted via storage('private') under IMPORT_FILES_PATH (local-disk root or S3, depending on PRIVATE_STORAGE_DISK)
• Job runs asynchronously in the background
• On completion, an admin task notification appears with results (e.g., "25 products imported")

---

Architecture

Admin Upload -> storage('private')->putStream(IMPORT_FILES_PATH . fileName)
  |
  +--> AdvInsertProductsFromFile          (new products)
  |      OR
  +--> AdvUpdateProductsFromFileByBarcode  (update by barcode)
  |      OR
  +--> AdvUpdateProductsFromFileByProductCode (update by SKU)
       |
       +--> LocalTempFile::withFile(...)  Materialise remote object as local temp
       +--> loadFile($localPath)          PhpSpreadsheet XLSX/XLS reader
       +--> processSheetData($data)       Parse rows, match/create products (in DB transaction)
       +--> tasks_model::addTask()        Notify admin of results
       +--> storage('private')->delete()  Remove source from storage (only on success)

Job Classes

Class	File	Purpose
AdvInsertProductsFromFile	ecommercen/job/libraries/AdvInsertProductsFromFile.php	Insert new products
AdvUpdateProductsFromFileByBarcode	ecommercen/job/libraries/AdvUpdateProductsFromFileByBarcode.php	Update by barcode
AdvUpdateProductsFromFileByProductCode	ecommercen/job/libraries/AdvUpdateProductsFromFileByProductCode.php	Update by SKU
InsertProductsFromFile	application/modules/job/libraries/InsertProductsFromFile.php	Client override (insert)
UpdateProductsFromFileByBarcode	application/modules/job/libraries/UpdateProductsFromFileByBarcode.php	Client override (barcode)
UpdateProductsFromFileByProductCode	application/modules/job/libraries/UpdateProductsFromFileByProductCode.php	Client override (SKU)

Dependencies

Class	File	Role
Product_model	application/modules/eshop/models/Product_model.php	Product lookup and slug creation
Vendors_model	application/modules/eshop/models/Vendors_model.php	Vendor lookup/creation
Product_category_model	application/modules/eshop/models/Product_category_model.php	Category lookup/creation
Vats_model	application/modules/eshop/models/Vats_model.php	VAT rate lookup/creation
Product_media_model	application/modules/eshop/models/Product_media_model.php	Image association
Tasks_model	application/modules/auth/models/Tasks_model.php	Admin notification

---

Code Flow

Common: File Loading

All three jobs share the same file loading strategy:

1. Build remote path: IMPORT_FILES_PATH . $fileName (storage-relative — used via storage('private'))
2. Materialise the remote object as a local temp file via LocalTempFile::withFile(storage('private'), $remotePath, fn ($localPath) => …) — PhpSpreadsheet requires a real filesystem path so the bytes are downloaded into sys_get_temp_dir() for the read, then unlinked in finally regardless of outcome
3. Try XLSX reader (PhpSpreadsheet\Reader\Xlsx) on the local path
4. On failure, try XLS reader (PhpSpreadsheet\Reader\Xls) on the local path
5. On failure, set error message title and return empty array
6. Sheet data is converted to an associative array keyed by column letters (A-Q)
7. After the callback returns, the source file on storage('private') is deleted only if processing succeeded — failed extractions leave the source for operator inspection

Spreadsheet Column Mapping

Column	Field	Required (Insert)	Description
A	SKU	Yes	Product code / SKU
B	Product Name	Yes	Product name
C	Vendor	No	Vendor name (matched or "No Vendor" fallback)
D	Barcode	No	Product barcode (match key for barcode updates)
E	VAT %	Yes	VAT percentage (matched or created)
F	Price	No	Retail price including VAT
G	Discount Price	No	Final/discounted price (used to calculate discount %)
H	Stock	No	Stock quantity
I	Weight	No	Product weight
J	Description	No	Full product description
K	Short Description	No	Short/summary description
L	Usage	No	Usage instructions
M	Ingredients	No	Product ingredients
N	Image 1	No	Primary image filename/URI
O	Image 2	No	Secondary image filename/URI
P	Image 3	No	Tertiary image filename/URI
Q	Image 4	No	Quaternary image filename/URI

SY-17a: Insert New Products (AdvInsertProductsFromFile)

1. Skip header row (row 1 is column headers)
2. Extract barcodes from column D of all rows
3. Find existing products via product_model->getProductIdsByBarcode()
4. Filter new products -- only rows whose barcode is NOT in the existing set
5. Validate required fields: columns A (SKU), B (Name), E (VAT%) must be non-empty
6. For each new product: a. Get or create VAT rate b. Get or create vendor (falls back to "No Vendor") c. Calculate net price: price / (1 + vat/100) d. Calculate discount percentage if discount price provided e. Insert into shop_product with active=0 f. Assign to "Uncategorized" category (created if missing) via Adv_product_category_model::linkProductCategory() (ecommercen/job/libraries/AdvInsertProductsFromFile.php:175-179); the call is idempotent — re-running the import or overlapping rows cannot create duplicate junction rows because the table carries a UNIQUE INDEX on (product_id, category_id) (see AD-05 Category Management) g. Insert shop_product_mui for all site languages (name + slug) h. Insert shop_product_barcodes if barcode provided i. Insert product_codes with SKU and stock j. Insert product images (up to 4, first is main)
7. Notify admin via tasks_model->addTask() with count of inserted products
8. Delete file after processing

SY-17b: Update by Barcode (AdvUpdateProductsFromFileByBarcode)

1. Skip header row
2. Extract barcodes from column D
3. Find existing products via product_model->getProductIdsByBarcode()
4. If no matches found, set error notification and exit
5. For each matching row: a. Update shop_product: VAT, vendor, weight, price, discount (only non-empty fields) b. Update shop_product_mui for all languages: name, description, short_description, usage, ingredients c. Update product_codes stock (if provided)
6. Notify admin with updated/total counts

SY-17c: Update by Product Code (AdvUpdateProductsFromFileByProductCode)

1. Skip header row
2. Extract SKUs from column A
3. Find existing products via product_model->getProductIdsByProductCode()
4. If no matches found, set error notification and exit
5. For each matching row: a. Update shop_product: VAT, vendor, weight, price, discount b. Update shop_product_mui for all languages c. Update shop_product_barcodes (if barcode provided) d. Update product_codes stock (if provided)
6. Notify admin with updated/total counts

---

Data Model

Primary Tables

Table	Role
shop_product	Main product record (price, weight, VAT, vendor, active status)
shop_product_mui	Product name, slug, descriptions per language
product_codes	SKU and stock per product variant
shop_product_barcodes	Barcode associations
product_media	Product images
shop_product_category_lp	Product-to-category assignments; duplicate-safe (UNIQUE INDEX on (product_id, category_id) — see AD-05 Category Management)

Auto-Created Reference Data

The import jobs automatically create missing reference data:

Entity	Condition	Default Values
VAT rate	No matching value in shop_vat	Created with specified percentage
Vendor	No matching vendor name	Falls back to "No Vendor" (created if missing)
Category	"Uncategorized" not found	Created as unpublished root category

---

Configuration

Job Scheduling

These jobs are NOT cron-scheduled. They are dispatched on-demand from the admin panel when a file is uploaded:

// Triggered via admin interface, not via jobs.php
// CLI: php cli.php job/run/InsertProductsFromFile --customerId=1 --fileName=import_20240101.xlsx

Job Options

All three jobs share the same options:

Option	Type	Required	Description
customerId	int	Yes	Admin user ID for task notification
fileName	string	Yes	Uploaded file name within the private storage type under IMPORT_FILES_PATH

Constants

Constant	Value	Defined In
IMPORT_FILES_PATH	'import/' (storage-relative — used via storage('private'))	application/config/constants.php

Import Defaults (Insert Job)

Setting	Value	Purpose
active	0	New products are created as inactive
pointFactor	10	Default loyalty points factor
uncategorizedCategory	'Uncategorized'	Default category for new products

---

Client Extension Points

1. Override the job classes: Create subclasses in application/modules/job/libraries/ to customize import logic (e.g., add custom columns, change matching strategy, add validation).

2. Override spreadsheet parsing: Override getProductData() in a subclass to add or remap columns (e.g., add custom attributes in columns R+).

3. Override product creation: Override insertProduct() or updateProduct() to add client-specific fields or post-processing (e.g., auto-categorization, ERP sync).

4. Override file loading: Override loadFile() to support additional formats (e.g., CSV).

5. Override notification: Override the task notification section in executeCommand() to customize the completion message or add email notification.

---

Business Rules

Rule	Description
Inactive by default	New products from insert are created with active=0 -- admin must review and activate
Transactional	All database operations run within a transaction; failure rolls back all changes
Barcode deduplication	Insert job skips rows whose barcode already exists in the database
Required fields (insert)	SKU (A), Name (B), and VAT (E) are required for insertion
Partial updates	Update jobs only modify non-empty fields; blank cells are skipped
Net price calculation	Prices in the file are VAT-inclusive; the system stores net prices: price / (1 + vat%)
Discount calculation	When a discount price is provided: discount% = ((price - finalPrice) / price) * 100
Multi-language MUI	Product names and descriptions are inserted/updated for ALL configured site languages
File cleanup	The uploaded file is deleted after processing regardless of outcome
Admin notification	A task is always created for the admin user, even on error

---

Related Flows

• SY-01 Cron Job Framework -- job execution framework (though these are on-demand, not scheduled)
• SY-18 Image Upload from ZIP -- companion bulk image import
• SY-25 File Upload & Storage -- AdvUploader and HandlesUploadActions used to accept the uploaded spreadsheet file from the admin UI
• AD-02 Product Management -- admin interface where import is triggered
• AD-55 Task Management -- explains the notification queue pattern: why creator_id = assignee_id = customerId and due_date = now() causes the bell to light up immediately, and the hardcoded Greek text quirk in the ZIP job