WITH RECURSIVE dep_level_view AS ( select eroad_index_id as cid, name,code, ARRAY[name] as name_list, ARRAY[eroad_index_id] as path_ids, FALSE as is_cycle from hr_department 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_table.name, dep_table.code, dep_level_view.name_list || dep_table.name 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 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, -- 是否有违法记录 legal_entity.name AS legal_entity_name, -- 签约主体 legal_entity_salary.name 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, -- 工时制度 erp_legal.name as x_erp_code, -- ERP代码 cs.code 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 "七级组织单元名称" , dep.name AS "当前组织单元名称" , -- 当前组织单元名称 organization.name AS organization_type, -- 组织类型 dep.name 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, location_guanxia.name AS location_guanxia, -- 管辖地 country_1.name AS x_work_place, -- 工作地 position.name AS position_name, -- 岗位 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 contract_type.name AS contract_type, -- 合同类型 contract_current.code AS contract_code, -- 合同编号 contract_current.contract_agreement_status, -- 合同状态 contract_current.total_signing_times, -- 合同签订次数 legal_entity_contract.name 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 res_country as country_1 on country_1.id = emp_job.x_work_place 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' left join contract_agreement as contract_type on contract_type.id = contract_current.contract_agreement_id left join benefits_employee_rule as ber on ber.employee_id=emp.id and ber.is_delete=0 left join legal_entity as erp_legal on erp_legal.id = emp_job.x_erp_code 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 <= ber.effective_start_date and bcr_social.effective_end_date >= ber.effective_start_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 <= ber.effective_start_date and bcr_fund.effective_end_date >= ber.effective_start_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 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 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 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 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 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 organization_type as organization on organization.id = dep.organization_type_id 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 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 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 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 left join cost_center as cs on cs.id=cost.cost_center_id 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;