SELECT 
				p.`id_product`,
				(stock.orion_stock + stock.shots_stock) AS total_stock,
				CASE 
					WHEN stock.`quantity` < 0 AND p.product_import_brand=1 THEN 0
					ELSE stock.`quantity`
				END AS `quantity`,
				IF(sp.`price` IS NOT NULL AND sp.`price` != -1, sp.`price`, p.`price`) AS `final_price`
			
FROM `ps_product` p
 INNER JOIN ps_product_shop product_shop
		ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 4)
LEFT JOIN `ps_specific_price` `sp` ON 
				sp.`id_product` = p.`id_product`
				AND (sp.`id_customer` = 0 OR sp.`id_customer` = 0)
				AND (sp.`id_group` = 1)
				AND sp.`id_cart` = 0
				AND sp.`from` <= NOW() AND sp.`to` >= NOW()
				AND sp.`id_product_attribute` = 0
			
LEFT JOIN `ps_product_lang` `pl` ON p.`id_product` = pl.`id_product`
 LEFT JOIN ps_stock_available stock
			ON (stock.id_product = p.id_product AND stock.id_product_attribute = 0 AND stock.id_shop_group = 1  AND stock.id_shop = 0  )
LEFT JOIN `ps_category_product` `cp` ON p.`id_product` = cp.`id_product`
WHERE (product_shop.`active` = 1 AND product_shop.saved = 1  ) AND (product_shop.`visibility` IN ("both", "catalog")) AND (pl.`id_shop` = 4 AND pl.`id_lang` = 3) AND (cp.`id_category` = 13) AND ( EXISTS(SELECT 1 FROM `ps_category_product` cp
                        JOIN `ps_category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` = 1)
                        WHERE cp.`id_product` = p.`id_product`))
ORDER BY pl.`name` ASC