Напреден развој на базата

Финансиски месечни пресметки и распределба на буџет (Finance Monthly Calculations & Allocation)

Опис на барањата за податочни ограничувања

Системот мора да обезбеди дека:

• Финансискиот корисник мора да внесе точно 5 процентуални вредности за распределба на буџетот.
• Секоја од процентуалните вредности мора да биде во опсег од 0 до 100.
• Збирот на сите 5 проценти мора да биде 100.
• Месечните приходи по корисник мора да можат да се пресметуваат без дополнителна логика во апликацијата.
• За тековниот месец мора да постои преглед на вкупно заработените средства и распределбата по буџетски категории.

Имплементација

Тригери

BEFORE INSERT и BEFORE UPDATE тригер на finance_users за валидација на 5-те процентуални полиња. Тригeрот гарантира дека сите вредности се внесени, се во дозволен опсег и дека збирот е 100.

CREATE OR REPLACE FUNCTION trekr.fn_validate_finance_percentages()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    s NUMERIC;
    v NUMERIC;
    vals NUMERIC[] := ARRAY[
        NEW.spending_budget,
        NEW.saving_budget,
        NEW.investing_budget,
        NEW.donation_budget,
        NEW.credit
    ];
    eps CONSTANT NUMERIC := 0.01;
BEGIN
    FOREACH v IN ARRAY vals LOOP
        IF v IS NULL THEN
            RAISE EXCEPTION 'All 5 finance percentage values are required';
        END IF;

        IF v < 0 OR v > 100 THEN
            RAISE EXCEPTION 'Finance percentage values must be between 0 and 100';
        END IF;
    END LOOP;

    s := (
        NEW.spending_budget +
        NEW.saving_budget +
        NEW.investing_budget +
        NEW.donation_budget +
        NEW.credit
    )::numeric;

    IF abs(s - 100) > eps THEN
        RAISE EXCEPTION 'Finance percentages must sum to 100 (got: %)', s;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_finance_percentages
BEFORE INSERT OR UPDATE ON trekr.finance_users
FOR EACH ROW
EXECUTE FUNCTION trekr.fn_validate_finance_percentages();

Функции / Stored Procedures

Функција за пресметка на вкупно заработени средства во тековниот месец за даден финансиски корисник.

CREATE OR REPLACE FUNCTION trekr.fn_finance_total_earned_this_month(p_user_id bigint)
RETURNS numeric
LANGUAGE sql
AS $$
    SELECT COALESCE(SUM(i.amount), 0)
    FROM trekr.incomes i
    WHERE i.finance_user_id = p_user_id
      AND date_trunc('month', i.date) = date_trunc('month', current_date);
$$;

Функција за пресметка на распределбата на тековниот месечен приход по 5-те финансиски категории.

CREATE OR REPLACE FUNCTION trekr.fn_finance_monthly_allocation(p_user_id bigint)
RETURNS TABLE (
    spending_amount numeric,
    saving_amount numeric,
    investing_amount numeric,
    donation_amount numeric,
    credit_amount numeric
)
LANGUAGE sql
AS $$
    SELECT
        COALESCE(f.spending_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS spending_amount,
        COALESCE(f.saving_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS saving_amount,
        COALESCE(f.investing_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS investing_amount,
        COALESCE(f.donation_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS donation_amount,
        COALESCE(f.credit, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS credit_amount
    FROM trekr.finance_users f
    LEFT JOIN trekr.vw_finance_current_month v
        ON v.user_id = f.user_id
    WHERE f.user_id = p_user_id;
$$;

Погледи (Views)

Поглед за месечен приход по корисник, по година и месец. Овој поглед се користи за историјски извештаи и агрегирани графици.

CREATE OR REPLACE VIEW trekr.vw_finance_monthly_summary AS
SELECT
    i.finance_user_id AS 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);

Поглед за вкупно заработени средства во тековниот месец по корисник. Овој поглед е основа за пресметка на распределбата на буџетот.

CREATE OR REPLACE VIEW trekr.vw_finance_current_month AS
SELECT
    f.user_id,
    COALESCE(SUM(i.amount), 0) AS total_earned_this_month
FROM trekr.finance_users f
LEFT JOIN trekr.incomes i
    ON i.finance_user_id = f.user_id
   AND date_trunc('month', i.date) = date_trunc('month', current_date)
GROUP BY f.user_id;

Поглед за распределба на тековниот месечен приход по буџетски категории. Погледот прикажува колку пари од вкупниот месечен приход припаѓаат на секоја категорија.

CREATE OR REPLACE VIEW trekr.vw_finance_allocations AS
SELECT
    f.user_id,
    v.total_earned_this_month,
    (f.spending_budget * v.total_earned_this_month / 100.0) AS spending_amount,
    (f.saving_budget * v.total_earned_this_month / 100.0) AS saving_amount,
    (f.investing_budget * v.total_earned_this_month / 100.0) AS investing_amount,
    (f.donation_budget * v.total_earned_this_month / 100.0) AS donation_amount,
    (f.credit * v.total_earned_this_month / 100.0) AS credit_amount
FROM trekr.finance_users f
LEFT JOIN trekr.vw_finance_current_month v
    ON v.user_id = f.user_id;

---

Следење на дневна завршеност на дисциплински задачи (Daily Discipline Completion)

Опис на барањата за податочни ограничувања

Системот мора да обезбеди дека:

• Дневната завршеност се пресметува за конкретен корисник и конкретен датум.
• Ако дневната завршеност за тој корисник и датум веќе постои, не смее да се креира дупликат.
• По пресметка на дневната завршеност, сите дисциплински задачи на тој корисник мора да се ресетираат на is_finished = false.
• Решението мора да може да се извршува и без апликациски сервер, преку складирана функција што може да се повикува од cron.

Имплементација

Тригери / Functions

Функција за пресметка на дневна завршеност за еден корисник и еден ден. Функцијата пресметува процент на завршени задачи и ја зачувува дневната завршеност само ако не постои веќе запис за тој датум.

CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion(p_user_id bigint, p_day date)
RETURNS TABLE(created boolean, daily_completion_id bigint, procent numeric)
LANGUAGE plpgsql
AS $$
DECLARE
    total_count bigint;
    finished_count bigint;
    pct numeric;
    dc_id bigint;
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM trekr.discipline_users du
        WHERE du.user_id = p_user_id
    ) THEN
        RAISE EXCEPTION 'Discipline tracking is not enabled for this user';
    END IF;

    SELECT dc.daily_completion_id, dc.procent
    INTO dc_id, pct
    FROM trekr.daily_completions dc
    WHERE dc.user_id = p_user_id
      AND dc.date = p_day
    LIMIT 1;

    IF dc_id IS NOT NULL THEN
        RETURN QUERY SELECT false, dc_id, pct;
        RETURN;
    END IF;

    SELECT COUNT(*)
    INTO total_count
    FROM trekr.tasks t
    WHERE t.discipline_user_id = p_user_id;

    SELECT COUNT(*)
    INTO finished_count
    FROM trekr.tasks t
    WHERE t.discipline_user_id = p_user_id
      AND t.is_finished = TRUE;

    pct := CASE
        WHEN total_count = 0 THEN 0
        ELSE ROUND((finished_count::numeric / total_count::numeric) * 100, 2)
    END;

    INSERT INTO trekr.daily_completions (user_id, date, procent)
    VALUES (p_user_id, p_day, pct)
    RETURNING daily_completion_id INTO dc_id;

    UPDATE trekr.tasks
    SET is_finished = FALSE
    WHERE discipline_user_id = p_user_id
      AND is_finished = TRUE;

    RETURN QUERY SELECT true, dc_id, pct;
END;
$$;

Функција за пресметка на дневна завршеност за сите дисциплински корисници за даден датум. Оваа функција е погодна за автоматско стартување преку cron job.

CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion_for_all(p_day date)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    u RECORD;
BEGIN
    FOR u IN
        SELECT user_id
        FROM trekr.discipline_users
    LOOP
        BEGIN
            PERFORM trekr.fn_compute_daily_completion(u.user_id, p_day);
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE 'compute_daily_completion failed for user %: %', u.user_id, SQLERRM;
        END;
    END LOOP;
END;
$$;

Погледи (Views)

Поглед за преглед на сите дневни завршености по корисник. Ги прикажува историјските проценти, датумите и корисникот на кој припаѓаат.

CREATE OR REPLACE VIEW trekr.vw_daily_completion_history AS
SELECT
    dc.daily_completion_id,
    dc.user_id,
    dc.date,
    dc.procent
FROM trekr.daily_completions dc
ORDER BY dc.date DESC, dc.daily_completion_id DESC;

---

Интеграција и употреба во апликацијата

Опис

Овие тригери, функции и погледи се користат за:

• валидација на внесот во финансискиот модул,
• автоматска пресметка на месечни финансиски суми,
• пресметка на распределба на буџет според тековниот приход,
• пресметка и архивирање на дневна дисциплинска завршеност,
• прикажување на агрегирани извештаи во frontend без дополнителни сложени пресметки.

Забелешка

SQL објектите се дизајнирани да бидат:

• идемпотентни каде што е можно,
• лесни за користење од backend и frontend,
• погодни за annual / monthly reporting,
• компатибилни со директно повикување од апликацијата или преку scheduled job.