Changes between Version 1 and Version 2 of AdvancedDatabaseDevelopment


Ignore:
Timestamp:
05/07/26 14:12:53 (3 days ago)
Author:
233062
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedDatabaseDevelopment

    v1 v2  
    11= Напреден развој на базата =
    22
    3 == Финансиски месечни пресметки и распределба на буџет (Finance Monthly Calculations & Allocation) ==
     3== Валидација на финансиски проценти (Finance Percentage Validation) ==
    44
    55=== Опис на барањата за податочни ограничувања ===
    66
    77Системот мора да обезбеди дека:
    8 * Финансискиот корисник мора да внесе точно 5 процентуални вредности за распределба на буџетот.
    9 * Секоја од процентуалните вредности мора да биде во опсег од 0 до 100.
    10 * Збирот на сите 5 проценти мора да биде 100.
    11 * Месечните приходи по корисник мора да можат да се пресметуваат без дополнителна логика во апликацијата.
    12 * За тековниот месец мора да постои преглед на вкупно заработените средства и распределбата по буџетски категории.
     8* Сите пет вредности за буџет мора да бидат внесени (не смеат да бидат NULL)
     9* Секоја вредност мора да биде во опсегот [0, 100]
     10* Збирот на сите пет вредности мора да биде 100 (со толеранција од 0.01 за мали разлики при заокружување)
     11* Ограничувањето се применува и при INSERT и при UPDATE на табелата finance_users
    1312
    1413=== Имплементација ===
    … …  
    1615==== Тригери ====
    1716
    18 BEFORE INSERT и BEFORE UPDATE тригер на finance_users за валидација на 5-те процентуални полиња.
    19 Тригeрот гарантира дека сите вредности се внесени, се во дозволен опсег и дека збирот е 100.
     17BEFORE INSERT OR UPDATE тригер на finance_users за валидација дека сите пет буџетски проценти се валидни и нивниот збир е 100.
    2018{{{
    2119CREATE OR REPLACE FUNCTION trekr.fn_validate_finance_percentages()
    … …  
    2523DECLARE
    2624    s NUMERIC;
     25    eps CONSTANT NUMERIC := 0.01;
     26    vals NUMERIC[] := ARRAY[NEW.spending_budget, NEW.saving_budget, NEW.investing_budget, NEW.donation_budget, NEW.credit];
    2727    v NUMERIC;
    28     vals NUMERIC[] := ARRAY[
    29         NEW.spending_budget,
    30         NEW.saving_budget,
    31         NEW.investing_budget,
    32         NEW.donation_budget,
    33         NEW.credit
    34     ];
    35     eps CONSTANT NUMERIC := 0.01;
    3628BEGIN
    3729    FOREACH v IN ARRAY vals LOOP
    … …  
    3931            RAISE EXCEPTION 'All 5 finance percentage values are required';
    4032        END IF;
    41 
    4233        IF v < 0 OR v > 100 THEN
    4334            RAISE EXCEPTION 'Finance percentage values must be between 0 and 100';
    … …  
    4536    END LOOP;
    4637
    47     s := (
    48         NEW.spending_budget +
    49         NEW.saving_budget +
    50         NEW.investing_budget +
    51         NEW.donation_budget +
    52         NEW.credit
    53     )::numeric;
    54 
     38    s := (NEW.spending_budget + NEW.saving_budget + NEW.investing_budget + NEW.donation_budget + NEW.credit)::numeric;
    5539    IF abs(s - 100) > eps THEN
    5640        RAISE EXCEPTION 'Finance percentages must sum to 100 (got: %)', s;
    … …  
    6145$$;
    6246
    63 CREATE TRIGGER trg_validate_finance_percentages
    64 BEFORE INSERT OR UPDATE ON trekr.finance_users
    65 FOR EACH ROW
    66 EXECUTE FUNCTION trekr.fn_validate_finance_percentages();
    67 }}}
     47DO $$
     48BEGIN
     49    IF NOT EXISTS (
     50        SELECT 1 FROM pg_trigger t
     51        JOIN pg_class c ON t.tgrelid = c.oid
     52        WHERE t.tgname = 'trg_validate_finance_percentages' AND c.relname = 'finance_users'
     53    ) THEN
     54        CREATE TRIGGER trg_validate_finance_percentages
     55        BEFORE INSERT OR UPDATE ON trekr.finance_users
     56        FOR EACH ROW
     57        EXECUTE FUNCTION trekr.fn_validate_finance_percentages();
     58    END IF;
     59END$$;
     60}}}
     61
     62----
     63
     64== Пресметување на дневни завршувања (Daily Completion Computation) ==
     65
     66=== Опис на барањата за податочни ограничувања ===
     67
     68Системот мора да обезбеди дека:
     69* Дневното завршување може да се пресмета само за корисник со овозможено следење (discipline_users)
     70* Не смее да се пресметува за иден датум
     71* Ако веќе постои запис за тој корисник и датум, се враќа постоечкиот резултат без дупликат
     72* По пресметувањето, завршените задачи се врзуваат за дневниот запис, а потоа нивниот статус се ресетира
     73
     74=== Имплементација ===
    6875
    6976==== Функции / Stored Procedures ====
    7077
    71 Функција за пресметка на вкупно заработени средства во тековниот месец за даден финансиски корисник.
    72 {{{
    73 CREATE OR REPLACE FUNCTION trekr.fn_finance_total_earned_this_month(p_user_id bigint)
    74 RETURNS numeric
    75 LANGUAGE sql
     78Функција за пресметување на дневно завршување за еден корисник и датум. Вметнува ред во daily_completions (доколку не постои), ги поврзува завршените задачи и го ресетира нивниот статус.
     79{{{
     80CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion(user_id bigint, day date)
     81RETURNS TABLE(created boolean, daily_completion_id bigint, procent numeric)
     82LANGUAGE plpgsql
    7683AS $$
    77     SELECT COALESCE(SUM(i.amount), 0)
    78     FROM trekr.incomes i
    79     WHERE i.finance_user_id = p_user_id
    80       AND date_trunc('month', i.date) = date_trunc('month', current_date);
     84DECLARE
     85    total_count bigint;
     86    finished_count bigint;
     87    pct numeric;
     88    dc_id bigint;
     89    finished_tasks RECORD;
     90BEGIN
     91    IF user_id IS NULL THEN
     92        RAISE EXCEPTION 'user_id is required';
     93    END IF;
     94    IF day IS NULL THEN
     95        RAISE EXCEPTION 'day is required';
     96    END IF;
     97    IF day > current_date THEN
     98        RAISE EXCEPTION 'date cannot be in the future';
     99    END IF;
     100
     101    IF NOT EXISTS (SELECT 1 FROM trekr.discipline_users du WHERE du.user_id = user_id) THEN
     102        RAISE EXCEPTION 'Discipline tracking is not enabled for this user';
     103    END IF;
     104
     105    SELECT dc.daily_completion_id, dc.procent INTO dc_id, pct
     106    FROM trekr.daily_completions dc
     107    WHERE dc.user_id = user_id AND dc.date = day
     108    LIMIT 1;
     109
     110    IF dc_id IS NOT NULL THEN
     111        RETURN QUERY SELECT false, dc_id, pct;
     112        RETURN;
     113    END IF;
     114
     115    SELECT COUNT(*) INTO total_count FROM trekr.tasks t WHERE t.discipline_user_id = (
     116        SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
     117    );
     118
     119    SELECT COUNT(*) INTO finished_count FROM trekr.tasks t WHERE t.discipline_user_id = (
     120        SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
     121    ) AND t.finished = true;
     122
     123    IF total_count <= 0 THEN
     124        pct := 0;
     125    ELSE
     126        pct := round((finished_count::numeric * 100) / total_count::numeric, 2);
     127    END IF;
     128
     129    INSERT INTO trekr.daily_completions (user_id, date, procent)
     130    VALUES (user_id, day, pct)
     131    RETURNING daily_completion_id INTO dc_id;
     132
     133    FOR finished_tasks IN
     134        SELECT t.task_id FROM trekr.tasks t
     135        WHERE t.discipline_user_id = (
     136            SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
     137        ) AND t.finished = true
     138    LOOP
     139        INSERT INTO trekr.task_daily_completions (task_id, daily_completion_id)
     140        VALUES (finished_tasks.task_id, dc_id)
     141        ON CONFLICT DO NOTHING;
     142    END LOOP;
     143
     144    UPDATE trekr.tasks t SET finished = false
     145    WHERE t.discipline_user_id = (
     146        SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
     147    );
     148
     149    RETURN QUERY SELECT true, dc_id, pct;
     150END;
    81151$$;
    82152}}}
    83153
    84 Функција за пресметка на распределбата на тековниот месечен приход по 5-те финансиски категории.
    85 {{{
    86 CREATE OR REPLACE FUNCTION trekr.fn_finance_monthly_allocation(p_user_id bigint)
    87 RETURNS TABLE (
    88     spending_amount numeric,
    89     saving_amount numeric,
    90     investing_amount numeric,
    91     donation_amount numeric,
    92     credit_amount numeric
    93 )
    94 LANGUAGE sql
     154Функција за пресметување на дневни завршувања за сите корисници со овозможено следење за даден датум. Грешките по корисник се логираат и се продолжува понатаму.
     155{{{
     156CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion_for_all(day date)
     157RETURNS void
     158LANGUAGE plpgsql
    95159AS $$
    96     SELECT
    97         COALESCE(f.spending_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS spending_amount,
    98         COALESCE(f.saving_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS saving_amount,
    99         COALESCE(f.investing_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS investing_amount,
    100         COALESCE(f.donation_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS donation_amount,
    101         COALESCE(f.credit, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS credit_amount
    102     FROM trekr.finance_users f
    103     LEFT JOIN trekr.vw_finance_current_month v
    104         ON v.user_id = f.user_id
    105     WHERE f.user_id = p_user_id;
     160DECLARE
     161    u RECORD;
     162BEGIN
     163    IF day IS NULL THEN
     164        RAISE EXCEPTION 'day is required';
     165    END IF;
     166
     167    FOR u IN SELECT user_id FROM trekr.discipline_users LOOP
     168        BEGIN
     169            PERFORM trekr.fn_compute_daily_completion(u.user_id, day);
     170        EXCEPTION WHEN OTHERS THEN
     171            RAISE NOTICE 'compute_daily_completion failed for user %: %', u.user_id, SQLERRM;
     172        END;
     173    END LOOP;
     174END;
    106175$$;
    107 }}}
    108 
    109 ==== Погледи (Views) ====
    110 
    111 Поглед за месечен приход по корисник, по година и месец.
    112 Овој поглед се користи за историјски извештаи и агрегирани графици.
     176
     177-- Опционално: pg_cron задача за секојдневно извршување (бара pg_cron екстензија)
     178-- CREATE EXTENSION IF NOT EXISTS pg_cron;
     179-- SELECT cron.schedule('compute_daily_completions_every_day', '59 23 * * *',
     180--     $$SELECT trekr.fn_compute_daily_completion_for_all(current_date - INTERVAL '1 day')$$);
     181}}}
     182
     183----
     184
     185== Дополнителни ограничувања на базата (Additional DB Constraints) ==
     186
     187=== Опис на барањата за податочни ограничувања ===
     188
     189Системот мора да обезбеди дека:
     190* Корисникот може да има само еден дневен внес (daily intake) по датум
     191* Тренинг сесиите не смеат да имаат иден датум
     192
     193=== Имплементација ===
     194
     195==== Индекси ====
     196
     197Уникатен индекс на daily_intakes за осигурување дека еден корисник може да има најмногу еден внес по датум.
     198{{{
     199DO $$
     200BEGIN
     201    IF NOT EXISTS (
     202        SELECT 1 FROM pg_indexes
     203        WHERE schemaname = 'trekr'
     204          AND tablename = 'daily_intakes'
     205          AND indexname = 'uq_daily_intake_user_date'
     206    ) THEN
     207        CREATE UNIQUE INDEX uq_daily_intake_user_date
     208        ON trekr.daily_intakes (weight_user_id, date);
     209    END IF;
     210END$$;
     211}}}
     212
     213==== Тригери ====
     214
     215BEFORE INSERT OR UPDATE тригер на training_sessions за спречување на внес со иден датум.
     216{{{
     217CREATE OR REPLACE FUNCTION trekr.fn_check_training_date()
     218RETURNS trigger
     219LANGUAGE plpgsql
     220AS $$
     221BEGIN
     222    IF NEW.date > current_date THEN
     223        RAISE EXCEPTION 'Training session date cannot be in the future: %', NEW.date;
     224    END IF;
     225    RETURN NEW;
     226END;
     227$$;
     228
     229DO $$
     230BEGIN
     231    IF NOT EXISTS (
     232        SELECT 1 FROM pg_trigger t
     233        JOIN pg_class c ON t.tgrelid = c.oid
     234        WHERE t.tgname = 'trg_check_training_date' AND c.relname = 'training_sessions'
     235    ) THEN
     236        CREATE TRIGGER trg_check_training_date
     237        BEFORE INSERT OR UPDATE ON trekr.training_sessions
     238        FOR EACH ROW
     239        EXECUTE FUNCTION trekr.fn_check_training_date();
     240    END IF;
     241END$$;
     242}}}
     243
     244----
     245
     246== Прегледи за финансии (Finance Views) ==
     247
     248=== Опис на барањата за податочни ограничувања ===
     249
     250Системот мора да обезбеди дека:
     251* Постои преглед за месечен приход по корисник и период
     252* Постои преглед за вкупниот приход на корисникот во тековниот месец
     253* Постои преглед за пресметани апсолутни износи по категорија врз основа на процентите и приходот во тековниот месец
     254
     255=== Имплементација ===
     256
     257==== Погледи (Views) ====
     258
     259Поглед за месечен приход по корисник — прикажува вкупен приход по корисник, месец и година.
    113260{{{
    114261CREATE OR REPLACE VIEW trekr.vw_finance_monthly_summary AS
    … …  
    122269}}}
    123270
    124 Поглед за вкупно заработени средства во тековниот месец по корисник.
    125 Овој поглед е основа за пресметка на распределбата на буџетот.
     271Поглед за вкупниот приход на секој корисник во тековниот месец.
    126272{{{
    127273CREATE OR REPLACE VIEW trekr.vw_finance_current_month AS
    … …  
    132278LEFT JOIN trekr.incomes i
    133279    ON i.finance_user_id = f.user_id
    134    AND date_trunc('month', i.date) = date_trunc('month', current_date)
     280    AND date_trunc('month', i.date) = date_trunc('month', current_date)
    135281GROUP BY f.user_id;
    136282}}}
    137283
    138 Поглед за распределба на тековниот месечен приход по буџетски категории.
    139 Погледот прикажува колку пари од вкупниот месечен приход припаѓаат на секоја категорија.
    140 {{{
    141 CREATE OR REPLACE VIEW trekr.vw_finance_allocations AS
     284Поглед за пресметување на апсолутни износи по финансиска категорија за тековниот месец, врз основа на буџетските проценти и вкупниот приход.
     285{{{
     286CREATE OR REPLACE VIEW trekr.vw_finance_allocations_current_month AS
    142287SELECT
    143288    f.user_id,
    144     v.total_earned_this_month,
    145     (f.spending_budget * v.total_earned_this_month / 100.0) AS spending_amount,
    146     (f.saving_budget * v.total_earned_this_month / 100.0) AS saving_amount,
    147     (f.investing_budget * v.total_earned_this_month / 100.0) AS investing_amount,
    148     (f.donation_budget * v.total_earned_this_month / 100.0) AS donation_amount,
    149     (f.credit * v.total_earned_this_month / 100.0) AS credit_amount
     289    fm.total_earned_this_month,
     290    f.spending_budget,
     291    f.saving_budget,
     292    f.investing_budget,
     293    f.donation_budget,
     294    f.credit,
     295    ROUND((COALESCE(f.spending_budget, 0) / 100.0) * fm.total_earned_this_month, 2) AS spending_amount,
     296    ROUND((COALESCE(f.saving_budget, 0) / 100.0) * fm.total_earned_this_month, 2) AS saving_amount,
     297    ROUND((COALESCE(f.investing_budget, 0) / 100.0) * fm.total_earned_this_month, 2) AS investing_amount,
     298    ROUND((COALESCE(f.donation_budget, 0) / 100.0) * fm.total_earned_this_month, 2) AS donation_amount,
     299    ROUND((COALESCE(f.credit, 0) / 100.0) * fm.total_earned_this_month, 2) AS credit_amount
    150300FROM trekr.finance_users f
    151 LEFT JOIN trekr.vw_finance_current_month v
    152     ON v.user_id = f.user_id;
    153 }}}
    154 
    155 ----
    156 
    157 == Следење на дневна завршеност на дисциплински задачи (Daily Discipline Completion) ==
    158 
    159 === Опис на барањата за податочни ограничувања ===
    160 
    161 Системот мора да обезбеди дека:
    162 * Дневната завршеност се пресметува за конкретен корисник и конкретен датум.
    163 * Ако дневната завршеност за тој корисник и датум веќе постои, не смее да се креира дупликат.
    164 * По пресметка на дневната завршеност, сите дисциплински задачи на тој корисник мора да се ресетираат на `is_finished = false`.
    165 * Решението мора да може да се извршува и без апликациски сервер, преку складирана функција што може да се повикува од cron.
    166 
    167 === Имплементација ===
    168 
    169 ==== Тригери / Functions ====
    170 
    171 Функција за пресметка на дневна завршеност за еден корисник и еден ден.
    172 Функцијата пресметува процент на завршени задачи и ја зачувува дневната завршеност само ако не постои веќе запис за тој датум.
    173 {{{
    174 CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion(p_user_id bigint, p_day date)
    175 RETURNS TABLE(created boolean, daily_completion_id bigint, procent numeric)
    176 LANGUAGE plpgsql
    177 AS $$
    178 DECLARE
    179     total_count bigint;
    180     finished_count bigint;
    181     pct numeric;
    182     dc_id bigint;
    183 BEGIN
    184     IF NOT EXISTS (
    185         SELECT 1
    186         FROM trekr.discipline_users du
    187         WHERE du.user_id = p_user_id
    188     ) THEN
    189         RAISE EXCEPTION 'Discipline tracking is not enabled for this user';
    190     END IF;
    191 
    192     SELECT dc.daily_completion_id, dc.procent
    193     INTO dc_id, pct
    194     FROM trekr.daily_completions dc
    195     WHERE dc.user_id = p_user_id
    196       AND dc.date = p_day
    197     LIMIT 1;
    198 
    199     IF dc_id IS NOT NULL THEN
    200         RETURN QUERY SELECT false, dc_id, pct;
    201         RETURN;
    202     END IF;
    203 
    204     SELECT COUNT(*)
    205     INTO total_count
    206     FROM trekr.tasks t
    207     WHERE t.discipline_user_id = p_user_id;
    208 
    209     SELECT COUNT(*)
    210     INTO finished_count
    211     FROM trekr.tasks t
    212     WHERE t.discipline_user_id = p_user_id
    213       AND t.is_finished = TRUE;
    214 
    215     pct := CASE
    216         WHEN total_count = 0 THEN 0
    217         ELSE ROUND((finished_count::numeric / total_count::numeric) * 100, 2)
    218     END;
    219 
    220     INSERT INTO trekr.daily_completions (user_id, date, procent)
    221     VALUES (p_user_id, p_day, pct)
    222     RETURNING daily_completion_id INTO dc_id;
    223 
    224     UPDATE trekr.tasks
    225     SET is_finished = FALSE
    226     WHERE discipline_user_id = p_user_id
    227       AND is_finished = TRUE;
    228 
    229     RETURN QUERY SELECT true, dc_id, pct;
    230 END;
    231 $$;
    232 }}}
    233 
    234 Функција за пресметка на дневна завршеност за сите дисциплински корисници за даден датум.
    235 Оваа функција е погодна за автоматско стартување преку cron job.
    236 {{{
    237 CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion_for_all(p_day date)
    238 RETURNS void
    239 LANGUAGE plpgsql
    240 AS $$
    241 DECLARE
    242     u RECORD;
    243 BEGIN
    244     FOR u IN
    245         SELECT user_id
    246         FROM trekr.discipline_users
    247     LOOP
    248         BEGIN
    249             PERFORM trekr.fn_compute_daily_completion(u.user_id, p_day);
    250         EXCEPTION
    251             WHEN OTHERS THEN
    252                 RAISE NOTICE 'compute_daily_completion failed for user %: %', u.user_id, SQLERRM;
    253         END;
    254     END LOOP;
    255 END;
    256 $$;
    257 }}}
    258 
    259 ==== Погледи (Views) ====
    260 
    261 Поглед за преглед на сите дневни завршености по корисник.
    262 Ги прикажува историјските проценти, датумите и корисникот на кој припаѓаат.
    263 {{{
    264 CREATE OR REPLACE VIEW trekr.vw_daily_completion_history AS
    265 SELECT
    266     dc.daily_completion_id,
    267     dc.user_id,
    268     dc.date,
    269     dc.procent
    270 FROM trekr.daily_completions dc
    271 ORDER BY dc.date DESC, dc.daily_completion_id DESC;
    272 }}}
    273 
    274 ----
    275 
    276 == Интеграција и употреба во апликацијата ==
    277 
    278 === Опис ===
    279 
    280 Овие тригери, функции и погледи се користат за:
    281 * валидација на внесот во финансискиот модул,
    282 * автоматска пресметка на месечни финансиски суми,
    283 * пресметка на распределба на буџет според тековниот приход,
    284 * пресметка и архивирање на дневна дисциплинска завршеност,
    285 * прикажување на агрегирани извештаи во frontend без дополнителни сложени пресметки.
    286 
    287 === Забелешка ===
    288 
    289 SQL објектите се дизајнирани да бидат:
    290 * идемпотентни каде што е можно,
    291 * лесни за користење од backend и frontend,
    292 * погодни за annual / monthly reporting,
    293 * компатибилни со директно повикување од апликацијата или преку scheduled job.
     301LEFT JOIN trekr.vw_finance_current_month fm ON fm.user_id = f.user_id;
     302}}}