<?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.admin.biz.dao.TsaSocialDao">
	<!-- 소셜 저장 -->
	<insert id="saveSocial" parameterType="Social">
		/* TsaSocial.saveSocial */
		INSERT INTO TB_SOCIAL
				(
				  SOCIAL_SQ
				,  SOCIAL_NM
				, SOCIAL_TYPE
				, SITE_CD
				, FRONT_GB
				, SOCIAL_STDT
				, SOCIAL_EDDT
				, SOCIAL_TNM
				, USE_YN
				, APPLY_GB
				, PLAN_SQ
				, REG_NO
				, REG_DT
				, UPD_NO
				, UPD_DT
				)
		VALUES(
		        #{socialSq}
			  ,	#{socialNm}
		      , #{socialType}
		      , #{siteCd}
		      , #{frontGb}
		      , #{socialStdt}
		      , #{socialEddt}
		      , #{socialTnm}
		      , #{useYn}
		      , #{applyGb}
		      , #{planSq}
		      , #{regNo}
		      , now()
		      , #{updNo}
		      , now()
		     )
		   ON
		     DUPLICATE KEY 
		UPDATE 
		   SOCIAL_NM=#{socialNm}
		 , SOCIAL_TYPE=#{socialType}
		 , SITE_CD=#{siteCd}
		 , FRONT_GB=#{frontGb}
		 , SOCIAL_STDT=  #{socialStdt}
		 , SOCIAL_EDDT= #{socialEddt}
		 , SOCIAL_TNM= #{socialTnm}
		 , USE_YN= #{useYn}
		 , APPLY_GB=#{applyGb}
		 , PLAN_SQ=#{planSq}
		 , UPD_NO=#{updNo}
		 , UPD_DT=now()
	</insert>
	
		<!-- 쇼셜 목록 -->
	<select id="getSocialList" parameterType="Social" resultType="Social">
		/* TsaSocial.getSocialList */
		SELECT Z.*
		FROM (
				 SELECT @rownum := @rownum + 1 AS RNUM
				 	         ,A.*
				 	   FROM (
								SELECT SOCIAL_SQ                                                    
								     , SOCIAL_NM                                                    
								     , SOCIAL_TNM                                                   
								     , SOCIAL_TYPE                                                  
								     , SITE_CD                                                      
								     , FRONT_GB                                                     
								     , SOCIAL_STDT													
								     , SOCIAL_EDDT 
								     , PLAN_SQ                                                      
								     , USE_YN  
								     , APPLY_GB 
								     , REG_DT
								     , REG_NO
								     , FN_GET_USER_NM(REG_NO)                AS REG_NM
								FROM   TB_SOCIAL JOIN ( SELECT @rownum := 0) R
								WHERE  1 = 1
								<include refid="getPlanListCondition_sql"/>
								ORDER  BY SOCIAL_SQ DESC
			<include refid="getListPagingCondition_sql"/>
	</select>
	
	<select id="getSocialCount" parameterType="Social" resultType="int">
		/* TsaSocial.getSocialCount */
		SELECT count(*) AS CNT
		FROM TB_SOCIAL	
	</select>
	
	<!-- 소셜 상품 저장 -->
	<insert id="saveSocialGoods" parameterType="Social">
		/* TsaSocial.saveSocialGoods */
		INSERT INTO TB_SOCIAL_GOODS
				(
					SOCIAL_SQ
				  , GOODS_CD
				  , CURR_BPRICE
				  , CURR_APRICE
				  , DC_BRATE
				  , DC_ARATE
				  , PNT_BPRATE
				  , PNT_APRATE
				  , PNT_BMRATE
				  , PNT_AMRATE
				  , ERP_PRICE_LINK_YN
				  , DISP_ORD
				  , DEL_YN
				  , REG_NO
				  , REG_DT
				  , UPD_NO
				  , UPD_DT
				)
			VALUES
				(
				   #{socialSq}
				 , #{goodsCd}
				 , #{currBprice}
				 , #{currAprice}
				 , #{dcBrate}
				 , #{dcArate}
				 , #{pntBprate}
				 , #{pntAprate}
				 , #{pntBmrate}
				 , #{pntAmrate}
				 , 'N'
				 , #{dispOrd}
				 , #{delYn}
				 , #{regNo}
				 , NOW()
				 , #{updNo}
				 , NOW()
				 )
			ON
			    DUPLICATE KEY 
			UPDATE 
			     CURR_BPRICE=#{currBprice}
			   , CURR_APRICE=#{currAprice}
			   , DC_BRATE=#{dcBrate}
			   , DC_ARATE=#{dcArate}
			   , PNT_BPRATE=#{pntBprate} 
			   , PNT_APRATE=#{pntAprate} 
			   , PNT_BMRATE=#{pntBmrate} 
			   , PNT_AMRATE=#{pntAmrate} 
			   , DISP_ORD=#{dispOrd}
			   , DEL_YN=#{delYn}
			   , UPD_NO=#{updNo}
			   , UPD_DT= NOW()
					
	</insert>
	
	<!-- 쇼셜 상품 목록 -->
	<select id="getSocialGoodsList" parameterType="Social" resultType="Social">
		/* TsaSocial.getSocialGoodsList */
		SELECT P.*
		FROM
		(
			SELECT SG.SCGOODS_SQ    
			     , SG.SOCIAL_SQ     
			     , SG.GOODS_CD      
			     , SG.CURR_BPRICE   
			     , SG.CURR_APRICE   
			     , SG.DC_BRATE      
			     , SG.DC_ARATE      
			     , SG.PNT_BPRATE    
			     , SG.PNT_APRATE    
			     , SG.PNT_BMRATE    
			     , SG.PNT_AMRATE    
			     , SG.DISP_ORD      
			     , SG.DEL_YN
			     , G.GOODS_NM 
			     , G.LIST_PRICE
			     , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG  WHERE GOODS_CD = G.GOODS_CD AND DEFAULT_IMG_YN = 'Y' ) AS SYS_IMG_NM
			FROM   TB_SOCIAL_GOODS SG LEFT JOIN TB_GOODS G ON SG.GOODS_CD = G.GOODS_CD 
			WHERE  SG.SOCIAL_SQ = #{socialSq}
			<if test="goodsCd != null and goodsCd != ''">
			AND    SG.GOODS_CD = #{goodsCd}
			</if>
			AND    SG.DEL_YN = 'N'
			ORDER  BY SG.DISP_ORD
		)P
	</select>
	
	<!-- 소셜 상품 삭제 -->
	<update id="deleteSocialGoods" parameterType="Social">
		/* TsaSocial.deleteSocialGoods */
		UPDATE TB_SOCIAL_GOODS
		SET    DEL_YN = #{delYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = now()
		WHERE  SOCIAL_SQ = #{socialSq}
		AND    SCGOODS_SQ = #{scgoodsSq}
		AND    GOODS_CD = #{goodsCd}
	</update>
	
	<!-- 진행중인 소셜 확인 -->
	<select id="getSocialGoOffCount" parameterType="Social" resultType="int">
		/*  TsaSocial.getSocialGoOffCount */
		SELECT COUNT(*)
		FROM TB_SOCIAL A
		WHERE 1=1
		AND A.USE_YN = 'Y'
		<if test='applyGb == "A"'>
		    AND A.APPLY_GB = 'A'
		</if>
		<if test='applyGb == "P"'>
		    AND A.APPLY_GB IN ('A','P')
		</if>
		<if test='frontGb == "P"'> <!-- PC웹 -->
		    AND A.FRONT_GB IN ('A','P')
		</if>
		<if test='frontGb == "M"'> <!-- 모바일 -->
		    AND A.FRONT_GB IN ('A','M')
		</if> 
		<if test='frontGb == "A"'> <!-- 전체 -->
		    AND A.FRONT_GB IN ('A','M','P')
		</if>
		AND A.SOCIAL_SQ != #{socialSq}
		AND A.SOCIAL_EDDT <![CDATA[ >= ]]> DATE_FORMAT(#{socialStdt} , '%Y-%m-%d %H:%i:%S')
		AND A.SOCIAL_STDT <![CDATA[ <= ]]> DATE_FORMAT(#{socialEddt} , '%Y-%m-%d %H:%i:%S')
	</select>

	<!-- 상품 저장 시 진행중인 소셜인지 확인 -->
	<select id="getSocialGoodsGoOffCount" parameterType="Social" resultType="int">
		/*  TsaSocial.getSocialGoodsGoOffCount */
		SELECT COUNT(*)
		FROM TB_SOCIAL
		WHERE SOCIAL_SQ = #{socialSq}
		AND   USE_YN    = 'Y'
		AND   APPLY_GB  = 'A'
	</select>
	
	
	
	<sql id="getPlanListCondition_sql">
		<if test ="siteCd != null and siteCd !=''">
		AND    SITE_CD = #{siteCd}
		</if>
		<if test="useYn != null and useYn != ''">
		AND    USE_YN = #{useYn}
		</if>
		<if test="socialSq != null and socialSq != ''">
		AND    SOCIAL_SQ = #{socialSq}
		</if>
		<if test="socialType != null and socialType != ''">
		AND    SOCIAL_TYPE = #{socialType}
		</if>
		<if test='frontGb != null and frontGb != "" and frontGb != "A"'>
			AND (FRONT_GB = 'A' OR FRONT_GB = #{frontGb} )
		</if>
		<if test="stDate != null and stDate != ''">
		AND    SOCIAL_EDDT <![CDATA[ >= ]]> DATE_FORMAT(concat(#{stDate}, ' 00:00:00'),'%Y-%m-%d %H:%i:%S')
		</if>
		<if test="edDate != null and edDate != ''">
		AND    SOCIAL_STDT <![CDATA[ <= ]]> DATE_FORMAT(concat(#{edDate}, ' 23:59:59') ,'%Y-%m-%d %H:%i:%S')
		</if>
	</sql>

	<sql id="getListPagingCondition_sql">
		<choose>
			<when test="pageable != null">
				) A
				)Z
				WHERE RNUM BETWEEN  #{pageable.startRow} AND #{pageable.endRow}
			</when>
			<otherwise>
				) A
				)Z
			</otherwise>
		</choose>
	</sql>

	<!-- 진행중인 상품 종료처리 : 소셜상품 가격정보 원복(before) 업데이트 -->
	<update id="updateGoodsBeforePriceInfo" parameterType="Social">
		/* TsaSocial.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'
		  <if test="immediatelyApplyYn == null or immediatelyApplyYn == ''">
		  AND C.SOCIAL_SQ = #{socialSq}
		  </if>
	</update>

	<!-- 진행중인 상품 종료처리 : 원복(before) 상품 이력 생성 -->
	<insert id="createBeforeGoodsHst" parameterType="Social">
		/* TsaSocial.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'
		                       <if test="immediatelyApplyYn == null or immediatelyApplyYn == ''">
		                         AND    A.SOCIAL_SQ = #{socialSq}
		                       </if>
		                       )
	</insert>

	<!-- 진행중인 상품 종료처리 : 소셜상품 가격정보 원복(before) 업데이트 -->
	<update id="updateSocialApplyGbtoF" parameterType="Social">
		/* TsaSocial.updateSocialApplyGbtoF */
		UPDATE TB_SOCIAL
		   SET   APPLY_GB = 'F'	<!-- 종료상태 -->
		        ,UPD_DT = NOW()
		        ,UPD_NO = #{updNo}
		WHERE  APPLY_GB = 'A' 	<!-- 적용상태 -->
		<if test="immediatelyApplyYn == null or immediatelyApplyYn == ''">
		  AND    SOCIAL_SQ = #{socialSq}
		</if>
	</update>

	<!-- 상품 가격정보 적용(after) -->
	<update id="updateGoodsAfterPriceInfo" parameterType="Social">
		/* TsaSocial.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_BPRICE,
		    A.DC_RATE = B.DC_BRATE,
		    A.UPD_DT = NOW(),
		    A.UPD_NO = #{updNo}
		WHERE C.USE_YN = 'Y'
		AND C.APPLY_GB = 'P'
		AND B.DEL_YN = 'N'
		AND C.SOCIAL_SQ = #{socialSq}
	</update>

	<!-- 적용(after) 상품 이력 생성 -->
	<insert id="createAfterGoodsHst" parameterType="Social">
		/* TsaSocial.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_SQ = #{socialSq}
		             )
	</insert>

	<!-- 소셜 적용 여부 '적용'(대기->적용) 업데이트 -->
	<update id="updateSocialApplyGbtoA" parameterType="Social">
		/* 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_SQ = #{socialSq}
	</update>

</mapper>