/** * 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 applyFromToFilter($from, $to) { $attribute = $this->getAttributeModel(); $tableAlias = $attribute->getAttributeCode() . '_idx'; if ($attribute->getAttributeCode() == 'price') { $bIsBasePrice = true; $tableName = Mage::getSingleton('core/resource')->getTableName('catalogindex/price'); $valueAlias = 'min_price'; } else { $bIsBasePrice = false; $tableName = Mage::getSingleton('core/resource')->getTableName('catalog/product_index_eav_decimal'); $valueAlias = 'value'; } $collection = $this->getLayer()->getProductCollection(); $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); $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 { $collection->getSelect()->joinLeft(array($tableAlias => $tableName), $sOnStatement, array()); } catch (Zend_No_Exception $e) { return $this; } $response = new Varien_Object(); $response->setAdditionalCalculations(array()); if ($bIsBasePrice) { $collection->getSelect()->where($tableAlias . '.website_id = ?', $websiteId); } if ($attribute->getAttributeCode() == 'price') { $collection->getSelect()->where($tableAlias . '.customer_group_id = ?', $custGroupId); // modified line $args = array('select' => $collection->getSelect(), 'table' => $tableAlias, 'store_id' => Mage::app()->getStore()->getId(), 'response_object' => $response); Mage::dispatchEvent('catalogindex_prepare_price_select', $args); } $rate = $this->_getCurrencyRate(); // make query a little bit faster if ($from > 0.01) { $collection->getSelect()->where("(({$tableAlias}.{$valueAlias}" . implode('', $response->getAdditionalCalculations()) . ")*{$rate}) >= ?", $from); } if ($to > 0.01) { $collection->getSelect()->where("(({$tableAlias}.{$valueAlias}" . implode('', $response->getAdditionalCalculations()) . ")*{$rate}) <= ?", $to); } $this->_isTableJoined = true; return $this; }
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 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; }