Changes between Version 9 and Version 10 of AdvancedReports

Timestamp:

01/05/23 10:52:40 (2 years ago)

Author:

201094

Comment:

--

AdvancedReports

@@ -151 +151 @@
 === Извештај за секое кино, кој филм има направено најмногу профит во последните 3 месеци ===
 {{{#!sql
-select c.id_cinema,c.name,
-max(sum_cena.sum1) as m
+select c3.id_cinema,cpf3.id_film from cinemas c3 
+join cinema_plays_film cpf3 on cpf3.id_cinema=c3.id_cinema 
+join (
+select c.id_cinema as id1,max(sum_cena.sum1) as sumMaks
 from cinemas c 
 join cinema_plays_film cpf on cpf.id_cinema=c.id_cinema 
@@ -166 +168 @@
 where t1.date_reserved between now()-interval'3 months' and now()
 group by c2.id_cinema,f2.id_film 
-) 
-as sum_cena on sum_cena.id=c.id_cinema 
+) as sum_cena on sum_cena.id=c.id_cinema 
 group by c.id_cinema
-order by c.id_cinema 
+) as maks on maks.id1=c3.id_cinema 
+join (
+select distinct(c2.id_cinema) as id,f2.id_film as film,sum(t1.price) as sum2 from tickets t1
+join projections p2 on p2.id_projection=t1.id_projection
+join films f2 on f2.id_film = p2.id_film 
+join cinema_plays_film cpf2 on cpf2.id_film=f2.id_film 
+join cinemas c2 on c2.id_cinema=cpf2.id_cinema 
+where t1.date_reserved between now()-interval'3 months' and now()
+group by c2.id_cinema,f2.id_film 
+
+) as counts on counts.id=c3.id_cinema and counts.film=cpf3.id_film
+where counts.sum2=maks.sumMaks
+group by c3.id_cinema,cpf3.id_film
+order by c3.id_cinema 
+
 }}}
 === Извештај за секој клиент, за секое кино, колку карти има купено во секое кино, колку пари има потрошено во секое кино, и колку кодови за попуст имаат искористено во секое кино ===