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 | } |
---|