myspace.lib.php 110 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. require_once api_get_path(LIBRARY_PATH).'export.lib.inc.php';
  4. require_once api_get_path(LIBRARY_PATH).'tracking.lib.php';
  5. /**
  6. * Class MySpace
  7. * @package chamilo.reporting
  8. */
  9. class MySpace
  10. {
  11. /**
  12. * This function serves exporting data in CSV format.
  13. * @param array $header The header labels.
  14. * @param array $data The data array.
  15. * @param string $file_name The name of the file which contains exported data.
  16. * @return string mixed Returns a message (string) if an error occurred.
  17. */
  18. function export_csv($header, $data, $file_name = 'export.csv') {
  19. $archive_path = api_get_path(SYS_ARCHIVE_PATH);
  20. $archive_url = api_get_path(WEB_CODE_PATH).'course_info/download.php?archive=';
  21. if (!$open = fopen($archive_path.$file_name, 'w+')) {
  22. $message = get_lang('noOpen');
  23. } else {
  24. $info = '';
  25. foreach ($header as $value) {
  26. $info .= $value.';';
  27. }
  28. $info .= "\r\n";
  29. foreach ($data as $row) {
  30. foreach ($row as $value) {
  31. $info .= $value.';';
  32. }
  33. $info .= "\r\n";
  34. }
  35. fwrite($open, $info);
  36. fclose($open);
  37. @chmod($file_name, api_get_permissions_for_new_files());
  38. header("Location:".$archive_url.$file_name);
  39. }
  40. return $message;
  41. }
  42. /**
  43. * Gets the connections to a course as an array of login and logout time
  44. *
  45. * @param int User ud
  46. * @param string Course code
  47. * @param int Session id (optional, default = 0)
  48. * @return array Conections
  49. */
  50. static function get_connections_to_course($user_id, $course_code, $session_id = 0)
  51. {
  52. // Database table definitions
  53. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  54. // protect data
  55. $user_id = intval($user_id);
  56. $course_code = Database::escape_string($course_code);
  57. $session_id = intval($session_id);
  58. $sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . '
  59. WHERE user_id = '.$user_id.' AND
  60. course_code="'.$course_code.'" AND
  61. session_id = '.$session_id.'
  62. ORDER BY login_course_date ASC';
  63. $rs = Database::query($sql);
  64. $connections = array();
  65. while ($row = Database::fetch_array($rs)) {
  66. $timestamp_login_date = api_strtotime($row['login_course_date'], 'UTC');
  67. $timestamp_logout_date = api_strtotime($row['logout_course_date'], 'UTC');
  68. $connections[] = array('login' => $timestamp_login_date, 'logout' => $timestamp_logout_date);
  69. }
  70. return $connections;
  71. }
  72. static function get_connections_from_course_list($user_id, $course_list, $session_id = 0) {
  73. // Database table definitions
  74. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  75. if (empty($course_list)) {
  76. return false;
  77. }
  78. // protect data
  79. $user_id = intval($user_id);
  80. $course_code = Database::escape_string($course_code);
  81. $session_id = intval($session_id);
  82. $new_course_list = array();;
  83. foreach ($course_list as $course_item) {
  84. $new_course_list[] = '"'.Database::escape_string($course_item['code']).'"';
  85. }
  86. $course_list = implode(', ', $new_course_list);
  87. if (empty($course_list)) {
  88. return false;
  89. }
  90. $sql = 'SELECT login_course_date, logout_course_date, course_code FROM ' . $tbl_track_course . '
  91. WHERE user_id = '.$user_id.' AND
  92. course_code IN ('.$course_list.') AND
  93. session_id = '.$session_id.'
  94. ORDER BY login_course_date ASC';
  95. $rs = Database::query($sql);
  96. $connections = array();
  97. while ($row = Database::fetch_array($rs)) {
  98. $timestamp_login_date = api_strtotime($row['login_course_date'], 'UTC');
  99. $timestamp_logout_date = api_strtotime($row['logout_course_date'], 'UTC');
  100. $connections[] = array('login' => $timestamp_login_date, 'logout' => $timestamp_logout_date,'course_code' => $row['course_code']);
  101. }
  102. return $connections;
  103. }
  104. /**
  105. * TODO: Not used, to b deleted?
  106. * Enter description here...
  107. * @param int $user_id
  108. * @param string $course_code
  109. * @param date $year
  110. * @param date $month
  111. * @param date $day
  112. * @return unknown
  113. */
  114. static function get_connections_to_course_by_time($user_id, $course_code, $year = '', $month = '', $day = '') {
  115. // Database table definitions
  116. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  117. $sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . '
  118. WHERE user_id = ' . intval($user_id) . '
  119. AND course_code="' . Database::escape_string($course_code) . '"
  120. ORDER BY login_course_date DESC';
  121. $rs = Database::query($sql);
  122. $connections = array();
  123. while ($row = Database::fetch_array($rs)) {
  124. $login_date = $row['login_course_date'];
  125. $logout_date = $row['logout_course_date'];
  126. $timestamp_login_date = strtotime($login_date);
  127. $timestamp_logout_date = strtotime($logout_date);
  128. $connections[] = array('login' => $timestamp_login_date, 'logout' => $timestamp_logout_date);
  129. }
  130. return $connections;
  131. }
  132. /**
  133. * Creates a small table in the last column of the table with the user overview
  134. *
  135. * @param integer $user_id the id of the user
  136. * @param array $url_params additonal url parameters
  137. * @param array $row the row information (the other columns)
  138. * @return string html code
  139. */
  140. function course_info_tracking_filter($user_id, $url_params, $row) {
  141. // the table header
  142. $return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  143. /*$return .= ' <tr>';
  144. $return .= ' <th>'.get_lang('Course').'</th>';
  145. $return .= ' <th>'.get_lang('AvgTimeSpentInTheCourse').'</th>';
  146. $return .= ' <th>'.get_lang('AvgStudentsProgress').'</th>';
  147. $return .= ' <th>'.get_lang('AvgCourseScore').'</th>';
  148. $return .= ' <th>'.get_lang('AvgExercisesScore').'</th>';
  149. $return .= ' <th>'.get_lang('AvgMessages').'</th>';
  150. $return .= ' <th>'.get_lang('AvgAssignments').'</th>';
  151. $return .= ' <th>'.get_lang('TotalExercisesScoreObtained').'</th>';
  152. $return .= ' <th>'.get_lang('TotalExercisesScorePossible').'</th>';
  153. $return .= ' <th>'.get_lang('TotalExercisesAnswered').'</th>';
  154. $return .= ' <th>'.get_lang('TotalExercisesScorePercentage').'</th>';
  155. $return .= ' <th>'.get_lang('FirstLogin').'</th>';
  156. $return .= ' <th>'.get_lang('LatestLogin').'</th>';
  157. $return .= ' </tr>';*/
  158. // database table definition
  159. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  160. // getting all the courses of the user
  161. $sql = "SELECT * FROM $tbl_course_user WHERE user_id = '".Database::escape_string($user_id)."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  162. $result = Database::query($sql);
  163. while ($row = Database::fetch_row($result)) {
  164. $return .= '<tr>';
  165. // course code
  166. $return .= ' <td width="157px" >'.cut($row[0], 20, true).'</td>';
  167. // time spent in the course
  168. $return .= ' <td><div>'.api_time_to_hms(Tracking :: get_time_spent_on_the_course($user_id, $row[0])).'</div></td>';
  169. // student progress in course
  170. $return .= ' <td><div>'.round(Tracking :: get_avg_student_progress($user_id, $row[0]), 2).'</div></td>';
  171. // student score
  172. $avg_score = Tracking :: get_avg_student_score($user_id, $row[0]);
  173. if (is_numeric($avg_score)) {
  174. $avg_score = round($avg_score,2);
  175. } else {
  176. $$avg_score = '-';
  177. }
  178. $return .= ' <td><div>'.$avg_score.'</div></td>';
  179. // student tes score
  180. //$return .= ' <td><div style="width:40px">'.round(Tracking :: get_avg_student_exercise_score ($user_id, $row[0]),2).'%</div></td>';
  181. // student messages
  182. $return .= ' <td><div>'.Tracking :: count_student_messages($user_id, $row[0]).'</div></td>';
  183. // student assignments
  184. $return .= ' <td><div>'.Tracking :: count_student_assignments($user_id, $row[0]).'</div></td>';
  185. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  186. $exercises_results = MySpace::exercises_results($user_id, $row[0]);
  187. $return .= ' <td width="105px"><div>'.(is_null($exercises_results['percentage']) ? '' : $exercises_results['score_obtained'].'/'.$exercises_results['score_possible'].' ( '.$exercises_results['percentage'].'% )').'</div></td>';
  188. //$return .= ' <td><div>'.$exercises_results['score_possible'].'</div></td>';
  189. $return .= ' <td><div>'.$exercises_results['questions_answered'].'</div></td>';
  190. //$return .= ' <td><div>'.$exercises_results['percentage'].'% </div></td>';
  191. // first connection
  192. //$return .= ' <td width="60px">'.Tracking :: get_first_connection_date_on_the_course ($user_id, $row[0]).'</td>';
  193. // last connection
  194. $return .= ' <td><div>'.Tracking :: get_last_connection_date_on_the_course ($user_id, $row[0]).'</div></td>';
  195. $return .= '<tr>';
  196. }
  197. $return .= '</table>';
  198. return $return;
  199. }
  200. /**
  201. * Display a sortable table that contains an overview off all the reporting progress of all users and all courses the user is subscribed to
  202. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  203. * @version Dokeos 1.8.6
  204. * @since October 2008
  205. */
  206. function display_tracking_user_overview() {
  207. MySpace::display_user_overview_export_options();
  208. $t_head .= ' <table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  209. //$t_head .= ' <caption>'.get_lang('CourseInformation').'</caption>';
  210. $t_head .= '<tr>';
  211. $t_head .= ' <th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>';
  212. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  213. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  214. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  215. //$t_head .= ' <th><div style="width:40px">'.get_lang('AvgExercisesScore').'</div></th>';
  216. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  217. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  218. $t_head .= ' <th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  219. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePossible').'</div></th>';
  220. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  221. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePercentage').'</div></th>';
  222. //$t_head .= ' <th><div style="width:60px">'.get_lang('FirstLogin').'</div></th>';
  223. $t_head .= ' <th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  224. $t_head .= ' </tr></table>';
  225. $addparams = array('view' => 'admin', 'display' => 'useroverview');
  226. $table = new SortableTable('tracking_user_overview', array('MySpace','get_number_of_users_tracking_overview'), array('MySpace','get_user_data_tracking_overview'), 0);
  227. $table->additional_parameters = $addparams;
  228. $table->set_header(0, get_lang('OfficialCode'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  229. if (api_is_western_name_order()) {
  230. $table->set_header(1, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  231. $table->set_header(2, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  232. } else {
  233. $table->set_header(1, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  234. $table->set_header(2, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  235. }
  236. $table->set_header(3, get_lang('LoginName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  237. $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;'));
  238. $table->set_column_filter(4, array('MySpace','course_info_tracking_filter'));
  239. $table->display();
  240. }
  241. /**
  242. * Display a sortable table that contains an overview off all the progress of the user in a session
  243. * @author César Perales <cesar.perales@beeznest.com>, Beeznest Team
  244. */
  245. public static function display_tracking_lp_progress_overview($sessionId = '', $courseId = '', $date_from, $date_to) {
  246. $course = api_get_course_info_by_id($courseId);
  247. /**
  248. * Column name
  249. * The order is important you need to check the $column variable in the model.ajax.php file
  250. */
  251. $columns = array(
  252. get_lang('Username'),
  253. get_lang('FirstName'),
  254. get_lang('LastName'),
  255. );
  256. //add lessons of course
  257. require_once api_get_path(SYS_CODE_PATH).'newscorm/learnpathList.class.php';
  258. $lessons = LearnpathList::get_course_lessons($course['code'], $sessionId);
  259. //create columns array
  260. foreach ($lessons as $lesson_id => $lesson)
  261. {
  262. $columns[] = $lesson['name'];
  263. }
  264. $columns[] = get_lang('Total');
  265. /**
  266. * Column config
  267. */
  268. $column_model = array(
  269. array('name'=>'username', 'index'=>'username', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  270. array('name'=>'firstname', 'index'=>'firstname', 'align'=>'left', 'search' => 'true'),
  271. array('name'=>'lastname', 'index'=>'lastname', 'align'=>'left', 'search' => 'true'),
  272. );
  273. //get dinamic column names
  274. foreach ($lessons as $lesson_id => $lesson)
  275. {
  276. $column_model[] = array('name'=> $lesson['id'], 'index'=>$lesson['id'], 'align'=>'left', 'search' => 'true');
  277. }
  278. $column_model[] = array('name'=>'total', 'index'=>'total', 'align'=>'left', 'search' => 'true');
  279. $action_links = '';
  280. // jqgrid will use this URL to do the selects
  281. $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;
  282. //Table Id
  283. $tableId = 'lpProgress';
  284. //Autowidth
  285. $extra_params['autowidth'] = 'true';
  286. //height auto
  287. $extra_params['height'] = 'auto';
  288. $table = Display::grid_js($tableId, $url, $columns, $column_model, $extra_params, array(), $action_links, true);
  289. $return = '<script>$(function() {'. $table .
  290. 'jQuery("#'.$tableId.'").jqGrid("navGrid","#'.$tableId.'_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
  291. jQuery("#'.$tableId.'").jqGrid("navButtonAdd","#'.$tableId.'_pager",{
  292. caption:"",
  293. title:"' . get_lang('ExportExcel') . '",
  294. onClickButton : function () {
  295. jQuery("#'.$tableId.'").jqGrid("excelExport",{"url":"'.$url.'&export_format=xls"});
  296. }
  297. });
  298. });</script>';
  299. $return .= Display::grid_html($tableId);
  300. return $return;
  301. }
  302. /**
  303. * Display a sortable table that contains an overview off all the progress of the user in a session
  304. * @param int $sessionId The session ID
  305. * @param int $courseId The course ID
  306. * @param int $exerciseId The quiz ID
  307. * @param int $answer Answer status (0 = incorrect, 1 = correct, 2 = both)
  308. * @return string HTML array of results formatted for gridJS
  309. * @author César Perales <cesar.perales@beeznest.com>, Beeznest Team
  310. */
  311. static function display_tracking_exercise_progress_overview(
  312. $sessionId = 0,
  313. $courseId = 0,
  314. $exerciseId = 0,
  315. $date_from = null,
  316. $date_to = null
  317. ) {
  318. $date_from = Security::remove_XSS($date_from);
  319. $date_to = Security::remove_XSS($date_to);
  320. /**
  321. * Column names
  322. * The column order is important. Check $column variable in the main/inc/ajax/model.ajax.php file
  323. */
  324. $columns = array(
  325. get_lang('Session'),
  326. get_lang('ExerciseId'),
  327. get_lang('ExerciseName'),
  328. get_lang('Username'),
  329. get_lang('LastName'),
  330. get_lang('FirstName'),
  331. get_lang('Time'),
  332. get_lang('QuestionId'),
  333. get_lang('QuestionTitle'),
  334. get_lang('WorkDescription'),
  335. get_lang('Answer'),
  336. get_lang('Correct'),
  337. );
  338. /**
  339. * Column config
  340. */
  341. $column_model = array(
  342. array('name'=>'session', 'index'=>'session', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  343. array('name'=>'exercise_id', 'index'=>'exercise_id', 'align'=>'left', 'search' => 'true'),
  344. array('name'=>'quiz_title', 'index'=>'quiz_title', 'align'=>'left', 'search' => 'true'),
  345. array('name'=>'username', 'index'=>'username', 'align'=>'left', 'search' => 'true'),
  346. array('name'=>'lastname', 'index'=>'lastname', 'align'=>'left', 'search' => 'true'),
  347. array('name'=>'firstname', 'index'=>'firstname', 'align'=>'left', 'search' => 'true'),
  348. array('name'=>'time', 'index'=>'time', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  349. array('name'=>'question_id', 'index'=>'question_id', 'align'=>'left', 'search' => 'true'),
  350. array('name'=>'question', 'index'=>'question', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  351. array('name'=>'description', 'index'=>'description', 'align'=>'left', 'width' => '550', 'search' => 'true', 'wrap_cell' => "true"),
  352. array('name'=>'answer', 'index'=>'answer', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  353. array('name'=>'correct', 'index'=>'correct', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  354. );
  355. //get dynamic column names
  356. // jqgrid will use this URL to do the selects
  357. $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;
  358. // Autowidth
  359. $extra_params['autowidth'] = 'true';
  360. // height auto
  361. $extra_params['height'] = 'auto';
  362. $tableId = 'exerciseProgressOverview';
  363. $table = Display::grid_js($tableId, $url, $columns, $column_model, $extra_params, array(), '', true);
  364. $return = '<script>$(function() {'. $table .
  365. 'jQuery("#'.$tableId.'").jqGrid("navGrid","#'.$tableId.'_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
  366. jQuery("#'.$tableId.'").jqGrid("navButtonAdd","#'.$tableId.'_pager",{
  367. caption:"",
  368. title:"' . get_lang('ExportExcel') . '",
  369. onClickButton : function () {
  370. jQuery("#'.$tableId.'").jqGrid("excelExport",{"url":"'.$url.'&export_format=xls"});
  371. }
  372. });
  373. });</script>';
  374. $return .= Display::grid_html($tableId);
  375. return $return;
  376. }
  377. /**
  378. * Display a sortable table that contains an overview off all the progress of the user in a session
  379. * @param int $sessionId The session ID
  380. * @param int $courseId The course ID
  381. * @param null $date_from
  382. * @param null $date_to
  383. * @internal param int $exerciseId The quiz ID
  384. * @internal param int $answer Answer status (0 = incorrect, 1 = correct, 2 = both)
  385. * @return string HTML array of results formatted for gridJS
  386. * @author Francis Gonzales <francis.gonzales@beeznest.com>, Beeznest Team
  387. */
  388. static function display_tracking_grade_overview($sessionId = 0, $courseId = 0, $date_from = null, $date_to = null)
  389. {
  390. /**
  391. * Column names
  392. * The column order is important. Check $column variable in the main/inc/ajax/model.ajax.php file
  393. */
  394. $objExercise = new Exercise();
  395. $exercises = $objExercise->getExercisesByCouseSession($courseId, $sessionId);
  396. $cntExer = 4;
  397. if (!empty($exercises)) {
  398. $cntExer += count($exercises);
  399. }
  400. $column = array();
  401. $column_model = array();
  402. $i = 1;
  403. //Get dynamic column names
  404. foreach (range(1, $cntExer) as $cnt) {
  405. switch ($cnt) {
  406. case 1:
  407. $column[] = get_lang('Section');
  408. $column_model[] = array(
  409. 'name' => 'session',
  410. 'index' => 'session',
  411. 'align' => 'center',
  412. 'search' => 'true',
  413. );
  414. break;
  415. case 2:
  416. $column[] = get_lang('Username');
  417. $column_model[] = array(
  418. 'name' => 'username',
  419. 'index' => 'username',
  420. 'align' => 'center',
  421. 'search' => 'true',
  422. );
  423. break;
  424. case 3:
  425. $column[] = get_lang('FirstName');
  426. $column_model[] = array(
  427. 'name' => 'name',
  428. 'index' => 'name',
  429. 'align' => 'left',
  430. 'search' => 'true',
  431. );
  432. break;
  433. case $cntExer:
  434. $column[] = get_lang('FinalScore');
  435. $column_model[] = array(
  436. 'name' => 'finalscore',
  437. 'index' => 'finalscore',
  438. 'align' => 'center',
  439. 'search' => 'true',
  440. 'wrap_cell' => "true"
  441. );
  442. break;
  443. default:
  444. $title = "";
  445. if (!empty($exercises[$cnt - 4]['title'])) {
  446. $title = ucwords(strtolower(trim($exercises[$cnt - 4]['title'])));
  447. }
  448. $column[] = $title;
  449. $column_model[] = array(
  450. 'name' => 'exer' . $i,
  451. 'index' => 'exer' . $i,
  452. 'align' => 'center',
  453. 'search' => 'true',
  454. 'wrap_cell' => "true"
  455. );
  456. $i++;
  457. break;
  458. }
  459. }
  460. //end get dynamic column names
  461. // jqgrid will use this URL to do the selects
  462. $url = api_get_path(WEB_AJAX_PATH) . 'model.ajax.php?a=get_exercise_grade&session_id=' . $sessionId . '&course_id=' . $courseId;
  463. //Autowidth
  464. $extra_params['autowidth'] = 'true';
  465. //height auto
  466. $extra_params['height'] = 'auto';
  467. $tableId = 'exerciseGradeOverview';
  468. $table = Display::grid_js($tableId, $url, $column, $column_model, $extra_params, array(), '', true);
  469. $return = '<script>$(function() {' . $table .
  470. 'jQuery("#' . $tableId . '").jqGrid("navGrid","#' . $tableId . '_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
  471. jQuery("#' . $tableId . '").jqGrid("navButtonAdd","#' . $tableId . '_pager",{
  472. caption:"",
  473. title:"' . get_lang('ExportExcel') . '",
  474. onClickButton : function () {
  475. jQuery("#' . $tableId . '").jqGrid("excelExport",{"url":"' . $url . '&export_format=xls"});
  476. }
  477. });
  478. });</script>';
  479. $return .= Display::grid_html($tableId);
  480. return $return;
  481. }
  482. /**
  483. * Display a sortable table that contains an overview off all the progress of the user in a session
  484. * @author César Perales <cesar.perales@beeznest.com>, Beeznest Team
  485. */
  486. function display_survey_overview($sessionId = 0, $courseId = 0, $surveyId = 0, $date_from, $date_to)
  487. {
  488. /**
  489. * Column name
  490. * The order is important you need to check the $column variable in the model.ajax.php file
  491. */
  492. $columns = array(
  493. get_lang('Username'),
  494. get_lang('FirstName'),
  495. get_lang('LastName'),
  496. );
  497. //add lessons of course
  498. $questions = survey_manager::get_questions($surveyId, $courseId);
  499. foreach ($questions as $question) {
  500. $columns[] = $question['question'];
  501. }
  502. /**
  503. * Column config
  504. */
  505. $column_model = array(
  506. array('name'=>'username', 'index'=>'username', 'align'=>'left', 'search' => 'true', 'wrap_cell' => "true"),
  507. array('name'=>'firstname', 'index'=>'firstname', 'align'=>'left', 'search' => 'true'),
  508. array('name'=>'lastname', 'index'=>'lastname', 'align'=>'left', 'search' => 'true'),
  509. );
  510. //get dinamic column names
  511. foreach ($questions as $question_id => $question) {
  512. $column_model[] = array(
  513. 'name'=> $question_id,
  514. 'index'=>$question_id,
  515. 'width'=>'70',
  516. 'align'=>'left',
  517. 'search' => 'true'
  518. );
  519. }
  520. $action_links = '';
  521. // jqgrid will use this URL to do the selects
  522. $url = api_get_path(WEB_AJAX_PATH).'model.ajax.php?a=get_survey_overview&session_id=' . $sessionId . '&course_id=' . $courseId . '&survey_id=' . $surveyId . '&date_to=' . $date_to . '&date_from=' . $date_from;
  523. //Table Id
  524. $tableId = 'lpProgress';
  525. //Autowidth
  526. $extra_params['autowidth'] = 'true';
  527. //height auto
  528. $extra_params['height'] = 'auto';
  529. $table = Display::grid_js($tableId, $url, $columns, $column_model, $extra_params, array(), $action_links, true);
  530. $return = '<script>$(function() {'. $table .
  531. 'jQuery("#'.$tableId.'").jqGrid("navGrid","#'.$tableId.'_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
  532. jQuery("#'.$tableId.'").jqGrid("navButtonAdd","#'.$tableId.'_pager",{
  533. caption:"",
  534. title:"' . get_lang('ExportExcel') . '",
  535. onClickButton : function () {
  536. jQuery("#'.$tableId.'").jqGrid("excelExport",{"url":"'.$url.'&export_format=xls"});
  537. }
  538. });
  539. });</script>';
  540. $return .= Display::grid_html($tableId);
  541. return $return;
  542. }
  543. /**
  544. * Display a sortable table that contains an overview off all the progress of the user in a session
  545. * @author César Perales <cesar.perales@beeznest.com>, Beeznest Team
  546. */
  547. static function display_tracking_progress_overview($sessionId = 0, $courseId = 0, $date_from, $date_to)
  548. {
  549. //The order is important you need to check the the $column variable in the model.ajax.php file
  550. $columns = array(
  551. get_lang('LastName'),
  552. get_lang('FirstName'),
  553. get_lang('Username'),
  554. #get_lang('Profile'),
  555. get_lang('Total'),
  556. get_lang('Courses'),
  557. get_lang('LearningPaths'),
  558. get_lang('Exercises'),
  559. get_lang('Forums'),
  560. get_lang('Assignments'),
  561. get_lang('ToolWiki'),
  562. get_lang('ToolSurvey'),
  563. //Learning paths
  564. get_lang('LearnpathsTotal'),
  565. get_lang('LearnpathsDone'),
  566. get_lang('LearnpathsLeft'),
  567. get_lang('LearnpathsProgress'),
  568. //Exercises
  569. get_lang('ExercisesTotal'),
  570. get_lang('ExercisesDone'),
  571. get_lang('ExercisesLeft'),
  572. get_lang('ExercisesProgress'),
  573. //Forums
  574. get_lang('ForumsTotal'),
  575. get_lang('ForumsDone'),
  576. get_lang('ForumsLeft'),
  577. get_lang('ForumsProgress'),
  578. //Assignments
  579. get_lang('AssignmentsTotal'),
  580. get_lang('AssignmentsDone'),
  581. get_lang('AssignmentsLeft'),
  582. get_lang('AssignmentsProgress'),
  583. //Wiki
  584. get_lang('WikiTotal'),
  585. get_lang('WikiRevisions'),
  586. get_lang('WikiRead'),
  587. get_lang('WikiUnread'),
  588. get_lang('WikiProgress'),
  589. //Surveys
  590. get_lang('SurveysTotal'),
  591. get_lang('SurveysDone'),
  592. get_lang('SurveysLeft'),
  593. get_lang('SurveysProgress'),
  594. );
  595. //Column config
  596. $column_model = array(
  597. array('name'=>'lastname', 'index'=>'lastname', 'align'=>'left'),
  598. array('name'=>'firstname', 'index'=>'firstname', 'align'=>'left'),
  599. array('name'=>'username', 'index'=>'username', 'align'=>'left'),
  600. #array('name'=>'profile', 'index'=>'username', 'align'=>'left'),
  601. array('name'=>'total', 'index'=>'total', 'align'=>'left'),
  602. array('name'=>'courses', 'index'=>'courses', 'align'=>'left', 'sortable' => 'false'),
  603. array('name'=>'lessons', 'index'=>'lessons', 'align'=>'left', 'sortable' => 'false'),
  604. array('name'=>'exercises', 'index'=>'exercises', 'align'=>'left', 'sortable' => 'false'),
  605. array('name'=>'forums', 'index'=>'forums', 'align'=>'left', 'sortable' => 'false'),
  606. array('name'=>'homeworks', 'index'=>'homeworks', 'align'=>'left', 'sortable' => 'false'),
  607. array('name'=>'wikis', 'index'=>'wikis', 'align'=>'left', 'sortable' => 'false'),
  608. array('name'=>'surveys', 'index'=>'surveys', 'align'=>'left', 'sortable' => 'false'),
  609. //Lessons
  610. array('name'=>'lessons_total', 'index'=>'lessons_total', 'align'=>'center', 'sortable' => 'false'),
  611. array('name'=>'lessons_done', 'index'=>'lessons_done', 'align'=>'center', 'sortable' => 'false'),
  612. array('name'=>'lessons_left', 'index'=>'lessons_left', 'align'=>'center', 'sortable' => 'false'),
  613. array('name'=>'lessons_progress', 'index'=>'lessons_progress', 'align'=>'center', 'sortable' => 'false'),
  614. //Exercises
  615. array('name'=>'exercises_total', 'index'=>'exercises_total', 'align'=>'center', 'sortable' => 'false'),
  616. array('name'=>'exercises_done', 'index'=>'exercises_done', 'align'=>'center', 'sortable' => 'false'),
  617. array('name'=>'exercises_left', 'index'=>'exercises_left', 'align'=>'center', 'sortable' => 'false'),
  618. array('name'=>'exercises_progress', 'index'=>'exercises_progress', 'align'=>'center', 'sortable' => 'false'),
  619. //Assignments
  620. array('name'=>'forums_total', 'index'=>'forums_total', 'align'=>'center', 'sortable' => 'false'),
  621. array('name'=>'forums_done', 'index'=>'forums_done', 'align'=>'center', 'sortable' => 'false'),
  622. array('name'=>'forums_left', 'index'=>'forums_left', 'align'=>'center', 'sortable' => 'false'),
  623. array('name'=>'forums_progress', 'index'=>'forums_progress', 'align'=>'center', 'sortable' => 'false'),
  624. //Assignments
  625. array('name'=>'assigments_total', 'index'=>'assigments_total', 'align'=>'center', 'sortable' => 'false'),
  626. array('name'=>'assigments_done', 'index'=>'assigments_done', 'align'=>'center', 'sortable' => 'false'),
  627. array('name'=>'assigments_left', 'index'=>'assigments_left', 'align'=>'center', 'sortable' => 'false'),
  628. array('name'=>'assigments_progress', 'index'=>'assigments_progress', 'align'=>'center', 'sortable' => 'false'),
  629. //Assignments
  630. array('name'=>'wiki_total', 'index'=>'wiki_total', 'align'=>'center', 'sortable' => 'false'),
  631. array('name'=>'wiki_revisions', 'index'=>'wiki_revisions', 'align'=>'center', 'sortable' => 'false'),
  632. array('name'=>'wiki_read', 'index'=>'wiki_read', 'align'=>'center', 'sortable' => 'false'),
  633. array('name'=>'wiki_unread', 'index'=>'wiki_unread', 'align'=>'center', 'sortable' => 'false'),
  634. array('name'=>'wiki_progress', 'index'=>'wiki_progress', 'align'=>'center', 'sortable' => 'false'),
  635. //Surveys
  636. array('name'=>'surveys_total', 'index'=>'surveys_total', 'align'=>'center', 'sortable' => 'false'),
  637. array('name'=>'surveys_done', 'index'=>'surveys_done', 'align'=>'center', 'sortable' => 'false'),
  638. array('name'=>'surveys_left', 'index'=>'surveys_left', 'align'=>'center', 'sortable' => 'false'),
  639. array('name'=>'surveys_progress', 'index'=>'surveys_progress', 'align'=>'center', 'sortable' => 'false'),
  640. );
  641. $action_links = '';
  642. // jqgrid will use this URL to do the selects
  643. $url = api_get_path(WEB_AJAX_PATH).'model.ajax.php?a=get_session_progress&session_id=' . $sessionId . '&course_id=' . $courseId . '&date_to=' . $date_to . '&date_from=' . $date_from;
  644. //Table Id
  645. $tableId = 'progressOverview';
  646. //Autowidth
  647. $extra_params['autowidth'] = 'true';
  648. $extra_params['shrinkToFit'] = 'true';
  649. $extra_params['headertitles'] = 'true';
  650. $extra_params['groupHeaders'] = array(
  651. 'courses_detail' => array(
  652. "startColumnName" => 'courses',
  653. "numberOfColumns" => 7,
  654. "titleText" => get_lang('Global'),
  655. ),
  656. 'lessons' => array(
  657. "startColumnName" => 'lessons_total',
  658. "numberOfColumns" => 4,
  659. "titleText" => get_lang('LearningPaths'),
  660. ),
  661. 'exercises' => array(
  662. "startColumnName" => 'exercises_total',
  663. "numberOfColumns" => 4,
  664. "titleText" => get_lang('Exercises'),
  665. ),
  666. 'forums' => array(
  667. "startColumnName" => 'forums_total',
  668. "numberOfColumns" => 4,
  669. "titleText" => get_lang('Forums'),
  670. ),
  671. 'assignments' => array(
  672. "startColumnName" => 'assigments_total',
  673. "numberOfColumns" => 4,
  674. "titleText" => get_lang('Assignments'),
  675. ),
  676. 'wikis' => array(
  677. "startColumnName" => 'wiki_total',
  678. "numberOfColumns" => 5,
  679. "titleText" => get_lang('Wiki'),
  680. ),
  681. 'surveys' => array(
  682. "startColumnName" => 'surveys_total',
  683. "numberOfColumns" => 4,
  684. "titleText" => get_lang('Survey'),
  685. ),
  686. );
  687. //height auto
  688. $extra_params['height'] = 'auto';
  689. $table = Display::grid_js($tableId, $url, $columns, $column_model, $extra_params, array(), $action_links, true);
  690. $return = '<script>$(function() {'. $table .
  691. 'jQuery("#'.$tableId.'").jqGrid("navGrid","#'.$tableId.'_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
  692. jQuery("#'.$tableId.'").jqGrid("navButtonAdd","#'.$tableId.'_pager",{
  693. caption:"",
  694. title:"' . get_lang('ExportExcel') . '",
  695. onClickButton : function () {
  696. jQuery("#'.$tableId.'").jqGrid("excelExport",{"url":"'.$url.'&export_format=xls"});
  697. }
  698. });
  699. });</script>';
  700. $return .= Display::grid_html($tableId);
  701. return $return;
  702. }
  703. /**
  704. * Display a sortable table that contains an overview off all the access to a session
  705. * @author César Perales <cesar.perales@beeznest.com>, Beeznest Team
  706. * @version Chamilo 1.9.6
  707. */
  708. static function display_tracking_access_overview($sessionId = 0, $courseId = 0, $studentId = '', $profile = '', $date_from, $date_to) {
  709. //The order is important you need to check the the $column variable in the model.ajax.php file
  710. $columns = array(
  711. get_lang('LoginDate'),
  712. get_lang('UserName'),
  713. get_lang('LastName'),
  714. get_lang('FirstName'),
  715. get_lang('Clicks'),
  716. get_lang('IP'),
  717. get_lang('TimeLoggedIn'),
  718. get_lang('Section'),
  719. );
  720. $column_model = array(
  721. array('name'=>'logindate', 'index'=>'loginDate', 'align'=>'left', 'search' => 'true'),
  722. array('name'=>'username', 'index'=>'username', 'align'=>'left', 'search' => 'true'),
  723. array('name'=>'firstname', 'index'=>'firstname', 'align'=>'left', 'search' => 'true'),
  724. array('name'=>'lastname', 'index'=>'lastname', 'align'=>'left', 'search' => 'true'),
  725. array('name'=>'clicks', 'index'=>'clicks', 'align'=>'left', 'search' => 'true'),
  726. array('name'=>'ip', 'index'=>'ip', 'align'=>'left', 'search' => 'true'),
  727. array('name'=>'timeloggedin', 'index'=>'timeLoggedIn', 'align'=>'left', 'search' => 'true'),
  728. array('name'=>'session', 'index'=>'session', 'align'=>'left')
  729. );
  730. $action_links = '';
  731. // jqgrid will use this URL to do the selects
  732. $url = api_get_path(WEB_AJAX_PATH).'model.ajax.php?a=get_session_access_overview&session_id=' . $sessionId . '&course_id=' . $courseId . '&student_id=' . $studentId . '&profile=' . $profile . '&date_to=' . $date_to . '&date_from=' . $date_from;
  733. //Table Id
  734. $tableId = 'accessOverview';
  735. //Autowidth
  736. $extra_params['autowidth'] = 'true';
  737. //height auto
  738. $extra_params['height'] = 'auto';
  739. $table = Display::grid_js($tableId, $url, $columns, $column_model, $extra_params, array(), $action_links, true);
  740. $return = '<script>$(function() {'. $table .
  741. 'jQuery("#'.$tableId.'").jqGrid("navGrid","#'.$tableId.'_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
  742. jQuery("#'.$tableId.'").jqGrid("navButtonAdd","#'.$tableId.'_pager",{
  743. caption:"",
  744. title:"' . get_lang('ExportExcel') . '",
  745. onClickButton : function () {
  746. jQuery("#'.$tableId.'").jqGrid("excelExport",{"url":"'.$url.'&export_format=xls"});
  747. }
  748. });
  749. });</script>';
  750. $return .= Display::grid_html($tableId);
  751. return $return;
  752. }
  753. /**
  754. * get the numer of users on track_e_course_access
  755. *
  756. * @return integer
  757. *
  758. * @author César Perales <cesar.perales@beeznest.com>, Beeznest Team
  759. * @version Chamilo 1.9.6
  760. */
  761. /**
  762. * Displays a form with all the additionally defined user fields of the profile
  763. * and give you the opportunity to include these in the CSV export
  764. *
  765. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  766. * @version Dokeos 1.8.6
  767. * @since November 2008
  768. */
  769. function display_user_overview_export_options() {
  770. // include the user manager and formvalidator library
  771. if ($_GET['export'] == 'options') {
  772. // get all the defined extra fields
  773. $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC', false, 1);
  774. // creating the form with all the defined extra fields
  775. $form = new FormValidator('exportextrafields', 'post', api_get_self()."?view=".Security::remove_XSS($_GET['view']).'&display='.Security::remove_XSS($_GET['display']).'&export='.Security::remove_XSS($_GET['export']));
  776. if (is_array($extrafields) && count($extrafields) > 0) {
  777. foreach ($extrafields as $key => $extra) {
  778. $form->addElement('checkbox', 'extra_export_field'.$extra[0], '', $extra[3]);
  779. }
  780. $form->addElement('style_submit_button','submit', get_lang('Ok'),'class="save"' );
  781. // setting the default values for the form that contains all the extra fields
  782. if (is_array($_SESSION['additional_export_fields'])) {
  783. foreach ($_SESSION['additional_export_fields'] as $key => $value) {
  784. $defaults['extra_export_field'.$value] = 1;
  785. }
  786. }
  787. $form->setDefaults($defaults);
  788. } else {
  789. $form->addElement('html', Display::display_warning_message(get_lang('ThereAreNotExtrafieldsAvailable')));
  790. }
  791. if ($form->validate()) {
  792. // exporting the form values
  793. $values = $form->exportValues();
  794. // re-initialising the session that contains the additional fields that need to be exported
  795. $_SESSION['additional_export_fields'] = array();
  796. // adding the fields that are checked to the session
  797. $message = '';
  798. foreach ($values as $field_ids => $value) {
  799. if ($value == 1 && strstr($field_ids,'extra_export_field')) {
  800. $_SESSION['additional_export_fields'][] = str_replace('extra_export_field', '', $field_ids);
  801. }
  802. }
  803. // adding the fields that will be also exported to a message string
  804. if (is_array($_SESSION['additional_export_fields'])) {
  805. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  806. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  807. }
  808. }
  809. // Displaying a feedback message
  810. if (!empty($_SESSION['additional_export_fields'])) {
  811. Display::display_confirmation_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', false);
  812. } else {
  813. Display::display_confirmation_message(get_lang('NoAdditionalFieldsWillBeExported'), false);
  814. }
  815. $message = '';
  816. } else {
  817. $form->display();
  818. }
  819. } else {
  820. if (!empty($_SESSION['additional_export_fields'])) {
  821. // get all the defined extra fields
  822. $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  823. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  824. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  825. }
  826. Display::display_normal_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', false);
  827. $message = '';
  828. }
  829. }
  830. }
  831. /**
  832. * Display a sortable table that contains an overview of all the reporting progress of all courses
  833. */
  834. function display_tracking_course_overview() {
  835. //MySpace::display_user_overview_export_options();
  836. $t_head .= ' <table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  837. //$t_head .= ' <caption>'.get_lang('CourseInformation').'</caption>';
  838. $t_head .= '<tr>';
  839. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  840. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  841. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  842. //$t_head .= ' <th><div style="width:40px">'.get_lang('AvgExercisesScore').'</div></th>';
  843. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  844. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  845. $t_head .= ' <th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  846. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePossible').'</div></th>';
  847. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  848. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePercentage').'</div></th>';
  849. //$t_head .= ' <th><div style="width:60px">'.get_lang('FirstLogin').'</div></th>';
  850. $t_head .= ' <th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  851. $t_head .= ' </tr></table>';
  852. $addparams = array('view' => 'admin', 'display' => 'courseoverview');
  853. $table = new SortableTable('tracking_session_overview', array('MySpace','get_total_number_courses'), array('MySpace','get_course_data_tracking_overview'), 1);
  854. $table->additional_parameters = $addparams;
  855. $table->set_header(0, '', false, null, array('style' => 'display: none'));
  856. $table->set_header(1, get_lang('Course'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  857. $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;'));
  858. $table->set_column_filter(2, array('MySpace','course_tracking_filter'));
  859. $table->display();
  860. }
  861. /**
  862. * Get the total number of courses
  863. *
  864. * @return integer Total number of courses
  865. */
  866. public function get_total_number_courses() {
  867. // database table definition
  868. $main_course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  869. return Database::count_rows($main_course_table);
  870. }
  871. /**
  872. * Get data for the courses
  873. *
  874. * @param int Inferior limit
  875. * @param int Number of items to select
  876. * @param string Column to order on
  877. * @param string Order direction
  878. * @return array Results
  879. */
  880. public function get_course_data_tracking_overview($from, $number_of_items, $column, $direction) {
  881. $main_course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  882. $from = intval($from);
  883. $number_of_items = intval($number_of_items);
  884. $sql = "SELECT code AS col0, title AS col1 FROM $main_course_table";
  885. $sql .= " ORDER BY col$column $direction ";
  886. $sql .= " LIMIT $from,$number_of_items";
  887. $result = Database::query($sql);
  888. $return = array ();
  889. while ($course = Database::fetch_row($result)) {
  890. $return[] = $course;
  891. }
  892. return $return;
  893. }
  894. /**
  895. * Fills in course reporting data
  896. *
  897. * @param integer course code
  898. * @param array $url_params additonal url parameters
  899. * @param array $row the row information (the other columns)
  900. * @return string html code
  901. */
  902. function course_tracking_filter($course_code, $url_params, $row) {
  903. $course_code = $row[0];
  904. // the table header
  905. $return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  906. // database table definition
  907. $tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  908. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  909. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  910. // getting all the courses of the user
  911. $sql = "SELECT * FROM $tbl_user AS u INNER JOIN $tbl_course_rel_user AS cu ON cu.user_id = u.user_id WHERE cu.course_code = '".$course_code."' AND ISNULL(cu.role);";
  912. $result = Database::query($sql);
  913. $time_spent = 0;
  914. $progress = 0;
  915. $nb_progress_lp = 0;
  916. $score = 0;
  917. $nb_score_lp = 0;
  918. $nb_messages = 0;
  919. $nb_assignments = 0;
  920. $last_login_date = false;
  921. $total_score_obtained = 0;
  922. $total_score_possible = 0;
  923. $total_questions_answered = 0;
  924. while ($row = Database::fetch_object($result)) {
  925. // get time spent in the course and session
  926. $time_spent += Tracking::get_time_spent_on_the_course($row->user_id, $course_code);
  927. $progress_tmp = Tracking::get_avg_student_progress($row->user_id, $course_code, array(), null, true);
  928. $progress += $progress_tmp[0];
  929. $nb_progress_lp += $progress_tmp[1];
  930. $score_tmp = Tracking :: get_avg_student_score($row->user_id, $course_code, array(), null, true);
  931. if(is_array($score_tmp)) {
  932. $score += $score_tmp[0];
  933. $nb_score_lp += $score_tmp[1];
  934. }
  935. $nb_messages += Tracking::count_student_messages($row->user_id, $course_code);
  936. $nb_assignments += Tracking::count_student_assignments($row->user_id, $course_code);
  937. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row->user_id, $course_code, null, false);
  938. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned
  939. $last_login_date = $last_login_date_tmp;
  940. } else if($last_login_date_tmp != false && $last_login_date != false) { // TODO: Repeated previous condition. To be cleaned.
  941. // Find the max and assign it to first_login_date
  942. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  943. $last_login_date = $last_login_date_tmp;
  944. }
  945. }
  946. $exercise_results_tmp = MySpace::exercises_results($row->user_id, $course_code);
  947. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  948. $total_score_possible += $exercise_results_tmp['score_possible'];
  949. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  950. }
  951. if($nb_progress_lp > 0) {
  952. $avg_progress = round($progress / $nb_progress_lp, 2);
  953. } else {
  954. $avg_progress = 0;
  955. }
  956. if($nb_score_lp > 0) {
  957. $avg_score = round($score / $nb_score_lp, 2);
  958. } else {
  959. $avg_score = '-';
  960. }
  961. if($last_login_date) {
  962. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  963. } else {
  964. $last_login_date = '-';
  965. }
  966. if($total_score_possible > 0) {
  967. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  968. } else {
  969. $total_score_percentage = 0;
  970. }
  971. if($total_score_percentage > 0) {
  972. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  973. } else {
  974. $total_score = '-';
  975. }
  976. $return .= '<tr>';
  977. // time spent in the course
  978. $return .= ' <td style="width:164px;">'.api_time_to_hms($time_spent).'</td>';
  979. // student progress in course
  980. $return .= ' <td>'.$avg_progress.'</td>';
  981. // student score
  982. $return .= ' <td>'.$avg_score.'</td>';
  983. // student messages
  984. $return .= ' <td>'.$nb_messages.'</td>';
  985. // student assignments
  986. $return .= ' <td>'.$nb_assignments.'</td>';
  987. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  988. $return .= '<td width="105px;">'.$total_score.'</td>';
  989. $return .= '<td>'.$total_questions_answered.'</td>';
  990. // last connection
  991. $return .= ' <td>'.$last_login_date.'</td>';
  992. $return .= '</tr>';
  993. $return .= '</table>';
  994. return $return;
  995. }
  996. /**
  997. * This function exports the table that we see in display_tracking_course_overview()
  998. *
  999. */
  1000. function export_tracking_course_overview() {
  1001. // database table definition
  1002. $tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1003. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  1004. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  1005. // the values of the sortable table
  1006. if ($_GET['tracking_course_overview_page_nr']) {
  1007. $from = $_GET['tracking_course_overview_page_nr'];
  1008. } else {
  1009. $from = 0;
  1010. }
  1011. if ($_GET['tracking_course_overview_column']) {
  1012. $orderby = $_GET['tracking_course_overview_column'];
  1013. } else {
  1014. $orderby = 0;
  1015. }
  1016. if ($_GET['tracking_course_overview_direction']) {
  1017. $direction = $_GET['tracking_course_overview_direction'];
  1018. } else {
  1019. $direction = 'ASC';
  1020. }
  1021. $course_data = MySpace::get_course_data_tracking_overview($from, 1000, $orderby, $direction);
  1022. $csv_content = array();
  1023. // the first line of the csv file with the column headers
  1024. $csv_row = array();
  1025. $csv_row[] = get_lang('Course', '');
  1026. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  1027. $csv_row[] = get_lang('AvgStudentsProgress', '');
  1028. $csv_row[] = get_lang('AvgCourseScore', '');
  1029. $csv_row[] = get_lang('TotalNumberOfMessages', '');
  1030. $csv_row[] = get_lang('TotalNumberOfAssignments', '');
  1031. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  1032. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  1033. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  1034. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  1035. $csv_row[] = get_lang('LatestLogin', '');
  1036. $csv_content[] = $csv_row;
  1037. // the other lines (the data)
  1038. foreach ($course_data as $key => $course) {
  1039. $course_code = $course[0];
  1040. $course_title = $course[1];
  1041. $csv_row = array();
  1042. $csv_row[] = $course_title;
  1043. // getting all the courses of the session
  1044. $sql = "SELECT * FROM $tbl_user AS u INNER JOIN $tbl_course_rel_user AS cu ON cu.user_id = u.user_id WHERE cu.course_code = '".$course_code."' AND ISNULL(cu.role);";
  1045. $result = Database::query($sql);
  1046. $time_spent = 0;
  1047. $progress = 0;
  1048. $nb_progress_lp = 0;
  1049. $score = 0;
  1050. $nb_score_lp = 0;
  1051. $nb_messages = 0;
  1052. $nb_assignments = 0;
  1053. $last_login_date = false;
  1054. $total_score_obtained = 0;
  1055. $total_score_possible = 0;
  1056. $total_questions_answered = 0;
  1057. while ($row = Database::fetch_object($result)) {
  1058. // get time spent in the course and session
  1059. $time_spent += Tracking::get_time_spent_on_the_course($row->user_id, $course_code);
  1060. $progress_tmp = Tracking::get_avg_student_progress($row->user_id, $course_code, array(), null, true);
  1061. $progress += $progress_tmp[0];
  1062. $nb_progress_lp += $progress_tmp[1];
  1063. $score_tmp = Tracking :: get_avg_student_score($row->user_id, $course_code, array(), null, true);
  1064. if(is_array($score_tmp)) {
  1065. $score += $score_tmp[0];
  1066. $nb_score_lp += $score_tmp[1];
  1067. }
  1068. $nb_messages += Tracking::count_student_messages($row->user_id, $course_code);
  1069. $nb_assignments += Tracking::count_student_assignments($row->user_id, $course_code);
  1070. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row->user_id, $course_code, null, false);
  1071. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned.
  1072. $last_login_date = $last_login_date_tmp;
  1073. } else if($last_login_date_tmp != false && $last_login_date == false) { // TODO: Repeated previous condition. To be cleaned.
  1074. // Find the max and assign it to first_login_date
  1075. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  1076. $last_login_date = $last_login_date_tmp;
  1077. }
  1078. }
  1079. $exercise_results_tmp = MySpace::exercises_results($row->user_id, $course_code);
  1080. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  1081. $total_score_possible += $exercise_results_tmp['score_possible'];
  1082. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  1083. }
  1084. if($nb_progress_lp > 0) {
  1085. $avg_progress = round($progress / $nb_progress_lp, 2);
  1086. } else {
  1087. $avg_progress = 0;
  1088. }
  1089. if($nb_score_lp > 0) {
  1090. $avg_score = round($score / $nb_score_lp, 2);
  1091. } else {
  1092. $avg_score = '-';
  1093. }
  1094. if($last_login_date) {
  1095. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  1096. } else {
  1097. $last_login_date = '-';
  1098. }
  1099. if($total_score_possible > 0) {
  1100. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  1101. } else {
  1102. $total_score_percentage = 0;
  1103. }
  1104. // time spent in the course
  1105. $csv_row[] = api_time_to_hms($time_spent);
  1106. // student progress in course
  1107. $csv_row[] = $avg_progress;
  1108. // student score
  1109. $csv_row[] = $avg_score;
  1110. // student messages
  1111. $csv_row[] = $nb_messages;
  1112. // student assignments
  1113. $csv_row[] = $nb_assignments;
  1114. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  1115. $csv_row[] = $total_score_obtained;
  1116. $csv_row[] = $total_score_possible;
  1117. $csv_row[] = $total_questions_answered;
  1118. $csv_row[] = $total_score_percentage;
  1119. // last connection
  1120. $csv_row[] = $last_login_date;
  1121. $csv_content[] = $csv_row;
  1122. }
  1123. Export :: export_table_csv($csv_content, 'reporting_course_overview');
  1124. exit;
  1125. }
  1126. /**
  1127. * Display a sortable table that contains an overview of all the reporting progress of all sessions and all courses the user is subscribed to
  1128. * @author Guillaume Viguier <guillaume@viguierjust.com>
  1129. */
  1130. function display_tracking_session_overview() {
  1131. //MySpace::display_user_overview_export_options();
  1132. $t_head .= ' <table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  1133. //$t_head .= ' <caption>'.get_lang('CourseInformation').'</caption>';
  1134. $t_head .= '<tr>';
  1135. $t_head .= ' <th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>';
  1136. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  1137. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  1138. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  1139. //$t_head .= ' <th><div style="width:40px">'.get_lang('AvgExercisesScore').'</div></th>';
  1140. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  1141. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  1142. $t_head .= ' <th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  1143. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePossible').'</div></th>';
  1144. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  1145. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePercentage').'</div></th>';
  1146. //$t_head .= ' <th><div style="width:60px">'.get_lang('FirstLogin').'</div></th>';
  1147. $t_head .= ' <th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  1148. $t_head .= ' </tr></table>';
  1149. $addparams = array('view' => 'admin', 'display' => 'sessionoverview');
  1150. $table = new SortableTable('tracking_session_overview', array('MySpace','get_total_number_sessions'), array('MySpace','get_session_data_tracking_overview'), 1);
  1151. $table->additional_parameters = $addparams;
  1152. $table->set_header(0, '', false, null, array('style' => 'display: none'));
  1153. $table->set_header(1, get_lang('Session'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  1154. $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;'));
  1155. $table->set_column_filter(2, array('MySpace','session_tracking_filter'));
  1156. $table->display();
  1157. }
  1158. /**
  1159. * Get the total number of sessions
  1160. *
  1161. * @return integer Total number of sessions
  1162. */
  1163. public function get_total_number_sessions() {
  1164. // database table definition
  1165. $main_session_table = Database :: get_main_table(TABLE_MAIN_SESSION);
  1166. return Database::count_rows($main_session_table);
  1167. }
  1168. /**
  1169. * Get data for the sessions
  1170. *
  1171. * @param int Inferior limit
  1172. * @param int Number of items to select
  1173. * @param string Column to order on
  1174. * @param string Order direction
  1175. * @return array Results
  1176. */
  1177. public function get_session_data_tracking_overview($from, $number_of_items, $column, $direction) {
  1178. //global $_configuration;
  1179. // database table definition
  1180. //$access_url_id = api_get_current_access_url_id();
  1181. //$tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  1182. $main_session_table = Database :: get_main_table(TABLE_MAIN_SESSION);
  1183. /*if ($_configuration['multiple_access_urls']) {
  1184. $condition_multi_url = ", $tbl_url_rel_user as url_user WHERE user.user_id=url_user.user_id AND access_url_id='$access_url_id'";
  1185. }
  1186. global $export_csv;
  1187. if ($export_csv) {
  1188. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  1189. } else {
  1190. $is_western_name_order = api_is_western_name_order();
  1191. }*/
  1192. $sql = "SELECT id AS col0, name AS col1 FROM $main_session_table";
  1193. $sql .= " ORDER BY col$column $direction ";
  1194. $sql .= " LIMIT $from,$number_of_items";
  1195. $result = Database::query($sql);
  1196. $return = array ();
  1197. while ($session = Database::fetch_row($result)) {
  1198. $return[] = $session;
  1199. }
  1200. return $return;
  1201. }
  1202. /**
  1203. * Fills in session reporting data
  1204. *
  1205. * @param integer $user_id the id of the user
  1206. * @param array $url_params additonal url parameters
  1207. * @param array $row the row information (the other columns)
  1208. * @return string html code
  1209. */
  1210. function session_tracking_filter($session_id, $url_params, $row) {
  1211. $session_id = $row[0];
  1212. // the table header
  1213. $return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  1214. /*$return .= ' <tr>';
  1215. $return .= ' <th>'.get_lang('Course').'</th>';
  1216. $return .= ' <th>'.get_lang('AvgTimeSpentInTheCourse').'</th>';
  1217. $return .= ' <th>'.get_lang('AvgStudentsProgress').'</th>';
  1218. $return .= ' <th>'.get_lang('AvgCourseScore').'</th>';
  1219. $return .= ' <th>'.get_lang('AvgExercisesScore').'</th>';
  1220. $return .= ' <th>'.get_lang('AvgMessages').'</th>';
  1221. $return .= ' <th>'.get_lang('AvgAssignments').'</th>';
  1222. $return .= ' <th>'.get_lang('TotalExercisesScoreObtained').'</th>';
  1223. $return .= ' <th>'.get_lang('TotalExercisesScorePossible').'</th>';
  1224. $return .= ' <th>'.get_lang('TotalExercisesAnswered').'</th>';
  1225. $return .= ' <th>'.get_lang('TotalExercisesScorePercentage').'</th>';
  1226. $return .= ' <th>'.get_lang('FirstLogin').'</th>';
  1227. $return .= ' <th>'.get_lang('LatestLogin').'</th>';
  1228. $return .= ' </tr>';*/
  1229. // database table definition
  1230. $tbl_session_rel_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  1231. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  1232. $tbl_session_rel_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1233. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  1234. // getting all the courses of the user
  1235. $sql = "SELECT * FROM $tbl_course AS c INNER JOIN $tbl_session_rel_course AS sc ON sc.course_code = c.code WHERE sc.id_session = '".$session_id."';";
  1236. $result = Database::query($sql);
  1237. while ($row = Database::fetch_object($result)) {
  1238. $return .= '<tr>';
  1239. // course code
  1240. $return .= ' <td width="157px" >'.$row->title.'</td>';
  1241. // get the users in the course
  1242. $sql = "SELECT user_id FROM $tbl_user AS u INNER JOIN $tbl_session_rel_course_rel_user AS scu ON u.user_id = scu.id_user WHERE scu.id_session = '".$session_id."' AND scu.course_code = '".$row->code."';";
  1243. $result_users = Database::query($sql);
  1244. $time_spent = 0;
  1245. $progress = 0;
  1246. $nb_progress_lp = 0;
  1247. $score = 0;
  1248. $nb_score_lp = 0;
  1249. $nb_messages = 0;
  1250. $nb_assignments = 0;
  1251. $last_login_date = false;
  1252. $total_score_obtained = 0;
  1253. $total_score_possible = 0;
  1254. $total_questions_answered = 0;
  1255. while($row_user = Database::fetch_object($result_users)) {
  1256. // get time spent in the course and session
  1257. $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $row->code, $session_id);
  1258. $progress_tmp = Tracking::get_avg_student_progress($row_user->user_id, $row->code, array(), $session_id, true);
  1259. $progress += $progress_tmp[0];
  1260. $nb_progress_lp += $progress_tmp[1];
  1261. $score_tmp = Tracking :: get_avg_student_score($row_user->user_id, $row->code, array(), $session_id, true);
  1262. if(is_array($score_tmp)) {
  1263. $score += $score_tmp[0];
  1264. $nb_score_lp += $score_tmp[1];
  1265. }
  1266. $nb_messages += Tracking::count_student_messages($row_user->user_id, $row->code, $session_id);
  1267. $nb_assignments += Tracking::count_student_assignments($row_user->user_id, $row->code, $session_id);
  1268. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row_user->user_id, $row->code, $session_id, false);
  1269. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned.
  1270. $last_login_date = $last_login_date_tmp;
  1271. } else if($last_login_date_tmp != false && $last_login_date != false) { // TODO: Repeated previous condition! To be cleaned.
  1272. // Find the max and assign it to first_login_date
  1273. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  1274. $last_login_date = $last_login_date_tmp;
  1275. }
  1276. }
  1277. $exercise_results_tmp = MySpace::exercises_results($row_user->user_id, $row->code, $session_id);
  1278. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  1279. $total_score_possible += $exercise_results_tmp['score_possible'];
  1280. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  1281. }
  1282. if($nb_progress_lp > 0) {
  1283. $avg_progress = round($progress / $nb_progress_lp, 2);
  1284. } else {
  1285. $avg_progress = 0;
  1286. }
  1287. if($nb_score_lp > 0) {
  1288. $avg_score = round($score / $nb_score_lp, 2);
  1289. } else {
  1290. $avg_score = '-';
  1291. }
  1292. if($last_login_date) {
  1293. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  1294. } else {
  1295. $last_login_date = '-';
  1296. }
  1297. if($total_score_possible > 0) {
  1298. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  1299. } else {
  1300. $total_score_percentage = 0;
  1301. }
  1302. if($total_score_percentage > 0) {
  1303. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  1304. } else {
  1305. $total_score = '-';
  1306. }
  1307. // time spent in the course
  1308. $return .= ' <td><div>'.api_time_to_hms($time_spent).'</div></td>';
  1309. // student progress in course
  1310. $return .= ' <td><div>'.$avg_progress.'</div></td>';
  1311. // student score
  1312. $return .= ' <td><div>'.$avg_score.'</div></td>';
  1313. // student messages
  1314. $return .= ' <td><div>'.$nb_messages.'</div></td>';
  1315. // student assignments
  1316. $return .= ' <td><div>'.$nb_assignments.'</div></td>';
  1317. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  1318. $return .= '<td width="105px;">'.$total_score.'</td>';
  1319. $return .= '<td>'.$total_questions_answered.'</td>';
  1320. // last connection
  1321. $return .= ' <td><div>'.$last_login_date.'</div></td>';
  1322. $return .= '<tr>';
  1323. }
  1324. $return .= '</table>';
  1325. return $return;
  1326. }
  1327. /**
  1328. * This function exports the table that we see in display_tracking_session_overview()
  1329. *
  1330. */
  1331. function export_tracking_session_overview() {
  1332. // database table definition
  1333. $tbl_session_rel_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  1334. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  1335. $tbl_session_rel_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1336. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  1337. // the values of the sortable table
  1338. if ($_GET['tracking_session_overview_page_nr']) {
  1339. $from = $_GET['tracking_session_overview_page_nr'];
  1340. } else {
  1341. $from = 0;
  1342. }
  1343. if ($_GET['tracking_session_overview_column']) {
  1344. $orderby = $_GET['tracking_session_overview_column'];
  1345. } else {
  1346. $orderby = 0;
  1347. }
  1348. if ($_GET['tracking_session_overview_direction']) {
  1349. $direction = $_GET['tracking_session_overview_direction'];
  1350. } else {
  1351. $direction = 'ASC';
  1352. }
  1353. $session_data = MySpace::get_session_data_tracking_overview($from, 1000, $orderby, $direction);
  1354. $csv_content = array();
  1355. // the first line of the csv file with the column headers
  1356. $csv_row = array();
  1357. $csv_row[] = get_lang('Session');
  1358. $csv_row[] = get_lang('Course', '');
  1359. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  1360. $csv_row[] = get_lang('AvgStudentsProgress', '');
  1361. $csv_row[] = get_lang('AvgCourseScore', '');
  1362. $csv_row[] = get_lang('TotalNumberOfMessages', '');
  1363. $csv_row[] = get_lang('TotalNumberOfAssignments', '');
  1364. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  1365. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  1366. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  1367. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  1368. $csv_row[] = get_lang('LatestLogin', '');
  1369. $csv_content[] = $csv_row;
  1370. // the other lines (the data)
  1371. foreach ($session_data as $key => $session) {
  1372. $session_id = $session[0];
  1373. $session_title = $session[1];
  1374. // getting all the courses of the session
  1375. $sql = "SELECT * FROM $tbl_course AS c INNER JOIN $tbl_session_rel_course AS sc ON sc.course_code = c.code WHERE sc.id_session = '".$session_id."';";
  1376. $result = Database::query($sql);
  1377. while ($row = Database::fetch_object($result)) {
  1378. $csv_row = array();
  1379. $csv_row[] = $session_title;
  1380. $csv_row[] = $row->title;
  1381. // get the users in the course
  1382. $sql = "SELECT user_id FROM $tbl_user AS u INNER JOIN $tbl_session_rel_course_rel_user AS scu ON u.user_id = scu.id_user WHERE scu.id_session = '".$session_id."' AND scu.course_code = '".$row->code."';";
  1383. $result_users = Database::query($sql);
  1384. $time_spent = 0;
  1385. $progress = 0;
  1386. $nb_progress_lp = 0;
  1387. $score = 0;
  1388. $nb_score_lp = 0;
  1389. $nb_messages = 0;
  1390. $nb_assignments = 0;
  1391. $last_login_date = false;
  1392. $total_score_obtained = 0;
  1393. $total_score_possible = 0;
  1394. $total_questions_answered = 0;
  1395. while($row_user = Database::fetch_object($result_users)) {
  1396. // get time spent in the course and session
  1397. $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $row->code, $session_id);
  1398. $progress_tmp = Tracking::get_avg_student_progress($row_user->user_id, $row->code, array(), $session_id, true);
  1399. $progress += $progress_tmp[0];
  1400. $nb_progress_lp += $progress_tmp[1];
  1401. $score_tmp = Tracking :: get_avg_student_score($row_user->user_id, $row->code, array(), $session_id, true);
  1402. if(is_array($score_tmp)) {
  1403. $score += $score_tmp[0];
  1404. $nb_score_lp += $score_tmp[1];
  1405. }
  1406. $nb_messages += Tracking::count_student_messages($row_user->user_id, $row->code, $session_id);
  1407. $nb_assignments += Tracking::count_student_assignments($row_user->user_id, $row->code, $session_id);
  1408. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row_user->user_id, $row->code, $session_id, false);
  1409. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned.
  1410. $last_login_date = $last_login_date_tmp;
  1411. } else if($last_login_date_tmp != false && $last_login_date == false) { // TODO: Repeated previous condition. To be cleaned.
  1412. // Find the max and assign it to first_login_date
  1413. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  1414. $last_login_date = $last_login_date_tmp;
  1415. }
  1416. }
  1417. $exercise_results_tmp = MySpace::exercises_results($row_user->user_id, $row->code, $session_id);
  1418. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  1419. $total_score_possible += $exercise_results_tmp['score_possible'];
  1420. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  1421. }
  1422. if($nb_progress_lp > 0) {
  1423. $avg_progress = round($progress / $nb_progress_lp, 2);
  1424. } else {
  1425. $avg_progress = 0;
  1426. }
  1427. if($nb_score_lp > 0) {
  1428. $avg_score = round($score / $nb_score_lp, 2);
  1429. } else {
  1430. $avg_score = '-';
  1431. }
  1432. if($last_login_date) {
  1433. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  1434. } else {
  1435. $last_login_date = '-';
  1436. }
  1437. if($total_score_possible > 0) {
  1438. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  1439. } else {
  1440. $total_score_percentage = 0;
  1441. }
  1442. if($total_score_percentage > 0) {
  1443. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  1444. } else {
  1445. $total_score = '-';
  1446. }
  1447. // time spent in the course
  1448. $csv_row[] = api_time_to_hms($time_spent);
  1449. // student progress in course
  1450. $csv_row[] = $avg_progress;
  1451. // student score
  1452. $csv_row[] = $avg_score;
  1453. // student messages
  1454. $csv_row[] = $nb_messages;
  1455. // student assignments
  1456. $csv_row[] = $nb_assignments;
  1457. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  1458. $csv_row[] = $total_score_obtained;
  1459. $csv_row[] = $total_score_possible;
  1460. $csv_row[] = $total_questions_answered;
  1461. $csv_row[] = $total_score_percentage;
  1462. // last connection
  1463. $csv_row[] = $last_login_date;
  1464. $csv_content[] = $csv_row;
  1465. }
  1466. }
  1467. Export :: export_table_csv($csv_content, 'reporting_session_overview');
  1468. exit;
  1469. }
  1470. /**
  1471. * Get general information about the exercise performance of the user
  1472. * the total obtained score (all the score on all the questions)
  1473. * the maximum score that could be obtained
  1474. * the number of questions answered
  1475. * the success percentage
  1476. * @param integer $user_id the id of the user
  1477. * @param string $course_code the course code
  1478. * @return array
  1479. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1480. * @version Dokeos 1.8.6
  1481. * @since November 2008
  1482. */
  1483. function exercises_results($user_id, $course_code, $session_id = false) {
  1484. $questions_answered = 0;
  1485. $sql = 'SELECT exe_result , exe_weighting
  1486. FROM '.Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES)."
  1487. WHERE exe_cours_id = '".Database::escape_string($course_code)."'
  1488. AND exe_user_id = '".Database::escape_string($user_id)."'";
  1489. if($session_id !== false) {
  1490. $sql .= " AND session_id = '".$session_id."' ";
  1491. }
  1492. $result = Database::query($sql);
  1493. $score_obtained = 0;
  1494. $score_possible = 0;
  1495. $questions_answered = 0;
  1496. while ($row = Database::fetch_array($result)) {
  1497. $score_obtained += $row['exe_result'];
  1498. $score_possible += $row['exe_weighting'];
  1499. $questions_answered ++;
  1500. }
  1501. if ($score_possible != 0) {
  1502. $percentage = round(($score_obtained / $score_possible * 100), 2);
  1503. } else {
  1504. $percentage = null;
  1505. }
  1506. return array('score_obtained' => $score_obtained, 'score_possible' => $score_possible, 'questions_answered' => $questions_answered, 'percentage' => $percentage);
  1507. }
  1508. /**
  1509. * This function exports the table that we see in display_tracking_user_overview()
  1510. *
  1511. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1512. * @version Dokeos 1.8.6
  1513. * @since October 2008
  1514. */
  1515. function export_tracking_user_overview() {
  1516. // database table definitions
  1517. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1518. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  1519. $sort_by_first_name = api_sort_by_first_name();
  1520. // the values of the sortable table
  1521. if ($_GET['tracking_user_overview_page_nr']) {
  1522. $from = $_GET['tracking_user_overview_page_nr'];
  1523. } else {
  1524. $from = 0;
  1525. }
  1526. if ($_GET['tracking_user_overview_column']) {
  1527. $orderby = $_GET['tracking_user_overview_column'];
  1528. } else {
  1529. $orderby = 0;
  1530. }
  1531. if ($is_western_name_order != api_is_western_name_order() && ($orderby == 1 || $orderby == 2)) {
  1532. // Swapping the sorting column if name order for export is different than the common name order.
  1533. $orderby = 3 - $orderby;
  1534. }
  1535. if ($_GET['tracking_user_overview_direction']) {
  1536. $direction = $_GET['tracking_user_overview_direction'];
  1537. } else {
  1538. $direction = 'ASC';
  1539. }
  1540. $user_data = MySpace::get_user_data_tracking_overview($from, 1000, $orderby, $direction);
  1541. // the first line of the csv file with the column headers
  1542. $csv_row = array();
  1543. $csv_row[] = get_lang('OfficialCode');
  1544. if ($is_western_name_order) {
  1545. $csv_row[] = get_lang('FirstName', '');
  1546. $csv_row[] = get_lang('LastName', '');
  1547. } else {
  1548. $csv_row[] = get_lang('LastName', '');
  1549. $csv_row[] = get_lang('FirstName', '');
  1550. }
  1551. $csv_row[] = get_lang('LoginName');
  1552. $csv_row[] = get_lang('CourseCode');
  1553. // the additional user defined fields (only those that were selected to be exported)
  1554. $fields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  1555. if (is_array($_SESSION['additional_export_fields'])) {
  1556. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  1557. $csv_row[] = $fields[$extra_field_export][3];
  1558. $field_names_to_be_exported[] = 'extra_'.$fields[$extra_field_export][1];
  1559. }
  1560. }
  1561. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  1562. $csv_row[] = get_lang('AvgStudentsProgress', '');
  1563. $csv_row[] = get_lang('AvgCourseScore', '');
  1564. $csv_row[] = get_lang('AvgExercisesScore', '');
  1565. $csv_row[] = get_lang('AvgMessages', '');
  1566. $csv_row[] = get_lang('AvgAssignments', '');
  1567. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  1568. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  1569. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  1570. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  1571. $csv_row[] = get_lang('FirstLogin', '');
  1572. $csv_row[] = get_lang('LatestLogin', '');
  1573. $csv_content[] = $csv_row;
  1574. // the other lines (the data)
  1575. foreach ($user_data as $key => $user) {
  1576. // getting all the courses of the user
  1577. $sql = "SELECT * FROM $tbl_course_user WHERE user_id = '".Database::escape_string($user[4])."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  1578. $result = Database::query($sql);
  1579. while ($row = Database::fetch_row($result)) {
  1580. $csv_row = array();
  1581. // user official code
  1582. $csv_row[] = $user[0];
  1583. // user first|last name
  1584. $csv_row[] = $user[1];
  1585. // user last|first name
  1586. $csv_row[] = $user[2];
  1587. // user login name
  1588. $csv_row[] = $user[3];
  1589. // course code
  1590. $csv_row[] = $row[0];
  1591. // the additional defined user fields
  1592. $extra_fields = MySpace::get_user_overview_export_extra_fields($user[4]);
  1593. if (is_array($field_names_to_be_exported)) {
  1594. foreach ($field_names_to_be_exported as $key => $extra_field_export) {
  1595. $csv_row[] = $extra_fields[$extra_field_export];
  1596. }
  1597. }
  1598. // time spent in the course
  1599. $csv_row[] = api_time_to_hms(Tracking :: get_time_spent_on_the_course ($user[4], $row[0]));
  1600. // student progress in course
  1601. $csv_row[] = round(Tracking :: get_avg_student_progress ($user[4], $row[0]), 2);
  1602. // student score
  1603. $csv_row[] = round(Tracking :: get_avg_student_score ($user[4], $row[0]), 2);
  1604. // student tes score
  1605. $csv_row[] = round(Tracking :: get_avg_student_exercise_score ($user[4], $row[0]), 2);
  1606. // student messages
  1607. $csv_row[] = Tracking :: count_student_messages ($user[4], $row[0]);
  1608. // student assignments
  1609. $csv_row[] = Tracking :: count_student_assignments ($user[4], $row[0]);
  1610. // student exercises results
  1611. $exercises_results = MySpace::exercises_results($user[4], $row[0]);
  1612. $csv_row[] = $exercises_results['score_obtained'];
  1613. $csv_row[] = $exercises_results['score_possible'];
  1614. $csv_row[] = $exercises_results['questions_answered'];
  1615. $csv_row[] = $exercises_results['percentage'];
  1616. // first connection
  1617. $csv_row[] = Tracking :: get_first_connection_date_on_the_course ($user[4], $row[0]);
  1618. // last connection
  1619. $csv_row[] = strip_tags(Tracking :: get_last_connection_date_on_the_course ($user[4], $row[0]));
  1620. $csv_content[] = $csv_row;
  1621. }
  1622. }
  1623. Export :: export_table_csv($csv_content, 'reporting_user_overview');
  1624. exit;
  1625. }
  1626. /**
  1627. * Get data for courses list in sortable with pagination
  1628. * @return array
  1629. */
  1630. static function get_course_data($from, $number_of_items, $column, $direction) {
  1631. global $courses, $csv_content, $charset, $session_id;
  1632. // definition database tables
  1633. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  1634. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1635. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1636. $course_data = array();
  1637. $courses_code = array_keys($courses);
  1638. foreach ($courses_code as &$code) {
  1639. $code = "'$code'";
  1640. }
  1641. // get all courses with limit
  1642. $sql = "SELECT course.code as col1, course.title as col2
  1643. FROM $tbl_course course
  1644. WHERE course.code IN (".implode(',',$courses_code).")";
  1645. if (!in_array($direction, array('ASC','DESC'))) $direction = 'ASC';
  1646. $column = intval($column);
  1647. $from = intval($from);
  1648. $number_of_items = intval($number_of_items);
  1649. $sql .= " ORDER BY col$column $direction ";
  1650. $sql .= " LIMIT $from,$number_of_items";
  1651. $res = Database::query($sql);
  1652. while ($row_course = Database::fetch_row($res)) {
  1653. $course_code = $row_course[0];
  1654. $course_info = api_get_course_info($course_code);
  1655. $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;
  1656. // students directly subscribed to the course
  1657. if (empty($session_id)) {
  1658. $sql = "SELECT user_id FROM $tbl_course_user as course_rel_user WHERE course_rel_user.status='5' AND course_rel_user.course_code='$course_code'";
  1659. } else {
  1660. $sql = "SELECT id_user as user_id FROM $tbl_session_course_user srcu WHERE srcu. course_code='$course_code' AND id_session = '$session_id' AND srcu.status<>2";
  1661. }
  1662. $rs = Database::query($sql);
  1663. $users = array();
  1664. while ($row = Database::fetch_array($rs)) { $users[] = $row['user_id']; }
  1665. if (count($users) > 0) {
  1666. $nb_students_in_course = count($users);
  1667. $avg_assignments_in_course = Tracking::count_student_assignments($users, $course_code, $session_id);
  1668. $avg_messages_in_course = Tracking::count_student_messages($users, $course_code, $session_id);
  1669. $avg_progress_in_course = Tracking::get_avg_student_progress($users, $course_code, array(), $session_id);
  1670. $avg_score_in_course = Tracking::get_avg_student_score($users, $course_code, array(), $session_id);
  1671. $avg_score_in_exercise = Tracking::get_avg_student_exercise_score($users, $course_code, 0, $session_id);
  1672. $avg_time_spent_in_course = Tracking::get_time_spent_on_the_course($users, $course_code, $session_id);
  1673. $avg_progress_in_course = round($avg_progress_in_course / $nb_students_in_course, 2);
  1674. if (is_numeric($avg_score_in_course)) {
  1675. $avg_score_in_course = round($avg_score_in_course / $nb_students_in_course, 2);
  1676. }
  1677. $avg_time_spent_in_course = api_time_to_hms($avg_time_spent_in_course / $nb_students_in_course);
  1678. } else {
  1679. $avg_time_spent_in_course = null;
  1680. $avg_progress_in_course = null;
  1681. $avg_score_in_course = null;
  1682. $avg_score_in_exercise = null;
  1683. $avg_messages_in_course = null;
  1684. $avg_assignments_in_course = null;
  1685. }
  1686. $table_row = array();
  1687. $table_row[] = $row_course[1];
  1688. $table_row[] = $nb_students_in_course;
  1689. $table_row[] = $avg_time_spent_in_course;
  1690. $table_row[] = is_null($avg_progress_in_course) ? '' : $avg_progress_in_course.'%';
  1691. $table_row[] = is_null($avg_score_in_course) ? '' : $avg_score_in_course.'%';
  1692. $table_row[] = is_null($avg_score_in_exercise) ? '' : $avg_score_in_exercise.'%';
  1693. $table_row[] = $avg_messages_in_course;
  1694. $table_row[] = $avg_assignments_in_course;
  1695. //set the "from" value to know if I access the Reporting by the chamilo tab or the course link
  1696. $table_row[] = '<center><a href="../tracking/courseLog.php?cidReq='.$course_code.'&from=myspace&id_session='.$session_id.'">
  1697. <img src="'.api_get_path(WEB_IMG_PATH).'2rightarrow.gif" border="0" /></a>
  1698. </center>';
  1699. $csv_content[] = array(
  1700. api_html_entity_decode($row_course[1], ENT_QUOTES, $charset),
  1701. $nb_students_in_course,
  1702. $avg_time_spent_in_course,
  1703. is_null($avg_progress_in_course) ? null : $avg_progress_in_course.'%',
  1704. is_null($avg_score_in_course) ? null : is_numeric($avg_score_in_course) ? $avg_score_in_course.'%' : $avg_score_in_course ,
  1705. is_null($avg_score_in_exercise) ? null : $avg_score_in_exercise.'%',
  1706. $avg_messages_in_course,
  1707. $avg_assignments_in_course,
  1708. );
  1709. $course_data[] = $table_row;
  1710. }
  1711. return $course_data;
  1712. }
  1713. /**
  1714. * get the numer of users of the platform
  1715. *
  1716. * @return integer
  1717. *
  1718. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1719. * @version Dokeos 1.8.6
  1720. * @since October 2008
  1721. */
  1722. function get_number_of_users_tracking_overview() {
  1723. // database table definition
  1724. $main_user_table = Database :: get_main_table(TABLE_MAIN_USER);
  1725. return Database::count_rows($main_user_table);
  1726. }
  1727. /**
  1728. * get all the data for the sortable table of the reporting progress of all users and all the courses the user is subscribed to.
  1729. *
  1730. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1731. * @version Dokeos 1.8.6
  1732. * @since October 2008
  1733. */
  1734. function get_user_data_tracking_overview($from, $number_of_items, $column, $direction) {
  1735. global $_configuration;
  1736. // database table definition
  1737. $access_url_id = api_get_current_access_url_id();
  1738. $tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  1739. $main_user_table = Database::get_main_table(TABLE_MAIN_USER);
  1740. if ($_configuration['multiple_access_urls']) {
  1741. $condition_multi_url = ", $tbl_url_rel_user as url_user WHERE user.user_id=url_user.user_id AND access_url_id='$access_url_id'";
  1742. }
  1743. global $export_csv;
  1744. if ($export_csv) {
  1745. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  1746. } else {
  1747. $is_western_name_order = api_is_western_name_order();
  1748. }
  1749. $sql = "SELECT
  1750. official_code AS col0,
  1751. ".($is_western_name_order ? "
  1752. firstname AS col1,
  1753. lastname AS col2,
  1754. " : "
  1755. lastname AS col1,
  1756. firstname AS col2,
  1757. ").
  1758. "username AS col3,
  1759. user.user_id AS col4
  1760. FROM
  1761. $main_user_table as user $condition_multi_url
  1762. ";
  1763. $sql .= " ORDER BY col$column $direction ";
  1764. $sql .= " LIMIT $from,$number_of_items";
  1765. $result = Database::query($sql);
  1766. $return = array ();
  1767. while ($user = Database::fetch_row($result)) {
  1768. $return[] = $user;
  1769. }
  1770. return $return;
  1771. }
  1772. /**
  1773. * Get all information that the user with user_id = $user_data has
  1774. * entered in the additionally defined profile fields
  1775. * @param integer $user_id the id of the user
  1776. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1777. * @version Dokeos 1.8.6
  1778. * @since November 2008
  1779. */
  1780. function get_user_overview_export_extra_fields($user_id) {
  1781. // include the user manager
  1782. $extra_data = UserManager::get_extra_user_data($user_id, true);
  1783. return $extra_data;
  1784. }
  1785. /**
  1786. * Checks if a username exist in the DB otherwise it create a "double"
  1787. * i.e. if we look into for jmontoya but the user's name already exist we create the user jmontoya2
  1788. * the return array will be array(username=>'jmontoya', sufix='2')
  1789. * @param string firstname
  1790. * @param string lastname
  1791. * @param string username
  1792. * @return array with the username, the sufix
  1793. * @author Julio Montoya Armas
  1794. */
  1795. function make_username($firstname, $lastname, $username, $language = null, $encoding = null) {
  1796. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  1797. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1798. // if username exist
  1799. if (!UserManager::is_username_available($username) || empty($username)) {
  1800. $i = 0;
  1801. while (1) {
  1802. if ($i == 0) {
  1803. $sufix = '';
  1804. } else {
  1805. $sufix = $i;
  1806. }
  1807. $desired_username = UserManager::create_username($firstname, $lastname, $language, $encoding);
  1808. if (UserManager::is_username_available($desired_username.$sufix)) {
  1809. break;
  1810. } else {
  1811. $i++;
  1812. }
  1813. }
  1814. $username_array = array('username' => $desired_username , 'sufix' => $sufix);
  1815. return $username_array;
  1816. } else {
  1817. $username_array = array('username' => $username, 'sufix' => '');
  1818. return $username_array;
  1819. }
  1820. }
  1821. /**
  1822. * Checks if there are repeted users in a given array
  1823. * @param array $usernames list of the usernames in the uploaded file
  1824. * @param array $user_array['username'] and $user_array['sufix'] where sufix is the number part in a login i.e -> jmontoya2
  1825. * @return array with the $usernames array and the $user_array array
  1826. * @author Julio Montoya Armas
  1827. */
  1828. function check_user_in_array($usernames, $user_array) {
  1829. $user_list = array_keys($usernames);
  1830. $username = $user_array['username'].$user_array['sufix'];
  1831. if (in_array($username, $user_list)) {
  1832. $user_array['sufix'] += $usernames[$username];
  1833. $usernames[$username]++;
  1834. } else {
  1835. $usernames[$username] = 1;
  1836. }
  1837. $result_array = array($usernames, $user_array);
  1838. return $result_array;
  1839. }
  1840. /**
  1841. * Checks whether a username has been already subscribed in a session.
  1842. * @param string a given username
  1843. * @param array the array with the course list codes
  1844. * @param the session id
  1845. * @return 0 if the user is not subscribed otherwise it returns the user_id of the given username
  1846. * @author Julio Montoya Armas
  1847. */
  1848. function user_available_in_session($username, $course_list, $id_session) {
  1849. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  1850. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1851. $id_session = intval($id_session);
  1852. $username = Database::escape_string($username);
  1853. foreach($course_list as $enreg_course) {
  1854. $sql_select = " SELECT u.user_id FROM $tbl_session_rel_course_rel_user rel INNER JOIN $table_user u
  1855. ON (rel.id_user=u.user_id)
  1856. WHERE rel.id_session='$id_session' AND u.status='5' AND u.username ='$username' AND rel.course_code='$enreg_course'";
  1857. $rs = Database::query($sql_select);
  1858. if (Database::num_rows($rs) > 0) {
  1859. return Database::result($rs, 0, 0);
  1860. } else {
  1861. return 0;
  1862. }
  1863. }
  1864. }
  1865. /**
  1866. This function checks whether some users in the uploaded file repeated and creates unique usernames if necesary.
  1867. A case: Within the file there is an user repeted twice (Julio Montoya / Julio Montoya) and the username fields are empty.
  1868. Then, this function would create unique usernames based on the first and the last name. Two users wiould be created - jmontoya and jmontoya2.
  1869. Of course, if in the database there is a user with the name jmontoya, the newly created two users registered would be jmontoya2 and jmontoya3.
  1870. @param $users list of users
  1871. @author Julio Montoya Armas
  1872. */
  1873. function check_all_usernames($users, $course_list, $id_session) {
  1874. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  1875. $usernames = array();
  1876. $new_users = array();
  1877. foreach ($users as $index => $user) {
  1878. $desired_username = array();
  1879. if (empty($user['UserName'])) {
  1880. $desired_username = MySpace::make_username($user['FirstName'], $user['LastName'], '');
  1881. $pre_username = $desired_username['username'].$desired_username['sufix'];
  1882. $user['UserName'] = $pre_username;
  1883. $user['create'] = '1';
  1884. } else {
  1885. if (UserManager::is_username_available($user['UserName'])) {
  1886. $desired_username = MySpace::make_username($user['FirstName'], $user['LastName'], $user['UserName']);
  1887. $user['UserName'] = $desired_username['username'].$desired_username['sufix'];
  1888. $user['create'] = '1';
  1889. } else {
  1890. $is_session_avail = MySpace::user_available_in_session($user['UserName'], $course_list, $id_session);
  1891. if ($is_session_avail == 0) {
  1892. $user_name = $user['UserName'];
  1893. $sql_select = "SELECT user_id FROM $table_user WHERE username ='$user_name' ";
  1894. $rs = Database::query($sql_select);
  1895. $user['create'] = Database::result($rs, 0, 0); // This should be the ID because the user exists.
  1896. } else {
  1897. $user['create'] = $is_session_avail;
  1898. }
  1899. }
  1900. }
  1901. // Usernames is the current list of users in the file.
  1902. $result_array = MySpace::check_user_in_array($usernames, $desired_username);
  1903. $usernames = $result_array[0];
  1904. $desired_username = $result_array[1];
  1905. $user['UserName'] = $desired_username['username'].$desired_username['sufix'];
  1906. $new_users[] = $user;
  1907. }
  1908. return $new_users;
  1909. }
  1910. /**
  1911. * This functions checks whether there are users that are already registered in the DB by different creator than the current coach.
  1912. * @param string a given username
  1913. * @param array the array with the course list codes
  1914. * @param the session id
  1915. * @author Julio Montoya Armas
  1916. */
  1917. function get_user_creator($users, $course_list, $id_session) {
  1918. $errors = array();
  1919. foreach ($users as $index => $user) {
  1920. // database table definition
  1921. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  1922. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1923. $username = Database::escape_string($user['UserName']);
  1924. //echo "<br>";
  1925. $sql = "SELECT creator_id FROM $table_user WHERE username='$username' ";
  1926. $rs = Database::query($sql);
  1927. $creator_id = Database::result($rs, 0, 0);
  1928. // check if we are the creators or not
  1929. if ($creator_id != '') {
  1930. if ($creator_id != api_get_user_id()) {
  1931. $user['error'] = get_lang('UserAlreadyRegisteredByOtherCreator');
  1932. $errors[] = $user;
  1933. }
  1934. }
  1935. }
  1936. return $errors;
  1937. }
  1938. /**
  1939. * Validates imported data.
  1940. * @param list of users
  1941. */
  1942. function validate_data($users, $id_session = null) {
  1943. $errors = array();
  1944. $usernames = array();
  1945. $new_users = array();
  1946. foreach ($users as $index => $user) {
  1947. // 1. Check whether mandatory fields are set.
  1948. $mandatory_fields = array('LastName', 'FirstName');
  1949. if (api_get_setting('registration', 'email') == 'true') {
  1950. $mandatory_fields[] = 'Email';
  1951. }
  1952. foreach ($mandatory_fields as $key => $field) {
  1953. if (!isset ($user[$field]) || strlen($user[$field]) == 0) {
  1954. $user['error'] = get_lang($field.'Mandatory');
  1955. $errors[] = $user;
  1956. }
  1957. }
  1958. // 2. Check whether the username is too long.
  1959. if (UserManager::is_username_too_long($user['UserName'])) {
  1960. $user['error'] = get_lang('UserNameTooLong');
  1961. $errors[] = $user;
  1962. }
  1963. $user['UserName'] = trim($user['UserName']);
  1964. if (empty($user['UserName'])) {
  1965. $user['UserName'] = UserManager::create_username($user['FirstName'], $user['LastName']);
  1966. }
  1967. $new_users[] = $user;
  1968. }
  1969. $results = array('errors' => $errors, 'users' => $new_users);
  1970. return $results;
  1971. }
  1972. /**
  1973. * Adds missing user-information (which isn't required, like password, etc).
  1974. */
  1975. function complete_missing_data($user) {
  1976. // 1. Generate a password if it is necessary.
  1977. if (!isset ($user['Password']) || strlen($user['Password']) == 0) {
  1978. $user['Password'] = api_generate_password();
  1979. }
  1980. return $user;
  1981. }
  1982. /**
  1983. * Saves imported data.
  1984. */
  1985. function save_data($users, $course_list, $id_session) {
  1986. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  1987. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1988. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1989. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1990. $id_session = intval($id_session);
  1991. $sendMail = $_POST['sendMail'] ? 1 : 0;
  1992. // Adding users to the platform.
  1993. $new_users = array();
  1994. foreach ($users as $index => $user) {
  1995. $user = MySpace::complete_missing_data($user);
  1996. // coach only will registered users
  1997. $default_status = '5';
  1998. if ($user['create'] == '1') {
  1999. $user['id'] = UserManager :: create_user($user['FirstName'], $user['LastName'], $default_status, $user['Email'], $user['UserName'], $user['Password'], $user['OfficialCode'], api_get_setting('PlatformLanguage'), $user['PhoneNumber'], '');
  2000. $user['added_at_platform'] = 1;
  2001. } else {
  2002. $user['id'] = $user['create'];
  2003. $user['added_at_platform'] = 0;
  2004. }
  2005. $new_users[] = $user;
  2006. }
  2007. // Update user list.
  2008. $users = $new_users;
  2009. // Inserting users.
  2010. $super_list = array();
  2011. foreach ($course_list as $enreg_course) {
  2012. $nbr_users = 0;
  2013. $new_users = array();
  2014. $enreg_course = Database::escape_string($enreg_course);
  2015. foreach ($users as $index => $user) {
  2016. $userid = intval($user['id']);
  2017. $sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user(id_session,course_code,id_user) VALUES('$id_session','$enreg_course','$userid')";
  2018. $course_session = array('course' => $enreg_course, 'added' => 1);
  2019. //$user['added_at_session'] = $course_session;
  2020. Database::query($sql);
  2021. if (Database::affected_rows()) {
  2022. $nbr_users++;
  2023. }
  2024. $new_users[] = $user;
  2025. }
  2026. $super_list[] = $new_users;
  2027. //update the nbr_users field
  2028. $sql_select = "SELECT COUNT(id_user) as nbUsers FROM $tbl_session_rel_course_rel_user WHERE id_session='$id_session' AND course_code='$enreg_course'";
  2029. $rs = Database::query($sql_select);
  2030. list($nbr_users) = Database::fetch_array($rs);
  2031. $sql_update = "UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users WHERE id_session='$id_session' AND course_code='$enreg_course'";
  2032. Database::query($sql_update);
  2033. $sql_update = "UPDATE $tbl_session SET nbr_users= '$nbr_users' WHERE id='$id_session'";
  2034. Database::query($sql_update);
  2035. }
  2036. // We don't delete the users (thoughts while dreaming)
  2037. //$sql_delete = "DELETE FROM $tbl_session_rel_user WHERE id_session = '$id_session'";
  2038. //Database::query($sql_delete);
  2039. $new_users = array();
  2040. foreach ($users as $index => $user) {
  2041. $userid = $user['id'];
  2042. $sql_insert = "INSERT IGNORE INTO $tbl_session_rel_user(id_session, id_user) VALUES('$id_session','$userid')";
  2043. Database::query($sql_insert);
  2044. $user['added_at_session'] = 1;
  2045. $new_users[] = $user;
  2046. }
  2047. $users = $new_users;
  2048. $registered_users = get_lang('FileImported').'<br /> Import file results : <br />';
  2049. // Sending emails.
  2050. $addedto = '';
  2051. if ($sendMail) {
  2052. $i = 0;
  2053. foreach ($users as $index => $user) {
  2054. $emailto = api_get_person_name($user['FirstName'], $user['LastName'], null, PERSON_NAME_EMAIL_ADDRESS).' <'.$user['Email'].'>';
  2055. $emailsubject = '['.api_get_setting('siteName').'] '.get_lang('YourReg').' '.api_get_setting('siteName');
  2056. $emailbody = get_lang('Dear').' '.api_get_person_name($user['FirstName'], $user['LastName']).",\n\n".get_lang('YouAreReg')." ".api_get_setting('siteName')." ".get_lang('WithTheFollowingSettings')."\n\n".get_lang('Username')." : $user[UserName]\n".get_lang('Pass')." : $user[Password]\n\n".get_lang('Address')." ".api_get_setting('siteName')." ".get_lang('Is')." : ".api_get_path(WEB_PATH)." \n\n".get_lang('Problem')."\n\n".get_lang('Formula').",\n\n".api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname'))."\n".get_lang('Manager')." ".api_get_setting('siteName')."\nT. ".api_get_setting('administratorTelephone')."\n".get_lang('Email')." : ".api_get_setting('emailAdministrator')."";
  2057. $emailheaders = 'From: '.api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname'), null, PERSON_NAME_EMAIL_ADDRESS).' <'.api_get_setting('emailAdministrator').">\n";
  2058. $emailheaders .= 'Reply-To: '.api_get_setting('emailAdministrator');
  2059. @api_send_mail($emailto, $emailsubject, $emailbody, $emailheaders);
  2060. if (($user['added_at_platform'] == 1 && $user['added_at_session'] == 1) || $user['added_at_session'] == 1) {
  2061. if ($user['added_at_platform'] == 1) {
  2062. $addedto = get_lang('UserCreatedPlatform');
  2063. } else {
  2064. $addedto = ' ';
  2065. }
  2066. if ($user['added_at_session'] == 1) {
  2067. $addedto .= get_lang('UserInSession');
  2068. }
  2069. $registered_users .= "<a href=\"../user/userInfo.php?uInfo=".$user['id']."\">".api_get_person_name($user['FirstName'], $user['LastName'])."</a> - ".$addedto.'<br />';
  2070. } else {
  2071. $addedto = get_lang('UserNotAdded');
  2072. $registered_users .= "<a href=\"../user/userInfo.php?uInfo=".$user['id']."\">".api_get_person_name($user['FirstName'], $user['LastName'])."</a> - ".$addedto.'<br />';
  2073. }
  2074. }
  2075. } else {
  2076. $i = 0;
  2077. foreach ($users as $index => $user) {
  2078. if (($user['added_at_platform'] == 1 && $user['added_at_session'] == 1) || $user['added_at_session'] == 1) {
  2079. if ($user['added_at_platform'] == 1) {
  2080. $addedto = get_lang('UserCreatedPlatform');
  2081. } else {
  2082. $addedto = ' ';
  2083. }
  2084. if ($user['added_at_session'] == 1) {
  2085. $addedto .= ' '.get_lang('UserInSession');
  2086. }
  2087. $registered_users .= "<a href=\"../user/userInfo.php?uInfo=".$user['id']."\">".api_get_person_name($user['FirstName'], $user['LastName'])."</a> - ".$addedto.'<br />';
  2088. } else {
  2089. $addedto = get_lang('UserNotAdded');
  2090. $registered_users .= "<a href=\"../user/userInfo.php?uInfo=".$user['id']."\">".api_get_person_name($user['FirstName'], $user['LastName'])."</a> - ".$addedto.'<br />';
  2091. }
  2092. }
  2093. }
  2094. header('Location: course.php?id_session='.$id_session.'&action=show_message&message='.urlencode($registered_users));
  2095. exit ();
  2096. //header('Location: resume_session.php?id_session='.$id_session);
  2097. }
  2098. /**
  2099. * Reads CSV-file.
  2100. * @param string $file Path to the CSV-file
  2101. * @return array All userinformation read from the file
  2102. */
  2103. function parse_csv_data($file) {
  2104. $users = Import :: csv_to_array($file);
  2105. foreach ($users as $index => $user) {
  2106. if (isset ($user['Courses'])) {
  2107. $user['Courses'] = explode('|', trim($user['Courses']));
  2108. }
  2109. $users[$index] = $user;
  2110. }
  2111. return $users;
  2112. }
  2113. /**
  2114. * XML-parser: the handler at the beginning of element.
  2115. */
  2116. function element_start($parser, $data) {
  2117. $data = api_utf8_decode($data);
  2118. global $user;
  2119. global $current_tag;
  2120. switch ($data) {
  2121. case 'Contact' :
  2122. $user = array ();
  2123. break;
  2124. default :
  2125. $current_tag = $data;
  2126. }
  2127. }
  2128. /**
  2129. * XML-parser: the handler at the end of element.
  2130. */
  2131. function element_end($parser, $data) {
  2132. $data = api_utf8_decode($data);
  2133. global $user;
  2134. global $users;
  2135. global $current_value;
  2136. global $purification_option_for_usernames;
  2137. $user[$data] = $current_value;
  2138. switch ($data) {
  2139. case 'Contact' :
  2140. $user['UserName'] = UserManager::purify_username($user['UserName'], $purification_option_for_usernames);
  2141. $users[] = $user;
  2142. break;
  2143. default :
  2144. $user[$data] = $current_value;
  2145. break;
  2146. }
  2147. }
  2148. /**
  2149. * XML-parser: the handler for character data.
  2150. */
  2151. function character_data($parser, $data) {
  2152. $data = trim(api_utf8_decode($data));
  2153. global $current_value;
  2154. $current_value = $data;
  2155. }
  2156. /**
  2157. * Reads XML-file.
  2158. * @param string $file Path to the XML-file
  2159. * @return array All userinformation read from the file
  2160. */
  2161. function parse_xml_data($file) {
  2162. global $current_tag;
  2163. global $current_value;
  2164. global $user;
  2165. global $users;
  2166. $users = array ();
  2167. $parser = xml_parser_create('UTF-8');
  2168. xml_set_element_handler($parser, array('MySpace','element_start'), array('MySpace','element_end'));
  2169. xml_set_character_data_handler($parser, "character_data");
  2170. xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, false);
  2171. xml_parse($parser, api_utf8_encode_xml(file_get_contents($file)));
  2172. xml_parser_free($parser);
  2173. return $users;
  2174. }
  2175. }
  2176. function get_stats($user_id, $course_code, $start_date = null, $end_date = null) {
  2177. // Database table definitions
  2178. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2179. $tbl_main = Database :: get_main_table(TABLE_MAIN_COURSE);
  2180. $course_info = api_get_course_info($course_code);
  2181. if (!empty($course_info)) {
  2182. $strg_sd = "";
  2183. $strg_ed = "";
  2184. if ($start_date != null && $end_date != null){
  2185. $end_date = add_day_to( $end_date );
  2186. $strg_sd = "AND login_course_date BETWEEN '$start_date' AND '$end_date'";
  2187. $strg_ed = "AND logout_course_date BETWEEN '$start_date' AND '$end_date'";
  2188. }
  2189. $sql = 'SELECT SEC_TO_TIME(avg(time_to_sec(timediff(logout_course_date,login_course_date)))) as avrg,
  2190. SEC_TO_TIME(sum(time_to_sec(timediff(logout_course_date,login_course_date)))) as total,
  2191. count(user_id) as times
  2192. FROM ' . $tbl_track_course . '
  2193. WHERE user_id = ' . intval($user_id) . '
  2194. AND course_code = "' . Database::escape_string($course_code) . '" '.$strg_sd.' '.$strg_ed.' '.'
  2195. ORDER BY login_course_date ASC';
  2196. $rs = Database::query($sql);
  2197. $result = array();
  2198. if ($row = Database::fetch_array($rs)) {
  2199. $foo_avg = $row['avrg'];
  2200. $foo_total = $row['total'];
  2201. $foo_times = $row['times'];
  2202. $result = array('avg' => $foo_avg, 'total' => $foo_total, 'times' => $foo_times);
  2203. }
  2204. }
  2205. return $result;
  2206. }
  2207. function add_day_to($end_date) {
  2208. $foo_date = strtotime( $end_date );
  2209. $foo_date = strtotime(" +1 day", $foo_date);
  2210. $foo_date = date("Y-m-d", $foo_date);
  2211. return $foo_date;
  2212. }
  2213. /**
  2214. * Gets the connections to a course as an array of login and logout time
  2215. *
  2216. * @param int $user_id
  2217. * @param string $course_code
  2218. * @author Jorge Frisancho Jibaja
  2219. * @author Julio Montoya <gugli100@gmail.com> fixing the function
  2220. * @version OCT-22- 2010
  2221. * @return array
  2222. */
  2223. function get_connections_to_course_by_date($user_id, $course_code, $start_date, $end_date)
  2224. {
  2225. // Database table definitions
  2226. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  2227. $course_info = api_get_course_info($course_code);
  2228. $user_id = intval($user_id);
  2229. $connections = array();
  2230. if (!empty($course_info)) {
  2231. $end_date = add_day_to($end_date);
  2232. $sql = "SELECT login_course_date, logout_course_date
  2233. FROM $tbl_track_course
  2234. WHERE
  2235. user_id = $user_id AND
  2236. course_code = '$course_code' AND
  2237. login_course_date BETWEEN '$start_date' AND '$end_date' AND
  2238. logout_course_date BETWEEN '$start_date' AND '$end_date'
  2239. ORDER BY login_course_date ASC";
  2240. $rs = Database::query($sql);
  2241. while ($row = Database::fetch_array($rs)) {
  2242. $login_date = $row['login_course_date'];
  2243. $logout_date = $row['logout_course_date'];
  2244. $timestamp_login_date = strtotime($login_date);
  2245. $timestamp_logout_date = strtotime($logout_date);
  2246. $connections[] = array(
  2247. 'login' => $timestamp_login_date,
  2248. 'logout' => $timestamp_logout_date
  2249. );
  2250. }
  2251. }
  2252. return $connections;
  2253. }
  2254. /**
  2255. *
  2256. *
  2257. * @param array
  2258. * @author Jorge Frisancho Jibaja
  2259. * @version OCT-22- 2010
  2260. * @return array
  2261. */
  2262. function convert_to_array($sql_result){
  2263. $result_to_print = '<table>';
  2264. foreach ($sql_result as $key => $data) {
  2265. $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";
  2266. }
  2267. $result_to_print .= '</table>';
  2268. $result_to_print = array("result"=>$result_to_print);
  2269. return $result_to_print;
  2270. }
  2271. /**
  2272. * Converte an array to a table in html
  2273. *
  2274. * @param array $sql_result
  2275. * @author Jorge Frisancho Jibaja
  2276. * @version OCT-22- 2010
  2277. * @return string
  2278. */
  2279. function convert_to_string($sql_result){
  2280. $result_to_print = '<table>';
  2281. if (!empty($sql_result)) {
  2282. foreach ($sql_result as $key => $data) {
  2283. $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";
  2284. }
  2285. }
  2286. $result_to_print .= '</table>';
  2287. return $result_to_print;
  2288. }
  2289. /**
  2290. * This function draw the graphic to be displayed on the user view as an image
  2291. *
  2292. * @param array $sql_result
  2293. * @param string $start_date
  2294. * @param string $end_date
  2295. * @param string $type
  2296. * @author Jorge Frisancho Jibaja
  2297. * @version OCT-22- 2010
  2298. * @return string
  2299. */
  2300. function grapher($sql_result, $start_date, $end_date, $type = "") {
  2301. require_once api_get_path(LIBRARY_PATH).'pchart/pData.class.php';
  2302. require_once api_get_path(LIBRARY_PATH).'pchart/pChart.class.php';
  2303. require_once api_get_path(LIBRARY_PATH).'pchart/pCache.class.php';
  2304. if (empty($start_date)) { $start_date =""; }
  2305. if (empty($end_date)) { $end_date =""; }
  2306. if ($type == ""){ $type = 'day'; }
  2307. $main_year = $main_month_year = $main_day = array();
  2308. // get last 8 days/months
  2309. $last_days = 5;
  2310. $last_months = 3;
  2311. for ($i = $last_days; $i >= 0; $i--) {
  2312. $main_day[date ('d-m-Y', mktime () - $i * 3600 * 24)] = 0;
  2313. }
  2314. for ($i = $last_months; $i >= 0; $i--) {
  2315. $main_month_year[date ('m-Y', mktime () - $i * 30 * 3600 * 24)] = 0;
  2316. }
  2317. $i = 0;
  2318. if (is_array($sql_result) && count($sql_result) > 0) {
  2319. foreach ($sql_result as $key => $data) {
  2320. //creating the main array
  2321. $main_month_year[date('m-Y', $data['login'])] += float_format(($data['logout'] - $data['login']) / 60, 0);
  2322. $main_day[date('d-m-Y', $data['login'])] += float_format(($data['logout'] - $data['login']) / 60, 0);
  2323. if ($i > 500) {
  2324. break;
  2325. }
  2326. $i++;
  2327. }
  2328. switch ($type) {
  2329. case 'day':
  2330. $main_date = $main_day;
  2331. break;
  2332. case 'month':
  2333. $main_date = $main_month_year;
  2334. break;
  2335. case 'year':
  2336. $main_date = $main_year;
  2337. break;
  2338. }
  2339. // the nice graphics :D
  2340. $labels = array_keys($main_date);
  2341. if (count($main_date) == 1) {
  2342. $labels = $labels[0];
  2343. $main_date = $main_date[$labels];
  2344. }
  2345. $data_set = new pData();
  2346. $data_set->AddPoint($main_date, 'Q');
  2347. if (count($main_date)!= 1) {
  2348. $data_set->AddPoint($labels, 'Date');
  2349. }
  2350. $data_set->AddAllSeries();
  2351. $data_set->RemoveSerie('Date');
  2352. $data_set->SetAbsciseLabelSerie('Date');
  2353. $data_set->SetYAxisName(get_lang('Minutes', ''));
  2354. $graph_id = api_get_user_id().'AccessDetails'.api_get_course_id().$start_date.$end_date.$type;
  2355. $data_set->AddAllSeries();
  2356. $cache = new pCache();
  2357. // the graph id
  2358. $data = $data_set->GetData();
  2359. if ($cache->IsInCache($graph_id, $data_set->GetData())) {
  2360. //if (0) {
  2361. //if we already created the img
  2362. // echo 'in cache';
  2363. $img_file = $cache->GetHash($graph_id, $data_set->GetData());
  2364. } else {
  2365. // if the image does not exist in the archive/ folder
  2366. // Initialise the graph
  2367. $test = new pChart(760, 230);
  2368. //which schema of color will be used
  2369. $quant_resources = count($data[0]) - 1;
  2370. // Adding the color schemma
  2371. $test->loadColorPalette(api_get_path(LIBRARY_PATH).'pchart/palette/default.txt');
  2372. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 8);
  2373. $test->setGraphArea(70, 30, 680, 200);
  2374. $test->drawFilledRoundedRectangle(7, 7, 693, 223, 5, 240, 240, 240);
  2375. $test->drawRoundedRectangle(5, 5, 695, 225, 5, 230, 230, 230);
  2376. $test->drawGraphArea(255, 255, 255, TRUE);
  2377. $test->drawScale($data_set->GetData(), $data_set->GetDataDescription(), SCALE_START0, 150, 150, 150, TRUE, 0, 0);
  2378. $test->drawGrid(4, TRUE, 230, 230, 230, 50);
  2379. $test->setLineStyle(2);
  2380. // Draw the 0 line
  2381. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 6);
  2382. $test->drawTreshold(0, 143, 55, 72, TRUE, TRUE);
  2383. if (count($main_date) == 1) {
  2384. //Draw a graph
  2385. echo '<strong>'.$labels.'</strong><br/>';
  2386. $test->drawBarGraph($data_set->GetData(), $data_set->GetDataDescription(), TRUE);
  2387. } else {
  2388. //Draw the line graph
  2389. $test->drawLineGraph($data_set->GetData(), $data_set->GetDataDescription());
  2390. $test->drawPlotGraph($data_set->GetData(), $data_set->GetDataDescription(), 3, 2, 255, 255, 255);
  2391. }
  2392. // Finish the graph
  2393. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 8);
  2394. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 10);
  2395. $test->drawTitle(60, 22, get_lang('AccessDetails'), 50, 50, 50, 585);
  2396. //------------------
  2397. //echo 'not in cache';
  2398. $cache->WriteToCache($graph_id, $data_set->GetData(), $test);
  2399. ob_start();
  2400. $test->Stroke();
  2401. ob_end_clean();
  2402. $img_file = $cache->GetHash($graph_id, $data_set->GetData());
  2403. }
  2404. $foo_img = '<img src="'.api_get_path(WEB_ARCHIVE_PATH).$img_file.'">';
  2405. return $foo_img;
  2406. } else {
  2407. $foo_img = api_convert_encoding('<div id="messages" class="warning-message">'.get_lang('GraphicNotAvailable').'</div>','UTF-8');
  2408. return $foo_img;
  2409. }
  2410. }