/**
* get user select
*
* @return Zend_Db_Select
*/
protected function _getUserSelectObject()
{
$interval = $this->_dbCommand->getDynamicInterval('SECOND', '1', 'CASE WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . ' > 5 THEN 60 ELSE POWER(2, ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . ') END');
$statusSQL = 'CASE WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('enabled') . ' THEN (' . 'CASE WHEN ' . $this->_dbCommand->setDate('NOW()') . ' > ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountExpires']) . ' THEN ' . $this->_db->quote('expired') . ' WHEN ( ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . ' > 0 AND ' . $this->_db->quoteIdentifier($this->rowNameMapping['lastLoginFailure']) . ' + ' . $interval . ' > NOW()) THEN ' . $this->_db->quote('blocked') . ' ELSE ' . $this->_db->quote('enabled') . ' END)' . ' WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('expired') . ' THEN ' . $this->_db->quote('expired') . ' ELSE ' . $this->_db->quote('disabled') . ' END';
$fields = 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', 'NOW()');
// modlog fields have been added later
if ($this->_userTableHasModlogFields()) {
$fields = array_merge($fields, array('created_by', 'creation_time', 'last_modified_by', 'last_modified_time', 'is_deleted', 'deleted_time', 'deleted_by', 'seq'));
}
$select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'accounts', $fields)->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;
}
/**
* get user select
*
* @return Zend_Db_Select
*/
protected function _getUserSelectObject()
{
/*
* CASE WHEN `status` = 'enabled' THEN (CASE WHEN DATE(NOW()) > `expires_at` THEN 'expired'
* WHEN ( `login_failures` > 5 AND DATE(`last_login_failure_at`) + INTERVAL '15' MINUTE > DATE(NOW())) THEN 'blocked'
* ELSE 'enabled' END) WHEN `status` = 'expired' THEN 'expired' ELSE 'disabled' END
*/
$maxLoginFailures = Tinebase_Config::getInstance()->get(Tinebase_Config::MAX_LOGIN_FAILURES, 5);
if ($maxLoginFailures > 0) {
$loginFailuresCondition = 'WHEN ( ' . $this->_db->quoteIdentifier($this->rowNameMapping['loginFailures']) . " > {$maxLoginFailures} AND " . $this->_dbCommand->setDate($this->_db->quoteIdentifier($this->rowNameMapping['lastLoginFailure'])) . " + INTERVAL '{$this->_blockTime}' MINUTE > " . $this->_dbCommand->setDate('NOW()') . ") THEN 'blocked'";
} else {
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' User blocking disabled.');
}
$loginFailuresCondition = '';
}
$statusSQL = 'CASE WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('enabled') . ' THEN (' . 'CASE WHEN ' . $this->_dbCommand->setDate('NOW()') . ' > ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountExpires']) . ' THEN ' . $this->_db->quote('expired') . ' ' . $loginFailuresCondition . ' ELSE ' . $this->_db->quote('enabled') . ' END)' . ' WHEN ' . $this->_db->quoteIdentifier($this->rowNameMapping['accountStatus']) . ' = ' . $this->_db->quote('expired') . ' THEN ' . $this->_db->quote('expired') . ' 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', 'created_by', 'creation_time', 'last_modified_by', 'last_modified_time', 'is_deleted', 'deleted_time', 'deleted_by', 'seq'))->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;
}
