select distinct * from (
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
    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
        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
),
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.employee_number AS 员工工号,
        emp.name AS 员工姓名,
        emp.business_phone AS 手机号码,
        emp.hire_date AS 入职日期,
        emp.termination_date AS 离职日期,
--         tran_recruit_channel.value AS 招聘渠道,
        tran_recruitment_channels.value AS 招聘渠道,
--         tran_expenses_account.value AS 招聘费科目,
        tran_expenses_account.value AS 招聘费科目,
--         emp.x_recruitment_fee AS 招聘金额,
        x_builder_recruitment_messages.x_recruitment_fee AS 招聘金额,
--         emp.x_internal_recommendation AS 内部推荐人id,
        internal_employee.name AS 内部推荐人,
        x_builder_recruitment_messages.x_recruit_notes AS 招聘备注,
        x_builder_recruitment_messages.x_start_date AS 开始日期,
        x_builder_recruitment_messages.x_end_date AS 结束日期,
        tran_status.value AS 员工状态,
        tran_dep.value AS 部门,
        case emp_job.x_emp_service_type when '1' then 'SEC' when '2' then 'CLN'  when '3' then 'CAT' when '5' then 'IFM' when '6' then 'HRMS' when '7' then 'OTH' when '8' then 'HT' when '9' then 'FIN' when '10' then 'CS' when '11' then 'COM' when '12' then 'DIG' when '13' then 'OPS' when '14' then 'ADM' when '15' then 'LEG' when '16' then 'MGT' when '17' then 'QAM' when '18' then 'MTN' when '19' then 'AKI' when '20' then 'SMP' when '21' then 'ASAP' when '22' then 'ASAC' when '23' then 'ENGY' when '24' then 'MKT' when '25' then 'F&E' when '26' then 'COE' when '27' then 'TAM' when '28' then 'IT' end as 部门属性,
        tran_position.value AS 岗位,
        tran_job_level.value AS 职级,
        tran_legal_entity_salary.value AS 薪资发放主体,
        tran_emp_type.value AS 员工身份,
        case emp_job.x_emp_mode when '1' then 'Aden用工' when '2' then '派遣' when '3' then '外包' when '4' then '平台用工' when '5' then 'Aden用工-社保代理' end AS 用工形式,
        tran_country_1.value AS 工作地（国籍）,
        tran_country_2.value AS 工作地（省）,
        tran_country_3.value AS 工作地（市）,
        tran_country_4.value AS 工作地（区）,
        tran_cs.value AS 成本中心,
        dep_level_view.name_list[1] AS "一级组织单元名称",
        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 "七级组织单元名称"

-- 雇佣信息
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 res_country as country_1 on country_1.id = emp_job.x_work_place
LEFT JOIN ir_translation AS tran_country_1 ON tran_country_1.res_id = country_1.id AND tran_country_1.lang='zh_CN' AND tran_country_1.name='res.country,name'
LEFT JOIN res_country_state as country_2 on country_2.id = emp_job.x_work_place2
LEFT JOIN ir_translation AS tran_country_2 ON tran_country_2.res_id = country_2.id AND tran_country_2.lang='zh_CN' AND tran_country_2.name='res.country.state,name'
LEFT JOIN res_city as country_3 on country_3.id = emp_job.x_work_place3
LEFT JOIN ir_translation AS tran_country_3 ON tran_country_3.res_id = country_3.id AND tran_country_3.lang='zh_CN' AND tran_country_3.name='res.city,name'
LEFT JOIN res_town as country_4 on country_4.id = emp_job.x_work_place4
LEFT JOIN ir_translation AS tran_country_4 ON tran_country_4.res_id = country_4.id AND tran_country_4.lang='zh_CN' AND tran_country_4.name='res.town,name'

LEFT JOIN legal_entity AS legal_entity_salary ON
  emp_job.x_salary_company = legal_entity_salary.eroad_index_id AND
	legal_entity_salary.eroad_index_id IS NOT NULL AND
	legal_entity_salary.eroad_start_date <= current_date AND
	(legal_entity_salary.eroad_end_date >= current_date OR legal_entity_salary.eroad_end_date IS NULL) AND
	legal_entity_salary.active = TRUE
LEFT JOIN ir_translation AS tran_legal_entity_salary ON tran_legal_entity_salary.res_id = legal_entity_salary.id AND tran_legal_entity_salary.lang='zh_CN' AND tran_legal_entity_salary.name='legal.entity,name'

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 ir_translation AS tran_dep ON tran_dep.res_id = dep.id AND tran_dep.lang='zh_CN' AND tran_dep.name='hr.department,name'

LEFT JOIN organization_type as organization on organization.id = dep.organization_type_id
LEFT JOIN ir_translation AS tran_organization_type ON tran_organization_type.res_id = organization.id AND tran_organization_type.lang='zh_CN' AND tran_organization_type.name='organization.type,name'

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 ir_translation AS tran_position ON tran_position.res_id = position.id AND tran_position.lang='zh_CN' AND tran_position.name='hr.position,name'

LEFT JOIN employee_status AS emp_status ON emp_job.employee_status = emp_status.id
LEFT JOIN ir_translation AS tran_status ON tran_status.res_id = emp_status.id AND tran_status.lang='zh_CN' AND tran_status.name='employee.status,name'
LEFT JOIN employee_type AS emp_type ON emp_job.employee_type = emp_type.id
LEFT JOIN ir_translation AS tran_emp_type ON tran_emp_type.res_id = emp_type.id AND tran_emp_type.lang='zh_CN' AND tran_emp_type.name='employee.type,name'
LEFT JOIN job_level AS job_level on emp_job.job_level = job_level.id AND job_level.active = TRUE
LEFT JOIN ir_translation AS tran_job_level ON tran_job_level.res_id = job_level.id AND tran_job_level.lang='zh_CN' AND tran_job_level.name='job.level,name'

-- 招聘费科目 x_expenses_account
-- LEFT JOIN res_selection AS expenses_account ON emp.x_expenses_account = expenses_account.id
-- LEFT JOIN ir_translation AS tran_expenses_account ON tran_expenses_account.res_id = expenses_account.id AND tran_expenses_account.lang='zh_CN' AND tran_expenses_account.name='res.selection,name'
-- 招聘渠道 x_recruit_channel
-- LEFT JOIN res_selection AS recruit_channel ON emp.x_recruit_channel = recruit_channel.id
-- LEFT JOIN ir_translation AS tran_recruit_channel ON tran_recruit_channel.res_id = recruit_channel.id AND tran_recruit_channel.lang='zh_CN' AND tran_recruit_channel.name='res.selection,name'

LEFT JOIN emp_cost_distribution AS cost on cost.employee_id = emp.id and cost.is_primary = TRUE and cost.start_date <= current_date and (cost.end_date >= current_date or cost.end_date is null) and cost.active = True
LEFT JOIN cost_center as cs on cs.id=cost.cost_center_id
LEFT JOIN ir_translation AS tran_cs ON tran_cs.res_id = cs.id AND tran_cs.lang='zh_CN' AND tran_cs.name = 'cost.center,name'
-- x_internal_recommendation
-- LEFT JOIN hr_employee AS internal_employee ON emp.x_internal_recommendation = internal_employee.id

-- 招聘信息 x_builder_recruitment_messages x_recruitment_channels
LEFT JOIN x_builder_recruitment_messages ON x_builder_recruitment_messages.x_employee = emp_job.employee_id
LEFT JOIN res_selection AS recruitment_channels ON x_builder_recruitment_messages.x_recruitment_channels = recruitment_channels.id
LEFT JOIN ir_translation AS tran_recruitment_channels ON tran_recruitment_channels.res_id = recruitment_channels.id AND tran_recruitment_channels.lang='zh_CN' AND tran_recruitment_channels.name='res.selection,name'

-- 招聘信息 x_builder_recruitment_messages x_expenses_account
-- LEFT JOIN x_builder_recruitment_messages AS recruitment_messages ON recruitment_messages.x_employee = emp_job.employee_id
LEFT JOIN res_selection AS expenses_account ON x_builder_recruitment_messages.x_expenses_account = expenses_account.id
LEFT JOIN ir_translation AS tran_expenses_account ON tran_expenses_account.res_id = expenses_account.id AND tran_expenses_account.lang='zh_CN' AND tran_expenses_account.name='res.selection,name'

LEFT JOIN hr_employee AS internal_employee ON x_builder_recruitment_messages.x_internal_recommendation = internal_employee.id

LEFT JOIN dep_level_view ON dep_level_view.cid = emp_job.department_id

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) foo