0,
'active_orders' => 0,
'new_customers' => 0,
'ready_orders' => 0,
];
// Today's revenue
$rev_where = !$filter_branch ? "" : " AND order_id IN (SELECT id FROM orders WHERE branch_id = ?)";
$rev_params = !$filter_branch ? [] : [$branch_id];
$stmt = db()->prepare("SELECT SUM(amount) as total FROM payments WHERE created_at >= CURDATE() $rev_where");
$stmt->execute($rev_params);
$stats['today_revenue'] = $stmt->fetch()['total'] ?? 0;
// Active orders (received, processing)
$ord_where = !$filter_branch ? "WHERE status IN ('received', 'processing')" : "WHERE branch_id = ? AND status IN ('received', 'processing')";
$ord_params = !$filter_branch ? [] : [$branch_id];
$stmt = db()->prepare("SELECT COUNT(*) as count FROM orders $ord_where");
$stmt->execute($ord_params);
$stats['active_orders'] = $stmt->fetch()['count'] ?? 0;
// Ready orders
$ready_where = !$filter_branch ? "WHERE status = 'ready'" : "WHERE branch_id = ? AND status = 'ready'";
$ready_params = !$filter_branch ? [] : [$branch_id];
$stmt = db()->prepare("SELECT COUNT(*) as count FROM orders $ready_where");
$stmt->execute($ready_params);
$stats['ready_orders'] = $stmt->fetch()['count'] ?? 0;
// New customers today
$cust_where = !$filter_branch ? "WHERE created_at >= CURDATE()" : "WHERE branch_id = ? AND created_at >= CURDATE()";
$cust_params = !$filter_branch ? [] : [$branch_id];
$stmt = db()->prepare("SELECT COUNT(*) as count FROM customers $cust_where");
$stmt->execute($cust_params);
$stats['new_customers'] = $stmt->fetch()['count'] ?? 0;
// Monthly Orders (Last 12 months)
$monthly_where = !$filter_branch ? "WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)" : "WHERE branch_id = ? AND created_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)";
$monthly_params = !$filter_branch ? [] : [$branch_id];
$stmt = db()->prepare("SELECT DATE_FORMAT(created_at, '%Y-%m') as month_year, COUNT(*) as count
FROM orders
$monthly_where
GROUP BY month_year
ORDER BY month_year ASC");
$stmt->execute($monthly_params);
$monthly_orders = $stmt->fetchAll();
// Charts data for Dashboard (Last 7 days)
$chart_where = !$filter_branch ? "WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)" : "WHERE branch_id = ? AND created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)";
$chart_params = !$filter_branch ? [] : [$branch_id];
// Daily Revenue
$stmt = db()->prepare("SELECT DATE(created_at) as date, SUM(total_price) as revenue
FROM orders
$chart_where
GROUP BY DATE(created_at)
ORDER BY DATE(created_at) ASC");
$stmt->execute($chart_params);
$daily_revenue = $stmt->fetchAll();
// Orders by Status (All time or current)
$status_where = !$filter_branch ? "" : "WHERE branch_id = ?";
$status_params = !$filter_branch ? [] : [$branch_id];
$stmt = db()->prepare("SELECT status, COUNT(*) as count FROM orders $status_where GROUP BY status");
$stmt->execute($status_params);
$orders_by_status = $stmt->fetchAll();
}
?>