-- https://pm.ersoft.cn/browse/SLAL2-4749
-- 考勤周期
WITH attendance_period AS (
        SELECT
            employee_id,
            TO_CHAR(attendance_date, 'YYYY-MM') AS "ap",
            TO_CHAR(TO_DATE(TO_CHAR(attendance_date, 'YYYY-MM'), 'YYYY-MM')+INTERVAL'-1 month', 'YYYY-MM') || '-' || '21' AS "ap_s_date",
            TO_CHAR(attendance_date, 'YYYY-MM') || '-' || '20' AS "ap_e_date"
        FROM attendance_period
        WHERE active=TRUE
        GROUP BY employee_id,TO_CHAR(attendance_date, 'YYYY-MM') ORDER BY TO_CHAR(attendance_date, 'YYYY-MM')
    )

SELECT
        ROW_NUMBER() OVER() AS "序号",
        attendance_period.ap AS "考勤月份",
        attendance_period.ap_s_date AS "考勤周期开始日期",
        attendance_period.ap_e_date AS "考勤周期截至日期",
        emp.employee_number AS "工号",
        emp.name AS "姓名",
        tran_dep.value AS "部门",
        dc.plan_attendance_hours AS "应出勤（H）",
        aa.real_attendance_hours AS "实出勤（H）",
        ab.sum_absence_time AS "旷工（H）",
        pb.count_zb AS "早班（次）",
        pb.count_yb AS "夜班（次）",
--         CASE ol.cw_type WHEN '工作日加班-换薪' THEN ol.sum_active_time END AS "工作日加班-换薪",
--         CASE ol.cw_type WHEN '休息日加班-换薪' THEN ol.sum_active_time END AS "休息日加班-换薪",
--         CASE ol.cw_type WHEN '节假日加班-换薪' THEN ol.sum_active_time END AS "节假日加班-换薪",
--         CASE ol.cw_type WHEN '工作日加班-调休' THEN ol.sum_active_time END AS "工作日加班-调休",
--         CASE ol.cw_type WHEN '休息日加班-调休' THEN ol.sum_active_time END AS "休息日加班-调休",
--         CASE ol.cw_type WHEN '节假日加班-调休' THEN ol.sum_active_time END AS "节假日加班-调休",
        ol."工作日加班-换薪" AS "工作日加班-换薪",
        ol."休息日加班-换薪" AS "休息日加班-换薪",
        ol."节假日加班-换薪" AS "节假日加班-换薪",
        ol."工作日加班-调休" AS "工作日加班-调休",
        ol."休息日加班-调休" AS "休息日加班-调休",
        ol."节假日加班-调休" 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."流产假" || ' ' || holiday_leave.leave_uom_name AS "公假",
        holiday_leave."陪产假" || ' ' || holiday_leave.leave_uom_name AS "陪产假",
        holiday_leave."出差" || ' ' || holiday_leave.leave_uom_name AS "出差",
        ac.minutes_lateleave_early AS "迟到早退总时长（分钟）",
        ac.count_lateleave_early AS "迟到早退总次数",
        '' AS "备注",
        '' AS "逻辑核对"

FROM emp_job
LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.id
-- LEFT JOIN payroll_schedule_info_11 AS psi ON psi.hr_employee_employee_number = emp_job.employee_number

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 attendance_period ON attendance_period.employee_id = emp_job.employee_id

-- LEFT JOIN ** ON **.** = **.**

-- 休假
LEFT JOIN (
        SELECT
                hr_holiday_leave.employee_id AS employee_id,
                attendance_period.ap AS ap,
                CASE leave_uom.name WHEN 'Day(s)' THEN '天' WHEN 'Hour(s)' THEN '小时' END AS leave_uom_name,
                SUM(CASE t_hr_holiday_type.value WHEN '三八福利假' THEN hr_holiday_leave.leave_total END) AS "三八福利假",
                SUM(CASE t_hr_holiday_type.value WHEN '丧假' THEN hr_holiday_leave.leave_total END) AS "丧假",
                SUM(CASE t_hr_holiday_type.value WHEN '事假' THEN hr_holiday_leave.leave_total END) AS "事假",
                SUM(CASE t_hr_holiday_type.value WHEN '产假' THEN hr_holiday_leave.leave_total END) AS "产假",
                SUM(CASE t_hr_holiday_type.value WHEN '产检假' THEN hr_holiday_leave.leave_total END) AS "产检假",
                SUM(CASE t_hr_holiday_type.value WHEN '公司年假' THEN hr_holiday_leave.leave_total END) AS "公司年假",
                SUM(CASE t_hr_holiday_type.value WHEN '出差' THEN hr_holiday_leave.leave_total END) AS "出差",
                SUM(CASE t_hr_holiday_type.value WHEN '哺乳假' THEN hr_holiday_leave.leave_total END) AS "哺乳假",
                SUM(CASE t_hr_holiday_type.value WHEN '外出' THEN hr_holiday_leave.leave_total END) AS "外出",
                SUM(CASE t_hr_holiday_type.value WHEN '婚假' THEN hr_holiday_leave.leave_total END) AS "婚假",
                SUM(CASE t_hr_holiday_type.value WHEN '带薪病假' THEN hr_holiday_leave.leave_total END) AS "带薪病假",
                SUM(CASE t_hr_holiday_type.value WHEN '年假' THEN hr_holiday_leave.leave_total END) AS "年假",
                SUM(CASE t_hr_holiday_type.value WHEN '法定年假' THEN hr_holiday_leave.leave_total END) AS "法定年假",
                SUM(CASE t_hr_holiday_type.value WHEN '流产假' THEN hr_holiday_leave.leave_total END) AS "流产假",
                SUM(CASE t_hr_holiday_type.value WHEN '病假' THEN hr_holiday_leave.leave_total END) AS "病假",
                SUM(CASE t_hr_holiday_type.value WHEN '调休假' THEN hr_holiday_leave.leave_total END) AS "调休假",
                SUM(CASE t_hr_holiday_type.value WHEN '陪产假' THEN hr_holiday_leave.leave_total END) AS "陪产假",
                SUM(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
        LEFT JOIN attendance_period ON attendance_period.employee_id = hr_holiday_leave.employee_id
        WHERE
                (hr_holiday_leave.x_bp_state='done')
                AND
--                 TO_CHAR(hr_holiday_leave.start_date, 'YYYY-MM-DD') >= '2021-05-21'
                TO_CHAR(hr_holiday_leave.start_date, 'YYYY-MM-DD') >= attendance_period.ap_s_date
                AND
--                 TO_CHAR(hr_holiday_leave.end_date, 'YYYY-MM-DD') <= '2021-06-20'
                TO_CHAR(hr_holiday_leave.end_date, 'YYYY-MM-DD') <= attendance_period.ap_e_date
        GROUP BY hr_holiday_leave.employee_id, leave_uom.name, attendance_period.ap
    ) holiday_leave ON holiday_leave.employee_id = emp_job.employee_id AND attendance_period.ap = holiday_leave.ap

-- 考勤周期内应出勤时数
LEFT JOIN (
        SELECT
            day_calendar.employee_id AS employee_id,
            attendance_period.ap AS ap,
            SUM(daily_model.working_hours) AS plan_attendance_hours
        FROM day_calendar
        LEFT JOIN daily_model ON daily_model.id = day_calendar.daily_model_id
        LEFT JOIN attendance_period ON attendance_period.employee_id = day_calendar.employee_id
        WHERE
                day_calendar.active = TRUE
                AND
                day_calendar.day_type = 'work'
                AND
--                 day_calendar.day >= '2021-05-21'
                TO_CHAR(day_calendar.day, 'YYYY-MM-DD') >= attendance_period.ap_s_date
                AND
--                 day_calendar.day <= '2021-06-20'
                TO_CHAR(day_calendar.day, 'YYYY-MM-DD') <= attendance_period.ap_e_date
--                 AND
--                 day_calendar.employee_id = '311'
        GROUP BY day_calendar.employee_id, attendance_period.ap
    ) dc ON dc.employee_id = emp_job.employee_id AND attendance_period.ap = dc.ap

-- 实际出勤小时数/考勤周期内实际出勤时数
LEFT JOIN (
        SELECT
               h.employee_id AS employee_id,
               attendance_period.ap AS ap,
               ROUND(SUM(h.attendance_hours)::NUMERIC, 2) AS real_attendance_hours
        FROM hr_attendance_details h
        LEFT JOIN attendance_period ON attendance_period.employee_id = h.employee_id
        WHERE
        --     h.attendance_date >= '2021-05-21'
            TO_CHAR(h.attendance_date, 'YYYY-MM-DD') >= attendance_period.ap_s_date
            AND
        --     h.attendance_date <= '2021-06-20'
            TO_CHAR(h.attendance_date, 'YYYY-MM-DD') <= attendance_period.ap_e_date
        GROUP BY h.employee_id, attendance_period.ap
    ) aa ON aa.employee_id = emp_job.employee_id AND attendance_period.ap = aa.ap

-- 早班次数/夜班次数
LEFT JOIN (
        SELECT
            day_calendar.employee_id AS employee_id,
            attendance_period.ap AS ap,
            COUNT(CASE daily_model.code WHEN 'DM01' THEN day_calendar.id END) AS count_zb,
            COUNT(CASE daily_model.code WHEN 'DM02' THEN day_calendar.id END) AS count_yb
        FROM day_calendar
        LEFT JOIN daily_model ON daily_model.id = day_calendar.daily_model_id
        LEFT JOIN attendance_period ON attendance_period.employee_id = day_calendar.employee_id
        WHERE
                (daily_model.code = 'DM01' OR daily_model.code = 'DM02')
                AND
                day_calendar.active = TRUE
                AND
                day_calendar.day_type = 'work'
                AND
--                 day_calendar.day >= '2021-05-21'
                TO_CHAR(day_calendar.day, 'YYYY-MM-DD') >= attendance_period.ap_s_date
                AND
--                 day_calendar.day <= '2021-06-20'
                TO_CHAR(day_calendar.day, 'YYYY-MM-DD') <= attendance_period.ap_e_date
--                 AND
--                 day_calendar.employee_id = '311'
        GROUP BY day_calendar.employee_id, attendance_period.ap
    ) pb ON pb.employee_id = emp_job.employee_id AND attendance_period.ap = pb.ap

-- 迟到/早退
LEFT JOIN (
        SELECT
            attendance_count.employee_id AS employee_id,
            attendance_period.ap AS ap,
            SUM(late_count+leave_early_count) AS count_lateleave_early,
            ROUND(SUM(late_time+leave_early_time)::NUMERIC, 2) AS minutes_lateleave_early
        FROM attendance_count
        LEFT JOIN attendance_period ON attendance_period.employee_id = attendance_count.employee_id
        WHERE
        --     attendance_count.name >= '2021-05-21'
            TO_CHAR(attendance_count.name, 'YYYY-MM-DD') >= attendance_period.ap_s_date
            AND
        --     attendance_count.name <= '2021-06-20'
            TO_CHAR(attendance_count.name, 'YYYY-MM-DD') <= attendance_period.ap_e_date
--             AND
--             attendance_count.employee_id = '311'
        GROUP BY attendance_count.employee_id, attendance_period.ap
    ) ac ON ac.employee_id = emp_job.employee_id AND attendance_period.ap = ac.ap

-- 加班
LEFT JOIN (
        SELECT
            hr_overtime_line.employee_id AS employee_id,
            attendance_period.ap AS ap,
--             t_hot.value || '-' || t_cw.value AS cw_type,
            SUM(hr_overtime_line.active_time) AS sum_active_time,
            SUM(CASE t_hot.value || '-' || t_cw.value WHEN '工作日加班-换薪' THEN hr_overtime_line.active_time END) AS "工作日加班-换薪",
            SUM(CASE t_hot.value || '-' || t_cw.value WHEN '休息日加班-换薪' THEN hr_overtime_line.active_time END) AS "休息日加班-换薪",
            SUM(CASE t_hot.value || '-' || t_cw.value WHEN '节假日加班-换薪' THEN hr_overtime_line.active_time END) AS "节假日加班-换薪",
            SUM(CASE t_hot.value || '-' || t_cw.value WHEN '工作日加班-调休' THEN hr_overtime_line.active_time END) AS "工作日加班-调休",
            SUM(CASE t_hot.value || '-' || t_cw.value WHEN '休息日加班-调休' THEN hr_overtime_line.active_time END) AS "休息日加班-调休",
            SUM(CASE t_hot.value || '-' || t_cw.value WHEN '节假日加班-调休' THEN hr_overtime_line.active_time END) AS "节假日加班-调休"
        FROM hr_overtime_line
        LEFT JOIN hr_overtime_type ON hr_overtime_type.id = hr_overtime_line.overtime_type
        LEFT JOIN ir_translation AS t_hot ON hr_overtime_type.id = t_hot.res_id AND t_hot.lang='zh_CN' AND t_hot.name='hr.overtime.type,name'
        LEFT JOIN compensation_way ON compensation_way.id = hr_overtime_line.compensation_way
        LEFT JOIN ir_translation AS t_cw ON compensation_way.id = t_cw.res_id AND t_cw.lang='zh_CN' AND t_cw.name='compensation.way,name'
        LEFT JOIN attendance_period ON attendance_period.employee_id = hr_overtime_line.employee_id
        WHERE
                hr_overtime_line.calculate_state = 'done'
                AND
                hr_overtime_line.active = TRUE
                AND
--                 hr_overtime_line.schedule_overtime_date >= '2021-05-21'
                TO_CHAR(hr_overtime_line.schedule_overtime_date, 'YYYY-MM-DD') >= attendance_period.ap_s_date
                AND
--                 hr_overtime_line.schedule_overtime_date <= '2021-06-20'
                TO_CHAR(hr_overtime_line.schedule_overtime_date, 'YYYY-MM-DD') <= attendance_period.ap_e_date
--                 AND
--                 hr_overtime_line.employee_id = '311'
        GROUP BY hr_overtime_line.employee_id, attendance_period.ap--, t_hot.value, t_cw.value
    ) ol ON ol.employee_id = emp_job.employee_id AND attendance_period.ap = ol.ap

-- 旷工
LEFT JOIN (
        SELECT
            attendance_submit_detail.employee_id AS employee_id,
            attendance_period.ap AS ap,
            SUM(attendance_submit_detail.duration) AS sum_absence_time
        FROM attendance_submit_detail
        LEFT JOIN attendance_period ON attendance_period.employee_id = attendance_submit_detail.employee_id
        WHERE
                (attendance_submit_detail.type_code = 'late_time' OR attendance_submit_detail.type_code = 'leave_early_time')
--                 AND
--                 attendance_submit_detail.day_type = 'workday'
--                 AND
--                 attendance_submit_detail.date_from >= '2021-05-21'
                AND
                TO_CHAR(attendance_submit_detail.date_from, 'YYYY-MM-DD') >= attendance_period.ap_s_date
--                 AND
--                 attendance_submit_detail.date_from <= '2021-06-20'
                AND
                TO_CHAR(attendance_submit_detail.date_from, 'YYYY-MM-DD') <= attendance_period.ap_e_date
--                 AND
--                 attendance_submit_detail.employee_id = '311'
        GROUP BY attendance_submit_detail.employee_id, attendance_period.ap
    ) ab ON ab.employee_id = emp_job.employee_id AND attendance_period.ap = ab.ap

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
