public static function whereProximity(Zend_Db_Select $select, $centerLat, $centerLong, $fieldLat, $fieldLong, $distance, $calculatedDistanceAs = "calcDistance")
{
self::init();
if (empty($centerLat) || empty($centerLong) || empty($fieldLat) || empty($fieldLong) || empty($distance)) {
return $select;
}
$centerLat = floatval($centerLat);
$centerLong = floatval($centerLong);
$distance = floatval($distance);
if ($distance >= 0 && $distance <= 12450.775) {
$longOffset = $distance / abs(cos(deg2rad($centerLat)) * 69);
$rectLong1 = $centerLong - $longOffset;
$rectLong2 = $centerLong + $longOffset;
$latOffset = $distance / 69;
$rectLat1 = $centerLat - $latOffset;
$rectLat2 = $centerLat + $latOffset;
/**
* @author Pavel Shutin
*/
$select->where("{$fieldLat} >= ? ", $rectLat1)->where("{$fieldLat} <= ? ", $rectLat2)->where("{$fieldLong} >= ? ", $rectLong1)->where("{$fieldLong} <= ? ", $rectLong2);
}
if (!empty($calculatedDistanceAs)) {
/* Distance Formula : 3956 * 2 * ASIN(SQRT( POWER(SIN((orig.lat - dest.lat) * pi()/180 / 2), 2) + COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) * POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2) ))as distance */
$earthDiameter = 7912;
$piOver180 = 0.01745329;
$select->columns(array($calculatedDistanceAs => new Zend_Db_Expr("{$earthDiameter} * ASIN(SQRT( POWER(SIN(({$centerLat} - {$fieldLat}) * {$piOver180} / 2), 2) + COS({$centerLat} * {$piOver180}) * COS({$fieldLat} * {$piOver180}) * POWER(SIN(({$centerLong} - {$fieldLong}) * {$piOver180} / 2), 2) ))")));
/**
* @author Pavel Shutin
*/
$select->having($calculatedDistanceAs . ' <= ?', $distance);
}
//var_dump($select->__toString());exit;
return $select;
}
/**
* filter
*
* @param string $column
* @param string $filter
*/
public function filter($column, $filter)
{
$this->_select->having($column . ' LIKE ?', str_replace('*', '%', $filter));
}
/**
* add to/cc/bcc and flags custom filters
*
* @param Zend_Db_Select $_select
* @param Felamimail_Backend_Cache_Sql_Message $_backend
* @param array $_filterData
* @return void
*/
protected function _addRecipientAndFlagsSql($_select, $_backend, $_filterData)
{
$db = $_backend->getAdapter();
$foreignTables = $_backend->getForeignTables();
// add conditions
$tablename = $foreignTables[$_filterData['field']]['table'];
if ($_filterData['field'] !== 'flags') {
$fieldName = $tablename . '.name';
$fieldEmail = $tablename . '.email';
}
// add filter value
if (!is_array($_filterData['value'])) {
$value = '%' . $_filterData['value'] . '%';
} else {
$value = array();
foreach ((array) $_filterData['value'] as $customValue) {
$value[] = '%' . $customValue . '%';
}
}
if ($_filterData['field'] == 'flags') {
$columns = $_select->getPart(Zend_Db_Select::COLUMNS);
$flags = '';
foreach ($columns as $column) {
if ($column[2] == 'flags') {
$flags = '(' . $column[1] . ')';
break;
}
}
if ($_filterData['operator'] == 'equals' || $_filterData['operator'] == 'contains') {
$_select->having($db->quoteInto($flags . ' LIKE ?', $value));
} else {
if ($_filterData['operator'] == 'in' || $_filterData['operator'] == 'notin') {
if (empty($value)) {
$whereString = $flags . ' IS NULL';
} else {
$value = (array) $value;
$where = array();
$op = $_filterData['operator'] == 'in' ? 'LIKE' : 'NOT LIKE';
$opImplode = $_filterData['operator'] == 'in' ? ' OR ' : ' AND ';
foreach ($value as $flag) {
$where[] = $db->quoteInto($flags . ' ' . $op . ' ?', $flag);
}
$whereString = implode($opImplode, $where);
if ($_filterData['operator'] == 'notin') {
$whereString = '(' . $whereString . ') OR ' . $flags . ' IS NULL';
}
}
$_select->having($whereString);
} else {
$_select->having($db->quoteInto($flags . ' NOT LIKE ? OR ' . $flags . ' IS NULL', $value));
}
}
} else {
$_select->where($db->quoteInto($fieldName . ' LIKE ?', $value) . ' OR ' . $db->quoteInto($fieldEmail . ' LIKE ?', $value));
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
if ($this->_value === 'all') {
$_select->where('1=1');
return;
}
$gs = new Tinebase_Backend_Sql_Filter_GroupSelect($_select);
$adapter = $_backend->getAdapter();
//if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . ' (' . __LINE__ . ') value: ' . print_r($this->_value, true));
foreach ($this->_value as $attenderValue) {
if (in_array($attenderValue['user_type'], array(Calendar_Model_Attender::USERTYPE_USER, Calendar_Model_Attender::USERTYPE_GROUPMEMBER))) {
// @todo user_id might contain filter in the future -> get userids from adressbook controller with contact filter
// transform CURRENTCONTACT
$attenderValue['user_id'] = $attenderValue['user_id'] == Addressbook_Model_Contact::CURRENTCONTACT ? Tinebase_Core::getUser()->contact_id : $attenderValue['user_id'];
$attendee = array(array('user_type' => Calendar_Model_Attender::USERTYPE_USER, 'user_id' => $attenderValue['user_id']), array('user_type' => Calendar_Model_Attender::USERTYPE_GROUPMEMBER, 'user_id' => $attenderValue['user_id']));
} else {
if ($attenderValue['user_type'] == self::USERTYPE_MEMBEROF) {
// resolve group members
$group = Tinebase_Group::getInstance()->getGroupById($attenderValue['user_id']);
$attendee = array();
// fetch list only if list_id is not NULL, otherwise we get back an empty list object
if (!empty($group->list_id)) {
$contactList = Addressbook_Controller_List::getInstance()->get($group->list_id);
foreach ($contactList->members as $member) {
$attendee[] = array('user_type' => Calendar_Model_Attender::USERTYPE_USER, 'user_id' => $member);
$attendee[] = array('user_type' => Calendar_Model_Attender::USERTYPE_GROUPMEMBER, 'user_id' => $member);
}
}
} else {
$attendee = array($attenderValue);
}
}
foreach ($attendee as $attender) {
$gs->orWhere($adapter->quoteInto($adapter->quoteIdentifier('attendee.user_type') . ' = ?', $attender['user_type']) . ' AND ' . $adapter->quoteInto($adapter->quoteIdentifier('attendee.user_id') . ' = ?', $attender['user_id']));
}
}
if (substr($this->_operator, 0, 3) === 'not') {
// join attendee to be excluded as a new column. records having this column NULL don't have the attendee
$dname = 'attendee-not-' . Tinebase_Record_Abstract::generateUID(5);
$_select->joinLeft(array($dname => $_backend->getTablePrefix() . 'cal_attendee'), $adapter->quoteIdentifier($dname . '.cal_event_id') . ' = ' . $adapter->quoteIdentifier($_backend->getTableName() . '.id') . ' AND ' . $gs->getSQL(), array($dname => $_backend->getDbCommand()->getAggregate($dname . '.id')));
$_select->having($_backend->getDbCommand()->getAggregate($dname . '.id') . ' IS NULL');
} else {
$gs->appendWhere(Zend_Db_Select::SQL_OR);
}
}
/**
* Apply the given tree to the query, either as where or as having clause
*
* @param Tree $tree The tree representing the filter
* @param \Zend_Db_Select $baseQuery The query to apply the filter on
*/
public function treeToSql(Tree $tree, $baseQuery)
{
if ($tree->root == null) {
return;
}
$sql = $this->nodeToSqlQuery($tree->normalizeTree($tree->root));
if ($this->filtersAggregate()) {
$baseQuery->having($sql);
} else {
$baseQuery->where($sql);
}
}
/**
* add to/cc/bcc and flags custom filters
*
* @param Zend_Db_Select $_select
* @param Felamimail_Backend_Cache_Sql_Message $_backend
* @param array $_filterData
* @return void
*/
protected function _addRecipientAndFlagsSql($_select, $_backend, $_filterData)
{
$db = $_backend->getAdapter();
$foreignTables = $_backend->getForeignTables();
// add conditions
$tablename = $foreignTables[$_filterData['field']]['table'];
if ($_filterData['field'] !== 'flags') {
$fieldName = $tablename . '.name';
$fieldEmail = $tablename . '.email';
}
// add filter value
if (!is_array($_filterData['value'])) {
$value = '%' . $_filterData['value'] . '%';
} else {
$value = array();
foreach ((array) $_filterData['value'] as $customValue) {
$value[] = '%' . $customValue . '%';
}
}
if ($_filterData['field'] == 'flags') {
$havingColumn = $db instanceof Zend_Db_Adapter_Pdo_Pgsql ? Tinebase_Backend_Sql_Command::factory($db)->getAggregate('felamimail_cache_msg_flag.flag') : 'flags';
if ($_filterData['operator'] == 'equals' || $_filterData['operator'] == 'contains') {
$_select->having($db->quoteInto($havingColumn . ' LIKE ?', $value));
} else {
if ($_filterData['operator'] == 'in' || $_filterData['operator'] == 'notin') {
if (empty($value)) {
$whereString = 'flags IS NULL';
} else {
$value = (array) $value;
$where = array();
$op = $_filterData['operator'] == 'in' ? 'LIKE' : 'NOT LIKE';
$opImplode = $_filterData['operator'] == 'in' ? ' OR ' : ' AND ';
foreach ($value as $flag) {
$where[] = $db->quoteInto('flags ' . $op . ' ?', $flag);
}
$whereString = implode($opImplode, $where);
if ($_filterData['operator'] == 'notin') {
$whereString = '(' . $whereString . ') OR flags IS NULL';
}
}
$_select->having(str_replace('flags', $havingColumn, $whereString));
} else {
$_select->having($db->quoteInto($havingColumn . ' NOT LIKE ? OR ' . $havingColumn . ' IS NULL', $value));
}
}
} else {
$_select->where($db->quoteInto($fieldName . ' LIKE ?', $value) . ' OR ' . $db->quoteInto($fieldEmail . ' LIKE ?', $value));
}
}
/**
* filter
*
* @param string $column
* @param string $filter
*/
public function filter($column, $filter)
{
//TODO: use Sphinx
$this->_select->having($column . ' LIKE ?', '%' . str_replace(array('%', '_'), array('\\%', '\\_'), $filter) . '%');
}
