/** * 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; }