/** * Add last available view event_id * We don't have any useragent data available just by sending out * an email. However we might have data from previous campaigns that we can borrow. * * We use the address ID to search crosswide around all address * * @todo Add a time limit so we don't use to old data * @param Mzax_Emarketing_Db_Select $select */ protected function joinLastViewEvent(Mzax_Emarketing_Db_Select $select, $type = Mzax_Emarketing_Model_Recipient::EVENT_TYPE_VIEW) { if ($select->hasAnyBindings('country_id')) { return; } $select->joinTable('address_id', 'recipient_address', 'address'); $select->joinTable(array('event_id' => '`address`.`view_id`'), 'recipient_event', 'event'); $select->addBinding('event_id', 'event.event_id'); $select->addBinding('country_id', 'event.country_id'); }
/** * Add last available view event_id * We don't have any useragent data available just by sending out * an email. However we might have data from previous campaigns that we can borrow. * * We use the address ID to search crosswide around all address * * @todo Add a time limit so we don't use to old data * @param Mzax_Emarketing_Db_Select $select */ protected function joinLastViewEvent(Mzax_Emarketing_Db_Select $select) { if ($select->hasAnyBindings('event_id', 'useragent_id')) { return; } $select->joinTable('address_id', 'recipient_address', 'address'); $select->joinTable(array('event_id' => '`address`.`view_id`'), 'recipient_event', 'last_view_event'); $select->addBinding('event_id', 'last_view_event.event_id'); $select->addBinding('useragent_id', 'last_view_event.useragent_id'); }
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) { $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) { $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('order_id', 'sales/shipment', 'shipment'); $query->group(); $query->having($this->getTimeRangeExpr('MIN(`shipment`.`created_at`)', 'shipped_at', false)); }
/** * * (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; } }