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