[Solved] Sale Products Page

SuperCharlie · August 14, 2012

I looked but might have missed it I suppose, but there dosent seem to be a sale page like the new products page. What Im looking for or needing a pointer for is how to get a product list for only sale items... the ones that are ticked On Sale in the admin product detail page.

My gut tells me copy and modify the new items controllers, templates, yada yada and modify/rename/hammer it to show sale items.. but maybe Im missing something easier.

any pointers on how to get from here to there?

Thanks,

SC

Oh.. a ps self-back-pat. After only 2-3 months up, we hit the front page of google for 2 of our biggest search terms. (#8 for one term, #2 for the other) Just found out today. Woohoo!!

Edited August 15, 2012 by SuperCharlie (see edit history)

shacker · August 15, 2012

you have a page for this

prices-drop.php

SuperCharlie · August 15, 2012

Awesome. Thanks Shacker

shacker · August 15, 2012

your welcome.

regards

w3bsolutions · September 10, 2012

Hello, I noticed that this page 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" on the product configuration page?

Any help is appreciated

mehnihma · October 26, 2012

I am interested in this also

shacker · October 26, 2012

in what version of PS?

mehnihma · October 26, 2012

I use PS 1.5.1

mehnihma · October 28, 2012

Is this possible?

shacker · November 1, 2012

edit in classes/product.php

find this line

public static function getPricesDrop

and in this line,

AND (pa.id_product_attribute IS NULL OR product_attribute_shop.default_on = 1)

add

AND p.on_sale = 1

mehnihma · November 1, 2012

I have tried this but after that change I get that there is no products,

Can you maybe post whole products.php?

Thanks

mehnihma · November 1, 2012

I would really like to know how to have it both on prices drop page

shacker · November 1, 2012

put OR instead of and

OR p.on_sale = 1

mehnihma · November 1, 2012

I have tried and it doesn't work but I have found another way by adding additional query

Thanks

shacker · November 1, 2012

put the query so other users can check.

thanks

mehnihma · November 1, 2012

In classes/Product.php

from:

/**
* Get prices drop
*
* @param integer $id_lang Language id
* @param integer $pageNumber Start from (optional)
* @param integer $nbProducts Number of products to return (optional)
* @param boolean $count Only in order to get total number (optional)
* @return array Prices drop
*/
public static function getPricesDrop($id_lang, $page_number = 0, $nb_products = 10, $count = false,
 $order_by = null, $order_way = null, $beginning = false, $ending = false, Context $context = null)
{
 if (!Validate::isBool($count))
  die(Tools::displayError());
 if (!$context) $context = Context::getContext();
 if ($page_number < 0) $page_number = 0;
 if ($nb_products < 1) $nb_products = 10;
 if (empty($order_by) || $order_by == 'position') $order_by = 'price';
 if (empty($order_way)) $order_way = 'DESC';
 if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add'  || $order_by == 'date_upd')
  $order_by_prefix = 'p';
 else if ($order_by == 'name')
  $order_by_prefix = 'pl';
 if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way))
  die (Tools::displayError());
 $current_date = date('Y-m-d H:i:s');
 $ids_product = Product::_getProductIdByDate((!$beginning ? $current_date : $beginning), (!$ending ? $current_date : $ending), $context);
 $tab_id_product = array();
 foreach ($ids_product as $product)
  if (is_array($product))
$tab_id_product[] = (int)$product['id_product'];
  else
$tab_id_product[] = (int)$product;
 $front = true;
 if (!in_array($context->controller->controller_type, array('front', 'modulefront')))
  $front = false;
 $groups = FrontController::getCurrentCustomerGroups();
 $sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');
 if ($count)
 {
  $sql = 'SELECT COUNT(DISTINCT p.`id_product`) AS nb
 FROM `'._DB_PREFIX_.'product` p
 '.Shop::addSqlAssociation('product', 'p').'
 WHERE product_shop.`active` = 1
  AND product_shop.`show_price` = 1
  '.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '').'
  '.((!$beginning && !$ending) ? 'AND p.`id_product` IN('.((is_array($tab_id_product) && count($tab_id_product)) ? implode(', ', $tab_id_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` '.$sql_groups.'
  )';
  $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
  return (int)$result['nb'];
 }
 if (strpos($order_by, '.') > 0)
 {
  $order_by = explode('.', $order_by);
  $order_by = pSQL($order_by[0]).'.`'.pSQL($order_by[1]).'`';
 }
 $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, product_attribute_shop.id_product_attribute,
 pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`,
 pl.`name`, image_shop.`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
'.Shop::addSqlAssociation('product', 'p').'
LEFT JOIN '._DB_PREFIX_.'product_attribute pa ON (pa.id_product = p.id_product)
'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on=1').'
'.Product::sqlStock('p', 0, false, $context->shop).'
LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (
 p.`id_product` = pl.`id_product`
 AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
)
LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product`)'.
Shop::addSqlAssociation('image', 'i', false, 'image_shop.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 (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`
 AND tr.`id_country` = '.(int)Context::getContext()->country->id.'
 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 product_shop.`active` = 1
AND product_shop.`show_price` = 1
AND ((image_shop.id_image IS NOT NULL OR i.id_image IS NULL) OR (image_shop.id_image IS NULL AND i.cover=1))
'.($front ? ' AND p.`visibility` IN ("both", "catalog")' : '').'
'.((!$beginning && !$ending) ? ' AND p.`id_product` IN ('.((is_array($tab_id_product) && count($tab_id_product)) ? implode(', ', $tab_id_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` '.$sql_groups.'
)
AND (pa.id_product_attribute IS NULL OR product_attribute_shop.default_on = 1)
ORDER BY '.(isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').pSQL($order_by).' '.pSQL($order_way).'
LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
 if ($order_by == 'price')
  Tools::orderbyPrice($result, $order_way);
 if (!$result)
  return false;
 return Product::getProductsProperties($id_lang, $result);
}

to:

/**
* Get prices drop
*
* @param integer $id_lang Language id
* @param integer $pageNumber Start from (optional)
* @param integer $nbProducts Number of products to return (optional)
* @param boolean $count Only in order to get total number (optional)
* @return array Prices drop
*/
public static function getPricesDrop($id_lang, $page_number = 0, $nb_products = 10, $count = false,
 $order_by = null, $order_way = null, $beginning = false, $ending = false, Context $context = null)
{
 if (!Validate::isBool($count))
  die(Tools::displayError());
 if (!$context) $context = Context::getContext();
 if ($page_number < 0) $page_number = 0;
 if ($nb_products < 1) $nb_products = 10;
 if (empty($order_by) || $order_by == 'position') $order_by = 'price';
 if (empty($order_way)) $order_way = 'DESC';
 if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add')
  $order_by_prefix = 'p';
 else if ($order_by == 'name')
  $order_by_prefix = 'pl';
 if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way))
  die (Tools::displayError());
 $current_date = date('Y-m-d H:i:s');
 $ids_product = Product::_getProductIdByDate((!$beginning ? $current_date : $beginning), (!$ending ? $current_date : $ending), $context);
 $tab_id_product = array();
 foreach ($ids_product as $product)
  if (is_array($product))
$tab_id_product[] = (int)$product['id_product'];
  else
$tab_id_product[] = (int)$product;
 $front = true;
 if (!in_array($context->controller->controller_type, array('front', 'modulefront')))
  $front = false;
 $groups = FrontController::getCurrentCustomerGroups();
 $sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');
 if ($count)
 {
  $sql = 'SELECT COUNT(DISTINCT p.`id_product`) AS nb
 FROM `'._DB_PREFIX_.'product` p
 '.Shop::addSqlAssociation('product', 'p').'
 WHERE product_shop.`active` = 1
  AND product_shop.`show_price` = 1
  '.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '').'
  '.((!$beginning && !$ending) ? 'AND p.`id_product` IN('.((is_array($tab_id_product) && count($tab_id_product)) ? implode(', ', $tab_id_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` '.$sql_groups.'
  )';
  $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
  return (int)$result['nb'];
 }
 if (strpos($order_by, '.') > 0)
 {
  $order_by = explode('.', $order_by);
  $order_by = pSQL($order_by[0]).'.`'.pSQL($order_by[1]).'`';
 }
 $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, product_attribute_shop.id_product_attribute,
 pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`,
 pl.`name`, image_shop.`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
'.Shop::addSqlAssociation('product', 'p').'
LEFT JOIN '._DB_PREFIX_.'product_attribute pa ON (pa.id_product = p.id_product)
'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on=1').'
'.Product::sqlStock('p', 0, false, $context->shop).'
LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (
 p.`id_product` = pl.`id_product`
 AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
)
LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product`)'.
Shop::addSqlAssociation('image', 'i', false, 'image_shop.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 (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`
 AND tr.`id_country` = '.(int)Context::getContext()->country->id.'
 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 product_shop.`active` = 1
AND product_shop.`show_price` = 1
AND ((image_shop.id_image IS NOT NULL OR i.id_image IS NULL) OR (image_shop.id_image IS NULL AND i.cover=1))
'.($front ? ' AND p.`visibility` IN ("both", "catalog")' : '').'
'.((!$beginning && !$ending) ? ' AND p.`id_product` IN ('.((is_array($tab_id_product) && count($tab_id_product)) ? implode(', ', $tab_id_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` '.$sql_groups.'
)
AND (pa.id_product_attribute IS NULL OR product_attribute_shop.default_on = 1)
ORDER BY '.(isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').pSQL($order_by).' '.pSQL($order_way).'
LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);

 $another_sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, product_attribute_shop.id_product_attribute,
 pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`,
 pl.`name`, image_shop.`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
'.Shop::addSqlAssociation('product', 'p').'
LEFT JOIN '._DB_PREFIX_.'product_attribute pa ON (pa.id_product = p.id_product)
'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on=1').'
'.Product::sqlStock('p', 0, false, $context->shop).'
LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (
 p.`id_product` = pl.`id_product`
 AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
)
LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product`)'.
Shop::addSqlAssociation('image', 'i', false, 'image_shop.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 (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`
 AND tr.`id_country` = '.(int)Context::getContext()->country->id.'
 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 product_shop.`active` = 1
AND p.on_sale = 1
AND product_shop.`show_price` = 1
AND ((image_shop.id_image IS NOT NULL OR i.id_image IS NULL) OR (image_shop.id_image IS NULL AND i.cover=1))
AND (pa.id_product_attribute IS NULL OR product_attribute_shop.default_on = 1)
ORDER BY '.(isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').pSQL($order_by).' '.pSQL($order_way).'
LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;
 $result += Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($another_sql);

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

Now it displays On Sale and Prices Drop at the same time

[Solved] Sale Products Page

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites