<?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="cn.cecep.talroad.mapper.effective.QEnvgasWqYearMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.cecep.talroad.domain.effective.QEnvgasWqYear">
        <id column="id" property="id" />
        <result column="data_time" property="dataTime" />
        <result column="year_time" property="yearTime" />
        <result column="region_code" property="regionCode" />
        <result column="tsp" property="tsp" />
        <result column="nox" property="nox" />
        <result column="tvoc" property="tvoc" />
        <result column="wind_level" property="windLevel" />
        <result column="wind_direction" property="windDirection" />
        <result column="pmi" property="pmi" />
        <result column="aqi" property="aqi" />
        <result column="so2" property="so2" />
        <result column="no2" property="no2" />
        <result column="pm25" property="pm25" />
        <result column="pm10" property="pm10" />
        <result column="co" property="co" />
        <result column="o3" property="o3" />
        <result column="o3_8h" property="o38h" />
        <result column="co_95_percent" property="co95Percent" />
        <result column="o38h_90_percent" property="o38h90Percent" />
        <result column="fk_pollutant_id" property="fkPollutantId" />
        <result column="max_ipmi" property="maxIpmi" />
        <result column="fk_aqilevel_id" property="fkAqilevelId" />
        <result column="so2_is_over" property="so2IsOver" />
        <result column="so2_max_value" property="so2MaxValue" />
        <result column="so2_min_value" property="so2MinValue" />
        <result column="iaqi_so2" property="iaqiSo2" />
        <result column="ipmi_so2" property="ipmiSo2" />
        <result column="no2_is_over" property="no2IsOver" />
        <result column="no2_max_value" property="no2MaxValue" />
        <result column="no2_min_value" property="no2MinValue" />
        <result column="iaqi_no2" property="iaqiNo2" />
        <result column="ipmi_no2" property="ipmiNo2" />
        <result column="pm10_is_over" property="pm10IsOver" />
        <result column="pm10_max_value" property="pm10MaxValue" />
        <result column="pm10_min_value" property="pm10MinValue" />
        <result column="iaqi_pm10" property="iaqiPm10" />
        <result column="ipmi_pm10" property="ipmiPm10" />
        <result column="pm25_is_over" property="pm25IsOver" />
        <result column="pm25_max_value" property="pm25MaxValue" />
        <result column="pm25_min_value" property="pm25MinValue" />
        <result column="iaqi_pm25" property="iaqiPm25" />
        <result column="ipmi_pm25" property="ipmiPm25" />
        <result column="co_is_over" property="coIsOver" />
        <result column="co_max_value" property="coMaxValue" />
        <result column="co_min_value" property="coMinValue" />
        <result column="iaqi_co" property="iaqiCo" />
        <result column="ipmi_co" property="ipmiCo" />
        <result column="o3_is_over" property="o3IsOver" />
        <result column="o3_max_value" property="o3MaxValue" />
        <result column="o3_min_value" property="o3MinValue" />
        <result column="iaqi_o3" property="iaqiO3" />
        <result column="ipmi_o3" property="ipmiO3" />
        <result column="o3_8h_is_over" property="o38hIsOver" />
        <result column="o3_8h_max_value" property="o38hMaxValue" />
        <result column="o3_8h_min_value" property="o38hMinValue" />
        <result column="iaqi_o3_8h" property="iaqiO38h" />
        <result column="ipmi_o3_8h" property="ipmiO38h" />
        <result column="temp" property="temp" />
        <result column="temp_max_value" property="tempMaxValue" />
        <result column="temp_min_value" property="tempMinValue" />
        <result column="humi" property="humi" />
        <result column="humi_max_value" property="humiMaxValue" />
        <result column="humi_min_value" property="humiMinValue" />
        <result column="winds" property="winds" />
        <result column="winds_max_value" property="windsMaxValue" />
        <result column="winds_min_value" property="windsMinValue" />
        <result column="windd" property="windd" />
        <result column="pres" property="pres" />
        <result column="pres_max_value" property="presMaxValue" />
        <result column="pres_min_value" property="presMinValue" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, data_time, year_time, region_code, tsp, nox, tvoc, wind_level,
        wind_direction, pmi, aqi, so2, no2, pm25, pm10, co, o3, o3_8h, co_95_percent,
        o38h_90_percent, fk_pollutant_id, max_ipmi, fk_aqilevel_id, so2_is_over,
        so2_max_value, so2_min_value, iaqi_so2, ipmi_so2, no2_is_over, no2_max_value,
        no2_min_value, iaqi_no2, ipmi_no2, pm10_is_over, pm10_max_value, pm10_min_value,
        iaqi_pm10, ipmi_pm10, pm25_is_over, pm25_max_value, pm25_min_value, iaqi_pm25,
        ipmi_pm25, co_is_over, co_max_value, co_min_value, iaqi_co, ipmi_co, o3_is_over,
        o3_max_value, o3_min_value, iaqi_o3, ipmi_o3, o3_8h_is_over, o3_8h_max_value,
        o3_8h_min_value, iaqi_o3_8h, ipmi_o3_8h, temp, temp_max_value, temp_min_value, humi,
        humi_max_value, humi_min_value, winds, winds_max_value, winds_min_value, windd,
        pres, pres_max_value, pres_min_value, create_time, update_time
    </sql>
    <select id="queryAreaYearData" resultType="cn.cecep.talroad.vo.effective.AirQualityData">
        SELECT
        ROUND( a.aqi, 0 ) aqi,
        a.year_time monitorTime,
        ROUND( a.so2, 0 ) so2,
        ROUND( a.no2, 0 ) no2,
        ROUND( a.co, 1 ) co,
        ROUND( a.o3, 0 ) o3,
        ROUND( a.o3_8h, 0 ) o38h,
        ROUND( a.pm25, 0 ) pm25,
        ROUND( a.pm10, 0 ) pm10,
        ROUND( a.winds, 0 ) winds,
        ROUND( a.pres, 0 ) pres,
        ROUND( a.humi, 0 ) humi,
        ROUND( a.temp, 0 ) temp,
        ROUND( a.windd, 0 ) windd,
        ROUND( a.tvoc, 0 ) tvoc,
        ROUND( a.tsp, 0 ) tsp,
        ROUND( a.pmi, 2 ) pmi,
        ROUND( a.co_95_percent,0) co95Percent,
        ROUND( a.o38h_90_percent,0) o38h90Percnet,
        ROUND( a.iaqi_pm25, 0 ) iaqiPm25,
        ROUND( a.iaqi_pm10, 0 ) iaqiPm10,
        ROUND( a.iaqi_so2, 0 ) iaqiSo2,
        ROUND( a.iaqi_no2, 0 ) iaqiNo2,
        ROUND( a.iaqi_co, 0 ) iaqiCO,
        ROUND( a.iaqi_o3, 0 ) iaqiO3,
        ROUND( a.iaqi_o3_8h, 0 ) iaqiO38h,
        ROUND( a.ipmi_pm25, 2 ) ipmiPm25,
        ROUND( a.ipmi_pm10, 2 ) ipmiPm10,
        ROUND( a.ipmi_so2, 2) ipmiSo2,
        ROUND( a.ipmi_no2, 2 ) ipmiNo2,
        ROUND( a.ipmi_co, 2 ) ipmiCo,
        ROUND( a.ipmi_o3, 2 ) ipmiO3,
        ROUND( a.ipmi_o3_8h, 2) ipmiO38h,
        a.fk_aqilevel_id fkAqiLevelId,
        a.fk_pollutant_id fkPollutantId,
        a.aqi_type fkAqiLevelName,
        a.aqi_color fkAqiLevelColor,
        a.pollutant_name fkPollutantName,
        d.region_name as regionName,
        d.region_code as regionCode,
        d.region_lever as regionLevel
        FROM
        p_code_region d
        LEFT JOIN (
        SELECT
        l.*,
        b.aqi_type,
        b.aqi_color,
        c.pollutant_name
        FROM
        q_envgas_wq_year l
        LEFT JOIN s_envgas_aqilevel b ON l.fk_aqilevel_id = b.pk_id
        LEFT JOIN s_envgas_pollutant c ON l.fk_pollutant_id = c.pk_id
        <where>
            <if test="datetime!=null and datetime !=''">
                AND l.year_time = #{datetime}
            </if>
            <if test="startTime != null and startTime != ''">
                AND l.year_time &gt;= #{startTime}
            </if>
            <if test="endTime != null and endTime !=''">
                AND l.year_time &lt;= #{endTime}
            </if>
        </where>
        ) a ON d.region_code = a.region_code
        <where>
            <if test="regionCode != null and regionCode != ''">
                AND d.region_code = #{regionCode}
            </if>
            <if test="regionCodeList != null and regionCodeList.size > 0">
                AND d.region_code IN
                (
                <foreach collection="regionCodeList" item="item" separator=",">
                    #{item}
                </foreach>
                )
            </if>
        </where>
        ORDER BY a.year_time,d.region_code DESC
    </select>

    <select id="queryAreaYearDataNew" resultType="cn.cecep.talroad.vo.effective.AirQualityData">
        SELECT
        ROUND( a.aqi, 0 ) aqi,
            a.year_time monitorTime,
            ROUND( a.so2, 0 ) so2,
            ROUND( a.no2, 0 ) no2,
            ROUND( a.co, 1 ) co,
            ROUND( a.o3, 0 ) o3,
            ROUND( a.o3_8h, 0 ) o38h,
            ROUND( a.pm25, 0 ) pm25,
            ROUND( a.pm10, 0 ) pm10,
            ROUND( a.winds, 0 ) winds,
            ROUND( a.pres, 0 ) pres,
            ROUND( a.humi, 0 ) humi,
            ROUND( a.temp, 0 ) temp,
            ROUND( a.windd, 0 ) windd,
            ROUND( a.tvoc, 0 ) tvoc,
            ROUND( a.tsp, 0 ) tsp,
            ROUND( a.pmi, 2 ) pmi,
            ROUND( a.co_95_percent,0) co95Percent,
            ROUND( a.o38h_90_percent,0) o38h90Percnet,
            ROUND( a.iaqi_pm25, 0 ) iaqiPm25,
            ROUND( a.iaqi_pm10, 0 ) iaqiPm10,
            ROUND( a.iaqi_so2, 0 ) iaqiSo2,
            ROUND( a.iaqi_no2, 0 ) iaqiNo2,
            ROUND( a.iaqi_co, 0 ) iaqiCO,
            ROUND( a.iaqi_o3, 0 ) iaqiO3,
            ROUND( a.iaqi_o3_8h, 0 ) iaqiO38h,
            ROUND( a.ipmi_pm25, 2 ) ipmiPm25,
            ROUND( a.ipmi_pm10, 2 ) ipmiPm10,
            ROUND( a.ipmi_so2, 2 ) ipmiSo2,
            ROUND( a.ipmi_no2, 2 ) ipmiNo2,
            ROUND( a.ipmi_co, 2 ) ipmiCo,
            ROUND( a.ipmi_o3, 2 ) ipmiO3,
            ROUND( a.ipmi_o3_8h, 2 ) ipmiO38h,
            a.fk_aqilevel_id fkAqiLevelId,
            a.fk_pollutant_id fkPollutantId,
            b.aqi_type fkAqiLevelName,
            b.aqi_color fkAqiLevelColor,
            c.pollutant_name fkPollutantName,
            d.region_name as regionName,
            d.region_code as regionCode,
            d.region_lever as regionLevel
        FROM
        q_envgas_wq_year a
        LEFT JOIN s_envgas_aqilevel b ON a.fk_aqilevel_id = b.pk_id
        LEFT JOIN s_envgas_pollutant c ON a.fk_pollutant_id = c.pk_id
        LEFT JOIN p_code_region d ON d.region_code = a.region_code
        <where>
            <if test="regionCode != null and regionCode != ''">
                AND a.region_code = #{regionCode}
            </if>
            <if test="regionCodeList != null and regionCodeList.size > 0">
                AND a.region_code IN
                (
                <foreach collection="regionCodeList" item="item" separator=",">
                    #{item}
                </foreach>
                )
            </if>
            <if test="datetime!=null and datetime !=''">
                AND a.year_time = #{datetime}
            </if>
            <if test="startTime != null and startTime != ''">
                AND a.year_time &gt;= #{startTime}
            </if>
            <if test="endTime != null and endTime !=''">
                AND a.year_time &lt;= #{endTime}
            </if>
        </where>
        ORDER BY a.year_time DESC
    </select>

    <select id="findReachabilityAnalysis" resultType="cn.cecep.talroad.vo.effective.QEnvgasAreaTargetVo">
        SELECT
            poll.name pollutantName,poll.field field,
            t.time_type,
            t.time,
            t.target_value,
            t.actual_value,
            t.fk_factor_id,
            t.region_code
        FROM
            p_code_index_pollutant poll
         LEFT JOIN
          ( select  *  from q_envgas_area_target tt
            WHERE 1=1 and tt.region_code = #{userRegionCode}
            AND tt.delete_flag = 0
            AND tt.time_type = #{timeType}
            and tt.time=#{monitorTime}
            and tt.fk_factor_id=#{factorCodeInt,jdbcType=INTEGER}
          ) t on poll.pk_id=t.fk_factor_id
        WHERE
           1=1  and  poll.pk_id=#{factorCodeInt,jdbcType=INTEGER}
    </select>
    <select id="findPeriodLastYear" resultType="java.math.BigDecimal">
        SELECT sum(${field}) AS counts
        FROM q_envgas_wq_day
        WHERE region_code = #{userRegionCode}
        AND DATE_TRUNC('day', data_time) >= DATE_TRUNC('day', #{startTime}::timestamp)
        AND DATE_TRUNC('day', data_time) &lt;= DATE_TRUNC('day', #{endTime}::timestamp)
    </select>

    <select id="findStandardMonitoringLastYear" resultType="java.util.Map">
        SELECT
            avg(aqi) aqi,
            avg( pm25 ) pm25,
            avg( pm10 ) pm10,
            avg( so2) so2,
            avg( no2) no2,
            avg( co ) co,
            avg( o3_8h ) o38h,
            SUM(CASE WHEN fk_aqilevel_id  &lt; 3 THEN 1 ELSE 0 END ) goodDays,
            SUM(CASE WHEN fk_aqilevel_id  &gt; 4 THEN 1 ELSE 0 END  ) heavyDays
        FROM
            q_envgas_wq_day
        WHERE
            region_code = #{userRegionCode}
        <if test="type == 1 or type=='1'">
           AND date_part('year', data_time) = date_part('year', now())
           AND TO_CHAR(data_time, 'YYYY-MM-DD') &lt;= TO_CHAR(NOW() - INTERVAL '1 day', 'YYYY-MM-DD')
        </if>
        <if test="type == 2 or type =='2'">
            AND DATE_PART('year', data_time) = DATE_PART('year', NOW() - INTERVAL '1 YEAR')
            AND TO_CHAR(data_time, 'YYYY-MM-DD') &lt;=TO_CHAR(DATE_TRUNC('day', NOW() - INTERVAL '1 YEAR' - INTERVAL '1 day'), 'YYYY-MM-DD')
        </if>
    </select>
    <select id="findPrevention" resultType="java.math.BigDecimal">
        SELECT
        AVG( actual_value )
        FROM
        q_envgas_area_target
        WHERE
        1 = 1
        AND region_code = #{userRegionCode}
        AND fk_factor_id = #{factorCode}
        AND time >=  CONCAT(EXTRACT(YEAR FROM NOW()), '-09')
        AND time &lt;= TO_CHAR(NOW(), 'YYYY-MM')
    </select>
    <select id="findAutumnWinter" resultType="java.util.Map">
        SELECT
        avg( aqi ) aqi,
        avg( pm25 ) pm25,
        avg( pm10 ) pm10,
        avg( so2 ) so2,
        avg( no2 ) no2,
        avg( co ) co,
        avg( o3_8h ) o38h
        FROM
        q_envgas_wq_day
        WHERE
        region_code = #{userRegionCode}
        <if test="timeType == 5 or timeType =='5'">
            AND TO_CHAR(data_time, 'YYYY-MM-DD') >= CONCAT(EXTRACT(YEAR FROM NOW()), '-10-01')
            AND TO_CHAR(data_time, 'YYYY-MM-DD') &lt;= CONCAT(EXTRACT(YEAR FROM NOW()), '-12-31')
        </if>
        <if test="timeType == 6 or timeType =='6'">
            AND TO_CHAR(data_time, 'YYYY-MM-DD') >= CONCAT(EXTRACT(YEAR FROM NOW()), '-10-01')
            AND TO_CHAR(data_time, 'YYYY-MM-DD') &lt;=  CONCAT(EXTRACT(YEAR FROM NOW()) + 1, '-03-31')
        </if>
    </select>
    <select id="findStandardDivisorLastYear" resultType="java.util.Map">
        SELECT
         co    co,
         o3_8h  o38h
        FROM
        q_envgas_wq_day
        WHERE
        region_code = #{userRegionCode}
      <!--  <if test="type == 1 or type =='1'">
           AND date_part('year', data_time) = date_part('year', now())
            and DATE_FORMAT(data_time,'%Y-%m-%d') &lt;= DATE_FORMAT(NOW(), '%Y-%m-%d' )
        </if>
        <if test="type == 2 or type =='2'">
            and year(data_time)= DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 1 YEAR ), '%Y' )
            and DATE_FORMAT(data_time,'%Y-%m-%d') &lt;= DATE_FORMAT(DATE_SUB( DATE_SUB( NOW(), INTERVAL 1 YEAR), INTERVAL 1 day) , '%Y-%m-%d' )
        </if>-->
        <if test="type == 1 or type == '1'">
            AND DATE_PART('year', data_time) = DATE_PART('year', NOW())
            AND TO_CHAR(data_time, 'YYYY-MM-DD')  &lt;= TO_CHAR(NOW(), 'YYYY-MM-DD')
        </if>
        <if test="type == 2 or type == '2'">
            AND DATE_TRUNC('year', data_time) = DATE_TRUNC('year', CURRENT_DATE - interval '1 year')
            AND TO_CHAR(data_time, 'YYYY-MM-DD') &lt;=TO_CHAR(CURRENT_DATE - INTERVAL '1 YEAR' - INTERVAL '1 DAY', 'YYYY-MM-DD')
        </if>
    </select>
</mapper>
