1 | package project.fmo.app.projcetfmo.Repository;
|
---|
2 |
|
---|
3 | import org.springframework.data.jpa.repository.JpaRepository;
|
---|
4 | import org.springframework.data.jpa.repository.Query;
|
---|
5 | import project.fmo.app.projcetfmo.Model.Korisnik;
|
---|
6 |
|
---|
7 | import java.util.List;
|
---|
8 |
|
---|
9 | public interface KorisnikRepository extends JpaRepository<Korisnik, Integer> {
|
---|
10 | Korisnik findByImePrezime(String s);
|
---|
11 | Korisnik findByImePrezimeAndPasswordKorisnik(String s, String p);
|
---|
12 |
|
---|
13 | @Query(value ="select k.id_korisnik,k.ime_prezime,\n" +
|
---|
14 | "\n" +
|
---|
15 | "(select count(n) from project.naracka as n\n" +
|
---|
16 | "where n.id_korisnik = k.id_korisnik)\n" +
|
---|
17 | "as vkupno_naracki, \n" +
|
---|
18 | "\n" +
|
---|
19 | "(select avg(vkupno_suma) from project.naracka )\n" +
|
---|
20 | "as prosecna_suma_na_naracka_za_site_kupuvaci,\n" +
|
---|
21 | "\n" +
|
---|
22 | "(select sum(n3.vkupno_suma) from project.naracka as n3 \n" +
|
---|
23 | "where k.id_korisnik = n3.id_korisnik) \n" +
|
---|
24 | "as vkupno_suma_od_site_naracki_na_kupuvacot,\n" +
|
---|
25 | "\n" +
|
---|
26 | "(select count(n2) from project.naracka as n2\n" +
|
---|
27 | "where k.id_korisnik = n2.id_korisnik \n" +
|
---|
28 | "and n2.datum between now() - interval '1year' and now() \n" +
|
---|
29 | "and n2.vkupno_suma > (select avg(vkupno_suma) from project.naracka) )\n" +
|
---|
30 | "as vkupno_naracki_nad_prosek_na_korisnikot,\n" +
|
---|
31 | "\n" +
|
---|
32 | "(select sum(n4.vkupno_suma) from project.naracka as n4 \n" +
|
---|
33 | "where k.id_korisnik = n4.id_korisnik \n" +
|
---|
34 | "and n4.datum between now() - interval '1year' and now() \n" +
|
---|
35 | "and n4.vkupno_suma > (select avg(vkupno_suma) from project.naracka) ) \n" +
|
---|
36 | "as vkupno_suma_na_naracki_nad_prosek_za_korisnikot\n" +
|
---|
37 | "\n" +
|
---|
38 | "from project.korisnik as k \n" +
|
---|
39 | "inner join project.kupuvac as k2 on k.id_korisnik = k2.id_korisnik \n" +
|
---|
40 | "group by k.ime_prezime,k.id_korisnik ;" ,
|
---|
41 | nativeQuery = true)
|
---|
42 | List<Object> statistics2();
|
---|
43 | }
|
---|