Profit Product Category Controller Documentation

File: /controllers/profitproductcatController.php

Purpose: Generates comprehensive profit analysis reports grouped by product categories

Last Updated: December 20, 2024

Total Functions: 18

Lines of Code: 1,400

---

📋 Overview

The Profit Product Category Controller provides category-level profit analysis with hierarchical category support and multi-level subcategory processing. It handles:

• • Category-based profit aggregation
• • Hierarchical category tree navigation
• • Multi-level subcategory analysis
• • Product-level profit calculations within categories
• • Optical products integration for specialized categories
• • Advanced discount reporting with category-specific views
• • Time zone adjustment for accurate reporting periods

Primary Functions

• ✅ Generate category-level profit reports
• ✅ Support hierarchical category structures
• ✅ Process all subcategories recursively
• ✅ Calculate individual product profits within categories
• ✅ Integrate optical product transactions
• ✅ Handle complex discount structures
• ✅ Support multiple buy price evaluation methods
• ✅ Generate discount-focused reports
• ✅ Apply time zone adjustments
• ✅ Process category tree navigation

Category Processing Features

• • Hierarchical category selection (up to 5 levels)
• • Automatic subcategory inclusion
• • Last-level category detection
• • Category tree navigation
• • Parent-child relationship handling

Related Controllers

• • profitproductController.php - Individual product analysis
• • profitdetailController.php - Overall profit analysis
• • profitreportController.php - Comprehensive profit reports

---

🗄️ Database Tables

Category & Product Tables

🔑 Key Functions

1. show() / Default Action - Category Profit Report

Location: Line 230

Purpose: Generate comprehensive profit analysis for a product category and all subcategories

Function Signature:

// Triggered when: do=show or empty $do
$level = filter_input(INPUT_POST, 'level');
$productCatId = filter_input(INPUT_POST, 'productCatId' . $level);
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];
$dis = filter_input(INPUT_GET, 'dis'); // Discount view flag

Process Flow:

1. Load category hierarchy data

2. Process multi-level category selection

3. Build category ID list including subcategories

4. Apply time zone adjustments

5. Call getProductInBillByDateAndCatId() for calculations

6. Display results via show.html or showdiscount.html

Category Tree Processing:

$level = filter_input(INPUT_POST, 'level');
$productCatId = filter_input(INPUT_POST, 'productCatId' . $level);
if (empty($productCatId) || $productCatId == -1) {
    $productCatId = filter_input(INPUT_POST, 'productCatId' . ($level - 1));
}

$catsIDS = '' . $productCatId;
if ($productCatId != '') {
    getAllSubCat($productCatId, 1); // mode = 1 get all sub cats
}

---

2. getProductInBillByDateAndCatId() - Core Category Analysis

Location: Line 303

Purpose: Calculate detailed profit metrics for all products in specified categories

Function Signature:

function getProductInBillByDateAndCatId($startDate, $endDate, $productcatId)

Process Flow:

1. Query all products in category tree

2. For each product, calculate:

- Sales revenue (regular + combined + optical)

- Return values (regular + combined + optical)

- Cost of goods sold

- Individual product profit

3. Aggregate totals across all products

4. Calculate final category profit

Product Processing Loop:

$h = 1;
foreach ($productIdData as $productIdDa) {
    $productId = $productIdDa->productid;
    
    // Load product data
    $productData = $myProductRecord->load($productId);
    $overAllAveragePrice = $productData->overAllAveragePrice;
    
    // Calculate sales data
    $sellPriceData = getTotalSellPriceByDateAndProductId($startDate, $endDate, $productId, $overAllAveragePrice);
    $sellPricePulsData = getTotalAditionalSellPriceByDateAndProductId($startDate, $endDate, $productId, $overAllAveragePrice);
    $sellOpticData = getTotalSellOpticByDateAndProductId($startDate, $endDate, $productId);
    
    // Aggregate totals
    $sellPrice = $sellPriceData[0] + $sellPricePulsData[0] + $sellOpticData[0];
    $quantityUnit = $sellPriceData[1] + $sellPricePlusData[1] + $sellOpticData[1];
    $discountPrice = $sellPriceData[3] + $sellPricePulsData[3] + $sellOpticData[3];
    
    // Calculate individual product profit
    $profitForOneProduct = round((($yy) - ($quantityForOneProduct * $xx)), 2);
    $finalProductcatProfit += $profitForOneProduct;
    
    $h++;
}

---

3. getAllSubCat() - Recursive Category Tree Processing

Location: Line 1362

Purpose: Recursively build list of all subcategories under a parent category

Function Signature:

function getAllSubCat($catid, $mode)
// mode = 1: get all sub categories
// mode = 2: get last level 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); // Recursive call
        } elseif ($mode == 2) {
            $childData = $productCatExt->queryByParentExt($data->productCatId);
            if (count($childData) > 0) {
                getAllSubCat($data->productCatId, $mode);
            } else {
                array_push($lastLevelCatIDS, $data->productCatId);
            }
        }
    }
}

Global Variables Used:

global $catsIDS; // Comma-separated list of category IDs
global $lastLevelCatIDS; // Array of leaf-level categories

---

4. getTotalSellPriceByDateAndProductId() - Sales Analysis

Location: Line 532

Purpose: Calculate sales revenue and costs for products in categories

Advanced Discount Handling:

// Product-level discount
$discountValue = $sellbilldetail->discountvalue;

// Bill-level discount calculation
if ($sellbillTotalBill == 0) {
    $sellbillTotalBill = 1;
}
$thirdStep = ($totalPrice / $sellbillTotalBill) * $sellbillDiscount;

// Combined discount calculation
$totalPriceDiscount = $discountValue + $thirdStep;
$sellPriceForOneProduct = $totalPriceBeforeDiscount - $totalPriceDiscount;
$discountPrice += $totalPriceDiscount;

Buy Price Selection (same as product controller):

switch ($buyPriceType) {
    case "first": $buyprice = (float) $sellbilldetail->buyprice; break;
    case "last": $buyprice = (float) $sellbilldetail->lastbuyprice; break;
    case "mean": $buyprice = (float) $sellbilldetail->meanbuyprice; break;
    case "last_discount": $buyprice = (float) $sellbilldetail->lastbuyprice_withDiscount; break;
    case "mean_discount": $buyprice = (float) $sellbilldetail->meanbuyprice_withDiscount; break;
    case "generalPrice": $buyprice = (float) $overAllAveragePrice; break;
    case "tax": $buyprice = (float) $sellbilldetail->lastbuyprice_withTax; break;
    case "mean_tax": $buyprice = (float) $sellbilldetail->meanbuyprice_withTax; break;
}

---

5. getTotalSellOpticByDateAndProductId() - Optical Category Products

Location: Line 1232

Purpose: Handle optical products within categories with specialized calculations

Optical-Specific Processing:

$quantity = $sellbilldetail->productno; // No unit conversion needed
$price = $sellbilldetail->productprice;
$totalPrice = $sellbilldetail->producttotalprice;
$sellbillTotalBill = $sellbilldetail->netbillvalue;

// Simplified discount calculation for optical products
$thirdStep = ($totalPrice / $sellbillTotalBill) * $sellbillDiscount;
$sellPriceForOneProduct = $totalPriceBeforeDiscount - $thirdStep;

---

6. loadProductCatNameById() - Category Name Builder

Location: Line 1203

Purpose: Build hierarchical category names with parent path

Function Signature:

function loadProductCatNameById($productCatId, $productcatName, $itr)

Recursive Path Building:

if ($itr == 1) {
    $productcatNamex = $productcatData->productCatName;
} elseif ($itr == 2) {
    $productcatNamex = $productcatData->productCatName . "/" . $productcatNamex;
}

if ($productcatData->productCatParent != 0) {
    return loadProductCatNameById($productcatData->productCatParent, $productcatNamex, 2);
}

Result Format: "Parent Category/Child Category/Product Name"

---

🔄 Workflows

Workflow 1: Category Profit Analysis

---

Workflow 2: Recursive Category Tree Processing

---

🌐 URL Routes & Actions

Category Analysis (do=show):

• • level - Category selection level (1-5) (POST)
• • productCatId{level} - Category ID for specified level (POST)
• • from - Start date (YYYY-MM-DD)
• • to - End date (YYYY-MM-DD)
• • buyPriceType - Price evaluation method (POST)

Category Selection Structure

Level 1: productCatId1 (Top level category)
Level 2: productCatId2 (Subcategory of Level 1)  
Level 3: productCatId3 (Subcategory of Level 2)
Level 4: productCatId4 (Subcategory of Level 3)
Level 5: productCatId5 (Subcategory of Level 4)

---

🧮 Calculation Methods

Category Profit Aggregation

$finalProductcatProfit = 0;
foreach ($productIdData as $productIdDa) {
    // Individual product calculations...
    $profitForOneProduct = round((($yy) - ($quantityForOneProduct * $xx)), 2);
    $finalProductcatProfit += $profitForOneProduct;
}

Discount Breakdown per Product

// Product-level discount tracking
$discountPrice = $sellPriceData[3] + $sellPricePulsData[3] + $sellOpticData[3];
$totalDiscountPrice += $discountPrice;

// Template assignment for individual products
$smarty->assign("discountPrice" . $h . '', $discountPrice);
$smarty->assign("profitForOneProduct" . $h . '', $profitForOneProduct);

Category Tree ID Building

$catsIDS = '' . $productCatId; // Start with selected category
getAllSubCat($productCatId, 1); // Add all subcategories
// Result: "1,5,12,23,45" (comma-separated category IDs)

Time Zone Adjustment

if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
    $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
    $endDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour', strtotime($endDate)));
    $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour', strtotime($startDate)));
} else {
    $endDate = $endDate . ' 23:59:59';
    $startDate = $startDate . " 00:00:00";
}

---

🔒 Security & Permissions

Authentication

• • Includes authentication.php for session validation
• • No specific role-based permissions for category access

Input Validation

• • Uses filter_input() for parameter sanitization
• • Category ID validation through database lookups
• • Date format validation

Security Considerations

• • No explicit permission checks for category-level financial data
• • Recursive function could potentially cause stack overflow
• • Missing rate limiting for complex category reports

---

📊 Performance Considerations

Database Optimization

1. Critical Indexes:

- productcat(productcatparent) for tree traversal

- product(productcatid) for category filtering

- sellbilldetail(sellbilldetailproductid, sysdate)

- Composite index on sellbilldetail(productid, date, conditions)

2. Performance Issues:

- Recursive category tree queries (N+1 problem)

- Multiple separate queries per product

- No result caching for category hierarchies

- Large category trees may cause timeouts

Memory Considerations

• • Recursive function calls may consume stack space
• • Large product lists loaded entirely into memory
• • No pagination for results
• • Multiple template variable assignments per product

Optimization Opportunities

-- Current: Recursive queries for each category
SELECT * FROM productcat WHERE productcatparent = ?

-- Optimized: Single hierarchical query with CTE
WITH RECURSIVE category_tree AS (
    SELECT productcatid FROM productcat WHERE productcatid = ?
    UNION ALL
    SELECT p.productcatid FROM productcat p
    INNER JOIN category_tree ct ON p.productcatparent = ct.productcatid
)
SELECT * FROM product WHERE productcatid IN (SELECT productcatid FROM category_tree)

---

🐛 Common Issues & Troubleshooting

1. Recursive Category Issues

Issue: Stack overflow or infinite loops in category tree

Causes:

• • Circular references in category hierarchy
• • Very deep category trees
• • Missing parent validation

Debug:

// Add depth limiting
function getAllSubCat($catid, $mode, $depth = 0) {
    if ($depth > 10) { // Limit recursion depth
        error_log("Category tree too deep: $catid");
        return;
    }
    // ... existing code ...
    getAllSubCat($data->productCatId, $mode, $depth + 1);
}

2. Missing Category Data

Issue: Some products not included in category reports

Causes:

• • Products assigned to deleted categories
• • NULL category assignments
• • Broken parent-child relationships

Fix:

-- Check for orphaned products
SELECT * FROM product WHERE productcatid NOT IN (SELECT productcatid FROM productcat);

-- Check for circular references
SELECT * FROM productcat p1 
JOIN productcat p2 ON p1.productcatid = p2.productcatparent 
WHERE p2.productcatid = p1.productcatparent;

3. Performance Degradation

Issue: Reports taking too long for large category trees

Solutions:

• • Add query result caching
• • Implement pagination
• • Optimize database indexes
• • Use materialized path for categories

4. Discount Calculation Errors

Issue: Category discount totals don't match sum of products

Cause: Rounding errors accumulating across products

Solution: Use decimal precision functions for financial calculations

---

🧪 Testing Scenarios

Test Case 1: Simple Category Profit

1. Create category with 2-3 products
2. Add sales transactions for each product
3. Run category report
4. Verify total matches sum of individual products

Test Case 2: Multi-Level Category Tree

1. Create parent category with subcategories
2. Add products to different levels
3. Create transactions across all levels
4. Verify report includes all subcategory products

Test Case 3: Recursive Category Handling

1. Create 5-level deep category hierarchy
2. Add products at various levels
3. Select top-level category for report
4. Verify all descendant products included

Test Case 4: Category with Optical Products

1. Create category with both regular and optical products
2. Add transactions for both product types
3. Run category report
4. Verify different calculation methods applied correctly

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • profitproductController.md - Individual product analysis
• • profitdetailController.md - Overall profit analysis
• • sellbillController.md - Sales operations

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur