# 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
- [x] Generate individual account ledgers
- [x] Display transactions in T-shape format
- [x] Link related transactions across different entries
- [x] Filter transactions by date ranges
- [x] Handle complex debtor/creditor relationships
- [x] Support account tree hierarchy analysis
- [x] Provide YouTube tutorial integration
- [x] Real-time transaction processing
- [x] Advanced SQL query optimization

### Related Controllers
- [accountstree.php](accountstree.md) - Account tree management
- [dailyentry.php](dailyentry.md) - Daily entry operations
- [balancereportController.php](balancereportController.md) - Balance reporting

---

## 🗄️ Database Tables

### Core Accounting Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **accountstree** | Chart of accounts | id, parent, customName, theValue, itemtype2 |
| **dailyentry** | Journal entry headers | id, thedate, entryComment, related |
| **dailyentrydebtor** | Debit entries | id, dailyentryid, accountstreeid, value, dComment |
| **dailyentrycreditor** | Credit entries | id, dailyentryid, accountstreeid, value, dComment |

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

---

## 🔑 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**:
```php
$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**:
```php
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**:
```php
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**:
```php
$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**:
```php
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**:
```php
$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**:
```php
// 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**:
```php
function sortById($type)
```

**Implementation**:
```php
$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**:
```php
function getChilds($parent)
```

**Implementation**:
```php
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
```
┌─────────────────────────────────────────────────────────────┐
│                START: Select Account & Date Range          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Validate Input Parameters                              │
│     - Account ID (-1 for all)                             │
│     - Date range (default to today)                       │
│     - Display shape (0=standard, 1=T-shape)               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Check Account Type                                     │
│     IF account has children AND itemtype2 == 0:           │
│       └─→ Skip processing (parent account)                │
│     ELSE:                                                  │
│       └─→ Proceed with ledger generation                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Build Date Filter SQL                                  │
│     - Add start date condition if specified               │
│     - Add end date condition if specified                 │
│     - Combine with account filter                         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Query Related Daily Entries                           │
│     - UNION query for debtor and creditor sides           │
│     - Get all daily entry IDs that involve this account   │
│     - Build consolidated ID list                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Process Debtor Transactions                            │
│     FOR EACH debtor transaction:                           │
│       │                                                    │
│       ├─→ Get main transaction details                     │
│       ├─→ Count sibling transactions                       │
│       ├─→ Get opposing creditor transactions               │
│       ├─→ Determine relationship type                      │
│       └─→ Format account names appropriately               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Process Creditor Transactions                          │
│     FOR EACH creditor transaction:                         │
│       │                                                    │
│       ├─→ Get main transaction details                     │
│       ├─→ Count sibling transactions                       │
│       ├─→ Get opposing debtor transactions                 │
│       ├─→ Determine relationship type                      │
│       └─→ Format account names appropriately               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  7. Apply Display Formatting                               │
│     IF shape == 0 (Standard):                             │
│       └─→ Sort by ID ascending                            │
│     ELSE IF shape == 1 (T-Shape):                         │
│       ├─→ Separate debtor and creditor arrays             │
│       ├─→ Alternate between debit/credit columns          │
│       └─→ Fill empty positions with blank entries         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  8. Generate Report Output                                 │
│     - Assign data to Smarty template                      │
│     - Include account details and totals                  │
│     - Display via assistantledgerview/add.html            │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: T-Shape Ledger Formatting
```
┌─────────────────────────────────────────────────────────────┐
│            START: T-Shape Format Request                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Separate Transaction Types                             │
│     - TShapeArrDebtor: All debit transactions             │
│     - TShapeArrCreditor: All credit transactions          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Calculate Layout Requirements                          │
│     - Total count = debtors + creditors                   │
│     - Real count = actual populated cells                 │
│     - Initialize iterators for each side                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Generate T-Shape Layout                                │
│     FOR i = 0 to (totalCount * 2):                        │
│       │                                                    │
│       ├─→ IF i is even: Place debtor transaction          │
│       │   └─→ Use blank entry if no more debtors         │
│       │                                                    │
│       └─→ IF i is odd: Place creditor transaction         │
│           └─→ Use blank entry if no more creditors       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Assign to Template                                     │
│     - Set realCount for template logic                    │
│     - Pass formatted array to Smarty                      │
│     - Display with T-shape CSS styling                    │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Generate ledger report |
| `do=sucess` | Show template | Display success message |
| `do=error` | Show template | Display error message |

### Required Parameters

**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
```sql
-- 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
```php
// 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
```php
$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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [accountstree.md](accountstree.md) - Account tree management
- [dailyentry.md](dailyentry.md) - Daily entry operations

---

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