Inventory Controller Documentation

File: /controllers/inventoryController.php

Purpose: Physical inventory management and stock reconciliation system

Last Updated: December 20, 2024

Total Functions: 15+

Lines of Code: ~634

---

📋 Overview

The Inventory Controller handles physical inventory counting, stock reconciliation, and inventory adjustments. It provides tools for:

• • Physical inventory counting by category, store, or product
• • Inventory adjustments and reconciliation
• • Automatic daily entries for inventory changes
• • Support for size/color variations
• • Store reporting and tracking
• • Multi-store inventory management
• • Real-time quantity updates

Primary Functions

• ✅ Physical inventory counting interface
• ✅ Quantity adjustments and updates
• ✅ Size/color variant inventory management
• ✅ Daily entry generation for inventory changes
• ✅ Store report generation
• ✅ Category-based inventory filtering
• ✅ Auto-save functionality
• ✅ Inventory history tracking
• ✅ Store quantity synchronization

Related Controllers

• • inventorybybarcodeController.php - Barcode-based inventory
• • inventoryexpirationController.php - Product expiration tracking
• • storedetailController.php - Store quantity management
• • storereportController.php - Store reports

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. show() / Default Action - Inventory Entry Interface

Location: Line 139

Purpose: Display inventory counting interface with category and store filters

Process Flow:

1. Load YouTube tutorial links

2. Load category hierarchy for filtering

3. Load available stores

4. Assign session variables for display

5. Display via inventoryview/add.html template

Template Variables:

• • catData - Category hierarchy
• • storesData - Available stores
• • programsettingsdata - System settings
• • youtubes - Tutorial links

---

2. add() - Process Inventory Adjustments

Location: Line 341

Purpose: Process multiple inventory quantity adjustments and generate reports

Function Flow:

1. Loop through submitted inventory items ($_POST['itr'])

2. For each item:

- Extract new quantity, product ID, store ID

- Handle size/color variants if present

- Update store quantities

- Calculate quantity differences

- Generate store reports

- Create daily entries

3. Sync with online store if applicable

Key Variables:

• • $newQty - Updated quantity
• • $oldQty - Previous quantity
• • $productId - Product identifier
• • $storeid - Store location
• • $sizeId, $colorId - Variant identifiers

Quantity Change Detection:

if ($oldQty > $newQty) {
    $status = "بالنقص";  // Decrease
    $actualQty = $oldQty - $newQty;
    $type = 1;
} else if ($oldQty < $newQty) {
    $status = "بالزيادة";  // Increase
    $actualQty = $newQty - $oldQty;
    $type = 0;
} else {
    $status = "لم يتغير";  // No change
    $type = 0;
}

---

3. doInventoryDailyEntry() - Accounting Entry Generation

Location: Line 440

Purpose: Generate accounting entries for inventory adjustments

Function Signature:

function doInventoryDailyEntry($storeId, $productId, $quantity, $newQty, $type, $storeReport)

Process Flow:

1. Get store and product data

2. Determine cost price based on system settings

3. Calculate total cost impact

4. Create debit/credit entries:

- Increase (type=0): Debit store account, Credit inventory variance

- Decrease (type=1): Debit inventory variance, Credit store account

5. Insert daily entry with linking

Cost Price Options:

• • first - Original buy price
• • last - Last purchase price
• • mean - Average buy price
• • last_discount - Last price with discount
• • mean_discount - Average price with discount
• • tax - Last price with tax
• • mean_tax - Average price with tax

---

4. autosave() - Single Item Auto-Save

Location: Line 510

Purpose: Save individual inventory adjustments via AJAX

Similar to add() but processes single item:

• • Handles one product adjustment
• • Immediate quantity update
• • Real-time store report generation
• • Used for progressive inventory entry

---

5. invReport() Action - Inventory Reports

Location: Line 202

Purpose: Generate inventory adjustment reports with filtering

Search Parameters:

• • storeId - Filter by store
• • productCatId - Filter by category
• • product - Filter by specific product
• • from/to - Date range filtering

Query Building:

$queryString = '';
if ($storeId > 0) {
    $queryString .= " and storereport.storeid=$storeId ";
}
if ($product > 0) {
    $queryString .= " and storereport.productid=$product ";
} else if ($productCatId > 0) {
    // Get all subcategories and products
    getAllSubCat($productCatId, 1);
    $productsOfCat = $ProductEX->queryByProductCatIdIn($catsIDS);
    $IDS = '0';
    foreach ($productsOfCat as $value) {
        $IDS .= ',' . $value->productId;
    }
    $queryString .= " and storereport.productid in ($IDS) ";
}

---

6. getCategoryChilds() - Category Hierarchy

Location: Line 284

Purpose: Load category tree structure for filtering

Returns: Array containing parent object and children array

---

7. getStores() - Store List

Location: Line 334

Purpose: Load active stores for selection

Returns: Array of store objects with conditions = 0

---

8. getAllSubCat() - Recursive Category Traversal

Location: Line 605

Purpose: Recursively get all subcategories for filtering

Parameters:

• • $catid - Starting category ID
• • $mode - 1=all subcats, 2=last level only

---

🔄 Workflows

Workflow 1: Physical Inventory Count Process

---

Workflow 2: Size/Color Variant Handling

---

🌐 URL Routes & Actions

Inventory Entry (do=add):

• • itr - Number of items being processed
• • newQty{N} - New quantity for item N
• • productId{N} - Product ID for item N
• • storeid{N} - Store ID for item N
• • oldQty{N} - Previous quantity for item N

Auto-save (do=autosave):

• • Same as add but for single item

Reports (do=invReport):

• • storeId - Store filter (optional)
• • productCatId{level} - Category filter (optional)
• • product - Product filter (optional)
• • from/to - Date range (optional)

---

🧮 Calculation Methods

Quantity Difference Calculation

if ($oldQty > $newQty) {
    $actualQty = $oldQty - $newQty;  // Shortage
    $type = 1;  // Decrease
} else if ($oldQty < $newQty) {
    $actualQty = $newQty - $oldQty;  // Overage
    $type = 0;  // Increase
}

Cost Impact Calculation

$productData = R::getRow('select ' . $priceColName . ' as price from product where productId=' . $productId);
$productCost = $productData['price'] * $quantity;

Size/Color Quantity Aggregation

// Update parent product from sum of all variants
$storeDetailExt->updateQuantityWithSumChild(
    $storeDetailData->storedetailid, 
    $_SESSION['userid'], 
    date("Y-m-d"), 
    0, 
    $storeid, 
    $productid
);

---

🔒 Security & Permissions

Authentication Required

• • All actions require valid session
• • User authentication checked via ../public/authentication.php

Session Variables Used

• • $_SESSION['userid'] - Current user ID for tracking
• • $_SESSION['storenegative'] - Display setting for negative quantities
• • $_SESSION["serialarray"] - Serial number tracking

Input Validation

• • Numeric casting for quantities and IDs
• • Product ID validation for size/color format
• • Store and product existence validation

---

📊 Performance Considerations

Database Optimization

1. Batch Processing: Processes multiple items in single transaction

2. Indexed Queries: Uses primary keys for quick lookups

3. Minimal Queries: Efficient update patterns

Memory Management

• • Processes items iteratively to avoid large memory usage
• • Cleans up variables after processing

Potential Bottlenecks

• • Large inventory counts with many variants
• • Daily entry generation for high-value items
• • Category traversal for large hierarchies

---

🐛 Common Issues & Troubleshooting

1. Size/Color Quantity Sync Issues

Issue: Parent product quantity doesn't match variant totals

Cause: Failed updateQuantityWithSumChild() call

Debug:

SELECT p.productName, 
       sd.productquantity as parent_qty,
       SUM(scsd.quantity) as variant_total
FROM product p
JOIN storedetail sd ON sd.productid = p.productId  
LEFT JOIN sizecolorstoredetail scsd ON scsd.productid = p.productId
WHERE p.productId = [ID]
GROUP BY p.productId;

2. Daily Entry Creation Failures

Issue: Accounting entries not created for inventory adjustments

Cause: Missing store tree ID or invalid cost price setting

Fix:

-- Check store tree mapping
SELECT storeId, storeName, treeId FROM store WHERE storeId = [ID];

-- Verify cost price setting
SELECT dailyEntryCostprice FROM programsettings WHERE programsettingsid = 1;

3. Negative Quantity Issues

Issue: System allows negative quantities

Cause: No validation on quantity entry

Fix: Add client-side and server-side validation for minimum quantities

4. Category Filtering Not Working

Issue: Category filter returns no products

Cause: Recursive category traversal issues

Debug:

-- Check category hierarchy
SELECT productCatId, productCatName, productCatParent 
FROM productcat 
WHERE productCatId = [CATID] OR productCatParent = [CATID];

---

🧪 Testing Scenarios

Test Case 1: Basic Inventory Adjustment

1. Navigate to inventory controller
2. Select a store and product
3. Enter new quantity different from current
4. Submit inventory adjustment
5. Verify quantity updated in storedetail table
6. Check store report entry created
7. Confirm daily entry generated

Test Case 2: Size/Color Variant Adjustment

1. Select product with size/color variants
2. Adjust quantity for specific variant
3. Verify sizecolorstoredetail updated
4. Check parent product quantity recalculated
5. Confirm variant totals match parent

Test Case 3: Category-Based Inventory

1. Select category filter
2. Verify all products in category displayed
3. Include subcategory products
4. Test recursive category inclusion

Test Case 4: Auto-Save Functionality

1. Enter quantity for single product
2. Trigger auto-save (AJAX)
3. Verify immediate quantity update
4. Check real-time report generation
5. Confirm no page reload required

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • inventorybybarcodeController.md - Barcode inventory
• • inventoryexpirationController.md - Expiration tracking
• • storedetailController.md - Store management
• • dailyentry.php - Accounting integration

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur