sql:
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
– 基本信息
1 人头数,
emp.employee_number, – 员工工号
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_trans.value 当前部门,
employee_type_trans.value 员工类型,
employee_status_trans.value 员工状态,
employee_group_trans.value 员工分组,
hr_location_trans.value 地区,
hr_position_trans.value 岗位,
legal_entity_trans.value 法人实体,
marital_selection_trans.value 婚姻状况
– 雇佣信息
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.person_id=per_person.id
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 employee_type on emp_job.employee_type=employee_type.id
left join ir_translation employee_type_trans on employee_type_trans.res_id=employee_type.id
and employee_type_trans.name ='employee.type,name'
and employee_type_trans.lang='zh_CN'
left join employee_status on emp_job.employee_status= employee_status.id
left join ir_translation employee_status_trans on employee_status_trans.res_id=employee_status.id
and employee_status_trans.name ='employee.status,name'
and employee_status_trans.lang='zh_CN'
left join employee_group on emp_job.employee_group= employee_group.id
left join ir_translation employee_group_trans on employee_group_trans.res_id=employee_group.id
and employee_group_trans.name ='employee.group,name'
and employee_group_trans.lang='zh_CN'
left join hr_location on emp_job.location_id=hr_location.id
left join ir_translation hr_location_trans on hr_location_trans.res_id=hr_location.id
and hr_location_trans.name ='hr.location,name'
and hr_location_trans.lang='zh_CN'
left join hr_position on emp_job.position_id=hr_position.id
left join ir_translation hr_position_trans on hr_position_trans.res_id=hr_position.id
and hr_position_trans.name ='hr.position,name'
and hr_position_trans.lang='zh_CN'
left join legal_entity on emp_job.legal_entity_id=legal_entity.id
left join ir_translation legal_entity_trans on legal_entity_trans.res_id=legal_entity.id
and legal_entity_trans.name ='legal.entity,name'
and legal_entity_trans.lang='zh_CN'
left join res_selection marital_selection
on per_personal.marital_status=marital_selection.id
left join ir_translation marital_selection_trans on marital_selection_trans.res_id=marital_selection.id
and marital_selection_trans.name ='res.selection,name'
and marital_selection_trans.lang='zh_CN'
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;
Medium 
杨光磊
韩玮
