<?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.BaseRegionalControlObjectivesMapper">
  <resultMap id="BaseResultMap" type="cn.cecep.talroad.domain.BaseRegionalControlObjectives">
    <!--@mbg.generated-->
    <!--@Table base_regional_control_objectives-->
    <id column="id" jdbcType="VARCHAR" property="id" />
    <result column="country_code" jdbcType="VARCHAR" property="countryCode" />
    <result column="pollutant_type" jdbcType="VARCHAR" property="pollutantType" />
    <result column="suggested_emissions" jdbcType="NUMERIC" property="suggestedEmissions" />
    <result column="control_emissions" jdbcType="NUMERIC" property="controlEmissions" />
    <result column="create_time" jdbcType="DATE" property="createTime" />
    <result column="update_time" jdbcType="DATE" property="updateTime" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--@mbg.generated-->
    id, country_code, pollutant_type, suggested_emissions, control_emissions, create_time, 
    update_time
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
    <!--@mbg.generated-->
    select 
    <include refid="Base_Column_List" />
    from base_regional_control_objectives
    where id = #{id,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String">
    <!--@mbg.generated-->
    delete from base_regional_control_objectives
    where id = #{id,jdbcType=VARCHAR}
  </delete>
  <insert id="insert" parameterType="cn.cecep.talroad.domain.BaseRegionalControlObjectives">
    <!--@mbg.generated-->
    insert into base_regional_control_objectives (id, country_code, pollutant_type, 
      suggested_emissions, control_emissions, create_time, 
      update_time)
    values (#{id,jdbcType=VARCHAR}, #{countryCode,jdbcType=VARCHAR}, #{pollutantType,jdbcType=VARCHAR}, 
      #{suggestedEmissions,jdbcType=NUMERIC}, #{controlEmissions,jdbcType=NUMERIC}, #{createTime,jdbcType=DATE}, 
      #{updateTime,jdbcType=DATE})
  </insert>
  <insert id="insertSelective" parameterType="cn.cecep.talroad.domain.BaseRegionalControlObjectives">
    <!--@mbg.generated-->
    insert into base_regional_control_objectives
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="countryCode != null">
        country_code,
      </if>
      <if test="pollutantType != null">
        pollutant_type,
      </if>
      <if test="suggestedEmissions != null">
        suggested_emissions,
      </if>
      <if test="controlEmissions != null">
        control_emissions,
      </if>
      <if test="createTime != null">
        create_time,
      </if>
      <if test="updateTime != null">
        update_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=VARCHAR},
      </if>
      <if test="countryCode != null">
        #{countryCode,jdbcType=VARCHAR},
      </if>
      <if test="pollutantType != null">
        #{pollutantType,jdbcType=VARCHAR},
      </if>
      <if test="suggestedEmissions != null">
        #{suggestedEmissions,jdbcType=NUMERIC},
      </if>
      <if test="controlEmissions != null">
        #{controlEmissions,jdbcType=NUMERIC},
      </if>
      <if test="createTime != null">
        #{createTime,jdbcType=DATE},
      </if>
      <if test="updateTime != null">
        #{updateTime,jdbcType=DATE},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="cn.cecep.talroad.domain.BaseRegionalControlObjectives">
    <!--@mbg.generated-->
    update base_regional_control_objectives
    <set>
      <if test="countryCode != null">
        country_code = #{countryCode,jdbcType=VARCHAR},
      </if>
      <if test="pollutantType != null">
        pollutant_type = #{pollutantType,jdbcType=VARCHAR},
      </if>
      <if test="suggestedEmissions != null">
        suggested_emissions = #{suggestedEmissions,jdbcType=NUMERIC},
      </if>
      <if test="controlEmissions != null">
        control_emissions = #{controlEmissions,jdbcType=NUMERIC},
      </if>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=DATE},
      </if>
      <if test="updateTime != null">
        update_time = #{updateTime,jdbcType=DATE},
      </if>
    </set>
    where id = #{id,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="cn.cecep.talroad.domain.BaseRegionalControlObjectives">
    <!--@mbg.generated-->
    update base_regional_control_objectives
    set country_code = #{countryCode,jdbcType=VARCHAR},
      pollutant_type = #{pollutantType,jdbcType=VARCHAR},
      suggested_emissions = #{suggestedEmissions,jdbcType=NUMERIC},
      control_emissions = #{controlEmissions,jdbcType=NUMERIC},
      create_time = #{createTime,jdbcType=DATE},
      update_time = #{updateTime,jdbcType=DATE}
    where id = #{id,jdbcType=VARCHAR}
  </update>
  <insert id="batchInsert" parameterType="map">
    <!--@mbg.generated-->
    insert into base_regional_control_objectives
    (id, country_code, pollutant_type, suggested_emissions, control_emissions, create_time, 
      update_time)
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.id,jdbcType=VARCHAR}, #{item.countryCode,jdbcType=VARCHAR}, #{item.pollutantType,jdbcType=VARCHAR}, 
        #{item.suggestedEmissions,jdbcType=NUMERIC}, #{item.controlEmissions,jdbcType=NUMERIC}, 
        #{item.createTime,jdbcType=DATE}, #{item.updateTime,jdbcType=DATE})
    </foreach>
  </insert>
  <update id="updateBatchSelective" parameterType="java.util.List">
    <!--@mbg.generated-->
    update base_regional_control_objectives
    <trim prefix="set" suffixOverrides=",">
      <trim prefix="country_code = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.countryCode != null">
            when id = #{item.id,jdbcType=VARCHAR} then #{item.countryCode,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="pollutant_type = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.pollutantType != null">
            when id = #{item.id,jdbcType=VARCHAR} then #{item.pollutantType,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="suggested_emissions = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.suggestedEmissions != null">
            when id = #{item.id,jdbcType=VARCHAR} then #{item.suggestedEmissions,jdbcType=NUMERIC}
          </if>
        </foreach>
      </trim>
      <trim prefix="control_emissions = case" suffix="end,">
        <foreach collection="list" index="index" item="item">

            when id = #{item.id,jdbcType=VARCHAR} then #{item.controlEmissions,jdbcType=NUMERIC}

        </foreach>
      </trim>
      <trim prefix="create_time = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.createTime != null">
            when id = #{item.id,jdbcType=VARCHAR} then #{item.createTime,jdbcType=DATE}
          </if>
        </foreach>
      </trim>
      <trim prefix="update_time = case" suffix="else now() end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.updateTime != null">
            when id = #{item.id,jdbcType=VARCHAR} then #{item.updateTime,jdbcType=DATE}
          </if>
        </foreach>
      </trim>
    </trim>
    where id in
    <foreach close=")" collection="list" item="item" open="(" separator=", ">
      #{item.id,jdbcType=VARCHAR}
    </foreach>
  </update>
  <update id="updateBatchSuggestedEmissions">
    <foreach collection="updataList" separator=";" item="item">
      UPDATE base_regional_control_objectives
      SET suggested_emissions = #{item.suggestedEmissions} , update_time = #{item.updateTime}
      WHERE id = #{item.id}
    </foreach>
  </update>
  <select id="selectListPage" resultType="cn.cecep.talroad.vo.BaseRegionalControlObjectivesVo">
    SELECT
    plv.arguments_code AS countryCode,
    plv.arguments_name AS countryName,
    <!--CASE
      pp.poll_code
      WHEN 'A34013' THEN
      ( SUM ( ( CASE bmepa.allowable_emission_pm WHEN '' THEN 0 WHEN NULL THEN 0 ELSE bmepa.allowable_emission_pm :: NUMERIC END ) ) )
      WHEN 'A21026' THEN
      SUM ( ( CASE bmepa.allowable_emission_so2 WHEN '' THEN 0 WHEN NULL THEN 0 ELSE bmepa.allowable_emission_so2 :: NUMERIC END ) )
      WHEN 'A21002' THEN
      SUM ( ( CASE bmepa.allowable_emission_nox WHEN '' THEN 0 WHEN NULL THEN 0 ELSE bmepa.allowable_emission_nox :: NUMERIC END ) ) ELSE NULL
    END suggested_emissions,-->
    MAX(brco.suggested_emissions)suggested_emissions,
    pp.poll_code AS pollutantType,
    pp.poll_name,
    MAX ( brco.control_emissions ) AS controlEmissions,
    MAX(brco.id) AS id
    FROM
    pc_label_value AS plv
    LEFT JOIN pc_factory pf ON plv.arguments_code = pf.country_code
<!--
    LEFT JOIN b_main_emission_pollution_air bmepa ON bmepa.factory_id = pf.ID
-->
    LEFT JOIN pc_pollutant pp ON pp.is_gas_type = '1'
    LEFT JOIN base_regional_control_objectives brco ON brco.pollutant_type = pp.poll_code
    AND brco.country_code = plv.arguments_code
    WHERE
    plv.label_id = '5'
<!--    and bmepa.poll_year=to_char(now(),'yyyy')-->
      <if test="query.id != null">
      AND  brco.id= #{query.id,jdbcType=VARCHAR}
      </if>
      <if test="query.countryCode != null">
        AND  brco.country_code = #{query.countryCode,jdbcType=VARCHAR}
      </if>
      <if test="query.pollutantType != null and query.pollutantType.trim() != ''">
        AND  pp.poll_code = #{query.pollutantType,jdbcType=VARCHAR}
      </if>
      <if test="query.suggestedEmissions != null">
        AND brco.suggested_emissions = #{query.suggestedEmissions,jdbcType=NUMERIC}
      </if>
      <if test="query.controlEmissions != null">
        AND brco.control_emissions = #{query.controlEmissions,jdbcType=NUMERIC}
      </if>
    GROUP BY
    plv.arguments_code,
    plv.arguments_name,
    pp.poll_code,
    pp.poll_name,
    plv.order_num
    ORDER BY
    plv.order_num,
    pp.poll_code
  </select>
    <select id="selectRegionAdviseEmissions" resultType="cn.cecep.talroad.domain.BaseRegionalControlObjectives">
        select
            t3.country_code,
            t1.pollutant_type,
            <!-- 将监测点的小时建议排放量求和作为区域的小时建议排放量 -->
            sum(t1.advice_emission_avg) as suggested_emissions
        from base_control_concent_standards t1
        inner join pc_factory t3 on t1.factory_id = t3.id
        WHERE t1.include_calculations = '1'
        GROUP BY t3.country_code, t1.pollutant_type
    </select>
</mapper>