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