tracking.lib.php 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581
  1. <?php // $Id: tracking.lib.php 2007-28-02 15:51:53
  2. /*
  3. ==============================================================================
  4. Dokeos - elearning and course management software
  5. Copyright (c) 2004 Dokeos S.A.
  6. Copyright (c) 2003 Ghent University (UGent)
  7. Copyright (c) 2001 Universite catholique de Louvain (UCL)
  8. Copyright (c) various contributors
  9. For a full list of contributors, see "credits.txt".
  10. The full license can be read in "license.txt".
  11. This program is free software; you can redistribute it and/or
  12. modify it under the terms of the GNU General Public License
  13. as published by the Free Software Foundation; either version 2
  14. of the License, or (at your option) any later version.
  15. See the GNU General Public License for more details.
  16. Contact: Dokeos, 181 rue Royale, B-1000 Brussels, Belgium, info@dokeos.com
  17. ==============================================================================
  18. */
  19. /**
  20. ==============================================================================
  21. * This is the tracking library for Dokeos.
  22. * Include/require it in your code to use its functionality.
  23. *
  24. * @package dokeos.library
  25. ==============================================================================
  26. */
  27. class Tracking {
  28. /**
  29. * Calculates the time spent on the platform by a user
  30. * @param integer $user_id the user id
  31. * @return timestamp $nb_seconds
  32. */
  33. function get_time_spent_on_the_platform ($user_id)
  34. {
  35. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  36. $sql = 'SELECT login_date, logout_date FROM '.$tbl_track_login.'
  37. WHERE login_user_id = '.intval($user_id);
  38. $rs = api_sql_query($sql);
  39. $nb_seconds=0;
  40. while($a_connections=mysql_fetch_array($rs))
  41. {
  42. $s_login_date = $a_connections["login_date"];
  43. $s_logout_date = $a_connections["logout_date"];
  44. $i_timestamp_login_date = strtotime($s_login_date);
  45. $i_timestamp_logout_date = strtotime($s_logout_date);
  46. $nb_seconds += ($i_timestamp_logout_date-$i_timestamp_login_date);
  47. }
  48. return $nb_seconds;
  49. }
  50. /**
  51. * Calculates the time spent on the course
  52. * @param integer $user_id the user id
  53. * @param string $course_code the course code
  54. * @return timestamp $nb_seconds
  55. */
  56. function get_time_spent_on_the_course ($user_id, $course_code)
  57. {
  58. // protect datas
  59. $student_id = intval($student_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 = '.intval($user_id).'
  64. AND course_code="'.$course_code.'"';
  65. $rs = api_sql_query($sql);
  66. $nb_seconds=0;
  67. while($a_connections=mysql_fetch_array($rs))
  68. {
  69. $s_login_date = $a_connections["login_course_date"];
  70. $s_logout_date = $a_connections["logout_course_date"];
  71. $i_timestamp_login_date = strtotime($s_login_date);
  72. $i_timestamp_logout_date = strtotime($s_logout_date);
  73. $nb_seconds += ($i_timestamp_logout_date-$i_timestamp_login_date);
  74. }
  75. return $nb_seconds;
  76. }
  77. function get_last_connection_date($student_id, $warning_message=false)
  78. {
  79. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  80. $sql = 'SELECT login_date FROM '.$tbl_track_login.'
  81. WHERE login_user_id = '.intval($student_id).'
  82. ORDER BY login_date DESC LIMIT 0,1';
  83. $rs = api_sql_query($sql);
  84. if($last_login_date = mysql_result($rs,0,0))
  85. {
  86. if(!$warning_message){
  87. return format_locale_date(get_lang('DateFormatLongWithoutDay'),strtotime($last_login_date));
  88. }
  89. else{
  90. $timestamp=strtotime($last_login_date);
  91. $currentTimestamp=mktime();
  92. //If the last connection is > than 7 days, the text is red
  93. //345600 = 7 days in seconds
  94. if($currentTimestamp-$timestamp>345600){
  95. return '<span style="color: #F00;">'.format_locale_date(get_lang('DateFormatLongWithoutDay'),strtotime($last_login_date)).'</span>';
  96. }
  97. else{
  98. return format_locale_date(get_lang('DateFormatLongWithoutDay'),strtotime($last_login_date));
  99. }
  100. }
  101. }
  102. else
  103. {
  104. return false;
  105. }
  106. }
  107. function get_last_connection_date_on_the_course($student_id, $course_code)
  108. {
  109. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  110. $sql = 'SELECT login_course_date FROM '.$tbl_track_login.'
  111. WHERE user_id = '.intval($student_id).'
  112. AND course_code = "'.mysql_real_escape_string($course_code).'"
  113. ORDER BY login_course_date DESC LIMIT 0,1';
  114. $rs = api_sql_query($sql);
  115. if($last_login_date = mysql_result($rs,0,0))
  116. {
  117. return format_locale_date(get_lang('dateTimeFormatLong'),strtotime($last_login_date));
  118. }
  119. else
  120. {
  121. return false;
  122. }
  123. }
  124. function count_course_per_student ($user_id)
  125. {
  126. $user_id = intval($user_id);
  127. $tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  128. $tbl_session_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  129. $sql = 'SELECT DISTINCT course_code
  130. FROM '.$tbl_course_rel_user.'
  131. WHERE user_id = '.$user_id;
  132. $rs = api_sql_query($sql , __FILE__ , __LINE__);
  133. $nb_courses = mysql_num_rows($rs);
  134. $sql = 'SELECT DISTINCT course_code
  135. FROM '.$tbl_session_course_rel_user.'
  136. WHERE id_user = '.$user_id;
  137. $rs = api_sql_query($sql , __FILE__ , __LINE__);
  138. $nb_courses += mysql_num_rows($rs);
  139. return $nb_courses;
  140. }
  141. function get_avg_student_progress($student_id, $course_code)
  142. {
  143. require_once(api_get_path(LIBRARY_PATH).'course.lib.php');
  144. // protect datas
  145. $student_id = intval($student_id);
  146. $course_code = addslashes($course_code);
  147. // get the informations of the course
  148. $a_course = CourseManager :: get_course_information($course_code);
  149. // table definition
  150. $tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW , $a_course['db_name']);
  151. $tbl_course_lp_view_item = Database :: get_course_table(TABLE_LP_ITEM_VIEW , $a_course['db_name']);
  152. $tbl_course_lp_item = Database :: get_course_table(TABLE_LP_ITEM , $a_course['db_name']);
  153. $tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN , $a_course['db_name']);
  154. //get the list of learning paths
  155. $sql = 'SELECT id FROM '.$tbl_course_lp;
  156. $rs = api_sql_query($sql, __FILE__, __LINE__);
  157. $nb_lp = mysql_num_rows($rs);
  158. $avg_progress = 0;
  159. if($nb_lp>0)
  160. {
  161. while($lp = Database :: fetch_array($rs))
  162. {
  163. // get the progress in learning pathes
  164. $sqlProgress = "SELECT progress
  165. FROM ".$tbl_course_lp_view." AS lp_view
  166. WHERE lp_view.user_id = ".$student_id."
  167. AND lp_view.lp_id = ".$lp['id']."
  168. ";
  169. $resultItem = api_sql_query($sqlProgress, __FILE__, __LINE__);
  170. $avg_progress += mysql_result($resultItem, 0, 0);
  171. }
  172. $avg_progress = round($avg_progress / $nb_lp,1);
  173. }
  174. return $avg_progress;
  175. }
  176. function get_avg_student_score($student_id, $course_code)
  177. {
  178. // protect datas
  179. $student_id = intval($student_id);
  180. $course_code = addslashes($course_code);
  181. $sqlScore = " SELECT exe_result,
  182. exe_weighting
  183. FROM ".Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES)."
  184. WHERE exe_user_id = ".$student_id."
  185. AND exe_cours_id = '".$course_code."'
  186. ";
  187. $resultScore = api_sql_query($sqlScore);
  188. $i = 0;
  189. $score = 0;
  190. while($a_score = mysql_fetch_array($resultScore))
  191. {
  192. $score = $score + $a_score['exe_result'];
  193. $weighting = $weighting + $a_score['exe_weighting'];
  194. $i++;
  195. }
  196. $totalScore = $totalScore + $score;
  197. $totalWeighting = $totalWeighting + $weighting;
  198. $pourcentageScore = round(($score*100)/$weighting);
  199. return $pourcentageScore;
  200. }
  201. /**
  202. * gets the list of students followed by coach
  203. * @param integer $coach_id the id of the coach
  204. * @return Array the list of students
  205. */
  206. function get_student_followed_by_coach ($coach_id)
  207. {
  208. $coach_id = intval($coach_id);
  209. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  210. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  211. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  212. $a_students = array();
  213. //////////////////////////////////////////////////////////////
  214. // At first, courses where $coach_id is coach of the course //
  215. //////////////////////////////////////////////////////////////
  216. $sql = 'SELECT id_session, course_code FROM '.$tbl_session_course.' WHERE id_coach='.$coach_id;
  217. $result=api_sql_query($sql);
  218. while($a_courses=mysql_fetch_array($result))
  219. {
  220. $course_code=$a_courses["course_code"];
  221. $id_session=$a_courses["id_session"];
  222. $sql = "SELECT distinct srcru.id_user
  223. FROM $tbl_session_course_user AS srcru
  224. WHERE course_code='$course_code' AND id_session='$id_session'";
  225. $rs=api_sql_query($sql);
  226. while($row=mysql_fetch_array($rs))
  227. {
  228. $a_students[$row['id_user']]=$row['id_user'];
  229. }
  230. }
  231. //////////////////////////////////////////////////////////////
  232. // Then, courses where $coach_id is coach of the session //
  233. //////////////////////////////////////////////////////////////
  234. $sql = 'SELECT session_course_user.id_user
  235. FROM '.$tbl_session_course_user.' as session_course_user
  236. INNER JOIN '.$tbl_session_course.' as session_course
  237. ON session_course.course_code = session_course_user.course_code
  238. INNER JOIN '.$tbl_session.' as session
  239. ON session.id = session_course.id_session
  240. AND session.id_coach = '.$coach_id;
  241. $result=api_sql_query($sql);
  242. while($row=mysql_fetch_array($result))
  243. {
  244. $a_students[$row['id_user']]=$row['id_user'];
  245. }
  246. return $a_students;
  247. }
  248. function is_allowed_to_coach_student($coach_id, $student_id)
  249. {
  250. $coach_id = intval($coach_id);
  251. $student_id = intval($student_id);
  252. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  253. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  254. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  255. //////////////////////////////////////////////////////////////
  256. // At first, courses where $coach_id is coach of the course //
  257. //////////////////////////////////////////////////////////////
  258. $sql = 'SELECT 1
  259. FROM '.$tbl_session_course_user.' AS session_course_user
  260. INNER JOIN '.$tbl_session_course.' AS session_course
  261. ON session_course.course_code = session_course_user.course_code
  262. AND id_coach='.$coach_id.'
  263. WHERE id_user='.$student_id;
  264. $result=api_sql_query($sql, __FILE__, __LINE__);
  265. if(mysql_num_rows($result) > 0)
  266. {
  267. return true;
  268. }
  269. //////////////////////////////////////////////////////////////
  270. // Then, courses where $coach_id is coach of the session //
  271. //////////////////////////////////////////////////////////////
  272. $sql = 'SELECT session_course_user.id_user
  273. FROM '.$tbl_session_course_user.' as session_course_user
  274. INNER JOIN '.$tbl_session_course.' as session_course
  275. ON session_course.course_code = session_course_user.course_code
  276. INNER JOIN '.$tbl_session.' as session
  277. ON session.id = session_course.id_session
  278. AND session.id_coach = '.$coach_id.'
  279. WHERE id_user = '.$student_id;
  280. $result=api_sql_query($sql, __FILE__, __LINE__);
  281. if(mysql_num_rows($result) > 0)
  282. {
  283. return true;
  284. }
  285. return false;
  286. }
  287. function get_courses_followed_by_coach ($coach_id, $id_session='') {
  288. $coach_id = intval($coach_id);
  289. if(!empty($id_session))
  290. $id_session = intval($id_session);
  291. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  292. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  293. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  294. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  295. //////////////////////////////////////////////////////////////
  296. // At first, courses where $coach_id is coach of the course //
  297. //////////////////////////////////////////////////////////////
  298. $sql = 'SELECT DISTINCT course_code FROM '.$tbl_session_course.' WHERE id_coach='.$coach_id;
  299. if(!empty($id_session))
  300. $sql .= ' AND id_session='.$id_session;
  301. $result=api_sql_query($sql, __FILE__, __LINE__);
  302. while($row = mysql_fetch_array($result))
  303. {
  304. $a_courses[$row['course_code']] = $row['course_code'];
  305. }
  306. //////////////////////////////////////////////////////////////
  307. // Then, courses where $coach_id is coach of the session //
  308. //////////////////////////////////////////////////////////////
  309. $sql = 'SELECT DISTINCT session_course.course_code
  310. FROM '.$tbl_session_course.' as session_course
  311. INNER JOIN '.$tbl_session.' as session
  312. ON session.id = session_course.id_session
  313. AND session.id_coach = '.$coach_id.'
  314. INNER JOIN '.$tbl_course.' as course
  315. ON course.code = session_course.course_code';
  316. if(!empty($id_session))
  317. $sql .= ' WHERE session_course.id_session='.$id_session;
  318. $result=api_sql_query($sql, __FILE__, __LINE__);
  319. while($row=mysql_fetch_array($result))
  320. {
  321. $a_courses[$row['course_code']] = $row['course_code'];
  322. }
  323. return $a_courses;
  324. }
  325. function get_sessions_coached_by_user($coach_id)
  326. {
  327. // table definition
  328. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  329. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  330. // protect datas
  331. $coach_id = intval($coach_id);
  332. // session where we are general coach
  333. $sql = 'SELECT DISTINCT id, name, date_start, date_end
  334. FROM '.$tbl_session.'
  335. WHERE id_coach='.$coach_id;
  336. $rs = api_sql_query($sql);
  337. while($row = mysql_fetch_array($rs))
  338. {
  339. $a_sessions[$row["id"]]=$row;
  340. }
  341. // session where we are coach of a course
  342. $sql = 'SELECT DISTINCT session.id, session.name, session.date_start, session.date_end
  343. FROM '.$tbl_session.' as session
  344. INNER JOIN '.$tbl_session_course.' as session_course
  345. ON session.id = session_course.id_session
  346. AND session_course.id_coach='.$coach_id;
  347. $rs = api_sql_query($sql);
  348. while($row = mysql_fetch_array($rs))
  349. {
  350. $a_sessions[$row["id"]]=$row;
  351. }
  352. foreach($a_sessions as &$session)
  353. {
  354. if($row['date_start'] == '0000-00-00')
  355. {
  356. $session['status'] = get_lang('Active');
  357. }
  358. else
  359. {
  360. $date_start = explode('-',$session['date_start']);
  361. $time_start = mktime(0,0,0,$date_start[1],$date_start[2],$date_start[0]);
  362. $date_end = explode('-',$session['date_end']);
  363. $time_end = mktime(0,0,0,$date_end[1],$date_end[2],$date_end[0]);
  364. if($time_start < time() && time() < $time_end)
  365. {
  366. $session['status'] = get_lang('SessionActive');
  367. }
  368. else if(time() < $time_start)
  369. {
  370. $session['status'] = get_lang('SessionFuture');
  371. }
  372. else if(time() > $time_end)
  373. {
  374. $session['status'] = get_lang('SessionPast');
  375. }
  376. }
  377. }
  378. return $a_sessions;
  379. }
  380. function get_courses_list_from_session($session_id)
  381. {
  382. //protect datas
  383. $session_id = intval($session_id);
  384. // table definition
  385. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  386. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  387. $sql = 'SELECT DISTINCT course_code, id_coach
  388. FROM '.$tbl_session_course.'
  389. WHERE id_session='.$session_id;
  390. $rs = api_sql_query($sql,__FILE__,__LINE__);
  391. $a_courses = array();
  392. while($row = mysql_fetch_array($rs))
  393. {
  394. $a_courses[$row['course_code']] = $row;
  395. }
  396. return $a_courses;
  397. }
  398. function count_student_assignments ($student_id, $course_code)
  399. {
  400. require_once(api_get_path(LIBRARY_PATH).'course.lib.php');
  401. // protect datas
  402. $student_id = intval($student_id);
  403. $course_code = addslashes($course_code);
  404. // get the informations of the course
  405. $a_course = CourseManager :: get_course_information($course_code);
  406. // table definition
  407. $tbl_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY, $a_course['db_name']);
  408. $sql = 'SELECT 1
  409. FROM '.$tbl_item_property.'
  410. WHERE insert_user_id='.$student_id.'
  411. AND tool="work"';
  412. $rs = api_sql_query($sql, __LINE__, __FILE__);
  413. return mysql_num_rows($rs);
  414. }
  415. function count_student_messages ($student_id, $course_code)
  416. {
  417. require_once(api_get_path(LIBRARY_PATH).'course.lib.php');
  418. // protect datas
  419. $student_id = intval($student_id);
  420. $course_code = addslashes($course_code);
  421. // get the informations of the course
  422. $a_course = CourseManager :: get_course_information($course_code);
  423. // table definition
  424. $tbl_messages = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']);
  425. $sql = 'SELECT 1
  426. FROM '.$tbl_messages.'
  427. WHERE poster_id='.$student_id;
  428. $rs = api_sql_query($sql, __LINE__, __FILE__);
  429. return mysql_num_rows($rs);
  430. }
  431. function count_student_visited_links ($student_id, $course_code)
  432. {
  433. // protect datas
  434. $student_id = intval($student_id);
  435. $course_code = addslashes($course_code);
  436. // table definition
  437. $tbl_stats_links = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
  438. $sql = 'SELECT 1
  439. FROM '.$tbl_stats_links.'
  440. WHERE links_user_id='.$student_id.'
  441. AND links_cours_id="'.$course_code.'"';
  442. $rs = api_sql_query($sql, __LINE__, __FILE__);
  443. return mysql_num_rows($rs);
  444. }
  445. function count_student_downloaded_documents ($student_id, $course_code)
  446. {
  447. // protect datas
  448. $student_id = intval($student_id);
  449. $course_code = addslashes($course_code);
  450. // table definition
  451. $tbl_stats_documents = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  452. $sql = 'SELECT 1
  453. FROM '.$tbl_stats_documents.'
  454. WHERE down_user_id='.$student_id.'
  455. AND down_cours_id="'.$course_code.'"';
  456. $rs = api_sql_query($sql, __LINE__, __FILE__);
  457. return mysql_num_rows($rs);
  458. }
  459. }
  460. ?>