tracking.lib.php 27 KB

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