<?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.data.EventDetailMapper'>
    <select id="queryVoltageCountsByLineIndexs" resultType="Integer">
        SELECT
        count(1)
        FROM
        PQS_EVENTDETAIL
        WHERE
        LINEID IN
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        AND TIMEID between #{startTime} and #{endTime}
    </select>

    <select id="getCountsByInterval" resultType="Integer">
        select count(*) from PQS_EVENTDETAIL where to_char(TIMEID,'YYYY-MM') =
        #{interTime} and LINEID in
        <foreach collection="list" item="item" index="index" open="("
                 separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="queryEventDetail" resultType="EventDetail">
        SELECT
        M .TIMEID,
        M.EVENTDETAIL_INDEX,
        to_char(M .TIMEID,'YYYY-mm-dd
        hh24:mi:ss.')||M.MS time,
        round(M.PERSISTTIME/1000,3) PERSISTTIME,
        round(M.EVENTVALUE*100,2) EVENTVALUE,
        M.EVENTREASON,
        M.EVENTTYPE,
        M.NAME,
        M.LINEID,
        S.DIC_NAME SCALE,
        S1.DIC_NAME LOADTYPE,
        s2.name gdName,
        s3.name subName
        FROM
        (SELECT
        T1.TIMEID,
        T1.EVENTDETAIL_INDEX,
        nvl(substr(10000+T1.MS,-3),T1.MS) MS,
        T1.PERSISTTIME,
        T1.EVENTVALUE,
        T2.DIC_NAME EVENTREASON,
        T3.DIC_NAME EVENTTYPE,
        T4. NAME,
        T1.LINEID,
        T4.SCALE,
        T4.SUB_INDEX,
        T4.GD_INDEX,
        T5.LOADTYPE
        FROM
        PQS_EVENTDETAIL T1
        INNER JOIN PQS_DICDATA T2 ON T1.EVENTREASON = T2.DIC_INDEX
        INNER JOIN
        PQS_DICDATA T3 ON T1.EVENTTYPE = T3.DIC_INDEX
        INNER JOIN PQ_LINE T4 ON
        T1.LINEID = T4.LINE_INDEX
        INNER JOIN PQ_LINEDETAIL T5 ON
        T1.LINEID=T5.LINE_INDEX
        WHERE
        T1.LINEID IN
        <foreach collection="list" item="item" index="index" open="("
                 separator="," close=")">
            #{item}
        </foreach>
        AND
        T1.TIMEID BETWEEN #{startTime} AND #{endTime}
        )M
        INNER JOIN
        PQS_DICDATA S ON M.SCALE=S.DIC_INDEX
        INNER JOIN PQS_DICDATA
        S1 ON
        M.LOADTYPE=S1.DIC_INDEX
        INNER JOIN PQ_GDINFORMATION S2 ON
        M.gd_index=S2.gd_INDEX
        INNER JOIN PQ_SUBSTATION S3 ON
        M.sub_index=S3.sub_index
    </select>

    <select id="queryVoltageList" resultType="eventDetail">
        SELECT
        M .EVENTDETAIL_INDEX,
        M .LINEID,
        m.fileflag,
        <if test="_databaseId == 'MariaDB'">
            str_to_date(M .TIMEID,'%Y-%m-%d %H:%i:%s.')
        </if>
        <if test="_databaseId == 'Oracle'">
            to_char(M .TIMEID,'YYYY-mm-dd hh24:mi:ss.')
        </if>
        ||M.MS time,
        round(M .PERSISTTIME/1000, 3) PERSISTTIME,
        round(M.EVENTVALUE*100,2) EVENTVALUE,
        M .EVENTREASON,
        M .EVENTTYPE,
        M.severity,
        M .NAME lineName,
        M.EVENTASS_INDEX,
        S. NAME GDNAME,
        S1. NAME SUBNAME,
        S2.DIC_NAME SCALE,
        S3.IP,
        M.DEVTYPE devtype
        FROM
        (
        SELECT
        T1.EVENTDETAIL_INDEX,
        T1.LINEID,
        T1.TIMEID,
        T1.FILEFLAG,
        nvl(substr(10000+T1.MS,-3),T1.MS) MS,
        T1.PERSISTTIME,
        T1.EVENTVALUE,
        T1.severity,
        T2.DIC_NAME EVENTREASON,
        T3.DIC_NAME EVENTTYPE,
        T1.EVENTASS_INDEX,
        T4.NAME,
        T4.GD_INDEX GDINDEX,
        T4.SUB_INDEX SUBINDEX,
        T4.SCALE,
        T4.DEV_INDEX DEVINDEX,
        s5.DIC_NAME DEVTYPE
        FROM
        PQS_EVENTDETAIL T1
        INNER JOIN PQS_DICDATA T2 ON T1.EVENTREASON =
        T2.DIC_INDEX
        INNER JOIN PQS_DICDATA T3 ON T1.EVENTTYPE = T3.DIC_INDEX
        INNER JOIN PQ_LINE T4 ON T1.LINEID = T4.LINE_INDEX
        INNER JOIN pq_device S4	 ON T4 .dev_index = S4.Dev_index
        INNER JOIN PQS_DICDATA S5	 ON S4.devtype=S5 .DIC_INDEX
        WHERE
        T1.TIMEID
        BETWEEN #{startTime} AND #{endTime}
        and T1.LINEID in
        <foreach collection="list" item="item" index="index" open="("
                 separator="," close=")">
            #{item}
        </foreach>
        ) M
        INNER JOIN PQ_GDINFORMATION S ON M .GDINDEX = S.GD_INDEX
        INNER JOIN PQ_SUBSTATION S1 ON M .SUBINDEX = S1.SUB_INDEX
        INNER JOIN PQS_DICDATA S2 ON M . SCALE = S2.DIC_INDEX
        INNER JOIN pq_device S3 ON M . DEVINDEX = S3.Dev_index
        ORDER BY
        M .TIMEID DESC
    </select>

    <select id="getrunevent" resultType="eventDetail">
		select * from (select
		pq_line.name
		lineName,pqs_eventdetail.timeid
		timeId,c.dic_name as eventType
		from
		pq_gdinformation,pq_substation,pq_subvoltage,pq_line,pqs_eventdetail,pqs_dicdata
		c
		where
		pq_line.subv_index = pq_subvoltage.subv_index
		and
		pqs_eventdetail.eventass_index is null
		and
		pq_line.sub_index =
		pq_substation.sub_index and pq_line.gd_index =
		pq_gdinformation.gd_index and pq_line.line_index =
		pqs_eventdetail.lineid
		and pqs_eventdetail.eventtype=c.dic_index
		order
		by pqs_eventdetail.timeid desc)
		where ROWNUM <![CDATA[ <= ]]>10
	</select>

    <select id="querySubLevel" resultType="simpleValue">
		select nvl(a.value,0)
		value,b.name from
		(select
		MYINDEX,
		NVL(
		SUM (SAN_INS) + SUM (SWELL_INS) +
		SUM (INT_MOM) + SUM (SAN_MOM) + SUM
		(SWELL_MOM) + SUM (INT_TEMP) + SUM
		(SAN_TEMP) + SUM (SWELL_TEMP) + SUM
		(INT_DUR) + SUM (SAN_DUR) + SUM
		(SWELL_DUR),
		0
		)VALUE
		from
		EVENT_COUNT_GD
		WHERE
		TIMEID between #{startTime}
		and #{endTime}
		AND MYINDEX =#{subIndex}
		GROUP BY MYINDEX
		) a
		right join
		(select * FROM PQ_SUBSTATION b where b.SUB_INDEX=#{subIndex}) b
		on
		a.MYINDEX = b.SUB_INDEX
	</select>




    <select id="getEventDetailList" resultType="eventDetailExcelBody">
        SELECT
            *
        FROM
           PQS_EVENTDETAIL T1
        WHERE
           T1.TIMEID
        BETWEEN #{startTime} AND #{endTime}
        and T1.LINEID in
        <foreach collection="list" item="item" index="index" open="("
                 separator="," close=")">
            #{item}
        </foreach>
    </select>



    <update id="updateSeverity" >
        update
          PQS_EVENTDETAIL
        set
          Severity = #{severity}
        where
          EVENTDETAIL_INDEX=#{index}
    </update>


    <select id="selectByIndex" resultType="eventDetail">
        SELECT
            EVENTDETAIL_INDEX,
            LINEID,
            TIMEID,
            MS,
            WAVETYPE,
            PERSISTTIME,
            EVENTVALUE,
            EVENTREASON,
            EVENTTYPE,
            EVENTASS_INDEX,
            DQTIME,
            DEALTIME,
            NUM,
            FILEFLAG,
            DEALFLAG,
            FIRSTTIME,
            FIRSTTYPE,
            FIRSTMS,
            WAVENAME,
            Energy,
            Severity
        FROM
            PQS_EVENTDETAIL
        WHERE
            EVENTDETAIL_INDEX = #{eventIndex}
    </select>
    <select id="selectByTimeIdAndLineIndex" resultType="eventDetail">
        SELECT
            EVENTDETAIL_INDEX,
            LINEID,
            TIMEID,
            MS,
            WAVETYPE,
            PERSISTTIME,
            EVENTVALUE,
            EVENTREASON,
            EVENTTYPE,
            EVENTASS_INDEX,
            DQTIME,
            DEALTIME,
            NUM,
            FILEFLAG,
            DEALFLAG,
            FIRSTTIME,
            FIRSTTYPE,
            FIRSTMS,
            WAVENAME,
            Energy,
            Severity
        FROM
            PQS_EVENTDETAIL
        WHERE
            TIMEID = #{timeId}
            and
            MS = #{ms}
            and
            lineId =#{lineId}
    </select>
</mapper>