= Напредни извештаи од базата (SQL и складирани процедури)

== Извештај за заработка на секоја фирма и генерален интерес за секое тромесечие од претходната година

{{{#!sql
select distinct f.firm_name,
    case when tabelaQuarter.quarter=1 then tabelaSuma.count else 0 end as prv_kvartal_suma,
    case when tabelaQuarter.quarter=2 then tabelaSuma.count  else 0 end as vtor_kvartal,
    case when tabelaQuarter.quarter=3 then tabelaSuma.count  else 0 end as tret_kvartal,
    case when tabelaQuarter.quarter=4 then tabelaSuma.count  else 0 end as cetvrt_kvartal,
       
    case when tabelaQuarter.quarter=1 then tabelaSuma.sum else 0 end as prv_kvartal_suma,
    case when tabelaQuarter.quarter=2 then tabelaSuma.sum  else 0 end as vtor_kvartal_suma,
    case when tabelaQuarter.quarter=3 then tabelaSuma.sum  else 0 end as tret_kvartal_suma,
    case when tabelaQuarter.quarter=4 then tabelaSuma.sum  else 0 end as cetvrt_kvartal_suma
       
       
       
    from firm as f
    join auditorium as a on a.firm_id = f.firm_id
    join gives as g on g.auditorium_id = a.auditorium_id
    join movieprojection as mp on mp.projection_id = g.projection_id
    join reservation as r on r.projection_id = mp.projection_id
    
    join(
    select distinct f.firm_name, extract(quarter from r.reservation_date) as quarter
    from firm as f
    join auditorium as a on a.firm_id = f.firm_id
    join gives as g on g.auditorium_id = a.auditorium_id
    join movieprojection as mp on mp.projection_id = g.projection_id
    join reservation as r on r.projection_id = mp.projection_id
   
    ) as tabelaQuarter on tabelaQuarter.firm_name = f.firm_name
    
    join(
    select distinct f.firm_id,f.firm_name , a.auditorium_name as sala,sum(mp.projection_price) ,count(distinct r.reservation_id ),mp.projection_price 
    from reservation as r
    join movieprojection as mp on mp.projection_id = r.projection_id
    join gives as g on g.projection_id = mp.projection_id
    join auditorium as a on a.auditorium_id = g.auditorium_id 
    join firm as f on f.firm_id = a.firm_id 
    group by 1,2,3,6
    order by sum desc
    ) as tabelaSuma on tabelaSuma.firm_id = f.firm_id
    where ((extract(year from now())::integer-1)=(extract(year from r.reservation_date)::integer))
}}}

== Извештај за прикажан интерес за секој филм , колку пати е резервирана проекција за тој филм во последни две недели  и просечна заработка од карти за таа проекција за тој филм
{{{#!sql 
   select distinct mp.projection_id,m.movie_name,vkupna_brojka.vkupno,prosek.prosek_zarabotka
from movieprojection as mp
join movie as m on mp.movie_id=m.movie_id

join(   select distinct r.reservation_id, count(r.projection_id) as vkupno
         from reservation as r
          join movieprojection as mp2 on r.projection_id=mp2.projection_id 
          join movie as m2 on mp2.movie_id=m2.movie_id
          where r.reservation_date between now() - interval '14 days' and now()
          group by 1
 )as vkupna_brojka on vkupna_brojka.vkupno is not null
 
 join(   select distinct r2.reservation_id,mp3.projection_id, avg(suma.sum1) as prosek_zarabotka
         from reservation as r2
          join movieprojection as mp3 on r2.projection_id=mp3.projection_id 
          
          join (select mp4.projection_id , sum(mp4.projection_price) as sum1
                 from movieprojection as mp4
                 group by 1
          )as suma on suma.sum1 is not null
          join movie as m3 on mp3.movie_id=m3.movie_id
          group by 1,2
          order by prosek_zarabotka desc
 )as prosek on prosek.prosek_zarabotka is not null
}}}

== Извештај за вкупно продадени карти по жанрови
{{{#!sql
 select g.genre_id,g.genre_name,
 case when tabela.prodadena_karta >=1 then tabela.prodadena_karta else 0 end as prodadeni_karti
 from genre as g
 join is_ as is1 on g.genre_id=is1.genre_id
 join movie as m on is1.movie_id=m.movie_id
 join movieprojection as mp on m.movie_id=mp.movie_id
 join reservation as r on mp.projection_id=r.projection_id
 join (
     select g2.genre_id,g2.genre_name,count(p1.payment_id) as prodadena_karta 
 from genre as g2
 join is_ as is2 on g2.genre_id=is2.genre_id
 join movie as m2 on is2.movie_id=m2.movie_id
 join movieprojection as mp2 on m2.movie_id=mp2.movie_id
 join reservation as r2 on mp2.projection_id=r2.projection_id
 join payment as p1 on r2.reservation_id=p1.reservation_id 
 group by 1,2
 
 )as tabela on g.genre_id=tabela.genre_id
}}}

== Прикажување на трите најрезервирани филмски жанрови според бројот на резервации на филмските проекции кои резервации се направени во последните 2 месеци гледано од моменталниот датум

{{{#!sql
select g.genre_id ,g.genre_name, count(r) as rezervacii from genre as g
     join is_ as i on i.genre_id = g.genre_id
     join movie as m on m.movie_id  = i.movie_id
     join movieprojection as mp on mp.movie_id = m.movie_id
     join reservation as r on r.projection_id = mp.projection_id
     and r.reservation_date between now() - interval '2 month' and now()
     group by g.genre_id
     order by rezervacii desc
     limit 3;
}}}
'''//Го селектираме и genre_id за да може Query-то да го употребиме во нашата Spring Boot Application//'''



== Извештај за клиент според тромесечие, купени карти и придонес

{{{#!sql
   select distinct u.user_name,u.user_surname,
    case when tabela.quarter=1 then tabela.broj_kupeni else 0 end as prv_kvartal,
   	case when tabela.quarter=2 then tabela.broj_kupeni  else 0 end as vtor_kvartal,
    case when tabela.quarter=3 then tabela.broj_kupeni  else 0 end as tret_kvartal,
    case when tabela.quarter=4 then tabela.broj_kupeni else 0 end as cetvrt_kvartal,
    -------------------------------------------------------------------------------
    case when tabela.quarter=1 then tabela.suma else 0 end as prv_kvartal_suma,
    case when tabela.quarter=2 then tabela.suma  else 0 end as vtor_kvartal_suma,
    case when tabela.quarter=3 then tabela.suma  else 0 end as tret_kvartal_suma,
    case when tabela.quarter=4 then tabela.suma else 0 end as cetvrt_kvartal_suma
    
    
 	from reservation as r
 	join client as c on c.user_id = r.user_id
 	join movieprojection as mp on mp.projection_id = r.projection_id 
 	join users as u on u.user_id = c.user_id
 	join(
 	select distinct c2.user_id,extract(quarter from r2.reservation_date) as quarter,sum(mp.projection_price) as suma,
 	count(r2.reservation_id) as broj_kupeni
 	from reservation as r2
 	join movieprojection as mp on mp.projection_id  = r2.projection_id
 	join client as c2 on c2.user_id = r2.user_id
 	group by 1,2
 	) as tabela on c.user_id = tabela.user_id
}}}


== Извештај за секоја сала,за секоја година со најмногу одржани филмски проекции и вкупен број на резервации за таа сала 

{{{#!sql
select distinct a.auditorium_name , q4.god as godina,a.auditorium_capacity,vkupna_brojka.vkupno,q4.broj_odrzani_filmovi as broj_odrzani_filmovi
 from 
 (
      select * from 
      (
          select q1.godina as god , max(q1.broj_odrzani_filmovi) as maksimum from 
          ( 
             select extract(year from r.reservation_date)as godina ,r.reservation_id as rez ,count(r.projection_id) as broj_odrzani_filmovi
             from reservation as r
             join seat as s2 on r.seat_id=s2.seat_id
             join auditorium as a2 on s2.auditorium_id=a2.auditorium_id
             join movieprojection as mp1 on r.projection_id=mp1.projection_id
             group by 1,2
          )q1
          group by godina
        
      )q2
      join 
      (
      select extract (year from r.reservation_date)as godina , r.reservation_id as rez,count(r.projection_id) as broj_odrzani_filmovi
      from reservation as r
             join seat as s3 on r.seat_id=s3.seat_id
             join auditorium as a3 on s3.auditorium_id=a3.auditorium_id
             join movieprojection as mp2 on r.projection_id=mp2.projection_id
             group by 1,2
      )q3
       on q2.god=q3.godina and q2.maksimum=q3.broj_odrzani_filmovi
 
 ) q4

 join( select distinct r3.reservation_id, count(r3.projection_id) as vkupno
         from reservation as r3
          join movieprojection as mp5 on r3.projection_id=mp5.projection_id 
          join movie as m4 on mp5.movie_id=m4.movie_id
          group by 1
 )as vkupna_brojka on vkupna_brojka.vkupno is not null

 join auditorium as a on a.auditorium_id=q4.rez
 order by godina desc 
}}}
'''//Преку овој извештај вработените може да проверат која
сала е најмногу оптеретена и да направат распределба на проекциите во однос на капацитетот на седишта што го нуди салата//'''