Changes between Version 1 and Version 2 of AnalyticalStatisticalQuerying

Timestamp:

02/13/25 16:28:42 (9 months ago)

Author:

221164

Comment:

--

AnalyticalStatisticalQuerying

@@ -140 +140 @@
 ORDER BY total_reservations DESC;
 }}}
+
+
+=== Stored procedure
+
+{{{
+CREATE OR REPLACE FUNCTION get_analytics_stats(
+    p_start_date DATE,
+    p_end_date DATE
+)
+RETURNS JSON AS $$
+DECLARE
+    result JSON;
+BEGIN
+    SELECT json_build_object(
+        'dailyRevenue', (
+            SELECT json_agg(r)
+            FROM (
+                SELECT DATE(timestamp) AS payment_date, SUM(amount) AS total_revenue
+                FROM payments
+                WHERE DATE(timestamp) BETWEEN p_start_date AND p_end_date
+                GROUP BY DATE(timestamp)
+                ORDER BY payment_date
+            ) r
+        ),
+        'dailyOrders', (
+            SELECT json_agg(r)
+            FROM (
+                SELECT DATE(o.datetime) AS order_date, COUNT(o.id) AS total_orders, AVG(p.amount) AS avg_order_value
+                FROM orders o
+                JOIN payments p ON o.id = p.order_id
+                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
+                GROUP BY DATE(o.datetime)
+                ORDER BY order_date
+            ) r
+        ),
+        'orderDistribution', (
+            SELECT json_agg(r)
+            FROM (
+                SELECT c.name AS category, COUNT(oi.id) AS total_items_ordered
+                FROM order_items oi
+                JOIN products p ON oi.product_id = p.id
+                JOIN categories c ON p.category_id = c.id
+                JOIN orders o ON o.id = oi.order_id  -- filter by order date
+                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
+                GROUP BY c.name
+                ORDER BY total_items_ordered DESC
+            ) r
+        ),
+        'topProducts', (
+            SELECT json_agg(r)
+            FROM (
+                SELECT p.name AS product, SUM(oi.quantity) AS total_quantity_sold
+                FROM order_items oi
+                JOIN products p ON oi.product_id = p.id
+                JOIN orders o ON o.id = oi.order_id  -- apply date filter
+                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
+                GROUP BY p.name
+                ORDER BY total_quantity_sold DESC
+                LIMIT 10
+            ) r
+        ),
+        'onlineVsTabOrders', (
+            SELECT json_agg(r)
+            FROM (
+                SELECT 'Online Orders' AS order_type, COUNT(*) AS total_orders
+                FROM online_orders oo
+                JOIN orders o ON oo.order_id = o.id
+                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
+                UNION ALL
+                SELECT 'Tab Orders' AS order_type, COUNT(*) AS total_orders
+                FROM tab_orders tord
+                JOIN orders o ON tord.order_id = o.id
+                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
+            ) r
+        ),
+        'revenueSplit', (
+            SELECT json_agg(r)
+            FROM (
+                SELECT 'Online Orders' AS order_type, SUM(p.amount) AS total_revenue
+                FROM orders o
+                JOIN payments p ON o.id = p.order_id
+                JOIN online_orders oo ON o.id = oo.order_id
+                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
+                UNION ALL
+                SELECT 'Tab Orders' AS order_type, SUM(p.amount) AS total_revenue
+                FROM orders o
+                JOIN payments p ON o.id = p.order_id
+                JOIN tab_orders tord ON o.id = tord.order_id
+                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
+            ) r
+        ),
+        'avgShiftDuration', (
+            SELECT json_agg(r)
+            FROM (
+                SELECT manager_id,
+                       AVG(EXTRACT(EPOCH FROM (end_time - start_time)) / 3600) AS avg_shift_duration_hours
+                FROM shifts
+                WHERE DATE(start_time) BETWEEN p_start_date AND p_end_date
+                GROUP BY manager_id
+            ) r
+        ),
+        'ordersByFrontStaff', (
+            SELECT json_agg(r)
+            FROM (
+                SELECT fs.employee_id, COUNT(tord.order_id) AS total_orders_managed
+                FROM tab_orders tord
+                JOIN front_staff fs ON tord.front_staff_id = fs.employee_id
+                JOIN orders o ON tord.order_id = o.id
+                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
+                GROUP BY fs.employee_id
+                ORDER BY total_orders_managed DESC
+            ) r
+        ),
+        'avgTipPercentage', (
+            SELECT json_agg(r)
+            FROM (
+                SELECT sr.name AS staff_role, AVG(fs.tip_percent) AS avg_tip_percent
+                FROM front_staff fs
+                JOIN staff_roles sr ON fs.staff_role_id = sr.id
+                GROUP BY sr.name
+            ) r
+        ),
+        'inventoryTurnover', (
+            SELECT json_agg(r)
+            FROM (
+                SELECT p.name AS product, inv.quantity AS current_inventory,
+                       SUM(oi.quantity) AS total_quantity_ordered,
+                       CASE WHEN inv.quantity = 0 THEN NULL ELSE SUM(oi.quantity)::decimal / inv.quantity END AS turnover_ratio
+                FROM products p
+                JOIN inventories inv ON p.id = inv.product_id
+                JOIN order_items oi ON p.id = oi.product_id
+                JOIN orders o ON o.id = oi.order_id
+                WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
+                GROUP BY p.name, inv.quantity
+                ORDER BY turnover_ratio DESC
+            ) r
+        ),
+        'mostPopularTable', (
+            SELECT json_build_object(
+                'table_number', table_number,
+                'reservation_count', reservation_count
+            )
+            FROM (
+                SELECT table_number, COUNT(*) AS reservation_count
+                FROM frontstaff_managed_reservations
+                WHERE DATE(datetime) BETWEEN p_start_date AND p_end_date
+                GROUP BY table_number
+                ORDER BY reservation_count DESC
+                LIMIT 1
+            ) sub
+        ),
+        'reservationsByDayOfWeek', (
+            SELECT json_agg(r)
+            FROM (
+                SELECT EXTRACT(DOW FROM datetime) AS day_of_week, COUNT(*) AS total_reservations
+                FROM reservations
+                WHERE DATE(datetime) BETWEEN p_start_date AND p_end_date
+                GROUP BY day_of_week
+                ORDER BY total_reservations DESC
+            ) r
+        )
+    ) INTO result;
+    
+    RETURN result;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+{{{
+SELECT get_analytics_stats('2025-01-01', '2025-01-31');
+}}}