Location: PHPKode > projects > phlyMail Lite > phlymail/handlers/calendar/driver.mysql.php
<?php
/**
 * Database driver for the calendar handler
 * Provides storage functions for use with a mySQL database
 *
 * @package phlyMail Nahariya 4.0+
 * @subpackage Handler Calendar
 * @author  Matthias Sommerfeld
 * @copyright 2004-2010 phlyLabs, Berlin http://phlylabs.de
 * @version 4.3.2 2010-08-16
 */

class calendar_driver
{
    protected $error;
    protected $queryType = 'default'; // @see $this->setQueryType()

    // Constructor
    // Read the config and open the DB
    public function __construct($uid = 0)
    {
        $this->uid = intval($uid);
        $this->DB = &$GLOBALS['DB'];
        $this->DB->Tbl['cal_event'] = '`'.$this->DB->DB['database'].'`.`'.$this->DB->DB['prefix'].'_calendar_events`';
        $this->DB->Tbl['cal_task']  = '`'.$this->DB->DB['database'].'`.`'.$this->DB->DB['prefix'].'_calendar_tasks`';
        $this->DB->Tbl['cal_holiday'] = '`'.$this->DB->DB['database'].'`.`'.$this->DB->DB['prefix'].'_calendar_holidays`';
        $this->DB->Tbl['cal_group'] = '`'.$this->DB->DB['database'].'`.`'.$this->DB->DB['prefix'].'_calendar_groups`';
        $this->DB->Tbl['cal_attach'] = '`'.$this->DB->DB['database'].'`.`'.$this->DB->DB['prefix'].'_calendar_event_attachments`';
        $this->DB->Tbl['cal_attendee'] = '`'.$this->DB->DB['database'].'`.`'.$this->DB->DB['prefix'].'_calendar_event_attendees`';
        $this->DB->Tbl['cal_reminder'] = '`'.$this->DB->DB['database'].'`.`'.$this->DB->DB['prefix'].'_calendar_event_reminders`';
        $this->DB->Tbl['cal_repetition'] = '`'.$this->DB->DB['database'].'`.`'.$this->DB->DB['prefix'].'_calendar_event_repetitions`';
        $this->DB->DB['ServerVersionString'] = $this->DB->serverinfo();
        $this->DB->DB['ServerVersionNum'] = preg_replace('![^0-9\.]!', '', $this->DB->DB['ServerVersionString']);
        return true;
    }

    public function affected()
    {
        return $this->DB->affected();
    }

    public function setQueryType($type)
    {
        if (!in_array($type, array('default', 'sync', 'root'), true)) {
            $this->set_error('Illegal query type');
            return false;
        }
        $this->queryType = $type;
        return true;
    }

    private function set_error($error)
    {
        if (isset($this->append_errors) && $this->append_errors) {
            $this->error[] = $error;
        } else {
            $this->error[0] = $error;
        }
    }

    public function get_errors($nl = "\n")
    {
        $error = implode($nl, $this->error);
        if (!isset($this->retain_errors) || !$this->retain_errors) {
            $this->error = array();
        }
        return $error;
    }

    /**
     * Install the required table(s) for the handler
     * @param  void
     * @return  boolean  return value of the MySQL query
     * @since 0.1.3
     */
    public function handler_install()
    {
        return true; // Handler always there
    }

    /**
     * Uninstall the required table(s) of the handler
     * @param  void
     * @return  boolean  return value of the MySQL query
     * @since 0.1.3
     */
    public function handler_uninstall()
    {
    	return true; // Handler always there
    }

    /**
     * Return a list of event types
     *
     * @return array
     */
    public function get_event_types()
    {
        return array(0 => 'miscellaneous', 1 => 'appointment', 2 => 'holiday'
                ,3 => 'birthday', 4 => 'personal', 5 => 'education'
                ,6 => 'travel', 7 => 'anniversary', 8 => 'not in office'
                ,9 => 'sick day', 10 => 'meeting', 11 => 'vacation'
                ,12 => 'phone call', 13 => 'business'
                ,14 => 'non-working hours', 50 => 'special occasion'
                );
    }

    /**
     * Return a list of event status types
     *
     * @return array
     */
    public function get_event_status()
    {
        return array(0 => 'undefined'//, 1 => 'due for approval'
                ,2 => 'confirmed', 3 => 'cancelled'//, 4 => 'delegated'
                ,10 => 'tentative', 11 => 'needs-action'
                );
    }

    public function get_task_status()
    {
        return array(0 => 'undefined'//, 1 => 'due for approval'
                ,2 => 'confirmed', 3 => 'cancelled'//, 4 => 'delegated'
                ,5 => 'in-process', 6 => 'completed'
                ,10 => 'tentative', 11 => 'needs-action'
                );
    }

    /**
     * Return a list of task importances
     *
     * @return array
     */
    public function get_task_importance()
    {
        return array(0 => 'undefined', 1 => 'A1 / very high', 2 => 'A2'
                ,3 => 'A3 / high', 4 => 'B1', 5 => 'B2 / normal'
                ,6 => 'B3', 7 => 'C1 / low', 8 => 'C2', 9 => 'C3 / very low'
                );
    }

    /**
     * Shorthand for optionally including the event list filter depending on query type property
     * @param int $gid
     * @return string
     */
    protected function getQueryTypeFilter($gid = 0, $pref = 'e')
    {
        $pref = $this->DB->escape($pref);
        if ($gid == 0 && $this->queryType != 'default') {
            $field = $this->queryType == 'sync' ? 'not_in_sync' : 'not_in_root';
            return array
                    (' LEFT JOIN '.$this->DB->Tbl['user_foldersettings'].' uf ON '.$pref.'.gid!=0'
                            .' AND '.$pref.'.gid=uf.fid AND uf.uid='.$this->uid.' AND uf.`handler`="calendar" AND uf.`key`="'.$field.'"'
                    ,' AND ('.$pref.'.gid=0 OR uf.`val`="0" OR uf.`val` IS NULL)'
                    );
        }
        return array('', '');
    }

    /**
     * Takes a reminder ID and returns the item associated with it.
     * Right now this can be either an event or a task.
     *
     * @param int $rem  ID of the reminder
     * @return false|array  ID and type of the found item; FALSE on fialre
     */
    public function get_item_by_reminder($rem)
    {
        $qid = $this->DB->query('SELECT `eid`,`ref` FROM '.$this->DB->Tbl['cal_reminder'].' WHERE id='.intval($rem));
        list ($id, $ref) = $this->DB->fetchrow($qid);
        if (!$id) return false; // Not found
        return array($id, $ref);
    }

    /**
     * Returns true, if a given date has events, false, if not
     * @param string|array $date MySQL date(s); Pass a string with a given date, an array for a date range
     *[@param int  $gid  ID of the group the events shall be in]
     * @return boolean TRUE if events are scheduled for that date, FALSE if not
     * @since 0.0.1
     */
    public function date_has_events($date, $gid = 0)
    {
        if (is_array($date)) {
            $from = $this->DB->escape($date[0]);
            $to = $this->DB->escape($date[1]);
        } else {
            $date = $from = $to = $this->DB->escape($date);
        }
        // Support for filtering out events from groups not included in result set according to query type
        $eventListFilter = $this->getQueryTypeFilter($gid);

        $query = 'SELECT 1 FROM '.$this->DB->Tbl['cal_repetition'].' rp, '.$this->DB->Tbl['cal_event'].' e'
                .$eventListFilter[0]
                .' WHERE rp.`eid`=e.`id` AND rp.`ref`="evt" AND e.`uid`='.$this->uid.($gid ? ' AND e.gid='.intval($gid) : '').$eventListFilter[1]
                .' AND IF (rp.`type`!="-", DATE_FORMAT(e.`starts`, "%Y%m%d") <= DATE_FORMAT("'.$date.'", "%Y%m%d"), 1)'
                .' AND IF (rp.`type`!="-" AND rp.`until` IS NOT NULL AND rp.`until` != "0-0-0 0:0:0", rp.`until`>"'.$date.'", 1) AND ('
                // Begins or ends today
                .'DATE_FORMAT(starts, "%Y%m%d") = DATE_FORMAT("'.$date.'", "%Y%m%d") OR DATE_FORMAT(e.ends, "%Y%m%d") = DATE_FORMAT("'.$date.'", "%Y%m%d") OR '
                // Begins in the past AND ends in the future
                .'(DATE_FORMAT(starts, "%Y%m%d") <= DATE_FORMAT("'.$date.'", "%Y%m%d") AND DATE_FORMAT(e.ends, "%Y%m%d") >= DATE_FORMAT("'.$date.'", "%Y%m%d")) OR '
                // Is an event occuring yearly. Todays date matches the repetition date
                .'(rp.`type`="year" AND (DATE_FORMAT(e.starts, "%m%d")=DATE_FORMAT("'.$date.'", "%m%d") OR DATE_FORMAT(e.ends, "%m%d")=DATE_FORMAT("'.$date.'", "%m%d") ) ) OR '
                // A monthly event, repetition day is today
                .'(rp.`type`="month" AND rp.`repeat` = DATE_FORMAT("'.$date.'", "%e") AND (rp.`extra`="" OR FIND_IN_SET(DATE_FORMAT("'.$date.'", "%c"), rp.`extra`)>0) ) OR '
                // Monthly event on e.g. the 31st of month with months shorter than 31 days, this is only supported from MySQL 4.1.1 onward
                .((version_compare($this->DB->DB['ServerVersionString'], '4.1.1', 'ge'))
                        ? '(rp.`type`="month" AND rp.`repeat`=31 AND (rp.`extra`="" OR FIND_IN_SET(DATE_FORMAT("'.$date.'", "%c"), rp.`extra`)>0) AND LAST_DAY("'.$date.'")=DATE_FORMAT("'.$date.'", "%Y-%m-%d") ) OR '
                        : '(rp.`type`="month" AND rp.`repeat`=31 AND (rp.`extra`="" OR FIND_IN_SET(DATE_FORMAT("'.$date.'", "%c"), rp.`extra`)>0) AND "'.date('Ymd', strtotime('+1 month -1 day '.substr($date, 0, 7).'-01')).'"=DATE_FORMAT("'.$date.'", "%Y%m%d") ) OR ' )
                // A weekly event, repetition weekday is today
                .'(rp.`type`="week" AND rp.`repeat`=DATE_FORMAT("'.$date.'", "%w")
                        AND IF(rp.`extra` IN("", "1"), 1, ABS(MOD(DATEDIFF(e.`starts`, "'.$date.'")/7, rp.`extra`))=0) ) OR '
                // A "daily" event, where the bit pattern should match today's weekday
                .'(rp.`type`="day" AND SUBSTRING(LPAD(BIN(rp.`repeat`),8,0), IF(DATE_FORMAT("'.$date.'", "%w")=0,8,DATE_FORMAT("'.$date.'", "%w")+1), 1)=1 )'
                .') LIMIT 1';
        list ($true) = $this->DB->fetchrow($this->DB->query($query));
        return (bool) $true;
    }

    /**
     * Return a list of scheduled evens for a given date
     *
     * Although advertised here, the method indeed does not use an array of dates.
     *
     * @param string|array $date MySQL date(s); Pass a string with a given date, an array for a date range
     *[@param int  $gid  ID of the group the events shall be in]
     * @return array Events with start time, end time, description
     * @since 0.0.1
     */
    public function date_get_eventlist($date, $gid = 0)
    {
        if (is_array($date)) {
            $from = $this->DB->escape($date[0]);
            $to = $this->DB->escape($date[1]);
        } else {
            $date = $from = $to = $this->DB->escape($date);
        }
        // Support for filtering out events from groups not included in result set according to query type
        $eventListFilter = $this->getQueryTypeFilter($gid);

        $return = array();
        $query = 'SELECT DISTINCT e.`id` '
                .', if (rp.`type`!="-", UNIX_TIMESTAMP(CONCAT(DATE_FORMAT("'.$date.'", "%Y-%m-%d"), " ",DATE_FORMAT(e.`starts`, "%T")) ), UNIX_TIMESTAMP(e.`starts`) ) as start'
                .', if (rp.`type`!="-", UNIX_TIMESTAMP(CONCAT(DATE_FORMAT("'.$date.'", "%Y-%m-%d"), " ",DATE_FORMAT(e.`ends`, "%T")) ), UNIX_TIMESTAMP(e.`ends`) ) as end'
                .',e.`starts`, e.`ends`, e.`location`, e.`title`, e.`description`, e.`type`, e.`status`, e.`opaque`, rp.`type` `repeat_type`, rp.`until` `repeat_until`'
                .((version_compare($this->DB->DB['ServerVersionString'], '4.1.1', 'ge'))
                        ? ', (SELECT `mode` FROM '.$this->DB->Tbl['cal_reminder'].' WHERE `uid`='.$this->uid.' AND `eid`=e.`id` AND `ref`="evt" LIMIT 1) `warn_mode`'
                        : ', "?" `warn_mode`')
                .' FROM '.$this->DB->Tbl['cal_repetition'].' rp, '.$this->DB->Tbl['cal_event'].' e'
                .$eventListFilter[0]
                .' WHERE rp.`eid`=e.`id` AND rp.`ref`="evt" AND e.`uid`='.$this->uid.($gid ? ' AND e.gid='.intval($gid) : '').$eventListFilter[1]
                .' AND IF (rp.`type`!="-", DATE_FORMAT(e.`starts`, "%Y%m%d") <= DATE_FORMAT("'.$from.'", "%Y%m%d"), 1)'
                .' AND IF (rp.`type`!="-" AND rp.`until` IS NOT NULL AND rp.`until` != "0-0-0 0:0:0", rp.`until`>"'.$to.'",1) AND ('
                // Begins or ends today
                .'DATE_FORMAT(e.`starts`, "%Y%m%d")=DATE_FORMAT("'.$date.'", "%Y%m%d") OR DATE_FORMAT(e.`ends`, "%Y%m%d")=DATE_FORMAT("'.$date.'", "%Y%m%d") OR '
                // Begins in the past AND ends in the future
                .'( DATE_FORMAT(e.`starts`, "%Y%m%d")<=DATE_FORMAT("'.$date.'", "%Y%m%d") AND DATE_FORMAT(e.`ends`, "%Y%m%d")>=DATE_FORMAT("'.$date.'", "%Y%m%d") ) OR '
                // Is an event occuring yearly. Todays date matches the repetition date
                .'(rp.`type`="year" AND (DATE_FORMAT(e.`starts`,"%m%d")=DATE_FORMAT("'.$date.'", "%m%d") OR DATE_FORMAT(e.`ends`,"%m%d")=DATE_FORMAT("'.$date.'","%m%d"))) OR '
                // A monthly event, repetition day is today, repetition month is empty or matches
                .'(rp.`type`="month" AND rp.`repeat`=DATE_FORMAT("'.$date.'", "%e") AND (rp.`extra`="" OR FIND_IN_SET(DATE_FORMAT("'.$date.'", "%c"), rp.`extra`)>0) ) OR '
                // Monthly event on e.g. the 31st of month with months shorter than 31 days, this is only supported from MySQL 4.1.1 onward
                .((version_compare($this->DB->DB['ServerVersionString'], '4.1.1', 'ge'))
                        ? '(rp.`type`="month" AND rp.`repeat`=31 AND (rp.`extra`="" OR FIND_IN_SET(DATE_FORMAT("'.$date.'", "%c"), rp.`extra`)>0) AND LAST_DAY("'.$date.'")=DATE_FORMAT("'.$date.'", "%Y-%m-%d"))'
                        : '(rp.`type`="month" AND rp.`repeat`=31 AND (rp.`extra`="" OR FIND_IN_SET(DATE_FORMAT("'.$date.'", "%c"), rp.`extra`)>0) AND "'.date('Ymd', strtotime('+1 month -1 day '.substr($date, 0, 7).'-01')).'"=DATE_FORMAT("'.$date.'", "%Y%m%d"))')
                // A weekly event, repetition weekday is today
                .' OR (rp.`type`="week" AND rp.`repeat`=DATE_FORMAT("'.$date.'", "%w")
                        AND IF(rp.`extra` IN("", "1"), 1, ABS(MOD(DATEDIFF(e.`starts`, "'.$date.'")/7, rp.`extra`))=0) ) OR '
                // A "daily" event, where the bit pattern should match today's weekday
                .'(rp.`type`="day" AND SUBSTRING(LPAD(BIN(rp.`repeat`), 8, 0), IF(DATE_FORMAT("'.$date.'", "%w")=0, 8, DATE_FORMAT("'.$date.'", "%w")+1), 1)=1 )'
                .') ORDER BY `start` ASC';
        $qh = $this->DB->query($query);
        while ($line = $this->DB->fetchassoc($qh)) {
            if ($line['warn_mode'] == '?') {
                $qid2 = $this->DB->query('SELECT `mode` FROM '.$this->DB->Tbl['cal_reminder']
                        .' WHERE `uid`='.$this->uid.' AND `eid`='.$line['id'].' AND `ref`="evt" AND `mode` != "-" LIMIT 1');
                list ($rem) = $this->DB->fetchrow($qid2);
                $line['warn_mode'] = $rem ? $rem : '-';
            } elseif ($line['warn_mode'] == '' || is_null($line['warn_mode'])) {
                $line['warn_mode'] = '-';
            }
            $return[] = $line;
        }
        return $return;
    }

    /**
     * Gives the unix timestamp of the next date with an event based on the start timestamp passed
     * @param  int  UNIX timestamp as starting offset
     * @return  int  UNIX timestamp of the next date which has an event defined
     * @since 0.1.1
     */
    public function get_nextday_withevents($basedate, $gid = 0)
    {
        $maxcount = 365; // Prevents too high load on DB
        $mydate = $basedate;
        while ($maxcount) {
            --$maxcount;
            $basedate = strtotime('+1 day', $basedate);
            if ($this->date_has_events(date('Y-m-d', $basedate), $gid)) return $basedate;
        }
        return $mydate;
    }

    /**
     * Gives the unix timestamp of the previous date with an event based on the start timestamp passed
     * @param  int  UNIX timestamp as starting offset
     * @return  int  UNIX timestamp of the previous date which has an event defined
     * @since 0.1.1
     */
    public function get_prevday_withevents($basedate, $gid = 0)
    {
        $maxcount = 365; // Prevents too high load on DB
        $mydate = $basedate;
        while ($maxcount) {
            --$maxcount;
            $basedate = strtotime('-1 day', $basedate);
            if ($this->date_has_events(date('Y-m-d', $basedate), $gid)) return $basedate;
        }
        return $mydate;
    }

    /**
     * Retrieve detailed data about an event
     * @param int ID of the event
     *[@param int ID of a reminder, which identifies an event]
     * @return array Specific data, false on failure / non-existant ID
     * @since 0.0.3
     */
    public function get_event($id, $reminder = null)
    {
        if (!is_null($reminder)) {
            $qid = $this->DB->query('SELECT `eid` FROM '.$this->DB->Tbl['cal_reminder'].' WHERE `ref`="evt" AND id='.intval($reminder));
            list($id) = $this->DB->fetchrow($qid);
            if (!$id) return array();
        }
        $id = intval($id);
        $query = 'SELECT `id` `eid`,UNIX_TIMESTAMP(starts) `start`, UNIX_TIMESTAMP(ends) `end`, `starts`, `ends`'
                 .',`title`,`description`,`location`,`type`,`status`,`opaque`,`gid`,`uuid`'
                 .' FROM '.$this->DB->Tbl['cal_event'].' WHERE uid='.$this->uid.' AND id='.$id;
        $event = $this->DB->fetchassoc($this->DB->query($query));
        $event['reminders'] = array();
        $qid = $this->DB->query('SELECT `id`,`time`,`mode`,`text`,`mailto`,`smsto` FROM '.$this->DB->Tbl['cal_reminder']
                .' WHERE `uid`='.$this->uid.' AND `eid`='.$id.' AND `ref`="evt" ORDER BY `mode` DESC, `time` DESC');
        while ($line = $this->DB->fetchassoc($qid)) {
        	$event['reminders'][] = $line;
        	if (!is_null($reminder) && $line['id'] == $reminder) {
        	    $event['reminder_text'] = $line['text'];
        	}
        }
        $event['repetitions'] = array();
        $qid = $this->DB->query('SELECT `id`,`type`,`repeat`,`extra`,`until`,IF (`until` IS NOT NULL, unix_timestamp(`until`), 0) `until_unix`'
                .' FROM '.$this->DB->Tbl['cal_repetition'].' WHERE `eid`='.$id.' AND `ref`="evt" ORDER BY `id` ASC');
        while ($line = $this->DB->fetchassoc($qid)) {
        	$event['repetitions'][] = $line;
        }
        return $event;
    }

    /**
     * Enter description here...
     *
     *[@param int $gid GID to filter against, 0 for all groups]
     *[@param string $pattern Search pattern to search events for; Default: null]
     * @return int
     * @since 4.2.2
     */
    public function get_eventcount($gid = 0, $pattern = null)
    {
        // Support for filtering out events from groups not included in result set according to query type
        $eventListFilter = $this->getQueryTypeFilter($gid);

        $query = 'SELECT COUNT(*) anzahl FROM '.$this->DB->Tbl['cal_event'].' e'.$eventListFilter[0].' WHERE e.uid='.$this->uid.$eventListFilter[1];
        if ($gid != 0) $query .= ' AND e.gid='.intval($gid);
        // Do we have a search pattern set?
        if ($pattern) {
            $pattern = $this->DB->escape($pattern);
            $pattern = (strstr($pattern, '*')) ? str_replace('*', '%', $pattern) : '%'.$pattern.'%';
            // Flatten the field list
            $v = array();
            foreach (array('title', 'location', 'description') as $k) { $v[] = 'e.`'.$k.'` LIKE "'.$pattern.'"'; }
            $query .=' AND ('.implode(' OR ', $v).')';
        }
        file_put_contents('handlers/calendar/moep', $query);
        $qid = $this->DB->query($query);
        try {
            $line = $this->DB->fetchassoc($qid);
            return ($line['anzahl']) ? $line['anzahl'] : 0;
        } catch (Exception $e) {
            return 0;
        }
    }

    /**
     * Retrieves a list of all events a given user has defined
     *
     *[@param  int  $gid  GID to filter against, 0 for all groups]
     *[@param  bool  $digest   TRUE to only return flags for having repetition / reminder; Default: false]
     *[@param  string  $pattern  Search pattern to search events for; Default: null]
     *[@param  integer $num  Number of entries to return]
     *[@param  integer $start  Start entry]
     *[@param  string  $orderby  Field to order by; Default: starts]
     *[@param  string  ASC|DESC  Order direction; Default: ASC]
     * @return  array
     * @since  4.0.3
     */
    public function get_eventlist($gid = 0, $digest = false, $pattern = null, $num = 0, $start = 0, $orderby = 'starts', $orderdir = 'ASC')
    {
        // Support for filtering out events from groups not included in result set according to query type
        $eventListFilter = $this->getQueryTypeFilter($gid);
        $return = array();
        $query = 'SELECT e.`id`,UNIX_TIMESTAMP(e.starts) `start`,UNIX_TIMESTAMP(e.ends) `end`,e.`starts`,e.`ends`'
                 .',e.`title`,e.`description`,e.`location`,e.`type`,e.`status`,e.`opaque`,e.`gid`,e.`uuid`'
                 .($digest ? ',COUNT(rep.id) `repetitions`,COUNT(rem.id) `reminders`,COUNT(rem2.id) `reminders_sms`,COUNT(rem3.id) `reminders_email`' : '')
                 .' FROM '.$this->DB->Tbl['cal_event'].' e'.$eventListFilter[0]
                 .($digest ? ' LEFT JOIN '.$this->DB->Tbl['cal_repetition'].' rep ON rep.`type`!="-" AND rep.`eid`=e.`id` AND rep.`ref`="evt"' : '')
                 .($digest ? ' LEFT JOIN '.$this->DB->Tbl['cal_reminder'].' rem ON rem.`eid`=e.`id` AND rem.`ref`="evt"' : '')
                 .($digest ? ' LEFT JOIN '.$this->DB->Tbl['cal_reminder'].' rem2 ON rem2.`eid`=e.`id` AND rem2.`ref`="evt" AND rem2.`smsto`!=""' : '')
                 .($digest ? ' LEFT JOIN '.$this->DB->Tbl['cal_reminder'].' rem3 ON rem3.`eid`=e.`id` AND rem3.`ref`="evt" AND rem3.`mailto`!=""' : '')
                 .' WHERE e.uid='.$this->uid.$eventListFilter[1];
        if ($gid > 0) $query .= ' AND e.gid='.intval($gid);

        // Do we have a search pattern set?
        if ($pattern == '@@upcoming@@') { // Special filter for upcoming events (pinboard)
            $query .= ' AND (`starts`>=NOW() OR (`starts`<=NOW() AND `ends`>=NOW()))';
        } elseif ($pattern) {
            $pattern = $this->DB->escape($pattern);
            $pattern = (strstr($pattern, '*')) ? str_replace('*', '%', $pattern) : '%'.$pattern.'%';
            // Flatten the field list
            $v = array();
            foreach (array('title', 'location', 'description') as $k) { $v[] = 'e.`'.$k.'` LIKE "'.$pattern.'"'; }
            $query .=' AND ('.implode(' OR ', $v).')';
        }
        if ($digest) {
            $query .= ' GROUP BY e.`id`';
        }
        $query .= ' ORDER BY `'.$this->DB->escape($orderby).'` '.($orderdir == 'ASC' ? 'ASC' : 'DESC');
        if ($num > 0) {
            $query .= ' LIMIT '.intval($start).', '.intval($num);
        }
        $qid = $this->DB->query($query);
        while ($line = $this->DB->fetchassoc($qid)) {
            if (!$digest) {
                $line['reminders'] = array();
                $qid2 = $this->DB->query('SELECT `id`,`time`,`mode`,`text`,`mailto`,`smsto` FROM '.$this->DB->Tbl['cal_reminder']
                        .' WHERE `uid`='.$this->uid.' AND `eid`='.$line['id'].' AND `ref`="evt" ORDER BY `mode` DESC, `time` DESC');
                while ($line2 = $this->DB->fetchassoc($qid2)) { $line['reminders'][] = $line2; }
                $line['repetitions'] = array();
                $qid2 = $this->DB->query('SELECT `id`,`type`,`repeat`,`extra`,IF (`until` IS NOT NULL, unix_timestamp(`until`), 0) `until_unix`'
                        .' FROM '.$this->DB->Tbl['cal_repetition'].' WHERE `eid`='.$line['id'].' AND `ref`="evt" ORDER BY `id` ASC');
                while ($line2 = $this->DB->fetchassoc($qid2)) { $line['repetitions'][] = $line2; }

                $line['attendees'] = $this->get_event_attendees($line['id']);
            }
            $return[$line['id']] = $line;
        }
        return $return;
    }

    /**
     * Add an event to the database
     * @param array Specification for that event
     * @return boolean TRUE on success, FALSE otherwise
     * @since 0.0.2
     */
    public function add_event($data)
    {
        $datafields = array
               ('start' => array('req' => true)
               ,'end' => array('req' => true)
               ,'gid' => array('req' => true)
               ,'title' => array('req' => false, 'def' => '')
               ,'description' => array('req' => false, 'def' => '')
               ,'location' => array('req' => false, 'def' => '')
               ,'type' => array('req' => false, 'def' => 0)
               ,'status' => array('req' => false, 'def' => 0)
               ,'opaque' => array('req' => false, 'def' => 1)
               );
        foreach ($datafields as $k => $v) {
            if (!isset($data[$k])) {
                if ($v['req'] === true) return false;
                $data[$k] = $v['def'];
            } else {
                $data[$k] = $this->DB->escape($data[$k]);
            }
        }
        $query = 'INSERT '.$this->DB->Tbl['cal_event']
                .' (`uid`,`gid`,`starts`,`ends`,`title`,`description`,`location`,`type`,`status`,`opaque`,`uuid`) VALUES ('
                .$this->uid.', "'.$data['gid'].'" ,"'.$data['start'].'","'.$data['end'].'","'.$data['title'].'","'.$data['description'].'"'
                .',"'.$data['location'].'",'.intval($data['type']).','.intval($data['status']).',"'.intval($data['opaque']).'", "'.basics::uuid().'")';
        if (!$this->DB->query($query)) {
            return false;
        }
        $newId = $this->DB->insertid();
        // Make sure, the end of an event is NOT before its beginning
        $this->DB->query('UPDATE '.$this->DB->Tbl['cal_event'].' SET `ends`=`starts` WHERE `ends`<`starts` AND id='.$newId);

        if (isset($data['attendees']) && !empty($data['attendees'])) {
            $query = 'INSERT INTO '.$this->DB->Tbl['cal_attendee'].' (`eid`,`ref`,`name`,`email`,`role`,`type`,`status`) VALUES ';
            $k = 0;
            foreach ($data['attendess'] as $v) {
                if ($k) $query .= ',';
                $query .= '('.intval($newId).',"evt","'.$this->DB->escape($v['name']).'""'.$this->DB->escape($v['email']).'"'
                        .',"'.$this->DB->escape($v['role']).'","'.$this->DB->escape($v['type']).'",'.intval($v['status']).')';
                $k++;
            }
            $this->DB->query($query);
        }

        if (isset($data['reminders']) && !empty($data['reminders'])) {
            $query = 'INSERT INTO '.$this->DB->Tbl['cal_reminder'].' (`eid`,`ref`,`uid`,`mode`,`time`,`text`,`smsto`,`mailto`) VALUES ';
            $k = 0;
            foreach ($data['reminders'] as $v) {
                if ($k) $query .= ',';
                if ($v['mode'] == '-') continue;
                $query .= '('.intval($newId).',"evt",'.$this->uid.',"'.$this->DB->escape($v['mode']).'",'.intval($v['time'])
                        .',"'.$this->DB->escape($v['text']).'","'.$this->DB->escape($v['smsto']).'","'.$this->DB->escape($v['mailto']).'")';
                $k++;
            }
            $this->DB->query($query);
        }
        if (isset($data['repetitions']) && !empty($data['repetitions'])) {
            $query = 'INSERT INTO '.$this->DB->Tbl['cal_repetition'].' (`eid`,`ref`,`type`,`repeat`,`extra`,`until`) VALUES ';
            $k = 0;
            foreach ($data['repetitions'] as $v) {
                if ($k) $query .= ',';
                $query .= '('.intval($newId).',"evt","'.$this->DB->escape($v['type']).'",'.intval($v['repeat'])
                        .','.(!is_null($v['extra']) ? '"'.$this->DB->escape($v['extra']).'"' : '""')
                        .','.(!is_null($v['until']) ? '"'.$this->DB->escape($v['until']).'"' : 'NULL').')';
                $k++;
            }
            $this->DB->query($query);
        } else {
            $query = 'INSERT INTO '.$this->DB->Tbl['cal_repetition'].' (`eid`,`ref`,`type`,`repeat`,`extra`,`until`) VALUES '
                    .'('.intval($newId).',"evt","-",0,"",NULL)';
            $this->DB->query($query);
        }
        return $newId;
    }

    /**
     * Update an event in the database
     * @param array Specification for that event
     * @return boolean TRUE on success, FALSE otherwise
     * @since 0.0.4
     */
    public function update_event($data)
    {
        if (!isset($data['id']) || !$data['id']) return false;
        $query = 'UPDATE '.$this->DB->Tbl['cal_event'].' SET  lastmod=NOW()';
        $datafields = array('start' => 'starts', 'end' => 'ends', 'title' => 'title'
                ,'description' => 'description', 'location' => 'location'
                ,'type' => 'type', 'status' => 'status', 'opaque' => 'opaque', 'gid' => 'gid');
        foreach ($datafields as $k => $v) {
            if (!isset($data[$k])) continue;
            $query .= ',`'.$v.'`="'.$this->DB->escape($data[$k]).'"';
        }
        $query .= ' WHERE uid='.$this->uid.' AND id='.$data['id'];
        $this->DB->query($query);

        $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_reminder'].' WHERE `uid`='.$this->uid.' AND `ref`="evt" AND `eid`='.$data['id']);
        $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_repetition'].' WHERE `eid`='.$data['id']);
        if (isset($data['reminders']) && !empty($data['reminders'])) {
            $query = 'INSERT INTO '.$this->DB->Tbl['cal_reminder'].' (`eid`,`ref`,`uid`,`mode`,`time`,`text`,`smsto`,`mailto`) VALUES ';
            $k = 0;
            foreach ($data['reminders'] as $v) {
                if ($k) $query .= ',';
                if ($v['mode'] == '-') continue;
                $query .= '('.intval($data['id']).',"evt",'.$this->uid.',"'.$this->DB->escape($v['mode']).'"'
                        .','.intval($v['time']).',"'.$this->DB->escape($v['text']).'"'
                        .',"'.$this->DB->escape($v['smsto']).'","'.$this->DB->escape($v['mailto']).'")';
                $k++;
            }
            $this->DB->query($query);
        }
        if (isset($data['repetitions']) && !empty($data['repetitions'])) {
            $query = 'INSERT INTO '.$this->DB->Tbl['cal_repetition'].' (`eid`,`ref`,`type`,`repeat`,`extra`,`until`) VALUES ';
            $k = 0;
            foreach ($data['repetitions'] as $v) {
                if ($k) $query .= ',';
                $query .= '('.intval($data['id']).',"evt","'.$this->DB->escape($v['type']).'",'.intval($v['repeat'])
                        .','.(!is_null($v['extra']) ? '"'.$this->DB->escape($v['extra']).'"' : '""')
                        .','.(!is_null($v['until']) ? '"'.$this->DB->escape($v['until']).'"' : 'NULL').')';
                $k++;
            }
            $this->DB->query($query);
        } else {
            $query = 'INSERT INTO '.$this->DB->Tbl['cal_repetition'].' (`eid`,`ref`,`type`,`repeat`,`extra`,`until`) VALUES '
                    .'('.intval($data['id']).',"evt","-",0,"",NULL)';
            $this->DB->query($query);
        }
        return true;
    }

    /**
     * Delete an event from the database
     * @param int ID of the event
     * @return boolean TRUE on success, FALSE otherwise
     * @since 0.0.6
     */
    public function delete_event($id)
    {
        return ($this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_event'].' WHERE uid='.$this->uid.' AND id='.intval($id))
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_reminder'].' WHERE `ref`="evt" AND `eid`='.intval($id))
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_repetition'].' WHERE `ref`="evt" AND `eid`='.intval($id))
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_attendee'].' WHERE `ref`="evt" AND `eid`='.intval($id))
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_attach'].' WHERE `ref`="evt" AND `eid`='.intval($id))
                && $this->DB->query('ALTER TABLE '.$this->DB->Tbl['cal_event'].' ORDER BY id')
                && $this->DB->query('ALTER TABLE '.$this->DB->Tbl['cal_reminder'].' ORDER BY id')
                && $this->DB->query('ALTER TABLE '.$this->DB->Tbl['cal_repetition'].' ORDER BY id')
                && $this->DB->query('ALTER TABLE '.$this->DB->Tbl['cal_attendee'].' ORDER BY id')
                && $this->DB->query('ALTER TABLE '.$this->DB->Tbl['cal_attach'].' ORDER BY id'));
    }

    /**
     * Return a list of events, where the alert time is overdue or will be reached within the next n minutes
     * @param  int Number of minutes to look into the future
     *[@param  bool  Query for all users' events; Default false]
     *[@param  bool  Only return events, where external alerts are set; Default false]
     * @return array  keys: event IDs, values: UNIX timestamp of alarm time; if onlyexternal is true, the format changes:
     *  keys: event IDs, values: array('warntime' => timestamp, 'mailto' => string, 'smsto' => string)
     * @since 0.0.9
     */
    public function get_alertable_events($min = 5, $allusers = false, $onlyexternal = false)
    {
        $date = date('Y-m-d');
    	$return = array();
    	$userfilter = ($allusers != false) ? '' : ' AND e.uid='.$this->uid;
    	$alertfilter = ($onlyexternal) ? ' AND (rm.`mailto` != "" OR rm.`smsto` != "")' : '';

    	$query = 'SELECT DISTINCT e.`id`,e.`uuid`, rm.`id` `reminder_id`,rm.`text` `reminder`,rm.mailto,rm.smsto, e.uid, e.title, e.description, e.location'
                .', IF (rp.`type`!="-", UNIX_TIMESTAMP(CONCAT(DATE_FORMAT(NOW(), "%Y-%m-%d"), " ",DATE_FORMAT(e.starts, "%T")) ), UNIX_TIMESTAMP(e.starts) ) `start`'
                .', IF (rp.`type`!="-", UNIX_TIMESTAMP(CONCAT(DATE_FORMAT(NOW(), "%Y-%m-%d"), " ",DATE_FORMAT(e.ends, "%T")) ), UNIX_TIMESTAMP(e.ends) ) `end`'
    	        .', IF (UNIX_TIMESTAMP(rm.snooze) != 0, UNIX_TIMESTAMP(rm.snooze), IF (rm.mode="s", UNIX_TIMESTAMP(DATE_SUB(CONCAT(DATE_FORMAT(NOW(), "%Y-%m-%d"), " ",DATE_FORMAT(e.starts, "%T")), INTERVAL rm.time SECOND)), UNIX_TIMESTAMP(DATE_SUB(CONCAT(DATE_FORMAT(NOW(), "%Y-%m-%d"), " ",DATE_FORMAT(e.ends, "%T")), INTERVAL rm.`time` SECOND)))) `warntime`'
    	        .' FROM '.$this->DB->Tbl['cal_event'].' e,'.$this->DB->Tbl['cal_reminder'].' rm,'.$this->DB->Tbl['cal_repetition'].' rp'
    	        .' WHERE e.id=rm.eid AND rm.`ref`="evt" AND e.id=rp.eid AND rp.`ref`="evt" AND rm.mode!="-"'.$userfilter.$alertfilter
    	        // Don't alert cancelled events
    	        .' AND `e`.`status` != 3'
    	        // Nonrepeated events get selected when they were not alerted yet or their warn_snooze is later than now
    	        .' AND IF(rp.`type`="-" AND rm.lastinfo!=0 AND rm.`snooze` < NOW(), 0, 1)'
    	        .' AND IF (rp.`type`!="-" AND rp.`until` IS NOT NULL AND rp.`until` != "0-0-0 0:0:0", rp.`until`>NOW(),1) AND ('
    	        .'IF (rm.`mode`="s"'
    	        .', e.starts > NOW() AND rm.lastinfo != e.starts AND DATE_SUB(e.starts, INTERVAL rm.`time`+'.($min * 60).' SECOND) < NOW()'
    	        .', e.ends > NOW() AND rm.lastinfo != e.ends AND DATE_SUB(e.ends, INTERVAL rm.`time`+'.($min * 60).' SECOND) < NOW()'
    	        .') OR '
    	        // A rescheduled alert
    	        .'IF(UNIX_TIMESTAMP(rm.`snooze`) > 0 AND UNIX_TIMESTAMP(rm.`snooze`)-'.($min * 60).' < UNIX_TIMESTAMP(NOW()), 1, 0) OR '
                // Is an event occuring yearly.
                .'(rp.`type`="year" AND DATE_FORMAT(rm.`lastinfo`, "%Y") != DATE_FORMAT(NOW(), "%Y") AND if (rm.`mode`="s"'
                .',CONCAT(DATE_FORMAT(NOW(),"%Y"), "-", DATE_FORMAT(e.`starts`,"%m-%d %T")) > NOW() AND DATE_SUB(CONCAT(DATE_FORMAT(NOW(),"%Y"), "-", DATE_FORMAT(e.`starts`,"%m-%d %T")), INTERVAL (rm.`time`+'.($min * 60).') SECOND) < NOW()'
                .',CONCAT(DATE_FORMAT(NOW(),"%Y"), "-", DATE_FORMAT(e.`starts`,"%m-%d %T")) > NOW() AND DATE_SUB(CONCAT(DATE_FORMAT(NOW(),"%Y"), "-", DATE_FORMAT(e.`ends`,"%m-%d %T")), INTERVAL (rm.`time`+'.($min * 60).') SECOND) < NOW() ) ) OR '
                // A monthly event
                .'(rp.`type`="month" AND DATE_FORMAT(rm.`lastinfo`, "%Y%m") != DATE_FORMAT(NOW(), "%Y%m") AND (rp.`extra`="" OR FIND_IN_SET(DATE_FORMAT(NOW(), "%c"), rp.`extra`)>0) AND IF (rm.`mode`="s"'
                .',CONCAT(DATE_FORMAT(NOW(),"%Y-%m"), "-", rp.`repeat`, " ", DATE_FORMAT(e.`starts`, "%T")) > NOW() AND DATE_SUB(CONCAT(DATE_FORMAT(NOW(),"%Y-%m"), "-", rp.`repeat`, " ", DATE_FORMAT(e.`starts`, "%T")), INTERVAL (rm.`time`+'.($min * 60).') SECOND) < NOW()'
                .',CONCAT(DATE_FORMAT(NOW(),"%Y-%m"), "-", rp.`repeat`, " ", DATE_FORMAT(e.`ends`, "%T"))> NOW() AND DATE_SUB(CONCAT(DATE_FORMAT(NOW(),"%Y-%m"), "-", rp.`repeat`, " ", DATE_FORMAT(e.`ends`, "%T")), INTERVAL (rm.`time`+'.($min * 60).') SECOND) < NOW() ) ) OR '
                // Monthly event on e.g. the 31st of month with months shorter than 31 days, will only work with alerts set for the same day
                .((date('Ymd', strtotime('+1 month -1 day '.substr($date, 0, 7).'-01')) == $date)
                        ? '(rp.`type`="month" AND rp.`repeat`=31 AND (rp.`extra`="" OR FIND_IN_SET(DATE_FORMAT("'.$date.'", "%c"), rp.`extra`)>0) AND IF (rm.`mode`="s"'
                                .', DATE_FORMAT(e.`starts`, "%d%H%i%s") > DATE_FORMAT(NOW(), "%d%H%i%s") AND DATE_FORMAT(rm.`lastinfo`, "%Y%m") != DATE_FORMAT(NOW(), "%Y%m") AND DATE_FORMAT(UNIX_TIMESTAMP(e.`starts`) - (rm.`time` + '.($min * 60).'), "%H%i%s") < DATE_FORMAT(NOW(), "%H%i%s")'
                                .', DATE_FORMAT(e.`ends`, "%d%H%i%s") > DATE_FORMAT(NOW(), "%d%H%i%s") AND DATE_FORMAT(rm.`lastinfo`, "%Y%m") != DATE_FORMAT(NOW(), "%Y%m") AND DATE_FORMAT(UNIX_TIMESTAMP(e.`ends`) - (rm.`time` + '.($min * 60).'), "%H%i%s") < DATE_FORMAT(NOW(), "%H%i%s") ) ) OR '
                        : ''
                 )
                // A weekly event
                .'(rp.`type`="week" AND DATE_FORMAT(rm.`lastinfo`, "%Y%m%d") < DATE_FORMAT(NOW(), "%Y%m%d") AND IF (rm.`mode`="s"'
                .', DATE_FORMAT(e.`starts`, "%w") > DATE_FORMAT(NOW(), "%w") AND DATE_FORMAT(UNIX_TIMESTAMP(e.`starts`) - (rm.`time` + '.($min * 60).'), "%w%H%i%s") < DATE_FORMAT(NOW(), "%w%H%i%s")
                        AND IF(rp.`extra` IN("", "1"), 1, ABS(MOD(DATEDIFF(e.`starts`, NOW())/7, rp.`extra`))=0)'
                .', DATE_FORMAT(e.`ends`, "%w") > DATE_FORMAT(NOW(), "%w") AND DATE_FORMAT(UNIX_TIMESTAMP(e.`ends`) - (rm.`time` + '.($min * 60).'), "%w%H%i%s") < DATE_FORMAT(NOW(), "%w%H%i%s")
                        AND IF(rp.`extra` IN("", "1"), 1, ABS(MOD(DATEDIFF(e.`ends`, NOW())/7, rp.`extra`))=0) ) ) OR '
                // A "daily" event, where the bit pattern should match today's weekday
                .'(rp.`type`="day" AND if (rm.`mode`="s"'
                .',SUBSTRING(LPAD(BIN(rp.`repeat`), 8, 0), IF(DATE_FORMAT(UNIX_TIMESTAMP(NOW()) + rm.`time` + '.($min * 60).', "%w")=0, 8, DATE_FORMAT(UNIX_TIMESTAMP(NOW()) + rm.`time` + '.($min * 60).', "%w")), 1) = 1 AND DATE_FORMAT(UNIX_TIMESTAMP(e.`starts`) - (rm.`time` + '.($min * 60).'), "%H%i%s") < DATE_FORMAT(NOW(), "%H%i%s")'
                .',SUBSTRING(LPAD(BIN(rp.`repeat`), 8, 0), IF(DATE_FORMAT(UNIX_TIMESTAMP(NOW()) + rm.`time` + '.($min * 60).', "%w")=0, 8,  DATE_FORMAT(UNIX_TIMESTAMP(NOW()) + rm.`time` + '.($min * 60).', "%w")), 1) = 1 AND DATE_FORMAT(UNIX_TIMESTAMP(e.`ends`) - (rm.`time` + '.($min * 60).'), "%H%i%s") < DATE_FORMAT(NOW(), "%H%i%s") ) )'
    	        .') ORDER BY `warntime` ASC';
    	if (function_exists('vecho')) vecho($query);
    	$qid = $this->DB->query($query);
    	echo $this->DB->error();
    	while ($line = $this->DB->fetchassoc($qid)) {
    		$return[$line['id']] = array
    		      ('warn_time' => $line['warntime'], 'mailto' => $line['mailto']
    		      ,'smsto' => $line['smsto'], 'uid' => $line['uid']
    		      ,'title' => $line['title'], 'description' => $line['description']
    		      ,'location' => $line['location'], 'starts' => $line['start'], 'ends' => $line['end']
    		      ,'reminder' => $line['reminder'], 'reminder_id' => $line['reminder_id']
    		      );
    	}
    	return $return;
    }

    /**
     * Switches off a scheduled warn_time of given event
     * @param int ID of the event reminder (WARNING: API break between 3.6 and 3.7)
     * @return bool  MySQL return value of the SQL statement
     * @since 0.1.0
     */
    public function discard_event_alert($rid)
    {
    	$query = 'UPDATE '.$this->DB->Tbl['cal_reminder'].' SET `lastinfo`=NOW(), `snooze`=NULL WHERE `id`='.intval($rid);
    	$return = $this->DB->query($query);
    	if (!$return) $this->set_error($this->DB->error());
    	return $return;
    }

    /**
     * Reschedules the alert for a given event, by default 5mins into the future
     * @param  int  ID of the event reminder to reschedule
     * @param  int  Number of seconds to set the delay to (starting with NOW())
     * @return  bool  MySQL return value of the SQL statement issued
     * @since 0.1.0
     */
    public function repeat_event_alert($rid, $timespan = 300)
    {
        if (!$timespan) $timespan = 300;
    	$query = 'UPDATE '.$this->DB->Tbl['cal_reminder'].' SET `lastinfo`=NULL,`snooze`=(UNIX_TIMESTAMP(NOW())+ '.intval($timespan).')'
    	       .' WHERE `id`='.intval($rid);
    	$return = $this->DB->query($query);
    	if (!$return) $this->set_error($this->DB->error());
    	return $return;
    }

    /**
     * Removes all old events from database. Optionally a minimum age of the events can be given
     *[@param  int  Minimum age in minutes of events considered to be old, default is 0]
     * @return boolean  MySQL return value of the performed SQL query
     * @since 0.1.2
     */
    public function killoldevents($span = 0)
    {
    	$query = 'DELETE FROM '.$this->DB->Tbl['cal_event'].' e, '.$this->DB->Tbl['cal_reminder'].' rm'
    	       .', '.$this->DB->Tbl['cal_repetition'].' rp'
    	       .' WHERE e.uid='.$this->uid.' AND rm.`eid`=e.`id` AND rm.`ref`="evt" AND rp.`eid`=e.`id` AND rp.`ref`="evt"'
    	       .' AND (rp.`type`!="-" OR (rp.`type`!="-" AND rp.`until` IS NOT NULL AND rp.`until`<NOW())) AND ';
    	$query .= ($span)
    			? 'DATE_FORMAT(DATE_ADD(e.ends, INTERVAL '.intval($span).' MINUTE), "%Y%m%d") < DATE_FORMAT(NOW(), "%Y%m%d")'
    			: 'DATE_FORMAT(e.ends, "%Y%m%d") < DATE_FORMAT(NOW(), "%Y%m%d")';
    	$return = $this->DB->query($query);
    	return $return;
    }

    /**
     * Adds an attende to an event. Generates the hash for invitation mails, too.
     *
     * @param int $eid The event to attach the attendee to
     * @param string $name  Firstname, might be empty
     * @param string $email   Email address, might be empty
     *[@param int $status  Attendance status; Default: 0]
     *[@param string $role  One of chair, req, opt, non; Default: opt]
     *[@param string $type  One of person, group, resource, room, unknown; Default: person]
     *[@param string evt|tsk|jou  Specify, what kind of entity the given $eid refers to (event, task, yournal); Default: evt]
     * @return array(int, string) The ID of the new entry and the unique hash to identify the attendee
     */
    public function add_event_attendee($eid, $name, $email, $status = 0, $role = 'opt', $type = 'person', $ref = 'evt')
    {
        if (!$eid) return false; // We NEED the event's ID
        $hash = basics::uuid();
        $query = 'INSERT INTO '.$this->DB->Tbl['cal_attendee'].' SET '
                .'`eid`='.intval($eid).',`name`="'.$this->DB->escape($name).'",`ref`="'.$this->DB->escape($ref).'"'
                .',`email`="'.$this->DB->escape($email).'",`role`="'.$this->DB->escape($role).'"'
                .',`type`="'.$this->DB->escape($type).'",`status`="'.$this->DB->escape($status).'"'
                .',`mailhash`="'.$this->DB->escape($hash).'"';
        if ($this->DB->query($query)) {
            $newId = $this->DB->insertid();
            return array($newId, $hash);
        }
        return false;
    }

    /**
     * Update event attendee's data
     *
     * @param int $id  ID of the attendee
     *[@param string $name  Name of the attendee]
     *[@param string $email  Email address of the attendee]
     *[@param int $status  Attendance status]
     *[@param string $role  One of chair, req, opt, non]
     *[@param string $type  One of person, group, resource, room, unknown]
     * @return bool
     */
    public function update_event_attendee($id, $name = null, $email = null, $status = 0, $role = null, $type = null)
    {
        if (is_null($name) && is_null($email)) return true; // Nothing to do
        $query = 'UPDATE '.$this->DB->Tbl['cal_attendee'].' SET eid=eid'
                .(!is_null($name) ? ',`name`="'.$this->DB->escape($name).'"' : '')
                .(!is_null($email) ? ',`email`="'.$this->DB->escape($email).'"' : '')
                .(!is_null($status) ? ',`status`="'.$this->DB->escape($status).'"' : '')
                .(!is_null($role) ? ',`role`="'.$this->DB->escape($role).'"' : '')
                .(!is_null($type) ? ',`type`="'.$this->DB->escape($type).'"' : '')
                .' WHERE `id`='.intval($id);
        return $this->DB->query($query);
    }

    /**
     * Delete an event's attendee. For your convenience a registered XNA for
     * the given attendee is unregistered, too.
     *
     * @param int $id  ID of the attendee
     * @return bool
     */
    public function delete_event_attendee($id)
    {
        $attendee = $this->get_event_attendees(null, $id, null);
        if (empty($attendee)) return true;
        $this->DB->xna_unregister($attendee['mailhash']);
        return $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_attendee'].' WHERE `id`='.intval($id));
    }

    /**
     * Get event attendee list
     *
     *[@param int $eid  event ID]
     *[@param int $aid  attendee ID]
     *[@param string $hash  attendee mail hash]
     *[@param evt|tsk|jou  Specify, what kind of entity the $eid refers to; Default: evt]
     * @return array
     */
    public function get_event_attendees($eid = null, $aid = null, $hash = null, $ref = 'evt')
    {
        if (is_null($eid) && is_null($aid) && is_null($hash)) return false;

        $return = array();
        $q_r = '`eid`='.intval($eid).' AND `ref`="'.$this->DB->escape($ref).'"';
        $order = ' ORDER BY `name` ASC';
        if (!is_null($hash)) {
            $q_r = '`mailhash`="'.$this->DB->escape($hash).'"';
            $order = '';
        } elseif (!is_null($aid)) {
            $q_r = '`id`='.intval($aid);
            $order = '';
        }
        $query = 'SELECT `id`,`eid`,`ref`,`name`,`email`,`role`,`type`,`mailhash`,`invited`,`rsvp`,`status`'
                .' FROM '.$this->DB->Tbl['cal_attendee'].' WHERE '.$q_r.$order;
        $qid = $this->DB->query($query);
        if ($order != '') {
            while ($line = $this->DB->fetchassoc($qid)) $return[] = $line;
        } else {
            $return = $this->DB->fetchassoc($qid);
        }
        return $return;
    }

    /**
     * Marks an event attendee as invited
     *
     * @param string $hash  The mail hash of the attendee (supposed to be unique)
     * @return bool
     */
    public function set_event_attendee_invited($hash)
    {
        return $this->DB->query('UPDATE '.$this->DB->Tbl['cal_attendee'].' SET `invited`=NOW() WHERE `mailhash`="'.$this->DB->escape($hash).'"');
    }

    /**
     * Marks the RSVP (invitation reply) status of an event attendee
     *
     * @param string $hash  The mail hash of the attendee
     * @param int $status  1 = accepted, 2 = denied, 3 = maybe
     * @return bool
     */
    public function set_event_attendee_rsvp($hash, $status)
    {
        $query = 'UPDATE '.$this->DB->Tbl['cal_attendee'].' SET `rsvp`=NOW(), `status`='.intval($status)
                .' WHERE `mailhash`="'.$this->DB->escape($hash).'"';
        return $this->DB->query($query);
    }


    public function get_task_count($gid = 0, $pattern = '', $criteria = '')
    {
        // Support for filtering out events from groups not included in result set according to query type
        $eventListFilter = $this->getQueryTypeFilter($gid, 't');

        $q_l = 'SELECT COUNT(*) FROM '.$this->DB->Tbl['cal_task'].' t'.$eventListFilter[0].' WHERE t.uid='.$this->uid.$eventListFilter[1];

        if ($gid) $q_l .= ' AND t.`gid`='.intval($gid);
        // Do we have a search criteria and a pattern set?
        if ($criteria && $pattern) {
            $pattern = $this->DB->escape($pattern);
            $pattern = (strstr($pattern, '*')) ? str_replace('*', '%', $pattern) : '%'.$pattern.'%';
            if (isset($this->criteria_list[$criteria])) {
                // Flatten the field list
                foreach ($this->criteria_list[$criteria] as $k) $v[] = 't.'.$k.' LIKE "'.$pattern.'"';
                $q_l.=' AND ('.implode(' OR ', $v).')';
            }
        }
        list ($count) = $this->DB->fetchrow($this->DB->query($q_l));
        return $count;
    }

    public function get_tasklist($gid = 0, $pattern = '', $criteria = '', $num = 0, $start = 0, $order_by = false, $order_dir = 'ASC')
    {
        $return = array();
        // Support for filtering out events from groups not included in result set according to query type
        $eventListFilter = $this->getQueryTypeFilter($gid, 't');
        $q_r = '';
        $q_l = 'SELECT t.`id`,t.`gid`,t.`title`,t.`location`,t.`description`,t.`type`,t.`status`,t.`importance`,t.`completion`,t.`uuid`, t.`starts`, t.`ends`'
                .', IF(t.`starts` IS NULL, NULL, UNIX_TIMESTAMP(t.`starts`)) `start`, IF(t.`ends` IS NULL, NULL, UNIX_TIMESTAMP(t.`ends`)) `end`'
                .((version_compare($this->DB->DB['ServerVersionString'], '4.1.1', 'ge'))
                        ? ', (SELECT `mode` FROM '.$this->DB->Tbl['cal_reminder'].' WHERE `uid`='.$this->uid.' AND `eid`=t.`id` AND `ref`="tsk" LIMIT 1) `warn_mode`'
                        : ', "?" `warn_mode`')
                .' FROM '.$this->DB->Tbl['cal_task'].' t'.$eventListFilter[0].' WHERE t.uid='.$this->uid.$eventListFilter[1];
        if ($gid) $q_l .= ' AND t.`gid`='.intval($gid);
        $order_dir = ('ASC' == $order_dir) ? 'ASC' : 'DESC';
        // Order by / direction given?
        $orderstring = ($order_by) ? 't.`'.$this->DB->escape($order_by).'` '.$order_dir : 't.`completion` DESC';
        // Do we have a search criteria and a pattern set?
        if ($pattern == '@@upcoming@@') { // Special filter for upcoming events (pinboard)
            $q_l .= ' AND (t.`starts` IS NULL OR t.`ends` IS NULL OR t.`starts`>=NOW() OR (t.`starts`<=NOW() AND t.`ends`>=NOW()))';
        } elseif ($criteria && $pattern) {
            $pattern = $this->DB->escape($pattern);
            $pattern = (strstr($pattern, '*')) ? str_replace('*', '%', $pattern) : '%'.$pattern.'%';
            if (isset($this->criteria_list[$criteria])) {
                // Flatten the field list
                foreach ($this->criteria_list[$criteria] as $k) $v[] = 't.'.$k.' LIKE "'.$pattern.'"';
                $q_l .= ' AND ('.implode(' OR ', $v).')';
            }
        }
        if ($num != 0) $q_r .= ' LIMIT '.($start).','.($num);
        $qid = $this->DB->query($q_l . ' ORDER BY ' . $orderstring.$q_r);
        while ($line = $this->DB->fetchassoc($qid)) {
            if ($line['warn_mode'] == '?') {
                $qid2 = $this->DB->query('SELECT `mode` FROM '.$this->DB->Tbl['cal_reminder']
                        .' WHERE `uid`='.$this->uid.' AND `eid`='.$line['id'].' AND `ref`="tsk" AND `mode` IS NOT NULL LIMIT 1');
                list ($rem) = $this->DB->fetchrow($qid2);
                $line['warn_mode'] = $rem ? $rem : '-';
            } elseif ($line['warn_mode'] == '' || is_null($line['warn_mode'])) {
                $line['warn_mode'] = '-';
            }
            $return[] = $line;
        }
        return $return;
    }

    /**
     * Retrieve detailed data about an task
     * @param int ID of the task
     * @return array Specific data, false on failure / non-existant ID
     * @since 0.0.3
     */
    public function get_task($id, $reminder = null)
    {
        if (!is_null($reminder)) {
            $qid = $this->DB->query('SELECT `eid` FROM '.$this->DB->Tbl['cal_reminder'].' WHERE `ref`="tsk" AND id='.intval($reminder));
            list($id) = $this->DB->fetchrow($qid);
            if (!$id) return array();
        }
        $id = intval($id);
        $query = 'SELECT `gid`,`title`,`location`,`description`,`type`,`status`,`importance`,`completion`,`uuid`'
                .', IF(`starts` IS NULL, NULL, UNIX_TIMESTAMP(`starts`)) as `start`, IF(`ends` IS NULL, NULL, UNIX_TIMESTAMP(`ends`)) as `end`'
                 .' FROM '.$this->DB->Tbl['cal_task'].' WHERE uid='.$this->uid.' AND id='.intval($id);
        $task = $this->DB->fetchassoc($this->DB->query($query));
        $task['reminders'] = array();
        $qid = $this->DB->query('SELECT `id`,`time`,`mode`,`text`,`mailto`,`smsto` FROM '.$this->DB->Tbl['cal_reminder']
                .' WHERE `uid`='.$this->uid.' AND `eid`='.$id.' AND `ref`="tsk" ORDER BY `mode` DESC, `time` DESC');
        while ($line = $this->DB->fetchassoc($qid)) {
        	$task['reminders'][] = $line;
        	if (!is_null($reminder) && $line['id'] == $reminder) {
        	    $task['reminder_text'] = $line['text'];
        	}
        }
        return $task;
    }

    /**
     * Add an task to the database
     * @param array Specification for that task
     * @return boolean TRUE on success, FALSE otherwise
     * @since 0.0.2
     */
    public function add_task($data)
    {
        $datafields = array
               ('start' => array('req' => false, 'def' => 'NULL')
               ,'end' => array('req' => false, 'def' => 'NULL')
               ,'gid' => array('req' => true)
               ,'title' => array('req' => false, 'def' => '')
               ,'location' => array('req' => false, 'def' => '')
               ,'description' => array('req' => false, 'def' => '')
               ,'importance' => array('req' => false, 'def' => '1')
               ,'completion' => array('req' => false, 'def' => '0')
               ,'type' => array('req' => false, 'def' => '0')
               ,'status' => array('req' => false, 'def' => '0')
               );
        foreach ($datafields as $k => $v) {
            if (!isset($data[$k])) {
                if ($v['req'] === true) return false;
                $data[$k] = $v['def'];
            } else {
                $data[$k] = $this->DB->escape($data[$k]);
            }
        }
        $query = 'INSERT '.$this->DB->Tbl['cal_task'].' SET `uid`='.$this->uid.',`gid`='.$data['gid']
                .',`starts`='.($data['start'] == 'NULL' ? 'NULL' : '"'.$data['start'].'"')
                .',`ends`='.($data['end'] == 'NULL' ? 'NULL' : '"'.$data['end'].'"')
                .',`title`="'.$data['title'].'",`location`="'.$data['location'].'"'
                .',`description`="'.$data['description'].'",`uuid`="'.basics::uuid().'"'
                .',`importance`='.intval($data['importance']).',`completion`='.intval($data['completion'])
                .',`type`='.intval($data['type']).',`status`='.intval($data['status']);
        if (!$this->DB->query($query)) {
            return false;
        }
        $newId = $this->DB->insertid();
        // Make sure, the end of an event is NOT before its beginning
        $this->DB->query('UPDATE '.$this->DB->Tbl['cal_event'].' SET `ends`=`starts` WHERE `ends`<`starts` AND id='.$newId);

        if (isset($data['reminders']) && !empty($data['reminders'])) {
            $query = 'INSERT INTO '.$this->DB->Tbl['cal_reminder'].' (`eid`,`ref`,`uid`,`mode`,`time`,`text`,`smsto`,`mailto`) VALUES ';
            $k = 0;
            foreach ($data['reminders'] as $v) {
                if ($k) $query .= ',';
                if ($v['mode'] == '-') continue;
                $query .= '('.intval($newId).',"tsk",'.$this->uid.',"'.$this->DB->escape($v['mode']).'",'.intval($v['time'])
                        .',"'.$this->DB->escape($v['text']).'","'.$this->DB->escape($v['smsto']).'","'.$this->DB->escape($v['mailto']).'")';
                $k++;
            }
            $this->DB->query($query);
        }
        return $newId;
    }

    /**
     * Update an task in the database
     * @param array Specification for that task
     * @return boolean TRUE on success, FALSE otherwise
     * @since 0.0.4
     */
    public function update_task($data)
    {
        if (!isset($data['id']) || !$data['id']) return false;
        $query = 'UPDATE '.$this->DB->Tbl['cal_task'].' SET lastmod=NOW()';
        foreach (array('start' => 'starts', 'end' => 'ends', 'title' => 'title', 'location' => 'location'
               ,'description' => 'description', 'importance' => 'importance', 'gid' => 'gid'
               ,'completion' => 'completion', 'type' => 'type', 'status' => 'status'
               ) as $k => $v) {
            if (!isset($data[$k])) continue;
            $query .= ',`'.$v.'`='.(('NULL' == $data[$k] || is_null($data[$k])) ? 'NULL' : '"'.$this->DB->escape($data[$k]).'"');
        }
        $this->DB->query($query.' WHERE uid='.$this->uid.' AND id='.$data['id']);
        // Reminders set
        $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_reminder'].' WHERE `uid`='.$this->uid.' AND `ref`="tsk" AND `eid`='.$data['id']);
        if (isset($data['reminders']) && !empty($data['reminders'])) {
            $query = 'INSERT INTO '.$this->DB->Tbl['cal_reminder'].' (`eid`,`ref`,`uid`,`mode`,`time`,`text`,`smsto`,`mailto`) VALUES ';
            $k = 0;
            foreach ($data['reminders'] as $v) {
                if ($k) $query .= ',';
                if ($v['mode'] == '-') continue;
                $query .= '('.intval($data['id']).',"tsk",'.$this->uid.',"'.$this->DB->escape($v['mode']).'"'
                        .','.intval($v['time']).',"'.$this->DB->escape($v['text']).'"'
                        .',"'.$this->DB->escape($v['smsto']).'","'.$this->DB->escape($v['mailto']).'")';
                $k++;
            }
            $this->DB->query($query);
        }
        return true;
    }

    /**
     * Delete an task from the database
     * @param int ID of the task
     * @return boolean TRUE on success, FALSE otherwise
     * @since 0.0.6
     */
    public function delete_task($id)
    {
        return ($this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_task'].' WHERE uid='.$this->uid.' AND id='.intval($id))
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_reminder'].' WHERE `ref`="tsk" AND `eid`='.intval($id))
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_attach'].' WHERE `ref`="tsk" AND id='.intval($id))
                && $this->DB->query('ALTER TABLE '.$this->DB->Tbl['cal_task'].' ORDER BY id')
                && $this->DB->query('ALTER TABLE '.$this->DB->Tbl['cal_reminder'].' ORDER BY id')
                && $this->DB->query('ALTER TABLE '.$this->DB->Tbl['cal_attach'].' ORDER BY id'));
    }

    /**
     * Return a list of events, where the alert time is overdue or will be reached within the next n minutes
     * @param  int Number of minutes to look into the future
     *[@param  bool  Query for all users' events; Default false]
     *[@param  bool  Only return events, where external alerts are set; Default false]
     * @return array  keys: event IDs, values: UNIX timestamp of alarm time; if onlyexternal is true, the format changes:
     *  keys: event IDs, values: array('warntime' => timestamp, 'mailto' => string, 'smsto' => string)
     * @since 0.0.9
     */
    public function get_alertable_tasks($min = 5, $allusers = false, $onlyexternal = false)
    {
        $date = date('Y-m-d');
    	$return = array();
    	$userfilter = ($allusers != false) ? '' : ' AND t.uid='.$this->uid;
    	$alertfilter = ($onlyexternal) ? ' AND (rm.`mailto` != "" OR rm.`smsto` != "")' : '';

    	$query = 'SELECT DISTINCT t.`id`,t.`uuid`, rm.`id` `reminder_id`,rm.`text` `reminder`,rm.mailto,rm.smsto, t.uid, t.title, t.description, t.location'
                .', UNIX_TIMESTAMP(t.starts)`start`, UNIX_TIMESTAMP(t.ends) `end`'
    	        .', IF (UNIX_TIMESTAMP(rm.snooze) != 0, UNIX_TIMESTAMP(rm.snooze), IF (rm.mode="s", UNIX_TIMESTAMP(DATE_SUB(CONCAT(DATE_FORMAT(NOW(), "%Y-%m-%d"), " ",DATE_FORMAT(t.starts, "%T")), INTERVAL rm.time SECOND)), UNIX_TIMESTAMP(DATE_SUB(CONCAT(DATE_FORMAT(NOW(), "%Y-%m-%d"), " ",DATE_FORMAT(t.ends, "%T")), INTERVAL rm.`time` SECOND)))) `warntime`'
    	        .' FROM '.$this->DB->Tbl['cal_task'].' t,'.$this->DB->Tbl['cal_reminder'].' rm'
    	        .' WHERE t.id=rm.eid AND rm.`ref`="tsk" AND rm.mode!="-"'.$userfilter.$alertfilter
    	        .' AND IF(rm.lastinfo!=0 AND rm.`snooze` < NOW(), 0, 1)'
    	        // Don't alert cancelled events
    	        .' AND t.`status` != 3 AND ('
    	        // Nonrepeated events get selected when they were not alerted yet or their warn_snooze is later than now
    	        .'IF (rm.`mode`="s"'
    	        .', t.starts > NOW() AND rm.lastinfo != t.starts AND DATE_SUB(t.starts, INTERVAL rm.`time`+'.($min * 60).' SECOND) < NOW()'
    	        .', t.ends > NOW() AND rm.lastinfo != t.ends AND DATE_SUB(t.ends, INTERVAL rm.`time`+'.($min * 60).' SECOND) < NOW()'
    	        .') OR '
    	        // A rescheduled alert
    	        .'IF(UNIX_TIMESTAMP(rm.`snooze`) > 0 AND UNIX_TIMESTAMP(rm.`snooze`)-'.($min * 60).' < UNIX_TIMESTAMP(NOW()), 1, 0)'
    	        .') ORDER BY `warntime` ASC';
    	if (function_exists('vecho')) vecho($query);
    	$qid = $this->DB->query($query);
    	echo $this->DB->error();
    	while ($line = $this->DB->fetchassoc($qid)) {
    		$return[$line['id']] = array
    		      ('warn_time' => $line['warntime'], 'mailto' => $line['mailto']
    		      ,'smsto' => $line['smsto'], 'uid' => $line['uid']
    		      ,'title' => $line['title'], 'description' => $line['description']
    		      ,'location' => $line['location'], 'starts' => $line['start'], 'ends' => $line['end']
    		      ,'reminder' => $line['reminder'], 'reminder_id' => $line['reminder_id']
    		      );
    	}
    	return $return;
    }

    /**
     * Removes all old tasks from database. Optionally a minimum age of the tasks can be given
     *[@param  int  Minimum age in minutes of tasks considered to be old, default is 0]
     * @return boolean  MySQL return value of the performed SQL query
     * @since 0.1.2
     */
    public function killoldtasks()
    {
        return;
        # FIXME this logic does not seem to be right!
    	return ($this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_attach']
    	           .' USING '.$this->DB->Tbl['cal_attach'].' a,'.$this->DB->Tbl['cal_task'].' t'
    	           .' WHERE t.uid='.$this->uid.' AND t.completion>=100 AND a.tid=t.id AND a.`ref`="tsk"')
    	       && $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_task'].' WHERE uid='.$this->uid.' AND completion>=100'));
    }

    /**
     * Return list of groups associated with a certain user
     * @param integer user id
     * @param boolean with global groups?
     * [@param string pattern
     * [@param integer num
     * [@param integer start]]])
     * @return $return array data on success, FALSE otherwise
     * @since 0.3.4
     */
    public function get_grouplist($inc_global = 0, $pattern = '', $num = 0, $start = 0)
    {
        $return = array();
        $counter = ' LEFT JOIN '.$this->DB->Tbl['cal_event'].' e ON e.gid = g.gid'; // Not in use right now
        $q_r = '';
        $q_l = 'SELECT g.gid, g.name'./*, count(e.id) ecount*/', g.owner FROM '.$this->DB->Tbl['cal_group'].' g';
        $q_l .= ($inc_global) ? ' WHERE g.owner IN('.$this->uid.',0)' : ' WHERE g.owner='.$this->uid;
        if ($num != 0) $q_r .= ' LIMIT ' . intval($start) . ',' . intval($num);
        $qid = $this->DB->query($q_l . ' GROUP BY g.gid ORDER BY g.owner, g.name' . $q_r);
        while ($line = $this->DB->fetchassoc($qid)) {
            $return[] = $line;
        }
        return $return;
    }

    /**
     * Retrieve information about a folder (right now still called groups due to historical reasons)
     *
     * @param int $gid  ID of the folder (group) to get info about
     *[@param bool $nameOnly Set to true, to get the name of the folder as a string; Default: FALSE]
     * @return string|array group name on $nameOnly == TRUE, array data otherwise; FALSE on error
     * @since 0.3.4
     */
    public function get_group($gid = 0, $nameOnly = false)
    {
        if (!$gid) return false;
        $query = 'SELECT '.($nameOnly ? '`name`' : '*').' FROM '.$this->DB->Tbl['cal_group'].' WHERE owner='.$this->uid.' AND gid='.intval($gid);
        $qh = $this->DB->query($query);
        if (false === $qh || !is_object($qh)) return false;
        if ($nameOnly) {
            list ($result) = $this->DB->fetchrow($qh);
            return $result;
        }
        $group = $this->DB->fetchassoc($qh);
        $sync = $this->DB->foldersetting_get('calendar', $gid, $this->uid, 'not_in_sync');
        $root = $this->DB->foldersetting_get('calendar', $gid, $this->uid, 'not_in_root');
        $group['show_in_sync'] = (is_null($sync) || !$sync) ? 1 : 0;
        $group['show_in_root'] = (is_null($root) || !$root) ? 1 : 0;
        return $group;
    }

    /**
     * Insert a group
     * @param string $name  Group name
     *[@param bool  $sync  Include in syncs; Default: TRUE]
     *[@param bool  $root  Show in main calendar; Default: TRUE]
     *[@param int  $type  0 for local, 1 for URI; Default: 0]
     *[@param string $uri  Pass URI for external calendar; Deafult: NULL]
     *[@param string  $mime  MIME type of external calendar; Deafult: NULL]
     *[@param int  $check  Check interval of external calendar in minute; Default: 0]
     * @return TRUE on success, FALSE otherwise
     * @since 0.3.4
     */
    public function add_group($name = '', $sync = 1, $root = 1, $type = 0, $uri = null, $mime = null, $check = 0)
    {
        $name = $this->DB->escape($name);
        $query = 'INSERT '.$this->DB->Tbl['cal_group'].' SET owner='.$this->uid.', name="'.$name.'"';
        if ($type == 1) {
            $query .= ',`type`=1,`checkevery`='.intval($check)
                    .',`uri`="'.(is_null($uri) ? '' : $this->DB->escape($uri)).'"'
                    .',`mime`="'.(is_null($mime) ? '' : $this->DB->escape($mime)).'"';
        } else {
            $query .= ',`type`=0';
        }
        $this->DB->query($query);
        $gid = $this->DB->insertid();
        if ($sync == 0) $this->DB->foldersetting_set('calendar', $gid, $this->uid, 'not_in_sync', 1);
        if ($root == 0) $this->DB->foldersetting_set('calendar', $gid, $this->uid, 'not_in_root', 1);
        return $gid;
    }

    /**
     * Update a given group
     * Input  : adb_update_group(integer owner, integer group id, string group name)
     * @return TRUE on success, FALSE otherwise
     * @since 0.3.4
     */
    public function update_group($gid = 0, $name = null, $sync = null, $root = null, $uri = null, $mime = null, $check = null)
    {
        if (!$gid) return false;
        $gid = (int) $gid;
        if (!is_null($sync)) {
            if ($sync) {
                $this->DB->foldersetting_del('calendar', $gid, $this->uid, 'not_in_sync');
            } else {
                $this->DB->foldersetting_set('calendar', $gid, $this->uid, 'not_in_sync', 1);
            }
        }
        if (!is_null($root)) {
            if ($root) {
                $this->DB->foldersetting_del('calendar', $gid, $this->uid, 'not_in_root');
            } else {
                $this->DB->foldersetting_set('calendar', $gid, $this->uid, 'not_in_root', 1);
            }
        }
        $sqladd = array();
        if (!is_null($name))  $sqladd[] = '`name`="'.$this->DB->escape($name).'"';
        if (!is_null($uri))   $sqladd[] = '`uri`="'.$this->DB->escape($uri).'"';
        if (!is_null($mime))  $sqladd[] = '`mime`="'.$this->DB->escape($mime).'"';
        if (!is_null($check)) $sqladd[] = '`checkevery`='.intval($check);
        if (empty($sqladd)) return true;

        $query = 'UPDATE '.$this->DB->Tbl['cal_group'].' SET '.implode(',', $sqladd).' WHERE gid='.$gid.' AND owner='.$this->uid;
        return $this->DB->query($query);
    }

    /**
     * Check, whether a group name for a ceratin user already exists
     * Input  : adb_checkfor_groupname(integer owner, string groupname)
     * @return group id if yes, FALSE otherwise
     * @since 0.3.4
     */
    public function checkfor_groupname($name = '')
    {
        $query = 'SELECT gid FROM '.$this->DB->Tbl['cal_group'].' WHERE owner='.$this->uid.' AND name="'.$this->DB->escape($name).'"';
        list ($result) = $this->DB->fetchrow($this->DB->query($query));
        return ($result) ? $result : false;
    }

    /**
     * Delete a given group from address book
     * Input  : adb_dele_group(integer group id)
     * @return TRUE on success or FALSE on failure
     * @since 0.3.4
     */
    public function dele_group($gid = 0)
    {
        $query = 'SELECT 1 FROM '.$this->DB->Tbl['cal_group'].' WHERE gid='.intval($gid).' AND owner='.$this->uid.' LIMIT 1';
        list ($result) = $this->DB->fetchrow($this->DB->query($query));
        if (!$result) return false;
        return ($this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_group'].' WHERE gid='.intval($gid))
             && $this->DB->query('ALTER TABLE '.$this->DB->Tbl['cal_group'].' ORDER BY gid'));
    }

    /**
     * Retrieves a list of holidays within a given date range.
     * If neither start nor end are given, all stored holidays are retrieved, which can be
     * useful for exporting. If only start is given, you'll get returned, whether this day
     * is a holiday, and if so the name of it. If both arguments are given, all holidays
     * with their name in this date range are retrieved.
     *
     * The third parameter is only obeyed, when parameters one and two are NOT specified!
     *
     *[@param string $start MySQL date of the first day of the range]
     *[@param string $end MySQL date of the last day of the range]
     *[@param int|array $limit Either an intege for "LIMIT x" or an array for "LIMIT x,y"]
     * @return array dates, which are holidays
     * @since 4.0.9
     * @todo Add support for recurring holidays. These need to be selected in the form mm-dd, but
     *   be returned with the correct year stamped in front. This might easily be done when querying
     *   a single date or a date range less than one year, but when spanning over more than one year ...
     */
    public function daterange_getholidays($start = false, $end = false, $limit = null)
    {
        $return = array();
        $mode = 0;
        if (!$start && !$end) {
            $query = 'SELECT `hid`, `hname`,`hdate` FROM '.$this->DB->Tbl['cal_holiday'].' WHERE `uid` IN (0,'.$this->uid.') ORDER BY `hdate`ASC';
            if (is_array($limit)) {
                $query .= ' LIMIT '.intval($limit[0]).','.intval($limit[1]);
            } elseif (!is_null($limit) && 0 < $limit) {
                $query .= ' LIMIT '.intval($limit);
            }
            $mode = 1;
        } elseif (!$end) {
            $query = 'SELECT `hname`, `hdate` FROM '.$this->DB->Tbl['cal_holiday'].' WHERE `uid` IN (0,'.$this->uid.') AND `hdate`="'.$this->DB->escape($start).'"';
        } else {
            $query = 'SELECT `hname`, `hdate` FROM '.$this->DB->Tbl['cal_holiday']
                    .' WHERE `uid` IN (0,'.$this->uid.') AND `hdate`>="'.$this->DB->escape($start).'" AND `hdate`<="'.$this->DB->escape($end).'"';
        }
        $res = $this->DB->query($query);
        while ($line = $this->DB->fetchassoc($res)) {
            if (1 == $mode) {
                $return[$line['hid']] = array($line['hdate'], $line['hname']);
            } else {
                $return[$line['hdate']] = $line['hname'];
            }
        }
        return $return;
    }

    /**
     * Add a holiday to the database
     *
     * @param string $date MySQL date of the holiday
     * @param string $name Descriptive name of it (may be localized)
     * @param bool $recurring Whether this is a holiday always celebrated at the same date of year
     * @return bool TRUE, if adding succeeded
     * @since 4.0.9
     */
    public function add_holiday($date, $name, $recurring = false, $is_global = false)
    {
        $query = 'INSERT INTO '.$this->DB->Tbl['cal_holiday'].' SET `hname`="'.$this->DB->escape($name).'"'
                .', `hdate`="'.$this->DB->escape($date).'", `recurring`="'.($recurring ? 1 : 0).'"'
                .($is_global === true ? ',uid=0' : ',uid='.intval($this->uid));
        return $this->DB->query($query);
    }

    /**
     * Update a holiday
     *
     * @param int $id  Index of the entry in the database (primary key)
     * @param string $date MySQL date of the holiday
     * @param string $name Descriptive name of it (may be localized)
     * @param bool $recurring Whether this is a holiday always celebrated at the same date of year
     * @return bool TRUE, if adding succeeded
     * @since 4.0.9
     */
    public function update_holiday($id, $date, $name, $recurring)
    {
        $query = 'UPDATE '.$this->DB->Tbl['cal_holiday'].' SET `hname`="'.$this->DB->escape($name).'"'
                .', `hdate`="'.$this->DB->escape($date).'", `recurring`="'.($recurring ? 1 : 0).'"'
                .' WHERE `hid`='.intval($id).' AND uid='.intval($this->uid);
        return $this->DB->query($query);
    }

    public function delete_holiday($id)
    {
        return $this->DB->query('DELETE FROM '.$this->DB->Tbl['cal_holiday'].' WHERE `hid`='.intval($id).' AND uid='.intval($this->uid));
    }

    public function quota_getnumberofrecords($stats = false)
    {
        if (false == $stats) {
            $query = 'SELECT count(*) FROM '.$this->DB->Tbl['cal_event'].' WHERE uid='.$this->uid;
            list ($records) = $this->DB->fetchrow($this->DB->query($query));
            return $records;
        }
        $query = 'SELECT count(distinct uid), count(*) FROM '.$this->DB->Tbl['cal_event'].' WHERE uid>0';
        list ($cnt, $sum) = $this->DB->fetchrow($this->DB->query($query));
        if ($cnt) {
            $query = 'SELECT uid, count(uid) moep FROM '.$this->DB->Tbl['cal_event'].' WHERE uid>0 GROUP BY uid ORDER BY moep DESC LIMIT 1';
            list ($max_uid, $max_cnt) = $this->DB->fetchrow($this->DB->query($query));
        }
        return array
                ('count' => isset($cnt) ? $cnt : 0
                ,'sum' => isset($sum) ? $sum : 0
                ,'max_uid' => isset($max_uid) ? $max_uid : 0
                ,'max_count' => isset($max_cnt) ? $max_cnt : 0
                );
    }

    public function quota_getnumberoftasks($stats = false)
    {
        if (false == $stats) {
            $query = 'SELECT count(*) FROM '.$this->DB->Tbl['cal_task'].' WHERE uid='.$this->uid;
            list ($records) = $this->DB->fetchrow($this->DB->query($query));
            return $records;
        }
        $query = 'SELECT count(distinct uid), count(*) FROM '.$this->DB->Tbl['cal_task'].' WHERE uid>0';
        list ($cnt, $sum) = $this->DB->fetchrow($this->DB->query($query));
        if ($cnt) {
            $query = 'SELECT uid, count(uid) moep FROM '.$this->DB->Tbl['cal_task'].' WHERE uid>0 GROUP BY uid ORDER BY moep DESC LIMIT 1';
            list ($max_uid, $max_cnt) = $this->DB->fetchrow($this->DB->query($query));
        }
        return array
                ('count' => isset($cnt) ? $cnt : 0
                ,'sum' => isset($sum) ? $sum : 0
                ,'max_uid' => isset($max_uid) ? $max_uid : 0
                ,'max_count' => isset($max_cnt) ? $max_cnt : 0
                );
    }

    public function quota_groupsnum($stats = false)
    {
        if (false == $stats) {
            $query = 'SELECT count(*) FROM '.$this->DB->Tbl['cal_group'].' WHERE owner='.intval($this->uid);
            list ($num) = $this->DB->fetchrow($this->DB->query($query));
            return $num;
        }
        $query = 'SELECT count(distinct owner), count(*) FROM '.$this->DB->Tbl['cal_group'].' WHERE owner>0';
        list ($cnt, $sum) = $this->DB->fetchrow($this->DB->query($query));
        if ($cnt) {
            $query = 'SELECT owner, count(owner) moep FROM '.$this->DB->Tbl['cal_group'].' WHERE owner>0 GROUP BY owner ORDER BY moep DESC LIMIT 1';
            list ($max_uid, $max_cnt) = $this->DB->fetchrow($this->DB->query($query));
        }
        return array
                ('count' => isset($cnt) ? $cnt : 0
                ,'sum' => isset($sum) ? $sum : 0
                ,'max_uid' => isset($max_uid) ? $max_uid : 0
                ,'max_count' => isset($max_cnt) ? $max_cnt : 0
                );
    }
}
?>
Return current item: phlyMail Lite