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

    <resultMap type="cn.cecep.talroad.domain.BMainHazardousWasteOutput" id="BMainHazardousWasteOutputResult">
        <result property="id"    column="id"    />
        <result property="factoryId"    column="factory_id"    />
        <result property="particularYear"    column="particular_year"    />
        <result property="particularMonth"    column="particular_month"    />
        <result property="materialsType"    column="materials_type"    />
        <result property="materialsCode"    column="materials_code"    />
        <result property="materialsName"    column="materials_name"    />
        <result property="materialsLastMonth"    column="materials_last_month"    />
        <result property="materialsOutput"    column="materials_output"    />
        <result property="materialsAllowance"    column="materials_allowance"    />
        <result property="materialsAsFollows"    column="materials_as_follows"    />
        <result property="materialsUse"    column="materials_use"    />
        <result property="materialsRepertory"    column="materials_repertory"    />
        <result property="materialsRepertoryYear"    column="materials_repertory_year"    />
        <result property="unit"    column="unit"    />
    </resultMap>


    <sql id="selectBMainHazardousWasteOutputVo">
        select id, factory_id, particular_year, particular_month, materials_type, materials_code, materials_name, materials_last_month, materials_output, materials_allowance, materials_as_follows, materials_use, materials_repertory, materials_repertory_year, unit from b_main_hazardous_waste_output
    </sql>

    <select id="selectBMainHazardousWasteOutputList" parameterType="cn.cecep.talroad.domain.BMainHazardousWasteOutput" resultMap="BMainHazardousWasteOutputResult">
        <include refid="selectBMainHazardousWasteOutputVo"/>
        <where>
            <if test="factoryId != null  and factoryId != ''"> and factory_id = #{factoryId}</if>
            <if test="particularYear != null  and particularYear != ''"> and particular_year = #{particularYear}</if>
            <if test="particularMonth != null  and particularMonth != ''"> and particular_month = #{particularMonth}</if>
            <if test="materialsType != null  and materialsType != ''"> and materials_type = #{materialsType}</if>
            <if test="materialsCode != null  and materialsCode != ''"> and materials_code = #{materialsCode}</if>
            <if test="materialsName != null  and materialsName != ''"> and materials_name like concat('%', #{materialsName}, '%')</if>
            <if test="materialsLastMonth != null  and materialsLastMonth != ''"> and materials_last_month = #{materialsLastMonth}</if>
            <if test="materialsOutput != null  and materialsOutput != ''"> and materials_output = #{materialsOutput}</if>
            <if test="materialsAllowance != null  and materialsAllowance != ''"> and materials_allowance = #{materialsAllowance}</if>
            <if test="materialsAsFollows != null  and materialsAsFollows != ''"> and materials_as_follows = #{materialsAsFollows}</if>
            <if test="materialsUse != null  and materialsUse != ''"> and materials_use = #{materialsUse}</if>
            <if test="materialsRepertory != null  and materialsRepertory != ''"> and materials_repertory = #{materialsRepertory}</if>
            <if test="materialsRepertoryYear != null  and materialsRepertoryYear != ''"> and materials_repertory_year = #{materialsRepertoryYear}</if>
        </where>
    </select>

    <select id="selectTop5WasteByIndustry" resultType="cn.cecep.talroad.vo.IndustrialHazardousWasteVo">
        SELECT
            materials_code,
            pf.industry_category,
            MAX ( bmhwo.materials_name ) materialsName,
            MAX ( bmhwo.materials_type ) materialsType,
            SUM ( ( CASE materials_last_month WHEN '' THEN 0 WHEN NULL THEN 0 ELSE materials_last_month :: NUMERIC END ) ) materialsStorage,
            SUM ( ( CASE materials_as_follows WHEN '' THEN 0 WHEN NULL THEN 0 ELSE materials_as_follows :: NUMERIC END ) ) materialsAsFollows,
            SUM ( ( CASE materials_use WHEN '' THEN 0 WHEN NULL THEN 0 ELSE materials_use :: NUMERIC END ) ) materialsUse,
            RANK() OVER ( PARTITION BY pf.industry_category ORDER BY SUM (
        ( CASE materials_last_month WHEN '' THEN 0 WHEN NULL THEN 0 ELSE materials_last_month :: NUMERIC END ) + ( CASE materials_as_follows WHEN '' THEN 0 WHEN NULL THEN 0 ELSE materials_as_follows :: NUMERIC END ) + ( CASE materials_use WHEN '' THEN 0 WHEN NULL THEN 0 ELSE materials_use :: NUMERIC END )
        ) DESC  )       pollutantEmissionsIndustryRanking
        FROM
            b_main_hazardous_waste_output bmhwo
            LEFT JOIN pc_factory pf ON bmhwo.factory_id = pf."id"
        WHERE industry_category = #{industry}
        <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.industry_category, materials_code
        LIMIT 5
    </select>

    <select id="selectWasteListGroupByIndustry" resultType="cn.cecep.talroad.vo.IndustrialHazardousWasteVo">

        SELECT
            tmp.particular_year             AS particularYear ,
            tmp.particular_month            AS particularMonth,
            tmp.materials_code              materialsCode,
            bmhwo.materials_name            materialsName,
            bmhwo.materials_type            materialsType,
            materialsStorage,
            materialsAsFollows,
            materialsUse
        FROM (
            SELECT
                particular_year,
                particular_month,
                materials_code                                              ,
        SUM ( ( CASE materials_last_month WHEN '' THEN 0 WHEN NULL THEN 0 ELSE materials_last_month :: NUMERIC END ) ) materialsStorage,
        SUM ( ( CASE materials_as_follows WHEN '' THEN 0 WHEN NULL THEN 0 ELSE materials_as_follows :: NUMERIC END ) ) materialsAsFollows,
        SUM ( ( CASE materials_use WHEN '' THEN 0 WHEN NULL THEN 0 ELSE materials_use :: NUMERIC END ) ) materialsUse,
            FROM
                b_main_hazardous_waste_output bmhwo
                    LEFT JOIN pc_factory pf ON bmhwo.factory_id = pf."id"
            WHERE ( particular_year <![CDATA[ >= ]]> #{startYear} AND particular_month <![CDATA[ >= ]]> #{startMonth} ) OR
                 ( particular_year <![CDATA[ <= ]]> #{endYear} AND particular_month <![CDATA[ <= ]]> #{endMonth} )
                <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>
            GROUP BY materials_code, particular_year, particular_month ) tmp
        LEFT JOIN b_main_hazardous_waste_output bmhwo ON tmp.materials_code = bmhwo.materials_code
    </select>
</mapper>
