Sales Report of Clients by Product Controller Documentation

File: /controllers/salesreportofclientsbyproduct.php

Purpose: Generates client-focused sales reports with product-level analysis and profit calculations

Last Updated: December 21, 2024

Total Functions: 3

Lines of Code: ~972

---

📋 Overview

The Sales Report of Clients by Product Controller provides comprehensive client analysis with detailed product transaction tracking. It handles:

• • Client-based sales reporting with transaction history
• • Product-level sales analysis per customer
• • Multi-bill type processing (sales, returns, combined, optical bills)
• • Profit calculations with flexible pricing methods
• • Detailed discount and payment processing
• • Product unit conversion and quantity calculations
• • Client filtering and search capabilities
• • Real-time cost calculations using various buy price methods

Primary Functions

• ✅ Generate client-focused sales reports
• ✅ Product transaction tracking per client
• ✅ Multi-pricing method profit analysis
• ✅ Discount distribution calculations
• ✅ Product unit conversion handling
• ✅ Date range filtering with time adjustments
• ✅ Client type and area filtering
• ✅ Net quantity and value calculations

Related Controllers

• • sellbillController.php - Sales operations
• • clientReportsController.php - Client reporting
• • returnsellbillController.php - Sales returns
• • productController.php - Product management
• • clientController.php - Client management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. getData() - Main Report Generation Function

Location: Line 436

Purpose: Core function that processes client sales data across all bill types

Function Signature:

function getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, 
                $chosenProductPrice, $productCatId, $theStore)

Process Flow:

1. Initialize clientData class for result storage

2. Handle default date filtering (today's sales if no filters)

3. Query optical bills (bills, billsreturnproducts)

4. Query regular sales (sellbilldetail, returnsellbilldetail)

5. Query combined bills (sellandruternbilldetail)

6. Calculate quantities, discounts, and profits per client

7. Generate final totals and assign to template

Key Variables:

• • $allDataArr - Array of clientData objects with sales totals
• • $totalsObj - Grand total summary object
• • $existId - Array to track processed client IDs
• • $flag - Discount calculation flag

---

2. getAllSubCat() - Category Hierarchy Processing

Location: Line 933

Purpose: Recursively processes product category trees for filtering

Function Signature:

function getAllSubCat($catid, $mode)

Parameters:

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

Process Flow:

1. Query immediate children of parent category

2. Recursively process each child category

3. Build global comma-separated ID string

4. Handle both full tree and leaf-only modes

---

3. Default Action - Main Controller Logic

Location: Line 168

Purpose: Handles routing, parameter processing, and report orchestration

Process Flow:

1. Load search tools (categories, clients, stores, sellers)

2. Process POST parameters for filters

3. Build dynamic query strings for each bill type

4. Apply date, store, client, seller, and product filters

5. Handle optic mode and search type variations

6. Call getData() with constructed parameters

7. Assign results to Smarty template

---

🔄 Workflows

Workflow 1: Client Sales Analysis Report Generation

---

🌐 URL Routes & Actions

• • clientId - Client filter (-1 for all clients)
• • storeId - Store filter (-1 for all stores)
• • branchId - Branch filter (-1 for all branches)
• • datefrom - Start date (YYYY-MM-DD)
• • dateto - End date (YYYY-MM-DD)
• • searchtype - Report type (0=product focus, 1=general)

Optional Parameters

• • sellerid - Seller filter (-1 for all sellers)
• • userid - User filter (-1 for all users)
• • pricetype - Price level filter (0=wholesale, 1=retail, 2=half-wholesale)
• • chosenProductPrice - Pricing method (0-7)
• • productCatId{level} - Category hierarchy selection
• • productId - Specific product filter
• • proIsOptic - Optic mode indicator

---

🧮 Calculation Methods

Client Data Structure

class clientData {
    public $id;           // Client ID
    public $clientName;   // Client name
    public $soldNo = 0;   // Total quantity sold
    public $soldVal = 0;  // Total sale value
    public $returnNo = 0; // Total quantity returned
    public $returnVal = 0; // Total return value
    public $netNo = 0;    // Net quantity (sold - returned)
    public $netVal = 0;   // Net value (sales - returns)
    public $realCost = 0; // Real cost based on pricing method
    public $netProfit = 0; // Net profit (net value - real cost)
}

Discount Distribution Calculation

// Bill-level discount
$dicount = $value->parcode - $value->note; // Total - After discount

if ($dicount != 0) {
    // Get total pieces in bill for proportional distribution
    $billpecies = $sellbilldetailEX->queryBillNoOfPecies($value->sellbillid);
    $billNoOfProduct = $billpecies->note;
    
    // Distribute discount proportionally
    $theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
    $theDiscount -= $value->discountvalue; // Subtract line-item discount
}

Cost Calculation by Pricing Method

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

Optical System Discount Calculation

// Card payment fees (Mada system)
if ($theBill->card == 1) {
    if ($theBill->paymentnetworkid == 4) { // Mada
        $madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
        if ($madaData->totalCarry < 5000)
            $dicount = (7 * $madaData->totalCarry) / 1000;
        else
            $dicount = 40; // Fixed fee for large amounts
    } else {
        $dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
    }
}

// Additional discounts
$dicount += ($theBill->productstotalprice - $theBill->finalnetbillvalue);

---

🔒 Security & Permissions

Store Access Control

if ($user->userstoreid == 0) {
    // Admin user - can access any store
    $theStore = $storeId;
} else {
    // Regular user - restricted to assigned store
    $theStore = $user->userstoreid;
    $queryString1 .= 'and sellbill.sellbillstoreid = ' . $user->userstoreid . ' ';
}

Branch Access Control

if ($user->branchId == 0) {
    // Can access specified branch
    $queryString .= 'and bills.branchid = ' . $branchId . ' ';
} else {
    // Restricted to user's branch
    $queryString .= 'and bills.branchid = ' . $user->branchId . ' ';
}

---

📊 Performance Considerations

Database Optimization

1. Critical Indexes:

- sellbilldetail(sellbillid, sellbilldetailproductid)

- sellbill(sellbillclientid, sellbilldate, sellbillstoreid)

- client(clientid, typeclientid)

- productunit(productid, productunitid)

2. Query Patterns:

- Uses joins to reduce separate queries

- Implements efficient bill detail loading

- Proper date range filtering with indexes

3. Memory Management:

- Processes large client lists efficiently

- Uses associative arrays for fast lookups

- Avoids N+1 queries through batching

Performance Warnings

-- Inefficient category expansion for large hierarchies
-- Solution: Consider flattened category materialization

-- Multiple bill type queries could benefit from union optimization
-- Current approach prioritizes maintainability over raw speed

---

🐛 Common Issues & Troubleshooting

1. Missing Client Sales Data

Issue: Known client sales don't appear in report

Cause: Client type filtering or permission restrictions

Debug:

-- Check client type assignments
SELECT c.clientid, c.clientname, c.typeclientid, tc.typename 
FROM client c 
LEFT JOIN typeclient tc ON c.typeclientid = tc.typeclientid 
WHERE c.clientid = [CLIENT_ID];

2. Incorrect Quantity Calculations

Issue: Product quantities don't match bill details

Cause: Product unit conversion errors

Debug:

// Verify unit conversions
$productunitData = loadProductUnitWithProductAndUnit($productId, $unitId);
echo "Product Number: " . $productunitData->productnumber;
echo "Final Quantity: " . ($quantity * $productunitData->productnumber);

3. Profit Calculation Discrepancies

Issue: Profit margins appear incorrect

Cause: Wrong pricing method or missing cost data

Fix:

// Debug pricing method selection
echo "Chosen Price Method: " . $chosenProductPrice . "<br>";

// Check available cost data
echo "Buy Price: " . $value->buyprice . "<br>";
echo "Last Buy Price: " . $value->lastbuyprice . "<br>";
echo "Mean Buy Price: " . $value->meanbuyprice . "<br>";

---

🧪 Testing Scenarios

Test Case 1: Client Sales Accuracy

1. Create test client with known sales
2. Generate sales bills with specific products/quantities
3. Run client report for test period  
4. Verify quantities and values match source bills
5. Check discount distributions are correct

Test Case 2: Multi-Bill Type Integration

1. Create sales, returns, and combined bills for same client
2. Use different products in each bill type
3. Run comprehensive report
4. Verify net calculations (sales - returns)
5. Check no double-counting occurs

Test Case 3: Pricing Method Validation

1. Set up products with different cost prices
2. Create sales using various pricing methods
3. Run report with each method (0-7)
4. Verify profit calculations use correct cost basis
5. Confirm totals are mathematically consistent

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • clientReportsController.md - Client reporting
• • sellbillController.md - Sales operations
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur