<?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.csharmonic.mapper.report.ReportMapper">
    <select id="getVirtualDataI" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        @rankSum := IF( @CI := phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_i_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' )
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN 1
        WHEN value_type = 'MAX' THEN 2
        WHEN value_type = 'MIN' THEN 3
        WHEN value_type = 'AVG' THEN 4
        ELSE 5
        END
        ),
        rms DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type;
    </select>
    <select id="getVirtualDataV" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        @rankSum := IF( @CI = phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_v_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' )
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN 1
        WHEN value_type = 'MAX' THEN 2
        WHEN value_type = 'MIN' THEN 3
        WHEN value_type = 'AVG' THEN 4
        ELSE 5
        END
        ),
        rms DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type
    </select>
    <select id="getTotalCP95Day" resultType="java.lang.Integer">
        SELECT
        count( rms ) total
        FROM
        r_stat_data_v_d
        <where>
            phasic_type IN ( 'A' )
            AND value_type = "CP95"
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
    </select>
    <select id="getTotalPltCP95Day" resultType="java.lang.Integer">
        SELECT
        count( plt ) total
        FROM
        r_stat_data_plt_d
        <where>
            phasic_type IN ( 'A' )
            AND value_type = "CP95"
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
    </select>
    <select id="getTotalPstCP95Day" resultType="java.lang.Integer">
        SELECT
        count( plt ) total
        FROM
        r_stat_data_flicker_d
        <where>
            phasic_type IN ( 'A' )
            AND value_type = "CP95"
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
    </select>
    <select id="getVVirtualData" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms_lvr as rms,
        @rankSum := IF( @CI = phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_v_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' )
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN 1
        WHEN value_type = 'MAX' THEN 2
        WHEN value_type = 'MIN' THEN 3
        WHEN value_type = 'AVG' THEN 4
        ELSE 5
        END
        ),
        rms_lvr DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type
    </select>
    <select id="getPowerP" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        p as rms,
        @rankSum :=
        IF
        ( @CI = phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_harmpower_p_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' ,'T')
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN
        1
        WHEN value_type = 'MAX' THEN
        2
        WHEN value_type = 'MIN' THEN
        3
        WHEN value_type = 'AVG' THEN
        4 ELSE 5
        END
        ),
        p DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type
    </select>
    <select id="getPF" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        pf as rms,
        @rankSum :=
        IF
        ( @CI = phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_harmpower_p_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' ,'T')
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN
        1
        WHEN value_type = 'MAX' THEN
        2
        WHEN value_type = 'MIN' THEN
        3
        WHEN value_type = 'AVG' THEN
        4 ELSE 5
        END
        ),
        pf DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type
    </select>
    <select id="getPowerQ" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        q as rms,
        @rankSum :=
        IF
        ( @CI = phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_harmpower_q_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' ,'T')
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN
        1
        WHEN value_type = 'MAX' THEN
        2
        WHEN value_type = 'MIN' THEN
        3
        WHEN value_type = 'AVG' THEN
        4 ELSE 5
        END
        ),
        q DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type
    </select>
    <select id="getPowerS" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        s as rms,
        @rankSum :=
        IF
        ( @CI = phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_harmpower_s_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' ,'T')
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN
        1
        WHEN value_type = 'MAX' THEN
        2
        WHEN value_type = 'MIN' THEN
        3
        WHEN value_type = 'AVG' THEN
        4 ELSE 5
        END
        ),
        s DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type
    </select>
    <select id="getFlickerData" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        plt as rms,
        @rankSum :=
        IF
        ( @CI = phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_flicker_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' )
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN
        1
        WHEN value_type = 'MAX' THEN
        2
        WHEN value_type = 'MIN' THEN
        3
        WHEN value_type = 'AVG' THEN
        4 ELSE 5
        END
        ),
        plt DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type
    </select>
    <select id="getLFlickerData" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        pst as rms,
        @rankSum :=
        IF
        ( @CI = phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_flicker_d
        <where>
            phasic_type IN ( 'A', 'B', 'C')
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN
        1
        WHEN value_type = 'MAX' THEN
        2
        WHEN value_type = 'MIN' THEN
        3
        WHEN value_type = 'AVG' THEN
        4 ELSE 5
        END
        ),
        pst DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type
    </select>
    <select id="getUVdeviationData" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        vu_dev as rms,
        @rankSum := IF( @CI := phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_v_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' )
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN 1
        WHEN value_type = 'MAX' THEN 2
        WHEN value_type = 'MIN' THEN 3
        WHEN value_type = 'AVG' THEN 4
        ELSE 5
        END
        ),
        vu_dev DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type;
    </select>
    <select id="getLVdeviationData" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        vl_dev as rms,
        @rankSum := IF( @CI := phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_v_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' )
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN 1
        WHEN value_type = 'MAX' THEN 2
        WHEN value_type = 'MIN' THEN 3
        WHEN value_type = 'AVG' THEN 4
        ELSE 5
        END
        ),
        vl_dev DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type;
    </select>
    <select id="getDistortionDataV" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
         rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        v_thd as rms,
        @rankSum := IF( @CI := phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_v_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' )
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN 1
        WHEN value_type = 'MAX' THEN 2
        WHEN value_type = 'MIN' THEN 3
        WHEN value_type = 'AVG' THEN 4
        ELSE 5
        END
        ),
        v_thd DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type;
    </select>
    <select id="getDistortionDataI" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
         rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        i_thd as rms,
        @rankSum := IF( @CI := phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_i_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' )
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN 1
        WHEN value_type = 'MAX' THEN 2
        WHEN value_type = 'MIN' THEN 3
        WHEN value_type = 'AVG' THEN 4
        ELSE 5
        END
        ),
        i_thd DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type;
    </select>
    <select id="getFrequencyData" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        freq as rms,
        @rankSum := IF( @CI := phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_v_d
        <where>
            phasic_type =  'T'
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN 1
        WHEN value_type = 'MAX' THEN 2
        WHEN value_type = 'MIN' THEN 3
        WHEN value_type = 'AVG' THEN 4
        ELSE 5
        END
        ),
        freq DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type;
    </select>
    <select id="getDEVFrequencyData" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        freq_dev as rms,
        @rankSum := IF( @CI := phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_v_d
        <where>
            phasic_type =  'T'
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN 1
        WHEN value_type = 'MAX' THEN 2
        WHEN value_type = 'MIN' THEN 3
        WHEN value_type = 'AVG' THEN 4
        ELSE 5
        END
        ),
        freq_dev DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type;
    </select>
    <select id="getINegData" resultType="com.njcn.csharmonic.pojo.vo.report.ReportValue">
        SELECT
        phasic_type AS phaseType,
        AVG( CASE WHEN value_type = 'AVG' THEN rms END ) AS meanValue,
        MIN( CASE WHEN value_type = 'MIN' THEN rms END ) AS minValue,
        MAX( CASE WHEN value_type = 'MAX' THEN rms END ) AS fmaxValue,
        MAX( CASE WHEN rankSum = #{param.count} THEN rms END ) AS cp95Value
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        rms,
        rankSum
        FROM
        (
        SELECT
        phasic_type,
        value_type,
        i_neg as rms,
        @rankSum := IF( @CI := phasic_type, @rankSum + 1, 1 ) AS rankSum,
        @CI := phasic_type
        FROM
        r_stat_data_i_d
        <where>
            phasic_type IN ( 'A', 'B', 'C' )
            and quality_flag = 0
            <if test="param.startTime != null and param.startTime != ''">
                and time >= #{param.startTime}
            </if>
            <if test="param.endTime != null and param.endTime != ''">
                and time &lt;= #{param.endTime}
            </if>
            <if test="param.lineId != null and param.lineId != ''">
                and line_id = #{param.lineId}
            </if>
        </where>
        ORDER BY
        phasic_type,
        (
        CASE
        WHEN value_type = 'CP95' THEN 1
        WHEN value_type = 'MAX' THEN 2
        WHEN value_type = 'MIN' THEN 3
        WHEN value_type = 'AVG' THEN 4
        ELSE 5
        END
        ),
        rms DESC
        ) AS t1
        ) a
        GROUP BY
        phasic_type;
    </select>

</mapper>
