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

	<!-- 페이징을 위한 select절 상단 -->
	<sql id="selectForPagingHeader">
		SELECT *
		FROM (
	</sql>

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		) ORIGINAL
		WHERE NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>

	<!-- 상품권 대량 등록 수-->
	<select id="getGiftCardListCount" parameterType="GiftCard" resultType="int">
		/*TsaGiftCard.getGiftCardListCount*/
		SELECT COUNT(*)  AS CNT
		FROM   TB_GFCD_BULK_REG GBR
		LEFT JOIN
		       TB_GIFTCARD_API_HST GAH
		ON     GBR.GFCD_API_HST_SQ = GAH.GFCD_API_HST_SQ
		WHERE  1 = 1
		<if test="custId != null and custId != ''">
		AND    LOWER(GBR.CUST_ID) LIKE CONCAT('%',LOWER(#{custId}),'%')
		</if>
		<if test="stDate != null and edDate != '' and stDate != null and edDate != ''">
		AND    GBR.REG_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		AND    GBR.REG_DT <![CDATA[<]]> DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		</if>
	</select>

	<!--상품권 대량 등록 목록-->
	<select id="getGiftCardList" parameterType="GiftCard" resultType="GiftCard">
		/*TsaGiftCard.getGiftCardList*/
		<include refid="selectForPagingHeader"/>
		SELECT GBR.GFCD_BULK_REG_SQ
		     , GBR.CUST_ID
		     , GBR.GFCD_NO
		     , GBR.RESULT_MSG
		     , FN_GET_USER_NM(GBR.REG_NO)              AS REG_NM
		     , DATE_FORMAT(GBR.REG_DT, '%Y%m%d%H%i%S') AS REG_DT
		     , FN_GET_USER_NM(GBR.UPD_NO)              AS UPD_NM
		     , DATE_FORMAT(GBR.UPD_DT, '%Y%m%d%H%i%S') AS UPD_DT
		     , GAH.CUST_NO
		     , GAH.IP_ADDR
		     , GAH.RESULT_VAL
		     , ROW_NUMBER() OVER(ORDER BY GBR.REG_DT DESC)   AS NUMB
		FROM   TB_GFCD_BULK_REG GBR
		LEFT JOIN
		       TB_GIFTCARD_API_HST GAH
		ON     GBR.GFCD_API_HST_SQ = GAH.GFCD_API_HST_SQ
		WHERE  1 = 1
		<if test="custId != null and custId != ''">
		AND    LOWER(GBR.CUST_ID) LIKE CONCAT('%',LOWER(#{custId}),'%')
		</if>
		<if test="stDate != null and edDate != '' and stDate != null and edDate != ''">
		AND    GBR.REG_DT >= DATE_FORMAT(#{stDate}, '%Y-%m-%d %H:%i:%S')
		AND    GBR.REG_DT <![CDATA[<]]> DATE_FORMAT(DATE_ADD(#{edDate}, INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%S')
		</if>
		<include refid="selectForPagingFooter"/>
	</select>

	<!--상품권 대량 엑셀 정보 등록-->
	<insert id="createGfcdBulkReg" parameterType="GiftCard" keyProperty="gfcdBulkRegSq">
		/*TsaGiftCard.createGfcdBulkReg*/
		INSERT INTO TB_GFCD_BULK_REG (
		       CUST_ID
		     , GFCD_NO
		     , GFCD_API_HST_SQ
		     , RESULT_MSG
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		) VALUES (
		       #{custId}
		     , #{gfcdNo}
		     , IFNULL(#{gfcdApiHstSq}, NULL)
		     , IFNULL(#{resultMsg}, NULL)
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
	</insert>

	<!-- 상품권API 결과 저장-->
	<insert id="createGiftcardApi" parameterType="GiftCard" keyProperty="gfcdApiHstSq">
		/*TsaGiftCard.createGiftcardApi*/
		INSERT INTO TB_GIFTCARD_API_HST (
		       GFCD_NO
		     , IP_ADDR
		     , CUST_NO
		     , RESULT_VAL
		     , REG_NO
		     , REG_DT
		) VALUES (
		       #{gfcdNo}
		     , #{ipAddr}
		     , #{custNo}
		     , #{resultVal}
		     , #{regNo}
		     , NOW()
		)
	</insert>
	
	<!-- 고객 상품권 등록 -->
	<insert id="createGiftcardUseConfirm" parameterType="GiftCard" keyProperty="custGfcdSq">
		/*TsaGiftCard.createGiftcardUseConfirm*/
		INSERT INTO TB_CUST_GIFTCARD (
		       CUST_NO
		     , GFCD_NO
		     , GFCD_NM
		     , CHG_GFCD_AMT
		     , US_GFCD_AMT
		     , RM_GFCD_AMT
		     , USE_ST_DATE
		     , USE_EXP_DATE
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		) VALUES(
		       #{custNo}
		     , #{gfcdNo}
		     , #{gfcdNm}
		     , #{chgGfcdAmt}
		     , #{usGfcdAmt}
		     , #{rmGfcdAmt}
		     , DATE_FORMAT(#{useStDate},'%Y%m%d')
		     , DATE_FORMAT(#{useExpDate},'%Y%m%d')
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
	</insert>

	<!-- 고객 상품권 정보 이력 -->
	<insert id="createCustGiftcardHistory"  parameterType="GiftCard" keyProperty="gfcdHstSq">
		/* TsaGiftCard.createCustGiftcardHistory */
		INSERT INTO TB_CUST_GIFTCARD_HST (
		      CUST_NO
		    , OCCUR_GB
		    , OCCUR_DTL_DESC
		    , GFCD_AMT
		    , CUST_GFCD_SQ
		    , ORD_NO
		    , ORD_DTL_NO
		    , REG_NO
		    , REG_DT
		    , UPD_NO
		    , UPD_DT
		) VALUES (
		      #{custNo}
		    , #{occurGb}
		    , #{occurDtlDesc}
		    , #{gfcdAmt}
		    , #{custGfcdSq}
		    , #{ordNo}
		    , #{ordDtlNo}
		    , #{regNo}
		    , NOW()
		    , #{updNo}
		    , NOW()
		)
	</insert>

	<!--상품권 대량등록 결과-->
	<update id="updateGfcdBulkReg" parameterType="GiftCard">
		UPDATE TB_GFCD_BULK_REG
		SET    GFCD_API_HST_SQ = #{gfcdApiHstSq}
		     , RESULT_MSG = #{resultMsg}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  GFCD_BULK_REG_SQ = #{gfcdBulkRegSq}
	</update>

</mapper>