<?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="uml.tech.bigdata.mapper.AlarmMapper">
    <update id="dropTable">
        drop table if exists ${tableName}
    </update>
    <update id="createAlarmTable">
        create table if not exists ${tableName}
        (
            `id` bigint
        (
            64
        ) comment '唯一id',
            `did` varchar
        (
            20
        ) comment '设备号',
            `vin` varchar
        (
            50
        ) comment '车架号',
            `code` varchar
        (
            32
        ) comment '报警编码',
            `name` varchar
        (
            500
        ) comment '报警名称',
            `type` varchar
        (
            20
        ) comment '报警类型',
            `level` int comment '报警级别',
            `start_lon` double comment '报警开始经度',
            `start_lat` double comment '报警开始纬度',
            `end_lon` double comment '报警结束经度',
            `end_lat` double comment '报警结束纬度',
            `address` varchar(256) comment '报警详细地址',
            `start_time` datetime NOT NULL comment '报警开始时间',
            `end_time` datetime comment '报警结束时间',
            `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP comment '报警更新时间'
            ) comment '非道路报警表';
    </update>

    <insert id="insertAlarmData" parameterType="uml.tech.bigdata.bean.AlarmBean">
        insert into ${tableName}
        (id, did,vin,code,name,type,level,start_lon,start_lat,end_lon,end_lat,start_time,end_time,update_time)
        values
        <foreach collection="list" index="index" item="alarmBean" separator=",">
            (
            #{alarmBean.id},
            #{alarmBean.did},
            #{alarmBean.vin},
            #{alarmBean.code},
            #{alarmBean.name},
            #{alarmBean.type},
            #{alarmBean.level},
            #{alarmBean.startLon},
            #{alarmBean.startLat},
            #{alarmBean.endLon},
            #{alarmBean.endLat},
            #{alarmBean.startTime},
            #{alarmBean.endTime},
            now()
            )
        </foreach>
    </insert>

    <insert id="insertFromDay" parameterType="uml.tech.bigdata.bean.AlarmBean">
        insert into ${tableName}
        select *
        from ${fromTableName}
    </insert>

    <update id="delExpiredByEndTime">
        delete
        from ${tableName}
        where end_time &lt; #{endTime}
    </update>

    <update id="delExpiredByUpdateTime">
        delete
        from ${tableName}
        where end_time &lt;= #{updateTime}
    </update>

    <insert id="insertFromAlarm" parameterType="uml.tech.bigdata.bean.AlarmBean">
        insert into ${tableName}
        select *
        from agri_alarm
        where end_time between #{startTime} and #{endTime}
    </insert>

    <update id="delByDay">
        delete
        from ${tableName}
        where end_time between #{startTime} and #{endTime}
    </update>

</mapper>