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,
  -- 部门名称
  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 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