Clients With Products Report Controller Documentation

File: /controllers/clientsWithProductsReport.php

Purpose: Generates detailed sales and product transaction reports grouped by clients or products with comprehensive financial analysis

Last Updated: December 20, 2024

Total Functions: 4+

Lines of Code: ~1,062

---

📋 Overview

The Clients With Products Report Controller is a comprehensive reporting module that provides detailed analysis of product sales and returns by customer. It handles:

• Client-centric product transaction reports with sales/return breakdowns
• Product-centric client analysis showing which customers buy specific products
• Combined sales and return bill processing with discount calculations
• Optical products special handling with category integration
• Store and branch filtering for multi-location businesses
• Real-time profit/loss calculations with current inventory levels
• Category-based product filtering with hierarchical subcategory support
• Complex discount calculations for different payment methods (cash, card, Mada)

Primary Functions

✅ Generate client vs product sales matrix reports
✅ Track product performance by customer
✅ Calculate net quantities (sold - returned)
✅ Compute real costs and profit margins
✅ Handle multiple bill types (regular, optical, combined)
✅ Process complex discount structures
✅ Support category hierarchy filtering
✅ Store/branch specific reporting
✅ Current inventory integration
✅ Dual report modes (by client or by product)

Related Controllers

• sellbillController.php - Sales operations
• clientController.php - Customer management
• productController.php - Product management
• storedetailController.php - Inventory management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
billsproducts	Optical bills product details	billsproductsid, billid, productid, productno, producttotalprice
billsreturnproducts	Optical return products	billsreturnproductsid, returnbillid, productid, productno, producttotalprice
sellbilldetail	Regular sales bill details	sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice
returnsellbilldetail	Sales return details	returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity
sellandruternbilldetail	Combined sell/return details	sellandruternbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, selltype

### Master Tables (Referenced)

Table Name	Purpose	Key Columns
bills	Optical service bills	billid, clientid, billdate, card, paymentnetworkid, cardvalue, netdiscountpercent, productstotalprice, finalnetbillvalue
billsreturn	Optical return bills	billsreturnid, billid, date
sellbill	Regular sales bills	sellbillid, sellbillclientid, sellbilldate, sellbilltotalbill, sellbillaftertotalbill, sellbilldiscount, sellbilldiscounttype
returnsellbill	Sales return bills	returnsellbillid, returnsellbillclientid, returnsellbilldate, returnsellbilltotalbill
sellbillandrutern	Combined bills	sellbillid, sellbillclientid, sellbilldate, sellbillprice, returnsellbillprice
client	Customer master data	clientid, clientname
product	Product master data	productId, productName, productBuyPrice, isOptic

### Support Tables

Table Name	Purpose	Key Columns
productcat	Product categories	productCatId, productCatName, productCatParent
productunit	Product units/conversions	productunitid, productid, unitid, productnumber
storedetail	Current inventory levels	storedetailid, productid, storeid, storedetailquantity
user	User permissions	userid, username, userstoreid, branchId
youtubelink	Tutorial videos	youtubelinkid, title, url

---

🔑 Key Functions

1. Main Controller Logic - Dual Mode Report Generator

Location: Lines 137-351

Purpose: Process search parameters and generate either client-based or product-based reports

Function Signature:

// URL Parameters
$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto'); 
$clients_product = filter_input(INPUT_GET, 'page'); // 'products' or empty
$storeId = filter_input(INPUT_POST, 'storeId');
$branchId = filter_input(INPUT_POST, 'branchId');
$productCatId = filter_input(INPUT_POST, 'productCatId' . $level);
$productId = filter_input(INPUT_POST, 'productId');

Process Flow:

1. Load all search form data (categories, products, clients, stores, branches)

2. Build dynamic query strings for different bill types

3. Apply date, store, branch, seller, and user filters

4. Handle product/category filtering with hierarchy support

5. Choose report mode based on $clients_product parameter:

- Empty/not 'products': Generate client-based report (default)

- 'products': Generate product-based report

6. Loop through all clients or products and call respective data functions

7. Display results via appropriate Smarty templates

Key Features:

• Dual reporting modes (client vs product perspective)
• Category hierarchy traversal with getAllSubCat()
• Multi-store and multi-branch filtering
• User permission-based access control
• Optical product special handling (isOptic = 2)

---

2. getDataByClientId() - Client Sales Analysis

Location: Lines 378-695

Purpose: Generate comprehensive product sales data for a specific client

Function Signature:

function getDataByClientId($queryString, $queryString1, $queryStringR, 
    $queryString1R, $queryString1SR, $searchtype, $productCatId, $theStore, $clientid)

Process Flow:

1. Data Collection: Query 5 different bill types:

- billsproducts (optical sales)

- billsreturnproducts (optical returns)

- sellbilldetail (regular sales)

- returnsellbilldetail (regular returns)

- sellandruternbilldetail (combined bills)

2. Product Aggregation:

- Group by product ID to avoid duplicates

- Sum quantities and values across bill types

- Apply unit conversions for regular sales

3. Discount Calculations:

   // Card payment discounts
   if ($theBill->card == 1) {
       if ($theBill->paymentnetworkid == 4) { // Mada
           $dicount = $madaData->totalCarry < 5000 ? 
               (7 * $madaData->totalCarry) / 1000 : 40;
       } else {
           $dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
       }
   }
   ```

4. **Final Calculations**:
   - Net quantities: `soldNo - returnNo`
   - Net values: `soldVal - returnVal`
   - Real cost: `netNo * productBuyPrice`
   - Net profit: `netVal - realCost`
   - Current inventory from `storedetail`

**Return Value**:

php

return array(

'client_data_array' => $client_data_array, // Product IDs

'clientDataArr' => $clientDataArr, // [clientName, productDataArray]

'clientId' => $clientid

);

---

### 3. **getDataByProductId()** - Product Customer Analysis  
**Location**: Lines 697-1021  
**Purpose**: Generate client sales data for a specific product (reverse perspective)

**Function Signature**:

php

function getDataByProductId($queryString, $queryString1, $queryStringR,

$queryString1R, $queryString1SR, $searchtype, $productCatId, $theStore, $productid)

**Process Flow**:
1. **Data Collection**: Same 5 bill type queries as `getDataByClientId()` but filtered by product
2. **Client Aggregation**: Group by client ID instead of product ID
3. **Identical Processing**: Same discount calculations and final computations
4. **Different Grouping**: Results show which clients bought the specified product

**Key Difference**:

php

// getDataByClientId: Groups products per client

if (in_array($value->productid, $existId)) {

$key = array_search($value->productid, $existId);

// getDataByProductId: Groups clients per product

if (in_array($value->clientid, $existId)) {

$key = array_search($value->clientid, $existId);

**Return Value**:

php

return array(

'product_data_array' => $product_data_array, // Client IDs

'productDataArr' => $productsDataArr // [productName, clientDataArray]

);

---

### 4. **getAllSubCat()** - Category Hierarchy Traversal
**Location**: Lines 1023-1061  
**Purpose**: Recursively traverse product category hierarchy to find all subcategories

**Function Signature**:

php

function getAllSubCat($catid, $mode)

**Modes**:
- `$mode = 1`: Get all subcategory IDs (builds `$catsIDS` string)
- `$mode = 2`: Get only leaf-level categories (builds `$lastLevelCatIDS` array)

**Process Flow**:
1. Query `productcat` table for direct children of `$catid`
2. If children found:
   - Mode 1: Add to `$catsIDS` comma-separated string
   - Mode 2: Recursively check if each child has children
3. Recursively call itself for each child category
4. For Mode 2: Only add categories with no children to final array

**Global Variables Modified**:

php

global $catsIDS; // "1,2,5,8,12" - all subcategory IDs

global $lastLevelCatIDS; // [15,16,17] - leaf categories only

---

### 5. **productData Class** - Data Transfer Object
**Location**: Lines 359-376  
**Purpose**: Standardized data structure for product sales analysis

**Class Properties**:

php

class productData {

public $id; // Product/Client ID (depends on report mode)

public $productName; // Product name

public $soldNo = 0; // Total quantity sold

public $soldVal = 0; // Total sales 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 (sold - return values)

public $realCost = 0; // Cost of goods sold

public $netProfit = 0; // Gross profit (net value - cost)

public $buyPrice = 0; // Product purchase price

public $currentQuantity = 0; // Current stock level

public $clientid; // Associated client ID

public $clientName; // Client name

}

---

## 🔄 Workflows

### Workflow 1: Client-Product Sales Analysis

┌─────────────────────────────────────────────────────────────┐

│ START: Select Date Range & Filters │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 1. Build Query Strings for All Bill Types │

│ - billsproducts (optical sales) │

│ - billsreturnproducts (optical returns) │

│ - sellbilldetail (regular sales) │

│ - returnsellbilldetail (regular returns) │

│ - sellandruternbilldetail (combined) │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 2. Apply Filters to Query Strings │

│ - Date range (datefrom/dateto) │

│ - Store/Branch restrictions │

│ - Seller/User permissions │

│ - Product/Category selection │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 3. Loop Through All Clients │

│ FOR EACH client in system: │

│ │ │

│ ├─→ Call getDataByClientId() │

│ │ ├─ Query all bill types │

│ │ ├─ Aggregate products by ID │

│ │ ├─ Calculate discounts │

│ │ ├─ Compute net quantities/values │

│ │ ├─ Get current stock levels │

│ │ └─ Calculate profits │

│ │ │

│ ├─→ Add to results if data found │

│ │ │

│ └─→ Continue to next client │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 4. Generate Client-Product Matrix Report │

│ - Display clients as rows │

│ - Show products purchased by each client │

│ - Include quantities, values, profits │

│ - Present via clientswithproductsreport/show.html │

└─────────────────────────────────────────────────────────────┘

---

### Workflow 2: Product-Client Analysis

┌─────────────────────────────────────────────────────────────┐

│ START: Select Products Mode (page=products) │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 1. Same Query String Building as Workflow 1 │

│ (Identical filter processing) │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 2. Loop Through All Products │

│ FOR EACH product in system: │

│ │ │

│ ├─→ Call getDataByProductId() │

│ │ ├─ Query all bill types by product │

│ │ ├─ Aggregate clients by ID │

│ │ ├─ Same discount/profit calculations │

│ │ └─ Group by client instead of product │

│ │ │

│ ├─→ Add to results if data found │

│ │ │

│ └─→ Continue to next product │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 3. Generate Product-Client Matrix Report │

│ - Display products as rows │

│ - Show which clients bought each product │

│ - Include same financial metrics │

│ - Present via clientswithproductsreport/showbyproducts.html │

└─────────────────────────────────────────────────────────────┘

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Display report form and process results |
| `page=products` | Product mode | Switch to product-centric analysis |
| `page=` (empty) | Client mode | Default client-centric analysis |

### Required Parameters

**Search Form Parameters**:
- `datefrom` - Start date filter (POST)
- `dateto` - End date filter (POST)  
- `storeId` - Store filter (POST)
- `branchId` - Branch filter (POST)
- `sellerid` - Seller filter (POST)
- `userid` - User filter (POST)
- `level` - Category level depth (POST)
- `productCatId{N}` - Category ID at level N (POST)
- `productId` - Specific product filter (POST)
- `searchtype` - Search type mode (POST)
- `proIsOptic` - Optical product flag (POST)

**Display Mode**:
- `page` - Report perspective ('products' or empty for clients) (GET)

---

## 🧮 Calculation Methods

### Discount Calculations

**Mada Payment Network Discount**:

php

if ($theBill->paymentnetworkid == 4) { // Mada

$madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);

if ($madaData->totalCarry < 5000)

$dicount = (7 * $madaData->totalCarry) / 1000;

else

$dicount = 40;

}

**Regular Card Discount**:

php

else {

$dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;

}

**Bill-Level Discount**:

php

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

**Product-Proportional Discount**:

php

$theDiscount = ($value->productno * $dicount) / $billNoOfProduct;

$theDiscount = round($theDiscount, 2);

### Unit Conversion

php

$quantity = $value->sellbilldetailquantity; // Quantity in bill unit

$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);

$productnumber = $productunitData->productnumber; // Conversion factor

$finalquantity = $quantity * $productnumber; // Quantity in base unit

### Financial Calculations

php

$data->netNo = $data->soldNo - $data->returnNo; // Net quantity

$data->netVal = $data->soldVal - $data->returnVal; // Net sales value

$data->realCost = $data->netNo * $buyDatail->productBuyPrice; // Cost of goods

$data->netProfit = $data->netVal - $data->realCost; // Gross profit

---

## 🔒 Security & Permissions

### User Access Control

php

// Store restriction based on user permissions

if ($user->userstoreid == 0) {

$theStore = $storeId; // Admin can see all stores

} else {

$theStore = $user->userstoreid; // Restricted to user's store

}

// Branch restriction

if ($user->branchId == 0) {

// Admin can select any branch

} else {

// Force user's branch only

$queryString .= 'and bills.branchid = ' . $user->branchId;

}

### Input Sanitization

php

$datefrom = filter_input(INPUT_POST, 'datefrom');

$dateto = filter_input(INPUT_POST, 'dateto');

$storeId = filter_input(INPUT_POST, 'storeId');

$isOptic = filter_input(INPUT_POST, 'proIsOptic');

if (!isset($isOptic) || empty($isOptic)) {

$isOptic = 0; // Default value

}

### SQL Injection Prevention
- All database queries use DAO layer with parameterized queries
- User inputs are filtered through `filter_input()` functions
- Numeric IDs are validated before inclusion in queries

---

## 📊 Performance Considerations

### Database Optimization

**Critical Indexes Required**:

sql

-- Bill product queries

CREATE INDEX idx_billsproducts_bill_product ON billsproducts(billid, productid);

CREATE INDEX idx_billsproducts_date ON bills(billdate);

-- Sales detail queries

CREATE INDEX idx_sellbilldetail_product ON sellbilldetail(sellbilldetailproductid);

CREATE INDEX idx_sellbill_client_date ON sellbill(sellbillclientid, sellbilldate);

-- Category hierarchy

CREATE INDEX idx_productcat_parent ON productcat(productCatParent);

CREATE INDEX idx_product_category ON product(productCatId);

-- Store inventory

CREATE INDEX idx_storedetail_product_store ON storedetail(productid, storeid);

### Performance Bottlenecks

**1. Category Traversal**:

php

// This recursive function can be slow for deep hierarchies

getAllSubCat($productCatId, 1);

// Consider caching category trees or using closure table

**2. Large Date Ranges**:
- Reports spanning long periods query massive datasets
- Consider pagination or summary views for large datasets

**3. Discount Calculations**:

php

// These queries run inside loops - consider batching

$madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);

$billpecies = $billsProductsEX->queryBillNoOfPecies($value->billid);

### Optimization Recommendations

**1. Batch Data Loading**:

php

// Instead of loading each bill individually

$theBill = $billsDAO->load($value->billid);

// Batch load all bills at once

$billIds = array_column($billsData, 'billid');

$bills = $billsDAO->loadMultiple($billIds);

**2. Category Caching**:

php

// Cache category hierarchies in session or memory

if (!isset($_SESSION['category_tree'])) {

$_SESSION['category_tree'] = buildCategoryTree();

}

---

## 🐛 Common Issues & Troubleshooting

### 1. **Zero Stock Quantities Show**
**Issue**: Products show zero `currentQuantity` despite having stock  
**Cause**: Store filter mismatch in inventory query

**Debug**:

sql

SELECT storedetail.storeid, SUM(storedetailquantity)

FROM storedetail

WHERE productid = [PRODUCT_ID]

GROUP BY storeid;

**Fix**:

php

// Ensure store filter matches between sales and inventory queries

if (isset($theStore) && !empty($theStore) && $theStore != -1) {

$myQuery = " and storedetail.storeid = " . $theStore;

}

### 2. **Discount Calculations Don't Match**
**Issue**: Product discounts don't equal bill discounts  
**Cause**: Division by zero or missing bill piece count

**Debug**:

php

echo "Bill Total Discount: " . $dicount . "
";

echo "Bill Pieces: " . $billNoOfProduct . "
";

echo "Product Discount: " . $theDiscount . "
";

### 3. **Missing Products in Category Filter**
**Issue**: Category filter doesn't return expected products  
**Cause**: Category hierarchy not fully traversed

**Debug**:

php

echo "Category IDs: " . $catsIDS . "
";

$productsInCat = $ProductEX->queryByProductCatIdIn($catsIDS);

echo "Products found: " . count($productsInCat);

### 4. **Optical vs Regular Product Mix-up**
**Issue**: Optical products show wrong calculations  
**Cause**: `isOptic` flag not properly checked

**Fix**:

php

if ($isOptic == 2 && $searchtype == 1) {

$productCatId = $productId; // Use category instead of product

}

---

## 🧪 Testing Scenarios

### Test Case 1: Client-Product Matrix Accuracy

1. Create test client with known transactions

2. Record specific products sold/returned with quantities

3. Run client report for test period

4. Verify each product line matches recorded transactions

5. Check net calculations: sold - returned = net

6. Confirm profit calculations: (net value) - (net qty × cost) = profit

### Test Case 2: Product-Client Analysis

1. Select specific product with multiple customers

2. Record which clients bought this product

3. Run product report mode (page=products)

4. Verify all purchasing clients appear

5. Check quantities match individual client records

### Test Case 3: Category Hierarchy Filtering

1. Create nested category structure: Electronics > Phones > Smartphones

2. Add products at different levels

3. Filter by parent category "Electronics"

4. Verify all subcategory products appear in results

5. Test with deepest level category - should show only direct products

### Test Case 4: Store/Branch Filtering

1. Create transactions in different stores/branches

2. Test with admin user - should see all locations

3. Test with restricted user - should see only assigned location

4. Verify inventory quantities match store selection

### Debug Mode Enable

php

// Add at top of controller for debugging

error_reporting(E_ALL);

ini_set('display_errors', 1);

// Debug query strings

echo "Bills Query: " . $queryString . "
";

echo "Sales Query: " . $queryString1 . "
";

// Debug data flow

echo "

";

print_r($allData);

echo "

```

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• sellbillController.md - Sales operations
• productController.md - Product management
• clientController.md - Customer management
• storedetailController.md - Inventory management

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

ClientsWithProductsReport Documentation

Clients With Products Report Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Main Controller Logic - Dual Mode Report Generator

2. getDataByClientId() - Client Sales Analysis

📚 Related Documentation