Changeset 0328cb0


Ignore:
Timestamp:
01/17/23 00:36:07 (18 months ago)
Author:
DarkoSasanski <darko.sasanski@…>
Branches:
main
Children:
c76db45
Parents:
4b34fed
Message:

Added custom queries

Location:
FullyStocked/src/main
Files:
6 added
7 edited

Legend:

Unmodified
Added
Removed

  • FullyStocked/src/main/java/com/bazi/fullystocked/Services/ArticlesService.java

    r4b34fed r0328cb0  
    33import com.bazi.fullystocked.Models.Articles;
    44import com.bazi.fullystocked.Models.Categories;
     5import com.bazi.fullystocked.Models.DTO.ArticleAnalysisDTO;
    56import com.bazi.fullystocked.Models.SqlViews.ArticlesAtLocationReport;
    67import com.bazi.fullystocked.Models.SqlViews.SupplierSuppliesArticleReport;
    … …  
    2021
    2122    List<ArticlesAtLocationReport> findAvailabilityAtAllLocations(Integer id);
     23    List<ArticleAnalysisDTO> getArticleAnalysis();
    2224
    2325}

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

    r4b34fed r0328cb0  
    33import com.bazi.fullystocked.Models.Articles;
    44import com.bazi.fullystocked.Models.Categories;
     5import com.bazi.fullystocked.Models.DTO.ArticleAnalysisDTO;
    56import com.bazi.fullystocked.Models.Exceptions.InvalidArgumentsException;
    67import com.bazi.fullystocked.Models.SqlViews.ArticlesAtLocationReport;
    … …  
    1112import com.bazi.fullystocked.Repositories.SupplierSuppliesArticleRepository;
    1213import com.bazi.fullystocked.Services.ArticlesService;
     14import org.hibernate.query.NativeQuery;
     15import org.hibernate.transform.Transformers;
    1316import org.springframework.stereotype.Service;
    1417
     18import javax.persistence.EntityManager;
     19import javax.persistence.PersistenceContext;
    1520import javax.transaction.Transactional;
    1621import java.util.List;
    … …  
    2328    private final SupplierSuppliesArticleRepository supplierSuppliesArticleRepository;
    2429    private final ArticlesAtLocationRepository articlesAtLocationRepository;
     30    @PersistenceContext
     31    private EntityManager entityManager;
    2532
    2633    public ArticlesServiceImpl(ArticlesRepository articlesRepository, CategoriesRepository categoriesRepository, SupplierSuppliesArticleRepository supplierSuppliesArticleRepository, ArticlesAtLocationRepository articlesAtLocationRepository) {
    … …  
    8592        return articlesAtLocationRepository.findAllByArticleid(id);
    8693    }
     94
     95    @Override
     96    public List<ArticleAnalysisDTO> getArticleAnalysis() {
     97        List<ArticleAnalysisDTO> results = entityManager.createNativeQuery("""
     98        select a.articlename, l.locationname,
     99        (case
     100            when q1.dostapnost+coalesce(q2.incoming, 0)<coalesce(q3.sold, 0) then 'Yes'
     101            else 'No'
     102        end
     103        ) as defict from project.articles a
     104       left join project.storedarticles s on s.articleid=a.articleid
     105       left join project.locations l on l.locationid=s.locationid
     106       left join (
     107        select articleid, locationid, coalesce(sum(quantity), 0) as dostapnost from project.storedarticles s
     108        group by s.sarticleid, s.locationid
     109       ) q1 on q1.articleid=a.articleid and q1.locationid=l.locationid
     110       left join (
     111        select articleid, locationid, coalesce(sum(quantity), 0) as incoming from project.orderedarticles o
     112        where o.articlestatus='Ordered' or o.articlestatus='Delivered'
     113        group by o.articleid, o.locationid
     114       ) q2 on q2.articleid=a.articleid and q2.locationid=l.locationid
     115       left join (
     116        select i.articleid, l.locationid, coalesce(sum(quantity), 0) as sold from project.invoicedarticles i
     117        left join project.invoices inv on inv.invoiceid=i.invoiceid and inv.datecreate between now()-interval '1 month' and now()
     118        left join project.workers w on w.userid=inv.workeruserid
     119        left join project.locations l on l.locationid=w.locationid
     120        group by i.articleid, l.locationid
     121       )q3 on q3.articleid=a.articleid and q3.locationid=l.locationid
     122       order by a.articlename\s
     123""")
     124                .unwrap(NativeQuery.class)
     125                .setResultTransformer(Transformers.aliasToBean(ArticleAnalysisDTO.class))
     126                .getResultList();
     127        return results;
     128    }
    87129}

  • FullyStocked/src/main/java/com/bazi/fullystocked/Services/Implementations/LocationServiceImpl.java

    r4b34fed r0328cb0  
    11package com.bazi.fullystocked.Services.Implementations;
    22
     3import com.bazi.fullystocked.Models.DTO.LocationAnalysisDTO;
     4import com.bazi.fullystocked.Models.DTO.TopUsersDTO;
    35import com.bazi.fullystocked.Models.Locations;
    46import com.bazi.fullystocked.Models.SqlViews.ArticlesAtLocationReport;
    … …  
    68import com.bazi.fullystocked.Repositories.LocationsRepository;
    79import com.bazi.fullystocked.Services.LocationsService;
     10import org.hibernate.query.NativeQuery;
     11import org.hibernate.transform.Transformers;
    812import org.springframework.stereotype.Service;
    913
     14import javax.persistence.EntityManager;
     15import javax.persistence.PersistenceContext;
    1016import java.util.List;
    1117
    … …  
    1420    private final ArticlesAtLocationRepository articlesAtLocationRepository;
    1521    private final LocationsRepository locationsRepository;
     22    @PersistenceContext
     23    private EntityManager entityManager;
    1624
    1725    public LocationServiceImpl(ArticlesAtLocationRepository articlesAtLocationRepository, LocationsRepository locationsRepository) {
    … …  
    2937        return locationsRepository.findAll();
    3038    }
     39
     40    @Override
     41    public List<LocationAnalysisDTO> getLocationAnalysis() {
     42        List<LocationAnalysisDTO> results = entityManager.createNativeQuery("""
     43        select q1.locationname, prihod-odliv as profit from\s
     44           (
     45            select l.locationid, l.locationname, coalesce(sum(i.price*i.quantity), 0) as prihod from project.locations l\s
     46            left join project.storedarticles s on s.locationid=l.locationid\s
     47            left join project.invoicedarticles i on i.articleid=s.articleid
     48            left join project.invoices i2 on i2.invoiceid=i.invoiceid and i2.datecreate between now()-interval '1 year' and now()
     49            group by l.locationid
     50           
     51           ) as q1
     52           full outer join\s
     53           (
     54            select l.locationid, l.locationname, coalesce(sum(o.price*o.quantity), 0) as odliv from project.locations l\s
     55            left join project.orderedarticles o on o.locationid=l.locationid\s
     56            left join project.orders o2 on o2.orderid=o.orderid and o2.datecreated between now()-interval '1 year' and now()
     57            group by l.locationid
     58           
     59           ) q2 on q2.locationid=q1.locationid
     60""")
     61                .unwrap(NativeQuery.class)
     62                .setResultTransformer(Transformers.aliasToBean(LocationAnalysisDTO.class))
     63                .getResultList();
     64        return results;
     65    }
    3166}

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

    r4b34fed r0328cb0  
    11package com.bazi.fullystocked.Services.Implementations;
    22
     3import com.bazi.fullystocked.Models.DTO.ArticleAnalysisDTO;
     4import com.bazi.fullystocked.Models.DTO.TopUsersDTO;
    35import com.bazi.fullystocked.Models.Exceptions.InvalidArgumentsException;
    46import com.bazi.fullystocked.Models.Locations;
    … …  
    79import com.bazi.fullystocked.Repositories.WorkersRepository;
    810import com.bazi.fullystocked.Services.WorkersService;
     11import org.hibernate.query.NativeQuery;
     12import org.hibernate.transform.Transformers;
    913import org.springframework.stereotype.Service;
    1014
     15import javax.persistence.EntityManager;
     16import javax.persistence.PersistenceContext;
    1117import java.util.List;
    1218import java.util.Optional;
    … …  
    1622    private final WorkersRepository workersRepository;
    1723    private final LocationsRepository locationsRepository;
     24    @PersistenceContext
     25    private EntityManager entityManager;
    1826
    1927    public WorkersServiceImpl(WorkersRepository workersRepository, LocationsRepository locationsRepository) {
    … …  
    3442        return workersRepository.findAllByLocationIsNull();
    3543    }
     44
     45    @Override
     46    public List<TopUsersDTO> findAllTopUsers() {
     47        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
     73""")
     74                .unwrap(NativeQuery.class)
     75                .setResultTransformer(Transformers.aliasToBean(TopUsersDTO.class))
     76                .getResultList();
     77        return results;
     78    }
     79
     80
    3681}

  • FullyStocked/src/main/java/com/bazi/fullystocked/Services/LocationsService.java

    r4b34fed r0328cb0  
    22
    33
     4import com.bazi.fullystocked.Models.DTO.LocationAnalysisDTO;
    45import com.bazi.fullystocked.Models.Locations;
    56import com.bazi.fullystocked.Models.SqlViews.ArticlesAtLocationReport;
    … …  
    1011    List<ArticlesAtLocationReport> findAllArticlesAtLocation(Integer locationid);
    1112    List<Locations> findAll();
     13    List<LocationAnalysisDTO> getLocationAnalysis();
    1214}

  • FullyStocked/src/main/java/com/bazi/fullystocked/Services/WorkersService.java

    r4b34fed r0328cb0  
    11package com.bazi.fullystocked.Services;
    22
     3import com.bazi.fullystocked.Models.DTO.ArticleAnalysisDTO;
     4import com.bazi.fullystocked.Models.DTO.TopUsersDTO;
    35import com.bazi.fullystocked.Models.Workers;
    46import java.util.*;
    … …  
    79    Optional<Workers> assignLocation(Integer workerId, Integer locationId);
    810    List<Workers> findAllWithNoLocation();
     11    List<TopUsersDTO> findAllTopUsers();
    912}

  • FullyStocked/src/main/java/com/bazi/fullystocked/Web/Controller/ManagerController.java

    r4b34fed r0328cb0  
    11package com.bazi.fullystocked.Web.Controller;
    22
     3import com.bazi.fullystocked.Services.ArticlesService;
    34import com.bazi.fullystocked.Services.LocationsService;
    45import com.bazi.fullystocked.Services.WorkersService;
    … …  
    1516    private final WorkersService workersService;
    1617    private final LocationsService locationsService;
     18    private final ArticlesService articlesService;
    1719
    18     public ManagerController(WorkersService workersService, LocationsService locationsService) {
     20    public ManagerController(WorkersService workersService, LocationsService locationsService, ArticlesService articlesService) {
    1921        this.workersService = workersService;
    2022        this.locationsService = locationsService;
     23        this.articlesService = articlesService;
    2124    }
    2225
    … …  
    4750        }
    4851    }
     52    @GetMapping("/topWorkers")
     53    public String listTopWorkersModel(Model model)
     54    {
     55        model.addAttribute("workers", workersService.findAllTopUsers());
     56        return "topWorkers";
     57    }
     58    @GetMapping("/articleAnalysis")
     59    public String getArticleAnalysis(Model model)
     60    {
     61        model.addAttribute("articles", articlesService.getArticleAnalysis());
     62        return "articleAnalysis";
     63    }
     64    @GetMapping("/locationAnalysis")
     65    public String getLocationAnalysis(Model model)
     66    {
     67        model.addAttribute("locations", locationsService.getLocationAnalysis());
     68        return "locationAnalysis";
     69    }
    4970}
Note: See TracChangeset for help on using the changeset viewer.