代码：
WITH RECURSIVE dep_level_view AS (
select
eroad_index_id as cid,
dep_trans.value,code,
ARRAY[dep_trans.value] as name_list,
ARRAY[eroad_index_id] as path_ids,
FALSE as is_cycle
from hr_department
left join ir_translation dep_trans on dep_trans.res_id=hr_department.id
and dep_trans.name ='hr.department,name'
and dep_trans.lang='zh_CN'
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_transt.value,
dep_table.code,
dep_level_view.name_list || dep_transt.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 dep_transt on dep_transt.res_id=dep_table.id
and dep_transt.name ='hr.department,name'
and dep_transt.lang='zh_CN'
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 员工工号,
emp.name 姓名,
hr_department_trans.value 部门,
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 "五级部门",
hr_department.code 部门代号,
emp.hire_date 入职日期,
emp. termination_date 离职日期,
person_bank_information.bank_account 银行账号
– 雇佣信息
FROM emp_job
LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.id
left join per_person on emp.person_id=per_person.id
left join per_personal on per_personal.id=per_person.per_personal
LEFT JOIN dep_level_view ON dep_level_view.cid = emp_job.department_id
left join hr_department on emp_job.department_id=hr_department.id
left join ir_translation hr_department_trans on hr_department_trans.res_id=hr_department.id
and hr_department_trans.name ='hr.department,name'
and hr_department_trans.lang='zh_CN'
left join (
select max(id) id,
person_id
from person_bank_information
group by person_id) sub_bank
on sub_bank.person_id=per_person.id
left join person_bank_information
on person_bank_information.person_id=sub_bank.person_id
and sub_bank.id=person_bank_information.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
Change
Medium
杨光磊
张鹏远
