tracking.lib.php 47 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298
  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. /**
  200. * This function gets the score average from all tests in a course by student
  201. * @param int $student_id - User id
  202. * @param string $course_code - Course id
  203. * @return string value (number %) Which represents a round integer about the score average.
  204. */
  205. function get_avg_student_exercise_score($student_id, $course_code) {
  206. // protect datas
  207. $student_id = Database::escape_string($student_id);
  208. $course_code = Database::escape_string($course_code);
  209. // get the informations of the course
  210. $a_course = CourseManager :: get_course_information($course_code);
  211. if(!empty($a_course['db_name']))
  212. {
  213. // table definition
  214. $tbl_course_quiz = Database::get_course_table(TABLE_QUIZ_TEST,$a_course['db_name']);
  215. $tbl_stats_exercise = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  216. //get the list of exercises
  217. $sql = "SELECT id, title FROM $tbl_course_quiz WHERE active <> -1";
  218. $rs = api_sql_query($sql, __FILE__, __LINE__);
  219. $count_exe = Database::num_rows($rs);
  220. if ($count_exe > 0) {
  221. $quiz_avg_total_score = 0;
  222. while($quiz = Database::fetch_array($rs)) {
  223. // get the score and max score from track_e_exercise
  224. $sql = 'SELECT exe_result , exe_weighting
  225. FROM '.$tbl_stats_exercise.'
  226. WHERE exe_exo_id = '.(int)$quiz['id'].'
  227. AND exe_user_id = '.(int)$student_id.'
  228. AND orig_lp_id = 0
  229. AND exe_cours_id = "'.Database::escape_string($course_code).'"
  230. AND orig_lp_item_id = 0
  231. ORDER BY exe_date DESC';
  232. $rsAttempt = api_sql_query($sql, __FILE__, __LINE__);
  233. $nb_attempts = 0;
  234. $quiz_avg_score = 0;
  235. while ($attempt = Database::fetch_array($rsAttempt)) {
  236. $nb_attempts++;
  237. $exe_weight=$attempt['exe_weighting'];
  238. if ($exe_weight >0) {
  239. $quiz_avg_score += round(($attempt['exe_result']/$exe_weight*100),2);
  240. }
  241. }
  242. if($nb_attempts>0) {
  243. $quiz_avg_score = $quiz_avg_score / $nb_attempts;
  244. }
  245. $quiz_avg_total_score += $quiz_avg_score;
  246. }
  247. return $quiz_avg_total_score/$count_exe;
  248. }
  249. }
  250. else
  251. {
  252. return null;
  253. }
  254. }
  255. function get_avg_student_progress($student_id, $course_code) {
  256. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  257. // protect datas
  258. $student_id = intval($student_id);
  259. $course_code = addslashes($course_code);
  260. // get the informations of the course
  261. $a_course = CourseManager :: get_course_information($course_code);
  262. if(!empty($a_course['db_name']))
  263. {
  264. // table definition
  265. $tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW, $a_course['db_name']);
  266. $tbl_course_lp_view_item = Database :: get_course_table(TABLE_LP_ITEM_VIEW, $a_course['db_name']);
  267. $tbl_course_lp_item = Database :: get_course_table(TABLE_LP_ITEM, $a_course['db_name']);
  268. $tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN, $a_course['db_name']);
  269. //get the list of learning paths
  270. $sql = 'SELECT id FROM ' . $tbl_course_lp;
  271. $rs = api_sql_query($sql, __FILE__, __LINE__);
  272. $nb_lp = Database::num_rows($rs);
  273. $avg_progress = 0;
  274. if ($nb_lp > 0) {
  275. while ($lp = Database :: fetch_array($rs)) {
  276. // get the progress in learning pathes
  277. $sqlProgress = "SELECT progress
  278. FROM " . $tbl_course_lp_view . " AS lp_view
  279. WHERE lp_view.user_id = " . $student_id . "
  280. AND lp_view.lp_id = " . $lp['id'] . "
  281. ";
  282. $resultItem = api_sql_query($sqlProgress, __FILE__, __LINE__);
  283. if(Database::num_rows($resultItem)>0)
  284. {
  285. $avg_progress += Database::result($resultItem, 0, 0);
  286. }
  287. }
  288. $avg_progress = round($avg_progress / $nb_lp, 1);
  289. }
  290. return $avg_progress;
  291. }
  292. else
  293. {
  294. return null;
  295. }
  296. }
  297. /**
  298. * This function gets:
  299. * 1. The score average from all SCORM Test items in all LP in a course-> All the answers / All the max score.
  300. * 2. The score average from all Tests (quiz) in all LP in a course-> All the answers / All the max score.
  301. * 3. And finally it will return the average between 1. and 2.
  302. * This function does not take the results of a Test out of a LP
  303. *
  304. * @param User id
  305. * @param Course id
  306. * @param Array limit average to listed lp ids
  307. * @return string value (number %) Which represents a round integer explain in got in 3.
  308. */
  309. function get_avg_student_score($student_id, $course_code, $lp_ids=array()) {
  310. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  311. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  312. $table_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  313. $tbl_stats_exercices = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  314. $tbl_stats_attempts= Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  315. $course = CourseManager :: get_course_information($course_code);
  316. if (!empty($course['db_name'])) {
  317. $tbl_quiz_questions= Database :: get_course_table(TABLE_QUIZ_QUESTION,$course['db_name']);
  318. $lp_table = Database :: get_course_table(TABLE_LP_MAIN,$course['db_name']);
  319. $lp_item_table = Database :: get_course_table(TABLE_LP_ITEM,$course['db_name']);
  320. $lp_view_table = Database :: get_course_table(TABLE_LP_VIEW,$course['db_name']);
  321. $lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW,$course['db_name']);
  322. $sql_course_lp = 'SELECT id FROM '.$lp_table;
  323. if(count($lp_ids)!=0)
  324. {
  325. $sql_course_lp.=' WHERE id IN ('.implode(',',$lp_ids).')';
  326. }
  327. $sql_result_lp = api_sql_query($sql_course_lp, __FILE__, __LINE__);
  328. $lp_scorm_score_total = 0;
  329. $lp_scorm_weighting_total = 0;
  330. $lp_scorm_result_score_total = 0;
  331. $lp_scorm_loop=0;
  332. if(Database::num_rows($sql_result_lp)>0){
  333. //Scorm test
  334. while($a_learnpath = Database::fetch_array($sql_result_lp)){
  335. //We get the last view id of this LP
  336. $sql='SELECT max(id) as id FROM '.$lp_view_table.' WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($student_id).'"';
  337. $rs_last_lp_view_id = api_sql_query($sql, __FILE__, __LINE__);
  338. $lp_view_id = Database::result($rs_last_lp_view_id,0,'id');
  339. $sql_max_score='SELECT lp_iv.score as score,lp_i.max_score
  340. FROM '.$lp_item_view_table.' as lp_iv
  341. INNER JOIN '.$lp_item_table.' as lp_i
  342. ON lp_i.id = lp_iv.lp_item_id
  343. AND lp_i.item_type="sco"
  344. WHERE lp_view_id="'.$lp_view_id.'"';
  345. //$rs = api_sql_query($sql, __FILE__, __LINE__);
  346. //$sql_max_score='SELECT max_score FROM '.$lp_item_view_table.' WHERE lp_view_id="'.$lp_view_id.'" ';
  347. $res_max_score=Database::query($sql_max_score,__FILE__,__LINE__);
  348. $count_total_loop=0;
  349. while ($row_max_score=Database::fetch_array($res_max_score)) {
  350. if ($row_max_score['max_score']==0) {
  351. $lp_scorm_result_score_total+=($row_max_score['score']/100)*100;
  352. } else {
  353. $lp_scorm_result_score_total+=($row_max_score['score']/$row_max_score['max_score'])*100;
  354. }
  355. $count_total_loop++;
  356. }
  357. if ($count_total_loop==0) {
  358. $count_total_loop=1;
  359. }
  360. $score_of_scorm_calculate=round((($lp_scorm_result_score_total/$count_total_loop)*100),2);
  361. }
  362. //The next call to a MySQL fetch function, such as mysql_fetch_assoc(), would return that row.
  363. mysql_data_seek($sql_result_lp,0);
  364. //Quizz in a LP
  365. while($a_learnpath = Database::fetch_array($sql_result_lp)){
  366. //we got the maxscore this is wrong
  367. /*
  368. echo $sql = 'SELECT id as item_id, max_score
  369. FROM '.$lp_item_table.' AS lp_item
  370. WHERE lp_id='.$a_learnpath['id'].'
  371. AND item_type="quiz"';
  372. */
  373. //Path is the exercise id
  374. $sql = 'SELECT path, id as item_id, max_score
  375. FROM '.$lp_item_table.' AS lp_item
  376. WHERE lp_id='.$a_learnpath['id'].'
  377. AND item_type="quiz"';
  378. $rsItems = api_sql_query($sql, __FILE__, __LINE__);
  379. //We get the last view id of this LP
  380. $sql = "SELECT id FROM $lp_view_table WHERE user_id = '".intval($student_id)."' and lp_id='".intval($a_learnpath['id'])."'";
  381. //$sql='SELECT max(id) as id FROM '.$lp_view_table.' WHERE lp_id='.$a_learnpath['id'].' AND user_id="'.intval($student_id).'"';
  382. $rs_last_lp_view_id = api_sql_query($sql, __FILE__, __LINE__);
  383. $lp_view_id = intval(Database::result($rs_last_lp_view_id,0,'id'));
  384. $total_score = $total_weighting = 0;
  385. if ($lp_view_id!=0) {
  386. while ($item = Database :: fetch_array($rsItems, 'ASSOC')) {
  387. // we take the score from a LP because we have lp_view_id
  388. $sql = "SELECT score FROM $lp_item_view_table WHERE lp_item_id = '".(int)$item['item_id']."' and lp_view_id = '".(int)$lp_view_id."'
  389. ORDER BY view_count DESC limit 1";
  390. /*$sql = 'SELECT score as student_score
  391. FROM '.$lp_item_view_table.' as lp_view_item
  392. WHERE lp_view_item.lp_item_id = '.$item['item_id'].'
  393. AND lp_view_id = "'.$lp_view_id.'" ';*/
  394. $rsScores = api_sql_query($sql, __FILE__, __LINE__);
  395. // Real max score - this was implemented because of the random exercises
  396. $sql_last_attempt = 'SELECT exe_id FROM '. $tbl_stats_exercices. ' ' .
  397. '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';
  398. $resultLastAttempt = api_sql_query($sql_last_attempt, __FILE__, __LINE__);
  399. $num = Database :: num_rows($resultLastAttempt);
  400. if ($num > 0){
  401. if ($num > 1){
  402. while ($rowLA = Database :: fetch_row($resultLastAttempt)) {
  403. $id_last_attempt = $rowLA[0];
  404. }
  405. } else {
  406. $id_last_attempt = Database :: result($resultLastAttempt, 0, 0);
  407. }
  408. }
  409. $sql = "SELECT SUM(t.ponderation) as maxscore from ( SELECT distinct question_id, marks,ponderation FROM $tbl_stats_attempts as at " .
  410. "INNER JOIN $tbl_quiz_questions as q on(q.id = at.question_id) where exe_id ='$id_last_attempt' ) as t";
  411. $result = api_sql_query($sql, __FILE__, __LINE__);
  412. $row_max_score = Database :: fetch_array($result);
  413. $maxscore = $row_max_score['maxscore'];
  414. if ($maxscore=='') {
  415. $maxscore = $item['max_score'];
  416. }
  417. // not right!
  418. /*if(Database::num_rows($rsScores)>0) {
  419. $total_score += Database::result($rsScores, 0, 0);
  420. //echo $total_weighting += $item['max_score'];
  421. $total_weighting += $maxscore;
  422. if($total_weighting>0) {
  423. //echo ($total_score/$total_weighting)*100;
  424. $lp_scorm_score_total += ($total_score/$total_weighting)*100;
  425. $lp_scorm_weighting_total+=100;
  426. }
  427. }*/
  428. if(Database::num_rows($rsScores)>0) {
  429. $total_score = Database::result($rsScores, 0, 0);
  430. //echo $total_weighting += $item['max_score'];
  431. $total_weighting += $maxscore;
  432. if($total_weighting>0 && $maxscore>0) {
  433. //echo $total_score.' - '.$maxscore; echo '<br>';
  434. //echo $lp_scorm_score_total += ($total_score/$total_weighting)*100;
  435. $lp_scorm_score_total += ($total_score/$maxscore)*100;
  436. $lp_scorm_weighting_total+=100;
  437. }
  438. }
  439. }
  440. }
  441. }
  442. }
  443. $totalScore = $lp_scorm_score_total;
  444. $pourcentageScore = 0;
  445. if($lp_scorm_weighting_total>0) {
  446. //i.e 10.52
  447. $pourcentageScore = round( (($totalScore * 100) / $lp_scorm_weighting_total),2);
  448. return $pourcentageScore;
  449. } elseif ($score_of_scorm_calculate>0) {
  450. return $score_of_scorm_calculate;
  451. } else {
  452. return null;
  453. }
  454. } else {
  455. return null;
  456. }
  457. }
  458. /**
  459. * gets the list of students followed by coach
  460. * @param integer $coach_id the id of the coach
  461. * @return Array the list of students
  462. */
  463. function get_student_followed_by_coach($coach_id) {
  464. $coach_id = intval($coach_id);
  465. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  466. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  467. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  468. $a_students = array ();
  469. //////////////////////////////////////////////////////////////
  470. // At first, courses where $coach_id is coach of the course //
  471. //////////////////////////////////////////////////////////////
  472. $sql = 'SELECT id_session, course_code FROM ' . $tbl_session_course . ' WHERE id_coach=' . $coach_id;
  473. global $_configuration;
  474. if ($_configuration['multiple_access_urls']==true) {
  475. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  476. $access_url_id = api_get_current_access_url_id();
  477. if ($access_url_id != -1){
  478. $sql = 'SELECT id_session, course_code
  479. FROM ' . $tbl_session_course . ' session_course INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
  480. ON (session_course.id_session=session_rel_url.session_id)
  481. WHERE id_coach=' . $coach_id.' AND access_url_id = '.$access_url_id;
  482. }
  483. }
  484. $result = api_sql_query($sql,__FILE__,__LINE__);
  485. while ($a_courses = Database::fetch_array($result)) {
  486. $course_code = $a_courses["course_code"];
  487. $id_session = $a_courses["id_session"];
  488. $sql = "SELECT distinct srcru.id_user
  489. FROM $tbl_session_course_user AS srcru
  490. WHERE course_code='$course_code' AND id_session='$id_session'";
  491. $rs = api_sql_query($sql,__FILE__,__LINE__);
  492. while ($row = Database::fetch_array($rs)) {
  493. $a_students[$row['id_user']] = $row['id_user'];
  494. }
  495. }
  496. //////////////////////////////////////////////////////////////
  497. // Then, courses where $coach_id is coach of the session //
  498. //////////////////////////////////////////////////////////////
  499. $sql = 'SELECT session_course_user.id_user
  500. FROM ' . $tbl_session_course_user . ' as session_course_user
  501. INNER JOIN ' . $tbl_session_course . ' as session_course
  502. ON session_course.course_code = session_course_user.course_code
  503. AND session_course_user.id_session = session_course.id_session
  504. INNER JOIN ' . $tbl_session . ' as session
  505. ON session.id = session_course.id_session
  506. AND session.id_coach = ' . $coach_id;
  507. if ($_configuration['multiple_access_urls']==true) {
  508. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  509. $access_url_id = api_get_current_access_url_id();
  510. if ($access_url_id != -1){
  511. $sql = 'SELECT session_course_user.id_user
  512. FROM ' . $tbl_session_course_user . ' as session_course_user
  513. INNER JOIN ' . $tbl_session_course . ' as session_course
  514. ON session_course.course_code = session_course_user.course_code
  515. AND session_course_user.id_session = session_course.id_session
  516. INNER JOIN ' . $tbl_session . ' as session
  517. ON session.id = session_course.id_session
  518. AND session.id_coach = ' . $coach_id.'
  519. INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
  520. ON session.id = session_rel_url.session_id WHERE access_url_id = '.$access_url_id;
  521. }
  522. }
  523. $result = api_sql_query($sql,__FILE__,__LINE__);
  524. while ($row = Database::fetch_array($result)) {
  525. $a_students[$row['id_user']] = $row['id_user'];
  526. }
  527. return $a_students;
  528. }
  529. function get_student_followed_by_coach_in_a_session($id_session, $coach_id) {
  530. $coach_id = intval($coach_id);
  531. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  532. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  533. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  534. $a_students = array ();
  535. //////////////////////////////////////////////////////////////
  536. // At first, courses where $coach_id is coach of the course //
  537. //////////////////////////////////////////////////////////////
  538. $sql = 'SELECT course_code FROM ' . $tbl_session_course . ' WHERE id_session="' . $id_session . '" AND id_coach=' . $coach_id;
  539. $result = api_sql_query($sql,__FILE__,__LINE__);
  540. while ($a_courses = Database::fetch_array($result)) {
  541. $course_code = $a_courses["course_code"];
  542. $sql = "SELECT distinct srcru.id_user
  543. FROM $tbl_session_course_user AS srcru
  544. WHERE course_code='$course_code' and id_session = '" . $id_session . "'";
  545. $rs = api_sql_query($sql, __FILE__, __LINE__);
  546. while ($row = Database::fetch_array($rs)) {
  547. $a_students[$row['id_user']] = $row['id_user'];
  548. }
  549. }
  550. //////////////////////////////////////////////////////////////
  551. // Then, courses where $coach_id is coach of the session //
  552. //////////////////////////////////////////////////////////////
  553. $dsl_session_coach = 'SELECT id_coach FROM ' . $tbl_session . ' WHERE id="' . $id_session . '" AND id_coach="' . $coach_id . '"';
  554. $result = api_sql_query($dsl_session_coach, __FILE__, __LINE__);
  555. //He is the session_coach so we select all the users in the session
  556. if (Database::num_rows($result) > 0) {
  557. $sql = 'SELECT DISTINCT srcru.id_user FROM ' . $tbl_session_course_user . ' AS srcru WHERE id_session="' . $id_session . '"';
  558. $result = api_sql_query($sql,__FILE__,__LINE__);
  559. while ($row = Database::fetch_array($result)) {
  560. $a_students[$row['id_user']] = $row['id_user'];
  561. }
  562. }
  563. return $a_students;
  564. }
  565. function is_allowed_to_coach_student($coach_id, $student_id) {
  566. $coach_id = intval($coach_id);
  567. $student_id = intval($student_id);
  568. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  569. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  570. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  571. //////////////////////////////////////////////////////////////
  572. // At first, courses where $coach_id is coach of the course //
  573. //////////////////////////////////////////////////////////////
  574. $sql = 'SELECT 1
  575. FROM ' . $tbl_session_course_user . ' AS session_course_user
  576. INNER JOIN ' . $tbl_session_course . ' AS session_course
  577. ON session_course.course_code = session_course_user.course_code
  578. AND id_coach=' . $coach_id . '
  579. WHERE id_user=' . $student_id;
  580. $result = api_sql_query($sql, __FILE__, __LINE__);
  581. if (Database::num_rows($result) > 0) {
  582. return true;
  583. }
  584. //////////////////////////////////////////////////////////////
  585. // Then, courses where $coach_id is coach of the session //
  586. //////////////////////////////////////////////////////////////
  587. $sql = 'SELECT session_course_user.id_user
  588. FROM ' . $tbl_session_course_user . ' as session_course_user
  589. INNER JOIN ' . $tbl_session_course . ' as session_course
  590. ON session_course.course_code = session_course_user.course_code
  591. INNER JOIN ' . $tbl_session . ' as session
  592. ON session.id = session_course.id_session
  593. AND session.id_coach = ' . $coach_id . '
  594. WHERE id_user = ' . $student_id;
  595. $result = api_sql_query($sql, __FILE__, __LINE__);
  596. if (Database::num_rows($result) > 0) {
  597. return true;
  598. }
  599. return false;
  600. }
  601. function get_courses_followed_by_coach($coach_id, $id_session = '')
  602. {
  603. $coach_id = intval($coach_id);
  604. if (!empty ($id_session))
  605. $id_session = intval($id_session);
  606. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  607. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  608. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  609. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  610. //////////////////////////////////////////////////////////////
  611. // At first, courses where $coach_id is coach of the course //
  612. //////////////////////////////////////////////////////////////
  613. $sql = 'SELECT DISTINCT course_code FROM ' . $tbl_session_course . ' WHERE id_coach=' . $coach_id;
  614. global $_configuration;
  615. if ($_configuration['multiple_access_urls']==true) {
  616. $tbl_course_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  617. $access_url_id = api_get_current_access_url_id();
  618. if ($access_url_id != -1){
  619. $sql = 'SELECT DISTINCT session_course.course_code FROM ' . $tbl_session_course . ' session_course INNER JOIN '.$tbl_course_rel_access_url.' course_rel_url
  620. ON (session_course.course_code = course_rel_url.course_code)
  621. WHERE id_coach=' . $coach_id.' AND access_url_id = '.$access_url_id;
  622. }
  623. }
  624. if (!empty ($id_session))
  625. $sql .= ' AND id_session=' . $id_session;
  626. $result = api_sql_query($sql, __FILE__, __LINE__);
  627. while ($row = Database::fetch_array($result)) {
  628. $a_courses[$row['course_code']] = $row['course_code'];
  629. }
  630. //////////////////////////////////////////////////////////////
  631. // Then, courses where $coach_id is coach of the session //
  632. //////////////////////////////////////////////////////////////
  633. $sql = 'SELECT DISTINCT session_course.course_code
  634. FROM ' . $tbl_session_course . ' as session_course
  635. INNER JOIN ' . $tbl_session . ' as session
  636. ON session.id = session_course.id_session
  637. AND session.id_coach = ' . $coach_id . '
  638. INNER JOIN ' . $tbl_course . ' as course
  639. ON course.code = session_course.course_code';
  640. if ($_configuration['multiple_access_urls']==true) {
  641. $tbl_course_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  642. $access_url_id = api_get_current_access_url_id();
  643. if ($access_url_id != -1){
  644. $sql = 'SELECT DISTINCT session_course.course_code
  645. FROM ' . $tbl_session_course . ' as session_course
  646. INNER JOIN ' . $tbl_session . ' as session
  647. ON session.id = session_course.id_session
  648. AND session.id_coach = ' . $coach_id . '
  649. INNER JOIN ' . $tbl_course . ' as course
  650. ON course.code = session_course.course_code
  651. INNER JOIN '.$tbl_course_rel_access_url.' course_rel_url
  652. ON (session_course.course_code = course_rel_url.course_code)';
  653. }
  654. }
  655. if (!empty ($id_session)) {
  656. $sql .= ' WHERE session_course.id_session=' . $id_session;
  657. if ($_configuration['multiple_access_urls']==true)
  658. $sql .= ' AND access_url_id = '.$access_url_id;
  659. } else {
  660. if ($_configuration['multiple_access_urls']==true)
  661. $sql .= ' WHERE access_url_id = '.$access_url_id;
  662. }
  663. $result = api_sql_query($sql, __FILE__, __LINE__);
  664. while ($row = Database::fetch_array($result)) {
  665. $a_courses[$row['course_code']] = $row['course_code'];
  666. }
  667. return $a_courses;
  668. }
  669. function get_sessions_coached_by_user($coach_id) {
  670. // table definition
  671. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  672. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  673. // protect datas
  674. $coach_id = intval($coach_id);
  675. // session where we are general coach
  676. $sql = 'SELECT DISTINCT id, name, date_start, date_end
  677. FROM ' . $tbl_session . '
  678. WHERE id_coach=' . $coach_id;
  679. global $_configuration;
  680. if ($_configuration['multiple_access_urls']==true) {
  681. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  682. $access_url_id = api_get_current_access_url_id();
  683. if ($access_url_id != -1){
  684. $sql = 'SELECT DISTINCT id, name, date_start, date_end
  685. FROM ' . $tbl_session . ' session INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
  686. ON (session.id = session_rel_url.session_id)
  687. WHERE id_coach=' . $coach_id.' AND access_url_id = '.$access_url_id;
  688. }
  689. }
  690. $rs = api_sql_query($sql,__FILE__,__LINE__);
  691. while ($row = Database::fetch_array($rs))
  692. {
  693. $a_sessions[$row["id"]] = $row;
  694. }
  695. // session where we are coach of a course
  696. $sql = 'SELECT DISTINCT session.id, session.name, session.date_start, session.date_end
  697. FROM ' . $tbl_session . ' as session
  698. INNER JOIN ' . $tbl_session_course . ' as session_course
  699. ON session.id = session_course.id_session
  700. AND session_course.id_coach=' . $coach_id;
  701. global $_configuration;
  702. if ($_configuration['multiple_access_urls']==true) {
  703. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  704. $access_url_id = api_get_current_access_url_id();
  705. if ($access_url_id != -1){
  706. $sql = 'SELECT DISTINCT session.id, session.name, session.date_start, session.date_end
  707. FROM ' . $tbl_session . ' as session
  708. INNER JOIN ' . $tbl_session_course . ' as session_course
  709. ON session.id = session_course.id_session AND session_course.id_coach=' . $coach_id.'
  710. INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
  711. ON (session.id = session_rel_url.session_id)
  712. WHERE access_url_id = '.$access_url_id;
  713. }
  714. }
  715. $rs = api_sql_query($sql,__FILE__,__LINE__);
  716. while ($row = Database::fetch_array($rs))
  717. {
  718. $a_sessions[$row["id"]] = $row;
  719. }
  720. if (is_array($a_sessions)) {
  721. foreach ($a_sessions as & $session) {
  722. if ($session['date_start'] == '0000-00-00') {
  723. $session['status'] = get_lang('SessionActive');
  724. }
  725. else {
  726. $date_start = explode('-', $session['date_start']);
  727. $time_start = mktime(0, 0, 0, $date_start[1], $date_start[2], $date_start[0]);
  728. $date_end = explode('-', $session['date_end']);
  729. $time_end = mktime(0, 0, 0, $date_end[1], $date_end[2], $date_end[0]);
  730. if ($time_start < time() && time() < $time_end) {
  731. $session['status'] = get_lang('SessionActive');
  732. }
  733. else{
  734. if (time() < $time_start) {
  735. $session['status'] = get_lang('SessionFuture');
  736. }
  737. else{
  738. if (time() > $time_end) {
  739. $session['status'] = get_lang('SessionPast');
  740. }
  741. }
  742. }
  743. }
  744. }
  745. }
  746. return $a_sessions;
  747. }
  748. function get_courses_list_from_session($session_id) {
  749. //protect datas
  750. $session_id = intval($session_id);
  751. // table definition
  752. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  753. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  754. $sql = 'SELECT DISTINCT course_code, id_coach
  755. FROM ' . $tbl_session_course . '
  756. WHERE id_session=' . $session_id;
  757. $rs = api_sql_query($sql, __FILE__, __LINE__);
  758. $a_courses = array ();
  759. while ($row = Database::fetch_array($rs)) {
  760. $a_courses[$row['course_code']] = $row;
  761. }
  762. return $a_courses;
  763. }
  764. function count_student_assignments($student_id, $course_code) {
  765. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  766. // protect datas
  767. $student_id = intval($student_id);
  768. $course_code = addslashes($course_code);
  769. // get the informations of the course
  770. $a_course = CourseManager :: get_course_information($course_code);
  771. if(!empty($a_course['db_name']))
  772. {
  773. // table definition
  774. $tbl_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY, $a_course['db_name']);
  775. $sql = 'SELECT 1
  776. FROM ' . $tbl_item_property . '
  777. WHERE insert_user_id=' . $student_id . '
  778. AND tool="work"';
  779. $rs = api_sql_query($sql, __LINE__, __FILE__);
  780. return Database::num_rows($rs);
  781. }
  782. else
  783. {
  784. return null;
  785. }
  786. }
  787. function count_student_messages($student_id, $course_code) {
  788. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  789. // protect datas
  790. $student_id = intval($student_id);
  791. $course_code = addslashes($course_code);
  792. // get the informations of the course
  793. $a_course = CourseManager :: get_course_information($course_code);
  794. if(!empty($a_course['db_name']))
  795. {
  796. // table definition
  797. $tbl_messages = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']);
  798. $sql = 'SELECT 1
  799. FROM ' . $tbl_messages . '
  800. WHERE poster_id=' . $student_id;
  801. $rs = api_sql_query($sql, __LINE__, __FILE__);
  802. return Database::num_rows($rs);
  803. }
  804. else
  805. {
  806. return null;
  807. }
  808. }
  809. /**
  810. * This function counts the number of post by course
  811. * @param string $course_code - Course ID
  812. * @return int the number of post by course
  813. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  814. * @version enero 2009, dokeos 1.8.6
  815. */
  816. function count_number_of_posts_by_course($course_code) {
  817. //protect data
  818. $course_code = addslashes($course_code);
  819. // get the informations of the course
  820. $a_course = CourseManager :: get_course_information($course_code);
  821. $count = 0;
  822. if (!empty($a_course['db_name'])) {
  823. $tbl_posts = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']);
  824. $sql = "SELECT count(*) FROM $tbl_posts";
  825. $result = api_sql_query($sql, __FILE__, __LINE__);
  826. $row = Database::fetch_row($result);
  827. $count = $row[0];
  828. return $count;
  829. } else {
  830. return null;
  831. }
  832. }
  833. /**
  834. * This function counts the number of threads by course
  835. * @param string $course_code - Course ID
  836. * @return int the number of threads by course
  837. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  838. * @version enero 2009, dokeos 1.8.6
  839. */
  840. function count_number_of_threads_by_course($course_code) {
  841. //protect data
  842. $course_code = addslashes($course_code);
  843. // get the informations of the course
  844. $a_course = CourseManager :: get_course_information($course_code);
  845. $count = 0;
  846. if (!empty($a_course['db_name'])) {
  847. $tbl_threads = Database :: get_course_table(TABLE_FORUM_THREAD, $a_course['db_name']);
  848. $sql = "SELECT count(*) FROM $tbl_threads";
  849. $result = api_sql_query($sql, __FILE__, __LINE__);
  850. $row = Database::fetch_row($result);
  851. $count = $row[0];
  852. return $count;
  853. } else {
  854. return null;
  855. }
  856. }
  857. /**
  858. * This function counts the number of forums by course
  859. * @param string $course_code - Course ID
  860. * @return int the number of forums by course
  861. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  862. * @version enero 2009, dokeos 1.8.6
  863. */
  864. function count_number_of_forums_by_course($course_code) {
  865. //protect data
  866. $course_code = addslashes($course_code);
  867. // get the informations of the course
  868. $a_course = CourseManager :: get_course_information($course_code);
  869. $count = 0;
  870. if (!empty($a_course['db_name'])) {
  871. $tbl_forums = Database :: get_course_table(TABLE_FORUM, $a_course['db_name']);
  872. $sql = "SELECT count(*) FROM $tbl_forums";
  873. $result = api_sql_query($sql, __FILE__, __LINE__);
  874. $row = Database::fetch_row($result);
  875. $count = $row[0];
  876. return $count;
  877. } else {
  878. return null;
  879. }
  880. }
  881. /**
  882. * This function counts the chat last connections by course in x days
  883. * @param string $course_code - Course ID
  884. * @param int $last_days - last x days
  885. * @return int the chat last connections by course in x days
  886. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  887. * @version enero 2009, dokeos 1.8.6
  888. */
  889. function chat_connections_during_last_x_days_by_course($course_code,$last_days) {
  890. //protect data
  891. $last_days = intval($last_days);
  892. $course_code = addslashes($course_code);
  893. // get the informations of the course
  894. $a_course = CourseManager :: get_course_information($course_code);
  895. $count = 0;
  896. if (!empty($a_course['db_name'])) {
  897. $tbl_stats_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS, $a_course['db_name']);
  898. $sql = "SELECT count(*) FROM $tbl_stats_access WHERE DATE_SUB(NOW(),INTERVAL $last_days DAY) <= access_date
  899. AND access_cours_code = '$course_code' AND access_tool='".TOOL_CHAT."'";
  900. $result = api_sql_query($sql, __FILE__, __LINE__);
  901. $row = Database::fetch_row($result);
  902. $count = $row[0];
  903. return $count;
  904. } else {
  905. return null;
  906. }
  907. }
  908. /**
  909. * This function gets the last student's connection in chat
  910. * @param int $student_id - Student ID
  911. * @param string $course_code - Course ID
  912. * @return string the last connection
  913. * @author Christian Fasanando <christian.fasanando@dokeos.com>,
  914. * @version enero 2009, dokeos 1.8.6
  915. */
  916. function chat_last_connection($student_id,$course_code) {
  917. require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
  918. //protect datas
  919. $student_id = intval($student_id);
  920. $course_code = addslashes($course_code);
  921. // get the informations of the course
  922. $a_course = CourseManager :: get_course_information($course_code);
  923. $date_time = '';
  924. if (!empty($a_course['db_name'])) {
  925. // table definition
  926. $tbl_stats_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS, $a_course['db_name']);
  927. $sql = "SELECT access_date FROM $tbl_stats_access
  928. WHERE access_tool='".TOOL_CHAT."' AND access_user_id='$student_id' AND access_cours_code = '$course_code' ORDER BY access_date DESC limit 1";
  929. $rs = api_sql_query($sql, __LINE__, __FILE__);
  930. $row = Database::fetch_array($rs);
  931. $last_connection = $row['access_date'];
  932. if (!empty($last_connection)) {
  933. $date_format_long = format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_connection));
  934. $time = explode(' ',$last_connection);
  935. $date_time = $date_format_long.' '.$time[1];
  936. }
  937. return $date_time;
  938. } else {
  939. return null;
  940. }
  941. }
  942. function count_student_visited_links($student_id, $course_code) {
  943. // protect datas
  944. $student_id = intval($student_id);
  945. $course_code = addslashes($course_code);
  946. // table definition
  947. $tbl_stats_links = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
  948. $sql = 'SELECT 1
  949. FROM ' . $tbl_stats_links . '
  950. WHERE links_user_id=' . $student_id . '
  951. AND links_cours_id="' . $course_code . '"';
  952. $rs = api_sql_query($sql, __LINE__, __FILE__);
  953. return Database::num_rows($rs);
  954. }
  955. function count_student_downloaded_documents($student_id, $course_code) {
  956. // protect datas
  957. $student_id = intval($student_id);
  958. $course_code = addslashes($course_code);
  959. // table definition
  960. $tbl_stats_documents = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  961. $sql = 'SELECT 1
  962. FROM ' . $tbl_stats_documents . '
  963. WHERE down_user_id=' . $student_id . '
  964. AND down_cours_id="' . $course_code . '"';
  965. $rs = api_sql_query($sql, __LINE__, __FILE__);
  966. return Database::num_rows($rs);
  967. }
  968. function get_course_list_in_session_from_student($user_id, $id_session) {
  969. $user_id = intval($user_id);
  970. $id_session = intval($id_session);
  971. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  972. $sql = 'SELECT course_code FROM ' . $tbl_session_course_user . ' WHERE id_user="' . $user_id . '" AND id_session="' . $id_session . '"';
  973. $result = api_sql_query($sql, __LINE__, __FILE__);
  974. $a_courses = array ();
  975. while ($row = Database::fetch_array($result)) {
  976. $a_courses[$row['course_code']] = $row['course_code'];
  977. }
  978. return $a_courses;
  979. }
  980. function get_inactives_students_in_course($course_code, $since, $session_id=0)
  981. {
  982. $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  983. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  984. $inner = '';
  985. if($session_id!=0)
  986. {
  987. $inner = ' INNER JOIN '.$tbl_session_course_user.' session_course_user
  988. ON stats_login.course_code = session_course_user.course_code
  989. AND session_course_user.id_session = '.intval($session_id).'
  990. AND session_course_user.id_user = stats_login.user_id ';
  991. }
  992. $sql = 'SELECT user_id, MAX(login_course_date) max_date FROM'.$tbl_track_login.' stats_login'.$inner.'
  993. GROUP BY user_id
  994. HAVING DATE_SUB( NOW(), INTERVAL '.$since.' DAY) > max_date ';
  995. //HAVING DATE_ADD(max_date, INTERVAL '.$since.' DAY) < NOW() ';
  996. $rs = api_sql_query($sql,__FILE__,__LINE__);
  997. $inactive_users = array();
  998. while($user = Database::fetch_array($rs))
  999. {
  1000. $inactive_users[] = $user['user_id'];
  1001. }
  1002. return $inactive_users;
  1003. }
  1004. function count_login_per_student($student_id, $course_code) {
  1005. $student_id = intval($student_id);
  1006. $course_code = addslashes($course_code);
  1007. $tbl_course_rel_user = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  1008. $sql = 'SELECT '.$student_id.'
  1009. FROM ' . $tbl_course_rel_user . '
  1010. WHERE access_user_id=' . $student_id . '
  1011. AND access_cours_code="' . $course_code . '"';
  1012. $rs = api_sql_query($sql, __FILE__, __LINE__);
  1013. $nb_login = Database::num_rows($rs);
  1014. return $nb_login;
  1015. }
  1016. function get_student_followed_by_drh($hr_dept_id) {
  1017. $hr_dept_id = intval($hr_dept_id);
  1018. $a_students = array ();
  1019. $tbl_organism = Database :: get_main_table(TABLE_MAIN_ORGANISM);
  1020. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  1021. $sql = 'SELECT DISTINCT user_id FROM '.$tbl_user.' as user
  1022. WHERE hr_dept_id='.$hr_dept_id;
  1023. $rs = api_sql_query($sql, __FILE__, __LINE__);
  1024. while($user = Database :: fetch_array($rs))
  1025. {
  1026. $a_students[$user['user_id']] = $user['user_id'];
  1027. }
  1028. return $a_students;
  1029. }
  1030. /**
  1031. * allow get average of test of scorm and lp
  1032. * @author isaac flores paz <florespaz@bidsoftperu.com>
  1033. * @param int the user id
  1034. * @param string the course id
  1035. */
  1036. function get_average_test_scorm_and_lp ($user_id,$course_id) {
  1037. //the score inside the Reporting table
  1038. $course_info=api_get_course_info($course_id);
  1039. $lp_table = Database :: get_course_table(TABLE_LP_MAIN,$course_info['dbName']);
  1040. $lp_view_table = Database :: get_course_table(TABLE_LP_VIEW,$course_info['dbName']);
  1041. $lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW,$course_info['dbName']);
  1042. $lp_item_table = Database :: get_course_table(TABLE_LP_ITEM,$course_info['dbName']);
  1043. $sql_type='SELECT id,lp_type FROM '.$lp_table;
  1044. $rs_type=Database::query($sql_type);
  1045. $average_data=0;
  1046. $count_loop=0;
  1047. while ($row_type=Database::fetch_array($rs_type)) {
  1048. if ($row_type['lp_type']==1) {//lp dokeos
  1049. $sql = "SELECT id FROM $lp_view_table WHERE user_id = '".intval($user_id)."' and lp_id='".$row_type['id']."'";
  1050. $rs_last_lp_view_id = Database::query($sql, __FILE__, __LINE__);
  1051. $lp_view_id = intval(Database::result($rs_last_lp_view_id,0,'id'));
  1052. $sql_list_view='SELECT li.max_score,lv.user_id,liw.score,(liw.score/li.max_score) as sum_data FROM '.$lp_item_table.' li INNER JOIN '.$lp_view_table.' lv
  1053. ON li.lp_id=lv.lp_id INNER JOIN '.$lp_item_view_table.' liw ON liw.lp_item_id=li.id WHERE lv.user_id="'.$user_id.'" AND li.item_type="quiz" AND liw.lp_view_id="'.$lp_view_id.'"';
  1054. $sum=0;
  1055. $tot=0;
  1056. $rs_list_view1=Database::query($sql_list_view,__FILE__,__LINE__);
  1057. while ($row_list_view=Database::fetch_array($rs_list_view1)) {
  1058. $sum=$sum+$row_list_view['sum_data'];
  1059. $tot++;
  1060. }
  1061. if ($tot==0) {
  1062. $tot=1;
  1063. }
  1064. $average_data1=$sum/$tot;
  1065. $sql_list_view='';
  1066. $rs_last_lp_view_id='';
  1067. } elseif ($row_type['lp_type']==2) {//lp scorm
  1068. $sql = "SELECT id FROM $lp_view_table WHERE user_id = '".intval($user_id)."' and lp_id='".$row_type['id']."'";
  1069. $rs_last_lp_view_id = Database::query($sql, __FILE__, __LINE__);
  1070. $lp_view_id = intval(Database::result($rs_last_lp_view_id,0,'id'));
  1071. $sql_list_view='SELECT li.max_score,lv.user_id,liw.score,((liw.score/li.max_score)*100) as sum_data FROM '.$lp_item_table.' li INNER JOIN '.$lp_view_table.' lv
  1072. ON li.lp_id=lv.lp_id INNER JOIN '.$lp_item_view_table.' liw ON liw.lp_item_id=li.id WHERE lv.user_id="'.$user_id.'" AND li.item_type="sco" AND liw.lp_view_id="'.$lp_view_id.'"';
  1073. $tot=0;
  1074. $sum=0;
  1075. $rs_list_view2=Database::query($sql_list_view,__FILE__,__LINE__);
  1076. while ($row_list_view=Database::fetch_array($rs_list_view2)) {
  1077. $sum=$sum+$row_list_view['sum_data'];
  1078. $tot++;
  1079. }
  1080. if ($tot==0) {
  1081. $tot=1;
  1082. }
  1083. $average_data2=$sum/$tot;
  1084. }
  1085. $average_data_sum=$average_data_sum+$average_data1+$average_data2;
  1086. $average_data2=0;
  1087. $average_data1=0;
  1088. $count_loop++;
  1089. }
  1090. if ((int)$count_loop > 0) {
  1091. $avg_student_score = round(($average_data_sum / $count_loop)*100, 2);
  1092. }
  1093. return $avg_student_score;
  1094. }
  1095. }
  1096. ?>