Customer Account Matching Controller Documentation

File: /controllers/customerAccountMatching.php

Purpose: Handles customer account reconciliation, debt adjustments, and account matching with automated accounting entries

Last Updated: December 20, 2024

Total Functions: 15

Lines of Code: ~440

---

📋 Overview

The Customer Account Matching Controller is a sophisticated financial reconciliation system that handles customer account matching, debt adjustments, and automated accounting entries. This controller is crucial for maintaining accurate customer balances and handling discrepancies between system records and actual customer accounts. The system features:

• • Customer account reconciliation and matching
• • Automated debt adjustment calculations
• • Journal entry generation for account corrections
• • Account matching approval workflows
• • Customer account balance corrections
• • Audit trail for all account adjustments
• • RedBeanPHP-based data operations with DAO integration

Primary Functions

• ✅ Customer account balance matching
• ✅ Automated debt adjustment calculations
• ✅ Journal entry generation for corrections
• ✅ Account matching approval workflows
• ✅ Account adjustment audit trail
• ✅ Customer balance correction processing
• ✅ Financial reconciliation reporting

Related Controllers

• • clientController.php - Customer management
• • dailyentry.php - Journal entries
• • clientdebtchange.php - Debt tracking
• • accountstree.md - Chart of accounts
• • clientReportsController.md - Customer reporting

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Account Matching Interface

Location: Line 32-35

Purpose: Display account matching form

if (empty($do)) {
    $smarty->display("header.html");
    $smarty->display("customerAccountMatchingview/add.html");
    $smarty->display("footer.html");
}

---

2. show Action - Account Matching History

Location: Line 40-44

Purpose: Display historical account matching records with search capabilities

elseif ($do == "show") {
    $smarty->display("header.html");
    $smarty->display("customerAccountMatchingview/show.html");
    $smarty->display("footer.html");
}

---

3. edit Action - Edit Account Matching Record

Location: Line 45-59

Purpose: Load and display existing account matching record for editing

Process Flow:

elseif ($do == "edit") {
    $id = filter_input(INPUT_GET, 'id');
    $editdata = R::load('customerqccountmatching', $id);
    
    // Enhance with customer name
    $client = R::getRow('SELECT * FROM `client` WHERE clientid = ?', [$editdata->clientid]);
    $editdata->clientname = $client['clientname'];
    
    $smarty->assign('editdata', $editdata);
    $smarty->display("header.html");
    $smarty->display("customerAccountMatchingview/edit.html");
    $smarty->display("footer.html");
}

---

4. savedata Action - Account Matching Processing Engine

Location: Line 60-61, Function: Line 132-261

Purpose: Process account matching requests and handle debt adjustments

Function Signature:

function savedata()

Core Processing Logic:

1. Parameter Processing:

$identical = filter_input(INPUT_POST, 'identical'); // 1 = match, 0 = adjust
$clientid = filter_input(INPUT_POST, 'clientid');
$newclientdebt = filter_input(INPUT_POST, 'newclientdebt');
$comment = filter_input(INPUT_POST, 'comment');
$id = filter_input(INPUT_POST, 'id'); // For updates

2. Record Creation/Update:

if (!$id) {
    // New matching record
    $realestates = R::dispense('customerqccountmatching');
    $realestates->conditions = 0;
    $realestates->addtoday = $today;
    $realestates->adduserid = $userid;
} else {
    // Update existing record
    $realestates = R::load('customerqccountmatching', $id);
    $realestates->updatetoday = $today;
    $realestates->updateuserid = $userid;
}

3. Client Balance Handling with Concurrency Control:

// Get current client debt with locking mechanism
$clientdataSP = getClientDataFromClientInUseSP($clientid);
$debtbefore = $clientdataSP->clientdebt;

if ($identical == 1) {
    // Account matches - no adjustment needed
    $newclientdebt = $clientdebt;
    $comment = 'مطابقة حساب عميل';
} else {
    // Account mismatch - update client debt
    R::exec("UPDATE `client` SET clientdebt = $newclientdebt WHERE clientid = ? ", [$clientid]);
    markClientAsNOTInUse($clientid);
}

4. Debt Change Tracking:

// Determine debt change direction
if ($newclientdebt > $clientdebt) {
    $clientdebtchangetype = 0; // Debt increase
} else {
    $clientdebtchangetype = 1; // Debt decrease
}
$amount = ABS($clientdebt - $newclientdebt);

// Insert debt change record
R::exec("INSERT INTO `clientdebtchange`(...) 
         VALUES ($clientid, $clientdebt, $amount, $clientdebtchangetype, 
                'مطابقة حساب عميل', $customerqccountmatchingid, $newclientdebt, ...)");

5. Automated Journal Entry Generation:

For Debt Increases (Customer owes more):

if ($clientdebtchangetype == 0) {
    // DR: Customer Account (Asset)
    $dailyEntryDebtor->value = $amount;
    $dailyEntryDebtor->accountstreeid = $dataClient->treeId; // Customer account
    
    // CR: Discount Earned (Income)  
    $dailyEntryCreditor->value = $amount;
    $dailyEntryCreditor->accountstreeid = 146; // ايرادات الخصم المكتسب
    
    insertEntery($dailyEntry, $dailyEntryDebtorArray, $dailyEntryCreditorArray, 1);
}

For Debt Decreases (Customer owes less):

else {
    // DR: Discount Allowed (Expense)
    $dailyEntryDebtor->value = $amount;  
    $dailyEntryDebtor->accountstreeid = 398; // خصم مسموح به
    
    // CR: Customer Account (Asset reduction)
    $dailyEntryCreditor->value = $amount;
    $dailyEntryCreditor->accountstreeid = $dataClient->treeId; // Customer account
    
    insertEntery($dailyEntry, $dailyEntryDebtorArray, $dailyEntryCreditorArray, 1);
}

---

5. showajax Action - DataTables Integration

Location: Line 266-366

Purpose: Provide AJAX data for account matching history grid

Function Signature:

function showajax()

Search Parameters:

$start_date = filter_input(INPUT_POST, 'start_date');
$end_date = filter_input(INPUT_POST, 'end_date');
$conditions = filter_input(INPUT_POST, 'conditions'); // Record status
$clientid = filter_input(INPUT_POST, 'clientid');
$identical = filter_input(INPUT_POST, 'identical'); // Match type

DataTables Response Structure:

$output = array(
    "draw" => intval($_POST["draw"]),
    "recordsTotal" => count($rResult),
    "recordsFiltered" => $totals,
    "data" => array()
);

foreach ($rResult as $row) {
    $sub_array = array();
    $sub_array[] = $row["id"];
    $sub_array[] = $row["clientname"];
    $sub_array[] = ($row["identical"] == 1) ? ' مطابق ' : ' غير مطابق ';
    $sub_array[] = $row["clientdebt"];
    $sub_array[] = $row["newclientdebt"];
    $sub_array[] = $row["comment"];
    $sub_array[] = $row["addtoday"];
    $sub_array[] = $row['employeename'];
    // Action buttons based on deletion status
    if ($row["del"] < 2) {
        $sub_array[] = '<a href="...?do=edit&id=' . $row["id"] . '" class="btn btn-default btn-lg editicon"></a>';
        $sub_array[] = '<a href="...?do=remove&id=' . $row["id"] . '" class="btn btn-default btn-lg deleteicon"></a>';
    } else {
        $sub_array[] = '<a href="...?do=edit&id=' . $row["id"] . '" class="btn btn-default btn-lm">تفاصيل</a>';
        $sub_array[] = 'محذوف ';
    }
    $output['data'][] = $sub_array;
}

---

6. select2client Action - Customer Search

Location: Line 113-127

Purpose: Provide AJAX customer search for Select2 dropdown

Function Signature:

function select2client()

Search Implementation:

$name = $_POST['searchTerm'];
$productsData = R::getAll("SELECT clientid, clientdebt, CONCAT(clientname,'/',clientphone) as texts
                          FROM client WHERE conditions = 0 
                          AND CONCAT(clientname,'/',clientphone) LIKE '%" . $name . "%' 
                          LIMIT 50");

$return_arr = array();
foreach ($productsData as $pro) {
    $row_array = array();
    $row_array['id'] = $pro['clientid'];
    $row_array['text'] = $pro['texts'];
    $row_array['debt'] = $pro['clientdebt']; // Additional data for UI
    array_push($return_arr, $row_array);
}
echo json_encode($return_arr);

---

7. getClientDataFromClientInUseSP() - Concurrency Control

Location: Line 407-429

Purpose: Safely retrieve client data with locking mechanism

Function Signature:

function getClientDataFromClientInUseSP($clientid)

Concurrency Control Logic:

if ($clientid == 1) {
    // Cash customer - no locking needed
    $client_data = $clientDAO->load($clientid);
} elseif ($clientid > 1) {
    $noOfTries = 0;
    $client_data = $clientExt->callClientInUseSP($clientid);
    
    // Wait for client to become available
    while ($client_data->clientdebt == 'in_use') {
        sleep(1);
        $noOfTries++;
        if ($noOfTries > 15) { // 15 second timeout
            // Force unlock client
            liveBackupComment("-- force free client=$clientid with redbean");
            R::exec('UPDATE client SET inUse = 0 WHERE clientid = ' . $clientid);
        }
        $client_data = $clientExt->callClientInUseSP($clientid);
    }
}
return $client_data;

---

8. markClientAsNOTInUse() - Client Unlocking

Location: Line 431-439

Purpose: Release client lock after processing

function markClientAsNOTInUse($clientid) {
    global $clientExt;
    if ($clientid == 1) {
        // Cash customer - no locking
    } else {
        $clientExt->markClientAsNOTInUse($clientid);
    }
}

---

9. agrees Action - Approval Workflow

Location: Line 70-74, Function: Line 77-94

Purpose: Handle account matching approvals

Function Signature:

function agrees()

Approval Processing:

$id = filter_input(INPUT_POST, 'id');
$agrees = filter_input(INPUT_POST, 'agrees'); // Approval status

$tables = R::load('customerqccountmatching', $id);
$tables->agrees = $agrees;
$tables->agreetoday = $today;
$tables->agreeuserid = $userid;

try {
    R::store($tables);
    echo 1; // Success
} catch (Exception $e) {
    echo 0; // Failure
}

---

10. remove Action - Account Matching Deletion

Location: Line 72-74, Function: Line 388-405

Purpose: Soft delete account matching records

Function Signature:

function remove()

Soft Delete Implementation:

$id = filter_input(INPUT_GET, 'id');
$tables = R::load('customerqccountmatching', $id);
$tables->conditions = 1; // Mark as deleted
$tables->deltoday = $today;
$tables->deluserid = $userid;

try {
    R::store($tables);
    header("location:customerAccountMatching.php?do=show");
    exit();
} catch (Exception $e) {
    echo 0;
}

---

🔄 Workflows

Workflow 1: Account Matching Process

---

🌐 URL Routes & Actions

Account Matching (do=savedata):

• • clientid - Customer ID
• • identical - Match status (1=match, 0=adjust)
• • newclientdebt - Correct balance (if adjusting)
• • comment - Explanation/notes
• • id - Record ID (for updates)

AJAX Search (do=select2client):

• • searchTerm - Customer search text

History Search (do=showajax):

• • start_date - Start date filter
• • end_date - End date filter
• • conditions - Record status filter
• • clientid - Customer filter
• • identical - Match type filter

---

🧮 Calculation Methods

Debt Change Calculation

$clientdebt = $debtbefore; // Current system balance
$newclientdebt = $_POST['newclientdebt']; // Correct balance

// Determine change direction
if ($newclientdebt > $clientdebt) {
    $clientdebtchangetype = 0; // Increase
} else {
    $clientdebtchangetype = 1; // Decrease
}

// Calculate absolute change amount
$amount = ABS($clientdebt - $newclientdebt);

Journal Entry Amounts

// Both debit and credit entries use the same amount
$dailyEntryDebtor->value = $amount;
$dailyEntryCreditor->value = $amount;

// Journal entry description
$dailyEntry->entryComment = ' مطابقة حساب العميل ' . $clientData->clientname;

---

🔒 Security & Permissions

Concurrency Control

// Prevent simultaneous edits to customer balance
$client_data = $clientExt->callClientInUseSP($clientid);

// Wait for exclusive access
while ($client_data->clientdebt == 'in_use') {
    sleep(1);
    $noOfTries++;
    if ($noOfTries > 15) {
        // Force unlock after timeout
        R::exec('UPDATE client SET inUse = 0 WHERE clientid = ' . $clientid);
    }
}

Input Sanitization

// Proper input filtering
$identical = filter_input(INPUT_POST, 'identical', FILTER_VALIDATE_INT);
$clientid = filter_input(INPUT_POST, 'clientid', FILTER_VALIDATE_INT);  
$newclientdebt = filter_input(INPUT_POST, 'newclientdebt', FILTER_VALIDATE_FLOAT);
$comment = filter_input(INPUT_POST, 'comment', FILTER_SANITIZE_STRING);

SQL Injection Prevention

• • Mixed Protection: Uses both parameterized queries and direct SQL
• • RedBeanPHP: Provides parameterized query protection
• • Risk Areas: Some direct SQL concatenation present

Safe Pattern:

$client = R::getRow('SELECT * FROM `client` WHERE clientid = ?', [$editdata->clientid]);

Risky Pattern:

R::exec("UPDATE `client` SET clientdebt = $newclientdebt WHERE clientid = ? ", [$clientid]);

---

📊 Performance Considerations

Database Optimization Tips

1. Indexes Required:

- customerqccountmatching(clientid, addtoday, conditions)

- client(clientid, inUse)

- clientdebtchange(clientid, tablename)

- dailyentry(dailyentryid)

2. Concurrency Performance:

- Client locking mechanism prevents deadlocks

- Timeout mechanism prevents indefinite waits

- Force unlock prevents system hangs

3. Query Optimization:

- Uses stored procedures for client locking

- Batch operations in single transaction

- Efficient JOIN queries for reporting

---

🐛 Common Issues & Troubleshooting

1. Client Locking Issues

Issue: Customer accounts getting permanently locked

Cause: Incomplete transactions or system crashes

Debug:

-- Check locked customers
SELECT clientid, clientname, inUse FROM client WHERE inUse = 1;

-- Manual unlock (emergency only)
UPDATE client SET inUse = 0 WHERE clientid = [ID];

2. Journal Entry Imbalance

Issue: Journal entries don't balance

Cause: Incorrect account assignments or amount calculations

Debug:

-- Check journal entry balance
SELECT dailyentryid, 
       (SELECT SUM(value) FROM dailyentrydebtor WHERE dailyentryid = de.dailyentryid) as total_dr,
       (SELECT SUM(value) FROM dailyentrycreditor WHERE dailyentryid = de.dailyentryid) as total_cr
FROM dailyentry de 
WHERE entryComment LIKE '%مطابقة حساب%';

3. Debt Change Tracking Issues

Issue: Debt changes not properly recorded

Cause: Missing clientdebtchange records

Debug:

-- Verify debt change record
SELECT * FROM clientdebtchange 
WHERE tablename = 'customerqccountmatching' 
AND clientdebtchangemodelid = [MATCHING_ID];

-- Check customer balance history
SELECT clientdebtchangedate, clientdebtchangebefore, clientdebtchangeafter, processname
FROM clientdebtchange 
WHERE clientid = [CUSTOMER_ID] 
ORDER BY clientdebtchangedate DESC;

4. Account Tree Assignment Issues

Issue: Customer accounts not properly linked to chart of accounts

Cause: Missing or incorrect treeId assignments

Debug:

-- Check customer account tree assignments
SELECT c.clientid, c.clientname, c.treeId, at.accounttreename
FROM client c
LEFT JOIN accountstree at ON c.treeId = at.accountstreeid
WHERE c.treeId IS NULL OR at.accountstreeid IS NULL;

---

🧪 Testing Scenarios

Test Case 1: Identical Account Matching

1. Create customer with known balance
2. Submit matching form with identical = 1
3. Verify no balance change occurs
4. Check matching record created with identical status
5. Verify no journal entry generated

Test Case 2: Account Adjustment (Increase)

1. Create customer with balance 100
2. Submit matching with new balance 150
3. Verify customer debt updated to 150
4. Check clientdebtchange record created
5. Verify journal entry: DR Customer 50, CR Discount Earned 50

Test Case 3: Account Adjustment (Decrease)

1. Create customer with balance 100
2. Submit matching with new balance 50
3. Verify customer debt updated to 50
4. Check clientdebtchange record created
5. Verify journal entry: DR Discount Allowed 50, CR Customer 50

Test Case 4: Concurrency Control

1. Start account matching for customer A
2. Simultaneously try to edit customer A balance elsewhere
3. Verify second operation waits or fails appropriately
4. Complete first operation
5. Verify customer unlocked for subsequent operations

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • dailyentry.md - Journal entry system
• • accountstree.md - Chart of accounts
• • clientController.php - Customer management
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur