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

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

{{{#!sql

create view podatoci_klienti as 

SELECT DISTINCT u.user_name, u.surname,
CASE WHEN tabela.quarter=1 THEN tabela.kupeni_proizvodi ELSE 0 END AS prv_kvartal,
CASE WHEN tabela.quarter=2 THEN tabela.kupeni_proizvodi ELSE 0 END AS vtor_kvartal,
CASE WHEN tabela.quarter=3 THEN tabela.kupeni_proizvodi ELSE 0 END AS tret_kvartal,
CASE WHEN tabela.quarter=4 THEN tabela.kupeni_proizvodi ELSE 0 END AS cetvrt_kvartal,

CASE WHEN tabela.quarter=1 THEN tabela.suma ELSE 0 END AS prv_kvartal_suma,
CASE WHEN tabela.quarter=1 THEN tabela.suma ELSE 0 END AS vtor_kvartal_suma,
CASE WHEN tabela.quarter=1 THEN tabela.suma ELSE 0 END AS tret_kvartal_suma,
CASE WHEN tabela.quarter=1 THEN tabela.suma ELSE 0 END AS cetvrt_kvartal_suma

FROM shopping_bag AS sb
JOIN client AS c ON sb.user_id = c.user_id
JOIN users AS u ON c.user_id = u.user_id
JOIN orders AS o ON sb.shopping_id = o.shopping_id
JOIN product_in_store AS pis ON o.product_in_store_id = pis.product_in_store_id
JOIN price AS pr ON pis.product_in_store_id = pr.product_in_store_id
JOIN (

SELECT DISTINCT c2.user_id, extract(quarter FROM sb2.order_date) AS quarter, sum(pr.price) AS suma,
count(sb2.shopping_id) AS kupeni_proizvodi
FROM shopping_bag AS sb2
JOIN orders AS o ON sb2.shopping_id = o.shopping_id
JOIN product_in_store AS pis ON o.product_in_store_id = pis.product_in_store_id
join price as pr on pis.product_in_store_id=pr.product_in_store_id
JOIN client AS c2 ON c2.user_id = sb2.user_id
GROUP BY 1,2
) AS tabela ON c.user_id = tabela.user_id
	}}}

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

{{{#!sql

create view podatoci_prodavnici as 

select os.names, os.store_id,
coalesce((
select count(pis.product_in_store_id)
	from online_stores as os
	join product_in_store as pis on os.store_id=pis.store_id
	),0) as vkupno_proizvodi,
(
select sum(p.price)
	from online_stores as os
	join product_in_store as pis on os.store_id=pis.store_id
	join price as p on pis.product_in_store_id=p.product_in_store_id
	
) as vkupna_vrednost,
(
select avg(p.price)


	from online_stores as os
	join product_in_store as pis on os.store_id=pis.store_id
	join price as p on pis.product_in_store_id=p.product_in_store_id
	
) as prosek_vrednost,
(
select count(sb.shopping_id)
	from online_stores as os 
	join product_in_store as pis on os.store_id=pis.product_id
	join orders as ord on pis.product_in_store_id=ord.product_in_store_id
	join shopping_bag as sb on ord.shopping_id=sb.shopping_id
	where sb.order_date between now() - interval '3 months' and now()
) as vkupno_naracki

	
from online_stores as os
group by 1, 2

	}}}

== Вработен кој извршил достава на најмногу нарачки
{{{#!sql
create view vkupen_broj_naracki as 

select se.shipping_employee_id, u.user_name, u.surname, count(s.shipping_id) as broj_naracki
from shipping_employee as se
join users as u on se.user_id=u.user_id
join shipping as s on se.user_id=s.user_id
join shopping_bag as sb on s.shopping_id=sb.shopping_id

group by 1, 2, 3;

select vbn.shipping_employee_id, vbn.user_name, vbn.surname, vbn.broj_naracki as najmnogu_naracki
from vkupen_broj_naracki as vbn
where vbn.broj_naracki = (select max(broj_naracki) from vkupen_broj_naracki);
}}}

== Вработените од сите продавници со искуство од 6 или повеќе години и нивната улога во продавницата
{{{#!sql
create view broj_vraboteni as

select os.store_id, os.names, u.user_name, u.surname, wa.works_from, r.role_name, r.role_desc
from online_stores as os
join store_employee as se on os.store_id=se.store_id
join users as u on se.user_id=u.user_id
join works_as as wa on se.user_id=wa.user_id
join roles as r on wa.id_role=r.id_role
where extract(year from now()) - extract(year from wa.works_from) >= 6
}}}

== Листање на сите продукти според достапност, каталог и продавница, и прикажување на продукт со најниска цена
{{{#!sql
create view produkti_cena as 

select p.names, p.in_store, pis.product_in_store_id, c.catalogue_id, c.type_, os.web_address, pr.price
from product as p
join product_in_store as pis on p.product_id=pis.product_id
join existss as e on pis.product_in_store_id=e.product_in_store_id
join catalogue as c on e.catalogue_id=c.catalogue_id
join online_stores as os on e.store_id=os.store_id
join price as pr on pis.product_in_store_id=pr.product_in_store_id

group by 1, 2, 3, 4, 5, 6, 7;

select pc.names, pc.in_store, pc.product_in_store_id, pc.catalogue_id, pc.type_, pc.web_address, pc.price as najniska_cena
from produkti_cena as pc
where pc.price = (select min(price) from produkti_cena)create view produkti_cena as 
}}}

== Извештај за вкупно продадени продукти по категорија
{{{#!sql
create view kategorija_produkti as

select distinct c.category_id, c.category_name,
case when tabela.naracan_proizvod >= 1 then tabela.naracan_proizvod else 0 end as naracani_proizvodi
from category as c
join product as p on p.category_id=c2.category_id
join product_in_store as pis on p.product_id=pis.product_id
join orders as o on o.product_in_store_id=pis.product_in_store_id
join(
	select distinct c2.category_id, c2.category_name, count(o.product_in_store_id) as naracan_proizvod
	from category as c2
	join product as p on p.category_id=c2.category_id
	join product_in_store as pis on p.product_id=pis.product_id
	join orders as o on o.product_in_store_id=pis.product_in_store_id
	group by 1, 2
) as tabela on c.category_id=tabela.category_id
}}}