<?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.pqs9000.mapper.responsibility.OverLimitMonitorMapper">
    <select id="getOverLimitMonitorList" resultType="com.pqs9000.pojo.responsibility.OverLimitMonitor">
        SELECT * from
        (
        SELECT
        primary.LINE_INDEX monitorId,
        primary.NAME monitorName,
        primary.cityCompany,
        primary.provinceCompany,
        primary.MONITORVOLTAGELEVEL,
        primary.PLANTNAME,
        primary.PLANTVOLAGELEVEL,
        primary.objName,
        primary.powerSubstationName,
        primary.DLCMP dlcMp,
        primary.DEVCMP devcMp,
        primary.XYCMP xycMp,
        NVL(A.OVERTIMES, 0) overLimitDay,
        NVL(B.OVERTIMES, 0) overFrequencyDeviationDay,
        NVL(C.OVERTIMES, 0) overVoltageDeviationDay,
        NVL(harmV.OVERTIMES,0) overLimitHarmVDay,
        NVL(D.OVERTIMES, 0) overVoltageDistortionDay,
        NVL(E.OVERTIMES, 0) overHarmonicVoltageDay,
        NVL(F.OVERTIMES, 0) overHarmonicCurrentDay,
        NVL(G.OVERTIMES, 0) overVoltageThreeTimes,
        NVL(H.OVERTIMES, 0) overVoltageFiveTimes,
        NVL(I.OVERTIMES, 0) overVoltageSevenTimes,
        NVL(J.OVERTIMES, 0) overVoltageElevenTimes,
        NVL(K.OVERTIMES, 0) overVoltageThirteenTimes,
        NVL(L.OVERTIMES, 0) overVoltageTwentyThirdTimes,
        NVL(M.OVERTIMES, 0) overVoltageTwentyFiveTimes,
        NVL(N.OVERTIMES, 0) overVoltageOtherTimes,
        NVL(O.OVERTIMES, 0) overCurrentThreeTimes,
        NVL(P.OVERTIMES, 0) overCurrentFiveTimes,
        NVL(Q.OVERTIMES, 0) overCurrentSevenTimes,
        NVL(R.OVERTIMES, 0) overCurrentElevenTimes,
        NVL(S.OVERTIMES, 0) overCurrentThirteenTimes,
        NVL(T.OVERTIMES, 0) overCurrentTwentyThirdTimes,
        NVL(U.OVERTIMES, 0) overCurrentTwentyFiveTimes,
        NVL(V.OVERTIMES, 0) overCurrentOtherTimes,
        NVL(W.OVERTIMES, 0) overThreeVoltageUnbalanceDay,
        NVL(X.OVERTIMES, 0) overFlickerDay,
        Y.monitorType,
        Y.monitorNumber,
        NVL(Z.OVERTIMES, 0) overNegativeDay,
        NVL(interharmonics.OVERTIMES, 0) overInterharmonicsDay
        FROM
        (
        SELECT
        fir.LINE LINE_INDEX,
        fir.NAME,
        fir.objName,
        fir.DLCMP ,
        fir.DEVCMP ,
        fir.XYCMP ,
        A.DIC_NAME cityCompany,
        B.DEPTSNAME provinceCompany,
        C.DIC_NAME MONITORVOLTAGELEVEL,
        fir.SUBNAME PLANTNAME,
        fir.powerSubstationName,
        D.DIC_NAME PLANTVOLAGELEVEL
        FROM
        (
        SELECT
        DISTINCT(A.LINE_INDEX) line,
        A.NAME,
        A.DLCMP ,
        A.DEVCMP ,
        A.XYCMP ,
        C.AREA,
        C.PARENTNODEID,
        A.SCALE lineScale,
        D.NAME subName,
        D.SCALE subScale,
        E.objname objName,
        E.POWER_SUBSTATION_NAME powerSubstationName
        FROM
        PQ_LINE A,
        PQS_DEPTSLINE B,
        PQS_DEPTS C,
        PQ_SUBSTATION D,
        PQ_LINEDETAIL E
        WHERE A.LINE_INDEX IN
        <foreach collection="list" item="list1" open="(" close=")" separator=",">
            #{list1}
        </foreach>
        AND A.LINE_INDEX = B.LINE_INDEX
        AND B.DEPTS_INDEX = C.DEPTS_INDEX
        <!-- 不限制部门类型 0:非自定义部门 1:自定义部门-->
        <!-- AND C.CUSTOM_DEPT = 0  -->
        AND c.state = 1
        AND A.SUB_INDEX = D.SUB_INDEX
        AND A.LINE_INDEX = E.LINE_INDEX
        <if test=" vollevel != ''">
            AND A.SCALE=#{vollevel}
        </if>
        ) fir
        LEFT JOIN
        PQS_DICDATA A
        ON fir.AREA = A.DIC_INDEX
        LEFT JOIN
        PQS_DEPTS B
        ON fir.PARENTNODEID = B.DEPTS_INDEX
        LEFT JOIN
        PQS_DICDATA C
        ON fir.lineScale = C.DIC_INDEX
        LEFT JOIN
        PQS_DICDATA D
        ON fir.SUBSCALE = D.DIC_INDEX
        ORDER BY fir.LINE
        ) primary
        LEFT JOIN
        (SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list2" open="(" close=")" separator=",">
            #{list2}
        </foreach>
        and (flicker_overtime >0 or ubalance_overtime >0 or freq_dev_overtime >0 or uaberrance_overtime >0 or voltage_dev_overtime >0 or uharm_2_overtime >0 or iharm_2_overtime >0 or uharm_3_overtime >0 or iharm_3_overtime >0 or uharm_4_overtime >0 or iharm_4_overtime >0
        or uharm_5_overtime >0 or iharm_5_overtime >0 or uharm_6_overtime >0 or iharm_6_overtime >0 or uharm_7_overtime >0 or iharm_7_overtime >0
        or uharm_8_overtime >0 or iharm_8_overtime >0 or uharm_9_overtime >0 or iharm_9_overtime >0 or uharm_10_overtime >0 or iharm_10_overtime >0
        or uharm_11_overtime >0 or iharm_11_overtime >0 or uharm_12_overtime >0 or iharm_12_overtime >0 or uharm_13_overtime >0 or iharm_13_overtime >0
        or uharm_14_overtime >0 or iharm_14_overtime >0 or uharm_15_overtime >0 or iharm_15_overtime >0 or uharm_16_overtime >0 or iharm_16_overtime >0
        or uharm_17_overtime >0 or iharm_17_overtime >0 or uharm_18_overtime >0 or iharm_18_overtime >0 or uharm_19_overtime >0 or iharm_19_overtime >0
        or uharm_20_overtime >0 or iharm_20_overtime >0 or uharm_21_overtime >0 or iharm_24_overtime >0 or uharm_22_overtime >0 or iharm_22_overtime >0
        or uharm_23_overtime >0 or iharm_23_overtime >0 or uharm_24_overtime >0 or iharm_24_overtime >0 or uharm_25_overtime >0 or iharm_25_overtime >0
        or I_NEG_OVERTIME > 0 or INUHARM_1_OVERTIME >0 or INUHARM_2_OVERTIME >0 or INUHARM_3_OVERTIME >0 or INUHARM_4_OVERTIME >0 or INUHARM_5_OVERTIME >0
        or INUHARM_6_OVERTIME >0 or INUHARM_7_OVERTIME >0 or INUHARM_8_OVERTIME >0 or INUHARM_9_OVERTIME >0 or INUHARM_10_OVERTIME >0 or INUHARM_11_OVERTIME >0
        or INUHARM_12_OVERTIME >0 or INUHARM_13_OVERTIME >0 or INUHARM_14_OVERTIME >0 or INUHARM_15_OVERTIME >0 or INUHARM_16_OVERTIME >0
        )  GROUP BY MYINDEX
        )A
        ON primary.LINE_INDEX = A.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list3" open="(" close=")" separator=",">
            #{list3}
        </foreach>
        and freq_dev_overtime >0 GROUP BY MYINDEX
        ) B
        ON primary.LINE_INDEX = B.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list4" open="(" close=")" separator=",">
            #{list4}
        </foreach>
        and voltage_dev_overtime >0 GROUP BY MYINDEX
        ) C
        ON primary.LINE_INDEX = C.MYINDEX
        LEFT JOIN
        (select MYINDEX,COUNT(1) overTimes
        from limit_target
        where phasic_type = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list4" open="(" close=")"
                 separator=",">
            #{list4}
        </foreach>
        and (uaberrance_overtime > 0 or uharm_2_overtime > 0 or
        uharm_3_overtime > 0 or uharm_4_overtime > 0 or
        uharm_5_overtime > 0 or uharm_6_overtime > 0 or
        uharm_7_overtime > 0 or uharm_8_overtime > 0 or
        uharm_9_overtime > 0 or uharm_10_overtime > 0 or
        uharm_11_overtime > 0 or uharm_12_overtime > 0 or
        uharm_13_overtime > 0 or uharm_14_overtime > 0 or
        uharm_15_overtime > 0 or uharm_16_overtime > 0 or
        uharm_17_overtime > 0 or uharm_18_overtime > 0 or
        uharm_19_overtime > 0 or uharm_20_overtime > 0 or
        uharm_21_overtime > 0 or uharm_22_overtime > 0 or
        uharm_23_overtime > 0 or uharm_24_overtime > 0 or
        uharm_25_overtime > 0) GROUP BY MYINDEX
        ) harmV
        ON primary.LINE_INDEX = harmV.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list5" open="(" close=")" separator=",">
            #{list5}
        </foreach>
        and uaberrance_overtime >0 GROUP BY MYINDEX
        ) D
        ON primary.LINE_INDEX = D.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list6" open="(" close=")" separator=",">
            #{list6}
        </foreach>
        and (uharm_2_overtime >0 or uharm_3_overtime >0 or uharm_4_overtime >0 or uharm_5_overtime >0 or uharm_6_overtime >0 or uharm_7_overtime >0 or uharm_8_overtime >0 or uharm_9_overtime >0 or uharm_10_overtime >0 or uharm_11_overtime >0 or uharm_12_overtime >0 or uharm_13_overtime >0 or uharm_14_overtime >0 or uharm_15_overtime >0 or uharm_16_overtime >0 or uharm_17_overtime >0 or uharm_18_overtime >0 or uharm_19_overtime >0 or uharm_20_overtime >0 or uharm_21_overtime >0 or uharm_22_overtime >0 or uharm_23_overtime >0 or uharm_24_overtime >0 or uharm_25_overtime >0) GROUP BY MYINDEX
        ) E
        ON primary.LINE_INDEX = E.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list7" open="(" close=")" separator=",">
            #{list7}
        </foreach>
        and (iharm_2_overtime >0 or iharm_3_overtime >0 or iharm_4_overtime >0 or iharm_5_overtime >0 or iharm_6_overtime >0 or iharm_7_overtime >0 or iharm_8_overtime >0 or iharm_9_overtime >0 or iharm_10_overtime >0 or iharm_11_overtime >0 or iharm_12_overtime >0 or iharm_13_overtime >0 or iharm_14_overtime >0 or iharm_15_overtime >0 or iharm_16_overtime >0 or iharm_17_overtime >0 or iharm_18_overtime >0 or iharm_19_overtime >0 or iharm_20_overtime >0 or iharm_21_overtime >0 or iharm_22_overtime >0 or iharm_23_overtime >0 or iharm_24_overtime >0 or iharm_25_overtime >0) GROUP BY MYINDEX
        ) F
        ON primary.LINE_INDEX = F.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list8" open="(" close=")" separator=",">
            #{list8}
        </foreach>
        and uharm_3_overtime >0 GROUP BY MYINDEX
        ) G
        ON primary.LINE_INDEX = G.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list9" open="(" close=")" separator=",">
            #{list9}
        </foreach>
        and uharm_5_overtime >0 GROUP BY MYINDEX
        ) H
        ON primary.LINE_INDEX = H.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list10" open="(" close=")" separator=",">
            #{list10}
        </foreach>
        and uharm_7_overtime >0 GROUP BY MYINDEX
        ) I
        ON primary.LINE_INDEX = I.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list11" open="(" close=")" separator=",">
            #{list11}
        </foreach>
        and uharm_11_overtime >0 GROUP BY MYINDEX
        ) J
        ON primary.LINE_INDEX = J.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list12" open="(" close=")" separator=",">
            #{list12}
        </foreach>
        and uharm_13_overtime >0 GROUP BY MYINDEX
        ) K
        ON primary.LINE_INDEX = K.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list13" open="(" close=")" separator=",">
            #{list13}
        </foreach>
        and uharm_23_overtime >0 GROUP BY MYINDEX
        ) L
        ON primary.LINE_INDEX = L.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list14" open="(" close=")" separator=",">
            #{list14}
        </foreach>
        and uharm_25_overtime >0 GROUP BY MYINDEX
        ) M
        ON primary.LINE_INDEX = M.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,SUM(uharm_2_overtime + uharm_4_overtime  + uharm_6_overtime  + uharm_8_overtime  + uharm_9_overtime  + uharm_10_overtime  + uharm_12_overtime  + uharm_14_overtime  + uharm_15_overtime  + uharm_16_overtime  + uharm_17_overtime  + uharm_18_overtime  + uharm_19_overtime + uharm_20_overtime + uharm_21_overtime + uharm_22_overtime + uharm_24_overtime) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list15" open="(" close=")" separator=",">
            #{list15}
        </foreach>
        and (uharm_2_overtime >0 or uharm_4_overtime >0 or uharm_6_overtime >0 or uharm_8_overtime >0 or uharm_9_overtime >0 or uharm_10_overtime >0 or uharm_12_overtime >0 or uharm_14_overtime >0 or uharm_15_overtime >0 or uharm_16_overtime >0 or uharm_17_overtime >0 or uharm_18_overtime >0 or uharm_19_overtime >0 or uharm_20_overtime >0 or uharm_21_overtime >0 or uharm_22_overtime >0 or uharm_24_overtime >0) GROUP BY MYINDEX
        ) N
        ON primary.LINE_INDEX = N.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list16" open="(" close=")" separator=",">
            #{list16}
        </foreach>
        and iharm_3_overtime >0 GROUP BY MYINDEX
        ) O
        ON primary.LINE_INDEX = O.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list17" open="(" close=")" separator=",">
            #{list17}
        </foreach>
        and iharm_5_overtime >0 GROUP BY MYINDEX
        ) P
        ON primary.LINE_INDEX = P.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list18" open="(" close=")" separator=",">
            #{list18}
        </foreach>
        and iharm_7_overtime >0 GROUP BY MYINDEX
        ) Q
        ON primary.LINE_INDEX = Q.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list19" open="(" close=")" separator=",">
            #{list19}
        </foreach>
        and iharm_11_overtime >0 GROUP BY MYINDEX
        ) R
        ON primary.LINE_INDEX = R.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list20" open="(" close=")" separator=",">
            #{list20}
        </foreach>
        and iharm_13_overtime >0 GROUP BY MYINDEX
        ) S
        ON primary.LINE_INDEX = S.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list21" open="(" close=")" separator=",">
            #{list21}
        </foreach>
        and iharm_23_overtime >0 GROUP BY MYINDEX
        ) T
        ON primary.LINE_INDEX = T.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list22" open="(" close=")" separator=",">
            #{list22}
        </foreach>
        and iharm_25_overtime >0 GROUP BY MYINDEX
        ) U
        ON primary.LINE_INDEX = U.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,SUM(iharm_2_overtime + iharm_4_overtime + iharm_6_overtime + iharm_8_overtime + iharm_9_overtime + iharm_10_overtime + iharm_12_overtime + iharm_14_overtime + iharm_15_overtime + iharm_16_overtime + iharm_17_overtime + iharm_18_overtime + iharm_19_overtime + iharm_20_overtime + iharm_21_overtime + iharm_22_overtime + iharm_24_overtime) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list23" open="(" close=")" separator=",">
            #{list23}
        </foreach>
        and (iharm_2_overtime >0 or iharm_4_overtime >0 or iharm_6_overtime >0 or iharm_8_overtime >0 or iharm_9_overtime >0 or iharm_10_overtime >0 or iharm_12_overtime >0 or iharm_14_overtime >0 or iharm_15_overtime >0 or iharm_16_overtime >0 or iharm_17_overtime >0 or iharm_18_overtime >0 or iharm_19_overtime >0 or iharm_20_overtime >0 or iharm_21_overtime >0 or iharm_22_overtime >0 or iharm_24_overtime >0) GROUP BY MYINDEX
        ) V
        ON primary.LINE_INDEX = V.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list24" open="(" close=")" separator=",">
            #{list24}
        </foreach>
        and ubalance_overtime >0  GROUP BY MYINDEX
        ) W
        ON primary.LINE_INDEX = W.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list25" open="(" close=")" separator=",">
            #{list25}
        </foreach>
        and flicker_overtime >0  GROUP BY MYINDEX
        ) X
        ON primary.LINE_INDEX = X.MYINDEX
        LEFT JOIN
        (
        SELECT
        pqld.LINE_INDEX MYINDEX,
        pd.DIC_NAME monitorType,
        pqld.MONITOR_ID monitorNumber
        FROM PQ_LINEDETAIL pqld,PQS_DICDATA pd WHERE LINE_INDEX IN
        <foreach collection="list" item="list26" open="(" close=")" separator=",">
            #{list26}
        </foreach>
        AND pqld.LOADTYPE = pd.DIC_INDEX
        ) Y
        ON primary.LINE_INDEX = Y.MYINDEX
        LEFT JOIN
        (
        SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
        AND MYINDEX IN
        <foreach collection="list" item="list27" open="(" close=")" separator=",">
            #{list27}
        </foreach>
        and I_Neg_OverTime >0  GROUP BY MYINDEX
        ) Z
        ON primary.LINE_INDEX = Z.MYINDEX
        LEFT JOIN
        (SELECT
        MYINDEX,COUNT(1) overTimes
        FROM
        limit_target
        WHERE
        PHASIC_TYPE = 'T' AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS') AND
        MYINDEX IN
        <foreach collection="list" item="list28" open="(" close=")" separator=",">
            #{list28}
        </foreach>
        and (InUHARM_1_OverTime >0 or InUHARM_2_OverTime >0 or InUHARM_3_OverTime >0 or InUHARM_4_OverTime >0 or InUHARM_5_OverTime >0 or InUHARM_6_OverTime >0
        or InUHARM_7_OverTime >0 or InUHARM_8_OverTime >0 or InUHARM_9_OverTime >0 or InUHARM_10_OverTime >0 or InUHARM_11_OverTime >0 or InUHARM_12_OverTime >0
        or InUHARM_13_OverTime >0 or InUHARM_14_OverTime >0 or InUHARM_15_OverTime >0 or InUHARM_16_OverTime >0)  GROUP BY MYINDEX) interharmonics
        ON primary.LINE_INDEX = interharmonics.MYINDEX
        ORDER BY primary.provinceCompany,primary.cityCompany,primary.NAME
        ) where overLimitDay >0
    </select>

    <select id="getLineList" resultType="java.lang.String">
        SELECT
            DISTINCT MYINDEX
        FROM
            LIMIT_TARGET
        WHERE
            (ALLTIME &gt; 0 OR FLICKET_ALLTIME &gt; 0)
            AND TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
    </select>

    <select id="getData1" resultType="com.pqs9000.pojo.responsibility.TargetDetailDto">
        SELECT
            A.LINEID monitorId,
            max(abs(A.VU_DEV_MAX)) maxVoltageDeviationData,
            B.VOLTAGE_DEV limitVoltageDeviationData,
            max(abs(A.VU_DEV_MAX))-B.VOLTAGE_DEV diffVoltageDeviationData,
            max(A.V_THD_CP95) maxVoltageDistortionData,
            B.UBALANCE limitVoltageDistortionData,
            max(A.V_THD_CP95)-B.UBALANCE diffVoltageDistortionData,
            max(A.V_UNBALANCE_CP95) maxThreeVoltageUnbalanceData,
            B.UABERRANCE limitThreeVoltageUnbalanceData,
            max(A.V_UNBALANCE_CP95)-B.UABERRANCE diffThreeVoltageUnbalanceData,
            max(abs(A.FREQ_DEV_MAX)) maxFrequencyDeviationData,
            B.FREQ_DEV limitFrequencyDeviationData,
            max(abs(A.FREQ_DEV_MAX))-B.FREQ_DEV diffFrequencyDeviationData
        FROM
            DATA_V A
            RIGHT JOIN OVERLIMIT B ON A.LINEID = B.MYINDEX
        WHERE
            A.TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
            AND B.MYINDEX IN
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        GROUP BY A.LINEID,B.VOLTAGE_DEV,B.UBALANCE,B.UABERRANCE,B.FREQ_DEV
    </select>

    <select id="getData2" resultType="com.pqs9000.pojo.responsibility.TargetDetailDto">
        SELECT
            A.LINEID monitorId,
            max(A.I_NEG_CP95) maxNegativeDay,
            B.I_NEG limitNegativeData,
            max(A.I_NEG_CP95)-B.I_NEG diffNegativeData
        FROM
            DATA_I A
            RIGHT JOIN OVERLIMIT B ON A.LINEID = B.MYINDEX
        WHERE
            A.TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
            AND B.MYINDEX IN
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        GROUP BY A.LINEID,B.I_NEG
    </select>

    <select id="getData3" resultType="com.pqs9000.pojo.responsibility.TargetDetailDto">
        SELECT
            A.LINEID monitorId,
            max(A.PLT) maxFlickerData,
            B.FLICKER limitFlickerData,
            max(A.PLT)-B.FLICKER  diffFlickerData
        FROM
            DATA_PLT A
            RIGHT JOIN OVERLIMIT B ON A.LINEID = B.MYINDEX
        WHERE
            A.TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
            AND B.MYINDEX IN
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        GROUP BY A.LINEID,B.FLICKER
    </select>

    <select id="getData4" resultType="com.pqs9000.pojo.responsibility.TargetDetailDto">
        SELECT
            A.LINEID monitorId,
            MAX(A.I_2_CP95/NULLIF(B.IHARM_2,0)) ratio1,
            MAX(A.I_3_CP95/NULLIF(B.IHARM_3,0)) ratio2,
            MAX(A.I_4_CP95/NULLIF(B.IHARM_4,0)) ratio3,
            MAX(A.I_5_CP95/NULLIF(B.IHARM_5,0)) ratio4,
            MAX(A.I_6_CP95/NULLIF(B.IHARM_6,0)) ratio5,
            MAX(A.I_7_CP95/NULLIF(B.IHARM_7,0)) ratio6,
            MAX(A.I_8_CP95/NULLIF(B.IHARM_8,0)) ratio7,
            MAX(A.I_9_CP95/NULLIF(B.IHARM_9,0)) ratio8,
            MAX(A.I_10_CP95/NULLIF(B.IHARM_10,0)) ratio9,
            MAX(A.I_11_CP95/NULLIF(B.IHARM_11,0)) ratio10,
            MAX(A.I_12_CP95/NULLIF(B.IHARM_12,0)) ratio11,
            MAX(A.I_13_CP95/NULLIF(B.IHARM_13,0)) ratio12,
            MAX(A.I_14_CP95/NULLIF(B.IHARM_14,0)) ratio13,
            MAX(A.I_15_CP95/NULLIF(B.IHARM_15,0)) ratio14,
            MAX(A.I_16_CP95/NULLIF(B.IHARM_16,0)) ratio15,
            MAX(A.I_17_CP95/NULLIF(B.IHARM_17,0)) ratio16,
            MAX(A.I_18_CP95/NULLIF(B.IHARM_18,0)) ratio17,
            MAX(A.I_19_CP95/NULLIF(B.IHARM_19,0)) ratio18,
            MAX(A.I_20_CP95/NULLIF(B.IHARM_20,0)) ratio19,
            MAX(A.I_21_CP95/NULLIF(B.IHARM_21,0)) ratio20,
            MAX(A.I_22_CP95/NULLIF(B.IHARM_22,0)) ratio21,
            MAX(A.I_23_CP95/NULLIF(B.IHARM_23,0)) ratio22,
            MAX(A.I_24_CP95/NULLIF(B.IHARM_24,0)) ratio23,
            MAX(A.I_25_CP95/NULLIF(B.IHARM_25,0)) ratio24
        FROM
            DATA_I A
        RIGHT JOIN OVERLIMIT B ON A.LINEID = B.MYINDEX
        WHERE
            A.TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
            AND B.MYINDEX IN
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        GROUP BY A.LINEID
    </select>

    <select id="getData5" resultType="com.pqs9000.pojo.responsibility.TargetDetailDto">
        SELECT
            A.LINEID monitorId,
            MAX(A.V_THD_CP95/NULLIF(B.UBALANCE,0)) ratio1,
            MAX(C.V_2_CP95/NULLIF(B.UHARM_2,0)) ratio2,
            MAX(C.V_3_CP95/NULLIF(B.UHARM_3,0)) ratio3,
            MAX(C.V_4_CP95/NULLIF(B.UHARM_4,0)) ratio4,
            MAX(C.V_5_CP95/NULLIF(B.UHARM_5,0)) ratio5,
            MAX(C.V_6_CP95/NULLIF(B.UHARM_6,0)) ratio6,
            MAX(C.V_7_CP95/NULLIF(B.UHARM_7,0)) ratio7,
            MAX(C.V_8_CP95/NULLIF(B.UHARM_8,0)) ratio8,
            MAX(C.V_9_CP95/NULLIF(B.UHARM_9,0)) ratio9,
            MAX(C.V_10_CP95/NULLIF(B.UHARM_10,0)) ratio10,
            MAX(C.V_11_CP95/NULLIF(B.UHARM_11,0)) ratio11,
            MAX(C.V_12_CP95/NULLIF(B.UHARM_12,0)) ratio12,
            MAX(C.V_13_CP95/NULLIF(B.UHARM_13,0)) ratio13,
            MAX(C.V_14_CP95/NULLIF(B.UHARM_14,0)) ratio14,
            MAX(C.V_15_CP95/NULLIF(B.UHARM_15,0)) ratio15,
            MAX(C.V_16_CP95/NULLIF(B.UHARM_16,0)) ratio16,
            MAX(C.V_17_CP95/NULLIF(B.UHARM_17,0)) ratio17,
            MAX(C.V_18_CP95/NULLIF(B.UHARM_18,0)) ratio18,
            MAX(C.V_19_CP95/NULLIF(B.UHARM_19,0)) ratio19,
            MAX(C.V_20_CP95/NULLIF(B.UHARM_20,0)) ratio20,
            MAX(C.V_21_CP95/NULLIF(B.UHARM_21,0)) ratio21,
            MAX(C.V_22_CP95/NULLIF(B.UHARM_22,0)) ratio22,
            MAX(C.V_23_CP95/NULLIF(B.UHARM_23,0)) ratio23,
            MAX(C.V_24_CP95/NULLIF(B.UHARM_24,0)) ratio24,
            MAX(C.V_25_CP95/NULLIF(B.UHARM_25,0)) ratio25
        FROM
            OVERLIMIT B
        LEFT JOIN DATA_V A ON A.LINEID = B.MYINDEX
        LEFT JOIN Data_HarmRate_V C ON C.LINEID = B.MYINDEX
        WHERE
            A.TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
            AND B.MYINDEX IN
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            AND A.TIMEID = C.TIMEID
        GROUP BY A.LINEID
    </select>

    <select id="getData6" resultType="com.pqs9000.pojo.responsibility.TargetDetailDto">
        SELECT
            A.LINEID monitorId,
            MAX(A.V_2_CP95/NULLIF(B.UHARM_2,0)) ratio2,
            MAX(A.V_3_CP95/NULLIF(B.UHARM_3,0)) ratio3,
            MAX(A.V_4_CP95/NULLIF(B.UHARM_4,0)) ratio4,
            MAX(A.V_5_CP95/NULLIF(B.UHARM_5,0)) ratio5,
            MAX(A.V_6_CP95/NULLIF(B.UHARM_6,0)) ratio6,
            MAX(A.V_7_CP95/NULLIF(B.UHARM_7,0)) ratio7,
            MAX(A.V_8_CP95/NULLIF(B.UHARM_8,0)) ratio8,
            MAX(A.V_9_CP95/NULLIF(B.UHARM_9,0)) ratio9,
            MAX(A.V_10_CP95/NULLIF(B.UHARM_10,0)) ratio10,
            MAX(A.V_11_CP95/NULLIF(B.UHARM_11,0)) ratio11,
            MAX(A.V_12_CP95/NULLIF(B.UHARM_12,0)) ratio12,
            MAX(A.V_13_CP95/NULLIF(B.UHARM_13,0)) ratio13,
            MAX(A.V_14_CP95/NULLIF(B.UHARM_14,0)) ratio14,
            MAX(A.V_15_CP95/NULLIF(B.UHARM_15,0)) ratio15,
            MAX(A.V_16_CP95/NULLIF(B.UHARM_16,0)) ratio16,
            MAX(A.V_17_CP95/NULLIF(B.UHARM_17,0)) ratio17,
            MAX(A.V_18_CP95/NULLIF(B.UHARM_18,0)) ratio18,
            MAX(A.V_19_CP95/NULLIF(B.UHARM_19,0)) ratio19,
            MAX(A.V_20_CP95/NULLIF(B.UHARM_20,0)) ratio20,
            MAX(A.V_21_CP95/NULLIF(B.UHARM_21,0)) ratio21,
            MAX(A.V_22_CP95/NULLIF(B.UHARM_22,0)) ratio22,
            MAX(A.V_23_CP95/NULLIF(B.UHARM_23,0)) ratio23,
            MAX(A.V_24_CP95/NULLIF(B.UHARM_24,0)) ratio24,
            MAX(A.V_25_CP95/NULLIF(B.UHARM_25,0)) ratio25
        FROM
            Data_HarmRate_V A
        RIGHT JOIN OVERLIMIT B ON A.LINEID = B.MYINDEX
        WHERE
            A.TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
            AND B.MYINDEX IN
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        GROUP BY A.LINEID
    </select>

    <select id="getData7" resultType="com.pqs9000.pojo.responsibility.TargetDetailDto">
        SELECT
            A.LINEID monitorId,
            MAX(A.V_1_CP95/NULLIF(B.INUHARM_1,0)) ratio1,
            MAX(A.V_2_CP95/NULLIF(B.INUHARM_2,0)) ratio2,
            MAX(A.V_3_CP95/NULLIF(B.INUHARM_3,0)) ratio3,
            MAX(A.V_4_CP95/NULLIF(B.INUHARM_4,0)) ratio4,
            MAX(A.V_5_CP95/NULLIF(B.INUHARM_5,0)) ratio5,
            MAX(A.V_6_CP95/NULLIF(B.INUHARM_6,0)) ratio6,
            MAX(A.V_7_CP95/NULLIF(B.INUHARM_7,0)) ratio7,
            MAX(A.V_8_CP95/NULLIF(B.INUHARM_8,0)) ratio8,
            MAX(A.V_9_CP95/NULLIF(B.INUHARM_9,0)) ratio9,
            MAX(A.V_10_CP95/NULLIF(B.INUHARM_10,0)) ratio10,
            MAX(A.V_11_CP95/NULLIF(B.INUHARM_11,0)) ratio11,
            MAX(A.V_12_CP95/NULLIF(B.INUHARM_12,0)) ratio12,
            MAX(A.V_13_CP95/NULLIF(B.INUHARM_13,0)) ratio13,
            MAX(A.V_14_CP95/NULLIF(B.INUHARM_14,0)) ratio14,
            MAX(A.V_15_CP95/NULLIF(B.INUHARM_15,0)) ratio15,
            MAX(A.V_16_CP95/NULLIF(B.INUHARM_16,0)) ratio16
        FROM
            Data_InHarm_V A
        RIGHT JOIN OVERLIMIT B ON A.LINEID = B.MYINDEX
        WHERE
            A.TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
            AND B.MYINDEX IN
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        GROUP BY A.LINEID
    </select>

    <select id="getHarmonicData1" resultType="com.pqs9000.pojo.responsibility.TargetDetailDto">
        SELECT
            A.MYINDEX monitorId,
            A.IHARM_${time} data1,
            MAX(B.I_${time}_CP95) data2
        FROM
            OVERLIMIT A
        LEFT JOIN DATA_I B ON
            A.MYINDEX = B.LINEID
        WHERE
            A.MYINDEX = #{lineId} AND B.TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
            GROUP BY A.MYINDEX,A.IHARM_${time}
    </select>

    <select id="getHarmonicData2" resultType="com.pqs9000.pojo.responsibility.TargetDetailDto">
        SELECT
            A.MYINDEX monitorId,
            A.UBALANCE data1,
            MAX(B.V_THD_CP95) data2
        FROM
            OVERLIMIT A
        LEFT JOIN DATA_V B ON
            A.MYINDEX = B.LINEID
        WHERE
            A.MYINDEX = #{lineId} AND B.TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
            GROUP BY A.MYINDEX,A.UBALANCE
    </select>

    <select id="getHarmonicData3" resultType="com.pqs9000.pojo.responsibility.TargetDetailDto">
        SELECT
            A.MYINDEX monitorId,
            A.UHARM_${time} data1,
            MAX(B.V_${time}_CP95) data2
        FROM
            OVERLIMIT A
        LEFT JOIN Data_HarmRate_V B ON
            A.MYINDEX = B.LINEID
        WHERE
            A.MYINDEX = #{lineId} AND B.TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
            GROUP BY A.MYINDEX,A.UHARM_${time}
    </select>

    <select id="getHarmonicData4" resultType="com.pqs9000.pojo.responsibility.TargetDetailDto">
        SELECT
            A.MYINDEX monitorId,
            A.INUHARM_${time} data1,
            MAX(B.V_${time}_CP95) data2
        FROM
            OVERLIMIT A
        LEFT JOIN Data_InHarm_V B ON
            A.MYINDEX = B.LINEID
        WHERE
            A.MYINDEX = #{lineId} AND B.TIMEID BETWEEN TO_DATE(#{startTime}, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(#{endTime}, 'YYYY-MM-DD HH24:MI:SS')
            GROUP BY A.MYINDEX,A.INUHARM_${time}
    </select>

</mapper>
