Best Sales - Pagination Wrong and Out of Stock Message

[web.geek]

[1.4.0.12]

The best sellers page is returning all of the products that have sales, but I am getting two strange behaviors: some of the products are listed "Out of Stock," and pagination does not adhere to the defined products per page rules.

So I went snooping around the code which led me ultimately to the BestSalesController. I was comparing it to the NewProductsController which works as intended (no non-new products, displays 5 per page, correct stock reporting), and found that the BestSalesController overrides the preProcess() function, but not the process() function; while the NewProductsController does the opposite.

Is this intended or important? Regardless, I went ahead and compared the code from the two functions and found many similarities, so I focused there.

I seemed to fix the pagination issue by changing the BestSalesController call to ProductSale::getBestSales() to more closely resemble the NewProductsController call to Product:getNewProducts. I removed the global variable declaration in line 43 and changed the 2nd and 3rd parameters of the function call. Before:

global $orderBy, $orderWay, $p, $n;
$this->smarty->assign(array(
   'products' => ProductSale::getBestSales((int)($this->cookie->id_lang), (int)($p) - 1, (int)($n), $this->orderBy, $this->orderWay),

After:

$this->smarty->assign(array(
   'products' => ProductSale::getBestSales((int)($this->cookie->id_lang), (int)($this->p) - 1, (int)($this->n), $this->orderBy, $this->orderWay),

and also removed the global variable declaration line 43. The new BestSalesController.php in my /override/controllers folder looks like:

<?php
class BestSalesController extends BestSalesControllerCore
{
   public function preProcess()
   {
       $this->productSort();
       $nbProducts = (int)(ProductSale::getNbSales());
       $this->pagination($nbProducts);

       $this->smarty->assign(array(
           'products' => ProductSale::getBestSales((int)($this->cookie->id_lang), (int)($this->p) - 1, (int)($this->n), $this->orderBy, $this->orderWay),
           'add_prod_display' => Configuration::get('PS_ATTRIBUTE_CATEGORY_DISPLAY'),
           'nbProducts' => (int)$nbProducts,
           'homeSize' => Image::getSize('home')
       ));
   }
}
?>

I will report this as a bug, and see where it goes.

The second problem seems to have been brought upon myself. Early on, I was trying to come up with a solution that would force customers to always click "View" when a product had selectable attributes. The workaround involved setting the cache_default_attribute to the id_product_attribute of the default attribute for the product. This effectively removed the "Add to Cart" button and I replaced it with a customization message.

Several upgrades later, a few new products added, some attribute changes, and a few sales; I realized that some of the products were showing "Out of Stock" on the Top Sellers page. They were fine everywhere else. After investigating, I discovered that the cache_default_attribute value in the product table for those specific products had become outdated. By resetting those values, the products no longer registered as "Out of Stock."

If anyone is interested, I'll post the query I have now saved to reset that value for all products

Anybody know what purpose that cache_default_attribute is supposed to serve and how it usually gets set?

[airelibre]

Hi,

I have the same problem with the cache_default_attribute value... what is the query you did to solve the problem ?

Thank you !

[web.geek]

The following SQL code will update the cache_default_attribute column with the default attribute id for all products that have defined attributes:

UPDATE `DB_PREFIX_product` AS p, `DB_PREFIX_product_attribute` AS pa 
SET p.`cache_default_attribute` = pa.`id_product_attribute` 
WHERE p.`id_product` = pa.`id_product` AND pa.`default_on` = 1;

Replace DB_PREFIX with your appropriate value (e.g. ps).

[airelibre]

Thank you !

Did you report the bug ?

[tillidan]

I got the same problem, "cache_default_attribute" doesn't work ok. I answered it in third party modules forum and if you let me I post this solution there. Also a few days ago I reported this to Prestashop Bugs. Do you know if this bug could be fix in the next version?

Thanks!
Regards,
Javier

[rav.tan]

Hi Web.Geek,
I am only having problem with the Out of Stock Message. Possible for you to give us some pointers on how to solve it? Zoom into that issue?

Using 1.4.0.17
BestSalesController

public function process()
   {
       $this->productSort();
       $nbProducts = (int)(ProductSale::getNbSales());
       $this->pagination($nbProducts);

       global $orderBy, $orderWay, $p, $n;
       self::$smarty->assign(array(
           'products' => ProductSale::getBestSales((int)(self::$cookie->id_lang), (int)($p) - 1, (int)($n), $this->orderBy, $this->orderWay),
           'add_prod_display' => Configuration::get('PS_ATTRIBUTE_CATEGORY_DISPLAY'),
           'nbProducts' => $nbProducts,
           'homeSize' => Image::getSize('home')
       ));
   }

productsale.php

static public function getBestSales($id_lang, $pageNumber = 0, $nbProducts = 10, $orderBy=NULL, $orderWay=NULL)
   {
       if ($pageNumber < 0) $pageNumber = 0;
       if ($nbProducts < 1) $nbProducts = 10;
       if (empty($orderBy) || $orderBy == 'position') $orderBy = 'sales';
       if (empty($orderWay)) $orderWay = 'DESC';

       $groups = FrontController::getCurrentCustomerGroups();
       $sqlGroups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');

       $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
       SELECT p.*,
           pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`,
           i.`id_image`, il.`legend`,
           ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`,
           DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 AS new
       FROM `'._DB_PREFIX_.'product_sale` ps
       LEFT JOIN `'._DB_PREFIX_.'product` p ON ps.`id_product` = p.`id_product`
       LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)($id_lang).')
       LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
       LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)($id_lang).')
       LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
                                                  AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
                                                     AND tr.`id_state` = 0)
       LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
       WHERE p.`active` = 1
       AND p.`id_product` IN (
           SELECT cp.`id_product`
           FROM `'._DB_PREFIX_.'category_group` cg
           LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_category` = cg.`id_category`)
           WHERE cg.`id_group` '.$sqlGroups.'
       )
       ORDER BY '.(isset($orderByPrefix) ? $orderByPrefix.'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
       LIMIT '.(int)($pageNumber * $nbProducts).', '.(int)($nbProducts));

       if ($orderBy == 'price')
           Tools::orderbyPrice($result,$orderWay);
       if (!$result)
           return false;
       return Product::getProductsProperties($id_lang, $result);
   }

[phunter121]

Thanks for the SQL to reset the cache_default_attribute. I found I had to also run the following SQL first, to reset all products:

UPDATE `pss_product` SET `cache_default_attribute` = 0

[jennywill]

I have this problem but only on best-sales.php, prices-drop.php and new-products.php on 1.4.1. Category pagination does work fine and I am not getting the out of stock problem. I have tried these changes with no avail. I even looked in the controller files of the newest version 1.4.3 and the only one different is best-sales.php, whereby prices-drop.php and new-products.php code is the same. Since they are all broken the fix isnt there. Any help? Thanks!