--员工花名册-商务 select emp.employee_number, emp.name emp_name, tran_gender.value AS emp_gender, tran_si_location.value AS x_si_location, tran_emp_status.value AS emp_status, tran_employee_type.value AS employee_type, person.age FROM emp_job LEFT JOIN res_selection AS si_location ON emp_job.x_si_location_id = si_location.id LEFT JOIN ir_translation AS tran_si_location on tran_si_location.res_id=si_location.id and tran_si_location.lang='zh_CN' and tran_si_location.name='res.selection,name' LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.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' WHERE emp_type.id in (1,4) AND emp_status.id=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; --员工花名册-数字化运营 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.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 (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=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); – 员工花名册-招聘 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, tran_gender.value AS emp_gender, dep_level_view.name_list [-1] AS Team, buddy.name AS buddy, manager.name AS manager, --直接主管 department_manager.name AS department_manager,--直线经理 department_director.name AS department_director, --部门总监 person_education.school, --学校 person_education.profession, --专业 personal.x_estimated_graduation_date, --预计毕业时间 tran_emp_status.value AS emp_status, tran_employee_type.value AS employee_type, position.name AS position_name, person.age, 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 person_education ON emp.person_id = person_education.person_id AND person_education.is_highest_education = TRUE LEFT JOIN hr_employee manager ON emp_job.manager_employee_id = manager.id LEFT JOIN hr_employee department_manager ON emp_job.x_department_manager_id = department_manager.id LEFT JOIN hr_employee department_director ON emp_job.x_department_director_id = department_director.id LEFT JOIN hr_employee buddy ON emp_job.x_buddy = buddy.id LEFT JOIN per_person AS person ON emp.person_id = person.id – 个人信息 LEFT JOIN per_personal AS personal ON person.per_personal = personal.eroad_index_id AND personal.eroad_index_id IS NOT NULL AND personal.eroad_start_date <= current_date AND (personal.eroad_end_date >= current_date OR personal.eroad_end_date IS NULL) AND personal.active = TRUE 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,2,3,4,7) AND emp_status.id=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; – 员工花名册-IT 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, tran_gender.value AS emp_gender, dep_level_view.name AS Team, emp.email, --邮箱名 emp.hire_date, --入职日期 location.name AS base_locaition, --工作地点 buddy.name AS buddy, manager.name AS manager, --直接主管 department_manager.name AS department_manager, --直线经理 department_director.name AS department_director, --部门总监 tran_legal.value AS legal_entity_name, --所属公司 person_education.school, --学校 person_education.profession, --专业 personal.x_estimated_graduation_date, --预计毕业时间 tran_emp_status.value AS emp_status, --员工状态 tran_employee_type.value AS employee_type, --员工类型 position.name AS position_name, person.age, 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_location AS location on emp_job.location_id = location.id AND location.active = TRUE LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.id LEFT JOIN person_education ON emp.person_id = person_education.person_id AND person_education.is_highest_education = TRUE LEFT JOIN hr_employee manager ON emp_job.manager_employee_id = manager.id LEFT JOIN hr_employee department_manager ON emp_job.x_department_manager_id = department_manager.id LEFT JOIN hr_employee department_director ON emp_job.x_department_director_id = department_director.id LEFT JOIN hr_employee buddy ON emp_job.x_buddy = buddy.id LEFT JOIN per_person AS person ON emp.person_id = person.id – 个人信息 LEFT JOIN per_personal AS personal ON person.per_personal = personal.eroad_index_id AND personal.eroad_index_id IS NOT NULL AND personal.eroad_start_date <= current_date AND (personal.eroad_end_date >= current_date OR personal.eroad_end_date IS NULL) AND personal.active = TRUE 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 LEFT JOIN legal_entity AS legal_entity ON emp_job.legal_entity_id = legal_entity.eroad_index_id AND legal_entity.eroad_index_id IS NOT NULL AND legal_entity.eroad_start_date <= current_date AND (legal_entity.eroad_end_date >= current_date OR legal_entity.eroad_end_date IS NULL) AND legal_entity.active = TRUE LEFT JOIN ir_translation AS tran_legal on tran_legal.res_id=legal_entity.id and tran_legal.lang='zh_CN' and tran_legal.name='legal.entity,name' WHERE emp_type.id in (1,2,4,7) AND emp_status.id=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;