<?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="com.madeu.crm.callLog.map.CallLogMAP">

    <!-- 통화 로그 목록 건수 조회 -->
    <select id="getCallLogCnt" parameterType="com.madeu.crm.callLog.dto.CallLogSearchDTO" resultType="Integer">
        /** CallLogMAP.getCallLogCnt **/
        SELECT COUNT(*) AS TOTAL_COUNT
          FROM MU_CTI_LOG A
         WHERE A.USE_YN = 'Y'
           AND A.FULLDNIS = #{fulldnis}
           AND A.REG_DATE &gt;= CONCAT(#{sDate}, ' 00:00:00')
           AND A.REG_DATE &lt;= CONCAT(#{eDate}, ' 23:59:59')
           <if test="callType != null and callType != '' and callType != 'callback'">
           AND A.MENU_NO = #{callType}
           AND A.STATE_TYPE IN ('start', 'ringout')
           </if>
           <if test="callType == 'callback'">
           AND A.STATE_TYPE = 'callback'
           </if>
           <if test="callType == null or callType == ''">
           AND (A.STATE_TYPE = 'start' OR A.STATE_TYPE = 'ringout')
           </if>
           <if test="callType1 != null and callType1 != ''">
           AND A.C_TYPE1 = #{callType1}
           </if>
           <if test="callType2 != null and callType2 != ''">
           AND A.C_TYPE2 = #{callType2}
           </if>
           <if test="mCid != null and mCid != ''">
           AND A.CID LIKE CONCAT('%', #{mCid}, '%')
           </if>
           <if test="incallSel != null and incallSel != ''">
           AND A.INCALL_SEL LIKE CONCAT('%|=|', #{incallSel}, '|=|%')
           </if>
    </select>

    <!-- 통화 로그 통계 집계 -->
    <select id="getCallLogStats" parameterType="com.madeu.crm.callLog.dto.CallLogSearchDTO" resultType="com.madeu.crm.callLog.dto.CallLogStatsDTO">
        /** CallLogMAP.getCallLogStats **/
        SELECT COUNT(*) AS "totalCnt"
              ,SUM(IF(A.BOUND = 'IN', 1, 0))          AS "inCnt"
              ,SUM(IF(A.BOUND = 'OUT', 1, 0))         AS "outCnt"
              ,SUM(IF(A.C_TYPE1 = '초진', 1, 0))       AS "type1Cnt"
              ,SUM(IF(A.C_TYPE1 = '재진', 1, 0))       AS "type2Cnt"
              ,SUM(IF(A.C_TYPE2 = '상담', 1, 0))       AS "type11Cnt"
              ,SUM(IF(A.C_TYPE2 = '상담후예약', 1, 0))  AS "type12Cnt"
              ,SUM(IF(A.C_TYPE2 = '회차예약', 1, 0))    AS "type13Cnt"
          FROM MU_CTI_LOG A
         WHERE A.USE_YN = 'Y'
           AND A.FULLDNIS = #{fulldnis}
           AND A.REG_DATE &gt;= CONCAT(#{sDate}, ' 00:00:00')
           AND A.REG_DATE &lt;= CONCAT(#{eDate}, ' 23:59:59')
           <if test="callType != null and callType != '' and callType != 'callback'">
           AND A.MENU_NO = #{callType}
           AND A.STATE_TYPE IN ('start', 'ringout')
           </if>
           <if test="callType == 'callback'">
           AND A.STATE_TYPE = 'callback'
           </if>
           <if test="callType == null or callType == ''">
           AND (A.STATE_TYPE = 'start' OR A.STATE_TYPE = 'ringout')
           </if>
           <if test="callType1 != null and callType1 != ''">
           AND A.C_TYPE1 = #{callType1}
           </if>
           <if test="callType2 != null and callType2 != ''">
           AND A.C_TYPE2 = #{callType2}
           </if>
           <if test="mCid != null and mCid != ''">
           AND A.CID LIKE CONCAT('%', #{mCid}, '%')
           </if>
    </select>

    <!-- 통화 연결 건수 (통계용) -->
    <select id="getCallLogLinkedStats" parameterType="com.madeu.crm.callLog.dto.CallLogSearchDTO" resultType="hashmap">
        /** CallLogMAP.getCallLogLinkedStats **/
        SELECT COUNT(*) AS "totalOkCnt"
              ,SUM(IF(A.BOUND = 'IN', 1, 0))  AS "inOkCnt"
              ,SUM(IF(A.BOUND = 'OUT', 1, 0)) AS "outOkCnt"
          FROM MU_CTI_LOG A
         WHERE A.USE_YN = 'Y'
           AND A.FULLDNIS = #{fulldnis}
           AND A.REG_DATE &gt;= CONCAT(#{sDate}, ' 00:00:00')
           AND A.REG_DATE &lt;= CONCAT(#{eDate}, ' 23:59:59')
           AND A.STATE_TYPE = 'link'
    </select>

    <!-- 통화 로그 목록 조회 -->
    <select id="getCallLogList" parameterType="com.madeu.crm.callLog.dto.CallLogSearchDTO" resultType="com.madeu.crm.callLog.dto.CallLogDTO">
        /** CallLogMAP.getCallLogList **/
        SELECT L.*
          FROM (
                SELECT L.*
                      ,CAST(@RNUM:=@RNUM + 1 AS CHAR) AS "rowNum"
                  FROM (
                        SELECT A.MU_CTI_LOG_ID                                   AS "muCtiLogId"
                              ,A.RECORD_NO                                       AS "recordNo"
                              ,A.CID                                             AS "cid"
                              ,A.BOUND                                           AS "bound"
                              ,A.MENU_NO                                         AS "menuNo"
                              ,A.STATE_TYPE                                      AS "stateType"
                              ,A.C_TYPE1                                         AS "cType1"
                              ,A.C_TYPE2                                         AS "cType2"
                              ,A.CRM_MB_PID                                      AS "crmMbPid"
                              ,A.STORE_PID                                       AS "storePid"
                              ,A.INCALL_SEL                                      AS "incallSel"
                              ,A.CALL_BACK_CHK                                   AS "callBackChk"
                              ,DATE_FORMAT(A.REG_DATE, '%Y-%m-%d %H:%i:%s')     AS "regDate"
                              /* ring - ARS 메뉴 선택 */
                              ,(SELECT B.MENU_NO
                                  FROM MU_CTI_LOG B
                                 WHERE B.RECORD_NO = A.RECORD_NO
                                   AND B.CTI_STEP = '2'
                                   AND B.USE_YN = 'Y'
                                 ORDER BY B.REG_DATE DESC LIMIT 1)              AS "ring"
                              /* 통화 연결 시각 */
                              ,(SELECT DATE_FORMAT(B.REG_DATE, '%Y-%m-%d %H:%i:%s')
                                  FROM MU_CTI_LOG B
                                 WHERE B.RECORD_NO = A.RECORD_NO
                                   AND B.CTI_STEP = '3'
                                   AND B.USE_YN = 'Y'
                                 ORDER BY B.REG_DATE DESC LIMIT 1)              AS "linkDate"
                              /* 통화 연결 상담원 */
                              ,(SELECT B.LOGINCID
                                  FROM MU_CTI_LOG B
                                 WHERE B.RECORD_NO = A.RECORD_NO
                                   AND B.CTI_STEP = '3'
                                   AND B.USE_YN = 'Y'
                                 ORDER BY B.REG_DATE DESC LIMIT 1)              AS "linkCid"
                              /* 통화 종료 시각 (step 4) */
                              ,(SELECT DATE_FORMAT(B.REG_DATE, '%Y-%m-%d %H:%i:%s')
                                  FROM MU_CTI_LOG B
                                 WHERE B.RECORD_NO = A.RECORD_NO
                                   AND B.CTI_STEP = '4'
                                   AND B.USE_YN = 'Y'
                                 ORDER BY B.REG_DATE DESC LIMIT 1)              AS "linkEndDate"
                              /* 통화 종료 시각 (step 5) */
                              ,(SELECT DATE_FORMAT(B.REG_DATE, '%Y-%m-%d %H:%i:%s')
                                  FROM MU_CTI_LOG B
                                 WHERE B.RECORD_NO = A.RECORD_NO
                                   AND B.CTI_STEP = '5'
                                   AND B.USE_YN = 'Y'
                                 ORDER BY B.REG_DATE DESC LIMIT 1)              AS "bendDate"
                              /* callback CID */
                              ,(SELECT B.CID
                                  FROM MU_CTI_LOG B
                                 WHERE B.RECORD_NO = A.RECORD_NO
                                   AND B.CTI_STEP = '98'
                                   AND B.USE_YN = 'Y'
                                 ORDER BY B.REG_DATE DESC LIMIT 1)              AS "callbackCid"
                              /* SMS 전송 여부 */
                              ,(SELECT B.STATE_TYPE
                                  FROM MU_CTI_LOG B
                                 WHERE B.RECORD_NO = A.RECORD_NO
                                   AND B.STATE_TYPE = 'lms'
                                   AND B.USE_YN = 'Y'
                                 ORDER BY B.REG_DATE DESC LIMIT 1)              AS "smsFlag"
                              /* 통화 메모 */
                              ,(SELECT T.CALL_MSG
                                  FROM MU_CTI_TEXT T
                                 WHERE T.RECORD_NO = A.RECORD_NO
                                   AND T.USE_YN = 'Y'
                                 ORDER BY T.REG_DATE DESC LIMIT 1)              AS "callMsg"
                              /* 고객명 */
                              ,(SELECT MM.NAME
                                  FROM MU_MEMBER MM
                                 WHERE MM.MU_MEMBER_ID = A.CRM_MB_PID
                                   AND MM.USE_YN = 'Y'
                                 LIMIT 1)                                        AS "memberName"
                          FROM MU_CTI_LOG A
                         WHERE A.USE_YN = 'Y'
                           AND A.FULLDNIS = #{fulldnis}
                           AND A.REG_DATE &gt;= CONCAT(#{sDate}, ' 00:00:00')
                           AND A.REG_DATE &lt;= CONCAT(#{eDate}, ' 23:59:59')
                           <if test="callType != null and callType != '' and callType != 'callback'">
                           AND A.MENU_NO = #{callType}
                           AND A.STATE_TYPE IN ('start', 'ringout')
                           </if>
                           <if test="callType == 'callback'">
                           AND A.STATE_TYPE = 'callback'
                           </if>
                           <if test="callType == null or callType == ''">
                           AND (A.STATE_TYPE = 'start' OR A.STATE_TYPE = 'ringout')
                           </if>
                           <if test="callType1 != null and callType1 != ''">
                           AND A.C_TYPE1 = #{callType1}
                           </if>
                           <if test="callType2 != null and callType2 != ''">
                           AND A.C_TYPE2 = #{callType2}
                           </if>
                           <if test="mCid != null and mCid != ''">
                           AND A.CID LIKE CONCAT('%', #{mCid}, '%')
                           </if>
                           <if test="incallSel != null and incallSel != ''">
                           AND A.INCALL_SEL LIKE CONCAT('%|=|', #{incallSel}, '|=|%')
                           </if>
                         ORDER BY A.REG_DATE DESC
                         LIMIT 18446744073709551615
                  ) L, (SELECT @RNUM:=0) R
                 WHERE 1 = 1
          ) L
         WHERE 1 = 1
         <if test="gridLimitEnd != null and gridLimitEnd != ''">
         LIMIT ${gridLimitStart}, ${gridLimitEnd}
         </if>
    </select>

    <!-- 통화 메모 존재 여부 -->
    <select id="getCallMemoCnt" parameterType="com.madeu.crm.callLog.dto.CallMemoDTO" resultType="Integer">
        /** CallLogMAP.getCallMemoCnt **/
        SELECT COUNT(*)
          FROM MU_CTI_TEXT
         WHERE RECORD_NO = #{recordNo}
           AND USE_YN = 'Y'
    </select>

    <!-- 통화 메모 신규 저장 -->
    <insert id="saveCallMemo" parameterType="com.madeu.crm.callLog.dto.CallMemoDTO">
        <selectKey resultType="string" keyProperty="muCtiTextId" order="BEFORE">
            SELECT CONCAT('CTXT', LPAD(IFNULL(MAX(CAST(SUBSTRING(MU_CTI_TEXT_ID, 5) AS UNSIGNED)), 0) + 1, 11, '0'))
              FROM MU_CTI_TEXT
        </selectKey>
        /** CallLogMAP.saveCallMemo **/
        INSERT INTO MU_CTI_TEXT (
             MU_CTI_TEXT_ID
            ,RECORD_NO
            ,CALL_MSG
            ,USE_YN
            ,CUD_FLAG
            ,REG_ID
            ,REG_DATE
            ,MOD_ID
            ,MOD_DATE
        ) VALUES (
             #{muCtiTextId}
            ,#{recordNo}
            ,#{callMsg}
            ,'Y'
            ,'C'
            ,#{loginMemberId}
            ,NOW()
            ,#{loginMemberId}
            ,NOW()
        )
    </insert>

    <!-- 통화 메모 수정 -->
    <update id="modCallMemo" parameterType="com.madeu.crm.callLog.dto.CallMemoDTO">
        /** CallLogMAP.modCallMemo **/
        UPDATE MU_CTI_TEXT
           SET CALL_MSG  = #{callMsg}
              ,CUD_FLAG  = 'U'
              ,MOD_ID    = #{loginMemberId}
              ,MOD_DATE  = NOW()
         WHERE RECORD_NO = #{recordNo}
           AND USE_YN = 'Y'
    </update>

    <!-- CTI 로그 저장 (Webhook용) -->
    <insert id="putCtiLog" parameterType="com.madeu.crm.callLog.dto.CallLogDTO">
        <selectKey resultType="string" keyProperty="muCtiLogId" order="BEFORE">
            SELECT CONCAT('CLOG', LPAD(IFNULL(MAX(CAST(SUBSTRING(MU_CTI_LOG_ID, 5) AS UNSIGNED)), 0) + 1, 11, '0'))
              FROM MU_CTI_LOG
        </selectKey>
        /** CallLogMAP.putCtiLog **/
        INSERT INTO MU_CTI_LOG (
             MU_CTI_LOG_ID
            ,RECORD_NO
            ,CID
            ,FULLDNIS
            ,BOUND
            ,MENU_NO
            ,STATE_TYPE
            ,CTI_STEP
            ,LOGINCID
            ,C_TYPE1
            ,C_TYPE2
            ,CRM_MB_PID
            ,STORE_PID
            ,INCALL_SEL
            ,USE_YN
            ,CUD_FLAG
            ,REG_DATE
            ,MOD_DATE
        ) VALUES (
             #{muCtiLogId}
            ,#{recordNo}
            ,#{cid}
            ,#{fulldnis}
            ,#{bound}
            ,#{menuNo}
            ,#{stateType}
            ,#{ctiStep}
            ,#{logincid}
            ,#{cType1}
            ,#{cType2}
            ,#{crmMbPid}
            ,#{storePid}
            ,#{incallSel}
            ,'Y'
            ,'C'
            ,NOW()
            ,NOW()
        )
    </insert>

    <!-- CTI 로그 회원 매핑 업데이트 -->
    <update id="modCtiLogMember" parameterType="hashmap">
        /** CallLogMAP.modCtiLogMember **/
        UPDATE MU_CTI_LOG
           SET STORE_PID   = #{storePid}
              ,CRM_MB_PID  = #{crmMbPid}
              ,MOD_DATE    = NOW()
         WHERE RECORD_NO = #{recordNo}
           AND USE_YN = 'Y'
    </update>

</mapper>
