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

	<!-- 기획전 등록 -->
	<insert id="createPlanInfo" parameterType="Plan">
		/* TsaPlan.createPlanInfo */
		<selectKey keyProperty="planSq" resultType="Integer" order="AFTER">
			SELECT LAST_INSERT_ID()	/* 기획전 일련번호  */
		</selectKey>
		INSERT INTO TB_PLAN (
		       PLAN_SQ               
		     , PLAN_NM               
		     , PLAN_GB               
		     , SITE_CD               
		     , FRONT_GB              
		     , DISP_STDT             
		     , DISP_EDDT             
		     , MAIN_PIMG             
		     , MAIN_MIMG             
		     , DTL_PIMG              
		     , DTL_MIMG              
		     , DTL_TITLE1            
		     , REPLY_YN              
		     , REPLY_LOC           
		     , REPLY_IMG           
		     , REPLY_TITLE1        
		     , REPLY_TITLE2        
		     , POLL_SQ       
		     , NEW_CUST_YN
		     , CUST_JOIN_STDT
		     , CUST_JOIN_EDDT     
		     , DEV_URL             
		     , CORNER_NM_DISP_YN   
		     , GOODS_LIMIT_YN      
		     , GOODS_LIMIT_QTY     
		     , DEL_YN              
		     , OPEN_YN             
		     , DISP_ORD            
		     , READ_CNT              
		     , BADGE_NM              
		     , BADGE_FCOLOR          
		     , BADGE_BCOLOR          
		     , REG_NO               
		     , REG_DT                
		     , UPD_NO                
		     , UPD_DT                
		   ) VALUES (
		       #{planSq}
		     , #{planNm}
		     , #{planGb}
		     , #{siteCd}
		     , #{frontGb}
		     , DATE_FORMAT(#{dispStdt} , '%Y-%m-%d %H:%i:%s')
		     , DATE_FORMAT(#{dispEddt} , '%Y-%m-%d %H:%i:%s')
		     , #{mainPimg}
		     , #{mainMimg}
		     , #{dtlPimg}
		     , #{dtlMimg}
		     , #{dtlTitle1}
		     , #{replyYn}
		     , #{replyLoc}
		     , #{replyImg}
		     , #{replyTitle1}
		     , #{replyTitle2}
		     , #{pollSq}
		     , #{newCustYn}
		     , #{custJoinStdt}
		     , #{custJoinEddt}
		     , #{devUrl}
		     , #{cornerNmDispYn}
		     , IFNULL(#{goodsLimitYn}, 'N')
		     , #{goodsLimitQty}
		     , 'N'
		     , #{openYn}
		     , #{dispOrd}
		     , 1
		     , #{badgeNm}
		     , #{badgeFcolor}
		     , #{badgeBcolor}
		     , #{regNo}
		     , now() 
		     , #{updNo}
		     , now()
		)
	</insert>
	
	<!-- 기획전 목록 -->
	<select id="getPlanList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanList */
		 SELECT Z.*
		 FROM(
		 	  SELECT @rownum := @rownum + 1 AS RNUM
		 	         ,A.*
		 	   FROM (
					SELECT PLAN_SQ                
					     , PLAN_NM                
					     , PLAN_GB                
					     , SITE_CD                
					     , FRONT_GB               
					     , DISP_STDT    
					     , DISP_EDDT   
					     , MAIN_PIMG              
					     , MAIN_MIMG              
					     , DTL_PIMG               
					     , DTL_MIMG               
					     , DTL_TITLE1             
					     , REPLY_YN               
					     , REPLY_TITLE1           
					     , REPLY_TITLE2           
					     , REPLY_LOC              
					     , REPLY_IMG              
					     , POLL_SQ  
					     , NEW_CUST_YN
		                 , CUST_JOIN_STDT
		                 , CUST_JOIN_EDDT                 
					     , DEV_URL                
					     , CORNER_NM_DISP_YN      
					     , GOODS_LIMIT_YN         
					     , GOODS_LIMIT_QTY        
					     , DEL_YN                 
					     , OPEN_YN                
					     , DISP_ORD               
					     , READ_CNT               
					     , BADGE_NM               
					     , BADGE_FCOLOR           
					     , BADGE_BCOLOR           
					     , FN_GET_USER_NM(REG_NO) AS REG_NM               
					     , REG_DT
					       AS REG_DT              
					     , UPD_NO                 
					     , UPD_DT              
					FROM   TB_PLAN T JOIN ( SELECT @rownum := 0) R
					WHERE  1=1
					AND    DEL_YN = 'N'
					<include refid="getPlanListCondition_sql"/>
					ORDER  BY PLAN_SQ DESC
				<include refid="getListPagingCondition_sql"/>
		
	</select>
	
	<!-- 기획전 리스트 카운트 조회-->
	<select id="getPlanListCount" parameterType="Plan" resultType="int">
		/* TsaPlan.getPlanListCount */
		SELECT COUNT(1)
		  FROM TB_PlAN
		 WHERE 1=1
		 AND DEL_YN = 'N'
		 <include refid="getPlanListCondition_sql"/>
	</select>
	
	<sql id="getPlanListCondition_sql">
		<if test ="siteCd != null and siteCd !=''">
		AND    SITE_CD = #{siteCd}
		</if>
		<if test="planSq != null and planSq != ''">
		AND    PLAN_SQ = #{planSq}
		</if>
		<if test="planNm != null and planNm != ''">
		AND    UPPER(PLAN_NM) LIKE CONCAT('%', #{planNm},'%') 
		</if>
		
		<if test="condition != null and condition != ''">
		<choose>
			     <when test="search != null and search == 'searchPlanNm'">
		AND  
		        <foreach collection="conditionList" item="item" index="index" separator="or">
		        UPPER(PLAN_NM) LIKE CONCAT('%',UPPER(#{item}),'%') 
		        </foreach>
	         </when>
	          <when test="search != null and search == 'searchPlanSq'">
 		AND 		 
		         <foreach collection="conditionList"  item="item" index="index" separator="or">
	    		   UPPER(PLAN_SQ) LIKE CONCAT('%',UPPER(#{item}),'%') 
		         </foreach>
			      </when>
		</choose>
		</if>
		<if test="openYn != null and openYn != ''">
		AND    OPEN_YN = #{openYn}
		</if>
		<if test="regNm != null and regNm != ''">
		AND    REG_NO IN (
		                 SELECT USER_NO 
		                 FROM TB_USER
		                 WHERE USER_NM LIKE CONCAT('%',UPPER(#{regNm}),'%') 
		                 )
		</if>
		<if test="frontGb != null and frontGb != ''">
		AND    FRONT_GB LIKE CONCAT('%',#{frontGb},'%')
		</if>
		<if test="planGb != null and planGb != ''">
		AND    PLAN_GB = #{planGb}
		</if>
		<if test="stDate != null and stDate != ''">
		AND    DISP_EDDT <![CDATA[ >= ]]> DATE_FORMAT(concat(#{stDate}, ' 00:00:00'),'%Y-%m-%d %H:%i:%S')
		</if>
		<if test="edDate != null and edDate != ''">
		AND    DISP_STDT <![CDATA[ <= ]]> DATE_FORMAT(concat(#{edDate}, ' 23:59:59') ,'%Y-%m-%d %H:%i:%S')
		</if>
		<if test="popupDispStdt != null and popupDispStdt != ''">
		AND    DISP_EDDT <![CDATA[ >= ]]> DATE_FORMAT(concat(#{popupDispStdt}, ' 00:00:00'),'%Y-%m-%d %H:%i:%S')
		</if>
		<if test="popupDispEddt != null and popupDispEddt != ''">
		AND    DISP_STDT <![CDATA[ <= ]]> DATE_FORMAT(concat(#{popupDispEddt}, ' 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>
	
	
	<!-- 기획전 삭제처리 -->
	<update id="deletePlan" parameterType="Plan">
		/* TsaPlan.deletePlan */
		UPDATE TB_PLAN
		SET    UPD_NO = #{updNo}
		     , UPD_DT = now()
		     , DEL_YN = 'Y'
		WHERE  PLAN_SQ = #{planSq}
	</update>
	
	<!-- 기획전  소스 삭제 -->
	<delete id="deletePlanFsrcInfo" parameterType="Plan">
		/* TsaPlan.deletePlanFsrcInfo */
		DELETE TB_PLAN_FSRC
		WHERE  PLAN_SQ = #{planSq}
		AND    FRONT_GB = #{frontGb}
	</delete>
	
	<!-- 기획전  소스 등록 -->
	<insert id="savePlanFsrcInfo" parameterType="Plan">
		/* TsaPlan.savePlanFsrcInfo */
		         INSERT INTO
				      TB_PLAN_FSRC (
				          PLAN_SQ
				          , FRONT_GB
				          , FSRC_PC
				          , FSRC_MOB
				          , DISP_YN
				          , REG_NO
				          , REG_DT
				          , UPD_NO
				          , UPD_DT
				      )
				   VALUES (
				    	  #{planSq}
				    	  , #{frontGb}
				          , #{fsrcPc}
				          , #{fsrcMob}
				          , #{dispYn}
				          , #{regNo}
				          , now()
				          , #{regNo}
				          , now() 
				   )
				   ON
				      DUPLICATE KEY
				   UPDATE
				       FSRC_PC = #{fsrcPc}
			         , FSRC_MOB = #{fsrcMob}
			         , DISP_YN = #{dispYn}
		         	 , UPD_NO = #{regNo}
		         	 , UPD_DT = now()
	</insert>
	
	<!--  기획전 웹용 소스 조회 -->
	<select id="getPlanFsrcList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanFsrcList */
		SELECT PLAN_SQ
		     , FRONT_GB
		     , FSRC_TOP
		     , FSRC_BTM
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		FROM   TB_PLAN_FSRC
		WHERE  PLAN_SQ = #{planSq}
		AND    FRONT_GB = #{frontGb}
	</select>
	
	<!-- 기획전 수정 -->
	<update id="updatePlanInfo" parameterType="Plan">
		/* TsaPlan.updatePlanInfo */
		UPDATE TB_PLAN
		   SET PLAN_GB = #{planGb}
		     , FRONT_GB = #{frontGb}
		     , PLAN_NM = #{planNm}
		     , DISP_STDT = DATE_FORMAT(#{dispStdt} , '%Y-%m-%d %H:%i:%s')
		     , DISP_EDDT = DATE_FORMAT(#{dispEddt} , '%Y-%m-%d %H:%i:%s')
		     , CORNER_NM_DISP_YN = #{cornerNmDispYn}
		     , OPEN_YN = #{openYn}
		     , SITE_CD = #{siteCd}
		     , DISP_ORD = IFNULL(#{dispOrd}, 999)
		     , DTL_TITLE1 = #{dtlTitle1}
		     , MAIN_PIMG = #{mainPimg}
		     , MAIN_MIMG = #{mainMimg}
		     , DTL_PIMG = #{dtlPimg}
		     , DTL_MIMG = #{dtlMimg}
		     , REPLY_IMG = #{replyImg}
		     , REPLY_TITLE1 = #{replyTitle1}
		     , REPLY_TITLE2 = #{replyTitle2}
		     , POLL_SQ = #{pollSq}
		     , NEW_CUST_YN = #{newCustYn}
		     <if test='newCustYn == "Y"'>
		     , CUST_JOIN_STDT = #{custJoinStdt}
		     , CUST_JOIN_EDDT = #{custJoinEddt}
		     </if>
		     , DEV_URL = #{devUrl}
		     , REPLY_YN = #{replyYn}
		     , REPLY_LOC = #{replyLoc}
		     , BADGE_NM = #{badgeNm}
		     , BADGE_FCOLOR = #{badgeFcolor}
		     , BADGE_BCOLOR = #{badgeBcolor}
		<if test='goodsLimitYn == "Y"'>
		     , GOODS_LIMIT_YN = 'Y'
		     , GOODS_LIMIT_QTY = #{goodsLimitQty}
		</if>
		<if test='goodsLimitYn == null or goodsLimitYn == "" or goodsLimitYn == "N"'>
		     , GOODS_LIMIT_YN = 'N'
		     , GOODS_LIMIT_QTY = 0
		</if>
		     , UPD_NO = #{updNo}
		     , UPD_DT = now()
		WHERE  PLAN_SQ = #{planSq}
	</update>
	
	<!-- 기획전 전체 조회 -->
	<select id="getPlanAllList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanAllList */
		SELECT PLAN_SQ
		     , PLAN_NM
		FROM   TB_PLAN
		WHERE  1=1
		AND    DEL_YN = 'N'
		<if test="planSq != null and planSq != ''">
		AND    PLAN_SQ != #{planSq}
		</if>
		ORDER  BY PLAN_SQ DESC
	</select>

	<!-- 기획전 복사 -->
	<insert id="createPlanCopy" parameterType="Plan" useGeneratedKeys="true" keyProperty="planSq">
		/* TsaPlan.createPlanCopy */
		<selectKey keyProperty="planSq" resultType="Integer" order="AFTER">
			SELECT LAST_INSERT_ID()	/* 기획전 일련번호  */
		</selectKey>
		INSERT INTO TB_PLAN (
		            PLAN_SQ
		          , PLAN_NM
		          , PLAN_GB
		          , SITE_CD
		          , FRONT_GB
		          , DISP_STDT
		          , DISP_EDDT
		          , MAIN_PIMG
		          , MAIN_MIMG
		          , DTL_PIMG
		          , DTL_MIMG
		          , DTL_TITLE1        
		          , REPLY_YN
		          , REPLY_LOC
		          , REPLY_IMG
		          , REPLY_TITLE1
		          , REPLY_TITLE2
		          , POLL_SQ
		          , NEW_CUST_YN
		          , CUST_JOIN_STDT
		          , CUST_JOIN_EDDT   
		          , DEV_URL
		          , CORNER_NM_DISP_YN
		          , GOODS_LIMIT_YN
		          , GOODS_LIMIT_QTY
		          , DEL_YN
		          , OPEN_YN
		          , DISP_ORD
		          , READ_CNT
		          , BADGE_NM
		          , BADGE_FCOLOR
		          , BADGE_BCOLOR
		          , REG_NO
		          , REG_DT
		          , UPD_NO
		          , UPD_DT
		      ) SELECT #{planSq}
		             , PLAN_NM
		             , PLAN_GB
		             , SITE_CD
		             , FRONT_GB
		             , DISP_STDT
		             , DISP_EDDT
		             , MAIN_PIMG
		             , MAIN_MIMG
		             , DTL_PIMG
		             , DTL_MIMG
		             , DTL_TITLE1
		             , REPLY_YN
		             , REPLY_LOC
		             , REPLY_IMG
		             , REPLY_TITLE1
		             , REPLY_TITLE2
		             , POLL_SQ
		             , NEW_CUST_YN
		             , CUST_JOIN_STDT
		             , CUST_JOIN_EDDT  
		             , DEV_URL
		             , CORNER_NM_DISP_YN
		             , GOODS_LIMIT_YN
		             , GOODS_LIMIT_QTY
		             , 'N'
		             , OPEN_YN
		             , DISP_ORD
		             , 0
		             , BADGE_NM
		             , BADGE_FCOLOR
		             , BADGE_BCOLOR
		             , #{regNo}
		             , now()
		             , #{updNo}
		             , now()
		        FROM   TB_PLAN
		        WHERE  PLAN_SQ = #{copyPlanSq}
	</insert>
	
	<insert id="createPlanBrand" parameterType="Plan">
		/* TsaPlan.createPlanBrand */
		INSERT INTO TB_PLAN_BRAND
			(
				BRAND_GROUP_NO
			  , PLAN_SQ
			  , DISP_YN
			  , REG_NO
			  , REG_DT
			  , UPD_NO
			  , UPD_DT
			)
		VALUES
		   (
			  #{brandGroupNo}
			, #{planSq}
			,'Y'
			, #{regNo}
			, CURRENT_TIMESTAMP
			, #{updNo}
			, CURRENT_TIMESTAMP
		   )
	</insert>
	
	<delete id="deletePlanBrand" parameterType="Plan">
		/* TsaPlan.deletePlanBrand */
		DELETE 
		FROM TB_PLAN_BRAND
		WHERE PLAN_SQ = #{planSq}
	</delete>
	
	<insert id="createPlanAfChannel" parameterType="Plan">
		/* TsaPlan.createPlanAfChannel */
		INSERT INTO TB_PLAN_AF_CHANNEL
			(
				PLAN_SQ
			  , AF_LINK_CD
			  , REG_NO
			  , REG_DT
			)
		VALUES
		   (
			    #{planSq}
			  , #{afLinkCd}
			  , #{regNo}
			  , CURRENT_TIMESTAMP
		   )
	</insert>
	
	<delete id="deletePlanAfChannel" parameterType="Plan">
		/* TsaPlan.deletePlanAfChannel */
		DELETE 
		FROM TB_PLAN_AF_CHANNEL
		WHERE PLAN_SQ = ${planSq}
	</delete>
	
	<select id="getPlanBrandList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanBrandList */
		SELECT TP.PLAN_NM AS PLAN_NM
				     , TP.PLAN_SQ AS PLAN_SQ
				     , TB.BRAND_GROUP_ENM AS BRAND_GROUP_ENM
				     , TB.BRAND_GROUP_NO AS BRAND_GROUP_NO
				     , TB.BRAND_GROUP_KNM AS BRAND_GROUP_KNM
		FROM TB_PLAN TP  LEFT OUTER JOIN TB_PLAN_BRAND TPB 
							   ON TP.PLAN_SQ = TPB.PLAN_SQ 
		            	 LEFT OUTER JOIN  TB_BRAND_GROUP TB
		   					   ON TPB.BRAND_GROUP_NO = TB.BRAND_GROUP_NO 
		WHERE 1=1
		 	AND TP.PLAN_SQ = TPB.PLAN_SQ 
		 	AND TP.PLAN_SQ = #{planSq}
		    AND TP.DEL_YN = 'N'
	</select>
	
	<select id="getPlanAfChannelList" parameterType="Plan" resultType="Plan">
			SELECT A.PLAN_SQ 
			      ,A.AF_LINK_CD 
			      ,B.AF_LINK_NM  
			      ,A.REG_DT 
			      ,B.FEE_RATE 
			      ,B.DISP_ORD 
			      ,B.AF_CHANNEL 
			FROM TB_PLAN_AF_CHANNEL A LEFT JOIN TB_AF_LINK B ON A.AF_LINK_CD = B.AF_LINK_CD 
			WHERE PLAN_SQ = #{planSq}
	</select>
	
	<insert id="createPlanBrandCopy" parameterType="Plan">
		/* TsaPlan.createPlanBrandCopy */
			INSERT INTO
					 TB_PLAN_BRAND 
					 (
			           BRAND_GROUP_NO
					  ,PLAN_SQ 
					  ,DISP_YN 
					  ,REG_NO  
					  ,REG_DT  
					  ,UPD_NO  
					  ,UPD_DT  
     			      ) SELECT 	
     			      		BRAND_GROUP_NO
						  , #{planSq} 
						  , DISP_YN 
						  , #{regNo} 
						  , CURRENT_TIMESTAMP  
						  , #{updNo} 
						  , CURRENT_TIMESTAMP  
	        	   FROM   TB_PLAN_BRAND
	        	   WHERE 1=1
					 AND PLAN_SQ = #{copyPlanSq}
	        	     AND BRAND_GROUP_NO = #{brandGroupNo}
	</insert>
	
	<insert id="createPlanCate" parameterType="Plan">
		/* TsaPlan.createPlanCate */
		INSERT INTO TB_PLAN_CATE
		(
			CATE_NO
		  , PLAN_SQ
		  , DISP_YN
		  , REG_NO
		  , REG_DT
		  , UPD_NO
		  , UPD_DT
		)
		VALUES(
				#{cateNo}
			  , #{planSq}
			  , 'Y'
			  , #{regNo}
			  , now()
			  , #{updNo}
			  , now()
			)
	
	</insert>
	
	<delete id="deletePlanCate" parameterType="Plan">
		/* TsaPlan.deletePlanCate */
		DELETE 
		FROM TB_PLAN_CATE
		WHERE PLAN_SQ = #{planSq}
	</delete>
	
	<select id="getPlanCateList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanCateList */
		 SELECT TP.PLAN_NM AS PLAN_NM
				     , TP.PLAN_SQ AS PLAN_SQ
				     , TC.CATE1_NM AS CATE_NM
				     , TC.CATE1_NO AS CATE_NO
				     , TC.CATE_GB AS CATE_GB
				     , TP.SITE_CD AS SITE_CD
		FROM TB_PLAN TP  LEFT OUTER JOIN TB_PLAN_CATE TPC 
							   ON TP.PLAN_SQ = TPC.PLAN_SQ 
		            	 LEFT OUTER JOIN  TB_CATE1 TC
		   					   ON TPC.CATE_NO = TC.CATE1_NO 
		WHERE 1=1
		 	AND TP.PLAN_SQ = TPC.PLAN_SQ 
		 	AND TP.PLAN_SQ = #{planSq}
		AND TP.DEL_YN = 'N'
	</select>
	
	<insert id="createPlanCateCopy" parameterType="Plan">
		/* TsaPlan.createPlanCateCopy */
			INSERT INTO
					 TB_PLAN_CATE
					 (
			           CATE_NO
					  ,PLAN_SQ
					  ,DISP_YN
					  ,REG_NO 
					  ,REG_DT 
					  ,UPD_NO 
					  ,UPD_DT 
     			      ) SELECT 	
     			      		CATE_NO
						  , #{planSq} 
						  , DISP_YN 
						  ,#{regNo}
						  , now()  
						  ,#{updNo}
						  , now()  
	        	   FROM   TB_PLAN_CATE
	        	   WHERE 1=1
					 AND PLAN_SQ = #{copyPlanSq}
	        	     AND CATE_NO = #{cateNo}
	</insert>
	
	<insert id="createPlanAfChannelCopy" parameterType="Plan">
		/* TsaPlan.createPlanAfChannelCopy */
			INSERT INTO
					 TB_PLAN_AF_CHANNEL
					 (
			         	  PLAN_SQ
			         	, AF_LINK_CD
			         	, REG_NO
			         	, REG_DT
     			      ) SELECT 	
     			      		 #{planSq}
     			      		,AF_LINK_CD
     			      		,#{regNo}
     			      		,now()
	        	   FROM   TB_PLAN_AF_CHANNEL
	        	   WHERE 1=1
					 AND PLAN_SQ = #{copyPlanSq}
					 AND AF_LINK_CD = #{afLinkCd}
	</insert>
	
	<insert id="createPlanCustGbCopy" parameterType="Plan">
		/* TsaPlan.createPlanCustGbCopy */
			INSERT INTO
					 TB_PLAN_CUST_DIVISION
					 (
			         	 PLAN_SQ
			           , CUST_GB
			           , REG_NO
			           , REG_DT
     			      ) SELECT 	
     			      		 #{planSq}
     			      		, CUST_GB
     			      		,#{regNo}
     			      		,now()
	        	   FROM  TB_PLAN_CUST_DIVISION
	        	   WHERE 1=1
					 AND PLAN_SQ = #{copyPlanSq}
					 AND CUST_GB = #{custGb}
	</insert>
	
	<insert id="createPlanCustGradeCopy" parameterType="Plan">
		/* TsaPlan.createPlanCustGbCopy */
			INSERT INTO
					 TB_PLAN_CUST_GRADE
					 (
			         	PLAN_SQ
			          , CUST_GRADE
			          , REG_NO
			          , REG_DT
     			      ) SELECT 	
     			      		 #{planSq}
     			      		, CUST_GRADE
     			      		,#{regNo}
     			      		,now()
	        	   FROM  TB_PLAN_CUST_GRADE
	        	   WHERE 1=1
					 AND PLAN_SQ = #{copyPlanSq}
					 AND CUST_GRADE = #{custGrade}
	</insert>
	
	
	<!-- 기획전 코너 시퀀스 조회 -->
	<select id="getPlanDtlSq" parameterType="Plan" resultType="Integer">
		/* TsaPlan.getPlanDtlSq */
		SELECT CASE COUNT(*) 
			   WHEN 0 THEN 1 
			   ELSE MAX(PLAN_DTL_SQ) + 1 
			   END AS PLAN_DTL_SQ 
		FROM TB_PLAN_DETAIL
	</select>
	
	<!-- 기획전 코너 목록 -->
	<select id="getPlanCornerList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanCornerList */
		SELECT A.PLAN_SQ
		     , B.PLAN_DTL_SQ
		     , B.CORNER_NM
		     , B.CORNER_DISP_TYPE
	<!-- 	     , #{adminPreview} AS ADMIN_PREVIEW -->
	         , FN_GET_USER_NM(B.REG_NO) AS REG_NM
	         , FN_GET_USER_NM(B.UPD_NO) AS UPD_NM     
	         , B.DISP_STDT 
     	     , B.DISP_EDDT 
     	     , B.DISP_ORD 
		FROM   TB_PLAN A
		     , TB_PLAN_DETAIL B
		WHERE  A.PLAN_SQ = B.PLAN_SQ
	<!-- 	<if test='adminPreview != "Y"'> 
		AND    now() BETWEEN B.DISP_STDT AND B.DISP_EDDT
		</if> -->
		AND    A.PLAN_SQ = #{planSq}
		ORDER  BY B.DISP_ORD
	</select>
	
	<!-- 기획전 코너 삭제 처리 -->
	<delete id="deletePlanCornerInfo" parameterType="Plan">
		/* TsaPlan.deletePlanCornerInfo */
		DELETE 
		FROM TB_PLAN_DETAIL 
		WHERE  PLAN_SQ = #{planSq}
		AND    PLAN_DTL_SQ = #{planDtlSq}
	</delete>
	
	<!-- 기획전 코너 상품 삭제 처리 -->
	<update id="deletePlanCornerGoods" parameterType="Plan">
		/* TsaPlan.deletePlanCornerGoods */
		UPDATE TB_PLAN_GOODS
		SET    DEL_YN = 'Y'
		     , DISP_ORD = 0
		WHERE  PLAN_DTL_SQ = #{planDtlSq}
		<if test="goodsCd != null and goodsCd != ''">
		AND    GOODS_CD = #{goodsCd}
		</if>
	</update>
	
	<!-- 기획전 코너 저장 -->
	<insert id="savePlanCornerInfo" parameterType="Plan">
		/* TsaPlan.savePlanCornerInfo */
		<selectKey keyProperty="planDtlSq" resultType="Integer" order="AFTER">
			SELECT LAST_INSERT_ID()	/* 기획전 일련번호  */
		</selectKey>
		INSERT INTO
			      TB_PLAN_DETAIL (
			            PLAN_SQ         
                      , PLAN_DTL_SQ     
                      , DISP_STDT       
                      , DISP_EDDT       
                      , CORNER_DISP_TYPE
                      , CORNER_NM  
                      , BRAND_GROUP_NO     
                      , DISP_ORD        
                      , REG_NO          
                      , REG_DT          
                      , UPD_DT          
                      , UPD_NO          
			      )
			   VALUES (
			    	   #{planSq}                                        
                     , #{planDtlSq}                                      
                     , DATE_FORMAT(#{dispStdt} , '%Y-%m-%d %H:%i:%s')     
                     , DATE_FORMAT(#{dispEddt} , '%Y-%m-%d %H:%i:%s')   
                     , #{cornerDispType}                                 
                     , #{cornerNm} 
                     , #{brandGroupNo}                                      
                     , #{dispOrd}                                        
                     , #{regNo}                                          
                     , now()                                           
                     , now()                                           
                     , #{updNo}                                          
			     )
			   ON
			      DUPLICATE KEY
			   UPDATE
			        DISP_STDT = DATE_FORMAT(#{dispStdt} , '%Y-%m-%d %H:%i:%s')      
				  , DISP_EDDT = DATE_FORMAT(#{dispEddt} , '%Y-%m-%d %H:%i:%s')      
				  , CORNER_DISP_TYPE = #{cornerDispType}                         
				  , CORNER_NM = #{cornerNm}                                      
				  , DISP_ORD = #{dispOrd}                                        
				  , UPD_DT = now()                                             
				  , UPD_NO = #{updNo}                                            
	</insert>
	
	<!-- 기획전 코너 상품 목록 조회 -->
	<select id="getPlanGoodsList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanGoodsList */
		SELECT Q.*
		   -- , (CASE WHEN Q.GOODS_TYPE = 'G056_N' THEN (SELECT IFNULL(SUM(CURR_STOCK_QTY - BASE_STOCK_QTY),0) FROM VW_STOCK WHERE GOODS_CD = Q.GOODS_CD) 
		   --         ELSE (SELECT IFNULL(SUM(CURR_STOCK_QTY - BASE_STOCK_QTY),0) FROM VW_STOCK_COMPOSE WHERE GOODS_CD = Q.GOODS_CD) 
		   --         END) AS STOCK_QTY_SUM
		    , IFNULL((SELECT STOCK_QTY FROM TB_GOODS_STOCK WHERE GOODS_CD = Q.GOODS_CD),0) AS STOCK_QTY_SUM
		    , FN_GET_USER_NM(REG_NO) AS REG_NM
		    , FN_GET_USER_NM(UPD_NO) AS UPD_NM
		    , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG  WHERE GOODS_CD = Q.GOODS_CD AND COLOR_CD = Q.MAIN_COLOR_CD AND DEFAULT_IMG_YN = 'Y' ) AS SYS_IMG_NM
		    , CASE IFNULL((SELECT COUNT(SYS_IMG_NM) FROM TB_GOODS_IMG  WHERE GOODS_CD = Q.GOODS_CD AND COLOR_CD = Q.MAIN_COLOR_CD AND DEFAULT_IMG_YN = 'Y' ),0) 
		            WHEN 0 THEN 'N'
		            ELSE 'Y' END AS GOODS_IMAGE_YN
		FROM (     
			SELECT Z.*
			FROM (
			    SELECT A.*, @rownum := @rownum + 1 AS RNUM 
			    FROM (
			        SELECT
			                 G.GOODS_CD                                                                   
			               , G.GOODS_TYPE                                                                 
			               , G.SUPPLY_COMP_CD                                                             
			               , G.SUPPLY_GOODS_CD                                                            
			               , G.GOODS_NUM                                                                  
			               , G.GOODS_NM                                                                   
			               , G.GOODS_STAT                                                                 
			               , G.LIST_PRICE                                                                 
			               , G.CURR_PRICE                                                                 
			               , G.DC_RATE      
			               , G.TAG_PRICE                                                              
			               , CASE G.FORMAL_GB WHEN 'G009_20' THEN '이월' ELSE '정상' END AS FORMAL_GB                                                                 
			               , G.SEASON_CD       
			               , (SELECT SUPPLY_COMP_NM
                 				FROM   TB_SUPPLY_COMPANY
                 				WHERE  SUPPLY_COMP_CD = G.SUPPLY_COMP_CD
              				   ) AS SUPPLY_COMP_NM                                                           
			               , G.ERP_STOCK_LINK_YN                                                          
			               , G.ERP_PRICE_LINK_YN                                                          
			               , G.CHANGEABLE_YN                                                              
			               , G.RETURNABLE_YN                                                              
			               , G.NEW_CUST_ORD_YN                                                            
			               , G.GIFT_PACK_YN                                                               
			               , G.DELV_FEE_CD                                                                
			               , G.MAIN_COLOR_CD                                                              
			               , G.FRST_CFRM_DT                  
			               , G.SELL_FEE_RATE                                                              
			               , G.PRICE_UPD_DT                   
			               , TG.REG_NO                                                                    
			               , TG.REG_DT                              
			               , TG.UPD_NO                                                                    
			               , TG.UPD_DT     
			               , TG.PLAN_DTL_SQ                          
			         FROM TB_GOODS G
					        JOIN ( SELECT @rownum := 0) R
					        INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
					        INNER JOIN TB_PLAN_GOODS TG ON G.GOODS_CD = TG.GOODS_CD 
			         WHERE 1=1
			         	 AND TG.PLAN_DTL_SQ = #{planDtlSq}
			         	 AND TG.DEL_YN = 'N'
			         ORDER BY G.REG_DT DESC, G.GOODS_CD
		<include refid="getListPagingCondition_sql"/>
		)Q
	</select>
	
	<!-- 기획전 코너 상품 목록 조회 건수 -->
	<select id="getPlanGoodsListCount" parameterType="Plan" resultType="int">
		/* TsaPlan.getPlanGoodsListCount */
		SELECT COUNT(*) AS CNT
		FROM (
		    SELECT
		            G.GOODS_CD
		          , G.GOODS_TYPE
		          , G.SUPPLY_COMP_CD
		          , G.SUPPLY_GOODS_CD
		          , G.GOODS_NUM
		          , G.GOODS_NM
		          , G.GOODS_STAT
		          , G.LIST_PRICE
		          , G.CURR_PRICE
		          , G.DC_RATE
		          , G.FORMAL_GB
		          , G.STYLE_YEAR
		          , G.SEASON_CD
		          , G.ERP_STOCK_LINK_YN
		          , G.ERP_PRICE_LINK_YN
		          , G.CHANGEABLE_YN
		          , G.RETURNABLE_YN
		          , G.NEW_CUST_ORD_YN
		          , G.GIFT_PACK_YN
		          , G.DELV_FEE_CD
		          , G.MAIN_COLOR_CD
		          , DATE_FORMAT(G.FRST_CFRM_DT, '%Y%m%d%H%i%S') AS FRST_CFRM_DT
		          , G.SELL_FEE_RATE
		          , DATE_FORMAT(G.PRICE_UPD_DT,'%Y%m%d%H%i%S') AS PRICE_UPD_DT
		          , TG.REG_NO
		          , DATE_FORMAT(TG.REG_DT,'%Y%m%d%H%i%S') AS REG_DT
		          , TG.UPD_NO
		          , DATE_FORMAT(TG.UPD_DT,'%Y%m%d%H%i%S') AS UPD_DT
		          , G.TAG_PRICE
		        FROM TB_GOODS G
		        INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
		        INNER JOIN TB_PLAN_GOODS TG ON G.GOODS_CD = TG.GOODS_CD 
		        WHERE 1=1
		        AND TG.PLAN_DTL_SQ = #{planDtlSq}
	            AND TG.DEL_YN = 'N'
		    ) CNT
	</select>
	
	<!-- 기획전 코너 상품저장 -->
	<insert id="savePlanCornerGoods" parameterType="Plan">
		/* TsaPlan.savePlanCornerGoods */
		INSERT INTO
			      TB_PLAN_GOODS (
			                PLAN_DTL_SQ
				          , GOODS_CD
				          , DISP_ORD
				          , DEL_YN
				          , REG_NO
				          , REG_DT
				          , UPD_DT
				          , UPD_NO    
			      )
			   VALUES (
			    	    #{planDtlSq}
			          , #{goodsCd}
			          , #{dispOrd}
			          , 'N'
			          , #{regNo}
			          , now()
			          , now()
			          , #{updNo}                                  
			     )
			   ON
			      DUPLICATE KEY
			   UPDATE
			        DEL_YN = 'N'
		          , UPD_DT = now()
		          , UPD_NO = #{updNo}                                  
	</insert>
	
	<!-- 기획전 코너 복사 -->
	<insert id="createPlanCornerCopy" parameterType="Plan">
		/* TsaPlan.createPlanCornerCopy */
		INSERT INTO TB_PLAN_DETAIL (
		            PLAN_DTL_SQ						          
		          , PLAN_SQ                              
		          , CORNER_DISP_TYPE                       
		          , CORNER_NM                              
		          , DISP_STDT                       
		          , DISP_EDDT                              
		          , DISP_ORD                                  
		          , REG_NO                                    
		          , REG_DT                                    
		          , UPD_NO                                    
		          , UPD_DT
		      ) SELECT #{planDtlSq}
		             , #{planSq}
		             , CORNER_DISP_TYPE
		             , CORNER_NM
		             , DISP_STDT
		             , DISP_EDDT
		             , DISP_ORD
		             , #{regNo}
		             , now()
		             , #{updNo}
		             , now()
		        FROM   TB_PLAN_DETAIL
		        WHERE  PLAN_DTL_SQ = #{copyPlanDtlSq}
	</insert>
	
	<!-- 기획전 상품 복사 -->
	<insert id="createPlanGoodsCopy" parameterType="Plan">
		/* TsaPlan.createPlanGoodsCopy */
		INSERT INTO TB_PLAN_GOODS (
		            PLAN_DTL_SQ				 
		          , GOODS_CD                 
		          , DISP_ORD                 
		          , DEL_YN                   
		          , REG_NO                   
		          , REG_DT                   
		          , UPD_NO                   
		          , UPD_DT                   
		      ) VALUES (
		            #{planDtlSq}
		          , #{goodsCd}
		          , #{dispOrd}
		          , 'N'
		          , #{regNo}
		          , now()
		          , #{updNo}
		          , now()
		       )
	</insert>
	
	<insert id="createPlanCustGb" parameterType="Plan">
		/* TsaPlan.createPlanCustGb */
		INSERT INTO TB_PLAN_CUST_DIVISION
						(
							PLAN_SQ
						  , CUST_GB
						  , REG_NO
						  , REG_DT
						)
				VALUES(
						   #{planSq}
						,  #{custGb}
					    ,  #{regNo}
					   , CURRENT_TIMESTAMP
					  )
	</insert>
	
	<delete id="deletePlanCustGb" parameterType="Plan">
		/* TsaPlan.deletePlanCustGb */
		DELETE 
		FROM TB_PLAN_CUST_DIVISION
		WHERE PLAN_SQ= #{planSq}
	</delete>
	
	<insert id="createPlanCustGrade" parameterType="Plan">
		/* TsaPlan.createPlanCustGrade */
		INSERT INTO TB_PLAN_CUST_GRADE
				(
					PLAN_SQ
				  , CUST_GRADE
				  , REG_NO
				  , REG_DT
				)
			VALUES(
					#{planSq}
				  , #{custGrade}
				  , #{regNo}
				  , CURRENT_TIMESTAMP
				 )
	</insert>
	
	<delete id="deletePlanCustGrade" parameterType="Plan">
		/* TsaPlan.deletePlanCustGrade */
		DELETE 
		FROM TB_PLAN_CUST_GRADE
		WHERE PLAN_SQ= #{planSq}
	</delete>
	
	<select id="getPlanCustGbList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.createPlanCustGrade */
		SELECT CUST_GB 
			  ,PLAN_SQ 
			  ,REG_DT 
			  ,REG_NO 
		FROM TB_PLAN_CUST_DIVISION
		WHERE PLAN_SQ = #{planSq}
	</select>
	
	<select id="getPlanCustGradeList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanCustGradeList */
		SELECT CUST_GRADE 
		      ,PLAN_SQ 
		      ,REG_DT 
		      ,REG_NO 
		FROM TB_PLAN_CUST_GRADE
		WHERE PLAN_SQ = #{planSq}
	</select>
	
	<insert id="createPlanContents" parameterType="Plan" useGeneratedKeys="true" keyProperty="planContSq">
		/* TsaPlan.createPlanContents */
		INSERT INTO 
			TB_PLAN_CONTENTS(
							 PLAN_CONT_SQ
							, PLAN_SQ
							, TMPL_TYPE
							, TITLE
							, LINK_URL
							, LINK_OPEN_GB
							, CPN_NOTE0
							, CPN_NOTE1
							, CPN_NOTE2
							, CPN_NOTE3
							, CPN_NOTE4
							, CPN_NOTE5
							, CPN_NOTE6
							, CPN_NOTE7
							, CPN_NOTE8
							, CPN_NOTE9
							, DISP_YN
							, DISP_ORD
							, REG_NO
							, REG_DT
							, UPD_NO
							, UPD_DT

						)
			  VALUES(
			  		   #{planContSq}
					 , #{planSq}
					 , #{tmplType}
					 , #{title}
					 , #{linkUrl}
					 , #{linkOpenGb}
					 , #{cpnNote0}
					 , #{cpnNote1}
					 , #{cpnNote2}
					 , #{cpnNote3}
					 , #{cpnNote4}
					 , #{cpnNote5}
					 , #{cpnNote6}
					 , #{cpnNote7}
					 , #{cpnNote8}
					 , #{cpnNote9}
					 , #{dispYn}
					 , #{dispOrd}
					 , #{regNo}
					 , now()
					 , #{updNo}
					 , now()
				   )
			  ON
			     DUPLICATE KEY 
			 UPDATE 
			 	   TITLE=#{title}
			 	 , TMPL_TYPE = #{tmplType}
			   	 , LINK_URL=#{linkUrl}
			   	 , LINK_OPEN_GB=#{linkOpenGb}
			   	 , CPN_NOTE0 = #{cpnNote0}
		         , CPN_NOTE1 = #{cpnNote1}
		         , CPN_NOTE2 = #{cpnNote2}
		         , CPN_NOTE3 = #{cpnNote3}
		         , CPN_NOTE4 = #{cpnNote4}
		         , CPN_NOTE5 = #{cpnNote5}
		         , CPN_NOTE6 = #{cpnNote6}
		         , CPN_NOTE7 = #{cpnNote7}
		         , CPN_NOTE8 = #{cpnNote8}
		         , CPN_NOTE9 = #{cpnNote9}
			     , DISP_YN=#{dispYn}
			     , DISP_ORD=#{dispOrd}
			     , UPD_NO=#{updNo}
			     , UPD_DT=now()
	</insert>
	
	
	<select id="getPlanContentsCount" parameterType="Plan" resultType="int">
		/* TsaPlan.getPlanContentsCount */
		SELECT COUNT(*) AS CNT
		FROM TB_PLAN_CONTENTS
		WHERE PLAN_SQ = #{planSq}
	</select>
	
	
	<insert id="createPlanContentsItem" parameterType="Plan">
		/* TsaPlan.createPlanContentsItem */
		INSERT INTO 
			TB_PLAN_CONTENTS_ITEM(
						PLAN_CONT_SQ
					  , ITEM_VAL
					  , LINK_URL
					  , LINK_OPEN_GB
					  , REVIEW_DISP_STDT
					  , REVIEW_DISP_EDDT
					  , REG_NO
					  , REG_DT
					)
			VALUES(
					#{planContSq}
				  , #{itemVal}
				  , #{linkUrl}
				  , #{linkOpenGb}
				  , #{reviewDispStdt}
				  , #{reviewDispEddt}
				  , #{regNo}
				  , now()
				 )
			 ON
			     DUPLICATE KEY 
			 UPDATE 
			 	 ITEM_VAL= #{itemVal}
			   , LINK_URL = #{linkUrl}
			   , LINK_OPEN_GB = #{linkOpenGb}
			   , REVIEW_DISP_STDT= #{reviewDispStdt}
			   , REVIEW_DISP_EDDT= #{reviewDispEddt}
			   , REG_NO= #{regNo}
			   , REG_DT= now()
	</insert>
	
	<select id="getPlanContentReviewList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanContentReviewList */
		SELECT A.PLAN_CONT_SQ 
			  ,A.PLAN_SQ 
		      ,A.TMPL_TYPE 
		      ,A.TITLE 
		      ,A.LINK_URL 
		      ,A.LINK_OPEN_GB
		      ,A.DISP_YN 
		      ,A.DISP_ORD 
		      ,A.REG_NO 
		      ,B.ITEM_VAL AS reItemVal
		      ,SUBSTRING_INDEX(B.REVIEW_DISP_STDT, ' ', 1) AS startSearchDate
		      ,SUBSTRING_INDEX(B.REVIEW_DISP_STDT, ' ', -1) AS startSearchTime
		      ,SUBSTRING_INDEX(B.REVIEW_DISP_EDDT , ' ', 1) AS endSearchDate
		      ,SUBSTRING_INDEX(B.REVIEW_DISP_EDDT , ' ', -1) AS endSearchTime
		      ,C.GOODS_NM AS reItemNm
		FROM TB_PLAN_CONTENTS A LEFT JOIN TB_PLAN_CONTENTS_ITEM B ON A.PLAN_CONT_SQ = B.PLAN_CONT_SQ
								LEFT JOIN TB_GOODS C ON B.ITEM_VAL = C.GOODS_CD 
	WHERE A.PLAN_SQ = #{planSq} AND A.TMPL_TYPE = #{tmplType}
	</select>
	
	<select id="getPlanContentCouponList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanContentCouponList */
		SELECT A.PLAN_CONT_SQ 
			  ,A.PLAN_SQ 
		      ,A.TMPL_TYPE 
		      ,A.TITLE 
		      ,A.LINK_URL 
		      ,A.LINK_OPEN_GB
		      ,A.CPN_NOTE0
		      ,A.CPN_NOTE1
		      ,A.CPN_NOTE2
		      ,A.CPN_NOTE3
		      ,A.CPN_NOTE4
		      ,A.CPN_NOTE5
		      ,A.CPN_NOTE6
		      ,A.CPN_NOTE7
		      ,A.CPN_NOTE8
		      ,A.CPN_NOTE9
		      ,A.DISP_YN 
		      ,A.DISP_ORD 
		      ,A.REG_NO 
		      ,B.ITEM_VAL AS cuItemVal
		      ,C.CPN_ID 
		      ,C.CPN_NM AS cuItemNm
		FROM TB_PLAN_CONTENTS A LEFT JOIN TB_PLAN_CONTENTS_ITEM B ON A.PLAN_CONT_SQ = B.PLAN_CONT_SQ
								LEFT JOIN TB_COUPON C ON B.ITEM_VAL = C.CPN_ID 
		WHERE A.PLAN_SQ = #{planSq} AND A.TMPL_TYPE = #{tmplType}
	</select>
	
	<select id="getPlanContentCouponNotice" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanContentCouponNotice */
		SELECT A.PLAN_CONT_SQ 
			  ,A.PLAN_SQ 
		      ,A.TMPL_TYPE 
		      ,A.TITLE 
		      ,A.LINK_URL 
		      ,A.LINK_OPEN_GB
		      ,A.CPN_NOTE0
		      ,A.CPN_NOTE1
		      ,A.CPN_NOTE2
		      ,A.CPN_NOTE3
		      ,A.CPN_NOTE4
		      ,A.CPN_NOTE5
		      ,A.CPN_NOTE6
		      ,A.CPN_NOTE7
		      ,A.CPN_NOTE8
		      ,A.CPN_NOTE9
		      ,A.DISP_YN 
		      ,A.DISP_ORD 
		      ,A.REG_NO 
		FROM TB_PLAN_CONTENTS A  
		WHERE A.PLAN_SQ = #{planSq} AND A.TMPL_TYPE = #{tmplType}
	</select>
	
	<select id="getPlanContentFileList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanContentFileList */
		SELECT A.PLAN_CONT_SQ 
			  ,A.PLAN_SQ 
		      ,A.TMPL_TYPE 
		      ,A.TITLE 
		      ,B.LINK_URL 
		      ,B.LINK_OPEN_GB
		      ,A.DISP_YN 
		      ,A.DISP_ORD 
		      ,A.REG_NO 
		      ,B.ITEM_VAL AS fileItemVal
		      ,B.PLAN_CONT_ITEM_SQ 
		FROM TB_PLAN_CONTENTS A LEFT JOIN TB_PLAN_CONTENTS_ITEM B ON A.PLAN_CONT_SQ = B.PLAN_CONT_SQ
		WHERE A.PLAN_SQ = #{planSq} AND A.TMPL_TYPE = #{tmplType}
		ORDER BY B.PLAN_CONT_ITEM_SQ 
	</select>
	
	<select id="getPlanContentNoticeList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanContentNoticeList */
		SELECT A.PLAN_CONT_SQ 
			  ,A.PLAN_SQ 
		      ,A.TMPL_TYPE 
		      ,A.DISP_YN 
		      ,A.DISP_ORD 
		      ,A.REG_NO 
		      ,B.ITEM_VAL AS noticeItemVal
		      ,B.PLAN_CONT_ITEM_SQ 
		FROM TB_PLAN_CONTENTS A LEFT JOIN TB_PLAN_CONTENTS_ITEM B ON A.PLAN_CONT_SQ = B.PLAN_CONT_SQ
		WHERE A.PLAN_SQ = #{planSq} AND A.TMPL_TYPE = #{tmplType}
		ORDER BY B.PLAN_CONT_ITEM_SQ 
	</select>
	
	<!-- 기획전 템플릿 이미지 파일 삭제 -->
	<delete id="deletePlanTemplateFile" parameterType="Plan">
		/* TsaPlan.deletePlanTemplateFile */
		DELETE FROM TB_PLAN_CONTENTS_ITEM
		WHERE  PLAN_CONT_ITEM_SQ = #{planContItemSq}
	</delete>
	
	<select id="getPlanContentFsrcList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanContentFsrcList */
		SELECT 
		  	  B.PLAN_SQ
		     ,B.FRONT_GB
		     ,B.DISP_YN 
		     ,B.FSRC_MOB 
		     ,B.FSRC_PC 
		     ,B.REG_NO
		     ,B.REG_DT
		     ,B.UPD_NO
		     ,B.UPD_DT
		FROM TB_PLAN_FSRC B
		WHERE B.PLAN_SQ = #{planSq}
			AND B.FRONT_GB = #{fontGb}  
	</select>
	
	<select id="getPlanContentGoodsList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanContentGoodsList */
		SELECT A.PLAN_CONT_SQ 
			  ,A.PLAN_SQ 
		      ,A.TMPL_TYPE 
		      ,A.TITLE 
		      ,A.LINK_URL 
		      ,A.LINK_OPEN_GB
		      ,A.DISP_YN 
		      ,A.DISP_ORD 
		      ,A.REG_NO 
		FROM TB_PLAN_CONTENTS A 
		WHERE A.PLAN_SQ = #{planSq}  AND TMPL_TYPE IN ('G082_50','G082_51','G082_52')
	</select>
	
	<select id="getPlanContentGoodsDetailList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanContentGoodsDetailList */	
		SELECT  B.PLAN_CONT_SQ 
		      ,B.PLAN_CONT_ITEM_SQ
		      ,B.ITEM_VAL as goodsItemVal
		      ,C.GOODS_NM
		      ,C.LIST_PRICE 
		      ,C.CURR_PRICE
		      ,C.GOODS_STAT 
		      ,D.BRAND_KNM 
		      ,D.BRAND_ENM 
		FROM TB_PLAN_CONTENTS_ITEM B LEFT JOIN TB_GOODS C ON B.ITEM_VAL = C.GOODS_CD 
									 LEFT JOIN TB_BRAND D ON C.BRAND_CD = D.BRAND_CD
		WHERE B.PLAN_CONT_SQ IN 
								(
									SELECT A.PLAN_CONT_SQ 
									FROM TB_PLAN_CONTENTS A 
									WHERE A.PLAN_SQ = #{planSq}  AND TMPL_TYPE IN ('G082_50','G082_51','G082_52') AND PLAN_CONT_SQ = #{planContSq}
								)
	</select>
	
	<delete id="deletePlanContentItem" parameterType="Plan">
		/* TsaPlan.deletePlanContentItem */	
		DELETE 
		FROM TB_PLAN_CONTENTS_ITEM
		WHERE PLAN_CONT_SQ = #{planContSq}
	</delete>
	
	<delete id="deletePlanContentItemGoods" parameterType="Plan">
		/* TsaPlan.deletePlanContentItemGoods */	
		DELETE 
		FROM TB_PLAN_CONTENTS
		WHERE PLAN_CONT_SQ = #{planContSq}
	</delete>
	
	<!-- 이벤트 출석체크 모듈-->
	<insert id="savePlanAttendBenefit" parameterType="Plan">
		/* TsaPlan.savePlanAttendBenefit */	
		INSERT INTO TB_PLAN_ATTEND
		      (PLAN_SQ
		      , BAS_DAYS
		      , BENEFIT_GB
		      , PNT_AMT
		      , PNT_USABLE_DAY
		      , CPN_ID
		      , REG_NO
		      , REG_DT
		      , UPD_NO
		      , UPD_DT
		      )
		VALUES
		      ( #{planSq}
		      , #{basDays}
		      , #{benefitGb}
		      , #{pntAmt}
		      , #{pntUsableDay}
		      , #{cpnId}
		      , #{regNo}
		      , CURRENT_TIMESTAMP
		      , #{updNo}
		      , CURRENT_TIMESTAMP
		      )
		ON DUPLICATE KEY
		UPDATE
		  BENEFIT_GB = #{benefitGb}  
		, PNT_AMT    = #{pntAmt}
		, PNT_USABLE_DAY  = #{pntUsableDay}
		, CPN_ID     = #{cpnId}
		, UPD_DT     = now()                                             
		, UPD_NO     = #{updNo}      
	</insert>
	
	<!-- 혜택구분 불러오기 -->
	<select id="getPlanAttendBenefitList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanAttendBenefitList */
		SELECT P.PLAN_SQ 
		      ,PA.BAS_DAYS 
		      ,PA.BENEFIT_GB 
		      ,PA.CPN_ID 
		      ,PA.PNT_AMT 
		      ,PA.PNT_USABLE_DAY
		      ,(CASE PA.BENEFIT_GB WHEN 'P' THEN '포인트' 
                           WHEN 'C' THEN '쿠폰'
                           ELSE '포인트/쿠폰' END) AS BENEFIT
		FROM TB_PLAN P INNER JOIN TB_PLAN_ATTEND PA ON P.PLAN_SQ = PA.PLAN_SQ 
		WHERE 1=1
		 AND P.PLAN_SQ = #{planSq}
		 AND P.PLAN_SQ = PA.PLAN_SQ
	</select>
	
	<delete id="deletePlanAttendBenefitList" parameterType="Plan">
		/* TsaPlan.deletePlanAttendBenefitList */	
		DELETE 
		FROM TB_PLAN_ATTEND
		WHERE PLAN_SQ = #{planSq}
	</delete>
	
	<insert id="createPlanAttendBenefitCopy" parameterType="Plan">
		/* TsaPlan.createPlanAttendBenefitCopy */
		INSERT INTO TB_PLAN_ATTEND
		      (PLAN_SQ
		      , BAS_DAYS
		      , BENEFIT_GB
		      , PNT_AMT
		      , PNT_USABLE_DAY
		      , CPN_ID
		      , REG_NO
		      , REG_DT
		      , UPD_NO
		      , UPD_DT
		      )
		SELECT
		       #{planSq}
		      , BAS_DAYS  
		      , BENEFIT_GB
		      , PNT_AMT
		      , PNT_USABLE_DAY  
		      , CPN_ID    
		      , #{regNo}
		      , CURRENT_TIMESTAMP
		      , #{updNo}
		      , CURRENT_TIMESTAMP
		FROM TB_PLAN_ATTEND
		WHERE 1=1
		  AND PLAN_SQ = #{copyPlanSq}
		  AND BAS_DAYS = #{basDays}
	</insert>
	
	<select id="getPlanReplyList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanReplyList */
		SELECT Z.*
		FROM(
		      SELECT @rownum := @rownum + 1 AS RNUM
		             ,A.*
		       FROM(
		             SELECT  P.PLAN_SQ 
		                   , PE.ENTRY_DT 
		                   , PE.ENTRY_VAL1 
		                   , PE.ENTRY_CUST_NO AS CUST_NO
		                   , PE.PLAN_ENTRY_SQ 
		                   , C.CUST_NM
		                   , C.CUST_ID
		             FROM TB_PLAN P INNER JOIN TB_PLAN_ENTRY PE
		                              ON P.PLAN_SQ = PE.PLAN_SQ
		                             INNER JOIN TB_CUSTOMER C
		                              ON PE.ENTRY_CUST_NO = C.CUST_NO 
		                            JOIN ( SELECT @rownum := 0) R
		             WHERE P.PLAN_SQ = #{planSq}
		             <if test="planEntrySq != null and planEntrySq != ''">
		              AND   PE.PLAN_ENTRY_SQ = #{planEntrySq}  
		             </if>
		             ORDER  BY PLAN_ENTRY_SQ DESC
		<include refid="getListPagingCondition_sql"/>
	</select>
	
	<select id="getPlanReplyListCount" parameterType="Plan" resultType="int">
		/* TsaPlan.getPlanReplyListCount */
		SELECT COUNT(*) AS CNT
		FROM TB_PLAN P INNER JOIN TB_PLAN_ENTRY PE
		                 ON P.PLAN_SQ = PE.PLAN_SQ
		WHERE P.PLAN_SQ = #{planSq}
	</select>
	
	<select id="getPlanReplyAttachList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanReplyAttachList */
		SELECT  PEA.PLAN_ENTRY_AT_SQ 
		      , PEA.SYS_FILE_NM AS SYS_FILE
		FROM TB_PLAN_ENTRY PE INNER JOIN TB_PLAN_ENTRY_ATTACH PEA 
		                      ON PE.PLAN_ENTRY_SQ = PEA.PLAN_ENTRY_SQ 
		WHERE PE.PLAN_ENTRY_SQ = #{planEntrySq}
		AND PE.PLAN_ENTRY_SQ = PEA.PLAN_ENTRY_SQ
	</select>
	
	<delete id="deleteReply" parameterType="Plan">
		/* TsaPlan.deleteReply */
		DELETE FROM TB_PLAN_ENTRY
		WHERE PLAN_ENTRY_SQ = #{planEntrySq}
	</delete>
	
	<delete id="deleteReplyAttach" parameterType="Plan">
		/* TsaPlan.deleteReplyAttach */
		DELETE FROM TB_PLAN_ENTRY_ATTACH
		WHERE PLAN_ENTRY_SQ = #{planEntrySq}
	</delete>
	
	<!-- 기획전 카테고리 목록 -->
	<select id="getPlanningCategoryList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanningCategoryList */
		SELECT C.CATE1_NO AS CATE_NO            /*카테고리번호*/
		     , C.CATE1_NM AS CATE_NM /*카테고리명*/
		FROM TB_CATE1 C
		WHERE 1=1
		AND    C.CATE_GB = 'G032_101' /*BYITEM*/
		AND    C.CATE_TYPE = 'G031_10' /*상품카테고리*/
		AND    C.DISP_YN = 'Y' /*전시하는 카테고리*/
	</select>
	
	<select id="getPlanAttendCoupon" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getPlanAttendCoupon */
		SELECT C.CPN_ID 
		      ,C.CPN_NM 
		      ,C.CPN_STAT 
		FROM TB_COUPON C
		WHERE 1=1
		AND C.CPN_ID = #{cpnId}
		AND C.CPN_TYPE IN ('G230_11' ,'G230_12', 'G230_13', 'G230_30', 'G230_20')
		AND C.CPN_STAT = 'G232_11'
		AND NOW() <![CDATA[<=]]> IF (C.PD_GB = 'D', CONCAT(CURRENT_DATE + INTERVAL C.AVAIL_DAYS DAY, ' 23:59:59'), C.AVAIL_EDDT)
	</select>
	
	<select id="getShotSelfBrandList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getShotSelfBrandList */
		SELECT SDB.BRAND_GROUP_NO
		      , CASE WHEN BG.DISP_NM_LANG = 'EN' THEN
		                BG.BRAND_GROUP_ENM
		            ELSE
		                BG.BRAND_GROUP_KNM
		       END                                     AS BRAND_NM
		  , SDB.CORNER_DISP_TYPE
		  , SDB.DISP_ORD
		  , SDB.REG_DT
		FROM TB_SHOT_DELV_BRAND SDB INNER JOIN TB_BRAND_GROUP BG 
		  ON SDB.BRAND_GROUP_NO = BG.BRAND_GROUP_NO 
		WHERE BG.USE_YN = 'Y'
		ORDER BY DISP_ORD 
	</select>
	
	<update id="updateShotBrand" parameterType="Plan">
		/* TsaPlan.updateShotBrand */
		UPDATE TB_SHOT_DELV_BRAND  
		SET DISP_ORD         = #{dispOrd}
		   ,CORNER_DISP_TYPE = #{cornerDispType}
		WHERE BRAND_GROUP_NO = #{brandGroupNo}
	</update>
	
	<!-- 기획전 코너 상품 목록 조회 건수 -->
	<select id="getShotBrandGoodsListCount" parameterType="Plan" resultType="int">
		/* TsaPlan.getShotBrandGoodsListCount */
		SELECT COUNT(*) AS CNT
		FROM TB_SHOT_DELV_GOODS
		AND DEL_YN = 'N'
		WHERE BRAND_GROUP_NO = #{brandGroupNo}
	</select>
	
	<select id="getShotBrandGoodsList" parameterType="Plan" resultType="Plan">
		/* TsaPlan.getShotBrandGoodsList */
		SELECT Q.*
		    , IFNULL((SELECT STOCK_QTY FROM TB_GOODS_STOCK WHERE GOODS_CD = Q.GOODS_CD),0) AS STOCK_QTY_SUM
		    , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG  WHERE GOODS_CD = Q.GOODS_CD AND COLOR_CD = Q.MAIN_COLOR_CD AND DEFAULT_IMG_YN = 'Y' ) AS SYS_IMG_NM
		    , CASE IFNULL((SELECT COUNT(SYS_IMG_NM) FROM TB_GOODS_IMG  WHERE GOODS_CD = Q.GOODS_CD AND COLOR_CD = Q.MAIN_COLOR_CD AND DEFAULT_IMG_YN = 'Y' ),0) 
		            WHEN 0 THEN 'N'
		            ELSE 'Y' END AS GOODS_IMAGE_YN
		FROM (     
		      SELECT Z.*
		      FROM (
		          SELECT A.*, @rownum := @rownum + 1 AS RNUM 
		          FROM (
		              SELECT
		                       G.GOODS_CD                                                                   
		                     , G.GOODS_TYPE                                                                 
		                     , G.SUPPLY_COMP_CD                                                             
		                     , G.SUPPLY_GOODS_CD                                                            
		                     , G.GOODS_NUM                                                                  
		                     , G.GOODS_NM                                                                   
		                     , G.GOODS_STAT                                                                 
		                     , G.LIST_PRICE                                                                 
		                     , G.CURR_PRICE                                                                 
		                     , G.DC_RATE      
		                     , G.TAG_PRICE                                                              
		                     , CASE G.FORMAL_GB WHEN 'G009_20' THEN '이월' ELSE '정상' END AS FORMAL_GB                                                                 
		                     , G.SEASON_CD       
		                     , (SELECT SUPPLY_COMP_NM
		              FROM   TB_SUPPLY_COMPANY
		              WHERE  SUPPLY_COMP_CD = G.SUPPLY_COMP_CD
		               ) AS SUPPLY_COMP_NM                                                           
		                   , G.ERP_STOCK_LINK_YN                                                          
		                   , G.ERP_PRICE_LINK_YN                                                          
		                   , G.CHANGEABLE_YN                                                              
		                   , G.RETURNABLE_YN                                                              
		                   , G.NEW_CUST_ORD_YN                                                            
		                   , G.GIFT_PACK_YN                                                               
		                   , G.DELV_FEE_CD                                                                
		                   , G.MAIN_COLOR_CD                                                              
		                   , G.FRST_CFRM_DT                  
		                   , G.SELL_FEE_RATE                                                              
		                   , G.PRICE_UPD_DT                   
		                   , SDE.BRAND_GROUP_NO    
		                   , SDE.REG_DT                   
		             FROM TB_GOODS G
		                   JOIN ( SELECT @rownum := 0) R
		                   INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
		                   INNER JOIN TB_SHOT_DELV_GOODS SDE ON G.GOODS_CD = SDE.GOODS_CD  
		             WHERE 1=1
		                 AND SDE.BRAND_GROUP_NO = #{brandGroupNo}
		                 AND SDE.DEL_YN = 'N'
		             ORDER BY G.REG_DT DESC, G.GOODS_CD
		<include refid="getListPagingCondition_sql"/>
		)Q
	</select>
	
	<insert id="saveShotBrandGoods" parameterType="Plan">
		/* TsaPlan.saveShotBrandGoods */
		INSERT INTO TB_SHOT_DELV_GOODS
		       (
		          BRAND_GROUP_NO
		        , GOODS_CD
		        , DISP_ORD
		        , DEL_YN
		        , REG_NO
		        , REG_DT
		        , UPD_NO
		        , UPD_DT
		       )
		       VALUES
		       (
		          #{brandGroupNo}
		        , #{goodsCd}
		        , #{dispOrd}
		        , #{delYn}
		        , #{regNo}
		        , now()
		        , #{updNo}
		        , now()
		       )
		        ON DUPLICATE KEY
		          UPDATE
		        DEL_YN = 'N'
		      , UPD_DT = now()
		      , UPD_NO = #{updNo}                                  
	</insert>
	
	<update id="deleteShotBrandGoods" parameterType="Plan">
		/* TsaPlan.updateShotBrandGoods */
		UPDATE TB_SHOT_DELV_GOODS
		SET  DEL_YN = 'Y'
		   , UPD_NO = #{updNo}
		   , UPD_DT = NOW()
		WHERE BRAND_GROUP_NO = #{brandGroupNo}
		 AND  GOODS_CD       = #{goodsCd}
	</update>
</mapper>










