CSV per import articoli su Facebook

[antonio04]

Buongiorno , ho trovato il modo di importare gli articoli del mio sito sulla vetrina di Facebook

I dati obbligatori che chiede FB sono

id     |   title   |   description   |    availability   |   condition   |   price   |   link (al prodotto sul sito)   |   image_link |  brand

 

Riesco ad esportare tutto tranne il link al prodotto sul sito

uso il codice riportato sotto per esportare una categoria

Come posso aggiungere una istruzione per esportare anche il link completo al prodotto o in alternativa il link rewrite della categoria?

SELECT
p.reference AS 'Reference #',
l.name,
l.description AS 'Descrizione ',
p.price AS 'Prezzo',
concat( 'https://www.fiorucciservice.it/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS url_image,
l.link_rewrite AS 'link',
p.id_category_default AS 'Categoria',
p.active AS 'Active (0/1)'

FROM  ps_product AS p inner JOIN ps_product_lang AS l ON p.id_product = l.id_product
LEFT JOIN ps_image im ON p.id_product = im.id_product
WHERE 
p.id_category_default  = 97;

 

 

Faccio presente che uso questo codice per esportare le info sulle categorie( a me servirebbe solo il link rewrite)

ma non so come integrare in una unica istruzione SQL

SELECT
a.`id_category`, `active`, `name`, `link_rewrite`, a.`id_parent`, a.`is_root_category`, `description`, `meta_title`, `meta_description`, a.`id_shop_default`
FROM `ps_category` a 
LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 1 AND b.`id_shop` = 1)
LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category` AND sa.id_shop = 1)  
WHERE 1

ORDER BY a.`id_category` ASC

 

Ringrazio chi potra darmi qualche info

[Guest]

#antonio04

 

SELECT p.id_product, p.active, pl.name AS 'Name',
    GROUP_CONCAT(DISTINCT(CONCAT('https://',
    IFNULL(conf.value, 'undefined_domain'),
    '/img/p/',
    IF(CHAR_LENGTH(pi.id_image) >= 5, 
        CONCAT(
        SUBSTRING(pi.id_image, -5, 1),
        '/'),
        ''),
    IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''),
    IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''),
    IF(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''),
    IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''),
    pi.id_image,
    '.jpg')) SEPARATOR ', ') AS 'Images (x,y,z...)',
    GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Categories (x,y,z...)',
    p.price AS 'Price tax excluded or Price tax included',
    p.id_tax_rules_group AS 'Tax rules ID',
    p.wholesale_price AS 'Wholesale price',
    p.on_sale AS 'On sale (0/1)',
    IF(pr.reduction_type = 'amount', pr.reduction, '') AS 'Discount amount',
    IF(pr.reduction_type = 'percentage', pr.reduction, '') AS 'Discount percent',
    pr.from AS 'Discount from (yyyy-mm-dd)',
    pr.to AS 'Discount to (yyyy-mm-dd)',
    p.reference AS 'Reference #',
    p.supplier_reference AS 'Supplier reference #',
    ps.name AS 'Supplier',
    pm.name AS 'Manufacturer',
    p.ean13 AS 'EAN13',
    p.upc AS 'UPC',
    p.ecotax AS 'Ecotax',
    p.width AS 'Width',
    p.height AS 'Height',
    p.depth AS 'Depth',
    p.weight AS 'Weight',
    sa.quantity AS 'Quantity',
    p.minimal_quantity AS 'Minimal quantity',
    'both' AS 'Visibility',
    p.additional_shipping_cost AS 'Additional shipping cost',
    p.unity AS 'Unity',
    p.unit_price_ratio AS 'Unit price',
    pl.description_short AS 'Short description',
    pl.description AS 'Description',
    IF(t.name IS NOT NULL, GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ','), '') AS 'Tags (x,y,z...)',
    pl.meta_title AS 'Meta title',
    pl.meta_keywords AS 'Meta keywords',
    pl.meta_description AS 'Meta description',
    pl.link_rewrite AS 'URL rewritten',
    pl.available_now AS 'Text when in stock',
    pl.available_later AS 'Text when backorder allowed',
    p.available_for_order AS 'Available for order (0 = No, 1 = Yes)',
    '' AS 'Product available date',
    p.date_add 'Product creation date',
    p.show_price AS 'Show price (0 = No, 1 = Yes)',
    0 AS 'Delete existing images (0 = No, 1 = Yes)',
    GROUP_CONCAT(DISTINCT(CONCAT((fl.name), ':', (fvl.value), ':0')) SEPARATOR ',') AS 'Feature (Name:Value:Position)',
    p.online_only AS 'Available online only (0 = No, 1 = Yes)',
    p.condition AS 'Cond',
    0 AS 'Customizable (0 = No, 1 = Yes)',
    0 AS 'Uploadable files (0 = No, 1 = Yes)',
    0 AS 'Text fields (0 = No, 1 = Yes)',
    p.out_of_stock AS 'Out of stock',
    '1' AS 'ID',
    NULL AS 'Action when out of stock',
    NULL AS 'Depends on stock',
    NULL AS 'Warehouse'
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_specific_price pr ON(p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON(p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON(pt.id_tag = t.id_tag)
LEFT JOIN ps_image pi ON(p.id_product = pi.id_product)
LEFT JOIN ps_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON(p.id_supplier = ps.id_supplier)
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
GROUP BY p.id_product;

 

 

buon divertimento

 

Saluto

[antonio04]

Grazie mille , ora faccio un po di prove

[antonio04]

Ho fatto qualche prova , ma credo manchi il link rewrite alle categorie che e' quello che mi serve per ricostruire il link completo al prodotto

[antonio04]

Ho risolto cosi , magari a qualcuno puo essere utile

In partica riesco ad avere il link rewrite al prodotto e il link rewrite alla categoria

Esiste un modo da SQL di concatenare il mio indirizzo principale www.fiorucciricamabi.it/rewrite_categoria/rewrite_prodotto

Altro problema mi trovo con i prezzi esportati in questo formato 15.000000 invece di 15,00  sapete se posso risolvere?

Grazie

 

SELECT
p.reference AS 'Reference #',
l.name,
l.description AS 'Descrizione ',
p.price AS 'Prezzo',
concat( 'https://www.fiorucciservice.it/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS url_image,
cl.link_rewrite AS 'Catogorie',
l.link_rewrite AS 'link',
p.id_category_default AS 'Categoria',
p.active AS 'Active (0/1)'

FROM  ps_product AS p inner JOIN ps_product_lang AS l ON p.id_product = l.id_product
LEFT JOIN ps_image im ON p.id_product = im.id_product
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)

WHERE 
p.id_category_default  = 97;

[antonio04]

Ok modificando la prima parte adesso ho tutto cio che mi serve

Devo risolvere il problema dei prezzi che vengono esportati con molti zeri

 

SELECT
p.reference AS 'Reference #',
l.name,
l.description AS 'Descrizione ',
p.price AS 'Prezzo',
CONCAT('https://www.fiorucciricambi.it/',cl.link_rewrite , '/',l.link_rewrite),
concat( 'https://www.fiorucciservice.it/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS url_image,
p.active AS 'Active (0/1)'

[Guest]

be semplice uso di PHP!!!

 

number_format(prezzo,2);

 

 

[antonio04]

Ok grazie, sono quasi riuscito a fare tutto

 

Per quanto riguarda il php sarebbe la soluzione perfetta perche potrei lanciare lo script tramite cron

Purtroppo non sono un programmatore e di solito risolvo con tentativi o consigli che ricevo dal forum

Sai se esiste qualche esempio di script php con il quale interrogare il database di prestashop?

[Guest]

non mi è chiaro cosa vuole fare

dettagli meglio la richiesta

saluto

[antonio04]

Invece che eseguire la Query da BO,  la stessa query andrebbe eseguita lanciando una pagina PHP tramite un link

esempio www.fiorucciricambi.it/esporta_dati.php

Sei gia stato molto gentile non voglio farti perdere troppo tempo

Per ora ho ripolto quasi tutto trami query  e va bene anche cosi

[Guest]

su che versione di presta siamo ?

[antonio04]

1.7.3  ,  nei prossimi giorni vorrei contattarti personalmente per un problema che devo risolvere vorrei chiederti un preventivo.

Al momento ho risolto cosi  , ho solo il problema che mi duplica gli articoli ma risolto tramite Excel

SELECT
p.reference AS 'id',
l.name AS 'title',
l.description AS 'description',
CASE WHEN p.active = '1' THEN 'in stock'
            ELSE 'out of stock' END    AS 'availability ',

CASE WHEN p.active = '1' THEN 'new' -------------------------------> questo lo uso per crreae una colonna dove c' e' sempre il valore "new"
            ELSE 'new' END    AS 'condition ',                                           non ho trovato altro modo per aggiungere una colonna che nel database non c' e'

FORMAT(p.price,2) AS 'price',
CONCAT('https://www.fiorucciricambi.it/',cl.link_rewrite , '/',l.link_rewrite) AS 'link',
concat( 'https://www.fiorucciservice.it/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS 'image_link',
pm.name AS 'brand'

FROM  ps_product AS p inner JOIN ps_product_lang AS l ON p.id_product = l.id_product

LEFT JOIN ps_image im ON p.id_product = im.id_product
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_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
WHERE l.id_lang = 1 AND p.id_category_default IN (97199,64);
 

[Guest]

mandami il file che stai facendo