/** * 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 do -> show all contacts! if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Query all account contacts.'); } } else { if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Only query visible and enabled account contacts.'); } if (Tinebase_Core::getUser() instanceof Tinebase_Model_FullUser) { $where = '/* is no user */ ' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'true', 'false') . ' OR /* is user */ (' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'false', 'true') . ' AND ' . $db->quoteInto($db->quoteIdentifier('accounts.status') . ' = ?', 'enabled') . " AND " . '(' . $db->quoteInto($db->quoteIdentifier('accounts.visibility') . ' = ?', 'displayed') . ' OR ' . $db->quoteInto($db->quoteIdentifier('accounts.id') . ' = ?', Tinebase_Core::getUser()->getId()) . ')' . ")"; } else { $where = '/* is no user */ ' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'true', 'false') . ' OR /* is user */ (' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'false', 'true') . ' AND ' . $db->quoteInto($db->quoteIdentifier('accounts.status') . ' = ?', 'enabled') . " AND " . $db->quoteInto($db->quoteIdentifier('accounts.visibility') . ' = ?', 'displayed') . ")"; } $_select->where($where); $select = $_select instanceof Zend_Db_Select ? $_select : $_select->getSelect(); $select = Tinebase_Backend_Sql_Abstract::traitGroup($db, $_backend->getTablePrefix(), $select); $_select instanceof Zend_Db_Select ? $_select = $select : $_select->setSelect($select); if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' contacts query ' . $_select->assemble()); } } }
/** * get grants for records * * @param Tinebase_Record_RecordSet $records */ public function getGrantsForRecords(Tinebase_Record_RecordSet $records) { $recordIds = $records->getArrayOfIds(); if (empty($recordIds)) { return; } $select = $this->_getAclSelectByRecordIds($recordIds)->group(array('record_id', 'account_type', 'account_id')); Tinebase_Backend_Sql_Abstract::traitGroup($select); if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' ' . $select); } $stmt = $this->_db->query($select); $grantsData = $stmt->fetchAll(Zend_Db::FETCH_ASSOC); if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' grantsData: ' . print_r($grantsData, true)); } foreach ($grantsData as $grantData) { $givenGrants = explode(',', $grantData['account_grants']); foreach ($givenGrants as $grant) { $grantData[$grant] = TRUE; } $recordGrant = new $this->_modelName($grantData, true); unset($recordGrant->account_grant); $record = $records->getById($recordGrant->record_id); if (!$record->grants instanceof Tinebase_Record_RecordSet) { $record->grants = new Tinebase_Record_RecordSet($this->_modelName); } $record->grants->addRecord($recordGrant); } if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Records with grants: ' . print_r($records->toArray(), true)); } }
/** * fetch one contact of a user identified by his user_id * * @param int $_userId * @return Addressbook_Model_Contact * @throws Addressbook_Exception_NotFound if contact not found */ public function getByUserId($_userId) { $select = $this->_getSelect()->where($this->_db->quoteIdentifier('accounts.id') . ' = ?', $_userId)->limit(1); Tinebase_Backend_Sql_Abstract::traitGroup($select); $stmt = $this->_db->query($select); $queryResult = $stmt->fetch(); $stmt->closeCursor(); if (!$queryResult) { throw new Addressbook_Exception_NotFound('Contact with user id ' . $_userId . ' not found.'); } $contact = $this->_rawDataToRecord($queryResult); return $contact; }
/** * all grants for configs given by array of ids * * @param string $_accountId * @param array $_id => account_grants */ public function getAclForIds($_accountId, $_ids) { $result = array(); if (empty($_ids)) { return $result; } $select = $this->_getAclSelect(array('id' => 'customfield_config.id', 'account_grants' => Tinebase_Backend_Sql_Command::getAggregateFunction($this->_db, $this->_db->quoteIdentifier('customfield_acl.account_grant')))); $select->where($this->_db->quoteInto($this->_db->quoteIdentifier('customfield_config.id') . ' IN (?)', (array) $_ids))->group(array('customfield_config.id', 'customfield_acl.account_type', 'customfield_acl.account_id')); Tinebase_Container::addGrantsSql($select, $_accountId, Tinebase_Model_CustomField_Grant::getAllGrants(), 'customfield_acl'); //if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' ' . $select->__toString()); $select = Tinebase_Backend_Sql_Abstract::traitGroup($this->_db, $this->_tablePrefix, $select); $stmt = $this->_db->query($select); $rows = $stmt->fetchAll(Zend_Db::FETCH_ASSOC); foreach ($rows as $row) { $result[$row['id']] = $row['account_grants']; } return $result; }
/** * update foreign key values * * @param string $_mode create|update * @param Tinebase_Record_Interface $_record */ protected function _updateForeignKeys($_mode, Tinebase_Record_Interface $_record) { if (!empty($this->_foreignTables)) { foreach ($this->_foreignTables as $modelName => $join) { if (!(isset($join['field']) || array_key_exists('field', $join))) { continue; } $idsToAdd = array(); $idsToRemove = array(); if (!empty($_record->{$modelName})) { $idsToAdd = Tinebase_Record_RecordSet::getIdsFromMixed($_record->{$modelName}); } $transactionId = Tinebase_TransactionManager::getInstance()->startTransaction(Tinebase_Core::getDb()); if ($_mode == 'update') { $select = $this->_db->select(); $select->from(array($join['table'] => $this->_tablePrefix . $join['table']), array($join['field']))->where($this->_db->quoteIdentifier($join['table'] . '.' . $join['joinOn']) . ' = ?', $_record->getId()); Tinebase_Backend_Sql_Abstract::traitGroup($select); $stmt = $this->_db->query($select); $currentIds = $stmt->fetchAll(PDO::FETCH_COLUMN, 0); $stmt->closeCursor(); $idsToRemove = array_diff($currentIds, $idsToAdd); $idsToAdd = array_diff($idsToAdd, $currentIds); } if (!empty($idsToRemove)) { $where = '(' . $this->_db->quoteInto($this->_db->quoteIdentifier($this->_tablePrefix . $join['table'] . '.' . $join['joinOn']) . ' = ?', $_record->getId()) . ' AND ' . $this->_db->quoteInto($this->_db->quoteIdentifier($this->_tablePrefix . $join['table'] . '.' . $join['field']) . ' IN (?)', $idsToRemove) . ')'; $this->_db->delete($this->_tablePrefix . $join['table'], $where); } foreach ($idsToAdd as $id) { $recordArray = array($join['joinOn'] => $_record->getId(), $join['field'] => $id); $this->_db->insert($this->_tablePrefix . $join['table'], $recordArray); } Tinebase_TransactionManager::getInstance()->commitTransaction($transactionId); } } }
/** * Calendar optimized search function * * 1. get all events neglecting grants filter * 2. get all related container grants (via resolving) * 3. compute effective grants in PHP and only keep events * user has required grant for * * @TODO rethink if an outer container filter could help * * @param Tinebase_Model_Filter_FilterGroup $_filter * @param Tinebase_Model_Pagination $_pagination * @param boolean $_onlyIds * @return Tinebase_Record_RecordSet|array */ public function search(Tinebase_Model_Filter_FilterGroup $_filter = NULL, Tinebase_Model_Pagination $_pagination = NULL, $_onlyIds = FALSE) { if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Searching events ...'); } if ($_pagination === NULL) { $_pagination = new Tinebase_Model_Pagination(); } $getDeleted = is_object($_filter) && $_filter->getFilter('is_deleted'); $select = parent::_getSelect('*', $getDeleted); $select->joinLeft(array('exdate' => $this->_tablePrefix . 'cal_exdate'), $this->_db->quoteIdentifier('exdate.cal_event_id') . ' = ' . $this->_db->quoteIdentifier($this->_tableName . '.id'), array('exdate' => $this->_dbCommand->getAggregate('exdate.exdate'))); // NOTE: we join here as attendee and role filters need it $select->joinLeft(array('attendee' => $this->_tablePrefix . 'cal_attendee'), $this->_db->quoteIdentifier('attendee.cal_event_id') . ' = ' . $this->_db->quoteIdentifier('cal_events.id'), array()); if (!$getDeleted) { $select->joinLeft(array('dispcontainer' => $this->_tablePrefix . 'container'), $this->_db->quoteIdentifier('dispcontainer.id') . ' = ' . $this->_db->quoteIdentifier('attendee.displaycontainer_id'), array()); $select->where($this->_db->quoteIdentifier('dispcontainer.is_deleted') . ' = 0 OR ' . $this->_db->quoteIdentifier('dispcontainer.is_deleted') . 'IS NULL'); } // remove grantsfilter here as we do grants computation in PHP $grantsFilter = $_filter->getFilter('grants'); if ($grantsFilter) { $_filter->removeFilter('grants'); } // clone the filter, as the filter is also used in the json frontend // and the calendar filter is used in the UI to $clonedFilters = clone $_filter; $calendarFilter = null; foreach ($clonedFilters as $filter) { if ($filter instanceof Calendar_Model_CalendarFilter) { $calendarFilter = $filter; $clonedFilters->removeFilter($filter); break; } } $this->_addFilter($select, $clonedFilters); $select->group($this->_tableName . '.' . 'id'); Tinebase_Backend_Sql_Abstract::traitGroup($select); if ($calendarFilter) { $select1 = clone $select; $select2 = clone $select; $calendarFilter->appendFilterSql1($select1, $this); $calendarFilter->appendFilterSql2($select2, $this); $select = $this->getAdapter()->select()->union(array($select1, $select2)); } $_pagination->appendPaginationSql($select); $stmt = $this->_db->query($select); $rows = (array) $stmt->fetchAll(Zend_Db::FETCH_ASSOC); if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) { Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Event base rows fetched: ' . count($rows) . ' select: ' . $select); } $result = $this->_rawDataToRecordSet($rows); $this->_checkGrants($result, $grantsFilter); return $_onlyIds ? $result->{is_bool($_onlyIds) ? $this->_getRecordIdentifier() : $_onlyIds} : $result; }
/** * get grants for containers assigned to given account of multiple records * * @param Tinebase_Record_RecordSet $_records records to get the grants for * @param string|Tinebase_Model_User $_accountId the account to get the grants for * @param string $_containerProperty container property * @param string $_grantModel * @throws Tinebase_Exception_NotFound * @return array of containers|void */ public function getContainerGrantsOfRecords(Tinebase_Record_RecordSet $_records, $_accountId, $_containerProperty = 'container_id', $_grantModel = 'Tinebase_Model_Grants') { $containerIds = array(); foreach ($_records as $record) { if (isset($record[$_containerProperty]) && !isset($containerIds[Tinebase_Model_Container::convertContainerIdToInt($record[$_containerProperty])])) { $containerIds[Tinebase_Model_Container::convertContainerIdToInt($record[$_containerProperty])] = null; } } if (empty($containerIds)) { return array(); } $accountId = $_accountId instanceof Tinebase_Record_Abstract ? $_accountId->getId() : $_accountId; $select = $this->_getSelect('*', TRUE)->where("{$this->_db->quoteIdentifier('container.id')} IN (?)", array_keys($containerIds))->join(array('container_acl' => SQL_TABLE_PREFIX . 'container_acl'), "{$this->_db->quoteIdentifier('container_acl.container_id')} = {$this->_db->quoteIdentifier('container.id')}", array('*', 'account_grants' => $this->_dbCommand->getAggregate('container_acl.account_grant')))->group('container.id', 'container_acl.account_type', 'container_acl.account_id'); $this->addGrantsSql($select, $accountId, '*'); Tinebase_Backend_Sql_Abstract::traitGroup($select); $stmt = $this->_db->query('/*' . __FUNCTION__ . '*/' . $select); $rows = $stmt->fetchAll(Zend_Db::FETCH_ASSOC); $containers = array(); // add results to container ids and get grants array foreach ($rows as $row) { // NOTE id is non-ambiguous $row['id'] = $row['container_id']; $grantsArray = array_unique(explode(',', $row['account_grants'])); $row['account_grants'] = $this->_getGrantsFromArray($grantsArray, $accountId, $_grantModel)->toArray(); $containers[$row['id']] = new Tinebase_Model_Container($row, TRUE); try { $containers[$row['id']]->path = $containers[$row['id']]->getPath(); } catch (Exception $e) { // @todo is it correct to catch all exceptions here? Tinebase_Exception::log($e); } } return $containers; }
/** * returns all contexts of a given tag * * @param string $_tagId * @return array array of application ids */ public function getContexts($_tagId) { $select = $this->_db->select()->from(array('tags_context' => SQL_TABLE_PREFIX . 'tags_context'), array('application_id' => $this->_dbCommand->getAggregate('application_id')))->where($this->_db->quoteInto($this->_db->quoteIdentifier('tag_id') . ' = ?', $_tagId))->group('tag_id'); Tinebase_Backend_Sql_Abstract::traitGroup($select); $apps = $this->_db->fetchOne($select); if ($apps === '0') { $apps = 'any'; } if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' got tag contexts: ' . $apps); } return explode(',', $apps); }
/** * return containerData of containers which made personal accessible to given account * * @param string|Tinebase_Model_User $_accountId * @param string|Tinebase_Model_Application $_application * @param array|string $_grant * @param bool $_ignoreACL * @return array of array of containerData */ protected function _getOtherUsersContainerData($_accountId, $_application, $_grant, $_ignoreACL = FALSE) { $accountId = Tinebase_Model_User::convertUserIdToInt($_accountId); $application = Tinebase_Application::getInstance()->getApplicationByName($_application); $grant = $_ignoreACL ? '*' : $_grant; $select = $this->_db->select()->from(array('owner' => SQL_TABLE_PREFIX . 'container_acl'), array('account_id'))->join(array('user' => SQL_TABLE_PREFIX . 'container_acl'), "{$this->_db->quoteIdentifier('owner.container_id')} = {$this->_db->quoteIdentifier('user.container_id')}", array())->join(array('container' => SQL_TABLE_PREFIX . 'container'), "{$this->_db->quoteIdentifier('owner.container_id')} = {$this->_db->quoteIdentifier('container.id')}")->join(array('accounts' => SQL_TABLE_PREFIX . 'accounts'), "{$this->_db->quoteIdentifier('owner.account_id')} = {$this->_db->quoteIdentifier('accounts.id')}", array())->where("{$this->_db->quoteIdentifier('owner.account_id')} != ?", $accountId)->where("{$this->_db->quoteIdentifier('owner.account_grant')} = ?", Tinebase_Model_Grants::GRANT_ADMIN)->where("{$this->_db->quoteIdentifier('container.application_id')} = ?", $application->getId())->where("{$this->_db->quoteIdentifier('container.type')} = ?", Tinebase_Model_Container::TYPE_PERSONAL)->where("{$this->_db->quoteIdentifier('container.is_deleted')} = ?", 0, Zend_Db::INT_TYPE)->where("{$this->_db->quoteIdentifier('accounts.status')} = ?", 'enabled')->order('accounts.display_name')->group('owner.account_id'); $this->addGrantsSql($select, $accountId, $grant, 'user'); $select = Tinebase_Backend_Sql_Abstract::traitGroup($this->_db, $this->_tablePrefix, $select); $stmt = $this->_db->query($select); $containersData = $stmt->fetchAll(Zend_Db::FETCH_ASSOC); return $containersData; }
/** * Gets tags of a given records where user has the required right to * The tags are stored in the records $_tagsProperty. * * @param Tinebase_Record_RecordSet $_records the recordSet * @param string $_tagsProperty the property in the record where the tags are in (defaults: 'tags') * @param string $_right the required right current user must have on the tags * @return Tinebase_Record_RecordSet tags of record */ public function getMultipleTagsOfRecords($_records, $_tagsProperty = 'tags', $_right = Tinebase_Model_TagRight::VIEW_RIGHT) { if (count($_records) == 0) { // do nothing return; } $recordIds = $_records->getArrayOfIds(); if (count($recordIds) == 0) { if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Can\'t get tags for records without ids'); } // do nothing return; } // get first record to determine application $first = $_records->getFirstRecord(); $appId = Tinebase_Application::getInstance()->getApplicationByName($first->getApplication())->getId(); $select = $this->_getSelect($recordIds, $appId); $select->group(array('tagging.tag_id', 'tagging.record_id')); Tinebase_Model_TagRight::applyAclSql($select, $_right, $this->_db->quoteIdentifier('tagging.tag_id')); Tinebase_Backend_Sql_Abstract::traitGroup($this->_db, SQL_TABLE_PREFIX, $select); $queryResult = $this->_db->fetchAll($select); //if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' ' . print_r($queryResult, TRUE)); // argh: Tinebase_Model_Tag has no record_id /* $tagsOfRecords = new Tinebase_Record_RecordSet('Tinebase_Model_Tag', $queryResult); $tagsOfRecords->addIndices(array('record_id')); */ // build array with tags (record_id => array of Tinebase_Model_Tag) $tagsOfRecords = array(); foreach ($queryResult as $result) { $tagsOfRecords[$result['record_id']][] = new Tinebase_Model_Tag($result, true); } Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' Getting ' . count($tagsOfRecords) . ' tags for ' . count($_records) . ' records.'); foreach ($_records as $record) { //$record->{$_tagsProperty} = $tagsOfRecords->filter('record_id', $record->getId()); $record->{$_tagsProperty} = new Tinebase_Record_RecordSet('Tinebase_Model_Tag', isset($tagsOfRecords[$record->getId()]) ? $tagsOfRecords[$record->getId()] : array()); } }