/**
* Prepare index select
*
* @param Mage_Core_Model_Website $website
* @param $time
* @return Varien_Db_Select
*/
protected function _prepareIndexSelect(Mage_Core_Model_Website $website, $time)
{
$nA = $this->_connection->quote('N/A');
$this->_connection->query('SET @price := NULL');
$this->_connection->query('SET @group_id := NULL');
$this->_connection->query('SET @action_stop := NULL');
$indexSelect = $this->_connection->select()->from(array('cppt' => $this->_getTemporaryTable()), array())->order(array('cppt.grouped_id', 'cppt.sort_order', 'cppt.rule_product_id'))->columns(array('customer_group_id' => 'cppt.customer_group_id', 'product_id' => 'cppt.product_id', 'rule_price' => $this->_calculatePrice(), 'latest_start_date' => 'cppt.from_date', 'earliest_end_date' => 'cppt.to_date', new Zend_Db_Expr($this->_connection->getCaseSql('', array($this->_connection->getIfNullSql(new Zend_Db_Expr('@group_id'), $nA) . ' != cppt.grouped_id' => new Zend_Db_Expr('@action_stop := cppt.action_stop'), $this->_connection->getIfNullSql(new Zend_Db_Expr('@group_id'), $nA) . ' = cppt.grouped_id' => '@action_stop := ' . $this->_connection->getIfNullSql(new Zend_Db_Expr('@action_stop'), new Zend_Db_Expr(0)) . ' + cppt.action_stop'))), new Zend_Db_Expr('@group_id := cppt.grouped_id'), 'from_time' => 'cppt.from_time', 'to_time' => 'cppt.to_time'));
$select = $this->_connection->select()->from($indexSelect, array())->joinInner(array('dates' => $this->_connection->select()->union(array(new Zend_Db_Expr('SELECT ' . $this->_connection->getDateAddSql($this->_connection->fromUnixtime($time), -1, Varien_Db_Adapter_Interface::INTERVAL_DAY) . ' AS rule_date'), new Zend_Db_Expr('SELECT ' . $this->_connection->fromUnixtime($time) . ' AS rule_date'), new Zend_Db_Expr('SELECT ' . $this->_connection->getDateAddSql($this->_connection->fromUnixtime($time), 1, Varien_Db_Adapter_Interface::INTERVAL_DAY) . ' AS rule_date')))), '1=1', array())->columns(array('rule_product_price_id' => new Zend_Db_Expr('NULL'), 'rule_date' => 'dates.rule_date', 'customer_group_id' => 'customer_group_id', 'product_id' => 'product_id', 'rule_price' => 'MIN(rule_price)', 'website_id' => new Zend_Db_Expr($website->getId()), 'latest_start_date' => 'latest_start_date', 'earliest_end_date' => 'earliest_end_date'))->where(new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " >= from_time"))->where($this->_connection->getCheckSql(new Zend_Db_Expr('to_time = 0'), new Zend_Db_Expr(1), new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " <= to_time")))->group(array('customer_group_id', 'product_id', 'dates.rule_date'));
return $select;
}
/**
* Retrieve select for reindex products of non anchor categories
*
* @param Mage_Core_Model_Store $store
* @return Varien_Db_Select
*/
protected function _getAnchorCategoriesSelect(Mage_Core_Model_Store $store)
{
if (!isset($this->_anchorCategoriesSelect[$store->getId()])) {
/** @var $eavConfig Mage_Eav_Model_Config */
$eavConfig = $this->_factory->getSingleton('eav/config');
$isAnchorAttributeId = $eavConfig->getAttribute(Mage_Catalog_Model_Category::ENTITY, 'is_anchor')->getId();
$statusAttributeId = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'status')->getId();
$visibilityAttributeId = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'visibility')->getId();
$rootCatIds = explode('/', $this->_getPathFromCategoryId($store->getRootCategoryId()));
array_pop($rootCatIds);
$select = $this->_connection->select()->from(array('cc' => $this->_getTable('catalog/category')), array())->joinInner(array('cc2' => $this->_getTable('catalog/category')), 'cc2.path LIKE ' . $this->_connection->getConcatSql(array($this->_connection->quoteIdentifier('cc.path'), $this->_connection->quote('/%'))) . ' AND cc.entity_id NOT IN (' . implode(',', $rootCatIds) . ')', array())->joinInner(array('ccp' => $this->_getTable('catalog/category_product')), 'ccp.category_id = cc2.entity_id', array())->joinInner(array('cpw' => $this->_getTable('catalog/product_website')), 'cpw.product_id = ccp.product_id', array())->joinInner(array('cpsd' => $this->_getTable(array('catalog/product', 'int'))), 'cpsd.entity_id = ccp.product_id AND cpsd.store_id = 0 AND cpsd.attribute_id = ' . $statusAttributeId, array())->joinLeft(array('cpss' => $this->_getTable(array('catalog/product', 'int'))), 'cpss.entity_id = ccp.product_id AND cpss.attribute_id = cpsd.attribute_id' . ' AND cpss.store_id = ' . $store->getId(), array())->joinInner(array('cpvd' => $this->_getTable(array('catalog/product', 'int'))), 'cpvd.entity_id = ccp.product_id AND cpvd.store_id = 0' . ' AND cpvd.attribute_id = ' . $visibilityAttributeId, array())->joinLeft(array('cpvs' => $this->_getTable(array('catalog/product', 'int'))), 'cpvs.entity_id = ccp.product_id AND cpvs.attribute_id = cpvd.attribute_id ' . 'AND cpvs.store_id = ' . $store->getId(), array())->joinInner(array('ccad' => $this->_getTable(array('catalog/category', 'int'))), 'ccad.entity_id = cc.entity_id AND ccad.store_id = 0' . ' AND ccad.attribute_id = ' . $isAnchorAttributeId, array())->joinLeft(array('ccas' => $this->_getTable(array('catalog/category', 'int'))), 'ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad.attribute_id' . ' AND ccas.store_id = ' . $store->getId(), array())->where('cpw.website_id = ?', $store->getWebsiteId())->where($this->_connection->getIfNullSql('cpss.value', 'cpsd.value') . ' = ?', Mage_Catalog_Model_Product_Status::STATUS_ENABLED)->where($this->_connection->getIfNullSql('cpvs.value', 'cpvd.value') . ' IN (?)', array(Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG))->where($this->_connection->getIfNullSql('ccas.value', 'ccad.value') . ' = ?', 1)->columns(array('category_id' => 'cc.entity_id', 'product_id' => 'ccp.product_id', 'position' => new Zend_Db_Expr('ccp.position + 10000'), 'is_parent' => new Zend_Db_Expr('0'), 'store_id' => new Zend_Db_Expr($store->getId()), 'visibility' => new Zend_Db_Expr($this->_connection->getIfNullSql('cpvs.value', 'cpvd.value'))));
$this->_anchorCategoriesSelect[$store->getId()] = $select;
}
return $this->_anchorCategoriesSelect[$store->getId()];
}