Store Parcode Controller Documentation

File: /controllers/storeparcodeController.php

Purpose: Generates beginning period inventory reports with pricing calculations and category hierarchies

Last Updated: December 21, 2024

Total Functions: 10

Lines of Code: ~511

---

📋 Overview

The Store Parcode Controller is a comprehensive inventory reporting module that provides beginning period stock analysis with advanced filtering and pricing calculations. It handles:

• • Beginning period inventory reporting across multiple stores
• • Product category hierarchy navigation with recursive path building
• • Multiple pricing strategies (last price, mean price, general price)
• • Store-based inventory filtering with user permissions
• • Product categorization with full path display
• • Dynamic query building for flexible reporting
• • Comprehensive inventory valuation calculations

Primary Functions

• ✅ Generate beginning period inventory reports
• ✅ Multi-store inventory analysis
• ✅ Category hierarchy navigation and display
• ✅ Multiple pricing strategy support
• ✅ User permission-based store filtering
• ✅ Product path construction for categorization
• ✅ Dynamic query building for complex filtering
• ✅ Comprehensive inventory valuation

Related Controllers

• • storedetailController.php - Store detail management
• • productController.php - Product management
• • categoryController.php - Product category management
• • inventoryController.php - Inventory operations

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action / show() - Report Interface

Location: Line 111

Purpose: Display the inventory report interface with store data and YouTube tutorials

Process Flow:

1. Load store data via loadStore()

2. Load YouTube tutorial links for user guidance

3. Parse request parameters for filtering

4. Display report interface template

5. Set up custom validation and display flags

Features:

• • Store selection interface
• • Tutorial integration
• • Parameter handling for productId, storeId, productCatId

---

2. loadProducts() - Product Data Loader

Location: Line 142

Purpose: Load all active product data for selection

Function Signature:

function loadProducts()

Returns: Array of product objects with condition filtering

---

3. loadStore() - Store Data Loader

Location: Line 153

Purpose: Load all active store data for selection

Function Signature:

function loadStore()

Returns: Array of store objects with condition filtering

---

4. loadProductCategories() - Category Hierarchy Builder

Location: Line 163

Purpose: Build complete product category hierarchy with path construction

Function Signature:

function loadProductCategories()

Process Flow:

1. Query all products via queryAllProducts()

2. For each product, get its category ID

3. Build recursive category path using fetch_recursive()

4. Assign category names and parent relationships to template

5. Return products data with category information

Template Variables Created:

• • names{N} - Category path for product N
• • parentId{N} - Parent category ID for product N
• • itr - Iterator count for template loops

---

5. fetch_recursive() - Recursive Category Path Builder

Location: Line 188

Purpose: Recursively build category path strings from leaf to root

Function Signature:

function fetch_recursive($parentid, $categories)

Process Flow:

1. Load category data for given parent ID

2. Add current category name to path string

3. If parent has its own parent, recursively call for parent

4. Build complete path with '/' separators

5. Return cleaned path string (remove trailing '/')

Path Construction Logic:

if (count($catData) > 0) {
    $categories .= $catData->productCatName . '/';
    $newParentId = $catData->productCatParent;
    
    if ($newParentId != 0) {
        $newParentName = $catData->parentName;
        $categories .= $newParentName . '/';
        fetch_recursive($newParentId, $categories);
    }
}

---

6. show() - Main Report Generation

Location: Line 207

Purpose: Generate comprehensive beginning period inventory report with multiple filtering options

Function Signature:

function show()

Process Flow:

1. Parse request parameters (productId, storeId, productCatId, order)

2. Build dynamic query string based on filters

3. Load store and product data for names/descriptions

4. Execute query via queryWithqueryString()

5. Calculate inventory values using configured pricing strategy

6. Build category paths for display

7. Return data array and sum value

Filter Building Logic:

$queryString = ' AND';

if (isset($productId) && $productId != '-1' && $productId != '') {
    $myprodactdata = $myProductRecord->load($productId);
    $message = $message . " للمنتج  :" . $myprodactdata->productName . "  ";
    $queryString .= ' storedetail.productId = ' . $productId . ' AND';
}

if (isset($storeId) && $storeId != '-1') {
    $queryString .= '  storedetail.storeid = ' . $storeId . ' AND';
    $mystordata = $myStoreRecord->load($storeId);
    $message = $message . "    والمخزن : " . $mystordata->storeName . "";
}

if (isset($productCatId) && $productCatId != '-1') {
    $queryString .= '  product.productCatId = ' . $productCatId . ' AND';
    $myProductCatData = $productCatDAO->load($productCatId);
    $message = $message . $myProductCatData->productCatName . " والمخزن " . $mystordata->storeName . "";
}

Pricing Strategy Implementation:

$Programsettingdata = $ProgramsettingDAO->load(1);
foreach ($storedetailData as $storedetail) {
    $myproduct = $myProductRecord->load($storedetail->productid);
    if ($Programsettingdata->lastprice == "0") {
        $storedetail->productBuyPrice = $myproduct->lastbuyprice;
    } else {
        $storedetail->productBuyPrice = $myproduct->meanbuyprice;
    }
    
    $productBuyPrice = $storedetail->productBuyPrice;
    $productQuantity = $storedetail->productquantity;
    $SumProductPrice = $productBuyPrice * $productQuantity;
    $sumValue = $SumProductPrice + $sumValue;
}

---

7. showByProductNameAndStore() - Product-Store Specific Report

Location: Line 319

Purpose: Generate report for specific product in specific store

Function Signature:

function showByProductNameAndStore()

Similar to show() but with hardcoded productId and storeId filtering

---

8. showBystoreName() - Store-Specific Report

Location: Line 368

Purpose: Generate report for all products in a specific store

Function Signature:

function showBystoreName()

Uses queryWithStoreId() for store-specific inventory data

---

9. showByProductCatNameAndStoreId() - Category-Store Report

Location: Line 419

Purpose: Generate report for specific product category in specific store

Function Signature:

function showByProductCatNameAndStoreId()

Uses queryWithProductCatAndStoreId() for filtered data

---

10. showAll() - Complete Inventory Report

Location: Line 469

Purpose: Generate comprehensive report for all inventory with ordering

Function Signature:

function showAll()

Features:

• • Complete inventory overview
• • Ordering support via queryWithOrder()
• • Category path construction for all products
• • Total value calculation across all inventory

---

11. getProductPath_recursive() - Enhanced Path Builder

Location: Line 496

Purpose: Build product category paths with enhanced recursive logic

Function Signature:

function getProductPath_recursive($parentid, $categories)

Difference from fetch_recursive():

• • Simplified path building
• • Direct return of recursive results
• • No parent name handling
• • Cleaner string termination

---

🔄 Workflows

Workflow 1: Beginning Period Report Generation

Workflow 2: Category Path Construction

---

🌐 URL Routes & Actions

Main Report (do=show or empty):

• • productId - Product filter (optional, -1 for all)
• • storeId - Store filter (optional, -1 for all)
• • productCatId - Category filter (optional, -1 for all)
• • order - Sort order specification (optional)

Filter Combinations Supported

1. All Products, All Stores: No filters specified

2. Specific Product: productId only

3. Specific Store: storeId only

4. Specific Category: productCatId only

5. Product in Store: productId + storeId

6. Category in Store: productCatId + storeId

7. Product in Category: productId + productCatId

8. Full Filter: productId + storeId + productCatId

---

🧮 Calculation Methods

Inventory Valuation

Pricing Strategy Selection:

$Programsettingdata = $ProgramsettingDAO->load(1);
if ($Programsettingdata->lastprice == "0") {
    $unitPrice = $product->lastbuyprice;    // Most recent purchase price
} else {
    $unitPrice = $product->meanbuyprice;    // Average purchase price
}

Line Value Calculation:

$productBuyPrice = $storedetail->productBuyPrice;
$productQuantity = $storedetail->productquantity;
$lineValue = $productBuyPrice * $productQuantity;

Total Inventory Value:

$totalValue = 0;
foreach ($inventoryItems as $item) {
    $lineValue = $item->unitPrice * $item->quantity;
    $totalValue += $lineValue;
}

Category Path Building

function buildCategoryPath($categoryId) {
    $path = "";
    $category = loadCategory($categoryId);
    
    while ($category && $category->productCatParent != 0) {
        $path = $category->productCatName . "/" . $path;
        $category = loadCategory($category->productCatParent);
    }
    
    return rtrim($path, "/"); // Remove trailing slash
}

---

🔒 Security & Permissions

Authentication

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

• • Standard session-based authentication required
• • All actions require valid user session

Input Validation

• • Basic parameter validation for numeric IDs
• • No explicit SQL injection protection implemented
• • Relies on DAO layer for query safety

Data Access Control

• • No user-specific data filtering implemented
• • All authenticated users can access all store/product data
• • Consider implementing store-based permissions for multi-tenant usage

Security Improvements Needed:

• • Add input sanitization for all parameters
• • Implement user-based store access controls
• • Add validation for date ranges and numeric parameters

---

📊 Performance Considerations

Database Optimization Tips

1. Critical Indexes Required:

- storedetail(productid, storeid) - For filtered queries

- product(productCatId) - For category filtering

- productcat(productCatParent) - For hierarchy navigation

- storedetail(storedetaildate) - For date-based queries

2. Query Optimization:

- Multiple separate queries for different filter combinations

- Consider consolidating into single dynamic query

- Category path building done in PHP vs database

3. Memory Management:

- Recursive category path building can be memory intensive

- No pagination for large inventory datasets

- Consider caching category paths

Known Performance Issues

Category Path Building:

• • Recursive function calls for each product category
• • Multiple database queries for deep category hierarchies
• • No caching of previously built paths

Large Dataset Handling:

• • No pagination implemented
• • All inventory loaded into memory simultaneously
• • Calculations performed in PHP rather than database

Optimization Recommendations:

// Cache category paths
$categoryPaths = [];
if (!isset($categoryPaths[$categoryId])) {
    $categoryPaths[$categoryId] = buildCategoryPath($categoryId);
}

// Use database aggregation instead of PHP loops
SELECT 
    SUM(storedetail.productquantity * product.lastbuyprice) as totalValue
FROM storedetail 
JOIN product ON storedetail.productid = product.productId
WHERE [filters]

---

🐛 Common Issues & Troubleshooting

1. Incorrect Inventory Values

Issue: Inventory totals don't match expected values

Cause: Wrong pricing strategy or missing product prices

Debug:

-- Check pricing configuration
SELECT lastprice FROM programsettings WHERE programsettingsid = 1;

-- Verify product prices
SELECT productId, productName, lastbuyprice, meanbuyprice, productBuyPrice
FROM product 
WHERE productId = [PRODUCT_ID];

2. Category Paths Not Displaying

Issue: Products show without full category paths

Cause: Recursive path building fails or missing parent relationships

Debug:

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

-- Find orphaned categories
SELECT * FROM productcat 
WHERE productCatParent NOT IN (SELECT productCatId FROM productcat)
AND productCatParent != 0;

3. Query Performance Issues

Issue: Reports load slowly or timeout

Cause: Missing indexes or inefficient query structure

Solutions:

-- Add required indexes
CREATE INDEX idx_storedetail_product_store ON storedetail(productid, storeid);
CREATE INDEX idx_product_category ON product(productCatId);
CREATE INDEX idx_category_parent ON productcat(productCatParent);

4. Empty Results with Valid Filters

Issue: No data returned despite valid filter parameters

Cause: Incorrect query string building or data integrity issues

Debug:

// Enable query debugging
echo "Query String: " . $queryString . "<br>";

// Check data exists
SELECT COUNT(*) FROM storedetail 
WHERE productid = [PRODUCT_ID] AND storeid = [STORE_ID];

---

🧪 Testing Scenarios

Test Case 1: Basic Inventory Report

1. Set no filters (all products, all stores)
2. Verify all active inventory appears
3. Check total values calculate correctly
4. Confirm category paths display properly
5. Validate pricing strategy is applied

Test Case 2: Multi-Level Category Filtering

1. Select parent category with multiple child levels
2. Verify all products in category hierarchy appear
3. Check category path construction is complete
4. Validate parent-child relationships work correctly

Test Case 3: Store-Specific Filtering

1. Select specific store
2. Verify only products in that store appear
3. Check store name appears in report message
4. Validate quantities are store-specific

Test Case 4: Pricing Strategy Validation

1. Change programsettings.lastprice value
2. Generate same report with different setting
3. Verify prices change according to strategy
4. Check totals recalculate correctly

Test Case 5: Complex Multi-Filter

1. Apply product + store + category filters simultaneously
2. Verify results match all criteria
3. Check message construction includes all filters
4. Validate no incorrect data leakage

---

📚 Related Documentation

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

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur