<?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="SapCheckMapper">
	<update id="deleteCheckinList">
		truncate table sap_checkin
	</update>
	<insert id="saveCheckin">
		insert into sap_checkin(userId,groupName,checkinType,checkinDate,checkinDay,exceptionType,
			checkinTime,locationTitle,locationDetail,wifiName,wifiMac) 
		values (#{userId},#{groupName},#{checkinType},#{checkinDate},#{checkinDay},#{exceptionType},
			#{checkinTime},#{locationTitle},#{locationDetail},#{wifiName},#{wifiMac})
	</insert>
	<update id="deleteCheckoffList">
		truncate table sap_checkoff
	</update>
	<insert id="saveCheckoff">
		insert into sap_checkoff(userId,groupName,checkinType,checkinDate,checkinDay,exceptionType,
			checkinTime,locationTitle,locationDetail,wifiName,wifiMac) 
		values (#{userId},#{groupName},#{checkinType},#{checkinDate},#{checkinDay},#{exceptionType},
			#{checkinTime},#{locationTitle},#{locationDetail},#{wifiName},#{wifiMac})
	</insert>
	<update id="deleteCheckoutList">
		truncate table sap_checkout
	</update>
	<insert id="saveCheckout">
		insert into sap_checkout(userId,groupName,checkinType,checkinDate,checkinDay,exceptionType,
			checkinTime,locationTitle,locationDetail,wifiName,wifiMac) 
		values (#{userId},#{groupName},#{checkinType},#{checkinDate},#{checkinDay},#{exceptionType},
			#{checkinTime},#{locationTitle},#{locationDetail},#{wifiName},#{wifiMac})
	</insert>
	<update id="deleteCheckoutStartList">
		truncate table sap_checkout_start
	</update>
	<insert id="saveCheckoutStart1">
		insert into sap_checkout_start
		SELECT a.* from sap_checkout a, (SELECT userId, checkinDay from sap_checkout GROUP BY userId, checkinDay having count(1) = 1) b
		where a.userId = b.userId and a.checkinDay = b.checkinDay 
	</insert>
	<insert id="saveCheckoutStart2">
		insert into sap_checkout_start
		SELECT a.* from sap_checkout a, (SELECT userId, checkinDay,min(checkinDate) as checkinDate from sap_checkout
			GROUP BY userId, checkinDay having count(1) > 1) b
		where a.userId = b.userId and a.checkinDay = b.checkinDay and a.checkinDate = b.checkinDate 
		ORDER BY a.userId, a.checkinDay, a.checkinDate;
	</insert>
	<update id="deleteCheckoutEndList">
		truncate table sap_checkout_end
	</update>
	<insert id="saveCheckoutEnd">
		insert into sap_checkout_end
		SELECT a.* from sap_checkout a, (SELECT userId, checkinDay,max(checkinDate) as checkinDate from sap_checkout 
			GROUP BY userId, checkinDay having count(1) > 1) b
		where a.userId = b.userId and a.checkinDay = b.checkinDay and a.checkinDate = b.checkinDate 
		ORDER BY a.userId, a.checkinDay, a.checkinDate;
	</insert>
	<update id="deleteCheckReport">
		delete from sap_check_report where checkinDay >= #{startDay} and checkinDay &lt;= #{endDay}
	</update>
	<insert id="saveCheckReport">
		insert into sap_check_report(userId,groupName,workTime,offWorkTime,name,checkinDay,checkinDate,checkinException,checkoffDate,checkoffException,
			checkoutDate1,checkoutDate2,leaveCreatedTime,leaveStartDate,leaveEndDate,leaveDay,leaveComment,bukaDay,result)
		select a.userId,c.groupName,'9:30','18:30',a.`name`,b.checkinDay, c.checkinDate as checkinDate,c.exceptionType as checkinException,
			d.checkinDate as checkoffDate,d.exceptionType as checkoffException,e.checkinDate as checkoutDate1,g.checkinDate as checkoutDate2,
			f.createdDateTime as leaveCreatedTime, f.startDate as leaveStartDate,
			f.endDate as leaveEndDate,f.deductionQuantity as leaveDay,f.`comment` as leaveComment, m.`day` as bukaDay, '正常' as result
		from wx_user a join sap_checkdate b on a.delFlag=0 
			and b.checkinDay >= #{startDay} and b.checkinDay &lt;= #{endDay}
		LEFT JOIN sap_checkin c on a.userId = c.userId and b.checkinDay = c.checkinDay
		LEFT JOIN sap_checkoff d on a.userId = d.userId and b.checkinDay = d.checkinDay
		LEFT JOIN sap_checkout_start e on a.userId = e.userId and b.checkinDay = e.checkinDay
		LEFT JOIN sap_checkout_end g on a.userId = g.userId and b.checkinDay = g.checkinDay
		LEFT JOIN sap_employee_time f on a.userId = f.userId and b.checkinDay >= f.startDate and b.checkinDay &lt;= f.endDate 
			and f.approvalStatus = 'APPROVED' and f.delFlag=0
		LEFT JOIN sap_approval_info m on a.userId = m.userId and b.checkinDay = m.`day`
		where EXISTS (SELECT 1 from sap_checkin_option o where a.userId = o.userId)
		order by a.userId ASC, b.checkinDay asc;
	</insert>
	
	<update id="updateHrsdWorkTime">
		update sap_check_report set workTime = '10:00' where groupName like '%华人时代%' 
			and checkinDay >= #{startDay} and checkinDay &lt;= #{endDay}
	</update>
	
	<update id="updateCheckinNullException">
		update sap_check_report set checkinException = '后台更新未打卡' where checkinDate is null
			and checkinDay >= #{startDay} and checkinDay &lt;= #{endDay}
	</update>
	
	<update id="updateCheckoffNullException">
		update sap_check_report set checkoffException = '后台更新未打卡' where checkoffDate is null
			and checkinDay >= #{startDay} and checkinDay &lt;= #{endDay}
	</update>

	<update id="updateAllExceptionResult">
		update sap_check_report set result = '异常' 
		where (LENGTH(checkinException) > 0 or LENGTH(checkoffException) > 0) and result = '正常' 
			and checkinDay >= #{startDay} and checkinDay &lt;= #{endDay}
	</update>

	<update id="updateResultOk">
		update sap_check_report set result = '正常' where id = #{id}
	</update>
	
	<select id="selectLeaveDayList" resultType="SapCheckReport">
		SELECT * from sap_check_report where result = '异常' and leaveDay is not null 
			and checkinDay >= #{startDay} and checkinDay &lt;= #{endDay}
	</select>
	
	<select id="selectExceptionList" resultType="SapCheckReport">
		SELECT * from sap_check_report where result = '异常' 
			and checkinDay >= #{startDay} and checkinDay &lt;= #{endDay}
	</select>
	
	<select id="selectRepeatCheckin" resultType="SapCheck">
		SELECT a.* from sap_checkin a, (SELECT userId, checkinDay from sap_checkin GROUP BY userId, checkinDay having count(1) > 1) b
		where a.userId = b.userId and a.checkinDay = b.checkinDay order by a.exceptionType, a.checkinDate;
	</select>
	
	<select id="selectRepeatCheckoff" resultType="SapCheck">
		SELECT a.* from sap_checkoff a, (SELECT userId, checkinDay from sap_checkoff GROUP BY userId, checkinDay having count(1) > 1) b
		where a.userId = b.userId and a.checkinDay = b.checkinDay order by a.exceptionType, a.checkinDate;
	</select>
	
	<select id="selectRepeatCheckoutStart" resultType="SapCheck">
		SELECT a.* from sap_checkout_start a, (SELECT userId, checkinDay from sap_checkout_start GROUP BY userId, checkinDay having count(1) > 1) b
		where a.userId = b.userId and a.checkinDay = b.checkinDay order by a.exceptionType, a.checkinDate;
	</select>
		
	<select id="selectRepeatCheckoutEnd" resultType="SapCheck">
		SELECT a.* from sap_checkout_end a, (SELECT userId, checkinDay from sap_checkout_end GROUP BY userId, checkinDay having count(1) > 1) b
		where a.userId = b.userId and a.checkinDay = b.checkinDay order by a.exceptionType, a.checkinDate;
	</select>
	
	<update id="deleteCheckinById">
		delete from sap_checkin where id = #{id}
	</update>
	
	<update id="deleteCheckoffById">
		delete from sap_checkoff where id = #{id}
	</update>
	
	<update id="deleteCheckoutStartById">
		delete from sap_checkout_start where id = #{id}
	</update>
	
	<update id="deleteCheckoutEndById">
		delete from sap_checkout_end where id = #{id}
	</update>
	
	<select id="queryCheckInTotal" resultType="Long">
		SELECT count(1) from sap_checkin
	</select>
</mapper>