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

	<!-- 무통장입금 입금기한 만료 주문 조회 -->
	<select id="getOrderDepositExpirationList" parameterType="Order" resultType="Order">
		/* TsbOrder.getOrderDepositExpirationList : 무통장입금 입금기한 만료 주문 조회 */
		SELECT OD.ORD_NO
		FROM   TB_ORDER_DETAIL OD
		INNER  JOIN TB_PAYMENT PAY
		ON     OD.ORD_NO = PAY.ORD_NO
		WHERE  OD.ORD_DTL_STAT = 'G013_10'
		AND    PAY.PAY_STAT = 'G016_10'
		AND    PAY.PG_GB = 'KCP'
		AND    PAY.PAY_MEANS = 'G014_20'
		AND    PAY.VA_DEADLINE <![CDATA[ < ]]> NOW()
		AND    OD.REG_DT >= DATE_ADD(DATE_FORMAT('20210831160000','%Y%m%d%H%i%S') , INTERVAL 0 DAY)   -- 오픈 시점 날짜 세팅 후 주석풀기
		GROUP BY OD.ORD_NO
	</select>

	<!-- 선물하기 배송지 등록 기간 만료 주문 취소 -->
	<select id="getOrderGiftExpirationList" parameterType="Order" resultType="Order">
		/* TsbOrder.getOrderGiftExpirationList : 선물하기 배송지 등록 기간 만료 주문 취소 */
		SELECT O.ORD_NO
		     , O.CUST_NO
		FROM   TB_ORDER O
		INNER  JOIN TB_ORDER_DETAIL OD
		ON     OD.ORD_NO = O.ORD_NO
		WHERE  1=1
		AND    OD.GIFT_PACK_YN = 'Y'
		AND    OD.GIFT_ADDR_INP_YN = 'N'
		AND    OD.ORD_DTL_STAT = 'G013_20'
		AND    O.PAY_DT <![CDATA[ < ]]> ADDDATE(NOW(), INTERVAL -3 DAY)
		GROUP  BY O.ORD_NO
	</select>

	<!-- 무통장입금 만료대기자 리스트 조회 -->
	<select id="getOrderDepositWaitingCustomerList" parameterType="Order" resultType="Order">
		/* TsbOrder.getOrderDepositWaitingCustomerList : 무통장입금 만료대기자 리스트 조회 */
		SELECT O.ORD_NO
		     , IFNULL(O.CUST_NO, 0) AS CUST_NO
		     , O.ORD_NM
		     , O.ORD_PHNNO
		     , P.PAY_AMT
		     , P.VA_NO
		     , DATE_FORMAT(P.VA_DEADLINE, '%Y.%m.%d %H:%i:%S') as VA_DEADLINE
		     , FN_GET_CODE_NM('G940', P.VA_BANK) AS BANK_NM
		     , OD.GOODS_CNT
		     , G.GOODS_NM
		FROM   TB_PAYMENT P
		INNER  JOIN TB_ORDER O
		ON     P.ORD_NO = O.ORD_NO
		AND    O.PAY_DT <![CDATA[>=]]> STR_TO_DATE('20210831','%Y%m%d') /*오픈일로부터 구매한 건만*/
		INNER  JOIN (SELECT OD.ORD_NO
		                  , COUNT(1) - 1 AS GOODS_CNT
		                  , MAX(GOODS_CD) AS GOODS_CD
		             FROM   TB_ORDER_DETAIL OD
		             WHERE  1=1
		             AND    ORD_DTL_STAT = 'G013_10'
		             GROUP  BY ORD_NO ) OD
		ON     OD.ORD_NO = O.ORD_NO
		INNER  JOIN TB_GOODS G
		ON     OD.GOODS_CD = G.GOODS_CD
		LEFT   OUTER JOIN TB_PAYMENT P2
		ON     P.ORD_NO = P2.ORD_NO
		AND    P2.PAY_STAT = 'G016_98'
		WHERE  1=1
		AND    P2.ORD_NO IS NULL
		AND    P.PAY_STAT = 'G016_10'
		AND    CAST(P.VA_DEADLINE AS DATE) = CAST(NOW() AS DATE)
	</select>
	
	<!-- 네이버페이 결제형 구매확정 대상 리스트 조회 -->
	<select id="getNaverPayOrderDecisionList" resultType="Order">
		/* TsbOrder.getNaverPayOrderDecisionList */
		SELECT P.ORD_NO
		     , P.PG_TID
		  FROM TB_PAYMENT P
		  INNER JOIN TB_ORDER_DETAIL OD
		  ON P.ORD_NO = OD.ORD_NO
		 WHERE P.PG_GB = 'NAVER'
		   AND P.PAY_GB = 'O'
		   AND P.PAY_STAT = 'G016_30'
		   AND P.PAY_AMT <![CDATA[>]]> 0
		   AND OD.ORD_EXCH_GB = 'O'
		   AND OD.ORD_DTL_STAT IN ('G013_50', 'G013_60', 'G013_70')
		   AND P.NPAY_DECISION_YN = 'N'
		   AND P.PAY_DT <![CDATA[>]]> DATE_ADD(CURRENT_DATE, INTERVAL -15 DAY)
		 GROUP BY P.ORD_NO, P.PG_TID
	</select>
	
	<!-- 네이버페이 결제형 구매확정 여부 수정 -->
	<update id="updateNaverPayOrderDecision" parameterType="Order">
		/* TsbOrder.updateNaverPayOrderDecision */
		UPDATE TB_PAYMENT
		   SET NPAY_DECISION_YN = 'Y'
		     , UPD_NO = 99999
		     , UPD_DT = NOW()
		 WHERE ORD_NO = #{ordNo}
		   AND NPAY_DECISION_YN = 'N'
		   AND PAY_STAT = 'G016_30'
		   AND PAY_GB = 'O'
		   AND PAY_AMT <![CDATA[>]]> 0
	</update>

	<!-- 구매확정 임시테이블 데이터 삭제 -->
	<update id="deleteTempBuyConfirmData">
		/* TsbOrder.deleteTempBuyConfirmData : 구매확정 임시테이블 테이터 삭제 */
		DROP TABLE IF EXISTS TEMP_BUY_CONFIRM_DATA_BAK
	</update>

	<!-- 구매확정 템프 테이블 생성 -->
	<update id="createTableBuyConfirmDataTmp">
		/* TsbOrder.deleteTempBuyConfirmData : 구매확정 템프 테이블 생성 */
		/* CREATE TABLE TEMP_BUY_CONFIRM_DATA_TMP LIKE TEMP_BUY_CONFIRM_DATA */
		TRUNCATE TABLE TEMP_BUY_CONFIRM_DATA_TMP
	</update>

	<!-- 구매확정 임시테이블 데이터 생성 -->
	<insert id="createTempBuyConfirmData">
		/* TsbOrder.createTempBuyConfirmData : 구매확정 임시테이블 데이터 생성 */
		INSERT INTO TEMP_BUY_CONFIRM_DATA_TMP
		SELECT OD.ORD_DTL_NO
		FROM   TB_ORDER_DETAIL OD
		WHERE  OD.ORD_DTL_STAT = 'G013_60'
		AND    OD.REG_DT <![CDATA[>]]> '2021-08-15'
		AND    DATEDIFF(NOW(), OD.DELV_EDDT) <![CDATA[>]]> (
		                                        SELECT STR_SET_VAL2 -- 자동 구매확정처리일수(배송완료일 기준)
			                                    FROM   TB_ENVSET
			                                    WHERE  ENVSET_SQ = (
			                                                        SELECT MAX(ENVSET_SQ)
			                                                        FROM   TB_ENVSET
			                                                        WHERE  SITE_CD = 'G000_10'
			                                                        AND    ENVSET_TYPE = 'D10'
			                                                       )
			                                   )
		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'
			              )
	</insert>

	<!-- 테이블명 DataBak -> Data 변경 -->
	<update id="renameBuyConfirmDataBakToData">
		/* TsbOrder.renameBuyConfirmDataBakToData : 테이블명 DataBak -> Data 변경 */
		RENAME TABLE TEMP_BUY_CONFIRM_DATA TO TEMP_BUY_CONFIRM_DATA_BAK
	</update>

	<!-- 테이블명 Data -> DataTmp 변경 -->
	<update id="renameBuyConfirmDataToTmp">
		/* TsbOrder.renameBuyConfirmDataToTmp : 테이블명 Data -> DataTmp 변경 */
		RENAME TABLE TEMP_BUY_CONFIRM_DATA_TMP TO TEMP_BUY_CONFIRM_DATA
	</update>

	<!-- 주문상세 상태 구매확정 변경 -->
	<update id="updateOrderDetailStatBuyConfirm">
		/* TsbOrder.updateOrderDetailStatBuyConfirm : 주문상세 상태 구매확정 변경 */
		UPDATE TB_ORDER_DETAIL
		SET    ORD_DTL_STAT = 'G013_70' -- 구매확정
		     , UPD_NO = '99999' -- 배치 : 99999
		     , UPD_DT = NOW()
		WHERE  ORD_DTL_NO IN (
		                      SELECT ORD_DTL_NO
		                      FROM   TEMP_BUY_CONFIRM_DATA_TMP
		                     )
	</update>

	<!-- 구매확정 주문상세이력 생성-->
	<insert id="createOrderDetailHstBuyConfirm">
		/* Order.createOrderDetailHstBuyConfirm : 구매확정 주문상세이력 생성 */
		INSERT INTO TB_ORDER_DETAIL_HST (
										  ORD_DTL_NO
										, ORD_NO
										, ORD_EXCH_GB
										, ORD_DTL_STAT
										, ORG_ORD_DTL_NO
										, SUPPLY_COMP_CD
										, GOODS_CD
										, DEAL_GOODS_CD
										, FORMAL_GB
										, GOODS_TYPE
										, LIST_PRICE
										, CURR_PRICE
										, DC_RATE
										, OPT_ADD_PRICE
										, ORD_QTY
										, ORD_AMT
										, CNCL_RTN_QTY
										, CNCL_RTN_AMT
										, CPN1_CPN_SQ
										, CPN1_DC_AMT
										, TMTB1_SQ
										, TMTB1_DC_AMT
										, TMTB2_SQ
										, TMTB2_DC_AMT
										, GOODS_CPN_SQ
										, GOODS_CPN_DC_AMT
										, CART_CPN_SQ
										, CART_CPN_DC_AMT
										, BURDEN_RATE
										, PNT_DC_AMT
										, PRE_PNT_DC_AMT
										, SAVE_PNT_AMT
										, REAL_ORD_AMT
										, GFCD_USE_AMT
										, VENDOR_ID
										, EXTMALL_ID
										, AGENT_ORDER_ID
										, EXTMALL_ORDER_ID
										, CHANGEABLE_YN
										, CHANGE_FEE_FREE_YN
										, RETURNABLE_YN
										, RETURN_FEE_FREE_YN
										, SOLDOUT_YN
										, SOLDOUT_MEMO
										, SOLDOUT_REG_NO
										, SOLDOUT_REG_DT
										, DELV_ADDR_SQ
										, DELV_FEE_CD
										, SHOT_DELV_YN
										, GIFT_PACK_YN
										, GIFT_ADDR_INP_YN
										, MAKE_GOODS_YN
										, ENTRY_NO
										, DELV_LOC_CD
										, DELV_ASSIGN_DT
										, DELV_ASSIGN_STAT
										, DSTRBT_NOTE
										, DELV_STDT
										, DELV_EDDT
										, SHIP_COMP_CD
										, INVOICE_NO
										, INVOICE_SEND_YN
										, SELL_STORE_CD
										, SELL_FEE_RATE
										, AF_LINK_CD
										, ITHR_CD
										, CONTENTS_LOC
										, PLAN_DTL_SQ
										, SOCIAL_SQ
										, REG_NO
										, REG_DT
										, UPD_NO
										, UPD_DT
		)
		SELECT ORD_DTL_NO
			 , ORD_NO
			 , ORD_EXCH_GB
			 , ORD_DTL_STAT
			 , ORG_ORD_DTL_NO
			 , SUPPLY_COMP_CD
			 , GOODS_CD
			 , DEAL_GOODS_CD
			 , FORMAL_GB
			 , GOODS_TYPE
			 , LIST_PRICE
			 , CURR_PRICE
			 , DC_RATE
			 , OPT_ADD_PRICE
			 , ORD_QTY
			 , ORD_AMT
			 , CNCL_RTN_QTY
			 , CNCL_RTN_AMT
			 , CPN1_CPN_SQ
			 , CPN1_DC_AMT
			 , TMTB1_SQ
			 , TMTB1_DC_AMT
			 , TMTB2_SQ
			 , TMTB2_DC_AMT
			 , GOODS_CPN_SQ
			 , GOODS_CPN_DC_AMT
			 , CART_CPN_SQ
			 , CART_CPN_DC_AMT
			 , BURDEN_RATE
			 , PNT_DC_AMT
			 , PRE_PNT_DC_AMT
			 , SAVE_PNT_AMT
			 , REAL_ORD_AMT
			 , GFCD_USE_AMT
			 , VENDOR_ID
			 , EXTMALL_ID
			 , AGENT_ORDER_ID
			 , EXTMALL_ORDER_ID
			 , CHANGEABLE_YN
			 , CHANGE_FEE_FREE_YN
			 , RETURNABLE_YN
			 , RETURN_FEE_FREE_YN
			 , SOLDOUT_YN
			 , SOLDOUT_MEMO
			 , SOLDOUT_REG_NO
			 , SOLDOUT_REG_DT
			 , DELV_ADDR_SQ
			 , DELV_FEE_CD
			 , SHOT_DELV_YN
			 , GIFT_PACK_YN
			 , GIFT_ADDR_INP_YN
			 , MAKE_GOODS_YN
			 , ENTRY_NO
			 , DELV_LOC_CD
			 , DELV_ASSIGN_DT
			 , DELV_ASSIGN_STAT
			 , DSTRBT_NOTE
			 , DELV_STDT
			 , DELV_EDDT
			 , SHIP_COMP_CD
			 , INVOICE_NO
			 , INVOICE_SEND_YN
			 , SELL_STORE_CD
			 , SELL_FEE_RATE
			 , AF_LINK_CD
			 , ITHR_CD
			 , CONTENTS_LOC
			 , PLAN_DTL_SQ
			 , SOCIAL_SQ
			 , '99999' -- 배치 : 99999
			 , NOW()
			 , '99999' -- 배치 : 99999
			 , NOW()
		FROM   TB_ORDER_DETAIL
		WHERE  ORD_DTL_NO IN (
			                  SELECT ORD_DTL_NO
			                  FROM   TEMP_BUY_CONFIRM_DATA_TMP
		                     )
	</insert>

	<!-- 주문상세단품 구매확정 -->
	<update id="updateOrderDetailItemStatBuyConfirm">
		/* Order.updateOrderDetailItemStatBuyConfirm : 주문상세단품 구매확정 */
		UPDATE TB_ORDER_DETAIL_ITEM
		SET    ORD_DTL_STAT = 'G013_70' -- 구매확정
		     , UPD_NO = '99999' -- 배치 : 99999
		     , UPD_DT = NOW()
		WHERE  ORD_DTL_NO IN (
			                  SELECT ORD_DTL_NO
			                  FROM   TEMP_BUY_CONFIRM_DATA_TMP
		                     )
	</update>

</mapper>