1

Basically, I need a way to find out when a price rule gets active/inactive, and which products that is being effected by it. Both when it's made manually, and per the date set in the rule.

I've tried using catalogrule_after_apply but I can't get any relevant data out of it.

Any suggestions?

Edit:

I've pretty much been fiddling around, dumping data:

<catalogrule_after_apply>
    <observers>
        <my_module_catalogrule_after_apply>
            <type>singleton</type>
            <class>My_Module_Model_Observer</class>
            <method>catalogruleProductApiUpdate</method>
        </my_module_catalogrule_after_apply>
    </observers>
</catalogrule_after_apply>

and

class My_Module_Model_Observer
{
    public function catalogruleProductApiUpdate (Varien_Event_Observer $observer)
    {
        $product = $observer->getEvent()->getProduct();
        Mage::log('crPAU ran:' . print_r($product, true));
    }
}

and similar stuff - just haven't found anything of relevance. I've used catalog_product_save_commit_after and catalog_product_attribute_update_before with success.

On a sidenote it also runs everytime i save a product - regardless of said product having a price rule or not - and then it prints out the product ID in the log.

I'm a bit unsure about this even being the right event to trigger on.

4
  • Paste what you've tried so far. Jan 25 '15 at 15:25
  • Edited the original post - I've only tried some var dumping and stuff - without finding anything. (to log file, and just plain die(var_dump($something));
    – Floddy
    Jan 25 '15 at 20:15
  • Might have found a solution in hooking into the catalogrule_affected_product table...
    – Floddy
    Jan 28 '15 at 15:30
  • Hooked into the catalogrule_affected_product table - but does not get anything out of it. Actually, when logging the SQL queries, it only ever logs a DELETE query on the table, except from the SELECT query which is in my module. Ideas?
    – Floddy
    Feb 14 '15 at 20:35
0

This code may not solve exactly what you're end goal is, but it will hopefully show you how to derive some information out of the catalogrule tables.

My goal was to populate a product's attribute value with either Yes or No depending on whether or not a given product was currently being affected by the Catalog Price Rules, (for use in the Enterprise Visual Merchandiser.)

This works on Magento EE 1.14 but should work on modern Magento CE as well:

public function hookCatalogruleAfterApply(Varien_Event_Observer $observer) {
    $product = $observer->getEvent()->getProduct();

    if ($product) {
        $this->updateAnyCprAppliedSingle($product);
    } else {
        $this->updateAnyCprAppliedMany();
    }
}

protected function updateAnyCprAppliedSingle($product_id) {
    /*
     * DB Setup
     */
    $resource       = Mage::getSingleton('core/resource');
    $readConnection = $resource->getConnection('core_read');
    $crp            = $resource->getTableName('catalogrule_product');

    $sql = <<<SQL
        SELECT DISTINCT crp.product_id FROM {$crp} crp
        WHERE crp.product_id = '{$product_id}'
SQL;

    $productRuleStatus = $readConnection->fetchRow($sql);

    $actionModel = Mage::getModel('catalog/product_action');

    if (!empty($productRuleStatus)) {
        $actionModel->updateAttributes(
            array($product_id),
            array('any_cpr_applied' => 1),
            0
        );
    } else {
        $actionModel->updateAttributes(
            array($product_id),
            array('any_cpr_applied' => 0),
            0
        );
    }
}

protected function updateAnyCprAppliedMany() {
    /*
     * DB Setup
     */
    $resource       = Mage::getSingleton('core/resource');
    $readConnection = $resource->getConnection('core_read');
    $cpe            = $resource->getTableName('catalog_product_entity');
    $crp            = $resource->getTableName('catalogrule_product');

    $sql = <<<SQL
        SELECT DISTINCT cpe.entity_id, IF(crp.product_id IS NULL, 0, 1) as applied
        FROM {$cpe} cpe
        LEFT JOIN {$crp} crp
        ON cpe.entity_id = crp.product_id
SQL;

    $productRuleStatuses = $readConnection->fetchAll($sql);

    if (!empty($productRuleStatuses)) {
        $actionModel = Mage::getModel('catalog/product_action');

        $yes = array();
        $no  = array();

        foreach ($productRuleStatuses as $productRuleStatus) {
            if ($productRuleStatus['applied'] == 1) {
                $yes[] = $productRuleStatus['entity_id'];
            } else {
                $no[] = $productRuleStatus['entity_id'];
            }
        }

        if (!empty($yes)) {
            $actionModel->updateAttributes(
                array_values($yes),
                array('any_cpr_applied' => 1),
                0
            );
        }

        if (!empty($no)) {
            $actionModel->updateAttributes(
                array_values($no),
                array('any_cpr_applied' => 0),
                0
            );
        }
    }
}

I purposefully named this "any_cpr_applied" because this doesn't take website or customer group into consideration. If you need finer details on which website the product is affected on, which customer group the product affected for, or how the product is affected, you'll need to join more tables.

A quick note on my direct table queries. You can totally replace this with use of the Magento models, however, I was trying to develop this quickly and just made sure I had the dynamic table names.

4
  • Thanks! Does this run on catalogrule_after_apply? If I understand correctly - when there are multiple products (when a rule is applied), it goes through ALL products and sets the attribute to yes/no, right? The issue with that is that I really don't know which products has "changed" - I need to also know the products that were effected that no longer are. But - the attribute way of doing it gave me an idea: checking for when the attribute is changed.
    – Floddy
    Apr 18 '15 at 7:37
  • Yes, it does run on catalogrule_after_apply and yes it's resetting the attribute for ALL products. (I ended up making it detect only a change to yes/no using the previous attribute values, as even Mage_Catalog_Model_Product_Action isn't fast enough to change 50,000 products quickly.) As I said, it doesn't give you details on store or customer group or which rule affected which product, but it's a start.
    – Tyler V.
    Apr 18 '15 at 7:44
  • This might be a solution: 1. Load all product IDs with any_cpr_applied = yes into an array (called $oldYes) 2. run the query in your post and populate the $yes array. 3. $diff1 = array_diff(array_values($oldYes), array_values($yes)); $diff2 = array_diff(array_values($yes), array_values($oldYes)); $changedProductIds = array_merge($diff1, $diff2); I'll now have all product IDs which have changed status in $changedProductIds. This could probably also be used to only update the any_cpr_applied attribute on the effected products - perhaps using array_intersect on $yes/no and $changedProductIds.
    – Floddy
    Apr 18 '15 at 8:48
  • That's basically what my final code did. Good luck!
    – Tyler V.
    Apr 18 '15 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.