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