| 1 | package com.example.demo.repository;
|
|---|
| 2 |
|
|---|
| 3 | import com.example.demo.model.Client.Client;
|
|---|
| 4 | import com.example.demo.model.Client.ClientCompositeKey;
|
|---|
| 5 | import com.example.demo.model.PaymentType;
|
|---|
| 6 | import com.example.demo.model.Reservation.Reservations;
|
|---|
| 7 | import org.springframework.data.jpa.repository.JpaRepository;
|
|---|
| 8 | import org.springframework.data.jpa.repository.Query;
|
|---|
| 9 |
|
|---|
| 10 | import java.util.List;
|
|---|
| 11 | import java.util.Optional;
|
|---|
| 12 |
|
|---|
| 13 | public interface ClientRepository extends JpaRepository<Client, ClientCompositeKey> {
|
|---|
| 14 |
|
|---|
| 15 | @Query(value="select * from Client where user_id=?",nativeQuery = true)
|
|---|
| 16 | Optional<Client> findClient(Integer user_id);
|
|---|
| 17 |
|
|---|
| 18 | @Query(value="select * from Client ", nativeQuery = true)
|
|---|
| 19 | public Optional<Client> findAllClients();
|
|---|
| 20 |
|
|---|
| 21 | @Query(value=" select c.user_id,sum(mp.projection_price) as vkupna_potroshuvacka,u.user_name,u.user_surname from users as u\n" +
|
|---|
| 22 | " join client as c on c.user_id = u.user_id\n" +
|
|---|
| 23 | " join reservation as r on r.user_id = c.user_id\n" +
|
|---|
| 24 | " and r.reservation_date between now() - interval '1 year' and now()\n" +
|
|---|
| 25 | " join movieprojection as mp on mp.projection_id = r.projection_id\n" +
|
|---|
| 26 | " where mp.projection_id = ?\n" +
|
|---|
| 27 | " group by c.user_id,u.user_name,u.user_surname\n" +
|
|---|
| 28 | " order by vkupna_potroshuvacka desc\n" +
|
|---|
| 29 | " limit 1;;",nativeQuery = true)
|
|---|
| 30 | public List<Client> findBestClient(Integer projection_id);
|
|---|
| 31 |
|
|---|
| 32 |
|
|---|
| 33 | @Query(value="select distinct u.user_id,c.client_id, u.user_name,u.user_surname,\n" +
|
|---|
| 34 | " case when tabela.quarter=1 then tabela.broj_kupeni else 0 end as prv_kvartal_kupeni,\n" +
|
|---|
| 35 | " \tcase when tabela.quarter=2 then tabela.broj_kupeni else 0 end as vtor_kvartal_kupeni,\n" +
|
|---|
| 36 | " case when tabela.quarter=3 then tabela.broj_kupeni else 0 end as tret_kvartal_kupeni,\n" +
|
|---|
| 37 | " case when tabela.quarter=4 then tabela.broj_kupeni else 0 end as cetvrt_kvartal_kupeni,\n" +
|
|---|
| 38 | " -------------------------------------------------------------------------------\n" +
|
|---|
| 39 | " tabela.suma as sum_quarter\n" +
|
|---|
| 40 | " \n" +
|
|---|
| 41 | " \n" +
|
|---|
| 42 | " \tfrom reservation as r\n" +
|
|---|
| 43 | " \tjoin client as c on c.user_id = r.user_id\n" +
|
|---|
| 44 | " \tjoin movieprojection as mp on mp.projection_id = r.projection_id \n" +
|
|---|
| 45 | " \tjoin users as u on u.user_id = c.user_id\n" +
|
|---|
| 46 | " \tjoin(\n" +
|
|---|
| 47 | " \tselect c2.user_id,extract(quarter from r2.reservation_date) as quarter,sum(mp.projection_price) as suma,\n" +
|
|---|
| 48 | " \tcount(r2.reservation_id) as broj_kupeni\n" +
|
|---|
| 49 | " \tfrom reservation as r2\n" +
|
|---|
| 50 | " \tjoin movieprojection as mp on mp.projection_id = r2.projection_id\n" +
|
|---|
| 51 | " \tjoin client as c2 on c2.user_id = r2.user_id\n" +
|
|---|
| 52 | " \tgroup by 1,2\n" +
|
|---|
| 53 | " \t) as tabela on c.user_id = tabela.user_id\n" +
|
|---|
| 54 | " \torder by u.user_name",nativeQuery = true)
|
|---|
| 55 | List<Client> findByUsersStats();
|
|---|
| 56 | } |
|---|
