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

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

select u.firstname, u.lastname, u.username, u.email, max(ti.totalPrice) as topInvoiceSum, 
(select a.articlename from articles a
        left join invoicedarticles i2 on i2.articleid=a.articleid
        where i2.price*i2.quantity=max(ba.totalPrice)) as topArticleName,
(select i2.price  from articles a
        left join invoicedarticles i2 on i2.articleid=a.articleid
        where i2.price*i2.quantity=max(ba.totalPrice)) as topArticlePrice,
(select i2.quantity from articles a
        left join invoicedarticles i2 on i2.articleid=a.articleid
        where i2.price*i2.quantity=max(ba.totalPrice)) as topArticleQuantity,
max(ba.totalPrice) as topArticleTotalPrice from workers w 
                left join users u on u.userid=w.userid
                left join 
                        (
                                select i.invoiceid, i.workeruserid , sum(ia.price*ia.quantity) as totalPrice from invoices i
                                left join invoicedarticles ia on ia.invoiceid=i.invoiceid
                                group by i.invoiceid, i.workeruserid
                        ) ti on ti.workeruserid=u.userid
                left join
                        (
                                select ia2.invoiceid, a.articlename, ia2.price as price, ia2.quantity as quantity, ia2.price*ia2.quantity as totalPrice from invoicedarticles ia2
                                left join articles a on a.articleid=ia2.articleid
                        ) ba on ba.invoiceid=ti.invoiceid
                group by u.userid
                order by topInvoiceSum desc
                limit 5

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

select c.categoryname, prihod.pri-odliv.odl as profit, prihod.pri as priliv, odliv.odl as odliv,
(select avg(i2.quantity*i2.price) from invoicedarticles i2) as avgPrihod,
(select avg(o2.quantity*o2.price) from orderedarticles o2) as avgOdliv
from categories c
        left join (
                        select abtc.categoryid, coalesce(sum(i.quantity*i.price), 0) as pri from articles a 
                        left join article_belongs_to_category abtc on abtc.articleid=a.articleid
                        left join invoicedarticles i on i.articleid=a.articleid
                        group by abtc.categoryid
                ) prihod on prihod.categoryid=c.categoryid
        left join (
                        select abtc.categoryid, coalesce(sum(o.quantity*o.price), 0) as odl from articles a 
                        left join article_belongs_to_category abtc on abtc.articleid=a.articleid
                        left join orderedarticles o on o.articleid=a.articleid
                        group by abtc.categoryid
                )odliv  on odliv.categoryid=c.categoryid
        where prihod.pri-odliv.odl=(select min(prihod.pri-odliv.odl) from categories c
                        left join (
                                        select abtc.categoryid, coalesce(sum(i.quantity*i.price), 0) as pri from articles a 
                                        left join article_belongs_to_category abtc on abtc.articleid=a.articleid
                                        left join invoicedarticles i on i.articleid=a.articleid
                                        group by abtc.categoryid
                                ) prihod on prihod.categoryid=c.categoryid
                        left join (
                                        select abtc.categoryid, coalesce(sum(o.quantity*o.price), 0) as odl from articles a 
                                        left join article_belongs_to_category abtc on abtc.articleid=a.articleid
                                        left join orderedarticles o on o.articleid=a.articleid
                                        group by abtc.categoryid
                                )odliv  on odliv.categoryid=c.categoryid)

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

select q1.locationname, prihod-odliv as profit from 
(
        select l.locationid, l.locationname, coalesce(sum(i.price*i.quantity), 0) as prihod from locations l 
        left join storedarticles s on s.locationid=l.locationid 
        left join invoicedarticles i on i.articleid=s.articleid
        left join invoices i2 on i2.invoiceid=i.invoiceid and i2.datecreate between now()-interval '1 year' and now()
        group by l.locationid
        
) as q1
full outer join 
(
        select l.locationid, l.locationname, coalesce(sum(o.price*o.quantity), 0) as odliv from locations l 
        left join orderedarticles o on o.locationid=l.locationid 
        left join orders o2 on o2.orderid=o.orderid and o2.datecreated between now()-interval '1 year' and now()
        group by l.locationid
        
) q2 on q2.locationid=q1.locationid

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

select c.categoryname, a.articlename, coalesce(sum(i.quantity),0) as vkol from articles a
        left join article_belongs_to_category abtc on abtc.articleid=a.articleid 
        left join categories c on c.categoryid=abtc.categoryid 
        left join invoicedarticles i on i.articleid=a.articleid 
        group by c.categoryid, a.articleid 
        having coalesce(sum(i.quantity), 0)=(
                select max(vkol) from
                (
                        select coalesce(sum(i.quantity),0) as vkol from articles a
                        left join article_belongs_to_category abtc on abtc.articleid=a.articleid 
                        left join categories c on c.categoryid=abtc.categoryid 
                        left join invoicedarticles i on i.articleid=a.articleid 
                        group by c.categoryid, a.articleid
                ) q1
        )

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

select s.supplierinfo, u.firstname, u.lastname, u.username, coalesce(sum(o2.quantity), 0) as koldostavena, coalesce(sum(o2.quantity*o2.price)) as zarabotkasupp from suppliers s 
                left join users u on u.userid=s.userid
                left join orders o on o.supplieruserid=s.userid and (o.status='Delivered' or o.status='Processed') 
                left join orderedarticles o2 on o2.orderid=o.orderid and (o2.articlestatus='Delivered' or o2.articlestatus='Processed')
                group by s.userid, u.userid
                order by zarabotkasupp desc

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

select c2.categoryname,
(select a.articlename from articles a
        left join invoicedarticles i on i.articleid=a.articleid
        left join article_belongs_to_category abtc on abtc.articleid=a.articleid
        where abtc.categoryid = c2.categoryid 
        group by a.articleid 
        having coalesce(sum(i.quantity),0)=max(q1.vkolicina) limit 1),
        max(q1.vkolicina) as vkolicinia from categories c2 
                left join(
                        select c.categoryid, c.categoryname, articlename, (coalesce(sum(i.quantity),0)) as vkolicina from categories c
                        left join article_belongs_to_category abtc on abtc.categoryid=c.categoryid
                        left join articles a on a.articleid=abtc.articleid
                        left join invoicedarticles i on i.articleid=a.articleid 
                        group by c.categoryid, a.articleid ) q1 on q1.categoryid=c2.categoryid 
        group by c2.categoryid