/**
* 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;
}
/**
* get the basic select object to fetch records from the database
*
* @param array|string|Zend_Db_Expr $_cols columns to get, * per default
* @param boolean $_getDeleted get deleted records (if modlog is active)
* @return Zend_Db_Select
*/
protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
{
$select = $this->_getSelectSimple();
$this->_appendEffectiveGrantCalculationSql($select);
$select->joinLeft(array('exdate' => $this->_tablePrefix . 'cal_exdate'), $this->_db->quoteIdentifier('exdate.cal_event_id') . ' = ' . $this->_db->quoteIdentifier($this->_tableName . '.id'), array('exdate' => Tinebase_Backend_Sql_Command::getAggregateFunction($this->_db, $this->_db->quoteIdentifier('exdate.exdate'))));
$select->group($this->_tableName . '.' . 'id');
$this->_traitGroup($select);
return $select;
}
/**
* update to 3.8
* - populate list table with internal groups
*/
public function update_7()
{
$select = $this->_db->select()->from(array('container' => SQL_TABLE_PREFIX . 'container'), array('id' => 'container.id'))->joinLeft(array('applications' => SQL_TABLE_PREFIX . 'applications'), $this->_db->quoteIdentifier('applications.id') . ' = ' . $this->_db->quoteIdentifier('container.application_id'), array())->where("container.name='Internal Contacts' and type='shared' and applications.name='Addressbook'");
$result = $this->_db->fetchRow($select);
$containerId = $result['id'];
$select = $this->_db->select()->from(array('groups' => SQL_TABLE_PREFIX . 'groups'))->group('groups' . '.id')->joinLeft(array('group_members' => SQL_TABLE_PREFIX . 'group_members'), $this->_db->quoteIdentifier('groups' . '.id') . ' = ' . $this->_db->quoteIdentifier('group_members' . '.' . 'group_id'), array())->joinLeft(array('accounts' => SQL_TABLE_PREFIX . 'accounts'), $this->_db->quoteIdentifier('group_members' . '.account_id') . ' = ' . $this->_db->quoteIdentifier('accounts' . '.' . 'id'), array('members' => Tinebase_Backend_Sql_Command::getAggregateFunction($this->_db, $this->_db->quoteIdentifier('accounts' . '.' . 'contact_id'))))->where("groups.visibility='displayed' and list_id IS NULL");
$result = $this->_db->fetchAll($select);
foreach ($result as $row) {
// populate list table
$listId = Tinebase_Record_Abstract::generateUID();
$data = array('id' => $listId, 'name' => $row['name'], 'description' => $row['description'], 'type' => Addressbook_Model_List::LISTTYPE_GROUP, 'container_id' => $containerId);
$this->_db->insert(SQL_TABLE_PREFIX . 'addressbook_lists', $data);
if (!empty($row['members'])) {
foreach (explode(',', $row['members']) as $member) {
$data = array('list_id' => $listId, 'contact_id' => $member);
$this->_db->insert(SQL_TABLE_PREFIX . 'addressbook_list_members', $data);
}
}
// update list_id
$data = array('list_id' => $listId);
$this->_db->update(SQL_TABLE_PREFIX . 'groups', $data, $this->_db->quoteInto("id = ?", $row['id']));
}
$this->setApplicationVersion('Addressbook', '3.8');
}
/**
* get grants 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
*/
public function getGrantsOfRecords(Tinebase_Record_RecordSet $_records, $_accountId, $_containerProperty = 'container_id', $_grantModel = 'Tinebase_Model_Grants')
{
// get container ids
$containers = array();
foreach ($_records as $record) {
if (isset($record[$_containerProperty]) && !isset($containers[Tinebase_Model_Container::convertContainerIdToInt($record[$_containerProperty])])) {
$containers[Tinebase_Model_Container::convertContainerIdToInt($record[$_containerProperty])] = array();
}
}
if (empty($containers)) {
return;
}
$accountId = Tinebase_Model_User::convertUserIdToInt($_accountId);
$select = $this->_getSelect(array('container.id', 'container.name'), TRUE)->where("{$this->_db->quoteIdentifier('container.id')} IN (?)", array_keys($containers))->join(array('container_acl' => SQL_TABLE_PREFIX . 'container_acl'), "{$this->_db->quoteIdentifier('container_acl.container_id')} = {$this->_db->quoteIdentifier('container.id')}", array('container_id', 'account_grants' => Tinebase_Backend_Sql_Command::getAggregateFunction($this->_db, $this->_db->quoteIdentifier('container_acl.account_grant'))))->group(array('container.id', 'container.name', 'container_acl.account_type', 'container_acl.container_id'));
$this->addGrantsSql($select, $accountId, '*');
$stmt = $this->_db->query($select);
$arr = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
// check array for duplicate entries of container_id
$rows = array();
$last_arr = array();
foreach ($arr as $row) {
if ($last_arr['container_id'] === $row['container_id']) {
$row['account_grants'] = $last_arr['account_grants'] . ',' . $row['account_grants'];
end($rows);
$rows[key($rows)] = $row;
} else {
array_push($rows, $row);
}
$last_arr = $row;
}
// 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);
}
// add container & grants to records
foreach ($_records as &$record) {
try {
if (!isset($record->{$_containerProperty})) {
continue;
}
$containerId = $record[$_containerProperty];
if (!is_array($containerId) && !$containerId instanceof Tinebase_Record_Abstract && !empty($containers[$containerId])) {
$record[$_containerProperty] = $containers[$containerId];
$record[$_containerProperty]['path'] = $containers[$containerId]->getPath();
}
} catch (Exception $e) {
// if path is not determinable, skip this container
$_records->removeRecord($record);
}
}
}
/**
* returns rights for given application and accountId
*
* @param string $_application the name of the application
* @param int $_accountId the numeric account id
* @return array list of rights
* @throws Tinebase_Exception_AccessDenied
*
* @todo add right group by to statement if possible or remove duplicates in result array
*/
public function getApplicationRights($_application, $_accountId)
{
$application = Tinebase_Application::getInstance()->getApplicationByName($_application);
if ($application->status != 'enabled') {
throw new Tinebase_Exception_AccessDenied('User has no rights. the application is disabled.');
}
$roleMemberships = $this->getRoleMemberships($_accountId);
$select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'role_rights', array('account_rights' => Tinebase_Backend_Sql_Command::getAggregateFunction($this->_db, $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'role_rights.right'))))->where($this->_db->quoteInto($this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'role_rights.application_id') . ' = ?', $application->getId()))->where($this->_db->quoteInto($this->_db->quoteIdentifier('role_id') . ' IN (?)', $roleMemberships))->group(SQL_TABLE_PREFIX . 'role_rights.application_id');
$stmt = $this->_db->query($select);
$row = $stmt->fetch(Zend_Db::FETCH_ASSOC);
if ($row === false) {
return array();
}
$rights = explode(',', $row['account_rights']);
// remove duplicates
$result = array();
foreach ($rights as $right) {
if (!in_array($right, $result)) {
$result[] = $right;
}
}
return $result;
}
/**
* get the basic select object to fetch records from the database
*
* @param array|string|Zend_Db_Expr $_cols columns to get, * per default
* @param boolean $_getDeleted get deleted records (if modlog is active)
* @return Zend_Db_Select
*/
protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
{
// _userTable.emailUserId=_destinationTable.emailUserId
$userIDMap = $this->_db->quoteIdentifier($this->_userTable . '.' . $this->_propertyMapping['emailUserId']);
$userEmailMap = $this->_db->quoteIdentifier($this->_userTable . '.' . $this->_propertyMapping['emailAddress']);
$select = $this->_db->select()->from($this->_userTable)->group($this->_userTable . '.userid')->limit(1);
// select source from alias table
$select->joinLeft(array('aliases' => $this->_destinationTable), '(' . $userIDMap . ' = ' . $this->_db->quoteIdentifier('aliases.' . $this->_propertyMapping['emailUserId']) . ' AND ' . $userEmailMap . ' = ' . $this->_db->quoteIdentifier('aliases.' . $this->_propertyMapping['emailForwards']) . ')', array($this->_propertyMapping['emailAliases'] => Tinebase_Backend_Sql_Command::getAggregateFunction($this->_db, $this->_db->quoteIdentifier('aliases.' . $this->_propertyMapping['emailAliases']))));
// Select
// select destination from alias table
$select->joinLeft(array('forwards' => $this->_destinationTable), '(' . $userIDMap . ' = ' . $this->_db->quoteIdentifier('forwards.' . $this->_propertyMapping['emailUserId']) . ' AND ' . $userEmailMap . ' = ' . $this->_db->quoteIdentifier('forwards.' . $this->_propertyMapping['emailAliases']) . ')', array($this->_propertyMapping['emailForwards'] => Tinebase_Backend_Sql_Command::getAggregateFunction($this->_db, $this->_db->quoteIdentifier('forwards.' . $this->_propertyMapping['emailForwards']))));
// Select
// append domain if set or domain IS NULL
if (!empty($this->_clientId)) {
$select->where($this->_db->quoteIdentifier($this->_userTable . '.client_idnr') . ' = ?', $this->_clientId);
} else {
$select->where($this->_db->quoteIdentifier($this->_userTable . '.client_idnr') . ' IS NULL');
}
return $select;
}
/**
* add foreign table joins
*
* @param Zend_Db_Select $_select
* @param array|string $_cols columns to get, * per default
*
* @todo find a way to preserve columns if needed without the need for the preserve setting
* @todo get joins from Zend_Db_Select before trying to join the same tables twice (+ remove try/catch)
*/
protected function _addForeignTableJoins(Zend_Db_Select $_select, $_cols, $_groupBy = NULL)
{
if (!empty($this->_foreignTables)) {
$groupBy = $_groupBy !== NULL ? $_groupBy : $this->_tableName . '.' . $this->_identifier;
$_select->group($groupBy);
$cols = (array) $_cols;
foreach ($this->_foreignTables as $foreignColumn => $join) {
// only join if field is in cols
if (in_array('*', $cols) || array_key_exists($foreignColumn, $cols)) {
if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) {
Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' foreign column: ' . $foreignColumn);
}
$selectArray = array_key_exists('select', $join) ? $join['select'] : (array_key_exists('field', $join) && (!array_key_exists('singleValue', $join) || !$join['singleValue']) ? array($foreignColumn => Tinebase_Backend_Sql_Command::getAggregateFunction($this->_db, $this->_db->quoteIdentifier($join['table'] . '.' . $join['field']))) : array($foreignColumn => $join['table'] . '.id'));
$joinId = array_key_exists('joinId', $join) ? $join['joinId'] : $this->_identifier;
$this->_removeColFromSelect($_select, $cols, $foreignColumn);
try {
$_select->joinLeft(array($join['table'] => $this->_tablePrefix . $join['table']), $this->_db->quoteIdentifier($this->_tableName . '.' . $joinId) . ' = ' . $this->_db->quoteIdentifier($join['table'] . '.' . $join['joinOn']), $selectArray);
// need to add it to cols to prevent _removeColFromSelect from removing it
if (array_key_exists('preserve', $join) && $join['preserve'] && array_key_exists($foreignColumn, $selectArray)) {
$cols[$foreignColumn] = $selectArray[$foreignColumn];
}
} catch (Zend_Db_Select_Exception $zdse) {
$_select->columns($selectArray, $join['table']);
}
}
}
}
}
/**
* 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(SQL_TABLE_PREFIX . 'tags_context', array('application_id' => Tinebase_Backend_Sql_Command::getAggregateFunction($this->_db, $this->_db->quoteIdentifier('application_id'))))->where($this->_db->quoteInto($this->_db->quoteIdentifier('tag_id') . ' = ?', $_tagId))->group('tag_id');
$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);
}
