Changes between Version 2 and Version 3 of DatabaseProgramming

Timestamp:

06/10/26 23:17:11 (8 hours ago)

Author:

231018

Comment:

--

DatabaseProgramming

@@ -17 +17 @@
 AS $$
 DECLARE
-    v_status INTEGER;
-BEGIN
-    SELECT status
+    v_status VARCHAR;
+BEGIN
+    SELECT sk.ime
     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;
+    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;
+
 }}}
 
@@ -54 +60 @@
     WHERE tip_prekrsok_id = p_tip_prekrsok_id;
 
-    RETURN v_iznos;
+    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;
 $$
@@ -73 +85 @@
 CREATE OR REPLACE PROCEDURE sp_evidentiraj_plakanje(
     p_kazna_id INTEGER,
-    p_metod INTEGER
+    p_metod_plakanje_id INTEGER
 )
 AS $$
-BEGIN
-    INSERT INTO Plakanje(metod, datum, kazna_id)
-    VALUES(p_metod, CURRENT_DATE, p_kazna_id);
+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;
 $$
@@ -96 +153 @@
     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
-    );
+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;
 $$
@@ -128 +222 @@
     p_zalba_id INTEGER,
     p_administrator_id INTEGER,
-    p_status VARCHAR
+    p_nov_status VARCHAR
 )
 AS $$
-BEGIN
-    UPDATE Zalba
-    SET administrator_id = p_administrator_id,
-        status = p_status
+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;
 $$
@@ -150 +277 @@
 CREATE OR REPLACE PROCEDURE sp_arhiviraj_stari_snimki()
 AS $$
-BEGIN
-    UPDATE Snimka
-    SET arhivirana = TRUE,
-        datum_arhiviranje = CURRENT_DATE
+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;
 $$
@@ -180 +321 @@
     v_kazna_id INTEGER;
     v_iznos INTEGER;
-BEGIN
+    v_status_kazna_id INTEGER;
+    v_status_prekrsok_id INTEGER;
+    v_postoi_tip INTEGER;
+    v_postoi_kamera 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;
+
+
+    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, iznos_za_plakanje)
-    VALUES(CURRENT_DATE, 0, v_iznos)
+
+    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,
+        status_prekrsok_id,
         datum,
         detektirana_brzina,
@@ -200 +410 @@
         p_opis,
         p_vreme,
-        0,
+        v_status_prekrsok_id,
         p_datum,
         p_detektirana_brzina,
@@ -231 +441 @@
     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;
 
@@ -251 +466 @@
 RETURNS TRIGGER
 AS $$
-BEGIN
-    UPDATE Kazna
-    SET status = 1
-    WHERE kazna_id = NEW.kazna_id;
+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;
@@ -272 +512 @@
 RETURNS TRIGGER
 AS $$
+DECLARE
+    v_status_prekrsok VARCHAR;
 BEGIN
     IF NEW.sodrzina IS NULL OR LENGTH(TRIM(NEW.sodrzina)) = 0 THEN
@@ -277 +519 @@
     END IF;
 
-    RETURN NEW;
+
+    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;
 $$