-
Change
-
解决结果: 完成
-
Medium
-
无
-
L-来伊份-Y2020036
-
PRO
-
基础通用功能
-
隐藏
sql:
select distinct
comp.x_company_code as 公司代码,
comp.x_company_name as 公司名称,
case
when dep0.x_bm_level=256 then COALESCE(dep0_trans.value,dep0.name)
when dep1.x_bm_level=256 then COALESCE(dep1_trans.value,dep1.name)
when dep2.x_bm_level=256 then COALESCE(dep2_trans.value,dep2.name)
when dep3.x_bm_level=256 then COALESCE(dep3_trans.value,dep3.name)
when dep4.x_bm_level=256 then COALESCE(dep4_trans.value,dep4.name)
when dep5.x_bm_level=256 then COALESCE(dep5_trans.value,dep5.name)
when dep6.x_bm_level=256 then COALESCE(dep6_trans.value,dep6.name)
when dep7.x_bm_level=256 then COALESCE(dep7_trans.value,dep7.name)
when dep8.x_bm_level=256 then COALESCE(dep8_trans.value,dep8.name)
else '' end 系统,
case
when dep0.x_bm_level=257 then COALESCE(dep0_trans.value,dep0.name)
when dep1.x_bm_level=257 then COALESCE(dep1_trans.value,dep1.name)
when dep2.x_bm_level=257 then COALESCE(dep2_trans.value,dep2.name)
when dep3.x_bm_level=257 then COALESCE(dep3_trans.value,dep3.name)
when dep4.x_bm_level=257 then COALESCE(dep4_trans.value,dep4.name)
when dep5.x_bm_level=257 then COALESCE(dep5_trans.value,dep5.name)
when dep6.x_bm_level=257 then COALESCE(dep6_trans.value,dep6.name)
when dep7.x_bm_level=257 then COALESCE(dep7_trans.value,dep7.name)
when dep8.x_bm_level=257 then COALESCE(dep8_trans.value,dep8.name)
else '' end 中心子公司,
COALESCE(dep0_trans.value,dep0.name) 直属部门名称,
dep0.x_shopid 门店编码,
dep0.x_mdname 门店名称,
hr_employee.employee_number 员工工号,
hr_employee.name 员工姓名,
COALESCE(pos_trans.value,hr_position.name) 离职前职位,
COALESCE(employee_type_trans.value,employee_type.name) 员工组,
employee_group.code 员工子组ID,
COALESCE(employee_group_trans.value,employee_group.name) 员工子组,
hr_employee.hire_date 入职日期,
hr_employee.termination_date 离职日期,
COALESCE(resignation_type_trans.value,resignation_type.name) 离职类型,
COALESCE(resignation_reason_trans.value,resignation_reason.name) 离职原因,
hr_employee.first_date_worked 司龄开始日期,
case when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=0
and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<6
then '0-6月'
when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=6
and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<12
then '6个月-1年'
when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=12
and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<24
then '1-2年'
when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=24
and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<36
then '2-3年'
when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=36
and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<60
then '3-5年'
when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=60
and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<120
then '5-10年'
when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=120
then '10年及以上'
else '无司龄' end 司龄结构,
round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12
+ extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked))
+(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1) 司龄,
manager.employee_number 上级工号,
manager.name 上级姓名,
manager2.employee_number 隔级上级工号,
manager2.name 隔级上级姓名,
cost_center.code 成本中心,
COALESCE(cost_center_trans.value,cost_center.name) 成本中心名称
from employee_resignation
left join hr_employee on employee_resignation.employee_id=hr_employee.id
left join emp_job on emp_job.employee_id=hr_employee.id
left join hr_department dep0
on emp_job.department_id=dep0.eroad_index_id
and dep0.eroad_index_id IS NOT NULL
and hr_employee.termination_date>=dep0.eroad_start_date
and (hr_employee.termination_date<=dep0.eroad_end_date or dep0.eroad_end_date is null)
left join ir_translation dep0_trans on dep0_trans.res_id=dep0.id
and dep0_trans.name ='hr.department,name'
and dep0_trans.lang='zh_CN'left join department_cost_center_rel dept_cost on dep0.id = dept_cost.department_id
left join cost_center on dept_cost.cost_center_id = cost_center.id
left join ir_translation cost_center_trans on cost_center_trans.res_id=cost_center.id
and cost_center_trans.name ='cost.center,name'
and cost_center_trans.lang='zh_CN'
left join x_builder_company comp on cost_center.x_lyfcompany_code = comp.id
left join hr_department dep1 on dep0.parent_department_id = dep1.eroad_index_id
and dep1.eroad_index_id IS NOT NULL AND
dep1.eroad_start_date <= hr_employee.termination_date AND
(dep1.eroad_end_date >= hr_employee.termination_date OR dep1.eroad_end_date IS NULL)
left join ir_translation dep1_trans on dep1_trans.res_id=dep1.id
and dep1_trans.name ='hr.department,name'
and dep1_trans.lang='zh_CN'left join hr_department dep2 on dep1.parent_department_id = dep2.eroad_index_id
and dep2.eroad_index_id IS NOT NULL AND
dep2.eroad_start_date <= hr_employee.termination_date AND
(dep2.eroad_end_date >= hr_employee.termination_date OR dep2.eroad_end_date IS NULL)
left join ir_translation dep2_trans on dep2_trans.res_id=dep2.id
and dep2_trans.name ='hr.department,name'
and dep2_trans.lang='zh_CN'left join hr_department dep3 on dep2.parent_department_id = dep3.eroad_index_id
and dep3.eroad_index_id IS NOT NULL AND
dep3.eroad_start_date <= hr_employee.termination_date AND
(dep3.eroad_end_date >= hr_employee.termination_date OR dep3.eroad_end_date IS NULL)
left join ir_translation dep3_trans on dep3_trans.res_id=dep3.id
and dep3_trans.name ='hr.department,name'
and dep3_trans.lang='zh_CN'left join hr_department dep4 on dep3.parent_department_id = dep4.eroad_index_id
and dep4.eroad_index_id IS NOT NULL AND
dep4.eroad_start_date <= hr_employee.termination_date AND
(dep4.eroad_end_date >= hr_employee.termination_date OR dep4.eroad_end_date IS NULL)
left join ir_translation dep4_trans on dep4_trans.res_id=dep4.id
and dep4_trans.name ='hr.department,name'
and dep4_trans.lang='zh_CN'left join hr_department dep5 on dep4.parent_department_id = dep5.eroad_index_id
and dep5.eroad_index_id IS NOT NULL AND
dep5.eroad_start_date <= hr_employee.termination_date AND
(dep5.eroad_end_date >= hr_employee.termination_date OR dep5.eroad_end_date IS NULL)
left join ir_translation dep5_trans on dep5_trans.res_id=dep5.id
and dep5_trans.name ='hr.department,name'
and dep5_trans.lang='zh_CN'left join hr_department dep6 on dep5.parent_department_id = dep6.eroad_index_id
and dep6.eroad_index_id IS NOT NULL AND
dep6.eroad_start_date <= hr_employee.termination_date AND
(dep6.eroad_end_date >= hr_employee.termination_date OR dep6.eroad_end_date IS NULL)
left join ir_translation dep6_trans on dep6_trans.res_id=dep6.id
and dep6_trans.name ='hr.department,name'
and dep6_trans.lang='zh_CN'left join hr_department dep7 on dep6.parent_department_id = dep7.eroad_index_id
and dep7.eroad_index_id IS NOT NULL AND
dep7.eroad_start_date <= hr_employee.termination_date AND
(dep7.eroad_end_date >= hr_employee.termination_date OR dep7.eroad_end_date IS NULL)
left join ir_translation dep7_trans on dep7_trans.res_id=dep7.id
and dep7_trans.name ='hr.department,name'
and dep7_trans.lang='zh_CN'left join hr_department dep8 on dep7.parent_department_id = dep8.eroad_index_id
and dep8.eroad_index_id IS NOT NULL AND
dep8.eroad_start_date <= hr_employee.termination_date AND
(dep8.eroad_end_date >= hr_employee.termination_date OR dep8.eroad_end_date IS NULL)
left join ir_translation dep8_trans on dep8_trans.res_id=dep8.id
and dep8_trans.name ='hr.department,name'
and dep8_trans.lang='zh_CN'
left join hr_position on emp_job.position_id = hr_position.eroad_index_id
and hr_position.eroad_index_id IS NOT NULL AND
hr_position.eroad_start_date <= hr_employee.termination_date AND
(hr_position.eroad_end_date >= hr_employee.termination_date OR hr_position.eroad_end_date IS NULL)
left join ir_translation pos_trans on pos_trans.res_id=hr_position.id
and pos_trans.name ='hr.position,name'
and pos_trans.lang='zh_CN'
left join employee_type on emp_job.employee_type = employee_type.id
LEFT JOIN ir_translation employee_type_trans
ON employee_type_trans.res_id = employee_type.id AND
employee_type_trans.name = 'employee.type,name' AND
employee_type_trans.lang = 'zh_CN'
left join job_classification
on emp_job.job_classification_id=job_classification.id
left join employee_group
on employee_group.id=job_classification.x_emp_childgroup1
LEFT JOIN ir_translation employee_group_trans
ON employee_group_trans.res_id = employee_group.id AND
employee_group_trans.name = 'employee.group,name' AND
employee_group_trans.lang = 'zh_CN'
left join resignation_type on employee_resignation.resignation_type=resignation_type.id
LEFT JOIN ir_translation resignation_type_trans
ON resignation_type_trans.res_id = resignation_type.id AND
resignation_type_trans.name = 'resignation.type,name' AND
resignation_type_trans.lang = 'zh_CN'
left join resignation_reason on employee_resignation.resignation_reason=resignation_reason.id
LEFT JOIN ir_translation resignation_reason_trans
ON resignation_reason_trans.res_id = resignation_type.id AND
resignation_reason_trans.name = 'resignation.reason,name' AND
resignation_reason_trans.lang = 'zh_CN'
left join hr_employee manager
on emp_job.manager_employee_id=manager.id
left join emp_job emp_job_manager on emp_job_manager.employee_id=manager.id
and emp_job_manager.eroad_index_id IS NOT NULL AND
emp_job_manager.eroad_start_date <= hr_employee.termination_date AND
(emp_job_manager.eroad_end_date >= hr_employee.termination_date OR emp_job_manager.eroad_end_date IS NULL) AND
emp_job_manager.status = 'active' AND
emp_job_manager.active = TRUE AND
emp_job_manager.employee_id IS NOT NULL
left join hr_employee manager2
on emp_job_manager.manager_employee_id=manager2.idwhere emp_job.eroad_index_id IS NOT NULL AND
emp_job.eroad_start_date <= hr_employee.termination_date AND
(emp_job.eroad_end_date >= hr_employee.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显示sql: select distinct comp.x_company_code as 公司代码, comp.x_company_name as 公司名称, case when dep0.x_bm_level=256 then COALESCE(dep0_trans.value,dep0.name) when dep1.x_bm_level=256 then COALESCE(dep1_trans.value,dep1.name) when dep2.x_bm_level=256 then COALESCE(dep2_trans.value,dep2.name) when dep3.x_bm_level=256 then COALESCE(dep3_trans.value,dep3.name) when dep4.x_bm_level=256 then COALESCE(dep4_trans.value,dep4.name) when dep5.x_bm_level=256 then COALESCE(dep5_trans.value,dep5.name) when dep6.x_bm_level=256 then COALESCE(dep6_trans.value,dep6.name) when dep7.x_bm_level=256 then COALESCE(dep7_trans.value,dep7.name) when dep8.x_bm_level=256 then COALESCE(dep8_trans.value,dep8.name) else '' end 系统, case when dep0.x_bm_level=257 then COALESCE(dep0_trans.value,dep0.name) when dep1.x_bm_level=257 then COALESCE(dep1_trans.value,dep1.name) when dep2.x_bm_level=257 then COALESCE(dep2_trans.value,dep2.name) when dep3.x_bm_level=257 then COALESCE(dep3_trans.value,dep3.name) when dep4.x_bm_level=257 then COALESCE(dep4_trans.value,dep4.name) when dep5.x_bm_level=257 then COALESCE(dep5_trans.value,dep5.name) when dep6.x_bm_level=257 then COALESCE(dep6_trans.value,dep6.name) when dep7.x_bm_level=257 then COALESCE(dep7_trans.value,dep7.name) when dep8.x_bm_level=257 then COALESCE(dep8_trans.value,dep8.name) else '' end 中心子公司, COALESCE(dep0_trans.value,dep0.name) 直属部门名称, dep0.x_shopid 门店编码, dep0.x_mdname 门店名称, hr_employee.employee_number 员工工号, hr_employee.name 员工姓名, COALESCE(pos_trans.value,hr_position.name) 离职前职位, COALESCE(employee_type_trans.value,employee_type.name) 员工组, employee_group.code 员工子组ID, COALESCE(employee_group_trans.value,employee_group.name) 员工子组, hr_employee.hire_date 入职日期, hr_employee.termination_date 离职日期, COALESCE(resignation_type_trans.value,resignation_type.name) 离职类型, COALESCE(resignation_reason_trans.value,resignation_reason.name) 离职原因, hr_employee.first_date_worked 司龄开始日期, case when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=0 and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<6 then '0-6月' when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=6 and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<12 then '6个月-1年' when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=12 and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<24 then '1-2年' when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=24 and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<36 then '2-3年' when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=36 and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<60 then '3-5年' when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=60 and round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)<120 then '5-10年' when round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1)>=120 then '10年及以上' else '无司龄' end 司龄结构, round((extract(year from age(hr_employee.termination_date,hr_employee.first_date_worked))*12 + extract(month from age(hr_employee.termination_date,hr_employee.first_date_worked)) +(extract(day from age(hr_employee.termination_date,hr_employee.first_date_worked))/30))::float,1) 司龄, manager.employee_number 上级工号, manager.name 上级姓名, manager2.employee_number 隔级上级工号, manager2.name 隔级上级姓名, cost_center.code 成本中心, COALESCE(cost_center_trans.value,cost_center.name) 成本中心名称 from employee_resignation left join hr_employee on employee_resignation.employee_id=hr_employee.id left join emp_job on emp_job.employee_id=hr_employee.id left join hr_department dep0 on emp_job.department_id=dep0.eroad_index_id and dep0.eroad_index_id IS NOT NULL and hr_employee.termination_date>=dep0.eroad_start_date and (hr_employee.termination_date<=dep0.eroad_end_date or dep0.eroad_end_date is null) left join ir_translation dep0_trans on dep0_trans.res_id=dep0.id and dep0_trans.name ='hr.department,name' and dep0_trans.lang='zh_CN' left join department_cost_center_rel dept_cost on dep0.id = dept_cost.department_id left join cost_center on dept_cost.cost_center_id = cost_center.id left join ir_translation cost_center_trans on cost_center_trans.res_id=cost_center.id and cost_center_trans.name ='cost.center,name' and cost_center_trans.lang='zh_CN' left join x_builder_company comp on cost_center.x_lyfcompany_code = comp.id left join hr_department dep1 on dep0.parent_department_id = dep1.eroad_index_id and dep1.eroad_index_id IS NOT NULL AND dep1.eroad_start_date <= hr_employee.termination_date AND (dep1.eroad_end_date >= hr_employee.termination_date OR dep1.eroad_end_date IS NULL) left join ir_translation dep1_trans on dep1_trans.res_id=dep1.id and dep1_trans.name ='hr.department,name' and dep1_trans.lang='zh_CN' left join hr_department dep2 on dep1.parent_department_id = dep2.eroad_index_id and dep2.eroad_index_id IS NOT NULL AND dep2.eroad_start_date <= hr_employee.termination_date AND (dep2.eroad_end_date >= hr_employee.termination_date OR dep2.eroad_end_date IS NULL) left join ir_translation dep2_trans on dep2_trans.res_id=dep2.id and dep2_trans.name ='hr.department,name' and dep2_trans.lang='zh_CN' left join hr_department dep3 on dep2.parent_department_id = dep3.eroad_index_id and dep3.eroad_index_id IS NOT NULL AND dep3.eroad_start_date <= hr_employee.termination_date AND (dep3.eroad_end_date >= hr_employee.termination_date OR dep3.eroad_end_date IS NULL) left join ir_translation dep3_trans on dep3_trans.res_id=dep3.id and dep3_trans.name ='hr.department,name' and dep3_trans.lang='zh_CN' left join hr_department dep4 on dep3.parent_department_id = dep4.eroad_index_id and dep4.eroad_index_id IS NOT NULL AND dep4.eroad_start_date <= hr_employee.termination_date AND (dep4.eroad_end_date >= hr_employee.termination_date OR dep4.eroad_end_date IS NULL) left join ir_translation dep4_trans on dep4_trans.res_id=dep4.id and dep4_trans.name ='hr.department,name' and dep4_trans.lang='zh_CN' left join hr_department dep5 on dep4.parent_department_id = dep5.eroad_index_id and dep5.eroad_index_id IS NOT NULL AND dep5.eroad_start_date <= hr_employee.termination_date AND (dep5.eroad_end_date >= hr_employee.termination_date OR dep5.eroad_end_date IS NULL) left join ir_translation dep5_trans on dep5_trans.res_id=dep5.id and dep5_trans.name ='hr.department,name' and dep5_trans.lang='zh_CN' left join hr_department dep6 on dep5.parent_department_id = dep6.eroad_index_id and dep6.eroad_index_id IS NOT NULL AND dep6.eroad_start_date <= hr_employee.termination_date AND (dep6.eroad_end_date >= hr_employee.termination_date OR dep6.eroad_end_date IS NULL) left join ir_translation dep6_trans on dep6_trans.res_id=dep6.id and dep6_trans.name ='hr.department,name' and dep6_trans.lang='zh_CN' left join hr_department dep7 on dep6.parent_department_id = dep7.eroad_index_id and dep7.eroad_index_id IS NOT NULL AND dep7.eroad_start_date <= hr_employee.termination_date AND (dep7.eroad_end_date >= hr_employee.termination_date OR dep7.eroad_end_date IS NULL) left join ir_translation dep7_trans on dep7_trans.res_id=dep7.id and dep7_trans.name ='hr.department,name' and dep7_trans.lang='zh_CN' left join hr_department dep8 on dep7.parent_department_id = dep8.eroad_index_id and dep8.eroad_index_id IS NOT NULL AND dep8.eroad_start_date <= hr_employee.termination_date AND (dep8.eroad_end_date >= hr_employee.termination_date OR dep8.eroad_end_date IS NULL) left join ir_translation dep8_trans on dep8_trans.res_id=dep8.id and dep8_trans.name ='hr.department,name' and dep8_trans.lang='zh_CN' left join hr_position on emp_job.position_id = hr_position.eroad_index_id and hr_position.eroad_index_id IS NOT NULL AND hr_position.eroad_start_date <= hr_employee.termination_date AND (hr_position.eroad_end_date >= hr_employee.termination_date OR hr_position.eroad_end_date IS NULL) left join ir_translation pos_trans on pos_trans.res_id=hr_position.id and pos_trans.name ='hr.position,name' and pos_trans.lang='zh_CN' left join employee_type on emp_job.employee_type = employee_type.id LEFT JOIN ir_translation employee_type_trans ON employee_type_trans.res_id = employee_type.id AND employee_type_trans.name = 'employee.type,name' AND employee_type_trans.lang = 'zh_CN' left join job_classification on emp_job.job_classification_id=job_classification.id left join employee_group on employee_group.id=job_classification.x_emp_childgroup1 LEFT JOIN ir_translation employee_group_trans ON employee_group_trans.res_id = employee_group.id AND employee_group_trans.name = 'employee.group,name' AND employee_group_trans.lang = 'zh_CN' left join resignation_type on employee_resignation.resignation_type=resignation_type.id LEFT JOIN ir_translation resignation_type_trans ON resignation_type_trans.res_id = resignation_type.id AND resignation_type_trans.name = 'resignation.type,name' AND resignation_type_trans.lang = 'zh_CN' left join resignation_reason on employee_resignation.resignation_reason=resignation_reason.id LEFT JOIN ir_translation resignation_reason_trans ON resignation_reason_trans.res_id = resignation_type.id AND resignation_reason_trans.name = 'resignation.reason,name' AND resignation_reason_trans.lang = 'zh_CN' left join hr_employee manager on emp_job.manager_employee_id=manager.id left join emp_job emp_job_manager on emp_job_manager.employee_id=manager.id and emp_job_manager.eroad_index_id IS NOT NULL AND emp_job_manager.eroad_start_date <= hr_employee.termination_date AND (emp_job_manager.eroad_end_date >= hr_employee.termination_date OR emp_job_manager.eroad_end_date IS NULL) AND emp_job_manager.status = 'active' AND emp_job_manager.active = TRUE AND emp_job_manager.employee_id IS NOT NULL left join hr_employee manager2 on emp_job_manager.manager_employee_id=manager2.id where emp_job.eroad_index_id IS NOT NULL AND emp_job.eroad_start_date <= hr_employee.termination_date AND (emp_job.eroad_end_date >= hr_employee.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
公司代码【取离职员工离职时有效的公司代码】
公司名称【取离职员工离职时有效的公司名称】
系统【查询员工上级部门，一直找到x_bm_level为256的部门名称】
中心/子公司【查询员工上级部门，一直找到x_bm_level为257的部门名称】
直属部门名称【员工所在部门名称】
门店编码【x_shipid 】
门店名称【x_mdname】
员工工号
员工姓名
离职前职位【岗位】
员工组【employee_type emp.job】
员工子组ID【根据emp.job job_classification_id从job_classification中查找x_emp_childgroup1。在根据x_emp_childgroup1从employee_grouo中找到code】
员工子组【根据emp.job job_classification_id从job_classification中查找x_emp_childgroup1。在根据x_emp_childgroup1从employee_grouo中找到name】
入职日期
离职日期
离职类型
离职原因
司龄开始日期【集团入职日期】
司龄结构【0-6月，6个月-1年，1-2年，2-3年，3-5年，5-10年，10年及以上，无司龄】
司龄
上级工号
上级姓名
隔级上级工号
隔级上级姓名
成本中心【取成本中心编码】
成本中心名称
