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
)
select
emp.employee_number,
emp.name emp_name,
emp.hire_date, --入职日期
emp.termination_date, --离职日期
emp.email, --邮箱名
manager.name AS manager, --直接主管
department_director.name AS department_director, --部门总监
tran_gender.value AS emp_gender,
tran_emp_status.value AS emp_status,
tran_employee_type.value AS employee_type,
position.name AS position_name,
person.age,
dep_level_view.path_ids[1] AS "一级部门id",
dep_level_view.path_ids[2] AS "二级部门id",
dep_level_view.path_ids[3] AS "三级部门id",
dep_level_view.path_ids[4] AS "四级部门id",
dep_level_view.path_ids[5] AS "五级部门id",
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 "五级部门"
FROM emp_job
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 hr_employee emp ON emp_job.employee_id = emp.id
LEFT JOIN hr_employee manager ON emp_job.manager_employee_id = manager.id
LEFT JOIN hr_employee department_director ON emp_job.x_department_director_id = department_director.id
LEFT JOIN per_person AS person ON emp.person_id = person.id
LEFT JOIN res_selection AS emp_gender ON emp.gender = emp_gender.id
LEFT JOIN ir_translation AS tran_gender on tran_gender.res_id=emp_gender.id and tran_gender.lang='zh_CN' and tran_gender.name='res.selection,name'
LEFT JOIN employee_type AS emp_type ON emp_job.employee_type = emp_type.id
LEFT JOIN ir_translation AS tran_employee_type on tran_employee_type.res_id=emp_type.id and tran_employee_type.lang='zh_CN' and tran_employee_type.name='employee.type,name'
LEFT JOIN employee_status emp_status ON emp_job.employee_status = emp_status.id
LEFT JOIN ir_translation AS tran_emp_status on tran_emp_status.res_id=emp_status.id and tran_emp_status.lang='zh_CN' and tran_emp_status.name='employee.status,name'
LEFT JOIN dep_level_view ON dep_level_view.cid = emp_job.department_id
WHERE (emp_type.id in (1,4) AND emp_status.id=2 and dep_level_view.name_list[1] = '销售市场部' and dep_level_view.name_list[2] != '市场部'
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) or (emp_type.id in (1,4) AND emp_status.id=3 and dep_level_view.name_list[1] = '销售市场部' and dep_level_view.name_list[2] != '市场部'
AND emp_job.eroad_index_id IS NOT NULL AND
emp_job.eroad_start_date <= emp.termination_date AND
(emp_job.eroad_end_date >= emp.termination_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
High
刘文强
卢桂香