JezK
Edit File: pl_report.php
<?php require_once __DIR__ . '/../config/auth.php'; require_role('accounts'); $pdo = db(); $user = current_user(); $month_filter = $_GET['month'] ?? date('Y-m'); $lab_filter = sanitize_int($_GET['lab_id'] ?? 0); // Basic Stats Querying for the selected month/lab $params = ["$month_filter-01", "$month_filter-31"]; $lab_where = $lab_filter ? "AND lab_id = $lab_filter" : ""; // 1. Total Revenue (Transactions) $rev_stmt = $pdo->prepare("SELECT SUM(net_amount) FROM transactions WHERE payment_status='paid' AND DATE(created_at) BETWEEN ? AND ? $lab_where"); $rev_stmt->execute($params); $total_revenue = $rev_stmt->fetchColumn() ?: 0; // Refunds removed // 3. Total Expenses (Only Approved) $exp_stmt = $pdo->prepare("SELECT SUM(amount) FROM expenses WHERE status = 'approved' AND expense_date BETWEEN ? AND ? $lab_where"); $exp_stmt->execute($params); $total_expenses = $exp_stmt->fetchColumn() ?: 0; // 4. Doctor Payouts $pay_stmt = $pdo->prepare("SELECT SUM(amount) FROM doctor_payouts WHERE paid_at BETWEEN ? AND ? " . ($lab_filter ? "AND recorded_by IN (SELECT id FROM users WHERE lab_id=$lab_filter)" : "")); $pay_stmt->execute(["$month_filter-01 00:00:00", "$month_filter-31 23:59:59"]); $total_payouts = $pay_stmt->fetchColumn() ?: 0; $net_profit = $total_revenue - ($total_expenses + $total_payouts); // Category breakdown for Expenses chart $cat_stmt = $pdo->prepare("SELECT category, SUM(amount) as total FROM expenses WHERE status = 'approved' AND expense_date BETWEEN ? AND ? $lab_where GROUP BY category"); $cat_stmt->execute($params); $expense_categories = $cat_stmt->fetchAll(); $labs = $pdo->query("SELECT id, name FROM labs WHERE status='active' ORDER BY name")->fetchAll(); ?> <!DOCTYPE html><html lang="en"> <head> <meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1"> <title>P&L Analytics — TrustOne Accounts</title> <link rel="stylesheet" href="../assets/css/style.css"> <style> .stat-grid { display: grid; grid-template-columns: repeat(4, 1fr); gap: 20px; margin-bottom: 24px; } .stat-card { background: #fff; padding: 20px; border-radius: 12px; border: 1px solid var(--border); } .stat-val { font-size: 24px; font-weight: 800; margin: 4px 0; } .stat-label { font-size: 11px; text-transform: uppercase; color: #64748b; font-weight: 600; letter-spacing: 0.02em; } .chart-container { background: #fff; padding: 24px; border-radius: 12px; border: 1px solid var(--border); margin-bottom: 24px; } </style> </head> <body> <div class="app-layout"> <?php if (is_role('superadmin')) include '../layouts/sidebar_superadmin.php'; else include '../layouts/sidebar_accounts.php'; ?> <header class="topbar"> <button class="topbar-hamburger" id="hamburger-btn"><span></span><span></span><span></span></button> <div class="topbar-title">Profit & Loss (P&L) Analytics</div> </header> <main class="main-content"> <div class="card" style="margin-bottom:20px;"> <div class="card-body"> <form method="get" class="filter-bar"> <div class="filter-group"> <label class="form-label">Month</label> <input type="month" name="month" class="form-control" value="<?= $month_filter ?>" onchange="this.form.submit()"> </div> <div class="filter-group"> <label class="form-label">Lab Focus</label> <select class="form-control" name="lab_id" onchange="this.form.submit()"> <option value="0">All Organization</option> <?php foreach($labs as $l): ?> <option value="<?= $l['id'] ?>" <?= $lab_filter==$l['id']?'selected':'' ?>><?= htmlspecialchars($l['name']) ?></option> <?php endforeach; ?> </select> </div> </form> </div> </div> <div class="stat-grid"> <div class="stat-card"> <div class="stat-label">Total Revenue</div> <div class="stat-val" style="color:#10b981;">₹<?= number_format($total_revenue, 2) ?></div> <div style="font-size:11px;color:#94a3b8;">Collected from patients</div> </div> <div class="stat-card" style="border-left:4px solid #ef4444;"> <div class="stat-label">Total Deductions</div> <div class="stat-val" style="color:#ef4444;">₹<?= number_format($total_expenses + $total_payouts, 2) ?></div> <div style="font-size:11px;color:#94a3b8;">Exp + Comm.</div> </div> <div class="stat-card"> <div class="stat-label">Verified Expenses</div> <div class="stat-val">₹<?= number_format($total_expenses, 2) ?></div> <div style="font-size:11px;color:#94a3b8;">Approved operating costs</div> </div> <div class="stat-card" style="background:var(--primary);color:#fff;border:none;"> <div class="stat-label" style="color:rgba(255,255,255,0.7);">Net Profit</div> <div class="stat-val">₹<?= number_format($net_profit, 2) ?></div> <div style="font-size:11px;color:rgba(255,255,255,0.7);"><?= $net_profit>=0?'Profitable Month':'Loss Incurred' ?></div> </div> </div> <div style="display:grid; grid-template-columns: 2fr 1fr; gap:20px;"> <div class="chart-container"> <div class="card-title" style="margin-bottom:20px;">Expense Distribution</div> <?php if (empty($expense_categories)): ?> <div style="height:300px; display:flex; align-items:center; justify-content:center; color:#94a3b8;">No expenses recorded this month</div> <?php else: ?> <canvas id="expenseChart" height="150"></canvas> <?php endif; ?> </div> <div class="card"> <div class="card-header"><div class="card-title">P&L Summary Table</div></div> <div class="card-body" style="padding:0;"> <table class="data-table"> <tr><td>Gross Collections</td><td style="text-align:right;font-weight:700;">₹<?= number_format($total_revenue, 2) ?></td></tr> <tr><td style="color:#ef4444;">(-) Operational Expenses</td><td style="text-align:right;color:#ef4444;">- ₹<?= number_format($total_expenses, 2) ?></td></tr> <tr><td style="color:#ef4444;">(-) Dr. Commissions</td><td style="text-align:right;color:#ef4444;">- ₹<?= number_format($total_payouts, 2) ?></td></tr> <tr style="background:#f8fafc;border-top:2px solid var(--border);"> <td style="font-weight:800;">NET OPERATING PROFIT</td> <td style="text-align:right;font-weight:800;font-size:16px;">₹<?= number_format($net_profit, 2) ?></td> </tr> </table> </div> </div> </div> </main> </div> <script src="https://cdn.jsdelivr.net/npm/chart.js"></script> <script src="https://unpkg.com/lucide@latest/dist/umd/lucide.min.js"></script> <script src="../assets/js/main.js"></script> <script> lucide.createIcons(); <?php if (!empty($expense_categories)): ?> const ctx = document.getElementById('expenseChart').getContext('2d'); new Chart(ctx, { type: 'bar', data: { labels: <?= json_encode(array_column($expense_categories, 'category')) ?>, datasets: [{ label: 'Amount (₹)', data: <?= json_encode(array_column($expense_categories, 'total')) ?>, backgroundColor: '#1a56e8', borderRadius: 6 }] }, options: { responsive: true, plugins: { legend: { display: false } }, scales: { y: { beginAtZero: true } } } }); <?php endif; ?> </script> </body></html>