<?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.HarmonicMapper'>
    <select id="getHarmonic" resultType="harmonic">
        select ROUND(avg(v_2), 2)  v2,
               ROUND(avg(v_3), 2)  v3,
               ROUND(avg(v_4), 2)  v4,
               ROUND(avg(v_5), 2)  v5,
               ROUND(avg(v_6), 2)  v6,
               ROUND(avg(v_7), 2)  v7,
               ROUND(avg(v_8), 2)  v8,
               ROUND(avg(v_9), 2)  v9,
               ROUND(avg(v_10), 2) v10,
               ROUND(avg(v_11), 2) v11,
               ROUND(avg(v_12), 2) v12,
               ROUND(avg(v_13), 2) v13,
               ROUND(avg(v_14), 2) v14,
               ROUND(avg(v_15), 2) v15,
               ROUND(avg(v_16), 2) v16,
               ROUND(avg(v_17), 2) v17,
               ROUND(avg(v_18), 2) v18,
               ROUND(avg(v_19), 2) v19,
               ROUND(avg(v_20), 2) v20,
               ROUND(avg(v_21), 2) v21,
               ROUND(avg(v_22), 2) v22,
               ROUND(avg(v_23), 2) v23,
               ROUND(avg(v_24), 2) v24,
               ROUND(avg(v_25), 2) v25,
               ROUND(avg(v_26), 2) v26,
               ROUND(avg(v_27), 2) v27,
               ROUND(avg(v_28), 2) v28,
               ROUND(avg(v_29), 2) v29,
               ROUND(avg(v_30), 2) v30,
               ROUND(avg(v_31), 2) v31,
               ROUND(avg(v_32), 2) v32,
               ROUND(avg(v_33), 2) v33,
               ROUND(avg(v_34), 2) v34,
               ROUND(avg(v_35), 2) v35,
               ROUND(avg(v_36), 2) v36,
               ROUND(avg(v_37), 2) v37,
               ROUND(avg(v_38), 2) v38,
               ROUND(avg(v_39), 2) v39,
               ROUND(avg(v_40), 2) v40,
               ROUND(avg(v_41), 2) v41,
               ROUND(avg(v_42), 2) v42,
               ROUND(avg(v_43), 2) v43,
               ROUND(avg(v_44), 2) v44,
               ROUND(avg(v_45), 2) v45,
               ROUND(avg(v_46), 2) v46,
               ROUND(avg(v_47), 2) v47,
               ROUND(avg(v_48), 2) v48,
               ROUND(avg(v_49), 2) v49,
               ROUND(avg(v_50), 2) v50

        from Data_HarmRate_V
        where lineId = #{lineId}
          and PHASIC_TYPE != 'T' and TIMEID
	 between #{startTime}
          and #{endTime}
    </select>


    <select id="getoffHarmonic" resultType="Float">
        select round(max(t2.a), 2)
        from (select max(CASE
                             WHEN a2 = #{condition.count} THEN v_${number}
                             ELSE NULL END) as a
              FROM (SELECT t.*,
                           row_number() over(partition BY phasic_type ORDER BY v_${number} DESC) a2
                    FROM Data_HarmRate_V 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 v_${number}
                             ELSE NULL END) as a
              FROM (SELECT t.*,
                           row_number() over(partition BY phasic_type ORDER BY v_${number} DESC) b2
                    FROM Data_HarmRate_V 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 v_${number}
                             ELSE NULL END) as a
              FROM (SELECT t.*,
                           row_number() over(partition BY phasic_type ORDER BY v_${number} DESC) c2
                    FROM Data_HarmRate_V t
                    where lineid = #{condition.pointIndex}
                      and phasic_type = 'C'
                      and timeid between #{condition.start} and #{condition.end}) m3) t2
    </select>
</mapper>
