<?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
);
}
}
?>