Changes between Version 11 and Version 12 of AdvancedReports

Timestamp:

01/06/23 04:15:52 (2 years ago)

Author:

201094

Comment:

--

AdvancedReports

@@ -153 +153 @@
 {{{#!sql
 
-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
+select c.id_cinema as id1,max(sum_cena.sum1) as sumMaks,f.id_film, f."name" 
 from cinemas c 
+join projection_rooms pr on pr.id_cinema=c.id_cinema
+join projection_is_played_in_room pipir on pipir.id_room=pr.id_room 
+join projections p on p.id_projection = pipir.id_projection
 join cinema_plays_film cpf on cpf.id_cinema=c.id_cinema 
-join films f on f.id_film=cpf.id_film 
-join projections p on p.id_film=f.id_film
+join films f on f.id_film=cpf.id_film and f.id_film=p.id_film 
 join tickets t on t.id_projection=p.id_projection and t.date_reserved between now()-interval'3 months' and now()
 join (
-select distinct(c2.id_cinema) as id,f2.id_film as film,sum(t1.price) as sum1 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 
+select c.id_cinema as id,f.id_film as film,sum(t.price) as sum1  from cinemas c
+join projection_rooms pr on pr.id_cinema=c.id_cinema 
+join projection_is_played_in_room pipir on pipir.id_room=pr.id_room 
+join projections p on p.id_projection=pipir.id_projection 
+join films f on f.id_film=p.id_film 
+join tickets t on t.id_projection=p.id_projection 
+where t.date_reserved between now()-interval'3 months' and now()
+group by c.id_cinema,f.id_film  
 ) as sum_cena on sum_cena.id=c.id_cinema 
-group 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 
+group by c.id_cinema,f.id_film 
 
 }}}