tracking.lib.php 36 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043
  1. <?php
  2. // $Id: tracking.lib.php 2007-28-02 15:51:53
  3. /*
  4. ==============================================================================
  5. Dokeos - elearning and course management software
  6. Copyright (c) 2004-2008 Dokeos SPRL
  7. Copyright (c) 2003 Ghent University (UGent)
  8. Copyright (c) 2001 Universite catholique de Louvain (UCL)
  9. Copyright (c) various contributors
  10. For a full list of contributors, see "credits.txt".
  11. The full license can be read in "license.txt".
  12. This program is free software; you can redistribute it and/or
  13. modify it under the terms of the GNU General Public License
  14. as published by the Free Software Foundation; either version 2
  15. of the License, or (at your option) any later version.
  16. See the GNU General Public License for more details.
  17. Contact address: Dokeos, rue du Corbeau, 108, B-1030 Brussels, Belgium
  18. Mail: info@dokeos.com
  19. ==============================================================================
  20. */
  21. /**
  22. ==============================================================================
  23. * This is the tracking library for Dokeos.
  24. * Include/require it in your code to use its functionality.
  25. *
  26. * @package dokeos.library
  27. * @author Julio Montoya <gugli100@gmail.com> (Score average fixes)
  28. ==============================================================================
  29. */
  30. class Tracking {
  31. /**
  32. * Calculates the time spent on the platform by a user
  33. * @param integer $user_id the user id
  34. * @return timestamp $nb_seconds
  35. */
  36. function get_time_spent_on_the_platform($user_id) {
  37. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  38. $sql = 'SELECT login_date, logout_date FROM ' . $tbl_track_login . '
  39. WHERE login_user_id = ' . intval($user_id);;
  40. $rs = api_sql_query($sql,__FILE__,__LINE__);
  41. $nb_seconds = 0;
  42. $wrong_logout_dates = false;
  43. while ($a_connections = Database::fetch_array($rs)) {
  44. $s_login_date = $a_connections["login_date"];
  45. $s_logout_date = $a_connections["logout_date"];
  46. $i_timestamp_login_date = strtotime($s_login_date);
  47. $i_timestamp_logout_date = strtotime($s_logout_date);
  48. if($i_timestamp_logout_date>0)
  49. {
  50. $nb_seconds += ($i_timestamp_logout_date - $i_timestamp_login_date);
  51. }
  52. else
  53. { // there are wrong datas in db, then we can't give a wrong time
  54. $wrong_logout_dates = true;
  55. }
  56. }
  57. if($nb_seconds>0 || !$wrong_logout_dates)
  58. {
  59. return $nb_seconds;
  60. }
  61. else
  62. {
  63. return -1; //-1 means we have wrong datas in the db
  64. }
  65. }
  66. /**
  67. * Calculates the time spent on the course
  68. * @param integer $user_id the user id
  69. * @param string $course_code the course code
  70. * @return timestamp $nb_seconds
  71. */
  72. function get_time_spent_on_the_course($user_id, $course_code) {
  73. // protect datas
  74. $user_id = intval($user_id);
  75. $course_code = addslashes($course_code);
  76. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  77. $sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . '
  78. WHERE user_id = ' . $user_id . '
  79. AND course_code="' . $course_code . '"';
  80. $rs = api_sql_query($sql,__FILE__,__LINE__);
  81. $nb_seconds = 0;
  82. while ($a_connections = Database::fetch_array($rs)) {
  83. $s_login_date = $a_connections["login_course_date"];
  84. $s_logout_date = $a_connections["logout_course_date"];
  85. $i_timestamp_login_date = strtotime($s_login_date);
  86. $i_timestamp_logout_date = strtotime($s_logout_date);
  87. $nb_seconds += ($i_timestamp_logout_date - $i_timestamp_login_date);
  88. }
  89. return $nb_seconds;
  90. }
  91. function get_first_connection_date($student_id) {
  92. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  93. $sql = 'SELECT login_date FROM ' . $tbl_track_login . '
  94. WHERE login_user_id = ' . intval($student_id) . '
  95. ORDER BY login_date ASC LIMIT 0,1';
  96. $rs = api_sql_query($sql,__FILE__,__LINE__);
  97. if(Database::num_rows($rs)>0)
  98. {
  99. if ($first_login_date = Database::result($rs, 0, 0)) {
  100. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($first_login_date));
  101. }
  102. }
  103. return false;
  104. }
  105. function get_last_connection_date($student_id, $warning_message = false, $return_timestamp = false) {
  106. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  107. $sql = 'SELECT login_date FROM ' . $tbl_track_login . '
  108. WHERE login_user_id = ' . intval($student_id) . '
  109. ORDER BY login_date DESC LIMIT 0,1';
  110. $rs = api_sql_query($sql,__FILE__,__LINE__);
  111. if(Database::num_rows($rs)>0)
  112. {
  113. if ($last_login_date = Database::result($rs, 0, 0))
  114. {
  115. if ($return_timestamp)
  116. {
  117. return strtotime($last_login_date);
  118. }
  119. else
  120. {
  121. if (!$warning_message)
  122. {
  123. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date));
  124. }
  125. else
  126. {
  127. $timestamp = strtotime($last_login_date);
  128. $currentTimestamp = mktime();
  129. //If the last connection is > than 7 days, the text is red
  130. //345600 = 7 days in seconds
  131. if ($currentTimestamp - $timestamp > 604800)
  132. {
  133. return '<span style="color: #F00;">' . format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)) . '</span>';
  134. }
  135. else
  136. {
  137. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date));
  138. }
  139. }
  140. }
  141. }
  142. }
  143. return false;
  144. }
  145. function get_first_connection_date_on_the_course($student_id, $course_code) {
  146. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  147. $sql = 'SELECT login_course_date FROM ' . $tbl_track_login . '
  148. WHERE user_id = ' . intval($student_id) . '
  149. AND course_code = "' . Database::escape_string($course_code) . '"
  150. ORDER BY login_course_date ASC LIMIT 0,1';
  151. $rs = api_sql_query($sql,__FILE__,__LINE__);
  152. if(Database::num_rows($rs)>0)
  153. {
  154. if ($first_login_date = Database::result($rs, 0, 0)) {
  155. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($first_login_date));
  156. }
  157. }
  158. return false;
  159. }
  160. function get_last_connection_date_on_the_course($student_id, $course_code) {
  161. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  162. $sql = 'SELECT login_course_date FROM ' . $tbl_track_login . '
  163. WHERE user_id = ' . intval($student_id) . '
  164. AND course_code = "' . Database::escape_string($course_code) . '"
  165. ORDER BY login_course_date DESC LIMIT 0,1';
  166. $rs = api_sql_query($sql,__FILE__,__LINE__);
  167. if(Database::num_rows($rs)>0)
  168. {
  169. if ($last_login_date = Database::result($rs, 0, 0)) {
  170. $timestamp = strtotime($last_login_date);
  171. $currentTimestamp = mktime();
  172. //If the last connection is > than 7 days, the text is red
  173. //345600 = 7 days in seconds
  174. if ($currentTimestamp - $timestamp > 604800) {
  175. return '<span style="color: #F00;">' . format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)) . ' <a href="'.api_get_path(REL_CODE_PATH).'announcements/announcements.php?action=add&remind_inactive='.$student_id.'" title="'.get_lang('RemindInactiveUser').'"><img align="middle" src="'.api_get_path(WEB_IMG_PATH).'messagebox_warning.gif" /></a></span>';
  176. } else {
  177. return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date));
  178. }
  179. }
  180. }
  181. return false;
  182. }
  183. function count_course_per_student($user_id) {
  184. $user_id = intval($user_id);
  185. $tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  186. $tbl_session_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  187. $sql = 'SELECT DISTINCT course_code
  188. FROM ' . $tbl_course_rel_user . '
  189. WHERE user_id = ' . $user_id;
  190. $rs = api_sql_query($sql, __FILE__, __LINE__);
  191. $nb_courses = Database::num_rows($rs);
  192. $sql = 'SELECT DISTINCT course_code
  193. FROM ' . $tbl_session_course_rel_user . '
  194. WHERE id_user = ' . $user_id;
  195. $rs = api_sql_query($sql, __FILE__, __LINE__);
  196. $nb_courses += Database::num_rows($rs);
  197. return $nb_courses;
  198. }
  199. function get_avg_student_progress($student_id, $course_code) {
  200. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  201. // protect datas
  202. $student_id = intval($student_id);
  203. $course_code = addslashes($course_code);
  204. // get the informations of the course
  205. $a_course = CourseManager :: get_course_information($course_code);
  206. if(!empty($a_course['db_name']))
  207. {
  208. // table definition
  209. $tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW, $a_course['db_name']);
  210. $tbl_course_lp_view_item = Database :: get_course_table(TABLE_LP_ITEM_VIEW, $a_course['db_name']);
  211. $tbl_course_lp_item = Database :: get_course_table(TABLE_LP_ITEM, $a_course['db_name']);
  212. $tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN, $a_course['db_name']);
  213. //get the list of learning paths
  214. $sql = 'SELECT id FROM ' . $tbl_course_lp;
  215. $rs = api_sql_query($sql, __FILE__, __LINE__);
  216. $nb_lp = Database::num_rows($rs);
  217. $avg_progress = 0;
  218. if ($nb_lp > 0) {
  219. while ($lp = Database :: fetch_array($rs)) {
  220. // get the progress in learning pathes
  221. $sqlProgress = "SELECT progress
  222. FROM " . $tbl_course_lp_view . " AS lp_view
  223. WHERE lp_view.user_id = " . $student_id . "
  224. AND lp_view.lp_id = " . $lp['id'] . "
  225. ";
  226. $resultItem = api_sql_query($sqlProgress, __FILE__, __LINE__);
  227. if(Database::num_rows($resultItem)>0)
  228. {
  229. $avg_progress += Database::result($resultItem, 0, 0);
  230. }
  231. }
  232. $avg_progress = round($avg_progress / $nb_lp, 1);
  233. }
  234. return $avg_progress;
  235. }
  236. else
  237. {
  238. return null;
  239. }
  240. }
  241. /**
  242. * This function gets:
  243. * 1. The score average from all SCORM Test items in all LP in a course-> All the answers / All the max score.
  244. * 2. The score average from all Tests (quiz) in all LP in a course-> All the answers / All the max score.
  245. * 3. And finally it will return the average between 1. and 2.
  246. * This function does not take the results of a Test out of a LP
  247. *
  248. * @param User id
  249. * @param Course id
  250. * @param Array limit average to listed lp ids
  251. * @return string value (number %) Which represents a round integer explain in got in 3.
  252. */
  253. function get_avg_student_score($student_id, $course_code, $lp_ids=array()) {
  254. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  255. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  256. $table_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  257. $tbl_stats_exercices = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  258. $tbl_stats_attempts= Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  259. $course = CourseManager :: get_course_information($course_code);
  260. if(!empty($course['db_name']))
  261. {
  262. $tbl_quiz_questions= Database :: get_course_table(TABLE_QUIZ_QUESTION,$course['db_name']);
  263. $lp_table = Database :: get_course_table(TABLE_LP_MAIN,$course['db_name']);
  264. $lp_item_table = Database :: get_course_table(TABLE_LP_ITEM,$course['db_name']);
  265. $lp_view_table = Database :: get_course_table(TABLE_LP_VIEW,$course['db_name']);
  266. $lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW,$course['db_name']);
  267. $sql_course_lp = 'SELECT id FROM '.$lp_table;
  268. if(count($lp_ids)!=0)
  269. {
  270. $sql_course_lp.=' WHERE id IN ('.implode(',',$lp_ids).')';
  271. }
  272. $sql_result_lp = api_sql_query($sql_course_lp, __FILE__, __LINE__);
  273. $lp_scorm_score_total = 0;
  274. $lp_scorm_weighting_total = 0;
  275. if(Database::num_rows($sql_result_lp)>0){
  276. //Scorm test
  277. while($a_learnpath = Database::fetch_array($sql_result_lp)){
  278. //We get the last view id of this LP
  279. $sql='SELECT max(id) as id FROM '.$lp_view_table.' WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($student_id).'"';
  280. $rs_last_lp_view_id = api_sql_query($sql, __FILE__, __LINE__);
  281. $lp_view_id = Database::result($rs_last_lp_view_id,0,'id');
  282. $sql='SELECT SUM(lp_iv.score)/count(lp_item_id) as score, SUM(lp_iv.max_score)/count(lp_item_id) as max_score
  283. FROM '.$lp_item_view_table.' as lp_iv
  284. INNER JOIN '.$lp_item_table.' as lp_i
  285. ON lp_i.id = lp_iv.lp_item_id
  286. AND lp_i.item_type="sco"
  287. WHERE lp_iv.max_score != ""
  288. AND lp_view_id="'.$lp_view_id.'"';
  289. $rs = api_sql_query($sql, __FILE__, __LINE__);
  290. $lp_scorm_score_total+=Database::result($rs, 0, 'score');
  291. $lp_scorm_weighting_total+=Database::result($rs, 0, 'max_score');
  292. }
  293. //The next call to a MySQL fetch function, such as mysql_fetch_assoc(), would return that row.
  294. mysql_data_seek($sql_result_lp,0);
  295. //Quizz in a LP
  296. while($a_learnpath = Database::fetch_array($sql_result_lp)){
  297. //we got the maxscore this is wrong
  298. /*
  299. echo $sql = 'SELECT id as item_id, max_score
  300. FROM '.$lp_item_table.' AS lp_item
  301. WHERE lp_id='.$a_learnpath['id'].'
  302. AND item_type="quiz"';
  303. */
  304. //Path is the exercise id
  305. $sql = 'SELECT path, id as item_id, max_score
  306. FROM '.$lp_item_table.' AS lp_item
  307. WHERE lp_id='.$a_learnpath['id'].'
  308. AND item_type="quiz"';
  309. $rsItems = api_sql_query($sql, __FILE__, __LINE__);
  310. //We get the last view id of this LP
  311. $sql='SELECT max(id) as id FROM '.$lp_view_table.' WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($student_id).'"';
  312. $rs_last_lp_view_id = api_sql_query($sql, __FILE__, __LINE__);
  313. $lp_view_id = intval(Database::result($rs_last_lp_view_id,0,'id'));
  314. $total_score = $total_weighting = 0;
  315. if($lp_view_id!=0)
  316. {
  317. while($item = Database :: fetch_array($rsItems, 'ASSOC'))
  318. {
  319. // we take the score from a LP because we have lp_view_id
  320. $sql = 'SELECT score as student_score
  321. FROM '.$lp_item_view_table.' as lp_view_item
  322. WHERE lp_view_item.lp_item_id = '.$item['item_id'].'
  323. AND lp_view_id = "'.$lp_view_id.'"
  324. ';
  325. $rsScores = api_sql_query($sql, __FILE__, __LINE__);
  326. // Real max score - this was implemented because of the random exercises
  327. $sql_last_attempt = 'SELECT exe_id FROM '. $tbl_stats_exercices. ' ' .
  328. 'WHERE exe_exo_id="' .$item['path']. '" AND exe_user_id="' . $student_id . '" AND orig_lp_id = "'.$a_learnpath['id'].'" AND orig_lp_item_id = "'.$item['item_id'].'" AND exe_cours_id="' . $course_code . '" ORDER BY exe_date DESC limit 1';
  329. $resultLastAttempt = api_sql_query($sql_last_attempt, __FILE__, __LINE__);
  330. $num = Database :: num_rows($resultLastAttempt);
  331. if ($num > 0){
  332. if ($num > 1){
  333. while ($rowLA = Database :: fetch_row($resultLastAttempt)) {
  334. $id_last_attempt = $rowLA[0];
  335. }
  336. } else {
  337. $id_last_attempt = Database :: result($resultLastAttempt, 0, 0);
  338. }
  339. }
  340. $sql = "SELECT SUM(t.ponderation) as maxscore from ( SELECT distinct question_id, marks,ponderation FROM $tbl_stats_attempts as at " .
  341. "INNER JOIN $tbl_quiz_questions as q on(q.id = at.question_id) where exe_id ='$id_last_attempt' ) as t";
  342. $result = api_sql_query($sql, __FILE__, __LINE__);
  343. $row_max_score = Database :: fetch_array($result);
  344. $maxscore = $row_max_score['maxscore'];
  345. if ($maxscore=='')
  346. {
  347. $maxscore = $item['max_score'];
  348. }
  349. if(Database::num_rows($rsScores)>0)
  350. {
  351. $total_score += Database::result($rsScores, 0, 0);
  352. //echo $total_weighting += $item['max_score'];
  353. $total_weighting += $maxscore;
  354. if($total_weighting>0)
  355. {
  356. $lp_scorm_score_total += ($total_score/$total_weighting)*100;
  357. $lp_scorm_weighting_total+=100;
  358. }
  359. }
  360. }
  361. }
  362. }
  363. }
  364. $totalScore = $lp_scorm_score_total;
  365. $pourcentageScore = 0;
  366. if($lp_scorm_weighting_total>0)
  367. {
  368. $pourcentageScore = round(($totalScore * 100) / $lp_scorm_weighting_total);
  369. return $pourcentageScore;
  370. }
  371. else
  372. {
  373. return null;
  374. }
  375. }
  376. else
  377. {
  378. return null;
  379. }
  380. }
  381. /**
  382. * gets the list of students followed by coach
  383. * @param integer $coach_id the id of the coach
  384. * @return Array the list of students
  385. */
  386. function get_student_followed_by_coach($coach_id) {
  387. $coach_id = intval($coach_id);
  388. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  389. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  390. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  391. $a_students = array ();
  392. //////////////////////////////////////////////////////////////
  393. // At first, courses where $coach_id is coach of the course //
  394. //////////////////////////////////////////////////////////////
  395. $sql = 'SELECT id_session, course_code FROM ' . $tbl_session_course . ' WHERE id_coach=' . $coach_id;
  396. $result = api_sql_query($sql,__FILE__,__LINE__);
  397. while ($a_courses = Database::fetch_array($result)) {
  398. $course_code = $a_courses["course_code"];
  399. $id_session = $a_courses["id_session"];
  400. $sql = "SELECT distinct srcru.id_user
  401. FROM $tbl_session_course_user AS srcru
  402. WHERE course_code='$course_code' AND id_session='$id_session'";
  403. $rs = api_sql_query($sql,__FILE__,__LINE__);
  404. while ($row = Database::fetch_array($rs)) {
  405. $a_students[$row['id_user']] = $row['id_user'];
  406. }
  407. }
  408. //////////////////////////////////////////////////////////////
  409. // Then, courses where $coach_id is coach of the session //
  410. //////////////////////////////////////////////////////////////
  411. $sql = 'SELECT session_course_user.id_user
  412. FROM ' . $tbl_session_course_user . ' as session_course_user
  413. INNER JOIN ' . $tbl_session_course . ' as session_course
  414. ON session_course.course_code = session_course_user.course_code
  415. AND session_course_user.id_session = session_course.id_session
  416. INNER JOIN ' . $tbl_session . ' as session
  417. ON session.id = session_course.id_session
  418. AND session.id_coach = ' . $coach_id;
  419. $result = api_sql_query($sql,__FILE__,__LINE__);
  420. while ($row = Database::fetch_array($result)) {
  421. $a_students[$row['id_user']] = $row['id_user'];
  422. }
  423. return $a_students;
  424. }
  425. function get_student_followed_by_coach_in_a_session($id_session, $coach_id) {
  426. $coach_id = intval($coach_id);
  427. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  428. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  429. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  430. $a_students = array ();
  431. //////////////////////////////////////////////////////////////
  432. // At first, courses where $coach_id is coach of the course //
  433. //////////////////////////////////////////////////////////////
  434. $sql = 'SELECT course_code FROM ' . $tbl_session_course . ' WHERE id_session="' . $id_session . '" AND id_coach=' . $coach_id;
  435. $result = api_sql_query($sql,__FILE__,__LINE__);
  436. while ($a_courses = Database::fetch_array($result)) {
  437. $course_code = $a_courses["course_code"];
  438. $sql = "SELECT distinct srcru.id_user
  439. FROM $tbl_session_course_user AS srcru
  440. WHERE course_code='$course_code' and id_session = '" . $id_session . "'";
  441. $rs = api_sql_query($sql, __FILE__, __LINE__);
  442. while ($row = Database::fetch_array($rs)) {
  443. $a_students[$row['id_user']] = $row['id_user'];
  444. }
  445. }
  446. //////////////////////////////////////////////////////////////
  447. // Then, courses where $coach_id is coach of the session //
  448. //////////////////////////////////////////////////////////////
  449. $dsl_session_coach = 'SELECT id_coach FROM ' . $tbl_session . ' WHERE id="' . $id_session . '" AND id_coach="' . $coach_id . '"';
  450. $result = api_sql_query($dsl_session_coach, __FILE__, __LINE__);
  451. //He is the session_coach so we select all the users in the session
  452. if (Database::num_rows($result) > 0) {
  453. $sql = 'SELECT DISTINCT srcru.id_user FROM ' . $tbl_session_course_user . ' AS srcru WHERE id_session="' . $id_session . '"';
  454. $result = api_sql_query($sql,__FILE__,__LINE__);
  455. while ($row = Database::fetch_array($result)) {
  456. $a_students[$row['id_user']] = $row['id_user'];
  457. }
  458. }
  459. return $a_students;
  460. }
  461. function is_allowed_to_coach_student($coach_id, $student_id) {
  462. $coach_id = intval($coach_id);
  463. $student_id = intval($student_id);
  464. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  465. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  466. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  467. //////////////////////////////////////////////////////////////
  468. // At first, courses where $coach_id is coach of the course //
  469. //////////////////////////////////////////////////////////////
  470. $sql = 'SELECT 1
  471. FROM ' . $tbl_session_course_user . ' AS session_course_user
  472. INNER JOIN ' . $tbl_session_course . ' AS session_course
  473. ON session_course.course_code = session_course_user.course_code
  474. AND 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. //////////////////////////////////////////////////////////////
  481. // Then, courses where $coach_id is coach of the session //
  482. //////////////////////////////////////////////////////////////
  483. $sql = 'SELECT session_course_user.id_user
  484. FROM ' . $tbl_session_course_user . ' as session_course_user
  485. INNER JOIN ' . $tbl_session_course . ' as session_course
  486. ON session_course.course_code = session_course_user.course_code
  487. INNER JOIN ' . $tbl_session . ' as session
  488. ON session.id = session_course.id_session
  489. AND session.id_coach = ' . $coach_id . '
  490. WHERE id_user = ' . $student_id;
  491. $result = api_sql_query($sql, __FILE__, __LINE__);
  492. if (Database::num_rows($result) > 0) {
  493. return true;
  494. }
  495. return false;
  496. }
  497. function get_courses_followed_by_coach($coach_id, $id_session = '')
  498. {
  499. $coach_id = intval($coach_id);
  500. if (!empty ($id_session))
  501. $id_session = intval($id_session);
  502. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  503. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  504. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  505. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  506. //////////////////////////////////////////////////////////////
  507. // At first, courses where $coach_id is coach of the course //
  508. //////////////////////////////////////////////////////////////
  509. $sql = 'SELECT DISTINCT course_code FROM ' . $tbl_session_course . ' WHERE id_coach=' . $coach_id;
  510. if (!empty ($id_session))
  511. $sql .= ' AND id_session=' . $id_session;
  512. $result = api_sql_query($sql, __FILE__, __LINE__);
  513. while ($row = Database::fetch_array($result)) {
  514. $a_courses[$row['course_code']] = $row['course_code'];
  515. }
  516. //////////////////////////////////////////////////////////////
  517. // Then, courses where $coach_id is coach of the session //
  518. //////////////////////////////////////////////////////////////
  519. $sql = 'SELECT DISTINCT session_course.course_code
  520. FROM ' . $tbl_session_course . ' as session_course
  521. INNER JOIN ' . $tbl_session . ' as session
  522. ON session.id = session_course.id_session
  523. AND session.id_coach = ' . $coach_id . '
  524. INNER JOIN ' . $tbl_course . ' as course
  525. ON course.code = session_course.course_code';
  526. if (!empty ($id_session))
  527. $sql .= ' WHERE session_course.id_session=' . $id_session;
  528. $result = api_sql_query($sql, __FILE__, __LINE__);
  529. while ($row = Database::fetch_array($result)) {
  530. $a_courses[$row['course_code']] = $row['course_code'];
  531. }
  532. return $a_courses;
  533. }
  534. function get_sessions_coached_by_user($coach_id) {
  535. // table definition
  536. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  537. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  538. // protect datas
  539. $coach_id = intval($coach_id);
  540. // session where we are general coach
  541. $sql = 'SELECT DISTINCT id, name, date_start, date_end
  542. FROM ' . $tbl_session . '
  543. WHERE id_coach=' . $coach_id;
  544. $rs = api_sql_query($sql,__FILE__,__LINE__);
  545. while ($row = Database::fetch_array($rs))
  546. {
  547. $a_sessions[$row["id"]] = $row;
  548. }
  549. // session where we are coach of a course
  550. $sql = 'SELECT DISTINCT session.id, session.name, session.date_start, session.date_end
  551. FROM ' . $tbl_session . ' as session
  552. INNER JOIN ' . $tbl_session_course . ' as session_course
  553. ON session.id = session_course.id_session
  554. AND session_course.id_coach=' . $coach_id;
  555. $rs = api_sql_query($sql,__FILE__,__LINE__);
  556. while ($row = Database::fetch_array($rs))
  557. {
  558. $a_sessions[$row["id"]] = $row;
  559. }
  560. foreach ($a_sessions as & $session)
  561. {
  562. if ($session['date_start'] == '0000-00-00') {
  563. $session['status'] = get_lang('SessionActive');
  564. }
  565. else {
  566. $date_start = explode('-', $session['date_start']);
  567. $time_start = mktime(0, 0, 0, $date_start[1], $date_start[2], $date_start[0]);
  568. $date_end = explode('-', $session['date_end']);
  569. $time_end = mktime(0, 0, 0, $date_end[1], $date_end[2], $date_end[0]);
  570. if ($time_start < time() && time() < $time_end) {
  571. $session['status'] = get_lang('SessionActive');
  572. }
  573. else{
  574. if (time() < $time_start) {
  575. $session['status'] = get_lang('SessionFuture');
  576. }
  577. else{
  578. if (time() > $time_end) {
  579. $session['status'] = get_lang('SessionPast');
  580. }
  581. }
  582. }
  583. }
  584. }
  585. return $a_sessions;
  586. }
  587. function get_courses_list_from_session($session_id) {
  588. //protect datas
  589. $session_id = intval($session_id);
  590. // table definition
  591. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  592. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  593. $sql = 'SELECT DISTINCT course_code, id_coach
  594. FROM ' . $tbl_session_course . '
  595. WHERE id_session=' . $session_id;
  596. $rs = api_sql_query($sql, __FILE__, __LINE__);
  597. $a_courses = array ();
  598. while ($row = Database::fetch_array($rs)) {
  599. $a_courses[$row['course_code']] = $row;
  600. }
  601. return $a_courses;
  602. }
  603. function count_student_assignments($student_id, $course_code) {
  604. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  605. // protect datas
  606. $student_id = intval($student_id);
  607. $course_code = addslashes($course_code);
  608. // get the informations of the course
  609. $a_course = CourseManager :: get_course_information($course_code);
  610. if(!empty($a_course['db_name']))
  611. {
  612. // table definition
  613. $tbl_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY, $a_course['db_name']);
  614. $sql = 'SELECT 1
  615. FROM ' . $tbl_item_property . '
  616. WHERE insert_user_id=' . $student_id . '
  617. AND tool="work"';
  618. $rs = api_sql_query($sql, __LINE__, __FILE__);
  619. return Database::num_rows($rs);
  620. }
  621. else
  622. {
  623. return null;
  624. }
  625. }
  626. function count_student_messages($student_id, $course_code) {
  627. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  628. // protect datas
  629. $student_id = intval($student_id);
  630. $course_code = addslashes($course_code);
  631. // get the informations of the course
  632. $a_course = CourseManager :: get_course_information($course_code);
  633. if(!empty($a_course['db_name']))
  634. {
  635. // table definition
  636. $tbl_messages = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']);
  637. $sql = 'SELECT 1
  638. FROM ' . $tbl_messages . '
  639. WHERE poster_id=' . $student_id;
  640. $rs = api_sql_query($sql, __LINE__, __FILE__);
  641. return Database::num_rows($rs);
  642. }
  643. else
  644. {
  645. return null;
  646. }
  647. }
  648. /**
  649. * This function counts the number of post by course
  650. * @param string $course_code - Course ID
  651. * @return int the number of post by course
  652. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  653. * @version enero 2009, dokeos 1.8.6
  654. */
  655. function count_number_of_posts_by_course($course_code) {
  656. //protect data
  657. $course_code = addslashes($course_code);
  658. // get the informations of the course
  659. $a_course = CourseManager :: get_course_information($course_code);
  660. $count = 0;
  661. if (!empty($a_course['db_name'])) {
  662. $tbl_posts = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']);
  663. $sql = "SELECT * FROM $tbl_posts";
  664. $result = api_sql_query($sql, __FILE__, __LINE__);
  665. $count = Database::num_rows($result);
  666. return $count;
  667. } else {
  668. return null;
  669. }
  670. }
  671. /**
  672. * This function counts the number of threads by course
  673. * @param string $course_code - Course ID
  674. * @return int the number of threads by course
  675. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  676. * @version enero 2009, dokeos 1.8.6
  677. */
  678. function count_number_of_threads_by_course($course_code) {
  679. //protect data
  680. $course_code = addslashes($course_code);
  681. // get the informations of the course
  682. $a_course = CourseManager :: get_course_information($course_code);
  683. $count = 0;
  684. if (!empty($a_course['db_name'])) {
  685. $tbl_threads = Database :: get_course_table(TABLE_FORUM_THREAD, $a_course['db_name']);
  686. $sql = "SELECT * FROM $tbl_threads";
  687. $result = api_sql_query($sql, __FILE__, __LINE__);
  688. $count = Database::num_rows($result);
  689. return $count;
  690. } else {
  691. return null;
  692. }
  693. }
  694. /**
  695. * This function counts the number of forums by course
  696. * @param string $course_code - Course ID
  697. * @return int the number of forums by course
  698. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  699. * @version enero 2009, dokeos 1.8.6
  700. */
  701. function count_number_of_forums_by_course($course_code) {
  702. //protect data
  703. $course_code = addslashes($course_code);
  704. // get the informations of the course
  705. $a_course = CourseManager :: get_course_information($course_code);
  706. $count = 0;
  707. if (!empty($a_course['db_name'])) {
  708. $tbl_forums = Database :: get_course_table(TABLE_FORUM, $a_course['db_name']);
  709. $sql = "SELECT * FROM $tbl_forums";
  710. $result = api_sql_query($sql, __FILE__, __LINE__);
  711. $count = Database::num_rows($result);
  712. return $count;
  713. } else {
  714. return null;
  715. }
  716. }
  717. /**
  718. * This function counts the chat last connections by course in x days
  719. * @param string $course_code - Course ID
  720. * @param int $last_days - last x days
  721. * @return int the chat last connections by course in x days
  722. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  723. * @version enero 2009, dokeos 1.8.6
  724. */
  725. function chat_connections_during_last_x_days_by_course($course_code,$last_days) {
  726. //protect data
  727. $last_days = intval($last_days);
  728. $course_code = addslashes($course_code);
  729. // get the informations of the course
  730. $a_course = CourseManager :: get_course_information($course_code);
  731. $count = 0;
  732. if (!empty($a_course['db_name'])) {
  733. $tbl_stats_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS, $a_course['db_name']);
  734. $sql = "SELECT * FROM $tbl_stats_access WHERE DATE_SUB(NOW(),INTERVAL $last_days DAY) <= access_date
  735. AND access_cours_code = '$course_code' AND access_tool='".TOOL_CHAT."'";
  736. $result = api_sql_query($sql, __FILE__, __LINE__);
  737. $count = Database::num_rows($result);
  738. return $count;
  739. } else {
  740. return null;
  741. }
  742. }
  743. /**
  744. * This function gets the last student's connection in chat
  745. * @param int $student_id - Student ID
  746. * @param string $course_code - Course ID
  747. * @return string the last connection
  748. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  749. * @version enero 2009, dokeos 1.8.6
  750. */
  751. function chat_last_connection($student_id,$course_code) {
  752. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  753. //protect datas
  754. $student_id = intval($student_id);
  755. $course_code = addslashes($course_code);
  756. // get the informations of the course
  757. $a_course = CourseManager :: get_course_information($course_code);
  758. $date_time = '';
  759. if (!empty($a_course['db_name'])) {
  760. // table definition
  761. $tbl_stats_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS, $a_course['db_name']);
  762. $sql = "SELECT access_date FROM $tbl_stats_access
  763. WHERE access_tool='".TOOL_CHAT."' AND access_user_id='$student_id' AND access_cours_code = '$course_code' ORDER BY access_date DESC limit 1";
  764. $rs = api_sql_query($sql, __LINE__, __FILE__);
  765. $row = Database::fetch_array($rs);
  766. $last_connection = $row['access_date'];
  767. if (!empty($last_connection)) {
  768. $date_format_long = format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_connection));
  769. $time = explode(' ',$last_connection);
  770. $date_time = $date_format_long.' '.$time[1];
  771. }
  772. return $date_time;
  773. } else {
  774. return null;
  775. }
  776. }
  777. function count_student_visited_links($student_id, $course_code) {
  778. // protect datas
  779. $student_id = intval($student_id);
  780. $course_code = addslashes($course_code);
  781. // table definition
  782. $tbl_stats_links = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
  783. $sql = 'SELECT 1
  784. FROM ' . $tbl_stats_links . '
  785. WHERE links_user_id=' . $student_id . '
  786. AND links_cours_id="' . $course_code . '"';
  787. $rs = api_sql_query($sql, __LINE__, __FILE__);
  788. return Database::num_rows($rs);
  789. }
  790. function count_student_downloaded_documents($student_id, $course_code) {
  791. // protect datas
  792. $student_id = intval($student_id);
  793. $course_code = addslashes($course_code);
  794. // table definition
  795. $tbl_stats_documents = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  796. $sql = 'SELECT 1
  797. FROM ' . $tbl_stats_documents . '
  798. WHERE down_user_id=' . $student_id . '
  799. AND down_cours_id="' . $course_code . '"';
  800. $rs = api_sql_query($sql, __LINE__, __FILE__);
  801. return Database::num_rows($rs);
  802. }
  803. function get_course_list_in_session_from_student($user_id, $id_session) {
  804. $user_id = intval($user_id);
  805. $id_session = intval($id_session);
  806. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  807. $sql = 'SELECT course_code FROM ' . $tbl_session_course_user . ' WHERE id_user="' . $user_id . '" AND id_session="' . $id_session . '"';
  808. $result = api_sql_query($sql, __LINE__, __FILE__);
  809. $a_courses = array ();
  810. while ($row = Database::fetch_array($result)) {
  811. $a_courses[$row['course_code']] = $row['course_code'];
  812. }
  813. return $a_courses;
  814. }
  815. function get_inactives_students_in_course($course_code, $since, $session_id=0)
  816. {
  817. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  818. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  819. $inner = '';
  820. if($session_id!=0)
  821. {
  822. $inner = ' INNER JOIN '.$tbl_session_course_user.' session_course_user
  823. ON stats_login.course_code = session_course_user.course_code
  824. AND session_course_user.id_session = '.intval($session_id).'
  825. AND session_course_user.id_user = stats_login.user_id ';
  826. }
  827. $sql = 'SELECT user_id, MAX(login_course_date) max_date FROM'.$tbl_track_login.' stats_login'.$inner.'
  828. GROUP BY user_id
  829. HAVING DATE_SUB( NOW(), INTERVAL '.$since.' DAY) > max_date ';
  830. //HAVING DATE_ADD(max_date, INTERVAL '.$since.' DAY) < NOW() ';
  831. $rs = api_sql_query($sql,__FILE__,__LINE__);
  832. $inactive_users = array();
  833. while($user = Database::fetch_array($rs))
  834. {
  835. $inactive_users[] = $user['user_id'];
  836. }
  837. return $inactive_users;
  838. }
  839. function count_login_per_student($student_id, $course_code) {
  840. $student_id = intval($student_id);
  841. $course_code = addslashes($course_code);
  842. $tbl_course_rel_user = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  843. $sql = 'SELECT '.$student_id.'
  844. FROM ' . $tbl_course_rel_user . '
  845. WHERE access_user_id=' . $student_id . '
  846. AND access_cours_code="' . $course_code . '"';
  847. $rs = api_sql_query($sql, __FILE__, __LINE__);
  848. $nb_login = Database::num_rows($rs);
  849. return $nb_login;
  850. }
  851. function get_student_followed_by_drh($hr_dept_id) {
  852. $hr_dept_id = intval($hr_dept_id);
  853. $a_students = array ();
  854. $tbl_organism = Database :: get_main_table(TABLE_MAIN_ORGANISM);
  855. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  856. $sql = 'SELECT DISTINCT user_id FROM '.$tbl_user.' as user
  857. WHERE hr_dept_id='.$hr_dept_id;
  858. $rs = api_sql_query($sql, __FILE__, __LINE__);
  859. while($user = Database :: fetch_array($rs))
  860. {
  861. $a_students[$user['user_id']] = $user['user_id'];
  862. }
  863. return $a_students;
  864. }
  865. }
  866. ?>