(Score average fixes)
*/
define('SESSION_LINK_TARGET','_self');
class Tracking {
/**
* Calculates the time spent on the platform by a user
* @param int User id
* @param bool True for calculating only time spent last week (optional)
* @param int Timestamp for filtering by Day (optional, default = 0)
* @return timestamp $nb_seconds
*/
public static function get_time_spent_on_the_platform($user_id, $last_week = false, $by_day = 0) {
$tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$condition_time = '';
if ($last_week) {
$a_last_week = get_last_week();
$fday_last_week = date('Y-m-d H:i:s',$a_last_week[0]);
$lday_last_week = date('Y-m-d H:i:s',$a_last_week[6]);
$condition_time = ' AND (login_date >= "'.$fday_last_week.'" AND logout_date <= "'.$lday_last_week.'") ';
} else if (!empty($by_day)) {
$fdate_time = date('Y-m-d',$by_day).' 00:00:00';
$ldate_time = date('Y-m-d',$by_day).' 23:59:59';
$condition_time = ' AND (login_date >= "'.$fdate_time.'" AND logout_date <= "'.$ldate_time.'" ) ';
}
$sql = 'SELECT login_date, logout_date FROM '.$tbl_track_login.'
WHERE login_user_id = '.intval($user_id).$condition_time;
$rs = Database::query($sql);
$nb_seconds = 0;
$wrong_logout_dates = false;
while ($a_connections = Database::fetch_array($rs)) {
$s_login_date = $a_connections["login_date"];
$s_logout_date = $a_connections["logout_date"];
$i_timestamp_login_date = strtotime($s_login_date);
$i_timestamp_logout_date = strtotime($s_logout_date);
if($i_timestamp_logout_date>0)
{
$nb_seconds += ($i_timestamp_logout_date - $i_timestamp_login_date);
}
else
{ // there are wrong datas in db, then we can't give a wrong time
$wrong_logout_dates = true;
}
}
if($nb_seconds>0 || !$wrong_logout_dates)
{
return $nb_seconds;
}
else
{
return -1; //-1 means we have wrong datas in the db
}
}
/**
* Calculates the time spent on the course
* @param integer User id
* @param string Course code
* @param int Session id (optional)
* @return timestamp Time in seconds
*/
public static function get_time_spent_on_the_course($user_id, $course_code, $session_id = 0) {
// protect datas
$course_code = Database::escape_string($course_code);
$session_id = intval($session_id);
$tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
$condition_user = "";
if (is_array($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 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_statistic_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_statistic_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 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_statistic_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, date_default_timezone_get());
} 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_course_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
$sql = 'SELECT login_course_date FROM '.$tbl_track_e_course_access.'
WHERE user_id = '.$student_id.'
AND course_code = "'.$course_code.'"
AND session_id = '.$session_id.'
ORDER BY login_course_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, null, date_default_timezone_get());
$timestamp = api_strtotime($last_login_date);
$currentTimestamp = time();
//If the last connection is > than 7 days, the text is red
//345600 = 7 days in seconds
if ($currentTimestamp - $timestamp > 604800) {
if ($convert_date) {
return '' . api_format_date($last_login_date, DATE_FORMAT_SHORT) . (api_is_allowed_to_edit()?'
':'').'';
} else {
return $last_login_date;
}
} else {
if ($convert_date) {
return api_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) {
// protect data
$month_filter = '';
if ($start < 0) { $start = 0; }
if (!isset($stop) or ($stop < 0)) {
$stop = api_get_utc_datetime();
}
$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_statistic_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;
}
/**
* This function gets the score average from all tests in a course by student
* @param int|array Student(s) id
* @param string Course code
* @param int Exercise id (optional), filtered by exercise
* @param int Session 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) {
// protect datas
$course_code = Database::escape_string($course_code);
// get the informations of the course
$a_course = CourseManager :: get_course_information($course_code);
if(!empty($a_course['db_name'])) {
// table definition
$tbl_course_quiz = Database::get_course_table(TABLE_QUIZ_TEST,$a_course['db_name']);
$tbl_stats_exercise = Database :: get_statistic_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 ";
}
$sql = "SELECT count(id) FROM $tbl_course_quiz WHERE active <> -1 $condition_quiz ";
$count_quiz = Database::fetch_row(Database::query($sql));
$quiz_avg_total_score = 0;
if (!empty($count_quiz[0]) && !empty($student_id)) {
$condition_user = "";
if (is_array($student_id)) {
$condition_user = " AND exe_user_id IN (".implode(',',$student_id).") ";
} else {
$condition_user = " AND exe_user_id = '$student_id' ";
}
if (empty($exercise_id)) {
$sql = "SELECT id FROM $tbl_course_quiz WHERE active <> -1 $condition_quiz";
$exercises = Database::fetch_row(Database::query($sql));
$exercise_list = array();
$exercise_id = 0;
if (!empty($exercises)) {
foreach($exercises as $row) {
$exercise_list[] = $row['id'];
}
$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
FROM $tbl_stats_exercise
WHERE exe_exo_id IN ('".$exercise_id."')
$condition_user
AND orig_lp_id = 0
AND exe_cours_id = '$course_code'
AND orig_lp_item_id = 0 $condition_session
ORDER BY exe_date DESC";
$res = Database::query($sql);
$row = Database::fetch_array($res);
$quiz_avg_score = 0;
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);
}
return $quiz_avg_score;
}
}
return null;
}
/**
* Get count student's exercise attempts
* @param int Student id
* @param string Course code
* @param int Exercise id
* @param int Learning path id (optional), for showing attempts inside a learning path $lp_id and $lp_item_id params are required.
* @param int Learning 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 function count_student_exercise_attempts($student_id, $course_code, $exercise_id, $lp_id = 0, $lp_item_id = 0, $session_id = 0) {
$course_code = Database::escape_string($course_code);
$course_info = CourseManager :: get_course_information($course_code);
$student_id = intval($student_id);
$exercise_id = intval($exercise_id);
$session_id = intval($session_id);
$count_attempts = 0;
if (!empty($lp_id)) $lp_id = intval($lp_id);
if (!empty($lp_item_id)) $lp_id = intval($lp_item_id);
if (!empty($course_info['db_name'])) {
$tbl_stats_exercices = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES, $course_info['db_name']);
$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 orig_lp_id = $lp_id
AND orig_lp_item_id = $lp_item_id
AND exe_user_id= $student_id
AND session_id = $session_id ";
$rs = Database::query($sql);
$row = Database::fetch_row($rs);
$count_attempts = $row[0];
}
return $count_attempts;
}
/**
* 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, $lp_ids = array(), $session_id = null, $return_array = false) {
// get the informations of the course
$a_course = CourseManager :: get_course_information($course_code);
if (!empty($a_course['db_name'])) {
// table definition
$tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW, $a_course['db_name']);
$tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN, $a_course['db_name']);
// Compose a filter based on optional learning paths list given
$condition_lp = "";
if (!empty($lp_ids)) {
if (count($lp_ids) > 0) {
$condition_lp =" WHERE id IN(".implode(',',$lp_ids).") ";
}
}
$session_id = intval($session_id);
$sql = "SELECT id FROM $tbl_course_lp lp $condition_lp";
$res_count_lp = Database::query($sql);
// count the number of learning paths
$lp_id = array();
while ($row_lp = Database::fetch_array($res_count_lp,'ASSOC')) {
//$visibility = api_get_item_visibility($a_course, TOOL_LEARNPATH, $row_lp['id'], $session_id);
// if ($visibility == 1) {
$lp_id[] = $row_lp['id'];
//}
}
$count_lp = count($lp_id);
$avg_progress = 0;
//if there is at least one learning path and one student
if ($count_lp>0 && !empty($student_id)) {
$condition_user = "";
if (is_array($student_id)) {
array_walk($student_id,'intval');
$condition_user = " lp_view.user_id IN (".implode(',',$student_id).") AND ";
} else {
$student_id = intval($student_id);
$condition_user = " lp_view.user_id = '$student_id' AND ";
}
// Get last view for each student (in case of multi-attempt)
// Also filter on LPs of this session
$sql_maxes = "SELECT MAX(view_count), progress FROM $tbl_course_lp_view lp_view ".
"WHERE $condition_user session_id = $session_id AND lp_view.lp_id IN (".implode(',',$lp_id).") ".
"GROUP BY lp_id, user_id";
$res_maxes = Database::query($sql_maxes);
$sum = 0;
while ($row_maxes = Database::fetch_array($res_maxes)) {
$sum += $row_maxes[1];
}
// average progress = total sum divided by the number of views
// summed up.
$number_items = count($lp_id);
if ($number_items == 0) {
return 0; //not necessary to return something else if there is no view
}
if (!$return_array) {
$avg_progress = round($sum / $number_items, 1);
return $avg_progress;
} else {
return array($sum, $number_items);
}
}
}
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.
* @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 ($debug) echo '
* @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);
$lp_table = Database :: get_course_table(TABLE_LP_MAIN,$course_info['dbName']);
$lp_view_table = Database :: get_course_table(TABLE_LP_VIEW,$course_info['dbName']);
$lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW,$course_info['dbName']);
$lp_item_table = Database :: get_course_table(TABLE_LP_ITEM,$course_info['dbName']);
$sql_type='SELECT id, lp_type FROM '.$lp_table;
$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 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 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 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 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 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_statistic_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_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 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_statistic_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) {
//protect data
$course_code = Database::escape_string($course_code);
$course_info=api_get_course_info($course_code);
$data = array();
$TABLETRACK_LINKS = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
$TABLECOURSE_LINKS = Database::get_course_table(TABLE_LINK, $course_info['dbName']);
$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 sl.links_link_id = cl.id
AND sl.links_cours_id = '$_cid'
$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
* @return string html code
*/
function show_user_progress($user_id, $session_id = 0, $extra_params = '', $show_courses = true) {
require_once api_get_path(LIBRARY_PATH).'sessionmanager.lib.php';
global $_configuration;
$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);
$tbl_access_rel_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$user_id = intval($user_id);
// get course list
if ($_configuration['multiple_access_urls']) {
$sql = 'SELECT cu.course_code FROM '.$tbl_course_user.' cu INNER JOIN '.$tbl_access_rel_course.' a ON(a.course_code = cu.course_code) WHERE user_id='.$user_id.' AND relation_type<>'.COURSE_RELATION_TYPE_RRHH.' AND access_url_id = '.api_get_current_access_url_id().'';
} else {
$sql = 'SELECT course_code FROM '.$tbl_course_user.' WHERE user_id='.$user_id.' AND relation_type<>'.COURSE_RELATION_TYPE_RRHH.' ';
}
$rs = Database::query($sql);
$courses = $course_in_session = array();
while($row = Database :: fetch_array($rs)) {
$courses[$row['course_code']] = CourseManager::get_course_information($row['course_code']);
}
// Get the list of sessions where the user is subscribed as student
if ($_configuration['multiple_access_urls']) {
$sql = 'SELECT DISTINCT cu.course_code, id_session as session_id FROM '.$tbl_session_course_user.' cu INNER JOIN '.$tbl_access_rel_session.' a ON(a.session_id = cu.id_session) WHERE id_user='.$user_id.' AND access_url_id = '.api_get_current_access_url_id().'';
} else {
$sql = 'SELECT DISTINCT course_code, id_session as session_id FROM '.$tbl_session_course_user.' WHERE id_user='.$user_id;
}
$rs = Database::query($sql);
while ($row = Database :: fetch_array($rs)) {
$course_in_session[$row['session_id']][$row['course_code']] = CourseManager::get_course_information($row['course_code']);
}
$html = '';
//Courses list
if ($show_courses) {
if (!empty($courses)) {
$html .= Display::tag('h1', Display::return_icon('course.png', get_lang('MyCourses'), array(), 22).' '.get_lang('MyCourses'));
$html .= '';
$html .= '
'.Display::tag('th', get_lang('Course'), array('width'=>'300px')).'
'.Display::tag('th', get_lang('Time'), 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 $enreg) {
$weighting = 0;
$total_time_login = Tracking :: get_time_spent_on_the_course($user_id, $enreg['code']);
$time = api_time_to_hms($total_time_login);
$progress = Tracking :: get_avg_student_progress($user_id, $enreg['code']);
$percentage_score = Tracking :: get_avg_student_score($user_id, $enreg['code'], array());
$last_connection = Tracking :: get_last_connection_date_on_the_course($user_id, $enreg['code']);
if ($enreg['code'] == $_GET['course'] && empty($_GET['session_id'])) {
$html .= '';
} else {
$html .= '
';
}
$url = api_get_course_url($enreg['code'], $session_id);
$course_url = Display::url($enreg['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 ($enreg['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
if (!empty($course_in_session)) {
$html .= Display::tag('h1',Display::return_icon('session.png', get_lang('Sessions'), array(), 22).' '.get_lang('Sessions'));
$exercise_graph_list = array();
$exercise_graph_name_list = array();
$session_graph = array();
foreach ($course_in_session as $my_session_id=>$course_list) {
$session_name = api_get_session_name($my_session_id);
$user_count = count(SessionManager::get_users_by_session($session_id));
foreach ($course_list as $course_data) {
$exercise_list = get_all_exercises($course_data, $my_session_id);
foreach($exercise_list as $exercise_data) {
if (!empty($exercise_data['start_time']) && $exercise_data['start_time'] != '0000-00-00 00:00:00') {
//echo $session_name.' - '.$course_data['title']." \n ".$exercise_data['title'].'
';
$exercise_graph_list[] = intval(get_best_average_score_by_exercise($exercise_data['id'], $course_data['code'], $my_session_id, $user_count));
$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);
}
//$user_results[] = 100;
$user_results[] = $score;
$exercise_graph_name_list[]= cut($session_name.' - '.$course_data['title'], 25)." \n ".cut($exercise_data['title'], 25);
}
}
}
if (!empty($user_results) && !empty($exercise_graph_list)) {
$session_graph[$my_session_id] = self::generate_session_exercise_graph($exercise_graph_name_list, $user_results, $exercise_graph_list);
}
}
$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('DoneExercises'), 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 $key => $course_list) {
if (isset($session_id) && !empty($session_id)) {
if ($session_id != $key) {
continue;
}
}
$all_exercises = 0;
$all_done_exercise = 0;
$all_average = 0;
$stats_array = array();
foreach ($course_list as $enreg) {
//All exercises in the course @todo change for a real count
$exercises = count(get_all_exercises($enreg, $key));
//Count of user results
//$done_exercises = get_best_exercise_results_by_course($enreg['code'], $key);
$done_exercises = get_count_exercises_attempted_by_course($enreg['code'], $key);
//Average
$average = get_average_score_by_course($enreg['code'], $key);
$all_exercises += $exercises;
$all_done_exercise += $done_exercises;
$all_average += $average;
$stats_array[$enreg['code']] = array('exercises'=>$exercises, 'done_exercises'=>$done_exercises, 'average'=>$average);
}
$all_average = $all_average / count($course_list);
if (isset($_GET['session_id']) && $key == $_GET['session_id']) {
$html .= '';
} else {
$html .= '
';
}
$html .= Display::tag('td', api_get_session_name($key));
$html .= Display::tag('td', $all_exercises);
$html .= Display::tag('td', $all_done_exercise);
$html .= Display::tag('td', convert_to_percentage($all_average));
if (isset($_GET['session_id']) && $key == $_GET['session_id']) {
$icon = Display::url(Display::return_icon('2rightarrow_na.gif', get_lang('Details')), '?session_id='.$key);
} else {
$icon = Display::url(Display::return_icon('2rightarrow.gif', get_lang('Details')), '?session_id='.$key);
}
$html .= Display::tag('td', $icon);
$html .= '
';
}
$html .='
';
//Checking selected session
if (isset($_GET['session_id'])) {
$key = intval($_GET['session_id']);
$html .= Display::tag('h2',api_get_session_name($key).' - '.get_lang('CourseList'));
$course_list = $course_in_session[$key];
$html .= '';
$html .= '
'.get_lang('Course').' |
'.Display::tag('th', get_lang('PublishedExercises'), array('class'=>'head')).'
'.Display::tag('th', get_lang('DoneExercises'), array('class'=>'head')).'
'.Display::tag('th', get_lang('AverageExerciseResult'), array('class'=>'head')).'
'.Display::tag('th', get_lang('Time') , 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 $enreg) {
//All exercises in the course @todo change for a real count
$exercises = count(get_all_exercises($enreg, $key));
//Count of user results
//$done_exercises = get_best_exercise_results_by_course($enreg['code'], $key);
$done_exercises = get_count_exercises_attempted_by_course($enreg['code'], $key);
//Average
$average = get_average_score_by_course($enreg['code'], $key);
$stats_array[$enreg['code']] = array('exercises'=>$exercises, 'done_exercises'=>$done_exercises, 'average'=>$average);
$weighting = 0;
$last_connection = Tracking :: get_last_connection_date_on_the_course($user_id, $enreg['code'], $key);
$progress = Tracking :: get_avg_student_progress($user_id, $enreg['code'],array(), $key);
$total_time_login = Tracking :: get_time_spent_on_the_course($user_id, $enreg['code'], $key);
$time = api_time_to_hms($total_time_login);
$percentage_score = Tracking :: get_avg_student_score($user_id, $enreg['code'], array(), $key);
if ($enreg['code'] == $_GET['course'] && $_GET['session_id'] == $key) {
$html .= '';
} else {
$html .= '
';
}
$url = api_get_course_url($enreg['code'], $key);
$course_url = Display::url($enreg['title'], $url, array('target'=>SESSION_LINK_TARGET));
$html .= Display::tag('td', $course_url);
$html .= Display::tag('td', $stats_array[$enreg['code']]['exercises']);
$html .= Display::tag('td', $stats_array[$enreg['code']]['done_exercises']);
$html .= Display::tag('td', convert_to_percentage($stats_array[$enreg['code']]['average']));
$html .= Display::tag('td', $time, array('align'=>'center'));
if (is_numeric($progress)) {
$progress = $progress.'%';
} else {
$progress = '0%';
}
$html .= Display::tag('td', $progress, array('align'=>'center'));
if (is_numeric($percentage_score)) {
$percentage_score = $percentage_score.'%';
} else {
$percentage_score = '0%';
}
$html .= Display::tag('td', $percentage_score, array('align'=>'center'));
$html .= Display::tag('td', $last_connection, array('align'=>'center'));
if ($enreg['code'] == $_GET['course'] && $_GET['session_id'] == $key) {
$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[$key])) {
$html .= Display::div($session_graph[$my_session_id], array('id'=>'session_graph_'.$key,'class'=>'chart-session', 'style'=>'position:relative; text-align: center;') );
}
}
}
if (!empty($html)) {
$html = Display::div($html, array('class'=>'rounded_div', 'style'=>'position:relative; float:none; width:95%'));
}
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
*/
function show_course_detail($user_id, $course_code, $session_id) {
$html = '';
if (isset($course_code)) {
require_once api_get_path(SYS_CODE_PATH).'exercice/exercise.lib.php';
$user_id = intval($user_id);
$session_id = intval($session_id);
$course = Database::escape_string($course_code);
$course_info = CourseManager::get_course_information($course);
$tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
$tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
$tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW, $course_info['db_name']);
$tbl_course_lp_view_item = Database :: get_course_table(TABLE_LP_ITEM_VIEW, $course_info['db_name']);
$tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN, $course_info['db_name']);
$tbl_course_lp_item = Database :: get_course_table(TABLE_LP_ITEM, $course_info['db_name']);
$tbl_course_quiz = Database :: get_course_table(TABLE_QUIZ_TEST, $course_info['db_name']);
$session_name = api_get_session_name($session_id);
$html .= Display::tag('h2', $course_info['title']);
$html .='';
$html .= Display::tag('th', get_lang('Learnpaths'), array('class'=>'head', 'style'=>'color:#000'));
$html .= Display::tag('th', get_lang('Time'), 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('LastConnexion'), array('class'=>'head', 'style'=>'color:#000'));
$html .= '';
if (empty($session_id)) {
$sql_learnpath = "SELECT lp.name,lp.id FROM ".$tbl_course_lp." AS lp WHERE session_id = 0 ORDER BY lp.display_order";
} else {
$sql_learnpath = "SELECT lp.name,lp.id FROM ".$tbl_course_lp." AS lp ORDER BY lp.display_order";
}
$result_learnpath = Database::query($sql_learnpath);
if (Database::num_rows($result_learnpath) > 0) {
while($learnpath = Database::fetch_array($result_learnpath)) {
$progress = Tracking::get_avg_student_progress($user_id, $course, array($learnpath['id']), $session_id);
$last_connection_in_lp = Tracking::get_last_connection_time_in_lp($user_id, $course, $learnpath['id'], $session_id);
$time_spent_in_lp = Tracking::get_time_spent_in_lp($user_id, $course, array($learnpath['id']), $session_id);
$time_spent_in_lp = api_time_to_hms($time_spent_in_lp);
$html .= '';
$html .= Display::tag('td', $learnpath['name']);
$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'));
$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 .='
';
// This code was commented on purpose see BT#924
/*$sql = 'SELECT visibility FROM '.$course_info['db_name'].'.'.TABLE_TOOL_LIST.' WHERE name="quiz"';
$result_visibility_tests = Database::query($sql);
if (Database::result($result_visibility_tests, 0, 'visibility') == 1) {*/
//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)) {
$sql_exercices = "SELECT quiz.title,id, results_disabled FROM ".$tbl_course_quiz." AS quiz WHERE active='1' AND session_id = 0";
} else {
$sql_exercices = "SELECT quiz.title,id, results_disabled FROM ".$tbl_course_quiz." AS quiz WHERE active='1'";
}
$result_exercices = Database::query($sql_exercices);
$to_graph_exercise_result = array();
if (Database::num_rows($result_exercices) > 0) {
$score = $weighting = $exe_id = 0;
while ($exercices = Database::fetch_array($result_exercices)) {
//if ($exercices['id'] != 3) continue;
$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);
//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);
$html .= '';
$url = api_get_path(WEB_CODE_PATH)."exercice/exercise_submit.php?cidReq={$course_info['code']}&id_session=$session_id&exerciseId={$exercices['id']}";
$exercices['title'] = Display::url($exercices['title'], $url, array('target'=>SESSION_LINK_TARGET));
$html .= Display::tag('td', $exercices['title']);
//Exercise configuration show results show results or show only score
if ($exercices['results_disabled'] == 0 || $exercices['results_disabled'] == 2) {
$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 .= ' '.Display::return_icon('quiz.gif', get_lang('Quiz')).' ';
$latest_attempt_url .= '../exercice/exercise_show.php?origin=myprogress&id='.$exe_id.'&cidReq='.$course_info['code'].'&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;
}
$position = get_exercise_result_ranking($my_score, $exe_id, $exercices['id'], $course_info['code'], $session_id);
$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']]);
}
}
echo 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 .= '
';
}
if (!empty($html)) {
$html = Display::div($html, array('class'=>'rounded_div', 'style'=>'position:relative; float:none; width:95%'));
}
return $html;
}
/**
* Generates an histogram
*
* @param array list of exercise names
* @param array my results 0 to 100
* @param array average scores 0-100
*/
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';
//var_dump($names, $my_results, $average);
$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->SetXAxisName(get_lang("Exercises"));
$data_set->SetYAxisName(get_lang("Percentage"));
$data_set->SetYAxisUnit("%");
// Initialise the graph
$main_width = 820;
$main_height = 440;
$y_label_angle = 35;
$data_set->RemoveSerie("Serie3");
$graph = new pChart($main_width, $main_height);
//$graph->setFixedScale(0,100);
$graph->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf',8);
$graph->setGraphArea(75,50,$main_width-140, $main_height-120);
$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->drawCubicCurve($data_set->GetData(),$data_set->GetDataDescription());
$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',8);
$graph->drawLegend($main_width - 130,50,$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);
$graph_id = 'generate_session_exercise_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(), $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
*/
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;
}
//var_dump($exercise_result, $my_exercise_result);
$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;
}
}
//var_dump($my_final_array, $final_array); exit;
//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;
}
}
//var_dump($my_final_array, $final_array); echo '
';
//echo ''; var_dump($my_exercise_result, $exercise_result,$x_axis);
$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
*/
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 = $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;
}
//var_dump($exercise_result, $my_exercise_result);
$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;
}
}
//var_dump($my_final_array, $final_array); exit;
//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;
}
}
//var_dump($my_final_array, $final_array); echo '
';
//echo ''; var_dump($my_exercise_result, $exercise_result,$x_axis);
$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;
}
}
class TrackingCourseLog {
function count_item_resources() {
global $session_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.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;
}
function get_item_resources_data($from, $number_of_items, $column, $direction) {
global $dateTimeFormatLong, $session_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);
$sql = "SELECT
tool as col0,
lastedit_type as col1,
ref as ref,
user.username as col3,
insert_date as col5,
visibility as col6
FROM $table_item_property track_resource, $table_user user
WHERE 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 ";
}
$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'];
if ($row['col0'] == 'thematic_plan' || $row['col0'] == 'thematic_advance') {
$tbl_thematic = Database :: get_course_table(TABLE_THEMATIC);
$rs_thematic = Database::query("SELECT thematic_id FROM $table_tool WHERE id=$ref");
$row_thematic = Database::fetch_array($rs_thematic);
$thematic_id = $row_thematic['thematic_id'];
$query = "SELECT session.id, session.name, user.username FROM $tbl_thematic t, $table_session session, $table_user user" .
" WHERE t.session_id = session.id AND session.id_coach = user.user_id AND t.id = $thematic_id";
} else {
$query = "SELECT session.id, session.name, user.username FROM $table_tool tool, $table_session session, $table_user user" .
" WHERE tool.session_id = session.id AND session.id_coach = user.user_id AND tool.$id = $ref";
}
$recorset = Database::query($query);
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[5] = api_convert_and_format_date($row['col5'], null, date_default_timezone_get());
$row[4] = '';
switch ($table_name['table_name']) {
case 'document' :
$query_document = "SELECT tool.title as title FROM $table_tool tool" .
" WHERE id = $ref";
$rs_document = Database::query($query_document);
$obj_document = Database::fetch_object($rs_document);
$row[4] = $obj_document->title;
break;
case 'announcement':
$query_document = "SELECT title FROM $table_tool " .
" WHERE id = $ref";
$rs_document = Database::query($query_document);
$obj_document = Database::fetch_object($rs_document);
$row[4] = $obj_document->title;
break;
case 'glossary':
$query_document = "SELECT name FROM $table_tool " .
" WHERE glossary_id = $ref";
$rs_document = Database::query($query_document);
$obj_document = Database::fetch_object($rs_document);
$row[4] = $obj_document->name;
break;
case 'lp':
$query_document = "SELECT name FROM $table_tool " .
" WHERE id = $ref";
$rs_document = Database::query($query_document);
$obj_document = Database::fetch_object($rs_document);
$row[4] = $obj_document->name;
break;
case 'quiz':
$query_document = "SELECT title FROM $table_tool " .
" WHERE id = $ref";
$rs_document = Database::query($query_document);
$obj_document = Database::fetch_object($rs_document);
$row[4] = $obj_document->title;
break;
case 'course_description':
$query_document = "SELECT title FROM $table_tool " .
" WHERE id = $ref";
$rs_document = Database::query($query_document);
$obj_document = Database::fetch_object($rs_document);
$row[4] = $obj_document->title;
break;
case 'thematic':
$rs = Database::query("SELECT title FROM $table_tool WHERE id = $ref");
if (Database::num_rows($rs) > 0) {
$obj = Database::fetch_object($rs);
$row[4] = $obj->title;
}
break;
case 'thematic_advance':
$rs = Database::query("SELECT content FROM $table_tool WHERE id = $ref");
if (Database::num_rows($rs) > 0) {
$obj = Database::fetch_object($rs);
$row[4] = $obj->content;
}
break;
case 'thematic_plan':
$rs = Database::query("SELECT title FROM $table_tool WHERE id = $ref");
if (Database::num_rows($rs) > 0) {
$obj = Database::fetch_object($rs);
$row[4] = $obj->title;
}
break;
default:
break;
}
$row2 = $name_session;
if (!empty($coach_name)) {
$row2 .= '
'.get_lang('Coach').': '.$coach_name;
}
$row[2] = $row2;
$resources[] = $row;
}
}
return $resources;
}
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);
}
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
*/
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
*/
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
*/
function count_student_in_course() {
global $nbStudents;
return $nbStudents;
}
function sort_users($a, $b) {
return strcmp(trim(api_strtolower($a[$_SESSION['tracking_column']])), trim(api_strtolower($b[$_SESSION['tracking_column']])));
}
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
*/
function get_number_of_users() {
global $user_ids;
return count($user_ids);
}
/**
* Get data for users list in sortable with pagination
* @return array
*/
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);
$course_info = CourseManager::get_course_information($course_code);
$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 ($_configuration['multiple_access_urls']) {
$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
FROM $tbl_user as user $url_table
$condition_user $url_condition";
if (!in_array($direction, array('ASC','DESC'))) {
$direction = 'ASC';
}
$column = intval($column);
if ($is_western_name_order) {
$original_column = $column;
if ($original_column == 1) {
$column = 2;
}
if ($original_column == 2) {
$column = 1;
}
}
$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();
$row = array();
while ($user = Database::fetch_array($res, 'ASSOC')) {
//$user['user_id'] = $user['user_id']
$user['official_code'] = $user['col0'];
if ($is_western_name_order) {
$user['lastname'] = $user['col2'];
$user['firstname'] = $user['col1'];
} else {
$user['lastname'] = $user['col1'];
$user['firstname'] = $user['col2'];
}
$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.'%';
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
if (isset($_GET['additional_profile_field']) AND is_numeric($_GET['additional_profile_field'])) {
if (is_array($additional_user_profile_info[$user['user_id']])) {
$row['additional']=implode(', ', $additional_user_profile_info[$user['user_id']]);
} else {
$row['additional']=' ';
}
}
$user['link'] = '
';
// store columns in array $users
$user_row = array($user['official_code'],
$user['lastname'],
$user['firstname'],
$user['time'],
$user['average_progress'],
$user['student_score'],
$user['count_assignments'],
$user['count_messages'],
$user['first_connection'],
$user['last_connection'],
$user['additional'],
$user['link']);
$users[] = $user_row;
if ($export_csv) {
unset($user_row[11]);
unset($user_row[12]);
$csv_content[] = $user_row;
}
}
return $users;
}
}
class TrackingUserLog {
/**
* Displays the number of logins every month for a specific user in a specific course.
*/
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_statistic_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
*/
function display_exercise_tracking_info($view, $user_id, $course_id)
{
global $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
*/
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
*/
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
*/
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_statistic_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')."
|
";
}
}
}
class TrackingUserLogCSV {
/**
* Displays the number of logins every month for a specific user in a specific course.
*/
function display_login_tracking_info($view, $user_id, $course_id, $session_id = 0)
{
$MonthsLong = $GLOBALS['MonthsLong'];
$track_access_table = Database::get_statistic_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
*/
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
*/
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
*/
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);
$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
*/
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_statistic_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";
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);
}
}