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

	<select id="Board.selectBoardList" parameterType="boardSearchVO" resultType="egovMap">
	/* Board.selectBoardList */
          SELECT
			  bd_idx AS bdIdx
			, bd_yn AS bdYn
			, bd_idx_grp AS bdIdxGrp
			, bbs_id AS bbsId
			, ct_id AS ctId
			, bd_part AS bdPart
			, bd_subject AS bdSubject
			, bd_content AS bdContent
			, bd_files AS bdFiles
			, bd_fileNames AS bdFileNames
			, bd_imgs AS bdImgs
			, bd_imgNames AS bdImgNames
			, grp_idx AS grpIdx
			, grp_name AS grpName
			, usr_idx AS usrIdx
			, usr_name AS usrName
			, bd_ipaddr AS bdIpaddr
			, FROM_UNIXTIME(bd_regdate) AS bdRegdate
			, bd_view_auth AS bdViewAuth
			, bd_view_grp AS bdViewGrp
			, bd_class AS bdClass
			, bd_tel AS bdTel
			, bd_object AS bdObject
			, bd_national AS bdNational
			, bd_timelimit AS bdTimelimit
			, bd_etc AS bdEtc
			, bd_count AS bdCount
		FROM h2_board_data
		WHERE 
			bbs_id = #{bbsId}
		AND 
		(
			bd_view_auth = '0'
			<if test='pla1 == "PS00" and (pla2 == "5" or pla2 == "22" ) '> 
		OR
			bd_view_grp like concat('%', #{pla2}, '%') 
			</if>
		OR usr_idx = #{userid}
		)
		<if test="ct != null and ct != ''">
		AND ct_id = #{ct}
		</if>
		<if test="bdNational != null and bdNational != ''">
		AND bd_national = #{bdNational}
		</if>
		<if test="searchKeyword != null and searchKeyword != ''">
	        <choose>
	            <when test="searchCondition == 0">
					AND	GRP_NAME LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
	            <when test="searchCondition == 1">
					AND	USR_NAME LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
	            <when test="searchCondition == 2">
					AND	BD_SUBJECT LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
				<!-- 20180528 검색기능 중에 내용 관련 부분 추가 -->
	            <when test="searchCondition == 3">
					AND	BD_CONTENT LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
			</choose>
		</if>	
		<choose>
			<when test=" 'joint'.equals(bbsId) or 'law'.equals(bbsId)">
		ORDER BY bd_idx_grp DESC , bd_idx ASC
			</when>
			<otherwise>
		ORDER BY  bd_idx desc
			</otherwise>
		</choose>
		LIMIT  #{firstIndex} , #{recordCountPerPage}
	</select>
	<select id="Board.selectBoardTotalCnt" parameterType="boardSearchVO" resultType="int">
	/* Board.selectBoardTotalCnt */
		SELECT
			COUNT(*)
		FROM 
			h2_board_data
		WHERE bbs_id = #{bbsId}
		AND 
		(
			bd_view_auth = '0'
			<if test='pla1 == "PS00" and (pla2 == "5" or pla2 == "22" ) '> 
		OR
			bd_view_grp like concat('%', #{pla2}, '%') 
			</if>
		OR usr_idx = #{userid}
		)
		<if test="ct != null and ct != ''">
		AND ct_id = #{ct}
		</if>
		<if test="bdNational != null and bdNational != ''">
		AND bd_national = #{bdNational}
		</if>
		<if test="searchKeyword != null and searchKeyword != ''">
	        <choose>
	            <when test="searchCondition == 0">
					AND	GRP_NAME LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
	            <when test="searchCondition == 1">
					AND	USR_NAME LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
	            <when test="searchCondition == 2">
					AND	BD_SUBJECT LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
				<!-- 20180528 검색기능 중에 내용 관련 부분 추가 -->
	            <when test="searchCondition == 3">
					AND	BD_CONTENT LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
			</choose>
		</if>	
	</select>
	
	<select id="Board.selectBoardListTotCnt" parameterType="boardSearchVO" resultType="int">
	/* Board.selectBoardListTotCnt */
		SELECT
			COUNT(*)
		FROM 
			h2_board_data
		WHERE bbs_id = #{bbsId}
		AND 
		(
			bd_view_auth = '0'
			<if test='pla1 == "PS00" and (pla2 == "5" or pla2 == "22" ) '> 
		OR
			bd_view_grp like concat('%', #{pla2}, '%') 
			</if>
		OR usr_idx = #{userid}
		)
		<if test="ct != null and ct != ''">
		AND ct_id = #{ct}
		</if>
		<if test="bdNational != null and bdNational != ''">
		AND bd_national = #{bdNational}
		</if>
		<if test="searchKeyword != null and searchKeyword != ''">
	        <choose>
	            <when test="searchCondition == 0">
					AND	GRP_NAME LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
	            <when test="searchCondition == 1">
					AND	USR_NAME LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
	            <when test="searchCondition == 2">
					AND	BD_SUBJECT LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
				<!-- 20180528 검색기능 중에 내용 관련 부분 추가 -->
	            <when test="searchCondition == 3">
					AND	BD_CONTENT LIKE CONCAT('%' , #{searchKeyword} , '%')
				</when>
			</choose>
		</if>	
	</select>
	
	<select id="Board.selectBoard" parameterType="boardVO" resultType="boardVO">
	/* Board.selectBoard */
		SELECT
			  bd_idx AS bdIdx
			, bd_yn AS bdYn
			, bd_idx_grp AS bdIdxGrp
			, bbs_id AS bbsId
			, ct_id AS ctId
			, bd_part AS bdPart
			, bd_subject AS bdSubject
			, bd_content AS bdContent
			, bd_files AS bdFiles
			, bd_fileNames AS bdFileNames
			, bd_imgs AS bdImgs
			, bd_imgNames AS bdImgNames
			, grp_idx AS grpIdx
			, grp_name AS grpName
			, usr_idx AS usrIdx
			, usr_name AS usrName
			, bd_ipaddr AS bdIpaddr
			, FROM_UNIXTIME(bd_regdate) AS bdRegdate
			, bd_view_auth AS bdViewAuth
			, bd_view_grp AS bdViewGrp
			, bd_class AS bdClass
			, bd_tel AS bdTel
			, bd_object AS bdObject
			, bd_national AS bdNational
			, bd_timelimit AS bdTimelimit
			, bd_etc AS bdEtc
			, bd_count AS bdCount
		FROM h2_board_data
		WHERE bd_idx = #{bdIdx}
	</select>
	
	<insert id="Board.insertBoard" parameterType="boardVO" useGeneratedKeys="true"  keyProperty="bdIdx">
	 <selectKey resultType="Integer" keyProperty="bdIdx" order="BEFORE">
        SELECT IFNULL(MAX(bd_idx) + 1, 1) FROM H2_BOARD_DATA        
    </selectKey>
    	/* Board.insertBoard */ 
		INSERT INTO H2_BOARD_DATA(
			  bd_idx
			, bd_yn
			, bd_idx_grp
			, bbs_id
			, ct_id
			, bd_part
			, bd_subject
			, bd_content
			, bd_files
			, bd_fileNames
			, bd_imgs
			, bd_imgNames
			, grp_idx
			, grp_name
			, usr_idx
			, usr_name
			, bd_ipaddr
			, bd_regdate
			, bd_view_auth
			, bd_view_grp
			, bd_class
			, bd_tel
			, bd_object
			, bd_national
			, bd_timelimit
			, bd_etc
			, bd_count
		)VALUES(
			  #{bdIdx}
			, #{bdYn}
			<choose>
				<when test="bbsId != null and 'joint'.equals(bbsId) and bdIdxGrp == 0 ">
			, #{bdIdx}
				</when>
				<when test="bbsId != null and 'law'.equals(bbsId) and bdIdxGrp == 0 ">
			, #{bdIdx}
				</when>
				<otherwise>
			, #{bdIdxGrp}
				</otherwise>
			</choose>
			, #{bbsId}
			, #{ctId}
			, #{bdPart}
			, #{bdSubject}
			, #{bdContent}
			, #{bdFiles}
			, #{bdFileNames}
			, #{bdImgs}
			, #{bdImgNames}
			, #{grpIdx}
			, #{grpName}
			, #{usrIdx}
			, #{usrName}
			, #{bdIpaddr}
			, unix_timestamp()
			, #{bdViewAuth}
			, #{bdViewGrp}
			, #{bdClass}
			, #{bdTel}
			, #{bdObject}
			, #{bdNational}
			, #{bdTimelimit}
			, #{bdEtc}
			, 0
			)
	</insert>
	
	<select id="Board.selectPrevSeq" parameterType="boardVO" resultType="int">
	<![CDATA[
	/* Board.selectPrevSeq */ 
		SELECT
			IFNULL(MIN(BD_IDX), 0) AS BD_IDX
		FROM
			H2_BOARD_DATA
		WHERE BBS_ID = #{bbsId}
		    AND (BD_IDX = BD_IDX_GRP OR BD_IDX_GRP = 0)
		    AND BD_IDX > #{bdIdx}
	]]>
	</select>
	
	<select id="Board.selectNextSeq" parameterType="boardVO" resultType="int">
	<![CDATA[
	/* Board.selectNextSeq */
		SELECT
			IFNULL(MAX(BD_IDX), 0) AS BD_IDX
		FROM
			H2_BOARD_DATA
		WHERE BBS_ID = #{bbsId}
		    AND (BD_IDX = BD_IDX_GRP OR BD_IDX_GRP = 0)
		    AND BD_IDX < #{bdIdx}
	]]>
	</select>
	
	<update id="Board.updateBoard" parameterType="boardVO">
	/* Board.updateBoard */
		UPDATE 
			H2_BOARD_DATA
		SET 
			  BD_SUBJECT = #{bdSubject}
			, BD_CONTENT = #{bdContent}
			, BD_FILES = #{bdFiles}
			, BD_FILENAMES = #{bdFileNames}
			, BD_IMGS = #{bdImgs}
			, BD_IMGNAMES = #{bdImgNames}
		WHERE 
			BD_IDX = #{bdIdx}
	</update>
	
	<update id="Board.hitBoard" parameterType="boardVO">
	<selectKey resultType="Integer" keyProperty="bdCount" order="BEFORE">
        SELECT IFNULL(BD_COUNT, 0) + 1 FROM H2_BOARD_DATA WHERE  BD_IDX = #{bdIdx}
    </selectKey>
    /* Board.hitBoard */ 
		UPDATE 
			H2_BOARD_DATA
		SET 
			BD_COUNT = #{bdCount}
		WHERE 
			BD_IDX = #{bdIdx}
	</update>
	
	<delete id="Board.deleteBoard" parameterType="boardVO">
	<![CDATA[
	/* Board.deleteBoard */ 
		DELETE 
		FROM
			H2_BOARD_DATA
		WHERE 
			BD_IDX = #{bdIdx}
	]]>
	</delete>
	
	<select id="Board.searchBoardList" parameterType="searchVO" resultType="egovMap">
	<![CDATA[
	SELECT
		bd_idx as bdIdx
		, bbs_id as bbsId
		, replace(bd_subject, #{searchKeyWord},'<span class="clr_red">${searchKeyWord}</span>') as bd_subject
		, replace(substring(bd_content,instr(bd_content, #{searchKeyWord}),100),#{searchKeyWord},'<span class="clr_red">${searchKeyWord}</span>') bd_content
		, replace(bd_fileNames,#{searchKeyWord},'<span class="clr_red">${searchKeyWord}</span>') bd_filenames
		, USR_NAME usrName
		, FROM_UNIXTIME(bd_regdate) AS bdRegdate
	FROM H2_BOARD_DATA
	WHERE BBS_ID = #{bbsId}
	]]>
	AND 
		(
		bd_view_auth = '0'
		<if test='pla1 == "PS00" and (pla2 == "5" or pla2 == "22" ) '> 
	OR
		bd_view_grp like concat('%', #{pla2}, '%') 
		</if>
	OR usr_idx = #{userid}
	)
	<if test=" 'subject'.equals(searchType)  ">
		AND BD_SUBJECT LIKE CONCAT('%',#{searchKeyWord} ,'%');
	</if>
	<if test=" 'content'.equals(searchType)  ">
		AND BD_CONTENT LIKE CONCAT('%',#{searchKeyWord} ,'%');
	</if>
	<if test=" 'file'.equals(searchType)  ">
		AND BD_FILENAMES LIKE CONCAT('%',#{searchKeyWord} ,'%');
	</if>
	<if test=" 'name'.equals(searchType)  ">
		AND USR_NAME LIKE CONCAT('%',#{searchKeyWord} ,'%');
	</if>
	</select>
	
	<select id="Board.searchBoardCnt" parameterType="searchVO" resultType="int">
	SELECT
		COUNT(1) AS CNT
	FROM H2_BOARD_DATA
	WHERE BBS_ID = #{bbsId}
	AND 
		(
		bd_view_auth = '0'
		<if test='pla1 == "PS00" and (pla2 == "5" or pla2 == "22" ) '> 
	OR
		bd_view_grp like concat('%', #{pla2}, '%') 
		</if>
	OR usr_idx = #{userid}
	)
	<if test=" 'subject'.equals(searchType)  ">
		AND BD_SUBJECT LIKE CONCAT('%',#{searchKeyWord} ,'%');
	</if>
	<if test=" 'content'.equals(searchType)  ">
		AND BD_CONTENT LIKE CONCAT('%',#{searchKeyWord} ,'%');
	</if>
	<if test=" 'file'.equals(searchType)  ">
		AND BD_FILENAMES LIKE CONCAT('%',#{searchKeyWord} ,'%');
	</if>
	<if test=" 'name'.equals(searchType)  ">
		AND USR_NAME LIKE CONCAT('%',#{searchKeyWord} ,'%');
	</if>
	</select>
	
</mapper>
