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

    <select id="getContentsBbsCnt" resultType="Integer">
        /** "ContentsBbsSql".getContentsBbsCnt **/
        SELECT COUNT(*) as TOTAL_COUNT
          FROM (SELECT HCB.CATEGORY_DIV_CD, HCB.CATEGORY_NO, HCB.POST_NO
		          FROM HP_CATEGORY HC, 
		               HP_CONTENTS_BBS HCB
		         WHERE HC.CATEGORY_DIV_CD = HCB.CATEGORY_DIV_CD
		           AND HC.CATEGORY_NO = HCB.CATEGORY_NO
		           AND HCB.USE_YN = 'Y'
		           AND HCB.CATEGORY_DIV_CD = #{categoryDivCd}
         		   <if test="@org.springframework.util.StringUtils@hasLength(categoryNm)">
			        AND HCB.CATEGORY_NM LIKE CONCAT('%', #{categoryNm}, '%')
			        </if>
			        <if test="@org.springframework.util.StringUtils@hasLength(title)">
			        AND HCB.TITLE LIKE CONCAT('%', #{title}, '%')
			        </if>
			        <if test="@org.springframework.util.StringUtils@hasLength(regNm)">
			        AND MM.NAME = #{regNm}
			        </if>
			    ) total	
    </select>

    <select id="getContentsBbsList" parameterType="hashmap" resultType="hashmap">
        /** ContentsBbsSql.getContentsBbsList **/
        SELECT HCB.*
          FROM (
                SELECT HCB.*
                      ,CAST(@RNUM:=@RNUM + 1 AS CHAR) as ROW_NUM
                  FROM (
                        SELECT HCB.POST_NO
                              ,HCB.TITLE
                              ,HCB.CONTENT
                              ,HC.CATEGORY_NM
                              ,HC.CATEGORY_NO
                              ,HCB.THUMBNAIL_BOTTOM_TXT
                              ,HCB.HASHTAG
                              ,DATE_FORMAT(HCB.REG_DATE, '%Y-%m-%d') as REG_DT 
                              ,MM.NAME as REG_NM
                          FROM HP_CATEGORY HC, 
                               HP_CONTENTS_BBS HCB, 
                               (SELECT * FROM MU_MEMBER WHERE USE_YN = 'Y') MM
                         WHERE HC.CATEGORY_DIV_CD = HCB.CATEGORY_DIV_CD
                           AND HCB.REG_ID = MM.MU_MEMBER_ID
                           AND HC.CATEGORY_NO = HCB.CATEGORY_NO
                           AND HCB.USE_YN = 'Y'
                           AND HC.USE_YN  = 'Y'
                           AND HCB.CATEGORY_DIV_CD = #{categoryDivCd}
                            <if test="@org.springframework.util.StringUtils@hasLength(categoryNm)">
							AND HCB.CATEGORY_NM LIKE CONCAT('%', #{categoryNm}, '%')
							</if>
							<if test="@org.springframework.util.StringUtils@hasLength(title)">
							AND HCB.TITLE LIKE CONCAT('%', #{title}, '%')
							</if>
							<if test="@org.springframework.util.StringUtils@hasLength(regNm)">
							AND MM.NAME = #{regNm}
							</if>
                           <choose>
                           <when test="@org.springframework.util.StringUtils@hasLength(webTreatmentPetitSort)">
                      ORDER BY ${webTreatmentPetitSort}
                           </when>
                           <otherwise>
                      ORDER BY CATEGORY_NO, POST_NO ASC
                           </otherwise>
                           </choose>
                         LIMIT 18446744073709551615
                ) HCB, (SELECT @RNUM:=0) R
                WHERE 1 = 1
        ) HCB
        WHERE 1 = 1
        LIMIT ${webTreatmentPetitStart}, ${webTreatmentPetitLimit}
    </select>

    <select id="getContentsBbs" parameterType="hashmap" resultType="hashmap">
        /** ContentsBbsSql.getContentsBbs **/
        SELECT HCB.POST_NO
        	 ,HCB.CATEGORY_NO
             ,HCB.TITLE
             ,HCB.CONTENT
             ,HCB.HASHTAG
             ,HCB.THUMBNAIL_BOTTOM_TXT
             ,HAF2.FILE_PATH as THUMBNAIL_IMG_PATH
             ,HAF.FILE_PATH as CONTENT_IMG_PATH
             ,HCB.OLD_CRM_ITEM_ID
             ,HCB.ORD_NO             
          FROM HP_CONTENTS_BBS AS HCB
          LEFT OUTER JOIN HP_ATTACH_FILE HAF ON HAF.ATTACHFILE_ID = HCB.CONTENTS_ATTACHFILE_ID
          LEFT OUTER JOIN HP_ATTACH_FILE HAF2 ON HAF2.ATTACHFILE_ID = HCB.THUMBNAIL_ATTACHFILE_ID
         WHERE HCB.USE_YN        = 'Y'
           AND HCB.CATEGORY_DIV_CD = #{categoryDivCd}
           AND HCB.CATEGORY_NO = #{categoryNo}
           AND HCB.POST_NO  = #{postNo}
         LIMIT 0, 1
    </select>
    
    <select id="getProcedureList" parameterType="hashmap" resultType="hashmap">
        /** ContentsBbsSql.getProcedureList **/
        SELECT HCBP.POST_NO
        	 ,HCBP.CATEGORY_NO
             ,HCBP.MU_TREATMENT_ID
             ,HCBP.MU_TREATMENT_PROCEDURE_ID
             ,MTP.TREATMENT_PROCEDURE_NAME   
             ,MTPP.PRICE
             ,IFNULL(MTPP.DISCOUNT_PRICE, 0) as DISCOUNT_PRICE
          FROM HP_CONTENTS_BBS_PROCEDURE AS HCBP
          LEFT OUTER JOIN MU_TREATMENT_PROCEDURE MTP ON HCBP.MU_TREATMENT_ID = MTP.MU_TREATMENT_ID AND HCBP.MU_TREATMENT_PROCEDURE_ID = MTP.MU_TREATMENT_PROCEDURE_ID
          LEFT OUTER JOIN MU_TREATMENT_PROCEDURE_PRICE MTPP ON MTP.MU_TREATMENT_PROCEDURE_ID = MTPP.MU_TREATMENT_PROCEDURE_ID
         WHERE HCBP.USE_YN        = 'Y'
           AND MTP.USE_YN         = 'Y'
           AND MTP.ENABLE_YN      = 'Y'
           AND MTPP.USE_YN        = 'Y'
           AND HCBP.CATEGORY_DIV_CD = #{categoryDivCd}
           AND HCBP.CATEGORY_NO  = #{categoryNo}
           AND HCBP.POST_NO  = #{postNo} 
                   
    </select>
    
    
    <insert id="putContentsBbs" parameterType="hashmap">
        <selectKey resultType="string" keyProperty="postNo" order="BEFORE">
            SELECT NVL(MAX(POST_NO),0) + 1 FROM HP_CONTENTS_BBS WHERE CATEGORY_DIV_CD = #{categoryDivCd} AND CATEGORY_NO = ${categoryNo}
        </selectKey>
        /** ContentsBbsSql.putContentsBbs **/
        INSERT INTO HP_CONTENTS_BBS(
                    CATEGORY_DIV_CD
                    ,CATEGORY_NO
                    ,POST_NO
                    ,TITLE
                    ,CONTENT
                    ,THUMBNAIL_BOTTOM_TXT
                  	,THUMBNAIL_ATTACHFILE_ID
                  	,CONTENTS_ATTACHFILE_ID
                    ,HASHTAG                    
                    ,USE_YN
                    ,REG_ID
                    ,REG_DATE
                    ,MOD_ID
                    ,MOD_DATE
                    ,OLD_CRM_ITEM_ID
                    ,ORD_NO
        )VALUES(
        			 #{categoryDivCd}
        			,#{categoryNo}
                    ,#{postNo}
                    ,#{title}
                    ,#{content}
                    ,#{thumbnail_bottom_txt}
                    ,#{thumbnailAttachfileId}
                    ,#{contentsAttachfileId}
                    ,#{hashtag}
                    ,'Y'
                    ,#{regId}
                    ,NOW()
                    ,#{modId}
                    ,NOW()
                    ,#{oldCrmItemId}
                    ,(SELECT MAX(ORD_NO) + 1 
                        FROM HP_CONTENTS_BBS as TEMP
                       WHERE CATEGORY_DIV_CD = #{categoryDivCd} AND CATEGORY_NO = #{categoryNo})
        )
    </insert>
    
        
    <insert id="insertWebTreatmentPetitContentFile" parameterType="hashmap">
    	<selectKey resultType="string" keyProperty="contentId" order="BEFORE">
            SELECT CONCAT('ATF-', LPAD(NEXTVAL(seq_attachfile_id), 21, '0'))
        </selectKey>
        /** ContentsBbsSql.insertWebTreatmentPetitContentFile **/
        INSERT INTO HP_ATTACH_FILE(
                     ATTACHFILE_ID
                    ,ATTACHFILE_NM
                    ,FILE_PATH
                    ,FILE_SIZE
                    ,FILE_TYPE
                    ,REG_ID
                    ,REG_DATE
                    ,MOD_ID
                    ,MOD_DATE
        )VALUES(
        			 #{contentId}
                    ,#{fileName2}
                    ,#{filePath2}
                    ,'0'
                    ,#{fileType2}
                    ,#{regId}
                    ,NOW()
                    ,#{modId}
                    ,NOW()
        )
        
    </insert>
    
    <insert id="insertWebTreatmentPetitThumbnailFile" parameterType="hashmap">
        <selectKey resultType="string" keyProperty="thumbnailId" order="BEFORE">
            SELECT CONCAT('ATF-', LPAD(NEXTVAL(seq_attachfile_id), 21, '0'))
        </selectKey>
        /** ContentsBbsSql.insertWebTreatmentPetitThumbnailFile **/
        INSERT INTO HP_ATTACH_FILE(
                     ATTACHFILE_ID
                    ,ATTACHFILE_NM
                    ,FILE_PATH
                    ,FILE_SIZE
                    ,FILE_TYPE
                    ,REG_ID
                    ,REG_DATE
                    ,MOD_ID
                    ,MOD_DATE
        )VALUES(
        			 #{thumbnailId}
                    ,#{fileName}
                    ,#{filePath}
                    ,'0'
                    ,#{fileType}
                    ,#{regId}
                    ,NOW()
                    ,#{modId}
                    ,NOW()
        )
    </insert>
    
    <update id="delContentsBbsProcedure" parameterType="hashmap">
        /** ContentsBbsSql.delContentsBbsProcedure **/
    	UPDATE HP_CONTENTS_BBS_PROCEDURE 
    	   SET USE_YN = 'N'
         WHERE CATEGORY_DIV_CD = #{categoryDivCd}
    	   AND CATEGORY_NO = ${categoryNo}
    	   AND POST_NO = #{postNo}
    </update>
    <insert id="putContentsBbsProcedure" parameterType="hashmap">
        /** ContentsBbsSql.putContentsBbsProcedure **/        	    	
        INSERT INTO HP_CONTENTS_BBS_PROCEDURE(
                    CATEGORY_DIV_CD
                    ,CATEGORY_NO
                    ,POST_NO
                    ,MU_TREATMENT_ID
                    ,MU_TREATMENT_PROCEDURE_ID      
                    ,USE_YN
                    ,REG_ID
                    ,REG_DATE
                    ,MOD_ID
                    ,MOD_DATE
        )VALUES(
        			 #{categoryDivCd}
        			,#{categoryNo}
        			,#{postNo}
                    ,#{muTreatmentId}
                    ,#{muTreatmentProcedureId}
                    ,'Y'
                    ,#{regId}
                    ,NOW()
                    ,#{modId}
                    ,NOW()
        )ON DUPLICATE KEY UPDATE
		    USE_YN = 'Y';
    </insert>

    <update id="modContentsBbs" parameterType="hashmap">
        /** ContentsBbsSql.modContentsBbs **/
        UPDATE HP_CONTENTS_BBS
           SET TITLE = #{title}
              ,CONTENT = #{content}
              ,THUMBNAIL_BOTTOM_TXT = #{thumbnailBottomTxt}
              ,HASHTAG = #{hashtag}
              <if test="@org.springframework.util.StringUtils@hasLength(thumbnailAttachfileId)">
           	  ,THUMBNAIL_ATTACHFILE_ID = #{thumbnailAttachfileId}
        	  </if>
              <if test="@org.springframework.util.StringUtils@hasLength(contentsAttachfileId)">
           	  ,CONTENTS_ATTACHFILE_ID = #{contentsAttachfileId}
        	  </if>             
              ,MOD_ID = #{modId}
              ,MOD_DATE = NOW()
              <if test="@org.springframework.util.StringUtils@hasLength(oldCrmItemId)">
              ,OLD_CRM_ITEM_ID = #{oldCrmItemId}
              </if>
              ,ORD_NO = #{ordNo}
         WHERE USE_YN = 'Y'
           AND CATEGORY_DIV_CD = #{categoryDivCd}
           AND CATEGORY_NO = #{categoryNo}
           AND POST_NO = #{postNo}
    </update>

    <update id="delContentsBbs" parameterType="hashmap">
	    /** ContentsBbsSql.delContentsBbs **/
	    UPDATE HP_CONTENTS_BBS
	       SET MOD_ID    = #{modId}
	         , MOD_DATE  = NOW()
	         , USE_YN    = 'N'
	     WHERE USE_YN        = 'Y'
	       AND CATEGORY_DIV_CD = #{categoryDivCd}
	       AND (CATEGORY_NO, POST_NO) IN
	       <foreach collection="delList" item="item" open="(" separator="," close=")">
	         (#{item.categoryNo}, #{item.postNo})
	       </foreach>
	</update>
     
    <select id="getCategoryList" parameterType="hashmap" resultType="hashmap">
        /** ContentsBbsSql.getCategoryList **/
        SELECT HC.CATEGORY_NO
              ,HC.CATEGORY_NM
          FROM HP_CATEGORY AS HC
         WHERE HC.USE_YN = 'Y'     
           AND HC.CATEGORY_DIV_CD = #{categoryDivCd}
         ORDER BY HC.CATEGORY_NO ASC
    </select>
</mapper>