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

=== Извештај за сите сервиси,број на критики кои ги добиле и нивниот просечен рејтинг ===
{{{#!sql
select rs.id_repair_shop as rs_id , rs.rs_name as rs_name , count(r2.review_rating) as review_count ,avg(r2.review_rating)::float as review_average
from project.repair_shop rs 
left join project.repair r on r.id_repair_shop = rs.id_repair_shop 
left join project.review r2 on r2.id_repair = r.id_repair 
group by rs.id_repair_shop 
}}}

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

{{{#!sql
select nar.deliverer, nar.deliverer_username,
	max(nar.nar_suma) as najdobra_naracka, max(par.par_suma) as najskap_proizvod 
	from project.delivery_man d 
	left join 
	(
		select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
		,sum(p2.amount*ocp.quantity_order) as nar_suma
		from project.delivery_man d
		left join project.users_table ut on ut.id_user = d.id_user 
		left join project.delivery d2 on d2.id_user = d.id_user 
		left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
		left join project.part p on p.id_part = ocp.id_part 
		left join project.price p2 on p2.id_part = p.id_part
		group by (d.id_user,ut.username,delivery_num)
	) as nar on d.id_user = nar.deliverer
	left join 
	(
		select d.id_user as deliverer, ocp.id_order as order_num,d2.id_delivery as delivery_num, p.part_name as part_name,
		sum(p2.amount*ocp.quantity_order) as par_suma
		from project.delivery_man d
		left join project.users_table ut on ut.id_user = d.id_user 
		left join project.delivery d2 on d2.id_user = d.id_user 
		left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
		left join project.part p on p.id_part = ocp.id_part 
		left join project.price p2 on p2.id_part = p.id_part
		group by (d.id_user,ocp.id_order,d2.id_delivery,p.part_name)
	) as par on nar.deliverer = par.deliverer and nar.delivery_num = par.delivery_num
	group by nar.deliverer,nar.deliverer_username 
	order by najdobra_naracka desc
}}}

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

{{{#!sql
select nar.deliverer, nar.deliverer_username, count(distinct(nar.delivery_num)) as vkupno_dostavi,
	sum(nar.nar_suma) as vkupna_suma
	from project.delivery_man d 
	left join 
	(
		select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
		,sum(p2.amount*ocp.quantity_order) as nar_suma
		from project.delivery_man d
		left join project.users_table ut on ut.id_user = d.id_user 
		left join project.delivery d2 on d2.id_user = d.id_user 
		left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
		left join project.part p on p.id_part = ocp.id_part 
		left join project.price p2 on p2.id_part = p.id_part
		group by (d.id_user,ut.username,delivery_num)
	) as nar on d.id_user = nar.deliverer
	group by nar.deliverer,nar.deliverer_username 
	order by vkupna_suma desc
}}}