<?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="com.bonus.system.att.dao.AttExceptionHandleDao2">

	<select id="selectAttExceptionList" resultType="com.bonus.system.att.entity.AttExceptionBean">
		SELECT
			DATE_FORMAT( att_current_day, '%Y-%m' ) AS yearMonth,
			SUM(
					CASE

						WHEN toWorkOriginalAttStatus IN ( 2, 3, 4, 8, 9 )
							OR offWorkOriginalAttStatus IN ( 2, 3, 4, 8, 9 ) THEN
							1 ELSE 0
						END
			) AS errorCount,
			SUM(
					CASE

						WHEN (
								 toWorkOriginalAttStatus IN ( 2, 3, 4, 8, 9 )
									 OR offWorkOriginalAttStatus IN ( 2, 3, 4, 8, 9 ))
							AND is_update = 1
							AND reviewer_status = 1 THEN
							1 ELSE 0
						END
			) AS finishCount
		FROM
			v_att_update_data
		<where>
			locate(#{attCurrentMonth},att_current_day)
		</where>
		GROUP BY
			yearMonth
	</select>

	<select id="getDetailException" resultType="com.bonus.system.att.entity.AttExceptionBean">
		SELECT
			vv.user_id,
			vv.org_id,
			su.user_name,
			so.org_name,
			sum( IF ( toWorkOriginalAttStatus = 2 OR offWorkOriginalAttStatus = 2, 1, 0 ) ) as lateCount,
			sum( IF ( toWorkOriginalAttStatus = 3 OR offWorkOriginalAttStatus = 3, 1, 0 ) ) as absenteeismCount,
			sum( IF ( toWorkOriginalAttStatus = 4 OR offWorkOriginalAttStatus = 4, 1, 0 ) ) as earlyCount,
			sum( IF ( toWorkOriginalAttStatus = 8 OR offWorkOriginalAttStatus = 8, 1, 0 ) ) as addressCount,
			sum( IF ( toWorkOriginalAttStatus = 9 OR offWorkOriginalAttStatus = 9, 1, 0 ) ) as goOutCount
		FROM
			v_att_update_data vv
				LEFT JOIN sys_user su ON su.user_id = vv.user_id
				AND su.is_active = '1'
				LEFT JOIN sys_organization so ON so.id = vv.org_id
				AND so.is_active = '1'
		WHERE
			locate( #{attCurrentMonth}, att_current_day )
		GROUP BY
			user_id,
			org_id
	</select>

</mapper>
