-- 休假明细表
WITH holiday_leave AS (
        SELECT
                hr_holiday_leave.employee_id AS employee_id,
                hr_holiday_leave.start_date AS start_date,
                hr_holiday_leave.end_date AS end_date,
                CASE leave_uom.name WHEN 'Day(s)' THEN '天' WHEN 'Hour(s)' THEN '小时' END AS leave_uom_name,
                CASE t_hr_holiday_type.value WHEN '长病假(30 天以上病假)' THEN hr_holiday_leave.leave_total END AS "长病假(30 天以上病假)",
                CASE t_hr_holiday_type.value WHEN '调休假' THEN hr_holiday_leave.leave_total END AS "调休假",
                CASE t_hr_holiday_type.value WHEN '产假' THEN hr_holiday_leave.leave_total END AS "产假",
                CASE t_hr_holiday_type.value WHEN '探亲假' THEN hr_holiday_leave.leave_total END AS "探亲假",
                CASE t_hr_holiday_type.value WHEN '产检假' THEN hr_holiday_leave.leave_total END AS "产检假",
                CASE t_hr_holiday_type.value WHEN '年假' THEN hr_holiday_leave.leave_total END AS "年假",
                CASE t_hr_holiday_type.value WHEN '事假' THEN hr_holiday_leave.leave_total END AS "事假",
                CASE t_hr_holiday_type.value WHEN '陪产假' THEN hr_holiday_leave.leave_total END AS "陪产假",
                CASE t_hr_holiday_type.value WHEN '法定年假' THEN hr_holiday_leave.leave_total END AS "法定年假",
                CASE t_hr_holiday_type.value WHEN '病假' THEN hr_holiday_leave.leave_total END AS "病假",
                CASE t_hr_holiday_type.value WHEN '丧假' THEN hr_holiday_leave.leave_total END AS "丧假",
                CASE t_hr_holiday_type.value WHEN '婚假' THEN hr_holiday_leave.leave_total END AS "婚假"
        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')
                AND
                hr_holiday_leave.start_date >= '${start_date:2021-12-01}'
                AND
                hr_holiday_leave.end_date <= '${end_date:2021-12-31}'
)
SELECT
        emp.employee_number AS "员工编号",
        emp.name AS "员工姓名",
        tran_dep.value AS "部门名称",
        tran_position.value AS "岗位",
        tran_employee_type.value "员工类型",
        tran_status.value AS "员工状态",
        CASE emp_job.x_branch
            WHEN '2' THEN '品牌群-北京分公司'
            WHEN '3' THEN '品牌群-成都分公司'
            WHEN '4' THEN '品牌群-广州分公司'
            WHEN '5' THEN '品牌群-杭州分公司-杭州'
            WHEN '6' THEN '品牌群-合肥分公司'
            WHEN '7' THEN '品牌群-南昌分公司'
            WHEN '8' THEN '品牌群-南京分公司'
            WHEN '9' THEN '品牌群-青岛分公司'
            WHEN '10' THEN '品牌群-厦门分公司'
            WHEN '11' THEN '品牌群-沈阳分公司'
            WHEN '12' THEN '品牌群-太原分公司'
            WHEN '13' THEN '品牌群-石家庄分公司'
            WHEN '14' THEN '品牌群-南宁分公司'
            WHEN '15' THEN '品牌群-华南分公司'
            WHEN '16' THEN '品牌群-内蒙分部'
            WHEN '17' THEN '品牌群-天津分公司'
            WHEN '18' THEN '品牌群-武汉分公司'
            WHEN '19' THEN '品牌群-西安分公司'
            WHEN '20' THEN '品牌群-银川分公司'
            WHEN '21' THEN '品牌群-长沙分公司'
            WHEN '22' THEN '品牌群-郑州分公司'
            WHEN '23' THEN '品牌群-重庆分公司'
            WHEN '30' THEN '华东区域'
            WHEN '31' THEN '华南区域'
            WHEN '32' THEN '福建区域'
            WHEN '33' THEN '西部区域'
            WHEN '34' THEN '华北区域'
            WHEN '35' THEN '北京区域'
            WHEN '36' THEN '东北区域'
            WHEN '37' THEN '西南区域'
            WHEN '38' THEN '川渝区域'
            WHEN '39' THEN '江西区域'
            WHEN '40' THEN '湖南区域'
            WHEN '41' THEN '湖北区域'
            WHEN '42' THEN '品牌群-杭州分公司-上海'
            WHEN '43' THEN '品牌群-杭州分公司-温州'
            WHEN '44' THEN '品牌群-杭州分公司-宁波'
            WHEN '45' THEN '华中大区'
            WHEN '46' THEN '亨吉利总部'  ELSE NULL END AS "所属分公司",
        hr_business_unit.name AS "业务单元",
        tran_employee_group.value AS "员工分组",
        tran_legal_entity.value AS "法人实体",
        holiday_leave.start_date AS "开始日期",
        holiday_leave.end_date AS "结束日期",
        holiday_leave."年假" || ' ' || holiday_leave.leave_uom_name AS "年假",
        holiday_leave."法定年假" || ' ' || holiday_leave.leave_uom_name AS "法定年假",
        holiday_leave."事假" || ' ' || holiday_leave.leave_uom_name AS "事假",
        holiday_leave."病假" || ' ' || holiday_leave.leave_uom_name AS "病假",
        holiday_leave."调休假" || ' ' || holiday_leave.leave_uom_name AS "调休假",
        holiday_leave."婚假" || ' ' || holiday_leave.leave_uom_name AS "婚假",
        holiday_leave."产检假" || ' ' || holiday_leave.leave_uom_name AS "产检假",
        holiday_leave."产假" || ' ' || holiday_leave.leave_uom_name AS "产假",
        holiday_leave."陪产假" || ' ' || holiday_leave.leave_uom_name AS "陪产假",
        holiday_leave."探亲假" || ' ' || holiday_leave.leave_uom_name AS "探亲假",
        holiday_leave."丧假" || ' ' || holiday_leave.leave_uom_name AS "丧假",
        holiday_leave."长病假(30 天以上病假)" || ' ' || holiday_leave.leave_uom_name AS "长病假(30 天以上病假)"

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 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 employee_type ON emp_job.employee_type=employee_type.id
LEFT JOIN ir_translation tran_employee_type ON tran_employee_type.res_id=employee_type.id AND tran_employee_type.name ='employee.type,name' AND tran_employee_type.lang='zh_CN'

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 hr_business_unit ON dep.business_unit_id = hr_business_unit.id

LEFT JOIN legal_entity ON emp_job.legal_entity_id = legal_entity.id
LEFT JOIN ir_translation tran_legal_entity ON tran_legal_entity.res_id = legal_entity.id AND tran_legal_entity.name = 'legal.entity,name' AND tran_legal_entity.lang = 'zh_CN'

LEFT JOIN employee_group ON emp_job.employee_group = employee_group.id
LEFT JOIN ir_translation tran_employee_group ON tran_employee_group.res_id = employee_group.id AND tran_employee_group.name = 'employee.group,name'AND tran_employee_group.lang ='zh_CN'

LEFT JOIN holiday_leave ON holiday_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
