<?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="com.style24.front.biz.dao.TsfNoticeDao">

	<!-- 페이징을 위한 select절 상단 -->
	<sql id="selectForPagingHeader">
		SELECT *
		FROM   (
	</sql>

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>
	
	<!-- 공지사항 전체 건수 조회 -->
	<select id="getNoticeTotalCount" parameterType="Notice" resultType="int">
		/* TsfNotice.getNoticeTotalCount */
		SELECT COUNT(*)
		FROM   TB_NOTICE A
		WHERE  NOTICE_TYPE = 'G047_10' /*사이트공지*/
		AND    USE_YN = 'Y'
		AND    CURRENT_DATE() BETWEEN NOTICE_STDT AND NOTICE_EDDT
		AND    EXISTS (SELECT 1
		               FROM   TB_NOTICE_RECEIVER
		               WHERE  NOTICE_SQ = A.NOTICE_SQ
		               AND    RECEIVER_ID = #{siteCd}
		              )
	</select>
	
	<!-- 공지사항 목록 -->
	<select id="getNoticeList" parameterType="Notice" resultType="Notice">
		/* TsfNotice.getNoticeList */
		<include refid="selectForPagingHeader"/>
		SELECT NOTICE_SQ                                     /*공지사항일련번호*/
		     , NOTICE_TITLE                                  /*공지제목*/
		     , NOTICE_CONTENT                                /*공지내용*/
		     , URGENT_YN                                     /*긴급여부*/
		     , READ_CNT                                      /*조회수*/
		     , DATE_FORMAT(REG_DT,'%Y%m%d')    AS REG_DT     /*등록일자*/
		     , (SELECT COUNT(1)
		        FROM   TB_NOTICE_FILE
		        WHERE  NOTICE_SQ = A.NOTICE_SQ
		       )                                 AS FILE_CNT /*첨부파일건수*/
		     , RANK() OVER(ORDER BY URGENT_YN DESC
		                          , REG_DT DESC) AS NUMB
		FROM   TB_NOTICE A
		WHERE  NOTICE_TYPE = 'G047_10' /*사이트공지*/
		AND    USE_YN =  'Y'
		AND    CURRENT_DATE() BETWEEN NOTICE_STDT AND NOTICE_EDDT
		AND    EXISTS (SELECT 1
		               FROM   TB_NOTICE_RECEIVER
		               WHERE  NOTICE_SQ = A.NOTICE_SQ
		               AND    RECEIVER_ID = #{siteCd}
		              )
		<include refid="selectForPagingFooter"/>
	</select>

	<!-- 공지사항 파일 목록 -->
	<select id="getNoticeFileList" parameterType="Integer" resultType="Notice">
		/* TsfNotice.getNoticeFileList */
		SELECT NOTICE_SQ
		     , SEQ
		     , ORG_FILE_NM
		     , SYS_FILE_NM
		FROM   TB_NOTICE_FILE
		WHERE  NOTICE_SQ = #{noticeSq}
	</select>
	
	<!-- 공지사항 조회건수 Update -->
	<update id="updateNoticeReadCount" parameterType="Integer">
		/* TsfNotice.updateNoticeReadCount */
		UPDATE TB_NOTICE
		SET    READ_CNT = READ_CNT + 1
		WHERE  NOTICE_SQ = #{noticeSq}
	</update>

</mapper>