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) { $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); } }
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)); }
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) { $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) { $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 _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; } }
/** * * (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; } }
/** * 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); } } }