(Score average fixes)
*/
/**
* Class
* @package chamilo.library
*/
class Tracking
{
/**
* @param int $userId
*
* @return array
*/
public static function getStats($userId)
{
if (api_is_drh() && api_drh_can_access_all_session_content()) {
$studentList = SessionManager::getAllUsersFromCoursesFromAllSessionFromStatus(
'drh_all',
$userId,
false,
null,
null,
null,
null,
null,
null,
null,
array(),
array(),
STUDENT
);
$students = array();
foreach ($studentList as $studentData) {
$students[] = $studentData['user_id'];
}
$teacherList = SessionManager::getAllUsersFromCoursesFromAllSessionFromStatus(
'drh_all',
$userId,
false,
null,
null,
null,
null,
null,
null,
null,
array(),
array(),
COURSEMANAGER
);
$teachers = array();
foreach ($teacherList as $teacherData) {
$teachers[] = $teacherData['user_id'];
}
$humanResources = SessionManager::getAllUsersFromCoursesFromAllSessionFromStatus(
'drh_all',
$userId,
false,
null,
null,
null,
null,
null,
null,
null,
array(),
array(),
DRH
);
$humanResourcesList = array();
foreach ($humanResources as $item) {
$humanResourcesList[] = $item['user_id'];
}
$platformCourses = SessionManager::getAllCoursesFollowedByUser(
$userId,
null,
null,
null,
null,
null
);
//$platformCourses = SessionManager::getAllCoursesFromAllSessionFromDrh($userId);
$courses = array();
foreach ($platformCourses as $course) {
$courses[$course['code']] = $course['code'];
}
$sessions = SessionManager::get_sessions_followed_by_drh($userId);
} else {
$studentList = UserManager::getUsersFollowedByUser(
$userId,
STUDENT,
false,
false,
false,
null,
null,
null,
null,
null,
null,
COURSEMANAGER
);
$students = array();
foreach ($studentList as $studentData) {
$students[] = $studentData['user_id'];
}
$teacherList = UserManager::getUsersFollowedByUser(
$userId,
COURSEMANAGER,
false,
false,
false,
null,
null,
null,
null,
null,
null,
COURSEMANAGER
);
$teachers = array();
foreach ($teacherList as $teacherData) {
$teachers[] = $teacherData['user_id'];
}
$humanResources = UserManager::getUsersFollowedByUser(
$userId,
DRH,
false,
false,
false,
null,
null,
null,
null,
null,
null,
COURSEMANAGER
);
$humanResourcesList = array();
foreach ($humanResources as $item) {
$humanResourcesList[] = $item['user_id'];
}
$platformCourses = CourseManager::getCoursesFollowedByUser(
$userId,
COURSEMANAGER
);
foreach ($platformCourses as $course) {
$courses[$course['code']] = $course['code'];
}
$sessions = SessionManager::getSessionsFollowedByUser(
$userId,
COURSEMANAGER
);
}
return array(
'drh' => $humanResourcesList,
'teachers' => $teachers,
'students' => $students,
'courses' => $courses,
'sessions' => $sessions
);
}
/**
* Calculates the time spent on the platform by a user
* @param int|array User id
* @param string type of time filter: 'last_week' or 'custom'
* @param strgin start date date('Y-m-d H:i:s')
* @param strgin end date date('Y-m-d H:i:s')
* @return timestamp $nb_seconds
*/
public static function get_time_spent_on_the_platform(
$userId,
$timeFilter = 'last_7_days',
$start_date = null,
$end_date = null
) {
$tbl_track_login = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$condition_time = '';
if (is_array($userId)) {
$userList = array_map('intval', $userId);
$userCondition = " login_user_id IN ('".implode("','", $userList)."')";
} else {
$userCondition = " login_user_id = ".intval($userId);
}
if (empty($timeFilter)) {
$timeFilter = 'last_week';
}
$today = date('Y-m-d H:i:s');
switch ($timeFilter) {
case 'last_7_days':
$new_date = date('Y-m-d H:i:s', strtotime('-7 day'));
$condition_time = ' AND (login_date >= "'.$new_date.'" AND logout_date <= "'.$today.'") ';
break;
case 'last_30_days':
$new_date = date('Y-m-d H:i:s', strtotime('-30 day'));
$condition_time = ' AND (login_date >= "'.$new_date.'" AND logout_date <= "'.$today.'") ';
break;
case 'custom':
if (!empty($start_date) && !empty($end_date)) {
$start_date = Database::escape_string($start_date);
$end_date = Database::escape_string($end_date);
$condition_time = ' AND (login_date >= "'.$start_date.'" AND logout_date <= "'.$end_date.'" ) ';
}
break;
}
$sql = 'SELECT SUM(TIMESTAMPDIFF(SECOND, login_date, logout_date)) diff
FROM '.$tbl_track_login.'
WHERE '.$userCondition.$condition_time;
$rs = Database::query($sql);
$row = Database::fetch_array($rs, 'ASSOC');
$diff = $row['diff'];
if ($diff >= 0) {
return $diff;
} else {
return -1;
}
}
/**
* Calculates the time spent on the course
* @param integer $user_id
* @param string $course_code
* @param int Session id (optional)
*
* @return int Time in seconds
*/
public static function get_time_spent_on_the_course($user_id, $course_code, $session_id = 0)
{
$course_code = Database::escape_string($course_code);
$session_id = intval($session_id);
$tbl_track_course = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
if (is_array($user_id)) {
$user_id = array_map('intval', $user_id);
$condition_user = " AND user_id IN (".implode(',',$user_id).") ";
} else {
$user_id = intval($user_id);
$condition_user = " AND user_id = '$user_id' ";
}
$sql = "SELECT SUM(UNIX_TIMESTAMP(logout_course_date) - UNIX_TIMESTAMP(login_course_date)) as nb_seconds
FROM $tbl_track_course
WHERE
UNIX_TIMESTAMP(logout_course_date) > UNIX_TIMESTAMP(login_course_date) AND
course_code='$course_code' AND
session_id = '$session_id' $condition_user";
$rs = Database::query($sql);
$row = Database::fetch_array($rs);
return $row['nb_seconds'];
}
/**
* Get first connection date for a student
* @param int Student id
* @return string|bool Date format long without day or false if there are no connections
*/
public static function get_first_connection_date($student_id)
{
$tbl_track_login = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$sql = 'SELECT login_date FROM ' . $tbl_track_login . '
WHERE login_user_id = ' . intval($student_id) . '
ORDER BY login_date ASC LIMIT 0,1';
$rs = Database::query($sql);
if (Database::num_rows($rs)>0) {
if ($first_login_date = Database::result($rs, 0, 0)) {
return api_convert_and_format_date($first_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
}
}
return false;
}
/**
* Get las connection date for a student
* @param int Student id
* @param bool Show a warning message (optional)
* @param bool True for returning results in timestamp (optional)
* @return string|int|bool Date format long without day, false if there are no connections or
* timestamp if parameter $return_timestamp is true
*/
public static function get_last_connection_date($student_id, $warning_message = false, $return_timestamp = false)
{
$tbl_track_login = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$sql = 'SELECT login_date FROM ' . $tbl_track_login . '
WHERE login_user_id = ' . intval($student_id) . '
ORDER BY login_date DESC LIMIT 0,1';
$rs = Database::query($sql);
if (Database::num_rows($rs) > 0) {
if ($last_login_date = Database::result($rs, 0, 0)) {
$last_login_date = api_get_local_time($last_login_date);
if ($return_timestamp) {
return api_strtotime($last_login_date,'UTC');
} else {
if (!$warning_message) {
return api_format_date($last_login_date, DATE_FORMAT_SHORT);
} else {
$timestamp = api_strtotime($last_login_date,'UTC');
$currentTimestamp = time();
//If the last connection is > than 7 days, the text is red
//345600 = 7 days in seconds
if ($currentTimestamp - $timestamp > 604800) {
return '' . api_format_date($last_login_date, DATE_FORMAT_SHORT) . '';
} else {
return api_format_date($last_login_date, DATE_FORMAT_SHORT);
}
}
}
}
}
return false;
}
/**
* Get las connection date for a student
* @param array Student id array
* @param int $days
* @param bool $getCount
* @return int
*/
public static function getInactiveUsers($studentList, $days, $getCount = true)
{
if (empty($studentList)) {
return 0;
}
$days = intval($days);
$date = api_get_utc_datetime(strtotime($days.' days ago'));
$studentList = array_map('intval', $studentList);
$tbl_track_login = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$select = " SELECT login_user_id ";
if ($getCount) {
$select = " SELECT count(DISTINCT login_user_id) as count";
}
$sql = "$select
FROM $tbl_track_login
WHERE
login_user_id IN (' ". implode("','", $studentList) . "' ) AND
login_date < '$date'
";
$rs = Database::query($sql);
if (Database::num_rows($rs) > 0) {
if ($getCount) {
$count = Database::fetch_array($rs);
return $count['count'];
}
return Database::store_result($rs, 'ASSOC');
}
return false;
}
/**
* Get first user's connection date on the course
* @param int User id
* @param string Course code
* @param int Session id (optional, default=0)
* @return string|bool Date with format long without day or false if there is no date
*/
public static function get_first_connection_date_on_the_course(
$student_id,
$course_code,
$session_id = 0,
$convert_date = true
) {
// protect data
$student_id = intval($student_id);
$course_code = Database::escape_string($course_code);
$session_id = intval($session_id);
$tbl_track_login = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
$sql = 'SELECT login_course_date FROM '.$tbl_track_login.'
WHERE user_id = '.$student_id.'
AND course_code = "'.$course_code.'"
AND session_id = '.$session_id.'
ORDER BY login_course_date ASC LIMIT 0,1';
$rs = Database::query($sql);
if (Database::num_rows($rs) > 0) {
if ($first_login_date = Database::result($rs, 0, 0)) {
if ($convert_date) {
return api_convert_and_format_date($first_login_date, DATE_FORMAT_SHORT);
} else {
return $first_login_date;
}
}
}
return false;
}
/**
* Get last user's connection date on the course
* @param int User id
* @param string Course code
* @param int Session id (optional, default=0)
* @return string|bool Date with format long without day or false if there is no date
*/
public static function get_last_connection_date_on_the_course(
$student_id,
$course_code,
$session_id = 0,
$convert_date = true
) {
// protect data
$student_id = intval($student_id);
$course_code = Database::escape_string($course_code);
$session_id = intval($session_id);
$tbl_track_e_access = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS);
$sql = 'SELECT access_date
FROM '.$tbl_track_e_access.'
WHERE access_user_id = '.$student_id.' AND
access_cours_code = "'.$course_code.'" AND
access_session_id = '.$session_id.'
ORDER BY access_date DESC
LIMIT 0,1';
$rs = Database::query($sql);
if (Database::num_rows($rs) > 0) {
if ($last_login_date = Database::result($rs, 0, 0)) {
if (empty($last_login_date) || $last_login_date == '0000-00-00 00:00:00') {
return false;
}
//$last_login_date_timestamp = api_strtotime($last_login_date, 'UTC');
//see #5736
$last_login_date_timestamp = api_strtotime($last_login_date);
$now = time();
//If the last connection is > than 7 days, the text is red
//345600 = 7 days in seconds
if ($now - $last_login_date_timestamp > 604800) {
if ($convert_date) {
$last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT);
$icon = api_is_allowed_to_edit() ? ' ': null;
return $icon. Display::label($last_login_date, 'warning');
} else {
return $last_login_date;
}
} else {
if ($convert_date) {
return api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT);
} else {
return $last_login_date;
}
}
}
}
return false;
}
/**
* Get count of the connections to the course during a specified period
* @param string Course code
* @param int Session id (optional)
* @param int Datetime from which to collect data (defaults to 0)
* @param int Datetime to which to collect data (defaults to now)
* @return int count connections
*/
public static function get_course_connections_count($course_code, $session_id = 0, $start = 0, $stop = null)
{
if ($start < 0) {
$start = 0;
}
if (!isset($stop) or ($stop < 0)) {
$stop = api_get_utc_datetime();
}
$start = Database::escape_string($start);
$stop = Database::escape_string($stop);
$month_filter = " AND login_course_date > '$start' AND login_course_date < '$stop' ";
$course_code = Database::escape_string($course_code);
$session_id = intval($session_id);
$count = 0;
$tbl_track_e_course_access = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
$sql = "SELECT count(*) as count_connections
FROM $tbl_track_e_course_access
WHERE
course_code = '$course_code' AND
session_id = $session_id
$month_filter";
$rs = Database::query($sql);
if (Database::num_rows($rs)>0) {
$row = Database::fetch_object($rs);
$count = $row->count_connections;
}
return $count;
}
/**
* Get count courses per student
* @param int Student id
* @param bool Include sessions (optional)
* @return int count courses
*/
public static function count_course_per_student($user_id, $include_sessions = true)
{
$user_id = intval($user_id);
$tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_session_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$sql = 'SELECT DISTINCT course_code
FROM ' . $tbl_course_rel_user . '
WHERE user_id = ' . $user_id.' AND relation_type<>'.COURSE_RELATION_TYPE_RRHH;
$rs = Database::query($sql);
$nb_courses = Database::num_rows($rs);
if ($include_sessions) {
$sql = 'SELECT DISTINCT course_code
FROM ' . $tbl_session_course_rel_user . '
WHERE id_user = ' . $user_id;
$rs = Database::query($sql);
$nb_courses += Database::num_rows($rs);
}
return $nb_courses;
}
/**
* Gets the score average from all tests in a course by student
*
* @param $student_id
* @param $course_code
* @param int $exercise_id
* @param null $session_id
* @param int $active_filter 2 for consider all tests
* 1 for active <> -1
* 0 for active <> 0
* @param int $into_lp 1 for all exercises
* 0 for without LP
* @internal param \Student $mixed id
* @internal param \Course $string code
* @internal param \Exercise $int id (optional), filtered by exercise
* @internal param \Session $int id (optional), if param $session_id is null it'll return results including sessions, 0 = session is not filtered
* @return string value (number %) Which represents a round integer about the score average.
*/
public static function get_avg_student_exercise_score(
$student_id,
$course_code,
$exercise_id = 0,
$session_id = null,
$active_filter = 1,
$into_lp = 0
) {
$course_code = Database::escape_string($course_code);
$course_info = api_get_course_info($course_code);
if (!empty($course_info)) {
// table definition
$tbl_course_quiz = Database::get_course_table(TABLE_QUIZ_TEST);
$tbl_stats_exercise = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
// Compose a filter based on optional exercise given
$condition_quiz = "";
if (!empty($exercise_id)) {
$exercise_id = intval($exercise_id);
$condition_quiz =" AND id = $exercise_id ";
}
// Compose a filter based on optional session id given
$condition_session = "";
if (isset($session_id)) {
$session_id = intval($session_id);
$condition_session = " AND session_id = $session_id ";
}
if ($active_filter == 1) {
$condition_active = 'AND active <> -1';
} elseif ($active_filter == 0) {
$condition_active = 'AND active <> 0';
} else {
$condition_active = '';
}
$condition_into_lp = '';
$select_lp_id = '';
if ($into_lp == 0) {
$condition_into_lp = 'AND orig_lp_id = 0 AND orig_lp_item_id = 0';
} else {
$select_lp_id = ', orig_lp_id as lp_id ';
}
$sql = "SELECT count(id) FROM $tbl_course_quiz
WHERE c_id = {$course_info['real_id']} $condition_active $condition_quiz ";
$count_quiz = Database::fetch_row(Database::query($sql));
if (!empty($count_quiz[0]) && !empty($student_id)) {
if (is_array($student_id)) {
$student_id = array_map('intval', $student_id);
$condition_user = " AND exe_user_id IN (".implode(',', $student_id).") ";
} else {
$student_id = intval($student_id);
$condition_user = " AND exe_user_id = '$student_id' ";
}
if (empty($exercise_id)) {
$sql = "SELECT id FROM $tbl_course_quiz
WHERE c_id = {$course_info['real_id']} $condition_active $condition_quiz";
$result = Database::query($sql);
$exercise_list = array();
$exercise_id = null;
if (Database::num_rows($result)) {
while ($row = Database::fetch_array($result)) {
$exercise_list[] = $row['id'];
}
}
if (!empty($exercise_list)) {
$exercise_id = implode("','",$exercise_list);
}
}
$count_quiz = Database::fetch_row(Database::query($sql));
$sql = "SELECT
SUM(exe_result/exe_weighting*100) as avg_score,
COUNT(*) as num_attempts
$select_lp_id
FROM $tbl_stats_exercise
WHERE
exe_exo_id IN ('".$exercise_id."')
$condition_user AND
status = '' AND
exe_cours_id = '$course_code'
$condition_session
$condition_into_lp
ORDER BY exe_date DESC";
$res = Database::query($sql);
$row = Database::fetch_array($res);
$quiz_avg_score = null;
if (!empty($row['avg_score'])) {
$quiz_avg_score = round($row['avg_score'],2);
}
if(!empty($row['num_attempts'])) {
$quiz_avg_score = round($quiz_avg_score / $row['num_attempts'], 2);
}
if (is_array($student_id)) {
$quiz_avg_score = round($quiz_avg_score / count($student_id), 2);
}
if ($into_lp == 0) {
return $quiz_avg_score;
} else {
if (!empty($row['lp_id'])) {
$tbl_lp = Database::get_course_table(TABLE_LP_MAIN);
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$sql = "SELECT lp.name
FROM $tbl_lp as lp, $tbl_course as c
WHERE
c.code = '$course_code' AND
lp.id = ".$row['lp_id']." AND
lp.c_id = c.id
LIMIT 1;
";
$result = Database::query($sql);
$row_lp = Database::fetch_row($result);
$lp_name = $row_lp[0];
return array($quiz_avg_score, $lp_name);
} else {
return array($quiz_avg_score, null);
}
}
}
}
return null;
}
/**
* Get count student's exercise COMPLETED attempts
* @param $student_id
* @param $course_code
* @param $exercise_id
* @param int $lp_id
* @param int $lp_item_id
* @param int $session_id
* @param int $find_all_lp 0 = just LP specified
* 1 = LP specified or whitout LP,
* 2 = all rows
* @internal param \Student $int id
* @internal param \Course $string code
* @internal param \Exercise $int id
* @internal param \Learning $int path id (optional), for showing attempts inside a learning path $lp_id and $lp_item_id params are required.
* @internal param \Learning $int path item id (optional), for showing attempts inside a learning path $lp_id and $lp_item_id params are required.
* @return int count of attempts
*/
public static function count_student_exercise_attempts(
$student_id,
$course_code,
$exercise_id,
$lp_id = 0,
$lp_item_id = 0,
$session_id = 0,
$find_all_lp = 0
) {
$course_code = Database::escape_string($course_code);
$student_id = intval($student_id);
$exercise_id = intval($exercise_id);
$session_id = intval($session_id);
$lp_id = intval($lp_id);
$lp_item_id = intval($lp_item_id);
$tbl_stats_exercices = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
$sql = "SELECT COUNT(ex.exe_id) as essais FROM $tbl_stats_exercices AS ex
WHERE ex.exe_cours_id = '$course_code'
AND ex.exe_exo_id = $exercise_id
AND status = ''
AND exe_user_id= $student_id
AND session_id = $session_id ";
if ($find_all_lp == 1) {
$sql .= "AND (orig_lp_id = $lp_id OR orig_lp_id = 0)
AND (orig_lp_item_id = $lp_item_id OR orig_lp_item_id = 0)";
} elseif ($find_all_lp == 0) {
$sql .= "AND orig_lp_id = $lp_id
AND orig_lp_item_id = $lp_item_id";
}
$rs = Database::query($sql);
$row = Database::fetch_row($rs);
$count_attempts = $row[0];
return $count_attempts;
}
/**
* Get count student's exercise progress
* @param int user id
* @param string course code
* @param int session id
*/
public static function get_exercise_student_progress($exercise_list, $user_id, $course_code, $session_id)
{
$course_code = Database::escape_string($course_code);
$user_id = intval($user_id);
$session_id = intval($session_id);
if (empty($exercise_list)) {
return '0%';
}
$tbl_stats_exercices = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
$exercise_list = array_keys($exercise_list);
$exercise_list = array_map('intval', $exercise_list);
$exercise_list_imploded = implode("' ,'", $exercise_list);
$sql = "SELECT COUNT(DISTINCT ex.exe_exo_id) FROM $tbl_stats_exercices AS ex
WHERE ex.exe_cours_id = '$course_code' AND
ex.session_id = $session_id AND
ex.exe_user_id = $user_id AND ex.exe_exo_id IN ('$exercise_list_imploded') ";
$rs = Database::query($sql);
$count = 0;
if ($rs) {
$row = Database::fetch_row($rs);
$count = $row[0];
}
$count = ($count != 0 ) ? 100*round(intval($count)/count($exercise_list), 2) .'%' : '0%';
return $count;
}
/**
* @param array $exercise_list
* @param int $user_id
* @param string $course_code
* @param int $session_id
* @return string
*/
public static function get_exercise_student_average_best_attempt($exercise_list, $user_id, $course_code, $session_id)
{
$result = 0;
if (!empty($exercise_list)) {
foreach ($exercise_list as $exercise_data) {
$exercise_id = $exercise_data['id'];
$best_attempt = get_best_attempt_exercise_results_per_user($user_id, $exercise_id , $course_code, $session_id);
if (!empty($best_attempt)) {
$result += $best_attempt['exe_result']/$best_attempt['exe_weighting'];
}
}
$result = $result/ count($exercise_list);
$result = round($result, 2)*100;
}
return $result.'%';
}
/**
* get teacher progress by course and session
* @param int course id
* @param int session id
* @return data array
*/
static function get_teachers_progress_by_course($courseId, $sessionId)
{
$course = api_get_course_info_by_id($courseId);
$sessionId = intval($sessionId);
$courseId = intval($courseId);
//get teachers
$sql = "SELECT scu.id_session, scu.id_user, s.name
FROM session_rel_course_rel_user scu, session s
WHERE
scu.id_session = s.id
AND scu.status = 2
AND scu.visibility = 1
AND scu.course_code = '%s'
AND scu.id_session = %s";
$query = sprintf($sql,$course['code'], $sessionId);
$rs = Database::query($query);
$teachers = array();
while ($teacher = Database::fetch_array($rs,'ASSOC')) {
$teachers[] = $teacher;
}
foreach ($teachers as $teacher) {
//total documents added
$sql = "SELECT count(*) as total
FROM c_item_property
WHERE lastedit_type = 'DocumentAdded'
AND c_id = %s
AND insert_user_id = %s
AND id_session = %s";
$query = sprintf($sql,
$courseId,
$teacher['id_user'],
$teacher['id_session']
);
$rs = Database::query($query);
$totalDocuments = 0;
if ($rs) {
$row = Database::fetch_row($rs);
$totalDocuments = $row[0];
}
//total links added
$sql = "SELECT count(*) as total
FROM c_item_property
WHERE lastedit_type = 'LinkAdded'
AND c_id = %s
AND insert_user_id = %s
AND id_session = %s";
$query = sprintf($sql,
$courseId,
$teacher['id_user'],
$teacher['id_session']
);
$rs = Database::query($query);
$totalLinks = 0;
if ($rs) {
$row = Database::fetch_row($rs);
$totalLinks = $row[0];
}
//total forums added
$sql = "SELECT count(*) as total
FROM c_item_property
WHERE lastedit_type = 'ForumthreadVisible'
AND c_id = %s
AND insert_user_id = %s
AND id_session = %s";
$query = sprintf($sql,
$courseId,
$teacher['id_user'],
$teacher['id_session']
);
$rs = Database::query($query);
$totalForums = 0;
if ($rs) {
$row = Database::fetch_row($rs);
$totalForums = $row[0];
}
//total wikis added
$sql = "SELECT COUNT(DISTINCT(ref)) as total
FROM c_item_property
WHERE lastedit_type = 'WikiAdded'
AND c_id = %s
AND insert_user_id = %s
AND id_session = %s";
$query = sprintf($sql,
$courseId,
$teacher['id_user'],
$teacher['id_session']
);
$rs = Database::query($query);
$totalWikis = 0;
if ($rs) {
$row = Database::fetch_row($rs);
$totalWikis = $row[0];
}
//total works added
$sql = "SELECT COUNT(*) as total
FROM c_item_property
WHERE lastedit_type = 'DirectoryCreated'
AND tool = 'work'
AND c_id = %s
AND insert_user_id = %s
AND id_session = %s";
$query = sprintf($sql,
$courseId,
$teacher['id_user'],
$teacher['id_session']
);
$rs = Database::query($query);
$totalWorks = 0;
if ($rs) {
$row = Database::fetch_row($rs);
$totalWorks = $row[0];
}
//total announcements added
$sql = "SELECT COUNT(*) as total
FROM c_item_property
WHERE lastedit_type = 'AnnouncementAdded'
AND c_id = %s
AND insert_user_id = %s
AND id_session = %s";
$query = sprintf($sql,
$courseId,
$teacher['id_user'],
$teacher['id_session']
);
$rs = Database::query($query);
$totalAnnouncements = 0;
if ($rs) {
$row = Database::fetch_row($rs);
$totalAnnouncements = $row[0];
}
$tutor = get_user_info_by_id($teacher['id_user']);
$data[] = array(
'course' => $course['title'],
'session' => $teacher['name'],
'tutor' => $tutor['username'] . ' - ' . $tutor['lastname'] . ' ' . $tutor['firstname'],
'documents' => $totalDocuments,
'links' => $totalLinks,
'forums' => $totalForums,
'works' => $totalWorks,
'wikis' => $totalWikis,
'announcements' => $totalAnnouncements,
);
}
return $data;
}
/**
* Returns the average student progress in the learning paths of the given
* course.
* @param int/array Student id(s)
* @param string Course code
* @param array Limit average to listed lp ids
* @param int Session id (optional), if parameter $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
* @param bool Will return an array of the type: [sum_of_progresses, number] if it is set to true
* @return double Average progress of the user in this course
*/
public static function get_avg_student_progress($student_id, $course_code = null, $lp_ids = array(), $session_id = null, $return_array = false)
{
$conditions = array();
$session_id = intval($session_id);
// Get the information of the course.
$course_info = api_get_course_info($course_code);
if (!empty($course_info)) {
$conditions[] = " c_id = {$course_info['real_id']} ";
}
// table definition
$tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW);
// Compose a filter based on optional learning paths list given
$condition_lp = null;
if (!empty($lp_ids)) {
if (count($lp_ids) > 0) {
$lp_ids = array_map('intval', $lp_ids);
$conditions[] = " lp_view.lp_id IN(".implode(',', $lp_ids).") ";
}
}
// If there is at least one learning path and one student.
if (!empty($student_id)) {
if (is_array($student_id)) {
$student_id = array_map('intval', $student_id);
$conditions[] = " lp_view.user_id IN (".implode(',', $student_id).") ";
} else {
$student_id = intval($student_id);
$conditions[] = " lp_view.user_id = '$student_id' ";
}
if (!empty($session_id)) {
$conditions[] = " session_id = $session_id ";
}
$conditionToString = implode('AND', $conditions);
// Get last view for each student (in case of multi-attempt)
// Also filter on LPs of this session
$sql = " SELECT
MAX(view_count),
AVG(progress) average,
SUM(progress) sum_progress,
count(progress) count_progress
FROM $tbl_course_lp_view lp_view
WHERE
$conditionToString
GROUP BY lp_id";
$result = Database::query($sql);
$row = Database::fetch_array($result, 'ASSOC');
if (!$return_array) {
$avg_progress = round($row['average'], 1);
return $avg_progress;
} else {
return array($row['sum_progress'], $row['count_progress']);
}
}
}
/**
* This function gets:
* 1. The score average from all SCORM Test items in all LP in a course-> All the answers / All the max scores.
* 2. The score average from all Tests (quiz) in all LP in a course-> All the answers / All the max scores.
* 3. And finally it will return the average between 1. and 2.
* @todo improve performance, when loading 1500 users with 20 lps the script dies
* This function does not take the results of a Test out of a LP
*
* @param mixed Array of user ids or an user id
* @param string Course code
* @param array List of LP ids
* @param int Session id (optional), if param $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
* @param bool Returns an array of the type [sum_score, num_score] if set to true
* @param bool get only the latest attempts or ALL attempts
* @return string Value (number %) Which represents a round integer explain in got in 3.
*/
public static function get_avg_student_score(
$student_id,
$course_code,
$lp_ids = array(),
$session_id = null,
$return_array = false,
$get_only_latest_attempt_results = false
) {
$debug = false;
if (empty($lp_ids)) {
$debug = false;
}
if ($debug) echo '
Tracking::get_avg_student_score
';
$tbl_stats_exercices = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
$tbl_stats_attempts = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
$course = api_get_course_info($course_code);
if (!empty($course)) {
// get course tables names
$tbl_quiz_questions = Database :: get_course_table(TABLE_QUIZ_QUESTION);
$lp_table = Database :: get_course_table(TABLE_LP_MAIN);
$lp_item_table = Database :: get_course_table(TABLE_LP_ITEM);
$lp_view_table = Database :: get_course_table(TABLE_LP_VIEW);
$lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW);
$course_id = $course['real_id'];
// Compose a filter based on optional learning paths list given
$condition_lp = "";
if (count($lp_ids) > 0) {
$condition_lp =" AND id IN(".implode(',',$lp_ids).") ";
}
// Compose a filter based on optional session id
$session_id = intval($session_id);
if (count($lp_ids) > 0) {
$condition_session = " AND session_id = $session_id ";
} else {
$condition_session = " WHERE session_id = $session_id ";
}
// Check the real number of LPs corresponding to the filter in the
// database (and if no list was given, get them all)
if (empty($session_id)) {
$sql = "SELECT DISTINCT(id), use_max_score FROM $lp_table WHERE c_id = $course_id AND session_id = 0 $condition_lp ";
} else {
$sql = "SELECT DISTINCT(id), use_max_score FROM $lp_table WHERE c_id = $course_id $condition_lp ";
}
$res_row_lp = Database::query($sql);
$count_row_lp = Database::num_rows($res_row_lp);
$lp_list = $use_max_score = array();
while ($row_lp = Database::fetch_array($res_row_lp)) {
$lp_list[] = $row_lp['id'];
$use_max_score[$row_lp['id']] = $row_lp['use_max_score'];
}
if ($debug) {
echo 'Use max score or not list '; var_dump($use_max_score);
}
// Init local variables that will be used through the calculation
$progress = 0;
// prepare filter on users
$condition_user1 = "";
if (is_array($student_id)) {
array_walk($student_id, 'intval');
$condition_user1 =" AND user_id IN (".implode(',', $student_id).") ";
} else {
$condition_user1 =" AND user_id = $student_id ";
}
if ($count_row_lp > 0 && !empty($student_id)) {
// Getting latest LP result for a student
//@todo problem when a course have more than 1500 users
$sql = "SELECT MAX(view_count) as vc, id, progress, lp_id, user_id FROM $lp_view_table
WHERE c_id = $course_id AND
lp_id IN (".implode(',',$lp_list).")
$condition_user1 AND
session_id = $session_id
GROUP BY lp_id, user_id";
if ($debug) echo $sql;
$rs_last_lp_view_id = Database::query($sql);
$global_result = 0;
if (Database::num_rows($rs_last_lp_view_id) > 0) {
// Cycle through each line of the results (grouped by lp_id, user_id)
while ($row_lp_view = Database::fetch_array($rs_last_lp_view_id)) {
$count_items = 0;
$lp_partial_total = 0;
$list = array();
$lp_view_id = $row_lp_view['id'];
$progress = $row_lp_view['progress'];
$lp_id = $row_lp_view['lp_id'];
$user_id = $row_lp_view['user_id'];
if ($debug) echo 'LP id '.$lp_id.'
';
if ($get_only_latest_attempt_results) {
//Getting lp_items done by the user
$sql = "SELECT DISTINCT lp_item_id
FROM $lp_item_view_table
WHERE c_id = $course_id AND
lp_view_id = $lp_view_id
ORDER BY lp_item_id";
$res_lp_item = Database::query($sql);
while ($row_lp_item = Database::fetch_array($res_lp_item,'ASSOC')) {
$my_lp_item_id = $row_lp_item['lp_item_id'];
//Getting the most recent attempt
$sql = "SELECT lp_iv.id as lp_item_view_id,
lp_iv.score as score,
lp_i.max_score,
lp_iv.max_score as max_score_item_view,
lp_i.path,
lp_i.item_type,
lp_i.id as iid
FROM $lp_item_view_table as lp_iv
INNER JOIN $lp_item_table as lp_i
ON lp_i.id = lp_iv.lp_item_id AND
lp_iv.c_id = $course_id AND
lp_i.c_id = $course_id AND
(lp_i.item_type='sco' OR lp_i.item_type='".TOOL_QUIZ."')
WHERE lp_item_id = $my_lp_item_id AND
lp_view_id = $lp_view_id
ORDER BY view_count DESC
LIMIT 1";
$res_lp_item_result = Database::query($sql);
while ($row_max_score = Database::fetch_array($res_lp_item_result,'ASSOC')) {
$list[]= $row_max_score;
}
}
} else {
// For the currently analysed view, get the score and
// max_score of each item if it is a sco or a TOOL_QUIZ
$sql_max_score = "SELECT lp_iv.id as lp_item_view_id,
lp_iv.score as score,
lp_i.max_score,
lp_iv.max_score as max_score_item_view,
lp_i.path,
lp_i.item_type,
lp_i.id as iid
FROM $lp_item_view_table as lp_iv
INNER JOIN $lp_item_table as lp_i
ON lp_i.id = lp_iv.lp_item_id AND
lp_iv.c_id = $course_id AND
lp_i.c_id = $course_id AND
(lp_i.item_type='sco' OR lp_i.item_type='".TOOL_QUIZ."')
WHERE lp_view_id = $lp_view_id ";
if ($debug) echo $sql_max_score.'
';
$res_max_score = Database::query($sql_max_score);
while ($row_max_score = Database::fetch_array($res_max_score,'ASSOC')) {
$list[]= $row_max_score;
}
}
// Go through each scorable element of this view
$score_of_scorm_calculate = 0;
foreach ($list as $row_max_score) {
$max_score = $row_max_score['max_score']; //Came from the original lp_item
$max_score_item_view = $row_max_score['max_score_item_view']; //Came from the lp_item_view
$score = $row_max_score['score'];
if ($debug) echo 'Item Type: ' .$row_max_score['item_type'].'
';
if ($row_max_score['item_type'] == 'sco') {
// Check if it is sco (easier to get max_score)
//when there's no max score, we assume 100 as the max score, as the SCORM 1.2 says that the value should always be between 0 and 100.
if ($max_score == 0 || is_null($max_score) || $max_score == '') {
//Chamilo style
if ($use_max_score[$lp_id]) {
$max_score = 100;
} else {
//Overwrites max score = 100 to use the one that came in the lp_item_view see BT#1613
$max_score = $max_score_item_view;
}
}
//Avoid division by zero errors
if (!empty($max_score)) {
$lp_partial_total += $score/$max_score;
}
if ($debug) echo '$lp_partial_total, $score, $max_score '.$lp_partial_total.' '.$score.' '.$max_score.'
';
} else {
// Case of a TOOL_QUIZ element
$item_id = $row_max_score['iid'];
$item_path = $row_max_score['path'];
$lp_item_view_id = $row_max_score['lp_item_view_id'];
// Get last attempt to this exercise through
// the current lp for the current user
$sql_last_attempt = "SELECT exe_id FROM $tbl_stats_exercices WHERE
exe_exo_id = '$item_path' AND
exe_user_id = $user_id AND
orig_lp_item_id = $item_id AND
orig_lp_item_view_id = $lp_item_view_id AND
exe_cours_id = '$course_code' AND
session_id = $session_id
ORDER BY exe_date DESC LIMIT 1";
if ($debug) echo $sql_last_attempt .'
';
$result_last_attempt = Database::query($sql_last_attempt);
$num = Database :: num_rows($result_last_attempt);
if ($num > 0 ) {
$id_last_attempt = Database :: result($result_last_attempt, 0, 0);
if ($debug) echo $id_last_attempt.'
';
// Within the last attempt number tracking, get the sum of
// the max_scores of all questions that it was
// made of (we need to make this call dynamic because of random questions selection)
$sql = "SELECT SUM(t.ponderation) as maxscore FROM
(
SELECT DISTINCT question_id, marks, ponderation
FROM $tbl_stats_attempts AS at INNER JOIN $tbl_quiz_questions AS q ON (q.id = at.question_id)
WHERE exe_id ='$id_last_attempt' AND q.c_id = $course_id
)
AS t";
if ($debug) echo '$sql: '.$sql.'
';
$res_max_score_bis = Database::query($sql);
$row_max_score_bis = Database::fetch_array($res_max_score_bis);
if (!empty($row_max_score_bis['maxscore'])) {
$max_score = $row_max_score_bis['maxscore'];
}
if (!empty($max_score) && floatval($max_score) > 0) {
$lp_partial_total += $score/$max_score;
}
if ($debug) echo '$lp_partial_total, $score, $max_score '.$lp_partial_total.' '.$score.' '.$max_score.'
';
}
}
if (in_array($row_max_score['item_type'], array('quiz','sco'))) {
// Normal way
if ($use_max_score[$lp_id]) {
$count_items++;
} else {
if ($max_score != '') {
$count_items++;
}
}
if ($debug) echo '$count_items: '.$count_items;
}
} //end for
$score_of_scorm_calculate += $count_items?(($lp_partial_total/$count_items)*100):0;
if ($debug) echo '$count_items '.$count_items.'
';
if ($debug) echo '$score_of_scorm_calculate '.$score_of_scorm_calculate.'
';
$global_result += $score_of_scorm_calculate;
if ($debug) echo '$global_result '.$global_result.'
';
} // end while
}
$lp_with_quiz = 0;
if ($debug) var_dump($lp_list);
foreach ($lp_list as $lp_id) {
//Check if LP have a score we asume that all SCO have an score
$sql = "SELECT count(id) as count FROM $lp_item_table
WHERE c_id = $course_id AND (item_type = 'quiz' OR item_type = 'sco') AND lp_id = ".$lp_id;
if ($debug) echo $sql;
$result_have_quiz = Database::query($sql);
if (Database::num_rows($result_have_quiz) > 0 ) {
$row = Database::fetch_array($result_have_quiz,'ASSOC');
if (is_numeric($row['count']) && $row['count'] != 0) {
$lp_with_quiz ++;
}
}
}
if ($debug) echo '$lp_with_quiz '.$lp_with_quiz.'
';
if ($debug) echo 'Final return
';
if ($lp_with_quiz != 0 ) {
if (!$return_array) {
$score_of_scorm_calculate = round(($global_result/$lp_with_quiz),2);
if ($debug) var_dump($score_of_scorm_calculate);
if (empty($lp_ids)) {
//$score_of_scorm_calculate = round($score_of_scorm_calculate/count($lp_list),2);
if ($debug) echo 'All lps fix: '.$score_of_scorm_calculate.'
';
}
return $score_of_scorm_calculate;
} else {
if ($debug) var_dump($global_result, $lp_with_quiz);
return array($global_result, $lp_with_quiz);
}
} else {
return '-';
}
}
}
return null;
}
/**
* This function gets:
* 1. The score average from all SCORM Test items in all LP in a course-> All the answers / All the max scores.
* 2. The score average from all Tests (quiz) in all LP in a course-> All the answers / All the max scores.
* 3. And finally it will return the average between 1. and 2.
* This function does not take the results of a Test out of a LP
*
* @param int|array Array of user ids or an user id
* @param string Course code
* @param array List of LP ids
* @param int Session id (optional), if param $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
* @param bool Returns an array of the type [sum_score, num_score] if set to true
* @param bool get only the latest attempts or ALL attempts
* @return string Value (number %) Which represents a round integer explain in got in 3.
*/
public static function getAverageStudentScore(
$student_id,
$course_code = null,
$lp_ids = array(),
$session_id = null
) {
if (empty($student_id)) {
return 0;
}
$conditions = array();
if (!empty($course_code)) {
$course = api_get_course_info($course_code);
$courseId = $course['real_id'];
$conditions[] = " c_id = $courseId";
}
// get course tables names
$tbl_quiz_questions = Database :: get_course_table(TABLE_QUIZ_QUESTION);
$lp_table = Database :: get_course_table(TABLE_LP_MAIN);
$lp_item_table = Database :: get_course_table(TABLE_LP_ITEM);
$lp_view_table = Database :: get_course_table(TABLE_LP_VIEW);
$lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW);
// Compose a filter based on optional learning paths list given
if (!empty($lp_ids) && count($lp_ids) > 0) {
$conditions[] = " id IN(".implode(',', $lp_ids).") ";
}
// Compose a filter based on optional session id
$session_id = intval($session_id);
if (!empty($session_id)) {
$conditions[] = " session_id = $session_id ";
}
if (is_array($student_id)) {
array_walk($student_id, 'intval');
$conditions[] =" lp_view.user_id IN (".implode(',', $student_id).") ";
} else {
$conditions[] =" lp_view.user_id = $student_id ";
}
$conditionsToString = implode('AND ', $conditions);
$sql = "SELECT SUM(lp_iv.score) sum_score,
SUM(lp_i.max_score) sum_max_score,
count(*) as count
FROM $lp_table as lp
INNER JOIN $lp_item_table as lp_i
ON lp.id = lp_id AND lp.c_id = lp_i.c_id
INNER JOIN $lp_view_table as lp_view
ON lp_view.lp_id = lp_i.lp_id AND lp_view.c_id = lp_i.c_id
INNER JOIN $lp_item_view_table as lp_iv
ON lp_i.id = lp_iv.lp_item_id AND lp_view.c_id = lp_iv.c_id AND lp_iv.lp_view_id = lp_view.id
WHERE (lp_i.item_type='sco' OR lp_i.item_type='".TOOL_QUIZ."') AND
$conditionsToString
";
$result = Database::query($sql);
$row = Database::fetch_array($result, 'ASSOC');
if (empty($row['sum_max_score'])) {
return 0;
}
return ($row['sum_score'] / $row['sum_max_score'])*100;
}
/**
* This function gets time spent in learning path for a student inside a course
* @param int|array Student id(s)
* @param string Course code
* @param array Limit average to listed lp ids
* @param int Session id (optional), if param $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
* @return int Total time
*/
public static function get_time_spent_in_lp($student_id, $course_code, $lp_ids = array(), $session_id = null)
{
$course = CourseManager :: get_course_information($course_code);
$student_id = intval($student_id);
$total_time = 0;
if (!empty($course)) {
$lp_table = Database :: get_course_table(TABLE_LP_MAIN);
$t_lpv = Database :: get_course_table(TABLE_LP_VIEW);
$t_lpiv = Database :: get_course_table(TABLE_LP_ITEM_VIEW);
$course_id = $course['real_id'];
// Compose a filter based on optional learning paths list given
$condition_lp = "";
if(count($lp_ids) > 0) {
$condition_lp =" AND id IN(".implode(',',$lp_ids).") ";
}
// Compose a filter based on optional session id
$condition_session = "";
$session_id = intval($session_id);
if (isset($session_id)) {
$condition_session = " AND session_id = $session_id ";
}
// Check the real number of LPs corresponding to the filter in the
// database (and if no list was given, get them all)
//$res_row_lp = Database::query("SELECT DISTINCT(id) FROM $lp_table $condition_lp $condition_session");
$res_row_lp = Database::query("SELECT DISTINCT(id) FROM $lp_table WHERE c_id = $course_id $condition_lp");
$count_row_lp = Database::num_rows($res_row_lp);
// calculates time
if ($count_row_lp > 0) {
while ($row_lp = Database::fetch_array($res_row_lp)) {
$lp_id = intval($row_lp['id']);
$sql = 'SELECT SUM(total_time)
FROM '.$t_lpiv.' AS item_view
INNER JOIN '.$t_lpv.' AS view
ON item_view.lp_view_id = view.id
WHERE
item_view.c_id = '.$course_id.' AND
view.c_id = '.$course_id.' AND
view.lp_id = '.$lp_id.'
AND view.user_id = '.$student_id.' AND
session_id = '.$session_id;
$rs = Database::query($sql);
if (Database :: num_rows($rs) > 0) {
$total_time += Database :: result($rs, 0, 0);
}
}
}
}
return $total_time;
}
/**
* This function gets last connection time to one learning path
* @param int|array Student id(s)
* @param string Course code
* @param int Learning path id
* @return int Total time
*/
public static function get_last_connection_time_in_lp($student_id, $course_code, $lp_id, $session_id = 0)
{
$course = CourseManager :: get_course_information($course_code);
$student_id = intval($student_id);
$lp_id = intval($lp_id);
$last_time = 0;
$session_id = intval($session_id);
if (!empty($course)) {
$course_id = $course['real_id'];
$lp_table = Database :: get_course_table(TABLE_LP_MAIN);
$t_lpv = Database :: get_course_table(TABLE_LP_VIEW);
$t_lpiv = Database :: get_course_table(TABLE_LP_ITEM_VIEW);
// Check the real number of LPs corresponding to the filter in the
// database (and if no list was given, get them all)
$res_row_lp = Database::query("SELECT id FROM $lp_table WHERE c_id = $course_id AND id = $lp_id ");
$count_row_lp = Database::num_rows($res_row_lp);
// calculates last connection time
if ($count_row_lp > 0) {
$sql = 'SELECT MAX(start_time)
FROM ' . $t_lpiv . ' AS item_view
INNER JOIN ' . $t_lpv . ' AS view
ON item_view.lp_view_id = view.id
WHERE
item_view.c_id = '.$course_id.' AND
view.c_id = '.$course_id.' AND
view.lp_id = '.$lp_id.'
AND view.user_id = '.$student_id.'
AND view.session_id = '.$session_id;
$rs = Database::query($sql);
if (Database :: num_rows($rs) > 0) {
$last_time = Database :: result($rs, 0, 0);
}
}
}
return $last_time;
}
/**
* gets the list of students followed by coach
* @param int Coach id
* @return array List of students
*/
public static function get_student_followed_by_coach($coach_id)
{
$coach_id = intval($coach_id);
$tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
$tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
$a_students = array ();
// At first, courses where $coach_id is coach of the course //
$sql = 'SELECT id_session, course_code
FROM ' . $tbl_session_course_user . '
WHERE id_user=' . $coach_id.' AND status=2';
if (api_is_multiple_url_enabled()) {
$tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
$access_url_id = api_get_current_access_url_id();
if ($access_url_id != -1) {
$sql = 'SELECT scu.id_session, scu.course_code
FROM ' . $tbl_session_course_user . ' scu
INNER JOIN '.$tbl_session_rel_access_url.' sru
ON (scu.id_session=sru.session_id)
WHERE
scu.id_user=' . $coach_id.' AND
scu.status=2 AND
sru.access_url_id = '.$access_url_id;
}
}
$result = Database::query($sql);
while ($a_courses = Database::fetch_array($result)) {
$course_code = $a_courses["course_code"];
$id_session = $a_courses["id_session"];
$sql = "SELECT distinct srcru.id_user
FROM $tbl_session_course_user AS srcru, $tbl_session_user sru
WHERE
srcru.id_user = sru.id_user AND
sru.relation_type<>".SESSION_RELATION_TYPE_RRHH." AND
srcru.id_session = sru.id_session AND
srcru.course_code='$course_code' AND
srcru.id_session='$id_session'";
$rs = Database::query($sql);
while ($row = Database::fetch_array($rs)) {
$a_students[$row['id_user']] = $row['id_user'];
}
}
// Then, courses where $coach_id is coach of the session //
$sql = 'SELECT session_course_user.id_user
FROM ' . $tbl_session_course_user . ' as session_course_user
INNER JOIN '.$tbl_session_user.' sru ON session_course_user.id_user = sru.id_user AND session_course_user.id_session = sru.id_session
INNER JOIN ' . $tbl_session_course . ' as session_course
ON session_course.course_code = session_course_user.course_code
AND session_course_user.id_session = session_course.id_session
INNER JOIN ' . $tbl_session . ' as session
ON session.id = session_course.id_session
AND session.id_coach = ' . $coach_id;
if (api_is_multiple_url_enabled()) {
$tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
$access_url_id = api_get_current_access_url_id();
if ($access_url_id != -1){
$sql = 'SELECT session_course_user.id_user
FROM ' . $tbl_session_course_user . ' as session_course_user
INNER JOIN '.$tbl_session_user.' sru
ON session_course_user.id_user = sru.id_user AND
session_course_user.id_session = sru.id_session
INNER JOIN ' . $tbl_session_course . ' as session_course
ON session_course.course_code = session_course_user.course_code AND
session_course_user.id_session = session_course.id_session
INNER JOIN ' . $tbl_session . ' as session
ON session.id = session_course.id_session AND
session.id_coach = ' . $coach_id.'
INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
ON session.id = session_rel_url.session_id WHERE access_url_id = '.$access_url_id;
}
}
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
$a_students[$row['id_user']] = $row['id_user'];
}
return $a_students;
}
/**
* Get student followed by a coach inside a session
* @param int Session id
* @param int Coach id
* @return array students list
*/
public static function get_student_followed_by_coach_in_a_session($id_session, $coach_id)
{
$coach_id = intval($coach_id);
$tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
$a_students = array ();
// At first, courses where $coach_id is coach of the course //
$sql = 'SELECT course_code FROM ' . $tbl_session_course_user . ' WHERE id_session="' . $id_session . '" AND id_user=' . $coach_id.' AND status=2';
$result = Database::query($sql);
while ($a_courses = Database::fetch_array($result)) {
$course_code = $a_courses["course_code"];
$sql = "SELECT distinct srcru.id_user
FROM $tbl_session_course_user AS srcru
WHERE
course_code='$course_code' AND
id_session = '" . $id_session . "'";
$rs = Database::query($sql);
while ($row = Database::fetch_array($rs)) {
$a_students[$row['id_user']] = $row['id_user'];
}
}
// Then, courses where $coach_id is coach of the session
$sql = 'SELECT id_coach FROM ' . $tbl_session . '
WHERE id="' . $id_session.'" AND id_coach="' . $coach_id . '"';
$result = Database::query($sql);
//He is the session_coach so we select all the users in the session
if (Database::num_rows($result) > 0) {
$sql = 'SELECT DISTINCT srcru.id_user
FROM ' . $tbl_session_course_user . ' AS srcru
WHERE id_session="' . $id_session . '"';
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
$a_students[$row['id_user']] = $row['id_user'];
}
}
return $a_students;
}
/**
* Check if a coach is allowed to follow a student
* @param int Coach id
* @param int Student id
* @return bool
*/
public static function is_allowed_to_coach_student($coach_id, $student_id)
{
$coach_id = intval($coach_id);
$student_id = intval($student_id);
$tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
// At first, courses where $coach_id is coach of the course //
$sql = 'SELECT 1 FROM ' . $tbl_session_course_user . ' WHERE id_user=' . $coach_id .' AND status=2';
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
return true;
}
// Then, courses where $coach_id is coach of the session
$sql = 'SELECT session_course_user.id_user
FROM ' . $tbl_session_course_user . ' as session_course_user
INNER JOIN ' . $tbl_session_course . ' as session_course
ON session_course.course_code = session_course_user.course_code
INNER JOIN ' . $tbl_session . ' as session
ON session.id = session_course.id_session
AND session.id_coach = ' . $coach_id . '
WHERE id_user = ' . $student_id;
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
return true;
}
return false;
}
/**
* Get courses followed by coach
* @param int Coach id
* @param int Session id (optional)
* @return array Courses list
*/
public static function get_courses_followed_by_coach($coach_id, $id_session = null)
{
$coach_id = intval($coach_id);
if (!empty($id_session)) {
$id_session = intval($id_session);
}
$tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
// At first, courses where $coach_id is coach of the course.
$sql = 'SELECT DISTINCT course_code
FROM ' . $tbl_session_course_user . '
WHERE id_user = ' . $coach_id.' AND status = 2';
if (api_is_multiple_url_enabled()) {
$tbl_course_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$access_url_id = api_get_current_access_url_id();
if ($access_url_id != -1){
$sql = 'SELECT DISTINCT scu.course_code
FROM ' . $tbl_session_course_user . ' scu
INNER JOIN '.$tbl_course_rel_access_url.' cru
ON (scu.course_code = cru.course_code)
WHERE
scu.id_user=' . $coach_id.' AND
scu.status=2 AND
cru.access_url_id = '.$access_url_id;
}
}
if (!empty($id_session)) {
$sql .= ' AND id_session=' . $id_session;
}
$courseList = array();
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
$courseList[$row['course_code']] = $row['course_code'];
}
// Then, courses where $coach_id is coach of the session
$sql = 'SELECT DISTINCT session_course.course_code
FROM ' . $tbl_session_course . ' as session_course
INNER JOIN ' . $tbl_session . ' as session
ON session.id = session_course.id_session
AND session.id_coach = ' . $coach_id . '
INNER JOIN ' . $tbl_course . ' as course
ON course.code = session_course.course_code';
if (api_is_multiple_url_enabled()) {
$tbl_course_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$access_url_id = api_get_current_access_url_id();
if ($access_url_id != -1){
$sql = 'SELECT DISTINCT session_course.course_code
FROM ' . $tbl_session_course . ' as session_course
INNER JOIN ' . $tbl_session . ' as session
ON session.id = session_course.id_session
AND session.id_coach = ' . $coach_id . '
INNER JOIN ' . $tbl_course . ' as course
ON course.code = session_course.course_code
INNER JOIN '.$tbl_course_rel_access_url.' course_rel_url
ON (session_course.course_code = course_rel_url.course_code)';
}
}
if (!empty ($id_session)) {
$sql .= ' WHERE session_course.id_session=' . $id_session;
if (api_is_multiple_url_enabled())
$sql .= ' AND access_url_id = '.$access_url_id;
} else {
if (api_is_multiple_url_enabled())
$sql .= ' WHERE access_url_id = '.$access_url_id;
}
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
$courseList[$row['course_code']] = $row['course_code'];
}
return $courseList;
}
/**
* Get sessions coached by user
* @param $coach_id
* @param int $start
* @param int $limit
* @param bool $getCount
* @param null $keyword
* @return mixed
*/
public static function get_sessions_coached_by_user(
$coach_id,
$start = 0,
$limit = 0,
$getCount = false,
$keyword = null
) {
// table definition
$tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
$tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$coach_id = intval($coach_id);
$select = " SELECT * FROM ";
if ($getCount) {
$select = " SELECT count(DISTINCT id) as count FROM ";
}
$limitCondition = null;
if (!empty($start) && !empty($limit)) {
$limitCondition = " LIMIT ".intval($start).", ".intval($limit);
}
$keywordCondition = null;
if (!empty($keyword)) {
$keyword = Database::escape_string($keyword);
$keywordCondition = " AND (name LIKE '%$keyword%' ) ";
}
$tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
$access_url_id = api_get_current_access_url_id();
$sql = "
$select
(
SELECT DISTINCT id, name, date_start, date_end
FROM $tbl_session session INNER JOIN $tbl_session_rel_access_url session_rel_url
ON (session.id = session_rel_url.session_id)
WHERE id_coach = $coach_id AND access_url_id = $access_url_id $keywordCondition
UNION
SELECT DISTINCT session.id, session.name, session.date_start, session.date_end
FROM $tbl_session as session
INNER JOIN $tbl_session_course_user as session_course_user
ON session.id = session_course_user.id_session AND
session_course_user.id_user= $coach_id AND
session_course_user.status=2
INNER JOIN $tbl_session_rel_access_url session_rel_url
ON (session.id = session_rel_url.session_id)
WHERE access_url_id = $access_url_id $keywordCondition
) as sessions $limitCondition
";
$rs = Database::query($sql);
if ($getCount) {
$row = Database::fetch_array($rs);
return $row['count'];
}
while ($row = Database::fetch_array($rs)) {
$a_sessions[$row["id"]] = $row;
}
if (is_array($a_sessions)) {
foreach ($a_sessions as & $session) {
if ($session['date_start'] == '0000-00-00') {
$session['status'] = get_lang('SessionActive');
}
else {
$date_start = explode('-', $session['date_start']);
$time_start = mktime(0, 0, 0, $date_start[1], $date_start[2], $date_start[0]);
$date_end = explode('-', $session['date_end']);
$time_end = mktime(0, 0, 0, $date_end[1], $date_end[2], $date_end[0]);
if ($time_start < time() && time() < $time_end) {
$session['status'] = get_lang('SessionActive');
} else {
if (time() < $time_start) {
$session['status'] = get_lang('SessionFuture');
} else {
if (time() > $time_end) {
$session['status'] = get_lang('SessionPast');
}
}
}
}
}
}
return $a_sessions;
}
/**
* Get courses list from a session
* @param int Session id
* @return array Courses list
*/
public static function get_courses_list_from_session($session_id)
{
$session_id = intval($session_id);
// table definition
$tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
$sql = 'SELECT DISTINCT course_code
FROM ' . $tbl_session_course . '
WHERE id_session=' . $session_id;
$rs = Database::query($sql);
$a_courses = array ();
while ($row = Database::fetch_array($rs)) {
$a_courses[$row['course_code']] = $row;
}
return $a_courses;
}
/**
* Count the number of documents that an user has uploaded to a course
* @param int|array Student id(s)
* @param string Course code
* @param int Session id (optional), if param $session_id is null(default) return count of assignments including sessions, 0 = session is not filtered
* @return int Number of documents
*/
public static function count_student_uploaded_documents($student_id, $course_code, $session_id = null)
{
// get the information of the course
$a_course = CourseManager::get_course_information($course_code);
if (!empty($a_course)) {
// table definition
$tbl_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY);
$tbl_document = Database :: get_course_table(TABLE_DOCUMENT);
$course_id = $a_course['real_id'];
if (is_array($student_id)) {
$studentList = array_map('intval', $student_id);
$condition_user = " AND ip.insert_user_id IN ('".implode(',', $studentList)."') ";
} else {
$student_id = intval($student_id);
$condition_user = " AND ip.insert_user_id = '$student_id' ";
}
$condition_session = null;
if (isset($session_id)) {
$session_id = intval($session_id);
$condition_session = " AND pub.session_id = $session_id ";
}
$sql = "SELECT count(ip.tool) AS count
FROM $tbl_item_property ip INNER JOIN $tbl_document pub
ON ip.ref = pub.id
WHERE ip.c_id = $course_id AND
pub.c_id = $course_id AND
pub.filetype ='file' AND
ip.tool = 'document'
$condition_user $condition_session ";
$rs = Database::query($sql);
$row = Database::fetch_row($rs);
return $row['count'];
}
return null;
}
/**
* Count assignments per student
* @param int|array Student id(s)
* @param string Course code
* @param int Session id (optional), if param $session_id is null(default) return count of assignments including sessions, 0 = session is not filtered
* @return int Count of assignments
*/
public static function count_student_assignments($student_id, $course_code = null, $session_id = null)
{
if (empty($student_id)) {
return 0;
}
$conditions = array();
// Get the information of the course
$a_course = CourseManager::get_course_information($course_code);
if (!empty($a_course)) {
$course_id = $a_course['real_id'];
$conditions[]= " ip.c_id = $course_id AND pub.c_id = $course_id ";
}
// table definition
$tbl_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY);
$tbl_student_publication = Database :: get_course_table(TABLE_STUDENT_PUBLICATION);
if (is_array($student_id)) {
$studentList = array_map('intval', $student_id);
$conditions[]= " ip.insert_user_id IN ('".implode("','", $studentList)."') ";
} else {
$student_id = intval($student_id);
$conditions[]= " ip.insert_user_id = '$student_id' ";
}
if (isset($session_id)) {
$session_id = intval($session_id);
$conditions[]= " pub.session_id = $session_id ";
}
$conditionToString = implode('AND', $conditions);
$sql = "SELECT count(ip.tool) as count
FROM $tbl_item_property ip
INNER JOIN $tbl_student_publication pub ON ip.ref = pub.id
WHERE
ip.tool='work' AND
$conditionToString";
$rs = Database::query($sql);
$row = Database::fetch_array($rs, 'ASSOC');
return $row['count'];
}
/**
* Count messages per student inside forum tool
* @param int|array Student id
* @param string Course code
* @param int Session id (optional), if param $session_id is
* null(default) return count of messages including sessions, 0 = session is not filtered
* @return int Count of messages
*/
public static function count_student_messages($student_id, $courseCode = null, $session_id = null)
{
if (empty($student_id)) {
return 0;
}
$courseInfo = api_get_course_info($courseCode);
$courseCondition = null;
$conditions = array();
if (!empty($courseInfo)) {
$course_id = $courseInfo['real_id'];
$conditions[]= " post.c_id = $course_id AND forum.c_id = $course_id ";
}
// Table definition.
$tbl_forum_post = Database :: get_course_table(TABLE_FORUM_POST);
$tbl_forum = Database :: get_course_table(TABLE_FORUM);
if (is_array($student_id)) {
$studentList = array_map('intval', $student_id);
$conditions[]= " post.poster_id IN ('".implode("','", $studentList)."') ";
} else {
$student_id = intval($student_id);
$conditions[]= " post.poster_id = '$student_id' ";
}
if (isset($session_id)) {
$session_id = intval($session_id);
$conditions[]= " forum.session_id = $session_id";
}
$conditionsToString = implode('AND ', $conditions);
$sql = "SELECT count(poster_id) as count
FROM $tbl_forum_post post INNER JOIN $tbl_forum forum
ON forum.forum_id = post.forum_id
WHERE $conditionsToString";
$rs = Database::query($sql);
$row = Database::fetch_array($rs, 'ASSOC');
$count = $row['count'];
return $count;
}
/**
* This function counts the number of post by course
* @param string Course code
* @param int Session id (optional), if param $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
* @return int The number of post by course
*/
public static function count_number_of_posts_by_course($course_code, $session_id = null)
{
$a_course = CourseManager :: get_course_information($course_code);
if (!empty($a_course)) {
$tbl_posts = Database :: get_course_table(TABLE_FORUM_POST);
$tbl_forums = Database :: get_course_table(TABLE_FORUM);
$condition_session = '';
if (isset($session_id)) {
$session_id = intval($session_id);
$condition_session = ' AND f.session_id = '. $session_id;
}
$course_id = $a_course['real_id'];
$sql = "SELECT count(*) FROM $tbl_posts p INNER JOIN $tbl_forums f
ON f.forum_id = p.forum_id
WHERE p.c_id = $course_id AND
f.c_id = $course_id
$condition_session
";
$result = Database::query($sql);
$row = Database::fetch_row($result);
$count = $row[0];
return $count;
} else {
return null;
}
}
/**
* This function counts the number of threads by course
* @param string Course code
* @param int Session id (optional), if param $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
* @return int The number of threads by course
*/
public static function count_number_of_threads_by_course($course_code, $session_id = null)
{
$course_info = api_get_course_info($course_code);
if (empty($course_info)) {
return null;
}
$course_id = $course_info['real_id'];
$tbl_threads = Database :: get_course_table(TABLE_FORUM_THREAD);
$tbl_forums = Database :: get_course_table(TABLE_FORUM);
$condition_session = '';
if (isset($session_id)) {
$session_id = intval($session_id);
$condition_session = ' AND f.session_id = '. $session_id;
}
$sql = "SELECT count(*) FROM $tbl_threads t INNER JOIN $tbl_forums f ON f.forum_id = t.forum_id
WHERE t.c_id = $course_id AND f.c_id = $course_id $condition_session ";
$result = Database::query($sql);
if (Database::num_rows($result)) {
$row = Database::fetch_row($result);
$count = $row[0];
return $count;
} else {
return null;
}
}
/**
* This function counts the number of forums by course
* @param string Course code
* @param int Session id (optional), if param $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
* @return int The number of forums by course
*/
public static function count_number_of_forums_by_course($course_code, $session_id = null)
{
$course_info = api_get_course_info($course_code);
if (empty($course_info)) {
return null;
}
$course_id = $course_info['real_id'];
$condition_session = '';
if (isset($session_id)) {
$session_id = intval($session_id);
$condition_session = ' session_id = '. $session_id;
}
$tbl_forums = Database :: get_course_table(TABLE_FORUM);
$sql = "SELECT count(*) FROM $tbl_forums WHERE c_id = $course_id AND $condition_session";
$result = Database::query($sql);
if (Database::num_rows($result)) {
$row = Database::fetch_row($result);
$count = $row[0];
return $count;
} else {
return null;
}
}
/**
* This function counts the chat last connections by course in x days
* @param string Course code
* @param int Last x days
* @param int Session id (optional)
* @return int Chat last connections by course in x days
*/
public static function chat_connections_during_last_x_days_by_course($course_code, $last_days, $session_id = 0)
{
$course_info = api_get_course_info($course_code);
if (empty($course_info)) {
return null;
}
$course_id = $course_info['real_id'];
//protect data
$last_days = intval($last_days);
$course_code = Database::escape_string($course_code);
$session_id = intval($session_id);
$tbl_stats_access = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS);
$sql = "SELECT count(*) FROM $tbl_stats_access WHERE DATE_SUB(NOW(),INTERVAL $last_days DAY) <= access_date
AND access_cours_code = '$course_code' AND access_tool='".TOOL_CHAT."' AND access_session_id='$session_id' ";
$result = Database::query($sql);
if (Database::num_rows($result)) {
$row = Database::fetch_row($result);
$count = $row[0];
return $count;
} else {
return null;
}
}
/**
* This function gets the last student's connection in chat
* @param int Student id
* @param string Course code
* @param int Session id (optional)
* @return string datetime formatted without day (e.g: February 23, 2010 10:20:50 )
*/
public static function chat_last_connection($student_id, $course_code, $session_id = 0)
{
$student_id = intval($student_id);
$course_code= Database::escape_string($course_code);
$session_id = intval($session_id);
$date_time = '';
// table definition
$tbl_stats_access = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
$sql = "SELECT access_date
FROM $tbl_stats_access
WHERE
access_tool='".TOOL_CHAT."' AND
access_user_id='$student_id' AND
access_cours_code = '$course_code' AND
access_session_id = '$session_id'
ORDER BY access_date DESC limit 1";
$rs = Database::query($sql);
if (Database::num_rows($rs) > 0) {
$row = Database::fetch_array($rs);
$date_time = api_convert_and_format_date($row['access_date'], null, date_default_timezone_get());
}
return $date_time;
}
/**
* Get count student's visited links
* @param int Student id
* @param string Course code
* @param int Session id (optional)
* @return int count of visited links
*/
public static function count_student_visited_links($student_id, $course_code, $session_id = 0)
{
// protect datas
$student_id = intval($student_id);
$course_code = Database::escape_string($course_code);
$session_id = intval($session_id);
// table definition
$tbl_stats_links = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LINKS);
$sql = 'SELECT 1
FROM '.$tbl_stats_links.'
WHERE
links_user_id= '.$student_id.' AND
links_cours_id = "'.$course_code.'" AND
links_session_id = '.$session_id.' ';
$rs = Database::query($sql);
return Database::num_rows($rs);
}
/**
* Get count student downloaded documents
* @param int Student id
* @param string Course code
* @param int Session id (optional)
* @return int Count downloaded documents
*/
public static function count_student_downloaded_documents($student_id, $course_code, $session_id = 0)
{
// protect datas
$student_id = intval($student_id);
$course_code = Database::escape_string($course_code);
$session_id = intval($session_id);
// table definition
$tbl_stats_documents = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
$sql = 'SELECT 1
FROM ' . $tbl_stats_documents . '
WHERE down_user_id = '.$student_id.'
AND down_cours_id = "'.$course_code.'"
AND down_session_id = '.$session_id.' ';
$rs = Database::query($sql);
return Database::num_rows($rs);
}
/**
* Get course list inside a session from a student
* @param int Student id
* @param int Session id (optional)
* @return array Courses list
*/
public static function get_course_list_in_session_from_student($user_id, $id_session = 0)
{
$user_id = intval($user_id);
$id_session = intval($id_session);
$tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$sql = 'SELECT course_code
FROM ' . $tbl_session_course_user . '
WHERE
id_user="' . $user_id . '" AND
id_session="' . $id_session . '"';
$result = Database::query($sql);
$a_courses = array ();
while ($row = Database::fetch_array($result)) {
$a_courses[$row['course_code']] = $row['course_code'];
}
return $a_courses;
}
/**
* Get inactives students in course
* @param string Course code
* @param string Since login course date (optional, default = 'never')
* @param int Session id (optional)
* @return array Inactives users
*/
public static function get_inactives_students_in_course($course_code, $since = 'never', $session_id=0)
{
$tbl_track_login = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
$tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$table_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
$inner = '';
if ($session_id!=0) {
$inner = ' INNER JOIN '.$tbl_session_course_user.' session_course_user
ON stats_login.course_code = session_course_user.course_code
AND session_course_user.id_session = '.intval($session_id).'
AND session_course_user.id_user = stats_login.user_id ';
}
$sql = 'SELECT user_id, MAX(login_course_date) max_date FROM'.$tbl_track_login.' stats_login'.$inner.'
GROUP BY user_id
HAVING DATE_SUB( NOW(), INTERVAL '.$since.' DAY) > max_date ';
//HAVING DATE_ADD(max_date, INTERVAL '.$since.' DAY) < NOW() ';
if ($since == 'never') {
$sql = 'SELECT course_user.user_id FROM '.$table_course_rel_user.' course_user
LEFT JOIN '. $tbl_track_login.' stats_login
ON course_user.user_id = stats_login.user_id AND relation_type<>'.COURSE_RELATION_TYPE_RRHH.' '.
$inner.'
WHERE course_user.course_code = \''.Database::escape_string($course_code).'\'
AND stats_login.login_course_date IS NULL
GROUP BY course_user.user_id';
}
$rs = Database::query($sql);
$inactive_users = array();
while($user = Database::fetch_array($rs)) {
$inactive_users[] = $user['user_id'];
}
return $inactive_users;
}
/**
* Get count login per student
* @param int Student id
* @param string Course code
* @param int Session id (optional)
* @return int count login
*/
public static function count_login_per_student($student_id, $course_code, $session_id = 0)
{
$student_id = intval($student_id);
$course_code = Database::escape_string($course_code);
$session_id = intval($session_id);
$tbl_course_rel_user = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS);
$sql = 'SELECT '.$student_id.'
FROM ' . $tbl_course_rel_user . '
WHERE access_user_id=' . $student_id . '
AND access_cours_code="' . $course_code . '" AND access_session_id = "'.$session_id.'" ';
$rs = Database::query($sql);
$nb_login = Database::num_rows($rs);
return $nb_login;
}
/**
* Get students followed by a human resources manager
* @param int Drh id
* @return array Student list
*/
public static function get_student_followed_by_drh($hr_dept_id)
{
$hr_dept_id = intval($hr_dept_id);
$a_students = array();
$tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
$sql = 'SELECT DISTINCT user_id FROM '.$tbl_user.' as user
WHERE hr_dept_id='.$hr_dept_id;
$rs = Database::query($sql);
while($user = Database :: fetch_array($rs)) {
$a_students[$user['user_id']] = $user['user_id'];
}
return $a_students;
}
/**
* Gets the average of test and scorm inside a learning path
* @param int User id
* @param string Course id
* @return float average of test
* @author isaac flores paz
* @deprecated get_avg_student_score should be use
*/
public static function get_average_test_scorm_and_lp ($user_id, $course_id)
{
//the score inside the Reporting table
$course_info = api_get_course_info($course_id);
$course_id = $course_info['real_id'];
$lp_table = Database :: get_course_table(TABLE_LP_MAIN);
$lp_view_table = Database :: get_course_table(TABLE_LP_VIEW);
$lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW);
$lp_item_table = Database :: get_course_table(TABLE_LP_ITEM);
$sql_type = "SELECT id, lp_type FROM $lp_table WHERE c_id = $course_id";
$rs_type=Database::query($sql_type);
$average_data=0;
$count_loop=0;
$lp_list = array();
while ($row_type = Database::fetch_array($rs_type)) {
$lp_list[] = $row_type['id'];
if ($row_type['lp_type']==1) {
//lp chamilo
$sql = "SELECT id FROM $lp_view_table WHERE c_id = $course_id AND user_id = '".intval($user_id)."' and lp_id='".$row_type['id']."'";
$rs_last_lp_view_id = Database::query($sql);
$lp_view_id = intval(Database::result($rs_last_lp_view_id,0,'id'));
$sql_list_view="SELECT li.max_score,lv.user_id,liw.score,(liw.score/li.max_score) as sum_data
FROM $lp_item_table li
INNER JOIN $lp_view_table lv
ON li.lp_id=lv.lp_id
INNER JOIN $lp_item_view_table liw
ON liw.lp_item_id=li.id
WHERE li.c_id = $course_id AND
liw.c_id = $course_id AND
lv.c_id = $course_id AND
lv.user_id= $user_id AND
li.item_type = 'quiz' AND
liw.lp_view_id= $lp_view_id";
$sum=0;
$tot=0;
$rs_list_view1=Database::query($sql_list_view);
while ($row_list_view=Database::fetch_array($rs_list_view1)) {
$sum=$sum+$row_list_view['sum_data'];
$tot++;
}
if ($tot==0) {
$tot=1;
}
$average_data1=$sum/$tot;
$sql_list_view='';
$rs_last_lp_view_id='';
} elseif ($row_type['lp_type']==2) {
//lp scorm
$sql = "SELECT id FROM $lp_view_table WHERE c_id = $course_id AND user_id = '".intval($user_id)."' and lp_id='".$row_type['id']."'";
$rs_last_lp_view_id = Database::query($sql);
$lp_view_id = intval(Database::result($rs_last_lp_view_id,0,'id'));
$sql_list_view = "SELECT li.max_score,lv.user_id,liw.score,((liw.score/li.max_score)*100) as sum_data
FROM $lp_item_table li
INNER JOIN $lp_view_table lv
ON li.lp_id=lv.lp_id
INNER JOIN $lp_item_view_table liw ON liw.lp_item_id=li.id
WHERE li.c_id = $course_id AND
liw.c_id = $course_id AND
lv.c_id = $course_id AND
lv.user_id= $user_id AND
(li.item_type = 'sco' OR li.item_type='quiz') AND
liw.lp_view_id = $lp_view_id";
$tot=0;
$sum=0;
$rs_list_view2=Database::query($sql_list_view);
while ($row_list_view=Database::fetch_array($rs_list_view2)) {
$sum=$sum+$row_list_view['sum_data'];
$tot++;
}
if ($tot==0) {
$tot=1;
}
$average_data2=$sum/$tot;
}
$average_data_sum = $average_data_sum+$average_data1+$average_data2;
$average_data2=0;
$average_data1=0;
$count_loop++;
}
//We only count the LP that have an exercise to get the average
$lp_with_quiz = 0;
foreach($lp_list as $lp_id) {
//check if LP have a score
$sql = "SELECT count(id) as count FROM $lp_item_table
WHERE c_id = $course_id AND item_type = 'quiz' AND lp_id = ".$lp_id." ";
$result_have_quiz = Database::query($sql);
if (Database::num_rows($result_have_quiz) > 0 ) {
$row = Database::fetch_array($result_have_quiz,'ASSOC');
if (is_numeric($row['count']) && $row['count'] != 0) {
$lp_with_quiz++;
}
}
}
if ($lp_with_quiz > 0) {
$avg_student_score = round(($average_data_sum / $lp_with_quiz * 100), 2);
}
return $avg_student_score;
}
/**
* get count clicks about tools most used by course
* @param string Course code
* @param int Session id (optional), if param $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
* @return array tools data
*/
public static function get_tools_most_used_by_course($course_code, $session_id = null)
{
//protect data
$course_code = Database::escape_string($course_code);
$data = array();
$TABLETRACK_ACCESS = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
$condition_session = '';
if (isset($session_id)) {
$session_id = intval($session_id);
$condition_session = ' AND access_session_id = '. $session_id;
}
$sql = "SELECT access_tool, COUNT(DISTINCT access_user_id),count( access_tool ) as count_access_tool
FROM $TABLETRACK_ACCESS
WHERE
access_tool IS NOT NULL AND
access_tool != '' AND
access_cours_code = '$course_code'
$condition_session
GROUP BY access_tool
ORDER BY count_access_tool DESC
LIMIT 0, 3";
$rs = Database::query($sql);
if (Database::num_rows($rs) > 0) {
while ($row = Database::fetch_array($rs)) {
$data[] = $row;
}
}
return $data;
}
/**
* Get total clicks
* THIS FUNCTION IS NOT BEEN USED, IT WAS MEANT TO BE USE WITH track_e_course_access.date_from and track_e_course_access.date_to,
* BUT NO ROW MATCH THE CONDITION, IT SHOULD BE FINE TO USE IT WHEN YOU USE USER DEFINED DATES AND NO CHAMILO DATES
* @param int User Id
* @param int Course Id
* @param int Session Id (optional), if param $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
* @param string Date from
* @param string Date to
* @return array Data
* @author César Perales cesar.perales@beeznest.com 2014-01-16
*/
public static function get_total_clicks($userId, $courseId, $sessionId = 0, $date_from = '', $date_to = '')
{
$course = api_get_course_info_by_id($courseId);
$tables = array(
TABLE_STATISTIC_TRACK_E_LASTACCESS => array(
'course' => 'access_cours_code',
'session' => 'access_session_id',
'user' => 'access_user_id',
'start_date'=> 'access_date',
),
TABLE_STATISTIC_TRACK_E_ACCESS => array(
'course' => 'access_cours_code',
'session' => 'access_session_id',
'user' => 'access_user_id',
'start_date'=> 'access_date',
),
#TABLE_STATISTIC_TRACK_E_LOGIN, array(,, 'login_date', 'logout_date');
TABLE_STATISTIC_TRACK_E_DOWNLOADS => array(
'course' => 'down_cours_id',
'session' => 'down_session_id',
'user' => 'down_user_id',
'start_date'=> 'down_date',
),
TABLE_STATISTIC_TRACK_E_LINKS => array(
'course' => 'links_cours_id',
'session' => 'links_session_id',
'user' => 'links_user_id',
'start_date'=> 'links_date',
),
TABLE_STATISTIC_TRACK_E_ONLINE => array(
'course' => 'course',
'session' => 'session_id',
'user' => 'login_user_id',
'start_date'=> 'login_date',
),
#TABLE_STATISTIC_TRACK_E_HOTPOTATOES,
/*TABLE_STATISTIC_TRACK_E_COURSE_ACCESS => array(
'course' => 'course_code',
'session' => 'session_id',
'user' => 'user_id',
'start_date'=> 'login_course_date',
'end_date' => 'logout_course_date',
),*/
TABLE_STATISTIC_TRACK_E_EXERCICES => array(
'course' => 'exe_cours_id',
'session' => 'session_id',
'user' => 'exe_user_id',
'start_date'=> 'exe_date',
),
TABLE_STATISTIC_TRACK_E_ATTEMPT => array(
'course' => 'course_code',
'session' => 'session_id',
'user' => 'user_id',
'start_date'=> 'tms',
),
#TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING,
#TABLE_STATISTIC_TRACK_E_DEFAULT,
TABLE_STATISTIC_TRACK_E_UPLOADS => array(
'course' => 'upload_cours_id',
'session' => 'upload_session_id',
'user' => 'upload_user_id',
'start_date'=> 'upload_date',
),
#TABLE_STATISTIC_TRACK_E_HOTSPOT,
#TABLE_STATISTIC_TRACK_E_ITEM_PROPERTY,
#TABLE_STATISTIC_TRACK_E_OPEN,
);
$error_sql = '';
foreach ($tables as $tableName => $fields) {
//If session is defined, add it to query
$where = '';
if (isset($sessionId) && !empty($sessionId)) {
$sessionField = $fields['session'];
$where .= " AND $sessionField = $sessionId";
}
//filter by date
if (!empty($date_from) && !empty($date_to)) {
$fieldStartDate = $fields['start_date'];
if (!isset($fields['end_date'])) {
$where .= sprintf(" AND ($fieldStartDate BETWEEN '%s' AND '%s' )", $date_from, $date_to) ;
} else {
$fieldEndDate = $fields['end_date'];
$where .= sprintf(" AND fieldStartDate >= '%s'
AND $fieldEndDate <= '%s'", $date_from, $date_to);
}
}
//query
$sql = "SELECT %s as user, count(*) as total
FROM %s
WHERE %s = '%s'
AND %s = %s
$where
GROUP BY %s";
$sql = sprintf($sql,
$fields['user'], //user field
$tableName, //FROM
$fields['course'], //course condition
$course['code'], //course condition
$fields['user'], //user condition
$userId, //user condition
$fields['user'] //GROUP BY
);
$rs = Database::query($sql);
//iterate query
if (Database::num_rows($rs) > 0) {
while ($row = Database::fetch_array($rs)) {
$data[$row['user']] = (isset($data[$row['user']])) ? $data[$row['user']] + $row[total]: $row['total'];
}
}
}
return $data;
}
/**
* get documents most downloaded by course
* @param string Course code
* @param int Session id (optional), if param $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
* @param int Limit (optional, default = 0, 0 = without limit)
* @return array documents downloaded
*/
public static function get_documents_most_downloaded_by_course($course_code, $session_id = null, $limit = 0)
{
//protect data
$course_code = Database::escape_string($course_code);
$data = array();
$TABLETRACK_DOWNLOADS = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
$condition_session = '';
if (isset($session_id)) {
$session_id = intval($session_id);
$condition_session = ' AND down_session_id = '. $session_id;
}
$sql = "SELECT down_doc_path, COUNT(DISTINCT down_user_id), COUNT(down_doc_path) as count_down
FROM $TABLETRACK_DOWNLOADS
WHERE down_cours_id = '$course_code'
$condition_session
GROUP BY down_doc_path
ORDER BY count_down DESC
LIMIT 0, $limit";
$rs = Database::query($sql);
if (Database::num_rows($rs) > 0) {
while ($row = Database::fetch_array($rs)) {
$data[] = $row;
}
}
return $data;
}
/**
* get links most visited by course
* @param string Course code
* @param int Session id (optional), if param $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
* @return array links most visited
*/
public static function get_links_most_visited_by_course($course_code, $session_id = null)
{
$course_code = Database::escape_string($course_code);
$course_info = api_get_course_info($course_code);
$course_id = $course_info['real_id'];
$data = array();
$TABLETRACK_LINKS = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LINKS);
$TABLECOURSE_LINKS = Database::get_course_table(TABLE_LINK);
$condition_session = '';
if (isset($session_id)) {
$session_id = intval($session_id);
$condition_session = ' AND cl.session_id = '.$session_id;
}
$sql = "SELECT cl.title, cl.url,count(DISTINCT sl.links_user_id), count(cl.title) as count_visits
FROM $TABLETRACK_LINKS AS sl, $TABLECOURSE_LINKS AS cl
WHERE cl.c_id = $course_id AND
sl.links_link_id = cl.id
AND sl.links_cours_id = '$course_code'
$condition_session
GROUP BY cl.title, cl.url
ORDER BY count_visits DESC
LIMIT 0, 3";
$rs = Database::query($sql);
if (Database::num_rows($rs) > 0) {
while ($row = Database::fetch_array($rs)) {
$data[] = $row;
}
}
return $data;
}
/**
* Shows the user progress (when clicking in the Progress tab)
*
* @param int $user_id
* @param int $session_id
* @param string $extra_params
* @param bool $show_courses
*
* @return string
*/
public static function show_user_progress($user_id, $session_id = 0, $extra_params = '', $show_courses = true, $showAllSessions = true)
{
$tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
$tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
$tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_access_rel_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_access_rel_session = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
$user_id = intval($user_id);
if (api_is_multiple_url_enabled()) {
$sql = "SELECT cu.course_code as code, title
FROM $tbl_course_user cu INNER JOIN $tbl_access_rel_course a
ON (a.course_code = cu.course_code)
INNER JOIN $tbl_course c ON (cu.course_code = c.code)
WHERE
user_id = $user_id AND
relation_type<> ".COURSE_RELATION_TYPE_RRHH." AND
access_url_id = ".api_get_current_access_url_id()."
ORDER BY title";
} else {
$sql = "SELECT course_code as code, title
FROM $tbl_course_user u
INNER JOIN $tbl_course c ON (course_code = c.code)
WHERE
u.user_id= $user_id AND
relation_type<>".COURSE_RELATION_TYPE_RRHH."
ORDER BY title";
}
$rs = Database::query($sql);
$courses = $course_in_session = $temp_course_in_session = array();
while($row = Database :: fetch_array($rs, 'ASSOC')) {
$courses[$row['code']] = $row['title'];
}
$orderBy = " ORDER BY name ";
$extraInnerJoin = null;
if (SessionManager::orderCourseIsEnabled() && !empty($session_id)) {
$orderBy = " ORDER BY s.id, position ";
$tableSessionRelCourse = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$extraInnerJoin = " INNER JOIN $tableSessionRelCourse src ON (cu.course_code = src.course_code AND src.id_session = $session_id) ";
}
// Get the list of sessions where the user is subscribed as student
if (api_is_multiple_url_enabled()) {
$sql = "SELECT DISTINCT cu.course_code, s.id as session_id, name
FROM $tbl_session_course_user cu
INNER JOIN $tbl_access_rel_session a
ON (a.session_id = cu.id_session)
INNER JOIN $tbl_session s ON (s.id = a.session_id)
$extraInnerJoin
WHERE
id_user = $user_id AND
access_url_id = ".api_get_current_access_url_id()."
$orderBy ";
} else {
$sql = "SELECT DISTINCT cu.course_code, s.id as session_id, name
FROM $tbl_session_course_user cu
INNER JOIN $tbl_session s ON (s.id = cu.id_session)
$extraInnerJoin
WHERE id_user = $user_id
$orderBy ";
}
$rs = Database::query($sql);
$simple_session_array = array();
while ($row = Database :: fetch_array($rs)) {
$course_info = CourseManager::get_course_information($row['course_code']);
$temp_course_in_session[$row['session_id']]['course_list'][$course_info['id']] = $course_info;
$temp_course_in_session[$row['session_id']]['name'] = $row['name'];
$simple_session_array[$row['session_id']] = $row['name'];
}
foreach ($simple_session_array as $my_session_id => $session_name) {
$course_list = $temp_course_in_session[$my_session_id]['course_list'];
$my_course_data = array();
foreach ($course_list as $course_data) {
$my_course_data[$course_data['id']] = $course_data['title'];
}
if (!SessionManager::orderCourseIsEnabled() && empty($session_id)) {
$my_course_data = utf8_sort($my_course_data);
}
$final_course_data = array();
foreach($my_course_data as $course_id => $value) {
$final_course_data[$course_id] = $course_list[$course_id];
}
$course_in_session[$my_session_id]['course_list'] = $final_course_data;
$course_in_session[$my_session_id]['name'] = $session_name;
}
$html = '';
// Course list
if ($show_courses) {
if (!empty($courses)) {
$html .= Display::page_subheader(
Display::return_icon('course.png', get_lang('MyCourses'), array(), ICON_SIZE_SMALL).' '.get_lang('MyCourses')
);
$html .= '';
$html .= '
'.Display::tag('th', get_lang('Course'), array('width'=>'300px')).'
'.Display::tag('th', get_lang('TimeSpentInTheCourse'), array('class'=>'head')).'
'.Display::tag('th', get_lang('Progress'), array('class'=>'head')).'
'.Display::tag('th', get_lang('Score').Display::return_icon('info3.gif', get_lang('ScormAndLPTestTotalAverage'), array('align' => 'absmiddle', 'hspace' => '3px')),array('class'=>'head')).'
'.Display::tag('th', get_lang('LastConnexion'), array('class'=>'head')).'
'.Display::tag('th', get_lang('Details'), array('class'=>'head')).'
';
foreach ($courses as $course_code => $course_title) {
$total_time_login = Tracking :: get_time_spent_on_the_course(
$user_id,
$course_code
);
$time = api_time_to_hms($total_time_login);
$progress = Tracking :: get_avg_student_progress(
$user_id,
$course_code
);
$percentage_score = Tracking :: get_avg_student_score(
$user_id,
$course_code,
array()
);
$last_connection = Tracking :: get_last_connection_date_on_the_course(
$user_id,
$course_code
);
if (is_null($progress)) {
$progress = '0%';
} else {
$progress = $progress.'%';
}
if ($course_code == $_GET['course'] && empty($_GET['session_id'])) {
$html .= '';
} else {
$html .= '
';
}
$url = api_get_course_url($course_code, $session_id);
$course_url = Display::url($course_title, $url, array('target'=>SESSION_LINK_TARGET));
$html .= ''.$course_url.' | ';
$html .= ''.$time.' | ';
$html .= ''.$progress.' | ';
$html .= '';
if (is_numeric($percentage_score)) {
$html .= $percentage_score.'%';
} else {
$html .= '0%';
}
$html .= ' | ';
$html .= ''.$last_connection.' | ';
$html .= '';
if ($course_code == $_GET['course'] && empty($_GET['session_id'])) {
$html .= '';
$html .= Display::return_icon('2rightarrow_na.gif', get_lang('Details'));
} else {
$html .= '';
$html .= Display::return_icon('2rightarrow.gif', get_lang('Details'));
}
$html .= '';
$html .= ' |
';
}
$html .= '
';
}
}
// Session list
if (!empty($course_in_session)) {
$main_session_graph = '';
//Load graphics only when calling to an specific session
$session_graph = array();
$all_exercise_graph_name_list = array();
$my_results = array();
$all_exercise_graph_list = array();
$all_exercise_start_time = array();
foreach ($course_in_session as $my_session_id => $session_data) {
$course_list = $session_data['course_list'];
$session_name = $session_data['name'];
$user_count = count(SessionManager::get_users_by_session($my_session_id));
$exercise_graph_name_list = array();
//$user_results = array();
$exercise_graph_list = array();
foreach ($course_list as $course_data) {
$exercise_list = get_all_exercises($course_data, $my_session_id);
foreach ($exercise_list as $exercise_data) {
$exercise_obj = new Exercise($course_data['id']);
$exercise_obj->read($exercise_data['id']);
//Exercise is not necessary to be visible to show results check the result_disable configuration instead
//$visible_return = $exercise_obj->is_visible();
if ($exercise_data['results_disabled'] == 0 || $exercise_data['results_disabled'] == 2) {
$best_average = intval(
get_best_average_score_by_exercise(
$exercise_data['id'],
$course_data['code'],
$my_session_id,
$user_count
)
);
$exercise_graph_list[] = $best_average;
$all_exercise_graph_list[] = $best_average;
$user_result_data = get_best_attempt_by_user(
api_get_user_id(),
$exercise_data['id'],
$course_data['code'],
$my_session_id
);
$score = 0;
if (!empty($user_result_data['exe_weighting']) && intval($user_result_data['exe_weighting']) != 0) {
$score = intval($user_result_data['exe_result']/$user_result_data['exe_weighting'] * 100);
}
$time = api_strtotime($exercise_data['start_time']) ? api_strtotime($exercise_data['start_time'], 'UTC') : 0;
$all_exercise_start_time[] = $time;
$my_results[] = $score;
if (count($exercise_list)<=10) {
$title = cut($course_data['title'], 30)." \n ".cut($exercise_data['title'], 30);
$exercise_graph_name_list[]= $title;
$all_exercise_graph_name_list[] = $title;
} else {
// if there are more than 10 results, space becomes difficult to find, so only show the title of the exercise, not the tool
$title = cut($exercise_data['title'], 30);
$exercise_graph_name_list[]= $title;
$all_exercise_graph_name_list[]= $title;
}
}
}
}
}
// Complete graph
if (!empty($my_results) && !empty($all_exercise_graph_list)) {
asort($all_exercise_start_time);
//Fix exams order
$final_all_exercise_graph_name_list = array();
$my_results_final = array();
$final_all_exercise_graph_list = array();
foreach ($all_exercise_start_time as $key => $time) {
$label_time = '';
if (!empty($time)) {
$label_time = date('d-m-y', $time);
}
$final_all_exercise_graph_name_list[] = $all_exercise_graph_name_list[$key].' '.$label_time;
$my_results_final[] = $my_results[$key];
$final_all_exercise_graph_list[] = $all_exercise_graph_list[$key];
}
$main_session_graph = self::generate_session_exercise_graph(
$final_all_exercise_graph_name_list,
$my_results_final,
$final_all_exercise_graph_list
);
}
$html .= Display::page_subheader(Display::return_icon('session.png', get_lang('Sessions'), array(), ICON_SIZE_SMALL).' '.get_lang('Sessions'));
$html .= '';
$html .= '
'.Display::tag('th', get_lang('Session'), array('width'=>'300px')).'
'.Display::tag('th', get_lang('PublishedExercises'), array('width'=>'300px')).'
'.Display::tag('th', get_lang('NewExercises'), array('class'=>'head')).'
'.Display::tag('th', get_lang('AverageExerciseResult'), array('class'=>'head')).'
'.Display::tag('th', get_lang('Details'), array('class'=>'head')).'
';
foreach ($course_in_session as $my_session_id => $session_data) {
$course_list = $session_data['course_list'];
$session_name = $session_data['name'];
if ($showAllSessions == false) {
if (isset($session_id) && !empty($session_id)) {
if ($session_id != $my_session_id) {
continue;
}
}
}
$all_exercises = 0;
$all_unanswered_exercises_by_user = 0;
$all_average = 0;
$stats_array = array();
foreach($course_list as $course_data) {
//All exercises in the course @todo change for a real count
$exercises = get_all_exercises($course_data, $my_session_id);
$count_exercises = 0;
if (is_array($exercises) && !empty($exercises)) {
$count_exercises = count($exercises);
}
//Count of user results
//$done_exercises = get_count_exercises_attempted_by_course($course_data['code'], $my_session_id);
$done_exercises = null;
$answered_exercises = 0;
if (!empty($exercises)) {
foreach($exercises as $exercise_item) {
$attempts = count_exercise_attempts_by_user(api_get_user_id(), $exercise_item['id'], $course_data['code'], $my_session_id);
if ($attempts > 1) {
$answered_exercises++;
}
}
}
// Average
$average = get_average_score_by_course($course_data['code'], $my_session_id);
$all_exercises += $count_exercises;
$all_unanswered_exercises_by_user += $count_exercises - $answered_exercises;
//$all_done_exercise += $done_exercises;
$all_average += $average;
//$stats_array[$course_data['code']] = array('exercises'=>$count_exercises, 'unanswered_exercises_by_user'=>$answered_exercises,'done_exercises'=>$done_exercises, 'average'=>$average);
}
$all_average = $all_average / count($course_list);
if (isset($_GET['session_id']) && $my_session_id == $_GET['session_id']) {
$html .= '';
} else {
$html .= '
';
}
$url = api_get_path(WEB_CODE_PATH)."session/index.php?session_id={$my_session_id}";
$html .= Display::tag('td', Display::url($session_name, $url, array('target'=>SESSION_LINK_TARGET)));
$html .= Display::tag('td', $all_exercises);
$html .= Display::tag('td', $all_unanswered_exercises_by_user);
//$html .= Display::tag('td', $all_done_exercise);
$html .= Display::tag('td', convert_to_percentage($all_average));
if (isset($_GET['session_id']) && $my_session_id == $_GET['session_id']) {
$icon = Display::url(Display::return_icon('2rightarrow_na.gif', get_lang('Details')), '?session_id='.$my_session_id);
} else {
$icon = Display::url(Display::return_icon('2rightarrow.gif', get_lang('Details')), '?session_id='.$my_session_id);
}
$html .= Display::tag('td', $icon);
$html .= '
';
}
$html .= '
';
$html .= Display::div($main_session_graph, array('id'=>'session_graph','class'=>'chart-session', 'style'=>'position:relative; text-align: center;') );
// Checking selected session.
if (isset($_GET['session_id'])) {
$session_id_from_get = intval($_GET['session_id']);
$session_data = $course_in_session[$session_id_from_get];
$course_list = $session_data['course_list'];
$html .= Display::tag('h3',$session_data['name'].' - '.get_lang('CourseList'));
$html .= '';
//'.Display::tag('th', get_lang('DoneExercises'), array('class'=>'head')).'
$html .= '
'.get_lang('Course').' |
'.Display::tag('th', get_lang('PublishedExercises'), array('class'=>'head')).'
'.Display::tag('th', get_lang('NewExercises'), array('class'=>'head')).'
'.Display::tag('th', get_lang('MyAverage'), array('class'=>'head')).'
'.Display::tag('th', get_lang('AverageExerciseResult'), array('class'=>'head')).'
'.Display::tag('th', get_lang('TimeSpentInTheCourse'), array('class'=>'head')).'
'.Display::tag('th', get_lang('LPProgress') , array('class'=>'head')).'
'.Display::tag('th', get_lang('Score').Display::return_icon('info3.gif', get_lang('ScormAndLPTestTotalAverage'), array ('align' => 'absmiddle', 'hspace' => '3px')), array('class'=>'head')).'
'.Display::tag('th', get_lang('LastConnexion'), array('class'=>'head')).'
'.Display::tag('th', get_lang('Details'), array('class'=>'head')).'
';
foreach ($course_list as $course_data) {
$course_code = $course_data['code'];
$course_title = $course_data['title'];
//All exercises in the course @todo change for a real count
$exercises = get_all_exercises($course_data, $session_id_from_get);
$count_exercises = 0;
if (!empty($exercises)) {
$count_exercises = count($exercises);
}
//Count of user results
//$done_exercises = get_best_exercise_results_by_course($course_code, $session_id_from_get);
//From course exercises NOT from LP exercises!!!
//$done_exercises = get_count_exercises_attempted_by_course($course_code, $session_id_from_get);
$answered_exercises = 0;
foreach($exercises as $exercise_item) {
$attempts = count_exercise_attempts_by_user(api_get_user_id(), $exercise_item['id'], $course_code, $session_id_from_get);
if ($attempts > 1) {
$answered_exercises++;
}
}
$unanswered_exercises = $count_exercises - $answered_exercises;
// Average
$average = get_average_score_by_course($course_code, $session_id_from_get);
$my_average = get_average_score_by_course_by_user(api_get_user_id(), $course_code, $session_id_from_get);
$stats_array[$course_code] = array(
'exercises' => $count_exercises,
'unanswered_exercises_by_user' => $unanswered_exercises,
'done_exercises' => $done_exercises,
'average' => $average,
'my_average' => $my_average
);
$weighting = 0;
$last_connection = Tracking :: get_last_connection_date_on_the_course($user_id, $course_code, $session_id_from_get);
$progress = Tracking :: get_avg_student_progress($user_id, $course_code,array(), $session_id_from_get);
$total_time_login = Tracking :: get_time_spent_on_the_course($user_id, $course_code, $session_id_from_get);
$time = api_time_to_hms($total_time_login);
$percentage_score = Tracking :: get_avg_student_score($user_id, $course_code, array(), $session_id_from_get);
$courseCodeFromGet = isset($_GET['course']) ? $_GET['course'] : null;
if ($course_code == $courseCodeFromGet && $_GET['session_id'] == $session_id_from_get) {
$html .= '';
} else {
$html .= '
';
}
$url = api_get_course_url($course_code, $session_id_from_get);
$course_url = Display::url($course_title, $url, array('target'=>SESSION_LINK_TARGET));
$html .= Display::tag('td', $course_url);
$html .= Display::tag('td', $stats_array[$course_code]['exercises']);
$html .= Display::tag('td', $stats_array[$course_code]['unanswered_exercises_by_user']);
//$html .= Display::tag('td', $stats_array[$course_code]['done_exercises']);
$html .= Display::tag('td', convert_to_percentage($stats_array[$course_code]['my_average']));
$html .= Display::tag('td', $stats_array[$course_code]['average'] == 0 ? '-' : '('.convert_to_percentage($stats_array[$course_code]['average']).')');
$html .= Display::tag('td', $time, array('align'=>'center'));
if (is_numeric($progress)) {
$progress = $progress.'%';
} else {
$progress = '0%';
}
//Progress
$html .= Display::tag('td', $progress, array('align'=>'center'));
if (is_numeric($percentage_score)) {
$percentage_score = $percentage_score.'%';
} else {
$percentage_score = '0%';
}
//Score
$html .= Display::tag('td', $percentage_score, array('align'=>'center'));
$html .= Display::tag('td', $last_connection, array('align'=>'center'));
if ($course_code == $courseCodeFromGet && $_GET['session_id'] == $session_id_from_get) {
$details = '';
$details .=Display::return_icon('2rightarrow_na.gif', get_lang('Details'));
} else {
$details = '';
$details .= Display::return_icon('2rightarrow.gif', get_lang('Details'));
}
$details .= '';
$html .= Display::tag('td', $details, array('align'=>'center'));
$html .= '
';
}
$html .= '
';
if (!empty($session_graph[$session_id_from_get])) {
//$html .= Display::div($session_graph[$session_id_from_get], array('id'=>'session_graph','class'=>'chart-session', 'style'=>'position:relative; text-align: center;') );
}
}
}
return $html;
}
/**
* Shows the user detail progress (when clicking in the details link)
* @param int user id
* @param string course code
* @param int session id
* @return string html code
*/
static function show_course_detail($user_id, $course_code, $session_id)
{
$html = '';
if (isset($course_code)) {
$user_id = intval($user_id);
$session_id = intval($session_id);
$course = Database::escape_string($course_code);
$course_info = CourseManager::get_course_information($course);
//$course_id = $course_info['real_id'];
//$session_name = api_get_session_name($session_id);
$html .= Display::page_subheader($course_info['title']);
$html .= '';
//Course details
$html .= '
'.get_lang('Exercices').' |
'.get_lang('Attempts').' |
'.get_lang('BestAttempt').' |
'.get_lang('Ranking').' |
'.get_lang('BestResultInCourse').' |
'.get_lang('Statistics').' '.Display :: return_icon('info3.gif', get_lang('OnlyBestResultsPerStudent'), array('align' => 'absmiddle', 'hspace' => '3px')).' |
';
if (empty($session_id)) {
$user_list = CourseManager::get_user_list_from_course_code($course, $session_id, null, null, STUDENT);
} else {
$user_list = CourseManager::get_user_list_from_course_code($course, $session_id, null, null, 0);
}
//$exercise_list = get_all_exercises($course_info, $session_id, true);
// Show exercise results of invisible exercises? see BT#4091
$exercise_list = get_all_exercises($course_info, $session_id, false);
$to_graph_exercise_result = array();
if (!empty($exercise_list)) {
$score = $weighting = $exe_id = 0;
foreach ($exercise_list as $exercices) {
$exercise_obj = new Exercise($course_info['real_id']);
$exercise_obj->read($exercices['id']);
$visible_return = $exercise_obj->is_visible();
$score = $weighting = $attempts = 0;
//Getting count of attempts by user
$attempts = count_exercise_attempts_by_user(api_get_user_id(), $exercices['id'], $course_info['code'], $session_id);
$html .= '';
$url = api_get_path(WEB_CODE_PATH)."exercice/overview.php?cidReq={$course_info['code']}&id_session=$session_id&exerciseId={$exercices['id']}";
if ($visible_return['value'] == true) {
$exercices['title'] = Display::url($exercices['title'], $url, array('target'=>SESSION_LINK_TARGET));
}
$html .= Display::tag('td', $exercices['title']);
//Exercise configuration show results or show only score
if ($exercices['results_disabled'] == 0 || $exercices['results_disabled'] == 2) {
//For graphics
$best_exercise_stats = get_best_exercise_results_by_user($exercices['id'], $course_info['code'], $session_id);
$to_graph_exercise_result[$exercices['id']] = array('title'=>$exercices['title'], 'data'=>$best_exercise_stats);
$latest_attempt_url = '';
$best_score = $position = $percentage_score_result = '-';
$graph = $normal_graph = null;
//Getting best results
$best_score_data = get_best_attempt_in_course($exercices['id'], $course_info['code'], $session_id);
$best_score = show_score($best_score_data['exe_result'], $best_score_data['exe_weighting']);
if ($attempts > 0) {
$exercise_stat = get_best_attempt_by_user(api_get_user_id(), $exercices['id'], $course_info['code'], $session_id);
if (!empty($exercise_stat)) {
//Always getting the BEST attempt
$score = $exercise_stat['exe_result'];
$weighting = $exercise_stat['exe_weighting'];
$exe_id = $exercise_stat['exe_id'];
$latest_attempt_url .= api_get_path(WEB_CODE_PATH).'exercice/result.php?id='.$exe_id.'&cidReq='.$course_info['code'].'&show_headers=1&id_session='.$session_id;
$percentage_score_result = Display::url(show_score($score, $weighting), $latest_attempt_url);
$my_score = 0;
if (!empty($weighting) && intval($weighting) != 0) {
$my_score = $score/$weighting;
}
//@todo this function slows the page
$position = get_exercise_result_ranking($my_score, $exe_id, $exercices['id'], $course_info['code'], $session_id, $user_list);
$graph = self::generate_exercise_result_thumbnail_graph($to_graph_exercise_result[$exercices['id']]);
$normal_graph = self::generate_exercise_result_graph($to_graph_exercise_result[$exercices['id']]);
}
}
$html .= Display::div($normal_graph, array('id'=>'main_graph_'.$exercices['id'],'class'=>'dialog', 'style'=>'display:none') );
if (empty($graph)) {
$graph = '-';
} else {
$graph = Display::url($graph, '#', array('id'=>$exercices['id'], 'class'=>'opener'));
}
$html .= Display::tag('td', $attempts, array('align'=>'center'));
$html .= Display::tag('td', $percentage_score_result, array('align'=>'center'));
$html .= Display::tag('td', $position, array('align'=>'center'));
$html .= Display::tag('td', $best_score, array('align'=>'center'));
$html .= Display::tag('td', $graph, array('align'=>'center'));
//$html .= Display::tag('td', $latest_attempt_url, array('align'=>'center', 'width'=>'25'));
} else {
// Exercise configuration NO results
$html .= Display::tag('td', $attempts, array('align'=>'center'));
$html .= Display::tag('td', '-', array('align'=>'center'));
$html .= Display::tag('td', '-', array('align'=>'center'));
$html .= Display::tag('td', '-', array('align'=>'center'));
$html .= Display::tag('td', '-', array('align'=>'center'));
}
$html .= '
';
}
} else {
$html .= ''.get_lang('NoEx').' |
';
}
$html .= '
';
//LP table results
$html .='';
$html .= Display::tag('th', get_lang('Learnpaths'), array('class'=>'head', 'style'=>'color:#000'));
$html .= Display::tag('th', get_lang('LatencyTimeSpent'), array('class'=>'head', 'style'=>'color:#000'));
$html .= Display::tag('th', get_lang('Progress'), array('class'=>'head', 'style'=>'color:#000'));
$html .= Display::tag('th', get_lang('Score'), array('class'=>'head', 'style'=>'color:#000'));
$html .= Display::tag('th', get_lang('LastConnexion'), array('class'=>'head', 'style'=>'color:#000'));
$html .= '';
$list = new LearnpathList(api_get_user_id(), $course_info['code'], $session_id, 'publicated_on ASC', true);
$lp_list = $list->get_flat_list();
if (!empty($lp_list) > 0) {
foreach($lp_list as $lp_id => $learnpath) {
$progress = Tracking::get_avg_student_progress($user_id, $course, array($lp_id), $session_id);
$last_connection_in_lp = Tracking::get_last_connection_time_in_lp($user_id, $course, $lp_id, $session_id);
$time_spent_in_lp = Tracking::get_time_spent_in_lp($user_id, $course, array($lp_id), $session_id);
$percentage_score = Tracking::get_avg_student_score($user_id, $course, array($lp_id), $session_id);
if (is_numeric($percentage_score)) {
$percentage_score = $percentage_score.'%';
} else {
$percentage_score = '0%';
}
$time_spent_in_lp = api_time_to_hms($time_spent_in_lp);
$html .= '';
$url = api_get_path(WEB_CODE_PATH)."newscorm/lp_controller.php?cidReq={$course_code}&id_session=$session_id&lp_id=$lp_id&action=view";
$html .= Display::tag('td', Display::url($learnpath['lp_name'], $url, array('target'=>SESSION_LINK_TARGET)));
$html .= Display::tag('td', $time_spent_in_lp, array('align'=>'center'));
if (is_numeric($progress)) {
$progress = $progress.'%';
}
$html .= Display::tag('td', $progress, array('align'=>'center'));
$html .= Display::tag('td', $percentage_score);
$last_connection = '-';
if (!empty($last_connection_in_lp)) {
$last_connection = api_convert_and_format_date($last_connection_in_lp, DATE_TIME_FORMAT_LONG);
}
$html .= Display::tag('td', $last_connection, array('align'=>'center','width'=>'180px'));
$html .= "
";
}
} else {
$html .= '
'.get_lang('NoLearnpath').'
|
';
}
$html .='
';
}
return $html;
}
/**
* Generates an histogram
*
* @param array list of exercise names
* @param array my results 0 to 100
* @param array average scores 0-100
*/
static function generate_session_exercise_graph($names, $my_results, $average)
{
require_once api_get_path(LIBRARY_PATH).'pchart/pData.class.php';
require_once api_get_path(LIBRARY_PATH).'pchart/pChart.class.php';
require_once api_get_path(LIBRARY_PATH).'pchart/pCache.class.php';
$cache = new pCache();
// Dataset definition
$data_set = new pData();
// Dataset definition
$data_set->AddPoint($average, "Serie1");
$data_set->AddPoint($my_results, "Serie2");
$data_set->AddPoint($names, "Serie3");
$data_set->AddAllSeries();
$data_set->SetAbsciseLabelSerie('Serie3');
$data_set->SetSerieName(get_lang('AverageScore'),"Serie1");
$data_set->SetSerieName(get_lang('MyResults'), "Serie2");
//$data_set->SetYAxisName(get_lang("Percentage"));
$data_set->SetYAxisUnit("%");
// Initialise the graph
$main_width = 860;
$main_height = 500;
$y_label_angle = 50;
$data_set->RemoveSerie("Serie3");
$graph = new pChart($main_width, $main_height);
//See 3.2 BT#2797
$graph->setFixedScale(0,100);
$graph->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf',8);
$graph->setGraphArea(65,50,$main_width-20, $main_height-140);
$graph->drawFilledRoundedRectangle(7,7,$main_width-7,$main_height-7,5,240,240,240);
$graph->drawRoundedRectangle(5,5,$main_width-5,$main_height -5,5,230,230,230);
$graph->drawGraphArea(255,255,255,TRUE);
//SCALE_NORMAL, SCALE_START0, SCALE_ADDALLSTART0, SCALE_ADDALL
$graph->drawScale($data_set->GetData(),$data_set->GetDataDescription(),SCALE_NORMAL ,150,150,150,TRUE,$y_label_angle,1, TRUE);
$graph->drawGrid(4,TRUE,230,230,230,70);
// Draw the 0 line
$graph->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf',6);
$graph->drawTreshold(0,143,55,72,TRUE,TRUE);
// Draw the cubic curve graph
$graph->drawLineGraph($data_set->GetData(),$data_set->GetDataDescription());
$graph->drawPlotGraph($data_set->GetData(),$data_set->GetDataDescription(),1,1,230,255,255);
// Finish the graph
$graph->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf',10);
$graph->drawLegend($main_width - 150,70,$data_set->GetDataDescription(),255,255,255);
$graph->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf',11);
$graph->drawTitle(50, 30, get_lang('ExercisesInTimeProgressChart'), 50,50,50,$main_width-110, true);
// $main_graph = new pChart($main_width,$main_height);
$courseCode = isset($_GET['course']) ? Security::remove_XSS($_GET['course']) : null;
$graph_id = 'generate_session_exercise_graph'.$courseCode.'-'.intval($_GET['session_id']).'-'.api_get_user_id();
if ($cache->IsInCache($graph_id, $data_set->GetData())) {
//if (0) {
//if we already created the img
//echo 'in cache';
$img_file = $cache->GetHash($graph_id,$data_set->GetData());
} else {
$cache->WriteToCache($graph_id, $data_set->GetData(), $graph);
ob_start();
$graph->Stroke();
ob_end_clean();
$img_file = $cache->GetHash($graph_id, $data_set->GetData());
}
$html = '';
return $html;
}
/**
*
* Returns a thumbnail of the function generate_exercise_result_graph
* @param array attempts
*/
static function generate_exercise_result_thumbnail_graph($attempts)
{
require_once api_get_path(LIBRARY_PATH).'pchart/pData.class.php';
require_once api_get_path(LIBRARY_PATH).'pchart/pChart.class.php';
require_once api_get_path(LIBRARY_PATH).'pchart/pCache.class.php';
$exercise_title = $attempts['title'];
$attempts = $attempts['data'];
$my_exercise_result_array = $exercise_result = array();
if (empty($attempts)) {
return null;
}
foreach ($attempts as $attempt) {
if (api_get_user_id() == $attempt['exe_user_id']) {
if ($attempt['exe_weighting'] != 0 ) {
$my_exercise_result_array[]= $attempt['exe_result']/$attempt['exe_weighting'];
}
} else {
if ($attempt['exe_weighting'] != 0 ) {
$exercise_result[]= $attempt['exe_result']/$attempt['exe_weighting'];
}
}
}
//Getting best result
rsort($my_exercise_result_array);
$my_exercise_result = 0;
if (isset($my_exercise_result_array[0])) {
$my_exercise_result = $my_exercise_result_array[0] *100;
}
$max = 100;
$pieces = 5 ;
$part = round($max / $pieces);
$x_axis = array();
$final_array = array();
$my_final_array = array();
for ($i=1; $i <=$pieces; $i++) {
$sum = 1;
if ($i == 1) {
$sum = 0;
}
$min = ($i-1)*$part + $sum;
$max = ($i)*$part;
$x_axis[]= $min." - ".$max;
$count = 0;
foreach($exercise_result as $result) {
$percentage = $result*100;
//echo $percentage.' - '.$min.' - '.$max."
";
if ($percentage >= $min && $percentage <= $max) {
//echo ' is > ';
$count++;
}
}
//echo '
';
$final_array[]= $count;
if ($my_exercise_result >= $min && $my_exercise_result <= $max) {
$my_final_array[] = 1;
} else {
$my_final_array[] = 0;
}
}
//Fix to remove the data of the user with my data
for($i = 0; $i<=count($my_final_array); $i++) {
if (!empty($my_final_array[$i])) {
$my_final_array[$i] = $final_array[$i] + 1; //Add my result
$final_array[$i] = 0;
}
}
$cache = new pCache();
// Dataset definition
$data_set = new pData();
$data_set->AddPoint($final_array,"Serie1");
$data_set->AddPoint($my_final_array,"Serie2");
//$data_set->AddPoint($x_axis,"Serie3");
$data_set->AddAllSeries();
// Initialise the graph
$main_width = 80;
$main_height = 35;
$thumbnail_graph = new pChart($main_width, $main_height);
$thumbnail_graph->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf',8);
//$thumbnail_graph->setGraphArea(50,30,680,200);
$thumbnail_graph->drawFilledRoundedRectangle(2,2,$main_width-2,$main_height-2,2,230,230,230);
$thumbnail_graph->setGraphArea(5,5,$main_width-5,$main_height-5);
$thumbnail_graph->drawGraphArea(255,255,255);
//SCALE_NORMAL, SCALE_START0, SCALE_ADDALLSTART0
$thumbnail_graph->drawScale($data_set->GetData(),$data_set->GetDataDescription(),SCALE_ADDALLSTART0, 150,150,150,FALSE,0,1,TRUE);
$thumbnail_graph->drawOverlayBarGraph($data_set->GetData(),$data_set->GetDataDescription(), 100);
// Finish the graph
$graph_id = 'thumbnail_exercise_result_graph_'.Security::remove_XSS($_GET['course']).'-'.intval($_GET['session_id']).'-'.api_get_user_id();
if ($cache->IsInCache($graph_id, $data_set->GetData())) {
//if (0) {
//if we already created the img
//echo 'in cache';
$img_file = $cache->GetHash($graph_id,$data_set->GetData());
} else {
$cache->WriteToCache($graph_id, $data_set->GetData(), $thumbnail_graph);
ob_start();
$thumbnail_graph->Stroke();
ob_end_clean();
$img_file = $cache->GetHash($graph_id, $data_set->GetData());
}
$html = '';
return $html;
}
/**
* Generates a big graph with the number of best results
* @param array
*/
static function generate_exercise_result_graph($attempts)
{
require_once api_get_path(LIBRARY_PATH).'pchart/pData.class.php';
require_once api_get_path(LIBRARY_PATH).'pchart/pChart.class.php';
require_once api_get_path(LIBRARY_PATH).'pchart/pCache.class.php';
$exercise_title = strip_tags($attempts['title']);
$attempts = $attempts['data'];
$my_exercise_result_array = $exercise_result = array();
if (empty($attempts)) {
return null;
}
foreach ($attempts as $attempt) {
if (api_get_user_id() == $attempt['exe_user_id']) {
if ($attempt['exe_weighting'] != 0 ) {
$my_exercise_result_array[]= $attempt['exe_result']/$attempt['exe_weighting'];
}
} else {
if ($attempt['exe_weighting'] != 0 ) {
$exercise_result[]= $attempt['exe_result']/$attempt['exe_weighting'];
}
}
}
//Getting best result
rsort($my_exercise_result_array);
$my_exercise_result = 0;
if (isset($my_exercise_result_array[0])) {
$my_exercise_result = $my_exercise_result_array[0] *100;
}
$max = 100;
$pieces = 5 ;
$part = round($max / $pieces);
$x_axis = array();
$final_array = array();
$my_final_array = array();
for ($i=1; $i <=$pieces; $i++) {
$sum = 1;
if ($i == 1) {
$sum = 0;
}
$min = ($i-1)*$part + $sum;
$max = ($i)*$part;
$x_axis[]= $min." - ".$max;
$count = 0;
foreach($exercise_result as $result) {
$percentage = $result*100;
//echo $percentage.' - '.$min.' - '.$max."
";
if ($percentage >= $min && $percentage <= $max) {
//echo ' is > ';
$count++;
}
}
//echo '
';
$final_array[]= $count;
if ($my_exercise_result >= $min && $my_exercise_result <= $max) {
$my_final_array[] = 1;
} else {
$my_final_array[] = 0;
}
}
//Fix to remove the data of the user with my data
for($i = 0; $i<=count($my_final_array); $i++) {
if (!empty($my_final_array[$i])) {
$my_final_array[$i] = $final_array[$i] + 1; //Add my result
$final_array[$i] = 0;
}
}
$cache = new pCache();
// Dataset definition
$data_set = new pData();
$data_set->AddPoint($final_array,"Serie1");
$data_set->AddPoint($my_final_array,"Serie2");
$data_set->AddPoint($x_axis,"Serie3");
$data_set->AddAllSeries();
$data_set->SetAbsciseLabelSerie('Serie3');
$data_set->SetSerieName(get_lang('Score'),"Serie1");
$data_set->SetSerieName(get_lang('MyResults'),"Serie2");
$data_set->SetXAxisName(get_lang("Score"));
// Initialise the graph
$main_width = 500;
$main_height = 250;
$main_graph = new pChart($main_width,$main_height);
$main_graph->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf',8);
$main_graph->setGraphArea(50,30, $main_width -20,$main_height -50);
$main_graph->drawFilledRoundedRectangle(10,10, $main_width- 10,$main_height -10,5,240,240,240);
$main_graph->drawRoundedRectangle(7,7,$main_width - 7,$main_height - 7,5,230,230,230);
$main_graph->drawGraphArea(255,255,255,TRUE);
//SCALE_NORMAL, SCALE_START0, SCALE_ADDALLSTART0
$main_graph->drawScale($data_set->GetData(),$data_set->GetDataDescription(),SCALE_ADDALLSTART0, 150,150,150,TRUE,0,1,TRUE);
$main_graph->drawGrid(4,TRUE,230,230,230,50);
// Draw the 0 line
$main_graph->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf',6);
// $main_graph->drawTreshold(0,143,55,72,TRUE,TRUE);
// Draw the bar graph
$data_set->RemoveSerie("Serie3");
//$main_graph->drawBarGraph($data_set->GetData(),$data_set->GetDataDescription(),TRUE);
//$main_graph->drawStackedBarGraph($data_set->GetData(),$data_set->GetDataDescription(),TRUE);
$main_graph->drawOverlayBarGraph($data_set->GetData(),$data_set->GetDataDescription(), 100);
// Finish the graph
$main_graph->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf',8);
$main_graph->drawLegend($main_width - 120,$main_height -100,$data_set->GetDataDescription(),255,255,255);
$main_graph->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf',8);
$main_graph->drawTitle(180,22,$exercise_title,50,50,50);
$graph_id = 'exercise_result_graph'.Security::remove_XSS($_GET['course']).'-'.intval($_GET['session_id']).'-'.api_get_user_id();
if ($cache->IsInCache($graph_id, $data_set->GetData())) {
//if (0) {
//if we already created the img
//echo 'in cache';
$img_file = $cache->GetHash($graph_id,$data_set->GetData());
} else {
$cache->WriteToCache($graph_id, $data_set->GetData(), $main_graph);
ob_start();
$main_graph->Stroke();
ob_end_clean();
$img_file = $cache->GetHash($graph_id, $data_set->GetData());
}
$html = '';
return $html;
}
/**
* @param FormValidator $form
* @return mixed
*/
public static function setUserSearchForm($form)
{
global $_configuration;
$form->addElement('text', 'keyword', get_lang('Keyword'));
$form->addElement('select', 'active', get_lang('Status'), array(1 => get_lang('Active'), 0 => get_lang('Inactive')));
if (isset($_configuration['save_user_last_login']) &&
$_configuration['save_user_last_login']
) {
$form->addElement(
'select',
'sleeping_days',
get_lang('InactiveDays'),
array('', 1 => 1, 5 => 5, 15 => 15, 30 => 30, 60 => 60, 90 => 90, 120 => 120)
);
}
$form->addElement('button', 'submit', get_lang('Search'));
return $form;
}
/**
* Get the progress of a exercise
* @param int $sessionId The session ID (session.id)
* @param int $courseId The course ID (course.id)
* @param int $exerciseId The quiz ID (c_quiz.id)
* @param int $answer The answer status (0 = incorrect, 1 = correct, 2 = both)
* @param array $options An array of options you can pass to the query (limit, where and order)
* @return array An array with the data of exercise(s) progress
*/
public static function get_exercise_progress(
$sessionId = 0,
$courseId = 0,
$exerciseId = 0,
$date_from = null,
$date_to = null,
$options = array()
) {
$sessionId = intval($sessionId);
$courseId = intval($courseId);
$exerciseId = intval($exerciseId);
$date_from = Database::escape_string($date_from);
$date_to = Database::escape_string($date_to);
/*
* This method gets the data by blocks, as previous attempts at one single
* query made it take ages. The logic of query division is described below
*/
// Get tables names
$tuser = Database::get_main_table(TABLE_MAIN_USER);
$tquiz = Database::get_course_table(TABLE_QUIZ_TEST);
$tquiz_answer = Database::get_course_table(TABLE_QUIZ_ANSWER);
$tquiz_question = Database::get_course_table(TABLE_QUIZ_QUESTION);
$tquiz_rel_question = Database::get_course_table(TABLE_QUIZ_TEST_QUESTION);
$ttrack_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
$ttrack_attempt = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
require_once api_get_path(SYS_CODE_PATH).'exercice/exercise.lib.php';
$sessions = array();
$courses = array();
// if session ID is defined but course ID is empty, get all the courses
// from that session
if (!empty($sessionId) && empty($courseId)) {
// $courses is an array of course int id as index and course details hash as value
$courses = SessionManager::get_course_list_by_session_id($sessionId);
$sessions[$sessionId] = api_get_session_info($sessionId);
} elseif (empty($sessionId) && !empty($courseId)) {
// if, to the contrary, course is defined but not sessions, get the sessions that include this course
// $sessions is an array like: [0] => ('id' => 3, 'name' => 'Session 35'), [1] => () etc;
$course = api_get_course_info_by_id($courseId);
$sessionsTemp = SessionManager::get_session_by_course($course['code']);
$courses[$courseId] = $course;
foreach ($sessionsTemp as $sessionItem) {
$sessions[$sessionItem['id']] = $sessionItem;
}
} elseif (!empty($courseId) && !empty($sessionId)) {
//none is empty
$course = api_get_course_info_by_id($courseId);
$courses[$courseId] = array($course['code']);
$courses[$courseId]['code'] = $course['code'];
$sessions[$sessionId] = api_get_session_info($sessionId);
} else {
//both are empty, not enough data, return an empty array
return array();
}
// Now we have two arrays of courses and sessions with enough data to proceed
// If no course could be found, we shouldn't return anything. Sessions can be empty (then we only return the pure-course-context results)
if (count($courses) < 1) {
return array();
}
$data = array();
// The following loop is less expensive than what it seems:
// - if a course was defined, then we only loop through sessions
// - if a session was defined, then we only loop through courses
// - if a session and a course were defined, then we only loop once
foreach ($courses as $courseIdx => $courseData) {
$where = '';
$whereParams = array();
$whereCourseCode = $courseData['code'];
$whereSessionParams = '';
if (count($sessions > 0)) {
foreach ($sessions as $sessionIdx => $sessionData) {
if (!empty($sessionIdx)) {
$whereSessionParams .= $sessionIdx.',';
}
}
$whereSessionParams = substr($whereSessionParams,0,-1);
}
if (!empty($exerciseId)) {
$exerciseId = intval($exerciseId);
$where .= ' AND q.id = %d ';
$whereParams[] = $exerciseId;
}
/*
* This feature has been disabled for now, to avoid having to
* join two very large tables
//2 = show all questions (wrong and correct answered)
if ($answer != 2) {
$answer = intval($answer);
//$where .= ' AND qa.correct = %d';
//$whereParams[] = $answer;
}
*/
$limit = '';
if (!empty($options['limit'])) {
$limit = " LIMIT ".$options['limit'];
}
if (!empty($options['where'])) {
$where .= ' AND '.Database::escape_string($options['where']);
}
$order = '';
if (!empty($options['order'])) {
$order = " ORDER BY ".$options['order'];
}
if (!empty($date_to) && !empty($date_from)) {
$where .= sprintf(" AND (te.start_date BETWEEN '%s 00:00:00' AND '%s 23:59:59')", $date_from, $date_to);
}
$sql = "SELECT
te.session_id,
ta.id as attempt_id,
te.exe_user_id as user_id,
te.exe_id as exercise_attempt_id,
ta.question_id,
ta.answer as answer_id,
ta.tms as time,
te.exe_exo_id as quiz_id,
CONCAT ('c', q.c_id, '_e', q.id) as exercise_id,
q.title as quiz_title,
qq.description as description
FROM $ttrack_exercises te
INNER JOIN $ttrack_attempt ta ON ta.exe_id = te.exe_id
INNER JOIN $tquiz q ON q.id = te.exe_exo_id
INNER JOIN $tquiz_rel_question rq ON rq.exercice_id = q.id AND rq.c_id = q.c_id
INNER JOIN $tquiz_question qq
ON
qq.id = rq.question_id AND
qq.c_id = rq.c_id AND
qq.position = rq.question_order AND
ta.question_id = rq.question_id
WHERE
te.exe_cours_id = '$whereCourseCode' ".(empty($whereSessionParams)?'':"AND te.session_id IN ($whereSessionParams)")."
AND q.c_id = $courseIdx
$where $order $limit";
$sql_query = vsprintf($sql, $whereParams);
// Now browse through the results and get the data
$rs = Database::query($sql_query);
$userIds = array();
$questionIds = array();
$answerIds = array();
while ($row = Database::fetch_array($rs)) {
//only show if exercise is visible
if (api_get_item_visibility($courseData, 'quiz', $row['exercise_id'])) {
$userIds[$row['user_id']] = $row['user_id'];
$questionIds[$row['question_id']] = $row['question_id'];
$answerIds[$row['question_id']][$row['answer_id']] = $row['answer_id'];
$row['session'] = $sessions[$row['session_id']];
$data[] = $row;
}
}
// Now fill questions data. Query all questions and answers for this test to avoid
$sqlQuestions = "SELECT tq.c_id, tq.id as question_id, tq.question, tqa.id_auto,
tqa.answer, tqa.correct, tq.position, tqa.id_auto as answer_id
FROM $tquiz_question tq, $tquiz_answer tqa
WHERE
tqa.question_id = tq.id AND
tqa.c_id = tq.c_id AND
tq.c_id = $courseIdx AND
tq.id IN (".implode(',', $questionIds).")";
$resQuestions = Database::query($sqlQuestions);
$answer = array();
$question = array();
while ($rowQuestion = Database::fetch_assoc($resQuestions)) {
$questionId = $rowQuestion['question_id'];
$answerId = $rowQuestion['answer_id'];
$answer[$questionId][$answerId] = array(
'position' => $rowQuestion['position'],
'question' => $rowQuestion['question'],
'answer' => $rowQuestion['answer'],
'correct' => $rowQuestion['correct']
);
$question[$questionId]['question'] = $rowQuestion['question'];
}
// Now fill users data
$sqlUsers = "SELECT user_id, username, lastname, firstname
FROM $tuser
WHERE user_id IN (".implode(',',$userIds).")";
$resUsers = Database::query($sqlUsers);
while ($rowUser = Database::fetch_assoc($resUsers)) {
$users[$rowUser['user_id']] = $rowUser;
}
foreach ($data as $id => $row) {
$rowQuestId = $row['question_id'];
$rowAnsId = $row['answer_id'];
$data[$id]['session'] = $sessions[$row['session_id']]['name'];
$data[$id]['firstname'] = $users[$row['user_id']]['firstname'];
$data[$id]['lastname'] = $users[$row['user_id']]['lastname'];
$data[$id]['username'] = $users[$row['user_id']]['username'];
$data[$id]['answer'] = $answer[$rowQuestId][$rowAnsId]['answer'];
$data[$id]['correct'] = ($answer[$rowQuestId][$rowAnsId]['correct'] == 0 ? get_lang('No') : get_lang('Yes'));
$data[$id]['question'] = $question[$rowQuestId]['question'];
$data[$id]['question_id'] = $rowQuestId;
$data[$id]['description'] = $row['description'];
}
/*
The minimum expected array structure at the end is:
attempt_id,
session name,
exercise_id,
quiz_title,
username,
lastname,
firstname,
time,
question_id,
question,
answer,
*/
}
return $data;
}
}
/**
* @todo move into a proper file
* @package chamilo.tracking
*/
class TrackingCourseLog
{
/**
* @return mixed
*/
public static function count_item_resources()
{
$session_id = api_get_session_id();
$course_id = api_get_course_int_id();
$table_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY);
$table_user = Database :: get_main_table(TABLE_MAIN_USER);
$sql = "SELECT count(tool) AS total_number_of_items
FROM $table_item_property track_resource, $table_user user
WHERE
track_resource.c_id = $course_id AND
track_resource.insert_user_id = user.user_id AND
id_session = $session_id ";
if (isset($_GET['keyword'])) {
$keyword = Database::escape_string(trim($_GET['keyword']));
$sql .= " AND (
user.username LIKE '%".$keyword."%' OR
lastedit_type LIKE '%".$keyword."%' OR
tool LIKE '%".$keyword."%'
)";
}
$sql .= " AND tool IN (
'document',
'learnpath',
'quiz',
'glossary',
'link',
'course_description',
'announcement',
'thematic',
'thematic_advance',
'thematic_plan'
)";
$res = Database::query($sql);
$obj = Database::fetch_object($res);
return $obj->total_number_of_items;
}
/**
* @param $from
* @param $number_of_items
* @param $column
* @param $direction
* @return array
*/
public static function get_item_resources_data($from, $number_of_items, $column, $direction)
{
$session_id = api_get_session_id();
$course_id = api_get_course_int_id();
$table_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY);
$table_user = Database :: get_main_table(TABLE_MAIN_USER);
$table_session = Database :: get_main_table(TABLE_MAIN_SESSION);
$session_id = intval($session_id);
$sql = "SELECT
tool as col0,
lastedit_type as col1,
ref as ref,
user.username as col3,
insert_date as col5,
visibility as col6,
user.user_id as user_id
FROM $table_item_property track_resource, $table_user user
WHERE
track_resource.c_id = $course_id AND
track_resource.insert_user_id = user.user_id AND
id_session = $session_id ";
if (isset($_GET['keyword'])) {
$keyword = Database::escape_string(trim($_GET['keyword']));
$sql .= " AND (
user.username LIKE '%".$keyword."%' OR
lastedit_type LIKE '%".$keyword."%' OR
tool LIKE '%".$keyword."%'
) ";
}
$sql .= " AND tool IN (
'document',
'learnpath',
'quiz',
'glossary',
'link',
'course_description',
'announcement',
'thematic',
'thematic_advance',
'thematic_plan'
)";
if ($column == 0) {
$column = '0';
}
if ($column != '' && $direction != '') {
if ($column != 2 && $column != 4) {
$sql .= " ORDER BY col$column $direction";
}
} else {
$sql .= " ORDER BY col5 DESC ";
}
$from = intval($from);
$number_of_items = intval($number_of_items);
$sql .= " LIMIT $from, $number_of_items ";
$res = Database::query($sql);
$resources = array();
$thematic_tools = array('thematic', 'thematic_advance', 'thematic_plan');
while ($row = Database::fetch_array($res)) {
$ref = $row['ref'];
$table_name = TrackingCourseLog::get_tool_name_table($row['col0']);
$table_tool = Database :: get_course_table($table_name['table_name']);
$id = $table_name['id_tool'];
$recorset = false;
if (in_array($row['col0'], array('thematic_plan', 'thematic_advance'))) {
$tbl_thematic = Database :: get_course_table(TABLE_THEMATIC);
$sql = "SELECT thematic_id FROM $table_tool WHERE c_id = $course_id AND id = $ref";
$rs_thematic = Database::query($sql);
if (Database::num_rows($rs_thematic)) {
$row_thematic = Database::fetch_array($rs_thematic);
$thematic_id = $row_thematic['thematic_id'];
$sql = "SELECT session.id, session.name, user.username
FROM $tbl_thematic t, $table_session session, $table_user user
WHERE
t.c_id = $course_id AND
t.session_id = session.id AND
session.id_coach = user.user_id AND
t.id = $thematic_id";
$recorset = Database::query($sql);
}
} else {
$sql = "SELECT session.id, session.name, user.username
FROM $table_tool tool, $table_session session, $table_user user
WHERE
tool.c_id = $course_id AND
tool.session_id = session.id AND
session.id_coach = user.user_id AND
tool.$id = $ref";
$recorset = Database::query($sql);
}
if (!empty($recorset)) {
$obj = Database::fetch_object($recorset);
$name_session = '';
$coach_name = '';
if (!empty($obj)) {
$name_session = $obj->name;
$coach_name = $obj->username;
}
$url_tool = api_get_path(WEB_CODE_PATH).$table_name['link_tool'];
$row[0] = '';
if ($row['col6'] != 2) {
if (in_array($row['col0'], $thematic_tools)) {
$exp_thematic_tool = explode('_', $row['col0']);
$thematic_tool_title = '';
if (is_array($exp_thematic_tool)) {
foreach ($exp_thematic_tool as $exp) {
$thematic_tool_title .= api_ucfirst($exp);
}
} else {
$thematic_tool_title = api_ucfirst($row['col0']);
}
$row[0] = ''.get_lang($thematic_tool_title).'';
} else {
$row[0] = ''.get_lang('Tool'.api_ucfirst($row['col0'])).'';
}
} else {
$row[0] = api_ucfirst($row['col0']);
}
$row[1] = get_lang($row[1]);
$row[6] = api_convert_and_format_date($row['col5'], null, date_default_timezone_get());
$row[5] = '';
//@todo Improve this code please
switch ($table_name['table_name']) {
case 'document' :
$sql = "SELECT tool.title as title FROM $table_tool tool
WHERE c_id = $course_id AND id = $ref";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
$row[5] = $obj_document->title;
break;
case 'announcement':
$sql = "SELECT title FROM $table_tool
WHERE c_id = $course_id AND id = $ref";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
$row[5] = $obj_document->title;
break;
case 'glossary':
$sql = "SELECT name FROM $table_tool
WHERE c_id = $course_id AND glossary_id = $ref";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
$row[5] = $obj_document->name;
break;
case 'lp':
$sql = "SELECT name
FROM $table_tool WHERE c_id = $course_id AND id = $ref";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
$row[5] = $obj_document->name;
break;
case 'quiz':
$sql = "SELECT title FROM $table_tool
WHERE c_id = $course_id AND id = $ref";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
$row[5] = $obj_document->title;
break;
case 'course_description':
$sql = "SELECT title FROM $table_tool
WHERE c_id = $course_id AND id = $ref";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
$row[5] = $obj_document->title;
break;
case 'thematic':
$rs = Database::query("SELECT title FROM $table_tool WHERE c_id = $course_id AND id = $ref");
if (Database::num_rows($rs) > 0) {
$obj = Database::fetch_object($rs);
$row[5] = $obj->title;
}
break;
case 'thematic_advance':
$rs = Database::query("SELECT content FROM $table_tool WHERE c_id = $course_id AND id = $ref");
if (Database::num_rows($rs) > 0) {
$obj = Database::fetch_object($rs);
$row[5] = $obj->content;
}
break;
case 'thematic_plan':
$rs = Database::query("SELECT title FROM $table_tool WHERE c_id = $course_id AND id = $ref");
if (Database::num_rows($rs) > 0) {
$obj = Database::fetch_object($rs);
$row[5] = $obj->title;
}
break;
default:
break;
}
$row2 = $name_session;
if (!empty($coach_name)) {
$row2 .= '
'.get_lang('Coach').': '.$coach_name;
}
$row[2] = $row2;
if (!empty($row['col3'])) {
$row['col3'] = Display::url(
$row['col3'],
api_get_path(WEB_CODE_PATH).'user/userInfo.php?'.api_get_cidreq().'&origin=tracking&uInfo='.$row['user_id']
);
$row[3] = $row['col3'];
$ip = TrackingUserLog::get_ip_from_user_event($row['user_id'], $row['col5'], true);
if (empty($ip)) {
$ip = get_lang('Unknown');
}
$row[4] = $ip;
}
$resources[] = $row;
}
}
return $resources;
}
/**
* @param string $tool
*
* @return array
*/
public static function get_tool_name_table($tool)
{
switch ($tool) {
case 'document':
$table_name = TABLE_DOCUMENT;
$link_tool = 'document/document.php';
$id_tool = 'id';
break;
case 'learnpath':
$table_name = TABLE_LP_MAIN;
$link_tool = 'newscorm/lp_controller.php';
$id_tool = 'id';
break;
case 'quiz':
$table_name = TABLE_QUIZ_TEST;
$link_tool = 'exercice/exercice.php';
$id_tool = 'id';
break;
case 'glossary':
$table_name = TABLE_GLOSSARY;
$link_tool = 'glossary/index.php';
$id_tool = 'glossary_id';
break;
case 'link':
$table_name = TABLE_LINK;
$link_tool = 'link/link.php';
$id_tool = 'id';
break;
case 'course_description':
$table_name = TABLE_COURSE_DESCRIPTION;
$link_tool = 'course_description/';
$id_tool = 'id';
break;
case 'announcement':
$table_name = TABLE_ANNOUNCEMENT;
$link_tool = 'announcements/announcements.php';
$id_tool = 'id';
break;
case 'thematic':
$table_name = TABLE_THEMATIC;
$link_tool = 'course_progress/index.php';
$id_tool = 'id';
break;
case 'thematic_advance':
$table_name = TABLE_THEMATIC_ADVANCE;
$link_tool = 'course_progress/index.php';
$id_tool = 'id';
break;
case 'thematic_plan':
$table_name = TABLE_THEMATIC_PLAN;
$link_tool = 'course_progress/index.php';
$id_tool = 'id';
break;
default:
$table_name = $tool;
break;
}
return array(
'table_name' => $table_name,
'link_tool' => $link_tool,
'id_tool' => $id_tool
);
}
public static function display_additional_profile_fields()
{
// getting all the extra profile fields that are defined by the platform administrator
$extra_fields = UserManager :: get_extra_fields(0,50,5,'ASC');
// creating the form
$return = '';
if ($extra_fields_to_show > 0) {
return $return;
} else {
return '';
}
}
/**
* This function gets all the information of a certrain ($field_id) additional profile field.
* It gets the information of all the users so that it can be displayed in the sortable table or in the csv or xls export
*
* @author Patrick Cool , Ghent University, Belgium
* @since October 2009
* @version 1.8.7
*/
public function get_addtional_profile_information_of_field($field_id)
{
// Database table definition
$table_user = Database::get_main_table(TABLE_MAIN_USER);
$table_user_field_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
$sql = "SELECT user.user_id, field.field_value FROM $table_user user, $table_user_field_values field
WHERE user.user_id = field.user_id
AND field.field_id='".intval($field_id)."'";
$result = Database::query($sql);
while($row = Database::fetch_array($result)) {
$return[$row['user_id']][] = $row['field_value'];
}
return $return;
}
/**
* This function gets all the information of a certrain ($field_id)
* additional profile field for a specific list of users is more efficent
* than get_addtional_profile_information_of_field() function
* It gets the information of all the users so that it can be displayed
* in the sortable table or in the csv or xls export
*
* @author Julio Montoya
* @param int field id
* @param array list of user ids
* @return array
* @since Nov 2009
* @version 1.8.6.2
*/
public function get_addtional_profile_information_of_field_by_user($field_id, $users)
{
// Database table definition
$table_user = Database::get_main_table(TABLE_MAIN_USER);
$table_user_field_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
$result_extra_field = UserManager::get_extra_field_information($field_id);
if (!empty($users)) {
if ($result_extra_field['field_type'] == USER_FIELD_TYPE_TAG ) {
foreach($users as $user_id) {
$user_result = UserManager::get_user_tags($user_id, $field_id);
$tag_list = array();
foreach($user_result as $item) {
$tag_list[] = $item['tag'];
}
$return[$user_id][] = implode(', ',$tag_list);
}
} else {
$new_user_array = array();
foreach($users as $user_id) {
$new_user_array[]= "'".$user_id."'";
}
$users = implode(',',$new_user_array);
//selecting only the necessary information NOT ALL the user list
$sql = "SELECT user.user_id, field.field_value FROM $table_user user INNER JOIN $table_user_field_values field
ON (user.user_id = field.user_id)
WHERE field.field_id=".intval($field_id)." AND user.user_id IN ($users)";
$result = Database::query($sql);
while($row = Database::fetch_array($result)) {
// get option value for field type double select by id
if (!empty($row['field_value'])) {
if ($result_extra_field['field_type'] == USER_FIELD_TYPE_DOUBLE_SELECT) {
$id_double_select = explode(';',$row['field_value']);
if (is_array($id_double_select)) {
$value1 = $result_extra_field['options'][$id_double_select[0]]['option_value'];
$value2 = $result_extra_field['options'][$id_double_select[1]]['option_value'];
$row['field_value'] = ($value1.';'.$value2);
}
}
}
// get other value from extra field
$return[$row['user_id']][] = $row['field_value'];
}
}
}
return $return;
}
/**
* count the number of students in this course (used for SortableTable)
* Deprecated
*/
public function count_student_in_course()
{
global $nbStudents;
return $nbStudents;
}
public function sort_users($a, $b)
{
return strcmp(trim(api_strtolower($a[$_SESSION['tracking_column']])), trim(api_strtolower($b[$_SESSION['tracking_column']])));
}
public function sort_users_desc($a, $b)
{
return strcmp( trim(api_strtolower($b[$_SESSION['tracking_column']])), trim(api_strtolower($a[$_SESSION['tracking_column']])));
}
/**
* Get number of users for sortable with pagination
* @return int
*/
public static function get_number_of_users()
{
global $user_ids;
return count($user_ids);
}
/**
* 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, $_configuration;
$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
$condition_user = "";
if (is_array($user_ids)) {
$user_ids = array_map('intval', $user_ids);
$condition_user = " WHERE user.user_id IN (".implode(',',$user_ids).") ";
} else {
$user_ids = intval($user_ids);
$condition_user = " WHERE user.user_id = $user_ids ";
}
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."%' ) ";
}
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();
$t = time();
$course_info = api_get_course_info($course_code);
$total_surveys = 0;
$total_exercises = get_all_exercises($course_info, $session_id);
if (empty($session_id)) {
$survey_user_list = array();
$survey_list = survey_manager::get_surveys($course_code, $session_id);
$total_surveys = count($survey_list);
$survey_data = array();
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'] = '';
// 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;
}
}
/**
* @package chamilo.tracking
*/
class TrackingUserLog
{
/**
* Displays the number of logins every month for a specific user in a specific course.
*/
public function display_login_tracking_info($view, $user_id, $course_id, $session_id = 0)
{
$MonthsLong = $GLOBALS['MonthsLong'];
// protected data
$user_id = intval($user_id);
$session_id = intval($session_id);
$course_id = Database::escape_string($course_id);
$track_access_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS);
$tempView = $view;
if(substr($view,0,1) == '1') {
$new_view = substr_replace($view,'0',0,1);
echo "
- " .
"".get_lang('LoginsAndAccessTools')." [".get_lang('Close')."] [".get_lang('ExportAsCSV')."]
|
";
echo "".get_lang('LoginsDetails')." ";
$sql = "SELECT UNIX_TIMESTAMP(access_date), count(access_date)
FROM $track_access_table
WHERE access_user_id = '$user_id'
AND access_cours_code = '$course_id'
AND access_session_id = '$session_id'
GROUP BY YEAR(access_date),MONTH(access_date)
ORDER BY YEAR(access_date),MONTH(access_date) ASC";
echo " |
";
//$results = getManyResults2Col($sql);
$results = getManyResults3Col($sql);
echo "";
echo "
".get_lang('LoginsTitleMonthColumn')."
|
".get_lang('LoginsTitleCountColumn')."
|
";
$total = 0;
if (is_array($results)) {
for($j = 0 ; $j < count($results) ; $j++) {
echo "";
echo "".$MonthsLong[date('n', $results[$j][0])-1].' '.date('Y', $results[$j][0])." | ";
echo "".$results[$j][1]." | ";
echo" ";
$total = $total + $results[$j][1];
}
echo "";
echo "".get_lang('Total')." | ";
echo "".$total." | ";
echo" ";
} else {
echo "";
echo "".get_lang('NoResult')." | ";
echo" ";
}
echo " ";
echo " |
";
} else {
$new_view = substr_replace($view,'1',0,1);
echo "
+ ".get_lang('LoginsAndAccessTools')."
|
";
}
}
/**
* Displays the exercise results for a specific user in a specific course.
* @todo remove globals
*/
public function display_exercise_tracking_info($view, $user_id, $course_id)
{
global $TBL_TRACK_HOTPOTATOES, $TABLECOURSE_EXERCICES, $TABLETRACK_EXERCICES, $dateTimeFormatLong;
if(substr($view,1,1) == '1') {
$new_view = substr_replace($view,'0',1,1);
echo "
- ".get_lang('ExercicesResults')." [".get_lang('Close')."] [".get_lang('ExportAsCSV')."]
|
";
echo "".get_lang('ExercicesDetails')." ";
$sql = "SELECT ce.title, te.exe_result , te.exe_weighting, UNIX_TIMESTAMP(te.exe_date)
FROM $TABLECOURSE_EXERCICES AS ce , $TABLETRACK_EXERCICES AS te
WHERE te.exe_cours_id = '".Database::escape_string($course_id)."'
AND te.exe_user_id = '".Database::escape_string($user_id)."'
AND te.exe_exo_id = ce.id
ORDER BY ce.title ASC, te.exe_date ASC";
$hpsql = "SELECT te.exe_name, te.exe_result , te.exe_weighting, UNIX_TIMESTAMP(te.exe_date)
FROM $TBL_TRACK_HOTPOTATOES AS te
WHERE te.exe_user_id = '".Database::escape_string($user_id)."' AND te.exe_cours_id = '".Database::escape_string($course_id)."'
ORDER BY te.exe_cours_id ASC, te.exe_date ASC";
$hpresults = getManyResultsXCol($hpsql, 4);
$NoTestRes = 0;
$NoHPTestRes = 0;
echo " |
\n\n";
$results = getManyResultsXCol($sql, 4);
echo "\n";
echo "
".get_lang('ExercicesTitleExerciceColumn')."
|
".get_lang('Date')."
|
".get_lang('ExercicesTitleScoreColumn')."
|
";
if (is_array($results)) {
for($i = 0; $i < sizeof($results); $i++) {
$display_date = api_convert_and_format_date($results[$i][3], null, date_default_timezone_get());
echo "\n";
echo "".$results[$i][0]." | \n";
echo "".$display_date." | \n";
echo "".$results[$i][1]." / ".$results[$i][2]." | \n";
echo " \n";
}
} else {
// istvan begin
$NoTestRes = 1;
}
// The Result of Tests
if(is_array($hpresults)) {
for($i = 0; $i < sizeof($hpresults); $i++) {
$title = GetQuizName($hpresults[$i][0],'');
if ($title == '')
$title = basename($hpresults[$i][0]);
$display_date = api_convert_and_format_date($hpresults[$i][3], null, date_default_timezone_get());
?>
|
|
/
|
\n";
echo "".get_lang('NoResult')." | \n";
echo "\n";
}
echo " ";
echo " | \n
\n";
} else {
$new_view = substr_replace($view,'1',1,1);
echo "
+ ".get_lang('ExercicesResults')."
|
";
}
}
/**
* Displays the student publications for a specific user in a specific course.
* @todo remove globals
*/
public function display_student_publications_tracking_info($view, $user_id, $course_id)
{
global $TABLETRACK_UPLOADS, $TABLECOURSE_WORK, $dateTimeFormatLong, $_course;
if (substr($view,2,1) == '1') {
$new_view = substr_replace($view,'0',2,1);
echo "
- ".get_lang('WorkUploads')." [".get_lang('Close')."] [".get_lang('ExportAsCSV')."]
|
";
echo "".get_lang('WorksDetails')." ";
$sql = "SELECT u.upload_date, w.title, w.author,w.url
FROM $TABLETRACK_UPLOADS u , $TABLECOURSE_WORK w
WHERE u.upload_work_id = w.id
AND u.upload_user_id = '".Database::escape_string($user_id)."'
AND u.upload_cours_id = '".Database::escape_string($course_id)."'
ORDER BY u.upload_date DESC";
echo " |
";
$results = getManyResultsXCol($sql,4);
echo "";
echo "
".get_lang('WorkTitle')."
|
".get_lang('WorkAuthors')."
|
".get_lang('Date')."
|
";
if (is_array($results)) {
for($j = 0 ; $j < count($results) ; $j++) {
$pathToFile = api_get_path(WEB_COURSE_PATH).$_course['path']."/".$results[$j][3];
$beautifulDate = api_convert_and_format_date($results[$j][0], null, date_default_timezone_get());
echo "";
echo ""
."".$results[$j][1].""
." | ";
echo "".$results[$j][2]." | ";
echo "".$beautifulDate." | ";
echo" ";
}
} else {
echo "";
echo "".get_lang('NoResult')." | ";
echo" ";
}
echo " ";
echo " |
";
} else {
$new_view = substr_replace($view,'1',2,1);
echo "
+ ".get_lang('WorkUploads')."
|
";
}
}
/**
* Displays the links followed for a specific user in a specific course.
* @todo remove globals
*/
public function display_links_tracking_info($view, $user_id, $course_id)
{
global $TABLETRACK_LINKS, $TABLECOURSE_LINKS;
if (substr($view,3,1) == '1') {
$new_view = substr_replace($view,'0',3,1);
echo "
- ".get_lang('LinksAccess')." [".get_lang('Close')."] [".get_lang('ExportAsCSV')."]
|
";
echo "".get_lang('LinksDetails')." ";
$sql = "SELECT cl.title, cl.url
FROM $TABLETRACK_LINKS AS sl, $TABLECOURSE_LINKS AS cl
WHERE sl.links_link_id = cl.id
AND sl.links_cours_id = '".Database::escape_string($course_id)."'
AND sl.links_user_id = '".Database::escape_string($user_id)."'
GROUP BY cl.title, cl.url";
echo " |
";
$results = getManyResults2Col($sql);
echo "";
echo "
".get_lang('LinksTitleLinkColumn')."
|
";
if (is_array($results)) {
for($j = 0 ; $j < count($results) ; $j++) {
echo "";
echo "".$results[$j][0]." | ";
echo" ";
}
} else {
echo "";
echo "".get_lang('NoResult')." | ";
echo" ";
}
echo " ";
echo " |
";
} else {
$new_view = substr_replace($view,'1',3,1);
echo "
+ ".get_lang('LinksAccess')."
|
";
}
}
/**
* Displays the documents downloaded for a specific user in a specific course.
* @param string kind of view inside tracking info
* @param int User id
* @param string Course code
* @param int Session id (optional, default = 0)
* @return void
*/
public static function display_document_tracking_info($view, $user_id, $course_id, $session_id = 0) {
// protect data
$user_id = intval($user_id);
$course_id = Database::escape_string($course_id);
$session_id = intval($session_id);
$downloads_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
if(substr($view,4,1) == '1') {
$new_view = substr_replace($view,'0',4,1);
echo "
- ".get_lang('DocumentsAccess')." [".get_lang('Close')."] [".get_lang('ExportAsCSV')."]
|
";
echo "".get_lang('DocumentsDetails')." ";
$sql = "SELECT down_doc_path
FROM $downloads_table
WHERE down_cours_id = '".$course_id."'
AND down_user_id = '$user_id'
AND down_session_id = '$session_id'
GROUP BY down_doc_path";
echo " |
";
$results = getManyResults1Col($sql);
echo "";
echo "
".get_lang('DocumentsTitleDocumentColumn')."
|
";
if (is_array($results)) {
for($j = 0 ; $j < count($results) ; $j++) {
echo "";
echo "".$results[$j]." | ";
echo" ";
}
} else {
echo "";
echo "".get_lang('NoResult')." | ";
echo" ";
}
echo " ";
echo " |
";
} else {
$new_view = substr_replace($view,'1',4,1);
echo "
+ ".get_lang('DocumentsAccess')."
|
";
}
}
/**
* Gets the IP of a given user, using the last login before the given date
* @param int User ID
* @param string Datetime
* @param bool Whether to return the IP as a link or just as an IP
* @param string If defined and return_as_link if true, will be used as the text to be shown as the link
* @return string IP address (or false on error)
* @assert (0,0) === false
*/
public static function get_ip_from_user_event($user_id, $event_date, $return_as_link = false, $body_replace = null) {
if (empty($user_id) or empty($event_date)) {
return false;
}
$table_login = Database :: get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$sql_ip = "SELECT login_date, login_ip FROM $table_login WHERE login_user_id = $user_id AND login_date < '$event_date' ORDER BY login_date DESC LIMIT 1";
$ip = '';
$res_ip = Database::query($sql_ip);
if ($res_ip !== false && Database::num_rows($res_ip)>0) {
$row_ip = Database::fetch_row($res_ip);
if ($return_as_link) {
$ip = Display::url((empty($body_replace)?$row_ip[1]:$body_replace), 'http://www.whatsmyip.org/ip-geo-location/?ip='.$row_ip[1], array('title'=>get_lang('TraceIP'), 'target'=>'_blank'));
} else {
$ip = $row_ip[1];
}
}
return $ip;
}
}
/**
* @package chamilo.tracking
*/
class TrackingUserLogCSV
{
/**
* Displays the number of logins every month for a specific user in a specific course.
*/
public function display_login_tracking_info($view, $user_id, $course_id, $session_id = 0)
{
$MonthsLong = $GLOBALS['MonthsLong'];
$track_access_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS);
// protected data
$user_id = intval($user_id);
$session_id = intval($session_id);
$course_id = Database::escape_string($course_id);
$tempView = $view;
if(substr($view,0,1) == '1')
{
$new_view = substr_replace($view,'0',0,1);
$title[1]= get_lang('LoginsAndAccessTools').get_lang('LoginsDetails');
$sql = "SELECT UNIX_TIMESTAMP(access_date), count(access_date)
FROM $track_access_table
WHERE access_user_id = '$user_id'
AND access_cours_code = '".$course_id."'
AND access_session_id = '$session_id'
GROUP BY YEAR(access_date),MONTH(access_date)
ORDER BY YEAR(access_date),MONTH(access_date) ASC";
//$results = getManyResults2Col($sql);
$results = getManyResults3Col($sql);
$title_line= get_lang('LoginsTitleMonthColumn').';'.get_lang('LoginsTitleCountColumn')."\n";
$line='';
$total = 0;
if (is_array($results)) {
for($j = 0 ; $j < count($results) ; $j++) {
$line .= $results[$j][0].';'.$results[$j][1]."\n";
$total = $total + $results[$j][1];
}
$line .= get_lang('Total').";".$total."\n";
} else {
$line= get_lang('NoResult')."";
}
} else {
$new_view = substr_replace($view,'1',0,1);
}
return array($title_line, $line);
}
/**
* Displays the exercise results for a specific user in a specific course.
* @todo remove globals
*/
public function display_exercise_tracking_info($view, $user_id, $course_id)
{
global $TABLECOURSE_EXERCICES, $TABLETRACK_EXERCICES, $TABLETRACK_HOTPOTATOES, $dateTimeFormatLong;
if (substr($view,1,1) == '1') {
$new_view = substr_replace($view,'0',1,1);
$title[1]= get_lang('ExercicesDetails');
$line='';
$sql = "SELECT ce.title, te.exe_result , te.exe_weighting, UNIX_TIMESTAMP(te.exe_date)
FROM $TABLECOURSE_EXERCICES AS ce , $TABLETRACK_EXERCICES AS te
WHERE te.exe_cours_id = '$course_id'
AND te.exe_user_id = '$user_id'
AND te.exe_exo_id = ce.id
ORDER BY ce.title ASC, te.exe_date ASC";
$hpsql = "SELECT te.exe_name, te.exe_result , te.exe_weighting, UNIX_TIMESTAMP(te.exe_date)
FROM $TABLETRACK_HOTPOTATOES AS te
WHERE te.exe_user_id = '$user_id' AND te.exe_cours_id = '$course_id'
ORDER BY te.exe_cours_id ASC, te.exe_date ASC";
$hpresults = getManyResultsXCol($hpsql, 4);
$NoTestRes = 0;
$NoHPTestRes = 0;
$results = getManyResultsXCol($sql, 4);
$title_line=get_lang('ExercicesTitleExerciceColumn').";".get_lang('Date').';'.get_lang('ExercicesTitleScoreColumn')."\n";
if (is_array($results)) {
for($i = 0; $i < sizeof($results); $i++)
{
$display_date = api_convert_and_format_date($results[$i][3], null, date_default_timezone_get());
$line .= $results[$i][0].";".$display_date.";".$results[$i][1]." / ".$results[$i][2]."\n";
}
} else {
// istvan begin
$NoTestRes = 1;
}
// The Result of Tests
if (is_array($hpresults)) {
for($i = 0; $i < sizeof($hpresults); $i++) {
$title = GetQuizName($hpresults[$i][0],'');
if ($title == '')
$title = basename($hpresults[$i][0]);
$display_date = api_convert_and_format_date($hpresults[$i][3], null, date_default_timezone_get());
$line .= $title.';'.$display_date.';'.$hpresults[$i][1].'/'.$hpresults[$i][2]."\n";
}
} else {
$NoHPTestRes = 1;
}
if ($NoTestRes == 1 && $NoHPTestRes == 1) {
$line=get_lang('NoResult');
}
} else {
$new_view = substr_replace($view,'1',1,1);
}
return array($title_line, $line);
}
/**
* Displays the student publications for a specific user in a specific course.
* @todo remove globals
*/
public function display_student_publications_tracking_info($view, $user_id, $course_id)
{
global $TABLETRACK_UPLOADS, $TABLECOURSE_WORK, $dateTimeFormatLong, $_course;
if (substr($view,2,1) == '1') {
$new_view = substr_replace($view,'0',2,1);
$sql = "SELECT u.upload_date, w.title, w.author, w.url
FROM $TABLETRACK_UPLOADS u , $TABLECOURSE_WORK w
WHERE u.upload_work_id = w.id
AND u.upload_user_id = '$user_id'
AND u.upload_cours_id = '$course_id'
ORDER BY u.upload_date DESC";
$results = getManyResultsXCol($sql,4);
$title[1]=get_lang('WorksDetails');
$line='';
$title_line=get_lang('WorkTitle').";".get_lang('WorkAuthors').";".get_lang('Date')."\n";
if (is_array($results)) {
for($j = 0 ; $j < count($results) ; $j++) {
$pathToFile = api_get_path(WEB_COURSE_PATH).$_course['path']."/".$results[$j][3];
$beautifulDate = api_convert_and_format_date($results[$j][0], null, date_default_timezone_get());
$line .= $results[$j][1].";".$results[$j][2].";".$beautifulDate."\n";
}
} else {
$line= get_lang('NoResult');
}
} else {
$new_view = substr_replace($view,'1',2,1);
}
return array($title_line, $line);
}
/**
* Displays the links followed for a specific user in a specific course.
* @todo remove globals
*/
public function display_links_tracking_info($view, $user_id, $course_id)
{
global $TABLETRACK_LINKS, $TABLECOURSE_LINKS;
$line = null;
if (substr($view,3,1) == '1') {
$new_view = substr_replace($view,'0',3,1);
$title[1]=get_lang('LinksDetails');
$sql = "SELECT cl.title, cl.url
FROM $TABLETRACK_LINKS AS sl, $TABLECOURSE_LINKS AS cl
WHERE sl.links_link_id = cl.id
AND sl.links_cours_id = '$course_id'
AND sl.links_user_id = '$user_id'
GROUP BY cl.title, cl.url";
$results = getManyResults2Col($sql);
$title_line= get_lang('LinksTitleLinkColumn')."\n";
if (is_array($results)) {
for ($j = 0 ; $j < count($results) ; $j++) {
$line .= $results[$j][0]."\n";
}
} else {
$line=get_lang('NoResult');
}
} else {
$new_view = substr_replace($view,'1',3,1);
}
return array($title_line, $line);
}
/**
* Displays the documents downloaded for a specific user in a specific course.
* @param string kind of view inside tracking info
* @param int User id
* @param string Course code
* @param int Session id (optional, default = 0)
* @return void
*/
public function display_document_tracking_info($view, $user_id, $course_id, $session_id = 0)
{
// protect data
$user_id = intval($user_id);
$course_id = Database::escape_string($course_id);
$session_id = intval($session_id);
$downloads_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
if (substr($view,4,1) == '1') {
$new_view = substr_replace($view,'0',4,1);
$title[1]= get_lang('DocumentsDetails');
$sql = "SELECT down_doc_path
FROM $downloads_table
WHERE down_cours_id = '$course_id'
AND down_user_id = '$user_id'
AND down_session_id = '$session_id'
GROUP BY down_doc_path";
$results = getManyResults1Col($sql);
$title_line = get_lang('DocumentsTitleDocumentColumn')."\n";
$line = null;
if (is_array($results)) {
for ($j = 0 ; $j < count($results) ; $j++) {
$line .= $results[$j]."\n";
}
} else {
$line = get_lang('NoResult');
}
} else {
$new_view = substr_replace($view,'1',4,1);
}
return array($title_line, $line);
}
}