<?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.madeu.crm.kiosk.map.KioskMAP">

    <select id="getTreatmentOptionList" parameterType="hashmap" resultType="hashmap">
        /** KioskSql.getTreatmentOptionList **/
        SELECT MT.MU_TREATMENT_ID             AS "muTreatmentId"
			  ,MT.TREATMENT_NAME              AS "treatmentName"
		  FROM MU_TREATMENT AS MT
		 WHERE MT.USE_YN     = 'Y'
		   AND MT.TREATMENT_NAME  != '멤버십'
	  	ORDER BY MT.ORDER_NUMBER ASC
    </select>
    
	<select id="getCategoryList" parameterType="hashmap" resultType="hashmap">
        /** KioskSql.getCategoryList **/
        SELECT
			CATEGORY_CODE		AS "categoryCode"
		   ,CATEGORY_NAME		AS "categoryName"
		FROM MU_CATEGORY
		WHERE USE_YN        = 'Y'
		GROUP BY CATEGORY_CODE
		ORDER BY CATEGORY_CODE ASC
    </select>
    
    <select id="getCategoryItem" parameterType="hashmap" resultType="hashmap">
        /** KioskSql.getCategoryItem **/
        SELECT
			 MCI.CATEGORY_CODE					AS "categoryCode"
			,MC.CATEGORY_NAME					AS "categoryName"
			,MCI.CATEGORY_ITEM_CODE  			AS "categoryItemCode"
			,MCI.CATEGORY_ITEM_NAME				AS "categoryItemName"
			,MCI.FILE_PATH						AS "filePath"
			,MCI.FILE_NAME						AS "fileName"
			,MCI.ORIGINAL_FILE_NAME				AS "originalFileName"
			,MCI.ETC							AS "etc"
			,MCI.ORDER_NUMBER					AS "orderNumber"
		FROM
		MU_CATEGORY MC,MU_CATEGORY_ITEM MCI
		WHERE MC.CATEGORY_CODE = MCI.CATEGORY_CODE
		AND MCI.USE_YN        = 'Y'
		AND MC.USE_YN        = 'Y'
		AND MC.CATEGORY_CODE = #{categoryCode}
		ORDER BY ORDER_NUMBER
    </select>
    
    <select id="getUserOptionListCnt" resultType="Integer">
        /** KioskSql.getUserOptionListCnt **/
        SELECT COUNT(*)                         AS "totalCount"
	          FROM MU_USER AS MU
	     LEFT JOIN MU_CATEGORY_ITEM AS MCIN
	            ON MCIN.MU_CATEGORY_ITEM_ID = MU.NATIONALITY
	     LEFT JOIN MU_CATEGORY_ITEM AS MCIT
	            ON MCIT.MU_CATEGORY_ITEM_ID = MU.USER_TYPE
	         WHERE MU.USE_YN = 'Y'
	        <if test="userType != null and userType != ''">
	           AND MCIT.CATEGORY_ITEM_CODE = #{userType}
	           AND MCIT.CATEGORY_CODE = #{userTypeCode}
	        </if>
	        <if test="nationality != null and nationality != ''">
	           AND MCIN.CATEGORY_ITEM_CODE = #{nationality}
	           AND MCIN.CATEGORY_CODE = #{nationalityCode}
	        </if>
	        <if test="membershipYn != null and membershipYn != ''">
	           AND MU.MEMBERSHIP_YN = #{membershipYn}
	        </if>
	        <if test="userSearchKeywordParam != null and userSearchKeywordParam != ''">
	           AND (MU.USER_NAME LIKE CONCAT('%',TRIM(#{userSearchKeywordParam}),'%')
	                OR MU.PHONE_NUMBER LIKE CONCAT('%',TRIM(#{userSearchKeywordParam}),'%')
	                OR DATE_FORMAT(MU.BIRTHDAY,'%Y%m%d') LIKE CONCAT('%',TRIM(#{userSearchKeywordParam}),'%'))
	        </if>
    </select>
    
    <select id="getUserOptionList" parameterType="hashmap" resultType="hashmap">
        /** KioskSql.getUserOptionList **/
        SELECT ROW_NUMBER() OVER (ORDER BY MU.REG_DATE DESC)            AS "rowNum"
              ,MU.MU_USER_ID                                            AS "muUserId"
              ,MU.USER_NAME                                             AS "userName"
              ,MCIN.CATEGORY_ITEM_NAME                                  AS "nationality"
              ,DATE_FORMAT(MU.BIRTHDAY, '%Y-%m-%d')                     AS "birthday"
              ,TIMESTAMPDIFF(YEAR, MU.BIRTHDAY, CURDATE()) - (DATE_FORMAT(CURDATE(), '%m%d') <![CDATA[ < ]]> DATE_FORMAT(MU.BIRTHDAY, '%m%d'))
                                                                        AS "age"
              ,CASE MU.GENDER
                   WHEN 'M' THEN '남'
                   WHEN 'F' THEN '여'
                   ELSE ''
               END                                                      AS "gender"
              ,IFNULL(MCIT.FILE_PATH,MU.USER_TYPE2)                     AS "userType"
              ,MU.USER_TYPE2                                            AS "userType2"
              ,MU.PHONE_NUMBER                                          AS "phoneNumber"
              ,CASE MU.MEMBERSHIP_YN
                   WHEN 'Y' THEN '가입'
                   WHEN 'N' THEN '미가입'
               END                                                      AS "membershipYn"
              ,MU.ETC                                                   AS "etc"
              ,MU.MEMO                                                  AS "memo"
              ,IFNULL(DATE_FORMAT(MU.LAST_VISIT_DATE, '%Y-%m-%d'), '')  AS "lastVisitDate"
              ,CASE IFNULL(APP_USE_YN,'N')
                      WHEN 'Y' THEN
                                    CASE APP_STATUS
                                        WHEN 'C' THEN '가입'
                                        WHEN 'D' THEN '탈퇴'
                                        ELSE '미가입'
                                    END
                      WHEN 'N' THEN '미가입'
                END                                                      AS "appUseYn"
        FROM MU_USER AS MU
        LEFT JOIN MU_CATEGORY_ITEM AS MCIN
    ON MCIN.MU_CATEGORY_ITEM_ID = MU.NATIONALITY
        LEFT JOIN MU_CATEGORY_ITEM AS MCIT
    ON MCIT.MU_CATEGORY_ITEM_ID = MU.USER_TYPE
        WHERE MU.USE_YN = 'Y'
        <if test="userType != null and userType != ''">
        AND MCIT.CATEGORY_ITEM_CODE = #{userType}
        AND MCIT.CATEGORY_CODE = #{userTypeCode}
        </if>
        <if test="nationality != null and nationality != ''">
        AND MCIN.CATEGORY_ITEM_CODE = #{nationality}
        AND MCIN.CATEGORY_CODE = #{nationalityCode}
        </if>
        <if test="membershipYn != null and membershipYn != ''">
        AND MU.MEMBERSHIP_YN = #{membershipYn}
        </if>
        <if test="appUseYn != null and appUseYn != ''">
            AND (
            (#{appUseYn} = 'Y' AND MU.APP_STATUS = 'C' AND MU.APP_USE_YN = 'Y') OR
            (#{appUseYn} = 'N' AND MU.APP_USE_YN = 'N') OR
            (#{appUseYn} = 'D' AND MU.APP_USE_YN = 'Y' AND MU.APP_STATUS = 'D')
            )
        </if>
        <if test="userSearchKeywordParam != null and userSearchKeywordParam != ''">
        AND (MU.USER_NAME LIKE CONCAT('%',TRIM(#{userSearchKeywordParam}),'%')
         OR MU.PHONE_NUMBER LIKE CONCAT('%',TRIM(#{userSearchKeywordParam}),'%')
         OR DATE_FORMAT(MU.BIRTHDAY,'%Y%m%d') LIKE CONCAT('%',TRIM(#{userSearchKeywordParam}),'%'))
        </if>
        <choose>
        <when test="userSort != null and userSort != ''">
    ORDER BY ${userSort}
       </when>
       <otherwise>
    ORDER BY MU.REG_DATE DESC
       </otherwise>
       </choose>
        <if test="userStart != null and userStart != ''">
            LIMIT ${userStart}
            <if test="userLimit != null and userLimit != ''">
            ,${userLimit}
            </if>
        </if>
    </select>
    <insert id="insertUser" parameterType="com.madeu.crm.kiosk.dto.UserDTO">
	    /** KioskMAP.insertUser **/
	    <selectKey resultType="HashMap" keyProperty="userNumber" order="BEFORE">
	        SELECT USER.id AS id
	              ,USER.NUMBER AS number
	              ,MCIN.MU_CATEGORY_ITEM_ID AS nationality
	              ,MCIT.MU_CATEGORY_ITEM_ID AS userType
	              ,MCIC.MU_CATEGORY_ITEM_ID AS channel
	          FROM (SELECT CONCAT('U', DATE_FORMAT(NOW(), '%Y%m%d'), '-', LPAD(IFNULL(MAX(SUBSTRING(USER_NUMBER, 11)) + 1, 1), 3, '0')) AS NUMBER
	                      ,CONCAT(#{muUserId}, LPAD((SELECT NEXTVAL(MU_USER_SEQ)), 11, 0)) AS id
	                  FROM MU_USER AS MU
	                 WHERE USER_NUMBER LIKE CONCAT('U', DATE_FORMAT(NOW(), '%Y%m%d'), '%')
	          ) AS USER
	     LEFT JOIN MU_CATEGORY_ITEM AS MCIN
	            ON MCIN.CATEGORY_CODE = #{nationalityCode}
	           AND MCIN.CATEGORY_ITEM_CODE = #{nationality}
	     LEFT JOIN MU_CATEGORY_ITEM AS MCIT
	            ON MCIT.CATEGORY_CODE = #{userTypeCode}
	           AND MCIT.CATEGORY_ITEM_CODE = #{userType}
	     LEFT JOIN MU_CATEGORY_ITEM AS MCIC
	            ON MCIC.CATEGORY_CODE = #{channelCode}
	           AND MCIC.CATEGORY_ITEM_CODE = #{channel}
	    </selectKey>
	
	    INSERT INTO MU_USER (
	        MU_USER_ID, USER_NUMBER, USER_NAME, NATIONALITY, BIRTHDAY, GENDER,
	        USER_RRN1, USER_RRN2, USER_PNO, USER_TYPE, PHONE_NUMBER,
	        CHANNEL, INTRO_USER_ID, ETC, SMS_YN, REFUSE_PHOTO_YN,
	        WRITE_DATE, WRITE_TIME, CUD_FLAG, USE_YN, REG_ID, REG_DATE, MOD_ID, MOD_DATE,
	        ADDRESS, ADDRESS_DETAILS, EMAIL, INTRO_NAME
	    ) VALUES (
	         #{userNumber.id}
	        ,#{userNumber.number}
	        ,#{userName}
	        ,#{userNumber.nationality}
	        ,#{birthday}
	        ,#{gender}
	        ,#{userRrn1}
	        ,#{userRrn2}
	        ,#{userPno}
	        ,#{userNumber.userType}
	        ,#{phoneNumber}
	        ,#{userNumber.channel}
	        ,#{introUserId}
	        ,#{etc}
	        ,#{smsYn}
	        ,#{refusePhotoYn}
	        ,NOW()
	        ,NOW()
	        ,'C'
	        ,'Y'
	        ,#{regId}
	        ,NOW()
	        ,#{modId}
	        ,NOW()
	        ,#{address}
	        ,#{addressDtl}
	        ,#{email}
	        ,#{introName}
	    )
	</insert>
    <insert id="putUser" parameterType="hashmap">
    	/** KioskSql.insertUser **/
        <selectKey resultType="HashMap" keyProperty="userNumber" order="BEFORE">
            SELECT USER.id AS id
                  ,USER.NUMBER AS number
                  ,MCIN.MU_CATEGORY_ITEM_ID AS nationality
                  ,MCIT.MU_CATEGORY_ITEM_ID AS userType
                  ,MCIC.MU_CATEGORY_ITEM_ID AS channel
              FROM (SELECT CONCAT('U', DATE_FORMAT(NOW(), '%Y%m%d'), '-', LPAD(IFNULL(MAX(SUBSTRING(USER_NUMBER, 11)) + 1, 1), 3, '0')) AS NUMBER
                          ,CONCAT(#{muUserId},LPAD((SELECT NEXTVAL(MU_USER_SEQ)), 11, 0)) AS id
                      FROM MU_USER AS MU
                     WHERE USER_NUMBER LIKE CONCAT('U', DATE_FORMAT(NOW(), '%Y%m%d'), '%')
              ) AS USER
         LEFT JOIN MU_CATEGORY_ITEM AS MCIN
                ON MCIN.CATEGORY_CODE = #{nationalityCode}
               AND MCIN.CATEGORY_ITEM_CODE = #{nationality}
         LEFT JOIN MU_CATEGORY_ITEM AS MCIT
                ON MCIT.CATEGORY_CODE = #{userTypeCode}
               AND MCIT.CATEGORY_ITEM_CODE = #{userType}
         LEFT JOIN MU_CATEGORY_ITEM AS MCIC
                ON MCIC.CATEGORY_CODE = #{channelCode}
               AND MCIC.CATEGORY_ITEM_CODE = #{channel}
        </selectKey>
        INSERT INTO MU_USER (
            MU_USER_ID
            ,USER_NUMBER
            ,USER_NAME
            ,NATIONALITY
            ,BIRTHDAY
            ,GENDER
            ,USER_RRN1
            ,USER_RRN2
            ,USER_PNO
            ,USER_ARC1
            ,USER_ARC2
            ,USER_TYPE
            ,PHONE_NUMBER
            ,PHONE_NUMBER2
            ,MU_GROUP_ID
            ,CHANNEL
            ,INTRO_USER_ID
            ,INTRO_MEMBER_ID
            ,ETC
            ,MEMO
            ,SMS_YN
            ,REFUSE_PHOTO_YN
            ,WRITE_DATE
            ,WRITE_TIME
            ,REMARK
            ,CUD_FLAG
            ,USE_YN
            ,REG_ID
            ,REG_DATE
            ,MOD_ID
            ,MOD_DATE
        ) VALUES (
             #{userNumber.id}
            ,#{userNumber.number}
            ,#{userName}
            ,#{userNumber.nationality}
            ,#{birthday}
            ,#{gender}
            ,#{userRrn1}
            ,#{userRrn2}
            ,#{userPno}
            ,#{userArc1}
            ,#{userArc2}
            ,#{userNumber.userType}
            ,#{phoneNumber}
            ,#{phoneNumber2}
            ,#{muGroupId}
            ,#{userNumber.channel}
            ,#{introUserId}
            ,#{introMemberId}
            ,#{etc}
            ,#{memo}
            ,#{smsYn}
            ,#{refusePhotoYn}
            ,NOW()
            ,NOW()
            ,#{remark}
            ,'C'
            ,'Y'
            ,#{modId}
            ,NOW()
            ,#{regId}
            ,NOW()
        )
    </insert>
    <select id="getUser" parameterType="hashmap" resultType="hashmap">
        /** KioskSql.getUser **/
        SELECT MU.MU_USER_ID                               AS "muUserId"
              ,MU.USER_NUMBER                              AS "userNumber"
              ,MU.USER_NAME                                AS "userName"
              ,MCIN.CATEGORY_ITEM_CODE                     AS "nationality"
              ,MCIN.CATEGORY_ITEM_NAME                     AS "nationalityName"
              ,DATE_FORMAT(MU.BIRTHDAY, '%Y-%m-%d')        AS "birthday"
              ,MU.USER_RRN1                                AS "userRrn1"
              ,MU.USER_RRN2                                AS "userRrn2"
              ,MU.GENDER                                   AS "gender"
              ,CONCAT(
                    '만 '
                    ,TIMESTAMPDIFF(YEAR,  DATE_FORMAT(MU.BIRTHDAY, '%Y-%m-%d'), CURDATE())
                    ,'세'
               )                                           AS "identity"
              ,MU.USER_PNO                                  AS "userPno"
              ,MU.USER_ARC1                                 AS "userArc1"
              ,MU.USER_ARC2                                 AS "userArc2"
              ,MCIT.CATEGORY_ITEM_CODE                      AS "userType"
              ,MCIT.FILE_PATH                               AS "filePath"
              ,MU.USER_TYPE2                                AS "userType2"
              ,MU.PHONE_NUMBER                              AS "phoneNumber"
              ,MU.PHONE_NUMBER2                             AS "phoneNumber2"
              ,MU.EMAIL                                     AS "email"
              ,MU.ZIP_CODE                                  AS "zipCode"
              ,MU.ADDRESS                                   AS "address"
              ,MU.ADDRESS_DETAILS                           AS "addressDetails"
              ,CASE MU.MEMBERSHIP_YN
               WHEN 'Y' THEN '가입'
               WHEN 'N' THEN '미가입'
                END                                         AS "membershipYn"
              ,MU.MEMBERSHIP_ACCOUNT                        AS "membershipAccount"
              ,MU.MU_GROUP_ID                               AS "muGroupId"
              ,MCIC.CATEGORY_ITEM_CODE                      AS "channel"
              ,MCIC.CATEGORY_ITEM_NAME                      AS "channelName"
              ,MU.INTRO_USER_ID                             AS "introUserId"
              ,MU.INTRO_MEMBER_ID                           AS "introMemberId"
              ,MU.ETC                                       AS "etc"
              ,MU.MEMO                                      AS "memo"
              ,MU.EMAIL_YN                                  AS "emailYn"
              ,MU.SMS_YN                                    AS "smsYn"
              ,MU.REFUSE_PHOTO_YN                           AS "refusePhotoYn"
              ,CASE IFNULL(APP_USE_YN,'N')
               WHEN 'Y'
               THEN CASE APP_STATUS
                    WHEN 'C' THEN '가입'
                    WHEN 'D' THEN '탈퇴'
                    ELSE '미가입'
                     END
               WHEN 'N'
               THEN '미가입'
                END                                         AS "appUseYn"
              ,DATE_FORMAT(MU.APP_INTRO_DATE, '%Y-%m-%d')   AS "appIntroDate"
              ,DATE_FORMAT(MU.LEAVE_DATE, '%Y-%m-%d')       AS "leaveDate"
          FROM MU_USER AS MU
     LEFT JOIN MU_CATEGORY_ITEM AS MCIN
            ON MCIN.MU_CATEGORY_ITEM_ID = MU.NATIONALITY
     LEFT JOIN MU_CATEGORY_ITEM AS MCIT
            ON MCIT.MU_CATEGORY_ITEM_ID = MU.USER_TYPE
     LEFT JOIN MU_CATEGORY_ITEM AS MCIC
            ON MCIC.MU_CATEGORY_ITEM_ID = MU.CHANNEL
         WHERE MU.USE_YN      = 'Y'
           AND MU.MU_USER_ID  = #{muUserId}
    </select>
    
    
    <select id="selectUserByPhoneNumber" parameterType="com.madeu.crm.kiosk.dto.KioskDTO" resultType="com.madeu.crm.kiosk.dto.KioskDTO">
	    SELECT 
	        MU_USER_ID,
	        USER_NUMBER,
	        USER_NAME,
	        BIRTHDAY,
	        GENDER,
	        PHONE_NUMBER,
	        USER_TYPE,
	        LAST_VISIT_DATE,
	        ETC,
	        MEMO
	    FROM MU_USER
	    WHERE USE_YN = 'Y'
	      AND PHONE_NUMBER = #{phoneNumber}
	    ORDER BY USER_NUMBER DESC
	    
	</select>
    
    
    <select id="getHospital" parameterType="hashmap" resultType="hashmap">
        /** KioskSql.getHospital **/
        SELECT MH.MU_HOSPITAL_ID                                   AS "muHospitalId"
			  ,MH.HOSPITAL_NAME                                    AS "hospitalName"
			  ,MH.MON_OPEN_YN                                      AS "monOpenYn"
			  ,DATE_FORMAT(MH.MON_OPEN_START_TIME , '%H:%i')       AS "monOpenStartTime"
			  ,DATE_FORMAT(MH.MON_OPEN_END_TIME   , '%H:%i')       AS "monOpenEndTime"
			  ,DATE_FORMAT(MH.MON_BREAK_START_TIME, '%H:%i')       AS "monBreakStartTime"
			  ,DATE_FORMAT(MH.MON_BREAK_END_TIME  , '%H:%i')       AS "monBreakEndTime"
			  ,MH.TUE_OPEN_YN                                      AS "tueOpenYn"
			  ,DATE_FORMAT(MH.TUE_OPEN_START_TIME , '%H:%i')       AS "tueOpenStartTime"
			  ,DATE_FORMAT(MH.TUE_OPEN_END_TIME   , '%H:%i')       AS "tueOpenEndTime"
			  ,DATE_FORMAT(MH.TUE_BREAK_START_TIME, '%H:%i')       AS "tueBreakStartTime"
			  ,DATE_FORMAT(MH.TUE_BREAK_END_TIME  , '%H:%i')       AS "tueBreakEndTime"
			  ,MH.WED_OPEN_YN                                      AS "wedOpenYn"
			  ,DATE_FORMAT(MH.WED_OPEN_START_TIME , '%H:%i')       AS "wedOpenStartTime"
			  ,DATE_FORMAT(MH.WED_OPEN_END_TIME   , '%H:%i')       AS "wedOpenEndTime"
			  ,DATE_FORMAT(MH.WED_BREAK_START_TIME, '%H:%i')       AS "wedBreakStartTime"
			  ,DATE_FORMAT(MH.WED_BREAK_END_TIME  , '%H:%i')       AS "wedBreakEndTime"
			  ,MH.THU_OPEN_YN                                      AS "thuOpenYn"
			  ,DATE_FORMAT(MH.THU_OPEN_START_TIME , '%H:%i')       AS "thuOpenStartTime"
			  ,DATE_FORMAT(MH.THU_OPEN_END_TIME   , '%H:%i')       AS "thuOpenEndTime"
			  ,DATE_FORMAT(MH.THU_BREAK_START_TIME, '%H:%i')       AS "thuBreakStartTime"
			  ,DATE_FORMAT(MH.THU_BREAK_END_TIME  , '%H:%i')       AS "thuBreakEndTime"
			  ,MH.FRI_OPEN_YN                                      AS "friOpenYn"
			  ,DATE_FORMAT(MH.FRI_OPEN_START_TIME , '%H:%i')       AS "friOpenStartTime"
			  ,DATE_FORMAT(MH.FRI_OPEN_END_TIME   , '%H:%i')       AS "friOpenEndTime"
			  ,DATE_FORMAT(MH.FRI_BREAK_START_TIME, '%H:%i')       AS "friBreakStartTime"
			  ,DATE_FORMAT(MH.FRI_BREAK_END_TIME  , '%H:%i')       AS "friBreakEndTime"
			  ,MH.SAT_OPEN_YN                                      AS "satOpenYn"
			  ,DATE_FORMAT(MH.SAT_OPEN_START_TIME , '%H:%i')       AS "satOpenStartTime"
			  ,DATE_FORMAT(MH.SAT_OPEN_END_TIME   , '%H:%i')       AS "satOpenEndTime"
			  ,DATE_FORMAT(MH.SAT_BREAK_START_TIME, '%H:%i')       AS "satBreakStartTime"
			  ,DATE_FORMAT(MH.SAT_BREAK_END_TIME  , '%H:%i')       AS "satBreakEndTime"
			  ,MH.SUN_OPEN_YN                                      AS "sunOpenYn"
			  ,DATE_FORMAT(MH.SUN_OPEN_START_TIME , '%H:%i')       AS "sunOpenStartTime"
			  ,DATE_FORMAT(MH.SUN_OPEN_END_TIME   , '%H:%i')       AS "sunOpenEndTime"
			  ,DATE_FORMAT(MH.SUN_BREAK_START_TIME, '%H:%i')       AS "sunBreakStartTime"
			  ,DATE_FORMAT(MH.SUN_BREAK_END_TIME  , '%H:%i')       AS "sunBreakEndTime"
			  ,MH.PUBLIC_HOLIDAY_USE_YN                            AS "publicHolidayUseYn"
			  ,IFNULL((SELECT CONCAT(MM.NAME,'(',MM.LOGIN_ID,')')  AS MEMBER_NAME
						 FROM MU_MEMBER AS MM
						WHERE MM.USE_YN = 'Y'
						  AND MM.MU_MEMBER_ID = MH.MU_MEMBER_ID
						LIMIT 0, 1
			   ),'')                                               AS "memberName"
		  FROM MU_HOSPITAL AS MH
		 WHERE MH.USE_YN        = 'Y'
		 LIMIT 0, 1
    </select>
    
    <select id="getHospitalHolidayList" parameterType="hashmap" resultType="hashmap">
        /** KioskSql.getHospitalHolidayList **/
        SELECT MHH.MU_HOSPITAL_HOLIDAY_ID               AS "muHospitalHolidayId"
			  ,MHH.MU_HOSPITAL_ID                       AS "muHospitalId"
			  ,MHH.HOSPITAL_HOLIDAY_NAME                AS "hospitalHolidayName"
			  ,DATE_FORMAT(MHH.LOC_DATE, '%Y-%m-%d')    AS "locDate"
			  ,MHH.REPEAT_YN                            AS "repeatYn"
		  FROM MU_HOSPITAL_HOLIDAY AS MHH
		 WHERE MHH.USE_YN          = 'Y'
		   AND MHH.MU_HOSPITAL_ID  = #{muHospitalId}
    </select>
    
    <select id="getTotalTreatmentProcedureOptionCount" resultType="Integer">
    /** KioskSql.getTotalTreatmentProcedureOptionCount **/
		SELECT COUNT(*)                           AS "totalCount"
		  FROM MU_TREATMENT AS MT
	INNER JOIN MU_TREATMENT_PROCEDURE AS MTP
			ON MT.MU_TREATMENT_ID                 = MTP.MU_TREATMENT_ID
		   AND MT.USE_YN                          = 'Y'
		   AND MTP.USE_YN                         = 'Y'
	</select>

	<select id="getTreatmentProcedureOptionList" parameterType="hashMap" resultType="hashMap">
	/** KioskSql.getTreatmentProcedureOptionList **/
		SELECT MTP.*
		  FROM (
					SELECT MTP.*
						  ,CAST(@RNUM:=@RNUM + 1 AS CHAR) AS "rowNum"
					  FROM (
								SELECT MT.MU_TREATMENT_ID                 AS "muTreatmentId"
									  ,MT.TREATMENT_NAME                  AS "treatmentName"
									  ,MTP.MU_TREATMENT_PROCEDURE_ID      AS "muTreatmentProcedureId"
									  ,MTP.TREATMENT_PROCEDURE_NAME       AS "treatmentProcedureName"
									  ,MT.ORDER_NUMBER                    AS "treatmentOrderNumber"
								  FROM MU_TREATMENT AS MT
							INNER JOIN MU_TREATMENT_PROCEDURE AS MTP
									ON MT.MU_TREATMENT_ID                 = MTP.MU_TREATMENT_ID
								   AND MT.USE_YN                          = 'Y'
								   AND MTP.USE_YN                         = 'Y'
								   AND MTP.ENABLE_YN                      = 'Y'
								   <if test="treatmentProcedureSearchKeywordParam != null and treatmentProcedureSearchKeywordParam != ''">
								   AND MTP.TREATMENT_PROCEDURE_NAME LIKE CONCAT('%',TRIM(#{treatmentProcedureSearchKeywordParam}),'%')
								   </if>
								  WHERE TREATMENT_NAME != '멤버십'
								 <choose>
									 <when test="treatmentProcedureSort != null and treatmentProcedureSort != ''">
								  ORDER BY ${treatmentProcedureSort}
									 </when>
									 <otherwise>
								  ORDER BY MT.ORDER_NUMBER ASC, MTP.TREATMENT_PROCEDURE_NAME ASC
									 </otherwise>
								 </choose>
								 LIMIT 18446744073709551615
						   ) MTP, (SELECT @RNUM:=0) R
					 WHERE 1 = 1
			   ) MTP
		 WHERE 1 = 1
		 LIMIT ${treatmentProcedureStart}, ${treatmentProcedureLimit}
	</select>
	
	<select id="getMemberDoctorConsultingOptionList" parameterType="hashMap" resultType="hashMap">
	/** KioskSql.getMemberDoctorConsultingOptionList **/
		SELECT MM.MU_MEMBER_ID              AS "muMemberId"
			  ,MM.NAME                      AS "memberName"
			  ,MM.TYPE                      AS "type"
			  ,CASE MM.TYPE
					WHEN 'D'  THEN '닥터'
					WHEN 'A'  THEN '상담'
					WHEN 'E'  THEN '직원'
					ELSE ''
			   END                          AS "typeName"
		  FROM (
					<!-- 해당 시술 닥터 -->
					SELECT MM.MU_MEMBER_ID
						  ,MM.NAME
						  ,MM.TYPE
   						  ,MM.ORDER_NUMBER
					  FROM MU_MEMBER AS MM
				INNER JOIN MU_TREATMENT_PROCEDURE_DOCTOR AS MTPD
						ON MM.MU_MEMBER_ID = MTPD.MU_MEMBER_ID
					   AND MM.USE_YN                      = 'Y'
					   AND MM.STATUS                      = 'Y'
					   AND MM.TYPE                        = 'D'
					   AND MTPD.USE_YN                    = 'Y'
					   AND MTPD.MU_TREATMENT_PROCEDURE_ID IN(${muTreatmentProcedureIdList})
					 UNION
					 <!-- 상담자 -->
					SELECT MM.MU_MEMBER_ID
						  ,MM.NAME
						  ,MM.TYPE
						  ,MM.ORDER_NUMBER
					  FROM MU_MEMBER AS MM
					 WHERE MM.USE_YN   = 'Y'
					   AND MM.STATUS   = 'Y'
					   AND MM.TYPE     = 'A'
					   AND MM.MU_GROUP_ID IN (SELECT MU_GROUP_ID
                                                FROM MU_GROUP MG
                                               WHERE MG.USE_YN='Y'
                                                 AND MG.GROUP_NAME IN (SELECT TREATMENT_NAME
                                                                         FROM MU_TREATMENT AS MT
                                                                         JOIN MU_TREATMENT_PROCEDURE MTP
                                                                           ON MT.MU_TREATMENT_ID=MTP.MU_TREATMENT_ID
                                                                          AND MT.USE_YN                     = 'Y'
                                                                          AND MTP.MU_TREATMENT_PROCEDURE_ID IN (${muTreatmentProcedureIdList})))
			   ) AS MM
		 <!-- 휴무/연차 아닌 직원 -->
		   <if test="searchDate != null and searchDate != ''">
		 WHERE MM.MU_MEMBER_ID NOT IN (SELECT MA.MU_MEMBER_ID
										 FROM MU_ATTENDANCE AS MA
										WHERE MA.USE_YN           = 'Y'
										  AND MA.STATUS          != 'D'
										  AND MA.ATTENDANCE_TYPE IN ('C', 'D')
										  AND DATE_FORMAT(MA.START_DATE, '%Y-%m-%d') &lt;= #{searchDate}
										  AND DATE_FORMAT(MA.END_DATE, '%Y-%m-%d')   &gt;= #{searchDate}

								    UNION ALL

			                           SELECT MA.MU_MEMBER_ID
			                             FROM MU_ATTENDANCE AS MA
										WHERE MA.USE_YN           = 'Y'
										  AND MA.STATUS          != 'D'
										  AND MA.ATTENDANCE_TYPE IN ('E')
										  AND DATE_FORMAT(CONCAT(MA.START_DATE, ' ', MA.START_TIME), '%Y-%m-%d %H:%i') &lt;= CONCAT(#{searchDate}, ' ', #{searchTime})
										  AND DATE_FORMAT(CONCAT(MA.END_DATE, ' ', MA.END_TIME), '%Y-%m-%d %H:%i') &gt;= CONCAT(#{searchDate}, ' ', #{searchTime})
									  )
		   </if>
 	  ORDER BY MM.TYPE DESC, MM.ORDER_NUMBER ASC
	</select>
	
	<select id="getMemberListCnt" parameterType="hashMap" resultType="hashMap">
	/** KioskSql.getMemberListCnt **/
		SELECT COUNT(*)  AS "totalCount"
		  FROM MU_MEMBER AS MM
		 WHERE MM.USE_YN = 'Y'
		   <if test="memberSearchKeywordParam != null and memberSearchKeywordParam != ''">
		   AND (MM.NAME LIKE CONCAT('%',TRIM(#{memberSearchKeywordParam}),'%')
			OR MM.EMAIL LIKE CONCAT('%',TRIM(#{memberSearchKeywordParam}),'%')
			OR MM.PHONE_NUMBER LIKE CONCAT('%',TRIM(#{memberSearchKeywordParam}),'%'))
		   </if>
		   <if test="memberSearchGroupId != null and memberSearchGroupId != ''">
		   AND MM.MU_GROUP_ID  = #{memberSearchGroupId}
		   </if>
		   <if test="memberSearchDutyId != null and memberSearchDutyId != ''">
		   AND MM.MU_DUTY_ID = #{memberSearchDutyId}
		   </if>
		   <if test="memberSearchAuthId != null and memberSearchAuthId != ''">
		   AND MM.MU_AUTH_ID   = #{memberSearchAuthId}
		   </if>
	</select>
	
	<select id="getMemberList" parameterType="hashMap" resultType="hashMap">
	/** KioskSql.getMemberList **/
		SELECT MM.MU_MEMBER_ID                        AS "muMemberId"
              ,MM.ORDER_NUMBER						  AS "orderNumber"
			  ,MM.MEMBER_CODE                         AS "memberCode"
			  ,MM.LOGIN_ID                            AS "loginId"
			  ,MM.NAME                                AS "memberName"
			  ,MM.PHONE_NUMBER                        AS "phoneNumber"
			  ,MM.EMAIL                               AS "email"
			  ,CASE MM.STATUS
					WHEN 'Y'  THEN '활성'
					WHEN 'N'  THEN '비활성'
			   END                                    AS "status"
			  ,MM.MU_AUTH_ID                          AS "muAuthId"
			  ,IFNULL((SELECT MA.AUTH_NAME
						 FROM MU_AUTH AS MA
						WHERE MA.USE_YN      = 'Y'
						  AND MA.MU_AUTH_ID = MM.MU_AUTH_ID
						LIMIT 0, 1
			   ),'')                                  AS "authName"
			  ,MM.MU_GROUP_ID                         AS "muGroupId"
			  ,IFNULL((SELECT MG.GROUP_NAME
						 FROM MU_GROUP AS MG
						WHERE MG.USE_YN      = 'Y'
						  AND MG.MU_GROUP_ID = MM.MU_GROUP_ID
						LIMIT 0, 1
			   ),'')                                  AS "groupName"
			  ,MM.MU_DUTY_ID                          AS "muDutyId"
			  ,IFNULL((SELECT MD.DUTY_NAME
						 FROM MU_DUTY AS MD
						WHERE MD.USE_YN      = 'Y'
						  AND MD.MU_DUTY_ID = MM.MU_DUTY_ID
						LIMIT 0, 1
			   ),'')                                  AS "dutyName"
			   ,CASE MM.TYPE
                    WHEN 'D'  THEN '닥터'
                    WHEN 'A'  THEN '상담'
                    WHEN 'E'  THEN '직원'
                    ELSE ''
               END                                    AS "typeName"
			  ,DATE_FORMAT(MM.WRITE_DATE, '%Y-%m-%d') AS "writeDate"
		  FROM MU_MEMBER AS MM
		 WHERE MM.USE_YN = 'Y'
		   <if test="memberSearchKeywordParam != null and memberSearchKeywordParam != ''">
		   AND (MM.NAME LIKE CONCAT('%',TRIM(#{memberSearchKeywordParam}),'%')
			OR MM.EMAIL LIKE CONCAT('%',TRIM(#{memberSearchKeywordParam}),'%')
			OR MM.PHONE_NUMBER LIKE CONCAT('%',TRIM(#{memberSearchKeywordParam}),'%'))
		   </if>
		   <if test="memberSearchGroupId != null and memberSearchGroupId != ''">
		   AND MM.MU_GROUP_ID  = #{memberSearchGroupId}
		   </if>
		   <if test="memberSearchDutyId != null and memberSearchDutyId != ''">
		   AND MM.MU_DUTY_ID = #{memberSearchDutyId}
		   </if>
		   <if test="memberSearchAuthId != null and memberSearchAuthId != ''">
		   AND MM.MU_AUTH_ID   = #{memberSearchAuthId}
		   </if>
		 <choose>
		 <when test="memberSort != null and memberSort != ''">
	  ORDER BY ${memberSort}
		 </when>
		 <otherwise>
	  ORDER BY MM.ORDER_NUMBER
		 </otherwise>
		 </choose>
		<if test="memberStart != null and memberStart != ''">
			LIMIT ${memberStart}
			<if test="memberLimit != null and memberLimit != ''">
				,${memberLimit}
			</if>
		</if>
	</select>
	
	<select id="getReserveUserOptionList" parameterType="hashMap" resultType="hashMap">
	/** KioskSql.getReserveUserOptionList **/
		SELECT MR.MU_RESERVE_ID                                       AS "muReserveId"
			  ,DATE_FORMAT(MR.RESERVE_DATE,'%Y-%m-%d')                AS "reserveDate"
			  ,DATE_FORMAT(MR.RESERVE_TIME, '%H:%i')                  AS "reserveTime"
			  ,MR.MU_USER_ID                                          AS "muUserId"
			  ,MR.USER_NAME                                           AS "userName"
			  ,MR.TREATMENT_NAME                                      AS "treatmentName"
			  ,MR.MU_TREATMENT_ID                                     AS "muTreatmentId"
			  ,MR.TREATMENT_NAME                                      AS "treatmentName"
			  ,MR.MU_TREATMENT_PROCEDURE_ID                           AS "muTreatmentProcedureId"
			  ,MR.TREATMENT_PROCEDURE_NAME                            AS "treatmentProcedureName"
			  ,MR.RESERVE_MEMBER_ID                                   AS "reserveMemberId"
			  ,IFNULL((SELECT MM.NAME
						 FROM MU_MEMBER AS MM
						WHERE MM.MU_MEMBER_ID = MR.RESERVE_MEMBER_ID
						LIMIT 0, 1
			   ), '')                                                 AS "reserveMemberName"
			  ,MP.PAYMENT_AMOUNT                                      AS "totalAmount"
			  ,MP.PAYMENT_CATEGORY_ITEM_NAME                          AS "paymentCategoryItemName"
		  FROM MU_RESERVE MR
	INNER JOIN MU_PAYMENT_DETAIL MPD
            ON MR.MU_RESERVE_ID = MPD.MU_RESERVE_ID
           AND MR.USE_YN     = 'Y'
           AND MR.STATUS2    = 'Y'
           AND MR.MU_USER_ID = #{muUserId}
	INNER JOIN MU_PAYMENT MP
            ON MPD.MU_PAYMENT_ID = MP.MU_PAYMENT_ID
           AND MP.USE_YN     = 'Y'
		   AND MP.STATUS IN ('Y', 'C', 'R')
	  ORDER BY MR.RECEPTION_DATE DESC
	          ,RESERVE_TIME ASC
	</select>
	
	<select id="selectConsentForm" parameterType="com.madeu.crm.kiosk.dto.ConsentFormDTO" resultType="com.madeu.crm.kiosk.dto.ConsentFormDTO">
		/* KioskSql.selectConsentFormContent - 동의서 최신 버전 조회 */
        SELECT 
              CONSENT_FORM_TYPE_CD  AS "consentFormTypeCd"
            , NATIONAL_CD           AS "nationalCd"
            , CONSENT_FORM_VERSION  AS "consentFormVersion"
            , CONSENT_FORM_CONTENT  AS "consentFormContent"
            , USE_YN                AS "useYn"
            , REG_DT                AS "regDt"
            , MOD_DT                AS "modDt"
        FROM CONSENT_FORM
        WHERE CONSENT_FORM_TYPE_CD = #{consentFormTypeCd}
          AND NATIONAL_CD          = #{nationalCd}
          AND USE_YN               = 'Y'
        ORDER BY CONSENT_FORM_VERSION DESC
        LIMIT 1
	</select>
	
	<select id="selectReserveByMuUserId" parameterType="com.madeu.crm.kiosk.dto.KioskDTO" resultType="com.madeu.crm.kiosk.dto.ReserveDTO">
	   SELECT MU_RESERVE_ID
	         ,RESERVE_TIME
		 FROM MU_RESERVE
		WHERE MU_USER_ID = #{muUserId}       -- 찾으려는 고객 ID
		  AND RESERVE_DATE = CURDATE()       -- 오늘 날짜
		  AND USE_YN = 'Y'                   -- 사용 중인 데이터
		  AND STATUS = 'T'
	
	</select>
	
	<update id="updateReserveStatus" parameterType="com.madeu.crm.kiosk.dto.ReserveDTO">
	   UPDATE MU_RESERVE
	      SET STATUS = #{status}
		WHERE MU_RESERVE_ID = #{muReserveId}       -- 찾으려는 예약 ID
		  AND USE_YN = 'Y'                   -- 사용 중인 데이터
	      AND STATUS = 'T'
	</update>
	
	<update id="insertReserve" parameterType="com.madeu.crm.kiosk.dto.ReserveDTO">
	   <selectKey resultType="string" keyProperty="id" order="BEFORE">
			SELECT CONCAT(#{muReserveId},LPAD((SELECT NEXTVAL(MU_RESERVE_SEQ)), 11, 0))
		</selectKey>
	   INSERT INTO MU_RESERVE (
	        MU_RESERVE_ID,
	        USER_NAME,
	        MU_USER_ID,
	        RESERVE_DATE,
	        RESERVE_TIME,
	        STATUS,
	        WRITE_DATE,
	        WRITE_TIME,
	        CUD_FLAG,
	        USE_YN,
	        REG_ID,
	        MOD_ID
	    ) VALUES (
	        #{id},
	        #{userName},
	        #{muUserId},
	        CURDATE(),
	        CURTIME(),
	        #{status},
	        CURDATE(),
	        CURTIME(),
	        'C',
	        'Y',
	        #{regId},
	        #{regId}
	    )
	</update>
</mapper>