<?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.njcn.mapper.log.UserLogDetailMapper">

<!-- 日志 -->
<resultMap type="com.njcn.pojo.log.UserLogDetail" id="UserLogDetailMapperMap" autoMapping="true">
	
</resultMap>
<!-- 查询所有日志 -->
<select id="getUserLog" resultMap="UserLogDetailMapperMap">
select t.dictype_name as type,
       a.dic_name as name,
       u.USERLOG_DESCRIBE as describe,
       u.updatetime as updatetime,
       u.ip as ip,
       decode(a.DIC_LEAVE, 0, '普通', 1, '中等',2,'严重') as leval
       from pqs_userlog u ,
            pqs_dicdata a ,
            pqs_dictype t
       where  u.type=a.dic_index
       and  a.dic_type=t.dictype_index
       order by u.updatetime desc
</select>
<!-- 根据条件查询所有日志 -->
<select id="getUserLogId" resultMap="UserLogDetailMapperMap">
select t.dictype_name as type,
       a.dic_name as name,
       u.USERLOG_DESCRIBE as describe,
       u.updatetime as updatetime,
       u.ip as ip,
       decode(a.DIC_LEAVE, 0, '普通', 1, '中等',2,'严重') as leval
       from pqs_userlog u ,
            pqs_dicdata a ,
            pqs_dictype t
       where  u.type=a.dic_index
       and  a.dic_type=t.dictype_index
       and  t.dictype_index like #{type}
       and  a.dic_index like #{name}
       and to_number(to_char(u.updatetime, 'yyyyMMddHHmiss')) between #{startTime}
		and #{endTime}
       order by u.updatetime desc
</select>
<!-- 统计事件类型 -->
<select id="geteventtype" resultMap="UserLogDetailMapperMap">
select t.dictype_name as type,
       count(1) as num
       from pqs_userlog u ,
            pqs_dicdata a ,
            pqs_dictype t
       where  u.type=a.dic_index
       and  a.dic_type=t.dictype_index
       and to_number(to_char(u.updatetime, 'yyyyMMddHHmmss')) between #{startTime}
		and #{endTime}
       group by t.dictype_name
</select>
<!-- 统计详细事件 -->
<select id="getdetailtype" resultMap="UserLogDetailMapperMap">
select a.dic_name as name,
       count(1) as num
       from pqs_userlog u ,
            pqs_dicdata a ,
            pqs_dictype t
       where  u.type=a.dic_index
       and  a.dic_type=t.dictype_index
       and to_number(to_char(u.updatetime, 'yyyyMMddHHmmss')) between #{startTime}
		and #{endTime}
       group by a.dic_name
</select>
<!-- 统计用户统计 -->
<select id="getUserStatistics" resultMap="UserLogDetailMapperMap">
select b.loginname name,
       count(1) num
from pqs_userlog a,
     pqs_user b 
where A.USERLOG_DESCRIBE like '%'||b.loginname||'%'
  and a.user_index=b.user_index
  and to_number(to_char(a.updatetime, 'yyyyMMddHHmmss')) between #{startTime}
		and #{endTime}
group by b.loginname
</select>
    
    <delete id="delExpireLog"  databaseId="Oracle">
    delete from pqs_userlog
	 where rowid = (select rowid
	                  from (select UPDATETIME from pqs_userlog where UPDATETIME &lt; #{endTime} order by UPDATETIME)
	                 where rownum = 1)
    </delete>
        
    <delete id="delExpireLog"  databaseId="MariaDB">
     delete from pqs_userlog
	 where UPDATETIME = (select UPDATETIME from pqs_userlog where UPDATETIME &lt; #{endTime} order by UPDATETIME LIMIT 1 )
    </delete>
</mapper>