23 -

SELECT p.products_id, p.only_show, p.show_price_tag, p.hide_price, 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_5 as price_del , p.products_other_tax_5 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 /* //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 = '315' AND (p.products_quantity - IF(p.products_min_stock_to_sell IS NULL,0,p.products_min_stock_to_sell)) > 0 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

[TEP STOP]