Catalog Price Rules applied to special_price

Question

First question on stackoverflow...i am excited :)

Currently magento is using the special price if its lower than the applied catalog price rule. If the catalog price rule makes the product cheaper than the special price, then the catalog price rule defines the shop price.

I am looking for an elegant way to make catalog price rules be applied to the special price (additionally). Maybe there is some store config for it? Maybe there is some neat observer way?

Thank you so much!

Larondalarosa

Answer 1

Works up to current Magento 1.9.3.10. Just tested it in a project after update. Josef tried another approach which might work as well.

---

I am sad to say, I solved my first real stackoverflow question for my own:

1. Goto Mage_CatalogRule_Model_Resource_Rule
2. Goto method _getRuleProductsStmt

3. Add this to the initial select of the method before the first original ->from:

   $select->from(null, array('default_price' => new Zend_Db_Expr("CASE WHEN pp_default_special.value THEN pp_default_special.value ELSE pp_default_normal.value END")));

4. Add this after the first join() has happened

   $specialPriceAttr  = Mage::getSingleton('eav/config')
                       ->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'special_price');
   $specialPriceTable = $specialPriceAttr->getBackend()->getTable();
   $specialPriceAttributeId= $specialPriceAttr->getId();
   $joinCondition2 = '%1$s.entity_id=rp.product_id AND (%1$s.attribute_id=' . $specialPriceAttributeId . ') 
                                                   AND %1$s.store_id=%2$s';
   $select->join(
           array('pp_default_special'=>$specialPriceTable),
           sprintf($joinCondition2, 'pp_default_special', Mage_Core_Model_App::ADMIN_STORE_ID), null
   );
   

How it works:

When a catalog price rule is applied (via backend or cron) the db table catalogrule_product_price is populated. The above SQL magic joins the special_price (if exists) to the resultset as column default_value, if no special_price is found the regular price gets joined.

The result has been checked and is working.

Have fun! And dont hack the core!

Larondalarosa

Where is the file I need to work? When I searched in all files I fould quite a few file containing "Mage_CatalogRule_Model_Resource_Rule" Thanks:) – Marquesan

Mage_CatalogRule_Model_Resource_Rule can be found in: app/code/core/Mage/CatalogRule/Model/Resource/Rule.php – Larondalarosa

Thanks. I added in my Magento 1.7 to Model/Resource/Rule.php from line 439: try { /** * Update products rules prices per each website separately * because of max join limit in mysql */ foreach (Mage::app()->getWebsites(false) as $website) { $select->from(null, array('default_price' => new Zend_Db_Expr("CASE WHEN pp_default_special.value THEN pp_default_special.value ELSE pp_default_normal.value END"))); $productsStmt = $this->_getRuleProductsStmt( – Marquesan

And then above from line 316: /** * Join default price and websites prices to result */ $priceAttr = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'price'); $priceTable = $priceAttr->getBackend()->getTable(); $attributeId= $priceAttr->getId(); $specialPriceAttr = Mage::getSingleton('eav/config') ->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'special_price'); $specialPriceTable = $specialPriceAttr->getBackend()->getTable(); – Marquesan

But then I got error: Fatal error: Call to a member function from() on a non-object in /app/code/core/Mage/CatalogRule/Model/Resource/Rule.php on line 445 and that line is: $select->from(null, array('default_price' => new Zend_Db_Expr("CASE WHEN pp_default_special.value THEN pp_default_special.value ELSE pp_default_normal.value END"))); – Marquesan

Answer 2

There seem to be some changes in newer Magento releases! For 1.9 i had to:

copy app/code/core/Mage/CatalogRule/Model/Action/Index/Refresh.php to app/code/local/Mage/CatalogRule/Model/Action/Index/Refresh.php Change _prepareTemporarySelect.

I post the function in full here. Joins for special_price are added and then the price added to the selection of the price field. It still prefers group prices, becuas I never use them, but that can be changed easily!

protected
function _prepareTemporarySelect(Mage_Core_Model_Website $website)
    {
    /** @var $catalogFlatHelper Mage_Catalog_Helper_Product_Flat */
    $catalogFlatHelper = $this->_factory->getHelper('catalog/product_flat');
    /** @var $eavConfig Mage_Eav_Model_Config */
    $eavConfig = $this->_factory->getSingleton('eav/config');
    $priceAttribute = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'price');
    $specialPriceAttr = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'special_price');
    $specialPriceTable = $specialPriceAttr->getBackend()->getTable();
    $specialPriceAttributeId = $specialPriceAttr->getId();
    $select = $this->_connection->select()->from(array(
        'rp' => $this->_resource->getTable('catalogrule/rule_product')
    ) , array())->joinInner(array(
        'r' => $this->_resource->getTable('catalogrule/rule')
    ) , 'r.rule_id = rp.rule_id', array())->where('rp.website_id = ?', $website->getId())->order(array(
        'rp.product_id',
        'rp.customer_group_id',
        'rp.sort_order',
        'rp.rule_product_id'
    ))->joinLeft(array(
        'pg' => $this->_resource->getTable('catalog/product_attribute_group_price')
    ) , 'pg.entity_id = rp.product_id AND pg.customer_group_id = rp.customer_group_id' . ' AND pg.website_id = rp.website_id', array())->joinLeft(array(
        'pgd' => $this->_resource->getTable('catalog/product_attribute_group_price')
    ) , 'pgd.entity_id = rp.product_id AND pgd.customer_group_id = rp.customer_group_id' . ' AND pgd.website_id = 0', array());
    $storeId = $website->getDefaultStore()->getId();
    if ($catalogFlatHelper->isEnabled() && $storeId && $catalogFlatHelper->isBuilt($storeId))
        {
        $select->joinInner(array(
            'p' => $this->_resource->getTable('catalog/product_flat') . '_' . $storeId
        ) , 'p.entity_id = rp.product_id', array());
        $priceColumn = $this->_connection->getIfNullSql($this->_connection->getIfNullSql('pg.value', 'pgd.value') , $this->_connection->getIfNullSql('p.special_price', 'p.price'));
        }
      else
        {
        $select->joinInner(array(
            'pd' => $this->_resource->getTable(array(
                'catalog/product',
                $priceAttribute->getBackendType()
            ))
        ) , 'pd.entity_id = rp.product_id AND pd.store_id = 0 AND pd.attribute_id = ' . $priceAttribute->getId() , array())->joinLeft(array(
            'pspd' => $specialPriceTable
        ) , 'pspd.entity_id = rp.product_id AND (pspd.attribute_id=' . $specialPriceAttributeId . ')' . 'AND pspd.store_id = 0', array())->joinLeft(array(
            'p' => $this->_resource->getTable(array(
                'catalog/product',
                $priceAttribute->getBackendType()
            ))
        ) , 'p.entity_id = rp.product_id AND p.store_id = ' . $storeId . ' AND p.attribute_id = pd.attribute_id', array())->joinLeft(array(
            'psp' => $specialPriceTable
        ) , 'psp.entity_id = rp.product_id AND (psp.attribute_id=' . $specialPriceAttributeId . ')' . 'AND psp.store_id = ' . $storeId, array());
        $priceColumn = $this->_connection->getIfNullSql($this->_connection->getIfNullSql('pg.value', 'pgd.value') , $this->_connection->getIfNullSql('psp.value', $this->_connection->getIfNullSql('pspd.value', $this->_connection->getIfNullSql('p.value', 'pd.value'))));
        }

    $select->columns(array(
        'grouped_id' => $this->_connection->getConcatSql(array(
            'rp.product_id',
            'rp.customer_group_id'
        ) , '-') ,
        'product_id' => 'rp.product_id',
        'customer_group_id' => 'rp.customer_group_id',
        'from_date' => 'r.from_date',
        'to_date' => 'r.to_date',
        'action_amount' => 'rp.action_amount',
        'action_operator' => 'rp.action_operator',
        'action_stop' => 'rp.action_stop',
        'sort_order' => 'rp.sort_order',
        'price' => $priceColumn,
        'rule_product_id' => 'rp.rule_product_id',
        'from_time' => 'rp.from_time',
        'to_time' => 'rp.to_time'
    ));
    return $select;
    }

Cloudscape

So, for a bit of clarity - does the above function simply get pasted in app/code/local/Mage/CatalogRule/Model/Action/Index/Refresh.php or does app/code/core/Mage/CatalogRule/Model/Action/Index/Refresh.php still have to be copied to app/code/local/Mage/CatalogRule/Model/Action/Index/Refresh.php and _prepareTemporarySelect have to be changed, and if so, to what? – Metalworking

Copy the file as instructed, replace the _prepareTemporarySelect function. I am not sure how to explain that any clearer. – Cloudscape

Answer 3

I fixed it in another way. It was just easy to put into the price field for example 100 and then into special price field 90 when there was 10% discount on product page but now I removed special price from product page and just created catalog price rule 10% discount to that product(s) and now other rules also work:)

Marquesan