events.lib.inc.php 59 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570
  1. <?php
  2. /* See license terms in /license.txt */
  3. /**
  4. * EVENTS LIBRARY
  5. *
  6. * This is the events library for Chamilo.
  7. * Include/require it in your code to use its functionality.
  8. * Functions of this library are used to record informations when some kind
  9. * of event occur. Each event has his own types of informations then each event
  10. * use its own function.
  11. *
  12. * @package chamilo.library
  13. */
  14. /* INIT SECTION */
  15. $TABLETRACK_LOGIN = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  16. $TABLETRACK_OPEN = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_OPEN);
  17. $TABLETRACK_ACCESS = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  18. $course_tracking_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  19. $TABLETRACK_DOWNLOADS = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  20. $TABLETRACK_UPLOADS = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_UPLOADS);
  21. $TABLETRACK_LINKS = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
  22. $TABLETRACK_EXERCICES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  23. $TABLETRACK_LASTACCESS = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LASTACCESS); //for "what's new" notification
  24. $TABLETRACK_DEFAULT = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  25. /* FUNCTIONS */
  26. /**
  27. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  28. * @desc Record information for open event (when homepage is opened)
  29. */
  30. function event_open()
  31. {
  32. global $_configuration;
  33. global $TABLETRACK_OPEN;
  34. // @getHostByAddr($_SERVER['REMOTE_ADDR']) : will provide host and country information
  35. // $_SERVER['HTTP_USER_AGENT'] : will provide browser and os information
  36. // $_SERVER['HTTP_REFERER'] : provide information about refering url
  37. if (isset($_SERVER['HTTP_REFERER'])) {
  38. $referer = Database::escape_string($_SERVER['HTTP_REFERER']);
  39. } else {
  40. $referer = '';
  41. }
  42. // record informations only if user comes from another site
  43. //if(!eregi($_configuration['root_web'],$referer))
  44. $pos = strpos($referer, $_configuration['root_web']);
  45. if ($pos === false && $referer != '') {
  46. $ip = api_get_real_ip();
  47. $remhost = @ getHostByAddr($ip);
  48. if ($remhost == $ip)
  49. $remhost = "Unknown"; // don't change this
  50. $reallyNow = api_get_utc_datetime();
  51. $sql = "INSERT INTO ".$TABLETRACK_OPEN."
  52. (open_remote_host,
  53. open_agent,
  54. open_referer,
  55. open_date)
  56. VALUES
  57. ('".$remhost."',
  58. '".Database::escape_string($_SERVER['HTTP_USER_AGENT'])."', '".Database::escape_string($referer)."', '$reallyNow')";
  59. $res = Database::query($sql);
  60. }
  61. return 1;
  62. }
  63. /**
  64. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  65. * @desc Record information for login event
  66. * (when an user identifies himself with username & password)
  67. */
  68. function event_login()
  69. {
  70. global $_user;
  71. global $TABLETRACK_LOGIN;
  72. $reallyNow = api_get_utc_datetime();
  73. $sql = "INSERT INTO ".$TABLETRACK_LOGIN." (login_user_id, login_ip, login_date, logout_date) VALUES
  74. ('".$_user['user_id']."',
  75. '".Database::escape_string(api_get_real_ip())."',
  76. '".$reallyNow."',
  77. '".$reallyNow."'
  78. )";
  79. Database::query($sql);
  80. // auto subscribe
  81. $user_status = $_user['status'] == SESSIONADMIN ? 'sessionadmin' :
  82. $_user['status'] == COURSEMANAGER ? 'teacher' :
  83. $_user['status'] == DRH ? 'DRH' : 'student';
  84. $autoSubscribe = api_get_setting($user_status.'_autosubscribe');
  85. if ($autoSubscribe) {
  86. $autoSubscribe = explode('|', $autoSubscribe);
  87. foreach ($autoSubscribe as $code) {
  88. if (CourseManager::course_exists($code)) {
  89. CourseManager::subscribe_user($_user['user_id'], $code);
  90. }
  91. }
  92. }
  93. }
  94. /**
  95. * @param tool name of the tool (name in mainDb.accueil table)
  96. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  97. * @desc Record information for access event for courses
  98. */
  99. function event_access_course()
  100. {
  101. global $TABLETRACK_ACCESS, $TABLETRACK_LASTACCESS;
  102. $id_session = api_get_session_id();
  103. $now = api_get_utc_datetime();
  104. $_cid = api_get_course_id();
  105. $user_id = api_get_user_id();
  106. if ($user_id) {
  107. $user_id = "'".$user_id."'";
  108. } else {
  109. $user_id = "0"; // no one
  110. }
  111. $sql = "INSERT INTO ".$TABLETRACK_ACCESS." (access_user_id, access_cours_code, access_date, access_session_id) VALUES
  112. (".$user_id.", '".$_cid."', '".$now."','".$id_session."')";
  113. $res = Database::query($sql);
  114. // added for "what's new" notification
  115. $sql = "UPDATE $TABLETRACK_LASTACCESS SET access_date = '$now'
  116. WHERE access_user_id = $user_id AND access_cours_code = '$_cid' AND access_tool IS NULL AND access_session_id=".$id_session;
  117. $res = Database::query($sql);
  118. if (Database::affected_rows() == 0) {
  119. $sql = "INSERT INTO $TABLETRACK_LASTACCESS (access_user_id, access_cours_code, access_date, access_session_id)
  120. VALUES (".$user_id.", '".$_cid."', '$now', '".$id_session."')";
  121. $res = Database::query($sql);
  122. }
  123. // end "what's new" notification
  124. return 1;
  125. }
  126. /**
  127. * @param tool name of the tool (name in mainDb.accueil table)
  128. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  129. * @desc Record information for access event for tools
  130. *
  131. * $tool can take this values :
  132. * Links, Calendar, Document, Announcements,
  133. * Group, Video, Works, Users, Exercices, Course Desc
  134. * ...
  135. * Values can be added if new modules are created (15char max)
  136. * I encourage to use $nameTool as $tool when calling this function
  137. *
  138. * Functionality for "what's new" notification is added by Toon Van Hoecke
  139. */
  140. function event_access_tool($tool, $id_session = 0)
  141. {
  142. global $_configuration;
  143. global $_user;
  144. global $_cid;
  145. global $TABLETRACK_ACCESS;
  146. global $_course;
  147. global $TABLETRACK_LASTACCESS; //for "what's new" notification
  148. $id_session = api_get_session_id();
  149. $tool = Database::escape_string($tool);
  150. $reallyNow = api_get_utc_datetime();
  151. $user_id = $_user['user_id'] ? "'".$_user['user_id']."'" : "0"; // no one
  152. // record information
  153. // only if user comes from the course $_cid
  154. //if( eregi($_configuration['root_web'].$_cid,$_SERVER['HTTP_REFERER'] ) )
  155. //$pos = strpos($_SERVER['HTTP_REFERER'],$_configuration['root_web'].$_cid);
  156. $pos = isset($_SERVER['HTTP_REFERER']) ? strpos(strtolower($_SERVER['HTTP_REFERER']), strtolower(api_get_path(WEB_COURSE_PATH).$_course['path'])) : false;
  157. // added for "what's new" notification
  158. $pos2 = isset($_SERVER['HTTP_REFERER']) ? strpos(strtolower($_SERVER['HTTP_REFERER']), strtolower($_configuration['root_web']."index")) : false;
  159. // end "what's new" notification
  160. if ($pos !== false || $pos2 !== false) {
  161. $sql = "INSERT INTO ".$TABLETRACK_ACCESS."
  162. (access_user_id,
  163. access_cours_code,
  164. access_tool,
  165. access_date,
  166. access_session_id
  167. )
  168. VALUES
  169. (".$user_id.",".// Don't add ' ' around value, it's already done.
  170. "'".$_cid."' ,
  171. '".$tool."',
  172. '".$reallyNow."',
  173. '".$id_session."')";
  174. $res = Database::query($sql);
  175. }
  176. // "what's new" notification
  177. $sql = "UPDATE $TABLETRACK_LASTACCESS
  178. SET access_date = '$reallyNow'
  179. WHERE access_user_id = ".$user_id." AND access_cours_code = '".$_cid."' AND access_tool = '".$tool."' AND access_session_id=".$id_session;
  180. $res = Database::query($sql);
  181. if (Database::affected_rows() == 0) {
  182. $sql = "INSERT INTO $TABLETRACK_LASTACCESS (access_user_id,access_cours_code,access_tool, access_date, access_session_id)
  183. VALUES (".$user_id.", '".$_cid."' , '$tool', '$reallyNow', $id_session)";
  184. $res = Database::query($sql);
  185. }
  186. return 1;
  187. }
  188. /**
  189. * @param doc_id id of document (id in mainDb.document table)
  190. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  191. * @desc Record information for download event
  192. * (when an user click to d/l a document)
  193. * it will be used in a redirection page
  194. * bug fixed: Roan Embrechts
  195. * Roan:
  196. * The user id is put in single quotes,
  197. * (why? perhaps to prevent sql insertion hacks?)
  198. * and later again.
  199. * Doing this twice causes an error, I remove one of them.
  200. */
  201. function event_download($doc_url)
  202. {
  203. $tbl_stats_downloads = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  204. $doc_url = Database::escape_string($doc_url);
  205. $reallyNow = api_get_utc_datetime();
  206. $user_id = "'".api_get_user_id()."'";
  207. $_cid = api_get_course_id();
  208. $sql = "INSERT INTO $tbl_stats_downloads (
  209. down_user_id,
  210. down_cours_id,
  211. down_doc_path,
  212. down_date,
  213. down_session_id
  214. )
  215. VALUES (
  216. ".$user_id.",
  217. '".$_cid."',
  218. '".$doc_url."',
  219. '".$reallyNow."',
  220. '".api_get_session_id()."'
  221. )";
  222. Database::query($sql);
  223. return 1;
  224. }
  225. /**
  226. * @param doc_id id of document (id in mainDb.document table)
  227. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  228. * @desc Record information for upload event
  229. * used in the works tool to record informations when
  230. * an user upload 1 work
  231. */
  232. function event_upload($doc_id)
  233. {
  234. global $_user;
  235. global $_cid;
  236. global $TABLETRACK_UPLOADS;
  237. $reallyNow = api_get_utc_datetime();
  238. if (isset($_user['user_id']) && $_user['user_id'] != '') {
  239. $user_id = "'".$_user['user_id']."'";
  240. } else {
  241. $user_id = "0"; // anonymous
  242. }
  243. $sql = "INSERT INTO ".$TABLETRACK_UPLOADS."
  244. ( upload_user_id,
  245. upload_cours_id,
  246. upload_work_id,
  247. upload_date,
  248. upload_session_id
  249. )
  250. VALUES (
  251. ".$user_id.",
  252. '".$_cid."',
  253. '".$doc_id."',
  254. '".$reallyNow."',
  255. '".api_get_session_id()."'
  256. )";
  257. $res = Database::query($sql);
  258. return 1;
  259. }
  260. /**
  261. * @param link_id (id in coursDb liens table)
  262. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  263. * @desc Record information for link event (when an user click on an added link)
  264. * it will be used in a redirection page
  265. */
  266. function event_link($link_id)
  267. {
  268. global $_user, $TABLETRACK_LINKS;
  269. $reallyNow = api_get_utc_datetime();
  270. if (isset($_user['user_id']) && $_user['user_id'] != '') {
  271. $user_id = "'".Database::escape_string($_user['user_id'])."'";
  272. } else {
  273. // anonymous
  274. $user_id = "0";
  275. }
  276. $sql = "INSERT INTO ".$TABLETRACK_LINKS."
  277. ( links_user_id,
  278. links_cours_id,
  279. links_link_id,
  280. links_date,
  281. links_session_id
  282. ) VALUES (
  283. ".$user_id.",
  284. '".api_get_course_id()."',
  285. '".Database::escape_string($link_id)."',
  286. '".$reallyNow."',
  287. '".api_get_session_id()."'
  288. )";
  289. $res = Database::query($sql);
  290. return 1;
  291. }
  292. /**
  293. * Update the TRACK_E_EXERCICES exercises
  294. *
  295. * @param int id of the attempt
  296. * @param int exercise id
  297. * @param mixed score obtained
  298. * @param int highest score for this exercise (and combination of questions)
  299. * @param int duration ( duration of the attempt in seconds )
  300. * @param int session_id
  301. * @param int learnpath_id (id of the learnpath)
  302. * @param int learnpath_item_id (id of the learnpath_item)
  303. *
  304. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  305. * @author Julio Montoya Armas <gugli100@gmail.com> Reworked 2010
  306. * @desc Record result of user when an exercice was done
  307. */
  308. function update_event_exercise($exeid, $exo_id, $score, $weight, $session_id, $learnpath_id = 0, $learnpath_item_id = 0, $learnpath_item_view_id = 0, $duration = 0, $status = '', $remind_list = array(), $end_date = null)
  309. {
  310. require_once api_get_path(SYS_CODE_PATH).'exercice/exercise.lib.php';
  311. global $debug;
  312. if ($debug) {
  313. error_log('Called to update_event_exercise');
  314. error_log('duration:'.$duration);
  315. }
  316. if ($exeid != '') {
  317. // Validation in case of fraud with actived control time
  318. if (!exercise_time_control_is_valid($exo_id, $learnpath_id, $learnpath_item_id)) {
  319. $score = 0;
  320. }
  321. if (!isset($status) || empty($status)) {
  322. $status = '';
  323. } else {
  324. $status = Database::escape_string($status);
  325. }
  326. $TABLETRACK_EXERCICES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  327. if (!empty($remind_list)) {
  328. $remind_list = array_map('intval', $remind_list);
  329. $remind_list = array_filter($remind_list);
  330. $remind_list = implode(",", $remind_list);
  331. } else {
  332. $remind_list = '';
  333. }
  334. if (empty($end_date)) {
  335. $end_date = api_get_utc_datetime();
  336. }
  337. $sql = "UPDATE $TABLETRACK_EXERCICES SET
  338. exe_exo_id = '".Database::escape_string($exo_id)."',
  339. exe_result = '".Database::escape_string($score)."',
  340. exe_weighting = '".Database::escape_string($weight)."',
  341. session_id = '".Database::escape_string($session_id)."',
  342. orig_lp_id = '".Database::escape_string($learnpath_id)."',
  343. orig_lp_item_id = '".Database::escape_string($learnpath_item_id)."',
  344. orig_lp_item_view_id = '".Database::escape_string($learnpath_item_view_id)."',
  345. exe_duration = '".Database::escape_string($duration)."',
  346. exe_date = '".$end_date."',
  347. status = '".$status."',
  348. questions_to_check = '".$remind_list."'
  349. WHERE exe_id = '".Database::escape_string($exeid)."'";
  350. $res = Database::query($sql);
  351. if ($debug)
  352. error_log('update_event_exercise called');
  353. if ($debug)
  354. error_log("$sql");
  355. //Deleting control time session track
  356. //exercise_time_control_delete($exo_id);
  357. return $res;
  358. } else {
  359. return false;
  360. }
  361. }
  362. /**
  363. * This function creates an empty Exercise in STATISTIC_TRACK_E_EXERCICES table.
  364. * After that in exercise_result.php we call the update_event_exercise() to update the exercise
  365. * @return $id the last id registered, or false on error
  366. * @author Julio Montoya <gugli100@gmail.com>
  367. * @desc Record result of user when an exercice was done
  368. * @deprecated this function seems to be deprecated
  369. */
  370. function create_event_exercice($exo_id)
  371. {
  372. if (empty($exo_id) or (intval($exo_id) != $exo_id)) {
  373. return false;
  374. }
  375. //error_log('create_event_exercice');
  376. $tbl_track_exe = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  377. $tbl_exe = Database::get_course_table(TABLE_QUIZ_TEST);
  378. $uid = api_get_user_id();
  379. $course_id = api_get_course_int_id();
  380. // First, check the exercise exists
  381. $sql_exe_id = "SELECT exercises.id FROM $tbl_exe as exercises WHERE c_id = $course_id AND exercises.id=$exo_id";
  382. $res_exe_id = Database::query($sql_exe_id);
  383. if ($res_exe_id === false) {
  384. return false;
  385. } //sql error
  386. if (Database::num_rows($res_exe_id) < 1) {
  387. return false;
  388. } //exe not found
  389. $row_exe_id = Database::fetch_row($res_exe_id);
  390. $exercise_id = intval($row_exe_id[0]);
  391. // Second, check if the record exists in the database (looking for incomplete records)
  392. $sql = "SELECT exe_id FROM $tbl_track_exe WHERE exe_exo_id = $exo_id AND ".
  393. "exe_user_id = $uid AND ".
  394. "exe_cours_id = '".api_get_course_id()."' AND ".
  395. "status = 'incomplete' AND ".
  396. "session_id = ".api_get_session_id();
  397. $res = Database::query($sql);
  398. if ($res === false) {
  399. return false;
  400. }
  401. if (Database::num_rows($res) > 0) {
  402. $row = Database::fetch_array($res);
  403. return $row['exe_id'];
  404. }
  405. // No record was found, so create one
  406. // get expire time to insert into the tracking record
  407. require_once api_get_path(SYS_CODE_PATH).'exercice/exercise.lib.php';
  408. $current_expired_time_key = get_time_control_key($exercise_id);
  409. if (isset($_SESSION['expired_time'][$current_expired_time_key])) { //Only for exercice of type "One page"
  410. $expired_date = $_SESSION['expired_time'][$current_expired_time_key];
  411. } else {
  412. $expired_date = '0000-00-00 00:00:00';
  413. }
  414. $sql = "INSERT INTO $tbl_track_exe ( exe_user_id, exe_cours_id, expired_time_control, exe_exo_id, session_id)
  415. VALUES ( $uid, '".api_get_course_id()."' ,'$expired_date','$exo_id','".api_get_session_id()."')";
  416. $res = Database::query($sql);
  417. $id = Database::insert_id();
  418. return $id;
  419. }
  420. /**
  421. * Record an event for this attempt at answering an exercise
  422. * @param float Score achieved
  423. * @param string Answer given
  424. * @param integer Question ID
  425. * @param integer Exercise ID
  426. * @param integer Position
  427. * @return boolean Result of the insert query
  428. */
  429. function exercise_attempt($score, $answer, $question_id, $exe_id, $position, $exercise_id = 0, $nano = null)
  430. {
  431. require_once api_get_path(SYS_CODE_PATH).'exercice/exercise.lib.php';
  432. global $debug, $learnpath_id, $learnpath_item_id;
  433. $score = Database::escape_string($score);
  434. $answer = Database::escape_string($answer);
  435. $question_id = Database::escape_string($question_id);
  436. $exe_id = Database::escape_string($exe_id);
  437. $position = Database::escape_string($position);
  438. $now = api_get_utc_datetime();
  439. $user_id = api_get_user_id();
  440. $TBL_TRACK_ATTEMPT = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  441. if ($debug)
  442. error_log("----- entering exercise_attempt() function ------");
  443. if ($debug)
  444. error_log("answer: $answer");
  445. if ($debug)
  446. error_log("score: $score");
  447. if ($debug)
  448. error_log("question_id : $question_id");
  449. if ($debug)
  450. error_log("position: $position");
  451. //Validation in case of fraud with actived control time
  452. if (!exercise_time_control_is_valid($exercise_id, $learnpath_id, $learnpath_item_id)) {
  453. if ($debug)
  454. error_log("exercise_time_control_is_valid is false");
  455. $score = 0;
  456. $answer = 0;
  457. }
  458. if (!empty($user_id)) {
  459. $user_id = "'".$user_id."'";
  460. } else {
  461. // anonymous
  462. $user_id = api_get_anonymous_id();
  463. }
  464. $file = '';
  465. if (isset($nano)) {
  466. $file = Database::escape_string(basename($nano->load_filename_if_exists(false)));
  467. }
  468. $course_code = api_get_course_id();
  469. $session_id = api_get_session_id();
  470. if (!empty($question_id) && !empty($exe_id) && !empty($user_id)) {
  471. //Check if attempt exists
  472. $sql = "SELECT exe_id FROM $TBL_TRACK_ATTEMPT
  473. WHERE course_code = '$course_code' AND session_id = $session_id AND exe_id = $exe_id AND user_id = $user_id AND question_id = $question_id AND position = $position";
  474. $result = Database::query($sql);
  475. if (Database::num_rows($result)) {
  476. if ($debug)
  477. error_log("Attempt already exist: exe_id: $exe_id - user_id:$user_id - question_id:$question_id");
  478. //The attempt already exist do not update use update_event_exercise() instead
  479. return false;
  480. }
  481. $sql = "INSERT INTO $TBL_TRACK_ATTEMPT (exe_id, user_id, question_id, answer, marks, course_code, session_id, position, tms, filename)
  482. VALUES (
  483. ".$exe_id.",
  484. ".$user_id.",
  485. '".$question_id."',
  486. '".$answer."',
  487. '".$score."',
  488. '".$course_code."',
  489. '".$session_id."',
  490. '".$position."',
  491. '".$now."',
  492. '".$file."'
  493. )";
  494. if ($debug)
  495. error_log("Saving question attempt: ");
  496. if ($debug)
  497. error_log($sql);
  498. $res = Database::query($sql);
  499. if (defined('ENABLED_LIVE_EXERCISE_TRACKING')) {
  500. $recording_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
  501. if ($debug)
  502. error_log("Saving e attempt recording ");
  503. $recording_changes = "INSERT INTO $recording_table (exe_id, question_id, marks, insert_date, author, session_id) VALUES ('$exe_id','$question_id','$score','".api_get_utc_datetime()."','', '".api_get_session_id()."') ";
  504. Database::query($recording_changes);
  505. }
  506. return $res;
  507. } else {
  508. return false;
  509. }
  510. }
  511. /**
  512. * Record an hotspot spot for this attempt at answering an hotspot question
  513. * @param int Exercise ID
  514. * @param int Question ID
  515. * @param int Answer ID
  516. * @param int Whether this answer is correct (1) or not (0)
  517. * @param string Coordinates of this point (e.g. 123;324)
  518. * @return boolean Result of the insert query
  519. * @uses Course code and user_id from global scope $_cid and $_user
  520. */
  521. function exercise_attempt_hotspot($exe_id, $question_id, $answer_id, $correct, $coords, $exerciseId = 0)
  522. {
  523. require_once api_get_path(SYS_CODE_PATH).'exercice/exercise.lib.php';
  524. global $safe_lp_id, $safe_lp_item_id;
  525. //Validation in case of fraud with actived control time
  526. if (!exercise_time_control_is_valid($exerciseId, $safe_lp_id, $safe_lp_item_id)) {
  527. $correct = 0;
  528. }
  529. $tbl_track_e_hotspot = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTSPOT);
  530. $sql = "INSERT INTO $tbl_track_e_hotspot (hotspot_user_id, hotspot_course_code, hotspot_exe_id, hotspot_question_id, hotspot_answer_id, hotspot_correct, hotspot_coordinate)".
  531. " VALUES ('".api_get_user_id()."',".
  532. " '".api_get_course_id()."', ".
  533. " '".Database :: escape_string($exe_id)."', ".
  534. " '".Database :: escape_string($question_id)."',".
  535. " '".Database :: escape_string($answer_id)."',".
  536. " '".Database :: escape_string($correct)."',".
  537. " '".Database :: escape_string($coords)."')";
  538. return $result = Database::query($sql);
  539. }
  540. /**
  541. * Records information for common (or admin) events (in the track_e_default table)
  542. * @author Yannick Warnier <yannick.warnier@beeznest.com>
  543. * @param string Type of event
  544. * @param string Type of value
  545. * @param string Value
  546. * @param string Timestamp (defaults to null)
  547. * @param integer User ID (defaults to null)
  548. * @param string Course code (defaults to null)
  549. * @assert ('','','') === false
  550. */
  551. function event_system($event_type, $event_value_type, $event_value, $datetime = null, $user_id = null, $course_code = null)
  552. {
  553. global $TABLETRACK_DEFAULT;
  554. if (empty($event_type)) {
  555. return false;
  556. }
  557. $event_type = Database::escape_string($event_type);
  558. $event_value_type = Database::escape_string($event_value_type);
  559. //Clean the user_info
  560. if ($event_value_type == LOG_USER_OBJECT) {
  561. if (is_array($event_value)) {
  562. unset($event_value['complete_name']);
  563. unset($event_value['complete_name_with_username']);
  564. unset($event_value['firstName']);
  565. unset($event_value['lastName']);
  566. unset($event_value['avatar_small']);
  567. unset($event_value['avatar_sys_path']);
  568. unset($event_value['avatar']);
  569. unset($event_value['mail']);
  570. unset($event_value['password']);
  571. unset($event_value['lastLogin']);
  572. unset($event_value['picture_uri']);
  573. $event_value = serialize($event_value);
  574. }
  575. }
  576. $event_value = Database::escape_string($event_value);
  577. $course_info = api_get_course_info($course_code);
  578. if (!empty($course_info)) {
  579. $course_id = $course_info['real_id'];
  580. $course_code = $course_info['code'];
  581. $course_code = Database::escape_string($course_code);
  582. } else {
  583. $course_id = null;
  584. $course_code = null;
  585. }
  586. if (!isset($datetime)) {
  587. $datetime = api_get_utc_datetime();
  588. }
  589. $datetime = Database::escape_string($datetime);
  590. if (!isset($user_id)) {
  591. $user_id = api_get_user_id();
  592. }
  593. $user_id = intval($user_id);
  594. $sql = "INSERT INTO $TABLETRACK_DEFAULT
  595. (default_user_id,
  596. default_cours_code,
  597. c_id,
  598. default_date,
  599. default_event_type,
  600. default_value_type,
  601. default_value
  602. )
  603. VALUES('$user_id.',
  604. '$course_code',
  605. '$course_id',
  606. '$datetime',
  607. '$event_type',
  608. '$event_value_type',
  609. '$event_value')";
  610. Database::query($sql);
  611. return true;
  612. }
  613. /**
  614. * Get every email stored in the database
  615. *
  616. * @param int $etId
  617. * @return type
  618. * @assert () !== false
  619. */
  620. function get_all_event_types()
  621. {
  622. global $event_config;
  623. $sql = 'SELECT etm.id, event_type_name, activated, language_id, message, subject, dokeos_folder
  624. FROM '.Database::get_main_table(TABLE_EVENT_EMAIL_TEMPLATE).' etm
  625. INNER JOIN '.Database::get_main_table(TABLE_MAIN_LANGUAGE).' l
  626. ON etm.language_id = l.id';
  627. $events_types = Database::store_result(Database::query($sql), 'ASSOC');
  628. $to_return = array();
  629. foreach ($events_types as $et) {
  630. $et['nameLangVar'] = $event_config[$et["event_type_name"]]["name_lang_var"];
  631. $et['descLangVar'] = $event_config[$et["event_type_name"]]["desc_lang_var"];
  632. $to_return[] = $et;
  633. }
  634. return $to_return;
  635. }
  636. /**
  637. * Get users linked to an event
  638. *
  639. * @param int $etId
  640. * @return type
  641. */
  642. function get_users_subscribed_to_event($event_name)
  643. {
  644. $event_name = Database::escape_string($event_name);
  645. $sql = 'SELECT u.* FROM '.Database::get_main_table(TABLE_MAIN_USER).' u,'
  646. .Database::get_main_table(TABLE_MAIN_EVENT_TYPE).' e,'
  647. .Database::get_main_table(TABLE_EVENT_TYPE_REL_USER).' ue
  648. WHERE ue.user_id = u.user_id
  649. AND e.name = "'.$event_name.'"
  650. AND e.id = ue.event_type_id';
  651. $return = Database::store_result(Database::query($sql), 'ASSOC');
  652. return json_encode($return);
  653. }
  654. /**
  655. * Get the users related to one event
  656. *
  657. * @param string $event_name
  658. */
  659. function get_event_users($event_name)
  660. {
  661. $event_name = Database::escape_string($event_name);
  662. $sql = 'SELECT user.user_id, user.firstname, user.lastname FROM '.Database::get_main_table(TABLE_MAIN_USER).' user JOIN '.Database::get_main_table(TABLE_EVENT_TYPE_REL_USER).' relUser
  663. ON relUser.user_id = user.user_id
  664. WHERE user.status <> '.ANONYMOUS.' AND relUser.event_type_name = "'.$event_name.'"';
  665. //For tests
  666. //$sql = 'SELECT user.user_id, user.firstname, user.lastname FROM '.Database::get_main_table(TABLE_MAIN_USER);
  667. $user_list = Database::store_result(Database::query($sql), 'ASSOC');
  668. return json_encode($user_list);
  669. }
  670. function get_events_by_user_and_type($user_id, $event_type)
  671. {
  672. global $TABLETRACK_DEFAULT;
  673. $user_id = intval($user_id);
  674. $event_type = Database::escape_string($event_type);
  675. $sql = "SELECT * FROM $TABLETRACK_DEFAULT
  676. WHERE default_value_type = 'user_id' AND
  677. default_value = $user_id AND
  678. default_event_type = '$event_type'
  679. ORDER BY default_date ";
  680. $result = Database::query($sql);
  681. if ($result) {
  682. return Database::store_result($result, 'ASSOC');
  683. }
  684. return false;
  685. }
  686. function get_latest_event_by_user_and_type($user_id, $event_type)
  687. {
  688. $result = get_events_by_user_and_type($user_id, $event_type);
  689. if ($result && !empty($result)) {
  690. return $result[0];
  691. }
  692. }
  693. /**
  694. * Save the new message for one event and for one language
  695. *
  696. * @param string $eventName
  697. * @param array $users
  698. * @param string $message
  699. * @param string $subject
  700. * @param string $eventMessageLanguage
  701. * @param int $activated
  702. */
  703. function save_event_type_message($event_name, $users, $message, $subject, $event_message_language, $activated)
  704. {
  705. $event_name = Database::escape_string($event_name);
  706. $activated = intval($activated);
  707. $event_message_language = Database::escape_string($event_message_language);
  708. // Deletes then re-adds the users linked to the event
  709. $sql = 'DELETE FROM '.Database::get_main_table(TABLE_EVENT_TYPE_REL_USER).' WHERE event_type_name = "'.$event_name.'" ';
  710. Database::query($sql);
  711. foreach ($users as $user) {
  712. $sql = 'INSERT INTO '.Database::get_main_table(TABLE_EVENT_TYPE_REL_USER).' (user_id,event_type_name) VALUES('.intval($user).',"'.$event_name.'")';
  713. Database::query($sql);
  714. }
  715. $language_id = api_get_language_id($event_message_language);
  716. // check if this template in this language already exists or not
  717. $sql = 'SELECT COUNT(id) as total FROM '.Database::get_main_table(TABLE_EVENT_EMAIL_TEMPLATE).'
  718. WHERE event_type_name = "'.$event_name.'" AND language_id = '.$language_id;
  719. $sql = Database::store_result(Database::query($sql), 'ASSOC');
  720. // if already exists, we update
  721. if ($sql[0]["total"] > 0) {
  722. $sql = 'UPDATE '.Database::get_main_table(TABLE_EVENT_EMAIL_TEMPLATE).'
  723. SET message = "'.Database::escape_string($message).'",
  724. subject = "'.Database::escape_string($subject).'",
  725. activated = '.$activated.'
  726. WHERE event_type_name = "'.$event_name.'" AND language_id = (SELECT id FROM '.Database::get_main_table(TABLE_MAIN_LANGUAGE).'
  727. WHERE dokeos_folder = "'.$event_message_language.'")';
  728. Database::query($sql);
  729. } else { // else we create a new record
  730. // gets the language_-_id
  731. $lang_id = '(SELECT id FROM '.Database::get_main_table(TABLE_MAIN_LANGUAGE).'
  732. WHERE dokeos_folder = "'.$event_message_language.'")';
  733. $lang_id = Database::store_result(Database::query($lang_id), 'ASSOC');
  734. if (!empty($lang_id[0]["id"])) {
  735. $sql = 'INSERT INTO '.Database::get_main_table(TABLE_EVENT_EMAIL_TEMPLATE).' (event_type_name, language_id, message, subject, activated)
  736. VALUES("'.$event_name.'", '.$lang_id[0]["id"].', "'.Database::escape_string($message).'", "'.Database::escape_string($subject).'", '.$activated.')';
  737. Database::query($sql);
  738. }
  739. }
  740. // set activated at every save
  741. $sql = 'UPDATE '.Database::get_main_table(TABLE_EVENT_EMAIL_TEMPLATE).'
  742. SET activated = '.$activated.'
  743. WHERE event_type_name = "'.$event_name.'"';
  744. Database::query($sql);
  745. }
  746. function eventType_mod($etId, $users, $message, $subject)
  747. {
  748. $etId = intval($etId);
  749. $sql = 'DELETE FROM '.Database::get_main_table(TABLE_EVENT_TYPE_REL_USER).' WHERE event_type_id = '.$etId.' ';
  750. Database::query($sql);
  751. foreach ($users as $user) {
  752. $sql = 'INSERT INTO '.Database::get_main_table(TABLE_EVENT_TYPE_REL_USER).' (user_id,event_type_id)
  753. VALUES('.intval($user).','.$etId.') ';
  754. Database::query($sql);
  755. }
  756. $sql = 'UPDATE '.Database::get_main_table(TABLE_MAIN_EVENT_TYPE_MESSAGE).'
  757. SET message = "'.Database::escape_string($message).'",
  758. subject = "'.Database::escape_string($subject).'"
  759. WHERE event_type_id = '.$etId.'';
  760. Database::query($sql);
  761. }
  762. /**
  763. * Gets the last attempt of an exercise based in the exe_id
  764. */
  765. function get_last_attempt_date_of_exercise($exe_id)
  766. {
  767. $exe_id = intval($exe_id);
  768. $track_attempts = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  769. $sql_track_attempt = 'SELECT max(tms) as last_attempt_date FROM '.$track_attempts.' WHERE exe_id='.$exe_id;
  770. $rs_last_attempt = Database::query($sql_track_attempt);
  771. $row_last_attempt = Database::fetch_array($rs_last_attempt);
  772. $last_attempt_date = $row_last_attempt['last_attempt_date']; //Get the date of last attempt
  773. return $last_attempt_date;
  774. }
  775. /**
  776. * Gets how many attempts exists by user, exercise, learning path
  777. * @param int user id
  778. * @param int exercise id
  779. * @param int lp id
  780. * @param int lp item id
  781. * @param int lp item view id
  782. */
  783. function get_attempt_count($user_id, $exerciseId, $lp_id, $lp_item_id, $lp_item_view_id)
  784. {
  785. $stat_table = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  786. $user_id = intval($user_id);
  787. $exerciseId = intval($exerciseId);
  788. $lp_id = intval($lp_id);
  789. $lp_item_id = intval($lp_item_id);
  790. $lp_item_view_id = intval($lp_item_view_id);
  791. $sql = "SELECT count(*) as count FROM $stat_table WHERE
  792. exe_exo_id = $exerciseId AND
  793. exe_user_id = $user_id AND
  794. status != 'incomplete' AND
  795. orig_lp_id = $lp_id AND
  796. orig_lp_item_id = $lp_item_id AND
  797. orig_lp_item_view_id = $lp_item_view_id AND
  798. exe_cours_id = '".api_get_course_id()."' AND
  799. session_id = '".api_get_session_id()."'";
  800. $query = Database::query($sql);
  801. if (Database::num_rows($query) > 0) {
  802. $attempt = Database :: fetch_array($query, 'ASSOC');
  803. return $attempt['count'];
  804. } else {
  805. return 0;
  806. }
  807. }
  808. function get_attempt_count_not_finished($user_id, $exerciseId, $lp_id, $lp_item_id)
  809. {
  810. $stat_table = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  811. $user_id = intval($user_id);
  812. $exerciseId = intval($exerciseId);
  813. $lp_id = intval($lp_id);
  814. $lp_item_id = intval($lp_item_id);
  815. $lp_item_view_id = intval($lp_item_view_id);
  816. $sql = "SELECT count(*) as count FROM $stat_table WHERE
  817. exe_exo_id = $exerciseId AND
  818. exe_user_id = $user_id AND
  819. status != 'incomplete' AND
  820. orig_lp_id = $lp_id AND
  821. orig_lp_item_id = $lp_item_id AND
  822. exe_cours_id = '".api_get_course_id()."' AND
  823. session_id = '".api_get_session_id()."'";
  824. $query = Database::query($sql);
  825. if (Database::num_rows($query) > 0) {
  826. $attempt = Database :: fetch_array($query, 'ASSOC');
  827. return $attempt['count'];
  828. } else {
  829. return 0;
  830. }
  831. }
  832. function delete_student_lp_events($user_id, $lp_id, $course, $session_id)
  833. {
  834. $lp_view_table = Database::get_course_table(TABLE_LP_VIEW);
  835. $lp_item_view_table = Database::get_course_table(TABLE_LP_ITEM_VIEW);
  836. $course_id = $course['real_id'];
  837. if (empty($course_id)) {
  838. $course_id = api_get_course_int_id();
  839. }
  840. $track_e_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  841. $track_attempts = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  842. $recording_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
  843. $user_id = intval($user_id);
  844. $lp_id = intval($lp_id);
  845. $session_id = intval($session_id);
  846. //Make sure we have the exact lp_view_id
  847. $sql = "SELECT id FROM $lp_view_table WHERE c_id = $course_id AND user_id = $user_id AND lp_id = $lp_id AND session_id = $session_id ";
  848. $result = Database::query($sql);
  849. if (Database::num_rows($result)) {
  850. $view = Database::fetch_array($result, 'ASSOC');
  851. $lp_view_id = $view['id'];
  852. $sql = "DELETE FROM $lp_item_view_table WHERE c_id = $course_id AND lp_view_id = $lp_view_id ";
  853. Database::query($sql);
  854. }
  855. $sql = "DELETE FROM $lp_view_table WHERE c_id = $course_id AND user_id = $user_id AND lp_id= $lp_id AND session_id = $session_id ";
  856. Database::query($sql);
  857. $sql = "SELECT exe_id FROM $track_e_exercises WHERE exe_user_id = $user_id AND session_id = $session_id AND exe_cours_id = '{$course['code']}' AND orig_lp_id = $lp_id";
  858. $result = Database::query($sql);
  859. $exe_list = array();
  860. while ($row = Database::fetch_array($result, 'ASSOC')) {
  861. $exe_list[] = $row['exe_id'];
  862. }
  863. if (!empty($exe_list) && is_array($exe_list) && count($exe_list) > 0) {
  864. $sql_delete = "DELETE FROM $track_e_exercises WHERE exe_id IN (".implode(',', $exe_list).")";
  865. Database::query($sql_delete);
  866. $sql_delete = "DELETE FROM $track_attempts WHERE exe_id IN (".implode(',', $exe_list).")";
  867. Database::query($sql_delete);
  868. $sql_delete = "DELETE FROM $recording_table WHERE exe_id IN (".implode(',', $exe_list).")";
  869. Database::query($sql_delete);
  870. }
  871. }
  872. /**
  873. * Delete all exercise attempts (included in LP or not)
  874. *
  875. * @param int user id
  876. * @param int exercise id
  877. * @param string course code
  878. * @param int session id
  879. */
  880. function delete_all_incomplete_attempts($user_id, $exercise_id, $course_code, $session_id = 0)
  881. {
  882. $track_e_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  883. $user_id = intval($user_id);
  884. $exercise_id = intval($exercise_id);
  885. $course_code = Database::escape_string($course_code);
  886. $session_id = intval($session_id);
  887. if (!empty($user_id) && !empty($exercise_id) && !empty($course_code)) {
  888. $sql = "DELETE FROM $track_e_exercises WHERE exe_user_id = $user_id AND exe_exo_id = $exercise_id AND exe_cours_id = '$course_code' AND session_id = $session_id AND status = 'incomplete' ";
  889. Database::query($sql);
  890. }
  891. }
  892. /**
  893. * Gets all exercise results (NO Exercises in LPs ) from a given exercise id, course, session
  894. * @param int exercise id
  895. * @param string course code
  896. * @param int session id
  897. * @return array with the results
  898. *
  899. */
  900. function get_all_exercise_results($exercise_id, $course_code, $session_id = 0, $load_question_list = true, $user_id = null)
  901. {
  902. $TABLETRACK_EXERCICES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  903. $TBL_TRACK_ATTEMPT = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  904. $course_code = Database::escape_string($course_code);
  905. $exercise_id = intval($exercise_id);
  906. $session_id = intval($session_id);
  907. $user_condition = null;
  908. if (!empty($user_id)) {
  909. $user_id = intval($user_id);
  910. $user_condition = "AND exe_user_id = $user_id ";
  911. }
  912. $sql = "SELECT * FROM $TABLETRACK_EXERCICES
  913. WHERE status = '' AND
  914. exe_cours_id = '$course_code' AND
  915. exe_exo_id = '$exercise_id' AND
  916. session_id = $session_id AND
  917. orig_lp_id =0 AND
  918. orig_lp_item_id = 0
  919. $user_condition
  920. ORDER BY exe_id";
  921. $res = Database::query($sql);
  922. $list = array();
  923. while ($row = Database::fetch_array($res, 'ASSOC')) {
  924. $list[$row['exe_id']] = $row;
  925. if ($load_question_list) {
  926. $sql = "SELECT * FROM $TBL_TRACK_ATTEMPT WHERE exe_id = {$row['exe_id']}";
  927. $res_question = Database::query($sql);
  928. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  929. $list[$row['exe_id']]['question_list'][$row_q['question_id']] = $row_q;
  930. }
  931. }
  932. }
  933. return $list;
  934. }
  935. /**
  936. * Gets all exercise results (NO Exercises in LPs ) from a given exercise id, course, session
  937. * @param string course code
  938. * @param int session id
  939. * @return array with the results
  940. *
  941. */
  942. function get_all_exercise_results_by_course($course_code, $session_id = 0, $get_count = true)
  943. {
  944. $table_track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  945. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  946. $course_code = Database::escape_string($course_code);
  947. $session_id = intval($session_id);
  948. $select = '*';
  949. if ($get_count) {
  950. $select = 'count(*) as count';
  951. }
  952. $sql = "SELECT $select FROM $table_track_exercises WHERE status = '' AND exe_cours_id = '$course_code' AND session_id = $session_id AND orig_lp_id = 0 AND orig_lp_item_id = 0 ORDER BY exe_id";
  953. $res = Database::query($sql);
  954. if ($get_count) {
  955. $row = Database::fetch_array($res, 'ASSOC');
  956. return $row['count'];
  957. } else {
  958. $list = array();
  959. while ($row = Database::fetch_array($res, 'ASSOC')) {
  960. $list[$row['exe_id']] = $row;
  961. $sql = "SELECT * FROM $table_track_attempt WHERE exe_id = {$row['exe_id']}";
  962. $res_question = Database::query($sql);
  963. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  964. $list[$row['exe_id']]['question_list'][$row_q['question_id']] = $row_q;
  965. }
  966. }
  967. return $list;
  968. }
  969. }
  970. /**
  971. * Gets all exercise results (NO Exercises in LPs) from a given exercise id, course, session
  972. * @param int exercise id
  973. * @param string course code
  974. * @param int session id
  975. * @return array with the results
  976. *
  977. */
  978. function get_all_exercise_results_by_user($user_id, $course_code, $session_id = 0)
  979. {
  980. $table_track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  981. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  982. $course_code = Database::escape_string($course_code);
  983. $exercise_id = intval($exercise_id);
  984. $session_id = intval($session_id);
  985. $user_id = intval($user_id);
  986. $sql = "SELECT * FROM $table_track_exercises WHERE status = '' AND exe_user_id = $user_id AND exe_cours_id = '$course_code' AND session_id = $session_id AND orig_lp_id = 0 AND orig_lp_item_id = 0 ORDER by exe_id";
  987. $res = Database::query($sql);
  988. $list = array();
  989. while ($row = Database::fetch_array($res, 'ASSOC')) {
  990. $list[$row['exe_id']] = $row;
  991. $sql = "SELECT * FROM $table_track_attempt WHERE exe_id = {$row['exe_id']}";
  992. $res_question = Database::query($sql);
  993. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  994. $list[$row['exe_id']]['question_list'][$row_q['question_id']] = $row_q;
  995. }
  996. }
  997. //echo '<pre>'; print_r($list);
  998. return $list;
  999. }
  1000. /**
  1001. * Gets exercise results (NO Exercises in LPs) from a given exercise id, course, session
  1002. * @param int exercise id
  1003. * @param string course code
  1004. * @param int session id
  1005. * @return array with the results
  1006. *
  1007. */
  1008. function get_exercise_results_by_attempt($exe_id)
  1009. {
  1010. $table_track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  1011. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1012. $table_track_attempt_recording = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
  1013. $exe_id = intval($exe_id);
  1014. $sql = "SELECT * FROM $table_track_exercises WHERE status = '' AND exe_id = $exe_id";
  1015. $res = Database::query($sql);
  1016. $list = array();
  1017. if (Database::num_rows($res)) {
  1018. $row = Database::fetch_array($res, 'ASSOC');
  1019. //Checking if this attempt was revised by a teacher
  1020. $sql_revised = 'SELECT exe_id FROM '.$table_track_attempt_recording.' WHERE author != "" AND exe_id = '.$exe_id.' LIMIT 1';
  1021. $res_revised = Database::query($sql_revised);
  1022. $row['attempt_revised'] = 0;
  1023. if (Database::num_rows($res_revised) > 0) {
  1024. $row['attempt_revised'] = 1;
  1025. }
  1026. $list[$exe_id] = $row;
  1027. $sql = "SELECT * FROM $table_track_attempt WHERE exe_id = $exe_id ORDER BY tms ASC";
  1028. $res_question = Database::query($sql);
  1029. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  1030. $list[$exe_id]['question_list'][$row_q['question_id']] = $row_q;
  1031. }
  1032. }
  1033. // echo '<pre>'; print_r($list); echo "</pre>";
  1034. return $list;
  1035. }
  1036. /**
  1037. * Gets exercise results (NO Exercises in LPs) from a given user, exercise id, course, session, lp_id, lp_item_id
  1038. * @param int user id
  1039. * @param int exercise id
  1040. * @param string course code
  1041. * @param int session id
  1042. * @param int lp id
  1043. * @param int lp item id
  1044. * @param string order asc or desc
  1045. * @return array with the results
  1046. *
  1047. */
  1048. function get_exercise_results_by_user($user_id, $exercise_id, $course_code, $session_id = 0, $lp_id = 0, $lp_item_id = 0, $order = null)
  1049. {
  1050. $table_track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  1051. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1052. $table_track_attempt_recording = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
  1053. $course_code = Database::escape_string($course_code);
  1054. $exercise_id = intval($exercise_id);
  1055. $session_id = intval($session_id);
  1056. $user_id = intval($user_id);
  1057. $lp_id = intval($lp_id);
  1058. $lp_item_id = intval($lp_item_id);
  1059. if (!in_array(strtolower($order), array('asc', 'desc'))) {
  1060. $order = 'asc';
  1061. }
  1062. $sql = "SELECT * FROM $table_track_exercises
  1063. WHERE status = '' AND
  1064. exe_user_id = $user_id AND
  1065. exe_cours_id = '$course_code' AND
  1066. exe_exo_id = $exercise_id AND
  1067. session_id = $session_id AND
  1068. orig_lp_id = $lp_id AND
  1069. orig_lp_item_id = $lp_item_id
  1070. ORDER by exe_id $order ";
  1071. $res = Database::query($sql);
  1072. $list = array();
  1073. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1074. //Checking if this attempt was revised by a teacher
  1075. $sql_revised = 'SELECT exe_id FROM '.$table_track_attempt_recording.' WHERE author != "" AND exe_id = '.$row['exe_id'].' LIMIT 1';
  1076. $res_revised = Database::query($sql_revised);
  1077. $row['attempt_revised'] = 0;
  1078. if (Database::num_rows($res_revised) > 0) {
  1079. $row['attempt_revised'] = 1;
  1080. }
  1081. $list[$row['exe_id']] = $row;
  1082. $sql = "SELECT * FROM $table_track_attempt WHERE exe_id = {$row['exe_id']}";
  1083. $res_question = Database::query($sql);
  1084. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  1085. $list[$row['exe_id']]['question_list'][$row_q['question_id']] = $row_q;
  1086. }
  1087. }
  1088. return $list;
  1089. }
  1090. /**
  1091. * Count exercise attempts (NO Exercises in LPs ) from a given exercise id, course, session
  1092. * @param int exercise id
  1093. * @param string course code
  1094. * @param int session id
  1095. * @return array with the results
  1096. *
  1097. */
  1098. function count_exercise_attempts_by_user($user_id, $exercise_id, $course_code, $session_id = 0)
  1099. {
  1100. $TABLETRACK_EXERCICES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  1101. $course_code = Database::escape_string($course_code);
  1102. $exercise_id = intval($exercise_id);
  1103. $session_id = intval($session_id);
  1104. $user_id = intval($user_id);
  1105. $sql = "SELECT count(*) as count FROM $TABLETRACK_EXERCICES
  1106. WHERE status = '' AND exe_user_id = '$user_id' AND exe_cours_id = '$course_code' AND exe_exo_id = '$exercise_id' AND session_id = $session_id AND orig_lp_id =0 AND orig_lp_item_id = 0 ORDER BY exe_id";
  1107. $res = Database::query($sql);
  1108. $result = 0;
  1109. if (Database::num_rows($res) > 0) {
  1110. $row = Database::fetch_array($res, 'ASSOC');
  1111. $result = $row['count'];
  1112. }
  1113. return $result;
  1114. }
  1115. /**
  1116. * Gets all exercise BEST results attempts (NO Exercises in LPs ) from a given exercise id, course, session per user
  1117. * @param int exercise id
  1118. * @param string course code
  1119. * @param int session id
  1120. * @return array with the results
  1121. * @todo rename this function
  1122. *
  1123. */
  1124. function get_best_exercise_results_by_user($exercise_id, $course_code, $session_id = 0)
  1125. {
  1126. $table_track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  1127. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1128. $course_code = Database::escape_string($course_code);
  1129. $exercise_id = intval($exercise_id);
  1130. $session_id = intval($session_id);
  1131. $sql = "SELECT * FROM $table_track_exercises WHERE status = '' AND exe_cours_id = '$course_code' AND exe_exo_id = '$exercise_id' AND session_id = $session_id AND orig_lp_id =0 AND orig_lp_item_id = 0 ORDER BY exe_id";
  1132. $res = Database::query($sql);
  1133. $list = array();
  1134. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1135. $list[$row['exe_id']] = $row;
  1136. $sql = "SELECT * FROM $table_track_attempt WHERE exe_id = {$row['exe_id']}";
  1137. $res_question = Database::query($sql);
  1138. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  1139. $list[$row['exe_id']]['question_list'][$row_q['question_id']] = $row_q;
  1140. }
  1141. }
  1142. //Getting the best results of every student
  1143. $best_score_return = array();
  1144. foreach ($list as $student_result) {
  1145. $user_id = $student_result['exe_user_id'];
  1146. $current_best_score[$user_id] = $student_result['exe_result'];
  1147. //echo $current_best_score[$user_id].' - '.$best_score_return[$user_id]['exe_result'].'<br />';
  1148. if ($current_best_score[$user_id] > $best_score_return[$user_id]['exe_result']) {
  1149. $best_score_return[$user_id] = $student_result;
  1150. }
  1151. }
  1152. return $best_score_return;
  1153. }
  1154. function get_best_attempt_exercise_results_per_user($user_id, $exercise_id, $course_code, $session_id = 0)
  1155. {
  1156. $table_track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  1157. //$table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1158. $course_code = Database::escape_string($course_code);
  1159. $exercise_id = intval($exercise_id);
  1160. $session_id = intval($session_id);
  1161. $user_id = intval($user_id);
  1162. $sql = "SELECT * FROM $table_track_exercises
  1163. WHERE status = '' AND
  1164. exe_cours_id = '$course_code' AND
  1165. exe_exo_id = '$exercise_id' AND
  1166. session_id = $session_id AND
  1167. exe_user_id = $user_id AND
  1168. orig_lp_id =0 AND
  1169. orig_lp_item_id = 0
  1170. ORDER BY exe_id";
  1171. $res = Database::query($sql);
  1172. $list = array();
  1173. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1174. $list[$row['exe_id']] = $row; /*
  1175. $sql = "SELECT * FROM $table_track_attempt WHERE exe_id = {$row['exe_id']}";
  1176. $res_question = Database::query($sql);
  1177. while($row_q = Database::fetch_array($res_question,'ASSOC')) {
  1178. $list[$row['exe_id']]['question_list'][$row_q['question_id']] = $row_q;
  1179. } */
  1180. }
  1181. //Getting the best results of every student
  1182. $best_score_return = array();
  1183. $best_score_return['exe_result'] = 0;
  1184. foreach ($list as $result) {
  1185. $current_best_score = $result;
  1186. if ($current_best_score['exe_result'] > $best_score_return['exe_result']) {
  1187. $best_score_return = $result;
  1188. }
  1189. }
  1190. if (!isset($best_score_return['exe_weighting'])) {
  1191. $best_score_return = array();
  1192. }
  1193. return $best_score_return;
  1194. }
  1195. function count_exercise_result_not_validated($exercise_id, $course_code, $session_id = 0)
  1196. {
  1197. $table_track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  1198. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
  1199. $course_code = Database::escape_string($course_code);
  1200. $session_id = intval($session_id);
  1201. $exercise_id = intval($exercise_id);
  1202. $sql = "SELECT count(e.exe_id) as count FROM $table_track_exercises e LEFT JOIN $table_track_attempt a ON e.exe_id = a.exe_id
  1203. WHERE exe_exo_id = $exercise_id AND
  1204. exe_cours_id = '$course_code' AND
  1205. e.session_id = $session_id AND
  1206. orig_lp_id = 0 AND
  1207. marks IS NULL AND
  1208. status = '' AND
  1209. orig_lp_item_id = 0 ORDER BY e.exe_id";
  1210. $res = Database::query($sql);
  1211. $row = Database::fetch_array($res, 'ASSOC');
  1212. return $row['count'];
  1213. }
  1214. /**
  1215. * Gets all exercise BEST results attempts (NO Exercises in LPs ) from a given exercise id, course, session per user
  1216. * @param int exercise id
  1217. * @param string course code
  1218. * @param int session id
  1219. * @return array with the results
  1220. *
  1221. */
  1222. function get_count_exercises_attempted_by_course($course_code, $session_id = 0)
  1223. {
  1224. $table_track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  1225. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1226. $course_code = Database::escape_string($course_code);
  1227. $session_id = intval($session_id);
  1228. $sql = "SELECT DISTINCT exe_exo_id, exe_user_id FROM $table_track_exercises WHERE status = '' AND exe_cours_id = '$course_code' AND session_id = $session_id AND orig_lp_id =0 AND orig_lp_item_id = 0 ORDER BY exe_id";
  1229. $res = Database::query($sql);
  1230. $count = 0;
  1231. if (Database::num_rows($res) > 0) {
  1232. $count = Database::num_rows($res);
  1233. }
  1234. return $count;
  1235. }
  1236. /**
  1237. * Gets all exercise events from a Learning Path within a Course nd Session
  1238. * @param int exercise id
  1239. * @param string course_code
  1240. * @param int session id
  1241. * @return array
  1242. */
  1243. function get_all_exercise_event_from_lp($exercise_id, $course_code, $session_id = 0)
  1244. {
  1245. $table_track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  1246. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1247. $course_code = Database::escape_string($course_code);
  1248. $exercise_id = intval($exercise_id);
  1249. $session_id = intval($session_id);
  1250. $sql = "SELECT * FROM $table_track_exercises WHERE status = '' AND exe_cours_id = '$course_code' AND exe_exo_id = '$exercise_id' AND session_id = $session_id AND orig_lp_id !=0 AND orig_lp_item_id != 0";
  1251. $res = Database::query($sql);
  1252. $list = array();
  1253. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1254. $list[$row['exe_id']] = $row;
  1255. $sql = "SELECT * FROM $table_track_attempt WHERE exe_id = {$row['exe_id']}";
  1256. $res_question = Database::query($sql);
  1257. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  1258. $list[$row['exe_id']]['question_list'][$row_q['question_id']] = $row_q;
  1259. }
  1260. }
  1261. return $list;
  1262. }
  1263. function get_all_exercises_from_lp($lp_id, $course_id)
  1264. {
  1265. $lp_item_table = Database :: get_course_table(TABLE_LP_ITEM);
  1266. $course_id = intval($course_id);
  1267. $lp_id = intval($lp_id);
  1268. $sql = "SELECT * FROM $lp_item_table WHERE c_id = $course_id AND lp_id = '".$lp_id."' ORDER BY parent_item_id, display_order";
  1269. $res = Database::query($sql);
  1270. $my_exercise_list = array();
  1271. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1272. if ($row['item_type'] == 'quiz') {
  1273. $my_exercise_list[] = $row;
  1274. }
  1275. }
  1276. return $my_exercise_list;
  1277. }
  1278. /**
  1279. * This function gets the comments of an exercise
  1280. *
  1281. * @param int $id
  1282. * @param int $question_id
  1283. * @return str the comment
  1284. */
  1285. function get_comments($exe_id, $question_id)
  1286. {
  1287. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1288. $sql = "SELECT teacher_comment FROM ".$table_track_attempt."
  1289. WHERE exe_id='".Database::escape_string($exe_id)."' AND question_id = '".Database::escape_string($question_id)."' ORDER by question_id";
  1290. $sqlres = Database::query($sql);
  1291. $comm = Database::result($sqlres, 0, "teacher_comment");
  1292. return $comm;
  1293. }
  1294. function get_all_exercise_event_by_exe_id($exe_id)
  1295. {
  1296. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1297. $exe_id = intval($exe_id);
  1298. $list = array();
  1299. $sql = "SELECT * FROM $table_track_attempt WHERE exe_id = $exe_id ORDER BY position";
  1300. $res_question = Database::query($sql);
  1301. if (Database::num_rows($res_question))
  1302. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  1303. $list[$row_q['question_id']][] = $row_q;
  1304. }
  1305. return $list;
  1306. }
  1307. function delete_attempt($exe_id, $user_id, $course_code, $session_id, $question_id)
  1308. {
  1309. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1310. $exe_id = intval($exe_id);
  1311. $user_id = intval($user_id);
  1312. $course_code = Database::escape_string($course_code);
  1313. $session_id = intval($session_id);
  1314. $question_id = intval($question_id);
  1315. $sql = "DELETE FROM $table_track_attempt WHERE exe_id = $exe_id AND user_id = $user_id AND course_code = '$course_code' AND session_id = $session_id AND question_id = $question_id ";
  1316. Database::query($sql);
  1317. }
  1318. function delete_attempt_hotspot($exe_id, $user_id, $course_code, $question_id)
  1319. {
  1320. $table_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTSPOT);
  1321. $exe_id = intval($exe_id);
  1322. $user_id = intval($user_id);
  1323. $course_code = Database::escape_string($course_code);
  1324. //$session_id = intval($session_id);
  1325. $question_id = intval($question_id);
  1326. $sql = "DELETE FROM $table_track_attempt WHERE hotspot_exe_id = $exe_id AND hotspot_user_id = $user_id AND hotspot_course_code = '$course_code' AND hotspot_question_id = $question_id ";
  1327. Database::query($sql);
  1328. }
  1329. /**
  1330. * User logs in for the first time to a course
  1331. */
  1332. function event_course_login($course_code, $user_id, $session_id)
  1333. {
  1334. global $course_tracking_table;
  1335. //@todo use api_get_utc_datetime
  1336. $time = api_get_datetime();
  1337. $course_code = Database::escape_string($course_code);
  1338. $user_id = Database::escape_string($user_id);
  1339. $session_id = Database::escape_string($session_id);
  1340. $sql = "INSERT INTO $course_tracking_table(course_code, user_id, login_course_date, logout_course_date, counter, session_id)
  1341. VALUES('".$course_code."', '".$user_id."', '$time', '$time', '1', '".$session_id."')";
  1342. Database::query($sql);
  1343. //Course catalog stats modifications see #4191
  1344. CourseManager::update_course_ranking(null, null, null, null, true, false);
  1345. }
  1346. /**
  1347. * For the sake of genericity, this function is a switch.
  1348. * It's called by EventsDispatcher and fires the good function
  1349. * with the good require_once.
  1350. *
  1351. * @param string $event_name
  1352. * @param array $params
  1353. */
  1354. function event_send_mail($event_name, $params)
  1355. {
  1356. EventsMail::send_mail($event_name, $params);
  1357. }
  1358. /**
  1359. * Internal function checking if the mail was already sent from that user to that user
  1360. * @param string $event_name
  1361. * @param int $user_from
  1362. * @param int $user_to
  1363. * @return boolean
  1364. */
  1365. function check_if_mail_already_sent($event_name, $user_from, $user_to = null)
  1366. {
  1367. if ($user_to == null) {
  1368. $sql = 'SELECT COUNT(*) as total FROM '.Database::get_main_table(TABLE_EVENT_SENT).'
  1369. WHERE user_from = '.$user_from.' AND event_type_name = "'.$event_name.'"';
  1370. } else {
  1371. $sql = 'SELECT COUNT(*) as total FROM '.Database::get_main_table(TABLE_EVENT_SENT).'
  1372. WHERE user_from = '.$user_from.' AND user_to = '.$user_to.' AND event_type_name = "'.$event_name.'"';
  1373. }
  1374. $result = Database::store_result(Database::query($sql), 'ASSOC');
  1375. return $result[0]["total"];
  1376. }
  1377. /*
  1378. *
  1379. * Filter EventEmailTemplate Filters see the main/inc/conf/events.conf.dist.php
  1380. *
  1381. */
  1382. /**
  1383. * Basic template event message filter (to be used by other filters as default)
  1384. * @param array $values (passing by reference)
  1385. * @return boolean True if everything is OK, false otherwise
  1386. */
  1387. function _event_send_mail_filter_func(&$values)
  1388. {
  1389. return true;
  1390. }
  1391. /**
  1392. * user_registration - send_mail filter
  1393. * @param array $values (passing by reference)
  1394. * @return boolean True if everything is OK, false otherwise
  1395. */
  1396. function user_registration_event_send_mail_filter_func(&$values)
  1397. {
  1398. $res = _event_send_mail_filter_func($values);
  1399. // proper logic for this filter
  1400. return $res;
  1401. }
  1402. /**
  1403. * portal_homepage_edited - send_mail filter
  1404. * @param array $values (passing by reference)
  1405. * @return boolean True if everything is OK, false otherwise
  1406. */
  1407. function portal_homepage_edited_event_send_mail_filter_func(&$values)
  1408. {
  1409. $res = _event_send_mail_filter_func($values);
  1410. // proper logic for this filter
  1411. return $res;
  1412. }