<?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.ytpm.dao.RiskMapper">
    <resultMap id="RiskTemplateViewMap" type="com.ytpm.risk.view.RiskTemplateView">
        <id column="template_id" property="templateId" />
        <result column="template_code" property="templateCode" />
        <result column="template_name" property="templateName" />
        <result column="template_content" property="templateContent" />
        <result column="enabled" property="enabled" />
        <result column="all_satisfy" property="allSatisfy" />
        <result column="effect_node" property="effectNode" />
        <collection property="configList" ofType="com.ytpm.risk.view.RiskConfigView">
            <result column="config_id" property="configId" />
            <result column="field_name" property="fieldName" />
            <result column="field_desc" property="fieldDesc" />
            <result column="config_type" property="configType" />
            <result column="config_val" property="configVal" />
            <result column="multy" property="multy" />
        </collection>
    </resultMap>
    <insert id="addBannedRecord">
        insert into yt_platform_banned
        (
         banned_id,
         user_id,
         app_id,
         channel_id,
         agent_id,
         banned_time,
         banned_reason,
         banned_type,
         banned_limit,
         operator,
         operator_name
        )
        values
        (
         #{bannedId},
         #{userId},
         #{appId},
         #{channelId},
         #{agentId},
         #{bannedTime},
         #{bannedReason},
         #{bannedType},
         #{bannedLimit},
         #{operator},
         #{operatorName}
        );
    </insert>
    <insert id="addDeblockingRecord">
        insert into yt_platform_deblocking
        (
            deblocking_id,
            banned_id,
            user_id,
            app_id,
            channel_id,
            agent_id,
            deblocking_time,
            deblocking_reason,
            operator,
            operator_name
        )
        values
            (
                #{deblockingId},
                #{bannedId},
                #{userId},
                #{appId},
                #{channelId},
                #{agentId},
                #{deblockingTime},
                #{deblockingReason},
                #{operator},
                #{operatorName}
            )
    </insert>
    <select id="getByCode" resultMap="RiskTemplateViewMap">
        SELECT
            rt.template_id,
            rt.template_code,
            rt.template_name,
            rt.template_content,
            rt.effect_node,
            rt.enabled,
            rc.config_id,
            rc.field_name,
            rc.field_desc,
            rc.config_type,
            rc.config_val,
            rc.multy
        FROM
            yt_risk_template rt
                LEFT JOIN yt_risk_template_config rtc ON rt.template_id = rtc.template_id
                LEFT JOIN yt_risk_config rc ON rtc.config_id = rc.config_id
        WHERE
            rt.template_code = #{code}
    </select>
    <select id="getRiskConfigList" resultType="com.ytpm.risk.view.RiskConfigListView">
        SELECT
        group_concat(ca.app_id) appIds,
        group_concat(ca.app_name) appNames,
        t.template_name,
        t.create_time,
        t.template_id,
        t.template_code,
        t.template_content,
        u.nick_name as nickName,
        t.enabled,
        t.effect_node,
        t.all_satisfy,
        t.can_modify,
        t.update_time,
        u_update.nick_name as updateNickName
        FROM
        yt_risk_template t
        JOIN yt_platform_user u on t.create_user_id = u.user_id
        LEFT JOIN yt_cofig_app ca ON t.template_id = ca.template_id
        LEFT JOIN yt_platform_user u_update ON t.update_user_id = u_update.user_id
        WHERE
        t.channel_id = #{channelId}
        <if test="appId != null and appId!= ''">
            and ca.app_id = #{appId}
        </if>
        group by t.template_id
    </select>
    <select id="getBannedList" resultType="com.ytpm.risk.view.RiskBannedListView">
        SELECT
        pb.banned_id,
        pb.banned_time,
        pb.banned_reason,
        pb.banned_type,
        pb.banned_limit,
        pb.operator,
        pb.operator_name,
        pb.user_id,
        pb.agent_id,
        a.app_id,
        a.app_name,
        a.ditch_id,
        a.ditch_name
        FROM
        yt_platform_banned pb
        JOIN yt_app a ON pb.app_id = a.app_id
        WHERE
        pb.agent_id = #{agentId}
        <if test="appId != null and appId != ''">
            and a.app_id = #{appId}
        </if>
        <if test="userId != null and userId != ''">
            and pb.user_id = #{userId}
        </if>
        <if test="channelOrigin != null">
            and a.ditch_id = #{channelOrigin}
        </if>
        <if test="bannedReason != null and bannedReason != ''">
            and pb.banned_reason like concat('%',#{bannedReason},'%')
        </if>
        <if test="bannedTimeBegin != null">
            and DATE_FORMAT(pb.banned_time,'%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{bannedTimeBegin}, '%Y-%m-%d')
        </if>
        <if test="bannedTimeEnd != null">
            and DATE_FORMAT(pb.banned_time,'%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(#{bannedTimeEnd}, '%Y-%m-%d')
        </if>
        ORDER BY pb.banned_time DESC
    </select>
    <select id="getDeblockingList" resultType="com.ytpm.risk.view.RiskDeblockingListView">
        SELECT
        pd.deblocking_id,
        pd.banned_id,
        pd.user_id,
        pd.agent_id,
        pd.channel_id,
        pd.deblocking_time,
        pd.deblocking_reason,
        pd.operator,
        pd.operator_name,
        a.app_id,
        a.app_name,
        a.ditch_id,
        a.ditch_name
        FROM
        yt_platform_deblocking pd
        JOIN yt_app a ON pd.app_id = a.app_id
        WHERE
        pd.agent_id = #{agentId}
        <if test="appId != null and appId != ''">
            and a.app_id = #{appId}
        </if>
        <if test="userId != null and userId != ''">
            and pd.user_id = #{userId}
        </if>
        <if test="channelOrigin != null">
            and a.ditch_id = #{channelOrigin}
        </if>
        <if test="deblockingReason != null and deblockingReason != ''">
            and pd.deblocking_reason like concat('%',#{deblockingReason},'%')
        </if>
        <if test="deblockingTimeBegin != null">
            and DATE_FORMAT(pd.deblocking_time,'%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{deblockingTimeBegin}, '%Y-%m-%d')
        </if>
        <if test="deblockingTimeEnd != null">
            and DATE_FORMAT(pd.deblocking_time,'%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(#{deblockingTimeEnd}, '%Y-%m-%d')
        </if>
        ORDER BY pd.deblocking_time DESC
    </select>
    <select id="getLastBanned" resultType="com.ytpm.agent.model.YtPlatformBanned">
        select
            banned_id, app_id, user_id, channel_id,agent_id, banned_time, banned_reason, banned_type, banned_limit, operator, operator_name
        from yt_platform_banned
        where user_id = #{userId}
        order by banned_time desc
        limit 1
    </select>
    <insert id="relativeApps" parameterType="com.ytpm.risk.param.RiskRelativeAppParam">
        insert into yt_cofig_app
        (
        template_id,
        app_id,
        app_name,
        operator,
        operator_name,
        operator_time
        )
        values
        <foreach collection="appList" separator="," index="index" item="item">
            (
            #{templateId},
            #{item.appId},
            #{item.appName},
            #{operator},
            #{operatorName},
            now()
            )
        </foreach>
    </insert>
    <delete id="deleteRelatives">
        delete from yt_cofig_app where template_id = #{templateId}
    </delete>
    <insert id="batchInsertConfig">
        insert into yt_risk_config
        (
        config_id,
        field_name,
        field_desc,
        config_type,
        config_val,
        multy,
        channel_id
        )
        values
        <foreach collection="configs" separator="," item="item" index="index">
            (
            #{item.configId},
            #{item.fieldName},
            #{item.fieldDesc},
            #{item.configType},
            #{item.configVal},
            #{item.multy},
            #{item.channelId}
            )
        </foreach>
    </insert>
    <insert id="saveTemplate">
        insert into yt_risk_template
        (
            template_id,
            template_name,
            template_content,
            template_code,
            effect_node,
            channel_id,
            all_satisfy,
            app_id,
            create_time,
            create_user_id,
            enabled
        )
        values
            (
                #{templateId},
                #{templateName},
                #{templateContent},
                #{templateCode},
                #{effectNode},
                #{channelId},
                #{allSatisfy},
                #{appId},
                #{createTime},
                #{createUserId},
                #{enabled}
            );
    </insert>
    <insert id="addRelease">
        insert into yt_risk_template_config
        (
        template_id,
        config_id,
        operator,
        operator_time,
        operator_name
        )
        values
        <foreach collection="configIds" item="item" separator="," index="index">
            (
            #{templateId},
            #{item},
            #{operator},
            now(),
            #{operatorName}
            )
        </foreach>
    </insert>

    <select id="selectOneTemplate" resultType="com.ytpm.risk.model.YtRiskTemplate">
        select
            template_id, template_name, template_content, template_code,effect_node, channel_id, app_id, create_time, create_user_id, update_time, update_user_id, enabled
        from yt_risk_template
        where template_id = #{templateId}
    </select>

    <select id="getAppClazz" resultType="com.ytpm.agent.view.AgentAppClassView">
        select
        app_id, full_name, class_desc, enabled
        from yt_app_class
        where enabled = 1
        <if test="appIds != null and appIds != ''">
            and app_id in
            <foreach collection="appIds.split(',')" item="item" separator="," open="(" close=")" >
                #{item}
            </foreach>
        </if>
    </select>

    <update id="updateConfigVal">
        update yt_risk_config set config_val = #{configVal} where config_id = #{configId}
    </update>

    <update id="updateByTempId">
        update yt_risk_template
        <set>
            <if test="templateName != null">
                template_name = #{templateName},
            </if>
            <if test="templateContent != null">
                template_content = #{templateContent},
            </if>
            <if test="templateCode != null">
                template_code = #{templateCode},
            </if>
            <if test="channelId != null">
                channel_id = #{channelId},
            </if>
            <if test="effectNode != null">
                effect_node = #{effectNode},
            </if>
            <if test="appId != null">
                app_id = #{appId},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
            <if test="updateUserId != null">
                update_user_id = #{updateUserId},
            </if>
            <if test="enabled != null">
                enabled = #{enabled},
            </if>
        </set>
        where template_id = #{templateId}
    </update>

    <select id="getTemplateView" resultMap="RiskTemplateViewMap">
        SELECT
            rt.template_id,
            rt.template_code,
            rt.template_name,
            rt.template_content,
            rt.effect_node,
            rt.enabled,
            rt.all_satisfy,
            rc.config_id,
            rc.field_name,
            rc.field_desc,
            rc.config_type,
            rc.config_val,
            rc.multy
        FROM
            yt_risk_template rt
                LEFT JOIN yt_risk_template_config rtc ON rt.template_id = rtc.template_id
                LEFT JOIN yt_risk_config rc ON rtc.config_id = rc.config_id
        WHERE
            rt.template_id = #{templateId}
          AND rt.channel_id = #{channelId}
    </select>
    <select id="queryBannedCount" resultType="java.lang.Integer">
        select
            count(distinct user_id)
        from yt_platform_banned
        <where>
            <if test="null!= appIdList and appIdList.size()>0">
                and app_id in
                <foreach collection="appIdList" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <choose>
                <when test="type != null and type ==1">
                    and DATE(banned_time) = DATE(now())
                </when>
                <when test="type != null and type ==2">
                    and DATE(banned_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
                </when>
                <when test="type != null and type ==3">
                    and DATE_FORMAT(banned_time, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')
                </when>
            </choose>
        </where>
    </select>
    <select id="queryMonthBanned" resultType="com.ytpm.agent.model.YtPlatformBanned">
        select
            banned_id, user_id, app_id, channel_id, agent_id, banned_time, banned_reason, banned_type, banned_limit, operator, operator_name
        from yt_platform_banned
        <where>
            DATE_FORMAT(banned_time, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')
            <if test="null!= appIdList and appIdList.size()>0">
                and app_id in
                <foreach collection="appIdList" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
</mapper>