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

    <select id="selectFacilityInformationManagementListByParams"
            resultType="cn.cecep.talroad.vo.FacilityInformationManagementVO">

        SELECT * FROM (
            SELECT
            bd.device_id as deviceId,
            bd.name as deviceName,
            bd.work_step as workStep,
            string_agg(bd2.pollutant_name, ',' ORDER BY bd2.pollutant_code ASC ) AS pollutantName,
            string_agg(bd2.pollutant_code, ',' ORDER BY bd2.pollutant_code ASC ) AS pollutantCode
            FROM
            bas_device bd
            LEFT JOIN bas_device bd2 ON bd2.pollutant_code  = ANY(string_to_array(bd.elecdcs_pollutant_relations, ','))
            AND bd2.factory_id = #{dto.factoryId}
            <if test="dto.queryType != null">
                <if test='dto.queryType == "1"'>
                    AND bd2.device_type = '0'
                </if>
                <if test='dto.queryType == "2"'>
                    AND bd2.device_type = '1'
                </if>
            </if>
            <where>
                bd.factory_id = #{dto.factoryId} AND bd.is_status = '1'
                <if test="dto.deviceName != null and dto.deviceName != ''">
                    AND bd.name LIKE CONCAT('%',#{dto.deviceName},'%')
                </if>
                <if test="dto.pollutantCode != null and dto.pollutantCode != ''">
                    AND bd.elecdcs_pollutant_relations LIKE CONCAT('%',#{dto.pollutantCode},'%')
                </if>
                <if test="dto.deviceIds != null">
                    AND bd.device_id IN
                    <foreach collection="dto.deviceIds" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="dto.queryType != null">
                    <if test='dto.queryType == "1"'>
                        AND bd.type = 'ELEC'
                        AND bd.device_type = '0'
                    </if>
                    <if test='dto.queryType == "2"'>
                        AND bd.type = 'ELEC'
                        AND bd.device_type = '1'
                    </if>
                    <if test='dto.queryType == "3"'>
                        AND bd.industry IN ( SELECT UNNEST(string_to_array( pf.industry_category, ',' )) industry_category FROM pc_factory pf WHERE pf.id = #{dto.factoryId})
                    </if>
                </if>
            </where>
        GROUP BY bd.device_id
        order by bd.name
        ) tmp
        <where>
            <if test="dto.pollutantName != null and dto.pollutantName != ''">
                AND tmp.pollutantName LIKE CONCAT('%',#{dto.pollutantName},'%')
            </if>
        </where>
        order by deviceName
    </select>

    <select id="selectPollutantDeviceList" resultType="cn.cecep.talroad.domain.BasDevice">
        SELECT
            pollutant_code,
            pollutant_name
        FROM
            bas_device
        WHERE
            factory_id = #{factoryId} AND industry = #{type} AND device_type = #{deviceType} AND is_status = '1'
    </select>

    <update id="updateDeviceAndPollutantCodeList">
        UPDATE bas_device
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="elecdcs_pollutant_relation = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when device_id = #{item.deviceId,jdbcType=VARCHAR} then #{item.pollutantCodeOne,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="elecdcs_pollutant_relations = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when device_id = #{item.deviceId,jdbcType=VARCHAR} then #{item.pollutantCode,jdbcType=VARCHAR}
                </foreach>
            </trim>
        </trim>
        WHERE device_id IN
        <foreach close=")" collection="list" item="item" open="(" separator=", ">
            #{item.deviceId,jdbcType=VARCHAR}
        </foreach>
    </update>

    <update id="cancelDeviceAndPollutantCodeList">
        UPDATE bas_device
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="elecdcs_pollutant_relation = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when device_id = #{item.deviceId,jdbcType=VARCHAR} then null
                </foreach>
            </trim>
            <trim prefix="elecdcs_pollutant_relations = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when device_id = #{item.deviceId,jdbcType=VARCHAR} then null
                </foreach>
            </trim>
        </trim>
        WHERE device_id IN
        <foreach close=")" collection="list" item="item" open="(" separator=", ">
            #{item.deviceId,jdbcType=VARCHAR}
        </foreach>
    </update>

    <select id="selectFacilityInformationManagementSearchListByParams"
            resultType="cn.cecep.talroad.vo.FacilityInformationManagementVO">
        SELECT
            bd.device_id as deviceId,
            bd.name as deviceName
        FROM
            bas_device bd
        <where>
             bd.factory_id = #{factoryId}  AND bd.is_status = '1'
            <if test="queryType != null">
                <if test='queryType == "1"'>
                    AND bd.type = 'ELEC'
                    AND bd.device_type = '0'
                </if>
                <if test='queryType == "2"'>
                    AND bd.type = 'ELEC'
                    AND bd.device_type = '1'
                </if>
                <if test='queryType == "3"'>
                    AND bd.industry IN ( SELECT UNNEST(string_to_array( pf.industry_category, ',' )) industry_category FROM pc_factory pf WHERE pf.id = #{factoryId})
                </if>
            </if>
        </where>
    </select>
</mapper>
