Requête SQL pour prestashop qui permet de faire un listing produit pour un client spécifique.

listing-des-produits-pour-un-client.sql

SET @idCustomer = 1208;

SELECT P.reference
	  ,PL.name
	  ,M.name AS 'Fabricant'
	  ,P.quantity
#	  ,SP.price	  
	  ,P.price AS 'prix public'
FROM ps_product P
	INNER JOIN ps_product_lang PL ON PL.id_product = P.id_product
	INNER JOIN ps_manufacturer M ON M.id_manufacturer = P.id_manufacturer
#	INNER JOIN ps_specific_price SP ON SP.id_product = P.id_product AND SP.id_group = 4
	INNER JOIN ps_category_product CP ON CP.id_product = P.id_product
	INNER JOIN ps_category_group CG ON CG.id_category = CP.id_category
WHERE CG.id_group IN (SELECT GL.id_group
						FROM ps_customer C
							INNER JOIN ps_customer_group CG ON CG.id_customer = C.id_customer
							INNER JOIN ps_group GL ON GL.id_group = CG.id_group
						WHERE C.id_customer = @idCustomer)

AND P.active = 1 AND PL.id_lang =1 
ORDER BY M.name;