# Mirror Report on Cash and Banks Controller Documentation

**File**: `/controllers/mirrorReportOnCashAndBanks.php`  
**Purpose**: Generates comprehensive cash flow reconciliation reports for cash registers and bank accounts  
**Last Updated**: December 20, 2024  
**Total Functions**: 1 main action  
**Lines of Code**: ~261

---

## 📋 Overview

The Mirror Report on Cash and Banks Controller provides detailed cash flow reconciliation by comparing opening balances, receipts, payments, and transfers between cash registers and bank accounts. It creates a mirror-like view showing the flow of money through different financial instruments during a specified period.

### Primary Functions
- [x] Cash flow reconciliation reporting
- [x] Bank account movement tracking
- [x] Cash register transaction analysis
- [x] Customer payment reconciliation
- [x] Supplier payment tracking
- [x] Check processing (dated and deposit)
- [x] Inter-account transfers
- [x] Net sales calculation
- [x] Expense categorization

### Related Controllers
- [saveController.md](saveController.md) - Cash register management
- [bankaccountController.md](bankaccountController.md) - Bank account operations
- [clientPayedDeptController.md](clientPayedDeptController.md) - Customer payments
- [checkController.md](checkController.md) - Check management

---

## 🗄️ Database Tables

### Cash Management Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **save** | Cash registers/safes | saveid, savename, savecurrentvalue, conditions, saveTreeParentType |
| **savedaily** | Cash register movements | savedailyid, saveid, savedailydate, savedailysavebefore, savedailymodelid, tablename |
| **bankaccount** | Bank accounts | accountid, accountname, accountbeginingbalance, conditions |
| **accountmovement** | Bank account movements | accountmovementid, accountmovementdate, accountmovementbefore, accountmovementmodelid, tablename |

### Transaction Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales transactions | sellbillid, sellbilldate, sellbilltotalpayed, conditions |
| **clientdebtchange** | Customer debt changes | clientdebtchangeid, clientdebtchangedate, clientdebtchangeamount |
| **checkdeposit** | Check deposits | checkdepositid, checkdepositdate, checkdepositamount, conditions |
| **datedchecked** | Post-dated checks | datedcheckedid, checkvalue, done, addtype |
| **checkwithdrawal** | Check withdrawals | checkwithdrawalid, checkwithdrawaldate, checkwithdrawalamount, conditions |
| **cashtransfer** | Cash transfers | cashtransferid, cashtransferdate, cashtransferamount, type, conditions |

### Sales Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **returnsellbill** | Sales returns | returnsellbillid, returnsellbilldate, returnsellbillaftertotalbill, conditions |

### System Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **programsettings** | System configuration | programsettingsid, reportsPlusHours |

---

## 🔑 Key Functions

### 1. **Default Action** - Cash Flow Mirror Report
**Location**: Lines 48-253  
**Purpose**: Generate comprehensive cash flow reconciliation report

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

**Process Flow**:
1. **Date Range Processing**: Handle date defaults and time adjustments
2. **Right Side Calculations** (Inflows):
   - Previous bank balances
   - Previous cash balances  
   - Daily cash collections from sales
   - Customer debt collections
   - Customer collections via banks
3. **Left Side Calculations** (Outflows):
   - Supplier payments from cash
   - Supplier payments via banks
   - Expenses by type
   - Cash register expenses
   - Bank account expenses
   - Cash transfers between accounts
4. **Net Calculations**: Cash and bank net positions
5. **Balance Array Creation**: Ensure equal display rows

**Date Range Logic**:
```php
// Handle automatic date ranges based on system settings
if (empty($startDate) && empty($endDate)) {
    $today = date('Y-m-d');
    $yesterday = date("Y-m-d", strtotime('-1 days'));
    
    if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
        $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
        $endToday = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour', strtotime($today)));
        // ... time calculations
    }
}
```

---

## 🔄 Workflows

### Workflow 1: Cash Flow Analysis
```
┌─────────────────────────────────────────────────────────────┐
│              START: Select Date Range                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Calculate Right Side (Inflows)                         │
│     ├─→ Previous bank balances from accountmovement        │
│     ├─→ Previous cash balances from savedaily              │
│     ├─→ Cash collections from sellbill.sellbilltotalpayed  │
│     ├─→ Customer collections via clientdebtchange          │
│     └─→ Bank collections via checkdeposit & dated checks   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Calculate Left Side (Outflows)                         │
│     ├─→ Supplier payments via supplierdebtchange           │
│     ├─→ Bank payments via checkwithdrawal                  │
│     ├─→ Expenses by type from expenses table               │
│     ├─→ Cash expenses from savedaily                       │
│     ├─→ Bank expenses from accountmovement                 │
│     └─→ Inter-account transfers from cashtransfer          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Calculate Net Positions                                │
│     ├─→ Net Cash = Inflows - Outflows + Transfers         │
│     ├─→ Net Bank = Inflows - Outflows + Transfers         │
│     └─→ Net Sales = Sales - Returns                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Generate Balanced Display                              │
│     ├─→ Create equal-length arrays for left/right         │
│     ├─→ Fill shorter array with empty objects              │
│     └─→ Assign to template variables                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Display Mirror Report                                  │
│     └─→ Show side-by-side cash flow analysis              │
└─────────────────────────────────────────────────────────────┘
```

---

## 🧮 Calculation Methods

### Opening Balance Calculation
```php
// Previous bank balances
$BanksFirstPeriodVal = 0;
foreach (R::getAll('select accountid from bankaccount where conditions=0') as $value) {
    $BanksFirstPeriodVal += (float) R::getCell('select accountmovementbefore from accountmovement 
        where accountmovementmodelid=' . $value['accountid'] . ' 
        and accountmovementdate<"' . $startDate . '" 
        order by accountmovementid desc limit 1');
}
```

### Cash Collections Calculation
```php
// Daily cash collections from sales
$billPaymentVal = R::getCell('select sum(sellbilltotalpayed) from sellbill 
    where conditions=0 and sellbilldate >= "' . $startDate . '" 
    and sellbilldate <= "' . $endDate . '"');
```

### Customer Collections via Banks
```php
// Check deposits and dated checks processed through banks
$checkdepositVal = R::getCell('select sum(checkdepositamount) from checkdeposit 
    where conditions=0 and checkdepositdate >= "' . $startDate . '" 
    and checkdepositdate <= "' . $endDate . '"');

$datedCheckedBankClient = R::getCell('select sum(checkValue) from datedchecked
    where addType = 0 and done=1
    and datedCheckedID in (select accountmovementmodelid from accountmovement 
        where tablename="datedCheckedController.php" 
        and accountmovementdate >= "' . $startDate . '" 
        and accountmovementdate <= "' . $endDate . '")');
```

### Transfer Calculation
```php
// Net transfers between cash and bank
$cashtransferSaveToBank = R::getCell('select sum(cashtransferamount) from cashtransfer 
    where type=2 and conditions=0 and cashtransferdate >= "' . $startDate . '" 
    and cashtransferdate <= "' . $endDate . '"');

$cashtransferBankToSave = R::getCell('select sum(cashtransferamount) from cashtransfer 
    where type=1 and conditions=0 and cashtransferdate >= "' . $startDate . '" 
    and cashtransferdate <= "' . $endDate . '"');

$cashTransferNet = $cashtransferSaveToBank - $cashtransferBankToSave;
```

### Expense Filtering
```php
// Cash expenses (excluding duplicates)
$saveOutMoney = R::getCell('select sum(savedailychangeamount) from (
    select savedailychangeamount,savedailychangetype from savedaily 
    where savedailydate >= "' . $startDate . '" and savedailydate <= "' . $endDate . '"
    GROUP BY savedailymodelid,tablename
    HAVING COUNT(*) = 1
) as ttt where savedailychangetype=1');
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Cash flow mirror report |

### Required Parameters
- `from` - Start date (YYYY-MM-DD, optional)
- `to` - End date (YYYY-MM-DD, optional)  
- `expensetypeid` - Expense type ID for filtering (optional)

### Default Behavior
- If no dates provided, uses system-configured time range
- Considers `reportsPlusHours` setting for business day calculations
- Automatically adjusts for different time zones

---

## 🔒 Security & Permissions

### Authentication Requirements
```php
include_once("../public/authentication.php");
```
- Requires valid user session
- No specific financial permissions checked

### Input Sanitization
- All inputs filtered via `filter_input()` functions
- Numeric values cast to appropriate types
- Database queries use parameterized statements

---

## 📊 Performance Considerations

### Database Optimization
1. **Multiple Aggregation Queries**: Report executes many SUM queries
2. **Date Range Performance**: Large date ranges can be slow
3. **Required Indexes**:
   - `sellbill(sellbilldate, conditions)`
   - `clientdebtchange(clientdebtchangedate)`
   - `checkdeposit(checkdepositdate, conditions)`
   - `accountmovement(accountmovementdate, tablename)`
   - `savedaily(savedailydate, tablename)`

### Query Optimization
```sql
-- Problematic query that could be optimized
SELECT sum(savedailychangeamount) FROM (
    SELECT savedailychangeamount, savedailychangetype FROM savedaily 
    WHERE savedailydate >= ? AND savedailydate <= ?
    GROUP BY savedailymodelid, tablename
    HAVING COUNT(*) = 1
) as ttt WHERE savedailychangetype = 1

-- Consider creating a view or materialized table for this complex query
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Unbalanced Cash Flow**
**Issue**: Net cash/bank values don't match actual balances  
**Cause**: Missing transactions or duplicate counting

**Debug Steps**:
1. Check for unrecorded transactions
2. Verify date range includes all relevant periods
3. Look for duplicate entries in savedaily/accountmovement

### 2. **Date Range Issues**
**Issue**: Unexpected results with date filtering  
**Cause**: Time zone handling or business day logic

**Fix**:
```php
// Ensure proper date formatting
if (!empty($startDate)) $startDate .= ' 00:00:00';
if (!empty($endDate)) $endDate .= ' 23:59:59';
```

### 3. **Transfer Discrepancies**
**Issue**: Transfer amounts don't balance between cash and bank  
**Cause**: Different transfer types or missing records

**Verification**:
```sql
-- Verify all transfers are recorded on both sides
SELECT type, SUM(cashtransferamount) 
FROM cashtransfer 
WHERE conditions = 0 
GROUP BY type;
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Cash Flow Balance
```
1. Record known cash transactions
2. Record known bank transactions  
3. Run report for test period
4. Verify inflows - outflows = expected net position
```

### Test Case 2: Transfer Verification
```
1. Create cash-to-bank transfer
2. Run report
3. Verify transfer reduces cash net and increases bank net by same amount
```

### Test Case 3: Date Range Filtering
```
1. Create transactions across multiple days
2. Run report for partial period
3. Verify only transactions in range are included
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [saveController.md](saveController.md) - Cash register management
- [bankaccountController.md](bankaccountController.md) - Bank operations
- [cashTransferController.md](cashTransferController.md) - Transfer operations

---

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