select distinct * from ( WITH RECURSIVE dep_level_view AS ( select eroad_index_id as cid, tran_dep1.value as name,code, ARRAY [tran_dep1.value] as name_list, ARRAY [eroad_index_id] as path_ids, FALSE as is_cycle from hr_department LEFT join ir_translation AS tran_dep1 ON tran_dep1.res_id = hr_department.id AND tran_dep1.lang='zh_CN' AND tran_dep1.name='hr.department,name' 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, tran_dep2.value as name, dep_table.code, dep_level_view.name_list || tran_dep2.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 AS tran_dep2 ON tran_dep2.res_id = dep_table.id AND tran_dep2.lang='zh_CN' AND tran_dep2.name='hr.department,name' 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.employee_number, – 员工工号 emp.name AS emp_name, – 员工姓名 personal.x_en_name, – 英文全名 personal.x_first_name, – 英文名 personal.x_middle_name, – middle name personal.x_last_name, – 英文姓 tran_gender.value AS emp_gender, – 性别 person.date_of_birth, – 出生日期 person.age, – 年龄 tran_country.value as tran_country, – 国籍 nation.name AS nation, – 民族 political.name AS political, – 政治面貌 country_state.name AS country_state, – 籍贯 tran_residence.value as residence_type, – 户籍类型 tran_card.value AS card_type, – 证件类型 per_nati.national_id, – 证件号码 per_nati.x_certification_address, – 证件地址 per_nati.due_date, – 证件到期日 emp.business_phone, – 手机号码 tran_marital.value AS marital_status, – 婚姻状况 tran_edu.value AS edu_background, – 最高学历 tran_relationship.value AS emergency_contact_relationship, – 紧急联系人关系 emergency_contact.name AS emergency_contact_name, – 紧急联系人姓名 emergency_contact.phone AS emergency_contact_phone, – 紧急联系人电话 children_situation.name AS children_situation, – 子女情况 per_email.email_address, – 个人邮箱 entry_and_exit.x_entry_date, – 入境日 entry_and_exit.x_exit_date, – 离境日 bank_information.bank_account, – 银行账户 bank_information.bank_name, – 开户行（支行) social_fund.social_security_account, – 个人社保账号 social_fund.provident_fund_account, – 个人公积金账号 know_emp.x_know_aden_employee, – 是否认识埃顿公司员工 personal.x_disease, – 自身疾病情况 personal.x_is_pregnant, – 是否怀孕 break_law.x_is_break_law, – 是否有违法记录 tran_legal_entity.value AS legal_entity_name, – 签约主体 tran_legal_entity_salary.value AS legal_entity_salary_name, – 薪资发放主体 case emp_job.x_emp_mode when 'Aden_employment' then 'Aden用工' when 'Aden_dispatch' then '派遣' when 'Aden_outsourcing' then '外包' when 'Aden_pingtai' then '平台用工' when 'Aden_agencyOAI' then 'Aden用工-社保代理' end as x_emp_mode, – 用工形式 case emp_job.x_emp_attributes when 'pre_hire_01' then '全日制' when 'part_time_02' then '非全日制' when 'pbl_03' then '项目制' end as x_emp_attributes, – 员工性质 tran_emp_type.value AS emp_type, – 员工身份 case emp_job.x_working_type when '1' then 'OFE' when '2' then 'Site' when '3' then '挂靠' end as x_working_type, – 工种 emp_job.x_working_hours, – 工时制度 tran_erp_legal.value as x_erp_code, – ERP代码 tran_cs.value AS cost_center_name, – 成本中心 dep_level_view.name_list [1] AS "一级组织单元名称", – 一级组织单元名称 dep_level_view.name_list [2] AS "二级组织单元名称", – 二级组织单元名称 dep_level_view.name_list [3] AS "三级组织单元名称", dep_level_view.name_list [4] AS "四级组织单元名称", dep_level_view.name_list [5] AS "五级组织单元名称", dep_level_view.name_list [6] AS "六级组织单元名称", dep_level_view.name_list [7] AS "七级组织单元名称", tran_dep.value AS "当前组织单元名称", – 当前组织单元名称 tran_organization_type.value AS organization_type, – 组织类型 tran_dep.value AS department, – 部门 case emp_job.x_emp_service_type when '1' then 'SEC' when '2' then 'CLN' when '3' then 'CAT' when '5' then 'IFM' when '6' then 'HRMS' when '7' then 'OTH' when '8' then 'HT' when '9' then 'FIN' when '10' then 'CS' when '11' then 'COM' when '12' then 'DIG' when '13' then 'OPS' when '14' then 'ADM' when '15' then 'LEG' when '16' then 'MGT' when '17' then 'QAM' when '18' then 'MTN' when '19' then 'AKI' when '20' then 'SMP' when '21' then 'ASAP' when '22' then 'ASAC' when '23' then 'ENGY' when '24' then 'MKT' when '25' then 'F&E' when '25' then 'COE' when '27' then 'TAM' end as x_emp_service_type, tran_location_guanxia.value AS location_guanxia, – 管辖地 country_1.name AS x_work_place, – 工作地 tran_position.value AS position_name, – 岗位 tran_job_level.name AS job_level, – 职级 emp_manager.name AS manager_employee, – 汇报上级 dep_manager.name AS dep_manager, – 组织单元经理 tran_status.value AS emp_status, – 员工状态 emp.username, – 用户名 emp.original_start_date, – 首次工作日期 emp.hire_date, – 入职日期 emp.termination_date, – 离职日期 emp.email, – 公司邮箱 emp_job.x_um_type, – 工会会员类型 case emp_job.x_bonus_type when '1' then '个人绩效奖' when '2' then '在职贡献奖' end as x_bonus_type, – 奖金类型 emp_job.x_is_hardship, – 是否有Hardship tran_contract_type.value AS contract_type, – 合同类型 contract_current.code AS contract_code, – 合同编号 case contract_current.contract_agreement_status when 'current_contract' then '当前合同' when 'expired_contract' then '失效合同' when 'non_effective_contract' then '未生效合同' end as contract_agreement_status, – 合同状态 contract_current.total_signing_times, – 合同签订次数 tran_legal_entity_contract.value AS legal_entity_contract, – 签约主体 contract_current.trial_date_start, – 试用期开始日期 contract_current.trial_date_end, – 试用期结束日期 contract_current.contract_date_start, – 合同开始日期 contract_current.contract_date_end, – 合同结束日期 contract_current.signing_period, – 合同期限 contract_current.x_expiry_date, – 终止日期 contract_current.x_end_remind_day, – 合同期满提醒日 bcr_social.name as social_rule_name, – 社保规则 bca_social.unit_name as social_unit_name, – 公积金规则 bcr_fund.name as house_fund_rule_name, – 社保缴纳单位 bca_fund.unit_name as house_fund_unit_name, – 公积金缴纳单位 tax_view.withhold_agent_name – 扣缴义务人 – 雇佣信息 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 hr_location as location_guanxia on location_guanxia.id = emp_job.location_id LEFT join ir_translation AS tran_location_guanxia ON tran_location_guanxia.res_id = location_guanxia.id AND tran_location_guanxia.lang='zh_CN' AND tran_location_guanxia.name='hr.location,name' left join res_country as country_1 on country_1.id = emp_job.x_work_place LEFT join ir_translation AS tran_country_1 ON tran_country_1.res_id = country_1.id AND tran_country_1.lang='zh_CN' AND tran_country_1.name='res.country,name' left join hr_employee_partner as partner on partner.employee_id=emp.id and relation_id=1 and partner.start_date <= current_date and (partner.end_date is null or partner.end_date >= current_date) left join hr_employee as emp_partner on emp_partner.id = partner.partner_id left join hr_contract as contract on contract.employee_id=emp.id and contract.signing_status ='first_sign' left join hr_contract as contract_current on contract_current.employee_id=emp.id and contract_current.contract_agreement_status='current_contract' and contract_current.x_bp_state='done' and contract_current.contract_date_start <= current_date and (contract_current.contract_date_end is null or contract_current.contract_date_end >= current_date) left join contract_agreement as contract_type on contract_type.id = contract_current.contract_agreement_id LEFT join ir_translation AS tran_contract_type ON tran_contract_type.res_id = contract_type.id AND tran_contract_type.lang='zh_CN' AND tran_contract_type.name='contract.agreement,name' left join benefits_employee_rule as ber on ber.employee_id=emp.id and ber.is_delete=0 and ber.effective_start_date <= current_date and ber.effective_end_date >= current_date left join legal_entity as erp_legal on erp_legal.eroad_index_id = emp_job.x_erp_code and erp_legal.eroad_index_id IS NOT NULL AND erp_legal.eroad_start_date <= current_date AND (erp_legal.eroad_end_date >= current_date OR erp_legal.eroad_end_date IS NULL) AND erp_legal.active = TRUE LEFT join ir_translation AS tran_erp_legal ON tran_erp_legal.res_id = erp_legal.id AND tran_erp_legal.lang='zh_CN' AND tran_erp_legal.name='legal.entity,name' left join benefits_company_rule as bcr_social on bcr_social.de_id = ber.social_insurance_rule_de_id and bcr_social.effective_start_date <= current_date and bcr_social.effective_end_date >= current_date and bcr_social.is_delete = 0 left join benefits_company_rule as bcr_fund on bcr_fund.de_id = ber.houseing_fund_rule_de_id and bcr_fund.effective_start_date <= current_date and bcr_fund.effective_end_date >= current_date and bcr_fund.is_delete = 0 left join benefits_employee_rule_information beri on beri.group_id = ber.benefits_employee_rule_information_group_id and beri.information_type = 4 and beri.is_delete = 0 and (beri.social_insurance_start_date <= current_date and (beri.social_insurance_end_date is null or beri.social_insurance_end_date >=current_date) or beri.houseing_fund_start_date <= current_date and (beri.houseing_fund_end_date is null or beri.houseing_fund_end_date >=current_date)) left join benefits_company_account bca_social on bca_social.id = beri.social_insurance_account_unit_id and bca_social.is_delete = 0 left join benefits_company_account bca_fund on bca_fund.id = beri.houseing_fund_account_unit_id and bca_fund.is_delete = 0 left join view_tax_employee tax_view on tax_view.employee_id = emp.id and tax_view.from_month <= current_date and tax_view.to_month >= current_date 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 ir_translation AS tran_legal_entity ON tran_legal_entity.res_id = legal_entity.id AND tran_legal_entity.lang='zh_CN' AND tran_legal_entity.name='legal.entity,name' LEFT JOIN legal_entity AS legal_entity_contract ON contract_current.legal_entity_id = legal_entity_contract.eroad_index_id AND legal_entity_contract.eroad_index_id IS NOT NULL AND legal_entity_contract.eroad_start_date <= current_date AND (legal_entity_contract.eroad_end_date >= current_date OR legal_entity_contract.eroad_end_date IS NULL) AND legal_entity_contract.active = TRUE LEFT join ir_translation AS tran_legal_entity_contract ON tran_legal_entity_contract.res_id = legal_entity_contract.id AND tran_legal_entity_contract.lang='zh_CN' AND tran_legal_entity_contract.name='legal.entity,name' LEFT JOIN legal_entity AS legal_entity_salary ON emp_job.x_salary_company = legal_entity_salary.eroad_index_id AND legal_entity_salary.eroad_index_id IS NOT NULL AND legal_entity_salary.eroad_start_date <= current_date AND (legal_entity_salary.eroad_end_date >= current_date OR legal_entity_salary.eroad_end_date IS NULL) AND legal_entity_salary.active = TRUE LEFT join ir_translation AS tran_legal_entity_salary ON tran_legal_entity_salary.res_id = legal_entity_salary.id AND tran_legal_entity_salary.lang='zh_CN' AND tran_legal_entity_salary.name='legal.entity,name' 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 ir_translation AS tran_dep ON tran_dep.res_id = dep.id AND tran_dep.lang='zh_CN' AND tran_dep.name='hr.department,name' left join organization_type as organization on organization.id = dep.organization_type_id LEFT join ir_translation AS tran_organization_type ON tran_organization_type.res_id = organization.id AND tran_organization_type.lang='zh_CN' AND tran_organization_type.name='organization.type,name' 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 AS tran_position ON tran_position.res_id = position.id AND tran_position.lang='zh_CN' AND tran_position.name='hr.position,name' 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 AS emp_status ON emp_job.employee_status = emp_status.id LEFT join ir_translation AS tran_status ON tran_status.res_id = emp_status.id AND tran_status.lang='zh_CN' AND tran_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 hr_employee AS emp_manager on emp_job.manager_employee_id = emp_manager.id AND emp_manager.active = TRUE left join hr_employee AS dep_manager on dep.manager_id = dep_manager.id LEFT JOIN job_level AS job_level on emp_job.job_level = job_level.id AND job_level.active = TRUE LEFT join ir_translation AS tran_job_level ON tran_job_level.res_id = job_level.id AND tran_job_level.lang='zh_CN' AND tran_job_level.name='job.level,name' – 自然人 LEFT JOIN per_person AS person ON emp.person_id = person.id left join per_national_id as per_nati on per_nati.person_id=person.id and per_nati.is_primary = TRUE – 证件类型 LEFT JOIN res_selection AS card_type ON per_nati.card_type = card_type.id LEFT join ir_translation AS tran_card ON tran_card.res_id = card_type.id AND tran_card.lang='zh_CN' AND tran_card.name='res.selection,name' – 邮箱信息 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 ir_translation AS tran_edu ON tran_edu.res_id = edu_background.id AND tran_edu.lang='zh_CN' AND tran_edu.name='res.selection,name' LEFT JOIN res_selection AS academic_degree ON person_education.academic_degree = academic_degree.id left join person_education as person_education_high on emp.person_id = person_education_high.person_id and person_education_high.is_highest_education = TRUE – 联系人信息 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 ir_translation AS tran_relationship ON tran_relationship.res_id = emergency_contact_relationship.id AND tran_relationship.lang='zh_CN' AND tran_relationship.name='res.selection,name' – 个人信息 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_selection as residence_type on personal.registered_residence_type=residence_type.id LEFT join ir_translation AS tran_residence ON tran_residence.res_id = residence_type.id AND tran_residence.lang='zh_CN' AND tran_residence.name='res.selection,name' LEFT JOIN res_selection AS per_gender ON personal.gender = per_gender.id LEFT JOIN ir_translation as tran_gender ON tran_gender.res_id = per_gender.id AND tran_gender.lang='zh_CN' AND tran_gender.name='res.selection,name' LEFT JOIN res_selection AS marital_status ON personal.marital_status = marital_status.id LEFT join ir_translation AS tran_marital ON tran_marital.res_id = marital_status.id AND tran_marital.lang='zh_CN' AND tran_marital.name='res.selection,name' LEFT JOIN res_country AS country ON personal.nationality_id = country.id LEFT join ir_translation AS tran_country ON tran_country.res_id = country.id AND tran_country.lang='zh_CN' AND tran_country.name = 'res.country,name' 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 ir_translation AS tran_political ON tran_political.res_id = political.id AND tran_political.lang='zh_CN' AND tran_political.name = 'political.status,name' LEFT JOIN res_selection AS children_situation ON personal.children_situation = children_situation.id LEFT JOIN dep_level_view ON dep_level_view.cid = emp_job.department_id left join x_builder_know_aden_employee AS know_emp on know_emp.x_employee = emp.id left join x_builder_entry_and_exit AS entry_and_exit on entry_and_exit.x_hr_employee_id = emp.id left join x_builder_is_break_law AS break_law on break_law.x_employee = emp.id left join emp_cost_distribution AS cost on cost.employee_id = emp.id and cost.is_primary = TRUE and cost.start_date <= current_date and (cost.end_date >= current_date or cost.end_date is null) and cost.active = True left join cost_center as cs on cs.id=cost.cost_center_id LEFT join ir_translation AS tran_cs ON tran_cs.res_id = cs.id AND tran_cs.lang='zh_CN' AND tran_cs.name = 'cost.center,name' left join social_security_provident_fund as social_fund on social_fund.person_id = person.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) aa