Skip to content

Instantly share code, notes, and snippets.

@Djourdain

Djourdain/CentralogProduitRepository.php

Created June 23, 2022 08:44
Show Gist options
  • Save Djourdain/200ea262b69d4c22a243ddd9ffd189c4 to your computer and use it in GitHub Desktop.
Save Djourdain/200ea262b69d4c22a243ddd9ffd189c4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
CentralogProduitRepository.php
WITH PRODUITS_SAUVE AS (
SELECT cdpdt, MAX(cdmaj) AS cdmaj, MAX(dtsave)
FROM ever_produit_sauve
WHERE dtsave >= :dateDernierTraitement
GROUP BY cdpdt
)
SELECT DISTINCT
p.cdpdt as "gencode_produit",
COALESCE(pap.cdappelprix,'') as "code_court",
p.cdcollecserie as "code_seriel",
LEFT(p.libpdtminus,80) as "libelle_produit",
a.nomaut as "auteur",
string_agg(COALESCE(a1.nomaut,''), '/') as "interprete",
p.numcollec as "num_collection",
COALESCE(fcs.cdfour, pf.cdfour) as "code_fournisseur",
p.nummarqueeditoriale as "marque_editoriale",
p.cdeditr AS "code_editeur",
CASE WHEN min(pt1.cdtva) IN ('E','M') THEN '0' ELSE min(pt1.cdtva) END AS "code_tva_1",
CASE WHEN COALESCE(max(pt2.cdtva),'0') IN ('0','E','M') THEN '0' ELSE max(pt2.cdtva) END AS "code_tva_2",
(SELECT code_produit_fdn FROM categorie_famille cf WHERE cf.cdcategfam = f.cdcategfam) AS "code_produit",
p.cdfam as "code_famille",
p.cdformat as "code_support",
pf.cdpdtfour as "reference_fournisseur",
ROUND(pdp.pvttc,2) as "prix_vente_ttc",
ROUND(pf.pahtcatalogue,2) as "prix_achat_ht",
ROUND(pdpp.pvttcn,2) as "prix_vente_promo",
pdpp.dtdebvali1 as "debut_promo",
pdpp.dtfinval as "fin_promo",
COALESCE((CASE WHEN p.cdssfam IS NOT NULL THEN p.cdssfam
WHEN p.cdsfam IS NOT NULL
THEN (SELECT min(cdssfam)
FROM sous_sous_famille ssf
WHERE ssf.cdsfam = p.cdsfam
AND p.cdsfam IS NOT NULL)
WHEN p.cdfam IS NOT NULL
THEN (SELECT min(cdssfam)
FROM sous_sous_famille ssf
WHERE ssf.cdsfam = (SELECT MIN (cdsfam) FROM sous_famille sf WHERE sf.cdfam = p.cdfam))
END),'') as "code_rayon",
ROUND(p.pdspdt * 1000,2) as"poids",
p.dtparu as "date_edition",
p.cdgamme as "code_gamme",
p.cdpdtnvedt as "gencode_successeur",
COALESCE(mcpf.id_centre_profit,1) as "type_produit",
eps.cdmaj= 'S' AS "a_supprimer"
FROM PRODUITS_SAUVE eps
INNER JOIN produit p
ON p.cdpdt = eps.cdpdt
AND NOT (COALESCE(p.cdprecre,0) = 1 AND COALESCE(p.cdaparaitre,0) = 0)
AND LENGTH(p.cdpdt) = 13
AND ISNUMERIC(p.cdpdt)
AND p.cdtyppdt <> 'LN'
AND NULLIF(p.libpdtminus,'') IS NOT NULL
AND COALESCE(p.pdspdt,0) < 32.767
INNER JOIN pdt_four pf
ON pf.cdpdt = p.cdpdt
AND pf.cdfourprinc
AND CURRENT_DATE BETWEEN pf.dtdebvali1 AND pf.dtfinval
INNER JOIN pdt_tva pt1
ON pt1.cdpdt = p.cdpdt
AND CURRENT_DATE BETWEEN pt1.dtdebvali1 AND pt1.dtfinval
LEFT JOIN pdt_tva pt2
ON pt2.cdpdt =pt1.cdpdt
AND CURRENT_DATE BETWEEN pt2.dtdebvali1 AND pt2.dtfinval
AND pt2.cdtva <> pt1.cdtva
INNER JOIN pdt_dateprix pdp
ON pdp.cdpdt = p.cdpdt
AND CURRENT_DATE BETWEEN pdp.dtdebvali1 AND pdp.dtfinval
INNER JOIN famille f
ON f.cdfam = p.cdfam
INNER JOIN mapping_centre_profit_famille mcpf
ON mcpf.cdcategfam = f.cdcategfam
LEFT JOIN pdt_appel_prix pap
ON pap.cdpdt = p.cdpdt
AND CURRENT_DATE BETWEEN pap.dtdebvali1 AND pap.dtfinval
LEFT JOIN pdt_auteur pa
ON pa.cdpdt = p.cdpdt
AND pa.numordre = 1
LEFT JOIN auteur a
ON a.numaut = pa.numaut
LEFT JOIN pdt_auteur pa1
ON pa1.cdpdt = p.cdpdt
AND pa1.cdfonc = '590'
LEFT JOIN auteur a1
ON a1.numaut = pa1.numaut
LEFT JOIN pdt_dateprix_promo pdpp
ON pdpp.cdpdt = p.cdpdt
AND CURRENT_DATE BETWEEN pdpp.dtdebvali1 AND pdpp.dtfinval
LEFT JOIN four_cde_societe fcs
ON fcs.cdfour = pf.cdfour
AND COALESCE(fcs.cdeditr = p.cdeditr)
AND fcs.cdsoc = 'FDN'
GROUP BY p.cdpdt,
COALESCE(pap.cdappelprix,''),
p.cdcollecserie,
LEFT(p.libpdtminus,80),
a.nomaut,
p.numcollec,
COALESCE(fcs.cdfour, pf.cdfour),
p.nummarqueeditoriale,
p.cdeditr,
p.cdfam,
f.cdcategfam,
p.cdformat,
pf.cdpdtfour,
pdp.pvttc,
pf.pahtcatalogue,
pdpp.pvttcn,
pdpp.dtdebvali1,
pdpp.dtfinval,
COALESCE((CASE WHEN p.cdssfam IS NOT NULL THEN p.cdssfam
WHEN p.cdsfam IS NOT NULL
THEN (SELECT min(cdssfam)
FROM sous_sous_famille ssf
WHERE ssf.cdsfam = p.cdsfam
AND p.cdsfam IS NOT NULL)
WHEN p.cdfam IS NOT NULL
THEN (SELECT min(cdssfam)
FROM sous_sous_famille ssf
WHERE ssf.cdsfam = (SELECT MIN (cdsfam)
FROM sous_famille sf
WHERE sf.cdfam = p.cdfam))
END),''),
p.pdspdt,
p.dtparu,
p.cdgamme,
COALESCE(mcpf.id_centre_profit,1),
p.cdpdtnvedt,
eps.cdmaj= 'S';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment