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

	<!--자동구매확정 포인트 전환 대상-->
	<select id="getAutoBuyConfirmList" parameterType="Point" resultType="Point">
		/*TsbPointDao.getAutoBuyConfirmList*/
		SELECT ORD_NO
		     , ORD_DTL_NO
		     , CPH.CUST_NO
		     , SUM(PNT_AMT) AS PNT_AMT
		     , CONCAT(CURRENT_DATE + INTERVAL #{buyPointExpireDays} DAY, ' 23:59:59') AS EXP_BE_DT
		FROM  TB_CUST_POINT_HST CPH
		INNER JOIN
		      TB_CUSTOMER C
		ON    CPH.CUST_NO = C.CUST_NO
		AND   C.CUST_STAT = 'G104_10'
		WHERE CPH.OCCUR_GB IN ('G069_12', 'G069_13')
		AND   CPH.PNT_UPLOAD_STAT = 'G070_10'
		AND   CPH.ORD_DTL_NO IN (
		                         SELECT OD.ORD_DTL_NO
		                         FROM   TB_ORDER_DETAIL OD
		                         WHERE  OD.ORD_DTL_STAT = 'G013_60'
		                         AND    DATEDIFF(NOW(), OD.DELV_EDDT) > #{autoBuyConfirmDays}
		                         AND    NOT EXISTS (
		                                            SELECT 1
		                                            FROM  TB_ORDER_CHANGE OC
		                                            INNER JOIN
		                                                  TB_ORDER_CHANGE_DETAIL OCD
		                                            ON    OC.ORD_CHG_SQ = OCD.ORD_CHG_SQ
		                                            WHERE OCD.ORD_DTL_NO = OD.ORD_DTL_NO
		                                            AND   OCD.CHG_STAT IN ('G685_20', 'G685_21', 'G685_30', 'G685_31', 'G685_32', 'G685_33', 'G685_34', 'G685_50', 'G685_51')
		                                            AND   OCD.DEL_YN = 'N'
			                                       )
		                         )
		GROUP BY CPH.ORD_NO, CPH.ORD_DTL_NO , CPH.CUST_NO
		HAVING SUM(PNT_AMT) >= 0
	</select>
	
	<select id="getExpirePointList" parameterType="Point" resultType="Point">
		/*TsbPointDao.getExpirePointList*/
		SELECT CP.CUST_PNT_SQ 
		      ,CP.CUST_NO 
		      ,CP.GV_PNT_AMT 
		      ,CP.RM_PNT_AMT 
		      ,CP.EXP_BE_DT 
		FROM TB_CUST_POINT CP
		WHERE  1=1
		AND CP.EXP_BE_DT <![CDATA[<=]]> DATE_FORMAT(CONCAT(CURRENT_DATE(),' 23:59:59'), '%Y%m%d%H%i%S')
		AND CP.RM_PNT_AMT > 0
		AND EXP_CMP_DT IS NULL
	</select>
	
	<insert id="saveExpirePointHst" parameterType="Point">
		/*TsbPointDao.saveExpirePointHst*/
		INSERT INTO TB_CUST_POINT_HST (
		       CUST_NO
		     , OCCUR_GB
		     , OCCUR_DTL_DESC
		     , PNT_AMT
		     , CUST_PNT_SQ
		     , PNT_UPLOAD_STAT
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES(
		       #{custNo}
		     , 'G069_99'
		     , '포인트 소멸'
		     , #{pntAmt}
		     , #{custPntSq}
		     , 'G070_30'
		     , #{regNo}
		     , CURRENT_TIMESTAMP
		     , #{updNo}
		     , CURRENT_TIMESTAMP
		)
	</insert>

	<update id="updateCustPointExpire" parameterType="Point">
		/*TsbPointDao.updateCustPointExpire*/
		UPDATE TB_CUST_POINT
		SET    EXP_CMP_DT = CURRENT_TIMESTAMP
		     , UPD_NO = #{updNo}
		     , UPD_DT = CURRENT_TIMESTAMP
		WHERE  CUST_PNT_SQ = #{custPntSq}
	</update>
	
	<select id="getSendPointExpectList" parameterType="Point" resultType="Point">
		/*TsbPointDao.getSendPointExpectList*/
		SELECT  C.CUST_NO 
		      , C.CUST_NM 
		      , C.CELL_PHNNO 
		      , C.EMAIL
		      , DATE_FORMAT(CP.EXP_BE_DT, '%Y.%m.%d') AS EXP_BE_DT
		      , CP.RM_PNT_AMT 
		      , DATE_FORMAT(NOW(),'%Y.%m.%d %H:%i') AS TODAY
		FROM TB_CUST_POINT CP INNER JOIN TB_CUSTOMER C 
		                  ON CP.CUST_NO = C.CUST_NO 
		WHERE CP.EXP_BE_DT BETWEEN CONCAT(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 30 DAY),'%Y-%m-%d'), ' 00:00:00') AND CONCAT(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 30 DAY),'%Y-%m-%d'), ' 23:59:59')
		AND CP.RM_PNT_AMT > 0
		AND CP.EXP_CMP_DT IS NULL
		AND C.CUST_STAT = 'G104_10' /*활동회원 만 */
	</select>
	
</mapper>