Assistant Ledger Controller Documentation

File: /controllers/assistantledger.php

Purpose: Generate detailed account ledger reports with transaction analysis and T-shape formatting

Last Updated: December 20, 2024

Total Functions: 6+

Lines of Code: ~559

---

📋 Overview

The Assistant Ledger Controller provides comprehensive accounting ledger functionality with advanced transaction analysis capabilities. It handles:

• • Individual account ledger generation
• • Transaction relationship analysis
• • T-shaped ledger formatting
• • Date range filtering for transactions
• • Account tree navigation and child account handling
• • Complex SQL query generation for transaction matching
• • Debtor and creditor transaction grouping
• • Multi-format report output (standard and T-shape views)

Primary Functions

• ✅ Generate individual account ledgers
• ✅ Display transactions in T-shape format
• ✅ Link related transactions across different entries
• ✅ Filter transactions by date ranges
• ✅ Handle complex debtor/creditor relationships
• ✅ Support account tree hierarchy analysis
• ✅ Provide YouTube tutorial integration
• ✅ Real-time transaction processing
• ✅ Advanced SQL query optimization

Related Controllers

• • accountstree.php - Account tree management
• • dailyentry.php - Daily entry operations
• • balancereportController.php - Balance reporting

---

🗄️ Database Tables

Core Accounting Tables

🔑 Key Functions

1. Default Action - Ledger Interface

Location: Line 76

Purpose: Display ledger interface with parameter handling

Process Flow:

1. Parse input parameters from POST data

2. Set default date range if not provided

3. Load YouTube tutorial links

4. Check account type for child account handling

5. Call appropriate ledger generation function

6. Display results via Smarty template

Parameter Handling:

$accountsTreeId = filter_input(INPUT_POST, "assistantledgerid");
$datefrom = filter_input(INPUT_POST, "datefrom");
$dateto = filter_input(INPUT_POST, "dateto");
$shape = filter_input(INPUT_POST, "shape");

Default Values:

if (empty($accountsTreeId)) {
    $accountsTreeId = -1;
}

if (empty($shape) || !isset($shape)) {
    $shape = 0; // Standard format
}

if (empty($datefrom) && empty($dateto)) {
    $datefrom = date('Y-m-d');
    $dateto = date('Y-m-d');
}

---

2. DrawTableByAccountNew() - Advanced Ledger Generation

Location: Line 130

Purpose: Generate comprehensive account ledger with transaction matching

Function Signature:

function DrawTableByAccountNew($id, $startDate, $endDate, $shape)

Process Flow:

1. Build date filter SQL clauses

2. Query for related daily entries (both debtor and creditor)

3. Process transactions to find matching relationships

4. Handle single vs. multiple transaction scenarios

5. Apply T-shape formatting if requested

6. Sort and assign data to template

Complex SQL Generation:

$queryStringDebtor = ' where dailyentrydebtor.accountstreeid in( ' . $id . ') ' . $queryString_date . ' ';
$queryStringCreditor = ' where dailyentrycreditor.accountstreeid in( ' . $id . ') ' . $queryString_date . ' ';

// Union query to get all related entry IDs
$dailyEntryIds = R::getAll('(   SELECT dailyentry.id
                                FROM dailyentry
                                join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
                               ' . $queryStringDebtor . '
                            )union(
                                SELECT dailyentry.id
                                FROM dailyentry
                                join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
                                ' . $queryStringCreditor . ')');

Transaction Relationship Logic:

foreach ($mainItem as $myItem) {
    // Get count of sibling transactions
    $itemBroCount = count($itemBro[$myItem['id']]) + 1;
    
    // Get opposing side transactions
    $result = $resultDataArr[$myItem['id']];
    $resultCount = count($result);
    
    if ($itemBroCount == 1 && $resultCount >= 1) {
        // Simple one-to-one or one-to-many
        foreach ($result as $value) {
            array_push($allDailyEntery, $value);
        }
    } elseif ($itemBroCount > 1 && $resultCount == 1) {
        // Many-to-one relationship
        $result[0]->value = $myItem['value'];
        array_push($allDailyEntery, $result[0]);
    } elseif ($itemBroCount > 1 && $resultCount > 1) {
        // Complex many-to-many relationship
        $result[0]->accountstreeName = "مذكورين"; // Multiple accounts
        $result[0]->value = $myItem['value'];
        array_push($allDailyEntery, $result[0]);
    }
}

---

3. T-Shape Formatting Logic

Location: Line 319-353

Purpose: Format ledger data in traditional T-account layout

Process Flow:

1. Separate debtor and creditor transactions

2. Calculate total count for proper alignment

3. Alternate between debit and credit columns

4. Fill empty cells with blank entries

5. Track actual vs. total record count

T-Shape Implementation:

$debtorItr = 0;
$creditorItr = 0;
$realCount = 0;
$totalCount = count($TShapeArrDebtor) + count($TShapeArrCerditor);

for ($i = 0; $i < ($totalCount * 2); $i++) {
    if (empty($TShapeArrDebtor[$debtorItr]) && empty($TShapeArrCerditor[$creditorItr])) {
        // No more data to process
    } else {
        $realCount++;
        if ($i % 2 == 0) { // Even positions for debtors
            if (empty($TShapeArrDebtor[$debtorItr])) {
                array_push($allDailyEntery, $dailyEntry);
            } else {
                array_push($allDailyEntery, $TShapeArrDebtor[$debtorItr]);
                $debtorItr++;
            }
        } else { // Odd positions for creditors
            if (empty($TShapeArrCerditor[$creditorItr])) {
                array_push($allDailyEntery, $dailyEntry);
            } else {
                array_push($allDailyEntery, $TShapeArrCerditor[$creditorItr]);
                $creditorItr++;
            }
        }
    }
}

---

4. Advanced Account Name Generation

Location: Lines 186-192, 256-262

Purpose: Create hierarchical account names with parent/child relationships

Implementation:

// Generate account path: Parent/Child format
concat(dailyentrycreditor.accountstreeid,"-",parent.customName,"/",accountstree.customName) as accountstreeName

// Join with parent accounts
join accountstree on accountstree.id = dailyentrycreditor.accountstreeid
join accountstree parent on parent.id = accountstree.parent

---

5. sortById() - Custom Sorting Algorithm

Location: Line 520

Purpose: Sort transactions by ID with custom bubble sort implementation

Function Signature:

function sortById($type)

Implementation:

$membresCount = count($allDailyEntery) - 1;
foreach ($allDailyEntery as $myalloutRole) {
    for ($i = 0; $i < $membresCount; $i++) {
        if ($type == "desc") {
            if ($allDailyEntery[$i]->id < $allDailyEntery[$i + 1]->id) {
                $tempMember = $allDailyEntery[$i];
                $allDailyEntery[$i] = $allDailyEntery[$i + 1];
                $allDailyEntery[$i + 1] = $tempMember;
            }
        } elseif ($type == "asc") {
            if ($allDailyEntery[$i]->id > $allDailyEntery[$i + 1]->id) {
                $tempMember = $allDailyEntery[$i + 1];
                $allDailyEntery[$i + 1] = $allDailyEntery[$i];
                $allDailyEntery[$i] = $tempMember;
            }
        }
    }
}

---

6. getChilds() - Recursive Account Hierarchy

Location: Line 545

Purpose: Recursively retrieve child accounts for hierarchical processing

Function Signature:

function getChilds($parent)

Implementation:

function getChilds($parent) {
    global $accountsTreeEX;
    global $cildrenIds;
    
    $result = $accountsTreeEX->queryByParentExt($parent);
    if (count($result) > 0) {
        foreach ($result as $type) {
            $cildrenIds .= $type->id . ',';
            getChilds($type->id); // Recursive call
        }
    }
    return $cildrenIds;
}

---

🔄 Workflows

Workflow 1: Standard Ledger Generation

---

Workflow 2: T-Shape Ledger Formatting

---

🌐 URL Routes & Actions

Ledger Generation:

• • assistantledgerid - Account ID to analyze
• • datefrom - Start date for transaction filter
• • dateto - End date for transaction filter
• • shape - Display format (0=standard, 1=T-shape)

---

🧮 Advanced SQL Analysis

Transaction Relationship Detection

-- Get main item transactions (account as debtor)
SELECT dailyentry.id, dailyentrydebtor.value, 0 as type
FROM dailyentry
JOIN dailyentrydebtor ON dailyentrydebtor.dailyentryid = dailyentry.id
WHERE dailyentry.id IN (entry_ids) 
  AND accountstreeid IN (account_id)
ORDER BY dailyentry.related, dailyentry.id, dailyentrydebtor.id

-- Get brother transactions (other accounts in same entry)
SELECT dailyentryid
FROM dailyentrydebtor
WHERE dailyentrydebtor.accountstreeid NOT IN (account_id) 
  AND dailyentryid IN (entry_ids)

-- Get opposing transactions (credits in same entries)
SELECT dailyentry.*, 
       dailyentrycreditor.value,
       CONCAT(parent.customName, "/", accountstree.customName) as accountstreeName
FROM dailyentry
JOIN dailyentrycreditor ON dailyentrycreditor.dailyentryid = dailyentry.id
JOIN accountstree ON accountstree.id = dailyentrycreditor.accountstreeid
JOIN accountstree parent ON parent.id = accountstree.parent
WHERE accountstree.id NOT IN (account_id) 
  AND dailyentry.id IN (entry_ids)

Custom Array Indexing

// Group results by daily entry ID for relationship analysis
$resultDataArr = customArrayIndexMany($resultDataArr, 'id');

// Convert to objects for template processing
foreach ($resultDataArr as $key => $value) {
    $rowArr = array();
    foreach ($value as $data) {
        $obj = new stdClass();
        foreach ($data as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $rowArr[] = $obj;
    }
    $tempArr[$key] = $rowArr;
}

---

📊 Performance Considerations

Query Optimization

1. Union Queries: Combine debtor and creditor lookups efficiently

2. Index Usage: Leverage indexes on accountstreeid and dailyentryid

3. Date Filtering: Apply date filters early in query processing

4. Result Limiting: Process only relevant entries

Memory Management

• • Custom array indexing reduces memory overhead
• • Object conversion only for final output
• • Streaming processing for large result sets

---

🔒 Security & Validation

Input Sanitization

$accountsTreeId = filter_input(INPUT_POST, "assistantledgerid");
$datefrom = filter_input(INPUT_POST, "datefrom");
$dateto = filter_input(INPUT_POST, "dateto");
$shape = filter_input(INPUT_POST, "shape");

Access Control

• • Authentication required for all operations
• • Session-based user validation

---

🐛 Common Issues & Troubleshooting

1. Missing Account Relationships

Issue: Transactions show incomplete account names

Cause: Missing parent account join

Fix: Ensure parent account table is properly joined

2. T-Shape Layout Issues

Issue: Unbalanced T-shape display

Cause: Incorrect iterator management

Debug: Check realCount vs totalCount values

3. Date Filter Problems

Issue: Transactions outside date range appear

Cause: Improper date format in SQL

Solution: Ensure proper date format (YYYY-MM-DD)

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • accountstree.md - Account tree management
• • dailyentry.md - Daily entry operations

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur