select
emp.employee_number,
  -- 8位员工工号
  emp.x_15number,
  -- 15位员工工号
  emp.name,
  -- 员工姓名
  per_national_id.national_id,
  -- 身份证号码
CASE employee_status.name WHEN 'Active' THEN '在职' when 'Inactive' THEN '离职' when 'Pre-hire' then '预入职' when 'Abandonment' then '放弃入职' ELSE '' END
   employee_status,
  -- 员工状态
employee_resignation.confirmed_resignation_date,-- 离职生效日期
resignation_type.name x_reducetype, -- 减员类型
x_reducereason, -- 减员原因
resignation_reason.name resignation_reason, -- 离职原因类型
x_dimission_to, -- 离职去向
ir_translation_quxiang.value x_lizhiquxiangleixing, -- 离职去向类型
employee_resignation.notes, -- 离职原因说明
case when is_event_done='t' then '已审批' when is_event_done='f' then  '未审批' else '' end is_event_done, -- 是否审批
array_to_string(dep_full.dep_full_name,'/') dep_full_name, -- 部门
emp_job.eroad_start_date, -- 雇佣信息生效日期
case when emp_job.eroad_end_date is null then '2999-01-01' else emp_job.eroad_end_date end  -- 雇佣信息失效日期
from employee_resignation
left join hr_employee emp
on employee_resignation.employee_id=emp.id
left join per_person person on emp.person_id = person.id
left join per_national_id on person.id = per_national_id.person_id
left join emp_job on emp_job.employee_id=emp.id
left join employee_status on emp_job.employee_status = employee_status.id
left join resignation_type on employee_resignation.resignation_type=resignation_type.id
left join res_selection quxiang_selection on employee_resignation.x_lizhiquxiangleixing=quxiang_selection.id
left join ir_translation ir_translation_quxiang on quxiang_selection.id=ir_translation_quxiang.res_id and ir_translation_quxiang.name='res.selection,name'
left join resignation_reason on employee_resignation.resignation_reason=resignation_reason.id
left join (select
id, 
dep_id,
dep_name,
dep_code,
x_jigoucategory,
case when (dep_array::varchar[])[1]=dep_name then dep_array else dep_array||array[dep_name] end dep_full_name
from
(WITH RECURSIVE subordinates (id,dep_id,dep_name,dep_code,dep_array,x_jigoucategory) AS (
 SELECT
id,
 id dep_id,
 name dep_name,
 code dep_code,
 array[name] dep_array,
x_jigoucategory
 FROM
 hr_department
 WHERE
 id = (select hr_department.eroad_index_id from hr_department
where hr_department.eroad_start_date<=current_date
and (hr_department.eroad_end_date>=current_date or hr_department.eroad_end_date is null)
and hr_department.active=True
and eroad_current_record_id is null 
and parent_department_id is null
and status='active'
and code='JXJK0001')
 UNION all
 SELECT
s.id,
 eroad_index_id dep_id,
 name dep_name,
 code dep_code,
case when s.dep_name='建信金融科技有限责任公司' 
then array[s.dep_name]
else s.dep_array||array[s.dep_name]
end
 dep_array,
s.x_jigoucategory
 FROM
 hr_department e
 INNER JOIN subordinates s ON s.dep_id = e.parent_department_id
 where 
 e.eroad_start_date<=current_date
 and (e.eroad_end_date>=current_date or e.eroad_end_date is null)
and e.active=True
and e.eroad_current_record_id is null 
) select * from subordinates order by rpad(dep_code::varchar,100,'0') asc) dep_all) dep_full
on emp_job.department_id=dep_full.dep_id
where
per_national_id.is_primary = True or per_national_id.person_id is null
and emp_job.active=TRUE
and emp_job.EROAD_END_DATE<=employee_resignation.confirmed_resignation_date
and emp_job.EROAD_END_DATE is not null