protected function _prepareQuery(Mzax_Emarketing_Db_Select $query) { $condition = $this->getDataSetDefault('condition', self::DEFAULT_CONDITION); $status = $this->getDataSetDefault('status', self::DEFAULT_STATUS); if (!$query->hasBinding('subscriber_status')) { if ($query->hasBinding('subscriber_id')) { $query->joinTableLeft('subscriber_id', 'newsletter/subscriber', 'subscriber'); } else { if ($query->hasBinding('customer_id')) { $query->joinTableLeft('customer_id', 'newsletter/subscriber', 'subscriber'); } } $query->addBinding('subscriber_status', 'subscriber.subscriber_status'); } $condition = $condition === 'is' ? '=' : '!='; $query->where("{subscriber_status} {$condition} ?", $status); }
protected function _prepareQuery(Mzax_Emarketing_Db_Select $query) { $categoryIds = $this->getData('value'); if (empty($categoryIds)) { $query->where('FALSE = TRUE'); return; } $categoryIds = $this->_explode($categoryIds); $operator = $this->getDataSetDefault('operator', '()'); $query->joinTable('product_id', 'catalog/category_product', 'link')->group(); $query->addBinding('category_id', 'category_id', 'link'); if ($operator === '()') { $query->where('`link`.`category_id` IN(?)', $categoryIds); } else { $query->where('`link`.`category_id` NOT IN(?)', $categoryIds); } }
/** * * @return Zend_Db_Select */ protected function _prepareQuery(Mzax_Emarketing_Db_Select $query) { $conditions = $this->_getConditions(); $aggregator = $this->getDataSetDefault('aggregator', self::DEFAULT_AGGREGATOR); $expectation = $this->getDataSetDefault('expectation', self::DEFAULT_EXPECTATION); $select = $this->_combineConditions($conditions, $aggregator, $expectation); // if value can match zero include all records if ($this->checkIfMatchZero('value')) { $zeroRecords = $this->getQuery(); // assume all orders have items, no right join required $zeroRecords->setColumn('sum_field', new Zend_Db_Expr('0')); $zeroRecords->setColumn('matches', new Zend_Db_Expr('0')); $select = $this->_select()->union(array($zeroRecords, $select)); } $query->useTemporaryTable($this->getTempTableName()); $query->joinSelect('order_id', $select, 'filter'); $query->addBinding('value', new Zend_Db_Expr('SUM(`filter`.`sum_field`)')); $query->having($this->getWhereSql('value', '{value}')); $query->group(); }
protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select) { $select->addBinding('value', 'DATE_FORMAT({local_date}, "%H")'); }
protected function _prepareQuery(Mzax_Emarketing_Db_Select $query) { $attribute = $this->getAttribute(); $adapter = $query->getAdapter(); $field = $query->joinAttribute($this->_requireBinding, $attribute); $query->addBinding('attribute_value', $field); $operator = $this->getDataSetDefault('operator', $this->helper()->getDefaultOperatorByType($this->getInputType())); $value = $this->getData(self::VALUE_KEY); /* * Relative data attributes */ if ($this->getData('relative')) { $future = $this->getDirection() == 'future'; $usesLocalTime = (bool) $this->getAttributeConfig($attribute, 'uses_local_time', false); if ($this->getAnniversary()) { $query->where($this->getAnniversaryTimeExpr('{attribute_value}', self::VALUE_KEY, $future, $usesLocalTime)); } else { $query->where($this->getTimeRangeExpr('{attribute_value}', self::VALUE_KEY, $future, $usesLocalTime)); } return; } /* * Multi select attributes are saved as list in varchar * (e.g. 123,1457,124,21) * * @todo can we use an index? */ if ($attribute->getFrontendInput() === 'multiselect') { $value = (array) $value; $where = array(); foreach ($value as $v) { $where[] = $adapter->quoteInto("FIND_IN_SET(?, {attribute_value})", $v); } if (strpos($operator, '()') !== false) { $where = implode(' OR ', $where); } else { $where = implode(' AND ', $where); } if (strpos($operator, '!') === 0) { $where = "!({$where})"; } $query->where($where); return; } switch ($operator) { case '!=': case '>=': case '<=': case '>': case '<': return $query->where("{attribute_value} {$operator} ?", $this->_implode($value)); break; case '{}': return $query->where("{attribute_value} LIKE ?", "%{$value}%"); break; case '!{}': return $query->where("{attribute_value} NOT LIKE ?", "%{$value}%"); break; case '()': return $query->where("{attribute_value} IN (?)", $this->_explode($value)); break; case '!()': return $query->where("{attribute_value} NOT IN (?)", $this->_explode($value)); break; default: return $query->where("{attribute_value} = ?", $this->_implode($value)); break; } }
protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select) { $select->addBinding('value', 'event.country_id'); }
/** * * (non-PHPdoc) * @see Mzax_Emarketing_Model_Object_Filter_Component::_prepareQuery() */ protected function _prepareQuery(Mzax_Emarketing_Db_Select $query) { /* @var $adapter Zend_Db_Adapter_Abstract */ $column = $this->getTableColumn(); $adapter = $query->getAdapter(); if (!$column) { throw new Exception("No valid table column defined"); } if ($this->_tableIdFieldName) { $bind = array($this->_tableIdFieldName => $this->_requireBinding); } else { $bind = $this->_requireBinding; } $query->joinTable($bind, $this->_table, $this->_tableAlias); $query->addBinding('column_value', $column->name, $this->_tableAlias); $operator = $this->getDataSetDefault('operator', $this->helper()->getDefaultOperatorByType($this->getInputType())); $value = $this->getData(self::VALUE_KEY); // relative date if ($this->getData('relative')) { $future = $this->getDirection() == 'future'; if ($this->getAnniversary()) { $query->where($this->getAnniversaryTimeExpr('{column_value}', self::VALUE_KEY, $future, $column->applyGmtOffset)); } else { $query->where($this->getTimeRangeExpr('{column_value}', self::VALUE_KEY, $future)); } return; } if ($column->type === 'boolean') { $value = '1'; } switch ($operator) { case '!=': case '>=': case '<=': case '>': case '<': return $query->where("{column_value} {$operator} ?", $value); break; case '{}': return $query->where("{column_value} LIKE ?", "%{$value}%"); break; case '!{}': return $query->where("{column_value} NOT LIKE ?", "%{$value}%"); break; case '()': return $query->where("{column_value} IN (?)", $this->_explode($value)); break; case '!()': return $query->where("{column_value} NOT IN (?)", $this->_explode($value)); break; default: return $query->where("{column_value} = ?", $value); break; } }
protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select) { $select->addBinding('value', $this->_column, 'ua'); }
protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select) { $select->addBinding('value', new Zend_Db_Expr('CONCAT_WS("-", `event`.`country_id`, `event`.`region_id`)')); }
protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select) { $select->addBinding('value', 'DAYOFWEEK({local_date})'); }