<?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.logs.dao.SysOperLogMapper">

    <sql id="selectOperLogVo">
        select oper_id, title, business_type, method, request_method, operator_type, oper_name, dept_name, oper_url, oper_ip, oper_param, json_result, status, error_msg, oper_time
        from sys_oper_log
    </sql>

    <sql id="where">
        <where>
            a.opersTime BETWEEN CONCAT(#{params.startTime},' 00:00:00') and CONCAT(#{params.endTime},' 23:59:59')
            <if test="params != null">
                <if test="params.type != null and params.type != ''">
                     and a.operatoType = #{params.type}
                </if>
                <if test="params.module != null and params.module != ''">
                    and locate(#{params.module},a.title) > 0
                </if>
                <if test="params.result != null and params.result != ''">
                    and locate(#{params.result},a.jsonResult) > 0
                </if>
                <if test="params.keyWord != null and params.keyWord != ''">
                    and (
                    a.title like concat('%', #{params.keyWord}, '%') or
                    a.type like concat('%', #{params.keyWord}, '%') or
                    a.operName like concat('%', #{params.keyWord}, '%') or
                    a.phone like concat('%', #{params.keyWord}, '%')
                    )
                </if>
            </if>
        </where>
    </sql>

    <insert id="insertOperlog">
		insert into sys_oper_log(title, user_id,business_type, method, request_method, operator_type, oper_url, oper_ip, oper_param, json_result, status, error_msg)
        values (#{title}, #{userId}, #{businessType}, #{method}, #{requestMethod}, #{operatorType}, #{operUrl}, #{operIp}, #{operParam}, #{jsonResult}, #{status}, #{errorMsg})
	</insert>

    <select id="selectOperLogList" resultType="com.bonus.system.api.domain.SysOperLog">
        <include refid="selectOperLogVo"/>
        <where>
            <if test="title != null and title != ''">
                AND title like concat('%', #{title}, '%')
            </if>
            <if test="businessType != null">
                AND business_type = #{businessType}
            </if>
            <if test="businessTypes != null and businessTypes.length > 0">
                AND business_type in
                <foreach collection="businessTypes" item="businessType" open="(" separator="," close=")">
                    #{businessType}
                </foreach>
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
            <if test="operName != null and operName != ''">
                AND oper_name like concat('%', #{operName}, '%')
            </if>
            <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
                and date_format(oper_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
            </if>
            <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
                and date_format(oper_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
            </if>
        </where>
        order by oper_id desc
    </select>

    <delete id="deleteOperLogByIds" parameterType="Long">
        delete from sys_oper_log where oper_id in
        <foreach collection="array" item="operId" open="(" separator="," close=")">
            #{operId}
        </foreach>
    </delete>

    <select id="selectOperLogById" parameterType="Long" resultType="com.bonus.system.api.domain.SysOperLog">
        <include refid="selectOperLogVo"/>
        where oper_id = #{operId}
    </select>

    <select id="count" resultType="java.lang.Integer">
        SELECT
        count(1)
        FROM
        sys_oper_log sol
        where
        create_time BETWEEN CONCAT(#{params.startTime},' 00:00:00') and CONCAT(#{params.endTime},' 23:59:59')
        <if test="params.type != null and params.type != ''">
            and operator_type = #{params.type}
        </if>
        <if test="params.module != null and params.module != ''">
            and locate(#{params.module},title) > 0
        </if>
        <if test="params.result != null and params.result != ''">
            and locate(#{params.result},json_result) > 0
        </if>
    </select>

    <select id="list" resultType="com.bonus.system.api.domain.SysOperLog">
        select
        su.username AS operName,
        su.phone,
        a.*
        from(
        SELECT
        user_id,
        oper_id AS operId,
        title,
        json_result as jsonResult,
        CASE
        business_type
        WHEN 1 THEN '查询'
        WHEN 2 THEN '新增'
        WHEN 3 THEN '修改'
        WHEN 4 THEN '删除'
        WHEN 5 THEN '授权'
        WHEN 6 THEN '导出'
        WHEN 7 THEN '导入'
        WHEN 8 THEN '强退'
        WHEN 9 THEN '生成代码'
        WHEN 10 THEN '清空数据'
        ELSE '其他'
        END AS type,
        CASE
        operator_type
        WHEN 1 THEN '后台'
        WHEN 2 THEN 'APP'
        ELSE '其他'
        END AS operatoType ,
        create_time AS opersTime
        FROM
        sys_oper_log
        where
        create_time BETWEEN CONCAT(#{params.startTime},' 00:00:00') and CONCAT(#{params.endTime},' 23:59:59')
        <if test="params.type != null and params.type != ''">
            and operator_type = #{params.type}
        </if>
        <if test="params.module != null and params.module != ''">
            and locate(#{params.module},title) > 0
        </if>
        <if test="params.result != null and params.result != ''">
            and locate(#{params.result},json_result) > 0
        </if>
        ) a
        LEFT JOIN sys_user su ON su.id = a.user_id
        order by a.opersTime DESC
        <if test="offset != null and offset >= 0 and limit != null and limit >= 0">
            limit #{offset}, #{limit}
        </if>
    </select>

    <update id="cleanOperLog">
        truncate table sys_oper_log
    </update>

</mapper> 