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

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

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>
	
	<!-- 위시리스트 월 -->
	<select id="getWishListMonth" resultType="WishList" parameterType="WishList">
		/* TsfWishlist.getWishListMonth*/
		SELECT COUNT(*) AS COUNT
		      ,DATE_FORMAT(W.REG_DT,'%Y.%m') AS REG_DT 
		FROM TB_WISHLIST W
		WHERE CUST_NO = #{custNo}
		GROUP BY DATE_FORMAT(W.REG_DT,'%Y.%m') 
		ORDER BY REG_DT DESC
	</select>
	
	<!-- 위시리스트 상품목록 (임시 = 수정예정)-->
	<select id="getWishListGoodsList" parameterType="WishList" resultType="Goods">
		/* TsfWishlist.getWishListGoodsList*/
		SELECT Z.*
			      ,(CASE WHEN Z.LIST_PRICE > 0 THEN 100 - ROUND((Z.CURR_PRICE / Z.LIST_PRICE) * 100 ,0) ELSE 0 END ) AS DC_RATE
			      ,DATE_FORMAT(Z.REG_DT,'%Y.%m') AS REG_DT_MONTH 
			FROM
			(
			WITH TAB_PLAN_GOODS AS (
			    SELECT W.AF_LINK_CD 
			          ,W.CONTENTS_LOC 
			          ,W.CUST_NO 
			          ,W.REG_DT
			          ,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.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.MIN_ORD_QTY                                            /*최소주문수량*/  				
			         , GS.STOCK_QTY
			         , MIN(CASE WHEN SOLDOUT_YN = 'N' THEN OPT_CD ELSE 'XXX' END) AS OPT_CD
			    FROM   TB_WISHlIST W
			         , TB_GOODS G LEFT OUTER JOIN VW_STOCK VS ON G.GOODS_CD = VS.GOODS_CD 
			         , TB_GOODS_STOCK GS
			         , TB_BRAND B
			         , TB_BRAND_GROUP BG
			    WHERE  W.GOODS_CD = G.GOODS_CD 
			    AND    G.GOODS_CD = GS.GOODS_CD
			    AND    G.BRAND_CD = B.BRAND_CD
			    AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
			    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
			    AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
			    AND    B.USE_YN = 'Y'
			    AND    BG.USE_YN = 'Y'
			    AND    W.CUST_NO = #{custNo}
			    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT
			    GROUP BY W.AF_LINK_CD 
			          ,W.CONTENTS_LOC 
			          ,W.CUST_NO 
			          ,W.REG_DT
			         , G.GOODS_CD                                               /*상품코드*/
			         , G.GOODS_NM                                               /*상품명*/
			         , G.GOODS_GB                                               /*상품구분*/
			         , G.GOODS_TYPE 											/*상품타입*/
			         , 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.MIN_ORD_QTY                                            /*최소주문수량*/  				
			         , GS.STOCK_QTY
			)
			, TAB_GOODS_IMG AS (
			    SELECT PG.GOODS_CD
			         , MAX(CASE WHEN GI.DEFAULT_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END)   AS SYS_IMG_NM
			         , MAX(CASE WHEN GI.MOUSEOVER_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END) AS SYS_IMG_NM2
			    FROM   TAB_PLAN_GOODS PG
			         , TB_GOODS_IMG GI
			    WHERE  PG.GOODS_CD = GI.GOODS_CD
			    AND    PG.MAIN_COLOR_CD = GI.COLOR_CD
			    AND    (GI.DEFAULT_IMG_YN = 'Y' OR GI.MOUSEOVER_IMG_YN = 'Y')
			    GROUP  BY PG.GOODS_CD
			)
			, TAB_COLORCHIP AS (
		            /* 상품 컬러칩 */
		            SELECT PG.GOODS_CD
		                 , GROUP_CONCAT(DISTINCT CONCAT(C.COLOR_CD,':',CC.CD_DESC) ORDER BY CC.DISP_ORD SEPARATOR ',') AS COLOR_CHIPS
		            FROM   TAB_PLAN_GOODS PG
		                 , TB_OPTION O
		                 , TB_COLOR C
		                 , TB_COMMON_CODE CC
		            WHERE  PG.GOODS_CD = O.GOODS_CD
		            AND    O.OPT_CD1 = C.COLOR_CD
		            AND    C.COLOR_GRP_CD = CC.CD
		            AND    O.DISP_YN = 'Y'
		            AND    C.USE_YN = 'Y'
		            AND    CC.USE_YN = 'Y'
		            GROUP  BY PG.GOODS_CD
		        )
		    , TAB_GOODS_BENEFIT AS (
		            /* 상품혜택 */
		            SELECT GOODS_CD
		                 , GROUP_CONCAT(CONCAT(BENEFIT_GB,':',BENEFIT_NM) ORDER BY NUMB SEPARATOR ',') AS ICON
		            FROM   (
		                    SELECT PG.GOODS_CD
		                         , GB.BENEFIT_GB
		                         , CASE WHEN GB.BENEFIT_GB = '10' THEN '쿠폰할인'
		                                WHEN GB.BENEFIT_GB = '20' THEN '무료배송'
		                                WHEN GB.BENEFIT_GB = '30' THEN '사은품'
		                                WHEN GB.BENEFIT_GB = '40' THEN '신상'
		                                ELSE '총알배송'
		                           END                               AS BENEFIT_NM
		                         , RANK() OVER(PARTITION BY PG.GOODS_CD
		                                       ORDER BY GB.BENEFIT_GB
		                                              , GB.GOODS_CD) AS NUMB
		                    FROM   TAB_PLAN_GOODS PG
		                         , TB_GOODS_BENEFIT GB
		                    WHERE  PG.GOODS_CD = GB.GOODS_CD
		                   ) Z
		            GROUP  BY GOODS_CD
		        )
		    SELECT 
		           PG.BRAND_GROUP_NM
		         , PG.GOODS_CD
		         , PG.STOCK_QTY
		         , PG.MIN_ORD_QTY
		         , PG.GOODS_TYPE
		         , PG.OPT_CD
		         , FN_GET_GOODS_NM(PG.GOODS_NM,PG.GOODS_GB,PG.FOREIGN_BUY_YN,PG.PARALLEL_IMPORT_YN,PG.ORDER_MADE_YN) AS GOODS_FULL_NM /*상품FULL명*/
		         , PG.GOODS_NM
		         , PG.GOODS_TNM
		         , PG.MAIN_COLOR_CD
		         , PG.LIST_PRICE
		         , PG.AF_LINK_CD 
		         , PG.CONTENTS_LOC 
		         , PG.CUST_NO 
		         , C.COLOR_CHIPS                                                                                                      /*컬러칩*/
		         , GB.ICON 
		         , PG.REG_DT
		         , FN_GET_BENEFIT_PRICE(#{frontGb},PG.GOODS_CD,PG.CURR_PRICE,#{custGb})                     AS CURR_PRICE    /*현재판매가*/
		         , GI.SYS_IMG_NM
		         , GI.SYS_IMG_NM2
		    	FROM   TAB_PLAN_GOODS PG
		    INNER JOIN TAB_GOODS_IMG GI ON PG.GOODS_CD = GI.GOODS_CD
		    LEFT OUTER JOIN TAB_COLORCHIP C ON PG.GOODS_CD = C.GOODS_CD
		    LEFT OUTER JOIN TAB_GOODS_BENEFIT GB ON PG.GOODS_CD = GB.GOODS_CD
		    )Z
		ORDER BY Z.REG_DT DESC
	</select>
	
	<select id="getQuickWishList" parameterType="WishList" resultType="Goods">
		/* TsfWishlist.getQuickWishList*/
		SELECT Z.*
		FROM
		(
		WITH TAB_GOODS AS (
		    SELECT W.AF_LINK_CD 
		          ,W.CONTENTS_LOC 
		          ,W.CUST_NO 
		          ,W.REG_DT
		          ,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.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.MIN_ORD_QTY                                            /*최소주문수량*/  				
		    FROM   TB_WISHlIST W
		         , TB_GOODS G 
		         , TB_BRAND B
		         , TB_BRAND_GROUP BG
		    WHERE  W.GOODS_CD = G.GOODS_CD 
		    AND    G.BRAND_CD = B.BRAND_CD
		    AND    B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		    AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
		    AND    G.SELF_MALL_YN = 'Y'   /*몰노출상품*/
		    AND    B.USE_YN = 'Y'
		    AND    BG.USE_YN = 'Y'
		    AND    W.CUST_NO =   #{custNo} /**P*/
		    AND    NOW() BETWEEN G.SELL_STDT AND G.SELL_EDDT
		    GROUP BY W.AF_LINK_CD 
		          ,W.CONTENTS_LOC 
		          ,W.CUST_NO 
		          ,W.REG_DT
		         , G.GOODS_CD                                               /*상품코드*/
		         , G.GOODS_NM                                               /*상품명*/
		         , G.GOODS_GB                                               /*상품구분*/
		         , G.GOODS_TYPE 											/*상품타입*/
		         , 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.MIN_ORD_QTY                                            /*최소주문수량*/  				
		)
		, TAB_GOODS_IMG AS (
		    SELECT G.GOODS_CD
		         , MAX(CASE WHEN GI.DEFAULT_IMG_YN = 'Y' THEN GI.SYS_IMG_NM ELSE NULL END)   AS SYS_IMG_NM
		    FROM   TAB_GOODS G
		         , TB_GOODS_IMG GI
		    WHERE  G.GOODS_CD = GI.GOODS_CD
		    AND    G.MAIN_COLOR_CD = GI.COLOR_CD
		    AND    (GI.DEFAULT_IMG_YN = 'Y' OR GI.MOUSEOVER_IMG_YN = 'Y')
		    GROUP  BY G.GOODS_CD
		)
		, 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
		         , 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
		    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 IN ('G056_D','G056_S')
		               AND S.DISP_YN = 'Y' /*노출하는상품만*/
		         ) Z
		    WHERE  NUMB = 1
		    GROUP BY GOODS_CD
		  )
		    SELECT 
		           G.BRAND_GROUP_NM
		         , G.GOODS_CD
		         , G.MIN_ORD_QTY
		         , G.GOODS_TYPE
		         , 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_NM
		         , G.GOODS_TNM
		         , G.MAIN_COLOR_CD
		         , G.LIST_PRICE
		         , G.AF_LINK_CD 
		         , G.CONTENTS_LOC 
		         , G.CUST_NO 
		         , G.REG_DT
		         , FN_GET_BENEFIT_PRICE( 'P' ,G.GOODS_CD,G.CURR_PRICE, 'G100_10' )                     AS CURR_PRICE    /*현재판매가*/
		         , GI.SYS_IMG_NM
		         , S.STOCK_QTY_SUM
		         , S.OPT_CD 
		         , (SELECT GROUP_CONCAT(COMPS_GOODS_CD)  FROM TB_GOODS_COMPOSE WHERE GOODS_CD = G.GOODS_CD) AS COMPS_GOODS_CD  
		    	FROM   TAB_GOODS G
		    INNER 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
		    )Z
		    ORDER BY FIELD(Z.STOCK_QTY_SUM, 1, 2 ,3 ,4 ,5) DESC ,Z.REG_DT DESC
	</select>
	
	<!-- 위시리스트 상품여부용-->
	<select id="getWishListGoodsCount" parameterType="WishList" resultType="int">
		/* TsfWishlist.getWishListGoodsCount*/
		SELECT COUNT(*)
		FROM TB_WISHLIST
		WHERE CUST_NO = #{custNo}
		AND GOODS_CD = #{goodsCd}
	</select>
	
</mapper>