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 ), emp_category AS ( -- 员工标签 SELECT emp_job_id AS emp_id, ARRAY_AGG(category.name) AS categories FROM category_hr_employee_rel LEFT JOIN hr_employee_category AS category ON category_hr_employee_rel.category_id = category.id GROUP BY emp_job_id ) SELECT -- 基本信息 emp.id, -- 员工id emp.employee_number, -- 员工工号 emp.name AS emp_name, -- 员工姓名 tran_emp_status.value AS emp_status, -- 员工状态 emp_category.categories AS emp_categories, -- 员工标签 tran_gender.value AS emp_gender, -- 员工性别 tran_emp_type.value AS emp_type, -- 员工类型 emp.hire_date, -- 入职日期 emp.first_date_worked, -- 集团入职日期 emp.probation_date, -- 转正日期 emp.termination_date, -- 离职日期 emp.inner_working_age, -- 公司服务年限 emp.original_start_date, -- 社会工作开始日期 emp.social_working_age, -- 社会服务年限 legal_entity.name AS legal_entity_name, -- 法人实体 bu.name AS bu_name, -- 业务单元 division.name AS divison_name, -- 分部 dep.name AS dep_name, -- 部门 cost_center.name AS cost_center_name, -- 成本中心 location.name AS clocation_name, -- 地点 emp_country.name AS country_name, -- 国家 position.name AS position_name, -- 岗位 emp_job.position_entry_date, -- 岗位入职日期 emp_group.name AS employee_group, -- 员工分组 emp_manager.name AS manager_employee, -- 上级 job_classification.name AS job_classification, -- 职务 job_sequence.name AS job_sequence, -- 职务序列 job_function.name AS job_function, -- 职能 job_grade.name AS job_grade, -- 职等 job_level.name AS job_level, -- 职级 pay_grade.name AS pay_grade, -- 薪等 pay_level.name AS pay_level, -- 薪级 emp.x_hireyearmonth AS "入职年月数" , --入职年月数 emp.x_neibugongling AS "内部工龄计算日期" , --内部工龄计算日期 tran_recruitment_students.value AS x_recruitment_students, -- 应届生采用 tran_x_language.value AS x_language, -- 语言能力 tran_x_language_certificate_level.value AS x_language_certificate_level, -- 语言级别 tran_x_dispatch_company.value AS x_dispatch_company, -- 所属派遣公司 tran_x_agency_company.value AS x_agency_company, -- 所属代理公司 tran_x_employment_location.value AS x_employment_location, -- 录用地 tran_x_working_content.value AS x_working_content, -- 工作内容 tran_x_organization_type.value AS x_organization_type, -- 组织类别 dep.x_dept_bu AS "部" , -- 部 dep.x_dept_zu AS "组" , -- 组 dep.x_dept_ke AS "课" , -- 课 emp.x_external_woking_age AS "外部工龄_总月数" , emp.is_probation AS “是否在试用期”, benefits_company_rule.name AS social_name, --社保缴纳城市 -- 个人信息 personal.marital_status, -- 婚姻状况 marital_status_trans.value marital_status_name, -- 婚姻状况 personal.registered_residence_type, -- 户口类型 country.name AS country, -- 国籍 nation.name AS nation, -- 民族 country_state.name AS country_state, -- 籍贯 political.name AS political, -- 政治面貌 children_situation.name AS children_situation, -- 生育状况 personal.x_pinyin AS “拼音”, -- 拼音 personal.x_preson_phone AS "个人移动电话号码" , personal.x_company_phone AS "公司手机号码" , personal.x_wechat AS "微信号" , personal.registered_residence_address AS "户籍所在地" , -- 自然人信息 person.date_of_birth, -- 出生日期 person.place_of_birth, -- 出生地 card_type.name AS card_type, -- 证件类型 person.national_id AS card_national_id, -- 证件号码 person.due_date AS card_due_date, -- 证件到期日 person.age, -- 年龄 person.zodiac_sign, -- 星座 person.zodiac, -- 生肖 -- 邮箱信息 per_email.email_address, -- 邮箱地址 email_type.name AS email_type, -- 邮箱类型 -- 电话信息 per_phone.phone_number, -- 电话 phone_type.name AS phone_type, -- 电话类型 huji_type_trans.value huji_type, -- 户籍性质 sheng_state.name huji_sheng, -- 户籍所在地-省 shi_city.name huji_shi, -- 户籍所在地-市 -- 银行卡信息 country_of_bank.name AS children_situation, -- 银行所在国 bank_identification_number_type.name AS bank_identification_number_type, -- 银行卡类型 bank_information.bank_name, -- 银行名称 bank_information.bank_identification_number, -- 金融机构识别码 bank_information.bank_address, -- 银行地址 bank_information.bank_account, -- 银行账户 bank_use.name AS bank_use, -- 用途 bank_information.bank_account_holder, -- 开户人姓名 -- 学历信息 person_education.start_date AS edu_start_date, -- 开始日期 person_education.end_date AS edu_end_date, -- 结束日期 edu_method.name AS edu_method, -- 教育方式 person_education.school AS edu_school, -- 学校 edu_background.name AS edu_background, -- 学历 academic_degree.name AS academic_degree, -- 学位 person_education.profession, -- 专业 person_education.profession_type, -- 专业类型 person_education.degree_certificate_number, -- 学位证书号 -- 工作信息 person_work.start_date AS work_start_date, -- 开始日期 person_work.end_date AS work_end_date, -- 结束日期 person_work.industry, -- 行业 person_work.company, -- 公司 person_work.department, -- 部门 person_work.job, -- 岗位 person_work.monthly_salary, -- 月薪 person_work.reason_of_leaving, -- 离职原因 person_work.certifier, -- 证明人 certifier_relationship.name AS certifier_relationship, -- 证明人关系 person_work.certifier_job, -- 证明人岗位 person_work.certifier_phone, -- 证明人号码 -- 紧急联系人信息 emergency_contact.name AS emergency_contact_name, -- 紧急联系人名称 emergency_contact.phone AS emergency_contact_phone, -- 紧急联系人号码 emergency_contact_relationship.name AS emergency_contact_relationship, -- 紧急联系人关系 contract_current.contract_date_start, -- 试用期开始日期 contract_current.contract_date_end, -- 合同结束日期 contract_current.trial_date_start, -- 试用期开始日期 contract_current.trial_date_end, -- 试用期结束日期 contract_type.name AS contract_type, -- 合同类型 contract_category.name AS category_type -- 期限类型 -- 雇佣信息 FROM emp_job LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.id LEFT JOIN emp_category ON emp_job.employee_id = emp_category.emp_id LEFT JOIN res_selection AS emp_gender ON emp.gender = emp_gender.id LEFT JOIN ir_translation AS tran_gender on tran_gender.res_id=emp_gender.id and tran_gender.lang= 'zh_CN' and tran_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 job_classification AS job_classification ON emp_job.job_classification_id = job_classification.eroad_index_id AND job_classification.eroad_index_id IS NOT NULL AND job_classification.eroad_start_date <= current_date AND (job_classification.eroad_end_date >= current_date OR job_classification.eroad_end_date IS NULL) AND job_classification.active = TRUE LEFT JOIN cost_center ON emp_job.cost_center_id = cost_center.id AND cost_center.active = TRUE LEFT JOIN hr_location AS location on emp_job.location_id = location.id AND location.active = TRUE LEFT JOIN res_country AS emp_country on emp_job.country = emp_country.id AND emp_country.active = TRUE LEFT JOIN employee_status emp_status ON emp_job.employee_status = emp_status.id LEFT JOIN ir_translation AS tran_emp_status on tran_emp_status.res_id=emp_status.id and tran_emp_status.lang= 'zh_CN' and tran_emp_status.name= 'employee.status,name' LEFT JOIN employee_type AS emp_type ON emp_job.employee_type = emp_type.id LEFT JOIN ir_translation AS tran_emp_type on tran_emp_type.res_id=emp_type.id and tran_emp_type.lang= 'zh_CN' and tran_emp_type.name= 'employee.type,name' LEFT JOIN employee_group AS emp_group on emp_job.employee_group = emp_group.id AND emp_group.active = TRUE LEFT JOIN hr_employee AS emp_manager on emp_job.manager_employee_id = emp_manager.id AND emp_manager.active = TRUE LEFT JOIN job_sequence AS job_sequence on emp_job.job_sequence_id = job_sequence.id AND job_sequence.active = TRUE LEFT JOIN job_function AS job_function on emp_job.job_function_id = job_function.id AND job_function.active = TRUE LEFT JOIN job_grade AS job_grade on emp_job.job_grade_id = job_grade.id AND job_grade.active = TRUE LEFT JOIN job_level AS job_level on emp_job.job_level = job_level.id AND job_level.active = TRUE LEFT JOIN pay_grade AS pay_grade on emp_job.pay_grade = pay_grade.id AND pay_grade.active = TRUE LEFT JOIN pay_level AS pay_level on emp_job.pay_level = pay_level.id LEFT JOIN res_selection x_recruitment_students ON emp.x_recruitment_students=x_recruitment_students.id LEFT JOIN ir_translation AS tran_recruitment_students on tran_recruitment_students.res_id=x_recruitment_students.id and tran_recruitment_students.lang= 'zh_CN' and tran_recruitment_students.name= 'res.selection,name' LEFT JOIN res_selection x_language ON emp.x_language=x_language.id LEFT JOIN ir_translation AS tran_x_language on tran_x_language.res_id=x_language.id and tran_x_language.lang= 'zh_CN' and tran_x_language.name= 'res.selection,name' LEFT JOIN res_selection x_language_certificate_level ON emp.x_language_certificate_level=x_language_certificate_level.id LEFT JOIN ir_translation AS tran_x_language_certificate_level on tran_x_language_certificate_level.res_id=x_language_certificate_level.id and tran_x_language_certificate_level.lang= 'zh_CN' and tran_x_language_certificate_level.name= 'res.selection,name' LEFT JOIN res_selection x_dispatch_company ON emp_job.x_dispatch_company=x_dispatch_company.id LEFT JOIN ir_translation AS tran_x_dispatch_company on tran_x_dispatch_company.res_id=x_dispatch_company.id and tran_x_dispatch_company.lang= 'zh_CN' and tran_x_dispatch_company.name= 'res.selection,name' LEFT JOIN res_selection x_agency_company ON emp_job.x_agency_company=x_agency_company.id LEFT JOIN ir_translation AS tran_x_agency_company on tran_x_agency_company.res_id=x_agency_company.id and tran_x_agency_company.lang= 'zh_CN' and tran_x_agency_company.name= 'res.selection,name' LEFT JOIN hr_location x_employment_location ON emp_job.x_employment_location=x_employment_location.id LEFT JOIN ir_translation AS tran_x_employment_location on tran_x_employment_location.res_id=x_employment_location.id and tran_x_employment_location.lang= 'zh_CN' and tran_x_employment_location.name= 'hr.location,name' LEFT JOIN res_selection x_working_content ON emp_job.x_working_content=x_working_content.id LEFT JOIN ir_translation AS tran_x_working_content on tran_x_working_content.res_id=x_working_content.id and tran_x_working_content.lang= 'zh_CN' and tran_x_working_content.name= 'res.selection,name' LEFT JOIN res_selection x_organization_type ON emp_job.x_organization_type=x_organization_type.id LEFT JOIN ir_translation AS tran_x_organization_type on tran_x_organization_type.res_id=x_organization_type.id and tran_x_organization_type.lang= 'zh_CN' and tran_x_organization_type.name= 'organization.type,name' -- 自然人 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_email AS per_email ON person.id = per_email.person_id AND per_email.is_primary = TRUE LEFT JOIN res_selection AS email_type ON per_email.email_type = email_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 (SELECT person_id, max(id) AS information_id FROM person_bank_information WHERE person_id IS NOT NULL GROUP BY person_id) AS bank_rel ON person.id = bank_rel.person_id LEFT JOIN person_bank_information AS bank_information ON bank_rel.information_id = bank_information.id LEFT JOIN res_country AS country_of_bank ON bank_information.country_of_bank = country_of_bank.id LEFT JOIN res_selection AS bank_identification_number_type ON bank_information.bank_identification_number_type = bank_identification_number_type.id LEFT JOIN res_selection AS bank_use ON bank_information.use = bank_use.id -- 学历信息 LEFT JOIN person_education ON emp.person_id = person_education.person_id AND person_education.is_highest_degree = TRUE LEFT JOIN res_selection AS edu_method ON person_education.study_method = edu_method.id LEFT JOIN res_selection AS edu_background ON person_education.educational_background = edu_background.id LEFT JOIN res_selection AS academic_degree ON person_education.academic_degree = academic_degree.id -- 工作信息 LEFT JOIN (SELECT person_id, max(id) AS work_experience_id FROM person_work_experience WHERE person_id IS NOT NULL GROUP BY person_id) AS work_experience_rel ON person.id = work_experience_rel.person_id LEFT JOIN person_work_experience AS person_work ON work_experience_rel.work_experience_id = person_work.id LEFT JOIN res_selection AS certifier_relationship ON person_work.certifier_relationship = certifier_relationship.id -- 联系人信息 LEFT JOIN (SELECT person_id, max(id) AS emergency_contact_id FROM emergency_contact WHERE person_id IS NOT NULL GROUP BY person_id) AS emergency_contact_rel ON person.id = emergency_contact_rel.person_id LEFT JOIN emergency_contact AS emergency_contact ON emergency_contact_rel.emergency_contact_id = emergency_contact.id LEFT JOIN res_selection AS emergency_contact_relationship ON emergency_contact.relationship = emergency_contact_relationship.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 left join res_country_state sheng_state on personal.state_id=sheng_state.id left join res_selection huji_type_selection on personal.registered_residence_type=huji_type_selection.id left join ir_translation huji_type_trans on huji_type_trans.res_id=huji_type_selection.id and huji_type_trans.name = 'res.selection,name' and huji_type_trans.lang= 'zh_CN' left join res_country_state shi_city on personal.city_id=shi_city.id left join res_selection marital_status_selection on personal.marital_status=marital_status_selection.id left join ir_translation marital_status_trans on marital_status_trans.res_id=marital_status_selection.id and marital_status_trans.name = 'res.selection,name' and marital_status_trans.lang= 'zh_CN' left join hr_contract as contract_current on contract_current.employee_id=emp.id and contract_current.contract_agreement_status= 'current_contract' left join contract_agreement as contract_type on contract_type.id = contract_current.contract_agreement_id left join contract_category as contract_category on contract_category.id = contract_current.contract_category_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;