tracking.lib.php 49 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351
  1. <?php
  2. // $Id: tracking.lib.php 2007-28-02 15:51:53
  3. /*
  4. ==============================================================================
  5. Dokeos - elearning and course management software
  6. Copyright (c) 2004-2008 Dokeos SPRL
  7. Copyright (c) 2003 Ghent University (UGent)
  8. Copyright (c) 2001 Universite catholique de Louvain (UCL)
  9. Copyright (c) various contributors
  10. For a full list of contributors, see "credits.txt".
  11. The full license can be read in "license.txt".
  12. This program is free software; you can redistribute it and/or
  13. modify it under the terms of the GNU General Public License
  14. as published by the Free Software Foundation; either version 2
  15. of the License, or (at your option) any later version.
  16. See the GNU General Public License for more details.
  17. Contact address: Dokeos, rue du Corbeau, 108, B-1030 Brussels, Belgium
  18. Mail: info@dokeos.com
  19. ==============================================================================
  20. */
  21. /**
  22. ==============================================================================
  23. * This is the tracking library for Dokeos.
  24. * Include/require it in your code to use its functionality.
  25. *
  26. * @package dokeos.library
  27. * @author Julio Montoya <gugli100@gmail.com> (Score average fixes)
  28. ==============================================================================
  29. */
  30. class Tracking {
  31. /**
  32. * Calculates the time spent on the platform by a user
  33. * @param integer $user_id the user id
  34. * @return timestamp $nb_seconds
  35. */
  36. function get_time_spent_on_the_platform($user_id) {
  37. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  38. $sql = 'SELECT login_date, logout_date FROM ' . $tbl_track_login . '
  39. WHERE login_user_id = ' . intval($user_id);
  40. $rs = Database::query($sql,__FILE__,__LINE__);
  41. $nb_seconds = 0;
  42. $wrong_logout_dates = false;
  43. while ($a_connections = Database::fetch_array($rs)) {
  44. $s_login_date = $a_connections["login_date"];
  45. $s_logout_date = $a_connections["logout_date"];
  46. $i_timestamp_login_date = strtotime($s_login_date);
  47. $i_timestamp_logout_date = strtotime($s_logout_date);
  48. if($i_timestamp_logout_date>0)
  49. {
  50. $nb_seconds += ($i_timestamp_logout_date - $i_timestamp_login_date);
  51. }
  52. else
  53. { // there are wrong datas in db, then we can't give a wrong time
  54. $wrong_logout_dates = true;
  55. }
  56. }
  57. if($nb_seconds>0 || !$wrong_logout_dates)
  58. {
  59. return $nb_seconds;
  60. }
  61. else
  62. {
  63. return -1; //-1 means we have wrong datas in the db
  64. }
  65. }
  66. /**
  67. * Calculates the time spent on the course
  68. * @param integer $user_id the user id
  69. * @param string $course_code the course code
  70. * @return timestamp $nb_seconds
  71. */
  72. function get_time_spent_on_the_course($user_id, $course_code) {
  73. // protect datas
  74. $user_id = intval($user_id);
  75. $course_code = addslashes($course_code);
  76. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  77. $sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . '
  78. WHERE user_id = ' . $user_id . '
  79. AND course_code="' . $course_code . '"';
  80. $rs = Database::query($sql,__FILE__,__LINE__);
  81. $nb_seconds = 0;
  82. while ($a_connections = Database::fetch_array($rs)) {
  83. $s_login_date = $a_connections["login_course_date"];
  84. $s_logout_date = $a_connections["logout_course_date"];
  85. $i_timestamp_login_date = strtotime($s_login_date);
  86. $i_timestamp_logout_date = strtotime($s_logout_date);
  87. $nb_seconds += ($i_timestamp_logout_date - $i_timestamp_login_date);
  88. }
  89. return $nb_seconds;
  90. }
  91. function get_first_connection_date($student_id) {
  92. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  93. $sql = 'SELECT login_date FROM ' . $tbl_track_login . '
  94. WHERE login_user_id = ' . intval($student_id) . '
  95. ORDER BY login_date ASC LIMIT 0,1';
  96. $rs = Database::query($sql,__FILE__,__LINE__);
  97. if(Database::num_rows($rs)>0)
  98. {
  99. if ($first_login_date = Database::result($rs, 0, 0)) {
  100. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($first_login_date));
  101. }
  102. }
  103. return false;
  104. }
  105. function get_last_connection_date($student_id, $warning_message = false, $return_timestamp = false) {
  106. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  107. $sql = 'SELECT login_date FROM ' . $tbl_track_login . '
  108. WHERE login_user_id = ' . intval($student_id) . '
  109. ORDER BY login_date DESC LIMIT 0,1';
  110. $rs = Database::query($sql,__FILE__,__LINE__);
  111. if(Database::num_rows($rs)>0)
  112. {
  113. if ($last_login_date = Database::result($rs, 0, 0))
  114. {
  115. if ($return_timestamp)
  116. {
  117. return strtotime($last_login_date);
  118. }
  119. else
  120. {
  121. if (!$warning_message)
  122. {
  123. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date));
  124. }
  125. else
  126. {
  127. $timestamp = strtotime($last_login_date);
  128. $currentTimestamp = mktime();
  129. //If the last connection is > than 7 days, the text is red
  130. //345600 = 7 days in seconds
  131. if ($currentTimestamp - $timestamp > 604800)
  132. {
  133. return '<span style="color: #F00;">' . format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)) . '</span>';
  134. }
  135. else
  136. {
  137. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date));
  138. }
  139. }
  140. }
  141. }
  142. }
  143. return false;
  144. }
  145. function get_first_connection_date_on_the_course($student_id, $course_code) {
  146. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  147. $sql = 'SELECT login_course_date FROM ' . $tbl_track_login . '
  148. WHERE user_id = ' . intval($student_id) . '
  149. AND course_code = "' . Database::escape_string($course_code) . '"
  150. ORDER BY login_course_date ASC LIMIT 0,1';
  151. $rs = Database::query($sql,__FILE__,__LINE__);
  152. if(Database::num_rows($rs)>0)
  153. {
  154. if ($first_login_date = Database::result($rs, 0, 0)) {
  155. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($first_login_date));
  156. }
  157. }
  158. return false;
  159. }
  160. function get_last_connection_date_on_the_course($student_id, $course_code) {
  161. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  162. $sql = 'SELECT login_course_date FROM ' . $tbl_track_login . '
  163. WHERE user_id = ' . intval($student_id) . '
  164. AND course_code = "' . Database::escape_string($course_code) . '"
  165. ORDER BY login_course_date DESC LIMIT 0,1';
  166. $rs = Database::query($sql,__FILE__,__LINE__);
  167. if(Database::num_rows($rs)>0)
  168. {
  169. if ($last_login_date = Database::result($rs, 0, 0)) {
  170. $timestamp = strtotime($last_login_date);
  171. $currentTimestamp = mktime();
  172. //If the last connection is > than 7 days, the text is red
  173. //345600 = 7 days in seconds
  174. if ($currentTimestamp - $timestamp > 604800) {
  175. 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>';
  176. } else {
  177. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date));
  178. }
  179. }
  180. }
  181. return false;
  182. }
  183. function count_course_per_student($user_id) {
  184. $user_id = intval($user_id);
  185. $tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  186. $tbl_session_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  187. $sql = 'SELECT DISTINCT course_code
  188. FROM ' . $tbl_course_rel_user . '
  189. WHERE user_id = ' . $user_id;
  190. $rs = Database::query($sql, __FILE__, __LINE__);
  191. $nb_courses = Database::num_rows($rs);
  192. $sql = 'SELECT DISTINCT course_code
  193. FROM ' . $tbl_session_course_rel_user . '
  194. WHERE id_user = ' . $user_id;
  195. $rs = Database::query($sql, __FILE__, __LINE__);
  196. $nb_courses += Database::num_rows($rs);
  197. return $nb_courses;
  198. }
  199. /**
  200. * This function gets the score average from all tests in a course by student
  201. * @param int $student_id - User id
  202. * @param string $course_code - Course id
  203. * @return string value (number %) Which represents a round integer about the score average.
  204. */
  205. function get_avg_student_exercise_score($student_id, $course_code) {
  206. // protect datas
  207. $student_id = Database::escape_string($student_id);
  208. $course_code = Database::escape_string($course_code);
  209. // get the informations of the course
  210. $a_course = CourseManager :: get_course_information($course_code);
  211. if(!empty($a_course['db_name']))
  212. {
  213. // table definition
  214. $tbl_course_quiz = Database::get_course_table(TABLE_QUIZ_TEST,$a_course['db_name']);
  215. $tbl_stats_exercise = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  216. //get the list of exercises
  217. $sql = "SELECT id, title FROM $tbl_course_quiz WHERE active <> -1";
  218. $rs = Database::query($sql, __FILE__, __LINE__);
  219. $count_exe = Database::num_rows($rs);
  220. if ($count_exe > 0) {
  221. $quiz_avg_total_score = 0;
  222. while($quiz = Database::fetch_array($rs)) {
  223. // get the score and max score from track_e_exercise
  224. $sql = 'SELECT exe_result , exe_weighting
  225. FROM '.$tbl_stats_exercise.'
  226. WHERE exe_exo_id = '.(int)$quiz['id'].'
  227. AND exe_user_id = '.(int)$student_id.'
  228. AND orig_lp_id = 0
  229. AND exe_cours_id = "'.Database::escape_string($course_code).'"
  230. AND orig_lp_item_id = 0
  231. ORDER BY exe_date DESC';
  232. $rsAttempt = Database::query($sql, __FILE__, __LINE__);
  233. $nb_attempts = 0;
  234. $quiz_avg_score = 0;
  235. while ($attempt = Database::fetch_array($rsAttempt)) {
  236. $nb_attempts++;
  237. $exe_weight=$attempt['exe_weighting'];
  238. if ($exe_weight >0) {
  239. $quiz_avg_score += round(($attempt['exe_result']/$exe_weight*100),2);
  240. }
  241. }
  242. if($nb_attempts>0) {
  243. $quiz_avg_score = $quiz_avg_score / $nb_attempts;
  244. }
  245. $quiz_avg_total_score += $quiz_avg_score;
  246. }
  247. return $quiz_avg_total_score/$count_exe;
  248. }
  249. }
  250. else
  251. {
  252. return null;
  253. }
  254. }
  255. function get_avg_student_progress($student_id, $course_code) {
  256. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  257. // protect datas
  258. $student_id = intval($student_id);
  259. $course_code = addslashes($course_code);
  260. // get the informations of the course
  261. $a_course = CourseManager :: get_course_information($course_code);
  262. if(!empty($a_course['db_name']))
  263. {
  264. // table definition
  265. $tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW, $a_course['db_name']);
  266. $tbl_course_lp_view_item = Database :: get_course_table(TABLE_LP_ITEM_VIEW, $a_course['db_name']);
  267. $tbl_course_lp_item = Database :: get_course_table(TABLE_LP_ITEM, $a_course['db_name']);
  268. $tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN, $a_course['db_name']);
  269. //get the list of learning paths
  270. $sql = 'SELECT id FROM ' . $tbl_course_lp;
  271. $rs = Database::query($sql, __FILE__, __LINE__);
  272. $nb_lp = Database::num_rows($rs);
  273. $avg_progress = 0;
  274. if ($nb_lp > 0) {
  275. while ($lp = Database :: fetch_array($rs)) {
  276. // get the progress in learning pathes
  277. $sqlProgress = "SELECT progress
  278. FROM " . $tbl_course_lp_view . " AS lp_view
  279. WHERE lp_view.user_id = " . $student_id . "
  280. AND lp_view.lp_id = " . $lp['id'] . "
  281. ";
  282. $resultItem = Database::query($sqlProgress, __FILE__, __LINE__);
  283. if(Database::num_rows($resultItem)>0)
  284. {
  285. $avg_progress += Database::result($resultItem, 0, 0);
  286. }
  287. }
  288. $avg_progress = round($avg_progress / $nb_lp, 1);
  289. }
  290. return $avg_progress;
  291. }
  292. else
  293. {
  294. return null;
  295. }
  296. }
  297. /**
  298. * This function gets:
  299. * 1. The score average from all SCORM Test items in all LP in a course-> All the answers / All the max score.
  300. * 2. The score average from all Tests (quiz) in all LP in a course-> All the answers / All the max score.
  301. * 3. And finally it will return the average between 1. and 2.
  302. * This function does not take the results of a Test out of a LP
  303. *
  304. * @param User id
  305. * @param Course id
  306. * @param Array limit average to listed lp ids
  307. * @return string value (number %) Which represents a round integer explain in got in 3.
  308. */
  309. function get_avg_student_score($student_id, $course_code, $lp_ids=array()) {
  310. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  311. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  312. $table_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  313. $tbl_stats_exercices = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  314. $tbl_stats_attempts= Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  315. $course = CourseManager :: get_course_information($course_code);
  316. if (!empty($course['db_name'])) {
  317. $tbl_quiz_questions= Database :: get_course_table(TABLE_QUIZ_QUESTION,$course['db_name']);
  318. $lp_table = Database :: get_course_table(TABLE_LP_MAIN,$course['db_name']);
  319. $lp_item_table = Database :: get_course_table(TABLE_LP_ITEM,$course['db_name']);
  320. $lp_view_table = Database :: get_course_table(TABLE_LP_VIEW,$course['db_name']);
  321. $lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW,$course['db_name']);
  322. $sql_course_lp = 'SELECT id FROM '.$lp_table;
  323. if(count($lp_ids)!=0) {
  324. $sql_course_lp.=' WHERE id IN ('.implode(',',$lp_ids).')';
  325. }
  326. $sql_result_lp = Database::query($sql_course_lp, __FILE__, __LINE__);
  327. $lp_scorm_score_total = 0;
  328. $lp_scorm_weighting_total = 0;
  329. $lp_scorm_result_score_total = 0;
  330. $lp_scorm_loop=0;
  331. $lp_count = 0;
  332. $progress = 0;
  333. if(Database::num_rows($sql_result_lp)>0){
  334. //Scorm test
  335. while($a_learnpath = Database::fetch_array($sql_result_lp)) {
  336. //We get the last view id of this LP (with the higher id)
  337. $sql='SELECT max(id) as id FROM '.$lp_view_table.'
  338. WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($student_id).'"';
  339. $rs_last_lp_view_id = Database::query($sql, __FILE__, __LINE__);
  340. $lp_view_id = Database::result($rs_last_lp_view_id,0,'id'); // THE view
  341. if ($lp_view_id != '') {
  342. // we get the progress
  343. $sql='SELECT progress FROM '.$lp_view_table.' WHERE id="'.$lp_view_id.'"';
  344. $rs = Database::query($sql, __FILE__, __LINE__);
  345. $progress = Database::result($rs,0,'progress');
  346. // item's list of an scorm
  347. $sql_max_score='SELECT lp_iv.score as score,lp_i.max_score
  348. FROM '.$lp_item_view_table.' as lp_iv
  349. INNER JOIN '.$lp_item_table.' as lp_i
  350. ON lp_i.id = lp_iv.lp_item_id
  351. AND lp_i.item_type="sco"
  352. WHERE lp_view_id="'.$lp_view_id.'"';
  353. //$rs = Database::query($sql, __FILE__, __LINE__);
  354. //$sql_max_score='SELECT max_score FROM '.$lp_item_view_table.' WHERE lp_view_id="'.$lp_view_id.'" ';
  355. $res_max_score=Database::query($sql_max_score,__FILE__,__LINE__);
  356. $count_total_loop=0;
  357. $num_rows_max_score=Database::num_rows($res_max_score);
  358. if ($num_rows_max_score==1) {
  359. while ($row_max_score=Database::fetch_array($res_max_score)) {
  360. echo $row_max_score['score'].' - '.$row_max_score['max_score'];
  361. if ($row_max_score['max_score']==0) {
  362. //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.
  363. $lp_scorm_result_score_total+=($row_max_score['score']/100);
  364. $current_value = $row_max_score['score']/100;
  365. } else {
  366. $lp_scorm_result_score_total+=($row_max_score['score']/$row_max_score['max_score']);
  367. $current_value = $row_max_score['score']/$row_max_score['max_score'];
  368. }
  369. $count_total_loop++;
  370. }
  371. } elseif ($num_rows_max_score > 1) {
  372. //echo ' ---- <br>';
  373. while ($row_max_score=Database::fetch_array($res_max_score)) {
  374. //echo $row_max_score['score'].' - '.$row_max_score['max_score'];
  375. //echo '<br>';
  376. if ($row_max_score['max_score']==0) {
  377. $lp_scorm_result_score_total+=($row_max_score['score']/100);
  378. $current_value = $row_max_score['score']/100;
  379. } else {
  380. //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.
  381. $lp_scorm_result_score_total+=($row_max_score['score']/$row_max_score['max_score']);
  382. $current_value = $row_max_score['score']/$row_max_score['max_score'];
  383. }
  384. $count_total_loop++;
  385. }
  386. }
  387. if ($num_rows_max_score > 0 && ($progress > 0 || $current_value > 0 )) {
  388. $lp_count++;
  389. }
  390. if ($count_total_loop==0) {
  391. $count_total_loop=1;
  392. }
  393. $score_of_scorm_calculate=round((($lp_scorm_result_score_total/$count_total_loop)*100),2);
  394. } else {
  395. $score_of_scorm_calculate = 0;
  396. }
  397. }
  398. //The next call to a MySQL fetch function, such as mysql_fetch_assoc(), would return that row.
  399. mysql_data_seek($sql_result_lp,0);
  400. if ($lp_count==0) {
  401. $lp_count=1;
  402. }
  403. if(count($lp_ids)==0 ) {
  404. $score_of_scorm_calculate=round((($score_of_scorm_calculate/$lp_count)),2);
  405. }
  406. $lp_scorm_score_total = $score_of_scorm_calculate;
  407. //Quizz in a LP
  408. while($a_learnpath = Database::fetch_array($sql_result_lp)) {
  409. //we got the maxscore this is wrong
  410. /*
  411. echo $sql = 'SELECT id as item_id, max_score
  412. FROM '.$lp_item_table.' AS lp_item
  413. WHERE lp_id='.$a_learnpath['id'].'
  414. AND item_type="quiz"';
  415. */
  416. //Path is the exercise id
  417. $sql = 'SELECT path, id as item_id, max_score
  418. FROM '.$lp_item_table.' AS lp_item
  419. WHERE lp_id='.$a_learnpath['id'].'
  420. AND item_type="quiz"';
  421. $rsItems = Database::query($sql, __FILE__, __LINE__);
  422. //We get the last view id of this LP
  423. $sql = "SELECT id FROM $lp_view_table WHERE user_id = '".intval($student_id)."' and lp_id='".intval($a_learnpath['id'])."'";
  424. //$sql='SELECT max(id) as id FROM '.$lp_view_table.' WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($student_id).'"';
  425. $rs_last_lp_view_id = Database::query($sql, __FILE__, __LINE__);
  426. $lp_view_id = intval(Database::result($rs_last_lp_view_id,0,'id'));
  427. $total_score = $total_weighting = 0;
  428. if ($lp_view_id!=0) {
  429. while ($item = Database :: fetch_array($rsItems, 'ASSOC')) {
  430. // we take the score from a LP because we have lp_view_id
  431. $sql = "SELECT score FROM $lp_item_view_table WHERE lp_item_id = '".(int)$item['item_id']."' and lp_view_id = '".(int)$lp_view_id."'
  432. ORDER BY view_count DESC limit 1";
  433. /*$sql = 'SELECT score as student_score
  434. FROM '.$lp_item_view_table.' as lp_view_item
  435. WHERE lp_view_item.lp_item_id = '.$item['item_id'].'
  436. AND lp_view_id = "'.$lp_view_id.'" ';*/
  437. $rsScores = Database::query($sql, __FILE__, __LINE__);
  438. // Real max score - this was implemented because of the random exercises
  439. $sql_last_attempt = 'SELECT exe_id FROM '. $tbl_stats_exercices. ' ' .
  440. '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';
  441. $resultLastAttempt = Database::query($sql_last_attempt, __FILE__, __LINE__);
  442. $num = Database :: num_rows($resultLastAttempt);
  443. if ($num > 0){
  444. if ($num > 1){
  445. while ($rowLA = Database :: fetch_row($resultLastAttempt)) {
  446. $id_last_attempt = $rowLA[0];
  447. }
  448. } else {
  449. $id_last_attempt = Database :: result($resultLastAttempt, 0, 0);
  450. }
  451. }
  452. $sql = "SELECT SUM(t.ponderation) as maxscore from ( SELECT distinct question_id, marks,ponderation FROM $tbl_stats_attempts as at " .
  453. "INNER JOIN $tbl_quiz_questions as q on(q.id = at.question_id) where exe_id ='$id_last_attempt' ) as t";
  454. $result = Database::query($sql, __FILE__, __LINE__);
  455. $row_max_score = Database :: fetch_array($result);
  456. $maxscore = $row_max_score['maxscore'];
  457. if ($maxscore=='') {
  458. $maxscore = $item['max_score'];
  459. }
  460. // not right!
  461. /*if(Database::num_rows($rsScores)>0) {
  462. $total_score += Database::result($rsScores, 0, 0);
  463. //echo $total_weighting += $item['max_score'];
  464. $total_weighting += $maxscore;
  465. if($total_weighting>0) {
  466. //echo ($total_score/$total_weighting)*100;
  467. $lp_scorm_score_total += ($total_score/$total_weighting)*100;
  468. $lp_scorm_weighting_total+=100;
  469. }
  470. }*/
  471. if(Database::num_rows($rsScores)>0) {
  472. $total_score = Database::result($rsScores, 0, 0);
  473. //echo $total_weighting += $item['max_score'];
  474. $total_weighting += $maxscore;
  475. if($total_weighting>0 && $maxscore>0) {
  476. //echo $total_score.' - '.$maxscore; echo '<br>';
  477. //echo $lp_scorm_score_total += ($total_score/$total_weighting)*100;
  478. $lp_scorm_score_total += ($total_score/$maxscore)*100;
  479. $lp_scorm_weighting_total+=100;
  480. }
  481. }
  482. }
  483. }
  484. }
  485. }
  486. $totalScore = $lp_scorm_score_total;
  487. $pourcentageScore = 0;
  488. if($lp_scorm_weighting_total>0) {
  489. //i.e 10.52
  490. $pourcentageScore = round( (($totalScore * 100) / $lp_scorm_weighting_total),2);
  491. return $pourcentageScore;
  492. } elseif ($score_of_scorm_calculate>0) {
  493. //echo '<br>'.$score_of_scorm_calculate;
  494. return $score_of_scorm_calculate;
  495. } else {
  496. return null;
  497. }
  498. } else {
  499. return null;
  500. }
  501. }
  502. /**
  503. * gets the list of students followed by coach
  504. * @param integer $coach_id the id of the coach
  505. * @return Array the list of students
  506. */
  507. function get_student_followed_by_coach($coach_id) {
  508. $coach_id = intval($coach_id);
  509. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  510. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  511. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  512. $a_students = array ();
  513. //////////////////////////////////////////////////////////////
  514. // At first, courses where $coach_id is coach of the course //
  515. //////////////////////////////////////////////////////////////
  516. $sql = 'SELECT id_session, course_code FROM ' . $tbl_session_course . ' WHERE id_coach=' . $coach_id;
  517. global $_configuration;
  518. if ($_configuration['multiple_access_urls']==true) {
  519. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  520. $access_url_id = api_get_current_access_url_id();
  521. if ($access_url_id != -1){
  522. $sql = 'SELECT id_session, course_code
  523. FROM ' . $tbl_session_course . ' session_course INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
  524. ON (session_course.id_session=session_rel_url.session_id)
  525. WHERE id_coach=' . $coach_id.' AND access_url_id = '.$access_url_id;
  526. }
  527. }
  528. $result = Database::query($sql,__FILE__,__LINE__);
  529. while ($a_courses = Database::fetch_array($result)) {
  530. $course_code = $a_courses["course_code"];
  531. $id_session = $a_courses["id_session"];
  532. $sql = "SELECT distinct srcru.id_user
  533. FROM $tbl_session_course_user AS srcru
  534. WHERE course_code='$course_code' AND id_session='$id_session'";
  535. $rs = Database::query($sql,__FILE__,__LINE__);
  536. while ($row = Database::fetch_array($rs)) {
  537. $a_students[$row['id_user']] = $row['id_user'];
  538. }
  539. }
  540. //////////////////////////////////////////////////////////////
  541. // Then, courses where $coach_id is coach of the session //
  542. //////////////////////////////////////////////////////////////
  543. $sql = 'SELECT session_course_user.id_user
  544. FROM ' . $tbl_session_course_user . ' as session_course_user
  545. INNER JOIN ' . $tbl_session_course . ' as session_course
  546. ON session_course.course_code = session_course_user.course_code
  547. AND session_course_user.id_session = session_course.id_session
  548. INNER JOIN ' . $tbl_session . ' as session
  549. ON session.id = session_course.id_session
  550. AND session.id_coach = ' . $coach_id;
  551. if ($_configuration['multiple_access_urls']==true) {
  552. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  553. $access_url_id = api_get_current_access_url_id();
  554. if ($access_url_id != -1){
  555. $sql = 'SELECT session_course_user.id_user
  556. FROM ' . $tbl_session_course_user . ' as session_course_user
  557. INNER JOIN ' . $tbl_session_course . ' as session_course
  558. ON session_course.course_code = session_course_user.course_code
  559. AND session_course_user.id_session = session_course.id_session
  560. INNER JOIN ' . $tbl_session . ' as session
  561. ON session.id = session_course.id_session
  562. AND session.id_coach = ' . $coach_id.'
  563. INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
  564. ON session.id = session_rel_url.session_id WHERE access_url_id = '.$access_url_id;
  565. }
  566. }
  567. $result = Database::query($sql,__FILE__,__LINE__);
  568. while ($row = Database::fetch_array($result)) {
  569. $a_students[$row['id_user']] = $row['id_user'];
  570. }
  571. return $a_students;
  572. }
  573. function get_student_followed_by_coach_in_a_session($id_session, $coach_id) {
  574. $coach_id = intval($coach_id);
  575. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  576. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  577. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  578. $a_students = array ();
  579. //////////////////////////////////////////////////////////////
  580. // At first, courses where $coach_id is coach of the course //
  581. //////////////////////////////////////////////////////////////
  582. $sql = 'SELECT course_code FROM ' . $tbl_session_course . ' WHERE id_session="' . $id_session . '" AND id_coach=' . $coach_id;
  583. $result = Database::query($sql,__FILE__,__LINE__);
  584. while ($a_courses = Database::fetch_array($result)) {
  585. $course_code = $a_courses["course_code"];
  586. $sql = "SELECT distinct srcru.id_user
  587. FROM $tbl_session_course_user AS srcru
  588. WHERE course_code='$course_code' and id_session = '" . $id_session . "'";
  589. $rs = Database::query($sql, __FILE__, __LINE__);
  590. while ($row = Database::fetch_array($rs)) {
  591. $a_students[$row['id_user']] = $row['id_user'];
  592. }
  593. }
  594. //////////////////////////////////////////////////////////////
  595. // Then, courses where $coach_id is coach of the session //
  596. //////////////////////////////////////////////////////////////
  597. $dsl_session_coach = 'SELECT id_coach FROM ' . $tbl_session . ' WHERE id="' . $id_session . '" AND id_coach="' . $coach_id . '"';
  598. $result = Database::query($dsl_session_coach, __FILE__, __LINE__);
  599. //He is the session_coach so we select all the users in the session
  600. if (Database::num_rows($result) > 0) {
  601. $sql = 'SELECT DISTINCT srcru.id_user FROM ' . $tbl_session_course_user . ' AS srcru WHERE id_session="' . $id_session . '"';
  602. $result = Database::query($sql,__FILE__,__LINE__);
  603. while ($row = Database::fetch_array($result)) {
  604. $a_students[$row['id_user']] = $row['id_user'];
  605. }
  606. }
  607. return $a_students;
  608. }
  609. function is_allowed_to_coach_student($coach_id, $student_id) {
  610. $coach_id = intval($coach_id);
  611. $student_id = intval($student_id);
  612. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  613. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  614. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  615. //////////////////////////////////////////////////////////////
  616. // At first, courses where $coach_id is coach of the course //
  617. //////////////////////////////////////////////////////////////
  618. $sql = 'SELECT 1
  619. FROM ' . $tbl_session_course_user . ' AS session_course_user
  620. INNER JOIN ' . $tbl_session_course . ' AS session_course
  621. ON session_course.course_code = session_course_user.course_code
  622. AND id_coach=' . $coach_id . '
  623. WHERE id_user=' . $student_id;
  624. $result = Database::query($sql, __FILE__, __LINE__);
  625. if (Database::num_rows($result) > 0) {
  626. return true;
  627. }
  628. //////////////////////////////////////////////////////////////
  629. // Then, courses where $coach_id is coach of the session //
  630. //////////////////////////////////////////////////////////////
  631. $sql = 'SELECT session_course_user.id_user
  632. FROM ' . $tbl_session_course_user . ' as session_course_user
  633. INNER JOIN ' . $tbl_session_course . ' as session_course
  634. ON session_course.course_code = session_course_user.course_code
  635. INNER JOIN ' . $tbl_session . ' as session
  636. ON session.id = session_course.id_session
  637. AND session.id_coach = ' . $coach_id . '
  638. WHERE id_user = ' . $student_id;
  639. $result = Database::query($sql, __FILE__, __LINE__);
  640. if (Database::num_rows($result) > 0) {
  641. return true;
  642. }
  643. return false;
  644. }
  645. function get_courses_followed_by_coach($coach_id, $id_session = '')
  646. {
  647. $coach_id = intval($coach_id);
  648. if (!empty ($id_session))
  649. $id_session = intval($id_session);
  650. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  651. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  652. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  653. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  654. //////////////////////////////////////////////////////////////
  655. // At first, courses where $coach_id is coach of the course //
  656. //////////////////////////////////////////////////////////////
  657. $sql = 'SELECT DISTINCT course_code FROM ' . $tbl_session_course . ' WHERE id_coach=' . $coach_id;
  658. global $_configuration;
  659. if ($_configuration['multiple_access_urls']==true) {
  660. $tbl_course_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  661. $access_url_id = api_get_current_access_url_id();
  662. if ($access_url_id != -1){
  663. $sql = 'SELECT DISTINCT session_course.course_code FROM ' . $tbl_session_course . ' session_course INNER JOIN '.$tbl_course_rel_access_url.' course_rel_url
  664. ON (session_course.course_code = course_rel_url.course_code)
  665. WHERE id_coach=' . $coach_id.' AND access_url_id = '.$access_url_id;
  666. }
  667. }
  668. if (!empty ($id_session))
  669. $sql .= ' AND id_session=' . $id_session;
  670. $result = Database::query($sql, __FILE__, __LINE__);
  671. while ($row = Database::fetch_array($result)) {
  672. $a_courses[$row['course_code']] = $row['course_code'];
  673. }
  674. //////////////////////////////////////////////////////////////
  675. // Then, courses where $coach_id is coach of the session //
  676. //////////////////////////////////////////////////////////////
  677. $sql = 'SELECT DISTINCT session_course.course_code
  678. FROM ' . $tbl_session_course . ' as session_course
  679. INNER JOIN ' . $tbl_session . ' as session
  680. ON session.id = session_course.id_session
  681. AND session.id_coach = ' . $coach_id . '
  682. INNER JOIN ' . $tbl_course . ' as course
  683. ON course.code = session_course.course_code';
  684. if ($_configuration['multiple_access_urls']==true) {
  685. $tbl_course_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  686. $access_url_id = api_get_current_access_url_id();
  687. if ($access_url_id != -1){
  688. $sql = 'SELECT DISTINCT session_course.course_code
  689. FROM ' . $tbl_session_course . ' as session_course
  690. INNER JOIN ' . $tbl_session . ' as session
  691. ON session.id = session_course.id_session
  692. AND session.id_coach = ' . $coach_id . '
  693. INNER JOIN ' . $tbl_course . ' as course
  694. ON course.code = session_course.course_code
  695. INNER JOIN '.$tbl_course_rel_access_url.' course_rel_url
  696. ON (session_course.course_code = course_rel_url.course_code)';
  697. }
  698. }
  699. if (!empty ($id_session)) {
  700. $sql .= ' WHERE session_course.id_session=' . $id_session;
  701. if ($_configuration['multiple_access_urls']==true)
  702. $sql .= ' AND access_url_id = '.$access_url_id;
  703. } else {
  704. if ($_configuration['multiple_access_urls']==true)
  705. $sql .= ' WHERE access_url_id = '.$access_url_id;
  706. }
  707. $result = Database::query($sql, __FILE__, __LINE__);
  708. while ($row = Database::fetch_array($result)) {
  709. $a_courses[$row['course_code']] = $row['course_code'];
  710. }
  711. return $a_courses;
  712. }
  713. function get_sessions_coached_by_user($coach_id) {
  714. // table definition
  715. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  716. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  717. // protect datas
  718. $coach_id = intval($coach_id);
  719. // session where we are general coach
  720. $sql = 'SELECT DISTINCT id, name, date_start, date_end
  721. FROM ' . $tbl_session . '
  722. WHERE id_coach=' . $coach_id;
  723. global $_configuration;
  724. if ($_configuration['multiple_access_urls']==true) {
  725. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  726. $access_url_id = api_get_current_access_url_id();
  727. if ($access_url_id != -1){
  728. $sql = 'SELECT DISTINCT id, name, date_start, date_end
  729. FROM ' . $tbl_session . ' session INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
  730. ON (session.id = session_rel_url.session_id)
  731. WHERE id_coach=' . $coach_id.' AND access_url_id = '.$access_url_id;
  732. }
  733. }
  734. $rs = Database::query($sql,__FILE__,__LINE__);
  735. while ($row = Database::fetch_array($rs))
  736. {
  737. $a_sessions[$row["id"]] = $row;
  738. }
  739. // session where we are coach of a course
  740. $sql = 'SELECT DISTINCT session.id, session.name, session.date_start, session.date_end
  741. FROM ' . $tbl_session . ' as session
  742. INNER JOIN ' . $tbl_session_course . ' as session_course
  743. ON session.id = session_course.id_session
  744. AND session_course.id_coach=' . $coach_id;
  745. global $_configuration;
  746. if ($_configuration['multiple_access_urls']==true) {
  747. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  748. $access_url_id = api_get_current_access_url_id();
  749. if ($access_url_id != -1){
  750. $sql = 'SELECT DISTINCT session.id, session.name, session.date_start, session.date_end
  751. FROM ' . $tbl_session . ' as session
  752. INNER JOIN ' . $tbl_session_course . ' as session_course
  753. ON session.id = session_course.id_session AND session_course.id_coach=' . $coach_id.'
  754. INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
  755. ON (session.id = session_rel_url.session_id)
  756. WHERE access_url_id = '.$access_url_id;
  757. }
  758. }
  759. $rs = Database::query($sql,__FILE__,__LINE__);
  760. while ($row = Database::fetch_array($rs))
  761. {
  762. $a_sessions[$row["id"]] = $row;
  763. }
  764. if (is_array($a_sessions)) {
  765. foreach ($a_sessions as & $session) {
  766. if ($session['date_start'] == '0000-00-00') {
  767. $session['status'] = get_lang('SessionActive');
  768. }
  769. else {
  770. $date_start = explode('-', $session['date_start']);
  771. $time_start = mktime(0, 0, 0, $date_start[1], $date_start[2], $date_start[0]);
  772. $date_end = explode('-', $session['date_end']);
  773. $time_end = mktime(0, 0, 0, $date_end[1], $date_end[2], $date_end[0]);
  774. if ($time_start < time() && time() < $time_end) {
  775. $session['status'] = get_lang('SessionActive');
  776. }
  777. else{
  778. if (time() < $time_start) {
  779. $session['status'] = get_lang('SessionFuture');
  780. }
  781. else{
  782. if (time() > $time_end) {
  783. $session['status'] = get_lang('SessionPast');
  784. }
  785. }
  786. }
  787. }
  788. }
  789. }
  790. return $a_sessions;
  791. }
  792. function get_courses_list_from_session($session_id) {
  793. //protect datas
  794. $session_id = intval($session_id);
  795. // table definition
  796. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  797. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  798. $sql = 'SELECT DISTINCT course_code, id_coach
  799. FROM ' . $tbl_session_course . '
  800. WHERE id_session=' . $session_id;
  801. $rs = Database::query($sql, __FILE__, __LINE__);
  802. $a_courses = array ();
  803. while ($row = Database::fetch_array($rs)) {
  804. $a_courses[$row['course_code']] = $row;
  805. }
  806. return $a_courses;
  807. }
  808. function count_student_assignments($student_id, $course_code) {
  809. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  810. // protect datas
  811. $student_id = intval($student_id);
  812. $course_code = addslashes($course_code);
  813. // get the informations of the course
  814. $a_course = CourseManager :: get_course_information($course_code);
  815. if(!empty($a_course['db_name']))
  816. {
  817. // table definition
  818. $tbl_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY, $a_course['db_name']);
  819. $sql = 'SELECT 1
  820. FROM ' . $tbl_item_property . '
  821. WHERE insert_user_id=' . $student_id . '
  822. AND tool="work"';
  823. $rs = Database::query($sql, __LINE__, __FILE__);
  824. return Database::num_rows($rs);
  825. }
  826. else
  827. {
  828. return null;
  829. }
  830. }
  831. function count_student_messages($student_id, $course_code) {
  832. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  833. // protect datas
  834. $student_id = intval($student_id);
  835. $course_code = addslashes($course_code);
  836. // get the informations of the course
  837. $a_course = CourseManager :: get_course_information($course_code);
  838. if(!empty($a_course['db_name']))
  839. {
  840. // table definition
  841. $tbl_messages = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']);
  842. $sql = 'SELECT 1
  843. FROM ' . $tbl_messages . '
  844. WHERE poster_id=' . $student_id;
  845. $rs = Database::query($sql, __LINE__, __FILE__);
  846. return Database::num_rows($rs);
  847. }
  848. else
  849. {
  850. return null;
  851. }
  852. }
  853. /**
  854. * This function counts the number of post by course
  855. * @param string $course_code - Course ID
  856. * @return int the number of post by course
  857. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  858. * @version enero 2009, dokeos 1.8.6
  859. */
  860. function count_number_of_posts_by_course($course_code) {
  861. //protect data
  862. $course_code = addslashes($course_code);
  863. // get the informations of the course
  864. $a_course = CourseManager :: get_course_information($course_code);
  865. $count = 0;
  866. if (!empty($a_course['db_name'])) {
  867. $tbl_posts = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']);
  868. $sql = "SELECT count(*) FROM $tbl_posts";
  869. $result = Database::query($sql, __FILE__, __LINE__);
  870. $row = Database::fetch_row($result);
  871. $count = $row[0];
  872. return $count;
  873. } else {
  874. return null;
  875. }
  876. }
  877. /**
  878. * This function counts the number of threads by course
  879. * @param string $course_code - Course ID
  880. * @return int the number of threads by course
  881. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  882. * @version enero 2009, dokeos 1.8.6
  883. */
  884. function count_number_of_threads_by_course($course_code) {
  885. //protect data
  886. $course_code = addslashes($course_code);
  887. // get the informations of the course
  888. $a_course = CourseManager :: get_course_information($course_code);
  889. $count = 0;
  890. if (!empty($a_course['db_name'])) {
  891. $tbl_threads = Database :: get_course_table(TABLE_FORUM_THREAD, $a_course['db_name']);
  892. $sql = "SELECT count(*) FROM $tbl_threads";
  893. $result = Database::query($sql, __FILE__, __LINE__);
  894. $row = Database::fetch_row($result);
  895. $count = $row[0];
  896. return $count;
  897. } else {
  898. return null;
  899. }
  900. }
  901. /**
  902. * This function counts the number of forums by course
  903. * @param string $course_code - Course ID
  904. * @return int the number of forums by course
  905. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  906. * @version enero 2009, dokeos 1.8.6
  907. */
  908. function count_number_of_forums_by_course($course_code) {
  909. //protect data
  910. $course_code = addslashes($course_code);
  911. // get the informations of the course
  912. $a_course = CourseManager :: get_course_information($course_code);
  913. $count = 0;
  914. if (!empty($a_course['db_name'])) {
  915. $tbl_forums = Database :: get_course_table(TABLE_FORUM, $a_course['db_name']);
  916. $sql = "SELECT count(*) FROM $tbl_forums";
  917. $result = Database::query($sql, __FILE__, __LINE__);
  918. $row = Database::fetch_row($result);
  919. $count = $row[0];
  920. return $count;
  921. } else {
  922. return null;
  923. }
  924. }
  925. /**
  926. * This function counts the chat last connections by course in x days
  927. * @param string $course_code - Course ID
  928. * @param int $last_days - last x days
  929. * @return int the chat last connections by course in x days
  930. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  931. * @version enero 2009, dokeos 1.8.6
  932. */
  933. function chat_connections_during_last_x_days_by_course($course_code,$last_days) {
  934. //protect data
  935. $last_days = intval($last_days);
  936. $course_code = addslashes($course_code);
  937. // get the informations of the course
  938. $a_course = CourseManager :: get_course_information($course_code);
  939. $count = 0;
  940. if (!empty($a_course['db_name'])) {
  941. $tbl_stats_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS, $a_course['db_name']);
  942. $sql = "SELECT count(*) FROM $tbl_stats_access WHERE DATE_SUB(NOW(),INTERVAL $last_days DAY) <= access_date
  943. AND access_cours_code = '$course_code' AND access_tool='".TOOL_CHAT."'";
  944. $result = Database::query($sql, __FILE__, __LINE__);
  945. $row = Database::fetch_row($result);
  946. $count = $row[0];
  947. return $count;
  948. } else {
  949. return null;
  950. }
  951. }
  952. /**
  953. * This function gets the last student's connection in chat
  954. * @param int $student_id - Student ID
  955. * @param string $course_code - Course ID
  956. * @return string the last connection
  957. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  958. * @version enero 2009, dokeos 1.8.6
  959. */
  960. function chat_last_connection($student_id,$course_code) {
  961. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  962. //protect datas
  963. $student_id = intval($student_id);
  964. $course_code = addslashes($course_code);
  965. // get the informations of the course
  966. $a_course = CourseManager :: get_course_information($course_code);
  967. $date_time = '';
  968. if (!empty($a_course['db_name'])) {
  969. // table definition
  970. $tbl_stats_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS, $a_course['db_name']);
  971. $sql = "SELECT access_date FROM $tbl_stats_access
  972. WHERE access_tool='".TOOL_CHAT."' AND access_user_id='$student_id' AND access_cours_code = '$course_code' ORDER BY access_date DESC limit 1";
  973. $rs = Database::query($sql, __LINE__, __FILE__);
  974. $row = Database::fetch_array($rs);
  975. $last_connection = $row['access_date'];
  976. if (!empty($last_connection)) {
  977. $date_format_long = format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_connection));
  978. $time = explode(' ',$last_connection);
  979. $date_time = $date_format_long.' '.$time[1];
  980. }
  981. return $date_time;
  982. } else {
  983. return null;
  984. }
  985. }
  986. function count_student_visited_links($student_id, $course_code) {
  987. // protect datas
  988. $student_id = intval($student_id);
  989. $course_code = addslashes($course_code);
  990. // table definition
  991. $tbl_stats_links = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
  992. $sql = 'SELECT 1
  993. FROM ' . $tbl_stats_links . '
  994. WHERE links_user_id=' . $student_id . '
  995. AND links_cours_id="' . $course_code . '"';
  996. $rs = Database::query($sql, __LINE__, __FILE__);
  997. return Database::num_rows($rs);
  998. }
  999. function count_student_downloaded_documents($student_id, $course_code) {
  1000. // protect datas
  1001. $student_id = intval($student_id);
  1002. $course_code = addslashes($course_code);
  1003. // table definition
  1004. $tbl_stats_documents = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  1005. $sql = 'SELECT 1
  1006. FROM ' . $tbl_stats_documents . '
  1007. WHERE down_user_id=' . $student_id . '
  1008. AND down_cours_id="' . $course_code . '"';
  1009. $rs = Database::query($sql, __LINE__, __FILE__);
  1010. return Database::num_rows($rs);
  1011. }
  1012. function get_course_list_in_session_from_student($user_id, $id_session) {
  1013. $user_id = intval($user_id);
  1014. $id_session = intval($id_session);
  1015. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1016. $sql = 'SELECT course_code FROM ' . $tbl_session_course_user . ' WHERE id_user="' . $user_id . '" AND id_session="' . $id_session . '"';
  1017. $result = Database::query($sql, __LINE__, __FILE__);
  1018. $a_courses = array ();
  1019. while ($row = Database::fetch_array($result)) {
  1020. $a_courses[$row['course_code']] = $row['course_code'];
  1021. }
  1022. return $a_courses;
  1023. }
  1024. function get_inactives_students_in_course($course_code, $since, $session_id=0)
  1025. {
  1026. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  1027. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1028. $inner = '';
  1029. if($session_id!=0)
  1030. {
  1031. $inner = ' INNER JOIN '.$tbl_session_course_user.' session_course_user
  1032. ON stats_login.course_code = session_course_user.course_code
  1033. AND session_course_user.id_session = '.intval($session_id).'
  1034. AND session_course_user.id_user = stats_login.user_id ';
  1035. }
  1036. $sql = 'SELECT user_id, MAX(login_course_date) max_date FROM'.$tbl_track_login.' stats_login'.$inner.'
  1037. GROUP BY user_id
  1038. HAVING DATE_SUB( NOW(), INTERVAL '.$since.' DAY) > max_date ';
  1039. //HAVING DATE_ADD(max_date, INTERVAL '.$since.' DAY) < NOW() ';
  1040. $rs = Database::query($sql,__FILE__,__LINE__);
  1041. $inactive_users = array();
  1042. while($user = Database::fetch_array($rs))
  1043. {
  1044. $inactive_users[] = $user['user_id'];
  1045. }
  1046. return $inactive_users;
  1047. }
  1048. function count_login_per_student($student_id, $course_code) {
  1049. $student_id = intval($student_id);
  1050. $course_code = addslashes($course_code);
  1051. $tbl_course_rel_user = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  1052. $sql = 'SELECT '.$student_id.'
  1053. FROM ' . $tbl_course_rel_user . '
  1054. WHERE access_user_id=' . $student_id . '
  1055. AND access_cours_code="' . $course_code . '"';
  1056. $rs = Database::query($sql, __FILE__, __LINE__);
  1057. $nb_login = Database::num_rows($rs);
  1058. return $nb_login;
  1059. }
  1060. function get_student_followed_by_drh($hr_dept_id) {
  1061. $hr_dept_id = intval($hr_dept_id);
  1062. $a_students = array ();
  1063. $tbl_organism = Database :: get_main_table(TABLE_MAIN_ORGANISM);
  1064. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  1065. $sql = 'SELECT DISTINCT user_id FROM '.$tbl_user.' as user
  1066. WHERE hr_dept_id='.$hr_dept_id;
  1067. $rs = Database::query($sql, __FILE__, __LINE__);
  1068. while($user = Database :: fetch_array($rs))
  1069. {
  1070. $a_students[$user['user_id']] = $user['user_id'];
  1071. }
  1072. return $a_students;
  1073. }
  1074. /**
  1075. * allow get average of test of scorm and lp
  1076. * @author isaac flores paz <florespaz@bidsoftperu.com>
  1077. * @param int the user id
  1078. * @param string the course id
  1079. */
  1080. function get_average_test_scorm_and_lp ($user_id,$course_id) {
  1081. //the score inside the Reporting table
  1082. $course_info=api_get_course_info($course_id);
  1083. $lp_table = Database :: get_course_table(TABLE_LP_MAIN,$course_info['dbName']);
  1084. $lp_view_table = Database :: get_course_table(TABLE_LP_VIEW,$course_info['dbName']);
  1085. $lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW,$course_info['dbName']);
  1086. $lp_item_table = Database :: get_course_table(TABLE_LP_ITEM,$course_info['dbName']);
  1087. $sql_type='SELECT id,lp_type FROM '.$lp_table;
  1088. $rs_type=Database::query($sql_type);
  1089. $average_data=0;
  1090. $count_loop=0;
  1091. while ($row_type=Database::fetch_array($rs_type)) {
  1092. if ($row_type['lp_type']==1) {//lp dokeos
  1093. $sql = "SELECT id FROM $lp_view_table WHERE user_id = '".intval($user_id)."' and lp_id='".$row_type['id']."'";
  1094. $rs_last_lp_view_id = Database::query($sql, __FILE__, __LINE__);
  1095. $lp_view_id = intval(Database::result($rs_last_lp_view_id,0,'id'));
  1096. $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
  1097. 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.'"';
  1098. $sum=0;
  1099. $tot=0;
  1100. $rs_list_view1=Database::query($sql_list_view,__FILE__,__LINE__);
  1101. while ($row_list_view=Database::fetch_array($rs_list_view1)) {
  1102. $sum=$sum+$row_list_view['sum_data'];
  1103. $tot++;
  1104. }
  1105. if ($tot==0) {
  1106. $tot=1;
  1107. }
  1108. $average_data1=$sum/$tot;
  1109. $sql_list_view='';
  1110. $rs_last_lp_view_id='';
  1111. } elseif ($row_type['lp_type']==2) {//lp scorm
  1112. $sql = "SELECT id FROM $lp_view_table WHERE user_id = '".intval($user_id)."' and lp_id='".$row_type['id']."'";
  1113. $rs_last_lp_view_id = Database::query($sql, __FILE__, __LINE__);
  1114. $lp_view_id = intval(Database::result($rs_last_lp_view_id,0,'id'));
  1115. $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
  1116. 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.'"';
  1117. $tot=0;
  1118. $sum=0;
  1119. $rs_list_view2=Database::query($sql_list_view,__FILE__,__LINE__);
  1120. while ($row_list_view=Database::fetch_array($rs_list_view2)) {
  1121. $sum=$sum+$row_list_view['sum_data'];
  1122. $tot++;
  1123. }
  1124. if ($tot==0) {
  1125. $tot=1;
  1126. }
  1127. $average_data2=$sum/$tot;
  1128. }
  1129. $average_data_sum=$average_data_sum+$average_data1+$average_data2;
  1130. $average_data2=0;
  1131. $average_data1=0;
  1132. $count_loop++;
  1133. }
  1134. if ((int)$count_loop > 0) {
  1135. $avg_student_score = round(($average_data_sum / $count_loop), 2);
  1136. }
  1137. return $avg_student_score;
  1138. }
  1139. }
  1140. ?>