Spaces:
Sleeping
Sleeping
File size: 3,020 Bytes
56fb0d1 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | <?php
header("Content-Type: application/json");
include "db.php";
$revenueStmt = $conn->query("SELECT COALESCE(SUM(total), 0) as total FROM orders WHERE status != 'Failed' AND status != 'Invalid'");
$revenue = $revenueStmt->fetch()['total'];
$ordersStmt = $conn->query("SELECT COUNT(*) as count FROM orders");
$totalOrders = $ordersStmt->fetch()['count'];
$lowStockCountStmt = $conn->query("SELECT COUNT(*) as count FROM foods f LEFT JOIN stock s ON f.id = s.food_id WHERE COALESCE(s.quantity, 0) < 5");
$lowStock = $lowStockCountStmt->fetch()['count'];
$lowStockItemsStmt = $conn->query("SELECT f.*, COALESCE(s.quantity, 0) as stock FROM foods f LEFT JOIN stock s ON f.id = s.food_id WHERE COALESCE(s.quantity, 0) < 5");
$lowStockItems = $lowStockItemsStmt->fetchAll();
foreach($lowStockItems as &$item) {
$cat_emojis = [
'Beverages' => '🥤',
'Wafers' => '🍪',
'Snacks' => '🍟',
'Coffee & Drinks' => '☕',
'Hot Dogs' => '🌭',
'Biryani' => '🍛'
];
$item['emoji'] = $cat_emojis[$item['category']] ?? '🍽';
}
$recentOrdersStmt = $conn->query("SELECT o.*, u.name as user_name FROM orders o JOIN users u ON o.user_id = u.id ORDER BY o.created_at DESC LIMIT 10");
$recentOrders = $recentOrdersStmt->fetchAll();
// Frequent/Popular items (by order count)
$frequentItemsStmt = $conn->query("
SELECT oi.item_name, oi.food_id, f.image_url, f.category,
SUM(oi.quantity) as total_qty, COUNT(DISTINCT o.id) as order_count
FROM order_items oi
LEFT JOIN foods f ON oi.food_id = f.id
LEFT JOIN orders o ON oi.order_id = o.id
GROUP BY oi.item_name, oi.food_id, f.image_url, f.category
ORDER BY total_qty DESC
LIMIT 5
");
$frequentItems = $frequentItemsStmt->fetchAll();
// Add emojis to frequent items
foreach($frequentItems as &$item) {
$cat_emojis = [
'Beverages' => '🥤',
'Wafers' => '🍪',
'Snacks' => '🍟',
'Coffee & Drinks' => '☕',
'Hot Dogs' => '🌭',
'Biryani' => '🍛'
];
$item['emoji'] = $cat_emojis[$item['category']] ?? '🍽';
}
// Today's stats
$todayStmt = $conn->query("
SELECT
COUNT(*) as today_orders,
COALESCE(SUM(total), 0) as today_revenue
FROM orders
WHERE DATE(created_at) = CURDATE()
");
$todayStats = $todayStmt->fetch();
// Pending orders count
$pendingStmt = $conn->query("SELECT COUNT(*) as count FROM orders WHERE status = 'Pending'");
$pendingOrders = $pendingStmt->fetch()['count'];
echo json_encode([
"status" => "success",
"stats" => [
"revenue" => (float)$revenue,
"total_orders" => (int)$totalOrders,
"low_stock" => (int)$lowStock,
"low_stock_items" => $lowStockItems,
"frequent_items" => $frequentItems,
"today_orders" => (int)$todayStats['today_orders'],
"today_revenue" => (float)$todayStats['today_revenue'],
"pending_orders" => (int)$pendingOrders
],
"recent_orders" => $recentOrders
]);
?>
|