Client Deficit Controller Documentation

File: /controllers/clientDeficitController.php

Purpose: Manages customer debt adjustments and client deficit settlements

Last Updated: December 20, 2024

Total Functions: 7

Lines of Code: ~398

---

📋 Overview

The Client Deficit Controller is a specialized financial module that handles customer debt adjustments and deficit settlements. It provides functionality for:

• Manual customer debt adjustments (increase/decrease)
• Client deficit settlements through deductions or additions
• Comprehensive debt change tracking and history
• Automatic daily journal entry generation
• Client-in-use concurrency management
• Transaction rollback capabilities
• Live backup commentary for audit trails

Primary Functions

✅ Manual debt adjustments for customers
✅ Debt settlement processing (deduction/addition)
✅ Automated accounting journal entries
✅ Client concurrency control (in-use management)
✅ Transaction history viewing and printing
✅ Debt change audit trail
✅ Live backup logging
✅ User permission enforcement
✅ Error handling with transaction rollback

Related Controllers

• clientController.php - Customer management
• clientReportsController.php - Customer reports
• clientPayedDeptController.php - Customer payment processing
• dailyentry.php - Journal entry management
• sellbillController.php - Sales operations

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
clientdebtchange	Customer debt transaction log	clientdebtchangeid, clientid, clientdebtchangeamount, clientdebtchangetype, clientdebtchangebefore, clientdebtchangeafter, clientdebtchangedate, tablename, processname, comment
client	Customer master data	clientid, clientname, clientdebt, treeId, inUse, userid

### Accounting Tables (Journal Entries)

Table Name	Purpose	Key Columns
accountstree	Chart of accounts	accountstreeid, accountname, accounttype
dailyentry	Journal entry headers	dailyentryid, entryComment, entryDate, userid
dailyentrycreditor	Credit side entries	dailyentrycreditorid, dailyentryid, accountstreeid, value
dailyentrydebtor	Debit side entries	dailyentrydebtorид, dailyentryid, accountstreeid, value

### Reference Tables

Table Name	Purpose	Key Columns
youtubelink	Tutorial/help links	youtubelinkid, title, url
programsettings	System configuration	programsettingsid, clientsearchvalue

---

🔑 Key Functions

1. Default Action / Add Form Display

Location: Line 94

Purpose: Display the debt adjustment form interface

Process Flow:

1. Include authentication check

2. Load client search configuration

3. Display add form template

4. Set custom validation flag

Template: clientDeficitview/add.html

---

2. add() - Process Debt Adjustment

Location: Line 218

Purpose: Core function for processing customer debt adjustments with full accounting integration

Function Signature:

function add()

Process Flow:

1. Input Processing:

- Extract POST data (clientid, debtchangetype, debtamount, comment)

- Load current client data using concurrency-safe SP

2. Client Concurrency Management:

- Call getClientDataFromClientInUseSP() to lock client

- Prevent concurrent debt modifications

- Handle timeout scenarios (15-second limit)

3. Debt Calculation:

   if ($debtchangetype == 1) { // Deduction
       $debtAfter = $debtbefore - $debtamount;
       $processname = "تسوية عجز عميل بالخصم";
   } else { // Addition
       $debtAfter = $debtbefore + $debtamount;
       $processname = "تسوية عجز عميل بالزيادة";
   }
   ```

4. **Record Creation**:
   - Insert `clientdebtchange` record with full audit trail
   - Update client master debt balance
   - Mark client as not in use

5. **Accounting Integration**:
   - Generate automatic journal entries
   - **For Additions (type=0)**: Debit Customer, Credit Discount Earned (146)
   - **For Deductions (type=1)**: Debit Allowed Discount (398), Credit Customer

**Accounting Entries**:

php

// Addition (type=0): Customer owes more

DR Customer Account (from client.treeId) $amount

CR Discount Earned (146) $amount

// Deduction (type=1): Customer owes less

DR Allowed Discount (398) $amount

CR Customer Account (from client.treeId) $amount

**Returns**: `clientdebtchangeid` - ID of created transaction record

---

### 3. **show()** - Display Transaction History
**Location**: Line 341  
**Purpose**: Query and display debt change history with filtering

**Function Signature**:

php

function show($queryString)

**Process Flow**:
1. Use `ClientdebtchangeMySqlExtDAO` to query with dynamic filters
2. Filter by table name ("clientDeficitController.php")
3. Apply date range and client ID filters
4. Assign results to Smarty template

**Query Method**:

php

$shownData = $clientDeptChangeExt->queryWithQueryString($queryString, "clientDeficitController.php");

---

### 4. **edit()** - Load Transaction for Viewing/Printing
**Location**: Line 352  
**Purpose**: Load specific debt change transaction for display

**Function Signature**:

php

function edit($clientdebtchangeid)

**Process Flow**:
1. Load transaction record by ID
2. Return for template assignment

**Template Usage**: `clientDeficitview/editprint.html`

---

### 5. **getClientDataFromClientInUseSP()** - Concurrency Control
**Location**: Line 360  
**Purpose**: Safely retrieve client data using stored procedure with locking mechanism

**Function Signature**:

php

function getClientDataFromClientInUseSP($clientid)

**Process Flow**:
1. **Special Case**: If `clientid = 1`, load directly (no locking needed)
2. **Standard Process**:
   - Call `callClientInUseSP()` to mark client as in-use
   - Check if client is already locked (`clientdebt == 'in_use'`)
   - Wait with 1-second sleep intervals
   - Force unlock after 15 attempts (15 seconds)
   - Use direct SQL execution for emergency unlock

**Concurrency Safety**:

php

while ($client_data->clientdebt == 'in_use') {

sleep(1);

$noOfTries++;

if ($noOfTries > 15) {

// Emergency unlock using RedBean direct execution

R::exec('UPDATE client SET inUse = 0 where clientid = ' . $clientid);

}

---

### 6. **markClientAsNOTInUse()** - Release Client Lock
**Location**: Line 383  
**Purpose**: Release client from in-use status after transaction completion

**Function Signature**:

php

function markClientAsNOTInUse($clientid)

**Process Flow**:
1. Skip for `clientid = 1` (never locked)
2. Call extended DAO method to update in-use flag

---

### 7. **liveBackupComment()** - Audit Logging
**Location**: Line 392  
**Purpose**: Write transaction details to daily backup/audit log

**Function Signature**:

php

function liveBackupComment($txt)

**Process Flow**:
1. Create/append to daily log file (`backup/YYYY-MM-DD.txt`)
2. Add formatted comment with delimiters
3. Include timestamp and operation details

**Log Format**:

-- ----------------------------------------------------------------------------------------------------

-- clientDeficitController add - with clientid = 123

-- ----------------------------------------------------------------------------------------------------

---

### 8. **getClients()** - Client Dropdown Data
**Location**: Line 211  
**Purpose**: Load all client data for dropdown selection

**Function Signature**:

php

function getClients()

**Returns**: Array of client objects for form population

---

## 🔄 Workflows

### Workflow 1: Customer Debt Adjustment Process

┌─────────────────────────────────────────────────────────────┐

│ START: Customer Debt Adjustment │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 1. User Input Collection │

│ - Select customer (clientid) │

│ - Choose adjustment type (0=add, 1=deduct) │

│ - Enter adjustment amount │

│ - Add comment/reason │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 2. Client Concurrency Lock │

│ - Call getClientDataFromClientInUseSP() │

│ - Wait if client is in-use by another process │

│ - Force unlock if timeout (15 seconds) │

│ - Get current debt balance │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 3. Calculate New Debt Balance │

│ IF adjustment_type = 1 (deduction): │

│ │ │

│ ├─→ new_debt = current_debt - adjustment_amount │

│ └─→ process_name = "تسوية عجز عميل بالخصم" │

│ │

│ ELSE (addition): │

│ │ │

│ ├─→ new_debt = current_debt + adjustment_amount │

│ └─→ process_name = "تسوية عجز عميل بالزيادة" │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 4. Record Transaction │

│ - Insert clientdebtchange record │

│ - Update client master debt balance │

│ - Mark client as not in-use │

│ - Add live backup comment │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 5. Generate Accounting Entries │

│ IF addition (type=0): │

│ │ │

│ ├─→ DR Customer Account (client.treeId) │

│ └─→ CR Discount Earned (146) │

│ │

│ ELSE deduction (type=1): │

│ │ │

│ ├─→ DR Allowed Discount (398) │

│ └─→ CR Customer Account (client.treeId) │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 6. Transaction Commit/Rollback │

│ - Commit all changes if successful │

│ - Rollback if any errors occur │

│ - Redirect to success/error page │

└─────────────────────────────────────────────────────────────┘

---

### Workflow 2: Transaction History Viewing

┌─────────────────────────────────────────────────────────────┐

│ START: View Transaction History │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 1. Parameter Processing │

│ - Extract clientid, from/to dates │

│ - Set default dates if empty │

│ - Load client information │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 2. Build Query String │

│ - Add client ID filter │

│ - Add date range filters │

│ - Include table name filter │

│ - Combine with AND logic │

└────────────────────┬────────────────────────────────────────┘

│

▼

┌─────────────────────────────────────────────────────────────┐

│ 3. Execute Query & Display │

│ - Call show() function with query string │

│ - Load YouTube tutorial links │

│ - Display via clientDeficitview/show.html │

└─────────────────────────────────────────────────────────────┘

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Display add form |
| `do=add` | `add()` | Process debt adjustment |
| `do=show` | Show with filters | View transaction history |
| `do=editprint` | `edit()` | Print transaction details |
| `do=sucess` | Template only | Success confirmation |
| `do=error` | Template only | Error display |

### Required Parameters by Action

**Add Form** (empty `do`):
- No parameters required

**Process Addition** (`do=add`):
- `clientid` - Customer ID
- `clientdebtchangetype` - Adjustment type (0=add, 1=deduct)
- `clientdebtchangeamount` - Adjustment amount
- `comment` - Reason for adjustment

**View History** (`do=show`):
- `clientid` - Customer ID (optional, -1 for all, -2 for all with no client filter)
- `from` - Start date (YYYY-MM-DD)
- `to` - End date (YYYY-MM-DD)

**Print Transaction** (`do=editprint`):
- `id` - Transaction ID (clientdebtchangeid)

---

## 🧮 Calculation Methods

### Debt Balance Calculation

php

// For debt addition (customer owes more)

if ($debtchangetype == 0) {

$debtAfter = $debtbefore + $debtamount;

}

// For debt deduction (customer owes less)

else {

$debtAfter = $debtbefore - $debtamount;

}

### Accounting Entry Logic

php

// Addition: Customer account increases (they owe more)

if ($debtchangetype == 0) {

$dailyEntryDebtor->accountstreeid = $client->treeId; // Customer account

$dailyEntryCreditor->accountstreeid = 146; // Discount earned revenue

}

// Deduction: Customer account decreases (they owe less)

else {

$dailyEntryDebtor->accountstreeid = 398; // Allowed discount expense

$dailyEntryCreditor->accountstreeid = $client->treeId; // Customer account

}

### Concurrency Timeout Handling

php

$noOfTries = 0;

while ($client_data->clientdebt == 'in_use') {

sleep(1);

$noOfTries++;

if ($noOfTries > 15) { // 15 second timeout

// Force unlock using direct SQL

R::exec('UPDATE client SET inUse = 0 where clientid = ' . $clientid);

}

---

## 🔒 Security & Permissions

### Authentication Requirements
- All actions require valid session authentication
- Uses `../public/authentication.php` include
- Session userid used for audit trails

### Input Validation
- Client ID cast to integer: `(int) $_REQUEST['clientid']`
- Amount validation through form controls
- POST data sanitization via framework

### Transaction Safety

php

// Full transaction wrapping

$mytransactions = new Transaction();

try {

$id = add();

$mytransactions->commit();

header("location:?do=sucess");

} catch (Exception $e) {

$mytransactions->rollback();

header("location:?do=error");

}

### Concurrency Control
- Client-in-use flagging prevents simultaneous debt modifications
- Stored procedure-based locking mechanism
- Automatic timeout and force-unlock capabilities
- Direct SQL execution for emergency scenarios

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Indexes Required**:
   - `clientdebtchange(clientid, clientdebtchangedate)`
   - `client(clientid, inUse)`
   - `accountstree(accountstreeid)`

2. **Concurrency Management**:
   - Client locking prevents race conditions
   - Timeout mechanism prevents deadlocks
   - Direct SQL for emergency unlock avoids transaction conflicts

3. **Query Efficiency**:
   - Uses parameterized queries through DAO layer
   - Selective loading based on table name filter
   - Date range indexing for history queries

### Known Performance Issues

sql

-- Long-running client lock check (if many concurrent users)

SELECT * FROM client WHERE clientid = ? AND inUse = 0;

-- Solution: Optimize stored procedure or use row-level locking

---

## 🐛 Common Issues & Troubleshooting

### 1. **Client Lock Timeout**
**Issue**: "Client in use" errors or 15-second delays  
**Cause**: Previous transaction didn't release client lock properly

**Debug**:

sql

-- Check locked clients

SELECT clientid, inUse FROM client WHERE inUse = 1;

-- Manual unlock

UPDATE client SET inUse = 0 WHERE clientid = [ID];

### 2. **Accounting Entry Mismatch**
**Issue**: Journal entries don't match debt change amount  
**Cause**: Account tree ID missing or incorrect

**Debug**:

sql

-- Check client account tree assignment

SELECT c.clientid, c.clientname, c.treeId, a.accountname

FROM client c

LEFT JOIN accountstree a ON a.accountstreeid = c.treeId

WHERE c.clientid = [ID];

### 3. **Transaction Rollback**
**Issue**: All changes rolled back without clear error  
**Cause**: Exception in accounting entry creation or client update

**Debug**:

php

// Add debugging in add() function

try {

echo "Step 1: Client data loaded\n";

// ... existing code ...

echo "Step 2: Debt change inserted\n";

// ... existing code ...

echo "Step 3: Accounting entries created\n";

} catch (Exception $e) {

echo "Error: " . $e->getMessage() . "\n";

print_r($e->getTraceAsString());

}

### 4. **Missing Backup Comments**
**Issue**: Backup log files not created or incomplete  
**Cause**: File permissions or invalid date format

**Fix**:

bash

Check backup directory permissions

ls -la backup/

chmod 755 backup/

chmod 666 backup/*.txt

Check if files are being created

tail -f backup/$(date +%Y-%m-%d).txt

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Debt Addition

1. Select customer with current debt balance

2. Add debt adjustment (type=0, amount=100)

3. Verify new balance = old balance + 100

4. Check journal entry: DR Customer, CR Discount Earned (146)

5. Confirm clientdebtchange record created

### Test Case 2: Debt Deduction

1. Select customer with sufficient debt balance

2. Add debt reduction (type=1, amount=50)

3. Verify new balance = old balance - 50

4. Check journal entry: DR Allowed Discount (398), CR Customer

5. Confirm transaction logged in backup file

### Test Case 3: Concurrency Control

1. Start debt adjustment for customer A (don't complete)

2. Attempt second adjustment for same customer

3. Verify second user waits or gets timeout

4. Complete first transaction

5. Verify second transaction can proceed

### Test Case 4: Error Handling

1. Start transaction with invalid data

2. Force database error (disconnect, etc.)

3. Verify transaction rollback occurs

4. Check that no partial data remains

5. Confirm error page displays

### Debug Mode Enable

php

// Add at top for debugging

error_reporting(E_ALL);

ini_set('display_errors', 1);

// Debug client lock status

echo "Client {$clientid} lock status: " . $client_data->clientdebt . "\n";

// Debug query string

echo "Query: " . $queryString . "\n";

```

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• clientController.md - Customer management
• clientReportsController.md - Customer reports
• dailyentry.md - Journal entry system
• Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

ClientDeficit Documentation

Client Deficit Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action / Add Form Display

2. add() - Process Debt Adjustment

Check backup directory permissions

Check if files are being created

📚 Related Documentation