<?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="SapEmployeeTimeMapper">
	<select id="selectById" resultType="SapEmployeeTime">
		select * from sap_employee_time where externalCode=#{externalCode}
	</select>

	<insert id="saveOne">
		insert into sap_employee_time(externalCode,lastModifiedDateTime,endDate,endDateTime,loaActualReturnDate,
			createdDateTime,timeType,loaExpectedReturnDate,flexibleRequesting,
			timeRecordOrigin,mdfSystemRecordStatus,custPeriod,quantityInHours,
			loaStartJobInfoId,startDate,startDateTime,displayQuantity,quantityInDays,
			startTime,loaEndJobInfoId,approvalStatus,cancellationWorkflowRequestId,
			deductionQuantity,editable,lastModifiedBy,workflowRequestId,
			undeterminedEndDate,userId,recurrenceGroup,createdBy,
			workflowInitiatedByAdmin,comment,fractionQuantity,endTime,
			originalQuantityInDays,delFlag
		) 
		values (#{externalCode},#{lastModifiedDateTime},#{endDate},#{endDateTime},#{loaActualReturnDate},
			#{createdDateTime},#{timeType},#{loaExpectedReturnDate},#{flexibleRequesting},
			#{timeRecordOrigin},#{mdfSystemRecordStatus},#{custPeriod},#{quantityInHours},
			#{loaStartJobInfoId},#{startDate},#{startDateTime},#{displayQuantity},#{quantityInDays},
			#{startTime},#{loaEndJobInfoId},#{approvalStatus},#{cancellationWorkflowRequestId},
			#{deductionQuantity},#{editable},#{lastModifiedBy},#{workflowRequestId},
			#{undeterminedEndDate},#{userId},#{recurrenceGroup},#{createdBy},
			#{workflowInitiatedByAdmin},#{comment},#{fractionQuantity},#{endTime},
			#{originalQuantityInDays},'0'
		)
	</insert>
	
	<update id="updateUnApprovedDelete">
		update sap_employee_time set delFlag=1 where approvalStatus != 'APPROVED'
	</update>
	
	<select id="selectRepeat" resultType="SapEmployeeTime">
		SELECT a.* from sap_employee_time a, (SELECT userId, startDate 
			from sap_employee_time where delFlag=0 GROUP BY userId, startDate having count(1) > 1) b
		where a.delFlag=0 and a.userId = b.userId and a.startDate = b.startDate 
		order by a.userId,a.startDate
	</select>
	
	<update id="updateDeleteByCode">
		update sap_employee_time set delFlag=1 where externalCode=#{externalCode}
	</update>
</mapper>
