Notice: Function _load_textdomain_just_in_time was called incorrectly. Translation loading for the ad-inserter domain was triggered too early. This is usually an indicator for some code in the plugin or theme running too early. Translations should be loaded at the init action or later. Please see Debugging in WordPress for more information. (This message was added in version 6.7.0.) in /var/www/vhosts/blog.webeats.it/httpdocs/wp-includes/functions.php on line 6114

Notice: Function _load_textdomain_just_in_time was called incorrectly. Translation loading for the cookie-law-info domain was triggered too early. This is usually an indicator for some code in the plugin or theme running too early. Translations should be loaded at the init action or later. Please see Debugging in WordPress for more information. (This message was added in version 6.7.0.) in /var/www/vhosts/blog.webeats.it/httpdocs/wp-includes/functions.php on line 6114

Notice: Function _load_textdomain_just_in_time was called incorrectly. Translation loading for the wordpress-seo domain was triggered too early. This is usually an indicator for some code in the plugin or theme running too early. Translations should be loaded at the init action or later. Please see Debugging in WordPress for more information. (This message was added in version 6.7.0.) in /var/www/vhosts/blog.webeats.it/httpdocs/wp-includes/functions.php on line 6114
Esportare le combinazioni prodotto con PrestaShop 1.6.x.x

Query MySQL per esportare tutte le combinazioni prodotto con PrestaShop testato su versione 1.6.0.x e 1.6.1.x

combinazioni prodotto con prestashop

Una volta aperto PhpMyAdmin e aver selezionato il database, clicchiamo sul tab SQL. In questa sezione possiamo inserire la seguente query per selezionare la lista di tutti i prodotti con le combinazioni esplose:

SELECT 
p.id_product, 
pam.name as nome_marca, 
p.reference,
pl.name as nome_prodotto, 
GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, 
pa.reference as reference_combination,
pa.ean13,
p.price as price_a, 
pap.price as price_b, 
pap.price + p.price AS prezzo_combinazione,
pq.quantity 
FROM ps_product_attribute pa 
LEFT JOIN ps_product p ON (p.id_product = pa.id_product) 
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute) 
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) 
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
LEFT JOIN ps_manufacturer pam ON (p.id_manufacturer = pam.id_manufacturer)
LEFT JOIN ps_product_attribute pap ON ( pap.id_product_attribute = pa.id_product_attribute ) 
WHERE pl.id_lang = 1 
AND pal.id_lang = 1 
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute

E’ possibile poi salvare il risultato in un file CSV o XLS cliccando sul tasto Esporta presente nel menù Operazioni sui risultati della query, sotto la lista dei prodotti appena printati. Questa query restituisce i seguenti valori:

ID prodotto, Nome marca, Referenza prodotto, Nome prodotto, Combinazione, Referenza combinazione, EAN13 combinazione, Prezzo, Quantità

Ovviamente è possibile personalizzare la query per restituire campi aggiuntivi. Ecco una versione più completa.

SELECT 
p.id_product,
p.active,
pl.name,
GROUP_CONCAT(DISTINCT (cl.name)
SEPARATOR ',') as categories,
p.price,
p.id_tax_rules_group,
p.wholesale_price,
p.reference,
p.supplier_reference,
p.id_supplier,
p.id_manufacturer,
p.ean13,
p.upc,
p.ecotax,
p.weight,
p.quantity,
pl.description_short,
pl.description,
pl.meta_title,
pl.meta_keywords,
pl.meta_description,
pl.link_rewrite,
pl.available_now,
pl.available_later,
p.available_for_order,
p.date_add,
p.show_price,
concat('http://',
ifnull(shop_domain.value, 'domain'),
'/img/p/',
if(CHAR_LENGTH(pi.id_image) >= 5,
concat(SUBSTRING(pi.id_image from - 5 FOR 1),
'/'),
''),
if(CHAR_LENGTH(pi.id_image) >= 4,
concat(SUBSTRING(pi.id_image from - 4 FOR 1),
'/'),
''),
if(CHAR_LENGTH(pi.id_image) >= 3,
concat(SUBSTRING(pi.id_image from - 3 FOR 1),
'/'),
''),
if(CHAR_LENGTH(pi.id_image) >= 2,
concat(SUBSTRING(pi.id_image from - 2 FOR 1),
'/'),
''),
if(CHAR_LENGTH(pi.id_image) >= 1,
concat(SUBSTRING(pi.id_image from - 1 FOR 1),
'/'),
''),
pi.id_image,
'.jpg') as image_url,
p.online_only,
p.condition,
p.id_shop_default
FROM
ps_product p
LEFT JOIN
ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN
ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN
ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN
ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN
ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN
ps_image pi ON p.id_product = pi.id_product
LEFT JOIN
ps_configuration shop_domain ON shop_domain.name = 'PS_SHOP_DOMAIN'
WHERE
pl.id_lang = (SELECT id_lang FROM ps_lang WHERE active = '1' ORDER BY id_lang ASC LIMIT 1)
AND cl.id_lang = pl.id_lang
AND p.id_shop_default = (SELECT id_shop FROM ps_shop WHERE active = '1' ORDER BY id_shop ASC LIMIT 1)
AND c.id_shop_default = p.id_shop_default
GROUP BY p.id_product