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
CustomerGroup is a select field and I'm sure everywhere is checked for ONE value.
e.g. here
\Mage_SalesRule_Model_Validator::init
this means, everywhere where magento expects
(int)7
you pass either(array) [0] => (int) 1, [1] => (int) 2, [2] => (int)3
or(string)1,2,3,4
and both is wrong.Magento is not built to use more than one customer group and you can change this setting, then it may work on some places, but on MOST places you get a lot of problems.
TL;DR You want to invest a lot of money to fix all problems or just revert/let it as it is.