/**
* Convert operator for sql where
*
* @param string $field
* @param string $operator
* @param string|array $value
* @return string
*/
public function getOperatorCondition($field, $operator, $value)
{
switch ($operator) {
case '!=':
case '>=':
case '<=':
case '>':
case '<':
$selectOperator = sprintf('%s?', $operator);
break;
case '{}':
case '!{}':
if (preg_match('/^.*(category_id)$/', $field) && is_array($value)) {
$selectOperator = ' IN (?)';
} else {
$selectOperator = ' LIKE ?';
}
if (substr($operator, 0, 1) == '!') {
$selectOperator = ' NOT' . $selectOperator;
}
break;
case '[]':
case '![]':
case '()':
case '!()':
$selectOperator = 'FIND_IN_SET(?,' . $this->_adapter->quoteIdentifier($field) . ')';
if (substr($operator, 0, 1) == '!') {
$selectOperator = 'NOT ' . $selectOperator;
}
break;
default:
$selectOperator = '=?';
break;
}
$field = $this->_adapter->quoteIdentifier($field);
if (is_array($value) && in_array($operator, array('==', '!=', '>=', '<=', '>', '<', '{}', '!{}'))) {
$results = array();
foreach ($value as $v) {
$results[] = $this->_adapter->quoteInto("{$field}{$selectOperator}", $v);
}
$result = implode(' AND ', $results);
} elseif (in_array($operator, array('()', '!()', '[]', '![]'))) {
if (!is_array($value)) {
$value = array($value);
}
$results = array();
foreach ($value as $v) {
$results[] = $this->_adapter->quoteInto("{$selectOperator}", $v);
}
$result = implode(in_array($operator, array('()', '!()')) ? ' OR ' : ' AND ', $results);
} else {
$result = $this->_adapter->quoteInto("{$field}{$selectOperator}", $value);
}
return $result;
}
/**
* Refresh redirect to url rewrite relations
*
* @return Enterprise_UrlRewrite_Model_Index_Action_Url_Rewrite_RefreshAbstract
*/
protected function _refreshRelation()
{
$insert = $this->_connection->insertFromSelect($this->_getRefreshRelationSelectSql(), $this->_relationTableName, $this->_relationColumns);
$insert .= sprintf(' ON DUPLICATE KEY UPDATE %1$s = VALUES(%1$s)', $this->_connection->quoteIdentifier('url_rewrite_id'));
$this->_connection->query($insert);
return $this;
}
/**
* Prepare price column
*
* @return Zend_Db_Expr
*/
protected function _calculatePrice()
{
$toPercent = $this->_connection->quote('to_percent');
$byPercent = $this->_connection->quote('by_percent');
$toFixed = $this->_connection->quote('to_fixed');
$byFixed = $this->_connection->quote('by_fixed');
$nA = $this->_connection->quote('N/A');
return $this->_connection->getCaseSql('', array($this->_connection->getIfNullSql(new Zend_Db_Expr('@group_id'), $nA) . ' != cppt.grouped_id' => '@price := ' . $this->_connection->getCaseSql($this->_connection->quoteIdentifier('cppt.action_operator'), array($toPercent => new Zend_Db_Expr('cppt.price * cppt.action_amount/100'), $byPercent => new Zend_Db_Expr('cppt.price * (1 - cppt.action_amount/100)'), $toFixed => $this->_connection->getCheckSql(new Zend_Db_Expr('cppt.action_amount < cppt.price'), new Zend_Db_Expr('cppt.action_amount'), new Zend_Db_Expr('cppt.price')), $byFixed => $this->_connection->getCheckSql(new Zend_Db_Expr('0 > cppt.price - cppt.action_amount'), new Zend_Db_Expr('0'), new Zend_Db_Expr('cppt.price - cppt.action_amount')))), $this->_connection->getIfNullSql(new Zend_Db_Expr('@group_id'), $nA) . ' = cppt.grouped_id AND ' . $this->_connection->getIfNullSql(new Zend_Db_Expr('@action_stop'), new Zend_Db_Expr(0)) . ' = 0' => '@price := ' . $this->_connection->getCaseSql($this->_connection->quoteIdentifier('cppt.action_operator'), array($toPercent => new Zend_Db_Expr('@price * cppt.action_amount/100'), $byPercent => new Zend_Db_Expr('@price * (1 - cppt.action_amount/100)'), $toFixed => $this->_connection->getCheckSql(new Zend_Db_Expr('cppt.action_amount < @price'), new Zend_Db_Expr('cppt.action_amount'), new Zend_Db_Expr('@price')), $byFixed => $this->_connection->getCheckSql(new Zend_Db_Expr('0 > @price - cppt.action_amount'), new Zend_Db_Expr('0'), new Zend_Db_Expr('@price - cppt.action_amount'))))), '@price := @price');
}
/**
* Generate and return trigger body's row
*
* @param string $event
* @param Varien_Object $subscriber
* @return string
*/
protected function _getInsertRow($event, Varien_Object $subscriber)
{
switch ($event) {
case Magento_Db_Sql_Trigger::SQL_EVENT_INSERT:
case Magento_Db_Sql_Trigger::SQL_EVENT_UPDATE:
return sprintf("INSERT IGNORE INTO %s (%s) VALUES (NEW.%s);\n", $this->_connection->quoteIdentifier($subscriber->getChangelogName()), $this->_connection->quoteIdentifier($subscriber->getKeyColumn()), $this->_connection->quoteIdentifier($subscriber->getTargetColumn()));
case Magento_Db_Sql_Trigger::SQL_EVENT_DELETE:
return sprintf("INSERT IGNORE INTO %s (%s) VALUES (OLD.%s);\n", $this->_connection->quoteIdentifier($subscriber->getChangelogName()), $this->_connection->quoteIdentifier($subscriber->getKeyColumn()), $this->_connection->quoteIdentifier($subscriber->getTargetColumn()));
default:
return '';
}
}
/**
* 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()];
}
/**
* Generate unique url key if current url key already occupied
*
* @param Mage_Catalog_Model_Abstract $object
* @return Mage_Catalog_Model_Abstract
*/
protected function _generateNextUrlKeySuffix(Mage_Catalog_Model_Abstract $object)
{
$prefixValue = $object->getData($this->getAttribute()->getAttributeCode());
$requestPathField = new Zend_Db_Expr($this->_connection->quoteIdentifier('value'));
//select increment part of request path and cast expression to integer
$urlIncrementPartExpression = $this->_eavHelper->getCastToIntExpression($this->_connection->getSubstringSql($requestPathField, strlen($prefixValue) + 1, $this->_connection->getLengthSql($requestPathField) . ' - ' . strlen($prefixValue)));
$prefixRegexp = preg_quote($prefixValue);
$orCondition = $this->_connection->select()->orWhere($this->_connection->prepareSqlCondition('value', array('regexp' => '^' . $prefixRegexp . '$')))->orWhere($this->_connection->prepareSqlCondition('value', array('regexp' => '^' . $prefixRegexp . '-[0-9]*$')))->getPart(Zend_Db_Select::WHERE);
$select = $this->_connection->select();
$select->from($this->getAttribute()->getBackendTable(), new Zend_Db_Expr('MAX(ABS(' . $urlIncrementPartExpression . '))'))->where('value LIKE :url_key')->where('entity_id <> :entity_id')->where(implode('', $orCondition));
$bind = array('url_key' => $prefixValue . '%', 'entity_id' => (int) $object->getId());
$suffix = $this->_connection->fetchOne($select, $bind);
if (!is_null($suffix)) {
$suffix = (int) $suffix;
$object->setData($this->getAttribute()->getAttributeCode(), sprintf('%s-%s', $prefixValue, ++$suffix));
}
return $object;
}
/**
* Drop database object
*
* @return Magento_Db_Object
*/
public function drop()
{
$query = 'DROP ' . $this->getDbType() . ' IF EXISTS ' . $this->_adapter->quoteIdentifier($this->_objectName);
$this->_adapter->query($query);
return $this;
}
