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

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

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		<choose>
			<when test="pageable != null">
			    ) ORIGINAL
			WHERE RNUM BETWEEN  #{pageable.startRow} AND #{pageable.endRow}
			</when>
			<otherwise>
			    ) ORIGINAL
			</otherwise>
		</choose>
	</sql>
	
	<sql id="selectForPagingFooter2">
		<choose>
			<when test="pageable2 != null">
			    ) ORIGINAL
			WHERE RNUM BETWEEN  #{pageable2.startRow} AND #{pageable2.endRow}
			</when>
			<otherwise>
			    ) ORIGINAL
			</otherwise>
		</choose>
	</sql>
	
	<!-- 기획전 카테고리 목록 -->
	<select id="getPlanningCategoryList" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getPlanningCategoryList */
		SELECT C.CATE1_NO AS CATE_NO            /*카테고리번호*/
		     , C.CATE1_NM AS CATE_NM /*카테고리명*/
		FROM TB_CATE1 C
		WHERE 1=1
		AND    C.SITE_CD = #{siteCd}
		AND    C.CATE_GB = 'G032_101' /*BYITEM*/
		AND    C.CATE_TYPE = 'G031_10' /*상품카테고리*/
		AND    C.DISP_YN = 'Y' /*전시하는 카테고리*/
<!-- 		SELECT PL.CATE_NO             /*카테고리번호*/
		     , C1.CATE1_NM AS CATE_NM /*카테고리명*/
		FROM   TB_PLAN P
		     , TB_PLAN_CATE PL
		     , TB_CATE1 C1
		WHERE  P.PLAN_SQ = PL.PLAN_SQ
		AND    PL.CATE_NO = C1.CATE1_NO
		AND    P.PLAN_GB = 'P' /*기획전*/
		AND    P.SITE_CD = #{siteCd}
		<if test="frontGb != null and frontGb != ''">
		AND    P.FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		</if>
		AND    NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT
		AND    P.DEL_YN = 'N' /*삭제안된 기획전*/
		AND    P.OPEN_YN = 'Y' /*오픈된 기획전*/
		AND    PL.DISP_YN = 'Y' /*전시하는 기획전카테고리*/
		AND    C1.SITE_CD = #{siteCd}
		AND    C1.CATE_GB = 'G032_101' /*BYITEM*/
		AND    C1.CATE_TYPE = 'G031_10' /*상품카테고리*/
		AND    C1.DISP_YN = 'Y' /*전시하는 카테고리*/
		ORDER  BY C1.DISP_ORD -->
	</select>
	
	<select id="getPlanAllCount" resultType="int" parameterType="Plan">
		/* TsfPlanning.getPlanAllCount */
		SELECT COUNT(*) AS CNT
		FROM TB_PLAN P  
		WHERE 1=1
		   AND P.DEL_YN = 'N'
		   AND P.OPEN_YN = 'Y'
		   AND P.PLAN_GB = 'P'
		   <if test="cateNo != null and cateNo != ''">
		   AND P.PLAN_SQ IN (
		   						SELECT PLAN_SQ 
		   						FROM TB_PLAN_CATE 
		   						WHERE CATE_NO = #{cateNo}
		   					)
		   </if>
		    AND NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT
		    AND P.FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		    <!-- AND P.FRONT_GB IN (#{frontGb}, 'A') -->
	</select>
	
	<!-- 기획전 목록 -->
	<select id="getPlanningList" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getPlanningList */
		SELECT PLAN_SQ
		     , PLAN_NM
		     , DTL_TITLE1
		     , MAIN_IMG
		     , NEW_YN
		     , END_DAYS
		FROM   (
		        SELECT P.PLAN_SQ                                        /*기획전번호*/
		             , P.PLAN_NM                                        /*기획전명*/
		             , P.DTL_TITLE1                                     /*상세제목1*/
		             , CASE WHEN #{frontGb} IN ('A','M') THEN P.MAIN_MIMG
		                    ELSE P.MAIN_PIMG
		               END                                  AS MAIN_IMG /*메인이미지*/
		             , CASE WHEN TIMESTAMPDIFF(DAY,P.REG_DT,NOW()) <![CDATA[<=]]> 3 THEN
		                        'Y'
		                    ELSE
		                        'N'
		               END                                  AS NEW_YN   /*신규여부(등록일기준3일)*/
		             , TIMESTAMPDIFF(DAY,NOW(),P.DISP_EDDT) AS END_DAYS /*종료임박일수*/
		        FROM   TB_PLAN P
		        WHERE  P.PLAN_GB = 'P' /*기획전*/
		        AND    P.SITE_CD = #{siteCd}
		        AND    P.FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		        AND    NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT
		        AND    P.DEL_YN  = 'N' /*삭제안된 기획전*/
				AND    p.USE_YN  = 'Y' /*사용하는 기획전*/
		        AND    P.OPEN_YN = 'Y' /*오픈된 기획전*/
		        <if test="cateNo != null and cateNo != ''">
		        AND    P.PLAN_SQ IN (SELECT PLAN_SQ
		                             FROM   TB_PLAN_CATE
		                             WHERE  CATE_NO = #{cateNo}
		                             AND    DISP_YN = 'Y' /*전시하는 기획전카테고리*/
		                            )
		        </if>
		        <if test="brandGroupNo != null and brandGroupNo != ''"> <!-- 브랜드메인에서 사용 -->
		        AND    P.PLAN_SQ IN (SELECT PLAN_SQ
		                             FROM   TB_PLAN_BRAND
		                             WHERE  BRAND_GROUP_NO = #{brandGroupNo}
		                             AND    DISP_YN = 'Y' /*전시하는브랜드기획전*/
		                            )
		        </if>
		        <if test="exceptPlanSq != null and exceptPlanSq != ''"> <!-- 제외할기획전 -->
		        AND    P.PLAN_SQ != #{exceptPlanSq}
		        </if>
		       ) Z
		ORDER  BY NEW_YN DESC, END_DAYS
	</select>
	
	<!-- 기획전 상품 목록 -->
	<select id="getPlanningGoodsList" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getPlanningGoodsList */
		WITH TAB_PLAN_GOODS AS (
		    SELECT P.PLAN_SQ                                                /*기획전번호*/
		         , PD.CORNER_NM                                             /*코너명*/
		         , PG.DISP_ORD                                              /*상품노출순서*/
		         , CASE WHEN BG.DISP_NM_LANG = 'EN' THEN
		                    BG.BRAND_GROUP_ENM
		                ELSE
		                    BG.BRAND_GROUP_KNM
		           END                                    AS BRAND_GROUP_NM /*브랜드그룹명*/
		         , G.GOODS_CD                                               /*상품코드*/
		         , G.GOODS_NM                                               /*상품명*/
		         , G.GOODS_GB                                               /*상품구분*/
		         , G.FOREIGN_BUY_YN                                         /*해외구매대행여부*/
		         , G.PARALLEL_IMPORT_YN                                     /*병행수입여부*/
		         , G.ORDER_MADE_YN                                          /*주문제작여부*/
		         , G.GOODS_TNM                                              /*상품타이틀명*/
		         , G.MAIN_COLOR_CD                                          /*대표색상코드*/
		         , G.LIST_PRICE                                             /*정상가(최초판매가)*/
		         , G.CURR_PRICE                                             /*현재판매가*/
		         , G.GOODS_TYPE
		         , RANK() OVER(PARTITION BY P.PLAN_SQ
		                       ORDER BY PG.DISP_ORD
		                              , PG.GOODS_CD)      AS NUMB
		    FROM   TB_PLAN P
		         , TB_PLAN_DETAIL PD
		         , TB_PLAN_GOODS PG
		         , TB_GOODS G
		         , TB_GOODS_STOCK GS
		         , TB_BRAND B
		         , TB_BRAND_GROUP BG
		    WHERE  P.PLAN_SQ = PD.PLAN_SQ
		    AND    PD.PLAN_DTL_SQ = PG.PLAN_DTL_SQ
		    AND    PG.GOODS_CD = G.GOODS_CD
		    AND    G.GOODS_CD = GS.GOODS_CD
		    AND    G.BRAND_CD = B.BRAND_CD
		    AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		    AND    P.PLAN_SQ = #{planSq}
		    AND    P.PLAN_GB = 'P' /*기획전*/
		    AND    P.SITE_CD = #{siteCd}
		    <if test="frontGb != null and frontGb != ''">
		    AND    P.FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		    </if>
		    AND    NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT
		    AND    P.DEL_YN = 'N' /*삭제안된 기획전*/
		    AND    P.OPEN_YN = 'Y' /*오픈된 기획전*/
		    <if test="cateNo != null and cateNo != ''">
		    AND    P.PLAN_SQ IN (SELECT PLAN_SQ
		                         FROM   TB_PLAN_CATE
		                         WHERE  CATE_NO = #{cateNo}
		                         AND    DISP_YN = 'Y' /*전시하는 기획전카테고리*/
		                        )
		    </if>
		    <if test="brandGroupNo != null and brandGroupNo != ''"> <!-- 브랜드메인에서 사용 -->
		    AND    P.PLAN_SQ IN (SELECT PLAN_SQ
		                         FROM   TB_PLAN_BRAND
		                         WHERE  BRAND_GROUP_NO = #{brandGroupNo}
		                         AND    DISP_YN = 'Y' /*전시하는브랜드기획전*/
		                        )
		    </if>
	
		    AND    NOW() BETWEEN PD.DISP_STDT AND PD.DISP_EDDT
		    AND    PD.DISP_YN = 'Y' /*전시하는코너*/
		    AND    PG.DEL_YN = 'N' /*삭제안된상품*/
		    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT
		    AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
		    AND    GS.STOCK_QTY <![CDATA[>]]> 0 /*재고있는상품*/
		    AND    B.USE_YN = 'Y'
		    AND    BG.USE_YN = 'Y'
		)
		, TAB_GOODS_IMG AS (
		    /* 상품의 이미지 */
		    SELECT PG.GOODS_CD
		         , MAX(CASE WHEN GI.DEFAULT_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END)   AS SYS_IMG_NM
		         , MAX(CASE WHEN GI.MOUSEOVER_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END) AS SYS_IMG_NM2
		    FROM   TAB_PLAN_GOODS PG
		         , TB_GOODS_IMG GI
		    WHERE  PG.GOODS_CD = GI.GOODS_CD
		    AND    PG.MAIN_COLOR_CD = GI.COLOR_CD
		    AND    (GI.DEFAULT_IMG_YN = 'Y' OR GI.MOUSEOVER_IMG_YN = 'Y')
		    GROUP  BY PG.GOODS_CD
		)
	  , TAB_PLAN_GOODS2 AS (                                                                                                                            
		    SELECT P.PLAN_SQ                                                /*기획전번호*/                                                                  
		         , PC.LINK_URL 
		         , PC.LINK_OPEN_GB
		         , PC.DISP_ORD 
		         , PC.TITLE 
		         , PC.TMPL_TYPE 
		         , PC.PLAN_CONT_SQ 		         
		         , PCI.ITEM_VAL 
		         , PCI.REVIEW_DISP_EDDT 
		         , PCI.REVIEW_DISP_STDT 
		         , CASE WHEN BG.DISP_NM_LANG = 'EN' THEN
		                    BG.BRAND_GROUP_ENM
		                ELSE
		                    BG.BRAND_GROUP_KNM
		           END                                    AS BRAND_GROUP_NM /*브랜드그룹명*/
		         , G.GOODS_CD                                               /*상품코드*/
		         , G.GOODS_NM                                               /*상품명*/
		         , G.GOODS_GB                                               /*상품구분*/
		         , G.FOREIGN_BUY_YN                                         /*해외구매대행여부*/
		         , G.PARALLEL_IMPORT_YN                                     /*병행수입여부*/
		         , G.ORDER_MADE_YN                                          /*주문제작여부*/
		         , G.GOODS_TNM                                              /*상품타이틀명*/
		         , G.MAIN_COLOR_CD                                          /*대표색상코드*/
		         , G.LIST_PRICE                                             /*정상가(최초판매가)*/
		         , G.CURR_PRICE                                             /*현재판매가*/
		         , RANK() OVER(PARTITION BY P.PLAN_SQ
		                       ORDER BY PC.DISP_ORD
		                              , G.GOODS_CD)      AS NUMB
		    FROM   TB_PLAN P
		    	 , TB_PLAN_CONTENTS PC
		    	 , TB_PLAN_CONTENTS_ITEM PCI
		         , TB_GOODS G
		         , TB_GOODS_STOCK GS
		         , TB_BRAND B
		         , TB_BRAND_GROUP BG
		    WHERE  P.PLAN_SQ = PC.PLAN_SQ
		    AND    PC.PLAN_CONT_SQ = PCI.PLAN_CONT_SQ
		    AND    PCI.ITEM_VAL = G.GOODS_CD
		    AND    G.GOODS_CD = GS.GOODS_CD
		    AND    G.BRAND_CD = B.BRAND_CD
		    AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		    AND    P.PLAN_SQ =  #{planSq}
		    AND    P.PLAN_GB = 'P' /*기획전*/
		    AND    P.SITE_CD =  #{siteCd} /**P*/
		    <if test="frontGb != null and frontGb != ''">
		    AND    P.FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		    </if>
		    AND    NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT
		    AND    P.DEL_YN = 'N' /*삭제안된 기획전*/
		    AND    P.OPEN_YN = 'Y' /*오픈된 기획전*/
		    AND    PC.TMPL_TYPE IN ('G082_50','G082_51','G082_52')
		    AND    PC.DISP_YN = 'Y' /*전시하는템플릿*/
		    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		    AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
		    AND    GS.STOCK_QTY  <![CDATA[>]]>  0 /*재고있는상품*/
		    AND    B.USE_YN = 'Y'
		    AND    BG.USE_YN = 'Y'
		    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT
		), TAB_GOODS_IMG2 AS (
		    /* 상품의 이미지 */
		    SELECT PG.GOODS_CD
		         , MAX(CASE WHEN GI.DEFAULT_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END)   AS SYS_IMG_NM
		         , MAX(CASE WHEN GI.MOUSEOVER_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END) AS SYS_IMG_NM2
		    FROM   TAB_PLAN_GOODS2 PG
		         , TB_GOODS_IMG GI
		    WHERE  PG.GOODS_CD = GI.GOODS_CD
		    AND    PG.MAIN_COLOR_CD = GI.COLOR_CD
		    AND    (GI.DEFAULT_IMG_YN = 'Y' OR GI.MOUSEOVER_IMG_YN = 'Y')
		    GROUP  BY PG.GOODS_CD
		)
	   SELECT PG.PLAN_SQ																																	
		   --  , PG.CORNER_NM                                                                                                                               
		     , PG.DISP_ORD                                                                                                                                  
		     , PG.BRAND_GROUP_NM                                                                                                                            
		     , PG.GOODS_CD                                                                                                                                  
		     , FN_GET_GOODS_NM(PG.GOODS_NM,PG.GOODS_GB,PG.FOREIGN_BUY_YN,PG.PARALLEL_IMPORT_YN,PG.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/  
		     , FN_GET_BENEFIT_PRICE(#{frontGb},PG.GOODS_CD,PG.CURR_PRICE,#{custGb})                              AS CURR_PRICE    /*현재판매가*/
		     , PG.GOODS_NM                                                                                                                                  
		     , PG.GOODS_TNM                                                                                                                                 
		     , PG.MAIN_COLOR_CD                                                                                                                             
		     , PG.LIST_PRICE                                                                                                                                
		     , GI.SYS_IMG_NM                                                                                                                                
		     , GI.SYS_IMG_NM2 
		     <choose>
		         <when test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		         , IF(W.GOODS_CD IS NULL,'','likeit')                                                                AS LIKE_IT       /*위시리스트담긴상품*/
		         </when>
		         <otherwise>
		         , ''                                                                                                AS LIKE_IT       /*위시리스트담긴상품*/
		        </otherwise>
		   </choose>                       
		FROM   TAB_PLAN_GOODS2 PG
		INNER JOIN TAB_GOODS_IMG2 GI ON PG.GOODS_CD = GI.GOODS_CD
		<if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		LEFT OUTER JOIN TB_WISHLIST W ON PG.GOODS_CD = W.GOODS_CD
		                             AND W.CUST_NO = #{custNo}
		</if>
		WHERE  (SELECT COUNT(*) FROM TAB_PLAN_GOODS) = 0
		AND PG.NUMB <![CDATA[<=]]> #{maxRow}
		UNION ALL 
		   SELECT distinct PG.PLAN_SQ																																	
		        , PG.DISP_ORD                                                                                                                                  
		        , PG.BRAND_GROUP_NM                                                                                                                            
		        , PG.GOODS_CD                                                                                                                                  
		        , FN_GET_GOODS_NM(PG.GOODS_NM,PG.GOODS_GB,PG.FOREIGN_BUY_YN,PG.PARALLEL_IMPORT_YN,PG.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/       
		        , FN_GET_BENEFIT_PRICE(#{frontGb},PG.GOODS_CD,PG.CURR_PRICE,#{custGb})                              AS CURR_PRICE    /*현재판매가*/
		        , PG.GOODS_NM                                                                                                                                  
		        , PG.GOODS_TNM                                                                                                                                 
		        , PG.MAIN_COLOR_CD                                                                                                                             
		        , PG.LIST_PRICE                                                                                                                                
		        , GI.SYS_IMG_NM                                                                                                                                
		        , GI.SYS_IMG_NM2 
		        <choose>
		            <when test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		            , IF(W.GOODS_CD IS NULL,'','likeit')                                                                AS LIKE_IT       /*위시리스트담긴상품*/
		            </when>
		            <otherwise>
		            , ''                                                                                                AS LIKE_IT       /*위시리스트담긴상품*/
		            </otherwise>
		        </choose>
		FROM   TAB_PLAN_GOODS PG 
		INNER JOIN TAB_GOODS_IMG GI ON PG.GOODS_CD = GI.GOODS_CD 
		<if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		LEFT OUTER JOIN TB_WISHLIST W ON PG.GOODS_CD = W.GOODS_CD
		                             AND W.CUST_NO = #{custNo}
		</if>
		<if test="maxRow != null and maxRow > 0">
		WHERE PG.NUMB <![CDATA[<=]]> #{maxRow}
		</if>
	</select>
	
	<!-- 유효한 기획전 여부 조회 -->
	<select id="getAvailPlanningYn" parameterType="Plan" resultType="String">
		/* TsfPlanning.getAvailPlanningYn */
		SELECT CASE WHEN IFNULL((SELECT COUNT(*)
		                         FROM   TB_PLAN
		                         WHERE  PLAN_SQ = #{planSq}
		                         <if test='adminPreview != "Y"'>
		                          AND    NOW() BETWEEN DISP_STDT AND DISP_EDDT
		                          AND    USE_YN = 'Y'
		                          AND    DEL_YN = 'N'
		                         </if>
		                        ),0) > 0 THEN 'Y'
		            ELSE 'N'
		       END
		FROM   DUAL
	</select>
	
	<!-- 디바이스접근가능여부 조회 -->
	<select id="getDeviceAccessibleYn" parameterType="Plan" resultType="String">
		/* TsfPlanning.getDeviceAccessibleYn */
		SELECT CASE WHEN COUNT(*) > 0 THEN
		                'Y' /*접근가능*/
		            ELSE
		                'N' /*접근불가*/
		       END AS ACCESSIBLE_YN
		FROM   TB_PLAN
		WHERE  PLAN_SQ = #{planSq}
		AND    FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
	</select>
	
	<!-- 신규회원접근가능여부 조회 -->
	<select id="getNewCustomerAccessibleYn" parameterType="Plan" resultType="String">
		/* TsfPlanning.getNewCustomerAccessibleYn */
		SELECT CASE WHEN NEW_CUST_YN = 'N' THEN
		                'Y' /*접근가능*/
		            ELSE
		                CASE WHEN (SELECT JOIN_DT 
		                           FROM TB_CUSTOMER C
		                           WHERE C.CUST_NO = #{custNo}) BETWEEN CUST_JOIN_STDT AND CUST_JOIN_EDDT THEN
		                         'Y' /*접근가능*/
		                     ELSE
		                         'N' /*접근불가*/
		                END
		       END AS ACCESSIBLE_YN
		FROM   TB_PLAN
		WHERE  PLAN_SQ = #{planSq}
	</select>
	
	<!-- 고객구분접근가능여부 조회 -->
	<select id="getCustomerGubunAccessibleYn" parameterType="Plan" resultType="String">
		/* TsfPlanning.getCustomerGubunAccessibleYn */
		SELECT CASE WHEN COUNT(*) > 0 THEN
		                'Y' /*접근가능*/
		            ELSE
		                'N' /*접근불가*/
		       END AS ACCESSIBLE_YN
		FROM   TB_PLAN_CUST_DIVISION
		WHERE  PLAN_SQ = #{planSq}
		AND    CUST_GB = #{custGb}
	</select>
	
	<!-- 고객구분접근가능리스트 -->
	<select id="getCustomerGubunAccessibleList" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getCustomerGubunAccessibleList */
		SELECT CASE CUST_GB WHEN 'G100_10' THEN '일반회원'
		                    WHEN 'G100_20' THEN '임직원' END AS CUST_GB 
		FROM TB_PLAN_CUST_DIVISION 
		WHERE PLAN_SQ = #{planSq}
	</select>
	
	<!-- 고객등급접근가능리스트 -->
	<select id="getCustomerGradeAccessibleList" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getCustomerGradeAccessibleList */
		SELECT CASE CUST_GRADE WHEN 'G110_10' THEN 'VIP'
		                       WHEN 'G110_20' THEN 'GOLD'
		                       WHEN 'G110_30' THEN 'SILVER'
		                       WHEN 'G110_40' THEN 'BRONZE'
		                       WHEN 'G110_50' THEN 'WELCOME'
		                       WHEN 'G110_60' THEN '비회원'
		                       END AS CUST_GRADE
		FROM TB_PLAN_CUST_GRADE 
		WHERE PLAN_SQ = #{planSq}
	</select>
	
		<!-- 고객등급접근가능리스트 -->
	<select id="getFrontAccessibleList" parameterType="Plan" resultType="String">
		/* TsfPlanning.getFrontAccessibleList */
		SELECT CASE FRONT_GB WHEN 'P'     THEN 'PC웹'
		                     WHEN 'P|M'   THEN 'PC웹, 모바일웹'
		                     WHEN 'P|M|A' THEN 'PC웹, 모바일웹, 모바일앱'
		                     WHEN 'M'     THEN '모바일웹'
		                     WHEN 'M|A'   THEN '모바일웹, 모바일앱'
		                     WHEN 'A'     THEN '모바일앱'
		                     WHEN 'P|A'   THEN 'PC웹, 모바일앱'
		                     END AS FRONT_GB 
		FROM tb_plan  
		WHERE PLAN_SQ = #{planSq}
	</select>
	
		<!-- 비회원 접근 조회 -->
	<select id="getNonmemberGradeAccessible" parameterType="Plan" resultType="int">
		/* TsfPlanning.getCustomerGubunAccessibleYn */
		SELECT count(*)
		FROM TB_PLAN_CUST_GRADE 
		WHERE PLAN_SQ = #{planSq}
	</select>
	
	<!-- 고객등급접근가능여부 조회 -->
	<select id="getCustomerGradeAccessibleYn" parameterType="Plan" resultType="String">
		/* TsfPlanning.getCustomerGradeAccessibleYn */
		SELECT CASE WHEN COUNT(*) > 0 THEN
		                'Y' /*접근가능*/
		            ELSE
		                'N' /*접근불가*/
		       END AS ACCESSIBLE_YN
		FROM   TB_PLAN_CUST_GRADE
		WHERE  PLAN_SQ = #{planSq}
		AND    CUST_GRADE = #{custGrade}
	</select>
	
	<!-- 웹제휴채널접근가능여부 조회 -->
	<select id="getAfLinkAccessibleYn" parameterType="Plan" resultType="String">
		/* TsfPlanning.getAfLinkAccessibleYn */
		SELECT CASE WHEN IFNULL(SUM(CNT1),0) = 0 THEN
		                'Y' /*접근가능*/
		            ELSE
		                CASE WHEN SUM(CNT2) > 0 THEN
		                         'Y' /*접근가능*/
		                     ELSE
		                         'N' /*접근불가*/
		                END
		       END AS ACCESSIBLE_YN
		FROM   (
		        SELECT 1                                                                            AS CNT1 /*제휴채널전체건수*/
		             , CASE WHEN PAC.AF_LINK_CD = #{afLinkCd} AND AF.USE_YN = 'Y' THEN 1 ELSE 0 END AS CNT2 /*특정제휴채널건수*/
		        FROM   TB_PLAN_AF_CHANNEL PAC
		             , TB_AF_LINK AF
		        WHERE  PAC.AF_LINK_CD = AF.AF_LINK_CD
		        AND    PAC.PLAN_SQ =  #{planSq}
		       ) Z
	</select>
	
	<!-- 기획전 상세정보 -->
	<select id="getPlanDetailInfo" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getPlanDetailInfo*/
		SELECT A.PLAN_SQ
		     , A.PLAN_NM
		     , A.PLAN_GB
		     , A.SITE_CD
		     , A.FRONT_GB
		     , DATE_FORMAT(A.DISP_STDT, '%Y-%m-%d') AS DISP_STDT 
		     , DATE_FORMAT(A.DISP_EDDT, '%Y-%m-%d') AS DISP_EDDT
		     , A.MAIN_PIMG
		     , A.MAIN_MIMG
		     , A.DTL_PIMG
		     , A.DTL_MIMG
		     , A.DTL_TITLE1
		     , A.DTL_TITLE2
		     , A.REPLY_YN
		     , A.REPLY_LOC
		     , A.REPLY_IMG
		     , A.REPLY_TITLE1
		     , A.REPLY_TITLE2
		     , A.CORNER_NM_DISP_YN
		     , A.GOODS_LIMIT_YN
		     , A.GOODS_LIMIT_QTY
		     , A.DEL_YN
		     , A.OPEN_YN
		     , A.DISP_ORD
		     , A.READ_CNT
		     , A.DEV_URL
		     , A.BADGE_NM
		     , A.BADGE_FCOLOR
		     , A.BADGE_BCOLOR
		     , A.POLL_SQ
		     , A.REG_NO
		     , A.REG_DT
		     , A.UPD_NO
		     , A.UPD_DT
		FROM   TB_PLAN A
		WHERE  A.PLAN_SQ = #{planSq}
		<!-- AND    A.SITE_CD = #{siteCd} -->
		<if test='adminPreview != "Y"'> -- 어드민 미리보기
		AND    NOW() BETWEEN A.DISP_STDT AND A.DISP_EDDT
		</if>
		<if test="frontGb != null and frontGb != ''">
		AND    A.FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		</if>
	</select>
	
	<select id="getEventAllCount" resultType="int" parameterType="Plan">
		/* TsfPlanning.getEventAllCount */
		SELECT COUNT(*) AS CNT
		FROM TB_PLAN P  
		WHERE 1=1
		   AND P.DEL_YN = 'N'
		   AND P.PLAN_GB = 'E'
		   AND P.OPEN_YN = 'Y'
		    AND NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT
		    <!-- AND P.FRONT_GB IN (#{frontGb}, 'A') -->
	</select>
	
	<select id="getPlanEventList" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getPlanEventList*/
		SELECT PLAN_SQ
		     , PLAN_NM
		     , DTL_TITLE1
		     , MAIN_IMG
		     , NEW_YN
		     , END_DAYS
		     , DISP_STDT
		     , DISP_EDDT
		FROM   (
		        SELECT P.PLAN_SQ                                        /*기획전번호*/
		             , P.PLAN_NM                                        /*기획전명*/
		             , P.DTL_TITLE1                                     /*상세제목1*/
		             , SUBSTRING_INDEX(P.DISP_STDT , ' ', +1) AS DISP_STDT
		             , SUBSTRING_INDEX(P.DISP_EDDT , ' ', +1) AS DISP_EDDT
		             , CASE WHEN #{frontGb} IN ('A','M') THEN P.MAIN_MIMG
		                    ELSE P.MAIN_PIMG
		               END                                  AS MAIN_IMG /*메인이미지*/
		             , CASE WHEN TIMESTAMPDIFF(DAY,P.REG_DT,NOW()) <![CDATA[<=]]> 3 THEN
		                        'Y'
		                    ELSE
		                        'N'
		               END                                  AS NEW_YN   /*신규여부(등록일기준3일)*/
		             , TIMESTAMPDIFF(DAY,NOW(),P.DISP_EDDT) AS END_DAYS /*종료임박일수*/
		        FROM   TB_PLAN P
		        WHERE  P.PLAN_GB IN ('E','C') /*이벤트,출석체크*/
		        AND    P.SITE_CD = #{siteCd}
		       <!--  <if test="frontGb != null and frontGb != ''">
		        AND    P.FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		        </if> -->
		        AND    NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT
		        AND    P.DEL_YN  = 'N' /*삭제안된 기획전*/
		   		AND    P.USE_YN  = 'Y' /*사용하는 기획전*/
		        AND    P.OPEN_YN = 'Y' /*오픈된 기획전*/
		        <if test="exceptPlanSq != null and exceptPlanSq != ''"> <!-- 제외할이벤트 -->
		        AND    P.PLAN_SQ != #{exceptPlanSq}
		        </if>
		       ) Z
		ORDER  BY NEW_YN DESC, END_DAYS
	</select>
	
	<select id="accessCustGrade"  parameterType="Plan" resultType="Plan">
		/* TsfPlanning.accessCustGrade*/
		SELECT CUST_GRADE
			  ,(CASE CUST_GRADE WHEN 'G110_10'THEN 'VIP'
			                    WHEN 'G110_20'THEN 'GOLD'
			                    WHEN 'G110_30'THEN 'SILVER'
			                    WHEN 'G110_40'THEN 'BRONZE'
			                    WHEN 'G110_50'THEN 'WELCOME'END) AS CUST_GRADE_NAME
		FROM TB_PLAN TP INNER JOIN tb_plan_cust_grade g ON TP.PLAN_SQ = G.PLAN_SQ 
		WHERE TP.PLAN_SQ = #{planSq}
	</select>
	
	<select id="getPlanFsrcInfo" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getPlanFsrcInfo*/
		SELECT DISP_YN 
		      ,FRONT_GB 
		      ,FSRC_MOB 
		      ,FSRC_PC 
		      ,PLAN_SQ 
		FROM TB_PLAN_FSRC PF 
		WHERE 1=1 
		  AND PLAN_SQ = #{planSq}
		  AND FRONT_GB = #{frontGb}
		  AND DISP_YN = 'Y'
	</select>
	
	<select id="templateDispOrdInfo" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.templateDispOrdInfo*/
		SELECT TMPL_TYPE 
		     , DISP_ORD
		     , PLAN_CONT_SQ
		     , TITLE 
		     , LINK_URL
		     , LINK_OPEN_GB
		     , DISP_YN 
		FROM TB_PLAN_CONTENTS TPC 
		WHERE 1=1 
			AND TPC.PLAN_SQ = #{planSq}
		    AND TPC.DISP_YN = 'Y'
		ORDER BY DISP_ORD 
	</select>
	
	<select id="getPlanReviewInfo" parameterType="Review" resultType="Review">
		/* TsfPlanning.getPlanReviewInfo*/
		SELECT F2.*
		FROM 
		(
			SELECT    F.REVIEW_SQ
					 ,F.GOODS_CD
					 ,F.REVIEW_GB
					 ,F.CUST_NO
					 ,F.ORD_NO
					 ,F.ORD_DTL_NO
					 ,F.REVIEW_CONTENT
					 ,F.SCORE
					 ,F.HEIGHT
					 ,F.WEIGHT
					 ,F.SCORE_SIZE
					 ,F.SCORE_COLOR
					 ,F.SCORE_FIT
					 ,F.SCORE_THICK
					 ,F.SCORE_WEIGHT
					 ,F.SCORE_BALL
					 ,F.GIVE_DUE_PNT
					 ,F.PNT_GIVE_STAT
					 ,F.BEST_YN
					 ,F.GIVE_DUE_BPNT
					 ,F.BPNT_GIVE_YN
					 ,F.DISP_YN
					 ,F.DEL_YN
					 ,F.CONFIRM_YN
					 ,F.CONFIRM_UNO
					 ,F.CONFIRM_DT
					 ,DATE_FORMAT( F.REG_DT, '%Y-%m-%d') AS REG_DT 
					 ,DATE_FORMAT( F.UPD_DT, '%Y-%m-%d') AS UPD_DT  
					 ,F.BRAND_CD
					 ,F.ITEMKIND_CD
					 ,F.GOODS_NM
					 ,F.GOODS_TNM
					 ,F.GOODS_SNM
					 ,F.GOODS_SNM1
					 ,F.MAIN_COLOR_CD
					 ,F.GOODS_NUM
					 ,F.GOODS_TYPE
					 ,F.TAG_PRICE
					 ,F.LIST_PRICE
					 ,FN_GET_BENEFIT_PRICE(#{frontGb},F.GOODS_CD,F.CURR_PRICE,#{custGb})                              AS CURR_PRICE
					 ,F.CURR_BPRICE
					 ,F.PRICE_UPD_DT
					 ,F.COST_PRICE
					 ,F.DC_RATE
					 ,F.GOODS_STAT
					 ,F.SELF_MALL_YN
					 ,F.GOODS_GB
					 ,F.DISTRIBUTION_GB
					 ,F.SELF_GOODS_YN
					 ,F.SUPPLY_COMP_CD
					 ,F.SUPPLY_GOODS_CD
					 ,F.PNT_PRATE
					 ,F.PNT_MRATE
				  ,(SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG  WHERE GOODS_CD =F.GOODS_CD AND COLOR_CD = F.MAIN_COLOR_CD AND DEFAULT_IMG_YN = 'Y' ) AS SYS_IMG_NM
				  ,(SELECT MAX(SYS_FILE_NM) FROM TB_REVIEW_ATTACH WHERE REVIEW_SQ =F.REVIEW_SQ ORDER BY FILE_GB DESC, REVIEW_SQ LIMIT 1) AS REVIEW_SYS_IMG
				  ,(SELECT MAX(KMC_KEY) FROM TB_REVIEW_ATTACH WHERE REVIEW_SQ =F.REVIEW_SQ ORDER BY FILE_GB DESC, REVIEW_SQ LIMIT 1) AS KMC_KEY
				  ,(SELECT MAX(FILE_GB) FROM TB_REVIEW_ATTACH WHERE REVIEW_SQ =F.REVIEW_SQ ORDER BY FILE_GB DESC , REVIEW_SQ LIMIT 1) AS FILE_GB  
				  , CASE IFNULL((SELECT COUNT(SYS_IMG_NM) FROM TB_GOODS_IMG  WHERE GOODS_CD = F.GOODS_CD AND COLOR_CD = F.MAIN_COLOR_CD AND DEFAULT_IMG_YN = 'Y' ),0) 
			            WHEN 0 THEN 'N'
			            ELSE 'Y' END AS GOODS_IMAGE_YN
			      ,(SELECT CUST_ID FROM TB_CUSTOMER WHERE CUST_NO = F.CUST_NO) AS CUST_ID
				  , ROW_NUMBER() OVER (PARTITION BY F.GOODS_CD ORDER BY F.BEST_YN DESC, F.SCORE DESC, F.REVIEW_GB, F.REG_DT) AS RANKNO
				  , 'G082_10' AS TMPL_TYPE
				  ,(SELECT PC.TITLE FROM TB_PLAN_CONTENTS PC WHERE PC.PLAN_SQ = #{planSq} AND PC.TMPL_TYPE = 'G082_10') AS TITLE
			FROM 
			(
				SELECT R.REVIEW_SQ
					 ,R.GOODS_CD
					 ,R.REVIEW_GB
					 ,R.CUST_NO
					 ,R.ORD_NO
					 ,R.ORD_DTL_NO
					 ,R.REVIEW_TITLE
					 ,R.REVIEW_CONTENT
					 ,R.SCORE
					 ,R.HEIGHT
					 ,R.WEIGHT
					 ,R.SCORE_SIZE
					 ,R.SCORE_COLOR
					 ,R.SCORE_FIT
					 ,R.SCORE_THICK
					 ,R.SCORE_WEIGHT
					 ,R.SCORE_BALL
					 ,R.GIVE_DUE_PNT
					 ,R.PNT_GIVE_STAT
					 ,R.BEST_YN
					 ,R.GIVE_DUE_BPNT
					 ,R.BPNT_GIVE_YN
					 ,R.DISP_YN
					 ,R.DEL_YN
					 ,R.CONFIRM_YN
					 ,R.CONFIRM_UNO
					 ,R.CONFIRM_DT
					 ,R.REG_DT 
					 ,R.UPD_DT 
					 ,G.BRAND_CD
					 ,G.ITEMKIND_CD
					 ,G.GOODS_NM
					 ,G.GOODS_TNM
					 ,G.GOODS_SNM
					 ,G.GOODS_SNM1
					 ,G.MAIN_COLOR_CD
					 ,G.GOODS_NUM
					 ,G.GOODS_TYPE
					 ,G.TAG_PRICE
					 ,G.LIST_PRICE
					 ,FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD,G.CURR_PRICE,#{custGb})                              AS CURR_PRICE
					 ,G.CURR_BPRICE
					 ,G.PRICE_UPD_DT
					 ,G.COST_PRICE
					 ,G.DC_RATE
					 ,G.GOODS_STAT
					 ,G.SELF_MALL_YN
					 ,G.GOODS_GB
					 ,G.DISTRIBUTION_GB
					 ,G.SELF_GOODS_YN
					 ,G.SUPPLY_COMP_CD
					 ,G.SUPPLY_GOODS_CD
					 ,G.PNT_PRATE
					 ,G.PNT_MRATE
				FROM TB_REVIEW R LEFT JOIN TB_GOODS G ON R.GOODS_CD = G.GOODS_CD 
				WHERE 1=1 
				 AND R.DEL_YN = 'N'
				 AND R.DISP_YN = 'Y'
				 AND R.GOODS_CD IN (
				 					SELECT PCI.ITEM_VAL 
				 					FROM TB_PLAN_CONTENTS_ITEM PCI 
				 					WHERE 1=1
				 					  AND NOW() BETWEEN PCI.REVIEW_DISP_STDT AND PCI.REVIEW_DISP_EDDT 
				 					  AND PCI.PLAN_CONT_SQ = (
				 												SELECT PC.PLAN_CONT_SQ
				 												FROM TB_PLAN_CONTENTS PC
				 												WHERE 1=1
				 												 AND PC.PLAN_SQ = #{planSq}
				 												 AND PC.TMPL_TYPE = 'G082_10'
				 												 AND PC.DISP_YN = 'Y'
				 												
				 					))
			)F
		)F2
		WHERE F2.RANKNO <![CDATA[<]]> 5
		AND F2.SCORE NOT IN (1,2)
		ORDER BY F2.BEST_YN DESC
		        ,F2.RANKNO ASC LIMIT 20
	</select>
	
	<select id="getPlanCouponInfo" parameterType="Coupon" resultType="Coupon">
		/* TsfPlanning.getPlanCouponInfo*/
		SELECT  Z.*
		       ,CASE CUST_PUB_LIMIT_QTY WHEN 0 THEN '쿠폰받기'
		                                WHEN CUST_PUB_LIMIT_QTY > CUST_COUPON_CNT THEN '쿠폰받기' 
		                                WHEN CUST_PUB_LIMIT_QTY = CUST_COUPON_CNT THEN '받기완료'
		        ELSE '쿠폰받기' END AS COUPON_STAT
		FROM 
		(
		SELECT C.CPN_ID 
		      ,C.CPN_NM 
		      ,C.CPN_DESC       
		      ,C.CPN_TYPE 
		      ,C.BUY_LIMIT_AMT 
		      ,C.CUST_PUB_LIMIT_QTY 
		      ,IF(C.DC_CD_GB = 'G233_30', LC.DOWN_LIMIT_QTY , C.TOT_PUB_LIMIT_QTY) AS TOT_PUB_LIMIT_QTY
		      ,C.ONE_PUB_QTY
		      ,C.MAX_DC_AMT
		      ,C.CPN_STAT 
		      ,C.REG_DT
		      ,PC.TITLE
		      ,PC.CPN_NOTE0
		      ,PC.CPN_NOTE1
		      ,PC.CPN_NOTE2
		      ,PC.CPN_NOTE3
		      ,PC.CPN_NOTE4
		      ,PC.CPN_NOTE5
		      ,PC.CPN_NOTE6
		      ,PC.CPN_NOTE7
		      ,PC.CPN_NOTE8
		      ,PC.CPN_NOTE9
		      , IFNULL((SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = C.CPN_ID AND CUST_NO = #{custNo}),0) AS CUST_COUPON_CNT
		      , (
		          CASE WHEN C.DC_CD_GB = 'G233_30' /* 선착순쿠폰일때 */ THEN IFNULL((
		                   SELECT COUNT(1)
		                   FROM TB_LIMITED_TIME_COUPON LTC
		                      , TB_CUST_COUPON CC
		                   WHERE LTC.CPN_ID  = CC.CPN_ID
		                   AND   NOW() BETWEEN LTC.DOWN_STDT AND LTC.DOWN_EDDT
		                   AND LTC.DEL_YN = 'N'
		                   AND LTC.CPN_ID = C.CPN_ID
		                   AND CC.REG_DT BETWEEN LTC.DOWN_STDT AND LTC.DOWN_EDDT
		                   ),0)
		          ELSE IFNULL((SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = C.CPN_ID),0) END
		        ) AS TOT_COUPON_CNT
		      , CONCAT(CASE WHEN C.BUY_LIMIT_AMT = 0 THEN ''
		                   ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT , 0),'원 이상 구매 시 ')
		              END
		             ,CASE WHEN C.MAX_DC_AMT = 0 THEN ''
		                   ELSE CONCAT('최대 ',FORMAT(C.MAX_DC_AMT , 0),'원 할인')
		              END)     AS USE_CONDITION  /*사용조건*/
		     , CASE WHEN C.CUST_PUB_LIMIT_QTY = 0 THEN ''
		            ELSE CONCAT('1인 최대 ',C.CUST_PUB_LIMIT_QTY,'매')
		       END             AS ISSUE_CONDITION /*발급수량*/
		      ,CASE WHEN  #{frontGb} = 'P' THEN C.DC_PVAL
		            WHEN  #{frontGb} = 'M' THEN C.DC_MVAL
		            WHEN  #{frontGb} = 'A' THEN C.DC_AVAL
		            ELSE C.DC_AVAL
		            END                                       AS DC_VAL
		      ,CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
		                    ELSE '%'
		                    END                                      AS DC_WAY    /*할인방법*/
		      ,C.PD_GB 
		      ,IF (C.PD_GB = 'D', NOW(), C.AVAIL_STDT) AS AVAIL_STDT
		      ,IF (C.PD_GB = 'D', CONCAT(CURRENT_DATE + INTERVAL C.AVAIL_DAYS DAY, ' 23:59:59'), C.AVAIL_EDDT) AS AVAIL_EDDT
		FROM TB_PLAN_CONTENTS PC 
		             INNER JOIN TB_PLAN_CONTENTS_ITEM PCI ON PC.PLAN_CONT_SQ = PCI.PLAN_CONT_SQ 
		             INNER JOIN TB_COUPON C ON PCI.ITEM_VAL = C.CPN_ID
		LEFT OUTER JOIN TB_LIMITED_TIME_COUPON LC ON C.CPN_ID = LC.CPN_ID AND NOW() BETWEEN LC.DOWN_STDT AND LC.DOWN_EDDT
		LEFT JOIN TB_CUST_COUPON CC ON C.CPN_ID = CC.CPN_ID
		WHERE 1=1
		<if test="planSq != null and planSq != ''">
		AND PC.PLAN_SQ = #{planSq}
		</if>
		AND C.CPN_STAT ='G232_11'
		AND C.SITE_CD = #{siteCd}
		<if test="cpnId != null and cpnId != ''">
		AND C.CPN_ID  = #{cpnId}
		</if>
		AND C.CPN_TYPE IN ('G230_11','G230_30','G230_20')
		AND NOW() BETWEEN IF(C.DC_CD_GB = 'G233_30', LC.DOWN_STDT, C.DOWN_STDT) AND IF(C.DC_CD_GB = 'G233_30', LC.DOWN_EDDT,  C.DOWN_EDDT)
		AND NOW()  <![CDATA[<=]]>  IF (C.PD_GB = 'D', CONCAT(CURRENT_DATE + INTERVAL C.AVAIL_DAYS DAY, ' 23:59:59'), C.AVAIL_EDDT)
		AND (CASE WHEN 'P' = #{frontGb} THEN C.DC_PVAL
		          WHEN 'M' = #{frontGb} THEN C.DC_MVAL
		     ELSE C.DC_AVAL END) > 0                  -- PC, MOBILE,APP 별로 0 보다 큰 쿠폰
		AND PC.DISP_YN ='Y'
		AND IF (C.TOT_PUB_LIMIT_QTY = 0, 9999999999,C.TOT_PUB_LIMIT_QTY) > (SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = C.CPN_ID) -- 총발행제한수
		GROUP BY    C.CPN_ID 
		           ,C.CPN_NM 
		           ,C.CPN_DESC       
		           ,C.CPN_TYPE 
		           ,C.BUY_LIMIT_AMT 
		           ,C.CUST_PUB_LIMIT_QTY
		           ,LC.DOWN_LIMIT_QTY
		           ,C.TOT_PUB_LIMIT_QTY
		           ,C.MAX_DC_AMT
		           ,C.REG_DT
		           ,PC.TITLE
		           ,PC.CPN_NOTE0
		           ,PC.CPN_NOTE1
		           ,PC.CPN_NOTE2
		           ,PC.CPN_NOTE3
		           ,PC.CPN_NOTE4
		           ,PC.CPN_NOTE5
		           ,PC.CPN_NOTE6
		           ,PC.CPN_NOTE7
		           ,PC.CPN_NOTE8
		           ,PC.CPN_NOTE9
		)Z
	  ORDER BY  Z.REG_DT DESC ,Z.AVAIL_EDDT ASC LIMIT 10
	</select>
	
	<select id="getPlanImageInfo" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getPlanImageInfo*/
		SELECT PC.PLAN_CONT_SQ
		      ,PC.PLAN_SQ
		      ,PC.TMPL_TYPE
		      ,PC.TITLE
		      ,PC.DISP_YN
		      ,PC.DISP_ORD
		      ,PCI.PLAN_CONT_ITEM_SQ
		      ,PCI.ITEM_VAL
		      ,PCI.LINK_URL
		      ,PCI.LINK_OPEN_GB
		      ,PCI.DISP_ORD
		      ,PCI.REVIEW_DISP_STDT
		      ,PCI.REVIEW_DISP_EDDT
		FROM TB_PLAN_CONTENTS PC INNER JOIN TB_PLAN_CONTENTS_ITEM PCI ON PC.PLAN_CONT_SQ = PCI.PLAN_CONT_SQ 
		WHERE 1=1
		 AND PC.TMPL_TYPE = 'G082_30'
		 AND PC.PLAN_SQ = #{planSq}
		 AND PC.DISP_YN = 'Y'
	</select>
	
	<select id="getPlanNoticeInfo" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getPlanNoticeInfo*/
		SELECT PC.PLAN_CONT_SQ
		      ,PC.PLAN_SQ
		      ,PC.TMPL_TYPE
		      ,PC.TITLE
		      ,PC.DISP_YN
		      ,PC.DISP_ORD
		      ,PCI.PLAN_CONT_ITEM_SQ
		      ,PCI.ITEM_VAL
		      ,PCI.LINK_URL
		      ,PCI.LINK_OPEN_GB
		      ,PCI.DISP_ORD
		FROM TB_PLAN_CONTENTS PC INNER JOIN TB_PLAN_CONTENTS_ITEM PCI ON PC.PLAN_CONT_SQ = PCI.PLAN_CONT_SQ 
		WHERE 1=1
		 AND PC.TMPL_TYPE = 'G082_60'
		 AND PC.PLAN_SQ = #{planSq}
		 AND PC.DISP_YN = 'Y'
	</select>
	
	<!-- 기획전 상품  전시 -->
	<select id="getPlanGoodsDisplayList" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getPlanGoodsDisplayList */
		<include refid="selectForPagingHeader"/>
		SELECT Z.*
		      ,(CASE WHEN Z.LIST_PRICE > 0 THEN 100 - ROUND((Z.CURR_PRICE / Z.LIST_PRICE) * 100 ,0) ELSE 0 END ) AS DC_RATE
		FROM
		(
		WITH TAB_PLAN_GOODS AS (
		    SELECT P.PLAN_SQ                                                /*기획전번호*/
		         , PC.LINK_URL 
		         , PC.LINK_OPEN_GB
		         , PC.DISP_ORD 
		         , PC.TITLE 
		         , PC.TMPL_TYPE 
		         , PC.PLAN_CONT_SQ 		         
		         , PCI.ITEM_VAL 
		         , PCI.REVIEW_DISP_EDDT 
		         , PCI.REVIEW_DISP_STDT 
		         , CASE WHEN BG.DISP_NM_LANG = 'EN' THEN
		                    BG.BRAND_GROUP_ENM
		                ELSE
		                    BG.BRAND_GROUP_KNM
		           END                                    AS BRAND_GROUP_NM /*브랜드그룹명*/
		         , G.GOODS_CD                                               /*상품코드*/
		         , G.GOODS_NM                                               /*상품명*/
		         , G.GOODS_GB                                               /*상품구분*/
		         , G.FOREIGN_BUY_YN                                         /*해외구매대행여부*/
		         , G.PARALLEL_IMPORT_YN                                     /*병행수입여부*/
		         , G.ORDER_MADE_YN                                          /*주문제작여부*/
		         , G.GOODS_TNM                                              /*상품타이틀명*/
		         , G.MAIN_COLOR_CD                                          /*대표색상코드*/
		         , G.LIST_PRICE                                             /*정상가(최초판매가)*/
		         , G.CURR_PRICE                                             /*현재판매가*/
		         , PCI.REG_DT 
		    FROM   TB_PLAN P
		    	 , TB_PLAN_CONTENTS PC
		    	 , TB_PLAN_CONTENTS_ITEM PCI
		         , TB_GOODS G
		         , TB_GOODS_STOCK GS
		         , TB_BRAND B
		         , TB_BRAND_GROUP BG
		    WHERE  P.PLAN_SQ = PC.PLAN_SQ
		    AND    PC.PLAN_CONT_SQ = PCI.PLAN_CONT_SQ
		    AND    PCI.ITEM_VAL = G.GOODS_CD
		    AND    G.GOODS_CD = GS.GOODS_CD
		    AND    G.BRAND_CD = B.BRAND_CD
		    AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		    AND    P.PLAN_SQ =  #{planSq} 
		    <if test='planStat == "Y"'>
		         AND PC.PLAN_CONT_SQ = #{planContSq}
		         AND PC.TMPL_TYPE = #{tmplType}
		    </if>
		    -- AND    P.PLAN_GB = 'P' /*기획전*/
		    AND    P.SITE_CD =  #{siteCd} /**P*/
		    <if test="frontGb != null and frontGb != ''">
		    AND    P.FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		    </if>
		    AND    NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT
		    AND    P.DEL_YN = 'N' /*삭제안된 기획전*/
		    AND    P.OPEN_YN = 'Y' /*오픈된 기획전*/
		    AND    PC.TMPL_TYPE = #{tmplType}
		    AND    PC.DISP_YN = 'Y' /*전시하는템플릿*/
		    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		    AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
		    AND    GS.STOCK_QTY  >  0 /*재고있는상품*/
		    AND    B.USE_YN = 'Y'
		    AND    BG.USE_YN = 'Y'
		    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT
		)
		, TAB_GOODS_IMG AS (
		    /* 상품의 이미지 */
		    SELECT PG.GOODS_CD
		         , MAX(CASE WHEN GI.DEFAULT_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END)   AS SYS_IMG_NM
		         , MAX(CASE WHEN GI.MOUSEOVER_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END) AS SYS_IMG_NM2
		    FROM   TAB_PLAN_GOODS PG
		         , TB_GOODS_IMG GI
		    WHERE  PG.GOODS_CD = GI.GOODS_CD
		    AND    PG.MAIN_COLOR_CD = GI.COLOR_CD
		    AND    (GI.DEFAULT_IMG_YN = 'Y' OR GI.MOUSEOVER_IMG_YN = 'Y')
		    GROUP  BY PG.GOODS_CD
		)
		SELECT PG.PLAN_SQ
		     , PG.DISP_ORD
		     , PG.BRAND_GROUP_NM
		     , PG.GOODS_CD
		     , PG.TITLE
		     , PG.TMPL_TYPE
		     , PG.LINK_URL
		     , PG.LINK_OPEN_GB
		     , PG.ITEM_VAL
		     , PG.PLAN_CONT_SQ
		     , FN_GET_GOODS_NM(PG.GOODS_NM,PG.GOODS_GB,PG.FOREIGN_BUY_YN,PG.PARALLEL_IMPORT_YN,PG.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		     , PG.GOODS_NM
		     , PG.GOODS_TNM
		     , PG.MAIN_COLOR_CD
		     , PG.LIST_PRICE
		     , FN_GET_BENEFIT_PRICE(#{frontGb},PG.GOODS_CD,PG.CURR_PRICE,#{custGb})                     AS CURR_PRICE    /*현재판매가*/
		     , GI.SYS_IMG_NM
		     , GI.SYS_IMG_NM2
		     , ROW_NUMBER() OVER(ORDER BY PG.REG_DT DESC) AS RNUM
		FROM   TAB_PLAN_GOODS PG
		INNER JOIN TAB_GOODS_IMG GI ON PG.GOODS_CD = GI.GOODS_CD
		)Z
		<include refid="selectForPagingFooter2"/>
	</select>
	
	<!-- 기획전 코너 목록 -->
	<select id="getPlanCornerList" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getPlanCornerList */
		SELECT P.PLAN_SQ
		     , PD.PLAN_DTL_SQ
		     , PD.CORNER_NM
		     , PD.CORNER_DISP_TYPE
		FROM TB_PLAN P INNER JOIN TB_PLAN_DETAIL PD ON P.PLAN_SQ = PD.PLAN_SQ
		 WHERE 1=1
		  AND P.PLAN_SQ = #{planSq}
		  AND P.SITE_CD = #{siteCd}
		  AND PD.DISP_YN = 'Y'
		  AND NOW() BETWEEN PD.DISP_STDT AND PD.DISP_EDDT 
		ORDER BY PD.DISP_ORD 
	</select>
	
	<!-- 기획전코너상품 목록 -->
	<select id="getPlanningCornerGoodsList" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getPlanningCornerGoodsList */
		SELECT Z.*
		     ,(CASE WHEN Z.LIST_PRICE > 0 THEN 100 - ROUND((Z.CURR_PRICE / Z.LIST_PRICE) * 100 ,0) ELSE 0 END ) AS DC_RATE
		FROM   (
		        WITH TAB_PLAN_GOODS AS (
		            SELECT P.PLAN_SQ                                                /*기획전번호*/
		                 , PD.CORNER_NM                                             /*코너명*/
		                 , PG.DISP_ORD                                              /*상품노출순서*/
		                 , CASE WHEN BG.DISP_NM_LANG = 'EN' THEN
		                            BG.BRAND_GROUP_ENM
		                        ELSE
		                            BG.BRAND_GROUP_KNM
		                   END                                    AS BRAND_GROUP_NM /*브랜드그룹명*/
		                 , G.GOODS_CD                                               /*상품코드*/
		                 , G.GOODS_NM                                               /*상품명*/
		                 , G.GOODS_GB                                               /*상품구분*/
		                 , G.FOREIGN_BUY_YN                                         /*해외구매대행여부*/
		                 , G.PARALLEL_IMPORT_YN                                     /*병행수입여부*/
		                 , G.ORDER_MADE_YN                                          /*주문제작여부*/
		                 , G.GOODS_TNM                                              /*상품타이틀명*/
		                 , G.MAIN_COLOR_CD                                          /*대표색상코드*/
		                 , G.LIST_PRICE                                             /*정상가(최초판매가)*/
		                 , G.CURR_PRICE                                             /*현재판매가*/
		                 , RANK() OVER(PARTITION BY P.PLAN_SQ
		                               ORDER BY PG.DISP_ORD
		                                      , PG.GOODS_CD)      AS NUMB
		            FROM   TB_PLAN P
		                 , TB_PLAN_DETAIL PD
		                 , TB_PLAN_GOODS PG
		                 , TB_GOODS G
		                 , TB_GOODS_STOCK GS
		                 , TB_BRAND B
		                 , TB_BRAND_GROUP BG
		            WHERE  P.PLAN_SQ = PD.PLAN_SQ
		            AND    PD.PLAN_DTL_SQ = PG.PLAN_DTL_SQ
		            AND    PG.GOODS_CD = G.GOODS_CD
		            AND    PG.GOODS_CD = GS.GOODS_CD
		            AND    G.BRAND_CD = B.BRAND_CD
		            AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		            AND    P.PLAN_SQ = #{planSq}
		            -- AND    P.PLAN_GB = 'P' /*기획전*/
		            AND    P.SITE_CD = #{siteCd}
		            <if test="frontGb != null and frontGb != ''">
		            AND    P.FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		            </if>
		            AND    NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT /*현재 전시되는 기획전*/
		            <if test='planGb == "S"'>
		            AND    G.GOODS_CD NOT IN (SELECT GOODS_CD FROM TB_GOODS_SHOT_DELV_SKIP GSDS)
		            </if>
		            AND    P.DEL_YN = 'N' /*삭제안된 기획전*/
		            AND    P.OPEN_YN = 'Y' /*오픈된 기획전*/
		            AND    NOW() BETWEEN PD.DISP_STDT AND PD.DISP_EDDT /*현재 전시되는 코너*/
		            AND    PD.DISP_YN = 'Y' /*전시하는코너*/
		            AND    PG.DEL_YN = 'N' /*삭제안된상품*/
		            AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		            AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
		            AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*현재 판매되는 상품*/
		            AND    GS.STOCK_QTY <![CDATA[>]]> 0 /*재고있는상품*/
		            AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
		            AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
		        )
		        , TAB_GOODS_IMG AS (
		            /* 상품의 이미지 */
		            SELECT PG.GOODS_CD
		                 , MAX(CASE WHEN GI.DEFAULT_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END)   AS SYS_IMG_NM
		                 , MAX(CASE WHEN GI.MOUSEOVER_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END) AS SYS_IMG_NM2
		            FROM   TAB_PLAN_GOODS PG
		                 , TB_GOODS_IMG GI
		            WHERE  PG.GOODS_CD = GI.GOODS_CD
		            AND    PG.MAIN_COLOR_CD = GI.COLOR_CD
		            AND    (GI.DEFAULT_IMG_YN = 'Y' OR GI.MOUSEOVER_IMG_YN = 'Y')
		            GROUP  BY PG.GOODS_CD
		        )
		        , TAB_COLORCHIP AS (
		            /* 상품 컬러칩 */
		            SELECT PG.GOODS_CD
		                 , GROUP_CONCAT(DISTINCT CONCAT(C.COLOR_CD,':',CC.CD_DESC) ORDER BY CC.DISP_ORD SEPARATOR ',') AS COLOR_CHIPS
		            FROM   TAB_PLAN_GOODS PG
		                 , TB_OPTION O
		                 , TB_COLOR C
		                 , TB_COMMON_CODE CC
		            WHERE  PG.GOODS_CD = O.GOODS_CD
		            AND    O.OPT_CD1 = C.COLOR_CD
		            AND    C.COLOR_GRP_CD = CC.CD
		            AND    O.DISP_YN = 'Y'
					AND    C.COLOR_GRP_CD = CC.CD
		            AND    C.USE_YN = 'Y'
		            AND    CC.USE_YN = 'Y'
		            GROUP  BY PG.GOODS_CD
		        )
		        , TAB_GOODS_BENEFIT AS (
		            /* 상품혜택 */
		            SELECT GOODS_CD
		                 , GROUP_CONCAT(CONCAT(BENEFIT_GB,':',BENEFIT_NM) ORDER BY NUMB SEPARATOR ',') AS ICON
		            FROM   (
		                    SELECT PG.GOODS_CD
		                         , GB.BENEFIT_GB
		                         , CASE WHEN GB.BENEFIT_GB = '10' THEN '쿠폰할인'
		                                WHEN GB.BENEFIT_GB = '20' THEN '무료배송'
		                                WHEN GB.BENEFIT_GB = '30' THEN '사은품'
		                                WHEN GB.BENEFIT_GB = '40' THEN '신상'
		                                ELSE '총알배송'
		                           END                               AS BENEFIT_NM
		                         , RANK() OVER(PARTITION BY PG.GOODS_CD
		                                       ORDER BY GB.BENEFIT_GB
		                                              , GB.GOODS_CD) AS NUMB
		                    FROM   TAB_PLAN_GOODS PG
		                         , TB_GOODS_BENEFIT GB
		                    WHERE  PG.GOODS_CD = GB.GOODS_CD
		                    GROUP  BY PG.GOODS_CD, GB.BENEFIT_GB 
		                   ) Z
		            GROUP  BY GOODS_CD
		        )
		        SELECT PG.PLAN_SQ
		             , PG.CORNER_NM
		             , PG.DISP_ORD
		             , PG.BRAND_GROUP_NM
		             , PG.GOODS_CD
		             , FN_GET_GOODS_NM(PG.GOODS_NM,PG.GOODS_GB,PG.FOREIGN_BUY_YN,PG.PARALLEL_IMPORT_YN,PG.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		             , PG.GOODS_NM
		             , PG.GOODS_TNM
		             , PG.MAIN_COLOR_CD
		             , PG.LIST_PRICE
		             , FN_GET_BENEFIT_PRICE(#{frontGb},PG.GOODS_CD,PG.CURR_PRICE,#{custGb})                              AS CURR_PRICE    /*현재판매가*/
		             , GI.SYS_IMG_NM
		             , GI.SYS_IMG_NM2
		             , C.COLOR_CHIPS                                                                                                      /*컬러칩*/
		             , GB.ICON                                                                                                            /*혜택아이콘*/
		             <choose>
		                 <when test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		             , IF(W.GOODS_CD IS NULL,'','likeit')                                                                AS LIKE_IT       /*위시리스트담긴상품*/
		                 </when>
		                 <otherwise>
		             , ''                                                                                                AS LIKE_IT       /*위시리스트담긴상품*/
		                 </otherwise>
		             </choose>
		        FROM   TAB_PLAN_GOODS PG
		        LEFT OUTER JOIN TAB_GOODS_IMG GI ON PG.GOODS_CD = GI.GOODS_CD
		        LEFT OUTER JOIN TAB_COLORCHIP C ON PG.GOODS_CD = C.GOODS_CD
		        LEFT OUTER JOIN TAB_GOODS_BENEFIT GB ON PG.GOODS_CD = GB.GOODS_CD
		        <if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		        LEFT OUTER JOIN TB_WISHLIST W ON PG.GOODS_CD = W.GOODS_CD
		                                     AND W.CUST_NO = #{custNo}
		        </if>
		        <if test="maxRow != null and maxRow > 0">
		        WHERE  PG.NUMB <![CDATA[<=]]> #{maxRow}
		        </if>
		        ORDER BY PG.DISP_ORD
		       ) Z
	</select>
	
	<!-- 총알배송 상단 html 소스  -->
	<select id="getShotDeliveryHtmlTop" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getShotDeliveryHtml */
		SELECT P.PLAN_SQ 
		      ,P.PLAN_NM 
		      ,PC.PLAN_CONT_SQ 
		      ,PF.DISP_YN
		      ,PF.FRONT_GB 
		      ,PF.FSRC_PC 
		      ,PF.FSRC_MOB 
		FROM TB_PLAN P INNER JOIN TB_PLAN_CONTENTS PC ON P.PLAN_SQ = PC.PLAN_SQ 
		                                              AND P.PLAN_SQ = PC.PLAN_SQ
		               INNER JOIN TB_PLAN_FSRC PF ON P.PLAN_SQ = PF.PLAN_SQ 
		                                              AND P.PLAN_SQ = PF.PLAN_SQ
		WHERE 1=1
		  AND P.PLAN_GB = 'S'
		  AND P.DEL_YN = 'N'
		        <if test="frontGb != null and frontGb != ''">
		            <if test='frontGb == "P"'> <!-- PC웹 -->
		        AND    P.FRONT_GB IN ('A','P')
		            </if>
		            <if test='frontGb == "A" or frontGb == "M"'> <!-- 모바일 -->
		        AND    P.FRONT_GB IN ('A','M')
		            </if>
		        </if>
		  AND P.OPEN_YN ='Y'
		  AND PF.FRONT_GB = 'T'
		  AND PF.DISP_YN ='Y'
		  AND P.SITE_CD = #{siteCd}
	</select>
	
	<!-- 총알배송 하단 html 소스  -->
	<select id="getShotDeliveryHtmlBtm" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getShotDeliveryHtml */
		SELECT P.PLAN_SQ 
		      ,P.PLAN_NM 
		      ,PC.PLAN_CONT_SQ 
		      ,PF.DISP_YN
		      ,PF.FRONT_GB 
		      ,PF.FSRC_PC 
		      ,PF.FSRC_MOB 
		FROM TB_PLAN P INNER JOIN TB_PLAN_CONTENTS PC ON P.PLAN_SQ = PC.PLAN_SQ 
		                                              AND P.PLAN_SQ = PC.PLAN_SQ
		               INNER JOIN TB_PLAN_FSRC PF ON P.PLAN_SQ = PF.PLAN_SQ 
		                                              AND P.PLAN_SQ = PF.PLAN_SQ
		WHERE 1=1
		  AND P.PLAN_GB = 'S'
		  AND P.DEL_YN = 'N'
		        <if test="frontGb != null and frontGb != ''">
		            <if test='frontGb == "P"'> <!-- PC웹 -->
		        AND    P.FRONT_GB IN ('A','P')
		            </if>
		            <if test='frontGb == "A" or frontGb == "M"'> <!-- 모바일 -->
		        AND    P.FRONT_GB IN ('A','M')
		            </if>
		        </if>
		  AND P.OPEN_YN ='Y'
		  AND PF.FRONT_GB = 'B'
		  AND PF.DISP_YN ='Y'
		  AND P.SITE_CD = #{siteCd}
	</select>
	
	
	<!-- 설문조사 정보 -->
	<select id="getPollQuestionInfo" parameterType="Plan" resultType="Poll">
		/* TsfPlanning.getPollQuestionInfo */
		 SELECT P.PLAN_SQ 
		      ,P.PLAN_NM 
		      ,P.PLAN_GB 
		      ,P.POLL_SQ 
		      ,TP.POLL_TITLE 
		      ,DATE_FORMAT(TP.POLL_STDT,'%Y-%m-%d') AS POLL_STDT
		      ,DATE_FORMAT(TP.POLL_EDDT,'%Y-%m-%d') AS POLL_EDDT
		      ,TP.DUP_PARTI_YN 
		      ,TP.FST_COM_YN 
		      ,TP.PARTI_CNT 
		      ,TP.POLL_CONTENT 
		      ,P.MAIN_PIMG
		      ,P.MAIN_MIMG
		FROM TB_PLAN P INNER JOIN TB_POLL TP ON P.POLL_SQ = TP.POLL_SQ 
		WHERE 1=1
		  AND P.PLAN_SQ = #{planSq}
		  AND P.SITE_CD = #{siteCd}
		  AND P.OPEN_YN ='Y'
		  AND P.DEL_YN ='N'
		  AND TP.DEL_YN = 'N'
		  AND NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT 
		  AND NOW() BETWEEN TP.POLL_STDT AND TP.POLL_EDDT 
		 <if test="frontGb != null and frontGb != ''">
		  AND    P.FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		</if>
	</select>
	
	<!-- 설문조사 질문내용 -->
	<select id="getPollQuestionList" parameterType="Plan" resultType="Poll">
		/* TsfPlanning.getPollQuestionList */
		SELECT  P.PLAN_SQ 
		      ,P.PLAN_NM 
		      ,P.POLL_SQ 
		      ,TP.POLL_TITLE 
		      ,TP.POLL_STDT 
		      ,TP.POLL_EDDT 
		      ,TP.DUP_PARTI_YN 
		      ,TP.FST_COM_YN 
		      ,TP.PARTI_CNT 
		      ,TP.POLL_CONTENT 
		      ,PQ.POLL_QSQ 
			  ,PQ.POLL_QTITLE 
			  ,PQ.POLL_QTYPE 
			  ,PQ.POLL_QVAL1 
			  ,PQ.POLL_QVAL2
			  ,PQ.POLL_QVAL3 
			  ,PQ.POLL_QVAL4 
			  ,PQ.POLL_QVAL5 
			  ,PQ.POLL_QVAL6 
			  ,PQ.POLL_QVAL7 
			  ,PQ.POLL_QVAL8 
			  ,PQ.POLL_QVAL9
			  ,PQ.POLL_QVAL10
		FROM TB_PLAN P INNER JOIN TB_POLL TP ON P.POLL_SQ = TP.POLL_SQ AND P.POLL_SQ = TP.POLL_SQ
		 		       INNER JOIN TB_POLL_QUESTION PQ ON TP.POLL_SQ = PQ.POLL_SQ AND TP.POLL_SQ = PQ.POLL_SQ
		WHERE 1=1
		 AND P.PLAN_SQ = #{planSq}
		 AND P.SITE_CD =  #{siteCd}
		 AND P.OPEN_YN ='Y'
		 AND P.DEL_YN ='N'
		 AND PQ.DEL_YN = 'N'
		 AND TP.DEL_YN = 'N'
		 AND NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT 
		 AND NOW() BETWEEN TP.POLL_STDT AND TP.POLL_EDDT
		 <if test="frontGb != null and frontGb != ''">
		  AND    P.FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		</if>
		ORDER BY PQ.POLL_QSQ 	
	</select>
	
	<!-- 고객설문조사 등록 -->
	<insert id="savePollCustAnswer" parameterType="Poll">
		/* TsfPlanning.savePollCustAnswer */
		INSERT INTO TB_POLL_ANSWER
		       (POLL_QSQ
		       , ANS_CUST_NO
		       , POLL_QTYPE
		       , DUMMY 
		       , ANS_INDEX
		       , REG_NO
		       , REG_DT
		       )
		VALUES
		       (#{pollQsq}
		       , #{custNo}
		       , (SELECT POLL_QTYPE FROM TB_POLL_QUESTION WHERE POLL_QSQ = #{pollQsq})
		       , #{dummy}
		       , #{ansIndex}
		       , #{custNo}
		       , CURRENT_TIMESTAMP
		       )	
	</insert>
	
	<select id="getPollCustAnswerIndex" parameterType="Plan" resultType="Integer">
		/* TsfPlanning.getPollCustAnswerIndex */
		SELECT MAX(ANS_INDEX)+1 AS ANS_INDEX 
		FROM TB_POLL_ANSWER 
	</select>
	
	<!-- 설문자 중복 카운트 -->
	<select id="getCustAnswerCount" parameterType="Plan" resultType="int">
		/* TsfPlanning.getCustAnswerCount */
		SELECT COUNT(PA.ANS_CUST_NO) AS COUNT
		FROM TB_PLAN P INNER JOIN TB_POLL TP ON P.POLL_SQ = TP.POLL_SQ 
		               INNER JOIN TB_POLL_QUESTION PQ ON TP.POLL_SQ = PQ.POLL_SQ
                	   INNER JOIN TB_POLL_ANSWER PA ON PQ.POLL_QSQ = PA.POLL_QSQ 
		WHERE 1=1
		 AND P.PLAN_SQ = #{planSq}
		 AND P.SITE_CD =  #{siteCd} /**P*/
		 AND P.OPEN_YN ='Y'
		 AND P.DEL_YN ='N'
		 AND TP.DEL_YN = 'N'
		 AND PA.ANS_CUST_NO = #{custNo}
		 AND NOW() BETWEEN P.DISP_STDT AND P.DISP_EDDT 
		 AND NOW() BETWEEN TP.POLL_STDT AND TP.POLL_EDDT	
	</select>
	
	<!-- 설문자 선착순 카운트 -->
	<select id="getCustAnswerPartiCount" parameterType="Poll" resultType="int">
		/* TsfPlanning.getCustAnswerPartiCount */
		SELECT DISTINCT (COUNT(*)) AS COUNT
		FROM TB_POLL_ANSWER A INNER JOIN TB_POLL_QUESTION B ON A.POLL_QSQ = B.POLL_QSQ 
		WHERE B.POLL_SQ =  #{pollSq}
		GROUP BY b.POLL_QSQ, B.POLL_SQ
		UNION 
		SELECT 0 AS COUNT
		FROM DUAL
		ORDER BY COUNT DESC LIMIT 1		
	</select>
	
	<!-- 총알배송 배송지 목록  -->
	<select id="getCustAddrInfo" parameterType="CustDeliveryAddr" resultType="CustDeliveryAddr">
		/* TsfPlanning.getCustAddrInfo */
		SELECT C.CUST_ID
		     , C.CUST_NO
		     , CDA.CUST_DELV_ADDR_SQ
		     , CDA.CUST_NO
		     , CDA.DELV_ADDR_NM
		     , CDA.DEFAULT_YN
		     , CDA.RECIP_NM
		     , CDA.RECIP_PHNNO
		     , CDA.RECIP_TELNO
		     , CDA.RECIP_ZIPCODE
		     , CDA.RECIP_BASE_ADDR
		     , CDA.RECIP_DTL_ADDR
		     , CDA.DEL_YN
		     , CDA.DELV_MEMO
		     , CASE WHEN DDZ.ZIP_NO IS NULL
		            THEN 'N'
		            ELSE 'Y'
		       END                                     AS SHOT_DELV_USE_YN
		     , CDA.REG_NO
		     , DATE_FORMAT(CDA.REG_DT, '%Y%m%d%H%i%S') AS REG_DT
		     , FN_GET_USER_NM(CDA.UPD_NO) AS REG_NM
		     , DATE_FORMAT(CDA.UPD_DT, '%Y%m%d%H%i%S') AS UPD_DT
		FROM   TB_CUSTOMER C
		INNER JOIN
		       TB_CUST_DELIVERY_ADDR CDA
		ON     C.CUST_NO = CDA.CUST_NO
		AND    CDA.DEL_YN = 'N'
		LEFT OUTER JOIN
		       TB_DAILY_DELIVERY_ZONE DDZ
		ON     DDZ.ZIP_NO = CDA.RECIP_ZIPCODE
		AND    DDZ.ISUSE = 'Y'
		WHERE  C.CUST_NO = #{custNo}
		ORDER BY DEFAULT_YN DESC, REG_DT DESC LIMIT 1	
	</select>
	
	<!-- 총알배송 여부 -->
	<select id="getCustAddrShotConfirm" resultType="CustDeliveryAddr" parameterType="CustDeliveryAddr">
		/* TsfPlanning.getCustAddrInfo */
		SELECT CASE WHEN ISUSE IS NULL
		            THEN 'N'
		            ELSE 'Y'
		       END                                     AS SHOT_DELV_USE_YN
		FROM TB_DAILY_DELIVERY_ZONE 
		WHERE 1=1 
		AND ZIP_NO = #{recipZipcode}
	</select>
	
	<!-- 달력 출력 -->
	<select id="getAttendMonth" resultType="Plan" parameterType="Plan">
		/* TsfPlanning.getAttendMonth" */
		SELECT
		      MIN(CASE WHEN WEEKDAY = '1' THEN DAY END ) AS 'sun'
		      ,MIN(CASE WHEN WEEKDAY = '2' THEN DAY END )AS 'mon'
		      ,MIN(CASE WHEN WEEKDAY = '3' THEN DAY END )AS 'tue'
		      ,MIN(CASE WHEN WEEKDAY = '4' THEN DAY END )AS 'wed'
		      ,MIN(CASE WHEN WEEKDAY = '5' THEN DAY END )AS 'thu'
		      ,MIN(CASE WHEN WEEKDAY = '6' THEN DAY END )AS 'fri'
		      ,MIN(CASE WHEN WEEKDAY = '7' THEN DAY END )AS 'sat'
		FROM (
		SELECT 
				 CASE WHEN DAYOFWEEK(CDATE) = 1 THEN  WEEKOFYEAR(CDATE) +1 ELSE WEEKOFYEAR(CDATE) END AS WEEK
				,DAYOFWEEK(CDATE) AS WEEKDAY
				,DAYOFMONTH(CDATE) AS DAY
		FROM (
		        SELECT D1+D2 AS CDATE
		        FROM (
		               SELECT STR_TO_DATE(#{dispStdt},'%Y-%m-%d')D1
		             )D1
		             ,(
		               SELECT 0 D2 UNION 
		               SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
		               SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
		               SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION
		               SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION
		               SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION
		               SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION
		               SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION
		               SELECT 28 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31
		             )AS D2) AS TEMP
		       WHERE TEMP.CDATE <![CDATA[<=]]> LAST_DAY(TEMP.CDATE)
		)AS TEMP2
		GROUP BY WEEK
	</select>
	
	<!-- 출석체크 insert -->
	<insert id="saveAttendEntry" parameterType="Plan">
		/* TsfPlanning.saveAttendEntry */	
		INSERT INTO TB_PLAN_ENTRY
		        ( 
		         PLAN_SQ
		        , ENTRY_CUST_NO
		        , ENTRY_DT
		        , ENTRY_VAL1
		        , ENTRY_VAL2
		        , ENTRY_VAL3
		        , ENTRY_VAL4
		        , ENTRY_VAL5
		        , ENTRY_VAL6
		        , ENTRY_VAL7
		        , ENTRY_VAL8
		        , REG_NO
		        , REG_DT
		        , UPD_NO
		        , UPD_DT
		        )
		VALUES
		        ( #{planSq}
		        , #{custNo}
		        , CURRENT_TIMESTAMP
		        , #{entryVal1}
		        , #{entryVal2}
		        , #{entryVal3}
		        , #{entryVal4}
		        , #{entryVal5}
		        , #{entryVal6}
		        , #{entryVal7}
		        , #{entryVal8}
		        , #{custNo}
		        , CURRENT_TIMESTAMP
		        , #{custNo}
		        , CURRENT_TIMESTAMP
		        )
	</insert>
	
	<!-- 출석 여부 -->
	<select id="getAttendEntryCount" resultType="int" parameterType="Plan">
		/* TsfPlanning.getAttendEntryCount */	
		SELECT COUNT(*) AS COUNT
		FROM TB_PLAN_ENTRY
		WHERE 1=1 
		 AND PLAN_SQ = #{planSq}
		 <if test='entryAllYn == "N"'>
		 AND DATE_FORMAT(ENTRY_DT, '%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')
		 </if>
		 AND ENTRY_CUST_NO = #{custNo}
		 AND ENTRY_VAL2 = 'Y'
	</select>
	
	<!-- 출석한 리스트 -->
	<select id="getCustAttendEntryList" resultType="Plan" parameterType="Plan">
		/* TsfPlanning.getCustAttendEntryList */	
		SELECT  PE.PLAN_ENTRY_SQ 
		      , P.PLAN_SQ 
		      , PE.ENTRY_CUST_NO 
		      , DATE_FORMAT(PE.ENTRY_DT,'%e') AS ENTRY_DT 
		      , PE.ENTRY_DT 
		      , PE.ENTRY_VAL2
		FROM TB_PLAN P INNER JOIN TB_PLAN_ENTRY PE
		    ON P.PLAN_SQ = PE.PLAN_SQ 
		WHERE P.PLAN_SQ = #{planSq}
		AND   PE.ENTRY_CUST_NO = #{custNo}
		AND   PE.ENTRY_VAL2 = 'Y'
	</select>
	
	<select id="getAttendBenefitList" resultType="Plan" parameterType="Plan">
		/* TsfPlanning.getAttendBenefitList */	
		SELECT  PA.PLAN_SQ 
		      , PA.BENEFIT_GB
		      , PA.BAS_DAYS 
		      , PA.PNT_AMT 
		      , PA.PNT_USABLE_DAY
		      , C.CPN_ID                    /*쿠폰ID*/
		      , C.CPN_NM                     /*쿠폰명*/
		      , C.BUY_LIMIT_AMT              /*구매제한금액*/
		      , C.MAX_DC_AMT                 /*최대할인금액*/
		      , CASE WHEN #{frontGb} = 'P' THEN C.DC_PVAL
		             WHEN #{frontGb} = 'M' THEN C.DC_MVAL
		             WHEN #{frontGb} = 'A' THEN C.DC_AVAL
		        END             AS DC_VAL    /*할인값*/
		      , CASE WHEN C.DC_WAY = 'G240_10' THEN '원'                                                                                             
		             ELSE '%'
		        END             AS DC_WAY    /*할인방법*/
		      , CONCAT(CASE WHEN C.BUY_LIMIT_AMT = 0 THEN ''
		                    ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT , 0),'원 이상 구매 시 ')
		               END
		              ,CASE WHEN C.MAX_DC_AMT = 0 THEN ''
		                    ELSE CONCAT('최대 ',FORMAT(C.MAX_DC_AMT , 0),'원 할인')
		               END)     AS USE_CONDITION  /*사용조건*/
		      , CASE WHEN C.CUST_PUB_LIMIT_QTY = 0 THEN ''
		             ELSE CONCAT('1인당 최대',C.CUST_PUB_LIMIT_QTY,'매')
		        END             AS ISSUE_CONDITION /*발급수량*/
		FROM TB_PLAN_ATTEND PA LEFT JOIN TB_COUPON C ON PA.CPN_ID = C.CPN_ID 
		WHERE 1=1
		-- AND C.CPN_STAT = 'G232_11' /*진행중인쿠폰*/
		AND PA.PLAN_SQ = #{planSq}	
	</select>
	
	<!-- 댓글 리스트  (수정필)-->
	<select id="getReplyList" resultType="Plan" parameterType="Plan">
		/* TsfPlanning.getReplyList */	
		<include refid="selectForPagingHeader"/>
		SELECT Z.*
		      ,(SELECT CUST_ID FROM TB_CUSTOMER WHERE CUST_NO = Z.ENTRY_CUST_NO) AS CUST_ID
		FROM 
		(		
			SELECT PE.PLAN_ENTRY_SQ
			     , PE.PLAN_SQ
			     , PE.ENTRY_CUST_NO
			     , DATE_FORMAT(PE.ENTRY_DT,'%Y-%m-%d') AS ENTRY_DT
			     , PE.ENTRY_VAL1
			     , PE.ENTRY_VAL2
			     , PE.ENTRY_VAL3
			     , PE.ENTRY_VAL4
			     , PE.ENTRY_VAL5
			     , PE.ENTRY_VAL6
			     , PE.ENTRY_VAL7
			     , PE.ENTRY_VAL8
			     , PE.REG_NO
			     , PE.REG_DT
			     , PE.UPD_NO
			     , PE.UPD_DT
			     , RANK() OVER(ORDER BY REG_DT DESC) AS RNUM
			FROM TB_PLAN_ENTRY PE
			WHERE 1=1
		    AND PE.PLAN_SQ = #{planSq}
		    <if test="planEntrySq != null and planEntrySq != ''">
		    AND PE.PLAN_ENTRY_SQ = #{planEntrySq}
		    </if>
		    ORDER BY REG_DT DESC
		)Z
		<include refid="selectForPagingFooter"/>
	</select>
	
	<insert id="saveEntryReply" parameterType="Plan" useGeneratedKeys="true" keyProperty="planEntrySq">
		/* TsfPlanning.saveEntryReply */	
		INSERT INTO TB_PLAN_ENTRY
		( 
		  PLAN_SQ
		, ENTRY_CUST_NO
		, ENTRY_DT
		, ENTRY_VAL1
		, ENTRY_VAL2
		, ENTRY_VAL3
		, ENTRY_VAL4
		, ENTRY_VAL5
		, ENTRY_VAL6
		, ENTRY_VAL7
		, ENTRY_VAL8
		, REG_NO
		, REG_DT
		, UPD_NO
		, UPD_DT
		)
		VALUES(
		  #{planSq}
		, #{custNo}
		, CURRENT_TIMESTAMP
		, #{entryVal1}
		, #{entryVal2}
		, #{entryVal3}
		, #{entryVal4}
		, #{entryVal5}
		, #{entryVal6}
		, #{entryVal7}
		, #{entryVal8}
		, #{custNo}
		, CURRENT_TIMESTAMP
		, #{custNo}
		, CURRENT_TIMESTAMP
		)	
	</insert>
	
	<select id="getEntryReplyCount" resultType="int" parameterType="Plan">
		/* TsfPlanning.getEntryReplyCount */		
		SELECT COUNT(*) AS COUNT
		FROM TB_PLAN_ENTRY
		WHERE PLAN_SQ = #{planSq}
	</select>
	
	
	<delete id="deleteEntryReply" parameterType="Plan">
		/* TsfPlanning.deleteEntryReply */
		DELETE 
		FROM TB_PLAN_ENTRY
		WHERE PLAN_ENTRY_SQ = #{planEntrySq}		
	</delete>
	
	<insert id="saveEntryReplyAttach" parameterType="Plan">
		/* TsfPlanning.saveEntryReplyAttach */	
		INSERT INTO TB_PLAN_ENTRY_ATTACH
		(
		   PLAN_ENTRY_SQ
		 , ORG_FILE_NM
		 , SYS_FILE_NM
		 , REG_NO
		 , REG_DT
		 )
		VALUES
		(
		  #{planEntrySq}
		, #{orgFileNm}
		, #{sysFileNm}
		, #{regNo}
		, CURRENT_TIMESTAMP
		)
	</insert>
	
	<select id="getEntryReplyAttachList" resultType="Plan" parameterType="Plan">
		/* TsfPlanning.getEntryReplyAttach */	
		SELECT PEA.PLAN_ENTRY_SQ
		      ,PEA.PLAN_ENTRY_AT_SQ
		      ,PEA.ORG_FILE_NM 
		      ,PEA.SYS_FILE_NM 
		FROM TB_PLAN_ENTRY PE INNER JOIN TB_PLAN_ENTRY_ATTACH PEA 
		                                    ON PE.PLAN_ENTRY_SQ = PEA.PLAN_ENTRY_SQ 
		WHERE 1=1
		   AND PE.PLAN_SQ = #{planSq}
		   <if test="planEntrySq != null and planEntrySq != ''">
		   AND PE.PLAN_ENTRY_SQ = #{planEntrySq}
		   </if>
	</select>
	
	<delete id="deleteEntryReplyAttach" parameterType="Plan">
		/* TsfPlanning.deleteEntryReplyAttach */	
		DELETE 
		FROM TB_PLAN_ENTRY_ATTACH
		WHERE PLAN_ENTRY_SQ= #{planEntrySq}
	</delete>
	
	<!-- 총알 배송 -->
	<select id="getShotPlanSq" resultType="Integer" parameterType="Plan">
		/* TsfPlanning.getShotPlanSq */	
		SELECT PLAN_SQ 
		FROM TB_PLAN TP 
		WHERE PLAN_GB = 'S'
		AND OPEN_YN = 'Y'
		AND NOW() BETWEEN DISP_STDT AND DISP_EDDT 
		ORDER BY REG_DT DESC LIMIT 1
	</select>
	
	<select id="getPlanContentInfo" resultType="Plan" parameterType="Plan">
		/* TsfPlanning.getPlanContentInfo */	
		SELECT PLAN_SQ 
		     , PLAN_CONT_SQ 
		     , TITLE 
		     , LINK_URL 
		     , LINK_OPEN_GB 
		     , CPN_NOTE0 
		     , CPN_NOTE1 
		     , CPN_NOTE2 
		     , CPN_NOTE3 
		     , CPN_NOTE4 
		     , CPN_NOTE5 
		     , CPN_NOTE6 
		     , CPN_NOTE7 
		     , CPN_NOTE8 
		     , CPN_NOTE9 
		FROM TB_PLAN_CONTENTS
		WHERE PLAN_SQ = #{planSq}
		AND TMPL_TYPE = #{tmplType}
		AND DISP_YN = 'Y'
	</select>
	
	<select id="getShotBrandList" resultType="Plan" parameterType="Plan">
		/* TsfPlanning.getShotBrandList */	
		SELECT SDB.BRAND_GROUP_NO
		      , CASE WHEN BG.DISP_NM_LANG = 'EN' THEN
		                BG.BRAND_GROUP_ENM
		            ELSE
		                BG.BRAND_GROUP_KNM
		       END                                     AS BRAND_NM
		  , SDB.CORNER_DISP_TYPE
		  , SDB.DISP_ORD
		  , SDB.REG_DT
		FROM TB_SHOT_DELV_BRAND SDB INNER JOIN TB_BRAND_GROUP BG 
		  ON SDB.BRAND_GROUP_NO = BG.BRAND_GROUP_NO 
		WHERE BG.USE_YN = 'Y'
		ORDER BY SDB.DISP_ORD 
	</select>
	
	<!-- 총알배송 상품 목록 -->
	<select id="getShotDeliveryGoodsList" parameterType="Plan" resultType="Plan">
		/* TsfPlanning.getShotDeliveryGoodsList */	
		WITH TAB_BRAND_GOODS AS (
		   SELECT RNK.*
		        ,(CASE WHEN RNK.LIST_PRICE > 0 THEN 100 - ROUND((RNK.CURR_PRICE / RNK.LIST_PRICE) * 100 ,0) ELSE 0 END ) AS DC_RATE
		      FROM
		         (
		          SELECT  ROW_NUMBER() OVER (PARTITION BY GOODS.BRAND_GROUP_NO ORDER BY GOODS.DIS, GOODS.DISP_ORD DESC, GOODS.REG_DT DESC, GOODS.GOODS_CD) AS RNUM
		                 , GOODS.*
		            FROM 
		                (
		                  SELECT AA.*
		                        ,COUNT(*) OVER(PARTITION BY GOODS_CD) AS GOODS_CD_CNT
		                  FROM 		                 
		                       (	                
		                        SELECT 
		                           CASE WHEN BG.DISP_NM_LANG = 'EN' THEN
		                                    BG.BRAND_GROUP_ENM
		                                ELSE
		                                    BG.BRAND_GROUP_KNM
		                           END                                    AS BRAND_NM /*브랜드그룹명*/
		                         , SDB.BRAND_GROUP_NO 
		                         , SDB.DISP_ORD
		                         , G.GOODS_CD 
		                         , G.GOODS_NM 
		                         , G.GOODS_GB                                               /*상품구분*/
		                         , G.FOREIGN_BUY_YN                                         /*해외구매대행여부*/
		                         , G.PARALLEL_IMPORT_YN                                     /*병행수입여부*/
		                         , G.ORDER_MADE_YN                                          /*주문제작여부*/
		                         , G.GOODS_TNM                                              /*상품타이틀명*/
		                         , G.MAIN_COLOR_CD                                          /*대표색상코드*/
		                         , G.LIST_PRICE                                             /*정상가(최초판매가)*/
		                         , G.CURR_PRICE                                             /*현재판매가*/
		                         , 1 AS DIS
								 , G.REG_DT
		                        FROM   TB_SHOT_DELV_BRAND SDB
		                                        INNER JOIN TB_SHOT_DELV_GOODS SDG
		                               ON SDB.BRAND_GROUP_NO = SDG.BRAND_GROUP_NO
		                                        INNER JOIN TB_GOODS G
		                               ON SDG.GOODS_CD = G.GOODS_CD 
		                            	        INNER JOIN TB_BRAND B
		                               ON G.BRAND_CD = B.BRAND_CD 
		                                        INNER JOIN TB_BRAND_GROUP BG 
		                               ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO 
		                                        INNER JOIN TB_GOODS_STOCK GS
		                               ON SDG.GOODS_CD = GS.GOODS_CD 
		                        WHERE  1=1  
		                        AND    G.GOODS_CD NOT IN (SELECT GOODS_CD FROM TB_GOODS_SHOT_DELV_SKIP GSDS)
		                        AND    G.SELF_GOODS_YN = 'Y'
		                        AND    G.SELF_MALL_YN = 'Y'
		                        AND    SDG.DEL_YN = 'N'
		                        AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		                        AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*현재 판매되는 상품*/
		                        AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
		                        AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
		                        AND    GS.STOCK_QTY  <![CDATA[>]]>  0 /*재고있는상품*/
		                        UNION ALL
		                        SELECT 
		                              CASE WHEN BG.DISP_NM_LANG = 'EN' THEN
		                                       BG.BRAND_GROUP_ENM
		                                   ELSE
		                                       BG.BRAND_GROUP_KNM
		                              END                                    AS BRAND_NM /*브랜드그룹명*/
		                            , SDB.BRAND_GROUP_NO 
		                            , SDB.DISP_ORD
		                            , G.GOODS_CD 
		                            , G.GOODS_NM 
		                            , G.GOODS_GB                                               /*상품구분*/
		                            , G.FOREIGN_BUY_YN                                         /*해외구매대행여부*/
		                            , G.PARALLEL_IMPORT_YN                                     /*병행수입여부*/
		                            , G.ORDER_MADE_YN                                          /*주문제작여부*/
		                            , G.GOODS_TNM                                              /*상품타이틀명*/
		                            , G.MAIN_COLOR_CD                                          /*대표색상코드*/
		                            , G.LIST_PRICE                                             /*정상가(최초판매가)*/
		                            , G.CURR_PRICE                                             /*현재판매가*/
		                            , 2 AS DIS
									, G.REG_DT
		                        FROM   TB_SHOT_DELV_BRAND SDB
		                                         INNER JOIN TB_BRAND_GROUP BG 
		                               ON SDB.BRAND_GROUP_NO = BG.BRAND_GROUP_NO 
		                                         INNER JOIN TB_BRAND B
		                               ON BG.BRAND_GROUP_NO = B.BRAND_GROUP_NO 
		                                        INNER JOIN TB_GOODS G
		                               ON B.BRAND_CD = G.BRAND_CD 
		                                        INNER JOIN TB_GOODS_STOCK GS
		                               ON G.GOODS_CD = GS.GOODS_CD 
		                        WHERE  1=1  
		                        AND    G.GOODS_CD NOT IN (SELECT GOODS_CD FROM TB_GOODS_SHOT_DELV_SKIP GSDS)
		                        AND    G.SELF_GOODS_YN = 'Y'
		                        AND    G.SELF_MALL_YN = 'Y'
		                        AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		                        AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*현재 판매되는 상품*/
		                        AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
		                        AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
		                        AND    GS.STOCK_QTY  <![CDATA[>]]>  0 /*재고있는상품*/
		                       )AA
		                       ORDER BY AA.BRAND_GROUP_NO,  AA.DISP_ORD DESC, AA.DIS, AA.REG_DT DESC, AA.GOODS_CD
		               )GOODS 
		          WHERE CASE WHEN GOODS.GOODS_CD_CNT>1 THEN GOODS.DIS = 1 ELSE GOODS.DIS = 2 END  
		        )RNK
		   WHERE RNK.RNUM <![CDATA[<=]]> 30
		)
		 SELECT
		        BG.RNUM
		      , BG.BRAND_NM
		      , BG.GOODS_CD
		      , BG.BRAND_GROUP_NO
		      , FN_GET_GOODS_NM(BG.GOODS_NM,BG.GOODS_GB,BG.FOREIGN_BUY_YN,BG.PARALLEL_IMPORT_YN,BG.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		      , BG.GOODS_NM
		      , BG.GOODS_TNM
		      , BG.MAIN_COLOR_CD
		      , BG.LIST_PRICE
		      , FN_GET_BENEFIT_PRICE( #{frontGb} /**P*/,BG.GOODS_CD,BG.CURR_PRICE,#{custGb})                              AS CURR_PRICE    /*현재판매가*/
			, (SELECT MAX(CASE WHEN GI.DEFAULT_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END)
				FROM TB_GOODS_IMG GI
				WHERE GI.GOODS_CD = BG.GOODS_CD AND GI.COLOR_CD = BG.MAIN_COLOR_CD AND (GI.DEFAULT_IMG_YN = 'Y' OR GI.MOUSEOVER_IMG_YN = 'Y')
			    ) AS SYS_IMG_NM
			, ( SELECT GROUP_CONCAT(DISTINCT CONCAT(C.COLOR_CD,':',CC.CD_DESC) ORDER BY CC.DISP_ORD SEPARATOR ',')
				FROM TB_OPTION O
					, TB_COLOR C
					, TB_COMMON_CODE CC
				WHERE  BG.GOODS_CD = O.GOODS_CD
					AND    O.OPT_CD1 = C.COLOR_CD
					AND    C.COLOR_GRP_CD = CC.CD
					AND    O.DISP_YN = 'Y'
					AND    C.USE_YN = 'Y'
					AND    CC.USE_YN = 'Y'
				) AS COLOR_CHIPS
			, (
				SELECT GROUP_CONCAT(CONCAT(BENEFIT_GB,':',BENEFIT_NM) ORDER BY NUMB SEPARATOR ',')
				FROM  (
					SELECT BG.GOODS_CD
						, GB.BENEFIT_GB
						, CASE WHEN GB.BENEFIT_GB = '10' THEN '쿠폰할인'
						WHEN GB.BENEFIT_GB = '20' THEN '무료배송'
						WHEN GB.BENEFIT_GB = '30' THEN '사은품'
						WHEN GB.BENEFIT_GB = '40' THEN '신상'
						ELSE '총알배송'
						END                               AS BENEFIT_NM
						, RANK() OVER(PARTITION BY BG.GOODS_CD
						ORDER BY GB.BENEFIT_GB
						, GB.GOODS_CD) AS NUMB
					FROM   TB_GOODS_BENEFIT GB
					WHERE  BG.GOODS_CD = GB.GOODS_CD
				) Z
			) AS ICON                                                                                                           /*혜택아이콘*/
		       <choose>
		                 <when test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		             , IF(W.GOODS_CD IS NULL,'','likeit')                                                                AS LIKE_IT       /*위시리스트담긴상품*/
		                 </when>
		                 <otherwise>
		             , ''                                                                                                AS LIKE_IT       /*위시리스트담긴상품*/
		                 </otherwise>
		      </choose>
		FROM   TAB_BRAND_GOODS BG
		<if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		LEFT OUTER JOIN TB_WISHLIST W ON BG.GOODS_CD = W.GOODS_CD
		        AND W.CUST_NO = #{custNo}
        </if>
	</select>
	
</mapper>