Changes between Version 4 and Version 5 of AdvancedReports

Timestamp:

12/23/25 19:11:14 (10 days ago)

Author:

211101

Comment:

--

AdvancedReports

@@ -50 +50 @@
 ===== Надминување на баланс на една сметка со трансакција во сегашно време
 {{{#!sql
-SELECT 
-    u.user_id,
-    u.user_name,
-    ta.account_name,
-    ta.balance AS current_balance,
-    t.transaction_id,
-    t.transaction_name,
-    tb.spent_amount AS transaction_amount,
-    t.date AS transaction_date
-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 
-    tb.spent_amount > ta.balance -- Трансакцијата го надминува моменталниот баланс на СМЕТКАТА
-    AND tb.spent_amount > 0
-ORDER BY 
-    u.user_id, ta.account_name, t.date DESC;
+CREATE OR REPLACE FUNCTION get_current_account_overdrafts()
+RETURNS TABLE (
+    user_id INT,
+    user_name TEXT,
+    account_name TEXT,
+    current_balance NUMERIC,
+    transaction_id INT,
+    transaction_name TEXT,
+    transaction_amount NUMERIC,
+    transaction_date TIMESTAMP
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        u.user_id,
+        u.user_name,
+        ta.account_name,
+        ta.balance,
+        t.transaction_id,
+        t.transaction_name,
+        tb.spent_amount,
+        t.date
+    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 tb.spent_amount > ta.balance
+      AND tb.spent_amount > 0
+    ORDER BY t.date DESC;
+END;
+$$;
 }}}