<?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.attractMgt.AttractMgtMapper">

    <select id="selectAttractListTotalCnt" parameterType="kr.co.uplus.ez.api.attractMgt.dto.ChannelListReqDto"
            resultType="int">
        /* attractMgt-mapper.xml(selectAttractListTotalCnt) */
        SELECT
        COUNT(*) AS totalCnt
        FROM
        hubez_common.EZ_SUBS_INFO esi
        INNER JOIN hubez_common.EZ_CUST_INFO eci
        ON eci.CUST_SEQ = esi.CUST_SEQ
        INNER JOIN hubez_common.EZ_SVC_USER esu
        ON esu.USER_SEQ = esi.USER_SEQ
        INNER JOIN hubez_imdb.EZ_IM_USER eiu
        ON eiu.LOGIN_ID = esi.ATTRCTOR_ID
        INNER JOIN hubez_imdb.EZ_IM_GROUP eig
        ON eiu.EX_DEPT_CD = eig.NORG_CD
        WHERE 1 = 1
        <include refid="attractListCondition"/>
    </select>

    <select id="selectAttractList" parameterType="kr.co.uplus.ez.api.attractMgt.dto.ChannelListReqDto"
            resultType="kr.co.uplus.ez.api.attractMgt.dto.ChannelInfo">
        /* attractMgt-mapper.xml(selectAttractList) */
        SELECT
        @ROWNUM := @ROWNUM + 1 AS NO
        , A.SUBS_DT
        , A.NORG_NM
        , A.USER_SEQ
        , A.LOGIN_ID
        , A.CUST_NM
        , A.BIZRNO
        , A.USER_NM
        , A.SUBS_STTUS_CD
        , (SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'SUBS_STTUS_CD' AND DTL_CD = A.SUBS_STTUS_CD) as
        SUBS_STTUS_NM
        , A.CUST_TY_CD
        , (SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'CUST_KD_CD' AND DTL_CD = A.CUST_TY_CD) as
        CUST_TY_NM
        , (
	        SELECT
				sum(IFNULL(ecm.SND_CNT,0)) AS SND_CNT
			FROM
				hubez_common.EZ_CUST_MSTAT ecm
			WHERE
				ecm.USER_SEQ = A.USER_SEQ
			AND ecm.SUM_YM BETWEEN DATE_ADD(NOW(), INTERVAL -24 MONTH) AND NOW()
        )  + 
        (
        	SELECT
				SUM(IFNULL(ewm.SEND_CNT, 0)) AS SND_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 esu.PRNTS_USER_SEQ = A.USER_SEQ
        ) AS SND_CNT
        FROM
        (
        SELECT
        DATE_FORMAT(esi.SUBS_DT , '%Y-%m-%d') AS SUBS_DT
        , eig.NORG_NM
        , esi.USER_SEQ
        , eiu.LOGIN_ID
        , eci.CUST_NM
        , eci.BIZRNO
        , esu.USER_NM
        , esi.SUBS_STTUS_CD
        , eci.CUST_TY_CD
        FROM
        hubez_common.EZ_SUBS_INFO esi
        INNER JOIN hubez_common.EZ_CUST_INFO eci
        ON eci.CUST_SEQ = esi.CUST_SEQ
        INNER JOIN hubez_common.EZ_SVC_USER esu
        ON esu.USER_SEQ = esi.USER_SEQ
        INNER JOIN hubez_imdb.EZ_IM_USER eiu
        ON eiu.LOGIN_ID = esi.ATTRCTOR_ID
        INNER JOIN hubez_imdb.EZ_IM_GROUP eig
        ON eiu.EX_DEPT_CD = eig.NORG_CD
        WHERE 1 = 1
        <include refid="attractListCondition"/>
        ORDER BY esi.SUBS_DT DESC
        LIMIT #{page}, #{pagePerRows}
        ) A , ( SELECT @ROWNUM := #{page} ) AS R
    </select>

    <select id="selectAttractExcelList" parameterType="kr.co.uplus.ez.api.attractMgt.dto.ChannelListExcelReqDto"
            resultType="kr.co.uplus.ez.api.attractMgt.dto.ChannelInfo">
        /* attractMgt-mapper.xml(selectAttractExcelList) */
        SELECT
        @ROWNUM := @ROWNUM + 1 AS NO
        , A.SUBS_DT
        , A.NORG_NM
        , A.USER_SEQ
        , A.LOGIN_ID
        , A.CUST_NM
        , A.BIZRNO
        , A.USER_NM
        , (SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'SUBS_STTUS_CD' AND DTL_CD = A.SUBS_STTUS_CD) as
        SUBS_STTUS_NM
        , (SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'CUST_KD_CD' AND DTL_CD = A.CUST_TY_CD) as
        CUST_TY_NM
        , (
	        SELECT
				sum(IFNULL(ecm.SND_CNT,0)) AS SND_CNT
			FROM
				hubez_common.EZ_CUST_MSTAT ecm
			WHERE
				ecm.USER_SEQ = A.USER_SEQ
			AND ecm.SUM_YM BETWEEN DATE_ADD(NOW(), INTERVAL -24 MONTH) AND NOW()
        )  + 
        (
        	SELECT
				SUM(IFNULL(ewm.SEND_CNT, 0)) AS SND_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 esu.PRNTS_USER_SEQ = A.USER_SEQ
        ) AS SND_CNT
        FROM
        (
        SELECT
        DATE_FORMAT(esi.SUBS_DT , '%Y-%m-%d') AS SUBS_DT
        , eig.NORG_NM
        , esi.USER_SEQ
        , eiu.LOGIN_ID
        , eci.CUST_NM
        , eci.BIZRNO
        , esu.USER_NM
        , esi.SUBS_STTUS_CD
        , eci.CUST_TY_CD
        FROM
        hubez_common.EZ_SUBS_INFO esi
        INNER JOIN hubez_common.EZ_CUST_INFO eci
        ON eci.CUST_SEQ = esi.CUST_SEQ
        INNER JOIN hubez_common.EZ_SVC_USER esu
        ON esu.USER_SEQ = esi.USER_SEQ
        INNER JOIN hubez_imdb.EZ_IM_USER eiu
        ON eiu.LOGIN_ID = esi.ATTRCTOR_ID
        INNER JOIN hubez_imdb.EZ_IM_GROUP eig
        ON eiu.EX_DEPT_CD = eig.NORG_CD
        WHERE 1 = 1
        <include refid="attractListCondition"/>
        ORDER BY esi.SUBS_DT DESC) A ,
        ( SELECT @ROWNUM := 0 ) AS R
    </select>


    <sql id="attractListCondition">
        <if test="subsStDt != null and subsStDt != '' and subsEdDt != null and subsEdDt != ''">
            AND esi.SUBS_DT >= #{subsStDt} AND esi.SUBS_DT <![CDATA[ < ]]> DATE_ADD(#{subsEdDt}, interval 1 day)
        </if>
        <if test="subsSttusCd != null and subsSttusCd != ''">
            AND esi.SUBS_STTUS_CD = #{subsSttusCd}
        </if>
        <if test="custTyCd != null and custTyCd != ''">
            AND eci.CUST_TY_CD = #{custTyCd}
        </if>
        <if test="loginId != null and loginId != ''">
            AND eiu.LOGIN_ID LIKE CONCAT('%', #{loginId}, '%')
        </if>
        <if test="norgNm != null and norgNm != ''">
            AND eig.NORG_NM LIKE CONCAT('%', #{norgNm}, '%')
        </if>
        <if test="searchType != null and searchType != ''">
            <if test="searchType == '01' and searchText != null and searchText != ''">
                AND eci.CUST_NM LIKE CONCAT('%', #{searchText}, '%')
            </if>
            <if test="searchType == '02' and searchText != null and searchText != ''">
                AND esu.USER_NM LIKE CONCAT('%', #{searchText}, '%')
            </if>
            <if test="searchType == '03' and searchText != null and searchText != ''">
                AND eci.BIZRNO LIKE CONCAT('%', #{searchText}, '%')
            </if>
        </if>
        <if test="attrctorId != null and attrctorId != ''">
            and esi.ATTRCTOR_ID LIKE CONCAT('%', #{attrctorId}, '%')
        </if>
    </sql>

    <select id="selectAttractDetail" parameterType="kr.co.uplus.ez.api.attractMgt.dto.ChannelDetailReqDto"
            resultType="kr.co.uplus.ez.api.attractMgt.dto.ChannelDetailRes">
        /* attractMgt-mapper.xml(selectAttractDetail) */
        SELECT DATE_FORMAT(esi.SUBS_DT, '%Y-%m-%d')  AS SUBS_DT
             , esi.SUBS_STTUS_CD
             , (SELECT T2.DTL_CD_NM AS codeNm
                FROM hubez_common.EZ_CD_GRP T1
                         LEFT JOIN hubez_common.EZ_CD_DTL T2
                                   ON T1.GRP_CD = T2.GRP_CD
                WHERE T1.GRP_CD = 'SUBS_STTUS_CD'
                  AND T2.DTL_CD = esi.SUBS_STTUS_CD) AS SUBS_STTUS_NM
             , eci.CUST_NM
             , esi.PROD_CD
             , eci.REPR_NM
             , eci.CUST_TY_CD
             , (SELECT T2.DTL_CD_NM AS codeNm
                FROM hubez_common.EZ_CD_GRP T1
                         LEFT JOIN hubez_common.EZ_CD_DTL T2
                                   ON T1.GRP_CD = T2.GRP_CD
                WHERE T1.GRP_CD = 'CUST_KD_CD'
                  AND T2.DTL_CD = eci.CUST_TY_CD)    AS CUST_TY_NM
             , eci.BIZRNO
             , eci.CORPNO                            AS CPR_REG_NO
             , eci.ZIPCD                             AS ADR1
             , eci.ADDR1                             AS ADR2
             , eci.ADDR2                             AS ADR3
             , esi.ATTRCTOR_ID                       AS CHANNEL_ID
             , esi.ATTRCTOR_NM                       AS CHANNEL_NM
             , esi.SUBSMNGR_ID                       AS ADMIN_ID
             , esi.SUBSMNGR_NM                       AS ADMIN_NM
             , esu.USER_ID
             , (
            SELECT PROD_NM
            FROM hubez_common.EZ_PROD_INFO
            WHERE PROD_CD = esi.PROD_CD)             AS PLAN
        FROM hubez_common.EZ_SUBS_INFO esi
                 INNER JOIN hubez_common.EZ_CUST_INFO eci
                            ON eci.CUST_SEQ = esi.CUST_SEQ
                 INNER JOIN hubez_common.EZ_SVC_USER esu
                            ON esu.USER_SEQ = esi.USER_SEQ
                 INNER JOIN hubez_imdb.EZ_IM_USER eiu
                            ON eiu.LOGIN_ID = esi.ATTRCTOR_ID
                 INNER JOIN hubez_imdb.EZ_IM_GROUP eig
                            ON eiu.EX_DEPT_CD = eig.NORG_CD
        WHERE 1 = 1
          AND esu.USER_SEQ = #{userSeq}
    </select>

    <select id="selectSndCountList" parameterType="kr.co.uplus.ez.api.attractMgt.dto.ChannelDetailReqDto" resultType="kr.co.uplus.ez.api.attractMgt.dto.ChannelDetail">
		/* attractMgt-mapper.xml(selectSndCountList) */
		SELECT
			SUM_YM AS sumYm, A.sndCnt, A.succCnt, A.sndCntS, A.succCntS, A.sndCntL, A.succCntL,
			A.sndCntM, A.succCntM, A.sndCntR AS sndCntA, A.succCntR AS succCntA, A.fbSndCntS, A.fbSuccCntS, A.fbSndCntL,
			A.fbSuccCntL, A.fbSndCntM, A.fbSuccCntM
		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 DATE_ADD(NOW(), INTERVAL -24 MONTH) AND NOW()
						AND ewm.USER_SEQ = #{userSeq}
						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
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND esu.PRNTS_USER_SEQ = #{userSeq}
								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
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND esu.PRNTS_USER_SEQ = #{userSeq}
								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
					) S1
				WHERE
					1 = 1
				GROUP BY S1.SUM_YM
			) A
		WHERE 1 = 1
		ORDER BY A.SUM_YM DESC
    </select>

    <select id="sendNumberListExcel" parameterType="kr.co.uplus.ez.api.attractMgt.dto.ChannelDetailReqDto" resultType="kr.co.uplus.ez.api.attractMgt.dto.ChannelDetail">
		/* attractMgt-mapper.xml(sendNumberListExcel) */
        SELECT
			SUM_YM AS sumYm, A.sndCnt, A.succCnt, A.sndCntS, A.succCntS, A.sndCntL, A.succCntL,
			A.sndCntM, A.succCntM, A.sndCntR AS sndCntA, A.succCntR AS succCntA, A.fbSndCntS, A.fbSuccCntS, A.fbSndCntL,
			A.fbSuccCntL, A.fbSndCntM, A.fbSuccCntM
		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 DATE_ADD(NOW(), INTERVAL -24 MONTH) AND NOW()
						AND ewm.USER_SEQ = #{userSeq}
						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
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND esu.PRNTS_USER_SEQ = #{userSeq}
								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
								WHERE
									ewm.YMD BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01') AND DATE_FORMAT( NOW(), '%Y-%m-%d')
								AND esu.PRNTS_USER_SEQ = #{userSeq}
								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
					) S1
				WHERE
					1 = 1
				GROUP BY S1.SUM_YM
			) A
		WHERE 1 = 1
		ORDER BY A.SUM_YM DESC
    </select>
</mapper>