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