How to obtain catalog price rule expire date?

Question

On catalog page I need to show offer "expires in N days". It can be easily achieved if each product has defined Special Price and date individually for example $this->getProduct()->getSpecialToDate();, but if you set discount via Catalog Price Rule product has no information about discount currently applied and other related data.

I'm wandering how can I get information about current product discount rules used like date?

Answer 1

I have not tried this, but I believe it's possible. I don't have the exact code, but I can provide you with it later when I have more free time if you really need it. This appraoch is pretty straightforward.

There's a table called catalogrule_product that stores Catalog Price Rules discounts per product and customer group. Note that this table needs to get indexed every time you make changes to your Catalog Price Rules. You can do this with the indexer or by clicking "Apply Rules" on this page (this could take long if you have a lot of products).

For a given product you have on a catalog (or any) page, get its entity_id and look it up on catalogrule_product.product_id. Make sure to check customer_group_id as well. Once you find the row(s) you're looking for, this row(s) will have have timestamps (in seconds) from_time and to_time for that given applied rule (rule_id).

Get its row_id and look this up on catalogrule if you need more information on the rule. The entry you find on catalogrule has from_date and to_date, and those are all you need to calculate what you want.

Answer 2

Here is the code which worked for me in this case.....

Created one function to sort the things.

function getCatalogSalePrice($_product)
{
 $productId = $_product->getId();
 $storeId = $_product->getStoreId();
 $websiteId = Mage::app()->getStore($storeId)->getWebsiteId();
 $timeStamp = Mage::app()->getLocale()->storeTimeStamp($storeId);
 $customerGroupId = 1;

 $resource = Mage::getResourceModel('catalogrule/rule');
 $rules = $resource->getRulesFromProduct($timeStamp, $websiteId, $customerGroupId, $productId);
 foreach($rules as $ruleval)
 {
  $catalogRuleID = $ruleval['rule_id']; //If more than one rule
  if($catalogRuleID)
  {
   $catalogRulObj = Mage::getModel('catalogrule/rule')->load($catalogRuleID);
   $conditions_serialized = unserialize($catalogRulObj->getConditionsSerialized());
   $actions_serialized = unserialize($catalogRulObj->getActionsSerialized());
   $ruleToDate = $catalogRulObj->getToDate();
  }
 }
 $now = time(); // or your date as well
 $saleEndDate = strtotime($ruleToDate);
 $datediff = $now - $saleEndDate;
 $datediff = floor($datediff / (60 * 60 * 24));

 if($datediff <= 0)
 {
  if($datediff < 0) {
   $message = abs($datediff). " Days Left";
  }
  else
  {
  $message = "Few Hours Left";
  }
 } 
 return $message;   
}

Cheers if it works.