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


== Вкупен број на резервации за храна во текот на претходните 2 месеци

{{{#!sql
select count(distinct rfpm.reservation_id) as broj_rezervacii_na_hrana
from reservation_for_prepared_meal rfpm 
join prepared_meal pm  on
rfpm.prepared_meal_id = pm.prepared_meal_id 
join reservation r on
rfpm.reservation_id  = r.reservation_id 
and 
start_date between now() - interval '2 months' and now()
}}}

== Вкупен број на настани по месец, вкупен број на гости на настаните, просечна цена на настан по гостин и вкупен промет од сите настани

{{{#!sql
select   extract(month from start_date) as months,
		 count(e.event_id) as num_events,  
         sum(e.number_of_interested_guests) as total_guests, 
         avg(e.price) as avg_price,
         sum(e.price * e.number_of_interested_guests) as sales
from reservation_for_events rfe 
join reservation r on
rfe.reservation_id = r.reservation_id
join events e on
rfe.event_id  = e.event_id 
group by months

}}}

== Вкупен број на активности по календарска година, вкупен број на гости на активностите, просечна цена на активност по гостин и вкупен промет од сите активности

{{{#!sql
select   extract(year from start_date) as years,
		 count(a.activity_id) as num_activities,  
         sum(a.number_of_interested_guests) as total_guests, 
         avg(a.price) as avg_price,
         sum(a.price * a.number_of_interested_guests) as sales
from reservation_for_activity rfa 
join reservation r on
rfa.reservation_id = r.reservation_id
join activities a  on
rfa.activity_id  = a.activity_id
group by years
}}}

== Просечна плата на секој тип на вработени во Вила Дихово

{{{#!sql
select 
	(
	  select count(w.user_id)
	  from waiter w
	)as waiters_in_villa_dihovo,
	(
	  select avg(w.salary)
	  from waiter w
	)as average_salary_waiter,
	(
	  select count(r.user_id) 
	  from receptionist r
	)as receptionists_in_villa_dihovo,
	(
	  select avg(r.salary)
	  from receptionist r 
	)as average_salary_receptionist,
	(
	  select count(c.user_id)
	  from chef c
	) as chefs_in_villa_dihovo,
	(
	  select avg(c.salary)
	  from chef c
	)as average_salary_chef

from user_table ut
group by 1,2,3;
}}}


== Нарачки за храна кој ги има направено еден корисник и вкупна сума на плаќање 

{{{#!sql
select pm.user_id,
       ut.full_name,
       count(ut.full_name) as broj_naracki_hrana, 
       sum(rfpm.quantity * m.price) as total_sum
from prepared_meal pm 
join user_table ut on
pm.user_id  = ut.user_id 
join meal m on
pm.meal_id  = m.meal_id 
join reservation_for_prepared_meal rfpm on
rfpm.prepared_meal_id  = pm.prepared_meal_id 
join reservation r on
rfpm.reservation_id  = r.reservation_id 
group by 1,2
}}}

== Одржана активност за која што има најголема заинтересираност во последната година


{{{#!sql
select a.activity_type as aktivnost,
       sum(a.number_of_interested_guests) as broj_na_zainteresirani_gosti
from activities a  
join reservation_for_activity rfa 
on a.activity_id  = rfa.activity_id 
join reservation r  
on r.reservation_id = rfa.reservation_id 
where r.start_date  between  now() - interval '1 year' and now()
group by a.activity_type 
order by count(a.number_of_interested_guests) desc
limit 1;
}}}

== Извештај за преглед на популарен тип на соба

{{{#!sql
select distinct r.room_type,
 (
 	select count(r2.reservation_id)
 	where r.room_type = 'Single'
 ) as single_room_reservation,
 (
 	select count(r2.reservation_id)
 	where r.room_type = 'Double'
 )as double_room_reservation
from rooms r
join reservation r2 
on r.room_id  = r2.room_id 
group by 1;
}}}

== Преглед на бројот на резервации по категории за секој корисник

select ut.full_name ,
       (
       		select  count(rfa.reservation_id)
       		from reservation_for_activity rfa
       )as number_reservations_for_activity,
	   (
	   		select count(rfe.reservation_id)
	   		from reservation_for_events rfe
	   )as number_reservations_for_events,
	   (
	   		select count(rfpm.reservation_id)
	   		from reservation_for_prepared_meal rfpm 
	   )as number_reservations_for_meal,
	   (
	   		select count(rfs.reservation_id)
	   		from reservation_for_service rfs 
	   )as number_reservations_for_service
from user_table ut
join guests g 
on ut.user_id  = g.user_id 
join guests_make_reservation gmr 
on g.user_id  = gmr.user_id 
join reservation r
on gmr.reservation_id = r.reservation_id
group by 1;




== Број на соби во секоја вила

{{{#!sql
select v.name as villa_name ,
       count(r.room_id) as number_of_rooms
    from villa v
    join rooms r on
    r.villa_id  = v.villa_id 
    group by villa_name 
    order by villa_name
}}}