CentralogEditeurRepository.php

WITH EDITEUR_SAUVE AS
         (
             SELECT cdeditr, MAX(dtsave), max(cdmaj) AS cdmaj FROM ever_editeur_sauve
             WHERE nummarqueeditoriale IS NULL
               AND dtsave >= :dateDernierTraitement
             GROUP BY cdeditr
         ),
CATEGORIE_EDITEUR AS (
    SELECT string_agg( DISTINCT code_produit_fdn,',') AS categorie, es.cdeditr
    FROM  EDITEUR_SAUVE es
    LEFT JOIN produit p
             ON p.cdeditr = es.cdeditr
    LEFT JOIN famille f
        ON f.cdfam = p.cdfam
    LEFT JOIN categorie_famille cf
        ON cf.cdcategfam = f.cdcategfam
    GROUP BY es.cdeditr
)
SELECT DISTINCT TRIM(ees.cdeditr) AS code,
                e.nomeditrlong AS libelle,
                CE.categorie AS categorie,
                a.cdimail AS email,
                f.libelle_fournisseur AS nom_fournisseur,
                ees.cdmaj = 'S' AS a_supprimer,
                a.adrlgn1 AS adresse_ligne_1,
                a.adrlgn2 AS adresse_ligne_2,
                a.adrlgn3 AS adresse_ligne_3,
                a.adrlgn4 AS adresse_ligne_4,
                a.nomburdis AS ville,
                a.cdpostal AS code_postal,
                a.numtel AS telephone,
                p.libpays as pays,
                COALESCE(MAX(numero_sequence), 0) AS numero_sequence,
                (fde.code_editeur IS NOT NULL) AS a_supprimer
FROM editeur_sauve ees
     INNER JOIN resadec.public.editeur e
            ON ees.cdeditr = e.cdeditr
            AND NULLIF(e.nomeditrlong,'') IS NOT NULL
     LEFT JOIN adresse a
            ON a.numadr = e.numadr
     LEFT JOIN pays p
            ON p.cdpays = a.cdpays
     LEFT JOIN four_editr fe
            ON fe.cdeditr = e.cdeditr
            AND CURRENT_DATE BETWEEN dtdebvali1 AND dtfinval
            AND cdfourprinc
     LEFT JOIN fdn.decitre_fournisseur f
            ON f.cdfour = fe.cdfour
            ANd f.libelle_fournisseur IS NOT NULL
     LEFT JOIN fdn.decitre_editeur fde
               ON fde.libelle_editeur_reference = LEFT(e.nomeditrlong,20)
     LEFT JOIN CATEGORIE_EDITEUR CE
        ON CE.cdeditr = ees.cdeditr
        AND CE.categorie IS NOT NULL
GROUP BY ees.cdeditr,
         e.nomeditrlong,
         f.libelle_fournisseur,
        ees.cdmaj = 'S',
        CE.categorie,
        a.cdimail,
        a.adrlgn1,
        a.adrlgn2,
        a.adrlgn3,
        a.adrlgn4,
        a.nomburdis,
        a.cdpostal,
        a.numtel,
        p.libpays,
        (fde.code_editeur IS NOT NULL)
ORDER BY 2;