Changeset 4da1b1c

Timestamp:

02/25/23 15:13:21 (21 months ago)

Author:

DarkoSasanski <darko.sasanski@…>

Branches:

main

Children:

3b4e204

Parents:

3a5ee68

Message:

top workers query fix

Location:

FullyStocked/src/main/java/com/bazi/fullystocked/Services/Implementations

Files:

• ArticlesServiceImpl.java (modified) (1 diff)
• WorkersServiceImpl.java (modified) (1 diff)

FullyStocked/src/main/java/com/bazi/fullystocked/Services/Implementations/ArticlesServiceImpl.java

@@ -110 +110 @@
        left join (
         select articleid, locationid, coalesce(sum(quantity), 0) as incoming from project.orderedarticles o
-        where o.articlestatus='Ordered' or o.articlestatus='Delivered'
+        where o.articlestatus='ORDERED' or o.articlestatus='DELIVERED'
         group by o.articleid, o.locationid
        ) q2 on q2.articleid=a.articleid and q2.locationid=l.locationid

FullyStocked/src/main/java/com/bazi/fullystocked/Services/Implementations/WorkersServiceImpl.java

@@ -46 +46 @@
     public List<TopUsersDTO> findAllTopUsers() {
         List<TopUsersDTO> results = entityManager.createNativeQuery("""
-        select u.firstname, u.lastname, u.username, u.email, coalesce(max(ti.totalPrice), 0) as topInvoiceSum,\s
-        (select a.articlename from project.articles a
-            left join project.invoicedarticles i2 on i2.articleid=a.articleid
-            where i2.price*i2.quantity=max(ba.totalPrice)) as topArticleName,
-        (select i2.price  from project.articles a
-            left join project.invoicedarticles i2 on i2.articleid=a.articleid
-            where i2.price*i2.quantity=max(ba.totalPrice)) as topArticlePrice,
-        (select i2.quantity from project.articles a
-            left join project.invoicedarticles i2 on i2.articleid=a.articleid
-            where i2.price*i2.quantity=max(ba.totalPrice)) as topArticleQuantity,
-        max(ba.totalPrice) as topArticleTotalPrice from project.workers w\s
-                left join project.users u on u.userid=w.userid
-                left join\s
-                    (
-                        select i.invoiceid, i.workeruserid , sum(ia.price*ia.quantity) as totalPrice from project.invoices i
-                        left join project.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 project.invoicedarticles ia2
-                        left join project.articles a on a.articleid=ia2.articleid
-                    ) ba on ba.invoiceid=ti.invoiceid
-                group by u.userid
-                order by topInvoiceSum desc
+        select u.firstname, u.lastname, u.username, u.email, coalesce(max(ti.totalPrice), 0) as topInvoiceSum,
+       (select a.articlename from project.articles a
+           left join project.invoicedarticles i2 on i2.articleid=a.articleid
+           where i2.price*i2.quantity=max(ba.totalPrice)
+           limit 1) as topArticleName,
+       (select i2.price  from project.articles a
+           left join project.invoicedarticles i2 on i2.articleid=a.articleid
+           where i2.price*i2.quantity=max(ba.totalPrice)
+           limit 1) as topArticlePrice,
+       (select i2.quantity from project.articles a
+           left join project.invoicedarticles i2 on i2.articleid=a.articleid
+           where i2.price*i2.quantity=max(ba.totalPrice)
+           limit 1) as topArticleQuantity,
+       max(ba.totalPrice) as topArticleTotalPrice from project.workers w
+               left join project.users u on u.userid=w.userid
+               left join
+                   (
+                       select i.invoiceid, i.workeruserid , sum(ia.price*ia.quantity) as totalPrice from project.invoices i
+                       left join project.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 project.invoicedarticles ia2
+                       left join project.articles a on a.articleid=ia2.articleid
+                   ) ba on ba.invoiceid=ti.invoiceid
+               group by u.userid
+               order by topInvoiceSum desc
 """)
                 .unwrap(NativeQuery.class)