Bank Account Deficit Controller Documentation

File: /controllers/bankAccountDeficitController.php

Purpose: Manages bank account deficit resolution and balance adjustments for financial reconciliation

Last Updated: December 20, 2024

Total Functions: 3

Lines of Code: ~329

---

📋 Overview

The Bank Account Deficit Controller is a specialized financial module that handles bank account deficit resolution and balance corrections. It provides:

• Bank account deficit adjustment operations (increase/decrease)
• Account movement tracking with audit trails
• Double-entry accounting integration
• Financial reconciliation through expense/income categorization
• Automatic journal entry creation for accounting compliance
• Transaction history viewing and reporting
• Print functionality for deficit adjustment records

Primary Functions

✅ Process bank account deficit adjustments
✅ Track account movements with before/after balances
✅ Generate automated journal entries for accounting
✅ Link deficit adjustments to expense/income accounts
✅ Provide audit trail for all balance modifications
✅ Support both increase and decrease operations
✅ Display transaction history with filtering
✅ Generate printable adjustment records

Related Controllers

• bankaccountController.php - Bank account management
• bankAccountOpController.php - Bank operations
• dailyentry.php - Journal entry management
• accountstreeController.php - Chart of accounts

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
accountmovement	Account transaction log	accountmovementid, accountid, accountmovementamount, accountmovementtype, accountmovementdate, tablename, bankid
bankaccount	Bank account master data	accountid, accountbeginingbalance, accountname, bankid, userid, treeId
accountmovementkind	Transaction type categories	accountmovementkindid, accountmovementkindname, accountmovementkindcomment

### Financial Integration Tables

Table Name	Purpose	Key Columns
dailyentry	Journal entry headers	dailyentryid, entryComment, entryDate
dailyentrycreditor	Credit side of journal entries	dailyentrycreditorid, dailyentryid, accountstreeid, value
dailyentrydebtor	Debit side of journal entries	dailyentrydebtoreid, dailyentryid, accountstreeid, value
accountstree	Chart of accounts	accountstreeid, name, parentid, accounttype

### Reference Tables

Table Name	Purpose	Key Columns
bank	Bank master data	bankid, bankname, bankcode
youtubelink	Tutorial/help links	youtubelinkid, title, url
user	System users	userid, username

---

🔑 Key Functions

1. Default Action - Deficit Adjustment Form

Location: Line 117

Purpose: Display form for creating new deficit adjustments

Process Flow:

1. Load authentication and permission checks

2. Query all banks for dropdown selection

3. Load account movement kinds (transaction types)

4. Display deficit adjustment form via add.html template

Template Variables:

• $bank - Available banks for selection
• $accountmovementkind - Transaction type categories

---

2. add() - Process Deficit Adjustment

Location: Line 222

Purpose: Execute deficit adjustment with complete accounting integration

Function Signature:

function add()

Process Flow:

1. Extract Form Data:

   $bankid = $_POST["ddlBank"];
   $accountid = $_POST["accountid"];
   $changetype = (int) $_POST["changetype"]; // 0=increase, 1=decrease
   $changeamount = $_POST["changeamount"];
   ```

2. **Load Current Account Balance**:
   ```php
   $account = $myBankaccountRecord->load($accountid);
   $balanceBefore = $account->accountbeginingbalance;
   ```

3. **Calculate New Balance Based on Operation Type**:
   - **Increase (changetype = 0)**: `$balanceAfter = $balanceBefore + $changeamount`
   - **Decrease (changetype = 1)**: `$balanceAfter = $balanceBefore - $changeamount`

4. **Create Account Movement Record**:
   ```php
   $myAccountmovement->accountmovementbefore = $balanceBefore;
   $myAccountmovement->accountmovementamount = $changeamount;
   $myAccountmovement->accountmovementafter = $balanceAfter;
   $myAccountmovement->tablename = "bankAccountDeficitController.php";
   ```

5. **Update Account Balance**:
   ```php
   $account->accountbeginingbalance = $balanceAfter;
   $myBankaccountRecord->update($account, $account->comment);
   ```

6. **Generate Journal Entries**:
   - **For Deficit Increase (Interest Income)**:
     - **Debit**: Bank Account (Asset)
     - **Credit**: Account #393 (Interest Income)
   - **For Deficit Decrease (Bank Charges)**:
     - **Debit**: Account #65 (Bank Expenses)  
     - **Credit**: Bank Account (Asset)

**Accounting Logic**:

php

if ($changetype == 1) { // Decrease - Bank charges

$dailyEntryDebtor->accountstreeid = 65; // Bank expenses

$dailyEntryCreditor->accountstreeid = $account->treeId; // Bank account

} elseif ($changetype == 0) { // Increase - Interest income

$dailyEntryDebtor->accountstreeid = $account->treeId; // Bank account

$dailyEntryCreditor->accountstreeid = 393; // Interest income

}

---

### 3. **show()** - Transaction History Display  
**Location**: Line 146  
**Purpose**: Display filtered deficit adjustment history with search capabilities

**Function Signature**:

php

// Parameters from $_REQUEST

$startDate = $_REQUEST['from'];

$endDate = $_REQUEST['to'];

$bankid = $_REQUEST['bankid'];

$accountid = $_REQUEST['accountid'];

$accountmovementkindid = $_REQUEST['accountmovementkindid'];

**Process Flow**:
1. **Build Dynamic Query String**:
   ```php
   $queryString = '';
   if (isset($bankid) && $bankid > 0) {
       $queryString .= ' AND accountmovement.bankid = ' . $bankid;
   }
   if (isset($accountid) && $accountid > 0) {
       $queryString .= ' AND accountmovement.accountid = ' . $accountid;
   }
   ```

2. **Apply Date Filters**:
   ```php
   if (isset($startDate) && !empty($startDate)) {
       $queryString .= ' AND DATE(accountmovement.accountmovementdate) >= "' . $startDate . '"';
   }
   if (isset($endDate) && !empty($endDate)) {
       $queryString .= ' AND DATE(accountmovement.accountmovementdate) <= "' . $endDate . '"';
   }
   ```

3. **Default to Today if No Filters**:
   ```php
   if ($queryString == '') {
       $queryString .= ' AND DATE(accountmovement.accountmovementdate) >= "' . $today . '"';
   }
   ```

4. **Filter by Controller Source**:
   ```php
   $queryString .= ' AND accountmovement.tablename = "bankAccountDeficitController.php"';
   ```

5. **Load and Display Results**:
   - Query movement data with extended information
   - Load reference data (banks, accounts, movement kinds)
   - Display via `show.html` template

---

### 4. **edit($id)** - Load Single Transaction
**Location**: Line 318  
**Purpose**: Load specific deficit adjustment record for display/editing

**Function Signature**:

php

function edit($id)

**Process Flow**:

php

$data = $myAccountmovementEx->loadMovementEX($id);

return $data;

**Used by**: `editprint` action for displaying printable transaction records

---

## 🔄 Workflows

### Workflow 1: Deficit Adjustment Process

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

│ START: Select Account & Amount │

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

│

▼

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

│ 1. Load Current Account Balance │

│ - Query bankaccount table │

│ - Get current accountbeginingbalance │

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

│

▼

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

│ 2. Determine Operation Type │

│ - changetype = 0: Increase (Interest) │

│ - changetype = 1: Decrease (Bank Charges) │

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

│

▼

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

│ 3. Calculate New Balance │

│ - Increase: balanceAfter = balanceBefore + amount │

│ - Decrease: balanceAfter = balanceBefore - amount │

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

│

▼

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

│ 4. Create Account Movement Record │

│ - Record before/after balances │

│ - Set transaction type and amount │

│ - Link to source controller │

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

│

▼

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

│ 5. Update Account Balance │

│ - Update accountbeginingbalance │

│ - Set modification date and user │

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

│

▼

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

│ 6. Generate Journal Entry │

│ - Create daily entry header │

│ - Add debit/credit entries │

│ - Link to appropriate expense/income accounts │

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

---

### Workflow 2: Transaction History Viewing

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

│ START: Apply Search Filters │

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

│

▼

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

│ 1. Build Query String │

│ - Add bank filter if specified │

│ - Add account filter if specified │

│ - Add date range filters │

│ - Add movement kind filter │

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

│

▼

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

│ 2. Apply Controller Filter │

│ - Only show bankAccountDeficitController transactions │

│ - Order by transaction ID descending │

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

│

▼

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

│ 3. Query and Display Results │

│ - Load movement data with extended info │

│ - Include bank names, account names │

│ - Show before/after balances │

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

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Display deficit adjustment form |
| `do=add` | `add()` | Process new deficit adjustment |
| `do=show` | Show transactions | Display filtered transaction history |
| `do=editprint` | `edit()` | Load single transaction for printing |
| `do=sucess` | - | Show success message |
| `do=error` | - | Show error message |

### Required Parameters by Action

**Add Deficit Adjustment** (`do=add`):
- `ddlBank` - Bank ID
- `accountid` - Account ID  
- `changetype` - Operation type (0=increase, 1=decrease)
- `changeamount` - Adjustment amount
- `accountmovementcomment` - Comment/description
- `accountmovementkindid` - Transaction type category

**Show Transactions** (`do=show`):
- `from` - Start date (YYYY-MM-DD) [optional]
- `to` - End date (YYYY-MM-DD) [optional]
- `bankid` - Bank filter [optional]
- `accountid` - Account filter [optional]
- `accountmovementkindid` - Movement kind filter [optional]

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

---

## 🧮 Calculation Methods

### Balance Calculation

php

// Current balance retrieval

$account = $myBankaccountRecord->load($accountid);

$balanceBefore = $account->accountbeginingbalance;

// New balance calculation

if ($changetype == 1) { // Decrease

$balanceAfter = $balanceBefore - $changeamount;

} elseif ($changetype == 0) { // Increase

$balanceAfter = $balanceBefore + $changeamount;

}

### Journal Entry Logic

php

// For decreases (bank charges)

if ($changetype == 1) {

$dailyEntryDebtor->accountstreeid = 65; // Bank expenses

$dailyEntryCreditor->accountstreeid = $account->treeId; // Bank account

}

// For increases (interest income)

elseif ($changetype == 0) {

$dailyEntryDebtor->accountstreeid = $account->treeId; // Bank account

$dailyEntryCreditor->accountstreeid = 393; // Interest income

}

---

## 🔒 Security & Permissions

### Authentication
- All actions require `include_once("../public/authentication.php")`
- Session-based user authentication required
- User ID tracked in all database modifications

### Transaction Safety
- Uses Transaction class for database consistency
- Rollback on any errors during processing
- Atomic operations ensure data integrity

### Input Validation
- Numeric casting: `(int) $_POST["changetype"]`
- Amount validation through form controls
- SQL injection prevented by DAO layer

---

## 🧪 Common Use Cases

### 1. Bank Charge Processing

Scenario: Bank charged $25 for monthly maintenance

• Select affected bank account
• Choose "Decrease" operation type
• Enter $25 as adjustment amount
• System debits "Bank Expenses" and credits bank account

### 2. Interest Income Recording

Scenario: Bank credited $150 interest income

• Select affected bank account
• Choose "Increase" operation type
• Enter $150 as adjustment amount
• System debits bank account and credits "Interest Income"

### 3. Reconciliation Adjustments

Scenario: Bank statement shows different balance

• Calculate difference amount
• Use appropriate increase/decrease operation
• Add detailed comment explaining reconciliation
• Generate audit trail for accounting review

---

## 🐛 Troubleshooting

### Common Issues

1. **Transaction Fails to Commit**
   - Check database connections
   - Verify account exists and is active
   - Ensure sufficient permissions

2. **Journal Entries Not Created**
   - Verify chart of accounts setup (accounts 65, 393)
   - Check dailyentry function availability
   - Confirm account tree ID exists

3. **Balance Calculation Errors**
   - Validate input amounts are numeric
   - Check for concurrent account modifications
   - Verify account balance before operation

### Debug Commands

sql

-- Check account movement history

SELECT * FROM accountmovement

WHERE tablename = 'bankAccountDeficitController.php'

ORDER BY accountmovementdate DESC;

-- Verify account balances

SELECT accountid, accountname, accountbeginingbalance

FROM bankaccount

WHERE conditions = 0;

-- Check journal entry creation

SELECT * FROM dailyentry

WHERE entryComment LIKE '%تسويه%'

ORDER BY entryDate DESC;

```

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• bankaccountController.md - Bank account management
• dailyentryfun.php - Journal entry functions
• Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Documentation Viewer

Bank Account Deficit Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Deficit Adjustment Form

2. add() - Process Deficit Adjustment

📚 Related Documentation