# 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
- [x] Manual debt adjustments for customers
- [x] Debt settlement processing (deduction/addition)
- [x] Automated accounting journal entries
- [x] Client concurrency control (in-use management)
- [x] Transaction history viewing and printing
- [x] Debt change audit trail
- [x] Live backup logging
- [x] User permission enforcement
- [x] Error handling with transaction rollback

### Related Controllers
- [clientController.php](clientController.md) - Customer management
- [clientReportsController.php](clientReportsController.md) - Customer reports
- [clientPayedDeptController.php](#) - Customer payment processing
- [dailyentry.php](dailyentry.md) - Journal entry management
- [sellbillController.php](sellbillController.md) - 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**:
```php
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**:
   ```php
   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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [clientController.md](clientController.md) - Customer management
- [clientReportsController.md](clientReportsController.md) - Customer reports
- [dailyentry.md](dailyentry.md) - Journal entry system
- [Database Schema Documentation](#) - Table relationships

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur