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

	<!-- 상품평등록안내발송대상건 생성 -->
	<insert id="createReviewGuideSendObjectList" parameterType="int">
		/* TsbReviewDao.createReviewGuideSendObjectList */
		INSERT INTO TB_REVIEW_GUIDE (
		       CUST_NO
		     , ORD_NO
		     , ORD_DTL_NO
		     , GOODS_CD
		     , SEND_YN
		     , REG_DT
		)
		SELECT O.CUST_NO
		     , OD.ORD_NO
		     , OD.ORD_DTL_NO
		     , OD.GOODS_CD
		     , 'N'           AS SEND_YN
		     , NOW()         AS REG_DT
		FROM   TB_ORDER O
		     , TB_ORDER_DETAIL OD
		WHERE  O.ORD_NO = OD.ORD_NO
		AND    O.ORD_DT <![CDATA[>=]]> DATE_ADD(NOW(), INTERVAL -1 * #{expireDays} DAY) /*구매일로부터 90일 이내*/
		AND    OD.ORD_DTL_STAT IN ('G013_50','G013_60','G013_70') /*배송중,배송완료,구매확정*/
		AND    NOT EXISTS (SELECT 1
		                   FROM   TB_REVIEW_GUIDE
		                   WHERE  CUST_NO = O.CUST_NO
		                   AND    ORD_NO = OD.ORD_NO
		                   AND    ORD_DTL_NO = OD.ORD_DTL_NO
		                   AND    GOODS_CD = OD.GOODS_CD
		                  )
	</insert>
	
	<!-- 상품평등록안내발송대상 목록 -->
	<select id="getReviewGuideSendObjectList" resultType="ReviewGuide">
		/* TsbReviewDao.getReviewGuideSendObjectList */
		SELECT RG.CUST_NO
		     , RG.ORD_NO
		     , RG.ORD_DTL_NO
		     , RG.GOODS_CD
		     , C.CUST_NM
		     , C.CELL_PHNNO
		FROM   TB_REVIEW_GUIDE RG
		     , TB_CUSTOMER C
		WHERE  RG.CUST_NO = C.CUST_NO
		AND    RG.SEND_YN = 'N' /*미발송건*/
	</select>
	
	<!-- 상품평등록안내발송완료 처리 -->
	<update id="updateReviewGuideSendComplete" parameterType="ReviewGuide">
		/* TsbReviewDao.updateReviewGuideSendComplete */
		UPDATE TB_REVIEW_GUIDE
		SET    SEND_YN = 'Y'
		WHERE  CUST_NO = #{custNo}
		AND    ORD_NO = #{ordNo}
		AND    ORD_DTL_NO = #{ordDtlNo}
		AND    GOODS_CD = #{goodsCd}
	</update>
	
</mapper>