SELECT
p.products_id,
p.only_show,
p.show_price_tag,
pov1.products_options_values_id AS pov_id_1,
pov1.products_options_values_name AS pov_name_1,
pov1.parameter_1 AS pov_parameter_1,
po1.products_options_id AS po_id_1,
po1.products_options_name AS po_name_1,
po1.display_type AS po_dtype_1,
pov2.products_options_values_id AS pov_id_2,
pov2.products_options_values_name AS pov_name_2,
pov2.parameter_1 AS pov_parameter_2,
po2.products_options_id AS po_id_2,
po2.products_options_name AS po_name_2,
po2.display_type AS po_dtype_2,
pov3.products_options_values_id AS pov_id_3,
pov3.products_options_values_name AS pov_name_3,
pov3.parameter_1 AS pov_parameter_3,
po3.products_options_id AS po_id_3,
po3.products_options_name AS po_name_3,
po3.display_type AS po_dtype_3,
pd.products_warranty,
pd.products_name,
pd.products_description,
pd.products_grouper_name,
pd.products_url_text,
p.products_model,
p.products_auxiliar_code,
p.gtin,
p.sku,
p.products_cant_package_discount,
(p.products_quantity - IF(p.products_min_stock_to_sell IS NULL,0,p.products_min_stock_to_sell)) AS products_quantity,
p.products_min_stock_to_sell,
p.products_quantity_future,
p.products_ignore_stock,
p.products_min_stock,
p.products_image,
p.g_type_image_id,
p.banner_3d,
p.products_price_2,
p.products_price_3,
p.products_price_4,
p.products_price_5,
p.products_price_6,
p.products_price_7,
p.products_price_8,
p.products_price_9,
p.products_price_10,
p.show_price_kg,
p.products_qty_vta_min,
p.products_qty_vta_max,
p.show_other_currency,
pd.products_url,
p.products_price AS products_price,
p.products_other_tax_1 AS products_other_tax,
p.products_tax_class_id,
p.products_date_added,
p.products_date_available,
p.manufacturers_id,
p.discount_id,
p.cubic_meters,
c.categories_id,
c.show_stock,
c.categories_alto,
c.categories_ancho,
c.categories_profundidad,
c.categories_peso,
p.products_alto,
p.products_ancho,
p.products_profundidad,
p.products_weight ,
mn.manufacturers_name,
p.video_link,
p.products_bulk_type,
p.products_tag,
p.products_purchase_equivalence,
p.installment_view,
p.free_shipping,
p.custom_id,
p.products_unit,
/* //DS PENDING 20231012 revisar este control que está un poco raro a demas de que products_unidad_compra estaba duplicado (sin dar error)
//DS 20240321 cambiamos este control, si p.products_unit es mayor a 0 usamos el campo, sino seteamos 1, antes comparaba si era mayor a 1, esto generaba error en el input de cantidad en los casos que se vende de a gramos (ej: 0.200 se vende de a 200 gramos) porque sumaba de a 1 cada vez que aumentabamos la cantidad
*/
If(p.products_unit > 0, p.products_unit, 1) AS products_unidad_compra,
p.products_unidad_compra AS products_unidad_compra_label
, p.g_tipo_1, p.g_tipo_2, p.g_tipo_3, p.product_grouper_id as id_grouper, mpg.name as grouper_name, mpg.description as grouper_desc, mpg.type_1 as grouper_type_1, mpg.type_2 as grouper_type_2, mpg.type_3 as grouper_type_3,
IF(IF(s.status && s.specials_new_products_price > 0, s.specials_new_products_price, p.products_price) < p.products_price, IF(s.status && s.specials_new_products_price > 0, s.specials_new_products_price, p.products_price) ,p.products_price) as final_price, IF(s.specials_new_products_price > 0, s.specials_new_products_price, NULL) as specials_new_products_price , p.products_price as list_price ,
IF(s.status && 0 > 0, 0, NULL) AS offer_package,
0 AS package_other_tax
FROM products_description pd, products p
LEFT JOIN products_options_values pov1 ON pov1.products_options_values_id = p.g_tipo_1
LEFT JOIN products_options_values_to_products_options povtpo1 ON povtpo1.products_options_values_id = pov1.products_options_values_id
LEFT JOIN products_options po1 ON po1.products_options_id = povtpo1.products_options_id
LEFT JOIN products_options_values pov2 ON pov2.products_options_values_id = p.g_tipo_2
LEFT JOIN products_options_values_to_products_options povtpo2 ON povtpo2.products_options_values_id = pov2.products_options_values_id
LEFT JOIN products_options po2 ON po2.products_options_id = povtpo2.products_options_id
LEFT JOIN products_options_values pov3 ON pov3.products_options_values_id = p.g_tipo_3
LEFT JOIN products_options_values_to_products_options povtpo3 ON povtpo3.products_options_values_id = pov3.products_options_values_id
LEFT JOIN products_options po3 ON po3.products_options_id = povtpo3.products_options_id
LEFT JOIN specials s ON p.products_id = s.products_id
AND s.status = 1 AND
(s.expires_date IS null OR DATE(CURDATE()) <= DATE(s.expires_date) ) AND ( s.start_date IS null OR DATE(CURDATE()) >= DATE(s.start_date) )
LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id
LEFT JOIN categories c ON p2c.categories_id = c.categories_id
LEFT JOIN manufacturers mn ON p.manufacturers_id = mn.manufacturers_id
left join mods_product_groupers mpg on p.product_grouper_id = mpg.id
WHERE p.products_status = '1'
AND p.products_id IN (3218,3283,3284,3285)
AND pd.products_id = p.products_id
AND pd.language_id = '2'
GROUP BY p.products_id
, pov1.products_options_values_id, pov2.products_options_values_id, pov3.products_options_values_id
ORDER BY pov1.position asc, pov2.position asc, pov3.position asc
