<?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.smsTemplate.map.SmsTemplateMAP">

    <!-- 상용구 목록 건수 조회 -->
    <select id="getSmsTemplateCnt" parameterType="com.madeu.crm.smsTemplate.dto.SmsTemplateSearchDTO" resultType="Integer">
        /** SmsTemplateMAP.getSmsTemplateCnt **/
        SELECT COUNT(*) AS TOTAL_COUNT
          FROM MU_SMS_TEMPLATE
         WHERE USE_YN = 'Y'
           <if test="searchKeyword != null and searchKeyword != ''">
           AND (TITLE LIKE CONCAT('%', TRIM(#{searchKeyword}), '%')
                OR CONTENT LIKE CONCAT('%', TRIM(#{searchKeyword}), '%'))
           </if>
    </select>

    <!-- 상용구 목록 조회 -->
    <select id="getSmsTemplateList" parameterType="com.madeu.crm.smsTemplate.dto.SmsTemplateSearchDTO" resultType="com.madeu.crm.smsTemplate.dto.SmsTemplateDTO">
        /** SmsTemplateMAP.getSmsTemplateList **/
        SELECT ST.*
          FROM (
                SELECT ST.*
                      ,CAST(@RNUM:=@RNUM + 1 AS CHAR) AS "rowNum"
                  FROM (
                        SELECT ST.MU_SMS_TEMPLATE_ID              AS "muSmsTemplateId"
                              ,ST.TITLE                           AS "title"
                              ,ST.CONTENT                         AS "content"
                              ,DATE_FORMAT(ST.REG_DATE, '%Y-%m-%d %H:%i') AS "regDate"
                              ,DATE_FORMAT(ST.MOD_DATE, '%Y-%m-%d %H:%i') AS "modDate"
                              ,(SELECT MM.NAME
                                  FROM MU_MEMBER AS MM
                                 WHERE MM.USE_YN = 'Y'
                                   AND MM.MU_MEMBER_ID = ST.REG_ID
                                 LIMIT 0, 1) AS "regName"
                          FROM MU_SMS_TEMPLATE AS ST
                         WHERE ST.USE_YN = 'Y'
                           <if test="searchKeyword != null and searchKeyword != ''">
                           AND (ST.TITLE LIKE CONCAT('%', TRIM(#{searchKeyword}), '%')
                                OR ST.CONTENT LIKE CONCAT('%', TRIM(#{searchKeyword}), '%'))
                           </if>
                         <choose>
                         <when test="gridSort != null and gridSort != ''">
                    ORDER BY ${gridSort}
                         </when>
                         <otherwise>
                    ORDER BY ST.REG_DATE DESC
                         </otherwise>
                         </choose>
                         LIMIT 18446744073709551615
                  ) ST, (SELECT @RNUM:=0) R
                 WHERE 1 = 1
          ) ST
         WHERE 1 = 1
         <if test="gridLimitEnd != null and gridLimitEnd != ''">
         LIMIT ${gridLimitStart}, ${gridLimitEnd}
         </if>
    </select>

    <!-- 상용구 상세 조회 -->
    <select id="getSmsTemplate" parameterType="com.madeu.crm.smsTemplate.dto.SmsTemplateDTO" resultType="com.madeu.crm.smsTemplate.dto.SmsTemplateDTO">
        /** SmsTemplateMAP.getSmsTemplate **/
        SELECT ST.MU_SMS_TEMPLATE_ID              AS "muSmsTemplateId"
              ,ST.TITLE                           AS "title"
              ,ST.CONTENT                         AS "content"
              ,DATE_FORMAT(ST.REG_DATE, '%Y-%m-%d %H:%i') AS "regDate"
              ,DATE_FORMAT(ST.MOD_DATE, '%Y-%m-%d %H:%i') AS "modDate"
              ,(SELECT MM.NAME
                  FROM MU_MEMBER AS MM
                 WHERE MM.USE_YN = 'Y'
                   AND MM.MU_MEMBER_ID = ST.REG_ID
                 LIMIT 0, 1) AS "regName"
          FROM MU_SMS_TEMPLATE AS ST
         WHERE ST.USE_YN = 'Y'
           AND ST.MU_SMS_TEMPLATE_ID = #{muSmsTemplateId}
         LIMIT 0, 1
    </select>

    <!-- 상용구 등록 -->
    <insert id="putSmsTemplate" parameterType="com.madeu.crm.smsTemplate.dto.SmsTemplateDTO">
        <selectKey resultType="string" keyProperty="muSmsTemplateId" order="BEFORE">
            SELECT CONCAT('SMST', LPAD(IFNULL(MAX(CAST(SUBSTRING(MU_SMS_TEMPLATE_ID, 5) AS UNSIGNED)), 0) + 1, 11, '0'))
              FROM MU_SMS_TEMPLATE
        </selectKey>
        /** SmsTemplateMAP.putSmsTemplate **/
        INSERT INTO MU_SMS_TEMPLATE (
             MU_SMS_TEMPLATE_ID
            ,TITLE
            ,CONTENT
            ,USE_YN
            ,CUD_FLAG
            ,REG_ID
            ,REG_DATE
            ,MOD_ID
            ,MOD_DATE
        ) VALUES (
             #{muSmsTemplateId}
            ,#{title}
            ,#{content}
            ,'Y'
            ,'C'
            ,#{loginMemberId}
            ,NOW()
            ,#{loginMemberId}
            ,NOW()
        )
    </insert>

    <!-- 상용구 수정 -->
    <update id="modSmsTemplate" parameterType="com.madeu.crm.smsTemplate.dto.SmsTemplateDTO">
        /** SmsTemplateMAP.modSmsTemplate **/
        UPDATE MU_SMS_TEMPLATE
           SET TITLE    = #{title}
              ,CONTENT  = #{content}
              ,CUD_FLAG = 'U'
              ,MOD_ID   = #{loginMemberId}
              ,MOD_DATE = NOW()
         WHERE USE_YN = 'Y'
           AND MU_SMS_TEMPLATE_ID = #{muSmsTemplateId}
    </update>

    <!-- 상용구 삭제 (논리 삭제) -->
    <update id="delSmsTemplate" parameterType="com.madeu.crm.smsTemplate.dto.SmsTemplateDTO">
        /** SmsTemplateMAP.delSmsTemplate **/
        UPDATE MU_SMS_TEMPLATE
           SET USE_YN   = 'N'
              ,CUD_FLAG = 'D'
              ,MOD_ID   = #{loginMemberId}
              ,MOD_DATE = NOW()
         WHERE USE_YN = 'Y'
           AND MU_SMS_TEMPLATE_ID = #{muSmsTemplateId}
    </update>

</mapper>
