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

    <sql id="selectPcFactoryVo">
        fac.id,
        fac.factory_name,
        fac.city_code,
        fac.city_name,
        fac.country_code,
        fac.country_name,
        fac.river_basin,
        fac.wipollunit,
        fac.license_man_type,
        fac.license_wabarbeitung,
        fac.pre_con_level,
        fac.poll_dis_license_code,
        fac.poll_dis_license_deadline,
        fac.poll_dis_license_issuing,
        fac.usc_code,
        fac.ins_fram_code,
        fac.address,
        fac.prod_busi_operation,
        fac.postal_code,
        fac.industry_category,
        fac.industry_category_other,
        fac.whether_production,
        fac.production_date,
        fac.production_lon,
        fac.production_lat,
        fac.principal_man,
        fac.technical_man,
        fac.tel,
        fac.mobile_telephone,
        fac.whether_local_key_ari,
        fac.whether_local_key_pho,
        fac.whether_local_key_nitrogen,
        fac.whether_local_key_poll_area,
        fac.whether_local_park,
        fac.whether_review_file,
        fac.whether_gov_pro_file,
        fac.environmental_code,
        fac.gov_pro_ident_file,
        fac.major_pollutants_code,
        fac.major_pollutant_categories,
        fac.air_main_pollutants,
        fac.air_emission_pollutants,
        fac.air_discharge_pollutants,
        fac.water_main_pollutants,
        fac.water_emission_pollutants,
        fac.water_discharge_pollutants,
        fac.use_trading_emission,
        fac.sud_links_pollution_prevention,
        fac.industrial_park_name,
        fac.industrial_park_code,
        fac.pollutant_total_file_where,
        fac.eco_env_credit_level,
        fac.industry_permission_name
    </sql>
    <sql id="selectPcFactoryHomeVo">
        fac.id,
        fac.factory_name,
        fac.license_man_type,
        fac.license_wabarbeitung,
        fac.production_lon,
        fac.production_lat,
        fac.mobile_telephone,
        fac.poll_dis_license_code,
        fac.address,
        fac.principal_man,
        fac.technical_man
    </sql>

    <select id="selectPcFactoryVoList" resultType="cn.cecep.talroad.vo.PcFactoryVo">
        <include refid="selectPcFactoryVo_SQL"/>
    </select>

    <sql id="selectPcFactoryVo_SQL">
        SELECT
        <!-- 查询企业基本信息字段 -->
        <if test="str == null">
            <include refid="selectPcFactoryVo"/>,
        </if>

        <!-- 地图点位字段 -->
        <if test="str =='home' ">
            <include refid="selectPcFactoryHomeVo"/>,
        </if>

        <!-- 固定源档案列表 -->
        <if test="str =='list' ">
            fac.id,
            fac.factory_name,
            fac.poll_dis_license_code,
            fac.address,
            fac.technical_man,
            fac.industry_category,
            fac.prod_busi_operation,
            fac.city_name,
            fac.country_name,
            fac.principal_man,
            fac.mobile_telephone,
            <if test="query.labelAndValue != null and query.labelAndValue.size() > 1">
                CASE WHEN t6.factory_id IS NULL THEN 0 ELSE 1 END AS label_checked,
            </if>
        </if>
            fac.industry_permission_name AS "industry_category_name",
            t3.qr_code_url,
            t5.emissions_actual
        FROM
            pc_factory fac
            LEFT JOIN (
                SELECT factory_id, MAX(qr_code_url) AS "qr_code_url" FROM b_main_poll_dis GROUP BY factory_id
            ) t3 ON fac.id = t3.factory_id
            LEFT JOIN (
                SELECT factory_id, SUM(emissions_actual) AS emissions_actual
                FROM pc_factory_pollutant_emissions_rank
                GROUP BY factory_id
            ) t5 ON fac.id = t5.factory_id
            <if test="query.labelAndValue != null and query.labelAndValue.size() > 1">
                LEFT JOIN (
                    SELECT factory_id
                    FROM pc_label_factory
                    WHERE label_id = '${query.labelAndValue[0]}'
                    AND '${query.labelAndValue[1]}' = ANY( string_to_array(label_value, ',') )
                    GROUP BY factory_id
                ) t6 ON fac.id = t6.factory_id
            </if>
        <where>
            <if test="query.ids != null and query.ids.size() > 0">
                AND fac.id IN
                <foreach collection="query.ids" open="(" separator="," close=")" item="item">
                    #{item}
                </foreach>
            </if>
            <if test="query.id != null and query.id != ''"> and fac.id = #{query.id}</if>
            <if test="query.factoryName != null  and query.factoryName != ''"> and factory_name like concat('%', #{query.factoryName}, '%')</if>
            <if test="query.cityCode != null  and query.cityCode != ''"> and city_code = #{query.cityCode}</if>
            <if test="query.cityName != null  and query.cityName != ''"> and city_name like concat('%', #{query.cityName}, '%')</if>
            <if test="query.countryCode != null  and query.countryCode != ''"> and country_code = #{query.countryCode}</if>
            <if test="query.countryName != null  and query.countryName != ''"> and country_name like concat('%', #{query.countryName}, '%')</if>
            <if test="regionCodes != null and regionCodes.size() != 0">
                and fac.country_code IN
                <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                    #{item,jdbcType=VARCHAR}
                </foreach>
            </if>
        </where>
        ORDER BY fac.order_num, fac.id
    </sql>

    <select id="selectCompanyOverview" resultType="cn.cecep.talroad.vo.homepage.HpmCompanyOverviewVo">
        SELECT
            COUNT( 1 ) 		overviewPollutingUnits,
            COALESCE ( SUM ( CAST ( wipollunit as int4 ) ), 0 ) overviewPollutingEmphasis,
            COALESCE ( SUM ( CASE license_man_type WHEN 'FOCUS' THEN 1 ELSE 0 END ), 0 ) overviewKeyManagement,
            COALESCE ( SUM ( CASE license_man_type WHEN 'SIMPLIFY' THEN 1 ELSE 0 END ), 0 ) overviewSimplifyManagement,
            COALESCE ( SUM ( CASE license_man_type WHEN 'REGISTER' THEN 1 ELSE 0 END ), 0 ) overviewRegisterManagement,
            COALESCE ( SUM ( CAST ( license_wabarbeitung as int4 ) ), 0 ) overviewAbarbeitungTimes,

            ROUND( SUM( CASE whether_local_park WHEN '是' THEN 1 ELSE 0 END )::numeric / COUNT( 1 )::numeric, 4 ) whetherLocalParkProp
        FROM
            pc_factory
        <where>
            <if test="industry != null and industry != ''">
              and  industry_category = #{industry}
            </if>
            <if test="regionCodes != null and regionCodes.size() != 0">
                and country_code IN
                <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                    #{item,jdbcType=VARCHAR}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectOutletOverview" resultType="cn.cecep.talroad.vo.homepage.HpmCompanyOverviewVo">
        SELECT
            count(distinct CASE pollutant_type WHEN '2' THEN po.factory_id END ) waterEnterprise,
            COALESCE ( SUM( CASE WHEN pollutant_type = '2' AND outlet_types = '1' THEN 1 ELSE 0 END ), 0 ) waterMainOutlet,
            COALESCE ( SUM( CASE WHEN pollutant_type = '2' AND outlet_types = '2' THEN 1 ELSE 0 END ), 0 ) waterOrdinaryOutlet,

            count(distinct CASE pollutant_type WHEN '1' THEN po.factory_id END ) airEnterprise,
            COALESCE ( SUM( CASE WHEN pollutant_type = '1' AND outlet_types = '1' THEN 1 ELSE 0 END ), 0 ) airMainOutlet,
            COALESCE ( SUM( CASE WHEN pollutant_type = '1' AND outlet_types = '2' THEN 1 ELSE 0 END ), 0 ) airOrdinaryOutlet,
            COALESCE ( SUM( CASE WHEN pollutant_type = '3' THEN 1 ELSE 0 END ), 0 ) airInorganization

        FROM
            pc_factory pf
                LEFT JOIN pc_outlet po ON pf."id" = po.factory_id
        <where>
            <if test="industry != null and industry != ''">
             and   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>
        </where>
    </select>

    <select id="selectMeritRatingOverview" resultType="cn.cecep.talroad.vo.homepage.HpmMeriRatingOverviewVo">
        SELECT
            COUNT ( 1 )    enterprises,
            SUM ( CASE WHEN pre_con_level IS NOT NULL THEN 1 ELSE 0 END )    completeRatingNum,
            SUM ( CASE WHEN pre_con_level IS NULL THEN 1 ELSE 0 END )    incompleteRatingNum,
            COUNT ( DISTINCT industry_category )    overviewIndustryAmount,
            COALESCE ( SUM( CASE WHEN pre_con_level = 'A' THEN 1 ELSE 0 END ), 0) enterpriseA,
            COALESCE ( SUM( CASE WHEN pre_con_level = 'B' THEN 1 ELSE 0 END ), 0) enterpriseB,
            COALESCE ( SUM( CASE WHEN pre_con_level = 'B-' THEN 1 ELSE 0 END ), 0) enterpriseBdown,
            COALESCE ( SUM( CASE WHEN pre_con_level = 'C' THEN 1 ELSE 0 END ), 0) enterpriseC,
            COALESCE ( SUM( CASE WHEN pre_con_level = 'D' THEN 1 ELSE 0 END ), 0) enterpriseD,
            COALESCE ( SUM( CASE WHEN pre_con_level = 'EXEMPTION' THEN 1 ELSE 0 END ), 0) enterpriseExempt,
            COALESCE ( SUM( CASE WHEN pre_con_level = 'NOT_RATED' THEN 1 ELSE 0 END ), 0) enterpriseExemptUnrated,
            COALESCE ( SUM( CASE WHEN pre_con_level = 'NON_PERFORMING_BUSINESS' THEN 1 ELSE 0 END ), 0) enterpriseNonPerformance
        FROM
            pc_factory
        WHERE 1 = 1
        <if test="industry != null and industry != ''">
            AND industry_category = #{industry}
        </if>
        <if test="regionCodes != null and regionCodes.size() != 0">
            and country_code IN
            <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                #{item,jdbcType=VARCHAR}
            </foreach>
        </if>
    </select>

    <select id="selectOnvironmentOverview" resultType="cn.cecep.talroad.vo.homepage.HpmEnvironmentOverviewVo">
        SELECT
            COALESCE ( SUM( CASE eco_env_credit_level WHEN 'A' THEN 1 ELSE 0 END ), 0) overviewGreen,
            COALESCE ( SUM( CASE eco_env_credit_level WHEN 'B' THEN 1 ELSE 0 END ), 0) overviewBlue,
            COALESCE ( SUM( CASE eco_env_credit_level WHEN 'C' THEN 1 ELSE 0 END ), 0) overviewYellow,
            COALESCE ( SUM( CASE eco_env_credit_level WHEN 'D' THEN 1 ELSE 0 END ), 0) overviewRed,
            COALESCE ( SUM( CASE eco_env_credit_level WHEN 'E' THEN 1 ELSE 0 END ), 0) overviewGray
        FROM
            pc_factory
        <where>
            <if test="industry != null and industry != ''">
              and  industry_category = #{industry}
            </if>
            <if test="regionCodes != null and regionCodes.size() != 0">
                and country_code IN
                <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                    #{item,jdbcType=VARCHAR}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectGasEmissionTrackingOverview" resultType="cn.cecep.talroad.vo.homepage.HpmEmissionTrackingOverviewVo">
        SELECT
            COALESCE ( SUM ( CASE WHEN is_numeric (actually_emitting_so2) THEN actually_emitting_so2::NUMERIC ELSE 0 END ), 0) AS actualDischargeSo2,
            COALESCE ( SUM ( CASE WHEN is_numeric (allowable_emission_so2) THEN allowable_emission_so2::NUMERIC ELSE 0 END ), 0) AS permissibleDischargeSo2,
            COALESCE ( SUM ( CASE WHEN is_numeric (actually_emitting_nox) THEN actually_emitting_nox::NUMERIC ELSE 0 END ), 0) AS actualDischargeNox,
            COALESCE ( SUM ( CASE WHEN is_numeric (allowable_emission_nox) THEN allowable_emission_nox::NUMERIC ELSE 0 END ), 0) AS permissibleDischargeNox,
            COALESCE ( SUM ( CASE WHEN is_numeric (actually_emitting_pm) THEN actually_emitting_pm::NUMERIC ELSE 0 END ), 0) AS actualDischargePm,
            COALESCE ( SUM ( CASE WHEN is_numeric (allowable_emission_pm) THEN allowable_emission_pm::NUMERIC ELSE 0 END ), 0) AS permissibleDischargePm,
            COALESCE ( SUM ( CASE WHEN is_numeric (actually_emitting_vocs) THEN actually_emitting_vocs::NUMERIC ELSE 0 END ), 0) AS actualDischargeVocs,
            COALESCE ( SUM ( CASE WHEN is_numeric (allowable_emission_vocs) THEN allowable_emission_vocs::NUMERIC ELSE 0 END ), 0) AS permissibleDischargeVocs
        FROM pc_factory pf
            LEFT JOIN b_main_emission_pollution_air bmepa ON pf.id = bmepa.factory_id
        WHERE data_source = #{emissionType}
        <if test="regionCodes != null and regionCodes.size() != 0">
            and country_code IN
            <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                #{item,jdbcType=VARCHAR}
            </foreach>
        </if>
    </select>

    <select id="selectWaterEmissionTrackingOverview" resultType="cn.cecep.talroad.vo.homepage.HpmEmissionTrackingOverviewVo">
        SELECT
            COALESCE ( SUM ( CASE WHEN is_numeric (actually_emitting_cod) THEN actually_emitting_cod::NUMERIC ELSE 0 END ), 0) AS actualDischargeCod,
            COALESCE ( SUM ( CASE WHEN is_numeric (allowable_emission_cod) THEN allowable_emission_cod::NUMERIC ELSE 0 END ), 0) AS permissibleDischargeCod,
            COALESCE ( SUM ( CASE WHEN is_numeric (actually_emitting_nhn) THEN actually_emitting_nhn::NUMERIC ELSE 0 END ), 0) AS actualDischargeNhn,
            COALESCE ( SUM ( CASE WHEN is_numeric (allowable_emission_nhn) THEN allowable_emission_nhn::NUMERIC ELSE 0 END ), 0) AS permissibleDischargeNhn,
            COALESCE ( SUM ( CASE WHEN is_numeric (actually_emitting_tn) THEN actually_emitting_tn::NUMERIC ELSE 0 END ), 0) AS actualDischargeTn,
            COALESCE ( SUM ( CASE WHEN is_numeric (allowable_emission_tn) THEN allowable_emission_tn::NUMERIC ELSE 0 END ), 0) AS permissibleDischargeTn,
            COALESCE ( SUM ( CASE WHEN is_numeric (actually_emitting_tp) THEN actually_emitting_tp::NUMERIC ELSE 0 END ), 0) AS actualDischargeTp,
            COALESCE ( SUM ( CASE WHEN is_numeric (allowable_emission_tp) THEN allowable_emission_tp::NUMERIC ELSE 0 END ), 0) AS permissibleDischargeTp
        FROM pc_factory pf
            LEFT JOIN b_main_emission_pollution_water bmepw ON pf.id = bmepw.factory_id
        WHERE data_source = #{emissionType}
        <if test="regionCodes != null and regionCodes.size() != 0">
            and country_code IN
            <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                #{item,jdbcType=VARCHAR}
            </foreach>
        </if>
    </select>

    <select id="selectImportPollutionDischarge" resultType="cn.cecep.talroad.vo.ImportPollutionDischargeVo">
        SELECT
            plv.arguments_code          AS argumentsCode,
            MAX(plv.arguments_name)     AS argumentsName,

            SUM( CASE WHEN plv.arguments_code = ANY( STRING_TO_ARRAY( label_value, ',') ) THEN 1 ELSE 0 END ) "count"
        FROM
            pc_label_value plv
            LEFT JOIN pc_label_factory plf ON plv.label_id = plf.label_id
            LEFT JOIN pc_factory pf ON pf.id=plf.factory_id
        WHERE plv.label_id= '7'
        <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>
        GROUP BY plv.arguments_code
        UNION ALL
        SELECT
            'importEnterprises'        AS argumentsCode,
            '企业总数'                  AS arguments_name,
            SUM ( CASE wipollunit WHEN '1' THEN 1 ELSE 0 END ) "count"
        FROM
            pc_factory
        <where>
            <if test="regionCodes != null and regionCodes.size() != 0">
                and country_code IN
                <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                    #{item,jdbcType=VARCHAR}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectProportionOfEcologicalEnvironmentCredit" resultType="cn.cecep.talroad.vo.EnvironmentCreditPropVo">
        SELECT
            count(1)    enterprises,
            ROUND( SUM( CASE eco_env_credit_level WHEN 'A' THEN 1 ELSE 0 END )::numeric / count( 1 )::numeric, 4) overviewA,
            ROUND( SUM( CASE eco_env_credit_level WHEN 'B' THEN 1 ELSE 0 END )::numeric / count( 1 )::numeric, 4) overviewB,
            ROUND( SUM( CASE eco_env_credit_level WHEN 'C' THEN 1 ELSE 0 END )::numeric / count( 1 )::numeric, 4) overviewC,
            ROUND( SUM( CASE eco_env_credit_level WHEN 'D' THEN 1 ELSE 0 END )::numeric / count( 1 )::numeric, 4) overviewD,
            ROUND( SUM( CASE eco_env_credit_level WHEN 'E' THEN 1 ELSE 0 END )::numeric / count( 1 )::numeric, 4) overviewE
        FROM
            pc_factory
        WHERE eco_env_credit_level IS NOT NULL
    </select>

    <select id="selectPcFactoryVoPage" resultType="cn.cecep.talroad.vo.PcFactoryVo">
        <include refid="selectPcFactoryVo_SQL"/>
    </select>

    <select id="selectIndustryEmissionTrackingOverviewVo"
            resultType="cn.cecep.talroad.vo.IndustryEmissionTrackingOverviewVo">
        SELECT
            p.poll_name,
            COALESCE ( emissions_actual, 0) AS emissions_actual,
            COALESCE ( emissions_permit, 0) AS emissions_permit,
            CASE WHEN emissions_permit = 0 THEN 0 ELSE COALESCE ( emissions_actual / emissions_permit, 0) END AS calculate,

            RANK() OVER ( ORDER BY CASE WHEN emissions_permit = 0 THEN NULL ELSE COALESCE ( emissions_actual / emissions_permit, 0) END DESC ) rank

        FROM
            ( SELECT regexp_split_to_table('SO₂,颗粒物,COD,总氮,NOₓ,VOCs,氨氮,总磷', ',') as poll_name ) p
                LEFT JOIN (
                SELECT
                    poll_name,
                    SUM ( emissions_actual ) AS emissions_actual,
                    SUM ( emissions_permit ) AS emissions_permit
                FROM
                    pc_factory_pollutant_emissions_rank pcper
                        LEFT JOIN pc_factory pf ON pf.id = pcper.factory_id
                        LEFT JOIN pc_industry pi ON pi.indus_code = pf.industry_category
                WHERE data_source = #{emissionType} and indus_code = #{industry}
                GROUP BY poll_name
            ) tmp ON p.poll_name = tmp.poll_name
    </select>


    <select id="selectTop10CompaniesWithPollutantEmissionsInTheIndustry"
            resultType="cn.cecep.talroad.vo.IndustryPollutantEmissionsTop10Vo">
        SELECT
            pf.factory_name     factoryName,
            concat( pf.city_name, pf.country_name )     district,
            emissions_actuals   emissions,
            tmp.rank
        FROM
            (
                SELECT
                    pf."id",
                    pi.indus_code,
                    SUM( CASE poll_name WHEN #{pollutantType} THEN emissions_actual ELSE 0 END ) emissions_actuals,
                    RANK() OVER ( PARTITION BY pi.indus_code ORDER BY SUM( CASE poll_name WHEN #{pollutantType} THEN emissions_actual ELSE 0 END ) DESC )         rank
                FROM pc_factory pf
                     LEFT JOIN (
                        SELECT 'SO₂'  poll_name, factory_id, CASE WHEN is_numeric (actually_emitting_so2) THEN actually_emitting_so2::NUMERIC ELSE 0 END AS emissions_actual, CASE WHEN is_numeric (allowable_emission_so2) THEN allowable_emission_so2::NUMERIC ELSE 0 END AS emissions_permit FROM b_main_emission_pollution_air WHERE data_source = '系统核算'
                        UNION ALL
                        SELECT '颗粒物'  poll_name, factory_id, CASE WHEN is_numeric (actually_emitting_pm) THEN actually_emitting_pm::NUMERIC ELSE 0 END AS emissions_actual, CASE WHEN is_numeric (allowable_emission_pm) THEN allowable_emission_pm::NUMERIC ELSE 0 END  AS emissions_permit FROM b_main_emission_pollution_air WHERE data_source = '系统核算'
                        UNION ALL
                        SELECT 'VOCs'  poll_name, factory_id, CASE WHEN is_numeric (actually_emitting_vocs) THEN actually_emitting_vocs::NUMERIC ELSE 0 END AS emissions_actual, CASE WHEN is_numeric (allowable_emission_vocs) THEN allowable_emission_vocs::NUMERIC ELSE 0 END  AS emissions_permit FROM b_main_emission_pollution_air WHERE data_source = '系统核算'
                        UNION ALL
                        SELECT 'NOₓ'  poll_name, factory_id, CASE WHEN is_numeric (actually_emitting_nox) THEN actually_emitting_nox::NUMERIC ELSE 0 END AS emissions_actual, CASE WHEN is_numeric (allowable_emission_nox) THEN allowable_emission_nox::NUMERIC ELSE 0 END  AS emissions_permit FROM b_main_emission_pollution_air WHERE data_source = '系统核算'
                        UNION ALL
                        SELECT 'COD'  poll_name, factory_id, CASE WHEN is_numeric (actually_emitting_cod) THEN actually_emitting_cod::NUMERIC ELSE 0 END AS emissions_actual, CASE WHEN is_numeric (allowable_emission_cod) THEN allowable_emission_cod::NUMERIC ELSE 0 END  AS emissions_permit FROM b_main_emission_pollution_water WHERE data_source = '系统核算'
                        UNION ALL
                        SELECT '总氮'  poll_name, factory_id, CASE WHEN is_numeric (actually_emitting_nhn) THEN actually_emitting_nhn::NUMERIC ELSE 0 END AS emissions_actual, CASE WHEN is_numeric (allowable_emission_nhn) THEN allowable_emission_nhn::NUMERIC ELSE 0 END  AS emissions_permit FROM b_main_emission_pollution_water WHERE data_source = '系统核算'
                        UNION ALL
                        SELECT '总磷'  poll_name, factory_id, CASE WHEN is_numeric (actually_emitting_tp) THEN actually_emitting_tp::NUMERIC ELSE 0 END AS emissions_actual, CASE WHEN is_numeric (allowable_emission_tp) THEN allowable_emission_tp::NUMERIC ELSE 0 END  AS emissions_permit FROM b_main_emission_pollution_water WHERE data_source = '系统核算'
                        UNION ALL
                        SELECT '氨氮'  poll_name, factory_id, CASE WHEN is_numeric (actually_emitting_tn) THEN actually_emitting_tn::NUMERIC ELSE 0 END AS emissions_actual, CASE WHEN is_numeric (allowable_emission_tn) THEN allowable_emission_tn::NUMERIC ELSE 0 END  AS emissions_permit FROM b_main_emission_pollution_water WHERE data_source = '系统核算'
                        ) pcper ON pf.id = pcper.factory_id
                     LEFT JOIN pc_industry pi ON pi.indus_code = pf.industry_category
        <where>
                <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>
        </where>
                GROUP BY pi.indus_code, pf."id"
        ) tmp
        LEFT JOIN pc_factory pf ON pf.id = tmp.id
        <where>
            <if test="industry != null and industry != ''">
                indus_code = #{industry}
            </if>
            AND tmp.rank <![CDATA[ <= ]]> 10
        </where>
        ORDER BY rank
        LIMIT  10 offset 0
    </select>

    <select id="selectEmissionsByCompany" resultType="hashmap">
        SELECT
            pf."id"         factoryId,
            MAX ( pi.indus_name )       AS indus_name,
            <if test="groupByIndustry">pf.industry_category,</if>
            SUM( CASE poll_name WHEN #{pollutantType} THEN emissions_actual ELSE 0 END ) emissions
        FROM pc_factory_pollutant_emissions_rank pcper
            LEFT JOIN pc_factory pf ON pf.id = pcper.factory_id
            LEFT JOIN pc_industry pi ON pf.industry_category = pi.indus_code
        WHERE data_source = '0'
        GROUP BY
            <if test="groupByIndustry">
                pf.industry_category ,
            </if>
            pf."id"

        ORDER BY emissions DESC <if test="groupByIndustry">, pf.industry_category ASC</if>
    </select>

    <select id="selectPcFactoryById" resultType="cn.cecep.talroad.domain.PcFactory">
        SELECT
            pf.*,
            pi.indus_name
        FROM
            pc_factory pf
            LEFT JOIN pc_industry pi ON pi.indus_code = pf.industry_category
        WHERE
            pf.id = #{id}
    </select>

    <select id="selectPcFactory" resultType="cn.cecep.talroad.domain.PcFactory">
        SELECT
            pf.id,
            pf.factory_name,
            pf.industry_category,
            pf.city_code,
            pf.country_code,
            pf.country_name,
            pf.city_name,
            pf.pre_con_level,
            pf.industry_permission_name,
            pi.indus_name
        FROM
            pc_factory pf
            LEFT JOIN pc_industry pi ON pi.indus_code = pf.industry_category
    </select>

    <select id="selectPermitEmissionsFactoryList"
            resultType="cn.cecep.talroad.vo.analysis.PermitEmissionsFactoryVO">
        --查询排污许可有效内的企业列表
        SELECT
            dis.id,
            dis.data_id,
            dis.poll_dis_license_code,
            dis.factory_id,
            substr(dis.poll_dis_license_deadline, 0,POSITION('至' in dis.poll_dis_license_deadline)-6) as startDate,
            substr(dis.poll_dis_license_deadline, POSITION('至' in dis.poll_dis_license_deadline)+1,4) as endDate,
            factory.factory_name
        FROM
            b_main_poll_dis dis,
            ( SELECT MAX( dis.ID ) AS license_id ,dis.factory_id FROM b_main_poll_dis dis GROUP BY dis.factory_id ) dis2,
            pc_factory factory
        WHERE
            dis.id = dis2.license_id and
            dis.factory_id = factory.id
    </select>
    <select id="selectWaterRainwaterOutlet" resultType="cn.cecep.talroad.vo.homepage.HpmCompanyOverviewVo">
        SELECT
            COALESCE ( SUM( CASE WHEN outlet_type = '2' THEN 1 ELSE 0 END ), 0 ) waterRainwaterOutlet
        FROM pc_factory pf
        LEFT JOIN m_supermonitor_drain_outlet t2 ON pf."id" = t2.factory_id
        <where>
            <if test="industry != null and industry != ''">
                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>
        </where>
    </select>

    <update id="updateScore">
        UPDATE pc_factory set score = #{score} WHERE id = #{id}
    </update>
    <update id="updateScoreBatch" parameterType="java.util.List">
        update pc_factory
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="score = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when id = #{item.id,jdbcType=VARCHAR} then #{item.score,jdbcType=BIGINT}
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach close=")" collection="list" item="item" open="(" separator=", ">
            #{item.id,jdbcType=VARCHAR}
        </foreach>
    </update>

    <select id="selectSourceIsHaveDataByFacIds" resultType="cn.cecep.talroad.domain.PcAdslFactory">
        select
        distinct on (factoryId)
        'A001'  adslCode,
        factory_id factoryId
        from pc_outlet
        where pollutant_type = '4' and factory_id in

        <foreach collection="factoryIds" item="factoryId" separator="," open="(" close=")">
            #{factoryId}
        </foreach>

        union

        select
        distinct on (factoryId)
        'A002'  adsl_code,
        factory_id factoryId
        from bas_device_site
        where factory_id in
        <foreach collection="factoryIds" item="factoryId" separator="," open="(" close=")">
            #{factoryId}
        </foreach>

        union

        select
        distinct on (factoryId)
        'A003'  adsl_code,
        factory_id factoryId
        from bas_device
        where industry in ('JH',
        'SN',
        'BL',
        'FS',
        'DL',
        'GT') and factory_id in
        <foreach collection="factoryIds" item="factoryId" separator="," open="(" close=")">
            #{factoryId}
        </foreach>

        union

        select
        distinct on (factoryId)
        'A004'  adsl_code,
        factory_id factoryId
        from pc_outlet
        where pollutant_type = '3' and factory_id in
        <foreach collection="factoryIds" item="factoryId" separator="," open="(" close=")">
            #{factoryId}
        </foreach>

        union

        select
        distinct on (factoryId)
        'A005'  adsl_code,
        factory_id factoryId
        from m_am_video_dev_fi
        where factory_id in
        <foreach collection="factoryIds" item="factoryId" separator="," open="(" close=")">
            #{factoryId}
        </foreach>

        union

        select
        distinct on (factoryId)
        'A006'  adsl_code,
        factory_id factoryId
        from s_main_dynamic_control
        where factory_id in
        <foreach collection="factoryIds" item="factoryId" separator="," open="(" close=")">
            #{factoryId}
        </foreach>

        union

        select
        distinct on (factoryId)
        'A007'  adsl_code,
        factory_id factoryId
        from pc_factory_access_control
        where factory_id in

        <foreach collection="factoryIds" item="factoryId" separator="," open="(" close=")">
            #{factoryId}
        </foreach>

    </select>

    <select id="selectRegulateBusinesses" resultType="cn.cecep.talroad.domain.PcFactory">
        select factory_name,industry_name as indusName,factory_id as id ,industry_code as industryCategory from regulate_businesses where factory_id is not null
    </select>

    <select id="overviewOnlineMonitors" resultType="cn.cecep.talroad.vo.homepage.OverviewOnlineMonitorsVo">
        select
            count(distinct case pollutant_type when '4' then po.factory_id end ) onlineEnterprise,
            count(distinct CASE pollutant_type WHEN '2' THEN po.factory_id END ) waterEnterprise,
            count(distinct CASE pollutant_type WHEN '1' THEN po.factory_id END ) airEnterprise,
            COALESCE ( SUM( CASE WHEN pollutant_type = '4'  THEN 1 ELSE 0 END ), 0 ) onlinePoint,
            COALESCE ( SUM( CASE WHEN pollutant_type = '4' and pollutant_outlet_type='1' or pollutant_outlet_type='3' THEN 1 ELSE 0 END ), 0 ) airOnlinePoint,
            COALESCE ( SUM( CASE WHEN pollutant_type = '4' and pollutant_outlet_type='2' THEN 1 ELSE 0 END ), 0 ) waterOnlinePoint,
            COALESCE ( SUM( CASE WHEN pollutant_type = '1' and source_permission ='2' THEN 1 ELSE 0 END ), 0 ) airMainOutlet,
            COALESCE ( SUM( CASE WHEN pollutant_type = '2' and source_permission ='2' THEN 1 ELSE 0 END ), 0 ) waterMainOutlet
        from
            pc_factory pf
                inner join pc_outlet po on
                pf."id" = po.factory_id
        <where>
            <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>
        </where>
    </select>

    <select id="gasMonitoringPointWarning" resultType="cn.cecep.talroad.vo.homepage.MonitoringPointWarningVo">
        SELECT
            COALESCE ( SUM( CASE WHEN t2.type = '0'   THEN 1 ELSE 0 END ), 0 ) gasMonitoringWarning,
            COALESCE ( SUM( CASE WHEN t2.problem_type = '003004' or t2.problem_type = '003002' THEN 1 ELSE 0 END ), 0 ) gasMonitoringExceeding,
            COALESCE ( SUM( CASE WHEN t2.problem_type = '001011' or t2.problem_type = '001006' or t2.problem_type = '001007' or t2.problem_type = '001008' or t2.problem_type = '001009' or t2.problem_type = '001010' or t2.problem_type = '003001'  THEN 1 ELSE 0 END ), 0 ) gasMonitoringAbnormal
        FROM
            ( SELECT ID,factory_id FROM pc_outlet WHERE pollutant_type = '4' AND pollutant_outlet_type = '1' OR pollutant_outlet_type = '3' ) AS t1
                INNER JOIN (select * from m_am_problem_fi where feedback_date is null ) AS t2 ON t1.ID = t2.station_id
        inner join pc_factory as pf on pf.id=t1.factory_id
        <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>
    </select>

    <select id="waterMonitoringPointWarning" resultType="cn.cecep.talroad.vo.homepage.MonitoringPointWarningVo">
        SELECT
            COALESCE ( SUM( CASE WHEN t2.type = '0'   THEN 1 ELSE 0 END ), 0 ) gasMonitoringWarning,
            COALESCE ( SUM( CASE WHEN t2.problem_type = '003004' or t2.problem_type = '003002'  THEN 1 ELSE 0 END ), 0 ) gasMonitoringExceeding,
            COALESCE ( SUM( CASE WHEN t2.problem_type = '001011' or t2.problem_type = '001006' or t2.problem_type = '001007' or t2.problem_type = '001008' or t2.problem_type = '001009' or t2.problem_type = '001010' or t2.problem_type = '003001'  THEN 1 ELSE 0 END ), 0 ) gasMonitoringAbnormal
        FROM
            ( SELECT ID,factory_id FROM pc_outlet WHERE pollutant_type = '4' AND pollutant_outlet_type = '2' ) AS t1
                INNER JOIN (select * from m_am_problem_fi where feedback_date is null ) AS t2 ON t1.ID = t2.station_id
        inner join pc_factory as pf on pf.id=t1.factory_id
        <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>
    </select>

    <select id="monitoringPointWarningDetails"
            resultType="cn.cecep.talroad.vo.homepage.MonitoringPointWarningDetailsVo">
        SELECT
            t2.param_name,
            split_part(REPLACE(t2.problem_details, '浓度为：', '浓度为:') ,'浓度为:', 2) as concentration,
            t2.problem_details,
            t3.factory_name,
            t5.name,
            CASE WHEN t2.problem_type in( '003004','003002' )   THEN '超标' WHEN t2.problem_type in('001011','001006','001007','001008','001009','001010','003001' ) THEN '异常' ELSE '预警' END as problem_type,
            CASE WHEN t1.pollutant_outlet_type ='2'  THEN '废水' ELSE '废气' END  as pollutant_outlet_type
        FROM
            ( SELECT ID,pollutant_outlet_type FROM pc_outlet WHERE pollutant_type = '4'  ) AS t1
                INNER JOIN (select * from m_am_problem_fi ) AS t2 ON t1.ID = t2.station_id
                INNER JOIN pc_ra_problem_type as t5 on t5.id=t2.problem_type
                INNER JOIN pc_factory AS t3 ON t3.ID = t2.factory_id
        where  t2.type = '0' or t2.problem_type in ('003004','003002','005001','001011','001006','001007','001008','001009','001010','003001') and feedback_date is null
        <if test="regionCodes != null and regionCodes.size() != 0">
            and t3.country_code IN
            <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                #{item,jdbcType=VARCHAR}
            </foreach>
        </if>
        ORDER BY analyse_time desc

    </select>

    <select id="enterpriseIssuesTop" resultType="cn.cecep.talroad.vo.homepage.EnterpriseIssuesTopVo">
        SELECT
            pf.factory_name,
            ta.warning ,
            ta.exceeding ,
            ta.abnormal,
            ta.warning + ta.exceeding + ta.abnormal as problem
        FROM
            (
                SELECT
                    factory_id,
                    COALESCE ( SUM ( CASE WHEN t2.TYPE = '0' THEN 1 ELSE 0 END ), 0 ) warning,
                    COALESCE (
                            SUM ( CASE WHEN t2.problem_type = '003004' OR t2.problem_type = '003002'  THEN 1 ELSE 0 END ),
                            0
                        ) exceeding,
                    COALESCE (
                            SUM (
                                    CASE

                                        WHEN t2.problem_type = '001011'
                                            OR t2.problem_type = '001006'
                                            OR t2.problem_type = '001007'
                                            OR t2.problem_type = '001008'
                                            OR t2.problem_type = '001009'
                                            OR t2.problem_type = '001010'
                                            OR t2.problem_type = '003001' THEN
                                            1 ELSE 0
                                        END
                                ),
                            0
                        ) abnormal
                FROM
                        ( SELECT ID FROM pc_outlet WHERE pollutant_type = '4' ) AS t1
                            INNER JOIN ( SELECT * FROM m_am_problem_fi WHERE feedback_date IS NULL ) AS t2 ON t1.ID = t2.station_id
                GROUP BY
                    t2.factory_id
            ) AS ta
                inner JOIN pc_factory as pf on pf.id=ta.factory_id
        <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>
        ORDER BY problem desc
            LIMIT 10
    </select>

    <select id="effectiveMonitoringRate" resultType="cn.cecep.talroad.domain.SEnvWaterMonReal">
        SELECT
            SUM( cod_emission::NUMERIC ) AS cod_emission,
            SUM( ammonia_nitrogen_emission::NUMERIC ) ammonia_nitrogen_emission,
            SUM( nitrogen_concentration_emission::NUMERIC ) nitrogen_concentration_emission,
            SUM( phosphorus_concentration_emission::NUMERIC ) phosphorus_concentration_emission,
            SUM( sewage_emission::NUMERIC ) sewage_emission,monitoring_time
        FROM
            "public"."s_env_water_mon_real" as wr inner join pc_factory as pf on wr.factory_id=pf.id
        <where>
            <if test="dataType != null and dataType!=''">
                and "data_type" = #{dataType}
            </if>
            <if test="monitoringStartTime != null and monitoringStartTime!=''">
                and "monitoring_time"  >=#{monitoringStartTime}
            </if>
            <if test="monitoringEndTime != null and monitoringEndTime!=''">
                and "monitoring_time"  &lt;=#{monitoringEndTime}
            </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>
            <if test="industryCode != null and industryCode != ''">
                and pf.industry_category = #{industryCode}
            </if>
        </where>

        group BY monitoring_time
        order by monitoring_time
    </select>

    <select id="gasEffectiveMonitoringRate" resultType="cn.cecep.talroad.domain.SEnvGasMonReal">
        SELECT
            SUM( so2_emission::NUMERIC ) AS so2_emission,
            SUM( nox_emission::NUMERIC ) nox_emission,
            SUM( no_emission::NUMERIC ) no_emission,
            SUM( no2_emission::NUMERIC ) no2_emission,
            SUM( smoke_dust_emission::NUMERIC ) smoke_dust_emission,
            SUM( pm_emission::NUMERIC ) pm_emission,
            SUM( vocs_emission::NUMERIC ) vocs_emission,
            SUM( oxygen_emission::NUMERIC ) oxygen_emission,monitoring_time
        FROM
            "public"."s_env_gas_mon_real" as gr inner join pc_factory as pf on gr.factory_id=pf.id
        <where>
            <if test="dataType != null and dataType!=''">
                and "data_type" = #{dataType}
            </if>
            <if test="monitoringStartTime != null and monitoringStartTime!=''">
                and "monitoring_time"  >=#{monitoringStartTime}
            </if>
            <if test="monitoringEndTime != null and monitoringEndTime!=''">
                and "monitoring_time"  &lt;=#{monitoringEndTime}
            </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>
            <if test="industryCode != null and industryCode != ''">
                and pf.industry_category = #{industryCode}
            </if>
        </where>

        group BY monitoring_time
        order by monitoring_time
    </select>

    <select id="numberMonitoringPoints" resultType="cn.cecep.talroad.vo.homepage.RegionalOnlineMonitoringVo">
        SELECT
        pf.country_code as districtCode,
            COUNT ( outlet.factory_id ) AS numberMonitoringPoints
        FROM
            pc_factory AS pf
                <if test="type==1">
                    INNER JOIN ( SELECT factory_id FROM pc_outlet WHERE pollutant_type = '4' AND pollutant_outlet_type = '1' OR pollutant_outlet_type = '3' ) AS outlet ON pf.ID = outlet.factory_id
                </if>
                <if test="type==2">
                    INNER JOIN ( SELECT factory_id FROM pc_outlet WHERE pollutant_type = '4' AND pollutant_outlet_type = '2' ) AS outlet ON pf.ID = outlet.factory_id
                </if>
        <where>
            <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>
        </where>
        GROUP BY
        pf.country_code,country_name
    </select>

    <select id="monitoringRate" resultType="cn.cecep.talroad.vo.homepage.RegionalOnlineMonitoringVo">
        SELECT
            district_code ,COALESCE(sum(online_monitoring_hour_num),0) as total,COALESCE(sum(online_monitoring_actual_hour_num),0) as divisor
        FROM
            s_ra_factory_transfer_rate_analysis AS ra
        <if test="type==1">
                INNER JOIN ( SELECT DISTINCT factory_id FROM pc_outlet as po inner join pc_factory as pf on pf.id=po.factory_id
            WHERE pollutant_type = '4' AND pollutant_outlet_type = '1' OR pollutant_outlet_type = '3'
            <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>
            ) AS pf ON pf.factory_id = ra.factory_id
        </if>
        <if test="type==2">
            INNER JOIN ( SELECT DISTINCT factory_id FROM pc_outlet as po inner join pc_factory as pf on pf.id=po.factory_id
            WHERE pollutant_type = '4' AND pollutant_outlet_type = '2'
            <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>
            ) AS pf ON pf.factory_id = ra.factory_id
        </if>

        where count_type='M' and statistics_time=replace(#{month},'-','')
        GROUP BY district_code

    </select>

    <select id="excessRate" resultType="cn.cecep.talroad.vo.homepage.RegionalOnlineMonitoringVo">
        <if test="type==1">
        SELECT
            pf.country_code districtCode,COALESCE(sum(ra.valid_data_count::NUMERIC),0) as total,COALESCE(sum(so2_over_count::NUMERIC)+sum(nox_over_count::NUMERIC)+sum(pm_over_count::NUMERIC)+sum(vocs_over_count::NUMERIC),0) as divisor
        FROM
            s_ra_over_rate_gas AS ra
                INNER JOIN pc_factory AS pf ON ra.factory_id = pf.ID
            where ra.statistics_type='N' and ra.statistics_time=#{month}
            <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>
        GROUP BY pf.country_code
        </if>
        <if test="type==2">
            SELECT
            pf.country_code districtCode,COALESCE(sum(ra.valid_data_count::NUMERIC),0) as total,COALESCE(sum(cod_over_count::NUMERIC)+sum(nh3_over_count::NUMERIC)+sum(tp_over_count::NUMERIC)+sum(tn_over_count::NUMERIC),0) as divisor
            FROM
            s_ra_over_rate_water AS ra
            INNER JOIN pc_factory AS pf ON ra.factory_id = pf.ID
            where ra.statistics_type='N' and ra.statistics_time=#{month}
            <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>
            GROUP BY pf.country_code
        </if>
    </select>
    <select id="selectMajorPollutantCategory" resultType="cn.cecep.talroad.domain.PcFactory">
        select
        id,
        major_pollutant_categories as majorPollutantCategories
        from pc_factory where major_pollutant_categories is not null
    </select>
    <select id="selectPcFactoryId" resultType="java.lang.String">
        select id
        from pc_factory
    </select>
    <select id="selectByFactoryAndOutletId"
        resultType="cn.cecep.talroad.vo.analysis.FactoryOutletVo">
        select
            pf.id as factoryId,
            pf.factory_name as factoryName,
            po.id as outletId,
            po.outlet_name as outletName,
            pf.country_code as cityCode,
            pf.country_name as cityName,
            pf.industry_category as industry,
            pi.indus_name as  industryName
        from pc_outlet po
                 inner join pc_factory pf on po.factory_id = pf.id
                 left join pc_industry pi ON pi.indus_code = pf.industry_category
        where po.factory_id = #{factoryId} and po.id = #{outletId}
    </select>

    <select id="gasEffectiveMonitoringRates" resultType="cn.cecep.talroad.domain.SEnvGasMonReal">
        SELECT
        SUM( so2_emission::NUMERIC ) AS so2_emission,
        SUM( nox_emission::NUMERIC ) nox_emission,
        SUM( no_emission::NUMERIC ) no_emission,
        SUM( no2_emission::NUMERIC ) no2_emission,
        SUM( smoke_dust_emission::NUMERIC ) smoke_dust_emission,
        SUM( pm_emission::NUMERIC ) pm_emission,
        SUM( vocs_emission::NUMERIC ) vocs_emission,
        SUM( oxygen_emission::NUMERIC ) oxygen_emission,monitoring_time
        FROM
        "public"."s_env_gas_mon_real" as gr inner join pc_factory as pf on gr.factory_id=pf.id
        <where>
            <if test="dataType != null and dataType!=''">
                and "data_type" = #{dataType}
            </if>
            <if test="monitoringTime != null and monitoringTime!=''">
                and "monitoring_time"  =#{monitoringTime}
            </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>
            <if test="industryCode != null and industryCode != ''">
                and pf.industry_category = #{industryCode}
            </if>
        </where>
        group BY monitoring_time
    </select>

    <select id="effectiveMonitoringRates" resultType="cn.cecep.talroad.domain.SEnvWaterMonReal">
        SELECT
        SUM( cod_emission::NUMERIC ) AS cod_emission,
        SUM( ammonia_nitrogen_emission::NUMERIC ) ammonia_nitrogen_emission,
        SUM( nitrogen_concentration_emission::NUMERIC ) nitrogen_concentration_emission,
        SUM( phosphorus_concentration_emission::NUMERIC ) phosphorus_concentration_emission,
        SUM( sewage_emission::NUMERIC ) sewage_emission,monitoring_time
        FROM
        "public"."s_env_water_mon_real" as wr inner join pc_factory as pf on wr.factory_id=pf.id
        <where>
            <if test="dataType != null and dataType!=''">
                and "data_type" = #{dataType}
            </if>
            <if test="monitoringTime != null and monitoringTime!=''">
                and "monitoring_time"  =#{monitoringTime}
            </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>
            <if test="industryCode != null and industryCode != ''">
                and pf.industry_category = #{industryCode}
            </if>
        </where>
        group BY monitoring_time
    </select>

    <select id="factoryLicensingStatus" resultType="cn.cecep.talroad.vo.PcFactoryLicensingStatusVo">
        SELECT COUNT ( 1 ) AS factoryCount,
               COUNT ( CASE WHEN license_man_type = 'REGISTER' THEN '1' END ) AS registerCount,
               COUNT ( CASE WHEN license_man_type = 'SIMPLIFY' THEN '1' END ) AS simplifyCount,
               COUNT ( CASE WHEN license_man_type = 'FOCUS' THEN '1' END ) AS focusCount
        FROM
            pc_factory
        WHERE
            license_man_type IS NOT NULL
    </select>

    <select id="countryLicensingStatus" resultType="cn.cecep.talroad.vo.PcCountryLicensingStatusVo">
        SELECT
            country_name,
            COUNT ( CASE WHEN license_man_type = 'REGISTER' THEN '1' END ) AS register,
            COUNT ( CASE WHEN license_man_type = 'SIMPLIFY' THEN '1' END ) AS simplify,
            COUNT ( CASE WHEN license_man_type = 'FOCUS' THEN '1' END ) AS focus
        FROM
            pc_factory
        WHERE
            license_man_type IS NOT NULL
          AND country_code IS NOT NULL
        GROUP BY
            country_name
    </select>
    <select id="selectAirEmissionIndustryRank" resultType="cn.cecep.talroad.vo.BMainEmissionPollutionAirVo$IndustryRank">
        SELECT
            t1.*,
            round(t1.so2_actually/t1.so2_allowable * 100, 3) AS so2_ratio,
            RANK() OVER (ORDER BY round(t1.so2_actually/t1.so2_allowable * 100, 3)) AS so2_rank,

            round(t1.nox_actually/t1.nox_allowable * 100, 3) AS nox_ratio,
            RANK() OVER (ORDER BY round(t1.nox_actually/t1.nox_allowable * 100, 3)) AS nox_rank,

            round(t1.pm_actually/t1.pm_allowable * 100, 3) AS pm_ratio,
            RANK() OVER (ORDER BY round(t1.pm_actually/t1.pm_allowable * 100, 3)) AS pm_rank,

            round(t1.vocs_actually/t1.vocs_allowable * 100, 3) AS vocs_ratio,
            RANK() OVER (ORDER BY round(t1.vocs_actually/t1.vocs_allowable * 100, 3)) AS vocs_rank
        FROM (
             SELECT
                 t2.industry_category,
                 sum(case when is_numeric(t1.actually_emitting_so2) then t1.actually_emitting_so2::NUMERIC else null end) so2_actually,
                 sum(case when is_numeric(t1.allowable_emission_so2) then t1.allowable_emission_so2::NUMERIC else null end) so2_allowable,

                 sum(case when is_numeric(t1.actually_emitting_nox) then t1.actually_emitting_nox::NUMERIC else null end) nox_actually,
                 sum(case when is_numeric(t1.allowable_emission_nox) then t1.allowable_emission_nox::NUMERIC else null end) nox_allowable,

                 sum(case when is_numeric(t1.actually_emitting_pm) then t1.actually_emitting_pm::NUMERIC else null end) pm_actually,
                 sum(case when is_numeric(t1.allowable_emission_pm) then t1.allowable_emission_pm::NUMERIC else null end) pm_allowable,

                 sum(case when is_numeric(t1.actually_emitting_vocs) then t1.actually_emitting_vocs::NUMERIC else null end) vocs_actually,
                 sum(case when is_numeric(t1.allowable_emission_vocs) then t1.allowable_emission_vocs::NUMERIC else null end) vocs_allowable
             FROM
                 b_main_emission_pollution_air t1
                 INNER JOIN pc_factory t2 ON t1.factory_id = t2."id"
                 INNER JOIN pc_industry t3 ON t2.industry_category = t3.indus_code
             <where>
                 <if test="dataSource != null and dataSource != ''">
                    AND t1.data_source = #{dataSource}
                 </if>
                 <if test="regionCodes != null and regionCodes.size() != 0">
                    AND t2.country_code IN
                    <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                        #{item,jdbcType=VARCHAR}
                    </foreach>
                 </if>
             </where>
             GROUP BY
                 t2.industry_category
         ) t1
    </select>
    <select id="selectWaterEmissionIndustryRank"
            resultType="cn.cecep.talroad.vo.BMainEmissionPollutionWaterVo$IndustryRank">
        SELECT
            t1.*,
            round(t1.cod_actually/t1.cod_allowable * 100, 3) AS cod_ratio,
            RANK() OVER (ORDER BY round(t1.cod_actually/t1.cod_allowable * 100, 3)) AS cod_rank,

            round(t1.nhh_actually/t1.nhh_allowable * 100, 3) AS nhh_ratio,
            RANK() OVER (ORDER BY round(t1.nhh_actually/t1.nhh_allowable * 100, 3)) AS nhh_rank,

            round(t1.tn_actually/t1.tn_allowable * 100, 3) AS tn_ratio,
            RANK() OVER (ORDER BY round(t1.tn_actually/t1.tn_allowable * 100, 3)) AS tn_rank,

            round(t1.tp_actually/t1.tp_allowable * 100, 3) AS tp_ratio,
            RANK() OVER (ORDER BY round(t1.tp_actually/t1.tp_allowable * 100, 3)) AS tp_rank
        FROM (
             SELECT
                 t2.industry_category,
                 sum(case when is_numeric(t1.actually_emitting_cod) then t1.actually_emitting_cod::NUMERIC else null end) cod_actually,
                 sum(case when is_numeric(t1.allowable_emission_cod) then t1.allowable_emission_cod::NUMERIC else null end) cod_allowable,

                 sum(case when is_numeric(t1.actually_emitting_nhn) then t1.actually_emitting_nhn::NUMERIC else null end) nhh_actually,
                 sum(case when is_numeric(t1.allowable_emission_nhn) then t1.allowable_emission_nhn::NUMERIC else null end) nhh_allowable,

                 sum(case when is_numeric(t1.actually_emitting_tn) then t1.actually_emitting_tn::NUMERIC else null end) tn_actually,
                 sum(case when is_numeric(t1.allowable_emission_tn) then t1.allowable_emission_tn::NUMERIC else null end) tn_allowable,

                 sum(case when is_numeric(t1.actually_emitting_tp) then t1.actually_emitting_tp::NUMERIC else null end) tp_actually,
                 sum(case when is_numeric(t1.allowable_emission_tp) then t1.allowable_emission_tp::NUMERIC else null end) tp_allowable
             FROM
                 b_main_emission_pollution_water t1
                 INNER JOIN pc_factory t2 ON t1.factory_id = t2."id"
                 INNER JOIN pc_industry t3 ON t2.industry_category = t3.indus_code
            <where>
                <if test="dataSource != null and dataSource != ''">
                    AND t1.data_source = #{dataSource}
                </if>
                <if test="regionCodes != null and regionCodes.size() != 0">
                    AND t2.country_code IN
                    <foreach collection="regionCodes" separator="," item="item" close=")" open="(">
                        #{item,jdbcType=VARCHAR}
                    </foreach>
                </if>
            </where>
             GROUP BY
                 t2.industry_category
         ) t1
    </select>

    <update id="updateBatchCoordinate" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            UPDATE pc_factory
            <set>
                <if test="item.productionLon != null and item.productionLon != ''">production_lon=#{item.productionLon},</if>
                <if test="item.productionLon == null or item.productionLon == ''">production_lon = '',</if>
                <if test="item.productionLat != null  and item.productionLat != ''">production_lat=#{item.productionLat},</if>
                <if test="item.productionLat == null or item.productionLat == ''">production_lat = '',</if>
            </set>
            WHERE id=#{item.id}
        </foreach>
    </update>

    <select id="selectPwxkWaterTreatmentLine" >
        SELECT
            *
        FROM
            b_stat_table_pwxk_water_treatment_line bswtl
        LEFT JOIN pc_factory pf ON pf.id=bswtl.factory_id
        LEFT JOIN pc_label_factory t1 ON t1.factory_id=pf.id
        <where>
            <if test="query.factoryName != null">
                AND pf.factory_name=#{query.factoryName,jdbcType=VARCHAR}
            </if>
            <if test="query.labelMap != null and query.labelMap.size()>0">
                <foreach collection="query.labelMap"  item="item" >
                   AND  t1.label_id =#{item.key} AND t1.label_value
                    <foreach collection="item.value" item="itemValue" open=" IN (" separator="," close=")">

                    </foreach>
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectId" resultType="java.lang.String">
        SELECT factory_id
        FROM pc_label_factory plf
        LEFT JOIN pc_factory pf ON pf.id=plf.factory_id
        <where>
           <if test="map != null and map.size()>0">
                <foreach collection="map.entrySet()"  item="value" index="key" open=" AND "  separator=" AND ">
                      (factory_id IN  (SELECT factory_id
                    FROM pc_label_factory plf WHERE plf.label_id =#{key}
                        <if test='key!="105"'>
                        <if test="value != null and value.size()>0">
                                AND
                            <foreach collection="value" item="itemValue"  separator=" OR " open="(" close=")">
                              #{itemValue}=  ANY (string_to_array(plf.label_value, ','))
                            </foreach>
                            )
                        </if>
                        </if>
                    <if test='key =="105"'>
                        <if test="value != null and value.size()>0">
                            AND
                            <foreach collection="value" item="itemValue"  separator=" AND " >
                                #{itemValue}=  ANY (string_to_array(plf.label_value, ','))
                            </foreach>
                            )
                        </if>
                    </if>
                    )
                </foreach>
            </if>
            <if test="factoryName != null and factoryName != ''">
              AND   pf.factory_name=#{factoryName,jdbcType=VARCHAR}
            </if>
        </where>
        GROUP BY factory_id
        ORDER BY factory_id
    </select>
</mapper>
