Changeset 0328cb0
- Timestamp:
- 01/17/23 00:36:07 (2 years ago)
- Branches:
- main
- Children:
- c76db45
- Parents:
- 4b34fed
- 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 3 3 import com.bazi.fullystocked.Models.Articles; 4 4 import com.bazi.fullystocked.Models.Categories; 5 import com.bazi.fullystocked.Models.DTO.ArticleAnalysisDTO; 5 6 import com.bazi.fullystocked.Models.SqlViews.ArticlesAtLocationReport; 6 7 import com.bazi.fullystocked.Models.SqlViews.SupplierSuppliesArticleReport; … … 20 21 21 22 List<ArticlesAtLocationReport> findAvailabilityAtAllLocations(Integer id); 23 List<ArticleAnalysisDTO> getArticleAnalysis(); 22 24 23 25 } -
FullyStocked/src/main/java/com/bazi/fullystocked/Services/Implementations/ArticlesServiceImpl.java
r4b34fed r0328cb0 3 3 import com.bazi.fullystocked.Models.Articles; 4 4 import com.bazi.fullystocked.Models.Categories; 5 import com.bazi.fullystocked.Models.DTO.ArticleAnalysisDTO; 5 6 import com.bazi.fullystocked.Models.Exceptions.InvalidArgumentsException; 6 7 import com.bazi.fullystocked.Models.SqlViews.ArticlesAtLocationReport; … … 11 12 import com.bazi.fullystocked.Repositories.SupplierSuppliesArticleRepository; 12 13 import com.bazi.fullystocked.Services.ArticlesService; 14 import org.hibernate.query.NativeQuery; 15 import org.hibernate.transform.Transformers; 13 16 import org.springframework.stereotype.Service; 14 17 18 import javax.persistence.EntityManager; 19 import javax.persistence.PersistenceContext; 15 20 import javax.transaction.Transactional; 16 21 import java.util.List; … … 23 28 private final SupplierSuppliesArticleRepository supplierSuppliesArticleRepository; 24 29 private final ArticlesAtLocationRepository articlesAtLocationRepository; 30 @PersistenceContext 31 private EntityManager entityManager; 25 32 26 33 public ArticlesServiceImpl(ArticlesRepository articlesRepository, CategoriesRepository categoriesRepository, SupplierSuppliesArticleRepository supplierSuppliesArticleRepository, ArticlesAtLocationRepository articlesAtLocationRepository) { … … 85 92 return articlesAtLocationRepository.findAllByArticleid(id); 86 93 } 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 } 87 129 } -
FullyStocked/src/main/java/com/bazi/fullystocked/Services/Implementations/LocationServiceImpl.java
r4b34fed r0328cb0 1 1 package com.bazi.fullystocked.Services.Implementations; 2 2 3 import com.bazi.fullystocked.Models.DTO.LocationAnalysisDTO; 4 import com.bazi.fullystocked.Models.DTO.TopUsersDTO; 3 5 import com.bazi.fullystocked.Models.Locations; 4 6 import com.bazi.fullystocked.Models.SqlViews.ArticlesAtLocationReport; … … 6 8 import com.bazi.fullystocked.Repositories.LocationsRepository; 7 9 import com.bazi.fullystocked.Services.LocationsService; 10 import org.hibernate.query.NativeQuery; 11 import org.hibernate.transform.Transformers; 8 12 import org.springframework.stereotype.Service; 9 13 14 import javax.persistence.EntityManager; 15 import javax.persistence.PersistenceContext; 10 16 import java.util.List; 11 17 … … 14 20 private final ArticlesAtLocationRepository articlesAtLocationRepository; 15 21 private final LocationsRepository locationsRepository; 22 @PersistenceContext 23 private EntityManager entityManager; 16 24 17 25 public LocationServiceImpl(ArticlesAtLocationRepository articlesAtLocationRepository, LocationsRepository locationsRepository) { … … 29 37 return locationsRepository.findAll(); 30 38 } 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 } 31 66 } -
FullyStocked/src/main/java/com/bazi/fullystocked/Services/Implementations/WorkersServiceImpl.java
r4b34fed r0328cb0 1 1 package com.bazi.fullystocked.Services.Implementations; 2 2 3 import com.bazi.fullystocked.Models.DTO.ArticleAnalysisDTO; 4 import com.bazi.fullystocked.Models.DTO.TopUsersDTO; 3 5 import com.bazi.fullystocked.Models.Exceptions.InvalidArgumentsException; 4 6 import com.bazi.fullystocked.Models.Locations; … … 7 9 import com.bazi.fullystocked.Repositories.WorkersRepository; 8 10 import com.bazi.fullystocked.Services.WorkersService; 11 import org.hibernate.query.NativeQuery; 12 import org.hibernate.transform.Transformers; 9 13 import org.springframework.stereotype.Service; 10 14 15 import javax.persistence.EntityManager; 16 import javax.persistence.PersistenceContext; 11 17 import java.util.List; 12 18 import java.util.Optional; … … 16 22 private final WorkersRepository workersRepository; 17 23 private final LocationsRepository locationsRepository; 24 @PersistenceContext 25 private EntityManager entityManager; 18 26 19 27 public WorkersServiceImpl(WorkersRepository workersRepository, LocationsRepository locationsRepository) { … … 34 42 return workersRepository.findAllByLocationIsNull(); 35 43 } 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 36 81 } -
FullyStocked/src/main/java/com/bazi/fullystocked/Services/LocationsService.java
r4b34fed r0328cb0 2 2 3 3 4 import com.bazi.fullystocked.Models.DTO.LocationAnalysisDTO; 4 5 import com.bazi.fullystocked.Models.Locations; 5 6 import com.bazi.fullystocked.Models.SqlViews.ArticlesAtLocationReport; … … 10 11 List<ArticlesAtLocationReport> findAllArticlesAtLocation(Integer locationid); 11 12 List<Locations> findAll(); 13 List<LocationAnalysisDTO> getLocationAnalysis(); 12 14 } -
FullyStocked/src/main/java/com/bazi/fullystocked/Services/WorkersService.java
r4b34fed r0328cb0 1 1 package com.bazi.fullystocked.Services; 2 2 3 import com.bazi.fullystocked.Models.DTO.ArticleAnalysisDTO; 4 import com.bazi.fullystocked.Models.DTO.TopUsersDTO; 3 5 import com.bazi.fullystocked.Models.Workers; 4 6 import java.util.*; … … 7 9 Optional<Workers> assignLocation(Integer workerId, Integer locationId); 8 10 List<Workers> findAllWithNoLocation(); 11 List<TopUsersDTO> findAllTopUsers(); 9 12 } -
FullyStocked/src/main/java/com/bazi/fullystocked/Web/Controller/ManagerController.java
r4b34fed r0328cb0 1 1 package com.bazi.fullystocked.Web.Controller; 2 2 3 import com.bazi.fullystocked.Services.ArticlesService; 3 4 import com.bazi.fullystocked.Services.LocationsService; 4 5 import com.bazi.fullystocked.Services.WorkersService; … … 15 16 private final WorkersService workersService; 16 17 private final LocationsService locationsService; 18 private final ArticlesService articlesService; 17 19 18 public ManagerController(WorkersService workersService, LocationsService locationsService ) {20 public ManagerController(WorkersService workersService, LocationsService locationsService, ArticlesService articlesService) { 19 21 this.workersService = workersService; 20 22 this.locationsService = locationsService; 23 this.articlesService = articlesService; 21 24 } 22 25 … … 47 50 } 48 51 } 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 } 49 70 }
Note:
See TracChangeset
for help on using the changeset viewer.