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

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

Related Controllers

• • clientReportsController.php - Customer reports
• • supplierReportsController.php - Supplier reports
• • clientController.php - Customer management
• • supplierController.php - Supplier management

---

🗄️ Database Tables

Customer Tables

🔑 Key Functions

1. Default Action - Customer Debt Aging Analysis

Location: Line 33

Purpose: Generate comprehensive customer debt aging report

Function Signature:

// 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:

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:

// 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:

$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

---

Workflow 2: Debt Age Calculation Logic

---

🌐 URL Routes & Actions

Both reports run automatically without user input parameters.

---

🧮 Calculation Methods

Debt Age Categorization Logic

// 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

-- 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

// Only include debt increases, exclude payments/reductions
WHERE clientdebtchangetype != 1

---

🔒 Security & Permissions

Authentication Required

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:

-- 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:

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:

-- 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

-- 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 - PHP 8.2 migration guide
• • 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