<?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.SEnvGasMonRealMapper">
    <sql id="Base_Column_List">
        id,
        factory_id,
        outlet_id,
        data_type,
        monitoring_time,
        so2,
        so2_convert,
        so2_emission,
        nox,
        nox_convert,
        nox_emission,
        "no",
        no_convert,
        no_emission,
        no2,
        no2_convert,
        no2_emission,
        smoke_dust,
        smoke_dust_convert,
        smoke_dust_emission,
        o3,
        oxygen,
        smoke_dust_tem,
        smoke_dust_pressure,
        smoke_dust_flow_velocity,
        smoke_dust_humidity,
        pm,
        pm_convert,
        pm_emission
    </sql>
    <resultMap type="cn.cecep.talroad.domain.SEnvGasMonReal" id="SEnvGasMonRealResult">
        <result property="id"    column="id"    />
        <result property="factoryId"    column="factory_id"    />
        <result property="outletId"    column="outlet_id"    />
        <result property="dataType"    column="data_type"    />
        <result property="monitoringTime"    column="monitoring_time"    />
        <result property="so2"    column="so2"    />
        <result property="so2Convert"    column="so2_convert"    />
        <result property="so2Emission"    column="so2_emission"    />
        <result property="nox"    column="nox"    />
        <result property="noxConvert"    column="nox_convert"    />
        <result property="noxEmission"    column="nox_emission"    />
        <result property="no"    column="no"    />
        <result property="noConvert"    column="no_convert"    />
        <result property="noEmission"    column="no_emission"    />
        <result property="no2"    column="no2"    />
        <result property="no2Convert"    column="no2_convert"    />
        <result property="no2Emission"    column="no2_emission"    />
        <result property="smokeDust"    column="smoke_dust"    />
        <result property="smokeDustConvert"    column="smoke_dust_convert"    />
        <result property="smokeDustEmission"    column="smoke_dust_emission"    />
        <result property="pm"    column="pm"    />
        <result property="pmConvert"    column="pm_convert"    />
        <result property="pmEmission"    column="pm_emission"    />
        <result property="vocs"    column="vocs"    />
        <result property="vocsConvert"    column="vocs_convert"    />
        <result property="vocsEmission"    column="vocs_emission"    />
        <result property="o3"    column="o3"    />
        <result property="oxygen"    column="oxygen"    />
        <result property="smokeDustTem"    column="smoke_dust_tem"    />
        <result property="smokeDustPressure"    column="smoke_dust_pressure"    />
        <result property="smokeDustFlowVelocity"    column="smoke_dust_flow_velocity"    />
        <result property="smokeDustHumidity"    column="smoke_dust_humidity"    />
        <result property="so2Flag"    column="so2_flag"    />
        <result property="noxFlag"    column="nox_flag"    />
        <result property="smokeDustFlag"    column="smoke_dust_flag"    />
        <result property="dataFlag"    column="data_flag"    />
        <result property="vocsFlag"    column="vocs_flag"    />
        <result property="so2CorrectEmission" column="so2_correct_emission" />
        <result property="noxCorrectEmission" column="nox_correct_emission" />
        <result property="vocsCorrectEmission" column="vocs_correct_emission" />
        <result property="smokeDustCorrectEmission" column="smoke_dust_correct_emission" />
    </resultMap>


    <sql id="selectSEnvGasMonRealVo">
        select id, factory_id, outlet_id, data_type, monitoring_time, so2,
               so2_convert, nox, nox_convert, "no", no_convert, no2, no2_convert, smoke_dust,
               smoke_dust_convert, o3, oxygen, smoke_dust_tem, smoke_dust_pressure,
               smoke_dust_flow_velocity, smoke_dust_humidity, pm, pm_convert,
               vocs, vocs_convert,so2_flag,nox_flag,smoke_dust_flag,vocs_falg,data_flag,
               nox_correct_emission,so2_correct_emission,vocs_correct_emission,smoke_dust_correct_emission
        from s_env_gas_mon_real
    </sql>

    <select id="selectSEnvGasMonRealList" parameterType="cn.cecep.talroad.vo.query.SEnvGasMonRealQuery" resultType="cn.cecep.talroad.vo.SEnvGasMonRealVo">
        SELECT
            segmr.monitoring_time       monitoringTime,
            segmr.so2,
            segmr.so2_convert           so2Convert,
            segmr.so2_emission           so2Emission,
            segmr.nox,
            segmr.nox_convert           noxConvert,
            segmr.nox_emission           noxEmission,
            segmr.no,
            segmr.no_convert,
            segmr.no_emission,
            segmr.no2,
            segmr.no2_convert,
            segmr.no2_emission,
            segmr.o3,
            segmr.oxygen,
            segmr.oxygen_convert,
            segmr.oxygen_emission,
            segmr.smoke_dust,
            segmr.smoke_dust_convert,
            segmr.smoke_dust_emission,
            segmr.smoke_dust_tem            smokeDustTem,
            segmr.smoke_dust_pressure       smokeDustPressure,
            segmr.smoke_dust_flow_velocity  smokeDustFlowVelocity,
            segmr.smoke_dust_humidity       smokeDustHumidity,
            segmr.pm,
            segmr.pm_convert            pmConvert,
            segmr.pm_emission            pmEmission,
            segmr.vocs,
            segmr.vocs_convert          vocsConvert,
            segmr.vocs_emission          vocsEmission,
            polet.outlet_name              outletName,
            polet.outlet_code              outletCode,
            pf.factory_name             factoryName,
            po.monitoring_point_name    monitoringPointName,
            polet.mark,
            segmr.oxygen_emission,

            segmr.so2_flag,
            segmr.nox_flag,
            segmr.no_flag,
            segmr.no2_flag,
            segmr.smoke_dust_flag,
            segmr.pm_flag,
            segmr.vocs_flag,
            segmr.o3_flag,
            segmr.oxygen_flag,
            segmr.smoke_dust_tem_flag,
            segmr.smoke_dust_pressure_flag,
            segmr.smoke_dust_flow_velocity_flag,
            segmr.smoke_dust_humidity_flag,
            segmr.data_flag
        FROM s_env_gas_mon_real segmr
            LEFT JOIN pc_outlet po ON segmr.outlet_id = po."id"
            LEFT JOIN pc_outlet polet ON polet."id" = po."parent_outlet_id"
            LEFT JOIN pc_factory pf ON segmr.factory_id = pf."id"
        <where>
            po.pollutant_type = '4'
            <if test="sEnvGasMonReal.factoryId != null  and sEnvGasMonReal.factoryId != ''"> AND segmr.factory_id = #{sEnvGasMonReal.factoryId}</if>
            <if test="sEnvGasMonReal.outletId != null  and sEnvGasMonReal.outletId != ''"> AND outlet_id = #{sEnvGasMonReal.outletId}</if>
            <if test="sEnvGasMonReal.dataType != null  and sEnvGasMonReal.dataType != ''"> AND data_type = #{sEnvGasMonReal.dataType}</if>
            <if test="sEnvGasMonReal.monitoringStartTime != null  and sEnvGasMonReal.monitoringStartTime != '' and sEnvGasMonReal.monitoringEndTime != null  and sEnvGasMonReal.monitoringEndTime != ''">
                and monitoring_time BETWEEN #{sEnvGasMonReal.monitoringStartTime} AND #{sEnvGasMonReal.monitoringEndTime}
            </if>

            <if test="sEnvGasMonReal.monitoringPointName != null  and sEnvGasMonReal.monitoringPointName != ''"> and (po.monitoring_point_name = #{sEnvGasMonReal.monitoringPointName} or polet.monitoring_point_name = #{sEnvGasMonReal.monitoringPointName})</if>
            <if test="sEnvGasMonReal.mark != null  and sEnvGasMonReal.mark != ''"> and mark = #{sEnvGasMonReal.mark}</if>
            <if test="sEnvGasMonReal.outletIdArr != null and sEnvGasMonReal.outletIdArr.length != 0">
                <foreach collection="sEnvGasMonReal.outletIdArr" item="item" separator="," open="and outlet_id in (" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        ${ew.customSqlSegment}
    </select>

    <select id="selectSEnvGasMonRealListByYear" resultType="cn.cecep.talroad.vo.SEnvGasMonRealVo">
        SELECT
            factory_id                                      factoryId,
            SUM ( CAST( so2 AS FLOAT ) )                    so2,
            SUM ( CAST( so2_convert AS FLOAT ) )            so2Convert,
            SUM ( CAST( so2_emission AS FLOAT ) )            so2Emission,
            SUM ( CAST( nox AS FLOAT ) )                    nox,
            SUM ( CAST( nox_convert AS FLOAT ) )            noxConvert,
            SUM ( CAST( nox_emission AS FLOAT ) )            noxEmission,
            SUM ( CAST( o3 AS FLOAT ) )                     o3,
            SUM ( CAST( oxygen AS FLOAT ) )                 oxygen,
            SUM ( CAST( pm AS FLOAT ) )                     pm,
            SUM ( CAST( pm_convert AS FLOAT ) )             pmConvert,
            SUM ( CAST( pm_emission AS FLOAT ) )             pmEmission,
            SUM ( CAST( vocs AS FLOAT ) )                   vocs,
            SUM ( CAST( vocs_convert AS FLOAT ) )           vocsConvert,
            SUM ( CAST( vocs_emission AS FLOAT ) )           vocsEmission
        FROM s_env_gas_mon_real
        WHERE data_type = 'D' AND SUBSTRING(monitoring_time FROM 0 FOR 5) = #{year}
        GROUP BY factory_id
    </select>

    <select id="selectSEnvGasMonRealListGroupByYear" resultType="cn.cecep.talroad.vo.SEnvGasMonRealVo">
        SELECT
            series.generate_date                                AS monitoringTime,
            ROUND ( COALESCE( tmp.so2_emissions, 0)::numeric, 2)                        AS so2Emission,
            ROUND ( COALESCE( tmp.nox_emissions, 0)::numeric, 2)                        AS noxEmission,
            ROUND ( COALESCE( tmp.pm_emissions, 0)::numeric, 2)                         AS pmEmission,
            ROUND ( COALESCE( tmp.vocs_emissions, 0)::numeric, 2)                       AS vocsEmission
        FROM
        ( SELECT to_char(generate_series(#{monitoringStartTime}::date, #{monitoringEndTime}::date, '1 month'), 'yyyy-mm') generate_date ) series
        LEFT JOIN (
            SELECT
                to_char( monitor_time, 'YYYY-MM' ) monitoringTime,
                SUM ( CASE pollutant_code WHEN 'A21026' THEN CAST ( pollutant_emissions AS DECIMAL ) ELSE 0 END )     so2_emissions,
                SUM ( CASE pollutant_code WHEN 'A21002' THEN CAST ( pollutant_emissions AS DECIMAL ) ELSE 0 END )     nox_emissions,
                SUM ( CASE pollutant_code WHEN 'A99911' THEN CAST ( pollutant_emissions AS DECIMAL ) ELSE 0 END )     pm_emissions,
                SUM ( CASE pollutant_code WHEN 'A99901' THEN CAST ( pollutant_emissions AS DECIMAL ) ELSE 0 END )     vocs_emissions
            FROM pc_factory pf
                LEFT JOIN s_ra_online_emissions_gas sroeg ON sroeg.factory_id = pf.ID
            <where>
                <if test="industry != null and industry != ''">
                    AND pf.industry_category = #{industry}
                </if>
                <if test="regionCodes != null and regionCodes.size() != 0">
                    and pf.country_code IN
                    <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                        #{item,jdbcType=VARCHAR}
                    </foreach>
                </if>
                AND monitor_time BETWEEN #{monitoringStartTime}::TIMESTAMP AND #{monitoringEndTime}::TIMESTAMP
            </where>
            GROUP BY monitoringTime
        ) tmp ON series.generate_date = tmp.monitoringTime
        ORDER BY monitoringTime
    </select>

    <select id="selectAvgDataForHour" resultType="cn.cecep.talroad.domain.SEnvGasMonReal">
        --查询小时内分钟数据的平均浓度
        select
        round(avg(cast(t1.so2_convert as decimal)),2) as so2_convert,
        round(avg(cast(t1.nox_convert as decimal)),2) as nox_convert,
        round(avg(cast(t1.no_convert as decimal)),2) as no_convert,
        round(avg(cast(t1.no2_convert as decimal)),2) as no2_convert,
        round(avg(cast(t1.smoke_dust_convert as decimal)),2) as smoke_dust_convert,
        round( AVG ( CAST ( t1.pm_convert AS DECIMAL ) ), 2 ) AS pm_convert,
        t1.outlet_id,t1.factory_id
        from (select
        case when (so2_convert is null or so2_convert = '') then '0' else so2_convert end as so2_convert,
        case when (nox_convert is null or nox_convert = '') then '0' else nox_convert end as nox_convert,
        case when (no_convert is null or no_convert = '') then '0' else no_convert end as no_convert,
        case when (no2_convert is null or no2_convert = '') then '0' else no2_convert end as no2_convert,
        case when (smoke_dust_convert is null or smoke_dust_convert = '') then '0' else smoke_dust_convert end as smoke_dust_convert,
        case when (pm_convert is null or pm_convert = '') then '0' else pm_convert end as pm_convert,
        outlet_id,
        factory_id
        from s_env_gas_mon_real
        where
        data_type = 'M'
          and monitoring_time >= #{dateStr}
          and monitoring_time &lt; #{dateStrEnd}) t1
        group by t1.outlet_id,t1.factory_id
    </select>

    <select id="selectSEnvGasMonRealListEchart" resultType="cn.cecep.talroad.domain.SEnvGasMonReal">
        SELECT
            segmr.factory_id,
            segmr.outlet_id,
            segmr.data_type,
            segmr.monitoring_time,
            segmr.so2,
            segmr.so2_convert,
            segmr.so2_emission,
            segmr.nox,
            segmr.nox_convert,
            segmr.nox_emission,
            segmr.no,
            segmr.no_convert,
            segmr.no_emission,
            segmr.no2,
            segmr.no2_convert,
            segmr.no2_emission,
            segmr.smoke_dust,
            segmr.smoke_dust_convert,
            segmr.smoke_dust_emission,
            segmr.pm,
            segmr.pm_convert,
            segmr.pm_emission,
            segmr.vocs,
            segmr.vocs_convert,
            segmr.vocs_emission,
            segmr.o3,
            segmr.oxygen,
            segmr.smoke_dust_tem,
            segmr.smoke_dust_pressure,
            segmr.smoke_dust_flow_velocity,
            segmr.smoke_dust_humidity,
            segmr.date_time,
            segmr.oxygen_convert,
            segmr.oxygen_emission,
            segmr.nmhc,
            segmr.nmhc_convert,
            segmr.nmhc_emission,
            segmr.so2_flag,
            segmr.nox_flag,
            segmr.no_flag,
            segmr.no2_flag,
            segmr.smoke_dust_flag,
            segmr.pm_flag,
            segmr.vocs_flag,
            segmr.o3_flag,
            segmr.oxygen_flag,
            segmr.smoke_dust_tem_flag,
            segmr.smoke_dust_pressure_flag,
            segmr.smoke_dust_flow_velocity_flag,
            segmr.smoke_dust_humidity_flag,
            segmr.nmhc_flag,
            segmr.data_flag,

        polet.outlet_name              outletName,
        polet.outlet_code              outletCode,
        pf.factory_name             factoryName,
        po.monitoring_point_name    monitoringPointName,
        polet.mark
        FROM s_env_gas_mon_real segmr
        LEFT JOIN pc_outlet po ON segmr.outlet_id = po."id"
        LEFT JOIN pc_outlet polet ON polet."id" = po."parent_outlet_id"
        LEFT JOIN pc_factory pf ON segmr.factory_id = pf."id"
        <where>
            po.pollutant_type = '4'
            <if test="sEnvGasMonReal.factoryId != null  and sEnvGasMonReal.factoryId != ''"> AND segmr.factory_id = #{sEnvGasMonReal.factoryId}</if>
            <if test="sEnvGasMonReal.outletId != null  and sEnvGasMonReal.outletId != ''"> AND outlet_id = #{sEnvGasMonReal.outletId}</if>
            <if test="sEnvGasMonReal.dataType != null  and sEnvGasMonReal.dataType != ''"> AND data_type = #{sEnvGasMonReal.dataType}</if>
            <if test="sEnvGasMonReal.monitoringStartTime != null  and sEnvGasMonReal.monitoringStartTime != '' and sEnvGasMonReal.monitoringEndTime != null  and sEnvGasMonReal.monitoringEndTime != ''">
                and monitoring_time BETWEEN #{sEnvGasMonReal.monitoringStartTime} AND #{sEnvGasMonReal.monitoringEndTime}
            </if>

            <if test="sEnvGasMonReal.monitoringPointName != null  and sEnvGasMonReal.monitoringPointName != ''"> and (po.monitoring_point_name = #{sEnvGasMonReal.monitoringPointName} or polet.monitoring_point_name = #{sEnvGasMonReal.monitoringPointName})</if>
            <if test="sEnvGasMonReal.mark != null  and sEnvGasMonReal.mark != ''"> and mark = #{sEnvGasMonReal.mark}</if>
            <if test="sEnvGasMonReal.outletIdArr != null and sEnvGasMonReal.outletIdArr.length != 0">
                <foreach collection="sEnvGasMonReal.outletIdArr" item="item" separator="," open="and outlet_id in (" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
    <select id="selectEmissionStat" resultType="cn.cecep.talroad.vo.SEnvGasMonRealVo$EmissionSumVo">
        select
            gmr.outlet_id,
            round(sum(so2_emission::numeric), 6) so2_emission_sum,
            round(sum(nox_emission::numeric), 6) nox_emission_sum,
            round(sum(no_emission::numeric), 6) no_emission_sum,
            round(sum(no2_emission::numeric), 6) no2_emission_sum,
            round(sum(smoke_dust_emission::numeric), 6) smoke_dust_emission_sum,
            round(sum(pm_emission::numeric), 6) pm_emission_sum,
            round(sum(vocs_emission::numeric), 6) vocs_emission_sum,
            round(sum(oxygen_emission::numeric), 6) oxygen_emission_sum,
            count(*) hour_data_count
        from s_env_gas_mon_real gmr
        where gmr.data_type = 'H'
        and gmr.date_time >= to_timestamp(#{startTime}, 'yyyy-mm-dd hh24:MI:SS')
        and gmr.date_time &lt;= to_timestamp(#{endTime}, 'yyyy-mm-dd hh24:MI:SS')
        <if test="outletIds != null and outletIds.size() > 0">
            AND gmr.outlet_id IN
            <foreach collection="outletIds" open="(" separator="," close=")" item="item">
                #{item}
            </foreach>
        </if>
        group by gmr.outlet_id
    </select>
    <select id="selectEmissionAvg" resultType="cn.cecep.talroad.vo.SEnvGasMonRealVo$OxygenEmissionAvg">
        SELECT
            t1.outlet_id,
            round(avg(oxygen_emission::numeric), 6) oxygen_emission_avg
        FROM (
            SELECT
                outlet_id, date_time, oxygen_emission
            FROM s_env_gas_mon_real
            WHERE data_type = 'H'
            AND date_time >= to_timestamp(#{startTime}, 'yyyy-mm-dd hh24:MI:SS')
            AND date_time &lt;= to_timestamp(#{endTime}, 'yyyy-mm-dd hh24:MI:SS')
            AND outlet_Id IN
            <foreach collection="outletIds" open="(" separator="," close=")" item="item">
                #{item}
            </foreach>
        ) t1
        <!-- 关联查询监测点停产记录 -->
        LEFT JOIN (
            SELECT t2.id , t3.start_time , t3.end_time
            FROM pc_outlet t2
            INNER JOIN ods_online_ol_stopinfo_a t3 ON t2.outlet_code = t3.port_num AND t2.factory_id = t3.factory_id
            WHERE t3.start_time >= to_timestamp(#{startTime}, 'yyyy-mm-dd hh24:MI:SS')
            and t3.end_time &lt;= to_timestamp(#{endTime}, 'yyyy-mm-dd hh24:MI:SS')
        ) t2 ON t1.outlet_id = t2."id" AND t1.date_time >= t2.start_time AND t1.date_time &lt;= t2.end_time

        <!-- 关联查询监测点白名单记录 -->
        LEFT JOIN (
            SELECT
                t2.id,
                t3.start_time,
                t3.end_time
            FROM pc_outlet t2
            INNER JOIN ods_online_ol_ps_whitelist_a t3 ON t2.outlet_code = t3.port_num AND t2.factory_id = t3.factory_id
            WHERE t3.start_time >= to_timestamp(#{startTime}, 'yyyy-mm-dd hh24:MI:SS')
            and t3.end_time &lt;= to_timestamp(#{endTime}, 'yyyy-mm-dd hh24:MI:SS')
            AND upper(t3.pollutant_code) IN ( SELECT upper(poll_code) FROM pc_pollutant WHERE is_gas_type = '1')
        ) t3 ON t1.outlet_id = t3."id" AND t1.date_time >= t3.start_time AND t1.date_time &lt;= t3.end_time
        WHERE t2."id" IS NULL
        AND t3."id" IS NULL
        GROUP BY t1.outlet_id
    </select>

    <select id="getEmissionDayAvg" resultType="cn.cecep.talroad.vo.analysis.AvgValueVo">
        SELECT
        oxygen_emission_avg as avgValue,
        device_id
        FROM
        (
        select
        outlet_id,
        date_time,
        factory_id,
        round(avg(oxygen_emission::numeric), 6) oxygen_emission_avg
        from
        s_env_gas_mon_hour
        where
        data_type = 'H'
        AND date_time >= to_timestamp(#{startTime}, 'yyyy-mm-dd hh24:MI:SS')
        AND date_time &lt;= to_timestamp(#{endTime}, 'yyyy-mm-dd hh24:MI:SS')
        group by outlet_id,date_time,factory_id
        )t1
        left join
        (select
        po.outlet_code,
        unnest (string_to_array(pro_facility_id,',')) pro_facility_id
        from
        b_main_production_organiz_gas bmpog
        left join
        pc_outlet po
        on
        po.outlet_code = bmpog.outlet_code
        )t2
        on
        t1.outlet_id = t2.outlet_code
        left join
        bas_device bd
        on
        t2.pro_facility_id in (bd.device_id)
        group by
        bd.device_id,t1.oxygen_emission_avg
    </select>
    <select id="selectGasByDeviceIds"
            resultType="cn.cecep.talroad.data.analyse.task.limestone.entity.GasDeviceVo">

        SELECT
            outlet.pro_facility_id deviceId,
            round(avg(oxygen_emission::numeric), 6)  avgGas
        FROM
            (
                SELECT
                    po."id",
                    UNNEST ( string_to_array( pro_facility_id, ',' ) ) pro_facility_id
                FROM
                    "pc_outlet" po
                        LEFT JOIN b_main_production_organiz_gas bmpog ON po.outlet_code = bmpog.outlet_code
                WHERE
                    po.outlet_code IS NOT NULL
                  AND bmpog.outlet_code IS NOT NULL
                  AND bmpog.pro_facility_id IS NOT NULL
            ) AS outlet
                LEFT JOIN s_env_gas_mon_real segmr ON segmr.outlet_id = outlet.id
        WHERE
            outlet.pro_facility_id IN
            <foreach collection="deviceIds" index="index" open="(" close=")" item="item" separator=",">
                #{item}
            </foreach>
             AND segmr.oxygen_emission IS NOT NULL
             AND segmr.monitoring_time >= #{startTime}
             AND segmr.monitoring_time &lt;= #{endTime}
        GROUP BY outlet.pro_facility_id;


    </select>
    <select id="selectDayEmissionAvg" resultType="cn.cecep.talroad.vo.analysis.AvgValueVo">

        SELECT
            oxygen_emission_avg as avgValue,
            device_id
        FROM
            (
                select
                    outlet_id,
                    date_time,
                    factory_id,
                    round(avg(oxygen_emission::numeric), 6) oxygen_emission_avg
                from
                    s_env_gas_mon_hour
                where
                    data_type = 'H'
                  AND date_time >= to_timestamp(#{startTime}, 'yyyy-mm-dd hh24:MI:SS')
                  AND date_time &lt;= to_timestamp(#{endTime}, 'yyyy-mm-dd hh24:MI:SS')
                group by outlet_id,date_time,factory_id
            )t1
                left join
            (select
                 po.outlet_code,
                 unnest (string_to_array(pro_facility_id,',')) pro_facility_id
             from
                 b_main_production_organiz_gas bmpog
                     left join
                 pc_outlet po
                 on
                     po.outlet_code = bmpog.outlet_code
            )t2
            on
                t1.outlet_id = t2.outlet_code
                left join
            bas_device bd
            on
                t2.pro_facility_id in (bd.device_id)
        group by
            bd.device_id,t1.oxygen_emission_avg

    </select>
    <select id="selectLastHourData" resultType="cn.cecep.talroad.domain.SEnvGasMonReal">
        select r.* from pc_outlet po
        left join s_env_gas_mon_hour r on po.factory_id =  r.factory_id and po.id = r.outlet_id
        where r.data_type ='H' and r.monitoring_time =#{lastHour} and po.pollutant_type ='4' and po.pollutant_outlet_type = #{pollutantOutletType}
    </select>
    <select id="selectByOutLetId" resultType="java.math.BigDecimal">

        SELECT
        ROUND( AVG ( oxygen_emission::NUMERIC ), 2 )
        FROM
        s_env_gas_mon_hour segr
        WHERE
        factory_id = #{factoryId}
        AND outlet_id = #{outLetId}
        AND data_type = 'H'
        AND monitoring_time between #{startTime} and #{endTime}

    </select>
    <select id="selectLastDayData"
        resultType="cn.cecep.talroad.vo.analysis.FactoryOutletStaticsVo">
        select a.* from
            (select
                 factory_id as factoryId,
                 outlet_id as outletId ,
                 count(1) as actualCount
             from
                 s_env_gas_mon_real segmh
             WHERE
                 data_type = 'R'
               AND monitoring_time between #{lastDayBegin} and #{lastDayEnd}
             group by factory_id , outlet_id
            ) a
                inner join pc_outlet po on a.factoryId = po.factory_id and a.outletId = po.id
        where po.pollutant_type ='4'
          and po.pollutant_outlet_type ='1'
          and po.monitoring_point_name not like '%入口%'
          and po.monitoring_point_name not like '%进口%'
    </select>
</mapper>
