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