<?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.TsfSocialDao">
	<select id="getSocialInfo" resultType="Social" parameterType="Social">
		/* TsfSocial.getSocialInfo*/
		SELECT A.SOCIAL_SQ
		     , A.SOCIAL_NM
		     , A.SOCIAL_TYPE
		     , A.SITE_CD
		     , A.FRONT_GB
		     , DATE_FORMAT(A.SOCIAL_STDT, '%Y%m%d') AS SOCIAL_STDT
		     , DATE_FORMAT(A.SOCIAL_EDDT, '%Y%m%d') AS SOCIAL_EDDT
		     , A.SOCIAL_TNM
		     , A.USE_YN
		     , A.APPLY_GB
		FROM TB_SOCIAL A 
		WHERE 1=1
		  AND NOW() BETWEEN A.SOCIAL_STDT AND A.SOCIAL_EDDT 
		  AND A.USE_YN = 'Y'			     		/*사용여부*/
		  AND A.APPLY_GB = 'A'						 /*적용구분(P:대기,A:적용,F:종료)*/
		  AND A.FRONT_GB IN ('A',#{frontGb}) 		 /*프론트 구분*/
		ORDER BY A.REG_DT DESC LIMIT 1
	</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                                              /*상품상태*/
		         , G.MIN_ORD_QTY 											 /*최소 주문수량*/
		         , 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
		)
		<if test="mainYn == null or mainYn ==''">
		, TAB_STOCK AS (
		     SELECT G.GOODS_CD
		         , SUM(case WHEN S.SOLDOUT_YN = 'Y' THEN 0
		                    ELSE S.CURR_STOCK_QTY - S.BASE_STOCK_QTY END ) AS STOCK_QTY_SUM
		         , MIN(CASE WHEN S.SOLDOUT_YN = 'N' THEN S.OPT_CD ELSE 'XXX' END) AS OPT_CD 
		    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 GOODS_CD
		     --  , COMPS_GOODS_CD
		         , SUM(case WHEN SOLDOUT_YN = 'Y' THEN 0
		              ELSE CURR_STOCK_QTY END ) AS STOCK_QTY_SUM
		         , GROUP_CONCAT(DISTINCT OPT_CD) AS OPT_CD
		           <!-- , GROUP_CONCAT(DISTINCT CONCAT(OPT_CD ,':' ,CASE WHEN SOLDOUT_YN = 'Y' THEN 'Y'
		                          ELSE CASE WHEN CURR_STOCK_QTY > 0 THEN 'N' ELSE 'Y' END
		                          END) ) AS OPT_CD -->
		    FROM (
		             SELECT G.GOODS_CD
		                  , GC.COMPS_GOODS_CD
		                  , S.OPT_CD
		                  , S.SOLDOUT_YN
		                  , S.CURR_STOCK_QTY - S.BASE_STOCK_QTY     AS CURR_STOCK_QTY
		                  , RANK()
		                     OVER (PARTITION BY G.GOODS_CD ,GC.COMPS_GOODS_CD ORDER BY S.SOLDOUT_YN, S.CURR_STOCK_QTY DESC , S.OPT_CD) AS NUMB
		             FROM TAB_GOODS G
		                , TB_GOODS_COMPOSE GC
		                , VW_STOCK S
		             WHERE 1 = 1
		               AND G.GOODS_CD = GC.GOODS_CD
		               AND GC.COMPS_GOODS_CD = S.GOODS_CD
		               AND G.GOODS_TYPE = 'G056_S'
		               AND S.DISP_YN = 'Y' /*노출하는상품만*/
		         ) Z
		    WHERE  NUMB = 1
		    GROUP BY GOODS_CD
		    )
		</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.BRAND_GROUP_NM
		     , G.GOODS_NM
		     , 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    /*현재판매가*/
		     , 100 - ROUND((G.CURR_PRICE / G.LIST_PRICE) * 100 ,0) AS DC_RATE 
		     , GI.SYS_IMG_NM
		     , GI.SYS_IMG_NM2
		<if test="mainYn == null or mainYn ==''">
		     , S.STOCK_QTY_SUM
		     , S.OPT_CD
		</if>
		     , G.MIN_ORD_QTY 
		     , (SELECT GROUP_CONCAT(COMPS_GOODS_CD)  FROM TB_GOODS_COMPOSE WHERE GOODS_CD = G.GOODS_CD) AS COMPS_GOODS_CD  
		     <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
		<if test="mainYn == null or mainYn ==''">
		LEFT OUTER JOIN TAB_STOCK S ON G.GOODS_CD = S.GOODS_CD
		</if>
		<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>
		<if test="pageable != null">
		WHERE G.NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
		</if>
	</select>
	
</mapper>