<?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.batch.biz.dao.TsbCouponDao">

	<!--쿠폰혜택 및 정보-->
	<select id="getCouponBenefitInfo" parameterType="CustCoupon" resultType="CustCoupon">
		/*TsbCoupon.getCouponBenefitInfo*/
		WITH TAB_COUPON_REFVAL1 AS (
		    SELECT CR.CPN_ID
		         , CR.CPN_TARGET
		         , CASE WHEN CR.CPN_TARGET = 'G260_10' /*상품*/ THEN (SELECT GOODS_NM FROM TB_GOODS WHERE GOODS_CD = CR.REF_VAL AND GOODS_STAT = 'G008_90')
		                WHEN CR.CPN_TARGET = 'G260_11' /*카테고리*/ THEN
		                    CASE WHEN IFNULL((SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE1_NO = CR.REF_VAL),0) > 0
		                              THEN (SELECT CATE1_NM FROM TB_CATE_4SRCH WHERE CATE1_NO = CR.REF_VAL LIMIT 1)
		                         WHEN IFNULL((SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE2_NO = CR.REF_VAL),0) > 0
		                              THEN (SELECT CONCAT(CATE1_NM,' > ',CATE2_NM) FROM TB_CATE_4SRCH WHERE CATE2_NO = CR.REF_VAL LIMIT 1)
		                         WHEN IFNULL((SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE3_NO = CR.REF_VAL),0) > 0 THEN (SELECT CONCAT(CATE1_NM,' > ',CATE2_NM) FROM TB_CATE_4SRCH WHERE CATE3_NO = CR.REF_VAL LIMIT 1)
		                         WHEN IFNULL((SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE4_NO = CR.REF_VAL),0) > 0 THEN (SELECT CONCAT(CATE1_NM,' > ',CATE2_NM) FROM TB_CATE_4SRCH WHERE CATE4_NO = CR.REF_VAL LIMIT 1)
		                    END
		                WHEN CR.CPN_TARGET = 'G260_12' /*브랜드*/ THEN (SELECT BRAND_KNM FROM TB_BRAND WHERE BRAND_CD = CR.REF_VAL AND USE_YN = 'Y')
		                WHEN CR.CPN_TARGET = 'G260_13' /*업체*/ THEN (SELECT SUPPLY_COMP_NM FROM TB_SUPPLY_COMPANY WHERE SUPPLY_COMP_CD = CR.REF_VAL AND USE_YN = 'Y' AND SUPPLY_STAT = 'G010_30')
		           END AS REF_VAL
		         , RANK() OVER(PARTITION BY CR.CPN_ID, CR.CPN_TARGET ORDER BY REF_VAL) AS RK
		    FROM   TB_COUPON_REFVAL CR
		    WHERE  CR.CPN_TARGET != 'G260_14' /*제외상품아닌넘*/
		    AND    CR.CPN_ID = #{cpnId}
		)
		, TAB_COUPON_REFVAL2 AS (
		    SELECT CPN_ID
		         , CPN_TARGET
		         , GROUP_CONCAT(REF_VAL ORDER BY RK ASC SEPARATOR '/' ) AS REF_VAL
		         , (SELECT COUNT(*)
		            FROM   TAB_COUPON_REFVAL1
		            WHERE  CPN_ID = CR.CPN_ID
		            AND    CPN_TARGET = CR.CPN_TARGET)                  AS CNT
		    FROM   TAB_COUPON_REFVAL1 CR
		    WHERE  1 = 1
		    AND    (
		            (CPN_TARGET != 'G260_12' AND RK = 1) /*브랜드 외는 1개만 노출*/
		            OR
		            (CPN_TARGET = 'G260_12' AND RK <![CDATA[<=]]> 2) /*브랜드만 2개 노출*/
		           )
		    GROUP  BY CPN_ID, CPN_TARGET
		    LIMIT 1
		)
		SELECT CP.CPN_ID
		     , CP.END_ALIM_YN
		     , CASE WHEN CP.DC_WAY = 'G240_10' THEN '원'
		            ELSE '%'
		       END                                      AS DC_WAY    /*할인방법*/
		     , CASE WHEN #{frontGb} = 'P' THEN CP.DC_PVAL
		            WHEN #{frontGb} = 'M' THEN CP.DC_MVAL
		            WHEN #{frontGb} = 'A' THEN CP.DC_AVAL
		            ELSE CP.DC_AVAL                                              END AS DC_VAL
		     , CASE WHEN CP.PD_GB = 'D' THEN DATE_FORMAT(NOW(), '%Y%m%d%H%i%S')
		            ELSE DATE_FORMAT(CP.AVAIL_STDT, '%Y%m%d%H%i%S')              END AS AVAIL_STDT
		     , CASE WHEN CP.PD_GB = 'D' THEN DATE_FORMAT(CONCAT(CURRENT_DATE + INTERVAL CP.AVAIL_DAYS DAY, ' 23:59:59'), '%Y%m%d%H%i%S')
		            ELSE  DATE_FORMAT(CP.AVAIL_EDDT, '%Y%m%d%H%i%S')             END AS AVAIL_EDDT
		     , CASE WHEN CP.PD_GB = 'D' THEN DATE_FORMAT(NOW(), '%Y.%m.%d %H:%i')
		            ELSE DATE_FORMAT(CP.AVAIL_STDT, '%Y.%m.%d %H:%i')              END AS AVAIL_STDT_TIME
		     , CASE WHEN CP.PD_GB = 'D' THEN DATE_FORMAT(CONCAT(CURRENT_DATE + INTERVAL CP.AVAIL_DAYS DAY, ' 23:59:59'), '%Y.%m.%d %H:%i')
		            ELSE  DATE_FORMAT(CP.AVAIL_EDDT, '%Y.%m.%d %H:%i')             END AS AVAIL_EDDT_TIME
		     , CP.MAX_DC_AMT
		     , CONCAT(CASE WHEN CP.BUY_LIMIT_AMT = 0 THEN ''
		                    ELSE CONCAT(FORMAT(CP.BUY_LIMIT_AMT , 0),'원 이상 구매 시 ')
		              END
		             ,CASE WHEN CP.MAX_DC_AMT = 0 THEN ''
		                   ELSE CONCAT('최대 ',FORMAT(CP.MAX_DC_AMT , 0),'원 할인')
		              END
		             )                                                                     AS USE_CONDITION
		     , CASE WHEN CP.CUST_PUB_LIMIT_QTY = 0 THEN ''
		            ELSE CONCAT('1인당 최대',CP.CUST_PUB_LIMIT_QTY,'매')
		       END                                                                         AS ISSUE_CONDITION
		    , CONCAT(CR.REF_VAL, CASE WHEN CR.CPN_TARGET = 'G260_10' AND CR.CNT > 1 THEN ' 외'
		                               WHEN CR.CPN_TARGET = 'G260_11' AND CR.CNT > 1 THEN ' 외'
		                               WHEN CR.CPN_TARGET = 'G260_12' AND CR.CNT > 2 THEN ' 외'
		                               WHEN CR.CPN_TARGET = 'G260_13' AND CR.CNT > 1 THEN ' 외'
		                               ELSE ''
		                          END) AS TGT_CONDITION /*대상조건*/
		FROM  TB_COUPON CP
		LEFT OUTER JOIN
		      TAB_COUPON_REFVAL2 CR
		ON    CP.CPN_ID = CR.CPN_ID
		WHERE CP.CPN_ID = #{cpnId}
		AND   CP.SITE_CD = #{siteCd}
		AND   CP.CPN_STAT = 'G232_11'
	</select>
	
	<select id="getSendCouponExpectList" parameterType="Coupon" resultType="Coupon">
		/*TsbCoupon.getSendCouponExpectList*/
		SELECT  C.CUST_NO 
		      , C.CUST_NM 
		      , C.CELL_PHNNO
		      , CC.CPN_ID 
		      , TC.CPN_NM 
		      , TC.DC_AVAL
		      , TC.PD_GB 
		      , TC.DC_MVAL                                                                     AS DC_VAL
		      , CASE WHEN TC.DC_WAY = 'G240_10' THEN '원'
		            ELSE '%'
		       END                                                                             AS DC_WAY
		      , DATE_FORMAT(CC.AVAIL_EDDT , '%Y.%m.%d') AS AVAIL_EDDT
		FROM TB_CUST_COUPON CC INNER JOIN TB_CUSTOMER C 
		                  ON CC.CUST_NO = C.CUST_NO
		                       INNER JOIN TB_COUPON TC
		                  ON CC.CPN_ID = TC.CPN_ID 
		WHERE CC.USED_DT IS NULL
		AND CC.AVAIL_EDDT BETWEEN CONCAT(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY),'%Y-%m-%d'), ' 00:00:00') AND CONCAT(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY),'%Y-%m-%d'), ' 23:59:59')
		AND C.CUST_STAT = 'G104_10' /*활동회원 만 */
		AND TC.CPN_STAT = 'G232_11'
		AND TC.END_ALIM_YN = 'Y'
	</select>


</mapper>