Changes between Version 9 and Version 10 of AdvancedReports


Ignore:
Timestamp:
01/05/23 18:30:53 (3 years ago)
Author:
181213
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v9 v10  
    11== Напредни извештаи од базата (SQL и складирани процедури)
    22
    3 - Ресторан со најмногу нарачки во последните 3 месеци
     3==  Ресторан со најмногу нарачки во последните 3 месеци.
    44{{{#!sql
    55SELECT q1.restoran_ime as restoran, q1.naracki as broj_na_naracki
    6 FROM(
    7 SELECT r.restoran_ime, COUNT(n.naracka_id) as naracki
    8 FROM restoran as r
    9 JOIN meni as m on r.restoran_id=m.restoran_id
    10 JOIN obrok as o on m.meni_id=o.meni_id
    11 JOIN se_sostoi_od as sso on o.obrok_id=sso.obrok_id
    12 JOIN naracka as n on sso.naracka_id=n.naracka_id
    13 where n.naracana_na between (now()-interval '3 months') and now()
    14 GROUP BY r.restoran_ime) q1
     6        FROM(
     7        SELECT r.restoran_ime, COUNT(n.naracka_id) as naracki
     8        FROM restoran as r
     9        JOIN meni as m on r.restoran_id=m.restoran_id
     10        JOIN obrok as o on m.meni_id=o.meni_id
     11        JOIN se_sostoi_od as sso on o.obrok_id=sso.obrok_id
     12        JOIN naracka as n on sso.naracka_id=n.naracka_id
     13        where n.naracana_na between (now()-interval '3 months') and now()
     14        GROUP BY r.restoran_ime) q1
    1515WHERE q1.naracki=(SELECT MAX(naracki)FROM (
    16 SELECT r.restoran_ime, COUNT (n.naracka_id) as naracki
    17 FROM restoran as r
    18 JOIN meni as m on r.restoran_id=m.restoran_id
    19 JOIN obrok as o on m.meni_id=o.meni_id
    20 JOIN se_sostoi_od as sso on o.obrok_id=sso.obrok_id
    21 JOIN naracka as n on sso.naracka_id=n.naracka_id
    22 where n.naracana_na between (now()-interval '3 months') and now()
    23 GROUP BY r.restoran_ime) q2
     16        SELECT r.restoran_ime, COUNT (n.naracka_id) as naracki
     17        FROM restoran as r
     18        JOIN meni as m on r.restoran_id=m.restoran_id
     19        JOIN obrok as o on m.meni_id=o.meni_id
     20        JOIN se_sostoi_od as sso on o.obrok_id=sso.obrok_id
     21        JOIN naracka as n on sso.naracka_id=n.naracka_id
     22        where n.naracana_na between (now()-interval '3 months') and now()
     23        GROUP BY r.restoran_ime) q2
    2424)
    2525}}}
    26 - Ресторан со најголем профит
     26== Ресторан со најголем профит.
    2727{{{#!sql
    2828select rr.restoran_ime as restoran, q1.cena_vkupna as profit from restoran rr join(select r.restoran_id,sum(sso.cena * sso.kolicina) as cena_vkupna
    29 from restoran r
    30 join meni as m on r.restoran_id=m.restoran_id
    31 join obrok as o on m.meni_id=o.meni_id
    32 join se_sostoi_od as sso on o.obrok_id=sso.obrok_id
    33 join naracka as n on sso.naracka_id = n.naracka_id
    34 group by r.restoran_id)q1
     29                from restoran r
     30                join meni as m on r.restoran_id=m.restoran_id
     31                join obrok as o on m.meni_id=o.meni_id
     32                join se_sostoi_od as sso on o.obrok_id=sso.obrok_id
     33                join naracka as n on sso.naracka_id = n.naracka_id
     34                group by r.restoran_id)q1
    3535on q1.restoran_id=rr.restoran_id
    3636where cena_vkupna=(select max(cena_vkupna) from(
    37 select r.restoran_id,sum(sso.cena * sso.kolicina) as cena_vkupna
     37                select r.restoran_id,sum(sso.cena * sso.kolicina) as cena_vkupna
     38                from restoran r
     39                join meni as m on r.restoran_id=m.restoran_id
     40                join obrok as o on m.meni_id=o.meni_id
     41                join se_sostoi_od as sso on o.obrok_id=sso.obrok_id
     42                join naracka as n on sso.naracka_id = n.naracka_id
     43                group by r.restoran_id
     44                )q2)
     45}}}
     46== За секој ресторан колку оброци има на понуда, колку оброци има продадено, колку различни купувачи имаат нарачано од ресторанот,заработка на ресторанот и рејтинг на ресторанот.
     47{{{#!sql
     48SELECT r.restoran_ime,
     49coalesce((select count( distinct o.obrok_id)
     50                from obrok o
     51                left join meni m on o.meni_id=m.meni_id
     52                left join restoran r1 on m.restoran_id=r1.restoran_id
     53                where r.restoran_id=r1.restoran_id) ,0) as broj_obroci,
     54coalesce((select SUM( sso.kolicina)
     55                from se_sostoi_od sso
     56                left join obrok o on sso.obrok_id=o.obrok_id
     57                left join meni m on o.meni_id=m.meni_id
     58                left join restoran r1 on m.restoran_id=r1.restoran_id
     59                where r.restoran_id=r1.restoran_id) ,0) as broj_prodadeni_obroci,
     60coalesce((select COUNT(  distinct k.user_id )
     61                from kupuvac k
     62                left join naracka n on k.user_id=n.user_id_kupuvac
     63                left join se_sostoi_od sso on n.naracka_id=sso.naracka_id
     64                left join obrok o on sso.obrok_id=o.obrok_id
     65                left join meni m on o.meni_id=m.meni_id
     66                left join restoran r1 on m.restoran_id=r1.restoran_id
     67                where r.restoran_id=r1.restoran_id) ,0) as broj_kupuvaci,
     68coalesce((select SUM( sso.cena * sso.kolicina)
     69                from se_sostoi_od sso
     70                left join obrok o on sso.obrok_id=o.obrok_id
     71                left join meni m on o.meni_id=m.meni_id
     72                left join restoran r1 on m.restoran_id=r1.restoran_id
     73                where r.restoran_id=r1.restoran_id) ,0) as zarabotka,
     74r.restoran_rejting as rejting
    3875from restoran r
    39 join meni as m on r.restoran_id=m.restoran_id
    40 join obrok as o on m.meni_id=o.meni_id
    41 join se_sostoi_od as sso on o.obrok_id=sso.obrok_id
    42 join naracka as n on sso.naracka_id = n.naracka_id
    43 group by r.restoran_id
    44 )q2)
    4576}}}
    46 - Најнарачан производ по ресторан
     77== За секој купувач, за секој ресторан колку оброци има нарачано од секој ресторан, колку пари има потрошено во секој ресторан и колку пати има направено резервација во секој ресторан.
     78{{{#!sql
     79select k.user_ime as ime, r.restoran_ime as restoran,q1.broj_obroci,q1.plateno,q1.broj_rezervacii from korisnik k , restoran r,(
     80                select k.user_id , r.restoran_id,
     81                coalesce((select sum( sso.kolicina)
     82                                from kupuvac as ku
     83                                left join naracka as n on ku.user_id =n.user_id_kupuvac
     84                                left join se_sostoi_od as sso on n.naracka_id =sso.naracka_id
     85                                left join obrok as o on sso.obrok_id =o.obrok_id
     86                                left join meni as m on o.meni_id=m.meni_id
     87                                left join restoran as r1 on m.restoran_id =r1.restoran_id
     88                                where r.restoran_id=r1.restoran_id and k.user_id =ku.user_id  ) ,0) as broj_obroci,
     89                coalesce((select SUM( sso.cena * sso.kolicina)
     90                                from kupuvac as ku
     91                                left join naracka as n on ku.user_id =n.user_id_kupuvac
     92                                left join se_sostoi_od as sso on n.naracka_id =sso.naracka_id
     93                                left join obrok as o on sso.obrok_id =o.obrok_id
     94                                left join meni as m on o.meni_id=m.meni_id
     95                                left join restoran as r1 on m.restoran_id =r1.restoran_id
     96                                where r.restoran_id=r1.restoran_id and k.user_id =ku.user_id  ) ,0) as plateno,
     97                coalesce((select COUNT( re.rezervacija_id)
     98                                from kupuvac as ku
     99                                left join rezervacija re on ku.user_id=re.user_id
     100                                left join restoran as r1 on re.restoran_id =r1.restoran_id
     101                                where r.restoran_id=r1.restoran_id and k.user_id =ku.user_id  ) ,0) as broj_rezervacii
     102                from korisnik k
     103                left join kupuvac ku on k.user_id=ku.user_id
     104                left join rezervacija re ON k.user_id=re.user_id
     105                left join naracka n on k.user_id=n.user_id_kupuvac
     106                left join se_sostoi_od sso on n.naracka_id=sso.naracka_id
     107                left join obrok o on sso.obrok_id=o.obrok_id
     108                left join meni m on o.meni_id=m.meni_id
     109                left join restoran r on re.restoran_id=r.restoran_id or m.restoran_id=r.restoran_id
     110                group by k.user_id , r.restoran_id)q1
     111where q1.user_id=k.user_id and q1.restoran_id=r.restoran_id
     112}}}
     113== Најнарачан производ по ресторан.
    47114{{{#!sql
    48115select q1.restoran_ime as restoran, MAX(q1.obrok_ime) as obrok, MAX(q1.br_obroci) as broj_naracki FROM(
    49 SELECT r.restoran_ime, o.obrok_ime,  SUM(sso.kolicina) as br_obroci
    50 FROM restoran as r
    51 JOIN meni as m on r.restoran_id=m.restoran_id
    52 JOIN obrok as o on m.meni_id=o.meni_id
    53 JOIN se_sostoi_od as sso on o.obrok_id=sso.obrok_id
    54 JOIN naracka as n on sso.naracka_id=n.naracka_id
    55 GROUP BY r.restoran_ime, o.obrok_ime)q1
     116                SELECT r.restoran_ime, o.obrok_ime,  SUM(sso.kolicina) as br_obroci
     117                FROM restoran as r
     118                JOIN meni as m on r.restoran_id=m.restoran_id
     119                JOIN obrok as o on m.meni_id=o.meni_id
     120                JOIN se_sostoi_od as sso on o.obrok_id=sso.obrok_id
     121                JOIN naracka as n on sso.naracka_id=n.naracka_id
     122                GROUP BY r.restoran_ime, o.obrok_ime)q1
    56123group by restoran
    57124order by broj_naracki desc
    58125}}}
    59 - Кој корисник колку има платено во секој ресторан
     126== Кој корисник колку има платено во секој ресторан.
    60127{{{#!sql
    61128select k2.user_ime as ime, k2.user_prezime as prezime, r2.restoran_ime as restoran, q1.plateno as plateno from(
    62 select ku.user_id, r.restoran_id, sum(sso.cena * sso.kolicina) as plateno
    63 from korisnik k
    64 join kupuvac as ku on k.user_id =ku.user_id
    65 join naracka as n on ku.user_id =n.user_id_kupuvac
    66 join se_sostoi_od as sso on n.naracka_id =sso.naracka_id
    67 join obrok as o on sso.obrok_id =o.obrok_id
    68 join meni as m on o.meni_id=m.meni_id
    69 join restoran as r on m.restoran_id =r.restoran_id
    70 group by  ku.user_id ,r.restoran_id)q1 join korisnik k2 on q1.user_id=k2.user_id  join restoran r2 on q1.restoran_id=r2.restoran_id
     129                select ku.user_id, r.restoran_id, sum(sso.cena * sso.kolicina) as plateno
     130                from korisnik k
     131                join kupuvac as ku on k.user_id =ku.user_id
     132                join naracka as n on ku.user_id =n.user_id_kupuvac
     133                join se_sostoi_od as sso on n.naracka_id =sso.naracka_id
     134                join obrok as o on sso.obrok_id =o.obrok_id
     135                join meni as m on o.meni_id=m.meni_id
     136                join restoran as r on m.restoran_id =r.restoran_id
     137                group by  ku.user_id ,r.restoran_id)q1
     138join korisnik k2 on q1.user_id=k2.user_id  join restoran r2 on q1.restoran_id=r2.restoran_id
    71139order by ime
    72140}}}