Full Category Report Controller Documentation

File: /controllers/fullCategoryReport.php

Purpose: Comprehensive product category analysis with buy/sell transaction integration

Last Updated: December 20, 2024

Total Functions: 4+

Lines of Code: ~1,391

---

📋 Overview

The Full Category Report Controller provides comprehensive analysis of product categories including both purchase and sales transactions. It offers:

• • Complete category transaction analysis (buy + sell)
• • Multi-bill type integration (regular, optical, returns, combined)
• • Unit-based quantity and value aggregation
• • Store quantity and valuation reporting
• • Category vs. product level reporting
• • Price type filtering support
• • Inventory evaluation with multiple pricing methods

Primary Functions

• ✅ Comprehensive buy/sell transaction analysis
• ✅ Multi-source bill integration
• ✅ Unit-based quantity aggregation
• ✅ Category hierarchy analysis
• ✅ Store inventory valuation
• ✅ Price type filtering
• ✅ Last vs. first level category reporting
• ✅ Inventory evaluation methods

Related Controllers

• • freebuys.php - Free purchase analysis
• • freesales.php - Free sales analysis
• • buyBillController.php - Purchase management
• • sellbillController.php - Sales management

---

🗄️ Database Tables

Purchase Bill Tables

🔑 Key Functions

1. Default Action - Report Selection Interface

Location: Line 233

Purpose: Display search criteria interface for comprehensive category analysis

Process Flow:

1. Load available stores

2. Load category hierarchy

3. Display search interface via fullCategoryReport/show.html

2. show Action - Generate Comprehensive Report

Location: Line 248

Purpose: Process search criteria and generate detailed category analysis

Search Parameters:

• • datefrom/dateto - Date range with hour adjustment
• • storeId - Store filter
• • pricetype - Price type filter (wholesale/retail/half-wholesale)
• • productCatId - Category selection
• • searchtype - Search mode
• • proIsOptic - Optical products flag

Hour Adjustment Logic:

if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
    $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
    $datefrom = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($datefrom)));
}

3. getData() - Core Analysis Engine

Location: Line 405

Purpose: Process all transaction types and generate comprehensive analysis

Function Signature:

function getData($productCatId, $queryString, $queryString1, $queryString1R, $queryString1SR, 
                $queryString_sell, $queryString1_sell, $queryStringR_sell, $queryString1R_sell, $queryString1SR_sell)

Product Unit Data Class:

class productUnitData {
    public $catId;
    public $catName;
    public $productId;
    public $productName;
    public $unitId;
    public $unitName;
    
    // Purchase data
    public $amount = 0;         // Buy quantity
    public $price = 0;          // Buy value
    public $amount_ret = 0;     // Buy return quantity
    public $price_ret = 0;      // Buy return value
    
    // Sales data
    public $amount_sell = 0;    // Sell quantity
    public $price_sell = 0;     // Sell value
    public $amount_ret_sell = 0; // Sell return quantity
    public $price_ret_sell = 0; // Sell return value
    
    // Inventory data
    public $productQuantity = 0;     // Current stock
    public $productLastPrice = 0;    // Unit cost
    public $storeQuantity = 0;       // Total store quantity
    public $storeQuantityPrice = 0;  // Total inventory value
}

4. Multi-Source Data Processing

Purchase Sources:

1. Optical Purchases (billsproductsbuy)

2. Regular Purchases (buybilldetail)

3. Purchase Returns (returnbuybilldetail)

4. Combined Purchase/Returns (buyandruternbilldetail)

Sales Sources:

1. Optical Sales (billsproducts)

2. Optical Returns (billsreturnproducts)

3. Regular Sales (sellbilldetail)

4. Sales Returns (returnsellbilldetail)

5. Combined Sales/Returns (sellandruternbilldetail)

5. Unit Aggregation Logic

Purpose: Aggregate quantities and values by product and unit combination

// For each transaction:
if (isset($allDataArr[$productid])) {
    if (in_array($unitid, $AllDataIndexArr[$productid])) {
        // Unit exists - add to existing
        $key2 = array_search($unitid, $AllDataIndexArr[$productid]);
        $myproduct = $allDataArr[$productid][$key2];
        $myproduct->amount += $quantity;
        $myproduct->price += $value;
    } else {
        // New unit for existing product
        $myproduct = new productUnitData();
        // ... initialize and add
        array_push($allDataArr[$productid], $myproduct);
        array_push($AllDataIndexArr[$productid], $unitid);
    }
} else {
    // New product and unit
    // ... create new entries
}

6. Inventory Valuation

Location: Line 1206

Purpose: Calculate current inventory values using different pricing methods

Pricing Methods:

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

7. Category vs Product Level Reporting

Purpose: Switch between category totals and product details based on hierarchy level

$pro = $ProductEX->queryByProductCatIdLimited($productCatId);
$isLastLevelCatFlag = 1;
if ($pro->productId > 0 && $productCatId > 0) {
    // Last level - show products
    $isLastLevelCatFlag = 1;
} else {
    // Not last level - show category totals
    $isLastLevelCatFlag = 0;
    // Aggregate products by category
}

---

🔄 Workflows

Workflow 1: Comprehensive Category Analysis

---

🌐 URL Routes & Actions

Report Generation (do=show):

• • datefrom/dateto - Date range (optional)
• • storeId - Store filter (optional)
• • pricetype - Price type filter (0=wholesale, 1=retail, 2=half-wholesale)
• • level - Category hierarchy level
• • productCatId{level} - Category selection
• • productId - Specific product (alternative to category)
• • searchtype - Search mode
• • proIsOptic - Optical products flag

---

🧮 Calculation Methods

Unit Conversion for Regular Sales

$quantity = $value->sellbilldetailquantity;
$productunitId = $value->productunitid;
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
$productnumber = $productunitData->productnumber;
$quantity = $quantity * $productnumber;

Optical Products (No Unit Conversion)

// Optical products come in standard units
$unitData = $productUnitEX->getUnitDataOfUnityWithProductId($productid);
$unitid = 0;
$unitname = 'وحدة';
if (count($unitData) > 0) {
    $unitid = $unitData->unitid;
    $unitname = $unitData->conditions;
}

Category Aggregation

foreach ($allDataArr as $value11) {
    foreach ($value11 as $value) {
        $catId = $value->catId;
        $unitid = $value->unitId;
        
        // Aggregate by category and unit
        $mycat->price += $value->price;
        $mycat->amount += $value->amount;
        $mycat->price_sell += $value->price_sell;
        $mycat->amount_sell += $value->amount_sell;
        // ... etc for all fields
    }
}

---

🔒 Security & Permissions

Authentication Required

• • All actions require valid session
• • User authentication via ../public/authentication.php

Data Access Control

• • Store-based filtering supported
• • Price type access control
• • Category hierarchy permission filtering

---

📊 Performance Considerations

Optimization Features

1. Multi-Source Querying: Parallel processing of different bill types

2. Unit-Based Aggregation: Efficient indexing by product/unit combination

3. Category Level Switching: Optimized for different reporting levels

4. Inventory Calculation: Cached pricing method selection

Potential Bottlenecks

• • Large date ranges across multiple sources
• • Complex category hierarchy traversals
• • Inventory valuation calculations for many products
• • Unit conversion lookups

Query Optimization

-- Recommended indexes
CREATE INDEX idx_buybill_date_store ON buybill(buybilldate, buybillstoreid);
CREATE INDEX idx_sellbill_date_store ON sellbill(sellbilldate, sellbillstoreid);
CREATE INDEX idx_product_category ON product(productCatId);
CREATE INDEX idx_storedetail_product ON storedetail(productid);

---

🐛 Common Issues & Troubleshooting

1. Missing Transaction Data

Issue: Some bills not appearing in report

Cause: Different date field formats or timezone issues

Debug:

-- Check date field consistency
SELECT COUNT(*) FROM buybill WHERE DATE(buybilldate) = '2024-12-20';
SELECT COUNT(*) FROM sellbill WHERE DATE(sellbilldate) = '2024-12-20';
SELECT COUNT(*) FROM bills WHERE DATE(billdate) = '2024-12-20';

2. Unit Aggregation Issues

Issue: Incorrect quantity totals

Cause: Missing unit conversion data

Fix:

-- Check unit conversion completeness
SELECT p.productName, pu.productnumber, u.unitName
FROM product p
LEFT JOIN productunit pu ON pu.productid = p.productId  
LEFT JOIN unit u ON u.unitId = pu.unitid
WHERE pu.productunitid IS NULL;

3. Category vs Product Level Confusion

Issue: Wrong level of detail in report

Cause: Incorrect isLastLevelCatFlag determination

Debug: Check queryByProductCatIdLimited() result for category

4. Inventory Valuation Errors

Issue: Wrong inventory values

Cause: Missing price data or incorrect evaluation method

Fix:

-- Check price data availability
SELECT productId, productBuyPrice, lastbuyprice, meanbuyprice, generalPrice
FROM product 
WHERE productId = [PRODUCT_ID];

---

🧪 Testing Scenarios

Test Case 1: Multi-Source Integration

1. Create transactions in all bill types
2. Run comprehensive report for date range
3. Verify all sources included in totals
4. Check unit aggregation accuracy
5. Confirm optical vs. regular processing

Test Case 2: Category Level Switching

1. Test with high-level category (has subcategories)
2. Verify category-level aggregation
3. Test with product-level category  
4. Confirm product detail display
5. Check totals consistency

Test Case 3: Inventory Valuation Methods

1. Test with different evaluation methods
2. Verify pricing method application
3. Check inventory value calculations
4. Confirm consistency across units

Test Case 4: Store and Price Type Filtering

1. Test store-specific filtering
2. Verify price type filtering works
3. Check combined filter effects
4. Confirm accurate data isolation

---

📚 Related Documentation

• • freebuys.md - Free purchase analysis
• • freesales.md - Free sales analysis
• • buyBillController.md - Purchase management
• • sellbillController.md - Sales management
• • productController.md - Product management

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur