<?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="WebVacation">

	<select id="selectListVacationOption" parameterType="hashMap" resultType="hashMap">
		SELECT MV.MU_VACATION_ID              AS "muVacationId"
			  ,MV.VACATION_NAME               AS "vacationName"
			  ,MV.HOUR_TYPE                   AS "hourType"
		  FROM MU_VACATION AS MV
		 WHERE MV.USE_YN = 'Y'
	  ORDER BY MV.VACATION_NAME ASC
	</select>
	
	<select id="selectTotalVacationCount" parameterType="hashmap" resultType="hashmap">
		SELECT COUNT(*)  AS "totalCount"
		  FROM MU_VACATION AS MV
		 WHERE MV.USE_YN = 'Y'
	</select>
	
	<select id="selectListVacation" parameterType="hashmap" resultType="hashmap">
		SELECT MV.*
		  FROM (
					SELECT MV.*
						  ,CAST(@RNUM:=@RNUM + 1 AS CHAR) AS "rowNum"
					  FROM (
								SELECT MV.MU_VACATION_ID                          AS "muVacationId"
									  ,MV.VACATION_NAME                           AS "vacationName"
									  ,CASE HOUR_TYPE
											WHEN 'A' THEN '전일'
-- 											ELSE CONCAT(DATE_FORMAT(START_TIME , '%H:%i'), '시 ~',DATE_FORMAT(END_TIME, '%H:%i'), '시')
											ELSE CONCAT(HOUR, '시간')
									   END                                        AS "hourType"
									  ,IFNULL(MV.AVAILABLE_DAYS, '제한없음')         AS "availableDays"
									  ,IFNULL(MV.ETC, '')                         AS "etc"
									  ,DATE_FORMAT(MV.WRITE_DATE, '%Y-%m-%d')     AS "writeDate"
								  FROM MU_VACATION AS MV
								 WHERE MV.USE_YN = 'Y'
								 <choose>
								 <when test="vacationSort != null and vacationSort != ''">
							  ORDER BY ${vacationSort}
								 </when>
								 <otherwise>
							  ORDER BY MV.WRITE_DATE DESC
								 </otherwise>
								 </choose>
								 LIMIT 18446744073709551615
						   ) MV, (SELECT @RNUM:=0) R
					 WHERE 1 = 1
			   ) MV
		 WHERE 1 = 1
		 <!-- LIMIT ${vacationStart}, ${vacationLimit} -->
	</select>

	<select id="selectVacation" parameterType="hashmap" resultType="hashmap">
		SELECT MV.MU_VACATION_ID                          AS "muVacationId"
			  ,MV.VACATION_NAME                           AS "vacationName"
			  ,MV.HOUR_TYPE                               AS "hourType"
			  ,MV.HOUR                                    AS "hour"
			  ,MV.AVAILABLE_DAYS                          AS "availableDays"
			  ,IFNULL(MV.ETC, '')                         AS "etc"
			  ,DATE_FORMAT(MV.WRITE_DATE, '%Y-%m-%d') AS "writeDate"
		  FROM MU_VACATION AS MV
		 WHERE MV.USE_YN        = 'Y'
		   AND MV.MU_VACATION_ID  = #{muVacationId}
		 LIMIT 0, 1
	</select>

	<insert id="insertVacation" parameterType="hashmap">
		<selectKey resultType="string" keyProperty="id" order="BEFORE">
			SELECT CONCAT(#{muVacationId},LPAD((SELECT NEXTVAL(MU_VACATION_SEQ)), 11, 0))
		</selectKey>
		INSERT INTO MU_VACATION(
					 MU_VACATION_ID
					,VACATION_NAME
					,HOUR_TYPE
<!--				 <if test="startTime != null and startTime != ''">-->
<!--					,START_TIME-->
<!--				 </if>-->
<!--				<if test="endTime != null and endTime != ''">-->
<!--					,END_TIME-->
<!--				</if>-->
				 <if test="hour != null and hour != ''">
					 ,HOUR
				 </if>
				 <if test="availableDays != null and availableDays != ''">
					,AVAILABLE_DAYS
				 </if>
					,ETC
					,WRITE_DATE
					,WRITE_TIME
					,CUD_FLAG
					,USE_YN
					,REG_ID
					,REG_DATE
					,MOD_ID
					,MOD_DATE
					,T_ID
					,T_DATE
		)VALUES(
					 #{id}
					,#{vacationName}
					,#{hourType}
<!--				 <if test="startTime != null and startTime != ''">-->
<!--					,#{startTime}-->
<!--				 </if>-->
<!--				<if test="endTime != null and endTime != ''">-->
<!--					,#{endTime}-->
<!--				</if>-->
				<if test="hour != null and hour != ''">
					,#{hour}
				</if>
				 <if test="availableDays != null and availableDays != ''">
					,#{availableDays}
				 </if>
					,#{etc, jdbcType=VARCHAR}
					,CURDATE()
					,CURTIME()
					,'C'
					,'Y'
					,#{regId}
					,NOW()
					,#{modId}
					,NOW()
					,#{tId}
					,#{tDate}
		)
	</insert>
	
	<update id="updateVacation" parameterType="hashmap">
		UPDATE MU_VACATION
		   SET MOD_ID              = #{modId}
			  ,MOD_DATE            = NOW()
			  ,CUD_FLAG            = 'U'
			  ,VACATION_NAME       = #{vacationName}
			  ,HOUR_TYPE           = #{hourType}
		   <if test="hour != null and hour != ''">
			  ,HOUR                = #{hour}
		   </if>
		   <if test="availableDays != null and availableDays != ''">
			  ,AVAILABLE_DAYS      = #{availableDays}
		   </if>
			  ,ETC                 = #{etc, jdbcType=VARCHAR}
		 WHERE USE_YN          = 'Y'
		   AND MU_VACATION_ID  = #{muVacationId}
	</update>
	
	<update id="deleteVacation" parameterType="hashmap">
		UPDATE MU_VACATION
		   SET MOD_ID            = #{modId}
			  ,MOD_DATE	         = NOW()
			  ,CUD_FLAG          = 'D'
			  ,USE_YN            = 'N'
		 WHERE USE_YN      = 'Y'
		   AND MU_VACATION_ID  = #{muVacationId}
	</update>
</mapper>