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