/**
* This function retrieves one personal agenda item returns it.
* @param array The array containing existing events. We add to this array.
* @param int Day
* @param int Month
* @param int Year (4 digits)
* @param int Week number
* @param string Type of view (month_view, week_view, day_view)
* @return array The results of the database query, or null if not found
*/
function get_global_agenda_items($agendaitems, $day = "", $month = "", $year = "", $week = "", $type)
{
$tbl_global_agenda = Database::get_main_table(TABLE_MAIN_SYSTEM_CALENDAR);
$month = intval($month);
$year = intval($year);
$week = intval($week);
$day = intval($day);
// 1. creating the SQL statement for getting the personal agenda items in MONTH view
$current_access_url_id = api_get_current_access_url_id();
if ($type == "month_view" or $type == "") {
// We are in month view
$sql = "SELECT * FROM " . $tbl_global_agenda . " WHERE MONTH(start_date) = " . $month . " AND YEAR(start_date) = " . $year . " AND access_url_id = {$current_access_url_id} ORDER BY start_date ASC";
}
// 2. creating the SQL statement for getting the personal agenda items in WEEK view
if ($type == "week_view") {
// we are in week view
$start_end_day_of_week = calculate_start_end_of_week($week, $year);
$start_day = $start_end_day_of_week['start']['day'];
$start_month = $start_end_day_of_week['start']['month'];
$start_year = $start_end_day_of_week['start']['year'];
$end_day = $start_end_day_of_week['end']['day'];
$end_month = $start_end_day_of_week['end']['month'];
$end_year = $start_end_day_of_week['end']['year'];
// in sql statements you have to use year-month-day for date calculations
$start_filter = $start_year . "-" . $start_month . "-" . $start_day . " 00:00:00";
$start_filter = api_get_utc_datetime($start_filter);
$end_filter = $end_year . "-" . $end_month . "-" . $end_day . " 23:59:59";
$end_filter = api_get_utc_datetime($end_filter);
$sql = " SELECT * FROM " . $tbl_global_agenda . " WHERE start_date>='" . $start_filter . "' AND start_date<='" . $end_filter . "' AND access_url_id = {$current_access_url_id} ";
}
// 3. creating the SQL statement for getting the personal agenda items in DAY view
if ($type == "day_view") {
// we are in day view
// we could use mysql date() function but this is only available from 4.1 and higher
$start_filter = $year . "-" . $month . "-" . $day . " 00:00:00";
$start_filter = api_get_utc_datetime($start_filter);
$end_filter = $year . "-" . $month . "-" . $day . " 23:59:59";
$end_filter = api_get_utc_datetime($end_filter);
$sql = " SELECT * FROM " . $tbl_global_agenda . " WHERE start_date>='" . $start_filter . "' AND start_date<='" . $end_filter . "' AND access_url_id = {$current_access_url_id}";
}
$result = Database::query($sql);
while ($item = Database::fetch_array($result)) {
if ($item['start_date'] != '0000-00-00 00:00:00') {
$item['start_date'] = api_get_local_time($item['start_date']);
$item['start_date_tms'] = api_strtotime($item['start_date']);
}
if ($item['end_date'] != '0000-00-00 00:00:00') {
$item['end_date'] = api_get_local_time($item['end_date']);
}
// we break the date field in the database into a date and a time part
$agenda_db_date = explode(" ", $item['start_date']);
$date = $agenda_db_date[0];
$time = $agenda_db_date[1];
// we divide the date part into a day, a month and a year
$agendadate = explode("-", $date);
$year = intval($agendadate[0]);
$month = intval($agendadate[1]);
$day = intval($agendadate[2]);
// we divide the time part into hour, minutes, seconds
$agendatime = explode(":", $time);
$hour = $agendatime[0];
$minute = $agendatime[1];
$second = $agendatime[2];
if ($type == 'month_view') {
$item['calendar_type'] = 'global';
$agendaitems[$day][] = $item;
continue;
}
$start_time = api_format_date($item['start_date'], TIME_NO_SEC_FORMAT);
$end_time = '';
if ($item['end_date'] != '0000-00-00 00:00:00') {
$end_time = ' - ' . api_format_date($item['end_date'], DATE_TIME_FORMAT_LONG);
}
// if the student has specified a course we a add a link to that course
if ($item['course'] != "") {
$url = api_get_path(WEB_CODE_PATH) . "admin/agenda.php?cidReq=" . urlencode($item['course']) . "&day={$day}&month={$month}&year={$year}#{$day}";
// RH //Patrick Cool: to highlight the relevant agenda item
$course_link = "<a href=\"{$url}\" title=\"" . $item['course'] . "\">" . $item['course'] . "</a>";
} else {
$course_link = "";
}
// Creating the array that will be returned. If we have week or month view we have an array with the date as the key
// if we have a day_view we use a half hour as index => key 33 = 16h30
if ($type !== "day_view") {
// This is the array construction for the WEEK or MONTH view
//Display the Agenda global in the tab agenda (administrator)
$agendaitems[$day] .= "<i>{$start_time} {$end_time}</i> - ";
$agendaitems[$day] .= "<b>" . get_lang('GlobalEvent') . "</b>";
$agendaitems[$day] .= "<div>" . $item['title'] . "</div><br>";
} else {
// this is the array construction for the DAY view
$halfhour = 2 * $agendatime['0'];
if ($agendatime['1'] >= '30') {
$halfhour = $halfhour + 1;
}
if (!is_array($agendaitems[$halfhour])) {
$content = $agendaitems[$halfhour];
}
$agendaitems[$halfhour] = $content . "<div><i>{$hour}:{$minute}</i> <b>" . get_lang('GlobalEvent') . ": </b>" . $item['title'] . "</div>";
}
}
return $agendaitems;
}
/**
* This function retrieves all the personal agenda items and add them to the agenda items found by the other functions.
*/
function get_personal_agenda_items($user_id, $agendaitems, $day = "", $month = "", $year = "", $week = "", $type)
{
$tbl_personal_agenda = Database::get_main_table(TABLE_PERSONAL_AGENDA);
$user_id = intval($user_id);
// 1. creating the SQL statement for getting the personal agenda items in MONTH view
if ($type == "month_view" or $type == "") {
// we are in month view
$sql = "SELECT * FROM " . $tbl_personal_agenda . " WHERE user='******' and MONTH(date)='" . $month . "' AND YEAR(date) = '" . $year . "' ORDER BY date ASC";
}
// 2. creating the SQL statement for getting the personal agenda items in WEEK view
// we are in week view
if ($type == "week_view") {
$start_end_day_of_week = calculate_start_end_of_week($week, $year);
$start_day = $start_end_day_of_week['start']['day'];
$start_month = $start_end_day_of_week['start']['month'];
$start_year = $start_end_day_of_week['start']['year'];
$end_day = $start_end_day_of_week['end']['day'];
$end_month = $start_end_day_of_week['end']['month'];
$end_year = $start_end_day_of_week['end']['year'];
// in sql statements you have to use year-month-day for date calculations
$start_filter = $start_year . "-" . $start_month . "-" . $start_day . " 00:00:00";
$start_filter = api_get_utc_datetime($start_filter);
$end_filter = $end_year . "-" . $end_month . "-" . $end_day . " 23:59:59";
$end_filter = api_get_utc_datetime($end_filter);
$sql = " SELECT * FROM " . $tbl_personal_agenda . " WHERE user='******' AND date>='" . $start_filter . "' AND date<='" . $end_filter . "'";
}
// 3. creating the SQL statement for getting the personal agenda items in DAY view
if ($type == "day_view") {
// we are in day view
// we could use mysql date() function but this is only available from 4.1 and higher
$start_filter = $year . "-" . $month . "-" . $day . " 00:00:00";
$start_filter = api_get_utc_datetime($start_filter);
$end_filter = $year . "-" . $month . "-" . $day . " 23:59:59";
$end_filter = api_get_utc_datetime($end_filter);
$sql = " SELECT * FROM " . $tbl_personal_agenda . " WHERE user='******' AND date>='" . $start_filter . "' AND date<='" . $end_filter . "'";
}
$result = Database::query($sql);
while ($item = Database::fetch_array($result, 'ASSOC')) {
$time_minute = api_convert_and_format_date($item['date'], TIME_NO_SEC_FORMAT);
$item['date'] = api_get_local_time($item['date']);
$item['start_date_tms'] = api_strtotime($item['date']);
$item['content'] = $item['text'];
// we break the date field in the database into a date and a time part
$agenda_db_date = explode(" ", $item['date']);
$date = $agenda_db_date[0];
$time = $agenda_db_date[1];
// we divide the date part into a day, a month and a year
$agendadate = explode("-", $item['date']);
$year = intval($agendadate[0]);
$month = intval($agendadate[1]);
$day = intval($agendadate[2]);
// we divide the time part into hour, minutes, seconds
$agendatime = explode(":", $time);
$hour = $agendatime[0];
$minute = $agendatime[1];
$second = $agendatime[2];
if ($type == 'month_view') {
$item['calendar_type'] = 'personal';
$item['start_date'] = $item['date'];
$agendaitems[$day][] = $item;
continue;
}
// if the student has specified a course we a add a link to that course
if ($item['course'] != "") {
$url = api_get_path(WEB_CODE_PATH) . "calendar/agenda.php?cidReq=" . urlencode($item['course']) . "&day={$day}&month={$month}&year={$year}#{$day}";
// RH //Patrick Cool: to highlight the relevant agenda item
$course_link = "<a href=\"{$url}\" title=\"" . $item['course'] . "\">" . $item['course'] . "</a>";
} else {
$course_link = "";
}
// Creating the array that will be returned. If we have week or month view we have an array with the date as the key
// if we have a day_view we use a half hour as index => key 33 = 16h30
if ($type !== "day_view") {
// This is the array construction for the WEEK or MONTH view
//Display events in agenda
$agendaitems[$day] .= "<div><i>{$time_minute}</i> {$course_link} <a href=\"myagenda.php?action=view&view=personal&day={$day}&month={$month}&year={$year}&id=" . $item['id'] . "#" . $item['id'] . "\" class=\"personal_agenda\">" . $item['title'] . "</a></div><br />";
} else {
// this is the array construction for the DAY view
$halfhour = 2 * $agendatime['0'];
if ($agendatime['1'] >= '30') {
$halfhour = $halfhour + 1;
}
//Display events by list
$agendaitems[$halfhour] .= "<div><i>{$time_minute}</i> {$course_link} <a href=\"myagenda.php?action=view&view=personal&day={$day}&month={$month}&year={$year}&id=" . $item['id'] . "#" . $item['id'] . "\" class=\"personal_agenda\">" . $item['title'] . "</a></div>";
}
}
return $agendaitems;
}
/**
* Return agenda items of the week
*/
function get_week_agendaitems($courses_dbs, $month, $year, $week = '')
{
global $_user;
global $_configuration;
global $setting_agenda_link;
$TABLEAGENDA = Database::get_main_table(TABLE_MAIN_SYSTEM_CALENDAR);
$items = array();
// The default value of the week
if ($week == '') {
$week_number = date("W", time());
} else {
$week_number = $week;
}
$start_end = calculate_start_end_of_week($week_number, $year);
$start_filter = $start_end['start']['year'] . "-" . $start_end['start']['month'] . "-" . $start_end['start']['day'];
$end_filter = $start_end['end']['year'] . "-" . $start_end['end']['month'] . "-" . $start_end['end']['day'];
// get agenda-items for every course
foreach ($courses_dbs as $key => $array_course_info) {
//databases of the courses
$TABLEAGENDA = Database::get_main_table(TABLE_MAIN_SYSTEM_CALENDAR);
//$TABLE_ITEMPROPERTY = Database :: get_course_table(TABLE_ITEM_PROPERTY, $array_course_info["db"]);
// getting all the groups of the user for the current course
// $group_memberships = GroupManager :: get_group_ids($array_course_info["db"], $_user['user_id']);
// if the user is administrator of that course we show all the agenda items
//if ($array_course_info['status'] == '1')
//{
//echo "course admin";
$sqlquery = "SELECT DISTINCT * FROM " . $TABLEAGENDA . " ORDER BY start_date";
//}
// if the user is not an administrator of that course
//else
//{
//echo "GEEN course admin";
/* if (is_array($group_memberships) && count($group_memberships)>0)
{
$sqlquery = "SELECT * FROM ".$TABLEAGENDA." ORDER BY a.start_date";
}
else*/
//{
// $sqlquery = "SELECT * FROM ".$TABLEAGENDA." ORDER BY a.start_date";
//}
//}
//echo "<pre>".$sqlquery."</pre>";
// $sqlquery = "SELECT * FROM $agendadb WHERE (DAYOFMONTH(day)>='$start_day' AND DAYOFMONTH(day)<='$end_day')
// AND (MONTH(day)>='$start_month' AND MONTH(day)<='$end_month')
// AND (YEAR(day)>='$start_year' AND YEAR(day)<='$end_year')";
$result = Database::query($sqlquery);
$portal_url = $_configuration['root_web'];
if ($_configuration['multiple_access_urls']) {
$access_url_id = api_get_current_access_url_id();
if ($access_url_id != -1) {
$url = api_get_access_url($access_url_id);
$portal_url = $url['url'];
}
}
while ($item = Database::fetch_array($result)) {
$agendaday_string = api_convert_and_format_date($item['start_date'], "%d", date_default_timezone_get());
$agendaday = intval($agendaday_string);
$time = api_convert_and_format_date($item['start_date'], TIME_NO_SEC_FORMAT, date_default_timezone_get());
if ($setting_agenda_link == 'coursecode') {
$title = $array_course_info['title'];
$agenda_link = api_substr($title, 0, 14);
} else {
$agenda_link = Display::return_icon('course_home.gif');
}
$URL = $portal_url . "main/admin/agenda.php?cidReq=" . urlencode($array_course_info["code"]) . "&day={$agendaday}&month={$month}&year={$year}#{$agendaday}";
// RH //Patrick Cool: to highlight the relevant agenda item
$items[$agendaday][$item['start_time']] .= "<i>{$time}</i> <a href=\"{$URL}\" title=\"" . $array_course_info["name"] . "\">" . $agenda_link . "</a> " . $item['title'] . "<br />";
}
}
// sorting by hour for every day
$agendaitems = array();
while (list($agendaday, $tmpitems) = each($items)) {
sort($tmpitems);
while (list($key, $val) = each($tmpitems)) {
$agendaitems[$agendaday] .= $val;
}
}
//print_r($agendaitems);
return $agendaitems;
}
