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

	<!-- 네이버페이 조회 시간(최근 3시간) -->
	<select id="getSelectDt" resultType="Order">
		/* TsbNaverPay.getSelectDt : 네이버페이 조회 시간(최근 3시간) */
		SELECT CONCAT(DATE_FORMAT(NOW() + 'INTERVAL -3 HOUR', '%Y%m%d%H'), '0000') AS STDT
			 , CONCAT(DATE_FORMAT(NOW() + 'INTERVAL -3 HOUR', '%Y%m%d%H'), '5959') AS STDT
		FROM   DUAL
	</select>

	<!-- 네이버페이 수신 정보 주문 데이터 확인 -->
	<select id="getOrdBaseInfo" parameterType="Order" resultType="Order">
		/* TsbNaverPay.getOrdBaseInfo : 네이버페이 수신 정보 주문 데이터 확인 (결제 대기 > 완료 데이터 조회)  */
		SELECT O.ORD_NO
			 , O.NPAY_ORD_NO
		     , O.CUST_NO
			 , OD.ORD_DTL_NO
			 , OD.GOODS_CD
			 , OD.NPAY_ORD_DTL_NO
			 , G.GOODS_TYPE
		FROM   TB_ORDER O
		INNER  JOIN TB_ORDER_DETAIL OD
		ON     O.ORD_NO = OD.ORD_NO
		INNER  JOIN TB_GOODS G
		ON     OD.GOODS_CD = G.GOODS_CD
		WHERE  1=1
		AND    O.ORD_NO = #{ordNo}
		<if test="npayOrdNo != null and npayOrdNo != ''">
		AND    O.NPAY_ORD_NO = #{npayOrdNo}
		</if>
		AND    G.GOODS_CD = #{goodsCd}
		AND    OD.ORD_DTL_STAT = 'G013_00'
	</select>

	<!-- 네이버페이 상품주문번호(주문상세번호) 등록 -->
	<update id="updateNpayOrdDtlNo" parameterType="Order">
		/* TsbNaverPay.updateNpayOrdDtlNo : 네이버페이 상품주문번호(주문상세번호) 등록 */
		UPDATE TB_ORDER_DETAIL SET
		      NPAY_ORD_DTL_NO = #{npayOrdDtlNo}
		    , ORD_DTL_STAT    = #{ordDtlStat}
		    , UPD_NO          = 0
		    , UPD_DT          = NOW()
		WHERE  ORD_DTL_NO = #{ordDtlNo}
		AND    IFNULL(NPAY_ORD_DTL_NO, 0) = 0
	</update>

	<!-- 네이버페이 결제완료 수신 상품 세트 옵션 여부 확인 -->
	<select id="getIsNaverPayItem" parameterType="Order" resultType="int">
		/* TsbNaverPay.getIsNaverPayItem : 네이버페이 결제완료 수신 상품 세트 옵션 여부 확인 */
		SELECT COUNT(1) AS CNT
		FROM   TB_ORDER_DETAIL_ITEM
		WHERE  ORD_DTL_NO = #{ordDtlNo}
		AND    OPT_CD = #{optCd}
	</select>

	<!-- 네이버페이 배송 주소 변경 -->
	<update id="updateShippingAddressInfo" parameterType="Order">
		/* TsbNaverPay.updateShippingAddressInfo : 네이버페이 배송 주소 변경 */
		UPDATE TB_DELIVERY_ADDR SET
		        RECIP_NM = #{recipNm}
		      , RECIP_PHNNO = #{recipPhnno}
		      , RECIP_TELNO = #{recipTelno}
		      , RECIP_ZIPCODE = #{recipZipcode}
		      , RECIP_BASE_ADDR = #{recipBaseAddr}
		      , RECIP_DTL_ADDR = #{recipDtlAddr}
		      , delv_memo = #{delvMemo}
		      , UPD_NO = 0
		      , UPD_DT = NOW()
		WHERE  1=1
		AND    DELV_ADDR_SQ = (SELECT OD.DELV_ADDR_SQ
		                       FROM   TB_ORDER_DETAIL OD
		                       WHERE  OD.ORD_DTL_NO = #{ordDtlNo})
	</update>

	<!-- 네이버페이 결제완료 정보 PAYMENT UPDATE -->
	<update id="updateNaverpayPaymentInfo" parameterType="Payment">
		/* TsbNaverPay.updateNaverpayPaymentInfo : 네이버페이 결제완료 정보 PAYMENT UPDATE */
		UPDATE TB_PAYMENT SET
		      PAY_STAT = #{payStat}
		    , PG_TID = #{pgTid}
		    , UPD_NO = 0
		    , UPD_DT = NOW()
		WHERE  ORD_NO = #{ordNo}
	</update>

	<!-- 네이버 상품주문번호 정보 조회 -->
	<select id="getProductOrderIdInfo" parameterType="Order" resultType="Order">
		/* TsbNaverPay.getProductOrderIdInfo : 네이버 상품주문번호 정보 조회 */
		SELECT OD.ORD_NO
		     , OD.ORD_DTL_NO
		     , OD.ORD_DTL_STAT
		     , OD.NPAY_ORD_DTL_NO
		     , OD.ORD_QTY
		     , P.PG_GB
		     , P.PAY_MEANS
		     , O.NPAY_ORD_NO
			 , O.CUST_NO
		     , O.ORD_NM
		     , O.ORD_PHNNO
		FROM   TB_ORDER O
		INNER  JOIN TB_ORDER_DETAIL OD
		ON     OD.ORD_NO = O.ORD_NO
		INNER  JOIN TB_PAYMENT P
		ON     P.ORD_NO = O.ORD_NO
		WHERE  OD.NPAY_ORD_DTL_NO = #{npayOrdDtlNo}
		AND    P.PG_GB = #{pgGb}
		AND    P.PAY_STAT = 'G016_30'
		AND    P.PAY_GB = 'O'
		AND    O.NPAY_ORD_NO IS NOT NULL
		AND    O.NPAY_ORD_NO > 0
	</select>

	<!-- 네이버 상품주문번호 취소 수량 확인 -->
	<select id="getOrdDtlCancelCheck" parameterType="Order" resultType="int">
		/* TsbNaverPay.getOrdDtlCancelCheck : 네이버 상품주문번호 취소 수량 확인 */
		SELECT COUNT(1)
		FROM   TB_ORDER O
		INNER  JOIN TB_ORDER_DETAIL OD
		ON     O.ORD_NO = OD.ORD_NO
		INNER  JOIN TB_PAYMENT P
		ON     O.ORD_NO = P.ORD_NO
		INNER  JOIN TB_ORDER_CHANGE_DETAIL OCD
		ON     OD.ORD_DTL_NO = OCD.ORD_DTL_NO
		WHERE  1=1
		AND    O.ORD_NO = #{ordNo}
		AND    OD.NPAY_ORD_DTL_NO = #{npayOrdDtlNo}
		AND    P.PG_GB = #{pgGb}
		AND    P.PAY_STAT = 'G016_30'
		AND    P.PAY_GB = 'O'
		<if test="chgGb == null or chgGb == ''">
		AND    OCD.CHG_STAT IN ('G685_10', 'G685_11', 'G685_17', 'G685_18')
		</if>
		<if test="chgGb != null and chgGb == 'COMPANION'">
		AND    OCD.CHG_STAT = 'G685_19'
		</if>
		<if test="chgGb != null and chgGb == 'CANCEL_COMPLETE'">
		AND    OCD.CHG_STAT = 'G685_18'
		</if>
	</select>

	<!-- 배송준비중 주문 조회 -->
	<select id="getReadyDeliveryList" parameterType="Order" resultType="Order">
		/* TsbNaverPay.getReadyDeliveryList : 배송준비중 주문 조회 */
		SELECT OD.ORD_NO
		     , OD.ORD_DTL_NO
		     , OD.ORD_DTL_STAT
		     , OD.NPAY_ORD_DTL_NO
		     , OD.DELV_ADDR_SQ
		FROM   TB_ORDER_DETAIL OD
		INNER  JOIN TB_PAYMENT P
		ON     OD.ORD_NO = P.ORD_NO
		AND    P.PAY_GB = 'O'
		AND    P.PAY_STAT = 'G016_30'
		LEFT   OUTER JOIN TB_ORDER_CHANGE_DETAIL OCD
		ON     OD.ORD_DTL_NO = OCD.ORD_DTL_NO
		AND    OCD.CHG_STAT NOT IN ('G685_19')
		WHERE  1=1
		AND    OCD.ORD_DTL_NO IS NULL
		AND    OD.ORD_EXCH_GB = 'O'
		AND    OD.ORD_QTY - OD.CNCL_RTN_QTY > 0
		AND    OD.ORD_DTL_STAT = #{ordDtlStat}
		AND    P.PG_GB = #{pgGb}
	</select>

	<!-- 구매확정 대상 목록(배송중, 출고완료, 배송완료) 정보 조회 -->
	<select id="getOrderConfirmTargetList" parameterType="Order" resultType="Order">
		/* TsbNaverPay.getOrderConfirmTargetList : 구매확정 대상 목록(배송중, 출고완료, 배송완료) 정보 조회 */
		SELECT OD.ORD_NO
		     , OD.ORD_DTL_NO
		     , OD.NPAY_ORD_DTL_NO
		     , OD.ORD_DTL_STAT
		FROM   TB_ORDER_DETAIL OD
		WHERE  1=1
		AND    OD.ORD_NO = #{ordNo}
		AND    OD.NPAY_ORD_DTL_NO = #{npayOrdDtlNo}
		AND    OD.ORD_QTY - OD.CNCL_RTN_QTY > 0
		AND    OD.ORD_DTL_STAT IN ('G013_50', 'G013_55', 'G013_60')
	</select>

	<!-- 교환 접수 대상 상품 정보 조회(교환진행중인 주문 제외) -->
	<select id="getNotExchangeOrderInfoList" parameterType="Order" resultType="Order">
		/* TsbNaverPay.getNotExchangeOrderInfoList : 교환 접수 대상 상품 정보 조회(교환진행중인 주문 제외) */
		SELECT O.CUST_NO
		     , OD.ORD_DTL_NO
		     , OD.ORD_QTY
		     , OD.NPAY_ORD_DTL_NO
		     , ODI.ORD_DTL_ITEM_SQ
		     , ODI.OPT_CD2
		     , G.SUPPLY_COMP_CD
		     , G.SELF_GOODS_YN
		     , G.DELV_FEE_CD
		     , DFP.DELV_FEE -- 출고배송비
		     , DFP.RTN_DELV_FEE -- 반품배송비
		FROM   TB_ORDER O
		INNER  JOIN TB_ORDER_DETAIL OD
		ON     O.ORD_NO = OD.ORD_NO
		INNER  JOIN TB_ORDER_DETAIL_ITEM ODI
		ON     OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		INNER  JOIN TB_GOODS G
		ON     OD.GOODS_CD = G.GOODS_CD
		INNER  JOIN TB_DELV_FEE_POLICY DFP
		ON     OD.DELV_FEE_CD = DFP.DELV_FEE_CD
		AND    DFP.USE_YN = 'Y'
		LEFT   OUTER JOIN TB_ORDER_CHANGE_DETAIL OCD
		ON     OCD.ORD_DTL_NO = OD.ORD_DTL_NO
		AND    OCD.CHG_STAT IN ('G685_20', 'G685_30', 'G685_31', 'G685_32', 'G685_34')
		WHERE  OCD.ORD_DTL_NO IS NULL
		AND    OD.ORD_DTL_STAT <![CDATA[ <> ]]> 'G013_97'
		<if test="claimNpayOrdDtlNos != null and claimNpayOrdDtlNos != ''">
		AND    OD.NPAY_ORD_DTL_NO IN
			<foreach collection="claimNpayOrdDtlNos" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
	</select>

	<!-- 반품 접수 대상 상품 정보 조회(반품진행중인 주문 제외) -->
	<select id="getNotReturnOrderInfoList" parameterType="Order" resultType="Order">
		/* TsbNaverPay.getNotReturnOrderInfoList : 반품 접수 대상 상품 정보 조회(반품진행중인 주문 제외) */
		SELECT O.CUST_NO
		     , O.ORD_NM
		     , O.CUST_NO
		     , O.ORD_PHNNO
		     , OD.ORD_DTL_NO
		     , OD.ORD_QTY
		     , OD.NPAY_ORD_DTL_NO
		     , G.SUPPLY_COMP_CD
		     , G.SELF_GOODS_YN
		     , G.DELV_FEE_CD
		     , DFP.DELV_FEE -- 출고배송비
		     , DFP.RTN_DELV_FEE -- 반품배송비
		FROM   TB_ORDER O
		INNER  JOIN TB_ORDER_DETAIL OD
		ON     O.ORD_NO = OD.ORD_NO
		INNER  JOIN TB_GOODS G
		ON     OD.GOODS_CD = G.GOODS_CD
		INNER  JOIN TB_DELV_FEE_POLICY DFP
		ON     OD.DELV_FEE_CD = DFP.DELV_FEE_CD
		AND    DFP.USE_YN = 'Y'
		LEFT   OUTER JOIN TB_ORDER_CHANGE_DETAIL OCD
		ON     OCD.ORD_DTL_NO = OD.ORD_DTL_NO
		AND    OCD.CHG_STAT IN ('G685_50', 'G685_51', 'G685_60')
		WHERE  OCD.ORD_DTL_NO IS NULL
		AND    OD.ORD_DTL_STAT <![CDATA[ <> ]]> 'G013_97'
		<if test="claimNpayOrdDtlNos != null and claimNpayOrdDtlNos != ''">
		AND    OD.NPAY_ORD_DTL_NO IN
			<foreach collection="claimNpayOrdDtlNos" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
	</select>

	<!-- 네이버페이 배송업체 코드 > 쇼핑몰 배송업체 코드 조회 -->
	<select id="getNaverPayShipCompanyCd" parameterType="Order" resultType="Order">
		/* TsbNaverPay.getNaverPayShipCompanyCd : 네이버페이 배송업체 코드 > 쇼핑몰 배송업체 코드 조회 */
		SELECT SHIP_COMP_CD
		FROM   TB_SHIP_COMPANY
		WHERE  NAVER_PAY_SHIP_COMP_CD = #{naverPayShipCompCd}
		AND    USE_YN = 'Y'
	</select>

	<!-- 회수송장번호 등록 필요 리스트 조회 -->
	<select id="getNotWriteWdInvoiceNoChangeList" resultType="Order">
		/* TsbNaverPay.getNotWriteWdInvoiceNoChangeList : 회수송장번호 등록 필요 리스트 조회 */
		SELECT OD.ORD_NO
		     , OD.ORD_DTL_NO
		     , OD.NPAY_ORD_DTL_NO
		     , OD.ORD_DTL_STAT
		     , OC.WD_INVOICE_NO
		     , OCD.CHG_STAT
		FROM   TB_ORDER_CHANGE OC
		INNER  JOIN TB_ORDER_CHANGE_DETAIL OCD
		ON     OC.ORD_CHG_SQ = OCD.ORD_CHG_SQ
		INNER  JOIN TB_ORDER_DETAIL OD
		ON     OCD.CHG_ORD_DTL_NO = OD.ORD_DTL_NO
		INNER  JOIN TB_PAYMENT P
		ON     OD.ORD_NO = P.ORD_NO
		AND    P.PG_GB = 'NAVER_ORDER'
		AND    P.PAY_GB = 'O'
		AND    P.PAY_STAT = 'G016_30'
		WHERE  OCD.CHG_STAT IN ('G685_20', 'G685_50')
		AND    NULLIF(OC.WD_INVOICE_NO, '') IS NULL
	</select>

	<!-- 네이버페이 회수송장번호 등록 -->
	<update id="updateNaverPayWdInvoiceNo" parameterType="Order">
		/* TsbNaverPay.updateNaverPayWdInvoiceNo : 네이버페이 회수송장번호 등록 */
		UPDATE TB_ORDER_CHANGE OC SET
		       OC.SHIP_COMP_CD = (SELECT SC.SHIP_COMP_CD FROM TB_SHIP_COMPANY SC WHERE SC.NAVER_PAY_SHIP_COMP_CD = #{naverPayShipCompCd})
		     , OC.WD_INVOICE_NO = #{wdInvoiceNo}
		     , OC.UPD_NO = #{updNo}
		     , OC.UPD_DT = NOW()
		WHERE  OC.ORD_CHG_SQ IN (SELECT ORD_CHG_SQ
		                         FROM   TB_ORDER_DETAIL OD
		                         INNER  JOIN TB_ORDER_CHANGE_DETAIL OCD
		                         ON     OD.ORD_DTL_NO = OCD.CHG_ORD_DTL_NO
		                         WHERE  OCD.CHG_STAT = #{chgOrdDtlStat}
		                         AND    OD.NPAY_ORD_DTL_NO = #{npayOrdDtlNo})
	</update>
</mapper>