private function get_announcements($username, $course_code, $announcement_id = 0)
{
$session_id = api_get_session_id();
$condition_session = api_get_session_condition($session_id);
$announcement_id = $announcement_id == 0 ? "" : "AND announcement.id=" . $announcement_id;
$user_id = UserManager::get_user_id_from_username($username);
//$listOfCourses = CourseManager::get_course_information_by_id($course_id);
$course_info = CourseManager::get_course_information($course_code);
$course_db = $course_info['db_name'];
$tbl_item_property = Database::get_course_table(TABLE_ITEM_PROPERTY, $course_db);
$tbl_announcement = Database::get_course_table(TABLE_ANNOUNCEMENT, $course_db);
$maximum = '12';
$group_memberships = GroupManager::get_group_ids($course_info['real_id'], $user_id);
if (api_get_group_id() == 0) {
$cond_user_id = " AND ( ip.to_user_id='" . $user_id . "'" . "OR ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ")) ";
} else {
$cond_user_id = " AND ( ip.to_user_id='" . $user_id . "'" . "OR ip.to_group_id IN (0, " . api_get_group_id() . ")) ";
}
// the user is member of several groups => display personal announcements AND his group announcements AND the general announcements
if (is_array($group_memberships) && count($group_memberships) > 0) {
$sql = "SELECT\n announcement.*, ip.visibility, ip.to_group_id, ip.insert_user_id\n FROM {$tbl_announcement} announcement, {$tbl_item_property} ip\n WHERE announcement.id = ip.ref\n AND ip.tool='announcement'\n AND ip.visibility='1'\n {$announcement_id}\n {$cond_user_id}\n {$condition_session}\n GROUP BY ip.ref\n ORDER BY display_order DESC\n LIMIT 0,{$maximum}";
} else {
// the user is not member of any group
// this is an identified user => show the general announcements AND his personal announcements
if ($user_id) {
if (api_get_course_setting('allow_user_edit_announcement') && !api_is_anonymous()) {
$cond_user_id = " AND (ip.lastedit_user_id = '" . api_get_user_id() . "' OR ( ip.to_user_id='" . $user_id . "' OR ip.to_group_id='0')) ";
} else {
$cond_user_id = " AND ( ip.to_user_id='" . $user_id . "' OR ip.to_group_id='0') ";
}
$sql = "SELECT\n announcement.*, ip.visibility, ip.to_group_id, ip.insert_user_id\n FROM {$tbl_announcement} announcement, {$tbl_item_property} ip\n WHERE announcement.id = ip.ref\n AND ip.tool='announcement'\n AND ip.visibility='1'\n {$announcement_id}\n {$cond_user_id}\n {$condition_session}\n GROUP BY ip.ref\n ORDER BY display_order DESC\n LIMIT 0,{$maximum}";
} else {
if (api_get_course_setting('allow_user_edit_announcement')) {
$cond_user_id = " AND (ip.lastedit_user_id = '" . api_get_user_id() . "' OR ip.to_group_id='0') ";
} else {
$cond_user_id = " AND ip.to_group_id='0' ";
}
// the user is not identiefied => show only the general announcements
$sql = "SELECT\n announcement.*, ip.visibility, ip.to_group_id, ip.insert_user_id\n FROM {$tbl_announcement} announcement, {$tbl_item_property} ip\n WHERE announcement.id = ip.ref\n AND ip.tool='announcement'\n AND ip.visibility='1'\n AND ip.to_group_id='0'\n {$announcement_id}\n {$condition_session}\n GROUP BY ip.ref\n ORDER BY display_order DESC\n LIMIT 0,{$maximum}";
}
}
$result = Database::query($sql);
return $result;
}
/**
* Get personal agenda items between two dates (=all events from all registered courses)
* @param int user ID of the user
* @param string Optional start date in datetime format (if no start date is given, uses today)
* @param string Optional end date in datetime format (if no date is given, uses one year from now)
* @return array Array of events ordered by start date, in [0]('datestart','dateend','title'),[1]('datestart','dateend','title','link','coursetitle') format, where datestart and dateend are in yyyyMMddhhmmss format.
* @TODO Implement really personal events (from user DB) and global events (from main DB)
*/
function get_personal_agenda_items_between_dates($user_id, $date_start = '', $date_end = '')
{
$items = array();
if ($user_id != strval(intval($user_id))) {
return $items;
}
if (empty($date_start)) {
$date_start = date('Y-m-d H:i:s');
}
if (empty($date_end)) {
$date_end = date('Y-m-d H:i:s', mktime(0, 0, 0, date("m"), date("d"), date("Y") + 1));
}
$expr = '/\\d{4}-\\d{2}-\\d{2}\\ \\d{2}:\\d{2}:\\d{2}/';
if (!preg_match($expr, $date_start)) {
return $items;
}
if (!preg_match($expr, $date_end)) {
return $items;
}
// get agenda-items for every course
$courses = api_get_user_courses($user_id, false);
foreach ($courses as $id => $course) {
$c = api_get_course_info($course['code']);
//databases of the courses
$t_a = Database::get_course_table(TABLE_AGENDA, $course['db']);
$t_ip = Database::get_course_table(TABLE_ITEM_PROPERTY, $course['db']);
// get the groups to which the user belong
$group_memberships = GroupManager::get_group_ids($course['db'], $user_id);
// if the user is administrator of that course we show all the agenda items
if ($course['status'] == '1') {
//echo "course admin";
$sqlquery = "SELECT " . " DISTINCT agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref " . " FROM " . $t_a . " agenda, " . $t_ip . " ip " . " WHERE agenda.id = ip.ref " . " AND agenda.start_date>='{$date_start}' " . " AND agenda.end_date<='{$date_end}' " . " AND ip.tool='" . TOOL_CALENDAR_EVENT . "' " . " AND ip.visibility='1' " . " GROUP BY agenda.id " . " ORDER BY start_date ";
} else {
// if the user is not an administrator of that course, then...
if (is_array($group_memberships) && count($group_memberships) > 0) {
$sqlquery = "SELECT " . "DISTINCT agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref " . " FROM " . $t_a . " agenda, " . $t_ip . " ip " . " WHERE agenda.id = ip.ref " . " AND agenda.start_date>='{$date_start}' " . " AND agenda.end_date<='{$date_end}' " . " AND ip.tool='" . TOOL_CALENDAR_EVENT . "' " . " AND\t( ip.to_user_id='" . $user_id . "' OR ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") ) " . " AND ip.visibility='1' " . " ORDER BY start_date ";
} else {
$sqlquery = "SELECT " . "DISTINCT agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref " . " FROM " . $t_a . " agenda, " . $t_ip . " ip " . " WHERE agenda.id = ip.ref " . " AND agenda.start_date>='{$date_start}' " . " AND agenda.end_date<='{$date_end}' " . " AND ip.tool='" . TOOL_CALENDAR_EVENT . "' " . " AND ( ip.to_user_id='" . $user_id . "' OR ip.to_group_id='0') " . " AND ip.visibility='1' " . " ORDER BY start_date ";
}
}
$result = Database::query($sqlquery);
while ($item = Database::fetch_array($result)) {
$agendaday = date("j", strtotime($item['start_date']));
$URL = api_get_path(WEB_PATH) . "main/calendar/agenda.php?cidReq=" . urlencode($course["code"]) . "&day={$agendaday}&month={$month}&year={$year}#{$agendaday}";
list($year, $month, $day, $hour, $min, $sec) = split('[-: ]', $item['start_date']);
$start_date = $year . $month . $day . $hour . $min;
list($year, $month, $day, $hour, $min, $sec) = split('[-: ]', $item['end_date']);
$end_date = $year . $month . $day . $hour . $min;
$items[] = array('datestart' => $start_date, 'dateend' => $end_date, 'title' => $item['title'], 'link' => $URL, 'coursetitle' => $c['name']);
}
}
return $items;
}
/*
RETRIEVING ALL THE FORUM CATEGORIES AND FORUMS
note: we do this here just after het handling of the actions to be
sure that we already incorporate the latest changes
*/
// Step 1: We store all the forum categories in an array $forum_categories.
$forumCategories = get_forum_categories();
// Step 2: We find all the forums (only the visible ones if it is a student).
// display group forum in general forum tool depending to configuration option
$setting = api_get_setting('display_groups_forum_in_general_tool');
$forum_list = get_forums('', '', $setting == 'true');
$user_id = api_get_user_id();
/* RETRIEVING ALL GROUPS AND THOSE OF THE USER */
// The groups of the user.
$groups_of_user = array();
$groups_of_user = GroupManager::get_group_ids($_course['real_id'], $user_id);
// All groups in the course (and sorting them as the
// id of the group = the key of the array).
if (!api_is_anonymous()) {
$all_groups = GroupManager::get_group_list();
if (is_array($all_groups)) {
foreach ($all_groups as $group) {
$all_groups[$group['id']] = $group;
}
}
}
/* CLEAN GROUP ID FOR AJAXFILEMANAGER */
if (isset($_SESSION['_gid'])) {
unset($_SESSION['_gid']);
}
/* ACTION LINKS */
/**
* Gets the exam'data results
* @todo this function should be moved in a library + no global calls
* @param int $from
* @param int $number_of_items
* @param int $column
* @param string $direction
* @param int $exercise_id
* @param null $extra_where_conditions
* @param bool $get_count
* @return array
*/
public static function get_exam_results_data($from, $number_of_items, $column, $direction, $exercise_id, $extra_where_conditions = null, $get_count = false)
{
//@todo replace all this globals
global $documentPath, $filter;
$course_id = api_get_course_int_id();
$sessionId = api_get_session_id();
$is_allowedToEdit = api_is_allowed_to_edit(null, true) || api_is_allowed_to_edit(true) || api_is_drh() || api_is_student_boss();
$TBL_USER = Database::get_main_table(TABLE_MAIN_USER);
$TBL_EXERCICES = Database::get_course_table(TABLE_QUIZ_TEST);
$TBL_GROUP_REL_USER = Database::get_course_table(TABLE_GROUP_USER);
$TBL_GROUP = Database::get_course_table(TABLE_GROUP);
$TBL_TRACK_EXERCICES = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
$TBL_TRACK_HOTPOTATOES = Database::get_main_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
$TBL_TRACK_ATTEMPT_RECORDING = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
$session_id_and = ' AND te.session_id = ' . $sessionId . ' ';
$exercise_id = intval($exercise_id);
$exercise_where = '';
if (!empty($exercise_id)) {
$exercise_where .= ' AND te.exe_exo_id = ' . $exercise_id . ' ';
}
$hotpotatoe_where = '';
if (!empty($_GET['path'])) {
$hotpotatoe_path = Database::escape_string($_GET['path']);
$hotpotatoe_where .= ' AND exe_name = "' . $hotpotatoe_path . '" ';
}
// sql for chamilo-type tests for teacher / tutor view
$sql_inner_join_tbl_track_exercices = "\n (\n SELECT DISTINCT ttte.*, if(tr.exe_id,1, 0) as revised\n FROM {$TBL_TRACK_EXERCICES} ttte LEFT JOIN {$TBL_TRACK_ATTEMPT_RECORDING} tr\n ON (ttte.exe_id = tr.exe_id)\n WHERE\n c_id = {$course_id} AND\n exe_exo_id = {$exercise_id} AND\n ttte.session_id = " . $sessionId . "\n )";
if ($is_allowedToEdit) {
//@todo fix to work with COURSE_RELATION_TYPE_RRHH in both queries
// Hack in order to filter groups
$sql_inner_join_tbl_user = '';
if (strpos($extra_where_conditions, 'group_id')) {
$sql_inner_join_tbl_user = "******" . $course_id . ")\n INNER JOIN {$TBL_GROUP} g\n ON (gru.group_id = g.id AND g.c_id=" . $course_id . ")\n )";
}
if (strpos($extra_where_conditions, 'group_all')) {
$extra_where_conditions = str_replace("AND ( group_id = 'group_all' )", '', $extra_where_conditions);
$extra_where_conditions = str_replace("AND group_id = 'group_all'", '', $extra_where_conditions);
$extra_where_conditions = str_replace("group_id = 'group_all' AND", '', $extra_where_conditions);
$sql_inner_join_tbl_user = "******";
$sql_inner_join_tbl_user = null;
}
if (strpos($extra_where_conditions, 'group_none')) {
$extra_where_conditions = str_replace("AND ( group_id = 'group_none' )", "AND ( group_id is null )", $extra_where_conditions);
$extra_where_conditions = str_replace("AND group_id = 'group_none'", "AND ( group_id is null )", $extra_where_conditions);
$sql_inner_join_tbl_user = "******" . $course_id . " )\n LEFT OUTER JOIN {$TBL_GROUP} g\n ON (gru.group_id = g.id AND g.c_id = " . $course_id . ")\n )";
}
// All
$is_empty_sql_inner_join_tbl_user = false;
if (empty($sql_inner_join_tbl_user)) {
$is_empty_sql_inner_join_tbl_user = true;
$sql_inner_join_tbl_user = "******" . api_get_users_status_ignored_in_reports('string') . ")\n )";
}
$sqlFromOption = " , {$TBL_GROUP_REL_USER} AS gru ";
$sqlWhereOption = " AND gru.c_id = " . $course_id . " AND gru.user_id = user.user_id ";
$first_and_last_name = api_is_western_name_order() ? "firstname, lastname" : "lastname, firstname";
if ($get_count) {
$sql_select = "SELECT count(te.exe_id) ";
} else {
$sql_select = "SELECT DISTINCT\n user_id,\n {$first_and_last_name},\n official_code,\n ce.title,\n username,\n te.exe_result,\n te.exe_weighting,\n te.exe_date,\n te.exe_id,\n email as exemail,\n te.start_date,\n steps_counter,\n exe_user_id,\n te.exe_duration,\n propagate_neg,\n revised,\n group_name,\n group_id,\n orig_lp_id,\n te.user_ip";
}
$sql = " {$sql_select}\n FROM {$TBL_EXERCICES} AS ce\n INNER JOIN {$sql_inner_join_tbl_track_exercices} AS te\n ON (te.exe_exo_id = ce.id)\n INNER JOIN {$sql_inner_join_tbl_user} AS user\n ON (user.user_id = exe_user_id)\n WHERE\n te.status != 'incomplete' AND\n te.c_id = " . $course_id . " {$session_id_and} AND\n ce.active <>-1 AND ce.c_id = " . $course_id . "\n {$exercise_where}\n {$extra_where_conditions}\n ";
// sql for hotpotatoes tests for teacher / tutor view
if ($get_count) {
$hpsql_select = "SELECT count(username)";
} else {
$hpsql_select = "SELECT\n {$first_and_last_name} ,\n username,\n official_code,\n tth.exe_name,\n tth.exe_result ,\n tth.exe_weighting,\n tth.exe_date";
}
$hpsql = " {$hpsql_select}\n FROM\n {$TBL_TRACK_HOTPOTATOES} tth,\n {$TBL_USER} user\n {$sqlFromOption}\n WHERE\n user.user_id=tth.exe_user_id\n AND tth.c_id = " . $course_id . "\n {$hotpotatoe_where}\n {$sqlWhereOption}\n AND user.status NOT IN(" . api_get_users_status_ignored_in_reports('string') . ")\n ORDER BY\n tth.c_id ASC,\n tth.exe_date DESC";
}
if ($get_count) {
$resx = Database::query($sql);
$rowx = Database::fetch_row($resx, 'ASSOC');
return $rowx[0];
}
$teacher_list = CourseManager::getTeacherListFromCourse(api_get_course_int_id());
$teacher_id_list = array();
if (!empty($teacher_list)) {
foreach ($teacher_list as $teacher) {
$teacher_id_list[] = $teacher['user_id'];
}
}
$list_info = array();
// Simple exercises
if (empty($hotpotatoe_where)) {
$column = !empty($column) ? Database::escape_string($column) : null;
$from = intval($from);
$number_of_items = intval($number_of_items);
if (!empty($column)) {
$sql .= " ORDER BY {$column} {$direction} ";
}
$sql .= " LIMIT {$from}, {$number_of_items}";
$results = array();
$resx = Database::query($sql);
while ($rowx = Database::fetch_array($resx, 'ASSOC')) {
$results[] = $rowx;
}
$group_list = GroupManager::get_group_list();
$clean_group_list = array();
if (!empty($group_list)) {
foreach ($group_list as $group) {
$clean_group_list[$group['id']] = $group['name'];
}
}
$lp_list_obj = new LearnpathList(api_get_user_id());
$lp_list = $lp_list_obj->get_flat_list();
if (is_array($results)) {
$users_array_id = array();
$from_gradebook = false;
if (isset($_GET['gradebook']) && $_GET['gradebook'] == 'view') {
$from_gradebook = true;
}
$sizeof = count($results);
$user_list_id = array();
$locked = api_resource_is_locked_by_gradebook($exercise_id, LINK_EXERCISE);
// Looping results
for ($i = 0; $i < $sizeof; $i++) {
$revised = $results[$i]['revised'];
if ($from_gradebook && $is_allowedToEdit) {
if (in_array($results[$i]['username'] . $results[$i]['firstname'] . $results[$i]['lastname'], $users_array_id)) {
continue;
}
$users_array_id[] = $results[$i]['username'] . $results[$i]['firstname'] . $results[$i]['lastname'];
}
$lp_obj = isset($results[$i]['orig_lp_id']) && isset($lp_list[$results[$i]['orig_lp_id']]) ? $lp_list[$results[$i]['orig_lp_id']] : null;
$lp_name = null;
if ($lp_obj) {
$url = api_get_path(WEB_CODE_PATH) . 'newscorm/lp_controller.php?' . api_get_cidreq() . '&action=view&lp_id=' . $results[$i]['orig_lp_id'];
$lp_name = Display::url($lp_obj['lp_name'], $url, array('target' => '_blank'));
}
//Add all groups by user
$group_name_list = null;
if ($is_empty_sql_inner_join_tbl_user) {
$group_list = GroupManager::get_group_ids(api_get_course_int_id(), $results[$i]['user_id']);
foreach ($group_list as $id) {
$group_name_list .= $clean_group_list[$id] . '<br/>';
}
$results[$i]['group_name'] = $group_name_list;
}
$results[$i]['exe_duration'] = !empty($results[$i]['exe_duration']) ? round($results[$i]['exe_duration'] / 60) : 0;
$user_list_id[] = $results[$i]['exe_user_id'];
$id = $results[$i]['exe_id'];
$dt = api_convert_and_format_date($results[$i]['exe_weighting']);
// we filter the results if we have the permission to
if (isset($results[$i]['results_disabled'])) {
$result_disabled = intval($results[$i]['results_disabled']);
} else {
$result_disabled = 0;
}
if ($result_disabled == 0) {
$my_res = $results[$i]['exe_result'];
$my_total = $results[$i]['exe_weighting'];
$results[$i]['start_date'] = api_get_local_time($results[$i]['start_date']);
$results[$i]['exe_date'] = api_get_local_time($results[$i]['exe_date']);
if (!$results[$i]['propagate_neg'] && $my_res < 0) {
$my_res = 0;
}
$score = self::show_score($my_res, $my_total);
$actions = '';
if ($is_allowedToEdit) {
if (isset($teacher_id_list)) {
if (in_array($results[$i]['exe_user_id'], $teacher_id_list)) {
$actions .= Display::return_icon('teachers.gif', get_lang('Teacher'));
}
}
if ($revised) {
$actions .= "<a href='exercise_show.php?" . api_get_cidreq() . "&action=edit&id={$id}'>" . Display::return_icon('edit.png', get_lang('Edit'), array(), ICON_SIZE_SMALL);
$actions .= ' ';
} else {
$actions .= "<a href='exercise_show.php?" . api_get_cidreq() . "&action=qualify&id={$id}'>" . Display::return_icon('quiz.gif', get_lang('Qualify'));
$actions .= ' ';
}
$actions .= "</a>";
if ($filter == 2) {
$actions .= ' <a href="exercise_history.php?' . api_get_cidreq() . '&exe_id=' . $id . '">' . Display::return_icon('history.gif', get_lang('ViewHistoryChange')) . '</a>';
}
//Admin can always delete the attempt
if (($locked == false || api_is_platform_admin()) && !api_is_student_boss()) {
$ip = TrackingUserLog::get_ip_from_user_event($results[$i]['exe_user_id'], date('Y-m-d h:i:s'), false);
$actions .= '<a href="http://www.whatsmyip.org/ip-geo-location/?ip=' . $ip . '" target="_blank">';
$actions .= Display::return_icon('info.png', $ip, ['title' => $ip]);
$actions .= '</a>';
$delete_link = '<a href="exercise_report.php?' . api_get_cidreq() . '&filter_by_user='******'filter_by_user']) . '&filter=' . $filter . '&exerciseId=' . $exercise_id . '&delete=delete&did=' . $id . '"
onclick="javascript:if(!confirm(\'' . sprintf(get_lang('DeleteAttempt'), $results[$i]['username'], $dt) . '\')) return false;">' . Display::return_icon('delete.png', get_lang('Delete')) . '</a>';
$delete_link = utf8_encode($delete_link);
if (api_is_drh() && !api_is_platform_admin()) {
$delete_link = null;
}
$actions .= $delete_link . ' ';
}
} else {
$attempt_url = api_get_path(WEB_CODE_PATH) . 'exercice/result.php?' . api_get_cidreq() . '&id=' . $results[$i]['exe_id'] . '&id_session=' . $sessionId;
$attempt_link = Display::url(get_lang('Show'), $attempt_url, ['class' => 'ajax btn btn-default', 'data-title' => get_lang('Show')]);
$actions .= $attempt_link;
}
if ($revised) {
$revised = Display::label(get_lang('Validated'), 'success');
} else {
$revised = Display::label(get_lang('NotValidated'), 'info');
}
if ($is_allowedToEdit) {
$results[$i]['status'] = $revised;
$results[$i]['score'] = $score;
$results[$i]['lp'] = $lp_name;
$results[$i]['actions'] = $actions;
$list_info[] = $results[$i];
} else {
$results[$i]['status'] = $revised;
$results[$i]['score'] = $score;
$results[$i]['actions'] = $actions;
$list_info[] = $results[$i];
}
}
}
}
} else {
$hpresults = StatsUtils::getManyResultsXCol($hpsql, 6);
// Print HotPotatoes test results.
if (is_array($hpresults)) {
for ($i = 0; $i < sizeof($hpresults); $i++) {
$hp_title = GetQuizName($hpresults[$i][3], $documentPath);
if ($hp_title == '') {
$hp_title = basename($hpresults[$i][3]);
}
$hp_date = api_get_local_time($hpresults[$i][6], null, date_default_timezone_get());
$hp_result = round($hpresults[$i][4] / ($hpresults[$i][5] != 0 ? $hpresults[$i][5] : 1) * 100, 2) . '% (' . $hpresults[$i][4] . ' / ' . $hpresults[$i][5] . ')';
if ($is_allowedToEdit) {
$list_info[] = array($hpresults[$i][0], $hpresults[$i][1], $hpresults[$i][2], '', $hp_title, '-', $hp_date, $hp_result, '-');
} else {
$list_info[] = array($hp_title, '-', $hp_date, $hp_result, '-');
}
}
}
}
return $list_info;
}
/**
* @param int $start
* @param int $end
* @param array $course_info
* @param int $group_id
* @param int $session_id
* @param int $user_id
* @return array
*/
public function get_course_events($start, $end, $course_info, $group_id = 0, $session_id = 0, $user_id = 0)
{
$course_id = $course_info['real_id'];
$user_id = intval($user_id);
$group_list = GroupManager::get_group_list(null, $course_info['code']);
$group_name_list = array();
if (!empty($group_list)) {
foreach ($group_list as $group) {
$group_name_list[$group['id']] = $group['name'];
}
}
if (!api_is_allowed_to_edit()) {
$group_memberships = GroupManager::get_group_ids($course_id, api_get_user_id());
$user_id = api_get_user_id();
} else {
$group_memberships = array_keys($group_name_list);
}
$tlb_course_agenda = Database::get_course_table(TABLE_AGENDA);
$tbl_property = Database::get_course_table(TABLE_ITEM_PROPERTY);
if (!empty($group_id)) {
$group_memberships = array($group_id);
}
$session_id = intval($session_id);
if (is_array($group_memberships) && count($group_memberships) > 0) {
if (api_is_allowed_to_edit()) {
if (!empty($user_id)) {
$where_condition = "( ip.to_user_id = {$user_id} AND ip.to_group_id is null OR ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") ) ";
} else {
$where_condition = "( ip.to_group_id is null OR ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") ) ";
}
} else {
$where_condition = "( ip.to_user_id = {$user_id} OR ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") ) ";
}
$sql = "SELECT DISTINCT\n agenda.*,\n ip.visibility,\n ip.to_group_id,\n ip.insert_user_id,\n ip.ref,\n to_user_id\n FROM " . $tlb_course_agenda . " agenda, " . $tbl_property . " ip\n WHERE agenda.id = ip.ref AND\n ip.tool ='" . TOOL_CALENDAR_EVENT . "' AND\n {$where_condition} AND\n ip.visibility = '1' AND\n agenda.c_id = {$course_id} AND\n ip.c_id = {$course_id}\n GROUP BY id";
} else {
if (api_is_allowed_to_edit()) {
$where_condition = "";
} else {
$where_condition = "( ip.to_user_id={$user_id} OR ip.to_group_id='0') AND ";
}
$sql = "SELECT DISTINCT agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref, to_user_id\n FROM " . $tlb_course_agenda . " agenda, " . $tbl_property . " ip\n WHERE agenda.id = ip.ref AND\n ip.tool='" . TOOL_CALENDAR_EVENT . "' AND\n {$where_condition}\n ip.visibility='1' AND\n agenda.c_id = {$course_id} AND\n ip.c_id = {$course_id} AND\n agenda.session_id = {$session_id} AND\n ip.id_session = {$session_id}\n ";
}
$result = Database::query($sql);
$events = array();
if (Database::num_rows($result)) {
$events_added = array();
while ($row = Database::fetch_array($result, 'ASSOC')) {
//to gather sent_tos
$sql = "SELECT to_user_id, to_group_id\n FROM " . $tbl_property . " ip\n WHERE ip.tool = '" . TOOL_CALENDAR_EVENT . "' AND\n ref = {$row['ref']} AND\n ip.visibility = '1' AND\n ip.c_id = {$course_id}";
$sent_to_result = Database::query($sql);
$user_to_array = array();
$group_to_array = array();
while ($row_send_to = Database::fetch_array($sent_to_result, 'ASSOC')) {
if (!empty($row_send_to['to_group_id'])) {
$group_to_array[] = $row_send_to['to_group_id'];
}
if (!empty($row_send_to['to_user_id'])) {
$user_to_array[] = $row_send_to['to_user_id'];
}
}
//Only show events from the session
/*if (api_get_course_int_id()) {
if ($row['session_id'] != api_get_session_id()) {
continue;
}
}*/
$event = array();
$event['id'] = 'course_' . $row['id'];
//To avoid doubles
if (in_array($row['id'], $events_added)) {
continue;
}
$events_added[] = $row['id'];
$attachment = get_attachment($row['id'], $course_id);
$has_attachment = '';
if (!empty($attachment)) {
$has_attachment = Display::return_icon('attachment.gif', get_lang('Attachment'));
$user_filename = $attachment['filename'];
$full_file_name = 'download.php?file=' . $attachment['path'] . '&course_id=' . $course_id;
$event['attachment'] = $has_attachment . Display::url($user_filename, $full_file_name);
} else {
$event['attachment'] = '';
}
$event['title'] = $row['title'];
$event['className'] = 'course';
$event['allDay'] = 'false';
$event['course_id'] = $course_id;
$event['borderColor'] = $event['backgroundColor'] = $this->event_course_color;
if (isset($row['session_id']) && !empty($row['session_id'])) {
$event['borderColor'] = $event['backgroundColor'] = $this->event_session_color;
}
if (isset($row['to_group_id']) && !empty($row['to_group_id'])) {
$event['borderColor'] = $event['backgroundColor'] = $this->event_group_color;
}
$event['editable'] = false;
if (api_is_allowed_to_edit() && $this->type == 'course') {
$event['editable'] = true;
}
if (!empty($row['start_date']) && $row['start_date'] != '0000-00-00 00:00:00') {
$event['start'] = $this->format_event_date($row['start_date']);
}
if (!empty($row['end_date']) && $row['end_date'] != '0000-00-00 00:00:00') {
$event['end'] = $this->format_event_date($row['end_date']);
}
$event['sent_to'] = '';
//$event['type'] = $this->type;
$event['type'] = 'course';
if ($row['session_id'] != 0) {
$event['type'] = 'session';
}
//Event Sent to a group?
if (isset($row['to_group_id']) && !empty($row['to_group_id'])) {
$sent_to = array();
if (!empty($group_to_array)) {
foreach ($group_to_array as $group_item) {
$sent_to[] = $group_name_list[$group_item];
}
}
$sent_to = implode('@@', $sent_to);
$sent_to = str_replace('@@', '</div><div class="label_tag notice">', $sent_to);
$event['sent_to'] = '<div class="label_tag notice">' . $sent_to . '</div>';
$event['type'] = 'group';
}
//Event sent to a user?
if (isset($row['to_user_id'])) {
$sent_to = array();
if (!empty($user_to_array)) {
foreach ($user_to_array as $item) {
$user_info = api_get_user_info($item);
// add username as tooltip for $event['sent_to'] - ref #4226
$username = api_htmlentities(sprintf(get_lang('LoginX'), $user_info['username']), ENT_QUOTES);
$sent_to[] = "<span title='" . $username . "'>" . $user_info['complete_name'] . "</span>";
}
}
$sent_to = implode('@@', $sent_to);
$sent_to = str_replace('@@', '</div><div class="label_tag notice">', $sent_to);
$event['sent_to'] = '<div class="label_tag notice">' . $sent_to . '</div>';
}
//Event sent to everyone!
if (empty($event['sent_to'])) {
$event['sent_to'] = '<div class="label_tag notice">' . get_lang('Everyone') . '</div>';
}
$event['description'] = $row['content'];
$event['allDay'] = isset($row['all_day']) && $row['all_day'] == 1 ? $row['all_day'] : 0;
$this->events[] = $event;
}
}
return $this->events;
}
/**
* Returns the "what's new" icon notifications
*
* The general logic of this function is to track the last time the user
* entered the course and compare to what has changed inside this course
* since then, based on the item_property table inside this course. Note that,
* if the user never entered the course before, he will not see notification
* icons. This function takes session ID into account (if any) and only shows
* the corresponding notifications.
* @param array Course information array, containing at least elements 'db' and 'k'
* @return string The HTML link to be shown next to the course
*/
public static function show_notification($course_info)
{
$t_track_e_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
$course_tool_table = Database::get_course_table(TABLE_TOOL_LIST);
$tool_edit_table = Database::get_course_table(TABLE_ITEM_PROPERTY);
$course_code = Database::escape_string($course_info['code']);
$user_id = api_get_user_id();
$course_id = $course_info['real_id'];
$course_info['id_session'] = intval($course_info['id_session']);
// Get the user's last access dates to all tools of this course
$sql = "SELECT *\n FROM {$t_track_e_access} USE INDEX (access_cours_code, access_user_id)\n WHERE\n access_cours_code = '" . $course_code . "' AND\n access_user_id = '{$user_id}' AND\n access_session_id ='" . $course_info['id_session'] . "'";
$resLastTrackInCourse = Database::query($sql);
$oldestTrackDate = $oldestTrackDateOrig = '3000-01-01 00:00:00';
while ($lastTrackInCourse = Database::fetch_array($resLastTrackInCourse)) {
$lastTrackInCourseDate[$lastTrackInCourse['access_tool']] = $lastTrackInCourse['access_date'];
if ($oldestTrackDate > $lastTrackInCourse['access_date']) {
$oldestTrackDate = $lastTrackInCourse['access_date'];
}
}
if ($oldestTrackDate == $oldestTrackDateOrig) {
//if there was no connexion to the course ever, then take the
// course creation date as a reference
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$sql = "SELECT course.creation_date " . "FROM {$course_table} course " . "WHERE course.code = '" . $course_code . "'";
$res = Database::query($sql);
if ($res && Database::num_rows($res) > 0) {
$row = Database::fetch_array($res);
}
$oldestTrackDate = $row['creation_date'];
}
// Get the last edits of all tools of this course.
$sql = "SELECT\n tet.*,\n tet.lastedit_date last_date,\n tet.tool tool,\n tet.ref ref,\n tet.lastedit_type type,\n tet.to_group_id group_id,\n ctt.image image,\n ctt.link link\n FROM {$tool_edit_table} tet, {$course_tool_table} ctt\n WHERE\n tet.c_id = {$course_id} AND\n ctt.c_id = {$course_id} AND\n tet.lastedit_date > '{$oldestTrackDate}' " . " AND (ctt.name = tet.tool OR (ctt.name = 'student_publication' AND tet.tool = 'work')) " . " AND ctt.visibility = '1' " . " AND tet.lastedit_user_id != {$user_id} AND tet.id_session = '" . $course_info['id_session'] . "'\n ORDER BY tet.lastedit_date";
$res = Database::query($sql);
// Get the group_id's with user membership.
$group_ids = GroupManager::get_group_ids($course_info['real_id'], $user_id);
$group_ids[] = 0;
//add group 'everyone'
$notifications = array();
// Filter all last edits of all tools of the course
while ($res && ($item_property = Database::fetch_array($res))) {
// First thing to check is if the user never entered the tool
// or if his last visit was earlier than the last modification.
if ((!isset($lastTrackInCourseDate[$item_property['tool']]) || $lastTrackInCourseDate[$item_property['tool']] < $item_property['lastedit_date']) && (in_array($item_property['to_group_id'], $group_ids) && ($item_property['tool'] != TOOL_DROPBOX && $item_property['tool'] != TOOL_NOTEBOOK && $item_property['tool'] != TOOL_CHAT)) && ($item_property['visibility'] == '1' || $course_info['status'] == '1' && $item_property['visibility'] == '0' || !isset($item_property['visibility']))) {
if ($course_info['real_id'] == 1) {
// var_dump($item_property);
}
// Also drop announcements and events that are not for the user or his group.
if (($item_property['tool'] == TOOL_ANNOUNCEMENT || $item_property['tool'] == TOOL_CALENDAR_EVENT) && ($item_property['to_user_id'] != $user_id && (!isset($item_property['to_group_id']) || !in_array($item_property['to_group_id'], $group_ids)))) {
continue;
}
// If it's a survey, make sure the user's invited. Otherwise drop it.
if ($item_property['tool'] == TOOL_SURVEY) {
$survey_info = survey_manager::get_survey($item_property['ref'], 0, $course_code);
if (!empty($survey_info)) {
$invited_users = SurveyUtil::get_invited_users($survey_info['code'], $course_code);
if (!in_array($user_id, $invited_users['course_users'])) {
continue;
}
}
}
// If it's a learning path, ensure it is currently visible to the user
if ($item_property['tool'] == TOOL_LEARNPATH) {
require_once api_get_path(SYS_CODE_PATH) . 'newscorm/learnpath.class.php';
if (!learnpath::is_lp_visible_for_student($item_property['ref'], $user_id, $course_code)) {
continue;
}
}
if ($item_property['tool'] == 'work' && $item_property['type'] == 'DirectoryCreated') {
$item_property['lastedit_type'] = 'WorkAdded';
}
$notifications[$item_property['tool']] = $item_property;
}
}
// Show all tool icons where there is something new.
$retvalue = ' ';
while (list($key, $notification) = each($notifications)) {
$lastDate = date('d/m/Y H:i', convert_sql_date($notification['lastedit_date']));
$type = $notification['lastedit_type'];
if (empty($course_info['id_session'])) {
$my_course['id_session'] = 0;
} else {
$my_course['id_session'] = $course_info['id_session'];
}
$label = get_lang('TitleNotification') . ": " . get_lang($type) . " ({$lastDate})";
$retvalue .= '<a href="' . api_get_path(WEB_CODE_PATH) . $notification['link'] . '?cidReq=' . $course_code . '&ref=' . $notification['ref'] . '&gidReq=' . $notification['to_group_id'] . '&id_session=' . $my_course['id_session'] . '">' . Display::return_icon($notification['image'], $label) . '</a> ';
}
return $retvalue;
}
/**
* Returns the "what's new" icon notifications
*
* The general logic of this function is to track the last time the user
* entered the course and compare to what has changed inside this course
* since then, based on the item_property table inside this course. Note that,
* if the user never entered the course before, he will not see notification
* icons. This function takes session ID into account (if any) and only shows
* the corresponding notifications.
* @param array Course information array, containing at least elements 'db' and 'k'
* @return string The HTML link to be shown next to the course
*/
public static function show_notification($course_info)
{
if (empty($course_info)) {
return '';
}
$t_track_e_access = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
$course_tool_table = Database::get_course_table(TABLE_TOOL_LIST);
$tool_edit_table = Database::get_course_table(TABLE_ITEM_PROPERTY);
$course_code = Database::escape_string($course_info['code']);
$user_id = api_get_user_id();
$course_id = intval($course_info['real_id']);
$sessionId = intval($course_info['id_session']);
// Get the user's last access dates to all tools of this course
$sql = "SELECT *\n FROM {$t_track_e_access}\n WHERE\n c_id = {$course_id} AND\n access_user_id = '{$user_id}' AND\n access_session_id ='" . $sessionId . "'";
$resLastTrackInCourse = Database::query($sql);
$oldestTrackDate = $oldestTrackDateOrig = '3000-01-01 00:00:00';
while ($lastTrackInCourse = Database::fetch_array($resLastTrackInCourse)) {
$lastTrackInCourseDate[$lastTrackInCourse['access_tool']] = $lastTrackInCourse['access_date'];
if ($oldestTrackDate > $lastTrackInCourse['access_date']) {
$oldestTrackDate = $lastTrackInCourse['access_date'];
}
}
if ($oldestTrackDate == $oldestTrackDateOrig) {
//if there was no connexion to the course ever, then take the
// course creation date as a reference
$oldestTrackDate = $course_info['creation_date'];
}
$sessionCondition = api_get_session_condition($sessionId, true, false, 'tet.session_id');
// Get the last edits of all tools of this course.
$sql = "SELECT\n tet.*,\n tet.lastedit_date last_date,\n tet.tool tool,\n tet.ref ref,\n tet.lastedit_type type,\n tet.to_group_id group_id,\n ctt.image image,\n ctt.link link\n FROM {$tool_edit_table} tet\n INNER JOIN {$course_tool_table} ctt\n ON tet.c_id = ctt.c_id\n WHERE\n tet.c_id = {$course_id} AND\n tet.lastedit_date > '{$oldestTrackDate}' " . " AND (ctt.name = tet.tool OR (ctt.name = 'student_publication' AND tet.tool = 'work'))\n AND ctt.visibility = '1'\n AND tet.lastedit_user_id != {$user_id} {$sessionCondition}\n ORDER BY tet.lastedit_date";
$res = Database::query($sql);
// Get the group_id's with user membership.
$group_ids = GroupManager::get_group_ids($course_info['real_id'], $user_id);
$group_ids[] = 0;
//add group 'everyone'
$notifications = array();
// Filter all last edits of all tools of the course
while ($res && ($item_property = Database::fetch_array($res, 'ASSOC'))) {
// First thing to check is if the user never entered the tool
// or if his last visit was earlier than the last modification.
if ((!isset($lastTrackInCourseDate[$item_property['tool']]) || $lastTrackInCourseDate[$item_property['tool']] < $item_property['lastedit_date']) && (in_array($item_property['to_group_id'], $group_ids) && ($item_property['tool'] != TOOL_NOTEBOOK && $item_property['tool'] != TOOL_CHAT)) && ($item_property['visibility'] == '1' || $course_info['status'] == '1' && $item_property['visibility'] == '0' || !isset($item_property['visibility']))) {
// Also drop announcements and events that are not for the user or his group.
if (($item_property['tool'] == TOOL_ANNOUNCEMENT || $item_property['tool'] == TOOL_CALENDAR_EVENT) && ($item_property['to_user_id'] != $user_id && (!isset($item_property['to_group_id']) || !in_array($item_property['to_group_id'], $group_ids)))) {
continue;
}
// If it's a survey, make sure the user's invited. Otherwise drop it.
if ($item_property['tool'] == TOOL_SURVEY) {
$survey_info = SurveyManager::get_survey($item_property['ref'], 0, $course_code);
if (!empty($survey_info)) {
$invited_users = SurveyUtil::get_invited_users($survey_info['code'], $course_code);
if (!in_array($user_id, $invited_users['course_users'])) {
continue;
}
}
}
// If it's a learning path, ensure it is currently visible to the user
if ($item_property['tool'] == TOOL_LEARNPATH) {
if (!learnpath::is_lp_visible_for_student($item_property['ref'], $user_id, $course_code)) {
continue;
}
}
if ($item_property['tool'] == TOOL_DROPBOX) {
$item_property['link'] = 'dropbox/dropbox_download.php?id=' . $item_property['ref'];
}
if ($item_property['tool'] == 'work' && $item_property['type'] == 'DirectoryCreated') {
$item_property['lastedit_type'] = 'WorkAdded';
}
$notifications[$item_property['tool']] = $item_property;
}
}
// Show all tool icons where there is something new.
$return = ' ';
foreach ($notifications as $notification) {
$lastDate = date('d/m/Y H:i', convert_sql_date($notification['lastedit_date']));
$type = $notification['lastedit_type'];
$label = get_lang('TitleNotification') . ": " . get_lang($type) . " ({$lastDate})";
if (strpos($notification['link'], '?') === false) {
$notification['link'] = $notification['link'] . '?notification=1';
} else {
$notification['link'] = $notification['link'] . '¬ification=1';
}
$return .= Display::url(Display::return_icon($notification['image'], $label), api_get_path(WEB_CODE_PATH) . $notification['link'] . '&cidReq=' . $course_code . '&ref=' . $notification['ref'] . '&gidReq=' . $notification['to_group_id'] . '&id_session=' . $sessionId) . ' ';
}
return $return;
}
* Announcements list display
*/
$course_id = api_get_course_int_id();
//if ($display_announcement_list && !$surveyid) {
if ($display_announcement_list) {
// by default we use the id of the current user. The course administrator can see the announcement of other users by using the user / group filter
//$user_id=$_user['user_id'];
if (isset($_SESSION['user'])) {
//$user_id=$_SESSION['user'];
}
$user_id = api_get_user_id();
if (isset($_SESSION['group'])) {
//$group_id=$_SESSION['group'];
}
$group_id = api_get_group_id();
$group_memberships = GroupManager::get_group_ids($course_id, api_get_user_id());
//$is_group_member = GroupManager :: is_tutor(api_get_user_id());
if (api_is_allowed_to_edit(false, true) or api_get_course_setting('allow_user_edit_announcement') && !api_is_anonymous()) {
// A.1. you are a course admin with a USER filter
// => see only the messages of this specific user + the messages of the group (s)he is member of.
if (!empty($_SESSION['user'])) {
if (is_array($group_memberships) && count($group_memberships) > 0) {
$sql = "SELECT announcement.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.insert_date\n\t\t\t\t\tFROM {$tbl_announcement} announcement, {$tbl_item_property} ip\n\t\t\t\t\tWHERE \tannouncement.c_id = {$course_id} AND\n\t\t\t\t\t\t\tip.c_id = {$course_id} AND\n\t\t\t\t\t\t\tannouncement.id = ip.ref AND\n\t\t\t\t\t\t\tip.tool\t\t\t= 'announcement' AND\n\t\t\t\t\t\t\t(ip.to_user_id={$user_id} OR ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") )\n\t\t\t\t\t\t\t{$condition_session}\n\n\t\t\t\t\tORDER BY display_order DESC";
} else {
$sql = "SELECT announcement.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.insert_date\n\t\t\t\t\tFROM {$tbl_announcement} announcement, {$tbl_item_property} ip\n\t\t\t\t\tWHERE\tannouncement.c_id \t= {$course_id} AND\n\t\t\t\t\t\t\tip.c_id \t\t\t= {$course_id} AND\n\t\t\t\t\t\t\tannouncement.id \t= ip.ref AND\n\t\t\t\t\t\t\tip.tool\t\t\t\t='announcement' AND\n\t\t\t\t\t\t\t(ip.to_user_id\t\t= {$user_id} OR ip.to_group_id='0') AND\n\t\t\t\t\t\t\tip.visibility='1'\n\t\t\t\t\t{$condition_session}\n\t\t\t\t\tORDER BY display_order DESC";
}
} elseif (api_get_group_id() != 0) {
// A.2. you are a course admin with a GROUP filter
// => see only the messages of this specific group
$sql = "SELECT announcement.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.insert_date\n\t\t\t\tFROM {$tbl_announcement} announcement, {$tbl_item_property} ip\n\t\t\t\tWHERE\tannouncement.c_id = {$course_id} AND\n\t\t\t\t\t\tip.c_id = {$course_id} AND\n\t\t\t\t\t\tannouncement.id = ip.ref\n\t\t\t\t\t\tAND ip.tool='announcement'\n\t\t\t\t\t\tAND ip.visibility<>'2'\n\t\t\t\t\t\tAND (ip.to_group_id={$group_id} OR ip.to_group_id='0')\n\t\t\t\t\t\t{$condition_session}\n\t\t\t\tGROUP BY ip.ref\n\t\t\t\tORDER BY display_order DESC";
} else {
/**
* @return int
*/
public static function getNumberAnnouncements()
{
// Maximum title messages to display
$maximum = '12';
// Database Table Definitions
$tbl_announcement = Database::get_course_table(TABLE_ANNOUNCEMENT);
$tbl_item_property = Database::get_course_table(TABLE_ITEM_PROPERTY);
$session_id = api_get_session_id();
$_course = api_get_course_info();
$course_id = $_course['real_id'];
$userId = api_get_user_id();
$condition_session = api_get_session_condition($session_id, true, true, 'announcement.session_id');
if (api_is_allowed_to_edit(false, true)) {
// check teacher status
if (empty($_GET['origin']) or $_GET['origin'] !== 'learnpath') {
if (api_get_group_id() == 0) {
$group_condition = "";
} else {
$group_condition = " AND (ip.to_group_id='" . api_get_group_id() . "' OR ip.to_group_id = 0 OR ip.to_group_id IS NULL)";
}
$sql = "SELECT announcement.*, ip.visibility, ip.to_group_id, ip.insert_user_id\n\t\t\t\tFROM {$tbl_announcement} announcement, {$tbl_item_property} ip\n\t\t\t\tWHERE\n\t\t\t\t announcement.c_id = {$course_id} AND\n ip.c_id = {$course_id} AND\n announcement.id = ip.ref AND\n ip.tool = 'announcement' AND\n ip.visibility <> '2'\n {$group_condition}\n {$condition_session}\n\t\t\t\tGROUP BY ip.ref\n\t\t\t\tORDER BY display_order DESC\n\t\t\t\tLIMIT 0,{$maximum}";
}
} else {
// students only get to see the visible announcements
if (empty($_GET['origin']) or $_GET['origin'] !== 'learnpath') {
$group_memberships = GroupManager::get_group_ids($_course['real_id'], $userId);
if (api_get_course_setting('allow_user_edit_announcement') && !api_is_anonymous()) {
if (api_get_group_id() == 0) {
$cond_user_id = " AND (\n ip.lastedit_user_id = '" . $userId . "' OR (\n ip.to_user_id='" . $userId . "' OR\n ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") OR\n ip.to_group_id IS NULL\n )\n )\n ";
} else {
$cond_user_id = " AND (\n ip.lastedit_user_id = '" . $userId . "'OR\n ip.to_group_id IN (0, " . api_get_group_id() . ") OR\n ip.to_group_id IS NULL\n )";
}
} else {
if (api_get_group_id() == 0) {
$cond_user_id = " AND (\n ip.to_user_id='" . $userId . "' OR\n ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") OR\n ip.to_group_id IS NULL\n ) ";
} else {
$cond_user_id = " AND (\n ip.to_user_id='" . $userId . "' OR\n ip.to_group_id IN (0, " . api_get_group_id() . ") OR\n ip.to_group_id IS NULL\n ) ";
}
}
// the user is member of several groups => display personal announcements AND his group announcements AND the general announcements
if (is_array($group_memberships) && count($group_memberships) > 0) {
$sql = "SELECT announcement.*, ip.visibility, ip.to_group_id, ip.insert_user_id\n FROM {$tbl_announcement} announcement, {$tbl_item_property} ip\n WHERE\n announcement.c_id = {$course_id} AND\n ip.c_id = {$course_id} AND\n announcement.id = ip.ref AND\n ip.tool='announcement'\n AND ip.visibility='1'\n {$cond_user_id}\n {$condition_session}\n GROUP BY ip.ref\n ORDER BY display_order DESC\n LIMIT 0, {$maximum}";
} else {
// the user is not member of any group
// this is an identified user => show the general announcements AND his personal announcements
if ($userId) {
if (api_get_course_setting('allow_user_edit_announcement') && !api_is_anonymous()) {
$cond_user_id = " AND (\n ip.lastedit_user_id = '" . $userId . "' OR\n ( ip.to_user_id='" . $userId . "' OR ip.to_group_id='0' OR ip.to_group_id IS NULL)\n ) ";
} else {
$cond_user_id = " AND ( ip.to_user_id='" . $userId . "' OR ip.to_group_id='0' OR ip.to_group_id IS NULL) ";
}
$sql = "SELECT announcement.*, ip.visibility, ip.to_group_id, ip.insert_user_id\n FROM {$tbl_announcement} announcement, {$tbl_item_property} ip\n WHERE\n announcement.c_id = {$course_id} AND\n ip.c_id = {$course_id} AND\n announcement.id = ip.ref\n AND ip.tool='announcement'\n AND ip.visibility='1'\n {$cond_user_id}\n {$condition_session}\n GROUP BY ip.ref\n ORDER BY display_order DESC\n LIMIT 0, {$maximum}";
} else {
if (api_get_course_setting('allow_user_edit_announcement')) {
$cond_user_id = " AND (\n ip.lastedit_user_id = '" . api_get_user_id() . "' OR ip.to_group_id='0' OR ip.to_group_id IS NULL\n ) ";
} else {
$cond_user_id = " AND ip.to_group_id='0' ";
}
// the user is not identiefied => show only the general announcements
$sql = "SELECT announcement.*, ip.visibility, ip.to_group_id, ip.insert_user_id\n FROM {$tbl_announcement} announcement, {$tbl_item_property} ip\n WHERE\n announcement.c_id = {$course_id} AND\n ip.c_id = {$course_id} AND\n announcement.id = ip.ref\n AND ip.tool='announcement'\n AND ip.visibility='1'\n AND ip.to_group_id='0'\n {$condition_session}\n GROUP BY ip.ref\n ORDER BY display_order DESC\n LIMIT 0, {$maximum}";
}
}
}
}
$result = Database::query($sql);
return Database::num_rows($result);
}
/**
* @param int $start
* @param int $end
* @param array $courseInfo
* @param int $groupId
* @param int $session_id
* @param int $user_id
* @return array
*/
public function get_course_events($start, $end, $courseInfo, $groupId = 0, $session_id = 0, $user_id = 0)
{
$start = isset($start) && !empty($start) ? api_get_utc_datetime(intval($start)) : null;
$end = isset($end) && !empty($end) ? api_get_utc_datetime(intval($end)) : null;
if (empty($courseInfo)) {
return array();
}
$course_id = $courseInfo['real_id'];
if (empty($course_id)) {
return array();
}
$user_id = intval($user_id);
$groupList = GroupManager::get_group_list(null, $courseInfo['code']);
$group_name_list = array();
if (!empty($groupList)) {
foreach ($groupList as $group) {
$group_name_list[$group['id']] = $group['name'];
}
}
if (!api_is_allowed_to_edit()) {
$group_memberships = GroupManager::get_group_ids($course_id, api_get_user_id());
$user_id = api_get_user_id();
} else {
$group_memberships = GroupManager::get_group_ids($course_id, $user_id);
}
$tlb_course_agenda = Database::get_course_table(TABLE_AGENDA);
$tbl_property = Database::get_course_table(TABLE_ITEM_PROPERTY);
if (!empty($groupId)) {
$group_memberships = array($groupId);
}
$session_id = intval($session_id);
if (is_array($group_memberships) && count($group_memberships) > 0) {
if (api_is_allowed_to_edit()) {
if (!empty($groupId)) {
$where_condition = "( ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") ) ";
} else {
if (!empty($user_id)) {
$where_condition = "( ip.to_user_id = {$user_id} OR ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") ) ";
} else {
$where_condition = "( ip.to_group_id is null OR ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") ) ";
}
}
} else {
$where_condition = "( ip.to_user_id = {$user_id} OR ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") ) ";
}
$sql = "SELECT DISTINCT agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref, to_user_id\n FROM {$tlb_course_agenda} agenda INNER JOIN {$tbl_property} ip\n ON (agenda.id = ip.ref AND agenda.c_id = ip.c_id)\n WHERE\n ip.tool ='" . TOOL_CALENDAR_EVENT . "' AND\n {$where_condition} AND\n ip.visibility = '1' AND\n agenda.c_id = {$course_id} AND\n ip.c_id = {$course_id}\n ";
} else {
$visibilityCondition = "ip.visibility='1' AND";
if (api_is_allowed_to_edit()) {
if ($user_id == 0) {
$where_condition = "";
} else {
$where_condition = "( ip.to_user_id=" . $user_id . ") AND ";
}
$visibilityCondition = " (ip.visibility IN ('1', '0')) AND ";
} else {
$where_condition = "( ip.to_user_id={$user_id} OR ip.to_group_id='0') AND ";
}
$sql = "SELECT DISTINCT agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref, to_user_id\n FROM {$tlb_course_agenda} agenda INNER JOIN {$tbl_property} ip\n ON (agenda.id = ip.ref AND agenda.c_id = ip.c_id)\n WHERE\n ip.tool='" . TOOL_CALENDAR_EVENT . "' AND\n {$where_condition}\n {$visibilityCondition}\n agenda.c_id = {$course_id} AND\n ip.c_id = {$course_id} AND\n agenda.session_id = {$session_id} AND\n ip.id_session = {$session_id}\n ";
}
$dateCondition = null;
if (!empty($start) && !empty($end)) {
$dateCondition .= "AND (\n (agenda.start_date >= '" . $start . "' OR agenda.start_date IS NULL) AND\n (agenda.end_date <= '" . $end . "' OR agenda.end_date IS NULL)\n )";
}
$sql .= $dateCondition;
$result = Database::query($sql);
if (Database::num_rows($result)) {
$events_added = array();
while ($row = Database::fetch_array($result, 'ASSOC')) {
$eventId = $row['ref'];
$items = $this->getUsersAndGroupSubscribedToEvent($eventId, $course_id, $this->sessionId);
$group_to_array = $items['groups'];
$user_to_array = $items['users'];
$event = array();
$event['id'] = 'course_' . $row['id'];
// To avoid doubles
if (in_array($row['id'], $events_added)) {
continue;
}
$events_added[] = $row['id'];
$attachment = $this->getAttachment($row['id'], $courseInfo);
if (!empty($attachment)) {
$has_attachment = Display::return_icon('attachment.gif', get_lang('Attachment'));
$user_filename = $attachment['filename'];
$url = api_get_path(WEB_CODE_PATH) . 'calendar/download.php?file=' . $attachment['path'] . '&course_id=' . $course_id . '&' . api_get_cidreq();
$event['attachment'] = $has_attachment . Display::url($user_filename, $url);
} else {
$event['attachment'] = '';
}
$event['title'] = $row['title'];
$event['className'] = 'course';
$event['allDay'] = 'false';
$event['course_id'] = $course_id;
$event['borderColor'] = $event['backgroundColor'] = $this->event_course_color;
if (isset($row['session_id']) && !empty($row['session_id'])) {
$event['borderColor'] = $event['backgroundColor'] = $this->event_session_color;
}
if (isset($row['to_group_id']) && !empty($row['to_group_id'])) {
$event['borderColor'] = $event['backgroundColor'] = $this->event_group_color;
}
$event['editable'] = false;
if (api_is_allowed_to_edit() && $this->type == 'course') {
$event['editable'] = true;
}
if (!empty($row['start_date']) && $row['start_date'] != '0000-00-00 00:00:00') {
$event['start'] = $this->format_event_date($row['start_date']);
$event['start_date_localtime'] = api_get_local_time($row['start_date']);
}
if (!empty($row['end_date']) && $row['end_date'] != '0000-00-00 00:00:00') {
$event['end'] = $this->format_event_date($row['end_date']);
$event['end_date_localtime'] = api_get_local_time($row['end_date']);
}
$event['sent_to'] = '';
$event['type'] = 'course';
if ($row['session_id'] != 0) {
$event['type'] = 'session';
}
// Event Sent to a group?
if (isset($row['to_group_id']) && !empty($row['to_group_id'])) {
$sent_to = array();
if (!empty($group_to_array)) {
foreach ($group_to_array as $group_item) {
$sent_to[] = $group_name_list[$group_item];
}
}
$sent_to = implode('@@', $sent_to);
$sent_to = str_replace('@@', '</div><div class="label_tag notice">', $sent_to);
$event['sent_to'] = '<div class="label_tag notice">' . $sent_to . '</div>';
$event['type'] = 'group';
}
//Event sent to a user?
if (isset($row['to_user_id'])) {
$sent_to = array();
if (!empty($user_to_array)) {
foreach ($user_to_array as $item) {
$user_info = api_get_user_info($item);
// Add username as tooltip for $event['sent_to'] - ref #4226
$username = api_htmlentities(sprintf(get_lang('LoginX'), $user_info['username']), ENT_QUOTES);
$sent_to[] = "<span title='" . $username . "'>" . $user_info['complete_name'] . "</span>";
}
}
$sent_to = implode('@@', $sent_to);
$sent_to = str_replace('@@', '</div><div class="label_tag notice">', $sent_to);
$event['sent_to'] = '<div class="label_tag notice">' . $sent_to . '</div>';
}
//Event sent to everyone!
if (empty($event['sent_to'])) {
$event['sent_to'] = '<div class="label_tag notice">' . get_lang('Everyone') . '</div>';
}
$event['description'] = $row['content'];
$event['visibility'] = $row['visibility'];
$event['real_id'] = $row['id'];
$event['allDay'] = isset($row['all_day']) && $row['all_day'] == 1 ? $row['all_day'] : 0;
$event['parent_event_id'] = $row['parent_event_id'];
$event['has_children'] = $this->hasChildren($row['id'], $course_id) ? 1 : 0;
$this->events[] = $event;
}
}
return $this->events;
}
/**
* Return agenda items of the week
* @deprecated
*/
function get_week_agendaitems($courses_dbs, $month, $year, $week = '')
{
global $setting_agenda_link;
$TABLEAGENDA = Database::get_course_table(TABLE_AGENDA);
$TABLE_ITEMPROPERTY = Database::get_course_table(TABLE_ITEM_PROPERTY);
$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'] . " 00:00:00";
$start_filter = api_get_utc_datetime($start_filter);
$end_filter = $start_end['end']['year'] . "-" . $start_end['end']['month'] . "-" . $start_end['end']['day'] . " 23:59:59";
$end_filter = api_get_utc_datetime($end_filter);
// get agenda-items for every course
foreach ($courses_dbs as $key => $array_course_info) {
//databases of the courses
$TABLEAGENDA = Database::get_course_table(TABLE_AGENDA);
$TABLE_ITEMPROPERTY = Database::get_course_table(TABLE_ITEM_PROPERTY);
// getting all the groups of the user for the current course
$group_memberships = GroupManager::get_group_ids($array_course_info["real_id"], api_get_user_id());
$user_course_status = CourseManager::get_user_in_course_status(api_get_user_id(), $array_course_info["code"]);
// if the user is administrator of that course we show all the agenda items
if ($user_course_status == '1') {
//echo "course admin";
$sqlquery = "SELECT\n\t\t\t\t\t\t\tDISTINCT a.*, i.*\n\t\t\t\t\t\t\tFROM " . $TABLEAGENDA . " a,\n\t\t\t\t\t\t\t\t" . $TABLE_ITEMPROPERTY . " i\n\t\t\t\t\t\t\tWHERE a.id = i.ref\n\t\t\t\t\t\t\tAND a.start_date>='" . $start_filter . "' AND a.start_date<='" . $end_filter . "'\n\t\t\t\t\t\t\tAND i.tool='" . TOOL_CALENDAR_EVENT . "'\n\t\t\t\t\t\t\tAND i.visibility='1'\n\t\t\t\t\t\t\tGROUP BY a.id\n\t\t\t\t\t\t\tORDER BY a.start_date";
} else {
//echo "GEEN course admin";
if (is_array($group_memberships) && count($group_memberships) > 0) {
$sqlquery = "SELECT\n\t\t\t\t\t\t\t\t\ta.*, i.*\n\t\t\t\t\t\t\t\t\tFROM " . $TABLEAGENDA . " a,\n\t\t\t\t\t\t\t\t\t\t " . $TABLE_ITEMPROPERTY . " i\n\t\t\t\t\t\t\t\t\tWHERE a.id = i.ref\n\t\t\t\t\t\t\t\t\tAND a.start_date>='" . $start_filter . "' AND a.start_date<='" . $end_filter . "'\n\t\t\t\t\t\t\t\t\tAND i.tool='" . TOOL_CALENDAR_EVENT . "'\n\t\t\t\t\t\t\t\t\tAND\t( i.to_user_id='" . api_get_user_id() . "' OR i.to_group_id IN (0, " . implode(", ", $group_memberships) . ") )\n\t\t\t\t\t\t\t\t\tAND i.visibility='1'\n\t\t\t\t\t\t\t\t\tORDER BY a.start_date";
} else {
$sqlquery = "SELECT\n\t\t\t\t\t\t\t\t\ta.*, i.*\n\t\t\t\t\t\t\t\t\tFROM " . $TABLEAGENDA . " a,\n\t\t\t\t\t\t\t\t\t\t " . $TABLE_ITEMPROPERTY . " i\n\t\t\t\t\t\t\t\t\tWHERE a.id = i.ref\n\t\t\t\t\t\t\t\t\tAND a.start_date>='" . $start_filter . "' AND a.start_date<='" . $end_filter . "'\n\t\t\t\t\t\t\t\t\tAND i.tool='" . TOOL_CALENDAR_EVENT . "'\n\t\t\t\t\t\t\t\t\tAND ( i.to_user_id='" . api_get_user_id() . "' OR i.to_group_id='0')\n\t\t\t\t\t\t\t\t\tAND i.visibility='1'\n\t\t\t\t\t\t\t\t\tORDER BY a.start_date";
}
}
$result = Database::query($sqlquery);
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);
$start_time = api_convert_and_format_date($item['start_date'], TIME_NO_SEC_FORMAT);
$end_time = api_convert_and_format_date($item['end_date'], DATE_TIME_FORMAT_LONG);
if ($setting_agenda_link == 'coursecode') {
$title = $array_course_info['title'];
$agenda_link = cut($title, 14, true);
} else {
$agenda_link = Display::return_icon('course_home.png', ' ', '', ICON_SIZE_SMALL);
}
$URL = api_get_path(WEB_CODE_PATH) . "calendar/agenda.php?cidReq=" . urlencode($array_course_info["code"]) . "&day={$agendaday}&month={$month}&year={$year}#{$agendaday}";
// RH //Patrick Cool: to highlight the relevant agenda item
//Display the events in agenda
$content = "<i>{$start_time} - {$end_time}</i> <a href=\"{$URL}\" title=\"" . $array_course_info["title"] . "\"> <br />" . $agenda_link . "</a>";
$content .= "<div>" . $item['title'] . "</div><br>";
$items[$agendaday][$item['start_date']] .= $content;
}
}
$agendaitems = array();
// sorting by hour for every day
while (list($agendaday, $tmpitems) = each($items)) {
sort($tmpitems);
while (list($key, $val) = each($tmpitems)) {
$agendaitems[$agendaday] .= $val;
}
}
return $agendaitems;
}
/**
* Displays one specific announcement
* @param int $announcement_id, the id of the announcement you want to display
*/
public static function display_announcement($announcement_id)
{
if ($announcement_id != strval(intval($announcement_id))) {
return false;
}
global $charset;
$tbl_announcement = Database::get_course_table(TABLE_ANNOUNCEMENT);
$tbl_item_property = Database::get_course_table(TABLE_ITEM_PROPERTY);
$course_id = api_get_course_int_id();
if (api_is_allowed_to_edit(false, true) || api_get_course_setting('allow_user_edit_announcement') && !api_is_anonymous()) {
$sql_query = " SELECT announcement.*, toolitemproperties.*\n FROM {$tbl_announcement} announcement, {$tbl_item_property} toolitemproperties\n WHERE announcement.id = toolitemproperties.ref\n AND announcement.id = '{$announcement_id}'\n AND toolitemproperties.tool='announcement' AND\n announcement.c_id = {$course_id} AND\n\t\t\t\t\t\t\ttoolitemproperties.c_id = {$course_id}\n ORDER BY display_order DESC";
} else {
$group_list = GroupManager::get_group_ids($course_id, api_get_user_id());
if (empty($group_list)) {
$group_list[] = 0;
}
if (api_get_user_id() != 0) {
$sql_query = "\tSELECT announcement.*, toolitemproperties.*\n \t\t\t\t\t\t\tFROM {$tbl_announcement} announcement, {$tbl_item_property} toolitemproperties\n \t\t\t\t\t\t\tWHERE announcement.id = toolitemproperties.ref\n \t\t\t\t\t\t\tAND announcement.id = '{$announcement_id}'\n \t\t\t\t\t\t\tAND toolitemproperties.tool='announcement'\n \t\t\t\t\t\t\tAND (toolitemproperties.to_user_id='" . api_get_user_id() . "' OR toolitemproperties.to_group_id IN ('0', '" . implode("', '", $group_list) . "'))\n \t\t\t\t\t\t\tAND toolitemproperties.visibility='1' AND\n \t\t\t\t\t\t\tannouncement.c_id = {$course_id} AND\n\t\t\t\t\t\t\t\ttoolitemproperties.c_id = {$course_id}\n \t\t\t\t\t\t\tORDER BY display_order DESC";
} else {
$sql_query = "\tSELECT announcement.*, toolitemproperties.*\n \t\t\t\t\t\t\tFROM {$tbl_announcement} announcement, {$tbl_item_property} toolitemproperties\n \t\t\t\t\t\t\tWHERE announcement.id = toolitemproperties.ref\n \t\t\t\t\t\t\tAND announcement.id = '{$announcement_id}'\n \t\t\t\t\t\t\tAND toolitemproperties.tool='announcement'\n \t\t\t\t\t\t\tAND toolitemproperties.to_group_id='0'\n \t\t\t\t\t\t\tAND toolitemproperties.visibility='1' AND\n \t\t\t\t\t\t\tannouncement.c_id = {$course_id} AND\n\t\t\t\t\t\t\t\ttoolitemproperties.c_id = {$course_id}\n \t\t\t\t\t\t\t";
}
}
$sql_result = Database::query($sql_query);
if (Database::num_rows($sql_result) > 0) {
$result = Database::fetch_array($sql_result, 'ASSOC');
$title = $result['title'];
$content = $result['content'];
echo "<table height=\"100\" width=\"100%\" cellpadding=\"5\" cellspacing=\"0\" class=\"data_table\">";
echo "<tr><td><h2>" . $title . "</h2></td></tr>";
if (api_is_allowed_to_edit(false, true) || api_get_course_setting('allow_user_edit_announcement') && !api_is_anonymous()) {
$modify_icons = "<a href=\"" . api_get_self() . "?" . api_get_cidreq() . "&action=modify&id=" . $announcement_id . "\">" . Display::return_icon('edit.png', get_lang('Edit'), '', ICON_SIZE_SMALL) . "</a>";
if ($result['visibility'] == 1) {
$image_visibility = "visible";
$alt_visibility = get_lang('Hide');
} else {
$image_visibility = "invisible";
$alt_visibility = get_lang('Visible');
}
global $stok;
$modify_icons .= "<a href=\"" . api_get_self() . "?" . api_get_cidreq() . "&origin=" . (!empty($_GET['origin']) ? Security::remove_XSS($_GET['origin']) : '') . "&action=showhide&id=" . $announcement_id . "&sec_token=" . $stok . "\">" . Display::return_icon($image_visibility . '.png', $alt_visibility, '', ICON_SIZE_SMALL) . "</a>";
if (api_is_allowed_to_edit(false, true)) {
$modify_icons .= "<a href=\"" . api_get_self() . "?" . api_get_cidreq() . "&action=delete&id=" . $announcement_id . "&sec_token=" . $stok . "\" onclick=\"javascript:if(!confirm('" . addslashes(api_htmlentities(get_lang('ConfirmYourChoice'), ENT_QUOTES, $charset)) . "')) return false;\">" . Display::return_icon('delete.png', get_lang('Delete'), '', ICON_SIZE_SMALL) . "</a>";
}
echo "<tr><th style='text-align:right'>{$modify_icons}</th></tr>";
}
$content = self::parse_content($result['to_user_id'], $content, api_get_course_id(), api_get_session_id());
echo "<tr><td>{$content}</td></tr>";
echo "<tr><td class=\"announcements_datum\">" . get_lang('LastUpdateDate') . " : " . api_convert_and_format_date($result['insert_date'], DATE_TIME_FORMAT_LONG) . "</td></tr>";
// User or group icon
$sent_to_icon = '';
if ($result['to_group_id'] !== '0' and $result['to_group_id'] !== 'NULL') {
$sent_to_icon = Display::return_icon('group.gif', get_lang('AnnounceSentToUserSelection'));
}
$sent_to = self::sent_to('announcement', $announcement_id);
$sent_to_form = self::sent_to_form($sent_to);
echo Display::tag('td', get_lang('SentTo') . ' : ' . $sent_to_form, array('class' => 'announcements_datum'));
$attachment_list = self::get_attachment($announcement_id);
if (count($attachment_list) > 0) {
echo "<tr><td>";
$realname = $attachment_list['path'];
$user_filename = $attachment_list['filename'];
$full_file_name = 'download.php?' . api_get_cidreq() . '&file=' . $realname;
echo '<br/>';
echo Display::return_icon('attachment.gif', get_lang('Attachment'));
echo '<a href="' . $full_file_name . ' "> ' . $user_filename . ' </a>';
echo ' - <span class="forum_attach_comment" >' . $attachment_list['comment'] . '</span>';
if (api_is_allowed_to_edit(false, true)) {
echo Display::url(Display::return_icon('delete.png', get_lang('Delete'), '', 16), api_get_self() . "?" . api_get_cidreq() . "&action=delete_attachment&id_attach=" . $attachment_list['id'] . "&sec_token=" . $stok);
}
echo '</td></tr>';
}
echo "</table>";
} else {
return false;
}
}
