<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.1//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.njcn.mapper.configuration.LineMapper">
    <select id="getDetailed" resultType="com.njcn.pojo.data.Detailed">
        select b.name pointName,b.line_index lineId, c.name gdName, d.name bdName,
        e.tinterval tinterval,a.timeid dTime,
        a.freq_dev_overtime freqDevOverTime,
        a.voltage_dev_overtime voltageDevOvertime,
        a.ubalance_overtime UBalanceOverTime,
        a.flicker_overtime flickerOverTime,
        a.uaberrance_overtime UAberranceOverTime,
        a.uharm_2_overtime UHarm2OverTime,a.iharm_2_overtime IHarm2OverTime,
        a.uharm_3_overtime UHarm3OverTime,a.iharm_3_overtime IHarm3OverTime,
        a.uharm_4_overtime UHarm4OverTime,a.iharm_4_overtime IHarm4OverTime,
        a.uharm_5_overtime UHarm5OverTime,a.iharm_5_overtime IHarm5OverTime,
        a.uharm_6_overtime UHarm6OverTime,a.iharm_6_overtime IHarm6OverTime,
        a.uharm_7_overtime UHarm7OverTime,a.iharm_7_overtime IHarm7OverTime,
        a.uharm_8_overtime UHarm8OverTime,a.iharm_8_overtime IHarm8OverTime,
        a.uharm_9_overtime UHarm9OverTime,a.iharm_9_overtime IHarm9OverTime,
        a.uharm_10_overtime UHarm10OverTime,a.iharm_10_overtime IHarm10OverTime,
        a.uharm_11_overtime UHarm11OverTime,a.iharm_11_overtime IHarm11OverTime,
        a.uharm_12_overtime UHarm12OverTime,a.iharm_12_overtime IHarm12OverTime,
        a.uharm_13_overtime UHarm13OverTime,a.iharm_13_overtime IHarm13OverTime,
        a.uharm_14_overtime UHarm14OverTime,a.iharm_14_overtime IHarm14OverTime,
        a.uharm_15_overtime UHarm15OverTime,a.iharm_15_overtime IHarm15OverTime,
        a.uharm_16_overtime UHarm16OverTime,a.iharm_16_overtime IHarm16OverTime,
        a.uharm_17_overtime UHarm17OverTime,a.iharm_17_overtime IHarm17OverTime,
        a.uharm_18_overtime UHarm18OverTime,a.iharm_18_overtime IHarm18OverTime,
        a.uharm_19_overtime UHarm19OverTime,a.iharm_19_overtime IHarm19OverTime,
        a.uharm_20_overtime UHarm20OverTime,a.iharm_20_overtime IHarm20OverTime,
        a.uharm_21_overtime UHarm21OverTime,a.iharm_21_overtime IHarm21OverTime,
        a.uharm_22_overtime UHarm22OverTime,a.iharm_22_overtime IHarm22OverTime,
        a.uharm_23_overtime UHarm23OverTime,a.iharm_23_overtime IHarm23OverTime,
        a.uharm_24_overtime UHarm24OverTime,a.iharm_24_overtime IHarm24OverTime,
        a.uharm_25_overtime UHarm25OverTime,a.iharm_25_overtime IHarm25OverTime,
        a.i_neg_overtime INegOvertime,
        a.inuharm_1_overtime INUHARM1OverTime,a.inuharm_2_overtime INUHARM2OverTime,
        a.inuharm_3_overtime INUHARM3OverTime,a.inuharm_4_overtime INUHARM4OverTime,
        a.inuharm_5_overtime INUHARM5OverTime,a.inuharm_6_overtime INUHARM6OverTime,
        a.inuharm_7_overtime INUHARM7OverTime,a.inuharm_8_overtime INUHARM8OverTime,
        a.inuharm_9_overtime INUHARM9OverTime,a.inuharm_10_overtime INUHARM10OverTime,
        a.inuharm_11_overtime INUHARM11OverTime,a.inuharm_12_overtime INUHARM12OverTime,
        a.inuharm_13_overtime INUHARM13OverTime,a.inuharm_14_overtime INUHARM14OverTime,
        a.inuharm_15_overtime INUHARM15OverTime,a.inuharm_16_overtime INUHARM16OverTime
        from limit_rate a, pq_line b, pq_gdinformation c, pq_substation
        d,pq_linedetail e
        where a.myindex = b.line_index
        and a.myindex in
        <foreach collection="listLine" item="item" index="index" open="("
                 separator="," close=")">
            #{item}
        </foreach>
        and b.gd_index = c.gd_index
        and b.sub_index = d.sub_index
        and
        e.line_index = a.myindex
        and a.phasic_type = 'T' and a.timeid between
        #{start} and #{end}
    </select>

    <select id="queryLineData" resultType="line">
        SELECT T.GD_INDEX,
               T
                   .SUB_INDEX,
               T.DEV_INDEX,
               T.NAME
        FROM PQ_LINE T
        where t.line_index = #{lineid}
    </select>


    <select id="getLineIndex" resultType="int">
        SELECT
        T.LINE_INDEX
        FROM
        PQ_LINE T
        WHERE
        T.Dev_Index
        IN
        <foreach collection="list" item="item" index="index" open="("
                 separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="getLineIndexs" resultType="int">
        SELECT
        T.LINE_INDEX
        FROM
        PQ_LINE T
        WHERE
        T.SCALE=#{dicIndex}
        AND
        T.DEV_INDEX
        IN
        <foreach collection="list" item="item" index="index" open="("
                 separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="getDeviceIndexs" resultType="int">
        SELECT
        DEV_INDEX
        FROM
        PQ_LINE
        WHERE
        DEV_INDEX IN
        <foreach collection="list" item="item" open="(" close=")"
                 separator=",">
            #{item}
        </foreach>
        AND
        SCALE=#{dicIndex}
        GROUP BY
        DEV_INDEX
    </select>

    <select id="getLineIndexsByLoadType" resultType="int">
        SELECT
        T.LINE_INDEX
        FROM
        PQ_LINE T
        INNER JOIN
        PQ_LINEDETAIL T1
        ON
        T.LINE_INDEX=T1.LINE_INDEX
        WHERE
        T1.LoadType=#{dicIndex}
        AND
        T.DEV_INDEX
        IN
        <foreach collection="list" item="item" index="index" open="("
                 separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="getDeviceIndexsByLoadType" resultType="int">
        SELECT
        DEV_INDEX
        FROM
        PQ_LINE T
        INNER JOIN PQ_LINEDETAIL T1
        ON
        T.LINE_INDEX=T1.LINE_INDEX
        WHERE
        T.DEV_INDEX
        IN
        <foreach collection="list" item="item" open="(" close=")"
                 separator=",">
            #{item}
        </foreach>
        AND
        T1.LOADTYPE=#{dicIndex}
        GROUP BY
        T.DEV_INDEX
    </select>

    <select id="getGWLines" resultType="Integer">
        SELECT
        T2.LINE_INDEX
        FROM
        PQ_LINE t1,
        PQ_LINEDETAIL t2,
        PQ_DEVICE t3
        WHERE
        T1.LINE_INDEX = T2.LINE_INDEX
        AND T1.DEV_INDEX=T3.DEV_INDEX
        AND
        T3.DEVFLAG=0
        AND
        T1.LINE_INDEX
        IN
        <foreach collection="list" item="item" open="(" close=")"
                 separator=",">
            #{item}
        </foreach>
        AND T2.MONITOR_ID IS NOT NULL
    </select>

    <select id="getLineByEventDetailIndex" resultType="line">
        select t1.*
        from PQ_LINE t1,
             PQS_EVENTDETAIL t2
        where t1.LINE_INDEX = t2.LINEID
          and t2.EVENTDETAIL_INDEX = #{eventIndex}
    </select>

    <select id="getNodeIpbylineIndex" resultType="String">
        select t1.Node_IP
        from NODEINFORMATION t1,
             PQ_LINE t2,
             PQ_DEVICE t3
        where t1.Node_Index = t3.NODE_INDEX
          and T2.DEV_INDEX = t3.DEV_INDEX
          and T2.LINE_INDEX = #{lineIndex}
    </select>

    <select id="getNodeIpbyDevIndex" resultType="String">
        SELECT t1.Node_IP
        FROM NODEINFORMATION t1,
             PQ_DEVICE t3
        WHERE t1.Node_Index = t3.NODE_INDEX
          and t3.DEV_INDEX = #{lineIndex}
    </select>

    <select id="getIntervalByIndex" resultType="int">
        SELECT tinterval
        FROM pq_lineDetail
        WHERE line_index = #{lineIndex}
    </select>

    <select id="queryByLineIndex" resultType="ResponsibilityData">
        SELECT T1.NAME       lineName,
               T1.LINE_INDEX lineIndex,
               T2.NAME       gdName,
               T3.NAME       subName,
               T4.NAME
                             devName,
               T4.IP
        FROM PQ_LINE t1,
             PQ_GDINFORMATION t2,
             PQ_SUBSTATION t3,
             PQ_DEVICE t4
        WHERE t1.gd_index = t2.gd_index
          AND t1.sub_index =
              t3.sub_index
          AND t1.dev_index =
              t4.dev_index
          AND t1.line_index =
              #{lineIndex}
    </select>
    <select id="getLinesByPro" resultType="line">
        select *
        from pq_line
        where gd_index in
              (select gd_index
               from pq_gdinformation
               where province_index
                         = #{proIndex})

    </select>

    <select id="getRole" resultType="java.lang.Integer">
        select count(a.path)
        from pqs_function a, pqs_rolefunction b,
        pqs_functionploy c
        where a.path = #{url}
        and a.function_index =
        b.function_index
        and b.function_ploy = c.function_index
        and c.state = 1
        and b.role_index in
        <foreach collection="role" item="item" open="(" separator=","
                 close=")">
            #{item}
        </foreach>
    </select>

    <select id="getLineName" resultType="com.njcn.pojo.commons.device.MachineAccount">
        select a.name     name,
               b.dic_name Scale,
               c.name     subName,
               d.pttype   pttype
        from pq_line a,
             pqs_dicdata b,
             pq_substation c,
             pq_linedetail d
        where a.line_index =
              #{lineId}
          and a.scale = b.dic_index
          and a.sub_index = c.sub_index
          and a.line_index = d.line_index
    </select>

    <select id="getSubDeptsIndex" resultType="com.njcn.pojo.user.Depts">
        select t.depts_index deptsIndex,
        t.area area,
        t.deptsname deptsName,
        a.dic_name areaName,
        t.custom_dept customDept
        from pqs_depts t,
        pqs_dicdata a
        where t.parentnodeid = #{parentDeptsIndex}
        and t.area =
        a.dic_index
        and t.state = 1
        and t.custom_dept = 0
        <if test="listArea !=null">
            and t.depts_index in
            <foreach collection="listArea" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getDeptsLineIndex" resultType="java.lang.Integer"
            databaseId="Oracle">
        select distinct (a.line_index)
        from pqs_deptsline a,
        pq_device b, pq_line c
        where a.depts_index in
        (select depts_index
        from
        pqs_depts
        start with depts_index = #{deptsIndex}
        connect by prior
        depts_index = parentnodeid)
        and a.systype = #{sysIndex}
        and c.line_index
        = a.line_index
        and c.dev_index = b.dev_index
        <if test="flag==false">
            and b.devflag = 0
        </if>
        and
        b.devmodel = 1
    </select>

    <select id="getDeptsLineIndex" resultType="java.lang.Integer"
            databaseId="MariaDB">
        SELECT DISTINCT
        ( a.line_index )
        FROM
        pqs_deptsline a,
        pq_device b,
        pq_line c
        WHERE
        a.depts_index IN (
        WITH recursive t2 AS (
        SELECT
        t1.depts_index,
        t1.parentnodeid
        FROM
        pqs_depts t1 where
        t1.parentnodeid = #{deptsIndex} UNION ALL
        SELECT
        t1.depts_index,
        t1.parentnodeid
        FROM
        pqs_depts t1,
        t2
        WHERE
        t1.parentnodeid = #{deptsIndex}
        and t1.depts_index = t2.parentnodeid
        ) SELECT
        depts_index
        FROM
        t2
        )
        AND
        a.systype = #{sysIndex}
        AND c.line_index = a.line_index
        AND c.dev_index
        = b.dev_index
        <if test="flag==false">
            AND b.devflag = 0
        </if>
        AND b.devmodel = 1
    </select>

    <select id="getPointIndexbyVoltage" resultType="java.lang.Integer"
            databaseId="Oracle">
        select a.line_index
        from pqs_deptsline a,
             pq_device b,
             pq_line c
        where a.depts_index in
              (select depts_index
               from pqs_depts start
        with parentnodeid = #{deptsIndex}
        connect by prior depts_index =
            parentnodeid)
               and a.systype = #{sysIndex}
               and c.line_index =
            a.line_index
               and c.dev_index = b.dev_index
               and b.devflag = 0
               and
            b.devmodel = 1
               and c.scale = #{guid}
    </select>

    <select id="getPointIndexbyVoltage" resultType="java.lang.Integer"
            databaseId="MariaDB">>
    select a.line_index
    from pqs_deptsline a,
         pq_device b,
         pq_line c
    where a.depts_index in (
        WITH recursive t2 AS (
            SELECT t1.depts_index,
                   t1.parentnodeid
            FROM pqs_depts t1
            where t1.parentnodeid =
                  #{deptsIndex}
            UNION ALL
            SELECT t1.depts_index,
                   t1.parentnodeid
            FROM pqs_depts t1,
                 t2
            WHERE t1.parentnodeid = #{deptsIndex}
              and t1.depts_index
                = t2.parentnodeid
        )
        SELECT depts_index
        FROM t2
    )
      and a.systype = #{sysIndex}
      and c.line_index = a.line_index
      and c.dev_index = b.dev_index
      and b.devflag = 0
      and b.devmodel = 1
      and c.scale = #{guid}
    </select>

    <select id="getPointIndexbyType" resultType="java.lang.Integer"
            databaseId="Oracle">
        select a.line_index
        from pqs_deptsline a,
             pq_device b,
             pq_line c,
             pq_linedetail d
        where a.depts_index in
              (select depts_index
               from pqs_depts start
        with parentnodeid = #{deptsIndex}
        connect by prior
            depts_index = parentnodeid)
               and a.systype = #{sysIndex}
               and c.line_index
            = a.line_index
               and c.dev_index = b.dev_index
               and b.devflag = 0
               and
            b.devmodel = 1
               and c.line_index = d.line_index
               and d.loadtype = #{guid}
    </select>

    <select id="getPointbyMonitor" resultType="java.lang.Integer"
            databaseId="Oracle">
        select a.line_index
        from pqs_deptsline a, pq_device b, pq_line c,
        pq_linedetail d
        where a.depts_index in
        (select depts_index
        from pqs_depts
        start with parentnodeid = #{deptsIndex}
        connect by prior depts_index =
        parentnodeid)
        and a.systype = #{sysIndex}
        and c.line_index =
        a.line_index
        and c.dev_index = b.dev_index
        and b.devflag = 0
        and
        b.devmodel = 1
        and c.line_index = d.line_index
        <choose>
            <when test="guid == 0">
                and d.monitor_id is null
            </when>
            <otherwise>
                and d.monitor_id is not null
            </otherwise>
        </choose>
    </select>


    <select id="getPointIndexbyType" resultType="java.lang.Integer"
            databaseId="MariaDB">
        select a.line_index
        from pqs_deptsline a,
             pq_device b,
             pq_line c,
             pq_linedetail d
        where a.depts_index in (
            WITH recursive t2 AS
                               (
                                   SELECT t1.depts_index,
                                          t1.parentnodeid
                                   FROM pqs_depts t1
                                   where t1.parentnodeid = #{deptsIndex}
                                   UNION ALL
                                   SELECT t1.depts_index,
                                          t1.parentnodeid
                                   FROM pqs_depts t1,
                                        t2
                                   WHERE t1.parentnodeid = #{deptsIndex}
                                     and t1.depts_index = t2.parentnodeid
                               )
            SELECT depts_index
            FROM t2
        )
          and a.systype = #{sysIndex}
          and c.line_index = a.line_index
          and c.dev_index
            = b.dev_index
          and b.devflag = 0
          and b.devmodel = 1
          and c.line_index =
              d.line_index
          and d.loadtype = #{guid}
    </select>

    <select id="getPointbyMonitor" resultType="java.lang.Integer"
            databaseId="MariaDB">
        select a.line_index
        from pqs_deptsline a, pq_device b, pq_line c,
        pq_linedetail d
        where a.depts_index in (
        WITH recursive t2 AS (
        SELECT
        t1.depts_index,
        t1.parentnodeid
        FROM
        pqs_depts t1 where t1.parentnodeid =
        #{deptsIndex} UNION ALL
        SELECT
        t1.depts_index,
        t1.parentnodeid
        FROM
        pqs_depts t1,
        t2
        WHERE
        t1.parentnodeid = #{deptsIndex}
        and t1.depts_index
        = t2.parentnodeid
        ) SELECT
        depts_index
        FROM
        t2
        )
        and a.systype = #{sysIndex}
        and c.line_index =
        a.line_index
        and c.dev_index = b.dev_index
        and
        b.devflag = 0
        and
        b.devmodel = 1
        and c.line_index = d.line_index
        <choose>
            <when test="guid == 0">
                and d.monitor_id is not null
            </when>
            <otherwise>
                and d.monitor_id is null
            </otherwise>
        </choose>
    </select>

    <select id="getDeptsLineIndexs" resultType="java.lang.Integer">
        select distinct (a.line_index)
        from pqs_deptsline a,
             pq_device b,
             pq_line c
        where a.depts_index = #{deptsIndex}
          and a.systype = #{sysIndex}
          and c.line_index = a.line_index
          and c.dev_index = b.dev_index
          and b.devflag
            = 0
          and b.devmodel = 1
    </select>


    <select id="getVoltageData" resultType="com.njcn.pojo.data.Legder$Data">
        select count(a.line_index) lineCount,
        count(distinct a.sub_index)
        gdCount,
        count(distinct a.dev_index) subCount,
        a.scale dicIndex,
        b.dic_name dicName
        from pq_line a, pqs_dicdata b, pq_linedetail c
        where
        a.scale = b.dic_index
        and a.line_index = c.line_index
        and a.line_index
        in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        <if test="listVoltage != null">
            and a.scale in
            <foreach collection="listVoltage" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="nature != -1">
            and c.powerid = #{nature}
        </if>
        group by a.scale, b.dic_name
    </select>


    <select id="getTypeData" resultType="com.njcn.pojo.data.Legder$Data">
        select count(a.line_index) lineCount,
        count(distinct a.sub_index)
        gdCount,
        count(distinct a.dev_index) subCount,
        c.loadtype dicIndex,
        b.dic_name dicName
        from pq_line a, pqs_dicdata b, pq_linedetail c
        where
        c.loadtype = b.dic_index
        and a.line_index = c.line_index
        and
        a.line_index in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        <if test="listType != null">
            and c.loadtype in
            <foreach collection="listType" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="nature != -1">
            and c.powerid = #{nature}
        </if>
        group by c.loadtype, b.dic_name
    </select>

    <select id="getFactoryData" resultType="com.njcn.pojo.data.Legder$Data">
        select count(a.line_index) lineCount,
        count(distinct a.sub_index)
        gdCount,
        count(distinct a.dev_index) subCount,
        c.manufacturer dicIndex,
        b.dic_name dicName
        from pq_line a, pqs_dicdata b, pq_devicedetail
        c,pq_linedetail d
        where c.dev_index = a.dev_index
        and c.manufacturer =
        b.dic_index
        and a.line_index = d.line_index
        and a.line_index in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        <if test="listFactory != null">
            and c.manufacturer in
            <foreach collection="listFactory" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="nature != -1">
            and d.powerid = #{nature}
        </if>
        group by c.manufacturer, b.dic_name
    </select>


    <select id="getMonitorData" resultType="com.njcn.pojo.data.Legder$Data">
        select count(a.line_index) lineCount,
        count(distinct a.sub_index)
        gdCount,
        count(distinct a.dev_index) subCount,
        c.monitor_id dicIndex
        from pq_line a, pq_linedetail c
        where c.line_index = a.line_index and
        a.line_index in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        <if test="appear != -1 and appear != null">
            <choose>
                <when test="appear == 0">
                    and c.monitor_id is not null
                </when>
                <otherwise>
                    and c.monitor_id is null
                </otherwise>
            </choose>
        </if>
        <if test="nature != -1">
            and c.powerid = #{nature}
        </if>
        group by c.monitor_id
    </select>


    <select id="getGwLinesByDeviceType" resultType="Integer">
        SELECT
        t3.LINE_INDEX
        FROM
        PQ_LINEDETAIL t3
        WHERE
        MONITOR_ID IS NOT NULL
        AND
        t3.LINE_INDEX IN (
        SELECT
        t2.LINE_INDEX
        FROM
        PQ_LINE t2
        WHERE
        t2.DEV_INDEX
        IN (
        SELECT
        t1.DEV_INDEX
        FROM
        pq_device t1
        WHERE
        t1.DATATYPE IN
        <foreach collection="type" open="(" close=")" separator=","
                 item="item">
            #{item}
        </foreach>
        ))
    </select>

    <select id="getGwLinesByDevicePlateType" resultType="Integer">
        SELECT
        t3.LINE_INDEX
        FROM
        PQ_LINEDETAIL t3
        WHERE
        MONITOR_ID IS NOT NULL
        AND
        t3.LINE_INDEX IN (
        SELECT
        t2.LINE_INDEX
        FROM
        PQ_LINE t2
        WHERE
        t2.DEV_INDEX
        IN (
        SELECT
        t1.DEV_INDEX
        FROM
        pq_device t1,,PQ_DEVICEDETAIL detail
        WHERE
        t1.DEV_INDEX = detail.DEV_INDEX
        and detail.ELECTROPLATE = 1
        and t1.DATATYPE IN
        <foreach collection="type" open="(" close=")" separator=","
                 item="item">
            #{item}
        </foreach>
        ))
    </select>

    <select id="siftLineIndexs" resultType="Integer">
        select a.line_index
        from pq_line a, pq_linedetail b, pq_device c,
        pq_devicedetail d
        where b.line_index = a.line_index
        and c.dev_index =
        a.dev_index
        and d.dev_index = c.dev_index
        <if test="scale != null">
            <if test="scale != 'null'">
                and a.scale = #{scale}
            </if>
        </if>
        <if test="manc != null">
            <if test="manc != 'null'">
                and d.manufacturer = #{manc}
            </if>
        </if>
        <if test="loadtype != null">
            <if test="loadtype != 'null'">
                and b.loadtype = #{loadtype}
            </if>
        </if>
        and a.line_index in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
    </select>

    <select id="siftAllIndexs" resultType="IndexsCount">
        select a.line_index lineIndex,a.dev_index devIndex,a.sub_index
        subIndex,a.gd_index gdIndex
        from pq_line a, pq_linedetail b, pq_device
        c, pq_devicedetail d
        where b.line_index = a.line_index
        and c.dev_index =
        a.dev_index
        and d.dev_index = c.dev_index
        <if test="scale != null">
            <if test="scale != 'null'">
                and a.scale = #{scale}
            </if>
        </if>
        <if test="manc != null">
            <if test="manc != 'null'">
                and d.manufacturer = #{manc}
            </if>
        </if>
        <if test="loadtype != null">
            <if test="loadtype != 'null'">
                and b.loadtype = #{loadtype}
            </if>
        </if>
        and a.line_index in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
    </select>

    <resultMap type="Tree" id="lineResultMap" autoMapping="true">
        <id column="line_index" property="nodeIndex"/>
        <result column="subv_index" property="pId"/>
    </resultMap>
    <select id='getTreeBySubv' resultMap='lineResultMap'>
        select
        a.subv_index, a.name,a.line_index,b.devflag as
        state,b.status,d.pttype,d.powerid as powerType
        from pq_line
        a,pq_device b ,pq_subvoltage
        c,pq_linedetail d
        where
        a.dev_index = b.dev_index
        and a.line_index =
        d.line_index
        and a.subv_index = c.subv_index
        and c.subv_index =
        #{subvIndex}
        and a.line_index in
        <foreach collection="list" index="index" item="item" open="("
                 separator="," close=") order by subv_index">
            #{item}
        </foreach>
    </select>

    <select id='getTreePlateBySubv' resultMap='lineResultMap'>
        select
        a.subv_index, a.name,a.line_index,b.devflag as
        state,b.status,d.pttype,d.powerid as powerType
        from pq_line
        a,pq_device b ,pq_subvoltage
        c,pq_linedetail d,pq_deviceDetail e
        where
        a.dev_index = b.dev_index
        and a.line_index =
        d.line_index
        and b.DEV_Index = e.DEV_Index
        and a.subv_index = c.subv_index
        and c.subv_index =
        #{subvIndex}
        and e.electroplate=1
        and a.line_index in
        <foreach collection="list" index="index" item="item" open="("
                 separator="," close=") order by subv_index">
            #{item}
        </foreach>
    </select>


    <select id="getAreaBytimeZero" resultType="com.njcn.pojo.data.InfoData">
        select count(distinct c.myindex) countTotal, c.timeid totalDate,
        #{index} as dicIndex,#{index} as dicName
        from limit_rate c
        where
        c.myindex in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        and c.timeid between #{start} and #{end}
        and c.phasic_type = 'T'
        group
        by c.timeid
    </select>


    <select id="getAreaBytimeOne" resultType="com.njcn.pojo.data.InfoData">
        select count(distinct c.myindex) countTotal, c.timeid totalDate,
        #{index} as dicIndex,#{index} as dicName
        from limit_rate c
        where
        c.myindex in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        and (c.flicker_overtime > 0 or c.ubalance_overtime > 0 or c.freq_dev_overtime > 0 or c.uaberrance_overtime > 0
        or c.voltage_dev_overtime > 0
        or c.uharm_2_overtime > 0 or c.iharm_2_overtime > 0
        or c.uharm_3_overtime > 0 or c.iharm_3_overtime > 0
        or c.uharm_4_overtime > 0 or c.iharm_4_overtime > 0
        or c.uharm_5_overtime > 0 or c.iharm_5_overtime > 0
        or c.uharm_6_overtime > 0 or c.iharm_6_overtime > 0
        or c.uharm_7_overtime > 0 or c.iharm_7_overtime > 0
        or c.uharm_8_overtime > 0 or c.iharm_8_overtime > 0
        or c.uharm_9_overtime > 0 or c.iharm_9_overtime > 0
        or c.uharm_10_overtime > 0 or c.iharm_10_overtime > 0
        or c.uharm_11_overtime > 0 or c.iharm_11_overtime > 0
        or c.uharm_12_overtime > 0 or c.iharm_12_overtime > 0
        or c.uharm_13_overtime > 0 or c.iharm_13_overtime > 0
        or c.uharm_14_overtime > 0 or c.iharm_14_overtime > 0
        or c.uharm_15_overtime > 0 or c.iharm_15_overtime > 0
        or c.uharm_16_overtime > 0 or c.iharm_16_overtime > 0
        or c.uharm_17_overtime > 0 or c.iharm_17_overtime > 0
        or c.uharm_18_overtime > 0 or c.iharm_18_overtime > 0
        or c.uharm_19_overtime > 0 or c.iharm_19_overtime > 0
        or c.uharm_20_overtime > 0 or c.iharm_20_overtime > 0
        or c.uharm_21_overtime > 0 or c.iharm_21_overtime > 0
        or c.uharm_22_overtime > 0 or c.iharm_22_overtime > 0
        or c.uharm_23_overtime > 0 or c.iharm_23_overtime > 0
        or c.uharm_24_overtime > 0 or c.iharm_24_overtime > 0
        or c.uharm_25_overtime > 0 or c.iharm_25_overtime > 0
        or c.i_neg_overtime > 0
        or c.inuharm_1_overtime > 0 or c.inuharm_2_overtime > 0
        or c.inuharm_3_overtime > 0 or c.inuharm_4_overtime > 0
        or c.inuharm_5_overtime > 0 or c.inuharm_6_overtime > 0
        or c.inuharm_7_overtime > 0 or c.inuharm_8_overtime > 0
        or c.inuharm_9_overtime > 0 or c.inuharm_10_overtime > 0
        or c.inuharm_11_overtime > 0 or c.inuharm_12_overtime > 0
        or c.inuharm_13_overtime > 0 or c.inuharm_14_overtime > 0
        or c.inuharm_15_overtime > 0 or c.inuharm_16_overtime > 0)
        and c.timeid between #{start} and #{end}
        and c.phasic_type = 'T'
        group
        by c.timeid
    </select>


    <select id="getVoltageBytimeZero" resultType="com.njcn.pojo.data.InfoData">
        select count(distinct c.myindex) countTotal,
        c.timeid totalDate,
        b.dic_index dicIndex,
        b.dic_name dicName
        from limit_rate c, pq_line a,
        pqs_dicdata b
        where c.myindex in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        and c.timeid between #{start} and #{end}
        and c.phasic_type = 'T'
        and
        a.line_index = c.myindex
        and a.scale = b.dic_index
        group by c.timeid,
        b.dic_index, b.dic_name
    </select>


    <select id="getVoltageBytimeOne" resultType="com.njcn.pojo.data.InfoData">
        select count(distinct c.myindex) countTotal,
        c.timeid totalDate,
        b.dic_index dicIndex,
        b.dic_name dicName
        from limit_rate c, pq_line a,
        pqs_dicdata b
        where c.myindex in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        and (c.flicker_overtime > 0 or c.ubalance_overtime > 0 or c.freq_dev_overtime > 0 or c.uaberrance_overtime > 0
        or c.voltage_dev_overtime > 0
        or c.uharm_2_overtime > 0 or c.iharm_2_overtime > 0
        or c.uharm_3_overtime > 0 or c.iharm_3_overtime > 0
        or c.uharm_4_overtime > 0 or c.iharm_4_overtime > 0
        or c.uharm_5_overtime > 0 or c.iharm_5_overtime > 0
        or c.uharm_6_overtime > 0 or c.iharm_6_overtime > 0
        or c.uharm_7_overtime > 0 or c.iharm_7_overtime > 0
        or c.uharm_8_overtime > 0 or c.iharm_8_overtime > 0
        or c.uharm_9_overtime > 0 or c.iharm_9_overtime > 0
        or c.uharm_10_overtime > 0 or c.iharm_10_overtime > 0
        or c.uharm_11_overtime > 0 or c.iharm_11_overtime > 0
        or c.uharm_12_overtime > 0 or c.iharm_12_overtime > 0
        or c.uharm_13_overtime > 0 or c.iharm_13_overtime > 0
        or c.uharm_14_overtime > 0 or c.iharm_14_overtime > 0
        or c.uharm_15_overtime > 0 or c.iharm_15_overtime > 0
        or c.uharm_16_overtime > 0 or c.iharm_16_overtime > 0
        or c.uharm_17_overtime > 0 or c.iharm_17_overtime > 0
        or c.uharm_18_overtime > 0 or c.iharm_18_overtime > 0
        or c.uharm_19_overtime > 0 or c.iharm_19_overtime > 0
        or c.uharm_20_overtime > 0 or c.iharm_20_overtime > 0
        or c.uharm_21_overtime > 0 or c.iharm_21_overtime > 0
        or c.uharm_22_overtime > 0 or c.iharm_22_overtime > 0
        or c.uharm_23_overtime > 0 or c.iharm_23_overtime > 0
        or c.uharm_24_overtime > 0 or c.iharm_24_overtime > 0
        or c.uharm_25_overtime > 0 or c.iharm_25_overtime > 0
        or c.i_neg_overtime > 0
        or c.inuharm_1_overtime > 0 or c.inuharm_2_overtime > 0
        or c.inuharm_3_overtime > 0 or c.inuharm_4_overtime > 0
        or c.inuharm_5_overtime > 0 or c.inuharm_6_overtime > 0
        or c.inuharm_7_overtime > 0 or c.inuharm_8_overtime > 0
        or c.inuharm_9_overtime > 0 or c.inuharm_10_overtime > 0
        or c.inuharm_11_overtime > 0 or c.inuharm_12_overtime > 0
        or c.inuharm_13_overtime > 0 or c.inuharm_14_overtime > 0
        or c.inuharm_15_overtime > 0 or c.inuharm_16_overtime > 0)
        and c.timeid between #{start} and #{end}
        and c.phasic_type = 'T'
        and
        a.line_index = c.myindex
        and a.scale = b.dic_index
        group by c.timeid,
        b.dic_index, b.dic_name
    </select>

    <select id="getLoadTypeBytimeZero" resultType="com.njcn.pojo.data.InfoData">
        select count(distinct c.myindex) countTotal,
        c.timeid totalDate,
        b.dic_index dicIndex,
        b.dic_name dicName
        from limit_rate c,
        pq_linedetail a, pqs_dicdata b
        where c.myindex in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        and c.timeid between #{start} and #{end}
        and c.phasic_type = 'T'
        and
        a.line_index = c.myindex
        and a.loadtype = b.dic_index
        group by c.timeid,
        b.dic_index, b.dic_name
    </select>

    <select id="getLoadTypeBytimeOne" resultType="com.njcn.pojo.data.InfoData">
        select count(distinct c.myindex) countTotal,
        c.timeid totalDate,
        b.dic_index dicIndex,
        b.dic_name dicName
        from limit_rate c,
        pq_linedetail a, pqs_dicdata b
        where c.myindex in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        and (c.flicker_overtime > 0 or c.ubalance_overtime > 0 or c.freq_dev_overtime > 0 or c.uaberrance_overtime > 0
        or c.voltage_dev_overtime > 0
        or c.uharm_2_overtime > 0 or c.iharm_2_overtime > 0
        or c.uharm_3_overtime > 0 or c.iharm_3_overtime > 0
        or c.uharm_4_overtime > 0 or c.iharm_4_overtime > 0
        or c.uharm_5_overtime > 0 or c.iharm_5_overtime > 0
        or c.uharm_6_overtime > 0 or c.iharm_6_overtime > 0
        or c.uharm_7_overtime > 0 or c.iharm_7_overtime > 0
        or c.uharm_8_overtime > 0 or c.iharm_8_overtime > 0
        or c.uharm_9_overtime > 0 or c.iharm_9_overtime > 0
        or c.uharm_10_overtime > 0 or c.iharm_10_overtime > 0
        or c.uharm_11_overtime > 0 or c.iharm_11_overtime > 0
        or c.uharm_12_overtime > 0 or c.iharm_12_overtime > 0
        or c.uharm_13_overtime > 0 or c.iharm_13_overtime > 0
        or c.uharm_14_overtime > 0 or c.iharm_14_overtime > 0
        or c.uharm_15_overtime > 0 or c.iharm_15_overtime > 0
        or c.uharm_16_overtime > 0 or c.iharm_16_overtime > 0
        or c.uharm_17_overtime > 0 or c.iharm_17_overtime > 0
        or c.uharm_18_overtime > 0 or c.iharm_18_overtime > 0
        or c.uharm_19_overtime > 0 or c.iharm_19_overtime > 0
        or c.uharm_20_overtime > 0 or c.iharm_20_overtime > 0
        or c.uharm_21_overtime > 0 or c.iharm_21_overtime > 0
        or c.uharm_22_overtime > 0 or c.iharm_22_overtime > 0
        or c.uharm_23_overtime > 0 or c.iharm_23_overtime > 0
        or c.uharm_24_overtime > 0 or c.iharm_24_overtime > 0
        or c.uharm_25_overtime > 0 or c.iharm_25_overtime > 0
        or c.i_neg_overtime > 0
        or c.inuharm_1_overtime > 0 or c.inuharm_2_overtime > 0
        or c.inuharm_3_overtime > 0 or c.inuharm_4_overtime > 0
        or c.inuharm_5_overtime > 0 or c.inuharm_6_overtime > 0
        or c.inuharm_7_overtime > 0 or c.inuharm_8_overtime > 0
        or c.inuharm_9_overtime > 0 or c.inuharm_10_overtime > 0
        or c.inuharm_11_overtime > 0 or c.inuharm_12_overtime > 0
        or c.inuharm_13_overtime > 0 or c.inuharm_14_overtime > 0
        or c.inuharm_15_overtime > 0 or c.inuharm_16_overtime > 0)
        and c.timeid between #{start} and #{end}
        and c.phasic_type = 'T'
        and
        a.line_index = c.myindex
        and a.loadtype = b.dic_index
        group by c.timeid,
        b.dic_index, b.dic_name
    </select>


    <select id="getMonitorBytimeZero" resultType="com.njcn.pojo.data.InfoData">
        select count(distinct c.myindex) countTotal,
        c.timeid totalDate,
        a.monitor_id dicName
        from limit_rate c, pq_linedetail a
        where c.myindex
        in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        and c.timeid between #{start} and #{end}
        and c.phasic_type = 'T'
        and
        a.line_index = c.myindex
        group by c.timeid, a.monitor_id
    </select>


    <select id="getMonitorBytimeOne" resultType="com.njcn.pojo.data.InfoData">
        select count(distinct c.myindex) countTotal,
        c.timeid totalDate,
        a.monitor_id dicName
        from limit_rate c, pq_linedetail a
        where c.myindex
        in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
        and (c.flicker_overtime > 0 or c.ubalance_overtime > 0 or c.freq_dev_overtime > 0 or c.uaberrance_overtime > 0
        or c.voltage_dev_overtime > 0
        or c.uharm_2_overtime > 0 or c.iharm_2_overtime > 0
        or c.uharm_3_overtime > 0 or c.iharm_3_overtime > 0
        or c.uharm_4_overtime > 0 or c.iharm_4_overtime > 0
        or c.uharm_5_overtime > 0 or c.iharm_5_overtime > 0
        or c.uharm_6_overtime > 0 or c.iharm_6_overtime > 0
        or c.uharm_7_overtime > 0 or c.iharm_7_overtime > 0
        or c.uharm_8_overtime > 0 or c.iharm_8_overtime > 0
        or c.uharm_9_overtime > 0 or c.iharm_9_overtime > 0
        or c.uharm_10_overtime > 0 or c.iharm_10_overtime > 0
        or c.uharm_11_overtime > 0 or c.iharm_11_overtime > 0
        or c.uharm_12_overtime > 0 or c.iharm_12_overtime > 0
        or c.uharm_13_overtime > 0 or c.iharm_13_overtime > 0
        or c.uharm_14_overtime > 0 or c.iharm_14_overtime > 0
        or c.uharm_15_overtime > 0 or c.iharm_15_overtime > 0
        or c.uharm_16_overtime > 0 or c.iharm_16_overtime > 0
        or c.uharm_17_overtime > 0 or c.iharm_17_overtime > 0
        or c.uharm_18_overtime > 0 or c.iharm_18_overtime > 0
        or c.uharm_19_overtime > 0 or c.iharm_19_overtime > 0
        or c.uharm_20_overtime > 0 or c.iharm_20_overtime > 0
        or c.uharm_21_overtime > 0 or c.iharm_21_overtime > 0
        or c.uharm_22_overtime > 0 or c.iharm_22_overtime > 0
        or c.uharm_23_overtime > 0 or c.iharm_23_overtime > 0
        or c.uharm_24_overtime > 0 or c.iharm_24_overtime > 0
        or c.uharm_25_overtime > 0 or c.iharm_25_overtime > 0
        or c.i_neg_overtime > 0
        or c.inuharm_1_overtime > 0 or c.inuharm_2_overtime > 0
        or c.inuharm_3_overtime > 0 or c.inuharm_4_overtime > 0
        or c.inuharm_5_overtime > 0 or c.inuharm_6_overtime > 0
        or c.inuharm_7_overtime > 0 or c.inuharm_8_overtime > 0
        or c.inuharm_9_overtime > 0 or c.inuharm_10_overtime > 0
        or c.inuharm_11_overtime > 0 or c.inuharm_12_overtime > 0
        or c.inuharm_13_overtime > 0 or c.inuharm_14_overtime > 0
        or c.inuharm_15_overtime > 0 or c.inuharm_16_overtime > 0)
        and c.timeid between #{start} and #{end}
        and c.phasic_type = 'T'
        and
        a.line_index = c.myindex
        group by c.timeid, a.monitor_id
    </select>


    <select id="getVoltage" resultType="com.njcn.pojo.data.InfoData">
        select distinct (b.dic_index) dicIndex, b.dic_name dicName
        from pq_line
        a, pqs_dicdata b
        where a.scale = b.dic_index
        and a.line_index in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
    </select>

    <select id="getLoadType" resultType="com.njcn.pojo.data.InfoData">
        select distinct (b.dic_index) dicIndex, b.dic_name dicName
        from
        pq_linedetail a, pqs_dicdata b
        where a.loadtype = b.dic_index
        and
        a.line_index in
        <foreach collection="list" item="item" separator="," open="("
                 close=")">
            #{item}
        </foreach>
    </select>

    <select id="getSubNameById" resultType="java.lang.String">
        SELECT NAME
        FROM PQ_SUBSTATION
        WHERE SUB_INDEX = #{id}
    </select>

    <select id="getDevSumForLine" resultType="java.lang.Integer">
        select count(distinct b.dev_index) from pq_line a inner join pq_device b on a.dev_index = b.dev_index
        where a.line_index in
        <foreach collection="list" item="item" open="(" close=")" separator="," index="index">
            #{item}
        </foreach>
    </select>

    <update id="updateLineStatus">
		update pq_line set status = #{status} where DEV_INDEX = #{devIndex}
	</update>
</mapper>
