/**
* Check if binder has the specified binding
*
* @param string $name
* @return boolean
*/
public function hasBinding($name)
{
if ($this->_select) {
return $this->_select->hasBinding($name);
}
return false;
}
/**
*
* @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);
// Check if we are looking for customer with no orders
if ($this->checkIfMatchZero('orders')) {
$customerId = new Zend_Db_Expr('`customer`.`entity_id`');
// Query with all orders + customers with out any orders
$zeroOrderQuery = $this->getQuery();
$zeroOrderQuery->joinTableRight(array('entity_id' => '{customer_id}'), 'customer/entity', 'customer');
$zeroOrderQuery->setColumn('matches', new Zend_Db_Expr('0'));
$zeroOrderQuery->setColumn('customer_id', $customerId);
$zeroOrderQuery->group($customerId, true);
$select = $this->_select()->union(array($zeroOrderQuery, $select));
// count customer_id AS order_id maybe NULL
// reduce by 1 as we added zero order results as well.
$query->having($this->getWhereSql('orders', 'COUNT({customer_id})-1'));
} else {
$query->having($this->getWhereSql('orders', 'COUNT(`filter`.`order_id`)'));
}
$select->useTemporaryTable($this->getTempTableName());
$query->joinSelect('customer_id', $select, 'filter', 'customer_id');
$query->group();
}
protected function _prepareQuery(Mzax_Emarketing_Db_Select $query)
{
$query->joinTable('customer_id', 'log/customer', 'log');
if ($storeId = $this->getParam('store_id')) {
$query->where('`log`.`store_id` = ?', $storeId);
}
$query->group();
$query->having($this->getTimeRangeExpr('MAX(`log`.`login_at`)', 'login', false));
}
/**
*
* @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);
$query->joinSelect(array('goal_id' => '{order_id}'), $select, 'recipients');
$query->group();
$query->provide('recipient_id', new Zend_Db_Expr('MAX(`recipients`.`id`)'));
}
/**
*
* @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);
$select->useTemporaryTable($this->getTempTableName());
$query->joinSelect('quote_id', $select, 'filter');
$query->group();
}
/**
*
* @return Zend_Db_Select
*/
protected function _prepareQuery(Mzax_Emarketing_Db_Select $query)
{
$this->checkIndexes(true);
$conditions = $this->_getConditions();
$aggregator = $this->getDataSetDefault('aggregator', self::DEFAULT_AGGREGATOR);
$expectation = $this->getDataSetDefault('expectation', self::DEFAULT_EXPECTATION);
$select = $this->_combineConditions($conditions, $aggregator, $expectation);
$select->useTemporaryTable($this->getTempTableName());
$addressId = $query->joinAttribute('customer_id', 'customer/default_billing');
$query->joinSelect(array('id' => $addressId), $select, 'filter');
$query->group();
}
protected function _prepareQuery(Mzax_Emarketing_Db_Select $query)
{
/* @var $adapter Zend_Db_Adapter_Abstract */
$adapter = $query->getAdapter();
$operator = $this->getDataSetDefault('operator', $this->helper()->getDefaultOperatorByType($this->_inputType));
$value = $this->getData(self::VALUE_KEY);
if ($this->_inputType === 'boolean') {
$value = '1';
}
switch ($operator) {
case '!=':
case '>=':
case '<=':
case '>':
case '<':
return $query->where("{{$this->_requireBinding}} {$operator} ?", $value);
break;
case '{}':
return $query->where("{{$this->_requireBinding}} LIKE ?", "%{$value}%");
break;
case '!{}':
return $query->where("{{$this->_requireBinding}} NOT LIKE ?", "%{$value}%");
break;
case '()':
return $query->where("{{$this->_requireBinding}} IN (?)", $this->_explode($value));
break;
case '!()':
return $query->where("{{$this->_requireBinding}} NOT IN (?)", $this->_explode($value));
break;
default:
return $query->where("{{$this->_requireBinding}} = ?", $value);
break;
}
}
protected function _prepareQuery(Mzax_Emarketing_Db_Select $query)
{
$action = $this->getDataSetDefault('action');
switch ($action) {
case self::ACTION_CLICKED:
case self::ACTION_VIEWED:
switch ($action) {
case self::ACTION_CLICKED:
$eventType = Mzax_Emarketing_Model_Recipient::EVENT_TYPE_CLICK;
break;
case self::ACTION_VIEWED:
$eventType = Mzax_Emarketing_Model_Recipient::EVENT_TYPE_VIEW;
break;
}
$query->joinTable(array('recipient_id', 'event_type' => $eventType), 'recipient_event', 'event')->group();
//$select->join($this->_getTable('recipient_event', 'event'), "`event`.`recipient_id` = $recipientId AND `event`.`event_type` = $eventType", null);
$eventTime = '`event`.`captured_at`';
$timeLimit = $this->getTimeExpr('offset', $eventTime);
$query->where("{goal_time} < {$timeLimit}");
$query->where("{goal_time} > {$eventTime}");
break;
default:
$timeLimit = $this->getTimeExpr('offset', '{recipient_sent_at}');
$query->where("{goal_time} < {$timeLimit}");
break;
}
$query->group();
//die($query);
}
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)
{
$conditions = $this->_getConditions();
$aggregator = $this->getDataSetDefault('aggregator');
$expectation = $this->getDataSetDefault('expectation');
$subFilterSelect = $this->_combineConditions($conditions, $aggregator, $expectation);
$subFilterSelect->useTemporaryTable($this->getTempTableName());
$type = $this->getDataSetDefault('direction');
$cond['customer_id'] = '{customer_id}';
if ($type === 'preceded') {
$cond[] = new Zend_Db_Expr('`prev_orders`.`ordered_at` < ' . '{ordered_at}');
$cond[] = new Zend_Db_Expr('`prev_orders`.`ordered_at` > ' . $this->getTimeExpr('offset', '{ordered_at}', true));
} else {
$cond[] = new Zend_Db_Expr('`prev_orders`.`ordered_at` > ' . '{ordered_at}');
$cond[] = new Zend_Db_Expr('`prev_orders`.`ordered_at` < ' . $this->getTimeExpr('offset', '{ordered_at}', true));
}
$query->joinSelect($cond, $subFilterSelect, 'prev_orders');
$query->having($this->getWhereSql('orders', 'COUNT(`order_increment_id`)'));
$query->group();
}
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 _prepareQuery(Mzax_Emarketing_Db_Select $query)
{
$query->joinTable('order_id', 'sales/shipment', 'shipment');
$query->group();
$query->having($this->getTimeRangeExpr('MIN(`shipment`.`created_at`)', 'shipped_at', false));
}
protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select)
{
$select->addBinding('value', 'DAYOFWEEK({local_date})');
}
/**
* Apply the incremental day look back on the date_filter expression
*
* @param Mzax_Emarketing_Db_Select $select
* @param string $lastRecord
*/
protected function applyDateFilter(Mzax_Emarketing_Db_Select $select, $lastRecord = null)
{
$incremental = $this->getOption('incremental');
if ($incremental && !$this->getOption('full')) {
if ($lastRecord === null) {
$lastRecord = $this->getLastRecordTime();
}
if ($lastRecord && $select->hasBinding('date_filter')) {
$select->where("{date_filter} >= {$this->getIncrementalSql()}", $lastRecord);
}
}
}
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', new Zend_Db_Expr('CONCAT_WS("-", `event`.`country_id`, `event`.`region_id`)'));
}
/**
* Set Query
*
* @param Mzax_Emarketing_Db_Select $query
* @return Mzax_Emarketing_Model_Object_Collection
*/
public function setQuery(Mzax_Emarketing_Db_Select $query)
{
$this->_conn = $query->getAdapter();
$this->_select = $query;
return $this;
}
protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select)
{
$select->addBinding('value', $this->_column, 'ua');
}
/**
* Assamble the select object without using temporary
* tables
*
* @return string
*/
public function assembleAll()
{
self::$_assambleAll = true;
try {
$sql = parent::assemble();
self::$_assambleAll = false;
return $sql;
} catch (Exception $e) {
self::$_assambleAll = false;
throw $e;
}
}
protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select)
{
$select->addBinding('value', 'event.country_id');
}
protected function _prepareQuery(Mzax_Emarketing_Db_Select $query)
{
$firstname = $query->joinAttribute('customer_id', 'customer/firstname');
$lastname = $query->joinAttribute('customer_id', 'customer/lastname');
$query->where($this->getWhereSql('name', "CONCAT_WS(' ', {$firstname}, {$lastname})"));
}
protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select)
{
$select->addBinding('value', 'DATE_FORMAT({local_date}, "%H")');
}
/**
*
* (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;
}
}