SELECT p.products_id,
po.products_options_id,
po.products_options_name,
po.display_type,
pov.products_options_values_id,
pov.products_options_values_name,
pov.parameter_1,
p.products_id,
p.only_show,
pd.products_name,
pd.products_description,
pd.products_grouper_name,
p.products_model,
( p.products_quantity - IF(p.products_min_stock_to_sell IS NULL,0,p.products_min_stock_to_sell) ) as products_quantity,
p.products_quantity_future,
p.products_ignore_stock,
p.products_image,
p.g_type_image_id,
p.products_unidad_compra,
p.products_unit,
p.products_qty_vta_min,
p.products_qty_vta_max,
p.products_cant_package_discount,
p.products_bulk_type,
p.products_tag,
pd.products_url,
p.products_price as products_price,
p.products_tax_class_id,
p.products_date_added,
p.products_date_available,
p.manufacturers_id,
p.product_grouper_id,
p.discount_id,
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.installment_view,
p.show_other_currency,
p.custom_id,
p.products_weight,
p.show_price_kg,
/* //DS 20230509 Se movio la coma dentro del if porque daba error el query */
0 as offer_package,
pd.products_url_text,
IF(p.products_unidad_compra > 1 , p.products_unidad_compra, 1) as products_unidad_compra ,
IF(s.status && s.specials_new_products_price > 0, s.specials_new_products_price, NULL) as specials_new_products_price,
IF(s.status && s.specials_new_products_price > 0, s.specials_new_products_price, p.products_price) as final_price,
p.products_unidad_compra as products_unidad_compra_label
from products_options po
left join products_options_values_to_products_options povtpo on povtpo.products_options_id = po.products_options_id
left join products_options_values pov on pov.products_options_values_id = povtpo.products_options_values_id
left join products p on p.g_tipo_1 = pov.products_options_values_id
inner join products_to_categories p2c on p2c.products_id = p.products_id
inner join categories c on p2c.categories_id = c.categories_id
left join specials s ON p.products_id = s.products_id 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) ), products_description pd
where po.products_options_id = 13
and pov.products_options_values_id = p.g_tipo_1
and p.products_id IN (2616, 2617)
and pd.language_id = '2'
and pd.products_id = p.products_id
and c.categories_status = 1
GROUP BY p.products_id, pov.products_options_values_id
ORDER BY pov.position asc
