SELECT
p.products_id,
p.only_show,
p.show_price_tag,
p.hide_price,
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,
pd.url_amigable as url_amigable,
p.products_model,
p.products_auxiliar_code,
p.gtin,
p.sku,
p.products_cant_package_discount,
CAST((p.products_quantity - IF(p.products_min_stock_to_sell IS NULL, 0, p.products_min_stock_to_sell)) AS DOUBLE) 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 , p.products_price_2 as price_del , p.products_other_tax_2 as other_tax_del ,
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,
@categoryDiscountPrice := p.products_price as category_discount_var,
@specialPriceBaseVar := IF(s.specials_new_products_price > 0, s.specials_new_products_price, NULL) as special_price_var,
@finalDiscountPriceVar := IF(CAST(@specialPriceBaseVar AS DECIMAL(10, 2)) < CAST(@categoryDiscountPrice AS DECIMAL(10, 2)), @specialPriceBaseVar , @categoryDiscountPrice) as final_discount_price_var,
@finalDiscountTypeVar := IF(CAST(@specialPriceBaseVar AS DECIMAL(10, 2)) < CAST(@categoryDiscountPrice AS DECIMAL(10, 2)), 'special' , 'category') as final_discount_type_var,
IF(CAST(@finalDiscountPriceVar AS DECIMAL(10, 2)) < CAST(p.products_price AS DECIMAL(10, 2)), @finalDiscountPriceVar , p.products_price) AS final_price,
@useFinalDiscountPriceVar := IF(CAST(@finalDiscountPriceVar AS DECIMAL(10, 2)) < CAST(p.products_price AS DECIMAL(10, 2)), 1 , 0) as use_final_discount_price_var,
IF(@useFinalDiscountPriceVar = 1 && @finalDiscountTypeVar = 'special', @specialPriceBaseVar , NULL) as specials_new_products_price,
p.products_price as list_price,
p.products_price AS without_discount_price,
IF(s.status && 0 > 0, 0, NULL) AS offer_package,0 AS package_other_tax
/*//DS 20240910 ahora si utilizamos descuentos nuevos tambien vienen joins, eliminamos el control de que si viene agregamos los campos de descuentos viejos, porque ahora ya vienen en si tienen que aplicarse. */
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
/* //DS 20240925 agregamos que tenga en cuenta el orden de la categoría tomando solo la categoría principal main_category_id*/
LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id AND p2c.categories_id = main_category_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 LEFT JOIN specials s ON p.products_id = s.products_id
AND s.status = 1
AND (s.expires_date IS null OR DATE(s.expires_date) = '0000-00-00' OR DATE(CURDATE()) <= DATE(s.expires_date) )
AND ( s.start_date IS null OR DATE(CURDATE()) >= DATE(s.start_date) )
WHERE p.products_status = '1'
AND p.products_id IN (15561,15562,15563,15564,15565,15566)
AND pd.products_id = p.products_id
AND pd.language_id = '2'
/* //DS 20241120 Al momento de mostrar el producto vamos a tener en cuenta que se pueden mostrar productos sin fabricantes y si el producto tiene fabricante este fabricante tiene que esta habilitado */
AND (mn.manufacturers_id is null OR mn.manufacturers_status = 1)
GROUP BY p.products_id
, pov1.products_options_values_id, pov2.products_options_values_id, pov3.products_options_values_id
ORDER BY p.order_in_grouper , pov1.position asc, pov2.position asc, pov3.position asc
