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

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

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

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

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

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

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

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

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

Извештај за сите категории и за кој прозиводител на коли биле купени највеќе делови од таа категорија

select c.id_category, c.category_name, cm.id_car_manufacturer , cm.cm_name ,
count(ocp.id_part) as category_by_car
from project.category as c
left join project.part_is_from_category pifc on pifc.id_category = c.id_category 
left join project.part p on p.id_part = pifc.id_part 
left join project.order_contains_part ocp on ocp.id_part = p.id_part 
left join project.order_table ot on ot.id_order = ocp.id_order 
left join project.users_table ut on ut.id_user = ot.id_user 
left join project.car_sample cs on cs.id_user = ut.id_user 
left join project.car c2 on c2.id_car = cs.id_car 
left join project.car_manufacturer cm on cm.id_car_manufacturer = c2.id_car_manufacturer 
group by c.id_category,cm.id_car_manufacturer  
order by category_by_car desc

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

select c.category_name , p.part_name , coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
        left join project.part_is_from_category pifc on pifc.id_part = p.id_part 
        left join project.category c on c.id_category = pifc.id_category  
        left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part 
        group by c.id_category , p.id_part  
        having coalesce(sum(ocp2.quantity_order), 0)=(
                select max(maxkol) from
                (
                        select coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
                        left join project.part_is_from_category pifc on pifc.id_part = p.id_part 
                        left join project.category c on c.id_category = pifc.id_category  
                        left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part 
                        group by c.id_category , p.id_part 
                ) q1
                )

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

Се пресметуваат колку количина од деловите имало во завршените нарачки во последните 3 месеци и се додава моменталната количина во нарачки што се уште не се завршени и се проверува дали има доволно во магацинот за тие

select p.id_part,p.part_name, sum(ocp.quantity_order) as quantity_ordered, piisiw.quantity_warehouse,
coalesce(pql3m.pql3,0) as quantity_ordered_last_3_months,
case 
        when piisiw.quantity_warehouse > sum(ocp.quantity_order)+coalesce(pql3m.pql3,0) then 'Has enough'
        else 'Needs to order more'
end has_enough_in_stock
from project.warehouse w 
join project.part_is_in_stock_in_warehouse piisiw on piisiw.id_warehouse = w.id_warehouse 
join project.order_contains_part ocp on ocp.id_part = piisiw.id_part 
join project.part p on p.id_part = ocp.id_part 
join project.delivery d on d.id_order = ocp.id_order and d.delivery_status ilike '%in progress%'
left join (
select p.id_part, p.part_name, sum(ocp.quantity_order) as pql3
from project.order_table ot 
join project.order_contains_part ocp on ocp.id_order = ot.id_order 
join project.part p on p.id_part = ocp.id_part 
where ot.order_date between now()-interval'3 months' and now()
and ot.order_status ilike '%finished%'
group by p.id_part, p.part_name
) as pql3m on pql3m.id_part = ocp.id_part
group by p.id_part, piisiw.quantity_warehouse, pql3m.pql3

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

select rs.id_repair_shop as rs_id , rs.rs_name as rs_name , coalesce(count(r2.review_rating),0) as review_count , coalesce(avg(r2.review_rating),0)::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