tracking.lib.php 31 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885
  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).' AND logout_date IS NOT NULL';
  40. $rs = api_sql_query($sql,__FILE__,__LINE__);
  41. $nb_seconds = 0;
  42. while ($a_connections = Database::fetch_array($rs)) {
  43. $s_login_date = $a_connections["login_date"];
  44. $s_logout_date = $a_connections["logout_date"];
  45. $i_timestamp_login_date = strtotime($s_login_date);
  46. $i_timestamp_logout_date = strtotime($s_logout_date);
  47. $nb_seconds += ($i_timestamp_logout_date - $i_timestamp_login_date);
  48. }
  49. return $nb_seconds;
  50. }
  51. /**
  52. * Calculates the time spent on the course
  53. * @param integer $user_id the user id
  54. * @param string $course_code the course code
  55. * @return timestamp $nb_seconds
  56. */
  57. function get_time_spent_on_the_course($user_id, $course_code) {
  58. // protect datas
  59. $user_id = intval($user_id);
  60. $course_code = addslashes($course_code);
  61. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  62. $sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . '
  63. WHERE user_id = ' . $user_id . '
  64. AND course_code="' . $course_code . '"';
  65. $rs = api_sql_query($sql,__FILE__,__LINE__);
  66. $nb_seconds = 0;
  67. while ($a_connections = Database::fetch_array($rs)) {
  68. $s_login_date = $a_connections["login_course_date"];
  69. $s_logout_date = $a_connections["logout_course_date"];
  70. $i_timestamp_login_date = strtotime($s_login_date);
  71. $i_timestamp_logout_date = strtotime($s_logout_date);
  72. $nb_seconds += ($i_timestamp_logout_date - $i_timestamp_login_date);
  73. }
  74. return $nb_seconds;
  75. }
  76. function get_first_connection_date($student_id) {
  77. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  78. $sql = 'SELECT login_date FROM ' . $tbl_track_login . '
  79. WHERE login_user_id = ' . intval($student_id) . '
  80. ORDER BY login_date ASC LIMIT 0,1';
  81. $rs = api_sql_query($sql,__FILE__,__LINE__);
  82. if(Database::num_rows($rs)>0)
  83. {
  84. if ($first_login_date = Database::result($rs, 0, 0)) {
  85. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($first_login_date));
  86. }
  87. }
  88. return false;
  89. }
  90. function get_last_connection_date($student_id, $warning_message = false, $return_timestamp = false) {
  91. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  92. $sql = 'SELECT login_date FROM ' . $tbl_track_login . '
  93. WHERE login_user_id = ' . intval($student_id) . '
  94. ORDER BY login_date DESC LIMIT 0,1';
  95. $rs = api_sql_query($sql,__FILE__,__LINE__);
  96. if(Database::num_rows($rs)>0)
  97. {
  98. if ($last_login_date = Database::result($rs, 0, 0))
  99. {
  100. if ($return_timestamp)
  101. {
  102. return strtotime($last_login_date);
  103. }
  104. else
  105. {
  106. if (!$warning_message)
  107. {
  108. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date));
  109. }
  110. else
  111. {
  112. $timestamp = strtotime($last_login_date);
  113. $currentTimestamp = mktime();
  114. //If the last connection is > than 7 days, the text is red
  115. //345600 = 7 days in seconds
  116. if ($currentTimestamp - $timestamp > 604800)
  117. {
  118. return '<span style="color: #F00;">' . format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)) . '</span>';
  119. }
  120. else
  121. {
  122. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date));
  123. }
  124. }
  125. }
  126. }
  127. }
  128. return false;
  129. }
  130. function get_first_connection_date_on_the_course($student_id, $course_code) {
  131. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  132. $sql = 'SELECT login_course_date FROM ' . $tbl_track_login . '
  133. WHERE user_id = ' . intval($student_id) . '
  134. AND course_code = "' . Database::escape_string($course_code) . '"
  135. ORDER BY login_course_date ASC LIMIT 0,1';
  136. $rs = api_sql_query($sql,__FILE__,__LINE__);
  137. if(Database::num_rows($rs)>0)
  138. {
  139. if ($first_login_date = Database::result($rs, 0, 0)) {
  140. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($first_login_date));
  141. }
  142. }
  143. return false;
  144. }
  145. function get_last_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 DESC LIMIT 0,1';
  151. $rs = api_sql_query($sql,__FILE__,__LINE__);
  152. if(Database::num_rows($rs)>0)
  153. {
  154. if ($last_login_date = Database::result($rs, 0, 0)) {
  155. $timestamp = strtotime($last_login_date);
  156. $currentTimestamp = mktime();
  157. //If the last connection is > than 7 days, the text is red
  158. //345600 = 7 days in seconds
  159. if ($currentTimestamp - $timestamp > 604800) {
  160. return '<span style="color: #F00;">' . format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)) . ' <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>';
  161. } else {
  162. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date));
  163. }
  164. }
  165. }
  166. return false;
  167. }
  168. function count_course_per_student($user_id) {
  169. $user_id = intval($user_id);
  170. $tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  171. $tbl_session_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  172. $sql = 'SELECT DISTINCT course_code
  173. FROM ' . $tbl_course_rel_user . '
  174. WHERE user_id = ' . $user_id;
  175. $rs = api_sql_query($sql, __FILE__, __LINE__);
  176. $nb_courses = Database::num_rows($rs);
  177. $sql = 'SELECT DISTINCT course_code
  178. FROM ' . $tbl_session_course_rel_user . '
  179. WHERE id_user = ' . $user_id;
  180. $rs = api_sql_query($sql, __FILE__, __LINE__);
  181. $nb_courses += Database::num_rows($rs);
  182. return $nb_courses;
  183. }
  184. function get_avg_student_progress($student_id, $course_code) {
  185. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  186. // protect datas
  187. $student_id = intval($student_id);
  188. $course_code = addslashes($course_code);
  189. // get the informations of the course
  190. $a_course = CourseManager :: get_course_information($course_code);
  191. if(!empty($a_course['db_name']))
  192. {
  193. // table definition
  194. $tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW, $a_course['db_name']);
  195. $tbl_course_lp_view_item = Database :: get_course_table(TABLE_LP_ITEM_VIEW, $a_course['db_name']);
  196. $tbl_course_lp_item = Database :: get_course_table(TABLE_LP_ITEM, $a_course['db_name']);
  197. $tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN, $a_course['db_name']);
  198. //get the list of learning paths
  199. $sql = 'SELECT id FROM ' . $tbl_course_lp;
  200. $rs = api_sql_query($sql, __FILE__, __LINE__);
  201. $nb_lp = Database::num_rows($rs);
  202. $avg_progress = 0;
  203. if ($nb_lp > 0) {
  204. while ($lp = Database :: fetch_array($rs)) {
  205. // get the progress in learning pathes
  206. $sqlProgress = "SELECT progress
  207. FROM " . $tbl_course_lp_view . " AS lp_view
  208. WHERE lp_view.user_id = " . $student_id . "
  209. AND lp_view.lp_id = " . $lp['id'] . "
  210. ";
  211. $resultItem = api_sql_query($sqlProgress, __FILE__, __LINE__);
  212. if(Database::num_rows($resultItem)>0)
  213. {
  214. $avg_progress += Database::result($resultItem, 0, 0);
  215. }
  216. }
  217. $avg_progress = round($avg_progress / $nb_lp, 1);
  218. }
  219. return $avg_progress;
  220. }
  221. else
  222. {
  223. return null;
  224. }
  225. }
  226. /**
  227. * This function gets:
  228. * 1. The score average from all SCORM Test items in all LP in a course-> All the answers / All the max score.
  229. * 2. The score average from all Tests (quiz) in all LP in a course-> All the answers / All the max score.
  230. * 3. And finally it will return the average between 1. and 2.
  231. * This function does not take the results of a Test out of a LP
  232. *
  233. * @param User id
  234. * @param Course id
  235. * @return string value (number %) Which represents a round integer explain in got in 3.
  236. */
  237. function get_avg_student_score($student_id, $course_code) {
  238. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  239. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  240. $table_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  241. $tbl_stats_exercices = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  242. $tbl_stats_attempts= Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  243. $course = CourseManager :: get_course_information($course_code);
  244. if(!empty($course['db_name']))
  245. {
  246. $tbl_quiz_questions= Database :: get_course_table(TABLE_QUIZ_QUESTION,$course['db_name']);
  247. $lp_table = Database :: get_course_table(TABLE_LP_MAIN,$course['db_name']);
  248. $lp_item_table = Database :: get_course_table(TABLE_LP_ITEM,$course['db_name']);
  249. $lp_view_table = Database :: get_course_table(TABLE_LP_VIEW,$course['db_name']);
  250. $lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW,$course['db_name']);
  251. $sql_course_lp = 'SELECT id FROM '.$lp_table;
  252. $sql_result_lp = api_sql_query($sql_course_lp, __FILE__, __LINE__);
  253. $lp_scorm_score_total = 0;
  254. $lp_scorm_weighting_total = 0;
  255. if(Database::num_rows($sql_result_lp)>0){
  256. //Scorm test
  257. while($a_learnpath = Database::fetch_array($sql_result_lp)){
  258. $sql = 'SELECT id, max_score
  259. FROM '.$lp_item_table.' AS lp_item
  260. WHERE lp_id='.$a_learnpath['id'].'
  261. AND item_type="sco" LIMIT 1';
  262. $rs_lp_item_id_scorm = api_sql_query($sql, __FILE__, __LINE__);
  263. if(Database::num_rows($rs_lp_item_id_scorm)>0){
  264. $lp_item_id = Database::result($rs_lp_item_id_scorm,0,'id');
  265. $lp_item__max_score = Database::result($rs_lp_item_id_scorm,0,'max_score');
  266. //We get the last view id of this LP
  267. $sql='SELECT max(id) as id FROM '.$lp_view_table.' WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($student_id).'"';
  268. $rs_last_lp_view_id = api_sql_query($sql, __FILE__, __LINE__);
  269. $lp_view_id = Database::result($rs_last_lp_view_id,0,'id');
  270. $sql='SELECT SUM(score)/count(lp_item_id) as score FROM '.$lp_item_view_table.' WHERE lp_view_id="'.$lp_view_id.'" GROUP BY lp_view_id';
  271. $rs_score = api_sql_query($sql, __FILE__, __LINE__);
  272. if(Database::num_rows($rs_score)>0)
  273. {
  274. $lp_scorm_score = Database::result($rs_score,0,'score');
  275. $lp_scorm_score = ($lp_scorm_score / $lp_item__max_score) * 100;
  276. $lp_scorm_score_total+=$lp_scorm_score;
  277. $lp_scorm_weighting_total+=100;
  278. }
  279. }
  280. }
  281. //mysql_data_seek() moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number.
  282. //The next call to a MySQL fetch function, such as mysql_fetch_assoc(), would return that row.
  283. mysql_data_seek($sql_result_lp,0);
  284. //Quizz in a LP
  285. while($a_learnpath = Database::fetch_array($sql_result_lp)){
  286. //we got the maxscore this is wrong
  287. /*
  288. echo $sql = 'SELECT id as item_id, max_score
  289. FROM '.$lp_item_table.' AS lp_item
  290. WHERE lp_id='.$a_learnpath['id'].'
  291. AND item_type="quiz"';
  292. */
  293. //Path is the exercise id
  294. $sql = 'SELECT path, id as item_id, max_score
  295. FROM '.$lp_item_table.' AS lp_item
  296. WHERE lp_id='.$a_learnpath['id'].'
  297. AND item_type="quiz"';
  298. $rsItems = api_sql_query($sql, __FILE__, __LINE__);
  299. //We get the last view id of this LP
  300. $sql='SELECT max(id) as id FROM '.$lp_view_table.' WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($student_id).'"';
  301. $rs_last_lp_view_id = api_sql_query($sql, __FILE__, __LINE__);
  302. $lp_view_id = intval(Database::result($rs_last_lp_view_id,0,'id'));
  303. $total_score = $total_weighting = 0;
  304. if($lp_view_id!=0)
  305. {
  306. while($item = Database :: fetch_array($rsItems, 'ASSOC'))
  307. {
  308. // we take the score from a LP because we have lp_view_id
  309. $sql = 'SELECT score as student_score
  310. FROM '.$lp_item_view_table.' as lp_view_item
  311. WHERE lp_view_item.lp_item_id = '.$item['item_id'].'
  312. AND lp_view_id = "'.$lp_view_id.'"
  313. ';
  314. $rsScores = api_sql_query($sql, __FILE__, __LINE__);
  315. // Real max score - this was implemented because of the random exercises
  316. $sql_last_attempt = 'SELECT exe_id FROM '. $tbl_stats_exercices. ' ' .
  317. 'WHERE exe_exo_id="' .$item['path']. '" AND exe_user_id="' . $student_id . '" AND exe_cours_id="' . $course_code . '" ORDER BY exe_date DESC limit 1';
  318. $resultLastAttempt = api_sql_query($sql_last_attempt, __FILE__, __LINE__);
  319. $num = Database :: num_rows($resultLastAttempt);
  320. if ($num > 0){
  321. if ($num > 1){
  322. while ($rowLA = Database :: fetch_row($resultLastAttempt)) {
  323. $id_last_attempt = $rowLA[0];
  324. }
  325. } else {
  326. $id_last_attempt = Database :: result($resultLastAttempt, 0, 0);
  327. }
  328. }
  329. $sql = "SELECT SUM(t.ponderation) as maxscore from ( SELECT distinct question_id, marks,ponderation FROM $tbl_stats_attempts as at " .
  330. "INNER JOIN $tbl_quiz_questions as q on(q.id = at.question_id) where exe_id ='$id_last_attempt' ) as t";
  331. $result = api_sql_query($sql, __FILE__, __LINE__);
  332. $row_max_score = Database :: fetch_array($result);
  333. $maxscore = $row_max_score['maxscore'];
  334. if ($maxscore=='')
  335. {
  336. $maxscore = $item['max_score'];
  337. }
  338. if(Database::num_rows($rsScores)>0)
  339. {
  340. $total_score += Database::result($rsScores, 0, 0);
  341. //echo $total_weighting += $item['max_score'];
  342. $total_weighting += $maxscore;
  343. if($total_weighting>0)
  344. {
  345. $lp_scorm_score_total += ($total_score/$total_weighting)*100;
  346. $lp_scorm_weighting_total+=100;
  347. }
  348. }
  349. }
  350. }
  351. }
  352. }
  353. $totalScore = $lp_scorm_score_total;
  354. $pourcentageScore = 0;
  355. if($lp_scorm_weighting_total>0)
  356. {
  357. $pourcentageScore = round(($totalScore * 100) / $lp_scorm_weighting_total);
  358. }
  359. return $pourcentageScore;
  360. }
  361. else
  362. {
  363. return null;
  364. }
  365. }
  366. /**
  367. * gets the list of students followed by coach
  368. * @param integer $coach_id the id of the coach
  369. * @return Array the list of students
  370. */
  371. function get_student_followed_by_coach($coach_id) {
  372. $coach_id = intval($coach_id);
  373. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  374. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  375. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  376. $a_students = array ();
  377. //////////////////////////////////////////////////////////////
  378. // At first, courses where $coach_id is coach of the course //
  379. //////////////////////////////////////////////////////////////
  380. $sql = 'SELECT id_session, course_code FROM ' . $tbl_session_course . ' WHERE id_coach=' . $coach_id;
  381. $result = api_sql_query($sql,__FILE__,__LINE__);
  382. while ($a_courses = Database::fetch_array($result)) {
  383. $course_code = $a_courses["course_code"];
  384. $id_session = $a_courses["id_session"];
  385. $sql = "SELECT distinct srcru.id_user
  386. FROM $tbl_session_course_user AS srcru
  387. WHERE course_code='$course_code' AND id_session='$id_session'";
  388. $rs = api_sql_query($sql,__FILE__,__LINE__);
  389. while ($row = Database::fetch_array($rs)) {
  390. $a_students[$row['id_user']] = $row['id_user'];
  391. }
  392. }
  393. //////////////////////////////////////////////////////////////
  394. // Then, courses where $coach_id is coach of the session //
  395. //////////////////////////////////////////////////////////////
  396. $sql = 'SELECT session_course_user.id_user
  397. FROM ' . $tbl_session_course_user . ' as session_course_user
  398. INNER JOIN ' . $tbl_session_course . ' as session_course
  399. ON session_course.course_code = session_course_user.course_code
  400. AND session_course_user.id_session = session_course.id_session
  401. INNER JOIN ' . $tbl_session . ' as session
  402. ON session.id = session_course.id_session
  403. AND session.id_coach = ' . $coach_id;
  404. $result = api_sql_query($sql,__FILE__,__LINE__);
  405. while ($row = Database::fetch_array($result)) {
  406. $a_students[$row['id_user']] = $row['id_user'];
  407. }
  408. return $a_students;
  409. }
  410. function get_student_followed_by_coach_in_a_session($id_session, $coach_id) {
  411. $coach_id = intval($coach_id);
  412. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  413. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  414. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  415. $a_students = array ();
  416. //////////////////////////////////////////////////////////////
  417. // At first, courses where $coach_id is coach of the course //
  418. //////////////////////////////////////////////////////////////
  419. $sql = 'SELECT course_code FROM ' . $tbl_session_course . ' WHERE id_session="' . $id_session . '" AND id_coach=' . $coach_id;
  420. $result = api_sql_query($sql,__FILE__,__LINE__);
  421. while ($a_courses = Database::fetch_array($result)) {
  422. $course_code = $a_courses["course_code"];
  423. $sql = "SELECT distinct srcru.id_user
  424. FROM $tbl_session_course_user AS srcru
  425. WHERE course_code='$course_code' and id_session = '" . $id_session . "'";
  426. $rs = api_sql_query($sql, __FILE__, __LINE__);
  427. while ($row = Database::fetch_array($rs)) {
  428. $a_students[$row['id_user']] = $row['id_user'];
  429. }
  430. }
  431. //////////////////////////////////////////////////////////////
  432. // Then, courses where $coach_id is coach of the session //
  433. //////////////////////////////////////////////////////////////
  434. $dsl_session_coach = 'SELECT id_coach FROM ' . $tbl_session . ' WHERE id="' . $id_session . '" AND id_coach="' . $coach_id . '"';
  435. $result = api_sql_query($dsl_session_coach, __FILE__, __LINE__);
  436. //He is the session_coach so we select all the users in the session
  437. if (Database::num_rows($result) > 0) {
  438. $sql = 'SELECT DISTINCT srcru.id_user FROM ' . $tbl_session_course_user . ' AS srcru WHERE id_session="' . $id_session . '"';
  439. $result = api_sql_query($sql,__FILE__,__LINE__);
  440. while ($row = Database::fetch_array($result)) {
  441. $a_students[$row['id_user']] = $row['id_user'];
  442. }
  443. }
  444. return $a_students;
  445. }
  446. function is_allowed_to_coach_student($coach_id, $student_id) {
  447. $coach_id = intval($coach_id);
  448. $student_id = intval($student_id);
  449. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  450. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  451. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  452. //////////////////////////////////////////////////////////////
  453. // At first, courses where $coach_id is coach of the course //
  454. //////////////////////////////////////////////////////////////
  455. $sql = 'SELECT 1
  456. FROM ' . $tbl_session_course_user . ' AS session_course_user
  457. INNER JOIN ' . $tbl_session_course . ' AS session_course
  458. ON session_course.course_code = session_course_user.course_code
  459. AND id_coach=' . $coach_id . '
  460. WHERE id_user=' . $student_id;
  461. $result = api_sql_query($sql, __FILE__, __LINE__);
  462. if (Database::num_rows($result) > 0) {
  463. return true;
  464. }
  465. //////////////////////////////////////////////////////////////
  466. // Then, courses where $coach_id is coach of the session //
  467. //////////////////////////////////////////////////////////////
  468. $sql = 'SELECT session_course_user.id_user
  469. FROM ' . $tbl_session_course_user . ' as session_course_user
  470. INNER JOIN ' . $tbl_session_course . ' as session_course
  471. ON session_course.course_code = session_course_user.course_code
  472. INNER JOIN ' . $tbl_session . ' as session
  473. ON session.id = session_course.id_session
  474. AND session.id_coach = ' . $coach_id . '
  475. WHERE id_user = ' . $student_id;
  476. $result = api_sql_query($sql, __FILE__, __LINE__);
  477. if (Database::num_rows($result) > 0) {
  478. return true;
  479. }
  480. return false;
  481. }
  482. function get_courses_followed_by_coach($coach_id, $id_session = '')
  483. {
  484. $coach_id = intval($coach_id);
  485. if (!empty ($id_session))
  486. $id_session = intval($id_session);
  487. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  488. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  489. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  490. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  491. //////////////////////////////////////////////////////////////
  492. // At first, courses where $coach_id is coach of the course //
  493. //////////////////////////////////////////////////////////////
  494. $sql = 'SELECT DISTINCT course_code FROM ' . $tbl_session_course . ' WHERE id_coach=' . $coach_id;
  495. if (!empty ($id_session))
  496. $sql .= ' AND id_session=' . $id_session;
  497. $result = api_sql_query($sql, __FILE__, __LINE__);
  498. while ($row = Database::fetch_array($result)) {
  499. $a_courses[$row['course_code']] = $row['course_code'];
  500. }
  501. //////////////////////////////////////////////////////////////
  502. // Then, courses where $coach_id is coach of the session //
  503. //////////////////////////////////////////////////////////////
  504. $sql = 'SELECT DISTINCT session_course.course_code
  505. FROM ' . $tbl_session_course . ' as session_course
  506. INNER JOIN ' . $tbl_session . ' as session
  507. ON session.id = session_course.id_session
  508. AND session.id_coach = ' . $coach_id . '
  509. INNER JOIN ' . $tbl_course . ' as course
  510. ON course.code = session_course.course_code';
  511. if (!empty ($id_session))
  512. $sql .= ' WHERE session_course.id_session=' . $id_session;
  513. $result = api_sql_query($sql, __FILE__, __LINE__);
  514. while ($row = Database::fetch_array($result)) {
  515. $a_courses[$row['course_code']] = $row['course_code'];
  516. }
  517. return $a_courses;
  518. }
  519. function get_sessions_coached_by_user($coach_id) {
  520. // table definition
  521. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  522. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  523. // protect datas
  524. $coach_id = intval($coach_id);
  525. // session where we are general coach
  526. $sql = 'SELECT DISTINCT id, name, date_start, date_end
  527. FROM ' . $tbl_session . '
  528. WHERE id_coach=' . $coach_id;
  529. $rs = api_sql_query($sql,__FILE__,__LINE__);
  530. while ($row = Database::fetch_array($rs))
  531. {
  532. $a_sessions[$row["id"]] = $row;
  533. }
  534. // session where we are coach of a course
  535. $sql = 'SELECT DISTINCT session.id, session.name, session.date_start, session.date_end
  536. FROM ' . $tbl_session . ' as session
  537. INNER JOIN ' . $tbl_session_course . ' as session_course
  538. ON session.id = session_course.id_session
  539. AND session_course.id_coach=' . $coach_id;
  540. $rs = api_sql_query($sql,__FILE__,__LINE__);
  541. while ($row = Database::fetch_array($rs))
  542. {
  543. $a_sessions[$row["id"]] = $row;
  544. }
  545. foreach ($a_sessions as & $session)
  546. {
  547. if ($session['date_start'] == '0000-00-00') {
  548. $session['status'] = get_lang('SessionActive');
  549. }
  550. else {
  551. $date_start = explode('-', $session['date_start']);
  552. $time_start = mktime(0, 0, 0, $date_start[1], $date_start[2], $date_start[0]);
  553. $date_end = explode('-', $session['date_end']);
  554. $time_end = mktime(0, 0, 0, $date_end[1], $date_end[2], $date_end[0]);
  555. if ($time_start < time() && time() < $time_end) {
  556. $session['status'] = get_lang('SessionActive');
  557. }
  558. else{
  559. if (time() < $time_start) {
  560. $session['status'] = get_lang('SessionFuture');
  561. }
  562. else{
  563. if (time() > $time_end) {
  564. $session['status'] = get_lang('SessionPast');
  565. }
  566. }
  567. }
  568. }
  569. }
  570. return $a_sessions;
  571. }
  572. function get_courses_list_from_session($session_id) {
  573. //protect datas
  574. $session_id = intval($session_id);
  575. // table definition
  576. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  577. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  578. $sql = 'SELECT DISTINCT course_code, id_coach
  579. FROM ' . $tbl_session_course . '
  580. WHERE id_session=' . $session_id;
  581. $rs = api_sql_query($sql, __FILE__, __LINE__);
  582. $a_courses = array ();
  583. while ($row = Database::fetch_array($rs)) {
  584. $a_courses[$row['course_code']] = $row;
  585. }
  586. return $a_courses;
  587. }
  588. function count_student_assignments($student_id, $course_code) {
  589. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  590. // protect datas
  591. $student_id = intval($student_id);
  592. $course_code = addslashes($course_code);
  593. // get the informations of the course
  594. $a_course = CourseManager :: get_course_information($course_code);
  595. if(!empty($a_course['db_name']))
  596. {
  597. // table definition
  598. $tbl_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY, $a_course['db_name']);
  599. $sql = 'SELECT 1
  600. FROM ' . $tbl_item_property . '
  601. WHERE insert_user_id=' . $student_id . '
  602. AND tool="work"';
  603. $rs = api_sql_query($sql, __LINE__, __FILE__);
  604. return Database::num_rows($rs);
  605. }
  606. else
  607. {
  608. return null;
  609. }
  610. }
  611. function count_student_messages($student_id, $course_code) {
  612. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  613. // protect datas
  614. $student_id = intval($student_id);
  615. $course_code = addslashes($course_code);
  616. // get the informations of the course
  617. $a_course = CourseManager :: get_course_information($course_code);
  618. if(!empty($a_course['db_name']))
  619. {
  620. // table definition
  621. $tbl_messages = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']);
  622. $sql = 'SELECT 1
  623. FROM ' . $tbl_messages . '
  624. WHERE poster_id=' . $student_id;
  625. $rs = api_sql_query($sql, __LINE__, __FILE__);
  626. return Database::num_rows($rs);
  627. }
  628. else
  629. {
  630. return null;
  631. }
  632. }
  633. function count_student_visited_links($student_id, $course_code) {
  634. // protect datas
  635. $student_id = intval($student_id);
  636. $course_code = addslashes($course_code);
  637. // table definition
  638. $tbl_stats_links = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
  639. $sql = 'SELECT 1
  640. FROM ' . $tbl_stats_links . '
  641. WHERE links_user_id=' . $student_id . '
  642. AND links_cours_id="' . $course_code . '"';
  643. $rs = api_sql_query($sql, __LINE__, __FILE__);
  644. return Database::num_rows($rs);
  645. }
  646. function count_student_downloaded_documents($student_id, $course_code) {
  647. // protect datas
  648. $student_id = intval($student_id);
  649. $course_code = addslashes($course_code);
  650. // table definition
  651. $tbl_stats_documents = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  652. $sql = 'SELECT 1
  653. FROM ' . $tbl_stats_documents . '
  654. WHERE down_user_id=' . $student_id . '
  655. AND down_cours_id="' . $course_code . '"';
  656. $rs = api_sql_query($sql, __LINE__, __FILE__);
  657. return Database::num_rows($rs);
  658. }
  659. function get_course_list_in_session_from_student($user_id, $id_session) {
  660. $user_id = intval($user_id);
  661. $id_session = intval($id_session);
  662. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  663. $sql = 'SELECT course_code FROM ' . $tbl_session_course_user . ' WHERE id_user="' . $user_id . '" AND id_session="' . $id_session . '"';
  664. $result = api_sql_query($sql, __LINE__, __FILE__);
  665. $a_courses = array ();
  666. while ($row = Database::fetch_array($result)) {
  667. $a_courses[$row['course_code']] = $row['course_code'];
  668. }
  669. return $a_courses;
  670. }
  671. function get_inactives_students_in_course($course_code, $since, $session_id=0)
  672. {
  673. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  674. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  675. $inner = '';
  676. if($session_id!=0)
  677. {
  678. $inner = ' INNER JOIN '.$tbl_session_course_user.' session_course_user
  679. ON stats_login.course_code = session_course_user.course_code
  680. AND session_course_user.id_session = '.intval($session_id).'
  681. AND session_course_user.id_user = stats_login.user_id ';
  682. }
  683. $sql = 'SELECT user_id, MAX(login_course_date) max_date FROM'.$tbl_track_login.' stats_login'.$inner.'
  684. GROUP BY user_id
  685. HAVING DATE_SUB( NOW(), INTERVAL '.$since.' DAY) > max_date ';
  686. //HAVING DATE_ADD(max_date, INTERVAL '.$since.' DAY) < NOW() ';
  687. $rs = api_sql_query($sql,__FILE__,__LINE__);
  688. $inactive_users = array();
  689. while($user = Database::fetch_array($rs))
  690. {
  691. $inactive_users[] = $user['user_id'];
  692. }
  693. return $inactive_users;
  694. }
  695. function count_login_per_student($student_id, $course_code) {
  696. $student_id = intval($student_id);
  697. $course_code = addslashes($course_code);
  698. $tbl_course_rel_user = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  699. $sql = 'SELECT '.$student_id.'
  700. FROM ' . $tbl_course_rel_user . '
  701. WHERE access_user_id=' . $student_id . '
  702. AND access_cours_code="' . $course_code . '"';
  703. $rs = api_sql_query($sql, __FILE__, __LINE__);
  704. $nb_login = Database::num_rows($rs);
  705. return $nb_login;
  706. }
  707. function get_student_followed_by_drh($hr_dept_id) {
  708. $hr_dept_id = intval($hr_dept_id);
  709. $a_students = array ();
  710. $tbl_organism = Database :: get_main_table(TABLE_MAIN_ORGANISM);
  711. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  712. $sql = 'SELECT DISTINCT user_id FROM '.$tbl_user.' as user
  713. WHERE hr_dept_id='.$hr_dept_id;
  714. $rs = api_sql_query($sql, __FILE__, __LINE__);
  715. while($user = Database :: fetch_array($rs))
  716. {
  717. $a_students[$user['user_id']] = $user['user_id'];
  718. }
  719. return $a_students;
  720. }
  721. }
  722. ?>