Changes between Version 6 and Version 7 of AdvancedReports

Timestamp:

12/27/22 20:59:59 (2 years ago)

Author:

201065

Comment:

--

AdvancedReports

@@ -70 +70 @@
 select q1.locationname, prihod-odliv as profit from 
 (
-        select l.locationid, l.locationname, sum(i.price*i.quantity) as prihod from locations l 
+        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
@@ -79 +79 @@
 full outer join 
 (
-        select l.locationid, l.locationname, sum(o.price*o.quantity) as odliv from locations l 
+        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()
@@ -85 +85 @@
         
 ) q2 on q2.locationid=q1.locationid
-
 }}}
 
@@ -109 +108 @@
 
 }}}
+
+===  Извештај за набавувачите со најголема заработка===
+
+{{{#!sql
+        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
+
+
+}}}