Skip to content

Instantly share code, notes, and snippets.

@rseon

rseon/Prestashop_exports.md

Last active November 9, 2023 16:17
Show Gist options
  • Save rseon/cb9ae67eba8c5a48bc071ecd484d49d4 to your computer and use it in GitHub Desktop.
Save rseon/cb9ae67eba8c5a48bc071ecd484d49d4 to your computer and use it in GitHub Desktop.
Download ZIP
Quelques scripts SQL d'export pour Prestashop
Raw
Prestashop_exports.md

Prestashop exports

URL replacement

ps_configuration.name (PS_SHOP_DOMAIN, PS_SHOP_DOMAIN_SSL, PS_SSL_ENABLED)
ps_shop_url.domain
ps_shop_url.domain_ssl

Extraction catégories

SELECT c.id_category, cl.name
	, (
		SELECT CONCAT(cl0.name, ' (', cl0.id_category, ')')
		FROM ps_category_lang cl0
		WHERE cl0.id_category = c.id_parent AND cl0.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr')
	) as Parent_category
FROM ps_category c
INNER JOIN ps_category_lang cl ON (cl.id_category = c.id_category AND cl.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr'))
WHERE c.active = 1
ORDER BY c.id_category

Extraction produits

SELECT
	p.*, pl.*
	, CAST(p.price * (1 + (
		SELECT t.rate
		FROM ps_tax t
		INNER JOIN ps_tax_rule tr ON tr.id_tax = t.id_tax
		WHERE tr.id_tax_rules_group = p.id_tax_rules_group
		AND tr.id_country = 8
	) / 100) as DECIMAL(20, 6)) as prix_ttc
	, (
		SELECT GROUP_CONCAT(cp0.id_category)
		FROM ps_category_product cp0
		WHERE cp0.id_product = p.id_product
	) as asso_categories
	, (
		SELECT GROUP_CONCAT(CONCAT(cl0.name, ' (', cp0.id_category, (CASE WHEN p.id_category_default = cp0.id_category THEN '*' ELSE '' END),')') SEPARATOR ', ')
		FROM ps_category_product cp0
		INNER JOIN ps_category_lang cl0 ON (cl0.id_category = cp0.id_category AND cl0.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr'))
		WHERE cp0.id_product = p.id_product
	) as Categories
	, m.name as Fabricant
	, sa.quantity as quantite
FROM ps_product p
INNER JOIN ps_product_lang pl ON (pl.id_product = p.id_product AND pl.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr'))
LEFT JOIN ps_manufacturer m ON m.id_manufacturer = p.id_manufacturer
LEFT JOIN ps_stock_available sa ON (sa.id_product = p.id_product AND sa.id_product_attribute = 0)
WHERE p.active = 1
ORDER BY p.id_product

Extraction déclinaisons

SELECT
	p.id_product
	, pl.name
	, 	agl.name as attribute_group_name
	, al.name as attribute_name
	, pa.reference
	, pa.upc as code_comp
	, pa.price
	, (
		SELECT sa0.quantity
		FROM ps_stock_available sa0
		WHERE sa0.id_product = p.id_product
			AND sa0.id_product_attribute = pa.id_product_attribute
	) as stock
	, (
		SELECT GROUP_CONCAT(cl0.name SEPARATOR ', ')
		FROM ps_category_product cp0
		INNER JOIN ps_category_lang cl0 ON (cl0.id_category = cp0.id_category AND cl0.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr'))
		WHERE cp0.id_product = p.id_product
		GROUP BY cp0.id_product
	) as categories
FROM ps_product p
INNER JOIN ps_product_lang pl ON (pl.id_product = p.id_product AND pl.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr'))
INNER JOIN ps_product_attribute pa ON (pa.id_product = p.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute a ON (a.id_attribute = pac.id_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute AND al.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr'))
LEFT JOIN ps_attribute_group_lang agl ON (agl.id_attribute_group = a.id_attribute_group AND agl.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr'))
ORDER BY p.id_product ASC

Extraction clients

Avec dernière adresse renseignée, infos d'inscription à la newsletter et infos de commande :

SELECT c.firstname, c.lastname, c.email, a.address1, a.address2, a.postcode, a.city, a.phone, a.phone_mobile, c.newsletter,
IF(c.newsletter = '', '', c.newsletter_date_add) as newsletter_date_add,
(SELECT SUM(total_paid) FROM ps_orders WHERE valid = 1 AND id_customer = c.id_customer) AS montant_total_commande,
(SELECT COUNT(id_order) FROM ps_orders WHERE valid = 1 AND id_customer = c.id_customer) AS nombre_commande,
(SELECT date_add FROM ps_orders WHERE valid = 1 AND id_customer = c.id_customer ORDER BY date_add DESC LIMIT 1) AS date_derniere_commande,
c.active
FROM ps_customer c
INNER JOIN (
	SELECT MAX(id_address) as max_id_address, id_customer
	FROM ps_address
	WHERE deleted = 0
	AND active = 1
	GROUP BY id_customer
) a0 ON a0.id_customer = c.id_customer
INNER JOIN ps_address a ON a.id_address = a0.max_id_address
WHERE c.deleted = 0

Pour générer un fichier d'import

Produit

Exporter depuis phpMyAdmin avec ces paramètres :

  • Format : CSV for MS Excel
  • Encodage : iso-8859-1
  • Remplacer NULL par : (vide)
  • Cocher Retirer les caractères de fin de ligne à l'intérieur des colonnes
  • Cocher Afficher les noms de colonnes en première ligne

Il faudra vérifier et retraiter le fichier CSV pour l'importer.

Pour l'import dans Prestashop :

  • Fichier encodé en ISO-8859-1 : Oui
  • Séparateur de champs : ;
  • Séparateur de champs à valeurs multiples : | (pipe)

Pour les images, 3 colonnes sont générées :

SELECT
	p.id_product
	, p.active
	, pl.name
	, CONCAT_WS('|', p.id_category_default, (
		SELECT GROUP_CONCAT(cp0.id_category SEPARATOR '|')
		FROM ps_category_product cp0
		WHERE cp0.id_product = p.id_product
		AND cp0.id_category <> p.id_category_default
	)) as id_categories
	, p.price
	, p.id_tax_rules_group
	, p.wholesale_price
	, p.on_sale
	, p.reference
	, p.id_manufacturer
	, p.ean13
	, p.upc
	, p.width
	, p.height
	, p.depth
	, p.weight
	, (
		SELECT sa0.quantity
		FROM ps_stock_available sa0
		WHERE sa0.id_product = p.id_product
		AND sa0.id_product_attribute = 0
	) as stock
	, p.visibility
	, p.minimal_quantity
	, pl.description
	, pl.description_short
	, pl.link_rewrite
	, pl.meta_description
	, pl.meta_keywords
	, pl.meta_title
	, (
		SELECT GROUP_CONCAT(CONCAT('https://my-website.com/images_produits/', p.id_product, '-', i0.id_image, '.jpg') SEPARATOR '|')
		FROM ps_image i0
		WHERE i0.id_product = p.id_product
	) as images_url_imported
	, (
		SELECT GROUP_CONCAT(CONCAT('https://my-website.com/img/p/',
			CASE
				WHEN LENGTH(i0.id_image) = 1 THEN i0.id_image
				WHEN LENGTH(i0.id_image) = 2 THEN CONCAT(SUBSTRING(i0.id_image FROM -2 FOR 1), '/', SUBSTRING(i0.id_image FROM -1 FOR 1))
				WHEN LENGTH(i0.id_image) = 3 THEN CONCAT(SUBSTRING(i0.id_image FROM -3 FOR 1), '/', SUBSTRING(i0.id_image FROM -2 FOR 1), '/', SUBSTRING(i0.id_image FROM -1 FOR 1))
				WHEN LENGTH(i0.id_image) = 4 THEN CONCAT(SUBSTRING(i0.id_image FROM -4 FOR 1), '/', SUBSTRING(i0.id_image FROM -3 FOR 1), '/', SUBSTRING(i0.id_image FROM -2 FOR 1), '/', SUBSTRING(i0.id_image FROM -1 FOR 1))
				WHEN LENGTH(i0.id_image) = 5 THEN CONCAT(SUBSTRING(i0.id_image FROM -5 FOR 1), '/', SUBSTRING(i0.id_image FROM -4 FOR 1), '/', SUBSTRING(i0.id_image FROM -3 FOR 1), '/', SUBSTRING(i0.id_image FROM -2 FOR 1), '/', SUBSTRING(i0.id_image FROM -1 FOR 1))
			END
		), '/', i0.id_image, '.jpg' SEPARATOR '|')
		FROM ps_image i0
		WHERE i0.id_product = p.id_product
	) as images_url_real
	, (
		SELECT GROUP_CONCAT(CONCAT('https://my-website.com/', i0.id_image, '/', pl.link_rewrite, '.jpg') SEPARATOR '|')
		FROM ps_image i0
		WHERE i0.id_product = p.id_product
	) as images_url_rewrited
	, 1 as delete_existing_images
	, (
		SELECT GROUP_CONCAT(CONCAT_WS(':', REPLACE(REPLACE(fl0.name, ':', ''), '  ', ''), fvl0.value) SEPARATOR '|')
		FROM ps_feature_product fp0
		INNER JOIN ps_feature_lang fl0 ON (fl0.id_feature = fp0.id_feature AND fl0.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr'))
		INNER JOIN ps_feature_value_lang fvl0 ON (fvl0.id_feature_value = fp0.id_feature_value AND fvl0.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr'))
		WHERE fp0.id_product = p.id_product
	) as features
FROM ps_product p
INNER JOIN ps_product_lang pl ON (pl.id_product = p.id_product AND pl.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr'))

Juste les images

3 colonnes générées (cf explication ci-dessus)

SELECT
	p.id_product
	, pl.name
	, GROUP_CONCAT(CONCAT(p.id_product, '-', i.id_image, '.jpg')) as images_url_imported
	, GROUP_CONCAT(CONCAT(
		CASE
			WHEN LENGTH(i.id_image) = 1 THEN i.id_image
			WHEN LENGTH(i.id_image) = 2 THEN CONCAT(SUBSTRING(i.id_image FROM -2 FOR 1), '/', SUBSTRING(i.id_image FROM -1 FOR 1))
			WHEN LENGTH(i.id_image) = 3 THEN CONCAT(SUBSTRING(i.id_image FROM -3 FOR 1), '/', SUBSTRING(i.id_image FROM -2 FOR 1), '/', SUBSTRING(i.id_image FROM -1 FOR 1))
			WHEN LENGTH(i.id_image) = 4 THEN CONCAT(SUBSTRING(i.id_image FROM -4 FOR 1), '/', SUBSTRING(i.id_image FROM -3 FOR 1), '/', SUBSTRING(i.id_image FROM -2 FOR 1), '/', SUBSTRING(i.id_image FROM -1 FOR 1))
			WHEN LENGTH(i.id_image) = 5 THEN CONCAT(SUBSTRING(i.id_image FROM -5 FOR 1), '/', SUBSTRING(i.id_image FROM -4 FOR 1), '/', SUBSTRING(i.id_image FROM -3 FOR 1), '/', SUBSTRING(i.id_image FROM -2 FOR 1), '/', SUBSTRING(i.id_image FROM -1 FOR 1))
		END
	), '/', i.id_image, '.jpg') as images_url_real
	, GROUP_CONCAT(CONCAT('/', i.id_image, '/', pl.link_rewrite, '.jpg')) as images_url_rewrited
	/*, (
		SELECT GROUP_CONCAT(CONCAT(p.id_product, '-', i0.id_image, '.jpg'))
		FROM ps_image i0
		WHERE i0.id_product = p.id_product
	) as images*/
FROM ps_product p
INNER JOIN ps_product_lang pl ON (pl.id_product = p.id_product AND pl.id_lang = (SELECT id_lang FROM ps_lang WHERE iso_code = 'fr'))
INNER JOIN ps_image i ON i.id_product = p.id_product
GROUP BY p.id_product

Trouver des doublons

Exemple de l'EAN13 dans les tables produits et déclinaisons

SELECT 'product' as type, COUNT(ean13) AS nbr_doublon, ean13, GROUP_CONCAT(id_product ORDER BY id_product) as id_products, null as id_product_attributes
FROM ps_product
GROUP BY ean13
HAVING COUNT(ean13) > 1

UNION

SELECT 'product_attribute', COUNT(ean13), ean13, GROUP_CONCAT(DISTINCT id_product ORDER BY id_product), GROUP_CONCAT(id_product_attribute)
FROM ps_product_attribute
GROUP BY ean13
HAVING COUNT(ean13) > 1

ORDER BY type ASC, nbr_doublon DESC, ean13 ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment