Changes between Version 13 and Version 14 of AdvancedReports

Timestamp:

01/12/23 14:35:02 (2 years ago)

Author:

201094

Comment:

--

AdvancedReports

@@ -153 +153 @@
 {{{#!sql
 
-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 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 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 
+select c2.id_cinema,maks.sumMaks,sum_3.film3 from cinemas c2
+join projection_rooms pr2 on pr2.id_cinema=c2.id_cinema
+join projection_is_played_in_room pipir2 on pipir2.id_room=pr2.id_room 
+join projections p2 on p2.id_projection = pipir2.id_projection
+join films f2 on  f2.id_film=p2.id_film 
+join(
+        select c.id_cinema as id1,max(sum_cena.sum1) as sumMaks
+        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,f.id_film
+        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=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 c1.id_cinema as id,f1.id_film as film,sum(t1.price) as sum1  from cinemas c1
+        join projection_rooms pr1 on pr1.id_cinema=c1.id_cinema 
+        join projection_is_played_in_room pipir1 on pipir1.id_room=pr1.id_room 
+        join projections p1 on p1.id_projection=pipir1.id_projection 
+        join films f1 on f1.id_film=p1.id_film 
+        join tickets t1 on t1.id_projection=p1.id_projection 
+        where t1.date_reserved between now()-interval'3 months' and now()
+        group by c1.id_cinema,f1.id_film  
+        ) as sum_cena on sum_cena.id=c.id_cinema and sum_cena.film=f.id_film 
+        group by c.id_cinema
+) as maks on maks.id1=c2.id_cinema 
+join(
+        select c3.id_cinema as id3,f3.id_film as film3,sum(t3.price) as sum3  from cinemas c3
+        join projection_rooms pr3 on pr3.id_cinema=c3.id_cinema 
+        join projection_is_played_in_room pipir3 on pipir3.id_room=pr3.id_room 
+        join projections p3 on p3.id_projection=pipir3.id_projection 
+        join films f3 on f3.id_film=p3.id_film 
+        join tickets t3 on t3.id_projection=p3.id_projection 
+        where t3.date_reserved between now()-interval'3 months' and now()
+        group by c3.id_cinema,f3.id_film  
+) as sum_3 on sum_3.id3=c2.id_cinema and sum_3.film3=f2.id_film and sum_3.sum3=maks.sumMaks
+group by c2.id_cinema,maks.sumMaks,sum_3.film3
 }}}
 === Извештај за секој клиент, за секое кино, колку карти има купено во секое кино, колку пари има потрошено во секое кино, и колку кодови за попуст имаат искористено во секое кино ===