Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

06/12/26 20:27:50 (8 days ago)

Author:

231025

Comment:

--

DatabaseProgramming

@@ +1 @@
+= Фаза 4: Функции, процедури и тригери =
+
+Во оваа фаза се надоградуваме со функции и процедури што ќе помогнат дел од честите операции во базата — отворање записник, додавање прекршок, наплата на казна, додавање категорија на возачка — да се извршуваат преку еден повик, со сите потребни проверки веќе вградени во нив. Исто така искористивме и тригери за работите што треба да се случуваат автоматски, како пресметката на вкупниот износ во уплатата, без рачно ажурирање.
+
+
+[[BR]]
+== 1. Тригери ==
+
+=== Тригер: Автоматска пресметка на износот во Uplata ===
+
+Секој пат кога ќе се додаде, измени или избрише ставка во `Stavka_Zapisnik`, тригерот ја пресметува вкупната сума на сите казни за тој записник и ја запишува во соодветната уплата. Така износот во `Uplata` секогаш е точен и не мора рачно да го ажурираме.
+
+{{{#!sql
+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();
+}}}
+
+
+[[BR]]
+== 2. Процедури ==
+
+=== Процедура 1: kreiraj_zapisnik_so_prekrsok ===
+Отвора нов записник со прв прекршок и веднаш креира празна уплата со статус `Neplateno`. Износот го пополнува тригерот штом ќе се вметне првата ставка. Прво проверува дали постојат граѓанинот, полицаецот, возилото, прекршокот и случајот.
+
+{{{#!sql
+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;
+$$;
+}}}
+
+Повик:
+{{{#!sql
+CALL kreiraj_zapisnik_so_prekrsok(
+    '1508004480145', 'CE8C5F1D5AA4C0871', '1609966470237',
+    'Bulevar Partizanski Odredi', 1, 1
+);
+}}}
+
+
+=== Процедура 2: dodadi_stavka_zapisnik ===
+Додава нов прекршок (ставка) на веќе постоечки записник и сам го одредува следниот реден број. Не дозволува додавање ако записникот е веќе платен.
+
+{{{#!sql
+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;
+$$;
+}}}
+
+Повик:
+{{{#!sql
+CALL dodadi_stavka_zapisnik(30000005, 3);
+}}}
+
+
+=== Процедура 3: plati_kazna ===
+Ја плаќа казната за даден записник — го менува статусот во `Plateno`, го запишува датумот и начинот на плаќање. Проверува дали уплатата постои, дали веќе е платена, дали има износ за наплата и дали начинот на плаќање е валиден.
+
+{{{#!sql
+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;
+$$;
+}}}
+
+Повик:
+{{{#!sql
+CALL plati_kazna(30000005, 'E-bankarstvo');
+}}}
+
+
+=== Процедура 4: dodadi_kategorija_na_vozacka ===
+Кога возач полага дополнителна категорија (на пр. има B, полага C), ја додава на возачката. Проверува дали дозволата постои и не е истечена, дали категоријата постои и не е веќе додадена, и дали датумот на полагање е валиден.
+
+{{{#!sql
+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;
+$$;
+}}}
+
+Повик:
+{{{#!sql
+CALL dodadi_kategorija_na_vozacka('L8207294', 7);
+CALL dodadi_kategorija_na_vozacka('L8207294', 9, '2025-11-15');
+}}}
+
+
+[[BR]]
+== 3. Дополнителна функција: azuriraj_kazna_plakanja ==
+Функција што ги обработува задоцнетите уплати наеднаш: на неплатените постари од 2 недели им го зголемува износот за 50%, а тие постари од 2 месеци ги префрла во статус `Sudska_postapka`. Враќа колку уплати вкупно се променети.
+
+Прво ја прошируваме табелата `Uplata` со новиот статус и со колона за обележување дали казната е веќе зголемена:
+{{{#!sql
+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;
+}}}
+
+{{{#!sql
+CREATE OR REPLACE FUNCTION azuriraj_kazna_plakanja()
+    RETURNS int
+    LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_zgolemeni int := 0;
+    v_na_sud    int := 0;
+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 '2 weeks';
+    GET DIAGNOSTICS v_zgolemeni = ROW_COUNT;
+
+    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';
+    GET DIAGNOSTICS v_na_sud = ROW_COUNT;
+
+    RAISE NOTICE 'Zgolemena kazna za % uplati, prefrleni % na sudska postapka.', v_zgolemeni, v_na_sud;
+    RETURN v_zgolemeni + v_na_sud;
+END;
+$$;
+}}}
+
+Повик:
+{{{#!sql
+SELECT azuriraj_kazna_plakanja();
+SELECT status, COUNT(*) FROM Uplata GROUP BY status;
+}}}