Controles | Unification du Référentiel Télécom

Consultation et edition

Comparer l'état du BAT

Les informations du controle se modifient directement dans le formulaire ci-dessous.

#18 Avec informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle de l'état du BAT ---*/
DROP TABLE IF EXISTS base_rop.ctrl_18;
CREATE TABLE base_rop.ctrl_18 AS 
WITH easy AS (
    SELECT
        ta.ad_batcode AS code_batiment,
        array_agg(DISTINCT la.libelle::text) AS easy_arr
    FROM t_adresse ta
    LEFT JOIN l_adresse_etat la ON ta.ad_ietat = la.code
    WHERE ta.ad_batcode IS NOT NULL
    GROUP BY ta.ad_batcode
),
ipe AS (
    SELECT
        identifiantimmeuble AS code_batiment,
        array_agg(DISTINCT etatimmeuble::text) AS ipe_arr
    FROM base_rop.ipe
    WHERE identifiantimmeuble IS NOT NULL
    GROUP BY identifiantimmeuble
),
baserop AS (
    SELECT
        lrc_idnt_immeuble AS code_batiment,
        array_agg(DISTINCT 'DEPLOYE'::text) AS baserop_arr
    FROM base_rop.baserop
    WHERE lrc_idnt_immeuble IS NOT NULL
    GROUP BY lrc_idnt_immeuble
)
SELECT
    ROW_NUMBER() OVER () AS id,
    'C18' AS id_ctr,
    'Comparer l état du BAT' AS name_ctr,
    COALESCE(cb.site_easy, cb.site_ipe, cb.site_baserop) AS site_sro,
    cb.code_batiment AS donnee_ref,
    CASE 
        WHEN 
            COALESCE((easy.easy_arr && ipe.ipe_arr OR easy.easy_arr && baserop.baserop_arr), FALSE)
            AND
            COALESCE((ipe.ipe_arr && easy.easy_arr OR ipe.ipe_arr && baserop.baserop_arr), FALSE)
            AND
            COALESCE((baserop.baserop_arr && easy.easy_arr OR baserop.baserop_arr && ipe.ipe_arr), FALSE)
        THEN 'OK'
        ELSE 'NOK'
    END AS statut,
    COALESCE(
        (easy.easy_arr && ipe.ipe_arr OR easy.easy_arr && baserop.baserop_arr),
        FALSE
    ) AS easy,
    COALESCE(array_to_string(easy.easy_arr, '; '), 'NON EXISTANT') AS easy_value,
    COALESCE(
        (ipe.ipe_arr && easy.easy_arr OR ipe.ipe_arr && baserop.baserop_arr),
        FALSE
    ) AS ipe,
    COALESCE(array_to_string(ipe.ipe_arr, '; '), 'NON EXISTANT') AS ipe_value,
    COALESCE(
        (baserop.baserop_arr && easy.easy_arr OR baserop.baserop_arr && ipe.ipe_arr),
        FALSE
    ) AS baserop,
    COALESCE(array_to_string(baserop.baserop_arr, '; '), 'NON EXISTANT') AS baserop_value
FROM base_rop.code_batiment cb
LEFT JOIN easy ON cb.code_batiment = easy.code_batiment
LEFT JOIN ipe ON cb.code_batiment = ipe.code_batiment
LEFT JOIN baserop ON cb.code_batiment = baserop.code_batiment;

Annuler