myspace.lib.php 125 KB

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