/**
* appends sql to given select statement
*
* @param Tinebase_Backend_Sql_Filter_GroupSelect $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
$db = $_backend->getAdapter();
$value = $this->_value ? 1 : 0;
if ($value) {
// nothing to do -> show all lists!
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Query all lists.');
}
} else {
if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) {
Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Only query visible lists.');
}
$_select->join(array('groupvisibility' => $db->table_prefix . 'groups'), $db->quoteIdentifier('groupvisibility.list_id') . ' = ' . $db->quoteIdentifier('addressbook_lists.id'), array());
$_select->where($db->quoteIdentifier('groupvisibility.visibility') . ' = ?', 'displayed');
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
if ($this->_value === 'all') {
$_select->where('1=1');
return;
}
$gs = new Tinebase_Backend_Sql_Filter_GroupSelect($_select);
$adapter = $_backend->getAdapter();
$isExcept = strpos($this->_operator, 'Except') !== false;
$sign = $isExcept ? '<>' : '=';
//if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . ' (' . __LINE__ . ') value: ' . print_r($this->_value, true));
foreach ($this->_value as $attenderValue) {
if (in_array($attenderValue['user_type'], array(Calendar_Model_Attender::USERTYPE_USER, Calendar_Model_Attender::USERTYPE_GROUPMEMBER))) {
// @todo user_id might contain filter in the future -> get userids from adressbook controller with contact filter
// transform CURRENTCONTACT
$attenderValue['user_id'] = $attenderValue['user_id'] == Addressbook_Model_Contact::CURRENTCONTACT ? Tinebase_Core::getUser()->contact_id : $attenderValue['user_id'];
$attendee = array(array('user_type' => Calendar_Model_Attender::USERTYPE_USER, 'user_id' => $attenderValue['user_id']));
if (!$isExcept) {
$attendee[] = array('user_type' => Calendar_Model_Attender::USERTYPE_GROUPMEMBER, 'user_id' => $attenderValue['user_id']);
}
} else {
if ($attenderValue['user_type'] == self::USERTYPE_MEMBEROF) {
// resolve group members
$group = Tinebase_Group::getInstance()->getGroupById($attenderValue['user_id']);
$attendee = array();
// fetch list only if list_id is not NULL, otherwise we get back an empty list object
if (!empty($group->list_id)) {
$contactList = Addressbook_Controller_List::getInstance()->get($group->list_id);
foreach ($contactList->members as $member) {
$attendee[] = array('user_type' => Calendar_Model_Attender::USERTYPE_USER, 'user_id' => $member);
if (!$isExcept) {
$attendee[] = array('user_type' => Calendar_Model_Attender::USERTYPE_GROUPMEMBER, 'user_id' => $member);
}
}
}
} else {
$attendee = array($attenderValue);
}
}
foreach ($attendee as $attender) {
$gs->orWhere(($isExcept ? '' : $adapter->quoteInto($adapter->quoteIdentifier('attendee.user_type') . ' = ?', $attender['user_type']) . ' AND ') . $adapter->quoteInto($adapter->quoteIdentifier('attendee.user_id') . ' ' . $sign . ' ?', $attender['user_id']));
}
}
if (substr($this->_operator, 0, 3) === 'not') {
// join attendee to be excluded as a new column. records having this column NULL don't have the attendee
$dname = 'attendee-not-' . Tinebase_Record_Abstract::generateUID(5);
$_select->joinLeft(array($dname => $_backend->getTablePrefix() . 'cal_attendee'), $adapter->quoteIdentifier($dname . '.cal_event_id') . ' = ' . $adapter->quoteIdentifier($_backend->getTableName() . '.id') . ' AND ' . $gs->getSQL(), array($dname => $_backend->getDbCommand()->getAggregate($dname . '.id')));
$_select->having($_backend->getDbCommand()->getAggregate($dname . '.id') . ' IS NULL');
} else {
if ($isExcept) {
$dname = 'attendee-hasSome-' . Tinebase_Record_Abstract::generateUID(5);
$_select->joinLeft(array($dname => $_backend->getTablePrefix() . 'cal_attendee'), $adapter->quoteIdentifier($dname . '.cal_event_id') . ' = ' . $adapter->quoteIdentifier($_backend->getTableName() . '.id') . ' AND ' . $gs->getSQL(), array($dname => $_backend->getDbCommand()->getAggregate($dname . '.id')));
$_select->having($_backend->getDbCommand()->getAggregate($dname . '.id') . ' IS NOT NULL');
} else {
$gs->appendWhere(Zend_Db_Select::SQL_OR);
}
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
if ($this->_value === 'all') {
$_select->where('1=1');
return;
}
$gs = new Tinebase_Backend_Sql_Filter_GroupSelect($_select);
$adapter = $_backend->getAdapter();
//if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . ' (' . __LINE__ . ') value: ' . print_r($this->_value, true));
foreach ($this->_value as $attenderValue) {
if (in_array($attenderValue['user_type'], array(Calendar_Model_Attender::USERTYPE_USER, Calendar_Model_Attender::USERTYPE_GROUPMEMBER))) {
// @todo user_id might contain filter in the future -> get userids from adressbook controller with contact filter
// transform CURRENTCONTACT
$attenderValue['user_id'] = $attenderValue['user_id'] == Addressbook_Model_Contact::CURRENTCONTACT ? Tinebase_Core::getUser()->contact_id : $attenderValue['user_id'];
$attendee = array(array('user_type' => Calendar_Model_Attender::USERTYPE_USER, 'user_id' => $attenderValue['user_id']), array('user_type' => Calendar_Model_Attender::USERTYPE_GROUPMEMBER, 'user_id' => $attenderValue['user_id']));
} else {
if ($attenderValue['user_type'] == self::USERTYPE_MEMBEROF) {
// resolve group members
$group = Tinebase_Group::getInstance()->getGroupById($attenderValue['user_id']);
$attendee = array();
// fetch list only if list_id is not NULL, otherwise we get back an empty list object
if (!empty($group->list_id)) {
$contactList = Addressbook_Controller_List::getInstance()->get($group->list_id);
foreach ($contactList->members as $member) {
$attendee[] = array('user_type' => Calendar_Model_Attender::USERTYPE_USER, 'user_id' => $member);
$attendee[] = array('user_type' => Calendar_Model_Attender::USERTYPE_GROUPMEMBER, 'user_id' => $member);
}
}
} else {
$attendee = array($attenderValue);
}
}
foreach ($attendee as $attender) {
$gs->orWhere($adapter->quoteInto($adapter->quoteIdentifier('attendee.user_type') . ' = ?', $attender['user_type']) . ' AND ' . $adapter->quoteInto($adapter->quoteIdentifier('attendee.user_id') . ' = ?', $attender['user_id']));
}
}
$gs->appendWhere(Zend_Db_Select::SQL_OR);
}
/**
* gets implicit exceptions due to status settings
*
* @param array $_egwEventAttendee
* @return Tinebase_Record_RecordSet of Calendar_Model_Event
*/
protected function _getRecurImplicitExceptions($_egwEventData)
{
$implictExceptions = new Tinebase_Record_RecordSet('Calendar_Model_Event');
if (empty($_egwEventData['attendee'])) {
return $implictExceptions;
}
$select = $this->_egwDb->select()->from(array('attendee' => 'egw_cal_user'), 'DISTINCT(' . $this->_egwDb->quoteIdentifier('attendee.cal_recur_date') . ')')->where($this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('cal_id') . ' = ?', $_egwEventData['attendee'][0]['cal_id']))->where($this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('cal_recur_date') . ' != ?', 0));
$groupSelect = new Tinebase_Backend_Sql_Filter_GroupSelect($select);
foreach ($_egwEventData['attendee'] as $attender) {
$groupSelect->orWhere($this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('attendee.cal_user_type') . ' = ?', $attender['cal_user_type']) . ' AND ' . $this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('attendee.cal_user_id') . ' = ?', $attender['cal_user_id']) . ' AND ' . $this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('attendee.cal_status') . ' NOT LIKE ?', $attender['cal_status']));
}
$groupSelect->appendWhere(Zend_Db_Select::SQL_AND);
$egwExceptionDates = $this->_egwDb->fetchAll($select, NULL, Zend_Db::FETCH_ASSOC);
if (count($egwExceptionDates) > 0) {
$this->_log->debug(__METHOD__ . '::' . __LINE__ . ' found ' . count($egwExceptionDates) . ' implicit exceptions for event ' . $_egwEventData['attendee'][0]['cal_id']);
//print_r($_egwEventAttendee);
}
if (count($egwExceptionDates) > 500) {
$this->_log->err(__METHOD__ . '::' . __LINE__ . " egw's horizont for event " . $_egwEventData['attendee'][0]['cal_id'] . " seems to be broken. Status exceptions will not be considered/migrated");
return $implictExceptions;
}
//print_r($egwExceptionDates);
$eventDuration = $_egwEventData['cal_end'] - $_egwEventData['cal_start'];
foreach ($egwExceptionDates as $exdate) {
$select = $this->_egwDb->select()->from(array('attendee' => 'egw_cal_user'))->where($this->_egwDb->quoteInto($this->_egwDb->quoteIdentifier('cal_recur_date') . ' = ?', $exdate['cal_recur_date']));
$egwExceptionEventAttendee = $this->_egwDb->fetchAll($select, NULL, Zend_Db::FETCH_ASSOC);
$exEventData = $_egwEventData;
$exEventData['cal_id'] = Calendar_Model_Event::generateUID();
$exEventData['cal_start'] = $exdate['cal_recur_date'];
$exEventData['cal_end'] = $exdate['cal_recur_date'] + $eventDuration;
$exEventData['attendee'] = $egwExceptionEventAttendee;
$event = $this->_getTineEventRecord($exEventData);
$event->attendee = $this->_getEventAttendee($exEventData);
$implictExceptions->addRecord($event);
}
return $implictExceptions;
}
/**
* appends container_acl sql
*
* @param Zend_Db_Select $_select
* @param String $_accountId
* @param Array|String $_grant
* @param String $_aclTableName
* @param bool $_andGrants
* @return void
*/
public static function addGrantsSql($_select, $_accountId, $_grant, $_aclTableName = 'container_acl', $_andGrants = FALSE, $joinCallBack = NULL)
{
$accountId = $_accountId instanceof Tinebase_Record_Abstract ? $_accountId->getId() : $_accountId;
$db = $_select->getAdapter();
$grants = is_array($_grant) ? $_grant : array($_grant);
$groupMemberships = Tinebase_Group::getInstance()->getGroupMemberships($accountId);
$quotedActId = $db->quoteIdentifier("{$_aclTableName}.account_id");
$quotedActType = $db->quoteIdentifier("{$_aclTableName}.account_type");
$accountSelect = new Tinebase_Backend_Sql_Filter_GroupSelect($_select);
$accountSelect->orWhere("{$quotedActId} = ? AND {$quotedActType} = " . $db->quote(Tinebase_Acl_Rights::ACCOUNT_TYPE_USER), $accountId)->orWhere("{$quotedActId} IN (?) AND {$quotedActType} = " . $db->quote(Tinebase_Acl_Rights::ACCOUNT_TYPE_GROUP), empty($groupMemberships) ? ' ' : $groupMemberships);
if (!Tinebase_Config::getInstance()->get(Tinebase_Config::ANYONE_ACCOUNT_DISABLED)) {
$accountSelect->orWhere("{$quotedActType} = ?", Tinebase_Acl_Rights::ACCOUNT_TYPE_ANYONE);
}
$accountSelect->appendWhere(Zend_Db_Select::SQL_AND);
// we only need to filter, if the filter does not contain %
if (!in_array('*', $grants)) {
// @todo fetch wildcard from specific db adapter
$grants = str_replace('*', '%', $grants);
$quotedGrant = $db->quoteIdentifier($_aclTableName . '.account_grant');
$iteration = 0;
$grantsSelect = new Tinebase_Backend_Sql_Filter_GroupSelect($_select);
foreach ($grants as $grant) {
if ($_andGrants) {
if ($iteration > 0) {
$callbackIdentifier = call_user_func($joinCallBack, $_select, $iteration);
$grantsSelect->where($db->quoteIdentifier($callbackIdentifier . '.account_grant') . ' LIKE ?', $grant);
} else {
$grantsSelect->where($quotedGrant . ' LIKE ?', $grant);
}
++$iteration;
} else {
$grantsSelect->orWhere($quotedGrant . ' LIKE ?', $grant);
}
}
// admin grant includes all other grants
if (!in_array(Tinebase_Model_Grants::GRANT_ADMIN, $grants)) {
$grantsSelect->orWhere($quotedGrant . ' LIKE ?', Tinebase_Model_Grants::GRANT_ADMIN);
}
$grantsSelect->appendWhere(Zend_Db_Select::SQL_AND);
}
}
/**
* appends container_acl sql
*
* @param Zend_Db_Select $_select
* @param String $_accountId
* @param Array|String $_grant
* @param String $_aclTableName
* @return void
*/
public static function addGrantsSql($_select, $_accountId, $_grant, $_aclTableName = 'container_acl')
{
$db = $_select->getAdapter();
$grants = is_array($_grant) ? $_grant : array($_grant);
// admin grant includes all other grants
if (!in_array(Tinebase_Model_Grants::GRANT_ADMIN, $grants)) {
$grants[] = Tinebase_Model_Grants::GRANT_ADMIN;
}
// @todo fetch wildcard from specific db adapter
$grants = str_replace('*', '%', $grants);
if (empty($grants)) {
$_select->where('1=0');
return;
}
// @todo add groupmembers via join
$groupMemberships = Tinebase_Group::getInstance()->getGroupMemberships($_accountId);
$quotedActId = $db->quoteIdentifier("{$_aclTableName}.account_id");
$quotedActType = $db->quoteIdentifier("{$_aclTableName}.account_type");
$quotedGrant = $db->quoteIdentifier("{$_aclTableName}.account_grant");
$accountSelect = new Tinebase_Backend_Sql_Filter_GroupSelect($_select);
$accountSelect->orWhere("{$quotedActId} = ? AND {$quotedActType} = " . $db->quote(Tinebase_Acl_Rights::ACCOUNT_TYPE_USER), $_accountId)->orWhere("{$quotedActId} IN (?) AND {$quotedActType} = " . $db->quote(Tinebase_Acl_Rights::ACCOUNT_TYPE_GROUP), empty($groupMemberships) ? ' ' : $groupMemberships)->orWhere("{$quotedActType} = ?", Tinebase_Acl_Rights::ACCOUNT_TYPE_ANYONE);
$grantsSelect = new Tinebase_Backend_Sql_Filter_GroupSelect($_select);
foreach ($grants as $grant) {
$grantsSelect->orWhere("{$quotedGrant} LIKE ?", $grant);
}
$grantsSelect->appendWhere(Zend_Db_Select::SQL_AND);
$accountSelect->appendWhere(Zend_Db_Select::SQL_AND);
}
