# Clients and Products Report Controller Documentation

**File**: `/controllers/clientsAndProductsReportController.php`  
**Purpose**: Generates comprehensive reports for client-product transaction history and analysis  
**Last Updated**: December 20, 2024  
**Total Functions**: 4+  
**Lines of Code**: ~450

---

## 📋 Overview

The Clients and Products Report Controller is a specialized reporting module that provides detailed analysis of customer transactions with specific products. It handles:
- Individual client-product transaction reports
- Combined sales, returns, and sell-return bills analysis
- Product-specific customer purchasing history
- Date range filtering for transaction analysis
- Multi-table union queries for comprehensive data
- Price history tracking by client and product
- Unit conversion and quantity analysis
- Discount and pricing analytics

### Primary Functions
- [x] Generate client-product transaction reports
- [x] Track product sales history per customer
- [x] Analyze price variations over time
- [x] Combine multiple bill types (sales, returns, combined)
- [x] Date range filtering and analysis
- [x] Product unit conversion tracking
- [x] Discount and price type analysis
- [x] Stock quantity monitoring
- [x] Multi-table data aggregation
- [x] Transaction linking to source documents

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

---

## 🗄️ Database Tables

### Primary Sales Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbilldetail** | Sales bill line items | sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailprice, discountvalue, pricetype |
| **sellbill** | Sales bills master | sellbillid, sellbillclientid, sellbilldate, conditions |
| **sellandruternbilldetail** | Combined sell-return details | sellandruternbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailprice, discountvalue, selltype |
| **sellbillandrutern** | Combined bills master | sellbillid, sellbillclientid, sellbilldate, sellQuantity |
| **returnsellbilldetail** | Return bill line items | returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity, returnsellbilldetailprice |
| **returnsellbill** | Return bills master | returnsellbillid, returnsellbillclientid, returnsellbilldate, conditions |

### Product and Inventory Tables (Referenced)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **product** | Product master data | productId, productName, productCatId |
| **productcat** | Product categories | productCatId, productCatName |
| **productunit** | Product unit conversions | productunitid, productid, unitid |
| **unit** | Unit definitions | unitId, unitName |
| **storedetail** | Stock quantities | storeid, productid, productquantity |

### Customer and Configuration Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **client** | Customer master data | clientid, clientname, conditions |
| **programsettings** | System configuration | programsettingsid, reportsPlusHours |
| **youtubelink** | Tutorial links | youtubelinkid, title, url |

### Service Bills (Alternative System)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **bills** | Service bills | billid, clientid, billdate |
| **billsproducts** | Service bill products | billsproductsid, billid, productid |
| **billsreturnproducts** | Service bill returns | billsreturnproductsid, billid, productid |

---

## 🔑 Key Functions

### 1. **show() / Default Action** - Client-Product Transaction Report
**Location**: Line 139  
**Purpose**: Generate comprehensive transaction report for specific client-product combinations

**Function Signature**:
```php
// Triggered when: do=show or empty $do
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];
$productId = $_REQUEST['productId'];
$clientId = (int) $_REQUEST['clientId'];
```

**Process Flow**:
1. Validate user permissions and load settings
2. Parse date parameters with default to current date
3. Load client and product data for display names
4. Build complex WHERE clauses for multiple table types
5. Execute union query combining sales, returns, and combined bills
6. Apply date adjustments based on system settings
7. Display via `clientsAndProductsReportview/showNew.html` template

**Features**:
- Date range filtering with time adjustments
- Product and client filtering
- Multi-table union queries
- Price type and discount tracking
- Stock quantity monitoring
- Message generation for report headers

**Union Query Structure**:
```sql
(SELECT ... FROM sellbilldetail JOIN sellbill ...)
UNION ALL
(SELECT ... FROM sellandruternbilldetail JOIN sellbillandrutern ...)
UNION ALL  
(SELECT ... FROM returnsellbilldetail JOIN returnsellbill ...)
```

---

### 2. **getClients()** - Customer Data Loader
**Location**: Line 280  
**Purpose**: Load active customers for dropdown selection

**Function Signature**:
```php
function getClients()
```

**Process Flow**:
1. Query client table with condition filter (active clients only)
2. Return array of client objects for dropdown population

**Returns**: Array of customer objects with conditions = 0 (active)

---

### 3. **loadPricesForClient()** - Comprehensive Price History
**Location**: Line 286  
**Purpose**: Load detailed price history for client-product combination (currently unused)

**Function Signature**:
```php
function loadPricesForClient($productId, $clientId, $startDate, $endDate)
```

**Process Flow**:
1. Call `getProductPriceFromSellBill()` to get all transaction types
2. Merge data from multiple bill types
3. Return combined array of transactions

**Note**: This function contains commented-out unit-based iteration logic that was previously used for per-unit analysis.

---

### 4. **getProductPriceFromSellBill()** - Sales Transaction Data
**Location**: Line 342  
**Purpose**: Retrieve product transactions from sales system tables

**Function Signature**:
```php
function getProductPriceFromSellBill($ProductId, $ClientId, $startDate, $endDate)
```

**Process Flow**:
1. Build dynamic WHERE clauses for multiple table types:
   - `$whereSellBill` - Regular sales bills
   - `$whereSellAndReturn` - Combined bills  
   - `$whereBillProduct` - Service bills
   - `$whereReturnBill` - Return bills
2. Apply product ID filter if specified
3. Apply client ID filter if specified  
4. Apply date range filter if specified
5. Query each table type using extended DAO methods
6. Return array with four datasets

**WHERE Clause Building**:
```php
if ($ProductId != '' && $ProductId != '-1') {
    $whereSellBill .= ' sellbilldetail.sellbilldetailproductid = ' . $ProductId;
    $whereSellAndReturn .= ' sellandruternbilldetail.sellbilldetailproductid = ' . $ProductId;
    // ... for each table type
}
```

**Returned Data Structure**:
```php
return array(
    $sellbilldetailData,        // [0] Regular sales
    $sellandruternbilldetailData, // [1] Combined bills  
    $BillProductData,           // [2] Service bills
    $ruternBillProductData      // [3] Service returns
);
```

---

### 5. **getProductPriceFromBills()** - Service Bills Analysis
**Location**: Line 438  
**Purpose**: Retrieve product transactions from service bills system (alternative)

**Function Signature**:
```php
function getProductPriceFromBills($ProductId, $ClientId, $startDate, $endDate)
```

**Process Flow**:
1. Query service bill products with filters
2. Query service bill returns with filters
3. Return array with both datasets

**Use Case**: Alternative billing system for service-based businesses (like optical shops)

---

## 🔄 Workflows

### Workflow 1: Client-Product Transaction Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│          START: Select Client, Product & Date Range        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Validate Input Parameters                               │
│     - Check clientId (optional)                             │
│     - Check productId (optional)                            │
│     - Parse date range (defaults to today)                  │
│     - Load program settings                                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Load Display Names                                      │
│     - Load client name if clientId specified                │
│     - Load product name if productId specified              │
│     - Build message string for report header                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Apply Date Adjustments                                  │
│     - Check reportsPlusHours setting                        │
│     - Adjust dates for timezone/business hours              │
│     - Format dates for SQL query                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Build WHERE Clauses                                     │
│     FOR EACH bill type (sell, return, combined):           │
│       │                                                     │
│       ├─→ Add product filter if specified                  │
│       ├─→ Add client filter if specified                   │
│       └─→ Add date range filter if specified               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Execute Union Query                                     │
│     - Combine sellbilldetail transactions                   │
│     - Combine sellandruternbilldetail transactions          │
│     - Combine returnsellbilldetail transactions             │
│     - Apply joins for product, unit, category, client      │
│     - Order results by detail ID descending                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Process and Display Results                             │
│     - Convert object structure for template                 │
│     - Assign data to Smarty template                        │
│     - Display via showNew.html template                     │
│     - Enable product/client selection widgets               │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Data Aggregation from Multiple Bill Types
```
┌─────────────────────────────────────────────────────────────┐
│            START: Build Comprehensive Dataset              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Regular Sales Bills                                     │
│     - Query: sellbilldetail + sellbill                      │
│     - Include: price, quantity, discount, stock             │
│     - Filter: conditions = 0 (not cancelled)               │
│     - Join: product, unit, category, client                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Combined Sell-Return Bills                              │
│     - Query: sellandruternbilldetail + sellbillandrutern    │
│     - Include: price, quantity, discount                    │
│     - Filter: selltype = 0 (sales portion)                 │
│     - Join: product, unit, category, client                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Return Bills                                            │
│     - Query: returnsellbilldetail + returnsellbill          │
│     - Include: return price, return quantity                │
│     - Filter: conditions = 0 (not cancelled)               │
│     - Join: product, unit, category, client                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Merge and Standardize                                   │
│     - Union all result sets                                 │
│     - Standardize column names (prices, quantitys, etc.)   │
│     - Add bill type indicator (0, 1, 4)                    │
│     - Apply consistent date formatting                      │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=show` | Default action | Client-product transaction report |

### Required Parameters
**Client-Product Report** (`do=show`):
- `from` - Start date (YYYY-MM-DD) - Optional, defaults to today
- `to` - End date (YYYY-MM-DD) - Optional, defaults to today
- `productId` - Product ID - Optional, empty or -1 for all products
- `clientId` - Client ID - Optional, must be > 1 for specific client

### Example URLs
```
# Specific client and product for date range
/clientsAndProductsReportController.php?do=show&clientId=5&productId=10&from=2024-12-01&to=2024-12-20

# All products for specific client
/clientsAndProductsReportController.php?do=show&clientId=5&from=2024-12-01&to=2024-12-20

# Specific product for all clients
/clientsAndProductsReportController.php?do=show&productId=10&from=2024-12-01&to=2024-12-20

# Today's transactions (default)
/clientsAndProductsReportController.php?do=show
```

---

## 🧮 Calculation Methods

### Date Adjustment Logic
```php
if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
    // Add extra hours for business timezone
    $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
    $endDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour', strtotime($endDate)));
    $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour', strtotime($startDate)));
} else {
    // Standard day boundaries
    $endDate = $endDate . ' 23:59:59';
    $startDate = $startDate . " 00:00:00";
}
```

### Bill Type Classification
```php
// Bill type indicators in union query
0 = Regular sales bills (sellbilldetail)
1 = Combined bills - sales portion (sellandruternbilldetail)  
4 = Return bills (returnsellbilldetail)
```

### Stock Quantity Handling
```php
// Left join with storedetail for current stock
LEFT JOIN storedetail ON (
    product.productId = storedetail.productid 
    AND sellbilldetail.storeid = storedetail.storeid
)
// Shows productquantity for stock reference
```

### Message Generation
```php
$message = '';
if ($clientId > 1) {
    $message .= "العميل :" . $clientName; // Client: [name]
}
if ($productId != '' && $productId != '-1') {
    $message .= "<br> المنتج: " . $productName; // Product: [name]
}
if ($startDate != '' && $endDate != '') {
    $message .= "<br> من تاريخ: " . $startDate . " إلى تاريخ: " . $endDate; // Date range
}
```

---

## 🔒 Security & Permissions

### Authentication Check
```php
include_once("../public/authentication.php");
```

**Security Features**:
- Session-based authentication required
- Input parameter sanitization
- SQL injection prevention via DAO layer
- Numeric ID casting: `(int) $_REQUEST['clientId']`

### Input Validation
```php
// Client ID validation
$clientId = (int) $_REQUEST['clientId']; // Cast to integer

// Product ID validation  
if ($productId != '' && $productId != '-1') {
    // Valid product specified
}

// Date validation through framework
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];
```

---

## 📊 Performance Considerations

### Database Optimization
1. **Indexes Required**:
   - `sellbilldetail(sellbilldetailproductid, sellbillid)`
   - `sellbill(sellbillclientid, sellbilldate)`
   - `sellandruternbilldetail(sellbilldetailproductid, sellbillid)`
   - `returnsellbilldetail(returnsellbilldetailproductid, returnsellbillid)`
   - `client(clientid)`
   - `product(productId)`

2. **Query Optimization**:
   - Union queries can be resource intensive
   - Date filtering with proper indexes
   - LEFT JOINs for optional data (storedetail)
   - Efficient WHERE clause ordering

3. **Memory Management**:
   - Large date ranges may return many records
   - JSON encoding for template data
   - Multiple table joins in single query

### Union Query Performance
```sql
-- The main query structure
( SELECT ... FROM sellbilldetail ... ORDER BY sellbilldetailid DESC )
UNION ALL
( SELECT ... FROM sellandruternbilldetail ... ORDER BY sellbilldetailid DESC )  
UNION ALL
( SELECT ... FROM returnsellbilldetail ... ORDER BY returnsellbilldetailid DESC )

-- Potential optimization: Add LIMIT if needed
-- Consider pagination for very large result sets
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Empty Results Despite Valid Data**
**Issue**: Report shows no data when transactions exist  
**Cause**: Date format mismatch or condition filtering

**Debug**:
```sql
-- Check data exists
SELECT COUNT(*) FROM sellbilldetail sd 
JOIN sellbill sb ON sd.sellbillid = sb.sellbillid
WHERE sb.sellbillclientid = [CLIENT_ID] 
AND sb.conditions = 0;

-- Check date format
SELECT sellbilldate FROM sellbill WHERE sellbillid = [BILL_ID];
```

### 2. **Incorrect Stock Quantities**
**Issue**: productquantity shows NULL or wrong values  
**Cause**: Missing storedetail records or mismatched store IDs

**Fix**:
```sql
-- Verify storedetail records
SELECT * FROM storedetail WHERE productid = [PRODUCT_ID];

-- Check store assignments in bills
SELECT DISTINCT storeid FROM sellbilldetail WHERE sellbilldetailproductid = [PRODUCT_ID];
```

### 3. **Missing Product/Client Names**
**Issue**: Names don't appear in message header  
**Cause**: Invalid IDs or inactive records

**Debug**:
```sql
-- Check client exists and is active
SELECT * FROM client WHERE clientid = [CLIENT_ID] AND conditions = 0;

-- Check product exists
SELECT * FROM product WHERE productId = [PRODUCT_ID];
```

### 4. **Date Adjustment Issues**
**Issue**: Transactions outside expected date range appear  
**Cause**: reportsPlusHours setting affecting date boundaries

**Fix**:
```php
// Check current setting
$setting = $ProgramsettingDAO->load(1);
echo "reportsPlusHours: " . $setting->reportsPlusHours;

// Disable adjustment temporarily
$Programsetting->reportsPlusHours = null;
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Client-Product Report
```
1. Select active client with recent transactions
2. Select product with sales in date range
3. Set date range covering known transactions
4. Verify all bill types appear (sales, returns, combined)
5. Check stock quantities are accurate
```

### Test Case 2: Date Range Filtering
```
1. Create transactions on specific dates
2. Test various date ranges (same day, month, year)
3. Verify reportsPlusHours adjustment works
4. Test edge cases (midnight, end of month)
```

### Test Case 3: Union Query Accuracy
```
1. Create one transaction of each type
2. Run report covering all transactions
3. Verify each appears with correct billtype indicator
4. Check data consistency across union branches
```

### Test Case 4: Performance with Large Datasets
```
1. Test with clients having 1000+ transactions
2. Monitor query execution time
3. Test with broad date ranges
4. Verify memory usage with large result sets
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [sellbillController.md](sellbillController.md) - Sales operations
- [clientController.md](clientController.md) - Customer management  
- [productController.md](productController.md) - Product management
- [Database Schema Documentation](#) - Table relationships

---

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