Changes between Version 2 and Version 3 of AdvancedDatabaseDevelopment

Timestamp:

05/11/26 13:05:39 (2 weeks ago)

Author:

233062

Comment:

--

AdvancedDatabaseDevelopment

@@ -76 +76 @@
 ==== Функции / Stored Procedures ====
 
-Функција за пресметување на дневно завршување за еден корисник и датум. Вметнува ред во daily_completions (доколку не постои), ги поврзува завршените задачи и го ресетира нивниот статус.
+Функција за пресметување на дневно завршување за еден корисник и датум. Вметнува ред во daily_completion (доколку не постои), ги поврзува завршените задачи и го ресетира нивниот статус.
 {{{
 CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion(user_id bigint, day date)
@@ -103 +103 @@
     END IF;
 
+    -- FIX: table name daily_completion (not daily_completions)
     SELECT dc.daily_completion_id, dc.procent INTO dc_id, pct
-    FROM trekr.daily_completions dc
+    FROM trekr.daily_completion dc
     WHERE dc.user_id = user_id AND dc.date = day
     LIMIT 1;
@@ -113 +114 @@
     END IF;
 
-    SELECT COUNT(*) INTO total_count FROM trekr.tasks t WHERE t.discipline_user_id = (
-        SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
-    );
-
-    SELECT COUNT(*) INTO finished_count FROM trekr.tasks t WHERE t.discipline_user_id = (
-        SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
-    ) AND t.finished = true;
+    -- FIX: discipline_users has no surrogate discipline_user_id; use user_id directly
+    -- FIX: column is is_finished (not finished)
+    SELECT COUNT(*) INTO total_count
+    FROM trekr.tasks t
+    WHERE t.discipline_user_id = user_id;
+
+    SELECT COUNT(*) INTO finished_count
+    FROM trekr.tasks t
+    WHERE t.discipline_user_id = user_id
+      AND t.is_finished = true;
 
     IF total_count <= 0 THEN
@@ -127 +131 @@
     END IF;
 
-    INSERT INTO trekr.daily_completions (user_id, date, procent)
+    -- FIX: table name daily_completion (not daily_completions)
+    INSERT INTO trekr.daily_completion (user_id, date, procent)
     VALUES (user_id, day, pct)
     RETURNING daily_completion_id INTO dc_id;
 
+    -- FIX: table name task_daily_completion (not task_daily_completions)
+    -- FIX: column is is_finished (not finished)
+    -- FIX: use user_id directly instead of subquery for discipline_user_id
     FOR finished_tasks IN
         SELECT t.task_id FROM trekr.tasks t
-        WHERE t.discipline_user_id = (
-            SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
-        ) AND t.finished = true
+        WHERE t.discipline_user_id = user_id
+          AND t.is_finished = true
     LOOP
-        INSERT INTO trekr.task_daily_completions (task_id, daily_completion_id)
+        INSERT INTO trekr.task_daily_completion (task_id, daily_completion_id)
         VALUES (finished_tasks.task_id, dc_id)
         ON CONFLICT DO NOTHING;
     END LOOP;
 
-    UPDATE trekr.tasks t SET finished = false
-    WHERE t.discipline_user_id = (
-        SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
-    );
+    -- FIX: column is is_finished (not finished)
+    -- FIX: use user_id directly instead of subquery for discipline_user_id
+    UPDATE trekr.tasks t SET is_finished = false
+    WHERE t.discipline_user_id = user_id;
 
     RETURN QUERY SELECT true, dc_id, pct;
@@ -255 +262 @@
 === Имплементација ===
 
-==== Погледи (Views) ==== 
+==== Погледи (Views) ====
 
 Поглед за месечен приход по корисник — прикажува вкупен приход по корисник, месец и година.
 {{{
+-- FIX: incomes uses user_id as FK (not finance_user_id)
 CREATE OR REPLACE VIEW trekr.vw_finance_monthly_summary AS
 SELECT
-    i.finance_user_id AS user_id,
+    i.user_id,
     EXTRACT(YEAR FROM i.date)::int AS year,
     EXTRACT(MONTH FROM i.date)::int AS month,
     SUM(i.amount) AS total_income
 FROM trekr.incomes i
-GROUP BY i.finance_user_id, EXTRACT(YEAR FROM i.date), EXTRACT(MONTH FROM i.date);
+GROUP BY i.user_id, EXTRACT(YEAR FROM i.date), EXTRACT(MONTH FROM i.date);
 }}}
 
 Поглед за вкупниот приход на секој корисник во тековниот месец.
 {{{
+-- FIX: incomes uses user_id as FK (not finance_user_id)
 CREATE OR REPLACE VIEW trekr.vw_finance_current_month AS
 SELECT
@@ -277 +286 @@
 FROM trekr.finance_users f
 LEFT JOIN trekr.incomes i
-    ON i.finance_user_id = f.user_id
+    ON i.user_id = f.user_id
     AND date_trunc('month', i.date) = date_trunc('month', current_date)
 GROUP BY f.user_id;