<?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.TsbMigrationDao">
	
	<select id="getCustomerCnt" parameterType="Migration" resultType="int">
		/* TsbMigration.getCustomerCnt */
		SELECT COUNT(1) AS CNT
		FROM TB_CUSTOMER
		WHERE CUST_NO > #{custNo}
		  AND CUST_NO &lt;= (#{custNo} + 350000)
		  AND ENC_UPD_YN = 'N'
	</select>

	<select id="getCustomerList" parameterType="Migration" resultType="Migration">
		/* TsbMigration.getCustomerList */
		SELECT
			CUST_NO
			 , CUST_NM
			 , BIRTH_YMD
			 , SEX_GB
			 , CELL_PHNNO
			 , EMAIL
			 , HOME_ZIPCODE
			 , HOME_BASE_ADDR
			 , HOME_DTL_ADDR
		FROM TB_CUSTOMER
		WHERE CUST_NO > #{custNo}
			AND CUST_NO &lt;= (#{custNo} + 350000)
		    AND ENC_UPD_YN = 'N'
		ORDER BY CUST_NO
		LIMIT 0, #{rows}
	</select>

	<update id="updateCustomerEncrypt" parameterType="Migration">
		UPDATE TB_CUSTOMER
		SET CUST_NM = #{custNm}
		  , BIRTH_YMD = #{birthYmd}
		  , SEX_GB = #{sexGb}
		  , CELL_PHNNO = #{cellPhnno}
		  , EMAIL = #{email}
		  , HOME_ZIPCODE = #{homeZipcode}
		  , HOME_BASE_ADDR = #{homeBaseAddr}
		  , HOME_DTL_ADDR = #{homeDtlAddr}
		  , ENC_UPD_YN = 'Y'
		WHERE CUST_NO  = #{custNo}
		  AND ENC_UPD_YN = 'N'
	</update>

	<select id="getCustomerAllCnt" parameterType="Migration" resultType="int">
		/* TsbMigration.getCustomerAllCnt */
		SELECT COUNT(1) AS CNT
		FROM TB_CUSTOMER
		WHERE CUST_NO > 100000
			AND ENC_UPD_YN = 'N'
	</select>

	<select id="getCustomerListAll" parameterType="Migration" resultType="Migration">
		/* TsbMigration.getCustomerListAll */
		SELECT
			CUST_NO
			 , CUST_NM
			 , BIRTH_YMD
			 , SEX_GB
			 , CELL_PHNNO
			 , EMAIL
			 , HOME_ZIPCODE
			 , HOME_BASE_ADDR
			 , HOME_DTL_ADDR
		FROM TB_CUSTOMER
		WHERE CUST_NO > 100000
			AND ENC_UPD_YN = 'N'
		ORDER BY CUST_NO
		LIMIT #{startRow}, #{rows}
	</select>

	<select id="getCancelOrderDtlList" resultType="Migration">
		/* TsbMigration.getCancelOrderDtlList */
		SELECT
			A.ORD_CHG_SQ
			 , A.ORD_DTL_NO
			 , B.ORD_QTY
			 , B.ORD_AMT
			 , B.CNCL_RTN_QTY
			 , B.CNCL_RTN_AMT
			 , B.GOODS_CPN_DC_AMT
			 , B.CART_CPN_DC_AMT
			 , B.PNT_DC_AMT
			 , B.SAVE_PNT_AMT
			 , B.REAL_ORD_AMT
			 , B.GFCD_USE_AMT
			 , A.CHG_QTY
			 , B.ORD_DTL_STAT
		FROM tb_order_change_detail A, tb_order_detail B
		WHERE A.ORD_DTL_NO = B.ORD_DTL_NO
		  AND A.CHG_STAT = 'G685_XX'
		ORDER BY A.ORD_DTL_NO
	</select>

	<select id="getCancelOrderDtl" resultType="Migration">
		/* TsbMigration.getCancelOrderDtl */
		SELECT
			 B.ORD_QTY
			 , B.ORD_AMT
			 , B.CNCL_RTN_QTY
			 , B.CNCL_RTN_AMT
			 , B.GOODS_CPN_DC_AMT
			 , B.CART_CPN_DC_AMT
			 , B.PNT_DC_AMT
			 , B.SAVE_PNT_AMT
			 , B.REAL_ORD_AMT
			 , B.GFCD_USE_AMT
			 , B.ORD_DTL_STAT
		FROM tb_order_detail B
		WHERE ORD_DTL_NO = #{ordDtlNo}
	</select>

	<update id="updateOrderDetail" parameterType="Migration">
		/* TsbMigration.updateOrderDetail */
		UPDATE tb_order_detail A
		SET CNCL_RTN_QTY = #{cnclRtnQty}
		  , CNCL_RTN_AMT = #{cnclRtnAmt}
		  , GOODS_CPN_DC_AMT = #{goodsCpnDcAmt}
		  , CART_CPN_DC_AMT = #{cartCpnDcAmt}
		  , PNT_DC_AMT = #{pntDcAmt}
		  , SAVE_PNT_AMT = #{savePntAmt}
		  , REAL_ORD_AMT = #{realOrdAmt}
		  , GFCD_USE_AMT = #{gfcdUseAmt}
		  , UPD_NO = 0
		  , UPD_DT = (SELECT MAX(X.REG_DT) FROM tb_order_change_detail X WHERE X.ORD_DTL_NO = A.ORD_DTL_NO)
		  , ORD_DTL_STAT = #{ordDtlStat}
		WHERE ORD_DTL_NO = #{ordDtlNo}
	</update>

	<update id="updateOrderChangeDetail" parameterType="Migration">
		/* TsbMigration.updateOrderChangeDetail */
		UPDATE tb_order_change_detail A
		SET CHG_STAT = #{chgStat}
		WHERE ORD_CHG_SQ = #{ordChgSq}
		  AND ORD_DTL_NO = #{ordDtlNo}
	</update>

	<select id="getCustAccountCnt" resultType="int">
		/* TsbMigration.getCustAccountCnt */
		SELECT
			COUNT(1) AS CNT
		FROM TB_CUST_ACCOUNT
		WHERE ENC_UPD_YN = 'N'
	</select>

	<select id="getCustAccountList" parameterType="Migration" resultType="Migration">
		/* TsbMigration.getCustAccountList */
		SELECT
			CUST_NO
			 , ACCOUNT_NO
		FROM TB_CUST_ACCOUNT
		WHERE ENC_UPD_YN = 'N'
		ORDER BY CUST_NO
		LIMIT 0, #{rows}
	</select>

	<update id="updateCustAccountEnc" parameterType="Migration">
		/* TsbMigration.updateCustAccountEnc */
		UPDATE TB_CUST_ACCOUNT
		SET ACCOUNT_NO = #{accountNo}
			, ENC_UPD_YN = 'Y'
		WHERE CUST_NO = #{custNo}
			AND ENC_UPD_YN = 'N'
	</update>

	<select id="getRefundAccountCnt" resultType="int">
		/* TsbMigration.getRefundAccountCnt */
		SELECT
			COUNT(1) AS CNT
		FROM TB_REFUND
		WHERE RA_NO IS NOT NULL
		  AND RA_NO != ''
		  AND ENC_UPD_YN = 'N'
	</select>

	<select id="getRefundAccountList" parameterType="Migration" resultType="Migration">
		/* TsbMigration.getRefundAccountList */
		SELECT
			ORD_NO
			 , PAY_SQ
			 , ORD_CHG_SQ
			 , RA_NO
		FROM TB_REFUND
		WHERE RA_NO IS NOT NULL
		  AND RA_NO != ''
		  AND ENC_UPD_YN = 'N'
		ORDER BY ORD_NO, PAY_SQ, ORD_CHG_SQ
		LIMIT 0, #{rows}
	</select>

	<update id="updateRefundAccountEnc" parameterType="Migration">
		/* TsbMigration.updateRefundAccountEnc */
		UPDATE TB_REFUND
		SET RA_NO = #{raNo}
		  , ENC_UPD_YN = 'Y'
		WHERE ORD_NO = #{ordNo}
		  AND PAY_SQ = #{paySq}
		  AND ORD_CHG_SQ = #{ordChgSq}
		  AND ENC_UPD_YN = 'N'
	</update>
</mapper>