tracking.lib.php 28 KB

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