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

	<!-- 상품권 기본정보  -->
	<select id="getGiftcardInfo" parameterType="GiftCard" resultType="GiftCard">
		/*TsfGiftcard.getGiftcardInfo*/
		SELECT SUM(CG.RM_GFCD_AMT) AS USE_GIFT/*사용가능한 보유 금액*/
		      ,(SELECT COUNT(*) 
		        FROM TB_CUST_GIFTCARD CG2 
		        WHERE DATE_FORMAT(NOW(), '%Y%m%d') BETWEEN CG2.USE_ST_DATE AND CG2.USE_EXP_DATE
		          AND CUST_NO = #{custNo}
		          AND CG2.RM_GFCD_AMT <![CDATA[>]]> 0 ) AS TOTAL_COUNT /*총 상품권 수*/
		      ,(SELECT SUM(CG3.RM_GFCD_AMT)
				FROM TB_CUST_GIFTCARD CG3
				WHERE DATE_FORMAT(NOW(), '%Y%m%d') BETWEEN CG3.USE_ST_DATE AND CG3.USE_EXP_DATE
				  AND  NOW() BETWEEN DATE_ADD(DATE_FORMAT(CG3.USE_EXP_DATE,'%Y-%m-%d'), INTERVAL -30 DAY) AND USE_EXP_DATE
				  AND CUST_NO = #{custNo}
				  AND CG3.RM_GFCD_AMT > 0 ) AS EXPIRE_COUNT  /*한달 이내 소멸*/
		FROM TB_CUST_GIFTCARD CG 
		WHERE 1=1  
			AND CUST_NO = #{custNo}
			AND DATE_FORMAT(NOW(), '%Y%m%d') BETWEEN CG.USE_ST_DATE AND CG.USE_EXP_DATE
	</select>
	
	<select id="getGiftcardUseList" parameterType="GiftCard" resultType="GiftCard">
		/*TsfGiftcard.getGiftcardUseList*/
		SELECT A.GFCD_HST_SQ
		      , A.CUST_NO
		      , A.OCCUR_GB
		      , A.OCCUR_DTL_DESC
		      , A.GFCD_AMT
		      , A.CUST_GFCD_SQ
		      , A.ORD_NO
		      , A.ORD_DTL_NO
		      , A.REG_NO
		      , DATE_FORMAT(A.REG_DT,'%Y.%m.%d %H:%i:%S') AS REG_DT
		      , A.UPD_NO
		      , A.UPD_DT
		      , B.GOODS_CD 
		      , D.GOODS_NM 
		FROM TB_CUST_GIFTCARD_HST A INNER JOIN TB_ORDER_DETAIL B 
													ON A.ORD_DTL_NO = B.ORD_DTL_NO AND A.ORD_NO = B.ORD_NO
									INNER JOIN TB_CUST_GIFTCARD C 
													ON A.CUST_GFCD_SQ =C.CUST_GFCD_SQ 
									INNER JOIN TB_GOODS D 
													ON B.GOODS_CD = D.GOODS_CD 
		WHERE 1=1
		 AND A.CUST_NO = #{custNo}
		 AND A.GFCD_AMT != 0
		 <choose>
		 	<when test="monthLength > 1">
		 		AND DATE_FORMAT(A.REG_DT,'%Y%m') = #{searchDt}
		 	</when>
		 	<otherwise>
		 		 AND DATE_FORMAT(A.REG_DT,'%Y%c') = #{searchDt}
		 	</otherwise>
		 </choose> 
		ORDER BY A.REG_DT DESC
	</select>
	
	<select id="getGiftcardOwnList" parameterType="GiftCard" resultType="GiftCard">
		/*TsfGiftcard.getGiftcardOwnList*/
		 SELECT  CUST_GFCD_SQ
				 , CUST_NO
				 , GFCD_NO
				 , GFCD_NM
				 , CHG_GFCD_AMT
				 , US_GFCD_AMT
				 , RM_GFCD_AMT
				 , DATE_FORMAT(USE_ST_DATE,'%Y-%m-%d') AS USE_ST_DATE
		         , DATE_FORMAT(USE_EXP_DATE,'%Y-%m-%d') AS USE_EXP_DATE
				 , REG_NO
				 , REG_DT
				 , UPD_NO
				 , UPD_DT
				 , CASE WHEN DATE_FORMAT(now(),'%Y%m%d') <![CDATA[<=]]> DATE_FORMAT(USE_EXP_DATE,'%Y%m%d') THEN 'Y' ELSE 'N' END AS USE_YN
		 FROM TB_CUST_GIFTCARD CG 
		 WHERE 1=1 
		  AND CG.CUST_NO = #{custNo}
		ORDER BY CG.REG_DT DESC
	</select>
	
	<insert id="saveGiftcardApi" parameterType="GiftCard">
		/*TsfGiftcard.saveGiftcardApi*/
		INSERT INTO TB_GIFTCARD_API_HST
		(
		   GFCD_NO
		 , IP_ADDR
		 , CUST_NO
		 , RESULT_VAL
		 , REG_NO
		 , REG_DT
		)
	VALUES(
			#{gfcdNo}
	      , #{ipAddr}
	      , #{custNo}
	      , #{resultVal}
	      , #{regNo}
	      , CURRENT_TIMESTAMP
	     )
	</insert>
	
	<insert id="createGiftcardUseConfirm" parameterType="GiftCard">
		/*TsfGiftcard.createGiftcardUseConfirm*/
		INSERT INTO TB_CUST_GIFTCARD
		(
			CUST_NO
		  , GFCD_NO
		  , GFCD_NM
		  , CHG_GFCD_AMT
		  , US_GFCD_AMT
		  , RM_GFCD_AMT
		  , USE_ST_DATE
		  , USE_EXP_DATE
		  , REG_NO
		  , REG_DT
		  , UPD_NO
		  , UPD_DT
		)
	  VALUES(
	           #{custNo}
	         , #{gfcdNo}
	         , 'YES24상품권'
	         , #{chgGfcdAmt}
	         , #{usGfcdAmt}
	         , #{rmGfcdAmt}
	         , DATE_FORMAT(#{useStDate},'%Y%m%d') /**P*/
	         , DATE_FORMAT(#{useExpDate},'%Y%m%d') /**P*/
	         , #{regNo}
	         , CURRENT_TIMESTAMP
	         , #{updNo}
	         , CURRENT_TIMESTAMP
	        )
	</insert>
	
	<select id="getRecentlyDate" resultType="GiftCard">
		/*TsfGiftcard.getRecentlyDate*/  
		SELECT A.*
		FROM
		    (		
		      SELECT DATE_FORMAT( NOW(), '%Y') AS YEAR ,DATE_FORMAT( NOW(), '%m') AS MONTH
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 1 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 1 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 2 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 2 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 3 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 3 MONTH), '%m')      
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 4 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 4 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 5 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 5 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 6 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 6 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 7 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 7 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 8 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 8 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 9 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 9 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 10 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 10 MONTH), '%m')
		      UNION
		      SELECT DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 11 MONTH), '%Y'),DATE_FORMAT( DATE_ADD(NOW(), INTERVAL - 11 MONTH), '%m')
		    )A	
	</select>
	
	<select id="getCustGiftCardSq" parameterType="GiftCard" resultType="int">
		/*TsfGiftcard.getCustGiftCardSq*/  
		SELECT CUST_GFCD_SQ 
		FROM TB_CUST_GIFTCARD 
		WHERE GFCD_NO = #{gfcdNo}
	</select>
	
	<insert id="createCustGiftcardHistory"  parameterType="GiftCard" >
		/* TsfGiftcard.createCustGiftcardHistorys */
		INSERT INTO TB_CUST_GIFTCARD_HST ( 
		      CUST_NO
		    , OCCUR_GB
		    , OCCUR_DTL_DESC
		    , GFCD_AMT
		    , CUST_GFCD_SQ
		    , ORD_NO
		    , ORD_DTL_NO
		    , REG_NO
		    , REG_DT
		    , UPD_NO
		    , UPD_DT
		) VALUES (
		      #{custNo}
		    , #{occurGb}
		    , #{occurDtlDesc}
		    , #{gfcdAmt}
		    , #{custGfcdSq}
		    , #{ordNo}
		    , #{ordDtlNo}
		    , #{custNo}
		    , NOW()
		    , #{custNo}
		    , NOW()
		)	
	</insert>
	
	<!-- 페이징을 위한 select절 상단 -->
	<sql id="selectForPagingHeader">
		SELECT *
		FROM   (
	</sql>

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>
	
	<!-- 상품권 KCP 현금영수증 신청 정보 조회 -->
	<select id="getCashReceiptsInfo" parameterType="GiftCard" resultType="GiftCard">
		/* TsfGiftcard.getCashReceiptsInfo */
		SELECT O.ORD_NO
		     , DATE_FORMAT(O.ORD_DT, '%Y%m%d%H%i%S') AS TRADE_TIME
		     , DATE_FORMAT(NOW(), '%Y%m%d%H%i%S')    AS NOW_TIME
		     , O.ORD_NM
		     , O.ORD_PHNNO
		     , O.ORD_EMAIL
		     , MAX((SELECT GOODS_NM FROM TB_GOODS WHERE GOODS_CD = OD.GOODS_CD)) AS GOODS_NM
		     , SUM(OD.GFCD_USE_AMT) AS US_GFCD_AMT
		  FROM TB_ORDER O
		 INNER JOIN TB_ORDER_DETAIL OD
		    ON O.ORD_NO = OD.ORD_NO
		 WHERE O.ORD_NO = #{ordNo}
		 GROUP BY O.ORD_NO, DATE_FORMAT(O.ORD_DT, '%Y%m%d%H%i%S'), O.ORD_NM, O.ORD_PHNNO, O.ORD_EMAIL
	</select>
	
	<!-- 상품권 KCP 현금영수증 신청 여부 조회 -->
	<select id="getCashReceiptsRequestInfo" parameterType="Order" resultType="GiftCard">
		/* TsfGiftcard.getCashReceiptsRequestInfo */
		SELECT ORD_NO
		     , TRADE_NO
		     , AUTH_NO
		     , AMT
		     , REG_DT
		     , UPD_DT
		  FROM TB_GIFTCARD_RECEIPT
		 WHERE ORD_NO = #{ordNo}
	</select>


</mapper>
