# Client Debt Controller Documentation

**File**: `/controllers/clientdebt.php`  
**Purpose**: Displays clients with outstanding debt balances and provides AJAX-based debt summary reporting  
**Last Updated**: December 20, 2024  
**Total Functions**: 1  
**Lines of Code**: ~409

---

## 📋 Overview

The Client Debt Controller is a specialized reporting module focused on displaying customers who have outstanding debt balances. It provides a DataTables-powered interface for viewing client debt information with optional payment history tracking. The controller handles:
- Client debt balance display
- Optional payment transaction history
- Optional sales bill tracking
- Optional return bill tracking  
- Combined sales and return tracking
- AJAX-powered DataTables integration
- Multi-column searching and sorting
- Export capabilities through DataTables

### Primary Functions
- [x] Display clients with debt balances only
- [x] AJAX-powered client debt listing
- [x] Payment history tracking
- [x] Sales transaction tracking
- [x] Return transaction tracking
- [x] Combined sales/return tracking
- [x] DataTables search and pagination
- [x] Column sorting and filtering
- [x] Real-time debt balance calculations
- [x] Arabic label support for transaction types

### Related Controllers
- [clientReportsController.php](clientReportsController.md) - Detailed client reports
- [clientController.php](clientController.md) - Customer management
- [clientPayedDeptController.php](clientPayedDeptController.md) - Payment processing
- [sellbillController.php](sellbillController.md) - Sales operations
- [returnsellbillController.php](#) - Sales returns

---

## 🗄️ Database Tables

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

### Financial Tables (Conditional Joins)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bills | sellbillid, sellbillclientid, sellbilldate, conditions |
| **returnsellbill** | Sales return bills | returnsellbillid, returnsellbillclientid, returnsellbilldate, conditions |
| **sellbillandrutern** | Combined sell & return | sellbillid, sellbillclientid, sellbilldate, conditions |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **user** | System users/delegates | userid, username, employeename |
| **youtubelink** | Tutorial links | youtubelinkid, title, url |
| **programsettings** | System configuration | programsettingsid, settingkey, settingvalue |

---

## 🔑 Key Functions

### 1. **Default Action** - Main Debt View
**Location**: Line 121-139  
**Purpose**: Display main client debt interface with YouTube tutorial links

**Process Flow**:
1. Check user authentication
2. Load YouTube tutorial links
3. Display main debt view template (`clientdebtview/show.html`)

**Features**:
- User authentication verification
- Tutorial video integration
- Template-based UI rendering

---

### 2. **showallajax()** - AJAX Data Provider
**Location**: Line 161-408  
**Purpose**: Provide JSON data for DataTables client debt listing

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

**Process Flow**:
1. Parse POST parameters for optional data inclusion:
   - `$payment` - Include last payment transaction data
   - `$sell` - Include last sales bill date
   - `$ret` - Include last return bill date  
   - `$sellRet` - Include last combined bill date
2. Build dynamic SQL query based on requested data
3. Apply search filters and sorting
4. Execute queries with pagination
5. Process transaction type labels (Arabic)
6. Calculate debt balance summaries
7. Return JSON response for DataTables

**Base Query Structure**:
```sql
SELECT client.clientid, client.clientname, client.clientdebt, 
       client.clientphone, client.clientmobile, 
       clientarea.name as areaName, deligate.employeename
FROM client
LEFT JOIN clientarea ON clientarea.id = client.clientareaid
LEFT JOIN user as deligate ON deligate.userid = client.delegateid
WHERE client.conditions = 0 AND client.clientdebt != 0
```

**Conditional Joins**:
- **Payment History**: Joins `clientdebtchange` for last payment transactions
- **Sales History**: Joins subquery for last sales bill dates
- **Return History**: Joins subquery for last return bill dates
- **Combined History**: Joins subquery for last combined bill dates

**Transaction Type Processing**:
```php
if ($aRow->tablename == "depositcheckController.php") {
    $aRow->rondomtxt = "ايداع"; // Deposit
} elseif ($aRow->tablename == "kempialaController.php") {
    $aRow->rondomtxt = "كمبيالات"; // Promissory Notes  
} elseif ($aRow->tablename == "clientPayedDeptController.php") {
    $aRow->rondomtxt = "تحصيل"; // Collection
}
```

**Features**:
- Dynamic column inclusion based on request parameters
- Server-side pagination and filtering
- Multi-column search with special handling for date/numeric fields
- Arabic transaction type labels
- Real-time debt balance calculations
- Export-ready JSON format

---

## 🔄 Workflows

### Workflow 1: Client Debt Listing
```
┌─────────────────────────────────────────────────────────────┐
│                START: Load Client Debt Page                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Authentication Check                                    │
│     - Verify user session                                   │
│     - Load program settings                                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Load Tutorial Resources                                 │
│     - Query YouTube tutorial links                          │
│     - Assign to template variables                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Display Main Interface                                  │
│     - Render clientdebtview/show.html                       │
│     - Initialize DataTables component                       │
│     - Set up AJAX endpoint                                  │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: AJAX Data Loading
```
┌─────────────────────────────────────────────────────────────┐
│              START: DataTables AJAX Request                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Request Parameters                                │
│     - Extract pagination (start, length)                    │
│     - Extract sorting (order, direction)                    │
│     - Extract search terms                                  │
│     - Extract feature flags (payment, sell, ret, sellRet)   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Dynamic SQL Query                                │
│     - Start with base client query                         │
│     - Add optional joins based on flags                    │
│     - Apply WHERE conditions (debt != 0, conditions = 0)   │
│     - Add search filters                                    │
│     - Apply sorting and pagination                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Execute Queries                                         │
│     - Main data query                                       │
│     - Count query for pagination                            │
│     - Total count query                                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Results                                         │
│     FOR EACH result row:                                    │
│       │                                                     │
│       ├─→ Apply Arabic transaction labels                  │
│       │                                                     │
│       ├─→ Calculate debt balance summaries                 │
│       │   └─ Call clientdeptchangeaz() for running totals  │
│       │                                                     │
│       └─→ Format data for DataTables                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Generate JSON Response                                  │
│     - Format DataTables response structure                  │
│     - Include pagination metadata                           │
│     - Include summary totals                                │
│     - Return JSON to client                                 │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Main debt view interface |
| `do=showallajax` | `showallajax()` | AJAX data provider for DataTables |
| `do=sucess` | Display template | Success message page |
| `do=error` | Display template | Error message page |

### AJAX Parameters (showallajax)

**DataTables Standard Parameters**:
- `start` - Pagination offset
- `length` - Records per page
- `search[value]` - Global search term
- `order[0][column]` - Sort column index
- `order[0][dir]` - Sort direction (asc/desc)
- `columns` - Column definitions

**Custom Feature Flags**:
- `payment` - Include last payment transaction data (0/1)
- `sell` - Include last sales bill date (0/1)  
- `ret` - Include last return bill date (0/1)
- `sellRet` - Include last combined bill date (0/1)

---

## 🧮 Calculation Methods

### Debt Balance Summary
```php
$plussum = 0;
$minussum = 0;
foreach ($shownData as $data) {
    if ($data->clientdebtchangeafter > 0) {
        $plussum += $data->clientdebtchangeafter;
    } else {
        $minussum += $data->clientdebtchangeafter;
    }
}
```

### Search Field Processing
```php
// Special handling for date and numeric fields
if ($sColumns[$i] == "clientdebtchange.clientdebtchangedate" || 
    $sColumns[$i] == "qsellbill.lastSellBillDate" || 
    $sColumns[$i] == "qreturnsellbill.lastReturnSellBillDate" || 
    $sColumns[$i] == "qsellbillandrutern.lastSellBillAndRuternDate" || 
    $sColumns[$i] == "qclientdebtchange.clientdebtchangeamount") {
    // Remove special characters, allow only alphanumeric
    $search = preg_replace('/[^#A-Za-z0-9]/', '', $_POST['search']['value']);
}
```

### Column Ordering Logic
```php
// Handle special column ordering cases
if ($orderByColumnIndex == 5) { // clientarea.name as areaName
    $orderByColumn = "name";
} elseif ($orderByColumnIndex == 6) { // clientdebtchangedate
    $orderByColumn = "clientdebtchangedate";
} else {
    $orderByColumn = $sColumns[intval($_POST['columns'][$orderByColumnIndex]['data'])];
}
```

---

## 🔒 Security & Permissions

### Input Sanitization
```php
// Type casting for security
$payment = (int) $_POST['payment'];
$sell = (int) $_POST['sell'];
$ret = (int) $_POST['ret'];
$sellRet = (int) $_POST['sellRet'];

// Pagination limits
$sLimit = "LIMIT " . intval($_POST['start']) . ", " . intval($_POST['length']);
```

### SQL Injection Prevention
- All numeric inputs are cast to integers
- Search terms are parameterized through DAO layer
- Column names are validated against predefined arrays
- Special character filtering for sensitive fields

### Data Filtering
```php
// Only show active clients with debt
$sWhere .= "where client.conditions = 0 and client.clientdebt != 0";
```

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Indexes Required**:
   - `client(conditions, clientdebt)`
   - `clientdebtchange(clientid, clientdebtchangeid, del, tablename)`
   - `clientarea(id)`
   - `user(userid)`

2. **Query Optimization**:
   - Conditional joins only loaded when requested
   - Efficient subqueries for last transaction dates
   - Proper WHERE clause ordering
   - Pagination to limit result sets

3. **Memory Management**:
   - Server-side processing prevents large data downloads
   - JSON response chunking for large datasets
   - Efficient column selection based on requirements

### Known Performance Issues
```sql
-- This subquery can be slow for large datasets
SELECT max(clientdebtchangeid) FROM clientdebtchange 
WHERE clientdebtchange.tablename IN ('clientPayedDeptController.php', 'kempialaController.php', 'depositcheckController.php') 
AND del = 0 GROUP BY clientid

-- Solution: Add composite index
CREATE INDEX idx_cdc_tablename_del_client ON clientdebtchange(tablename, del, clientid, clientdebtchangeid);
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **DataTables Not Loading Data**
**Issue**: Empty table or loading spinner persists  
**Cause**: AJAX endpoint returning invalid JSON

**Debug**:
```javascript
// Add to browser console
$('#dataTable').DataTable().ajax.url().load();
```

**Fix**: Check PHP error logs and JSON response format

### 2. **Search Not Working on Date Columns**
**Issue**: Date searches return no results  
**Cause**: Special character filtering too restrictive

**Debug**:
```php
// Temporarily log search terms
error_log("Search term: " . $_POST['search']['value']);
error_log("Processed: " . $search);
```

### 3. **Missing Transaction Type Labels**
**Issue**: Payment types show as blank  
**Cause**: `tablename` field not matching expected values

**Debug**:
```sql
SELECT DISTINCT tablename, COUNT(*) FROM clientdebtchange 
WHERE del = 0 GROUP BY tablename;
```

### 4. **Slow Performance with Large Datasets**
**Issue**: Page loading slowly with many clients  
**Cause**: Missing database indexes or inefficient joins

**Fix**:
```sql
-- Add required indexes
CREATE INDEX idx_client_conditions_debt ON client(conditions, clientdebt);
CREATE INDEX idx_clientarea_id ON clientarea(id);
CREATE INDEX idx_user_userid ON user(userid);
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Debt Listing
```
1. Access main debt view page
2. Verify DataTables loads successfully
3. Check that only clients with debt > 0 appear
4. Verify all active clients (conditions = 0) are shown
5. Test search functionality on various columns
```

### Test Case 2: Optional Data Features
```
1. Enable payment history tracking
2. Verify last payment dates appear
3. Test sales bill date inclusion
4. Check return bill date functionality
5. Verify combined bill tracking
```

### Test Case 3: Large Dataset Performance
```
1. Create test environment with 10,000+ clients
2. Monitor page load times
3. Test search performance
4. Verify pagination works correctly
5. Check memory usage during operations
```

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

// Debug SQL query
echo "Query: " . $sQuery . "<br>";

// Debug result counts
echo "Total: $iTotal, Filtered: $iFilteredTotal<br>";
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [clientReportsController.md](clientReportsController.md) - Detailed client reports
- [clientController.php](#) - Customer management
- [DataTables Documentation](https://datatables.net/) - Frontend component docs

---

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