with dept_md as (
select  x_dept_id,x_dept_pmsm,x_dept_name,x_dept_bmlx,x_dept_ppsx from x_department_path where x_dept_type='S000017'
)

select 
emp.employee_number 员工工号,
emp.x_employee_oldnumber 旧工号,
emp.name 员工姓名,
employee_status_trans.value  员工状态,
case employee_status.code 
when 'active' then '在职'
when 'inactive' then'离职'
when 'pre_hire' then '预入职'
when 'abandonment' then '放弃入职' 
else null end 
员工状态code,
ft.name 所属法体,
--ft_trans.value 所属法体,
ft.code 法体code,
dp.x_dept_bmlx 部门类型,
emp.hire_date 入职日期,
emp.first_date_worked 集团入职日期,
case emp.is_probation  when True then '是' when False then '否'  end 是否试用期,
emp.probation_period_end_date 试用期结束日期,
emp.probation_date 转正日期,
dp.x_dept_path 部门路径,
dp.x_dept_name1 as "一级部门",
dp.x_dept_name2 as "二级部门",
dp.x_dept_name3 as "三级部门",
dp.x_dept_name4 as "四级部门",
dp.x_dept_name5 as "五级部门",
dp.x_dept_name6 as "六级部门",
dp.x_dept_name7 as "七级部门",
dp.x_dept_name8 as "八级部门",
dp.x_dept_name9 as "九级部门",
case when hd.parent_department_id=dept_md.x_dept_id then dp.x_dept_name else null end 门店部门,
coalesce(dept_md.x_dept_pmsm,p_dept_md.x_dept_pmsm) PMS编码,
coalesce(dept_md.x_dept_ppsx,p_dept_md.x_dept_ppsx)  所属品牌,
coalesce(dept_md.x_dept_bmlx ,p_dept_md.x_dept_bmlx) 门店性质,
coalesce(dept_md.x_dept_name ,p_dept_md.x_dept_name) 门店名称,
hr_position_trans.value 岗位,
job_classification_trans.value 职务,
job_grade_trans.value 职等,
job_level_trans.value 职级,
case emp.x_isleader  when True then '是' when False then '否'  end  是否为储备,
emp.termination_date 离职日期,
card_type_trans.value 证件类型,
per_person.date_of_birth 出生日期,
per_person.age 年龄,
gender_trans.value 性别,
emp.business_phone 手机号码,
nation_trans.value 民族,
per_person.national_id 证件号码,
per_personal.x_contact_address 身份证地址,
per_personal.registered_residence_address 户口所在地,	
rrtype_trans.value  户口类型,
person_bank_information.bank_account  银行卡号,
person_bank_information.bank_name  开户银行,
person_bank_information.bank_address 开户城市,
person_bank_information.subbank_name 开户支行,
employee_type_trans.value 员工类型,
xueli_trans.value 最高学历,
person_education.school 毕业院校	,
person_education.profession 所学专业,
marital_selection_trans.value  婚姻状况,
legal_entity_trans.value 法人实体,
contract_date_start  合同开始日期,
contract_date_end  合同结束日期,
email_comp.email_address  公司邮箱,
emp.inner_working_age   公司服务年限,
 person_skill_information.zyzg 专业人员等级,
person_skill_information.zc   专业职务名称,
person_skill_information.jndj   技能人员等级,
''  技能证书名称
from hr_employee emp
left join emp_job  on emp.job_info_id =emp_job.id
left join legal_entity on emp_job.legal_entity_id=legal_entity.id
left join ir_translation legal_entity_trans on legal_entity_trans.res_id=legal_entity.id and legal_entity_trans.name ='legal.entity,name'  and legal_entity_trans.lang='zh_CN'
left join res_selection ft on legal_entity.x_ft=ft.id
left join ir_translation ft_trans on ft_trans.res_id=ft.id and ft_trans.name ='res.selection,name'  and ft_trans.lang='zh_CN'
left join employee_status on emp_job.employee_status= employee_status.id
left join ir_translation employee_status_trans on employee_status_trans.res_id=employee_status.id and employee_status_trans.name ='employee.status,name'  and employee_status_trans.lang='zh_CN'
left join x_department_path dp on emp_job.department_id =dp.x_dept_id
left join hr_department hd on emp_job.department_id =hd.id
left join dept_md dept_md on hd.parent_department_id=dept_md.x_dept_id 
left join dept_md p_dept_md on  hd.id=p_dept_md.x_dept_id
left join hr_position on emp_job.position_id=hr_position.id
left join ir_translation hr_position_trans on hr_position_trans.res_id=hr_position.id and hr_position_trans.name ='hr.position,name' and hr_position_trans.lang='zh_CN'

left join job_classification on  emp_job.job_classification_id=job_classification.id
left join ir_translation job_classification_trans on job_classification_trans.res_id=job_classification.id and job_classification_trans.name ='job.classification,name' and job_classification_trans.lang='zh_CN'
left join job_level on emp_job.job_level=job_level.id
left join ir_translation job_level_trans on job_level_trans.res_id=job_level.id and job_level_trans.name ='job.level,name'  and job_level_trans.lang='zh_CN'
left join job_grade on emp_job.job_grade_id=job_grade.id
left join ir_translation job_grade_trans on job_grade_trans.res_id=job_grade.id and job_grade_trans.name ='job.grade,name'  and job_grade_trans.lang='zh_CN'

left join per_person  on emp.person_id=per_person.id
left join res_selection res on per_person.card_type=res.id
left join ir_translation card_type_trans on card_type_trans.res_id=res.id  and card_type_trans.name='res.selection,name'  and  card_type_trans.LANG='zh_CN'
left join per_personal  on per_personal.id=per_person.per_personal
left join res_selection res_gender on per_personal.gender = res_gender.id
left join ir_translation gender_trans on gender_trans.res_id=res_gender.id and gender_trans.name ='res.selection,name' and gender_trans.lang='zh_CN'

left join res_nation on per_personal.nation=res_nation.id
left join ir_translation  nation_trans on nation_trans.res_id=res_nation.id and nation_trans.name ='res.nation,name'  and nation_trans.lang='zh_CN'
left join res_selection rrtype_selection on per_personal.registered_residence_type=rrtype_selection.id
left join ir_translation rrtype_trans on rrtype_trans.res_id=rrtype_selection.id and rrtype_trans.name ='res.selection,name' and rrtype_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 person_bank_information on person_bank_information.person_id=per_person.id and person_bank_information.use=43

left join person_education on person_education.person_id=per_person.id and person_education.is_highest_degree=True and person_education.is_highest_education=True
left join res_selection xueli_selection on person_education.educational_background=xueli_selection.id
left join ir_translation xueli_trans on xueli_trans.res_id=xueli_selection.id and xueli_trans.name ='res.selection,name'  and xueli_trans.lang='zh_CN'

left join res_selection marital_selection on per_personal.marital_status=marital_selection.id
left join ir_translation marital_selection_trans on marital_selection_trans.res_id=marital_selection.id and marital_selection_trans.name ='res.selection,name'  and marital_selection_trans.lang='zh_CN'

left join
(select hr_contract.id,
employee_id
from hr_contract
left join contract_agreement on hr_contract.contract_agreement_id= contract_agreement.id
where
hr_contract.x_bp_state='done'
and contract_agreement_status='current_contract'
and contract_agreement.code = 'A001') sub_contract
on emp.id=sub_contract.employee_id
left join hr_contract on hr_contract.id=sub_contract.id
and hr_contract.x_bp_state='done'
and contract_agreement_status='current_contract'
left join contract_agreement on hr_contract.contract_agreement_id= contract_agreement.id
left join legal_entity contract_legal_entity on emp_job.legal_entity_id=contract_legal_entity.id
left join ir_translation con_legal_entity_trans on con_legal_entity_trans.res_id=contract_legal_entity.id
and con_legal_entity_trans.name ='legal.entity,name' 
and con_legal_entity_trans.lang='zh_CN'
left join contract_category on hr_contract.contract_category_id=contract_category.id
left join ir_translation contract_category_trans on contract_category_trans.res_id=contract_category.id
and contract_category_trans.name ='contract.category,name' 
and contract_category_trans.lang='zh_CN'

left join
(select max(case when res_selection.code='email_type.personal' then per_email.id end) per_id,
max(case when res_selection.code='email_type.business' then per_email.id end) comp_id,
person_id
from per_email
left join res_selection 
on per_email.email_type=res_selection.id
group by per_email.person_id) email_sub
on per_person.id=email_sub.person_id
left join per_email email_person on email_sub.per_id=email_person.id
left join per_email email_comp on email_sub.comp_id=email_comp.id

left join political_status on per_personal.political_status=political_status .id
left join(
select person_id,
max(case when  skill_type like '%职称%'  then name end ) as zc,
max(case when  skill_type like '%国家职业资格%'  then name end  )  as zyzg,
max(case when  skill_type like '%职业技能等级%'  then name end )  as jndj
from person_skill_information group by person_id) person_skill_information  on per_person.id=person_skill_information.person_id

where emp.active=True 
and job_info_id is not null