<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.style24.batch.biz.dao.TsbGiftcardDao">

	<select id="getExpireGiftcardList" parameterType="Giftcard" resultType="Giftcard">
		/*TsbGiftcardDao.getExpireGiftcardList*/
		SELECT  CG.CUST_NO 
		      , CG.CUST_GFCD_SQ 
		      , CG.CHG_GFCD_AMT 
		      , CG.RM_GFCD_AMT 
		      , CG.US_GFCD_AMT 
		      , CG.GFCD_NO 
		FROM TB_CUST_GIFTCARD CG
		WHERE USE_EXP_DATE <![CDATA[<]]> DATE_FORMAT(NOW(), '%Y%m%d')
		AND CG.RM_GFCD_AMT > 0
	</select>
	
	<insert id="saveExpireGiftcardHst" parameterType="Giftcard">
		/*TsbGiftcardDao.saveExpireGiftcardHst*/
		INSERT INTO TB_CUST_GIFTCARD_HST
		( 
		      CUST_NO
		    , OCCUR_GB
		    , OCCUR_DTL_DESC
		    , GFCD_AMT
		    , CUST_GFCD_SQ
		    , REG_NO
		    , REG_DT
		    , UPD_NO
		    , UPD_DT
		)
		VALUES(
		      #{custNo}
		    , 'G074_14'
		    , '유효기간만료'
		    , #{gfcdAmt}
		    , #{custGfcdSq}
		    , #{regNo}
		    , CURRENT_TIMESTAMP
		    , #{updNo}
		    , CURRENT_TIMESTAMP
		)
	</insert>
	
	<select id="getSendGiftcardExpectList" parameterType="Giftcard" resultType="Giftcard">
		/*TsbGiftcardDao.getSendGiftcardExpectList*/
		SELECT  CG.CUST_NO 
		      , CG.RM_GFCD_AMT
		      , DATE_FORMAT(CG.USE_EXP_DATE ,'%Y.%m.%d') AS USE_EXP_DATE 
		      , C.CUST_STAT   
		      , C.CUST_NM   
		      , C.CELL_PHNNO 
		FROM TB_CUST_GIFTCARD CG INNER JOIN TB_CUSTOMER C 
		                  ON CG.CUST_NO = C.CUST_NO 
		WHERE CG.USE_EXP_DATE = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 30 DAY),'%Y%m%d')
		AND CG.RM_GFCD_AMT > 0
		AND C.CUST_STAT = 'G104_10'
	</select>
</mapper>