<?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.excelreport.GetThreephaseMapper">
    <select id="getThreephase" resultType="java.util.HashMap">
        select max(${str}_Unbalance_MAX) ${str}_Unbalance_MAX,min(${str}_Unbalance_MIN)
        ${str}_Unbalance_MIN,round(avg(${str}_Unbalance) ,2) ${str}_Unbalance,
        max(CASE WHEN ${str}unbalancecp95 = #{conditionData.count}
        THEN ${str}_Unbalance
        ELSE NULL END) ${str}_Unbalance_CP95
        ,max(${str}_Pos_MAX) ${str}_Pos_MAX,min(${str}_Pos_MIN) ${str}_Pos_MIN,round(avg(${str}_Pos) ,2)
        ${str}_Pos,
        max(CASE WHEN ${str}poscp95 = #{conditionData.count}
        THEN ${str}_Pos
        ELSE NULL END) ${str}_Pos_CP95
        ,max(${str}_Neg_MAX) ${str}_Neg_MAX,min(${str}_Neg_MIN) ${str}_Neg_MIN,round(avg(${str}_Neg) ,2)
        ${str}_Neg,
        max(CASE WHEN ${str}negcp95 = #{conditionData.count}
        THEN ${str}_Neg
        ELSE NULL END) ${str}_Neg_CP95
        ,max(${str}_Zero_MAX) ${str}_Zero_MAX,min(${str}_Zero_MIN) ${str}_Zero_MIN,round(avg(${str}_Zero) ,2)
        ${str}_Zero,
        max(CASE WHEN ${str}zerocp95 = #{conditionData.count}
        THEN ${str}_Zero
        ELSE NULL END) ${str}_Zero_CP95
        FROM (SELECT
        t.*,
        row_number() over (partition BY phasic_type ORDER BY ${str}_Unbalance DESC) ${str}unbalancecp95,
        row_number() over (partition BY phasic_type ORDER BY ${str}_Pos DESC) ${str}poscp95,
        row_number() over (partition BY phasic_type ORDER BY ${str}_Neg DESC) ${str}negcp95,
        row_number() over (partition BY phasic_type ORDER BY ${str}_Zero DESC) ${str}zerocp95
        from
        <choose>
            <when test="conditionData.b == true">
                data_${str}
            </when>
            <otherwise>
                day_${str}
            </otherwise>
        </choose>
        t
        where lineid=#{conditionData.pointIndex} and timeid between
        #{conditionData.start} and #{conditionData.end} and phasic_type = 'T'
        <if test="conditionData.b == true">
            and QualityFlag = 0
        </if>
        <foreach collection="conditionData.exceptTime" index="index" item="item" open=" " separator=" " close="">
            and timeid not between #{item.start} and #{item.end}
        </foreach>) a
    </select>
</mapper>