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级
  -- 合同信息
  case contract.contract_agreement_status when 'current_contract' then '当前合同' when 'expired_contract' then '失效合同' when 'non_effective_contract' then '未生效合同' end as contract_agreement_status, -- 合同状态
  contract_legal_entity.name AS contract_company,  --甲方信息
  contract.code AS contract_code,     --合同编号
  contract.contract_date_start AS contract_start, --合同开始日期
  contract.contract_date_end AS contract_end,   --合同结束日期
  agreement_translation.value AS contract_agreement, --合同类型
  category_translation.value AS contract_category,  --合同期限类型
  contract.signing_period AS contract_signing_period, --合同签订期限
  case contract.x_bp_state when 'draft' then '草稿'
                           when 'waiting' then '待审批'
                           when 'reject' then '已拒绝'
                           when 'cancel' then '已撤销'
                           when 'rollback' then '已打回'
                           when 'done' then '完成' 
                           else '草稿' end as contract_bp_state -- 合同审批状态
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 hr_contract AS contract on contract.employee_id = emp.id and contract.employee_id IS NOT NULL
LEFT JOIN legal_entity AS contract_legal_entity ON contract.legal_entity_id = contract_legal_entity.eroad_index_id AND
    contract_legal_entity.eroad_index_id IS NOT NULL AND
    contract_legal_entity.eroad_start_date <= current_date AND
    (contract_legal_entity.eroad_end_date >= current_date OR contract_legal_entity.eroad_end_date IS NULL) AND
    contract_legal_entity.active = TRUE
LEFT JOIN contract_agreement as agreement on contract.contract_agreement_id = agreement.id
LEFT JOIN ir_translation as agreement_translation
            ON agreement_translation.res_id = agreement.id and
                agreement_translation.lang = 'zh_CN' and
                agreement_translation.name = 'contract.agreement,name'
LEFT JOIN contract_category as category on contract.contract_category_id = category.id
LEFT JOIN ir_translation as category_translation
            ON category_translation.res_id = category.id and
                category_translation.lang = 'zh_CN' and
                category_translation.name = 'contract.category,name'