= Напредни извештаи од базата (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  
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  
having 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  
		order by count(distinct nn.naracka_id) desc
		limit 1
		
		);

}}}

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

{{{#!sql
create view najdobar_dostavuvac as select k.ime, (
	select count(distinct naplata_id) from naplata nn
	where nn.dostavuvac_id = k.korisnik_id 
) as broj_naplati,
(
	select sum(iznos) from naplata nn
	where nn.dostavuvac_id = k.korisnik_id 
) as vkupna_suma,
(
	select avg(iznos) from naplata nn
	where nn.dostavuvac_id = k.korisnik_id 
) as prosecna_suma,
(
	select kk.ime
	from korisnik kk join admin_table at2 on kk.korisnik_id = at2.korisnik_id 
	join dostavuvac d2 on d2.admin_id = at2.korisnik_id 
	where d2.korisnik_id = k.korisnik_id 
) as admin_koj_go_dodal
from korisnik k join dostavuvac d on k.korisnik_id  = d.korisnik_id 
join naracka n on n.dostavuvac_id = d.korisnik_id 
join naplata nap on nap.naplata_id  = n.naplata_id
group by k.korisnik_id 
having count(distinct n.naplata_id) = 
(
	select count(naplata_id)
	from naplata n 
	group by dostavuvac_id
	order by count(naplata_id) desc
	limit 1
);

}}}


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

{{{#!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 najprometno_prodazno_mesto as select pr.ime, sum(iznos) as promet
from prodazhnomesto pr  join naracka n on pr.id_mesto = n.id_mesto 
join naplata nap on nap.naplata_id = n.naplata_id 
and nap.datum_naplata between now () - interval '3 months' and now()
group by pr.id_mesto
having sum(iznos)
= (
	select sum(iznos) 
	from prodazhnomesto pr  join naracka n on pr.id_mesto = n.id_mesto 
	 join naplata nap on nap.naplata_id = n.naplata_id 
	and nap.datum_naplata between now () - interval '3 months' and now()
	group by pr.id_mesto
	order by sum(iznos) desc
	limit 1
);

}}}

== Да се најдат храните кои се продаваат најмногу за дадени три календарски месеци во годината ==
{{{#!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
$$

}}}