function retrieve($start, $end, $iFilter = '', $xFilter = '')
{
global $privs, $ucats, $pcats, $evtList;
$evtList = array();
//clear event list
//set categories to retrieve
$catList = ($ucats == '0' or $pcats == '0') ? '0' : $ucats . (!empty($pcats) ? ",{$pcats}" : '');
//set filter
$filter = '';
if (strpos($iFilter, 'g') !== false and count($_SESSION['cG']) > 0 and $_SESSION['cG'][0] != 0) {
$filter .= " AND u.`groupID` IN (" . implode(",", $_SESSION['cG']) . ")";
}
if (strpos($iFilter, 'u') !== false and count($_SESSION['cU']) > 0 and $_SESSION['cU'][0] != 0) {
$filter .= " AND e.`userID` IN (" . implode(",", $_SESSION['cU']) . ")";
}
if (strpos($iFilter, 'c') !== false and count($_SESSION['cC']) > 0 and $_SESSION['cC'][0] != 0) {
$filter .= " AND c.`sequence` IN (" . implode(",", $_SESSION['cC']) . ")";
}
if ($xFilter) {
$filter .= ' AND ' . $xFilter;
}
//add external filter
//set user id
$userId = isset($_SESSION['uid']) ? $_SESSION['uid'] : 1;
//if not set, public
/* retrieve events between $start and $end */
$q0 = "SELECT\n\t\te.`ID` AS eid,\n\t\te.`type` AS typ,\n\t\te.`private` AS pri,\n\t\te.`title` AS tit,\n\t\te.`venue` AS ven,\n\t\te.`text1` AS des,\n\t\te.`text2` AS xf1,\n\t\te.`text3` AS xf2,\n\t\te.`catID` AS cid,\n\t\te.`userID` AS uid,\n\t\te.`editor` AS edr,\n\t\te.`approved` AS apd,\n\t\te.`checked` AS chd,\n\t\te.`notify` AS rem,\n\t\te.`notMail` AS rml,\n\t\te.`sDate` AS sda,\n\t\te.`eDate` AS eda,\n\t\te.`xDates` AS xda,\n\t\te.`sTime` AS sti,\n\t\te.`eTime` AS eti,\n\t\te.`rType` AS r_t,\n\t\te.`rInterval` AS r_i,\n\t\te.`rPeriod` AS r_p,\n\t\te.`rMonth` AS r_m,\n\t\te.`rUntil` AS r_u,\n\t\te.`aDateTime` AS adt,\n\t\te.`mDateTime` AS mdt,\n\t\tc.`name` AS cnm,\n\t\tc.`sequence` AS seq,\n\t\tc.`repeat` AS rpt,\n\t\tc.`approve` AS app,\n\t\tc.`dayColor` AS dbg,\n\t\tc.`color` AS cco,\n\t\tc.`bgColor` AS cbg,\n\t\tc.`checkBx` AS cbx,\n\t\tc.`checkLb` AS clb,\n\t\tc.`checkMk` AS cmk,\n\t\tu.`name` AS una,\n\t\tg.`color` AS uco\n\tFROM `events` AS e\n\tINNER JOIN `categories` AS c ON c.`ID` = e.`catID`\n\tINNER JOIN `users` AS u ON u.`ID` = e.`userID`\n\tINNER JOIN `groups` AS g ON g.`ID` = u.`groupID`\n\tWHERE e.`status` >= 0" . $filter;
//process non-recurring events
$q1 = $q0 . "\n\t\tAND c.`repeat` = 0\n\t\tAND e.`rType` = 0" . ($catList != '0' ? "\nAND c.`ID` IN ({$catList})" : '') . "\n\t\tAND ((e.`sDate` <= '{$end}') AND (CASE WHEN e.`eDate` != '9999-00-00' THEN e.`eDate` ELSE e.`sDate` END >= '{$start}'))\n\tORDER BY\n\t\te.`sDate`";
$stH = dbQuery($q1);
while ($row = $stH->fetch(PDO::FETCH_ASSOC)) {
if ((!$row['app'] or $row['apd'] or $privs > 3) and !$row['pri'] or $row['uid'] == $userId or $privs == 9) {
//private / not approved: for current user + admin
$eEnd = $row['eda'][0] != '9' ? $row['eda'] : $row['sda'];
processEvent(max($start, $row['sda']), min($end, $eEnd), $row['sda'], $eEnd, $row);
}
}
//process recurring events
$q1 = $q0 . "\n\t\tAND (c.`repeat` > 0 OR e.`rType` > 0)" . ($catList != '0' ? "\nAND c.`ID` IN ({$catList})" : '') . "\n\t\tAND e.`sDate` <= '{$end}'\n\t\tAND e.`rUntil` >= '{$start}'\n\tORDER BY\n\t\te.`sDate`";
$stH = dbQuery($q1);
while ($row = $stH->fetch(PDO::FETCH_ASSOC)) {
if ((!$row['app'] or $row['apd'] or $privs > 3) and !$row['pri'] or $row['uid'] == $userId or $privs == 9) {
//private / not approved: for current user + admin
$dDif = $row['eda'][0] != '9' ? intval((strtotime($row['eda']) - strtotime($row['sda'])) / 86400) : 0;
//delta start date - end date
$eStart = $row['sda'];
if ($row['rpt'] > 0) {
//cat repeat overrides
$row['r_t'] = $row['r_i'] = 1;
$row['r_p'] = $row['rpt'];
$row['r_u'] = '9999-00-00';
} elseif ($row['r_t'] == 2) {
$nxtD = nextRdate2($eStart, $row['r_i'], $row['r_p'], $row['r_m'], 0);
//goto 1st occurrence of xth <dayname> in month y
$eStart = $nxtD < $eStart ? nextRdate2($eStart, $row['r_i'], $row['r_p'], $row['r_m'], 1) : $nxtD;
}
$eEnd = date("Y-m-d", mktime(12, 0, 0, substr($eStart, 5, 2), substr($eStart, 8, 2) + $dDif, substr($eStart, 0, 4)));
while ($eStart <= min($end, $row['r_u']) and $row['r_u'] >= $start) {
if ($eEnd >= $start) {
//hit
processEvent(max($start, $eStart), min($end, $eEnd), $eStart, $eEnd, $row);
}
$eStart = $row['r_t'] == 1 ? nextRdate1($eStart, $row['r_i'], $row['r_p']) : nextRdate2($eStart, $row['r_i'], $row['r_p'], $row['r_m'], 1);
$eEnd = date("Y-m-d", mktime(12, 0, 0, substr($eStart, 5, 2), substr($eStart, 8, 2) + $dDif, substr($eStart, 0, 4)));
}
}
}
//sort the event list per date
ksort($evtList);
foreach ($evtList as &$events) {
usort($events, 'sortEvt');
}
}
function retrieve($start, $end, $iFilter = '', $xFilter = '')
{
global $privs, $evtList;
$evtList = array();
//clear event list
//set filter
$filter = '';
if (strpos($iFilter, 'u') !== false and count($_SESSION['cU']) > 0 and $_SESSION['cU'][0] != 0) {
$filter .= " AND e.user_id IN (" . implode(",", $_SESSION['cU']) . ")";
}
if (strpos($iFilter, 'c') !== false and count($_SESSION['cC']) > 0 and $_SESSION['cC'][0] != 0) {
$filter .= " AND c.sequence IN (" . implode(",", $_SESSION['cC']) . ")";
}
if (!isset($_SESSION['uid']) or $_SESSION['uid'] == 1) {
$filter .= " AND c.public = 1";
}
//only show public events
if ($xFilter) {
$filter .= ' AND ' . $xFilter;
}
//add external filter
//set user id
$userId = isset($_SESSION['uid']) ? $_SESSION['uid'] : 1;
//if not set, public
/* retrieve events between $start and $end */
$q0 = "SELECT\n\t\te.s_date AS sda,\n\t\te.e_date AS eda,\n\t\tDATE_FORMAT(e.s_time,'%H:%i') AS sti,\n\t\tDATE_FORMAT(e.e_time,'%H:%i') AS eti,\n\t\te.r_type AS r_t,\n\t\te.r_interval AS r_i,\n\t\te.r_period AS r_p,\n\t\te.r_month AS r_m,\n\t\te.r_until AS r_u,\n\t\te.notify AS rem,\n\t\te.not_mail AS rml,\n\t\te.a_datetime AS adt,\n\t\te.m_datetime AS mdt,\n\t\te.event_id AS eid,\n\t\te.event_type AS typ,\n\t\te.title AS tit,\n\t\te.category_id AS cid,\n\t\te.venue AS ven,\n\t\te.description AS des,\n\t\te.xfield1 AS xf1,\n\t\te.xfield2 AS xf2,\n\t\te.user_id AS uid,\n\t\te.editor AS edr,\n\t\te.approved AS apd,\n\t\te.private AS pri,\n\t\te.checked AS chd,\n\t\te.x_dates AS xda,\n\t\tc.name AS cnm,\n\t\tc.approve AS app,\n\t\tc.sequence AS seq,\n\t\tc.rpeat AS rpt,\n\t\tc.color AS cco,\n\t\tc.background AS cbg,\n\t\tc.chbox AS cbx,\n\t\tc.chlabel AS clb,\n\t\tc.chmark AS cmk,\n\t\tu.user_name AS una,\n\t\tu.color AS uco\n\tFROM [db]events AS e\n\tINNER JOIN [db]categories AS c ON c.category_id = e.category_id\n\tINNER JOIN [db]users AS u ON u.user_id = e.user_id\n\tWHERE e.status >= 0" . $filter;
//process non-recurring events
$q1 = $q0 . "\n\t\tAND c.rpeat = 0\n\t\tAND e.r_type = 0\n\t\tAND ((e.s_date <= '{$end}') AND (IF(e.e_date != '9999-00-00', e.e_date, e.s_date) >= '{$start}') )\n\tORDER BY\n\t\te.s_date";
$rSet = dbQuery($q1);
//echo "NR: ".mysql_num_rows($rSet)." "; //TEST
while ($row = mysql_fetch_assoc($rSet)) {
if ((!$row['app'] or $row['apd'] or $privs > 3) and !$row['pri'] or $row['uid'] == $userId or $privs == 9) {
//private / not approved: for current user + admin
$eEnd = $row['eda'][0] != '9' ? $row['eda'] : $row['sda'];
processEvent(max($start, $row['sda']), min($end, $eEnd), $row['sda'], $eEnd, $row);
}
}
//process recurring events
$q1 = $q0 . "\n\t\tAND (c.rpeat > 0 OR e.r_type > 0)\n\t\tAND e.s_date <= '{$end}'\n\t\tAND e.r_until >= '{$start}'\n\tORDER BY\n\t\te.s_date";
$rSet = dbQuery($q1);
while ($row = mysql_fetch_assoc($rSet)) {
if ((!$row['app'] or $row['apd'] or $privs > 3) and !$row['pri'] or $row['uid'] == $userId or $privs == 9) {
//private / not approved: for current user + admin
$dDif = $row['eda'][0] != '9' ? intval((strtotime($row['eda']) - strtotime($row['sda'])) / 86400) : 0;
//delta start date - end date
$eStart = $row['sda'];
if ($row['rpt'] > 0) {
//cat repeat overrides
$row['r_t'] = $row['r_i'] = 1;
$row['r_p'] = $row['rpt'];
$row['r_u'] = '9999-00-00';
} elseif ($row['r_t'] == 2) {
$nxtD = nextRdate($eStart, $row['r_t'], $row['r_i'], $row['r_p'], $row['r_m'], 0);
//goto 1st occurence of xth <dayname> in month y
$eStart = $nxtD < $eStart ? nextRdate($eStart, $row['r_t'], $row['r_i'], $row['r_p'], $row['r_m'], 1) : $nxtD;
}
$eEnd = date("Y-m-d", mktime(12, 0, 0, substr($eStart, 5, 2), substr($eStart, 8, 2) + $dDif, substr($eStart, 0, 4)));
while ($eStart <= min($end, $row['r_u']) and $row['r_u'] >= $start) {
if ($eEnd >= $start) {
//hit
processEvent(max($start, $eStart), min($end, $eEnd), $eStart, $eEnd, $row);
}
$eStart = nextRdate($eStart, $row['r_t'], $row['r_i'], $row['r_p'], $row['r_m'], 1);
$eEnd = date("Y-m-d", mktime(12, 0, 0, substr($eStart, 5, 2), substr($eStart, 8, 2) + $dDif, substr($eStart, 0, 4)));
}
}
}
//sort the event list per date
ksort($evtList);
foreach ($evtList as &$events) {
usort($events, 'sortEvt');
}
}
