myspace.lib.php 127 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756275727582759276027612762276327642765276627672768276927702771277227732774277527762777277827792780278127822783278427852786278727882789279027912792279327942795279627972798279928002801280228032804280528062807280828092810281128122813281428152816281728182819282028212822282328242825282628272828282928302831283228332834283528362837283828392840284128422843284428452846284728482849285028512852285328542855285628572858285928602861286228632864286528662867286828692870287128722873287428752876287728782879288028812882288328842885288628872888288928902891289228932894289528962897289828992900290129022903290429052906290729082909291029112912291329142915291629172918291929202921292229232924292529262927292829292930293129322933293429352936293729382939294029412942294329442945294629472948294929502951295229532954295529562957295829592960296129622963296429652966296729682969297029712972297329742975297629772978297929802981298229832984298529862987298829892990299129922993299429952996299729982999300030013002300330043005300630073008300930103011301230133014301530163017301830193020302130223023302430253026302730283029303030313032303330343035303630373038303930403041304230433044304530463047304830493050305130523053305430553056305730583059306030613062306330643065306630673068306930703071307230733074307530763077307830793080308130823083308430853086308730883089309030913092309330943095309630973098309931003101310231033104310531063107310831093110311131123113311431153116311731183119312031213122312331243125312631273128312931303131313231333134313531363137313831393140314131423143314431453146314731483149315031513152315331543155315631573158315931603161316231633164316531663167316831693170317131723173317431753176317731783179318031813182318331843185318631873188318931903191319231933194319531963197319831993200320132023203320432053206320732083209321032113212321332143215321632173218321932203221322232233224322532263227322832293230323132323233323432353236323732383239324032413242324332443245324632473248324932503251325232533254325532563257325832593260326132623263326432653266326732683269327032713272327332743275327632773278327932803281328232833284328532863287328832893290329132923293329432953296329732983299330033013302330333043305330633073308330933103311331233133314331533163317331833193320332133223323332433253326332733283329333033313332333333343335333633373338333933403341334233433344334533463347334833493350335133523353335433553356335733583359336033613362336333643365336633673368336933703371337233733374337533763377337833793380338133823383338433853386338733883389339033913392339333943395
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. use ChamiloSession as Session;
  4. use CpChart\Cache as pCache;
  5. use CpChart\Data as pData;
  6. use CpChart\Image as pImage;
  7. /**
  8. * Class MySpace.
  9. *
  10. * @package chamilo.reporting
  11. */
  12. class MySpace
  13. {
  14. /**
  15. * Get admin actions.
  16. *
  17. * @return string
  18. */
  19. public static function getAdminActions()
  20. {
  21. $actions = [
  22. [
  23. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=coaches',
  24. 'content' => get_lang('DisplayCoaches'),
  25. ],
  26. [
  27. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=user',
  28. 'content' => get_lang('DisplayUserOverview'),
  29. ],
  30. [
  31. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=session',
  32. 'content' => get_lang('DisplaySessionOverview'),
  33. ],
  34. [
  35. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=course',
  36. 'content' => get_lang('DisplayCourseOverview'),
  37. ],
  38. [
  39. 'url' => api_get_path(WEB_CODE_PATH).'tracking/question_course_report.php?view=admin',
  40. 'content' => get_lang('LPQuestionListResults'),
  41. ],
  42. [
  43. 'url' => api_get_path(WEB_CODE_PATH).'tracking/course_session_report.php?view=admin',
  44. 'content' => get_lang('LPExerciseResultsBySession'),
  45. ],
  46. [
  47. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=accessoverview',
  48. 'content' => get_lang('DisplayAccessOverview').' ('.get_lang('Beta').')',
  49. ],
  50. [
  51. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/exercise_category_report.php',
  52. 'content' => get_lang('ExerciseCategoryAllSessionsReport'),
  53. ],
  54. [
  55. 'url' => api_get_path(WEB_CODE_PATH).'mySpace/survey_report.php',
  56. 'content' => get_lang('SurveyReport'),
  57. ],
  58. ];
  59. return Display::actions($actions, null);
  60. }
  61. /**
  62. * @return string
  63. */
  64. public static function getTopMenu()
  65. {
  66. $menuItems = [];
  67. $menuItems[] = Display::url(
  68. Display::return_icon(
  69. 'statistics.png',
  70. get_lang('MyStats'),
  71. '',
  72. ICON_SIZE_MEDIUM
  73. ),
  74. api_get_path(WEB_CODE_PATH)."auth/my_progress.php"
  75. );
  76. $menuItems[] = Display::url(
  77. Display::return_icon(
  78. 'teacher.png',
  79. get_lang('TeacherInterface'),
  80. [],
  81. 32
  82. ),
  83. api_get_path(WEB_CODE_PATH).'mySpace/?view=teacher'
  84. );
  85. $menuItems[] = Display::url(
  86. Display::return_icon(
  87. 'star_na.png',
  88. get_lang('AdminInterface'),
  89. [],
  90. 32
  91. ),
  92. '#'
  93. );
  94. $menuItems[] = Display::url(
  95. Display::return_icon('quiz.png', get_lang('ExamTracking'), [], 32),
  96. api_get_path(WEB_CODE_PATH).'tracking/exams.php'
  97. );
  98. $menu = '';
  99. foreach ($menuItems as $item) {
  100. $menu .= $item;
  101. }
  102. $menu .= '<br />';
  103. return $menu;
  104. }
  105. /**
  106. * This function serves exporting data in CSV format.
  107. *
  108. * @param array $header the header labels
  109. * @param array $data the data array
  110. * @param string $file_name the name of the file which contains exported data
  111. *
  112. * @return string mixed Returns a message (string) if an error occurred
  113. */
  114. public function export_csv($header, $data, $file_name = 'export.csv')
  115. {
  116. $archive_path = api_get_path(SYS_ARCHIVE_PATH);
  117. $archive_url = api_get_path(WEB_CODE_PATH).'course_info/download.php?archive_path=&archive=';
  118. $message = '';
  119. if (!$open = fopen($archive_path.$file_name, 'w+')) {
  120. $message = get_lang('noOpen');
  121. } else {
  122. $info = '';
  123. foreach ($header as $value) {
  124. $info .= $value.';';
  125. }
  126. $info .= "\r\n";
  127. foreach ($data as $row) {
  128. foreach ($row as $value) {
  129. $info .= $value.';';
  130. }
  131. $info .= "\r\n";
  132. }
  133. fwrite($open, $info);
  134. fclose($open);
  135. @chmod($file_name, api_get_permissions_for_new_files());
  136. header("Location:".$archive_url.$file_name);
  137. exit;
  138. }
  139. return $message;
  140. }
  141. /**
  142. * Gets the connections to a course as an array of login and logout time.
  143. *
  144. * @param int $userId User id
  145. * @param array $courseInfo
  146. * @param int $sessionId Session id (optional, default = 0)
  147. *
  148. * @return array Connections
  149. */
  150. public static function get_connections_to_course(
  151. $userId,
  152. $courseInfo,
  153. $sessionId = 0
  154. ) {
  155. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  156. // protect data
  157. $userId = (int) $userId;
  158. $courseId = (int) $courseInfo['real_id'];
  159. $sessionId = (int) $sessionId;
  160. $sessionCondition = api_get_session_condition($sessionId);
  161. $sql = 'SELECT login_course_date, logout_course_date
  162. FROM '.$table.'
  163. WHERE
  164. user_id = '.$userId.' AND
  165. c_id = '.$courseId.'
  166. '.$sessionCondition.'
  167. ORDER BY login_course_date ASC';
  168. $rs = Database::query($sql);
  169. $connections = [];
  170. while ($row = Database::fetch_array($rs)) {
  171. $connections[] = [
  172. 'login' => $row['login_course_date'],
  173. 'logout' => $row['logout_course_date'],
  174. ];
  175. }
  176. return $connections;
  177. }
  178. /**
  179. * @param $user_id
  180. * @param $course_list
  181. * @param int $session_id
  182. *
  183. * @return array|bool
  184. */
  185. public static function get_connections_from_course_list(
  186. $user_id,
  187. $course_list,
  188. $session_id = 0
  189. ) {
  190. // Database table definitions
  191. $tbl_track_course = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  192. if (empty($course_list)) {
  193. return false;
  194. }
  195. // protect data
  196. $user_id = (int) $user_id;
  197. $session_id = (int) $session_id;
  198. $new_course_list = [];
  199. foreach ($course_list as $course_item) {
  200. $courseInfo = api_get_course_info($course_item['code']);
  201. if ($courseInfo) {
  202. $courseId = $courseInfo['real_id'];
  203. $new_course_list[] = '"'.$courseId.'"';
  204. }
  205. }
  206. $course_list = implode(', ', $new_course_list);
  207. if (empty($course_list)) {
  208. return false;
  209. }
  210. $sql = 'SELECT login_course_date, logout_course_date, c_id
  211. FROM '.$tbl_track_course.'
  212. WHERE
  213. user_id = '.$user_id.' AND
  214. c_id IN ('.$course_list.') AND
  215. session_id = '.$session_id.'
  216. ORDER BY login_course_date ASC';
  217. $rs = Database::query($sql);
  218. $connections = [];
  219. while ($row = Database::fetch_array($rs)) {
  220. $timestamp_login_date = api_strtotime($row['login_course_date'], 'UTC');
  221. $timestamp_logout_date = api_strtotime($row['logout_course_date'], 'UTC');
  222. $connections[] = [
  223. 'login' => $timestamp_login_date,
  224. 'logout' => $timestamp_logout_date,
  225. 'c_id' => $row['c_id'],
  226. ];
  227. }
  228. return $connections;
  229. }
  230. /**
  231. * Creates a small table in the last column of the table with the user overview.
  232. *
  233. * @param int $user_id the id of the user
  234. * @param array $url_params additional url parameters
  235. * @param array $row the row information (the other columns)
  236. *
  237. * @return string html code
  238. */
  239. public static function course_info_tracking_filter($user_id, $url_params, $row)
  240. {
  241. // the table header
  242. $return = '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  243. // database table definition
  244. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  245. $user_id = (int) $user_id;
  246. // getting all the courses of the user
  247. $sql = "SELECT * FROM $tbl_course_user
  248. WHERE
  249. user_id = '".$user_id."' AND
  250. relation_type <> ".COURSE_RELATION_TYPE_RRHH;
  251. $result = Database::query($sql);
  252. while ($row = Database::fetch_array($result)) {
  253. $courseInfo = api_get_course_info_by_id($row['c_id']);
  254. if (empty($courseInfo)) {
  255. continue;
  256. }
  257. $courseCode = $courseInfo['code'];
  258. $courseId = $courseInfo['real_id'];
  259. $return .= '<tr>';
  260. // course code
  261. $return .= ' <td width="157px" >'.cut($courseCode, 20, true).'</td>';
  262. // time spent in the course
  263. $return .= '<td><div>'.api_time_to_hms(Tracking::get_time_spent_on_the_course($user_id, $courseId)).'</div></td>';
  264. // student progress in course
  265. $return .= '<td><div>'.round(Tracking::get_avg_student_progress($user_id, $courseCode), 2).'</div></td>';
  266. // student score
  267. $avg_score = Tracking::get_avg_student_score($user_id, $courseCode);
  268. if (is_numeric($avg_score)) {
  269. $avg_score = round($avg_score, 2);
  270. } else {
  271. $avg_score = '-';
  272. }
  273. $return .= ' <td><div>'.$avg_score.'</div></td>';
  274. // student tes score
  275. //$return .= ' <td><div style="width:40px">'.round(Tracking::get_avg_student_exercise_score ($user_id, $courseCode),2).'%</div></td>';
  276. // student messages
  277. $return .= ' <td><div>'.Tracking::count_student_messages($user_id, $courseCode).'</div></td>';
  278. // student assignments
  279. $return .= ' <td><div>'.Tracking::count_student_assignments($user_id, $courseCode).'</div></td>';
  280. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  281. $exercises_results = self::exercises_results($user_id, $courseCode);
  282. $return .= ' <td width="105px"><div>'.(is_null($exercises_results['percentage']) ? '' : $exercises_results['score_obtained'].'/'.$exercises_results['score_possible'].' ( '.$exercises_results['percentage'].'% )').'</div></td>';
  283. $return .= ' <td><div>'.$exercises_results['questions_answered'].'</div></td>';
  284. $return .= ' <td><div>'.Tracking::get_last_connection_date_on_the_course($user_id, $courseInfo).'</div></td>';
  285. $return .= '<tr>';
  286. }
  287. $return .= '</table>';
  288. return $return;
  289. }
  290. /**
  291. * Creates a small table in the last column of the table with the user overview.
  292. *
  293. * @param int $user_id the id of the user
  294. *
  295. * @return array List course
  296. */
  297. public static function returnCourseTracking($user_id)
  298. {
  299. $user_id = (int) $user_id;
  300. if (empty($user_id)) {
  301. return [];
  302. }
  303. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  304. // getting all the courses of the user
  305. $sql = "SELECT * FROM $tbl_course_user
  306. WHERE
  307. user_id = $user_id AND
  308. relation_type <> ".COURSE_RELATION_TYPE_RRHH;
  309. $result = Database::query($sql);
  310. $list = [];
  311. while ($row = Database::fetch_array($result)) {
  312. $courseInfo = api_get_course_info_by_id($row['c_id']);
  313. $courseId = $courseInfo['real_id'];
  314. $courseCode = $courseInfo['code'];
  315. if (empty($courseInfo)) {
  316. continue;
  317. }
  318. $avg_score = Tracking::get_avg_student_score($user_id, $courseCode);
  319. if (is_numeric($avg_score)) {
  320. $avg_score = round($avg_score, 2);
  321. } else {
  322. $avg_score = '-';
  323. }
  324. // Student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  325. $exercisesResults = self::exercises_results($user_id, $courseCode);
  326. $resultToString = '';
  327. if (!is_null($exercisesResults['percentage'])) {
  328. $resultToString = $exercisesResults['score_obtained'].'/'.$exercisesResults['score_possible'].' ( '.$exercisesResults['percentage'].'% )';
  329. }
  330. $item = [
  331. 'code' => $courseInfo['code'],
  332. 'real_id' => $courseInfo['real_id'],
  333. 'title' => $courseInfo['title'],
  334. 'category' => $courseInfo['categoryName'],
  335. 'image_small' => $courseInfo['course_image'],
  336. 'image_large' => $courseInfo['course_image_large'],
  337. 'time_spent' => api_time_to_hms(Tracking::get_time_spent_on_the_course($user_id, $courseId)),
  338. 'student_progress' => round(Tracking::get_avg_student_progress($user_id, $courseCode)),
  339. 'student_score' => $avg_score,
  340. 'student_message' => Tracking::count_student_messages($user_id, $courseCode),
  341. 'student_assignments' => Tracking::count_student_assignments($user_id, $courseCode),
  342. 'student_exercises' => $resultToString,
  343. 'questions_answered' => $exercisesResults['questions_answered'],
  344. 'last_connection' => Tracking::get_last_connection_date_on_the_course($user_id, $courseInfo),
  345. ];
  346. $list[] = $item;
  347. }
  348. return $list;
  349. }
  350. /**
  351. * Display a sortable table that contains an overview off all the
  352. * reporting progress of all users and all courses the user is subscribed to.
  353. *
  354. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  355. * Alex Aragon <alex.aragon@beeznest.com>, BeezNest, Perú
  356. *
  357. * @version Chamilo 1.11.8
  358. *
  359. * @since April 2019
  360. */
  361. public static function returnTrackingUserOverviewFilter($user_id)
  362. {
  363. $tpl = new Template('', false, false, false, false, false, false);
  364. $userInfo = api_get_user_info($user_id);
  365. $avatar = UserManager::getUserPicture($user_id, USER_IMAGE_SIZE_SMALL);
  366. $user = [
  367. 'id' => $user_id,
  368. 'code_user' => $userInfo['official_code'],
  369. 'complete_name' => $userInfo['complete_name'],
  370. 'username' => $userInfo['username'],
  371. 'course' => self::returnCourseTracking($user_id),
  372. 'avatar' => $avatar,
  373. ];
  374. $tpl->assign('item', $user);
  375. $templateName = $tpl->get_template('my_space/partials/tracking_user_overview.tpl');
  376. $content = $tpl->fetch($templateName);
  377. return $content;
  378. }
  379. /**
  380. * Display a sortable table that contains an overview off all the
  381. * reporting progress of all users and all courses the user is subscribed to.
  382. *
  383. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  384. * Alex Aragon <alex.aragon@beeznest.com>, BeezNest, Perú
  385. *
  386. * @version Chamilo 1.11.8
  387. *
  388. * @since October 2008, Update April 2019
  389. */
  390. public static function display_tracking_user_overview()
  391. {
  392. self::display_user_overview_export_options();
  393. $params = ['view' => 'admin', 'display' => 'user'];
  394. $table = new SortableTable(
  395. 'tracking_user_overview',
  396. ['MySpace', 'get_number_of_users_tracking_overview'],
  397. ['MySpace', 'get_user_data_tracking_overview'],
  398. 0,
  399. 20,
  400. 'ASC',
  401. null, [
  402. 'class' => 'table table-transparent',
  403. ]
  404. );
  405. $table->additional_parameters = $params;
  406. $table->set_column_filter(0, ['MySpace', 'returnTrackingUserOverviewFilter']);
  407. $tableContent = $table->return_table();
  408. $tpl = new Template('', false, false, false, false, false, false);
  409. $tpl->assign('table', $tableContent);
  410. $templateName = $tpl->get_template('my_space/user_summary.tpl');
  411. $tpl->display($templateName);
  412. }
  413. /**
  414. * @param $export_csv
  415. */
  416. public static function display_tracking_coach_overview($export_csv)
  417. {
  418. if ($export_csv) {
  419. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  420. } else {
  421. $is_western_name_order = api_is_western_name_order();
  422. }
  423. $sort_by_first_name = api_sort_by_first_name();
  424. $tracking_column = isset($_GET['tracking_list_coaches_column']) ? $_GET['tracking_list_coaches_column'] : ($is_western_name_order xor $sort_by_first_name) ? 1 : 0;
  425. $tracking_direction = (isset($_GET['tracking_list_coaches_direction']) && in_array(strtoupper($_GET['tracking_list_coaches_direction']), ['ASC', 'DESC', 'ASCENDING', 'DESCENDING', '0', '1'])) ? $_GET['tracking_list_coaches_direction'] : 'DESC';
  426. // Prepare array for column order - when impossible, use some of user names.
  427. if ($is_western_name_order) {
  428. $order = [
  429. 0 => 'firstname',
  430. 1 => 'lastname',
  431. 2 => $sort_by_first_name ? 'firstname' : 'lastname',
  432. 3 => 'login_date',
  433. 4 => $sort_by_first_name ? 'firstname' : 'lastname',
  434. 5 => $sort_by_first_name ? 'firstname' : 'lastname',
  435. ];
  436. } else {
  437. $order = [
  438. 0 => 'lastname',
  439. 1 => 'firstname',
  440. 2 => $sort_by_first_name ? 'firstname' : 'lastname',
  441. 3 => 'login_date',
  442. 4 => $sort_by_first_name ? 'firstname' : 'lastname',
  443. 5 => $sort_by_first_name ? 'firstname' : 'lastname',
  444. ];
  445. }
  446. $table = new SortableTable(
  447. 'tracking_list_coaches_myspace',
  448. ['MySpace', 'count_coaches'],
  449. null,
  450. ($is_western_name_order xor $sort_by_first_name) ? 1 : 0
  451. );
  452. $parameters['view'] = 'admin';
  453. $table->set_additional_parameters($parameters);
  454. if ($is_western_name_order) {
  455. $table->set_header(0, get_lang('FirstName'), true);
  456. $table->set_header(1, get_lang('LastName'), true);
  457. } else {
  458. $table->set_header(0, get_lang('LastName'), true);
  459. $table->set_header(1, get_lang('FirstName'), true);
  460. }
  461. $table->set_header(2, get_lang('TimeSpentOnThePlatform'), false);
  462. $table->set_header(3, get_lang('LastConnexion'), false);
  463. $table->set_header(4, get_lang('NbStudents'), false);
  464. $table->set_header(5, get_lang('CountCours'), false);
  465. $table->set_header(6, get_lang('NumberOfSessions'), false);
  466. $table->set_header(7, get_lang('Sessions'), false);
  467. if ($is_western_name_order) {
  468. $csv_header[] = [
  469. get_lang('FirstName'),
  470. get_lang('LastName'),
  471. get_lang('TimeSpentOnThePlatform'),
  472. get_lang('LastConnexion'),
  473. get_lang('NbStudents'),
  474. get_lang('CountCours'),
  475. get_lang('NumberOfSessions'),
  476. ];
  477. } else {
  478. $csv_header[] = [
  479. get_lang('LastName'),
  480. get_lang('FirstName'),
  481. get_lang('TimeSpentOnThePlatform'),
  482. get_lang('LastConnexion'),
  483. get_lang('NbStudents'),
  484. get_lang('CountCours'),
  485. get_lang('NumberOfSessions'),
  486. ];
  487. }
  488. $tbl_track_login = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  489. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  490. $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  491. $tbl_sessions = Database::get_main_table(TABLE_MAIN_SESSION);
  492. $sqlCoachs = "SELECT DISTINCT
  493. scu.user_id as id_coach,
  494. u.id as user_id,
  495. lastname,
  496. firstname,
  497. MAX(login_date) as login_date
  498. FROM $tbl_user u, $tbl_session_course_user scu, $tbl_track_login
  499. WHERE
  500. scu.user_id = u.id AND scu.status=2 AND login_user_id=u.id
  501. GROUP BY user_id ";
  502. if (api_is_multiple_url_enabled()) {
  503. $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  504. $access_url_id = api_get_current_access_url_id();
  505. if ($access_url_id != -1) {
  506. $sqlCoachs = "SELECT DISTINCT
  507. scu.user_id as id_coach,
  508. u.id as user_id,
  509. lastname,
  510. firstname,
  511. MAX(login_date) as login_date
  512. FROM $tbl_user u,
  513. $tbl_session_course_user scu,
  514. $tbl_track_login ,
  515. $tbl_session_rel_access_url session_rel_url
  516. WHERE
  517. scu.user_id = u.id AND
  518. scu.status = 2 AND
  519. login_user_id = u.id AND
  520. access_url_id = $access_url_id AND
  521. session_rel_url.session_id = scu.session_id
  522. GROUP BY u.id";
  523. }
  524. }
  525. if (!empty($order[$tracking_column])) {
  526. $sqlCoachs .= " ORDER BY ".$order[$tracking_column]." ".$tracking_direction;
  527. }
  528. $result_coaches = Database::query($sqlCoachs);
  529. $global_coaches = [];
  530. while ($coach = Database::fetch_array($result_coaches)) {
  531. $global_coaches[$coach['user_id']] = $coach;
  532. }
  533. $sql_session_coach = "SELECT session.id_coach, u.id as user_id, lastname, firstname, MAX(login_date) as login_date
  534. FROM $tbl_user u , $tbl_sessions as session, $tbl_track_login
  535. WHERE id_coach = u.id AND login_user_id = u.id
  536. GROUP BY u.id
  537. ORDER BY login_date $tracking_direction";
  538. if (api_is_multiple_url_enabled()) {
  539. $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  540. $access_url_id = api_get_current_access_url_id();
  541. if ($access_url_id != -1) {
  542. $sql_session_coach = "SELECT session.id_coach, u.id as user_id, lastname, firstname, MAX(login_date) as login_date
  543. FROM $tbl_user u , $tbl_sessions as session, $tbl_track_login , $tbl_session_rel_access_url as session_rel_url
  544. WHERE
  545. id_coach = u.id AND
  546. login_user_id = u.id AND
  547. access_url_id = $access_url_id AND
  548. session_rel_url.session_id = session.id
  549. GROUP BY u.id
  550. ORDER BY login_date $tracking_direction";
  551. }
  552. }
  553. $result_sessions_coach = Database::query($sql_session_coach);
  554. //$total_no_coaches += Database::num_rows($result_sessions_coach);
  555. while ($coach = Database::fetch_array($result_sessions_coach)) {
  556. $global_coaches[$coach['user_id']] = $coach;
  557. }
  558. $all_datas = [];
  559. foreach ($global_coaches as $id_coach => $coaches) {
  560. $time_on_platform = api_time_to_hms(
  561. Tracking::get_time_spent_on_the_platform($coaches['user_id'])
  562. );
  563. $last_connection = Tracking::get_last_connection_date(
  564. $coaches['user_id']
  565. );
  566. $nb_students = count(
  567. Tracking::get_student_followed_by_coach($coaches['user_id'])
  568. );
  569. $nb_courses = count(
  570. Tracking::get_courses_followed_by_coach($coaches['user_id'])
  571. );
  572. $nb_sessions = count(
  573. Tracking::get_sessions_coached_by_user($coaches['user_id'])
  574. );
  575. $table_row = [];
  576. if ($is_western_name_order) {
  577. $table_row[] = $coaches['firstname'];
  578. $table_row[] = $coaches['lastname'];
  579. } else {
  580. $table_row[] = $coaches['lastname'];
  581. $table_row[] = $coaches['firstname'];
  582. }
  583. $table_row[] = $time_on_platform;
  584. $table_row[] = $last_connection;
  585. $table_row[] = $nb_students;
  586. $table_row[] = $nb_courses;
  587. $table_row[] = $nb_sessions;
  588. $table_row[] = '<a href="session.php?id_coach='.$coaches['user_id'].'">
  589. '.Display::return_icon('2rightarrow.png', get_lang('Details')).'
  590. </a>';
  591. $all_datas[] = $table_row;
  592. if ($is_western_name_order) {
  593. $csv_content[] = [
  594. api_html_entity_decode($coaches['firstname'], ENT_QUOTES),
  595. api_html_entity_decode($coaches['lastname'], ENT_QUOTES),
  596. $time_on_platform,
  597. $last_connection,
  598. $nb_students,
  599. $nb_courses,
  600. $nb_sessions,
  601. ];
  602. } else {
  603. $csv_content[] = [
  604. api_html_entity_decode($coaches['lastname'], ENT_QUOTES),
  605. api_html_entity_decode($coaches['firstname'], ENT_QUOTES),
  606. $time_on_platform,
  607. $last_connection,
  608. $nb_students,
  609. $nb_courses,
  610. $nb_sessions,
  611. ];
  612. }
  613. }
  614. if ($tracking_column != 3) {
  615. if ($tracking_direction == 'DESC') {
  616. usort($all_datas, ['MySpace', 'rsort_users']);
  617. } else {
  618. usort($all_datas, ['MySpace', 'sort_users']);
  619. }
  620. }
  621. if ($export_csv && $tracking_column != 3) {
  622. usort($csv_content, 'sort_users');
  623. }
  624. if ($export_csv) {
  625. $csv_content = array_merge($csv_header, $csv_content);
  626. }
  627. foreach ($all_datas as $row) {
  628. $table->addRow($row, 'align="right"');
  629. }
  630. $table->display();
  631. }
  632. /**
  633. * @return mixed
  634. */
  635. public static function count_coaches()
  636. {
  637. global $total_no_coaches;
  638. return $total_no_coaches;
  639. }
  640. public static function sort_users($a, $b)
  641. {
  642. $tracking = Session::read('tracking_column');
  643. return api_strcmp(
  644. trim(api_strtolower($a[$tracking])),
  645. trim(api_strtolower($b[$tracking]))
  646. );
  647. }
  648. public static function rsort_users($a, $b)
  649. {
  650. $tracking = Session::read('tracking_column');
  651. return api_strcmp(
  652. trim(api_strtolower($b[$tracking])),
  653. trim(api_strtolower($a[$tracking]))
  654. );
  655. }
  656. /**
  657. * Display a sortable table that contains an overview off all the progress of the user in a session.
  658. *
  659. * @author César Perales <cesar.perales@beeznest.com>, Beeznest Team
  660. */
  661. public static function display_tracking_lp_progress_overview(
  662. $sessionId = '',
  663. $courseId = '',
  664. $date_from,
  665. $date_to
  666. ) {
  667. $course = api_get_course_info_by_id($courseId);
  668. /**
  669. * Column name
  670. * The order is important you need to check the $column variable in the model.ajax.php file.
  671. */
  672. $columns = [
  673. get_lang('Username'),
  674. get_lang('FirstName'),
  675. get_lang('LastName'),
  676. ];
  677. //add lessons of course
  678. $lessons = LearnpathList::get_course_lessons($course['code'], $sessionId);
  679. //create columns array
  680. foreach ($lessons as $lesson_id => $lesson) {
  681. $columns[] = $lesson['name'];
  682. }
  683. $columns[] = get_lang('Total');
  684. /**
  685. * Column config.
  686. */
  687. $column_model = [
  688. [
  689. 'name' => 'username',
  690. 'index' => 'username',
  691. 'align' => 'left',
  692. 'search' => 'true',
  693. 'wrap_cell' => "true",
  694. ],
  695. [
  696. 'name' => 'firstname',
  697. 'index' => 'firstname',
  698. 'align' => 'left',
  699. 'search' => 'true',
  700. ],
  701. [
  702. 'name' => 'lastname',
  703. 'index' => 'lastname',
  704. 'align' => 'left',
  705. 'search' => 'true',
  706. ],
  707. ];
  708. // Get dinamic column names
  709. foreach ($lessons as $lesson_id => $lesson) {
  710. $column_model[] = [
  711. 'name' => $lesson['id'],
  712. 'index' => $lesson['id'],
  713. 'align' => 'left',
  714. 'search' => 'true',
  715. ];
  716. }
  717. $column_model[] = [
  718. 'name' => 'total',
  719. 'index' => 'total',
  720. 'align' => 'left',
  721. 'search' => 'true',
  722. ];
  723. $action_links = '';
  724. // jqgrid will use this URL to do the selects
  725. $url = api_get_path(WEB_AJAX_PATH).'model.ajax.php?a=get_session_lp_progress&session_id='.$sessionId.'&course_id='.$courseId.'&date_to='.$date_to.'&date_from='.$date_from;
  726. // Table Id
  727. $tableId = 'lpProgress';
  728. // Autowidth
  729. $extra_params['autowidth'] = 'true';
  730. // height auto
  731. $extra_params['height'] = 'auto';
  732. $table = Display::grid_js(
  733. $tableId,
  734. $url,
  735. $columns,
  736. $column_model,
  737. $extra_params,
  738. [],
  739. $action_links,
  740. true
  741. );
  742. $return = '<script>$(function() {'.$table.
  743. 'jQuery("#'.$tableId.'").jqGrid("navGrid","#'.$tableId.'_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
  744. jQuery("#'.$tableId.'").jqGrid("navButtonAdd","#'.$tableId.'_pager",{
  745. caption:"",
  746. title:"'.get_lang('ExportExcel').'",
  747. onClickButton : function () {
  748. jQuery("#'.$tableId.'").jqGrid("excelExport",{"url":"'.$url.'&export_format=xls"});
  749. }
  750. });
  751. });</script>';
  752. $return .= Display::grid_html($tableId);
  753. return $return;
  754. }
  755. /**
  756. * Display a sortable table that contains an overview off all the progress of the user in a session.
  757. *
  758. * @param int $sessionId The session ID
  759. * @param int $courseId The course ID
  760. * @param int $exerciseId The quiz ID
  761. * @param $date_from
  762. * @param $date_to
  763. *
  764. * @return string HTML array of results formatted for gridJS
  765. *
  766. * @author César Perales <cesar.perales@beeznest.com>, Beeznest Team
  767. */
  768. public static function display_tracking_exercise_progress_overview(
  769. $sessionId = 0,
  770. $courseId = 0,
  771. $exerciseId = 0,
  772. $date_from = null,
  773. $date_to = null
  774. ) {
  775. $date_from = Security::remove_XSS($date_from);
  776. $date_to = Security::remove_XSS($date_to);
  777. /**
  778. * Column names
  779. * The column order is important. Check $column variable in the main/inc/ajax/model.ajax.php file.
  780. */
  781. $columns = [
  782. get_lang('Session'),
  783. get_lang('ExerciseId'),
  784. get_lang('ExerciseName'),
  785. get_lang('Username'),
  786. get_lang('LastName'),
  787. get_lang('FirstName'),
  788. get_lang('Time'),
  789. get_lang('QuestionId'),
  790. get_lang('QuestionTitle'),
  791. get_lang('WorkDescription'),
  792. get_lang('Answer'),
  793. get_lang('Correct'),
  794. ];
  795. /**
  796. * Column config.
  797. */
  798. $column_model = [
  799. ['name' => 'session', 'index' => 'session', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"],
  800. ['name' => 'exercise_id', 'index' => 'exercise_id', 'align' => 'left', 'search' => 'true'],
  801. ['name' => 'quiz_title', 'index' => 'quiz_title', 'align' => 'left', 'search' => 'true'],
  802. ['name' => 'username', 'index' => 'username', 'align' => 'left', 'search' => 'true'],
  803. ['name' => 'lastname', 'index' => 'lastname', 'align' => 'left', 'search' => 'true'],
  804. ['name' => 'firstname', 'index' => 'firstname', 'align' => 'left', 'search' => 'true'],
  805. ['name' => 'time', 'index' => 'time', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"],
  806. ['name' => 'question_id', 'index' => 'question_id', 'align' => 'left', 'search' => 'true'],
  807. ['name' => 'question', 'index' => 'question', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"],
  808. ['name' => 'description', 'index' => 'description', 'align' => 'left', 'width' => '550', 'search' => 'true', 'wrap_cell' => "true"],
  809. ['name' => 'answer', 'index' => 'answer', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"],
  810. ['name' => 'correct', 'index' => 'correct', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"],
  811. ];
  812. //get dynamic column names
  813. // jqgrid will use this URL to do the selects
  814. $url = api_get_path(WEB_AJAX_PATH).'model.ajax.php?a=get_exercise_progress&session_id='.$sessionId.'&course_id='.$courseId.'&exercise_id='.$exerciseId.'&date_to='.$date_to.'&date_from='.$date_from;
  815. // Autowidth
  816. $extra_params['autowidth'] = 'true';
  817. // height auto
  818. $extra_params['height'] = 'auto';
  819. $tableId = 'exerciseProgressOverview';
  820. $table = Display::grid_js(
  821. $tableId,
  822. $url,
  823. $columns,
  824. $column_model,
  825. $extra_params,
  826. [],
  827. '',
  828. true
  829. );
  830. $return = '<script>$(function() {'.$table.
  831. 'jQuery("#'.$tableId.'").jqGrid("navGrid","#'.$tableId.'_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
  832. jQuery("#'.$tableId.'").jqGrid("navButtonAdd","#'.$tableId.'_pager",{
  833. caption:"",
  834. title:"'.get_lang('ExportExcel').'",
  835. onClickButton : function () {
  836. jQuery("#'.$tableId.'").jqGrid("excelExport",{"url":"'.$url.'&export_format=xls"});
  837. }
  838. });
  839. });</script>';
  840. $return .= Display::grid_html($tableId);
  841. return $return;
  842. }
  843. /**
  844. * Displays a form with all the additionally defined user fields of the profile
  845. * and give you the opportunity to include these in the CSV export.
  846. *
  847. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  848. *
  849. * @version 1.8.6
  850. *
  851. * @since November 2008
  852. */
  853. public static function display_user_overview_export_options()
  854. {
  855. $message = '';
  856. $defaults = [];
  857. // include the user manager and formvalidator library
  858. if (isset($_GET['export']) && $_GET['export'] == 'options') {
  859. // get all the defined extra fields
  860. $extrafields = UserManager::get_extra_fields(
  861. 0,
  862. 50,
  863. 5,
  864. 'ASC',
  865. false,
  866. 1
  867. );
  868. // creating the form with all the defined extra fields
  869. $form = new FormValidator(
  870. 'exportextrafields',
  871. 'post',
  872. api_get_self()."?view=".Security::remove_XSS($_GET['view']).'&display='.Security::remove_XSS($_GET['display']).'&export='.Security::remove_XSS($_GET['export'])
  873. );
  874. if (is_array($extrafields) && count($extrafields) > 0) {
  875. foreach ($extrafields as $key => $extra) {
  876. $form->addElement('checkbox', 'extra_export_field'.$extra[0], '', $extra[3]);
  877. }
  878. $form->addButtonSave(get_lang('Ok'), 'submit');
  879. // setting the default values for the form that contains all the extra fields
  880. $exportFields = Session::read('additional_export_fields');
  881. if (is_array($exportFields)) {
  882. foreach ($exportFields as $key => $value) {
  883. $defaults['extra_export_field'.$value] = 1;
  884. }
  885. }
  886. $form->setDefaults($defaults);
  887. } else {
  888. $form->addElement('html', Display::return_message(get_lang('ThereAreNotExtrafieldsAvailable'), 'warning'));
  889. }
  890. if ($form->validate()) {
  891. // exporting the form values
  892. $values = $form->exportValues();
  893. // re-initialising the session that contains the additional fields that need to be exported
  894. Session::write('additional_export_fields', []);
  895. // adding the fields that are checked to the session
  896. $message = '';
  897. $additionalExportFields = [];
  898. foreach ($values as $field_ids => $value) {
  899. if ($value == 1 && strstr($field_ids, 'extra_export_field')) {
  900. $additionalExportFields[] = str_replace('extra_export_field', '', $field_ids);
  901. }
  902. }
  903. Session::write('additional_export_fields', $additionalExportFields);
  904. // adding the fields that will be also exported to a message string
  905. $additionalExportFields = Session::read('additional_export_fields');
  906. if (is_array($additionalExportFields)) {
  907. foreach ($additionalExportFields as $key => $extra_field_export) {
  908. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  909. }
  910. }
  911. // Displaying a feedback message
  912. if (!empty($additionalExportFields)) {
  913. echo Display::return_message(
  914. get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>',
  915. 'confirm',
  916. false
  917. );
  918. } else {
  919. echo Display::return_message(
  920. get_lang('NoAdditionalFieldsWillBeExported'),
  921. 'confirm',
  922. false
  923. );
  924. }
  925. } else {
  926. $form->display();
  927. }
  928. } else {
  929. $additionalExportFields = Session::read('additional_export_fields');
  930. if (!empty($additionalExportFields)) {
  931. // get all the defined extra fields
  932. $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  933. foreach ($additionalExportFields as $key => $extra_field_export) {
  934. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  935. }
  936. echo Display::return_message(
  937. get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>',
  938. 'normal',
  939. false
  940. );
  941. }
  942. }
  943. }
  944. /**
  945. * Display a sortable table that contains an overview of all the reporting progress of all courses.
  946. */
  947. public static function display_tracking_course_overview()
  948. {
  949. $params = ['view' => 'admin', 'display' => 'courseoverview'];
  950. $table = new SortableTable(
  951. 'tracking_session_overview',
  952. ['MySpace', 'get_total_number_courses'],
  953. ['MySpace', 'get_course_data_tracking_overview'],
  954. 1,
  955. 20,
  956. 'ASC',
  957. null, [
  958. 'class' => 'table table-transparent',
  959. ]
  960. );
  961. $table->additional_parameters = $params;
  962. $table->set_column_filter(0, ['MySpace', 'course_tracking_filter']);
  963. $tableContent = $table->return_table();
  964. $tpl = new Template('', false, false, false, false, false, false);
  965. $tpl->assign('table', $tableContent);
  966. $templateName = $tpl->get_template('my_space/course_summary.tpl');
  967. $tpl->display($templateName);
  968. }
  969. /**
  970. * Get the total number of courses.
  971. *
  972. * @return int Total number of courses
  973. */
  974. public static function get_total_number_courses()
  975. {
  976. return CourseManager::count_courses(api_get_current_access_url_id());
  977. }
  978. /**
  979. * Get data for the courses.
  980. *
  981. * @param int $from Inferior limit
  982. * @param int $numberItems Number of items to select
  983. * @param string $column Column to order on
  984. * @param string $direction Order direction
  985. *
  986. * @return array Results
  987. */
  988. public static function get_course_data_tracking_overview(
  989. $from,
  990. $numberItems,
  991. $column,
  992. $direction
  993. ) {
  994. $courses = CourseManager::get_courses_list(
  995. $from,
  996. $numberItems,
  997. $column,
  998. $direction,
  999. -1,
  1000. '',
  1001. api_get_current_access_url_id()
  1002. );
  1003. $list = [];
  1004. foreach ($courses as $course) {
  1005. $list[] = [
  1006. '0' => $course['code'],
  1007. 'col0' => $course['code'],
  1008. ];
  1009. }
  1010. return $list;
  1011. }
  1012. /**
  1013. * Fills in course reporting data.
  1014. *
  1015. * @param int course code
  1016. * @param array $url_params additional url parameters
  1017. * @param array $row the row information (the other columns)
  1018. *
  1019. * @return string html code
  1020. */
  1021. public static function course_tracking_filter($course_code, $url_params, $row)
  1022. {
  1023. $course_code = $row[0];
  1024. $courseInfo = api_get_course_info($course_code);
  1025. $courseId = $courseInfo['real_id'];
  1026. $tpl = new Template('', false, false, false, false, false, false);
  1027. $data = null;
  1028. // database table definition
  1029. $tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1030. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1031. // getting all the courses of the user
  1032. $sql = "SELECT *
  1033. FROM $tbl_user AS u
  1034. INNER JOIN $tbl_course_rel_user AS cu
  1035. ON cu.user_id = u.user_id
  1036. WHERE cu.c_id = '".$courseId."'";
  1037. $result = Database::query($sql);
  1038. $time_spent = 0;
  1039. $progress = 0;
  1040. $nb_progress_lp = 0;
  1041. $score = 0;
  1042. $nb_score_lp = 0;
  1043. $nb_messages = 0;
  1044. $nb_assignments = 0;
  1045. $last_login_date = false;
  1046. $total_score_obtained = 0;
  1047. $total_score_possible = 0;
  1048. $total_questions_answered = 0;
  1049. while ($row = Database::fetch_object($result)) {
  1050. // get time spent in the course and session
  1051. $time_spent += Tracking::get_time_spent_on_the_course(
  1052. $row->user_id,
  1053. $courseInfo['real_id']
  1054. );
  1055. $progress_tmp = Tracking::get_avg_student_progress(
  1056. $row->user_id,
  1057. $course_code,
  1058. [],
  1059. null,
  1060. true
  1061. );
  1062. $progress += $progress_tmp[0];
  1063. $nb_progress_lp += $progress_tmp[1];
  1064. $score_tmp = Tracking::get_avg_student_score(
  1065. $row->user_id,
  1066. $course_code,
  1067. [],
  1068. null,
  1069. true
  1070. );
  1071. if (is_array($score_tmp)) {
  1072. $score += $score_tmp[0];
  1073. $nb_score_lp += $score_tmp[1];
  1074. }
  1075. $nb_messages += Tracking::count_student_messages(
  1076. $row->user_id,
  1077. $course_code
  1078. );
  1079. $nb_assignments += Tracking::count_student_assignments(
  1080. $row->user_id,
  1081. $course_code
  1082. );
  1083. $last_login_date_tmp = Tracking::get_last_connection_date_on_the_course(
  1084. $row->user_id,
  1085. $courseInfo,
  1086. null,
  1087. false
  1088. );
  1089. if ($last_login_date_tmp != false &&
  1090. $last_login_date == false
  1091. ) { // TODO: To be cleaned
  1092. $last_login_date = $last_login_date_tmp;
  1093. } elseif ($last_login_date_tmp != false && $last_login_date != false) {
  1094. // TODO: Repeated previous condition. To be cleaned.
  1095. // Find the max and assign it to first_login_date
  1096. if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  1097. $last_login_date = $last_login_date_tmp;
  1098. }
  1099. }
  1100. $exercise_results_tmp = self::exercises_results($row->user_id, $course_code);
  1101. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  1102. $total_score_possible += $exercise_results_tmp['score_possible'];
  1103. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  1104. }
  1105. if ($nb_progress_lp > 0) {
  1106. $avg_progress = round($progress / $nb_progress_lp, 2);
  1107. } else {
  1108. $avg_progress = 0;
  1109. }
  1110. if ($nb_score_lp > 0) {
  1111. $avg_score = round($score / $nb_score_lp, 2);
  1112. } else {
  1113. $avg_score = '-';
  1114. }
  1115. if ($last_login_date) {
  1116. $last_login_date = api_convert_and_format_date(
  1117. $last_login_date,
  1118. DATE_FORMAT_SHORT,
  1119. date_default_timezone_get()
  1120. );
  1121. } else {
  1122. $last_login_date = '-';
  1123. }
  1124. if ($total_score_possible > 0) {
  1125. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  1126. } else {
  1127. $total_score_percentage = 0;
  1128. }
  1129. if ($total_score_percentage > 0) {
  1130. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  1131. } else {
  1132. $total_score = '-';
  1133. }
  1134. $data = [
  1135. 'course_code' => $course_code,
  1136. 'id' => $courseId,
  1137. 'image' => $courseInfo['course_image_large'],
  1138. 'image_small' => $courseInfo['course_image'],
  1139. 'title' => $courseInfo['title'],
  1140. 'url' => $courseInfo['course_public_url'],
  1141. 'category' => $courseInfo['categoryName'],
  1142. 'time_spent' => api_time_to_hms($time_spent),
  1143. 'avg_progress' => $avg_progress,
  1144. 'avg_score' => $avg_score,
  1145. 'number_message' => $nb_messages,
  1146. 'number_assignments' => $nb_assignments,
  1147. 'total_score' => $total_score,
  1148. 'questions_answered' => $total_questions_answered,
  1149. 'last_login' => $last_login_date,
  1150. ];
  1151. $tpl->assign('data', $data);
  1152. $layout = $tpl->get_template('my_space/partials/tracking_course_overview.tpl');
  1153. $content = $tpl->fetch($layout);
  1154. return $content;
  1155. }
  1156. /**
  1157. * This function exports the table that we see in display_tracking_course_overview().
  1158. */
  1159. public static function export_tracking_course_overview()
  1160. {
  1161. // database table definition
  1162. $tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1163. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1164. // the values of the sortable table
  1165. if ($_GET['tracking_course_overview_page_nr']) {
  1166. $from = $_GET['tracking_course_overview_page_nr'];
  1167. } else {
  1168. $from = 0;
  1169. }
  1170. if ($_GET['tracking_course_overview_column']) {
  1171. $orderby = $_GET['tracking_course_overview_column'];
  1172. } else {
  1173. $orderby = 0;
  1174. }
  1175. if ($_GET['tracking_course_overview_direction']) {
  1176. $direction = $_GET['tracking_course_overview_direction'];
  1177. } else {
  1178. $direction = 'ASC';
  1179. }
  1180. $course_data = self::get_course_data_tracking_overview(
  1181. $from,
  1182. 1000,
  1183. $orderby,
  1184. $direction
  1185. );
  1186. $csv_content = [];
  1187. // the first line of the csv file with the column headers
  1188. $csv_row = [];
  1189. $csv_row[] = get_lang('Course');
  1190. $csv_row[] = get_lang('AvgTimeSpentInTheCourse');
  1191. $csv_row[] = get_lang('AvgStudentsProgress');
  1192. $csv_row[] = get_lang('AvgCourseScore');
  1193. $csv_row[] = get_lang('TotalNumberOfMessages');
  1194. $csv_row[] = get_lang('TotalNumberOfAssignments');
  1195. $csv_row[] = get_lang('TotalExercisesScoreObtained');
  1196. $csv_row[] = get_lang('TotalExercisesScorePossible');
  1197. $csv_row[] = get_lang('TotalExercisesAnswered');
  1198. $csv_row[] = get_lang('TotalExercisesScorePercentage');
  1199. $csv_row[] = get_lang('LatestLogin');
  1200. $csv_content[] = $csv_row;
  1201. // the other lines (the data)
  1202. foreach ($course_data as $key => $course) {
  1203. $course_code = $course[0];
  1204. $courseInfo = api_get_course_info($course_code);
  1205. $course_title = $courseInfo['title'];
  1206. $courseId = $courseInfo['real_id'];
  1207. $csv_row = [];
  1208. $csv_row[] = $course_title;
  1209. // getting all the courses of the session
  1210. $sql = "SELECT *
  1211. FROM $tbl_user AS u
  1212. INNER JOIN $tbl_course_rel_user AS cu
  1213. ON cu.user_id = u.user_id
  1214. WHERE cu.c_id = '".$courseId."'";
  1215. $result = Database::query($sql);
  1216. $time_spent = 0;
  1217. $progress = 0;
  1218. $nb_progress_lp = 0;
  1219. $score = 0;
  1220. $nb_score_lp = 0;
  1221. $nb_messages = 0;
  1222. $nb_assignments = 0;
  1223. $last_login_date = false;
  1224. $total_score_obtained = 0;
  1225. $total_score_possible = 0;
  1226. $total_questions_answered = 0;
  1227. while ($row = Database::fetch_object($result)) {
  1228. // get time spent in the course and session
  1229. $time_spent += Tracking::get_time_spent_on_the_course(
  1230. $row->user_id,
  1231. $courseId
  1232. );
  1233. $progress_tmp = Tracking::get_avg_student_progress(
  1234. $row->user_id,
  1235. $course_code,
  1236. [],
  1237. null,
  1238. true
  1239. );
  1240. $progress += $progress_tmp[0];
  1241. $nb_progress_lp += $progress_tmp[1];
  1242. $score_tmp = Tracking::get_avg_student_score(
  1243. $row->user_id,
  1244. $course_code,
  1245. [],
  1246. null,
  1247. true
  1248. );
  1249. if (is_array($score_tmp)) {
  1250. $score += $score_tmp[0];
  1251. $nb_score_lp += $score_tmp[1];
  1252. }
  1253. $nb_messages += Tracking::count_student_messages(
  1254. $row->user_id,
  1255. $course_code
  1256. );
  1257. $nb_assignments += Tracking::count_student_assignments(
  1258. $row->user_id,
  1259. $course_code
  1260. );
  1261. $last_login_date_tmp = Tracking::get_last_connection_date_on_the_course(
  1262. $row->user_id,
  1263. $courseInfo,
  1264. null,
  1265. false
  1266. );
  1267. if ($last_login_date_tmp != false && $last_login_date == false) {
  1268. // TODO: To be cleaned.
  1269. $last_login_date = $last_login_date_tmp;
  1270. } elseif ($last_login_date_tmp != false && $last_login_date == false) {
  1271. // TODO: Repeated previous condition. To be cleaned.
  1272. // Find the max and assign it to first_login_date
  1273. if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  1274. $last_login_date = $last_login_date_tmp;
  1275. }
  1276. }
  1277. $exercise_results_tmp = self::exercises_results($row->user_id, $course_code);
  1278. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  1279. $total_score_possible += $exercise_results_tmp['score_possible'];
  1280. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  1281. }
  1282. if ($nb_progress_lp > 0) {
  1283. $avg_progress = round($progress / $nb_progress_lp, 2);
  1284. } else {
  1285. $avg_progress = 0;
  1286. }
  1287. if ($nb_score_lp > 0) {
  1288. $avg_score = round($score / $nb_score_lp, 2);
  1289. } else {
  1290. $avg_score = '-';
  1291. }
  1292. if ($last_login_date) {
  1293. $last_login_date = api_convert_and_format_date(
  1294. $last_login_date,
  1295. DATE_FORMAT_SHORT,
  1296. date_default_timezone_get()
  1297. );
  1298. } else {
  1299. $last_login_date = '-';
  1300. }
  1301. if ($total_score_possible > 0) {
  1302. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  1303. } else {
  1304. $total_score_percentage = 0;
  1305. }
  1306. // time spent in the course
  1307. $csv_row[] = api_time_to_hms($time_spent);
  1308. // student progress in course
  1309. $csv_row[] = $avg_progress;
  1310. // student score
  1311. $csv_row[] = $avg_score;
  1312. // student messages
  1313. $csv_row[] = $nb_messages;
  1314. // student assignments
  1315. $csv_row[] = $nb_assignments;
  1316. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  1317. $csv_row[] = $total_score_obtained;
  1318. $csv_row[] = $total_score_possible;
  1319. $csv_row[] = $total_questions_answered;
  1320. $csv_row[] = $total_score_percentage;
  1321. // last connection
  1322. $csv_row[] = $last_login_date;
  1323. $csv_content[] = $csv_row;
  1324. }
  1325. Export::arrayToCsv($csv_content, 'reporting_course_overview');
  1326. exit;
  1327. }
  1328. /**
  1329. * Display a sortable table that contains an overview of all the reporting
  1330. * progress of all sessions and all courses the user is subscribed to.
  1331. *
  1332. * @author Guillaume Viguier <guillaume@viguierjust.com>
  1333. */
  1334. public static function display_tracking_session_overview()
  1335. {
  1336. $head = '<table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  1337. $head .= '<tr>';
  1338. $head .= '<th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>';
  1339. $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  1340. $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  1341. $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  1342. $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  1343. $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  1344. $head .= '<th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  1345. $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  1346. $head .= '<th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  1347. $head .= '</tr></table>';
  1348. $params = ['view' => 'admin', 'display' => 'sessionoverview'];
  1349. $table = new SortableTable(
  1350. 'tracking_session_overview',
  1351. ['MySpace', 'get_total_number_sessions'],
  1352. ['MySpace', 'get_session_data_tracking_overview'],
  1353. 1
  1354. );
  1355. $table->additional_parameters = $params;
  1356. $table->set_header(0, '', false, null, ['style' => 'display: none']);
  1357. $table->set_header(
  1358. 1,
  1359. get_lang('Session'),
  1360. true,
  1361. ['style' => 'font-size:8pt'],
  1362. ['style' => 'font-size:8pt']
  1363. );
  1364. $table->set_header(
  1365. 2,
  1366. $head,
  1367. false,
  1368. ['style' => 'width:90%;border:0;padding:0;font-size:7.5pt;'],
  1369. ['style' => 'width:90%;padding:0;font-size:7.5pt;']
  1370. );
  1371. $table->set_column_filter(2, ['MySpace', 'session_tracking_filter']);
  1372. $table->display();
  1373. }
  1374. /**
  1375. * Get the total number of sessions.
  1376. *
  1377. * @return int Total number of sessions
  1378. */
  1379. public static function get_total_number_sessions()
  1380. {
  1381. return SessionManager::count_sessions(api_get_current_access_url_id());
  1382. }
  1383. /**
  1384. * Get data for the sessions.
  1385. *
  1386. * @param int $from Inferior limit
  1387. * @param int $numberItems Number of items to select
  1388. * @param string $column Column to order on
  1389. * @param string $direction Order direction
  1390. *
  1391. * @return array Results
  1392. */
  1393. public static function get_session_data_tracking_overview(
  1394. $from,
  1395. $numberItems,
  1396. $column,
  1397. $direction
  1398. ) {
  1399. $from = (int) $from;
  1400. $numberItems = (int) $numberItems;
  1401. $direction = Database::escape_string($direction);
  1402. $columnName = 'name';
  1403. if ($column === 1) {
  1404. $columnName = 'id';
  1405. }
  1406. $options = [
  1407. 'order' => " $columnName $direction",
  1408. 'limit' => " $from,$numberItems",
  1409. ];
  1410. $sessions = SessionManager::formatSessionsAdminForGrid($options);
  1411. $list = [];
  1412. foreach ($sessions as $session) {
  1413. $list[] = [
  1414. '0' => $session['id'],
  1415. 'col0' => $session['id'],
  1416. '1' => strip_tags($session['name']),
  1417. 'col1' => strip_tags($session['name']),
  1418. ];
  1419. }
  1420. return $list;
  1421. }
  1422. /**
  1423. * Fills in session reporting data.
  1424. *
  1425. * @param int $session_id the id of the user
  1426. * @param array $url_params additonal url parameters
  1427. * @param array $row the row information (the other columns)
  1428. *
  1429. * @return string html code
  1430. */
  1431. public static function session_tracking_filter($session_id, $url_params, $row)
  1432. {
  1433. $session_id = $row[0];
  1434. // the table header
  1435. $return = '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  1436. // database table definition
  1437. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1438. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1439. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1440. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1441. // getting all the courses of the user
  1442. $sql = "SELECT * FROM $tbl_course AS c
  1443. INNER JOIN $tbl_session_rel_course AS sc
  1444. ON sc.c_id = c.id
  1445. WHERE sc.session_id = '".$session_id."'";
  1446. $result = Database::query($sql);
  1447. while ($row = Database::fetch_object($result)) {
  1448. $courseId = $row->c_id;
  1449. $courseInfo = api_get_course_info_by_id($courseId);
  1450. $return .= '<tr>';
  1451. // course code
  1452. $return .= ' <td width="157px" >'.$row->title.'</td>';
  1453. // get the users in the course
  1454. $sql = "SELECT u.user_id
  1455. FROM $tbl_user AS u
  1456. INNER JOIN $tbl_session_rel_course_rel_user AS scu
  1457. ON u.user_id = scu.user_id
  1458. WHERE scu.session_id = '".$session_id."' AND scu.c_id = '".$courseId."'";
  1459. $result_users = Database::query($sql);
  1460. $time_spent = 0;
  1461. $progress = 0;
  1462. $nb_progress_lp = 0;
  1463. $score = 0;
  1464. $nb_score_lp = 0;
  1465. $nb_messages = 0;
  1466. $nb_assignments = 0;
  1467. $last_login_date = false;
  1468. $total_score_obtained = 0;
  1469. $total_score_possible = 0;
  1470. $total_questions_answered = 0;
  1471. while ($row_user = Database::fetch_object($result_users)) {
  1472. // get time spent in the course and session
  1473. $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $courseId, $session_id);
  1474. $progress_tmp = Tracking::get_avg_student_progress($row_user->user_id, $row->code, [], $session_id, true);
  1475. $progress += $progress_tmp[0];
  1476. $nb_progress_lp += $progress_tmp[1];
  1477. $score_tmp = Tracking::get_avg_student_score($row_user->user_id, $row->code, [], $session_id, true);
  1478. if (is_array($score_tmp)) {
  1479. $score += $score_tmp[0];
  1480. $nb_score_lp += $score_tmp[1];
  1481. }
  1482. $nb_messages += Tracking::count_student_messages($row_user->user_id, $row->code, $session_id);
  1483. $nb_assignments += Tracking::count_student_assignments($row_user->user_id, $row->code, $session_id);
  1484. $last_login_date_tmp = Tracking::get_last_connection_date_on_the_course(
  1485. $row_user->user_id,
  1486. $courseInfo,
  1487. $session_id,
  1488. false
  1489. );
  1490. if ($last_login_date_tmp != false && $last_login_date == false) {
  1491. // TODO: To be cleaned.
  1492. $last_login_date = $last_login_date_tmp;
  1493. } elseif ($last_login_date_tmp != false && $last_login_date != false) {
  1494. // TODO: Repeated previous condition! To be cleaned.
  1495. // Find the max and assign it to first_login_date
  1496. if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  1497. $last_login_date = $last_login_date_tmp;
  1498. }
  1499. }
  1500. $exercise_results_tmp = self::exercises_results($row_user->user_id, $row->code, $session_id);
  1501. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  1502. $total_score_possible += $exercise_results_tmp['score_possible'];
  1503. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  1504. }
  1505. if ($nb_progress_lp > 0) {
  1506. $avg_progress = round($progress / $nb_progress_lp, 2);
  1507. } else {
  1508. $avg_progress = 0;
  1509. }
  1510. if ($nb_score_lp > 0) {
  1511. $avg_score = round($score / $nb_score_lp, 2);
  1512. } else {
  1513. $avg_score = '-';
  1514. }
  1515. if ($last_login_date) {
  1516. $last_login_date = api_convert_and_format_date(
  1517. $last_login_date,
  1518. DATE_FORMAT_SHORT,
  1519. date_default_timezone_get()
  1520. );
  1521. } else {
  1522. $last_login_date = '-';
  1523. }
  1524. if ($total_score_possible > 0) {
  1525. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  1526. } else {
  1527. $total_score_percentage = 0;
  1528. }
  1529. if ($total_score_percentage > 0) {
  1530. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  1531. } else {
  1532. $total_score = '-';
  1533. }
  1534. // time spent in the course
  1535. $return .= ' <td><div>'.api_time_to_hms($time_spent).'</div></td>';
  1536. // student progress in course
  1537. $return .= ' <td><div>'.$avg_progress.'</div></td>';
  1538. // student score
  1539. $return .= ' <td><div>'.$avg_score.'</div></td>';
  1540. // student messages
  1541. $return .= ' <td><div>'.$nb_messages.'</div></td>';
  1542. // student assignments
  1543. $return .= ' <td><div>'.$nb_assignments.'</div></td>';
  1544. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  1545. $return .= '<td width="105px;">'.$total_score.'</td>';
  1546. $return .= '<td>'.$total_questions_answered.'</td>';
  1547. // last connection
  1548. $return .= ' <td><div>'.$last_login_date.'</div></td>';
  1549. $return .= '<tr>';
  1550. }
  1551. $return .= '</table>';
  1552. return $return;
  1553. }
  1554. /**
  1555. * This function exports the table that we see in display_tracking_session_overview().
  1556. */
  1557. public static function export_tracking_session_overview()
  1558. {
  1559. // database table definition
  1560. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1561. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1562. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1563. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1564. // the values of the sortable table
  1565. $from = 0;
  1566. if ($_GET['tracking_session_overview_page_nr']) {
  1567. $from = $_GET['tracking_session_overview_page_nr'];
  1568. }
  1569. $orderby = 0;
  1570. if ($_GET['tracking_session_overview_column']) {
  1571. $orderby = $_GET['tracking_session_overview_column'];
  1572. }
  1573. $direction = 'ASC';
  1574. if ($_GET['tracking_session_overview_direction']) {
  1575. $direction = $_GET['tracking_session_overview_direction'];
  1576. }
  1577. $session_data = self::get_session_data_tracking_overview($from, 1000, $orderby, $direction);
  1578. $csv_content = [];
  1579. // the first line of the csv file with the column headers
  1580. $csv_row = [];
  1581. $csv_row[] = get_lang('Session');
  1582. $csv_row[] = get_lang('Course');
  1583. $csv_row[] = get_lang('AvgTimeSpentInTheCourse');
  1584. $csv_row[] = get_lang('AvgStudentsProgress');
  1585. $csv_row[] = get_lang('AvgCourseScore');
  1586. $csv_row[] = get_lang('TotalNumberOfMessages');
  1587. $csv_row[] = get_lang('TotalNumberOfAssignments');
  1588. $csv_row[] = get_lang('TotalExercisesScoreObtained');
  1589. $csv_row[] = get_lang('TotalExercisesScorePossible');
  1590. $csv_row[] = get_lang('TotalExercisesAnswered');
  1591. $csv_row[] = get_lang('TotalExercisesScorePercentage');
  1592. $csv_row[] = get_lang('LatestLogin');
  1593. $csv_content[] = $csv_row;
  1594. // the other lines (the data)
  1595. foreach ($session_data as $key => $session) {
  1596. $session_id = $session[0];
  1597. $session_title = $session[1];
  1598. // getting all the courses of the session
  1599. $sql = "SELECT * FROM $tbl_course AS c
  1600. INNER JOIN $tbl_session_rel_course AS sc
  1601. ON sc.c_id = c.id
  1602. WHERE sc.session_id = '".$session_id."';";
  1603. $result = Database::query($sql);
  1604. while ($row = Database::fetch_object($result)) {
  1605. $courseId = $row->c_id;
  1606. $courseInfo = api_get_course_info_by_id($courseId);
  1607. $csv_row = [];
  1608. $csv_row[] = $session_title;
  1609. $csv_row[] = $row->title;
  1610. // get the users in the course
  1611. $sql = "SELECT scu.user_id
  1612. FROM $tbl_user AS u
  1613. INNER JOIN $tbl_session_rel_course_rel_user AS scu
  1614. ON u.user_id = scu.user_id
  1615. WHERE scu.session_id = '".$session_id."' AND scu.c_id = '".$courseId."'";
  1616. $result_users = Database::query($sql);
  1617. $time_spent = 0;
  1618. $progress = 0;
  1619. $nb_progress_lp = 0;
  1620. $score = 0;
  1621. $nb_score_lp = 0;
  1622. $nb_messages = 0;
  1623. $nb_assignments = 0;
  1624. $last_login_date = false;
  1625. $total_score_obtained = 0;
  1626. $total_score_possible = 0;
  1627. $total_questions_answered = 0;
  1628. while ($row_user = Database::fetch_object($result_users)) {
  1629. // get time spent in the course and session
  1630. $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $courseId, $session_id);
  1631. $progress_tmp = Tracking::get_avg_student_progress(
  1632. $row_user->user_id,
  1633. $row->code,
  1634. [],
  1635. $session_id,
  1636. true
  1637. );
  1638. $progress += $progress_tmp[0];
  1639. $nb_progress_lp += $progress_tmp[1];
  1640. $score_tmp = Tracking::get_avg_student_score(
  1641. $row_user->user_id,
  1642. $row->code,
  1643. [],
  1644. $session_id,
  1645. true
  1646. );
  1647. if (is_array($score_tmp)) {
  1648. $score += $score_tmp[0];
  1649. $nb_score_lp += $score_tmp[1];
  1650. }
  1651. $nb_messages += Tracking::count_student_messages(
  1652. $row_user->user_id,
  1653. $row->code,
  1654. $session_id
  1655. );
  1656. $nb_assignments += Tracking::count_student_assignments(
  1657. $row_user->user_id,
  1658. $row->code,
  1659. $session_id
  1660. );
  1661. $last_login_date_tmp = Tracking:: get_last_connection_date_on_the_course(
  1662. $row_user->user_id,
  1663. $courseInfo,
  1664. $session_id,
  1665. false
  1666. );
  1667. if ($last_login_date_tmp != false && $last_login_date == false) {
  1668. // TODO: To be cleaned.
  1669. $last_login_date = $last_login_date_tmp;
  1670. } elseif ($last_login_date_tmp != false && $last_login_date == false) {
  1671. // TODO: Repeated previous condition. To be cleaned.
  1672. // Find the max and assign it to first_login_date
  1673. if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  1674. $last_login_date = $last_login_date_tmp;
  1675. }
  1676. }
  1677. $exercise_results_tmp = self::exercises_results($row_user->user_id, $row->code, $session_id);
  1678. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  1679. $total_score_possible += $exercise_results_tmp['score_possible'];
  1680. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  1681. }
  1682. if ($nb_progress_lp > 0) {
  1683. $avg_progress = round($progress / $nb_progress_lp, 2);
  1684. } else {
  1685. $avg_progress = 0;
  1686. }
  1687. if ($nb_score_lp > 0) {
  1688. $avg_score = round($score / $nb_score_lp, 2);
  1689. } else {
  1690. $avg_score = '-';
  1691. }
  1692. if ($last_login_date) {
  1693. $last_login_date = api_convert_and_format_date(
  1694. $last_login_date,
  1695. DATE_FORMAT_SHORT,
  1696. date_default_timezone_get()
  1697. );
  1698. } else {
  1699. $last_login_date = '-';
  1700. }
  1701. if ($total_score_possible > 0) {
  1702. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  1703. } else {
  1704. $total_score_percentage = 0;
  1705. }
  1706. if ($total_score_percentage > 0) {
  1707. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  1708. } else {
  1709. $total_score = '-';
  1710. }
  1711. // time spent in the course
  1712. $csv_row[] = api_time_to_hms($time_spent);
  1713. // student progress in course
  1714. $csv_row[] = $avg_progress;
  1715. // student score
  1716. $csv_row[] = $avg_score;
  1717. // student messages
  1718. $csv_row[] = $nb_messages;
  1719. // student assignments
  1720. $csv_row[] = $nb_assignments;
  1721. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  1722. $csv_row[] = $total_score_obtained;
  1723. $csv_row[] = $total_score_possible;
  1724. $csv_row[] = $total_questions_answered;
  1725. $csv_row[] = $total_score_percentage;
  1726. // last connection
  1727. $csv_row[] = $last_login_date;
  1728. $csv_content[] = $csv_row;
  1729. }
  1730. }
  1731. Export::arrayToCsv($csv_content, 'reporting_session_overview');
  1732. exit;
  1733. }
  1734. /**
  1735. * Get general information about the exercise performance of the user
  1736. * the total obtained score (all the score on all the questions)
  1737. * the maximum score that could be obtained
  1738. * the number of questions answered
  1739. * the success percentage.
  1740. *
  1741. * @param int $user_id the id of the user
  1742. * @param string $course_code the course code
  1743. * @param int $session_id
  1744. *
  1745. * @return array
  1746. *
  1747. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1748. *
  1749. * @version Dokeos 1.8.6
  1750. *
  1751. * @since November 2008
  1752. */
  1753. public static function exercises_results($user_id, $course_code, $session_id = 0)
  1754. {
  1755. $user_id = (int) $user_id;
  1756. $courseId = api_get_course_int_id($course_code);
  1757. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
  1758. $sql = "SELECT exe_result, exe_weighting
  1759. FROM $table
  1760. WHERE
  1761. c_id = $courseId AND
  1762. exe_user_id = $user_id";
  1763. $session_id = (int) $session_id;
  1764. if (!empty($session_id)) {
  1765. $sql .= " AND session_id = '".$session_id."' ";
  1766. }
  1767. $result = Database::query($sql);
  1768. $score_obtained = 0;
  1769. $score_possible = 0;
  1770. $questions_answered = 0;
  1771. while ($row = Database::fetch_array($result)) {
  1772. $score_obtained += $row['exe_result'];
  1773. $score_possible += $row['exe_weighting'];
  1774. $questions_answered++;
  1775. }
  1776. $percentage = null;
  1777. if ($score_possible != 0) {
  1778. $percentage = round(($score_obtained / $score_possible * 100), 2);
  1779. }
  1780. return [
  1781. 'score_obtained' => $score_obtained,
  1782. 'score_possible' => $score_possible,
  1783. 'questions_answered' => $questions_answered,
  1784. 'percentage' => $percentage,
  1785. ];
  1786. }
  1787. /**
  1788. * This function exports the table that we see in display_tracking_user_overview().
  1789. *
  1790. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1791. *
  1792. * @version Dokeos 1.8.6
  1793. *
  1794. * @since October 2008
  1795. */
  1796. public static function export_tracking_user_overview()
  1797. {
  1798. // database table definitions
  1799. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1800. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  1801. // the values of the sortable table
  1802. if ($_GET['tracking_user_overview_page_nr']) {
  1803. $from = $_GET['tracking_user_overview_page_nr'];
  1804. } else {
  1805. $from = 0;
  1806. }
  1807. if ($_GET['tracking_user_overview_column']) {
  1808. $orderby = $_GET['tracking_user_overview_column'];
  1809. } else {
  1810. $orderby = 0;
  1811. }
  1812. if ($is_western_name_order != api_is_western_name_order() && ($orderby == 1 || $orderby == 2)) {
  1813. // Swapping the sorting column if name order for export is different than the common name order.
  1814. $orderby = 3 - $orderby;
  1815. }
  1816. if ($_GET['tracking_user_overview_direction']) {
  1817. $direction = $_GET['tracking_user_overview_direction'];
  1818. } else {
  1819. $direction = 'ASC';
  1820. }
  1821. $user_data = self::get_user_data_tracking_overview(
  1822. $from,
  1823. 1000,
  1824. $orderby,
  1825. $direction
  1826. );
  1827. // the first line of the csv file with the column headers
  1828. $csv_row = [];
  1829. $csv_row[] = get_lang('OfficialCode');
  1830. if ($is_western_name_order) {
  1831. $csv_row[] = get_lang('FirstName');
  1832. $csv_row[] = get_lang('LastName');
  1833. } else {
  1834. $csv_row[] = get_lang('LastName');
  1835. $csv_row[] = get_lang('FirstName');
  1836. }
  1837. $csv_row[] = get_lang('LoginName');
  1838. $csv_row[] = get_lang('CourseCode');
  1839. // the additional user defined fields (only those that were selected to be exported)
  1840. $fields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  1841. $additionalExportFields = Session::read('additional_export_fields');
  1842. if (is_array($additionalExportFields)) {
  1843. foreach ($additionalExportFields as $key => $extra_field_export) {
  1844. $csv_row[] = $fields[$extra_field_export][3];
  1845. $field_names_to_be_exported[] = 'extra_'.$fields[$extra_field_export][1];
  1846. }
  1847. }
  1848. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  1849. $csv_row[] = get_lang('AvgStudentsProgress', '');
  1850. $csv_row[] = get_lang('AvgCourseScore', '');
  1851. $csv_row[] = get_lang('AvgExercisesScore', '');
  1852. $csv_row[] = get_lang('AvgMessages', '');
  1853. $csv_row[] = get_lang('AvgAssignments', '');
  1854. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  1855. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  1856. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  1857. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  1858. $csv_row[] = get_lang('FirstLogin', '');
  1859. $csv_row[] = get_lang('LatestLogin', '');
  1860. $csv_content[] = $csv_row;
  1861. // the other lines (the data)
  1862. foreach ($user_data as $key => $user) {
  1863. // getting all the courses of the user
  1864. $sql = "SELECT * FROM $tbl_course_user
  1865. WHERE user_id = '".intval($user[4])."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  1866. $result = Database::query($sql);
  1867. while ($row = Database::fetch_row($result)) {
  1868. $courseInfo = api_get_course_info($row['course_code']);
  1869. $courseId = $courseInfo['real_id'];
  1870. $csv_row = [];
  1871. // user official code
  1872. $csv_row[] = $user[0];
  1873. // user first|last name
  1874. $csv_row[] = $user[1];
  1875. // user last|first name
  1876. $csv_row[] = $user[2];
  1877. // user login name
  1878. $csv_row[] = $user[3];
  1879. // course code
  1880. $csv_row[] = $row[0];
  1881. // the additional defined user fields
  1882. $extra_fields = self::get_user_overview_export_extra_fields($user[4]);
  1883. if (is_array($field_names_to_be_exported)) {
  1884. foreach ($field_names_to_be_exported as $key => $extra_field_export) {
  1885. $csv_row[] = $extra_fields[$extra_field_export];
  1886. }
  1887. }
  1888. // time spent in the course
  1889. $csv_row[] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user[4], $courseId));
  1890. // student progress in course
  1891. $csv_row[] = round(Tracking::get_avg_student_progress($user[4], $row[0]), 2);
  1892. // student score
  1893. $csv_row[] = round(Tracking::get_avg_student_score($user[4], $row[0]), 2);
  1894. // student tes score
  1895. $csv_row[] = round(Tracking::get_avg_student_exercise_score($user[4], $row[0]), 2);
  1896. // student messages
  1897. $csv_row[] = Tracking::count_student_messages($user[4], $row[0]);
  1898. // student assignments
  1899. $csv_row[] = Tracking::count_student_assignments($user[4], $row[0]);
  1900. // student exercises results
  1901. $exercises_results = self::exercises_results($user[4], $row[0]);
  1902. $csv_row[] = $exercises_results['score_obtained'];
  1903. $csv_row[] = $exercises_results['score_possible'];
  1904. $csv_row[] = $exercises_results['questions_answered'];
  1905. $csv_row[] = $exercises_results['percentage'];
  1906. // first connection
  1907. $csv_row[] = Tracking::get_first_connection_date_on_the_course($user[4], $courseId);
  1908. // last connection
  1909. $csv_row[] = strip_tags(Tracking::get_last_connection_date_on_the_course($user[4], $courseInfo));
  1910. $csv_content[] = $csv_row;
  1911. }
  1912. }
  1913. Export::arrayToCsv($csv_content, 'reporting_user_overview');
  1914. exit;
  1915. }
  1916. /**
  1917. * Get data for courses list in sortable with pagination.
  1918. *
  1919. * @return array
  1920. */
  1921. public static function get_course_data($from, $number_of_items, $column, $direction)
  1922. {
  1923. global $courses, $csv_content, $charset, $session_id;
  1924. // definition database tables
  1925. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1926. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1927. $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1928. $course_data = [];
  1929. $courses_code = array_keys($courses);
  1930. foreach ($courses_code as &$code) {
  1931. $code = "'$code'";
  1932. }
  1933. // get all courses with limit
  1934. $sql = "SELECT course.code as col1, course.title as col2
  1935. FROM $tbl_course course
  1936. WHERE course.code IN (".implode(',', $courses_code).")";
  1937. if (!in_array($direction, ['ASC', 'DESC'])) {
  1938. $direction = 'ASC';
  1939. }
  1940. $column = intval($column);
  1941. $from = intval($from);
  1942. $number_of_items = intval($number_of_items);
  1943. $sql .= " ORDER BY col$column $direction ";
  1944. $sql .= " LIMIT $from,$number_of_items";
  1945. $res = Database::query($sql);
  1946. while ($row_course = Database::fetch_row($res)) {
  1947. $course_code = $row_course[0];
  1948. $courseInfo = api_get_course_info($course_code);
  1949. $courseId = $courseInfo['real_id'];
  1950. $avg_assignments_in_course = $avg_messages_in_course = $nb_students_in_course = $avg_progress_in_course = $avg_score_in_course = $avg_time_spent_in_course = $avg_score_in_exercise = 0;
  1951. // students directly subscribed to the course
  1952. if (empty($session_id)) {
  1953. $sql = "SELECT user_id
  1954. FROM $tbl_course_user as course_rel_user
  1955. WHERE
  1956. course_rel_user.status='5' AND
  1957. course_rel_user.c_id = '$courseId'";
  1958. } else {
  1959. $sql = "SELECT user_id FROM $tbl_session_course_user srcu
  1960. WHERE
  1961. c_id = '$courseId' AND
  1962. session_id = '$session_id' AND
  1963. status<>2";
  1964. }
  1965. $rs = Database::query($sql);
  1966. $users = [];
  1967. while ($row = Database::fetch_array($rs)) {
  1968. $users[] = $row['user_id'];
  1969. }
  1970. if (count($users) > 0) {
  1971. $nb_students_in_course = count($users);
  1972. $avg_assignments_in_course = Tracking::count_student_assignments($users, $course_code, $session_id);
  1973. $avg_messages_in_course = Tracking::count_student_messages($users, $course_code, $session_id);
  1974. $avg_progress_in_course = Tracking::get_avg_student_progress($users, $course_code, [], $session_id);
  1975. $avg_score_in_course = Tracking::get_avg_student_score($users, $course_code, [], $session_id);
  1976. $avg_score_in_exercise = Tracking::get_avg_student_exercise_score($users, $course_code, 0, $session_id);
  1977. $avg_time_spent_in_course = Tracking::get_time_spent_on_the_course(
  1978. $users,
  1979. $courseInfo['real_id'],
  1980. $session_id
  1981. );
  1982. $avg_progress_in_course = round($avg_progress_in_course / $nb_students_in_course, 2);
  1983. if (is_numeric($avg_score_in_course)) {
  1984. $avg_score_in_course = round($avg_score_in_course / $nb_students_in_course, 2);
  1985. }
  1986. $avg_time_spent_in_course = api_time_to_hms($avg_time_spent_in_course / $nb_students_in_course);
  1987. } else {
  1988. $avg_time_spent_in_course = null;
  1989. $avg_progress_in_course = null;
  1990. $avg_score_in_course = null;
  1991. $avg_score_in_exercise = null;
  1992. $avg_messages_in_course = null;
  1993. $avg_assignments_in_course = null;
  1994. }
  1995. $table_row = [];
  1996. $table_row[] = $row_course[1];
  1997. $table_row[] = $nb_students_in_course;
  1998. $table_row[] = $avg_time_spent_in_course;
  1999. $table_row[] = is_null($avg_progress_in_course) ? '' : $avg_progress_in_course.'%';
  2000. $table_row[] = is_null($avg_score_in_course) ? '' : $avg_score_in_course.'%';
  2001. $table_row[] = is_null($avg_score_in_exercise) ? '' : $avg_score_in_exercise.'%';
  2002. $table_row[] = $avg_messages_in_course;
  2003. $table_row[] = $avg_assignments_in_course;
  2004. //set the "from" value to know if I access the Reporting by the chamilo tab or the course link
  2005. $table_row[] = '<center><a href="../../tracking/courseLog.php?cidReq='.$course_code.'&from=myspace&id_session='.$session_id.'">
  2006. '.Display::return_icon('2rightarrow.png', get_lang('Details')).'
  2007. </a>
  2008. </center>';
  2009. $csv_content[] = [
  2010. api_html_entity_decode($row_course[1], ENT_QUOTES, $charset),
  2011. $nb_students_in_course,
  2012. $avg_time_spent_in_course,
  2013. is_null($avg_progress_in_course) ? null : $avg_progress_in_course.'%',
  2014. is_null($avg_score_in_course) ? null : is_numeric($avg_score_in_course) ? $avg_score_in_course.'%' : $avg_score_in_course,
  2015. is_null($avg_score_in_exercise) ? null : $avg_score_in_exercise.'%',
  2016. $avg_messages_in_course,
  2017. $avg_assignments_in_course,
  2018. ];
  2019. $course_data[] = $table_row;
  2020. }
  2021. return $course_data;
  2022. }
  2023. /**
  2024. * Get the number of users of the platform.
  2025. *
  2026. * @return int
  2027. */
  2028. public static function get_number_of_users_tracking_overview()
  2029. {
  2030. return UserManager::get_number_of_users(0, api_get_current_access_url_id());
  2031. }
  2032. /**
  2033. * Get all the data for the sortable table of the reporting progress of
  2034. * all users and all the courses the user is subscribed to.
  2035. *
  2036. * @param int $from
  2037. * @param int $numberItems
  2038. * @param int $column
  2039. * @param string $direction
  2040. *
  2041. * @return array
  2042. */
  2043. public static function get_user_data_tracking_overview($from, $numberItems, $column, $direction)
  2044. {
  2045. $isWestern = api_is_western_name_order();
  2046. switch ($column) {
  2047. case '0':
  2048. $column = $isWestern ? 'firstname' : 'lastname';
  2049. break;
  2050. }
  2051. $order = [
  2052. "$column $direction",
  2053. ];
  2054. $userList = UserManager::get_user_list([], $order, $from, $numberItems);
  2055. $return = [];
  2056. foreach ($userList as $user) {
  2057. $return[] = [
  2058. '0' => $user['user_id'],
  2059. 'col0' => $user['user_id'],
  2060. ];
  2061. }
  2062. return $return;
  2063. }
  2064. /**
  2065. * Get all information that the user with user_id = $user_data has
  2066. * entered in the additionally defined profile fields.
  2067. *
  2068. * @param int $user_id the id of the user
  2069. *
  2070. * @return array
  2071. *
  2072. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  2073. *
  2074. * @version Dokeos 1.8.6
  2075. *
  2076. * @since November 2008
  2077. */
  2078. public static function get_user_overview_export_extra_fields($user_id)
  2079. {
  2080. // include the user manager
  2081. $data = UserManager::get_extra_user_data($user_id, true);
  2082. return $data;
  2083. }
  2084. /**
  2085. * Checks if a username exist in the DB otherwise it create a "double"
  2086. * i.e. if we look into for jmontoya but the user's name already exist we create the user jmontoya2
  2087. * the return array will be array(username=>'jmontoya', sufix='2').
  2088. *
  2089. * @param string firstname
  2090. * @param string lastname
  2091. * @param string username
  2092. *
  2093. * @return array with the username, the sufix
  2094. *
  2095. * @author Julio Montoya Armas
  2096. */
  2097. public static function make_username($firstname, $lastname, $username, $language = null, $encoding = null)
  2098. {
  2099. // if username exist
  2100. if (!UserManager::is_username_available($username) || empty($username)) {
  2101. $i = 0;
  2102. while (1) {
  2103. if ($i == 0) {
  2104. $sufix = '';
  2105. } else {
  2106. $sufix = $i;
  2107. }
  2108. $desired_username = UserManager::create_username(
  2109. $firstname,
  2110. $lastname
  2111. );
  2112. if (UserManager::is_username_available($desired_username.$sufix)) {
  2113. break;
  2114. } else {
  2115. $i++;
  2116. }
  2117. }
  2118. $username_array = ['username' => $desired_username, 'sufix' => $sufix];
  2119. return $username_array;
  2120. } else {
  2121. $username_array = ['username' => $username, 'sufix' => ''];
  2122. return $username_array;
  2123. }
  2124. }
  2125. /**
  2126. * Checks if there are repeted users in a given array.
  2127. *
  2128. * @param array $usernames list of the usernames in the uploaded file
  2129. * @param array $user_array $user_array['username'] and $user_array['sufix']
  2130. * where suffix is the number part in a login i.e -> jmontoya2
  2131. *
  2132. * @return array with the $usernames array and the $user_array array
  2133. *
  2134. * @author Julio Montoya
  2135. */
  2136. public static function check_user_in_array($usernames, $user_array)
  2137. {
  2138. $user_list = array_keys($usernames);
  2139. $username = $user_array['username'].$user_array['sufix'];
  2140. if (in_array($username, $user_list)) {
  2141. $user_array['sufix'] += $usernames[$username];
  2142. $usernames[$username]++;
  2143. } else {
  2144. $usernames[$username] = 1;
  2145. }
  2146. $result_array = [$usernames, $user_array];
  2147. return $result_array;
  2148. }
  2149. /**
  2150. * Checks whether a username has been already subscribed in a session.
  2151. *
  2152. * @param string $username a given username
  2153. * @param array $course_list the array with the course list id
  2154. * @param int $id_session the session id
  2155. *
  2156. * @return int 0 if the user is not subscribed otherwise it returns the user_id of the given username
  2157. *
  2158. * @author Julio Montoya
  2159. */
  2160. public static function user_available_in_session($username, $course_list, $id_session)
  2161. {
  2162. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  2163. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  2164. $id_session = (int) $id_session;
  2165. $username = Database::escape_string($username);
  2166. foreach ($course_list as $courseId) {
  2167. $courseId = (int) $courseId;
  2168. $sql = " SELECT u.user_id FROM $tbl_session_rel_course_rel_user rel
  2169. INNER JOIN $table_user u
  2170. ON (rel.user_id = u.user_id)
  2171. WHERE
  2172. rel.session_id='$id_session' AND
  2173. u.status='5' AND
  2174. u.username ='$username' AND
  2175. rel.c_id='$courseId'";
  2176. $rs = Database::query($sql);
  2177. if (Database::num_rows($rs) > 0) {
  2178. return Database::result($rs, 0, 0);
  2179. } else {
  2180. return 0;
  2181. }
  2182. }
  2183. return 0;
  2184. }
  2185. /**
  2186. * This function checks whether some users in the uploaded file
  2187. * repeated and creates unique usernames if necesary.
  2188. * A case: Within the file there is an user repeted twice (Julio Montoya / Julio Montoya)
  2189. * and the username fields are empty.
  2190. * Then, this function would create unique usernames based on the first and the last name.
  2191. * Two users wiould be created - jmontoya and jmontoya2.
  2192. * Of course, if in the database there is a user with the name jmontoya,
  2193. * the newly created two users registered would be jmontoya2 and jmontoya3.
  2194. *
  2195. * @param $users list of users
  2196. *
  2197. * @return array
  2198. *
  2199. * @author Julio Montoya Armas
  2200. */
  2201. public static function check_all_usernames($users, $course_list, $id_session)
  2202. {
  2203. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  2204. $usernames = [];
  2205. $new_users = [];
  2206. foreach ($users as $index => $user) {
  2207. $desired_username = [];
  2208. if (empty($user['UserName'])) {
  2209. $desired_username = self::make_username($user['FirstName'], $user['LastName'], '');
  2210. $pre_username = $desired_username['username'].$desired_username['sufix'];
  2211. $user['UserName'] = $pre_username;
  2212. $user['create'] = '1';
  2213. } else {
  2214. if (UserManager::is_username_available($user['UserName'])) {
  2215. $desired_username = self::make_username($user['FirstName'], $user['LastName'], $user['UserName']);
  2216. $user['UserName'] = $desired_username['username'].$desired_username['sufix'];
  2217. $user['create'] = '1';
  2218. } else {
  2219. $is_session_avail = self::user_available_in_session($user['UserName'], $course_list, $id_session);
  2220. if ($is_session_avail == 0) {
  2221. $user_name = $user['UserName'];
  2222. $sql_select = "SELECT user_id FROM $table_user WHERE username ='$user_name' ";
  2223. $rs = Database::query($sql_select);
  2224. $user['create'] = Database::result($rs, 0, 0);
  2225. } else {
  2226. $user['create'] = $is_session_avail;
  2227. }
  2228. }
  2229. }
  2230. // Usernames is the current list of users in the file.
  2231. $result_array = self::check_user_in_array($usernames, $desired_username);
  2232. $usernames = $result_array[0];
  2233. $desired_username = $result_array[1];
  2234. $user['UserName'] = $desired_username['username'].$desired_username['sufix'];
  2235. $new_users[] = $user;
  2236. }
  2237. return $new_users;
  2238. }
  2239. /**
  2240. * This functions checks whether there are users that are already
  2241. * registered in the DB by different creator than the current coach.
  2242. *
  2243. * @param array $users
  2244. *
  2245. * @return array
  2246. *
  2247. * @author Julio Montoya Armas
  2248. */
  2249. public static function get_user_creator($users)
  2250. {
  2251. $errors = [];
  2252. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  2253. foreach ($users as $index => $user) {
  2254. $username = Database::escape_string($user['UserName']);
  2255. $sql = "SELECT creator_id FROM $table_user WHERE username='$username' ";
  2256. $rs = Database::query($sql);
  2257. $creator_id = Database::result($rs, 0, 0);
  2258. // check if we are the creators or not
  2259. if ($creator_id != '') {
  2260. if ($creator_id != api_get_user_id()) {
  2261. $user['error'] = get_lang('UserAlreadyRegisteredByOtherCreator');
  2262. $errors[] = $user;
  2263. }
  2264. }
  2265. }
  2266. return $errors;
  2267. }
  2268. /**
  2269. * Validates imported data.
  2270. *
  2271. * @param array $users list of users
  2272. */
  2273. public static function validate_data($users, $id_session = null)
  2274. {
  2275. $errors = [];
  2276. $new_users = [];
  2277. foreach ($users as $index => $user) {
  2278. // 1. Check whether mandatory fields are set.
  2279. $mandatory_fields = ['LastName', 'FirstName'];
  2280. if (api_get_setting('registration', 'email') == 'true') {
  2281. $mandatory_fields[] = 'Email';
  2282. }
  2283. foreach ($mandatory_fields as $key => $field) {
  2284. if (!isset($user[$field]) || strlen($user[$field]) == 0) {
  2285. $user['error'] = get_lang($field.'Mandatory');
  2286. $errors[] = $user;
  2287. }
  2288. }
  2289. // 2. Check whether the username is too long.
  2290. if (UserManager::is_username_too_long($user['UserName'])) {
  2291. $user['error'] = get_lang('UserNameTooLong');
  2292. $errors[] = $user;
  2293. }
  2294. $user['UserName'] = trim($user['UserName']);
  2295. if (empty($user['UserName'])) {
  2296. $user['UserName'] = UserManager::create_username($user['FirstName'], $user['LastName']);
  2297. }
  2298. $new_users[] = $user;
  2299. }
  2300. $results = ['errors' => $errors, 'users' => $new_users];
  2301. return $results;
  2302. }
  2303. /**
  2304. * Adds missing user-information (which isn't required, like password, etc).
  2305. */
  2306. public static function complete_missing_data($user)
  2307. {
  2308. // 1. Generate a password if it is necessary.
  2309. if (!isset($user['Password']) || strlen($user['Password']) == 0) {
  2310. $user['Password'] = api_generate_password();
  2311. }
  2312. return $user;
  2313. }
  2314. /**
  2315. * Saves imported data.
  2316. */
  2317. public static function save_data($users, $course_list, $id_session)
  2318. {
  2319. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  2320. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  2321. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  2322. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  2323. $id_session = (int) $id_session;
  2324. $sendMail = $_POST['sendMail'] ? 1 : 0;
  2325. // Adding users to the platform.
  2326. $new_users = [];
  2327. foreach ($users as $index => $user) {
  2328. $user = self::complete_missing_data($user);
  2329. // coach only will registered users
  2330. $default_status = STUDENT;
  2331. if ($user['create'] == COURSEMANAGER) {
  2332. $user['id'] = UserManager:: create_user(
  2333. $user['FirstName'],
  2334. $user['LastName'],
  2335. $default_status,
  2336. $user['Email'],
  2337. $user['UserName'],
  2338. $user['Password'],
  2339. $user['OfficialCode'],
  2340. api_get_setting('PlatformLanguage'),
  2341. $user['PhoneNumber'],
  2342. ''
  2343. );
  2344. $user['added_at_platform'] = 1;
  2345. } else {
  2346. $user['id'] = $user['create'];
  2347. $user['added_at_platform'] = 0;
  2348. }
  2349. $new_users[] = $user;
  2350. }
  2351. // Update user list.
  2352. $users = $new_users;
  2353. // Inserting users.
  2354. foreach ($course_list as $enreg_course) {
  2355. $nbr_users = 0;
  2356. $new_users = [];
  2357. $enreg_course = Database::escape_string($enreg_course);
  2358. foreach ($users as $index => $user) {
  2359. $userid = (int) $user['id'];
  2360. $sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user(session_id, c_id, user_id)
  2361. VALUES('$id_session','$enreg_course','$userid')";
  2362. $result = Database::query($sql);
  2363. if (Database::affected_rows($result)) {
  2364. $nbr_users++;
  2365. }
  2366. $new_users[] = $user;
  2367. }
  2368. //update the nbr_users field
  2369. $sql_select = "SELECT COUNT(user_id) as nbUsers FROM $tbl_session_rel_course_rel_user
  2370. WHERE session_id='$id_session' AND c_id='$enreg_course'";
  2371. $rs = Database::query($sql_select);
  2372. list($nbr_users) = Database::fetch_array($rs);
  2373. $sql_update = "UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users
  2374. WHERE session_id='$id_session' AND c_id='$enreg_course'";
  2375. Database::query($sql_update);
  2376. $sql_update = "UPDATE $tbl_session SET nbr_users= '$nbr_users' WHERE id='$id_session'";
  2377. Database::query($sql_update);
  2378. }
  2379. $new_users = [];
  2380. foreach ($users as $index => $user) {
  2381. $userid = $user['id'];
  2382. $sql_insert = "INSERT IGNORE INTO $tbl_session_rel_user(session_id, user_id, registered_at)
  2383. VALUES ('$id_session','$userid', '".api_get_utc_datetime()."')";
  2384. Database::query($sql_insert);
  2385. $user['added_at_session'] = 1;
  2386. $new_users[] = $user;
  2387. }
  2388. $users = $new_users;
  2389. $registered_users = get_lang('FileImported').'<br /> Import file results : <br />';
  2390. // Sending emails.
  2391. $addedto = '';
  2392. if ($sendMail) {
  2393. foreach ($users as $index => $user) {
  2394. $emailsubject = '['.api_get_setting('siteName').'] '.get_lang('YourReg').' '.api_get_setting('siteName');
  2395. $emailbody = get_lang('Dear').' '.
  2396. api_get_person_name($user['FirstName'], $user['LastName']).",\n\n".
  2397. get_lang('YouAreReg')." ".api_get_setting('siteName')." ".get_lang('WithTheFollowingSettings')."\n\n".
  2398. get_lang('Username')." : $user[UserName]\n".
  2399. get_lang('Pass')." : $user[Password]\n\n".
  2400. get_lang('Address')." ".api_get_setting('siteName')." ".get_lang('Is')." : ".api_get_path(WEB_PATH)." \n\n".
  2401. get_lang('Problem')."\n\n".
  2402. get_lang('SignatureFormula').",\n\n".
  2403. api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname'))."\n".
  2404. get_lang('Manager')." ".api_get_setting('siteName')."\nT. ".
  2405. api_get_setting('administratorTelephone')."\n".get_lang('Email')." : ".api_get_setting('emailAdministrator');
  2406. api_mail_html(
  2407. api_get_person_name($user['FirstName'], $user['LastName'], null, PERSON_NAME_EMAIL_ADDRESS),
  2408. $user['Email'],
  2409. $emailsubject,
  2410. $emailbody
  2411. );
  2412. $userInfo = api_get_user_info($user['id']);
  2413. if (($user['added_at_platform'] == 1 && $user['added_at_session'] == 1) || $user['added_at_session'] == 1) {
  2414. if ($user['added_at_platform'] == 1) {
  2415. $addedto = get_lang('UserCreatedPlatform');
  2416. } else {
  2417. $addedto = ' ';
  2418. }
  2419. if ($user['added_at_session'] == 1) {
  2420. $addedto .= get_lang('UserInSession');
  2421. }
  2422. } else {
  2423. $addedto = get_lang('UserNotAdded');
  2424. }
  2425. $registered_users .= UserManager::getUserProfileLink($userInfo).' - '.$addedto.'<br />';
  2426. }
  2427. } else {
  2428. foreach ($users as $index => $user) {
  2429. $userInfo = api_get_user_info($user['id']);
  2430. if (($user['added_at_platform'] == 1 && $user['added_at_session'] == 1) || $user['added_at_session'] == 1) {
  2431. if ($user['added_at_platform'] == 1) {
  2432. $addedto = get_lang('UserCreatedPlatform');
  2433. } else {
  2434. $addedto = ' ';
  2435. }
  2436. if ($user['added_at_session'] == 1) {
  2437. $addedto .= ' '.get_lang('UserInSession');
  2438. }
  2439. } else {
  2440. $addedto = get_lang('UserNotAdded');
  2441. }
  2442. $registered_users .= "<a href=\"../user/userInfo.php?uInfo=".$user['id']."\">".
  2443. Security::remove_XSS($userInfo['complete_user_name'])."</a> - ".$addedto.'<br />';
  2444. }
  2445. }
  2446. Display::addFlash(Display::return_message($registered_users, 'normal', false));
  2447. header('Location: course.php?id_session='.$id_session);
  2448. exit;
  2449. }
  2450. /**
  2451. * Reads CSV-file.
  2452. *
  2453. * @param string $file Path to the CSV-file
  2454. *
  2455. * @return array All userinformation read from the file
  2456. */
  2457. public function parse_csv_data($file)
  2458. {
  2459. $users = Import::csvToArray($file);
  2460. foreach ($users as $index => $user) {
  2461. if (isset($user['Courses'])) {
  2462. $user['Courses'] = explode('|', trim($user['Courses']));
  2463. }
  2464. $users[$index] = $user;
  2465. }
  2466. return $users;
  2467. }
  2468. /**
  2469. * Reads XML-file.
  2470. *
  2471. * @param string $file Path to the XML-file
  2472. *
  2473. * @return array All userinformation read from the file
  2474. */
  2475. public static function parse_xml_data($file)
  2476. {
  2477. $crawler = new \Symfony\Component\DomCrawler\Crawler();
  2478. $crawler->addXmlContent(file_get_contents($file));
  2479. $crawler = $crawler->filter('Contacts > Contact ');
  2480. $array = [];
  2481. foreach ($crawler as $domElement) {
  2482. $row = [];
  2483. foreach ($domElement->childNodes as $node) {
  2484. if ($node->nodeName != '#text') {
  2485. $row[$node->nodeName] = $node->nodeValue;
  2486. }
  2487. }
  2488. if (!empty($row)) {
  2489. $array[] = $row;
  2490. }
  2491. }
  2492. return $array;
  2493. }
  2494. /**
  2495. * @param int $courseId
  2496. * @param int $sessionId
  2497. * @param int $studentId
  2498. */
  2499. public static function displayTrackingAccessOverView($courseId, $sessionId, $studentId)
  2500. {
  2501. $courseId = (int) $courseId;
  2502. $sessionId = (int) $sessionId;
  2503. $studentId = (int) $studentId;
  2504. $courseList = [];
  2505. $sessionList = [];
  2506. $studentList = [];
  2507. if (!empty($courseId)) {
  2508. $course = api_get_course_entity($courseId);
  2509. if ($course) {
  2510. $courseList[$course->getId()] = $course->getTitle();
  2511. }
  2512. }
  2513. if (!empty($sessionId)) {
  2514. $session = api_get_session_entity($sessionId);
  2515. if ($session) {
  2516. $sessionList[$session->getId()] = $session->getName();
  2517. }
  2518. }
  2519. if (!empty($studentId)) {
  2520. $student = api_get_user_entity($studentId);
  2521. if ($student) {
  2522. $studentList[$student->getId()] = $student->getCompleteName();
  2523. }
  2524. }
  2525. $form = new FormValidator('access_overview', 'GET');
  2526. $form->addElement(
  2527. 'select_ajax',
  2528. 'course_id',
  2529. get_lang('SearchCourse'),
  2530. $courseList,
  2531. [
  2532. 'url' => api_get_path(WEB_AJAX_PATH).'course.ajax.php?'.http_build_query([
  2533. 'a' => 'search_course_by_session_all',
  2534. 'session_id' => $sessionId,
  2535. 'course_id' => $courseId,
  2536. ]),
  2537. ]
  2538. );
  2539. $form->addElement(
  2540. 'select_ajax',
  2541. 'session_id',
  2542. get_lang('SearchSession'),
  2543. $sessionList,
  2544. [
  2545. 'url_function' => "
  2546. function () {
  2547. var params = $.param({
  2548. a: 'search_session_by_course',
  2549. course_id: $('#access_overview_course_id').val() || 0
  2550. });
  2551. return '".api_get_path(WEB_AJAX_PATH)."session.ajax.php?' + params;
  2552. }
  2553. ",
  2554. ]
  2555. );
  2556. $form->addSelect(
  2557. 'profile',
  2558. get_lang('Profile'),
  2559. [
  2560. '' => get_lang('Select'),
  2561. STUDENT => get_lang('Student'),
  2562. COURSEMANAGER => get_lang('CourseManager'),
  2563. DRH => get_lang('Drh'),
  2564. ],
  2565. ['id' => 'profile']
  2566. );
  2567. $form->addElement(
  2568. 'select_ajax',
  2569. 'student_id',
  2570. get_lang('SearchUsers'),
  2571. $studentList,
  2572. [
  2573. 'placeholder' => get_lang('All'),
  2574. 'url_function' => "
  2575. function () {
  2576. var params = $.param({
  2577. a: 'search_user_by_course',
  2578. session_id: $('#access_overview_session_id').val(),
  2579. course_id: $('#access_overview_course_id').val()
  2580. });
  2581. return '".api_get_path(WEB_AJAX_PATH)."course.ajax.php?' + params;
  2582. }
  2583. ",
  2584. ]
  2585. );
  2586. $form->addDateRangePicker(
  2587. 'date',
  2588. get_lang('DateRange'),
  2589. true,
  2590. [
  2591. 'id' => 'date_range',
  2592. 'format' => 'YYYY-MM-DD',
  2593. 'timePicker' => 'false',
  2594. 'validate_format' => 'Y-m-d',
  2595. ]
  2596. );
  2597. $form->addHidden('display', 'accessoverview');
  2598. $form->addRule('course_id', get_lang('Required'), 'required');
  2599. $form->addRule('profile', get_lang('Required'), 'required');
  2600. $form->addButton('submit', get_lang('Generate'), 'gear', 'primary');
  2601. $table = null;
  2602. if ($form->validate()) {
  2603. $table = new SortableTable(
  2604. 'tracking_access_overview',
  2605. ['MySpace', 'getNumberOfTrackAccessOverview'],
  2606. ['MySpace', 'getUserDataAccessTrackingOverview'],
  2607. 0
  2608. );
  2609. $table->set_header(0, get_lang('LoginDate'), true);
  2610. $table->set_header(1, get_lang('Username'), true);
  2611. if (api_is_western_name_order()) {
  2612. $table->set_header(2, get_lang('FirstName'), true);
  2613. $table->set_header(3, get_lang('LastName'), true);
  2614. } else {
  2615. $table->set_header(2, get_lang('LastName'), true);
  2616. $table->set_header(3, get_lang('FirstName'), true);
  2617. }
  2618. $table->set_header(4, get_lang('Clicks'), false);
  2619. $table->set_header(5, get_lang('IP'), false);
  2620. $table->set_header(6, get_lang('TimeLoggedIn'), false);
  2621. }
  2622. $template = new Template(
  2623. null,
  2624. false,
  2625. false,
  2626. false,
  2627. false,
  2628. false,
  2629. false
  2630. );
  2631. $template->assign('form', $form->returnForm());
  2632. $template->assign('table', $table ? $table->return_table() : null);
  2633. echo $template->fetch(
  2634. $template->get_template('my_space/accessoverview.tpl')
  2635. );
  2636. }
  2637. /**
  2638. * @return int
  2639. */
  2640. public static function getNumberOfTrackAccessOverview()
  2641. {
  2642. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2643. $sql = "SELECT COUNT(course_access_id) count FROM $table";
  2644. $result = Database::query($sql);
  2645. $row = Database::fetch_assoc($result);
  2646. return $row['count'];
  2647. }
  2648. /**
  2649. * @param $from
  2650. * @param $numberItems
  2651. * @param $column
  2652. * @param $orderDirection
  2653. *
  2654. * @return array
  2655. */
  2656. public static function getUserDataAccessTrackingOverview(
  2657. $from,
  2658. $numberItems,
  2659. $column,
  2660. $orderDirection
  2661. ) {
  2662. $user = Database::get_main_table(TABLE_MAIN_USER);
  2663. $course = Database::get_main_table(TABLE_MAIN_COURSE);
  2664. $track_e_login = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  2665. $track_e_course_access = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2666. global $export_csv;
  2667. $is_western_name_order = api_is_western_name_order();
  2668. if ($export_csv) {
  2669. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  2670. }
  2671. //TODO add course name
  2672. $sql = "SELECT
  2673. a.login_course_date as col0,
  2674. u.username as col1,
  2675. ".(
  2676. $is_western_name_order ? "
  2677. u.firstname AS col2,
  2678. u.lastname AS col3,
  2679. " : "
  2680. u.lastname AS col2,
  2681. u.firstname AS col3,
  2682. "
  2683. )."
  2684. a.logout_course_date,
  2685. c.title,
  2686. c.code,
  2687. u.user_id
  2688. FROM $track_e_course_access a
  2689. INNER JOIN $user u ON a.user_id = u.user_id
  2690. INNER JOIN $course c ON a.c_id = c.id
  2691. WHERE 1=1 ";
  2692. if (isset($_GET['course_id']) && !empty($_GET['course_id'])) {
  2693. $courseId = (int) $_GET['course_id'];
  2694. $sql .= " AND c.id = ".$courseId;
  2695. }
  2696. if (isset($_GET['session_id']) && !empty($_GET['session_id'])) {
  2697. $sessionId = (int) $_GET['session_id'];
  2698. $sql .= " AND a.session_id = ".$sessionId;
  2699. }
  2700. if (isset($_GET['student_id']) && !empty($_GET['student_id'])) {
  2701. $userId = (int) $_GET['student_id'];
  2702. $sql .= " AND u.user_id = ".$userId;
  2703. }
  2704. if (isset($_GET['date']) && !empty($_GET['date'])) {
  2705. $dates = DateRangePicker::parseDateRange($_GET['date']);
  2706. if (isset($dates['start']) && !empty($dates['start'])) {
  2707. $dates['start'] = Database::escape_string($dates['start']);
  2708. $sql .= " AND login_course_date >= '".$dates['start']."'";
  2709. }
  2710. if (isset($dates['end']) && !empty($dates['end'])) {
  2711. $dates['end'] = Database::escape_string($dates['end']);
  2712. $sql .= " AND logout_course_date <= '".$dates['end']."'";
  2713. }
  2714. }
  2715. $sql .= " ORDER BY col$column $orderDirection ";
  2716. $sql .= " LIMIT $from,$numberItems";
  2717. $result = Database::query($sql);
  2718. $data = [];
  2719. while ($user = Database::fetch_assoc($result)) {
  2720. $data[] = $user;
  2721. }
  2722. $return = [];
  2723. //TODO: Dont use numeric index
  2724. foreach ($data as $key => $info) {
  2725. $start_date = $info['col0'];
  2726. $end_date = $info['logout_course_date'];
  2727. $return[$info['user_id']] = [
  2728. $start_date,
  2729. $info['col1'],
  2730. $info['col2'],
  2731. $info['col3'],
  2732. $info['user_id'],
  2733. 'ip',
  2734. //TODO is not correct/precise, it counts the time not logged between two loggins
  2735. gmdate("H:i:s", strtotime($end_date) - strtotime($start_date)),
  2736. ];
  2737. }
  2738. foreach ($return as $key => $info) {
  2739. $ipResult = Database::select(
  2740. 'user_ip',
  2741. $track_e_login,
  2742. ['where' => [
  2743. '? BETWEEN login_date AND logout_date' => $info[0],
  2744. ]],
  2745. 'first'
  2746. );
  2747. $return[$key][5] = $ipResult['user_ip'];
  2748. }
  2749. return $return;
  2750. }
  2751. /**
  2752. * Gets the connections to a course as an array of login and logout time.
  2753. *
  2754. * @param int $user_id
  2755. * @param array $course_info
  2756. * @param int $sessionId
  2757. * @param string $start_date
  2758. * @param string $end_date
  2759. *
  2760. * @author Jorge Frisancho Jibaja
  2761. * @author Julio Montoya <gugli100@gmail.com> fixing the function
  2762. *
  2763. * @version OCT-22- 2010
  2764. *
  2765. * @return array
  2766. */
  2767. public static function get_connections_to_course_by_date(
  2768. $user_id,
  2769. $course_info,
  2770. $sessionId,
  2771. $start_date,
  2772. $end_date
  2773. ) {
  2774. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2775. $user_id = (int) $user_id;
  2776. $connections = [];
  2777. if (!empty($course_info)) {
  2778. $courseId = (int) $course_info['real_id'];
  2779. $end_date = add_day_to($end_date);
  2780. $sessionCondition = api_get_session_condition($sessionId);
  2781. $sql = "SELECT login_course_date, logout_course_date
  2782. FROM $table
  2783. WHERE
  2784. user_id = $user_id AND
  2785. c_id = $courseId AND
  2786. login_course_date BETWEEN '$start_date' AND '$end_date' AND
  2787. logout_course_date BETWEEN '$start_date' AND '$end_date'
  2788. $sessionCondition
  2789. ORDER BY login_course_date ASC";
  2790. $rs = Database::query($sql);
  2791. while ($row = Database::fetch_array($rs)) {
  2792. $connections[] = [
  2793. 'login' => $row['login_course_date'],
  2794. 'logout' => $row['logout_course_date'],
  2795. ];
  2796. }
  2797. }
  2798. return $connections;
  2799. }
  2800. }
  2801. /**
  2802. * @param $user_id
  2803. * @param array $course_info
  2804. * @param int $sessionId
  2805. * @param null $start_date
  2806. * @param null $end_date
  2807. *
  2808. * @return array
  2809. */
  2810. function get_stats($user_id, $course_info, $sessionId, $start_date = null, $end_date = null)
  2811. {
  2812. $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2813. $result = [];
  2814. if (!empty($course_info)) {
  2815. $stringStartDate = '';
  2816. $stringEndDate = '';
  2817. if ($start_date != null && $end_date != null) {
  2818. $end_date = add_day_to($end_date);
  2819. $stringStartDate = "AND login_course_date BETWEEN '$start_date' AND '$end_date'";
  2820. $stringEndDate = "AND logout_course_date BETWEEN '$start_date' AND '$end_date'";
  2821. }
  2822. $user_id = intval($user_id);
  2823. $courseId = intval($course_info['real_id']);
  2824. $sessionCondition = api_get_session_condition($sessionId);
  2825. $sql = "SELECT
  2826. SEC_TO_TIME(AVG(time_to_sec(timediff(logout_course_date,login_course_date)))) as avrg,
  2827. SEC_TO_TIME(SUM(time_to_sec(timediff(logout_course_date,login_course_date)))) as total,
  2828. count(user_id) as times
  2829. FROM $table
  2830. WHERE
  2831. user_id = $user_id AND
  2832. c_id = $courseId $stringStartDate $stringEndDate
  2833. $sessionCondition
  2834. ORDER BY login_course_date ASC";
  2835. $rs = Database::query($sql);
  2836. if ($row = Database::fetch_array($rs)) {
  2837. $foo_avg = $row['avrg'];
  2838. $foo_total = $row['total'];
  2839. $foo_times = $row['times'];
  2840. $result = [
  2841. 'avg' => $foo_avg,
  2842. 'total' => $foo_total,
  2843. 'times' => $foo_times,
  2844. ];
  2845. }
  2846. }
  2847. return $result;
  2848. }
  2849. function add_day_to($end_date)
  2850. {
  2851. $foo_date = strtotime($end_date);
  2852. $foo_date = strtotime(' +1 day', $foo_date);
  2853. $foo_date = date('Y-m-d', $foo_date);
  2854. return $foo_date;
  2855. }
  2856. /**
  2857. * @param array
  2858. *
  2859. * @author Jorge Frisancho Jibaja
  2860. *
  2861. * @version OCT-22- 2010
  2862. *
  2863. * @return array
  2864. */
  2865. function convert_to_array($sql_result)
  2866. {
  2867. $result_to_print = '<table>';
  2868. foreach ($sql_result as $key => $data) {
  2869. $result_to_print .= '<tr><td>'.date('d-m-Y (H:i:s)', $data['login']).'</td><td>'.
  2870. api_time_to_hms($data['logout'] - $data['login']).'</tr></td>'."\n";
  2871. }
  2872. $result_to_print .= '</table>';
  2873. $result_to_print = ['result' => $result_to_print];
  2874. return $result_to_print;
  2875. }
  2876. /**
  2877. * Converte an array to a table in html.
  2878. *
  2879. * @param array $result
  2880. *
  2881. * @author Jorge Frisancho Jibaja
  2882. *
  2883. * @version OCT-22- 2010
  2884. *
  2885. * @return string
  2886. */
  2887. function convert_to_string($result)
  2888. {
  2889. $html = '<table class="table">';
  2890. if (!empty($result)) {
  2891. foreach ($result as $key => $data) {
  2892. $html .= '<tr><td>';
  2893. $html .= api_get_local_time($data['login']);
  2894. $html .= '</td>';
  2895. $html .= '<td>';
  2896. $html .= api_time_to_hms(api_strtotime($data['logout']) - api_strtotime($data['login']));
  2897. $html .= '</tr></td>';
  2898. }
  2899. }
  2900. $html .= '</table>';
  2901. return $html;
  2902. }
  2903. /**
  2904. * This function draw the graphic to be displayed on the user view as an image.
  2905. *
  2906. * @param array $sql_result
  2907. * @param string $start_date
  2908. * @param string $end_date
  2909. * @param string $type
  2910. *
  2911. * @author Jorge Frisancho Jibaja
  2912. *
  2913. * @version OCT-22- 2010
  2914. *
  2915. * @return string
  2916. */
  2917. function grapher($sql_result, $start_date, $end_date, $type = '')
  2918. {
  2919. if (empty($start_date)) {
  2920. $start_date = '';
  2921. }
  2922. if (empty($end_date)) {
  2923. $end_date = '';
  2924. }
  2925. if ($type == '') {
  2926. $type = 'day';
  2927. }
  2928. $main_year = $main_month_year = $main_day = [];
  2929. $period = new DatePeriod(
  2930. new DateTime($start_date),
  2931. new DateInterval('P1D'),
  2932. new DateTime($end_date)
  2933. );
  2934. foreach ($period as $date) {
  2935. $main_day[$date->format('d-m-Y')] = 0;
  2936. }
  2937. $period = new DatePeriod(
  2938. new DateTime($start_date),
  2939. new DateInterval('P1M'),
  2940. new DateTime($end_date)
  2941. );
  2942. foreach ($period as $date) {
  2943. $main_month_year[$date->format('m-Y')] = 0;
  2944. }
  2945. $i = 0;
  2946. if (is_array($sql_result) && count($sql_result) > 0) {
  2947. foreach ($sql_result as $key => $data) {
  2948. $login = api_strtotime($data['login']);
  2949. $logout = api_strtotime($data['logout']);
  2950. //creating the main array
  2951. if (isset($main_month_year[date('m-Y', $login)])) {
  2952. $main_month_year[date('m-Y', $login)] += float_format(($logout - $login) / 60, 0);
  2953. }
  2954. if (isset($main_day[date('d-m-Y', $login)])) {
  2955. $main_day[date('d-m-Y', $login)] += float_format(($logout - $login) / 60, 0);
  2956. }
  2957. if ($i > 500) {
  2958. break;
  2959. }
  2960. $i++;
  2961. }
  2962. switch ($type) {
  2963. case 'day':
  2964. $main_date = $main_day;
  2965. break;
  2966. case 'month':
  2967. $main_date = $main_month_year;
  2968. break;
  2969. case 'year':
  2970. $main_date = $main_year;
  2971. break;
  2972. }
  2973. $labels = array_keys($main_date);
  2974. if (count($main_date) == 1) {
  2975. $labels = $labels[0];
  2976. $main_date = $main_date[$labels];
  2977. }
  2978. /* Create and populate the pData object */
  2979. $myData = new pData();
  2980. $myData->addPoints($main_date, 'Serie1');
  2981. if (count($main_date) != 1) {
  2982. $myData->addPoints($labels, 'Labels');
  2983. $myData->setSerieDescription('Labels', 'Months');
  2984. $myData->setAbscissa('Labels');
  2985. }
  2986. $myData->setSerieWeight('Serie1', 1);
  2987. $myData->setSerieDescription('Serie1', get_lang('MyResults'));
  2988. $myData->setAxisName(0, get_lang('Minutes'));
  2989. $myData->loadPalette(api_get_path(SYS_CODE_PATH).'palettes/pchart/default.color', true);
  2990. // Cache definition
  2991. $cachePath = api_get_path(SYS_ARCHIVE_PATH);
  2992. $myCache = new pCache(['CacheFolder' => substr($cachePath, 0, strlen($cachePath) - 1)]);
  2993. $chartHash = $myCache->getHash($myData);
  2994. if ($myCache->isInCache($chartHash)) {
  2995. //if we already created the img
  2996. $imgPath = api_get_path(SYS_ARCHIVE_PATH).$chartHash;
  2997. $myCache->saveFromCache($chartHash, $imgPath);
  2998. $imgPath = api_get_path(WEB_ARCHIVE_PATH).$chartHash;
  2999. } else {
  3000. /* Define width, height and angle */
  3001. $mainWidth = 760;
  3002. $mainHeight = 230;
  3003. $angle = 50;
  3004. /* Create the pChart object */
  3005. $myPicture = new pImage($mainWidth, $mainHeight, $myData);
  3006. /* Turn of Antialiasing */
  3007. $myPicture->Antialias = false;
  3008. /* Draw the background */
  3009. $settings = ["R" => 255, "G" => 255, "B" => 255];
  3010. $myPicture->drawFilledRectangle(0, 0, $mainWidth, $mainHeight, $settings);
  3011. /* Add a border to the picture */
  3012. $myPicture->drawRectangle(
  3013. 0,
  3014. 0,
  3015. $mainWidth - 1,
  3016. $mainHeight - 1,
  3017. ["R" => 0, "G" => 0, "B" => 0]
  3018. );
  3019. /* Set the default font */
  3020. $myPicture->setFontProperties(
  3021. [
  3022. "FontName" => api_get_path(SYS_FONTS_PATH).'opensans/OpenSans-Regular.ttf',
  3023. "FontSize" => 10, ]
  3024. );
  3025. /* Write the chart title */
  3026. $myPicture->drawText(
  3027. $mainWidth / 2,
  3028. 30,
  3029. get_lang('TimeSpentInTheCourse'),
  3030. [
  3031. "FontSize" => 12,
  3032. "Align" => TEXT_ALIGN_BOTTOMMIDDLE,
  3033. ]
  3034. );
  3035. /* Set the default font */
  3036. $myPicture->setFontProperties(
  3037. [
  3038. "FontName" => api_get_path(SYS_FONTS_PATH).'opensans/OpenSans-Regular.ttf',
  3039. "FontSize" => 8,
  3040. ]
  3041. );
  3042. /* Define the chart area */
  3043. $myPicture->setGraphArea(50, 40, $mainWidth - 40, $mainHeight - 80);
  3044. /* Draw the scale */
  3045. $scaleSettings = [
  3046. 'XMargin' => 10,
  3047. 'YMargin' => 10,
  3048. 'Floating' => true,
  3049. 'GridR' => 200,
  3050. 'GridG' => 200,
  3051. 'GridB' => 200,
  3052. 'DrawSubTicks' => true,
  3053. 'CycleBackground' => true,
  3054. 'LabelRotation' => $angle,
  3055. 'Mode' => SCALE_MODE_ADDALL_START0,
  3056. ];
  3057. $myPicture->drawScale($scaleSettings);
  3058. /* Turn on Antialiasing */
  3059. $myPicture->Antialias = true;
  3060. /* Enable shadow computing */
  3061. $myPicture->setShadow(
  3062. true,
  3063. [
  3064. "X" => 1,
  3065. "Y" => 1,
  3066. "R" => 0,
  3067. "G" => 0,
  3068. "B" => 0,
  3069. "Alpha" => 10,
  3070. ]
  3071. );
  3072. /* Draw the line chart */
  3073. $myPicture->setFontProperties(
  3074. [
  3075. "FontName" => api_get_path(SYS_FONTS_PATH).'opensans/OpenSans-Regular.ttf',
  3076. "FontSize" => 10,
  3077. ]
  3078. );
  3079. $myPicture->drawSplineChart();
  3080. $myPicture->drawPlotChart(
  3081. [
  3082. "DisplayValues" => true,
  3083. "PlotBorder" => true,
  3084. "BorderSize" => 1,
  3085. "Surrounding" => -60,
  3086. "BorderAlpha" => 80,
  3087. ]
  3088. );
  3089. /* Do NOT Write the chart legend */
  3090. /* Write and save into cache */
  3091. $myCache->writeToCache($chartHash, $myPicture);
  3092. $imgPath = api_get_path(SYS_ARCHIVE_PATH).$chartHash;
  3093. $myCache->saveFromCache($chartHash, $imgPath);
  3094. $imgPath = api_get_path(WEB_ARCHIVE_PATH).$chartHash;
  3095. }
  3096. $html = '<img src="'.$imgPath.'">';
  3097. return $html;
  3098. } else {
  3099. $foo_img = api_convert_encoding(
  3100. '<div id="messages" class="warning-message">'.get_lang('GraphicNotAvailable').'</div>',
  3101. 'UTF-8'
  3102. );
  3103. return $foo_img;
  3104. }
  3105. }