product-variations-fsorting-fix.diff

product-variations-fsorting-fix.diff

diff --git a/app/addons/bestsellers/func.php b/app/addons/bestsellers/func.php
index 57892b4c6a6..b2880c54680 100644
--- a/app/addons/bestsellers/func.php
+++ b/app/addons/bestsellers/func.php
@@ -15,6 +15,8 @@
 use Tygh\Registry;
 use Tygh\Enum\UserTypes;
 use Tygh\Enum\ProductTracking;
+use Tygh\Enum\ObjectStatuses;
+use Tygh\Enum\YesNo;
 
 if (!defined('BOOTSTRAP')) { die('Access denied'); }
 
@@ -175,29 +177,42 @@ function fn_bestsellers_get_products(&$params, &$fields, &$sortings, &$condition
             $fields['bs_sales_discount'] = db_quote('100 - ((?p * 100) / products.list_price) AS sales_discount', fn_ult_build_sql_product_price_field($ult_prices_table_alias));
 
             $price_usergroup_cond = db_quote(
-                " AND {$ult_prices_table_alias}.usergroup_id IN (?n)",
+                ' AND ?p.usergroup_id IN (?n)',
+                $ult_prices_table_alias,
                 ($params['area'] == 'A')
                     ? USERGROUP_ALL
-                    : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids'])
+                    : array_merge([USERGROUP_ALL], $auth['usergroup_ids'])
+            );
+            $join .= db_quote(
+                ' LEFT JOIN ?:ult_product_prices as ?p'
+                . ' ON ?p.product_id = products.product_id'
+                    . ' AND ?p.lower_limit = 1 ?p'
+                    . ' AND ?p.company_id = ?i',
+                $ult_prices_table_alias,
+                $ult_prices_table_alias,
+                $ult_prices_table_alias,
+                $price_usergroup_cond,
+                $ult_prices_table_alias,
+                Registry::get('runtime.company_id')
             );
-            $join .= db_quote(" LEFT JOIN ?:ult_product_prices as {$ult_prices_table_alias} ON {$ult_prices_table_alias}.product_id = products.product_id AND {$ult_prices_table_alias}.lower_limit = 1 ?p AND {$ult_prices_table_alias}.company_id = ?i", $price_usergroup_cond, Registry::get('runtime.company_id'));
         } else {
-            $fields[] = '100 - ((prices.price * 100) / products.list_price) AS sales_discount';
+            $fields[] = 'MAX(100 - ((prices.price * 100) / products.list_price)) AS sales_discount';
         }
     }
 
     // in stock conditions are applied if out of stock products are not cut-off in ::fn_get_products
     if (
         !empty($params['similar_in_stock'])
-        && $params['similar_in_stock'] == 'Y'
+        && $params['similar_in_stock'] == YesNo::YES
         && !(
-            Registry::get('settings.General.inventory_tracking') == 'Y'
-            && Registry::get('settings.General.show_out_of_stock_products') == 'N'
+            Registry::get('settings.General.inventory_tracking') == YesNo::YES
+            && Registry::get('settings.General.show_out_of_stock_products') == YesNo::NO
         )
     ) {
         $condition .= db_quote(
-            " AND (IF(products.tracking = ?s, inventory_b.amount >= 1, products.amount >= 1) OR (products.tracking = 'D'))",
-            ProductTracking::TRACK_WITH_OPTIONS
+            ' AND (IF(products.tracking = ?s, inventory_b.amount >= 1, products.amount >= 1) OR (products.tracking = ?s))',
+            ProductTracking::TRACK_WITH_OPTIONS,
+            ObjectStatuses::DISABLED
         );
 
         $join .= " LEFT JOIN ?:product_options_inventory as inventory_b ON inventory_b.product_id = products.product_id AND inventory_b.amount >= 1";