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

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

Related Controllers

• • clientReportsController.php - Detailed client reports
• • clientController.php - Customer management
• • clientPayedDeptController.php - Payment processing
• • sellbillController.php - Sales operations
• • returnsellbillController.php - Sales returns

---

🗄️ Database Tables

Primary Tables (Direct Operations)

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

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:

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:

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

---

Workflow 2: AJAX Data Loading

---

🌐 URL Routes & Actions

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

$plussum = 0;
$minussum = 0;
foreach ($shownData as $data) {
    if ($data->clientdebtchangeafter > 0) {
        $plussum += $data->clientdebtchangeafter;
    } else {
        $minussum += $data->clientdebtchangeafter;
    }
}

Search Field Processing

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

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

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

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

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

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

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

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:

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

// 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 - PHP 8.2 migration guide
• • clientReportsController.md - Detailed client reports
• • clientController.php - Customer management
• • DataTables Documentation - Frontend component docs

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur