<?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.common.HarmIMapper'>
    <select id="getHarmI" resultType="harmI">
        select ROUND(avg(i_2), 2)  i2,
               ROUND(avg(i_3), 2)  i3,
               ROUND(avg(i_4), 2)  i4,
               ROUND(avg(i_5), 2)  i5,
               ROUND(avg(i_6), 2)  i6,
               ROUND(avg(i_7), 2)  i7,
               ROUND(avg(i_8), 2)  i8,
               ROUND(avg(i_9), 2)  i9,
               ROUND(avg(i_10), 2) i10,
               ROUND(avg(i_11), 2) i11,
               ROUND(avg(i_12), 2) i12,
               ROUND(avg(i_13), 2) i13,
               ROUND(avg(i_14), 2) i14,
               ROUND(avg(i_15), 2) i15,
               ROUND(avg(i_16), 2) i16,
               ROUND(avg(i_17), 2) i17,
               ROUND(avg(i_18), 2) i18,
               ROUND(avg(i_19), 2) i19,
               ROUND(avg(i_20), 2) i20,
               ROUND(avg(i_21), 2) i21,
               ROUND(avg(i_22), 2) i22,
               ROUND(avg(i_23), 2) i23,
               ROUND(avg(i_24), 2) i24,
               ROUND(avg(i_25), 2) i25,
               ROUND(avg(i_26), 2) i26,
               ROUND(avg(i_27), 2) i27,
               ROUND(avg(i_28), 2) i28,
               ROUND(avg(i_29), 2) i29,
               ROUND(avg(i_30), 2) i30,
               ROUND(avg(i_31), 2) i31,
               ROUND(avg(i_32), 2) i32,
               ROUND(avg(i_33), 2) i33,
               ROUND(avg(i_34), 2) i34,
               ROUND(avg(i_35), 2) i35,
               ROUND(avg(i_36), 2) i36,
               ROUND(avg(i_37), 2) i37,
               ROUND(avg(i_38), 2) i38,
               ROUND(avg(i_39), 2) i39,
               ROUND(avg(i_40), 2) i40,
               ROUND(avg(i_41), 2) i41,
               ROUND(avg(i_42), 2) i42,
               ROUND(avg(i_43), 2) i43,
               ROUND(avg(i_44), 2) i44,
               ROUND(avg(i_45), 2) i45,
               ROUND(avg(i_46), 2) i46,
               ROUND(avg(i_47), 2) i47,
               ROUND(avg(i_48), 2) i48,
               ROUND(avg(i_49), 2) i49,
               ROUND(avg(i_50), 2) i50 from Data_i where lineId=
               #{lineId} and PHASIC_TYPE != 'T' and TIMEID
            between
               #{startTime} and
               #{endTime}
    </select>

    <select id="getoffHarmI" resultType="Float">
        select round(max(t2.a), 2)
        from (select max(CASE
                             WHEN a2 = #{condition.count} THEN i_${number}
                             ELSE NULL END) as a
              FROM (SELECT t.*,
                           row_number() over(partition BY phasic_type ORDER BY i_${number} DESC) a2
                    FROM Data_i t
                    where lineid = #{condition.pointIndex}
                      and phasic_type = 'A'
                      and timeid between #{condition.start} and #{condition.end}) m1
              UNION all
              select max(CASE
                             WHEN b2 = #{condition.count} THEN i_${number}
                             ELSE NULL END) as a
              FROM (SELECT t.*,
                           row_number() over(partition BY phasic_type ORDER BY i_${number} DESC) b2
                    FROM Data_i t
                    where lineid = #{condition.pointIndex}
                      and phasic_type = 'B'
                      and timeid between #{condition.start} and #{condition.end}) m2
              UNION all
              select max(CASE
                             WHEN c2 = #{condition.count} THEN i_${number}
                             ELSE NULL END) as a
              FROM (SELECT t.*,
                           row_number() over(partition BY phasic_type ORDER BY i_${number} DESC) c2
                    FROM Data_i t
                    where lineid = #{condition.pointIndex}
                      and phasic_type = 'C'
                      and timeid between #{condition.start} and #{condition.end}) m3) t2
    </select>
</mapper>
