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.x_15number,
  -- 15位员工工号
  emp.x_number,
  -- 7位员工编号
  emp.username,
  -- UASS
  emp.name,
  -- 员工姓名
  emp.hire_date,
  -- 入职日期
  hr_department.code department_code,
  -- 部门编码
  hr_department.name department_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级

  hr_position.name position_name,
  -- 岗位
CASE employee_status.name WHEN 'Active' THEN '在职' when 'Inactive' THEN '离职' when 'Pre-hire' then '预入职' when 'Abandonment' then '放弃入职' ELSE '' END
   employee_status,
  -- 员工状态
 manager_emp.name manager_emp_name,
  -- 汇报上级姓名
  manager_emp.employee_number manager_employee_number,
  -- 汇报上级8位工号
employee_type_translation.value employee_type_name,
  -- 员工类型
  yuangongleibie_selection.name x_yuangongleibie,
  -- 员工类别
 increasetype_selection.name x_increasetype,
  -- 增员类别
emp.x_hrbp_temp, -- 工作伙伴
relationship_type_selection.name relationship_type, -- 关系
per_person_relationship.first_name, -- 家属姓名
CASE gender_selection.name WHEN 'Male' THEN '男' when 'Female' THEN '女' else '' END gender,-- 性别
per_person_relationship.x_birthdate,-- 出生日期
per_person_relationship.x_shenfenzheng,-- 身份证号
edu_selection.name x_education,-- 文化程度
political_status.name x_political,-- 政治面貌
per_person_relationship.x_work,-- 工作单位
CASE per_person_relationship.x_jhyuangong WHEN 'f' THEN '否' when 't' THEN '是' else '' END x_jhyuangong,-- 是否建行员工
per_person_relationship.x_job,-- 职务
per_person_relationship.x_jianhangemployeeid,-- 建行员工编号
CASE per_person_relationship.x_is_agree WHEN 'f' THEN '否' when 't' THEN '是' else '' END x_is_agree,-- 声明确认
per_person_relationship.x_zhbianma -- 总行编码
from hr_employee emp
  inner 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 hr_department on emp_job.department_id = hr_department.id
  left join dep_level_view ON dep_level_view.cid = emp_job.department_id
  left join hr_position on emp_job.position_id = hr_position.id
left join employee_status on emp_job.employee_status = employee_status.id
  left join hr_employee manager_emp on emp_job.manager_employee_id = manager_emp.id
left join employee_type on emp_job.employee_type = employee_type.id
left join ir_translation employee_type_translation on employee_type_translation.src=employee_type.name
and employee_type_translation.lang='zh_CN'
and employee_type_translation.name='employee.type,name'
left join res_selection yuangongleibie_selection on emp_job.x_yuangongleibie = yuangongleibie_selection.id
left join res_selection increasetype_selection on emp_job.x_increasetype = increasetype_selection.id
left join per_person person on emp.person_id = person.id
left join per_person_relationship on per_person_relationship.person_id=person.id
left join res_selection relationship_type_selection on relationship_type_selection.id=per_person_relationship.relationship_type
left join res_selection gender_selection on per_person_relationship.x_gender=gender_selection.id
left join political_status on per_person_relationship.x_political=political_status.id
left join res_selection edu_selection on edu_selection.id=per_person_relationship.x_education
where emp.active=True