/**
* Add stock status limitation to catalog product price index select object
*
* @param Varien_Db_Select $select
* @param string|Zend_Db_Expr $entityField
* @param string|Zend_Db_Expr $websiteField
* @return Mage_CatalogInventory_Model_Resource_Stock_Status
*/
public function prepareCatalogProductIndexSelect(Varien_Db_Select $select, $entityField, $websiteField)
{
$subquery = $this->getReadConnection()->select()->from(array('sub_ciss' => $this->getMainTable()), array('product_id', 'website_id'))->join(array('cis' => $this->getTable('cataloginventory/stock')), 'sub_ciss.stock_id=cis.stock_id', array())->join(array('acsw' => $this->getTable('adm_warehouse/stock_website')), 'acsw.stock_id=cis.stock_id', array())->columns(new Zend_Db_Expr("MAX(sub_ciss.stock_status) AS stock_status"))->where('cis.is_active = 1')->where('acsw.website_id=sub_ciss.website_id OR acsw.website_id=0')->group(array('sub_ciss.product_id', 'sub_ciss.website_id'));
$select->join(array('ciss' => $subquery), "ciss.product_id = {$entityField} AND ciss.website_id = {$websiteField}", array());
$select->where('ciss.stock_status = ?', Mage_CatalogInventory_Model_Stock_Status::STATUS_IN_STOCK);
return $this;
}
/**
* Apply price rule price to price index table
*
* @param Varien_Db_Select $select
* @param array|string $indexTable
* @param string $entityId
* @param string $customerGroupId
* @param string $websiteId
* @param array $updateFields the array of fields for compare with rule price and update
* @param string $websiteDate
* @return Mage_CatalogRule_Model_Resource_Rule_Product_Price
*/
public function applyPriceRuleToIndexTable(Varien_Db_Select $select, $indexTable, $entityId, $customerGroupId, $websiteId, $updateFields, $websiteDate)
{
if (empty($updateFields)) {
return $this;
}
if (is_array($indexTable)) {
foreach ($indexTable as $k => $v) {
if (is_string($k)) {
$indexAlias = $k;
} else {
$indexAlias = $v;
}
break;
}
} else {
$indexAlias = $indexTable;
}
$select->join(array('rp' => $this->getMainTable()), "rp.rule_date = {$websiteDate}", array())->where("rp.product_id = {$entityId} AND rp.website_id = {$websiteId} AND rp.customer_group_id = {$customerGroupId}");
foreach ($updateFields as $priceField) {
$priceCond = $this->_getWriteAdapter()->quoteIdentifier(array($indexAlias, $priceField));
$priceExpr = $this->_getWriteAdapter()->getCheckSql("rp.rule_price < {$priceCond}", 'rp.rule_price', $priceCond);
$select->columns(array($priceField => $priceExpr));
}
$query = $select->crossUpdateFromSelect($indexTable);
$this->_getWriteAdapter()->query($query);
return $this;
}
/**
* @param Varien_Object $filter
* @param array|string|int $value
* @param Varien_Db_Select $select
* @return void
*/
public function prepareSelect($filter, $value, $select)
{
$alias = 'category_idx';
$value = (array) $value;
foreach ($value as $_value) {
$where[] = intval($_value);
}
$select->join(array($alias => Mage::getSingleton('core/resource')->getTableName('catalog/category_product_index')), $alias . '.category_id IN (' . implode(',', $where) . ') AND ' . $alias . '.product_id = e.entity_id', array());
}
public function changedItemsSelectModifier(Varien_Db_Select $select)
{
$select->join(array('alp' => $this->getMainTable()), '`lp`.`id` = `alp`.`listing_product_id`', array());
$select->where('`alp`.`is_variation_product` = 0
OR
(
`alp`.`is_variation_product` = 1
AND
`alp`.`is_variation_matched` = 1
)');
}
/**
* Add attribute filter to select
*
* @param Varien_Db_Select $select
* @param string $attributeCode
* @param string $table the main table name or alias
* @param string $field entity_id field name
* @param int $store
* @param int|string|array $value the filter value
* @return Mage_CatalogIndex_Model_Resource_Data_Abstract
*/
protected function _addAttributeFilter(Varien_Db_Select $select, $attributeCode, $table, $field, $store, $value)
{
$adapter = $this->_getReadAdapter();
$attribute = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, $attributeCode);
/* @var $attribute Mage_Catalog_Model_Resource_Eav_Attribute */
$attributeTable = $attribute->getBackend()->getTable();
if ($attribute->getBackendType() == 'static') {
$tableAlias = sprintf('t_%s', $attribute->getAttributeCode());
$joinCond = join(' AND ', array(sprintf('`%s`.`%s`=`%s`.`entity_id`', $table, $field, $tableAlias)));
$select->join(array($tableAlias => $attributeTable), $joinCond, array())->where(sprintf('%s.%s IN(?)', $tableAlias, $attribute->getAttributeCode()), $value);
} elseif ($attribute->isScopeGlobal()) {
$tableAlias = sprintf('t_%s', $attribute->getAttributeCode());
$joinCond = join(' AND ', array(sprintf('`%s`.`%s`=`%s`.`entity_id`', $table, $field, $tableAlias), $adapter->quoteInto(sprintf('`%s`.`attribute_id`=?', $tableAlias), $attribute->getAttributeId()), $adapter->quoteInto(sprintf('`%s`.`store_id`=?', $tableAlias), 0)));
$select->join(array($tableAlias => $attributeTable), $joinCond, array())->where(sprintf('%s.value IN(?)', $tableAlias), $value);
} else {
$tableGlobal = sprintf('t_global_%s', $attribute->getAttributeCode());
$tableStore = sprintf('t_store_%s', $attribute->getAttributeCode());
$joinCondGlobal = join(' AND ', array(sprintf('`%s`.`%s`=`%s`.`entity_id`', $table, $field, $tableGlobal), $adapter->quoteInto(sprintf('`%s`.`attribute_id`=?', $tableGlobal), $attribute->getAttributeId()), $adapter->quoteInto(sprintf('`%s`.`store_id`=?', $tableGlobal), 0)));
$joinCondStore = join(' AND ', array(sprintf('`%s`.`entity_id`=`%s`.`entity_id`', $tableGlobal, $tableStore), sprintf('`%s`.`attribute_id`=`%s`.`attribute_id`', $tableGlobal, $tableStore), $adapter->quoteInto(sprintf('`%s`.`store_id`=?', $tableStore), $store)));
$whereCond = sprintf('IF(`%s`.`value_id`>0, `%s`.`value`, `%s`.`value`) IN(?)', $tableStore, $tableStore, $tableGlobal);
$select->join(array($tableGlobal => $attributeTable), $joinCondGlobal, array())->joinLeft(array($tableStore => $attributeTable), $joinCondStore, array())->where($whereCond, $value);
}
return $this;
}
/**
* Add stock status limitation to catalog product price index select object
*
* @param Varien_Db_Select $select
* @param string|Zend_Db_Expr $entityField
* @param string|Zend_Db_Expr $websiteField
* @return Mage_CatalogInventory_Model_Resource_Stock_Status
*/
public function prepareCatalogProductIndexSelect(Varien_Db_Select $select, $entityField, $websiteField)
{
$select->join(array('ciss' => $this->getMainTable()), "ciss.product_id = {$entityField} AND ciss.website_id = {$websiteField}", array());
$select->where('ciss.stock_status = ?', Mage_CatalogInventory_Model_Stock_Status::STATUS_IN_STOCK);
return $this;
}
/**
* Add join for catalog/product_website table
*
* Joined table has alias pw
*
* @param Varien_Db_Select $select the select object
* @param string|Zend_Db_Expr $website the limitation of website_id
* @param string|Zend_Db_Expr $product the limitation of product_id
* @return Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Abstract
*/
protected function _addProductWebsiteJoinToSelect($select, $website, $product)
{
$select->join(array('pw' => $this->getTable('catalog/product_website')), "pw.product_id = {$product} AND pw.website_id = {$website}", array());
return $this;
}
public function dbSelectModifier(Varien_Db_Select $dbSelect)
{
$dbSelect->join(array('blp' => Mage::getResourceModel('M2ePro/Buy_Listing_Product')->getMainTable()), '`lp`.`id` = `blp`.`listing_product_id`', array());
$dbSelect->where('`blp`.`is_variation_product` = ' . self::IS_VARIATION_PRODUCT_NO . ' OR (' . '`blp`.`is_variation_product` = ' . self::IS_VARIATION_PRODUCT_YES . ' AND `blp`.`is_variation_matched` = ' . self::IS_VARIATION_MATCHED_YES . ')');
}
/**
* @param Varien_Db_Select $select
* @param $conditionString
* @return mixed|null
*/
protected function _replaceProductTableAlias($select, $conditionString, $oldMainFromClause)
{
if (is_null($conditionString)) {
return null;
}
$adapter = $this->_getReadAdapter();
if (strpos($conditionString, 'e.') === false) {
return $conditionString;
}
$fromPart = $select->getPart(Zend_Db_Select::FROM);
if (!isset($fromPart['main_table'])) {
$select->join(array('main_table' => $oldMainFromClause['tableName']), "`e`.`entity_id` = `main_table`.`entity_id`", null);
}
$oldAlias = array('e' . '.', $adapter->quoteIdentifier('e') . '.');
$newAlias = array('main_table' . '.', $adapter->quoteIdentifier('main_table') . '.');
return $this->_replaceAliasExpr($oldAlias, $newAlias, $conditionString);
}
/**
* fastIndexer method and only used in _getProducts
*
* @param Varien_Db_Select $select
* @param int $storeId
*
* @return $this
*/
private function _excludeDisabledCategories(Varien_Db_Select $select, $storeId)
{
if (false === $this->_getHelper()->excludeDisabledCategories($storeId)) {
return $this;
}
$attributeCode = 'is_active';
if (!isset($this->_categoryAttributes[$attributeCode])) {
$attribute = $this->getCategoryModel()->getResource()->getAttribute($attributeCode);
$this->_categoryAttributes[$attributeCode] = array('entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal(), 'is_static' => $attribute->isStatic());
unset($attribute);
}
$tableAlias = 'tjIsActive';
$tAttrId = (int) $this->_categoryAttributes[$attributeCode]['attribute_id'];
$disableValue = 0;
// <@todo remove duplicate code>
$t1 = 't1' . $tableAlias;
$t2 = 't2' . $tableAlias;
$mainTable = $this->getTable('catalog/category_product');
$valueExpr = $this->_getReadAdapter()->getCheckSql('IFNULL(' . $t2 . '.value_id,0) > 0', $t2 . '.value', $t1 . '.value');
$select->join(array($t1 => $this->_categoryAttributes[$attributeCode]['table']), $mainTable . '.category_id = ' . $t1 . '.entity_id AND ' . $t1 . '.store_id = 0 AND ' . $t1 . '.attribute_id = ' . $tAttrId, array())->joinLeft(array($t2 => $this->_categoryAttributes[$attributeCode]['table']), $t1 . '.entity_id = ' . $t2 . '.entity_id AND ' . $t1 . '.attribute_id = ' . $t2 . '.attribute_id AND ' . $t2 . '.store_id = ' . $storeId, array());
$select->where($valueExpr . ' != ' . $disableValue);
// </@todo remove duplicate code>
return $this;
}
