Changes between Version 1 and Version 2 of AdvancedReport14

Timestamp:

12/29/25 19:53:12 (4 days ago)

Author:

211101

Comment:

--

AdvancedReport14

@@ -1 @@
-===== Долг на кредитна картичка од минатиот месец за корисник
+==== Долг на кредитна картичка од минатиот месец за корисник
 Потрошено од сметки означени како „кредитна картичка“ за минатиот месец за еден корисник
 {{{#!sql
-SELECT 
-    SUM(tb.spent_amount) AS credit_card_debt
-FROM 
-    transaction_breakdown tb
-JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
-JOIN transaction t ON tb.transaction_id = t.transaction_id
-WHERE 
-    ta.user_id = 101 -- ID на конкретниот корисник
-    AND (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
-    AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
-    AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
+CREATE OR REPLACE FUNCTION get_credit_card_debt_last_month_user(
+    p_user_id INT
+)
+RETURNS TABLE (
+    credit_card_debt NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT 
+        COALESCE(SUM(tb.spent_amount), 0) AS credit_card_debt
+    FROM transaction_breakdown tb
+    JOIN transaction_account ta
+        ON tb.transaction_account_id = ta.transaction_account_id
+    JOIN transaction t
+        ON tb.transaction_id = t.transaction_id
+    WHERE ta.user_id = p_user_id
+      AND (ta.account_name ILIKE '%кредитна%' OR ta.account_name ILIKE '%credit%')
+      AND EXTRACT(MONTH FROM t.date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH')
+      AND EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH');
+END;
+$$;
 }}}
+
+==== Релациона алгебра
+- TA(transaction_account_id, user_id, account_name)
+- TB(transaction_id, transaction_account_id, spent_amount)
+- T(transaction_id, date)
+
+JOIN на сите табели:
+- J1 ← TA ⨝,,TA.transaction_account_id = TB.transaction_account_id,, TB
+- J2 ← J1 ⨝,,TB.transaction_id = T.transaction_id,, T
+
+Филтрирање по корисник, кредитна картичка и минат месец:
+- F ← σ,,user_id = p_user_id ∧ (account_name ILIKE '%кредитна%' OR account_name ILIKE '%credit%') ∧ EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 MONTH') ∧ EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 MONTH'),,(J2)
+
+Агрегација на вкупен долг:
+- R_final ← γ,,; SUM(spent_amount) → credit_card_debt,,(F)