<?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="post">
	
	<resultMap id="postPagingResult" type="postPaging">
		<id property="postId" column="POST_ID" />
		<result property="classificationName" column="CLASSIFICATION_NAME" />
		<result property="postTitle" column="POST_TITLE" />
		<result property="postDetail" column="POST_DETAIL" />
		<result property="upperPostId" column="UPPER_POST_ID" />
		<result property="hierarchyLevel" column="HIERARCHY_LEVEL" />
		<result property="replySequence" column="REPLY_SEQUENCE" />
		<result property="noticePopupStartDate" column="NOTICE_POPUP_START_DATE" />
		<result property="noticePopupEndDate" column="NOTICE_POPUP_END_DATE" />
		<result property="regDate" column="REG_DATE" jdbcType="TIMESTAMP" />
		<result property="regrName" column="REGR_NAME" />
		<result property="hitCount" column="HIT_COUNT" />
		<result property="attachExistsYn" column="ATTACH_EXISTS_YN" />
		<result property="imageAttachExistsYn" column="IMAGE_ATTACH_EXISTS_YN" />
		<result property="defaultImageFileId" column="DEFAULT_IMAGE_FILE_ID" />
		<result property="noticeChecked" column="NOTICE_CHECK_YN" />
		<collection property="replyPosts" column="{upperPostId=POST_ID}" javaType="ArrayList" ofType="replyPosts" select="selectReplyPostList" />
	</resultMap>
	
	<resultMap id="postResult" type="post">
		<id property="boardId" column="BOARD_ID" />
		<id property="postId" column="POST_ID" />
		<result property="postTitle" column="POST_TITLE" />
		<result property="postDetail" column="POST_DETAIL" />
		<result property="classificationId" column="CLASSIFICATION_ID" />
		<result property="classificationName" column="CLASSIFICATION_NAME" />
		<result property="upperPostId" column="UPPER_POST_ID" />
		<result property="hierarchyLevel" column="HIERARCHY_LEVEL" />
		<result property="replySequence" column="REPLY_SEQUENCE" />
		<result property="noticePopupUsed" column="NOTICE_POPUP_YN" />
		<result property="noticePopupStartDate" column="NOTICE_POPUP_START_DATE" />
		<result property="noticePopupEndDate" column="NOTICE_POPUP_END_DATE" />
		<result property="noticePopupWidth" column="NOTICE_POPUP_WIDTH" />
		<result property="noticePopupHeight" column="NOTICE_POPUP_HEIGHT" />
		<result property="noticeLabelUsed" column="NOTICE_LABEL_YN" />
		<result property="noticeLabelType" column="NOTICE_LABEL_TYPE" />
		<result property="noticeLabelStartDate" column="NOTICE_LABEL_START_DATE" />
		<result property="noticeLabelEndDate" column="NOTICE_LABEL_END_DATE" />
		<result property="regDate" column="REG_DATE" />
		<result property="regrId" column="REGR_ID" />
		<result property="regrName" column="REGR_NAME" />
		<result property="hitCount" column="HIT_COUNT" />
		<result property="recommendYn" column="RECOMMEND_YN" />
		<result property="recommendCount" column="RECOMMEND_COUNT" />
		<association property="boardInfo" column="{boardId=BOARD_ID}" select="board.selectBoard" />
		<collection property="classifications" column="{boardId=BOARD_ID}" select="board.selectBoardClassificationList" />
		<collection property="attachments" column="{downloadType=BOARD_ID, refId=POST_ID}" select="file.selectFileInfo" />
		<collection property="imageAttachments" column="{downloadType=BOARD_ID_IMAGE, refId=POST_ID}" select="file.selectFileInfo" />
	</resultMap>
	
	<sql id="tablePost">TN_CF_POST</sql>
	<sql id="tablePostHitHistory">TN_CF_POST_HIT_HISTORY</sql>
	<sql id="tablePostRecommend">TN_CF_POST_RECOMMEND</sql>
	<sql id="tableNoticeChecked">TN_CF_NOTICE_CHECKED</sql>
	
	<sql id="conditionPost">
	WHERE  P.DELETE_YN = '0'
	AND    P.HIERARCHY_LEVEL = 1
	AND    P.BOARD_ID = #{boardId}
	<if test="@com.samsung.SdlComparator@isNotEmpty(postTitle)">
	AND    P.POST_TITLE like '%'+#{postTitle}+'%'
	</if>
	<if test="@com.samsung.SdlComparator@isNotEmpty(postDetail)">
	AND    P.POST_DETAIL like '%'+#{postDetail}+'%'
	</if>
	<if test="@com.samsung.SdlComparator@isNotEmpty(postKeyword)">
	AND    (P.POST_TITLE like '%'+#{postKeyword}+'%' OR P.POST_DETAIL LIKE '%'+#{postKeyword}+'%')
	</if>
	<if test="@com.samsung.SdlComparator@isNotEmpty(classificationId)">
	AND    P.CLASSIFICATION_ID = #{classificationId}
	</if>
	<if test="@com.samsung.SdlComparator@isNotEmpty(startDate)">
	AND    CONVERT(VARCHAR(8), P.FIRST_REG_DATETIME, 112) <![CDATA[ >= ]]> #{startDate}
	</if>
	<if test="@com.samsung.SdlComparator@isNotEmpty(endDate)">
	AND    CONVERT(VARCHAR(8), P.FIRST_REG_DATETIME, 112) <![CDATA[ <= ]]> #{endDate}
	</if>
	</sql>
	
	<select id="selectNoticePopupPostList" resultMap="postPagingResult">
		SELECT P.POST_ID, BC.CLASSIFICATION_NAME,
		       P.POST_TITLE, P.POST_DETAIL, U.USER_NAME REGR_NAME,
		       CASE
		          WHEN NC.USER_ID IS NOT NULL THEN '1'
		          ELSE '0'
		       END NOTICE_CHECK_YN
		FROM   <include refid="board.tableBoard" /> B
		       LEFT JOIN <include refid="tablePost" /> P
		       ON B.BOARD_ID = P.BOARD_ID
		       LEFT OUTER JOIN <include refid="board.tableBoardClassification" /> BC
		       ON P.BOARD_ID = BC.BOARD_ID AND P.CLASSIFICATION_ID = BC.CLASSIFICATION_ID
		       LEFT JOIN <include refid="user.tableUser" /> U
		       ON P.FIRST_REGR_ID = U.USER_ID
		       LEFT OUTER JOIN <include refid="tableNoticeChecked" /> NC
		       ON P.POST_ID = NC.POST_ID AND NC.USER_ID = #{userId}
		WHERE  B.NOTICE_POPUP_UNFOLD_YN = '0'
		AND    P.DELETE_YN = '0'
		AND    P.NOTICE_POPUP_YN = '1'
		AND    P.NOTICE_POPUP_START_DATE <![CDATA[ <= ]]> CONVERT(NVARCHAR(8), GETDATE(), 112)
		AND    P.NOTICE_POPUP_END_DATE <![CDATA[ >= ]]> CONVERT(NVARCHAR(8), GETDATE(), 112)
	</select>
	
	<select id="selectNoticeChecked" parameterType="java.util.HashMap" resultType="boolean">
		SELECT CASE
		          WHEN COUNT(USER_ID) = 0 THEN '0'
		          ELSE '1'
		       END CHECKED
		FROM   <include refid="tableNoticeChecked" />
		WHERE  POST_ID = #{postId}
		AND    USER_ID = #{userId}
	</select>
	
	<insert id="insertNoticeChecked" parameterType="java.util.HashMap">
		INSERT INTO <include refid="tableNoticeChecked" />
		(POST_ID, USER_ID, CHECKED_DATETIME)
		VALUES
		(#{postId}, #{userId}, GETDATE())
	</insert>
	
	<select id="selectSortInfo" parameterType="java.util.HashMap" resultMap="board.boardResult">
		SELECT BOARD_ID, SORT_COLUMN, SORT_METHOD
		FROM   <include refid="board.tableBoard" />
		WHERE  BOARD_ID = #{boardId}
	</select>
	
	<select id="selectPostPagingList" parameterType="java.util.HashMap" resultMap="postPagingResult">
		SELECT P.*
		FROM   (SELECT T.*,
		               CEILING(ROW_NUMBER() OVER(ORDER BY ${sortColumn} ${sortMethod}) / (#{pageSize}*1.0)) PAGE
		        FROM   (SELECT P.POST_ID, P.POST_TITLE, BC.CLASSIFICATION_NAME,
		                       P.UPPER_POST_ID, P.HIERARCHY_LEVEL, P.REPLY_SEQUENCE,
		                       P.NOTICE_POPUP_START_DATE, P.NOTICE_POPUP_END_DATE,
		                       P.FIRST_REG_DATETIME REG_DATE, U.USER_NAME REGR_NAME,
		                       (SELECT COUNT(USER_ID) CNT
		                        FROM   <include refid="tablePostHitHistory" /> H
		                        WHERE  H.POST_ID = P.POST_ID) HIT_COUNT,
		                       CASE
		                          WHEN AF.FILE_ID IS NOT NULL THEN '1'
		                          ELSE '0'
		                       END ATTACH_EXISTS_YN,
		                       CASE
		                          WHEN IAF.FILE_ID IS NOT NULL THEN '1'
		                          ELSE '0'
		                       END IMAGE_ATTACH_EXISTS_YN,
		                       IAF.FILE_ID DEFAULT_IMAGE_FILE_ID
		                FROM   <include refid="tablePost" /> P
		                       LEFT OUTER JOIN <include refid="board.tableBoardClassification" /> BC
		                       ON P.BOARD_ID = BC.BOARD_ID AND P.CLASSIFICATION_ID = BC.CLASSIFICATION_ID
		                       LEFT OUTER JOIN <include refid="file.tableAttachFile" /> AF
		                       ON P.BOARD_ID = AF.DOWNLOAD_TYPE AND P.POST_ID = AF.OWNER_OBJECT_PK_ID
		                          AND AF.ORDER_IDX = 0 AND AF.DELETE_YN = '0'
		                       LEFT OUTER JOIN <include refid="file.tableAttachFile" /> IAF
		                       ON P.BOARD_ID+'_IMAGE' = IAF.DOWNLOAD_TYPE AND P.POST_ID = IAF.OWNER_OBJECT_PK_ID
		                          AND IAF.ORDER_IDX = 0 AND IAF.DELETE_YN = '0'
		                       LEFT JOIN <include refid="user.tableUser" /> U
         		               ON P.FIRST_REGR_ID = U.USER_ID
		                <include refid="conditionPost" />) T) P
		WHERE  P.PAGE = #{pageIndex}
	</select>
	
	<select id="selectReplyPostList" parameterType="java.util.HashMap" resultMap="postPagingResult">
		SELECT P.POST_ID, P.POST_TITLE, BC.CLASSIFICATION_NAME,
		       P.UPPER_POST_ID, P.HIERARCHY_LEVEL, P.REPLY_SEQUENCE,
		       P.NOTICE_POPUP_START_DATE, P.NOTICE_POPUP_END_DATE,
		       P.FIRST_REG_DATETIME REG_DATE, U.USER_NAME REGR_NAME,
		       (SELECT COUNT(USER_ID) CNT
		        FROM   <include refid="tablePostHitHistory" /> H
		        WHERE  H.POST_ID = P.POST_ID) HIT_COUNT,
		       CASE
		          WHEN AF.FILE_ID IS NOT NULL THEN '1'
		          ELSE '0'
		       END ATTACH_EXISTS_YN,
		       CASE
		          WHEN IAF.FILE_ID IS NOT NULL THEN '1'
		          ELSE '0'
		       END IMAGE_ATTACH_EXISTS_YN
		FROM   <include refid="tablePost" /> P
		       LEFT OUTER JOIN <include refid="board.tableBoardClassification" /> BC
		       ON P.BOARD_ID = BC.BOARD_ID AND P.CLASSIFICATION_ID = BC.CLASSIFICATION_ID
		       LEFT OUTER JOIN <include refid="file.tableAttachFile" /> AF
		       ON P.BOARD_ID = AF.DOWNLOAD_TYPE AND P.POST_ID = AF.OWNER_OBJECT_PK_ID
		          AND AF.ORDER_IDX = 0 AND AF.DELETE_YN = '0'
		       LEFT OUTER JOIN <include refid="file.tableAttachFile" /> IAF
		       ON P.BOARD_ID+'_IMAGE' = IAF.DOWNLOAD_TYPE AND P.POST_ID = IAF.OWNER_OBJECT_PK_ID
		          AND IAF.ORDER_IDX = 0 AND IAF.DELETE_YN = '0'
		       LEFT JOIN <include refid="user.tableUser" /> U
		       ON P.FIRST_REGR_ID = U.USER_ID
		WHERE  P.DELETE_YN = '0'
		AND    P.UPPER_POST_ID = #{upperPostId}
		ORDER BY P.REPLY_SEQUENCE ASC
	</select>
	
	<select id="countPost" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(P.POST_ID) TOTAL
		FROM   <include refid="tablePost" /> P
		<include refid="conditionPost" />
	</select>
	
	<select id="selectNoticeLabelPostList" parameterType="java.util.HashMap" resultMap="postPagingResult">
		SELECT P.POST_ID, P.POST_TITLE, P.HIERARCHY_LEVEL, BC.CLASSIFICATION_NAME,
		       P.NOTICE_POPUP_YN, P.NOTICE_POPUP_START_DATE, P.NOTICE_POPUP_END_DATE,
		       P.NOTICE_POPUP_WIDTH, P.NOTICE_POPUP_HEIGHT,
		       P.NOTICE_LABEL_YN, P.NOTICE_LABEL_TYPE,
		       P.NOTICE_LABEL_START_DATE, P.NOTICE_LABEL_END_DATE,
		       P.FIRST_REG_DATETIME REG_DATE, U.USER_NAME REGR_NAME,
		       (SELECT COUNT(USER_ID) CNT
		        FROM   <include refid="tablePostHitHistory" /> H
		        WHERE  H.POST_ID = P.POST_ID) HIT_COUNT,
		       CASE
		          WHEN AF.FILE_ID IS NOT NULL THEN '1'
		          ELSE '0'
		       END ATTACH_EXISTS_YN,
		       CASE
		          WHEN IAF.FILE_ID IS NOT NULL THEN '1'
		          ELSE '0'
		       END IMAGE_ATTACH_EXISTS_YN
		FROM   <include refid="tablePost" /> P
		       LEFT OUTER JOIN <include refid="board.tableBoardClassification" /> BC
		       ON P.BOARD_ID = BC.BOARD_ID AND P.CLASSIFICATION_ID = BC.CLASSIFICATION_ID
		       LEFT OUTER JOIN <include refid="file.tableAttachFile" /> AF
		       ON P.BOARD_ID = AF.DOWNLOAD_TYPE AND P.POST_ID = AF.OWNER_OBJECT_PK_ID
		          AND AF.ORDER_IDX = 0 AND AF.DELETE_YN = '0'
		       LEFT OUTER JOIN <include refid="file.tableAttachFile" /> IAF
		       ON P.BOARD_ID+'_IMAGE' = IAF.DOWNLOAD_TYPE AND P.POST_ID = IAF.OWNER_OBJECT_PK_ID
		          AND IAF.ORDER_IDX = 0 AND IAF.DELETE_YN = '0'
		       LEFT JOIN <include refid="user.tableUser" /> U
		       ON P.FIRST_REGR_ID = U.USER_ID
		WHERE  (P.NOTICE_LABEL_TYPE = 'ALL'
		        OR
		        (P.NOTICE_LABEL_TYPE = 'THIS' AND P.BOARD_ID = #{boardId}))
		AND    P.NOTICE_LABEL_YN = '1'
		AND    P.HIERARCHY_LEVEL = 1
		AND    P.DELETE_YN = '0'
		AND    P.NOTICE_LABEL_START_DATE <![CDATA[ <= ]]> CONVERT(NVARCHAR(8), GETDATE(), 112)
		AND    P.NOTICE_LABEL_END_DATE <![CDATA[ >= ]]> CONVERT(NVARCHAR(8), GETDATE(), 112)
		ORDER BY P.NOTICE_LABEL_TYPE ASC, P.NOTICE_LABEL_START_DATE DESC
	</select>
	
	<select id="selectPost" parameterType="java.util.HashMap" resultMap="postResult">
		SELECT P.BOARD_ID, P.POST_ID, P.POST_TITLE, P.POST_DETAIL,
		       P.CLASSIFICATION_ID, BC.CLASSIFICATION_NAME,
		       P.UPPER_POST_ID, P.HIERARCHY_LEVEL, P.REPLY_SEQUENCE,
		       NOTICE_POPUP_YN, NOTICE_POPUP_START_DATE, NOTICE_POPUP_END_DATE,
		       NOTICE_POPUP_WIDTH, NOTICE_POPUP_HEIGHT,
		       NOTICE_LABEL_YN, NOTICE_LABEL_TYPE,
		       NOTICE_LABEL_START_DATE, NOTICE_LABEL_END_DATE,
		       P.FIRST_REG_DATETIME REG_DATE, P.FIRST_REGR_ID REGR_ID, U.USER_NAME REGR_NAME,
		       (SELECT COUNT(USER_ID) CNT
		        FROM   <include refid="tablePostHitHistory" /> H
		        WHERE  H.POST_ID = P.POST_ID) HIT_COUNT,
		       (SELECT CASE
		                 WHEN COUNT(PR.USER_ID) = 0 THEN 'N'
		                 WHEN COUNT(PR.USER_ID) != 0 THEN 'Y'
		               END
		        FROM   <include refid="tablePostRecommend" /> PR
		        WHERE  PR.POST_ID = P.POST_ID
		        AND    PR.USER_ID = #{userId}) RECOMMEND_YN,
		       (SELECT COUNT(USER_ID) CNT
		        FROM   <include refid="tablePostRecommend" /> R
		        WHERE  R.POST_ID = P.POST_ID) RECOMMEND_COUNT,
		       P.BOARD_ID+'_IMAGE' BOARD_ID_IMAGE
		FROM   <include refid="tablePost" /> P
		       LEFT OUTER JOIN <include refid="board.tableBoardClassification" /> BC
		       ON P.BOARD_ID = BC.BOARD_ID AND P.CLASSIFICATION_ID = BC.CLASSIFICATION_ID
		       LEFT JOIN <include refid="user.tableUser" /> U
		       ON P.FIRST_REGR_ID = U.USER_ID
		WHERE  P.POST_ID = #{postId}
	</select>
	
	<select id="selectPostHitByUserId" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(USER_ID) CNT
		FROM   <include refid="tablePostHitHistory" />
		WHERE  POST_ID = #{postId}
		AND    USER_ID = #{userId}
	</select>
	
	<insert id="insertPostHit" parameterType="java.util.HashMap">
		INSERT INTO <include refid="tablePostHitHistory" />
		(POST_ID, USER_ID, HIT_DATETIME)
		VALUES
		(#{postId}, #{userId}, GETDATE())
	</insert>
	
	<select id="selectCurrentReplySequence" parameterType="java.util.HashMap" resultType="int">
		SELECT ISNULL(MAX(REPLY_SEQUENCE), 0)+1 SEQ
		FROM   <include refid="tablePost" />
		WHERE  BOARD_ID = #{boardId}
		AND    UPPER_POST_ID = #{upperPostId}
	</select>
	
	<insert id="insertPost" parameterType="post">
		INSERT INTO <include refid="tablePost" />
		(BOARD_ID, POST_ID, POST_TITLE, POST_DETAIL,
		 UPPER_POST_ID, HIERARCHY_LEVEL, REPLY_SEQUENCE, CLASSIFICATION_ID,
		 NOTICE_POPUP_YN, NOTICE_POPUP_START_DATE, NOTICE_POPUP_END_DATE,
		 NOTICE_POPUP_WIDTH, NOTICE_POPUP_HEIGHT,
		 NOTICE_LABEL_YN, NOTICE_LABEL_TYPE,
		 NOTICE_LABEL_START_DATE, NOTICE_LABEL_END_DATE,
		 FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID)
		VALUES
		(#{boardId}, #{postId}, #{postTitle}, #{postDetail},
		 #{upperPostId}, #{hierarchyLevel}, #{replySequence}, #{classificationId},
		 #{noticePopupUsed}, #{noticePopupStartDate}, #{noticePopupEndDate},
		 #{noticePopupWidth}, #{noticePopupHeight},
		 #{noticeLabelUsed}, #{noticeLabelType},
		 #{noticeLabelStartDate}, #{noticeLabelEndDate},
		 #{firstRegDatetime, jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime, jdbcType=TIMESTAMP}, #{lastModrId})
	</insert>
	
	<select id="selectPostRegrId" parameterType="java.util.HashMap" resultType="string">
		SELECT FIRST_REGR_ID
		FROM   <include refid="tablePost" />
		WHERE  POST_ID = #{postId}
	</select>
	
	<update id="updatePost" parameterType="post">
		UPDATE <include refid="tablePost" />
		SET    POST_TITLE = #{postTitle},
		       POST_DETAIL = #{postDetail},
		       CLASSIFICATION_ID = #{classificationId},
		       NOTICE_POPUP_YN = #{noticePopupUsed},
		       NOTICE_POPUP_START_DATE = #{noticePopupStartDate},
		       NOTICE_POPUP_END_DATE = #{noticePopupEndDate},
		       NOTICE_POPUP_WIDTH = #{noticePopupWidth},
		       NOTICE_POPUP_HEIGHT = #{noticePopupHeight},
		       NOTICE_LABEL_YN = #{noticeLabelUsed},
		       NOTICE_LABEL_TYPE = #{noticeLabelType},
		       NOTICE_LABEL_START_DATE = #{noticeLabelStartDate},
		       NOTICE_LABEL_END_DATE = #{noticeLabelEndDate},
		       LAST_MOD_DATETIME = #{lastModDatetime, jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  POST_ID = #{postId}
	</update>
	
	<update id="deletePost" parameterType="java.util.HashMap">
		UPDATE <include refid="tablePost" />
		SET    DELETE_YN = '1'
		WHERE  POST_ID = #{postId}
	</update>
	
	<select id="selectPostRecommendByUserId" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(USER_ID) CNT
		FROM   <include refid="tablePostRecommend" />
		WHERE  POST_ID = #{postId}
		AND    USER_ID = #{userId}
	</select>
	
	<insert id="insertPostRecommend" parameterType="java.util.HashMap">
		INSERT INTO <include refid="tablePostRecommend" />
		(POST_ID, USER_ID, RECOMMEND_DATETIME)
		VALUES
		(#{postId}, #{userId}, GETDATE())
	</insert>
	
	<delete id="deletePostRecommend" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tablePostRecommend" />
		WHERE  POST_ID = #{postId}
		AND    USER_ID = #{userId}
	</delete>
	
	<select id="selectPostRecommentCount" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(POST_ID) CNT
		FROM   <include refid="tablePostRecommend" />
		WHERE  POST_ID = #{postId}
	</select>
	
</mapper>