<?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.pqs9900.mapper.message.MessageMapper">

    <select id="getAllSendDept" resultType="com.pqs9900.pojo.message.EventdetailInfo">
 SELECT DISTINCT
	c.depts_index deptsId,a.user_index userId,a.phone
FROM
	pqs_user a
	INNER JOIN pqs_userset b ON a.user_index = b.user_index
	INNER JOIN pqs_depts c ON c.depts_index = b.depts_index
WHERE
	a.state = 1
	AND b.isnotice = 1
    </select>

    <select id="getEventList" resultType="com.pqs9900.pojo.message.EventdetailInfo">
        select distinct
        a.eventdetail_index evenIndex,
        a.lineid lineId,
        a.timeid dTime,
        a.ms ms,
        b.name lineName,
        j.dic_name voltage,
        a.persisttime msTime,
        a.eventvalue eventvalue,
        g.name bdName,
        h.name gdName
        from pqs_eventdetail a
        inner join pq_line b on a.lineid=b.line_index
        inner join pq_substation g
        on b.sub_index = g.sub_index
        inner join pq_gdinformation h
        on b.gd_index = h.gd_index
        inner join pqs_dicdata j on b.scale = j.dic_index
        where a.timeid >= #{start}
        and a.timeid &lt; #{end}
        <foreach collection="wavetype" item="item" index="index" open="and ("
                 separator="or" close=")">
            a.wavetype = #{item}
        </foreach>
        <if test="linelist != null and linelist.size()>0">
            and a.lineid in
            <foreach collection="linelist" item="item" index="index" open="("
                     separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getEventdetailLineId" resultType="com.pqs9900.pojo.message.EventdetailInfo">
        select distinct c.depts_index deptsId,
        b.lineid lineId,
        b.timeid dTime,
        b.ms ms,
        e.name lineName,
        b.persisttime msTime,
        b.eventvalue eventvalue,
        g.name bdName,
        h.name gdName
        from pqs_user f
        inner join pqs_userset d
        on f.user_index = d.user_index and d.isnotice = 1
        inner join pqs_depts c
        on c.depts_index = d.depts_index
        inner join pqs_deptsline a
        on a.depts_index = c.depts_index
        inner join pq_line e
        on a.line_index = e.line_index
        inner join pq_substation g
        on e.sub_index = g.sub_index
        inner join pq_gdinformation h
        on e.gd_index = h.gd_index
        inner join pqs_eventdetail b
        on e.line_index = b.lineid
        where b.timeid > #{start}
        and b.timeid &lt; #{end}
        <foreach collection="wavetype" item="item" index="index" open="and ("
                 separator="or" close=")">
            b.wavetype = #{item}
        </foreach>
        and f.state = 1
    </select>

    <select id="getBaseInfo" resultType="com.pqs9900.pojo.message.EventdetailInfo">
        select distinct a.depts_index deptsId, d.phone phone,d.user_index userId, g.name bdName, h.name gdName
        from pqs_user d
        inner join pqs_userset c
        on d.user_index = c.user_index and c.isnotice = 1
        inner join pqs_deptsline a
        on c.depts_index = a.depts_index
        inner join pq_line e
        on a.line_index = e.line_index
        inner join pq_substation g
        on e.sub_index = g.sub_index
        inner join pq_gdinformation h
        on e.gd_index = h.gd_index
        inner join pqs_eventdetail b
        on b.lineid = a.line_index where b.timeid > #{start}
        and b.timeid &lt; #{end}
        <foreach collection="wavetype" item="item" index="index" open="and ("
                 separator="or" close=")">
            b.wavetype = #{item}
        </foreach>
        and d.state = 1
    </select>

    <insert id="insertMsgInfo">
        insert into msg_info values(#{userId},#{insertTime},#{content},#{type},#{state},#{msgIndex})
    </insert>

    <select id="getUserIdByPhone" resultType="java.lang.String">
        select user_Index from pqs_user where phone = #{phone} and state = 1
    </select>


    <select id="getLastTime" resultType="com.pqs9900.pojo.message.MsgInfoDTO">
SELECT
	t1.*
FROM(
	SELECT * FROM MSG_INFO t ORDER BY t.SEND_TIME DESC
) t1
WHERE rownum = 1
    </select>
    <select id="getAllMsgList" resultType="com.pqs9900.pojo.message.MsgInfoDTO">
        select
        p.NAME,
        p.PHONE,
        m.SEND_TIME,
        m.MSG_CONTENT,
        m.TYPE,
        m.STATE,
        m.MSG_INDEX
        from MSG_INFO m
        left join PQS_USER p on m.USER_ID = p.USER_INDEX
        where m.STATE = 1
        and m.SEND_TIME between #{startTime} and #{endTime}
        <if test="type!=null">
            and m.TYPE = #{type}
        </if>
        order by SEND_TIME desc
    </select>

    <update id="deleteMsgList">
        update MSG_INFO
        set
        STATE=0
        where MSG_INDEX in
        <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            #{item,jdbcType=VARCHAR}
        </foreach>
    </update>

    <select id="getHasSendEvent" resultType="com.pqs9900.pojo.message.UserEventDTO">
        select * from pqs_userevent
        where pqs_userevent.time between #{start} and #{end}
    </select>

    <insert id="addHasSendEvent">
        insert into pqs_userevent (
        <foreach collection="list" item="item" index="index" separator="UNION ALL">
            select
            #{item.userIndex},
            #{item.eventdetailIndex},
            #{item.time}
            from
            dual
        </foreach>
        )
    </insert>



</mapper>
