events.lib.php 83 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466
  1. <?php
  2. /* See license terms in /license.txt */
  3. //use Chamilo\UserBundle\Entity\User;
  4. use Chamilo\CoreBundle\Component\Utils\ChamiloApi;
  5. use ChamiloSession as Session;
  6. /**
  7. * Class Event
  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. class Event
  13. {
  14. /**
  15. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  16. * @desc Record information for open event (when homepage is opened)
  17. */
  18. public static function open()
  19. {
  20. global $_configuration;
  21. global $TABLETRACK_OPEN;
  22. // @getHostByAddr($_SERVER['REMOTE_ADDR']) : will provide host and country information
  23. // $_SERVER['HTTP_USER_AGENT'] : will provide browser and os information
  24. // $_SERVER['HTTP_REFERER'] : provide information about refering url
  25. if (isset($_SERVER['HTT_REFERER'])) {
  26. $referer = Database::escape_string($_SERVER['HTTP_REFERER']);
  27. } else {
  28. $referer = '';
  29. }
  30. // record informations only if user comes from another site
  31. //if(!eregi($_configuration['root_web'],$referer))
  32. $pos = strpos($referer, $_configuration['root_web']);
  33. if ($pos === false && $referer != '') {
  34. $ip = api_get_real_ip();
  35. $remhost = @gethostbyaddr($ip);
  36. if ($remhost == $ip) {
  37. $remhost = "Unknown";
  38. } // don't change this
  39. $reallyNow = api_get_utc_datetime();
  40. $params = [
  41. 'open_remote_host' => Database::escape_string($remhost),
  42. 'open_agent' => $_SERVER['HTTP_USER_AGENT'],
  43. 'open_referer' => $referer,
  44. 'open_date' => $reallyNow,
  45. ];
  46. Database::insert($TABLETRACK_OPEN, $params);
  47. }
  48. return 1;
  49. }
  50. /**
  51. * @author Sebastien Piraux <piraux_seb@hotmail.com> old code
  52. * @author Julio Montoya
  53. *
  54. * @param int $userId
  55. *
  56. * @return bool
  57. * @desc Record information for login event when an user identifies himself with username & password
  58. */
  59. public static function eventLogin($userId)
  60. {
  61. $userInfo = api_get_user_info($userId);
  62. $userId = (int) $userId;
  63. if (empty($userInfo)) {
  64. return false;
  65. }
  66. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  67. $reallyNow = api_get_utc_datetime();
  68. $userIp = Database::escape_string(api_get_real_ip());
  69. $sql = "INSERT INTO $table (login_user_id, user_ip, login_date, logout_date) VALUES
  70. ($userId,
  71. '$userIp',
  72. '$reallyNow',
  73. '$reallyNow'
  74. )";
  75. Database::query($sql);
  76. // Auto subscribe
  77. $user_status = $userInfo['status'] == SESSIONADMIN ? 'sessionadmin' : $userInfo['status'] == COURSEMANAGER ? 'teacher' : $userInfo['status'] == DRH ? 'DRH' : 'student';
  78. $autoSubscribe = api_get_setting($user_status.'_autosubscribe');
  79. if ($autoSubscribe) {
  80. $autoSubscribe = explode('|', $autoSubscribe);
  81. foreach ($autoSubscribe as $code) {
  82. if (CourseManager::course_exists($code)) {
  83. CourseManager::subscribeUser($userId, $code);
  84. }
  85. }
  86. }
  87. return true;
  88. }
  89. /**
  90. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  91. * @desc Record information for access event for courses
  92. */
  93. public static function accessCourse()
  94. {
  95. if (Session::read('login_as')) {
  96. return false;
  97. }
  98. $TABLETRACK_ACCESS = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  99. //for "what's new" notification
  100. $TABLETRACK_LASTACCESS = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
  101. $id_session = api_get_session_id();
  102. $now = api_get_utc_datetime();
  103. $courseId = api_get_course_int_id();
  104. $userId = api_get_user_id();
  105. $ip = Database::escape_string(api_get_real_ip());
  106. if ($userId) {
  107. $userId = $userId;
  108. } else {
  109. $userId = "0"; // no one
  110. }
  111. $sql = "INSERT INTO $TABLETRACK_ACCESS (user_ip, access_user_id, c_id, access_date, access_session_id)
  112. VALUES ('$ip', $userId, $courseId, '$now', $id_session)";
  113. Database::query($sql);
  114. // added for "what's new" notification
  115. $sql = "UPDATE $TABLETRACK_LASTACCESS SET access_date = '$now'
  116. WHERE
  117. access_user_id = $userId AND
  118. c_id = $courseId AND
  119. access_tool IS NULL AND
  120. access_session_id = $id_session";
  121. $result = Database::query($sql);
  122. if (Database::affected_rows($result) == 0) {
  123. $sql = "INSERT INTO $TABLETRACK_LASTACCESS (access_user_id, c_id, access_date, access_session_id)
  124. VALUES ($userId, $courseId, '$now', $id_session)";
  125. Database::query($sql);
  126. }
  127. return 1;
  128. }
  129. /**
  130. * @param string $tool name of the tool
  131. *
  132. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  133. * @desc Record information for access event for tools
  134. *
  135. * $tool can take this values :
  136. * Links, Calendar, Document, Announcements,
  137. * Group, Video, Works, Users, Exercises, Course Desc
  138. * ...
  139. * Values can be added if new modules are created (15char max)
  140. * I encourage to use $nameTool as $tool when calling this function
  141. *
  142. * Functionality for "what's new" notification is added by Toon Van Hoecke
  143. *
  144. * @return bool
  145. */
  146. public static function event_access_tool($tool)
  147. {
  148. if (Session::read('login_as')) {
  149. return false;
  150. }
  151. $tool = Database::escape_string($tool);
  152. if (empty($tool)) {
  153. return false;
  154. }
  155. $courseInfo = api_get_course_info();
  156. $sessionId = api_get_session_id();
  157. $reallyNow = api_get_utc_datetime();
  158. $userId = api_get_user_id();
  159. if (empty($courseInfo)) {
  160. return false;
  161. }
  162. $courseId = $courseInfo['real_id'];
  163. $tableAccess = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  164. //for "what's new" notification
  165. $tableLastAccess = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
  166. // record information
  167. // only if user comes from the course $_cid
  168. //if( eregi($_configuration['root_web'].$_cid,$_SERVER['HTTP_REFERER'] ) )
  169. //$pos = strpos($_SERVER['HTTP_REFERER'],$_configuration['root_web'].$_cid);
  170. $coursePath = isset($courseInfo['path']) ? $courseInfo['path'] : null;
  171. $pos = isset($_SERVER['HTTP_REFERER']) ? strpos(strtolower($_SERVER['HTTP_REFERER']), strtolower(api_get_path(WEB_COURSE_PATH).$coursePath)) : false;
  172. // added for "what's new" notification
  173. $pos2 = isset($_SERVER['HTTP_REFERER']) ? strpos(strtolower($_SERVER['HTTP_REFERER']), strtolower(api_get_path(WEB_PATH)."index")) : false;
  174. // end "what's new" notification
  175. if ($pos !== false || $pos2 !== false) {
  176. $params = [
  177. 'access_user_id' => $userId,
  178. 'c_id' => $courseId,
  179. 'access_tool' => $tool,
  180. 'access_date' => $reallyNow,
  181. 'access_session_id' => $sessionId,
  182. 'user_ip' => Database::escape_string(api_get_real_ip()),
  183. ];
  184. Database::insert($tableAccess, $params);
  185. }
  186. // "what's new" notification
  187. $sql = "UPDATE $tableLastAccess
  188. SET access_date = '$reallyNow'
  189. WHERE
  190. access_user_id = $userId AND
  191. c_id = $courseId AND
  192. access_tool = '$tool' AND
  193. access_session_id = $sessionId";
  194. $result = Database::query($sql);
  195. if (Database::affected_rows($result) == 0) {
  196. $params = [
  197. 'access_user_id' => $userId,
  198. 'c_id' => $courseId,
  199. 'access_tool' => $tool,
  200. 'access_date' => $reallyNow,
  201. 'access_session_id' => $sessionId,
  202. ];
  203. Database::insert($tableLastAccess, $params);
  204. }
  205. return true;
  206. }
  207. /**
  208. * Record information for download event (when an user click to d/l a
  209. * document) it will be used in a redirection page.
  210. *
  211. * @param string $documentUrl
  212. *
  213. * @return int
  214. *
  215. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  216. * @author Evie Embrechts (bug fixed: The user id is put in single quotes)
  217. */
  218. public static function event_download($documentUrl)
  219. {
  220. if (Session::read('login_as')) {
  221. return false;
  222. }
  223. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  224. $documentUrl = Database::escape_string($documentUrl);
  225. $reallyNow = api_get_utc_datetime();
  226. $userId = api_get_user_id();
  227. $courseId = api_get_course_int_id();
  228. $sessionId = api_get_session_id();
  229. $sql = "INSERT INTO $table (
  230. down_user_id,
  231. c_id,
  232. down_doc_path,
  233. down_date,
  234. down_session_id
  235. )
  236. VALUES (
  237. $userId,
  238. $courseId,
  239. '$documentUrl',
  240. '$reallyNow',
  241. $sessionId
  242. )";
  243. Database::query($sql);
  244. return 1;
  245. }
  246. /**
  247. * @param int $documentId of document (id in mainDb.document table)
  248. *
  249. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  250. * @desc Record information for upload event
  251. * used in the works tool to record informations when
  252. * an user upload 1 work
  253. *
  254. * @return int
  255. */
  256. public static function event_upload($documentId)
  257. {
  258. if (Session::read('login_as')) {
  259. return false;
  260. }
  261. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_UPLOADS);
  262. $courseId = api_get_course_int_id();
  263. $reallyNow = api_get_utc_datetime();
  264. $userId = api_get_user_id();
  265. $documentId = (int) $documentId;
  266. $sessionId = api_get_session_id();
  267. $sql = "INSERT INTO $table
  268. ( upload_user_id,
  269. c_id,
  270. upload_cours_id,
  271. upload_work_id,
  272. upload_date,
  273. upload_session_id
  274. )
  275. VALUES (
  276. $userId,
  277. $courseId,
  278. '',
  279. $documentId,
  280. '$reallyNow',
  281. $sessionId
  282. )";
  283. Database::query($sql);
  284. return 1;
  285. }
  286. /**
  287. * Record information for link event (when an user click on an added link)
  288. * it will be used in a redirection page.
  289. *
  290. * @param int $linkId (id in c_link table)
  291. *
  292. * @return int
  293. *
  294. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  295. */
  296. public static function event_link($linkId)
  297. {
  298. if (Session::read('login_as')) {
  299. return false;
  300. }
  301. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LINKS);
  302. $reallyNow = api_get_utc_datetime();
  303. $userId = api_get_user_id();
  304. $courseId = api_get_course_int_id();
  305. $linkId = (int) $linkId;
  306. $sessionId = api_get_session_id();
  307. $sql = "INSERT INTO ".$table."
  308. ( links_user_id,
  309. c_id,
  310. links_link_id,
  311. links_date,
  312. links_session_id
  313. ) VALUES (
  314. $userId,
  315. $courseId,
  316. $linkId,
  317. '$reallyNow',
  318. $sessionId
  319. )";
  320. Database::query($sql);
  321. return 1;
  322. }
  323. /**
  324. * Update the TRACK_E_EXERCICES exercises.
  325. *
  326. * @param int exeid id of the attempt
  327. * @param int exo_id exercise id
  328. * @param mixed result score
  329. * @param int weighting ( higher score )
  330. * @param int duration ( duration of the attempt in seconds )
  331. * @param int session_id
  332. * @param int learnpath_id (id of the learnpath)
  333. * @param int learnpath_item_id (id of the learnpath_item)
  334. *
  335. * @return bool
  336. *
  337. * @author Sebastien Piraux <piraux_seb@hotmail.com>
  338. * @author Julio Montoya Armas <gugli100@gmail.com> Reworked 2010
  339. * @desc Record result of user when an exercise was done
  340. */
  341. public static function updateEventExercise(
  342. $exeId,
  343. $exoId,
  344. $score,
  345. $weighting,
  346. $sessionId,
  347. $learnpathId = 0,
  348. $learnpathItemId = 0,
  349. $learnpathItemViewId = 0,
  350. $duration = 0,
  351. $questionsList = [],
  352. $status = '',
  353. $remindList = [],
  354. $endDate = null
  355. ) {
  356. if (empty($exeId)) {
  357. return false;
  358. }
  359. /*
  360. * Code commented due BT#8423 do not change the score to 0.
  361. *
  362. * Validation in case of fraud with actived control time
  363. if (!ExerciseLib::exercise_time_control_is_valid($exo_id, $learnpath_id, $learnpath_item_id)) {
  364. $score = 0;
  365. }
  366. */
  367. if (!isset($status) || empty($status)) {
  368. $status = '';
  369. } else {
  370. $status = Database::escape_string($status);
  371. }
  372. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  373. if (!empty($questionsList)) {
  374. $questionsList = array_map('intval', $questionsList);
  375. }
  376. if (!empty($remindList)) {
  377. $remindList = array_map('intval', $remindList);
  378. $remindList = array_filter($remindList);
  379. $remindList = implode(",", $remindList);
  380. } else {
  381. $remindList = '';
  382. }
  383. if (empty($endDate)) {
  384. $endDate = api_get_utc_datetime();
  385. }
  386. $exoId = (int) $exoId;
  387. $sessionId = (int) $sessionId;
  388. $learnpathId = (int) $learnpathId;
  389. $learnpathItemId = (int) $learnpathItemId;
  390. $learnpathItemViewId = (int) $learnpathItemViewId;
  391. $duration = (int) $duration;
  392. $exeId = (int) $exeId;
  393. $score = Database::escape_string($score);
  394. $weighting = Database::escape_string($weighting);
  395. $questions = implode(',', $questionsList);
  396. $userIp = Database::escape_string(api_get_real_ip());
  397. $sql = "UPDATE $table SET
  398. exe_exo_id = $exoId,
  399. exe_result = '$score',
  400. exe_weighting = '$weighting',
  401. session_id = $sessionId,
  402. orig_lp_id = $learnpathId,
  403. orig_lp_item_id = $learnpathItemId,
  404. orig_lp_item_view_id = $learnpathItemViewId,
  405. exe_duration = $duration,
  406. exe_date = '$endDate',
  407. status = '$status',
  408. questions_to_check = '$remindList',
  409. data_tracking = '$questions',
  410. user_ip = '$userIp'
  411. WHERE exe_id = $exeId";
  412. Database::query($sql);
  413. //Deleting control time session track
  414. //ExerciseLib::exercise_time_control_delete($exo_id);
  415. return true;
  416. }
  417. /**
  418. * Record an event for this attempt at answering an exercise.
  419. *
  420. * @param float Score achieved
  421. * @param string Answer given
  422. * @param int Question ID
  423. * @param int Exercise attempt ID a.k.a exe_id (from track_e_exercise)
  424. * @param int Position
  425. * @param int Exercise ID (from c_quiz)
  426. * @param bool update results?
  427. * @param $fileName string Filename (for audio answers - using nanogong)
  428. * @param int User ID The user who's going to get this score. Default value of null means "get from context".
  429. * @param int Course ID (from the "id" column of course table). Default value of null means "get from context".
  430. * @param int Session ID (from the session table). Default value of null means "get from context".
  431. * @param int Learnpath ID (from c_lp table). Default value of null means "get from context".
  432. * @param int Learnpath item ID (from the c_lp_item table). Default value of null means "get from context".
  433. *
  434. * @return bool Result of the insert query
  435. */
  436. public static function saveQuestionAttempt(
  437. $score,
  438. $answer,
  439. $question_id,
  440. $exe_id,
  441. $position,
  442. $exercise_id = 0,
  443. $updateResults = false,
  444. $fileName = null,
  445. $user_id = null,
  446. $course_id = null,
  447. $session_id = null,
  448. $learnpath_id = null,
  449. $learnpath_item_id = null
  450. ) {
  451. global $debug;
  452. $question_id = Database::escape_string($question_id);
  453. $exe_id = Database::escape_string($exe_id);
  454. $position = Database::escape_string($position);
  455. $now = api_get_utc_datetime();
  456. $course_id = (int) $course_id;
  457. // check user_id or get from context
  458. if (empty($user_id)) {
  459. $user_id = api_get_user_id();
  460. // anonymous
  461. if (empty($user_id)) {
  462. $user_id = api_get_anonymous_id();
  463. }
  464. }
  465. // check course_id or get from context
  466. if (empty($course_id)) {
  467. $course_id = api_get_course_int_id();
  468. }
  469. // check session_id or get from context
  470. $session_id = (int) $session_id;
  471. if (empty($session_id)) {
  472. $session_id = api_get_session_id();
  473. }
  474. // check learnpath_id or get from context
  475. if (empty($learnpath_id)) {
  476. global $learnpath_id;
  477. }
  478. // check learnpath_item_id or get from context
  479. if (empty($learnpath_item_id)) {
  480. global $learnpath_item_id;
  481. }
  482. $TBL_TRACK_ATTEMPT = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  483. if ($debug) {
  484. error_log("----- entering saveQuestionAttempt() function ------");
  485. error_log("answer: $answer");
  486. error_log("score: $score");
  487. error_log("question_id : $question_id");
  488. error_log("position: $position");
  489. }
  490. //Validation in case of fraud with active control time
  491. if (!ExerciseLib::exercise_time_control_is_valid($exercise_id, $learnpath_id, $learnpath_item_id)) {
  492. if ($debug) {
  493. error_log("exercise_time_control_is_valid is false");
  494. }
  495. $score = 0;
  496. $answer = 0;
  497. }
  498. if (!empty($question_id) && !empty($exe_id) && !empty($user_id)) {
  499. if (is_null($answer)) {
  500. $answer = '';
  501. }
  502. if (is_null($score)) {
  503. $score = 0;
  504. }
  505. $attempt = [
  506. 'user_id' => $user_id,
  507. 'question_id' => $question_id,
  508. 'answer' => $answer,
  509. 'marks' => $score,
  510. 'c_id' => $course_id,
  511. 'session_id' => $session_id,
  512. 'position' => $position,
  513. 'tms' => $now,
  514. 'filename' => !empty($fileName) ? basename($fileName) : $fileName,
  515. 'teacher_comment' => '',
  516. ];
  517. // Check if attempt exists.
  518. $sql = "SELECT exe_id FROM $TBL_TRACK_ATTEMPT
  519. WHERE
  520. c_id = $course_id AND
  521. session_id = $session_id AND
  522. exe_id = $exe_id AND
  523. user_id = $user_id AND
  524. question_id = $question_id AND
  525. position = $position";
  526. $result = Database::query($sql);
  527. if (Database::num_rows($result)) {
  528. if ($debug) {
  529. error_log("Attempt already exist: exe_id: $exe_id - user_id:$user_id - question_id:$question_id");
  530. }
  531. if ($updateResults == false) {
  532. //The attempt already exist do not update use update_event_exercise() instead
  533. return false;
  534. }
  535. } else {
  536. $attempt['exe_id'] = $exe_id;
  537. }
  538. if ($debug) {
  539. error_log("updateResults : $updateResults");
  540. error_log("Saving question attempt: ");
  541. error_log($sql);
  542. }
  543. $recording_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
  544. if ($updateResults == false) {
  545. $attempt_id = Database::insert($TBL_TRACK_ATTEMPT, $attempt);
  546. if ($debug) {
  547. error_log("Insert attempt with id #$attempt_id");
  548. }
  549. if (defined('ENABLED_LIVE_EXERCISE_TRACKING')) {
  550. if ($debug) {
  551. error_log("Saving e attempt recording ");
  552. }
  553. $attempt_recording = [
  554. 'exe_id' => $attempt_id,
  555. 'question_id' => $question_id,
  556. 'marks' => $score,
  557. 'insert_date' => $now,
  558. 'author' => '',
  559. 'session_id' => $session_id,
  560. ];
  561. Database::insert($recording_table, $attempt_recording);
  562. }
  563. } else {
  564. Database::update(
  565. $TBL_TRACK_ATTEMPT,
  566. $attempt,
  567. [
  568. 'exe_id = ? AND question_id = ? AND user_id = ? ' => [
  569. $exe_id,
  570. $question_id,
  571. $user_id,
  572. ],
  573. ]
  574. );
  575. if (defined('ENABLED_LIVE_EXERCISE_TRACKING')) {
  576. $attempt_recording = [
  577. 'exe_id' => $exe_id,
  578. 'question_id' => $question_id,
  579. 'marks' => $score,
  580. 'insert_date' => $now,
  581. 'author' => '',
  582. 'session_id' => $session_id,
  583. ];
  584. Database::update(
  585. $recording_table,
  586. $attempt_recording,
  587. [
  588. 'exe_id = ? AND question_id = ? AND session_id = ? ' => [
  589. $exe_id,
  590. $question_id,
  591. $session_id,
  592. ],
  593. ]
  594. );
  595. }
  596. $attempt_id = $exe_id;
  597. }
  598. return $attempt_id;
  599. } else {
  600. return false;
  601. }
  602. }
  603. /**
  604. * Record an hotspot spot for this attempt at answering an hotspot question.
  605. *
  606. * @param int $exeId
  607. * @param int $questionId Question ID
  608. * @param int $answerId Answer ID
  609. * @param int $correct
  610. * @param string $coords Coordinates of this point (e.g. 123;324)
  611. * @param bool $updateResults
  612. * @param int $exerciseId
  613. *
  614. * @return bool Result of the insert query
  615. *
  616. * @uses \Course code and user_id from global scope $_cid and $_user
  617. */
  618. public static function saveExerciseAttemptHotspot(
  619. $exeId,
  620. $questionId,
  621. $answerId,
  622. $correct,
  623. $coords,
  624. $updateResults = false,
  625. $exerciseId = 0
  626. ) {
  627. $debug = false;
  628. global $safe_lp_id, $safe_lp_item_id;
  629. if ($updateResults == false) {
  630. // Validation in case of fraud with activated control time
  631. if (!ExerciseLib::exercise_time_control_is_valid($exerciseId, $safe_lp_id, $safe_lp_item_id)) {
  632. if ($debug) {
  633. error_log('Attempt is fraud');
  634. }
  635. $correct = 0;
  636. }
  637. }
  638. if (empty($exeId)) {
  639. if ($debug) {
  640. error_log('exe id is empty');
  641. }
  642. return false;
  643. }
  644. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_HOTSPOT);
  645. if ($updateResults) {
  646. if ($debug) {
  647. error_log("Insert hotspot results: exeId: $exeId correct: $correct");
  648. }
  649. $params = [
  650. 'hotspot_correct' => $correct,
  651. 'hotspot_coordinate' => $coords,
  652. ];
  653. Database::update(
  654. $table,
  655. $params,
  656. [
  657. 'hotspot_user_id = ? AND hotspot_exe_id = ? AND hotspot_question_id = ? AND hotspot_answer_id = ? ' => [
  658. api_get_user_id(),
  659. $exeId,
  660. $questionId,
  661. $answerId,
  662. ],
  663. ]
  664. );
  665. } else {
  666. if ($debug) {
  667. error_log("Insert hotspot results: exeId: $exeId correct: $correct");
  668. }
  669. return Database::insert(
  670. $table,
  671. [
  672. 'hotspot_course_code' => api_get_course_id(),
  673. 'hotspot_user_id' => api_get_user_id(),
  674. 'c_id' => api_get_course_int_id(),
  675. 'hotspot_exe_id' => $exeId,
  676. 'hotspot_question_id' => $questionId,
  677. 'hotspot_answer_id' => $answerId,
  678. 'hotspot_correct' => $correct,
  679. 'hotspot_coordinate' => $coords,
  680. ]
  681. );
  682. }
  683. }
  684. /**
  685. * Records information for common (or admin) events (in the track_e_default table).
  686. *
  687. * @author Yannick Warnier <yannick.warnier@beeznest.com>
  688. *
  689. * @param string $event_type Type of event
  690. * @param string $event_value_type Type of value
  691. * @param mixed $event_value Value (string, or array in the case of user info)
  692. * @param string $datetime Datetime (UTC) (defaults to null)
  693. * @param int $user_id User ID (defaults to null)
  694. * @param int $course_id Course ID (defaults to null)
  695. * @param int $sessionId Session ID
  696. *
  697. * @return bool
  698. * @assert ('','','') === false
  699. */
  700. public static function addEvent(
  701. $event_type,
  702. $event_value_type,
  703. $event_value,
  704. $datetime = null,
  705. $user_id = null,
  706. $course_id = null,
  707. $sessionId = 0
  708. ) {
  709. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  710. if (empty($event_type)) {
  711. return false;
  712. }
  713. $event_type = Database::escape_string($event_type);
  714. $event_value_type = Database::escape_string($event_value_type);
  715. if (!empty($course_id)) {
  716. $course_id = (int) $course_id;
  717. } else {
  718. $course_id = api_get_course_int_id();
  719. }
  720. if (!empty($sessionId)) {
  721. $sessionId = (int) $sessionId;
  722. } else {
  723. $sessionId = api_get_session_id();
  724. }
  725. //Clean the user_info
  726. if ($event_value_type == LOG_USER_OBJECT) {
  727. if (is_array($event_value)) {
  728. unset($event_value['complete_name']);
  729. unset($event_value['complete_name_with_username']);
  730. unset($event_value['firstName']);
  731. unset($event_value['lastName']);
  732. unset($event_value['avatar_small']);
  733. unset($event_value['avatar']);
  734. unset($event_value['mail']);
  735. unset($event_value['password']);
  736. unset($event_value['last_login']);
  737. unset($event_value['picture_uri']);
  738. $event_value = serialize($event_value);
  739. }
  740. }
  741. // If event is an array then the $event_value_type should finish with
  742. // the suffix _array for example LOG_WORK_DATA = work_data_array
  743. if (is_array($event_value)) {
  744. $event_value = serialize($event_value);
  745. }
  746. $event_value = Database::escape_string($event_value);
  747. $sessionId = empty($sessionId) ? api_get_session_id() : (int) $sessionId;
  748. if (!isset($datetime)) {
  749. $datetime = api_get_utc_datetime();
  750. }
  751. $datetime = Database::escape_string($datetime);
  752. if (!isset($user_id)) {
  753. $user_id = api_get_user_id();
  754. }
  755. $params = [
  756. 'default_user_id' => $user_id,
  757. 'c_id' => $course_id,
  758. 'default_date' => $datetime,
  759. 'default_event_type' => $event_type,
  760. 'default_value_type' => $event_value_type,
  761. 'default_value' => $event_value,
  762. 'session_id' => $sessionId,
  763. ];
  764. Database::insert($table, $params);
  765. return true;
  766. }
  767. /**
  768. * Get every email stored in the database.
  769. *
  770. * @deprecated
  771. *
  772. * @return array
  773. * @assert () !== false
  774. */
  775. public static function get_all_event_types()
  776. {
  777. global $event_config;
  778. $sql = 'SELECT etm.id, event_type_name, activated, language_id, message, subject, dokeos_folder
  779. FROM '.Database::get_main_table(TABLE_EVENT_EMAIL_TEMPLATE).' etm
  780. INNER JOIN '.Database::get_main_table(TABLE_MAIN_LANGUAGE).' l
  781. ON etm.language_id = l.id';
  782. $events_types = Database::store_result(Database::query($sql), 'ASSOC');
  783. $to_return = [];
  784. foreach ($events_types as $et) {
  785. $et['nameLangVar'] = $event_config[$et["event_type_name"]]["name_lang_var"];
  786. $et['descLangVar'] = $event_config[$et["event_type_name"]]["desc_lang_var"];
  787. $to_return[] = $et;
  788. }
  789. return $to_return;
  790. }
  791. /**
  792. * Get the users related to one event.
  793. *
  794. * @param string $event_name
  795. *
  796. * @return string
  797. */
  798. public static function get_event_users($event_name)
  799. {
  800. $event_name = Database::escape_string($event_name);
  801. $sql = 'SELECT user.user_id, user.firstname, user.lastname
  802. FROM '.Database::get_main_table(TABLE_MAIN_USER).' user
  803. JOIN '.Database::get_main_table(TABLE_EVENT_TYPE_REL_USER).' relUser
  804. ON relUser.user_id = user.user_id
  805. WHERE user.status <> '.ANONYMOUS.' AND relUser.event_type_name = "'.$event_name.'"';
  806. $user_list = Database::store_result(Database::query($sql), 'ASSOC');
  807. return json_encode($user_list);
  808. }
  809. /**
  810. * @param int $user_id
  811. * @param string $event_type
  812. *
  813. * @return array|bool
  814. */
  815. public static function get_events_by_user_and_type($user_id, $event_type)
  816. {
  817. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  818. $user_id = (int) $user_id;
  819. $event_type = Database::escape_string($event_type);
  820. $sql = "SELECT * FROM $table
  821. WHERE default_value_type = 'user_id' AND
  822. default_value = $user_id AND
  823. default_event_type = '$event_type'
  824. ORDER BY default_date ";
  825. $result = Database::query($sql);
  826. if ($result) {
  827. return Database::store_result($result, 'ASSOC');
  828. }
  829. return false;
  830. }
  831. /**
  832. * Save the new message for one event and for one language.
  833. *
  834. * @param string $event_name
  835. * @param array $users
  836. * @param string $message
  837. * @param string $subject
  838. * @param string $event_message_language
  839. * @param int $activated
  840. */
  841. public static function save_event_type_message(
  842. $event_name,
  843. $users,
  844. $message,
  845. $subject,
  846. $event_message_language,
  847. $activated
  848. ) {
  849. $event_name = Database::escape_string($event_name);
  850. $activated = (int) $activated;
  851. $event_message_language = Database::escape_string($event_message_language);
  852. // Deletes then re-adds the users linked to the event
  853. $sql = 'DELETE FROM '.Database::get_main_table(TABLE_EVENT_TYPE_REL_USER).'
  854. WHERE event_type_name = "'.$event_name.'" ';
  855. Database::query($sql);
  856. $eventTable = Database::get_main_table(TABLE_EVENT_TYPE_REL_USER);
  857. foreach ($users as $user) {
  858. $user = (int) $user;
  859. $sql = "INSERT INTO $eventTable (user_id,event_type_name)
  860. VALUES($user,'$event_name')";
  861. Database::query($sql);
  862. }
  863. $language_id = api_get_language_id($event_message_language);
  864. // check if this template in this language already exists or not
  865. $eventMailTable = Database::get_main_table(TABLE_EVENT_EMAIL_TEMPLATE);
  866. $sql = "SELECT COUNT(id) as total
  867. FROM $eventMailTable
  868. WHERE event_type_name = '$event_name' AND language_id = $language_id";
  869. $sql = Database::store_result(Database::query($sql), 'ASSOC');
  870. $languageTable = Database::get_main_table(TABLE_MAIN_LANGUAGE);
  871. $message = Database::escape_string($message);
  872. $subject = Database::escape_string($subject);
  873. // if already exists, we update
  874. if ($sql[0]["total"] > 0) {
  875. $sql = "UPDATE $eventMailTable
  876. SET message = '$message',
  877. subject = '$subject',
  878. activated = $activated
  879. WHERE event_type_name = '$event_name' AND language_id = (
  880. SELECT id FROM $languageTable
  881. WHERE dokeos_folder = '$event_message_language'
  882. )";
  883. Database::query($sql);
  884. } else { // else we create a new record
  885. // gets the language_-_id
  886. $lang_id = "(SELECT id FROM $languageTable
  887. WHERE dokeos_folder = '$event_message_language')";
  888. $lang_id = Database::store_result(Database::query($lang_id), 'ASSOC');
  889. $lang_id = $lang_id[0]['id'];
  890. if (!empty($lang_id[0]["id"])) {
  891. $sql = "INSERT INTO $eventMailTable (event_type_name, language_id, message, subject, activated)
  892. VALUES ('$event_name', $lang_id, '$message', '$subject', $activated)";
  893. Database::query($sql);
  894. }
  895. }
  896. // set activated at every save
  897. $sql = "UPDATE $eventMailTable
  898. SET activated = $activated
  899. WHERE event_type_name = '$event_name'";
  900. Database::query($sql);
  901. }
  902. /**
  903. * Gets the last attempt of an exercise based in the exe_id.
  904. *
  905. * @param int $exeId
  906. *
  907. * @return mixed
  908. */
  909. public static function getLastAttemptDateOfExercise($exeId)
  910. {
  911. $exeId = (int) $exeId;
  912. $track_attempts = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  913. $sql = "SELECT max(tms) as last_attempt_date
  914. FROM $track_attempts
  915. WHERE exe_id = $exeId";
  916. $rs_last_attempt = Database::query($sql);
  917. $row_last_attempt = Database::fetch_array($rs_last_attempt);
  918. $date = $row_last_attempt['last_attempt_date']; //Get the date of last attempt
  919. return $date;
  920. }
  921. /**
  922. * Gets the last attempt of an exercise based in the exe_id.
  923. *
  924. * @param int $exeId
  925. *
  926. * @return mixed
  927. */
  928. public static function getLatestQuestionIdFromAttempt($exeId)
  929. {
  930. $exeId = (int) $exeId;
  931. $track_attempts = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  932. $sql = "SELECT question_id FROM $track_attempts
  933. WHERE exe_id = $exeId
  934. ORDER BY tms DESC
  935. LIMIT 1";
  936. $result = Database::query($sql);
  937. if (Database::num_rows($result)) {
  938. $row = Database::fetch_array($result);
  939. return $row['question_id'];
  940. } else {
  941. return false;
  942. }
  943. }
  944. /**
  945. * Gets how many attempts exists by user, exercise, learning path.
  946. *
  947. * @param int user id
  948. * @param int exercise id
  949. * @param int lp id
  950. * @param int lp item id
  951. * @param int lp item view id
  952. *
  953. * @return int
  954. */
  955. public static function get_attempt_count(
  956. $user_id,
  957. $exerciseId,
  958. $lp_id,
  959. $lp_item_id,
  960. $lp_item_view_id
  961. ) {
  962. $stat_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  963. $user_id = (int) $user_id;
  964. $exerciseId = (int) $exerciseId;
  965. $lp_id = (int) $lp_id;
  966. $lp_item_id = (int) $lp_item_id;
  967. $lp_item_view_id = (int) $lp_item_view_id;
  968. $courseId = api_get_course_int_id();
  969. $sessionId = api_get_session_id();
  970. $sql = "SELECT count(*) as count
  971. FROM $stat_table
  972. WHERE
  973. exe_exo_id = $exerciseId AND
  974. exe_user_id = $user_id AND
  975. status != 'incomplete' AND
  976. orig_lp_id = $lp_id AND
  977. orig_lp_item_id = $lp_item_id AND
  978. orig_lp_item_view_id = $lp_item_view_id AND
  979. c_id = $courseId AND
  980. session_id = $sessionId";
  981. $query = Database::query($sql);
  982. if (Database::num_rows($query) > 0) {
  983. $attempt = Database::fetch_array($query, 'ASSOC');
  984. return $attempt['count'];
  985. } else {
  986. return 0;
  987. }
  988. }
  989. /**
  990. * @param $user_id
  991. * @param $exerciseId
  992. * @param $lp_id
  993. * @param $lp_item_id
  994. *
  995. * @return int
  996. */
  997. public static function get_attempt_count_not_finished(
  998. $user_id,
  999. $exerciseId,
  1000. $lp_id,
  1001. $lp_item_id
  1002. ) {
  1003. $stat_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1004. $user_id = (int) $user_id;
  1005. $exerciseId = (int) $exerciseId;
  1006. $lp_id = (int) $lp_id;
  1007. $lp_item_id = (int) $lp_item_id;
  1008. //$lp_item_view_id = (int) $lp_item_view_id;
  1009. $courseId = api_get_course_int_id();
  1010. $sessionId = api_get_session_id();
  1011. $sql = "SELECT count(*) as count
  1012. FROM $stat_table
  1013. WHERE
  1014. exe_exo_id = $exerciseId AND
  1015. exe_user_id = $user_id AND
  1016. status != 'incomplete' AND
  1017. orig_lp_id = $lp_id AND
  1018. orig_lp_item_id = $lp_item_id AND
  1019. c_id = $courseId AND
  1020. session_id = $sessionId";
  1021. $query = Database::query($sql);
  1022. if (Database::num_rows($query) > 0) {
  1023. $attempt = Database::fetch_array($query, 'ASSOC');
  1024. return $attempt['count'];
  1025. } else {
  1026. return 0;
  1027. }
  1028. }
  1029. /**
  1030. * @param int $user_id
  1031. * @param int $lp_id
  1032. * @param array $course
  1033. * @param int $session_id
  1034. *
  1035. * @return bool
  1036. */
  1037. public static function delete_student_lp_events(
  1038. $user_id,
  1039. $lp_id,
  1040. $course,
  1041. $session_id
  1042. ) {
  1043. $lp_view_table = Database::get_course_table(TABLE_LP_VIEW);
  1044. $lp_item_view_table = Database::get_course_table(TABLE_LP_ITEM_VIEW);
  1045. $lpInteraction = Database::get_course_table(TABLE_LP_IV_INTERACTION);
  1046. $lpObjective = Database::get_course_table(TABLE_LP_IV_OBJECTIVE);
  1047. if (empty($course)) {
  1048. return false;
  1049. }
  1050. $course_id = $course['real_id'];
  1051. $user_id = (int) $user_id;
  1052. $lp_id = (int) $lp_id;
  1053. $session_id = (int) $session_id;
  1054. if (empty($course_id)) {
  1055. $course_id = api_get_course_int_id();
  1056. }
  1057. $track_e_exercises = Database::get_main_table(
  1058. TABLE_STATISTIC_TRACK_E_EXERCISES
  1059. );
  1060. $track_attempts = Database::get_main_table(
  1061. TABLE_STATISTIC_TRACK_E_ATTEMPT
  1062. );
  1063. $recording_table = Database::get_main_table(
  1064. TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING
  1065. );
  1066. //Make sure we have the exact lp_view_id
  1067. $sql = "SELECT id FROM $lp_view_table
  1068. WHERE
  1069. c_id = $course_id AND
  1070. user_id = $user_id AND
  1071. lp_id = $lp_id AND
  1072. session_id = $session_id";
  1073. $result = Database::query($sql);
  1074. if (Database::num_rows($result)) {
  1075. $view = Database::fetch_array($result, 'ASSOC');
  1076. $lp_view_id = $view['id'];
  1077. $sql = "DELETE FROM $lp_item_view_table
  1078. WHERE c_id = $course_id AND lp_view_id = $lp_view_id";
  1079. Database::query($sql);
  1080. $sql = "DELETE FROM $lpInteraction
  1081. WHERE c_id = $course_id AND lp_iv_id = $lp_view_id";
  1082. Database::query($sql);
  1083. $sql = "DELETE FROM $lpObjective
  1084. WHERE c_id = $course_id AND lp_iv_id = $lp_view_id";
  1085. Database::query($sql);
  1086. }
  1087. if (api_get_configuration_value('lp_minimum_time')) {
  1088. $sql = "DELETE FROM track_e_access_complete
  1089. WHERE
  1090. tool = 'learnpath' AND
  1091. c_id = $course_id AND
  1092. tool_id = $lp_id AND
  1093. user_id = $user_id AND
  1094. session_id = $session_id
  1095. ";
  1096. Database::query($sql);
  1097. }
  1098. $sql = "DELETE FROM $lp_view_table
  1099. WHERE
  1100. c_id = $course_id AND
  1101. user_id = $user_id AND
  1102. lp_id= $lp_id AND
  1103. session_id = $session_id
  1104. ";
  1105. Database::query($sql);
  1106. $sql = "SELECT exe_id FROM $track_e_exercises
  1107. WHERE
  1108. exe_user_id = $user_id AND
  1109. session_id = $session_id AND
  1110. c_id = $course_id AND
  1111. orig_lp_id = $lp_id";
  1112. $result = Database::query($sql);
  1113. $exe_list = [];
  1114. while ($row = Database::fetch_array($result, 'ASSOC')) {
  1115. $exe_list[] = $row['exe_id'];
  1116. }
  1117. if (!empty($exe_list) && is_array($exe_list) && count($exe_list) > 0) {
  1118. $exeListString = implode(',', $exe_list);
  1119. $sql = "DELETE FROM $track_e_exercises
  1120. WHERE exe_id IN ($exeListString)";
  1121. Database::query($sql);
  1122. $sql = "DELETE FROM $track_attempts
  1123. WHERE exe_id IN ($exeListString)";
  1124. Database::query($sql);
  1125. $sql = "DELETE FROM $recording_table
  1126. WHERE exe_id IN ($exeListString)";
  1127. Database::query($sql);
  1128. }
  1129. self::addEvent(
  1130. LOG_LP_ATTEMPT_DELETE,
  1131. LOG_LP_ID,
  1132. $lp_id,
  1133. null,
  1134. null,
  1135. $course_id,
  1136. $session_id
  1137. );
  1138. return true;
  1139. }
  1140. /**
  1141. * Delete all exercise attempts (included in LP or not).
  1142. *
  1143. * @param int user id
  1144. * @param int exercise id
  1145. * @param int $course_id
  1146. * @param int session id
  1147. */
  1148. public static function delete_all_incomplete_attempts(
  1149. $user_id,
  1150. $exercise_id,
  1151. $course_id,
  1152. $session_id = 0
  1153. ) {
  1154. $user_id = (int) $user_id;
  1155. $exercise_id = (int) $exercise_id;
  1156. $course_id = (int) $course_id;
  1157. $session_id = (int) $session_id;
  1158. if (!empty($user_id) && !empty($exercise_id) && !empty($course_id)) {
  1159. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1160. $sql = "DELETE FROM $table
  1161. WHERE
  1162. exe_user_id = $user_id AND
  1163. exe_exo_id = $exercise_id AND
  1164. c_id = $course_id AND
  1165. session_id = $session_id AND
  1166. status = 'incomplete' ";
  1167. Database::query($sql);
  1168. self::addEvent(
  1169. LOG_EXERCISE_RESULT_DELETE,
  1170. LOG_EXERCISE_AND_USER_ID,
  1171. $exercise_id.'-'.$user_id,
  1172. null,
  1173. null,
  1174. $course_id,
  1175. $session_id
  1176. );
  1177. }
  1178. }
  1179. /**
  1180. * Gets all exercise results (NO Exercises in LPs ) from a given exercise id, course, session.
  1181. *
  1182. * @param int $exercise_id
  1183. * @param int $courseId
  1184. * @param int $session_id
  1185. *
  1186. * @return array with the results
  1187. */
  1188. public static function get_all_exercise_results(
  1189. $exercise_id,
  1190. $courseId,
  1191. $session_id = 0,
  1192. $load_question_list = true,
  1193. $user_id = null
  1194. ) {
  1195. $TABLETRACK_EXERCICES = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1196. $TBL_TRACK_ATTEMPT = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1197. $courseId = (int) $courseId;
  1198. $exercise_id = (int) $exercise_id;
  1199. $session_id = (int) $session_id;
  1200. $user_condition = null;
  1201. if (!empty($user_id)) {
  1202. $user_id = (int) $user_id;
  1203. $user_condition = "AND exe_user_id = $user_id ";
  1204. }
  1205. $sql = "SELECT * FROM $TABLETRACK_EXERCICES
  1206. WHERE
  1207. status = '' AND
  1208. c_id = $courseId AND
  1209. exe_exo_id = $exercise_id AND
  1210. session_id = $session_id AND
  1211. orig_lp_id =0 AND
  1212. orig_lp_item_id = 0
  1213. $user_condition
  1214. ORDER BY exe_id";
  1215. $res = Database::query($sql);
  1216. $list = [];
  1217. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1218. $list[$row['exe_id']] = $row;
  1219. if ($load_question_list) {
  1220. $sql = "SELECT * FROM $TBL_TRACK_ATTEMPT
  1221. WHERE exe_id = {$row['exe_id']}";
  1222. $res_question = Database::query($sql);
  1223. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  1224. $list[$row['exe_id']]['question_list'][$row_q['question_id']] = $row_q;
  1225. }
  1226. }
  1227. }
  1228. return $list;
  1229. }
  1230. /**
  1231. * Gets all exercise results (NO Exercises in LPs ) from a given exercise id, course, session.
  1232. *
  1233. * @param int $courseId
  1234. * @param int $session_id
  1235. * @param bool $get_count
  1236. *
  1237. * @return array with the results
  1238. */
  1239. public static function get_all_exercise_results_by_course(
  1240. $courseId,
  1241. $session_id = 0,
  1242. $get_count = true
  1243. ) {
  1244. $table_track_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1245. $courseId = (int) $courseId;
  1246. $session_id = (int) $session_id;
  1247. $select = '*';
  1248. if ($get_count) {
  1249. $select = 'count(*) as count';
  1250. }
  1251. $sql = "SELECT $select FROM $table_track_exercises
  1252. WHERE status = '' AND
  1253. c_id = $courseId AND
  1254. session_id = $session_id AND
  1255. orig_lp_id = 0 AND
  1256. orig_lp_item_id = 0
  1257. ORDER BY exe_id";
  1258. $res = Database::query($sql);
  1259. if ($get_count) {
  1260. $row = Database::fetch_array($res, 'ASSOC');
  1261. return $row['count'];
  1262. } else {
  1263. $list = [];
  1264. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1265. $list[$row['exe_id']] = $row;
  1266. }
  1267. return $list;
  1268. }
  1269. }
  1270. /**
  1271. * Gets all exercise results (NO Exercises in LPs) from a given exercise id, course, session.
  1272. *
  1273. * @param int $user_id
  1274. * @param int $courseId
  1275. * @param int $session_id
  1276. *
  1277. * @return array with the results
  1278. */
  1279. public static function get_all_exercise_results_by_user(
  1280. $user_id,
  1281. $courseId,
  1282. $session_id = 0
  1283. ) {
  1284. $table_track_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1285. $table_track_attempt = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1286. $courseId = (int) $courseId;
  1287. $session_id = (int) $session_id;
  1288. $user_id = (int) $user_id;
  1289. $sql = "SELECT * FROM $table_track_exercises
  1290. WHERE
  1291. status = '' AND
  1292. exe_user_id = $user_id AND
  1293. c_id = $courseId AND
  1294. session_id = $session_id AND
  1295. orig_lp_id = 0 AND
  1296. orig_lp_item_id = 0
  1297. ORDER by exe_id";
  1298. $res = Database::query($sql);
  1299. $list = [];
  1300. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1301. $list[$row['exe_id']] = $row;
  1302. $sql = "SELECT * FROM $table_track_attempt
  1303. WHERE exe_id = {$row['exe_id']}";
  1304. $res_question = Database::query($sql);
  1305. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  1306. $list[$row['exe_id']]['question_list'][$row_q['question_id']] = $row_q;
  1307. }
  1308. }
  1309. return $list;
  1310. }
  1311. /**
  1312. * Gets exercise results (NO Exercises in LPs) from a given exercise id, course, session.
  1313. *
  1314. * @param int $exe_id attempt id
  1315. * @param string $status
  1316. *
  1317. * @return array with the results
  1318. */
  1319. public static function get_exercise_results_by_attempt($exe_id, $status = null)
  1320. {
  1321. $table_track_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1322. $table_track_attempt = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1323. $table_track_attempt_recording = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
  1324. $exe_id = (int) $exe_id;
  1325. $status = Database::escape_string($status);
  1326. $sql = "SELECT * FROM $table_track_exercises
  1327. WHERE status = '$status' AND exe_id = $exe_id";
  1328. $res = Database::query($sql);
  1329. $list = [];
  1330. if (Database::num_rows($res)) {
  1331. $row = Database::fetch_array($res, 'ASSOC');
  1332. //Checking if this attempt was revised by a teacher
  1333. $sql_revised = "SELECT exe_id FROM $table_track_attempt_recording
  1334. WHERE author != '' AND exe_id = $exe_id
  1335. LIMIT 1";
  1336. $res_revised = Database::query($sql_revised);
  1337. $row['attempt_revised'] = 0;
  1338. if (Database::num_rows($res_revised) > 0) {
  1339. $row['attempt_revised'] = 1;
  1340. }
  1341. $list[$exe_id] = $row;
  1342. $sql = "SELECT * FROM $table_track_attempt
  1343. WHERE exe_id = $exe_id
  1344. ORDER BY tms ASC";
  1345. $res_question = Database::query($sql);
  1346. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  1347. $list[$exe_id]['question_list'][$row_q['question_id']] = $row_q;
  1348. }
  1349. }
  1350. return $list;
  1351. }
  1352. /**
  1353. * Gets exercise results (NO Exercises in LPs) from a given user, exercise id, course, session, lp_id, lp_item_id.
  1354. *
  1355. * @param int user id
  1356. * @param int exercise id
  1357. * @param string course code
  1358. * @param int session id
  1359. * @param int lp id
  1360. * @param int lp item id
  1361. * @param string order asc or desc
  1362. *
  1363. * @return array with the results
  1364. */
  1365. public static function getExerciseResultsByUser(
  1366. $user_id,
  1367. $exercise_id,
  1368. $courseId,
  1369. $session_id = 0,
  1370. $lp_id = 0,
  1371. $lp_item_id = 0,
  1372. $order = null
  1373. ) {
  1374. $table_track_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1375. $table_track_attempt = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1376. $table_track_attempt_recording = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
  1377. $courseId = (int) $courseId;
  1378. $exercise_id = (int) $exercise_id;
  1379. $session_id = (int) $session_id;
  1380. $user_id = (int) $user_id;
  1381. $lp_id = (int) $lp_id;
  1382. $lp_item_id = (int) $lp_item_id;
  1383. if (!in_array(strtolower($order), ['asc', 'desc'])) {
  1384. $order = 'asc';
  1385. }
  1386. $sql = "SELECT * FROM $table_track_exercises
  1387. WHERE
  1388. status = '' AND
  1389. exe_user_id = $user_id AND
  1390. c_id = $courseId AND
  1391. exe_exo_id = $exercise_id AND
  1392. session_id = $session_id AND
  1393. orig_lp_id = $lp_id AND
  1394. orig_lp_item_id = $lp_item_id
  1395. ORDER by exe_id $order ";
  1396. $res = Database::query($sql);
  1397. $list = [];
  1398. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1399. // Checking if this attempt was revised by a teacher
  1400. $exeId = $row['exe_id'];
  1401. $sql = "SELECT exe_id FROM $table_track_attempt_recording
  1402. WHERE author != '' AND exe_id = $exeId
  1403. LIMIT 1";
  1404. $res_revised = Database::query($sql);
  1405. $row['attempt_revised'] = 0;
  1406. if (Database::num_rows($res_revised) > 0) {
  1407. $row['attempt_revised'] = 1;
  1408. }
  1409. $list[$row['exe_id']] = $row;
  1410. $sql = "SELECT * FROM $table_track_attempt
  1411. WHERE exe_id = $exeId";
  1412. $res_question = Database::query($sql);
  1413. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  1414. $list[$row['exe_id']]['question_list'][$row_q['question_id']][] = $row_q;
  1415. }
  1416. }
  1417. return $list;
  1418. }
  1419. /**
  1420. * Count exercise attempts (NO Exercises in LPs ) from a given exercise id, course, session.
  1421. *
  1422. * @param int $user_id
  1423. * @param int $exercise_id
  1424. * @param int $courseId
  1425. * @param int $session_id
  1426. *
  1427. * @return array with the results
  1428. */
  1429. public static function count_exercise_attempts_by_user(
  1430. $user_id,
  1431. $exercise_id,
  1432. $courseId,
  1433. $session_id = 0
  1434. ) {
  1435. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1436. $courseId = (int) $courseId;
  1437. $exercise_id = (int) $exercise_id;
  1438. $session_id = (int) $session_id;
  1439. $user_id = (int) $user_id;
  1440. $sql = "SELECT count(*) as count
  1441. FROM $table
  1442. WHERE status = '' AND
  1443. exe_user_id = $user_id AND
  1444. c_id = $courseId AND
  1445. exe_exo_id = $exercise_id AND
  1446. session_id = $session_id AND
  1447. orig_lp_id =0 AND
  1448. orig_lp_item_id = 0
  1449. ORDER BY exe_id";
  1450. $res = Database::query($sql);
  1451. $result = 0;
  1452. if (Database::num_rows($res) > 0) {
  1453. $row = Database::fetch_array($res, 'ASSOC');
  1454. $result = $row['count'];
  1455. }
  1456. return $result;
  1457. }
  1458. /**
  1459. * Gets all exercise BEST results attempts (NO Exercises in LPs)
  1460. * from a given exercise id, course, session per user.
  1461. *
  1462. * @param int $exercise_id
  1463. * @param int $courseId
  1464. * @param int $session_id
  1465. * @param int $userId
  1466. *
  1467. * @return array with the results
  1468. *
  1469. * @todo rename this function
  1470. */
  1471. public static function get_best_exercise_results_by_user(
  1472. $exercise_id,
  1473. $courseId,
  1474. $session_id = 0,
  1475. $userId = 0
  1476. ) {
  1477. $table_track_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1478. $table_track_attempt = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1479. $courseId = (int) $courseId;
  1480. $exercise_id = (int) $exercise_id;
  1481. $session_id = (int) $session_id;
  1482. $sql = "SELECT * FROM $table_track_exercises
  1483. WHERE
  1484. status = '' AND
  1485. c_id = $courseId AND
  1486. exe_exo_id = $exercise_id AND
  1487. session_id = $session_id AND
  1488. orig_lp_id = 0 AND
  1489. orig_lp_item_id = 0";
  1490. if (!empty($userId)) {
  1491. $userId = (int) $userId;
  1492. $sql .= " AND exe_user_id = $userId ";
  1493. }
  1494. $sql .= " ORDER BY exe_id";
  1495. $res = Database::query($sql);
  1496. $list = [];
  1497. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1498. $list[$row['exe_id']] = $row;
  1499. $exeId = $row['exe_id'];
  1500. $sql = "SELECT * FROM $table_track_attempt
  1501. WHERE exe_id = $exeId";
  1502. $res_question = Database::query($sql);
  1503. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  1504. $list[$exeId]['question_list'][$row_q['question_id']] = $row_q;
  1505. }
  1506. }
  1507. // Getting the best results of every student
  1508. $best_score_return = [];
  1509. foreach ($list as $student_result) {
  1510. $user_id = $student_result['exe_user_id'];
  1511. $current_best_score[$user_id] = $student_result['exe_result'];
  1512. //echo $current_best_score[$user_id].' - '.$best_score_return[$user_id]['exe_result'].'<br />';
  1513. if (!isset($best_score_return[$user_id]['exe_result'])) {
  1514. $best_score_return[$user_id] = $student_result;
  1515. }
  1516. if ($current_best_score[$user_id] > $best_score_return[$user_id]['exe_result']) {
  1517. $best_score_return[$user_id] = $student_result;
  1518. }
  1519. }
  1520. return $best_score_return;
  1521. }
  1522. /**
  1523. * @param int $user_id
  1524. * @param int $exercise_id
  1525. * @param int $courseId
  1526. * @param int $session_id
  1527. *
  1528. * @return array
  1529. */
  1530. public static function get_best_attempt_exercise_results_per_user(
  1531. $user_id,
  1532. $exercise_id,
  1533. $courseId,
  1534. $session_id = 0
  1535. ) {
  1536. $table_track_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1537. $courseId = (int) $courseId;
  1538. $exercise_id = (int) $exercise_id;
  1539. $session_id = (int) $session_id;
  1540. $user_id = (int) $user_id;
  1541. $sql = "SELECT * FROM $table_track_exercises
  1542. WHERE
  1543. status = '' AND
  1544. c_id = $courseId AND
  1545. exe_exo_id = $exercise_id AND
  1546. session_id = $session_id AND
  1547. exe_user_id = $user_id AND
  1548. orig_lp_id = 0 AND
  1549. orig_lp_item_id = 0
  1550. ORDER BY exe_id";
  1551. $res = Database::query($sql);
  1552. $list = [];
  1553. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1554. $list[$row['exe_id']] = $row;
  1555. }
  1556. //Getting the best results of every student
  1557. $best_score_return = [];
  1558. $best_score_return['exe_result'] = 0;
  1559. foreach ($list as $result) {
  1560. $current_best_score = $result;
  1561. if ($current_best_score['exe_result'] > $best_score_return['exe_result']) {
  1562. $best_score_return = $result;
  1563. }
  1564. }
  1565. if (!isset($best_score_return['exe_weighting'])) {
  1566. $best_score_return = [];
  1567. }
  1568. return $best_score_return;
  1569. }
  1570. /**
  1571. * @param int $exercise_id
  1572. * @param int $courseId
  1573. * @param int $session_id
  1574. *
  1575. * @return mixed
  1576. */
  1577. public static function count_exercise_result_not_validated(
  1578. $exercise_id,
  1579. $courseId,
  1580. $session_id = 0
  1581. ) {
  1582. $table_track_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1583. $table_track_attempt = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
  1584. $courseId = (int) $courseId;
  1585. $session_id = (int) $session_id;
  1586. $exercise_id = (int) $exercise_id;
  1587. $sql = "SELECT count(e.exe_id) as count
  1588. FROM $table_track_exercises e
  1589. LEFT JOIN $table_track_attempt a
  1590. ON e.exe_id = a.exe_id
  1591. WHERE
  1592. exe_exo_id = $exercise_id AND
  1593. c_id = $courseId AND
  1594. e.session_id = $session_id AND
  1595. orig_lp_id = 0 AND
  1596. marks IS NULL AND
  1597. status = '' AND
  1598. orig_lp_item_id = 0
  1599. ORDER BY e.exe_id";
  1600. $res = Database::query($sql);
  1601. $row = Database::fetch_array($res, 'ASSOC');
  1602. return $row['count'];
  1603. }
  1604. /**
  1605. * Gets all exercise BEST results attempts (NO Exercises in LPs)
  1606. * from a given exercise id, course, session per user.
  1607. *
  1608. * @param int exercise id
  1609. * @param int course id
  1610. * @param int session id
  1611. *
  1612. * @return array with the results
  1613. */
  1614. public static function get_count_exercises_attempted_by_course(
  1615. $courseId,
  1616. $session_id = 0
  1617. ) {
  1618. $table_track_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1619. $courseId = (int) $courseId;
  1620. $session_id = (int) $session_id;
  1621. $sql = "SELECT DISTINCT exe_exo_id, exe_user_id
  1622. FROM $table_track_exercises
  1623. WHERE
  1624. status = '' AND
  1625. c_id = $courseId AND
  1626. session_id = $session_id AND
  1627. orig_lp_id = 0 AND
  1628. orig_lp_item_id = 0
  1629. ORDER BY exe_id";
  1630. $res = Database::query($sql);
  1631. $count = 0;
  1632. if (Database::num_rows($res) > 0) {
  1633. $count = Database::num_rows($res);
  1634. }
  1635. return $count;
  1636. }
  1637. /**
  1638. * Gets all exercise events from a Learning Path within a Course nd Session.
  1639. *
  1640. * @param int $exercise_id
  1641. * @param int $courseId
  1642. * @param int $session_id
  1643. *
  1644. * @return array
  1645. */
  1646. public static function get_all_exercise_event_from_lp(
  1647. $exercise_id,
  1648. $courseId,
  1649. $session_id = 0
  1650. ) {
  1651. $table_track_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1652. $table_track_attempt = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1653. $courseId = (int) $courseId;
  1654. $exercise_id = (int) $exercise_id;
  1655. $session_id = (int) $session_id;
  1656. $sql = "SELECT * FROM $table_track_exercises
  1657. WHERE
  1658. status = '' AND
  1659. c_id = $courseId AND
  1660. exe_exo_id = $exercise_id AND
  1661. session_id = $session_id AND
  1662. orig_lp_id !=0 AND
  1663. orig_lp_item_id != 0";
  1664. $res = Database::query($sql);
  1665. $list = [];
  1666. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1667. $exeId = $row['exe_id'];
  1668. $list[$exeId] = $row;
  1669. $sql = "SELECT * FROM $table_track_attempt
  1670. WHERE exe_id = $exeId";
  1671. $res_question = Database::query($sql);
  1672. while ($row_q = Database::fetch_array($res_question, 'ASSOC')) {
  1673. $list[$exeId]['question_list'][$row_q['question_id']] = $row_q;
  1674. }
  1675. }
  1676. return $list;
  1677. }
  1678. /**
  1679. * Get a list of all the exercises in a given learning path.
  1680. *
  1681. * @param int $lp_id
  1682. * @param int $course_id This parameter is probably deprecated as lp_id now is a global iid
  1683. *
  1684. * @return array
  1685. */
  1686. public static function get_all_exercises_from_lp($lp_id, $course_id)
  1687. {
  1688. $lp_item_table = Database::get_course_table(TABLE_LP_ITEM);
  1689. $course_id = (int) $course_id;
  1690. $lp_id = (int) $lp_id;
  1691. $sql = "SELECT * FROM $lp_item_table
  1692. WHERE
  1693. c_id = $course_id AND
  1694. lp_id = $lp_id AND
  1695. item_type = 'quiz'
  1696. ORDER BY parent_item_id, display_order";
  1697. $res = Database::query($sql);
  1698. $my_exercise_list = [];
  1699. while ($row = Database::fetch_array($res, 'ASSOC')) {
  1700. $my_exercise_list[] = $row;
  1701. }
  1702. return $my_exercise_list;
  1703. }
  1704. /**
  1705. * This function gets the comments of an exercise.
  1706. *
  1707. * @param int $exe_id
  1708. * @param int $question_id
  1709. *
  1710. * @return string the comment
  1711. */
  1712. public static function get_comments($exe_id, $question_id)
  1713. {
  1714. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1715. $exe_id = (int) $exe_id;
  1716. $question_id = (int) $question_id;
  1717. $sql = "SELECT teacher_comment
  1718. FROM $table
  1719. WHERE
  1720. exe_id = $exe_id AND
  1721. question_id = $question_id
  1722. ORDER by question_id";
  1723. $sqlres = Database::query($sql);
  1724. $comm = strval(Database::result($sqlres, 0, 'teacher_comment'));
  1725. $comm = trim($comm);
  1726. return $comm;
  1727. }
  1728. /**
  1729. * @param int $exeId
  1730. *
  1731. * @return array
  1732. */
  1733. public static function getAllExerciseEventByExeId($exeId)
  1734. {
  1735. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1736. $exeId = (int) $exeId;
  1737. $sql = "SELECT * FROM $table
  1738. WHERE exe_id = $exeId
  1739. ORDER BY position";
  1740. $res_question = Database::query($sql);
  1741. $list = [];
  1742. if (Database::num_rows($res_question)) {
  1743. while ($row = Database::fetch_array($res_question, 'ASSOC')) {
  1744. $list[$row['question_id']][] = $row;
  1745. }
  1746. }
  1747. return $list;
  1748. }
  1749. /**
  1750. * @param int $exeId
  1751. * @param int $user_id
  1752. * @param int $courseId
  1753. * @param int $session_id
  1754. * @param int $question_id
  1755. */
  1756. public static function delete_attempt(
  1757. $exeId,
  1758. $user_id,
  1759. $courseId,
  1760. $session_id,
  1761. $question_id
  1762. ) {
  1763. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1764. $exeId = (int) $exeId;
  1765. $user_id = (int) $user_id;
  1766. $courseId = (int) $courseId;
  1767. $session_id = (int) $session_id;
  1768. $question_id = (int) $question_id;
  1769. $sql = "DELETE FROM $table
  1770. WHERE
  1771. exe_id = $exeId AND
  1772. user_id = $user_id AND
  1773. c_id = $courseId AND
  1774. session_id = $session_id AND
  1775. question_id = $question_id ";
  1776. Database::query($sql);
  1777. self::addEvent(
  1778. LOG_QUESTION_RESULT_DELETE,
  1779. LOG_EXERCISE_ATTEMPT_QUESTION_ID,
  1780. $exeId.'-'.$question_id,
  1781. null,
  1782. null,
  1783. $courseId,
  1784. $session_id
  1785. );
  1786. }
  1787. /**
  1788. * @param $exeId
  1789. * @param $user_id
  1790. * @param int $courseId
  1791. * @param $question_id
  1792. * @param int $sessionId
  1793. */
  1794. public static function delete_attempt_hotspot(
  1795. $exeId,
  1796. $user_id,
  1797. $courseId,
  1798. $question_id,
  1799. $sessionId = null
  1800. ) {
  1801. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_HOTSPOT);
  1802. $exeId = (int) $exeId;
  1803. $user_id = (int) $user_id;
  1804. $courseId = (int) $courseId;
  1805. $question_id = (int) $question_id;
  1806. if (!isset($sessionId)) {
  1807. $sessionId = api_get_session_id();
  1808. }
  1809. $sql = "DELETE FROM $table
  1810. WHERE
  1811. hotspot_exe_id = $exeId AND
  1812. hotspot_user_id = $user_id AND
  1813. c_id = $courseId AND
  1814. hotspot_question_id = $question_id ";
  1815. Database::query($sql);
  1816. self::addEvent(
  1817. LOG_QUESTION_RESULT_DELETE,
  1818. LOG_EXERCISE_ATTEMPT_QUESTION_ID,
  1819. $exeId.'-'.$question_id,
  1820. null,
  1821. null,
  1822. $courseId,
  1823. $sessionId
  1824. );
  1825. }
  1826. /**
  1827. * Registers in track_e_course_access when user logs in for the first time to a course.
  1828. *
  1829. * @param int $courseId ID of the course
  1830. * @param int $user_id ID of the user
  1831. * @param int $sessionId ID of the session (if any)
  1832. *
  1833. * @return bool
  1834. */
  1835. public static function eventCourseLogin($courseId, $user_id, $sessionId)
  1836. {
  1837. if (Session::read('login_as')) {
  1838. return false;
  1839. }
  1840. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  1841. $loginDate = $logoutDate = api_get_utc_datetime();
  1842. // $counter represents the number of time this record has been refreshed
  1843. $counter = 1;
  1844. $courseId = (int) $courseId;
  1845. $user_id = (int) $user_id;
  1846. $sessionId = (int) $sessionId;
  1847. $ip = Database::escape_string(api_get_real_ip());
  1848. $sql = "INSERT INTO $table(c_id, user_ip, user_id, login_course_date, logout_course_date, counter, session_id)
  1849. VALUES($courseId, '$ip', $user_id, '$loginDate', '$logoutDate', $counter, $sessionId)";
  1850. $courseAccessId = Database::query($sql);
  1851. if ($courseAccessId) {
  1852. // Course catalog stats modifications see #4191
  1853. CourseManager::update_course_ranking(
  1854. null,
  1855. null,
  1856. null,
  1857. null,
  1858. true,
  1859. false
  1860. );
  1861. return true;
  1862. }
  1863. }
  1864. /**
  1865. * Updates the user - course - session every X minutes
  1866. * In order to avoid.
  1867. *
  1868. * @param int $courseId
  1869. * @param int $userId
  1870. * @param int $sessionId
  1871. * @param int $minutes
  1872. *
  1873. * @return bool
  1874. */
  1875. public static function eventCourseLoginUpdate(
  1876. $courseId,
  1877. $userId,
  1878. $sessionId,
  1879. $minutes = 5
  1880. ) {
  1881. if (Session::read('login_as')) {
  1882. return false;
  1883. }
  1884. if (empty($courseId) || empty($userId)) {
  1885. return false;
  1886. }
  1887. $courseId = (int) $courseId;
  1888. $userId = (int) $userId;
  1889. $sessionId = (int) $sessionId;
  1890. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  1891. $sql = "SELECT course_access_id, logout_course_date
  1892. FROM $table
  1893. WHERE
  1894. c_id = $courseId AND
  1895. session_id = $sessionId AND
  1896. user_id = $userId
  1897. ORDER BY login_course_date DESC
  1898. LIMIT 1";
  1899. $result = Database::query($sql);
  1900. // Save every 5 minutes by default
  1901. $seconds = $minutes * 60;
  1902. $maxSeconds = 3600; // Only update if max diff is one hour
  1903. if (Database::num_rows($result)) {
  1904. $row = Database::fetch_array($result);
  1905. $id = $row['course_access_id'];
  1906. $logout = $row['logout_course_date'];
  1907. $now = time();
  1908. $logout = api_strtotime($logout, 'UTC');
  1909. if ($now - $logout > $seconds &&
  1910. $now - $logout < $maxSeconds
  1911. ) {
  1912. $now = api_get_utc_datetime();
  1913. $sql = "UPDATE $table SET
  1914. logout_course_date = '$now',
  1915. counter = counter + 1
  1916. WHERE course_access_id = $id";
  1917. Database::query($sql);
  1918. }
  1919. return true;
  1920. }
  1921. return false;
  1922. }
  1923. /**
  1924. * Register the logout of the course (usually when logging out of the platform)
  1925. * from the track_e_course_access table.
  1926. *
  1927. * @param array $logoutInfo Information stored by local.inc.php
  1928. * before new context ['uid'=> x, 'cid'=>y, 'sid'=>z]
  1929. *
  1930. * @return bool
  1931. */
  1932. public static function courseLogout($logoutInfo)
  1933. {
  1934. if (Session::read('login_as')) {
  1935. return false;
  1936. }
  1937. if (empty($logoutInfo['uid']) || empty($logoutInfo['cid'])) {
  1938. return false;
  1939. }
  1940. $sessionLifetime = api_get_configuration_value('session_lifetime');
  1941. /*
  1942. * When $_configuration['session_lifetime'] is larger than ~100 hours
  1943. * (in order to let users take exercises with no problems)
  1944. * the function Tracking::get_time_spent_on_the_course() returns larger values (200h) due the condition:
  1945. * login_course_date > now() - INTERVAL $session_lifetime SECOND
  1946. */
  1947. if (empty($sessionLifetime) || $sessionLifetime > 86400) {
  1948. $sessionLifetime = 3600; // 1 hour
  1949. }
  1950. if (!empty($logoutInfo) && !empty($logoutInfo['cid'])) {
  1951. $tableCourseAccess = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  1952. $userId = (int) $logoutInfo['uid'];
  1953. $courseId = (int) $logoutInfo['cid'];
  1954. $sessionId = 0;
  1955. if (!empty($logoutInfo['sid'])) {
  1956. $sessionId = (int) $logoutInfo['sid'];
  1957. }
  1958. $currentDate = api_get_utc_datetime();
  1959. // UTC time
  1960. $diff = time() - $sessionLifetime;
  1961. $time = api_get_utc_datetime($diff);
  1962. $sql = "SELECT course_access_id, logout_course_date
  1963. FROM $tableCourseAccess
  1964. WHERE
  1965. user_id = $userId AND
  1966. c_id = $courseId AND
  1967. session_id = $sessionId AND
  1968. login_course_date > '$time'
  1969. ORDER BY login_course_date DESC
  1970. LIMIT 1";
  1971. $result = Database::query($sql);
  1972. $insert = false;
  1973. if (Database::num_rows($result) > 0) {
  1974. $row = Database::fetch_array($result, 'ASSOC');
  1975. $courseAccessId = $row['course_access_id'];
  1976. $sql = "UPDATE $tableCourseAccess SET
  1977. logout_course_date = '$currentDate',
  1978. counter = counter + 1
  1979. WHERE course_access_id = $courseAccessId";
  1980. Database::query($sql);
  1981. } else {
  1982. $insert = true;
  1983. }
  1984. if ($insert) {
  1985. $ip = Database::escape_string(api_get_real_ip());
  1986. $sql = "INSERT INTO $tableCourseAccess (c_id, user_ip, user_id, login_course_date, logout_course_date, counter, session_id)
  1987. VALUES ($courseId, '$ip', $userId, '$currentDate', '$currentDate', 1, $sessionId)";
  1988. Database::query($sql);
  1989. }
  1990. return true;
  1991. }
  1992. }
  1993. /**
  1994. * Register a "fake" time spent on the platform, for example to match the
  1995. * estimated time he took to author an assignment/work, see configuration
  1996. * setting considered_working_time.
  1997. * This assumes there is already some connection of the student to the
  1998. * course, otherwise he wouldn't be able to upload an assignment.
  1999. * This works by creating a new record, copy of the current one, then
  2000. * updating the current one to be just the considered_working_time and
  2001. * end at the same second as the user connected to the course.
  2002. *
  2003. * @param int $courseId The course in which to add the time
  2004. * @param int $userId The user for whom to add the time
  2005. * @param int $sessionId The session in which to add the time (if any)
  2006. * @param string $virtualTime The amount of time to be added,
  2007. * in a hh:mm:ss format. If int, we consider it is expressed in hours.
  2008. *
  2009. * @return true on successful insertion, false otherwise
  2010. */
  2011. public static function eventAddVirtualCourseTime(
  2012. $courseId,
  2013. $userId,
  2014. $sessionId,
  2015. $virtualTime = ''
  2016. ) {
  2017. $courseId = (int) $courseId;
  2018. $userId = (int) $userId;
  2019. $sessionId = (int) $sessionId;
  2020. $logoutDate = api_get_utc_datetime();
  2021. $loginDate = ChamiloApi::addOrSubTimeToDateTime(
  2022. $virtualTime,
  2023. $logoutDate,
  2024. false
  2025. );
  2026. $params = [
  2027. 'login_course_date' => $loginDate,
  2028. 'logout_course_date' => $logoutDate,
  2029. 'session_id' => $sessionId,
  2030. 'user_id' => $userId,
  2031. 'counter' => 0,
  2032. 'c_id' => $courseId,
  2033. 'user_ip' => api_get_real_ip(),
  2034. ];
  2035. $courseTrackingTable = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2036. Database::insert($courseTrackingTable, $params);
  2037. return true;
  2038. }
  2039. /**
  2040. * Removes a "fake" time spent on the platform, for example to match the
  2041. * estimated time he took to author an assignment/work, see configuration
  2042. * setting considered_working_time.
  2043. * This method should be called when something that generated a fake
  2044. * time record is removed. Given the database link is weak (no real
  2045. * relationship kept between the deleted item and this record), this
  2046. * method just looks for the latest record that has the same time as the
  2047. * item's fake time, is in the past and in this course+session. If such a
  2048. * record cannot be found, it doesn't do anything.
  2049. * The IP address is not considered a useful filter here.
  2050. *
  2051. * @param int $courseId The course in which to add the time
  2052. * @param int $userId The user for whom to add the time
  2053. * @param int $sessionId The session in which to add the time (if any)
  2054. * @param string $virtualTime The amount of time to be added, in a hh:mm:ss format. If int, we consider it is expressed in hours.
  2055. *
  2056. * @return true on successful removal, false otherwise
  2057. */
  2058. public static function eventRemoveVirtualCourseTime(
  2059. $courseId,
  2060. $userId,
  2061. $sessionId = 0,
  2062. $virtualTime = ''
  2063. ) {
  2064. if (empty($virtualTime)) {
  2065. return false;
  2066. }
  2067. $courseTrackingTable = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2068. $courseId = (int) $courseId;
  2069. $userId = (int) $userId;
  2070. $sessionId = (int) $sessionId;
  2071. // Change $virtualTime format from hh:mm:ss to hhmmss which is the
  2072. // format returned by SQL for a subtraction of two datetime values
  2073. // @todo make sure this is portable between DBMSes
  2074. if (preg_match('/:/', $virtualTime)) {
  2075. list($h, $m, $s) = preg_split('/:/', $virtualTime);
  2076. $virtualTime = $h * 3600 + $m * 60 + $s;
  2077. } else {
  2078. $virtualTime *= 3600;
  2079. }
  2080. // Get the current latest course connection register. We need that
  2081. // record to re-use the data and create a new record.
  2082. $sql = "SELECT course_access_id
  2083. FROM $courseTrackingTable
  2084. WHERE
  2085. user_id = $userId AND
  2086. c_id = $courseId AND
  2087. session_id = $sessionId AND
  2088. counter = 0 AND
  2089. (UNIX_TIMESTAMP(logout_course_date) - UNIX_TIMESTAMP(login_course_date)) = '$virtualTime'
  2090. ORDER BY login_course_date DESC LIMIT 0,1";
  2091. $result = Database::query($sql);
  2092. // Ignore if we didn't find any course connection record in the last
  2093. // hour. In this case it wouldn't be right to add a "fake" time record.
  2094. if (Database::num_rows($result) > 0) {
  2095. // Found the latest connection
  2096. $row = Database::fetch_row($result);
  2097. $courseAccessId = $row[0];
  2098. $sql = "DELETE FROM $courseTrackingTable
  2099. WHERE course_access_id = $courseAccessId";
  2100. $result = Database::query($sql);
  2101. return $result;
  2102. }
  2103. return false;
  2104. }
  2105. /**
  2106. * For the sake of genericity, this function is a switch.
  2107. * It's called by EventsDispatcher and fires the good function
  2108. * with the good require_once.
  2109. *
  2110. * @deprecated
  2111. *
  2112. * @param string $event_name
  2113. * @param array $params
  2114. */
  2115. public static function event_send_mail($event_name, $params)
  2116. {
  2117. EventsMail::send_mail($event_name, $params);
  2118. }
  2119. /**
  2120. * Internal function checking if the mail was already sent from that user to that user.
  2121. *
  2122. * @param string $event_name
  2123. * @param int $user_from
  2124. * @param int $user_to
  2125. *
  2126. * @return bool
  2127. */
  2128. public static function check_if_mail_already_sent(
  2129. $event_name,
  2130. $user_from,
  2131. $user_to = null
  2132. ) {
  2133. if ($user_to == null) {
  2134. $sql = 'SELECT COUNT(*) as total
  2135. FROM '.Database::get_main_table(TABLE_EVENT_SENT).'
  2136. WHERE
  2137. user_from = '.$user_from.' AND
  2138. event_type_name = "'.$event_name.'"';
  2139. } else {
  2140. $sql = 'SELECT COUNT(*) as total
  2141. FROM '.Database::get_main_table(TABLE_EVENT_SENT).'
  2142. WHERE
  2143. user_from = '.$user_from.' AND
  2144. user_to = '.$user_to.' AND
  2145. event_type_name = "'.$event_name.'"';
  2146. }
  2147. $result = Database::store_result(Database::query($sql), 'ASSOC');
  2148. return $result[0]["total"];
  2149. }
  2150. /**
  2151. * Filter EventEmailTemplate Filters see the main/inc/conf/events.conf.dist.php.
  2152. */
  2153. /**
  2154. * Basic template event message filter (to be used by other filters as default).
  2155. *
  2156. * @deprecated
  2157. *
  2158. * @param array $values (passing by reference) *
  2159. *
  2160. * @return bool True if everything is OK, false otherwise
  2161. */
  2162. public function event_send_mail_filter_func(&$values)
  2163. {
  2164. return true;
  2165. }
  2166. /**
  2167. * user_registration - send_mail filter.
  2168. *
  2169. * @deprecated
  2170. *
  2171. * @param array $values (passing by reference)
  2172. *
  2173. * @return bool True if everything is OK, false otherwise
  2174. */
  2175. public function user_registration_event_send_mail_filter_func(&$values)
  2176. {
  2177. $res = self::event_send_mail_filter_func($values);
  2178. // proper logic for this filter
  2179. return $res;
  2180. }
  2181. /**
  2182. * portal_homepage_edited - send_mail filter.
  2183. *
  2184. * @deprecated
  2185. *
  2186. * @param array $values (passing by reference)
  2187. *
  2188. * @return bool True if everything is OK, false otherwise
  2189. */
  2190. public function portal_homepage_edited_event_send_mail_filter_func(&$values)
  2191. {
  2192. $res = self::event_send_mail_filter_func($values);
  2193. // proper logic for this filter
  2194. return $res;
  2195. }
  2196. /**
  2197. * Register the logout of the course (usually when logging out of the platform)
  2198. * from the track_e_access_complete table.
  2199. *
  2200. * @param array $logInfo Information stored by local.inc.php
  2201. *
  2202. * @return bool
  2203. */
  2204. public static function registerLog($logInfo)
  2205. {
  2206. if (!Tracking::minimunTimeAvailable(api_get_session_id(), api_get_course_int_id())) {
  2207. return false;
  2208. }
  2209. $loginAs = (int) Session::read('login_as') === true;
  2210. $logInfo['user_id'] = api_get_user_id();
  2211. $logInfo['date_reg'] = api_get_utc_datetime();
  2212. $logInfo['tool'] = !empty($logInfo['tool']) ? $logInfo['tool'] : '';
  2213. $logInfo['tool_id'] = !empty($logInfo['tool_id']) ? (int) $logInfo['tool_id'] : 0;
  2214. $logInfo['tool_id_detail'] = !empty($logInfo['tool_id_detail']) ? (int) $logInfo['tool_id_detail'] : 0;
  2215. $logInfo['action'] = !empty($logInfo['action']) ? $logInfo['action'] : '';
  2216. $logInfo['action_details'] = !empty($logInfo['action_details']) ? $logInfo['action_details'] : '';
  2217. $logInfo['ip_user'] = api_get_real_ip();
  2218. $logInfo['user_agent'] = $_SERVER['HTTP_USER_AGENT'];
  2219. $logInfo['session_id'] = api_get_session_id();
  2220. $logInfo['c_id'] = api_get_course_int_id();
  2221. $logInfo['ch_sid'] = session_id();
  2222. $logInfo['login_as'] = $loginAs;
  2223. $logInfo['info'] = !empty($logInfo['info']) ? $logInfo['info'] : '';
  2224. $logInfo['url'] = $_SERVER['REQUEST_URI'];
  2225. $logInfo['current_id'] = Session::read('last_id', 0);
  2226. $id = Database::insert('track_e_access_complete', $logInfo);
  2227. if ($id && empty($logInfo['current_id'])) {
  2228. Session::write('last_id', $id);
  2229. }
  2230. return true;
  2231. }
  2232. }