<?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="kr.co.uplus.ez.api.homeMgt.HomeMgtMapper">

	<!-- 공지사항 개수 조회 -->
	<select id="selectNoticeTotalCnt"
		parameterType="kr.co.uplus.ez.api.homeMgt.dto.NoticeListReqDto"
		resultType="int">
		/* homeMgt-mapper.xml(selectNoticeTotalCnt) */
		SELECT
		COUNT(*)
		FROM
		hubez_common.EZ_NTBBS N1
		WHERE 1 = 1
		<include refid="ListCondition"></include>
	</select>
	
	<!-- 공지사항 조회 -->
	<select id="selectNoticeList"
		parameterType="kr.co.uplus.ez.api.homeMgt.dto.NoticeListReqDto"
		resultType="kr.co.uplus.ez.api.homeMgt.dto.NoticeDto">
	/* homeMgt-mapper.xml(selectNoticeList) */
	SELECT
		@ROWNUM := @ROWNUM + 1 AS NO,
		NT_NO,
		CTG_CD,
		CASE WHEN CTG_CD = '06' THEN '팝업공지'
		ELSE
			(
				SELECT DTL_CD_NM
				FROM hubez_common.EZ_CD_DTL
				WHERE DTL_CD = N1.CTG_CD
				AND GRP_CD = 'NTBBS_CTG_CD' AND USE_YN = 'Y'
			)
		END AS CTG_CD_NM,
		TITLE,
		EMG_YN,
		NT_SBST,
		REGR,
		USE_YN,
		RETV_CNT,
		DATE_FORMAT(NT_STRT_DT, '%Y-%m-%d') AS ntStrtDt,
		DATE_FORMAT(NT_END_DT, '%Y-%m-%d') AS ntEndDt,
		REG_ID,
		DATE_FORMAT(REG_DT, '%Y-%m-%d') AS regDt,
		CHG_ID,
		CHG_DT,
		(
			SELECT 
				GROUP_CONCAT(FILE_NO  SEPARATOR ',')  
			FROM hubez_common.EZ_BBS_FATC 
			WHERE BBS_NO = N1.NT_NO 
			AND BBS_DIV_CD = '02'
		) AS FILE_NO,
		(
			SELECT 
				GROUP_CONCAT(FILE_PATH  SEPARATOR ',')  
			FROM hubez_common.EZ_BBS_FATC 
			WHERE BBS_NO = N1.NT_NO 
			AND BBS_DIV_CD = '02'
		) AS FILE_PATH,
		(
			SELECT 
				GROUP_CONCAT(FILE_NM  SEPARATOR ',')  
			FROM hubez_common.EZ_BBS_FATC 
			WHERE BBS_NO = N1.NT_NO 
			AND BBS_DIV_CD = '02'
		) AS FILE_NM,
		(
			SELECT 
				GROUP_CONCAT(FILE_TITLE  SEPARATOR ',')  
			FROM hubez_common.EZ_BBS_FATC 
			WHERE BBS_NO = N1.NT_NO 
			AND BBS_DIV_CD = '02'
		) AS FILE_TITLE,
		(SELECT COUNT(*) FROM hubez_common.EZ_BBS_FATC WHERE BBS_NO = N1.NT_NO AND BBS_DIV_CD = '02') AS FILE_COUNT,
		CASE
		WHEN 
			(SELECT COUNT(*) FROM hubez_common.EZ_BBS_FATC WHERE BBS_NO = N1.NT_NO AND BBS_DIV_CD = '02') > 0 THEN "Y"
		ELSE 
			"N"
		END AS FILE_YN
	FROM
	hubez_common.EZ_NTBBS N1 , ( SELECT @ROWNUM := #{page} ) AS R
	WHERE 1=1
	<include refid="ListCondition"></include>
	ORDER BY N1.REG_DT DESC
 	LIMIT #{page}, #{pagePerRows} 
	</select>
	
	<!-- 공지사항 채번 -->
	<select id="selectNoticeNumber"
		parameterType="kr.co.uplus.ez.api.homeMgt.dto.NoticeListReqDto"
		resultType="int">
	/* homeMgt-mapper.xml(selectNoticeList) */
	SELECT
		NT_NO,
	FROM
	hubez_common.EZ_NTBBS N1
	WHERE 1=1
	<include refid="ListCondition"></include>
	ORDER BY N1.REG_DT DESC
 	LIMIT #{page}, #{pagePerRows} 
	</select>
	
	<!-- 공지사항 등록 -->
	<insert id="insertNotice" parameterType="kr.co.uplus.ez.api.homeMgt.dto.InsertNoticeReqDto">
		/* homeMgt-mapper.xml (insertNotice) */
		INSERT INTO 
			hubez_common.EZ_NTBBS(
			CTG_CD, 
			EMG_YN, 
			TITLE, 
			NT_SBST, 
			REGR, 
			USE_YN, 
			RETV_CNT,
			<if test="ctgCd == '06'">
			NT_STRT_DT,
			NT_END_DT,
			</if> 
			REG_ID, 
			REG_DT, 
			CHG_ID, 
			CHG_DT
		)VALUES(
			#{ctgCd},
			#{emgYn},
			#{title},
			#{ntSbst},
			#{regr},
			#{useYn},
			0,
			<if test="ctgCd == '06'">
			#{ntStrtDt},
			#{ntEndDt},
			</if> 
			#{regId},
			NOW(),
			#{chgId},
			NOW()
		)
		
	</insert>
	
	<!-- 공지사항 채번 -->
	<select id="insertNoticeNtNo" resultType="int">
		/* homeMgt-mapper.xml (insertNoticeNtNo) */
		SELECT LAST_INSERT_ID()
	</select>
	
	<!-- 공지사항 파일 등록 -->
	<insert id="insertNoticeFile" parameterType="kr.co.uplus.ez.api.homeMgt.dto.InsertNoticeFileDto">
	/* homeMgt-mapper.xml (insertNoticeFile) */
		INSERT INTO 
			hubez_common.EZ_BBS_FATC (
				BBS_DIV_CD,
				BBS_NO,
				FILE_NO,
				FILE_TITLE,
				FILE_NM,
				FILE_SIZE,
				FILE_PATH,
				DOWNL_CNT,
				REG_ID,
				REG_DT,
				CHG_ID,
				CHG_DT
				)
			VALUES(
				#{bbsDivCd}, 
				#{bbsNo}, 
				#{fileNo}, 
				#{fileTitle}, 
				#{fileNm}, 
				#{fileSize}, 
				#{filePath}, 
				0, 
				#{regId}, 
				NOW(), 
				#{chgId}, 
				NOW()
			)
	</insert>
	
	<!-- 공지사항 정보 변경 -->
	<update id="updateNotice" parameterType="kr.co.uplus.ez.api.homeMgt.dto.UpdateNoticeReqDto">
		/* homeMgt-mapper.xml (updateNotice) */
		UPDATE hubez_common.EZ_NTBBS
			SET 
				CTG_CD = #{ctgCd}
				,EMG_YN = #{emgYn}
				,USE_YN = #{useYn}
				,TITLE = #{title}
				,NT_SBST = #{ntSbst}
				<if test="ctgCd == '06'">
				,NT_STRT_DT = #{ntStrtDt}
				,NT_END_DT = #{ntEndDt}
				</if> 
				,CHG_DT = NOW()
				,CHG_ID = #{chgId}						
			WHERE
				NT_NO = #{ntNo}	
	</update>
	
	<!-- 공지사항 첨부파일 번호 최근값 가져오기 -->
	<select id="updateNoticeFileMaxNum" parameterType="kr.co.uplus.ez.api.homeMgt.dto.UpdateNoticeReqDto" resultType="int">
	/* homeMgt-mapper.xml (updateNoticeFileMaxNum) */
		SELECT 
			IFNULL( MAX(FILE_NO), 0) + 1 
		FROM hubez_common.EZ_BBS_FATC 
		WHERE BBS_NO = #{ntNo}
	</select>
	
	<!-- 공지사항 첨부파일 삭제 -->
	<delete id="delNoticeFile" parameterType="kr.co.uplus.ez.api.homeMgt.dto.DelNoticeReqDto">
		/* homeMgt-mapper.xml(delNoticeFile) */
		DELETE FROM hubez_common.EZ_BBS_FATC
		WHERE BBS_NO = #{ntNo}
		AND FILE_NO = #{fileNo}
		AND BBS_DIV_CD = '02'
	</delete>
	
	<!-- 공지사항 삭제 -->
	<delete id="deleteNotice" parameterType="kr.co.uplus.ez.api.homeMgt.dto.DeleteNoticeReqDto">
		/* homeMgt-mapper.xml(deleteNotice) */
		DELETE
		FROM
			hubez_common.EZ_NTBBS
		<where>
			NT_NO IN
			<foreach collection="list" item="item" index="i" open="("
				separator="," close=")">
				#{item.ntNo}
			</foreach>
		</where>
	</delete>
	
	
	<!-- faq 개수 조회 -->
	<select id="selectFaqTotalCnt"
		parameterType="kr.co.uplus.ez.api.homeMgt.dto.FaqListReqDto"
		resultType="int">
		/* homeMgt-mapper.xml(selectFaqTotalCnt) */
		SELECT
		COUNT(*)
		FROM
		hubez_common.EZ_FAQ N1
		WHERE 1 = 1
		<include refid="ListCondition"></include>
	</select>
	
	<!-- faq 조회 -->
	<select id="selectFaqList" parameterType="kr.co.uplus.ez.api.homeMgt.dto.FaqListReqDto"
		resultType="kr.co.uplus.ez.api.homeMgt.dto.FaqDto">
		/* homeMgt-mapper.xml(selectFaqList) */
		SELECT 
			@ROWNUM := @ROWNUM + 1 AS NO,
			FAQ_NO, 
			CTG_CD,
			(
				SELECT DTL_CD_NM
				FROM hubez_common.EZ_CD_DTL
				WHERE DTL_CD = N1.CTG_CD
				AND GRP_CD = 'FAQ_CTG_CD' AND USE_YN = 'Y'
			) AS CTG_CD_NM,
			TITLE, 
			REPLACE(FAQ_SBST,<![CDATA['<br />']]>, '\n') AS FAQ_SBST,
			USE_YN, 
			RETV_CNT, 
			REG_ID, 
			DATE_FORMAT(REG_DT, '%Y-%m-%d') AS REG_DT, 
			CHG_ID, 
			CHG_DT
		FROM hubez_common.EZ_FAQ AS N1 , ( SELECT @ROWNUM := #{page} ) AS R
		WHERE 1=1
			<include refid="ListCondition"></include>
			ORDER BY N1.REG_DT DESC
		 	LIMIT #{page}, #{pagePerRows} 
	</select>
	
	
	<!-- FAQ 채번 -->
	<select id="selectFaqNumber"
		parameterType="kr.co.uplus.ez.api.homeMgt.dto.FaqListReqDto"
		resultType="int">
	/* homeMgt-mapper.xml(selectFaqList) */
	SELECT
		FAQ_NO,
	FROM
	hubez_common.EZ_FAQ N1
	WHERE 1=1
	<include refid="ListCondition"></include>
	ORDER BY N1.REG_DT DESC
 	LIMIT #{page}, #{pagePerRows} 
	</select>
	
	<!-- FAQ 등록 -->
	<insert id="insertFaq" parameterType="kr.co.uplus.ez.api.homeMgt.dto.InsertFaqReqDto">
		/* homeMgt-mapper.xml (insertFaq) */
			INSERT INTO
				hubez_common.EZ_FAQ(
					CTG_CD,
					TITLE,
					FAQ_SBST,
					USE_YN,
					RETV_CNT,
					REG_ID,
					REG_DT,
					CHG_ID,
					CHG_DT
				)VALUES(
					#{ctgCd},
					#{title},
					#{faqSbst},
					#{useYn},
					0,
					#{regId},
					NOW(),
					#{chgId},
					NOW()
				);	
	</insert>
	
	<!-- FAQ 변경 -->
	<update id="updateFaq" parameterType="kr.co.uplus.ez.api.homeMgt.dto.UpdateFaqReqDto">
		/* homeMgt-mapper.xml (updateFaq) */
		UPDATE hubez_common.EZ_FAQ
			SET 
				CTG_CD = #{ctgCd}
				,USE_YN = #{useYn}
				,TITLE = #{title}
				,FAQ_SBST = #{faqSbst}
				,CHG_DT = NOW()
				,CHG_ID = #{chgId}						
			WHERE
				FAQ_NO = #{faqNo}	
	</update>
	
	<!-- 공지사항 삭제 -->
	<delete id="deleteFaq" parameterType="kr.co.uplus.ez.api.homeMgt.dto.DeleteFaqReqDto">
		/* homeMgt-mapper.xml(deleteFaq) */
		DELETE
		FROM
			hubez_common.EZ_FAQ
		<where>
			FAQ_NO IN
			<foreach collection="list" item="item" index="i" open="("
				separator="," close=")">
				#{item.faqNo}
			</foreach>
		</where>
	</delete>
	

	<sql id="ListCondition">
		<if test="searchType1 != null and searchType1 != ''">
			AND N1.CTG_CD = #{searchType1}
		</if>
		<if test="searchText1 != null and searchText1 != ''">
			AND N1.TITLE LIKE CONCAT('%', #{searchText1}, '%')
		</if>
	</sql>

</mapper>