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,
x_jigoucategory
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,
dep_table.x_jigoucategory
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
)
select
emp.employee_number,
-- 8位员工工号
emp.x_15number,
-- 15位员工工号
emp.x_number,
-- 7位员工编号
emp.username,
-- UASS
emp.name,
-- 员工姓名
emp.hire_date,
-- 入职日期
hr_department.code department_code,
-- 部门编码
hr_department.name department_name,
-- 部门名称
dep_level_view.name_list[1] dep_name1, -- 部门1级
dep_level_view.name_list[2] dep_name2, -- 部门2级
dep_level_view.name_list[3] dep_name3,-- 部门3级
dep_level_view.name_list[4] dep_name4,-- 部门4级
dep_level_view.name_list[5] dep_name5, -- 部门5级
hr_position.name position_name,
-- 岗位
CASE employee_status.name WHEN 'Active' THEN '在职' when 'Inactive' THEN '离职' when 'Pre-hire' then '预入职' when 'Abandonment' then '放弃入职' ELSE '' END
employee_status,
-- 员工状态
manager_emp.name manager_emp_name,
-- 汇报上级姓名
manager_emp.employee_number manager_employee_number,
-- 汇报上级8位工号
employee_type_translation.value employee_type_name,
-- 员工类型
yuangongleibie_selection.name x_yuangongleibie,
-- 员工类别
increasetype_selection.name x_increasetype,
-- 增员类别
emp.x_hrbp_temp, -- 工作伙伴
relationship_type_selection.name relationship_type, -- 关系
per_person_relationship.first_name, -- 家属姓名
CASE gender_selection.name WHEN 'Male' THEN '男' when 'Female' THEN '女' else '' END gender,-- 性别
per_person_relationship.x_birthdate,-- 出生日期
per_person_relationship.x_shenfenzheng,-- 身份证号
edu_selection.name x_education,-- 文化程度
political_status.name x_political,-- 政治面貌
per_person_relationship.x_work,-- 工作单位
CASE per_person_relationship.x_jhyuangong WHEN 'f' THEN '否' when 't' THEN '是' else '' END x_jhyuangong,-- 是否建行员工
per_person_relationship.x_job,-- 职务
per_person_relationship.x_jianhangemployeeid,-- 建行员工编号
CASE per_person_relationship.x_is_agree WHEN 'f' THEN '否' when 't' THEN '是' else '' END x_is_agree,-- 声明确认
per_person_relationship.x_zhbianma -- 总行编码
from hr_employee emp
inner join emp_job on emp.id = emp_job.employee_id 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
left join hr_department on emp_job.department_id = hr_department.id
left join dep_level_view ON dep_level_view.cid = emp_job.department_id
left join hr_position on emp_job.position_id = hr_position.id
left join employee_status on emp_job.employee_status = employee_status.id
left join hr_employee manager_emp on emp_job.manager_employee_id = manager_emp.id
left join employee_type on emp_job.employee_type = employee_type.id
left join ir_translation employee_type_translation on employee_type_translation.src=employee_type.name
and employee_type_translation.lang='zh_CN'
and employee_type_translation.name='employee.type,name'
left join res_selection yuangongleibie_selection on emp_job.x_yuangongleibie = yuangongleibie_selection.id
left join res_selection increasetype_selection on emp_job.x_increasetype = increasetype_selection.id
left join per_person person on emp.person_id = person.id
left join per_person_relationship on per_person_relationship.person_id=person.id
left join res_selection relationship_type_selection on relationship_type_selection.id=per_person_relationship.relationship_type
left join res_selection gender_selection on per_person_relationship.x_gender=gender_selection.id
left join political_status on per_person_relationship.x_political=political_status.id
left join res_selection edu_selection on edu_selection.id=per_person_relationship.x_education
where emp.active=True
Change
Medium
