November 17, 2019

cm-mini

Thinking Magento

Exclude Tier Prices From Catalog Price Rules

It maybe necessary for you to exclude products that already have tier prices from catalog price rule promotions. An example would be that you want to have a global sales rule for all products, but on some items you have to purchase a certain quantity in order to qualify for the discount. This work around has been applied to a multi-store magento website.

The following adjustments to Mage_CatalogRule_Model_Resource_Rule will be beneficial for you. 

Copy Mage_CatalogRule_Model_Resource_Rule to local directory structure

Find function _getRuleProductsStmt

FIND THE FOLLOWING

$select->joinInner(
array('product_website' => $this->getTable('catalog/product_website')),
'product_website.product_id=rp.product_id ' .
'AND rp.website_id=product_website.website_id ' .
'AND product_website.website_id='.$websiteId,
array()
);

ADD THE FOLLOWING BELOW IT

$select->joinInner(
array('pt' => 'catalog_product_index_price'),
'pt.entity_id=rp.product_id AND pt.customer_group_id IN(rp.customer_group_id) AND pt.tier_price IS NULL AND pt.website_id='.$websiteId,
array()
);

It's not the cleanest way of doing it and requires that you have Flat Catalog Products enabled in your System > Configuration. It will however get you to where you want to be and you can process your Catalog Price Rules without worrying about removing tier price discounts.

If this saved you hours of work or saved you from a headache, feel free to buy us a beer.