<?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.QEnvgasWqMonthMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.cecep.talroad.domain.effective.QEnvgasWqMonth">
        <id column="id" property="id" />
        <result column="data_time" property="dataTime" />
        <result column="month_time" property="monthTime" />
        <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="fk_pollutant_id" property="fkPollutantId" />
        <result column="max_ipmi" property="maxIpmi" />
        <result column="fk_aqilevel_id" property="fkAqilevelId" />
        <result column="so2" property="so2" />
        <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" property="no2" />
        <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" property="pm10" />
        <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" property="pm25" />
        <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" property="co" />
        <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" property="o3" />
        <result column="o3_is_over" property="o3IsOver" />
        <result column="o3_max_value" property="o3MaxValue" />
        <result column="o3_min_value" property="o3MinValue" />
        <result column="o3_8h" property="o38h" />
        <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" property="iaqiO3" />
        <result column="ipmi_o3" property="ipmiO3" />
        <result column="iaqi_o3_8h" property="iaqiO38h" />
        <result column="ipmi_o3_8h" property="ipmiO38h" />
        <result column="co_95_percent" property="co95Percent" />
        <result column="o38h_90_percent" property="o38h90Percent" />
        <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="windd_max_value" property="winddMaxValue" />
        <result column="windd_min_value" property="winddMinValue" />
        <result column="pres" property="pres" />
        <result column="pres_max_value" property="presMaxValue" />
        <result column="pres_min_value" property="presMinValue" />
        <result column="cal_type" property="calType" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, data_time, month_time, region_code, tsp, nox, tvoc, wind_level, wind_direction, pmi, aqi, fk_pollutant_id, max_ipmi, fk_aqilevel_id, so2, so2_is_over, so2_max_value, so2_min_value, iaqi_so2, ipmi_so2, no2, no2_is_over, no2_max_value, no2_min_value, iaqi_no2, ipmi_no2, pm10, pm10_is_over, pm10_max_value, pm10_min_value, iaqi_pm10, ipmi_pm10, pm25, pm25_is_over, pm25_max_value, pm25_min_value, iaqi_pm25, ipmi_pm25, co, co_is_over, co_max_value, co_min_value, iaqi_co, ipmi_co, o3, o3_is_over, o3_max_value, o3_min_value, o3_8h, o3_8h_is_over, o3_8h_max_value, o3_8h_min_value, iaqi_o3, ipmi_o3, iaqi_o3_8h, ipmi_o3_8h, co_95_percent, o38h_90_percent, temp, temp_max_value, temp_min_value, humi, humi_max_value, humi_min_value, winds, winds_max_value, winds_min_value, windd, windd_max_value, windd_min_value, pres, pres_max_value, pres_min_value, cal_type, create_time, update_time
    </sql>
    <select id="selectByAverageData" resultType="java.util.Map"
            parameterType="cn.cecep.talroad.domain.dto.SequenceDto">
        SELECT
        month_time monitorTime,
        r.region_name stationRegionName,
        r.region_code stationRegionCode,
        aqi,
        pm25,
        pm10,
        so2,
        no2,
        co,
        o3,
        o3_8h
        FROM
        p_code_region r
        LEFT JOIN q_envgas_wq_month q ON r.region_code = q.region_code
        <if test="startTime != null and startTime != ''">
            AND month_time &gt;=#{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            AND month_time &lt;= #{endTime}
        </if>

        WHERE
        1 =1
        <if test="regionCodeList != null ">
            <foreach collection="regionCodeList" item="name" open="and  r.region_code in (" separator="," close=")">
                #{name}
            </foreach>
        </if>



        <if test="citySort == 1 or citySort == '1'">
            ORDER BY latitude asc
        </if>
        <if test="citySort == 2 or citySort == '2'">
            ORDER BY latitude desc
        </if>
        <if test="citySort == 3 or citySort == '3'">
            ORDER BY longitude desc
        </if>
        <if test="citySort == 4 or citySort == '4'">
            ORDER BY longitude asc
        </if>
        <if test="citySort == 5 or citySort == '5'">
            ORDER BY longitude desc, latitude desc
        </if>
        <if test="citySort == 6 or citySort == '6'">
            ORDER BY longitude asc, latitude asc
        </if>
        <if test="citySort == 7 or citySort == '7'">
            ORDER BY longitude desc, latitude asc
        </if>
        <if test="citySort == 8 or citySort == '8'">
            ORDER BY longitude asc, latitude desc
        </if>
    </select>

    <select id="queryAreaMonthData" resultType="cn.cecep.talroad.vo.effective.AirQualityData">
        SELECT
        ROUND( a.aqi, 0 ) aqi,
        a.month_time monitorTime,
        TO_CHAR(a.data_time,'MM月') dateAlias,
        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,
        a.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_month 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.month_time = #{datetime}
            </if>
            <if test="startTime != null and startTime != ''">
                AND l.month_time &gt;= #{startTime}
            </if>
            <if test="endTime != null and endTime !=''">
                AND l.month_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.month_time,d.region_code DESC
    </select>

    <!--
            <if test="startTime != null and startTime != ''">
                AND DATE_FORMAT(data_time,'%Y-%m-%d') &gt;=  DATE_FORMAT(#{startTime},'%Y-%m-%d')
            </if>
            <if test="endTime != null and endTime != ''">
                AND DATE_FORMAT(data_time,'%Y-%m-%d') &lt;=  DATE_FORMAT(#{endTime},'%Y-%m-%d')
            </if>
     -->
    <select id="selectByInterpolationData" resultType="java.util.Map"
            parameterType="cn.cecep.talroad.domain.dto.SequenceDto">

        SELECT
        p.region_name regionName,
        p.longitude,
        p.latitude,
        TO_CHAR(data_time,'YYYY-MM') monitorTime,
        p.region_code regionCode,
        COALESCE(aqi,0) aqi,
        COALESCE(pm25,0) pm25,
        COALESCE(pm10,0) pm10,
        COALESCE(so2,0) so2,
        COALESCE(no2,0) no2,
        COALESCE(co,0) co,
        COALESCE(o3,0) o3,
        COALESCE(o3_8h,0) o3_8h,
        COALESCE(windd,0) windd,
        COALESCE(pres,0) pres,
        COALESCE(humi,0) humi,
        COALESCE(temp,0) temp ,
        COALESCE(rain,0) rain,
        COALESCE(wind_level,0)  windLevel
        FROM
        p_code_region p
        LEFT JOIN (
        select  * from  q_envgas_wq_month where 1=1
        <if test="startTime != null and startTime != ''">
            AND TO_CHAR(data_time,'YYYY-MM') &gt;=  TO_CHAR(#{startTime},'YYYY-MM')
        </if>
        <if test="endTime != null and endTime !=''">
            AND TO_CHAR(data_time,'YYYY-MM') &lt;= TO_CHAR(#{endTime} ,'YYYY-MM')
        </if>
        <if test="monitorTime != null and monitorTime !=''">
            AND TO_CHAR(data_time,'YYYY-MM') = TO_CHAR(#{monitorTime} ,'YYYY-MM'
        </if>

        ) q ON q.region_code = p.region_code

        WHERE
        1 = 1
        <if test="regionCodeList != null and regionCodeList.size > 0">
            AND p.region_code IN  (
            <foreach collection="regionCodeList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>
    </select>
    <select id="selectByAerData" resultType="Map">
        select
             count(*) counts,
			fk_aqilevel_id,
            TO_CHAR(data_time,'YYYY-MM') dataTime
        from
            q_envgas_wq_day
        where
            1=1
            and region_code = #{regionCode}
             and  TO_CHAR(data_time,'YYYY-MM') BETWEEN #{startTime} AND #{endTime}
						and fk_aqilevel_id is not null
						AND fk_aqilevel_id > 0
            GROUP BY fk_aqilevel_id,TO_CHAR(data_time,'YYYY-MM')
	        order by  data_time asc
    </select>
    <select id="getCount" resultType="java.util.Map" parameterType="cn.cecep.talroad.domain.dto.SequenceDto">
    SELECT
        COUNT(*) counts,
        TO_CHAR(data_time, 'YYYY-MM') dataTime
    FROM
        q_envgas_wq_day
    WHERE
        1 = 1
        AND AQI IS NOT NULL
        AND fk_aqilevel_id > 0
        AND region_code = #{regionCode}
        AND TO_CHAR(data_time, 'YYYY-MM') BETWEEN #{startTime} AND #{endTime}
        GROUP BY TO_CHAR(data_time, 'YYYY-MM')
	    order by  data_time
    </select>

    <select id="selectByInterpolationDataList" resultType="java.util.Map"
            parameterType="cn.cecep.talroad.domain.dto.SequenceDto">
        SELECT
        p.region_name regionName,
        p.longitude,
        p.latitude,
        q.month_time monitorTime,
        p.region_code regionCode,
        ROUND(q.aqi,0)  aqi,
        ROUND(q.pm25,0) pm25,
        ROUND(q.pm10,0) pm10,
        ROUND(q.so2,0)  so2,
        ROUND(q.no2,0)  no2,
        ROUND(q.co,1)    co,
        ROUND(q.o3,0)    o3,
        ROUND(q.o3_8h,0)  o38h,
        q.windd  windd,
        q.pres  pres,
        q.humi  humi,
        q.temp  temp,
        q.rain  rain,
        q.wind_level windLevel
        FROM
        p_code_region p
        LEFT JOIN (
        select  * from  q_envgas_wq_month where 1=1
        <if test="startTime != null and startTime != ''">
            AND TO_CHAR(data_time, 'YYYY-MM') >= TO_CHAR(#{startTime}, 'YYYY-MM')
        </if>
        <if test="endTime != null and endTime !=''">
            AND TO_CHAR(data_time, 'YYYY-MM') &lt;= TO_CHAR(#{endTime}, 'YYYY-MM')
        </if>
        <if test="monitorTime != null and monitorTime !=''">
            AND month_time = #{monitorTime}
        </if>
        ) q ON q.region_code = p.region_code
        WHERE
        1 = 1
        <if test="regionCodeList != null and regionCodeList.size > 0">
            AND p.region_code IN  (
            <foreach collection="regionCodeList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>
    </select>

    <select id="queryAreaMonthDataNew" resultType="cn.cecep.talroad.vo.effective.AirQualityData">
        SELECT
            ROUND( a.aqi, 0 ) aqi,
            a.month_time monitorTime,
<!--            DATE_FORMAT(a.data_time,'%c月') dateAlias,-->
            to_char(a.data_time, 'fMM月') AS dateAlias,
            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,
            a.region_code as regionCode,
            d.region_lever as regionLevel
        FROM
        q_envgas_wq_month 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.month_time = #{datetime}
            </if>
            <if test="startTime != null and startTime != ''">
                AND a.month_time &gt;= #{startTime}
            </if>
            <if test="endTime != null and endTime !=''">
                AND a.month_time &lt;= #{endTime}
            </if>
        </where>
        ORDER BY a.month_time DESC
    </select>
    <select id="selectAreaYearData" resultType="cn.cecep.talroad.vo.effective.MonthDataVo">
        SELECT
            ROUND( aqi, 0 ) aqi,
            ROUND( pm25, 0 ) pm25,
            ROUND( pm10, 0 ) pm10,
            ROUND( so2, 0 ) so2,
            ROUND( no2, 0 ) no2,
            ROUND( co, 1 ) co,
            ROUND( o3_8h, 0 ) o38h,
            month_time monthTime,
            cast(EXTRACT(MONTH FROM data_time) as int) AS monthNum
        FROM
            q_envgas_wq_month
        WHERE
            region_code = #{regionCode}

            AND EXTRACT(YEAR FROM data_time) = #{year}
        ORDER BY
            month_time ASC
    </select>
    <select id="findStandardMonitoringLastMonth" 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 TO_CHAR(data_time, 'YYYY-MM-DD') >= TO_CHAR(NOW(), 'YYYY-MM-01')
            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 TO_CHAR(data_time, 'YYYY-MM-DD') >= TO_CHAR(CURRENT_DATE - INTERVAL '1 YEAR', 'YYYY-MM-01')
            AND TO_CHAR(data_time, 'YYYY-MM-DD') &lt;=  TO_CHAR(CURRENT_DATE - INTERVAL '1 YEAR' - INTERVAL '1 DAY', 'YYYY-MM-DD')
        </if>
    </select>
    <select id="findStandardDivisorMonth" 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  TO_CHAR(data_time, 'YYYY-MM-DD') &gt;= TO_CHAR(NOW(), 'YYYY-MM-01')
            AND TO_CHAR(data_time, 'YYYY-MM-DD') &lt;= TO_CHAR(NOW(), 'YYYY-MM-DD')
        </if>
        <if test="type == 2 or type =='2'">
            AND TO_CHAR(data_time, 'YYYY-MM-DD') >= TO_CHAR(CURRENT_DATE - INTERVAL '1 YEAR', 'YYYY-MM-01')
            AND TO_CHAR(data_time, 'YYYY-MM-DD') &lt;=  TO_CHAR(CURRENT_DATE - INTERVAL '1 YEAR' - INTERVAL '1 DAY', 'YYYY-MM-DD')
        </if>
    </select>
    <select id="selectAnalysisMonthData" resultType="cn.cecep.talroad.vo.effective.AirQualityData">
        SELECT
        month_time monitorTime,
        region_code,
        aqi,
        no2,
        pm10,
        co,
        so2,
        pm25,
        o3_8h,
        temp,
        humi,
        winds,
        wind_level windLevel,
        windd
        FROM
        `q_envgas_wq_month`
        WHERE
        region_code =  #{regionCode}
        <if test="startTime != null and startTime != ''">
            AND month_time &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime !=''">
            AND month_time &lt;= #{endTime}
        </if>
    </select>
    <!--
            FLOOR(rand()*100)  aqi,
            FLOOR(rand()*100)  pm25,
            FLOOR(rand()*100)  pm10,
            FLOOR(rand()*100)  so2,
            FLOOR(rand()*100)  no2,
            FLOOR(rand()*100)  co,
            FLOOR(rand()*100)  o3,
            FLOOR(rand()*100) o38h,
            FLOOR(rand()*100) windd,
            FLOOR(rand()*100)  pres,
            FLOOR(rand()*100)  humi,
            FLOOR(rand()*100)  temp ,
            FLOOR(rand()*100)  rain,
            FLOOR(rand()*100)  windLevel

    -->

</mapper>
