1. 由于需要取得各时间点的 员工分组统计，取时间轴数据，抽取所有emp.job表的信息，通过对报表增加过滤组件，对字段start_date & end_date进行过滤设置，已达到取某个时间点的emp_job信息。例如： start_date<= 某个时间点 <= end_date
2. 需要抽取员工的字段信息参考 附件中截图的数据集
3. 增加字段【统计部门】，该信息为员工身上部门的最上级部门，该处需要递归查询
4. 仅抽取 业务单元为 艺术设计中心（BU002），以及部门为3D角色部（BP007）的员工信息
5. 透视表中的比例，当前功能貌似不可实现？：
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
dep_level_view.name_list[1] 统计部门,
emp.id,
emp_job.active,
emp_job.status,
emp_job.eroad_start_date AS start_date,
CASE WHEN emp_job.eroad_end_date is null THEN to_date('9999-12-31','YYYY-MM-DD') ELSE emp_job.eroad_end_date END AS end_date,
emp.employee_number, – 员工工号
emp.name AS emp_name, – 员工姓名
CASE emp_status.name
WHEN 'Active' THEN '在职'
WHEN 'Inactive' THEN '离职'
ELSE '预入职' END AS emp_status, – 员工状态
CASE emp_type.name
WHEN 'Regular Employee' THEN '正式员工'
WHEN 'Temporary Employee' THEN '临时员工'
WHEN 'Trainee' THEN '实习生'
WHEN 'Outsourced Employee' THEN '外包员工'
WHEN 'Contractor' THEN '劳务派遣'
WHEN 'Part-Time Employee' THEN '兼岗员工'
WHEN 'Pre-Position Employee' THEN '岗前员工'
ELSE '试用员工' END AS emp_type, – 员工类型
emp.hire_date, – 入职日期
emp.termination_date, – 离职日期
COALESCE(legal_entity_irt.value, legal_entity.name, '') AS legal_entity_name, – 法人实体
COALESCE(bu_irt.value, bu.name, '') AS bu_name, – 业务单元
COALESCE(division_irt.value, division.name, '') AS divison_name, – 分部
COALESCE(dep_irt.value, dep.name, '') AS dep_name, – 部门
COALESCE(position_irt.value, position.name, '') AS position_name, – 岗位
COALESCE(emp_group_irt.value, emp_group.name, '') AS employee_group, – 员工分组
emp_manager.name AS manager_employee – 上级
FROM emp_job
left join
dep_level_view ON dep_level_view.cid = emp_job.department_id
LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.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 legal_entity_irt ON
legal_entity_irt.res_id = legal_entity.id AND
legal_entity_irt.name = 'legal.entity,name' AND
legal_entity_irt.lang = #
{system.lang}
LEFT JOIN hr_business_unit AS bu ON
emp_job.business_unit_id = bu.eroad_index_id AND
bu.eroad_index_id IS NOT NULL AND
bu.eroad_start_date <= current_date AND
(bu.eroad_end_date >= current_date OR bu.eroad_end_date IS NULL) AND
bu.active = TRUE
LEFT JOIN ir_translation AS bu_irt ON
bu_irt.res_id = bu.id AND
bu_irt.name = 'hr.business.unit,name' AND
bu_irt.lang = #{system.lang}
LEFT JOIN hr_division AS division ON
emp_job.division_id = division.eroad_index_id AND
division.eroad_index_id IS NOT NULL AND
division.eroad_start_date <= current_date AND
(division.eroad_end_date >= current_date OR division.eroad_end_date IS NULL) AND
division.active = TRUE
LEFT JOIN ir_translation AS division_irt ON
division_irt.res_id = division.id AND
division_irt.name = 'hr.division,name' AND
division_irt.lang = #
{system.lang}
LEFT JOIN hr_department AS dep ON
emp_job.department_id = dep.eroad_index_id AND
dep.eroad_index_id IS NOT NULL AND
dep.eroad_start_date <= current_date AND
(dep.eroad_end_date >= current_date OR dep.eroad_end_date IS NULL) AND
dep.active = TRUE
LEFT JOIN ir_translation AS dep_irt ON
dep_irt.res_id = dep.id AND
dep_irt.name = 'hr.department,name' AND
dep_irt.lang = #{system.lang}
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 ir_translation AS position_irt ON
position_irt.res_id = position.id AND
position_irt.name = 'hr.position,name' AND
position_irt.lang = #
{system.lang}
LEFT JOIN employee_status emp_status ON emp_job.employee_status = emp_status.id
LEFT JOIN employee_type AS emp_type ON emp_job.employee_type = emp_type.id
LEFT JOIN employee_group AS emp_group on emp_job.employee_group = emp_group.id AND emp_group.active = TRUE
LEFT JOIN ir_translation AS emp_group_irt ON
emp_group_irt.res_id = emp_group.id AND
emp_group_irt.name = 'employee.group,name' AND
emp_group_irt.lang = #{system.lang}
LEFT JOIN hr_employee AS emp_manager on emp_job.manager_employee_id = emp_manager.id AND emp_manager.active = TRUE
WHERE
emp_job.eroad_index_id IS NOT NULL AND
emp_job.active = TRUE AND
emp_job.employee_id IS NOT NULL AND
(bu.code = 'BU002' or dep.code = 'DP007')
ORDER BY emp.id;
High 
杨光磊
罗军