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

### Related Controllers
- [sellbillController.php](sellbillController.md) - Sales operations
- [clientController.php](clientController.md) - Customer management
- [productController.php](productController.md) - Product management
- [storedetailController.php](storedetailController.md) - 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**:
```php
// 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**:
```php
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**:
   ```php
   // 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 . "<br>";
echo "Bill Pieces: " . $billNoOfProduct . "<br>";
echo "Product Discount: " . $theDiscount . "<br>";
```

### 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 . "<br>";
$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 . "<br>";
echo "Sales Query: " . $queryString1 . "<br>";

// Debug data flow
echo "<pre>";
print_r($allData);
echo "</pre>";
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [sellbillController.md](sellbillController.md) - Sales operations
- [productController.md](productController.md) - Product management  
- [clientController.md](clientController.md) - Customer management
- [storedetailController.md](storedetailController.md) - Inventory management

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur