<?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.mntrng.MntrngMapper">

	<select id="selectSendListTotalCnt" parameterType="kr.co.uplus.ez.api.mntrng.dto.SendListReqDto" resultType="int">
      SELECT    COUNT(*) AS TOTALCNT
      FROM hubez_send.EZ_MSG_LOG eml
      WHERE 1=1
	      AND eml.REQ_DATE<![CDATA[ >= ]]>STR_TO_DATE(#{sentDate}, '%Y%m%d') 
	      AND eml.REQ_DATE<![CDATA[ < ]]>DATE_ADD(STR_TO_DATE(#{sentDate}, '%Y%m%d'), INTERVAL 1 DAY)
      <if test='reqChennel != null and reqChennel != "" and reqChennel != "ALL"'>
          AND eml.REQ_PRODUCT = #{reqChennel}
      </if>
	      AND eml.PHONE = #{phone}
	      AND eml.CALLBACK_NUMBER = #{callbackNumber}
      <if test='custNm != null and custNm != ""'>
         AND EXISTS (
          SELECT 1
          FROM hubez_admin.EZ_SNDDCT_REQ esr 
             INNER JOIN hubez_common.EZ_SVC_USER esi 
                ON esi.USER_SEQ = esr.USER_SEQ 
             INNER JOIN hubez_common.EZ_CUST_INFO eci 
                ON eci.CUST_SEQ = esi.CUST_SEQ 
                    AND eci.CUST_NM LIKE CONCAT('%',#{custNm},'%')
          WHERE esr.ORDER_ID  = eml.ORDER_ID 
          AND esr.ORDER_CRET_DT BETWEEN DATE_ADD(#{sentDate}, INTERVAL -3 DAY) AND DATE_ADD(#{sentDate}, INTERVAL 4 DAY)
       	 )
      </if>
	</select>
	
	<select id="selectSendList" parameterType="kr.co.uplus.ez.api.mntrng.dto.SendListReqDto" resultType="kr.co.uplus.ez.api.mntrng.dto.SendList">
	   SELECT
	      @ROWNUM := @ROWNUM + 1 AS NO,
	      A.*
	   FROM
	      (
	      SELECT
	         DATE_FORMAT(eml.REQ_DATE,'%Y-%m-%d') AS SENT_DATE
	         , CASE WHEN ORDER_ID in ('1', '2') THEN 'System' 
	               ELSE (
	                  SELECT esc.CUST_NM
	                  FROM hubez_admin.EZ_SNDDCT_REQ esr
	                       JOIN hubez_common.EZ_SVC_USER esu
	                       ON esr.SNDREQ_USER_SEQ = esu.USER_SEQ
	                    JOIN hubez_common.EZ_CUST_INFO esc
	                       ON esu.CUST_SEQ = esc.CUST_SEQ
	                       WHERE esr.ORDER_ID = eml.ORDER_ID
	               ) 
	            END AS CUST_NM
	         , CASE WHEN ORDER_ID IN ('1', '2') THEN 'System' 
	            ELSE (
	               SELECT     esu.USER_ID
	                 FROM hubez_admin.EZ_SNDDCT_REQ esr
	                 JOIN hubez_common.EZ_SVC_USER esu
	                 ON esr.SNDREQ_USER_SEQ = esu.USER_SEQ
	                 WHERE esr.ORDER_ID = eml.ORDER_ID
	             )
	            END AS USER_ID
	         ,eml.PHONE
	         ,eml.CALLBACK_NUMBER
	         ,eml.REQ_PRODUCT
	         ,eml.DONE_CH
	         ,eml.DONE_TELCO
	         ,eml.DONE_CODE
	         ,DATE_FORMAT(eml.REQ_DATE,'%Y-%m-%d %T') AS REQ_DATE
	         ,DATE_FORMAT(eml.DONE_DATE,'%Y-%m-%d %T') AS DONE_DATE
	      FROM hubez_send.EZ_MSG_LOG eml
	      WHERE 1=1
	      AND eml.REQ_DATE<![CDATA[ >= ]]>STR_TO_DATE(#{sentDate}, '%Y%m%d') 
	      AND eml.REQ_DATE<![CDATA[ < ]]>DATE_ADD(STR_TO_DATE(#{sentDate}, '%Y%m%d'), INTERVAL 1 DAY)
	      <if test='reqChennel != null and reqChennel != ""'>
	         AND eml.REQ_PRODUCT = #{reqChennel}
	      </if>
	          AND eml.PHONE = #{phone}
	          AND eml.CALLBACK_NUMBER = #{callbackNumber}
	          <if test='custNm != null and custNm != ""'>
	           AND EXISTS (
	            SELECT 1
	            FROM hubez_admin.EZ_SNDDCT_REQ esr 
	               INNER JOIN hubez_common.EZ_SVC_USER esi 
	                  ON esi.USER_SEQ = esr.USER_SEQ 
	               INNER JOIN hubez_common.EZ_CUST_INFO eci 
	                  ON eci.CUST_SEQ = esi.CUST_SEQ 
	                      and eci.CUST_NM LIKE CONCAT('%',#{custNm},'%')
	            WHERE esr.ORDER_ID  = eml.ORDER_ID 
	            AND esr.ORDER_CRET_DT BETWEEN DATE_ADD(#{sentDate}, INTERVAL -3 DAY) AND DATE_ADD(#{sentDate}, INTERVAL 4 day)
	         )
	        </if>
	      ORDER BY eml.SENT_DATE DESC
	      LIMIT #{page}, #{pagePerRows}) A,
	      ( SELECT @ROWNUM := #{page} ) AS R
	</select>

	<select id="selectLiveSendSttus" parameterType="kr.co.uplus.ez.api.mntrng.dto.LiveSendSttus" resultType="kr.co.uplus.ez.api.mntrng.dto.LiveSendSttus">
		SELECT IFNULL(SUM(SEND_CNT + FB_SEND_CNT), 0) AS SEND_CNT
			 , IFNULL(SUM(SUCC_CNT + FB_SUCC_CNT), 0) AS SUCC_CNT
			 , IFNULL(ROUND((SUM(SUCC_CNT + FB_SUCC_CNT) / SUM(SEND_CNT + FB_SEND_CNT) * 100), 2), 0) AS SUCC_RT
				<if test='reqType != null and reqType != ""'>
					<if test='reqType eq "M"'>
						, DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -10 MINUTE), '%Y.%m.%d %H:%i') AS START_TIME
					</if>
					<if test='reqType eq "H"'>
						, DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 HOUR), '%Y.%m.%d %H:%i') AS START_TIME
					</if>
					<if test='reqType eq "D"'> 
						, DATE_FORMAT(NOW(),'%Y.%m.%d 00:00') AS START_TIME
					</if>
				</if>
				, DATE_FORMAT(NOW(),'%Y.%m.%d %H:%i') AS END_TIME
		FROM (
				SELECT SUM(IFNULL(SEND_CNT,0)) AS SEND_CNT
					, SUM(IFNULL(SUCC_CNT,0)) AS SUCC_CNT
					, 0 as FB_SEND_CNT
					, 0 as FB_SUCC_CNT
				FROM hubez_common.EZ_WEB_MSG
				WHERE 1=1
				AND CH_STR = #{chStr}
				<if test='reqType != null and reqType != ""'>
					<if test='reqType eq "M"'>
					<![CDATA[
					AND REQ_DT > DATE_ADD(NOW(), INTERVAL -10 MINUTE)
					]]>
					</if>
					<if test='reqType eq "H"'>
					<![CDATA[
					AND REQ_DT > DATE_ADD(NOW(), INTERVAL -1 HOUR)
					]]>
					</if>
					<if test='reqType eq "D"'>
					<![CDATA[ 
					AND REQ_DT >= STR_TO_DATE(DATE_FORMAT(NOW(),'%Y%m%d'), '%Y%m%d')
				 	]]>
				 	</if>
				 </if>
				GROUP BY CH_STR
				union all 
				SELECT  0 AS SEND_CNT
					, 0 AS SUCC_CNT
					, SUM(IFNULL(FB_SUCC_CNT,0)+IFNULL(FB_FAIL_CNT,0)) AS FB_SEND_CNT
					, SUM(IFNULL(FB_SUCC_CNT,0)) AS FB_SUCC_CNT
				FROM hubez_common.EZ_WEB_MSG
				WHERE 1=1
				AND FB_CH_STR = #{chStr}
				<if test='reqType != null and reqType != ""'>
					<if test='reqType eq "M"'>
					<![CDATA[
					AND REQ_DT > DATE_ADD(NOW(), INTERVAL -10 MINUTE)
					]]>
					</if>
					<if test='reqType eq "H"'>
					<![CDATA[
					AND REQ_DT > DATE_ADD(NOW(), INTERVAL -1 HOUR)
					]]>
					</if>
					<if test='reqType eq "D"'>
					<![CDATA[ 
					AND REQ_DT >= STR_TO_DATE(DATE_FORMAT(NOW(),'%Y%m%d'), '%Y%m%d')
				 	]]>
				 	</if>
				</if>
				GROUP BY FB_CH_STR
		) T 
	</select>	

</mapper>