Функции, Процедури и Тригери

Во оваа фаза беа имплементирани функции, процедури и тригери кои ја поддржуваат основната бизнис логика на системот Safe City Security. Тие овозможуваат автоматизација на процеси како проверка на статус на казна, пресметка на износ, евидентирање плаќање, поднесување жалба, архивирање снимки и автоматско ажурирање на податоци преку тригери.

phase04.sql​

Функции

Функциите се користат за пресметување и враќање на вредности кои се потребни во повеќе делови од системот.

1. fn_status_kazna

CREATE OR REPLACE FUNCTION fn_status_kazna(p_kazna_id INTEGER)
RETURNS VARCHAR
AS $$
DECLARE
    v_status VARCHAR;
BEGIN
    SELECT sk.ime
    INTO v_status
    FROM Kazna k
    JOIN StatusKazna sk ON sk.status_kazna_id = k.status_kazna_id
    WHERE k.kazna_id = p_kazna_id;

    IF v_status IS NULL THEN
        RETURN 'Kaznata ne postoi';
    ELSIF LOWER(v_status) = 'platena' THEN
        RETURN 'Kaznata e platena';
    ELSIF LOWER(v_status) = 'neplatena' THEN
        RETURN 'Kaznata ne e platena';
    ELSIF LOWER(v_status) = 'ponishtena' THEN
        RETURN 'Kaznata e ponishtena';
    ELSE
        RETURN 'Nepoznat status na kazna';
    END IF;
END;
$$
LANGUAGE plpgsql;

Функцијата fn_status_kazna го враќа статусот на одредена казна според нејзиниот идентификатор. Како влезен параметар прима p_kazna_id, а потоа од табелата Kazna и поврзаната табела StatusKazna го зема името на статусот на казната. Ако казната не постои, функцијата враќа Kaznata ne postoi. Ако статусот е platena, враќа Kaznata e platena. Ако статусот е neplatena, враќа Kaznata ne e platena. Ако статусот е ponishtena, враќа Kaznata e ponishtena, а во останатите случаи враќа Nepoznat status na kazna.

Оваа функција е корисна кога системот треба брзо да прикаже дали одредена казна е платена, неплатена или поништена, без секогаш рачно да се проверува статусот преку табелите Kazna и StatusKazna.

2. fn_presmetaj_iznos_kazna

CREATE OR REPLACE FUNCTION fn_presmetaj_iznos_kazna(p_tip_prekrsok_id INTEGER)
RETURNS INTEGER
AS $$
DECLARE
    v_iznos INTEGER;
BEGIN
    SELECT iznos
    INTO v_iznos
    FROM TipPrekrsok
    WHERE tip_prekrsok_id = p_tip_prekrsok_id;

    IF v_iznos IS NULL THEN
        RAISE EXCEPTION 'Ne postoi tip na prekrsok so id %', p_tip_prekrsok_id;
    ELSIF v_iznos <= 0 THEN
        RAISE EXCEPTION 'Iznosot na kaznata mora da bide pogolem od 0';
    ELSE
        RETURN v_iznos;
    END IF;
END;
$$
LANGUAGE plpgsql;

Функцијата fn_presmetaj_iznos_kazna се користи за пресметување на износот на казната според типот на прекршок. Како параметар прима tip_prekrsok_id, а потоа од табелата TipPrekrsok го зема соодветниот износ.

Оваа функција се користи во процедурата за креирање прекршок со казна, каде што прво се пресметува износот, а потоа автоматски се креира запис во табелата Kazna.

Процедури

Процедурите се користат за извршување операции врз базата, како внесување, ажурирање и архивирање податоци.

1. sp_evidentiraj_plakanje

CREATE OR REPLACE PROCEDURE sp_evidentiraj_plakanje(
    p_kazna_id INTEGER,
    p_metod_plakanje_id INTEGER
)
AS $$
DECLARE
    v_postoi_kazna INTEGER;
    v_postoi_metod INTEGER;
    v_status VARCHAR;
BEGIN
    SELECT COUNT(*)
    INTO v_postoi_kazna
    FROM Kazna
    WHERE kazna_id = p_kazna_id;

    IF v_postoi_kazna = 0 THEN
        RAISE EXCEPTION 'Ne postoi kazna so id %', p_kazna_id;
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_metod
    FROM MetodPlakanje
    WHERE metod_plakanje_id = p_metod_plakanje_id;

    IF v_postoi_metod = 0 THEN
        RAISE EXCEPTION 'Ne postoi metod na plakanje so id %', p_metod_plakanje_id;
    END IF;


    SELECT sk.ime
    INTO v_status
    FROM Kazna k
    JOIN StatusKazna sk ON sk.status_kazna_id = k.status_kazna_id
    WHERE k.kazna_id = p_kazna_id;


    IF LOWER(v_status) = 'platena' THEN
        RAISE EXCEPTION 'Kaznata e vekje platena';
    ELSIF LOWER(v_status) = 'ponishtena' THEN
        RAISE EXCEPTION 'Ponisthena kazna ne moze da se plati';
    ELSE
        INSERT INTO Plakanje(
            metod_plakanje_id,
            datum,
            kazna_id
        )
        VALUES(
            p_metod_plakanje_id,
            CURRENT_DATE,
            p_kazna_id
        );
    END IF;
END;
$$
LANGUAGE plpgsql;

Процедурата sp_evidentiraj_plakanje служи за евидентирање плаќање на казна. Како влезни параметри прима p_kazna_id, односно идентификатор на казната, и p_metod_plakanje_id, односно идентификатор на методот на плаќање. Најпрво процедурата проверува дали постои казна со дадениот идентификатор во табелата Kazna. Доколку не постои, се прикажува грешка. Потоа проверува дали постои метод на плаќање со дадениот идентификатор во табелата MetodPlakanje.

Со помош на IF / ELSIF / ELSE проверки, процедурата го зема моменталниот статус на казната преку табелата StatusKazna. Ако казната е веќе platena, се прикажува грешка дека казната е веќе платена. Ако казната е ponishtena, се прикажува грешка дека поништена казна не може да се плати. Во спротивно, ако казната може да се плати, се креира нов запис во табелата Plakanje, каде што се зачувуваат методот на плаќање, тековниот датум и идентификаторот на казната.

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

2. sp_podnesi_zalba

CREATE OR REPLACE PROCEDURE sp_podnesi_zalba(
    p_sodrzina VARCHAR,
    p_korisnik_id INTEGER,
    p_prekrsok_id INTEGER
)
AS $$
DECLARE
    v_status_podnesena INTEGER;
    v_postoi_korisnik INTEGER;
    v_postoi_prekrsok INTEGER;
BEGIN
    IF p_sodrzina IS NULL OR LENGTH(TRIM(p_sodrzina)) = 0 THEN
        RAISE EXCEPTION 'Zalbata mora da ima sodrzina';
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_korisnik
    FROM Korisnik
    WHERE korisnik_id = p_korisnik_id;

    IF v_postoi_korisnik = 0 THEN
        RAISE EXCEPTION 'Ne postoi korisnik so id %', p_korisnik_id;
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_prekrsok
    FROM Prekrsok
    WHERE prekrsok_id = p_prekrsok_id;

    IF v_postoi_prekrsok = 0 THEN
        RAISE EXCEPTION 'Ne postoi prekrsok so id %', p_prekrsok_id;
    END IF;


    SELECT status_zalba_id
    INTO v_status_podnesena
    FROM StatusZalba
    WHERE LOWER(ime) = 'podnesena';

    IF v_status_podnesena IS NULL THEN
        RAISE EXCEPTION 'Vo StatusZalba ne postoi status podnesena';
    ELSE
        INSERT INTO Zalba(
            sodrzina,
            datum_na_podnesuvanje,
            status_zalba_id,
            korisnik_id,
            prekrsok_id
        )
        VALUES(
            p_sodrzina,
            CURRENT_DATE,
            v_status_podnesena,
            p_korisnik_id,
            p_prekrsok_id
        );
    END IF;
END;
$$
LANGUAGE plpgsql;

Процедурата sp_podnesi_zalba се користи за поднесување жалба од страна на корисник за одреден прекршок. Како параметри прима p_sodrzina, p_korisnik_id и p_prekrsok_id.

Процедурата проверува дали жалбата има содржина, дали постои корисникот во табелата Korisnik и дали постои прекршокот во табелата Prekrsok. Потоа од табелата StatusZalba го зема статусот podnesena, кој се поставува како почетен статус на новата жалба.

Ако сите проверки се успешни, се креира нов запис во табелата Zalba, со содржина, тековен датум, статус, корисник и прекршок. Ова овозможува жалбата правилно да биде поврзана со конкретен корисник и конкретен прекршок.

3. sp_razgledaj_zalba

CREATE OR REPLACE PROCEDURE sp_razgledaj_zalba(
    p_zalba_id INTEGER,
    p_administrator_id INTEGER,
    p_nov_status VARCHAR
)
AS $$
DECLARE
    v_status_id INTEGER;
    v_postoi_zalba INTEGER;
    v_postoi_admin INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO v_postoi_zalba
    FROM Zalba
    WHERE zalba_id = p_zalba_id;

    IF v_postoi_zalba = 0 THEN
        RAISE EXCEPTION 'Ne postoi zalba so id %', p_zalba_id;
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_admin
    FROM Administrator
    WHERE administrator_id = p_administrator_id;

    IF v_postoi_admin = 0 THEN
        RAISE EXCEPTION 'Ne postoi administrator so id %', p_administrator_id;
    END IF;


    SELECT status_zalba_id
    INTO v_status_id
    FROM StatusZalba
    WHERE LOWER(ime) = LOWER(p_nov_status);

    IF v_status_id IS NULL THEN
        RAISE EXCEPTION 'Ne postoi status na zalba so ime %', p_nov_status;
    ELSE
        UPDATE Zalba
        SET administrator_id = p_administrator_id,
            status_zalba_id = v_status_id
        WHERE zalba_id = p_zalba_id;
    END IF;
END;
$$
LANGUAGE plpgsql;

Процедурата sp_razgledaj_zalba се користи кога администратор разгледува жалба. Таа го ажурира администраторот кој ја обработува жалбата и го менува нејзиниот статус.

Оваа процедура ја претставува логиката за управување со жалби во системот, односно премин од поднесена жалба кон понатамошна обработка.

4. sp_arhiviraj_stari_snimki

CREATE OR REPLACE PROCEDURE sp_arhiviraj_stari_snimki()
AS $$
DECLARE
    v_broj_snimki INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO v_broj_snimki
    FROM Snimka
    WHERE datum < CURRENT_DATE - INTERVAL '365 days'
      AND arhivirana = FALSE;

    IF v_broj_snimki = 0 THEN
        RAISE NOTICE 'Nema snimki za arhiviranje';
    ELSE
        UPDATE Snimka
        SET arhivirana = TRUE,
            datum_arhiviranje = CURRENT_DATE
        WHERE datum < CURRENT_DATE - INTERVAL '365 days'
          AND arhivirana = FALSE;

        RAISE NOTICE 'Arhivirani se % snimki', v_broj_snimki;
    END IF;
END;
$$
LANGUAGE plpgsql;

Процедурата sp_arhiviraj_stari_snimki ги архивира старите снимки кои се постари од една година. Таа ги ажурира записите во табелата Snimka, поставува arhivirana = TRUE и внесува датум на архивирање.

Оваа процедура е корисна за одржување на базата, бидејќи старите снимки не мора постојано да се третираат како активни податоци.

5. sp_kreiraj_prekrsok_so_kazna

CREATE OR REPLACE PROCEDURE sp_kreiraj_prekrsok_so_kazna(
    p_opis VARCHAR,
    p_vreme TIME,
    p_datum DATE,
    p_detektirana_brzina INTEGER,
    p_tip_prekrsok_id INTEGER,
    p_kamera_id INTEGER,
    p_storitel_embg VARCHAR
)
AS $$
DECLARE
    v_kazna_id INTEGER;
    v_prekrsok_id INTEGER;
    v_iznos INTEGER;
    v_status_kazna_id INTEGER;
    v_status_prekrsok_id INTEGER;
    v_postoi_tip INTEGER;
    v_postoi_kamera INTEGER;
    v_postoi_gragjanin INTEGER;
BEGIN
    IF p_opis IS NULL OR LENGTH(TRIM(p_opis)) = 0 THEN
        RAISE EXCEPTION 'Opisot na prekrsokot ne smee da bide prazen';
    END IF;


    IF p_datum IS NULL THEN
        p_datum := CURRENT_DATE;
    END IF;


    IF p_detektirana_brzina IS NULL OR p_detektirana_brzina <= 0 THEN
        RAISE EXCEPTION 'Detektiranata brzina mora da bide pogolema od 0';
    END IF;


    IF p_storitel_embg IS NULL OR LENGTH(TRIM(p_storitel_embg)) <> 13 THEN
        RAISE EXCEPTION 'EMBG na storitelot mora da ima 13 karakteri';
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_gragjanin
    FROM Gragjanin
    WHERE embg = p_storitel_embg;

    IF v_postoi_gragjanin = 0 THEN
        RAISE EXCEPTION 'Ne postoi gragjanin so EMBG %', p_storitel_embg;
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_tip
    FROM TipPrekrsok
    WHERE tip_prekrsok_id = p_tip_prekrsok_id;

    IF v_postoi_tip = 0 THEN
        RAISE EXCEPTION 'Ne postoi tip prekrsok so id %', p_tip_prekrsok_id;
    END IF;


    SELECT COUNT(*)
    INTO v_postoi_kamera
    FROM Kamera
    WHERE kamera_id = p_kamera_id;

    IF v_postoi_kamera = 0 THEN
        RAISE EXCEPTION 'Ne postoi kamera so id %', p_kamera_id;
    END IF;


    SELECT status_kazna_id
    INTO v_status_kazna_id
    FROM StatusKazna
    WHERE LOWER(ime) = 'neplatena';

    IF v_status_kazna_id IS NULL THEN
        RAISE EXCEPTION 'Vo StatusKazna ne postoi status neplatena';
    END IF;


    SELECT status_prekrsok_id
    INTO v_status_prekrsok_id
    FROM StatusPrekrsok
    WHERE LOWER(ime) = 'aktiven';

    IF v_status_prekrsok_id IS NULL THEN
        RAISE EXCEPTION 'Vo StatusPrekrsok ne postoi status aktiven';
    END IF;


    v_iznos := fn_presmetaj_iznos_kazna(p_tip_prekrsok_id);


    INSERT INTO Kazna(
        datum,
        status_kazna_id,
        iznos_za_plakanje
    )
    VALUES(
        CURRENT_DATE,
        v_status_kazna_id,
        v_iznos
    )
    RETURNING kazna_id INTO v_kazna_id;


    INSERT INTO Prekrsok(
        opis,
        vreme,
        status_prekrsok_id,
        datum,
        detektirana_brzina,
        tip_prekrsok_id,
        kamera_id,
        kazna_id
    )
    VALUES(
        p_opis,
        p_vreme,
        v_status_prekrsok_id,
        p_datum,
        p_detektirana_brzina,
        p_tip_prekrsok_id,
        p_kamera_id,
        v_kazna_id
    )
    RETURNING prekrsok_id INTO v_prekrsok_id;


    INSERT INTO Prekrsok_Storitel(
        prekrsok_id,
        storitel_embg
    )
    VALUES(
        v_prekrsok_id,
        p_storitel_embg
    );
END;
$$
LANGUAGE plpgsql;

Процедурата sp_kreiraj_prekrsok_so_kazna автоматски креира прекршок заедно со соодветна казна. Најпрво се повикува функцијата fn_presmetaj_iznos_kazna, со која се пресметува износот на казната според типот на прекршок. Потоа се внесува нов запис во табелата Kazna, а добиениот kazna_id се користи при внесување на нов прекршок во табелата Prekrsok.

Оваа процедура е важна бидејќи поврзува две операции во една целина: креирање казна и креирање прекршок.

Тригери

Тригерите се користат за автоматско извршување логика при внесување или промена на податоци во табелите.

1. trg_auto_rok_kazna

CREATE OR REPLACE FUNCTION trg_fn_auto_rok_kazna()
RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.datum IS NULL THEN
        NEW.datum := CURRENT_DATE;
    END IF;


    IF NEW.rok_na_plakanje IS NULL THEN
        NEW.rok_na_plakanje := NEW.datum + INTERVAL '30 days';
    ELSIF NEW.rok_na_plakanje < NEW.datum THEN
        RAISE EXCEPTION 'Rokot za plakanje ne moze da bide pred datumot na kaznata';
    ELSE
        RETURN NEW;
    END IF;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Тригерот trg_auto_rok_kazna се активира пред внесување нова казна во табелата Kazna. Ако не е внесен датум, автоматски се поставува тековниот датум. Ако не е внесен рок за плаќање, тој автоматски се пресметува како 30 дена по датумот на казната.

Со ова се избегнува рачно внесување на рок за секоја казна и се обезбедува конзистентност во системот.

2. trg_kazna_platena

CREATE OR REPLACE FUNCTION trg_fn_kazna_platena()
RETURNS TRIGGER
AS $$
DECLARE
    v_status_platena_id INTEGER;
    v_status_momentalen VARCHAR;
BEGIN
    SELECT sk.ime
    INTO v_status_momentalen
    FROM Kazna k
    JOIN StatusKazna sk ON sk.status_kazna_id = k.status_kazna_id
    WHERE k.kazna_id = NEW.kazna_id;


    IF v_status_momentalen IS NULL THEN
        RAISE EXCEPTION 'Kaznata ne postoi';
    ELSIF LOWER(v_status_momentalen) = 'ponishtena' THEN
        RAISE EXCEPTION 'Ponisthena kazna ne moze da se oznaci kako platena';
    ELSE
        SELECT status_kazna_id
        INTO v_status_platena_id
        FROM StatusKazna
        WHERE LOWER(ime) = 'platena';

        IF v_status_platena_id IS NULL THEN
            RAISE EXCEPTION 'Vo StatusKazna ne postoi status platena';
        ELSE
            UPDATE Kazna
            SET status_kazna_id = v_status_platena_id
            WHERE kazna_id = NEW.kazna_id;
        END IF;
    END IF;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Тригерот trg_kazna_platena се активира по внесување запис во табелата Plakanje. Кога ќе се евидентира плаќање, тригерот автоматски го ажурира статусот на соодветната казна во табелата Kazna и го поставува на 1, што означува дека казната е платена.

Ова овозможува автоматско поврзување меѓу плаќањето и статусот на казната.

3. trg_validacija_zalba

CREATE OR REPLACE FUNCTION trg_fn_validacija_zalba()
RETURNS TRIGGER
AS $$
DECLARE
    v_status_prekrsok VARCHAR;
BEGIN
    IF NEW.sodrzina IS NULL OR LENGTH(TRIM(NEW.sodrzina)) = 0 THEN
        RAISE EXCEPTION 'Zalbata mora da ima sodrzina';
    END IF;


    IF NEW.datum_na_podnesuvanje IS NULL THEN
        NEW.datum_na_podnesuvanje := CURRENT_DATE;
    END IF;


    SELECT sp.ime
    INTO v_status_prekrsok
    FROM Prekrsok p
    JOIN StatusPrekrsok sp ON sp.status_prekrsok_id = p.status_prekrsok_id
    WHERE p.prekrsok_id = NEW.prekrsok_id;


    IF v_status_prekrsok IS NULL THEN
        RAISE EXCEPTION 'Ne postoi prekrsok za koj se podnesuva zalbata';
    ELSIF LOWER(v_status_prekrsok) = 'ponishten' THEN
        RAISE EXCEPTION 'Ne moze da se podnese zalba za ponishten prekrsok';
    ELSE
        RETURN NEW;
    END IF;
END;
$$
LANGUAGE plpgsql;

Тригерот trg_validacija_zalba се активира пред внесување нова жалба во табелата Zalba. Тој проверува дали содржината на жалбата е празна или NULL. Ако нема содржина, тригерот фрла грешка и не дозволува внесување на невалидна жалба.

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