123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296 |
- <?php
- // $Id: tracking.lib.php 2007-28-02 15:51:53
- /*
- ==============================================================================
- Dokeos - elearning and course management software
- Copyright (c) 2004-2008 Dokeos SPRL
- Copyright (c) 2003 Ghent University (UGent)
- Copyright (c) 2001 Universite catholique de Louvain (UCL)
- Copyright (c) various contributors
- For a full list of contributors, see "credits.txt".
- The full license can be read in "license.txt".
- This program is free software; you can redistribute it and/or
- modify it under the terms of the GNU General Public License
- as published by the Free Software Foundation; either version 2
- of the License, or (at your option) any later version.
- See the GNU General Public License for more details.
- Contact address: Dokeos, rue du Corbeau, 108, B-1030 Brussels, Belgium
- Mail: info@dokeos.com
- ==============================================================================
- */
- /**
- ==============================================================================
- * This is the tracking library for Dokeos.
- * Include/require it in your code to use its functionality.
- *
- * @package dokeos.library
- * @author Julio Montoya <gugli100@gmail.com> (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 = Database::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 = Database::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 = Database::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 = Database::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 '<span style="color: #F00;">' . format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)) . '</span>';
- }
- 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 = Database::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 = Database::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 '<span style="color: #F00;">' . format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)) . (api_is_allowed_to_edit()?' <a href="'.api_get_path(REL_CODE_PATH).'announcements/announcements.php?action=add&remind_inactive='.$student_id.'" title="'.get_lang('RemindInactiveUser').'"><img align="middle" src="'.api_get_path(WEB_IMG_PATH).'messagebox_warning.gif" /></a>':'').'</span>';
- } 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 = Database::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 = Database::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 - or array for multiples User id (array(0=>1,1=>2))
- * @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
- $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);
- $count_quiz = Database::fetch_row(Database::query("SELECT count(id) FROM $tbl_course_quiz WHERE active <> -1",__FILE__,__LINE__));
- $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_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, __FILE__, __LINE__);
- $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_user AND orig_lp_id = 0 AND exe_cours_id = '$course_code' AND orig_lp_item_id = 0 ORDER BY exe_date DESC",__FILE__,__LINE__));
- 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;
- }
- function get_avg_student_progress($student_id, $course_code) {
- // protect datas
- $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 = Database :: get_course_table(TABLE_LP_MAIN, $a_course['db_name']);
- $count_lp = Database::fetch_row(Database::query("SELECT count(id) FROM $tbl_course_lp",__FILE__,__LINE__));
- $avg_progress = 0;
- if (!empty($count_lp[0]) && !empty($student_id)) {
- $condition_user = "";
- if (is_array($student_id)) {
- $condition_user = " lp_view.user_id IN (".implode(',',$student_id).") AND ";
- } else {
- $condition_user = " lp_view.user_id = '$student_id' AND ";
- }
- $sqlProgress = "SELECT SUM(progress) FROM $tbl_course_lp_view AS lp_view WHERE $condition_user lp_view.lp_id IN (SELECT id FROM $tbl_course_lp)";
- $resultItem = Database::query($sqlProgress, __FILE__, __LINE__);
- $rowItem = Database::fetch_row($resultItem);
- $avg_progress = round($rowItem[0] / $count_lp[0], 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 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']);
-
- $condition_lp = "";
- if(count($lp_ids) > 0) {
- $condition_lp =" WHERE id IN(".implode(',',$lp_ids).") ";
- }
-
- $count_row_lp = Database::fetch_row(Database::query("SELECT count(*) FROM $lp_table $condition_lp",__FILE__,__LINE__));
-
- $lp_scorm_score_total = 0;
- $lp_scorm_weighting_total = 0;
- $lp_scorm_result_score_total = 0;
- $lp_scorm_loop=0;
- $lp_count = 0;
- $progress = 0;
-
- if (!empty($count_row_lp[0]) && !empty($student_id)) {
-
- $condition_user1 = "";
- if (is_array($student_id)) {
- $condition_user1 =" AND user_id IN (".implode(',',$student_id).") ";
- } else {
- $condition_user1 =" AND user_id = '$student_id' ";
- }
-
- $sql = "SELECT max(id) as id, lp_id, user_id FROM $lp_view_table WHERE lp_id IN (SELECT id FROM $lp_table $condition_lp) $condition_user1 GROUP BY lp_id,user_id";
- $rs_last_lp_view_id = Database::query($sql, __FILE__, __LINE__);
-
- $score_of_scorm_calculate = 0;
- if (Database::num_rows($rs_last_lp_view_id) > 0) {
- while ($row_lp_view = Database::fetch_array($rs_last_lp_view_id)) {
-
- $lp_view_id = $row_lp_view['id'];
- // we get the progress
- $sql='SELECT progress FROM '.$lp_view_table.' WHERE id="'.$lp_view_id.'"';
-
- $rs = Database::query($sql, __FILE__, __LINE__);
- $progress = Database::result($rs,0,'progress');
-
- // item's list of an scorm
- $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.'"';
- $res_max_score=Database::query($sql_max_score,__FILE__,__LINE__);
- $count_total_loop=0;
- $num_rows_max_score=Database::num_rows($res_max_score);
-
- if ($num_rows_max_score==1) {
- while ($row_max_score=Database::fetch_array($res_max_score)) {
- if ($row_max_score['max_score']==0) {
- //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.
- $lp_scorm_result_score_total+=($row_max_score['score']/100);
- $current_value = $row_max_score['score']/100;
-
- } else {
- $lp_scorm_result_score_total+=($row_max_score['score']/$row_max_score['max_score']);
- $current_value = $row_max_score['score']/$row_max_score['max_score'];
- }
- $count_total_loop++;
- }
- } elseif ($num_rows_max_score > 1) {
- 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);
- $current_value = $row_max_score['score']/100;
- } else {
- //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.
- $lp_scorm_result_score_total+=($row_max_score['score']/$row_max_score['max_score']);
- $current_value = $row_max_score['score']/$row_max_score['max_score'];
- }
- $count_total_loop++;
- }
- }
-
- if ($num_rows_max_score > 0 && ($progress > 0 || $current_value > 0 )) {
- $lp_count++;
- }
-
- if ($count_total_loop==0) {
- $count_total_loop=1;
- }
- $score_of_scorm_calculate=round((($lp_scorm_result_score_total/$count_total_loop)*100),2);
-
- }
- }
-
- if(count($lp_ids)==0 ) {
- $score_of_scorm_calculate=round((($score_of_scorm_calculate/$count_row_lp[0])),2);
- }
-
- $lp_scorm_score_total = $score_of_scorm_calculate;
-
- $condition_user2 = "";
- if (is_array($student_id)) {
- $condition_user2 =" lp_view.user_id IN (".implode(',',$student_id).") AND ";
- } else {
- $condition_user2 =" lp_view.user_id = '$student_id' AND ";
- }
-
- //Path is the exercise id
- $sql = "SELECT lp_view.id as lp_view_id, lp_view.lp_id as lp_id, lp_item.path, lp_item.id as item_id, lp_item.max_score, lp_view.user_id as user_id
- FROM $lp_view_table lp_view
- INNER JOIN $lp_item_table lp_item ON lp_item.lp_id = lp_view.lp_id AND item_type='".TOOL_QUIZ."'
- WHERE $condition_user2 lp_view.lp_id IN (SELECT id FROM $lp_table $condition_lp)";;
-
- $rsItems = Database::query($sql, __FILE__, __LINE__);
-
- $total_score = $total_weighting = 0;
- if (Database::num_rows($rsItems) > 0) {
- while ($item = Database::fetch_array($rsItems)) {
-
- $lp_view_id = $item['lp_view_id'];
- $lp_item_id = $item['item_id'];
- $lp_id = $item['lp_id'];
- $user_id = $item['user_id'];
-
- // 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 = '$lp_item_id' and lp_view_id = '$lp_view_id'
- ORDER BY view_count DESC limit 1";
- $rsScores = Database::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="' . $user_id . '" AND orig_lp_id = "'.$lp_id.'" AND orig_lp_item_id = "'.$lp_item_id.'" AND exe_cours_id="' . $course_code . '" ORDER BY exe_date DESC limit 1';
- $resultLastAttempt = Database::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 = Database::query($sql, __FILE__, __LINE__);
- $row_max_score = Database :: fetch_array($result);
- $maxscore = $row_max_score['maxscore'];
- if ($maxscore=='') {
- $maxscore = $item['max_score'];
- }
- if(Database::num_rows($rsScores)>0) {
- $total_score = Database::result($rsScores, 0, 0);
- $total_weighting += $maxscore;
- if($total_weighting>0 && $maxscore>0) {
- $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) {
- //echo '<br>'.$score_of_scorm_calculate;
- return $score_of_scorm_calculate;
- } 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_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,__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, $tbl_session_user sru
- WHERE srcru.id_user = sru.id_user AND srcru.id_session = sru.id_session AND srcru.course_code='$course_code' AND srcru.id_session='$id_session'";
- $rs = Database::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_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,__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_user . ' WHERE id_session="' . $id_session . '" AND id_user=' . $coach_id.' AND status=2';
- $result = Database::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 = Database::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 = Database::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 = Database::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;*/
- $sql = 'SELECT 1 FROM ' . $tbl_session_course_user . ' WHERE id_user=' . $coach_id .' AND status=2';
-
- $result = Database::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 = Database::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_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, __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 = Database::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);
- $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,__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_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,__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
- FROM ' . $tbl_session_course . '
- WHERE id_session=' . $session_id;
- $rs = Database::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 = Database::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 = Database::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 <christian.fasanando@dokeos.com>,
- * @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 = Database::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 <christian.fasanando@dokeos.com>,
- * @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 = Database::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 <christian.fasanando@dokeos.com>,
- * @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 = Database::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 <christian.fasanando@dokeos.com>,
- * @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 = Database::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 <christian.fasanando@dokeos.com>,
- * @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 = Database::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 = Database::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 = Database::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 = Database::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);
- $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'.
- $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,__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 = Database::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 = Database::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 <florespaz@bidsoftperu.com>
- * @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;
- }
-
- }
- ?>
|