SELECT – 基本信息 emp.id, – 员工id emp.employee_number, – 员工工号 emp.name AS emp_name, – 员工姓名 emp_status.value AS emp_status, – 员工状态 emp_gender.value AS emp_gender, – 员工性别 emp.hire_date, – 入职日期 emp.termination_date, – 离职日期 division.name AS divison_name, – 组织类别（分部） legal_entity.name AS legal_entity_name, – 法人实体 emp_type.name AS emp_type, – 员工类型 benefits_company_rule.name AS social_name, --社保缴纳城市 dep.name AS dep_name, – 部门 position.name AS position_name, – 岗位 person.date_of_birth, – 出生日期 person.age, – 年龄 person.national_id AS card_national_id, – 证件号码 per_phone.phone_number, – 电话 emergency_contact.name AS emergency_contact_name, – 紧急联系人名称 emergency_contact.phone AS emergency_contact_phone, – 紧急联系人号码 emergency_contact_relationship.value AS emergency_contact_relationship, – 紧急联系人关系 emergency_contact.x_birthdate, – 联系人出生年月 emergency_contact.x_age, – 联系人年龄 emergency_card_type.name AS contract_name, – 联系人证件类型 emergency_contact.x_national_id, – 联系人证件号码 to_char(emergency_contact.create_date, 'yyyy-mm-dd') AS create_date, – 子女信息登记日期 case when emergency_contact.is_primary then '是' else '否' end AS is_primary – 是否紧急联系人 – 雇佣信息 FROM emp_job LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.id – LEFT JOIN res_selection AS emp_gender ON emp.gender = emp_gender.id LEFT JOIN ir_translation emp_gender ON emp.gender = emp_gender.res_id and emp_gender.lang='zh_CN' and emp_gender.name='res.selection,name' 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 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 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 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 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 emp_status ON emp_job.employee_status = emp_status.res_id and emp_status.lang='zh_CN' and emp_status.name='employee.status,name' LEFT JOIN employee_type AS emp_type ON emp_job.employee_type = emp_type.id – 自然人 LEFT JOIN per_person AS person ON emp.person_id = person.id – 证件类型 LEFT JOIN res_selection AS card_type ON person.card_type = card_type.id – 电话信息 LEFT JOIN per_phone AS per_phone ON person.id = per_phone.person_id AND per_phone.is_primary = TRUE LEFT JOIN res_selection AS phone_type ON per_phone.phone_type = phone_type.id – 联系人信息 LEFT JOIN emergency_contact on emergency_contact.person_id = emp.person_id LEFT JOIN ir_translation emergency_contact_relationship ON emergency_contact.relationship = emergency_contact_relationship.res_id and emergency_contact_relationship.lang='zh_CN' and emergency_contact_relationship.name='res.selection,name' LEFT JOIN res_selection AS emergency_card_type ON emergency_contact.x_card_type = emergency_card_type.id – 个人信息 LEFT JOIN per_personal AS personal ON person.per_personal = personal.eroad_index_id AND personal.eroad_index_id IS NOT NULL AND personal.eroad_start_date <= current_date AND (personal.eroad_end_date >= current_date OR personal.eroad_end_date IS NULL) AND personal.active = TRUE LEFT JOIN res_country AS country ON personal.nationality_id = country.id LEFT JOIN res_nation AS nation ON personal.nation = nation.id LEFT JOIN res_country_state AS country_state ON personal.native_place = country_state.id LEFT JOIN political_status AS political ON personal.political_status = political.id LEFT JOIN res_selection AS children_situation ON personal.children_situation = children_situation.id LEFT JOIN benefits_employee_rule on benefits_employee_rule.employee_id=emp.id left join benefits_company_rule on benefits_employee_rule.social_insurance_rule_de_id = benefits_company_rule.id WHERE 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 and (emergency_contact.is_primary or emergency_contact.relationship in (16,17)) and emp_job.employee_status in (1,2,3,4);