Client Debt Discrepancy Discovery Controller Documentation

File: /controllers/clientdebtmessdiscoverer.php

Purpose: Discovers and reports discrepancies between actual customer debt records and calculated debt from transaction history, providing debt reconciliation tools

Last Updated: December 20, 2024

Total Functions: 1 main process + SQL procedures

Lines of Code: ~195

---

📋 Overview

The Client Debt Discrepancy Discovery Controller is a specialized tool designed to identify inconsistencies in customer debt calculations. It creates temporary tables to recalculate debt balances from transaction history and compares them with recorded debt amounts. This is essential for:

• • Debt Reconciliation: Finding customers whose recorded debt doesn't match transaction history
• • Data Integrity: Ensuring accounting accuracy across the system
• • Audit Trails: Providing detailed transaction-by-transaction debt calculations
• • System Maintenance: Cleaning up inconsistent debt records
• • Financial Accuracy: Maintaining reliable customer account balances

Primary Functions

• ✅ Create temporary calculation tables for debt analysis
• ✅ Recalculate customer debt from complete transaction history
• ✅ Compare calculated debt with recorded debt balances
• ✅ Identify customers with debt discrepancies
• ✅ Provide transaction-by-transaction audit trail
• ✅ Handle concurrent transaction processing
• ✅ Generate comprehensive discrepancy reports

Related Controllers

• • clientReportsController.php - Customer debt reporting
• • debtclientController.php - Debt analysis
• • clientController.php - Customer management
• • clientPayedDeptController.php - Payment processing

---

🗄️ Database Tables

Temporary Analysis Tables (Created by Process)

🔑 Key Functions

1. Default Action - Debt Discrepancy Analysis

Location: Line 65

Purpose: Main process that performs complete debt reconciliation analysis

Process Flow:

Phase 1: Table Structure Creation

// Create temporary tables if they don't exist
$sqlQuery[] = 'CREATE TABLE IF NOT EXISTS `clienttemp` (
                `clientid` int(11) NOT NULL AUTO_INCREMENT,
                `clientdebtcalc` float NOT NULL,
                PRIMARY KEY (`clientid`)
              ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;';

$sqlQuery[] = 'CREATE TABLE IF NOT EXISTS `clientdebtchangetemp` (
                `clientdebtchangeid` int(11) NOT NULL,
                `clientid` int(11) NOT NULL,
                `clientdebtchangebefore` float NOT NULL,
                `clientdebtchangeamount` float NOT NULL,
                `clientdebtchangetype` int(11) NOT NULL,
                `clientdebtchangeafter` float NOT NULL,
                `tablename` varchar(256) NOT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=latin1;';

Phase 2: Data Population

// Initialize clienttemp with all customers (except ID 1)
$sqlQuery[] = 'INSERT IGNORE INTO clienttemp
               SELECT clientid,0 FROM client where clientid !=1';

Phase 3: Incremental Transaction Loading

// Get last processed transaction ID
$last_clientdebtchangeid_inTemp = (int) $programSettingEX->runSqlQueryGetSingleResult(
    'select clientdebtchangeid from clientdebtchangetemp order by clientdebtchangeid desc limit 1'
);

// Insert only new transactions
$sqlQuery[] = 'INSERT INTO clientdebtchangetemp
               SELECT clientdebtchangeid,clientid,clientdebtchangebefore,clientdebtchangeamount,clientdebtchangetype,clientdebtchangeafter,tablename
               FROM clientdebtchange
               where del = 0 and tablename != "clientDeficitController.php" and clientid !=1 and clientdebtchangeid > ' . $last_clientdebtchangeid_inTemp . ';';

Phase 4: Data Type Correction

// Reverse clientPayedDeptController.php transaction types (they are entered in reverse)
$sqlQuery[] = 'UPDATE clientdebtchangetemp
               SET clientdebtchangetype = CASE clientdebtchangetype WHEN 0 THEN 1 WHEN 1 THEN 0 ELSE clientdebtchangetype END
               where tablename="clientPayedDeptController.php" and id > ' . $last_clientdebtchangeid_inTemp . ';';

Phase 5: Debt Recalculation Procedure

CREATE PROCEDURE ROWPERROW(IN i INT)
BEGIN
    DECLARE n INT DEFAULT 0;
    DECLARE debtbefore INT DEFAULT 0;
    DECLARE clientdebtchangetypeinrow INT DEFAULT 0;
    DECLARE clientidinrow INT DEFAULT 0;
    
    SELECT COUNT(*) FROM clientdebtchangetemp INTO n;
    
    WHILE i <= n DO
        -- Get client ID for current row
        SELECT clientid FROM clientdebtchangetemp where id=i INTO clientidinrow;
        SET debtbefore=0;
        
        -- Calculate debt before from previous transactions
        SELECT clientdebtchangeafter FROM clientdebtchangetemp 
        where id<i and clientid = clientidinrow 
        order by id desc limit 1 INTO debtbefore;
        
        -- Update debt before in current row
        update clientdebtchangetemp set clientdebtchangebefore = debtbefore where id=i;
        
        -- Calculate debt after based on transaction type
        SELECT clientdebtchangetype FROM clientdebtchangetemp where id=i INTO clientdebtchangetypeinrow;
        IF clientdebtchangetypeinrow = 1 THEN
           -- Type 1: Debt decrease (payment)
           update clientdebtchangetemp set clientdebtchangeafter = clientdebtchangebefore - clientdebtchangeamount where id = i;
        ELSE
           -- Type 0: Debt increase (sale/charge)
           update clientdebtchangetemp set clientdebtchangeafter = clientdebtchangebefore + clientdebtchangeamount where id = i;
        END IF;
        
        -- Update final calculated debt in clienttemp
        update clienttemp set clientdebtcalc = (select clientdebtchangeafter from clientdebtchangetemp where id=i) where clientid=clientidinrow;
        
        SET i = i + 1;
    END WHILE;
End;

Phase 6: Results Display

// Get customers with debt discrepancies
$shownData = $clientEX->getClientsDebtAfterRevision();
// Display results showing calculated vs recorded debt

---

🔄 Workflows

Workflow 1: Complete Debt Reconciliation Process

Workflow 2: Incremental Processing Strategy

---

🌐 URL Routes & Actions

🔧 Technical Implementation

SQL Procedures Used

The system creates a sophisticated stored procedure ROWPERROW that:

• • Processes transactions in chronological order
• • Maintains running debt balances for each customer
• • Handles different transaction types correctly
• • Updates both before/after amounts for audit trails

Data Type Corrections

-- Correct reversed transaction types from payment controller
UPDATE clientdebtchangetemp
SET clientdebtchangetype = CASE clientdebtchangetype 
    WHEN 0 THEN 1 
    WHEN 1 THEN 0 
    ELSE clientdebtchangetype 
END
WHERE tablename="clientPayedDeptController.php";

Incremental Processing Benefits

1. Performance: Only processes new transactions on subsequent runs

2. Scalability: Handles large transaction volumes efficiently

3. Reliability: Maintains calculation history for audit purposes

4. Flexibility: Can be run frequently without performance impact

---

📊 Analysis Results

Discrepancy Types Identified

1. Calculation Errors: Where recorded debt ≠ calculated debt

2. Missing Transactions: Gaps in transaction history

3. Type Errors: Incorrect transaction type assignments

4. Data Corruption: Inconsistent before/after amounts

Report Columns

The analysis provides:

• • Customer ID and name
• • Current recorded debt (from client table)
• • Calculated debt (from transaction history)
• • Difference amount
• • Last transaction date
• • Transaction count

---

🔒 Security & Permissions

Data Protection

• • Creates temporary tables that don't affect live data
• • Uses transaction isolation for data consistency
• • Preserves original data while performing analysis

Access Control

• • Requires standard authentication
• • Uses session-based user identification
• • Maintains audit trail of analysis execution

Performance Safeguards

• • Excludes system customer (ID=1) from analysis
• • Filters out deleted transactions
• • Uses efficient incremental processing

---

📊 Performance Considerations

Optimization Strategies

1. Incremental Processing: Only processes new transactions

2. Indexed Queries: Uses primary keys for efficient lookups

3. Memory Management: Uses temporary tables to avoid large result sets

4. Stored Procedures: Leverages database-level processing

Resource Requirements

• • Temporary disk space for clientdebtchangetemp table
• • Processing time proportional to new transactions
• • Memory for stored procedure execution
• • Network bandwidth for result display

Scalability Factors

• • First run processes all historical data (slow)
• • Subsequent runs only process new transactions (fast)
• • Performance degrades with very large customer bases
• • Consider pagination for enterprise-scale implementations

---

🐛 Common Issues & Troubleshooting

1. Procedure Creation Errors

Issue: ROWPERROW procedure creation fails

Cause: Database permission issues or syntax errors

Solution:

-- Check procedure exists
SHOW PROCEDURE STATUS LIKE 'ROWPERROW';

-- Manually drop and recreate if needed
DROP PROCEDURE IF EXISTS ROWPERROW;

2. Large Dataset Performance

Issue: Process takes very long time on first run

Cause: Processing entire transaction history

Solutions:

• • Run during off-peak hours
• • Consider processing in smaller date ranges
• • Monitor database performance during execution

3. Discrepancy Investigation

Issue: Many customers show debt discrepancies

Cause: May indicate systematic data issues

Investigation Steps:

-- Check transaction type distribution
SELECT tablename, clientdebtchangetype, COUNT(*) 
FROM clientdebtchangetemp 
GROUP BY tablename, clientdebtchangetype;

-- Find customers with largest discrepancies
SELECT c.clientname, c.clientdebt, ct.clientdebtcalc, 
       (ct.clientdebtcalc - c.clientdebt) as difference
FROM client c
JOIN clienttemp ct ON c.clientid = ct.clientid
WHERE ABS(ct.clientdebtcalc - c.clientdebt) > 100
ORDER BY ABS(ct.clientdebtcalc - c.clientdebt) DESC;

4. Memory Issues

Issue: Process runs out of memory or disk space

Cause: Very large clientdebtchangetemp table

Solutions:

• • Increase MySQL temp table size
• • Add disk space for temporary tables
• • Process in smaller batches

---

🧪 Testing Scenarios

Test Case 1: Clean Data Verification

1. Run analysis on known-good customer data
2. Verify no discrepancies appear
3. Check that calculated debt = recorded debt
4. Confirm transaction counts are accurate

Test Case 2: Known Discrepancy Detection

1. Manually create debt discrepancy (update client debt)
2. Run analysis process
3. Verify discrepancy appears in report
4. Check difference amount is correct

Test Case 3: Incremental Processing

1. Run full analysis and record results
2. Add new transactions to system
3. Run analysis again
4. Verify only new transactions were processed
5. Confirm final results include new data

Test Case 4: Performance Testing

1. Measure runtime on various data sizes
2. Test incremental vs full processing times
3. Monitor database resource usage
4. Verify memory and disk space consumption

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • clientReportsController.md - Customer debt reporting
• • Database Schema Documentation - Table relationships
• • System Maintenance Guide - Regular maintenance procedures

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur