Product Reports Controller Documentation

File: /controllers/productReportsController.php

Purpose: Generates comprehensive inventory reports with price analysis, category-based filtering, and inventory valuation

Last Updated: December 20, 2024

Total Functions: 12+

Lines of Code: ~890

---

📋 Overview

The Product Reports Controller is a specialized reporting module that provides detailed product and inventory analysis capabilities. It handles:

• • Product category reporting with all pricing tiers
• • Product buy price and evaluation reports
• • Inventory valuation with multiple pricing methods
• • Category-based product grouping and analysis
• • Date range filtering for product additions
• • Multi-price level reporting (12 different price levels)
• • Discount calculations for weighted pricing
• • Print-ready report formatting
• • YouTube tutorial integration

Primary Functions

• ✅ Generate product reports with all price levels
• ✅ Track product buy prices with inventory evaluation
• ✅ Category-based product analysis
• ✅ Weighted discount calculations
• ✅ Date range filtering for product additions
• ✅ Multiple inventory valuation methods
• ✅ Category hierarchy navigation
• ✅ Store-based product reporting
• ✅ Quantity summation across categories
• ✅ Tax and discount calculations

Related Controllers

• • productController.php - Product management
• • sellbillController.php - Sales operations
• • buyBillController.php - Purchase operations
• • storeController.php - Store management
• • productcatController.php - Category management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. reportWithAllPrices - Complete Product Price Report

Location: Line 108

Purpose: Generate comprehensive report showing all product prices and inventory valuation

Function Signature:

// Triggered when: do=reportWithAllPrices
$sellAllDiscount = (float) $_REQUEST['sellAllDiscount'];
$productCatId = $_REQUEST['productCatId'];
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];

Process Flow:

1. Load program settings for inventory evaluation method

2. Load YouTube tutorial links

3. Get product categories for dropdown

4. Process category selection:

- Single category: Call searchProductsWithBuyPrice()

- All categories: Call searchProductsWithBuyPriceforallSimple()

5. Apply weighted discount calculations

6. Display report or print version

Features:

• • 12 different price levels (price4-price13)
• • Multiple inventory evaluation methods
• • Weighted discount calculations
• • Date range filtering
• • Category hierarchy support
• • Print-ready formatting

---

2. reportWithBuyPrice - Buy Price Analysis Report

Location: Line 172

Purpose: Focus on purchase prices and category-based cost analysis

Function Signature:

// Triggered when: do=reportWithBuyPrice
$productCatId = $_REQUEST['productCatId'];

Process Flow:

1. Load all categories with parent relationships

2. Load YouTube tutorial links

3. Process category selection:

- Single category: Call searchProductsWithBuyPrice()

- All categories: Loop through all categories calling searchProductsWithBuyPriceforall()

4. Calculate totals across all categories

5. Display comprehensive buy price report

Features:

• • Category-by-category analysis
• • Total quantity and price summation
• • Parent category relationships
• • All categories combined view
• • Buy price evaluation methods

---

3. searchProductsWithBuyPrice() - Core Price Analysis Logic

Location: Line 321

Purpose: Main function for product price analysis with inventory valuation

Function Signature:

function searchProductsWithBuyPrice($sellAllDiscount = 0)

Process Flow:

1. Build date filter query if date range provided

2. Load program settings for inventory evaluation method

3. Query products by category with quantity summation

4. For each product:

- Apply inventory evaluation method (first/last/mean/tax)

- Calculate weighted discount pricing

- Apply category discounts (fixed or percentage)

- Process all 12 price levels

- Calculate running totals

5. Assign processed data to template

Inventory Evaluation Methods:

• • first - First purchase price
• • last - Last purchase price
• • mean - Average purchase price
• • last_discount - Last price after discount
• • mean_discount - Average price after discount
• • generalPrice - Overall average price
• • tax - Last price including tax
• • mean_tax - Average price including tax

Discount Processing:

if ($discounttype == 0) { // Fixed amount discount
    $productBuyPrice = $productBuyPrice - $buydiscount;
    $productSellAllPrice = $productSellAllPrice - $selldiscount;
} else { // Percentage discount
    $productBuyPrice = $productBuyPrice - (($buydiscount / 100) * $productBuyPrice);
    $productSellAllPrice = $productSellAllPrice - (($selldiscount / 100) * $productSellAllPrice);
}

---

4. searchProductsWithBuyPriceforallSimple() - All Products Analysis

Location: Line 509

Purpose: Simplified version for processing all products across categories

Function Signature:

function searchProductsWithBuyPriceforallSimple($sellAllDiscount = 0)

Process Flow:

1. Load program settings for evaluation method

2. Build date filter if provided

3. Query all products with quantity summation

4. Process each product with same logic as single category

5. Apply category discounts from loaded category data

6. Calculate grand totals across all products

Key Differences from Single Category:

• • No category filter in main query
• • Loads category data for each product individually
• • Processes all products in single operation
• • More efficient for "all categories" reports

---

5. searchProductsWithBuyPriceforall() - Category Loop Processor

Location: Line 686

Purpose: Process products for a specific category when looping through all categories

Function Signature:

function searchProductsWithBuyPriceforall($productCatId, $i)

Process Flow:

1. Load program settings

2. Query products for specific category

3. Process each product with inventory evaluation

4. Apply category-specific discounts

5. Calculate category totals

6. Assign to indexed template variables (totalPrice{$i}, totalQuantity{$i})

7. Add to global running totals

Template Variable Pattern:

$smarty->assign('totalPrice' . $i, $totalPrice);
$smarty->assign('totalQuantity' . $i, $totalQuantity);

---

6. getProductCats() - Category Navigation Builder

Location: Line 240

Purpose: Build category hierarchy for navigation and display

Function Signature:

function getProductCats()

Process Flow:

1. Query all products with category information

2. For each product:

- Get category parent hierarchy

- Build recursive category path

- Assign navigation variables to template

3. Create indexed category data for template loops

Recursive Category Building:

function fetch_recursive($parentid, $categories) {
    $catData = $productCatExt->getCategoryAndParentByCatId($parentid);
    $categories .= $catData->productCatName . '/';
    if ($newParentId != 0) {
        fetch_recursive($newParentId, $categories);
    }
}

---

7. getAllProducts() - Product Dropdown Data

Location: Line 306

Purpose: Load product list with category names for selection dropdowns

Function Signature:

function getAllProducts()

Process Flow:

1. Query all products with extended category information

2. Build full product name with category path

3. Return array for dropdown population

Product Name Construction:

$productFullName = $pro->productCatName . '/' . $pro->productName;

---

8. showProductsWithBuyPrice() - Basic Display Function

Location: Line 827

Purpose: Simple product display with buy price analysis (appears unused)

Note: This function appears to be legacy code and is not actively used in the current workflow.

---

🔄 Workflows

Workflow 1: Complete Product Price Report Generation

---

Workflow 2: Buy Price Category Analysis

---

🌐 URL Routes & Actions

Complete Price Report (do=reportWithAllPrices):

• • sellAllDiscount - Additional discount percentage to apply
• • productCatId - Category ID (-1 for none, "all" for all categories)
• • from - Start date for product additions (optional)
• • to - End date for product additions (optional)

Buy Price Report (do=reportWithBuyPrice):

• • productCatId - Category ID (-1 for none, "all" for all categories)

Output Formats

Print Mode (when sellAllDiscount > 0):

• • Uses weightedDiscountReportPrint.html template
• • Optimized for printing with discount calculations

Standard Mode:

• • Uses reportWithAllPrices.html or reportWithBuyPrice.html
• • Interactive web display with navigation

---

🧮 Calculation Methods

Inventory Evaluation Calculation

switch ($pro_price) {
    case "first":
        $productBuyPrice = (float) $pro->productBuyPrice;
        break;
    case "last":
        $productBuyPrice = (float) $pro->lastbuyprice;
        break;
    case "mean":
        $productBuyPrice = (float) $pro->meanbuyprice;
        break;
    case "last_discount":
        $productBuyPrice = (float) $pro->lastbuyprice_withDiscount;
        break;
    case "mean_discount":
        $productBuyPrice = (float) $pro->meanbuyprice_withDiscount;
        break;
    case "tax":
        $productBuyPrice = (float) $pro->lastbuyprice_withTax;
        break;
    case "mean_tax":
        $productBuyPrice = (float) $pro->meanbuyprice_withTax;
        break;
    default:
        $productBuyPrice = (float) $pro->overAllAveragePrice;
        break;
}

Weighted Discount Processing

$pro->weightedDiscount = (float) ($pro->weightedDiscount - $sellAllDiscount);
$pro->weightedDiscountBuyPrice = (float) $pro->lastbuyprice;
$pro->weightedDiscountPer = 1 - ($pro->weightedDiscount / 100);
$pro->weightedDiscountBuyPriceAfterDisc = round(($pro->weightedDiscountBuyPrice * $pro->weightedDiscountPer), 2);

Category Discount Application

// Fixed amount discount
if ($discounttype == 0) {
    $productBuyPrice = $productBuyPrice - $buydiscount;
    $productSellAllPrice = $productSellAllPrice - $selldiscount;
    // Apply to all 12 price levels
}
// Percentage discount
else {
    $productBuyPrice = $productBuyPrice - (($buydiscount / 100) * $productBuyPrice);
    $productSellAllPrice = $productSellAllPrice - (($selldiscount / 100) * $productSellAllPrice);
    // Apply to all 12 price levels
}

Total Value Calculation

$totalPrice += $pro->sumProductQuantity * $productBuyPrice;
$totalQuantity += $pro->sumProductQuantity;

---

🔒 Security & Permissions

User Permission Levels

$userGroup = $userGroupDAO->load($_SESSION['usergroupid']);
$smarty->assign("userGroup", $userGroup);

Permission Checks:

• • User group permissions control access to reports
• • Session validation through authentication.php
• • User-specific settings from program configuration

Input Sanitization

$sellAllDiscount = (float) $_REQUEST['sellAllDiscount'];
$productCatId = $_REQUEST['productCatId'];
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];

Security Measures:

• • Type casting for numeric values
• • Date validation before SQL inclusion
• • SQL injection prevented by DAO layer parameterized queries
• • Category ID validation against existing categories

---

📊 Performance Considerations

Database Optimization Tips

1. Indexes Required:

- product(productcatid, productdate)

- productcat(productcatparent)

- storedetail(storedetailproductid)

- buypriceshistorybook(productid)

2. Query Optimization:

- Date filtering for product additions: product.productDate >= ? AND product.productDate <= ?

- Category filtering with parent relationships

- Quantity summation queries with proper GROUP BY

3. Memory Management:

- Large product catalogs may require pagination

- Category loop processing can be memory intensive

- Template variable cleanup for multiple categories

Known Performance Issues

-- This query can be slow for large product catalogs
SELECT product.*, SUM(storedetail.storedetailproductquantity) as sumProductQuantity
FROM product 
LEFT JOIN storedetail ON product.productid = storedetail.storedetailproductid
WHERE product.productcatid = ?
GROUP BY product.productid;

-- Solution: Add covering index
CREATE INDEX idx_product_cat_date ON product(productcatid, productdate, productid);

---

🐛 Common Issues & Troubleshooting

1. Missing Price Data

Issue: Products show zero or null prices

Cause: Missing buypriceshistorybook entries or incorrect evaluation method

Debug:

SELECT p.productname, p.productbuysprice, 
       bph.buyprice, bph.buypricewithindiscount
FROM product p
LEFT JOIN buypriceshistorybook bph ON p.productid = bph.productid
WHERE p.productcatid = [CATEGORY_ID];

2. Incorrect Quantity Totals

Issue: Quantity summation doesn't match actual inventory

Cause: Missing or duplicate storedetail records

Debug:

SELECT productid, SUM(storedetailproductquantity) as total_qty
FROM storedetail 
WHERE storedetailproductid = [PRODUCT_ID]
GROUP BY storedetailproductid;

3. Category Hierarchy Issues

Issue: Category paths not displaying correctly

Cause: Circular references or missing parent categories

Fix:

-- Check for circular references
WITH RECURSIVE cat_tree AS (
  SELECT productcatid, productcatname, productcatparent, 1 as level
  FROM productcat WHERE productcatparent = 0
  UNION ALL
  SELECT c.productcatid, c.productcatname, c.productcatparent, ct.level + 1
  FROM productcat c
  INNER JOIN cat_tree ct ON c.productcatparent = ct.productcatid
  WHERE ct.level < 10
)
SELECT * FROM cat_tree WHERE level > 5;

4. Discount Calculation Errors

Issue: Discount calculations producing negative prices

Cause: Discount values higher than base prices

Fix:

// Add validation before discount application
if ($discounttype == 0) { // Fixed discount
    $productBuyPrice = max(0, $productBuyPrice - $buydiscount);
} else { // Percentage discount
    if ($buydiscount <= 100) {
        $productBuyPrice = $productBuyPrice - (($buydiscount / 100) * $productBuyPrice);
    }
}

---

🧪 Testing Scenarios

Test Case 1: Basic Product Report

1. Select category with products
2. Verify all price levels display correctly
3. Check quantity summation accuracy
4. Confirm discount calculations
5. Validate total calculations

Test Case 2: Inventory Evaluation Methods

1. Set up product with multiple purchase prices
2. Test each evaluation method (first/last/mean/tax)
3. Verify calculations match expected values
4. Check discount applications

Test Case 3: Category Hierarchy

1. Create nested category structure
2. Add products to various levels
3. Test "all categories" report
4. Verify category totals accuracy
5. Check parent path generation

Test Case 4: Date Range Filtering

1. Add products on different dates
2. Set date range filters
3. Verify only products in range appear
4. Test edge cases (same date, empty range)

Debug Mode Enable

// Add at top of controller for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Debug evaluation method
echo "Evaluation Method: " . $pro_price . "<br>";

// Debug price calculations
echo "Buy Price: " . $productBuyPrice . "<br>";
echo "Quantity: " . $pro->sumProductQuantity . "<br>";
echo "Total Value: " . ($pro->sumProductQuantity * $productBuyPrice) . "<br>";

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • productController.php - Product management
• • sellbillController.md - Sales operations
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur