/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
// prepare value
if ($this->_operator === 'equals' && empty($this->_value)) {
// @see 0009362: allow to filter for empty datetimes
$operator = 'isnull';
$value = array($this->_value);
} else {
$operator = $this->_operator;
$value = $this->_getDateValues($operator, $this->_value);
if (!is_array($value)) {
// NOTE: (array) null is an empty array
$value = array($value);
}
}
// quote field identifier
$field = $this->_getQuotedFieldName($_backend);
$db = Tinebase_Core::getDb();
$dbCommand = Tinebase_Backend_Sql_Command::factory($db);
// append query to select object
foreach ((array) $this->_opSqlMap[$operator]['sqlop'] as $num => $operator) {
if (isset($value[$num]) || array_key_exists($num, $value)) {
if (get_parent_class($this) === 'Tinebase_Model_Filter_Date' || in_array($operator, array('isnull', 'notnull'))) {
$_select->where($field . $operator, $value[$num]);
} else {
$_select->where($dbCommand->setDate($field) . $operator, new Zend_Db_Expr($dbCommand->setDateValue($value[$num])));
}
} else {
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' No filter value found, skipping operator: ' . $operator);
}
}
}
}
/**
* constructor
*/
public function __construct()
{
$this->_db = Tinebase_Core::getDb();
$this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db);
// temporary on the fly creation of table
$this->_dbTable = new Tinebase_Db_Table(array('name' => SQL_TABLE_PREFIX . 'relations', 'primary' => 'id'));
}
/**
* 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 a new single filter action
*
* @param string|array $_fieldOrData
* @param string $_operator
* @param mixed $_value
* @param array $_options
*
* @todo remove legacy code + obsolete params sometimes
*/
public function __construct($_fieldOrData, $_operator = NULL, $_value = NULL, array $_options = array())
{
$this->_db = Tinebase_Core::getDb();
$this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db);
if (is_array($_fieldOrData)) {
$data = $_fieldOrData;
} else {
if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) {
Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Using deprecated constructor syntax. Please pass all filter data in one array (filter field: ' . $_fieldOrData . ').');
}
$data = array('field' => $_fieldOrData, 'operator' => $_operator, 'value' => $_value, 'options' => $_options);
}
foreach (array('field', 'operator', 'value') as $requiredKey) {
if (!(isset($data[$requiredKey]) || array_key_exists($requiredKey, $data))) {
throw new Tinebase_Exception_InvalidArgument('Filter object needs ' . $requiredKey);
}
}
$this->_setOptions(isset($data['options']) ? $data['options'] : array());
$this->setField($data['field']);
$this->setOperator($data['operator']);
$this->setValue($data['value']);
if (isset($data['id'])) {
$this->setId($data['id']);
}
if (isset($data['label'])) {
$this->setLabel($data['label']);
}
}
/**
* the constructor
*
* @param array $_options
*/
public function __construct(array $_options = array())
{
if ($this instanceof Tinebase_EmailUser_Smtp_Interface) {
$this->_configKey = Tinebase_Config::SMTP;
} else {
if ($this instanceof Tinebase_EmailUser_Imap_Interface) {
$this->_configKey = Tinebase_Config::IMAP;
} else {
throw new Tinebase_Exception_UnexpectedValue('Plugin must be instance of Tinebase_EmailUser_Smtp_Interface or Tinebase_EmailUser_Imap_Interface');
}
}
// get email user backend config options (host, dbname, username, password, port)
$emailConfig = Tinebase_Config::getInstance()->get($this->_configKey, new Tinebase_Config_Struct())->toArray();
// merge _config and email backend config
if ($this->_subconfigKey) {
// flatten array
$emailConfig = array_merge($emailConfig[$this->_subconfigKey], $emailConfig);
}
// merge _config and email backend config
$this->_config = array_merge($this->_config, $emailConfig);
// _tablename (for example "dovecot_users")
$this->_userTable = $this->_config['prefix'] . $this->_config['userTable'];
// connect to DB
$this->_getDB();
$this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db);
if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) {
Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' ' . print_r($this->_config, TRUE));
}
}
/**
* 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);
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
* @throws Tinebase_Exception_NotFound
*/
public function appendFilterSql($_select, $_backend)
{
$this->_options['ignoreAcl'] = TRUE;
$this->_resolve();
$quotedDisplayContainerIdentifier = $_backend->getAdapter()->quoteIdentifier('attendee.displaycontainer_id');
$where = empty($this->_containerIds) ? Tinebase_Backend_Sql_Command::getFalseValue($_backend->getAdapter()) : $_select->getAdapter()->quoteInto($this->_getQuotedFieldName($_backend) . ' IN (?)', $this->_containerIds);
$orWhere = empty($this->_containerIds) ? Tinebase_Backend_Sql_Command::getFalseValue($_backend->getAdapter()) : $_select->getAdapter()->quoteInto($quotedDisplayContainerIdentifier . ' IN (?)', $this->_containerIds);
$_select->where($where);
$_select->orWhere($orWhere);
}
/**
* 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;
}
/**
* 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)
{
// prepare value
$value = (array) $this->_getDateValues($this->_operator, $this->_value);
// quote field identifier
$field = $this->_getQuotedFieldName($_backend);
// db
$this->_db = Tinebase_Core::getDb();
// append query to select object
foreach ((array) $this->_opSqlMap[$this->_operator]['sqlop'] as $num => $operator) {
if (array_key_exists($num, $value)) {
if (get_parent_class($this) === 'Tinebase_Model_Filter_Date' || in_array($this->_operator, array('isnull', 'notnull'))) {
$_select->where($field . $operator, $value[$num]);
} else {
$value = Tinebase_Backend_Sql_Command::setDateValue($this->_db, $value[$num]);
$_select->where(Tinebase_Backend_Sql_Command::setDate($this->_db, $field) . $operator, $value);
}
} else {
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' No filter value found, skipping operator: ' . $operator);
}
}
}
}
/**
* 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;
}
public function __construct($_dbAdapter = null, array $_options = array())
{
parent::__construct($_dbAdapter, $_options);
$this->_foreignTables['jpegphoto']['select'] = array('jpegphoto' => Tinebase_Backend_Sql_Command::getIfIsNull($this->_db, $this->_db->quoteIdentifier('addressbook_image.contact_id'), 0, 1));
}
/**
* check if user connected with too many user agent during the last hour
*
* @param Tinebase_Model_FullUser $_user
* @param int $numberOfAllowedUserAgents
* @return bool
*/
protected function _tooManyUserAgents($_user, $numberOfAllowedUserAgents = 3)
{
$result = false;
$db = $this->_backend->getAdapter();
$dbCommand = Tinebase_Backend_Sql_Command::factory($db);
$select = $db->select()->distinct(true)->from($this->_backend->getTablePrefix() . $this->_backend->getTableName(), 'user_agent')->where($db->quoteIdentifier('account_id') . ' = ?', $_user->getId())->where($db->quoteIdentifier('li') . ' > NOW() - ' . $dbCommand->getInterval('HOUR', '1'))->where($db->quoteIdentifier('result') . ' <> ?', Tinebase_Auth::SUCCESS, Zend_Db::PARAM_INT)->limit(10);
if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) {
Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' ' . $select);
}
$stmt = $db->query($select);
if ($stmt->columnCount() > $numberOfAllowedUserAgents) {
if (Tinebase_Core::isLogLevel(Zend_Log::INFO)) {
Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' More than ' . $numberOfAllowedUserAgents . ' different UserAgents? we don\'t trust you!');
}
$result = true;
}
$stmt->closeCursor();
return $result;
}
/**
* perform rollBack on all transactionables with open transactions
*
* @return void
*/
public function rollBack()
{
if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) {
Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . " rollBack request, rollBack all transactionables");
}
foreach ($this->_openTransactionables as $transactionable) {
if ($transactionable instanceof Zend_Db_Adapter_Abstract) {
$transactionable->rollBack();
if ($transactionable instanceof Zend_Db_Adapter_Oracle) {
// Oracle ???
} else {
Tinebase_Backend_Sql_Command::setAutocommit($transactionable, true);
}
}
}
$this->_openTransactionables = array();
$this->_openTransactions = array();
}
/**
* the constructor
*
* @param array $options Options used in connecting, binding, etc.
*/
public function __construct(array $_options = array())
{
parent::__construct($_options);
$this->_db = Tinebase_Core::getDb();
$this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db);
}
/**
* 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 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));
}
}
/**
* 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);
}
}
}
/**
* the constructor
*
* allowed options:
* - modelName
* - tableName
* - tablePrefix
* - modlogActive
*
* @param Zend_Db_Adapter_Abstract $_db (optional)
* @param array $_options (optional)
* @throws Tinebase_Exception_Backend_Database
*/
public function __construct($_dbAdapter = NULL, $_options = array())
{
$this->_db = $_dbAdapter instanceof Zend_Db_Adapter_Abstract ? $_dbAdapter : Tinebase_Core::getDb();
$this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db);
$this->_modelName = isset($_options['modelName']) || array_key_exists('modelName', $_options) ? $_options['modelName'] : $this->_modelName;
$this->_tableName = isset($_options['tableName']) || array_key_exists('tableName', $_options) ? $_options['tableName'] : $this->_tableName;
$this->_tablePrefix = isset($_options['tablePrefix']) || array_key_exists('tablePrefix', $_options) ? $_options['tablePrefix'] : $this->_db->table_prefix;
$this->_modlogActive = isset($_options['modlogActive']) || array_key_exists('modlogActive', $_options) ? $_options['modlogActive'] : $this->_modlogActive;
if (!($this->_tableName && $this->_modelName)) {
throw new Tinebase_Exception_Backend_Database('modelName and tableName must be configured or given.');
}
if (!$this->_db) {
throw new Tinebase_Exception_Backend_Database('Database adapter must be configured or given.');
}
}
/**
* Initializes database procedures if they exist
*/
protected function _initProcedures()
{
$backend = Setup_Backend_Factory::factory();
$dbCommand = Tinebase_Backend_Sql_Command::factory(Tinebase_Core::getDb());
$dbCommand->initProcedures($backend);
}
/**
* 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;
}
/**
* 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');
}
/**
* the constructor
*
*/
private function __construct()
{
$this->_db = Tinebase_Core::getDb();
$this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db);
}
/**
* the constructor
*
* allowed options:
* - modelName
* - tableName
* - tablePrefix
* - modlogActive
*
* @param Zend_Db_Adapter_Abstract $_dbAdapter (optional)
* @param array $_options (optional)
* @throws Tinebase_Exception_Backend_Database
*/
public function __construct($_dbAdapter = NULL, $_options = array())
{
$this->_db = $_dbAdapter instanceof Zend_Db_Adapter_Abstract ? $_dbAdapter : Tinebase_Core::getDb();
$this->_dbCommand = Tinebase_Backend_Sql_Command::factory($this->_db);
$this->_modelName = isset($_options['modelName']) || array_key_exists('modelName', $_options) ? $_options['modelName'] : $this->_modelName;
$this->_tableName = isset($_options['tableName']) || array_key_exists('tableName', $_options) ? $_options['tableName'] : $this->_tableName;
/** @noinspection PhpUndefinedFieldInspection */
$this->_tablePrefix = isset($_options['tablePrefix']) || array_key_exists('tablePrefix', $_options) ? $_options['tablePrefix'] : $this->_db->table_prefix;
$this->_modlogActive = isset($_options['modlogActive']) || array_key_exists('modlogActive', $_options) ? $_options['modlogActive'] : $this->_modlogActive;
foreach ($this->_additionalColumns as $name => $query) {
$this->_additionalColumns[$name] = str_replace("{prefix}", $this->_tablePrefix, $query);
}
if (!($this->_tableName && $this->_modelName)) {
throw new Tinebase_Exception_Backend_Database('modelName and tableName must be configured or given.');
}
if (!$this->_db) {
throw new Tinebase_Exception_Backend_Database('Database adapter must be configured or given.');
}
}
/**
* 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);
}
}
}
/**
* 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);
}
/**
* get user select
*
* @return Zend_Db_Select
*/
protected function _getUserSelectObject()
{
/*
* CASE WHEN `status` = 'enabled' THEN (CASE WHEN NOW() > `expires_at` THEN 'expired'
* WHEN (`login_failures` > 5 AND `last_login_failure_at` + INTERVAL 15 MINUTE > NOW())
* THEN 'blocked' ELSE 'enabled' END) ELSE 'disabled' END
*/
$statusSQL = 'CASE WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('enabled') . ' THEN (';
$statusSQL .= 'CASE WHEN ' . Tinebase_Backend_Sql_Command::setDate($this->_db, 'NOW()') . ' > ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountExpires']) . ' THEN ' . $this->_db->quote('expired') . ' WHEN (' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . " > {$this->_maxLoginFailures} AND " . Tinebase_Backend_Sql_Command::setDate($this->_db, $this->_db->quoteIdentifier($this->rowNameMapping['lastLoginFailure'])) . " + INTERVAL '{$this->_blockTime}' MINUTE > " . Tinebase_Backend_Sql_Command::setDate($this->_db, 'NOW()') . ") THEN 'blocked'" . ' ELSE ' . $this->_db->quote('enabled') . ' END) ELSE ' . $this->_db->quote('disabled') . ' END ';
$select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'accounts', array('accountId' => $this->rowNameMapping['accountId'], 'accountLoginName' => $this->rowNameMapping['accountLoginName'], 'accountLastLogin' => $this->rowNameMapping['accountLastLogin'], 'accountLastLoginfrom' => $this->rowNameMapping['accountLastLoginfrom'], 'accountLastPasswordChange' => $this->rowNameMapping['accountLastPasswordChange'], 'accountStatus' => $statusSQL, 'accountExpires' => $this->rowNameMapping['accountExpires'], 'accountPrimaryGroup' => $this->rowNameMapping['accountPrimaryGroup'], 'accountHomeDirectory' => $this->rowNameMapping['accountHomeDirectory'], 'accountLoginShell' => $this->rowNameMapping['accountLoginShell'], 'accountDisplayName' => $this->rowNameMapping['accountDisplayName'], 'accountFullName' => $this->rowNameMapping['accountFullName'], 'accountFirstName' => $this->rowNameMapping['accountFirstName'], 'accountLastName' => $this->rowNameMapping['accountLastName'], 'accountEmailAddress' => $this->rowNameMapping['accountEmailAddress'], 'lastLoginFailure' => $this->rowNameMapping['lastLoginFailure'], 'loginFailures' => $this->rowNameMapping['loginFailures'], 'contact_id', 'openid', 'visibility'))->joinLeft(SQL_TABLE_PREFIX . 'addressbook', $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'accounts.contact_id') . ' = ' . $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'addressbook.id'), array('container_id' => 'container_id'));
return $select;
}
/**
* 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)
{
// quote field identifier, set action and replace wildcards
$field = $this->_getQuotedFieldName($_backend);
if (!(isset($this->_opSqlMap[$this->_operator]) || array_key_exists($this->_operator, $this->_opSqlMap))) {
throw new Tinebase_Exception_InvalidArgument('Operator "' . $this->_operator . '" not defined in sql map of ' . get_class($this));
}
$action = $this->_opSqlMap[$this->_operator];
// don't remove wildcards for certain operators
// TODO add an option for this?
$value = !in_array($this->_operator, array('in', 'notin')) ? $this->_replaceWildcards($this->_value) : $this->_value;
// check if group by is operator and return if this is the case
if ($this->_operator == 'group') {
$_select->group($this->_field);
}
if (in_array($this->_operator, array('in', 'notin')) && !is_array($value)) {
$value = explode(' ', $value);
}
// this is a text filter, so all items in the filter must be of type text (needed in pgsql)
if (in_array($this->_operator, array('in', 'notin')) && is_array($value)) {
foreach ($value as &$item) {
$item = (string) $item;
}
}
$db = Tinebase_Core::getDb();
if (is_array($value) && empty($value)) {
$_select->where('1=' . (substr($this->_operator, 0, 3) == 'not' ? '1/* empty query */' : '0/* impossible query */'));
return;
}
if ($this->_operator == 'equalsspecial') {
if (is_array($value)) {
foreach ($value as $key => $v) {
$value[$key] = preg_replace('/(\\s+|\\-)/', '%', $v);
}
} else {
$value = preg_replace('/(\\s+|\\-)/', '%', $value);
}
}
if (!in_array($this->_operator, array('in', 'notin'))) {
$where = Tinebase_Core::getDb()->quoteInto(Tinebase_Backend_Sql_Command::factory($db)->prepareForILike($field) . ' ' . $action['sqlop'], $value);
} else {
$where = Tinebase_Core::getDb()->quoteInto($field . $action['sqlop'], $value);
}
if (in_array($this->_operator, array('not', 'notin')) && $value !== '') {
$where = "( {$where} OR {$field} IS NULL)";
}
if (in_array($this->_operator, array('equals', 'equalsspecial', 'contains', 'startswith', 'endswith', 'in')) && $value === '') {
$where = "( {$where} OR {$field} IS NULL)";
}
// finally append query to select object
$_select->where($where);
}
/**
* 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']);
}
}
}
}
}
