<?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="cn.iocoder.yudao.module.system.dal.mysql.robot.RobotWarnMsgMapper">

    <!--
        一般情况下，尽可能使用 Mapper 进行 CRUD 增删改查即可。
        无法满足的场景，例如说多表关联查询，才使用 XML 编写 SQL。
        代码生成器暂时只生成 Mapper XML 文件本身，更多推荐 MybatisX 快速开发插件来生成查询。
        文档可见：https://www.iocoder.cn/MyBatis/x-plugins/
     -->

    <update id="updateAllRead">
        update
            robot_warn_msg
        set read_status = '1',
            read_time   = now()
        where read_status = '0' LIMIT 1000
    </update>


    <select id="getFourLevelWarnMsgByDetailIds"
            resultType="cn.iocoder.yudao.module.system.dal.dataobject.robot.RobotWarnMsgDO">
        SELECT
        t1.*
        FROM
        robot_warn_msg t1
        WHERE
        ( robot_no, create_time ) IN ( SELECT robot_no, MAX( create_time )
        FROM
        robot_warn_msg
        where
        deleted = '0'
        and warn_level = '4'
        and task_detail_id
        in
        <foreach collection="detailIds" item="taskDetailId" index="index" open="(" close=")"
                 separator=",">
            #{taskDetailId}
        </foreach>
        GROUP BY robot_no )
    </select>

    <select id="getRobotWarnMsgClassification"
            resultType="cn.iocoder.yudao.module.system.controller.admin.statistics.dto.RobotWarnMsgClassificationDTO">
        SELECT
        DATE_FORMAT( create_time, '%Y-%m-%d' ) AS warnTime,
        COUNT(*) AS num ,
        warn_level as warnLevel
        FROM
        robot_warn_msg
        <where>
            deleted = '0'
            AND warn_level != ''
            <choose>
                <when test="type == 1">
                    AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) &lt;= date(create_time)
                </when>
            </choose>
        </where>
        GROUP BY warnTime ,warn_level
    </select>
    <select id="getRobotWarnMsgByTime"
            resultType="cn.iocoder.yudao.module.system.controller.admin.statistics.dto.RobotWarnMsgClassificationDTO">
        SELECT
            COUNT(*) AS num ,
            warn_level as warnLevel
        FROM
            robot_warn_msg
        <where>
            deleted = '0'
            AND warn_level != ''
            <if test="startTime!=null">
                and date_format(create_time,'%Y-%m-%d') <![CDATA[<]]> date_format(#{startTime},'%Y-%m-%d')
            </if>
            <if test="endTime!=null ">
                and date_format(create_time,'%Y-%m-%d') <![CDATA[>=]]> date_format(#{endTime},'%Y-%m-%d')
            </if>
        </where>
        GROUP BY warn_level
    </select>
</mapper>