Фаза 4: Функции, процедури и тригери

Во оваа фаза се надоградуваме со функции и процедури што ќе помогнат дел од честите операции во базата — отворање записник, додавање прекршок, наплата на казна, додавање категорија на возачка — да се извршуваат преку еден повик, со сите потребни проверки веќе вградени во нив. Исто така искористивме и тригери за работите што треба да се случуваат автоматски, како пресметката на вкупниот износ во уплатата, без рачно ажурирање.

1. Тригери

Тригер: Автоматска пресметка на износот во Uplata

Секој пат кога ќе се додаде, измени или избрише ставка во Stavka_Zapisnik, тригерот ја пресметува вкупната сума на сите казни за тој записник и ја запишува во соодветната уплата. Така износот во Uplata секогаш е точен и не мора рачно да го ажурираме.

CREATE OR REPLACE FUNCTION azuriraj_iznos_uplata()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
DECLARE
    v_id_zapisnik int;
    v_nov_iznos   numeric(10,2);
BEGIN
    IF TG_OP = 'DELETE' THEN
        v_id_zapisnik := OLD.id_na_zapisnik;
    ELSE
        v_id_zapisnik := NEW.id_na_zapisnik;
    END IF;

    SELECT COALESCE(SUM(k.iznos_kazna), 0)
    INTO v_nov_iznos
    FROM Stavka_Zapisnik sz
    JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
    JOIN Kazna    k ON p.id_kazna        = k.id_kazna
    WHERE sz.id_na_zapisnik = v_id_zapisnik;

    UPDATE Uplata
    SET iznos = v_nov_iznos
    WHERE id_zapisnik = v_id_zapisnik
      AND status = 'Neplateno';

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

CREATE OR REPLACE TRIGGER trigger_azuriraj_iznos_uplata
    AFTER INSERT OR UPDATE OR DELETE ON Stavka_Zapisnik
    FOR EACH ROW
    EXECUTE FUNCTION azuriraj_iznos_uplata();

2. Процедури

Процедура 1: kreiraj_zapisnik_so_prekrsok

Отвора нов записник со прв прекршок и веднаш креира празна уплата со статус Neplateno. Износот го пополнува тригерот штом ќе се вметне првата ставка. Прво проверува дали постојат граѓанинот, полицаецот, возилото, прекршокот и случајот.

CREATE OR REPLACE PROCEDURE kreiraj_zapisnik_so_prekrsok(
    p_embg_prekrsuvac char(13),
    p_broj_sasija     varchar(17),
    p_embg_policaec   char(13),
    p_lokacija        varchar(100),
    p_id_slucaj       int,
    p_id_prekrsok     int,
    p_vreme           time    DEFAULT CURRENT_TIME,
    p_datum           date    DEFAULT CURRENT_DATE,
    p_potpis          boolean DEFAULT false
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_id_zapisnik int;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Gragjanin WHERE EMBG = p_embg_prekrsuvac) THEN
        RAISE EXCEPTION 'Gragjaninot so EMBG % ne postoi!', p_embg_prekrsuvac;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM Policaec WHERE EMBG_P = p_embg_policaec) THEN
        RAISE EXCEPTION 'Policaecot so EMBG % ne postoi!', p_embg_policaec;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM Vozilo WHERE broj_na_sasija = p_broj_sasija) THEN
        RAISE EXCEPTION 'Voziloto so broj na sasija % ne postoi!', p_broj_sasija;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM Prekrsok WHERE id_prekrsok = p_id_prekrsok) THEN
        RAISE EXCEPTION 'Prekrsokot so id % ne postoi!', p_id_prekrsok;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM Slucaj WHERE id_slucaj = p_id_slucaj) THEN
        RAISE EXCEPTION 'Slucajot so id % ne postoi!', p_id_slucaj;
    END IF;

    INSERT INTO Zapisnik (vreme, datum, lokacija, Potpis,
        id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec)
    VALUES (p_vreme, p_datum, p_lokacija, p_potpis,
        p_id_slucaj, p_embg_prekrsuvac, p_broj_sasija, p_embg_policaec)
    RETURNING id_na_zapisnik INTO v_id_zapisnik;

    INSERT INTO Uplata (iznos, status, Uplatil_Gragjanin, id_zapisnik)
    VALUES (0, 'Neplateno', p_embg_prekrsuvac, v_id_zapisnik);

    INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok)
    VALUES (1, v_id_zapisnik, p_id_prekrsok);

    RAISE NOTICE 'Kreiran zapisnik so id % so prv prekrsok %.', v_id_zapisnik, p_id_prekrsok;
END;
$$;

Повик:

CALL kreiraj_zapisnik_so_prekrsok(
    '1508004480145', 'CE8C5F1D5AA4C0871', '1609966470237',
    'Bulevar Partizanski Odredi', 1, 1
);

Процедура 2: dodadi_stavka_zapisnik

Додава нов прекршок (ставка) на веќе постоечки записник и сам го одредува следниот реден број. Не дозволува додавање ако записникот е веќе платен.

CREATE OR REPLACE PROCEDURE dodadi_stavka_zapisnik(
    p_id_zapisnik int,
    p_id_prekrsok int
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_reden_broj     int;
    v_status_uplata  varchar(30);
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Zapisnik WHERE id_na_zapisnik = p_id_zapisnik) THEN
        RAISE EXCEPTION 'Zapisnikot so id % ne postoi!', p_id_zapisnik;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM Prekrsok WHERE id_prekrsok = p_id_prekrsok) THEN
        RAISE EXCEPTION 'Prekrsokot so id % ne postoi!', p_id_prekrsok;
    END IF;

    SELECT status INTO v_status_uplata
    FROM Uplata WHERE id_zapisnik = p_id_zapisnik;

    IF v_status_uplata = 'Plateno' THEN
        RAISE EXCEPTION 'Zapisnik % e vekje platen, ne moze da se dodavaat novi stavki!', p_id_zapisnik;
    END IF;

    SELECT COALESCE(MAX(reden_broj), 0) + 1 INTO v_reden_broj
    FROM Stavka_Zapisnik WHERE id_na_zapisnik = p_id_zapisnik;

    INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok)
    VALUES (v_reden_broj, p_id_zapisnik, p_id_prekrsok);

    RAISE NOTICE 'Dodadena stavka so reden_broj % vo zapisnik %.', v_reden_broj, p_id_zapisnik;
END;
$$;

Повик:

CALL dodadi_stavka_zapisnik(30000005, 3);

Процедура 3: plati_kazna

Ја плаќа казната за даден записник — го менува статусот во Plateno, го запишува датумот и начинот на плаќање. Проверува дали уплатата постои, дали веќе е платена, дали има износ за наплата и дали начинот на плаќање е валиден.

CREATE OR REPLACE PROCEDURE plati_kazna(
    p_id_zapisnik    int,
    p_nacin_plakanje varchar(30)
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_status varchar(30);
    v_iznos  numeric(10,2);
BEGIN
    SELECT status, iznos INTO v_status, v_iznos
    FROM Uplata WHERE id_zapisnik = p_id_zapisnik;

    IF v_status IS NULL THEN
        RAISE EXCEPTION 'Ne postoi uplata za zapisnik %!', p_id_zapisnik;
    END IF;
    IF v_status = 'Plateno' THEN
        RAISE EXCEPTION 'Kaznata za zapisnik % e vekje platena!', p_id_zapisnik;
    END IF;
    IF v_iznos <= 0 THEN
        RAISE EXCEPTION 'Zapisnikot % nema stavki/kazni za naplata!', p_id_zapisnik;
    END IF;
    IF p_nacin_plakanje NOT IN ('E-bankarstvo', 'Platezna karticka', 'Gotovo/Uplatnica') THEN
        RAISE EXCEPTION 'Nepoznat nacin na plakanje: %.', p_nacin_plakanje;
    END IF;

    UPDATE Uplata
    SET status         = 'Plateno',
        datum_uplata   = CURRENT_DATE,
        nacin_plakanje = p_nacin_plakanje
    WHERE id_zapisnik = p_id_zapisnik;

    RAISE NOTICE 'Uspesno naplatena kazna od % za zapisnik % (%).', v_iznos, p_id_zapisnik, p_nacin_plakanje;
END;
$$;

Повик:

CALL plati_kazna(30000005, 'E-bankarstvo');

Процедура 4: dodadi_kategorija_na_vozacka

Кога возач полага дополнителна категорија (на пр. има B, полага C), ја додава на возачката. Проверува дали дозволата постои и не е истечена, дали категоријата постои и не е веќе додадена, и дали датумот на полагање е валиден.

CREATE OR REPLACE PROCEDURE dodadi_kategorija_na_vozacka(
    p_broj_dozvola    varchar(30),
    p_id_kategorija   int,
    p_datum_polaganje date DEFAULT CURRENT_DATE
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_datum_vaznost_do date;
    v_datum_izdavanje  date;
    v_kod_kategorija   varchar(3);
BEGIN
    SELECT datum_vaznost_do, datum_izdavanje
    INTO v_datum_vaznost_do, v_datum_izdavanje
    FROM Vozacka_dozvola WHERE broj_dozvola = p_broj_dozvola;

    IF v_datum_vaznost_do IS NULL THEN
        RAISE EXCEPTION 'Vozackata dozvola so broj % ne postoi!', p_broj_dozvola;
    END IF;
    IF v_datum_vaznost_do < CURRENT_DATE THEN
        RAISE EXCEPTION 'Vozackata dozvola % e istechena (vaznost do %)!', p_broj_dozvola, v_datum_vaznost_do;
    END IF;

    SELECT kod INTO v_kod_kategorija
    FROM Kategorija WHERE id_kategorija = p_id_kategorija;

    IF v_kod_kategorija IS NULL THEN
        RAISE EXCEPTION 'Kategorijata so id % ne postoi!', p_id_kategorija;
    END IF;

    IF EXISTS (
        SELECT 1 FROM Kategorija_Vozacka_dozvola
        WHERE broj_dozvola = p_broj_dozvola AND id_kategorija = p_id_kategorija
    ) THEN
        RAISE EXCEPTION 'Kategorijata % vekje e dodadena na vozackata %!', v_kod_kategorija, p_broj_dozvola;
    END IF;

    IF p_datum_polaganje > CURRENT_DATE THEN
        RAISE EXCEPTION 'Datumot na polaganje % ne moze da bide vo idnina!', p_datum_polaganje;
    END IF;
    IF p_datum_polaganje < v_datum_izdavanje THEN
        RAISE EXCEPTION 'Datumot na polaganje % ne moze da bide pred izdavanjeto na vozackata (%).',
            p_datum_polaganje, v_datum_izdavanje;
    END IF;

    INSERT INTO Kategorija_Vozacka_dozvola (id_kategorija, broj_dozvola, datum_polaganje)
    VALUES (p_id_kategorija, p_broj_dozvola, p_datum_polaganje);

    RAISE NOTICE 'Uspesno dodadena kategorija % na vozackata %.', v_kod_kategorija, p_broj_dozvola;
END;
$$;

Повик:

CALL dodadi_kategorija_na_vozacka('L8207294', 7);
CALL dodadi_kategorija_na_vozacka('L8207294', 9, '2025-11-15');

3. Дополнителна функција: azuriraj_kazna_plakanja

Функција што ги обработува задоцнетите неплатени казни: на неплатените постари од 8 дена им го зголемува износот за 50% (само ако сè уште не се зголемени), а на тие постари од 2 месеци им го менува статусот во Sudska_postapka. Враќа порака дека постапката е успешно извршена.

Прво ја прошируваме табелата Uplata со новиот статус и со колона за обележување дали казната е веќе зголемена:

ALTER TABLE Uplata DROP CONSTRAINT IF EXISTS uplata_status_check;
ALTER TABLE Uplata ADD CONSTRAINT uplata_status_check
    CHECK (status IN ('Plateno', 'Neplateno', 'Sudska_postapka'));

ALTER TABLE Uplata ADD COLUMN IF NOT EXISTS kazna_zgolemena boolean DEFAULT false;

CREATE OR REPLACE FUNCTION azuriraj_kazna_plakanja()
    RETURNS text
    LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE Uplata u
    SET iznos = u.iznos + (u.iznos * 0.50),
        kazna_zgolemena = true
    FROM Zapisnik z
    WHERE u.id_zapisnik = z.id_na_zapisnik
      AND u.status = 'Neplateno'
      AND u.kazna_zgolemena = false
      AND z.datum < CURRENT_DATE - INTERVAL '8 days';

    UPDATE Uplata u
    SET status = 'Sudska_postapka'
    FROM Zapisnik z
    WHERE u.id_zapisnik = z.id_na_zapisnik
      AND u.status = 'Neplateno'
      AND z.datum < CURRENT_DATE - INTERVAL '2 months';

    RETURN 'Постапката за автоматско ажурирање на неплатените казни е успешно извршена.';
END;
$$;

Повик:

SELECT azuriraj_kazna_plakanja();
SELECT status, COUNT(*) FROM Uplata GROUP BY status;

Оваа функција е наменета да се извршува автоматски како закажана (scheduled) задача што периодично ги обработува задоцнетите неплатени казни — ги зголемува застарените и најстарите ги префрла во судска постапка без рачна интервенција.