<?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.sendNumMgt.SendNumMgtMapper">

	<select id="selectProfileTotalCnt"
		parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.ProfileListReqDto"
		resultType="int">
		/* sysMgt-mapper.xml(selectAdmUserTotalCnt) */
		select count(*)
		from hubez_common.EZ_KKO_CHNL ekc
			inner join hubez_common.EZ_SVC_USER esu
				on esu.USER_SEQ = ekc.USER_SEQ
			inner join hubez_common.EZ_CUST_INFO eci
				on eci.CUST_SEQ = esu.CUST_SEQ
		<include refid="profileListCondition"></include>
	</select>

	<select id="selectProfileList"
		parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.ProfileListReqDto"
		resultType="kr.co.uplus.ez.api.sendNumMgt.dto.ProfileInfo">
		/* sysMgt-mapper.xml(selectProfileList) */
		SELECT
			  @ROWNUM := @ROWNUM + 1 AS NO
			, A.custNm
			, bRegNo
			, sendProfile
			, sendProfileKey
			, DORM_YN
			, case when A.MSGHB_STTUS_CD = 'D' then '삭제'
				   when A.DORM_YN = 'Y' then '휴면'
				   when A.BLCK_YN = 'Y' then '미사용'
				   when A.MSGHB_STTUS_CD = 'A' then '사용'
					else '미사용'
				  end as stat
			, DATE_FORMAT(A.REG_DT, '%Y-%m-%d') AS regDt
		FROM
		(
			select eci.CUST_NM as custNm
				, eci.BIZRNO as bRegNo
				, ekc.CHNL_ID as sendProfile
				, ekc.SNDRPROF_KEY as sendProfileKey
				, ekc.DORM_YN
				, ekc.MSGHB_STTUS_CD
				, ekc.BLCK_YN
				, ekc.REG_DT
			from hubez_common.EZ_KKO_CHNL ekc
				inner join hubez_common.EZ_SVC_USER esu
					on esu.USER_SEQ = ekc.USER_SEQ
				inner join hubez_common.EZ_CUST_INFO eci
					on eci.CUST_SEQ = esu.CUST_SEQ
			<include refid="profileListCondition"></include>
			ORDER BY ekc.REG_DT DESC
			LIMIT #{page}, #{pagePerRows}
		) A, ( SELECT @ROWNUM := #{page} ) AS R
	</select>

	<sql id="profileListCondition">
		<!-- 사용 미사용 휴면 삭제 -->
		<if test="searchType1 != null and searchType1 != ''">
			<choose>
				<when test='searchType1 == "A"'> <!-- 상태 사용-->
					AND ekc.MSGHB_STTUS_CD = 'A'
					AND ekc.BLCK_YN = 'N'
					AND ekc.DORM_YN = 'N'
				</when>
				<when test='searchType1 == "N"'> <!-- 상태 미사용-->
					AND ekc.BLCK_YN = 'Y'
					AND MSGHB_STTUS_CD != 'D'
					AND ekc.DORM_YN != 'Y'
				</when>
				<when test='searchType1 == "D"'> <!-- 상태 삭제-->
					AND MSGHB_STTUS_CD = 'D'
				</when>
				<when test='searchType1 == "H"'> <!-- 상태 휴면-->
					AND DORM_YN = 'Y'
					AND MSGHB_STTUS_CD != 'D'
				</when>
			</choose>
		</if>
		<if test='searchText1 != null and searchText1 != ""'>
			<if test='searchType2 != null and searchType2 != ""'>
				<choose>
					<when test='searchType2 == "custNm"'><!-- 검색조건 - 고객사명 -->
						and eci.CUST_NM LIKE CONCAT('%' , #{searchText1}, '%')
					</when>
					<when test='searchType2 == "bregNo"'><!-- 검색조건 - 사업자번호 -->
						and eci.BIZRNO like CONCAT('%', #{searchText1}, '%')
					</when>
					<when test='searchType2 == "sendProfile"'><!-- 검색조건 - 발신프로필 -->
						and ekc.CHNL_ID like CONCAT('%', #{searchText1}, '%')
					</when>
				</choose>
			</if>
		</if>
	</sql>
	<select id="selectSendNumberTotalCnt" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberListReqDto" resultType="int">
		/* sendNumMgt-mapper.xml(selectSendNumberTotalCnt) */
		SELECT count(*)
			from hubez_common.EZ_SVC_SNDRNO ess
				inner join hubez_common.EZ_SNDRNO_REG esr
					on esr.REG_REQ_NO = ess.REG_REQ_NO
				inner join hubez_common.EZ_SVC_USER esu
					on esu.USER_SEQ = esr.USER_SEQ
				inner join hubez_common.EZ_CUST_INFO eci
					on eci.CUST_SEQ = esu.CUST_SEQ
			WHERE 1=1
			<include refid="numberListCondition"></include>
	</select>

	<select id="selectSendNumberList" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberListReqDto" resultType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberInfo">
		/* sendNumMgt-mapper.xml(selectSendNumberList) */
		SELECT
			@ROWNUM := @ROWNUM + 1 AS NO
			, A.CUST_NM
			, REG_REQ_NO
			, A.USER_ID as ADMIN_ID
			, A.RQTR_ID AS register
			, A.BIZRNO AS bRegNo
			, (SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'NMINEE_DIV_CD' AND DTL_CD = A.NMINEE_DIV_CD) AS NMINEE_DIV_CD
			, (SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'INCH_DIV_CD' AND DTL_CD = A.INCH_DIV_CD) AS INCH_DIV_CD
			, (SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'SNDRNO_STTUS_CD' AND DTL_CD = A.STTUS_CD) AS sttusCd
			, A.SNDRNO AS SNDRNO
			, A.SEQ_NO
			, DATE_FORMAT(A.REG_DT, '%Y-%m-%d') AS REG_DT
		FROM
		(
			SELECT
				  eci.CUST_NM
			    , esr.REG_REQ_NO
			    , esu.USER_ID
				, esr.RQTR_ID
				, eci.BIZRNO
				, ess.NMINEE_DIV_CD
				, ess.INCH_DIV_CD
				, ess.STTUS_CD
				, ess.SNDRNO
				, ess.SEQ_NO
				, esr.REG_DT
			from hubez_common.EZ_SVC_SNDRNO ess
				inner join hubez_common.EZ_SNDRNO_REG esr
					on esr.REG_REQ_NO = ess.REG_REQ_NO
				inner join hubez_common.EZ_SVC_USER esu
					on esu.USER_SEQ = esr.USER_SEQ
				inner join hubez_common.EZ_CUST_INFO eci
					on eci.CUST_SEQ = esu.CUST_SEQ
			WHERE 1=1
			<include refid="numberListCondition"></include>
			ORDER BY ess.REG_REQ_NO DESC, ess.REG_DT
			LIMIT #{page}, #{pagePerRows}) A, ( SELECT @ROWNUM := #{page} ) AS R
	</select>

	<sql id="numberListCondition">
		<if test='searchType1 != null and searchType1 != ""'>
			AND ess.STTUS_CD = #{searchType1}
		</if>
		<if test='searchType2 != null and searchType2 != ""'>
			AND ess.NMINEE_DIV_CD = #{searchType2}
		</if>
		<if test='searchType3 != null and searchType3 != ""'>
			AND ess.INCH_DIV_CD = #{searchType3}
		</if>
		<if test="searchType5 != null and searchType5 != ''">
			AND ess.REG_TP_CD = #{searchType5}
		</if>
		<if test='searchText1 != null and searchText1 != ""'>
			<if test='searchType4 != null and searchType4 != ""'>
				<choose>
					<when test='searchType4 == "bregNo"'><!-- 검색조건 - 사업자번호 -->
						AND eci.BIZRNO like CONCAT('%', #{searchText1}, '%')
					</when>
					<when test='searchType4 == "regNo"'><!-- 검색조건 - 발신번호 -->
						AND ess.SNDRNO like CONCAT('%', #{searchText1}, '%')
					</when>
					<when test='searchType4 == "custNm"'><!-- 검색조건 - 고객사명 -->
						AND eci.CUST_NM LIKE CONCAT('%' , #{searchText1}, '%')
					</when>
				</choose>
			</if>
		</if>
	</sql>

	<select id="selectAdminTotalCnt" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.SendAdminListReqDto" resultType="int">
		/* sendNumMgt-mapper.xml(selectAdminTotalCnt) */
		SELECT
			COUNT(*)
		FROM
			hubez_common.EZ_SVC_USER esu, hubez_common.EZ_CUST_INFO eci
		WHERE
			esu.CUST_SEQ = eci.CUST_SEQ
		    AND esu.USER_TP_CD IN ('01', '03')
			AND esu.USER_ID LIKE CONCAT('%' , #{searchText1}, '%')
	</select>

	<select id="selectAdminList" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.SendAdminListReqDto" resultType="kr.co.uplus.ez.api.sendNumMgt.dto.SendAdminInfo">
		/* sendNumMgt-mapper.xml(selectAdminList) */
		SELECT
			esu.USER_ID AS adminId,
			eci.BIZRNO AS bRegNo,
			eci.CUST_NM AS custNm,
			esu.BIZR_AUTH_YN,
			eci.CUST_SEQ
		FROM
			hubez_common.EZ_SVC_USER esu, hubez_common.EZ_CUST_INFO eci
		WHERE
			esu.CUST_SEQ = eci.CUST_SEQ
			AND esu.USER_TP_CD IN ('01', '03')
		    AND esu.USER_ID LIKE CONCAT('%' , #{searchText1}, '%')
	</select>

	<select id="selectUserSeqByAdminId" parameterType="String" resultType="String">
		/* sendNumMgt-mapper.xml(selectUserSeqByAdminId) */
		SELECT
			USER_SEQ
		FROM
			hubez_common.EZ_SVC_USER
		WHERE
			USER_ID = #{adminId}
	</select>
	
	<select id="selectUserEmailByAdminId" parameterType="String" resultType="String">
		/* sendNumMgt-mapper.xml(selectUserSeqByAdminId) */
		SELECT
			EMAIL
		FROM
			hubez_common.EZ_SVC_USER
		WHERE
			USER_ID = #{adminId}
	</select>
	

	<delete id="deleteNumber" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.DeleteNumberReqDto">
		/* sendNumMgt-mapper.xml(deleteNumber) */
		DELETE
		FROM
			hubez_common.EZ_SVC_SNDRNO
		<where>
			SEQ_NO IN
			<foreach collection="list" item="item" index="i" open="("
				separator="," close=")">
				#{item.seqNo}
			</foreach>
		</where>
	</delete>

	<select id="selectSndrnoList" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.InsertNumberReqDto" resultType="kr.co.uplus.ez.api.sendNumMgt.dto.InsertNumber">
		/* sendNumMgt-mapper.xml(selectSndrnoList) */
		SELECT
			ess.SNDRNO AS sendNum
		FROM
			hubez_common.EZ_SVC_SNDRNO ess
		INNER JOIN hubez_common.EZ_CUST_INFO eci
			ON
			ess.CUST_SEQ = eci.CUST_SEQ
		WHERE eci.BIZRNO = #{bregno}
		AND ess.SNDRNO IN
		<foreach collection="list" item="item" index="i" open="(" separator="," close=")">
			#{item.sendNum}
		</foreach>
	</select>

	<select id="selectSndrnoListAll" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.InsertNumberReqDto" resultType="kr.co.uplus.ez.api.sendNumMgt.dto.InsertNumber">
		/* sendNumMgt-mapper.xml(selectSndrnoListAll) */
		SELECT
			SNDRNO AS sendNum
		FROM
			hubez_common.EZ_SVC_SNDRNO
		WHERE 1=1
		AND SNDRNO IN
		<foreach collection="list" item="item" index="i" open="(" separator="," close=")">
			#{item.sendNum}
		</foreach>
	</select>

	<select id="selectRegReqNo" resultType="String">
		SELECT CONCAT(DATE_FORMAT(NOW(), '%Y%m'),  LPAD(hubez_common.FUN_NEXT_SEQ('SNDRNO_REG_SEQ'), 7,  '0')) AS REG_REQ_NO
	</select>

	<insert id="insertNumber" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.InsertNumberReqDto">
		/* sendNumMgt-mapper.xml(insertNumber) */
		INSERT INTO
		hubez_common.EZ_SNDRNO_REG (
			 REG_REQ_NO
			,REQ_STTUS_CD
			,REQ_CNT
			,APV_CNT
			,REJT_CNT
			,REQ_DT
			,USER_SEQ
			,REG_ID
			,REG_DT
			,CHG_ID
			,CHG_DT			
			,RQTR_ID
			,RQTR_NM
			,CMPLT_DT			
		)
		VALUES(
		    #{regReqNo}
			,#{reqSttusCd}
			,#{reqCnt}
			,#{apvCnt}
			,#{rejtCnt}
			,NOW()
			,#{userSeq}
			,#{regId}
			,NOW()
			,#{regId}
			,NOW()
			,#{regId}
			,(select OPRTR_NM  from hubez_admin.EZ_ADM_USER eau where OPRTR_ID = #{regId})
			,#{cmpltDt}						
		)
	</insert>

	<insert id="insertNumberDoc" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.InsertNumberFile">
	/* sendNumMgt-mapper.xml(insertNumberDoc) */
	INSERT INTO
	hubez_common.EZ_SNDRNO_DOC (
		REG_REQ_NO
		,DOC_NO
		,DOC_TP_CD
		,DOC_TITLE
		,FILE_NM
		,FILE_PATH
		,FILE_SIZE
		,REG_ID
		,REG_DT
		,CHG_ID
		,CHG_DT
	)
	VALUES(
		#{regReqNo}
		,#{docNo}
		,#{docTpCd}
		,#{docTitle}
		,#{fileNm}
		,#{filePath}
		,#{fileSize}
		,#{regId}
		,NOW()
		,#{regId}
		,NOW()
	)
	</insert>

	<insert id="insertNumbers" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.InsertNumberReqDto">
		INSERT INTO
			hubez_common.EZ_SVC_SNDRNO(
			  SNDRNO
			 ,REG_TP_CD
			 ,SNDRNO_NM
			 ,CUST_SEQ
			 ,REG_REQ_NO
			 ,STTUS_CD
			 ,USE_YN
			 ,NMINEE_DIV_CD
			 ,INCH_DIV_CD
			 ,REG_ID
			 ,REG_DT
			 ,CHG_ID
			 ,CHG_DT
		)
		VALUES
		<foreach collection="list" item="item" separator="," open="(" close=")">
			#{item.sendNum}
			,'02'
			,#{item.sendNm}
			,#{custSeq}
			,#{regReqNo}
			,'01'
			,'Y'
			,#{nmineeDivCd}
			,#{inchDivCd}
			,#{regId}
			,NOW()
			,#{regId}
			,NOW()
		</foreach>


	</insert>

	<insert id="insertNumberList" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.InsertNumber">
	/* sendNumMgt-mapper.xml(insertNumberList) */
	INSERT INTO
	hubez_common.EZ_SVC_SNDRNO(
		SNDRNO
		,REG_TP_CD
		,SNDRNO_NM
		,CUST_SEQ
		,REG_REQ_NO
		,STTUS_CD
		,USE_YN
		,NMINEE_DIV_CD
		,INCH_DIV_CD
		,REG_ID
		,REG_DT
		,CHG_ID
		,CHG_DT
	)
	VALUES(
		#{sendNum}
		,'02'
		,#{sendNm}
		,#{custSeq}
		,#{regReqNo}
		,'02'
		,'Y'
		,#{nmineeDivCd}
		,#{inchDivCd}
		,#{regId}
		,NOW()
		,#{regId}
		,NOW()

	)
	</insert>

	<select id="detailNumber" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.DetailNumberReqDto"  resultType="kr.co.uplus.ez.api.sendNumMgt.dto.DetailNumber">
		/* sendNumMgt-mapper.xml(detailNumber) */
		SELECT
			eci.CUST_NM AS custNm
			 , ess.SNDRNO
			 , esu.USER_ID AS adminId
			 , ess.REG_ID AS register
			 , eci.BIZRNO AS bRegNo
			 , ess.SNDRNO_NM
			 , ess.SNDRNO
			 , esr.REG_REQ_NO
			 ,(
			SELECT
				ecd.DTL_CD_NM
			FROM
				hubez_common.EZ_CD_DTL ecd
			WHERE
				ecd.GRP_CD = 'NMINEE_DIV_CD'
			  AND ecd.DTL_CD = ess.NMINEE_DIV_CD) AS nmineeDivCd
			 ,(
			SELECT
				ecd.DTL_CD_NM
			FROM
				hubez_common.EZ_CD_DTL ecd
			WHERE
				ecd.GRP_CD = 'INCH_DIV_CD'
			  AND ecd.DTL_CD = ess.INCH_DIV_CD) AS inchDivCd
			 ,(
			SELECT
				ecd.DTL_CD_NM
			FROM
				hubez_common.EZ_CD_DTL ecd
			WHERE
				ecd.GRP_CD = 'SNDRNO_STTUS_CD'
			  AND ecd.DTL_CD = ess.STTUS_CD) AS sttusCd
			 ,(
			SELECT
				ecd.DTL_CD_NM
			FROM
				hubez_common.EZ_CD_DTL ecd
			WHERE
				ecd.GRP_CD = 'SNDRNO_REG_TP_CD'
			  AND ecd.DTL_CD = ess.REG_TP_CD) AS regTpCd
			 , DATE_FORMAT(ess.REG_DT, '%Y-%m-%d') AS regDt
		FROM
			hubez_common.EZ_CUST_INFO eci
				INNER JOIN hubez_common.EZ_SVC_USER esu
						   ON
							   eci.CUST_SEQ = esu.CUST_SEQ
				INNER JOIN hubez_common.EZ_SNDRNO_REG esr
						   ON
							   esu.USER_SEQ = esr.USER_SEQ
				INNER JOIN hubez_common.EZ_SVC_SNDRNO ess
						   ON
							   esr.REG_REQ_NO = ess.REG_REQ_NO
		WHERE 1 = 1
		  AND ess.SEQ_NO = #{seqNo}
	</select>

	<select id="selectAuthFileList" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.DetailNumberReqDto"  resultType="kr.co.uplus.ez.api.sendNumMgt.dto.AuthFileInfo">
		/* sendNumMgt-mapper.xml(selectAuthFileList) */
		SELECT
			DOC_TP_CD AS docTpCd,
			DOC_TITLE AS docTitle,
			FILE_NM AS fileNm,
			FILE_PATH AS filePath,
			FILE_SIZE AS fileSize
		FROM
			hubez_common.EZ_SNDRNO_DOC
		WHERE
			REG_REQ_NO = 
				(
					SELECT
						REG_REQ_NO
					FROM
						hubez_common.EZ_SNDRNO_REG
					WHERE
						USER_SEQ = (SELECT USER_SEQ FROM hubez_common.EZ_SNDRNO_REG	WHERE REG_REQ_NO = 	#{regReqNo})
					AND REQ_STTUS_CD = '02'
					AND APV_CNT > 0
					ORDER BY REG_REQ_NO ASC
					LIMIT 1
				)
		AND DOC_TP_CD IN ('06', '07', '13', '14')
		UNION
		SELECT
			DOC_TP_CD AS docTpCd,
			DOC_TITLE AS docTitle,
			FILE_NM AS fileNm,
			FILE_PATH AS filePath,
			FILE_SIZE AS fileSize
		FROM
			hubez_common.EZ_SNDRNO_DOC
		WHERE
			REG_REQ_NO = #{regReqNo}
	</select>

	<select id="selectSvcSndrnoListTotalCnt" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberApprListReqDto" resultType="int">
		/* sendNumMgt-mapper.xml(selectSvcSndrnoListTotalCnt) */
		SELECT COUNT(*)
         FROM hubez_common.EZ_SNDRNO_REG esr
            inner join hubez_common.EZ_SVC_USER esu
               on esu.USER_SEQ = esr.USER_SEQ
            inner join hubez_common.EZ_CUST_INFO eci
               on eci.CUST_SEQ = esu.CUST_SEQ
         where 1 = 1
         and exists (
            select 1 from hubez_common.EZ_SVC_SNDRNO
            where REG_REQ_NO = esr.REG_REQ_NO
            <if test='searchType2 != null and searchType2 != ""'>
               and  NMINEE_DIV_CD =  #{searchType2}
            </if>
         )
         <include refid="selectSvcSndrnoListCondition" />
	</select>

	<select id="selectSvcSndrnoList" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberApprListReqDto" resultType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberApprInfo">
		/* sendNumMgt-mapper.xml(selectSvcSndrnoList) */
		SELECT  @ROWNUM := @ROWNUM + 1 AS NO
           , A.REG_REQ_NO
           , DATE_FORMAT(A.REG_DT, '%Y-%m-%d') AS REG_DT
           , A.USER_ID AS adminId
           , A.BIZRNO
		    , A.CUST_NM
		     , A.REQ_CNT
		     , A.APV_CNT
		     , A.REJT_CNT
           ,(
              SELECT ecd.DTL_CD_NM  FROM hubez_common.EZ_CD_DTL ecd
              WHERE ecd.GRP_CD = 'NMINEE_DIV_CD'  AND ecd.DTL_CD = A.NMINEE_DIV_CD) AS nmineeDivCd
           , ( SELECT ecd.DTL_CD_NM FROM hubez_common.EZ_CD_DTL ecd WHERE ecd.GRP_CD = 'SNDRNO_REQ_STTUS_CD' AND ecd.DTL_CD = A.REQ_STTUS_CD) AS REQ_STTUS_CD
           , DATE_FORMAT(A.CMPLT_DT, '%Y-%m-%d') AS CMPLT_DT
      FROM
      (
         SELECT esr.REG_REQ_NO
             , esr.REG_DT
             , esu.USER_ID
             , eci.BIZRNO
             , eci.CUST_NM
             , esr.REQ_CNT
             , esr.APV_CNT
             , esr.REJT_CNT
             , esr.REQ_STTUS_CD
             , esr.CMPLT_DT
             , (select NMINEE_DIV_CD from hubez_common.EZ_SVC_SNDRNO where REG_REQ_NO = esr.REG_REQ_NO limit 1) as NMINEE_DIV_CD
         FROM hubez_common.EZ_SNDRNO_REG esr
            inner join hubez_common.EZ_SVC_USER esu
               on esu.USER_SEQ = esr.USER_SEQ
            inner join hubez_common.EZ_CUST_INFO eci
               on eci.CUST_SEQ = esu.CUST_SEQ
         where 1 = 1
         and exists (
            select 1 from hubez_common.EZ_SVC_SNDRNO
            where REG_REQ_NO = esr.REG_REQ_NO
            <if test='searchType2 != null and searchType2 != ""'>
               and  NMINEE_DIV_CD =  #{searchType2}
            </if>
         )
         <include refid="selectSvcSndrnoListCondition" />
         ORDER BY esr.REG_DT DESC
      LIMIT #{page}, #{pagePerRows}) A,
      ( SELECT @ROWNUM :=  #{page} ) AS R
	</select>

	<sql id="selectSvcSndrnoListCondition">
		<if test='searchType1 != null and searchType1 != ""'>
			AND esr.REQ_STTUS_CD = #{searchType1}
		</if>
		<if test='searchText1 != null and searchText1 != ""'>
			AND eci.BIZRNO like concat('%', #{searchText1},'%')
		</if>
		<if test='searchText2 != null and searchText2 != ""'>
			AND eci.CUST_NM like concat('%', #{searchText2},'%')
		</if>
	</sql>

	<select id="selectSvcSndrnoReqDetail" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberApprDetailReqDto" resultType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberApprDetailRes">
		/* sendNumMgt-mapper.xml(selectSvcSndrnoReqDetail) */
		SELECT
			esr.REG_REQ_NO
			 , DATE_FORMAT(esr.REG_DT, '%Y-%m-%d') AS REG_DT
		     , DATE_FORMAT(esr.CMPLT_DT, '%Y-%m-%d') AS CMPLT_DT
			 , esu.USER_ID AS adminId
			 , eci.CUST_NM
			 , eci.BIZRNO
			 ,(
			SELECT
				ecd.DTL_CD_NM
			FROM
				hubez_common.EZ_CD_DTL ecd
			WHERE
				ecd.GRP_CD = 'NMINEE_DIV_CD'
			  AND ecd.DTL_CD = ess.NMINEE_DIV_CD) AS nmineeDivCd
			 , esr.SLF_AUTH_HP
			 , esr.MEMO
		FROM
			hubez_common.EZ_SVC_SNDRNO ess
				INNER JOIN hubez_common.EZ_SNDRNO_REG esr
						   ON ess.REG_REQ_NO = esr.REG_REQ_NO
				INNER JOIN hubez_common.EZ_SVC_USER esu
						   ON esu.USER_SEQ = esr.USER_SEQ
				INNER JOIN hubez_common.EZ_CUST_INFO eci
						   ON eci.CUST_SEQ = esu.CUST_SEQ
						   AND esr.REG_REQ_NO = #{regReqNo}
		GROUP BY ess.REG_REQ_NO
	</select>

	<select id="selectSvcSndnoList" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberApprDetailReqDto" resultType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberApprNum">
		/* sendNumMgt-mapper.xml(selectSvcSndnoList) */
		SELECT
			ess.SNDRNO
			 , ess.STTUS_CD
			 , ess.SEQ_NO
			 , ess.REG_REQ_NO
		FROM
			hubez_common.EZ_SVC_SNDRNO ess
		WHERE
			ess.REG_REQ_NO = #{regReqNo}
	</select>

	<select id="selectSndnoDocList" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberApprDetailReqDto" resultType="kr.co.uplus.ez.api.sendNumMgt.dto.SendNumberApprDoc">
		/* sendNumMgt-mapper.xml(selectSndnoDocList) */
		SELECT
			DOC_TP_CD AS docTpCd,
			DOC_TITLE AS docTitle,
			FILE_NM AS fileNm,
			FILE_PATH AS filePath,
			FILE_SIZE AS fileSize
		FROM
			hubez_common.EZ_SNDRNO_DOC
		WHERE
			REG_REQ_NO = 
				(
					SELECT
						REG_REQ_NO
					FROM
						hubez_common.EZ_SNDRNO_REG
					WHERE
						USER_SEQ = (SELECT USER_SEQ FROM hubez_common.EZ_SNDRNO_REG	WHERE REG_REQ_NO = 	#{regReqNo})
					AND REQ_STTUS_CD = '02'
					AND APV_CNT > 0
					ORDER BY REG_REQ_NO ASC
					LIMIT 1
				)
		AND DOC_TP_CD IN ('06', '07', '13', '14')
		UNION
		SELECT
			DOC_TP_CD AS docTpCd,
			DOC_TITLE AS docTitle,
			FILE_NM AS fileNm,
			FILE_PATH AS filePath,
			FILE_SIZE AS fileSize
		FROM
			hubez_common.EZ_SNDRNO_DOC
		WHERE
			REG_REQ_NO = #{regReqNo}
	</select>

	<update id="updateSndrno" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.UpdateApprSendNumber">
		/* sendNumMgt-mapper.xml(updateSndrno) */
			UPDATE
				hubez_common.EZ_SVC_SNDRNO
			SET
				CHG_ID = #{adminId}
			  , CHG_DT = NOW()
			  , STTUS_CD = #{sttusCd}			  
			  , USE_YN = #{useYn}			  
			WHERE SEQ_NO = #{seqNo}
			  AND SNDRNO = #{sndrno}
	</update>

	<update id="upddateSndrnoReg" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.UpdateApprReqDto">
		/* sendNumMgt-mapper.xml(upddateSndrnoReg) */
		UPDATE
			hubez_common.EZ_SNDRNO_REG
		SET
			CHG_ID = #{adminId}
		  , CHG_DT = NOW()
		  , MEMO = #{memo}
		  , APV_CNT = #{apvCnt}
		  , REJT_CNT = #{rejtCnt}
		<if test='cmpltYn != null and cmpltYn != ""'>
		  , CMPLT_DT = NOW()
		</if>
		<if test='reqSttusCd != null and reqSttusCd != ""'>
		  , REQ_STTUS_CD = #{reqSttusCd}
		</if>
		WHERE REG_REQ_NO = #{regReqNo}
	</update>

	<update id="updateSvcUser" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.UpdateApprReqDto">
		/* sendNumMgt-mapper.xml(updateSvcUser) */
		UPDATE
			hubez_common.EZ_SVC_USER
		SET
			BIZR_AUTH_YN = 'Y'
		  , CHG_ID = #{adminId}
		  , CHG_DT = NOW()
		WHERE
			USER_ID = #{adminId}
	</update>
	
	<select id="selectDownload" parameterType="kr.co.uplus.ez.api.sendNumMgt.dto.InsertNumberFile" resultType="kr.co.uplus.ez.api.sendNumMgt.dto.InsertNumberFile">
		/* sendNumMgt-mapper.xml(selectDownload) */
		 select 
		 REG_REQ_NO,
		 DOC_NO ,
		 DOC_TP_CD ,
		 DOC_TITLE ,
		 FILE_NM ,
		 FILE_PATH ,
		 FILE_SIZE ,
		 REG_ID ,
		 REG_DT ,
		 CHG_ID ,
		 CHG_DT 
		 from hubez_common.EZ_SNDRNO_DOC
		 WHERE REG_REQ_NO = #{regReqNo}
		 and DOC_NO = #{docNo}
	</select>
	
	<select id="selectApiAuthToken" parameterType="String" resultType="String">
		/* sendNumMgt-mapper.xml(selectApiAuthToken) */
		SELECT AUTHTKN
		FROM hubez_common.EZ_IFSYS_INFO
		WHERE SYS_ID = #{apiApplicationId}
	</select>
	
	<select id="selectSequence" parameterType="String" resultType="String">
		/* sendNumMgt.selectSequence -- sequence 채번 일반 */
		SELECT hubez_common.FUN_NEXT_SEQ(#{seqKey})
	</select>
	
	<select id="selectUserPhoneByAdminId" parameterType="String" resultType="String">
		/* sendNumMgt-mapper.xml(selectUserPhoneByAdminId) */
		SELECT
			HP_NO
		FROM
			hubez_common.EZ_SVC_USER
		WHERE
			USER_ID = #{adminId}
	</select>
	
	<select id="selectNotiSeq" resultType="String">
		/* sendNumMgt.selectNotiSeq 노티 알림 시퀀스 채번 */
		SELECT hubez_common.FUN_NEXT_SEQ('NOTI_SEQ') AS notiSeq
	</select>
	
</mapper>