Edit file File name : dashboard.php Content :<?php // No login required, so no session check. require_once '_helpers.php'; require_once '_payroll_helpers.php'; $conn = get_db_connection(); // --- Fetch Business Settings --- $business = ['name' => 'InvoiceFlow', 'logo' => null, 'primary_color' => '217 91% 60%']; $result = $conn->query("SELECT * FROM business_settings LIMIT 1"); if ($result && $result->num_rows > 0) { $business = $result->fetch_assoc(); } // Overdue Invoices List (Top 5) $overdue_invoices = []; $stmt = $conn->prepare("SELECT i.id, c.name as customer_name, i.due_date, i.total FROM invoices i JOIN customers c ON i.customer_id = c.id WHERE i.status = 'Overdue' ORDER BY i.due_date ASC LIMIT 5"); if ($stmt) { $stmt->execute(); $overdue_invoices_list = fetch_all_assoc($stmt); $stmt->close(); foreach ($overdue_invoices_list as &$invoice) { $invoice['due_text'] = format_relative_date($invoice['due_date']); } unset($invoice); $overdue_invoices = $overdue_invoices_list; } // Overdue Summary $total_unpaid = 0.0; $total_overdue = 0.0; $overdue_count = 0; $result = $conn->query("SELECT total, status FROM invoices WHERE status IN ('Due', 'Overdue')"); if ($result) { while ($row = $result->fetch_assoc()) { $total_unpaid += (float)$row['total']; if ($row['status'] === 'Overdue') { $total_overdue += (float)$row['total']; $overdue_count++; } } } $overdue_summary = ['total_overdue' => $total_overdue, 'overdue_count' => $overdue_count, 'overdue_percentage' => $total_unpaid > 0 ? ($total_overdue / $total_unpaid) * 100 : 0]; // Chart Data $monthly_data = []; for ($i = 11; $i >= 0; $i--) { $date = new DateTime("first day of -$i months"); $monthly_data[$date->format('M y')] = ['inflow' => 0, 'outflow' => 0, 'income' => 0, 'expense' => 0]; } $conn->query("SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));"); $result_inflow = $conn->query("SELECT DATE_FORMAT(paid_at, '%b %y') as month, SUM(total) as total FROM invoices WHERE status = 'Paid' AND paid_at IS NOT NULL GROUP BY month"); if($result_inflow) while($row = $result_inflow->fetch_assoc()) { if(isset($monthly_data[$row['month']])) $monthly_data[$row['month']]['inflow'] = (float)$row['total']; } $result_outflow = $conn->query("SELECT DATE_FORMAT(date, '%b %y') as month, SUM(amount) as total FROM expenses GROUP BY month"); if($result_outflow) while($row = $result_outflow->fetch_assoc()) { if(isset($monthly_data[$row['month']])) $monthly_data[$row['month']]['outflow'] += (float)$row['total']; } $payroll_result = $conn->query("SELECT * FROM payroll"); if ($payroll_result) { while($employee_raw = $payroll_result->fetch_assoc()) { $employee_calculated = calculate_payroll($employee_raw); $current_month_key = date('M y'); if(isset($monthly_data[$current_month_key])) { $monthly_data[$current_month_key]['outflow'] += (float)($employee_calculated['netSalary'] ?? 0); } } } $result_income = $conn->query("SELECT DATE_FORMAT(invoice_date, '%b %y') as month, SUM(total) as total FROM invoices WHERE status IN ('Paid', 'Due', 'Overdue') GROUP BY month"); if($result_income) while($row = $result_income->fetch_assoc()) { if(isset($monthly_data[$row['month']])) $monthly_data[$row['month']]['income'] = (float)$row['total']; } foreach ($monthly_data as $key => $value) { $monthly_data[$key]['expense'] = $value['outflow']; } $chart_data = array_values($monthly_data); $chart_labels = array_keys($monthly_data); // Payable & Owing $today_obj = new DateTime(); $payable_breakdown = ['coming_due' => 0, 'overdue_1_30' => 0, 'overdue_31_60' => 0, 'overdue_61_90' => 0, 'overdue_90_plus' => 0, 'total_payable' => 0]; $result_payable = $conn->query("SELECT total, due_date FROM invoices WHERE status IN ('Due', 'Overdue')"); if($result_payable){ while($row = $result_payable->fetch_assoc()){ $due_date_obj = new DateTime($row['due_date']); $payable_breakdown['total_payable'] += (float)$row['total']; if ($due_date_obj > $today_obj) { $payable_breakdown['coming_due'] += (float)$row['total']; } else { $days_overdue = $today_obj->diff($due_date_obj)->days; if ($days_overdue <= 30) $payable_breakdown['overdue_1_30'] += (float)$row['total']; elseif ($days_overdue <= 60) $payable_breakdown['overdue_31_60'] += (float)$row['total']; elseif ($days_overdue <= 90) $payable_breakdown['overdue_61_90'] += (float)$row['total']; else $payable_breakdown['overdue_90_plus'] += (float)$row['total']; } } } // Income Chart $current_year = date('Y'); $previous_year = $current_year - 1; $sales_by_year = [$current_year => 0, $previous_year => 0]; $result_income_chart = $conn->query("SELECT YEAR(paid_at) as year, SUM(total) as total_sales FROM invoices WHERE status = 'Paid' AND paid_at IS NOT NULL AND YEAR(paid_at) IN ($current_year, $previous_year) GROUP BY year"); if($result_income_chart) while($row = $result_income_chart->fetch_assoc()) { $sales_by_year[$row['year']] = (float)$row['total_sales']; } $income_summary = ['current_year' => $current_year, 'previous_year' => $previous_year, 'current_year_sales' => $sales_by_year[$current_year], 'previous_year_sales' => $sales_by_year[$previous_year]]; // Expense Breakdown $expense_breakdown = []; $result_expense = $conn->query("SELECT category, SUM(amount) as amount FROM expenses GROUP BY category"); if($result_expense) while($row = $result_expense->fetch_assoc()) { $expense_breakdown[] = ['category' => $row['category'], 'amount' => (float)$row['amount']]; } $payroll_result_exp = $conn->query("SELECT SUM(netSalary) as total FROM payroll"); if ($payroll_result_exp && $payroll_result_exp->num_rows > 0) { $payroll_row_exp = $payroll_result_exp->fetch_assoc(); if (isset($payroll_row_exp['total']) && $payroll_row_exp['total'] > 0) { $expense_breakdown[] = ['category' => 'Payroll', 'amount' => (float)$payroll_row_exp['total']]; } } $conn->close(); ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Dashboard - <?php echo htmlspecialchars($business['name']); ?></title> <script src="https://cdn.tailwindcss.com"></script> <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.2/dist/chart.umd.min.js"></script> <link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600;700&display=swap" rel="stylesheet"> <style> body { font-family: 'Inter', sans-serif; } :root { --primary: <?php echo htmlspecialchars($business['primary_color'] ?? '217 91% 60%'); ?>; } .bg-primary { background-color: hsl(var(--primary)); } .text-primary { color: hsl(var(--primary)); } .border-primary { border-color: hsl(var(--primary)); } .ring-primary:focus-visible { --tw-ring-color: hsl(var(--primary)); } .animate-pulse-destructive { animation: pulse-destructive 2s cubic-bezier(0.4, 0, 0.6, 1) infinite; } @keyframes pulse-destructive { 50% { opacity: .5; } } </style> </head> <body class="bg-slate-50"> <div class="flex h-screen"> <?php include '_sidebar.php'; ?> <div class="flex-1 flex flex-col overflow-hidden lg:ml-64"> <?php include '_header.php'; ?> <main class="flex-1 overflow-x-hidden overflow-y-auto bg-slate-50 p-4 sm:p-6 lg:p-8"> <div class="flex flex-col gap-8"> <!-- Overdue Section --> <div class="grid grid-cols-1 lg:grid-cols-3 gap-8 items-stretch"> <div class="lg:col-span-1 h-full"> <div class="bg-red-50 border border-red-200 h-full rounded-lg"> <div class="p-4"><h3 class="text-base font-semibold">Overdue Summary</h3></div> <div class="flex flex-col items-center gap-4 p-4"> <div class="w-full py-4 shadow-md px-4 mb-4 bg-white rounded-lg"> <div class="p-0 text-center flex flex-col items-center justify-center"> <p class="text-sm text-slate-500">Total Amount Overdue</p> <p class="text-xl font-bold text-red-600 mt-1"><?php echo format_currency($overdue_summary['total_overdue']); ?></p> </div> </div> <div class="w-full space-y-2 px-2"> <div class="flex justify-between text-sm text-slate-500"> <span><?php echo $overdue_summary['overdue_count']; ?> invoices overdue</span> <span><?php echo round($overdue_summary['overdue_percentage']); ?>% of unpaid</span> </div> <div class="w-full bg-red-200 rounded-full h-2.5"><div class="bg-primary h-2.5 rounded-full" style="width: <?php echo $overdue_summary['overdue_percentage']; ?>%"></div></div> </div> <button class="mt-4 w-full max-w-xs bg-red-600 text-white font-semibold py-2 px-4 rounded-full hover:bg-red-700">Send Reminders</button> </div> </div> </div> <div class="lg:col-span-2 h-full"> <div class="bg-white rounded-lg shadow-sm border h-full"> <div class="p-4 flex items-center justify-between"> <h3 class="text-lg font-semibold">Overdue Invoices</h3> <a href="#" class="text-primary text-sm font-semibold hover:underline">View All →</a> </div> <div class="p-0"> <table class="w-full text-sm"> <thead><tr class="border-b"><th class="px-6 py-2 font-medium text-slate-500 text-left">Customer</th><th class="px-6 py-2 font-medium text-slate-500 text-left">Due</th><th class="px-6 py-2 font-medium text-slate-500 text-right">Amount</th></tr></thead> <tbody> <?php if (empty($overdue_invoices)): ?> <tr><td colspan="3" class="text-center p-6 text-slate-500">No overdue invoices. Great job!</td></tr> <?php else: ?> <?php foreach ($overdue_invoices as $invoice): ?> <tr class="border-t"><td class="px-6 py-3 font-medium"><?php echo htmlspecialchars($invoice['customer_name']); ?></td><td class="px-6 py-3 text-slate-500"><?php echo htmlspecialchars($invoice['due_text']); ?></td><td class="px-6 py-3 text-right font-semibold"><?php echo format_currency($invoice['total']); ?></td></tr> <?php endforeach; ?> <?php endif; ?> </tbody> </table> </div> </div> </div> </div> <!-- Charts Section --> <div class="grid grid-cols-1 lg:grid-cols-2 gap-8"> <div class="bg-white rounded-lg shadow-sm border"> <div class="p-6"><h3 class="text-lg font-semibold">Cash Flow</h3><p class="text-sm text-slate-500">Cash in vs. cash out.</p></div><div class="px-6 pb-6 h-[250px]"><canvas id="cash-flow-chart"></canvas></div> </div> <div class="bg-white rounded-lg shadow-sm border"> <div class="p-6"><h3 class="text-lg font-semibold">Profit And Loss</h3><p class="text-sm text-slate-500">Income vs. expenses.</p></div><div class="px-6 pb-6 h-[250px]"><canvas id="pnl-chart"></canvas></div> </div> </div> <!-- Payable & Owing --> <div class="bg-white rounded-lg shadow-sm border p-6"> <h3 class="text-lg font-semibold mb-4">Payable & Owing</h3> <div class="grid grid-cols-1 md:grid-cols-2 gap-8"> <div> <div class='flex items-center justify-between'><h4 class="font-medium">Invoices payable to you</h4><a href="#" class="text-primary text-sm font-semibold hover:underline">View</a></div> <p class='text-lg font-bold tracking-tight'><?php echo format_currency($payable_breakdown['total_payable']); ?></p> <hr class="my-2" /> <div class="space-y-2 text-sm"> <div class="flex justify-between py-1"><span class="text-primary font-semibold">Coming Due</span><span><?php echo format_currency($payable_breakdown['coming_due']); ?></span></div><hr/> <div class="flex justify-between py-1"><span>1-30 days overdue</span><span><?php echo format_currency($payable_breakdown['overdue_1_30']); ?></span></div><hr/> <div class="flex justify-between py-1"><span>31-60 days overdue</span><span><?php echo format_currency($payable_breakdown['overdue_31_60']); ?></span></div><hr/> <div class="flex justify-between py-1"><span>61-90 days overdue</span><span><?php echo format_currency($payable_breakdown['overdue_61_90']); ?></span></div><hr/> <div class="flex justify-between py-1"><span>> 90 days overdue</span><span><?php echo format_currency($payable_breakdown['overdue_90_plus']); ?></span></div> </div> </div> <div> <div class='flex items-center justify-between'><h4 class="font-medium">Bills you owe</h4><a href="#" class="text-primary text-sm font-semibold hover:underline">View</a></div> <p class='text-lg font-bold tracking-tight'><?php echo format_currency(0); ?></p> <hr class="my-2" /> <div class="space-y-2 text-sm text-slate-500"><p class="text-center py-8">You don't have any bills yet.</p></div> </div> </div> </div> <!-- Income & Expense Breakdown --> <div class="grid grid-cols-1 sm:grid-cols-2 gap-8 items-stretch"> <div class="bg-white rounded-lg shadow-sm border"> <div class="p-6"><h3 class="text-lg font-semibold">Income</h3></div><div class="p-6 h-[300px] flex items-center justify-center"><canvas id="income-chart"></canvas></div> </div> <div class="bg-white rounded-lg shadow-sm border"> <div class="p-6"><h3 class="text-lg font-semibold">Expense Breakdown</h3></div><div class="p-6 h-[300px] flex items-center justify-center"><canvas id="expense-chart"></canvas></div> </div> </div> </div> </main> </div> </div> <script> document.addEventListener('DOMContentLoaded', function() { const chartData = <?php echo json_encode($chart_data); ?>; const chartLabels = <?php echo json_encode($chart_labels); ?>; const incomeSummary = <?php echo json_encode($income_summary); ?>; const expenseBreakdown = <?php echo json_encode($expense_breakdown); ?>; new Chart(document.getElementById('cash-flow-chart').getContext('2d'), { type: 'bar', data: { labels: chartLabels, datasets: [{ label: 'Inflow', data: chartData.map(d => d.inflow), backgroundColor: 'hsl(var(--primary))', borderRadius: 4 }, { label: 'Outflow', data: chartData.map(d => d.outflow), backgroundColor: '#34d399', borderRadius: 4 }] }, options: { responsive: true, maintainAspectRatio: false, scales: { y: { beginAtZero: true, ticks: { callback: (val) => `BZ$${val/1000}k` } }, x: { grid: { display: false } } }, plugins: { legend: { position: 'bottom', align: 'start' } } } }); new Chart(document.getElementById('pnl-chart').getContext('2d'), { type: 'bar', data: { labels: chartLabels, datasets: [{ label: 'Income', data: chartData.map(d => d.income), backgroundColor: '#ef4444', borderRadius: 4 }, { label: 'Expense', data: chartData.map(d => d.expense), backgroundColor: '#f59e0b', borderRadius: 4 }] }, options: { responsive: true, maintainAspectRatio: false, scales: { y: { beginAtZero: true, ticks: { callback: (val) => `BZ$${val/1000}k` } }, x: { grid: { display: false } } }, plugins: { legend: { position: 'bottom', align: 'start' } } } }); new Chart(document.getElementById('income-chart').getContext('2d'), { type: 'doughnut', data: { labels: [`Current Year (${incomeSummary.current_year})`, `Previous Year (${incomeSummary.previous_year})`], datasets: [{ data: [incomeSummary.current_year_sales, incomeSummary.previous_year_sales], backgroundColor: ['hsl(var(--primary))', '#fca5a5'] }] }, options: { responsive: true, maintainAspectRatio: false, cutout: '80%', circumference: 180, rotation: -90, plugins: { legend: { display: true, position: 'bottom' } } } }); new Chart(document.getElementById('expense-chart').getContext('2d'), { type: 'doughnut', data: { labels: expenseBreakdown.map(e => e.category), datasets: [{ data: expenseBreakdown.map(e => e.amount), backgroundColor: ['#3b82f6', '#10b981', '#ef4444', '#f59e0b', '#6366f1', '#8b5cf6'] }] }, options: { responsive: true, maintainAspectRatio: false, plugins: { legend: { display: true, position: 'right' } } } }); }); </script> </body> </html> Save