<?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.servMgt.ServMgtMapper">

<select id="selectRejectRecvTotalCnt" parameterType="kr.co.uplus.ez.api.servMgt.dto.RejectRecvListReqDto" resultType="int">
		select
			count(*)
		from hubez_common.EZ_SVC_USER esu
			inner join hubez_common.EZ_SUBS_INFO esi
				on esi.USER_SEQ  = esu.USER_SEQ
					and esi.SUBS_STTUS_CD in ('02','03','04','99')
			inner join hubez_common.EZ_CUST_INFO eci
				on eci.CUST_SEQ = esi.CUST_SEQ
		where esu.USER_TP_CD = '01'
		and esu.AUTHCD_080 >= '000000'
		<include refid="rejectRecvListCondition"></include>
</select>
<select id="selectRejectRecvList" parameterType="kr.co.uplus.ez.api.servMgt.dto.RejectRecvListReqDto" resultType="kr.co.uplus.ez.api.servMgt.dto.RejectRecvInfo">
SELECT
		@ROWNUM := @ROWNUM + 1 AS NO,
		A.*
		FROM
		(

		select eci.CUST_NM as custNm
			, eci.BIZRNO as bRegNo
			, esu.USER_ID
			, esu.AUTHCD_080
			, case when esi.SUBS_STTUS_CD  = '99' then '폐기'
				else '사용'
			  end as useYN
			, DATE_FORMAT(esi.OPN_DT, '%Y-%m-%d') as regDt
			, case when esi.SUBS_STTUS_CD  = '99' then DATE_FORMAT(esi.CHG_DT, '%Y-%m-%d')
				else DATE_FORMAT(esi.OPN_DT, '%Y-%m-%d')
			  end as chgDt
		from hubez_common.EZ_SVC_USER esu
			inner join hubez_common.EZ_SUBS_INFO esi
				on esi.USER_SEQ  = esu.USER_SEQ
					and esi.SUBS_STTUS_CD in ('02','03','04','99')
			inner join hubez_common.EZ_CUST_INFO eci
				on eci.CUST_SEQ = esi.CUST_SEQ
		where esu.USER_TP_CD = '01'
		and esu.AUTHCD_080 >= '000000'


		<include refid="rejectRecvListCondition"></include>
		ORDER BY esi.OPN_DT DESC
		LIMIT #{page}, #{pagePerRows}) A,
		( SELECT @ROWNUM := #{page} ) AS R
</select>

<sql id="rejectRecvListCondition">
		<if test='searchType1 != null and searchType1 != ""'>
			<choose>
				<when test='searchType1 == "N"'> <!-- 상태 - 중지 -->
					AND esi.SUBS_STTUS_CD = '99'
				</when>
				<otherwise><!-- 상태 - 사용 -->
					AND esi.SUBS_STTUS_CD != '99'
				</otherwise>
			</choose>
		</if>
		<if test='searchText1 != null and searchText1 != ""'>
			<if test='searchType2 != null and searchType2 != ""'>
				<choose>
					<when test='searchType2 == "custNm"'><!-- 검색조건 - 고객사명 -->
						and UPPER(eci.CUST_NM) LIKE CONCAT('%' , UPPER(#{searchText1}) , '%')
					</when>
					<when test='searchType2 == "bizNo"'><!-- 검색조건 - 사업자번호 -->
						AND eci.BIZRNO like CONCAT('%', #{searchText1}, '%')
					</when>
					<when test='searchType2 == "authCd"'><!-- 검색조건 - 인증코드 -->
						AND esu.AUTHCD_080 like CONCAT('%', #{searchText1}, '%')
					</when>
				</choose>
			</if>
		</if>
</sql>

</mapper>