<?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="kr.co.uplus.ez.api.custMgt.CustMgtMapper">

	<select id="selectSubsListTotalCnt" parameterType="kr.co.uplus.ez.api.custMgt.dto.SubsListReqDto" resultType="int">
		/* custMgt-mapper.xml(selectSubsListTotalCnt) */
		SELECT
			COUNT(*) AS TOTALCNT
		FROM
			hubez_common.EZ_SUBS_INFO esi
		INNER JOIN hubez_common.EZ_SVC_USER esu
				ON
			esu.USER_SEQ = esi.USER_SEQ
			AND esu.USER_TP_CD IN ('01','03')
		INNER JOIN hubez_common.EZ_CUST_INFO eci
				ON
			eci.CUST_SEQ = esi.CUST_SEQ
			AND esi.CUST_SEQ = esu.CUST_SEQ
		LEFT JOIN hubez_imdb.EZ_IM_USER eiu
	      	on eiu.LOGIN_ID = esi.ATTRCTOR_ID
		WHERE 1 = 1
			<include refid="subsListCondition"/>
	</select>

	<select id="selectSubsLists" parameterType="kr.co.uplus.ez.api.custMgt.dto.SubsListReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.SubsList">
      /* custMgt-mapper.xml(selectSubsLists) */
	   SELECT
	        @ROWNUM := @ROWNUM + 1 AS NO
	      , A.SERVICE_ID
	      , A.CUST_NM
	      , A.BIZR_NO
	      , A.REG_NO
	      , A.REG_DT
	      , (
	            SELECT
	               DTL_CD_NM
	            FROM
	               hubez_common.EZ_CD_DTL
	            WHERE
	               GRP_CD = 'SUBS_STTUS_CD'
	               AND DTL_CD = A.SUBS_STTUS_CD
	         ) AS STAT 
	      ,  case when A.EX_PROVUSERTYPE in (  'CCS-CS1'
									, 'CCS-CB1'
									, 'CCS-CB2'
									, 'CCS-CJ'
									, 'CCS-LS2'
									, 'CCS-LS3'
									, 'CCS-M'
									, 'CCS-MJ'
									, 'CCS-P'
									, 'CCS-P2'
									, 'CCS-D'
									, 'CCS-G'
									, 'CCS-S') then '고객센터'
				   when A.EX_PROVUSERTYPE = 'DEALER' then '대리점'
				   when A.EX_PROVUSERTYPE is null then '고객셀프가입'
				   else '직접영업'
				end    as CHANNEL
		  ,CASE WHEN A.NORG_NM IS NULL THEN '-'
				ELSE A.NORG_NM
				END AS NORG_NM
	      , (
	            SELECT
	               PROD_NM
	            FROM
	               hubez_common.EZ_PROD_INFO
	            WHERE
	               PROD_CD = A.PROD_CD
	         ) AS PLAN
	      , A.BIND_DC_YN
	      , IFNULL((SELECT CFWD_AMT FROM hubez_admin.EZ_USER_LMT WHERE USER_SEQ = A.adminSeq AND LMT_YM = DATE_FORMAT(NOW(), '%Y%m')), 0) AS CARRY_OVER  -- 년월까지만 비교 해야 함.
	   FROM
	      (
	      SELECT
	         esu.USER_ID as SERVICE_ID,
	         CASE
			     WHEN esu.USER_TP_CD = '03' THEN esu.USER_NM
			 ELSE
				 eci.CUST_NM
			 END AS CUST_NM,
			 eci.BIZRNO AS BIZR_NO,
	         esi.ENTR_NO as REG_NO,
	         DATE_FORMAT(esi.SUBS_DT, '%Y-%m-%d') AS REG_DT,
	         esi.SUBS_STTUS_CD ,
	         esi.PROD_CD,
	         esi.USER_SEQ as adminSeq,
	         esi.ATTRCTOR_ID as attrctorId,
	         esi.BIND_DC_YN,
	         eiu.EX_PROVUSERTYPE,
	         eig.NORG_NM
	      FROM
	         hubez_common.EZ_SUBS_INFO esi
	      INNER JOIN hubez_common.EZ_SVC_USER esu
	         ON    esu.USER_SEQ = esi.USER_SEQ
	            and esu.USER_TP_CD in ('01','03')
	      INNER JOIN hubez_common.EZ_CUST_INFO eci
	         ON  eci.CUST_SEQ = esi.CUST_SEQ
	         AND esi.CUST_SEQ = esu.CUST_SEQ
		  LEFT OUTER JOIN hubez_imdb.EZ_IM_USER eiu
	      	ON eiu.LOGIN_ID = esi.ATTRCTOR_ID
	      LEFT OUTER JOIN hubez_imdb.EZ_IM_GROUP eig
			ON eiu.EX_DEPT_CD = eig.NORG_CD	
	      WHERE 1 = 1
	         <include refid="subsListCondition"/>
	      ORDER BY esi.SUBS_DT DESC
	   LIMIT #{page}, #{pagePerRows}) A,  ( SELECT @ROWNUM := #{page} ) AS R
	</select>

	<select id="selectSubsListsExcel" parameterType="kr.co.uplus.ez.api.custMgt.dto.SubsListReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.SubsList">
		/* custMgt-mapper.xml(selectSubsListsExcel) */
		SELECT
	        @ROWNUM := @ROWNUM + 1 AS NO
	      , A.SERVICE_ID
	      , A.CUST_NM
	      , A.BIZR_NO
	      , A.REG_NO
	      , A.REG_DT
	      , (
	            SELECT
	               DTL_CD_NM
	            FROM
	               hubez_common.EZ_CD_DTL
	            WHERE
	               GRP_CD = 'SUBS_STTUS_CD'
	               AND DTL_CD = A.SUBS_STTUS_CD
	         ) AS STAT 
	      , ifnull((
	         select case when EX_PROVUSERTYPE in (  'CCS-CS1'   
	                                    , 'CCS-CB1'   
	                                    , 'CCS-CB2'   
	                                    , 'CCS-CJ'   
	                                    , 'CCS-LS2'   
	                                    , 'CCS-LS3'   
	                                    , 'CCS-M'   
	                                    , 'CCS-MJ'   
	                                    , 'CCS-P'   
	                                    , 'CCS-P2'   
	                                    , 'CCS-D'   
	                                    , 'CCS-G'   
	                                    , 'CCS-S') then '고객센터' 
	                   when EX_PROVUSERTYPE = 'DEALER' then '대리점'
	               else '직접영업'
	            end as ATTRACT_CHANNEL
	         from hubez_imdb.EZ_IM_USER 
	         where LOGIN_ID = A.attrctorId
	         ) , '고객셀프가입') as CHANNEL
	      ,CASE WHEN A.NORG_NM IS NULL THEN '-'
				ELSE A.NORG_NM
				END AS NORG_NM
	      , (
	            SELECT
	               PROD_NM
	            FROM
	               hubez_common.EZ_PROD_INFO
	            WHERE
	               PROD_CD = A.PROD_CD
	         ) AS PLAN 
	      , A.BIND_DC_YN
	      , IFNULL((SELECT CFWD_AMT FROM hubez_admin.EZ_USER_LMT WHERE USER_SEQ = A.adminSeq AND LMT_YM = DATE_FORMAT(NOW(), '%Y%m')), 0) AS CARRY_OVER  -- 년월까지만 비교 해야 함.
	   FROM
	      (
	      SELECT
	         esu.USER_ID as SERVICE_ID,
	         CASE
			     WHEN esu.USER_TP_CD = '03' THEN esu.USER_NM
			 ELSE
				 eci.CUST_NM
			 END AS CUST_NM,
			 eci.BIZRNO AS BIZR_NO,
	         esi.ENTR_NO as REG_NO,
	         DATE_FORMAT(esi.SUBS_DT, '%Y-%m-%d') AS REG_DT,
	         esi.SUBS_STTUS_CD ,
	         esi.PROD_CD,
	         esi.USER_SEQ as adminSeq,
	         esi.BIND_DC_YN,
	         esi.ATTRCTOR_ID as attrctorId,
	         eig.NORG_NM
	      FROM
	         hubez_common.EZ_SUBS_INFO esi
	      INNER JOIN hubez_common.EZ_SVC_USER esu
	         ON    esu.USER_SEQ = esi.USER_SEQ
	            and esu.USER_TP_CD in ('01','03')
	      INNER JOIN hubez_common.EZ_CUST_INFO eci
	         ON
	         eci.CUST_SEQ = esi.CUST_SEQ
	         AND esi.CUST_SEQ = esu.CUST_SEQ
	      LEFT OUTER JOIN hubez_imdb.EZ_IM_USER eiu
	      	ON eiu.LOGIN_ID = esi.ATTRCTOR_ID
	      LEFT OUTER JOIN hubez_imdb.EZ_IM_GROUP eig
			ON eiu.EX_DEPT_CD = eig.NORG_CD	
	      WHERE 1 = 1
	         <include refid="subsListCondition"/>
	      ORDER BY esi.SUBS_DT DESC ) A,  ( SELECT @ROWNUM := 0 ) AS R
	</select>
	
	<sql id="subsListCondition">
		<if test="startDt != null and startDt != ''">
			AND esi.SUBS_DT <![CDATA[ >= ]]> STR_TO_DATE(concat(REPLACE(#{startDt}, '-', '' ),'000000') , '%Y%m%d%H%i%s')
		</if>
		<if test="endDt != null and endDt != ''">
			AND esi.SUBS_DT <![CDATA[ <= ]]> STR_TO_DATE(concat(REPLACE(#{endDt}, '-', '' ),'235959'), '%Y%m%d%H%i%s')
		</if>
		<if test="searchType1 != null and searchType1 != ''">
			AND esi.SUBS_STTUS_CD = #{searchType1}
		</if>
		<if test="searchType2 != null and searchType2 != ''">
			<if test="searchType2 == '01'">
				AND eiu.EX_PROVUSERTYPE is null
			</if>
			<if test="searchType2 == '02' ">
				AND eiu.EX_PROVUSERTYPE = 'DEALER'
			</if>
			<if test="searchType2 == '03' ">
				AND eiu.EX_PROVUSERTYPE in (  'CCS-CS1'
		                                    , 'CCS-CB1'
		                                    , 'CCS-CB2'
		                                    , 'CCS-CJ'
		                                    , 'CCS-LS2'
		                                    , 'CCS-LS3'
		                                    , 'CCS-M'
		                                    , 'CCS-MJ'
		                                    , 'CCS-P'
		                                    , 'CCS-P2'
		                                    , 'CCS-D'
		                                    , 'CCS-G'
		                                    , 'CCS-S')
			</if>
			<if test="searchType2 == '04' ">
				AND eiu.EX_PROVUSERTYPE not in (  'CCS-CS1'
		                                    , 'CCS-CB1'
		                                    , 'CCS-CB2'
		                                    , 'CCS-CJ'
		                                    , 'CCS-LS2'
		                                    , 'CCS-LS3'
		                                    , 'CCS-M'
		                                    , 'CCS-MJ'
		                                    , 'CCS-P'
		                                    , 'CCS-P2'
		                                    , 'CCS-D'
		                                    , 'CCS-G'
		                                    , 'CCS-S', 'DEALER')
			</if>
		</if>
		<if test="searchType3 != null and searchType3 != ''">
			<if test="searchType3 == '01' and searchText1 != null and searchText1 != ''">
				AND  UPPER(eci.CUST_NM) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
			</if>
			<if test="searchType3 == '02' and searchText1 != null and searchText1 != ''">
				AND  UPPER(esi.ENTR_NO) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
			</if>
			<if test="searchType3 == '03' and searchText1 != null and searchText1 != ''">
				AND  UPPER(esu.USER_ID) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
			</if>
			<if test="searchType3 == '04' and searchText1 != null and searchText1 != ''">
				AND  UPPER(eci.BIZRNO) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
			</if>
		</if>
	</sql>

	<insert id="insertSvcUser" parameterType="kr.co.uplus.ez.api.custMgt.dto.InsertTestIdReqDto">
		/* custMgt-mapper.xml(insertSvcUser) */
		INSERT
			INTO
			hubez_common.EZ_SVC_USER(
			USER_SEQ,
			USER_ID,
			USER_NM,
			USER_STTUS_CD,
			PRNTS_USER_SEQ,
			CUST_SEQ,
			USER_TP_CD,
			PWD,
			BIZR_AUTH_YN,
			LINE_TP_CD,
			HP_NO,
			EMAIL,
			AUTHCD_080,
			AUT_CD,
			LOGIN_FAIL_CNT,
			REG_ID,
			REG_DT,
			CHG_ID,
			CHG_DT
		)VALUES(
			hubez_admin.FUN_GET_USER_SEQ(),
			#{userId},
			#{userNm},
			#{userStat},
			#{prntsUserSeq},
			hubez_admin.FUN_GET_CUST_SEQ(),
			#{userTpCd},
			#{userPw},
			#{bizrAuthYn},
			#{lineTpCd},
			#{hpNo},
			#{email},
			#{authcd080},
			#{autCd},
			#{loginFailCnt},
			#{regId},
			NOW(),
			#{chgId},
			NOW())
	</insert>

	<insert id="insertUserInfo" parameterType="kr.co.uplus.ez.api.custMgt.dto.InsertUserReqDto">
		/* custMgt-mapper.xml(insertUserInfo) */
		INSERT INTO
			hubez_common.EZ_SVC_USER(
			USER_SEQ,
			USER_ID,
			USER_NM,
			USER_STTUS_CD,
			PRNTS_USER_SEQ,
			CUST_SEQ,
			USER_TP_CD,
			PWD,
			LINE_TP_CD,
			HP_NO,
			EMAIL,
			AUT_CD,
			LOGIN_FAIL_CNT,
			REG_ID,
			REG_DT,
			CHG_ID,
			CHG_DT
		) SELECT
		    hubez_admin.FUN_GET_USER_SEQ(),
			#{userId},
			#{userNm},
			#{userStat},
			esu.USER_SEQ,
			esu.CUST_SEQ,
			'02', -- 사용자ID
			#{userPw},
			(SELECT LINE_TP_CD FROM hubez_common.EZ_SVC_USER WHERE USER_SEQ = esu.USER_SEQ),
			#{mdn},
			#{userEmail},
			'H002',
			0,
			#{userId},
			NOW(),
			#{userId},
			NOW()
		FROM
			hubez_common.EZ_SVC_USER esu
		WHERE esu.USER_ID = #{adminId}
	</insert>

	<select id="selectUserInfoCnt" parameterType="kr.co.uplus.ez.api.custMgt.dto.InsertUserReqDto" resultType="int">
		/* custMgt-mapper.xml(selectUserInfoCnt) */
		SELECT
		    COUNT(*)
		FROM
			hubez_common.EZ_SVC_USER
		WHERE USER_ID = #{userId}
	</select>

	<select id="selectSubsDetailInfo" parameterType="kr.co.uplus.ez.api.custMgt.dto.SubsDetailReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.SubsDetail">
      /* custMgt-mapper.xml(selectSubsDetailInfo) */
      SELECT
         esi.SUBS_ID ,
         -- 기본정보
         eci.CUST_NM as CUST_NM ,
         eci.REPR_NM,
         (
         SELECT
            ifnull(DTL_CD_NM, '-')
         FROM
            hubez_common.EZ_CD_DTL
         WHERE
            GRP_CD = 'CUST_KD_CD'
            AND DTL_CD = eci.CUST_TY_CD) AS CUST_TYPE,
         eci.ZIPCD AS ADR1,
         eci.ADDR1 AS ADR2,
         eci.ADDR2 AS ADR3,
         eci.BIZRNO AS B_REG_NO,
         eci.CORPNO AS CPR_REG_NO,
         -- 사용정보
         ifnull(DATE_FORMAT(esi.OPN_DT, '%Y-%m-%d'), DATE_FORMAT(esi.SUBS_DT , '%Y-%m-%d')) AS SUBS_DT,
         esi.PROD_CD,
         (
         SELECT
            DTL_CD_NM
         FROM
            hubez_common.EZ_CD_DTL
         WHERE
            GRP_CD = 'SUBS_STTUS_CD'
            AND DTL_CD = esi.SUBS_STTUS_CD) AS STAT,
         (
         SELECT
            PROD_NM
         FROM
            hubez_common.EZ_PROD_INFO
         WHERE
            PROD_CD = esi.PROD_CD) AS PLAN,
         esi.ENTR_NO as SUBS_NO,
         esi.ATTRCTOR_ID AS CHANNEL_ID,
         esi.ATTRCTOR_NM AS CHANNEL_NM,
         esi.SUBSMNGR_ID AS ADMIN_ID,
         esi.SUBSMNGR_NM AS ADMIN_NM,
         esi.BIND_DC_YN,
         -- 사용자 데이터
         esu.USER_ID as SERVICE_ID,
         esu.USER_SEQ,
         esu.HP_NO as mdn,
         esu.EMAIL,
         (
         SELECT
            DTL_CD_NM
         FROM
            hubez_common.EZ_CD_DTL
         WHERE
            GRP_CD = 'SVCUSER_TP_CD'
            AND DTL_CD = esu.USER_TP_CD) AS USE_AUTH,
         esu.USER_NM,
         esu.HP_NO,
         IFNULL((SELECT CFWD_AMT + CFWD_RSTRT_BLNC FROM hubez_admin.EZ_USER_LMT WHERE USER_SEQ = esi.USER_SEQ AND LMT_YM = DATE_FORMAT(NOW(), '%Y%m')), 0) AS CARRY_OVER,
         (SELECT COUNT(*) FROM hubez_common.EZ_SVC_USER WHERE PRNTS_USER_SEQ = esu.USER_SEQ and USER_TP_CD = '02' ) AS USER_CNT
      FROM
         hubez_common.EZ_SUBS_INFO esi
      INNER JOIN hubez_common.EZ_SVC_USER esu
         ON
         esu.USER_SEQ = esi.USER_SEQ
      INNER JOIN hubez_common.EZ_CUST_INFO eci
         ON
         eci.CUST_SEQ = esi.CUST_SEQ
         AND esi.CUST_SEQ = esu.CUST_SEQ
      WHERE 1 = 1
      AND esu.USER_TP_CD in( '01','03')
      AND esu.USER_ID = #{serviceId}
   </select>

	<update id="updateAdminInfo" parameterType="kr.co.uplus.ez.api.custMgt.dto.SubsDetail">
		/* custMgt-mapper.xml(updateAdminInfo) */
		UPDATE hubez_common.EZ_SUBS_INFO
        SET
           CHG_ID = #{serviceId}
           ,CHG_DT = NOW()
           <if test="adminId != null and adminId != ''">
           ,SUBSMNGR_ID= #{adminId}
           </if>
           <if test="adminNm != null and adminNm != ''">
           ,SUBSMNGR_NM= #{adminNm}
           </if>
        WHERE USER_SEQ = (SELECT USER_SEQ  FROM (SELECT esi.USER_SEQ
                    FROM hubez_common.EZ_SUBS_INFO esi
                    INNER JOIN hubez_common.EZ_SVC_USER esu
                    ON esu.USER_SEQ = esi.USER_SEQ
        			WHERE esu.USER_TP_CD = '01'
                    AND esu.USER_ID = #{serviceId}) as sub1)
	</update>

	<update id="updateUserInfo" parameterType="kr.co.uplus.ez.api.custMgt.dto.UpdateUserReqDto">
		/* custMgt-mapper.xml(updateUserInfo) */
		UPDATE hubez_common.EZ_SVC_USER SET
           CHG_ID = #{userId}
           ,CHG_DT = NOW()
           <if test="userNm != null and userNm != ''">
           ,USER_NM = #{userNm}
           </if>
           <if test="userStat != null and userStat != ''">
           ,USER_STTUS_CD = #{userStat}
           </if>
           <if test="userPw != null and userPw != ''">
		   ,PWD = #{userPw}
		   </if>
		   <if test="mdn != null and mdn != ''">
		   ,HP_NO = #{mdn}
		   </if>
		   <if test="userEmail != null and userEmail != ''">
		   ,EMAIL = #{userEmail}
		   </if>
		   <if test="lineType != null and lineType != ''">
		   ,LINE_TP_CD 		= #{lineType}
		   </if>
        WHERE 
        	USER_ID = #{userId}
	</update>

	<select id="selectCarryOverList" parameterType="kr.co.uplus.ez.api.custMgt.dto.CarryOverListReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.CarryOver">
       /* custMgt-mapper.xml(selectCarryOverList) */
      SELECT DATE_FORMAT(STR_TO_DATE(eul.LMT_YM ,'%Y%m'),'%Y-%m') AS LMT_YM
		, esu1.USER_ID AS USER_ID
		, (FX_LMT_AMT + CFWD_AMT)  AS START_AMOUNT
		, (SMS_USE_AMT + LMS_USE_AMT + MMS_USE_AMT + ATLK_USE_AMT + RCS_USE_AMT ) AS USE_AMOUNT
		, CFWD_AMT  AS KRRR_AMOUNT
		, EXTNC_AMT AS EXTSH_AMOUNT
      FROM hubez_admin.EZ_USER_LMT eul
         JOIN hubez_common.EZ_SVC_USER esu1 ON eul.USER_SEQ = esu1.USER_SEQ
      WHERE esu1.USER_ID =#{serviceId} and eul.LMT_YM = DATE_FORMAT(now(), '%Y%m')
      UNION ALL
      (SELECT  DATE_FORMAT(eud.USE_YM, '%Y-%m') AS LMT_YM
            ,esu.USER_ID
            ,ROUND(IFNULL(eud.FX_LMT_AMT,0) + IFNULL(eud.CFWD_AMT,0)) AS START_AMOUNT
            ,ROUND(IFNULL(eud.FX_USE_AMT,0) + IFNULL(eud.CFWD_USE_AMT,0) + IFNULL(eud.MRT_USE_AMT,0)) AS USE_AMOUNT
            ,ROUND(IFNULL(eud.CFWD_AMT,0)) AS KRRR_AMOUNT
            ,ROUND(IFNULL(eud.EXTNC_AMT,0)) AS EXTSH_AMOUNT
         FROM hubez_admin.EZ_UTXNCOLEC_DTL eud
            join hubez_admin.EZ_USETXN_COLEC euc on euc.USE_YM = eud.USE_YM and euc.COLEC_TMS = eud.COLEC_TMS and euc.LAST_YN = 'Y'
            JOIN hubez_common.EZ_SUBS_INFO esi ON eud.SUBS_ID = esi.SUBS_ID
            JOIN hubez_common.EZ_SVC_USER esu ON esi.USER_SEQ = esu.USER_SEQ
      <![CDATA[
      AND eud.USE_YM >= DATE_ADD(NOW(), INTERVAL -#{limitCnt} MONTH)
      AND eud.USE_YM < DATE_FORMAT(NOW(),'%Y-%m-01') 
      ]]>
      AND esu.USER_ID = #{serviceId}
      )  ORDER BY LMT_YM DESC
	</select>

	<select id="selectImUser" parameterType="kr.co.uplus.ez.common.data.ImUser" resultType="kr.co.uplus.ez.common.data.ImUser">
		/* custMgt-mapper.xml(selectImUser) */
		SELECT
			LOGIN_ID
			, NAME
			, USER_CODE
			, STATUS
			, EMAIL
			, MOBILE
			, EX_EMP_DV_CD
			, EX_PROVUSERTYPE
			, EX_HOFC_STTS_CD
			, EX_DEPT_CD
			, EX_DEPT_LEVEL
		FROM hubez_imdb.EZ_IM_USER
		WHERE STATUS = 1
		AND LOGIN_ID = #{loginId}
	</select>

	<select id="selectSvcUserListTotalCnt" parameterType="kr.co.uplus.ez.api.custMgt.dto.MemberListReqDto" resultType="int">
		/* custMgt-mapper.xml(selectSvcUserListTotalCnt) */
		SELECT
			COUNT(*)
		FROM hubez_common.EZ_SVC_USER esu
		WHERE 1 = 1
		<include refid="svcUserListCondition"></include>
	</select>

	<select id="selectSvcUserList" parameterType="kr.co.uplus.ez.api.custMgt.dto.MemberListReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.MemberList">
		/* custMgt-mapper.xml(selectSvcUserList) */
		SELECT
			@ROWNUM := @ROWNUM + 1 AS NO,
			A.*
		FROM
		(
			SELECT
				USER_NM
				,(SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'SVCUSER_TP_CD' AND DTL_CD = esu.USER_TP_CD) AS USER_TYPE
				,(SELECT USER_ID FROM hubez_common.EZ_SVC_USER WHERE USER_SEQ = esu.PRNTS_USER_SEQ) AS ADMIN_ID
				,USER_ID
				,DATE_FORMAT(esu.REG_DT, '%Y-%m-%d') AS REG_DT
				,(SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'SVCUSER_STTUS_CD' AND DTL_CD = esu.USER_STTUS_CD) AS USER_STAT
			FROM hubez_common.EZ_SVC_USER esu
			WHERE 1 = 1
			<include refid="svcUserListCondition"></include>
			ORDER BY
				esu.REG_DT DESC
		LIMIT #{page}, #{pagePerRows}) A,
		( SELECT @ROWNUM := #{page} ) AS R
	</select>

	<sql id="svcUserListCondition">
		<if test="startDt != null and startDt != ''">
			AND esu.REG_DT <![CDATA[ >= ]]> STR_TO_DATE(concat(REPLACE(#{startDt}, '-', '' ),'000000') , '%Y%m%d%H%i%s')
		</if>
		<if test="endDt != null and endDt != ''">
			AND esu.REG_DT <![CDATA[ <= ]]> STR_TO_DATE(concat(REPLACE(#{endDt}, '-', '' ),'235959'), '%Y%m%d%H%i%s')
		</if>
		<if test="searchType1 != null and searchType1 != ''">
			AND esu.USER_STTUS_CD = #{searchType1}
		</if>
		<if test="searchType2 != null and searchType2 != ''">
			AND esu.USER_TP_CD = #{searchType2}
		</if>
		<if test="searchType3 != null and searchType3 != ''">
			<if test="searchType3 == '01' and searchText1 != null and searchText1 != ''">
				AND  UPPER(esu.USER_ID) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
			</if>
			<if test="searchType3 == '02' and searchText1 != null and searchText1 != ''">
				AND  UPPER(esu.USER_NM) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
			</if>
			<if test="searchType3 == '03' and searchText1 != null and searchText1 != ''">
				AND esu.USER_SEQ IN (SELECT PRNTS_USER_SEQ FROM hubez_common.EZ_SVC_USER WHERE USER_ID LIKE CONCAT('%', #{searchText1}, '%') AND USER_TP_CD ='01')
			</if>
		</if>
	</sql>

	<select id="selectMemberDetail" parameterType="kr.co.uplus.ez.api.custMgt.dto.MemberDetailReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.MemberDetailRes">
		/* custMgt-mapper.xml(selectMemberDetail) */
		SELECT
			USER_NM
			,DATE_FORMAT(esu.REG_DT, '%Y-%m-%d') AS REG_DT
			,(SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD='SVCUSER_TP_CD' AND esu.USER_TP_CD = DTL_CD) AS USER_TYPE
			,USER_ID
			,(SELECT USER_ID FROM hubez_common.EZ_SVC_USER where USER_SEQ = esu.PRNTS_USER_SEQ LIMIT 1) AS ADMIN_ID
			,esu.USER_STTUS_CD AS USER_STAT
			,DATE_FORMAT(esu.LAST_LOGIN_DT, '%Y-%m-%d %H:%i:%s') AS LAST_LOGIN_DT
			,esu.HP_NO AS MDN
			,esu.EMAIL
			,LINE_TP_CD AS LINE_TYPE
		FROM hubez_common.EZ_SVC_USER esu
			WHERE esu.USER_ID = #{userId}
	</select>

	<select id="selectMemberAdminDetail" parameterType="kr.co.uplus.ez.api.custMgt.dto.MemberAdminDetailReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.MemberAdminDetailRes">
		/* custMgt-mapper.xml(selectMemberAdminDetail) */
		SELECT
			USER_NM
			,DATE_FORMAT(esu.REG_DT, '%Y-%m-%d') AS REG_DT
			,(SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'SVCUSER_TP_CD' AND DTL_CD = esu.USER_TP_CD LIMIT 1) AS USER_TYPE
			,USER_ID
			,(SELECT USER_ID FROM hubez_common.EZ_SVC_USER where USER_SEQ = esu.PRNTS_USER_SEQ LIMIT 1) AS ADMIN_ID
			,(SELECT USER_NM FROM hubez_common.EZ_SVC_USER where USER_SEQ = esu.PRNTS_USER_SEQ LIMIT 1) AS ADMIN_NM
			,IFNULL((SELECT ROUND(SUM(IFNULL(FX_LMT_AMT,0) + IFNULL(MRT_LMT_AMT,0))) FROM hubez_admin.EZ_USER_LMT WHERE USER_SEQ = esu.PRNTS_USER_SEQ AND LMT_YM = DATE_FORMAT(NOW(),'%Y%m')),0) AS SENDING_LIMIT
			,esu.LINE_TP_CD AS LINE_TYPE
			,esu.USER_STTUS_CD AS USER_STAT
			,DATE_FORMAT(esu.LAST_LOGIN_DT, '%Y-%m-%d %H:%i:%s') AS LAST_LOGIN_DT
			,(SELECT MEMO FROM hubez_common.EZ_USER_MEMO EUM WHERE USER_SEQ = esu.USER_SEQ ORDER BY REG_DT DESC LIMIT 1) AS MEMO
			,esu.HP_NO AS MDN
			,esu.EMAIL
		FROM hubez_common.EZ_SUBS_INFO esi
		INNER JOIN hubez_common.EZ_SVC_USER esu
			ON esu.USER_SEQ = esi.USER_SEQ
			AND esu.USER_ID = #{userId}
	</select>

	<select id="selectMemberDetailListTotalCnt" parameterType="kr.co.uplus.ez.api.custMgt.dto.MemberDetailReqDto" resultType="int">
		/* custMgt-mapper.xml(selectMemberDetailListTotalCnt) */
		SELECT
			COUNT(*)
		FROM hubez_common.EZ_SVC_USER esu
		WHERE esu.PRNTS_USER_SEQ = (SELECT USER_SEQ FROM hubez_common.EZ_SVC_USER WHERE USER_ID = #{userId} LIMIT 1)
		AND esu.USER_SEQ <![CDATA[ <> ]]>esu.PRNTS_USER_SEQ
	</select>

	<select id="selectMemberDetailList" parameterType="kr.co.uplus.ez.api.custMgt.dto.MemberDetailReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.MemberDetail">
		/* custMgt-mapper.xml(selectMemberDetailList) */
		<!--
		SELECT
			@ROWNUM := @ROWNUM + 1 AS NO,
			A.*
		FROM
		(
			SELECT
				USER_ID AS userId
				,USER_NM AS userNm
				,HP_NO AS mdn
				,(SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'SVCUSER_STTUS_CD' AND DTL_CD = esu.USER_STTUS_CD) AS userStat
			FROM hubez_common.EZ_SVC_USER esu
			WHERE esu.PRNTS_USER_SEQ = (SELECT USER_SEQ FROM hubez_common.EZ_SVC_USER WHERE USER_ID = #{userId})
			ORDER BY esu.REG_DT DESC
			LIMIT #{page}, #{pagePerRows}) A,
		( SELECT @ROWNUM := #{page} ) AS R
		 -->
		SELECT
			@ROWNUM := @ROWNUM + 1 AS NO,
			A.*
		FROM
		(
			SELECT
				USER_ID AS userId
				,USER_NM AS userNm
				,HP_NO AS mdn
				,(SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'SVCUSER_STTUS_CD' AND DTL_CD = esu.USER_STTUS_CD) AS userStat
			FROM hubez_common.EZ_SVC_USER esu
			WHERE esu.PRNTS_USER_SEQ = (SELECT USER_SEQ FROM hubez_common.EZ_SVC_USER WHERE USER_ID = #{userId} LIMIT 1)
			AND esu.USER_SEQ <![CDATA[ <> ]]>esu.PRNTS_USER_SEQ
			ORDER BY esu.REG_DT DESC) A,
		( SELECT @ROWNUM := 0 ) AS R
	</select>

	<select id="selectMemoListTotalCnt" parameterType="kr.co.uplus.ez.api.custMgt.dto.AllMemoListReqDto" resultType="int">
		/* custMgt-mapper.xml(selectMemoList) */
		SELECT
			COUNT(*)
		FROM
			hubez_common.EZ_USER_MEMO EUM
			INNER JOIN hubez_common.EZ_SVC_USER ESU
			ON EUM.USER_SEQ = ESU.USER_SEQ
		WHERE
			ESU.USER_ID = #{userId}
	</select>

	<select id="selectMemoList" parameterType="kr.co.uplus.ez.api.custMgt.dto.AllMemoListReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.AllMemoList">
		/* custMgt-mapper.xml(selectMemoList) */
		SELECT
			EUM.MEMO AS memo,
			EUM.SEQ_NO AS seqNo,
			EUM.MKER_NM AS register,
			DATE_FORMAT(EUM.REG_DT, '%Y-%m-%d') AS regDt
		FROM
			hubez_common.EZ_USER_MEMO EUM
			INNER JOIN hubez_common.EZ_SVC_USER ESU
			ON EUM.USER_SEQ = ESU.USER_SEQ
		WHERE
			ESU.USER_ID = #{userId}
		ORDER BY EUM.REG_DT DESC
		LIMIT #{page}, #{pagePerRows}
	</select>

	<select id="selectCustInfoCount" parameterType="String" resultType="int">
		/* custMgt-mapper.xml(selectCustInfo) */
		SELECT
			COUNT(*)
		FROM
			hubez_common.EZ_CUST_INFO ECI
		WHERE
			BIZRNO = #{bizrno}
	</select>

	<!-- 이용자 아이디 유무 확인. -->
	<select id="getUserIdCount" parameterType="String" resultType="int">
		/* custMgt-mapper.xml(getUserIdCount) */
		SELECT
			COUNT(*)
		FROM
			hubez_common.EZ_SVC_USER
		WHERE
			USER_ID = #{userId}
	</select>

	<!-- 고객 일련번호 조회 -->
	<select id="getCustSeq" parameterType="String" resultType="String">
		/* custMgt-mapper.xml(getCustSeq) */
		SELECT IFNULL((SELECT CUST_SEQ FROM hubez_common.EZ_CUST_INFO ECI WHERE ECI.BIZRNO = #{bizrno}), hubez_admin.FUN_GET_CUST_SEQ()) AS CUST_SEQ
	</select>

	<!-- 이용자 일련번호 조회(채번) -->
	<select id="getUserSeq" resultType="String">
		/* custMgt-mapper.xml(getUserSeq) */
		SELECT hubez_admin.FUN_GET_USER_SEQ()
	</select>

	<!-- 080인증번호 채번 -->
	<select id="getAuth080" resultType="String">
		/* custMgt-mapper.xml(getAuth080) */
		SELECT hubez_admin.FUN_GET_AUTH080()
	</select>

	<insert id="insertCustInfo" parameterType="kr.co.uplus.ez.api.custMgt.dto.CustInfo">
		/* custMgt-mapper.xml(insertCustInfo) */
		INSERT
			INTO
			hubez_common.EZ_CUST_INFO(
			CUST_SEQ,
			BIZRNO,
			CUST_NM,
			CUST_TY_CD,
			CORPNO,
			REPR_NM,
			BRTHDY,
			ZIPCD,
			ADDR1,
			ADDR2,
			REG_ID,
			REG_DT,
			CHG_ID,
			CHG_DT
		)VALUES(
		    #{custSeq},
			#{bizrno},
			#{custNm},
			#{custTyCd},
			#{corpno},
			#{reprNm},
			#{brthdy},
			#{zipcd},
			#{addr1},
			#{addr2},
			#{regId},
			NOW(),
			#{regId},
			NOW()
		)
	</insert>

	<!-- 서비스 이용자 정보 저장 -->
	<insert id="insertSvcUserInfo" parameterType="kr.co.uplus.ez.api.custMgt.dto.SvcUserInfo">
		/* custMgt-mapper.xml(insertSvcUserInfo) */
		INSERT INTO hubez_common.EZ_SVC_USER(
			USER_SEQ
			,USER_ID
			,USER_NM
			,USER_STTUS_CD
			,PRNTS_USER_SEQ
			,CUST_SEQ
			,USER_TP_CD
			,PWD
			,BIZR_AUTH_YN
			,LINE_TP_CD
			,HP_NO
			,EMAIL
			,AUTHCD_080
			,AUT_CD
			,LOGIN_FAIL_CNT
			,REG_ID
			,REG_DT
			,CHG_ID
			,CHG_DT
		)VALUES(
			#{userSeq}
        	, #{userId}
        	, #{userNm}
        	, #{userSttusCd}
        	, #{prntsUserSeq}
        	, #{custSeq}
        	, #{userTpCd}
        	, sha2(concat(#{userId}, #{pwd}), 512)
        	, #{bizrAuthYn}
        	, #{lineTpCd}
        	, #{hpNo}
        	, #{email}
        	, #{authcd080}
        	, #{autCd}
        	, 0
        	, #{regId}
        	, now()
        	, #{regId}
        	, now()
        )
	</insert>

	<!-- 청약 정보 저장 -->
	<insert id="insertSubsInfo" parameterType="kr.co.uplus.ez.api.custMgt.dto.SubsInfo">
		/* custMgt-mapper.xml(insertSubsInfo) */
		INSERT INTO hubez_common.EZ_SUBS_INFO(
			SUBS_ID
			,ENTR_NO
			,USER_SEQ
			,CUST_SEQ
			,SUBS_STTUS_CD
			,PROD_CD
			,SUBS_DT
			,OPN_DT
			,BIND_DC_YN
			,REG_ID
			,REG_DT
			,CHG_ID
			,CHG_DT
		)values(
      		#{subsId}
      		,#{entrNo}
      		, #{userSeq}
      		, #{custSeq}
      		, #{subsSttusCd}
      		, #{prodCd}
      		, NOW()
      		, NOW()
      		, #{bindDcYn}
      		, #{regId}
      		, NOW()
      		, #{regId}
      		, NOW()
      	)
	</insert>
	<select id="selectUserSeq" parameterType="String" resultType="int">
	/* custMgt-mapper.xml(selectUserSeq) */
	SELECT USER_SEQ
	FROM hubez_common.EZ_SVC_USER
	WHERE USER_ID = #{userId}
	</select>

	<delete id="deleteMemo" parameterType="Map">
	/* custMgt-mapper.xml(deleteMemo) */
	DELETE FROM hubez_common.EZ_USER_MEMO
	WHERE SEQ_NO = #{seqNo}
	AND USER_SEQ = #{userSeq}
	</delete>

	<delete id="deleteUserList" parameterType="kr.co.uplus.ez.api.custMgt.dto.DeleteUserReqDto" >
	/* custMgt-mapper.xml(deleteUserList) */
	DELETE FROM hubez_common.EZ_SVC_USER
		WHERE 1=1
		AND	USER_ID IN
				<foreach collection ="list" item="item" index="i" open="("
				separator="," close=")">
				#{item.userId}
				</foreach>

		AND USER_TP_CD = '02'
		</delete>

	<select id="selectAdminId" parameterType="String" resultType="String">
	/* custMgt-mapper.xml(selectAdminId) */
		SELECT USER_ID from hubez_common.EZ_SVC_USER
		WHERE USER_ID = #{adminId}
	</select>

	<update id="updateAdminInfoTotal" parameterType="kr.co.uplus.ez.api.custMgt.dto.UpdateAdminInfoTotalReqDto">
	/* custMgt-mapper.xml(updateAdminInfoTotal) */
		UPDATE hubez_common.EZ_SVC_USER
        SET
           CHG_ID = #{userId}
           ,CHG_DT = NOW()
           <if test="userStat != null and userStat != ''">
           ,USER_STTUS_CD = #{userStat}
           </if>
		   <if test="lineType != null and lineType != ''">
		   ,LINE_TP_CD = #{lineType}
		   </if>
        WHERE USER_ID = #{userId}
	</update>
	
	<update id="updateMemberInfo" parameterType="kr.co.uplus.ez.api.custMgt.dto.UpdateAdminInfoTotalReqDto">
		/* custMgt-mapper.xml(updateMemberInfo) */
		UPDATE hubez_common.EZ_SVC_USER SET
			USER_NM			= #{userNm},
			HP_NO			= #{hpNo},
			EMAIL			= #{email},
			USER_STTUS_CD 	= #{userStat},
		   	LINE_TP_CD 		= #{lineType},
		   	CHG_ID 			= #{userId},
		   	CHG_DT 			= NOW()
        WHERE
        	USER_ID = #{userId}
	</update>

	<insert id="insertMemo" parameterType="kr.co.uplus.ez.api.custMgt.dto.UpdateAdminInfoTotalReqDto">
		/* custMgt-mapper.xml(insertMemo) */
		INSERT INTO hubez_common.EZ_USER_MEMO(
			USER_SEQ
			,MEMO
			,MKER_ID
			,MKER_NM
			,REG_ID
			,REG_DT
		) SELECT
			esu.USER_SEQ
      		, #{memo}
      		, #{userId}
      		, esu.USER_NM
      		, #{userId}
      		, NOW()
      	FROM hubez_common.EZ_SVC_USER esu
      	WHERE esu.USER_ID = #{userId}
	</insert>

	<select id="selectAdminSendingLimt" parameterType="map" resultType="map">
		SELECT
			IFNULL(eul.FX_LMT_AMT,0) AS fxLmtAmt
			,IFNULL(eul.MRT_LMT_AMT,0) AS mrtLmtAmt
		FROM hubez_admin.EZ_USER_LMT eul
		INNER JOIN hubez_common.EZ_SVC_USER esu
		ON eul.USER_SEQ = esu.USER_SEQ
        WHERE esu.USER_ID = #{userId}
        AND eul.LMT_YM = DATE_FORMAT(NOW(),'%Y%m')
	</select>

	<update id="updateAdminSendingLimit" parameterType="map">
		UPDATE hubez_admin.EZ_USER_LMT
        SET
           CHG_ID = #{userId}
           ,CHG_DT = NOW()
           <if test="sendingLimit != null and sendingLimit != ''">
           ,MRT_LMT_AMT = #{sendingLimit}
           </if>
        WHERE USER_SEQ = (SELECT USER_SEQ FROM hubez_common.EZ_SVC_USER WHERE USER_ID = #{userId} LIMIT 1)
        AND LMT_YM = DATE_FORMAT(NOW(),'%Y%m')
	</update>

	<select id="selectSvcUserInfo" parameterType="String" resultType="kr.co.uplus.ez.api.custMgt.dto.SvcUserInfo">
		/* custMgt-mapper.xml(selectSvcUserInfo) */
		SELECT
			USER_SEQ
			,USER_ID
			,USER_NM
			,USER_STTUS_CD
			,PRNTS_USER_SEQ
			,CUST_SEQ
			,USER_TP_CD
			,PWD
			,BIZR_AUTH_YN
			,LINE_TP_CD
			,HP_NO
			,EMAIL
			,AUTHCD_080
			,AUT_CD
			,LAST_LOGIN_DT
			,LOGIN_FAIL_CNT
			,PWD_INIT
			,PWD_CHG_DT
			,REG_ID
			,REG_DT
			,CHG_ID
			,CHG_DT
		FROM hubez_common.EZ_SVC_USER
		WHERE USER_ID = #{userId}
	</select>

	<select id="selectApiAuthToken" parameterType="String" resultType="String">
		/* custMgt-mapper.xml(selectApiAuthToken) */
		SELECT AUTHTKN
		FROM hubez_common.EZ_IFSYS_INFO
		WHERE SYS_ID = #{apiApplicationId}
	</select>

	<select id="getSubId" resultType="String">
		SELECT CONCAT('TESTSUBID', LPAD(hubez_common.FUN_NEXT_SEQ('TEST_SUBS_SEQ'), 11, 0)) AS SUB_ID
	</select>

	<select id="getEntrNo" resultType="String">
		SELECT CONCAT('TESTENTRN', LPAD(hubez_common.FUN_NEXT_SEQ('TEST_SUBS_SEQ'), 11, 0)) as ENTR_NO
	</select>	
	
	<!-- 청약 상태 이력 저장 -->
	<insert id="insertSbscstHst" parameterType="kr.co.uplus.ez.api.custMgt.dto.SbscstHstDto">
		/* custMgt-mapper.xml(insertSbscstHst) */
		INSERT INTO hubez_common.EZ_SBSCST_HST(
			SUBS_ID
			,EFCT_ST_DT
		<if test="subsSttusCd != null and subsSttusCd != ''">
			,EFCT_FNS_DT
		</if>
			,PROD_CD
			,SUBS_STTUS_CD
			,REG_ID
			,REG_DT
			,CHG_ID
			,CHG_DT
		)VALUES(  
			#{subsId}
    		, #{efctStDt}
   		<if test="subsSttusCd != null and subsSttusCd != ''">
			, STR_TO_DATE('9999-12-31', '%Y-%m-%d')	
   		</if>
    		, #{prodCd}
    		, #{subsSttusCd}
    		, #{regId}
      		, now() 
      		, #{regId}
      		, now()
      	)
	</insert>
	
	<!-- 상품 정보 조회 -->
	<select id="getProdAmt" parameterType="String" resultType="kr.co.uplus.ez.api.custMgt.dto.ProdInfo">
		/* custMgt-mapper.xml(getProdAmt) */
		SELECT EPI.PROD_AMT
		FROM hubez_common.EZ_SUBS_INFO ESI 
			INNER JOIN hubez_common.EZ_PROD_INFO EPI 
				ON EPI.PROD_CD = ESI.PROD_CD 
		WHERE USER_SEQ = #{userSeq}
	</select>
	
	<!-- 주문 번호 채번 -->
	<select id="getOrder" resultType="String">
		/* custMgt-mapper.xml(getOrder) */
		SELECT hubez_admin.FUN_GET_ORDER();
	</select>
	
	<!-- 오더 저장 -->
	<insert id="insertOrder" parameterType="kr.co.uplus.ez.api.custMgt.dto.OrderDto">
		/* custMgt-mapper.xml(insertOrder) */
		INSERT INTO hubez_admin.EZ_ORDER(
			ORDER_ID
			,ORDER_CRET_DT
			,ORDER_TY_CD
			,REG_ID
		)VALUES(
			#{orderId}
			, STR_TO_DATE(#{orderCretDt}, '%y%m%d%H%i%s')
			, #{orderTyCd}
			, #{regId}
		)
	</insert>
	
	<!-- 차감 상세 저장 -->
	<insert id="insertLmtdctDtl" parameterType="kr.co.uplus.ez.api.custMgt.dto.LmtdctDtlDto">
		/* custMgt-mapper.xml(insertLmtdctDtl) */
		INSERT INTO hubez_admin.EZ_LMTDCT_DTL(
			ORDER_CRET_DT
			,USER_SEQ
			,LMT_YM
			,USE_YM
			,LMT_TP_CD
			,DDCT_TP_CD
			,DDCT_AMT
			,EXE_ORDER_ID
			,SNDDCT_ORDER_ID
			,REG_ID
			,REG_DT
		)VALUES(
			#{orderCretDt}
	   		, #{userSeq}
	   		, #{lmtYm}
	   		, #{useYm}
	   		, #{lmtTpCd}
	   		, #{ddctTpcd}
	   		, #{ddctAmt}
	   		, #{exeOrderId}
	   		, #{snddctOrderId}
	   		, #{regId}
	   		, now()
	   	)
	</insert>
	
	<!-- 한도초기화 추가 -->
	<insert id="insertUserLmt" parameterType="kr.co.uplus.ez.api.custMgt.dto.UserLmtDto">
		/* custMgt-mapper.xml(insertUserLmt) */
		INSERT INTO hubez_admin.EZ_USER_LMT(
			USER_SEQ
			,LMT_YM
			,FX_LMT_AMT
			,MRT_LMT_AMT
			,CFWD_AMT
			,EXTNC_AMT
			,CFWD_RSTRT_BLNC
			,FX_BLNC
			,MRT_USE_AMT
			,SMS_USE_AMT
			,MMS_USE_AMT
			,LMS_USE_AMT
			,ATLK_USE_AMT
			,SMS_USE_CNT
			,LMS_USE_CNT
			,MMS_USE_CNT
			,ATLK_USE_CNT
			,REG_ID
			,REG_DT
			,CHG_ID
			,CHG_DT
		)VALUES(
			#{userSeq}
	   		, #{lmtYm}
	   		, #{fxLmtAmt}
	   		, #{mrtLmtAmt}
	   		, 0
	   		, 0
	   		, 0
	   		, #{fxBlnc}
	   		, 0
	   		, 0
	   		, 0
	   		, 0
	   		, 0 
	   		, 0
	   		, 0
	   		, 0
	   		, 0
	   		, #{regId}
	   		,now()
	   		, #{chgId}
	   		,now()
	   	)
	</insert>
	
	
	<update id="updateBindDcYn" parameterType="kr.co.uplus.ez.api.custMgt.dto.SubsDetail">
		/* custMgt-mapper.xml(updateBindDcYn) */
		UPDATE hubez_common.EZ_SUBS_INFO
        SET
           BIND_DC_YN = #{bindDcYn},
           CHG_DT = NOW()
        WHERE 
           ENTR_NO = #{entrNo}
	</update>
	
	<select id="cntChrgList" parameterType="kr.co.uplus.ez.api.custMgt.dto.ChrgListReqDto" resultType="int">
	SELECT 
	
		 COUNT(*) AS TOTALCNT	
		
			FROM hubez_admin.EZ_CHARGE_MNG ecm 
			INNER JOIN hubez_common.EZ_SVC_USER esu ON esu.USER_SEQ = ecm.USER_SEQ 
			INNER JOIN hubez_common.EZ_CUST_INFO eci ON eci.CUST_SEQ = esu.CUST_SEQ 
			INNER JOIN hubez_common.EZ_SUBS_INFO esi ON esi.USER_SEQ = ecm.USER_SEQ 
			WHERE 1 = 1
				AND ecm.USE_YN ='Y'
				AND esi.SUBS_STTUS_CD ='02'
				AND (#{startDt} BETWEEN ecm.STRT_YM AND ecm.END_YM 
			        OR  #{endDt} BETWEEN ecm.STRT_YM AND ecm.END_YM
			        OR ecm.STRT_YM BETWEEN #{startDt} AND #{endDt}
			        OR ecm.END_YM BETWEEN #{startDt} AND #{endDt})
			<if test="searchType1 != null and searchType1 != ''">
				<if test="searchType1 == '01' and searchText1 != null and searchText1 != ''">
					AND  UPPER(eci.CUST_NM) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
				</if>
				<if test="searchType1 == '02' and searchText1 != null and searchText1 != ''">
					AND  UPPER(eci.BIZRNO) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
				</if>
				<if test="searchType1 == '03' and searchText1 != null and searchText1 != ''">
					AND  UPPER(esu.USER_ID) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
				</if>
			</if>
			ORDER BY ecm.CHRG_SEQ DESC
	</select>
	
	<!-- 충전관리 목록 조회 -->
	<select id="selectChrgList" parameterType="kr.co.uplus.ez.api.custMgt.dto.ChrgListReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.ChrgList">
	SELECT 
		@ROWNUM := @ROWNUM + 1 AS NO
		,A.*
		FROM (
			SELECT
				 eci.CUST_NM 
				, esu.USER_ID 
				, (SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'CHRG_DIV' AND DTL_CD = ecm.CHRG_DIV) as CHRG_DIV
				, ecm.STRT_YM 
				, ecm.END_YM 
				, ecm.CHRG_AMT 
				, ecm.USE_YN 
				, eci.BIZRNO 
				, ecm.CHRG_SEQ 
		
			FROM hubez_admin.EZ_CHARGE_MNG ecm 
			INNER JOIN hubez_common.EZ_SVC_USER esu ON esu.USER_SEQ = ecm.USER_SEQ 
			INNER JOIN hubez_common.EZ_CUST_INFO eci ON eci.CUST_SEQ = esu.CUST_SEQ 
			INNER JOIN hubez_common.EZ_SUBS_INFO esi ON esi.USER_SEQ = ecm.USER_SEQ 
			WHERE 1 = 1
				AND ecm.USE_YN ='Y'
				AND esi.SUBS_STTUS_CD ='02'
				AND (#{startDt} BETWEEN ecm.STRT_YM AND ecm.END_YM 
			        OR  #{endDt} BETWEEN ecm.STRT_YM AND ecm.END_YM
			        OR ecm.STRT_YM BETWEEN #{startDt} AND #{endDt}
			        OR ecm.END_YM BETWEEN #{startDt} AND #{endDt})
			<if test="searchType1 != null and searchType1 != ''">
				<if test="searchType1 == '01' and searchText1 != null and searchText1 != ''">
					AND  UPPER(eci.CUST_NM) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
				</if>
				<if test="searchType1 == '02' and searchText1 != null and searchText1 != ''">
					AND  UPPER(eci.BIZRNO) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
				</if>
				<if test="searchType1 == '03' and searchText1 != null and searchText1 != ''">
					AND  UPPER(esu.USER_ID) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
				</if>
			</if>
			ORDER BY ecm.CHRG_SEQ DESC
			 LIMIT #{page}, #{pagePerRows}) A,  ( SELECT @ROWNUM := #{page} ) AS R
	</select>
	
	<!-- 충전관리 상세 조회 -->
	<select id="selectChrgDetail" parameterType="kr.co.uplus.ez.api.custMgt.dto.ChrgDetailReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.ChrgDetail">
		SELECT 
			 eci.CUST_NM 
			, esu.USER_ID 
			, ecm.CHRG_DIV 
			, ecm.STRT_YM 
			, ecm.END_YM 
			, ecm.CHRG_AMT 
			, ecm.USE_YN 
			, eci.BIZRNO 
			, ecm.CHRG_SEQ 
			, esi.JOIN_DIV
		FROM hubez_admin.EZ_CHARGE_MNG ecm 
		INNER JOIN hubez_common.EZ_SVC_USER esu ON esu.USER_SEQ = ecm.USER_SEQ 
		INNER JOIN hubez_common.EZ_CUST_INFO eci ON eci.CUST_SEQ = esu.CUST_SEQ 
		INNER JOIN hubez_common.EZ_SUBS_INFO esi ON esi.USER_SEQ = ecm.USER_SEQ  
		WHERE 1 = 1
			AND ecm.USE_YN ='Y'		
			AND ecm.CHRG_SEQ  = #{chrgSeq}
			ORDER BY ecm.CHRG_SEQ DESC
	</select>
	
	<!-- 유저ID 카운트 조회 -->
	<select id="selectuserTotalCnt" parameterType="kr.co.uplus.ez.api.custMgt.dto.UserListReqDto" resultType="int">
		/* custMgt-mapper.xml(getChrgSeq) */
		SELECT
	 		COUNT(*)
		 FROM hubez_common.EZ_SVC_USER esu 
		 INNER JOIN hubez_common.EZ_SUBS_INFO esi ON esi.USER_SEQ = esu.USER_SEQ 
		 INNER JOIN hubez_common.EZ_CUST_INFO eci ON eci.CUST_SEQ = esu.CUST_SEQ 
		 WHERE  1 = 1
		 	AND	 esu.USER_TP_CD ='01'
	 		AND  esi.SUBS_STTUS_CD ='02'
	 		AND esu.USER_ID LIKE CONCAT('%' , #{searchText1}, '%')
	</select>
	
	<!-- 유저ID 목록 조회 -->
	<select id="selectUserList" parameterType="kr.co.uplus.ez.api.custMgt.dto.UserListReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.UserinInfo">
		/* custMgt-mapper.xml(getChrgSeq) */
		SELECT
			  esu.USER_ID 
			, eci.CUST_NM  
			, esu.USER_SEQ  
			, esu.USER_STTUS_CD 
			, esu.USER_TP_CD 
			, esi.JOIN_DIV
		 FROM hubez_common.EZ_SVC_USER esu 
		 INNER JOIN hubez_common.EZ_SUBS_INFO esi ON esi.USER_SEQ = esu.USER_SEQ 
		 INNER JOIN hubez_common.EZ_CUST_INFO eci ON eci.CUST_SEQ = esu.CUST_SEQ 
		 WHERE  1 = 1
		 	AND	 esu.USER_TP_CD ='01'
			AND  esi.SUBS_STTUS_CD ='02'
	 		AND esu.USER_ID LIKE CONCAT('%' , #{searchText1}, '%')
		
	</select>
	
	
	
	
	<!-- 이용자 충전번호 조회(채번) -->
	<select id="getChrgSeq" resultType="String">
		/* custMgt-mapper.xml(getChrgSeq) */
		SELECT hubez_common.FUN_NEXT_SEQ('CHRG_SEQ')
	</select>
	
	<!-- 충전관리 등록 -->
	<insert id="insertChrgInfo" parameterType="kr.co.uplus.ez.api.custMgt.dto.ChrgInfo">
	INSERT INTO hubez_admin.EZ_CHARGE_MNG (
		  CHRG_SEQ
		, USER_SEQ
		, CHRG_DIV
		, STRT_YM
		, END_YM
		, CHRG_AMT
		, USE_YN
		, REG_ID
		, REG_DT
		, CHG_ID
		, CHG_DT
	) VALUES(
		  #{chrgSeq}
		, #{userSeq}
		, #{chrgDiv}
		, #{strtYm}
		, #{endYm}
		, #{chrgAmt}
		, 'Y'
		, #{regId}
		, NOW()
		, #{regId}
		, NOW()
		)
	</insert>
	
	<update id="updateChrgAmt"  parameterType= "kr.co.uplus.ez.api.custMgt.dto.ChrgAmtDto">
	UPDATE
		hubez_admin.EZ_USER_LMT eul  
			set
				 eul.MRT_LMT_AMT = eul.MRT_LMT_AMT + #{extcChrgAmt}+ #{cfwdChrgAmt}
				,eul.EXTC_CHRG_AMT = eul.EXTC_CHRG_AMT + #{extcChrgAmt}
				,eul.EXTC_CHRG_BLNC = eul.EXTC_CHRG_BLNC + #{extcChrgAmt}
				,eul.CFWD_CHRG_AMT = eul.CFWD_CHRG_AMT+ #{cfwdChrgAmt}
				,eul.CFWD_CHRG_BLNC = eul.CFWD_CHRG_BLNC+ #{cfwdChrgAmt}
			WHERE eul.USER_SEQ = #{userSeq}
			AND eul.LMT_YM =#{lmtYm}
		
	</update>
	
	<update id="updateChrg" parameterType="kr.co.uplus.ez.api.custMgt.dto.ChrgInfo">
	UPDATE
		hubez_admin.EZ_CHARGE_MNG ecm
			set
				ecm.USE_YN ='N'
			WHERE  ecm.CHRG_SEQ  = #{chrgSeq}
	</update>
	
	<insert id="addChrg" parameterType="kr.co.uplus.ez.api.custMgt.dto.ChrgInfo">
	INSERT INTO hubez_admin.EZ_CHARGE_MNG (
		  CHRG_SEQ
		, USER_SEQ
		, CHRG_DIV
		, STRT_YM
		, END_YM
		, CHRG_AMT
		, USE_YN
		, REG_ID
		, REG_DT
		, CHG_ID
		, CHG_DT
	) VALUES(
		  #{chrgSeq}
		, #{userSeq}
		, 'E'
		, #{strtYm}
		, #{endYm}
		, #{chrgAmt}
		, 'Y'
		, #{regId}
		, NOW()
		, #{regId}
		, NOW()
		)
	</insert>
	
	<update id="deleteChrg" parameterType="kr.co.uplus.ez.api.custMgt.dto.DeleteChrgReqDto">
	UPDATE 
		hubez_admin.EZ_CHARGE_MNG ecm
		set
			ecm.USE_YN ='N'
		WHERE ecm.CHRG_SEQ  in 
		<foreach collection="list" index="i" item="list" open="(" close=")" separator=",">
			#{list.chrgSeq}
		</foreach>
	</update>
	
	
	<!--  2023-03-07 add request 부분
	<select id="selectBLNCLists" parameterType="kr.co.uplus.ez.api.custMgt.dto.UserLmtListReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.UserLmtList"  >
      /* custMgt-mapper.xml(selectBLNCLists) */
	   SELECT 
	      @ROWNUM := @ROWNUM + 1 AS NO
		, eci.CUST_SEQ
		, eul.LMT_YM
		, eul.FX_LMT_AMT
		, eul.CFWD_AMT
		, eul.FX_LMT_AMT + eul.CFWD_AMT AS TOTAL_AMT 
		, eul.SMS_USE_AMT + eul.MMS_USE_AMT + eul.LMS_USE_AMT + eul.MMS_USE_AMT AS YM_USE_AMT 
		, CASE WHEN (eul.FX_LMT_AMT + eul.CFWD_AMT)-(eul.SMS_USE_AMT + eul.MMS_USE_AMT+ eul.LMS_USE_AMT + eul.MMS_USE_AMT)  <![CDATA[<]]>0 THEN '-'
		   ELSE (eul.FX_LMT_AMT + eul.CFWD_AMT)-(eul.SMS_USE_AMT + eul.MMS_USE_AMT + eul.LMS_USE_AMT + eul.MMS_USE_AMT)
		   END AS YM_BLNC 
	FROM hubez_admin.EZ_USER_LMT eul 
	INNER JOIN hubez_common.EZ_SUBS_INFO esi on eul.USER_SEQ = esi.USER_SEQ 
	INNER JOIN hubez_common.EZ_CUST_INFO eci on esi.CUST_SEQ =eci.CUST_SEQ 
	 LEFT JOIN hubez_imdb.EZ_IM_USER eiu on eiu.LOGIN_ID = esi.ATTRCTOR_ID
	      WHERE 1 = 1
	      <if test="startDt != null and startDt != ''">
	       AND eul.REG_DT <![CDATA[ >= ]]>  STR_TO_DATE(concat(REPLACE(#{startDt}, '-', '' ),'000000') , '%Y%m%d%H%i%s')
	      </if>
	      <if test="endDt != null and endDt != ''">
	      	AND esi.SUBS_STTUS_CD <![CDATA[ <= ]]>  STR_TO_DATE(concat(REPLACE(#{endDt}, '-', '' ),'235959'), '%Y%m%d%H%i%s')
	      </if> 
	      <if test="searchType1 != null and searchType1 != ''">
	      	AND esi.SUBS_STTUS_CD = #{searchType1}
	      </if>
	      <if test="searchType2 != null and searchType2 != ''">
	      	<if test="searchType2 == '01'">
	      	AND eiu.EX_PROVUSERTYPE is null
	      	</if>
	      	<if test="searchType2 == '02' ">
	      	AND eiu.EX_PROVUSERTYPE = 'DEALER'
	      	</if>
	      	<if test="searchType2 == '03' ">
	      	AND eiu.EX_PROVUSERTYPE in (  'CCS-CS1'
		                                    , 'CCS-CB1'
		                                    , 'CCS-CB2'
		                                    , 'CCS-CJ'
		                                    , 'CCS-LS2'
		                                    , 'CCS-LS3'
		                                    , 'CCS-M'
		                                    , 'CCS-MJ'
		                                    , 'CCS-P'
		                                    , 'CCS-P2'
		                                    , 'CCS-D'
		                                    , 'CCS-G'
		                                    , 'CCS-S')
		    </if>
	      	<if test="searchType2 == '04' ">
	      	AND eiu.EX_PROVUSERTYPE not in (  'CCS-CS1'
		                                    , 'CCS-CB1'
		                                    , 'CCS-CB2'
		                                    , 'CCS-CJ'
		                                    , 'CCS-LS2'
		                                    , 'CCS-LS3'
		                                    , 'CCS-M'
		                                    , 'CCS-MJ'
		                                    , 'CCS-P'
		                                    , 'CCS-P2'
		                                    , 'CCS-D'
		                                    , 'CCS-G'
		                                    , 'CCS-S', 'DEALER')
		     </if>
		   </if>
	      <if test="searchType3 != null and searchType3 != ''">
	      	<if test="searchType3 == '01' and searchText1 != null and searchText1 != ''">
	      	AND  UPPER(esi.ENTR_NO) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
	      	</if>
	      	<if test="searchType3 == '02' and searchText1 != null and searchText1 != ''">
	      	AND  UPPER(eci.BIZRNO) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
	      	</if>
	      </if>
	      ORDER BY eci.CUST_SEQ DESC
	</select>
	
	<select id="selectBLNCListsExcel" parameterType="kr.co.uplus.ez.api.custMgt.dto.UserLmtListReqDto" resultType="kr.co.uplus.ez.api.custMgt.dto.UserLmtList">
	   SELECT 
	      @ROWNUM := @ROWNUM + 1 AS NO
		, eci.CUST_SEQ
		, eul.LMT_YM
		, eul.FX_LMT_AMT
		, eul.CFWD_AMT
		, eul.FX_LMT_AMT + eul.CFWD_AMT AS TOTAL_AMT 
		, eul.SMS_USE_AMT + eul.MMS_USE_AMT + eul.LMS_USE_AMT + eul.MMS_USE_AMT AS YM_USE_AMT 
		, CASE WHEN (eul.FX_LMT_AMT + eul.CFWD_AMT)-(eul.SMS_USE_AMT + eul.MMS_USE_AMT+ eul.LMS_USE_AMT + eul.MMS_USE_AMT)  <![CDATA[<]]>0 THEN '-'
		   ELSE (eul.FX_LMT_AMT + eul.CFWD_AMT)-(eul.SMS_USE_AMT + eul.MMS_USE_AMT + eul.LMS_USE_AMT + eul.MMS_USE_AMT)
		   END AS YM_BLNC 
	FROM hubez_admin.EZ_USER_LMT eul 
	INNER JOIN hubez_common.EZ_SUBS_INFO esi on eul.USER_SEQ = esi.USER_SEQ 
	INNER JOIN hubez_common.EZ_CUST_INFO eci on esi.CUST_SEQ =eci.CUST_SEQ 
	 LEFT JOIN hubez_imdb.EZ_IM_USER eiu on eiu.LOGIN_ID = esi.ATTRCTOR_ID
	      WHERE 1 = 1
	      <if test="startDt != null and startDt != ''">
	       AND eul.REG_DT <![CDATA[ >= ]]>  STR_TO_DATE(concat(REPLACE(#{startDt}, '-', '' ),'000000') , '%Y%m%d%H%i%s')
	      </if>
	      <if test="endDt != null and endDt != ''">
	      	AND esi.SUBS_STTUS_CD <![CDATA[ <= ]]>  STR_TO_DATE(concat(REPLACE(#{endDt}, '-', '' ),'235959'), '%Y%m%d%H%i%s')
	      </if> 
	      <if test="searchType1 != null and searchType1 != ''">
	      	AND esi.SUBS_STTUS_CD = #{searchType1}
	      </if>
	      <if test="searchType2 != null and searchType2 != ''">
	      	<if test="searchType2 == '01'">
	      	AND eiu.EX_PROVUSERTYPE is null
	      	</if>
	      	<if test="searchType2 == '02' ">
	      	AND eiu.EX_PROVUSERTYPE = 'DEALER'
	      	</if>
	      	<if test="searchType2 == '03' ">
	      	AND eiu.EX_PROVUSERTYPE in (  'CCS-CS1'
		                                    , 'CCS-CB1'
		                                    , 'CCS-CB2'
		                                    , 'CCS-CJ'
		                                    , 'CCS-LS2'
		                                    , 'CCS-LS3'
		                                    , 'CCS-M'
		                                    , 'CCS-MJ'
		                                    , 'CCS-P'
		                                    , 'CCS-P2'
		                                    , 'CCS-D'
		                                    , 'CCS-G'
		                                    , 'CCS-S')
		    </if>
	      	<if test="searchType2 == '04' ">
	      	AND eiu.EX_PROVUSERTYPE not in (  'CCS-CS1'
		                                    , 'CCS-CB1'
		                                    , 'CCS-CB2'
		                                    , 'CCS-CJ'
		                                    , 'CCS-LS2'
		                                    , 'CCS-LS3'
		                                    , 'CCS-M'
		                                    , 'CCS-MJ'
		                                    , 'CCS-P'
		                                    , 'CCS-P2'
		                                    , 'CCS-D'
		                                    , 'CCS-G'
		                                    , 'CCS-S', 'DEALER')
		     </if>
		   </if>
	      <if test="searchType3 != null and searchType3 != ''">
	      	<if test="searchType3 == '01' and searchText1 != null and searchText1 != ''">
	      	AND  UPPER(esi.ENTR_NO) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
	      	</if>
	      	<if test="searchType3 == '02' and searchText1 != null and searchText1 != ''">
	      	AND  UPPER(eci.BIZRNO) LIKE UPPER(CONCAT('%', #{searchText1}, '%'))
	      	</if>
	      </if>
	      ORDER BY eci.CUST_SEQ DESC
	</select>
	-->
	<select id="selectUserInfo" parameterType="String" resultType="kr.co.uplus.ez.api.custMgt.dto.SvcUserInfo">
	/* custMgt-mapper.xml(selectUserInfo) */
		SELECT
			USER_SEQ, HP_NO  
		FROM
			hubez_common.EZ_SVC_USER
		WHERE
			USER_ID = #{userId}
	</select>
	
	<select id="selectSequence" parameterType="String" resultType="String">
		/* extCarryOverAmtNoti.selectSequence -- sequence 채번 일반 */
		SELECT hubez_common.FUN_NEXT_SEQ(#{seqKey})
	</select>
	
	<select id="selectNotiSeq" resultType="String">
		/* extCarryOverAmtNoti.selectNotiSeq 노티 알림 시퀀스 채번 */
		SELECT hubez_common.FUN_NEXT_SEQ('NOTI_SEQ') AS notiSeq
	</select>
	
	<insert id="insertWebMsgCli" parameterType="kr.co.uplus.ez.api.custMgt.dto.WebMsgCliDto">
    	/* extCarryOverAmtNoti.insertWebMsgCli - 메시지 원장 등록 */
    	INSERT INTO 
    		<if test="tableName == 'EZ_MSG_NORMAL'">
	    		hubez_send.EZ_MSG_NORMAL
	    	</if>
	    	<if test="tableName == 'EZ_MSG_REAL'">
	    		hubez_send.EZ_MSG_REAL
	    	</if>
    		(
    			CLIENT_KEY,
				REQ_PRODUCT,
				TRAFFIC_TYPE,
				MSG_STATUS,
				REQ_DATE,
				MSG_BIZ_KEY,
				TEMPLATE_CODE,
				SEND_GROUP_KEY,
				CALLBACK_NUMBER,
				DEPT_CODE,
				MSG,
				PHONE,
				MERGE_DATA,
				TITLE,
				FILE_LIST,
				FB_PRODUCT,
				FB_TITLE,
				FB_MSG,
				FB_FILE_ID,
				RCS_MSGBASE_ID,
				RCS_HEADER,
				RCS_FOOTER,
				RCS_COPY_ALLOWED,
				RCS_EXPIRY_OPTION,
				RCS_AGENCY_ID,
				RCS_BUTTON,
				KAKAO_SENDER_KEY,
				KAKAO_TEMPLATE_KEY,
				KAKAO_BUTTON,
				KAKAO_IMAGE_WIDE_YN,
				KAKAO_IMAGE_URL,
				KAKAO_IMAGE_LINK,
				PUSH_APP_ID,
				PUSH_PROVIDER,
				PUSH_APP_USER_ID,
				WEB_REQ_ID,
				ORDER_ID
	    	) 
	    VALUES 
	    	(
    		    #{clientKey},
				#{reqProduct},
				#{trafficType},
				#{msgStatus},
				NOW(),
				#{msgBizKey},
				#{templateCode},
				#{sendGroupKey},
				#{callbackNumber},
				#{deptCode},
				#{msg},
				#{phone},
				#{mergeData},
				'충전금액알림',
				#{fileList},
				#{fbProduct},
				#{fbTitle},
				#{fbMsg},
				#{fbFileId},
				#{rcsMsgbaseId},
				#{rcsHeader},
				#{rcsFooter},
				#{rcsCopyAllowed},
				#{rcsExpiryOption},
				#{rcsAgencyId},
				#{rcsButton},
				#{kakaoSenderKey},
				#{kakaoTemplateKey},
				#{kakaoButton},
				#{kakaoImageWideYn},
				#{kakaoImageUrl},
				#{kakaoImageLink},
				#{pushAppId},
				#{pushProvider},
				#{pushAppUserId},
				#{webReqId},
				#{orderId}
    		)
    </insert>
	
	<insert id="insertNotiHst" parameterType="kr.co.uplus.ez.api.custMgt.dto.NotiHistDto">
    	/* extCarryOverAmtNoti.insertNotiHst - 알림 이력 등록 */
    	INSERT INTO hubez_common.EZ_NOTI_HIST
    		(
    			SND_YMD,
				USER_SEQ,
				NOTI_SEQ,
				NOTI_DIV,
				NOTI_MSG,
				WEB_REQ_ID,
				REG_DT
	    	) 
	    VALUES 
	    	(
    		    DATE_FORMAT(NOW(), '%Y%m%d'),
				#{userSeq},
				#{notiSeq},
				'04',
				#{notiMsg},
				#{webReqId},
				NOW()
    		)
    </insert>
</mapper>
