Changes between Version 12 and Version 13 of AdvancedReports

Timestamp:

01/06/23 04:22:18 (3 years ago)

Author:

201094

Comment:

--

AdvancedReports

@@ -162 +162 @@
 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 
-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  
+        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,f.id_film 
-
+group by c.id_cinema,f.id_film
 }}}
 === Извештај за секој клиент, за секое кино, колку карти има купено во секое кино, колку пари има потрошено во секое кино, и колку кодови за попуст имаат искористено во секое кино === 
@@ -179 +178 @@
 select c.id_customer, c2.id_cinema ,
 coalesce((
-select count(distinct t1.id_ticket) from tickets t1
-join projections p on p.id_projection=t1.id_projection 
-join projection_is_played_in_room pipir on pipir.id_projection=p.id_projection 
-join projection_rooms pr on pr.id_room=pipir.id_room 
-join cinemas cin on cin.id_cinema=pr.id_cinema
-where cin.id_cinema=c2.id_cinema and t1.id_customer=c.id_customer)
+        select count(distinct t1.id_ticket) from tickets t1
+        join projections p on p.id_projection=t1.id_projection 
+        join projection_is_played_in_room pipir on pipir.id_projection=p.id_projection 
+        join projection_rooms pr on pr.id_room=pipir.id_room 
+        join cinemas cin on cin.id_cinema=pr.id_cinema
+        where cin.id_cinema=c2.id_cinema and t1.id_customer=c.id_customer)
 ,0),
 coalesce((
-select sum(t1.price) from tickets t1
-join projections p on p.id_projection=t1.id_projection 
-join projection_is_played_in_room pipir on pipir.id_projection=p.id_projection 
-join projection_rooms pr on pr.id_room=pipir.id_room 
-join cinemas cin on cin.id_cinema=pr.id_cinema
-where cin.id_cinema=c2.id_cinema and t1.id_customer=c.id_customer)
+        select sum(t1.price) from tickets t1
+        join projections p on p.id_projection=t1.id_projection 
+        join projection_is_played_in_room pipir on pipir.id_projection=p.id_projection 
+        join projection_rooms pr on pr.id_room=pipir.id_room 
+        join cinemas cin on cin.id_cinema=pr.id_cinema
+        where cin.id_cinema=c2.id_cinema and t1.id_customer=c.id_customer)
 ,0),
 coalesce((
-select count(distinct d.id_discount) from discounts d 
-join tickets t1  on t1.id_discount = d.id_discount 
-join projections p on p.id_projection=t1.id_projection 
-join projection_is_played_in_room pipir on pipir.id_projection=p.id_projection 
-join projection_rooms pr on pr.id_room=pipir.id_room 
-join cinemas cin on cin.id_cinema=pr.id_cinema
-where cin.id_cinema=c2.id_cinema and t1.id_customer=c.id_customer
+        select count(distinct d.id_discount) from discounts d 
+        join tickets t1  on t1.id_discount = d.id_discount 
+        join projections p on p.id_projection=t1.id_projection 
+        join projection_is_played_in_room pipir on pipir.id_projection=p.id_projection 
+        join projection_rooms pr on pr.id_room=pipir.id_room 
+        join cinemas cin on cin.id_cinema=pr.id_cinema
+        where cin.id_cinema=c2.id_cinema and t1.id_customer=c.id_customer
 ),0)
 from customers c 
@@ -212 +211 @@
 group by c.id_customer,c2.id_cinema 
 order by c.id_customer
+
 }}}
 
@@ -218 +218 @@
 select f.id_film,
 coalesce((
-select count(distinct t.id_ticket) from tickets t
-left join projections p on p.id_projection=t.id_projection 
-left join films f1 on f1.id_film=p.id_film 
-where f.id_film=f1.id_film 
-),0),
-coalesce((
-select sum(t.price) from tickets t
-left join projections p on p.id_projection=t.id_projection 
-left join films f1 on f1.id_film=p.id_film 
-where f.id_film=f1.id_film 
-),0),
-coalesce((
-select count(distinct c.id_customer) from customers c
-left join tickets t2 on t2.id_customer=c.id_customer 
-left join projections p on p.id_projection=t2.id_projection 
-left join films f1 on f1.id_film=p.id_film 
-where f.id_film=f1.id_film 
-),0),
-coalesce((
-select count(crf.rating) from customer_rates_film crf 
-left join films f2 on f2.id_film=crf.id_film 
-where f2.id_film=f.id_film
+        select count(distinct t.id_ticket) from tickets t
+        left join projections p on p.id_projection=t.id_projection 
+        left join films f1 on f1.id_film=p.id_film 
+        where f.id_film=f1.id_film 
+),0),
+coalesce((
+        select sum(t.price) from tickets t
+        left join projections p on p.id_projection=t.id_projection 
+        left join films f1 on f1.id_film=p.id_film 
+        where f.id_film=f1.id_film 
+),0),
+coalesce((
+        select count(distinct c.id_customer) from customers c
+        left join tickets t2 on t2.id_customer=c.id_customer 
+        left join projections p on p.id_projection=t2.id_projection 
+        left join films f1 on f1.id_film=p.id_film 
+        where f.id_film=f1.id_film 
+),0),
+coalesce((
+        select count(crf.rating) from customer_rates_film crf 
+        left join films f2 on f2.id_film=crf.id_film 
+        where f2.id_film=f.id_film
 ),0)
 from films f 
@@ -248 +248 @@
 select c.id_cinema, 
 coalesce((
-select count(distinct w.id_worker) from workers w
-left join cinemas c1 on c1.id_cinema=w.id_cinema 
-where c1.id_cinema=c.id_cinema
-),0),
-coalesce((
-select sum(s.sum) from salaries s 
-left join workers w2 on s.id_worker=w2.id_worker
-left join cinemas c1 on c1.id_cinema=w2.id_cinema 
-where c1.id_cinema=c.id_cinema and s.date_to is null
+        select count(distinct w.id_worker) from workers w
+        left join cinemas c1 on c1.id_cinema=w.id_cinema 
+        where c1.id_cinema=c.id_cinema
+),0),
+coalesce((
+        select sum(s.sum) from salaries s 
+        left join workers w2 on s.id_worker=w2.id_worker
+        left join cinemas c1 on c1.id_cinema=w2.id_cinema 
+        where c1.id_cinema=c.id_cinema and s.date_to is null
 ),0)
-from cinemas c 
+from cinemas c  
 }}}