(Score average fixes) ============================================================================== */ class Tracking { /** * Calculates the time spent on the platform by a user * @param integer $user_id the user id * @return timestamp $nb_seconds */ function get_time_spent_on_the_platform($user_id) { $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN); $sql = 'SELECT login_date, logout_date FROM ' . $tbl_track_login . ' WHERE login_user_id = ' . intval($user_id);; $rs = api_sql_query($sql,__FILE__,__LINE__); $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 the user id * @param string $course_code the course code * @return timestamp $nb_seconds */ function get_time_spent_on_the_course($user_id, $course_code) { // protect datas $user_id = intval($user_id); $course_code = addslashes($course_code); $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS); $sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . ' WHERE user_id = ' . $user_id . ' AND course_code="' . $course_code . '"'; $rs = api_sql_query($sql,__FILE__,__LINE__); $nb_seconds = 0; while ($a_connections = Database::fetch_array($rs)) { $s_login_date = $a_connections["login_course_date"]; $s_logout_date = $a_connections["logout_course_date"]; $i_timestamp_login_date = strtotime($s_login_date); $i_timestamp_logout_date = strtotime($s_logout_date); $nb_seconds += ($i_timestamp_logout_date - $i_timestamp_login_date); } return $nb_seconds; } 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 = api_sql_query($sql,__FILE__,__LINE__); if(Database::num_rows($rs)>0) { if ($first_login_date = Database::result($rs, 0, 0)) { return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($first_login_date)); } } return false; } 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 = api_sql_query($sql,__FILE__,__LINE__); if(Database::num_rows($rs)>0) { if ($last_login_date = Database::result($rs, 0, 0)) { if ($return_timestamp) { return strtotime($last_login_date); } else { if (!$warning_message) { return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)); } else { $timestamp = strtotime($last_login_date); $currentTimestamp = mktime(); //If the last connection is > than 7 days, the text is red //345600 = 7 days in seconds if ($currentTimestamp - $timestamp > 604800) { return '' . format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)) . ''; } else { return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)); } } } } } return false; } function get_first_connection_date_on_the_course($student_id, $course_code) { $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 = ' . intval($student_id) . ' AND course_code = "' . Database::escape_string($course_code) . '" ORDER BY login_course_date ASC LIMIT 0,1'; $rs = api_sql_query($sql,__FILE__,__LINE__); if(Database::num_rows($rs)>0) { if ($first_login_date = Database::result($rs, 0, 0)) { return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($first_login_date)); } } return false; } function get_last_connection_date_on_the_course($student_id, $course_code) { $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 = ' . intval($student_id) . ' AND course_code = "' . Database::escape_string($course_code) . '" ORDER BY login_course_date DESC LIMIT 0,1'; $rs = api_sql_query($sql,__FILE__,__LINE__); if(Database::num_rows($rs)>0) { if ($last_login_date = Database::result($rs, 0, 0)) { $timestamp = strtotime($last_login_date); $currentTimestamp = mktime(); //If the last connection is > than 7 days, the text is red //345600 = 7 days in seconds if ($currentTimestamp - $timestamp > 604800) { return '' . format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)) . ' '; } else { return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)); } } } return false; } function count_course_per_student($user_id) { $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; $rs = api_sql_query($sql, __FILE__, __LINE__); $nb_courses = Database::num_rows($rs); $sql = 'SELECT DISTINCT course_code FROM ' . $tbl_session_course_rel_user . ' WHERE id_user = ' . $user_id; $rs = api_sql_query($sql, __FILE__, __LINE__); $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 $student_id - User id * @param string $course_code - Course id * @return string value (number %) Which represents a round integer about the score average. */ function get_avg_student_exercise_score($student_id, $course_code) { // protect datas $student_id = Database::escape_string($student_id); $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); //get the list of exercises $sql = "SELECT id, title FROM $tbl_course_quiz WHERE active <> -1"; $rs = api_sql_query($sql, __FILE__, __LINE__); $count_exe = Database::num_rows($rs); if ($count_exe > 0) { $quiz_avg_total_score = 0; while($quiz = Database::fetch_array($rs)) { // get the score and max score from track_e_exercise $sql = 'SELECT exe_result , exe_weighting FROM '.$tbl_stats_exercise.' WHERE exe_exo_id = '.(int)$quiz['id'].' AND exe_user_id = '.(int)$student_id.' AND orig_lp_id = 0 AND exe_cours_id = "'.Database::escape_string($course_code).'" AND orig_lp_item_id = 0 ORDER BY exe_date DESC'; $rsAttempt = api_sql_query($sql, __FILE__, __LINE__); $nb_attempts = 0; $quiz_avg_score = 0; while ($attempt = Database::fetch_array($rsAttempt)) { $nb_attempts++; $exe_weight=$attempt['exe_weighting']; if ($exe_weight >0) { $quiz_avg_score += round(($attempt['exe_result']/$exe_weight*100),2); } } if($nb_attempts>0) { $quiz_avg_score = $quiz_avg_score / $nb_attempts; } $quiz_avg_total_score += $quiz_avg_score; } return $quiz_avg_total_score/$count_exe; } } else { return null; } } function get_avg_student_progress($student_id, $course_code) { 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_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW, $a_course['db_name']); $tbl_course_lp_view_item = Database :: get_course_table(TABLE_LP_ITEM_VIEW, $a_course['db_name']); $tbl_course_lp_item = Database :: get_course_table(TABLE_LP_ITEM, $a_course['db_name']); $tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN, $a_course['db_name']); //get the list of learning paths $sql = 'SELECT id FROM ' . $tbl_course_lp; $rs = api_sql_query($sql, __FILE__, __LINE__); $nb_lp = Database::num_rows($rs); $avg_progress = 0; if ($nb_lp > 0) { while ($lp = Database :: fetch_array($rs)) { // get the progress in learning pathes $sqlProgress = "SELECT progress FROM " . $tbl_course_lp_view . " AS lp_view WHERE lp_view.user_id = " . $student_id . " AND lp_view.lp_id = " . $lp['id'] . " "; $resultItem = api_sql_query($sqlProgress, __FILE__, __LINE__); if(Database::num_rows($resultItem)>0) { $avg_progress += Database::result($resultItem, 0, 0); } } $avg_progress = round($avg_progress / $nb_lp, 1); } return $avg_progress; } else { 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 score. * 2. The score average from all Tests (quiz) in all LP in a course-> All the answers / All the max score. * 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 User id * @param Course id * @param Array limit average to listed lp ids * @return string value (number %) Which represents a round integer explain in got in 3. */ function get_avg_student_score($student_id, $course_code, $lp_ids=array()) { $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'])) { $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']); $sql_course_lp = 'SELECT id FROM '.$lp_table; if(count($lp_ids)!=0) { $sql_course_lp.=' WHERE id IN ('.implode(',',$lp_ids).')'; } $sql_result_lp = api_sql_query($sql_course_lp, __FILE__, __LINE__); $lp_scorm_score_total = 0; $lp_scorm_weighting_total = 0; $lp_scorm_result_score_total = 0; $lp_scorm_loop=0; if(Database::num_rows($sql_result_lp)>0){ //Scorm test while($a_learnpath = Database::fetch_array($sql_result_lp)){ //We get the last view id of this LP $sql='SELECT max(id) as id FROM '.$lp_view_table.' WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($student_id).'"'; $rs_last_lp_view_id = api_sql_query($sql, __FILE__, __LINE__); $lp_view_id = Database::result($rs_last_lp_view_id,0,'id'); $sql_max_score='SELECT lp_iv.score as score,lp_i.max_score 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" WHERE lp_view_id="'.$lp_view_id.'"'; //$rs = api_sql_query($sql, __FILE__, __LINE__); //$sql_max_score='SELECT max_score FROM '.$lp_item_view_table.' WHERE lp_view_id="'.$lp_view_id.'" '; $res_max_score=Database::query($sql_max_score,__FILE__,__LINE__); $count_total_loop=0; while ($row_max_score=Database::fetch_array($res_max_score)) { if ($row_max_score['max_score']==0) { $lp_scorm_result_score_total+=($row_max_score['score']/100)*100; } else { $lp_scorm_result_score_total+=($row_max_score['score']/$row_max_score['max_score'])*100; } $count_total_loop++; } if ($count_total_loop==0) { $count_total_loop=1; } $score_of_scorm_calculate=round((($lp_scorm_result_score_total/$count_total_loop)*100),2); } //The next call to a MySQL fetch function, such as mysql_fetch_assoc(), would return that row. mysql_data_seek($sql_result_lp,0); //Quizz in a LP while($a_learnpath = Database::fetch_array($sql_result_lp)){ //we got the maxscore this is wrong /* echo $sql = 'SELECT id as item_id, max_score FROM '.$lp_item_table.' AS lp_item WHERE lp_id='.$a_learnpath['id'].' AND item_type="quiz"'; */ //Path is the exercise id $sql = 'SELECT path, id as item_id, max_score FROM '.$lp_item_table.' AS lp_item WHERE lp_id='.$a_learnpath['id'].' AND item_type="quiz"'; $rsItems = api_sql_query($sql, __FILE__, __LINE__); //We get the last view id of this LP $sql = "SELECT id FROM $lp_view_table WHERE user_id = '".intval($student_id)."' and lp_id='".intval($a_learnpath['id'])."'"; //$sql='SELECT max(id) as id FROM '.$lp_view_table.' WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($student_id).'"'; $rs_last_lp_view_id = api_sql_query($sql, __FILE__, __LINE__); $lp_view_id = intval(Database::result($rs_last_lp_view_id,0,'id')); $total_score = $total_weighting = 0; if ($lp_view_id!=0) { while ($item = Database :: fetch_array($rsItems, 'ASSOC')) { // we take the score from a LP because we have lp_view_id $sql = "SELECT score FROM $lp_item_view_table WHERE lp_item_id = '".(int)$item['item_id']."' and lp_view_id = '".(int)$lp_view_id."' ORDER BY view_count DESC limit 1"; /*$sql = 'SELECT score as student_score FROM '.$lp_item_view_table.' as lp_view_item WHERE lp_view_item.lp_item_id = '.$item['item_id'].' AND lp_view_id = "'.$lp_view_id.'" ';*/ $rsScores = api_sql_query($sql, __FILE__, __LINE__); // Real max score - this was implemented because of the random exercises $sql_last_attempt = 'SELECT exe_id FROM '. $tbl_stats_exercices. ' ' . 'WHERE exe_exo_id="' .$item['path']. '" AND exe_user_id="' . $student_id . '" AND orig_lp_id = "'.$a_learnpath['id'].'" AND orig_lp_item_id = "'.$item['item_id'].'" AND exe_cours_id="' . $course_code . '" ORDER BY exe_date DESC limit 1'; $resultLastAttempt = api_sql_query($sql_last_attempt, __FILE__, __LINE__); $num = Database :: num_rows($resultLastAttempt); if ($num > 0){ if ($num > 1){ while ($rowLA = Database :: fetch_row($resultLastAttempt)) { $id_last_attempt = $rowLA[0]; } } else { $id_last_attempt = Database :: result($resultLastAttempt, 0, 0); } } $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"; $result = api_sql_query($sql, __FILE__, __LINE__); $row_max_score = Database :: fetch_array($result); $maxscore = $row_max_score['maxscore']; if ($maxscore=='') { $maxscore = $item['max_score']; } // not right! /*if(Database::num_rows($rsScores)>0) { $total_score += Database::result($rsScores, 0, 0); //echo $total_weighting += $item['max_score']; $total_weighting += $maxscore; if($total_weighting>0) { //echo ($total_score/$total_weighting)*100; $lp_scorm_score_total += ($total_score/$total_weighting)*100; $lp_scorm_weighting_total+=100; } }*/ if(Database::num_rows($rsScores)>0) { $total_score = Database::result($rsScores, 0, 0); //echo $total_weighting += $item['max_score']; $total_weighting += $maxscore; if($total_weighting>0 && $maxscore>0) { //echo $total_score.' - '.$maxscore; echo '
'; //echo $lp_scorm_score_total += ($total_score/$total_weighting)*100; $lp_scorm_score_total += ($total_score/$maxscore)*100; $lp_scorm_weighting_total+=100; } } } } } } $totalScore = $lp_scorm_score_total; $pourcentageScore = 0; if($lp_scorm_weighting_total>0) { //i.e 10.52 $pourcentageScore = round( (($totalScore * 100) / $lp_scorm_weighting_total),2); return $pourcentageScore; } elseif ($score_of_scorm_calculate>0) { return $score_of_scorm_calculate; } else { return null; } } else { return null; } } /** * gets the list of students followed by coach * @param integer $coach_id the id of the coach * @return Array the list of students */ 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 = 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 . ' 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 id_session, course_code FROM ' . $tbl_session_course . ' session_course INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url ON (session_course.id_session=session_rel_url.session_id) WHERE id_coach=' . $coach_id.' AND access_url_id = '.$access_url_id; } } $result = api_sql_query($sql,__FILE__,__LINE__); 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 WHERE course_code='$course_code' AND id_session='$id_session'"; $rs = api_sql_query($sql,__FILE__,__LINE__); 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_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_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 = api_sql_query($sql,__FILE__,__LINE__); while ($row = Database::fetch_array($result)) { $a_students[$row['id_user']] = $row['id_user']; } return $a_students; } 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 . ' WHERE id_session="' . $id_session . '" AND id_coach=' . $coach_id; $result = api_sql_query($sql,__FILE__,__LINE__); 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 = api_sql_query($sql, __FILE__, __LINE__); 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 = api_sql_query($dsl_session_coach, __FILE__, __LINE__); //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 = api_sql_query($sql,__FILE__,__LINE__); while ($row = Database::fetch_array($result)) { $a_students[$row['id_user']] = $row['id_user']; } } return $a_students; } 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; $result = api_sql_query($sql, __FILE__, __LINE__); 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 = api_sql_query($sql, __FILE__, __LINE__); if (Database::num_rows($result) > 0) { return true; } return false; } 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 . ' WHERE id_coach=' . $coach_id; 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 session_course.course_code FROM ' . $tbl_session_course . ' session_course INNER JOIN '.$tbl_course_rel_access_url.' course_rel_url ON (session_course.course_code = course_rel_url.course_code) WHERE id_coach=' . $coach_id.' AND access_url_id = '.$access_url_id; } } if (!empty ($id_session)) $sql .= ' AND id_session=' . $id_session; $result = api_sql_query($sql, __FILE__, __LINE__); 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 = api_sql_query($sql, __FILE__, __LINE__); while ($row = Database::fetch_array($result)) { $a_courses[$row['course_code']] = $row['course_code']; } return $a_courses; } 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); // 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 = api_sql_query($sql,__FILE__,__LINE__); 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 . ' as session_course ON session.id = session_course.id_session AND session_course.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 session.id, session.name, session.date_start, session.date_end FROM ' . $tbl_session . ' as session INNER JOIN ' . $tbl_session_course . ' as session_course ON session.id = session_course.id_session AND session_course.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; } } $rs = api_sql_query($sql,__FILE__,__LINE__); 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; } 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, id_coach FROM ' . $tbl_session_course . ' WHERE id_session=' . $session_id; $rs = api_sql_query($sql, __FILE__, __LINE__); $a_courses = array (); while ($row = Database::fetch_array($rs)) { $a_courses[$row['course_code']] = $row; } return $a_courses; } function count_student_assignments($student_id, $course_code) { 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_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY, $a_course['db_name']); $sql = 'SELECT 1 FROM ' . $tbl_item_property . ' WHERE insert_user_id=' . $student_id . ' AND tool="work"'; $rs = api_sql_query($sql, __LINE__, __FILE__); return Database::num_rows($rs); } else { return null; } } function count_student_messages($student_id, $course_code) { 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_messages = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']); $sql = 'SELECT 1 FROM ' . $tbl_messages . ' WHERE poster_id=' . $student_id; $rs = api_sql_query($sql, __LINE__, __FILE__); return Database::num_rows($rs); } else { return null; } } /** * This function counts the number of post by course * @param string $course_code - Course ID * @return int the number of post by course * @author Christian Fasanando , * @version enero 2009, dokeos 1.8.6 */ function count_number_of_posts_by_course($course_code) { //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'])) { $tbl_posts = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']); $sql = "SELECT count(*) FROM $tbl_posts"; $result = api_sql_query($sql, __FILE__, __LINE__); $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 - Course ID * @return int the number of threads by course * @author Christian Fasanando , * @version enero 2009, dokeos 1.8.6 */ function count_number_of_threads_by_course($course_code) { //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'])) { $tbl_threads = Database :: get_course_table(TABLE_FORUM_THREAD, $a_course['db_name']); $sql = "SELECT count(*) FROM $tbl_threads"; $result = api_sql_query($sql, __FILE__, __LINE__); $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 - Course ID * @return int the number of forums by course * @author Christian Fasanando , * @version enero 2009, dokeos 1.8.6 */ function count_number_of_forums_by_course($course_code) { //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'])) { $tbl_forums = Database :: get_course_table(TABLE_FORUM, $a_course['db_name']); $sql = "SELECT count(*) FROM $tbl_forums"; $result = api_sql_query($sql, __FILE__, __LINE__); $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 - Course ID * @param int $last_days - last x days * @return int the chat last connections by course in x days * @author Christian Fasanando , * @version enero 2009, dokeos 1.8.6 */ function chat_connections_during_last_x_days_by_course($course_code,$last_days) { //protect data $last_days = intval($last_days); $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'])) { $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."'"; $result = api_sql_query($sql, __FILE__, __LINE__); $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 - Student ID * @param string $course_code - Course ID * @return string the last connection * @author Christian Fasanando , * @version enero 2009, dokeos 1.8.6 */ function chat_last_connection($student_id,$course_code) { 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); $date_time = ''; if (!empty($a_course['db_name'])) { // table definition $tbl_stats_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS, $a_course['db_name']); $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' ORDER BY access_date DESC limit 1"; $rs = api_sql_query($sql, __LINE__, __FILE__); $row = Database::fetch_array($rs); $last_connection = $row['access_date']; if (!empty($last_connection)) { $date_format_long = format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_connection)); $time = explode(' ',$last_connection); $date_time = $date_format_long.' '.$time[1]; } return $date_time; } else { return null; } } function count_student_visited_links($student_id, $course_code) { // protect datas $student_id = intval($student_id); $course_code = addslashes($course_code); // 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 . '"'; $rs = api_sql_query($sql, __LINE__, __FILE__); return Database::num_rows($rs); } function count_student_downloaded_documents($student_id, $course_code) { // protect datas $student_id = intval($student_id); $course_code = addslashes($course_code); // 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 . '"'; $rs = api_sql_query($sql, __LINE__, __FILE__); return Database::num_rows($rs); } function get_course_list_in_session_from_student($user_id, $id_session) { $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 = api_sql_query($sql, __LINE__, __FILE__); $a_courses = array (); while ($row = Database::fetch_array($result)) { $a_courses[$row['course_code']] = $row['course_code']; } return $a_courses; } function get_inactives_students_in_course($course_code, $since, $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); $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() '; $rs = api_sql_query($sql,__FILE__,__LINE__); $inactive_users = array(); while($user = Database::fetch_array($rs)) { $inactive_users[] = $user['user_id']; } return $inactive_users; } function count_login_per_student($student_id, $course_code) { $student_id = intval($student_id); $course_code = addslashes($course_code); $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 . '"'; $rs = api_sql_query($sql, __FILE__, __LINE__); $nb_login = Database::num_rows($rs); return $nb_login; } 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 = api_sql_query($sql, __FILE__, __LINE__); while($user = Database :: fetch_array($rs)) { $a_students[$user['user_id']] = $user['user_id']; } return $a_students; } /** * allow get average of test of scorm and lp * @author isaac flores paz * @param int the user id * @param string the course id */ 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, __FILE__, __LINE__); $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,__FILE__,__LINE__); 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, __FILE__, __LINE__); $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,__FILE__,__LINE__); 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; } } ?>