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

	<!-- 목록 페이징 정보 -->
	<sql id="getListPagingCondition_sql">
		<choose>
		<when test="pageable != null">
		    ) A
		WHERE NUMB BETWEEN  #{pageable.startRow} AND #{pageable.endRow}
		</when>
		<otherwise>
		    ) A
		</otherwise>
		</choose>
	</sql>
	
	<!-- 카드무이자할부 목록 건수-->
	<select id="getCardInterestListCount" parameterType="CardPromotion" resultType="int">
		/* TsaCardPromotionDao.getCardInterestListCount */
		SELECT COUNT(1)
		FROM TB_CARD_PROMOTION 
		WHERE PRMT_GB = 'B'
		<if test="condition != null and condition != ''">
		    <choose>
		        <when test='search != null and search == "1"'>
		AND CARD_PRMT_SQ = #{condition}
		        </when>
		        <otherwise >
		AND UPPER(PRMT_NM) LIKE CONCAT('%',UPPER(#{condition}),'%')
		        </otherwise>
		    </choose>
		</if>
		<if test="stDate != null and stDate != ''">
		AND PRMT_EDD >= REPLACE(#{stDate},'-','')
		</if>
		<if test="edDate != null and edDate != ''">
		<![CDATA[
		AND PRMT_STD <= REPLACE(#{edDate},'-','')
		]]>
		</if>
		<if test='beforSkipFlag != null and beforSkipFlag == "Y"'>
		AND PRMT_EDD >= NOW() 
		</if>
	</select>
	
	<!-- 카드무이자할부 목록 -->
	<select id="getCardInterestList" parameterType="CardPromotion" resultType="CardPromotion">
		/* TsaCardPromotionDao.getCardInterestList */
		SELECT A.*
		FROM (
		    SELECT CARD_PRMT_SQ
		         , PRMT_NM
		         , DATE_FORMAT(PRMT_STD, '%Y-%m-%d') AS PRMT_STD
		         , DATE_FORMAT(PRMT_EDD, '%Y-%m-%d') AS PRMT_EDD
		         , PRMT_GB
		         , DC_GB
		         , LINK_URL
		         , NOTE
		         , DISP_YN
		         , FN_GET_USER_NM(REG_NO) AS REG_NM
		         , DATE_FORMAT(REG_DT, '%Y%m%d%H%i%S') AS REG_DT
		         , FN_GET_USER_NM(UPD_NO) AS UPD_NM
		         , DATE_FORMAT(UPD_DT, '%Y%m%d%H%i%S') AS UPD_DT
		         , RANK() OVER(ORDER BY CARD_PRMT_SQ DESC)        AS NUMB
		    FROM TB_CARD_PROMOTION 
		    WHERE PRMT_GB = 'B'
		    <if test="condition != null and condition != ''">
		        <choose>
		            <when test='search != null and search == "1"'>
		    AND CARD_PRMT_SQ = #{condition}
		            </when>
		            <otherwise >
		    AND UPPER(PRMT_NM) LIKE CONCAT('%',UPPER(#{condition}),'%')
		            </otherwise>
		        </choose>
		    </if>
		    <if test="stDate != null and stDate != ''">
		    AND PRMT_EDD >= REPLACE(#{stDate},'-','')
		    </if>
		    <if test="edDate != null and edDate != ''">
		    <![CDATA[
		    AND PRMT_STD <= REPLACE(#{edDate},'-','')
		    ]]>
		    </if>
		    <if test='beforSkipFlag != null and beforSkipFlag == "Y"'>
		    AND PRMT_EDD >= DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
		    </if>
		<include refid="getListPagingCondition_sql"/>
	</select>
	
	<!-- 카드 프로모션 조건 목록 -->
	<select id="getCardPromotionConditionList" parameterType="int" resultType="CardPromotionCondition">
		/* TsaCardPromotionDao.getCardPromotionConditionList */
		SELECT CARD_PRMT_CDT_SQ
		     , CARD_PRMT_SQ
		     , MIN_PAY_AMT
		     , DC_WAY
		     , DC_VAL
		     , MAX_DC_AMT
		     , MIN_NO_ITRT
		     , MAX_NO_ITRT
		     , FN_GET_USER_NM(REG_NO) AS REG_NM
		     , DATE_FORMAT(REG_DT, '%Y%m%d%H%i%S') AS REG_DT
		     , FN_GET_USER_NM(UPD_NO) AS UPD_NM
		     , DATE_FORMAT(UPD_DT, '%Y%m%d%H%i%S') AS UPD_DT
		FROM TB_CARD_PROMOTION_CONDITION 
		WHERE CARD_PRMT_SQ = #{cardPrmtSq}
		ORDER BY CARD_PRMT_CDT_SQ
	</select>
	
	<!-- 카드 프로모션 대상 목록 -->
	<select id="getCardPromotionTargetList" parameterType="int" resultType="CardPromotionTarget">
		/* TsaCardPromotionDao.getCardPromotionTargetList */
		SELECT A.CARD_PRMT_CDT_SQ
		     , A.PRMT_TARGET_CD
		     , B.CD_NM AS PRMT_TARGET_NM
		     , FN_GET_USER_NM(A.REG_NO) AS REG_NM
		     , DATE_FORMAT(A.REG_DT, '%Y%m%d%H%i%S') AS REG_DT
		FROM TB_CARD_PROMOTION_TARGET A
		INNER JOIN TB_COMMON_CODE B ON A.PRMT_TARGET_CD = B.CD
		                            AND B.CD_GB = 'G941'
		WHERE A.CARD_PRMT_CDT_SQ = #{cardPrmtCdtSq}
		ORDER BY A.PRMT_TARGET_CD
	</select>
	
	<!-- 카드프로모션 저장 -->
	<insert id="saveCardPromotion" parameterType="CardPromotion"  keyProperty="cardPrmtSq">
		/* TsaGoods.saveCardPromotion */
		INSERT INTO TB_CARD_PROMOTION (
		       CARD_PRMT_SQ
		     , PRMT_NM
		     , PRMT_STD
		     , PRMT_EDD
		     , PRMT_GB
		     , DC_GB
		     , LINK_URL
		     , NOTE
		     , DISP_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{cardPrmtSq}
		     , #{prmtNm}
		     , #{prmtStd}
		     , #{prmtEdd}
		     , #{prmtGb}
		     , #{dcGb}
		     , #{linkUrl}
		     , #{note}
		     , #{dispYn}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       PRMT_NM = #{prmtNm}
		     , PRMT_STD = #{prmtStd}
		     , PRMT_EDD = #{prmtEdd}
		     , LINK_URL = #{linkUrl}
		     , NOTE = #{note}
		     , DISP_YN = #{dispYn}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 카드프로모션 행사조건 삭제 -->
	<delete id="deleteCardPromotionCondition"  parameterType="int" >
		/* TsaGoods.deleteCardPromotionCondition */
		DELETE FROM TB_CARD_PROMOTION_CONDITION 
		WHERE CARD_PRMT_CDT_SQ = #{cardPrmtCdtSq}
	</delete>
	
	<!-- 카드프로모션 행사조건 저장 -->
	<insert id="saveCardPromotionCondition" parameterType="CardPromotionCondition"  keyProperty="cardPrmtCdtSq">
		/* TsaGoods.saveCardPromotionCondition */
		INSERT INTO TB_CARD_PROMOTION_CONDITION (
		       CARD_PRMT_CDT_SQ
		     , CARD_PRMT_SQ
		     , MIN_PAY_AMT
		     , DC_WAY
		     , DC_VAL
		     , MAX_DC_AMT
		     , MIN_NO_ITRT
		     , MAX_NO_ITRT
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{cardPrmtCdtSq}
		     , #{cardPrmtSq}
		     , #{minPayAmt}
		     , #{dcWay}
		     , #{dcVal}
		     , #{maxDcAmt}
		     , #{minNoItrt}
		     , #{maxNoItrt}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       MIN_PAY_AMT = #{minPayAmt}
		     , DC_WAY = #{dcWay}
		     , DC_VAL = #{dcVal}
		     , MAX_DC_AMT = #{maxDcAmt}
		     , MIN_NO_ITRT = #{minNoItrt}
		     , MAX_NO_ITRT = #{maxNoItrt}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 카드프로모션 타겟 삭제 -->
	<delete id="deleteCardPromotionTarget"  parameterType="int" >
		/* TsaGoods.deleteCardPromotionTarget */
		DELETE FROM TB_CARD_PROMOTION_TARGET 
		WHERE CARD_PRMT_CDT_SQ = #{cardPrmtCdtSq}
	</delete>
	
	<!-- 카드프로모션 타겟 등록 -->
	<insert id="createCardPromotionTarget" parameterType="CardPromotionTarget">
		/* TsaGoods.createCardPromotionTarget */
		INSERT INTO TB_CARD_PROMOTION_TARGET (
		       CARD_PRMT_CDT_SQ
		     , PRMT_TARGET_CD
		     , REG_NO
		     , REG_DT
		)
		VALUES (
		       #{cardPrmtCdtSq}
		     , #{prmtTargetCd}
		     , #{regNo}
		     , NOW()
		)
	</insert>
	
	<!-- 카드프로모션 비노출처리 -->
	<delete id="updateCardPromotionDsipYn"  parameterType="CardPromotion" >
		/* TsaGoods.updateCardPromotionDsipYn */
		UPDATE TB_CARD_PROMOTION 
		SET DISP_YN = 'N'
		  , UPD_NO = #{updNo} 
		  , UPD_DT = NOW()
		WHERE 1 = 1
		<choose>
		    <when test="arrCardPrmtSq != null and arrCardPrmtSq != ''">
		AND CARD_PRMT_SQ IN
		        <foreach collection="arrCardPrmtSq" item="item" index="index"  open="(" close=")" separator=",">
		        #{item}
		        </foreach>
		    </when>
		<otherwise>
		AND CARD_PRMT_SQ = #{cardPrmtSq}
		</otherwise>
		</choose>
	</delete>
	
	<!-- 카드프로모션 삭제 -->
	<delete id="deleteCardPromotion"  parameterType="int" >
		/* TsaGoods.deleteCardPromotion */
		DELETE FROM TB_CARD_PROMOTION
		WHERE CARD_PRMT_SQ = #{cardPrmtSq}
	</delete>
	
	<!-- 카드프로모션 목록 건수-->
	<select id="getCardPromotionListCount" parameterType="CardPromotion" resultType="int">
		/* TsaCardPromotionDao.getCardPromotionListCount */
		SELECT COUNT(1)
		FROM TB_CARD_PROMOTION A
		INNER JOIN TB_CARD_PROMOTION_CONDITION B ON A.CARD_PRMT_SQ = B.CARD_PRMT_SQ
		WHERE A.PRMT_GB = 'A'
		<if test="condition != null and condition != ''">
		    <choose>
		        <when test='search != null and search == "1"'>
		AND A.CARD_PRMT_SQ = #{condition}
		        </when>
		        <otherwise >
		AND UPPER(A.PRMT_NM) LIKE CONCAT('%',UPPER(#{condition}),'%')
		        </otherwise>
		    </choose>
		</if>
		<if test="stDate != null and stDate != ''">
		AND A.PRMT_EDD >= REPLACE(#{stDate},'-','')
		</if>
		<if test="edDate != null and edDate != ''">
		<![CDATA[
		AND A.PRMT_STD <= REPLACE(#{edDate},'-','')
		]]>
		</if>
		<if test='beforSkipFlag != null and beforSkipFlag == "Y"'>
		AND A.PRMT_EDD >= NOW() 
		</if>
	</select>
	
	<!-- 카드프로모션 목록 -->
	<select id="getCardPromotionList" parameterType="CardPromotion" resultType="CardPromotion">
		/* TsaCardPromotionDao.getCardPromotionList */
		SELECT A.*
		FROM (
		    SELECT A.CARD_PRMT_SQ
		         , A.PRMT_NM
		         , DATE_FORMAT(A.PRMT_STD, '%Y-%m-%d') AS PRMT_STD
		         , DATE_FORMAT(A.PRMT_EDD, '%Y-%m-%d') AS PRMT_EDD
		         , A.PRMT_GB
		         , A.DC_GB
		         , A.LINK_URL
		         , A.NOTE
		         , A.DISP_YN
		         , B.MIN_PAY_AMT
		         , B.DC_WAY
		         , B.DC_VAL
		         , (CASE WHEN B.DC_WAY = 'G240_10' THEN CONCAT(B.DC_VAL, '원')
		                 WHEN B.DC_WAY = 'G240_11' THEN CONCAT(B.DC_VAL, '%')
		                 ELSE '' END
		            ) DC_VAL_NM
		         , B.MAX_DC_AMT
		         , B.MIN_NO_ITRT
		         , B.MAX_NO_ITRT
		         , FN_GET_USER_NM(A.REG_NO) AS REG_NM
		         , DATE_FORMAT(A.REG_DT, '%Y%m%d%H%i%S') AS REG_DT
		         , FN_GET_USER_NM(A.UPD_NO) AS UPD_NM
		         , DATE_FORMAT(A.UPD_DT, '%Y%m%d%H%i%S') AS UPD_DT
		         , RANK() OVER(ORDER BY A.CARD_PRMT_SQ DESC)        AS NUMB
		    FROM TB_CARD_PROMOTION A
		    INNER JOIN TB_CARD_PROMOTION_CONDITION B ON A.CARD_PRMT_SQ = B.CARD_PRMT_SQ
		    WHERE A.PRMT_GB = 'A'
		    <if test="condition != null and condition != ''">
		        <choose>
		            <when test='search != null and search == "1"'>
		    AND A.CARD_PRMT_SQ = #{condition}
		            </when>
		            <otherwise >
		    AND UPPER(A.PRMT_NM) LIKE CONCAT('%',UPPER(#{condition}),'%')
		            </otherwise>
		        </choose>
		    </if>
		    <if test="stDate != null and stDate != ''">
		    AND A.PRMT_EDD >= REPLACE(#{stDate},'-','')
		    </if>
		    <if test="edDate != null and edDate != ''">
		    <![CDATA[
		    AND A.PRMT_STD <= REPLACE(#{edDate},'-','')
		    ]]>
		    </if>
		    <if test='beforSkipFlag != null and beforSkipFlag == "Y"'>
		    AND A.PRMT_EDD >= DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
		    </if>
		<include refid="getListPagingCondition_sql"/>
	</select>
</mapper>