Store Detail Price Controller Documentation

File: /controllers/storedetailpriceController.php

Purpose: Product inventory price list reporting and analysis system

Last Updated: December 20, 2024

Total Functions: 15+

Lines of Code: ~1,133

---

📋 Overview

The Store Detail Price Controller is a specialized inventory reporting module that provides detailed product price analysis and inventory valuation capabilities. It handles:

• • Product price list reports with multiple price levels
• • Inventory valuation using different costing methods
• • Store-based product quantity analysis
• • Category-based product filtering
• • Multi-level product pricing displays
• • Product image and description reporting
• • Barcode/parcode generation and display
• • Quantity filtering and zero-stock options
• • Discount calculations and application
• • Comprehensive inventory pricing reports

Primary Functions

• ✅ Generate product price lists
• ✅ Inventory valuation calculations
• ✅ Store-based product filtering
• ✅ Category-based product grouping
• ✅ Multi-level pricing displays (price4-price13)
• ✅ Quantity range filtering
• ✅ Zero stock hiding options
• ✅ Product image management
• ✅ Barcode/parcode handling
• ✅ Discount application logic

Related Controllers

• • storedetailController.php - Basic inventory operations
• • productController.php - Product management
• • storeController.php - Store management
• • productReportsController.php - Product reporting

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. show() / Default Action - Main Price Report

Location: Line 129

Purpose: Generate comprehensive product price list with filtering options

Function Signature:

// Triggered when: do=show or empty $do
if ($do == "show" || empty($do))

Process Flow:

1. Load store data for dropdown selection

2. Get category hierarchy for filtering

3. Process POST parameters for filtering criteria

4. Call show() function with filters

5. Display results via showprice.html template

Features:

• • Store selection filtering
• • Category hierarchical filtering
• • Product selection options
• • Custom column selection
• • Price calculation options

---

2. show() - Core Report Generation Logic

Location: Line 291

Purpose: Build detailed product price report with customizable columns

Function Signature:

function show()

Process Flow:

1. Parse request parameters for filters and display options

2. Build dynamic SELECT columns based on user choices

3. Construct WHERE clause with filters:

- Product ID filter

- Store ID filter

- Category ID filter (with subcategories)

- Quantity range filters

- Zero quantity hiding

4. Execute query via queryWithqueryStringprice()

5. Process each product record:

- Apply inventory valuation method

- Calculate product paths/categories

- Apply discount calculations

- Generate barcodes

6. Return data array and sum value

Dynamic Column Building:

if (isset($productSellUnitPrice) && $productSellUnitPrice == '1') {
    $str2 .= ',product.productSellUnitPrice';
}
// Repeated for all optional columns

Inventory Valuation Methods:

switch ($Programsettingdata->Inventoryevaluation) {
    case "first": $pro_price = (float) $myproduct->productBuyPrice; break;
    case "last": $pro_price = (float) $myproduct->lastbuyprice; break;
    case "mean": $pro_price = (float) $myproduct->meanbuyprice; break;
    case "last_discount": $pro_price = (float) $myproduct->lastbuyprice_withDiscount; break;
    case "mean_discount": $pro_price = (float) $myproduct->meanbuyprice_withDiscount; break;
    default: $pro_price = (float) $myproduct->overAllAveragePrice; break;
}

---

3. showByProductNameAndStore() - Product/Store Report

Location: Line 736

Purpose: Generate report for specific product in specific store

Function Signature:

function showByProductNameAndStore()

Process Flow:

1. Get product ID and store ID from request

2. Query storedetail by product and store

3. Apply inventory valuation calculations

4. Calculate product category paths

5. Return formatted data array

---

4. showBystoreName() - Store-Based Report

Location: Line 819

Purpose: Show all products in a specific store

Function Signature:

function showBystoreName()

Process Flow:

1. Get store ID from request

2. Query all products in specified store

3. Apply valuation calculations for each product

4. Return comprehensive store inventory report

---

5. showByProductCatNameAndStoreId() - Category/Store Report

Location: Line 902

Purpose: Filter products by category within specific store

Function Signature:

function showByProductCatNameAndStoreId()

Process Flow:

1. Get category and store IDs

2. Query products matching category in store

3. Process inventory valuations

4. Generate category-specific report

---

6. getCategoryChilds() - Category Hierarchy

Location: Line 1084

Purpose: Build hierarchical category structure for filtering

Function Signature:

function getCategoryChilds($parentid = 0)

Returns:

return array($parentobject, $childsArray);
// [0] = Parent category object
// [1] = Array of child categories

---

7. getAllSubCat() - Recursive Category Processing

Location: Line 1094

Purpose: Recursively get all subcategories for filtering

Function Signature:

function getAllSubCat($catid, $mode)

Modes:

• • mode = 1: Get all subcategories
• • mode = 2: Get only leaf-level categories

Global Variables Updated:

• • $catsIDS: Comma-separated category IDs
• • $lastLevelCatIDS: Array of leaf categories

---

8. getProductPath_recursive() - Product Category Path

Location: Line 1049

Purpose: Build full category path for product display

Function Signature:

function getProductPath_recursive($parentid, $categories)

Returns: String like "Electronics/Computers/Laptops"

---

🔄 Workflows

Workflow 1: Basic Price List Generation

---

🧮 Calculation Methods

Inventory Valuation Options

switch ($Programsettingdata->Inventoryevaluation) {
    case "first":           // Original purchase price
    case "last":            // Latest purchase price  
    case "mean":            // Average purchase price
    case "last_discount":   // Latest with discount
    case "mean_discount":   // Average with discount
    case "tax":             // Latest with tax
    case "mean_tax":        // Average with tax
    default:                // Overall average
}

Discount Processing

if ($selldiscount == 1 && $storedetail->selldiscount > 0) {
    if ($storedetail->discounttype == 0) { // Direct discount
        $storedetail->productSellAllPrice -= $storedetail->selldiscount;
    } elseif ($storedetail->discounttype == 1) { // Percentage
        $storedetail->productSellAllPrice -= 
            $storedetail->productSellAllPrice * ($storedetail->selldiscount / 100);
    }
}

Barcode Generation

if ($Programsettingdata->usedParcode == 1) { // ID-based
    $storedetail->parcode = "i" . addPreDigitsToNum($myproduct->productId);
} else { // Custom parcode
    $storedetail->parcode = $myproduct->parcode;
}

---

🌐 URL Routes & Actions

Main Price Report:

• • storeId - Store filter (-1 for all stores)
• • productCatId - Category filter (-1 for all categories)
• • productId - Specific product filter (-1 for all products)
• • Display options: productSellUnitPrice, productBuyPrice, etc.
• • Filters: hideZeroQuantity, quantityGreaterThan, quantityLessThan

---

🔒 Security & Permissions

User Permission Levels

$usergroupdata = $myUserGroupRecord->load($_SESSION['usergroupid']);
if ($usergroupdata->hidecat == 1) {
    // Show full product category paths
    $storedetail->productName = $storedetail->productName . '/' . $pathArr;
} else {
    // Show product name only
    $storedetail->productName = $storedetail->productName;
}

Input Sanitization

• • Category ID cast to integer: (int) $productCatId
• • Quantity filters cast to float: (float) $quantityGreaterThan
• • SQL injection prevented by DAO layer parameterization

---

📊 Performance Considerations

Database Optimization Tips

1. Required Indexes:

- storedetail(storeid, productid)

- product(productCatId)

- productcat(productCatParent)

2. Query Optimization:

- Dynamic column selection reduces data transfer

- Category hierarchy pre-built to avoid N+1 queries

- Price calculations done in PHP vs database

3. Memory Management:

- Large product catalogs may require pagination

- Category recursion depth should be monitored

Known Performance Issues

-- This recursive category lookup can be slow
SELECT * FROM productcat WHERE productCatParent = ?
-- Solution: Consider materialized category paths or LTREE

---

🐛 Common Issues & Troubleshooting

1. Missing Product Prices

Issue: Products show zero or null prices

Cause: Inventory valuation method references missing price fields

Debug:

SELECT productId, productBuyPrice, lastbuyprice, meanbuyprice 
FROM product WHERE productId = [ID];

2. Category Filter Not Working

Issue: Category filter doesn't include subcategories

Cause: getAllSubCat() not properly building category list

Fix: Check $catsIDS global variable building

3. Quantity Filters Ignored

Issue: Quantity range filters don't work

Cause: Float conversion or SQL syntax errors

Debug:

echo "Query: " . $queryString; // Check built query

---

🧪 Testing Scenarios

Test Case 1: Basic Price Report

1. Access controller without filters
2. Verify all stores and categories load
3. Check default column display
4. Confirm price calculations match settings

Test Case 2: Category Filtering

1. Select parent category
2. Verify subcategories included
3. Test leaf category selection
4. Check category path display

Test Case 3: Discount Application

1. Enable discount display option
2. Test both direct and percentage discounts
3. Verify calculations across all price levels
4. Check rounding accuracy

---

📚 Related Documentation

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

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur