<?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.gyee.wisdom.alarm.sharding.mapper.AlarmSnapMapper">

    <select id="queryAll" parameterType="java.util.Map" resultType="com.gyee.wisdom.alarm.sharding.entity.AlarmSnap">
        select * from  ALARMSNAP a
        <where>
            1=1
            <if test="starttime !=null and endtime !=null">
                and lastupdatetime &gt;= #{starttime,jdbcType=DATE} and lastupdatetime &lt;= #{endtime,jdbcType=DATE}
            </if>
            <if test="stid !=null and stid !=''">
                and a.stationid=#{stid}
            </if>
            <if test="wtid !=null and wtid !=''">
                and a.windturbineid=#{wtid}
            </if>
            <if test="category1 !=null and category1 !=''">
                and a.category1=#{category1}
            </if>
            <if test="category2 !=null and category2 !=''">
                and a.category2=#{category2}
            </if>
            <if test="rank !=null and rank !=''">
                and a.rank=#{rank}
            </if>
            <if test="modelid !=null and modelid !=''">
                and a.modelid=#{modelid}
            </if>
            <if test="isopened !=null ">
                and a.isopened=#{isopened}
            </if>
            <if test="keyword !=null and keyword !=''">
                and a.alerttext like '%${keyword}%'
            </if>
        </where>
        order by a.lastupdatetime  desc
    </select>

    <select id="pageQueryAll" parameterType="java.util.Map" resultType="com.gyee.wisdom.alarm.sharding.entity.AlarmSnap">
        select * from  ALARMSNAP a
        <where>
            1=1
            <if test="starttime !=null and endtime !=null">
                and lastupdatetime &gt;= #{starttime,jdbcType=DATE} and lastupdatetime &lt;= #{endtime,jdbcType=DATE}
            </if>
            <if test="stid !=null and stid !=''">
                and a.stationid=#{stid}
            </if>
            <if test="wtid !=null and wtid !=''">
                and a.windturbineid=#{wtid}
            </if>
            <if test="category1 !=null and category1 !=''">
                and a.category1=#{category1}
            </if>
            <if test="category2 !=null and category2 !=''">
                and a.category2=#{category2}
            </if>
            <if test="rank !=null and rank !=''">
                and a.rank=#{rank}
            </if>
            <if test="modelid !=null and modelid !=''">
                and a.modelid=#{modelid}
            </if>
            <if test="isopened !=null ">
                and a.isopened=#{isopened}
            </if>
            <if test="keyword !=null and keyword !=''">
                and a.alerttext like '%${keyword}%'
            </if>
        </where>
        order by a.lastupdatetime  desc
    </select>

    <insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="false">
        insert into alarmsnap ( ID, STATIONID, PROJECTID, LINEID, WINDTURBINEID, ALERTVALUE, CATEGORY1, CATEGORY2, CATEGORY3, RANK, ISOPENED, LASTUPDATETIME, STATIONNAME, PROJECTNAME, LINENAME, WINDTURBINENAME, ALERTTEXT, MODELID, TESTINGPOINTKEY, DATAINFO )
        <foreach collection="list" item="item" index="index" separator="union all" >
            ( select
                #{item.id},#{item.stationId},#{item.projectId},#{item.lineId},#{item.windturbineId},
                #{item.alertValue},#{item.category1},#{item.category2},#{item.category3},#{item.rank},
                #{item.isOpened},#{item.lastUpdateTime},#{item.stationName},#{item.projectName},
                #{item.lineName},#{item.windturbineName},#{item.alertText},#{item.modelId},
                #{item.testingpointKey},#{item.dataInfo}
            from dual )
        </foreach>
    </insert>

    <update id="batchUpdate" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            UPDATE ALARMSNAP
            <set>
                <if test="item.isOpened != null">ISOPENED = #{item.isOpened}</if>
                <if test="item.lastUpdateTime != null">,LASTUPDATETIME = #{item.lastUpdateTime}</if>
                <if test="item.category3 != null">,CATEGORY3 = #{item.category3}</if>
                <if test="item.dataInfo != null">,DATAINFO = #{item.dataInfo}</if>
            </set>
            <where>
                <if test="item.id != null">
                    and id = #{item.id}
                </if>
            </where>
        </foreach>
    </update>

    <select id="findByStationIdAndAlertValue" parameterType="java.util.Map" resultType="com.gyee.wisdom.alarm.sharding.entity.AlarmSnap">
        select * from  ALARMSNAP a
        <where>
            a.stationid=#{stationid} and a.alertvalue = #{alertvalue}  limit 1
        </where>
    </select>

    <select id="findByWindturbineIdAndAlertValue" parameterType="java.util.Map" resultType="com.gyee.wisdom.alarm.sharding.entity.AlarmSnap">
        select * from  ALARMSNAP a
        <where>
            a.windturbineid=#{windturbineid} and a.alertvalue = #{alertvalue}  limit  1
        </where>
    </select>

    <select id="findByTestingpointkey" parameterType="java.util.Map" resultType="com.gyee.wisdom.alarm.sharding.entity.AlarmSnap">
        select * from  ALARMSNAP a
        <where>
            a.testingpointkey=#{testingpointkey}  limit  1
        </where>
    </select>

    <select id="getTopNumAlarm" parameterType="java.util.Map" resultType="com.gyee.wisdom.alarm.sharding.entity.AlarmSnap">
        select * from ( select * from alarmsnap h order by h.lastupdatetime desc) t
        where t.category1 = #{category1}  limit 0,#{topnum}
    </select>

    <select id="getRecentAlarm" parameterType="java.util.Map" resultType="com.gyee.wisdom.alarm.sharding.entity.AlarmSnap">
        SELECT * FROM alarmsnap
        WHERE category1 = #{category1} and lastupdatetime >= #{lastupdatetime}
        order by lastupdatetime desc
    </select>

</mapper>
