select 
	extract(year from ca.concluded_on) as rok_zmluvy,
	ca.concluded_on as datum_uzavretia_zmluvy,
	n.contracting_authority_name as obstaravatel,
	cc.name as nazov_obstaravania,
	ct.corporate_body_name as dodavatel,
	ca.final_value_amount as suma_zakazky,
	ca.final_value_currency as mena_zakazky
from vvo.bulletin_issues  bi
	 join vvo.notices n on bi.id = n.bulletin_issue_id
	 join vvo.notice_types nt on nt.id = n.notice_type_id
	 join vvo.contracts cc on cc.id = n.contract_id
	 join vvo.result_notices rn on rn.contract_id = n.contract_id
	 join vvo.contract_awards ca on ca.result_notice_id = rn.id
	 join vvo.contractors ct on ct.contract_award_id = ca.id	
where 1 = 1
	and n.procedure_type_id  in (27156,27116,27135,27165)    -- priame rokovacie konanie 
	and nt.code in ('IPT','IPS','IPP')     --informacia o vysledku verejneho obstaravania
	
	/* podmienka na vyber vyhradnych prav */
	and (rn.direct_negotiate_procedure_reason like '%výhradné práva%' 
				or rn.direct_negotiate_procedure_reason like '%výhradných práv%') 
				
	/* podmienka pre vyber IKT/IT na zaklade hlavneho cpv kodu v zakazke*/ 
	and (rn.main_cpv like '32%'  
			or rn.main_cpv like '302%'
			or rn.main_cpv like '48%'
			or rn.main_cpv like '72%')  
	 	
	/* nasledujuca podmienka vrati o 4 zakazky viac, pretoze je postavena na tom ze hlavny cpv kod nemusi suvisiet s IT ale az nejaky zo vsetkych zverejnenych
	and exists (select 1 
			from vvo.result_notice_main_cpvs rnm 
			where rnm.result_notice_id = rn.id
				and (code like '32%'  
					or code like '302%'
					or code like '48%'
					or code like '72%')) */
order by ca.concluded_on desc;