== Напредни извештаи од базата (SQL)

=== Пример извештаи
* Број на казнети патници според линија во изминатата година
* Мерење побарувачка на секоја линија во првиот квартал од оваа година
* Проток на патници според постојка
* Најпрометен период во денот (интервали од 1 час) според линија - од податоци од ланската година
* Период од денот во кој најмногу се купува билети (часовни и дневни)
* Период од денот во кој најмногу се пишуваат казни

==== 1. Број на казнети патници според линија во изминатата година

{{{#!div style="font-size: 80%"
  {{{#!sql
select li_ime, count(li_ime)
from linija l
left join instancanalinija as i on l.li_id = i.li_id
left join kontroli as ko on i.inl_id = ko.inl_id
left join kazna as ka on ko.kontrola_id = ka.kontrola_id
where extract(year from ka.kz_datum) = extract(year from (now() - interval'1 year'))
group by li_ime
order by count(li_ime) desc
  }}}
}}}

==== 2. Најпрометен период во денот (интервали од 1 час) според линија - од податоци од ланската година

{{{#!div style="font-size: 80%"
  {{{#!sql


select li_ime_given as li_ime, interval_1h , broj_pati
from(
	select li_ime as li_ime_given, interval_1h , count(li_ime||'-'||interval_1h) broj_pati
	from(
		select *, 
		(to_char(extract (hour from vozenje_start), '00') || ':00 -' || to_char(extract (hour from (vozenje_start + '1 hours'::interval))::int, '00') || ':00') as interval_1h
		from project.linija li 
		left join project.instancanalinija inl on li.li_id = inl.li_id 
		left join project.vozenje v on inl.inl_id = v.inl_id 
		where v.vozenje_start between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))
	)
	group by li_ime, interval_1h 
	order by li_ime, interval_1h
)
where broj_pati = (
	select max(broj_pati)
	from(
		select li_ime, interval_1h , count(li_ime||'-'||interval_1h) broj_pati
		from(
			select *, 
			(to_char(extract (hour from vozenje_start), '00') || ':00 -' || to_char(extract (hour from (vozenje_start + '1 hours'::interval))::int, '00') || ':00') as interval_1h
			from project.linija li 
			left join project.instancanalinija inl on li.li_id = inl.li_id 
			left join project.vozenje v on inl.inl_id = v.inl_id 
			where li_ime = li_ime_given
		)
		group by li_ime, interval_1h 
		order by li_ime, interval_1h 
	)
	group by li_ime
)

  }}}
}}}

==== 3. Мерење побарувачка на секоја линија во првиот квартал од оваа година

==== 4. Проток на патници според постојка и име на линија во вториот квардтал од минатата година
{{{#!div style="font-size: 80%"
  {{{#!sql
select p_ime, li_ime, count(patnik_k_id) as broj_patnici
from postojka as p
join postojkanalinija as pnl on p.p_id = pnl.p_id
join vozenje as v on pnl.pnl_id = v.kacuva_pnl_id
join patnik as pa on v.patnik_k_id = pa.k_id
join linija as l on pnl.li_id = l.li_id
where v.vozenje_start between (make_date(extract(year from (now() - '1 years'::interval))::int, 4, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 6, 30)) 
group by p_ime, li_ime
order by count(patnik_k_id) desc
  }}}
}}}

==== 5. Период од денот (интервали од 1 час) во кој најмногу се купува билети (часовни и дневни)


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

{{{#!div style="font-size: 80%"
  {{{#!sql
select
CASE 
  WHEN sum_kz IS NOT NULL AND sum_b IS NOT NULL THEN COALESCE(sum_kz::int, 0) + COALESCE(sum_b::int, 0)
  WHEN sum_kz IS NOT NULL THEN COALESCE(sum_kz::int, 0)
  WHEN sum_b IS NOT NULL THEN COALESCE(sum_b::int, 0)
  ELSE 0
END
from (
select 
(
select sum(kz_iznos)
from kazna k
where k.kz_plateno = true and (k.kz_datum between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and
									(make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31)))
									and (k.kz_datum_plateno between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and
									(make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31)))
)  sum_kz,
(
select sum(tb_cena)
from bilet b 
join tipbilet tb on tb.tb_id = b.tb_id 
where b.b_datum_na_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and
									(make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))
)  sum_b
)

  }}}
}}}