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

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

Related Controllers

• • accountstree.md - Account tree management
• • dailyentry.md - Journal entries
• • assistantledger.md - Subsidiary ledgers

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 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:

// 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:

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:

// 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:

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:

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:

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:

function getDailyEntrySumByParentAndDate($parent, $from, $to)

SQL Queries:

-- 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:

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:

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

---

🌐 URL Routes & Actions

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

// 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

// 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

// 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

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

// 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:

-- 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:

// 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:

-- 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 - PHP 8.2 migration guide
• • accountstree.md - Account tree management
• • dailyentry.md - Journal entry system
• • assistantledger.md - Subsidiary ledgers

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur