WITH RECURSIVE dep_level_view AS (
    select
        eroad_index_id as cid,
        case when tran_dep1.value is not null then tran_dep1.value else hr_department.name end as name,
        hr_department.code,
        ARRAY[case when tran_dep1.value is not null then tran_dep1.value else hr_department.name end ] as name_list,
        ARRAY[res_level.code] as level_code_list,
        ARRAY[eroad_index_id] as path_ids,
        FALSE as is_cycle
    from hr_department
    LEFT join ir_translation AS tran_dep1 ON tran_dep1.res_id = hr_department.id AND tran_dep1.lang='zh_CN' AND tran_dep1.name='hr.department,name'
    left join res_selection as res_level on hr_department.x_bm_level = res_level.id
    where
        eroad_index_id is not null and
        eroad_start_date <= current_date and
        (eroad_end_date >= current_date or eroad_end_date is null) and
        not is_virtual_org and status = 'active' and hr_department.active and
          parent_department_id is NULL
    UNION
        SELECT
            dep_table.eroad_index_id as cid,
            case when tran_dep2.value is not null then tran_dep2.value else dep_table.name end as name,
            dep_table.code,
            dep_level_view.name_list || dep_table.name name_list,
            dep_level_view.level_code_list || res_level.code as level_code_list,
            dep_level_view.path_ids || dep_table.eroad_index_id as path_ids,
            dep_table.eroad_index_id = any(dep_level_view.path_ids) as is_cycle
        FROM hr_department dep_table
        LEFT join ir_translation AS tran_dep2 ON tran_dep2.res_id = dep_table.id AND tran_dep2.lang='zh_CN' AND tran_dep2.name='hr.department,name'
        left join res_selection as res_level on dep_table.x_bm_level = res_level.id
        JOIN dep_level_view
        ON dep_table.parent_department_id = dep_level_view.cid and not dep_level_view.is_cycle and
           dep_table.eroad_index_id is not null and dep_table.eroad_start_date <= current_date and
            (dep_table.eroad_end_date >= current_date or dep_table.eroad_end_date is null) and
            not dep_table.is_virtual_org and dep_table.status = 'active' and dep_table.active
)

select empl.employee_number as "员工工号",empl.name as "员工姓名",
case
when ress.name = 'Female' then '女'
when ress.name = 'Male' then '男'
else '' end as "性别",
empl.hire_date as "入职日期",empr.confirmed_resignation_date as "异动日期",'离职' as "异动事件",
COALESCE(ir_resr.value, resr.name, '') as "异动原因",'' as "公司代码",'' as "公司名称",

'' as "系统",
'' as "中心",

'' as "部门名称",'' as "职位",'' as "层级",'' as "职级等级",jobc.x_groupcode as "员工子组编码",
COALESCE(ir_empg.value, empg.name, '') as "员工子组描述",'' as "用工类型",'' as "成本中心编码",'' as "成本中心",comp.x_company_code as "异动前公司代码",
comp.x_company_name as "异动前公司名称",
-- dep_level_view.name_list[3] as "异动前系统",
case when dep_level_view.level_code_list[10]='S000149' then dep_level_view.name_list[10]
     when dep_level_view.level_code_list[9]='S000149' then dep_level_view.name_list[9]
     when dep_level_view.level_code_list[8]='S000149' then dep_level_view.name_list[8]
     when dep_level_view.level_code_list[7]='S000149' then dep_level_view.name_list[7]
     when dep_level_view.level_code_list[6]='S000149' then dep_level_view.name_list[6]
     when dep_level_view.level_code_list[5]='S000149' then dep_level_view.name_list[5]
     when dep_level_view.level_code_list[4]='S000149' then dep_level_view.name_list[4]
     when dep_level_view.level_code_list[3]='S000149' then dep_level_view.name_list[3]
     when dep_level_view.level_code_list[2]='S000149' then dep_level_view.name_list[2]
     when dep_level_view.level_code_list[1]='S000149' then dep_level_view.name_list[1]
      end as 异动前系统,
-- dep_level_view.name_list[4] as "异动前中心",
case when dep_level_view.level_code_list[10]='S000150' then dep_level_view.name_list[10]
     when dep_level_view.level_code_list[9]='S000150' then dep_level_view.name_list[9]
     when dep_level_view.level_code_list[8]='S000150' then dep_level_view.name_list[8]
     when dep_level_view.level_code_list[7]='S000150' then dep_level_view.name_list[7]
     when dep_level_view.level_code_list[6]='S000150' then dep_level_view.name_list[6]
     when dep_level_view.level_code_list[5]='S000150' then dep_level_view.name_list[5]
     when dep_level_view.level_code_list[4]='S000150' then dep_level_view.name_list[4]
     when dep_level_view.level_code_list[3]='S000150' then dep_level_view.name_list[3]
     when dep_level_view.level_code_list[2]='S000150' then dep_level_view.name_list[2]
     when dep_level_view.level_code_list[1]='S000150' then dep_level_view.name_list[1]
      end as 异动前中心,


dept.name as "异动前部门",jobc.name as "异动前职位",jobs.name as "异动前层级",
jobl.name as "异动前职级等级",COALESCE(ir_empt.value, empt.name, '') as "异动前用工类型",cost.code as "异动前成本中心编码",cost.name as "异动前成本中心",
'' as "调整类型",empr.confirmed_resignation_date as "离职日期",perpe.x_hjadress as "联系地址",empl.business_phone as "手机号",
ir_cres.value as "证件类型",perp.national_id as "证件号码",
perpe.x_hjadress 户籍地址,

round(((extract(year  from age(empr.confirmed_resignation_date,empl.first_date_worked))*12
+ extract(month  from age(empr.confirmed_resignation_date,empl.first_date_worked))
+(extract(day  from age(empr.confirmed_resignation_date,empl.first_date_worked))/30)::float)/12)::float,1) 司龄,
resignation_type.name 异动类型,
perpe.x_gjj_account_mount 公积金帐号,
roup1selection.name 员工类型,
COALESCE(ir_educa.value ,educa.name,'') as "最高学历",
COALESCE(ir_x_city.value ,x_city.name,'') as 城市


from employee_resignation empr
LEFT JOIN dep_level_view ON dep_level_view.cid = empr.department_id
left join hr_employee empl on empr.employee_id = empl.id
left join res_selection ress on empl.gender = ress.id
left join resignation_reason resr on empr.resignation_reason = resr.id
LEFT JOIN ir_translation ir_resr
    ON ir_resr.res_id = resr.id AND
       ir_resr.name = 'resignation.reason,name' AND
       ir_resr.lang = 'zh_CN'
left join hr_department dept on empr.department_id = dept.id
left join department_cost_center_rel dept_cost on dept.id = dept_cost.department_id
left join res_selection resse on dept.x_bm_level = resse.id

left join emp_cost_distribution emp_cost on empl.id = emp_cost.employee_id
and emp_cost.start_date<=empl.termination_date
AND (emp_cost.end_date >=empl.termination_date OR emp_cost.end_date IS NULL)
and emp_cost.active=True
and emp_cost.is_primary=True
left join cost_center cost on emp_cost.cost_center_id = cost.id
left join x_builder_company comp on cost.x_lyfcompany_code = comp.id

left join emp_job empj on empr.employee_id = empj.employee_id
left join employee_status emps on empj.employee_status = emps.id
left join job_classification jobc on empj.job_classification_id = jobc.id
left join employee_group empg on jobc.x_emp_childgroup1 = empg.id
LEFT JOIN ir_translation ir_empg
    ON ir_empg.res_id = empg.id AND
       ir_empg.name = 'employee.group,name' AND
       ir_empg.lang = 'zh_CN'
left join job_sequence jobs on empj.job_sequence_id = jobs.id
left join job_level jobl on jobc.job_level_id = jobl.id
left join employee_type empt on empj.employee_type = empt.id
LEFT JOIN ir_translation ir_empt
    ON ir_empt.res_id = empt.id AND
       ir_empt.name = 'employee.type,name' AND
       ir_empt.lang = 'zh_CN'
left join per_person perp on empl.person_id = perp.id
left join per_personal perpe on perp.per_personal = perpe.id
left join res_selection cres on perp.card_type = cres.id
LEFT JOIN ir_translation ir_cres
    ON ir_cres.res_id = cres.id AND
       ir_cres.name = 'res.selection,name' AND
       ir_cres.lang = 'zh_CN'
left join resignation_type on empr.resignation_type=resignation_type.id
left join res_selection roup1selection on empj. x_emptype_empgroup1 =roup1selection.id
left join person_education pers on empl.person_id = pers.person_id and pers.is_highest_education = 't'
left join res_selection educa on pers.educational_background = educa.id
LEFT JOIN ir_translation ir_educa
    ON ir_educa.res_id = educa.id AND
       ir_educa.name = 'res.selection,name' AND
       ir_educa.lang = 'zh_CN'
-- 城市
LEFT JOIN res_selection x_city on empj.x_city = x_city.id
LEFT JOIN ir_translation ir_x_city
    ON ir_x_city.res_id = x_city.id AND
       ir_x_city.name = 'res.selection,name' AND
       ir_x_city.lang = 'zh_CN'

where
empj.eroad_index_id is null
and empr.x_bp_state = 'done'

union

(
select empl.employee_number as "员工工号",empl.name as "员工姓名",
case
when ress.name = 'Female' then '女'
when ress.name = 'Male' then '男'
else '' end as "性别",
empl.hire_date as "入职日期",empl.hire_date as "异动日期",'入职' as "异动事件",
'' as "异动原因",comp.x_company_code as "公司代码",comp.x_company_name as "公司名称",

-- dep_level_view.name_list[3] as "异动前系统",
case when dep_level_view.level_code_list[10]='S000149' then dep_level_view.name_list[10]
     when dep_level_view.level_code_list[9]='S000149' then dep_level_view.name_list[9]
     when dep_level_view.level_code_list[8]='S000149' then dep_level_view.name_list[8]
     when dep_level_view.level_code_list[7]='S000149' then dep_level_view.name_list[7]
     when dep_level_view.level_code_list[6]='S000149' then dep_level_view.name_list[6]
     when dep_level_view.level_code_list[5]='S000149' then dep_level_view.name_list[5]
     when dep_level_view.level_code_list[4]='S000149' then dep_level_view.name_list[4]
     when dep_level_view.level_code_list[3]='S000149' then dep_level_view.name_list[3]
     when dep_level_view.level_code_list[2]='S000149' then dep_level_view.name_list[2]
     when dep_level_view.level_code_list[1]='S000149' then dep_level_view.name_list[1]
      end as 系统,
-- dep_level_view.name_list[4] as "异动前中心",
case when dep_level_view.level_code_list[10]='S000150' then dep_level_view.name_list[10]
     when dep_level_view.level_code_list[9]='S000150' then dep_level_view.name_list[9]
     when dep_level_view.level_code_list[8]='S000150' then dep_level_view.name_list[8]
     when dep_level_view.level_code_list[7]='S000150' then dep_level_view.name_list[7]
     when dep_level_view.level_code_list[6]='S000150' then dep_level_view.name_list[6]
     when dep_level_view.level_code_list[5]='S000150' then dep_level_view.name_list[5]
     when dep_level_view.level_code_list[4]='S000150' then dep_level_view.name_list[4]
     when dep_level_view.level_code_list[3]='S000150' then dep_level_view.name_list[3]
     when dep_level_view.level_code_list[2]='S000150' then dep_level_view.name_list[2]
     when dep_level_view.level_code_list[1]='S000150' then dep_level_view.name_list[1]
      end as 中心,

dept.name as "部门名称",jobc.name as "职位",jobs.name as "层级",
jobl.name as "职级等级",jobc.x_groupcode as "员工子组编码",COALESCE(ir_empg.value, empg.name, '') as "员工子组描述",COALESCE(ir_empt.value, empt.name, '') as "用工类型",cost.code as "成本中心编码",
cost.name as "成本中心",'' as "异动前公司代码",'' as "异动前公司名称",

'' as "异动前系统",'' as "异动前中心",


'' as "异动前部门",'' as "异动前职位",'' as "异动前层级",
'' as "异动前职级等级",'' as "异动前用工类型",'' as "异动前成本中心编码",'' as "异动前成本中心",'' as "调整类型",'9999-12-31' as "离职日期",perpe.x_hjadress as "联系地址",empl.business_phone as "手机号",
ir_cres.value as "证件类型",perp.national_id as "证件号码",
perpe.x_hjadress 户籍地址,
0.0::float 司龄,
'' 异动类型,
perpe.x_gjj_account_mount 公积金帐号,
roup1selection.name 员工类型,
COALESCE(ir_educa.value ,educa.name,'') as "最高学历",
COALESCE(ir_x_city.value ,x_city.name,'') as 城市

from  hr_employee empl
left join res_selection ress on empl.gender = ress.id
left join emp_job empj on empl.id = empj.employee_id
LEFT JOIN dep_level_view ON dep_level_view.cid = empj.department_id
left join hr_department dept on empj.department_id = dept.id
left join department_cost_center_rel dept_cost on dept.id = dept_cost.department_id
left join res_selection resse on dept.x_bm_level = resse.id

left join emp_cost_distribution emp_cost on empl.id = emp_cost.employee_id
and emp_cost.start_date<=empl.hire_date
AND (emp_cost.end_date >=empl.hire_date OR emp_cost.end_date IS NULL)
and emp_cost.active=True
and emp_cost.is_primary=True
left join cost_center cost on emp_cost.cost_center_id = cost.id
left join x_builder_company comp on cost.x_lyfcompany_code = comp.id

left join job_classification jobc on empj.job_classification_id = jobc.id
left join employee_group empg on jobc.x_emp_childgroup1 = empg.id
LEFT JOIN ir_translation ir_empg
    ON ir_empg.res_id = empg.id AND
       ir_empg.name = 'employee.group,name' AND
       ir_empg.lang = 'zh_CN'
left join job_sequence jobs on empj.job_sequence_id = jobs.id
left join job_level jobl on jobc.job_level_id = jobl.id
left join employee_type empt on empj.employee_type = empt.id
LEFT JOIN ir_translation ir_empt
    ON ir_empt.res_id = empt.id AND
       ir_empt.name = 'employee.type,name' AND
       ir_empt.lang = 'zh_CN'
left join per_person perp on empl.person_id = perp.id
left join per_personal perpe on perp.per_personal = perpe.id
left join pre_hire preh on empl.id = preh.employee_id
left join res_selection cres on perp.card_type = cres.id
LEFT JOIN ir_translation ir_cres
    ON ir_cres.res_id = cres.id AND
       ir_cres.name = 'res.selection,name' AND
       ir_cres.lang = 'zh_CN'

left join res_selection roup1selection on empj. x_emptype_empgroup1 =roup1selection.id
left join person_education pers on empl.person_id = pers.person_id and pers.is_highest_education = 't'
left join res_selection educa on pers.educational_background = educa.id
LEFT JOIN ir_translation ir_educa
    ON ir_educa.res_id = educa.id AND
       ir_educa.name = 'res.selection,name' AND
       ir_educa.lang = 'zh_CN'
-- 城市
LEFT JOIN res_selection x_city on empj.x_city = x_city.id
LEFT JOIN ir_translation ir_x_city
    ON ir_x_city.res_id = x_city.id AND
       ir_x_city.name = 'res.selection,name' AND
       ir_x_city.lang = 'zh_CN'
where empj.eroad_index_id is null
and  perpe.status = 'active'
and preh.hire_status = 'hired')
union
(
select empl.employee_number as "员工工号",empl.name as "员工姓名",
case
when ress.name = 'Female' then '女'
when ress.name = 'Male' then '男'
else '' end as "性别",
empl.hire_date as "入职日期",jobt.effective_date as "异动日期",'异动' as "异动事件",
COALESCE(ir_reas.value, reas.name, '') as "异动原因",
-- adj_type.name 异动原因,
comp.x_company_code as "公司代码",comp.x_company_name as "公司名称",

case when dep_level_view.level_code_list[10]='S000149' then dep_level_view.name_list[10]
     when dep_level_view.level_code_list[9]='S000149' then dep_level_view.name_list[9]
     when dep_level_view.level_code_list[8]='S000149' then dep_level_view.name_list[8]
     when dep_level_view.level_code_list[7]='S000149' then dep_level_view.name_list[7]
     when dep_level_view.level_code_list[6]='S000149' then dep_level_view.name_list[6]
     when dep_level_view.level_code_list[5]='S000149' then dep_level_view.name_list[5]
     when dep_level_view.level_code_list[4]='S000149' then dep_level_view.name_list[4]
     when dep_level_view.level_code_list[3]='S000149' then dep_level_view.name_list[3]
     when dep_level_view.level_code_list[2]='S000149' then dep_level_view.name_list[2]
     when dep_level_view.level_code_list[1]='S000149' then dep_level_view.name_list[1]
      end as 系统,
case when dep_level_view.level_code_list[10]='S000150' then dep_level_view.name_list[10]
     when dep_level_view.level_code_list[9]='S000150' then dep_level_view.name_list[9]
     when dep_level_view.level_code_list[8]='S000150' then dep_level_view.name_list[8]
     when dep_level_view.level_code_list[7]='S000150' then dep_level_view.name_list[7]
     when dep_level_view.level_code_list[6]='S000150' then dep_level_view.name_list[6]
     when dep_level_view.level_code_list[5]='S000150' then dep_level_view.name_list[5]
     when dep_level_view.level_code_list[4]='S000150' then dep_level_view.name_list[4]
     when dep_level_view.level_code_list[3]='S000150' then dep_level_view.name_list[3]
     when dep_level_view.level_code_list[2]='S000150' then dep_level_view.name_list[2]
     when dep_level_view.level_code_list[1]='S000150' then dep_level_view.name_list[1]
      end as 中心,



dept.name as "部门名称",jobc.name as "职位",
jobs.name as "层级",jobl.name as "职级等级",empg.code as "员工子组编码",COALESCE(ir_empg.value, empg.name, '') as "员工子组描述",COALESCE(ir_empt.value, empt.name, '') as "用工类型",cost.code as "成本中心编码",
cost.name as "成本中心",yd_comp.x_company_code as "异动前公司代码",yd_comp.x_company_name as "异动前公司名称",






case when ydq_dep.level_code_list[10]='S000149' then ydq_dep.name_list[10]
     when ydq_dep.level_code_list[9]='S000149' then ydq_dep.name_list[9]
     when ydq_dep.level_code_list[8]='S000149' then ydq_dep.name_list[8]
     when ydq_dep.level_code_list[7]='S000149' then ydq_dep.name_list[7]
     when ydq_dep.level_code_list[6]='S000149' then ydq_dep.name_list[6]
     when ydq_dep.level_code_list[5]='S000149' then ydq_dep.name_list[5]
     when ydq_dep.level_code_list[4]='S000149' then ydq_dep.name_list[4]
     when ydq_dep.level_code_list[3]='S000149' then ydq_dep.name_list[3]
     when ydq_dep.level_code_list[2]='S000149' then ydq_dep.name_list[2]
     when ydq_dep.level_code_list[1]='S000149' then ydq_dep.name_list[1]
      end as 异动前系统,
case when ydq_dep.level_code_list[10]='S000150' then ydq_dep.name_list[10]
     when ydq_dep.level_code_list[9]='S000150' then ydq_dep.name_list[9]
     when ydq_dep.level_code_list[8]='S000150' then ydq_dep.name_list[8]
     when ydq_dep.level_code_list[7]='S000150' then ydq_dep.name_list[7]
     when ydq_dep.level_code_list[6]='S000150' then ydq_dep.name_list[6]
     when ydq_dep.level_code_list[5]='S000150' then ydq_dep.name_list[5]
     when ydq_dep.level_code_list[4]='S000150' then ydq_dep.name_list[4]
     when ydq_dep.level_code_list[3]='S000150' then ydq_dep.name_list[3]
     when ydq_dep.level_code_list[2]='S000150' then ydq_dep.name_list[2]
     when ydq_dep.level_code_list[1]='S000150' then ydq_dep.name_list[1]
      end as 异动前中心,

yd_dept.name as "异动前部门",yd_jobc.name as "异动前职位",yd_jobs.name as "异动前层级",yd_jobl.name as "异动前职级等级",COALESCE(ir_empta.value, empta.name, '') as "异动前用工类型",
yd_cost.code as "异动前成本中心编码",yd_cost.name as "异动前成本中心",adj_type.name as "调整类型",'9999-12-31' as "离职日期",
perpe.x_hjadress as "联系地址",empl.business_phone as "手机号",ir_cres.value as "证件类型",perp.national_id as "证件号码",

perpe.x_hjadress 户籍地址,
0.0::float 司龄,
adj_type.name 异动类型,
perpe.x_gjj_account_mount 公积金帐号,
roup1selection.name 员工类型,
COALESCE(ir_educa.value ,educa.name,'') as "最高学历",
COALESCE(ir_x_city.value ,x_city.name,'') as 城市

from job_transfer jobt
left join hr_employee empl on jobt.employee_id = empl.id
left join emp_job empj on empl.job_info_id=empj.id
left join res_selection ress on empl.gender = ress.id
left join adjustment_reason reas on jobt.adjustment_reason = reas.id
LEFT JOIN ir_translation ir_reas
    ON ir_reas.res_id = reas.id AND
       ir_reas.name = 'adjustment.reason,name' AND
       ir_reas.lang = 'zh_CN'
left join adjustment_type adj_type on jobt.adjustment_type = adj_type.id
LEFT JOIN dep_level_view ON dep_level_view.cid = jobt.new_department_id
left join hr_department dept on jobt.new_department_id = dept.id
left join department_cost_center_rel dept_cost on dept.id = dept_cost.department_id
left join res_selection resse on dept.x_bm_level = resse.id
left join cost_center cost on dept_cost.cost_center_id = cost.id
left join x_builder_company comp on cost.x_lyfcompany_code = comp.id
left join job_classification jobc on jobt.new_job_id = jobc.id
left join employee_group empg on jobc.x_emp_childgroup1 = empg.id
LEFT JOIN ir_translation ir_empg
    ON ir_empg.res_id = empg.id AND
       ir_empg.name = 'employee.group,name' AND
       ir_empg.lang = 'zh_CN'
left join job_sequence jobs on jobt.new_job_sequence_id = jobs.id
left join job_level jobl on jobt.new_job_level_id = jobl.id
-- left join employee_type empt on empl.employee_type = empt.id
LEFT JOIN dep_level_view ydq_dep ON ydq_dep.cid = jobt.department_id

left join hr_department yd_dept on jobt.department_id = yd_dept.id
left join department_cost_center_rel yd_dept_cost on yd_dept.id = yd_dept_cost.department_id
left join cost_center yd_cost on yd_dept_cost.cost_center_id = yd_cost.id
left join x_builder_company yd_comp on yd_cost.x_lyfcompany_code = yd_comp.id
left join res_selection yd_resse on yd_dept.x_bm_level = yd_resse.id
left join job_classification yd_jobc on jobt.job_id = yd_jobc.id
left join job_sequence yd_jobs on jobt.job_sequence_id = yd_jobs.id
left join job_level yd_jobl on jobt.job_level_id = yd_jobl.id
left join per_person perp on empl.person_id = perp.id
left join per_personal perpe on perp.per_personal = perpe.id
left join res_selection cres on perp.card_type = cres.id
LEFT JOIN ir_translation ir_cres
    ON ir_cres.res_id = cres.id AND
       ir_cres.name = 'res.selection,name' AND
       ir_cres.lang = 'zh_CN'

left join res_selection roup1selection on empj. x_emptype_empgroup1 =roup1selection.id
left join person_education pers on empl.person_id = pers.person_id and pers.is_highest_education = 't'
left join res_selection educa on pers.educational_background = educa.id
LEFT JOIN ir_translation ir_educa
    ON ir_educa.res_id = educa.id AND
       ir_educa.name = 'res.selection,name' AND
       ir_educa.lang = 'zh_CN'

left join employee_type empt on empj.employee_type = empt.id
LEFT JOIN ir_translation ir_empt
    ON ir_empt.res_id = empt.id AND
       ir_empt.name = 'employee.type,name' AND
       ir_empt.lang = 'zh_CN'

left join employee_type empta on jobt.employee_type = empta.id
LEFT JOIN ir_translation ir_empta
    ON ir_empta.res_id = empta.id AND
       ir_empta.name = 'employee.type,name' AND
       ir_empta.lang = 'zh_CN'
-- 城市
LEFT JOIN res_selection x_city on empj.x_city = x_city.id
LEFT JOIN ir_translation ir_x_city
    ON ir_x_city.res_id = x_city.id AND
       ir_x_city.name = 'res.selection,name' AND
       ir_x_city.lang = 'zh_CN'

where jobt.x_bp_state = 'done')

union
(
select empl.employee_number as "员工工号",empl.name as "员工姓名",
case
when ress.name = 'Female' then '女'
when ress.name = 'Male' then '男'
else '' end as "性别",
empl.hire_date as "入职日期",empr.probation_date as "异动日期",'转正' as "异动事件",
-- COALESCE(ir_resr.value, resr.name, '') as "异动原因",
'' 异动原因,
'' as "公司代码",'' as "公司名称",
'' 系统,
'' 中心,

'' as "部门名称",'' as "职位",'' as "层级",'' as "职级等级",jobc.x_groupcode as "员工子组编码",
COALESCE(ir_empg.value, empg.name, '') as "员工子组描述",'' as "用工类型",'' as "成本中心编码",'' as "成本中心",comp.x_company_code as "异动前公司代码",
comp.x_company_name as "异动前公司名称",
case when dep_level_view.level_code_list[10]='S000149' then dep_level_view.name_list[10]
     when dep_level_view.level_code_list[9]='S000149' then dep_level_view.name_list[9]
     when dep_level_view.level_code_list[8]='S000149' then dep_level_view.name_list[8]
     when dep_level_view.level_code_list[7]='S000149' then dep_level_view.name_list[7]
     when dep_level_view.level_code_list[6]='S000149' then dep_level_view.name_list[6]
     when dep_level_view.level_code_list[5]='S000149' then dep_level_view.name_list[5]
     when dep_level_view.level_code_list[4]='S000149' then dep_level_view.name_list[4]
     when dep_level_view.level_code_list[3]='S000149' then dep_level_view.name_list[3]
     when dep_level_view.level_code_list[2]='S000149' then dep_level_view.name_list[2]
     when dep_level_view.level_code_list[1]='S000149' then dep_level_view.name_list[1]
      end as 异动前系统,
case when dep_level_view.level_code_list[10]='S000150' then dep_level_view.name_list[10]
     when dep_level_view.level_code_list[9]='S000150' then dep_level_view.name_list[9]
     when dep_level_view.level_code_list[8]='S000150' then dep_level_view.name_list[8]
     when dep_level_view.level_code_list[7]='S000150' then dep_level_view.name_list[7]
     when dep_level_view.level_code_list[6]='S000150' then dep_level_view.name_list[6]
     when dep_level_view.level_code_list[5]='S000150' then dep_level_view.name_list[5]
     when dep_level_view.level_code_list[4]='S000150' then dep_level_view.name_list[4]
     when dep_level_view.level_code_list[3]='S000150' then dep_level_view.name_list[3]
     when dep_level_view.level_code_list[2]='S000150' then dep_level_view.name_list[2]
     when dep_level_view.level_code_list[1]='S000150' then dep_level_view.name_list[1]
      end as 异动前中心,

dept.name as "异动前部门",jobc.name as "异动前职位",jobs.name as "异动前层级",
jobl.name as "异动前职级等级",COALESCE(ir_empt.value, empt.name, '') as "异动前用工类型",cost.code as "异动前成本中心编码",cost.name as "异动前成本中心",
'' as "调整类型",'9999-12-31' "离职日期",perpe.x_hjadress as "联系地址",empl.business_phone as "手机号",
ir_cres.value as "证件类型",perp.national_id as "证件号码",
perpe.x_hjadress 户籍地址,

0.0::float 司龄,
'' 异动类型,
perpe.x_gjj_account_mount 公积金帐号,
roup1selection.name 员工类型,
COALESCE(ir_educa.value ,educa.name,'') as "最高学历",
COALESCE(ir_x_city.value ,x_city.name,'') as 城市

from employee_probation empr
LEFT JOIN dep_level_view ON dep_level_view.cid = empr.department_id
left join hr_employee empl on empr.employee_id = empl.id
left join res_selection ress on empl.gender = ress.id
-- left join resignation_reason resr on empr.resignation_reason = resr.id
-- LEFT JOIN ir_translation ir_resr
--     ON ir_resr.res_id = resr.id AND
 --       ir_resr.name = 'resignation.reason,name' AND
 --       ir_resr.lang = 'zh_CN'
left join hr_department dept on empr.department_id = dept.id
left join department_cost_center_rel dept_cost on dept.id = dept_cost.department_id
left join res_selection resse on dept.x_bm_level = resse.id
left join emp_cost_distribution emp_cost on empl.id = emp_cost.employee_id
and emp_cost.start_date<=empl.termination_date
AND (emp_cost.end_date >=empl.termination_date OR emp_cost.end_date IS NULL)
and emp_cost.active=True
and emp_cost.is_primary=True
left join cost_center cost on emp_cost.cost_center_id = cost.id
left join x_builder_company comp on cost.x_lyfcompany_code = comp.id
left join emp_job empj on empr.employee_id = empj.employee_id
left join employee_status emps on empj.employee_status = emps.id
left join job_classification jobc on empj.job_classification_id = jobc.id
left join employee_group empg on jobc.x_emp_childgroup1 = empg.id
LEFT JOIN ir_translation ir_empg
    ON ir_empg.res_id = empg.id AND
       ir_empg.name = 'employee.group,name' AND
       ir_empg.lang = 'zh_CN'
left join job_sequence jobs on empj.job_sequence_id = jobs.id
left join job_level jobl on jobc.job_level_id = jobl.id
left join employee_type empt on empj.employee_type = empt.id
LEFT JOIN ir_translation ir_empt
    ON ir_empt.res_id = empt.id AND
       ir_empt.name = 'employee.type,name' AND
       ir_empt.lang = 'zh_CN'
left join per_person perp on empl.person_id = perp.id
left join per_personal perpe on perp.per_personal = perpe.id
left join res_selection cres on perp.card_type = cres.id
LEFT JOIN ir_translation ir_cres
    ON ir_cres.res_id = cres.id AND
       ir_cres.name = 'res.selection,name' AND
       ir_cres.lang = 'zh_CN'
-- left join resignation_type on empr.resignation_type=resignation_type.id


left join res_selection roup1selection on empj. x_emptype_empgroup1 =roup1selection.id
left join person_education pers on empl.person_id = pers.person_id and pers.is_highest_education = 't'
left join res_selection educa on pers.educational_background = educa.id
LEFT JOIN ir_translation ir_educa
    ON ir_educa.res_id = educa.id AND
       ir_educa.name = 'res.selection,name' AND
       ir_educa.lang = 'zh_CN'
-- 城市
LEFT JOIN res_selection x_city on empj.x_city = x_city.id
LEFT JOIN ir_translation ir_x_city
    ON ir_x_city.res_id = x_city.id AND
       ir_x_city.name = 'res.selection,name' AND
       ir_x_city.lang = 'zh_CN'
where
empj.eroad_index_id is null
and empr.x_bp_state = 'done')

union
(
select empl.employee_number as "员工工号",empl.name as "员工姓名",
case
when ress.name = 'Female' then '女'
when ress.name = 'Male' then '男'
else '' end as "性别",
empl.hire_date as "入职日期",empl.hire_date as "异动日期",'重新雇佣' as "异动事件",
'' as "异动原因",comp.x_company_code as "公司代码",comp.x_company_name as "公司名称",

-- dep_level_view.name_list[3] as "异动前系统",
case when dep_level_view.level_code_list[10]='S000149' then dep_level_view.name_list[10]
     when dep_level_view.level_code_list[9]='S000149' then dep_level_view.name_list[9]
     when dep_level_view.level_code_list[8]='S000149' then dep_level_view.name_list[8]
     when dep_level_view.level_code_list[7]='S000149' then dep_level_view.name_list[7]
     when dep_level_view.level_code_list[6]='S000149' then dep_level_view.name_list[6]
     when dep_level_view.level_code_list[5]='S000149' then dep_level_view.name_list[5]
     when dep_level_view.level_code_list[4]='S000149' then dep_level_view.name_list[4]
     when dep_level_view.level_code_list[3]='S000149' then dep_level_view.name_list[3]
     when dep_level_view.level_code_list[2]='S000149' then dep_level_view.name_list[2]
     when dep_level_view.level_code_list[1]='S000149' then dep_level_view.name_list[1]
      end as 异动前系统,
-- dep_level_view.name_list[4] as "异动前中心",
case when dep_level_view.level_code_list[10]='S000150' then dep_level_view.name_list[10]
     when dep_level_view.level_code_list[9]='S000150' then dep_level_view.name_list[9]
     when dep_level_view.level_code_list[8]='S000150' then dep_level_view.name_list[8]
     when dep_level_view.level_code_list[7]='S000150' then dep_level_view.name_list[7]
     when dep_level_view.level_code_list[6]='S000150' then dep_level_view.name_list[6]
     when dep_level_view.level_code_list[5]='S000150' then dep_level_view.name_list[5]
     when dep_level_view.level_code_list[4]='S000150' then dep_level_view.name_list[4]
     when dep_level_view.level_code_list[3]='S000150' then dep_level_view.name_list[3]
     when dep_level_view.level_code_list[2]='S000150' then dep_level_view.name_list[2]
     when dep_level_view.level_code_list[1]='S000150' then dep_level_view.name_list[1]
      end as 异动前中心,

dept.name as "部门名称",jobc.name as "职位",jobs.name as "层级",
jobl.name as "职级等级",jobc.x_groupcode as "员工子组编码",COALESCE(ir_empg.value, empg.name, '') as "员工子组描述",COALESCE(ir_empt.value, empt.name, '') as "用工类型",cost.code as "成本中心编码",
cost.name as "成本中心",'' as "异动前公司代码",'' as "异动前公司名称",

-- dep_level_view.name_list[3] as "异动前系统",
case when dep_level_viewold.level_code_list[10]='S000149' then dep_level_viewold.name_list[10]
     when dep_level_viewold.level_code_list[9]='S000149' then dep_level_viewold.name_list[9]
     when dep_level_viewold.level_code_list[8]='S000149' then dep_level_viewold.name_list[8]
     when dep_level_viewold.level_code_list[7]='S000149' then dep_level_viewold.name_list[7]
     when dep_level_viewold.level_code_list[6]='S000149' then dep_level_viewold.name_list[6]
     when dep_level_viewold.level_code_list[5]='S000149' then dep_level_viewold.name_list[5]
     when dep_level_viewold.level_code_list[4]='S000149' then dep_level_viewold.name_list[4]
     when dep_level_viewold.level_code_list[3]='S000149' then dep_level_viewold.name_list[3]
     when dep_level_viewold.level_code_list[2]='S000149' then dep_level_viewold.name_list[2]
     when dep_level_viewold.level_code_list[1]='S000149' then dep_level_viewold.name_list[1]
      end as 系统,
case when dep_level_viewold.level_code_list[10]='S000150' then dep_level_viewold.name_list[10]
     when dep_level_viewold.level_code_list[9]='S000150' then dep_level_viewold.name_list[9]
     when dep_level_viewold.level_code_list[8]='S000150' then dep_level_viewold.name_list[8]
     when dep_level_viewold.level_code_list[7]='S000150' then dep_level_viewold.name_list[7]
     when dep_level_viewold.level_code_list[6]='S000150' then dep_level_viewold.name_list[6]
     when dep_level_viewold.level_code_list[5]='S000150' then dep_level_viewold.name_list[5]
     when dep_level_viewold.level_code_list[4]='S000150' then dep_level_viewold.name_list[4]
     when dep_level_viewold.level_code_list[3]='S000150' then dep_level_viewold.name_list[3]
     when dep_level_viewold.level_code_list[2]='S000150' then dep_level_viewold.name_list[2]
     when dep_level_viewold.level_code_list[1]='S000150' then dep_level_viewold.name_list[1]
      end as 中心,

'' as "异动前部门",'' as "异动前职位",'' as "异动前层级",
'' as "异动前职级等级",'' as "异动前用工类型",'' as "异动前成本中心编码",'' as "异动前成本中心",'' as "调整类型",'9999-12-31' as "离职日期",perpe.x_hjadress as "联系地址",empl.business_phone as "手机号",
ir_cres.value as "证件类型",perp.national_id as "证件号码",
perpe.x_hjadress 户籍地址,
0.0::float 司龄,
'' 异动类型,
perpe.x_gjj_account_mount 公积金帐号,
roup1selection.name 员工类型,
COALESCE(ir_educa.value ,educa.name,'') as "最高学历",
COALESCE(ir_x_city.value ,x_city.name,'') as 城市

from
employee_rehire preh
left join hr_employee empl on preh.employee_id=empl.id
left join res_selection ress on empl.gender = ress.id
left join emp_job empj on empl.id = empj.employee_id
LEFT JOIN dep_level_view ON dep_level_view.cid = empj.department_id
LEFT JOIN dep_level_view dep_level_viewold ON dep_level_viewold.cid = preh.department_id

left join hr_department dept on empj.department_id = dept.id
left join department_cost_center_rel dept_cost on dept.id = dept_cost.department_id
left join res_selection resse on dept.x_bm_level = resse.id
left join emp_cost_distribution emp_cost on empl.id = emp_cost.employee_id
and emp_cost.start_date<=empl.termination_date
AND (emp_cost.end_date >=empl.termination_date OR emp_cost.end_date IS NULL)
and emp_cost.active=True
and emp_cost.is_primary=True
left join cost_center cost on emp_cost.cost_center_id = cost.id
left join x_builder_company comp on cost.x_lyfcompany_code = comp.id

left join job_classification jobc on empj.job_classification_id = jobc.id
left join employee_group empg on jobc.x_emp_childgroup1 = empg.id
LEFT JOIN ir_translation ir_empg
    ON ir_empg.res_id = empg.id AND
       ir_empg.name = 'employee.group,name' AND
       ir_empg.lang = 'zh_CN'
left join job_sequence jobs on empj.job_sequence_id = jobs.id
left join job_level jobl on jobc.job_level_id = jobl.id
left join employee_type empt on empj.employee_type = empt.id
LEFT JOIN ir_translation ir_empt
    ON ir_empt.res_id = empt.id AND
       ir_empt.name = 'employee.type,name' AND
       ir_empt.lang = 'zh_CN'
left join per_person perp on empl.person_id = perp.id
left join per_personal perpe on perp.per_personal = perpe.id
-- left join pre_hire preh on empl.person_id = preh.person_id
left join res_selection cres on perp.card_type = cres.id
LEFT JOIN ir_translation ir_cres
    ON ir_cres.res_id = cres.id AND
       ir_cres.name = 'res.selection,name' AND
       ir_cres.lang = 'zh_CN'
left join res_selection roup1selection on empj. x_emptype_empgroup1 =roup1selection.id
left join person_education pers on empl.person_id = pers.person_id and pers.is_highest_education = 't'
left join res_selection educa on pers.educational_background = educa.id
LEFT JOIN ir_translation ir_educa
    ON ir_educa.res_id = educa.id AND
       ir_educa.name = 'res.selection,name' AND
       ir_educa.lang = 'zh_CN'
-- 城市
LEFT JOIN res_selection x_city on empj.x_city = x_city.id
LEFT JOIN ir_translation ir_x_city
    ON ir_x_city.res_id = x_city.id AND
       ir_x_city.name = 'res.selection,name' AND
       ir_x_city.lang = 'zh_CN'

where empj.eroad_index_id is null
and  perpe.status = 'active'
and preh.x_bp_state='done')
