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

Consultation et edition

Comparatif du BAT en fonction du PM affecté

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

#16 Avec informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle PM en fonction du BAT ---*/
DROP TABLE IF EXISTS base_rop.ctrl_16;
CREATE TABLE base_rop.ctrl_16 AS 
WITH easy AS (
    SELECT
        code_batiment,
        array_agg(DISTINCT site_easy::text) AS easy_arr
    FROM base_rop.code_batiment
    WHERE site_easy IS NOT NULL
    GROUP BY code_batiment
),
ipe AS (
    SELECT
        code_batiment,
        array_agg(DISTINCT site_ipe::text) AS ipe_arr
    FROM base_rop.code_batiment
    WHERE site_ipe IS NOT NULL
    GROUP BY code_batiment
),
baserop AS (
    SELECT
        code_batiment,
        array_agg(DISTINCT site_baserop::text) AS baserop_arr
    FROM base_rop.code_batiment
    WHERE site_baserop IS NOT NULL
    GROUP BY code_batiment
)
SELECT
    ROW_NUMBER() OVER () AS id,
    'C16' AS id_ctr,
    'Comparatif du BAT en fonction du PM affecté' 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