Changes between Version 7 and Version 8 of AdvancedReports

Timestamp:

12/28/22 15:02:52 (2 years ago)

Author:

201084

Comment:

--

AdvancedReports

@@ -1 +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 
+=== Извештај за сите делови, со нивни категории и производители, и количината која била нарачана од овие делови во секој од последните 3 календарски месеци. ===
+
+{{{#!sql
+select p.id_part, p.part_name, cat.category_name, pm.pm_name, 
+coalesce (f.narachani_vo_ovoj_mesec,0) as narachani_ovoj_mesec, 
+coalesce (s.narachani_vo_prethoden_mesec,0) as narachani_prethoden_mesec,
+coalesce (t.narachani_vo_predprethoden_mesec,0) as narachani_predprethoden_mesec
+from project.part as p
+join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
+join project.part_is_from_category as pifc on p.id_part = pifc.id_part
+join project.category as cat on pifc.id_category = cat.id_category
+join project.order_contains_part as ocp on p.id_part = ocp.id_part
+join project.order_table as o on ocp.id_order = o.id_order
+left join (
+select p.id_part, p.part_name, cat.category_name, pm.pm_name,
+       sum(ocp.quantity_order) as narachani_vo_ovoj_mesec
+from project.part as p
+join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
+join project.part_is_from_category as pifc on p.id_part = pifc.id_part
+join project.category as cat on pifc.id_category = cat.id_category
+join project.order_contains_part as ocp on p.id_part = ocp.id_part
+join project.order_table as o on ocp.id_order = o.id_order
+                                  and
+                              extract(month from o.order_date) = extract(month from now()) and extract(year from o.order_date) = extract(year from now())
+group by p.id_part, p.part_name, cat.category_name, pm.pm_name
+) as f on p.id_part = f.id_part and pm.pm_name = f.pm_name
+left join (
+select p.id_part, p.part_name, cat.category_name, pm.pm_name,
+       sum(ocp.quantity_order)  as narachani_vo_prethoden_mesec
+from project.part as p
+join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
+join project.part_is_from_category as pifc on p.id_part = pifc.id_part
+join project.category as cat on pifc.id_category = cat.id_category
+join project.order_contains_part as ocp on p.id_part = ocp.id_part
+join project.order_table as o on ocp.id_order = o.id_order
+                                  and
+                              extract(month from o.order_date) = extract(month from now()-interval'1 month') and extract(year from o.order_date) = extract(year from now()-interval'1 month')
+group by p.id_part, p.part_name, cat.category_name, pm.pm_name 
+) as s on p.id_part = s.id_part and pm.pm_name = s.pm_name
+left join (
+select p.id_part, p.part_name, cat.category_name, pm.pm_name,
+       sum(ocp.quantity_order)  as narachani_vo_predprethoden_mesec
+from project.part as p
+join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
+join project.part_is_from_category as pifc on p.id_part = pifc.id_part
+join project.category as cat on pifc.id_category = cat.id_category
+join project.order_contains_part as ocp on p.id_part = ocp.id_part
+join project.order_table as o on ocp.id_order = o.id_order
+                                  and
+                              extract(month from o.order_date) = extract(month from now()-interval'2 month') and extract(year from o.order_date) = extract(year from now()-interval'2 month')
+group by p.id_part, p.part_name, cat.category_name, pm.pm_name 
+) as t on p.id_part = t.id_part and pm.pm_name = t.pm_name
+group by p.id_part, p.part_name, cat.category_name, pm.pm_name, f.narachani_vo_ovoj_mesec, s.narachani_vo_prethoden_mesec, t.narachani_vo_predprethoden_mesec
+}}}
+
+=== Извештај за производителите на авто делови, колку делови од тој производител се продаваат за секоја четвртина од годината ===
+
+{{{#!sql
+select pm.id_part_manufacturer ,pm.pm_name , 
+coalesce(fq.count_first_quarter,0) as first_quarter, coalesce(sq.count_second_quarter,0) as second_quarter,
+coalesce(tq.count_third_quarter,0) as third_quarter, coalesce(frq.count_fourth_quarter,0) as fourth_quarter
+from project.part_manufacturer pm 
+join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
+join project.order_contains_part ocp on ocp.id_part = p.id_part 
+join project.order_table ot on ot.id_order = ocp.id_order
+left join (
+select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_first_quarter
+from project.part_manufacturer pm 
+join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
+join project.order_contains_part ocp on ocp.id_part = p.id_part 
+join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date between to_date(concat('01-01-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('31-03-',extract(year from now())),'dd-mm-yyyy') 
+group by pm.id_part_manufacturer 
+) as fq on pm.id_part_manufacturer = fq.id_part_manufacturer and pm.pm_name = fq.pm_name
+left join (
+select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_second_quarter
+from project.part_manufacturer pm 
+join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
+join project.order_contains_part ocp on ocp.id_part = p.id_part 
+join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date between to_date(concat('01-04-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('30-06-',extract(year from now())),'dd-mm-yyyy') 
+group by pm.id_part_manufacturer 
+) as sq on pm.id_part_manufacturer = sq.id_part_manufacturer and pm.pm_name = sq.pm_name
+left join (
+select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_third_quarter
+from project.part_manufacturer pm 
+join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
+join project.order_contains_part ocp on ocp.id_part = p.id_part 
+join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date between to_date(concat('01-07-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('30-09-',extract(year from now())),'dd-mm-yyyy') 
+group by pm.id_part_manufacturer 
+) as tq on pm.id_part_manufacturer = tq.id_part_manufacturer and pm.pm_name = tq.pm_name
+left join (
+select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_fourth_quarter
+from project.part_manufacturer pm 
+join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
+join project.order_contains_part ocp on ocp.id_part = p.id_part 
+join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date between to_date(concat('01-10-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('31-12-',extract(year from now())),'dd-mm-yyyy') 
+group by pm.id_part_manufacturer 
+) as frq on pm.id_part_manufacturer = frq.id_part_manufacturer and pm.pm_name = frq.pm_name
+group by pm.id_part_manufacturer, fq.count_first_quarter, sq.count_second_quarter, tq.count_third_quarter, frq.count_fourth_quarter
 }}}
 
@@ -65 +156 @@
         order by vkupna_suma desc
 }}}
+
 === Извештај за сите категории и за кој прозиводител на коли биле купени највеќе делови од таа категорија ===
+
 {{{#!sql
 select c.id_category, c.category_name, cm.id_car_manufacturer , cm.cm_name ,
@@ -83 +176 @@
 
 === Извештај за најкупуван артикл, во која количина и од која категорија е истиот ===
+
 {{{#!sql 
 select c.category_name , p.part_name , coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
@@ -101 +195 @@
 }}}
 
-=== Извештај за сите делови, со нивни категории и производители, и количината која била нарачана од овие делови во секој од последните 3 календарски месеци. ===
-{{{#!sql
-select p.id_part, p.part_name, cat.category_name, pm.pm_name, 
-coalesce (f.narachani_vo_ovoj_mesec,0) as narachani_ovoj_mesec, 
-coalesce (s.narachani_vo_prethoden_mesec,0) as narachani_prethoden_mesec,
-coalesce (t.narachani_vo_predprethoden_mesec,0) as narachani_predprethoden_mesec
-from project.part as p
-join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
-join project.part_is_from_category as pifc on p.id_part = pifc.id_part
-join project.category as cat on pifc.id_category = cat.id_category
-join project.order_contains_part as ocp on p.id_part = ocp.id_part
-join project.order_table as o on ocp.id_order = o.id_order
-left join (
-select p.id_part, p.part_name, cat.category_name, pm.pm_name,
-       sum(ocp.quantity_order) as narachani_vo_ovoj_mesec
-from project.part as p
-join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
-join project.part_is_from_category as pifc on p.id_part = pifc.id_part
-join project.category as cat on pifc.id_category = cat.id_category
-join project.order_contains_part as ocp on p.id_part = ocp.id_part
-join project.order_table as o on ocp.id_order = o.id_order
-                                  and
-                              extract(month from o.order_date) = extract(month from now()) and extract(year from o.order_date) = extract(year from now())
-group by p.id_part, p.part_name, cat.category_name, pm.pm_name
-) as f on p.id_part = f.id_part and pm.pm_name = f.pm_name
-left join (
-select p.id_part, p.part_name, cat.category_name, pm.pm_name,
-       sum(ocp.quantity_order)  as narachani_vo_prethoden_mesec
-from project.part as p
-join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
-join project.part_is_from_category as pifc on p.id_part = pifc.id_part
-join project.category as cat on pifc.id_category = cat.id_category
-join project.order_contains_part as ocp on p.id_part = ocp.id_part
-join project.order_table as o on ocp.id_order = o.id_order
-                                  and
-                              extract(month from o.order_date) = extract(month from now()-interval'1 month') and extract(year from o.order_date) = extract(year from now()-interval'1 month')
-group by p.id_part, p.part_name, cat.category_name, pm.pm_name 
-) as s on p.id_part = s.id_part and pm.pm_name = s.pm_name
-left join (
-select p.id_part, p.part_name, cat.category_name, pm.pm_name,
-       sum(ocp.quantity_order)  as narachani_vo_predprethoden_mesec
-from project.part as p
-join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
-join project.part_is_from_category as pifc on p.id_part = pifc.id_part
-join project.category as cat on pifc.id_category = cat.id_category
-join project.order_contains_part as ocp on p.id_part = ocp.id_part
-join project.order_table as o on ocp.id_order = o.id_order
-                                  and
-                              extract(month from o.order_date) = extract(month from now()-interval'2 month') and extract(year from o.order_date) = extract(year from now()-interval'2 month')
-group by p.id_part, p.part_name, cat.category_name, pm.pm_name 
-) as t on p.id_part = t.id_part and pm.pm_name = t.pm_name
-group by p.id_part, p.part_name, cat.category_name, pm.pm_name, f.narachani_vo_ovoj_mesec, s.narachani_vo_prethoden_mesec, t.narachani_vo_predprethoden_mesec
-
-}}}
 === Извештај за деловите кои биле нарачани во последните 3 месеци, со нивни категории и производители и количината која била нарачана ===
+
 {{{#!sql
 select p.id_part, p.part_name, cat.category_name, pm.pm_name,
@@ -170 +211 @@
 }}}
 
-=== Извештај за производителите на авто делови, колку делови од тој производител се продаваат за секоја четвртина од годината ===
-{{{#!sql
-select pm.id_part_manufacturer ,pm.pm_name , 
-coalesce(fq.count_first_quarter,0) as first_quarter, coalesce(sq.count_second_quarter,0) as second_quarter,
-coalesce(tq.count_third_quarter,0) as third_quarter, coalesce(frq.count_fourth_quarter,0) as fourth_quarter
-from project.part_manufacturer pm 
-join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
-join project.order_contains_part ocp on ocp.id_part = p.id_part 
-join project.order_table ot on ot.id_order = ocp.id_order
-left join (
-select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_first_quarter
-from project.part_manufacturer pm 
-join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
-join project.order_contains_part ocp on ocp.id_part = p.id_part 
-join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date between to_date(concat('01-01-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('31-03-',extract(year from now())),'dd-mm-yyyy') 
-group by pm.id_part_manufacturer 
-) as fq on pm.id_part_manufacturer = fq.id_part_manufacturer and pm.pm_name = fq.pm_name
-left join (
-select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_second_quarter
-from project.part_manufacturer pm 
-join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
-join project.order_contains_part ocp on ocp.id_part = p.id_part 
-join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date between to_date(concat('01-04-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('30-06-',extract(year from now())),'dd-mm-yyyy') 
-group by pm.id_part_manufacturer 
-) as sq on pm.id_part_manufacturer = sq.id_part_manufacturer and pm.pm_name = sq.pm_name
-left join (
-select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_third_quarter
-from project.part_manufacturer pm 
-join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
-join project.order_contains_part ocp on ocp.id_part = p.id_part 
-join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date between to_date(concat('01-07-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('30-09-',extract(year from now())),'dd-mm-yyyy') 
-group by pm.id_part_manufacturer 
-) as tq on pm.id_part_manufacturer = tq.id_part_manufacturer and pm.pm_name = tq.pm_name
-left join (
-select pm.id_part_manufacturer ,pm.pm_name , count(ocp.id_part) as count_fourth_quarter
-from project.part_manufacturer pm 
-join project.part p on p.id_part_manufacturer = pm.id_part_manufacturer 
-join project.order_contains_part ocp on ocp.id_part = p.id_part 
-join project.order_table ot on ot.id_order = ocp.id_order and ot.order_date between to_date(concat('01-10-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('31-12-',extract(year from now())),'dd-mm-yyyy') 
-group by pm.id_part_manufacturer 
-) as frq on pm.id_part_manufacturer = frq.id_part_manufacturer and pm.pm_name = frq.pm_name
-group by pm.id_part_manufacturer, fq.count_first_quarter, sq.count_second_quarter, tq.count_third_quarter, frq.count_fourth_quarter
-
-}}}
+=== Извештај за сите сервиси,број на критики кои ги добиле и нивниот просечен рејтинг ===
+
+{{{#!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 
+}}}
+