// 1. Cash Out (New Loans)
$stmt_out = $pdo->prepare("SELECT l.*, c.name FROM loans l JOIN customers c ON l.customer_id = c.id WHERE l.loan_date = ?");
$stmt_out->execute([$date]);
$loans_given = $stmt_out->fetchAll();

$total_out = 0;
foreach ($loans_given as $l) $total_out += $l['loan_amount'];

// 2. Cash In (Repayments + Appraisal Charges)
// A. Transactions (Repayments & Advance Interest)
$stmt_trans = $pdo->prepare("SELECT t.*, c.name, l.id as loan_id FROM transactions t JOIN loans l ON t.loan_id = l.id JOIN customers c ON l.customer_id = c.id WHERE t.transaction_date = ?");
$stmt_trans->execute([$date]);
$transactions = $stmt_trans->fetchAll();

$total_in_trans = 0;
foreach ($transactions as $t) $total_in_trans += $t['amount'];

// B. Appraisal Charges (from loans created today)
$stmt_appraisal = $pdo->prepare("SELECT SUM(appraisal_charge) as total_appraisal FROM loans WHERE loan_date = ?");
$stmt_appraisal->execute([$date]);
$total_appraisal = $stmt_appraisal->fetch()['total_appraisal'] ?? 0;

$total_in = $total_in_trans + $total_appraisal;

$net_balance = $total_in - $total_out;
?>

<div class="row">
    <div class="col-md-12">
        <h2>Money Management (Day Book)</h2>
        
        <div class="card mb-4 no-print">
            <div class="card-body">
                <form method="GET" class="row g-3 align-items-center">
                    <div class="col-auto">
                        <label class="col-form-label">Select Date:</label>
                    </div>
                    <div class="col-auto">
                        <input type="date" class="form-control" name="date" value="">
                    </div>
                    <div class="col-auto">
                        <button type="submit" class="btn btn-primary btn-3d">View Report</button>
                    </div>
                    <div class="col-auto">
                        <button type="button" onclick="window.print()" class="btn btn-secondary btn-3d">Print Report</button>
                    </div>
                </form>
            </div>
        </div>

        <div class="row">
            <!-- Summary Cards -->
            <div class="col-md-4">
                <div class="card text-white bg-success mb-3">
                    <div class="card-header">Total Cash IN</div>
                    <div class="card-body">
                        <h4 class="card-title"><br />
<b>Fatal error</b>:  Uncaught Error: Call to undefined function formatMoney() in /home2/skyinfoi/public_html/loan/daybook.php:57
Stack trace:
#0 {main}
  thrown in <b>/home2/skyinfoi/public_html/loan/daybook.php</b> on line <b>57</b><br />