<?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.bonus.system.mapper.SysLogMapper">

    <!--保存系统日志-->
    <insert id="saveLogs" parameterType="SysLogsVo">
        insert into  sys_logs(
        <if test=" logId!= null and logId != ''">log_id,</if>
        <if test=" operaUserName!= null">opera_user_name,</if>
        <if test=" ip!= null">ip,</if>
        <if test=" userId!= null and userId != 0">user_id,</if>
        <if test=" model!= null">model,</if>
        <if test=" operTime!= null">oper_time,</if>
        <if test=" method!= null">method,</if>
        <if test=" params!= null">params,</if>
        <if test=" operateDetail!= null">operate_detail,</if>
        <if test=" operaType!= null">oper_type,</if>
        <if test=" operUri!= null">oper_uri,</if>
        <if test=" logType!= null">log_type,</if>
        <if test=" result!= null">result,</if>
        <if test=" times!= null">times,</if>
        <if test=" failureReason!= null">failure_reason,</if>
        <if test=" grade!= null">grade,</if>
        <if test=" errType!= null">err_type,</if>
        <if test=" methodType!= null">method_type,</if>
        <if test=" title!= null">title,</if>
        <if test=" resultData!= null">result_data,</if>
        <if test=" warningStatus!= null">warning_status,</if>
        )values (
        <if test=" logId!= null and logId != ''">{logId},</if>
        <if test=" operaUserName!= null">{operaUserName},</if>
        <if test=" ip!= null">{ip},</if>
        <if test=" userId!= null and userId != 0">{userId},</if>
        <if test=" model!= null">{model},</if>
        <if test=" operTime!= null">{operTime},</if>
        <if test=" method!= null">{method},</if>
        <if test=" params!= null">{params},</if>
        <if test=" operateDetail!= null">{operateDetail},</if>
        <if test=" operaType!= null">{operaType},</if>
        <if test=" operUri!= null">{operUri},</if>
        <if test=" logType!= null">{logType},</if>
        <if test=" result!= null">{result},</if>
        <if test=" times!= null">{times},</if>
        <if test=" failureReason!= null">{failureReason},</if>
        <if test=" grade!= null">{grade},</if>
        <if test=" errType!= null">{errType},</if>
        <if test=" methodType!= null">{methodType},</if>
        <if test=" title!= null">{title},</if>
        <if test=" resultData!= null">{resultData},</if>
        <if test=" warningStatus!= null">{warningStatus},</if>
    </insert>

    <update id="updateWarningStatus" parameterType="SysLogsVo">
        update sys_warning
        set warning_status = #{warningStatus}
        where log_id = #{logId}
    </update>
<!--    <insert id="saveLogs">-->
<!--        INSERT INTO sys_logs-->
<!--        <trim prefix="(" suffix=")" suffixOverrides=",">-->
<!--            <if test="logId != null and logId != ''">log_id,</if>-->
<!--            <if test="ip != null and ip!=''">ip,</if>-->
<!--            <if test="userId != null ">user_id,</if>-->
<!--            <if test="model != null and model!=''">model,</if>-->
<!--            <if test="operaUserName != null and operaUserName!=''">opera_user_name,</if>-->
<!--            <if test="operTime != null and operTime!=''">oper_time,</if>-->
<!--            <if test="method != null and method!=''">method,</if>-->
<!--            <if test="params != null and params!=''">params,</if>-->
<!--            <if test="operateDetail != null and operateDetail!=''">operate_detail,</if>-->
<!--            <if test="operType != null and operType!=''">oper_type,</if>-->
<!--            <if test="operUri != null and operUri!=''">oper_uri,</if>-->
<!--            <if test="logType != null">log_type,</if>-->
<!--            <if test="fruit != null">result,</if>-->
<!--            <if test="times != null and times!=''">times,</if>-->
<!--            <if test="failureReason != null and failureReason!=''">failure_reason,</if>-->
<!--            <if test="grade != null and grade!=''">grade,</if>-->
<!--            <if test="errType != null and errType!=''">err_type,</if>-->
<!--        </trim>-->
<!--        <trim prefix="values (" suffix=")" suffixOverrides=",">-->
<!--            <if test="logId != null and logId != ''">#{logId},</if>-->
<!--            <if test="ip != null and ip!=''">#{ip},</if>-->
<!--            <if test="userId != null">#{userId},</if>-->
<!--            <if test="model != null and model!=''">#{model},</if>-->
<!--            <if test="operaUserName != null and operaUserName!=''">#{operaUserName},</if>-->
<!--            <if test="operTime != null and operTime!=''">#{operTime},</if>-->
<!--            <if test="method != null and method!=''">#{method},</if>-->
<!--            <if test="params != null and params!=''">#{params},</if>-->
<!--            <if test="operateDetail != null and operateDetail!=''">#{operateDetail},</if>-->
<!--            <if test="operType != null and operType!=''">#{operType},</if>-->
<!--            <if test="operUri != null and operUri!=''">#{operUri},</if>-->
<!--            <if test="logType != null">#{logType},</if>-->
<!--            <if test="fruit != null">#{fruit},</if>-->
<!--            <if test="times != null and times!=''">#{times},</if>-->
<!--            <if test="failureReason != null and failureReason!=''">#{failureReason},</if>-->
<!--            <if test="grade != null and grade!=''">#{grade},</if>-->
<!--            <if test="errType != null and errType!=''">#{errType},</if>-->
<!--        </trim>-->
<!--    </insert>-->
    <insert id="setLogsSet">
         insert into sys_logs_set(capacity) value(#{capacity})
    </insert>

    <update id="updateLogsSet">
         update sys_logs_set set capacity=#{capacity}
    </update>
    <select id="getSystemLogs" resultType="com.bonus.system.api.domain.SysLogsVo">
        SELECT
            log_id,
            opera_user_name operaUserName,
            ip,
            user_id userId,
            model,
            oper_time operTime,
            method,
            params,
            operate_detail operateDetail,
            oper_type operType,
            oper_uri operUri,
            log_type logType,
            result,
            grade,
            err_type errType,
            method_type methodType,
            times
        from sys_logs
            where    log_type=#{logType}
            <if test="operaUserName!=null and operaUserName!=''">
                and opera_user_name LIKE concat('%',#{operaUserName},'%')
            </if>
            <if test="model!=null and model!=''">
                and model LIKE concat('%',#{model},'%')
            </if>
            <if test="operType!=null and operType !=''">
                 and oper_type LIKE concat('%',#{operType},'%')
            </if>
            <if test="params!=null and params !=''">
                and params LIKE concat('%',#{params},'%')
            </if>

            <if test="errType!=null and errType!=''">
                and err_type=#{errType}
            </if>
            <if test="grade!=null and grade!=''">
                and grade=#{grade}
            </if>
            <if test="endTime!=null and endTime!=''">
                and  oper_time  BETWEEN concat(#{startTime}, ' 00:00:00') AND concat(#{endTime},' 23:59:59')
            </if>
            <if test="ip !=null and ip!=''">
                and ip LIKE concat('%',#{ip},'%')
            </if>
            <if test="result !=null and result !=''">
                and result LIKE concat('%',#{result},'%')
            </if>
        <if test="logSort!=null  and  logDesc !=null and  logSort!='' and logDesc!='' ">
            order by
                <if test='logSort=="1"'>
                        oper_time
                </if>
                <if test='logSort=="2"'>
                        opera_user_name
                </if>
                <if test='logSort=="3"'>
                       model
                </if>
                <if test='logSort=="4"'>
                         ip
                </if>
                <if test='logSort=="5"'>
                    oper_type
                </if>
                <if test='logDesc=="1"'>
                    COLLATE utf8mb4_unicode_ci DESC
                </if>
                <if test='logDesc=="2"'>
                    COLLATE utf8mb4_unicode_ci ASC
                </if>
        </if>
    </select>
    <select id="getLogsSet" resultType="java.lang.String">
        select  capacity
                 from sys_logs_set
    </select>
    <select id="getLogStatistics" resultType="java.lang.Integer">
        select  COUNT(1) num
            FROM sys_logs
            where  log_type=#{logType}
            <if test="endTime!=null and endTime!=''">
                and  oper_time  BETWEEN concat(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime},' 23:59:59')
            </if>
    </select>
    <select id="getLogsListByOperType" resultType="com.bonus.system.api.domain.SysLogsVo">
        select  COUNT(1) num,oper_type operType
            FROM sys_logs
            WHERE  oper_type IS NOT NULL
                <if test="endTime!=null and endTime!=''">
                    and  oper_time  BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime},' 23:59:59')
                </if>
            GROUP  by oper_type
    </select>
    <select id="getLogsListByOperUserName" resultType="com.bonus.system.api.domain.SysLogsVo">
        select  COUNT(1) num,opera_user_name operaUserName
        FROM sys_logs
        WHERE  opera_user_name IS NOT NULL
        <if test="endTime!=null and endTime!=''">
            and  oper_time  BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime},' 23:59:59')
        </if>
        GROUP  by opera_user_name
    </select>

    <!--查询模块-->
    <select id="getModule" resultType="com.bonus.system.api.domain.SysLogsVo">
        select  CONCAT(IFNULL(sm.menu_name,''),'->',IFNULL(sm2.menu_name,''))  module ,sm3.menu_name operateDetail
        from  sys_menu  sm
        left join  sys_menu sm2 on sm2.p_id=sm.menu_id  and sm2.menu_type=1
        left join  sys_menu sm3 on sm3.p_id=sm2.menu_id   and sm3.menu_type=2
        where sm3.menu_auth=#{module}
        limit 1
    </select>
    <select id="getErrorLogs" resultType="java.lang.Integer">
        select count(1)
            from sys_logs
            where log_type=2
            and   DATE_FORMAT(oper_time,'%Y-%m-%d')=CURRENT_DATE
    </select>
    <select id="getLogsRl" parameterType="java.lang.String" resultType="java.lang.String" >
        SELECT
            round(((data_length + index_length) / 1024 / 1024 / 1024), 2) AS 'Size in GB'
            FROM information_schema.TABLES
        WHERE table_schema = #{schemaName} AND table_name = 'sys_logs'
    </select>

    <select id="getAllLogs" resultType="com.bonus.system.api.domain.SysLogsVo">
        select count(1) num,result
                from sys_logs
                 where   oper_time  BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime},' 23:59:59')
                GROUP BY result
    </select>

    <select id="getModuleName" resultType="java.lang.String">
        select  CONCAT(IFNULL(sm.menu_name,''),'->',IFNULL(sm2.menu_name,''))  module
            from  sys_menu  sm
            left join  sys_menu sm2 on sm2.p_id=sm.menu_id  and sm2.menu_type=1
            where sm2.menu_url=#{operUri}
            limit 1
    </select>

    <select id="getModuleIsc" resultType="java.lang.String">
        select menu_name
        from  sys_menu  sm
         where sm.menu_url=#{operUri}
        limit 1
    </select>
    <!--查询全部日志-->
    <select id="getLogsLists" resultType="com.bonus.system.api.domain.SysLogsVo">
        SELECT
            log_id logId,	opera_user_name operaUserName,ip,
            user_id userId,	model,	oper_time operTime,
            method,params ,operate_detail operateDetail,
            oper_type operType,oper_uri operUri,	log_type logType,
             result fruit,times,failure_reason failureReason,
            grade,err_type errType,	method_type methodType,
            title
        FROM  sys_logs
             where  log_type=#{logType}

    </select>

    <select id="getNotHandleWarningLog" resultType="com.bonus.system.api.domain.SysLogsVo">
        SELECT
        log_id logId, opera_user_name operaUserName,ip,
        user_id userId,	oper_time operTime,
        oper_type operType, err_type errType
        FROM  sys_logs
        where  warning_status=0
    </select>

    <update id="updateLogsWithHandledStatus">
        update sys_logs set warning_status=1 where log_id=#{logId}
    </update>


</mapper>
