/** * * @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); // 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(); }