/*hiretések*/
drop view if exists ap_vw_allasajanlat_fej;
CREATE VIEW `ap_vw_allasajanlat_fej` AS 
Select a.id,
a.title,
a.short_description,
a.tasks,
a.expectations,
a.what_we_provide,
a.advantage,
a.other_public_information,
a.company_information,
a.tasks_visible,
a.what_we_provide_visible,
a.advantage_visible,
a.other_public_information_visible,
a.company_information_visible,
a.expectations_visible,
a.short_description_visible,
a.enabled,
a.approved,
a.release_date,
from_unixtime(a.release_date) as release_date_readable,
a.reference_number,
a.outer_advert,
a.inner_advert,
a.video_interview,
a.user_id,
a.region_id,
r.title as region_title,
r.zip as region_zip,
a.workplace_zip,
a.workplace_city,
a.workplace_address,
a.workplace,
concat(r.zip, ' ', r.title, ' ', a.workplace_address) as workplace_full_address,
a.nature_of_employment_id,
a.information,
a.user_create_id,
u.name as user_create_name,
case when ifnull(u.shown_company,
'')='' then u.company else u.shown_company end as shown_company,
u.contact_name,
u.contact_email,
u.contact_phone,
a.date_create,
a.date_modify,
a.user_modify_id,
u1.name as user_modify_name,
from_unixtime(a.date_create) as date_create_readable,
from_unixtime(a.date_modify) as date_modify_readable,
a.original_id,
a.youtube,
a.start_date,
a.professional_experience_id,
pf.title as professional_experience_title,
a.education_id,
ev.title as education_title,
a.category_id,
ac.title as category_title,
a.work_schedules_id,
ws.title as work_schedules_title,
a.driving_licence_ids,
/*group_concat(dl.title) as driving_licence_title*/
'' as driving_licence_title,
a.video_link,
a.letter_level_id,
a.parent_advert_id,
a.recruitment_area,
a.advert_type_id,
t.title as advert_type_title,
fj.title as nature_of_employment_title,
u.logo_url,
/*DATE_FORMAT(DATE_ADD(from_unixtime(a.release_date),INTERVAL a.running_days - a.views DAY),'%Y-%m-%d') as expiration_date ,*/
DATE_FORMAT(DATE_ADD(NOW(),INTERVAL a.running_days - a.views DAY),'%Y-%m-%d') as expiration_date ,
/*DATE_FORMAT(DATE_ADD(release_date,INTERVAL a.running_days - a.views DAY),'%Y-%m-%d') as expiration_date ,*/
a.competence_ids,
a.email,
a.phone,
a.email_visible,
a.salery,
a.salery_type,
a.salery_visible,
a.salery_currency,

a.short_description_title,
a.company_information_title,
a.tasks_title,
a.expectations_title,
a.what_we_provide_title,
a.advantage_title,
a.other_public_information_title,
a.list_views,
a.openings,
a.callable_days,
 a.callable_hours,
 a.question_option_ids,
 a.language_id,
 a.language_level_1,
 a.language_level_2,
a.language_level_3,
a.language_level_4,
a.language_level_5,
ny.title as language_title,
 a.language_2_id,
 a.language_2_level_1,
 a.language_2_level_2,
a.language_2_level_3,
a.language_2_level_4,
a.language_2_level_5,
ny1.title as language_2_title,
a.company_id,
c.title as company_title,
sz.Megnevezes as letter_level_title,
sz.Utvonal as letter_level_path,
a.running_days,
a.views,
a.deleted


				from ap_allasajanlat_fej a left outer join ap_regions_full r on r.id = a.region_id
				left outer join ap_allasajanlat_foglalakoztatas_jellege fj on fj.id = a.nature_of_employment_id
				left outer join ap_users u on u.id = a.user_id
				left outer join ap_users u1 on u1.id = a.user_modify_id
				left outer join ap_torzs_hirdetes_szakmai_tapasztalat pf on pf.id = a.professional_experience_id
				left outer join ap_torzs_hirdetes_elvart_vegzettseg ev on ev.id = a.education_id
				left outer join ap_torzs_hirdetes_alkategoria ac on ac.id = a.category_id
				left outer join ap_advert_types t on t.id = a.advert_type_id
				left outer join ap_allasajanlat_munkarend ws on ws.id = a.work_schedules_id
				/*left outer join ap_torzs_jogositvany dl on FIND_IN_SET(dl.id,a.driving_licence_ids)!=0*/
				left outer join ap_torzs_nyelv ny on ny.id = a.language_id
				left outer join ap_torzs_nyelv ny1 on ny1.id = a.language_2_id
				left outer join ap_company c on c.id = a.company_id
				left outer join ap_levelszintek sz on sz.LevelSzintKOD = a.letter_level_id
			
			/*group by a.id,
a.title,
 a.short_description,
a.enabled,
a.release_date,
a.reference_number,
a.outer_advert,
a.inner_advert,
a.video_interview,
a.user_id,
a.region_id,
r.title,
a.nature_of_employment_id,
a.information,
a.user_create_id,
u.name,
u.shown_company,
u.contact_name,
u.contact_email,
u.contact_phone,
a.date_create,
a.date_modify,
a.user_modify_id,
u1.name,
a.original_id,
a.youtube,
a.start_date,
a.professional_experience_id,
pf.title,
a.education_id,
ev.title,
a.category_id,
ac.title,
a.work_schedules_id,
ws.title,
a.driving_licence_ids,
a.video_link,
a.letter_level_id,
a.parent_advert_id,
a.recruitment_area,
fj.title,
a.logoUrl,
a.tasks,
a.expectations,
a.what_we_provide,
a.advantage,
a.other_public_information,
a.company_information,
a.tasks_visible,
a.what_we_provide_visible,
a.advantage_visible,
a.other_public_information_visible,
a.company_information_visible,
a.expectations_visible,
a.short_description_visible,
a.competence_ids,
a.email,
a.phone,
a.email_visible,
a.salery,
a.salery_type,
a.salery_currency,
a.short_description_title,
a.company_information_title,
a.tasks_title,
a.expectations_title,
a.what_we_provide_title,
a.advantage_title,
a.other_public_information_title,
a.salery_visible,
a.salery_currency,
a.salery_type,
a.salery,
a.workplace_zip,
a.workplace_city,
a.workplace_address,
a.workplace,
a.list_views,
a.openings,
a.callable_days,
 a.callable_hours,
 a.question_option_ids,
 a.language_id,
 a.language_level_1,
 a.language_level_2,
a.language_level_3,
a.language_level_4,
a.language_level_5,
ny.title,
a.language_2_id,
 a.language_2_level_1,
 a.language_2_level_2,
a.language_2_level_3,
a.language_2_level_4,
a.language_2_level_5,
ny1.title,
a.company_id,
c.title,
sz.Megnevezes,
sz.Utvonal,
a.running_days,
a.views*/


/*Állásajénlatok szűkített nézet*/
drop view if exists ap_vw_allasajalnatok_s;
create view ap_vw_allasajalnatok_s as
Select 
a.id,
a.title,
a.enabled,
a.release_date,
from_unixtime(a.release_date) as release_date_readable,
a.reference_number,
concat(r.zip, ' ', r.title, ' ', a.workplace_address) as workplace_full_address,
a.information,
a.running_days,
/*DATE_FORMAT(DATE_ADD(NOW(),INTERVAL a.running_days - a.views DAY),'%Y-%m-%d') as expiration_date ,*/
DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(a.release_date),INTERVAL a.running_days DAY),'%Y-%m-%d') as expiration_date ,
c.title as company_title,
sz.Megnevezes as letter_level_title,
sz.Utvonal as letter_level_path,
a.views,
a.user_id,
a.region_id,
a.nature_of_employment_id,
case when ifnull(u.shown_company,
'')='' then u.company else u.shown_company end as shown_company,
a.professional_experience_id,
a.education_id,
a.category_id,
a.work_schedules_id,
a.driving_licence_ids,
a.letter_level_id,
a.parent_advert_id,
a.advert_type_id,
a.competence_ids,
a.list_views,
a.openings,
a.company_id,
a.deleted,
crm.IrodaNev as CRMIrodaNev


				from ap_allasajanlat_fej a left outer join ap_regions_full r on r.id = a.region_id
				/*left outer join ap_allasajanlat_foglalakoztatas_jellege fj on fj.id = a.nature_of_employment_id*/
				left outer join ap_users u on u.id = a.user_id
				left outer join ap_users u1 on u1.id = a.user_modify_id
				/*left outer join ap_torzs_hirdetes_szakmai_tapasztalat pf on pf.id = a.professional_experience_id
				left outer join ap_torzs_hirdetes_elvart_vegzettseg ev on ev.id = a.education_id
				left outer join ap_torzs_hirdetes_alkategoria ac on ac.id = a.category_id
				left outer join ap_advert_types t on t.id = a.advert_type_id
				left outer join ap_allasajanlat_munkarend ws on ws.id = a.work_schedules_id
				left outer join ap_torzs_jogositvany dl on FIND_IN_SET(dl.id,a.driving_licence_ids)!=0
				left outer join ap_torzs_nyelv ny on ny.id = a.language_id
				left outer join ap_torzs_nyelv ny1 on ny1.id = a.language_2_id*/
				left outer join ap_company c on c.id = a.company_id
				left outer join ap_levelszintek sz on sz.LevelSzintKOD = a.letter_level_id
				left outer join hr112.crm_irodak crm on crm.IrodaKOD = a.CRMIrodaKOD
				

/*tipusok*/
	drop view if exists ap_vw_advert_types;

	CREATE VIEW `ap_vw_advert_types` AS 
	select `a`.`id` AS `id`,
	`a`.`title` AS `title`,
	`a`.`description` AS `description`,
	`a`.`user_type_ids` AS `user_type_ids`,
	group_concat(`t`.`title` separator ',') AS `user_types`,
	`a`.`net_price` AS `net_price`,
	a.discount_net_price,
	a.`vat` AS `vat`,
	`a`.`enabled` AS `enabled`,
	`a`.`daily_edm` AS `daily_edm`,
	`a`.`daily_edm_num` AS `daily_edm_num`,
	`a`.`visible_in_sites` AS `visible_in_sites`,
	`a`.`visible_in_sites_main` AS `visible_in_sites_main`,
	`a`.`days` AS `days`,
	`a`.`user_create_id` AS `user_create_id`,
	`a`.`user_modify_id` AS `user_modify_id`,
	`a`.`date_create` AS `date_create`
	,`a`.`date_modify` AS `date_modify` ,
	a.daily_edm_repeat,
	a.daily_edm_repeat_again,
	a.highlighting_price,
	a.another_highlighting_price

	from (`ap_advert_types` `a` left join `ap_user_types` `t` on((find_in_set(`t`.`id`,`a`.`user_type_ids`) <> 0))) 
	group by `a`.`id`,
	`a`.`title`,
	`a`.`description`,
	`a`.`user_type_ids`,
	`a`.`net_price`,
	`a`.`vat`,
	`a`.`enabled`,
	`a`.`daily_edm`,
	`a`.`daily_edm_num`,
	`a`.`visible_in_sites`,
	`a`.`visible_in_sites_main`,
	`a`.`days`,
	`a`.`user_create_id`,
	`a`.`user_modify_id`,
	`a`.`date_create`,
	`a`.`date_modify`;

	
/*BANK*/
drop view if exists ap_vw_bank;
CREATE VIEW `ap_vw_bank` AS 
select `b`.`id` AS `id`,
b.advert_id as advert_id,
case b.tr_type
	when 1 then a.title
	when 2 then 'Kredit feltötlés'
	when 3 then 'Éves előfizetés vásárlás'
	else a.title
end as title,
`u`.`name` AS `name`,
`b`.`trid` AS `trid`,
`b`.`message` AS `message`,
`b`.`answer` AS `answer`,
`b`.`user_create_id` AS `user_create_id`,
`b`.`user_modify_id` AS `user_modify_id`,
`b`.`date_create` AS `date_create`,
`b`.`date_modify` AS `date_modify`,
`b`.`amount` AS `amount`,
`b`.`anum` AS `anum`,
`b`.`payment` AS `payment`,
(case `b`.`payment` when 1 then 'Átutalás' when 2 then 'Bankkártya' end) AS `payment_title`,
`b`.`paid` AS `paid`,
`b`.`invoice_number` AS `invoice_number` ,
a.enabled,
b.sent,
b.transaction_data,
a.order_number
from `ap_bank` `b` left join `ap_allasajanlat_fej` `a` on `a`.`id` = `b`.`advert_id`
left outer join `ap_users` `u` on `u`.`id` = `b`.`user_create_id` ;



/*Hirdetés kategória*/
drop view if exists ap_vw_advert_category;
create view ap_vw_advert_category as
select c.id,
c.title,
c.description,
c.category,
c.user_create_id,
c.user_modify_id,
c.date_create,
c.date_modify,
c.user_type_ids,
group_concat(t.title) as user_type_title

from ap_advert_category c left outer join ap_user_types t on FIND_IN_SET(t.id, c.user_type_ids)!=0 
group by 
c.id,
c.title,
c.description,
c.category,
c.user_create_id,
c.user_modify_id,
c.date_create,
c.date_modify,
c.user_type_ids


;
/******************************/
drop view if exists ap_vw_regions;
create view ap_vw_regions as SELECT
r.id, 
r.title,
r.zip,
r.county_id,
c.title as county,
r.user_create_id,
r.user_modify_id,
r.date_create,
r.date_modify

from ap_regions r join ap_county c on c.id = r.county_id
union 
select 0, 'Válasszon', 0,1,'',0,0,0,0
;


drop view if exists ap_vw_regions_full;
create view ap_vw_regions_full as SELECT
r.id, 
r.title,
r.zip,
r.county_id,
r.country_id,
c.title as county,
r.user_create_id,
r.user_modify_id,
r.date_create,
r.date_modify,
concat(r.zip,' ',r.title) as title_full,
c1.title as country_title

from ap_regions_full r left outer join ap_county c on c.id = r.county_id
left outer join ap_country c1 on c1.id = r.country_id


/***************************************/
drop view if exists ap_vw_banner_type;
create view ap_vw_banner_type as
SELECT 
b.id,b.title,
b.price,
b.price2,
b.vat,
b.user_create_id,
b.user_modify_id,
b.date_create,
b.date_modify,
b.enabled,
b.days,
b.order_num,
b.description,
b.domain_ids,
group_concat(d.title) as domain_titles

from ap_banner_type b join ap_core_domain d on FIND_IN_SET(d.id, b.domain_ids)
group by
b.id,b.title,
b.price,
b.price2,
b.vat,
b.user_create_id,
b.user_modify_id,
b.date_create,
b.date_modify,
b.enabled,
b.days,
b.order_num,
b.description,
b.domain_ids

;

DROP VIEW IF EXISTS ap_vw_hirdetes_kompetencia;
CREATE VIEW ap_vw_hirdetes_kompetencia AS
SELECT k.id, k.title, k.date_create, k.user_create_id, k.date_modify, k.user_modify_id, k.type_id, k.enabled,k.num_of_order,
k1.title as type_title
FROM ap_hirdetes_kompetenciak k JOIN ap_hirdetes_kompetencia_tipus k1 on k.type_id = k1.id


;

drop view if exists ap_vw_hirdetes_kategoria_fa;
create view ap_vw_hirdetes_kategoria_fa as
select concat(999,id) as ElemKOD, 0 as SzuloElemKOD, title as Megnevezes, num_of_order as Sorrend from milapunk_allasportal.ap_torzs_hirdetes_kategoria
union
select id as ElemKOD, concat(999,parent_id) as SzuloElemKOD, title as Megnevezes, num_of_order as Sorrend from milapunk_allasportal.ap_torzs_hirdetes_alkategoria

drop view if exists ap_vw_hirdetes_kompetencia_fa;
create view ap_vw_hirdetes_kompetencia_fa as
select concat(999,id) as ElemKOD, 0 as SzuloElemKOD, title as Megnevezes, num_of_order as Sorrend from milapunk_allasportal.ap_hirdetes_kompetencia_tipus
union
select id as ElemKOD, concat(999,type_id) as SzuloElemKOD, title as Megnevezes, num_of_order as Sorrend from milapunk_allasportal.ap_hirdetes_kompetenciak
	



drop view if exists ap_vw_admins;
create view ap_vw_admins as
select a.id as administrator_id, a.fullname, u.id as user_id, u.name as user_name, u.company as user_company
from ap_administrators a join ap_users u on u.id = a.company_user_id
where u.user_type_id = 1;


drop view if exists ap_vw_ap_interju_idopontok;
create view ap_vw_ap_interju_idopontok AS
select i.`id`,
 i.`advert_id`, 
 a.title, 
 from_unixtime(i.`datumido`) as datumido, 
 case when i.number > 1 then 1 else 0 end as groupevent,
 i.`number` ,
 i.type,
 case i.type
 	when 1 then 'személyes interjú'
 	when 2 then 'on-line interjú'
 	when 3 then 'egyéb találkozó'
 end as type_title, i.address,i.description,i.archive

from ap_interju_idopontok i left  outer join ap_allasajanlat_fej a on a.id = i.advert_id
;


drop view if exists ap_vw_interju_jelentkezesek;
 create view ap_vw_interju_jelentkezesek as
 SELECT j.id as JelentkezesID, l.FeladoNev, l.FeladoEmail,a.MOBIL as Mobil,a.TELEFON as Telefon, af.title as HirdetesNev, FROM_UNIXTIME(i.datumido) as datumido,
 j.advert_id as HirdetesKOD, i.id as IdopontKOD
  FROM 
 milapunk_allasportal.ap_interju_jelentkezes j join milapunk_allasportal.ap_interju_idopontok i on i.id = j.time_id
 join hr112.aide_levelek l on l.LevelKOD = j.letter_id
 join hr112.abc_allaskeresok a on a.SZEMELY_ID = j.user_id
 join milapunk_allasportal.ap_allasajanlat_fej af on af.id = j.advert_id
;