Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

12/27/22 13:53:24 (3 years ago)

Author:

201084

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи од базата (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
+}}}