$filter .= ' AND level=' . $exerciseLevel . ' ';
}
if (isset($answerType) && $answerType > 0) {
$filter .= ' AND qu.type=' . $answerType . ' ';
}
if (!empty($session_id) && $session_id != '-1') {
$mainQuestionList = array();
if (!empty($course_list)) {
foreach ($course_list as $course_item) {
$courseItemId = $course_item['real_id'];
if (!empty($selected_course) && $selected_course != '-1') {
if ($selected_course != $courseItemId) {
continue;
}
}
$exerciseList = get_all_exercises($course_item, $session_id);
if (!empty($exerciseList)) {
foreach ($exerciseList as $exercise) {
$my_exercise = new Exercise($courseItemId);
$my_exercise->read($exercise['id']);
if (!empty($my_exercise)) {
if (!empty($my_exercise->questionList)) {
foreach ($my_exercise->questionList as $question_id) {
$question_obj = Question::read($question_id, $courseItemId);
if ($exerciseLevel != '-1') {
if ($exerciseLevel != $question_obj->level) {
continue;
}
}
if ($answerType > 0) {
if ($answerType != $question_obj->type) {
while ($user = Database::fetch_assoc($result)) {
$data[] = array('id' => $user['id'], 'text' => $user['username'] . ' (' . $user['firstname'] . ' ' . $user['lastname'] . ')');
}
if (!empty($data)) {
echo json_encode($data);
} else {
echo json_encode(array());
}
}
break;
case 'search_exercise_by_course':
if (api_is_platform_admin()) {
$course = api_get_course_info_by_id($_GET['course_id']);
require_once api_get_path(SYS_CODE_PATH) . 'exercice/exercise.lib.php';
$session_id = !empty($_GET['session_id']) ? intval($_GET['session_id']) : 0;
$exercises = get_all_exercises($course, $session_id, false, $_GET['q'], true, 3);
foreach ($exercises as $exercise) {
$data[] = array('id' => $exercise['id'], 'text' => html_entity_decode($exercise['title']));
}
if (!empty($data)) {
$data[] = array('id' => 'T', 'text' => 'TODOS');
echo json_encode($data);
} else {
echo json_encode(array(array('id' => 'T', 'text' => 'TODOS')));
}
}
break;
case 'search_survey_by_course':
if (api_is_platform_admin()) {
$survey = Database::get_course_table(TABLE_SURVEY);
$sql = "SELECT survey_id as id, title, anonymous\n FROM {$survey}\n WHERE\n c_id = %d AND\n session_id = %d AND\n title LIKE '%s'";
/**
* @param string $course_code
* @param int $session_id
*
* @return array
*/
function get_exercises_to_be_taken($course_code, $session_id)
{
$course_info = api_get_course_info($course_code);
$exercises = get_all_exercises($course_info, $session_id);
$result = array();
$now = time() + 15 * 24 * 60 * 60;
foreach ($exercises as $exercise_item) {
if (isset($exercise_item['end_time']) && !empty($exercise_item['end_time']) && $exercise_item['end_time'] != '0000-00-00 00:00:00' && api_strtotime($exercise_item['end_time'], 'UTC') < $now) {
$result[] = $exercise_item;
}
}
return $result;
}
/**
* Get data for users list in sortable with pagination
* @param $from
* @param $number_of_items
* @param $column
* @param $direction
* @return array
*/
public static function get_user_data($from, $number_of_items, $column, $direction)
{
global $user_ids, $course_code, $additional_user_profile_info, $export_csv, $is_western_name_order, $csv_content, $session_id;
$course_code = Database::escape_string($course_code);
$tbl_user = Database::get_main_table(TABLE_MAIN_USER);
$tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
$access_url_id = api_get_current_access_url_id();
// get all users data from a course for sortable with limit
if (is_array($user_ids)) {
$user_ids = array_map('intval', $user_ids);
$condition_user = "******".implode(',',$user_ids).") ";
} else {
$user_ids = intval($user_ids);
$condition_user = "******";
}
if (!empty($_GET['user_keyword'])) {
$keyword = trim(Database::escape_string($_GET['user_keyword']));
$condition_user .= " AND (
user.firstname LIKE '%".$keyword."%' OR
user.lastname LIKE '%".$keyword."%' OR
user.username LIKE '%".$keyword."%' OR
user.email LIKE '%".$keyword."%'
) ";
}
$url_table = null;
$url_condition = null;
if (api_is_multiple_url_enabled()) {
$url_table = ", ".$tbl_url_rel_user."as url_users";
$url_condition = " AND user.user_id = url_users.user_id AND access_url_id='$access_url_id'";
}
$sql = "SELECT user.user_id as user_id,
user.official_code as col0,
user.lastname as col1,
user.firstname as col2,
user.username as col3
FROM $tbl_user as user $url_table
$condition_user $url_condition";
if (!in_array($direction, array('ASC','DESC'))) {
$direction = 'ASC';
}
$column = intval($column);
$from = intval($from);
$number_of_items = intval($number_of_items);
$sql .= " ORDER BY col$column $direction ";
$sql .= " LIMIT $from,$number_of_items";
$res = Database::query($sql);
$users = array();
$course_info = api_get_course_info($course_code);
$total_surveys = 0;
$total_exercises = get_all_exercises(
$course_info,
$session_id,
false,
null,
false,
3
);
if (empty($session_id)) {
$survey_user_list = array();
$survey_list = survey_manager::get_surveys($course_code, $session_id);
$total_surveys = count($survey_list);
foreach ($survey_list as $survey) {
$user_list = survey_manager::get_people_who_filled_survey(
$survey['survey_id'],
false,
$course_info['real_id']
);
foreach ($user_list as $user_id) {
isset($survey_user_list[$user_id]) ? $survey_user_list[$user_id]++ : $survey_user_list[$user_id] = 1;
}
}
}
while ($user = Database::fetch_array($res, 'ASSOC')) {
$user['official_code'] = $user['col0'];
$user['lastname'] = $user['col1'];
$user['firstname'] = $user['col2'];
$user['username'] = $user['col3'];
$user['time'] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user['user_id'], $course_code, $session_id));
$avg_student_score = Tracking::get_avg_student_score(
$user['user_id'],
$course_code,
array(),
$session_id
);
$avg_student_progress = Tracking::get_avg_student_progress(
$user['user_id'],
$course_code,
array(),
$session_id
);
if (empty($avg_student_progress)) {
$avg_student_progress=0;
}
$user['average_progress'] = $avg_student_progress.'%';
$total_user_exercise = Tracking::get_exercise_student_progress(
$total_exercises,
$user['user_id'],
$course_code,
$session_id
);
$user['exercise_progress'] = $total_user_exercise;
$total_user_exercise = Tracking::get_exercise_student_average_best_attempt(
$total_exercises,
$user['user_id'],
$course_code,
$session_id
);
$user['exercise_average_best_attempt'] = $total_user_exercise;
if (is_numeric($avg_student_score)) {
$user['student_score'] = $avg_student_score.'%';
} else {
$user['student_score'] = $avg_student_score;
}
$user['count_assignments'] = Tracking::count_student_assignments($user['user_id'], $course_code, $session_id);
$user['count_messages'] = Tracking::count_student_messages($user['user_id'], $course_code, $session_id);
$user['first_connection'] = Tracking::get_first_connection_date_on_the_course($user['user_id'], $course_code, $session_id);
$user['last_connection'] = Tracking::get_last_connection_date_on_the_course($user['user_id'], $course_code, $session_id);
// we need to display an additional profile field
$user['additional'] = '';
if (isset($_GET['additional_profile_field']) AND is_numeric($_GET['additional_profile_field'])) {
if (isset($additional_user_profile_info[$user['user_id']]) &&
is_array($additional_user_profile_info[$user['user_id']])
) {
$user['additional'] = implode(', ', $additional_user_profile_info[$user['user_id']]);
}
}
if (empty($session_id)) {
$user['survey'] = (isset($survey_user_list[$user['user_id']]) ? $survey_user_list[$user['user_id']] : 0) .' / '.$total_surveys;
}
$user['link'] = '<center><a href="../mySpace/myStudents.php?student='.$user['user_id'].'&details=true&course='.$course_code.'&origin=tracking_course&id_session='.$session_id.'"><img src="'.api_get_path(WEB_IMG_PATH).'2rightarrow.gif" border="0" /></a></center>';
// store columns in array $users
$is_western_name_order = api_is_western_name_order();
$user_row = array();
$user_row[]= $user['official_code']; //0
if ($is_western_name_order) {
$user_row[]= $user['firstname'];
$user_row[]= $user['lastname'];
} else {
$user_row[]= $user['lastname'];
$user_row[]= $user['firstname'];
}
$user_row[]= $user['username'];
$user_row[]= $user['time'];
$user_row[]= $user['average_progress'];
$user_row[]= $user['exercise_progress'];
$user_row[]= $user['exercise_average_best_attempt'];
$user_row[]= $user['student_score'];
$user_row[]= $user['count_assignments'];
$user_row[]= $user['count_messages'];
if (empty($session_id)) {
$user_row[]= $user['survey'];
}
$user_row[]= $user['first_connection'];
$user_row[]= $user['last_connection'];
if (isset($_GET['additional_profile_field']) AND is_numeric($_GET['additional_profile_field'])) {
$user_row[]= $user['additional'];
}
$user_row[]= $user['link'];
$users[] = $user_row;
if ($export_csv) {
if (empty($session_id)) {
$user_row = array_map('strip_tags', $user_row);
unset($user_row[14]);
unset($user_row[15]);
} else {
$user_row = array_map('strip_tags', $user_row);
unset($user_row[13]);
unset($user_row[14]);
}
$csv_content[] = $user_row;
}
}
return $users;
}
/**
* Gets the progress of the given session
* @param int session id
* @param array options order and limit keys
* @return array table with user name, lp name, progress
*/
public static function get_session_progress($sessionId, $courseId, $date_from, $date_to, $options)
{
$sessionId = intval($sessionId);
$getAllSessions = false;
if (empty($sessionId)) {
$sessionId = 0;
$getAllSessions = true;
}
//tables
$session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$user = Database::get_main_table(TABLE_MAIN_USER);
$course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$workTable = Database::get_course_table(TABLE_STUDENT_PUBLICATION);
$workTableAssignment = Database::get_course_table(TABLE_STUDENT_PUBLICATION_ASSIGNMENT);
$forum = Database::get_course_table(TABLE_FORUM);
$forum_post = Database::get_course_table(TABLE_FORUM_POST);
$tbl_course_lp = Database::get_course_table(TABLE_LP_MAIN);
$wiki = Database::get_course_table(TABLE_WIKI);
$table_stats_default = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
$table_stats_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
$course = api_get_course_info_by_id($courseId);
//getting all the students of the course
//we are not using this because it only returns user ids
/* if (empty($sessionId)
{
// Registered students in a course outside session.
$users = CourseManager :: get_student_list_from_course_code($course_code);
} else {
// Registered students in session.
$users = CourseManager :: get_student_list_from_course_code($course_code, true, $sessionId);
} */
$where = " WHERE course_code = '%s' AND s.status <> 2 ";
$limit = null;
if (!empty($options['limit'])) {
$limit = " LIMIT " . $options['limit'];
}
if (!empty($options['where'])) {
$where .= ' '.$options['where'];
}
$order = null;
if (!empty($options['order'])) {
$order = " ORDER BY " . $options['order'];
}
//TODO, fix create report without session
$queryVariables = array($course['code']);
if (!empty($sessionId)) {
$where .= ' AND id_session = %s';
$queryVariables[] = $sessionId;
$sql = "SELECT
u.user_id, u.lastname, u.firstname, u.username,
u.email, s.course_code, s.id_session
FROM $session_course_user s
INNER JOIN $user u
ON u.user_id = s.id_user
$where $order $limit";
} else {
$sql = "SELECT
u.user_id, u.lastname, u.firstname, u.username,
u.email, s.course_code, s.id_session
FROM $session_course_user s
INNER JOIN $user u ON u.user_id = s.id_user
$where $order $limit";
}
$sql_query = vsprintf($sql, $queryVariables);
$rs = Database::query($sql_query);
while ($user = Database::fetch_array($rs)) {
$users[$user['user_id']] = $user;
}
/**
* Lessons
*/
$sql = "SELECT * FROM $tbl_course_lp WHERE c_id = %s "; //AND session_id = %s
$sql_query = sprintf($sql, $course['real_id']);
$result = Database::query($sql_query);
$arrLesson = array(array());
while ($row = Database::fetch_array($result)) {
// if (api_get_item_visibility(api_get_course_info($course['code']), 'learnpath', $row['id'], $row['session_id']))
// {
if (empty($arrLesson[$row['session_id']]['lessons_total'])) {
$arrLesson[$row['session_id']]['lessons_total'] = 1;
} else {
$arrLesson[$row['session_id']]['lessons_total'] ++;
}
// }
}
/**
* Exercises
*/
require_once api_get_path(SYS_CODE_PATH) . 'exercice/exercise.lib.php';
$exercises = get_all_exercises($course, $sessionId, false, '', $getAllSessions);
$exercises_total = count($exercises);
/**
* Assignments
*/
//total
if ($getAllSessions) {
$sql = "SELECT count(w.id) as count
FROM $workTable w
LEFT JOIN $workTableAssignment a
ON (a.publication_id = w.id AND a.c_id = w.c_id)
WHERE w.c_id = %s
AND parent_id = 0
AND active IN (1, 0)";
} else {
$sql = "SELECT count(w.id) as count
FROM $workTable w
LEFT JOIN $workTableAssignment a
ON (a.publication_id = w.id AND a.c_id = w.c_id)
WHERE w.c_id = %s
AND parent_id = 0
AND active IN (1, 0)
AND session_id = %s";
}
$sql_query = sprintf($sql, $course['real_id'], $sessionId);
$result = Database::query($sql_query);
$row = Database::fetch_array($result);
$assignments_total = $row['count'];
/**
* Wiki
*/
if ($getAllSessions) {
$sql = "SELECT count(distinct page_id) as count FROM $wiki
WHERE c_id = %s";
} else {
$sql = "SELECT count(distinct page_id) as count FROM $wiki
WHERE c_id = %s and session_id = %s";
}
$sql_query = sprintf($sql, $course['real_id'], $sessionId);
$result = Database::query($sql_query);
$row = Database::fetch_array($result);
$wiki_total = $row['count'];
/**
* Surveys
*/
$survey_user_list = array();
$survey_list = survey_manager::get_surveys($course['code'], $sessionId);
$surveys_total = count($survey_list);
foreach ($survey_list as $survey) {
$user_list = survey_manager::get_people_who_filled_survey(
$survey['survey_id'],
false,
$course['real_id']
);
foreach ($user_list as $user_id) {
isset($survey_user_list[$user_id]) ? $survey_user_list[$user_id] ++ : $survey_user_list[$user_id] = 1;
}
}
/**
* Forums
*/
$forums_total = CourseManager::getCountForum(
$course['real_id'],
$sessionId,
$getAllSessions
);
//process table info
foreach ($users as $user) {
//Course description
$sql = "SELECT count(*) as count
FROM $table_stats_access
WHERE access_tool = 'course_description'
AND access_cours_code = '%s'
AND access_session_id = %s
AND access_user_id = %s ";
$sql_query = sprintf($sql, $course['code'], $user['id_session'], $user['user_id']);
$result = Database::query($sql_query);
$row = Database::fetch_array($result);
$course_description_progress = ($row['count'] > 0) ? 100 : 0;
if (!empty($arrLesson[$user['id_session']]['lessons_total'])) {
$lessons_total = $arrLesson[$user['id_session']]['lessons_total'];
} else {
$lessons_total = !empty($arrLesson[0]['lessons_total']) ? $arrLesson[0]['lessons_total'] : 0;
}
//Lessons
//TODO: Lessons done and left is calculated by progress per item in lesson, maybe we should calculate it only per completed lesson?
$lessons_progress = Tracking::get_avg_student_progress($user['user_id'], $course['code'], array(), $user['id_session']);
$lessons_done = ($lessons_progress * $lessons_total) / 100;
$lessons_left = $lessons_total - $lessons_done;
//Exercises
$exercises_progress = str_replace('%', '', Tracking::get_exercise_student_progress($exercises, $user['user_id'], $course['code'], $user['id_session']));
$exercises_done = round(($exercises_progress * $exercises_total) / 100);
$exercises_left = $exercises_total - $exercises_done;
//Assignments
$assignments_done = Tracking::count_student_assignments($user['user_id'], $course['code'], $user['id_session']);
$assignments_left = $assignments_total - $assignments_done;
if (!empty($assignments_total)) {
$assignments_progress = round((( $assignments_done * 100 ) / $assignments_total), 2);
} else {
$assignments_progress = 0;
}
//Wiki
//total revisions per user
$sql = "SELECT count(*) as count
FROM $wiki
where c_id = %s and session_id = %s and user_id = %s";
$sql_query = sprintf($sql, $course['real_id'], $user['id_session'], $user['user_id']);
$result = Database::query($sql_query);
$row = Database::fetch_array($result);
$wiki_revisions = $row['count'];
//count visited wiki pages
$sql = "SELECT count(distinct default_value) as count
FROM $table_stats_default
WHERE default_user_id = %s
AND default_cours_code = '%s'
AND default_event_type = 'wiki_page_view'
AND default_value_type = 'wiki_page_id'
AND c_id = %s";
$sql_query = sprintf($sql, $user['user_id'], $course['code'], $course['real_id']);
$result = Database::query($sql_query);
$row = Database::fetch_array($result);
$wiki_read = $row['count'];
$wiki_unread = $wiki_total - $wiki_read;
if (!empty($wiki_total)) {
$wiki_progress = round((( $wiki_read * 100 ) / $wiki_total), 2);
} else {
$wiki_progress = 0;
}
//Surveys
$surveys_done = (isset($survey_user_list[$user['user_id']]) ? $survey_user_list[$user['user_id']] : 0);
$surveys_left = $surveys_total - $surveys_done;
if (!empty($surveys_total)) {
$surveys_progress = round((( $surveys_done * 100 ) / $surveys_total), 2);
} else {
$surveys_progress = 0;
}
//Forums
$forums_done = CourseManager::getCountForumPerUser(
$user['user_id'],
$course['real_id'],
$user['id_session']
);
$forums_left = $forums_total - $forums_done;
if (!empty($forums_total)) {
$forums_progress = round((( $forums_done * 100 ) / $forums_total), 2);
} else {
$forums_progress = 0;
}
//Overall Total
$overall_total = ($course_description_progress + $exercises_progress + $forums_progress + $assignments_progress + $wiki_progress + $surveys_progress) / 6;
$link = '<a href="' . api_get_path(WEB_CODE_PATH) . 'mySpace/myStudents.php?student=' . $user[0] . '&details=true&course=' . $course['code'] . '&id_session=' . $user['id_session'] . '"> %s </a>';
$linkForum = '<a href="' . api_get_path(WEB_CODE_PATH) . 'forum/index.php?cidReq=' . $course['code'] . '&id_session=' . $user['id_session'] . '"> %s </a>';
$linkWork = '<a href="' . api_get_path(WEB_CODE_PATH) . 'work/work.php?cidReq=' . $course['code'] . '&id_session=' . $user['id_session'] . '"> %s </a>';
$linkWiki = '<a href="' . api_get_path(WEB_CODE_PATH) . 'wiki/index.php?cidReq=' . $course['code'] . '&session_id=' . $user['id_session'] . '&action=statistics"> %s </a>';
$linkSurvey = '<a href="' . api_get_path(WEB_CODE_PATH) . 'survey/survey_list.php?cidReq=' . $course['code'] . '&id_session=' . $user['id_session'] . '"> %s </a>';
$table[] = array(
'lastname' => $user[1],
'firstname' => $user[2],
'username' => $user[3],
#'profile' => '',
'total' => round($overall_total, 2) . '%',
'courses' => sprintf($link, $course_description_progress . '%'),
'lessons' => sprintf($link, $lessons_progress . '%'),
'exercises' => sprintf($link, $exercises_progress . '%'),
'forums' => sprintf($link, $forums_progress . '%'),
'homeworks' => sprintf($link, $assignments_progress . '%'),
'wikis' => sprintf($link, $wiki_progress . '%'),
'surveys' => sprintf($link, $surveys_progress . '%'),
//course description
'course_description_progress' => $course_description_progress . '%',
//lessons
'lessons_total' => sprintf($link, $lessons_total),
'lessons_done' => sprintf($link, $lessons_done),
'lessons_left' => sprintf($link, $lessons_left),
'lessons_progress' => sprintf($link, $lessons_progress . '%'),
//exercises
'exercises_total' => sprintf($link, $exercises_total),
'exercises_done' => sprintf($link, $exercises_done),
'exercises_left' => sprintf($link, $exercises_left),
'exercises_progress' => sprintf($link, $exercises_progress . '%'),
//forums
'forums_total' => sprintf($linkForum, $forums_total),
'forums_done' => sprintf($linkForum, $forums_done),
'forums_left' => sprintf($linkForum, $forums_left),
'forums_progress' => sprintf($linkForum, $forums_progress . '%'),
//assignments
'assignments_total' => sprintf($linkWork, $assignments_total),
'assignments_done' => sprintf($linkWork, $assignments_done),
'assignments_left' => sprintf($linkWork, $assignments_left),
'assignments_progress' => sprintf($linkWork, $assignments_progress . '%'),
//wiki
'wiki_total' => sprintf($linkWiki, $wiki_total),
'wiki_revisions' => sprintf($linkWiki, $wiki_revisions),
'wiki_read' => sprintf($linkWiki, $wiki_read),
'wiki_unread' => sprintf($linkWiki, $wiki_unread),
'wiki_progress' => sprintf($linkWiki, $wiki_progress . '%'),
//survey
'surveys_total' => sprintf($linkSurvey, $surveys_total),
'surveys_done' => sprintf($linkSurvey, $surveys_done),
'surveys_left' => sprintf($linkSurvey, $surveys_left),
'surveys_progress' => sprintf($linkSurvey, $surveys_progress . '%'),
);
}
return $table;
}
$lp_count = 0;
if (!empty($lp_list)) {
foreach ($lp_list as $valLp) {
if ($valLp['lp_visibility']) {
$lp_count++;
}
}
}
$course_info = api_get_course_info($course_data['code']);
$exercise_count = count(
get_all_exercises(
$course_info,
$session_id,
true,
null,
false,
1
)
);
$max_mutation_date = '';
$last_date = Tracking::get_last_connection_date_on_the_course(
api_get_user_id(),
$course_data['code'],
$session_id,
false
);
$icons = '';
foreach ($lp_list as $item) {
