protected function applyFromToFilter($from, $to)
{
$tableName = 'price_table';
$collection = $this->getLayer()->getProductCollection();
$attribute = $this->getAttributeModel();
$websiteId = Mage::app()->getStore()->getWebsiteId();
$custGroupId = Mage::getSingleton('customer/session')->getCustomerGroupId();
/**
* Distinct required for removing duplicates in case when we have grouped products
* which contain multiple rows for one product id
*/
$collection->getSelect()->distinct(true);
$tableName = $tableName . '_' . $attribute->getAttributeCode();
$collection->getSelect()->joinLeft(array($tableName => Mage::getSingleton('core/resource')->getTableName('catalogindex/price')), $tableName . '.entity_id=e.entity_id', array());
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
$collection->getSelect()->where($tableName . '.website_id = ?', $websiteId);
if ($attribute->getAttributeCode() == 'price') {
//$collection->getSelect()->where($tableName . '.customer_group_id = ?', $custGroupId); // modified line
$args = array('select' => $collection->getSelect(), 'table' => $tableName, 'store_id' => Mage::app()->getStore()->getId(), 'response_object' => $response);
Mage::dispatchEvent('catalogindex_prepare_price_select', $args);
}
// TODO - explore!
$rate = 1;
// make query a little bit faster
if ($from > 0.01) {
$collection->getSelect()->where("(({$tableName}.price" . implode('', $response->getAdditionalCalculations()) . ")*{$rate}) >= ?", $from);
}
if ($to > 0.01) {
$collection->getSelect()->where("(({$tableName}.price" . implode('', $response->getAdditionalCalculations()) . ")*{$rate}) <= ?", $to);
}
/*echo $collection->getSelect(); die;*/
return $this;
}
/**
* Prepare response object and dispatch prepare price event
* Return response object
*
* @param Varien_Db_Select $select
* @return Varien_Object
*/
protected function _dispatchPreparePriceEvent($select)
{
// prepare response object for event
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
// prepare event arguments
$eventArgs = array('select' => $select, 'table' => 'price_index', 'store_id' => Mage::app()->getStore()->getId(), 'response_object' => $response);
Mage::dispatchEvent('catalog_prepare_price_select', $eventArgs);
return $response;
}
/**
* Prepare response object and dispatch prepare price event
* Return response object
*
* @param Mage_Catalog_Model_Layer_Filter_Price $filter
* @param Varien_Db_Select $select
* @return Varien_Object
*/
protected function _dispatchPreparePriceEvent($filter, $select)
{
// prepare response object for event
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
// prepare event arguments
$eventArgs = array('select' => $select, 'table' => $this->_getIndexTableAlias(), 'store_id' => $filter->getStoreId(), 'response_object' => $response);
/**
* @since 1.4
*/
Mage::dispatchEvent('catalog_prepare_price_select', $eventArgs);
return $response;
}
/**
* Prepare price expression use in sql string
*
* @return EM_LayeredNavigation_Block_Catalog_Filter_Price
*/
protected function _preparePriceExpressionParameters($select)
{
// prepare response object for event
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
$tableAliases = array_keys($select->getPart(Zend_Db_Select::FROM));
if (in_array('price_index', $tableAliases)) {
$table = 'price_index';
} else {
$table = reset($tableAliases);
}
// prepare event arguments
$storeId = Mage::app()->getStore()->getId();
$eventArgs = array('select' => $select, 'table' => $table, 'store_id' => $storeId, 'response_object' => $response);
Mage::dispatchEvent('catalog_prepare_price_select', $eventArgs);
$additional = join('', $response->getAdditionalCalculations());
$this->_priceExpression = $table . '.min_price';
$this->_additionalPriceExpression = $additional;
$this->_currencyRate = Mage::app()->getStore($storeId)->getCurrentCurrencyRate();
return $this;
}
/**
* Prepare additional price expression sql part
*
* @param Varien_Db_Select $select
* @return Mage_Catalog_Model_Resource_Product_Collection
*/
protected function _preparePriceExpressionParameters($select)
{
// prepare response object for event
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
$tableAliases = array_keys($select->getPart(Zend_Db_Select::FROM));
if (in_array(self::INDEX_TABLE_ALIAS, $tableAliases)) {
$table = self::INDEX_TABLE_ALIAS;
} else {
$table = reset($tableAliases);
}
// prepare event arguments
$eventArgs = array('select' => $select, 'table' => $table, 'store_id' => $this->getStoreId(), 'response_object' => $response);
Mage::dispatchEvent('catalog_prepare_price_select', $eventArgs);
$additional = join('', $response->getAdditionalCalculations());
$this->_priceExpression = $table . '.min_price';
$this->_additionalPriceExpression = $additional;
$this->_catalogPreparePriceSelect = clone $select;
return $this;
}
protected function _prepareSelect($filter, $clone = false, $baseSelect = null, $attribute = null)
{
if ($baseSelect) {
// we need for 1.3 compatibility
$select = $baseSelect;
} else {
$collection = $filter->getLayer()->getProductCollection();
$select = $collection->getSelect();
$attribute = $filter->getAttributeModel();
}
$ret = $clone ? clone $select : $select;
if ($this->_price) {
// after apply() function or getMaxInt()
$oldWhere = $ret->getPart(Varien_Db_Select::WHERE);
$newWhere = array();
foreach ($oldWhere as $cond) {
if (false === strpos($cond, $this->_price)) {
$newWhere[] = $cond;
}
}
if ($newWhere && substr($newWhere[0], 0, 3) == 'AND') {
$newWhere[0] = substr($newWhere[0], 3);
}
$ret->setPart(Varien_Db_Select::WHERE, $newWhere);
} else {
//first time
$ret->distinct(true);
$tableName = 'price_table';
$ret->joinLeft(array($tableName => $this->getMainTable()), $tableName . '.entity_id=e.entity_id', array());
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
$ret->where($tableName . '.website_id = ?', $this->getWebsiteId())->where($tableName . '.attribute_id = ?', $attribute->getId());
if ($attribute->getAttributeCode() == 'price') {
$ret->where($tableName . '.customer_group_id = ?', $this->getCustomerGroupId());
$args = array('select' => $ret, 'table' => $tableName, 'store_id' => $this->getStoreId(), 'response_object' => $response);
Mage::dispatchEvent('catalogindex_prepare_price_select', $args);
}
$additional = join('', $response->getAdditionalCalculations());
// will be used in the count function
$this->_price = "(({$tableName}.value {$additional}) * {$this->getRate()})";
}
if ($clone) {
$ret->reset(Zend_Db_Select::COLUMNS);
$ret->reset(Zend_Db_Select::ORDER);
$ret->reset(Zend_Db_Select::LIMIT_COUNT);
$ret->reset(Zend_Db_Select::LIMIT_OFFSET);
}
return $ret;
}
/**
* Build Entity price filter
*
* @param array $attributes
* @param array $values
* @param array $filteredAttributes
* @param Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection $productCollection
* @return array
*/
public function buildEntityPriceFilter($attributes, $values, &$filteredAttributes, $productCollection)
{
$additionalCalculations = array();
$filter = array();
$store = Mage::app()->getStore()->getId();
$website = Mage::app()->getStore()->getWebsiteId();
$currentStoreCurrency = Mage::app()->getStore()->getCurrentCurrencyCode();
foreach ($attributes as $attribute) {
$code = $attribute->getAttributeCode();
if (isset($values[$code])) {
foreach ($this->_priceIndexers as $indexerName) {
$indexer = $this->_indexers[$indexerName];
/* @var $indexer Mage_CatalogIndex_Model_Indexer_Abstract */
if ($indexer->isAttributeIndexable($attribute)) {
if ($values[$code]) {
if (isset($values[$code]['from']) && isset($values[$code]['to']) && (strlen($values[$code]['from']) == 0 && strlen($values[$code]['to']) == 0)) {
continue;
}
$table = $indexer->getResource()->getMainTable();
if (!isset($filter[$code])) {
$filter[$code] = $this->_getSelect();
$filter[$code]->from($table, array('entity_id'));
$filter[$code]->distinct(true);
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
$args = array('select' => $filter[$code], 'table' => $table, 'store_id' => $store, 'response_object' => $response);
Mage::dispatchEvent('catalogindex_prepare_price_select', $args);
$additionalCalculations[$code] = $response->getAdditionalCalculations();
if ($indexer->isAttributeIdUsed()) {
//$filter[$code]->where("$table.attribute_id = ?", $attribute->getId());
}
}
if (is_array($values[$code])) {
$rateConversion = 1;
$filter[$code]->distinct(true);
if (isset($values[$code]['from']) && isset($values[$code]['to'])) {
if (isset($values[$code]['currency'])) {
$rateConversion = $this->_getBaseToSpecifiedCurrencyRate($values[$code]['currency']);
} else {
$rateConversion = $this->_getBaseToSpecifiedCurrencyRate($currentStoreCurrency);
}
if (strlen($values[$code]['from']) > 0) {
$filter[$code]->where("({$table}.min_price" . implode('', $additionalCalculations[$code]) . ")*{$rateConversion} >= ?", $values[$code]['from']);
}
if (strlen($values[$code]['to']) > 0) {
$filter[$code]->where("({$table}.min_price" . implode('', $additionalCalculations[$code]) . ")*{$rateConversion} <= ?", $values[$code]['to']);
}
}
}
$filter[$code]->where("{$table}.website_id = ?", $website);
if ($code == 'price') {
$filter[$code]->where($table . '.customer_group_id = ?', Mage::getSingleton('customer/session')->getCustomerGroupId());
}
$filteredAttributes[] = $code;
}
}
}
}
}
return $filter;
}
public function applyFilterToCollection($collection, $attribute, $range, $index, $tableName = 'price_table')
{
/**
* Distinct required for removing duplicates in case when we have grouped products
* which contain multiple rows for one product id
*/
$collection->getSelect()->distinct(true);
$tableName = $tableName . '_' . $attribute->getAttributeCode();
$collection->getSelect()->joinLeft(array($tableName => $this->getMainTable()), $tableName . '.entity_id=e.entity_id', array());
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
$collection->getSelect()->where($tableName . '.website_id = ?', $this->getWebsiteId())->where($tableName . '.attribute_id = ?', $attribute->getId());
if ($attribute->getAttributeCode() == 'price') {
$collection->getSelect()->where($tableName . '.customer_group_id = ?', $this->getCustomerGroupId());
$args = array('select' => $collection->getSelect(), 'table' => $tableName, 'store_id' => $this->getStoreId(), 'response_object' => $response);
Mage::dispatchEvent('catalogindex_prepare_price_select', $args);
}
$collection->getSelect()->where("(({$tableName}.value" . implode('', $response->getAdditionalCalculations()) . ")*{$this->getRate()}) >= ?", ($index - 1) * $range);
$collection->getSelect()->where("(({$tableName}.value" . implode('', $response->getAdditionalCalculations()) . ")*{$this->getRate()}) < ?", $index * $range);
return $this;
}
protected function getMinMax($filter, $bIsBasePrice)
{
$attribute = $this->getAttributeModel();
$MinPrice1 = $this->getLayer()->getProductCollection()->getMinPrice();
$MaxPrice1 = $this->getLayer()->getProductCollection()->getMaxPrice();
$MinPrice1 = number_format($MinPrice1, 4, '.', '');
$MaxPrice1 = number_format($MaxPrice1, 4, '.', '');
$mainCollection = $this->getLayer()->getProductCollection();
$websiteId = Mage::app()->getStore()->getWebsiteId();
$custGroupId = Mage::getSingleton('customer/session')->getCustomerGroupId();
$select = clone $mainCollection->getSelect();
$select->setPart(Varien_Db_Select::COLUMNS, array());
$select->setPart(Varien_Db_Select::ORDER, array());
$select->distinct(false);
$tableAlias = $attribute->getAttributeCode() . '_idx';
if ($bIsBasePrice) {
$tableName = Mage::getSingleton('core/resource')->getTableName('catalogindex/price');
$valueAlias = 'min_price';
} else {
$tableName = Mage::getSingleton('core/resource')->getTableName('catalog/product_index_eav_decimal');
$valueAlias = 'value';
}
$select->columns(array('min_value' => new Zend_Db_Expr('MIN(' . $tableAlias . '.' . $valueAlias . ')'), 'max_value' => new Zend_Db_Expr('MAX(' . $tableAlias . '.' . $valueAlias . ')')));
if ($this->_isTableJoined) {
$oldWhere = $select->getPart(Varien_Db_Select::WHERE);
$newWhere = array();
$alias = $tableAlias . '.' . $valueAlias;
foreach ($oldWhere as $cond) {
if (!strpos($cond, $alias)) {
$newWhere[] = $cond;
}
}
if ($newWhere && substr($newWhere[0], 0, 3) == 'AND') {
$newWhere[0] = substr($newWhere[0], 3);
}
$select->setPart(Varien_Db_Select::WHERE, $newWhere);
} else {
$sOnStatement = $tableAlias . '.entity_id=e.entity_id';
if (!$bIsBasePrice) {
$sOnStatement .= ' AND ' . $tableAlias . '.attribute_id = ' . $attribute->getId() . ' AND ' . $tableAlias . '.store_id = ' . Mage::app()->getStore()->getId();
}
try {
$select->joinLeft(array($tableAlias => $tableName), $sOnStatement, array());
} catch (Zend_Db_No_Exception $e) {
return $this;
}
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
if ($bIsBasePrice) {
$select->where($tableAlias . '.website_id = ?', $websiteId);
}
if ($bIsBasePrice) {
$select->where($tableAlias . '.customer_group_id = ?', $custGroupId);
// modified line
$args = array('select' => $select, 'table' => $tableAlias, 'store_id' => Mage::app()->getStore()->getId(), 'response_object' => $response);
Mage::dispatchEvent('catalogindex_prepare_price_select', $args);
}
}
$adapter = Mage::getSingleton('core/resource')->getConnection('core_read');
$result = $adapter->fetchRow($select);
$rate = $this->_getCurrencyRate();
if (!$result) {
return array($MinPrice1 * $rate, ceil($MaxPrice1 * $rate));
}
return array($result['min_value'] * $rate, ceil($result['max_value'] * $rate));
}
protected function _getPriceExpression($filter, $select, $replaceAlias = true)
{
foreach ($this->_preparedExpressions as $expr) {
if ($expr['select'] == $select) {
return $expr['result'];
}
}
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
$tableAliases = array_keys($select->getPart(Zend_Db_Select::FROM));
if (in_array(Mage_Catalog_Model_Resource_Product_Collection::INDEX_TABLE_ALIAS, $tableAliases)) {
$table = Mage_Catalog_Model_Resource_Product_Collection::INDEX_TABLE_ALIAS;
} else {
$table = reset($tableAliases);
}
// prepare event arguments
$eventArgs = array('select' => $select, 'table' => $table, 'store_id' => $filter->getStoreId(), 'response_object' => $response);
Mage::dispatchEvent('catalog_prepare_price_select', $eventArgs);
$table = Mage_Catalog_Model_Resource_Product_Collection::MAIN_TABLE_ALIAS;
$additional = $this->_replaceTableAlias(join('', $response->getAdditionalCalculations()));
$fix = $this->_getConfigurablePriceFix();
$result = "{$table}.min_price {$additional} {$fix}";
if ($replaceAlias) {
$result = $this->_replaceTableAlias($result);
}
$this->_preparedExpressions[] = compact('select', 'result');
return $result;
}
public function getFilteredEntities($range, $index, $attribute, $entityIdsFilter, $tableName = 'price_table')
{
$select = $this->_getReadAdapter()->select();
$select->from(array($tableName => $this->getMainTable()), $tableName . '.entity_id');
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
$args = array('select' => $select, 'table' => $tableName, 'store_id' => $this->getStoreId(), 'response_object' => $response);
AO::dispatchEvent('catalogindex_prepare_price_select', $args);
$select->distinct(true)->where($tableName . '.entity_id in (?)', $entityIdsFilter)->where($tableName . '.website_id = ?', $this->getWebsiteId())->where($tableName . '.attribute_id = ?', $attribute->getId());
if ($attribute->getAttributeCode() == 'price') {
$select->where($tableName . '.customer_group_id = ?', $this->getCustomerGroupId());
}
$select->where("(({$tableName}.value" . implode('', $response->getAdditionalCalculations()) . ")*{$this->getRate()}) >= ?", ($index - 1) * $range);
$select->where("(({$tableName}.value" . implode('', $response->getAdditionalCalculations()) . ")*{$this->getRate()}) < ?", $index * $range);
return $this->_getReadAdapter()->fetchCol($select);
}
protected function getMinOrMax($filter, $bIsBasePrice, $minOfMaxFlag = 'min')
{
$attribute = $this->getAttributeModel();
$mainCollection = $this->getLayer()->getProductCollection();
$websiteId = Mage::app()->getStore()->getWebsiteId();
$custGroupId = Mage::getSingleton('customer/session')->getCustomerGroupId();
# $tableAlias = 'price_table';
$select = clone $mainCollection->getSelect();
$select->setPart(Varien_Db_Select::COLUMNS, array());
$select->setPart(Varien_Db_Select::ORDER, array());
$select->distinct(false);
$tableAlias = $attribute->getAttributeCode() . '_idx';
if ($bIsBasePrice) {
$tableName = Mage::getSingleton('core/resource')->getTableName('catalogindex/price');
$valueAlias = 'min_price';
} else {
$tableName = Mage::getSingleton('core/resource')->getTableName('catalog/product_index_eav_decimal');
$valueAlias = 'value';
}
if ($minOfMaxFlag === 'min') {
$select->columns(array('value' => new Zend_Db_Expr('MIN(' . $tableAlias . '.' . $valueAlias . ')'), 'id' => new Zend_Db_Expr($tableAlias . '.entity_id')));
} else {
$select->columns(array('value' => new Zend_Db_Expr('MAX(' . $tableAlias . '.' . $valueAlias . ')'), 'id' => new Zend_Db_Expr($tableAlias . '.entity_id')));
}
#d($select->__toString(), 0);
if ($this->_isTableJoined) {
// 1) remove from conditions
$oldWhere = $select->getPart(Varien_Db_Select::WHERE);
$newWhere = array();
$alias = $tableAlias . '.' . $valueAlias;
foreach ($oldWhere as $cond) {
if (!strpos($cond, $alias)) {
$newWhere[] = $cond;
}
}
if ($newWhere && substr($newWhere[0], 0, 3) == 'AND') {
$newWhere[0] = substr($newWhere[0], 3);
}
$select->setPart(Varien_Db_Select::WHERE, $newWhere);
} else {
$sOnStatement = $tableAlias . '.entity_id=e.entity_id';
if (!$bIsBasePrice) {
$sOnStatement .= ' AND ' . $tableAlias . '.attribute_id = ' . $attribute->getId() . ' AND ' . $tableAlias . '.store_id = ' . Mage::app()->getStore()->getId();
} else {
# $sOnStatement .= ' AND ' . $tableAlias . '.website_id = ' . $websiteId . ' AND ' . $tableAlias . '.customer_group_id = ' . Mage::app()->getStore()->getId();
}
$select->joinLeft(array($tableAlias => $tableName), $sOnStatement, array());
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
if ($bIsBasePrice) {
$select->where($tableAlias . '.website_id = ?', $websiteId);
}
if ($bIsBasePrice) {
$select->where($tableAlias . '.customer_group_id = ?', $custGroupId);
// modified line
$args = array('select' => $select, 'table' => $tableAlias, 'store_id' => Mage::app()->getStore()->getId(), 'response_object' => $response);
Mage::dispatchEvent('catalogindex_prepare_price_select', $args);
}
}
if ($minOfMaxFlag === 'min') {
$select->order('value ASC');
} else {
$select->order('value DESC');
}
$select->group('id');
$select->limit(1);
//die($select->__toString());
$adapter = Mage::getSingleton('core/resource')->getConnection('core_read');
$result = $adapter->fetchRow($select);
return $result;
}
