<?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.stats.StatsMapper">

    <select id="selectMonStatList" parameterType="kr.co.uplus.ez.api.stats.dto.MonthListReqDto" resultType="kr.co.uplus.ez.api.stats.dto.MonthList">
        /* stats-mapper.xml(selectMonStatList) */
		SELECT
			SUM_YM AS sumYm,
			A.sndCnt, A.succCnt, IFNULL(ROUND((A.succCnt/A.sndCnt)*100,2),0) AS succRt,
			A.sndCntS, A.succCntS, IFNULL(ROUND((A.succCntS/A.sndCntS)*100,2),0) AS succRtS,
			A.sndCntL, A.succCntL, IFNULL(ROUND((A.succCntL/A.sndCntL)*100,2),0) AS succRtL,
			A.sndCntM, A.succCntM, IFNULL(ROUND((A.succCntM/A.sndCntM)*100,2),0) AS succRtM,
			A.sndCntR, A.succCntR, IFNULL(ROUND((A.succCntR/A.sndCntR)*100,2),0) AS succRtR,
			A.fbSndCntS, A.fbSuccCntS, IFNULL(ROUND((A.fbSuccCntS/A.fbSndCntS)*100,2),0) AS succFbRtS,
			A.fbSndCntL, A.fbSuccCntL, IFNULL(ROUND((A.fbSuccCntL/A.fbSndCntL)*100,2),0) AS succFbRtL,
			A.fbSndCntM, A.fbSuccCntM, IFNULL(ROUND((A.fbSuccCntM/A.fbSndCntM)*100,2),0) AS succFbRtM
		FROM
			(
				SELECT
					DATE_FORMAT(S1.SUM_YM, '%Y-%m') AS SUM_YM,
					(SUM(S1.sndCntS) + SUM(S1.sndCntL) + SUM(S1.sndCntM) + SUM(S1.sndCntR)) AS sndCnt,
					(SUM(S1.succCntS) + SUM(S1.succCntL) +SUM(S1.succCntM) +SUM(S1.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
					SUM(S1.sndCntS) AS sndCntS, SUM(S1.succCntS) AS succCntS, SUM(S1.sndCntL) AS sndCntL, SUM(S1.succCntL) AS succCntL, SUM(S1.sndCntM) AS sndCntM,
					SUM(S1.succCntM) AS succCntM, SUM(S1.sndCntR) AS sndCntR, SUM(S1.succCntR) AS succCntR, SUM(S1.fbSndCntS) AS fbSndCntS, SUM(S1.fbSuccCntS) AS fbSuccCntS,
					SUM(S1.fbSndCntL) AS fbSndCntL, SUM(S1.fbSuccCntL) AS fbSuccCntL, SUM(S1.fbSndCntM) AS fbSndCntM, SUM(S1.fbSuccCntM) AS fbSuccCntM
				FROM
					(
						SELECT
							SUM_YM,
							CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntM,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntR,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntR,
							CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntS,
							CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntS,
							CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntL,
							CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntL,
							CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntM,
							CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntM
						FROM
							hubez_common.EZ_CUST_MSTAT ewm INNER JOIN
							hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
						WHERE
							SUM_YM BETWEEN STR_TO_DATE(CONCAT(#{startMon},'01'),'%Y%m%d')  AND STR_TO_DATE(CONCAT(#{endMon},'01'),'%Y%m%d')
						AND BIZRNO != '1234567890'
						<if test='nowDiv == "Y"'>
						UNION ALL 			
						SELECT
							SUM_YM,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntM,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SND_CNT) ELSE 0 END AS sndCntR,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntR,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntM
						FROM
							(
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-01') AS SUM_YM,
									REPLACE (ewm.CH_STR ,'KKO_NOTI', 'ALIMTALK') AS SND_CH_CD,
									SUM(IFNULL(ewm.SEND_CNT, 0)) AS SND_CNT,
									SUM(IFNULL(ewm.SUCC_CNT, 0)) AS SUCC_CNT,
									0 AS FBACK_CNT,
									0 AS FBACK_SUCC_CNT
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
									INNER JOIN hubez_common.EZ_CUST_INFO eci ON esu.CUST_SEQ = eci.CUST_SEQ
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND BIZRNO != '1234567890'
								GROUP BY ewm.YMD, ewm.CH_STR
								UNION ALL
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-01') AS SUM_YM,
									FB_CH_STR AS SND_CH_CD,
									0 AS SND_CNT,
									0 AS SUCC_CNT,
									SUM(IFNULL(ewm.FB_SUCC_CNT + ewm.FB_FAIL_CNT, 0)) AS FBACK_CNT,
									SUM(IFNULL(ewm.FB_SUCC_CNT, 0)) AS FBACK_SUCC_CNT
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
									INNER JOIN hubez_common.EZ_CUST_INFO eci ON esu.CUST_SEQ = eci.CUST_SEQ
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND BIZRNO != '1234567890'
								AND (FB_SUCC_CNT > 0 OR FB_FAIL_CNT > 0)
								GROUP BY ewm.YMD, ewm.FB_CH_STR
							) T1
						WHERE
							1 = 1
						GROUP BY SUM_YM, SND_CH_CD
						</if>
					) S1
				WHERE
					1 = 1
				GROUP BY S1.SUM_YM
				UNION ALL
				SELECT
					S1.SUM_YM,
					(SUM(S1.sndCntS) + SUM(S1.sndCntL) + SUM(S1.sndCntM) + SUM(S1.sndCntR)) AS sndCnt,
					(SUM(S1.succCntS) + SUM(S1.succCntL) +SUM(S1.succCntM) +SUM(S1.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
					SUM(S1.sndCntS) AS sndCntS, SUM(S1.succCntS) AS succCntS, SUM(S1.sndCntL) AS sndCntL, SUM(S1.succCntL) AS succCntL, SUM(S1.sndCntM) AS sndCntM,
					SUM(S1.succCntM) AS succCntM, SUM(S1.sndCntR) AS sndCntR, SUM(S1.succCntR) AS succCntR, SUM(S1.fbSndCntS) AS fbSndCntS, SUM(S1.fbSuccCntS) AS fbSuccCntS,
					SUM(S1.fbSndCntL) AS fbSndCntL, SUM(S1.fbSuccCntL) AS fbSuccCntL, SUM(S1.fbSndCntM) AS fbSndCntM, SUM(S1.fbSuccCntM) AS fbSuccCntM
				FROM
					(
						SELECT
							'전체' AS SUM_YM,
							CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntM,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntR,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntR,
							CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntS,
							CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntS,
							CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntL,
							CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntL,
							CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntM,
							CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntM
						FROM
							hubez_common.EZ_CUST_MSTAT ewm INNER JOIN
							hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
						WHERE
							SUM_YM BETWEEN STR_TO_DATE(CONCAT(#{startMon},'01'),'%Y%m%d')  AND STR_TO_DATE(CONCAT(#{endMon},'01'),'%Y%m%d')
						AND BIZRNO != '1234567890'
						<if test='nowDiv == "Y"'>
						UNION ALL 			
						SELECT
							'전체' AS SUM_YM,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntM,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SND_CNT) ELSE 0 END AS sndCntR,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntR,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntM
						FROM
							(
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-01') AS SUM_YM,
									REPLACE (ewm.CH_STR ,'KKO_NOTI', 'ALIMTALK') AS SND_CH_CD,
									SUM(IFNULL(ewm.SEND_CNT, 0)) AS SND_CNT,
									SUM(IFNULL(ewm.SUCC_CNT, 0)) AS SUCC_CNT,
									0 AS FBACK_CNT,
									0 AS FBACK_SUCC_CNT
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
									INNER JOIN hubez_common.EZ_CUST_INFO eci ON esu.CUST_SEQ = eci.CUST_SEQ
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND BIZRNO != '1234567890'
								GROUP BY ewm.YMD, ewm.CH_STR
								UNION ALL
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-01') AS SUM_YM,
									FB_CH_STR AS SND_CH_CD,
									0 AS SND_CNT,
									0 AS SUCC_CNT,
									SUM(IFNULL(ewm.FB_SUCC_CNT + ewm.FB_FAIL_CNT, 0)) AS FBACK_CNT,
									SUM(IFNULL(ewm.FB_SUCC_CNT, 0)) AS FBACK_SUCC_CNT
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
									INNER JOIN hubez_common.EZ_CUST_INFO eci ON esu.CUST_SEQ = eci.CUST_SEQ
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND BIZRNO != '1234567890'
								AND (FB_SUCC_CNT > 0 OR FB_FAIL_CNT > 0)
								GROUP BY ewm.YMD, ewm.FB_CH_STR
							) T1
						WHERE
							1 = 1
						GROUP BY SUM_YM, SND_CH_CD
						</if>
					) S1
				WHERE
					1 = 1
				GROUP BY S1.SUM_YM
			) A
		WHERE 1 = 1
		ORDER BY A.SUM_YM DESC
	</select>   
	
	
	<select id="selectDayStatList" parameterType="kr.co.uplus.ez.api.stats.dto.DayListReqDto" resultType="kr.co.uplus.ez.api.stats.dto.DayList">
	        /* stats-mapper.xml(selectDayStatList) */
	        SELECT
				A.SUM_YMD AS sumYmd,
				A.sndCnt, A.succCnt, IFNULL(ROUND((A.succCnt/A.sndCnt)*100,2),0) AS succRt,
				A.sndCntS, A.succCntS, IFNULL(ROUND((A.succCntS/A.sndCntS)*100,2),0) AS succRtS,
				A.sndCntL, A.succCntL, IFNULL(ROUND((A.succCntL/A.sndCntL)*100,2),0) AS succRtL,
				A.sndCntM, A.succCntM, IFNULL(ROUND((A.succCntM/A.sndCntM)*100,2),0) AS succRtM,
				A.sndCntR, A.succCntR, IFNULL(ROUND((A.succCntR/A.sndCntR)*100,2),0) AS succRtR,
				A.fbSndCntS, A.fbSuccCntS, IFNULL(ROUND((A.fbSuccCntS/A.fbSndCntS)*100,2),0) AS succFbRtS,
				A.fbSndCntL, A.fbSuccCntL, IFNULL(ROUND((A.fbSuccCntL/A.fbSndCntL)*100,2),0) AS succFbRtL,
				A.fbSndCntM, A.fbSuccCntM, IFNULL(ROUND((A.fbSuccCntM/A.fbSndCntM)*100,2),0) AS succFbRtM
			FROM
				(
					SELECT
						DATE_FORMAT(S1.SUM_YMD, '%Y-%m-%d') AS SUM_YMD,
						(SUM(S1.sndCntS) + SUM(S1.sndCntL) + SUM(S1.sndCntM) + SUM(S1.sndCntR)) AS sndCnt,
						(SUM(S1.succCntS) + SUM(S1.succCntL) +SUM(S1.succCntM) +SUM(S1.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
						SUM(S1.sndCntS) AS sndCntS, SUM(S1.succCntS) AS succCntS, SUM(S1.sndCntL) AS sndCntL, SUM(S1.succCntL) AS succCntL, SUM(S1.sndCntM) AS sndCntM,
						SUM(S1.succCntM) AS succCntM, SUM(S1.sndCntR) AS sndCntR, SUM(S1.succCntR) AS succCntR, SUM(S1.fbSndCntS) AS fbSndCntS, SUM(S1.fbSuccCntS) AS fbSuccCntS,
						SUM(S1.fbSndCntL) AS fbSndCntL, SUM(S1.fbSuccCntL) AS fbSuccCntL, SUM(S1.fbSndCntM) AS fbSndCntM, SUM(S1.fbSuccCntM) AS fbSuccCntM
					FROM
						(
							SELECT
								SUM_YMD,
								CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntS,
								CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntS,
								CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntL,
								CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntL,
								CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntM,
								CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntM,
								CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntR,
								CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntR,
								CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntS,
								CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntS,
								CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntL,
								CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntL,
								CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntM,
								CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntM
							FROM
								hubez_common.EZ_CUST_DSTAT ewm INNER JOIN
								hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
							WHERE
								SUM_YMD BETWEEN DATE_FORMAT(#{startDay},'%Y%m%d')  AND DATE_FORMAT(#{endDay},'%Y%m%d')
							AND BIZRNO != '1234567890'
							<if test='nowDiv == "Y"'>
							UNION ALL
							SELECT
								SUM_YMD,
								CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntS,
								CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntS,
								CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntL,
								CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntL,
								CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntM,
								CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntM,
								CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SND_CNT) ELSE 0 END AS sndCntR,
								CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntR,
								CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntS,
								CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntS,
								CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntL,
								CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntL,
								CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntM,
								CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntM
							FROM
								(
									SELECT
										DATE_FORMAT(ewm.YMD, '%Y-%m-%d') AS SUM_YMD,
										REPLACE (ewm.CH_STR ,'KKO_NOTI', 'ALIMTALK') AS SND_CH_CD,
										SUM(IFNULL(ewm.SEND_CNT, 0)) AS SND_CNT,
										SUM(IFNULL(ewm.SUCC_CNT, 0)) AS SUCC_CNT,
										0 AS FBACK_CNT,
										0 AS FBACK_SUCC_CNT
									FROM
										hubez_common.EZ_WEB_MSG ewm INNER JOIN
										hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
										INNER JOIN hubez_common.EZ_CUST_INFO eci ON esu.CUST_SEQ = eci.CUST_SEQ
									WHERE
										ewm.YMD = DATE_FORMAT( NOW(), '%Y-%m-%d')
									AND BIZRNO != '1234567890'
									GROUP BY ewm.YMD, ewm.CH_STR
									UNION ALL
									SELECT
										DATE_FORMAT(ewm.YMD, '%Y-%m-%d') AS SUM_YMD,
										FB_CH_STR AS SND_CH_CD,
										0 AS SND_CNT,
										0 AS SUCC_CNT,
										SUM(IFNULL(ewm.FB_SUCC_CNT + ewm.FB_FAIL_CNT, 0)) AS FBACK_CNT,
										SUM(IFNULL(ewm.FB_SUCC_CNT, 0)) AS FBACK_SUCC_CNT
									FROM
										hubez_common.EZ_WEB_MSG ewm INNER JOIN
										hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
										INNER JOIN hubez_common.EZ_CUST_INFO eci ON esu.CUST_SEQ = eci.CUST_SEQ
									WHERE
										ewm.YMD = DATE_FORMAT( NOW(), '%Y-%m-%d')
									AND BIZRNO != '1234567890'
									AND (FB_SUCC_CNT > 0 OR FB_FAIL_CNT > 0)
									GROUP BY ewm.YMD, ewm.FB_CH_STR
								) T1
							WHERE
								1 = 1
							GROUP BY SUM_YMD, SND_CH_CD
							</if>
						) S1
					WHERE
						1 = 1
					GROUP BY S1.SUM_YMD
					UNION ALL
					SELECT
						S1.SUM_YMD AS SUM_YMD,
						(SUM(S1.sndCntS) + SUM(S1.sndCntL) + SUM(S1.sndCntM) + SUM(S1.sndCntR)) AS sndCnt,
						(SUM(S1.succCntS) + SUM(S1.succCntL) +SUM(S1.succCntM) +SUM(S1.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
						SUM(S1.sndCntS) AS sndCntS, SUM(S1.succCntS) AS succCntS, SUM(S1.sndCntL) AS sndCntL, SUM(S1.succCntL) AS succCntL, SUM(S1.sndCntM) AS sndCntM,
						SUM(S1.succCntM) AS succCntM, SUM(S1.sndCntR) AS sndCntR, SUM(S1.succCntR) AS succCntR, SUM(S1.fbSndCntS) AS fbSndCntS, SUM(S1.fbSuccCntS) AS fbSuccCntS,
						SUM(S1.fbSndCntL) AS fbSndCntL, SUM(S1.fbSuccCntL) AS fbSuccCntL, SUM(S1.fbSndCntM) AS fbSndCntM, SUM(S1.fbSuccCntM) AS fbSuccCntM
					FROM
						(
							SELECT
								'전체' AS SUM_YMD,
								CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntS,
								CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntS,
								CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntL,
								CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntL,
								CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntM,
								CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntM,
								CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntR,
								CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntR,
								CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntS,
								CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntS,
								CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntL,
								CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntL,
								CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntM,
								CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntM
							FROM
								hubez_common.EZ_CUST_DSTAT ewm INNER JOIN
								hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
							WHERE
								SUM_YMD BETWEEN DATE_FORMAT(#{startDay},'%Y%m%d')  AND DATE_FORMAT(#{endDay},'%Y%m%d')
							AND BIZRNO != '1234567890'
							<if test='nowDiv == "Y"'>
							UNION ALL
							SELECT
								'전체' AS SUM_YMD,
								CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntS,
								CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntS,
								CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntL,
								CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntL,
								CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntM,
								CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntM,
								CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SND_CNT) ELSE 0 END AS sndCntR,
								CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntR,
								CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntS,
								CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntS,
								CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntL,
								CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntL,
								CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntM,
								CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntM
							FROM
								(
									SELECT
										DATE_FORMAT(ewm.YMD, '%Y-%m-%d') AS SUM_YMD,
										REPLACE (ewm.CH_STR ,'KKO_NOTI', 'ALIMTALK') AS SND_CH_CD,
										SUM(IFNULL(ewm.SEND_CNT, 0)) AS SND_CNT,
										SUM(IFNULL(ewm.SUCC_CNT, 0)) AS SUCC_CNT,
										0 AS FBACK_CNT,
										0 AS FBACK_SUCC_CNT
									FROM
										hubez_common.EZ_WEB_MSG ewm INNER JOIN
										hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
										INNER JOIN hubez_common.EZ_CUST_INFO eci ON esu.CUST_SEQ = eci.CUST_SEQ
									WHERE
										ewm.YMD = DATE_FORMAT( NOW(), '%Y-%m-%d')
									AND BIZRNO != '1234567890'
									GROUP BY ewm.YMD, ewm.CH_STR
									UNION ALL
									SELECT
										DATE_FORMAT(ewm.YMD, '%Y-%m-%d') AS SUM_YMD,
										FB_CH_STR AS SND_CH_CD,
										0 AS SND_CNT,
										0 AS SUCC_CNT,
										SUM(IFNULL(ewm.FB_SUCC_CNT + ewm.FB_FAIL_CNT, 0)) AS FBACK_CNT,
										SUM(IFNULL(ewm.FB_SUCC_CNT, 0)) AS FBACK_SUCC_CNT
									FROM
										hubez_common.EZ_WEB_MSG ewm INNER JOIN
										hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ
										INNER JOIN hubez_common.EZ_CUST_INFO eci ON esu.CUST_SEQ = eci.CUST_SEQ
									WHERE
										ewm.YMD = DATE_FORMAT( NOW(), '%Y-%m-%d')
									AND BIZRNO != '1234567890'
									AND (FB_SUCC_CNT > 0 OR FB_FAIL_CNT > 0)
									GROUP BY ewm.YMD, ewm.FB_CH_STR
								) T1
							WHERE
								1 = 1
							GROUP BY SUM_YMD, SND_CH_CD
							</if>
						) S1
					WHERE
						1 = 1
					GROUP BY S1.SUM_YMD
				) A
			WHERE
				1 = 1
			ORDER BY A.SUM_YMD DESC

	 </select>   

    <select id="selectCustMstatListTotalCnt" parameterType="kr.co.uplus.ez.api.stats.dto.BsnmMonthList" resultType="int">
        /* stats-mapper.xml(selectCustMstatListTotalCnt) */
    	SELECT
			COUNT(A.SUM_YM) AS TOTALCNT
		FROM
			(
				SELECT
					S1.SUM_YM, S1.USER_SEQ,
					(SUM(S1.sndCntS) + SUM(S1.sndCntL) + SUM(S1.sndCntM) + SUM(S1.sndCntR)) AS sndCnt,
					(SUM(S1.succCntS) + SUM(S1.succCntL) +SUM(S1.succCntM) +SUM(S1.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
					SUM(S1.sndCntS) AS sndCntS, SUM(S1.succCntS) AS succCntS, SUM(S1.sndCntL) AS sndCntL, SUM(S1.succCntL) AS succCntL, SUM(S1.sndCntM) AS sndCntM,
					SUM(S1.succCntM) AS succCntM, SUM(S1.sndCntR) AS sndCntR, SUM(S1.succCntR) AS succCntR, SUM(S1.fbSndCntS) AS fbSndCntS, SUM(S1.fbSuccCntS) AS fbSuccCntS,
					SUM(S1.fbSndCntL) AS fbSndCntL, SUM(S1.fbSuccCntL) AS fbSuccCntL, SUM(S1.fbSndCntM) AS fbSndCntM, SUM(S1.fbSuccCntM) AS fbSuccCntM
				FROM
					(
						SELECT
							SUM_YM, USER_SEQ,
							CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntM,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntR,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntR,
							CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntS,
							CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntS,
							CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntL,
							CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntL,
							CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntM,
							CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntM
						FROM
							hubez_common.EZ_CUST_MSTAT
						WHERE
							SUM_YM BETWEEN STR_TO_DATE(CONCAT(#{startMon},'01'),'%Y%m%d')  AND STR_TO_DATE(CONCAT(#{endMon},'01'),'%Y%m%d')
						AND BIZRNO != '1234567890'
					) S1
				WHERE
					1 = 1
				GROUP BY S1.SUM_YM, S1.USER_SEQ
				<if test='nowDiv == "Y"'>
				UNION ALL
				SELECT
					S2.SUM_YM, S2.USER_SEQ,
					(SUM(S2.sndCntS) + SUM(S2.sndCntL) + SUM(S2.sndCntM) + SUM(S2.sndCntR)) AS sndCnt,
					(SUM(S2.succCntS) + SUM(S2.succCntL) +SUM(S2.succCntM) +SUM(S2.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
					SUM(S2.sndCntS) AS sndCntS, SUM(S2.succCntS) AS succCntS, SUM(S2.sndCntL) AS sndCntL, SUM(S2.succCntL) AS succCntL, SUM(S2.sndCntM) AS sndCntM,
					SUM(S2.succCntM) AS succCntM, SUM(S2.sndCntR) AS sndCntR, SUM(S2.succCntR) AS succCntR, SUM(S2.fbSndCntS) AS fbSndCntS, SUM(S2.fbSuccCntS) AS fbSuccCntS,
					SUM(S2.fbSndCntL) AS fbSndCntL, SUM(S2.fbSuccCntL) AS fbSuccCntL, SUM(S2.fbSndCntM) AS fbSndCntM, SUM(S2.fbSuccCntM) AS fbSuccCntM
				FROM
					(
						SELECT
							SUM_YM, USER_SEQ,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntM,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SND_CNT) ELSE 0 END AS sndCntR,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntR,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntM
						FROM
							(
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-01') AS SUM_YM,
									esu.PRNTS_USER_SEQ AS USER_SEQ,
									REPLACE (ewm.CH_STR ,'KKO_NOTI', 'ALIMTALK') AS SND_CH_CD,
									SUM(IFNULL(ewm.SEND_CNT, 0)) AS SND_CNT,
									SUM(IFNULL(ewm.SUCC_CNT, 0)) AS SUCC_CNT,
									0 AS FBACK_CNT,
									0 AS FBACK_SUCC_CNT
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.CH_STR
								UNION ALL
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-01') AS SUM_YM,
									esu.PRNTS_USER_SEQ AS USER_SEQ,
									FB_CH_STR AS SND_CH_CD,
									0 AS SND_CNT,
									0 AS SUCC_CNT,
									SUM(IFNULL(ewm.FB_SUCC_CNT + ewm.FB_FAIL_CNT, 0)) AS FBACK_CNT,
									SUM(IFNULL(ewm.FB_SUCC_CNT, 0)) AS FBACK_SUCC_CNT
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND (FB_SUCC_CNT > 0 OR FB_FAIL_CNT > 0)
								GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.FB_CH_STR
							) T1
						WHERE
							1 = 1
						GROUP BY SUM_YM, USER_SEQ, SND_CH_CD
					) S2
				WHERE
					1 = 1
				GROUP BY S2.SUM_YM, S2.USER_SEQ
				</if>
			) A,
			hubez_common.EZ_SUBS_INFO B,
			hubez_common.EZ_CUST_INFO C
		WHERE
			A.USER_SEQ 	= B.USER_SEQ
		AND B.CUST_SEQ = C.CUST_SEQ
		AND C.BIZRNO 	!= '1234567890'
		<include refid="custMstatListCondition"/>
    </select>

    <select id="selectCustMstatList" parameterType="kr.co.uplus.ez.api.stats.dto.BsnmMonthList" resultType="kr.co.uplus.ez.api.stats.dto.BsnmMonthList">
        /* stats-mapper.xml(selectCustMstatList) */
		SELECT
			DATE_FORMAT(A.SUM_YM, '%Y-%m') AS sumYm, C.CUST_NM AS custNm, C.BIZRNO AS bizrno,
			A.sndCnt, A.succCnt, IFNULL(ROUND((A.succCnt/A.sndCnt)*100,2),0) AS succRt,
			A.sndCntS, A.succCntS, IFNULL(ROUND((A.succCntS/A.sndCntS)*100,2),0) AS succRtS,
			A.sndCntL, A.succCntL, IFNULL(ROUND((A.succCntL/A.sndCntL)*100,2),0) AS succRtL,
			A.sndCntM, A.succCntM, IFNULL(ROUND((A.succCntM/A.sndCntM)*100,2),0) AS succRtM,
			A.sndCntR, A.succCntR, IFNULL(ROUND((A.succCntR/A.sndCntR)*100,2),0) AS succRtR,
			A.fbSndCntS, A.fbSuccCntS, IFNULL(ROUND((A.fbSuccCntS/A.fbSndCntS)*100,2),0) AS succFbRtS,
			A.fbSndCntL, A.fbSuccCntL, IFNULL(ROUND((A.fbSuccCntL/A.fbSndCntL)*100,2),0) AS succFbRtL,
			A.fbSndCntM, A.fbSuccCntM, IFNULL(ROUND((A.fbSuccCntM/A.fbSndCntM)*100,2),0) AS succFbRtM
		FROM
			(
				SELECT
					S1.SUM_YM, S1.USER_SEQ,
					(SUM(S1.sndCntS) + SUM(S1.sndCntL) + SUM(S1.sndCntM) + SUM(S1.sndCntR)) AS sndCnt,
					(SUM(S1.succCntS) + SUM(S1.succCntL) +SUM(S1.succCntM) +SUM(S1.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
					SUM(S1.sndCntS) AS sndCntS, SUM(S1.succCntS) AS succCntS, SUM(S1.sndCntL) AS sndCntL, SUM(S1.succCntL) AS succCntL, SUM(S1.sndCntM) AS sndCntM,
					SUM(S1.succCntM) AS succCntM, SUM(S1.sndCntR) AS sndCntR, SUM(S1.succCntR) AS succCntR, SUM(S1.fbSndCntS) AS fbSndCntS, SUM(S1.fbSuccCntS) AS fbSuccCntS,
					SUM(S1.fbSndCntL) AS fbSndCntL, SUM(S1.fbSuccCntL) AS fbSuccCntL, SUM(S1.fbSndCntM) AS fbSndCntM, SUM(S1.fbSuccCntM) AS fbSuccCntM
				FROM
					(
						SELECT
							SUM_YM, USER_SEQ,
							CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntM,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntR,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntR,
							CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntS,
							CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntS,
							CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntL,
							CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntL,
							CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntM,
							CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntM
						FROM
							hubez_common.EZ_CUST_MSTAT
						WHERE
							SUM_YM BETWEEN STR_TO_DATE(CONCAT(#{startMon},'01'),'%Y%m%d')  AND STR_TO_DATE(CONCAT(#{endMon},'01'),'%Y%m%d')
						AND BIZRNO != '1234567890'
					) S1
				WHERE
					1 = 1
				GROUP BY S1.SUM_YM, S1.USER_SEQ
				<if test='nowDiv == "Y"'>
				UNION ALL
				SELECT
					S2.SUM_YM, S2.USER_SEQ,
					(SUM(S2.sndCntS) + SUM(S2.sndCntL) + SUM(S2.sndCntM) + SUM(S2.sndCntR)) AS sndCnt,
					(SUM(S2.succCntS) + SUM(S2.succCntL) +SUM(S2.succCntM) +SUM(S2.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
					SUM(S2.sndCntS) AS sndCntS, SUM(S2.succCntS) AS succCntS, SUM(S2.sndCntL) AS sndCntL, SUM(S2.succCntL) AS succCntL, SUM(S2.sndCntM) AS sndCntM,
					SUM(S2.succCntM) AS succCntM, SUM(S2.sndCntR) AS sndCntR, SUM(S2.succCntR) AS succCntR, SUM(S2.fbSndCntS) AS fbSndCntS, SUM(S2.fbSuccCntS) AS fbSuccCntS,
					SUM(S2.fbSndCntL) AS fbSndCntL, SUM(S2.fbSuccCntL) AS fbSuccCntL, SUM(S2.fbSndCntM) AS fbSndCntM, SUM(S2.fbSuccCntM) AS fbSuccCntM
				FROM
					(
						SELECT
							SUM_YM, USER_SEQ,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntM,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SND_CNT) ELSE 0 END AS sndCntR,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntR,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntM
						FROM
							(
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-01') AS SUM_YM,
									esu.PRNTS_USER_SEQ AS USER_SEQ,
									REPLACE (ewm.CH_STR ,'KKO_NOTI', 'ALIMTALK') AS SND_CH_CD,
									SUM(IFNULL(ewm.SEND_CNT, 0)) AS SND_CNT,
									SUM(IFNULL(ewm.SUCC_CNT, 0)) AS SUCC_CNT,
									0 AS FBACK_CNT,
									0 AS FBACK_SUCC_CNT
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.CH_STR
								UNION ALL
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-01') AS SUM_YM,
									esu.PRNTS_USER_SEQ AS USER_SEQ,
									FB_CH_STR AS SND_CH_CD,
									0 AS SND_CNT,
									0 AS SUCC_CNT,
									SUM(IFNULL(ewm.FB_SUCC_CNT + ewm.FB_FAIL_CNT, 0)) AS FBACK_CNT,
									SUM(IFNULL(ewm.FB_SUCC_CNT, 0)) AS FBACK_SUCC_CNT
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND (FB_SUCC_CNT > 0 OR FB_FAIL_CNT > 0)
								GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.FB_CH_STR
							) T1
						WHERE
							1 = 1
						GROUP BY SUM_YM, USER_SEQ, SND_CH_CD
					) S2
				WHERE
					1 = 1
				GROUP BY S2.SUM_YM, S2.USER_SEQ
				</if>
			) A,
			hubez_common.EZ_SUBS_INFO B,
			hubez_common.EZ_CUST_INFO C
		WHERE
			A.USER_SEQ 	= B.USER_SEQ
		AND B.CUST_SEQ = C.CUST_SEQ
		AND C.BIZRNO 	!= '1234567890'
		<include refid="custMstatListCondition"/>
		ORDER BY A.SUM_YM DESC, C.CUST_NM
		LIMIT #{page}, #{pagePerRows}
    </select>

    <select id="selectCustMstatExcelList" parameterType="kr.co.uplus.ez.api.stats.dto.BsnmMonthListExcelReqDto" resultType="kr.co.uplus.ez.api.stats.dto.BsnmMonthList">
        /* stats-mapper.xml(selectCustMstatExcelList) */
        SELECT
        	sumYm, custNm, bizrno,
        	sndCnt, CONCAT(succCnt,'(',succRt,'%)') AS succCnt,
        	sndCntS, CONCAT(succCntS,'(',succRtS,'%)') AS succCntS,
        	sndCntL, CONCAT(succCntL,'(',succRtL,'%)') AS succCntL,
        	sndCntM, CONCAT(succCntM,'(',succRtM,'%)') AS succCntM,
        	sndCntR, CONCAT(succCntR,'(',succRtR,'%)') AS succCntR,
        	fbSndCntS, CONCAT(fbSuccCntS,'(',succFbRtS,'%)') AS fbSuccCntS,
        	fbSndCntL, CONCAT(fbSuccCntL,'(',succFbRtL,'%)') AS fbSuccCntL,
        	fbSndCntM, CONCAT(fbSuccCntM,'(',succFbRtM,'%)') AS fbSuccCntM
        FROM
        	(
		        SELECT
					DATE_FORMAT(A.SUM_YM, '%Y-%m') AS sumYm, C.CUST_NM AS custNm, C.BIZRNO AS bizrno,
					A.sndCnt, A.succCnt, IFNULL(ROUND((A.succCnt/A.sndCnt)*100,2), 0) AS succRt,
					A.sndCntS, A.succCntS, IFNULL(ROUND((A.succCntS/A.sndCntS)*100,2), 0) AS succRtS,
					A.sndCntL, A.succCntL, IFNULL(ROUND((A.succCntL/A.sndCntL)*100,2), 0) AS succRtL,
					A.sndCntM, A.succCntM, IFNULL(ROUND((A.succCntM/A.sndCntM)*100,2), 0) AS succRtM,
					A.sndCntR, A.succCntR, IFNULL(ROUND((A.succCntR/A.sndCntR)*100,2), 0) AS succRtR,
					A.fbSndCntS, A.fbSuccCntS, IFNULL(ROUND((A.fbSuccCntS/A.fbSndCntS)*100,2), 0) AS succFbRtS,
					A.fbSndCntL, A.fbSuccCntL, IFNULL(ROUND((A.fbSuccCntL/A.fbSndCntL)*100,2), 0) AS succFbRtL,
					A.fbSndCntM, A.fbSuccCntM, IFNULL(ROUND((A.fbSuccCntM/A.fbSndCntM)*100,2), 0) AS succFbRtM
				FROM
					(
						SELECT
							S1.SUM_YM, S1.USER_SEQ,
							(SUM(S1.sndCntS) + SUM(S1.sndCntL) + SUM(S1.sndCntM) + SUM(S1.sndCntR)) AS sndCnt,
							(SUM(S1.succCntS) + SUM(S1.succCntL) +SUM(S1.succCntM) +SUM(S1.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
							SUM(S1.sndCntS) AS sndCntS, SUM(S1.succCntS) AS succCntS, SUM(S1.sndCntL) AS sndCntL, SUM(S1.succCntL) AS succCntL, SUM(S1.sndCntM) AS sndCntM,
							SUM(S1.succCntM) AS succCntM, SUM(S1.sndCntR) AS sndCntR, SUM(S1.succCntR) AS succCntR, SUM(S1.fbSndCntS) AS fbSndCntS, SUM(S1.fbSuccCntS) AS fbSuccCntS,
							SUM(S1.fbSndCntL) AS fbSndCntL, SUM(S1.fbSuccCntL) AS fbSuccCntL, SUM(S1.fbSndCntM) AS fbSndCntM, SUM(S1.fbSuccCntM) AS fbSuccCntM
						FROM
							(
								SELECT
									SUM_YM, USER_SEQ,
									CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntS,
									CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntS,
									CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntL,
									CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntL,
									CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntM,
									CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntM,
									CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntR,
									CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntR,
									CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntS,
									CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntS,
									CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntL,
									CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntL,
									CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntM,
									CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntM
								FROM
									hubez_common.EZ_CUST_MSTAT
								WHERE
									SUM_YM BETWEEN STR_TO_DATE(CONCAT(#{startMon},'01'),'%Y%m%d')  AND STR_TO_DATE(CONCAT(#{endMon},'01'),'%Y%m%d')
								AND BIZRNO != '1234567890'
							) S1
						WHERE
							1 = 1
						GROUP BY S1.SUM_YM, S1.USER_SEQ
						<if test='nowDiv == "Y"'>
						UNION ALL
						SELECT
							S2.SUM_YM, S2.USER_SEQ,
							(SUM(S2.sndCntS) + SUM(S2.sndCntL) + SUM(S2.sndCntM) + SUM(S2.sndCntR)) AS sndCnt,
							(SUM(S2.succCntS) + SUM(S2.succCntL) +SUM(S2.succCntM) +SUM(S2.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
							SUM(S2.sndCntS) AS sndCntS, SUM(S2.succCntS) AS succCntS, SUM(S2.sndCntL) AS sndCntL, SUM(S2.succCntL) AS succCntL, SUM(S2.sndCntM) AS sndCntM,
							SUM(S2.succCntM) AS succCntM, SUM(S2.sndCntR) AS sndCntR, SUM(S2.succCntR) AS succCntR, SUM(S2.fbSndCntS) AS fbSndCntS, SUM(S2.fbSuccCntS) AS fbSuccCntS,
							SUM(S2.fbSndCntL) AS fbSndCntL, SUM(S2.fbSuccCntL) AS fbSuccCntL, SUM(S2.fbSndCntM) AS fbSndCntM, SUM(S2.fbSuccCntM) AS fbSuccCntM
						FROM
							(
								SELECT
									SUM_YM, USER_SEQ,
									CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntS,
									CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntS,
									CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntL,
									CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntL,
									CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntM,
									CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntM,
									CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SND_CNT) ELSE 0 END AS sndCntR,
									CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntR,
									CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntS,
									CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntS,
									CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntL,
									CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntL,
									CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntM,
									CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntM
								FROM
									(
										SELECT
											DATE_FORMAT(ewm.YMD, '%Y-%m-01') AS SUM_YM,
											esu.PRNTS_USER_SEQ AS USER_SEQ,
											REPLACE (ewm.CH_STR ,'KKO_NOTI', 'ALIMTALK') AS SND_CH_CD,
											SUM(IFNULL(ewm.SEND_CNT, 0)) AS SND_CNT,
											SUM(IFNULL(ewm.SUCC_CNT, 0)) AS SUCC_CNT,
											0 AS FBACK_CNT,
											0 AS FBACK_SUCC_CNT
										FROM
											hubez_common.EZ_WEB_MSG ewm INNER JOIN
											hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
										WHERE
											ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
										GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.CH_STR
										UNION ALL
										SELECT
											DATE_FORMAT(ewm.YMD, '%Y-%m-01') AS SUM_YM,
											esu.PRNTS_USER_SEQ AS USER_SEQ,
											FB_CH_STR AS SND_CH_CD,
											0 AS SND_CNT,
											0 AS SUCC_CNT,
											SUM(IFNULL(ewm.FB_SUCC_CNT + ewm.FB_FAIL_CNT, 0)) AS FBACK_CNT,
											SUM(IFNULL(ewm.FB_SUCC_CNT, 0)) AS FBACK_SUCC_CNT
										FROM
											hubez_common.EZ_WEB_MSG ewm INNER JOIN
											hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
										WHERE
											ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
										AND (FB_SUCC_CNT > 0 OR FB_FAIL_CNT > 0)
										GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.FB_CH_STR
									) T1
								WHERE
									1 = 1
								GROUP BY SUM_YM, USER_SEQ, SND_CH_CD
							) S2
						WHERE
							1 = 1
						GROUP BY S2.SUM_YM, S2.USER_SEQ
						</if>
					) A,
					hubez_common.EZ_SUBS_INFO B,
					hubez_common.EZ_CUST_INFO C
				WHERE
					A.USER_SEQ 	= B.USER_SEQ
				AND B.CUST_SEQ = C.CUST_SEQ
				AND C.BIZRNO 	!= '1234567890'
				<include refid="custMstatListCondition"/>
			) M
		WHERE
			1 = 1
		ORDER BY M.sumYm DESC, M.custNm
    </select>

    <select id="selectCustDstatListTotalCnt" parameterType="kr.co.uplus.ez.api.stats.dto.BsnmDayListReqDto" resultType="int">
        /* stats-mapper.xml(selectCustDstatListTotalCnt) */
    	SELECT 
			COUNT(SUM_YMD) AS TOTALCNT
		FROM
			(
				SELECT
					S1.SUM_YMD, S1.USER_SEQ
				FROM
					(
						SELECT
							SUM_YMD, USER_SEQ
						FROM
							hubez_common.EZ_CUST_DSTAT
						WHERE
							SUM_YMD BETWEEN DATE_FORMAT(#{startDay},'%Y%m%d')  AND DATE_FORMAT(#{endDay},'%Y%m%d')
						AND BIZRNO != '1234567890'
					) S1
				WHERE
					1 = 1
				GROUP BY S1.SUM_YMD, S1.USER_SEQ
				<if test='nowDiv == "Y"'>
				UNION ALL
				SELECT
					S2.SUM_YMD, S2.USER_SEQ
				FROM
					(
						SELECT
							SUM_YMD, USER_SEQ
						FROM
							(
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-%d') AS SUM_YMD,
									esu.PRNTS_USER_SEQ AS USER_SEQ,
									REPLACE (ewm.CH_STR ,'KKO_NOTI', 'ALIMTALK') AS SND_CH_CD
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
								WHERE
									ewm.YMD = DATE_FORMAT( NOW(), '%Y-%m-%d')
								GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.CH_STR
								UNION ALL
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-%d') AS SUM_YMD,
									esu.PRNTS_USER_SEQ AS USER_SEQ,
									FB_CH_STR AS SND_CH_CD
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
								WHERE
									ewm.YMD = DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND (FB_SUCC_CNT > 0 OR FB_FAIL_CNT > 0)
								GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.FB_CH_STR
							) T1
						WHERE
							1 = 1
						GROUP BY SUM_YMD, USER_SEQ, SND_CH_CD
					) S2
				WHERE
					1 = 1
				GROUP BY S2.SUM_YMD, S2.USER_SEQ
				</if>
			) A,
			hubez_common.EZ_SUBS_INFO B,
			hubez_common.EZ_CUST_INFO C
		WHERE
			A.USER_SEQ 	= B.USER_SEQ
		AND B.CUST_SEQ = C.CUST_SEQ
		AND C.BIZRNO 	!= '1234567890'
		<include refid="custMstatListCondition"/>
    </select>

    <select id="selectCustDstatList" parameterType="kr.co.uplus.ez.api.stats.dto.BsnmDayListReqDto" resultType="kr.co.uplus.ez.api.stats.dto.BsnmDayList">
        /* stats-mapper.xml(selectCustDstatList) */
		SELECT
			DATE_FORMAT(A.SUM_YMD, '%Y-%m-%d') AS sumYmd, C.CUST_NM AS custNm, C.BIZRNO AS bizrno,
			A.sndCnt, A.succCnt, IFNULL(ROUND((A.succCnt/A.sndCnt)*100,2),0) AS succRt,
			A.sndCntS, A.succCntS, IFNULL(ROUND((A.succCntS/A.sndCntS)*100,2),0) AS succRtS,
			A.sndCntL, A.succCntL, IFNULL(ROUND((A.succCntL/A.sndCntL)*100,2),0) AS succRtL,
			A.sndCntM, A.succCntM, IFNULL(ROUND((A.succCntM/A.sndCntM)*100,2),0) AS succRtM,
			A.sndCntR, A.succCntR, IFNULL(ROUND((A.succCntR/A.sndCntR)*100,2),0) AS succRtR,
			A.fbSndCntS, A.fbSuccCntS, IFNULL(ROUND((A.fbSuccCntS/A.fbSndCntS)*100,2),0) AS succFbRtS,
			A.fbSndCntL, A.fbSuccCntL, IFNULL(ROUND((A.fbSuccCntL/A.fbSndCntL)*100,2),0) AS succFbRtL,
			A.fbSndCntM, A.fbSuccCntM, IFNULL(ROUND((A.fbSuccCntM/A.fbSndCntM)*100,2),0) AS succFbRtM
		FROM
			(
				SELECT
					S1.SUM_YMD, S1.USER_SEQ,
					(SUM(S1.sndCntS) + SUM(S1.sndCntL) + SUM(S1.sndCntM) + SUM(S1.sndCntR)) AS sndCnt,
					(SUM(S1.succCntS) + SUM(S1.succCntL) +SUM(S1.succCntM) +SUM(S1.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
					SUM(S1.sndCntS) AS sndCntS, SUM(S1.succCntS) AS succCntS, SUM(S1.sndCntL) AS sndCntL, SUM(S1.succCntL) AS succCntL, SUM(S1.sndCntM) AS sndCntM,
					SUM(S1.succCntM) AS succCntM, SUM(S1.sndCntR) AS sndCntR, SUM(S1.succCntR) AS succCntR, SUM(S1.fbSndCntS) AS fbSndCntS, SUM(S1.fbSuccCntS) AS fbSuccCntS,
					SUM(S1.fbSndCntL) AS fbSndCntL, SUM(S1.fbSuccCntL) AS fbSuccCntL, SUM(S1.fbSndCntM) AS fbSndCntM, SUM(S1.fbSuccCntM) AS fbSuccCntM
				FROM
					(
						SELECT
							SUM_YMD, USER_SEQ,
							CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntM,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntR,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntR,
							CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntS,
							CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntS,
							CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntL,
							CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntL,
							CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntM,
							CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntM
						FROM
							hubez_common.EZ_CUST_DSTAT
						WHERE
							SUM_YMD BETWEEN DATE_FORMAT(#{startDay},'%Y%m%d')  AND DATE_FORMAT(#{endDay},'%Y%m%d')
						AND BIZRNO != '1234567890'
					) S1
				WHERE
					1 = 1
				GROUP BY S1.SUM_YMD, S1.USER_SEQ
				<if test='nowDiv == "Y"'>
				UNION ALL
				SELECT
					S2.SUM_YMD, S2.USER_SEQ,
					(SUM(S2.sndCntS) + SUM(S2.sndCntL) + SUM(S2.sndCntM) + SUM(S2.sndCntR)) AS sndCnt,
					(SUM(S2.succCntS) + SUM(S2.succCntL) +SUM(S2.succCntM) +SUM(S2.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
					SUM(S2.sndCntS) AS sndCntS, SUM(S2.succCntS) AS succCntS, SUM(S2.sndCntL) AS sndCntL, SUM(S2.succCntL) AS succCntL, SUM(S2.sndCntM) AS sndCntM,
					SUM(S2.succCntM) AS succCntM, SUM(S2.sndCntR) AS sndCntR, SUM(S2.succCntR) AS succCntR, SUM(S2.fbSndCntS) AS fbSndCntS, SUM(S2.fbSuccCntS) AS fbSuccCntS,
					SUM(S2.fbSndCntL) AS fbSndCntL, SUM(S2.fbSuccCntL) AS fbSuccCntL, SUM(S2.fbSndCntM) AS fbSndCntM, SUM(S2.fbSuccCntM) AS fbSuccCntM
				FROM
					(
						SELECT
							SUM_YMD, USER_SEQ,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntM,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SND_CNT) ELSE 0 END AS sndCntR,
							CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntR,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntS,
							CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntS,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntL,
							CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntL,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntM,
							CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntM
						FROM
							(
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-%d') AS SUM_YMD,
									esu.PRNTS_USER_SEQ AS USER_SEQ,
									REPLACE (ewm.CH_STR ,'KKO_NOTI', 'ALIMTALK') AS SND_CH_CD,
									SUM(IFNULL(ewm.SEND_CNT, 0)) AS SND_CNT,
									SUM(IFNULL(ewm.SUCC_CNT, 0)) AS SUCC_CNT,
									0 AS FBACK_CNT,
									0 AS FBACK_SUCC_CNT
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
								WHERE
									ewm.YMD = DATE_FORMAT( NOW(), '%Y-%m-%d')
								GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.CH_STR
								UNION ALL
								SELECT
									DATE_FORMAT(ewm.YMD, '%Y-%m-%d') AS SUM_YMD,
									esu.PRNTS_USER_SEQ AS USER_SEQ,
									FB_CH_STR AS SND_CH_CD,
									0 AS SND_CNT,
									0 AS SUCC_CNT,
									SUM(IFNULL(ewm.FB_SUCC_CNT + ewm.FB_FAIL_CNT, 0)) AS FBACK_CNT,
									SUM(IFNULL(ewm.FB_SUCC_CNT, 0)) AS FBACK_SUCC_CNT
								FROM
									hubez_common.EZ_WEB_MSG ewm INNER JOIN
									hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
								WHERE
									ewm.YMD = DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND (FB_SUCC_CNT > 0 OR FB_FAIL_CNT > 0)
								GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.FB_CH_STR
							) T1
						WHERE
							1 = 1
						GROUP BY SUM_YMD, USER_SEQ, SND_CH_CD
					) S2
				WHERE
					1 = 1
				GROUP BY S2.SUM_YMD, S2.USER_SEQ
				</if>
			) A,
			hubez_common.EZ_SUBS_INFO B,
			hubez_common.EZ_CUST_INFO C
		WHERE
			A.USER_SEQ 	= B.USER_SEQ
		AND B.CUST_SEQ = C.CUST_SEQ
		AND C.BIZRNO 	!= '1234567890'
		<include refid="custMstatListCondition"/>
		ORDER BY A.SUM_YMD DESC, C.CUST_NM
		LIMIT #{page}, #{pagePerRows}
    </select>

    <select id="selectCustDstatExcelList" parameterType="kr.co.uplus.ez.api.stats.dto.BsnmDayListExcelReqDto" resultType="kr.co.uplus.ez.api.stats.dto.BsnmDayList">
        /* stats-mapper.xml(selectCustDstatExcelList) */
        SELECT
			sumYmd, custNm, bizrno,
			sndCnt, CONCAT(succCnt,'(',succRt,'%)') AS succCnt,
			sndCntS, CONCAT(succCntS,'(',succRtS,'%)') AS succCntS,
			sndCntL, CONCAT(succCntL,'(',succRtL,'%)') AS succCntL,
			sndCntM, CONCAT(succCntM,'(',succRtM,'%)') AS succCntM,
			sndCntR, CONCAT(succCntR,'(',succRtR,'%)') AS succCntR,
			fbSndCntS, CONCAT(fbSuccCntS,'(',succFbRtS,'%)') AS fbSuccCntS,
			fbSndCntL, CONCAT(fbSuccCntL,'(',succFbRtL,'%)') AS fbSuccCntL,
			fbSndCntM, CONCAT(fbSuccCntM,'(',succFbRtM,'%)') AS fbSuccCntM
		FROM
			(
				SELECT
					DATE_FORMAT(A.SUM_YMD, '%Y-%m-%d') AS sumYmd, C.CUST_NM AS custNm, C.BIZRNO AS bizrno,
					A.sndCnt, A.succCnt, IFNULL(ROUND((A.succCnt/A.sndCnt)*100,2),0) AS succRt,
					A.sndCntS, A.succCntS, IFNULL(ROUND((A.succCntS/A.sndCntS)*100,2),0) AS succRtS,
					A.sndCntL, A.succCntL, IFNULL(ROUND((A.succCntL/A.sndCntL)*100,2),0) AS succRtL,
					A.sndCntM, A.succCntM, IFNULL(ROUND((A.succCntM/A.sndCntM)*100,2),0) AS succRtM,
					A.sndCntR, A.succCntR, IFNULL(ROUND((A.succCntR/A.sndCntR)*100,2),0) AS succRtR,
					A.fbSndCntS, A.fbSuccCntS, IFNULL(ROUND((A.fbSuccCntS/A.fbSndCntS)*100,2),0) AS succFbRtS,
					A.fbSndCntL, A.fbSuccCntL, IFNULL(ROUND((A.fbSuccCntL/A.fbSndCntL)*100,2),0) AS succFbRtL,
					A.fbSndCntM, A.fbSuccCntM, IFNULL(ROUND((A.fbSuccCntM/A.fbSndCntM)*100,2),0) AS succFbRtM
				FROM
					(
						SELECT
							S1.SUM_YMD, S1.USER_SEQ,
							(SUM(S1.sndCntS) + SUM(S1.sndCntL) + SUM(S1.sndCntM) + SUM(S1.sndCntR)) AS sndCnt,
							(SUM(S1.succCntS) + SUM(S1.succCntL) +SUM(S1.succCntM) +SUM(S1.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
							SUM(S1.sndCntS) AS sndCntS, SUM(S1.succCntS) AS succCntS, SUM(S1.sndCntL) AS sndCntL, SUM(S1.succCntL) AS succCntL, SUM(S1.sndCntM) AS sndCntM,
							SUM(S1.succCntM) AS succCntM, SUM(S1.sndCntR) AS sndCntR, SUM(S1.succCntR) AS succCntR, SUM(S1.fbSndCntS) AS fbSndCntS, SUM(S1.fbSuccCntS) AS fbSuccCntS,
							SUM(S1.fbSndCntL) AS fbSndCntL, SUM(S1.fbSuccCntL) AS fbSuccCntL, SUM(S1.fbSndCntM) AS fbSndCntM, SUM(S1.fbSuccCntM) AS fbSuccCntM
						FROM
							(
								SELECT
									SUM_YMD, USER_SEQ,
									CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntS,
									CASE WHEN SND_CH_CD = 'SMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntS,
									CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntL,
									CASE WHEN SND_CH_CD = 'LMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntL,
									CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntM,
									CASE WHEN SND_CH_CD = 'MMS' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntM,
									CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SND_CNT, 0) ELSE 0 END AS sndCntR,
									CASE WHEN SND_CH_CD = 'ALIMTALK' THEN IFNULL(SUCC_CNT, 0) ELSE 0 END AS succCntR,
									CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntS,
									CASE WHEN RPLCSND_CH_CD = 'SMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntS,
									CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntL,
									CASE WHEN RPLCSND_CH_CD = 'LMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntL,
									CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_CNT, 0) ELSE 0 END AS fbSndCntM,
									CASE WHEN RPLCSND_CH_CD = 'MMS' THEN IFNULL(FBACK_SUCC_CNT, 0) ELSE 0 END AS fbSuccCntM
								FROM
									hubez_common.EZ_CUST_DSTAT
								WHERE
									SUM_YMD BETWEEN DATE_FORMAT(#{startDay},'%Y%m%d')  AND DATE_FORMAT(#{endDay},'%Y%m%d')
								AND BIZRNO != '1234567890'
							) S1
						WHERE
							1 = 1
						GROUP BY S1.SUM_YMD, S1.USER_SEQ
						<if test='nowDiv == "Y"'>
						UNION ALL
						SELECT
							S2.SUM_YMD, S2.USER_SEQ,
							(SUM(S2.sndCntS) + SUM(S2.sndCntL) + SUM(S2.sndCntM) + SUM(S2.sndCntR)) AS sndCnt,
							(SUM(S2.succCntS) + SUM(S2.succCntL) +SUM(S2.succCntM) +SUM(S2.succCntR) + SUM(fbSuccCntS) + SUM(fbSuccCntL)+ SUM(fbSuccCntM)) AS succCnt,
							SUM(S2.sndCntS) AS sndCntS, SUM(S2.succCntS) AS succCntS, SUM(S2.sndCntL) AS sndCntL, SUM(S2.succCntL) AS succCntL, SUM(S2.sndCntM) AS sndCntM,
							SUM(S2.succCntM) AS succCntM, SUM(S2.sndCntR) AS sndCntR, SUM(S2.succCntR) AS succCntR, SUM(S2.fbSndCntS) AS fbSndCntS, SUM(S2.fbSuccCntS) AS fbSuccCntS,
							SUM(S2.fbSndCntL) AS fbSndCntL, SUM(S2.fbSuccCntL) AS fbSuccCntL, SUM(S2.fbSndCntM) AS fbSndCntM, SUM(S2.fbSuccCntM) AS fbSuccCntM
						FROM
							(
								SELECT
									SUM_YMD, USER_SEQ,
									CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntS,
									CASE WHEN SND_CH_CD = 'SMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntS,
									CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntL,
									CASE WHEN SND_CH_CD = 'LMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntL,
									CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SND_CNT) ELSE 0 END AS sndCntM,
									CASE WHEN SND_CH_CD = 'MMS' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntM,
									CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SND_CNT) ELSE 0 END AS sndCntR,
									CASE WHEN SND_CH_CD = 'ALIMTALK' THEN SUM(SUCC_CNT) ELSE 0 END AS succCntR,
									CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntS,
									CASE WHEN SND_CH_CD = 'SMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntS,
									CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntL,
									CASE WHEN SND_CH_CD = 'LMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntL,
									CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_CNT) ELSE 0 END AS fbSndCntM,
									CASE WHEN SND_CH_CD = 'MMS' THEN SUM(FBACK_SUCC_CNT) ELSE 0 END AS fbSuccCntM
								FROM
									(
										SELECT
											DATE_FORMAT(ewm.YMD, '%Y-%m-%d') AS SUM_YMD,
											esu.PRNTS_USER_SEQ AS USER_SEQ,
											REPLACE (ewm.CH_STR ,'KKO_NOTI', 'ALIMTALK') AS SND_CH_CD,
											SUM(IFNULL(ewm.SEND_CNT, 0)) AS SND_CNT,
											SUM(IFNULL(ewm.SUCC_CNT, 0)) AS SUCC_CNT,
											0 AS FBACK_CNT,
											0 AS FBACK_SUCC_CNT
										FROM
											hubez_common.EZ_WEB_MSG ewm INNER JOIN
											hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
										WHERE
											ewm.YMD = DATE_FORMAT( NOW(), '%Y-%m-%d')
										GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.CH_STR
										UNION ALL
										SELECT
											DATE_FORMAT(ewm.YMD, '%Y-%m-%d') AS SUM_YMD,
											esu.PRNTS_USER_SEQ AS USER_SEQ,
											FB_CH_STR AS SND_CH_CD,
											0 AS SND_CNT,
											0 AS SUCC_CNT,
											SUM(IFNULL(ewm.FB_SUCC_CNT + ewm.FB_FAIL_CNT, 0)) AS FBACK_CNT,
											SUM(IFNULL(ewm.FB_SUCC_CNT, 0)) AS FBACK_SUCC_CNT
										FROM
											hubez_common.EZ_WEB_MSG ewm INNER JOIN
											hubez_common.EZ_SVC_USER esu ON ewm.USER_SEQ = esu.USER_SEQ  
										WHERE
											ewm.YMD = DATE_FORMAT( NOW(), '%Y-%m-%d')
										AND (FB_SUCC_CNT > 0 OR FB_FAIL_CNT > 0)
										GROUP BY ewm.YMD, esu.PRNTS_USER_SEQ, ewm.FB_CH_STR
									) T1
								WHERE
									1 = 1
								GROUP BY SUM_YMD, USER_SEQ, SND_CH_CD
							) S2
						WHERE
							1 = 1
						GROUP BY S2.SUM_YMD, S2.USER_SEQ
						</if>
					) A,
					hubez_common.EZ_SUBS_INFO B,
					hubez_common.EZ_CUST_INFO C
				WHERE
					A.USER_SEQ 	= B.USER_SEQ
				AND B.CUST_SEQ = C.CUST_SEQ
				AND C.BIZRNO 	!= '1234567890'
				<include refid="custMstatListCondition"/>
				ORDER BY A.SUM_YMD DESC, C.CUST_NM
			) M
		WHERE
			1 = 1
		ORDER BY M.sumYmd DESC, M.custNm
    </select>

    <sql id="custMstatListCondition">
        <if test="custNm != null and custNm != ''">
            AND CUST_NM LIKE CONCAT('%',#{custNm},'%')
        </if>
        <if test="bizrno != null and bizrno != ''">
            AND BIZRNO LIKE CONCAT('%',#{bizrno},'%')
        </if>
    </sql>
    <sql id="custDstatListCondition">
        <if test="custNm != null and custNm != ''">
            AND CUST_NM LIKE CONCAT('%',#{custNm},'%')
        </if>
        <if test="bizrno != null and bizrno != ''">
            AND BIZRNO LIKE CONCAT('%',#{bizrno},'%')
        </if>
    </sql>
    
    <!-- 실시간 일별 통계 반영분 (합계) -->
    <sql id="totalTodayDate">
    	SELECT  SUM(IFNULL(SEND_CNT, 0)) AS T_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS T_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE ((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND DATE_FORMAT( #{endDay}, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND DATE_FORMAT( REG_DT, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m-%d')
		UNION ALL
		SELECT  0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS S_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS S_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE ((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND DATE_FORMAT( #{endDay}, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND DATE_FORMAT( REG_DT, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND CH_STR = 'SMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m-%d')
		UNION ALL
		SELECT  0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS L_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS L_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE ((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND DATE_FORMAT( #{endDay}, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND DATE_FORMAT( REG_DT, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND CH_STR = 'LMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m-%d')
		UNION ALL
		SELECT  0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS M_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS M_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE ((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND DATE_FORMAT( #{endDay}, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND DATE_FORMAT( REG_DT, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND CH_STR = 'MMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m-%d')
		UNION ALL
		SELECT  0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS A_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0))  AS A_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE ((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND DATE_FORMAT( #{endDay}, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND DATE_FORMAT( REG_DT, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND CH_STR = 'KKO_NOTI'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m-%d')
		UNION ALL
	</sql>
    
    <!-- 실시간 일별 통계 반영분 (날짜) -->
    <sql id="todayDate">
    	SELECT DATE_FORMAT(REG_DT, '%Y-%m-%d') AS SUM_YMD
			,SUM(IFNULL(SEND_CNT, 0)) AS T_SND_CNT
			,SUM(IFNULL(SUCC_CNT, 0)) AS T_SUCC_CNT
			,CASE 
				WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
				WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
				ELSE TRUNCATE((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
			END AS T_SUCC_RT
			,0 AS S_SND_CNT
			,0 AS S_SUCC_CNT
			,0 AS S_SUCC_RT
			,0 AS L_SND_CNT
			,0 AS L_SUCC_CNT
			,0 AS L_SUCC_RT
			,0 AS M_SND_CNT
			,0 AS M_SUCC_CNT
			,0 AS M_SUCC_RT
			,0 AS A_SND_CNT
			,0 AS A_SUCC_CNT
			,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND DATE_FORMAT( #{endDay}, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND DATE_FORMAT( REG_DT, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m-%d')
		UNION ALL
		SELECT DATE_FORMAT(REG_DT, '%Y-%m-%d') AS SUM_YMD
				,0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS S_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS S_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND DATE_FORMAT( #{endDay}, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND DATE_FORMAT( REG_DT, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND CH_STR = 'SMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m-%d')
		UNION ALL
		SELECT DATE_FORMAT(REG_DT, '%Y-%m-%d') AS SUM_YMD
				,0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS L_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS L_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND DATE_FORMAT( #{endDay}, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND DATE_FORMAT( REG_DT, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND CH_STR = 'LMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m-%d')
		UNION ALL
		SELECT DATE_FORMAT(REG_DT, '%Y-%m-%d') AS SUM_YMD
				,0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS M_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS M_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND DATE_FORMAT( #{endDay}, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND DATE_FORMAT( REG_DT, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND CH_STR = 'MMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m-%d')
		UNION ALL
		SELECT DATE_FORMAT(REG_DT, '%Y-%m-%d') AS SUM_YMD
				,0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS A_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS A_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND DATE_FORMAT( #{endDay}, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND DATE_FORMAT( REG_DT, '%Y-%m-%d') = DATE_FORMAT( NOW(), '%Y-%m-%d')
		AND CH_STR = 'KKO_NOTI'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m-%d')
		UNION ALL
	</sql>
	
	<!-- 실시간 월별 통계 반영분 (합계) -->
    <sql id="totalTodayMonth">
    	SELECT  SUM(IFNULL(SEND_CNT, 0)) AS T_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS T_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE ((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND #{endMon} = DATE_FORMAT( NOW(), '%Y%m')
		AND DATE_FORMAT( REG_DT, '%Y-%m') = DATE_FORMAT( NOW(), '%Y-%m')
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m')
		UNION ALL
		SELECT  0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS S_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS S_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE ((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND #{endMon} = DATE_FORMAT( NOW(), '%Y%m')
		AND DATE_FORMAT( REG_DT, '%Y-%m') = DATE_FORMAT( NOW(), '%Y-%m')
		AND CH_STR = 'SMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m')
		UNION ALL
		SELECT  0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS L_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS L_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE ((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND #{endMon} = DATE_FORMAT( NOW(), '%Y%m')
		AND DATE_FORMAT( REG_DT, '%Y-%m') = DATE_FORMAT( NOW(), '%Y-%m')
		AND CH_STR = 'LMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m')
		UNION ALL
		SELECT  0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS M_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS M_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE ((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND #{endMon} = DATE_FORMAT( NOW(), '%Y%m')
		AND DATE_FORMAT( REG_DT, '%Y-%m') = DATE_FORMAT( NOW(), '%Y-%m')
		AND CH_STR = 'MMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m')
		UNION ALL
		SELECT  0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS A_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0))  AS A_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE ((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND #{endMon} = DATE_FORMAT( NOW(), '%Y%m')
		AND DATE_FORMAT( REG_DT, '%Y-%m') = DATE_FORMAT( NOW(), '%Y-%m')
		AND CH_STR = 'KKO_NOTI'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m')
		UNION ALL
	</sql>
    
    <!-- 실시간 월별 통계 반영분 (날짜) -->
    <sql id="todayMonth">
    	SELECT DATE_FORMAT(REG_DT, '%Y-%m') AS SUM_YM
			,SUM(IFNULL(SEND_CNT, 0)) AS T_SND_CNT
			,SUM(IFNULL(SUCC_CNT, 0)) AS T_SUCC_CNT
			,CASE 
				WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
				WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
				ELSE TRUNCATE((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
			END AS T_SUCC_RT
			,0 AS S_SND_CNT
			,0 AS S_SUCC_CNT
			,0 AS S_SUCC_RT
			,0 AS L_SND_CNT
			,0 AS L_SUCC_CNT
			,0 AS L_SUCC_RT
			,0 AS M_SND_CNT
			,0 AS M_SUCC_CNT
			,0 AS M_SUCC_RT
			,0 AS A_SND_CNT
			,0 AS A_SUCC_CNT
			,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND #{endMon} = DATE_FORMAT( NOW(), '%Y%m')
		AND DATE_FORMAT( REG_DT, '%Y-%m') = DATE_FORMAT( NOW(), '%Y-%m')
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m')
		UNION ALL
		SELECT DATE_FORMAT(REG_DT, '%Y-%m') AS SUM_YM
				,0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS S_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS S_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND #{endMon} = DATE_FORMAT( NOW(), '%Y%m')
		AND DATE_FORMAT( REG_DT, '%Y-%m') = DATE_FORMAT( NOW(), '%Y-%m')
		AND CH_STR = 'SMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m')
		UNION ALL
		SELECT DATE_FORMAT(REG_DT, '%Y-%m') AS SUM_YM
				,0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS L_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS L_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND #{endMon} = DATE_FORMAT( NOW(), '%Y%m')
		AND DATE_FORMAT( REG_DT, '%Y-%m') = DATE_FORMAT( NOW(), '%Y-%m')
		AND CH_STR = 'LMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m')
		UNION ALL
		SELECT DATE_FORMAT(REG_DT, '%Y-%m') AS SUM_YM
				,0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS M_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS M_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS M_SUCC_RT
				,0 AS A_SND_CNT
				,0 AS A_SUCC_CNT
				,0 AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND #{endMon} = DATE_FORMAT( NOW(), '%Y%m')
		AND DATE_FORMAT( REG_DT, '%Y-%m') = DATE_FORMAT( NOW(), '%Y-%m')
		AND CH_STR = 'MMS'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m')
		UNION ALL
		SELECT DATE_FORMAT(REG_DT, '%Y-%m') AS SUM_YM
				,0 AS T_SND_CNT
				,0 AS T_SUCC_CNT
				,0 AS T_SUCC_RT
				,0 AS S_SND_CNT
				,0 AS S_SUCC_CNT
				,0 AS S_SUCC_RT
				,0 AS L_SND_CNT
				,0 AS L_SUCC_CNT
				,0 AS L_SUCC_RT
				,0 AS M_SND_CNT
				,0 AS M_SUCC_CNT
				,0 AS M_SUCC_RT
				,SUM(IFNULL(SEND_CNT, 0)) AS A_SND_CNT
				,SUM(IFNULL(SUCC_CNT, 0)) AS A_SUCC_CNT
				,CASE 
					WHEN IFNULL(SUCC_CNT, 0) = 0 THEN 0
					WHEN IFNULL(SEND_CNT, 0) = 0 THEN 0
					ELSE TRUNCATE((SUM(IFNULL(SUCC_CNT, 0)) / SUM(IFNULL(SEND_CNT, 0)) * 100),2)
				END AS A_SUCC_RT
		FROM hubez_common.EZ_WEB_MSG
		WHERE 1 = 1
		AND #{endMon} = DATE_FORMAT( NOW(), '%Y%m')
		AND DATE_FORMAT( REG_DT, '%Y-%m') = DATE_FORMAT( NOW(), '%Y-%m')
		AND CH_STR = 'KKO_NOTI'
		GROUP BY DATE_FORMAT(REG_DT, '%Y-%m')
		UNION ALL
	</sql>	
	
</mapper>