Changes between Initial Version and Version 1 of AdvancedReport7

Timestamp:

09/24/26 02:08:45 (7 days ago)

Author:

201178

Comment:

--

AdvancedReport7

@@ +1 @@
+= Месечни приходи и расходи =
+
+== Опис ==
+
+Извештајот ги прикажува вкупните приходи по месеци, заедно со бројот на нарачки, просечната плаќања и бројот на активни келнери.
+
+== SQL решение ==
+
+{{{
+CREATE OR REPLACE FUNCTION get_monthly_revenue()
+RETURNS TABLE (
+    month TEXT,
+    order_count BIGINT,
+    total_revenue NUMERIC,
+    avg_payment NUMERIC,
+    tables_used BIGINT,
+    waiters_active BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        TO_CHAR(p.payment_date, 'YYYY-MM')::TEXT AS month,
+        COUNT(DISTINCT o.order_id) AS order_count,
+        SUM(p.amount) AS total_revenue,
+        ROUND(AVG(p.amount), 2) AS avg_payment,
+        COUNT(DISTINCT o.table_id) AS tables_used,
+        COUNT(DISTINCT o.user_id) AS waiters_active
+    FROM project.payment p
+    JOIN project.orders o ON o.order_id = p.order_id
+    WHERE o.status = 'ПЛАТЕНА'
+    GROUP BY TO_CHAR(p.payment_date, 'YYYY-MM')
+    ORDER BY month DESC;
+END;
+$$;
+}}}
+
+== Релациона алгебра ==
+
+{{{
+P(payment_id, order_id, amount, payment_date)
+O(order_id, table_id, user_id, status)
+
+J1 ← P ⨝ P.order_id = O.order_id O
+
+F1 ← σ status='ПЛАТЕНА' (J1)
+
+G ← γ month;
+     COUNT(DISTINCT order_id) → order_count,
+     SUM(amount) → total_revenue,
+     AVG(amount) → avg_payment,
+     COUNT(DISTINCT table_id) → tables_used,
+     COUNT(DISTINCT user_id) → waiters_active (F1)
+
+R ← π month, order_count, total_revenue, avg_payment, tables_used, waiters_active (G)
+
+R_final ← τ month DESC (R)
+}}}