<?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="board">
	
	<resultMap id="boardPagingResult" type="boardPaging">
		<id property="boardId" column="BOARD_ID" />
		<result property="boardType" column="BOARD_TYPE" />
		<result property="boardName" column="BOARD_NAME" />
		<result property="description" column="DESCRIPTION" />
		<result property="postCount" column="POST_COUNT" />
		<result property="regDate" column="FIRST_REG_DATETIME" />
		<result property="deleted" column="DELETE_YN" />
	</resultMap>
	
	<resultMap id="boardResult" type="board">
		<id property="boardId" column="BOARD_ID" />
		<result property="boardType" column="BOARD_TYPE" />
		<result property="boardName" column="BOARD_NAME" />
		<result property="description" column="DESCRIPTION" />
		<result property="pageSize" column="PAGE_SIZE" />
		<result property="searchConditionUsed" column="SEARCH_CONDITION_USE_YN" />
		<result property="searchClassificationUsed" column="SEARCH_CLASSIFICATION_USE_YN" />
		<result property="searchDateUsed" column="SEARCH_DATE_USE_YN" />
		<result property="classificationUsed" column="CLASSIFICATION_USE_YN" />
		<result property="noticePopupUsed" column="NOTICE_POPUP_USE_YN" />
		<result property="noticePopupUnfoldUsed" column="NOTICE_POPUP_UNFOLD_YN" />
		<result property="noticeLabelUsed" column="NOTICE_LABEL_USE_YN" />
		<result property="editorUsed" column="EDITOR_USE_YN" />
		<result property="answerUsed" column="ANSWER_USE_YN" />
		<result property="commentUsed" column="COMMENT_USE_YN" />
		<result property="replyCommentUsed" column="REPLY_COMMENT_USE_YN" />
		<result property="fileAttachUsed" column="FILE_ATTACH_USE_YN" />
		<result property="imageAttachUsed" column="IMAGE_ATTACH_USE_YN" />
		<result property="sortColumn" column="SORT_COLUMN" />
		<result property="sortMethod" column="SORT_METHOD" />
		<result property="deleted" column="DELETE_YN" />
		<collection property="classifications" column="{boardId=BOARD_ID}" javaType="ArrayList" ofType="classification" select="selectBoardClassificationList" />
		<collection property="columns" column="{boardId=BOARD_ID}" javaType="ArrayList" ofType="column" select="selectBoardColumnList" />
	</resultMap>
	
	<resultMap id="boardPopupResult" type="boardPopup">
		<id property="boardId" column="BOARD_ID" />
		<result property="boardType" column="BOARD_TYPE" />
		<result property="boardName" column="BOARD_NAME" />
		<result property="firstRegDatetime" column="FIRST_REG_DATETIME" />
	</resultMap>
	
	<resultMap id="boardClassificationResult" type="boardClassification">
		<id property="classificationId" column="CLASSIFICATION_ID" />
		<result property="classificationName" column="CLASSIFICATION_NAME" />
		<result property="description" column="DESCRIPTION" />
	</resultMap>
	
	<resultMap id="boardColumnResult" type="boardColumn">
		<id property="columnSequence" column="COLUMN_SEQUENCE" />
		<result property="columnName" column="COLUMN_NAME" />
		<result property="columnLabelCode" column="COLUMN_LABEL_CODE" />
		<result property="columnWidth" column="COLUMN_WIDTH" />
		<result property="postMappColumn" column="POST_MAPP_COLUMN" />
		<result property="used" column="USE_YN" />
	</resultMap>
	
	<sql id="tableBoard">TN_CF_BOARD</sql>
	<sql id="tableBoardClassification">TN_CF_BOARD_CLASSIFICATION</sql>
	<sql id="tableBoardColumn">TN_CF_BOARD_COLUMN</sql>
	
	<sql id="columnsBoard">
	BOARD_ID, BOARD_TYPE, BOARD_NAME, DESCRIPTION, PAGE_SIZE,
	SEARCH_CONDITION_USE_YN, SEARCH_CLASSIFICATION_USE_YN, SEARCH_DATE_USE_YN,
	CLASSIFICATION_USE_YN, NOTICE_POPUP_USE_YN, NOTICE_POPUP_UNFOLD_YN, NOTICE_LABEL_USE_YN,
	EDITOR_USE_YN, ANSWER_USE_YN, COMMENT_USE_YN, REPLY_COMMENT_USE_YN,
	FILE_ATTACH_USE_YN, IMAGE_ATTACH_USE_YN, SORT_COLUMN, SORT_METHOD, DELETE_YN,
	FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID
	</sql>
	
	<select id="selectBoardListByPopup" resultMap="boardPopupResult">
		SELECT BOARD_ID,
		       BOARD_TYPE,
		       BOARD_NAME,
		       FIRST_REG_DATETIME
		FROM   <include refid="tableBoard" />
		WHERE  DELETE_YN = '0'
		ORDER BY BOARD_NAME
	</select>
	
	<select id="selectBoardPagingList" parameterType="java.util.HashMap" resultMap="boardPagingResult">
		SELECT BOARD_ID, BOARD_TYPE, BOARD_NAME, DESCRIPTION,
		       POST_COUNT, FIRST_REG_DATETIME, DELETE_YN
		FROM   (SELECT T.*,
		               CEILING(ROW_NUMBER() OVER(<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">ORDER BY ${orderBy}</if>)/(#{pageSize}*1.0)) PAGE
		        FROM   (SELECT B.BOARD_ID, B.BOARD_TYPE, B.BOARD_NAME, B.DESCRIPTION,
		                       B.FIRST_REG_DATETIME, B.DELETE_YN,
		                       ISNULL((SELECT SUM(1)
		                               FROM   <include refid="post.tablePost" /> P
		                               WHERE  P.BOARD_ID = B.BOARD_ID
		                               AND    P.DELETE_YN = '0'), 0) POST_COUNT
						FROM   <include refid="tableBoard" /> B
		                WHERE  B.BOARD_ID != 'DEFAULT_COLUMNS') T) P
		WHERE  P.PAGE = #{pageIndex}
	</select>
	
	<select id="countBoard" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(BOARD_ID) TOTAL
		FROM   <include refid="tableBoard" />
		WHERE  BOARD_ID != 'DEFAULT_COLUMNS'
	</select>
	
	<select id="selectBoard" parameterType="java.util.HashMap" resultMap="boardResult">
		SELECT <include refid="columnsBoard" />
		FROM   <include refid="tableBoard" />
		WHERE  BOARD_ID = #{boardId}
	</select>
	
	<select id="selectBoardClassificationList" resultMap="boardClassificationResult">
		SELECT CLASSIFICATION_ID, CLASSIFICATION_NAME, DESCRIPTION
		FROM   <include refid="tableBoardClassification" />
		WHERE  BOARD_ID = #{boardId}
		ORDER BY CLASSIFICATION_NAME ASC
	</select>
	
	<select id="selectBoardColumnList" resultMap="boardColumnResult">
		SELECT COLUMN_SEQUENCE, COLUMN_NAME, COLUMN_LABEL_CODE,
		       COLUMN_WIDTH, POST_MAPP_COLUMN, USE_YN
		FROM   <include refid="tableBoardColumn" />
		WHERE  BOARD_ID = #{boardId}
		ORDER BY COLUMN_SEQUENCE
	</select>
	
	<insert id="insertBoard" parameterType="board">
		INSERT INTO <include refid="tableBoard" />
		(<include refid="columnsBoard" />)
		VALUES
		(#{boardId}, #{boardType}, #{boardName}, #{description}, #{pageSize},
		 #{searchConditionUsed}, #{searchClassificationUsed}, #{searchDateUsed},
		 #{classificationUsed}, #{noticePopupUsed}, #{noticePopupUnfoldUsed}, #{noticeLabelUsed},
		 #{editorUsed}, #{answerUsed}, #{commentUsed}, #{replyCommentUsed},
		 #{fileAttachUsed}, #{imageAttachUsed}, #{sortColumn}, #{sortMethod}, #{deleted},
		 #{firstRegDatetime, jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime, jdbcType=TIMESTAMP}, #{lastModrId})
	</insert>
	
	<insert id="insertBoardClassification" parameterType="boardClassification">
		INSERT INTO <include refid="tableBoardClassification" />
		(BOARD_ID, CLASSIFICATION_ID, CLASSIFICATION_NAME, DESCRIPTION,
		 FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID)
		VALUES
		(#{boardId}, #{classificationId}, #{classificationName}, #{description},
		 #{firstRegDatetime, jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime, jdbcType=TIMESTAMP}, #{lastModrId})
	</insert>
	
	<insert id="insertBoardColumn" parameterType="java.util.HashMap">
		INSERT INTO <include refid="tableBoardColumn" />
		SELECT #{boardId} BOARD_ID, COLUMN_SEQUENCE, COLUMN_NAME,
		       COLUMN_LABEL_CODE, COLUMN_WIDTH, POST_MAPP_COLUMN, USE_YN,
		       GETDATE(), #{userId}, GETDATE(), #{userId}
		FROM   <include refid="tableBoardColumn" />
		WHERE  BOARD_ID = 'DEFAULT_COLUMNS'
	</insert>
	
	<update id="updateBoard" parameterType="java.util.HashMap">
		UPDATE <include refid="tableBoard" />
		SET    BOARD_NAME = #{boardName},
		       DESCRIPTION = #{description},
		       PAGE_SIZE = #{pageSize},
		       SEARCH_CONDITION_USE_YN = #{searchConditionUsed},
		       SEARCH_CLASSIFICATION_USE_YN = #{searchClassificationUsed},
		       SEARCH_DATE_USE_YN = #{searchDateUsed},
		       CLASSIFICATION_USE_YN = #{classificationUsed},
		       NOTICE_POPUP_USE_YN = #{noticePopupUsed},
		       NOTICE_POPUP_UNFOLD_YN = #{noticePopupUnfoldUsed},
		       NOTICE_LABEL_USE_YN = #{noticeLabelUsed},
		       EDITOR_USE_YN = #{editorUsed},
		       ANSWER_USE_YN = #{answerUsed},
		       COMMENT_USE_YN = #{commentUsed},
		       REPLY_COMMENT_USE_YN = #{replyCommentUsed},
		       FILE_ATTACH_USE_YN = #{fileAttachUsed},
		       IMAGE_ATTACH_USE_YN = #{imageAttachUsed},
		       SORT_COLUMN = #{sortColumn},
		       SORT_METHOD = #{sortMethod},
		       LAST_MOD_DATETIME = #{lastModDatetime, jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  BOARD_ID = #{boardId}
	</update>
	
	<update id="updateBoardClassification" parameterType="java.util.HashMap">
		UPDATE <include refid="tableBoardClassification" />
		SET    CLASSIFICATION_NAME = #{classificationName},
		       DESCRIPTION = #{description},
		       LAST_MOD_DATETIME = #{lastModDatetime, jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  BOARD_ID = #{boardId}
		AND    CLASSIFICATION_ID = #{classificationId}
	</update>
	
	<update id="updateBoardColumn" parameterType="java.util.HashMap">
		UPDATE <include refid="tableBoardColumn" />
		SET    COLUMN_NAME = #{columnName},
		       COLUMN_LABEL_CODE = #{columnLabelCode},
		       COLUMN_WIDTH = #{columnWidth},
		       USE_YN = #{used},
		       LAST_MOD_DATETIME = #{lastModDatetime, jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  BOARD_ID = #{boardId}
		AND    COLUMN_SEQUENCE = #{columnSequence}
	</update>
	
	<update id="deleteBoard" parameterType="java.util.HashMap">
		UPDATE <include refid="tableBoard" />
		SET    DELETE_YN = '1'
		WHERE  BOARD_ID = #{boardId}
	</update>
	
	<delete id="deleteBoardClassification" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableBoardClassification" />
		WHERE  CLASSIFICATION_ID = #{classificationId}
	</delete>
	
</mapper>