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); }
/** * * @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); $select->useTemporaryTable($this->getTempTableName()); $query->joinSelect('quote_id', $select, 'filter'); $query->group(); }
/** * * @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) { $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(); }
/** * * @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 _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(); }