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

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

Функции

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

1. fn_status_kazna

CREATE OR REPLACE FUNCTION fn_status_kazna(p_kazna_id INTEGER)
RETURNS VARCHAR
AS $$
DECLARE
    v_status INTEGER;
BEGIN
    SELECT status
    INTO v_status
    FROM Kazna
    WHERE kazna_id = p_kazna_id;

    IF v_status = 1 THEN
        RETURN 'platena';
    ELSIF v_status = 0 THEN
        RETURN 'neplatena';
    ELSE
        RETURN 'ponishtena';
    END IF;
END;
$$
LANGUAGE plpgsql;

Функцијата fn_status_kazna го враќа статусот на одредена казна според нејзиниот идентификатор. Како влезен параметар прима kazna_id, а потоа од табелата Kazna го зема статусот на казната. Ако статусот е 1, функцијата враќа platena. Ако статусот е 0, враќа neplatena, а во останатите случаи враќа ponishtena.

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

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;

    RETURN v_iznos;
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 INTEGER
)
AS $$
BEGIN
    INSERT INTO Plakanje(metod, datum, kazna_id)
    VALUES(p_metod, CURRENT_DATE, p_kazna_id);
END;
$$
LANGUAGE plpgsql;

Процедурата sp_evidentiraj_plakanje служи за евидентирање плаќање на казна. Како параметри прима kazna_id и метод на плаќање. При повик на процедурата се внесува нов запис во табелата Plakanje, со тековен датум и поврзаната казна.

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

2. sp_podnesi_zalba

CREATE OR REPLACE PROCEDURE sp_podnesi_zalba(
    p_sodrzina VARCHAR,
    p_korisnik_id INTEGER,
    p_prekrsok_id INTEGER
)
AS
$$
BEGIN
    INSERT INTO Zalba(
        sodrzina,
        datum_na_podnesuvanje,
        status,
        korisnik_id,
        prekrsok_id
    )
    VALUES(
        p_sodrzina,
        CURRENT_DATE,
        'podnesena',
        p_korisnik_id,
        p_prekrsok_id
    );
END;
$$
LANGUAGE plpgsql;

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

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

3. sp_razgledaj_zalba

CREATE OR REPLACE PROCEDURE sp_razgledaj_zalba(
    p_zalba_id INTEGER,
    p_administrator_id INTEGER,
    p_status VARCHAR
)
AS $$
BEGIN
    UPDATE Zalba
    SET administrator_id = p_administrator_id,
        status = p_status
    WHERE zalba_id = p_zalba_id;
END;
$$
LANGUAGE plpgsql;

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

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

4. sp_arhiviraj_stari_snimki

CREATE OR REPLACE PROCEDURE sp_arhiviraj_stari_snimki()
AS $$
BEGIN
    UPDATE Snimka
    SET arhivirana = TRUE,
        datum_arhiviranje = CURRENT_DATE
    WHERE datum < CURRENT_DATE - INTERVAL '365 days'
      AND arhivirana = FALSE;
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
)
AS $$
DECLARE
    v_kazna_id INTEGER;
    v_iznos INTEGER;
BEGIN
    v_iznos := fn_presmetaj_iznos_kazna(p_tip_prekrsok_id);

    INSERT INTO Kazna(datum, status, iznos_za_plakanje)
    VALUES(CURRENT_DATE, 0, v_iznos)
    RETURNING kazna_id INTO v_kazna_id;

    INSERT INTO Prekrsok(
        opis,
        vreme,
        status,
        datum,
        detektirana_brzina,
        tip_prekrsok_id,
        kamera_id,
        kazna_id
    )
    VALUES(
        p_opis,
        p_vreme,
        0,
        p_datum,
        p_detektirana_brzina,
        p_tip_prekrsok_id,
        p_kamera_id,
        v_kazna_id
    );
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';
    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 $$
BEGIN
    UPDATE Kazna
    SET status = 1
    WHERE kazna_id = NEW.kazna_id;

    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 $$
BEGIN
    IF NEW.sodrzina IS NULL OR LENGTH(TRIM(NEW.sodrzina)) = 0 THEN
        RAISE EXCEPTION 'Zalbata mora da ima sodrzina';
    END IF;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

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

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