Product Controller Documentation

File: /controllers/productController.php

Purpose: Manages product master data, categories, units, barcodes, and inventory

Last Updated: December 19, 2024

Total Functions: 62

Lines of Code: ~5,950

---

📋 Overview

The Product Controller is the central component for managing all product-related operations in the ERP system. It handles:

• • Creating and editing products
• • Product categories (hierarchical)
• • Product units of measurement
• • Barcode generation and management
• • Product variants (size/color)
• • Collective products (recipes/bundles)
• • Excel import/export
• • Optical products (specialized)
• • Product images
• • Initial stock setup

Primary Functions

• ✅ Create new products
• ✅ Edit existing products
• ✅ Delete products (soft delete)
• ✅ View product list with search/filters
• ✅ Manage product categories (tree structure)
• ✅ Manage product units (conversion factors)
• ✅ Generate barcodes automatically
• ✅ Handle product variants (size/color)
• ✅ Manage collective products (recipes)
• ✅ Import products from Excel
• ✅ Export products to Excel
• ✅ Print barcode labels
• ✅ Upload product images
• ✅ Set initial stock quantities

Related Controllers

• • buyBillController.php - Purchase operations
• • sellbillController.php - Sales operations
• • storeController.php - Warehouse/store management
• • supplierController.php - Supplier management (product suppliers)
• • storedetailController.php - Inventory management
• • productcatController.php - Product categories
• • productunitController.php - Units of measurement
• • productcatController.php - Category management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. add() - Create New Product

Location: Line 2253

Purpose: Main function to create a new product with all details

Function Signature:

function add()

Process Flow:

1. Begin transaction

2. Validate product name and barcode uniqueness

3. Handle image upload

4. Generate barcode (if auto-generation enabled)

5. Create product master record

6. Create product units (conversion factors)

7. Set initial stock quantities per store

8. Handle size/color variants (if applicable)

9. Commit transaction

10. Redirect to success page

Key Variables:

• • $productname - Product name
• • $productcatid - Category ID
• • $productbuyprice - Purchase price
• • $productsellallprice - Wholesale price
• • $productsellunitprice - Retail price
• • $parcode - Barcode
• • $limitamount - Minimum stock alert level
• • $isCollectiveProduct - Is recipe/bundle flag

Dependencies:

• • generateParcode() - Auto barcode generation
• • checkbarcode() - Barcode uniqueness validation
• • addProductSizeAndColor() - Variant handling
• • addproductIngridients() - Recipe ingredients

---

2. generateParcode() - Auto Generate Barcode

Location: Line 2113

Purpose: Generate unique barcode automatically

Function Signature:

function generateParcode()

Logic:

// Get next available barcode number
$sql = "SELECT MAX(CAST(parcode AS UNSIGNED)) as maxparcode FROM parcode";
$result = R::getAll($sql);
$nextBarcode = $result[0]['maxparcode'] + 1;

// Pad with leading zeros (7 digits)
$barcode = str_pad($nextBarcode, 7, '0', STR_PAD_LEFT);

return $barcode;

Example:

• • Last barcode: 0001234
• • Generated: 0001235

Used By:

• • add() - When creating products
• • addProductExcel() - Excel import

---

3. checkbarcode() - Validate Barcode Uniqueness

Location: Line 4969

Purpose: Check if barcode already exists

Function Signature:

function checkbarcode($parcode, $productId = 0)

Returns:

• • true - Barcode is unique
• • false - Barcode already exists

Logic:

$sql = "SELECT * FROM product 
        WHERE parcode = ? 
        AND productid != ? 
        AND conditions = 0";

$exists = R::getAll($sql, [$parcode, $productId]);

return empty($exists);

Parameters:

• • $parcode - Barcode to check
• • $productId - Current product ID (for edit mode, 0 for new)

---

4. update() - Edit Existing Product

Location: Line 3160

Purpose: Update product master data and related records

Process Flow:

1. Begin transaction

2. Load existing product data

3. Validate changes (name, barcode)

4. Handle image changes

5. Update product master record

6. Update/add/remove product units

7. Update size/color variants (if applicable)

8. Update collective product ingredients (if applicable)

9. Handle price change accounting entries

10. Commit transaction

Critical Notes:

• • Changing buy price generates accounting adjustment entry
• • Cannot change product type (simple ↔ collective ↔ variant)
• • Stock quantities are NOT changed (use stock adjustment)

Price Change Accounting:

if ($oldBuyPrice != $newBuyPrice) {
    priceDiffDailyEntry($productId, $newBuyPrice);
}

---

5. show() - List All Products

Location: Line 2882

Purpose: Display paginated product list with search and filters

Features:

• • Pagination (50 products per page)
• • Search by: name, barcode, category
• • Filter by: category, stock status, price range
• • Sort by: name, price, stock quantity
• • Export to Excel

SQL Query:

SELECT 
    product.*,
    productcat.productcatname,
    (SELECT SUM(productquantity) FROM storedetail 
     WHERE storedetail.productid = product.productid) as totalstock
FROM product
LEFT JOIN productcat ON product.productcatid = productcat.productcatid
WHERE product.conditions = 0
ORDER BY product.productname ASC
LIMIT 50 OFFSET 0

Template Variables:

• • $productArr - Array of products
• • $productCatArr - Categories for filter dropdown
• • $totalProducts - Total count for pagination
• • $currentPage - Current page number

---

6. tempdelete() - Soft Delete Product

Location: Line 3074

Purpose: Soft delete product (mark as deleted, preserve data)

Function Signature:

function tempdelete($productId)

Process Flow:

1. Check if product is used in bills

2. If used: prevent deletion, show error

3. If not used: Set product.conditions = 1

4. Mark related records as deleted

5. Redirect to success

Validation:

-- Check buy bills
SELECT COUNT(*) FROM buybilldetail 
WHERE buybilldetailproductid = ?

-- Check sell bills
SELECT COUNT(*) FROM sellbilldetail 
WHERE sellbilldetailproductid = ?

If product is used: Cannot delete, show error message

Soft Delete:

UPDATE product 
SET conditions = 1 
WHERE productid = ?

---

7. deleteFinaly() - Permanent Delete

Location: Line 3691

Purpose: Permanently delete product and all related data

Warning: ⚠️ Destructive operation - Cannot be undone!

Process Flow:

1. Begin transaction

2. Delete from productunit

3. Delete from storedetail

4. Delete from storereport

5. Delete from sizecolorstoredetail (if variants)

6. Delete from productingridient (if collective)

7. Delete from productserial (if serialized)

8. Delete from product master table

9. Commit transaction

Used By: Admin only, after tempdelete()

---

8. addProductSizeAndColor() - Create Variant Product

Location: Line 5247

Purpose: Create product with size/color variants

Function Signature:

function addProductSizeAndColor($productId, $buyprice, $sellunitprice)

Process Flow:

1. Read size/color inputs from $_POST

2. Loop through each size/color combination

3. Create sizecolorstoredetail records

4. Set initial stock quantities per store

5. Insert into storereport for audit

Example Data Structure:

$_POST['sizeArray'] = [1, 2, 3]; // Size IDs
$_POST['colorArray'] = [1, 2];   // Color IDs
$_POST['storeid'] = 5;           // Store ID

// Creates 6 records (3 sizes × 2 colors)

Database Records Created:

INSERT INTO sizecolorstoredetail 
(productid, sizeid, colorid, storeid, quantity, buyprice, sellprice)
VALUES 
(?, ?, ?, ?, ?, ?, ?)

---

9. addProductExcel() - Import Products from Excel

Location: Line 4369

Purpose: Bulk import products from Excel file

Function Signature:

function addProductExcel()

Excel File Format:

1. Upload Excel file

2. Parse file using PHPExcel library

3. Begin transaction

4. Loop through rows:

- Find/create category

- Generate/validate barcode

- Create product

- Set initial stock

5. Commit transaction

6. Show import summary

Error Handling:

• • Skip invalid rows
• • Log errors to import log
• • Continue processing remaining rows
• • Show success/error summary

---

10. getProductCatsForShow() - Get Category Tree

Location: Line 2163

Purpose: Build hierarchical category tree for dropdowns

Function Signature:

function getProductCatsForShow()

Returns: Array of categories with indentation

Example Output:

[
    ['productcatid' => 1, 'productcatname' => 'Electronics'],
    ['productcatid' => 2, 'productcatname' => '  Laptops'],
    ['productcatid' => 3, 'productcatname' => '    Gaming'],
    ['productcatid' => 4, 'productcatname' => '    Business'],
    ['productcatid' => 5, 'productcatname' => '  Phones'],
]

Logic:

Uses recursive fetch_recursive() to build tree with indentation based on level

---

11. addproductIngridients() - Create Collective Product

Location: Line 5113

Purpose: Add recipe/bundle ingredients for collective products

Function Signature:

function addproductIngridients($proId)

Example: Pizza = Dough + Cheese + Sauce

Process Flow:

1. Read ingredients from $_POST

2. Loop through each ingredient:

- Ingredient product ID

- Quantity required

3. Insert into productingridient table

Data Structure:

$_POST['productIngredientid'] = [10, 11, 12]; // Ingredient IDs
$_POST['productIngredientquantity'] = [1, 0.5, 0.2]; // Quantities

// Pizza (product 5) needs:
// - Dough (product 10): 1 unit
// - Cheese (product 11): 0.5 kg
// - Sauce (product 12): 0.2 liter

Database:

INSERT INTO productingridient 
(productid, ingredientproductid, quantity)
VALUES 
(5, 10, 1),
(5, 11, 0.5),
(5, 12, 0.2)

---

12. priceDiffDailyEntry() - Price Adjustment Accounting

Location: Line 5845

Purpose: Generate accounting entry when product cost changes

Function Signature:

function priceDiffDailyEntry($productId, $productBuyPrice)

Accounting Logic:

When Price Increases:

Current stock: 100 units
Old price: $5
New price: $7
Difference: $2 per unit
Total adjustment: 100 × $2 = $200

Debit:  Inventory Account     $200
Credit: Price Adjustment      $200

When Price Decreases:

Debit:  Price Adjustment      $200
Credit: Inventory Account     $200

Purpose: Keep inventory value accurate when costs change

---

🔄 Workflows

Workflow 1: Create Simple Product

---

Workflow 2: Create Product with Size/Color Variants

---

🌐 URL Routes & Actions

🐛 Known Issues & Fixes

1. Barcode Uniqueness Validation

Location: Line 4969 (checkbarcode)

Issue: In edit mode, barcode validation fails when user doesn't change barcode

Fix Applied:

// Exclude current product from uniqueness check
$sql = "SELECT * FROM product 
        WHERE parcode = ? 
        AND productid != ?  // Exclude self
        AND conditions = 0";

---

2. Image Upload Path Issues

Location: Line 2253 (add)

Issue: Image paths not working on different operating systems

Fix: Use DIRECTORY_SEPARATOR constant

$targetDir = __DIR__ . DIRECTORY_SEPARATOR . '..' . DIRECTORY_SEPARATOR . 
             'views' . DIRECTORY_SEPARATOR . 'img' . DIRECTORY_SEPARATOR . 'product';

---

3. Excel Import Memory Issues

Location: Line 4369 (addProductExcel)

Issue: Large Excel files cause out-of-memory errors

Fix: Process in chunks, increase PHP memory limit

ini_set('memory_limit', '512M');
set_time_limit(300); // 5 minutes

---

4. Category Parent Loop Prevention

Location: Line 2244 (getProductCats)

Issue: User can set category as its own parent, creating infinite loop

Fix: Validate parent selection

if ($parentId == $categoryId) {
    throw new Exception("Category cannot be its own parent");
}

// Check for circular reference
if (isDescendant($parentId, $categoryId)) {
    throw new Exception("Circular reference detected");
}

---

5. Collective Product Stock Calculation

Location: Line 5113 (addproductIngridients)

Issue: Selling collective product doesn't decrease ingredient stock

Solution: Handled in sellbillController.php

• • ifCollectiveDecreaseItsIngriedientsInStore()
• • Decreases ingredient quantities when collective product is sold

---

🔒 Security & Permissions

Authentication

• • All actions require active session
• • User ID logged with every operation
• • Session regenerated on every request

Authorization

• • Create/Edit/Delete: Requires product management permission
• • View: All users
• • Excel import/export: Admin only
• • Price change: Manager approval (optional setting)

Input Validation

• • Product name: Required, max 255 chars
• • Prices: Numeric, >= 0
• • Barcode: Numeric, unique
• • Category: Must exist in database
• • Images: Allowed types (jpg, png), max 5MB

SQL Injection Prevention

• • All queries use RedBeanPHP ORM
• • Prepared statements for raw SQL

File Upload Security

• • Validate file type (MIME check)
• • Rename uploaded files (prevent overwrite)
• • Store outside web root (optional)
• • Scan for malware (optional)

---

🧪 Testing & Debugging

Enable Debug Mode

// In productController.php
error_reporting(E_ALL);
ini_set('display_errors', 1);

Check Product Data

-- View product with all details
SELECT 
    p.*,
    pc.productcatname,
    (SELECT SUM(productquantity) FROM storedetail WHERE productid = p.productid) as totalstock
FROM product p
LEFT JOIN productcat pc ON p.productcatid = pc.productcatid
WHERE p.productid = [ID];

Check Product Units

SELECT 
    pu.*,
    u.unitname,
    pu.productnumber as conversion_factor
FROM productunit pu
LEFT JOIN unit u ON pu.unitid = u.unitid
WHERE pu.productid = [ID];

Check Stock by Warehouse

SELECT 
    sd.*,
    s.storename
FROM storedetail sd
LEFT JOIN store s ON sd.storeid = s.storeid
WHERE sd.productid = [ID];

Debug Barcode Generation

// Test barcode generator
$barcode = generateParcode();
echo "Generated: " . $barcode;

// Check if unique
$isUnique = checkbarcode($barcode);
echo $isUnique ? "Unique" : "Duplicate";

---

📊 Performance Considerations

Optimization Tips

1. Index columns: productname, parcode, productcatid

2. Cache category tree: Store in session

3. Limit image size: Resize to max 800×800px

4. Paginate product list: 50 items per page

5. Use lazy loading: Load images on demand

Slow Queries

-- This is slow on large datasets
SELECT * FROM product 
WHERE productname LIKE '%search%';

-- Faster with fulltext index
ALTER TABLE product ADD FULLTEXT(productname);
SELECT * FROM product 
WHERE MATCH(productname) AGAINST('search');

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • buyBillController.md - Purchase operations
• • sellbillController.md - Sales operations

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur