/**
* @param Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection $productCollection
* @param $categoryCollection
* @param bool $inCurrentCategory
* @return $this
*/
public function addCountToCategories($productCollection, $categoryCollection, $inCurrentCategory = false)
{
Mana_Core_Profiler2::start(__METHOD__);
$isAnchor = array();
$isNotAnchor = array();
foreach ($categoryCollection as $category) {
if ($category->getIsAnchor()) {
$isAnchor[] = $category->getId();
} else {
$isNotAnchor[] = $category->getId();
}
}
$productCounts = array();
if ($isAnchor || $isNotAnchor) {
/* @var $select Varien_Db_Select */
$select = $productCollection->getProductCountSelect();
if ($inCurrentCategory) {
$from = $select->getPart(Varien_Db_Select::FROM);
if (isset($from['cat_index'])) {
$categoryId = $this->getLayer()->getCurrentCategory()->getId();
$from['cat_index']['joinCondition'] = preg_replace("/(.*)(`?)cat_index(`?).(`?)category_id(`?)='(\\d+)'(.*)/", "\$1\$2cat_index\$3.\$4category_id\$5='{$categoryId}'\$7", $from['cat_index']['joinCondition']);
$select->setPart(Varien_Db_Select::FROM, $from);
}
}
Mage::dispatchEvent('catalog_product_collection_before_add_count_to_categories', array('collection' => $productCollection));
if ($isAnchor) {
$anchorStmt = clone $select;
$anchorStmt->limit();
//reset limits
$anchorStmt->where('count_table.category_id IN (?)', $isAnchor);
$sql = $anchorStmt->__toString();
$productCounts += $productCollection->getConnection()->fetchPairs($anchorStmt);
$anchorStmt = null;
}
if ($isNotAnchor) {
$notAnchorStmt = clone $select;
$notAnchorStmt->limit();
//reset limits
$notAnchorStmt->where('count_table.category_id IN (?)', $isNotAnchor);
$notAnchorStmt->where('count_table.is_parent = 1');
$productCounts += $productCollection->getConnection()->fetchPairs($notAnchorStmt);
$notAnchorStmt = null;
}
$select = null;
$productCollection->unsProductCountSelect();
}
foreach ($categoryCollection as $category) {
$_count = 0;
if (isset($productCounts[$category->getId()])) {
$_count = $productCounts[$category->getId()];
}
$category->setProductCount($_count);
}
Mana_Core_Profiler2::stop();
return $this;
}
/**
* Add price index filter
*
* @param Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection $collection
*
* @return Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection
*/
public function addPriceIndexFilter($collection)
{
$select = $collection->getSelect();
$fromPart = $select->getPart(Zend_Db_Select::FROM);
if (isset($fromPart['price_index'])) {
$joinCond = $fromPart['price_index']['joinCondition'];
if (strpos($joinCond, 'price_index.store_id') === false) {
$helper = $this->getStorePricingHelper();
$connection = $collection->getConnection();
if (!$collection->getFlag('store_id')) {
$storeId = $helper->getCurrentStoreId();
} else {
$storeId = $collection->getFlag('store_id');
}
$storeId = $connection->quote($storeId);
if ($storeId) {
$joinCond .= " AND (price_index.store_id = {$storeId})";
} else {
$joinCond .= " AND (price_index.store_id = 0)";
}
$fromPart['price_index']['joinCondition'] = $joinCond;
$select->setPart(Zend_Db_Select::FROM, $fromPart);
}
}
return $collection;
}
/**
* prepare sql for attribute compare
*
* @param object $attribute - attribute class with data
* @param array $joinedAttributes - attribute which was joined into query
* @param Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection $collection - product collection
* @param string $operator - operator for compare
* @param string $value - value for compare
* @return string $where - sql query
*/
public function prepareSqlForAtt($attribute, $joinedAttributes, $collection, $operator, $value)
{
$nOperator = array('==', '!{}', '{}', '!()', '()');
$mOperator = array('=', 'NOT LIKE', 'LIKE', 'NOT IN', 'IN');
//category operator
$cnOperator = array('==', '!=', '<=', '>=', '=', '>', '<', '!{}', '{}', '!()', '()', 'NOT LIKE', 'LIKE');
$cmOperator = array('IN', 'NOT IN', 'NOT IN', 'NOT IN', 'IN', 'NOT IN', 'NO IN', 'NOT IN', 'IN', 'NOT IN', 'IN', 'NOT IN', 'IN');
$operator = str_replace($nOperator, $mOperator, $operator);
if ($attribute == 'category_ids') {
$operator = str_replace($cnOperator, $cmOperator, $operator);
}
if (($operator == 'LIKE' || $operator == 'NOT LIKE') && is_array($value)) {
if ($operator == 'LIKE') {
$operator = 'FIND_IN_SET';
} else {
$operator = '!FIND_IN_SET';
}
}
/* Quote rule value depending operator type */
switch ($operator) {
case 'LIKE':
case 'NOT LIKE':
$value = $collection->getConnection()->quote("%{$value}%");
break;
case 'IN':
case 'NOT IN':
if (!is_array($value)) {
$value = array_filter(array_map('trim', explode(',', $value)));
}
$value = '(' . $collection->getConnection()->quote($value) . ')';
break;
case 'FIND_IN_SET':
case '!FIND_IN_SET':
$_resultSql = '(' . $collection->getConnection()->quote($value[0]) . ', att_table_' . $attribute . '.' . 'value)';
unset($value[0]);
foreach ($value as $_conditionValue) {
$_resultSql .= ' OR ' . $operator . '(' . $collection->getConnection()->quote($_conditionValue) . ', att_table_' . $attribute . '.' . 'value)';
}
$value = $_resultSql;
break;
default:
if (is_array($value)) {
$value = implode(',', $value);
}
$value = $collection->getConnection()->quote($value);
}
if (!in_array($attribute, $this->_joinedAttributes)) {
array_push($this->_joinedAttributes, $attribute);
$att = Mage::getModel('catalog/product')->getResource()->getAttribute($attribute);
if (!$att && $attribute != 'category_ids') {
return null;
}
switch ($attribute) {
case 'sku':
$collection->getSelect()->join(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id', array('sku'));
break;
case 'category_ids':
$collection->getSelect()->joinLeft(array('att_table_' . $attribute => $collection->getTable('catalog/category_product')), 'att_table_' . $attribute . '.product_id = e.entity_id', array('category_id'));
break;
case 'attribute_set_id':
$collection->getSelect()->joinLeft(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id', array('attribute_set_id'));
break;
case 'price':
$collection->addPriceData();
break;
default:
$collection->getSelect()->joinLeft(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id AND att_table_' . $attribute . '.attribute_id = ' . $att->getId(), array('value'));
}
}
switch ($attribute) {
case 'sku':
$where = '(att_table_' . $attribute . '.' . 'sku' . ' ' . $operator . ' ' . $value . ')';
break;
case 'category_ids':
$where = '(IFNULL(att_table_' . $attribute . '.' . 'category_id,\'\')' . ' ' . $operator . ' ' . $value . ' AND e.entity_id ' . $operator . '(SELECT `product_id` FROM `' . $collection->getTable('catalog/category_product') . '` WHERE `category_id` IN' . $value . ')' . ' )';
break;
case 'price':
$where = '(price_index.final_price ' . $operator . ' ' . $value . ')' . ' AND (price_index.min_price ' . $operator . ' ' . $value . ')';
break;
case 'attribute_set_id':
$where = '(IFNULL(att_table_' . $attribute . '.' . 'attribute_set_id,\'\')' . ' ' . $operator . ' ' . $value . ')';
break;
default:
$where = '(IFNULL(att_table_' . $attribute . '.' . 'value,\'\')' . ' ' . $operator . ' ' . $value . ')';
if ($operator == 'FIND_IN_SET' || $operator == '!FIND_IN_SET') {
$where = '(' . $operator . ' ' . $value . ')';
}
}
return $where;
}
/**
* prepare sql for attribute compare
*
* @param object $attribute - attribute class with data
* @param array $joinedAttributes - attribute which was joined into query
* @param Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection $collection - product collection
* @param string $operator - operator for compare
* @param string $value - value for compare
* @return string $where - sql query
*/
public function prepareSqlForAtt($attribute, $joinedAttributes, $collection, $operator, $value)
{
$nOperator = array('==', '!{}', '{}', '!()', '()');
$mOperator = array('=', 'NOT LIKE', 'LIKE', 'NOT IN', 'IN');
//category operator
//$cnOperator = array('==', '!=', '=', '>=', '<=', '>', '<', '!{}', '{}', '!()', '()', 'NOT LIKE', 'LIKE');
$cnOperator = array('==', '!=', '<=', '>=', '=', '>', '<', '!{}', '{}', '!()', '()', 'NOT LIKE', 'LIKE');
//$cmOperator = array('=','!=','!=','!=','!=','!=','NOT IN','IN','NOT IN','IN');
//$cmOperator = array('IN', 'NOT IN', 'IN', 'NOT IN', 'NOT IN', 'NOT IN', 'NO IN', 'NOT IN', 'IN', 'NOT IN', 'IN', 'NOT IN', 'IN');
$cmOperator = array('IN', 'NOT IN', 'NOT IN', 'NOT IN', 'IN', 'NOT IN', 'NO IN', 'NOT IN', 'IN', 'NOT IN', 'IN', 'NOT IN', 'IN');
$operator = $attribute == 'category_ids' ? str_replace($cnOperator, $cmOperator, $operator) : str_replace($nOperator, $mOperator, $operator);
/* Quote rule value depending operator type */
switch ($operator) {
case 'LIKE':
case 'NOT LIKE':
$value = $collection->getConnection()->quote("%{$value}%");
break;
case 'IN':
case 'NOT IN':
$value = '(' . $collection->getConnection()->quote(explode(',', $value)) . ')';
break;
default:
$value = $collection->getConnection()->quote($value);
}
/* */
if (!in_array($attribute, $this->_joinedAttributes)) {
array_push($this->_joinedAttributes, $attribute);
//$this->_joinedAttributes = $joinedAttributes;
$att = Mage::getModel('catalog/product')->getResource()->getAttribute($attribute);
if (!$att && $attribute != 'category_ids') {
return null;
}
switch ($attribute) {
case 'sku':
$collection->getSelect()->join(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id', array('sku'));
break;
case 'category_ids':
$collection->getSelect()->joinLeft(array('att_table_' . $attribute => $collection->getTable('catalog/category_product')), 'att_table_' . $attribute . '.product_id = e.entity_id', array('category_id'));
break;
case 'attribute_set_id':
$collection->getSelect()->joinLeft(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id', array('attribute_set_id'));
break;
case 'price':
$collection->addPriceData();
break;
default:
$collection->getSelect()->joinLeft(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id AND att_table_' . $attribute . '.attribute_id = ' . $att->getId(), array('value'));
}
}
switch ($attribute) {
case 'sku':
$where = '(att_table_' . $attribute . '.' . 'sku' . ' ' . $operator . ' ' . $value . ')';
break;
case 'category_ids':
$where = '(IFNULL(att_table_' . $attribute . '.' . 'category_id,\'\')' . ' ' . $operator . ' ' . $value . ' AND e.entity_id ' . $operator . '(SELECT `product_id` FROM `' . $collection->getTable('catalog/category_product') . '` WHERE `category_id` IN' . $value . ')' . ' )';
break;
case 'price':
$where = $collection->getConnection()->quoteInto('(price_index.final_price ' . $operator . '?)', $value);
break;
case 'attribute_set_id':
$where = '(IFNULL(att_table_' . $attribute . '.' . 'attribute_set_id,\'\')' . ' ' . $operator . ' ' . $value . ')';
break;
default:
$where = '(IFNULL(att_table_' . $attribute . '.' . 'value,\'\')' . ' ' . $operator . ' ' . $value . ')';
}
return $where;
}
