/**
Id et nom des Catégories actives dans PrestaShop 
par référence, id ou nom de produit
pour la langue id=1 et boutique id=1
**/

/**REQUETES PHP MY ADMIN **/
SELECT ps17_category_product.id_product AS 'Id_Produit', ps17_product.reference AS 'Ref_Produit', ps17_category_lang.name AS 'Nom_Produit', ps17_category.id_category AS 'Id_Catégorie', ps17_product_lang.name AS 'Nom_Produit', ps17_category.id_parent AS 'Id_Catégorie_Parente', ps17_category.date_upd AS 'Date_MAJ', ps17_category.is_root_category AS 'Racine' FROM ps17_category INNER JOIN ps17_category_lang ON ps17_category.id_category = ps17_category_lang.id_category INNER JOIN ps17_category_product ON ps17_category.id_category = ps17_category_product.id_category INNER JOIN ps17_product_lang ON ps17_category_product.id_product= ps17_product_lang.id_product INNER JOIN ps17_product ON ps17_product.id_product = ps17_product_lang.id_product WHERE ps17_category.active = 1 AND ps17_category_lang.id_lang = 1 AND ps17_category_lang.id_shop = 1 ORDER BY ps17_category_product.id_product ASC; 
/**REQUETES PRESTASHOP**/
SELECT b.id_product as 'Id_Produit', d.reference as 'Référence', e.name as 'Nom_Produit', a.id_category as 'Id_Catégorie', c.name 'Nom_Catégorie', , a.id_parent as 'Catégorie_Parente', a.date_upd AS 'Date_MAJ', a.is_root_category as 'Racine'
FROM ps17_category a 
INNER JOIN ps17_category_lang c ON a.id_category = c.id_category 
INNER JOIN ps17_category_product b ON a.id_category = b.id_category 
INNER JOIN ps17_product_lang e ON b.id_product= e.id_product 
INNER JOIN ps17_product d ON d.id_product = e.id_product 
WHERE a.active = 1 AND c.id_lang = 1 AND c.id_shop = 1 
ORDER BY b.id_product ASC; 

________________________________________________________________________________________

/**
Produit à déclinaisons - Référence Maitre - Référence Déclinaison - Prix - Qte par déclianaison - Qte Totale - Date Maj Article
**/
/**REQUETES PHP MY ADMIN **/

SELECT ps17_category_product.id_product AS 'Id_Produit', ps17_product_attribute_combination.id_product_attribute AS 'Id_Attribut_Produit', ps17_product_lang.name AS 'Nom_Produit', ps17_product_attribute.reference AS 'Référence_Déclinaison', ps17_product.reference AS 'Référence_Produit', ps17_product.price AS 'Prix', ps17_product.ean13 AS 'Code_Barre'
FROM ps17_product 
LEFT JOIN ps17_product_lang ON (ps17_product.id_product = ps17_product_lang.id_product AND ps17_product_lang.id_lang=1)
LEFT JOIN ps17_product_attribute ON (ps17_product.id_product = ps17_product_attribute.id_product )
LEFT JOIN ps17_category_product ON (ps17_product.id_product = ps17_category_product.id_product)
LEFT JOIN ps17_stock_available ON (ps17_product.id_product = ps17_stock_available.id_product AND ps17_product_attribute.id_product_attribute = ps17_stock_available.id_product_attribute)
LEFT JOIN ps17_product_attribute_combination ON (ps17_product_attribute_combination.id_product_attribute = ps17_product_attribute.id_product_attribute)
LEFT JOIN ps17_attribute_lang ON (ps17_attribute_lang.id_attribute = ps17_product_attribute_combination.id_product_attribute AND ps17_attribute_lang.id_lang= 1 )
GROUP BY ps17_product.id_product, ps17_product_attribute_combination.id_product_attribute
ORDER BY ps17_product.id_product


/**REQUETES PRESTASHOP**/
SELECT p.id_product 'Product ID', pac.id_product_attribute 'id_product_attribute', pl.name 'Product Name', pa.reference as 'reference declinaison',  p.reference as 'reference produit', GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination', p.price 'prix', p.ean13 'EAN 13', p.upc 'UPC'
FROM ps17_product p
LEFT JOIN ps17_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=1)
LEFT JOIN ps17_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps17_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps17_stock_available s ON (p.id_product = s.id_product and pa.id_product_attribute=s.id_product_attribute)
LEFT JOIN ps17_stock_available pt ON (p.id_product = pt.id_product)
LEFT JOIN ps17_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps17_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=1)

GROUP BY p.id_product,pac.id_product_attribute
order by p.id_product



________________________________________________________________________________________

/**
Produit à déclinaisons - Référence Maitre - Référence Déclinaison - Prix - Quantité - Date MAJ
**/
/**REQUETES PHP MY ADMIN **/

SELECT ps17_category_product.id_product AS 'Id_Produit', ps17_product_attribute_combination.id_product_attribute AS 'Id_Attribut_Produit', ps17_product_lang.name AS 'Nom_Produit', ps17_product_attribute.reference AS 'Référence_Déclinaison', ps17_product.ean13 AS 'Code_Barre', ps17_product.reference AS 'Référence_Produit', ps17_product.price AS 'Prix',ps17_stock_available.quantity AS 'Quantité', ps17_product.date_upd AS 'Date_MAJ' 
FROM ps17_product 
LEFT JOIN ps17_product_lang ON (ps17_product.id_product = ps17_product_lang.id_product AND ps17_product_lang.id_lang=1)
LEFT JOIN ps17_product_attribute ON (ps17_product.id_product = ps17_product_attribute.id_product )
LEFT JOIN ps17_category_product ON (ps17_product.id_product = ps17_category_product.id_product)
LEFT JOIN ps17_stock_available ON (ps17_product.id_product = ps17_stock_available.id_product AND ps17_product_attribute.id_product_attribute = ps17_stock_available.id_product_attribute)
LEFT JOIN ps17_product_attribute_combination ON (ps17_product_attribute_combination.id_product_attribute = ps17_product_attribute.id_product_attribute)
LEFT JOIN ps17_attribute_lang ON (ps17_attribute_lang.id_attribute = ps17_product_attribute_combination.id_product_attribute AND ps17_attribute_lang.id_lang= 1 )
WHERE ps17_product.reference like 'NOVA%'
GROUP BY ps17_product.id_product, ps17_product_attribute_combination.id_product_attribute
ORDER BY ps17_product.id_product

/**REQUETES PRESTASHOP**/

SELECT p.id_product AS 'ID_Produit', pac.id_product_attribute AS'Attribute_Produit', pl.name AS 'Nom_Prduit', pa.reference as 'Ref_Declinaison',  p.reference as 'Reference_Produit', al.name AS 'Nom_Déclinaison', st.quantity as 'Qte', p.ean13 AS 'Code_EAN13', p.price AS 'Prix', q.quantity AS 'Qte_Total', p.date_upd AS 'Dat_Maj'
FROM ps17_product p
LEFT JOIN ps17_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=1)
LEFT JOIN ps17_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps17_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps17_stock_available s ON (p.id_product = s.id_product and pa.id_product_attribute=s.id_product_attribute)
LEFT JOIN ps17_stock_available pt ON (p.id_product = pt.id_product)
LEFT JOIN ps17_stock_available q ON (p.id_product = q.id_product)
LEFT JOIN ps17_stock_available st ON (pa.id_product_attribute = st.id_product_attribute)
LEFT JOIN ps17_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps17_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=1)
WHERE p.reference like 'NOVASL%'
GROUP BY p.id_product,pac.id_product_attribute
order by p.id_product