[SOLVED] Only products marked "On Sale" in prices-drop.php

[w3bsolutions]

Hello everyone,

 

I noticed that prices-drop.php shows all the products that have a discount rule, but is there any way to make it show only the products marked as "On Sale" only on the product configuration page, not all the products with a discount rule?

 

Any help is appreciated.

Edited October 25, 2012 by fire2 (see edit history)

[shacker]

open classes/product.php

 

find

 

 

public static function getPricesDrop($id_lang, $pageNumber = 0, $nbProducts = 10, $count = false, $orderBy = NULL, $orderWay = NULL, $beginning = false, $ending = false)

{

if (!Validate::isBool($count))

die(Tools::displayError());

 

if ($pageNumber < 0) $pageNumber = 0;

if ($nbProducts < 1) $nbProducts = 10;

if (empty($orderBy) || $orderBy == 'position') $orderBy = 'price';

if (empty($orderWay)) $orderWay = 'DESC';

if ($orderBy == 'id_product' OR $orderBy == 'price' OR $orderBy == 'date_add')

$orderByPrefix = 'p';

elseif ($orderBy == 'name')

$orderByPrefix = 'pl';

if (!Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay))

die (Tools::displayError());

$currentDate = date('Y-m-d H:i:s');

$ids_product = self::_getProductIdByDate((!$beginning ? $currentDate : $beginning), (!$ending ? $currentDate : $ending));

 

$groups = FrontController::getCurrentCustomerGroups();

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

 

if ($count)

{

$sql = '

SELECT COUNT(DISTINCT p.`id_product`) AS nb

FROM `'._DB_PREFIX_.'product` p

WHERE p.`active` = 1

AND p.`show_price` = 1

'.((!$beginning AND !$ending) ? ' AND p.`id_product` IN('.((is_array($ids_product) AND sizeof($ids_product)) ? implode(', ', array_map('intval', $ids_product)) : 0).')' : '').'

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.'

)';

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);

return (int)($result['nb']);

}

$sql = '

SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`,

pl.`name`, p.`ean13`, p.`upc`, i.`id_image`, il.`legend`, t.`rate`, m.`name` AS manufacturer_name,

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` p

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`)

LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)

WHERE 1

AND p.`active` = 1

AND p.`show_price` = 1

'.((!$beginning AND !$ending) ? ' AND p.`id_product` IN ('.((is_array($ids_product) AND sizeof($ids_product)) ? implode(', ', $ids_product) : 0).')' : '').'

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) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'`'.' '.pSQL($orderWay).'

LIMIT '.(int)($pageNumber * $nbProducts).', '.(int)($nbProducts);

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);

if ($orderBy == 'price')

Tools::orderbyPrice($result,$orderWay);

if (!$result)

return false;

return Product::getProductsProperties($id_lang, $result);

}

 

 

 

simply add in the code this

 

AND p.`on_sale` = 1 after AND p.`active` = 1

[w3bsolutions]

Hi

 

Thanks for your answer, but if I have a product marked "On Sale" but I don't have any discount rule set on it the product does not show up. How would I fix that? I want to show all products marked "on Sale" , without checking if it has a discount rule or not.

[w3bsolutions]

Ok I solved it removing the lines

 

'.((!$beginning AND !$ending) ? ' AND p.`id_product` IN ('.((is_array($ids_product) AND sizeof($ids_product)) ? implode(', ', $ids_product) : 0).')' : '').'

[mehnihma]

Is there a way to display on sale and prices drop ond the same page?

Or how can I create different page just with product on sale?

[noesac]

Thank god I would have spent forever trying to figure this out for myself.

 

I updated my query to support both a price adjustment or the "on sale" flag, by adding an "OR" statement instead of an "AND" to the bottom:

 

 LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
 WHERE 1
 AND p.`active` = 1
 AND p.`show_price` = 1
 '.((!$beginning AND !$ending) ? ' AND p.`id_product` IN ('.((is_array($ids_product) AND sizeof($ids_product)) ? implode(', ', $ids_product) : 0).')' : '').'
 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.'
 )


 OR p.`on_sale` = 1

[shacker]

thanks for the code

[mehnihma]

Hi does anyone has the full code to show on sale and prices drop on same page.

When I add OR p.on_sale = 1 it works but it does not show all pages just one page with products on sale?