Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

12/25/22 11:49:50 (3 years ago)

Author:

201065

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи од базата (SQL и складирани процедури) =
+
+==  Извештај за најдобрите магационери во однос на нивната највредна креирана фактура. Се прикажуваат информации за вкупната сума во фактурата која ја наплатиле и дополнително се прикажува фактурираниот артиклот кој имал најголема вкупна цена во истата таа фактура==
+{{{#!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
+}}}
+
+==  Извештај за најнепрофитабилната категорија на артикли. Се прикажуваат информации за името на категоријата, нејзиниот профит и информации за вкупниот и просечниот прилив и одлив по артикл==
+
+{{{#!sql
+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)
+}}}