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