DebtHistoryReport Documentation

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

Related Controllers

---

🗄️ Database Tables

Customer Tables

Table NamePurposeKey Columns
**client**Customer master dataclientid, clientname, clientdebt, conditions
**clientdebtchange**Customer debt transaction historyclientdebtchangeid, clientid, clientdebtchangeamount, clientdebtchangedate, clientdebtchangetype
### Supplier Tables

Table NamePurposeKey Columns
**supplier**Supplier master datasupplierid, suppliername, suppliercurrentDebt, conditions
**supplierdebtchange**Supplier debt transaction historysupplierdebtchangeid, 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: 

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

┌─────────────────────────────────────────────────────────────┐
START: Generate Customer Debt Aging Report
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
1Initialize Tracking Variables
- Create empty customer array
- Initialize totals for each age period
- Set up debt change tracking variable
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
2Query Customers with Outstanding Debt
- Get all active customers (conditions = 0)
- Filter only those with debt > 0
- Order by debt amount (highest first)
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
3Process 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 │
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
4Aggregate Totals
- Sum all period totals across customers
- Calculate grand total debt
- Verify totals match current debt figures
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
5Display 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
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
1Initialize Debt Tracking
- debtChange = customer's current total debt
- totalCustomerDebt = 0
- Initialize each period debt = 0
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
2Process 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
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
3IF debtChange > 0, Process 31-60 Days
- Query debt changes 31-60 days ago
- Apply same logic as above
- debtChange -= periodDebt
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
4IF debtChange > 0, Process 61-90 Days
- Continue same pattern
- Keep reducing debtChange
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
5Continue for Remaining Periods
- 91-120 days
- 120+ days (everything else)
- Ensure all debt is accounted for
└────────────────────┬────────────────────────────────────────┘
▼
▼
┌─────────────────────────────────────────────────────────────┐
6Validation
- Sum all periods should equal original debt
- Store final totals in customer record
└─────────────────────────────────────────────────────────────┘

---

🌐 URL Routes & Actions

URL ParameterFunction CalledDescription
`do=` (empty)Default actionCustomer debt aging analysis
`do=supplier`Supplier analysisSupplier debt aging analysis
### No Parameters Required

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

Query Safety

---

🐛 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

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur