/**
* 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;
}
/**
* Get select for all products
*
* @param $store
* @return Varien_Db_Select
*/
protected function _getAllProducts(Mage_Core_Model_Store $store)
{
if (!isset($this->_allProductsSelect[$store->getId()])) {
/** @var $eavConfig Mage_Eav_Model_Config */
$eavConfig = $this->_factory->getSingleton('eav/config');
$statusAttributeId = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'status')->getId();
$visibilityAttributeId = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'visibility')->getId();
$select = $this->_connection->select()->from(array('cp' => $this->_getTable('catalog/product')), array())->joinInner(array('cpw' => $this->_getTable('catalog/product_website')), 'cpw.product_id = cp.entity_id', array())->joinInner(array('cpsd' => $this->_getTable(array('catalog/product', 'int'))), 'cpsd.entity_id = cp.entity_id AND cpsd.store_id = 0 AND cpsd.attribute_id = ' . $statusAttributeId, array())->joinLeft(array('cpss' => $this->_getTable(array('catalog/product', 'int'))), 'cpss.entity_id = cp.entity_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 = cp.entity_id AND cpvd.store_id = 0' . ' AND cpvd.attribute_id = ' . $visibilityAttributeId, array())->joinLeft(array('cpvs' => $this->_getTable(array('catalog/product', 'int'))), 'cpvs.entity_id = cp.entity_id AND cpvs.attribute_id = cpvd.attribute_id ' . 'AND cpvs.store_id = ' . $store->getId(), array())->joinLeft(array('ccp' => $this->_getTable('catalog/category_product')), 'ccp.product_id = cp.entity_id', 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, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_SEARCH))->group('cp.entity_id')->columns(array('category_id' => new Zend_Db_Expr($store->getRootCategoryId()), 'product_id' => 'cp.entity_id', 'position' => new Zend_Db_Expr($this->_connection->getCheckSql('ccp.product_id IS NOT NULL', 'ccp.position', '0')), 'is_parent' => new Zend_Db_Expr($this->_connection->getCheckSql('ccp.product_id IS NOT NULL', '0', '1')), 'store_id' => new Zend_Db_Expr($store->getId()), 'visibility' => new Zend_Db_Expr($this->_connection->getIfNullSql('cpvs.value', 'cpvd.value'))));
$this->_allProductsSelect[$store->getId()] = $select;
}
return $this->_allProductsSelect[$store->getId()];
}
/**
* Get downloadable link price select
*
* @param Varien_Db_Adapter_Interface $write
* @param string $table
*
* @return Zend_Db_Select
*/
public function getDownloadableLinkPriceSelect($write, $table)
{
$dlType = $this->getProductHelper()->getAttribute('links_purchased_separately');
if ($this->getVersionHelper()->isGe1600()) {
$ifPrice = $write->getIfNullSql('dlpw.price_id', 'dlpd.price');
$minPrice = new Zend_Db_Expr('MIN(' . $ifPrice . ')');
$maxPrice = new Zend_Db_Expr('SUM(' . $ifPrice . ')');
} else {
$minPrice = new Zend_Db_Expr('MIN(IF(dlpw.price_id, dlpw.price, dlpd.price))');
$maxPrice = new Zend_Db_Expr('SUM(IF(dlpw.price_id, dlpw.price, dlpd.price))');
}
$select = $write->select()->from(array('i' => $table), array('entity_id', 'customer_group_id', 'website_id'))->join(array('dl' => $dlType->getBackend()->getTable()), "dl.entity_id = i.entity_id AND dl.attribute_id = {$dlType->getAttributeId()} AND dl.store_id = 0", array())->join(array('dll' => $this->getTable('downloadable/link')), 'dll.product_id = i.entity_id', array())->join(array('dlpd' => $this->getTable('downloadable/link_price')), 'dll.link_id = dlpd.link_id AND dlpd.website_id = 0', array())->joinLeft(array('dlpw' => $this->getTable('downloadable/link_price')), 'dlpd.link_id = dlpw.link_id AND dlpw.website_id = i.website_id', array())->where('dl.value = ?', 1)->group(array('i.entity_id', 'i.customer_group_id', 'i.website_id', 'i.store_id'))->columns(array('min_price' => $minPrice, 'max_price' => $maxPrice, 'store_id' => 'i.store_id'));
return $select;
}
/**
* Join url rewrite to select
*
* @param Varien_Db_Select $select
* @param int $storeId
* @return Enterprise_Catalog_Helper_Category_UrlRewrite
*/
public function joinTableToSelect(Varien_Db_Select $select, $storeId)
{
$requestPath = $this->_connection->getIfNullSql('url_rewrite.request_path', 'default_ur.request_path');
$select->joinLeft(array('url_rewrite_category' => $this->_resource->getTableName('enterprise_catalog/category')), 'url_rewrite_category.category_id = main_table.entity_id' . ' AND ' . $this->_connection->quoteInto('url_rewrite_category.store_id = ?', (int) $storeId), array(''))->joinLeft(array('url_rewrite' => $this->_resource->getTableName('enterprise_urlrewrite/url_rewrite')), 'url_rewrite_category.url_rewrite_id = url_rewrite.url_rewrite_id AND url_rewrite.is_system = 1', array(''))->joinLeft(array('default_urc' => $this->_resource->getTableName('enterprise_catalog/category')), 'default_urc.category_id = url_rewrite_category.category_id AND default_urc.store_id = 0', array(''))->joinLeft(array('default_ur' => $this->_resource->getTableName('enterprise_urlrewrite/url_rewrite')), 'default_ur.url_rewrite_id = default_urc.url_rewrite_id AND default_ur.is_system = 1', array('request_path' => $requestPath));
return $this;
}
/**
* Prepare url rewrite left join statement for given select instance and store_id parameter.
*
* @param Varien_Db_Select $select
* @param int $storeId
* @return Enterprise_Catalog_Helper_Product_UrlRewrite
*/
public function joinTableToSelect(Varien_Db_Select $select, $storeId)
{
$requestPath = $this->_connection->getIfNullSql('url_rewrite.request_path', 'default_ur.request_path');
$select->joinLeft(array('url_rewrite_product' => $this->_resource->getTableName('enterprise_catalog/product')), 'url_rewrite_product.product_id = main_table.entity_id ' . 'AND url_rewrite_product.store_id = ' . (int) $storeId, array('url_rewrite_product.product_id' => 'url_rewrite_product.product_id'))->joinLeft(array('url_rewrite' => $this->_resource->getTableName('enterprise_urlrewrite/url_rewrite')), 'url_rewrite_product.url_rewrite_id = url_rewrite.url_rewrite_id AND url_rewrite.is_system = 1', array(''))->joinLeft(array('default_urp' => $this->_resource->getTableName('enterprise_catalog/product')), 'default_urp.product_id = main_table.entity_id AND default_urp.store_id = 0', array(''))->joinLeft(array('default_ur' => $this->_resource->getTableName('enterprise_urlrewrite/url_rewrite')), 'default_ur.url_rewrite_id = default_urp.url_rewrite_id', array('request_path' => $requestPath));
return $this;
}
