# 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
- [x] Generate client-focused sales reports
- [x] Product transaction tracking per client
- [x] Multi-pricing method profit analysis
- [x] Discount distribution calculations
- [x] Product unit conversion handling
- [x] Date range filtering with time adjustments
- [x] Client type and area filtering
- [x] Net quantity and value calculations

### Related Controllers
- [sellbillController.php](sellbillController.md) - Sales operations
- [clientReportsController.php](clientReportsController.md) - Client reporting
- [returnsellbillController.php](#) - Sales returns
- [productController.php](#) - Product management
- [clientController.php](#) - Client management

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills master | sellbillid, sellbillclientid, sellbilltotalbill, sellbillaftertotalbill, sellbilldate, sellbillstoreid |
| **sellbilldetail** | Sales line items | sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice |
| **returnsellbill** | Return bills master | returnsellbillid, returnsellbillclientid, returnsellbillaftertotalbill, returnsellbilldate |
| **returnsellbilldetail** | Return line items | returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity |
| **sellbillandrutern** | Combined bills | sellbillid, sellbillclientid, sellbilldate, selltype |
| **sellandruternbilldetail** | Combined bill details | sellandruternbilldetailid, sellbillid, sellbilldetailproductid, selltype |

### Optical System Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **bills** | Optical service bills | billid, clientid, productstotalprice, finalnetbillvalue, billdate |
| **billsproducts** | Optical products | billproductid, billid, productid, producttotalprice, productno |
| **billsreturnproducts** | Optical returns | billreturnproductid, billproductid, productid, producttotalprice |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **client** | Client master data | clientid, clientname, typeclientid |
| **product** | Product information | productId, productName, productCatId, overAllAveragePrice, isService |
| **productunit** | Product units | productunitid, productid, productnumber, buyprice, lastbuyprice, meanbuyprice |
| **programsettings** | System settings | programsettingsid, vatValue, reportsPlusHours |

---

## 🔑 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**:
```php
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**:
```php
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
```
┌─────────────────────────────────────────────────────────────┐
│            START: Select Client & Product Filters          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Process Search Parameters                               │
│     - Client ID filter                                      │
│     - Product/Category selection                            │
│     - Date range with time adjustments                     │
│     - Store, branch, seller filters                        │
│     - Price type and pricing method                        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Query Strings for Each Bill Type                 │
│     - Regular sales bills (sellbill)                       │
│     - Return bills (returnsellbill)                        │
│     - Combined bills (sellbillandrutern)                   │
│     - Optical bills (bills) - if applicable                │
│     - Apply all filters to each query                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Process Optical System Bills (if applicable)           │
│     - Query bills and billsproducts for sales              │
│     - Query billsreturnproducts for returns                │
│     - Calculate card payment discounts (Mada fees)         │
│     - Handle percentage vs fixed discounts                 │
│     - Group by client ID                                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Regular Sales Bills                            │
│     - Query sellbilldetail with joins                      │
│     - Load product and unit information                    │
│     - Calculate final quantities (qty * unit number)       │
│     - Process bill-level discounts proportionally          │
│     - Calculate costs using chosen pricing method          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Process Return Bills                                    │
│     - Query returnsellbilldetail with joins                │
│     - Calculate returned quantities                        │
│     - Process return-specific discount calculations        │
│     - Subtract costs from client totals                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Process Combined Bills                                  │
│     - Query sellandruternbilldetail                        │
│     - Check selltype flag (0=sale, 1=return)               │
│     - Apply appropriate calculations based on type         │
│     - Handle discount distribution                         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  7. Calculate Final Metrics                                │
│     - Net quantities (sold - returned)                     │
│     - Net values (sales - returns)                         │
│     - Net profit (net value - real cost)                   │
│     - Grand totals across all clients                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  8. Generate Report Output                                  │
│     - Assign client data array to template                 │
│     - Include summary totals                               │
│     - Display via salesreportofclientsbyproductview       │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Client sales report based on search criteria |

### Required Parameters
- `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
```php
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
```php
// 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
```php
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
```php
// 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
```php
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
```php
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
```sql
-- 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**:
```sql
-- 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**:
```php
// 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**:
```php
// 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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [clientReportsController.md](clientReportsController.md) - Client reporting
- [sellbillController.md](sellbillController.md) - Sales operations
- [Database Schema Documentation](#) - Table relationships

---

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