Consulta Sql Para Combinaciones Con Precio

[naniuska]

Hola, Tengo creadas dos consultas SQL que he encontrado por el foro: una para listado de productos y otro para combinaciones.

 

El problema que tengo es que ninguna de las dos me devuelve ni Cantidad ni Precio.

 

Supongo que para el precio de las combinaciones seria algo tipo

SELECT * FROM `sh_attribute_impact`

 

Pero no consigo que se muestren estos dos valores en ninguna de las dos consultas.

 

Os muestro la consulta para productos:

 

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.upc, p.ecotax, p.weight, p.quantity, 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, p.online_only, p.condition, p.id_shop_default FROM sh_product p LEFT JOIN sh_product_lang pl ON (p.id_product = pl.id_product) LEFT JOIN sh_category_product cp ON (p.id_product = cp.id_product) LEFT JOIN sh_category_lang cl ON (cp.id_category = cl.id_category) LEFT JOIN sh_category c ON (cp.id_category = c.id_category) LEFT JOIN sh_product_tag pt ON (p.id_product = pt.id_product) WHERE pl.id_lang = 1 AND cl.id_lang = 1 AND p.id_shop_default = 1 AND c.id_shop_default = 1 GROUP BY p.id_product

 

 

La de Combinaciones:

 

SELECT p.id_product, pl.name, pa.id_product_attribute, pa.id_product, pac.id_attribute, pac.id_product_attribute, al.id_attribute, al.id_lang, al.name, a.id_attribute, a.id_attribute_group, agl.id_attribute_group, p.quantity, agl.name FROM sh_product p LEFT JOIN sh_product_lang pl ON (p.id_product = pl.id_product) LEFT JOIN sh_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN sh_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN sh_attribute_lang al ON (pac.id_attribute = al.id_attribute) LEFT JOIN sh_attribute a ON (al.id_attribute = a.id_attribute) LEFT JOIN sh_attribute_group_lang agl ON (a.id_attribute_group = agl.id_attribute_group) WHERE pl.id_lang = 1 GROUP BY p.id_product, pac.id_attribute

 

 

Alguna idea???????????

[naniuska]

me devuelve el campo precio como 0.

No hay manera de exportar los datos a csv.....

He probado con Moussiq y me pasa lo mismo...

alguna idea?

[naniuska]

Resuelto. Me autocontesto

Éste es el código, hay que cambiar ps_ por el prefijo de las tablas que cada uno tenga

________________________

 

SELECT

p.id_product AS `ID`,

p.active AS `Active (0/1)`,

pl.`name` AS `Name`,

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 #`,

pps.product_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`,

pq.quantity AS `Quantity`,

p.minimal_quantity AS `Minimal quantity`,

p.visibility AS `Visibility`,

p.additional_shipping_cost AS `Additional shipping cost`,

p.unity AS `Unit for the unit price`,

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`), ':' , (f.position), ':' , (fv.custom))) SEPARATOR ',') AS `Feature (Name:Value:Position:Customized)`,

p.online_only AS `Available online only (0 = No, 1 = Yes)`,

p.condition AS `Condition`,

0 AS `Customizable (0 = No, 1 = Yes)`,

0 AS `Uploadable files (0 = No, 1 = Yes)`,

0 AS `Text fields (0 = No, 1 = Yes)`,

'' AS `Action when out of stock`,

p.id_shop_default AS `ID / Name of shop`,

p.advanced_stock_management AS `Advanced Stock Management`,

'' AS `Depends on stock`,

'' 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_category c ON (cp.id_category = c.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_product_supplier pps ON (p.id_supplier = pps.id_supplier)

LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)

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

WHERE pl.id_lang = 1

AND cl.id_lang = 1

AND p.id_shop_default = 1

AND c.id_shop_default = 1

GROUP BY p.id_product

[naniuska]

Finalmente compre este modulo por 10€

 

http://addons.prestashop.com/es/importacion-exportacion-de-datos/19214-export-pro-products-combinations-orders-etc.html

 

me devuelve un excel con el mismo formato de importacion de productos: o por producto o por combinaciones (con id de producto)