WITH RECURSIVE dep_level_view AS (
    select
        eroad_index_id as cid,
        tran_dep1.value as name,code,
        ARRAY[tran_dep1.value] as name_list,
        ARRAY[eroad_index_id] as path_ids,
        FALSE as is_cycle,
        x_jigoucategory
    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'
    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 active and
          parent_department_id is NULL
    UNION
        SELECT
            dep_table.eroad_index_id as cid,
            tran_dep2.value as name,
            dep_table.code,
            dep_level_view.name_list || tran_dep2.value as name_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,
            dep_table.x_jigoucategory
        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'

        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
  emp.employee_number,
  -- 8位员工工号
  emp.name,
  -- 员工姓名
CASE employee_status.name WHEN 'Active' THEN '在职' when 'Inactive' THEN '离职' when 'Pre-hire' then '预入职' when 'Abandonment' then '放弃入职' ELSE '' END
   employee_status,
  -- 员工状态
  employee_type_translation.value employee_type_name, 
  --员工类型
  dep_level_view.name_list[1] dep_name1, -- 部门1级
  dep_level_view.name_list[2] dep_name2, -- 部门2级
  dep_level_view.name_list[3] dep_name3,-- 部门3级
  dep_level_view.name_list[4] dep_name4,-- 部门4级
  dep_level_view.name_list[5] dep_name5, -- 部门5级
  emergency_contact.name AS emergency_contact_name,          -- 紧急联系人名称
  emergency_contact.phone AS emergency_contact_phone,         -- 紧急联系人号码
  emergency_contact.address AS emergency_contact_address,     -- 紧急联系人地址
  emergency_contact_relationship_ir_translation.value AS emergency_contact_relationship  -- 紧急联系人关系
from hr_employee emp
left join emp_job on emp.id = emp_job.employee_id and
    emp_job.eroad_index_id IS NOT NULL and
    emp_job.eroad_start_date <= current_date AND
    (emp_job.eroad_end_date >= current_date OR emp_job.eroad_end_date IS NULL) AND
    emp_job.status = 'active' AND
    emp_job.active = TRUE AND
    emp_job.employee_id IS NOT NULL
left join employee_status on emp_job.employee_status = employee_status.id
left join ir_translation employee_type_translation on employee_type_translation.res_id=emp_job.employee_type and employee_type_translation.lang='zh_CN' and employee_type_translation.name='employee.type,name'
left join dep_level_view ON dep_level_view.cid = emp_job.department_id

LEFT JOIN emergency_contact AS emergency_contact
        ON emp.person_id = emergency_contact.person_id
LEFT JOIN res_selection AS emergency_contact_relationship
        ON emergency_contact.relationship = emergency_contact_relationship.id
LEFT JOIN ir_translation as emergency_contact_relationship_ir_translation
        ON emergency_contact_relationship_ir_translation.res_id = emergency_contact_relationship.id and
            emergency_contact_relationship_ir_translation.lang = 'zh_CN' and
            emergency_contact_relationship_ir_translation.name = 'res.selection,name'