SELECT
        emp.employee_number AS 员工工号,
        emp.name AS 员工姓名,
        tran_dep.value AS 部门,
        per_national_id.national_id 身份证号,
        TO_CHAR(person.date_of_birth, 'YYYY-MM') AS 出生年月,
        person.date_of_birth AS 出生日期,
        person.age AS 年龄,
        tran_gender.value AS 性别,
        tran_marital.value AS 婚姻状况,
        emp.hire_date AS 入职日期,
        CASE emp.is_from_rehire WHEN 't' THEN '是' WHEN 'f' THEN '否' END AS 是否重新雇佣,
        leave.leave_type AS 请假类型,
        leave.leave_date_time AS 休假起止时间,
        leave.leave_uom AS 时间单位,
        leave.leave_total AS 休假时长

FROM emp_job
LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.id
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 per_person AS person ON emp.person_id = person.id
LEFT JOIN per_national_id ON per_national_id.person_id=person.id AND per_national_id.is_primary=True

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 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 (
--         hr_holiday_leave
        SELECT
                hr_holiday_leave.employee_id AS employee_id,
                t_hr_holiday_type.value AS leave_type,  --请假类型
                hr_holiday_leave.start_date || '~' || hr_holiday_leave.end_date AS leave_date,  --休假起止日期
                hr_holiday_leave.start_time || '~' || hr_holiday_leave.end_time AS leave_time,  --休假起止时间
                hr_holiday_leave.start_date || ' ' || hr_holiday_leave.start_time || '~' || hr_holiday_leave.end_date  || ' ' || hr_holiday_leave.end_time AS leave_date_time,  --休假起止日期时间
                CASE leave_uom.name WHEN 'Day(s)' THEN '天' WHEN 'Hour(s)' THEN '小时' END AS leave_uom,  --时间单位
                hr_holiday_leave.leave_total AS leave_total  --休假时长
        FROM hr_holiday_leave
        LEFT JOIN hr_holiday_type ON hr_holiday_leave.holiday_type_id = hr_holiday_type.id
        LEFT JOIN ir_translation AS t_hr_holiday_type ON t_hr_holiday_type.res_id = hr_holiday_type.id AND t_hr_holiday_type.lang='zh_CN' AND t_hr_holiday_type.name='hr.holiday.type,name'
        LEFT JOIN leave_uom ON hr_holiday_leave.product_uom_id = leave_uom.id
        WHERE (hr_holiday_leave.x_bp_state='done')
    ) leave ON leave.employee_id = emp_job.employee_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
