(Score average fixes) ============================================================================== */ 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, null, date_default_timezone_get()); if ($return_timestamp) { return api_strtotime($last_login_date); } else { if (!$warning_message) { return api_format_date($last_login_date, DATE_FORMAT_SHORT); } else { $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) { 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) { // 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)) { return api_convert_and_format_date($first_login_date, DATE_FORMAT_SHORT, date_default_timezone_get()); } } 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) { // 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) { return '' . api_format_date($last_login_date, DATE_FORMAT_SHORT) . (api_is_allowed_to_edit()?' ':'').''; } else { return api_format_date($last_login_date, DATE_FORMAT_SHORT); } } } return false; } /** * 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_lp =" 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 "; } $count_quiz = Database::fetch_row(Database::query("SELECT count(id) FROM $tbl_course_quiz WHERE active <> -1 $condition_quiz $condition_session")); $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' "; } $sql = "SELECT SUM(exe_result/exe_weighting*100) as avg_score FROM $tbl_stats_exercise WHERE exe_exo_id IN (SELECT id FROM $tbl_course_quiz WHERE active <> -1 $condition_quiz $condition_session) $condition_user AND orig_lp_id = 0 AND exe_cours_id = '$course_code' AND orig_lp_item_id = 0 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); } $count_attempt = Database::fetch_row(Database::query("SELECT count(*) FROM $tbl_stats_exercise WHERE exe_exo_id IN (SELECT id FROM $tbl_course_quiz WHERE active <> -1 $condition_quiz $condition_session) $condition_user AND orig_lp_id = 0 AND exe_cours_id = '$course_code' AND orig_lp_item_id = 0 ORDER BY exe_date DESC")); if(!empty($count_attempt[0])) { $quiz_avg_score = $quiz_avg_score / $count_attempt[0]; } $quiz_avg_total_score = $quiz_avg_score; return $quiz_avg_total_score/$count_quiz[0]; } } 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) { $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); $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 "; $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 * @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) { // 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(count($lp_ids) > 0) { $condition_lp =" WHERE id IN(".implode(',',$lp_ids).") "; } // Compose a filter based on optional session id $condition_session = ""; if (isset($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 "; } } $res_count_lp = Database::query("SELECT id FROM $tbl_course_lp lp $condition_lp $condition_session"); // count the number of learning paths $count_lp = Database::num_rows($res_count_lp); $lp_id = array(); while ($row_lp = Database::fetch_array($res_count_lp)) { $lp_id[] = $row_lp[0]; } $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)) { $$r = 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 ". "lp_view.lp_id IN (".implode(',',$lp_id).") ". "GROUP BY lp_id, user_id"; $res_maxes = Database::query($sql_maxes); $sum = $number_items = 0; while ($row_maxes = Database::fetch_array($res_maxes)) { $sum += $row_maxes[1]; $number_items++; } if ($number_items == 0) { return 0; //not necessary to return something else if there is no view } // average progress = total sum divided by the number of views // summed up. $avg_progress = round($sum / $number_items, 1); return $avg_progress; } } 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 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 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) { // get global tables names $course_table = Database :: get_main_table(TABLE_MAIN_COURSE); $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER); $table_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER); $tbl_stats_exercices = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES); $tbl_stats_attempts= Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT); $course = CourseManager :: get_course_information($course_code); if (!empty($course['db_name'])) { // get course tables names $tbl_quiz_questions= Database :: get_course_table(TABLE_QUIZ_QUESTION,$course['db_name']); $lp_table = Database :: get_course_table(TABLE_LP_MAIN,$course['db_name']); $lp_item_table = Database :: get_course_table(TABLE_LP_ITEM,$course['db_name']); $lp_view_table = Database :: get_course_table(TABLE_LP_VIEW,$course['db_name']); $lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW,$course['db_name']); // Compose a filter based on optional learning paths list given $condition_lp = ""; if(count($lp_ids) > 0) { $condition_lp =" WHERE id IN(".implode(',',$lp_ids).") "; } // Compose a filter based on optional session id $condition_session = ""; if (isset($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) $res_row_lp = Database::query("SELECT DISTINCT(id) FROM $lp_table $condition_lp $condition_session"); $count_row_lp = Database::num_rows($res_row_lp); $lp_list = array(); while ($row_lp = Database::fetch_array($res_row_lp)) { $lp_list[] = $row_lp[0]; } // Init local variables that will be used through the calculation $lp_scorm_score_total = 0; $lp_scorm_result_score_total = 0; $lp_scorm_loop=0; $lp_count = 0; $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)) { // Get all views through learning paths filter $sql = "SELECT MAX(view_count) as vc, id, progress, lp_id, user_id ". "FROM $lp_view_table ". "WHERE lp_id IN (".implode(',',$lp_list).") ". "$condition_user1 GROUP BY lp_id,user_id"; $rs_last_lp_view_id = Database::query($sql); $count_views = 0; $score_of_scorm_calculate = 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)) { $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']; // 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.score as score,lp_i.max_score, 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_i.item_type='sco' ". " OR lp_i.item_type='".TOOL_QUIZ."') ". " WHERE lp_view_id='$lp_view_id'"; $res_max_score = Database::query($sql_max_score); $count_total_loop = 0; $num_rows_max_score = Database::num_rows($res_max_score); // Go through each scorable element of this view $count_items = 0; while ($row_max_score = Database::fetch_array($res_max_score)) { $max_score = $row_max_score['max_score']; $score = $row_max_score['score']; 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) { $max_score = 100; } $lp_scorm_result_score_total += ($score/$max_score); $current_value = $score/$max_score; } else { // Case of a TOOL_QUIZ element $item_id = $row_max_score['iid']; $item_path = $row_max_score['path']; // 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_id = '$lp_id' ". //lp_id is already defined by the item_id " AND orig_lp_item_id = '$item_id' ". " AND exe_cours_id = '$course_code' ". " ORDER BY exe_date DESC limit 1"; $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); // 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' ) AS t"; $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']; } $lp_scorm_result_score_total += ($score/$max_score); $current_value = $score/$max_score; } else { //$lp_scorm_result_score_total += 0; } } $count_items++; } $score_of_scorm_calculate += $count_items?round((($lp_scorm_result_score_total/$count_items)*100),2):0; $count_views++; } } if ( $count_views > 0 ) { $score_of_scorm_calculate = round(($score_of_scorm_calculate/$count_views),2); } return $score_of_scorm_calculate; } } return null; } /** * 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['db_name'])) { $lp_table = Database :: get_course_table(TABLE_LP_MAIN, $course['db_name']); $t_lpv = Database :: get_course_table(TABLE_LP_VIEW, $course['db_name']); $t_lpiv = Database :: get_course_table(TABLE_LP_ITEM_VIEW, $course['db_name']); // Compose a filter based on optional learning paths list given $condition_lp = ""; if(count($lp_ids) > 0) { $condition_lp =" WHERE id IN(".implode(',',$lp_ids).") "; } // Compose a filter based on optional session id $condition_session = ""; if (isset($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) $res_row_lp = Database::query("SELECT DISTINCT(id) FROM $lp_table $condition_lp $condition_session"); $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 AND view.lp_id = '.$lp_id.' AND view.user_id = '.$student_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) { $course = CourseManager :: get_course_information($course_code); $student_id = intval($student_id); $lp_id = intval($lp_id); $last_time = 0; if (!empty($course['db_name'])) { $lp_table = Database :: get_course_table(TABLE_LP_MAIN, $course['db_name']); $t_lpv = Database :: get_course_table(TABLE_LP_VIEW, $course['db_name']); $t_lpiv = Database :: get_course_table(TABLE_LP_ITEM_VIEW, $course['db_name']); // 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 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 AND view.lp_id = '.$lp_id.' AND view.user_id = '.$student_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'; global $_configuration; if ($_configuration['multiple_access_urls']==true) { $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 ($_configuration['multiple_access_urls']==true) { $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_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE); $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 // ////////////////////////////////////////////////////////////// $dsl_session_coach = 'SELECT id_coach FROM ' . $tbl_session . ' WHERE id="' . $id_session . '" AND id_coach="' . $coach_id . '"'; $result = Database::query($dsl_session_coach); //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 . ' AS session_course_user INNER JOIN ' . $tbl_session_course . ' AS session_course ON session_course.course_code = session_course_user.course_code AND id_coach=' . $coach_id . ' WHERE id_user=' . $student_id;*/ $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 = '') { $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'; global $_configuration; if ($_configuration['multiple_access_urls']==true) { $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; $result = Database::query($sql); while ($row = Database::fetch_array($result)) { $a_courses[$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 ($_configuration['multiple_access_urls']==true) { $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 ($_configuration['multiple_access_urls']==true) $sql .= ' AND access_url_id = '.$access_url_id; } else { if ($_configuration['multiple_access_urls']==true) $sql .= ' WHERE access_url_id = '.$access_url_id; } $result = Database::query($sql); while ($row = Database::fetch_array($result)) { $a_courses[$row['course_code']] = $row['course_code']; } return $a_courses; } /** * Get sessions coached by user * @param int Coach id * @return array Sessions list */ public static function get_sessions_coached_by_user($coach_id) { // table definition $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); // protect datas $coach_id = intval($coach_id); // session where we are general coach $sql = 'SELECT DISTINCT id, name, date_start, date_end FROM ' . $tbl_session . ' WHERE id_coach=' . $coach_id; global $_configuration; if ($_configuration['multiple_access_urls']==true) { $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 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; } } $rs = Database::query($sql); while ($row = Database::fetch_array($rs)) { $a_sessions[$row["id"]] = $row; } // session where we are coach of a course $sql = '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'; global $_configuration; if ($_configuration['multiple_access_urls']==true) { $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 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; } } $rs = Database::query($sql); 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) { //protect datas $session_id = intval($session_id); // table definition $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION); $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 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, $session_id = null) { require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php'); // 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_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY, $a_course['db_name']); $tbl_student_publication = Database :: get_course_table(TABLE_STUDENT_PUBLICATION, $a_course['db_name']); $condition_user = ""; if (is_array($student_id)) { $condition_user = " AND ip.insert_user_id IN (".implode(',',$student_id).") "; } else { $condition_user = " AND ip.insert_user_id = '$student_id' "; } $condition_session = ""; if (isset($session_id)) { $session_id = intval($session_id); $condition_session = " AND pub.session_id = $session_id "; } $sql = "SELECT count(ip.tool) FROM $tbl_item_property ip INNER JOIN $tbl_student_publication pub ON ip.ref = pub.id WHERE ip.tool='work' $condition_user $condition_session "; $rs = Database::query($sql); $row = Database::fetch_row($rs); return $row[0]; } return null; } /** * Count messages per student inside forum tool * @param int 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 */ function count_student_messages($student_id, $course_code, $session_id = null) { require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php'); // protect datas $student_id = intval($student_id); $course_code = addslashes($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_forum_post = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']); $tbl_forum = Database :: get_course_table(TABLE_FORUM, $a_course['db_name']); $condition_user = ""; if (is_array($student_id)) { $condition_user = " WHERE post.poster_id IN (".implode(',',$student_id).") "; } else { $condition_user = " WHERE post.poster_id = '$student_id' "; } $condition_session = ""; if (isset($session_id)) { $session_id = intval($session_id); $condition_session = " AND forum.session_id = $session_id"; } $sql = "SELECT 1 FROM $tbl_forum_post post INNER JOIN $tbl_forum forum ON forum.forum_id = post.forum_id $condition_user $condition_session "; $rs = Database::query($sql); return Database::num_rows($rs); } else { return null; } } /** * 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) { //protect data $course_code = Database::escape_string($course_code); // get the informations of the course $a_course = CourseManager :: get_course_information($course_code); $count = 0; if (!empty($a_course['db_name'])) { $tbl_posts = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']); $tbl_forums = Database :: get_course_table(TABLE_FORUM, $a_course['db_name']); $condition_session = ''; if (isset($session_id)) { $session_id = intval($session_id); $condition_session = ' WHERE f.session_id = '. $session_id; } $sql = "SELECT count(*) FROM $tbl_posts p INNER JOIN $tbl_forums f ON f.forum_id = p.forum_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) { //protect data $course_code = Database::escape_string($course_code); // get the informations of the course $a_course = CourseManager :: get_course_information($course_code); $count = 0; if (!empty($a_course['db_name'])) { $tbl_threads = Database :: get_course_table(TABLE_FORUM_THREAD, $a_course['db_name']); $tbl_forums = Database :: get_course_table(TABLE_FORUM, $a_course['db_name']); $condition_session = ''; if (isset($session_id)) { $session_id = intval($session_id); $condition_session = ' WHERE f.session_id = '. $session_id; } $sql = "SELECT count(*) FROM $tbl_threads t INNER JOIN $tbl_forums f ON f.forum_id = t.forum_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 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) { //protect data $course_code = addslashes($course_code); // get the informations of the course $a_course = CourseManager :: get_course_information($course_code); $count = 0; if (!empty($a_course['db_name'])) { $condition_session = ''; if (isset($session_id)) { $session_id = intval($session_id); $condition_session = ' WHERE session_id = '. $session_id; } $tbl_forums = Database :: get_course_table(TABLE_FORUM, $a_course['db_name']); $sql = "SELECT count(*) FROM $tbl_forums $condition_session"; $result = Database::query($sql); $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) { //protect data $last_days = intval($last_days); $course_code = Database::escape_string($course_code); $session_id = intval($session_id); // get the informations of the course $a_course = CourseManager :: get_course_information($course_code); $count = 0; if (!empty($a_course['db_name'])) { $tbl_stats_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS, $a_course['db_name']); $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); $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) { //protect datas $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_statistic_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_statistic_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_statistic_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_statistic_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 = api_sql_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_statistic_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_organism = Database :: get_main_table(TABLE_MAIN_ORGANISM); $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; } /** * allow get 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 */ 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; while ($row_type=Database::fetch_array($rs_type)) { if ($row_type['lp_type']==1) {//lp dokeos $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" 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++; } if ((int)$count_loop > 0) { $avg_student_score = round(($average_data_sum / $count_loop * 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; } } 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')"; $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')"; 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) or die(Database::error()); $resources = array (); 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']; $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) { $row[0] = ''.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] = ''; if ($table_name['table_name'] == 'document') { $condition = 'tool.title as title'; $query_document = "SELECT $condition FROM $table_tool tool" . " WHERE id = $ref"; $rs_document = Database::query($query_document) or die(Database::error()); $obj_document = Database::fetch_object($rs_document); $row[4] = $obj_document->title; } $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; 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 = '
'; // the select field with the additional user profile fields (= this is where we select the field of which we want to see // the information the users have entered or selected. $return .= ''; // the form elements for the $_GET parameters (because the form is passed through GET foreach ($_GET as $key=>$value){ if ($key <> 'additional_profile_field') { $return .= ''; } } // the submit button $return .= ''; $return .= '
'; return $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 = api_sql_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 = api_sql_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; $course_code = Database::escape_string($course_code); $course_info = CourseManager :: get_course_information($course_code); $tbl_user = Database :: get_main_table(TABLE_MAIN_USER); // get all users data from a course for sortable with limit $condition_user = ""; if (is_array($user_ids)) { $condition_user = " WHERE user.user_id IN (".implode(',',$user_ids).") "; } else { $condition_user = " WHERE user.user_id = '$user_ids' "; } $sql = "SELECT user.user_id as col0, user.official_code as col1, user.lastname as col2, user.firstname as col3 FROM $tbl_user as user $condition_user "; 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(); $row = array(); while ($user = Database::fetch_row($res)) { $row[0] = $user[1]; if ($is_western_name_order) { $row[1] = $user[3]; $row[2] = $user[2]; } else { $row[1] = $user[2]; $row[2] = $user[3]; } $row[3] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user[0], $course_code, $session_id)); $avg_student_score = Tracking::get_avg_student_score($user[0], $course_code, array(), $session_id); $avg_student_progress = Tracking::get_avg_student_progress($user[0], $course_code, array(), $session_id); if (empty($avg_student_score)) {$avg_student_score=0;} if (empty($avg_student_progress)) {$avg_student_progress=0;} $row[4] = $avg_student_progress.'%'; $row[5] = $avg_student_score.'%'; $row[6] = Tracking::count_student_assignments($user[0], $course_code, $session_id); $row[7] = Tracking::count_student_messages($user[0], $course_code, $session_id); $row[8] = Tracking::get_first_connection_date_on_the_course($user[0], $course_code, $session_id); $row[9] = Tracking::get_last_connection_date_on_the_course($user[0], $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[0]])) { $row[10]=implode(', ', $additional_user_profile_info[$user[0]]); } else { $row[10]=' '; } } $row[11] = '
'; if ($export_csv) { $row[8] = strip_tags($row[8]); $row[9] = strip_tags($row[9]); unset($row[10]); unset($row[11]); $csv_content[] = $row; } // store columns in array $users $users[] = array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$row[7],$row[8],$row[9],$row[10],$row[11]); } 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 ""; $total = 0; if (is_array($results)) { for($j = 0 ; $j < count($results) ; $j++) { echo ""; echo ""; echo ""; echo""; $total = $total + $results[$j][1]; } echo ""; echo ""; echo ""; echo""; } else { echo ""; echo ""; echo""; } echo "
".get_lang('LoginsTitleMonthColumn')." ".get_lang('LoginsTitleCountColumn')."
".$MonthsLong[date('n', $results[$j][0])-1].' '.date('Y', $results[$j][0])."".$results[$j][1]."
".get_lang('Total')."".$total."
".get_lang('NoResult')."
"; 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 " "; 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 "\n"; echo "\n"; echo "\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 "\n"; echo "\n"; } echo "
".get_lang('ExercicesTitleExerciceColumn')." ".get_lang('Date')." ".get_lang('ExercicesTitleScoreColumn')."
".$results[$i][0]."".$display_date."".$results[$i][1]." / ".$results[$i][2]."
/
".get_lang('NoResult')."
"; 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 ""; 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 ""; echo ""; echo ""; echo""; } } else { echo ""; echo ""; echo""; } echo "
".get_lang('WorkTitle')." ".get_lang('WorkAuthors')." ".get_lang('Date')."
" ."".$results[$j][1]."" ."".$results[$j][2]."".$beautifulDate."
".get_lang('NoResult')."
"; 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 ""; if (is_array($results)) { for($j = 0 ; $j < count($results) ; $j++) { echo ""; echo ""; echo""; } } else { echo ""; echo ""; echo""; } echo "
".get_lang('LinksTitleLinkColumn')."
".$results[$j][0]."
".get_lang('NoResult')."
"; 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 ""; if (is_array($results)) { for($j = 0 ; $j < count($results) ; $j++) { echo ""; echo ""; echo""; } } else { echo ""; echo ""; echo""; } echo "
".get_lang('DocumentsTitleDocumentColumn')."
".$results[$j]."
".get_lang('NoResult')."
"; 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; 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); } } ?>