Напредни извештаи од базата (SQL и складирани процедури)

За секоја компанија приказ на која релација колку завршени резервации има во секоја година

-- kompanija_id | kompanija_ime | pocnuva_vo_id | pochnuva_vo_ime | zavrshuva_vo_id | zavrshuva_vo_ime | vo_godina | broj_zavrsheni_rezervacii

select k.kompanija_id, k.kompanija_ime, g_pocnuva.grad_id as pocnuva_vo_id, g_pocnuva.grad_ime as pocnuva_vo, g_zavrsuva.grad_id as zavrsuva_vo_id, 
g_zavrsuva.grad_ime as zavrsuva_vo, extract(year from r.datum_pristignuvanje) as vo_godina, COUNT(rezervacii.rezervacija_id) as broj_zavrseni_rezervacii
from kompanii k 
	join vozila v on k.kompanija_id = v.kompanija_id 
	join ruti r on v.vozilo_id = r.vozilo_id 
	join gradovi g_pocnuva on r.zapocnuva_vo = g_pocnuva.grad_id 
	join gradovi g_zavrsuva on r.zavrsuva_vo = g_zavrsuva.grad_id 
	join rezervacii rezervacii on r.ruta_id = rezervacii.ruta_id 
where lower(rezervacii.rezervacija_status)  like 'завршена' 
group by 1, 2, 3, 4, 5, 6, 7
order by vo_godina desc;

Извештај за секоја компанија со број на вработени, вкупна заработувачка, вкупно клиенти, вкупно резервации за секој статус(активна, завршена, откажана, за одобрување)

-- kompanija | broj_vraboteni | vkupna_zarabotka | vkupno_klienti | vkupno_rezervacii | aktivni_rezervacii | zavrseni_rezervacii | otkazani_rezervacii | za_odobruvanje_rezervacii

select k.kompanija_id, k.kompanija_ime, 

( select count(distinct v.vraboten_id)
	from vraboteni v
	where v.kompanija_id = k.kompanija_id
) as broj_vraboteni,

coalesce (( select sum(f.faktura_iznos)
	from fakturi f
	join rezervacii r on f.rezervacija_id = r.rezervacija_id 
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id 
), 0) as vkupna_zarabotka,

( select count(distinct kl.klient_id)
	from klienti kl 
	join rezervacii r on kl.klient_id = r.klient_id 
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id 
) as vkupno_klienti,

( select count(r.rezervacija_id)
	from rezervacii r
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id 
) as vkupno_rezervacii,

( select count(r.rezervacija_id)
	from rezervacii r
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id and lower(r.rezervacija_status) like 'активна'
) as vkupno_aktivni_rezervacii,

( select count(r.rezervacija_id)
	from rezervacii r
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id and lower(r.rezervacija_status) like 'завршена'
) as vkupno_zavrseni_rezervacii,

( select count(r.rezervacija_id)
	from rezervacii r
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id and lower(r.rezervacija_status) like 'откажана'
) as vkupno_otkazani_rezervacii,

( select count(r.rezervacija_id)
	from rezervacii r
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id and lower(r.rezervacija_status) like 'за одобрување'
) as vkupno_za_odobruvanje_rezervacii

from kompanii k;

За секоја компанија, приказ на сите клиенти и вкупната сума која ја имаат потрошено во компанијата

-- kompanija_id | kompanija_ime | klient_id | klient_ime | klient_prezime | suma_fakturi | broj_na_rezervacii
-- подредени според компанија, па според сума на фактури на клиентот во опаѓачки редослед

select k.kompanija_id, k.kompanija_ime, klient.klient_id, klient.klient_ime, klient.klient_prezime , 
SUM(f.faktura_iznos) as suma_fakturi, COUNT( rezervacija.rezervacija_id ) as broj_na_rezervacii
from kompanii k 
	join vozila v on k.kompanija_id = v.kompanija_id 
	join ruti r on v.vozilo_id = r.vozilo_id 
	join rezervacii rezervacija on r.ruta_id = rezervacija.ruta_id 
	join klienti klient on rezervacija.klient_id = klient.klient_id
	join fakturi f on rezervacija.rezervacija_id = f.rezervacija_id 
group by k.kompanija_id, klient.klient_id
order by k.kompanija_id, suma_fakturi desc;

За секоја компанија, приказ на релација, приказ на број на активни или завршени резервации на таа релација и приказ на вкупна заработка направена од тие резервации, во последните 30 дена

-- kompanija_id | kompanija_ime | ruta_pocnuva_vo | ruta_zavrshuva_vo | vkupna_zarabotka | broj_rezervacii
-- подредени според вкупна заработка во опаѓачки редослед

select k.kompanija_id, k.kompanija_ime, g_pocnuva.grad_ime as ruta_pocnuva_vo, g_zavrsuva.grad_ime as ruta_zavrsuva_vo, 
	SUM(f.faktura_iznos) as vkupna_zarabotka, COUNT(rezervacii.rezervacija_id) as broj_rezervacii
from kompanii k 
	join vozila v on k.kompanija_id = v.kompanija_id 
	join ruti r on v.vozilo_id = r.vozilo_id 
	join gradovi g_pocnuva on r.zapocnuva_vo = g_pocnuva.grad_id 
	join gradovi g_zavrsuva on r.zavrsuva_vo = g_zavrsuva.grad_id 
	join rezervacii rezervacii on r.ruta_id = rezervacii.ruta_id 
	join fakturi f on rezervacii.rezervacija_id = f.rezervacija_id 
where 
	r.datum_poagjanje between now()-interval '1 month' and now() and
	r.datum_pristignuvanje between now()-interval '1 month' and now() and 
	(lower(rezervacii.rezervacija_status) like 'активна' or lower(rezervacii.rezervacija_status) like 'завршена')
group by 1, 2, 3, 4
order by vkupna_zarabotka desc;