<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.com.oa.mapper.AttendanceRecordResultMapper">

    <select id="getResultMonth" resultType="java.util.Map">
        SELECT
            (
                SELECT
                    COUNT(*)
                FROM
                    hr_attendance_record_result
                WHERE
                    special_type = '1'
                    <if test="attendanceRecordResult.unitId!=null">
                        AND unit_id = #{attendanceRecordResult.unitId}
                    </if>
                  AND att_date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND CURDATE()
                  AND late1_num = 0
                  AND late2_num = 0
                  AND late3_num = 0
                  AND late4_num = 0
                  AND late5_num = 0
                  AND leave_early1_num = 0
                  AND absenteeism_num = 0
		          AND segment_name IS NOT NULL 
            ) AS zcCount,
            (
                SELECT
                    COUNT(*)
                FROM
                    hr_attendance_record_result
                WHERE
                    special_type = '1'
                    <if test="attendanceRecordResult.unitId!=null">
                        AND unit_id = #{attendanceRecordResult.unitId}
                    </if>
                  AND att_date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND CURDATE()
                  AND general_holiday_num > 0
                  AND segment_name IS NOT NULL
        ) AS gxCount,
            (
                SELECT
                    COUNT(*)
                FROM
                    hr_attendance_record_result
                WHERE
                    special_type = '1'
                    <if test="attendanceRecordResult.unitId!=null">
                        AND unit_id = #{attendanceRecordResult.unitId}
                    </if>
                    AND att_date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND CURDATE()
                    AND out_num > 0
                    AND segment_name IS NOT NULL
        ) AS wcCount,
            (
                SELECT
                    COUNT(*)
                FROM
                    hr_attendance_record_result
                WHERE
                    special_type = '1'
                    <if test="attendanceRecordResult.unitId!=null">
                        AND unit_id = #{attendanceRecordResult.unitId}
                    </if>
                  AND att_date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND CURDATE()
                  AND segment_name IS NULL
                  AND general_holiday_num = 0
            ) AS wpbCount,
            (
                SELECT
                    COUNT(*)
                FROM
                    hr_attendance_record_result
                WHERE
                    special_type = '1'
                    <if test="attendanceRecordResult.unitId!=null">
                        AND unit_id = #{attendanceRecordResult.unitId}
                    </if>
                  AND att_date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND CURDATE()
                  AND (late1_num > 0 OR late2_num > 0 OR late3_num > 0 OR late4_num > 0 OR late5_num > 0)
                  AND segment_name IS NOT NULL
        ) AS cdCount,
            (
                SELECT
                    COUNT(*)
                FROM
                    hr_attendance_record_result
                WHERE
                    special_type = '1'
                    <if test="attendanceRecordResult.unitId!=null">
                        AND unit_id = #{attendanceRecordResult.unitId}
                    </if>
                  AND att_date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND CURDATE()
                  AND leave_early1_num > 0
                  AND segment_name IS NOT NULL
        ) AS ztCount,
            (
                SELECT
                    COUNT(*)
                FROM
                    hr_attendance_record_result
                WHERE
                    special_type = '1'
                    <if test="attendanceRecordResult.unitId!=null">
                        AND unit_id = #{attendanceRecordResult.unitId}
                    </if>
                  AND att_date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND CURDATE()
                  AND absenteeism_num > 0
                  AND segment_name IS NOT NULL
        ) AS qqCount
    </select>

    <select id="getResultDayAndDept" resultType="java.util.Map">
        SELECT
            tg.GROUPNAME,
            SUM(CASE WHEN harr.late1_num = 0 AND harr.late2_num = 0 AND harr.late3_num = 0 AND harr.late4_num = 0 AND harr.late5_num = 0 AND harr.leave_early1_num = 0 AND harr.absenteeism_num = 0 THEN 1 ELSE 0 END) AS zcCount,
            SUM(CASE WHEN harr.general_holiday_num > 0 THEN 1 ELSE 0 END) AS gxCount,
            SUM(CASE WHEN harr.out_num > 0 THEN 1 ELSE 0 END) AS wcCount,
            SUM(CASE WHEN harr.segment_name IS NULL AND harr.general_holiday_num = 0 THEN 1 ELSE 0 END) AS wpbCount,
            SUM(CASE WHEN harr.late1_num > 0 OR harr.late2_num > 0 OR harr.late3_num > 0 OR harr.late4_num > 0 OR harr.late5_num > 0 THEN 1 ELSE 0 END) AS cdCount,
            SUM(CASE WHEN harr.leave_early1_num > 0 THEN 1 ELSE 0 END) AS ztCount,
            SUM(CASE WHEN harr.absenteeism_num > 0 THEN 1 ELSE 0 END) AS qqCount
        FROM
            hr_attendance_record_result harr
                JOIN
            t_user tu ON harr.sys_user_id = tu.UNIVERSALID
                JOIN
            t_group tg ON tu.groupid=tg.UNIVERSALID
        WHERE
            harr.special_type = '1'
          AND harr.att_date = DATE_FORMAT(CURDATE(), '%Y-%m-01')
        GROUP BY
            tu.groupid
    </select>
</mapper>
