CentralogFournisseurRepository.php

WITH FOURNISSURS_SAUVE AS (
    SELECT cdfour, MAX(cdmaj) AS cdmaj, MAX(dtsave)
    FROM ever_fournisseur_sauve
    WHERE dtsave >= :dateDernierTraitement
    GROUP BY cdfour
),
     FOURNISSEURS AS (SELECT DISTINCT efs.cdfour,
                                      f.nomfour,
                                      f.gencodfour,
                                      de.cddevisefdn,
                                      efs.cdmaj = 'S'
                                          OR
                                      NOT EXISTS(SELECT 1
                                                 FROM pdt_four
                                                 WHERE CURRENT_DATE BETWEEN dtdebvali1 AND dtfinval
                                                   AND cdfourprinc
                                                   AND cdfour = efs.cdfour)
                                          AS a_supprimer
                      FROM FOURNISSURS_SAUVE efs
                               INNER JOIN fournisseur f
                                          ON f.cdfour = efs.cdfour
                                              AND f.cdfour <> 'A010546'
                                              AND f.nomfour IS NOT NULL
                               LEFT JOIN devise AS de ON de.cddevisefdn = f.cddevise),
     ADR_CDE AS (SELECT F.cdfour     as four_cde,
                        pa.cdpays    AS cdpays_cde,
                        pa.libpays   AS libpays_cde,
                        ad.numtel    AS numtel_cde,
                        ad.nomburdis AS nomburdis_cde,
                        ad.cdpostal  AS cdpostal_cde,
                        ad.cdimail   AS cdimail_cde,
                        ad.cdtypadr  AS cdtypadr_cde,
                        ad.adrlgn1   AS adrlgn1_cde,
                        ad.adrlgn2   AS adrlgn2_cde,
                        ad.adrlgn3   AS adrlgn3_cde,
                        ad.adrlgn4   AS adrlgn4_cde
                 FROM FOURNISSEURS F
                          INNER JOIN adr_four AS af
                                     ON af.cdfour = F.cdfour AND CURRENT_DATE BETWEEN dtdebvali1 AND dtfinval
                          INNER JOIN adresse ad ON ad.numadr = af.numadr AND ad.cdtypadr = 'CD'
                          INNER JOIN pays pa ON pa.cdpays = ad.cdpays),
     ADR_RET AS (SELECT F.cdfour     as four_ret,
                        pa.cdpays    AS cdpays_ret,
                        pa.libpays   AS libpays_ret,
                        ad.numtel    AS numtel_ret,
                        ad.nomburdis AS nomburdis_ret,
                        ad.cdpostal  AS cdpostal_ret,
                        ad.cdimail   AS cdimail_ret,
                        ad.cdtypadr  AS cdtypadr_ret,
                        ad.adrlgn1   AS adrlgn1_ret,
                        ad.adrlgn2   AS adrlgn2_ret,
                        ad.adrlgn3   AS adrlgn3_ret,
                        ad.adrlgn4   AS adrlgn4_ret
                 FROM FOURNISSEURS F
                          INNER JOIN adr_four AS af
                                     ON af.cdfour = F.cdfour AND CURRENT_DATE BETWEEN dtdebvali1 AND dtfinval
                          INNER JOIN adresse ad ON ad.numadr = af.numadr AND ad.cdtypadr = 'RT'
                          INNER JOIN pays pa ON pa.cdpays = ad.cdpays)
SELECT DISTINCT
    F.cdfour,
    F.nomfour,
    F.gencodfour,
    F.cddevisefdn,
    max(fdf.numseq) AS numero_sequence,
    cdpays_cde,
    libpays_cde,
    numtel_cde,
    nomburdis_cde,
    cdpostal_cde,
    cdimail_cde,
    cdtypadr_cde,
    adrlgn1_cde,
    adrlgn2_cde,
    adrlgn3_cde,
    adrlgn4_cde,
    cdpays_ret,
    libpays_ret,
    numtel_ret,
    nomburdis_ret,
    cdpostal_ret,
    cdimail_ret,
    cdtypadr_ret,
    adrlgn1_ret,
    adrlgn2_ret,
    adrlgn3_ret,
    adrlgn4_ret,
    (fdf.cdfour IS NOT NULL) AS deja_envoye,
    F.a_supprimer,
    CASE WHEN F.cdfour ILIKE '7%7' THEN 'PAPET' ELSE 'LIVRE' END AS categorie
FROM FOURNISSEURS F
         LEFT JOIN ADR_CDE ac ON ac.four_cde = F.cdfour
         LEFT JOIN ADR_RET ar ON ar.four_ret = F.cdfour
         LEFT JOIN fdn.decitre_fournisseur fdf
                   ON TRIM(nomfour_reference) = TRIM(LEFT(F.nomfour, 20))
GROUP BY
    F.cdfour,
    F.nomfour,
    F.gencodfour,
    F.cddevisefdn,
    cdpays_cde,
    libpays_cde,
    numtel_cde,
    nomburdis_cde,
    cdpostal_cde,
    cdimail_cde,
    cdtypadr_cde,
    adrlgn1_cde,
    adrlgn2_cde,
    adrlgn3_cde,
    adrlgn4_cde,
    cdpays_ret,
    libpays_ret,
    numtel_ret,
    nomburdis_ret,
    cdpostal_ret,
    cdimail_ret,
    cdtypadr_ret,
    adrlgn1_ret,
    adrlgn2_ret,
    adrlgn3_ret,
    adrlgn4_ret,
    fdf.cdfour IS NOT NULL,
    F.a_supprimer,
    CASE WHEN F.cdfour ILIKE '7%7' THEN 'PAPET' ELSE 'LIVRE' END;