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 AS 员工工号,
        emp.name AS 员工姓名,
        emp.username AS 用户名,
        emp.business_phone AS 手机号码,
        emp.hire_date AS 入职日期,
        emp.original_start_date AS 首次工作日期,
        emp.probation_date AS 转正日期,
        case emp.is_probation when 't' then '是' when 'f' then '否' end AS 是否试用期,
        emp.probation_period_end_date AS 合同试用期结束日期,
        emp.termination_date AS 离职日期,
        tran_res_country_state_x_address.value AS 联系地址省,
        tran_res_city_x_address2.value AS 联系地址市,
        emp.x_address3 AS 联系地址,
        tran_recruit_channel.value AS 招聘渠道,
        emp.x_recruit_notes AS 招聘备注,
        emp.email AS 公司邮箱,
        per_email.email_address AS 个人邮箱,
        tran_status.value AS 员工状态,
        tran_dep.value AS 部门,
        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 '26' then 'COE' when '27' then 'TAM' when '28' then 'IT' end as 部门属性,
        emp_manager.name AS 汇报上级,
        tran_position.value AS 岗位,
        tran_erp_legal.value AS ERP代码,
        tran_location_guanxia.value AS 管辖地,
        tran_job_level.value AS 职级,
        tran_legal_entity_salary.value AS 薪资发放主体,
        case emp_job.x_emp_attributes when '1' then '全日制' when '2' then '非全日制' when '3' then '项目制' end AS 员工性质,
        case emp_job.x_working_type when '1' then 'OFE' when '2' then 'Site' when '3' then '挂靠' end AS 工种,
        erp_reimbursement.x_erp_reimbursement AS ERP报销,
        tran_emp_type.value AS 员工身份,
        emp_job.x_um_type AS 工会会员类型,
        emp_job.x_is_hardship AS 是否有Hardship,
        case emp_job.x_emp_mode when '1' then 'Aden用工' when '2' then '派遣' when '3' then '外包' when '4' then '平台用工' when '5' then 'Aden用工-社保代理' end AS 用工形式,
        case emp_job.x_bonus_type when '1' then '个人绩效奖' when '2' then '在职贡献奖' when '3' then 'IPR' when '4' then 'AB' end AS 奖金类型,
        emp_job.x_working_hours AS 工时制度,
        tran_country_1.value AS 工作地（国籍）,
        tran_country_2.value AS 工作地（省）,
        tran_country_3.value AS 工作地（市）,
        emp_job.x_note AS 备注（特殊员工身份）,
        tran_country_4.value AS 工作地（区）,
        personal.x_first_name AS 英文名,
        personal.x_last_name AS 英文姓,
        personal.x_en_name AS 英文全名,
        tran_gender.value AS 性别,
        tran_residence.value as 户籍类型,
        tran_country.value as 国籍,
        nation.name AS 民族,
        political.name AS 政治面貌,
        personal.x_is_pregnant AS 是否怀孕,
        tran_marital.value AS 婚姻状况,
        tran_children_situation.value AS 生育状况,
        country_state.name AS 籍贯省,
        res_city1.name AS 籍贯市,
        personal.x_disease AS 自身疾病情况,
        person.date_of_birth AS 出生日期,
        person.age AS 年龄,
        tran_card.value AS 证件类型,
        per_nati.national_id AS 证件号码,
        per_nati.due_date AS 证件有效期,
        per_nati.x_certification_address AS 证件地址,
        bank_information.bank_account AS 银行账户,
        bank_information.subbank_name AS 开户支行,
        case bank_information.x_is_primary_card when 't' then '是' end AS 是否主卡,
        bank_information.bank_account_holder AS 开户人姓名,
        tran_edu.value AS 最高学历,
        tran_relationship.value AS 紧急联系人关系,
        emergency_contact.name AS 紧急联系人姓名,
        emergency_contact.phone AS 紧急联系人电话,
        entry_and_exit.x_entry_date AS 入境日,
        entry_and_exit.x_exit_date AS 离境日,
        break_law.x_is_break_law AS 是否有违法记录,
        tran_legal_entity_contract.value AS 签约主体,
        tran_contract_type.value AS 合同类型,
        contract_current.signing_period AS 合同期限,
        contract_current.contract_date_start AS 合同开始日期,
        contract_current.contract_date_end AS 合同结束日期,
        contract_current.trial_date_start AS 试用期开始日期,
        contract_current.trial_date_end AS 试用期结束日期,
        case contract_current.contract_agreement_status when 'current_contract' then '当前合同' when 'expired_contract' then '失效合同' when 'non_effective_contract' then '未生效合同' end AS 合同状态,
        contract_current.x_expiry_date AS 终止日期,
        contract_current.total_signing_times AS 合同签订次数,
        contract_current.code AS 合同编号,
        contract_current.x_end_remind_day AS 合同期满提醒日,
        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 "七级组织单元名称",
        org_hrbp.name AS HRBP,
        ssc.name AS AreaSSCStaff,
        hr_emp.name AS AreaSSCSupervisor,
        tran_organization_type.value AS 组织类型,
        tran_cs.value AS 成本中心,
        bcr_social.name AS 社保规则,
        bca_social.unit_name AS 社保缴纳单位,
        bcr_fund.name AS 公积金规则,
        bca_fund.unit_name AS 公积金缴纳单位,
        social_fund.social_security_account AS 个人社保账号,
        social_fund.provident_fund_account AS 个人公积金账号,
        tax_view.withhold_agent_name AS 扣缴义务人

-- 雇佣信息
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 res_country_state as country_2 on country_2.id = emp_job.x_work_place2
LEFT JOIN ir_translation AS tran_country_2 ON tran_country_2.res_id = country_2.id AND tran_country_2.lang='zh_CN' AND tran_country_2.name='res.country.state,name'
LEFT JOIN res_city as country_3 on country_3.id = emp_job.x_work_place3
LEFT JOIN ir_translation AS tran_country_3 ON tran_country_3.res_id = country_3.id AND tran_country_3.lang='zh_CN' AND tran_country_3.name='res.city,name'
LEFT JOIN res_town as country_4 on country_4.id = emp_job.x_work_place4
LEFT JOIN ir_translation AS tran_country_4 ON tran_country_4.res_id = country_4.id AND tran_country_4.lang='zh_CN' AND tran_country_4.name='res.town,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)
LEFT JOIN benefits_employee_rule_information beri1 on beri1.group_id = ber.benefits_employee_rule_information_group_id and beri1.information_type = 4 and beri1.is_delete = 0
            and beri1.houseing_fund_start_date <= current_date and (beri1.houseing_fund_end_date  is null or beri1.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 = beri1.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'

-- x_org_hrbp
LEFT JOIN hr_employee AS org_hrbp ON dep.hrbp_id = org_hrbp.id

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_education = TRUE
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 (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 res_city as res_city1 on personal.x_native_city=res_city1.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 ir_translation AS tran_children_situation ON tran_children_situation.res_id = children_situation.id AND tran_children_situation.lang='zh_CN' AND tran_children_situation.name='res.selection,name'

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

-- 招聘渠道 x_recruit_channel
LEFT JOIN res_selection AS recruit_channel ON emp.x_recruit_channel = recruit_channel.id
LEFT JOIN ir_translation AS tran_recruit_channel ON tran_recruit_channel.res_id = recruit_channel.id AND tran_recruit_channel.lang='zh_CN' AND tran_recruit_channel.name='res.selection,name'

LEFT JOIN res_country_state as res_country_state_x_address on res_country_state_x_address.id = emp.x_address
LEFT JOIN ir_translation AS tran_res_country_state_x_address ON tran_res_country_state_x_address.res_id = res_country_state_x_address.id AND tran_res_country_state_x_address.lang='zh_CN' AND tran_res_country_state_x_address.name='res.country.state,name'

-- x_erp_reimbursement
-- 字段标签	ERP报销
LEFT JOIN (
select
    emp_job.id as emp_job_id,
-- string_agg(legal_entity.name,';') as x_erp_reimbursement
    string_agg(ir_translation.value,';') as x_erp_reimbursement
from x_emp_job_legal_entity_rel
LEFT JOIN emp_job on x_emp_job_legal_entity_rel.emp_job_id=emp_job.id and
        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
LEFT JOIN legal_entity on x_emp_job_legal_entity_rel.legal_entity_id=legal_entity.id and
	legal_entity.eroad_index_id IS 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 on legal_entity.id=ir_translation.res_id and ir_translation.name ='legal.entity,name' and ir_translation.lang='zh_CN'
group by emp_job.id
    ) erp_reimbursement on erp_reimbursement.emp_job_id = emp_job.id

LEFT JOIN res_city res_city_x_address2 on res_city_x_address2.id = emp.x_address2
LEFT JOIN ir_translation AS tran_res_city_x_address2 ON tran_res_city_x_address2.res_id = res_city_x_address2.id AND tran_res_city_x_address2.lang='zh_CN' AND tran_res_city_x_address2.name='res.city,name'

-- x_sscstaff
LEFT JOIN (
select x_hr_department_hr_employee_rel.hr_department_id as hr_department_id, hr_employee.name as name
from x_hr_department_hr_employee_rel
LEFT JOIN hr_employee ON x_hr_department_hr_employee_rel.hr_employee_id = hr_employee.id
LEFT JOIN hr_department ON x_hr_department_hr_employee_rel.hr_department_id = hr_department.id AND
	hr_department.eroad_index_id IS NOT NULL AND
	hr_department.eroad_start_date <= current_date AND
	(hr_department.eroad_end_date >= current_date OR hr_department.eroad_end_date IS NULL) AND
	hr_department.active = TRUE
    ) ssc ON ssc.hr_department_id = emp_job.department_id

-- x_sscsup
LEFT JOIN hr_employee hr_emp ON hr_emp.id = dep.x_sscsup

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) foo
