示例#1
0
 /**
  * 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;
 }
示例#2
0
 /**
  * 
  * @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();
 }
示例#3
0
 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));
 }
示例#4
0
 /**
  *
  * @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`)'));
 }
示例#5
0
 /**
  * 
  * @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();
 }
示例#6
0
 /**
  * 
  * @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();
 }
示例#7
0
 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;
     }
 }
示例#8
0
 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);
 }
示例#9
0
 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);
 }
示例#10
0
 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();
 }
示例#11
0
 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);
     }
 }
示例#12
0
 /**
  * 
  * @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();
 }
示例#13
0
 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));
 }
示例#14
0
 protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select)
 {
     $select->addBinding('value', 'DAYOFWEEK({local_date})');
 }
示例#15
0
 /**
  * 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);
         }
     }
 }
示例#16
0
 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;
     }
 }
示例#17
0
 protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select)
 {
     $select->addBinding('value', new Zend_Db_Expr('CONCAT_WS("-", `event`.`country_id`, `event`.`region_id`)'));
 }
示例#18
0
 /**
  * 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;
 }
示例#19
0
 protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select)
 {
     $select->addBinding('value', $this->_column, 'ua');
 }
示例#20
0
 /**
  * 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;
     }
 }
示例#21
0
 protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select)
 {
     $select->addBinding('value', 'event.country_id');
 }
示例#22
0
 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})"));
 }
示例#23
0
 protected function prepareAggregationSelect(Mzax_Emarketing_Db_Select $select)
 {
     $select->addBinding('value', 'DATE_FORMAT({local_date}, "%H")');
 }
示例#24
0
 /**
  * 
  * (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;
     }
 }