/**
* Check structure of sql query
*
* @param Varien_Db_Select $select
* @return array
*/
public function fetchAllCallback(Varien_Db_Select $select)
{
$whereParts = $select->getPart(Varien_Db_Select::WHERE);
$this->assertCount(2, $whereParts);
$this->assertContains("rule_name IS NOT NULL", $whereParts[0]);
$this->assertContains("rule_name <> ''", $whereParts[1]);
$orderParts = $select->getPart(Varien_Db_Select::ORDER);
$this->assertCount(1, $orderParts);
$expectedOrderParts = array('rule_name', 'ASC');
$this->assertEquals($expectedOrderParts, $orderParts[0]);
return $this->_rules;
}
/**
* Remove price records from where query
*
* @param Varien_Db_Select $select
* @param string $priceExpression
* @return Varien_Db_Select
*/
public function _removePriceFromSelect($select, $priceExpression)
{
$oldWhere = $select->getPart(Varien_Db_Select::WHERE);
$newWhere = array();
foreach ($oldWhere as $cond) {
if (false === strpos($cond, $priceExpression)) {
$newWhere[] = $cond;
}
}
if ($newWhere && substr($newWhere[0], 0, 3) == 'AND') {
$newWhere[0] = substr($newWhere[0], 3);
}
$select->setPart(Varien_Db_Select::WHERE, $newWhere);
return $select;
}
/**
* @param Varien_Db_Select $select
* @param string $attributeCode
* @return $this
*/
public function joinAttribute($select, $attributeCode)
{
/* @var $core Mana_Core_Helper_Data */
$core = Mage::helper(strtolower('Mana_Core'));
/* @var $attribute Mage_Catalog_Model_Resource_Eav_Attribute */
$attribute = $core->collectionFind($this->getAttributes(), 'attribute_code', $attributeCode);
/* @var $db Varien_Db_Adapter_Pdo_Mysql */
$db = $select->getAdapter();
$alias = 'meav_' . $attributeCode;
$storeAlias = 's' . $alias;
$from = $select->getPart(Varien_Db_Select::FROM);
if (!isset($from[$alias])) {
$select->joinLeft(array($alias => $attribute->getBackendTable()), implode(' AND ', array("`{$alias}`.`entity_id` = `e`.`entity_id`", $db->quoteInto("`{$alias}`.`attribute_id` = ?", $attribute->getId()), "`{$alias}`.`store_id` = 0")), null);
$select->joinLeft(array($storeAlias => $attribute->getBackendTable()), implode(' AND ', array("`{$storeAlias}`.`entity_id` = `e`.`entity_id`", $db->quoteInto("`{$storeAlias}`.`attribute_id` = ?", $attribute->getId()), $db->quoteInto("`{$storeAlias}`.`store_id` = ?", Mage::app()->getStore()->getId()))), null);
}
return $this;
}
/**
* Prepare additional price expression sql part
*
* @param Varien_Db_Select $select
* @return Mage_Catalog_Model_Resource_Product_Collection
*/
protected function _preparePriceExpressionParameters($select)
{
// prepare response object for event
$response = new Varien_Object();
$response->setAdditionalCalculations(array());
$tableAliases = array_keys($select->getPart(Zend_Db_Select::FROM));
if (in_array(self::INDEX_TABLE_ALIAS, $tableAliases)) {
$table = self::INDEX_TABLE_ALIAS;
} else {
$table = reset($tableAliases);
}
// prepare event arguments
$eventArgs = array('select' => $select, 'table' => $table, 'store_id' => $this->getStoreId(), 'response_object' => $response);
Mage::dispatchEvent('catalog_prepare_price_select', $eventArgs);
$additional = join('', $response->getAdditionalCalculations());
$this->_priceExpression = $table . '.min_price';
$this->_additionalPriceExpression = $additional;
$this->_catalogPreparePriceSelect = clone $select;
return $this;
}
/**
* Get update table query using select object for join and update
*
* @param Varien_Db_Select $select
* @param string|array $table
* @throws Varien_Db_Exception
* @return string
*/
public function updateFromSelect(Varien_Db_Select $select, $table)
{
if (!is_array($table)) {
$table = array($table => $table);
}
// get table name and alias
$keys = array_keys($table);
$tableAlias = $keys[0];
$tableName = $table[$keys[0]];
$query = sprintf('UPDATE %s', $this->quoteTableAs($tableName, $tableAlias));
// render JOIN conditions (FROM Part)
$joinConds = array();
foreach ($select->getPart(Zend_Db_Select::FROM) as $correlationName => $joinProp) {
if ($joinProp['joinType'] == Zend_Db_Select::FROM) {
$joinType = strtoupper(Zend_Db_Select::INNER_JOIN);
} else {
$joinType = strtoupper($joinProp['joinType']);
}
$joinTable = '';
if ($joinProp['schema'] !== null) {
$joinTable = sprintf('%s.', $this->quoteIdentifier($joinProp['schema']));
}
$joinTable .= $this->quoteTableAs($joinProp['tableName'], $correlationName);
$join = sprintf(' %s %s', $joinType, $joinTable);
if (!empty($joinProp['joinCondition'])) {
$join = sprintf('%s ON %s', $join, $joinProp['joinCondition']);
}
$joinConds[] = $join;
}
if ($joinConds) {
$query = sprintf("%s\n%s", $query, implode("\n", $joinConds));
}
// render UPDATE SET
$columns = array();
foreach ($select->getPart(Zend_Db_Select::COLUMNS) as $columnEntry) {
list($correlationName, $column, $alias) = $columnEntry;
if (empty($alias)) {
$alias = $column;
}
if (!$column instanceof Zend_Db_Expr && !empty($correlationName)) {
$column = $this->quoteIdentifier(array($correlationName, $column));
}
$columns[] = sprintf('%s = %s', $this->quoteIdentifier(array($tableAlias, $alias)), $column);
}
if (!$columns) {
throw new Varien_Db_Exception('The columns for UPDATE statement are not defined');
}
$query = sprintf("%s\nSET %s", $query, implode(', ', $columns));
// render WHERE
$wherePart = $select->getPart(Zend_Db_Select::WHERE);
if ($wherePart) {
$query = sprintf("%s\nWHERE %s", $query, implode(' ', $wherePart));
}
return $query;
}
/**
* Enter description here ...
* @param Varien_Db_Select $select
* @param string $column
*/
public function selectColumn($select, $column)
{
list($expr, $alias) = explode(' AS ', $column);
foreach ($select->getPart(Zend_Db_Select::COLUMNS) as $columnInfo) {
if ($columnInfo[2] == $alias) {
return $this;
}
}
$select->columns($column);
return $this;
}
/**
* Prepare select column list
*
* @param Varien_Db_Select $select
* @param $groupByCondition OPTIONAL
* @return array
* @throws Zend_Db_Exception
*/
public function prepareColumnsList(Varien_Db_Select $select, $groupByCondition = null)
{
if (!count($select->getPart(Zend_Db_Select::FROM))) {
return $select->getPart(Zend_Db_Select::COLUMNS);
}
$columns = $select->getPart(Zend_Db_Select::COLUMNS);
$tables = $select->getPart(Zend_Db_Select::FROM);
$preparedColumns = array();
foreach ($columns as $columnEntry) {
list($correlationName, $column, $alias) = $columnEntry;
if ($column instanceof Zend_Db_Expr) {
if ($alias !== null) {
if (preg_match('/(^|[^a-zA-Z_])^(SELECT)?(SUM|MIN|MAX|AVG|COUNT)\\s*\\(/i', $column, $matches)) {
$column = $this->prepareColumn($column, $groupByCondition);
}
$preparedColumns[strtoupper($alias)] = array(null, $column, $alias);
} else {
throw new Zend_Db_Exception("Can't prepare expression without alias");
}
} else {
if ($column == Zend_Db_Select::SQL_WILDCARD) {
if ($tables[$correlationName]['tableName'] instanceof Zend_Db_Expr) {
throw new Zend_Db_Exception("Can't prepare expression when tableName is instance of Zend_Db_Expr");
}
$tableColumns = $this->_getReadAdapter()->describeTable($tables[$correlationName]['tableName']);
foreach (array_keys($tableColumns) as $col) {
$preparedColumns[strtoupper($col)] = array($correlationName, $col, null);
}
} else {
$columnKey = is_null($alias) ? $column : $alias;
$preparedColumns[strtoupper($columnKey)] = array($correlationName, $column, $alias);
}
}
}
// $select->reset(Zend_Db_Select::COLUMNS);
// $select->setPart(Zend_Db_Select::COLUMNS, array_values($preparedColumns));
return $preparedColumns;
}
/**
* @param Varien_Db_Select $select
*/
protected function getMainAlias($select)
{
$from = $select->getPart(Varien_Db_Select::FROM);
$aliases = array_keys($from);
return $aliases[0];
}
/**
* Prepare select for load
*
* @return string
*/
protected function _prepareSelect(Varien_Db_Select $select)
{
$helper = Mage::getResourceHelper('core');
$unionParts = $select->getPart(Zend_Db_Select::UNION);
if (!empty($unionParts)) {
$select = $helper->limitUnion($select);
}
if ($this->_useAnalyticFunction) {
return $helper->getQueryUsingAnalyticFunction($select);
}
return (string) $select;
}
/**
* @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);
}
/**
* Join Tracker table for the given tracker id
*
* return table alias
*
* @param Varien_Db_Select $select
* @param integer $trackerId
* @param string $table
* @return string
*/
protected function _joinTracker(Varien_Db_Select $select, $trackerId, $table)
{
$alias = 'tracker_' . $trackerId;
// already joined
if (array_key_exists($alias, $select->getPart(Varien_Db_Select::FROM))) {
return $alias;
}
$description = $this->getReadAdapter()->describeTable($this->getTable($table));
$joins = array('campaign_id', 'variation_id', 'dimension_id', 'value_id', 'date');
foreach ($joins as $join) {
if (isset($description[$join])) {
$cond[] = "`report`.`{$join}` = `{$alias}`.`{$join}`";
}
}
$cond[] = "`{$alias}`.`tracker_id` = {$trackerId}";
// seperate join condition is a bit faster
//$cond[] = "((`report`.`variation_id` = `$alias`.`variation_id`) OR (`report`.`variation_id` IS NULL && `$alias`.`variation_id` IS NULL))";
/* if($this->getParam(self::VARIATION)) {
$cond[] = "`report`.`variation_id` = `$alias`.`variation_id`";
}
else {
$cond[] = "`$alias`.`variation_id` IS NULL";
}*/
$cond = implode(' AND ', $cond);
$select->joinLeft(array($alias => $this->getTable($table)), $cond, null);
return $alias;
}
