Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

12/23/25 19:04:59 (10 days ago)

Author:

211101

Comment:

--

AdvancedReports

@@ -3 +3 @@
 ===== Листа на трансакции кои го надминале балансот на една сметка подредени хронолошки
 {{{#!sql
-WITH CumulativeBalances AS (
-    SELECT 
-        t.transaction_id,
-        t.transaction_name,
-        t.date AS transaction_date,
-        ta.account_name,
-        u.user_id,
-        u.user_name,
-        tb.spent_amount AS transaction_amount,
-        SUM(tb.earned_amount - tb.spent_amount) OVER (
-            PARTITION BY ta.transaction_account_id
-            ORDER BY t.date
-        ) AS calculated_balance
-    FROM 
-        transaction_account ta
-    JOIN 
-        user u ON ta.user_id = u.user_id
-    JOIN 
-        transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
-    JOIN 
-        transaction t ON tb.transaction_id = t.transaction_id
-    WHERE 
-        ta.account_name = 'Specific Account Name' -- Може да се замени со специфично име на акаунт
+CREATE OR REPLACE FUNCTION get_transactions_exceeding_account_balance(
+    p_account_name TEXT
 )
-SELECT 
-    user_id,
-    user_name,
-    account_name,
-    transaction_id,
-    transaction_name,
-    transaction_amount,
-    transaction_date,
-    calculated_balance
-FROM 
-    CumulativeBalances
-WHERE 
-    transaction_amount > calculated_balance -- Трансакцијата го надминува пресметаниот баланс
-    AND transaction_amount > 0
-ORDER BY 
-    user_id, account_name, transaction_date DESC;
+RETURNS TABLE (
+    user_id INT,
+    user_name TEXT,
+    account_name TEXT,
+    transaction_id INT,
+    transaction_name TEXT,
+    transaction_amount NUMERIC,
+    transaction_date TIMESTAMP,
+    calculated_balance NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    WITH cumulative_balances AS (
+        SELECT
+            u.user_id,
+            u.user_name,
+            ta.account_name,
+            t.transaction_id,
+            t.transaction_name,
+            tb.spent_amount AS transaction_amount,
+            t.date AS transaction_date,
+            SUM(tb.earned_amount - tb.spent_amount)
+                OVER (PARTITION BY ta.transaction_account_id ORDER BY t.date)
+                AS calculated_balance
+        FROM transaction_account ta
+        JOIN "user" u ON ta.user_id = u.user_id
+        JOIN transaction_breakdown tb ON ta.transaction_account_id = tb.transaction_account_id
+        JOIN transaction t ON tb.transaction_id = t.transaction_id
+        WHERE ta.account_name = p_account_name
+    )
+    SELECT *
+    FROM cumulative_balances
+    WHERE transaction_amount > calculated_balance
+      AND transaction_amount > 0
+    ORDER BY transaction_date DESC;
+END;
+$$;
+
 }}}