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

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

-- kompanija | pochnuva_vo | zavrshuva_vo | vo_godina | broj_zavrsheni_rezervacii

select k.kompanija_ime, g_pocnuva.grad_ime as pocnuva_vo, g_zavrshuva.grad_ime as zavrshuva_vo, 
	extract(year from r.datum_pristignuvanje) as vo_godina,
	COUNT(rezervacii.rezervacija_id) as broj_zavrsheni_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_zavrshuva on r.zavrsuva_vo = g_zavrshuva.grad_id 
	join rezervacii rezervacii on r.ruta_id = rezervacii.ruta_id 
where lower(rezervacii.rezervacija_status)  like 'zavrshena' 
group by 1, 2, 3, 4

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

-- kompanija | broj_vraboteni | vkupna_zarabotka | vkupno_klienti | vkupno_rezervacii | aktivni_rezervacii | zavrsheni_rezervacii | otkazhani_rezervacii | za_odobruvanje_rezervacii

select 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 'aktivna'
) 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 'zavrshena'
) as vkupno_zavrsheni_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 'otkazhana'
) as vkupno_otkazhani_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 'za odobruvanje'
) as vkupno_za_odobruvanje_rezervacii

from kompanii k;

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

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

select k.kompanija_ime, 
	klient.klient_ime,
	klient.klient_prezime ,
	SUM(f.faktura_iznos) as suma_fakturi
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 | ruta_pocnuva_vo | ruta_zavrshuva_vo | vkupna_zarabotka | broj_rezervacii
-- подредени според вкупна заработка во опаѓачки редослед

select k.kompanija_ime, 
	g_pocnuva.grad_ime as ruta_pocnuva_vo,
	g_zavrsuva.grad_ime as ruta_zavrshuva_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 '30 day' and now() and
	r.datum_pristignuvanje between now()-interval '30 day' and now() and 
	(lower(rezervacii.rezervacija_status) like 'aktivna' or lower(rezervacii.rezervacija_status) like 'zavrshena')
group by 1, 2, 3
order by vkupna_zarabotka desc;