<?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.riskMgt.RiskMgtMapper">

    <select id="selectSndrnoBlckTotalCnt" parameterType="kr.co.uplus.ez.api.riskMgt.dto.SendNumIntrcpListReqDto" resultType="int">
        /* riskMgt-mapper.xml(selectSndrnoBlckTotalCnt) */
        SELECT
            COUNT(*) AS TOTALCNT
        FROM hubez_common.EZ_SNDRNO_BLCK esb
        WHERE 1 = 1
        <include refid="sndnoBlckCondition" />
    </select>

    <select id="selectSndrnoBlckList" parameterType="kr.co.uplus.ez.api.riskMgt.dto.SendNumIntrcpListReqDto" resultType="kr.co.uplus.ez.api.riskMgt.dto.SendNumIntrcpList">
        /* riskMgt-mapper.xml(selectSndrnoBlckList) */
        SELECT
            @ROWNUM := @ROWNUM + 1 AS NO
            , A.*
                FROM
                    (
                    SELECT
                        esb.BLCK_SNDRNO
                        ,esb.SNDBLCK_TP_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 = 'SNDBLCK_TP_CD'
					        and T2.DTL_CD=esb.SNDBLCK_TP_CD) AS SNDBLCK_TP_NM
					    ,esb.BLCK_RSN_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 = 'SNDBLCK_RSN_CD'
					        and T2.DTL_CD=esb.BLCK_RSN_CD) AS BLCK_RSN_NM
                        , (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 = 'SNDBLCK_YN_CD'
                        	AND T2.DTL_CD=esb.BLCK_YN) AS BLCK_YN
                        , esb.REG_ID
                        , esb.CHG_ID
                        , DATE_FORMAT(esb.CHG_DT, '%Y-%m-%d') As LAST_CHG_DT
                    FROM
                    hubez_common.EZ_SNDRNO_BLCK esb
                    WHERE 1 = 1
                        <include refid="sndnoBlckCondition"/>
            ORDER BY esb.CHG_DT DESC
            LIMIT #{page}, #{pagePerRows}
        ) A ,  ( SELECT @ROWNUM := #{page} ) AS R
    </select>

    <sql id="sndnoBlckCondition">
        <if test="regId != null and regId != ''">
            AND UPPER(esb.REG_ID) LIKE UPPER(CONCAT('%', #{regId}, '%'))
        </if>
        <if test="sndblckTpCd != null and sndblckTpCd != ''">
            AND esb.SNDBLCK_TP_CD = #{sndblckTpCd}
        </if>
        <if test="blckYn != null and blckYn != ''">
            AND esb.BLCK_YN = #{blckYn}
        </if>
        <if test="blckRsnCd != null and blckRsnCd != ''">
            AND esb.BLCK_RSN_CD = #{blckRsnCd}
        </if>
        <if test="blckSndrno != null and blckSndrno != ''">
            AND esb.BLCK_SNDRNO LIKE CONCAT('%', #{blckSndrno},'%')
        </if>
    </sql>

    <select id="selectSndrnoBlckDetail" parameterType="String" resultType="kr.co.uplus.ez.api.riskMgt.dto.SendNumIntrcpDetail">
        /* riskMgt-mapper.xml(selectSndrnoBlckDetail) */
        SELECT
            BLCK_SNDRNO
             ,SNDBLCK_TP_CD
             ,BLCK_RSN_CD
             ,MENO
             ,BLCK_YN
        FROM hubez_common.EZ_SNDRNO_BLCK esb
        WHERE  esb.BLCK_SNDRNO = #{blckSndrno}
    </select>

    <insert id="insertSndrnoBlck" parameterType="kr.co.uplus.ez.api.riskMgt.dto.SendNumInsertIntrcpReqDto">
        /* riskMgt-mapper.xml(insertSndrnoBlck) */
        INSERT
            INTO hubez_common.EZ_SNDRNO_BLCK
                (BLCK_SNDRNO
                , SNDBLCK_TP_CD
                , BLCK_RSN_CD
                , MENO
                , BLCK_YN
                , REG_ID
                , REG_DT
                , CHG_ID
                , CHG_DT
            ) VALUES (
               #{blckSndrno}
              , #{sndblckTpCd}
              , #{blckRsnCd}
              , #{meno}
              , #{blckYn}
              , #{regId}
              , NOW()
              , #{regId}
              , NOW())
    </insert>

    <update id="updateSndrnoBlck" parameterType="kr.co.uplus.ez.api.riskMgt.dto.SendNumUpdateIntrcplReqDto">
        /* riskMgt-mapper.xml(updateSndrnoBlck) */
        UPDATE
            hubez_common.EZ_SNDRNO_BLCK
        SET
          CHG_ID = #{chgId}
          , CHG_DT = NOW()
        <if test="sndblckTpCd != null and sndblckTpCd != ''">
          ,  SNDBLCK_TP_CD = #{sndblckTpCd}
        </if>
        <if test="blckRsnCd != null and blckRsnCd != ''">
          , BLCK_RSN_CD = #{blckRsnCd}
        </if>
        <if test="meno != null and meno != ''">
          , MENO = #{meno}
        </if>
        <if test="blckYn != null and blckYn != ''">
          , BLCK_YN = #{blckYn}
        </if>
        WHERE BLCK_SNDRNO = #{blckSndrno}
    </update>

    <delete id="deleteSndrnoBlck" parameterType="kr.co.uplus.ez.api.riskMgt.dto.SendNumDeleteIntrcpReqDto">
        /* riskMgt-mapper.xml(deleteSndrnoBlck) */
        DELETE
        FROM
            hubez_common.EZ_SNDRNO_BLCK
        WHERE
            BLCK_SNDRNO = #{blckSndrno}
    </delete>


    <select id="selectRcvblck080ListTotalCnt" parameterType="kr.co.uplus.ez.api.riskMgt.dto.ZezNumIntrcpListReqDto" resultType="int">
        /* riskMgt-mapper.xml(selectRcvblck080ListTotalCnt) */
        SELECT count(*) AS TOTAL_CNT
        FROM
            hubez_common.EZ_RCVBLCK_080 er
                INNER JOIN hubez_common.EZ_SVC_USER esu
                           ON er.AUTHCD_080 = esu.AUTHCD_080
                INNER JOIN hubez_common.EZ_CUST_INFO eci
                           ON esu.CUST_SEQ = eci.CUST_SEQ
        WHERE 1 = 1
        AND esu.USER_SEQ = esu.PRNTS_USER_SEQ
        <include refid="selectRcvblck080Condition"/>
    </select>

    <select id="selectRcvblck080List" parameterType="kr.co.uplus.ez.api.riskMgt.dto.ZezNumIntrcpListReqDto" resultType="kr.co.uplus.ez.api.riskMgt.dto.ZezNumIntrcpList">
        /* riskMgt-mapper.xml(selectRcvblck080List) */
        SELECT
             @ROWNUM := @ROWNUM + 1 AS NO
            , A.CUST_NM
            , A.BIZRNO
             , A.AUTHCD_080
             , A.RCVBLCKNO
             , A.USER_ID
             , A.REG_DT
             , ( SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'BLKCALL_REG_TP_CD' AND DTL_CD = A.REG_TP_CD) AS REG_TP_CD
        FROM
            (
                SELECT
                    eci.CUST_NM
                     , eci.BIZRNO
                     , er.AUTHCD_080
                     , er.RCVBLCKNO
                     , esu.USER_ID
                     , DATE_FORMAT(er.REG_DT, '%Y-%m-%d') AS REG_DT
                     , er.REG_TP_CD
                FROM
                    hubez_common.EZ_RCVBLCK_080 er
                        INNER JOIN hubez_common.EZ_SVC_USER esu
                                   ON er.AUTHCD_080 = esu.AUTHCD_080
                        INNER JOIN hubez_common.EZ_CUST_INFO eci
                                   ON esu.CUST_SEQ = eci.CUST_SEQ
                WHERE 1 = 1
                AND esu.USER_SEQ = esu.PRNTS_USER_SEQ
                <include refid="selectRcvblck080Condition"/>
                ORDER BY er.REG_DT DESC
                LIMIT #{page}, #{pagePerRows}
            ) A, ( SELECT @ROWNUM := #{page} ) AS R
    </select>

    <sql id="selectRcvblck080Condition">
        <if test="custNm != null and custNm != ''">
        AND eci.CUST_NM LIKE CONCAT('%', #{custNm}, '%')
        </if>
        <if test="authcd080 != null and authcd080 != ''">
        AND er.AUTHCD_080 LIKE CONCAT('%', #{authcd080}, '%')
        </if>
        <if test="rcvblckno != null and rcvblckno != ''">
        AND er.RCVBLCKNO LIKE CONCAT('%', #{rcvblckno},'%')
        </if>
    </sql>

    <select id="selectMsgBlckwordListTotalCnt" parameterType="kr.co.uplus.ez.api.riskMgt.dto.MsgIntrcpListReqDto" resultType="int">
        /* riskMgt-mapper.xml(selectMsgBlckwordListTotalCnt) */        
        SELECT COUNT(*) AS TOTAL_CNT
        FROM hubez_common.EZ_MSG_BLCKWORD emb
        WHERE 1 = 1
        <include refid="msgBlckwordListCondition"/>
    </select>

    <select id="selectMsgBlckwordList" parameterType="kr.co.uplus.ez.api.riskMgt.dto.MsgIntrcpListReqDto" resultType="kr.co.uplus.ez.api.riskMgt.dto.MsgIntrcpList">
        /* riskMgt-mapper.xml(selectMsgBlckwordList) */        
                select @ROWNUM := @ROWNUM + 1 AS NO
                , A.SEQ_NO
      , (   select case when char_length(GROUP_CONCAT(WORD)) > 12 then concat(substr(GROUP_CONCAT(WORD) , 1, 12),'...')
               else GROUP_CONCAT(WORD)
             end 
          from hubez_common.EZ_BLCKWORD_DTL
         where SEQ_NO  = A.SEQ_NO
      ) as WORD
     , (select DTL_CD_NM from hubez_common.EZ_CD_DTL where GRP_CD  = 'SNDBLCK_YN_CD' and DTL_CD  = A.BLCK_YN ) as BLCK_YN
     , DATE_FORMAT(A.CHG_DT , '%Y-%m-%d') AS LAST_CHG_DT
     , (select DTL_CD_NM from hubez_common.EZ_CD_DTL where GRP_CD  = 'SNDBLCK_RSN_CD' and DTL_CD  = A.BLCK_RSN_CD ) as BLCK_RSN_CD
     , A.REG_ID
from (
   select emb.SEQ_NO 
   , emb.BLCK_YN 
   , emb.BLCK_RSN_CD
   , emb.REG_ID 
   , emb.CHG_DT
   from hubez_common.EZ_MSG_BLCKWORD emb
   where 1 = 1
   <include refid="msgBlckwordListCondition"/>
   ORDER BY emb.CHG_DT DESC
   LIMIT #{page}, #{pagePerRows} ) A ,( SELECT @ROWNUM := #{page} ) AS R
    </select>

    <sql id="msgBlckwordListCondition">        
        <if test="regId != null and regId != ''">
        AND emb.REG_ID LIKE CONCAT('%',  #{regId}, '%')  
        </if>
        <if test="blckRsnCd != null and blckRsnCd != ''">
        AND emb.BLCK_RSN_CD = #{blckRsnCd}
        </if>
        <if test="word != null and word != ''">
        and emb.SEQ_NO in  (select SEQ_NO from hubez_common.EZ_BLCKWORD_DTL where WORD like CONCAT('%', #{word}, '%'))
        </if>
    </sql>

    <select id="selectMsgBlckwordDetail" parameterType="kr.co.uplus.ez.api.riskMgt.dto.MsgIntrcpDetailReqDto" resultType="kr.co.uplus.ez.api.riskMgt.dto.MsgIntrcpDetail">
        /* riskMgt-mapper.xml(selectMsgBlckwordDetail) */
        SELECT
            SEQ_NO
             , BLCK_CONT_CD
             , BLCK_RSN_CD
             , MEMO
             , BLCK_YN
        FROM
            hubez_common.EZ_MSG_BLCKWORD emb
        WHERE emb.SEQ_NO = #{seqNo}
    </select>

    <select id="selectBlckwordDtlList" parameterType="kr.co.uplus.ez.api.riskMgt.dto.MsgIntrcpDetailReqDto" resultType="kr.co.uplus.ez.api.riskMgt.dto.MsgBlckwordList">
        /* riskMgt-mapper.xml(selectBlckwordDtlList) */
        SELECT
            ebd.SEQ_NO
             , ebd.WORD
        FROM
            hubez_common.EZ_BLCKWORD_DTL ebd
        WHERE ebd.SEQ_NO = #{seqNo}
    </select>

	<select id="selectMsgBlckWordSeq" resultType="String">
	/* riskMgt-mapper.xml(selectMsgBlckWordSeq) */
	SELECT hubez_common.FUN_NEXT_SEQ('MSG_BLCKWORD_SEQ')
	</select>

    <insert id="insertMsgBlckword" parameterType="kr.co.uplus.ez.api.riskMgt.dto.MsgInsertIntrcpReqDto">
        /* riskMgt-mapper.xml(insertMsgBlckword) */
        INSERT
            INTO hubez_common.EZ_MSG_BLCKWORD (
                SEQ_NO
                , BLCK_CONT_CD
                , BLCK_RSN_CD
                , BLCK_YN
                , MEMO
                , REG_ID
                , REG_DT
                , CHG_ID
                , CHG_DT
            ) VALUES(
                #{seqNo}
                , #{blckContCd}
                , #{blckRsnCd}
                , #{blckYn}
                , #{memo}
                , #{regId}
                , NOW()
                , #{regId}
                , NOW())
    </insert>

    <insert id="insertBlckwordDtl" parameterType="java.util.List">
        /* riskMgt-mapper.xml(insertBlckwordDtl) */
        INSERT
            INTO hubez_common.EZ_BLCKWORD_DTL (
               SEQ_NO
                , WORD
                , REG_ID
                , REG_DT
            ) VALUES
            <foreach collection="list" item="item" index="i"
            separator=",">
				(#{item.seqNo}, #{item.word}, #{item.regId}, NOW() )
            </foreach>
    </insert>

    <update id="updateMsgBlckword" parameterType="kr.co.uplus.ez.api.riskMgt.dto.MsgUpdateIntrcplReqDto">
        /* riskMgt-mapper.xml(updateMsgBlckword) */
        UPDATE
            hubez_common.EZ_MSG_BLCKWORD
        SET
           CHG_ID = #{regId}
          , CHG_DT = NOW()
        <if test="blckContCd != null and blckContCd != ''">
          ,  BLCK_CONT_CD = #{blckContCd}
        </if>
        <if test="blckRsnCd != null and blckRsnCd != ''">
          , BLCK_RSN_CD = #{blckRsnCd}
        </if>
        <if test="blckYn != null and blckYn != ''">
          , BLCK_YN = #{blckYn}
        </if>
        <if test="memo != null and memo != ''">
          , MEMO = #{memo}
        </if>
        WHERE
            SEQ_NO = #{seqNo}
    </update>

    <delete id="deleteMsgBlckword" parameterType="kr.co.uplus.ez.api.riskMgt.dto.MsgDeleteIntrcpReqDto">
        /* riskMgt-mapper.xml(deleteMsgBlckword) */
        DELETE FROM
            hubez_common.EZ_MSG_BLCKWORD
        WHERE
            SEQ_NO = #{seqNo}
    </delete>

    <delete id="deleteBlckwordDtl" parameterType="String">
        /* riskMgt-mapper.xml(deleteBlckwordDtl) */
        DELETE FROM
            hubez_common.EZ_BLCKWORD_DTL
        WHERE SEQ_NO = #{seqNo}
    </delete>

    <select id="selectWebsndBlckListTotalCnt" parameterType="kr.co.uplus.ez.api.riskMgt.dto.WebInsertIntrcpReqDto" resultType="int">
        /* riskMgt-mapper.xml(selectWebsndBlckListTotalCnt) */
        SELECT
            COUNT(*)
        FROM
            hubez_common.EZ_WEBSND_BLCK ewb
            WHERE 1=1
            AND ewb.BLCK_DT BETWEEN STR_TO_DATE(#{blckDt}, '%Y%m%d') AND DATE_ADD(STR_TO_DATE(#{blckDt}, '%Y%m%d'), INTERVAL 1 DAY)
        <include refid="websndBlckListCondition"/>
    </select>

    <select id="selectWebsndBlckList" parameterType="kr.co.uplus.ez.api.riskMgt.dto.WebInsertIntrcpReqDto" resultType="kr.co.uplus.ez.api.riskMgt.dto.WebIntrcpList">
        /* riskMgt-mapper.xml(selectWebsndBlckList) */
        SELECT
            @ROWNUM := @ROWNUM + 1 AS NO
             , A.SEQ_NO
             ,(SELECT ecd.DTL_CD_NM FROM hubez_common.EZ_CD_DTL ecd WHERE ecd.GRP_CD = 'WEBBLCK_TP_CD' AND DTL_CD = A.BLCK_TP_CD) AS BLCK_TP_CD
             , A.CUST_SEQ
             , A.USER_SEQ
             , A.USER_ID             
             , A.SNDRNO
             , A.CUST_NM
             , A.BIZRNO
             , A.RCVNO
             ,(SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD = 'SNDBLCK_RSN_CD' AND DTL_CD = A.BLCK_RSN_CD) AS BLCK_RSN_CD
             , DATE_FORMAT(A.BLCK_DT, '%Y-%m-%d') AS BLCK_DT
             , (SELECT B.USER_ID FROM hubez_common.EZ_SVC_USER B WHERE B.USER_SEQ = (SELECT esu2.PRNTS_USER_SEQ FROM hubez_common.EZ_SVC_USER esu2 WHERE esu2.USER_SEQ = A.USER_SEQ LIMIT 1)) AS SERVICE_ID
        FROM
            (
                SELECT
                    SEQ_NO
                     ,ewb.BLCK_TP_CD
                     , CUST_SEQ
                     , USER_SEQ
                     , USER_ID
                     , SNDRNO
                     , CUST_NM
                     , BIZRNO
                     , RCVNO
                     , ewb.BLCK_RSN_CD
                     , ewb.BLCK_DT                     
                FROM
                    hubez_common.EZ_WEBSND_BLCK ewb
                WHERE 1 = 1
                AND ewb.BLCK_DT <![CDATA[ >= ]]> #{blckDt}
                and ewb.BLCK_DT <![CDATA[ <  ]]>  DATE_ADD(#{blckDt}, INTERVAL 1 DAY)
                <include refid="websndBlckListCondition"/>
                ORDER BY ewb.BLCK_DT DESC
                LIMIT #{page}, #{pagePerRows}
            ) A
           , ( SELECT @ROWNUM := #{page} ) AS R
    </select>

    <sql id="websndBlckListCondition">
        <if test="blckRsnCd != null and blckRsnCd != ''">
            AND BLCK_RSN_CD = #{blckRsnCd}
        </if>
        <if test="blckTpCd != null and blckTpCd != ''">
            AND BLCK_TP_CD = #{blckTpCd}
        </if>
        <if test="sndrno != null and sndrno != ''">
            AND SNDRNO LIKE CONCAT('%', #{sndrno},'%')
        </if>
        <if test="rcvno != null and rcvno != ''">
            AND RCVNO LIKE CONCAT('%', #{rcvno},'%')
        </if>
        <if test="searchType1 != null and searchType1 != ''">
            <if test="searchType1 == '01' and searchText1 != null and searchText1 != ''">
                AND CUST_NM LIKE CONCAT('%', #{searchText1}, '%')
            </if>
            <if test="searchType1 == '02' and searchText1 != null and searchText1 != ''">
                AND BIZRNO LIKE CONCAT('%', #{searchText1},'%')
            </if>
            <if test="searchType1 == '03' and searchText1 != null and searchText1 != ''">
                AND USER_ID LIKE CONCAT('%', #{searchText1},'%')
            </if>
        </if>
    </sql>
</mapper>