SELECT
    emp.employee_number AS 员工号,
    emp.name AS 员工姓名,
    legal_entity.name AS 所属公司,
    dispatch_company.name AS 派遣公司,
    agency_company.name AS 代理公司,
    dep.code AS 部门代码,
    dep.name AS 部门,
    emp_group.name AS 组,
    position.name AS 职位名称,
    emp.hire_date AS 雇用公告日期,
    location.name AS 工作地,
    benefits_company_rule.city_name AS 社保缴纳城市,
    term_instance.term_type AS 离职类型,
    case when parchc.code='PA003' then parch.amount else 0 end AS 试用期基本工资,
    case when parchc.code='PA004' then parch.amount else 0 end AS 基本工资,
--     case when parchc.code='PA006' then parch.amount else 0 end AS 职责津贴,
--     case when parchc.code='PA006' then parch.expired_date else null end AS 职责津贴结束日期,
    case
    when zz.x_zhizejintie > 0 then zz.x_zhizejintie
    when zz.x_zhizejintie = 0 and zz.x_jintie > 0 then zz.x_jintie
    else nga.duty_std
    end AS 职责津贴,
    zz.x_end_date AS 职责津贴结束日期,

    case when parchc.code='PA013' then parch.amount else 0 end AS 全勤奖,
    case when parchc.code='PA009' then parch.amount else 0 end AS 调动津贴,
    case when parchc.code='PA009' then parch.expired_date else null end AS 调动津贴结束日期,
    case when parchc.code='PA008' then parch.amount else 0 end AS 地域津贴,
    case when parchc.code='PA010' then parch.amount else 0 end AS 单身赴任津贴,
    case when parchc.code='PA010' then parch.expired_date else null end AS 单身赴任津贴结束日期,
    case when parchc.code='PA011' then parch.amount else 0 end AS 生活津贴,
    case when parchc.code='PA011' then parch.expired_date else null end AS 生活津贴结束日期,
    case when parchc.code='PA016' then parch.amount else 0 end AS 调整津贴,
    benefits_employee_rule.social_insurance_employee_basis AS 社保基数,
    benefits_employee_rule.houseing_fund_employee_basis AS 公积金基数

FROM emp_job
         LEFT JOIN res_selection AS dispatch_company ON emp_job.x_dispatch_company=dispatch_company.id
         LEFT JOIN res_selection AS agency_company ON emp_job.x_agency_company=agency_company.id
         LEFT JOIN hr_employee emp ON emp_job.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 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 hr_location AS location on emp_job.location_id = location.id AND location.active = TRUE
        LEFT JOIN employee_group AS emp_group on emp_job.employee_group = emp_group.id AND emp_group.active = TRUE

        LEFT JOIN payroll_archive parch on parch.employee_id=emp.id AND parch.effective_date <= current_date AND (parch.expired_date >= current_date OR parch.expired_date IS NULL) AND parch.active = True
        LEFT JOIN payroll_archive_config parchc on parch.payroll_item_id=parchc.id

        LEFT JOIN benefits_employee_rule on emp_job.employee_id = benefits_employee_rule.employee_id AND benefits_employee_rule.is_active = 1

        LEFT JOIN benefits_company_rule on benefits_company_rule.de_id = benefits_employee_rule.social_insurance_rule_de_id AND
                      benefits_company_rule.effective_start_date <= benefits_employee_rule.effective_start_date AND
                      benefits_company_rule.effective_end_date >= benefits_employee_rule.effective_start_date
--
-- x_builder_yqcs 职责组、指责等级页签拆分 别名 zz
        LEFT JOIN x_builder_yqcs zz on zz.x_zhizezu_id = emp.id

        LEFT JOIN (
            select
--                 data->>'c1' id,
--                 data->>'c2' cate,
                data->>'c3' duty,
--                 data->>'c4' duty_level,
                data->>'c5' duty_std
            from  global_table_rows where table_id = (select id from  global_tables where code = 'nitori_grade_allowance') and data->>'c2' = 'NCH'
    ) nga on nga.duty = to_char(zz.x_zhizezu,'99')

--
        LEFT JOIN (
          SELECT
            t.name AS term_type,
            term.id AS employee_id
          FROM employee_resignation term
          LEFT JOIN resignation_type t on term.resignation_type=t.id
          WHERE term.x_bp_id IS NOT NULL AND term.x_bp_state = 'done'
) AS term_instance on term_instance.employee_id = emp.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;