Skip to content

Instantly share code, notes, and snippets.

@Djourdain

Djourdain/CentralogFournisseurRepository.php

Created June 23, 2022 08:43
Show Gist options
  • Save Djourdain/2df0254831a70a08e5787bc8e76d8e4f to your computer and use it in GitHub Desktop.
Save Djourdain/2df0254831a70a08e5787bc8e76d8e4f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment