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

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

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>

	<!-- 시리얼 쿠폰 지급 정보 조회 -->
	<select id="getSerialCpnUseInfo" parameterType="Coupon" resultType="Coupon">
		/* TsfCoupon.getSerialCpnUseInfo */
		SELECT CC.CPN_ID
		     , DATE_FORMAT(CC.USED_DT, '%Y-%m-%d') AS USED_DT
		     , CASE WHEN NOW() BETWEEN CC.AVAIL_STDT AND CC.AVAIL_EDDT THEN 'Y'
		            ELSE 'N' END AS AVAIL_YN
		     , DATE_FORMAT(CC.AVAIL_STDT, '%Y.%m.%d') AS AVAIL_STDT
		     , DATE_FORMAT(CC.AVAIL_EDDT, '%Y.%m.%d') AS AVAIL_EDDT
		     , CP.BUY_LIMIT_AMT
		     , CP.MAX_DC_AMT
		     , CP.CPN_NM
		     , CP.CUST_PUB_LIMIT_QTY
		     , CP.APPLY_SCOPE
		     , RC.RD_CPN_NM
		     , CASE WHEN #{rdCpnNm} = RC.RD_CPN_NM THEN 1 ELSE 0 END AS CNT
		     , DATE_FORMAT(CC.AVAIL_STDT, '%Y.%m.%d %H:%i') AS AVAIL_STDT_TIME
		     , DATE_FORMAT(CC.AVAIL_EDDT, '%Y.%m.%d %H:%i') AS AVAIL_EDDT_TIME
		FROM   TB_COUPON CP
		INNER  JOIN TB_CUST_COUPON CC
		ON     CC.CPN_ID = CP.CPN_ID
		LEFT   OUTER JOIN TB_RANDOM_COUPON RC
		ON     RC.CPN_ID = CC.CPN_ID
		AND    CC.CUST_NO = RC.CUST_NO
		WHERE  1=1
		AND    CC.CPN_ID = #{cpnId}
		AND    CC.CUST_NO = #{custNo}
	</select>

	<!-- 쿠폰 적용 대상조건명 및 조건별 수량 조회 -->
	<select id="getCpnRefName" parameterType="Coupon" resultType="Coupon">
		/* TsfCoupon.getCpnRefName */
		SELECT A.CPN_ID
		     , A.CPN_TARGET
		     , MIN(REF_VAL) AS REF_VAL
		     , MAX(REF_VAL) AS SECOND_REF_VAL
		     , COUNT(A.CPN_ID) AS CNT
		FROM   (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 (SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE1_NO = CR.REF_VAL) > 0 THEN (SELECT CATE1_NM FROM TB_CATE_4SRCH WHERE CATE1_NO = CR.REF_VAL LIMIT 1)
		                                                             WHEN (SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE2_NO = CR.REF_VAL) > 0 THEN (SELECT CONCAT(CATE1_NM, ' > ', CATE2_NM) FROM TB_CATE_4SRCH WHERE CATE2_NO = CR.REF_VAL LIMIT 1)
		                                                             WHEN (SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE3_NO = CR.REF_VAL) > 0 THEN (SELECT CONCAT(CATE1_NM, ' > ', CATE2_NM) FROM TB_CATE_4SRCH WHERE CATE3_NO = CR.REF_VAL LIMIT 1)
		                                                             WHEN (SELECT COUNT(1) FROM TB_CATE_4SRCH WHERE CATE4_NO = CR.REF_VAL) > 0 THEN (SELECT CONCAT(CATE1_NM, ' > ', CATE2_NM) FROM TB_CATE_4SRCH WHERE CATE4_NO = CR.REF_VAL LIMIT 1)
		                                                             END    <!-- 카테고리명은 대 > 중 카테고리까지만 노출 20210330 정지혜과장님 확인 -->
		                    WHEN CR.CPN_TARGET = 'G260_12' THEN (SELECT (CASE WHEN D.DISP_NM_LANG = 'EN' THEN D.BRAND_GROUP_ENM ELSE D.BRAND_GROUP_KNM END) FROM TB_BRAND BR INNER JOIN TB_BRAND_GROUP D ON BR.BRAND_GROUP_NO = D.BRAND_GROUP_NO AND D.USE_YN = 'Y' WHERE BR.BRAND_CD = CR.REF_VAL AND BR.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
		        FROM   TB_COUPON_REFVAL CR
		        WHERE  1=1
		        AND    CPN_TARGET != 'G260_14'
		        AND    CPN_ID = #{cpnId}) A
		GROUP  BY A.CPN_ID
		        , A.CPN_TARGET
	</select>

	<!-- 입력된 시리얼 쿠폰 정보 조회 -->
	<select id="getSearchSerialCpnInfo" parameterType="Coupon" resultType="Coupon">
		/* TsfCoupon.getSearchSerialCpnInfo : 입력된 시리얼 쿠폰 정보 조회 */
		SELECT CP.CPN_ID
			 , CP.CPN_NM
		     , CP.CPN_DESC
		     , CP.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
			 , RC.RD_CPN_NM
		     , RC.RD_CPN_ID
			 , 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(DATE_ADD(NOW(), INTERVAL CP.AVAIL_DAYS DAY), '%Y%m%d%H%i%S')
			     	ELSE  DATE_FORMAT(CP.AVAIL_EDDT, '%Y%m%d%H%i%S') END AS AVAIL_EDDT
		FROM   TB_COUPON CP
		INNER  JOIN TB_RANDOM_COUPON RC
		ON     CP.CPN_ID = RC.CPN_ID
		WHERE  1=1
		AND    RC.RD_CPN_NM = #{rdCpnNm}
		AND    NOW() BETWEEN CP.DOWN_STDT AND CP.DOWN_EDDT
		AND    CP.CPN_STAT = 'G232_11'
		AND    RC.CUST_NO IS NULL
		ORDER  BY RC.CPN_ID
		<!-- 랜덤쿠폰은 RD_CPN_NM이 1개이나, 시리얼 쿠폰은 다수개이므로 하나의 정보만 조회 -->
		LIMIT  1
	</select>
	
	<!-- 마이페이지 등급쿠폰 다운가능 쿠폰 정보 조회 -->
	<select id="getCustGradeCouponInfo" parameterType="Coupon" resultType="Coupon">
		/* TsfCoupon.getCustGradeCouponInfo */
		SELECT Z.CUST_NO
		     , Z.CPN_ID
		     , Z.AVAIL_STDT
		     , Z.AVAIL_EDDT
		     , Z.END_ALIM_YN
		     , Z.DOWNLOAD_CNT
		  FROM (SELECT A.*
					 , CASE WHEN A.CNT <![CDATA[<=]]> 0 THEN 0
							WHEN A.CNT > (A.DN_ABLE_CNT * A.ONE_PUB_QTY) THEN (A.DN_ABLE_CNT * A.ONE_PUB_QTY)
							ELSE A.CNT
						END AS DOWNLOAD_CNT
				  FROM (SELECT #{custNo} AS CUST_NO
							 , C.CPN_ID
							 , C.CPN_NM
							 , C.CUST_PUB_LIMIT_QTY
							 , C.ONE_PUB_QTY
							 , IF(C.PD_GB = 'P', C.AVAIL_STDT, NOW()) AS AVAIL_STDT
							 , IF(C.PD_GB = 'P', C.AVAIL_EDDT, CONCAT(CURRENT_DATE + INTERVAL C.AVAIL_DAYS DAY, ' 23:59:59')) AS AVAIL_EDDT
				             , C.END_ALIM_YN
							 , IFNULL(CC.CPN_CNT, 0) AS DN_CNT
				             , IF(C.CUST_PUB_LIMIT_QTY = 0 OR C.CUST_PUB_LIMIT_QTY - CC.CPN_CNT > 0, 1, 0) AS DN_ABLE_CNT
				             , IF(C.TOT_PUB_LIMIT_QTY > 0, C.TOT_PUB_LIMIT_QTY - CC2.CPN_CNT, 99999) AS CNT
						  FROM TB_COUPON C
						 INNER JOIN TB_COUPON_CUST_GBN CCGB
							ON C.CPN_ID = CCGB.CPN_ID
						   AND CCGB.USABLE_CUST_GB IN ('G100_00', IFNULL(#{custGb}, 'G100_00'))
						 INNER JOIN TB_COUPON_CUST_GRADE CCGR
							ON C.CPN_ID = CCGR.CPN_ID
						   AND CCGR.USABLE_CUST_GRADE = #{custGrade}
						  LEFT OUTER JOIN (SELECT CPN_ID
												, CUST_NO
												, COUNT(*) AS CPN_CNT
											 FROM TB_CUST_COUPON
											GROUP BY CPN_ID, CUST_NO) CC
							ON C.CPN_ID = CC.CPN_ID
						   AND CC.CUST_NO = #{custNo}
						  LEFT OUTER JOIN (SELECT CPN_ID
												, COUNT(*) AS CPN_CNT
											 FROM TB_CUST_COUPON
											GROUP BY CPN_ID) CC2
							ON C.CPN_ID = CC2.CPN_ID
						 WHERE C.DOWN_ABL_YN = 'Y'
						   AND NOW() BETWEEN C.DOWN_STDT AND C.DOWN_EDDT
						   AND C.CPN_STAT = 'G232_11'  -- 진행
						   AND CASE WHEN C.TOT_PUB_LIMIT_QTY = 0 THEN 1
									WHEN C.TOT_PUB_LIMIT_QTY - CC2.CPN_CNT > 0 THEN 1
									ELSE 0
								END = 1
						   AND IF(C.NEW_CUST_YN = 'Y', (SELECT COUNT(*) FROM TB_CUSTOMER WHERE CUST_NO = #{custNo} AND REG_DT BETWEEN C.CUST_JOIN_STDT AND C.CUST_JOIN_EDDT), 1) = 1
						   AND IF(C.FIRST_ORD_YN = 'Y', (SELECT COUNT(*) FROM TB_ORDER WHERE CUST_NO = #{custNo} AND ORD_DT BETWEEN C.BUY_STDT AND C.BUY_EDDT), 0) = 0
						) A
				) Z
		 WHERE Z.DOWNLOAD_CNT > 0
	</select>

	<!--회원가입 쿠폰-->
	<select id="getJoinCoupon" parameterType="Coupon" resultType="Coupon">
		/* TsfCoupon.getJoinCoupon */
		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 = (
		                        SELECT GRADE_CPN_ID1
		                        FROM   TB_CUST_GRADE_POLICY
		                        WHERE  GRADE_CD = #{custGrade}
		                        AND    USE_YN = 'Y'
                                )
		)
		, 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 #{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.DC_WAY = 'G240_10' THEN '원'
		            ELSE '%'
		       END                                                                   AS DC_WAY
		     , 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.CPN_NM
		     , 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_CUST_GRADE_POLICY CGP
		INNER JOIN
		      TB_COUPON CP
		ON    CGP.GRADE_CPN_ID1 = CP.CPN_ID
		LEFT OUTER JOIN
		      TAB_COUPON_REFVAL2 CR
		ON    CP.CPN_ID = CR.CPN_ID
		WHERE CGP.GRADE_CD = #{custGrade}
		AND   CP.SITE_CD = #{siteCd}
		AND   CGP.USE_YN = 'Y'
	</select>
	
	<!--상품 쿠폰 조회-->
	<select id="getGoodsCouponList" parameterType="Goods" resultType="Coupon">
		/* TsfCoupon.getGoodsCouponList */
		SELECT A.DC_AMT
		     , A.DC_VAL
		     , A.DC_WAY
		     , A.CPN_ID
		     , A.CPN_NM
		     , A.CUST_PUB_LIMIT_QTY
		     , A.TOT_PUB_LIMIT_QTY
		     , A.BUY_LIMIT_AMT
		     , A.ONE_PUB_QTY
		     , A.MAX_DC_AMT
		     , A.CPN_TYPE
		     , A.AVAIL_STDT
		     , A.AVAIL_EDDT
		     , A.USABLE_CUST_GRADE
		     , A.NEW_CUST_YN
		     , A.CUST_JOIN_STDT 
		     , A.CUST_JOIN_EDDT
		     , A.FIRST_ORD_YN
		     , A.BUY_STDT
		     , A.BUY_EDDT
		     , GOODS_CD
		    -- , A.CURR_PRICE
		     , DATE_FORMAT(A.AVAIL_EDDT, '%Y-%m-%d') AS AVAIL_EDDT
		     , IFNULL((SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = A.CPN_ID AND CUST_NO = #{custNo}),0) AS CUST_COUPON_CNT
		     , (SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = A.CPN_ID) AS TOT_COUPON_CNT
		     , RNUM
		FROM (
		SELECT  DISTINCT CPN_ID
		       , CPN_NM
		       , DC_AMT
		       , DC_VAL
		       , DC_WAY
		       , CUST_PUB_LIMIT_QTY
		       , TOT_PUB_LIMIT_QTY
		       , BUY_LIMIT_AMT
		       , ONE_PUB_QTY
		       , MAX_DC_AMT
		       , CPN_TYPE
		       , AVAIL_STDT
		       , AVAIL_EDDT
		       , USABLE_CUST_GRADE
		       , NEW_CUST_YN
		       , CUST_JOIN_STDT 
		       , CUST_JOIN_EDDT
		       , FIRST_ORD_YN
		       , BUY_STDT
		       , BUY_EDDT
		       , GOODS_CD
		      -- , CURR_PRICE
		       , RANK() OVER(ORDER BY DC_AMT DESC)  AS RNUM   -- 할인금액순
		FROM   (
		        SELECT CP.CPN_ID                                              -- 쿠폰ID
		             , CP.CPN_NM                                              -- 쿠폰명
		             , CP.DC_WAY                                              -- 할인방식
		             , CP.DC_VAL                                              -- 할인값
		             , (CASE WHEN CP.DC_WAY = 'G240_10' THEN CP.DC_VAL
		                     ELSE CP.MAX_DC_AMT END) AS MAX_DC_AMT            -- 최대할인금액
		             , CP.CURR_PRICE                                          -- 즉시쿠폰적용판매가
		             , CP.CUST_PUB_LIMIT_QTY                                  -- 1인당 발급제한수량
		             , CP.TOT_PUB_LIMIT_QTY                                   -- 총발행제한수량(0은 무제한)
		             , CP.ONE_PUB_QTY                                         -- 1회발행수량
		             , CP.BUY_LIMIT_AMT                                       -- 구매제한금액
		             , CP.CPN_TYPE                                            -- 쿠폰타입
		             , CP.AVAIL_STDT                                          -- 유효시작일시
		             , CP.AVAIL_EDDT                                          -- 유효종료일시
		             , CP.USABLE_CUST_GRADE                                   -- 고객등급(,구분)
		             , CP.NEW_CUST_YN                                         -- 신규회원여부
		             , CP.CUST_JOIN_STDT                                      -- 회원가입시작일시(신규회원여부) 
		             , CP.CUST_JOIN_EDDT                                      -- 회원가입종료일시(신규회원여부)
		             , CP.FIRST_ORD_YN                                        -- 첫구매여부
		             , CP.BUY_STDT                                            -- 첫구매시작일시
		             , CP.BUY_EDDT                                            -- 첫구매종료일시
		             , CP.GOODS_CD                                            -- 상품코드
		             <![CDATA[
		             , (CASE WHEN CP.DC_WAY = 'G240_10' THEN
		           --         (CASE WHEN IFNULL(CP.MAX_DC_AMT, 0) >0 AND CP.MAX_DC_AMT < CP.DC_VAL THEN CP.MAX_DC_AMT ELSE CP.DC_VAL END)
		                       CP.DC_VAL
		                ELSE
		                     (CASE WHEN IFNULL(CP.MAX_DC_AMT, 0) >0 AND CP.MAX_DC_AMT < FLOOR((CP.CURR_PRICE * DC_VAL / 100) / #{pointUnit}) * #{pointUnit} THEN CP.MAX_DC_AMT ELSE FLOOR((CP.CURR_PRICE * CP.DC_VAL / 100) / #{pointUnit}) * #{pointUnit} END)
		                END) AS DC_AMT
		              ]]>
		        FROM   (
		                SELECT CP.CPN_ID
		                     , CP.CPN_NM
		                     , CP.DC_WAY
		                     , (CASE WHEN 'P' = #{frontGb} THEN CP.DC_PVAL
		                             WHEN 'M' = #{frontGb} THEN CP.DC_MVAL
		                             ELSE CP.DC_AVAL END) AS DC_VAL
		                     , CP.MAX_DC_AMT
		                     , CP.BUY_LIMIT_AMT
		                     , CP.CUST_PUB_LIMIT_QTY
		                     , CP.TOT_PUB_LIMIT_QTY
		                     , CP.CPN_TYPE
		                     , CP.NEW_CUST_YN
		                     , CP.ONE_PUB_QTY
		                     , DATE_FORMAT(CP.CUST_JOIN_STDT, '%Y%m%d%H%i%S') AS CUST_JOIN_STDT
		                     , DATE_FORMAT(CP.CUST_JOIN_EDDT, '%Y%m%d%H%i%S') AS CUST_JOIN_EDDT
		                     , CP.FIRST_ORD_YN
		                     , DATE_FORMAT(CP.BUY_STDT, '%Y%m%d%H%i%S') AS BUY_STDT
		                     , DATE_FORMAT(CP.BUY_EDDT, '%Y%m%d%H%i%S') AS BUY_EDDT
		                     , IF (CP.PD_GB = 'D', NOW(), CP.AVAIL_STDT) AS AVAIL_STDT
		                     , IF (CP.PD_GB = 'D', CONCAT(CURRENT_DATE + INTERVAL Cp.AVAIL_DAYS DAY, ' 23:59:59'), CP.AVAIL_EDDT) AS AVAIL_EDDT
		                     , (SELECT GROUP_CONCAT(USABLE_CUST_GRADE) FROM TB_COUPON_CUST_GRADE WHERE CPN_ID = CP.CPN_ID) AS USABLE_CUST_GRADE
		                     , G.GOODS_CD
		                     , FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD, G.CURR_PRICE,#{custGb}) AS CURR_PRICE
		                FROM   TB_GOODS G
		                     , TB_COUPON CP
		                WHERE 1 = 1
		                <choose>
		                    <when test="arrGoodsCd != null and arrGoodsCd != ''">
		                AND G.GOODS_CD IN
		                    <foreach collection="arrGoodsCd" item="item" index="index"  open="(" close=")" separator=",">
		                    UPPER(#{item})
		                    </foreach>
		                    </when>
		                    <otherwise>
		                AND G.GOODS_CD = #{goodsCd}
		                    </otherwise>
		                </choose> 
		                <if test="cpnId != null and cpnId != ''">
		                AND    CP.CPN_ID = #{cpnId}
		                </if>
		                <if test="arrCpnId != null and arrCpnId != ''">
		                AND    CP.CPN_ID IN
		                <foreach collection="arrCpnId" item="item" index="index"  open="(" close=")" separator=",">
		                UPPER(#{item})
		                </foreach>
		                </if>
		                AND    CP.SITE_CD = #{siteCd}
		                AND    CP.CPN_STAT = 'G232_11'   -- 쿠폰인 진행중인 쿠폰만
		                <![CDATA[
		                AND    NOW() <= IF (CP.PD_GB = 'D', CONCAT(CURRENT_DATE + INTERVAL CP.AVAIL_DAYS DAY, ' 23:59:59'), CP.AVAIL_EDDT)
		                ]]>
		                AND    NOW() BETWEEN CP.DOWN_STDT AND CP.DOWN_EDDT
		                AND    CP.DOWN_ABL_YN = 'Y'                            -- 다운 가능 쿠폰 
		                AND    CP.CPN_TYPE IN ('G230_11','G230_20','G230_30')  --  상품쿠폰, 주문서쿠폰, 배송비쿠폰
		                AND    CP.DC_CD_GB = 'G233_00'                         -- 일반유형(할인쿠폰구분)
		                AND    (CASE WHEN 'P' = #{frontGb} THEN CP.DC_PVAL
		                             WHEN 'M' = #{frontGb} THEN CP.DC_MVAL
		                             ELSE CP.DC_AVAL END) > 0                  -- PC, MOBILE,APP 별로 0 보다 큰 쿠폰
		                AND    (SELECT COUNT(1)
		                        FROM TB_COUPON_CUST_GBN
		                        WHERE CPN_ID = CP.CPN_ID
		                        AND USABLE_CUST_GB IN (#{custGb})              -- 사용가능고객구분
		                       ) >= 1
		                AND    IF (CP.TOT_PUB_LIMIT_QTY = 0, 9999999999,CP.TOT_PUB_LIMIT_QTY) > (SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = CP.CPN_ID) -- 총발행제한수
		                AND   (
		                        (CP.APPLY_SCOPE = 'A' ) 
		                        OR  /* 적용대상:상품*/
		                        (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) FROM TB_COUPON_REFVAL
		                                                   WHERE CPN_ID = CP.CPN_ID
		                                                   AND CPN_TARGET = 'G260_10'
		                                                   AND DEL_YN = 'N'
		                                                   AND REF_VAL = G.GOODS_CD
		                                                   ) > 0
		                        ) 
		                        OR /* 적용대상:카테고리*/
		                        (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) FROM TB_COUPON_REFVAL
		                                                   WHERE CPN_ID = CP.CPN_ID
		                                                   AND CPN_TARGET = 'G260_11' 
		                                                   AND DEL_YN = 'N'
		                                                   AND IFNULL(REF_FORMAL_GB,G.FORMAL_GB) = G.FORMAL_GB
		                                                   AND IFNULL(REF_BRAND_CD,G.BRAND_CD) = G.BRAND_CD
		                                                   AND REF_VAL IN (SELECT DISTINCT X.CATE_NO 
		                                                                   FROM 
		                                                                        (SELECT LEAF_CATE_NO 
		                                                                              , CATE_LVL 
		                                                                              , CATE_NO
		                                                                         FROM (
		                                                                               SELECT LEAF_CATE_NO ,
		                                                                                      CASE WHEN X = 1 THEN 'CATE1_NO'  -- 컬럼과 매핑(컬럼명)
		                                                                                           WHEN X = 2 THEN 'CATE2_NO'
		                                                                                           WHEN X = 3 THEN 'CATE3_NO'
		                                                                                           WHEN X = 4 THEN 'CATE4_NO'
		                                                                                           ELSE  'CATE5_NO' 
		                                                                                      END CATE_LVL,
		                                                                                      CASE WHEN X = 1 THEN CATE1_NO  -- 컬럼과 매핑(컬럼 데이터)
		                                                                                           WHEN X = 2 THEN CATE2_NO
		                                                                                           WHEN X = 3 THEN CATE3_NO
		                                                                                           WHEN X = 4 THEN CATE4_NO
		                                                                                           ELSE  CATE5_NO 
		                                                                                      END CATE_NO
		                                                                               FROM (
		                                                                                      ( SELECT LEAF_CATE_NO
		                                                                                             , CATE1_NO
		                                                                                             , CATE2_NO
		                                                                                             , CATE3_NO
		                                                                                             , CATE4_NO
		                                                                                             , CATE5_NO
		                                                                                        FROM  TB_CATE_4SRCH
		                                                                                        WHERE SITE_CD = 'G000_10'
		                                                                                        AND CATE_TYPE = 'G031_10') A, -- 상품타입
		                                                                                      (SELECT 1 AS X                  -- UNPIVOT 컬럼 수 만큼 선언
		                                                                                       UNION ALL SELECT 2 AS X
		                                                                                       UNION ALL SELECT 3 AS X
		                                                                                       UNION ALL SELECT 4 AS X
		                                                                                       UNION ALL SELECT 5 AS X
		                                                                                     ) B
		                                                                                    )  
		                                                                               ORDER BY LEAF_CATE_NO, CATE_LVL
		                                                                              ) K
		                                                                              WHERE CATE_NO IS NOT NULL
		                                                                        ) X, 
		                                                                        (
		                                                                         SELECT A.CATE_NO, A.GOODS_CD
		                                                                         FROM TB_CATE_GOODS A
		                                                                         WHERE GOODS_CD = G.GOODS_CD
		                                                                        ) Y 
		                                                                        WHERE X.LEAF_CATE_NO = Y.CATE_NO
		                                                                  )
		                                                  ) >0
		                        ) 
		                        /* 2021.08.23 적용대상 : 브랜드, 공급업체 */
		                        OR 
		                        (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) 
		                                                   FROM   TB_COUPON_REFVAL CR1
		                                                   INNER  JOIN TB_COUPON_REFVAL CR2
		                                                   ON     1=1
		                                                   AND    CR2.CPN_ID = CP.CPN_ID
		                                                   AND    CR2.CPN_TARGET = 'G260_13' 
		                                                   AND    CR2.DEL_YN = 'N'
		                                                   AND    CR2.REF_VAL = G.SUPPLY_COMP_CD
		                                                   AND    IFNULL(CR2.REF_FORMAL_GB, G.FORMAL_GB) = G.FORMAL_GB
		                                                   WHERE  1=1
		                                                   AND    CR1.CPN_ID = CP.CPN_ID
		                                                   AND    CR1.CPN_TARGET = 'G260_12' 
		                                                   AND    CR1.DEL_YN = 'N'
		                                                   AND    CR1.REF_VAL = G.BRAND_CD
		                                                   AND    IFNULL(CR1.REF_FORMAL_GB, G.FORMAL_GB) = G.FORMAL_GB
		                                                  ) > 0
		                        )
		                      )
		                AND NOT EXISTS (
		                                SELECT 1
		                                FROM   TB_COUPON_REFVAL SCPR
		                                WHERE  SCPR.CPN_ID = CP.CPN_ID
		                                AND    SCPR.REF_VAL = G.GOODS_CD
		                                AND    SCPR.DEL_YN = 'N'      -- 삭제안된넘
		                                AND    SCPR.CPN_TARGET = 'G260_14' -- 제외상품
		                               )
		                AND NOT EXISTS (
		                                 SELECT 1
		                                FROM TB_COUPON_BAN_GOODS
		                                WHERE GOODS_CD = G.GOODS_CD
		                                AND DEL_YN = 'N'
		                               )
		               ) CP
		               WHERE 1 = 1
		               <![CDATA[
		              -- AND CP.BUY_LIMIT_AMT <= CP.CURR_PRICE  -- 최소주문금액
		               ]]>
		       ) Y
		) A
		WHERE 1 = 1
		<if test="maxRownum != null and maxRownum > 0">
		LIMIT #{maxRownum}
		</if> 
	</select>
	
	<!--EP 폰 조회-->
	<select id="getGoodsEpCouponList" parameterType="Goods" resultType="Coupon">
		/* TsfCoupon.getGoodsEpCouponList */
		SELECT A.DC_AMT
		     , A.DC_VAL
		     , A.DC_WAY
		     , A.CPN_ID
		     , A.CPN_NM
		     , A.CUST_PUB_LIMIT_QTY
		     , A.TOT_PUB_LIMIT_QTY
		     , A.BUY_LIMIT_AMT
		     , A.MAX_DC_AMT
		     , A.CPN_TYPE
		     , A.AVAIL_STDT
		    , A.AVAIL_EDDT
		     , A.USABLE_CUST_GRADE
		     , A.NEW_CUST_YN
		     , A.CUST_JOIN_STDT 
		     , A.CUST_JOIN_EDDT
		     , A.FIRST_ORD_YN
		     , A.BUY_STDT
		     , A.BUY_EDDT
		     , A.CURR_PRICE
		     -- , DATE_FORMAT(A.AVAIL_EDDT, '%Y-%m-%d') AS AVAIL_EDDT
		     , IFNULL((SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = A.CPN_ID AND CUST_NO = #{custNo}),0) AS CUST_COUPON_CNT
		     , (SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = A.CPN_ID) AS TOT_COUPON_CNT
		     , RNUM
		FROM (
		SELECT   CPN_ID
		       , CPN_NM
		       , DC_AMT
		       , DC_VAL
		       , DC_WAY
		       , CUST_PUB_LIMIT_QTY
		       , TOT_PUB_LIMIT_QTY
		       , BUY_LIMIT_AMT
		       , MAX_DC_AMT
		       , CPN_TYPE
		       , AVAIL_STDT
		       , AVAIL_EDDT
		       , USABLE_CUST_GRADE
		       , NEW_CUST_YN
		       , CUST_JOIN_STDT 
		       , CUST_JOIN_EDDT
		       , FIRST_ORD_YN
		       , BUY_STDT
		       , BUY_EDDT
		       , CURR_PRICE
		       , RANK() OVER(ORDER BY DC_AMT DESC)  AS RNUM   -- 할인금액순
		FROM   (
		        SELECT CP.CPN_ID                                              -- 쿠폰ID
		             , CP.CPN_NM                                              -- 쿠폰명
		             , CP.DC_WAY                                              -- 할인방식
		             , CP.DC_VAL                                              -- 할인값
		             , (CASE WHEN CP.DC_WAY = 'G240_10' THEN CP.DC_VAL
		                     ELSE CP.MAX_DC_AMT END) AS MAX_DC_AMT            -- 최대할인금액
		             , CP.CURR_PRICE                                          -- 즉시쿠폰적용판매가
		             , CP.CUST_PUB_LIMIT_QTY                                  -- 1인당 발급제한수량
		             , CP.TOT_PUB_LIMIT_QTY                                   -- 총발행제한수량(0은 무제한)
		             , CP.BUY_LIMIT_AMT                                       -- 구매제한금액
		             , CP.CPN_TYPE                                            -- 쿠폰타입
		             , CP.AVAIL_STDT                                          -- 유효시작일시
		             , CP.AVAIL_EDDT                                          -- 유효종료일시
		             , CP.USABLE_CUST_GRADE                                   -- 고객등급(,구분)
		             , CP.NEW_CUST_YN                                         -- 신규회원여부
		             , CP.CUST_JOIN_STDT                                      -- 회원가입시작일시(신규회원여부) 
		             , CP.CUST_JOIN_EDDT                                      -- 회원가입종료일시(신규회원여부)
		             , CP.FIRST_ORD_YN                                        -- 첫구매여부
		             , CP.BUY_STDT                                            -- 첫구매시작일시
		             , CP.BUY_EDDT                                            -- 첫구매종료일시
		             <![CDATA[
		             , (CASE WHEN CP.DC_WAY = 'G240_10' THEN
		             --       (CASE WHEN IFNULL(CP.MAX_DC_AMT, 0) >0 AND CP.MAX_DC_AMT < CP.DC_VAL THEN CP.MAX_DC_AMT ELSE CP.DC_VAL END)
		                     CP.DC_VAL
		                ELSE
		                     (CASE WHEN IFNULL(CP.MAX_DC_AMT, 0) >0 AND CP.MAX_DC_AMT < FLOOR((CP.CURR_PRICE * DC_VAL / 100) / #{pointUnit}) * #{pointUnit} THEN CP.MAX_DC_AMT ELSE FLOOR((CP.CURR_PRICE * CP.DC_VAL / 100) / #{pointUnit}) * #{pointUnit} END)
		                END) AS DC_AMT
		              ]]>
		        FROM   (
		                SELECT CP.CPN_ID
		                     , CP.CPN_NM
		                     , CP.DC_WAY
		                     , (CASE WHEN 'P' = #{frontGb} THEN CP.DC_PVAL
		                             WHEN 'M' = #{frontGb} THEN CP.DC_MVAL
		                             ELSE CP.DC_AVAL END) AS DC_VAL
		                     , CP.MAX_DC_AMT
		                     , CP.BUY_LIMIT_AMT
		                     , CP.CUST_PUB_LIMIT_QTY
		                     , CP.TOT_PUB_LIMIT_QTY
		                     , CP.CPN_TYPE
		                     , CP.NEW_CUST_YN
		                     , DATE_FORMAT(CP.CUST_JOIN_STDT, '%Y%m%d%H%i%S') AS CUST_JOIN_STDT
		                     , DATE_FORMAT(CP.CUST_JOIN_EDDT, '%Y%m%d%H%i%S') AS CUST_JOIN_EDDT
		                     , CP.FIRST_ORD_YN
		                     , DATE_FORMAT(CP.BUY_STDT, '%Y%m%d%H%i%S') AS BUY_STDT
		                     , DATE_FORMAT(CP.BUY_EDDT, '%Y%m%d%H%i%S') AS BUY_EDDT
		                     , IF (CP.PD_GB = 'D', NOW(), CP.AVAIL_STDT) AS AVAIL_STDT
		                     , IF (CP.PD_GB = 'D', CONCAT(CURRENT_DATE + INTERVAL Cp.AVAIL_DAYS DAY, ' 23:59:59'), CP.AVAIL_EDDT) AS AVAIL_EDDT
		                     , (SELECT GROUP_CONCAT(USABLE_CUST_GRADE) FROM TB_COUPON_CUST_GRADE WHERE CPN_ID = CP.CPN_ID) AS USABLE_CUST_GRADE
		                     , G.GOODS_CD
		                     , FN_GET_BENEFIT_PRICE(#{frontGb},G.GOODS_CD, G.CURR_PRICE,#{custGb}) AS CURR_PRICE
		                FROM   TB_GOODS G
		                     , TB_COUPON CP
		                     , TB_COUPON_AF_CHANNEL CA
		                WHERE 1 = 1
		                AND CA.AF_LINK_CD = #{afLinkCd}	-- 제휴코드 
		                AND CA.CPN_ID = CP.CPN_ID
		                <choose>
		                    <when test="arrGoodsCd != null and arrGoodsCd != ''">
		                AND G.GOODS_CD IN
		                    <foreach collection="arrGoodsCd" item="item" index="index"  open="(" close=")" separator=",">
		                    UPPER(#{item})
		                    </foreach>
		                    </when>
		                    <otherwise>
		                AND G.GOODS_CD = #{goodsCd}
		                    </otherwise>
		                </choose> 
		                <if test="cpnId != null and cpnId != ''">
		                AND    CP.CPN_ID = #{cpnId}
		                </if>
		                <if test="arrCpnId != null and arrCpnId != ''">
		                AND    CP.CPN_ID IN
		                <foreach collection="arrCpnId" item="item" index="index"  open="(" close=")" separator=",">
		                UPPER(#{item})
		                </foreach>
		                </if>
		                AND    CP.SITE_CD = #{siteCd}
		                AND    CP.CPN_STAT = 'G232_11'   -- 쿠폰인 진행중인 쿠폰만
		                <![CDATA[
		                AND    NOW() <= IF (CP.PD_GB = 'D', CONCAT(CURRENT_DATE + INTERVAL CP.AVAIL_DAYS DAY, ' 23:59:59'), CP.AVAIL_EDDT)
		                ]]>
		                AND    NOW() BETWEEN CP.DOWN_STDT AND CP.DOWN_EDDT
		                AND    CP.CPN_TYPE IN ('G230_11','G230_20','G230_30')  --  상품쿠폰, 주문서쿠폰, 배송비쿠폰
		                AND    CP.DC_CD_GB = 'G233_00'                         -- 일반유형(할인쿠폰구분)
		                AND    (CASE WHEN 'P' = #{frontGb} THEN CP.DC_PVAL
		                             WHEN 'M' = #{frontGb} THEN CP.DC_MVAL
		                             ELSE CP.DC_AVAL END) > 0                  -- PC, MOBILE,APP 별로 0 보다 큰 쿠폰
		                AND    (SELECT COUNT(1)
		                        FROM TB_COUPON_CUST_GBN
		                        WHERE CPN_ID = CP.CPN_ID
		                        AND USABLE_CUST_GB IN (#{custGb})              -- 사용가능고객구분
		                       ) >= 1
		                AND    IF (CP.TOT_PUB_LIMIT_QTY = 0, 9999999999,CP.TOT_PUB_LIMIT_QTY) > (SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = CP.CPN_ID) -- 총발행제한수
		                AND   (
		                        (CP.APPLY_SCOPE = 'A' ) 
		                        OR  /* 적용대상:상품*/
		                        (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) FROM TB_COUPON_REFVAL
		                                                   WHERE CPN_ID = CP.CPN_ID
		                                                   AND CPN_TARGET = 'G260_10'
		                                                   AND DEL_YN = 'N'
		                                                   AND REF_VAL = G.GOODS_CD
		                                                   ) > 0
		                        ) 
		                        OR /* 적용대상:카테고리*/
		                        (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) FROM TB_COUPON_REFVAL
		                                                   WHERE CPN_ID = CP.CPN_ID
		                                                   AND CPN_TARGET = 'G260_11' 
		                                                   AND DEL_YN = 'N'
		                                                   AND IFNULL(REF_FORMAL_GB,G.FORMAL_GB) = G.FORMAL_GB
		                                                   AND IFNULL(REF_BRAND_CD,G.BRAND_CD) = G.BRAND_CD
		                                                   AND REF_VAL IN (SELECT DISTINCT X.CATE_NO 
		                                                                   FROM 
		                                                                        (SELECT LEAF_CATE_NO 
		                                                                              , CATE_LVL 
		                                                                              , CATE_NO
		                                                                         FROM (
		                                                                               SELECT LEAF_CATE_NO ,
		                                                                                      CASE WHEN X = 1 THEN 'CATE1_NO'  -- 컬럼과 매핑(컬럼명)
		                                                                                           WHEN X = 2 THEN 'CATE2_NO'
		                                                                                           WHEN X = 3 THEN 'CATE3_NO'
		                                                                                           WHEN X = 4 THEN 'CATE4_NO'
		                                                                                           ELSE  'CATE5_NO' 
		                                                                                      END CATE_LVL,
		                                                                                      CASE WHEN X = 1 THEN CATE1_NO  -- 컬럼과 매핑(컬럼 데이터)
		                                                                                           WHEN X = 2 THEN CATE2_NO
		                                                                                           WHEN X = 3 THEN CATE3_NO
		                                                                                           WHEN X = 4 THEN CATE4_NO
		                                                                                           ELSE  CATE5_NO 
		                                                                                      END CATE_NO
		                                                                               FROM (
		                                                                                      ( SELECT LEAF_CATE_NO
		                                                                                             , CATE1_NO
		                                                                                             , CATE2_NO
		                                                                                             , CATE3_NO
		                                                                                             , CATE4_NO
		                                                                                             , CATE5_NO
		                                                                                        FROM  TB_CATE_4SRCH
		                                                                                        WHERE SITE_CD = 'G000_10'
		                                                                                        AND CATE_TYPE = 'G031_10') A, -- 상품타입
		                                                                                      (SELECT 1 AS X                  -- UNPIVOT 컬럼 수 만큼 선언
		                                                                                       UNION ALL SELECT 2 AS X
		                                                                                       UNION ALL SELECT 3 AS X
		                                                                                       UNION ALL SELECT 4 AS X
		                                                                                       UNION ALL SELECT 5 AS X
		                                                                                     ) B
		                                                                                    )  
		                                                                               ORDER BY LEAF_CATE_NO, CATE_LVL
		                                                                              ) K
		                                                                              WHERE CATE_NO IS NOT NULL
		                                                                        ) X, 
		                                                                        (
		                                                                         SELECT A.CATE_NO, A.GOODS_CD
		                                                                         FROM TB_CATE_GOODS A
		                                                                         WHERE GOODS_CD = G.GOODS_CD
		                                                                        ) Y 
		                                                                        WHERE X.LEAF_CATE_NO = Y.CATE_NO
		                                                                  )
		                                                  ) >0
		                        ) 
		                        /* 2021.08.23 적용대상 : 브랜드, 공급업체 */
		                        OR 
		                        (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) 
		                                                   FROM   TB_COUPON_REFVAL CR1
		                                                   INNER  JOIN TB_COUPON_REFVAL CR2
		                                                   ON     1=1
		                                                   AND    CR2.CPN_ID = CP.CPN_ID
		                                                   AND    CR2.CPN_TARGET = 'G260_13' 
		                                                   AND    CR2.DEL_YN = 'N'
		                                                   AND    CR2.REF_VAL = G.SUPPLY_COMP_CD
		                                                   AND    IFNULL(CR2.REF_FORMAL_GB, G.FORMAL_GB) = G.FORMAL_GB
		                                                   WHERE  1=1
		                                                   AND    CR1.CPN_ID = CP.CPN_ID
		                                                   AND    CR1.CPN_TARGET = 'G260_12' 
		                                                   AND    CR1.DEL_YN = 'N'
		                                                   AND    CR1.REF_VAL = G.BRAND_CD
		                                                   AND    IFNULL(CR1.REF_FORMAL_GB, G.FORMAL_GB) = G.FORMAL_GB
		                                                  ) > 0
		                        )
		                      )
		                AND NOT EXISTS (
		                                SELECT 1
		                                FROM   TB_COUPON_REFVAL SCPR
		                                WHERE  SCPR.CPN_ID = CP.CPN_ID
		                                AND    SCPR.REF_VAL = G.GOODS_CD
		                                AND    SCPR.DEL_YN = 'N'      -- 삭제안된넘
		                                AND    SCPR.CPN_TARGET = 'G260_14' -- 제외상품
		                               )
		                AND NOT EXISTS (
		                                 SELECT 1
		                                FROM TB_COUPON_BAN_GOODS
		                                WHERE GOODS_CD = G.GOODS_CD
		                                AND DEL_YN = 'N'
		                               )
		               ) CP
		               WHERE 1 = 1
		               <![CDATA[
		             --  AND CP.BUY_LIMIT_AMT <= CP.CURR_PRICE  -- 최소주문금액
		               ]]>
		       ) Y
		) A
		WHERE 1 = 1
		<if test="maxRownum != null and maxRownum > 0">
		LIMIT #{maxRownum}
		</if> 
	</select>
	
	<!--상품 즉시할인 쿠폰 조회-->
	<select id="getGoodsCoupon1List" parameterType="Goods" resultType="Coupon">
		/* TsfCoupon.getGoodsCoupon1List */
		SELECT A.DC_AMT
		     , A.DC_VAL
		     , A.DC_WAY
		     , A.CPN_ID
		     , A.CPN_NM
		     , A.CPN_TYPE
		     , A.AVAIL_STDT
		     , DATE_FORMAT(A.AVAIL_EDDT, '%Y-%m-%d') AS AVAIL_EDDT
		     , A.GOODS_CD
		     , RNUM
		FROM (
		SELECT  DISTINCT CPN_ID
		       , CPN_NM
		       , DC_AMT
		       , DC_VAL
		       , DC_WAY
		       , CPN_TYPE
		       , AVAIL_STDT
		       , AVAIL_EDDT
		       , GOODS_CD
		       , RANK() OVER(ORDER BY DC_AMT DESC)  AS RNUM   -- 할인금액순
		FROM   (
		        SELECT CP.CPN_ID                                              -- 쿠폰ID
		             , CP.CPN_NM                                              -- 쿠폰명
		             , CP.DC_WAY                                              -- 할인방식
		             , CP.DC_VAL                                              -- 할인값
		             , CP.CPN_TYPE                                            -- 쿠폰타입
		             , CP.AVAIL_STDT                                          -- 유효시작일시
		             , CP.AVAIL_EDDT                                          -- 유효종료일시
		             , CP.GOODS_CD                                            -- 상품코드
		             <![CDATA[
		             , (CASE WHEN CP.DC_WAY = 'G240_10' THEN CP.DC_VAL
		                     ELSE FLOOR((CP.CURR_PRICE * CP.DC_VAL / 100) / #{pointUnit}) * #{pointUnit} 
		                     END) AS DC_AMT
		              ]]>
		              
		        FROM   (
		                SELECT CP.CPN_ID
		                     , CP.CPN_NM
		                     , CP.DC_WAY
		                     , (CASE WHEN 'P' = #{frontGb} THEN CP.DC_PVAL
		                             WHEN 'M' = #{frontGb} THEN CP.DC_MVAL
		                             ELSE CP.DC_AVAL END) AS DC_VAL
		                     , CP.CPN_TYPE
		                     , CP.AVAIL_STDT
		                     , CP.AVAIL_EDDT
		                     , G.GOODS_CD
		                     , G.CURR_PRICE
		                FROM   TB_GOODS G
		                     , TB_COUPON CP
		                WHERE 1 = 1
		                <choose>
		                    <when test="arrGoodsCd != null and arrGoodsCd != ''">
		                AND G.GOODS_CD IN
		                    <foreach collection="arrGoodsCd" item="item" index="index"  open="(" close=")" separator=",">
		                    UPPER(#{item})
		                    </foreach>
		                    </when>
		                    <otherwise>
		                AND G.GOODS_CD = #{goodsCd}
		                    </otherwise>
		                </choose> 
		                AND    CP.SITE_CD = #{siteCd}
		                AND    CP.CPN_STAT = 'G232_11'   -- 쿠폰인 진행중인 쿠폰만
		                AND    NOW() BETWEEN CP.AVAIL_STDT AND CP.AVAIL_EDDT
		                AND    CP.CPN_TYPE = 'G230_10' /*즉시할인쿠폰*/
		                AND    CP.DC_CD_GB = 'G233_00'                         -- 일반유형(할인쿠폰구분)
		                AND    (CASE WHEN 'P' = #{frontGb} THEN CP.DC_PVAL
		                             WHEN 'M' = #{frontGb} THEN CP.DC_MVAL
		                             ELSE CP.DC_AVAL END) > 0                  -- PC, MOBILE,APP 별로 0 보다 큰 쿠폰
		                AND   (
		                        /* 적용대상:상품*/
		                        (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) FROM TB_COUPON_REFVAL
		                                                   WHERE CPN_ID = CP.CPN_ID
		                                                   AND CPN_TARGET = 'G260_10'
		                                                   AND DEL_YN = 'N'
		                                                   AND REF_VAL = G.GOODS_CD
		                                                   ) > 0
		                        ) 
		                        OR /* 적용대상:카테고리*/
		                        (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) FROM TB_COUPON_REFVAL
		                                                   WHERE CPN_ID = CP.CPN_ID
		                                                   AND CPN_TARGET = 'G260_11' 
		                                                   AND DEL_YN = 'N'
		                                                   AND IFNULL(REF_FORMAL_GB,G.FORMAL_GB) = G.FORMAL_GB
		                                                   AND IFNULL(REF_BRAND_CD,G.BRAND_CD) = G.BRAND_CD
		                                                   AND REF_VAL IN (SELECT DISTINCT X.CATE_NO 
		                                                                   FROM 
		                                                                        (SELECT LEAF_CATE_NO 
		                                                                              , CATE_LVL 
		                                                                              , CATE_NO
		                                                                         FROM (
		                                                                               SELECT LEAF_CATE_NO ,
		                                                                                      CASE WHEN X = 1 THEN 'CATE1_NO'  -- 컬럼과 매핑(컬럼명)
		                                                                                           WHEN X = 2 THEN 'CATE2_NO'
		                                                                                           WHEN X = 3 THEN 'CATE3_NO'
		                                                                                           WHEN X = 4 THEN 'CATE4_NO'
		                                                                                           ELSE  'CATE5_NO' 
		                                                                                      END CATE_LVL,
		                                                                                      CASE WHEN X = 1 THEN CATE1_NO  -- 컬럼과 매핑(컬럼 데이터)
		                                                                                           WHEN X = 2 THEN CATE2_NO
		                                                                                           WHEN X = 3 THEN CATE3_NO
		                                                                                           WHEN X = 4 THEN CATE4_NO
		                                                                                           ELSE  CATE5_NO 
		                                                                                      END CATE_NO
		                                                                               FROM (
		                                                                                      ( SELECT LEAF_CATE_NO
		                                                                                             , CATE1_NO
		                                                                                             , CATE2_NO
		                                                                                             , CATE3_NO
		                                                                                             , CATE4_NO
		                                                                                             , CATE5_NO
		                                                                                        FROM  TB_CATE_4SRCH
		                                                                                        WHERE SITE_CD = 'G000_10'
		                                                                                        AND CATE_TYPE = 'G031_10') A, -- 상품타입
		                                                                                      (SELECT 1 AS X                  -- UNPIVOT 컬럼 수 만큼 선언
		                                                                                       UNION ALL SELECT 2 AS X
		                                                                                       UNION ALL SELECT 3 AS X
		                                                                                       UNION ALL SELECT 4 AS X
		                                                                                       UNION ALL SELECT 5 AS X
		                                                                                     ) B
		                                                                                    )  
		                                                                               ORDER BY LEAF_CATE_NO, CATE_LVL
		                                                                              ) K
		                                                                              WHERE CATE_NO IS NOT NULL
		                                                                        ) X, 
		                                                                        (
		                                                                         SELECT A.CATE_NO, A.GOODS_CD
		                                                                         FROM TB_CATE_GOODS A
		                                                                         WHERE GOODS_CD = G.GOODS_CD
		                                                                        ) Y 
		                                                                        WHERE X.LEAF_CATE_NO = Y.CATE_NO
		                                                                  )
		                                                  ) >0
		                        ) 
		                        /* 2021.08.23 적용대상 : 브랜드, 공급업체 and 조건변경 */
		                        OR 
		                        (CP.APPLY_SCOPE = 'I' AND (SELECT COUNT(1) 
		                                                   FROM   TB_COUPON_REFVAL CR1
		                                                   INNER  JOIN TB_COUPON_REFVAL CR2
		                                                   ON     1=1
		                                                   AND    CR2.CPN_ID = CP.CPN_ID
		                                                   AND    CR2.CPN_TARGET = 'G260_13' 
		                                                   AND    CR2.DEL_YN = 'N'
		                                                   AND    CR2.REF_VAL = G.SUPPLY_COMP_CD
		                                                   AND    IFNULL(CR2.REF_FORMAL_GB, G.FORMAL_GB) = G.FORMAL_GB
		                                                   WHERE  1=1
		                                                   AND    CR1.CPN_ID = CP.CPN_ID
		                                                   AND    CR1.CPN_TARGET = 'G260_12' 
		                                                   AND    CR1.DEL_YN = 'N'
		                                                   AND    CR1.REF_VAL = G.BRAND_CD
		                                                   AND    IFNULL(CR1.REF_FORMAL_GB, G.FORMAL_GB) = G.FORMAL_GB
		                                                  ) > 0
		                        )
		                      )
		                AND NOT EXISTS (
		                                SELECT 1
		                                FROM   TB_COUPON_REFVAL SCPR
		                                WHERE  SCPR.CPN_ID = CP.CPN_ID
		                                AND    SCPR.REF_VAL = G.GOODS_CD
		                                AND    SCPR.DEL_YN = 'N'      -- 삭제안된넘
		                                AND    SCPR.CPN_TARGET = 'G260_14' -- 제외상품
		                               )
		                AND NOT EXISTS (
		                                 SELECT 1
		                                FROM TB_COUPON_BAN_GOODS
		                                WHERE GOODS_CD = G.GOODS_CD
		                                AND DEL_YN = 'N'
		                               )
		               ) CP
		               WHERE 1 = 1
		       ) Y
		) A
		WHERE 1 = 1
		<if test="maxRownum != null and maxRownum > 0">
		<![CDATA[
		AND RNUM <= #{maxRownum}
		]]>
		</if> 
	</select>
	
	<select id="getMypageCouponList" resultType="Coupon" parameterType="Coupon">
		/* TsfCoupon.getMypageCouponList */
		SELECT CC.CPN_ID                    /*쿠폰ID*/
		     , C.CPN_NM                     /*쿠폰명*/
		     , C.BUY_LIMIT_AMT              /*구매제한금액*/
		     , C.MAX_DC_AMT                 /*최대할인금액*/
		     , CASE WHEN #{frontGb} = 'P' THEN C.DC_PVAL
		            WHEN #{frontGb} = 'M' THEN C.DC_MVAL
		            WHEN #{frontGb} = 'A' THEN C.DC_AVAL
		       END             AS DC_VAL    /*할인값*/
		     , CASE WHEN C.DC_WAY = 'G240_10' THEN '원'                                                                                             
		            ELSE '%'
		       END             AS DC_WAY    /*할인방법*/
		     , CONCAT(CASE WHEN C.BUY_LIMIT_AMT = 0 THEN ''
		                   ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT , 0),'원 이상 구매 시 ')
		              END
		             ,CASE WHEN C.MAX_DC_AMT = 0 THEN ''
		                   ELSE CONCAT('최대 ',FORMAT(C.MAX_DC_AMT , 0),'원 할인')
		              END)     AS USE_CONDITION  /*사용조건*/
		     , CASE WHEN C.CUST_PUB_LIMIT_QTY = 0 THEN ''
		            ELSE CONCAT('1인당 최대',C.CUST_PUB_LIMIT_QTY,'매')
		       END             AS ISSUE_CONDITION /*발급수량*/
		     , CC.AVAIL_STDT                /*유효시작일자*/
		     , CC.AVAIL_EDDT                /*유효종료일자*/
		     , CC.CPN_CNT                   /*보유쿠폰수*/
		     , CC.EXPIRE_YN                 /*만료여부*/
		FROM   (
		        SELECT CC.CPN_ID
		             , DATE_FORMAT(CC.AVAIL_STDT,'%Y.%m.%d') AS AVAIL_STDT  /*유효시작일자*/
		             , DATE_FORMAT(CC.AVAIL_EDDT,'%Y.%m.%d') AS AVAIL_EDDT  /*유효종료일자*/
		             , COUNT(*)                              AS CPN_CNT /*보유쿠폰수*/
		             , MAX(CASE WHEN NOW() > CC.AVAIL_EDDT THEN 'Y'
		                        ELSE 'N'
		                   END)                              AS EXPIRE_YN /*만료여부*/
		        FROM   TB_CUST_COUPON CC
		        WHERE  1 = 1
		        AND    CC.CUST_NO = #{custNo} /**P*/
		        AND    CC.USED_DT IS NULL /*사용하지않은쿠폰만*/
		        AND    CC.AVAIL_EDDT >= DATE_ADD(NOW(), INTERVAL -3 MONTH) /*최근3개월쿠폰만*/
		        GROUP  BY CC.CPN_ID, DATE_FORMAT(CC.AVAIL_STDT,'%Y.%m.%d'), DATE_FORMAT(CC.AVAIL_EDDT,'%Y.%m.%d')
		       ) CC
		     , TB_COUPON C
		WHERE  CC.CPN_ID = C.CPN_ID
		AND    C.CPN_STAT = 'G232_11' /*진행중인쿠폰*/
		AND    C.SITE_CD = #{siteCd}
		<if test="quickYn != null and quickYn != ''">
		AND    CC.CC.EXPIRE_YN = 'N'
		</if>
		ORDER  BY CC.EXPIRE_YN, CC.AVAIL_EDDT, DC_VAL DESC, MAX_DC_AMT DESC
		<if test="quickYn != null and quickYn != ''">
		LIMIT 30
		</if>
	</select>
	
	<select id="getMypageCouponInfo" resultType="Coupon" parameterType="Coupon">
		/* TsfCoupon.getMypageCouponInfo*/
		SELECT COUNT(*) AS ALL_COUPON_CNT
		    ,(  SELECT COUNT(*) AS USE_COUPON
		        FROM TB_CUST_COUPON CC 
		        WHERE 1=1
		        AND CC.CUST_NO = #{custNo}
		        AND NOW() BETWEEN CC.AVAIL_STDT AND CC.AVAIL_EDDT 
		        AND CC.USED_DT IS NULL ) AS USE_COUPON_CNT
		    ,(  SELECT COUNT(*) AS USE_COUPON
		        FROM TB_CUST_COUPON 
		        WHERE 1=1
		        AND CUST_NO = #{custNo}
		        AND NOW() BETWEEN AVAIL_STDT AND AVAIL_EDDT 
		        AND NOW() BETWEEN DATE_ADD(AVAIL_EDDT , INTERVAL -7 DAY) AND AVAIL_EDDT 
		        AND USED_DT IS NULL) AS EXPIRE_COUPON_CNT
		FROM TB_CUST_COUPON CC INNER JOIN TB_COUPON C
		ON CC.CPN_ID = C.CPN_ID 
		WHERE 1=1
		  AND CC.CUST_NO = #{custNo}
		  AND NOW() BETWEEN CC.AVAIL_STDT AND CC.AVAIL_EDDT
		  AND CC.USED_DT IS NULL
		  AND C.CPN_STAT = 'G232_11'
	</select>
	
	<select id="getCouponDetailInfo" resultType="Coupon" parameterType="Coupon">
		/* TsfCoupon.getCouponDetailInfo -- custNo 변경 예정*/
		SELECT Z.CPN_ID                                /*쿠폰ID*/
		     , Z.CPN_NM                                /*쿠폰명*/
		     , Z.BUY_LIMIT_AMT                         /*구매제한금액*/
		     , Z.MAX_DC_AMT                            /*최대할인금액*/
		     , Z.DC_VAL                                /*할인값*/
		     , Z.DC_WAY                                /*할인방법*/
		     , Z.USE_CONDITION                         /*사용조건*/
		     , Z.ISSUE_CONDITION                       /*발급조건*/
		     , Z.AVAIL_STDT                            /*유효시작일자*/
		     , Z.AVAIL_EDDT                            /*유효종료일자*/
		     , Z.CPN_CNT                               /*보유쿠폰수*/
		     , Z.EXPIRE_YN                             /*만료여부*/
		     , GROUP_CONCAT(Z.TGT_CONDITION) AS TGT_CONDITION
		FROM
		(
		WITH TAB_COUPON AS (
		    SELECT CC.CPN_ID                          /*쿠폰ID*/
		         , C.CPN_NM                           /*쿠폰명*/
		         , C.BUY_LIMIT_AMT                    /*구매제한금액*/
		         , C.MAX_DC_AMT                       /*최대할인금액*/
		         , CASE WHEN #{frontGb} = 'P' THEN C.DC_PVAL
		                WHEN #{frontGb} = 'M' THEN C.DC_MVAL
		                WHEN #{frontGb} = 'A' THEN C.DC_AVAL
		           END             AS DC_VAL          /*할인값*/
		         , CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
		                ELSE '%'
		           END             AS DC_WAY          /*할인방법*/
		         , CONCAT(CASE WHEN C.BUY_LIMIT_AMT = 0 THEN ''
		                       ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT , 0),'원 이상 구매 시 ')
		                  END
		                 ,CASE WHEN C.MAX_DC_AMT = 0 THEN ''
		                       ELSE CONCAT('최대 ',FORMAT(C.MAX_DC_AMT , 0),'원 할인')
		                  END)     AS USE_CONDITION   /*사용조건*/
		         , CASE WHEN C.CUST_PUB_LIMIT_QTY = 0 THEN ''
		                ELSE CONCAT('1인당 최대',C.CUST_PUB_LIMIT_QTY,'매')
		           END             AS ISSUE_CONDITION /*발급조건*/
		         , CC.AVAIL_STDT                      /*유효시작일자*/
		         , CC.AVAIL_EDDT                      /*유효종료일자*/
		         , CC.CPN_CNT                         /*보유쿠폰수*/
		         , CC.EXPIRE_YN                       /*만료여부*/
		    FROM   (
		            SELECT CC.CPN_ID
		                 , DATE_FORMAT(CC.AVAIL_STDT,'%Y.%m.%d %H:%i') AS AVAIL_STDT  /*유효시작일자*/
		                 , DATE_FORMAT(CC.AVAIL_EDDT,'%Y.%m.%d %H:%i') AS AVAIL_EDDT  /*유효종료일자*/
		                 , COUNT(*)                              AS CPN_CNT /*보유쿠폰수*/
		                 , MAX(CASE WHEN NOW() > CC.AVAIL_EDDT THEN 'Y'
		                            ELSE 'N'
		                       END)                              AS EXPIRE_YN /*만료여부*/
		            FROM   TB_CUST_COUPON CC
		            WHERE  1 = 1
		            GROUP  BY CC.CPN_ID, DATE_FORMAT(CC.AVAIL_STDT,'%Y.%m.%d %H:%i'), DATE_FORMAT(CC.AVAIL_EDDT,'%Y.%m.%d %H:%i')
		           ) CC
		         , TB_COUPON C
		    WHERE  CC.CPN_ID  = C.CPN_ID
		    AND    C.CPN_STAT = 'G232_11' /*진행중인쿠폰*/
		    AND    C.SITE_CD  = #{siteCd}
		    AND    C.CPN_ID   = #{cpnId}
		)
		, 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 CR.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
		)
		SELECT C.CPN_ID                                /*쿠폰ID*/
		     , C.CPN_NM                                /*쿠폰명*/
		     , C.BUY_LIMIT_AMT                         /*구매제한금액*/
		     , C.MAX_DC_AMT                            /*최대할인금액*/
		     , C.DC_VAL                                /*할인값*/
		     , C.DC_WAY                                /*할인방법*/
		     , C.USE_CONDITION                         /*사용조건*/
		     , C.ISSUE_CONDITION                       /*발급조건*/
		     , C.AVAIL_STDT                            /*유효시작일자*/
		     , C.AVAIL_EDDT                            /*유효종료일자*/
		     , C.CPN_CNT                               /*보유쿠폰수*/
		     , C.EXPIRE_YN                             /*만료여부*/
		     , 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   TAB_COUPON C LEFT OUTER JOIN TAB_COUPON_REFVAL2 CR
		            ON  C.CPN_ID = CR.CPN_ID
		WHERE C.CPN_ID = #{cpnId}
		ORDER  BY C.EXPIRE_YN, C.AVAIL_EDDT
		)Z
		GROUP BY  Z.CPN_ID                                /*쿠폰ID*/
		        , Z.CPN_NM                                /*쿠폰명*/
		        , Z.BUY_LIMIT_AMT                         /*구매제한금액*/
		        , Z.MAX_DC_AMT                            /*최대할인금액*/
		        , Z.DC_VAL                                /*할인값*/
		        , Z.DC_WAY                                /*할인방법*/
		        , Z.USE_CONDITION                         /*사용조건*/
		        , Z.ISSUE_CONDITION                       /*발급조건*/
		        , Z.AVAIL_STDT                            /*유효시작일자*/
		        , Z.AVAIL_EDDT                            /*유효종료일자*/
		        , Z.CPN_CNT                               /*보유쿠폰수*/
		        , Z.EXPIRE_YN                             /*만료여부*/
		ORDER BY CPN_ID LIMIT 1
	</select>
	
	<!-- 기획전 다운가능 쿠폰 정보 조회 -->
	<select id="getPlanCouponInfo" parameterType="Coupon" resultType="Coupon">
		/* TsfCoupon.getPlanCouponInfo */
		SELECT C.CPN_ID 
		      ,C.CPN_NM 
		      ,C.CPN_DESC       
		      ,C.CPN_TYPE 
		      ,C.BUY_LIMIT_AMT 
		      ,C.CUST_PUB_LIMIT_QTY
		      ,IF(C.DC_CD_GB = 'G233_30', LC.DOWN_LIMIT_QTY , C.TOT_PUB_LIMIT_QTY) AS TOT_PUB_LIMIT_QTY
		      ,C.ONE_PUB_QTY
		      ,C.MAX_DC_AMT
		      ,C.FIRST_ORD_YN
		      ,DATE_FORMAT(C.BUY_STDT ,'%Y%m%d') AS BUY_STDT 
              ,DATE_FORMAT(C.BUY_EDDT ,'%Y%m%d') AS BUY_EDDT
		      , IFNULL((SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = C.CPN_ID AND CUST_NO = #{custNo}),0) AS CUST_COUPON_CNT
		      , (
		          CASE WHEN C.DC_CD_GB = 'G233_30' /* 선착순쿠폰일때 */ THEN IFNULL((
		                                                   SELECT COUNT(1)
		                                                   FROM TB_LIMITED_TIME_COUPON LTC
		                                                      , TB_CUST_COUPON CC
		                                                   WHERE LTC.CPN_ID  = CC.CPN_ID
		                                                     AND   NOW() BETWEEN LTC.DOWN_STDT AND LTC.DOWN_EDDT
		                                                     AND LTC.DEL_YN = 'N'
		                                                     AND LTC.CPN_ID = C.CPN_ID
		                                                     AND CC.REG_DT BETWEEN LTC.DOWN_STDT AND LTC.DOWN_EDDT
		                                               ),0)
		          ELSE IFNULL((SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = C.CPN_ID),0) END
		         ) AS TOT_COUPON_CNT
		     , CONCAT(CASE WHEN C.BUY_LIMIT_AMT = 0 THEN ''
		                   ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT , 0),'원 이상 구매 시 ')
		              END
		             ,CASE WHEN C.MAX_DC_AMT = 0 THEN ''
		                   ELSE CONCAT('최대 ',FORMAT(C.MAX_DC_AMT , 0),'원 할인')
		              END)     AS USE_CONDITION  /*사용조건*/
		     , CASE WHEN C.CUST_PUB_LIMIT_QTY = 0 THEN ''
		            ELSE CONCAT('1인당 최대',C.CUST_PUB_LIMIT_QTY,'매')
		       END             AS ISSUE_CONDITION /*발급수량*/
		      ,CASE WHEN #{frontGb} = 'P' THEN C.DC_PVAL
		            WHEN #{frontGb} = 'M' THEN C.DC_MVAL
		            ELSE C.DC_AVAL
		            END                                       AS DC_VAL
		      ,CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
		                    ELSE '%'
		                    END                                      AS DC_WAY    /*할인방법*/
		      ,C.PD_GB 
		      ,IF (C.PD_GB = 'D', NOW(), C.AVAIL_STDT) AS AVAIL_STDT
		      ,IF (C.PD_GB = 'D', CONCAT(CURRENT_DATE + INTERVAL C.AVAIL_DAYS DAY, ' 23:59:59'), C.AVAIL_EDDT) AS AVAIL_EDDT
		FROM TB_COUPON C
				 LEFT OUTER JOIN TB_LIMITED_TIME_COUPON LC ON C.CPN_ID = LC.CPN_ID AND NOW() BETWEEN LC.DOWN_STDT AND LC.DOWN_EDDT
		WHERE 1=1
		AND C.CPN_STAT = 'G232_11'
		AND C.CPN_ID = #{cpnId}
		AND C.SITE_CD = #{siteCd}
		AND C.CPN_TYPE IN ('G230_11','G230_20','G230_30')
		  AND NOW() BETWEEN IF(C.DC_CD_GB = 'G233_30', LC.DOWN_STDT, C.DOWN_STDT) AND IF(C.DC_CD_GB = 'G233_30', LC.DOWN_EDDT,  C.DOWN_EDDT)
		AND NOW()  <![CDATA[<=]]> IF (C.PD_GB = 'D', CONCAT(CURRENT_DATE + INTERVAL C.AVAIL_DAYS DAY, ' 23:59:59'), C.AVAIL_EDDT)
		AND (CASE WHEN #{frontGb} = 'P' THEN C.DC_PVAL
		          WHEN #{frontGb} = 'M' THEN C.DC_MVAL
		     ELSE C.DC_AVAL END) > 0                  -- PC, MOBILE,APP 별로 0 보다 큰 쿠폰
		AND IF (C.TOT_PUB_LIMIT_QTY = 0, 9999999999,C.TOT_PUB_LIMIT_QTY) > (SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = C.CPN_ID) -- 총발행제한수
		AND IF (C.CUST_PUB_LIMIT_QTY = 0, 9999999999,C.CUST_PUB_LIMIT_QTY) > (SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = C.CPN_ID AND CUST_NO= #{custNo}) -- 고객당발행제한수량
		AND (SELECT COUNT(1)
		     FROM TB_COUPON_CUST_GBN
		     WHERE CPN_ID = C.CPN_ID
		     AND USABLE_CUST_GB IN (#{custGb})              -- 사용가능고객구분
		     ) <![CDATA[>=]]> 1
		AND (SELECT COUNT(1)
		     FROM TB_COUPON_CUST_GRADE
		     WHERE CPN_ID = C.CPN_ID
		     AND USABLE_CUST_GRADE IN (#{custGrade})              -- 사용가능고객구분
		     ) <![CDATA[>=]]> 1
	</select>

	<!--회원등급쿠폰 정보-->
	<select id="getCustGradePolicyCoupon" parameterType="Coupon" resultType="Coupon">
		/*TsfCoupon.getCustGradePolicyCoupon*/
		SELECT CP.CPN_ID
		     , CP.END_ALIM_YN
		     , 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
		     , DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH)+ INTERVAL 1 DAY, '%Y%m%d%H%i%S') AS AVAIL_STDT
		     , DATE_FORMAT(CONCAT(LAST_DAY(NOW()), ' 23:59:59'), '%Y%m%d%H%i%S')               AS AVAIL_EDDT
		FROM  TB_COUPON CP
		WHERE CP.CPN_ID = #{cpnId}
		AND   CP.CPN_STAT = 'G232_11'
		AND   NOT EXISTS (
		                  SELECT 1
		                  FROM   TB_CUST_COUPON CC
		                  WHERE  CC.CPN_ID = CP.CPN_ID
		                  AND    CC.CUST_NO = #{custNo}
		                  AND    CC.REG_DT >= DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH)+ INTERVAL 1 DAY, '%Y%m%d%H%i%S')
		                  AND    CC.REG_DT <![CDATA[<=]]> DATE_FORMAT(CONCAT(LAST_DAY(NOW()), ' 23:59:59'), '%Y%m%d%H%i%S')
		                  )
	</select>
	
	<select id="getAttendCouponInfo" parameterType="CustCoupon" resultType="CustCoupon">
		/*TsfCoupon.getAttendCouponInfo*/
		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(DATE_ADD(NOW(), INTERVAL CP.AVAIL_DAYS DAY), '%Y%m%d%H%i%S')
		            ELSE  DATE_FORMAT(CP.AVAIL_EDDT, '%Y%m%d%H%i%S')             END AS AVAIL_EDDT
		FROM  TB_COUPON CP
		WHERE CP.CPN_ID = #{cpnId}
		AND   CP.SITE_CD = #{siteCd}
		AND   CP.CPN_STAT = 'G232_11'
	</select>
	
	<select id="getQuickCouponDownList"  parameterType="Coupon" resultType="Coupon">
		/*TsfCoupon.getQuickCouponDownList*/
		SELECT C.CPN_ID 
		      ,C.CPN_NM 
		      ,C.CPN_DESC       
		      ,C.CPN_TYPE 
		      ,C.BUY_LIMIT_AMT 
		      ,C.CUST_PUB_LIMIT_QTY 
		      ,C.TOT_PUB_LIMIT_QTY
		      ,C.MAX_DC_AMT 
		      ,C.FIRST_ORD_YN
		      ,DATE_FORMAT(C.BUY_STDT, '%Y%m%d') AS BUY_STDT
		      ,DATE_FORMAT(C.BUY_EDDT, '%Y%m%d') AS BUY_EDDT
		      , IFNULL((SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = C.CPN_ID AND CUST_NO = #{custNo}),0) AS CUST_COUPON_CNT
		      , CONCAT(CASE WHEN C.BUY_LIMIT_AMT = 0 THEN ''
		                   ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT , 0),'원 이상 구매 시 ')
		              END
		             ,CASE WHEN C.MAX_DC_AMT = 0 THEN ''
		                   ELSE CONCAT('최대 ',FORMAT(C.MAX_DC_AMT , 0),'원 할인')
		              END)     AS USE_CONDITION  /*사용조건*/
		     , CASE WHEN C.CUST_PUB_LIMIT_QTY = 0 THEN ''
		            ELSE CONCAT('1인당 최대',C.CUST_PUB_LIMIT_QTY,'매')
		       END             AS ISSUE_CONDITION /*발급수량*/
		      ,CASE WHEN #{frontGb} = 'P' THEN C.DC_PVAL
		            WHEN #{frontGb} = 'M' THEN C.DC_MVAL
		            ELSE C.DC_AVAL
		            END                                       AS DC_VAL
		      ,CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
		                    ELSE '%'
		                    END                                      AS DC_WAY    /*할인방법*/
		      ,C.PD_GB 
		      ,IF (C.PD_GB = 'D', NOW(), C.AVAIL_STDT) AS AVAIL_STDT
		      ,IF (C.PD_GB = 'D', CONCAT(CURRENT_DATE + INTERVAL C.AVAIL_DAYS DAY, ' 23:59:59'), C.AVAIL_EDDT) AS AVAIL_EDDT
		FROM TB_COUPON C
		WHERE 1=1
		AND C.CPN_STAT = 'G232_11'
		<if test="cpnId != null and cpnId != ''">
		AND    C.CPN_ID = #{cpnId}
		</if>
		AND C.SITE_CD = #{siteCd}
		AND C.CPN_TYPE IN ('G230_20','G230_30','G230_11')
		AND C.DOWN_ABL_YN = 'Y'
		AND NOW() BETWEEN C.DOWN_STDT AND C.DOWN_EDDT
		AND NOW()  <![CDATA[<=]]> IF (C.PD_GB = 'D', CONCAT(CURRENT_DATE + INTERVAL C.AVAIL_DAYS DAY, ' 23:59:59'), C.AVAIL_EDDT)
		AND (CASE WHEN #{frontGb} = 'P' THEN C.DC_PVAL
	              WHEN #{frontGb} = 'M' THEN C.DC_MVAL
		     ELSE C.DC_AVAL END) > 0                  -- PC, MOBILE,APP 별로 0 보다 큰 쿠폰
		AND IF (C.TOT_PUB_LIMIT_QTY = 0, 9999999999,C.TOT_PUB_LIMIT_QTY) > (SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = C.CPN_ID) -- 총발행제한수
		AND IF (C.FIRST_ORD_YN = 'Y', (SELECT COUNT(*) FROM TB_ORDER WHERE CUST_NO = #{custNo} AND ORD_DT BETWEEN C.BUY_STDT AND C.BUY_EDDT), 0) = 0 -- 첫구매 
		AND IF (C.NEW_CUST_YN = 'Y', (SELECT COUNT(*) FROM TB_CUSTOMER WHERE CUST_NO = #{custNo} AND JOIN_DT BETWEEN C.CUST_JOIN_STDT AND C.CUST_JOIN_EDDT), 1) = 1 -- 신규회원
		<if test='quickYn == "N" and quickYn != ""'>
		AND IF (C.CUST_PUB_LIMIT_QTY = 0, 9999999999,C.CUST_PUB_LIMIT_QTY) > (SELECT COUNT(1) FROM TB_CUST_COUPON WHERE CPN_ID = C.CPN_ID AND CUST_NO= #{custNo}) -- 고객당발행제한수량
		</if>
		AND (SELECT COUNT(1)
		     FROM TB_COUPON_CUST_GBN
		     WHERE CPN_ID = C.CPN_ID
		     AND USABLE_CUST_GB IN (#{custGb})              -- 사용가능고객구분
		     ) <![CDATA[>=]]> 1
		AND (SELECT COUNT(1)
		     FROM TB_COUPON_CUST_GRADE
		     WHERE CPN_ID = C.CPN_ID
		     AND USABLE_CUST_GRADE IN (#{custGrade})              -- 사용가능고객구분
		     ) <![CDATA[>=]]> 1
		ORDER BY  C.REG_DT DESC ,C.AVAIL_EDDT ASC LIMIT 10
	</select>

	<select id="getPlanCouponDetailInfo" parameterType="Coupon" resultType="Coupon">
		/*TsfCoupon.getPlanCouponDetailInfo*/
		SELECT Z.CPN_ID                                /*쿠폰ID*/
		     , Z.CPN_NM                                /*쿠폰명*/
		     , Z.BUY_LIMIT_AMT                         /*구매제한금액*/
		     , Z.MAX_DC_AMT                            /*최대할인금액*/
		     , Z.DC_VAL                                /*할인값*/
		     , Z.DC_WAY                                /*할인방법*/
		     , Z.USE_CONDITION                         /*사용조건*/
		     , Z.ISSUE_CONDITION                       /*발급조건*/
		     , Z.AVAIL_STDT                            /*유효시작일자*/
		     , Z.AVAIL_EDDT                            /*유효종료일자*/
		     , GROUP_CONCAT(Z.TGT_CONDITION) AS TGT_CONDITION
		FROM
		    (
		        WITH TAB_COUPON AS (
		            SELECT  C.CPN_ID                          /*쿠폰ID*/
		                 , C.CPN_NM                           /*쿠폰명*/
		                 , C.BUY_LIMIT_AMT                    /*구매제한금액*/
		                 , C.MAX_DC_AMT                       /*최대할인금액*/
		                 , CASE WHEN #{frontGb} = 'P' THEN C.DC_PVAL
		                        WHEN #{frontGb} = 'M' THEN C.DC_MVAL
		                        WHEN #{frontGb} = 'A' THEN C.DC_AVAL
		                END             AS DC_VAL          /*할인값*/
		                 , CASE WHEN C.DC_WAY = 'G240_10' THEN '원'
		                        ELSE '%'
		                END             AS DC_WAY          /*할인방법*/
		                 , CONCAT(CASE WHEN C.BUY_LIMIT_AMT = 0 THEN ''
		                               ELSE CONCAT(FORMAT(C.BUY_LIMIT_AMT , 0),'원 이상 구매 시 ')
		                              END
		                ,CASE WHEN C.MAX_DC_AMT = 0 THEN ''
		                      ELSE CONCAT('최대 ',FORMAT(C.MAX_DC_AMT , 0),'원 할인')
		                              END)     AS USE_CONDITION   /*사용조건*/
		                 , CASE WHEN C.CUST_PUB_LIMIT_QTY = 0 THEN ''
		                        ELSE CONCAT('1인당 최대',C.CUST_PUB_LIMIT_QTY,'매')
		                END             AS ISSUE_CONDITION /*발급조건*/
		                 , C.AVAIL_STDT                      /*유효시작일자*/
		                 , C.AVAIL_EDDT                      /*유효종료일자*/
		            FROM   TB_COUPON C
		            WHERE  1=1
		              AND    C.CPN_STAT = 'G232_11' /*진행중인쿠폰*/
		              AND    C.CPN_ID   = #{cpnId}
		        )
		           , 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 CR.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
		        )
		        SELECT C.CPN_ID                                /*쿠폰ID*/
		             , C.CPN_NM                                /*쿠폰명*/
		             , C.BUY_LIMIT_AMT                         /*구매제한금액*/
		             , C.MAX_DC_AMT                            /*최대할인금액*/
		             , C.DC_VAL                                /*할인값*/
		             , C.DC_WAY                                /*할인방법*/
		             , C.USE_CONDITION                         /*사용조건*/
		             , C.ISSUE_CONDITION                       /*발급조건*/
		             , C.AVAIL_STDT                            /*유효시작일자*/
		             , C.AVAIL_EDDT                            /*유효종료일자*/
		             , 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   TAB_COUPON C LEFT OUTER JOIN TAB_COUPON_REFVAL2 CR
		                                           ON  C.CPN_ID = CR.CPN_ID
		        WHERE C.CPN_ID = #{cpnId}
		        ORDER  BY C.AVAIL_EDDT
		    )Z
		GROUP BY  Z.CPN_ID                                /*쿠폰ID*/
		        , Z.CPN_NM                                /*쿠폰명*/
		        , Z.BUY_LIMIT_AMT                         /*구매제한금액*/
		        , Z.MAX_DC_AMT                            /*최대할인금액*/
		        , Z.DC_VAL                                /*할인값*/
		        , Z.DC_WAY                                /*할인방법*/
		        , Z.USE_CONDITION                         /*사용조건*/
		        , Z.ISSUE_CONDITION                       /*발급조건*/
		        , Z.AVAIL_STDT                            /*유효시작일자*/
		        , Z.AVAIL_EDDT                            /*유효종료일자*/
		ORDER BY CPN_ID LIMIT 1
	</select>
	
</mapper>