WITH RECURSIVE dep_level_view AS (
  select
    eroad_index_id as cid,
    name,
    code,
    ARRAY [name] as name_list,
    ARRAY [eroad_index_id] as path_ids,
    FALSE as is_cycle
  from
    hr_department
  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,
    dep_table.name,
    dep_table.code,
    dep_level_view.name_list || dep_table.name 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
  FROM
    hr_department dep_table
    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
),
emp_category AS (
  -- 员工标签
  SELECT
    emp_job_id AS emp_id,
    ARRAY_AGG(category.name) AS categories
  FROM
    category_hr_employee_rel
    LEFT JOIN hr_employee_category AS category ON category_hr_employee_rel.category_id = category.id
  GROUP BY
    emp_job_id
)
SELECT
  --   基本信息
  emp.name AS name,
  -- 员工姓名
  emp.employee_number AS emp_number,
  -- 员工工号
  case
    emp_gender.name
    when 'Male' then '男'
    when 'Female' then '女'
  end AS emp_gender,
  -- 员工性别
  person.age AS age,
  -- 年龄
  nation.name AS nation,
  --民族
  case
    marital_selection.name
    when 'Married' then '已婚'
    when 'Unmarried' then '未婚'
    when 'Divorced' then '离异'
  end AS marital_status,
  -- 婚姻状况
  political.name AS political,
  --政治面貌
  person.national_id AS card_national_id,
  -- 证件号码
  legal_entity.name AS legal_entity_name,
  -- 法人实体
  builder_fanwei.x_name AS x_rsfw,
  -- 人事范围
  gz_selection.name AS x_gzfw,
  -- 工资范围
  emp_job.x_cbzx AS cost_center_name,
  -- 成本中心
  emp_job.x_cbzxms AS cost_center_description,
  -- 成本中心描述
  emp_type.name AS emp_type,
  -- 员工类型
  emp_group.name AS employee_group,
  --  计薪方式
  dep_level_view.name_list [2] AS "一级组织",
  -- 一级部门
  dep_level_view.name_list [3] AS "二级组织",
  -- 一级部门
  dep_level_view.name_list [4] AS "三级组织",
  -- 三级部门
  dep_level_view.name_list [5] AS "四级组织",
  -- 四级部门
  dep_level_view.name_list [6] AS "五级组织",
  -- 五级部门
  dep_level_view.name_list [7] AS "六级组织",
  -- 六级部门
  position.code AS position_code,
  -- 岗位编码
  position.name AS position_name,
  -- 岗位
  job_sequence.name AS job_sequence,
  -- 职位序列
  job_level.name AS job_level,
  -- 职级
  leader_type_selection.name AS leader_type,
  --  干部类别
  emp_manager.name AS manager_employee,
  -- 汇报上级
  emp.hire_date AS hire_date,
  -- 入职日期
  case
    emp_status.name
    when 'Active' then '在职'
    when 'Inactive' then '离职'
    when 'Pre-hire' then '预入职'
  end AS emp_status,
  -- 员工状态
  emp.business_phone AS business_phone,
  -- 手机号码
  emp.email AS email,
  -- 邮箱地址
  address.address,
  -- 地址
  bank_information.bank_name AS bank_name,
  -- 开户银行
  bank_information.bank_account AS bank_account,
  -- 银行账户
  edu_background.name AS edu_background,
  -- 学历
  person_education.school AS edu_school,
  -- 学校
  edu_selection.name AS edu_selection,
  -- 院校性质
  person_education.profession AS profession,
  -- 专业
  emp.inner_working_age AS inner_working_age,
  -- 公司服务年限
  emp.social_working_age AS social_working_age,
  -- 社会服务年限
  case
    emp.is_probation
    when 'f' then '否'
    when 't' then '是'
  end AS is_probation,
  -- 是否在试用期
  emp.probation_date AS probation_date,
  -- 转正日期
  emp.termination_date AS termination_date,
  -- 离职日期
  -- 雇佣信息
  1 as head_count
FROM
 emp_job
  LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.id
  LEFT JOIN emp_category ON emp_job.employee_id = emp_category.emp_id
  left join x_builder_fanwei as builder_fanwei on builder_fanwei.id = emp_job.x_rsfw
  left join res_selection as gz_selection on gz_selection.id = emp_job.x_gzfw
  left join (select hy_appointment.* from hy_appointment inner join (select max(hy_appointment.id) as max_appiontment_id,hy_appointment.employee_id from hy_appointment group by hy_appointment.employee_id) as appo on hy_appointment.id = appo.max_appiontment_id) as appointment on appointment.employee_id = emp.id
  left join res_selection as leader_type_selection on leader_type_selection.id = appointment.leader_type

left join hr_employee_partner as partner on partner.employee_id = emp.id
  and relation_id = 1
  and partner.start_date <= current_date
  and (
    partner.end_date is null
    or partner.end_date >= current_date
  )
  left join hr_employee as emp_partner on emp_partner.id = partner.partner_id
  LEFT JOIN res_selection AS emp_gender ON emp.gender = emp_gender.id
  LEFT JOIN legal_entity AS legal_entity ON emp_job.legal_entity_id = legal_entity.eroad_index_id
  AND legal_entity.eroad_index_id IS NOT NULL
  AND legal_entity.eroad_start_date <= current_date
  AND (
    legal_entity.eroad_end_date >= current_date
    OR legal_entity.eroad_end_date IS NULL
  )
  AND legal_entity.active = TRUE
LEFT JOIN hr_business_unit AS bu ON emp_job.business_unit_id = bu.eroad_index_id
  AND bu.eroad_index_id IS NOT NULL
  AND bu.eroad_start_date <= current_date
  AND (
    bu.eroad_end_date >= current_date
    OR bu.eroad_end_date IS NULL
  )
  AND bu.active = TRUE
  LEFT JOIN hr_division AS division ON emp_job.division_id = division.eroad_index_id
  AND division.eroad_index_id IS NOT NULL
  AND division.eroad_start_date <= current_date
  AND (
    division.eroad_end_date >= current_date
    OR division.eroad_end_date IS NULL
  )
  AND division.active = TRUE
  LEFT JOIN hr_department AS dep ON emp_job.department_id = dep.eroad_index_id
  AND dep.eroad_index_id IS NOT NULL
  AND dep.eroad_start_date <= current_date
  AND (
    dep.eroad_end_date >= current_date
    OR dep.eroad_end_date IS NULL
  )
  AND dep.active = TRUE
  LEFT JOIN hr_position AS position ON emp_job.position_id = position.eroad_index_id
  AND position.eroad_index_id IS NOT NULL
  AND position.eroad_start_date <= current_date
  AND (
    position.eroad_end_date >= current_date
    OR position.eroad_end_date IS NULL
  )
  AND position.active = TRUE
  LEFT JOIN job_classification AS job_classification ON emp_job.job_classification_id = job_classification.eroad_index_id
  AND job_classification.eroad_index_id IS NOT NULL
  AND job_classification.eroad_start_date <= current_date
  AND (
    job_classification.eroad_end_date >= current_date
    OR job_classification.eroad_end_date IS NULL
  )
  AND job_classification.active = TRUE
  LEFT JOIN cost_center ON emp_job.cost_center_id = cost_center.id
  AND cost_center.active = TRUE
  LEFT JOIN hr_location AS location on emp_job.location_id = location.id
  AND location.active = TRUE
  LEFT JOIN res_country AS emp_country on emp_job.country = emp_country.id
  AND emp_country.active = TRUE
  LEFT JOIN employee_status emp_status ON emp_job.employee_status = emp_status.id
  LEFT JOIN employee_type AS emp_type ON emp_job.employee_type = emp_type.id
  LEFT JOIN employee_group AS emp_group on emp_job.employee_group = emp_group.id
  AND emp_group.active = TRUE
  LEFT JOIN hr_employee AS emp_manager on emp_job.manager_employee_id = emp_manager.id
  AND emp_manager.active = TRUE
  LEFT JOIN job_sequence AS job_sequence on emp_job.job_sequence_id = job_sequence.id
  AND job_sequence.active = TRUE
  LEFT JOIN job_function AS job_function on emp_job.job_function_id = job_function.id
  AND job_function.active = TRUE
  LEFT JOIN job_grade AS job_grade on emp_job.job_grade_id = job_grade.id
  AND job_grade.active = TRUE
  LEFT JOIN job_level AS job_level on emp_job.job_level = job_level.id
  AND job_level.active = TRUE
  LEFT JOIN pay_grade AS pay_grade on emp_job.pay_grade = pay_grade.id
  AND pay_grade.active = TRUE
  LEFT JOIN pay_level AS pay_level on emp_job.pay_level = pay_level.id -- 自然人
  LEFT JOIN per_person AS person ON emp.person_id = person.id -- 证件类型
  LEFT JOIN res_selection AS card_type ON person.card_type = card_type.id -- 邮箱信息
  LEFT JOIN per_email AS per_email ON person.id = per_email.person_id
  AND per_email.is_primary = TRUE
  LEFT JOIN res_selection AS email_type ON per_email.email_type = email_type.id -- 电话信息
  LEFT JOIN per_phone AS per_phone ON person.id = per_phone.person_id
  AND per_phone.is_primary = TRUE
  LEFT JOIN res_selection AS phone_type ON per_phone.phone_type = phone_type.id -- 银行卡信息
  LEFT JOIN (
    SELECT
      person_id,
      max(id) AS information_id
    FROM
      person_bank_information
    WHERE
      person_id IS NOT NULL
    GROUP BY
      person_id
  ) AS bank_rel ON person.id = bank_rel.person_id
  LEFT JOIN person_bank_information AS bank_information ON bank_rel.information_id = bank_information.id
  LEFT JOIN res_country AS country_of_bank ON bank_information.country_of_bank = country_of_bank.id
  LEFT JOIN res_selection AS bank_identification_number_type ON bank_information.bank_identification_number_type = bank_identification_number_type.id
  LEFT JOIN res_selection AS bank_use ON bank_information.use = bank_use.id
  LEFT JOIN person_education ON emp.person_id = person_education.person_id -- 学历信息
  AND person_education.is_highest_education = TRUE
  LEFT JOIN (
    select
      hy_address.employee_id,
      hy_address.address,
      address_selection.code
    from
      hy_address
      left join res_selection as address_selection on hy_address.address_type = address_selection.id
    where
      address_selection.code = 'dz_1' and hy_address.id in (select max_id from (select max(id) as max_id,employee_id from hy_address group by employee_id) as addr)
  ) as address on address.employee_id = emp.id -- 地址信息
left join res_selection as edu_selection on edu_selection.id = person_education.x_yxxz
  LEFT JOIN res_selection AS edu_method ON person_education.study_method = edu_method.id
  LEFT JOIN res_selection AS edu_background ON person_education.educational_background = edu_background.id
  LEFT JOIN res_selection AS academic_degree ON person_education.academic_degree = academic_degree.id -- 工作信息
  LEFT JOIN (
    SELECT
      person_id,
      max(id) AS work_experience_id
    FROM
      person_work_experience
    WHERE
      person_id IS NOT NULL
    GROUP BY
      person_id
  ) AS work_experience_rel ON person.id = work_experience_rel.person_id
  LEFT JOIN person_work_experience AS person_work ON work_experience_rel.work_experience_id = person_work.id
  LEFT JOIN res_selection AS certifier_relationship ON person_work.certifier_relationship = certifier_relationship.id -- 联系人信息
  LEFT JOIN (
    SELECT
      person_id,
      max(id) AS emergency_contact_id
    FROM
      emergency_contact
    WHERE
      person_id IS NOT NULL
    GROUP BY
      person_id
  ) AS emergency_contact_rel ON person.id = emergency_contact_rel.person_id
  LEFT JOIN emergency_contact AS emergency_contact ON emergency_contact_rel.emergency_contact_id = emergency_contact.id
  LEFT JOIN res_selection AS emergency_contact_relationship ON emergency_contact.relationship = emergency_contact_relationship.id -- 个人信息
  LEFT JOIN per_personal AS personal ON person.per_personal = personal.eroad_index_id
  AND personal.eroad_index_id IS NOT NULL
  AND personal.eroad_start_date <= current_date
  AND (
    personal.eroad_end_date >= current_date
    OR personal.eroad_end_date IS NULL
  )
  AND personal.active = TRUE
  LEFT JOIN res_country AS country ON personal.nationality_id = country.id
  LEFT JOIN res_nation AS nation ON personal.nation = nation.id
  LEFT JOIN res_country_state AS country_state ON personal.native_place = country_state.id
  LEFT JOIN political_status AS political ON personal.political_status = political.id
  LEFT JOIN res_selection AS children_situation ON personal.children_situation = children_situation.id
  LEFT JOIN dep_level_view ON dep_level_view.cid = emp_job.department_id
  left join res_selection as marital_selection on marital_selection.id = personal.marital_status


WHERE
  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
order by
emp_number