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

	<!-- 소셜상품 가격정보 원복(before) 업데이트 -->
	<update id="updateGoodsBeforePriceInfo">
		/* TsbSocial.updateGoodsBeforePriceInfo */
		UPDATE TB_GOODS A
			INNER JOIN TB_SOCIAL_GOODS B ON A.GOODS_CD = B.GOODS_CD 
			INNER JOIN TB_SOCIAL C ON B.SOCIAL_SQ = C.SOCIAL_SQ 								
		SET A.GOODS_CD = B.GOODS_CD ,
		    A.CURR_PRICE = B.CURR_BPRICE,
		    A.DC_RATE = B.DC_BRATE,
		    A.UPD_DT = NOW(),
		    A.UPD_NO = #{updNo}
		WHERE C.USE_YN = 'Y'
		  AND C.APPLY_GB = 'A'
		  AND B.DEL_YN = 'N'
		  AND C.SOCIAL_EDDT <![CDATA[<=]]> DATE_FORMAT(DATE_ADD(CONCAT(CURRENT_DATE(),' 23:59:59'), INTERVAL 0 DAY), '%Y%m%d%H%i%S')
	</update>
	
	<!-- 원복(before) 상품 이력 생성 -->
	<insert id="createBeforeGoodsHst" parameterType="Social">
		/* TsbSocial.createBeforeGoodsHst */
	 		INSERT INTO TB_GOODS_HST 
			(   
				GOODS_CD		
			  , BRAND_CD
			  , ITEMKIND_CD
			  , GOODS_NM
			  , GOODS_TNM
			  , GOODS_SNM
			  , GOODS_SNM1
			  , MAIN_COLOR_CD
			  , STYLE_YEAR
			  , SEASON_CD
			  , SEX_GB
			  , GOODS_NUM
			  , GOODS_TYPE
			  , TAG_PRICE
			  , LIST_PRICE
			  , CURR_PRICE
			  , CURR_BPRICE
			  , PRICE_UPD_DT
			  , COST_PRICE
			  , DC_RATE
			  , GOODS_STAT
			  , SELL_STDT
			  , SELL_EDDT
			  , SELF_MALL_YN
			  , GOODS_GB
			  , FOREIGN_BUY_YN
			  , PARALLEL_IMPORT_YN
			  , ORDER_MADE_YN
			  , DISTRIBUTION_GB
			  , SELF_GOODS_YN
			  , SUPPLY_COMP_CD
			  , SUPPLY_GOODS_CD
			  , AGE_GRP_CD
			  , DELV_LOC_CD
			  , DELV_FEE_CD
			  , DELV_FEE
			  , MIN_ORD_AMT
			  , PNT_PRATE
			  , PNT_MRATE
			  , SELL_FEE_RATE
			  , FORMAL_GB
			  , CHANGEABLE_YN
			  , RETURNABLE_YN
			  , CHANGE_FEE_FREE_YN
			  , RETURN_FEE_FREE_YN
			  , PRE_PPNT_USABLE_YN
			  , PRE_MPNT_USABLE_YN
			  , MIN_ORD_QTY
			  , MAX_ORD_QTY
			  , DAY_MAX_ORD_QTY
			  , GIFT_PACK_YN
			  , FRST_CFRM_DT
			  , ORIGIN_CD
			  , MAKE_YMD
			  , TAX_GB
			  , ERP_PRICE_LINK_YN
			  , ERP_STOCK_LINK_YN
			  , NEW_CUST_ORD_YN
			  , NEW_CUST_ORD_STDT
		      , NEW_CUST_ORD_EDDT
			  , ADULT_YN
			  , TOBE_FORM_YN
			  , REG_NO
			  , REG_DT
			  , UPD_NO
			  , UPD_DT
			)
			SELECT GOODS_CD
				  , BRAND_CD
				  , ITEMKIND_CD
				  , GOODS_NM
				  , GOODS_TNM
				  , GOODS_SNM
				  , GOODS_SNM1
				  , MAIN_COLOR_CD
				  , STYLE_YEAR
				  , SEASON_CD
				  , SEX_GB
				  , GOODS_NUM
				  , GOODS_TYPE
				  , TAG_PRICE
				  , LIST_PRICE
				  , CURR_PRICE
				  , CURR_BPRICE
				  , PRICE_UPD_DT
				  , COST_PRICE
				  , DC_RATE
				  , GOODS_STAT
				  , SELL_STDT
				  , SELL_EDDT
				  , SELF_MALL_YN
				  , GOODS_GB
				  , FOREIGN_BUY_YN
				  , PARALLEL_IMPORT_YN
				  , ORDER_MADE_YN
				  , DISTRIBUTION_GB
				  , SELF_GOODS_YN
				  , SUPPLY_COMP_CD
				  , SUPPLY_GOODS_CD
				  , AGE_GRP_CD
				  , DELV_LOC_CD
				  , DELV_FEE_CD
				  , DELV_FEE
				  , MIN_ORD_AMT
				  , PNT_PRATE
				  , PNT_MRATE
				  , SELL_FEE_RATE
				  , FORMAL_GB
				  , CHANGEABLE_YN
				  , RETURNABLE_YN
				  , CHANGE_FEE_FREE_YN
				  , RETURN_FEE_FREE_YN
				  , PRE_PPNT_USABLE_YN
				  , PRE_MPNT_USABLE_YN
				  , MIN_ORD_QTY
				  , MAX_ORD_QTY
				  , DAY_MAX_ORD_QTY
				  , GIFT_PACK_YN
				  , FRST_CFRM_DT
				  , ORIGIN_CD
				  , MAKE_YMD
				  , TAX_GB
				  , ERP_PRICE_LINK_YN
				  , ERP_STOCK_LINK_YN
				  , NEW_CUST_ORD_YN
				  , NEW_CUST_ORD_STDT
				  , NEW_CUST_ORD_EDDT
				  , ADULT_YN
				  , TOBE_FORM_YN
				  , #{regNo}
			      , now()
				  , #{updNo}
				  , now()		    
			FROM   TB_GOODS
			WHERE  1 = 1
			AND    GOODS_CD IN (
			                    SELECT B.GOODS_CD
			                    FROM   TB_SOCIAL A
			                         , TB_SOCIAL_GOODS B
			                    WHERE  A.SOCIAL_SQ = B.SOCIAL_SQ
			                    AND    A.USE_YN   = 'Y' 
			                    AND    A.APPLY_GB = 'A' 
			                    AND    B.DEL_YN   = 'N' 
			                    AND    A.SOCIAL_EDDT <![CDATA[<=]]> DATE_FORMAT(DATE_ADD(CONCAT(CURRENT_DATE(),' 23:59:59'), INTERVAL 0 DAY), '%Y%m%d%H%i%S')
								)
	</insert>
	<!-- 소셜 적용 여부 '종료'(적용->종료) 업데이트 -->
	<update id="updateSocialApplyGbtoF">
		/* TsbSocial.updateSocialApplyGbtoF */
		UPDATE TB_SOCIAL
		SET    APPLY_GB = 'F'	<!-- 종료상태 -->
		      ,UPD_DT = NOW()
		      ,UPD_NO = #{updNo}
		WHERE  APPLY_GB = 'A' 	<!-- 적용상태 -->
		AND    SOCIAL_EDDT <![CDATA[<=]]> DATE_FORMAT(DATE_ADD(CONCAT(CURRENT_DATE(),' 23:59:59'), INTERVAL 0 DAY), '%Y%m%d%H%i%S')
	</update>
	
	<!-- 상품 가격정보 적용(after) -->
	<update id="updateGoodsAfterPriceInfo">
		/* TsbSocial.updateGoodsAfterPriceInfo */
		UPDATE TB_GOODS A
				INNER JOIN TB_SOCIAL_GOODS B ON A.GOODS_CD = B.GOODS_CD 
				INNER JOIN TB_SOCIAL C ON B.SOCIAL_SQ = C.SOCIAL_SQ 								
		SET A.GOODS_CD = B.GOODS_CD ,
		    A.CURR_PRICE = B.CURR_APRICE,
		    A.DC_RATE = B.DC_ARATE,
		    A.UPD_DT = NOW() ,
		    A.UPD_NO = #{updNo}
			WHERE C.USE_YN = 'Y'
			  AND C.APPLY_GB = 'P'
			  AND B.DEL_YN = 'N'
			  AND SOCIAL_STDT <![CDATA[>=]]>  DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 0 DAY), '%Y%m%d%H%i%S')  
	</update>
	
	<!-- 적용(after) 상품 이력 생성 -->
	<insert id="createAfterGoodsHst">
		/* TsbSocial.createAfterGoodsHst */
		INSERT INTO TB_GOODS_HST 
		(   
			GOODS_CD		
		  , BRAND_CD
		  , ITEMKIND_CD
		  , GOODS_NM
		  , GOODS_TNM
		  , GOODS_SNM
		  , GOODS_SNM1
		  , MAIN_COLOR_CD
		  , STYLE_YEAR
		  , SEASON_CD
		  , SEX_GB
		  , GOODS_NUM
		  , GOODS_TYPE
		  , TAG_PRICE
		  , LIST_PRICE
		  , CURR_PRICE
		  , CURR_BPRICE
		  , PRICE_UPD_DT
		  , COST_PRICE
		  , DC_RATE
		  , GOODS_STAT
		  , SELL_STDT
		  , SELL_EDDT
		  , SELF_MALL_YN
		  , GOODS_GB
		  , FOREIGN_BUY_YN
		  , PARALLEL_IMPORT_YN
		  , ORDER_MADE_YN
		  , DISTRIBUTION_GB
		  , SELF_GOODS_YN
		  , SUPPLY_COMP_CD
		  , SUPPLY_GOODS_CD
		  , AGE_GRP_CD
		  , DELV_LOC_CD
		  , DELV_FEE_CD
		  , DELV_FEE
		  , MIN_ORD_AMT
		  , PNT_PRATE
		  , PNT_MRATE
		  , SELL_FEE_RATE
		  , FORMAL_GB
		  , CHANGEABLE_YN
		  , RETURNABLE_YN
		  , CHANGE_FEE_FREE_YN
		  , RETURN_FEE_FREE_YN
		  , PRE_PPNT_USABLE_YN
		  , PRE_MPNT_USABLE_YN
		  , MIN_ORD_QTY
		  , MAX_ORD_QTY
		  , DAY_MAX_ORD_QTY
		  , GIFT_PACK_YN
		  , FRST_CFRM_DT
		  , ORIGIN_CD
		  , MAKE_YMD
		  , TAX_GB
		  , ERP_PRICE_LINK_YN
		  , ERP_STOCK_LINK_YN
		  , NEW_CUST_ORD_YN
		  , NEW_CUST_ORD_STDT
		  , NEW_CUST_ORD_EDDT
		  , ADULT_YN
		  , TOBE_FORM_YN
		  , REG_NO
		  , REG_DT
		  , UPD_NO
		  , UPD_DT
			
		)
		SELECT GOODS_CD
			  , BRAND_CD
			  , ITEMKIND_CD
			  , GOODS_NM
			  , GOODS_TNM
			  , GOODS_SNM
			  , GOODS_SNM1
			  , MAIN_COLOR_CD
			  , STYLE_YEAR
			  , SEASON_CD
			  , SEX_GB
			  , GOODS_NUM
			  , GOODS_TYPE
			  , TAG_PRICE
			  , LIST_PRICE
			  , CURR_PRICE
			  , CURR_BPRICE
			  , PRICE_UPD_DT
			  , COST_PRICE
			  , DC_RATE
			  , GOODS_STAT
			  , SELL_STDT
			  , SELL_EDDT
			  , SELF_MALL_YN
			  , GOODS_GB
			  , FOREIGN_BUY_YN
			  , PARALLEL_IMPORT_YN
			  , ORDER_MADE_YN
			  , DISTRIBUTION_GB
			  , SELF_GOODS_YN
			  , SUPPLY_COMP_CD
			  , SUPPLY_GOODS_CD
			  , AGE_GRP_CD
			  , DELV_LOC_CD
			  , DELV_FEE_CD
			  , DELV_FEE
			  , MIN_ORD_AMT
			  , PNT_PRATE
			  , PNT_MRATE
			  , SELL_FEE_RATE
			  , FORMAL_GB
			  , CHANGEABLE_YN
			  , RETURNABLE_YN
			  , CHANGE_FEE_FREE_YN
			  , RETURN_FEE_FREE_YN
			  , PRE_PPNT_USABLE_YN
			  , PRE_MPNT_USABLE_YN
			  , MIN_ORD_QTY
			  , MAX_ORD_QTY
			  , DAY_MAX_ORD_QTY
			  , GIFT_PACK_YN
			  , FRST_CFRM_DT
			  , ORIGIN_CD
			  , MAKE_YMD
			  , TAX_GB
			  , ERP_PRICE_LINK_YN
			  , ERP_STOCK_LINK_YN
			  , NEW_CUST_ORD_YN
			  , NEW_CUST_ORD_STDT
			  , NEW_CUST_ORD_EDDT
			  , ADULT_YN
			  , TOBE_FORM_YN
			  , #{regNo}
		      , now()
			  , #{updNo}
			  , now()		    
		FROM   TB_GOODS
		WHERE  1 = 1
		AND    GOODS_CD IN (
		                    SELECT B.GOODS_CD
		                    FROM   TB_SOCIAL A
		                         , TB_SOCIAL_GOODS B
		                    WHERE  A.SOCIAL_SQ = B.SOCIAL_SQ
		                    AND    A.USE_YN   = 'Y' 
		                    AND    A.APPLY_GB = 'P' 
		                    AND    B.DEL_YN   = 'N' 
		                    AND    A.SOCIAL_STDT <![CDATA[>=]]>  DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 0 DAY), '%Y%m%d%H%i%S')
							)
	</insert>
	
	<!-- 소셜 적용 여부 '적용'(대기->적용) 업데이트 -->
	<update id="updateSocialApplyGbtoA">
		/* TsbSocial.updateSocialApplyGbtoA */
		UPDATE TB_SOCIAL
		SET    APPLY_GB = 'A',
		       UPD_DT = NOW(),
		       UPD_NO = #{updNo} 
		WHERE  APPLY_GB = 'P' 
		AND    USE_YN   = 'Y' 
		AND    SOCIAL_STDT <![CDATA[>=]]>  DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 0 DAY), '%Y%m%d%H%i%S')  
	</update>
</mapper>