Sales Report Category Store Controller Documentation

File: /controllers/salesreportcatstore.php

Purpose: Generates sales reports grouped by product categories and stores with profit analysis

Last Updated: December 21, 2024

Total Functions: 4

Lines of Code: ~1,281

---

📋 Overview

The Sales Report Category Store Controller is a specialized reporting module that provides comprehensive sales analysis by product categories and stores. It handles:

• • Category-based sales reporting with hierarchical subcategory inclusion
• • Store-based sales comparison reporting
• • Profit calculations with multiple pricing methods
• • Sales vs returns analysis with net calculations
• • Detailed discount processing and VAT handling
• • Multi-bill type processing (regular sales, returns, combined bills)
• • Real-time cost calculation using various pricing strategies
• • Session-based search parameter caching for new tab functionality

Primary Functions

• ✅ Generate category-based sales reports
• ✅ Store-wise sales comparison
• ✅ Net profit calculations with multiple cost methods
• ✅ Hierarchical category tree processing
• ✅ Discount and VAT calculations
• ✅ Multi-pricing strategy support
• ✅ Session caching for cross-tab functionality
• ✅ Date range filtering with hour adjustments

Related Controllers

• • sellbillController.php - Sales operations
• • buyBillController.php - Purchase operations
• • returnsellbillController.php - Sales returns
• • productController.php - Product management
• • storeController.php - Store management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. getDataNew() - Main Report Generation Function

Location: Line 370

Purpose: Core function that processes all sales data and generates comprehensive reports

Function Signature:

function getDataNew($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, 
                   $chosenProductPrice, $searchtype, $productCatId, $theStore, $IDSOfProducts, 
                   $typeId, $servicesAreIncluded)

Process Flow:

1. Initialize productData class for result storage

2. Calculate VAT value for profit calculations

3. Process regular sales bills with R::getAll() queries

4. Process return bills and combined bills

5. Apply category/store filtering based on searchtype

6. Calculate real costs using chosen pricing method

7. Generate summary totals and category aggregations

Key Variables:

• • $vatValue - VAT multiplier (1 + VAT percentage)
• • $chosenProductPrice - Pricing method (0-7 different cost bases)
• • $searchtype - Report grouping (0=category, 1=store)
• • $allDataArr - Final result array of productData objects

---

2. getAllSubCat() - Category Hierarchy Processing

Location: Line 1229

Purpose: Recursively processes product category hierarchies to include all subcategories

Function Signature:

function getAllSubCat($catid, $mode)

Parameters:

• • $catid - Parent category ID
• • $mode - Processing mode (1=all subcats, 2=leaf level only)

Process Flow:

1. Query subcategories of given parent

2. Recursively process each child category

3. Build comma-separated ID string for SQL IN clauses

4. Handle both full tree and leaf-only modes

---

3. isCatChildToParent() - Category Parent Checking

Location: Line 1267

Purpose: Determines if a category is a child of a specified parent category

Function Signature:

function isCatChildToParent($catId, $parent)

Process Flow:

1. Query parent ID of given category

2. Check if matches target parent

3. Recursively check grandparent relationships

4. Set global flag when relationship found

---

4. Default Action - Main Controller Logic

Location: Line 199

Purpose: Handles URL routing, parameter processing, and report orchestration

Process Flow:

1. Load program settings and user permissions

2. Process POST parameters and session data

3. Handle "today's bills" quick filter

4. Build dynamic query strings for each bill type

5. Apply store, date, seller, and category filters

6. Call getDataNew() with constructed parameters

7. Assign results to Smarty template

---

🔄 Workflows

Workflow 1: Category-Based Sales Report Generation

---

🌐 URL Routes & Actions

• • storeId - Store filter (-1 for all stores)
• • productCatId - Category filter (-1 for all categories)
• • searchtype - Report grouping (0=category, 1=store)
• • datefrom - Start date (YYYY-MM-DD)
• • dateto - End date (YYYY-MM-DD)
• • sellerid - Seller filter (-1 for all sellers)
• • chosenProductPrice - Pricing method (0-7)

Optional Parameters

• • getTodayBills - Quick filter for today's sales (1=enabled)
• • search - Search type indicator for date processing

---

🧮 Calculation Methods

Profit Calculation by Pricing Method

switch ($chosenProductPrice) {
    case 0: // Buy price from bill
        $myproduct->realCost += $finalquantity * $value->buyprice * $vatValue;
        break;
    case 1: // Mean buy price
        $myproduct->realCost += $finalquantity * $value->meanbuyprice * $vatValue;
        break;
    case 2: // Last buy price  
        $myproduct->realCost += $finalquantity * $value->lastbuyprice * $vatValue;
        break;
    case 3: // Last buy price with discount
        $myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
        break;
    case 4: // Mean buy price with discount
        $myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
        break;
    case 5: // General/average price
        $myproduct->realCost += $finalquantity * $value->generalPrice * $vatValue;
        break;
    case 6: // Last buy price with tax
        $myproduct->realCost += $finalquantity * $value->lastbuyprice_withTax * $vatValue;
        break;
    case 7: // Mean buy price with tax
        $myproduct->realCost += $finalquantity * $value->meanbuyprice_withTax * $vatValue;
        break;
}

Discount Processing

// Get bill-level discount
$dicount = $value->sellbilldiscount;
if ($value->sellbilldiscounttype == 2) {
    // Percentage discount
    $dicount = ($value->sellbilldiscount / 100) * $value->parcode;
}

// Distribute discount proportionally to line item
if ($dicount != 0) {
    $theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
    $theDiscount = round($theDiscount, 2);
}

VAT Calculation

// Calculate VAT multiplier for cost calculations
$vatValue = 1 + ($Programsetting->vatValue / 100);

// Apply to cost calculation
$realCost = $finalquantity * $buyPrice * $vatValue;

---

🔒 Security & Permissions

User Store Access Control

if ($user->userstoreid == 0) {
    // Admin user - can select any store
    $theStore = $storeId;
    $queryString1 .= 'and sellbill.sellbillstoreid = ' . $storeId . ' ';
} else if ($_SESSION['searchinonestore'] == 0) {
    // Multi-store user
    if ($_SESSION['storeids'] != 0) {
        $queryString1 .= 'and sellbill.sellbillstoreid in (' . $_SESSION['storeids'] . ') ';
    }
} else {
    // Single store user
    $theStore = $_SESSION['storeid'];
    $queryString1 .= 'and sellbill.sellbillstoreid = ' . $_SESSION['storeid'] . ' ';
}

Authentication Requirements

• • User must be logged in (authentication.php included)
• • Store access controlled by user permissions
• • Seller filtering respects user hierarchy

---

📊 Performance Considerations

Database Optimization Tips

1. Critical Indexes:

- sellbill(sellbilldate, sellbillstoreid, sellerid)

- sellbilldetail(sellbillid, sellbilldetailproductid)

- productcat(productCatParent) for hierarchy queries

- product(productCatId) for category filtering

2. Query Optimization:

- Uses R::getAll() for efficient data retrieval

- Implements proper JOIN relationships

- Date filtering with time adjustment logic

- IN clauses for category hierarchy filtering

3. Memory Considerations:

- Large date ranges may process thousands of records

- Category hierarchy recursion depth should be monitored

- Product arrays built in memory for fast lookups

Known Performance Issues

-- Category hierarchy expansion can be expensive for deep trees
-- Solution: Consider materialized path or nested set model

-- Multiple bill type queries could be combined with UNION
-- Current separate queries allow for better debugging but impact performance

---

🐛 Common Issues & Troubleshooting

1. Missing Category Data

Issue: Products don't appear in category reports

Cause: Product category assignments missing or hierarchy broken

Debug:

-- Check products without categories
SELECT productId, productName FROM product WHERE productCatId IS NULL OR productCatId = 0;

-- Verify category hierarchy
SELECT productCatId, productCatName, productCatParent FROM productcat WHERE conditions = 0;

2. Incorrect Profit Calculations

Issue: Profit margins don't match expectations

Cause: VAT settings or pricing method selection issues

Debug:

// Check VAT configuration
$Programsetting = $ProgramsettingDAO->load(1);
echo "VAT Value: " . $Programsetting->vatValue . "%<br>";
echo "VAT Calculation: " . (1 + ($Programsetting->vatValue / 100));

// Verify pricing method data
echo "Chosen Price Method: " . $chosenProductPrice;

3. Store Filtering Not Working

Issue: All stores showing when specific store selected

Cause: User permission conflicts or session data issues

Fix:

// Debug user store permissions
echo "User Store ID: " . $user->userstoreid . "<br>";
echo "Search In One Store: " . $_SESSION['searchinonestore'] . "<br>";
echo "User Store IDs: " . $_SESSION['storeids'];

---

🧪 Testing Scenarios

Test Case 1: Category Report Accuracy

1. Create test products in specific category
2. Generate test sales bills with known quantities/prices
3. Run category report for test period
4. Verify quantities and amounts match source bills
5. Check profit calculations against manual calculations

Test Case 2: Store Comparison Report

1. Create sales in multiple stores
2. Use searchtype=1 for store-based grouping
3. Verify each store's totals are isolated correctly
4. Check that combined totals match overall sales

Test Case 3: Pricing Method Validation

1. Set up product with different cost prices
2. Create sales bill using known pricing method
3. Run report with each pricing method (0-7)
4. Verify cost calculations use correct price source
5. Confirm profit margins calculated properly

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • sellbillController.md - Sales operations
• • buyBillController.md - Purchase operations
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur