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

== За секое продажно место, да се најдат пакетите кои се најчесто продавани, од кои продукти се состои пакетот, вкупна количина на продадени пакети, како и името на вработениот кој го додал пакетот ==
{{{#!sql
create view najdobri_paketi_za_sekoe_prodazno_mesto as select pr.ime, pa.paket_id, string_agg(distinct h.ime_hra, ', ') as se_sostoi_od, sum(nip.kolicina) vkupno_prodadeni,
(
	select k.ime 
	from korisnik k join vrabotenpd v on k.korisnik_id = v.korisnik_id 
	join paket paa on paa.vraboten_id = v.korisnik_id 
	where paa.paket_id = pa.paket_id 
) as ime_vraboten
from prodazhnomesto pr join prodazhnomesto_nudi_paket pnp on pr.id_mesto = pnp.id_mesto 
join paket pa on pa.paket_id = pnp.paket_id 
join paket_sodrzi_hrana psh  on psh.paket_id = pa.paket_id 
join hrana h on h.id_stavka = psh.id_stavka
join naracka_ima_paket nip on nip.paket_id = pa.paket_id 
group by pr.id_mesto, pa.paket_id
having sum(nip.kolicina)
= (
select sum(nipp.kolicina) 
from prodazhnomesto prr join prodazhnomesto_nudi_paket pnpp on prr.id_mesto = pnpp.id_mesto 
join paket paa on paa.paket_id = pnpp.paket_id 
join paket_sodrzi_hrana pshh  on pshh.paket_id = paa.paket_id 
join hrana hh on hh.id_stavka = pshh.id_stavka
join naracka_ima_paket nipp on nipp.paket_id = paa.paket_id 
group by prr.id_mesto, paa.paket_id
order by sum(nipp.kolicina) 
limit 1
);
}}}

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

{{{#!sql

	create view najveren_potrosuvac_za_sekoe_prodazno_mesto as select k.ime as  ime_potrosuvac, pr.ime as ime_mesto, l.adresa as adresa_potrosuvac, l.broj as broj_adresa_potrosuvac,  count(distinct n.naracka_id) as broj_naracka,
extract(year from n.datum_naracka) as godina

from korisnik k join potrosuvac p on k.korisnik_id = p.korisnik_id 
join naracka n on n.potrosuvac_id = p.korisnik_id 
right join prodazhnomesto pr on pr.id_mesto = n.id_mesto 
left join lokacija l on l.lokacija_id = p.lokacija_id 
group by k.korisnik_id, pr.id_mesto, l.adresa, l.broj, extract(year from n.datum_naracka) 
having extract(year from n.datum_naracka) = extract(year from now()) and
 count(distinct n.naracka_id)
= 
		(select  count(distinct nn.naracka_id)  
		from korisnik kk join potrosuvac pp on kk.korisnik_id = pp.korisnik_id 
		join naracka nn on nn.potrosuvac_id = pp.korisnik_id 
		right join prodazhnomesto prr on prr.id_mesto = nn.id_mesto 
		left join lokacija ll on ll.lokacija_id = pp.lokacija_id 
		where pr.id_mesto = prr.id_mesto 
		group by kk.korisnik_id, prr.id_mesto, ll.adresa, ll.broj, extract(year from nn.datum_naracka) 
		having extract( year from nn.datum_naracka) = extract(year from now())
		order by count(distinct nn.naracka_id) desc
		limit 1
		
		);	
}}}

== За секој доставувач да се прикаже колку нарачки има доставено за секое продажно место, вкупен износ на нарачките и да се прикажат сортитани според вкупна сума во опаѓачки редослед ==

{{{#!sql
create view dostavuvac_dostavi_za_sekoe_prod_mesto as 
	select k.ime,p.ime as ime_mesto,count(distinct n2.naracka_id) as broj_naracki,sum(n.iznos) as vkupna_suma 
	from korisnik k  join dostavuvac  d 
	on k.korisnik_id = d.korisnik_id
	left join naplata n on n.dostavuvac_id  = d.korisnik_id 
	left join naracka n2 on n2.naplata_id = n.naplata_id 
	left join prodazhnomesto p on p.id_mesto = n2.id_mesto 
	group by k.korisnik_id, p.id_mesto 
	order by sum(n.iznos) desc

}}}


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

{{{#!sql
create view potrosuvaci_vkupen_iznos_kuponi as select ime, sum(ku.iznos_kupon) as vkupen_iznos
 from korisnik k join potrosuvac p on p.korisnik_id = k.korisnik_id 
 join kupon ku on ku.potrosuvac_id = p.korisnik_id 
group by k.korisnik_id; 

}}}

== За секоја франшиза да се прикаже колку промет има направено секое продажно место, во последните три месеци ==
{{{#!sql

create view franshiza_promet_mesta_tri_meseci as select f.ime as ime_franshiza, p.ime as ime_prodaznomesto, sum(n2.iznos) as vkupen_promet
from franshiza f join prodazhnomesto p on f.franshiza_id = p.franshiza_id 
join naracka n on n.id_mesto  = p.id_mesto 
join naplata n2 on n2.naplata_id = n.naplata_id 
where n2.datum_naplata between now() - interval '3 months' and now()
group by f.franshiza_id, p.id_mesto 


}}}

== Да се најдат храните кои се продаваат најмногу, во секое од продажните места за дадени три календарски месеци во годината ==
{{{#!sql
create function najprodavano_za_dadeni_meseci(month_1 int, month_2 int, month_3 int)
returns table(
	id_stavka int,
	ime_hra varchar(200),
	id_mesto int,
	ime_stavka varchar(200),
	vkupno_prodadeni bigint
)
language plpgsql
as $$
begin	
	return query select h.id_stavka, h.ime_hra,pr.id_mesto, pr.ime as ime_stavka, sum(nsh.kolicina) as vkupno_prodadeni
	from naracka n join naracka_sodrzi_hrana nsh on n.naracka_id = nsh.naracka_id 
	join hrana h on nsh.id_stavka = h.id_stavka 
	join prodazhnomesto pr on pr.id_mesto = n.id_mesto 
	where (lower(n.status) like '%zavrsena%' or lower(n.status) like '%завршена%')
	and (extract (month from n.datum_naracka) in (month_1,month_2,month_3) and extract(year from now()) = extract (year from n.datum_naracka) )
	group by h.id_stavka, pr.id_mesto,h.ime_hra, pr.ime 
	having sum(nsh.kolicina) = 
 (
		select  sum(nshh.kolicina)
		from naracka nn join naracka_sodrzi_hrana nshh on nn.naracka_id = nshh.naracka_id 
		join hrana hh on nshh.id_stavka = hh.id_stavka 
		join prodazhnomesto prr on prr.id_mesto = nn.id_mesto 
		where (lower(nn.status) like '%zavrsena%' or lower(nn.status) like '%завршена%')
		and (extract (month from nn.datum_naracka) in (month_1,month_2,month_3) and extract(year from now()) = extract (year from nn.datum_naracka) )
		group by hh.id_stavka, prr.id_mesto,hh.ime_hra, prr.ime 
		having prr.id_mesto = pr.id_mesto
		order by sum(nshh.kolicina) desc 
		limit 1
);
end
$$
}}}

== Функција за пресметување на вкупен износ на нарачка ==

{{{#!sql
drop function calculate_cost_of_order_food(order_id int);


create  function calculate_cost_of_order_food(order_id int)
returns table(
	vkupno_hrana int
)
language plpgsql
as 
$$
begin 
	return query select kolicina * iznos as vkupno
	from naracka n join naracka_sodrzi_hrana nih on n.naracka_id = nih.naracka_id
	join hrana h on h.id_stavka = nih.id_stavka 
	join cena c on (c.id_stavka = h.id_stavka and vazi_do is null)
	or (c.id_stavka = h.id_stavka and vazi_do > now())
	where n.naracka_id = order_id;
	
	
	
end 
$$

drop function calculate_cost_of_order_packets(order_id int);

create function calculate_cost_of_order_packets(order_id int)
returns table (
	vkupno_paketi bigint
)
language plpgsql
as 
$$
begin
	return query select sum(iznos) * nip.kolicina  as vkupno_paketi
	from naracka n join naracka_ima_paket nip on n.naracka_id = nip.naracka_id 
	join paket pa on pa.paket_id  = nip.paket_id 
	join paket_sodrzi_hrana psh on psh.paket_id = pa.paket_id 
	join hrana h on h.id_stavka = psh.id_stavka 
	join cena c on (c.id_stavka = h.id_stavka and vazi_do is null)
		or (c.id_stavka = h.id_stavka and vazi_do > now())
	where n.naracka_id = order_id
	group by n.naracka_id, pa.paket_id, nip.kolicina ; 
end
$$


drop function calculate_order_cost(orider_id int);

create function calculate_order_cost(order_id int)
returns bigint
language plpgsql
as 
$$
declare 
vkupno_cena_hrana bigint;
vkupno_cena_paketi bigint;
begin
	select sum(vkupno_hrana) into vkupno_cena_hrana from calculate_cost_of_order_food(order_id);
	select sum(vkupno_paketi) into vkupno_cena_paketi from calculate_cost_of_order_packets(order_id);
	return vkupno_cena_hrana + vkupno_cena_paketi;
	
end
$$

}}}