<?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.GetFlickerMapper">
    <select id="getFlickerData" resultType="java.util.HashMap">
        <choose>
            <when test="conditionData.b == true">
                select phasic_type phaseType,max(pst) pst_MAX,min(pst) pst_MIN,round(avg(pst) ,2) pst,max(case when pstCP95
                =#{conditionData.pstCount} then pst else null end) pst_CP95 from ( select t.*
                ,row_number() over (partition by phasic_type order by pst desc) pstCP95
                from
                Data_Flicker
            </when>
            <otherwise>
                select phasic_type phaseType,max(pst_max) pst_MAX,min(pst_min) pst_MIN,round(avg(pst) ,2) pst,max(case when pstCP95
                =#{conditionData.pstCount} then pst else null end) pst_CP95 from ( select t.*
                ,row_number() over (partition by phasic_type order by pst desc) pstCP95
                from
                Day_Flicker
            </otherwise>
        </choose>
        t where lineid=#{conditionData.pointIndex} and phasic_type in ('A','B','C') and timeid between
        #{conditionData.start} and #{conditionData.end}
        <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
        group by phasic_type
    </select>

    <select id="getLFlickerData" resultType="java.util.HashMap">
        <choose>
            <when test="conditionData.b == true">
                select phasic_type phaseType,max(plt) plt_MAX,min(plt) plt_MIN,round(avg(plt) ,2) plt,max(case when pltCP95
                =#{conditionData.pltCount} then plt else null end) plt_CP95 from ( select t.*
                ,row_number() over (partition by phasic_type order by plt desc) pltCP95
                from
                Data_plt
            </when>
            <otherwise>
                select phasic_type phaseType,max(plt_max) plt_MAX,min(plt_min) plt_MIN,round(avg(plt) ,2) plt,max(case when pltCP95
                =#{conditionData.pltCount} then plt else null end) plt_CP95 from ( select t.*
                ,row_number() over (partition by phasic_type order by plt desc) pltCP95
                from
                Day_plt
            </otherwise>
        </choose>
        t where lineid=#{conditionData.pointIndex} and phasic_type in ('A','B','C') and timeid between
        #{conditionData.start} and #{conditionData.end}
        <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
        group by phasic_type
    </select>
</mapper>