1 | package project.fmo.app.projcetfmo.Repository;
|
---|
2 |
|
---|
3 | import org.springframework.data.domain.Pageable;
|
---|
4 | import org.springframework.data.jpa.repository.JpaRepository;
|
---|
5 | import org.springframework.data.jpa.repository.Query;
|
---|
6 | import project.fmo.app.projcetfmo.Model.Produkt;
|
---|
7 |
|
---|
8 | import java.util.List;
|
---|
9 |
|
---|
10 | public interface ProduktRepository extends JpaRepository<Produkt, Integer> {
|
---|
11 | @Query(value = """
|
---|
12 | select p.id_produkt,p.ime,\s
|
---|
13 | count(nsp) as broj_na_naracki_vo_koi_e_prisuten_produktot,\s
|
---|
14 | sum(nsp.kolicina) as kolicina_prodadeno_od_produktot,\s
|
---|
15 |
|
---|
16 | (select avg(n4.vkupno_suma) from project.naracka as n4\s
|
---|
17 | join project.naracka_sodrzi_produkt nsp4 on n4.id_naracka = nsp4.id_naracka \s
|
---|
18 | where n4.id_naracka =nsp4.id_naracka and p.id_produkt = nsp4.id_produkt and n4.datum between now() - interval '3months'\s
|
---|
19 | and now())
|
---|
20 | as prosecna_suma_na_naracka_vo_koja_se_sodrzi_produktot,
|
---|
21 |
|
---|
22 | (select count(n2) from project.naracka as n2\040
|
---|
23 | join project.naracka_sodrzi_produkt nsp2 on n2.id_naracka = nsp2.id_naracka\s
|
---|
24 | where nsp2.id_produkt = p.id_produkt and n2.adresa like '/'\s
|
---|
25 | and n2.datum between now() - interval '3months' and now())
|
---|
26 | as broj_na_naracki_na_produktot_koi_korisnikot_sam_ke_gi_podigne,
|
---|
27 |
|
---|
28 | (select sum(nsp2.kolicina) from project.naracka as n2\s
|
---|
29 | join project.naracka_sodrzi_produkt nsp2 on n2.id_naracka = nsp2.id_naracka\s
|
---|
30 | where nsp2.id_produkt = p.id_produkt and n2.adresa like '/'\s
|
---|
31 | and n2.datum between now() - interval '3months' and now())\s
|
---|
32 | as kolicina_prodadena_na_produktot_bez_dostava,
|
---|
33 |
|
---|
34 | (select count(n3) from project.naracka as n3\s
|
---|
35 | join project.naracka_sodrzi_produkt nsp3 on n3.id_naracka = nsp3.id_naracka\s
|
---|
36 | where nsp3.id_produkt = p.id_produkt and n3.adresa not like '/' and n3.datum between now() - interval '3months' and now())
|
---|
37 | as broj_na_naracki_na_produktot_koi_ke_bidat_dostaveni_na_korisnikot,
|
---|
38 |
|
---|
39 | (select sum(nsp3.kolicina) from project.naracka as n3\s
|
---|
40 | join project.naracka_sodrzi_produkt nsp3 on n3.id_naracka = nsp3.id_naracka
|
---|
41 | where nsp3.id_produkt = p.id_produkt and n3.adresa not like '/' and n3.datum between now() - interval '3months' and now())
|
---|
42 | as kolicina_prodadena_na_produktot_preku_dostava
|
---|
43 |
|
---|
44 | from project.produkt as p
|
---|
45 | join project.naracka_sodrzi_produkt as nsp on p.id_produkt = nsp.id_produkt\s
|
---|
46 | join project.naracka as n on nsp.id_naracka = n.id_naracka\s
|
---|
47 | where n.datum between now() - interval '3months' and now()
|
---|
48 | group by p.ime,p.id_produkt;""",
|
---|
49 | nativeQuery = true)
|
---|
50 | List<Object> statistics1();
|
---|
51 |
|
---|
52 | }
|
---|