<?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.calculate.CalculateMapper">

	<select id="selectCalculateListTotalCnt" parameterType="kr.co.uplus.ez.api.calculate.dto.CalcListReqDto" resultType="int">
		/* calculate-mapper.xml(selectCalculateListTotalCnt) */
		SELECT	count(*) AS TOTALCNT
			FROM (
					SELECT RAT_YM
						, RAT_TMS
						, USE_YM
						, COLEC_TMS
					from hubez_admin.EZ_RAT_TXN
					where LAST_YN = 'Y'
					<![CDATA[
					and RAT_YM >= STR_TO_DATE(CONCAT(#{startMonth},'01'),'%Y%m%d')
					AND RAT_YM < DATE_ADD(STR_TO_DATE(CONCAT(#{endMonth},'01'),'%Y%m%d'), INTERVAL 1 MONTH)
					]]>
			) tms
				inner join hubez_admin.EZ_UTXNCOLEC_DTL eud
					on eud.USE_YM = tms.USE_YM
						and tms.COLEC_TMS = eud.COLEC_TMS
			where 1 =1
			<![CDATA[
			and eud.USE_YM >= STR_TO_DATE(CONCAT(#{startMonth},'01'),'%Y%m%d')
			and eud.USE_YM < DATE_ADD(STR_TO_DATE(CONCAT(#{endMonth},'01'),'%Y%m%d'), INTERVAL 1 MONTH)
			]]>
			<if test="custNm != null and custNm != ''">
				AND eud.CUST_NM LIKE CONCAT('%',#{custNm},'%')
			</if>
			<if test="bizrno != null and bizrno != ''">
				AND eud.BIZRNO LIKE CONCAT('%',#{bizrno},'%')
			</if>
			ORDER BY eud.USE_YM DESC, eud.CUST_NM DESC
	</select>

	<select id="selectCalculateList" parameterType="kr.co.uplus.ez.api.calculate.dto.CalcListReqDto" resultType="kr.co.uplus.ez.api.calculate.dto.CalcList">
		/* calculate-mapper.xml(selectCalculateList) */
		SELECT	eud.CUST_NM
			, eud.BIZRNO
			, DATE_FORMAT(eud.USE_YM, '%Y-%m') AS USE_YM
			, eud.PROD_NM
			, ROUND(IFNULL(eud.PROD_AMT,0), 1) AS PROD_AMT
			, ROUND(IFNULL(eud.FX_LMT_AMT,0) + IFNULL(eud.CFWD_AMT,0), 1) AS START_AMT  -- 시작금액 : 정액한도금액+이월금액
			, ROUND(IFNULL(eud.FX_USE_AMT,0) + IFNULL(eud.CFWD_USE_AMT,0) + IFNULL(eud.MRT_USE_AMT,0), 1) AS USE_AMT  -- 사용금액 : 정액사용금액 + 이월사용금액 + 종량사용금액
			, ROUND(IFNULL(eud.CFWD_AMT,0), 1) AS CFWD_AMT -- 이월금액
			, ROUND(IFNULL(eud.MRT_USE_AMT,0), 1) AS MRT_USE_AMT -- 종량금액
			, ROUND(IFNULL(eud.EXTNC_AMT,0), 1) AS EXTNC_AMT  -- 소멸금액 : 소멸처리금액
			, ifnull((
				select RAT_AMT
				from hubez_admin.EZ_RATTXN_DTL
				where RAT_YM = tms.RAT_YM and RAT_TMS = tms. RAT_TMS and SUBS_ID = eud.SUBS_ID
			  ) , 0) as BILLING_AMT
			, (IFNULL(eud.SMS_USE_CNT,0) + IFNULL(eud.LMS_USE_CNT,0) + IFNULL(eud.MMS_USE_CNT,0) + IFNULL(eud.ATLK_USE_CNT,0)) AS TOTAL_SND_CNT
			, IFNULL(eud.SMS_USE_CNT,0) AS SMS_SND_CNT
			, IFNULL(eud.LMS_USE_CNT,0) AS LMS_SND_CNT
			, IFNULL(eud.MMS_USE_CNT,0) AS MMS_SND_CNT
			, IFNULL(eud.ATLK_USE_CNT,0) AS ATLK_SND_CNT
		FROM (
				SELECT RAT_YM
					, RAT_TMS
					, USE_YM
					, COLEC_TMS
				from hubez_admin.EZ_RAT_TXN
				where LAST_YN = 'Y'
				<![CDATA[
				and RAT_YM >= STR_TO_DATE(CONCAT(#{startMonth},'01'),'%Y%m%d')
				AND RAT_YM < DATE_ADD(STR_TO_DATE(CONCAT(#{endMonth},'01'),'%Y%m%d'), INTERVAL 1 MONTH)
				]]>
		) tms
			inner join hubez_admin.EZ_UTXNCOLEC_DTL eud
				on eud.USE_YM = tms.USE_YM
					and tms.COLEC_TMS = eud.COLEC_TMS
		where 1 =1
		<![CDATA[
		and eud.USE_YM >= STR_TO_DATE(CONCAT(#{startMonth},'01'),'%Y%m%d')
		and eud.USE_YM < DATE_ADD(STR_TO_DATE(CONCAT(#{endMonth},'01'),'%Y%m%d'), INTERVAL 1 MONTH)
		]]>
		<if test="custNm != null and custNm != ''">
			AND eud.CUST_NM LIKE CONCAT('%',#{custNm},'%')
		</if>
		<if test="bizrno != null and bizrno != ''">
			AND eud.BIZRNO LIKE CONCAT('%',#{bizrno},'%')
		</if>
		ORDER BY eud.USE_YM DESC, eud.CUST_NM DESC
		LIMIT #{page}, #{pagePerRows}
	</select>


	<select id="selectCalculateExcelList" parameterType="kr.co.uplus.ez.api.calculate.dto.CalcListReqDto" resultType="kr.co.uplus.ez.api.calculate.dto.CalcList">
		/* calculate-mapper.xml(selectCalculateExcelList) */
		SELECT	eud.CUST_NM
			, eud.BIZRNO
			, DATE_FORMAT(eud.USE_YM, '%Y-%m') AS USE_YM
			, eud.PROD_NM
			, ROUND(IFNULL(eud.PROD_AMT,0), 1) AS PROD_AMT
			, ROUND(IFNULL(eud.FX_LMT_AMT,0) + IFNULL(eud.CFWD_AMT,0), 1) AS START_AMT  -- 시작금액 : 정액한도금액+이월금액
			, ROUND(IFNULL(eud.FX_USE_AMT,0) + IFNULL(eud.CFWD_USE_AMT,0) + IFNULL(eud.MRT_USE_AMT,0), 1) AS USE_AMT  -- 사용금액 : 정액사용금액 + 이월사용금액 + 종량사용금액
			, ROUND(IFNULL(eud.CFWD_AMT,0), 1) AS CFWD_AMT -- 이월금액
			, ROUND(IFNULL(eud.MRT_USE_AMT,0), 1) AS MRT_USE_AMT -- 종량금액
			, ROUND(IFNULL(eud.EXTNC_AMT,0), 1) AS EXTNC_AMT  -- 소멸금액 : 소멸처리금액
			, ifnull((
				select RAT_AMT
				from hubez_admin.EZ_RATTXN_DTL
				where RAT_YM = tms.RAT_YM and RAT_TMS = tms. RAT_TMS and SUBS_ID = eud.SUBS_ID
			  ) , 0) as BILLING_AMT
			, (IFNULL(eud.SMS_USE_CNT,0) + IFNULL(eud.LMS_USE_CNT,0) + IFNULL(eud.MMS_USE_CNT,0) + IFNULL(eud.ATLK_USE_CNT,0)) AS TOTAL_SND_CNT
			, IFNULL(eud.SMS_USE_CNT,0) AS SMS_SND_CNT
			, IFNULL(eud.LMS_USE_CNT,0) AS LMS_SND_CNT
			, IFNULL(eud.MMS_USE_CNT,0) AS MMS_SND_CNT
			, IFNULL(eud.ATLK_USE_CNT,0) AS ATLK_SND_CNT
		FROM (
				SELECT RAT_YM
					, RAT_TMS
					, USE_YM
					, COLEC_TMS
				from hubez_admin.EZ_RAT_TXN
				where LAST_YN = 'Y'
				<![CDATA[
				and RAT_YM >= STR_TO_DATE(CONCAT(#{startMonth},'01'),'%Y%m%d')
				AND RAT_YM < DATE_ADD(STR_TO_DATE(CONCAT(#{endMonth},'01'),'%Y%m%d'), INTERVAL 1 MONTH)
				]]>
		) tms
			inner join hubez_admin.EZ_UTXNCOLEC_DTL eud
				on eud.USE_YM = tms.USE_YM
					and tms.COLEC_TMS = eud.COLEC_TMS
		where 1 =1
		<![CDATA[
		and eud.USE_YM >= STR_TO_DATE(CONCAT(#{startMonth},'01'),'%Y%m%d')
		and eud.USE_YM < DATE_ADD(STR_TO_DATE(CONCAT(#{endMonth},'01'),'%Y%m%d'), INTERVAL 1 MONTH)
		]]>
		<if test="custNm != null and custNm != ''">
			AND eud.CUST_NM LIKE CONCAT('%',#{custNm},'%')
		</if>
		<if test="bizrno != null and bizrno != ''">
			AND eud.BIZRNO LIKE CONCAT('%',#{bizrno},'%')
		</if>
		ORDER BY eud.USE_YM DESC, eud.CUST_NM DESC
	</select>

</mapper>