# Debt History Report Controller Documentation

**File**: `/controllers/debtHistoryReportController.php`  
**Purpose**: Generates comprehensive debt aging analysis for customers and suppliers with time-based categorization  
**Last Updated**: December 20, 2024  
**Total Functions**: 1  
**Lines of Code**: ~269

---

## 📋 Overview

The Debt History Report Controller provides sophisticated debt aging analysis by categorizing outstanding debts into time periods (0-30, 31-60, 61-90, 91-120, and 120+ days). It supports both customer and supplier debt analysis with detailed breakdowns showing how long each debt has been outstanding.

### Primary Functions
- [x] Customer debt aging analysis (0-30, 31-60, 61-90, 91-120, 120+ days)
- [x] Supplier debt aging analysis with same categorization
- [x] Current debt totals and historical debt breakdown
- [x] Complex debt calculation algorithms
- [x] Time-based debt categorization
- [x] Comprehensive debt analytics and insights

### Related Controllers
- [clientReportsController.php](clientReportsController.md) - Customer reports
- [supplierReportsController.php](#) - Supplier reports
- [clientController.php](#) - Customer management
- [supplierController.php](#) - Supplier management

---

## 🗄️ Database Tables

### Customer Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **client** | Customer master data | clientid, clientname, clientdebt, conditions |
| **clientdebtchange** | Customer debt transaction history | clientdebtchangeid, clientid, clientdebtchangeamount, clientdebtchangedate, clientdebtchangetype |

### Supplier Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **supplier** | Supplier master data | supplierid, suppliername, suppliercurrentDebt, conditions |
| **supplierdebtchange** | Supplier debt transaction history | supplierdebtchangeid, supplierid, supplierdebtchangeamount, supplierdebtchangedate, supplierdebtchangetype |

---

## 🔑 Key Functions

### 1. **Default Action** - Customer Debt Aging Analysis
**Location**: Line 33  
**Purpose**: Generate comprehensive customer debt aging report

**Function Signature**:
```php
// Triggered when: empty($do)
```

**Process Flow**:
1. Initialize debt tracking arrays and totals
2. Query all customers with outstanding debt
3. For each customer, categorize debt by age periods:
   - 0-30 days (last month)
   - 31-60 days (before month)
   - 61-90 days (before 2 months)
   - 91-120 days (before 3 months)
   - 120+ days (before 4+ months)
4. Calculate running balances ensuring no double-counting
5. Assign data to template for display

**Debt Aging Algorithm**:
```php
foreach ($allClientsCurrentDebts as $debt) {
    $debtChange = $debt['clientdebt']; // Start with current debt
    
    // 0-30 days
    $lastMonthDebt = R::getAll('SELECT clientdebtchangeamount FROM clientdebtchange 
                               WHERE clientid = ' . $debt['clientid'] . ' 
                               AND clientdebtchangetype != 1 
                               AND clientdebtchangedate BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()');
    
    $lastMonthTotal = array_sum(array_column($lastMonthDebt, 'clientdebtchangeamount'));
    if ($debtChange < $lastMonthTotal) $lastMonthTotal = $debtChange;
    
    $clientsArr[$debt['clientid']]['lastMonthDebt'] = $lastMonthTotal;
    $debtChange -= $lastMonthTotal;
    
    // Continue for other periods...
}
```

---

### 2. **supplier** - Supplier Debt Aging Analysis  
**Location**: Line 150  
**Purpose**: Generate comprehensive supplier debt aging report

**Function Signature**:
```php
// Triggered when: do=supplier
```

**Process Flow**:
1. Initialize supplier debt tracking arrays
2. Query all suppliers with outstanding debt
3. Apply same aging categorization logic as customers
4. Use supplier-specific debt change tables
5. Display results via supplier template

**Supplier Query Structure**:
```php
$allSuppliersCurrentDebts = R::getAll('SELECT suppliercurrentDebt, suppliername, supplierid 
                                      FROM supplier 
                                      WHERE conditions = 0 AND suppliercurrentDebt > 0 
                                      ORDER BY suppliercurrentDebt DESC');

// Similar aging logic but with supplier tables
$lastMonthSupplierDebt = R::getAll('SELECT supplierdebtchangeamount FROM supplierdebtchange 
                                   WHERE supplierid = ' . $debt['supplierid'] . ' 
                                   AND supplierdebtchangetype != 1 
                                   AND supplierdebtchangedate BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()');
```

---

## 🔄 Workflows

### Workflow 1: Customer Debt Aging Analysis
```
┌─────────────────────────────────────────────────────────────┐
│            START: Generate Customer Debt Aging Report      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Initialize Tracking Variables                           │
│     - Create empty customer array                           │
│     - Initialize totals for each age period                 │
│     - Set up debt change tracking variable                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Query Customers with Outstanding Debt                   │
│     - Get all active customers (conditions = 0)             │
│     - Filter only those with debt > 0                      │
│     - Order by debt amount (highest first)                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Process Each Customer                                   │
│     FOR EACH customer with debt:                            │
│       │                                                     │
│       ├─ Set debtChange = current debt amount               │
│       │                                                     │
│       ├─ Calculate 0-30 Days Debt:                          │
│       │   ├─ Query clientdebtchange for last 30 days       │
│       │   ├─ Sum debt increases (type != 1)                │
│       │   ├─ Take minimum of sum or remaining debt          │
│       │   └─ Subtract from debtChange                      │
│       │                                                     │
│       ├─ Calculate 31-60 Days Debt:                         │
│       │   ├─ Query for 31-60 days ago                      │
│       │   ├─ Apply same logic as above                     │
│       │   └─ Continue if debt remains                      │
│       │                                                     │
│       ├─ Calculate 61-90 Days Debt                          │
│       ├─ Calculate 91-120 Days Debt                         │
│       └─ Calculate 120+ Days Debt                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Aggregate Totals                                        │
│     - Sum all period totals across customers               │
│     - Calculate grand total debt                           │
│     - Verify totals match current debt figures             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Display Report                                          │
│     - Assign customer array to template                     │
│     - Assign period totals to template                      │
│     - Show aging analysis with breakdown                    │
│     - Provide drill-down capabilities                       │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Debt Age Calculation Logic
```
┌─────────────────────────────────────────────────────────────┐
│         START: Calculate Debt Age for One Customer         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Initialize Debt Tracking                               │
│     - debtChange = customer's current total debt           │
│     - totalCustomerDebt = 0                                │
│     - Initialize each period debt = 0                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Most Recent Period (0-30 days)                 │
│     - Query debt changes in last 30 days                   │
│     - Sum amounts where type != 1 (debt increases)         │
│     - periodDebt = MIN(querySum, debtChange)               │
│     - debtChange -= periodDebt                             │
│     - Store periodDebt in customer array                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. IF debtChange > 0, Process 31-60 Days                  │
│     - Query debt changes 31-60 days ago                    │
│     - Apply same logic as above                            │
│     - debtChange -= periodDebt                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. IF debtChange > 0, Process 61-90 Days                  │
│     - Continue same pattern                                 │
│     - Keep reducing debtChange                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Continue for Remaining Periods                         │
│     - 91-120 days                                          │
│     - 120+ days (everything else)                          │
│     - Ensure all debt is accounted for                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Validation                                              │
│     - Sum all periods should equal original debt           │
│     - Store final totals in customer record                │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Customer debt aging analysis |
| `do=supplier` | Supplier analysis | Supplier debt aging analysis |

### No Parameters Required
Both reports run automatically without user input parameters.

---

## 🧮 Calculation Methods

### Debt Age Categorization Logic
```php
// Start with current debt and work backwards through time periods
$debtChange = $customer['currentDebt'];

// Most Recent: 0-30 days
$recentDebtQuery = 'SELECT clientdebtchangeamount FROM clientdebtchange 
                   WHERE clientid = ? AND clientdebtchangetype != 1 
                   AND clientdebtchangedate BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()';

$recentDebtSum = array_sum($queryResults);
$period1Debt = min($recentDebtSum, $debtChange);
$debtChange -= $period1Debt;

// Continue for older periods only if debt remains
if ($debtChange > 0) {
    // 31-60 days ago
    // 61-90 days ago  
    // 91-120 days ago
    // 120+ days ago
}
```

### Date Range Calculations
```sql
-- Last 30 days
clientdebtchangedate BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()

-- 31-60 days ago  
clientdebtchangedate BETWEEN DATE_SUB(CURDATE(), INTERVAL 60 DAY) AND DATE_SUB(CURDATE(), INTERVAL 30 DAY)

-- 61-90 days ago
clientdebtchangedate BETWEEN DATE_SUB(CURDATE(), INTERVAL 90 DAY) AND DATE_SUB(CURDATE(), INTERVAL 60 DAY)

-- 91-120 days ago
clientdebtchangedate BETWEEN DATE_SUB(CURDATE(), INTERVAL 120 DAY) AND DATE_SUB(CURDATE(), INTERVAL 90 DAY)

-- 120+ days ago
datediff(CURDATE(), clientdebtchangedate) > 120
```

### Debt Type Filtering
```php
// Only include debt increases, exclude payments/reductions
WHERE clientdebtchangetype != 1
```

---

## 🔒 Security & Permissions

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

### Data Access Control
- Only shows active customers/suppliers (conditions = 0)
- Respects user session permissions
- No direct user input parameters to validate

### Query Safety
- Uses RedBean ORM for parameterized queries
- No direct SQL injection risks
- Proper date function usage

---

## 🐛 Common Issues & Troubleshooting

### 1. **Debt Totals Don't Match**
**Issue**: Period totals don't equal current debt  
**Cause**: Complex debt aging logic or data inconsistencies

**Debug**:
```sql
-- Verify current debt vs debt change sum
SELECT 
    c.clientdebt,
    SUM(CASE WHEN cdc.clientdebtchangetype = 0 THEN cdc.clientdebtchangeamount ELSE -cdc.clientdebtchangeamount END) as calculated_debt
FROM client c
LEFT JOIN clientdebtchange cdc ON c.clientid = cdc.clientid
WHERE c.clientid = [ID]
GROUP BY c.clientid;
```

### 2. **Missing Historical Data**
**Issue**: Old debts not appearing in aging categories  
**Cause**: Missing debt change records

**Debug**:
```sql
SELECT MIN(clientdebtchangedate), MAX(clientdebtchangedate), COUNT(*)
FROM clientdebtchange 
WHERE clientid = [ID];
```

### 3. **Performance Issues**
**Issue**: Report takes too long to generate  
**Cause**: Large number of customers or debt records

**Debug**:
```sql
-- Check data volume
SELECT COUNT(*) as active_customers FROM client WHERE conditions = 0 AND clientdebt > 0;
SELECT COUNT(*) as debt_records FROM clientdebtchange;
```

---

## 🧪 Testing Scenarios

### Test Case 1: Simple Debt Aging
```
1. Create customer with known debt
2. Create debt change records at specific dates
3. Run aging report
4. Verify debt appears in correct time periods
5. Check total calculations
```

### Test Case 2: Complex Multi-Period Debt
```
1. Create customer with multiple debt transactions
2. Spread transactions across all time periods
3. Generate aging report
4. Verify each period shows correct amounts
5. Ensure no double-counting occurs
```

### Test Case 3: Edge Cases
```
1. Test customers with zero debt
2. Test very old debt (> 120 days)
3. Test with missing debt change records
4. Verify proper handling of edge cases
```

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Indexes Required**:
   - `client(conditions, clientdebt)` - For active customers with debt
   - `clientdebtchange(clientid, clientdebtchangedate, clientdebtchangetype)` - For aging queries
   - `supplier(conditions, suppliercurrentDebt)` - For active suppliers with debt
   - `supplierdebtchange(supplierid, supplierdebtchangedate, supplierdebtchangetype)` - For supplier aging

2. **Query Optimization**:
   - Use efficient date range filtering
   - Consider materialized views for large datasets
   - Optimize for customers with many debt transactions

3. **Memory Management**:
   - Process customers in batches for large datasets
   - Clear arrays between customer processing
   - Monitor memory usage with many customers

### Performance Monitoring
```sql
-- Check for customers with many debt changes
SELECT clientid, COUNT(*) as debt_change_count
FROM clientdebtchange 
GROUP BY clientid 
ORDER BY debt_change_count DESC 
LIMIT 10;
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [clientReportsController.md](clientReportsController.md) - Customer reports
- [supplierController.php](#) - Supplier management
- [Database Schema Documentation](#) - Table relationships

---

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