select case sel_ca.code when 'gender' then '性别/编码'
when 'registered_residence_type' then '户籍类型/编码'
when 'marital_status' then '婚姻状况/编码'
when 'card_type' then '证件类型/编码'
when 'educational_background' then '学历/编码'
when 'academic_degree' then '学位/编码'
when 'contact_relationship' then '联系人关系/编码'
when 'fertility_status' then '生育状况/编码'
when 'email_type' then '邮箱类型/编码'
when 'certifier_relationship' then '证明人关系/编码'
end as field_name,
sel.code as code,
tran_.value as name
from res_selection sel
left join res_selection_category sel_ca on sel.selection_category=sel_ca.id
left join ir_translation tran_ on tran_.res_id = sel.id and tran_.lang='zh_CN' AND tran_.name = 'res.selection,name'
where sel_ca.code in ('gender','registered_residence_type','marital_status','card_type','educational_background','academic_degree','contact_relationship','fertility_status','email_type','certifier_relationship')
UNION
select case when country.id!=0 then '国籍/国家/地区编号' end, country.code, tran_country.value
from res_country country
left join ir_translation tran_country on tran_country.res_id = country.id and tran_country.lang='zh_CN' AND tran_country.name = 'res.country,name'
UNION
select case when type_.id != 0 then '员工类型/编码' end, type_.code, tran_type.value
from employee_type type_
left join ir_translation tran_type on tran_type.res_id = type_.id and tran_type.lang='zh_CN' AND tran_type.name = 'employee.type,name'
UNION
select case when status_.id != 0 then '员工状态/编码' end, status_.code, tran_status.value
from employee_status status_
left join ir_translation tran_status on tran_status.res_id = status_.id and tran_status.lang='zh_CN' AND tran_status.name = 'employee.status,name'
UNION
select case when nation_.id != 0 then '民族/编码' end, nation_.code, nation_.name
from res_nation nation_
UNION
select case when country_state.id != 0 then '籍贯/州/省代码' end, country_state.code, country_state.name
from res_country_state country_state
left join res_country country_ on country_state.country_id=country_.id
where country_.code ='CN'
UNION
select case when political.id != 0 then '政治面貌/编码' end, political.code, political.name
from political_status political
UNION
select case when payroll.id != 0 then '薪资档案名称/编码' end, payroll.code, tran_payroll.value
from payroll_archive_config payroll
left join ir_translation tran_payroll on tran_payroll.res_id = payroll.id and tran_payroll.lang='zh_CN' AND tran_payroll.name = 'payroll.archive.config,name'
UNION
select case when city.id != 0 then '工作地/编码' end, city.code, city.name
from res_city city
order by field_name desc
High 程丹丹
俞晶晶