# Monthly Trial Balance Controller Documentation

**File**: `/controllers/monthlytrialbalance.php`  
**Purpose**: Generates monthly trial balance reports with hierarchical account structure and period comparisons  
**Last Updated**: December 20, 2024  
**Total Functions**: 15+  
**Lines of Code**: ~612

---

## 📋 Overview

The Monthly Trial Balance Controller provides comprehensive financial reporting for accounting balance verification. It creates hierarchical trial balance reports with account tree structure, period-based filtering, and balance calculations across multiple months. The controller supports both standard and monitoring views with configurable display options for clients and suppliers.

### Primary Functions
- [x] Generate hierarchical trial balance reports
- [x] Monthly period-based filtering and comparison
- [x] Account tree structure with up to 6 levels
- [x] Debtor and creditor balance calculations
- [x] First period balance tracking
- [x] Client and supplier account hiding options
- [x] Monitor mode for real-time values
- [x] YouTube tutorial integration

### Related Controllers
- [accountstree.md](accountstree.md) - Account tree management
- [dailyentry.md](dailyentry.md) - Journal entries
- [assistantledger.md](assistantledger.md) - Subsidiary ledgers

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **accountstree** | Chart of accounts hierarchy | id, customName, parent, layingOrder, theValue, conditions |
| **accountstreesetting** | Account level configuration | levelno, nooffields |
| **dailyentry** | Journal entry headers | id, thedate, conditions |
| **dailyentrydebtor** | Debit entries | dailyentryid, accountstreeid, value |
| **dailyentrycreditor** | Credit entries | dailyentryid, accountstreeid, value |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **youtubelink** | Tutorial videos | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **Default Action** - Standard Trial Balance Report
**Location**: Lines 79-141  
**Purpose**: Generate hierarchical trial balance report for specified date range

**Function Signature**:
```php
// Triggered when: empty $do
$from = filter_input(INPUT_POST, 'from');
$to = filter_input(INPUT_POST, 'to');
```

**Process Flow**:
1. Parse date range (defaults to current month if empty)
2. Initialize account level settings (6 levels)
3. Generate months array between date range
4. Call `display_children(0, 0, 0, 0)` to build account tree
5. Calculate totals and assign to template
6. Display via `monthlytrialbalanceview/add.html`

**Features**:
- Month-by-month breakdown
- Account hierarchy display with indentation
- Cookie-based client/supplier hiding
- YouTube tutorial integration

---

### 2. **monitor** - Monitor Mode Report
**Location**: Lines 142-202  
**Purpose**: Generate trial balance with real-time program values

**Key Differences from Standard Mode**:
- Calls `getTheTrueValueProgram()` for each account
- Shows actual system balances instead of period balances
- Useful for real-time financial position verification

---

### 3. **display_children()** - Recursive Account Tree Builder
**Location**: Lines 217-348  
**Purpose**: Recursively builds hierarchical account display with calculations

**Function Signature**:
```php
function display_children($parent, $level, $itr, $itr2)
```

**Process Flow**:
1. Query child accounts for given parent
2. Generate laying order numbering system
3. For each child account:
   - Update database laying order if needed
   - Calculate balance values using helper functions
   - Apply indentation based on level
   - Recursively process child accounts

**Key Variables**:
- `$parent` - Parent account ID (0 for root)
- `$level` - Current hierarchy level (0-6)
- `$itr` - Iteration counter without zeros
- `$itr2` - Iteration counter with zeros

**Laying Order System**:
```php
// Generate account numbering: 01, 02, 0101, 0102, etc.
$preDigits = '';
if (strlen($i) != $levelSetting[($level + 1)]->nooffields) {
    $noDigitsLeft = $levelSetting[($level + 1)]->nooffields - strlen($i);
    for ($j = 0; $j < $noDigitsLeft; $j++) {
        $preDigits .= '0';
    }
}
$nodeName = $itr . $preDigits . $i;
```

---

### 4. **getTheTrueValue()** - Parent Account Balance Calculation
**Location**: Lines 350-377  
**Purpose**: Calculate balance for parent accounts by summing children

**Function Signature**:
```php
function getTheTrueValue($parent, $sum)
```

**Process Flow**:
1. Check if account is client/supplier with hiding enabled
2. If hidden, get aggregated balance via `getDailyEntrySumByParentAndDate()`
3. Otherwise, recursively sum child account balances
4. Accumulate monthly totals in `$sum` array

---

### 5. **getTheTrueValueLastLeaf()** - Leaf Account Balance
**Location**: Lines 379-388  
**Purpose**: Calculate balance for leaf accounts (accounts with no children)

**Function Signature**:
```php
function getTheTrueValueLastLeaf($accountId, $sum)
```

**Process Flow**:
1. Call `getDailyEntrySumByAccountIdAndDate()` for account
2. Accumulate monthly debtor/creditor values
3. Update global `$sum` array

---

### 6. **getMonthsBetweenTwoDates()** - Date Range Helper
**Location**: Lines 423-443  
**Purpose**: Generate array of months between two dates

**Function Signature**:
```php
function getMonthsBetweenTwoDates($from, $to, $order = 'asc')
```

**Returns**: Array of month strings in YYYY-MM format

---

### 7. **getDailyEntrySumByParentAndDate()** - Parent Account Query
**Location**: Lines 445-463  
**Purpose**: Aggregate daily entry totals for all accounts under a parent

**Function Signature**:
```php
function getDailyEntrySumByParentAndDate($parent, $from, $to)
```

**SQL Queries**:
```sql
-- Debtor totals grouped by month
SELECT DATE_FORMAT(thedate, "%Y-%m") AS thedate, sum(value) as debtorVal
FROM dailyentry
JOIN dailyentrydebtor ON dailyentrydebtor.dailyentryid = dailyentry.id
JOIN accountstree ON accountstree.id = dailyentrydebtor.accountstreeid
WHERE `condition` = 0 AND parent = ? AND thedate >= ? AND thedate <= ?
GROUP BY MONTH(thedate), YEAR(thedate)
```

---

### 8. **getDailyEntrySumByAccountIdAndDate()** - Individual Account Query
**Location**: Lines 465-481  
**Purpose**: Get daily entry totals for specific account

**Function Signature**:
```php
function getDailyEntrySumByAccountIdAndDate($accountId, $from, $to)
```

---

### 9. **getFirstPeriodVal Functions** - Opening Balance Calculation
**Location**: Lines 483-511  
**Purpose**: Calculate opening balances before the report period

**Key Functions**:
- `getFirstPeriodValLastLeaf()` - For leaf accounts
- `getFirstPeriodValOfParent()` - For parent accounts
- `getFirstPeriodValUsingDailyEntry()` - Base SQL query

---

### 10. **getTheTrueValueProgram()** - Real-time System Values
**Location**: Lines 532-612  
**Purpose**: Get actual system balances for monitor mode

**Function Signature**:
```php
function getTheTrueValueProgram($treeId)
```

**System Value Types**:
- **save/3ohad** - Cash register balances
- **bank** - Bank account balances  
- **client** - Customer debt totals
- **supplier** - Supplier debt totals
- **expenses** - Expense totals by category
- **income** - Revenue totals
- **asset** - Fixed asset values
- **capital** - Capital account balance
- **partner** - Partner account balances

---

## 🔄 Workflows

### Workflow 1: Trial Balance Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│                START: Select Date Range                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Initialize Parameters                                   │
│     - Parse from/to dates                                   │
│     - Default to current month if empty                     │
│     - Load account level settings                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Generate Months Array                                   │
│     - Create month list between dates                       │
│     - Initialize monthly totals structure                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Build Account Tree (Recursive)                          │
│     - Start with root accounts (parent = 0)                │
│     - For each account:                                     │
│       ├─→ Generate laying order number                     │
│       ├─→ Calculate monthly balances                       │
│       ├─→ Calculate first period balances                  │
│       ├─→ Apply indentation based on level                 │
│       └─→ Process child accounts recursively               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Calculate Totals                                        │
│     - Sum all debtor balances                              │
│     - Sum all creditor balances                            │
│     - Calculate monthly totals                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Display Report                                          │
│     - Assign data to Smarty template                       │
│     - Include YouTube tutorials                            │
│     - Render trial balance view                            │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Standard trial balance report |
| `do=monitor` | Monitor mode | Trial balance with real-time values |
| `do=sucess` | Success page | Success confirmation |
| `do=error` | Error page | Error display |

### Required Parameters
**All Actions**:
- `from` - Start date (YYYY-MM-DD, defaults to first day of month)
- `to` - End date (YYYY-MM-DD, defaults to last day of month)

### Cookie Parameters
- `showClientsAtTree` - Hide/show client accounts (0/1)
- `showSuppliersAtTree` - Hide/show supplier accounts (0/1)

---

## 🧮 Calculation Methods

### Account Balance Calculation
```php
// Monthly balance calculation for each account
list($debtor, $creditor) = getDailyEntrySumByAccountIdAndDate($accountId, $from, $to);
foreach ($sum as $month => $arr) {
    $sum[$month]['debt'] += (float) $debtor[$month]['debtorVal'];
    $sum[$month]['credit'] += (float) $creditor[$month]['creditorVal'];
}
```

### Opening Balance Calculation
```php
// First period balance (before report date)
$debtor = R::getCell('select sum(value) as debtorVal
    from dailyentry
    join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
    where `condition`=0 and accountstreeid=' . $accountId . ' and thedate < "' . $from . '"');
```

### Laying Order Generation
```php
// Account numbering system
$preDigits = '';
if (strlen($i) != $levelSetting[($level + 1)]->nooffields) {
    $noDigitsLeft = $levelSetting[($level + 1)]->nooffields - strlen($i);
    for ($j = 0; $j < $noDigitsLeft; $j++) {
        $preDigits .= '0';
    }
}
$nodeName = ($itr == 0) ? $preDigits . $i : $itr . $preDigits . $i;
```

---

## 🔒 Security & Permissions

### Authentication Requirements
```php
include_once("../public/authentication.php");
```
- All actions require valid user session
- No specific permission checks beyond basic authentication

### Input Sanitization
- Date inputs filtered via `filter_input(INPUT_POST, 'from')`
- All database queries use parameterized statements via RedBean
- No direct SQL injection vulnerabilities identified

---

## 📊 Performance Considerations

### Database Optimization
1. **Account Tree Queries**: Recursive queries can be expensive for deep hierarchies
2. **Daily Entry Aggregation**: Monthly grouping queries on large datasets
3. **Indexes Required**:
   - `accountstree(parent)`
   - `dailyentry(thedate, condition)`
   - `dailyentrydebtor(dailyentryid, accountstreeid)`
   - `dailyentrycreditor(dailyentryid, accountstreeid)`

### Memory Usage
- Recursive function calls may cause stack overflow with very deep account trees
- Monthly data arrays stored in memory for entire report period

### Known Issues
```php
// Potential infinite recursion if circular references exist in account tree
display_children($type->id, $level + 1, $nodeName, $nodeNameWithZeros);
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Unbalanced Trial Balance**
**Issue**: Total debits don't equal total credits  
**Cause**: Missing or incorrectly recorded journal entries

**Debug**:
```sql
-- Check for unbalanced journal entries
SELECT dailyentryid, 
    SUM(debtor_total) as total_debits,
    SUM(creditor_total) as total_credits
FROM (
    SELECT dailyentryid, SUM(value) as debtor_total, 0 as creditor_total
    FROM dailyentrydebtor GROUP BY dailyentryid
    UNION ALL
    SELECT dailyentryid, 0, SUM(value) as creditor_total  
    FROM dailyentrycreditor GROUP BY dailyentryid
) combined
GROUP BY dailyentryid
HAVING ABS(total_debits - total_credits) > 0.01;
```

### 2. **Missing Account Balances**
**Issue**: Accounts show zero balance when transactions exist  
**Cause**: Date range issues or condition filtering

**Fix**:
```php
// Verify date format and range
$from = date('Y-m-01'); // First day of month
$to = date('Y-m-t');    // Last day of month
```

### 3. **Incorrect Account Hierarchy**
**Issue**: Accounts appear at wrong levels or missing  
**Cause**: Corrupted `layingOrder` or `parent` relationships

**Fix**:
```sql
-- Rebuild laying order
UPDATE accountstree SET layingOrder = NULL;
-- Run the report to regenerate laying orders
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Trial Balance Accuracy
```
1. Create test journal entries with known amounts
2. Run trial balance for test period
3. Verify total debits = total credits
4. Check individual account balances match journal entries
```

### Test Case 2: Period Filtering
```
1. Create entries across multiple months
2. Run report for single month
3. Verify only entries in selected period appear
4. Test first period balance calculations
```

### Test Case 3: Account Hierarchy Display
```
1. Create multi-level account structure
2. Run trial balance report
3. Verify proper indentation and numbering
4. Check parent/child balance relationships
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [accountstree.md](accountstree.md) - Account tree management
- [dailyentry.md](dailyentry.md) - Journal entry system
- [assistantledger.md](assistantledger.md) - Subsidiary ledgers

---

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