style24.doc/산출물/4.구축/데이터처리/상품리스트_쿼리문.sql

-- 브랜드 *
-- 사이즈 *
-- 가격 *
-- 할인율 *
-- 연령 *
-- 시즌 *
-- 컬러 *
-- 혜택 

-- 필터-브랜드 (getFilterBrandList)
SELECT BG.BRAND_GROUP_NO                   /*브랜드그룹번호*/
     , CASE WHEN BG.DISP_NM_LANG = 'EN' THEN
                BG.BRAND_GROUP_ENM
            ELSE
                BG.BRAND_GROUP_KNM
       END               AS BRAND_GROUP_NM /*브랜드그룹명*/
FROM   TB_BRAND_GROUP BG
WHERE  BRAND_GROUP_NO IN (SELECT B.BRAND_GROUP_NO
                          FROM   TB_CATE_4SRCH C4
                               , TB_CATE_GOODS CG
                               , TB_GOODS G
                               , TB_GOODS_STOCK GS
                               , TB_BRAND B
                               , TB_SITE_BRAND SB
                          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    G.GOODS_CD = GS.GOODS_CD
                          AND    G.BRAND_CD = B.BRAND_CD
                          AND    B.BRAND_CD = SB.BRAND_CD
                          AND    C4.SITE_CD = #{siteCd}
                          AND    C4.CATE_GB = #{cateGb}
                          AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
                          AND    C4.CATE1_NO = #{cate1No}
                          <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>
                          AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
                          AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
                          AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT   /*판매기간*/
                          AND    GS.STOCK_QTY <![CDATA[>]]> 0 /*재고있는상품*/
                          AND    SB.SITE_CD = #{siteCd}
                          AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
                          AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
                         )
WHERE  BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
ORDER  BY 2
;

-- 필터 - 사이즈 (getFilterSizeList)
SELECT DISTINCT
       I.SIZE_GB /*사이즈구분(T:상의, B:하의, S:신발)*/
     , S.OPT_CD2 /*옵션코드2*/
FROM   TB_CATE_4SRCH C4
     , TB_CATE_GOODS CG
     , TB_GOODS G
     , TB_ITEMKIND I
     , VW_STOCK S
     , TB_BRAND B
     , TB_SITE_BRAND SB
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    G.ITEMKIND_CD = I.ITEMKIND_CD
AND    G.GOODS_CD = S.GOODS_CD
AND    G.BRAND_CD = B.BRAND_CD
AND    B.BRAND_CD = SB.BRAND_CD
AND    C4.SITE_CD = #{siteCd}
AND    C4.CATE_GB = #{cateGb}
AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
AND    C4.CATE1_NO = #{cate1No}
<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>
AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT   /*판매기간*/
AND    I.SIZE_GB IS NOT NULL
AND    S.CURR_STOCK_QTY - S.BASE_STOCK_QTY <![CDATA[>]]> 0 /*재고있는옵션*/
AND    S.SOLDOUT_YN = 'N' /*품절이 아닌 옵션*/
AND    S.DISP_YN = 'Y' /*노출하는 옵션*/
AND    LENGTH(S.OPT_CD2) > 0
AND    SB.SITE_CD = #{siteCd}
AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
ORDER  BY CASE WHEN I.SIZE_GB = 'T' THEN 1
               WHEN I.SIZE_GB = 'B' THEN 2
               WHEN I.SIZE_GB = 'S' THEN 3
               ELSE 4
          END, S.OPT_CD2
;

-- 필터-가격 (getFilterPriceList)
SELECT FLOOR(MIN_CURR_PRICE / 1000) * 1000                   AS PRICE1
     , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 1) / 1000) * 1000 AS PRICE2
     , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 2) / 1000) * 1000 AS PRICE3
     , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 3) / 1000) * 1000 AS PRICE4
     , FLOOR((MIN_CURR_PRICE + SLICE_VAL * 4) / 1000) * 1000 AS PRICE5
     , FLOOR(MAX_CURR_PRICE / 1000) * 1000                   AS PRICE6
FROM   (
        SELECT MIN(CURR_PRICE) AS MIN_CURR_PRICE                    /*최소현재판매가*/
             , MAX(CURR_PRICE) AS MAX_CURR_PRICE                    /*최대현재판매가*/
             , (MAX(CURR_PRICE) - MIN(CURR_PRICE)) / 5 AS SLICE_VAL /*분할값*/
        FROM   (
                SELECT FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD,G.CURR_PRICE,#{custGb}) AS CURR_PRICE /*현재판매가*/
                FROM   TB_CATE_4SRCH C4
                     , TB_CATE_GOODS CG
                     , TB_GOODS G
                     , TB_GOODS_STOCK GS
                     , TB_BRAND B
                     , TB_SITE_BRAND SB
                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 = GS.GOODS_CD
                AND    G.BRAND_CD = B.BRAND_CD
                AND    B.BRAND_CD = SB.BRAND_CD
                AND    C4.SITE_CD = #{siteCd}
                AND    C4.CATE_GB = #{cateGb}
                AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
                AND    C4.CATE1_NO = #{cate1No}
                <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>
                AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
                AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
                AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT   /*판매기간*/
                AND    GS.STOCK_QTY <![CDATA[>]]> 0 /*재고있는상품*/
                AND    SB.SITE_CD = #{siteCd}
                AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
                AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
               ) Z
       ) Z
;

-- 필터-연령대 (getFilterAgesList)
SELECT CD    AS AGES_CD /*연령대코드*/
     , CD_NM AS AGES_NM /*연령대명*/
FROM   TB_COMMON_CODE
WHERE  CD_GB = 'G023'
AND    CD IN (SELECT G.AGE_GRP_CD
              FROM   TB_CATE_4SRCH C4
                   , TB_CATE_GOODS CG
                   , TB_GOODS G
                   , TB_GOODS_STOCK GS
                   , TB_BRAND B
                   , TB_SITE_BRAND SB
              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 = GS.GOODS_CD
              AND    G.BRAND_CD = B.BRAND_CD
              AND    B.BRAND_CD = SB.BRAND_CD
              AND    C4.SITE_CD = #{siteCd}
              AND    C4.CATE_GB = #{cateGb}
              AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
              AND    C4.CATE1_NO = #{cate1No}
              <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>
              AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
              AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
              AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT   /*판매기간*/
              AND    GS.STOCK_QTY <![CDATA[>]]> 0 /*재고있는상품*/
              AND    SB.SITE_CD = #{siteCd}
              AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
              AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
             )
AND    USE_YN = 'Y'
ORDER  BY DISP_ORD
;

-- 필터-시즌 (getFilterSeasonList)
SELECT CD    AS SEASON_CD /*시즌코드*/
     , CD_NM AS SEASON_NM /*시즌명*/
FROM   TB_COMMON_CODE
WHERE  CD_GB = 'G006'
AND    CD IN (SELECT G.SEASON_CD
              FROM   TB_CATE_4SRCH C4
                   , TB_CATE_GOODS CG
                   , TB_GOODS G
                   , TB_GOODS_STOCK GS
                   , TB_BRAND B
                   , TB_SITE_BRAND SB
              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 = GS.GOODS_CD
              AND    G.BRAND_CD = B.BRAND_CD
              AND    B.BRAND_CD = SB.BRAND_CD
              AND    C4.SITE_CD = #{siteCd}
              AND    C4.CATE_GB = #{cateGb}
              AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
              AND    C4.CATE1_NO = #{cate1No}
              <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>
              AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
              AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
              AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT   /*판매기간*/
              AND    GS.STOCK_QTY <![CDATA[>]]> 0 /*재고있는상품*/
              AND    SB.SITE_CD = #{siteCd}
              AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
              AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
             )
AND    USE_YN = 'Y'
ORDER  BY DISP_ORD
;

-- 필터-컬러 (getFilterColorList)
SELECT C.COLOR_GRP_CD               /*컬러그룹코드*/
     , CC.CD_DESC     AS COLOR_CHIP /*컬러칩*/
FROM   TB_COLOR C
     , TB_COMMON_CODE CC
WHERE  C.COLOR_GRP_CD = CC.CD
AND    CC.CD_GB = 'G072'
AND    C.COLOR_CD IN (SELECT O.OPT_CD1
                      FROM   TB_CATE_4SRCH C4
                           , TB_CATE_GOODS CG
                           , TB_GOODS G
                           , TB_GOODS_STOCK GS
                           , TB_BRAND B
                           , TB_SITE_BRAND SB
                           , TB_OPTION O
                      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 = GS.GOODS_CD
                      AND    G.BRAND_CD = B.BRAND_CD
                      AND    B.BRAND_CD = SB.BRAND_CD
                      AND    G.GOODS_CD = O.GOODS_CD
                      AND    C4.SITE_CD = #{siteCd}
                      AND    C4.CATE_GB = #{cateGb}
                      AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
                      AND    C4.CATE1_NO = #{cate1No}
                      <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>
                      AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
                      AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
                      AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT   /*판매기간*/
                      AND    GS.STOCK_QTY <![CDATA[>]]> 0 /*재고있는상품*/
                      AND    SB.SITE_CD = #{siteCd}
                      AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
                      AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
                      AND    O.SOLDOUT_YN = 'N' /*품절이 아닌 옵션*/
                      AND    O.DISP_YN = 'Y' /*노출하는 옵션*/
                     )
AND    CC.USE_YN = 'Y'
ORDER  BY CC.DISP_ORD
;

-- 필터 - 상품혜택 (getFilterGoodsBenefitList)
WITH TAB_BENEFIT AS (
    SELECT GB.CPN_YN
         , GB.FREEGIFT_YN
         , G.FORMAL_GB
         , G.MIN_ORD_AMT
         , FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD,G.CURR_PRICE,#{custGb}) AS CURR_PRICE /*현재판매가*/
    FROM   TB_CATE_4SRCH C4
         , TB_CATE_GOODS CG
         , TB_GOODS G
         , TB_GOODS_STOCK GS
         , TB_GOODS_BENEFIT GB
         , TB_BRAND B
         , TB_SITE_BRAND SB
    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 = GS.GOODS_CD
    AND    G.GOODS_CD = GB.GOODS_CD
    AND    G.BRAND_CD = B.BRAND_CD
    AND    B.BRAND_CD = SB.BRAND_CD
    AND    C4.SITE_CD = #{siteCd}
    AND    C4.CATE_GB = #{cateGb}
    AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
    AND    C4.CATE1_NO = #{cate1No}
    <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>
    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
    AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT   /*판매기간*/
    AND    GS.STOCK_QTY <![CDATA[>]]> 0 /*재고있는상품*/
    AND    SB.SITE_CD = #{siteCd}
    AND    SB.USE_YN = 'Y' /*사이트에서 사용하는 브랜드*/
    AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
)
SELECT '10'       AS BENEFIT_CD
     , '쿠폰할인' AS BENEFIT_NM
FROM   TAB_BENEFIT
WHERE  CPN_YN = 'Y'

UNION ALL

SELECT '20'       AS BENEFIT_CD
     , '무료배송' AS BENEFIT_NM
FROM   TAB_BENEFIT
WHERE  MIN_ORD_AMT >= CURR_PRICE

UNION ALL

SELECT '30'       AS BENEFIT_CD
     , '사은품' AS BENEFIT_NM
FROM   TAB_BENEFIT
WHERE  FREEGIFT_YN = 'Y'

UNION ALL

SELECT '40'   AS BENEFIT_CD
     , '신상' AS BENEFIT_NM
FROM   TAB_BENEFIT
WHERE  FORMAL_GB = 'G009_10'
;

	<!-- 상품 목록 -->
	<select id="getGoodsList" parameterType="Cate4Srch" resultType="Goods">
		/* TsfGoods.getGoodsList */
		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             /*등록일시*/
		         , G.OPT_CD1            /*옵션코드1(색상코드)*/
		         , G.NUMB
		    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                                                                         /*정상가(최초판매가)*/
		                 , FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD,G.CURR_PRICE,#{custGb}) AS CURR_PRICE     /*현재판매가*/
		                 , G.MIN_ORD_AMT                                                                        /*최수주문금액*/
		                 , G.REG_DT                                                                             /*등록일시*/
		                 , O.OPT_CD1                                                                            /*옵션코드1(색상코드)*/
		                 <choose>
		                     <when test="sortingType == 'BELOVED'"> <!-- 인기상품순 -->
		                 , RANK() OVER(ORDER BY CG.DISP_ORD
		                                      , GS.SELL_WEEK_QTY DESC
		                                      , G.GOODS_CD) AS NUMB
		                     </when>
		                     <when test="sortingType == 'REVIEW'"> <!-- 리뷰많은순 -->
		                 , RANK() OVER(ORDER BY CG.DISP_ORD
		                                      , GS.REVIEW_REG_CNT DESC
		                                      , G.GOODS_CD) AS NUMB
		                     </when>
		                     <otherwise> <!-- 최신상품순 -->
		                 , RANK() OVER(ORDER BY CG.DISP_ORD
		                                      , G.FORMAL_GB
		                                      , G.REG_DT DESC
		                                      , G.GOODS_CD) AS NUMB
		                     </otherwise>
		                 </choose>
		            FROM   TB_CATE_4SRCH C4
		                 , TB_CATE_GOODS CG
		                 , TB_GOODS G
		                 , TB_GOODS_STOCK S
		                 , TB_BRAND B
		                 , TB_BRAND_GROUP BG
		                 , TB_OPTION O
		                 <if test="sortingType == 'BELOVED' or sortingType == 'REVIEW'"> <!-- 인기상품순, 리뷰많은순 -->
		                 , TB_GOODS_SUMMARY GS
		                 </if>
		            WHERE  C4.LEAF_CATE_NO = CG.CATE_NO
		            AND    CG.GOODS_CD = G.GOODS_CD
		            AND    G.GOODS_CD = S.GOODS_CD
		            AND    G.BRAND_CD = B.BRAND_CD
		            AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		            AND    G.GOODS_CD = O.GOODS_CD
		            <if test="sortingType == 'BELOVED' or sortingType == 'REVIEW'"> <!-- 인기상품순, 리뷰많은순 -->
		            AND    G.GOODS_CD = GS.GOODS_CD
		            </if>
		            AND    C4.SITE_CD = #{siteCd}
		            AND    C4.CATE_GB = #{cateGb}
		            AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
		            AND    C4.CATE1_NO = #{cate1No}
		            <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 != ''">
		            AND    CG.BRAND_GROUP_NO = #{brandGroupNo}
		                </when>
		                <otherwise>
		            AND    CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
		                </otherwise>
		            </choose>
		            <if test="brandGroupNoArr != null and brandGroupNoArr.length > 0">
		            AND	   CG.BRAND_GROUP_NO IN
		                <foreach collection="brandGroupNoArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		                </foreach>
		            </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="agesArr != null and agesArr.length > 0"> <!-- 연령대 -->
		            AND	   G.AGE_GRP_CD IN
		                <foreach collection="agesArr" 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 <![CDATA[>]]> 0 /*재고있는 상품*/
		            AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
		            AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
		            <if test="colorArr != null and colorArr.length > 0"> <!-- 색상 -->
		            AND	   O.OPT_CD1 IN (SELECT COLOR_CD
		                                 FROM   TB_COLOR
		                                 WHERE  COLOR_GRP_CD IN
		                                 <foreach collection="colorArr" item="item" index="index"  open="(" close=")" separator=",">
		                                     #{item}
		                                 </foreach>
		                                 AND    USE_YN = 'Y' /*사용하는색상*/
		                                )
		            </if>
		            <if test="sizeArr != null and sizeArr.length > 0"> <!-- 사이즈 -->
		            AND	   O.OPT_CD2 IN
		                <foreach collection="sizeArr" item="item" index="index"  open="(" close=")" separator=",">
		                #{item}
		                </foreach>
		            </if>
		           ) G
		    WHERE  G.NUMB <![CDATA[<=]]> #{maxRow}
		    <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>
		    <if test="benefitArr != null and benefitArr.length > 0"> <!-- 혜택 -->
		        <foreach collection="benefitArr" item="item" index="index">
		            <if test="item == '10'"> <!-- 쿠폰할인 -->
		    AND   EXISTS (SELECT 1
		                  FROM   TB_GOODS_BENEFIT
		                  WHERE  GOODS_CD = G.GOODS_CD
		                  AND    CPN_YN = 'Y'
		                 )
		            </if>
		            <if test="item == '20'"> <!-- 무료배송 -->
		    AND   G.MIN_ORD_AMT <![CDATA[>=]]> G.CURR_PRICE
		            </if>
		            <if test="item == '30'"> <!-- 사은품 -->
		    AND   EXISTS (SELECT 1
		                  FROM   TB_GOODS_BENEFIT
		                  WHERE  GOODS_CD = G.GOODS_CD
		                  AND    FREEGIFT_YN = 'Y'
		                 )
		            </if>
		            <if test="item == '40'"> <!-- 신상 -->
		    AND   G.FORMAL_GB = 'G009_10'
		            </if>
		        </foreach>
		    </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_GOODS G
		                 , TB_GOODS_IMG GI
		            WHERE  G.GOODS_CD = GI.GOODS_CD
		            AND    G.MAIN_COLOR_CD = GI.COLOR_CD
		           ) Z
		    GROUP  BY GOODS_CD
		)
		, TAB_GOODS_VIDEO AS (
		    /* 상품의 동영상 목록 */
		    SELECT GOODS_CD
		         , MAX(CASE WHEN RNUM = 1 THEN VIDEO_GB END)  AS VIDEO_GB_M
		         , MAX(CASE WHEN RNUM = 1 THEN VIDEO_VAL END) AS VIDEO_VAL_M
		         , MAX(CASE WHEN RNUM = 2 THEN VIDEO_GB END)  AS VIDEO_GB_S
		         , MAX(CASE WHEN RNUM = 2 THEN VIDEO_VAL END) AS VIDEO_VAL_S
		    FROM   (
		            SELECT G.GOODS_CD
		                 , V.VIDEO_GB
		                 , V.VIDEO_VAL
		                 , VD.REG_DT
		                 , RANK() OVER(PARTITION BY G.GOODS_CD ORDER BY VD.REG_DT) AS RNUM
		            FROM   TAB_GOODS G
		                 , TB_VIDEO_DISPLOC VD
		                 , TB_VIDEO V
		            WHERE  G.GOODS_CD = VD.DISPLOC_VAL
		            AND    VD.VIDEO_SQ = V.VIDEO_SQ
		            AND    VD.DISPLOC_GB = 'G' /*상품*/
		            AND    VD.DISP_YN = 'Y'
		            AND    V.DISP_YN ='Y'
		           ) 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
		     , GV.VIDEO_GB_M
		     , GV.VIDEO_VAL_M
		     , GV.VIDEO_GB_S
		     , GV.VIDEO_VAL_S
		     <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  
		LEFT OUTER JOIN TAB_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD
		LEFT OUTER JOIN TAB_GOODS_VIDEO GV ON G.GOODS_CD = GV.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>
	
	<!-- 소셜상품(=핫딜) 목록 -->
	<select id="getSocialGoodsList" resultType="Social" parameterType="Social">
		/* TsfSocial.getSocialGoodsList */
		WITH TAB_GOODS AS (
		    SELECT S.SOCIAL_SQ
		         , CASE WHEN BG.DISP_NM_LANG = 'EN' THEN
		                BG.BRAND_GROUP_ENM
		            ELSE
		                BG.BRAND_GROUP_KNM
		           END                                     AS BRAND_GROUP_NM /*브랜드그룹명*/
		         , SG.GOODS_CD                                               /*상품코드*/
		         , G.GOODS_TNM                                               /*상품타이틀명*/
		         , G.MAIN_COLOR_CD                                           /*메인컬러코드*/
		         , G.GOODS_NM                                                /*상품명*/
		         , G.GOODS_GB                                                /*상품구분*/
		         , G.FOREIGN_BUY_YN                                          /*해외구매대행여부*/
		         , G.PARALLEL_IMPORT_YN                                      /*병행수입여부*/
		         , G.ORDER_MADE_YN                                           /*주문제작여부*/
		         , G.LIST_PRICE                                              /*정상가*/
		         , SG.CURR_APRICE                          AS CURR_PRICE     /*변경후현재판매가*/
		         , SG.DC_ARATE                             AS DC_RATE        /*변경후할인율*/
		         , SG.DISP_ORD                                               /*노출순서*/
		         , G.GOODS_TYPE                                              /*상품유형*/
		         , G.GOODS_STAT                                              /*상품상태*/
		         , ROW_NUMBER() OVER(ORDER BY SG.DISP_ORD
		                                    , SG.GOODS_CD) AS NUMB
		    FROM   TB_SOCIAL S
		         , TB_SOCIAL_GOODS SG
		         , TB_GOODS G
		         , TB_BRAND B
		         , TB_BRAND_GROUP BG
		    WHERE  S.SOCIAL_SQ = SG.SOCIAL_SQ
		    AND    SG.GOODS_CD = G.GOODS_CD
		    AND    G.BRAND_CD = B.BRAND_CD
		    AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		    AND    S.SOCIAL_SQ = #{socialSq}
		    AND    NOW() BETWEEN S.SOCIAL_STDT AND S.SOCIAL_EDDT
		    AND    S.SOCIAL_TYPE = 'G062_10' /*핫딜*/
		    AND    S.SITE_CD = #{siteCd}
		    AND    S.FRONT_GB IN ('A', #{frontGb}) /*프론트구분*/
		    AND    S.APPLY_GB = 'A' /*적용구분(P:대기,A:적용,F:종료)*/
		    AND    S.USE_YN = 'Y' /*사용하는소셜*/
		    AND    SG.DEL_YN = 'N' /*삭제안된상품*/
		    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		    AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
		    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*유효한판매기간*/
		)
		, 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_GOODS G
		               , TB_GOODS_IMG GI
		          WHERE  G.GOODS_CD = GI.GOODS_CD
		          AND    G.MAIN_COLOR_CD = GI.COLOR_CD
		          ) Z
		     GROUP BY GOODS_CD
		)
		, TAB_STOCK AS (
		    SELECT G.GOODS_CD
		         , SUM(S.CURR_STOCK_QTY - S.BASE_STOCK_QTY) AS STOCK_QTY_SUM
		    FROM   TAB_GOODS G
		         , VW_STOCK S
		    WHERE  G.GOODS_CD = S.GOODS_CD
		    AND    G.GOODS_TYPE = 'G056_N'
		    AND    S.DISP_YN = 'Y' /*노출하는상품만*/
		    GROUP  BY G.GOODS_CD
		    UNION ALL 
		    SELECT G.GOODS_CD
		         , SUM(S.CURR_STOCK_QTY - S.BASE_STOCK_QTY) AS STOCK_QTY_SUM
		    FROM   TAB_GOODS G
		         , VW_STOCK_COMPOSE S
		    WHERE  G.GOODS_CD = S.GOODS_CD
		    AND    G.GOODS_TYPE != 'G056_N'
		    AND    S.DISP_YN = 'Y' /*노출하는상품만*/
		    GROUP  BY 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
		     , G.CURR_PRICE    /*현재판매가*/
		     , GI.SYS_IMG_NM
		     , GI.SYS_IMG_NM2
		     , S.STOCK_QTY_SUM
		     <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
		LEFT OUTER JOIN TAB_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD
		LEFT OUTER JOIN TAB_STOCK S ON G.GOODS_CD = S.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>

	SELECT * FROM TB_GOODS_VIDEO;

SELECT * FROM TB_GOODS_BENEFIT_PRICE;

SELECT GROUP_CONCAT(CONCAT(VIDEO_GB,':',KMC_KEY) ORDER BY RNUM SEPARATOR ',')
	    FROM   (
	            SELECT GV.VIDEO_GB
	                 , GV.KMC_KEY
	                 , GV.REG_DT
	                 , RANK() OVER(ORDER BY GV.REG_DT, GV.KMC_KEY) AS RNUM
	            FROM   TB_GOODS_VIDEO GV
	            WHERE  GV.GOODS_CD = 'CNW1XAPT32'
	            AND    GV.DISP_YN = 'Y'
	            AND    GV.KMC_KEY IS NOT NULL
	            ) Z
	            ;
	           
/* TsfGoods.getGoodsList */
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 /*리뷰등록건수*/
--          , G.NUMB
    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                                                                         /*정상가(최초판매가)*/
                 -- , FN_GET_BENEFIT_PRICE('P',G.GOODS_CD,G.CURR_PRICE,'') AS CURR_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_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 = 'G000_10'
            AND    C4.CATE_GB = 'G032_101'
            AND    C4.CATE_TYPE = 'G031_10' /*상품카테고리*/
            AND    C4.CATE1_NO = 1100
--             <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 != ''">
--             AND    CG.BRAND_GROUP_NO = #{brandGroupNo}
--                 </when>
--                 <otherwise>
            AND    CG.BRAND_GROUP_NO = 0 /*브랜드메인에서 접근한 것이 아니면*/
--                 </otherwise>
--             </choose>
            AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
            AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
            AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT /*유효한 판매기간*/
--             <if test="agesArr != null and agesArr.length > 0"> <!-- 연령대 -->
--             AND	   G.AGE_GRP_CD IN
--                 <foreach collection="agesArr" 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>
--             <if test="formalGb != null and formalGb != ''"> <!-- 정상이월구분 -->
            AND    G.FORMAL_GB = 'G009_10' /*정상상품만*/
--             </if>
--             <if test="sexGb != null and sexGb != ''"> <!-- 성별구분 -->
            AND    G.SEX_GB = 'G007_Z' /*남여공용*/
--             </if>
            AND    S.STOCK_QTY > 0 /*재고있는 상품*/
            AND    B.USE_YN = 'Y' /*사용하는 브랜드*/
--             <if test="brandGroupNoArr != null and brandGroupNoArr.length > 0">
--             AND	   BG.BRAND_GROUP_NO IN
--                 <foreach collection="brandGroupNoArr" item="item" index="index"  open="(" close=")" separator=",">
--                 #{item}
--                 </foreach>
--             </if>
            AND    BG.USE_YN = 'Y' /*사용하는 브랜드그룹*/
--             <if test="benefitArr != null and benefitArr.length > 0"> <!-- 혜택 -->
--             AND    EXISTS (SELECT 1
--                            FROM   TB_GOODS_BENEFIT
--                            WHERE  GOODS_CD = CG.GOODS_CD
--                            AND    BENEFIT_GB IN
--                            <foreach collection="benefitArr" item="item" index="index"  open="(" close=")" separator=",">
--                            #{item}
--                            </foreach>
--                           )
--             </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 = 'N'
--                           )
--             </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 = 'N'
    GROUP  BY O.GOODS_CD, O.OPT_CD1
)
, TAB_ALL_GOODS AS (
    SELECT *
    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 == 'BELOVED'"> <!-- 인기상품순 -->
		--                  , RANK() OVER(ORDER BY GS.SELL_WEEK_QTY DESC
		--                                       , G.GOODS_CD) AS NUMB
		--                      </when>
		--                      <when test="sortingType == 'REVIEW'"> <!-- 리뷰많은순 -->
		--                  , RANK() OVER(ORDER BY GS.REVIEW_REG_CNT DESC
		--                                       , G.GOODS_CD) AS NUMB
		--                      </when>
		--                      <otherwise> <!-- 최신상품순 -->
		                 , RANK() OVER(ORDER BY G.FORMAL_GB
		                                      , G.REG_DT DESC
		                                      , G.GOODS_CD) AS NUMB
		--                      </otherwise>
		--                  </choose>
			FROM TAB_GOODS G
			LEFT OUTER JOIN TAB_OPTION O ON G.GOODS_CD = O.GOODS_CD
           ) ORIGINAL
    WHERE NUMB BETWEEN 1 AND 30
)
, 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
           ) Z
    GROUP  BY GOODS_CD
)
-- , TAB_GOODS_VIDEO AS (
--     /* 상품의 동영상 목록 */
--     SELECT 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 G.GOODS_CD
--                  , GV.VIDEO_GB
--                  , GV.KMC_KEY
--                  , GV.REG_DT
--                  , RANK() OVER(PARTITION BY G.GOODS_CD ORDER BY GV.REG_DT) AS RNUM
--             FROM   TAB_ALL_GOODS G
--                  , TB_GOODS_VIDEO GV
--             WHERE  G.GOODS_CD = GV.GOODS_CD
--             AND    GV.DISP_YN = 'Y'
--             AND    GV.KMC_KEY IS NOT NULL
--            ) 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 MAX(GI.SYS_IMG_NM)
--         FROM   TB_GOODS_IMG GI
--         WHERE  GI.GOODS_CD = G.GOODS_CD
--         AND    GI.COLOR_CD = G.MAIN_COLOR_CD
--         AND    GI.DEFAULT_IMG_YN = 'Y'
-- 	   ) AS SYS_IMG_NM
-- 	  , (
--         SELECT MAX(GI.SYS_IMG_NM)
--         FROM   TB_GOODS_IMG GI
--         WHERE  GI.GOODS_CD = G.GOODS_CD
--         AND    GI.COLOR_CD = G.MAIN_COLOR_CD
--         AND    GI.MOUSEOVER_IMG_YN = 'Y'
-- 	   ) 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 <= 2
       ) AS VIDEO_VAL
     , (
        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 /*사이즈*/
     , FORMAL_GB
     , REG_DT
     , 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
-- LEFT OUTER JOIN TAB_GOODS_VIDEO GV ON G.GOODS_CD = GV.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>
;