# 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
- [x] Generate reports for products below minimum stock levels
- [x] Support multiple store/warehouse monitoring
- [x] Category-based product filtering and analysis
- [x] Compare product limits vs productstore limits
- [x] Recursive category hierarchy processing
- [x] User permission-based store access
- [x] Exclude service products from inventory reports
- [x] Flexible sorting and ordering options
- [x] Breadcrumb navigation integration
- [x] YouTube tutorial integration

### Related Controllers
- [storeController.php](storeController.md) - Store/warehouse management
- [productController.php](productController.md) - Product management
- [storedetailController.php](storedetailController.md) - Store inventory
- [productCatController.php](productCatController.md) - Category management
- [storereportController.php](storereportController.md) - Store reporting

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **storedetail** | Product inventory by store | storedetailid, storeid, productid, productquantity |
| **product** | Product master data | productid, productname, productcatid, productlimit, isService |
| **productstore** | Product store-specific limits | productstoreid, productid, storeid, productlimit |
| **store** | Store/warehouse master | storeid, storename, storeaddress |
| **productcat** | Product category hierarchy | productcatid, productcatname, productcatparent |

### System Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **youtubelink** | Tutorial video links | youtubelinkid, title, url |
| **breadcrumb** | Navigation breadcrumbs | level, title, url |

### Session-Based Access Control
| Session Variable | Purpose | Usage |
|------------------|---------|-------|
| **$_SESSION['searchinonestore']** | Store access mode | 0=multiple stores, 1=single store |
| **$_SESSION['storeid']** | Default store ID | Primary store for user |
| **$_SESSION['storeids']** | Accessible store list | Comma-separated store IDs |
| **$_SESSION['hidecat']** | Category display setting | 0=show categories, 1=hide categories |

---

## 🔑 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**:
```php
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**:
```php
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**:
```php
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**:
```php
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**:
```php
$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
```
┌─────────────────────────────────────────────────────────────┐
│              START: Select Store & Filters                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Validate User Permissions                               │
│     - Check store access mode (single/multiple)            │
│     - Load accessible stores list                          │
│     - Set default store if single mode                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Parse Filter Parameters                                 │
│     - Store ID selection                                    │
│     - Category hierarchy level                             │
│     - Product ID specific filter                           │
│     - Search type (individual vs category)                 │
│     - Limit type (product vs productstore)                 │
│     - Sort order preference                                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Build Query Conditions                                  │
│     - Store filter: IN (accessible_store_ids)              │
│     - Service filter: isService = 0                        │
│     - Category filter: productid IN (category_products)    │
│     - Apply sort order specification                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Category Hierarchy (if selected)               │
│     IF category selected:                                   │
│       ├─→ getAllSubCat(categoryId, 1) // Get all children  │
│       ├─→ Query products in category tree                  │
│       └─→ Add product IDs to filter                        │
│     IF search type = 1 (category summary):                 │
│       ├─→ getAllSubCat(categoryId, 2) // Get leaf cats     │
│       └─→ Process each leaf category separately            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Execute Inventory Analysis                              │
│     IF limit type = 'product':                             │
│       ├─→ Query storedetail JOIN product                   │
│       ├─→ Compare quantity vs product.productlimit         │
│       └─→ Filter: quantity < limit                         │
│     IF limit type = 'productstore':                        │
│       ├─→ Query storedetail JOIN productstore              │
│       ├─→ Compare quantity vs productstore.productlimit    │
│       └─→ Filter: quantity < store_specific_limit          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Process Results by Search Type                          │
│     IF search type = 0 (individual products):              │
│       ├─→ Return filtered product list                     │
│       └─→ Each product below its limit                     │
│     IF search type = 1 (category summary):                 │
│       ├─→ Group results by category                        │
│       ├─→ Sum quantities per category                      │
│       ├─→ Create category summary objects                  │
│       └─→ Return category aggregations                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  7. Generate Report Output                                  │
│     - Assign data to template variables                    │
│     - Include store and category information               │
│     - Add breadcrumb navigation                            │
│     - Display via productlimitreportview/show.html         │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Category Hierarchy Processing
```
┌─────────────────────────────────────────────────────────────┐
│               START: getAllSubCat(catid, mode)            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Query Immediate Children                                │
│     - Get direct subcategories of catid                    │
│     - Check if any children exist                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Based on Mode                                   │
│     IF mode = 1 (Get all subcategories):                   │
│       ├─→ Add each child to global catsIDS list           │
│       ├─→ Recursively call getAllSubCat() for each child  │
│       └─→ Build comprehensive ID list                      │
│     IF mode = 2 (Get leaf categories only):                │
│       ├─→ Check if child has its own children             │
│       ├─→ If no grandchildren: Add to lastLevelCatIDS     │
│       ├─→ If has grandchildren: Recursively process       │
│       └─→ Build leaf node list only                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Update Global Variables                                 │
│     MODE 1 results:                                         │
│       └─→ $catsIDS = "1,5,12,18,25,..." (all descendants) │
│     MODE 2 results:                                         │
│       └─→ $lastLevelCatIDS = [15, 22, 31] (leaf nodes)    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Return to Calling Function                              │
│     - Global variables updated with category lists         │
│     - Ready for product filtering queries                  │
│     - Category tree fully processed                        │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=show` | `showBystoreName()` | Main inventory limit report |
| `do=sucess` | Display template | Success message page |
| `do=error` | Display template | Error message page |

### Required Parameters by Action

**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
```php
// 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
```php
// 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
```php
// 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
```php
// 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
```php
// 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
```sql
-- 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**:
```sql
-- 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**:
```php
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**:
```sql
-- 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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [storeController.md](storeController.md) - Store management  
- [productController.md](productController.md) - Product management
- [storedetailController.md](storedetailController.md) - Store inventory
- [Database Schema Documentation](#) - Table relationships

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur