# Client Reports Controller Documentation

**File**: `/controllers/clientReportsController.php`  
**Purpose**: Generates comprehensive reports for customer accounts, debt tracking, and transaction history  
**Last Updated**: December 20, 2024  
**Total Functions**: 12+  
**Lines of Code**: ~1,458

---

## 📋 Overview

The Client Reports Controller is a specialized reporting module that provides detailed customer account analysis and debt tracking capabilities. It handles:
- Individual customer debt reports
- Customer transaction history 
- Area-based customer grouping reports
- Sales-only customer reports
- Combined operation reports (sales, returns, payments)
- Customer debt change tracking
- Multi-format report output (HTML, potentially Excel/PDF)
- Date range filtering and analysis
- Payment history tracking

### Primary Functions
- [x] Generate customer debt reports
- [x] Track customer payment history
- [x] Area-based customer reporting
- [x] Sales transaction analysis
- [x] Combined operation reports
- [x] Date range filtering
- [x] Debt balance calculations
- [x] Transaction linking to source documents
- [x] Multi-customer area summaries
- [x] Payment vs invoice analysis

### Related Controllers
- [sellbillController.php](sellbillController.md) - Sales operations
- [clientController.php](#) - Customer management
- [returnsellbillController.php](#) - Sales returns
- [clientPayedDeptController.php](#) - Payment processing
- [depositcheckController.php](#) - Check deposits
- [billreceiptController.php](#) - Bill receipts

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **clientdebtchange** | Customer debt transaction log | clientdebtchangeid, clientid, clientdebtchangeamount, clientdebtchangetype, clientdebtchangedate, tablename |
| **client** | Customer master data | clientid, clientname, clientdebt, clientarea, userid |
| **clientarea** | Customer area/region groupings | id, name, description |

### Sales Tables (Referenced)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills | sellbillid, sellbillclientid, sellbilltotalbill, sellbillaftertotalbill, sellbilltotalpayed, conditions |
| **sellbilldetail** | Sales bill line items | sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice |
| **returnsellbill** | Sales return bills | returnsellbillid, returnsellbillclientid, returnsellbilltotalbill, returnsellbillaftertotalbill |
| **returnsellbilldetail** | Return bill details | returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity |
| **sellbillandrutern** | Combined sell & return | sellbillid, sellbillclientid, sellbillprice, returnsellbillprice, sellbilldate |
| **sellandruternbilldetail** | Combined bill details | sellandruternbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, selltype |

### Financial Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **checkdeposit** | Check deposits | checkdepositid, clientid, bankname, accountname, checkamount |
| **save** | Cash registers/safes | saveid, savename, savevalue |
| **bills** | Service bills | billid, clientid, productstotalprice, finalnetbillvalue |
| **datedchecked** | Post-dated checks | datedcheckedid, endorsePersonId, done |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **supplier** | Supplier information | supplierid, suppliername |
| **user** | System users | userid, username, viewclients, viewbills, usergroupid |
| **youtubelink** | Tutorial links | youtubelinkid, title, url |
| **programsettings** | System configuration | programsettingsid, settingkey, settingvalue |

---

## 🔑 Key Functions

### 1. **show() / Default Action** - Individual Customer Report
**Location**: Line 208  
**Purpose**: Generate comprehensive debt and transaction report for a specific customer

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

**Process Flow**:
1. Validate user permissions (`viewclients` check)
2. Parse date parameters (supports "month" shortcut)
3. Load customer data for report header
4. Call `clientShow()` with parameters
5. Load YouTube tutorial links
6. Display via `clientShow.html` template

**Features**:
- Date range filtering
- Monthly quick filter (`find=month`)
- User permission filtering
- Order/sorting options
- Match-only filtering for account reconciliation

---

### 2. **clientShow()** - Core Customer Report Logic
**Location**: Line 443  
**Purpose**: Build detailed customer debt history with transaction linking

**Function Signature**:
```php
function clientShow($clientid, $startDate, $endDate, $order)
```

**Process Flow**:
1. Build dynamic SQL query string based on filters
2. Apply user permission restrictions
3. Query `clientdebtchange` table with date/client filters
4. Process each transaction record:
   - Link to source documents (bills, payments, checks)
   - Calculate running totals
   - Load related data (supplier names, bank info)
5. Handle "bure" (net) view calculation
6. Assign data to Smarty template

**Transaction Types Handled**:
- `sellbillController.php` - Sales bills
- `returnsellbillController.php` - Sales returns  
- `sellbillandruternController.php` - Combined bills
- `depositcheckController.php` - Check deposits
- `clientPayedDeptController.php` - Customer payments
- `kempialaController.php` - Promissory notes
- `billreceiptController.php` - Bill receipts
- `transferclientccountsController.php` - Account transfers
- `dailyentry.php` - Journal entries

**Key Variables**:
- `$total` - Running debt balance
- `$queryString` - Dynamic WHERE clause
- `$orderBy` - Sort specification
- `$searchDate` - Date field to filter on (sysDate vs userSelectedDate)
- `$matchOnly` - Filter for reconciliation entries only

---

### 3. **clientShowByArea()** - Area-Based Customer Summary
**Location**: Line 243  
**Purpose**: Group customers by area and show debt summaries by region

**Process Flow**:
1. Load all customer areas
2. Build query string with area filter
3. For each area:
   - Query customers in that area
   - Sum total debt per area
   - Assign area data to template variables
4. Calculate grand total across all areas
5. Display via `clientShowByArea.html`

**Template Variables**:
- `$clientShowData{N}` - Customer data for area N
- `$sum{N}` - Debt total for area N  
- `$totalsum` - Grand total across all areas
- `$allclientarea` - Area master data

---

### 4. **clientShowforsellonly()** - Sales Transaction Report
**Location**: Line 733  
**Purpose**: Focus specifically on sales transactions with product detail

**Function Signature**:
```php
function clientShowforsellonly($clientid, $startDate, $endDate)
```

**Process Flow**:
1. Query customer debt changes filtered for sales operations
2. For each sales bill type:
   - Load bill master data
   - Load detailed line items
   - Calculate quantities and totals
   - Handle discount and tax calculations
3. Aggregate totals:
   - `$allPayed` - Total sales amount
   - `$allPcsPlus` - Total quantity sold
   - `$allPcsMinus` - Total quantity returned
   - `$allTax` - Total tax amount

**Sales Bill Types**:
- Regular sales (`sellbillController.php`)
- Sales returns (`returnsellbillController.php`)
- Combined bills (`sellbillandruternController.php`)

**Discount Calculation**:
```php
if ($sellBillData->sellbilldiscounttype == 1)
    $discount = $sellBillData->sellbilldiscount; // Fixed amount
else
    $discount = $sellBillData->sellbilltotalbill * $sellBillData->sellbilldiscount / 100; // Percentage
```

---

### 5. **showAllOperations()** - Comprehensive Operations Report
**Location**: Line 1192  
**Purpose**: Combined view of all customer operations (sales, returns, payments)

**Function Signature**:
```php
function showAllOperations($clientid, $startDate, $endDate)
```

**Process Flow**:
1. Call `showSellBillsByClientAndDate()` for sales data
2. Call `showReturnSellBillsByClientAndDate()` for returns data  
3. Query `clientdebtchange` for other operations
4. Cross-reference and eliminate double-counting
5. Calculate net payment totals
6. Assign all data arrays to template

**Data Arrays**:
- `$sellbillData` - Sales bills with quantities
- `$returnsellbillData` - Return bills
- `$sellBillAndReturnData` - Combined bills
- `$clientShowData` - Payment/debt transactions
- `$totalPrice` - Net payment total

**Double-counting Prevention**:
```php
// Subtract bill payments already counted in debt changes
foreach ($sellbillData as $data) {
    if ($data->sellbilltotalpayed != 0 && $data->conditions == 0) {
        $totalPrice -= $data->sellbilltotalpayed;
    }
}
```

---

### 6. **showSellBillsByClientAndDate()** - Sales Bills Query
**Location**: Line 1285  
**Purpose**: Retrieve and process sales bills for a customer/date range

**Function Signature**:
```php
function showSellBillsByClientAndDate($clientid, $startDate, $endDate)
```

**Process Flow**:
1. Query `sellbill` table with client/date filters
2. Query `sellbillandrutern` table for combined bills
3. For each bill:
   - Count product quantities via detail tables
   - Apply discount calculations
   - Filter out cancelled bills (`conditions != 0`)
4. Merge datasets and return combined array

**Discount Processing**:
```php
if ($sellbilldiscounttype == 1) {
    $sellbillprice = ($bill->sellbillprice) - ($bill->sellbilldiscount); // Fixed
} else {
    $sellbillprice = ($bill->sellbillprice) - ((($bill->sellbillprice) * ($bill->sellbilldiscount)) / 100); // Percentage
}
```

---

### 7. **showReturnSellBillsByClientAndDate()** - Returns Query
**Location**: Line 1370  
**Purpose**: Retrieve and process return bills for analysis

**Function Signature**:
```php
function showReturnSellBillsByClientAndDate($clientid, $startDate, $endDate)
```

**Process Flow**:
1. Query `returnsellbill` table
2. Query `sellbillandrutern` table for return portions
3. Count returned quantities via detail tables
4. Sort results by date
5. Return array with both datasets

**Return Value**:
```php
$returnsellbill = array($returnsellbillData, $sellbillandruternData);
// [0] = Pure return bills
// [1] = Combined bill return portions
```

---

### 8. **getClientData()** - Customer Dropdown Data
**Location**: Line 427  
**Purpose**: Load customer list for report selection dropdowns

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

**Returns**: Array of customer objects for dropdown population

---

## 🔄 Workflows

### Workflow 1: Individual Customer Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│              START: Select Customer & Date Range            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Validate Input Parameters                               │
│     - Check clientid                                        │
│     - Parse date range                                      │
│     - Check user permissions                                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build SQL Query String                                  │
│     - Add client filter                                     │
│     - Add date range filter                                 │
│     - Add permission restrictions                           │
│     - Handle match-only filter                              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Query clientdebtchange Table                            │
│     - Get all transactions for client/date                  │
│     - Order by specified criteria                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Each Transaction                                │
│     FOR EACH debt change record:                            │
│       │                                                     │
│       ├─→ Identify source table (sellbill, payment, etc.)  │
│       │                                                     │
│       ├─→ Load source document details                     │
│       │   ├─ Bill totals                                   │
│       │   ├─ Payment amounts                               │
│       │   └─ Related party info                            │
│       │                                                     │
│       ├─→ Generate link to source document                 │
│       │                                                     │
│       └─→ Calculate running balance                        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Apply Net View Processing (if requested)                │
│     - Remove offsetting entries                             │
│     - Recalculate running balances                          │
│     - Clean up display names                                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Generate Report Output                                  │
│     - Assign data to Smarty template                        │
│     - Include totals and summaries                          │
│     - Display via clientShow.html                           │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Area-Based Customer Summary
```
┌─────────────────────────────────────────────────────────────┐
│               START: Select Customer Area                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Load All Customer Areas                                 │
│     - Query clientarea table                                │
│     - Apply area filter if specified                        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Each Area                                       │
│     FOR EACH customer area:                                 │
│       │                                                     │
│       ├─→ Query customers in this area                     │
│       │                                                     │
│       ├─→ Calculate area debt total                        │
│       │   └─ Sum clientdebt for all customers              │
│       │                                                     │
│       ├─→ Assign to template variables                     │
│       │   ├─ clientShowData{N}                             │
│       │   └─ sum{N}                                        │
│       │                                                     │
│       └─→ Add to grand total                               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Generate Area Summary Report                            │
│     - Display all areas with totals                         │
│     - Show grand total across areas                         │
│     - Present via clientShowByArea.html                     │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=show` | Default action | Individual customer report |
| `do=clientShowByArea` | Area grouping | Customer reports grouped by area |
| `do=clientShowforsellonly` | `clientShowforsellonly()` | Sales transactions only |
| `do=clientShow2` | `clientShow2()` | Alternative customer view format |
| `do=all` | `showAllOperations()` | Comprehensive operations report |

### Required Parameters by Action

**Individual Customer Report** (`do=show`):
- `clientid` - Customer ID
- `from` - Start date (YYYY-MM-DD)  
- `to` - End date (YYYY-MM-DD)
- `find` - Optional: "month" for current month
- `order` - Sort order (1=date desc, 2=ID asc, 3=date asc)

**Area Report** (`do=clientShowByArea`):
- `clientareaid` - Area ID (-1 for all areas)

**Sales Only** (`do=clientShowforsellonly`):
- `clientid` - Customer ID
- `from` - Start date
- `to` - End date

**All Operations** (`do=all`):
- `clientid` - Customer ID
- `from` - Start date (optional)
- `to` - End date (optional)

---

## 🧮 Calculation Methods

### Debt Balance Calculation
```php
foreach ($shownData as $data) {
    if ($data->clientdebtchangetype == 0) { // Debt increase
        $total = $total + $data->clientdebtchangeamount;
    } else { // Debt decrease (payment)
        $total = $total - $data->clientdebtchangeamount;
    }
}
```

### Discount Processing
```php
// Fixed amount discount
if ($sellBillData->sellbilldiscounttype == 1) {
    $discount = $sellBillData->sellbilldiscount;
}
// Percentage discount  
else {
    $discount = $sellBillData->sellbilltotalbill * $sellBillData->sellbilldiscount / 100;
}
```

### Tax Calculation
```php
$tax = ($sellBillData->sellbilltotalbill - $discount) * $sellBillData->tax / 100;
```

### Net View Processing
```php
// Remove offsetting entries for cleaner view
if ($type == 1) { // Payment entry
    $otherfinal = substr_replace($finalstring, '0', -1);
    foreach ($shownData as $xx) {
        if ($xx->finalstring == $otherfinal) {
            $xx->clientdebtchangeid = -1; // Hide from display
            $datax->clientdebtchangeid = -1;
        }
    }
}
```

---

## 🔒 Security & Permissions

### User Permission Levels
```php
// ViewClients Permission Check
$userData = $myUserRecord->load($_SESSION['userid']);
if ($userData->viewclients == 0) {
    $queryString .= ' client.userid =' . $_SESSION['userid'] . ' AND';
}
```

**Permission Levels**:
- `viewclients = 0` - User can only see their own customers
- `viewclients = 1` - User can see all customers  
- `viewbills = 0` - User can only see their own bills
- `viewbills = 2` - User can see bills from their user group

### Input Sanitization
- All `$_REQUEST` parameters are filtered through the framework
- Numeric IDs cast to integer: `(int) $_REQUEST['order']`
- Date strings validated before SQL inclusion
- SQL injection prevented by DAO layer parameterized queries

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Indexes Required**:
   - `clientdebtchange(clientid, clientdebtchangedate)`
   - `client(clientarea)`
   - `sellbill(sellbillclientid, sellbilldate)`
   - `returnsellbill(returnsellbillclientid, returnsellbilldate)`

2. **Query Optimization**:
   - Date filtering with proper format: `YYYY-MM-DD HH:MM:SS`
   - Use of efficient WHERE clauses
   - Avoid N+1 queries by batching related data loads

3. **Memory Management**:
   - Large date ranges may return thousands of records
   - Consider pagination for very active customers
   - Template variable cleanup for area reports

### Known Performance Issues
```sql
-- This query can be slow for customers with many transactions
SELECT * FROM clientdebtchange 
WHERE clientid = ? 
AND clientdebtchangedate BETWEEN ? AND ?
ORDER BY clientdebtchangedate DESC;

-- Solution: Add composite index
CREATE INDEX idx_client_date ON clientdebtchange(clientid, clientdebtchangedate);
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Missing Transaction Links**
**Issue**: Transaction shows "#" link instead of proper document link  
**Cause**: `clientdebtchangemodelid = -1` or unrecognized `tablename`

**Debug**:
```sql
SELECT tablename, COUNT(*) FROM clientdebtchange 
WHERE clientid = [ID] GROUP BY tablename;
```

### 2. **Incorrect Running Totals**
**Issue**: Debt balance calculations don't match  
**Cause**: Mixed transaction types or missing entries

**Debug**:
```sql
SELECT 
    SUM(CASE WHEN clientdebtchangetype = 0 THEN clientdebtchangeamount ELSE 0 END) as increases,
    SUM(CASE WHEN clientdebtchangetype = 1 THEN clientdebtchangeamount ELSE 0 END) as decreases
FROM clientdebtchange WHERE clientid = [ID];
```

### 3. **Area Totals Don't Match**
**Issue**: Area summaries don't equal individual customer totals  
**Cause**: Customer area assignments changed or missing

**Fix**:
```sql
-- Check for customers without areas
SELECT * FROM client WHERE clientarea IS NULL OR clientarea = 0;

-- Verify area assignments
SELECT ca.name, COUNT(c.clientid) as customer_count, SUM(c.clientdebt) as total_debt
FROM clientarea ca
LEFT JOIN client c ON c.clientarea = ca.id
GROUP BY ca.id;
```

### 4. **Date Range Issues**
**Issue**: Reports show no data for valid date ranges  
**Cause**: Date format mismatch or timezone issues

**Fix**:
```php
// Ensure proper date format
if (!empty($startDate)) $startDate .= " 00:00:00";
if (!empty($endDate)) $endDate .= " 23:59:59";
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Customer Report
```
1. Select customer with recent transactions
2. Set date range covering known transactions  
3. Verify transaction count matches database
4. Check running balance calculation
5. Confirm links to source documents work
```

### Test Case 2: Area Summary Accuracy
```
1. Create test customers in different areas
2. Add known debt amounts to each customer
3. Run area report
4. Verify area totals match individual sums
5. Check grand total accuracy
```

### Test Case 3: Permission Enforcement
```
1. Login as restricted user (viewclients = 0)
2. Attempt to view other user's customers
3. Verify only own customers appear
4. Test with different permission levels
```

### Debug Mode Enable
```php
// Add at top of controller for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Debug query string building
echo "Query String: " . $queryString . "<br>";

// Debug data arrays
echo "<pre>";
print_r($shownData);
echo "</pre>";
```

---

## 📚 Related Documentation

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

---

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