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

    <update id="updateOrganizProDevice">
        UPDATE b_main_production_organiz_gas ttt1
        SET pro_facility_id = ttt2.device_ids
        FROM
            (
            SELECT
                tt1.factory_id,
                tt1.device_type,
                tt1.elecdcs_pollutant_relation,
                string_agg ( tt1.device_id, ',' ) device_ids
            FROM
                (
                SELECT
                    t1."id" factory_id,
                    t2.device_id,
                    UNNEST ( string_to_array( t2.elecdcs_pollutant_relations, ',' ) ) elecdcs_pollutant_relation,
                    device_type
                FROM
                    pc_factory t1
                    LEFT JOIN bas_device t2 ON t1."id" = t2.factory_id
                WHERE
                    t1.factory_source IS NULL
                    AND t1.license_man_type != 'REGISTER'
                    AND t2.industry != 'XK'
                    AND t2.elecdcs_pollutant_relation IS NOT NULL
                ) tt1
                WHERE
                tt1.device_type = '0'
            GROUP BY
                tt1.factory_id,
                tt1.device_type,
                tt1.elecdcs_pollutant_relation
            ) ttt2
        WHERE
            ttt1.factory_id = ttt2.factory_id
            AND ttt1.pro_facility_code = ttt2.elecdcs_pollutant_relation
    </update>

    <update id="updateOrganizCepDevice">
        UPDATE b_main_production_organiz_gas ttt1
        SET cep_facility_id = ttt2.device_ids
        FROM
            (
            SELECT
                tt1.factory_id,
                tt1.device_type,
                tt1.elecdcs_pollutant_relation,
                string_agg ( tt1.device_id, ',' ) device_ids
            FROM
                (
                SELECT
                    t1."id" factory_id,
                    t2.device_id,
                    UNNEST ( string_to_array( t2.elecdcs_pollutant_relations, ',' ) ) elecdcs_pollutant_relation,
                    device_type
                FROM
                    pc_factory t1
                    LEFT JOIN bas_device t2 ON t1."id" = t2.factory_id
                WHERE
                    t1.factory_source IS NULL
                    AND t1.license_man_type != 'REGISTER'
                    AND t2.industry != 'XK'
                    AND t2.elecdcs_pollutant_relation IS NOT NULL
                ) tt1
                WHERE
                tt1.device_type = '1'
            GROUP BY
                tt1.factory_id,
                tt1.device_type,
                tt1.elecdcs_pollutant_relation
            ) ttt2
        WHERE
            ttt1.factory_id = ttt2.factory_id
            AND ttt1.cep_facility_code = ttt2.elecdcs_pollutant_relation
    </update>

</mapper>
