Changeset 4da1b1c


Ignore:
Timestamp:
02/25/23 15:13:21 (22 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:
2 edited

Legend:

Unmodified
Added
Removed
  • FullyStocked/src/main/java/com/bazi/fullystocked/Services/Implementations/ArticlesServiceImpl.java

    r3a5ee68 r4da1b1c  
    110110       left join (
    111111        select articleid, locationid, coalesce(sum(quantity), 0) as incoming from project.orderedarticles o
    112         where o.articlestatus='Ordered' or o.articlestatus='Delivered'
     112        where o.articlestatus='ORDERED' or o.articlestatus='DELIVERED'
    113113        group by o.articleid, o.locationid
    114114       ) q2 on q2.articleid=a.articleid and q2.locationid=l.locationid
  • FullyStocked/src/main/java/com/bazi/fullystocked/Services/Implementations/WorkersServiceImpl.java

    r3a5ee68 r4da1b1c  
    4646    public List<TopUsersDTO> findAllTopUsers() {
    4747        List<TopUsersDTO> results = entityManager.createNativeQuery("""
    48         select u.firstname, u.lastname, u.username, u.email, coalesce(max(ti.totalPrice), 0) as topInvoiceSum,\s
    49         (select a.articlename from project.articles a
    50             left join project.invoicedarticles i2 on i2.articleid=a.articleid
    51             where i2.price*i2.quantity=max(ba.totalPrice)) as topArticleName,
    52         (select i2.price  from project.articles a
    53             left join project.invoicedarticles i2 on i2.articleid=a.articleid
    54             where i2.price*i2.quantity=max(ba.totalPrice)) as topArticlePrice,
    55         (select i2.quantity from project.articles a
    56             left join project.invoicedarticles i2 on i2.articleid=a.articleid
    57             where i2.price*i2.quantity=max(ba.totalPrice)) as topArticleQuantity,
    58         max(ba.totalPrice) as topArticleTotalPrice from project.workers w\s
    59                 left join project.users u on u.userid=w.userid
    60                 left join\s
    61                     (
    62                         select i.invoiceid, i.workeruserid , sum(ia.price*ia.quantity) as totalPrice from project.invoices i
    63                         left join project.invoicedarticles ia on ia.invoiceid=i.invoiceid
    64                         group by i.invoiceid, i.workeruserid
    65                     ) ti on ti.workeruserid=u.userid
    66                 left join
    67                     (
    68                         select ia2.invoiceid, a.articlename, ia2.price as price, ia2.quantity as quantity, ia2.price*ia2.quantity as totalPrice from project.invoicedarticles ia2
    69                         left join project.articles a on a.articleid=ia2.articleid
    70                     ) ba on ba.invoiceid=ti.invoiceid
    71                 group by u.userid
    72                 order by topInvoiceSum desc
     48        select u.firstname, u.lastname, u.username, u.email, coalesce(max(ti.totalPrice), 0) as topInvoiceSum,
     49       (select a.articlename from project.articles a
     50           left join project.invoicedarticles i2 on i2.articleid=a.articleid
     51           where i2.price*i2.quantity=max(ba.totalPrice)
     52           limit 1) as topArticleName,
     53       (select i2.price  from project.articles a
     54           left join project.invoicedarticles i2 on i2.articleid=a.articleid
     55           where i2.price*i2.quantity=max(ba.totalPrice)
     56           limit 1) as topArticlePrice,
     57       (select i2.quantity from project.articles a
     58           left join project.invoicedarticles i2 on i2.articleid=a.articleid
     59           where i2.price*i2.quantity=max(ba.totalPrice)
     60           limit 1) as topArticleQuantity,
     61       max(ba.totalPrice) as topArticleTotalPrice from project.workers w
     62               left join project.users u on u.userid=w.userid
     63               left join
     64                   (
     65                       select i.invoiceid, i.workeruserid , sum(ia.price*ia.quantity) as totalPrice from project.invoices i
     66                       left join project.invoicedarticles ia on ia.invoiceid=i.invoiceid
     67                       group by i.invoiceid, i.workeruserid
     68                   ) ti on ti.workeruserid=u.userid
     69               left join
     70                   (
     71                       select ia2.invoiceid, a.articlename, ia2.price as price, ia2.quantity as quantity, ia2.price*ia2.quantity as totalPrice from project.invoicedarticles ia2
     72                       left join project.articles a on a.articleid=ia2.articleid
     73                   ) ba on ba.invoiceid=ti.invoiceid
     74               group by u.userid
     75               order by topInvoiceSum desc
    7376""")
    7477                .unwrap(NativeQuery.class)
Note: See TracChangeset for help on using the changeset viewer.