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

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

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>

	<!-- GNB 브랜드그룹 목록 -->
	<select id="getGnbBrandGroupList" parameterType="Contents" resultType="BrandGroup">
		/* TsfDisplay.getGnbBrandGroupList */
		WITH TAB AS (
		    SELECT  CAST(STR_TITLE3 AS UNSIGNED) AS STR_TITLE3
		    FROM
		<choose>
		    <when test='preview != null and preview == "Y"'>
		        TB_CONTENTS_PREVIEW
		    </when>
		    <otherwise>
		        TB_CONTENTS
		    </otherwise>
		</choose>
		    WHERE 1=1
		    AND   CONTENTS_LOC = #{contentsLoc}
		    AND   DISP_STDT <![CDATA[<=]]> NOW()
		    AND   DISP_EDDT <![CDATA[>=]]> NOW()
		    GROUP BY  CAST(STR_TITLE3 AS UNSIGNED)
		    ORDER BY  CAST(STR_TITLE3 AS UNSIGNED)
		    LIMIT 1
		)
		SELECT B.STR_TITLE1     AS TITLE
		     , A.BRAND_GROUP_NO
		     , CASE WHEN A.DISP_NM_LANG = 'EN' THEN A.BRAND_GROUP_ENM
		            ELSE A.BRAND_GROUP_KNM
		       END              AS BRAND_GROUP_NM
		     , A.LOGO_FILE_NM
		     , A.BW_LOGO_FILE_NM
		     , B.DISP_ORD
		FROM   TB_BRAND_GROUP A
		<choose>
		    <when test='preview != null and preview == "Y"'>
		     , TB_CONTENTS_PREVIEW B
		    </when>
		    <otherwise>
		     , TB_CONTENTS B
		    </otherwise>
		</choose>
		     , TAB C
		WHERE  B.STR_TITLE3 = C.STR_TITLE3
		AND    A.BRAND_GROUP_NO = B.STR_VAR1
		AND    A.USE_YN = 'Y'
		AND    B.CONTENTS_LOC = #{contentsLoc}
		<choose>
		    <when test='preview != null and preview == "Y"'>
		AND    B.DISP_STDT <![CDATA[<=]]> STR_TO_DATE(#{viewDt},'%Y%m%d%H%i%S')
		AND    B.DISP_EDDT <![CDATA[>=]]> STR_TO_DATE(#{viewDt},'%Y%m%d%H%i%S')
		ORDER  BY B.DISP_ORD, B.PRE_CONTENTS_SQ
		    </when>
		    <otherwise>
		AND    B.DISP_STDT <![CDATA[<=]]> NOW()
		AND    B.DISP_EDDT <![CDATA[>=]]> NOW()
		ORDER  BY B.DISP_ORD, B.CONTENTS_SQ
		    </otherwise>
		</choose>
	</select>
	
	<!-- GNB 브랜드그룹 조회 -->
	<select id="getGnbBrandGroup" parameterType="Integer" resultType="BrandGroup">
		/* TsfDisplay.getGnbBrandGroup */
		SELECT BRAND_GROUP_NO
		     , CASE WHEN DISP_NM_LANG = 'EN' THEN BRAND_GROUP_ENM
		            ELSE BRAND_GROUP_KNM
		       END              AS BRAND_GROUP_NM
		     , LOGO_FILE_NM
		     , RGB_CD
		     , DEFAULT_CATE_YN
		     , CASE WHEN DEFAULT_CATE_YN = 'Y' THEN (SELECT CD FROM TB_COMMON_CODE WHERE 1=1 AND CD_NM = 'BY ITEM')
		            ELSE (SELECT CD FROM TB_COMMON_CODE WHERE 1=1 AND CD_NM = 'BY BRAND') END AS CATE_GB
		     , IFNULL((SELECT MAX(SS.SUPPLY_COMP_CD) 
					FROM TB_BRAND SB INNER JOIN TB_BRAND_SUPPLY SS ON SB.BRAND_CD = SS.BRAND_CD 
					WHERE SB.BRAND_GROUP_NO = A.BRAND_GROUP_NO
			   ), '') AS SUPPLY_COMP_CD
		FROM   TB_BRAND_GROUP A
		WHERE  BRAND_GROUP_NO = #{brandGroupNo}
		AND    USE_YN = 'Y'
	</select>
	
	<!-- GNB탭 목록 -->
	<select id="getGnbTabList" parameterType="GnbTab" resultType="GnbTab">
		/* TsfDisplay.getGnbTabList */
		SELECT GTAB_GB                                            /*GNB탭구분*/
		     , GTAB_NM                                            /*GNB탭명*/
		     , CONTENTS_TYPE                                      /*컨텐츠유형*/
		     , CATE1_NO                                           /*카테고리1번호*/
		     , BRAND_GROUP_NO                                     /*브랜드그룹번호*/
		     , LINK_URL                                           /*링크URL*/
		     , DATE_FORMAT(DISP_STDT,'%Y%m%d%H%i%S') AS DISP_STDT /*전시시작일시*/
		     , DATE_FORMAT(DISP_EDDT,'%Y%m%d%H%i%S') AS DISP_EDDT /*전시종료일시*/
		     , DISP_ORD                                           /*표시순서*/
		<choose>
		    <when test='preview != null and preview == "Y"'>
		     , PRE_GTAB_SQ                           AS GTAB_SQ   /*GNB탭일련번호*/
		FROM   TB_GNB_TAB_PREVIEW A
		    </when>
		    <otherwise>
		     , GTAB_SQ                                            /*GNB탭일련번호*/
		FROM   TB_GNB_TAB A
		    </otherwise>
		</choose>
		WHERE  GTAB_GB = #{gtabGb}
		<if test='brandGroupNo != null and brandGroupNo != ""'>
		AND    BRAND_GROUP_NO = #{brandGroupNo}
		</if>
		AND    USE_YN = 'Y'
		<choose>
		    <when test='preview != null and preview == "Y"'>
		AND    DISP_STDT <![CDATA[<=]]> STR_TO_DATE(#{viewDt},'%Y%m%d%H%i%S')
		AND    DISP_EDDT <![CDATA[>=]]> STR_TO_DATE(#{viewDt},'%Y%m%d%H%i%S')
		ORDER  BY DISP_ORD, PRE_GTAB_SQ
		    </when>
		    <otherwise>
		AND    DISP_STDT <![CDATA[<=]]> NOW()
		AND    DISP_EDDT <![CDATA[>=]]> NOW()
		ORDER  BY DISP_ORD, GTAB_SQ
		    </otherwise>
		</choose>
	</select>
	
	<!-- 컨텐츠 목록 -->
	<select id="getContentsList" parameterType="Contents" resultType="Contents">
		/* TsfDisplay.getContentsList */
		SELECT CONTENTS_SQ     /*컨텐츠일련번호*/
		     , CATE_NO         /*카테고리번호*/
		     , CONTENTS_LOC    /*컨텐츠위치*/
		     , CONTENTS_TYPE   /*컨텐츠유형*/
		     , DISP_STDT       /*전시시작일시*/
		     , DISP_EDDT       /*전시종료일시*/
		     , DISP_ORD        /*표시순서*/
		     , IMG_PATH1       /*이미지경로1*/
		     , IMG_PATH2       /*이미지경로2*/
		     , IMG_PATH3       /*이미지경로3*/
		     , IMG_PATH4       /*이미지경로4*/
		     , IMG_PATH5       /*이미지경로5*/
		     , IMG_PATH6       /*이미지경로6*/
		     , IMG_PATH7       /*이미지경로7*/
		     , IMG_PATH8       /*이미지경로8*/
		     , STR_VAR1        /*링크필드1*/
		     , STR_VAR2        /*링크필드2*/
		     , STR_VAR3        /*링크필드3*/
		     , STR_VAR4        /*링크필드4*/
		     , STR_VAR5        /*링크필드5*/
		     , STR_VAR6        /*링크필드6*/
		     , STR_VAR7        /*링크필드7*/
		     , STR_VAR8        /*링크필드8*/
		     , STR_TITLE1      /*제목필드1*/
		     , STR_TITLE2      /*제목필드2*/
		     , STR_TITLE3      /*제목필드3*/
		     , STR_TITLE4      /*제목필드4*/
		     , SUB_TEXT1       /*서브텍스트1*/
		     , SUB_TEXT2       /*서브텍스트2*/
		     , SUB_TEXT3       /*서브텍스트3*/
		     , SUB_TEXT4       /*서브텍스트4*/
		     <if test='contentsLoc != null and contentsLoc =="SGNB001"'>
		     , HTML
		     </if>
		     , BRAND_GROUP_NO  /*브랜드그룹번호*/
		     <if test='contentsLoc != null and contentsLoc =="SMM007"'>
		     , BRAND_GROUP_NM
		     </if>
		     , BRAND_GROUP_IMG /*브랜드그룹이미지경로*/
		     , MD_TITLE        /*MD타이틀*/
		     <if test='contentsLoc != null and contentsLoc =="SMM001"'>
		     , PLAN_BRAND_CNT
		     , PLAN_BRAND_GROUP_NO
		     , PLAN_BRAND_GROUP_NM
		     , PLAN_GB
		     </if>
		FROM   (
		        SELECT A.CATE_NO                                            /*카테고리번호*/
		             , A.CONTENTS_LOC                                       /*컨텐츠위치*/
		             , A.CONTENTS_TYPE                                      /*컨텐츠유형*/
		             , DATE_FORMAT(A.DISP_STDT,'%Y%m%d%H%i%S') AS DISP_STDT /*전시시작일시*/
		             , DATE_FORMAT(A.DISP_EDDT,'%Y%m%d%H%i%S') AS DISP_EDDT /*전시종료일시*/
		             , A.DISP_ORD                                           /*표시순서*/
		             , A.IMG_PATH1                                          /*이미지경로1*/
		             , A.IMG_PATH2                                          /*이미지경로2*/
		             , A.IMG_PATH3                                          /*이미지경로3*/
		             , A.IMG_PATH4                                          /*이미지경로4*/
		             , A.IMG_PATH5                                          /*이미지경로5*/
		             , A.IMG_PATH6                                          /*이미지경로6*/
		             , A.IMG_PATH7                                          /*이미지경로7*/
		             , A.IMG_PATH8                                          /*이미지경로8*/
		             , A.STR_VAR1                                           /*링크필드1*/
		             , A.STR_VAR2                                           /*링크필드2*/
		             , A.STR_VAR3                                           /*링크필드3*/
		             , A.STR_VAR4                                           /*링크필드4*/
		             , A.STR_VAR5                                           /*링크필드5*/
		             , A.STR_VAR6                                           /*링크필드6*/
		             , A.STR_VAR7                                           /*링크필드7*/
		             , A.STR_VAR8                                           /*링크필드8*/
		             , A.STR_TITLE1                                         /*제목필드1*/
		             , A.STR_TITLE2                                         /*제목필드2*/
		             , A.STR_TITLE3                                         /*제목필드3*/
		             , A.STR_TITLE4                                         /*제목필드4*/
		             , A.SUB_TEXT1                                          /*서브텍스트1*/
		             , A.SUB_TEXT2                                          /*서브텍스트2*/
		             , A.SUB_TEXT3                                          /*서브텍스트3*/
		             , A.SUB_TEXT4                                          /*서브텍스트4*/
		             <if test='contentsLoc != null and contentsLoc =="SGNB001"'>
		             , A.HTML
					 </if>
		             , A.BRAND_GROUP_NO                                     /*브랜드그룹번호*/
		             <if test='contentsLoc != null and contentsLoc =="SMM007"'>
		             , (SELECT FN_GET_BRAND_GROUP_NM(A.BRAND_GROUP_NO)) AS BRAND_GROUP_NM
		             </if>
		             <choose>
		                 <when test='contentsLoc != null and contentsLoc =="SMM007"'>
		                    , (SELECT LOGO_FILE_NM FROM TB_BRAND_GROUP WHERE 1=1 AND A.BRAND_GROUP_NO = BRAND_GROUP_NO) AS BRAND_GROUP_IMG		/*브랜드그룹이미지경로*/
		                 </when>
		                 <otherwise>
		                    , A.BRAND_GROUP_IMG	/*브랜드그룹이미지경로*/
		                 </otherwise>
		             </choose>
		             , A.MD_TITLE                                           /*MD타이틀*/
		             <if test='contentsLoc != null and contentsLoc =="SMM001"'>
		                , (SELECT count(C.BRAND_GROUP_NO) AS CNT
		                   FROM TB_PLAN_BRAND C
		                   WHERE B.PLAN_SQ = C.PLAN_SQ
		                   ) AS PLAN_BRAND_CNT
		                , (SELECT min(C.BRAND_GROUP_NO) AS BRAND_GROUP_NO
		                   FROM TB_PLAN_BRAND C
		                   WHERE B.PLAN_SQ = C.PLAN_SQ
		                   ) AS PLAN_BRAND_GROUP_NO
		                , (SELECT FN_GET_BRAND_GROUP_NM(min(C.BRAND_GROUP_NO)) AS BRAND_GROUP_NM
		                   FROM TB_PLAN_BRAND C
		                   WHERE B.PLAN_SQ = C.PLAN_SQ
		                   ) AS PLAN_BRAND_GROUP_NM
		                , B.PLAN_GB
		             </if>
		        <choose>
		            <when test='preview != null and preview == "Y"'>
		             , A.PRE_CONTENTS_SQ                       AS CONTENTS_SQ /*컨텐츠일련번호*/
		             -- , RANK() OVER(PARTITION BY CATE_NO, CONTENTS_LOC
		             --               ORDER BY CATE_NO, DISP_ORD, PRE_CONTENTS_SQ) AS NUMB
		        FROM   TB_CONTENTS_PREVIEW A
		            </when>
		            <otherwise>
		             , A.CONTENTS_SQ
		             -- , RANK() OVER (PARTITION BY CATE_NO, CONTENTS_LOC
		             --               ORDER BY CATE_NO, DISP_ORD, CONTENTS_SQ) AS NUMB
		        FROM   TB_CONTENTS A
		            </otherwise>
		        </choose>
				<if test='contentsLoc != null and contentsLoc =="SMM001"'>
					LEFT OUTER JOIN		TB_PLAN B
		        	ON A.STR_VAR6 = B.PLAN_SQ
				</if>
		        WHERE  A.CONTENTS_LOC = #{contentsLoc}
		        AND    A.USE_YN = 'Y'
		        <if test="cateNo != null and cateNo != ''">
		        AND    A.CATE_NO = #{cateNo}
		        </if>
		        <if test="brandGroupNo != null and brandGroupNo !=''">
		        AND    A.BRAND_GROUP_NO  = #{brandGroupNo}
		        </if>
		        <if test="contentsType != null and contentsType != ''">
		        AND    A.CONTENTS_TYPE = #{contentsType}
		        </if>
		        <choose>
		            <when test='preview != null and preview == "Y"'>
		        AND    A.DISP_STDT <![CDATA[<=]]> STR_TO_DATE(#{viewDt},'%Y%m%d%H%i%S')
		        AND    A.DISP_EDDT <![CDATA[>=]]> STR_TO_DATE(#{viewDt},'%Y%m%d%H%i%S')
		            </when>
		            <otherwise>
		        AND    A.DISP_STDT <![CDATA[<=]]> NOW()
		        AND    A.DISP_EDDT <![CDATA[>=]]> NOW()
		            </otherwise>
		        </choose>
		       ) A
		ORDER BY DISP_ORD
		<if test="maxRow != null and maxRow > 0">
		LIMIT #{maxRow}
		</if>
	</select>

	<!-- 전체 카테고리 목록 -->
	<select id="getAllCategoryList" parameterType="Cate4Srch" resultType="Cate4Srch">
		/* TsfDisplay.getAllCategoryList */
		SELECT SITE_CD
		     , CATE_GB
		     , CATE1_NO
		     , CATE1_NM
		     , CATE2_NO
		     , CATE2_NM
		     , CATE3_NO
		     , CATE3_NM
		     , CATE4_NO
		     , CATE4_NM
		     , CATE5_NO
		     , CATE5_NM
		     , DISP_ORD
		FROM   TB_CATE_4SRCH C4
		WHERE  SITE_CD = #{siteCd}
		AND    CATE_GB = #{cateGb}
		AND    CATE_TYPE = #{cateType} /*카테고리유형*/
		<if test="cate1No != null and cate1No != ''">
		AND    CATE1_NO = #{cate1No}
		</if>
		<if test='defaultCateYn != null and defaultCateYn != "" and defaultCateYn == "N"'> <!-- 별도카테고리이면 -->
		AND    CATE1_NO = (SELECT BRAND_CATE_NO
		                   FROM   TB_BRAND_GROUP
		                   WHERE  BRAND_GROUP_NO = #{brandGroupNo}
		                  )
		</if>
		AND    EXISTS (SELECT 1
		               FROM   TB_CATE_STOCK
		               WHERE  SITE_CD = C4.SITE_CD
		               AND    CATE_NO = C4.CATE1_NO
		               <if test='brandGroupNo != null'>
		               AND    BRAND_GROUP_NO = #{brandGroupNo}
		               </if>
		               <if test='formalGb != null'>
		               AND    FORMAL_GB = #{formalGb}
		               </if>
		               <if test='soldoutGoodsDispYn != null and soldoutGoodsDispYn =="N"'>
		               AND    STOCK_QTY > 0 /*품절상품전시여부가 N일 때 재고가 있는 카테고리만*/
		               </if>
		              )
		<if test="keyword != null and keyword != ''"> <!-- 상품검색키워드 -->
		AND    LEAF_CATE_NO IN (SELECT CG.CATE_NO
		                        FROM   TB_CATE_GOODS CG
		                             , TB_GOODS G USE INDEX (IX_GOODS_11)
		                             , TB_GOODS_STOCK S
		                             , TB_BRAND B
		                        WHERE  CG.GOODS_CD = G.GOODS_CD
		                        AND    CG.GOODS_CD = S.GOODS_CD
		                        AND    G.BRAND_CD = B.BRAND_CD
		                        AND    (
		                                G.GOODS_NUM LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                                OR
		                                G.GOODS_CD LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                                OR
		                                UPPER(G.GOODS_NM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                                OR
		                                UPPER(B.BRAND_ENM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                                OR
		                                UPPER(B.BRAND_KNM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                               )
		                        AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		                        AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		                        AND    G.SELL_STDT <![CDATA[<=]]> NOW() AND G.SELL_EDDT >= NOW() /*유효한 판매기간*/
		                       )
		</if>
		ORDER  BY DISP_ORD
	</select>
	
	<!-- 카테고리1 목록 -->
	<select id="getCategory1List" parameterType="Cate4Srch" resultType="Cate1">
		/* TsfDisplay.getCategory1List */
		SELECT SITE_CD
		     , CATE_GB
		     , CATE1_NO
		     , CATE1_NM
		     , LEAF_YN
		FROM   TB_CATE1 A
		WHERE  SITE_CD = #{siteCd} /*사이트코드*/
		AND    CATE_GB = #{cateGb} /*카테고리구분*/
		AND    CATE_TYPE = #{cateType} /*카테고리유형*/
		<if test="cate1No != null and cate1No != ''">
		AND    CATE1_NO = #{cate1No}
		</if>
		<if test='defaultCateYn != null and defaultCateYn != "" and defaultCateYn == "N"'> <!-- 별도카테고리이면 -->
		AND    CATE1_NO = (SELECT BRAND_CATE_NO
		                   FROM   TB_BRAND_GROUP
		                   WHERE  BRAND_GROUP_NO = #{brandGroupNo}
		                  )
		</if>
		AND    DISP_YN = 'Y'
		AND    USE_YN = 'Y'
		AND    EXISTS (SELECT 1
		               FROM   TB_CATE_STOCK
		               WHERE  SITE_CD = A.SITE_CD
		               AND    CATE_NO = A.CATE1_NO
		               <if test='brandGroupNo != null'>
		               AND    BRAND_GROUP_NO = #{brandGroupNo}
		               </if>
		               <if test='formalGb != null'>
		               AND    FORMAL_GB = #{formalGb}
		               </if>
		               <if test='soldoutGoodsDispYn != null and soldoutGoodsDispYn =="N"'>
		               AND    STOCK_QTY > 0 /*품절상품전시여부가 N일 때 재고가 있는 카테고리만*/
		               </if>
		              )
		<if test="keyword != null and keyword != ''"> <!-- 상품검색키워드 -->
		AND    CATE1_NO IN (SELECT DISTINCT C4.CATE1_NO
		                    FROM   TB_CATE_4SRCH C4
		                         , TB_CATE_GOODS CG
		                         , TB_GOODS G USE INDEX (IX_GOODS_11)
		                         , TB_GOODS_STOCK S
		                         , TB_BRAND B
		                    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
		                    AND    CG.GOODS_CD = G.GOODS_CD
		                    AND    CG.GOODS_CD = S.GOODS_CD
		                    AND    G.BRAND_CD = B.BRAND_CD
		                    AND    C4.SITE_CD = #{siteCd}
		                    AND    C4.CATE_GB = #{cateGb}
		                    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		                    AND    (
		                            G.GOODS_NUM LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                            OR
		                            G.GOODS_CD LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                            OR
		                            UPPER(G.GOODS_NM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                            OR
		                            UPPER(B.BRAND_ENM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                            OR
		                            UPPER(B.BRAND_KNM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                           )
		                    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		                    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		                    AND    G.SELL_STDT <![CDATA[<=]]> NOW() AND G.SELL_EDDT >= NOW() /*유효한 판매기간*/
		                   )
		</if>
		ORDER  BY DISP_ORD
	</select>
	
	<!-- 카테고리2 목록 -->
	<select id="getCategory2List" parameterType="Cate4Srch" resultType="Cate2">
		/* TsfDisplay.getCategory2List */
		SELECT SITE_CD
		     , CATE_GB
		     , CATE1_NO
		     , CATE2_NO
		     , CATE2_NM
		     , LEAF_YN
		FROM   TB_CATE2 A
		WHERE  SITE_CD = #{siteCd} /*사이트코드*/
		AND    CATE_GB = #{cateGb} /*카테고리구분*/
		AND    CATE_TYPE = #{cateType} /*카테고리유형*/
		AND    CATE1_NO = #{cate1No} /*카테고리1번호*/
		AND    DISP_YN = 'Y'
		AND    USE_YN = 'Y'
		AND    EXISTS (SELECT 1
		               FROM   TB_CATE_STOCK
		               WHERE  SITE_CD = A.SITE_CD
		               AND    CATE_NO = A.CATE2_NO
		               <if test='brandGroupNo != null'>
		               AND    BRAND_GROUP_NO = #{brandGroupNo}
		               </if>
		               <if test='formalGb != null'>
		               AND    FORMAL_GB = #{formalGb}
		               </if>
		               <if test='soldoutGoodsDispYn != null and soldoutGoodsDispYn =="N"'>
		               AND    STOCK_QTY > 0 /*품절상품전시여부가 N일 때 재고가 있는 카테고리만*/
		               </if>
		              )
		<if test="keyword != null and keyword != ''"> <!-- 상품검색키워드 -->
		AND    CATE2_NO IN (SELECT DISTINCT C4.CATE2_NO
		                      FROM   TB_CATE_4SRCH C4
		                           , TB_CATE_GOODS CG
		                           , TB_GOODS G USE INDEX (IX_GOODS_11)
		                           , TB_GOODS_STOCK S
		                           , TB_BRAND B
		                      WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
		                      AND    CG.GOODS_CD = G.GOODS_CD
		                      AND    CG.GOODS_CD = S.GOODS_CD
		                      AND    G.BRAND_CD = B.BRAND_CD
		                      AND    C4.SITE_CD = #{siteCd}
		                      AND    C4.CATE_GB = #{cateGb}
		                      AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		                      AND    (
		                              G.GOODS_NUM LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                              OR
		                              G.GOODS_CD LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                              OR
		                              UPPER(G.GOODS_NM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                              OR
		                              UPPER(B.BRAND_ENM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                              OR
		                              UPPER(B.BRAND_KNM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                             )
		                      AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		                      AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		                      AND    G.SELL_STDT <![CDATA[<=]]> NOW() AND G.SELL_EDDT >= NOW() /*유효한 판매기간*/
		                     )
		</if>
		ORDER  BY DISP_ORD
	</select>
	
	<!-- 카테고리3 목록 -->
	<select id="getCategory3List" parameterType="Cate4Srch" resultType="Cate3">
		/* TsfDisplay.getCategory3List */
		SELECT SITE_CD
		     , CATE_GB
		     , CATE1_NO
		     , CATE2_NO
		     , CATE3_NO
		     , CATE3_NM
		     , LEAF_YN
		FROM   TB_CATE3 A
		WHERE  SITE_CD = #{siteCd} /*사이트코드*/
		AND    CATE_GB = #{cateGb} /*카테고리구분*/
		AND    CATE_TYPE = #{cateType} /*카테고리유형*/
		AND    CATE1_NO = #{cate1No} /*카테고리1번호*/
		AND    CATE2_NO = #{cate2No} /*카테고리2번호*/
		AND    DISP_YN = 'Y'
		AND    USE_YN = 'Y'
		AND    EXISTS (SELECT 1
		               FROM   TB_CATE_STOCK
		               WHERE  SITE_CD = A.SITE_CD
		               AND    CATE_NO = A.CATE3_NO
		               <if test='brandGroupNo != null'>
		               AND    BRAND_GROUP_NO = #{brandGroupNo}
		               </if>
		               <if test='formalGb != null'>
		               AND    FORMAL_GB = #{formalGb}
		               </if>
		               <if test='soldoutGoodsDispYn != null and soldoutGoodsDispYn =="N"'>
		               AND    STOCK_QTY > 0 /*품절상품전시여부가 N일 때 재고가 있는 카테고리만*/
		               </if>
		              )
		<if test="keyword != null and keyword != ''"> <!-- 상품검색키워드 -->
		AND    CATE3_NO IN (SELECT DISTINCT C4.CATE3_NO
		                      FROM   TB_CATE_4SRCH C4
		                           , TB_CATE_GOODS CG
		                           , TB_GOODS G USE INDEX (IX_GOODS_11)
		                           , TB_GOODS_STOCK S
		                           , TB_BRAND B
		                     WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
		                     AND    CG.GOODS_CD = G.GOODS_CD
		                     AND    CG.GOODS_CD = S.GOODS_CD
		                     AND    G.BRAND_CD = B.BRAND_CD
		                     AND    C4.SITE_CD = #{siteCd}
		                     AND    C4.CATE_GB = #{cateGb}
		                     AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		                     AND    (
		                              G.GOODS_NUM LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                              OR
		                              G.GOODS_CD LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                              OR
		                              UPPER(G.GOODS_NM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                              OR
		                              UPPER(B.BRAND_ENM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                              OR
		                              UPPER(B.BRAND_KNM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                            )
		                     AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		                     AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		                     AND    G.SELL_STDT <![CDATA[<=]]> NOW() AND G.SELL_EDDT >= NOW() /*유효한 판매기간*/
		                    )
		</if>
		ORDER  BY DISP_ORD
	</select>
	
	<!-- 카테고리4 목록 -->
	<select id="getCategory4List" parameterType="Cate4Srch" resultType="Cate4">
		/* TsfDisplay.getCategory4List */
		SELECT SITE_CD
		     , CATE_GB
		     , CATE1_NO
		     , CATE2_NO
		     , CATE3_NO
		     , CATE4_NO
		     , CATE4_NM
		     , LEAF_YN
		FROM   TB_CATE4 A
		WHERE  SITE_CD = #{siteCd} /*사이트코드*/
		AND    CATE_GB = #{cateGb} /*카테고리구분*/
		AND    CATE_TYPE = #{cateType} /*카테고리유형*/
		AND    CATE1_NO = #{cate1No} /*카테고리1번호*/
		AND    CATE2_NO = #{cate2No} /*카테고리2번호*/
		AND    CATE3_NO = #{cate3No} /*카테고리3번호*/
		AND    DISP_YN = 'Y'
		AND    USE_YN = 'Y'
		AND    EXISTS (SELECT 1
		               FROM   TB_CATE_STOCK
		               WHERE  SITE_CD = A.SITE_CD
		               AND    CATE_NO = A.CATE4_NO
		               <if test='brandGroupNo != null'>
		               AND    BRAND_GROUP_NO = #{brandGroupNo}
		               </if>
		               <if test='formalGb != null'>
		               AND    FORMAL_GB = #{formalGb}
		               </if>
		               <if test='soldoutGoodsDispYn != null and soldoutGoodsDispYn =="N"'>
		               AND    STOCK_QTY > 0 /*품절상품전시여부가 N일 때 재고가 있는 카테고리만*/
		               </if>
		              )
		<if test="keyword != null and keyword != ''"> <!-- 상품검색키워드 -->
		AND    CATE4_NO IN (SELECT DISTINCT C4.CATE4_NO
		                    FROM   TB_CATE_4SRCH C4
		                         , TB_CATE_GOODS CG
		                         , TB_GOODS G USE INDEX (IX_GOODS_11)
		                         , TB_GOODS_STOCK S
		                         , TB_BRAND B
		                    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
		                    AND    CG.GOODS_CD = G.GOODS_CD
		                    AND    CG.GOODS_CD = S.GOODS_CD
		                    AND    G.BRAND_CD = B.BRAND_CD
		                    AND    C4.SITE_CD = #{siteCd}
		                    AND    C4.CATE_GB = #{cateGb}
		                    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		                    AND    (
		                             G.GOODS_NUM LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                             OR
		                             G.GOODS_CD LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                             OR
		                             UPPER(G.GOODS_NM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                             OR
		                             UPPER(B.BRAND_ENM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                             OR
		                             UPPER(B.BRAND_KNM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                            )
		                    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		                    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		                    AND    G.SELL_STDT <![CDATA[<=]]> NOW() AND G.SELL_EDDT >= NOW() /*유효한 판매기간*/
		                  )
		</if>
		ORDER  BY DISP_ORD
	</select>
	
	<!-- 카테고리5 목록 -->
	<select id="getCategory5List" parameterType="Cate4Srch" resultType="Cate5">
		/* TsfDisplay.getCategory5List */
		SELECT SITE_CD
		     , CATE_GB
		     , CATE1_NO
		     , CATE2_NO
		     , CATE3_NO
		     , CATE4_NO
		     , CATE5_NM
		     , CATE5_NM
		     , LEAF_YN
		FROM   TB_CATE5 A
		WHERE  SITE_CD = #{siteCd} /*사이트코드*/
		AND    CATE_GB = #{cateGb} /*카테고리구분*/
		AND    CATE_TYPE = #{cateType} /*카테고리유형*/
		AND    CATE1_NO = #{cate1No} /*카테고리1번호*/
		AND    CATE2_NO = #{cate2No} /*카테고리2번호*/
		AND    CATE3_NO = #{cate3No} /*카테고리3번호*/
		AND    CATE4_NO = #{cate4No} /*카테고리4번호*/
		AND    DISP_YN = 'Y'
		AND    USE_YN = 'Y'
		AND    EXISTS (SELECT 1
		               FROM   TB_CATE_STOCK
		               WHERE  SITE_CD = A.SITE_CD
		               AND    CATE_NO = A.CATE5_NO
		               <if test='brandGroupNo != null'>
		               AND    BRAND_GROUP_NO = #{brandGroupNo}
		               </if>
		               <if test='formalGb != null'>
		               AND    FORMAL_GB = #{formalGb}
		               </if>
		               <if test='soldoutGoodsDispYn != null and soldoutGoodsDispYn =="N"'>
		               AND    STOCK_QTY > 0 /*품절상품전시여부가 N일 때 재고가 있는 카테고리만*/
		               </if>
		              )
		<if test="keyword != null and keyword != ''"> <!-- 상품검색키워드 -->
		AND    CATE5_NO IN (SELECT DISTINCT C4.CATE5_NO
		                    FROM   TB_CATE_4SRCH C4
		                         , TB_CATE_GOODS CG
		                         , TB_GOODS G USE INDEX (IX_GOODS_11)
		                         , TB_GOODS_STOCK S
		                         , TB_BRAND B
		                    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
		                    AND    CG.GOODS_CD = G.GOODS_CD
		                    AND    CG.GOODS_CD = S.GOODS_CD
		                    AND    G.BRAND_CD = B.BRAND_CD
		                    AND    C4.SITE_CD = #{siteCd}
		                    AND    C4.CATE_GB = #{cateGb}
		                    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		                    AND    (
		                             G.GOODS_NUM LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                             OR
		                             G.GOODS_CD LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                             OR
		                             UPPER(G.GOODS_NM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                             OR
		                             UPPER(B.BRAND_ENM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                             OR
		                             UPPER(B.BRAND_KNM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                           )
		                    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		                    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		                    AND    G.SELL_STDT <![CDATA[<=]]> NOW() AND G.SELL_EDDT >= NOW() /*유효한 판매기간*/
		                  )
		</if>
		ORDER  BY DISP_ORD
	</select>

	<!-- 메인 레이아웃 목록 -->
	<select id="getMainLayout" parameterType="MainLayout" resultType="MainLayout">
		/* TsfDisplay.getMainLayout */
		SELECT A.CATE_NO
			 , A.CONTENTS_LOC
		     , A.BRAND_GROUP_NO
			 , A.DISP_ORD
			 , A.CONTENTS_YN
			 , A.CONTENTS_TITLE
			 , A.COL_CNT
			 , A.COL_NO
		<choose>
		    <when test='preview != null and preview == "Y"'>
		    , (SELECT COUNT(1) AS CNT
		         FROM TB_CONTENTS_PREVIEW CP
		        WHERE 1=1
		          AND A.CONTENTS_LOC = CP.CONTENTS_LOC
		          AND A.CATE_NO = CP.CATE_NO
		        <if test="brandGroupNo != null and brandGroupNo !=''">
		          AND A.BRAND_GROUP_NO = CP.BRAND_GROUP_NO
		        </if>
		          AND NOW() BETWEEN CP.DISP_STDT AND CP.DISP_EDDT
		          AND CP.USE_YN = 'Y'
		        ) AS CONTENTS_CNT
		    </when>
		    <otherwise>
		    , (SELECT COUNT(1) AS CNT
		         FROM TB_CONTENTS CP
		        WHERE 1=1
		          AND A.CONTENTS_LOC = CP.CONTENTS_LOC
		          AND A.CATE_NO = CP.CATE_NO
		    <if test="brandGroupNo != null and brandGroupNo !=''">
		          AND A.BRAND_GROUP_NO = CP.BRAND_GROUP_NO
		    </if>
		          AND NOW() BETWEEN CP.DISP_STDT AND CP.DISP_EDDT
		          AND CP.USE_YN = 'Y'
		        ) AS CONTENTS_CNT
		    </otherwise>
		</choose>
		FROM   TB_MAIN_LAYOUT A
		WHERE  A.CATE_NO = #{cateNo}
		<if test="brandGroupNo != null and brandGroupNo !=''">
		  AND  A.BRAND_GROUP_NO  = #{brandGroupNo}
		</if>
		ORDER BY A.DISP_ORD, A.COL_NO
	</select>

	<!-- 카테고리4srch 조회-->
	<select id="getCate4srch" parameterType="Cate4Srch" resultType="Cate4Srch">
		/* TsfDisplay.getCate4srch */
		SELECT SITE_CD
		    , CATE_GB
			, LEAF_CATE_NO
			, CATE_TYPE
			, CATE1_NO
			, CATE1_NM
			, CATE2_NO
			, CATE2_NM
			, CATE3_NO
			, CATE3_NM
			, CATE4_NO
			, CATE4_NM
			, FULL_CATE_NO
			, FULL_CATE_NM
			, DISP_ORD
			, FORMAL_GB
			, CONTENTS_LOC
		FROM TB_CATE_4SRCH
		WHERE 1 = 1
		<if test='leafCateNo != null and leafCateNo != ""'>
			AND LEAF_CATE_NO = #{leafCateNo}
		</if>
		<if test='cate1No != null and cate1No != ""'>
			AND CATE1_NO = #{cate1No}
		</if>
		<if test='contentsLoc != null and contentsLoc != ""'>
			AND CONTENTS_LOC = #{contentsLoc}
		</if>
	</select>
	
	<!-- 팝업 목록 -->
	<select id="getPopupList" parameterType="PopUp" resultType="PopUp">
		/* TsfDisplay.getPopupList */
		SELECT *
		FROM (
		    SELECT POPUP_SQ
		         , POPUP_GB
		         , IMG_URL1
		         , LINK_URL1
		         , UNEXP_DAYS
		         , LINK_TARGET
		         , ROW_NUMBER() OVER( ORDER BY DISP_ORD, REG_DT DESC) AS RNUM
		    <choose>
		         <when test='preview != null and preview == "Y"'>
		         FROM   TB_POPUP_PREVIEW
		         </when>
		         <otherwise>
		         FROM   TB_POPUP
		         </otherwise>
		    </choose>
		    WHERE  1 = 1
		    <choose>
		    <when test='frontGb != null and frontGb =="P"'>
		    AND FRONT_GB = #{frontGb}
		    </when>
		    <otherwise>
		    AND FRONT_GB = 'M'
		    </otherwise>
		    </choose>
		    <if test="popupGb != null and popupGb != ''">
		    AND POPUP_GB = #{popupGb}
		    </if>
		    AND ( VIEW_PAGE = 'G037_10'
		    <if test='viewPage != null and viewPage !=""'>
		          OR ( VIEW_PAGE = #{viewPage}
		               <if test='cateNo != null and cateNo > 0'>
		               AND CATE_NO = #{cateNo}
		               </if>
		               <if test='planSq != null and planSq !=""'>
		               AND PLAN_SQ = #{planSq}
		               </if>
		                <if test='brandCd != null and brandCd !=""'>
		               AND BRAND_CD = #{brandCd}
		               </if>
		             )
		    </if>
		         )
		    AND USE_YN = 'Y'
		    AND SITE_CD = #{siteCd}
		    <choose>
		    <when test='preview != null and preview == "Y"'>
		    AND  DISP_STDT <![CDATA[<=]]> STR_TO_DATE(#{viewDt},'%Y%m%d%H%i%S')
		    AND DISP_EDDT >= STR_TO_DATE(#{viewDt},'%Y%m%d%H%i%S')
		    </when>
		    <otherwise>
		    AND DISP_STDT <![CDATA[ <= ]]> NOW()
		    AND DISP_EDDT >= NOW()
		    </otherwise>
		    </choose>
		    ORDER BY POPUP_GB, DISP_ORD, REG_DT DESC
		) Z 
	</select>
	

	<!-- 몰메인 브랜드픽 조회 -->
	<select id="getContentsBannerList" parameterType="Contents" resultType="Contents">
		/* TsfDisplay.getContentsBannerList */
		WITH CONTENTS_DATA AS (
		    SELECT BRAND_GROUP_NO
		         , IMG_PATH1
		         , STR_VAR1
		         , STR_TITLE1
		         , SUB_TEXT1
		    FROM TB_CONTENTS
		    WHERE 1 = 1
		      AND CONTENTS_LOC =  #{contentsLoc}
		      AND NOW() BETWEEN DISP_STDT AND DISP_EDDT
		      AND USE_YN = 'Y'
		      AND BRAND_GROUP_NO =  #{brandGroupNo}
		      AND IMG_PATH1 IS NOT NULL
		      AND IMG_PATH1 <![CDATA[<>]]> ''
		    UNION ALL
		    SELECT BRAND_GROUP_NO
		         , IMG_PATH2
		         , STR_VAR2
		         , STR_TITLE2
		         , SUB_TEXT2
		    FROM TB_CONTENTS
		    WHERE 1 = 1
		      AND CONTENTS_LOC =  #{contentsLoc}
		      AND NOW() BETWEEN DISP_STDT AND DISP_EDDT
		      AND USE_YN = 'Y'
		      AND BRAND_GROUP_NO =  #{brandGroupNo}
		      AND IMG_PATH2 IS NOT NULL
		      AND IMG_PATH2 <![CDATA[<>]]> ''
		    UNION ALL
		    SELECT BRAND_GROUP_NO
		         , IMG_PATH3
		         , STR_VAR3
		         , STR_TITLE3
		         , SUB_TEXT3
		    FROM TB_CONTENTS
		    WHERE 1 = 1
		      AND CONTENTS_LOC =  #{contentsLoc}
		      AND NOW() BETWEEN DISP_STDT AND DISP_EDDT
		      AND USE_YN = 'Y'
		      AND BRAND_GROUP_NO =  #{brandGroupNo}
		      AND IMG_PATH3 IS NOT NULL
		      AND IMG_PATH3 <![CDATA[<>]]> ''
		    UNION ALL
		    SELECT BRAND_GROUP_NO
		         , IMG_PATH4
		         , STR_VAR4
		         , STR_TITLE4
		         , SUB_TEXT4
		    FROM TB_CONTENTS
		    WHERE 1 = 1
		      AND CONTENTS_LOC =  #{contentsLoc}
		      AND NOW() BETWEEN DISP_STDT AND DISP_EDDT
		      AND USE_YN = 'Y'
		      AND BRAND_GROUP_NO =  #{brandGroupNo}
		      AND IMG_PATH4 IS NOT NULL
		      AND IMG_PATH4 <![CDATA[<>]]> ''
		    UNION ALL
		    SELECT BRAND_GROUP_NO
		         , IMG_PATH5
		         , STR_VAR5
		         , STR_TITLE5
		         , SUB_TEXT5
		    FROM TB_CONTENTS
		    WHERE 1 = 1
		      AND CONTENTS_LOC =  #{contentsLoc}
		      AND NOW() BETWEEN DISP_STDT AND DISP_EDDT
		      AND USE_YN = 'Y'
		      AND BRAND_GROUP_NO =  #{brandGroupNo}
		      AND IMG_PATH5 IS NOT NULL
		      AND IMG_PATH5 <![CDATA[<>]]> ''
		)
		SELECT BRAND_GROUP_NO
		     , IMG_PATH1
		     , IMG_PATH2
		     , STR_VAR1
		     , STR_VAR2
		     , STR_TITLE1
		     , STR_TITLE2
		     , SUB_TEXT1
		     , SUB_TEXT2
		FROM (
		        SELECT BRAND_GROUP_NO
		             , IMG_PATH1
		             , '' AS IMG_PATH2
		             , STR_VAR1
		             , '' AS STR_VAR2
		             , STR_TITLE1
		             , '' AS STR_TITLE2
		             , SUB_TEXT1
		             , '' AS SUB_TEXT2
		        FROM CONTENTS_DATA
		        UNION ALL
		         SELECT BRAND_GROUP_NO
		              , IMG_PATH1
		              , IMG_PATH2
		              , STR_VAR1
		              , STR_VAR2
		              , STR_TITLE1
		              , STR_TITLE2
		              , SUB_TEXT1
		              , SUB_TEXT2
		         FROM (
		              SELECT BRAND_CD                      AS BRAND_GROUP_NO
		                   , ORG_TNFILE_NM                 AS IMG_PATH1
		                   , ''                            AS IMG_PATH2
		                   , CONCAT('/display/lookbook/detail/form?lookbookGb=L&amp;lookbookSq=',LOOKBOOK_SQ ,'&amp;brandCd=',BRAND_CD) AS STR_VAR1
		                   , ''                            AS STR_VAR2
		                   , TITLE                         AS STR_TITLE1
		                   , ''                            AS STR_TITLE2
		                   , ''                            AS SUB_TEXT1
		                   , ''                            AS SUB_TEXT2
		              FROM TB_LOOKBOOK
		              WHERE 1 = 1
		                AND BRAND_CD = #{brandGroupNo}
		                AND NOW() BETWEEN DISP_STDT AND DISP_EDDT
		                AND DISP_YN = 'Y'
		              ORDER BY REG_DT DESC
		              LIMIT 1
		              ) A
		         UNION ALL
		         SELECT PB.BRAND_GROUP_NO
		              , P.MAIN_PIMG                                        AS IMG_PATH1
		              , ''                                                 AS IMG_PATH2
		              , CONCAT('/planning/detail/form?planSq=', P.PLAN_SQ) AS STR_VAR1
		              , ''                                                 AS STR_VAR2
		              , P.PLAN_NM                                          AS STR_TITLE1
		              , ''                                                 AS STR_TITLE2
		              , P.DTL_TITLE1                                       AS SUB_TEXT1
		              , P.DTL_TITLE2                                       AS SUB_TEXT2
		         FROM TB_PLAN P
		         INNER JOIN TB_PLAN_BRAND PB ON P.PLAN_SQ = PB.PLAN_SQ AND PB.DISP_YN = 'Y'
		         WHERE 1 = 1
		           AND BRAND_GROUP_NO = #{brandGroupNo}
		           AND NOW() BETWEEN DISP_STDT AND DISP_EDDT
		           AND PLAN_GB = 'P'
		           AND DEL_YN = 'N'
		           AND OPEN_YN = 'Y'
		        <if test="frontGb != null and frontGb != ''">
		           AND FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		        </if>
		     ) AS Z
		LIMIT 5
	</select>

	<!-- 상품리스트 카테고리별 필터 목록 -->
	<select id="getGoodsListCategoryFilterList" parameterType="SearchEngine" resultType="Filter">
		/* TsfDisplay.getGoodsListCategoryFilterList */
		SELECT
		    DISTINCT
		       FILTER_GB
		     , CASE WHEN FILTER_GB = 'SIZE' THEN
		                SUBSTRING_INDEX(FILTER_CD,'|',1)
		            ELSE
		                FILTER_CD
		       END       AS FILTER_CD
		     , DISP_ORD
		     , FILTER_NM
		FROM   TB_CATE_FILTER
		WHERE  SITE_CD = #{siteCd}
		AND    CATE_GB = #{cateGb}
		<if test="formalGb != null and formalGb != ''">
		AND    FORMAL_GB = #{formalGb}
		</if>
		AND    BRAND_GROUP_NO = #{brandGroupNo}
		  AND    CATE_NO = CASE WHEN IFNULL(#{cate4No},0) > 0 THEN
		                                    #{cate4No}
		                             ELSE
		                                  CASE WHEN IFNULL(#{cate3No},0) > 0 THEN
		                                           #{cate3No}
		                                       ELSE
		                                           CASE WHEN IFNULL(#{cate2No},0) > 0 THEN
		                                                     #{cate2No}
		                                                ELSE
		                                                    IFNULL(#{cate1No},0)
		                                            END
		                                  END
		                             END
		 ORDER  BY FILTER_GB, DISP_ORD
	</select>
	
	<!-- 검색상품리스트 카테고리별 필터 목록 -->
	<select id="getSearchGoodsListCategoryFilterList" parameterType="SearchEngine" resultType="Filter">
		/* TsfDisplay.getSearchGoodsListCategoryFilterList */
		SELECT DISTINCT
		       FILTER_GB
		     , CASE WHEN FILTER_GB = 'SIZE' THEN
		                SUBSTRING_INDEX(FILTER_CD,'|',1)
		            ELSE
		                FILTER_CD
		       END       AS FILTER_CD
		     , DISP_ORD
		     , FILTER_NM
		FROM   TB_CATE_FILTER
		WHERE  SITE_CD = #{siteCd}
		AND    CATE_GB = #{cateGb}
		AND    FORMAL_GB = #{formalGb}
		AND    BRAND_GROUP_NO = #{brandGroupNo}
		AND    CATE_NO IN (SELECT DISTINCT C4.LEAF_CATE_NO
		                   FROM   TB_CATE_4SRCH C4
		                        , TB_CATE_GOODS CG
		                        , TB_GOODS G
		                        , TB_GOODS_STOCK S
		                        , TB_BRAND B
		                   WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
		                   AND    CG.GOODS_CD = G.GOODS_CD
		                   AND    CG.GOODS_CD = S.GOODS_CD
		                   AND    G.BRAND_CD = B.BRAND_CD
		                   AND    C4.SITE_CD = #{siteCd}
		                   AND    C4.CATE_GB = #{cateGb}
		                   AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		                   AND    (
		                           G.GOODS_NUM LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                           OR
		                           G.GOODS_CD LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                           OR
		                           UPPER(G.GOODS_NM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                           OR
		                           UPPER(B.BRAND_ENM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                           OR
		                           UPPER(B.BRAND_KNM) LIKE CONCAT('%',UPPER(#{keyword}),'%')
		                          )
		                   AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		                   AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		                   AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*유효한 판매기간*/
		                  )
		ORDER  BY FILTER_GB, DISP_ORD
	</select>
	
	<!-- 상품 카테고리 필터 -->
	<select id="getCategoryFilter" parameterType="Cate4srch" resultType="GoodsSearch">
		/* TsfDisplay.getCategoryFilter */
		SELECT
		<choose>
		     <when test="filterGb != null and filterGb =='SIZE'">
		SUBSTRING_INDEX(FILTER_CD,'|',1) AS FILTER_CD
		     </when>
		     <otherwise>
		FILTER_CD
		     </otherwise>
		</choose>
		, FILTER_NM
		FROM TB_CATE_FILTER
		WHERE 1=1
		AND SITE_CD = #{siteCd}
		AND CATE_GB = #{cateGb}
		AND FORMAL_GB = #{formalGb}
		AND BRAND_GROUP_NO = #{brandGroupNo}
		<if test="cateNo != null and cateNo != ''">
		AND CATE_NO = #{cateNo}
		</if>
		AND FILTER_GB = #{filterGb}
		GROUP BY FILTER_CD, FILTER_NM
		ORDER BY DISP_ORD
	</select>

	<!-- 상품 카테고리 필터 중 혜택 -->
	<select id="getCategoryFilterBenefit" parameterType="Cate4srch" resultType="GoodsSearch">
		/* TsfDisplay.getCategoryFilterBenefit */
		SELECT	GB.BENEFIT_GB AS FILTER_CD
		      , 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 '신상'
		             WHEN GB.BENEFIT_GB = '50' THEN '총알배송'
		             ELSE '' END AS FILTER_NM
		FROM	TB_GOODS_BENEFIT GB
		INNER JOIN TB_CATE_GOODS CG ON GB.GOODS_CD = CG.GOODS_CD
		WHERE	1=1
		  AND	EXISTS (
		                  SELECT 1
		                  FROM	 TB_CATE_4SRCH
		                  WHERE	 1=1
		                    AND	 LEAF_CATE_NO = CG.CATE_NO
		                  <if test="cate1No != null and cate1No != ''">
		                    AND CATE1_NO = #{cate1No}
		                  </if>
		                  <if test="cate2No != null and cate2No != ''">
		                    AND CATE2_NO = #{cate2No}
		                  </if>
		                  <if test="cate3No != null and cate3No != ''">
		                    AND CATE3_NO = #{cate3No}
		                  </if>
		                  <if test="cate4No != null and cate4No != ''">
		                    AND CATE4_NO = #{cate4No}
		                  </if>
		                  <if test="cate5No != null and cate5No != ''">
		                    AND CATE5_NO = #{cate5No}
		                  </if>
		                )
		<if test="brandGroupNo != null and brandGroupNo > 0">
		AND   CG.BRAND_GROUP_NO = #{brandGroupNo}
		</if>
		GROUP BY GB.BENEFIT_GB
	</select>

	<!-- 카테고리 상품별  목록 필터 조건 -->
	<sql id="getCategoryGoodsList_sql">
		<if test='brandSearch != null and brandSearch.length > 0'>
		     AND EXISTS ( SELECT 1 FROM TB_BRAND
		     WHERE BRAND_CD = G.BRAND_CD
		     AND BRAND_GROUP_NO IN
		     <foreach collection="brandSearch" item="item" index="index" open="(" close=")" separator=",">
		        #{item}
		     </foreach>
		     )
		</if>
		<if test='sizeSearch != null and sizeSearch.length > 0'>
		     AND G.GOODS_CD IN (
		          SELECT GOODS_CD
		          FROM VW_STOCK
		          WHERE OPT_CD2 IN
		     <foreach collection="sizeSearch" item="item" index="index"  open="(" close=")" separator=",">
		         #{item}
		     </foreach>
		     )
		</if>
		<if test='colorSearch != null and colorSearch.length > 0'>
		     AND G.MAIN_COLOR_CD IN (
		             SELECT O.OPT_CD1
		             FROM TB_OPTION O
		             WHERE	1=1
		             AND	O.OPT_CD1 IN (
		                    SELECT	CD
		                    FROM	TB_COMMON_CODE
		                    WHERE	1=1
		                    AND		CD_GB = 'G072'
		                    AND		CD_DESC IN
		                    <foreach collection="colorSearch" item="item" index="index"  open="(" close=")" separator=",">
		                         #{item}
		                    </foreach>
		             )
		             AND O.GOODS_CD = G.GOODS_CD
		     )
		</if>
		<if test='seasonSearch != null and seasonSearch.length > 0'>
		     AND G.SEASON_CD IN
		    <foreach collection="seasonSearch" item="item" index="index"  open="(" close=")" separator=",">
		         #{item}
		    </foreach>
		</if>
		<if test='benefitSearch != null and benefitSearch.length > 0'>
			AND EXISTS ( SELECT 1 FROM TB_GOODS_BENEFIT
			WHERE GOODS_CD = G.GOODS_CD
			AND BENEFIT_GB IN
			<foreach collection="benefitSearch" item="item" index="index" open="(" close=")" separator=",">
				#{item}
			</foreach>
			)
		</if>
	</sql>
	
	<!-- 몰메인 베스트아이템 조회 -->
	<select id="getBestItemCategoryList" parameterType="Contents" resultType="Contents">
		/* TsfDisplay.getBestItemCategoryList */
		SELECT
		       SITE_CD
		     , CATE_GB
		     , CATE1_NO AS CATE_NO
			 , CATE1_NM AS CATE_NM
             , CONTENTS_LOC
		FROM  TB_CATE_4SRCH
		WHERE 1=1
		AND CATE_TYPE = 'G031_20'
		AND	CONTENTS_LOC = #{contentsLoc}
		<if test="cateNo != null and cateNo != ''">
		AND CATE1_NO = #{cateNo}
		</if>
	</select>

	<!-- 브랜드 전체 이미지 리스트 조회 -->
	<select id="getBrandImgList" parameterType="BrandGroup" resultType="BrandGroup">
		/* TsfDisplay.getBrandImgList */
		WITH TAB AS (
		    SELECT  CAST(STR_TITLE3 AS UNSIGNED) AS STR_TITLE3
		    FROM   TB_CONTENTS
		    WHERE  1=1
		    AND    CONTENTS_LOC = 'STAB001'
		    AND    DISP_STDT <![CDATA[<=]]> NOW()
		    AND    DISP_EDDT <![CDATA[>=]]> NOW()
		    GROUP BY  CAST(STR_TITLE3 AS UNSIGNED)
		    ORDER BY  CAST(STR_TITLE3 AS UNSIGNED)
		    LIMIT 1
		)
		SELECT	Z.BRAND_GROUP_NO
		     , FN_GET_BRAND_INITIAL(Z.BRAND_GROUP_NM) AS BRAND_INITIAL
		     , Z.BRAND_GROUP_NM
		     , Z.DISP_NM_LANG
		     , Z.BRAND_GROUP_ENM
		     , Z.BRAND_GROUP_KNM
		     , Z.LOGO_FILE_NM
		FROM (
		    SELECT  BG.BRAND_GROUP_NO
		          , (CASE WHEN BG.DISP_NM_LANG = 'EN' THEN BG.BRAND_GROUP_ENM ELSE BG.BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
		          , BG.DISP_NM_LANG
		          , BG.BRAND_GROUP_ENM
		          , BG.BRAND_GROUP_KNM
		          , BG.LOGO_FILE_NM
		    FROM  TAB T
		    JOIN  TB_CONTENTS C ON T.STR_TITLE3 = C.STR_TITLE3
		    LEFT JOIN TB_BRAND B ON C.STR_VAR1 = B.BRAND_GROUP_NO
		    INNER JOIN TB_BRAND_GROUP BG ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		    WHERE	1=1
		    AND		B.USE_YN = 'Y'
		    AND		BG.USE_YN = 'Y'
		    AND     EXISTS (
		                SELECT 1
		                FROM   TB_GOODS G
		                WHERE  G.BRAND_CD = B.BRAND_CD
		                AND    G.GOODS_STAT = 'G008_90'
		            )
		<if test='selfYn != null and selfYn == "Y"'>
		    <!-- AND		B.SELF_YN = 'Y'  21.07.28 기획 한리더 요청으로 입점 브랜드도 노출하도록 수정 -->
		    AND		(BG.LOGO_FILE_NM <![CDATA[<>]]> '' AND BG.LOGO_FILE_NM IS NOT NULL)
		</if>
		<if test="brandGroupNo != null and brandGroupNo != ''">
		    AND		B.BRAND_GROUP_NO = #{brandGroupNo}
		</if>
		<if test="brandGroupNm != null and brandGroupNm != ''">
		    AND		(BG.BRAND_GROUP_ENM LIKE CONCAT('%',#{brandGroupNm},'%') OR BG.BRAND_GROUP_KNM LIKE CONCAT('%',#{brandGroupNm},'%'))
		</if>
		GROUP BY  C.STR_TITLE2, C.DISP_ORD , BG.BRAND_GROUP_NO
		) Z
		WHERE	1=1
	</select>

	<!-- 브랜드 전체 조회 -->
	<select id="getAllBrandList" parameterType="BrandGroup" resultType="BrandGroup">
		/* TsfDisplay.getAllBrandList */
		SELECT	DISTINCT Z.BRAND_GROUP_NO
		      , Z.BRAND_INITIAL
		      , Z.BRAND_GROUP_NM
		      , Z.DISP_NM_LANG
		      , Z.BRAND_GROUP_ENM
		      , Z.BRAND_GROUP_KNM
		      , Z.LOGO_FILE_NM
		FROM (
		      SELECT  BG.BRAND_GROUP_NO
		            , (CASE WHEN BG.DISP_NM_LANG = 'EN' THEN BG.BRAND_GROUP_ENM ELSE BG.BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
		            , BG.DISP_NM_LANG
		            , BG.BRAND_GROUP_ENM
		            , BG.BRAND_GROUP_KNM
		            , BG.LOGO_FILE_NM
		            , BG.BRAND_INITIAL
		      FROM  TB_BRAND B
		      INNER JOIN TB_BRAND_GROUP BG ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		     -- INNER JOIN EXIST_BRAND EB ON B.BRAND_CD = EB.BRAND_CD
		      WHERE	1=1
		      AND	B.USE_YN = 'Y'
		      AND	BG.USE_YN = 'Y'
		      AND   BG.DISP_YN = 'Y' -- 추가 
		      <if test='selfYn != null and selfYn == "Y"'>
		      AND	B.SELF_YN = 'Y'
		      AND	(BG.LOGO_FILE_NM <![CDATA[<>]]> '' AND BG.LOGO_FILE_NM IS NOT NULL)
		      </if>
		      <if test="brandGroupNo != null and brandGroupNo != ''">
		      AND	B.BRAND_GROUP_NO = #{brandGroupNo}
		      </if>
		      <if test="brandGroupNm != null and brandGroupNm != ''">
		      AND	(BG.BRAND_GROUP_ENM LIKE CONCAT('%',#{brandGroupNm},'%') OR BG.BRAND_GROUP_KNM LIKE CONCAT('%',#{brandGroupNm},'%'))
		      </if>
		      GROUP BY BG.BRAND_GROUP_NO, B.BRAND_ENM, B.BRAND_KNM
		) Z
		WHERE	1=1
		ORDER BY (CASE  WHEN ASCII(SUBSTRING(Z.BRAND_GROUP_NM,1)) BETWEEN 48 AND 57 THEN 3
		                WHEN ASCII(SUBSTRING(Z.BRAND_GROUP_NM,1)) <![CDATA[<]]> 128 THEN 2 ELSE 1 END), Z.BRAND_GROUP_NM
	</select>
	
	<!-- 브랜드그룹의 기본카테고리여부 조회 -->
	<select id="getBrandGroupDefaultCategoryYn" parameterType="Integer" resultType="String">
		/* TsfDisplay.getBrandGroupDefaultCategoryYn */
		SELECT DEFAULT_CATE_YN
		FROM   TB_BRAND_GROUP
		WHERE  BRAND_GROUP_NO = #{brandGroupNo}
	</select>

	<!-- GNB TAB 기획전배너 조회 -->
	<select id="getGnbTabBannerList" parameterType="Contents" resultType="Contents">
		/* TsfDisplay.getGnbTabBannerList */
		SELECT
		       <choose>
				   <when test="contentsLoc != null and contentsLoc =='SOM005'">
					   (SELECT CONTENTS_TITLE FROM TB_MAIN_LAYOUT WHERE 1=1 AND CATE_NO = 1713 AND CONTENTS_LOC = #{contentsLoc}) AS GNB_TAB_TITLE
				   </when>
				   <otherwise>
					   (SELECT CONTENTS_TITLE FROM TB_MAIN_LAYOUT WHERE 1=1 AND CATE_NO = #{cateNo} AND CONTENTS_LOC = #{contentsLoc}) AS GNB_TAB_TITLE
				   </otherwise>
			   </choose>
		      , Z.BANNER_GB
		      , Z.IMG_PATH1
		      , Z.STR_VAR1
		      , Z.STR_TITLE1
		      , Z.STR_TITLE2
		      , Z.SUB_TEXT1
		      , Z.SUB_TEXT2
		      , Z.DISP_ORD
		FROM    (
		    SELECT *
		    FROM   (
		            SELECT  'ADMIN' AS BANNER_GB
		                  , IMG_PATH1
		                  , STR_VAR1
		                  , STR_TITLE1
		                  , STR_TITLE2
		                  , SUB_TEXT1
		                  , SUB_TEXT2
		                  , DISP_ORD
		            FROM    TB_CONTENTS
		            WHERE   1=1
		              <choose>
		                  <when test="contentsLoc != null and contentsLoc =='SOM005'">
		            AND CATE_NO = 1713
		                  </when>
		                  <otherwise>
		            AND     CATE_NO = #{cateNo}
		                  </otherwise>
		              </choose>
		            AND     CONTENTS_LOC = #{contentsLoc}
		            AND     USE_YN = 'Y'
		            AND    DISP_STDT <![CDATA[<=]]> NOW()
		            AND    DISP_EDDT <![CDATA[>=]]> NOW()
		            ORDER BY  DISP_ORD
		        <if test="maxRow != null and maxRow != ''">
		            LIMIT #{maxRow}
		        </if>
		    ) AS TC
		            UNION ALL
		            SELECT  'PLAN' AS BANNER_GB
		                  , A.MAIN_PIMG AS IMG_PATH1
		                  , CONCAT('/planning/detail/form?planSq=',A.PLAN_SQ) AS STR_VAR1
		                  , A.PLAN_NM AS STR_TITLE1
		                  , '' AS STR_TITLE2
		                  , A.DTL_TITLE1 AS SUB_TEXT1
		                  , '' AS SUB_TEXT2
		                  , SUM(A.DISP_ORD+100) AS DISP_ORD
		            FROM  TB_PLAN A
		            INNER JOIN TB_PLAN_CATE B ON A.PLAN_SQ = B.PLAN_SQ
		            WHERE  1=1
		              AND  B.CATE_NO = #{cateNo}
		              AND  A.OPEN_YN = 'Y'
		              AND  A.DEL_YN = 'N'
		              AND  B.DISP_YN = 'Y'
		              AND  A.DISP_STDT <![CDATA[<=]]> NOW()
		              AND  A.DISP_EDDT <![CDATA[>=]]> NOW()
		            GROUP BY  A.MAIN_PIMG, A.PLAN_SQ, A.PLAN_NM, A.DTL_TITLE1
		        ) Z
		WHERE  1=1
		ORDER BY  Z.DISP_ORD
		<if test="maxRow != null and maxRow != ''">
			LIMIT #{maxRow}
		</if>
	</select>

	<select id="getSearchKeywordList" parameterType="SearchKeyword" resultType="SearchKeyword">
		/* TsfDisplay.getSearchKeywordList */
		SELECT
		       KEYWORD_SQ
		     , KEYWORD
		     , LINK_URL
		     , DISP_STDT
		     , DISP_EDDT
		FROM TB_SEARCH_KEYWORD
		WHERE 1=1
		AND NOW() BETWEEN DISP_STDT AND DISP_EDDT
		ORDER BY KEYWORD_SQ
		LIMIT 1
	</select>
	
	<!-- 스플래시이미지 -->
	<select id="getSplashImage" resultType="String">
		/* TsfDisplay.getSplashImage */
		SELECT SPLASH_IMG
		FROM   TB_SPLASH_IMG SI
		WHERE  NOW() BETWEEN DISP_STDT AND DISP_EDDT
		AND    DISP_YN = 'Y'
		ORDER  BY SPLASH_IMG_SQ DESC
		LIMIT 1
	</select>

	<!-- 브랜드픽 타이틀 브랜드 조회 -->
	<select id="getBrandpickTitleBrandList" parameterType="Contents" resultType="Contents">
		/* TsfDisplay.getBrandpickTitleBrandList */
		SELECT  T.BRAND_GROUP_NO
		      , T.BRAND_GROUP_NM
		      , T.BRAND_GROUP_IMG
		      , ROW_NUMBER() OVER() AS DISP_ORD
		      , T.CATE_NO
		      , T.CONTENTS_LOC
		FROM  (
		         SELECT
		                A.BRAND_GROUP_NO                                     /*브랜드그룹번호*/
		              , (SELECT FN_GET_BRAND_GROUP_NM(A.BRAND_GROUP_NO)) AS BRAND_GROUP_NM
		              , (SELECT LOGO_FILE_NM FROM TB_BRAND_GROUP WHERE 1=1 AND A.BRAND_GROUP_NO = BRAND_GROUP_NO) AS BRAND_GROUP_IMG		/*브랜드그룹이미지경로*/
		              , A.CATE_NO
		              , A.CONTENTS_LOC
		         FROM
		         <choose>
		            <when test='preview != null and preview == "Y"'>
		                TB_CONTENTS_PREVIEW A
		            </when>
		            <otherwise>
		                TB_CONTENTS A
		            </otherwise>
		         </choose>
		         WHERE  A.CONTENTS_LOC = #{contentsLoc}
		         AND    A.USE_YN = 'Y'
		        <if test="cateNo != null and cateNo != ''">
		         AND    A.CATE_NO = #{cateNo}
		        </if>
		        <choose>
		            <when test='preview != null and preview == "Y"'>
		          AND    A.DISP_STDT <![CDATA[<=]]> STR_TO_DATE(#{viewDt},'%Y%m%d%H%i%S')
		          AND    A.DISP_EDDT <![CDATA[>=]]> STR_TO_DATE(#{viewDt},'%Y%m%d%H%i%S')
		            </when>
		            <otherwise>
		          AND    A.DISP_STDT <![CDATA[<=]]> NOW()
		          AND    A.DISP_EDDT <![CDATA[>=]]> NOW()
		            </otherwise>
		        </choose>
		         ORDER BY  A.DISP_ORD
		      ) T
		WHERE  1=1
		GROUP BY  T.BRAND_GROUP_NO, T.CATE_NO, T.CONTENTS_LOC
	</select>
	
</mapper>
