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

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

[[Image(faza4.sql)]]

== Функции ==

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

=== 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. Ако нема содржина, тригерот фрла грешка и не дозволува внесување на невалидна жалба.

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