DatabaseCreation: views.sql

CREATE VIEW vw_neplateni_kazni_posledni_2_nedeli AS
SELECT k.kazna_id,
       k.datum,
       k.rok_na_plakanje,
       k.iznos_za_plakanje,
       p.prekrsok_id,
       p.opis,
       g.ime,
       g.prezime,
       v.registarska_oznaka
FROM Kazna k
         JOIN Prekrsok p ON p.kazna_id = k.kazna_id
         LEFT JOIN Prekrsok_Storitel ps ON p.prekrsok_id = ps.prekrsok_id
         LEFT JOIN Gragjanin g ON ps.storitel_embg = g.embg
         LEFT JOIN Prekrsok_Vozilo pv ON p.prekrsok_id = pv.prekrsok_id
         LEFT JOIN Vozilo v ON pv.vozilo_id = v.vozilo_id
         LEFT JOIN Plakanje pl ON pl.kazna_id = k.kazna_id
WHERE k.datum >= CURRENT_DATE - INTERVAL '14 days'
  AND pl.kazna_id IS NULL;

----zasekogash a ne za 2 nedeli i dole kaj sho e


CREATE VIEW vw_prekrsoci_denes AS
SELECT p.prekrsok_id,
       p.datum,
       p.vreme,
       p.opis,
       p.detektirana_brzina,
       z.ime     AS zona,
       z.dozvolena_brzina,
       v.registarska_oznaka,
       g.ime     AS ime_gragjanin,
       g.prezime AS prezime_gragjanin
FROM Prekrsok p
         LEFT JOIN Kamera k ON p.kamera_id = k.kamera_id
         LEFT JOIN Lokacija l ON k.lokacija_id = l.lokacija_id
         LEFT JOIN Zona z ON l.zona_id = z.zona_id
         LEFT JOIN Prekrsok_Vozilo pv ON p.prekrsok_id = pv.prekrsok_id
         LEFT JOIN Vozilo v ON pv.vozilo_id = v.vozilo_id
         LEFT JOIN Prekrsok_Storitel ps ON p.prekrsok_id = ps.prekrsok_id
         LEFT JOIN Gragjanin g ON ps.storitel_embg = g.embg
WHERE p.datum = CURRENT_DATE;



CREATE VIEW vw_sopstvenici_na_vozila AS
SELECT v.vozilo_id,
       v.registarska_oznaka,
       v.marka,
       v.model,
       g.ime,
       g.prezime,
       g.telefonski_broj
FROM Vozilo v
         JOIN Sopstvenik_Vozilo sv ON v.vozilo_id = sv.vozilo_id
         JOIN Gragjanin g ON sv.embg = g.embg;



CREATE VIEW vw_aktivni_zhalbi AS
SELECT z.zalba_id,
       z.datum_na_podnesuvanje,
       z.sodrzina,

       p.status AS status_prekrsok,

       g.ime,
       g.prezime
FROM Zalba z
         LEFT JOIN Korisnik k ON z.korisnik_id = k.korisnik_id
         LEFT JOIN Gragjanin g ON k.embg = g.embg
         LEFT JOIN Prekrsok p ON z.prekrsok_id = p.prekrsok_id;



CREATE VIEW vw_neaktivni_kameri AS
SELECT k.kamera_id,
       k.status,
       k.seriski_broj,
       l.ulica,
       z.ime AS zona
FROM Kamera k
         LEFT JOIN Lokacija l ON k.lokacija_id = l.lokacija_id
         LEFT JOIN Zona z ON l.zona_id = z.zona_id
WHERE k.status IN ('neaktivna', 'servis');



CREATE VIEW vw_prekrsoci_so_nadminata_brzina AS
SELECT p.prekrsok_id,
       p.detektirana_brzina,
       z.dozvolena_brzina,
       (p.detektirana_brzina - z.dozvolena_brzina) AS razlika,
       z.ime                                       AS zona
FROM Prekrsok p
         JOIN Kamera k ON p.kamera_id = k.kamera_id
         JOIN Lokacija l ON k.lokacija_id = l.lokacija_id
         JOIN Zona z ON l.zona_id = z.zona_id
WHERE p.detektirana_brzina > z.dozvolena_brzina;

-----prekrshocii i prichini





-- istecheni registracii
-- prekshoci bez kazna
-- vozila so anjmnogu prekrshoci i povtoreni prekshoci
-- izveshtai na koja kamera od koj tip na prekrshok

CREATE VIEW vw_istecheni_registracii AS
SELECT r.registracija_id,
       r.broj,
       r.mesto,
       r.datum,
       r.datum_istekuvanje,
       v.vozilo_id,
       v.registarska_oznaka,
       s.embg
FROM Registracija r
         JOIN Vozilo v ON r.Vozilovozilo_id = v.vozilo_id
         JOIN Sopstvenik s ON r.sopstvenikEmbg = s.embg
WHERE r.datum_istekuvanje < CURRENT_DATE;



CREATE VIEW vw_prekshoci_bez_kazna AS
SELECT p.prekrsok_id,
       p.datum,
       p.vreme,
       p.opis,
       p.detektirana_brzina,
       tk.ime AS tip_prekrsok
FROM Prekrsok p
         LEFT JOIN TipPrekrsok tk ON p.tip_prekrsok_id = tk.tip_prekrsok_id
WHERE p.kazna_id IS NULL;



CREATE VIEW vw_vozila_najmnogu_prekshoci AS
SELECT v.vozilo_id,
       v.registarska_oznaka,
       COUNT(pv.prekrsok_id) AS vkupno_prekshoci
FROM Vozilo v
         LEFT JOIN Prekrsok_Vozilo pv ON v.vozilo_id = pv.vozilo_id
GROUP BY v.vozilo_id, v.registarska_oznaka;



CREATE VIEW vw_povtoreni_prekshoci AS
SELECT tk.ime   AS tip_prekrsok,
       v.registarska_oznaka,
       COUNT(*) AS broj_povtoruvanja
FROM Prekrsok p
         JOIN TipPrekrsok tk ON p.tip_prekrsok_id = tk.tip_prekrsok_id
         JOIN Prekrsok_Vozilo pv ON p.prekrsok_id = pv.prekrsok_id
         JOIN Vozilo v ON pv.vozilo_id = v.vozilo_id
GROUP BY tk.ime, v.registarska_oznaka
HAVING COUNT(*) > 1;



CREATE VIEW vw_kamera_tip_prekshok AS
SELECT k.kamera_id,
       k.seriski_broj,
       tk.ime               AS tip_prekrsok,
       z.ime                AS zona,
       COUNT(p.prekrsok_id) AS broj_deteckii
FROM Kamera k
         LEFT JOIN Prekrsok p ON k.kamera_id = p.kamera_id
         LEFT JOIN TipPrekrsok tk ON p.tip_prekrsok_id = tk.tip_prekrsok_id
         LEFT JOIN Lokacija l ON k.lokacija_id = l.lokacija_id
         LEFT JOIN Zona z ON l.zona_id = z.zona_id
GROUP BY k.kamera_id, k.seriski_broj, tk.ime, z.ime;