Product Limit Report Controller Documentation

File: /controllers/productlimitreportController.php

Purpose: Generates inventory limit reports showing products below minimum stock thresholds across stores

Last Updated: December 20, 2024

Total Functions: 3

Lines of Code: ~341

---

📋 Overview

The Product Limit Report Controller is a specialized reporting module that monitors inventory levels and alerts when products fall below minimum stock thresholds. It handles:

• • Store-specific inventory monitoring across multiple warehouses
• • Product minimum stock threshold analysis
• • Category-based product filtering and grouping
• • Multi-level product category hierarchy support
• • Product vs ProductStore limit comparison
• • User permission-based store access control
• • Breadcrumb navigation for report hierarchy
• • Order/sorting options for report presentation
• • Service product exclusion from reports

Primary Functions

• ✅ Generate reports for products below minimum stock levels
• ✅ Support multiple store/warehouse monitoring
• ✅ Category-based product filtering and analysis
• ✅ Compare product limits vs productstore limits
• ✅ Recursive category hierarchy processing
• ✅ User permission-based store access
• ✅ Exclude service products from inventory reports
• ✅ Flexible sorting and ordering options
• ✅ Breadcrumb navigation integration
• ✅ YouTube tutorial integration

Related Controllers

• • storeController.php - Store/warehouse management
• • productController.php - Product management
• • storedetailController.php - Store inventory
• • productCatController.php - Category management
• • storereportController.php - Store reporting

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. show() / Default Action - Main Inventory Limit Report

Location: Line 103 (do=show or empty)

Purpose: Generate comprehensive inventory limit report with store and category filtering

Process Flow:

1. Load authentication and YouTube tutorial links

2. Get category hierarchy data for filtering dropdown

3. Determine user store access permissions:

- Single store mode: Use assigned store only

- Multiple store mode: Use accessible stores list

4. Process filter parameters:

- Store ID selection

- Category hierarchy level selection

- Product ID specific filtering

- Search type (individual products vs category summaries)

5. Call showBystoreName() for data processing

6. Build breadcrumb navigation

7. Display via productlimitreportview/show.html

Features:

• • Multi-store inventory monitoring
• • Category hierarchy filtering
• • User permission-based access control
• • Flexible sorting options
• • Breadcrumb navigation integration
• • Service product exclusion
• • YouTube tutorial integration

User Permission Logic:

if ($_SESSION['searchinonestore'] == 0) {
    // Multiple store access
    if ($_SESSION['storeids'] == 0) {
        $stores = $StoreEX->queryByConditions(); // All stores
    } else {
        $stores = $StoreEX->queryByConditions(' and store.storeId in (' . $_SESSION['storeids'] . ')');
    }
} else {
    // Single store mode - use assigned store only
    $storedef = $StoreEX->queryByConditionsOne(' and store.storeId = ' . $_SESSION['storeid'] . ' ');
}

---

2. showBystoreName() - Core Inventory Analysis Logic

Location: Line 195

Purpose: Process inventory data and identify products below minimum thresholds

Function Signature:

function showBystoreName()

Process Flow:

1. Parse Filter Parameters:

- Store ID selection

- Sort order preference

- Limit type (product vs productstore limits)

- Category hierarchy level selection

- Search type (individual vs category summary)

2. Build Base Query Conditions:

- Store filtering based on user permissions

- Exclude service products (product.isService = 0)

- Category-based product filtering

3. Handle Search Types:

- Type 0 (Individual Products): Process each product separately

- Type 1 (Category Summary): Group by last-level categories

4. Execute Inventory Queries:

- Product Limit Mode: Use limits from product table

- ProductStore Limit Mode: Use store-specific limits from productstore table

5. Process Results:

- Compare current quantities against thresholds

- Filter to show only products below limits

- Build category summary objects for type 1 search

Search Type Processing:

if ($searchtype == 0) {
    // Individual product analysis
    if ($searchStoreLimitType == 'product') {
        $storedetailData = $myStoredetailEx->queryWithStoreIdAndProductandorderLimit_f($order, $queryString);
    } else if ($searchStoreLimitType == 'productstore') {
        $storedetailData = $myStoredetailEx->queryWithStoreIdAndProductandorderLimit_f2($order, $queryString);
    }
} elseif ($searchtype == 1) {
    // Category summary analysis
    foreach ($lastLevelCatIDS as $mycatId) {
        // Process each category individually
        // Aggregate results by category
        // Create summary objects
    }
}

Key Variables:

• • $queryString - Dynamic WHERE clause builder
• • $searchStoreLimitType - Limit source ('product' or 'productstore')
• • $searchtype - Report type (0=individual, 1=category summary)
• • $allDataArr - Final result array for category summaries

---

3. getAllSubCat() - Recursive Category Processor

Location: Line 302

Purpose: Recursively process product category hierarchies for filtering

Function Signature:

function getAllSubCat($catid, $mode)

Modes:

• • Mode 1: Get all subcategories (recursive descent)
• • Mode 2: Get only last-level categories (leaf nodes)

Process Flow:

1. Query immediate child categories for given category

2. Mode 1 (All Subcategories):

- Add each subcategory to global list

- Recursively process each subcategory

- Build comprehensive category ID list

3. Mode 2 (Last Level Only):

- Check if category has children

- If no children: Add to last level list

- If has children: Recursively check children

- Build list of leaf categories only

Recursive Logic:

$result = $productCatExt->queryByParentExt($catid);
if (count($result) > 0) {
    foreach ($result as $data) {
        if ($mode == 1) {
            $catsIDS .= "," . $data->productCatId;
            getAllSubCat($data->productCatId, $mode); // Recurse for all
        } elseif ($mode == 2) {
            $childData = $productCatExt->queryByParentExt($data->productCatId);
            if (count($childData) > 0) {
                getAllSubCat($data->productCatId, $mode); // Has children, recurse
            } else {
                array_push($lastLevelCatIDS, $data->productCatId); // Leaf node
            }
        }
    }
}

Global Variables Used:

• • $catsIDS - Comma-separated list of all category IDs
• • $lastLevelCatIDS - Array of leaf category IDs only

---

🔄 Workflows

Workflow 1: Store Inventory Limit Analysis

---

Workflow 2: Category Hierarchy Processing

---

🌐 URL Routes & Actions

Inventory Limit Report (do=show):

• • storeId - Store ID for filtering (optional, uses session defaults)
• • order - Sort order for results (optional)
• • searchStoreLimitType - Limit source: 'product' or 'productstore' (optional)
• • level - Category hierarchy level (POST parameter)
• • productCatId{level} - Category ID for specific level (POST parameter)
• • productId - Specific product filter (POST parameter, optional)
• • searchtype - Report type: 0=individual, 1=category summary (POST parameter)

Session-Based Parameters

• • $_SESSION['searchinonestore'] - Store access mode
• • $_SESSION['storeid'] - Default store ID
• • $_SESSION['storeids'] - Accessible store list
• • $_SESSION['hidecat'] - Category display preference

---

🧮 Calculation Methods

Store Access Control

// Determine user store access
if ($_SESSION['searchinonestore'] == 0) {
    // Multiple store mode
    if ($_SESSION['storeids'] != 0) {
        $queryString .= 'and storedetail.storeid in (' . $_SESSION['storeids'] . ') ';
    }
} else {
    // Single store mode
    $queryString .= 'and storedetail.storeid = ' . $_SESSION['storeid'] . ' ';
    $storeId = $_SESSION['storeid'];
}

Product Filtering

// Service product exclusion
$queryString .= ' and product.isService = 0 ';

// Category-based filtering
if (isset($productCatId) && $productCatId != -1) {
    $catsIDS = '' . $productCatId;
    getAllSubCat($productCatId, 1); // Get all subcategories
    $productsOfCat = $ProductEX->queryByProductCatIdIn($catsIDS);
    
    $IDS = '0,' . $productCatId;
    foreach ($productsOfCat as $value) {
        $IDS .= ',' . $value->productId;
    }
    $queryString .= 'and storedetail.productid in (' . $IDS . ') ';
}

Category Summary Aggregation

// Category summary mode processing
if ($searchtype == 1) {
    $lastLevelCatIDS = array();
    array_push($lastLevelCatIDS, $productCatId);
    getAllSubCat($productCatId, 2); // Get leaf categories
    
    foreach ($lastLevelCatIDS as $mycatId) {
        // Process each leaf category
        $catsIDS = '' . $mycatId;
        getAllSubCat($mycatId, 1); // Get all products in category
        
        $myObj = new Storedetail();
        $myObj->productName = $categoryName;
        foreach ($storedetailData as $value) {
            $myObj->productquantity += $value->productquantity; // Sum quantities
        }
        array_push($allDataArr, $myObj);
    }
}

---

🔒 Security & Permissions

User Authentication

// Required authentication
include_once("../public/authentication.php");

Store Access Control

• • Single Store Mode: User restricted to assigned store only
• • Multiple Store Mode: User can access specific store list
• • Admin Mode: Full store access when storeids = 0
• • Session-based permission enforcement

Input Sanitization

// Filter input parameters
$level = filter_input(INPUT_POST, 'level');
$productCatId = filter_input(INPUT_POST, 'productCatId' . $level);
$productId = filter_input(INPUT_POST, 'productId');
$searchtype = filter_input(INPUT_POST, 'searchtype');

Security Measures:

• • POST parameter filtering for form inputs
• • SQL injection prevention via DAO layer
• • Session validation for store access
• • Parameter validation before database queries

---

📊 Performance Considerations

Database Optimization

1. Required Indexes:

- storedetail(storeid, productid)

- product(productcatid, isService)

- productstore(productid, storeid)

- productcat(productcatparent)

2. Query Optimization:

- Store filtering reduces dataset size

- Service product exclusion early in query

- Category filtering with IN clauses

- Limit comparison at database level

3. Memory Management:

- Recursive category processing has depth limits

- Large store inventories may need pagination

- Category summary mode reduces memory usage vs individual products

Performance Tips

-- Optimize limit queries with proper indexing
CREATE INDEX idx_storedetail_limit ON storedetail(storeid, productid, productquantity);
CREATE INDEX idx_product_service ON product(isService, productcatid);

-- Consider materialized views for complex category hierarchies
CREATE VIEW product_category_tree AS 
WITH RECURSIVE cat_tree AS (...)

---

🐛 Common Issues & Troubleshooting

1. No Products Below Limit

Issue: Report shows no results despite low inventory

Cause: Limits not properly configured or wrong limit type selected

Debug:

-- Check product limits configuration
SELECT productname, productlimit, isService 
FROM product 
WHERE productcatid = [CATEGORY_ID];

-- Check productstore limits
SELECT ps.productlimit, p.productname, s.storename
FROM productstore ps
JOIN product p ON ps.productid = p.productid  
JOIN store s ON ps.storeid = s.storeid;

2. Store Access Issues

Issue: User cannot see expected stores

Cause: Session permissions not properly configured

Debug:

echo "Search Mode: " . $_SESSION['searchinonestore'] . "<br>";
echo "Store ID: " . $_SESSION['storeid'] . "<br>";
echo "Store IDs: " . $_SESSION['storeids'] . "<br>";

3. Category Recursion Problems

Issue: Category filtering returns too many/few products

Cause: Infinite recursion or incorrect hierarchy

Fix:

-- Check for circular references
SELECT * FROM productcat WHERE productcatid = productcatparent;

-- Verify category tree depth
WITH RECURSIVE depth_check AS (
  SELECT productcatid, productcatparent, 1 as level
  FROM productcat WHERE productcatparent = 0
  UNION ALL
  SELECT c.productcatid, c.productcatparent, d.level + 1
  FROM productcat c
  JOIN depth_check d ON c.productcatparent = d.productcatid
  WHERE d.level < 20
)
SELECT MAX(level) FROM depth_check;

---

🧪 Testing Scenarios

Test Case 1: Basic Limit Report

1. Set product limits in product table
2. Ensure inventory quantities below limits
3. Run report for specific store
4. Verify only below-limit products appear
5. Check quantity accuracy against database

Test Case 2: Category Hierarchy Filtering

1. Create nested category structure (3+ levels)
2. Add products to various category levels
3. Set some products below limits
4. Test category filtering at different levels
5. Verify subcategory inclusion works correctly

Test Case 3: Store Permission Enforcement

1. Configure user with limited store access
2. Set session variables appropriately  
3. Run report and verify only accessible stores appear
4. Test both single and multiple store modes

Test Case 4: ProductStore vs Product Limits

1. Set up products with both limit types
2. Configure different limits in product vs productstore
3. Test both limit type options
4. Verify correct limits are applied

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • storeController.md - Store management
• • productController.md - Product management
• • storedetailController.md - Store inventory
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur