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

• ✅ Cash flow reconciliation reporting
• ✅ Bank account movement tracking
• ✅ Cash register transaction analysis
• ✅ Customer payment reconciliation
• ✅ Supplier payment tracking
• ✅ Check processing (dated and deposit)
• ✅ Inter-account transfers
• ✅ Net sales calculation
• ✅ Expense categorization

Related Controllers

• • saveController.md - Cash register management
• • bankaccountController.md - Bank account operations
• • clientPayedDeptController.md - Customer payments
• • checkController.md - Check management

---

🗄️ Database Tables

Cash Management Tables

🔑 Key Functions

1. Default Action - Cash Flow Mirror Report

Location: Lines 48-253

Purpose: Generate comprehensive cash flow reconciliation report

Function Signature:

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

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

---

🧮 Calculation Methods

Opening Balance Calculation

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

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

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

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

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

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

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

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

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

-- 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 - PHP 8.2 migration guide
• • saveController.md - Cash register management
• • bankaccountController.md - Bank operations
• • cashTransferController.md - Transfer operations

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur