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


== Број на извршени прегледи од секој доктор, број на издадени упати од секој доктор и број на закажани термини кај секој доктор, за секое тримесечје, за тековната година

{{{#!sql

select distinct d.doktor_id, c.ime, c.prezime, b.naziv as bolnica, o.naziv as oddel, b.grad,
	case when pregledi_od_doktor.trimesecje = 1 then pregledi_od_doktor.pregledi else 0 end as quarter_1_pregledi,
	case when pregledi_od_doktor.trimesecje = 2 then pregledi_od_doktor.pregledi else 0 end as quarter_2_pregledi,
	case when pregledi_od_doktor.trimesecje = 3 then pregledi_od_doktor.pregledi else 0 end as quarter_3_pregledi,
	case when pregledi_od_doktor.trimesecje = 4 then pregledi_od_doktor.pregledi else 0 end as quarter_4_pregledi,
	
	case when upati_od_doktor.trimesecje = 1 then upati_od_doktor.upati else 0 end as quarter_1_upati_od_pregled,
	case when upati_od_doktor.trimesecje = 2 then upati_od_doktor.upati else 0 end as quarter_2_upati_od_pregled,
	case when upati_od_doktor.trimesecje = 3 then upati_od_doktor.upati else 0 end as quarter_3_upati_od_pregled,
	case when upati_od_doktor.trimesecje = 4 then upati_od_doktor.upati else 0 end as quarter_4_upati_od_pregled,
	
	case when zakazani_termini_kaj_doktor.trimesecje = 1 then zakazani_termini_kaj_doktor.rezervacii else 0 end as quarter_1_zakazani_termini,
	case when zakazani_termini_kaj_doktor.trimesecje = 2 then zakazani_termini_kaj_doktor.rezervacii else 0 end as quarter_2_zakazani_termini,
	case when zakazani_termini_kaj_doktor.trimesecje = 3 then zakazani_termini_kaj_doktor.rezervacii else 0 end as quarter_3_zakazani_termini,
	case when zakazani_termini_kaj_doktor.trimesecje = 4 then zakazani_termini_kaj_doktor.rezervacii else 0 end as quarter_4_zakazani_termini
from doktor d 
left join pregled p on d.doktor_id = p.doktor_id 
left join termin t on d.doktor_id = t.doktor_id 
join covek c on d.doktor_id = c.covek_id 
join oddel o on (d.oddel_id, d.bolnica_id) = (o.oddel_id, o.bolnica_id) 
join bolnica b on o.bolnica_id = b.bolnica_id 
left join (
	select extract (quarter from p2.vreme) as trimesecje, d3.doktor_id, count(p2.pregled_id) as pregledi
	from doktor d3 
	left join pregled p2 on d3.doktor_id = p2.doktor_id 
	where extract (year from p2.vreme) = extract (year from current_date)
	group by 1, 2
) pregledi_od_doktor on d.doktor_id = pregledi_od_doktor.doktor_id
left join (
	select d3.doktor_id, extract (quarter from p2.vreme) as trimesecje, count(u.upat_id) as upati
	from doktor d3 
	left join pregled p2 on d3.doktor_id = p2.doktor_id 
	left join upat u on p2.pregled_id = u.pregled_id 
	where extract (year from p2.vreme) = extract (year from current_date)
	group by 1, 2
) upati_od_doktor on d.doktor_id = upati_od_doktor.doktor_id
left join (
	select t3.doktor_id, extract (quarter from t3.vreme) as trimesecje, count(r.rezervacija_id) as rezervacii
	from upat u 
	left join rezervacija r on u.upat_id = r.upat_id 
	left join termin t3 on (r.termin_id, r.doktor_id) = (t3.termin_id, t3.doktor_id) 
	where extract (year from t3.vreme) = extract (year from current_date)
	group by 1, 2
) zakazani_termini_kaj_doktor on d.doktor_id = zakazani_termini_kaj_doktor.doktor_id ;

}}}



== Број на пациенти кои биле на преглед во даден оддел и број на упати кои биле/се издадени за во оддел, за секое тримесечје, за тековната година

{{{#!sql
select o.naziv as oddel, b.naziv as bolnica,
	case when pacienti_quarter.trimesecje = 1 then pacienti_quarter.pacienti else 0 end as quarter_1_pacienti,
	case when pacienti_quarter.trimesecje = 2 then pacienti_quarter.pacienti else 0 end as quarter_2_pacienti,
	case when pacienti_quarter.trimesecje = 3 then pacienti_quarter.pacienti else 0 end as quarter_3_pacienti,
	case when pacienti_quarter.trimesecje = 4 then pacienti_quarter.pacienti else 0 end as quarter_4_pacienti,
	
	case when upati_quarter.trimesecje = 1 then upati_quarter.upati else 0 end as quarter_1_upati,
	case when upati_quarter.trimesecje = 2 then upati_quarter.upati else 0 end as quarter_2_upati,
	case when upati_quarter.trimesecje = 3 then upati_quarter.upati else 0 end as quarter_3_upati,
	case when upati_quarter.trimesecje = 4 then upati_quarter.upati else 0 end as quarter_4_upati
from oddel o 
join bolnica b on o.bolnica_id = b.bolnica_id 
left join (
	select o.oddel_id, o.bolnica_id, count (p.covek_pacient_id) as pacienti,
		extract (quarter from p.vreme) as trimesecje
	from pregled p 
	join doktor d on p.doktor_id = d.doktor_id 
	join oddel o on (d.oddel_id, d.bolnica_id) = (o.oddel_id, o.bolnica_id)
	join bolnica b on o.bolnica_id = b.bolnica_id 
	where extract (year from p.vreme) = extract (year from current_date)
	group by 1, 2, 4
) pacienti_quarter on (o.oddel_id, o.bolnica_id) = (pacienti_quarter.oddel_id, pacienti_quarter.bolnica_id)	
left join (
	select u.oddel_id, u.bolnica_id, count(u.upat_id) as upati,
		extract (quarter from t.vreme) as trimesecje
	from upat u 
	join rezervacija r on u.upat_id = r.upat_id 
	join termin t on (r.termin_id, r.doktor_id) = (t.termin_id, t.doktor_id)
	where extract (year from t.vreme) = extract (year from current_date) 
	group by 1, 2, 4
) upati_quarter on (o.oddel_id, o.bolnica_id) = (upati_quarter.oddel_id, upati_quarter.bolnica_id) ;
}}}



== Тренд на препишани лекови по тримесечја за тековната година

{{{#!sql
select distinct l.ime_lek,
	case when recepta_quarter.trimesecje = 1 then recepti_za_lekovi.recepti_za_lek else 0 end as quarter_1_recepti_za_lek,
	case when recepta_quarter.trimesecje = 2 then recepti_za_lekovi.recepti_za_lek else 0 end as quarter_2_recepti_za_lek,
	case when recepta_quarter.trimesecje = 3 then recepti_za_lekovi.recepti_za_lek else 0 end as quarter_3_recepti_za_lek,
	case when recepta_quarter.trimesecje = 4 then recepti_za_lekovi.recepti_za_lek else 0 end as quarter_4_recepti_za_lek
from recepta r
join lekovi l on r.lek_id = l.lek_id
join (
	select r.recepta_id, extract (quarter from r.datum_izdavanje) as trimesecje
	from recepta r 
) recepta_quarter on r.recepta_id = recepta_quarter.recepta_id
join (
	select r.lek_id, count(r.recepta_id) as recepti_za_lek
	from recepta r
	where extract (year from r.datum_izdavanje) = extract (year from current_date)
	group by 1
) recepti_za_lekovi on r.lek_id = recepti_za_lekovi.lek_id
order by 2 desc, 3 desc, 4 desc, 5 desc ;
}}}



== Број на закажани термини кај докторите кај кои има такви, во наредните 3 недели

{{{#!sql
select zafatenost.doktor_id, zafatenost.br_licenca, c.ime,
	c.prezime, zafatenost.zakazani_pregledi
from (
	select d.doktor_id, d.br_licenca, d.oddel_id, d.bolnica_id,
	count(r.rezervacija_id) as zakazani_pregledi
	from doktor d 
	join termin t on d.doktor_id = t.doktor_id
	join rezervacija r on (t.termin_id, t.doktor_id) = (r.termin_id, r.doktor_id)
	where t.vreme between now() and  t.vreme + interval '3 weeks'
	group by 1, 2, 3, 4
        order by 5 desc
) zafatenost
join covek c on zafatenost.doktor_id = c.covek_id ;
}}}



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

{{{#!sql
create view pacient_najmnogu_pregledi as 
select pacient_id ,covek.ime,covek.prezime, count (pregled.pregled_id) pacientot_bil_na_pregled
from pacient as p 
join pregled as pregled on pregled.covek_pacient_id=p.covek_pacient_id
join covek as covek on covek.covek_id=p.covek_pacient_id
group by p.pacient_id,covek.covek_id;

select pnp.ime, pnp.prezime ,pnp.pacient_id , pnp.pacientot_bil_na_pregled  as broj_pregledi
from pacient_najmnogu_pregledi as pnp 
where pnp.pacientot_bil_na_pregled=
(select max(pacientot_bil_na_pregled) from pacient_najmnogu_pregledi);
}}}



{{{#!comment
== Број на издадени упати за оддел

{{{#!sql  
create view broj_na_upati as 
select o.oddel_id ,o.naziv , count(u.oddel_id) as broj_upati
from oddel as o
join upat as u on u.oddel_id=o.oddel_id
group by o.naziv ,o.oddel_id

select  * from broj_na_upati - --пребарување за сите оддели.

select  * from broj_na_upati
where naziv='Kardiologija' - -- пребарување според назив на одделот.
}}}
}}}



{{{#!comment
== Број на издадени рецепти за пациентите

{{{#!sql 
create view broj_recepti as 
select covek_pacient_id ,c.ime ,c.prezime ,count(recepta_id) as broj_recepti
from recepta as r
join covek as c on c.covek_id=r.covek_pacient_id
group by recepta_id ,c.covek_id

select covek_pacient_id ,ime ,prezime ,broj_recepti
from broj_recepti as br -- за сите пациенти.

select covek_pacient_id ,ime ,prezime ,broj_recepti
from broj_recepti as br
where broj_recepti=(select max(broj_recepti) from broj_recepti) 
--пациент со најголем број на издадени рецепти.

select covek_pacient_id ,ime ,prezime ,broj_recepti
from broj_recepti as br
where covek_pacient_id=9 --може да се пребарува според пациент.
}}}
}}}



== Доктор кој извршил највеќе прегледи во минатата година

{{{#!sql
create view br_pregledi_za_sekoj_doktor as 
select p.doktor_id,d.br_licenca,  count(p.pregled_id) br_pregledi_od_doktor
from pregled p
join doktor d on p.doktor_id = d.doktor_id
where extract (year from p.vreme) = extract (year from now() - interval '1 year')
group by p.doktor_id, d.doktor_id ;

select br_p.doktor_id, br_p.br_licenca, c.ime, c.prezime, br_p.br_pregledi_od_doktor as broj_pregledi
from br_pregledi_za_sekoj_doktor br_p
join covek c on br_p.doktor_id = c.covek_id
where br_p.br_pregledi_od_doktor = (
     select max(br_pregledi_od_doktor) from br_pregledi_za_sekoj_doktor
                                   ) ;
}}}



== Просечна возраст на пациенти за во даден оддел во болница за претходната година

{{{#!sql
select oo.bolnica_id, bb.naziv as bolnica, oo.oddel_id, oo.naziv as oddel,
(
	select avg(distinct extract (year from age(((case when substring(c.embg, 5, 1) in ('9')
		then '1'|| substring(c.embg, 5, 3) else '2'|| substring(c.embg, 5, 3) end)
		|| '-' || substring(c.embg, 3, 2) || '-' || substring(c.embg, 1, 2))::date))) as godini
	from oddel o 
	join bolnica b on o.bolnica_id = b.bolnica_id
	join upat u on (o.bolnica_id, o.oddel_id) = (u.bolnica_id, u.oddel_id)
	join rezervacija r on u.upat_id = r.upat_id 
	join termin t on (r.doktor_id, r.termin_id) = (t.doktor_id, t.termin_id)
	join pacient p on u.covek_pacient_id = p.covek_pacient_id 
	join covek c on p.covek_pacient_id = c.covek_id 
	where extract (year from t.vreme) = extract (year from now() - interval '1 year') 
	group by o.bolnica_id, o.oddel_id
	having (o.bolnica_id, o.oddel_id) = (oo.bolnica_id, oo.oddel_id)
) prosek_godini_pacienti
from oddel oo 
join bolnica bb on oo.bolnica_id = bb.bolnica_id ;
}}}



== Број на доктори во секој оддел

{{{#!sql
select o.bolnica_id, b.naziv as bolnica, b.grad as bolnica_grad,
o.naziv as oddel, count (d.doktor_id) broj_doktori_vo_oddel
from oddel o 
join doktor d on (o.bolnica_id, o.oddel_id) = (d.bolnica_id, d.oddel_id)
join bolnica b on o.bolnica_id = b.bolnica_id 
group by (o.bolnica_id, o.oddel_id), b.naziv, b.grad ;
}}}