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

    <resultMap type="cn.cecep.talroad.domain.BMainEmissionPollutionWater" id="BMainEmissionPollutionWaterResult">
        <result property="id"    column="id"    />
        <result property="factoryId"    column="factory_id"    />
        <result property="pollYear"    column="poll_year"    />
        <result property="pollutionCategory"    column="pollution_category"    />
        <result property="dataSource"    column="data_source"    />
        <result property="actuallyEmittingCod"    column="actually_emitting_cod"    />
        <result property="allowableEmissionCod"    column="allowable_emission_cod"    />
        <result property="actuallyEmittingNhn"    column="actually_emitting_nhn"    />
        <result property="allowableEmissionNhn"    column="allowable_emission_nhn"    />
        <result property="actuallyEmittingTn"    column="actually_emitting_tn"    />
        <result property="allowableEmissionTn"    column="allowable_emission_tn"    />
        <result property="actuallyEmittingTp"    column="actually_emitting_tp"    />
        <result property="allowableEmissionTp"    column="allowable_emission_tp"    />
        <result property="groupByField"    column="groupByField"    />
        <result property="indusName"    column="indus_name"    />
        <result property="industryCategory"    column="industry_category"    />
    </resultMap>


    <sql id="selectBMainEmissionPollutionWaterVo">
        select id, factory_id, poll_year, pollution_category, data_source, actually_emitting_cod, allowable_emission_cod, actually_emitting_nhn, allowable_emission_nhn, actually_emitting_tn, allowable_emission_tn, actually_emitting_tp, allowable_emission_tp from b_main_emission_pollution_water
    </sql>

    <select id="selectBMainEmissionPollutionWaterList" parameterType="cn.cecep.talroad.domain.BMainEmissionPollutionWater" resultMap="BMainEmissionPollutionWaterResult">
        <include refid="selectBMainEmissionPollutionWaterVo"/>
        <where>
            <if test="factoryId != null  and factoryId != ''"> and factory_id = #{factoryId}</if>
            <if test="pollYear != null  and pollYear != ''"> and poll_year = #{pollYear}</if>
            <if test="pollutionCategory != null  and pollutionCategory != ''"> and pollution_category = #{pollutionCategory}</if>
            <if test="dataSource != null  and dataSource != ''"> and data_source = #{dataSource}</if>
            <if test="actuallyEmittingCod != null  and actuallyEmittingCod != ''"> and actually_emitting_cod = #{actuallyEmittingCod}</if>
            <if test="allowableEmissionCod != null  and allowableEmissionCod != ''"> and allowable_emission_cod = #{allowableEmissionCod}</if>
            <if test="actuallyEmittingNhn != null  and actuallyEmittingNhn != ''"> and actually_emitting_nhn = #{actuallyEmittingNhn}</if>
            <if test="allowableEmissionNhn != null  and allowableEmissionNhn != ''"> and allowable_emission_nhn = #{allowableEmissionNhn}</if>
            <if test="actuallyEmittingTn != null  and actuallyEmittingTn != ''"> and actually_emitting_tn = #{actuallyEmittingTn}</if>
            <if test="allowableEmissionTn != null  and allowableEmissionTn != ''"> and allowable_emission_tn = #{allowableEmissionTn}</if>
            <if test="actuallyEmittingTp != null  and actuallyEmittingTp != ''"> and actually_emitting_tp = #{actuallyEmittingTp}</if>
            <if test="allowableEmissionTp != null  and allowableEmissionTp != ''"> and allowable_emission_tp = #{allowableEmissionTp}</if>
        </where>
    </select>

    <select id="selectEmissionDataList" resultMap="BMainEmissionPollutionWaterResult">
        SELECT
            pf."id"   AS  factory_id,
            MAX ( pi.indus_name )       AS indus_name,
            <if test="groupByIndustry"> pf.industry_category  AS  industry_category, </if>
            <if test="groupByField != null and groupByField != ''"> ${groupByField} AS groupByField, </if>
            SUM ( CASE WHEN is_numeric (actually_emitting_cod) THEN actually_emitting_cod::NUMERIC ELSE 0 END )    AS actually_emitting_cod,
            SUM ( CASE WHEN is_numeric (allowable_emission_cod) THEN allowable_emission_cod::NUMERIC ELSE 0 END )    AS allowable_emission_cod,
            SUM ( CASE WHEN is_numeric (actually_emitting_nhn) THEN actually_emitting_nhn::NUMERIC ELSE 0 END )    AS actually_emitting_nhn,
            SUM ( CASE WHEN is_numeric (allowable_emission_nhn) THEN allowable_emission_nhn::NUMERIC ELSE 0 END )    AS allowable_emission_nhn,
            SUM ( CASE WHEN is_numeric (actually_emitting_tn) THEN actually_emitting_tn::NUMERIC ELSE 0 END )    AS actually_emitting_tn,
            SUM ( CASE WHEN is_numeric (allowable_emission_tn) THEN allowable_emission_tn::NUMERIC ELSE 0 END )    AS allowable_emission_tn,
            SUM ( CASE WHEN is_numeric (actually_emitting_tp) THEN actually_emitting_tp::NUMERIC ELSE 0 END )    AS actually_emitting_tp,
            SUM ( CASE WHEN is_numeric (allowable_emission_tp) THEN allowable_emission_tp::NUMERIC ELSE 0 END ) AS allowable_emission_tp
        FROM
            pc_factory pf
            LEFT JOIN pc_industry pi ON pf.industry_category = pi.indus_code
            LEFT JOIN b_main_emission_pollution_water bmepw  on bmepw.factory_id = pf.id
        <where>
            data_source = #{dataSource}
            AND poll_year = #{year}
            <if test="factoryId != null  and factoryId != ''"> AND factory_id = #{factoryId}</if>
            <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>
        GROUP BY
            pf."id"
            <if test="groupByIndustry"> , pf.industry_category </if>
            <if test="groupByField != null and groupByField != ''">, ${groupByField} </if>
    </select>
    <select id="selectEmissioinDataByYear" resultMap="BMainEmissionPollutionWaterResult">
        select * from b_main_emission_pollution_water where poll_year = #{lastYear} and data_source  ='系统核算'
    </select>
</mapper>
