/** * 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 === self::VALUE_NOTSET) { return; } $action = $this->_opSqlMap[$this->_operator]; $db = $_backend->getAdapter(); // prepare value $value = $this->_value ? 1 : 0; if (!empty($this->_options['fields'])) { foreach ((array) $this->_options['fields'] as $fieldName) { $quotedField = $db->quoteIdentifier(strpos($fieldName, '.') === false ? $_backend->getTableName() . '.' . $fieldName : $fieldName); if ($value) { $_select->where($quotedField . $action['sqlop'], $value); } else { $_select->orwhere($quotedField . $action['sqlop'], $value); } } } else { if (!empty($this->_options['leftOperand'])) { $_select->where($this->_options['leftOperand'] . $action['sqlop'], $value); } else { $_select->where($this->_getQuotedFieldName($_backend) . $action['sqlop'], $value); } } }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend */ public function appendFilterSql($_select, $_backend) { if (empty($this->_value)) { $_select->where('1=1/* empty query */'); return; } $db = Tinebase_Core::getDb(); switch ($this->_operator) { case 'contains': case 'equals': case 'startswith': $queries = explode(' ', $this->_value); foreach ($queries as $query) { $whereParts = array(); foreach ($this->_options['fields'] as $qField) { // if field has . in name, then we already have tablename if (strpos($qField, '.') !== FALSE) { $whereParts[] = Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent($db->quoteIdentifier($qField))) . ' ' . Tinebase_Backend_Sql_Command::factory($db)->getLike() . Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent('(?)')); } else { $whereParts[] = Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent($db->quoteIdentifier($_backend->getTableName() . '.' . $qField))) . ' ' . Tinebase_Backend_Sql_Command::factory($db)->getLike() . Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent('(?)')); } } $whereClause = ''; if (!empty($whereParts)) { $whereClause = implode(' OR ', $whereParts); } if (!empty($whereClause)) { if ($this->_operator == 'equals') { $_select->where($db->quoteInto($whereClause, trim($query))); } else { if ($this->_operator == 'startswith') { $_select->where($db->quoteInto($whereClause, trim($query) . '%')); } else { $_select->where($db->quoteInto($whereClause, '%' . trim($query) . '%')); } } } } break; case 'in': foreach ($this->_options['fields'] as $qField) { // if field has . in name, then we allready have tablename if (strpos($qField, '.') !== FALSE) { $whereParts[] = $db->quoteInto($db->quoteIdentifier($qField) . ' IN (?)', (array) $this->_value); } else { $whereParts[] = $db->quoteInto($db->quoteIdentifier($_backend->getTableName() . '.' . $qField) . ' IN (?)', (array) $this->_value); } } if (!empty($whereParts)) { $whereClause = implode(' OR ', $whereParts); } if (!empty($whereClause)) { $_select->where($whereClause); } break; default: throw new Tinebase_Exception_InvalidArgument('Operator not defined: ' . $this->_operator); } }
/** * append grants acl filter * * @param Zend_Db_Select $select * @param Tinebase_Backend_Sql_Abstract $backend * @param Tinebase_Model_User $user */ protected function _appendGrantsFilter($select, $backend, $user) { $db = $backend->getAdapter(); $select->join(array($this->_aclTableName => SQL_TABLE_PREFIX . $this->_aclTableName), "{$db->quoteIdentifier($this->_aclTableName . '.record_id')} = {$db->quoteIdentifier($backend->getTableName() . '.id')}", array()); Tinebase_Container::addGrantsSql($select, $user, $this->_requiredGrants, $this->_aclTableName); if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' $select after appending grants sql: ' . $select); } }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend */ public function appendFilterSql($_select, $_backend) { $db = $_backend->getAdapter(); // prepare value $value = $this->_value ? 1 : 0; if ($value) { // nothing to filter } else { $_select->where($db->quoteIdentifier($_backend->getTableName() . '.is_open') . ' = 1'); } }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend */ public function appendFilterSql($_select, $_backend) { if (empty($this->_value)) { $_select->where('1=1/* empty query */'); return; } $db = Tinebase_Core::getDb(); switch ($this->_operator) { case 'contains': $queries = explode(' ', $this->_value); foreach ($queries as $query) { $whereParts = array(); foreach ($this->_options['fields'] as $qField) { $whereParts[] = $db->quoteIdentifier($_backend->getTableName() . '.' . $qField) . ' ' . Tinebase_Backend_Sql_Command::getLike($db) . ' ?'; } $whereClause = ''; if (!empty($whereParts)) { $whereClause = implode(' OR ', $whereParts); } if (!empty($whereClause)) { $_select->where($db->quoteInto($whereClause, '%' . trim($query) . '%')); } } break; case 'in': foreach ($this->_options['fields'] as $qField) { $whereParts[] = $db->quoteInto($db->quoteIdentifier($_backend->getTableName() . '.' . $qField) . ' IN (?)', (array) $this->_value); } if (!empty($whereParts)) { $whereClause = implode(' OR ', $whereParts); } if (!empty($whereClause)) { $_select->where($whereClause); } break; default: throw new Tinebase_Exception_InvalidArgument('Operator not defined: ' . $this->_operator); } Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' SQL filter: ' . $_select->assemble()); }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend */ public function appendFilterSql($_select, $_backend) { if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' ' . 'Adding Path filter for: ' . $_backend->getModelName()); } $this->_resolvePathIds(); $idField = isset($this->_options['idProperty']) || array_key_exists('idProperty', $this->_options) ? $this->_options['idProperty'] : 'id'; $db = $_backend->getAdapter(); $qField = $db->quoteIdentifier($_backend->getTableName() . '.' . $idField); if (empty($this->_pathRecordIds)) { $_select->where('1=0'); } else { $_select->where($db->quoteInto("{$qField} IN (?)", $this->_pathRecordIds)); } }
/** * appends custom filters to a given select object * - add user phone ids to filter * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend * @return void */ public function appendFilterSql($_select, $_backend) { $accountIdFilter = $this->_findFilter('account_id'); if ($accountIdFilter !== NULL) { $db = Tinebase_Core::getDb(); $backend = new Voipmanager_Backend_Snom_Phone(); $_validPhoneIds = $backend->getValidPhoneIds($accountIdFilter->getValue()); if (empty($_validPhoneIds)) { $_select->where('1=0'); } else { $_select->where($db->quoteInto($db->quoteIdentifier($_backend->getTableName() . '.id') . ' IN (?)', $_validPhoneIds)); } // remove filter $this->_removeFilter('account_id'); } }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend */ public function appendFilterSql($_select, $_backend) { if ($this->_accountId === '0') { // get anyones preferences $field = $_backend->getAdapter()->quoteIdentifier($_backend->getTableName() . '.account_type'); $_select->where(Tinebase_Core::getDb()->quoteInto($field . '= ?', Tinebase_Acl_Rights::ACCOUNT_TYPE_ANYONE)); } else { $conditions = array(array('condition' => Tinebase_Model_Filter_FilterGroup::CONDITION_AND, 'filters' => array(array('field' => 'account_id', 'operator' => 'equals', 'value' => $this->_accountId), array('field' => 'account_type', 'operator' => 'equals', 'value' => $this->_accountType))), array('field' => 'account_type', 'operator' => 'equals', 'value' => Tinebase_Acl_Rights::ACCOUNT_TYPE_ANYONE)); // add groups if accountType is user if ($this->_accountType === Tinebase_Acl_Rights::ACCOUNT_TYPE_USER) { $groups = Tinebase_Group::getInstance()->getGroupMemberships($this->_accountId); $conditions[] = array('condition' => Tinebase_Model_Filter_FilterGroup::CONDITION_AND, 'filters' => array(array('field' => 'account_id', 'operator' => 'in', 'value' => $groups), array('field' => 'account_type', 'operator' => 'equals', 'value' => Tinebase_Acl_Rights::ACCOUNT_TYPE_GROUP))); } $filter = new Tinebase_Model_PreferenceFilter($conditions, Tinebase_Model_Filter_FilterGroup::CONDITION_OR); Tinebase_Backend_Sql_Filter_FilterGroup::appendFilters($_select, $filter, $_backend); } }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend */ public function appendFilterSql($_select, $_backend) { if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' ' . 'Adding Relation filter: ' . $_backend->getModelName() . ' <-> ' . $this->_options['related_model']); } $this->_resolveForeignIds(); $ownIds = $this->_getOwnIds($_backend->getModelName()); $idField = array_key_exists('idProperty', $this->_options) ? $this->_options['idProperty'] : 'id'; $db = $_backend->getAdapter(); $qField = $db->quoteIdentifier($_backend->getTableName() . '.' . $idField); $_select->where($db->quoteInto("{$qField} IN (?)", empty($ownIds) ? ' ' : $ownIds)); }
/** * 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); } }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend * @throws Tinebase_Exception_InvalidArgument */ public function appendFilterSql($_select, $_backend) { if (empty($this->_value)) { $_select->where('1=1/* empty query */'); return; } $db = $_backend->getAdapter(); $sqlCommand = Tinebase_Backend_Sql_Command::factory($db); if (0 === strpos($this->_operator, 'not')) { $not = true; } else { $not = false; } switch ($this->_operator) { case 'contains': case 'notcontains': case 'equals': case 'not': case 'startswith': case 'endswith': $queries = explode(' ', $this->_value); foreach ($queries as $query) { $whereParts = array(); foreach ($this->_options['fields'] as $qField) { // if field has . in name, then we already have tablename if (strpos($qField, '.') !== FALSE) { $whereParts[] = $sqlCommand->prepareForILike($sqlCommand->getUnaccent($db->quoteIdentifier($qField))) . ' ' . ($not ? 'NOT ' : '') . $sqlCommand->getLike() . $sqlCommand->prepareForILike($sqlCommand->getUnaccent('(?)')); } else { $whereParts[] = $sqlCommand->prepareForILike($sqlCommand->getUnaccent($db->quoteIdentifier($_backend->getTableName() . '.' . $qField))) . ' ' . ($not ? 'NOT ' : '') . $sqlCommand->getLike() . $sqlCommand->prepareForILike($sqlCommand->getUnaccent('(?)')); } } $whereClause = ''; if (!empty($whereParts)) { if ($not) { $whereClause = implode(' AND ', $whereParts); } else { $whereClause = implode(' OR ', $whereParts); } } if (!empty($whereClause)) { $query = trim($query); if ($this->_operator === 'startswith') { $query .= '%'; } else { if ($this->_operator === 'contains' || $this->_operator === 'notcontains') { $query = '%' . $query . '%'; } else { if ($this->_operator === 'endswith') { $query = '%' . $query; } } } $_select->where($db->quoteInto($whereClause, $query)); } } break; case 'notin': case 'in': foreach ($this->_options['fields'] as $qField) { // if field has . in name, then we allready have tablename if (strpos($qField, '.') !== FALSE) { $whereParts[] = $db->quoteInto($db->quoteIdentifier($qField) . ($not ? ' NOT' : '') . ' IN (?)', (array) $this->_value); } else { $whereParts[] = $db->quoteInto($db->quoteIdentifier($_backend->getTableName() . '.' . $qField) . ($not ? ' NOT' : '') . ' IN (?)', (array) $this->_value); } } if (!empty($whereParts)) { if ($not) { $whereClause = implode(' AND ', $whereParts); } else { $whereClause = implode(' OR ', $whereParts); } } if (!empty($whereClause)) { $_select->where($whereClause); } break; default: throw new Tinebase_Exception_InvalidArgument('Operator not defined: ' . $this->_operator); } // append advanced search filter if configured if (isset($this->_options['relatedModels']) && isset($this->_options['modelName'])) { $relationFilter = $this->_getAdvancedSearchFilter($this->_options['modelName'], $this->_options['relatedModels']); if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Got relation filter: ' . ($relationFilter instanceof Tinebase_Model_Filter_Abstract ? print_r($relationFilter->toArray(), true) : '')); } if ($relationFilter) { $relationSelect = new Tinebase_Backend_Sql_Filter_GroupSelect($_select); $relationFilter->appendFilterSql($relationSelect, $_backend); $relationSelect->appendWhere($not ? Zend_Db_Select::SQL_AND : Zend_Db_Select::SQL_OR); } } }