CentralogCollectionRepository.php

WITH COLLECTION_SAUVE AS (
        SELECT numcollec, MAX(cdmaj) AS cdmaj, MAX(dtsave)
        FROM ever_collection_sauve ecs
        WHERE ecs.dtsave >= :dateDernierTraitement
        GROUP BY numcollec
    )
SELECT DISTINCT ecs.numcollec as numero_collection,
                TRIM(c.nomcollec) AS libelle_collection,
                COALESCE(cf.code_produit_fdn,'LIVRE') AS code_produit,
                COALESCE(fdc.numero_sequence,0)::VARCHAR AS numero_sequence,
                fdc.numero_collection IS NOT NULL AS deja_envoyee,
                ecs.cdmaj = 'S' AS a_supprimer
FROM COLLECTION_SAUVE ecs
         INNER JOIN collection c
                    ON c.numcollec = ecs.numcollec
                    AND NULLIF(c.nomcollec,'') IS NOT NULL
         LEFT JOIN famille f
                   ON f.cdfam = c.cdfam
         LEFT JOIN categorie_famille cf
                   ON cf.cdcategfam = f.cdcategfam
         LEFT JOIN fdn.decitre_collection fdc
                   ON fdc.numero_collection = c.numcollec
ORDER BY TRIM(c.nomcollec), ecs.numcollec;