Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

01/05/23 03:43:12 (3 years ago)

Author:

201094

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи од базата (SQL и складирани процедури) =
+
+=== Извештај за сите кина, на месечно ниво, колку карти се пордадени и вкупниот профит во предходната година ===
+{{{#!sql
+select c.id_cinema,c.name,
+coalesce(prv_mesec.karti, 0) as prvi_mesec,
+coalesce(vtor_mesec.karti, 0) as vtori_mesec,
+coalesce(tret_mesec.karti, 0) as tretti_mesec,
+coalesce(cetvrt_mesec.karti,0) as cetvrti_mesec,
+coalesce(pet_mesec.karti,0) as petti_mesec,
+coalesce(sest_mesec.karti,0) as sesti_mesec,
+coalesce(sedum_mesec.karti, 0)  as sedmi_mesec,
+coalesce(osum_mesec.karti,0)  as osmi_mesec,
+coalesce(devet_mesec.karti,0) as devetti_mesec,
+coalesce(deset_mesec.karti, 0) as desetti_mesec,
+coalesce(edinaeset_mesec.karti11,0) as edinaesti_mesec,
+coalesce(dvanaeset_mesec.karti,0) as dvanaesti_mesec,
+sum(t.price) as vkupen_promet
+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 tickets t on t.id_projection=p.id_projection 
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=1
+        group by c2.id_cinema 
+        
+) as prv_mesec on prv_mesec.id=c.id_cinema  
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=2
+        group by c2.id_cinema 
+        
+) as vtor_mesec on vtor_mesec.id=c.id_cinema  
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=3
+        group by c2.id_cinema 
+        
+) as tret_mesec on tret_mesec.id=c.id_cinema  
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=4
+        group by c2.id_cinema 
+        
+) as cetvrt_mesec on cetvrt_mesec.id=c.id_cinema  
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=5
+        group by c2.id_cinema 
+        
+) as pet_mesec on pet_mesec.id=c.id_cinema  
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=6
+        group by c2.id_cinema 
+        
+) as sest_mesec on sest_mesec.id=c.id_cinema  
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=7
+        group by c2.id_cinema 
+        
+) as sedum_mesec on sedum_mesec.id=c.id_cinema  
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=8
+        group by c2.id_cinema 
+        
+) as osum_mesec on osum_mesec.id=c.id_cinema  
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=9
+        group by c2.id_cinema 
+        
+) as devet_mesec on devet_mesec.id=c.id_cinema  
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=10
+        group by c2.id_cinema 
+        
+) as deset_mesec on deset_mesec.id=c.id_cinema  
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti11,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=11
+        group by c2.id_cinema 
+        
+) as edinaeset_mesec on edinaeset_mesec.id=c.id_cinema  
+left join (
+        select distinct(c2.id_cinema) as id,count(distinct t3.id_ticket) as karti,sum(t3.price) as sum1 from tickets t3
+        join projections p1 on t3.id_projection = p1.id_projection 
+        join projection_is_played_in_room pipir2 on pipir2.id_projection=p1.id_projection 
+        join projection_rooms pr2 on pr2.id_room=pipir2.id_room 
+        join cinemas c2 on c2.id_cinema=pr2.id_cinema 
+        where extract(month from t3.date_reserved)=12
+        group by c2.id_cinema 
+        
+) as dvanaeset_mesec on dvanaeset_mesec.id=c.id_cinema 
+where extract(year from t.date_reserved)=extract(year from now()-interval'1 year')
+group by c.id_cinema,prv_mesec.karti,vtor_mesec.karti,tret_mesec.karti,cetvrt_mesec.karti,
+pet_mesec.karti,sest_mesec.karti,sedum_mesec.karti,osum_mesec.karti,devet_mesec.karti,deset_mesec.karti,
+edinaeset_mesec.karti11,dvanaeset_mesec.karti
+order by c.id_cinema
+
+}}}
+=== Извештај за успешноста на еден доставувач. Се прикажуваат информации за доставувачот, број на нарачки што ги доставил и вкупна вредност на сите нарачки што ги доставил ===
+
+
+=== Извештај за сеуште недоставените нарачки, од кој дел во која количина ни треба, колку од тој дел имаме во магацин и дали е потребно да се нарачаат за во магацин ===
+===== Се пресметуваат колку количина од деловите имало во завршените нарачки во последните 3 месеци и се додава моменталната количина во нарачки што се уште не 
+=== Извештај за сите сервиси,број на критики кои ги добиле и нивниот просечен рејтинг ===
+