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

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

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>
	
	<!-- 상품상세보기이력 생성 -->
	<insert id="createGoodsViewHst" parameterType="GoodsViewHst">
		/* TsfGoods.createGoodsViewHst */
		INSERT INTO TB_GOODS_VIEW_HST
		    (
		    GOODS_CD
		  , JSESSION_ID
		  , SITE_CD
		  , FRONT_GB
		  , AF_LINK_CD
		  , ITHR_CD
		  , CONTENTS_LOC
		  , CUST_NO
		  , REFERER
		  , REG_DT
		    )
		SELECT #{goodsCd}
		     , #{jsessionId}
		     , #{siteCd}
		     , #{frontGb}
		     , #{afLinkCd}
		     , #{ithrCd}
		     , #{contentsLoc}
		     , #{custNo}
		     , #{referer}
		     , NOW()
		FROM DUAL
		WHERE EXISTS (SELECT 1
		              FROM TB_GOODS
		              WHERE GOODS_CD = #{goodsCd}
		              )
	</insert>
	
	<!-- 상품 네비게이션 조회 -->
	<select id="getGoodsNavigation" parameterType="Goods" resultType="Cate4Srch">
		/* TsfGoods.getGoodsNavigation */
		SELECT G.FORMAL_GB
		     , I.CATE_NO
		     , CATE1_NO
		     , CATE1_NM
		     , CATE2_NO
		     , CATE2_NM
		     , CATE3_NO
		     , CATE3_NM
		     , CATE4_NO
		     , CATE4_NM
		     , CATE5_NO
		     , CATE5_NM
		     , CATE_GB
		     , LEAF_CATE_NO
		FROM TB_GOODS G
		   , TB_ITEMKIND_CATE I
		   , TB_CATE_4SRCH A
		WHERE G.ITEMKIND_CD = I.ITEMKIND_CD
		AND I.CATE_NO = A.LEAF_CATE_NO
		AND G.GOODS_CD = #{goodsCd}
		AND A.CATE_TYPE = 'G031_10' -- 상품카테
		AND A.CATE_GB = #{cateGb}
		AND A.SITE_CD = #{siteCd}
		LIMIT 1
	</select>

	<!-- 상품 정보 -->
	<select id="getGoodsInfo" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getGoodsInfo */
		SELECT Z.*
		     , 100 - ROUND((CURR_PRICE / LIST_PRICE) * 100 ,0) AS DC_RATE 
		     , ((PNT_RATE * CURR_PRICE) / 100) AS PNT_AMT
		     , FN_GET_GOODS_NM(GOODS_NM,GOODS_GB,FOREIGN_BUY_YN,PARALLEL_IMPORT_YN,ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		     , IFNULL((SELECT  STOCK_QTY FROM TB_GOODS_STOCK WHERE GOODS_CD = #{goodsCd}),0) AS GOODS_STOCK_QTY
		FROM (
		SELECT G.GOODS_CD
		     , G.GOODS_NM
		     , G.GOODS_TNM
		     , G.GOODS_GB
		     , G.FOREIGN_BUY_YN
		     , G.PARALLEL_IMPORT_YN
		     , G.ORDER_MADE_YN
		     , G.BRAND_CD
		     , (CASE WHEN D.DISP_NM_LANG = 'EN' THEN D.BRAND_GROUP_ENM ELSE D.BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
		     , D.BRAND_GROUP_NO
		     , B.LOGO_FILE_NM
		     , G.ITEMKIND_CD
		     , G.FORMAL_GB
		     , G.LIST_PRICE
		     , FN_GET_APPLY_CPN1_PRICE(G.GOODS_CD, #{frontGb})  AS CURR_PRICE
		     , G.SELF_GOODS_YN
		     , G.GOODS_STAT
		     , (CASE WHEN #{frontGb} = 'P' THEN G.PNT_PRATE ELSE G.PNT_MRATE END) AS PNT_RATE
		     , (CASE WHEN #{frontGb} = 'P' THEN G.PRE_PPNT_USABLE_YN ELSE G.PRE_MPNT_USABLE_YN END) AS PRE_PNT_USABLE_YN
		     , G.MIN_ORD_QTY
		     , G.MAX_ORD_QTY
		     , G.DAY_MAX_ORD_QTY
		     , G.SEX_GB
		     , FN_GET_CODE_NM('G007',G.SEX_GB) AS SEX_NM
		     , E.DELV_FEE
		     , E.RTN_DELV_FEE
		     , E.MIN_ORD_AMT
		     , E.DELV_FEE_CRITE
		     , FN_GET_CODE_NM('G073',G.GOODS_GB) AS GOODS_GB_NM
		     , (SELECT NI_CLSF_CD
		        FROM TB_ITEMKIND
		        WHERE ITEMKIND_CD = G.ITEMKIND_CD) AS NI_CLSF_CD
		     , G.MAIN_COLOR_CD
		     , (IFNULL(NULLIF(#{colorCd},'') , G.MAIN_COLOR_CD )) AS COLOR_CD
		     , G.SUPPLY_COMP_CD
		     , G.SELF_MALL_YN
		     , G.AGE_GRP_CD
		     , G.GIFT_PACK_YN
		     , G.NEW_CUST_ORD_YN
		     , G.ADULT_YN
		     , G.GOODS_TYPE
		     , G.CHANGEABLE_YN
		     , G.RETURNABLE_YN
		     , G.ORIGIN_CD
		     , G.TOBE_FORM_YN
		     , FN_GET_CODE_NM('G076',G.ORIGIN_CD) AS ORIGIN_NM
		     , FN_GET_CODE_NM('G008',G.GOODS_STAT) AS GOODS_STAT_NM
		     , DATE_FORMAT(G.REG_DT ,'%Y%m%d%H%i%S') AS REG_DT
		     , DATE_FORMAT(G.UPD_DT ,'%Y%m%d%H%i%S') AS UPD_DT 
		     , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(#{colorCd}, IFNULL(G.MAIN_COLOR_CD,'XX')) AND DEFAULT_IMG_YN = 'Y') AS SYS_IMG_NM
		     , (SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(#{colorCd},'') , G.MAIN_COLOR_CD ) AND USE_YN  = 'Y') AS COLOR_NM
		     -- , BP.CURR_PRICE AS BENEFIT_PRICE
		     , (CASE WHEN W.GOODS_CD IS NULL THEN 'N' ELSE 'Y' END) AS WISH_YN
		     , (CASE WHEN G.GOODS_TYPE = 'G056_N' THEN (SELECT IFNULL(MAX(CASE WHEN SOLDOUT_YN = 'Y' THEN 0
		                                                               ELSE CURR_STOCK_QTY
		                                                               END) ,0)
		                                                FROM  VW_STOCK
		                                                WHERE GOODS_CD = G.GOODS_CD
		                                                AND OPT_CD1 =  (CASE WHEN G.SELF_GOODS_YN = 'N' THEN OPT_CD1 
		                                                                    ELSE  IFNULL(#{colorCd}, IFNULL(G.MAIN_COLOR_CD,'XX')) END)
		                                                GROUP BY GOODS_CD )
		            ELSE (SELECT IFNULL(MAX(CASE WHEN SOLDOUT_YN = 'Y' THEN 0
		                                      ELSE CURR_STOCK_QTY
		                                      END) ,0)
		                  FROM  VW_STOCK_COMPOSE
		                  WHERE GOODS_CD= G.GOODS_CD
		                  GROUP BY GOODS_CD )
		            END) AS STOCK_QTY
		     , (CASE WHEN G.SELF_GOODS_YN ='Y' AND IFNULL(DS.GOODS_CD, 'Y') = 'Y' THEN 'Y' ELSE 'N' END ) AS QUIK_DELV_YN
		     , GS.REVIEW_REG_CNT + (SELECT COUNT(1)
		                            FROM   TB_REVIEW Z
		                            WHERE  1=1
		                            AND    Z.GOODS_CD = GS.GOODS_CD
		                            AND    Z.DISP_YN = 'Y'
		                            AND    Z.DEL_YN = 'N'
		                            AND    Z.CONFIRM_YN = 'Y'
		                            AND    Z.REG_DT BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL + 1 DAY)) AS REVIEW_REG_CNT
		     , GS.SCORE
		     , GS.SCORE_SIZE1
		     , GS.SCORE_SIZE2
		     , GS.SCORE_SIZE3
		     , GS.SCORE_COLOR1
		     , GS.SCORE_COLOR2
		     , GS.SCORE_COLOR3
		     , GS.SCORE_FIT1
		     , GS.SCORE_FIT2
		     , GS.SCORE_FIT3
		     , GS.SCORE_THICK1
		     , GS.SCORE_THICK2
		     , GS.SCORE_THICK3
		     , GS.SCORE_WEIGHT1
		     , GS.SCORE_WEIGHT2
		     , GS.SCORE_WEIGHT3
		     , GS.SCORE_BALL1
		     , GS.SCORE_BALL2
		     , GS.SCORE_BALL3
		     , GS.GOODS_QNA_CNT
		     , (SELECT TI.SIZE_GB FROM TB_ITEMKIND TI WHERE TI.ITEMKIND_CD = G.ITEMKIND_CD) AS SIZE_GB
		     , G.DELV_FEE_CD
		FROM TB_GOODS G
		INNER JOIN TB_GOODS_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD
		INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
		                      AND B.USE_YN = 'Y'
		INNER JOIN TB_SITE_BRAND SB ON B.BRAND_CD  = SB.BRAND_CD
		                            AND SB.USE_YN = 'Y'
		                            AND SB.SITE_CD = #{siteCd}
		INNER JOIN TB_BRAND_GROUP D ON B.BRAND_GROUP_NO = D.BRAND_GROUP_NO
		                            AND D.USE_YN = 'Y'
		INNER JOIN TB_DELV_FEE_POLICY E ON G.SUPPLY_COMP_CD = E.SUPPLY_COMP_CD
		                                AND G.DELV_FEE_CD =  E.DELV_FEE_CD
		LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
		                              AND IFNULL(#{custNo}, 0) = W.CUST_NO
		LEFT OUTER JOIN TB_GOODS_SHOT_DELV_SKIP DS ON G.GOODS_CD =DS.GOODS_CD
		WHERE G.GOODS_CD = #{goodsCd}
		AND NOW() BETWEEN SELL_STDT AND SELL_EDDT	/*판매기간 확인*/
		<if test='adminYn == null or adminYn != "Y"'>
		AND G.GOODS_STAT = 'G008_90'
		</if>
		) Z
	</select>

	<!-- 상품 상세 정보 조회 -->
	<select id="getGoodsDescList" parameterType="GoodsDesc" resultType="GoodsDesc">
		/* TsfGoods.getGoodsDescList */
		SELECT GOODS_CD
		     , DESC_GB
		     , SEQ
		     , GOODS_DESC
		FROM TB_GOODS_DESC
		WHERE GOODS_CD = #{goodsCd}
		AND DESC_GB = #{descGb}
		ORDER BY SEQ
	</select>

	<!-- 상품 옵션 정보 조회 -->
	<select id="getGoodsOptionInfoList" parameterType="GoodsStock" resultType="GoodsStock">
		/* TsfGoods.getGoodsOptionInfoList : 상품 옵션 정보 조회 */
		SELECT VS.GOODS_CD
		     , VS.CURR_STOCK_QTY
		     , VS.SOLDOUT_YN
		     , VS.OPT_CD
		     , VS.OPT_CD1
		     , VS.OPT_CD2
		     , VS.DISP_YN
		     , VS.ADD_PRICE
		     , IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = NULLIF(VS.OPT_CD1,'') AND USE_YN  = 'Y'), VS.OPT_CD1) AS COLOR_NM
		     , CASE WHEN VS.DISP_YN = 'Y' AND VS.SOLDOUT_YN = 'N' THEN VS.CURR_STOCK_QTY ELSE 0 END AS STOCK_QTY
		FROM   VW_STOCK VS
		WHERE  VS.GOODS_CD = #{goodsCd}
		ORDER  BY VS.OPT_CD
	</select>
	
	<!-- 핫딜 상품 조회 -->
	<select id="getSocialGoods" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getSocialGoods */
		SELECT *
		FROM (
		    SELECT A.SOCIAL_SQ
		         , A.SOCIAL_TYPE
		         , FN_GET_CODE_NM('G062', A.SOCIAL_TYPE) AS SOCIAL_TYPE_NM
		         , A.SOCIAL_TNM
		         , B.GOODS_CD
		         , IFNULL(B.CURR_APRICE, C.CURR_PRICE) AS CURR_PRICE
		         , IFNULL(B.DC_ARATE, C.DC_RATE) AS DC_RATE
		         , (CASE WHEN #{frontGb} = 'P' THEN IFNULL(B.PNT_APRATE, C.PNT_PRATE) ELSE IFNULL(B.PNT_AMRATE,C.PNT_MRATE) END ) AS PNT_RATE
		         , DATE_FORMAT(A.SOCIAL_STDT, '%Y%m%d%H%i%S')  AS SOCIAL_STDT
		         , DATE_FORMAT(A.SOCIAL_EDDT, '%Y-%m-%dT%H:%i:%S')  AS SOCIAL_EDDT
		    FROM TB_SOCIAL A
		    INNER JOIN TB_SOCIAL_GOODS B ON A.SOCIAL_SQ = B.SOCIAL_SQ
		                                 AND B.GOODS_CD = #{goodsCd}
		                                 AND B.DEL_YN = 'N'
		    INNER JOIN TB_GOODS C ON B.GOODS_CD = C.GOODS_CD
		                          AND NOW() BETWEEN C.SELL_STDT AND C.SELL_EDDT           /*판매기간 확인*/
		    WHERE A.USE_YN = 'Y'
		    AND A.SITE_CD = #{siteCd}
		    AND NOW() BETWEEN A.SOCIAL_STDT AND A.SOCIAL_EDDT
		    AND A.FRONT_GB IN ('A', #{frontGb})
		    AND A.APPLY_GB = 'A' -- 적용
		    <if test='adminYn == null or adminYn != "Y"'>
		    AND C.GOODS_STAT = 'G008_90'
		    </if>
		    ORDER BY A.SOCIAL_SQ DESC
		) A
		LIMIT 1
	</select>
	
	<!-- 상품 옵션1 목록 - 자사용 -->
	<select id="getGoodsOption1List" parameterType="Goods" resultType="GoodsStock">
		/* TsfGoods.getGoodsOption1List */
		SELECT A.GOODS_CD
		     , A.SELF_GOODS_YN
		     , A.MIN_ORD_QTY
		     , A.MAX_ORD_QTY
		     , A.GOODS_STAT
		     , A.STOCK_QTY
		     , A.OPT_CD1
		     , A.QTY
		     , IFNULL(C.COLOR_KNM, A.OPT_CD1) AS OPT_CD1_NM
		     , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = A.GOODS_CD AND COLOR_CD = IF(A.SELF_GOODS_YN ='Y', A.OPT_CD1, 'XX') AND DEFAULT_IMG_YN = 'Y') AS SYS_IMG_NM
		FROM (
		      SELECT G.GOODS_CD
		           , G.SELF_GOODS_YN
		           , G.MIN_ORD_QTY
		           , G.MAX_ORD_QTY
		           , G.GOODS_STAT
		           , O.OPT_CD1
		           , SUM(CASE WHEN O.SOLDOUT_YN = 'Y' THEN 0
		                      WHEN VS.CURR_STOCK_QTY  <![CDATA[<=]]> 0 THEN 0
		                      WHEN VS.CURR_STOCK_QTY >= G.MIN_ORD_QTY THEN VS.CURR_STOCK_QTY
		                      ELSE 0 END) AS STOCK_QTY
		           , IFNULL(GC.QTY , 1) AS QTY
		      FROM TB_GOODS G
		      INNER JOIN TB_OPTION O ON G.GOODS_CD = O.GOODS_CD
		                             AND O.DISP_YN = 'Y'
		      INNER JOIN VW_STOCK VS ON O.GOODS_CD = VS.GOODS_CD
		                          AND O.OPT_CD = VS.OPT_CD
		      LEFT OUTER JOIN TB_GOODS_COMPOSE GC ON GC.COMPS_GOODS_CD = G.GOODS_CD
		                                          AND GC.GOODS_CD = IFNULL(#{mastrGoodsCd},#{goodsCd})
		      WHERE G.GOODS_CD =  #{goodsCd}
		      AND G.GOODS_TYPE = 'G056_N' -- 일반상품
		      AND NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT           /*판매기간 확인*/
		      GROUP BY G.GOODS_CD , G.SELF_GOODS_YN , G.MIN_ORD_QTY, G.MAX_ORD_QTY, O.OPT_CD1
		) A
		LEFT OUTER JOIN TB_COLOR C ON A.OPT_CD1 = C.COLOR_CD
		                           AND C.USE_YN = 'Y'
		WHERE 1 = 1
		<if test='optionSoldoutSkip != null and optionSoldoutSkip == "Y" '>
		AND A.STOCK_QTY > 0
		</if>
		ORDER BY A.GOODS_CD, IFNULL(C.COLOR_KNM, A.OPT_CD1)
	</select>
		
	<!-- 상품 옵션2 목록  - 자사용-->
	<select id="getGoodsOption2List" parameterType="Goods" resultType="GoodsStock">
		/* TsfGoods.getGoodsOption2List */
		WITH TMP_GOODS AS (
		    SELECT Y.GOODS_CD
		         , IFNULL(Z.CURR_PRICE, Y.CURR_PRICE) AS CURR_PRICE
		         , Y.MIN_ORD_QTY
		         , Y.SELF_GOODS_YN
		         , Y.GOODS_TYPE
		         , Y.GIFT_PACK_YN
		         , Y.SELL_STDT
		         , Y.SELL_EDDT
		    FROM (
		         SELECT A.GOODS_CD
		              , A.GOODS_TYPE
		              , IFNULL(B.COMPS_GOODS_CD, A.GOODS_CD) AS COMPS_GOODS_CD
		              , IFNULL(B.DISP_ORD, 1)                AS DISP_ORD
		              , IFNULL(B.COMPS_CURR_PRICE, A.CURR_PRICE) AS CURR_PRICE
		              , A.SELF_GOODS_YN
		              , A.GIFT_PACK_YN
		         FROM TB_GOODS A
		         LEFT OUTER JOIN TB_GOODS_COMPOSE B ON A.GOODS_CD = B.GOODS_CD
		         WHERE A.GOODS_CD = #{mastrGoodsCd}
		         ) Z
		    INNER  JOIN TB_GOODS Y ON Z.COMPS_GOODS_CD = Y.GOODS_CD
		    WHERE  Z.COMPS_GOODS_CD = #{goodsCd}
		)
		SELECT GOODS_CD
		     , OPT_CD
		     , OPT_CD1
		     , IFNULL(C.COLOR_KNM, B.OPT_CD1) AS OPT_CD1_NM
		     , OPT_CD2
		     , ADD_PRICE 
		     , (CASE WHEN STOCK_QTY >= MIN_ORD_QTY THEN STOCK_QTY
		             ELSE 0
		        END ) AS STOCK_QTY
		     , SOLDOUT_YN
		     , DISP_ORD
		     , SELF_GOODS_YN
		     , GIFT_PACK_YN
		     , CURR_PRICE
		FROM (
		      SELECT O.GOODS_CD
		           , O.OPT_CD
		           , O.OPT_CD1
		           , O.OPT_CD2
		           , O.ADD_PRICE 
		           , VS.CURR_STOCK_QTY AS STOCK_QTY
		           , O.SOLDOUT_YN
		           , O.DISP_ORD
		           , G.MIN_ORD_QTY
		           , G.SELF_GOODS_YN
		           , G.GIFT_PACK_YN
		           , G.CURR_PRICE
		      FROM TMP_GOODS G
		      INNER JOIN TB_OPTION O ON G.GOODS_CD = O.GOODS_CD
		                             AND O.OPT_CD1 = #{optCd1}
		                             AND O.DISP_YN = 'Y' 
		      INNER JOIN VW_STOCK VS ON O.GOODS_CD = VS.GOODS_CD
		                          AND O.OPT_CD = VS.OPT_CD
		      WHERE G.GOODS_CD = #{goodsCd}
		      AND G.GOODS_TYPE = 'G056_N' -- 일반상품
		      AND NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT           /*판매기간 확인*/
		     ) B
		LEFT OUTER JOIN TB_COLOR C ON B.OPT_CD1 = C.COLOR_CD
		                           AND C.USE_YN = 'Y'
		WHERE 1 = 1
		<if test='optionSoldoutSkip != null and optionSoldoutSkip == "Y" '>
		AND STOCK_QTY > 0
		AND SOLDOUT_YN = 'N'
		</if>
		ORDER BY DISP_ORD
	</select>
	
	<!-- 상품 옵션 목록  - 입점용-->
	<select id="getGoodsOptionList" parameterType="Goods" resultType="GoodsStock">
		/* TsfGoods.getGoodsOptionList */
		SELECT GOODS_CD
		     , OPT_CD
		     , OPT_CD1
		     , OPT_CD2
		     , CONCAT(OPT_CD1,'/', OPT_CD2) AS OPT_NM
		     , ADD_PRICE 
		     , (CASE WHEN STOCK_QTY >= MIN_ORD_QTY THEN STOCK_QTY
		             ELSE 0
		        END ) AS STOCK_QTY
		     , SOLDOUT_YN
		     , DISP_ORD
		FROM (
		      SELECT O.GOODS_CD
		           , O.OPT_CD
		           , O.OPT_CD1
		           , O.OPT_CD2
		           , O.ADD_PRICE 
		           , VS.CURR_STOCK_QTY AS STOCK_QTY
		           , O.SOLDOUT_YN
		           , O.DISP_ORD
		           , (SELECT MIN_ORD_QTY FROM TB_GOODS WHERE GOODS_CD = O.GOODS_CD ) AS MIN_ORD_QTY
		      FROM TB_GOODS G
		      INNER JOIN TB_OPTION O ON G.GOODS_CD = O.GOODS_CD
		                             AND O.DISP_YN = 'Y' 
		      INNER JOIN VW_STOCK VS ON O.GOODS_CD = VS.GOODS_CD
		                          AND O.OPT_CD = VS.OPT_CD
		      WHERE G.GOODS_CD = #{goodsCd}
		      AND G.GOODS_TYPE = 'G056_N' -- 일반상품
		      AND NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT           /*판매기간 확인*/
		     ) B
		ORDER BY DISP_ORD
	</select>
	
	<!-- 상품 동영상정보 -->
	<select id="getVideoList" parameterType="GoodsVideo" resultType="GoodsVideo">
		/* TsfGoods.getVideoList */
		SELECT A.VIDEO_SQ
		     , A.GOODS_CD
		     , A.VIDEO_GB
		     , A.KMC_KEY
		     , A.KUF_KEY
		     , A.DISP_YN
		FROM TB_GOODS_VIDEO A
		WHERE A.GOODS_CD = #{goodsCd} 
		AND A.DISP_YN = 'Y'
		AND NULLIF(A.KMC_KEY,'') IS NOT NULL
		ORDER BY A.VIDEO_SQ DESC
		<if test="maxRownum != null and maxRownum > 0">
		LIMIT #{maxRownum}
		</if>
	</select>
	
	<!-- 상품 고시정보 목록 -->
	<select id="getGoodsNotiList" parameterType="Goods" resultType="GoodsNotiInfo">
		/* TsfGoods.getGoodsNotiList */
		SELECT A.GOODS_CD
		     , A.NI_CLSF_CD
		     , A.NI_ITEM_CD
		     , FN_GET_CODE_NM('G005',A.NI_ITEM_CD) AS NI_ITEM_NM
		     , A.NI_CONTENT
		     , A.DISP_ORD
		FROM TB_GOODS_NOTI_INFO A
		WHERE A.GOODS_CD = #{goodsCd}
		ORDER BY A.DISP_ORD
	</select>
		
	<!-- 상품 이미지 정보 -->
	<select id="getGoodsImgList" parameterType="Goods" resultType="GoodsImg">
		/* TsfGoods.getGoodsImgList */
		SELECT A.GOODS_CD
		     , A.COLOR_CD
		     , A.DISP_ORD
		     , A.SYS_IMG_NM
		     , A.DEFAULT_IMG_YN
		     , A.MOUSEOVER_IMG_YN
		     , B.MODEL_INFO
		FROM TB_GOODS_IMG A
		LEFT OUTER JOIN TB_GOODS_MODEL_INFO B ON A.GOODS_CD = B.GOODS_CD
		                                      AND B.MODEL_INFO IS NOT NULL
		WHERE A.GOODS_CD = #{goodsCd}
		<if test='selfGoodsYn != null and selfGoodsYn == "Y"'>
		AND A.COLOR_CD = #{colorCd}
		</if>
		AND A.EXTMALL_IMG_YN = 'N'
		ORDER BY A.DISP_ORD
	</select>
	
	<!-- 딜상품 구성상품정보 조회 -->
	<select id="getGoodsDealComposeList"  parameterType="Goods" resultType="Goods">
		/* TsfGoods.getGoodsDealComposeList */
		SELECT Z.* 
		     , 100 - ROUND((CURR_PRICE / LIST_PRICE) * 100 ,0) AS DC_RATE 
		     , FN_GET_GOODS_NM(GOODS_NM,GOODS_GB,FOREIGN_BUY_YN,PARALLEL_IMPORT_YN,ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		FROM (
		SELECT C.GOODS_CD
		     , C.COMPS_GOODS_CD
		     , C.DISP_ORD
		     , G.GOODS_NM
		     , G.GOODS_GB
		     , G.FOREIGN_BUY_YN
		     , G.PARALLEL_IMPORT_YN
		     , G.ORDER_MADE_YN
		     , G.LIST_PRICE
		     , G.GOODS_STAT
		     , G.SELF_GOODS_YN
		     , G.GIFT_PACK_YN
		     , DATE_FORMAT(G.SELL_STDT ,'%Y%m%d%H%i%S') AS SELL_STDT
		     , DATE_FORMAT(G.SELL_EDDT ,'%Y%m%d%H%i%S') AS SELL_EDDT
		     , FN_GET_APPLY_CPN1_PRICE(G.GOODS_CD, #{frontGb})  AS CURR_PRICE
		     , (CASE WHEN D.DISP_NM_LANG = 'EN' THEN D.BRAND_GROUP_ENM ELSE D.BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
		     , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = G.MAIN_COLOR_CD AND DEFAULT_IMG_YN = 'Y') AS SYS_IMG_NM
		     , (SELECT IFNULL(MAX(CASE WHEN SOLDOUT_YN = 'Y' THEN 0
		                          ELSE CURR_STOCK_QTY
		                          END) ,0)
		        FROM  VW_STOCK
		        WHERE GOODS_CD = G.GOODS_CD
		        GROUP BY GOODS_CD 
		        ) AS STOCK_QTY
		     , GS.REVIEW_REG_CNT + (SELECT COUNT(1)
		                            FROM   TB_REVIEW Z
		                            WHERE  1=1
		                            AND    Z.GOODS_CD = GS.GOODS_CD
		                            AND    Z.DISP_YN = 'Y'
		                            AND    Z.DEL_YN = 'N'
		                            AND    Z.CONFIRM_YN = 'Y'
		                            AND    Z.REG_DT BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL + 1 DAY)) AS REVIEW_REG_CNT
		     , GS.GOODS_QNA_CNT
		FROM TB_GOODS_COMPOSE C
		INNER JOIN TB_GOODS G ON C.COMPS_GOODS_CD = G.GOODS_CD
		                      AND NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT	/*판매기간 확인*/
		                      AND G.SELF_MALL_YN = 'Y' -- 자사몰 노출
		INNER JOIN TB_GOODS_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD
		INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
		                      AND B.USE_YN = 'Y'
		INNER JOIN TB_SITE_BRAND SB ON B.BRAND_CD = SB.BRAND_CD
		                            AND SB.USE_YN = 'Y'
		                            AND SB.SITE_CD = #{siteCd}
		INNER JOIN TB_BRAND_GROUP D ON B.BRAND_GROUP_NO = D.BRAND_GROUP_NO
		                            AND D.USE_YN = 'Y'
		WHERE C.GOODS_CD = #{goodsCd}
		AND C.GOODS_TYPE = 'G056_D'
		AND C.USE_YN = 'Y'
		ORDER BY C.DISP_ORD
		) Z
		WHERE 1 = 1
		<if test='optionSoldoutSkip != null and optionSoldoutSkip == "Y" '>
		AND STOCK_QTY > 0
		AND GOODS_STAT = 'G008_90'
		</if>
		LIMIT 200
	</select>

	<!-- 상품 정보 조회 -->
	<select id="getGoodsSimpleInfo" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getGoodsSimpleInfo */
		SELECT GOODS_CD
		     , GOODS_TYPE
		     , CASE WHEN NOW() BETWEEN SELL_STDT AND SELL_EDDT THEN GOODS_STAT
		            ELSE 'G008_95' END AS GOODS_STAT
		     , SELF_GOODS_YN
		     , SELF_MALL_YN
		FROM   TB_GOODS
		WHERE  GOODS_CD = #{goodsCd}
	</select>

	<!-- 구성 상품 정보 -->
	<select id="getGoodsCompsInfo" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getGoodsCompsInfo */
		SELECT GC.GOODS_CD
		     , GC.COMPS_GOODS_CD
		     , GC.GOODS_TYPE
		     , GC.QTY
		     , G.GOODS_STAT
			 , G.GOODS_NM
		  FROM TB_GOODS_COMPOSE GC
		 INNER JOIN TB_GOODS G
		    ON GC.COMPS_GOODS_CD = G.GOODS_CD
		 WHERE GC.USE_YN = 'Y'
		   AND G.SELF_MALL_YN = 'Y'							<!-- 자사몰 노출 여부 -->
		   AND GC.GOODS_TYPE = #{goodsType}
		   AND GC.GOODS_CD = #{goodsCd}
		   AND GC.COMPS_GOODS_CD = #{compsGoodsCd}
	</select>

	<!-- 상품 재고 조회 -->
	<select id="getGoodsStockInfo" parameterType="GoodsStock" resultType="GoodsStock">
		/* TsfGoods.getGoodsStockInfo */
		SELECT VS.GOODS_CD
			 , VS.OPT_CD
			 , VS.OPT_CD1
			 , VS.OPT_CD2
			 , VS.SOLDOUT_YN
			 , VS.CURR_STOCK_QTY
		     , VS.ADD_PRICE
			 , O.SKU_MODEL_NO
		  FROM VW_STOCK VS
		 INNER JOIN TB_OPTION O
		    ON VS.GOODS_CD = O.GOODS_CD
		   AND VS.OPT_CD = O.OPT_CD
		 WHERE VS.GOODS_CD = #{goodsCd}
		   AND VS.OPT_CD = #{optCd}
	</select>

	<!-- 주문 상품 옵션 정보 조회 -->
	<select id="getOrderGoodsOptionList" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getOrderGoodsOptionList */
		SELECT G.GOODS_CD
		     , G.GOODS_STAT
		     , G.GOODS_NM
		     , VS.OPT_CD
		     , VS.OPT_CD1
		     , VS.OPT_CD2
		     , CASE WHEN VS.SOLDOUT_YN = 'Y' OR G.GOODS_STAT <![CDATA[ <> ]]> 'G008_90' THEN 0 ELSE VS.CURR_STOCK_QTY END AS STOCK_QTY
		     , IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(VS.OPT_CD1,'') , G.MAIN_COLOR_CD ) AND USE_YN  = 'Y'), VS.OPT_CD1) AS COLOR_NM
		FROM   TB_GOODS G
		INNER  JOIN VW_STOCK VS
		ON     G.GOODS_CD = VS.GOODS_CD
		WHERE  1=1
		AND    VS.GOODS_CD IN
		<foreach collection="arrGoodsCd" item="item" index="index"  open="(" close=")" separator=",">
			#{item}
		</foreach>
		<if test="sizeArr != null and sizeArr.length > 0">
		AND    VS.OPT_CD IN
			<foreach collection="sizeArr" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
		ORDER  BY G.GOODS_CD ASC, VS.OPT_CD ASC
	</select>
	
	<!-- 무이자 혜택안내 -->
	<select id="getCardPromotionList" parameterType="CardPromotion" resultType="CardPromotion">
		/* TsfGoods.getCardPromotionList */
		SELECT * 
		FROM (
		      SELECT A.CARD_PRMT_SQ 
		           , A.PRMT_NM 
		           , A.PRMT_GB 
		           , A.DC_GB 
		           , (CASE WHEN A.PRMT_GB = 'A' AND A.DC_GB = '1' THEN '즉시할인' 
		                   WHEN A.PRMT_GB = 'A' AND A.DC_GB = '2' THEN '청구할인'
		                   WHEN A.PRMT_GB = 'A' AND A.DC_GB = '3' THEN '청구할인'
		                   ELSE '신용카드 무이자 혜택' END
		              ) AS PRMT_GB_NM
		           , A.LINK_URL 
		           , A.NOTE 
		           , B.CARD_PRMT_CDT_SQ 
		           --  , (B.MIN_PAY_AMT / 10000) AS MIN_PAY_AMT  -- 만원단위
		           , B.MIN_PAY_AMT 
		           , B.MAX_DC_AMT
		           , B.MAX_NO_ITRT 
		           , B.MIN_NO_ITRT 
		           , B.DC_WAY
		           , (CASE WHEN B.DC_WAY = 'G240_11' THEN '%' ELSE '원' END) AS DC_WAY_NM 
		           , B.DC_VAL 
		           , C.PRMT_TARGET_CD 
		           , D.CD_NM AS PRMT_TARGET_NM
		           , RANK() OVER(PARTITION BY A.PRMT_GB, A.DC_GB ORDER BY A.CARD_PRMT_SQ , B.CARD_PRMT_CDT_SQ, C.PRMT_TARGET_CD DESC) ROWNUM
		      FROM TB_CARD_PROMOTION A
		      INNER JOIN TB_CARD_PROMOTION_CONDITION B ON A.CARD_PRMT_SQ = B.CARD_PRMT_SQ
		      INNER JOIN TB_CARD_PROMOTION_TARGET C ON B.CARD_PRMT_CDT_SQ = C.CARD_PRMT_CDT_SQ
		      INNER JOIN TB_COMMON_CODE D ON C.PRMT_TARGET_CD  = D.CD 
		                                  AND D.CD_GB = 'G941'
		                                  AND D.USE_YN ='Y'
		      WHERE NOW() BETWEEN A.PRMT_STD AND ADDDATE(A.PRMT_EDD, INTERVAL 1 DAY)
		      AND A.DISP_YN  = 'Y'
		      <if test="prmtGb != null and prmtGb != '' ">
		      AND A.PRMT_GB = #{prmtGb} -- A:프로모션, B:무이자
		      </if>
		      <if test='prmtGb != null and prmtGb == "A" '>
		      <choose>
		          <when test='dcGb != null and dcGb=="3"'>
		      AND A.DC_GB IN ('3')
		          </when>
		          <otherwise>
		      AND A.DC_GB IN ('1','2')
		          </otherwise>
		      </choose>
		      </if>
		) A
		<if test="rownum != null and rownum > 0 ">
		WHERE ROWNUM = 1
		</if>
	</select>
	
	<!-- 상품예약판매정보 조회 -->
	<select id="getGoodsResSell" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getGoodsResSell */
		SELECT GOODS_CD
		     , DATE_FORMAT(DELV_RES_DT ,'%Y.%m.%d') AS DELV_RES_DT
		FROM TB_GOODS_RES_SELL
		WHERE GOODS_CD = #{goodsCd}
		AND DELV_RES_DT > NOW()
		AND USE_YN = 'Y'
		ORDER BY DELV_RES_DT
		LIMIT 1
	</select>
	
	<!-- 상품 사이즈 선택 시 재고수량 가져오기 -->
	<select id="getGoodsStockQty_ORG"  parameterType="GoodsStock" resultType="int">
		/* TsfGoods.getGoodsStockQty_ORG */
		SELECT (CASE WHEN STOCK_QTY >= MIN_ORD_QTY THEN STOCK_QTY
		             ELSE 0 END ) AS STOCK_QTY
		FROM ( 
		    SELECT CASE WHEN A.SOLDOUT_YN = 'Y' THEN 0
		                ELSE GREATEST(GREATEST((GREATEST(A.CURR_STOCK_QTY,0) + GREATEST(A.STORE_STOCK_QTY,0)) - A.BASE_STOCK_QTY, 0) - IFNULL(S.SELL_QTY, 0), 0) END AS STOCK_QTY
		         , B.MIN_ORD_QTY  
		    FROM TB_OPTION A
		    LEFT OUTER JOIN (
		                     SELECT GOODS_CD,
		                            OPT_CD,
		                            SUM((CASE SELL_GB WHEN '10' THEN 1
		                                              WHEN '20' THEN 1
		                                              ELSE -1 END) * SELL_QTY) AS SELL_QTY
		                     FROM TB_SELL_QTY
		                     WHERE GOODS_CD = #{goodsCd}
		                     AND OPT_CD = #{optCd}
		                     GROUP BY GOODS_CD, OPT_CD
		                     ) S
		                     ON (A.GOODS_CD = S.GOODS_CD
		                         AND A.OPT_CD = S.OPT_CD
		                         )
		    INNER JOIN TB_GOODS B ON A.GOODS_CD = B.GOODS_CD
		    WHERE A.GOODS_CD =  #{goodsCd}
		    AND A.OPT_CD = #{optCd}
		) Z
	</select>
	
	<!-- 상품 사이즈 선택 시 재고수량 가져오기 -->
	<select id="getGoodsStockQty"  parameterType="GoodsStock" resultType="int">
		/* TsfGoods.getGoodsStockQty */
		SELECT CASE WHEN SOLDOUT_YN = 'Y' THEN 0
		            ELSE CURR_STOCK_QTY END AS CURR_STOCK_QTY
		FROM VW_STOCK
		WHERE GOODS_CD =  #{goodsCd}
		AND OPT_CD = #{optCd}
		
	</select>
	
	<!-- 상품 사이즈  매장 쵀대 재고수량 가져오기 -->
	<select id="getGoodsStoreStockQty"  parameterType="GoodsStock" resultType="int">
		/* TsfGoods.getGoodsStoreStockQty */
		SELECT CASE WHEN STORE_MAX_QTY >= MIN_ORD_QTY THEN STORE_MAX_QTY
		            ELSE 0 END
		FROM (
		    SELECT (CASE
		                WHEN WMS_STOCK_QTY >= STORE_STOCK_QTY THEN WMS_STOCK_QTY
		                ELSE STORE_STOCK_QTY END) AS STORE_MAX_QTY
		         , MIN_ORD_QTY
		    FROM (
		             SELECT CASE
		                        WHEN WMS_STOCK_QTY - SELL_QTY > 0 THEN WMS_STOCK_QTY - SELL_QTY
		                        ELSE 0 END AS WMS_STOCK_QTY
		                  , CASE
		                        WHEN SELL_QTY - WMS_STOCK_QTY >= 0
		                            THEN STORE_STOCK_QTY - (SELL_QTY - WMS_STOCK_QTY)
		                        ELSE STORE_STOCK_QTY END          AS STORE_STOCK_QTY
		                  , MIN_ORD_QTY
		             FROM (
		                   SELECT CASE WHEN A.SOLDOUT_YN = 'Y' THEN 0
		                               ELSE A.CURR_STOCK_QTY END  AS WMS_STOCK_QTY
		                        , CASE WHEN A.SOLDOUT_YN = 'Y' THEN 0
		                               ELSE A.STORE_MAX_QTY END AS STORE_STOCK_QTY
		                        , IFNULL(S.SELL_QTY, 0) AS SELL_QTY
		                        , B.MIN_ORD_QTY
		                   FROM TB_OPTION A
		                   LEFT OUTER JOIN (
		                                    SELECT GOODS_CD,
		                                           OPT_CD,
		                                           SUM((CASE SELL_GB
		                                                    WHEN '10' THEN 1
		                                                    WHEN '20' THEN 1
		                                                    ELSE -1 END) * SELL_QTY) AS SELL_QTY
		                                    FROM TB_SELL_QTY
		                                    WHERE GOODS_CD = #{goodsCd}
		                                    AND OPT_CD = #{optCd}
		                                    GROUP BY GOODS_CD, OPT_CD
		                                    ) S
		                                    ON (A.GOODS_CD = S.GOODS_CD
		                                        AND A.OPT_CD = S.OPT_CD
		                                        )
		                   INNER JOIN TB_GOODS B ON A.GOODS_CD = B.GOODS_CD
		                   WHERE A.GOODS_CD =  #{goodsCd}
		                   AND A.OPT_CD = #{optCd}
		                  ) X
		         ) Y
		)Z
	</select>	
	
	<!-- 상품  공지 목록  -->
	<select id="getGoodsNoticeList"  parameterType="Goods" resultType="Notice">
		/* TsfGoods.getGoodsNoticeList */
		SELECT A.NOTICE_SQ
		     , A.NOTICE_TYPE
		     , A.NOTICE_TITLE
		     , A.NOTICE_CONTENT
		     , DATE_FORMAT(A.NOTICE_STDT,'%Y-%m-%d')  AS NOTICE_STDT 
		     , DATE_FORMAT(A.NOTICE_EDDT,'%Y-%m-%d')  AS NOTICE_EDDT 
		     , A.USE_YN
		FROM   TB_NOTICE A
		INNER JOIN TB_NOTICE_GOODS B ON A.NOTICE_SQ = B.NOTICE_SQ
		                             AND B.GOODS_CD = #{goodsCd}
		WHERE  A.NOTICE_TYPE = 'G047_30'
		AND CURRENT_DATE() BETWEEN A.NOTICE_STDT AND A.NOTICE_EDDT
		AND A.USE_YN  = 'Y'
		ORDER  BY A.NOTICE_SQ DESC
	</select>
	
	<!-- 상품  안전인증 정보  -->
	<select id="getGoodsSafeNo"  parameterType="Goods" resultType="GoodsSafeNo">
		/* TsfGoods.getGoodsSafeNo */
		SELECT  GOODS_CD
		      , CERT_TARGET_GB
		      , CERT_FORM_GB
		      , FN_GET_CODE_NM('G084',CERT_FORM_GB) AS CERT_FORM_NM  
		      , CERT_TYPE
		      , FN_GET_CODE_NM('G081',CERT_TYPE) AS CERT_TYPE_NM  
		      , CERT_NUM
		      , CERT_STATE
		      , CERT_DIV
		      , CERT_ORGAN_NAME
		      , CERT_DT
		FROM TB_GOODS_SAFE_NO 
		WHERE GOODS_CD = #{goodsCd}
		AND CERT_TARGET_GB IN ('G083_1', 'G083_3')  -- 인증대상, 상세설명별도표기
	</select>
	
	<!-- 상품  배송안내정보  -->
	<select id="getGoodsDeliveryInfo"  parameterType="String" resultType="Delivery">
		/* TsfGoods.getGoodsDeliveryInfo */
		SELECT G.GOODS_CD
		     , G.GOODS_TYPE
		     , A.DELV_FEE_CD 
		     , A.SUPPLY_COMP_CD 
		     , A.DELV_FEE 
		     , A.MIN_ORD_AMT 
		     , A.RTN_DELV_FEE 
		     , B.RTN_LOC_ZIPCODE
		     , B.RTN_LOC_BASE_ADDR
		     , B.RTN_LOC_DTL_ADDR
		     , IFNULL(B.RTN_LOC_TELNO, '1544-5336') AS RTN_LOC_TELNO
		     , B.RTN_LOC_NM
		     , S.NOTE
		     , SC.SHIP_COMP_CD
		     , SC.SHIP_COMP_NM
		FROM TB_GOODS G
		INNER JOIN TB_SUPPLY_COMPANY S ON G.SUPPLY_COMP_CD = S.SUPPLY_COMP_CD
		INNER JOIN TB_DELV_FEE_POLICY A ON G.DELV_FEE_CD = A.DELV_FEE_CD
		                                AND G.SUPPLY_COMP_CD = A.SUPPLY_COMP_CD
		INNER JOIN TB_DELIVERY_LOC B ON A.SUPPLY_COMP_CD = B.SUPPLY_COMP_CD 
		                             AND B.USE_YN = 'Y'
		                             AND G.DELV_LOC_CD = B.DELV_LOC_CD 
		INNER JOIN TB_SHIP_COMPANY SC ON B.SHIP_COMP_CD = SC.SHIP_COMP_CD
		                              AND SC.USE_YN = 'Y'
		WHERE G.GOODS_CD = #{goodsCd}
		ORDER BY A.DELV_FEE_CD 
		LIMIT 1
	</select>
	
	<!-- 상품 다다익선 시퀀스 목록 -->
	<select id="getTmtbSqList" parameterType="Goods" resultType="Tmtb">
		/* TsfGoods.getTmtbSqList */
		SELECT A.TMTB_SQ
		FROM TB_TMTB_APPLY_GOODS A
		LEFT JOIN TB_TMTB_APPLY_GOODS B ON A.GOODS_CD = B.GOODS_CD
		                                AND B.DEL_YN = 'N' AND B.GOODS_GB = 'G800_30' -- 제외상품
		                                AND B.GOODS_CD =  #{goodsCd}
		WHERE A.GOODS_CD = #{goodsCd}  AND A.DEL_YN = 'N' AND A.GOODS_GB IN ( 'G800_10', 'G800_20')  -- 기본과 적용상품
		AND B.TMTB_SQ IS NULL
		ORDER BY A.TMTB_SQ DESC -- 최근 다다익선
	</select>	
	
	<!-- 상품 다다익선 목록 -->
	<select id="getTmtbGoodsList" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getTmtbGoodsList */
		WITH  TAB_MASTER_GOODS AS (
		SELECT  Z.*
		FROM (
		    SELECT B.GOODS_CD
		         , G.GOODS_NM
		         , G.GOODS_GB
		         , G.FOREIGN_BUY_YN
		         , G.PARALLEL_IMPORT_YN
		         , G.ORDER_MADE_YN
		         , G.GOODS_STAT
		         , G.GOODS_TYPE
		         , G.MAIN_COLOR_CD
		         , G.MIN_ORD_QTY
		         , (CASE WHEN E.DISP_NM_LANG = 'EN' THEN E.BRAND_GROUP_ENM ELSE E.BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
		         , G.LIST_PRICE
		         , G.CURR_PRICE
		         , GS.STOCK_QTY
		    FROM TB_TMTB A
		    INNER JOIN TB_TMTB_APPLY_GOODS B ON A.TMTB_SQ  = B.TMTB_SQ
		                                     AND B.DEL_YN = 'N'
		                                     AND B.GOODS_GB IN ( 'G800_10', 'G800_20')  -- 기본과 적용상품
		    LEFT OUTER JOIN TB_TMTB_APPLY_GOODS BB ON B.TMTB_SQ  = BB.TMTB_SQ
		                                     AND BB.DEL_YN = 'N'
		                                     AND B.GOODS_CD = BB.GOODS_CD
		                                     AND BB.GOODS_GB = 'G800_30'  -- 제외상품
		                                     AND BB.GOODS_CD IS NULL
		    INNER JOIN TB_GOODS G ON B.GOODS_CD = G.GOODS_CD
		                          AND G.GOODS_STAT = 'G008_90'
		                          AND G.SELF_MALL_YN = 'Y'
		                          AND NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT
		                          <![CDATA[
		                          AND B.GOODS_CD <> #{goodsCd}  -- 자기상품은 제외
		                          ]]>
		    INNER JOIN TB_BRAND D ON G.BRAND_CD = D.BRAND_CD
		                  AND D.USE_YN = 'Y'
		    INNER JOIN TB_SITE_BRAND SB ON D.BRAND_CD  = SB.BRAND_CD
		                          AND SB.USE_YN = 'Y'
		                          AND SB.SITE_CD = #{siteCd}
		    INNER JOIN TB_BRAND_GROUP E ON D.BRAND_GROUP_NO = E.BRAND_GROUP_NO
		                          AND E.USE_YN = 'Y'
		    INNER JOIN TB_GOODS_STOCK GS ON G.GOODS_CD = GS.GOODS_CD
		                                 AND GS.STOCK_QTY > 0
		    WHERE NOW() BETWEEN A.TMTB_ST_DT AND A.TMTB_ED_DT
		    AND A.TMTB_STAT ='G232_11'
		    <choose>
		    <when test="arrTmtbSq != null and arrTmtbSq.length > 0">
		    AND A.TMTB_SQ IN
		         <foreach collection="arrTmtbSq" item="item" index="index"  open="(" close=")" separator=",">
		    #{item}
		         </foreach>
		    </when>
		    <otherwise>
		    AND A.TMTB_SQ = #{tmtbSq}
		    </otherwise>
		    </choose>
		    LIMIT #{maxRownum}
		    ) Z
		)
		, TAB_GOODS AS (
		    SELECT G.GOODS_CD
		         , G.GOODS_NM
		         , FN_GET_GOODS_NM(GOODS_NM,GOODS_GB,FOREIGN_BUY_YN,PARALLEL_IMPORT_YN,ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		         -- , G.GOODS_GB
		         -- , G.FOREIGN_BUY_YN
		         -- , G.PARALLEL_IMPORT_YN
		         -- , G.ORDER_MADE_YN
		         , G.GOODS_STAT
		         , G.GOODS_TYPE
		         , G.MAIN_COLOR_CD
		         , G.MIN_ORD_QTY
		         , G.BRAND_GROUP_NM
		         , G.LIST_PRICE
		         , G.STOCK_QTY
		         , FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD, G.CURR_PRICE,#{custGb}) AS CURR_PRICE
		         , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y') AS SYS_IMG_NM
		         , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND MOUSEOVER_IMG_YN = 'Y') AS SYS_IMG_NM2
		         , (CASE WHEN W.GOODS_CD IS NULL THEN 'N' ELSE 'Y' END) AS WISH_YN
		    FROM TAB_MASTER_GOODS G
		    LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
		                                  AND IFNULL(#{custNo}, 0) = W.CUST_NO
		)
		SELECT Z.*
		     , 100 - ROUND((CURR_PRICE / LIST_PRICE) * 100 ,0) AS DC_RATE
		--     , V.VIDEO_GB_M
		--     , V.VIDEO_VAL_M
		--     , V.VIDEO_GB_S
		--     , V.VIDEO_VAL_S
		FROM TAB_GOODS Z
		-- LEFT OUTER JOIN (SELECT MAX(GOODS_CD) AS GOODS_CD
		--                       , MAX(CASE WHEN  RNUM = 1 THEN  VIDEO_GB END) AS  VIDEO_GB_M
		--                       , MAX(CASE WHEN  RNUM = 1 THEN  KMC_KEY END) AS  VIDEO_VAL_M
		--                       , MAX(CASE WHEN  RNUM = 2 THEN  VIDEO_GB END) AS  VIDEO_GB_S
		--                       , MAX(CASE WHEN  RNUM = 2 THEN  KMC_KEY END) AS  VIDEO_VAL_S
		--                 FROM (
		--                       SELECT A.GOODS_CD
		--                            , A.VIDEO_GB
		--                            , A.KMC_KEY
		--                            , A.REG_DT
		--                            , RANK() OVER(PARTITION BY A.GOODS_CD ORDER BY A.REG_DT, A.KMC_KEY ) RNUM
		--                       FROM TAB_GOODS G
		--                       INNER JOIN TB_GOODS_VIDEO A ON G.GOODS_CD = A.GOODS_CD
		--                       WHERE A.DISP_YN = 'Y'
		--                       AND A.KMC_KEY IS NOT NULL
		--                      ) V
		--                 ) V ON Z.GOODS_CD = V.GOODS_CD
	</select>
	
	<!--  추천상품 목록 -->
	<select id="getRecommendGoodsList" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getRecommendGoodsList */
		WITH  TAB_MASTER_GOODS AS (
		SELECT  Z.*
		FROM (
		    SELECT  @rownum := @rownum + 1 AS RNUM
		         , G.GOODS_CD
		         , G.GOODS_NM
		         , G.GOODS_GB
		         , G.FOREIGN_BUY_YN
		         , G.PARALLEL_IMPORT_YN
		         , G.ORDER_MADE_YN
		         , G.GOODS_STAT
		         , G.GOODS_TYPE
		         , G.MAIN_COLOR_CD
		         , G.MIN_ORD_QTY
		         , (CASE WHEN E.DISP_NM_LANG = 'EN' THEN E.BRAND_GROUP_ENM ELSE E.BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
		         , G.LIST_PRICE
		         , G.CURR_PRICE
		         , GS.STOCK_QTY
		         , A.DISP_ORD
		    FROM (
		             ${goodsSql}
		           ) A
		      JOIN ( SELECT @rownum := 0) R     
		      INNER JOIN TB_GOODS G ON A.GOODS_CD = G.GOODS_CD
		      INNER JOIN TB_BRAND D ON G.BRAND_CD = D.BRAND_CD
		                            AND D.USE_YN = 'Y'
		      INNER JOIN TB_SITE_BRAND SB ON D.BRAND_CD  = SB.BRAND_CD
		                                  AND SB.USE_YN = 'Y'
		                                  AND SB.SITE_CD = #{siteCd}
		      INNER JOIN TB_BRAND_GROUP E ON D.BRAND_GROUP_NO = E.BRAND_GROUP_NO
		                                  AND E.USE_YN = 'Y'
		      INNER JOIN TB_GOODS_STOCK GS ON G.GOODS_CD = GS.GOODS_CD
		                                   AND GS.STOCK_QTY > 0
		     WHERE G.GOODS_STAT = 'G008_90'
		      AND G.SELF_MALL_YN = 'Y'
		      AND NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT
		      <![CDATA[
		      AND G.GOODS_CD <> #{goodsCd}  -- 자기상품은 제외
		      ]]>
		    ) Z
		WHERE RNUM <![CDATA[<= ]]>#{maxRownum}
		)
		, TAB_GOODS AS (
		    SELECT G.GOODS_CD
		         , G.GOODS_NM
		         , FN_GET_GOODS_NM(GOODS_NM,GOODS_GB,FOREIGN_BUY_YN,PARALLEL_IMPORT_YN,ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		         , G.GOODS_STAT
		         , G.GOODS_TYPE
		         , G.MAIN_COLOR_CD
		         , G.MIN_ORD_QTY
		         , G.BRAND_GROUP_NM
		         , G.LIST_PRICE
		         , G.STOCK_QTY
		         , G.DISP_ORD
		         , FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD, G.CURR_PRICE,#{custGb}) AS CURR_PRICE
		         , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y') AS SYS_IMG_NM
		         , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND MOUSEOVER_IMG_YN = 'Y') AS SYS_IMG_NM2
		         , (CASE WHEN W.GOODS_CD IS NULL THEN 'N' ELSE 'Y' END) AS WISH_YN
		    FROM TAB_MASTER_GOODS G
		    LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
		                                  AND IFNULL(#{custNo}, 0) = W.CUST_NO
		)
		SELECT Z.*
		     , 100 - ROUND((CURR_PRICE / LIST_PRICE) * 100 ,0) AS DC_RATE
		FROM TAB_GOODS Z
		ORDER BY DISP_ORD
	</select>
	
	<!-- 재입고 알림 미 알림 조회 -->
	<select id="getNotReinboundInform" parameterType="ReinboundInform" resultType="ReinboundInform">
		/* TsfGoods.getNotReinboundInform */
		SELECT RINBD_INFO_SQ
		    , CUST_NO
		    , GOODS_CD
		    , OPT_CD
		    , OPT_CD1
		    , OPT_CD2
		    , REQ_DT
		    , INFORM_DT
		FROM TB_REINBOUND_INFORM
		WHERE CUST_NO = #{custNo}
		AND GOODS_CD = #{goodsCd}
		AND OPT_CD = #{optCd}
		AND INFORM_DT IS NULL  -- 미알림
		<![CDATA[
		AND DATE_FORMAT(NOW(), '%Y%m%d' ) < DATE_FORMAT(DATE_ADD(REQ_DT, INTERVAL 15 DAY),'%Y%m%d')   -- 요청일기준15일경과
		]]>
		ORDER BY  RINBD_INFO_SQ DESC 
		LIMIT 1
	</select>
	
	<!-- 재입고알림 생성 -->
	<insert id="createReinboundInform" parameterType="ReinboundInform">
		/* TsfGoods.createReinboundInform */
		INSERT INTO TB_REINBOUND_INFORM
		    (
		    RINBD_INFO_SQ
		  , CUST_NO
		  , GOODS_CD
		  , OPT_CD
		  , OPT_CD1
		  , OPT_CD2
		  , REQ_DT
		  , REG_NO
		  , REG_DT
		  , UPD_NO
		  , UPD_DT
		    )
		VALUES(
		    NULL
		  , #{custNo}
		  , #{goodsCd}
		  , #{optCd}
		  , #{optCd1}
		  , #{optCd2}
		  , NOW()
		  , #{regNo}
		  , NOW()
		  , #{updNo}
		  , NOW()
		    )
	</insert>
	
	<!-- 재입고알림 수정 -->
	<update id="updateReinboundInform" parameterType="ReinboundInform">
		/* TsfGoods.updateReinboundInform */
		UPDATE TB_REINBOUND_INFORM
		SET REQ_DT = NOW()
		  , UPD_NO = #{updNo}
		  , UPD_DT = NOW()
		WHERE RINBD_INFO_SQ = #{rinbdInfoSq}
	</update>
	
	<!-- 실측사이즈 조회 -->
	<select id="getMeasurementList" parameterType="Goods" resultType="Measurement">
		/* TsfGoods.getMeasurementList */
		SELECT A.GOODS_CD 
		     , A.OPT_CD 
		     , A.OPT_CD1 
		     , A.OPT_CD2 
		     , B.TYPECD 
		     , B.VALUE1 
		     , B.VALUE2
		     , B.VALUE3
		     , B.VALUE4
		     , B.VALUE5
		FROM TB_OPTION A
		INNER JOIN TB_IF_MEASUREMENT B ON A.OPT_CD = B.SKUCODE 
		AND B.ISUSE = '1'                                               -- 사용
		WHERE A.GOODS_CD  =  #{goodsCd}
		AND A.OPT_CD1 = #{optCd1}
	</select>
	
	<!-- 사이즈 정보 조회 -->
	<select id="getSizeInfoList" parameterType="SizeInfo" resultType="SizeInfo">
		/* TsfGoods.getSizeInfoList */
		SELECT A.SIZE_GB
		     , A.SIZE_CATE1_CD
		     , A.SIZE_CATE1_NM
		     , A.SIZE_CATE2_CD
		     , A.SIZE_CATE2_NM
		     , A.SIZE_INFO
		     , A.DISP_ORD
		     , A.USE_YN
		FROM TB_SIZE_INFO A
		WHERE A.USE_YN = 'Y' 
		AND A.SIZE_GB = #{sizeGb}
		<if test="sizeCate1Cd != null and sizeCate1Cd != ''">
		AND A.SIZE_CATE1_CD = #{sizeCate1Cd}
		</if>
		ORDER BY A.SIZE_GB, A.DISP_ORD
	</select>
	
	<!-- 셋트 구성상품의 구성상품 -->
	<select id="getGoodsSetComposeList"  parameterType="Goods" resultType="GoodsCompose">
		/* TsfGoods.getGoodsSetComposeList */
		SELECT A.GOODS_CD
		     , A.COMPS_GOODS_CD
		     , G.GOODS_NM AS COMPS_GOODS_NM
		     , FN_GET_GOODS_NM(G.GOODS_NM,G.GOODS_GB,G.FOREIGN_BUY_YN,G.PARALLEL_IMPORT_YN,G.ORDER_MADE_YN) AS COMPS_GOODS_FULL_NM /*상품FULL명*/
		     , A.QTY
		     , G.BRAND_CD
		     , G.GOODS_STAT
		     , G.MAIN_COLOR_CD AS COLOR_CD
		     , G.ITEMKIND_CD
		     , DATE_FORMAT(G.SELL_STDT ,'%Y%m%d%H%i%S') AS SELL_STDT
		     , DATE_FORMAT(G.SELL_EDDT ,'%Y%m%d%H%i%S') AS SELL_EDDT
		     , (SELECT TI.SIZE_GB FROM TB_ITEMKIND TI WHERE TI.ITEMKIND_CD = G.ITEMKIND_CD) AS SIZE_GB
		     , G.TOBE_FORM_YN
		     , A.COMPS_CURR_PRICE
		     , FN_GET_CODE_NM('G073',G.GOODS_GB) AS GOODS_GB_NM
		     , FN_GET_CODE_NM('G076',G.ORIGIN_CD) AS ORIGIN_NM
		     , (CASE WHEN D.DISP_NM_LANG = 'EN' THEN D.BRAND_GROUP_ENM ELSE D.BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
		     , (SELECT IFNULL(MAX(CASE WHEN SOLDOUT_YN = 'Y' THEN 0
		                          ELSE CURR_STOCK_QTY
		                          END) ,0)
		        FROM  VW_STOCK
		        WHERE GOODS_CD = G.GOODS_CD
		        GROUP BY GOODS_CD 
		        ) AS STOCK_QTY 
		FROM TB_GOODS_COMPOSE A
		INNER JOIN TB_GOODS G ON A.COMPS_GOODS_CD = G.GOODS_CD
		                      AND G.SELF_MALL_YN = 'Y' -- 자사몰 노출
		INNER JOIN TB_BRAND B ON  G.BRAND_CD = B.BRAND_CD
		                      AND B.USE_YN = 'Y'
		INNER JOIN TB_SITE_BRAND SB ON B.BRAND_CD  = SB.BRAND_CD
		                            AND SB.USE_YN = 'Y'
		                            AND SB.SITE_CD = #{siteCd}
		INNER JOIN TB_BRAND_GROUP D ON B.BRAND_GROUP_NO = D.BRAND_GROUP_NO
		                            AND D.USE_YN = 'Y'
		WHERE A.GOODS_CD = #{goodsCd}
		AND A.GOODS_TYPE = 'G056_S'
		AND A.USE_YN = 'Y'
		ORDER BY A.DISP_ORD
	</select>
	
	<!-- 상품의 다다익선 목록 -->
	<select id="getTmtbList"  parameterType="Goods" resultType="Tmtb">
		/* TsfGoods.getTmtbList */
		SELECT Z.*
		FROM (
		      SELECT A.TMTB_SQ 
		           , C.TMTB_SECTION_SQ 
		           , D.TMTB_VAL_SQ 
		           , C.SECTION_GB  -- 구간설정구분값(G810_10:수량,G810_11:금액)
		           , C.SECTION_VAL -- 구간설정값/구간할인시작(수량이상,금액이상)
		           , D.DC_WAY      -- 할인방식(G240_10:할인금액,G240_11:할인율)
		           , D.DC_VAL      -- 할인값
		      FROM TB_TMTB A
		      INNER JOIN TB_TMTB_SECTION C ON A.TMTB_SQ = C.TMTB_SQ 
		                                      AND C.DEL_YN  = 'N'
		      INNER JOIN TB_TMTB_VAL D ON C.TMTB_SQ = D.TMTB_SQ
		                               AND C.TMTB_SECTION_SQ  = D.TMTB_SECTION_SQ  
		                               AND D.DEL_YN  = 'N'
		      INNER JOIN TB_TMTB_APPLY_GOODS B ON A.TMTB_SQ = B.TMTB_SQ
		                                       <choose>
		                                           <when test="arrGoodsCd != null and arrGoodsCd != ''">
		                                       AND B.GOODS_CD IN
		                                           <foreach collection="arrGoodsCd" item="item" index="index"  open="(" close=")" separator=",">
		                                           UPPER(#{item})
		                                           </foreach>
		                                           </when>
		                                           <otherwise>
		                                       AND B.GOODS_CD =  #{goodsCd}
		                                           </otherwise>
		                                       </choose>
		                                       AND B.DEL_YN = 'N'
		                                       AND B.GOODS_GB IN ( 'G800_10', 'G800_20')  -- 기본과 적용상품
		      LEFT OUTER JOIN TB_TMTB_APPLY_GOODS E ON A.TMTB_SQ = E.TMTB_SQ
		                                            <choose>
		                                                <when test="arrGoodsCd != null and arrGoodsCd != ''">
		                                            AND E.GOODS_CD IN
		                                                <foreach collection="arrGoodsCd" item="item" index="index"  open="(" close=")" separator=",">
		                                                UPPER(#{item})
		                                                </foreach>
		                                                </when>
		                                                <otherwise>
		                                            AND E.GOODS_CD =  #{goodsCd}
		                                                </otherwise>
		                                            </choose>
		                                            AND E.DEL_YN = 'N'
		                                            AND E.GOODS_GB = 'G800_30'  -- 제외상품 
		      WHERE NOW() BETWEEN A.TMTB_ST_DT AND A.TMTB_ED_DT
		      AND A.TMTB_STAT ='G232_11'  -- 진행중
		      AND A.DEL_YN = 'N'
		      AND E.GOODS_CD IS NULL                                      -- 제외상품이 아닌 다다익선 
		      ORDER BY A.TMTB_SQ , C.TMTB_SECTION_SQ , D.TMTB_VAL_SQ 
		) Z
		WHERE 1 = 1
		<if test="maxRownum != null and maxRownum > 0">
		LIMIT #{maxRownum}
		</if> 
	</select>
	
	<!-- 상품의 사은품 목록 -->
	<select id="getFreeGoodsList"  parameterType="Goods" resultType="Freegift">
		/* TsfGoods.getFreeGoodsList */
		WITH TAB_GOODS AS (
		          SELECT GOODS_CD
		               , SUPPLY_COMP_CD
		               , BRAND_CD
		          FROM TB_GOODS
		          WHERE GOODS_CD =  #{goodsCd}
		      )
		, TAB_FREEGIFT AS (
		     SELECT A.FREEGIFT_SQ
		          , B.FREEGIFT_SECTION_SQ
		          , A.ALL_YN     -- 모두지급
		          , B.SECTION_GB -- G810_10|수량, G810_11|금액
		          , B.SECTION_VAL
		          , D.GOODS_CD
		     FROM TB_FREEGIFT A
		     INNER JOIN TB_FREEGIFT_SECTION B ON A.FREEGIFT_SQ = B.FREEGIFT_SQ
		     AND B.DEL_YN = 'N'
		     INNER JOIN (SELECT D.FREEGIFT_SQ
		                      , G.GOODS_CD
		                 FROM TAB_GOODS G
		                          INNER JOIN TB_FREEGIFT_GOODS D ON G.GOODS_CD = D.TARGET_VAL
		                     AND D.GOODS_GB IN ('G800_10', 'G800_20')
		                     AND D.TARGET_GB = 'G260_10' -- 상품코드
		                     AND D.DEL_YN = 'N'
		                 UNION
		                 
		                 /* 적용대상 조건이 브랜드는 없이 공급업체만 있을 때 */
		                 SELECT S.FREEGIFT_SQ
		                      , G.GOODS_CD
		                 FROM TAB_GOODS G
		                          INNER JOIN TB_FREEGIFT_GOODS S ON G.SUPPLY_COMP_CD = S.TARGET_VAL
		                     AND S.GOODS_GB IN ('G800_10', 'G800_20')
		                     AND S.TARGET_GB = 'G260_13' -- 공급업체코드
		                     AND S.DEL_YN = 'N'
		                     AND (SELECT COUNT(1) FROM TB_FREEGIFT_GOODS WHERE TARGET_GB = 'G260_12' AND DEL_YN = 'N' AND FREEGIFT_SQ = S.FREEGIFT_SQ) = 0
		                 UNION
		                 
		                 /* 적용대상 조건이 공급업체가 없고 브랜드만 있을 때 */ 
		                 SELECT SB.FREEGIFT_SQ
		                      , G.GOODS_CD
		                 FROM TAB_GOODS G
		                          INNER JOIN TB_FREEGIFT_GOODS SB ON G.BRAND_CD = SB.TARGET_VAL
		                     AND SB.GOODS_GB IN ('G800_10', 'G800_20')
		                     AND SB.TARGET_GB = 'G260_12' -- 브랜드코드
		                     AND SB.DEL_YN = 'N'
		                     AND (SELECT COUNT(1) FROM TB_FREEGIFT_GOODS WHERE TARGET_GB = 'G260_13' AND DEL_YN = 'N' AND FREEGIFT_SQ = SB.FREEGIFT_SQ) = 0
		                 
		                 UNION 
		                    
		                 /* 공급업체와 브랜드 모두 있을 때  */   
		                 SELECT SB1.FREEGIFT_SQ
		                      , G.GOODS_CD
		                 FROM TAB_GOODS G
		                          INNER JOIN TB_FREEGIFT_GOODS SB1 ON G.SUPPLY_COMP_CD = SB1.TARGET_VAL
		                          INNER JOIN TB_FREEGIFT_GOODS SB2 ON G.BRAND_CD = SB2.TARGET_VAL 
		                     AND SB1.GOODS_GB IN ('G800_10', 'G800_20')
		                     AND SB1.TARGET_GB = 'G260_13' -- 공급업체코드
		                     AND SB1.DEL_YN = 'N'		                     
		                     AND SB2.GOODS_GB IN ('G800_10', 'G800_20')
		                     AND SB2.TARGET_GB = 'G260_12' -- 브랜드코드
		                     AND SB2.DEL_YN = 'N'
		                 WHERE 1=1
		                 AND   SB1.FREEGIFT_SQ = SB2.FREEGIFT_SQ    
		                     
		     ) D ON A.FREEGIFT_SQ = D.FREEGIFT_SQ
		     LEFT OUTER JOIN TB_FREEGIFT_GOODS E ON A.FREEGIFT_SQ = E.FREEGIFT_SQ
		                                         AND E.GOODS_GB = 'G800_30'
		                                         AND E.TARGET_GB = 'G260_10'
		                                         AND E.DEL_YN = 'N'
		                                         AND E.TARGET_VAL = #{goodsCd}
		     WHERE NOW() BETWEEN A.FREEGIFT_STDT AND A.FREEGIFT_EDDT
		     AND A.FREEGIFT_STAT = 'G232_11'
		     AND NULLIF(E.TARGET_VAL, '') IS NULL
		     AND A.DEL_YN = 'N'
		     ORDER BY A.FREEGIFT_SQ, B.FREEGIFT_SECTION_SQ
		 )
		 , TAB_FREEGOODS AS (
		     SELECT B.FREEGIFT_SQ
		          , B.FREEGIFT_SECTION_SQ
		          , C.FREEGIFT_VAL_SQ
		          , B.ALL_YN     -- 모두지급
		          , B.SECTION_GB -- G810_10|수량, G810_11|금액
		          , B.SECTION_VAL
		          , F.GOODS_NM
		          , C.ITEM_QTY
		          , CASE WHEN B.ALL_YN = 'Y' THEN 1
		                 ELSE (RANK() OVER (PARTITION BY B.ALL_YN , C.FREEGIFT_SQ, C.FREEGIFT_SECTION_SQ
		                                    ORDER BY  C.FREEGIFT_SQ , C.FREEGIFT_SECTION_SQ , C.FREEGIFT_VAL_SQ)) 
		                 END AS ROWNUM
		     FROM TAB_FREEGIFT B
		     INNER JOIN TB_FREEGIFT_VAL C ON B.FREEGIFT_SQ = C.FREEGIFT_SQ
		                                  AND B.FREEGIFT_SECTION_SQ = C.FREEGIFT_SECTION_SQ
		                                  AND C.DEL_YN = 'N'
		                                  AND C.LEFT_QTY - C.ITEM_QTY >= 0 -- 사은품 잔여수량
		     INNER JOIN TB_FREE_GOODS F ON F.PRODUCT_NO = C.PRODUCT_NO
		                                AND F.USE_YN = 'Y'
		 )
		SELECT Z.*
		FROM (
		    SELECT  FG.FREEGIFT_SQ
		          , FG.FREEGIFT_SECTION_SQ
		          , FG.ALL_YN     -- 모두지급
		          , FG.SECTION_GB -- G810_10|수량, G810_11|금액
		          , FG.SECTION_VAL
		<choose>
		<when test="maxRownum != null and maxRownum > 0">
		          , FG.GOODS_NM
		          , FG.ITEM_QTY
		</when>
		<otherwise>
		          ,  '' AS GOODS_NM
		          ,  0 AS ITEM_QTY
		</otherwise>
		</choose>
		          , GROUP_CONCAT(CONCAT(FG.GOODS_NM,'@:',FG.ITEM_QTY)) AS FREE_GOODS
		          -- , RANK() OVER (PARTITION BY F.FREEGIFT_SQ , F.FREEGIFT_SECTION_SQ ORDER BY  FG.FREEGIFT_VAL_SQ ) AS RNUM
		    FROM TAB_FREEGOODS FG
		     WHERE FG.ROWNUM = 1
		<choose>
		<when test="maxRownum != null and maxRownum > 0">
		    GROUP BY  FG.FREEGIFT_SQ , FG.FREEGIFT_SECTION_SQ , FG.ALL_YN , FG.SECTION_GB , FG.SECTION_VAL, FG.GOODS_NM, FG.ITEM_QTY
		</when>
		<otherwise>
		    GROUP BY  FG.FREEGIFT_SQ , FG.FREEGIFT_SECTION_SQ , FG.ALL_YN , FG.SECTION_GB , FG.SECTION_VAL
		</otherwise>
		</choose>
		    
		    ORDER BY FG.FREEGIFT_SQ, FG.FREEGIFT_SECTION_SQ
		) Z
		WHERE 1 = 1
		<if test="maxRownum != null and maxRownum > 0">
		LIMIT #{maxRownum}
		</if>		
	</select>
	
	<!-- 상품의 사은품 목록 -->
	<select id="getFreeGoodsList_org"  parameterType="Goods" resultType="Freegift">
		/* TsfGoods.getFreeGoodsList_org */
		SELECT Z.*
		FROM (
		      WITH TAB_GOODS AS (
		          SELECT GOODS_CD
		               , SUPPLY_COMP_CD
		               , BRAND_CD
		          FROM TB_GOODS
		          WHERE GOODS_CD = #{goodsCd}
		      )
		      SELECT  A.FREEGIFT_SQ
		            , B.FREEGIFT_SECTION_SQ
		            , C.FREEGIFT_VAL_SQ
		            , A.ALL_YN              -- 모두지급
		            , B.SECTION_GB          -- G810_10|수량, G810_11|금액
		            , B.SECTION_VAL
		            , C.PRODUCT_NO          -- 사은품 번호
		            , F.GOODS_NM            -- 사은품 명
		            , C.ITEM_QTY            -- 사은품 지급 수량
		            , C.LEFT_QTY            -- 잔여수량
		            , D.GOODS_CD
		      FROM TB_FREEGIFT A
		      INNER JOIN TB_FREEGIFT_SECTION B ON A.FREEGIFT_SQ = B.FREEGIFT_SQ
		                                       AND B.DEL_YN = 'N'
		      INNER JOIN TB_FREEGIFT_VAL C ON B.FREEGIFT_SQ = C.FREEGIFT_SQ
		                                   AND B.FREEGIFT_SECTION_SQ = C.FREEGIFT_SECTION_SQ
		                                   AND C.DEL_YN = 'N'
		                                   AND C.LEFT_QTY - C.ITEM_QTY > 0    -- 사은품 잔여수량
		      INNER JOIN TB_FREE_GOODS F ON F.PRODUCT_NO = C.PRODUCT_NO
		                                 AND F.USE_YN = 'Y'
		      INNER JOIN (SELECT D.FREEGIFT_SQ
		                        ,G.GOODS_CD
		                  FROM TAB_GOODS G
		                  INNER JOIN TB_FREEGIFT_GOODS D ON G.GOODS_CD = D.TARGET_VAL
		                                                 AND D.GOODS_GB  IN ( 'G800_10',  'G800_20')
		                                                 AND D.TARGET_GB = 'G260_10'            -- 상품코드
		                                                 AND D.DEL_YN = 'N'
		                  UNION
		                  SELECT S.FREEGIFT_SQ
		                        ,G.GOODS_CD
		                  FROM TAB_GOODS G
		                  INNER JOIN TB_FREEGIFT_GOODS S ON G.SUPPLY_COMP_CD = S.TARGET_VAL
		                                                 AND S.GOODS_GB  IN ( 'G800_10',  'G800_20')
		                                                 AND S.TARGET_GB = 'G260_13'             -- 공급업체코드
		                                                 AND S.DEL_YN = 'N'
		                  UNION
		                  SELECT SB.FREEGIFT_SQ
		                        ,G.GOODS_CD
		                  FROM TAB_GOODS G
		                  INNER JOIN TB_FREEGIFT_GOODS SB ON G.BRAND_CD = SB.TARGET_VAL
		                                                  AND SB.GOODS_GB  IN ( 'G800_10',  'G800_20')
		                                                  AND SB.TARGET_GB = 'G260_12'            -- 브랜드코드
		                                                  AND SB.DEL_YN = 'N'
		                  ) D ON A.FREEGIFT_SQ = D.FREEGIFT_SQ
		      LEFT OUTER JOIN TB_FREEGIFT_GOODS E ON A.FREEGIFT_SQ = E.FREEGIFT_SQ
		                                          AND E.GOODS_GB = 'G800_30'
		                                          AND E.TARGET_GB = 'G260_10'
		                                          AND E.DEL_YN = 'N'
		                                          AND E.TARGET_VAL = #{goodsCd}
		      WHERE NOW() BETWEEN  A.FREEGIFT_STDT AND A.FREEGIFT_EDDT
		      AND  A.FREEGIFT_STAT = 'G232_11'
		      AND NULLIF(E.TARGET_VAL,'') IS NULL
		      AND A.DEL_YN = 'N'
		      ORDER BY A.FREEGIFT_SQ, B.FREEGIFT_SECTION_SQ, C.FREEGIFT_VAL_SQ
		) Z
		WHERE 1 = 1
		<if test="maxRownum != null and maxRownum > 0">
		LIMIT #{maxRownum}
		</if>
	</select>
	
	<!-- 컨텐츠카테고리상품 목록 -->
	<select id="getContentsCategoryGoodsList"  parameterType="Cate4Srch" resultType="Goods">
		/* TsfGoods.getContentsCategoryGoodsList */
		WITH TAB_GOODS AS (
		    SELECT
		           <if test="contentsLoc == 'SMM003' or contentsLoc == 'SBM007' or contentsLoc == 'SBM008' or contentsLoc == 'SBM009' or contentsLoc == 'SBM017' or contentsLoc == 'SBM018' or contentsLoc == 'SBM019' or contentsLoc == 'SOM003' or contentsLoc == 'SOM002'">
					   DISTINCT
				   </if>
		           G.BRAND_GROUP_NM     /*브랜드그룹명*/
		         , G.GOODS_CD           /*상품코드*/
		         , G.GOODS_NM           /*상품명*/
		         , G.GOODS_GB           /*상품구분*/
		         , G.SELF_GOODS_YN      /*자사상품여부*/
		         , 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.REG_DT             /*등록일시*/
		         , G.DISP_ORD
		         <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'">
		         , G.SELL_WEEK_QTY
		         </if>
		    FROM   (
		            SELECT
		                   <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'">
		                      DISTINCT
		                   </if>
		                   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.SELF_GOODS_YN      /*자사상품여부*/
		                 , 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.REG_DT                                                 /*등록일시*/
		                 , G.UPD_DT
		            <choose>
		                <when test="contentsLoc == 'SMM009' or contentsLoc == 'SMM007' or contentsLoc == 'SMM012'">
		                 , CT.DISP_ORD
		                </when>
		                <when test="contentsLoc == 'SMM003' or contentsLoc == 'SBM007' or contentsLoc == 'SBM008' or contentsLoc == 'SBM009' or contentsLoc == 'SBM017' or contentsLoc == 'SBM018' or contentsLoc == 'SBM019' or contentsLoc == 'SOM003' or contentsLoc == 'SCM002' or contentsLoc == 'SCM003' or contentsLoc == 'SOM002'">
		                , CTG.DISP_ORD
		                </when>
		                 <otherwise>
		                 , CG.DISP_ORD
		                 </otherwise>
		            </choose>
		            <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'">
		                 , GS.SELL_WEEK_QTY
		                 , CT.CATE_NO AS CATE1_NO
		            </if>
		            <choose>
		                <when test="contentsLoc == 'SMM009' or contentsLoc == 'SMM007' or contentsLoc == 'SMM012' or contentsLoc == 'SMM003' or contentsLoc == 'SBM007' or contentsLoc == 'SBM008' or contentsLoc == 'SBM009' or contentsLoc == 'SBM017' or contentsLoc == 'SBM018' or contentsLoc == 'SBM019' or contentsLoc == 'SOM003' or contentsLoc == 'SCM002' or contentsLoc == 'SCM003' or contentsLoc == 'SOM002'"> <!-- md추천 -->
		                    FROM	TB_CONTENTS CT
		                    , TB_CONTENTS_GOODS CTG
		                    , TB_GOODS G 
		                    , TB_GOODS_STOCK S
		                    , TB_BRAND B
		                    , TB_BRAND_GROUP BG
		                    <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'">
		                    , TB_GOODS_SUMMARY GS
		                    </if>
		                    WHERE	1=1
		                    AND     CT.CONTENTS_LOC = CTG.CONTENTS_LOC
		                    AND 	CT.DISP_ORD = CTG.CONTENTS_SQ
		                    <if test="contentsLoc == 'SCM003' or contentsLoc == 'SCM002'">
		                    AND    CT.CATE_NO = CTG.CATE_NO
		                    </if>
		                    <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'"> <!-- 베스트 -->
		                    AND    CTG.GOODS_CD = GS.GOODS_CD
		                    </if>
		                    AND		CTG.GOODS_CD = G.GOODS_CD
		                    AND		CTG.GOODS_CD = S.GOODS_CD
		                    AND		G.BRAND_CD = B.BRAND_CD
		                    AND     B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		                    AND		CT.CONTENTS_LOC = #{contentsLoc}
		                    <if test="contentsLoc == 'SMM009' or contentsLoc == 'SMM012'">
		                    AND 	CTG.CONTENTS_SQ = #{dispOrd}
		                    </if>
		                    <if test="contentsLoc == 'SMM007'">
		                    AND		CT.BRAND_GROUP_NO = #{brandGroupNo}
		                    </if>
		                    <if test="contentsLoc == 'SMM003' or contentsLoc == 'SBM007' or contentsLoc == 'SBM008' or contentsLoc == 'SBM009' or contentsLoc == 'SBM017' or contentsLoc == 'SBM018' or contentsLoc == 'SBM019' or contentsLoc == 'SOM003' or contentsLoc == 'SCM002' or contentsLoc == 'SCM003' or contentsLoc == 'SOM002'">
		                        <if test="brandGroupNo != null and brandGroupNo != ''">
		                        AND		CTG.BRAND_GROUP_NO = #{brandGroupNo}
		                        </if>
		                      AND CT.CONTENTS_SQ IN ( SELECT TMP.CONTENTS_SQ FROM (SELECT A.CONTENTS_SQ FROM TB_CONTENTS A WHERE 1=1 AND A.CONTENTS_LOC = #{contentsLoc} AND NOW() BETWEEN A.DISP_STDT AND A.DISP_EDDT
		                                                                                    <if test="brandGroupNo != null and brandGroupNo != ''">
		                                                                                        AND A.BRAND_GROUP_NO = #{brandGroupNo}
		                                                                                    </if>
		                                                                                    <if test="contentsLoc=='SCM003'">
		                                                                                        <if test="cateNo != null and cateNo != ''">
		                                                                                        AND A.CATE_NO = #{cateNo}
		                                                                                        </if>
		                                                                                    </if>
		                                                                                    <if test="contentsLoc=='SCM002'">
		                                                                                        <if test="cate1No != null and cate1No != ''">
		                                                                                            AND A.CATE_NO = #{cate1No}
		                                                                                        </if>
		                                                                                    </if>
		                                                                                    <if test="pageGb != null and pageGb == 'BEST'">
		                                                                                        <if test="cate1No != null and cate1No != ''">
		                                                                                        AND A.CATE_NO = #{cate1No}
		                                                                                        </if>
		                                                                                    </if>
		                                                                                    AND A.USE_YN = 'Y' ORDER BY A.DISP_ORD
		                                                                                    LIMIT 1
		                                                                          ) AS TMP
		                                                              )
		                    </if>
		                    AND		NOW() BETWEEN CT.DISP_STDT AND CT.DISP_EDDT
		                </when>
		                <otherwise>
		                    FROM   TB_CATE_4SRCH C4
		                    , TB_CATE_GOODS CG
		                    , TB_GOODS G USE INDEX (`PRIMARY`, IX_GOODS_11)
		                    , TB_GOODS_STOCK S
		                    , TB_BRAND B
		                    , TB_BRAND_GROUP BG
		                    <if test="contentsLoc == 'SCM003'"> <!-- 베스트 -->
		                    , TB_GOODS_SUMMARY GS
		                    </if>
		                    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
		                    <choose>
		                        <when test="brandGroupNo != null and brandGroupNo != ''">
		                    AND    CG.BRAND_GROUP_NO = #{brandGroupNo}
		                        </when>
		                        <otherwise>
		                    AND    CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
		                        </otherwise>
		                    </choose>
		                    AND    CG.GOODS_CD = G.GOODS_CD
		                    AND    CG.GOODS_CD = S.GOODS_CD
		                    AND    G.BRAND_CD = B.BRAND_CD
		                    AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		                    <if test="contentsLoc == 'SCM003'"> <!-- 베스트 -->
		                    AND    CG.GOODS_CD = GS.GOODS_CD
	                        </if>
	                        AND    C4.SITE_CD = #{siteCd}
	                        AND    C4.CATE_GB = #{cateGb}
	                        AND    C4.CATE_TYPE = 'G031_20' /*컨텐츠카테고리*/
		                    <choose>
		                        <when test="pageGb != null and pageGb == 'BEST'">
		                            <if test="cate1No != null and cate1No != ''">
		                            AND    C4.CATE1_NO = #{cate1No}
		                            </if>
		                        </when>
		                        <otherwise>
		                        AND    C4.CATE1_NO = #{cate1No}
		                        </otherwise>
		                    </choose>
		                    AND    C4.CONTENTS_LOC = #{contentsLoc}
		              </otherwise>
		           </choose>
		            AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		            AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
		            AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT   /*판매기간*/
		            AND    S.STOCK_QTY <![CDATA[>]]> 0 /*재고있는상품*/
		            AND    B.USE_YN = 'Y'
		            AND    BG.USE_YN = 'Y'
		            <if test="pageGb != null and pageGb == 'BEST'">
		            ORDER BY CT.CATE_NO
		            LIMIT 100
		            </if>
		           ) G
		    WHERE  1=1
		<choose>
		    <when test="contentsLoc == 'SCM013'"><!-- 베스트 -->
		     ORDER BY G.SELL_WEEK_QTY DESC , G.GOODS_CD
		    </when>
		    <when test="contentsLoc == 'SMM009' or contentsLoc == 'SMM007' or contentsLoc == 'SMM012'"> <!-- md추천 -->
		    </when>
		    <when test="contentsLoc == 'SCM002' or (contentsLoc == 'SCM003' and pageGb == null)"> <!-- md추천 -->
		     ORDER BY G.DISP_ORD ,G.UPD_DT DESC, G.CURR_PRICE DESC, G.GOODS_CD ASC
		    </when>
		    <when test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'">
		     ORDER BY G.CATE1_NO, G.DISP_ORD ,G.UPD_DT DESC, G.CURR_PRICE DESC, G.GOODS_CD ASC
		    </when>
		    <otherwise>
		     ORDER BY G.DISP_ORD , G.REG_DT DESC , G.GOODS_CD
		    </otherwise>
		</choose>
		<if test="pageable != null and pageable.endRow != null and pageable.endRow > 0">
			LIMIT #{pageable.limitStartRow} , #{pageable.pageSize}
		</if>
		<if test="maxRow != null and maxRow !=''">
		    LIMIT #{maxRow}
		</if>
		)
		SELECT G.BRAND_GROUP_NM
		     , G.GOODS_CD
		     , FN_GET_GOODS_NM(G.GOODS_NM,G.GOODS_GB,G.FOREIGN_BUY_YN,G.PARALLEL_IMPORT_YN,G.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		     , G.GOODS_TNM
		     , G.MAIN_COLOR_CD
		     , G.LIST_PRICE
		     , FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD,G.CURR_PRICE,#{custGb})                           AS CURR_PRICE    /*현재판매가*/
		     , (SELECT SYS_IMG_NM FROM TB_GOODS_IMG X WHERE X.GOODS_CD = G.GOODS_CD AND IF(G.SELF_GOODS_YN= 'N', 'XX', G.MAIN_COLOR_CD) = X.COLOR_CD AND DEFAULT_IMG_YN = 'Y' LIMIT 1) AS SYS_IMG_NM
		     , (SELECT SYS_IMG_NM FROM TB_GOODS_IMG X WHERE X.GOODS_CD = G.GOODS_CD AND IF(G.SELF_GOODS_YN= 'N', 'XX', G.MAIN_COLOR_CD) = X.COLOR_CD AND MOUSEOVER_IMG_YN = 'Y' LIMIT 1) AS SYS_IMG_NM2
		, (
		      SELECT GROUP_CONCAT(CONCAT(VIDEO_GB,':',KMC_KEY) ORDER BY NUMB SEPARATOR ',')
		      FROM   (
		         SELECT GV.VIDEO_GB
		              , GV.KMC_KEY
		              , GV.REG_DT
		              , RANK() OVER(ORDER BY GV.REG_DT, GV.KMC_KEY) AS NUMB
		         FROM   TB_GOODS_VIDEO GV
		         WHERE  GV.GOODS_CD = G.GOODS_CD
		         AND    GV.DISP_YN = 'Y'
		         AND    GV.KMC_KEY IS NOT NULL
		      ) Z
		      WHERE  NUMB  <![CDATA[<=]]>  2
		   ) AS VIDEOS
		, (
		        SELECT GROUP_CONCAT(DISTINCT CONCAT(C.COLOR_CD,':',CC.CD_DESC) ORDER BY CC.DISP_ORD SEPARATOR ',') AS COLOR_CHIPS
		        FROM   TB_OPTION O
		        , TB_COLOR C
		        , TB_COMMON_CODE CC
		        WHERE  O.OPT_CD1 = C.COLOR_CD
		        AND    C.COLOR_GRP_CD = CC.CD
		        AND    O.GOODS_CD = G.GOODS_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 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(ORDER BY GB.BENEFIT_GB, GB.GOODS_CD) AS NUMB
		              FROM   TB_GOODS_BENEFIT GB
		              WHERE  GB.GOODS_CD = G.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_GOODS G
		<if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
		                             AND W.CUST_NO = #{custNo}
		</if>
	</select>
	
	<!-- 컨텐츠카테고리상품 목록(메인용) -->
	<select id="getContentsCategoryGoodsListMain"  parameterType="Cate4Srch" resultType="Goods">
	/* TsfGoods.getContentsCategoryGoodsListMain */
		SELECT
	           <if test="contentsLoc == 'SMM003' or contentsLoc == 'SBM007' or contentsLoc == 'SBM008' or contentsLoc == 'SBM009' or contentsLoc == 'SBM017' or contentsLoc == 'SBM018' or contentsLoc == 'SBM019' or contentsLoc == 'SOM003' or contentsLoc == 'SOM002'">
				   DISTINCT
			   </if>
	           G.BRAND_GROUP_NM     /*브랜드그룹명*/
	         , G.GOODS_CD           /*상품코드*/
	         , G.GOODS_NM           /*상품명*/
	         , G.GOODS_FULL_NM
		     , G.GOODS_TYPE
	         , G.GOODS_GB           /*상품구분*/
	         , G.SELF_GOODS_YN      /*자사상품여부*/
	         , G.FOREIGN_BUY_YN     /*해외구매대행여부*/
	         , G.PARALLEL_IMPORT_YN /*병행수입여부*/
	         , G.ORDER_MADE_YN      /*주문제작여부*/
	         , G.GOODS_TNM          /*상품타이틀명*/
	         , G.MAIN_COLOR_CD      /*대표색상코드*/
	         , G.LIST_PRICE         /*정상가(최초판매가)*/
	         , FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD,G.CURR_PRICE,#{custGb}) AS CURR_PRICE         /*현재판매가*/
	         , G.REG_DT             /*등록일시*/
	         , G.DISP_ORD
	         , G.SYS_IMG_NM
	         , G.SYS_IMG_NM2
	         , G.VIDEOS
	         <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'">
	         , G.SELL_WEEK_QTY
	         </if>
	         <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   (
	            SELECT
	                   <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'">
	                      DISTINCT
	                   </if>
	                   TT.BRAND_GROUP_NM 									     /*브랜드그룹명*/
	                 , TT.GOODS_CD                                               /*상품코드*/
	                 , TT.GOODS_NM                                               /*상품명*/
	                 , TT.GOODS_FULL_NM
	                 , TT.GOODS_GB                                               /*상품구분*/
	                 , TT.SELF_GOODS_YN      /*자사상품여부*/
	                 , TT.FOREIGN_BUY_YN                                         /*해외구매대행여부*/
	                 , TT.PARALLEL_IMPORT_YN                                     /*병행수입여부*/
	                 , TT.ORDER_MADE_YN                                          /*주문제작여부*/
	                 , TT.GOODS_TNM                                              /*상품타이틀명*/
	                 , TT.MAIN_COLOR_CD                                          /*대표색상코드*/
	                 , TT.LIST_PRICE                                             /*정상가(최초판매가)*/
	                 , TT.CURR_PRICE                                             /*현재판매가*/
	                 , TT.REG_DT                                                 /*등록일시*/
	                 , TT.UPD_DT
	                 , TT.SYS_IMG_NM
			         , TT.SYS_IMG_NM2
			         , TT.VIDEOS
	                 , G.GOODS_TYPE
	            <choose>
	                <when test="contentsLoc == 'SMM009' or contentsLoc == 'SMM007' or contentsLoc == 'SMM012'">
	                 , CT.DISP_ORD
	                </when>
	                <when test="contentsLoc == 'SMM003' or contentsLoc == 'SBM007' or contentsLoc == 'SBM008' or contentsLoc == 'SBM009' or contentsLoc == 'SBM017' or contentsLoc == 'SBM018' or contentsLoc == 'SBM019' or contentsLoc == 'SOM003' or contentsLoc == 'SCM002' or contentsLoc == 'SCM003' or contentsLoc == 'SOM002'">
	                , CTG.DISP_ORD
	                </when>
	                 <otherwise>
	                 , CG.DISP_ORD
	                 </otherwise>
	            </choose>
	            <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'">
	                 , GS.SELL_WEEK_QTY
	                 , CT.CATE_NO AS CATE1_NO
	            </if>
	            <choose>
	                <when test="contentsLoc == 'SMM009' or contentsLoc == 'SMM007' or contentsLoc == 'SMM012' or contentsLoc == 'SMM003' or contentsLoc == 'SBM007' or contentsLoc == 'SBM008' or contentsLoc == 'SBM009' or contentsLoc == 'SBM017' or contentsLoc == 'SBM018' or contentsLoc == 'SBM019' or contentsLoc == 'SOM003' or contentsLoc == 'SCM002' or contentsLoc == 'SCM003' or contentsLoc == 'SOM002'"> <!-- md추천 -->
	                    FROM	TB_CONTENTS CT
	                    , TB_CONTENTS_GOODS CTG
	                    , TB_CONTENTS_GOODS_MAIN TT
	                    , TB_GOODS G
	                    <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'">
	                    , TB_GOODS_SUMMARY GS
	                    </if>
	                    WHERE	1=1
	                    AND     CT.CONTENTS_LOC = CTG.CONTENTS_LOC
	                    AND 	CT.DISP_ORD = CTG.CONTENTS_SQ
	                    <if test="contentsLoc == 'SCM003' or contentsLoc == 'SCM002'">
	                    AND    CT.CATE_NO = CTG.CATE_NO
	                    </if>
	                    <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'"> <!-- 베스트 -->
	                    AND    CTG.GOODS_CD = GS.GOODS_CD
	                    </if>
	                    AND		CTG.GOODS_CD = TT.GOODS_CD
	                    AND     CTG.GOODS_CD = G.GOODS_CD
	                    AND		CT.CONTENTS_LOC = #{contentsLoc}
	                    <if test="contentsLoc == 'SMM009' or contentsLoc == 'SMM012'">
	                    AND 	CTG.CONTENTS_SQ = #{dispOrd}
	                    </if>
	                    <if test="contentsLoc == 'SMM007'">
	                    AND		CT.BRAND_GROUP_NO = #{brandGroupNo}
	                    </if>
	                    <if test="contentsLoc == 'SMM003' or contentsLoc == 'SBM007' or contentsLoc == 'SBM008' or contentsLoc == 'SBM009' or contentsLoc == 'SBM017' or contentsLoc == 'SBM018' or contentsLoc == 'SBM019' or contentsLoc == 'SOM003' or contentsLoc == 'SCM002' or contentsLoc == 'SCM003' or contentsLoc == 'SOM002'">
	                        <if test="brandGroupNo != null and brandGroupNo != ''">
	                        AND		CTG.BRAND_GROUP_NO = #{brandGroupNo}
	                        </if>
	                      AND CT.CONTENTS_SQ IN ( SELECT TMP.CONTENTS_SQ FROM (SELECT A.CONTENTS_SQ FROM TB_CONTENTS A WHERE 1=1 AND A.CONTENTS_LOC = #{contentsLoc} AND NOW() BETWEEN A.DISP_STDT AND A.DISP_EDDT
	                                                                                    <if test="brandGroupNo != null and brandGroupNo != ''">
	                                                                                        AND A.BRAND_GROUP_NO = #{brandGroupNo}
	                                                                                    </if>
	                                                                                    <if test="contentsLoc=='SCM003'">
	                                                                                        <if test="cateNo != null and cateNo != ''">
	                                                                                        AND A.CATE_NO = #{cateNo}
	                                                                                        </if>
	                                                                                    </if>
	                                                                                    <if test="contentsLoc=='SCM002'">
	                                                                                        <if test="cate1No != null and cate1No != ''">
	                                                                                            AND A.CATE_NO = #{cate1No}
	                                                                                        </if>
	                                                                                    </if>
	                                                                                    <if test="pageGb != null and pageGb == 'BEST'">
	                                                                                        <if test="cate1No != null and cate1No != ''">
	                                                                                        AND A.CATE_NO = #{cate1No}
	                                                                                        </if>
	                                                                                    </if>
	                                                                                    AND A.USE_YN = 'Y' ORDER BY A.DISP_ORD
	                                                                                    LIMIT 1
	                                                                          ) AS TMP
	                                                              )
	                    </if>
	                    AND		NOW() BETWEEN CT.DISP_STDT AND CT.DISP_EDDT
	                    <if test="contentsLoc == 'SMM007'">
	                    ORDER BY CTG.DISP_ORD
	                    </if>
	                </when>
	                <otherwise>
	                    FROM   TB_CATE_4SRCH C4
	                    , TB_CATE_GOODS CG
	                    , TB_CONTENTS_GOODS_MAIN TT
	                    , TB_GOODS G
	                    <if test="contentsLoc == 'SCM003'"> <!-- 베스트 -->
	                    , TB_GOODS_SUMMARY GS
	                    </if>
	                    WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
	                    <choose>
	                        <when test="brandGroupNo != null and brandGroupNo != ''">
	                    AND    CG.BRAND_GROUP_NO = #{brandGroupNo}
	                        </when>
	                        <otherwise>
	                    AND    CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
	                        </otherwise>
	                    </choose>
	                    AND    CG.GOODS_CD = TT.GOODS_CD
						AND    CG.GOODS_CD = G.GOODS_CD
	                    <if test="contentsLoc == 'SCM003'"> <!-- 베스트 -->
	                    AND    CG.GOODS_CD = GS.GOODS_CD
                        </if>
                        AND    C4.SITE_CD = #{siteCd}
                        AND    C4.CATE_GB = #{cateGb}
                        AND    C4.CATE_TYPE = 'G031_20' /*컨텐츠카테고리*/
	                    <choose>
	                        <when test="pageGb != null and pageGb == 'BEST'">
	                            <if test="cate1No != null and cate1No != ''">
	                            AND    C4.CATE1_NO = #{cate1No}
	                            </if>
	                        </when>
	                        <otherwise>
	                        AND    C4.CATE1_NO = #{cate1No}
	                        </otherwise>
	                    </choose>
	                    AND    C4.CONTENTS_LOC = #{contentsLoc}
	              </otherwise>
	           </choose>
	           
	            <if test="pageGb != null and pageGb == 'BEST'">
	            ORDER BY CT.CATE_NO
	            LIMIT 100
	            </if>
	           ) G
	           <if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
				LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
				                             AND W.CUST_NO = #{custNo}
			   </if>
				   
		    WHERE  1=1
		<choose>
		    <when test="contentsLoc == 'SCM013'"><!-- 베스트 -->
		     ORDER BY G.SELL_WEEK_QTY DESC , G.GOODS_CD
		    </when>
		    <when test="contentsLoc == 'SMM009' or contentsLoc == 'SMM007' or contentsLoc == 'SMM012'"> <!-- md추천 -->
		    </when>
		    <when test="contentsLoc == 'SCM002' or (contentsLoc == 'SCM003' and pageGb == null)"> <!-- md추천 -->
		     ORDER BY G.DISP_ORD ,G.UPD_DT DESC, G.CURR_PRICE DESC, G.GOODS_CD ASC
		    </when>
		    <when test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'">
		     ORDER BY G.CATE1_NO, G.DISP_ORD ,G.UPD_DT DESC, G.CURR_PRICE DESC, G.GOODS_CD ASC
		    </when>
		    <otherwise>
		     ORDER BY G.DISP_ORD , G.REG_DT DESC , G.GOODS_CD
		    </otherwise>
		</choose>
		<if test="pageable != null and pageable.endRow != null and pageable.endRow > 0">
			LIMIT #{pageable.limitStartRow} , #{pageable.pageSize}
		</if>
		<if test="maxRow != null and maxRow !=''">
		    LIMIT #{maxRow}
		</if>
		
	</select>

	<!-- 컨텐츠카테고리상품 건수 -->
	<select id="getContentsCategoryGoodsCount"  parameterType="Cate4Srch" resultType="int">
		/* TsfGoods.getContentsCategoryGoodsCount */
		WITH TAB_GOODS AS (
		           SELECT G.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.REG_DT             /*등록일시*/
		           FROM   (
		                    SELECT 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.REG_DT                                                 /*등록일시*/
		        <choose>
		            <when test="contentsLoc == 'SMM009' or contentsLoc == 'SMM007' or contentsLoc == 'SMM012' or contentsLoc == 'SMM003' or contentsLoc == 'SBM007' or contentsLoc == 'SBM008' or contentsLoc == 'SBM009' or contentsLoc == 'SBM017' or contentsLoc == 'SBM018' or contentsLoc == 'SBM019' or contentsLoc == 'SOM003' or contentsLoc == 'SCM002' or contentsLoc == 'SCM003'">
		                FROM	TB_CONTENTS CT
		                , TB_CONTENTS_GOODS CTG
		                , TB_GOODS G USE INDEX (`PRIMARY`, IX_GOODS_11)
		                , TB_GOODS_STOCK S
		                , TB_BRAND B
		                , TB_BRAND_GROUP BG
		                <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'">
		                	, TB_GOODS_SUMMARY GS
		                </if>
		                WHERE	CT.CONTENTS_LOC = CTG.CONTENTS_LOC
		                AND 	CT.DISP_ORD = CTG.CONTENTS_SQ
		                <if test="contentsLoc == 'SCM003'">
		                AND    CT.CATE_NO = CTG.CATE_NO
		                </if>
		                <if test="contentsLoc == 'SCM003' and pageGb != null and pageGb == 'BEST'"> <!-- 베스트 -->
		                AND    CTG.GOODS_CD = GS.GOODS_CD
		                </if>
		                AND		CTG.GOODS_CD = G.GOODS_CD
		                AND		CTG.GOODS_CD = S.GOODS_CD
		                AND		G.BRAND_CD = B.BRAND_CD
		                AND     B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		                AND		CT.CONTENTS_LOC = #{contentsLoc}
		                <if test="contentsLoc == 'SMM009' or contentsLoc == 'SMM012'">
		                AND 	CTG.CONTENTS_SQ = #{dispOrd}
		                </if>
		                <if test="contentsLoc == 'SMM007'">
		                AND		CT.BRAND_GROUP_NO = #{brandGroupNo}
		                </if>
		                <if test="contentsLoc == 'SMM003' or contentsLoc == 'SBM007' or contentsLoc == 'SBM008' or contentsLoc == 'SBM009' or contentsLoc == 'SBM017' or contentsLoc == 'SBM018' or contentsLoc == 'SBM019' or contentsLoc == 'SOM003' or contentsLoc == 'SCM002' or contentsLoc == 'SCM003'">
		                    <if test="brandGroupNo != null and brandGroupNo != ''">
		                    AND		CTG.BRAND_GROUP_NO = #{brandGroupNo}
		                    </if>
		                  AND CT.CONTENTS_SQ IN ( SELECT TMP.CONTENTS_SQ FROM (SELECT A.CONTENTS_SQ FROM TB_CONTENTS A WHERE 1=1 AND A.CONTENTS_LOC = #{contentsLoc} AND NOW() BETWEEN A.DISP_STDT AND A.DISP_EDDT
		                                                                                 <if test="brandGroupNo != null and brandGroupNo != ''">
		                                                                                 AND A.BRAND_GROUP_NO = #{brandGroupNo}
		                                                                                 </if>
		                                                                                 <if test="contentsLoc=='SCM003'">
		                                                                                     <if test="cateNo != null and cateNo != ''">
		                                                                                 AND A.CATE_NO = #{cateNo}
		                                                                                     </if>
		                                                                                 </if>
		                                                                                 <if test="pageGb != null and pageGb == 'BEST'">
		                                                                                     <if test="cate1No != null and cate1No != ''">
		                                                                                 AND    A.CATE_NO = #{cate1No}
		                                                                                     </if>
		                                                                                 </if>
		                                                                                 AND A.USE_YN = 'Y' ORDER BY A.CONTENTS_SQ
		                                                                                 <if test="pageGb == null and pageGb != 'BEST'">
		                                                                                 LIMIT 1
		                                                                                 </if>
		                                                                            ) AS TMP
		                                         )
		                </if>
		                AND		NOW() BETWEEN CT.DISP_STDT AND CT.DISP_EDDT
		            </when>
		            <otherwise>
		                FROM   TB_CATE_4SRCH C4
		                , TB_CATE_GOODS CG
		                , TB_GOODS G USE INDEX (`PRIMARY`, IX_GOODS_11)
		                , TB_GOODS_STOCK S
		                , TB_BRAND B
		                , TB_BRAND_GROUP BG
		                <if test="contentsLoc == 'SCM003'"> <!-- 베스트 -->
		                , TB_GOODS_SUMMARY GS
		                </if>
		                WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
		                <choose>
		                    <when test="brandGroupNo != null and brandGroupNo != ''">
		                        AND    CG.BRAND_GROUP_NO = #{brandGroupNo}</when>
		                    <otherwise>
		                        AND    CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
		                    </otherwise>
		                </choose>
		                AND    CG.GOODS_CD = G.GOODS_CD
		                AND    CG.GOODS_CD = S.GOODS_CD
		                AND    G.BRAND_CD = B.BRAND_CD
		                AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		                <if test="contentsLoc == 'SCM003'"> <!-- 베스트 -->
		                AND    CG.GOODS_CD = GS.GOODS_CD
		                </if>
		                AND    C4.SITE_CD = #{siteCd}
		                AND    C4.CATE_GB = #{cateGb}
		                AND    C4.CATE_TYPE = 'G031_20' /*컨텐츠카테고리*/
		                <choose>
		                    <when test="pageGb != null and pageGb == 'BEST'">
		                        <if test="cate1No != null and cate1No != ''">
		                        AND    C4.CATE1_NO = #{cate1No}
		                        </if>
		                    </when>
		                    <otherwise>
		                        AND    C4.CATE1_NO = #{cate1No}
		                    </otherwise>
		                </choose>
		                AND    C4.CONTENTS_LOC = #{contentsLoc}
		            </otherwise>
		        </choose>
		        AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		        AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
		        AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT   /*판매기간*/
		        AND    S.STOCK_QTY <![CDATA[>]]> 0 /*재고있는상품*/
		        AND    B.USE_YN = 'Y'
		        AND    BG.USE_YN = 'Y'
		        <if test="pageGb != null and pageGb == 'BEST'">
		        	ORDER BY CT.CATE_NO
		        </if>
		        ) G
		    WHERE  1=1
		    <if test="pageable != null and pageable.endRow != null and pageable.endRow > 0">
		    LIMIT #{pageable.limitStartRow} , #{pageable.pageSize}
		    </if>
		    <if test="maxRow != null and maxRow !=''">
		    LIMIT #{maxRow}
		    </if>
		    )
		SELECT COUNT(1) AS CNT
		FROM TAB_GOODS G
		<if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		    LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
		    AND W.CUST_NO = #{custNo}
		</if>
	</select>
	
	<!-- 컨텐츠카테고리 신규상품 목록 -->
	<select id="getContentsCategoryNewGoodsList"  parameterType="Cate4Srch" resultType="Goods">
		/* TsfGoods.getContentsCategoryNewGoodsList */
		WITH TAB_GOODS AS (
		    SELECT G.BRAND_GROUP_NM     /*브랜드그룹명*/
		         , G.GOODS_CD           /*상품코드*/
		         , G.GOODS_NM           /*상품명*/
		         , G.GOODS_GB           /*상품구분*/
		         , G.SELF_GOODS_YN      /*자사상품여부*/
		         , 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
		    FROM   (
		            SELECT DISTINCT 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.SELF_GOODS_YN                                          /*자사상품여부*/
		                 , 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                                             /*현재판매가*/
		                 , RRG.DISP_ORD                                             /*노출순서*/
		                 , G.GOODS_TYPE
		            FROM   TB_RECENT_REG_GOODS RRG
		                 , TB_GOODS G
		                 , TB_GOODS_STOCK GS
		                 , TB_BRAND B
		                 , TB_BRAND_GROUP BG
		            WHERE  RRG.GOODS_CD = G.GOODS_CD
		            AND    RRG.GOODS_CD = GS.GOODS_CD
		            AND    G.BRAND_CD = B.BRAND_CD
		            AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		            <if test="(cate1No == null or cate1No == '') and (brandGroupNo == null or brandGroupNo == '')">
		            AND    RRG.MAIN_GB = 'M' /*몰메인*/
		            </if>
		            <if test="cate1No != null and cate1No != ''">
		            AND    RRG.MAIN_GB = 'C' /*카테고리메인*/
		            AND    RRG.CATE_BGRP_NO = #{cate1No}
		            </if>
		            <if test="brandGroupNo != null and brandGroupNo != ''">
		            AND    RRG.MAIN_GB = 'B' /*브랜드메인*/
		            AND    RRG.CATE_BGRP_NO = #{brandGroupNo}
		            </if>
		            <if test="exceptGoodsArr != null and exceptGoodsArr.length > 0">
		            AND	   RRG.GOODS_CD NOT IN
		                <foreach collection="exceptGoodsArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		                </foreach>
		            </if>
		            AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		            AND    G.FORMAL_GB = 'G009_10' /*정상상품*/
		            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'
		            ORDER  BY RRG.DISP_ORD
		            <if test="maxRow != null and maxRow !=''">
		            LIMIT #{maxRow}
		            </if>
		           ) G
		    WHERE  1=1
		<if test="pageable != null and pageable.endRow != null and pageable.endRow > 0">
			LIMIT #{pageable.limitStartRow} , #{pageable.pageSize}
		</if>
		)
		SELECT G.BRAND_GROUP_NM
		     , G.GOODS_CD
		     , FN_GET_GOODS_NM(G.GOODS_NM,G.GOODS_GB,G.FOREIGN_BUY_YN,G.PARALLEL_IMPORT_YN,G.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		     , G.GOODS_TNM
		     , G.GOODS_TYPE
		     , G.MAIN_COLOR_CD
		     , G.LIST_PRICE
		     , FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD,G.CURR_PRICE,#{custGb})                           AS CURR_PRICE    /*현재판매가*/
		     , (SELECT SYS_IMG_NM FROM TB_GOODS_IMG X WHERE X.GOODS_CD = G.GOODS_CD AND IF(G.SELF_GOODS_YN= 'N', 'XX', G.MAIN_COLOR_CD) = X.COLOR_CD AND DEFAULT_IMG_YN = 'Y' LIMIT 1) AS SYS_IMG_NM
		     , (SELECT SYS_IMG_NM FROM TB_GOODS_IMG X WHERE X.GOODS_CD = G.GOODS_CD AND IF(G.SELF_GOODS_YN= 'N', 'XX', G.MAIN_COLOR_CD) = X.COLOR_CD AND MOUSEOVER_IMG_YN = 'Y' LIMIT 1) AS SYS_IMG_NM2
		     , (
		        SELECT GROUP_CONCAT(CONCAT(VIDEO_GB,':',KMC_KEY) ORDER BY NUMB SEPARATOR ',')
		        FROM   (
		             SELECT GV.VIDEO_GB
		                  , GV.KMC_KEY
		                  , GV.REG_DT
		                  , RANK() OVER(ORDER BY GV.REG_DT, GV.KMC_KEY) AS NUMB
		             FROM   TB_GOODS_VIDEO GV
		             WHERE  GV.GOODS_CD = G.GOODS_CD
		             AND    GV.DISP_YN = 'Y'
		             AND    GV.KMC_KEY IS NOT NULL
		          ) Z
		        WHERE  NUMB <![CDATA[<=]]> 2
		     ) AS VIDEOS
		     <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_GOODS G
		<if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
		                             AND W.CUST_NO = #{custNo}
		</if>
	</select>

	<!-- 배송비 SAVE 상품 조회 -->
	<select id="getDelvFeeSaveGoodsList" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getDelvFeeSaveGoodsList : 배송비 SAVE 상품 조회 */
		SELECT G.GOODS_CD
		     , G.LIST_PRICE
		     , G.GOODS_NM
		     , A.OPT_CD
		     , A.BRAND_NM
		     , FN_GET_APPLY_CPN1_PRICE(G.GOODS_CD,  #{frontGb}) AS PRICE
		     , CASE WHEN G.LIST_PRICE <![CDATA[ < ]]> G.CURR_PRICE THEN G.LIST_PRICE ELSE G.CURR_PRICE END AS CURR_PRICE
		     , ROUND((((FN_GET_APPLY_CPN1_PRICE(G.GOODS_CD,  #{frontGb}) / G.LIST_PRICE) - 1) * 100) * - 1, 0) AS DC_RATE
		     , CASE WHEN G.GOODS_TYPE != 'G056_S' AND G.SELF_GOODS_YN = 'Y' THEN (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = G.MAIN_COLOR_CD AND DEFAULT_IMG_YN = 'Y')
		            ELSE (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y') END AS SYS_IMG_NM
		FROM   TB_GOODS G
		INNER  JOIN (SELECT G.GOODS_CD
		                  , (SELECT OPT_CD FROM vw_stock X WHERE X.GOODS_CD = G.GOODS_CD AND X.SOLDOUT_YN = 'N' AND X.CURR_STOCK_QTY > 0 ORDER BY OPT_CD LIMIT 1) AS OPT_CD
		                  , CASE WHEN D.DISP_NM_LANG = 'EN' THEN D.BRAND_GROUP_ENM ELSE D.BRAND_GROUP_KNM END AS BRAND_NM
		             FROM   TB_GOODS G USE INDEX (IX_GOODS_11)
		             INNER  JOIN TB_BRAND BR
		             ON     G.BRAND_CD = BR.BRAND_CD
		             INNER  JOIN TB_BRAND_GROUP D
		             ON     BR.BRAND_GROUP_NO = D.BRAND_GROUP_NO
		             AND    D.USE_YN = 'Y'
		             INNER  JOIN TB_SUPPLY_COMPANY CM
		             ON     G.SUPPLY_COMP_CD = CM.SUPPLY_COMP_CD
		             WHERE  1=1
		             AND    G.GOODS_STAT = 'G008_90'
		             AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT
		             AND    G.GOODS_TYPE = 'G056_N'
		             AND    G.SELF_MALL_YN = 'Y'
		             AND    G.MIN_ORD_QTY = 1
		             AND    BR.USE_YN = 'Y'
		             AND    CM.USE_YN = 'Y'
		             <if test="goodsCd != null and goodsCd != ''">
		             AND    G.GOODS_CD = #{goodsCd}
		             </if>
		             <if test="price != null and price != ''">
		             AND    G.CURR_PRICE BETWEEN ${price} AND ${price} + 10000
		             </if>
		             <if test="delvFeeCd != null and delvFeeCd != ''">
		             AND    G.DELV_FEE_CD = #{delvFeeCd}
		             </if>
		             <if test="selfGoodsYn != null and selfGoodsYn != ''">
		             AND    G.SELF_GOODS_YN = #{selfGoodsYn}
		             </if>
		             LIMIT  20 ) A
		ON     G.GOODS_CD = A.GOODS_CD
	</select>

	<!-- 카테고리별 상품 총건수 -->
	<select id="getCategoryGoodsCount" parameterType="SearchEngine" resultType="int">
		/* TsfGoods.getCategoryGoodsCount */
		WITH TAB_GOODS AS (
		     SELECT DISTINCT G.BRAND_GROUP_NM     /*브랜드그룹명*/
		          , G.GOODS_CD           /*상품코드*/
		          , G.GOODS_NM           /*상품명*/
		          , G.GOODS_GB           /*상품구분*/
		          , G.SELF_GOODS_YN      /*자사상품여부*/
		          , G.FOREIGN_BUY_YN     /*해외구매대행여부*/
		          , G.PARALLEL_IMPORT_YN /*병행수입여부*/
		          , G.ORDER_MADE_YN      /*주문제작여부*/
		          , G.GOODS_TNM          /*상품타이틀명*/
		          , IFNULL(O.OPT_CD1,G.MAIN_COLOR_CD) AS MAIN_COLOR_CD      /*대표색상코드*/
		          , G.LIST_PRICE         /*정상가(최초판매가)*/
		          , G.CURR_PRICE         /*현재판매가*/
		          , G.FORMAL_GB          /*정상이월구분*/
		          , G.REG_DT             /*등록일시*/
		          , G.SELL_WEEK_QTY      /*주간판매수량*/
		          , G.REVIEW_REG_CNT     /*리뷰등록건수*/
		     FROM   (
		          SELECT 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.SELF_GOODS_YN                         /*자사상품여부*/
		               , G.FOREIGN_BUY_YN                        /*해외구매대행여부*/
		               , G.PARALLEL_IMPORT_YN                    /*병행수입여부*/
		               , G.ORDER_MADE_YN                         /*주문제작여부*/
		               , G.GOODS_TNM                             /*상품타이틀명*/
		               , G.MAIN_COLOR_CD                         /*대표색상코드*/
		               , G.LIST_PRICE                            /*정상가(최초판매가)*/
		               , CASE WHEN #{frontGb} = 'P' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_PC_CURR_PRICE,G.CURR_PRICE)
		                      WHEN #{frontGb} = 'M' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_MO_CURR_PRICE,G.CURR_PRICE)
		                      WHEN #{frontGb} = 'A' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_APP_CURR_PRICE,G.CURR_PRICE)
		                      WHEN #{frontGb} = 'P' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE)
		                      WHEN #{frontGb} = 'M' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.MO_CURR_PRICE,G.CURR_PRICE)
		                      WHEN #{frontGb} = 'A' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.APP_CURR_PRICE,G.CURR_PRICE)
		                      ELSE G.CURR_PRICE
		                 END                   AS CURR_PRICE     /*현재판매가*/
		               , G.MIN_ORD_AMT                           /*최소주문금액*/
		               , G.FORMAL_GB                             /*정상이월구분*/
		               , G.REG_DT                                /*등록일시*/
		               , GS.SELL_WEEK_QTY                        /*주간판매수량*/
		               , GS.REVIEW_REG_CNT + (SELECT COUNT(1)
		                                      FROM   TB_REVIEW Z
		                                      WHERE  1=1
		                                      AND    Z.GOODS_CD = GS.GOODS_CD
		                                      AND    Z.DISP_YN = 'Y'
		                                      AND    Z.DEL_YN = 'N'
		                                      AND    Z.CONFIRM_YN = 'Y'
		                                      AND    Z.REG_DT BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL + 1 DAY)) AS REVIEW_REG_CNT /*리뷰등록건수*/
		          FROM   TB_CATE_4SRCH C4
		          INNER JOIN TB_CATE_GOODS CG ON C4.LEAF_CATE_NO = CG.CATE_NO
		          INNER JOIN TB_GOODS G ON CG.GOODS_CD = G.GOODS_CD
		          INNER JOIN TB_GOODS_STOCK S ON CG.GOODS_CD = S.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_SUMMARY GS ON CG.GOODS_CD = GS.GOODS_CD
		          LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON CG.GOODS_CD = GBP.GOODS_CD
		          WHERE  C4.SITE_CD = #{siteCd}
		          AND    C4.CATE_GB = #{cateGb}
		          AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		         <if test="cate1No != null and cate1No != ''">
		          AND    C4.CATE1_NO = #{cate1No}
		         </if>
		         <if test="cate2No != null and cate2No != ''">
		          AND    C4.CATE2_NO = #{cate2No}
		         </if>
		         <if test="cate3No != null and cate3No != ''">
		          AND    C4.CATE3_NO = #{cate3No}
		         </if>
		         <if test="cate4No != null and cate4No != ''">
		          AND    C4.CATE4_NO = #{cate4No}
		         </if>
		         <if test="cate5No != null and cate5No != ''">
		          AND    C4.CATE5_NO = #{cate5No}
		         </if>
		         <choose>
		            <when test="brandGroupNo != null and brandGroupNo > 0">
		          AND    B.BRAND_GROUP_NO = #{brandGroupNo}
		             </when>
		             <otherwise>
		          AND    CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
		             </otherwise>
		         </choose>
		         <if test="formalGb != null and formalGb !=''">
		          AND    G.FORMAL_GB = #{formalGb}
		         </if>
		          AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		          AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		          AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*유효한 판매기간*/
		         <if test="ageArr != null and ageArr.length > 0"> <!-- 연령대 -->
		          AND    G.AGE_GRP_CD IN
		             <foreach collection="ageArr" item="item" index="index"  open="(" close=")" separator=",">
		             #{item}
		             </foreach>
		         </if>
		         <if test="seasonArr != null and seasonArr.length > 0"> <!-- 시즌 -->
		          AND    G.SEASON_CD IN
		             <foreach collection="seasonArr" item="item" index="index"  open="(" close=")" separator=",">
		             #{item}
		             </foreach>
		         </if>
		          AND    S.STOCK_QTY > 0 /*재고있는 상품*/
		          AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
		          AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
		          AND G.SELF_GOODS_YN = 'Y'
		         <if test="brandGroupArr != null and brandGroupArr.length > 0">
		          AND    BG.BRAND_GROUP_NO IN
		             <foreach collection="brandGroupArr" item="item" index="index"  open="(" close=")" separator=",">
		             #{item}
		             </foreach>
		         </if>
		         <if test="unisex != null and unisex != ''">
		          AND    G.SEX_GB = #{unisex}
		         </if>
		         <if test='sizeArr != null and sizeArr.length > 0'>
		          AND    EXISTS (SELECT 1
		                         FROM   TB_OPTION
		                         WHERE  GOODS_CD = CG.GOODS_CD
		                         AND    OPT_CD2 IN
		                         <foreach collection="sizeArr" item="item" index="index"  open="(" close=")" separator=",">
		                             #{item}
		                         </foreach>
		                         AND    DISP_YN = 'Y'
		                         )
		         </if>
		         <if test="newGoods != null and newGoods != ''">
		          AND    EXISTS (SELECT 1
		                         FROM   TB_GOODS_BENEFIT
		                         WHERE  GOODS_CD = G.GOODS_CD
		                         AND    BENEFIT_GB = #{newGoods}
		                         )
		         </if>
		         <if test='benefitArr != null and benefitArr.length > 0'>
		          AND    EXISTS (SELECT 1
		                         FROM   TB_GOODS_BENEFIT
		                         WHERE  GOODS_CD = G.GOODS_CD
		                         AND    BENEFIT_GB IN
		                         <foreach collection="benefitArr" item="item" index="index"  open="(" close=")" separator=",">
		                             #{item}
		                         </foreach>
		                         )
		         </if>
		         <if test="priceFrom != null and priceFrom != ''">
		          AND    G.CURR_PRICE <![CDATA[>=]]> #{priceFrom}
		         </if>
		         <if test="priceTo != null and priceTo != ''">
		          AND    G.CURR_PRICE <![CDATA[<=]]> #{priceTo}
		         </if>
		         <if test="dcRateFrom != null and dcRateFrom != ''">
		          AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[>=]]> #{dcRateFrom}
		         </if>
		         <if test="dcRateTo != null and dcRateTo != ''">
		          AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[<=]]> #{dcRateTo}
		         </if>
		     ) G
		     INNER JOIN TB_OPTION O ON G.GOODS_CD = O.GOODS_CD AND    O.DISP_YN = 'Y' <!--AND G.SELF_GOODS_YN = 'Y' /*자사상품만*/-->
		     <if test="colorArr != null and colorArr.length > 0"> <!-- 색상 -->
		      AND    O.OPT_CD1 IN (SELECT COLOR_CD
		                            FROM   TB_COLOR
		                            WHERE  1 = 1
		                            AND    COLOR_GRP_CD IN
		                            <foreach collection="colorArr" item="item" index="index"  open="(" close=")" separator=",">
		                                #{item}
		                            </foreach>
		                            AND    USE_YN = 'Y' /*사용하는색상*/
		                        )
		     </if>
		    UNION ALL
		    SELECT DISTINCT G.BRAND_GROUP_NM     /*브랜드그룹명*/
		         , G.GOODS_CD           /*상품코드*/
		         , G.GOODS_NM           /*상품명*/
		         , G.GOODS_GB           /*상품구분*/
		         , G.SELF_GOODS_YN      /*자사상품여부*/
		         , G.FOREIGN_BUY_YN     /*해외구매대행여부*/
		         , G.PARALLEL_IMPORT_YN /*병행수입여부*/
		         , G.ORDER_MADE_YN      /*주문제작여부*/
		         , G.GOODS_TNM          /*상품타이틀명*/
		         , NULL AS MAIN_COLOR_CD      /*대표색상코드*/
		         , G.LIST_PRICE         /*정상가(최초판매가)*/
		         , G.CURR_PRICE         /*현재판매가*/
		         , G.FORMAL_GB          /*정상이월구분*/
		         , G.REG_DT             /*등록일시*/
		         , G.SELL_WEEK_QTY      /*주간판매수량*/
		         , G.REVIEW_REG_CNT     /*리뷰등록건수*/
		    FROM   (
		         SELECT 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.SELF_GOODS_YN                         /*자사상품여부*/
		             , G.FOREIGN_BUY_YN                        /*해외구매대행여부*/
		             , G.PARALLEL_IMPORT_YN                    /*병행수입여부*/
		             , G.ORDER_MADE_YN                         /*주문제작여부*/
		             , G.GOODS_TNM                             /*상품타이틀명*/
		             , G.MAIN_COLOR_CD                         /*대표색상코드*/
		             , G.LIST_PRICE                            /*정상가(최초판매가)*/
		             , CASE WHEN #{frontGb} = 'P' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_PC_CURR_PRICE,G.CURR_PRICE)
		                WHEN #{frontGb} = 'M' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_MO_CURR_PRICE,G.CURR_PRICE)
		                WHEN #{frontGb} = 'A' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_APP_CURR_PRICE,G.CURR_PRICE)
		                WHEN #{frontGb} = 'P' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE)
		                WHEN #{frontGb} = 'M' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.MO_CURR_PRICE,G.CURR_PRICE)
		                WHEN #{frontGb} = 'A' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.APP_CURR_PRICE,G.CURR_PRICE)
		                ELSE G.CURR_PRICE
		                END                   AS CURR_PRICE     /*현재판매가*/
		             , G.MIN_ORD_AMT                           /*최소주문금액*/
		             , G.FORMAL_GB                             /*정상이월구분*/
		             , G.REG_DT                                /*등록일시*/
		             , GS.SELL_WEEK_QTY                        /*주간판매수량*/
		             , GS.REVIEW_REG_CNT                       /*리뷰등록건수*/
		         FROM   TB_CATE_4SRCH C4
		         INNER JOIN TB_CATE_GOODS CG ON C4.LEAF_CATE_NO = CG.CATE_NO
		         INNER JOIN TB_GOODS G USE INDEX (`PRIMARY`, IX_GOODS_11) ON CG.GOODS_CD = G.GOODS_CD
		         INNER JOIN TB_GOODS_STOCK S ON CG.GOODS_CD = S.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_SUMMARY GS ON CG.GOODS_CD = GS.GOODS_CD
		         LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON CG.GOODS_CD = GBP.GOODS_CD
		         WHERE  C4.SITE_CD = #{siteCd}
		         AND    C4.CATE_GB = #{cateGb}
		         AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		        <if test="cate1No != null and cate1No != ''">
		         AND    C4.CATE1_NO = #{cate1No}
		        </if>
		        <if test="cate2No != null and cate2No != ''">
		         AND    C4.CATE2_NO = #{cate2No}
		        </if>
		        <if test="cate3No != null and cate3No != ''">
		         AND    C4.CATE3_NO = #{cate3No}
		        </if>
		        <if test="cate4No != null and cate4No != ''">
		         AND    C4.CATE4_NO = #{cate4No}
		        </if>
		        <if test="cate5No != null and cate5No != ''">
		         AND    C4.CATE5_NO = #{cate5No}
		        </if>
		        <choose>
		            <when test="brandGroupNo != null and brandGroupNo > 0">
		         AND    B.BRAND_GROUP_NO = #{brandGroupNo}
		            </when>
		            <otherwise>
		         AND    CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
		            </otherwise>
		        </choose>
		        <if test="formalGb != null and formalGb !=''">
		         AND    G.FORMAL_GB = #{formalGb}
		        </if>
		         AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		         AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		         AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*유효한 판매기간*/
		        <if test="ageArr != null and ageArr.length > 0"> <!-- 연령대 -->
		         AND    G.AGE_GRP_CD IN
		            <foreach collection="ageArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		            </foreach>
		        </if>
		        <if test="seasonArr != null and seasonArr.length > 0"> <!-- 시즌 -->
		         AND    G.SEASON_CD IN
		            <foreach collection="seasonArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		            </foreach>
		        </if>
		         AND    S.STOCK_QTY > 0 /*재고있는 상품*/
		         AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
		         AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
		         AND G.SELF_GOODS_YN = 'N'
		        <if test="brandGroupArr != null and brandGroupArr.length > 0">
		         AND    BG.BRAND_GROUP_NO IN
		            <foreach collection="brandGroupArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		            </foreach>
		        </if>
		        <if test="unisex != null and unisex != ''">
		         AND    G.SEX_GB = #{unisex}
		        </if>
		        <if test='sizeArr != null and sizeArr.length > 0'>
		         AND    EXISTS (SELECT 1
		                        FROM   TB_OPTION
		                        WHERE  GOODS_CD = CG.GOODS_CD
		                        AND    OPT_CD2 IN
		                        <foreach collection="sizeArr" item="item" index="index"  open="(" close=")" separator=",">
		                            #{item}
		                        </foreach>
		                        AND    DISP_YN = 'Y'
		                        )
		        </if>
		        <if test="newGoods != null and newGoods != ''">
		         AND    EXISTS (SELECT 1
		                        FROM   TB_GOODS_BENEFIT
		                        WHERE  GOODS_CD = G.GOODS_CD
		                        AND    BENEFIT_GB = #{newGoods}
		                        )
		        </if>
		        <if test='benefitArr != null and benefitArr.length > 0'>
		         AND    EXISTS (SELECT 1
		                        FROM   TB_GOODS_BENEFIT
		                        WHERE  GOODS_CD = G.GOODS_CD
		                        AND    BENEFIT_GB IN
		                        <foreach collection="benefitArr" item="item" index="index"  open="(" close=")" separator=",">
		                            #{item}
		                        </foreach>
		                        )
		        </if>
		        <if test="colorArr != null and colorArr.length > 0"> <!-- 색상 -->
		         AND    G.MAIN_COLOR_CD IN (SELECT COLOR_CD
		                                     FROM   TB_COLOR
		                                     WHERE  1 = 1
		                                     AND    COLOR_GRP_CD IN
		                                     <foreach collection="colorArr" item="item" index="index"  open="(" close=")" separator=",">
		                                         #{item}
		                                     </foreach>
		                                     AND    USE_YN = 'Y' /*사용하는색상*/
		                                     )
		        </if>
		        <if test="priceFrom != null and priceFrom != ''">
		         AND    G.CURR_PRICE <![CDATA[>=]]> #{priceFrom}
		        </if>
		        <if test="priceTo != null and priceTo != ''">
		         AND    G.CURR_PRICE <![CDATA[<=]]> #{priceTo}
		        </if>
		        <if test="dcRateFrom != null and dcRateFrom != ''">
		         AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[>=]]> #{dcRateFrom}
		        </if>
		        <if test="dcRateTo != null and dcRateTo != ''">
		         AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[<=]]> #{dcRateTo}
		        </if>
		    ) G
		)
		SELECT COUNT(*)
		FROM   TAB_GOODS G
		WHERE 1=1
	</select>

	<!-- 카테고리별 상품 목록 -->
	<select id="getCategoryGoodsList" parameterType="SearchEngine" resultType="SearchEngine">
		/* TsfGoods.getCategoryGoodsList */
		WITH TAB_GOODS AS (
		    SELECT DISTINCT G.BRAND_GROUP_NM     /*브랜드그룹명*/
		        , G.GOODS_CD           /*상품코드*/
		        , G.GOODS_NM           /*상품명*/
		        , G.GOODS_GB           /*상품구분*/
		        , G.SELF_GOODS_YN      /*자사상품여부*/
		        , G.FOREIGN_BUY_YN     /*해외구매대행여부*/
		        , G.PARALLEL_IMPORT_YN /*병행수입여부*/
		        , G.ORDER_MADE_YN      /*주문제작여부*/
		        , G.GOODS_TNM          /*상품타이틀명*/
		        , IFNULL(O.OPT_CD1,G.MAIN_COLOR_CD) AS MAIN_COLOR_CD      /*대표색상코드*/
		        , G.LIST_PRICE         /*정상가(최초판매가)*/
		        , G.CURR_PRICE         /*현재판매가*/
		        , G.FORMAL_GB          /*정상이월구분*/
		        , G.REG_DT             /*등록일시*/
		        , G.SELL_WEEK_QTY      /*주간판매수량*/
		        , G.REVIEW_REG_CNT     /*리뷰등록건수*/
		    FROM   (
		            SELECT 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.SELF_GOODS_YN                         /*자사상품여부*/
		                , G.FOREIGN_BUY_YN                        /*해외구매대행여부*/
		                , G.PARALLEL_IMPORT_YN                    /*병행수입여부*/
		                , G.ORDER_MADE_YN                         /*주문제작여부*/
		                , G.GOODS_TNM                             /*상품타이틀명*/
		                , G.MAIN_COLOR_CD                         /*대표색상코드*/
		                , G.LIST_PRICE                            /*정상가(최초판매가)*/
		                , CASE WHEN #{frontGb} = 'P' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_PC_CURR_PRICE,G.CURR_PRICE)
		                       WHEN #{frontGb} = 'M' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_MO_CURR_PRICE,G.CURR_PRICE)
		                       WHEN #{frontGb} = 'A' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_APP_CURR_PRICE,G.CURR_PRICE)
		                       WHEN #{frontGb} = 'P' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE)
		                       WHEN #{frontGb} = 'M' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.MO_CURR_PRICE,G.CURR_PRICE)
		                       WHEN #{frontGb} = 'A' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.APP_CURR_PRICE,G.CURR_PRICE)
		                       ELSE G.CURR_PRICE
		                   END                   AS CURR_PRICE     /*현재판매가*/
		                , G.MIN_ORD_AMT                           /*최소주문금액*/
		                , G.FORMAL_GB                             /*정상이월구분*/
		                , G.REG_DT                                /*등록일시*/
		                , GS.SELL_WEEK_QTY                        /*주간판매수량*/
		                , GS.REVIEW_REG_CNT + (SELECT COUNT(1)
		                                       FROM   TB_REVIEW Z
		                                       WHERE  1=1
		                                       AND    Z.GOODS_CD = GS.GOODS_CD
		                                       AND    Z.DISP_YN = 'Y'
		                                       AND    Z.DEL_YN = 'N'
		                                       AND    Z.CONFIRM_YN = 'Y'
		                                       AND    Z.REG_DT BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL + 1 DAY)) AS REVIEW_REG_CNT                       /*리뷰등록건수*/
		            FROM   TB_CATE_4SRCH C4
		            INNER JOIN TB_CATE_GOODS CG ON C4.LEAF_CATE_NO = CG.CATE_NO
		            INNER JOIN TB_GOODS G ON CG.GOODS_CD = G.GOODS_CD
		            INNER JOIN TB_GOODS_STOCK S ON CG.GOODS_CD = S.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_SUMMARY GS ON CG.GOODS_CD = GS.GOODS_CD
		            LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON CG.GOODS_CD = GBP.GOODS_CD
		            WHERE  C4.SITE_CD = #{siteCd}
		            AND    C4.CATE_GB = #{cateGb}
		            AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		            <if test="cate1No != null and cate1No != ''">
		             AND    C4.CATE1_NO = #{cate1No}
		            </if>
		            <if test="cate2No != null and cate2No != ''">
		             AND    C4.CATE2_NO = #{cate2No}
		            </if>
		            <if test="cate3No != null and cate3No != ''">
		             AND    C4.CATE3_NO = #{cate3No}
		            </if>
		            <if test="cate4No != null and cate4No != ''">
		             AND    C4.CATE4_NO = #{cate4No}
		            </if>
		            <if test="cate5No != null and cate5No != ''">
		             AND    C4.CATE5_NO = #{cate5No}
		            </if>
		            <choose>
		                <when test="brandGroupNo != null and brandGroupNo > 0">
		             AND    B.BRAND_GROUP_NO = #{brandGroupNo}
		                </when>
		                <otherwise>
		             AND    CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
		                </otherwise>
		            </choose>
		            <if test="formalGb != null and formalGb !=''">
		             AND    G.FORMAL_GB = #{formalGb}
		            </if>
		             AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		             AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		             AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*유효한 판매기간*/
		            <if test="ageArr != null and ageArr.length > 0"> <!-- 연령대 -->
		             AND    G.AGE_GRP_CD IN
		                <foreach collection="ageArr" item="item" index="index"  open="(" close=")" separator=",">
		                    #{item}
		                </foreach>
		            </if>
		            <if test="seasonArr != null and seasonArr.length > 0"> <!-- 시즌 -->
		             AND    G.SEASON_CD IN
		                <foreach collection="seasonArr" item="item" index="index"  open="(" close=")" separator=",">
		                    #{item}
		                </foreach>
		            </if>
		             AND    S.STOCK_QTY > 0 /*재고있는 상품*/
		             AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
		             AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
		             AND G.SELF_GOODS_YN = 'Y'
		            <if test="brandGroupArr != null and brandGroupArr.length > 0">
		             AND    BG.BRAND_GROUP_NO IN
		                <foreach collection="brandGroupArr" item="item" index="index"  open="(" close=")" separator=",">
		                    #{item}
		                </foreach>
		            </if>
		            <if test="unisex != null and unisex != ''">
		             AND    G.SEX_GB = #{unisex}
		            </if>
		            <if test='sizeArr != null and sizeArr.length > 0'>
		             AND    EXISTS (SELECT 1
		                            FROM   TB_OPTION
		                            WHERE  GOODS_CD = CG.GOODS_CD
		                            AND    OPT_CD2 IN
		                            <foreach collection="sizeArr" item="item" index="index"  open="(" close=")" separator=",">
		                                #{item}
		                            </foreach>
		                            AND    DISP_YN = 'Y'
		                            )
		            </if>
		            <if test="newGoods != null and newGoods != ''">
		             AND    EXISTS (SELECT 1
		                           FROM   TB_GOODS_BENEFIT
		                           WHERE  GOODS_CD = G.GOODS_CD
		                           AND    BENEFIT_GB = #{newGoods}
		                           )
		            </if>
		            <if test='benefitArr != null and benefitArr.length > 0'>
		             AND    EXISTS (SELECT 1
		                            FROM   TB_GOODS_BENEFIT
		                            WHERE  GOODS_CD = G.GOODS_CD
		                            AND    BENEFIT_GB IN
		                            <foreach collection="benefitArr" item="item" index="index"  open="(" close=")" separator=",">
		                                #{item}
		                            </foreach>
		                            )
		            </if>
		            <if test="priceFrom != null and priceFrom != ''">
		             AND    G.CURR_PRICE <![CDATA[>=]]> #{priceFrom}
		            </if>
		            <if test="priceTo != null and priceTo != ''">
		             AND    G.CURR_PRICE <![CDATA[<=]]> #{priceTo}
		            </if>
		            <if test="dcRateFrom != null and dcRateFrom != ''">
		             AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[>=]]> #{dcRateFrom}
		            </if>
		            <if test="dcRateTo != null and dcRateTo != ''">
		             AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[<=]]> #{dcRateTo}
		            </if>
		        ) G
		        INNER JOIN TB_OPTION O ON G.GOODS_CD = O.GOODS_CD AND    O.DISP_YN = 'Y' <!--AND G.SELF_GOODS_YN = 'Y' /*자사상품만*/-->
		    <if test="colorArr != null and colorArr.length > 0"> <!-- 색상 -->
		        AND    O.OPT_CD1 IN (SELECT COLOR_CD
		                             FROM   TB_COLOR
		                             WHERE  1 = 1
		                             AND    COLOR_GRP_CD IN
		                             <foreach collection="colorArr" item="item" index="index"  open="(" close=")" separator=",">
		                                 #{item}
		                             </foreach>
		                             AND    USE_YN = 'Y' /*사용하는색상*/
		                           )
		    </if>
		    UNION ALL
		    SELECT DISTINCT G.BRAND_GROUP_NM     /*브랜드그룹명*/
		         , G.GOODS_CD           /*상품코드*/
		         , G.GOODS_NM           /*상품명*/
		         , G.GOODS_GB           /*상품구분*/
		         , G.SELF_GOODS_YN      /*자사상품여부*/
		         , G.FOREIGN_BUY_YN     /*해외구매대행여부*/
		         , G.PARALLEL_IMPORT_YN /*병행수입여부*/
		         , G.ORDER_MADE_YN      /*주문제작여부*/
		         , G.GOODS_TNM          /*상품타이틀명*/
		         , NULL AS MAIN_COLOR_CD      /*대표색상코드*/
		         , G.LIST_PRICE         /*정상가(최초판매가)*/
		         , G.CURR_PRICE         /*현재판매가*/
		         , G.FORMAL_GB          /*정상이월구분*/
		         , G.REG_DT             /*등록일시*/
		         , G.SELL_WEEK_QTY      /*주간판매수량*/
		         , G.REVIEW_REG_CNT     /*리뷰등록건수*/
		    FROM   (
		         SELECT 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.SELF_GOODS_YN                         /*자사상품여부*/
		               , G.FOREIGN_BUY_YN                        /*해외구매대행여부*/
		               , G.PARALLEL_IMPORT_YN                    /*병행수입여부*/
		               , G.ORDER_MADE_YN                         /*주문제작여부*/
		               , G.GOODS_TNM                             /*상품타이틀명*/
		               , G.MAIN_COLOR_CD                         /*대표색상코드*/
		               , G.LIST_PRICE                            /*정상가(최초판매가)*/
		               , CASE WHEN #{frontGb} = 'P' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_PC_CURR_PRICE,G.CURR_PRICE)
		               WHEN #{frontGb} = 'M' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_MO_CURR_PRICE,G.CURR_PRICE)
		               WHEN #{frontGb} = 'A' AND #{custGb} = 'G100_20' THEN  IFNULL(GBP.STAFF_APP_CURR_PRICE,G.CURR_PRICE)
		               WHEN #{frontGb} = 'P' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE)
		               WHEN #{frontGb} = 'M' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.MO_CURR_PRICE,G.CURR_PRICE)
		               WHEN #{frontGb} = 'A' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.APP_CURR_PRICE,G.CURR_PRICE)
		               ELSE G.CURR_PRICE
		               END                   AS CURR_PRICE     /*현재판매가*/
		               , G.MIN_ORD_AMT                           /*최소주문금액*/
		               , G.FORMAL_GB                             /*정상이월구분*/
		               , G.REG_DT                                /*등록일시*/
		               , GS.SELL_WEEK_QTY                        /*주간판매수량*/
		               , GS.REVIEW_REG_CNT + (SELECT COUNT(1)
		                                      FROM   TB_REVIEW Z
		                                      WHERE  1=1
		                                      AND    Z.GOODS_CD = GS.GOODS_CD
		                                      AND    Z.DISP_YN = 'Y'
		                                      AND    Z.DEL_YN = 'N'
		                                      AND    Z.CONFIRM_YN = 'Y'
		                                      AND    Z.REG_DT BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL + 1 DAY)) AS REVIEW_REG_CNT                      /*리뷰등록건수*/
		         FROM   TB_CATE_4SRCH C4
		         INNER JOIN TB_CATE_GOODS CG ON C4.LEAF_CATE_NO = CG.CATE_NO
		         INNER JOIN TB_GOODS G USE INDEX (`PRIMARY`, IX_GOODS_11) ON CG.GOODS_CD = G.GOODS_CD
		         INNER JOIN TB_GOODS_STOCK S ON CG.GOODS_CD = S.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_SUMMARY GS ON CG.GOODS_CD = GS.GOODS_CD
		         LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON CG.GOODS_CD = GBP.GOODS_CD
		         WHERE  C4.SITE_CD = #{siteCd}
		         AND    C4.CATE_GB = #{cateGb}
		         AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		        <if test="cate1No != null and cate1No != ''">
		         AND    C4.CATE1_NO = #{cate1No}
		        </if>
		        <if test="cate2No != null and cate2No != ''">
		         AND    C4.CATE2_NO = #{cate2No}
		        </if>
		        <if test="cate3No != null and cate3No != ''">
		         AND    C4.CATE3_NO = #{cate3No}
		        </if>
		        <if test="cate4No != null and cate4No != ''">
		         AND    C4.CATE4_NO = #{cate4No}
		        </if>
		        <if test="cate5No != null and cate5No != ''">
		         AND    C4.CATE5_NO = #{cate5No}
		        </if>
		        <choose>
		            <when test="brandGroupNo != null and brandGroupNo > 0">
		         AND    B.BRAND_GROUP_NO = #{brandGroupNo}
		            </when>
		            <otherwise>
		          AND    CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
		            </otherwise>
		        </choose>
		        <if test="formalGb != null and formalGb !=''">
		          AND    G.FORMAL_GB = #{formalGb}
		        </if>
		          AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		          AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		          AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*유효한 판매기간*/
		        <if test="ageArr != null and ageArr.length > 0"> <!-- 연령대 -->
		         AND    G.AGE_GRP_CD IN
		            <foreach collection="ageArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		            </foreach>
		        </if>
		        <if test="seasonArr != null and seasonArr.length > 0"> <!-- 시즌 -->
		         AND    G.SEASON_CD IN
		            <foreach collection="seasonArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		            </foreach>
		        </if>
		         AND    S.STOCK_QTY > 0 /*재고있는 상품*/
		         AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
		         AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
		         AND G.SELF_GOODS_YN = 'N'
		        <if test="brandGroupArr != null and brandGroupArr.length > 0">
		         AND    BG.BRAND_GROUP_NO IN
		            <foreach collection="brandGroupArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		            </foreach>
		        </if>
		        <if test="unisex != null and unisex != ''">
		         AND    G.SEX_GB = #{unisex}
		        </if>
		        <if test='sizeArr != null and sizeArr.length > 0'>
		         AND    EXISTS (SELECT 1
		                         FROM   TB_OPTION
		                         WHERE  GOODS_CD = CG.GOODS_CD
		                         AND    OPT_CD2 IN
		                         <foreach collection="sizeArr" item="item" index="index"  open="(" close=")" separator=",">
		                             #{item}
		                         </foreach>
		                         AND    DISP_YN = 'Y'
		                         )
		        </if>
		        <if test="newGoods != null and newGoods != ''">
		         AND    EXISTS (SELECT 1
		                        FROM   TB_GOODS_BENEFIT
		                        WHERE  GOODS_CD = G.GOODS_CD
		                        AND    BENEFIT_GB = #{newGoods}
		                        )
		        </if>
		        <if test='benefitArr != null and benefitArr.length > 0'>
		         AND    EXISTS (SELECT 1
		                        FROM   TB_GOODS_BENEFIT
		                        WHERE  GOODS_CD = G.GOODS_CD
		                        AND    BENEFIT_GB IN
		                        <foreach collection="benefitArr" item="item" index="index"  open="(" close=")" separator=",">
		                           #{item}
		                        </foreach>
		                        )
		        </if>
		        <if test="colorArr != null and colorArr.length > 0"> <!-- 색상 -->
		         AND    G.MAIN_COLOR_CD IN (SELECT COLOR_CD
		                                     FROM   TB_COLOR
		                                     WHERE  1 = 1
		                                     AND    COLOR_GRP_CD IN
		                                     <foreach collection="colorArr" item="item" index="index"  open="(" close=")" separator=",">
		                                         #{item}
		                                     </foreach>
		                                     AND    USE_YN = 'Y' /*사용하는색상*/
		                                     )
		        </if>
		        <if test="priceFrom != null and priceFrom != ''">
		         AND    G.CURR_PRICE <![CDATA[>=]]> #{priceFrom}
		        </if>
		        <if test="priceTo != null and priceTo != ''">
		         AND    G.CURR_PRICE <![CDATA[<=]]> #{priceTo}
		        </if>
		        <if test="dcRateFrom != null and dcRateFrom != ''">
		         AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[>=]]> #{dcRateFrom}
		        </if>
		        <if test="dcRateTo != null and dcRateTo != ''">
		         AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[<=]]> #{dcRateTo}
		        </if>
		    ) G
		    <choose>
		        <when test="sortingType == 'BEST'"> <!-- 인기상품순 -->
		     ORDER BY SELF_GOODS_YN DESC, SELL_WEEK_QTY DESC, GOODS_CD
		        </when>
		        <when test="sortingType == 'REVIEW'"> <!-- 리뷰많은순 -->
		     ORDER BY SELF_GOODS_YN DESC, REVIEW_REG_CNT DESC, GOODS_CD
		        </when>
		        <when test="sortingType == 'ROWPRICE'"> <!-- 낮은가격순 -->
		     ORDER BY CURR_PRICE, GOODS_CD
                 </when>
		        <otherwise> <!-- 최신상품순 -->
		     ORDER BY SELF_GOODS_YN DESC, REG_DT DESC, GOODS_CD
		        </otherwise>
		    </choose>
		    <if test="pageable != null and pageable.endRow != null and pageable.endRow > 0">
		     LIMIT #{pageable.limitStartRow} , #{pageable.pageSize}
		    </if>
		)
		SELECT G.BRAND_GROUP_NM
		     , G.GOODS_CD
		     , FN_GET_GOODS_NM(G.GOODS_NM,G.GOODS_GB,G.FOREIGN_BUY_YN,G.PARALLEL_IMPORT_YN,G.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		     , G.GOODS_TNM
		     , G.SELF_GOODS_YN
		     , G.MAIN_COLOR_CD
		     , G.LIST_PRICE
		     , G.CURR_PRICE
		     , (SELECT SYS_IMG_NM FROM TB_GOODS_IMG X WHERE X.GOODS_CD = G.GOODS_CD AND IF(G.SELF_GOODS_YN= 'N', 'XX', G.MAIN_COLOR_CD) = X.COLOR_CD AND DEFAULT_IMG_YN = 'Y' LIMIT 1) AS SYS_IMG_NM
		     , (SELECT SYS_IMG_NM FROM TB_GOODS_IMG X WHERE X.GOODS_CD = G.GOODS_CD AND IF(G.SELF_GOODS_YN= 'N', 'XX', G.MAIN_COLOR_CD) = X.COLOR_CD AND MOUSEOVER_IMG_YN = 'Y' LIMIT 1) AS SYS_IMG_NM2
		     , (
		          SELECT GROUP_CONCAT(CONCAT(VIDEO_GB,':',KMC_KEY) ORDER BY NUMB SEPARATOR ',')
		          FROM   (
		                 SELECT GV.VIDEO_GB
		                 , GV.KMC_KEY
		                 , GV.REG_DT
		                 , RANK() OVER(ORDER BY GV.REG_DT, GV.KMC_KEY) AS NUMB
		                 FROM   TB_GOODS_VIDEO GV
		                 WHERE  GV.GOODS_CD = G.GOODS_CD
		                 AND    GV.DISP_YN = 'Y'
		                 AND    GV.KMC_KEY IS NOT NULL
		          ) Z
		          WHERE  NUMB <![CDATA[<=]]> 2
		       ) AS VIDEOS
		     , (
		          SELECT GROUP_CONCAT(DISTINCT CONCAT(C.COLOR_CD,':',CC.CD_DESC) ORDER BY CC.DISP_ORD SEPARATOR ',') AS COLOR_CHIPS
		          FROM   TB_OPTION O
		          , TB_COLOR C
		          , TB_COMMON_CODE CC
		          WHERE  O.OPT_CD1 = C.COLOR_CD
		          AND    C.COLOR_GRP_CD = CC.CD
		          AND    O.GOODS_CD = G.GOODS_CD
		          AND    O.DISP_YN = 'Y'
		          AND    C.USE_YN = 'Y'
		          AND    CC.USE_YN = 'Y'
		     ) AS COLOR_CHIPS /*컬러칩*/
		     , (
		         SELECT GROUP_CONCAT(DISTINCT CONCAT(OPT_CD2 ,':' ,CASE WHEN SOLDOUT_YN = 'Y' THEN 'Y'
		         ELSE CASE WHEN CURR_STOCK_QTY - BASE_STOCK_QTY > 0 THEN 'N' ELSE 'Y' END
		         END) ORDER BY DISP_ORD SEPARATOR ',') AS SIZES
		         FROM   VW_STOCK
		         WHERE  GOODS_CD = G.GOODS_CD
		         AND    OPT_CD1 = G.MAIN_COLOR_CD
		         AND    DISP_YN = 'Y'
		     ) AS SIZES /*사이즈*/
		     , (
		         SELECT GROUP_CONCAT(CONCAT(BENEFIT_GB,':',BENEFIT_NM) ORDER BY NUMB SEPARATOR ',')
		         FROM   (
		            SELECT 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(ORDER BY GB.BENEFIT_GB, GB.GOODS_CD) AS NUMB
		            FROM   TB_GOODS_BENEFIT GB
		            WHERE  GB.GOODS_CD = G.GOODS_CD
		         ) Z
		     ) AS BENEFITS
		     , G.FORMAL_GB
		     , G.REG_DT
		<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_GOODS G
		<if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		 LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
		 AND    W.CUST_NO = #{custNo}
		</if>
		WHERE 1=1
	</select>
	
	<!-- 검색상품 총건수 -->
	<select id="getSearchGoodsCount" parameterType="SearchEngine" resultType="int">
		/* TsfGoods.getSearchGoodsCount */
		SELECT COUNT(*)
		FROM   (
		        SELECT 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.SELF_GOODS_YN                        /*자사상품여부*/
		             , G.FOREIGN_BUY_YN                       /*해외구매대행여부*/
		             , G.PARALLEL_IMPORT_YN                   /*병행수입여부*/
		             , G.ORDER_MADE_YN                        /*주문제작여부*/
		             , G.GOODS_TNM                            /*상품타이틀명*/
		             , G.MAIN_COLOR_CD                        /*대표색상코드*/
		             , G.LIST_PRICE                           /*정상가(최초판매가)*/
		             , CASE WHEN #{frontGb} = 'P' AND #{custGb} = 'G100_20' THEN GBP.STAFF_PC_CURR_PRICE
		                    WHEN #{frontGb} = 'M' AND #{custGb} = 'G100_20' THEN GBP.STAFF_MO_CURR_PRICE
		                    WHEN #{frontGb} = 'A' AND #{custGb} = 'G100_20' THEN GBP.STAFF_APP_CURR_PRICE
		                    WHEN #{frontGb} = 'P' AND #{custGb} != 'G100_20' THEN GBP.PC_CURR_PRICE
		                    WHEN #{frontGb} = 'M' AND #{custGb} != 'G100_20' THEN GBP.MO_CURR_PRICE
		                    WHEN #{frontGb} = 'A' AND #{custGb} != 'G100_20' THEN GBP.APP_CURR_PRICE
		                    ELSE G.CURR_PRICE
		               END                  AS CURR_PRICE     /*현재판매가*/
		             , G.MIN_ORD_AMT                          /*최소주문금액*/
		             , G.FORMAL_GB                            /*정상이월구분*/
		             , G.REG_DT                               /*등록일시*/
		             , GS.SELL_WEEK_QTY                       /*주간판매수량*/
		             , GS.REVIEW_REG_CNT                      /*리뷰등록건수*/
		        FROM   TB_GOODS G USE INDEX (IX_GOODS_11)
		        INNER JOIN TB_GOODS_STOCK S ON G.GOODS_CD = S.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_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD
		        LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON G.GOODS_CD = GBP.GOODS_CD
		        WHERE  (
		                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 /*유효한 판매기간*/
		        <if test="ageArr != null and ageArr.length > 0"> <!-- 연령대 -->
		        AND    G.AGE_GRP_CD IN
		            <foreach collection="ageArr" item="item" index="index"  open="(" close=")" separator=",">
		            #{item}
		            </foreach>
		        </if>
		        <if test="seasonArr != null and seasonArr.length > 0"> <!-- 시즌 -->
		        AND    G.SEASON_CD IN
		            <foreach collection="seasonArr" item="item" index="index"  open="(" close=")" separator=",">
		            #{item}
		            </foreach>
		        </if>
		        AND    S.STOCK_QTY > 0 /*재고있는 상품*/
		        AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
		        AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
		        <if test="brandGroupArr != null and brandGroupArr.length > 0">
		        AND    BG.BRAND_GROUP_NO IN
		            <foreach collection="brandGroupArr" item="item" index="index"  open="(" close=")" separator=",">
		            #{item}
		            </foreach>
		        </if>
		        <if test="unisex != null and unisex != ''">
		        AND    G.SEX_GB = #{unisex}
		        </if>
		        <if test='sizeArr != null and sizeArr.length > 0'>
		        AND    EXISTS (SELECT 1
		                       FROM   TB_OPTION
		                       WHERE  GOODS_CD = G.GOODS_CD
		                       AND    OPT_CD2 IN
		                       <foreach collection="sizeArr" item="item" index="index"  open="(" close=")" separator=",">
		                       #{item}
		                       </foreach>
		                       AND    DISP_YN = 'Y'
		                      )
		        </if>
		        <if test="colorArr != null and colorArr.length > 0">
		        AND    EXISTS (SELECT 1
		                       FROM   TB_OPTION O
		                            , TB_COLOR C
		                       WHERE  O.GOODS_CD = G.GOODS_CD
		                       AND    O.OPT_CD1 = C.COLOR_CD
		                       AND    O.DISP_YN = 'Y'
		                       AND    C.COLOR_GRP_CD IN
		                       <foreach collection="colorArr" item="item" index="index"  open="(" close=")" separator=",">
		                       #{item}
		                       </foreach>
		                       AND    C.USE_YN = 'Y' /*사용하는색상*/
		                       AND    G.SELF_GOODS_YN = 'Y' /*자사상품만*/
		                      )
		        </if>
		        <if test="newGoods != null and newGoods != ''">
		        AND    EXISTS (SELECT 1
		                       FROM   TB_GOODS_BENEFIT
		                       WHERE  GOODS_CD = G.GOODS_CD
		                       AND    BENEFIT_GB = #{newGoods}
		                      )
		        </if>
		        <if test='benefitArr != null and benefitArr.length > 0'>
		        AND    EXISTS (SELECT 1
		                       FROM   TB_GOODS_BENEFIT
		                       WHERE  GOODS_CD = G.GOODS_CD
		                       AND    BENEFIT_GB IN
		                       <foreach collection="benefitArr" item="item" index="index"  open="(" close=")" separator=",">
		                       #{item}
		                       </foreach>
		                      )
		        </if>
		       ) G
		WHERE  1 = 1
		<if test="priceFrom != null and priceFrom != ''">
		AND    G.CURR_PRICE <![CDATA[>=]]> #{priceFrom}
		</if>
		<if test="priceTo != null and priceTo != ''">
		AND    G.CURR_PRICE <![CDATA[<=]]> #{priceTo}
		</if>
		<if test="dcRateFrom != null and dcRateFrom != ''">
		AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[>=]]> #{dcRateFrom}
		</if>
		<if test="dcRateTo != null and dcRateTo != ''">
		AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[<=]]> #{dcRateTo}
		</if>
	</select>
	
	<!-- 검색 상품 목록 -->
	<select id="getSearchGoodsList" parameterType="SearchEngine" resultType="SearchEngine">
		/* TsfGoods.getSearchGoodsList */
		WITH TAB_GOODS AS (
		    SELECT G.BRAND_GROUP_NM     /*브랜드그룹명*/
		         , G.GOODS_CD           /*상품코드*/
		         , G.GOODS_NM           /*상품명*/
		         , G.GOODS_GB           /*상품구분*/
		         , G.SELF_GOODS_YN      /*자사상품여부*/
		         , 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.FORMAL_GB          /*정상이월구분*/
		         , G.REG_DT             /*등록일시*/
		         , G.SELL_WEEK_QTY      /*주간판매수량*/
		         , G.REVIEW_REG_CNT     /*리뷰등록건수*/
		    FROM   (
		            SELECT 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.SELF_GOODS_YN                         /*자사상품여부*/
		                 , G.FOREIGN_BUY_YN                        /*해외구매대행여부*/
		                 , G.PARALLEL_IMPORT_YN                    /*병행수입여부*/
		                 , G.ORDER_MADE_YN                         /*주문제작여부*/
		                 , G.GOODS_TNM                             /*상품타이틀명*/
		                 , G.MAIN_COLOR_CD                         /*대표색상코드*/
		                 , G.LIST_PRICE                            /*정상가(최초판매가)*/
		                 , CASE WHEN #{frontGb} = 'P' AND #{custGb} = 'G100_20' THEN GBP.STAFF_PC_CURR_PRICE
		                        WHEN #{frontGb} = 'M' AND #{custGb} = 'G100_20' THEN GBP.STAFF_MO_CURR_PRICE
		                        WHEN #{frontGb} = 'A' AND #{custGb} = 'G100_20' THEN GBP.STAFF_APP_CURR_PRICE
		                        WHEN #{frontGb} = 'P' AND #{custGb} != 'G100_20' THEN GBP.PC_CURR_PRICE
		                        WHEN #{frontGb} = 'M' AND #{custGb} != 'G100_20' THEN GBP.MO_CURR_PRICE
		                        WHEN #{frontGb} = 'A' AND #{custGb} != 'G100_20' THEN GBP.APP_CURR_PRICE
		                        ELSE G.CURR_PRICE
		                  END                   AS CURR_PRICE     /*현재판매가*/
		                 , G.MIN_ORD_AMT                           /*최소주문금액*/
		                 , G.FORMAL_GB                             /*정상이월구분*/
		                 , G.REG_DT                                /*등록일시*/
		                 , GS.SELL_WEEK_QTY                        /*주간판매수량*/
		                 , GS.REVIEW_REG_CNT + (SELECT COUNT(1)
		                                        FROM   TB_REVIEW Z
		                                        WHERE  1=1
		                                        AND    Z.GOODS_CD = GS.GOODS_CD
		                                        AND    Z.DISP_YN = 'Y'
		                                        AND    Z.DEL_YN = 'N'
		                                        AND    Z.CONFIRM_YN = 'Y'
		                                        AND    Z.REG_DT BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL + 1 DAY)) AS REVIEW_REG_CNT                      /*리뷰등록건수*/
		            FROM   TB_GOODS G USE INDEX (IX_GOODS_11)
		            INNER JOIN TB_GOODS_STOCK S ON G.GOODS_CD = S.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_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD
		            LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON G.GOODS_CD = GBP.GOODS_CD
		            WHERE  (
		                    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 /*유효한 판매기간*/
		            <if test="ageArr != null and ageArr.length > 0"> <!-- 연령대 -->
		            AND    G.AGE_GRP_CD IN
		                <foreach collection="ageArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		                </foreach>
		            </if>
		            <if test="seasonArr != null and seasonArr.length > 0"> <!-- 시즌 -->
		            AND    G.SEASON_CD IN
		                <foreach collection="seasonArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		                </foreach>
		            </if>
		            AND    S.STOCK_QTY > 0 /*재고있는 상품*/
		            AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
		            AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
		            <if test="brandGroupArr != null and brandGroupArr.length > 0">
		            AND    BG.BRAND_GROUP_NO IN
		                <foreach collection="brandGroupArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		                </foreach>
		            </if>
		            <if test="unisex != null and unisex != ''">
		            AND    G.SEX_GB = #{unisex}
		            </if>
		            <if test='sizeArr != null and sizeArr.length > 0'>
		            AND    EXISTS (SELECT 1
		                           FROM   TB_OPTION
		                           WHERE  GOODS_CD = G.GOODS_CD
		                           AND    OPT_CD2 IN
		                           <foreach collection="sizeArr" item="item" index="index"  open="(" close=")" separator=",">
		                           #{item}
		                           </foreach>
		                           AND    DISP_YN = 'Y'
		                        )
		            </if>
		            <if test="newGoods != null and newGoods != ''">
		            AND    EXISTS (SELECT 1
		                           FROM   TB_GOODS_BENEFIT
		                           WHERE  GOODS_CD = G.GOODS_CD
		                           AND    BENEFIT_GB = #{newGoods}
		                         )
		            </if>
		            <if test='benefitArr != null and benefitArr.length > 0'>
		            AND    EXISTS (SELECT 1
		                           FROM   TB_GOODS_BENEFIT
		                           WHERE  GOODS_CD = G.GOODS_CD
		                           AND    BENEFIT_GB IN
		                           <foreach collection="benefitArr" item="item" index="index"  open="(" close=")" separator=",">
		                           #{item}
		                           </foreach>
		                          )
		            </if>
		           ) G
		    WHERE  1 = 1
		    <if test="priceFrom != null and priceFrom != ''">
		    AND    G.CURR_PRICE <![CDATA[>=]]> #{priceFrom}
		    </if>
		    <if test="priceTo != null and priceTo != ''">
		    AND    G.CURR_PRICE <![CDATA[<=]]> #{priceTo}
		    </if>
		    <if test="dcRateFrom != null and dcRateFrom != ''">
		    AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[>=]]> #{dcRateFrom}
		    </if>
		    <if test="dcRateTo != null and dcRateTo != ''">
		    AND    ((IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) - G.CURR_PRICE) / IF(G.LIST_PRICE = 0,0,G.LIST_PRICE) * 100) <![CDATA[<=]]> #{dcRateTo}
		    </if>
		)
		, TAB_OPTION AS (
		    /* 자사상품 색상 목록 */
		    SELECT O.GOODS_CD
		         , O.OPT_CD1  AS MAIN_COLOR_CD
		    FROM   TAB_GOODS G
		         , TB_OPTION O
		    WHERE  G.GOODS_CD = O.GOODS_CD
		    AND    G.SELF_GOODS_YN = 'Y' /*자사상품만*/
		    <if test="colorArr != null and colorArr.length > 0"> <!-- 색상 -->
		    AND    O.OPT_CD1 IN (SELECT COLOR_CD
		                         FROM   TB_COLOR
		                         WHERE  1 = 1
		                         AND    COLOR_GRP_CD IN
		                         <foreach collection="colorArr" item="item" index="index"  open="(" close=")" separator=",">
		                         #{item}
		                         </foreach>
		                         AND    USE_YN = 'Y' /*사용하는색상*/
		                        )
		    </if>
		    AND    O.DISP_YN = 'Y'
		    GROUP  BY O.GOODS_CD, O.OPT_CD1
		)
		, TAB_ALL_GOODS AS (
		    SELECT BRAND_GROUP_NM     /*브랜드그룹명*/
		         , GOODS_CD           /*상품코드*/
		         , GOODS_NM           /*상품명*/
		         , GOODS_GB           /*상품구분*/
		         , SELF_GOODS_YN      /*자사상품여부*/
		         , FOREIGN_BUY_YN     /*해외구매대행여부*/
		         , PARALLEL_IMPORT_YN /*병행수입여부*/
		         , ORDER_MADE_YN      /*주문제작여부*/
		         , GOODS_TNM          /*상품타이틀명*/
		         , MAIN_COLOR_CD      /*대표색상코드*/
		         , LIST_PRICE         /*정상가(최초판매가)*/
		         , CURR_PRICE         /*현재판매가*/
		         , FORMAL_GB          /*정상이월구분*/
		         , REG_DT             /*등록일시*/
		         , SELL_WEEK_QTY      /*주간판매수량*/
		         , REVIEW_REG_CNT     /*리뷰등록건수*/
		         , NUMB
		    FROM   (
		            SELECT G.BRAND_GROUP_NM                                         /*브랜드그룹명*/
		                 , G.GOODS_CD                                               /*상품코드*/
		                 , G.GOODS_NM                                               /*상품명*/
		                 , G.GOODS_GB                                               /*상품구분*/
		                 , G.SELF_GOODS_YN                                          /*자사상품여부*/
		                 , G.FOREIGN_BUY_YN                                         /*해외구매대행여부*/
		                 , G.PARALLEL_IMPORT_YN                                     /*병행수입여부*/
		                 , G.ORDER_MADE_YN                                          /*주문제작여부*/
		                 , G.GOODS_TNM                                              /*상품타이틀명*/
		                 , IFNULL(O.MAIN_COLOR_CD,G.MAIN_COLOR_CD) AS MAIN_COLOR_CD /*대표색상코드*/
		                 , G.LIST_PRICE                                             /*정상가(최초판매가)*/
		                 , G.CURR_PRICE                                             /*현재판매가*/
		                 , G.FORMAL_GB                                              /*정상이월구분*/
		                 , G.REG_DT                                                 /*등록일시*/
		                 , G.SELL_WEEK_QTY                                          /*주간판매수량*/
		                 , G.REVIEW_REG_CNT                                         /*리뷰등록건수*/
		                 <choose>
		                     <when test="sortingType == 'BEST'"> <!-- 인기상품순 -->
		                 , ROW_NUMBER() OVER(ORDER BY G.SELL_WEEK_QTY DESC, G.GOODS_CD) AS NUMB
		                     </when>
		                     <when test="sortingType == 'REVIEW'"> <!-- 리뷰많은순 -->
		                 , ROW_NUMBER() OVER(ORDER BY G.REVIEW_REG_CNT DESC, G.GOODS_CD) AS NUMB
		                     </when>
		                     <when test="sortingType == 'ROWPRICE'"> <!-- 낮은가격순 -->
		                 , ROW_NUMBER() OVER(ORDER BY G.CURR_PRICE, G.GOODS_CD) AS NUMB
		                     </when>
		                     <otherwise> <!-- 최신상품순 -->
		                 , ROW_NUMBER() OVER(ORDER BY G.REG_DT DESC, G.GOODS_CD) AS NUMB
		                     </otherwise>
		                 </choose>
		            FROM   TAB_GOODS G
		            INNER JOIN TAB_OPTION O ON G.GOODS_CD = O.GOODS_CD
		           ) ORIGINAL
		    WHERE  1 = 1
		    <if test="pageable != null and pageable.endRow != null and pageable.endRow > 0">
		    AND    NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
		    </if>
		)
		, TAB_GOODS_IMG AS (
		    /* 상품의 이미지 */
		    SELECT GOODS_CD
		         , MAX(SYS_IMG_NM)  AS SYS_IMG_NM
		         , MAX(SYS_IMG_NM2) AS SYS_IMG_NM2
		    FROM   (
		            SELECT G.GOODS_CD
		                 , CASE WHEN GI.DEFAULT_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END   AS SYS_IMG_NM
		                 , CASE WHEN GI.MOUSEOVER_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END AS SYS_IMG_NM2
		            FROM   TAB_ALL_GOODS G
		                 , TB_GOODS_IMG GI
		            WHERE  G.GOODS_CD = GI.GOODS_CD
		            AND    G.MAIN_COLOR_CD = GI.COLOR_CD
		            AND    GI.EXTMALL_IMG_YN = 'N'
		           ) Z
		    GROUP  BY GOODS_CD
		)
		SELECT G.BRAND_GROUP_NM
		     , G.GOODS_CD
		     , FN_GET_GOODS_NM(G.GOODS_NM,G.GOODS_GB,G.FOREIGN_BUY_YN,G.PARALLEL_IMPORT_YN,G.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		     , G.GOODS_TNM
		     , G.MAIN_COLOR_CD
		     , G.LIST_PRICE
		     , G.CURR_PRICE
		     , GI.SYS_IMG_NM
		     , GI.SYS_IMG_NM2
		     , (
		        SELECT GROUP_CONCAT(CONCAT(VIDEO_GB,':',KMC_KEY) ORDER BY NUMB SEPARATOR ',')
		        FROM   (
		                SELECT GV.VIDEO_GB
		                     , GV.KMC_KEY
		                     , GV.REG_DT
		                     , RANK() OVER(ORDER BY GV.REG_DT, GV.KMC_KEY) AS NUMB
		                FROM   TB_GOODS_VIDEO GV
		                WHERE  GV.GOODS_CD = G.GOODS_CD
		                AND    GV.DISP_YN = 'Y'
		                AND    GV.KMC_KEY IS NOT NULL
		               ) Z
		        WHERE  NUMB <![CDATA[<=]]> 2
		       ) AS VIDEOS
		     , (
		        SELECT GROUP_CONCAT(DISTINCT CONCAT(C.COLOR_CD,':',CC.CD_DESC) ORDER BY CC.DISP_ORD SEPARATOR ',') AS COLOR_CHIPS
		        FROM   TB_OPTION O
		             , TB_COLOR C
		             , TB_COMMON_CODE CC
		        WHERE  O.OPT_CD1 = C.COLOR_CD
		        AND    C.COLOR_GRP_CD = CC.CD
		        AND    O.GOODS_CD = G.GOODS_CD
		        AND    O.DISP_YN = 'Y'
		        AND    C.USE_YN = 'Y'
		        AND    CC.USE_YN = 'Y'
		       ) AS COLOR_CHIPS /*컬러칩*/
		     , (
		        SELECT GROUP_CONCAT(DISTINCT CONCAT(OPT_CD2 ,':' ,CASE WHEN SOLDOUT_YN = 'Y' THEN 'Y'
		                                                               ELSE CASE WHEN CURR_STOCK_QTY - BASE_STOCK_QTY > 0 THEN 'N' ELSE 'Y' END
		                                                          END) ORDER BY DISP_ORD SEPARATOR ',') AS SIZES
		        FROM   VW_STOCK
		        WHERE  GOODS_CD = G.GOODS_CD
		        AND    OPT_CD1 = G.MAIN_COLOR_CD
		        AND    DISP_YN = 'Y'
		       ) AS SIZES /*사이즈*/
		     , (
		        SELECT GROUP_CONCAT(CONCAT(BENEFIT_GB,':',BENEFIT_NM) ORDER BY NUMB SEPARATOR ',')
		        FROM   (
		                SELECT 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(ORDER BY GB.BENEFIT_GB, GB.GOODS_CD) AS NUMB
		                FROM   TB_GOODS_BENEFIT GB
		                WHERE  GB.GOODS_CD = G.GOODS_CD
		               ) Z
		       ) AS BENEFITS
		     , G.FORMAL_GB
		     , G.REG_DT
		     , G.NUMB
		     <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_ALL_GOODS G
		LEFT OUTER JOIN TAB_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD
		<if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
		                             AND W.CUST_NO = #{custNo}
		</if>
	</select>

	<!-- 브랜드메인 브랜드별 신상품 50개 목록 -->
	<select id="getBrandNewGoodsList" parameterType="goodsSearch" resultType="Goods">
		/* TsfGoods.getBrandNewGoodsList */
		WITH TAB_GOODS AS (
		       SELECT DISTINCT G.BRAND_GROUP_NM /*브랜드그룹명*/
		            , G.GOODS_CD /*상품코드*/
		            , G.GOODS_NM /*상품명*/
		            , G.GOODS_GB /*상품구분*/
		            , G.GOODS_TYPE
		            , G.SELF_GOODS_YN /*자사상품여부*/
		            , 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.FORMAL_GB /*정상이월구분*/
		            , G.REG_DT /*등록일시*/
		            , G.SELL_WEEK_QTY /*주간판매수량*/
		            , G.REVIEW_REG_CNT /*리뷰등록건수*/
		       FROM (
		            SELECT 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.GOODS_TYPE
		                 , G.SELF_GOODS_YN /*자사상품여부*/
		                 , G.FOREIGN_BUY_YN /*해외구매대행여부*/
		                 , G.PARALLEL_IMPORT_YN /*병행수입여부*/
		                 , G.ORDER_MADE_YN /*주문제작여부*/
		                 , G.GOODS_TNM /*상품타이틀명*/
		                 , G.MAIN_COLOR_CD /*대표색상코드*/
		                 , G.LIST_PRICE /*정상가(최초판매가)*/
		                 , CASE WHEN #{frontGb} = 'P' AND #{custGb} = 'G100_20' THEN IFNULL(GBP.STAFF_PC_CURR_PRICE,G.CURR_PRICE)
		                        WHEN #{frontGb} = 'M' AND #{custGb} = 'G100_20' THEN IFNULL(GBP.STAFF_MO_CURR_PRICE,G.CURR_PRICE)
		                        WHEN #{frontGb} = 'A' AND #{custGb} = 'G100_20' THEN IFNULL(GBP.STAFF_APP_CURR_PRICE,G.CURR_PRICE)
		                        WHEN #{frontGb} = 'P' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE)
		                        WHEN #{frontGb} = 'M' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.MO_CURR_PRICE,G.CURR_PRICE)
		                        WHEN #{frontGb} = 'A' AND #{custGb} != 'G100_20' THEN IFNULL(GBP.APP_CURR_PRICE,G.CURR_PRICE)
		                    ELSE G.CURR_PRICE
		                    END AS CURR_PRICE /*현재판매가*/
		                 , G.MIN_ORD_AMT /*최소주문금액*/
		                 , G.FORMAL_GB /*정상이월구분*/
		                 , G.REG_DT /*등록일시*/
		                 , GS.SELL_WEEK_QTY /*주간판매수량*/
		                 , GS.REVIEW_REG_CNT + (SELECT COUNT(1)
		                                        FROM   TB_REVIEW Z
		                                        WHERE  1=1
		                                        AND    Z.GOODS_CD = GS.GOODS_CD
		                                        AND    Z.DISP_YN = 'Y'
		                                        AND    Z.DEL_YN = 'N'
		                                        AND    Z.CONFIRM_YN = 'Y'
		                                        AND    Z.REG_DT BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL + 1 DAY)) AS REVIEW_REG_CNT/*리뷰등록건수*/
		            FROM TB_CATE_4SRCH C4
		            INNER JOIN TB_CATE_GOODS CG ON C4.LEAF_CATE_NO = CG.CATE_NO
		            INNER JOIN TB_GOODS G ON CG.GOODS_CD = G.GOODS_CD
		            INNER JOIN TB_GOODS_STOCK S ON CG.GOODS_CD = S.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_SUMMARY GS ON CG.GOODS_CD = GS.GOODS_CD
		            LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON CG.GOODS_CD = GBP.GOODS_CD
		            WHERE C4.SITE_CD = #{siteCd}
		            AND C4.CATE_GB = #{cateGb}
		            AND C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		        <if test="cate1No != null and cate1No != ''">
		            AND C4.CATE1_NO = #{cate1No}
		        </if>
		        <if test="cate2No != null and cate2No != ''">
		            AND C4.CATE2_NO = #{cate2No}
		        </if>
		        <if test="cate3No != null and cate3No != ''">
		            AND C4.CATE3_NO = #{cate3No}
		        </if>
		        <if test="cate4No != null and cate4No != ''">
		            AND C4.CATE4_NO = #{cate4No}
		        </if>
		        <if test="cate5No != null and cate5No != ''">
		            AND C4.CATE5_NO = #{cate5No}
		        </if>
		    <choose>
		        <when test="brandGroupNo != null and brandGroupNo > 0">
		            AND B.BRAND_GROUP_NO = #{brandGroupNo}
		        </when>
		        <otherwise>
		            AND CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
			    </otherwise>
		    </choose>
		        <if test="formalGb != null and formalGb !=''">
		            AND G.FORMAL_GB = #{formalGb}
		        </if>
		            AND G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		            AND G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		            AND NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*유효한 판매기간*/
		            AND S.STOCK_QTY > 0 /*재고있는 상품*/
		            AND B.USE_YN = 'Y' /*사용하는 브랜드*/
		            AND BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
		            AND EXISTS (SELECT 1
		            FROM TB_GOODS_BENEFIT
		            WHERE GOODS_CD = G.GOODS_CD
		            AND BENEFIT_GB = '40'
		        )
		<if test="maxRow != null and maxRow > 0">
		    LIMIT #{maxRow}
		</if>
		    ) G
		    WHERE 1 = 1
		)
		, TAB_OPTION AS (
		      /* 자사상품 색상 목록 */
		      SELECT O.GOODS_CD
		           , O.OPT_CD1 AS MAIN_COLOR_CD
		      FROM TAB_GOODS G
		      , TB_OPTION O
		      WHERE G.GOODS_CD = O.GOODS_CD
		      AND G.SELF_GOODS_YN = 'Y' /*자사상품만*/
		      AND O.DISP_YN = 'Y'
		      GROUP BY O.GOODS_CD, O.OPT_CD1
		)
		, TAB_ALL_GOODS AS (
		     SELECT BRAND_GROUP_NM /*브랜드그룹명*/
		          , GOODS_CD /*상품코드*/
		          , GOODS_NM /*상품명*/
		          , GOODS_GB /*상품구분*/
		          , GOODS_TYPE
		          , SELF_GOODS_YN /*자사상품여부*/
		          , FOREIGN_BUY_YN /*해외구매대행여부*/
		          , PARALLEL_IMPORT_YN /*병행수입여부*/
		          , ORDER_MADE_YN /*주문제작여부*/
		          , GOODS_TNM /*상품타이틀명*/
		          , MAIN_COLOR_CD /*대표색상코드*/
		          , LIST_PRICE /*정상가(최초판매가)*/
		          , CURR_PRICE /*현재판매가*/
		          , FORMAL_GB /*정상이월구분*/
		          , REG_DT /*등록일시*/
		          , SELL_WEEK_QTY /*주간판매수량*/
		          , REVIEW_REG_CNT /*리뷰등록건수*/
		          , NUMB
		     FROM (
		                SELECT G.BRAND_GROUP_NM /*브랜드그룹명*/
		                , G.GOODS_CD /*상품코드*/
		                , G.GOODS_NM /*상품명*/
		                , G.GOODS_GB /*상품구분*/
		                , G.GOODS_TYPE
		                , G.SELF_GOODS_YN /*자사상품여부*/
		                , G.FOREIGN_BUY_YN /*해외구매대행여부*/
		                , G.PARALLEL_IMPORT_YN /*병행수입여부*/
		                , G.ORDER_MADE_YN /*주문제작여부*/
		                , G.GOODS_TNM /*상품타이틀명*/
		                , IFNULL(O.MAIN_COLOR_CD,G.MAIN_COLOR_CD) AS MAIN_COLOR_CD /*대표색상코드*/
		                , G.LIST_PRICE /*정상가(최초판매가)*/
		                , G.CURR_PRICE /*현재판매가*/
		                , G.FORMAL_GB /*정상이월구분*/
		                , G.REG_DT /*등록일시*/
		                , G.SELL_WEEK_QTY /*주간판매수량*/
		                , G.REVIEW_REG_CNT /*리뷰등록건수*/
		                , ROW_NUMBER() OVER(ORDER BY G.REG_DT DESC, G.GOODS_CD) AS NUMB
		                FROM TAB_GOODS G
		                LEFT OUTER JOIN TAB_OPTION O ON G.GOODS_CD = O.GOODS_CD
		                WHERE 1=1
		         ) ORIGINAL
		     WHERE 1 = 1
		    <if test="maxRow != null and maxRow > 0">
		     LIMIT #{maxRow}
		    </if>
		)
		SELECT G.BRAND_GROUP_NM
		     , G.GOODS_CD
		     , FN_GET_GOODS_NM(G.GOODS_NM,G.GOODS_GB,G.FOREIGN_BUY_YN,G.PARALLEL_IMPORT_YN,G.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		     , G.GOODS_TNM
		     , G.GOODS_TYPE
		     , G.MAIN_COLOR_CD
		     , G.LIST_PRICE
		     , G.CURR_PRICE
		     , (SELECT SYS_IMG_NM FROM TB_GOODS_IMG X WHERE X.GOODS_CD = G.GOODS_CD AND IF(G.SELF_GOODS_YN= 'N', 'XX', G.MAIN_COLOR_CD) = X.COLOR_CD AND DEFAULT_IMG_YN = 'Y' LIMIT 1) AS SYS_IMG_NM
		     , (SELECT SYS_IMG_NM FROM TB_GOODS_IMG X WHERE X.GOODS_CD = G.GOODS_CD AND IF(G.SELF_GOODS_YN= 'N', 'XX', G.MAIN_COLOR_CD) = X.COLOR_CD AND MOUSEOVER_IMG_YN = 'Y' LIMIT 1) AS SYS_IMG_NM2
		, (
		    SELECT GROUP_CONCAT(CONCAT(VIDEO_GB,':',KMC_KEY) ORDER BY NUMB SEPARATOR ',')
		    FROM (
		        SELECT GV.VIDEO_GB
		             , GV.KMC_KEY
		             , GV.REG_DT
		             , RANK() OVER(ORDER BY GV.REG_DT, GV.KMC_KEY) AS NUMB
		        FROM TB_GOODS_VIDEO GV
		        WHERE GV.GOODS_CD = G.GOODS_CD
		        AND GV.DISP_YN = 'Y'
		        AND GV.KMC_KEY IS NOT NULL
		    ) Z
		    WHERE NUMB <![CDATA[<=]]> 2
		) AS VIDEOS
		, (
		    SELECT GROUP_CONCAT(DISTINCT CONCAT(C.COLOR_CD,':',CC.CD_DESC) ORDER BY CC.DISP_ORD SEPARATOR ',') AS COLOR_CHIPS
		    FROM TB_OPTION O
		       , TB_COLOR C
		       , TB_COMMON_CODE CC
		    WHERE O.OPT_CD1 = C.COLOR_CD
		    AND C.COLOR_GRP_CD = CC.CD
		    AND O.GOODS_CD = G.GOODS_CD
		    AND O.DISP_YN = 'Y'
		    AND C.USE_YN = 'Y'
		    AND CC.USE_YN = 'Y'
		) AS COLOR_CHIPS /*컬러칩*/
		, (
		   SELECT GROUP_CONCAT(DISTINCT CONCAT(OPT_CD2 ,':' ,CASE WHEN SOLDOUT_YN = 'Y' THEN 'Y'
		          ELSE CASE WHEN CURR_STOCK_QTY - BASE_STOCK_QTY > 0 THEN 'N' ELSE 'Y' END
		          END) ORDER BY DISP_ORD SEPARATOR ',') AS SIZES
		   FROM VW_STOCK
		   WHERE GOODS_CD = G.GOODS_CD
		   AND OPT_CD1 = G.MAIN_COLOR_CD
		   AND DISP_YN = 'Y'
		) AS SIZES /*사이즈*/
		, (
		   SELECT GROUP_CONCAT(CONCAT(BENEFIT_GB,':',BENEFIT_NM) ORDER BY NUMB SEPARATOR ',')
		   FROM (
		       SELECT 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(ORDER BY GB.BENEFIT_GB, GB.GOODS_CD) AS NUMB
		       FROM TB_GOODS_BENEFIT GB
		       WHERE GB.GOODS_CD = G.GOODS_CD
		   ) Z
		) AS BENEFITS
		, G.FORMAL_GB
		, G.REG_DT
		, G.NUMB
		<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_ALL_GOODS G
		<if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		    LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
		    AND W.CUST_NO = #{custNo}
		</if>
	</select>

	<!-- 상품 등급 리스트 조회 -->
	<select id="getGoodsDetailOrderGradeList" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getGoodsDetailOrderGradeList */
		SELECT  G.GOODS_CD
			 , A.CUST_GRADE
			 , FN_GET_CODE_NM('G101', A.CUST_GRADE) AS CUST_GRADE_NAME
		FROM TB_GOODS_ORDER_GRADE A
				 INNER JOIN TB_GOODS G ON A.GOODS_CD = G.GOODS_CD
		WHERE A.GOODS_CD = #{goodsCd}
		ORDER BY G.GOODS_CD, A.CUST_GRADE
	</select>

	<!-- 구성 상품 목록 -->
	<select id="getCompsGoodsList" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getSimpleCompsGoodsList */
		SELECT ROW_NUMBER() OVER() AS RNUM
		     , A.GOODS_CD
		     , A.COMPS_GOODS_CD
		     , A.GOODS_NM
		     , A.OPT_CD
		     , A.OPT_CD2
		     , A.ITEM_PRICES
		     , A.ITEM_STOCK_QTY
		     , A.COLOR_NM
		FROM (SELECT GC.GOODS_CD
		           , GC.COMPS_GOODS_CD
		           , G.GOODS_NM
		           , GROUP_CONCAT(VS.OPT_CD ORDER BY VS.OPT_CD ASC) AS OPT_CD
		           , GROUP_CONCAT(VS.OPT_CD2 ORDER BY VS.OPT_CD ASC) AS OPT_CD2
		           , GROUP_CONCAT(VS.ADD_PRICE ORDER BY VS.OPT_CD ASC) AS ITEM_PRICES
		           , GROUP_CONCAT(TRUNCATE(VS.CURR_STOCK_QTY / GC.QTY, 0) ORDER BY VS.OPT_CD ASC) AS ITEM_STOCK_QTY
		           , GROUP_CONCAT(IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(VS.OPT_CD1,'') , VS.OPT_CD1 ) AND USE_YN  = 'Y'), VS.OPT_CD1) ORDER BY VS.OPT_CD ASC) AS COLOR_NM
		      FROM   TB_GOODS_COMPOSE GC
		      INNER  JOIN VW_STOCK VS
		      ON     GC.COMPS_GOODS_CD = VS.GOODS_CD
		      INNER  JOIN TB_GOODS G
		      ON     GC.COMPS_GOODS_CD = G.GOODS_CD
		      WHERE  GC.GOODS_CD = #{goodsCd}
		      GROUP  BY GC.GOODS_CD
		              , G.GOODS_NM
		              , GC.COMPS_GOODS_CD
		      ORDER  BY GC.COMPS_GOODS_CD) A		<!-- 변경x > 네이버페이 KEY로 송부되는 순서 -->
	</select>
	
	<!-- 상품 예약판매여부 확인 -->
	<select id="getGoodsResSellCount" parameterType="String" resultType="int">
		/* TsfGoods.getGoodsResSellCount */
		SELECT  COUNT(*)
		FROM TB_GOODS_RES_SELL 
		WHERE GOODS_CD = #{goodsCd} 
		AND USE_YN = 'Y'
		AND DELV_RES_DT > NOW()
	</select>

	<!-- 카테고리메인 신규상품 목록 -->
	<select id="getCategoryMainNewGoodsList"  parameterType="Cate4Srch" resultType="Goods">
		/* TsfGoods.getCategoryMainNewGoodsList */
		WITH TAB_GOODS AS (
		     SELECT G.BRAND_GROUP_NM     /*브랜드그룹명*/
		          , G.GOODS_CD           /*상품코드*/
		          , G.GOODS_NM           /*상품명*/
		          , G.GOODS_GB           /*상품구분*/
		          , G.SELF_GOODS_YN      /*자사상품여부*/
		          , 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.REG_DT             /*등록일시*/
		    FROM   (
		        SELECT DISTINCT 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.SELF_GOODS_YN      /*자사상품여부*/
		             , 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.REG_DT                                                 /*등록일시*/
		        FROM   TB_CATE_4SRCH C4
		        , TB_CATE_GOODS CG
		        , TB_GOODS G USE INDEX (IX_GOODS_12)
		        , TB_GOODS_STOCK GS
		        , TB_BRAND B
		        , TB_BRAND_GROUP BG
		        WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
		        AND    CG.GOODS_CD = G.GOODS_CD
		        AND    CG.GOODS_CD = GS.GOODS_CD
		        AND    G.BRAND_CD = B.BRAND_CD
		        AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		        AND    C4.SITE_CD = #{siteCd}
		        AND    C4.CATE_GB = #{cateGb}
		        AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		    <if test="cate1No != null and cate1No != ''">
		        AND    C4.CATE1_NO = #{cate1No}
		    </if>
		    <choose>
		        <when test="brandGroupNo != null and brandGroupNo != ''">
		        AND    B.BRAND_GROUP_NO = #{brandGroupNo}
		        </when>
		        <otherwise>
		        AND    CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
		        </otherwise>
		    </choose>
		    <if test="exceptGoodsArr != null and exceptGoodsArr.length > 0">
		        AND	   CG.GOODS_CD NOT IN
		        <foreach collection="exceptGoodsArr" item="item" index="index"  open="(" close=")" separator=",">
		            #{item}
		        </foreach>
		    </if>
		    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		    AND    G.FORMAL_GB = 'G009_10' /*정상상품*/
		    AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
		    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT   /*판매기간*/
		    AND    G.REG_DT BETWEEN DATE_ADD(NOW(), INTERVAL -14 DAY) AND NOW()
		    AND    GS.STOCK_QTY <![CDATA[>]]> 0 /*재고있는상품*/
		    AND    B.USE_YN = 'Y'
		    AND    BG.USE_YN = 'Y'
		    <if test="maxRow != null and maxRow !=''">
		    	LIMIT #{maxRow}
		    </if>
		    ) G
		    WHERE  1=1
		    ORDER BY G.REG_DT DESC, G.GOODS_CD
		)
		, TAB_NEW AS (
		    SELECT G.BRAND_GROUP_NM     /*브랜드그룹명*/
		         , G.GOODS_CD           /*상품코드*/
		         , G.GOODS_NM           /*상품명*/
		         , G.GOODS_GB           /*상품구분*/
		         , G.SELF_GOODS_YN      /*자사상품여부*/
		         , 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.REG_DT             /*등록일시*/
		    FROM   (
		        SELECT DISTINCT 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.SELF_GOODS_YN      /*자사상품여부*/
		             , 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.REG_DT                                                 /*등록일시*/
		        FROM   TB_CATE_4SRCH C4
		        , TB_CATE_GOODS CG
		        , TB_GOODS G
		        , TB_GOODS_STOCK GS
		        , TB_BRAND B
		        , TB_BRAND_GROUP BG
		        WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
		        AND    CG.GOODS_CD = G.GOODS_CD
		        AND    CG.GOODS_CD = GS.GOODS_CD
		        AND    G.BRAND_CD = B.BRAND_CD
		        AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		        AND    C4.SITE_CD = #{siteCd}
		        AND    C4.CATE_GB = #{cateGb}
		        AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		        <if test="cate1No != null and cate1No != ''">
		        AND    C4.CATE1_NO = #{cate1No}
		        </if>
		        <choose>
		            <when test="brandGroupNo != null and brandGroupNo != ''">
		                AND    B.BRAND_GROUP_NO = #{brandGroupNo}
		            </when>
		            <otherwise>
		                AND    CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
		            </otherwise>
		        </choose>
		        <if test="exceptGoodsArr != null and exceptGoodsArr.length > 0">
		            AND   CG.GOODS_CD NOT IN
		            <foreach collection="exceptGoodsArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		            </foreach>
		        </if>
		        AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		        AND    G.FORMAL_GB = 'G009_10' /*정상상품*/
		        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'
		        AND NOT EXISTS (
		                      SELECT 1
		                      FROM TAB_GOODS TG
		                      WHERE 1=1
		                      AND TG.GOODS_CD = G.GOODS_CD
		                )
		        <if test="maxRow != null and maxRow !=''">
		        LIMIT #{maxRow}
		        </if>
		    ) G
		    WHERE  1=1
		    ORDER BY G.REG_DT DESC, G.GOODS_CD
		)
		SELECT G.BRAND_GROUP_NM
		     , G.GOODS_CD
		     , FN_GET_GOODS_NM(G.GOODS_NM,G.GOODS_GB,G.FOREIGN_BUY_YN,G.PARALLEL_IMPORT_YN,G.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		     , G.GOODS_TNM
		     , G.MAIN_COLOR_CD
		     , G.LIST_PRICE
		     , FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD,G.CURR_PRICE,#{custGb})                           AS CURR_PRICE    /*현재판매가*/
		     , (SELECT SYS_IMG_NM FROM TB_GOODS_IMG X WHERE X.GOODS_CD = G.GOODS_CD AND IF(G.SELF_GOODS_YN= 'N', 'XX', G.MAIN_COLOR_CD) = X.COLOR_CD AND DEFAULT_IMG_YN = 'Y' LIMIT 1) AS SYS_IMG_NM
		     , (SELECT SYS_IMG_NM FROM TB_GOODS_IMG X WHERE X.GOODS_CD = G.GOODS_CD AND IF(G.SELF_GOODS_YN= 'N', 'XX', G.MAIN_COLOR_CD) = X.COLOR_CD AND MOUSEOVER_IMG_YN = 'Y' LIMIT 1) AS SYS_IMG_NM2
		     , (
		     SELECT GROUP_CONCAT(CONCAT(VIDEO_GB,':',KMC_KEY) ORDER BY NUMB SEPARATOR ',')
		     FROM   (
		     SELECT GV.VIDEO_GB
		     , GV.KMC_KEY
		     , GV.REG_DT
		     , RANK() OVER(ORDER BY GV.REG_DT, GV.KMC_KEY) AS NUMB
		     FROM   TB_GOODS_VIDEO GV
		     WHERE  GV.GOODS_CD = G.GOODS_CD
		     AND    GV.DISP_YN = 'Y'
		     AND    GV.KMC_KEY IS NOT NULL
		     ) Z
		     WHERE  NUMB <![CDATA[<=]]> 2
		     ) AS VIDEOS
		     <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 (
		    SELECT *
		      FROM TAB_GOODS
		    UNION ALL
		    SELECT *
		      FROM TAB_NEW
		) G
		<if test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
			LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
			AND W.CUST_NO = #{custNo}
		</if>
		WHERE 1=1
		<if test="maxRow != null and maxRow !=''">
			LIMIT #{maxRow}
		</if>
	</select>

	<!-- 신규고객 구매 가능 상품 여부 정보 조회 -->
	<select id="getNewCustOrdCanYnInfo" parameterType="Goods" resultType="Goods">
		/* TsfGoods.getNewCustOrdCanYnInfo : 신규고객 구매 가능 상품 여부 정보 조회 */
		SELECT G.GOODS_CD
		     , G.NEW_CUST_ORD_YN
		     , CASE WHEN G.NEW_CUST_ORD_YN = 'Y' AND (SELECT JOIN_DT FROM TB_CUSTOMER WHERE CUST_NO = #{custNo}) BETWEEN G.NEW_CUST_ORD_STDT AND G.NEW_CUST_ORD_EDDT THEN 'Y'
		            WHEN G.NEW_CUST_ORD_YN = 'N' THEN 'Y'
		            ELSE 'N' END AS NEW_CUST_CAN_YN
		FROM   TB_GOODS G
		WHERE  G.GOODS_CD = #{goodsCd}
	</select>
	
	<!-- 상품추가정보 목록 -->
	<select id="getGoodsAddInfoList" parameterType="Goods" resultType="Item">
		/* TsfGoods.getGoodsAddInfoList */
		SELECT SUM(X.COUNT)                                                             AS COUNT
		     , X.ITEM_ID
		     , X.IMAGE_URL
		     , X.ITEM_NAME
		     , X.ITEM_URL
		     , MAX(X.CATEGORY_ID)                                                       AS CATEGORY_ID
		     , X.ORIGINAL_PRICE
		     , X.SALE_PRICE
		     , X.BRAND_ID
		     , X.BRAND_NAME
		     , X.WISH_CNT
		FROM (SELECT Z.COUNT                                                                              /*view건수*/
		           , Z.ITEM_ID                                                                            /*상품코드*/
		           , Z.IMAGE_URL
		           , Z.ITEM_NAME
		           , Z.ITEM_URL
		           , CASE Z.CATEGORY_ID WHEN 'null' THEN 0
		                                ELSE Z.CATEGORY_ID
		              END                                                               AS CATEGORY_ID
		           , G.LIST_PRICE                                                       AS ORIGINAL_PRICE /*정상가*/
		           , FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD,G.CURR_PRICE,#{custGb}) AS SALE_PRICE     /*현재판매가*/
		           , BG.BRAND_GROUP_NO                                                  AS BRAND_ID       /*브랜드ID(브랜드그룹번호)*/
		           , CASE WHEN BG.DISP_NM_LANG = 'EN' THEN BG.BRAND_GROUP_ENM
		                  ELSE BG.BRAND_GROUP_KNM END                                   AS BRAND_NAME     /*브랜드명(브랜드그룹명)*/
		           ,
		     <choose>
		         <when test="custNo != null and custNo > 0"> <!-- 로그인 했으면 -->
		             IF(W.GOODS_CD IS NULL,0,1)                                         AS WISH_CNT       /*위시리스트담긴상품*/
		         </when>
		         <otherwise>
		             0                                                                  AS WISH_CNT       /*위시리스트담긴상품*/
		         </otherwise>
		     </choose>
		      FROM   (
		              ${sql}
		             ) Z
		      INNER JOIN TB_GOODS G ON Z.ITEM_ID = 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
		      LEFT OUTER JOIN TB_WISHLIST W ON Z.ITEM_ID = W.GOODS_CD
		                                   AND IFNULL(#{custNo}, 0) = W.CUST_NO
		      ORDER  BY Z.RK) X
		WHERE IMAGE_URL IS NOT NULL AND IMAGE_URL != 'null'
		GROUP BY X.ITEM_ID
		       , X.IMAGE_URL
		       , X.ITEM_NAME
		       , X.ITEM_URL
		       , X.ORIGINAL_PRICE
		       , X.SALE_PRICE
		       , X.BRAND_ID
		       , X.BRAND_NAME
		       , X.WISH_CNT
	</select>
	
	<!-- 상세용 컨텐츠 목록  -->
	<select id="getGoodsContentsList"  parameterType="GoodsContents" resultType="GoodsContents">
		/* TsfGoods.getGoodsContentsList */
		SELECT A.GOODS_CONTENTS_SQ
		     , A.GOODS_CONTENTS_TYPE
		     , A.GOODS_CONTENTS_TITLE
		     , A.GOODS_CONTENTS_VIDEO_GUBUN
		     , A.KMC_KEY
		     , A.KUF_KEY
		     , IMG_PATH
		     , A.USE_YN
		FROM   TB_GOODS_CONTENTS A
		INNER JOIN TB_GOODS_CONTENTS_DATA B ON A.GOODS_CONTENTS_SQ = B.GOODS_CONTENTS_SQ
		                             AND B.GOODS_CD = #{goodsCd}
		WHERE  A.GOODS_CONTENTS_TYPE = #{goodsContentsType}
		AND CURRENT_DATE() BETWEEN A.GOODS_CONTENTS_STDT AND A.GOODS_CONTENTS_EDDT
		AND A.USE_YN  = 'Y'
		<if test="goodsContentsType != null and goodsContentsType == 'G086_1'">
		AND NULLIF(A.KMC_KEY,'') IS NOT NULL
		</if>
		ORDER  BY A.GOODS_CONTENTS_SQ DESC
		LIMIT 1
	</select>
	
	<!-- 상품 다른 색상의 productno 찾기 (자신과 동일한 productNo인 것들은 제외)  -->
	<select id="getProductNoByGoodsColor"  parameterType="Goods" resultType="GoodsStock">
		/* TsfGoods.getProductNoByGoodsColor */
		SELECT DISTINCT PRODUCT_NO
		FROM   TB_OPTION A
		WHERE GOODS_CD = #{goodsCd}
		AND   OPT_CD1 <![CDATA[<>]]>  #{colorCd}
		AND   PRODUCT_NO IS NOT NULL
		AND   PRODUCT_NO NOT IN (SELECT PRODUCT_NO FROM TB_OPTION S WHERE S.GOODS_CD = #{goodsCd} AND S.OPT_CD1 = #{colorCd} )
	</select>
		
</mapper>
