Here is i have found solution after deep analysis.
you can override model
app\code\core\Mage\CatalogRule\Model\Action\Index\Refresh.php
<global>
<models>
<catalogrule>
<rewrite> <action_index_refresh>Yournamespace_Promorule_Model_CatalogRule_Action_Index_Refresh</action_index_refresh></rewrite>
</catalogrule>
</models>
</global>
class Yournamespace_Promorule_Model_CatalogRule_Action_Index_Refresh extends Mage_CatalogRule_Model_Action_Index_Refresh {
/**
* Prepare temporary data
*
* @param Mage_Core_Model_Website $website
* @return Varien_Db_Select
*/
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');
$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'
), '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(
'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()
);
$specialPriceAttribute = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'special_price');
$specialFormDateAttribute = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'special_from_date');
$specialToDateAttribute = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'special_to_date');
$todayStartOfDayDate = Mage::app()->getLocale()->date()
->setTime('00:00:00')
->toString(Varien_Date::DATETIME_INTERNAL_FORMAT);
$todayEndOfDayDate = Mage::app()->getLocale()->date()
->setTime('23:59:59')
->toString(Varien_Date::DATETIME_INTERNAL_FORMAT);
$select->joinLeft(
array(
'spd' => $this->_resource->getTable(array('catalog/product', $specialPriceAttribute->getBackendType()))
), 'spd.entity_id = rp.product_id AND spd.store_id = 0 AND spd.attribute_id = '
. $specialPriceAttribute->getId(), array()
)
->joinLeft(
array(
'sp' => $this->_resource->getTable(array('catalog/product', $specialPriceAttribute->getBackendType()))
), 'sp.entity_id = rp.product_id AND sp.store_id = ' . $storeId
. ' AND sp.attribute_id = spd.attribute_id', array()
);
$select->joinLeft(
array(
'sdfd' => $this->_resource->getTable(array('catalog/product', $specialFormDateAttribute->getBackendType()))
), 'sdfd.entity_id = rp.product_id AND sdfd.store_id = 0 AND sdfd.attribute_id = '
. $specialFormDateAttribute->getId(), array()
)
->joinLeft(
array(
'sdf' => $this->_resource->getTable(array('catalog/product', $specialFormDateAttribute->getBackendType()))
), 'sdf.entity_id = rp.product_id AND sdf.store_id = ' . $storeId
. ' AND sdf.attribute_id = sdfd.attribute_id', array()
);
$select->joinLeft(
array(
'sdtd' => $this->_resource->getTable(array('catalog/product', $specialToDateAttribute->getBackendType()))
), 'sdtd.entity_id = rp.product_id AND sdtd.store_id = 0 AND sdtd.attribute_id = '
. $specialToDateAttribute->getId(), array()
)
->joinLeft(
array(
'sdt' => $this->_resource->getTable(array('catalog/product', $specialToDateAttribute->getBackendType()))
), 'sdt.entity_id = rp.product_id AND sdt.store_id = ' . $storeId
. ' AND sdt.attribute_id = sdtd.attribute_id', array()
);
$priceColumn = $this->_connection->getCheckSql(
'(' . $this->_connection->getIfNullSql(
'sp.value', 'spd.value'
) . ' IS NOT NULL AND ((' .
$this->_connection->getIfNullSql(
'sdf.value', 'sdfd.value'
) . ' IS NULL) OR (' .
$this->_connection->getIfNullSql(
'sdf.value', 'sdfd.value'
) . ' <= "' . $todayEndOfDayDate . '")'
. ') AND ((' .
$this->_connection->getIfNullSql(
'sdt.value', 'sdtd.value'
) . ' IS NULL) OR (' .
$this->_connection->getIfNullSql(
'sdt.value', 'sdtd.value'
) . ' >= "' . $todayStartOfDayDate . '")'
. '))', $this->_connection->getIfNullSql(
'sp.value', 'spd.value'
), $this->_connection->getIfNullSql(
$this->_connection->getIfNullSql(
'pg.value', 'pgd.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;
}
}
hope this will sure help to our community.
Let me know if any inputs from your end.
Best Answer
All good, i figured it all out.
were the files i needed in case anyone is interested