myspace.lib.php 84 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063
  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. class MySpace {
  6. /**
  7. * This function serves exporting data in CSV format.
  8. * @param array $header The header labels.
  9. * @param array $data The data array.
  10. * @param string $file_name The name of the file which contains exported data.
  11. * @return string mixed Returns a message (string) if an error occurred.
  12. */
  13. function export_csv($header, $data, $file_name = 'export.csv') {
  14. $archive_path = api_get_path(SYS_ARCHIVE_PATH);
  15. $archive_url = api_get_path(WEB_CODE_PATH).'course_info/download.php?archive=';
  16. if (!$open = fopen($archive_path.$file_name, 'w+')) {
  17. $message = get_lang('noOpen');
  18. } else {
  19. $info = '';
  20. foreach ($header as $value) {
  21. $info .= $value.';';
  22. }
  23. $info .= "\r\n";
  24. foreach ($data as $row) {
  25. foreach ($row as $value) {
  26. $info .= $value.';';
  27. }
  28. $info .= "\r\n";
  29. }
  30. fwrite($open, $info);
  31. fclose($open);
  32. @chmod($file_name, api_get_permissions_for_new_files());
  33. header("Location:".$archive_url.$file_name);
  34. }
  35. return $message;
  36. }
  37. /**
  38. * Gets the connections to a course as an array of login and logout time
  39. *
  40. * @param int User ud
  41. * @param string Course code
  42. * @param int Session id (optional, default = 0)
  43. * @return array Conections
  44. */
  45. static function get_connections_to_course($user_id, $course_code, $session_id = 0) {
  46. // Database table definitions
  47. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  48. // protect data
  49. $user_id = intval($user_id);
  50. $course_code = Database::escape_string($course_code);
  51. $session_id = intval($session_id);
  52. $sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . '
  53. WHERE user_id = '.$user_id.' AND
  54. course_code="'.$course_code.'" AND
  55. session_id = '.$session_id.'
  56. ORDER BY login_course_date ASC';
  57. $rs = Database::query($sql);
  58. $connections = array();
  59. while ($row = Database::fetch_array($rs)) {
  60. $timestamp_login_date = api_strtotime($row['login_course_date'], 'UTC');
  61. $timestamp_logout_date = api_strtotime($row['logout_course_date'], 'UTC');
  62. $connections[] = array('login' => $timestamp_login_date, 'logout' => $timestamp_logout_date);
  63. }
  64. return $connections;
  65. }
  66. static function get_connections_from_course_list($user_id, $course_list, $session_id = 0) {
  67. // Database table definitions
  68. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  69. if (empty($course_list)) {
  70. return false;
  71. }
  72. // protect data
  73. $user_id = intval($user_id);
  74. $course_code = Database::escape_string($course_code);
  75. $session_id = intval($session_id);
  76. $new_course_list = array();;
  77. foreach ($course_list as $course_item) {
  78. $new_course_list[] = '"'.Database::escape_string($course_item['code']).'"';
  79. }
  80. $course_list = implode(', ', $new_course_list);
  81. if (empty($course_list)) {
  82. return false;
  83. }
  84. $sql = 'SELECT login_course_date, logout_course_date, course_code FROM ' . $tbl_track_course . '
  85. WHERE user_id = '.$user_id.' AND
  86. course_code IN ('.$course_list.') AND
  87. session_id = '.$session_id.'
  88. ORDER BY login_course_date ASC';
  89. $rs = Database::query($sql);
  90. $connections = array();
  91. while ($row = Database::fetch_array($rs)) {
  92. $timestamp_login_date = api_strtotime($row['login_course_date'], 'UTC');
  93. $timestamp_logout_date = api_strtotime($row['logout_course_date'], 'UTC');
  94. $connections[] = array('login' => $timestamp_login_date, 'logout' => $timestamp_logout_date,'course_code' => $row['course_code']);
  95. }
  96. return $connections;
  97. }
  98. /**
  99. * TODO: Not used, to b deleted?
  100. * Enter description here...
  101. * @param int $user_id
  102. * @param string $course_code
  103. * @param date $year
  104. * @param date $month
  105. * @param date $day
  106. * @return unknown
  107. */
  108. static function get_connections_to_course_by_time($user_id, $course_code, $year = '', $month = '', $day = '') {
  109. // Database table definitions
  110. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  111. $sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . '
  112. WHERE user_id = ' . intval($user_id) . '
  113. AND course_code="' . Database::escape_string($course_code) . '"
  114. ORDER BY login_course_date DESC';
  115. $rs = Database::query($sql);
  116. $connections = array();
  117. while ($row = Database::fetch_array($rs)) {
  118. $login_date = $row['login_course_date'];
  119. $logout_date = $row['logout_course_date'];
  120. $timestamp_login_date = strtotime($login_date);
  121. $timestamp_logout_date = strtotime($logout_date);
  122. $connections[] = array('login' => $timestamp_login_date, 'logout' => $timestamp_logout_date);
  123. }
  124. return $connections;
  125. }
  126. /**
  127. * Creates a small table in the last column of the table with the user overview
  128. *
  129. * @param integer $user_id the id of the user
  130. * @param array $url_params additonal url parameters
  131. * @param array $row the row information (the other columns)
  132. * @return string html code
  133. */
  134. function course_info_tracking_filter($user_id, $url_params, $row) {
  135. // the table header
  136. $return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  137. /*$return .= ' <tr>';
  138. $return .= ' <th>'.get_lang('Course').'</th>';
  139. $return .= ' <th>'.get_lang('AvgTimeSpentInTheCourse').'</th>';
  140. $return .= ' <th>'.get_lang('AvgStudentsProgress').'</th>';
  141. $return .= ' <th>'.get_lang('AvgCourseScore').'</th>';
  142. $return .= ' <th>'.get_lang('AvgExercisesScore').'</th>';
  143. $return .= ' <th>'.get_lang('AvgMessages').'</th>';
  144. $return .= ' <th>'.get_lang('AvgAssignments').'</th>';
  145. $return .= ' <th>'.get_lang('TotalExercisesScoreObtained').'</th>';
  146. $return .= ' <th>'.get_lang('TotalExercisesScorePossible').'</th>';
  147. $return .= ' <th>'.get_lang('TotalExercisesAnswered').'</th>';
  148. $return .= ' <th>'.get_lang('TotalExercisesScorePercentage').'</th>';
  149. $return .= ' <th>'.get_lang('FirstLogin').'</th>';
  150. $return .= ' <th>'.get_lang('LatestLogin').'</th>';
  151. $return .= ' </tr>';*/
  152. // database table definition
  153. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  154. // getting all the courses of the user
  155. $sql = "SELECT * FROM $tbl_course_user WHERE user_id = '".Database::escape_string($user_id)."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  156. $result = Database::query($sql);
  157. while ($row = Database::fetch_row($result)) {
  158. $return .= '<tr>';
  159. // course code
  160. $return .= ' <td width="157px" >'.cut($row[0], 20, true).'</td>';
  161. // time spent in the course
  162. $return .= ' <td><div>'.api_time_to_hms(Tracking :: get_time_spent_on_the_course($user_id, $row[0])).'</div></td>';
  163. // student progress in course
  164. $return .= ' <td><div>'.round(Tracking :: get_avg_student_progress($user_id, $row[0]), 2).'</div></td>';
  165. // student score
  166. $avg_score = Tracking :: get_avg_student_score($user_id, $row[0]);
  167. if (is_numeric($avg_score)) {
  168. $avg_score = round($avg_score,2);
  169. } else {
  170. $$avg_score = '-';
  171. }
  172. $return .= ' <td><div>'.$avg_score.'</div></td>';
  173. // student tes score
  174. //$return .= ' <td><div style="width:40px">'.round(Tracking :: get_avg_student_exercise_score ($user_id, $row[0]),2).'%</div></td>';
  175. // student messages
  176. $return .= ' <td><div>'.Tracking :: count_student_messages($user_id, $row[0]).'</div></td>';
  177. // student assignments
  178. $return .= ' <td><div>'.Tracking :: count_student_assignments($user_id, $row[0]).'</div></td>';
  179. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  180. $exercises_results = MySpace::exercises_results($user_id, $row[0]);
  181. $return .= ' <td width="105px"><div>'.(is_null($exercises_results['percentage']) ? '' : $exercises_results['score_obtained'].'/'.$exercises_results['score_possible'].' ( '.$exercises_results['percentage'].'% )').'</div></td>';
  182. //$return .= ' <td><div>'.$exercises_results['score_possible'].'</div></td>';
  183. $return .= ' <td><div>'.$exercises_results['questions_answered'].'</div></td>';
  184. //$return .= ' <td><div>'.$exercises_results['percentage'].'% </div></td>';
  185. // first connection
  186. //$return .= ' <td width="60px">'.Tracking :: get_first_connection_date_on_the_course ($user_id, $row[0]).'</td>';
  187. // last connection
  188. $return .= ' <td><div>'.Tracking :: get_last_connection_date_on_the_course ($user_id, $row[0]).'</div></td>';
  189. $return .= '<tr>';
  190. }
  191. $return .= '</table>';
  192. return $return;
  193. }
  194. /**
  195. * Display a sortable table that contains an overview off all the reporting progress of all users and all courses the user is subscribed to
  196. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  197. * @version Dokeos 1.8.6
  198. * @since October 2008
  199. */
  200. function display_tracking_user_overview() {
  201. MySpace::display_user_overview_export_options();
  202. $t_head .= ' <table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  203. //$t_head .= ' <caption>'.get_lang('CourseInformation').'</caption>';
  204. $t_head .= '<tr>';
  205. $t_head .= ' <th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>';
  206. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  207. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  208. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  209. //$t_head .= ' <th><div style="width:40px">'.get_lang('AvgExercisesScore').'</div></th>';
  210. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  211. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  212. $t_head .= ' <th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  213. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePossible').'</div></th>';
  214. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  215. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePercentage').'</div></th>';
  216. //$t_head .= ' <th><div style="width:60px">'.get_lang('FirstLogin').'</div></th>';
  217. $t_head .= ' <th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  218. $t_head .= ' </tr></table>';
  219. $addparams = array('view' => 'admin', 'display' => 'useroverview');
  220. $table = new SortableTable('tracking_user_overview', array('MySpace','get_number_of_users_tracking_overview'), array('MySpace','get_user_data_tracking_overview'), 0);
  221. $table->additional_parameters = $addparams;
  222. $table->set_header(0, get_lang('OfficialCode'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  223. if (api_is_western_name_order()) {
  224. $table->set_header(1, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  225. $table->set_header(2, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  226. } else {
  227. $table->set_header(1, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  228. $table->set_header(2, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  229. }
  230. $table->set_header(3, get_lang('LoginName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  231. $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;'));
  232. $table->set_column_filter(4, array('MySpace','course_info_tracking_filter'));
  233. $table->display();
  234. }
  235. /**
  236. * Displays a form with all the additionally defined user fields of the profile
  237. * and give you the opportunity to include these in the CSV export
  238. *
  239. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  240. * @version Dokeos 1.8.6
  241. * @since November 2008
  242. */
  243. function display_user_overview_export_options() {
  244. // include the user manager and formvalidator library
  245. if ($_GET['export'] == 'options') {
  246. // get all the defined extra fields
  247. $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC', false, 1);
  248. // creating the form with all the defined extra fields
  249. $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']));
  250. if (is_array($extrafields) && count($extrafields) > 0) {
  251. foreach ($extrafields as $key => $extra) {
  252. $form->addElement('checkbox', 'extra_export_field'.$extra[0], '', $extra[3]);
  253. }
  254. $form->addElement('style_submit_button','submit', get_lang('Ok'),'class="save"' );
  255. // setting the default values for the form that contains all the extra fields
  256. if (is_array($_SESSION['additional_export_fields'])) {
  257. foreach ($_SESSION['additional_export_fields'] as $key => $value) {
  258. $defaults['extra_export_field'.$value] = 1;
  259. }
  260. }
  261. $form->setDefaults($defaults);
  262. } else {
  263. $form->addElement('html', Display::display_warning_message(get_lang('ThereAreNotExtrafieldsAvailable')));
  264. }
  265. if ($form->validate()) {
  266. // exporting the form values
  267. $values = $form->exportValues();
  268. // re-initialising the session that contains the additional fields that need to be exported
  269. $_SESSION['additional_export_fields'] = array();
  270. // adding the fields that are checked to the session
  271. $message = '';
  272. foreach ($values as $field_ids => $value) {
  273. if ($value == 1 && strstr($field_ids,'extra_export_field')) {
  274. $_SESSION['additional_export_fields'][] = str_replace('extra_export_field', '', $field_ids);
  275. }
  276. }
  277. // adding the fields that will be also exported to a message string
  278. if (is_array($_SESSION['additional_export_fields'])) {
  279. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  280. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  281. }
  282. }
  283. // Displaying a feedback message
  284. if (!empty($_SESSION['additional_export_fields'])) {
  285. Display::display_confirmation_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', false);
  286. } else {
  287. Display::display_confirmation_message(get_lang('NoAdditionalFieldsWillBeExported'), false);
  288. }
  289. $message = '';
  290. } else {
  291. $form->display();
  292. }
  293. } else {
  294. if (!empty($_SESSION['additional_export_fields'])) {
  295. // get all the defined extra fields
  296. $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  297. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  298. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  299. }
  300. Display::display_normal_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', false);
  301. $message = '';
  302. }
  303. }
  304. }
  305. /**
  306. * Display a sortable table that contains an overview of all the reporting progress of all courses
  307. */
  308. function display_tracking_course_overview() {
  309. //MySpace::display_user_overview_export_options();
  310. $t_head .= ' <table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  311. //$t_head .= ' <caption>'.get_lang('CourseInformation').'</caption>';
  312. $t_head .= '<tr>';
  313. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  314. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  315. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  316. //$t_head .= ' <th><div style="width:40px">'.get_lang('AvgExercisesScore').'</div></th>';
  317. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  318. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  319. $t_head .= ' <th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  320. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePossible').'</div></th>';
  321. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  322. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePercentage').'</div></th>';
  323. //$t_head .= ' <th><div style="width:60px">'.get_lang('FirstLogin').'</div></th>';
  324. $t_head .= ' <th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  325. $t_head .= ' </tr></table>';
  326. $addparams = array('view' => 'admin', 'display' => 'courseoverview');
  327. $table = new SortableTable('tracking_session_overview', array('MySpace','get_total_number_courses'), array('MySpace','get_course_data_tracking_overview'), 1);
  328. $table->additional_parameters = $addparams;
  329. $table->set_header(0, '', false, null, array('style' => 'display: none'));
  330. $table->set_header(1, get_lang('Course'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  331. $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;'));
  332. $table->set_column_filter(2, array('MySpace','course_tracking_filter'));
  333. $table->display();
  334. }
  335. /**
  336. * Get the total number of courses
  337. *
  338. * @return integer Total number of courses
  339. */
  340. public function get_total_number_courses() {
  341. // database table definition
  342. $main_course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  343. return Database::count_rows($main_course_table);
  344. }
  345. /**
  346. * Get data for the courses
  347. *
  348. * @param int Inferior limit
  349. * @param int Number of items to select
  350. * @param string Column to order on
  351. * @param string Order direction
  352. * @return array Results
  353. */
  354. public function get_course_data_tracking_overview($from, $number_of_items, $column, $direction) {
  355. $main_course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  356. $from = intval($from);
  357. $number_of_items = intval($number_of_items);
  358. $sql = "SELECT code AS col0, title AS col1 FROM $main_course_table";
  359. $sql .= " ORDER BY col$column $direction ";
  360. $sql .= " LIMIT $from,$number_of_items";
  361. $result = Database::query($sql);
  362. $return = array ();
  363. while ($course = Database::fetch_row($result)) {
  364. $return[] = $course;
  365. }
  366. return $return;
  367. }
  368. /**
  369. * Fills in course reporting data
  370. *
  371. * @param integer course code
  372. * @param array $url_params additonal url parameters
  373. * @param array $row the row information (the other columns)
  374. * @return string html code
  375. */
  376. function course_tracking_filter($course_code, $url_params, $row) {
  377. $course_code = $row[0];
  378. // the table header
  379. $return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  380. // database table definition
  381. $tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  382. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  383. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  384. // getting all the courses of the user
  385. $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);";
  386. $result = Database::query($sql);
  387. $time_spent = 0;
  388. $progress = 0;
  389. $nb_progress_lp = 0;
  390. $score = 0;
  391. $nb_score_lp = 0;
  392. $nb_messages = 0;
  393. $nb_assignments = 0;
  394. $last_login_date = false;
  395. $total_score_obtained = 0;
  396. $total_score_possible = 0;
  397. $total_questions_answered = 0;
  398. while ($row = Database::fetch_object($result)) {
  399. // get time spent in the course and session
  400. $time_spent += Tracking::get_time_spent_on_the_course($row->user_id, $course_code);
  401. $progress_tmp = Tracking::get_avg_student_progress($row->user_id, $course_code, array(), null, true);
  402. $progress += $progress_tmp[0];
  403. $nb_progress_lp += $progress_tmp[1];
  404. $score_tmp = Tracking :: get_avg_student_score($row->user_id, $course_code, array(), null, true);
  405. if(is_array($score_tmp)) {
  406. $score += $score_tmp[0];
  407. $nb_score_lp += $score_tmp[1];
  408. }
  409. $nb_messages += Tracking::count_student_messages($row->user_id, $course_code);
  410. $nb_assignments += Tracking::count_student_assignments($row->user_id, $course_code);
  411. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row->user_id, $course_code, null, false);
  412. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned
  413. $last_login_date = $last_login_date_tmp;
  414. } else if($last_login_date_tmp != false && $last_login_date != false) { // TODO: Repeated previous condition. To be cleaned.
  415. // Find the max and assign it to first_login_date
  416. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  417. $last_login_date = $last_login_date_tmp;
  418. }
  419. }
  420. $exercise_results_tmp = MySpace::exercises_results($row->user_id, $course_code);
  421. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  422. $total_score_possible += $exercise_results_tmp['score_possible'];
  423. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  424. }
  425. if($nb_progress_lp > 0) {
  426. $avg_progress = round($progress / $nb_progress_lp, 2);
  427. } else {
  428. $avg_progress = 0;
  429. }
  430. if($nb_score_lp > 0) {
  431. $avg_score = round($score / $nb_score_lp, 2);
  432. } else {
  433. $avg_score = '-';
  434. }
  435. if($last_login_date) {
  436. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  437. } else {
  438. $last_login_date = '-';
  439. }
  440. if($total_score_possible > 0) {
  441. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  442. } else {
  443. $total_score_percentage = 0;
  444. }
  445. if($total_score_percentage > 0) {
  446. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  447. } else {
  448. $total_score = '-';
  449. }
  450. $return .= '<tr>';
  451. // time spent in the course
  452. $return .= ' <td style="width:164px;">'.api_time_to_hms($time_spent).'</td>';
  453. // student progress in course
  454. $return .= ' <td>'.$avg_progress.'</td>';
  455. // student score
  456. $return .= ' <td>'.$avg_score.'</td>';
  457. // student messages
  458. $return .= ' <td>'.$nb_messages.'</td>';
  459. // student assignments
  460. $return .= ' <td>'.$nb_assignments.'</td>';
  461. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  462. $return .= '<td width="105px;">'.$total_score.'</td>';
  463. $return .= '<td>'.$total_questions_answered.'</td>';
  464. // last connection
  465. $return .= ' <td>'.$last_login_date.'</td>';
  466. $return .= '</tr>';
  467. $return .= '</table>';
  468. return $return;
  469. }
  470. /**
  471. * This function exports the table that we see in display_tracking_course_overview()
  472. *
  473. */
  474. function export_tracking_course_overview() {
  475. // database table definition
  476. $tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  477. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  478. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  479. // the values of the sortable table
  480. if ($_GET['tracking_course_overview_page_nr']) {
  481. $from = $_GET['tracking_course_overview_page_nr'];
  482. } else {
  483. $from = 0;
  484. }
  485. if ($_GET['tracking_course_overview_column']) {
  486. $orderby = $_GET['tracking_course_overview_column'];
  487. } else {
  488. $orderby = 0;
  489. }
  490. if ($_GET['tracking_course_overview_direction']) {
  491. $direction = $_GET['tracking_course_overview_direction'];
  492. } else {
  493. $direction = 'ASC';
  494. }
  495. $course_data = MySpace::get_course_data_tracking_overview($from, 1000, $orderby, $direction);
  496. $csv_content = array();
  497. // the first line of the csv file with the column headers
  498. $csv_row = array();
  499. $csv_row[] = get_lang('Course', '');
  500. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  501. $csv_row[] = get_lang('AvgStudentsProgress', '');
  502. $csv_row[] = get_lang('AvgCourseScore', '');
  503. $csv_row[] = get_lang('TotalNumberOfMessages', '');
  504. $csv_row[] = get_lang('TotalNumberOfAssignments', '');
  505. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  506. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  507. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  508. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  509. $csv_row[] = get_lang('LatestLogin', '');
  510. $csv_content[] = $csv_row;
  511. // the other lines (the data)
  512. foreach ($course_data as $key => $course) {
  513. $course_code = $course[0];
  514. $course_title = $course[1];
  515. $csv_row = array();
  516. $csv_row[] = $course_title;
  517. // getting all the courses of the session
  518. $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);";
  519. $result = Database::query($sql);
  520. $time_spent = 0;
  521. $progress = 0;
  522. $nb_progress_lp = 0;
  523. $score = 0;
  524. $nb_score_lp = 0;
  525. $nb_messages = 0;
  526. $nb_assignments = 0;
  527. $last_login_date = false;
  528. $total_score_obtained = 0;
  529. $total_score_possible = 0;
  530. $total_questions_answered = 0;
  531. while ($row = Database::fetch_object($result)) {
  532. // get time spent in the course and session
  533. $time_spent += Tracking::get_time_spent_on_the_course($row->user_id, $course_code);
  534. $progress_tmp = Tracking::get_avg_student_progress($row->user_id, $course_code, array(), null, true);
  535. $progress += $progress_tmp[0];
  536. $nb_progress_lp += $progress_tmp[1];
  537. $score_tmp = Tracking :: get_avg_student_score($row->user_id, $course_code, array(), null, true);
  538. if(is_array($score_tmp)) {
  539. $score += $score_tmp[0];
  540. $nb_score_lp += $score_tmp[1];
  541. }
  542. $nb_messages += Tracking::count_student_messages($row->user_id, $course_code);
  543. $nb_assignments += Tracking::count_student_assignments($row->user_id, $course_code);
  544. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row->user_id, $course_code, null, false);
  545. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned.
  546. $last_login_date = $last_login_date_tmp;
  547. } else if($last_login_date_tmp != false && $last_login_date == false) { // TODO: Repeated previous condition. To be cleaned.
  548. // Find the max and assign it to first_login_date
  549. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  550. $last_login_date = $last_login_date_tmp;
  551. }
  552. }
  553. $exercise_results_tmp = MySpace::exercises_results($row->user_id, $course_code);
  554. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  555. $total_score_possible += $exercise_results_tmp['score_possible'];
  556. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  557. }
  558. if($nb_progress_lp > 0) {
  559. $avg_progress = round($progress / $nb_progress_lp, 2);
  560. } else {
  561. $avg_progress = 0;
  562. }
  563. if($nb_score_lp > 0) {
  564. $avg_score = round($score / $nb_score_lp, 2);
  565. } else {
  566. $avg_score = '-';
  567. }
  568. if($last_login_date) {
  569. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  570. } else {
  571. $last_login_date = '-';
  572. }
  573. if($total_score_possible > 0) {
  574. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  575. } else {
  576. $total_score_percentage = 0;
  577. }
  578. // time spent in the course
  579. $csv_row[] = api_time_to_hms($time_spent);
  580. // student progress in course
  581. $csv_row[] = $avg_progress;
  582. // student score
  583. $csv_row[] = $avg_score;
  584. // student messages
  585. $csv_row[] = $nb_messages;
  586. // student assignments
  587. $csv_row[] = $nb_assignments;
  588. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  589. $csv_row[] = $total_score_obtained;
  590. $csv_row[] = $total_score_possible;
  591. $csv_row[] = $total_questions_answered;
  592. $csv_row[] = $total_score_percentage;
  593. // last connection
  594. $csv_row[] = $last_login_date;
  595. $csv_content[] = $csv_row;
  596. }
  597. Export :: export_table_csv($csv_content, 'reporting_course_overview');
  598. exit;
  599. }
  600. /**
  601. * Display a sortable table that contains an overview of all the reporting progress of all sessions and all courses the user is subscribed to
  602. * @author Guillaume Viguier <guillaume@viguierjust.com>
  603. */
  604. function display_tracking_session_overview() {
  605. //MySpace::display_user_overview_export_options();
  606. $t_head .= ' <table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  607. //$t_head .= ' <caption>'.get_lang('CourseInformation').'</caption>';
  608. $t_head .= '<tr>';
  609. $t_head .= ' <th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>';
  610. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  611. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  612. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  613. //$t_head .= ' <th><div style="width:40px">'.get_lang('AvgExercisesScore').'</div></th>';
  614. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  615. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  616. $t_head .= ' <th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  617. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePossible').'</div></th>';
  618. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  619. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePercentage').'</div></th>';
  620. //$t_head .= ' <th><div style="width:60px">'.get_lang('FirstLogin').'</div></th>';
  621. $t_head .= ' <th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  622. $t_head .= ' </tr></table>';
  623. $addparams = array('view' => 'admin', 'display' => 'sessionoverview');
  624. $table = new SortableTable('tracking_session_overview', array('MySpace','get_total_number_sessions'), array('MySpace','get_session_data_tracking_overview'), 1);
  625. $table->additional_parameters = $addparams;
  626. $table->set_header(0, '', false, null, array('style' => 'display: none'));
  627. $table->set_header(1, get_lang('Session'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  628. $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;'));
  629. $table->set_column_filter(2, array('MySpace','session_tracking_filter'));
  630. $table->display();
  631. }
  632. /**
  633. * Get the total number of sessions
  634. *
  635. * @return integer Total number of sessions
  636. */
  637. public function get_total_number_sessions() {
  638. // database table definition
  639. $main_session_table = Database :: get_main_table(TABLE_MAIN_SESSION);
  640. return Database::count_rows($main_session_table);
  641. }
  642. /**
  643. * Get data for the sessions
  644. *
  645. * @param int Inferior limit
  646. * @param int Number of items to select
  647. * @param string Column to order on
  648. * @param string Order direction
  649. * @return array Results
  650. */
  651. public function get_session_data_tracking_overview($from, $number_of_items, $column, $direction) {
  652. //global $_configuration;
  653. // database table definition
  654. //$access_url_id = api_get_current_access_url_id();
  655. //$tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  656. $main_session_table = Database :: get_main_table(TABLE_MAIN_SESSION);
  657. /*if ($_configuration['multiple_access_urls']) {
  658. $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'";
  659. }
  660. global $export_csv;
  661. if ($export_csv) {
  662. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  663. } else {
  664. $is_western_name_order = api_is_western_name_order();
  665. }*/
  666. $sql = "SELECT id AS col0, name AS col1 FROM $main_session_table";
  667. $sql .= " ORDER BY col$column $direction ";
  668. $sql .= " LIMIT $from,$number_of_items";
  669. $result = Database::query($sql);
  670. $return = array ();
  671. while ($session = Database::fetch_row($result)) {
  672. $return[] = $session;
  673. }
  674. return $return;
  675. }
  676. /**
  677. * Fills in session reporting data
  678. *
  679. * @param integer $user_id the id of the user
  680. * @param array $url_params additonal url parameters
  681. * @param array $row the row information (the other columns)
  682. * @return string html code
  683. */
  684. function session_tracking_filter($session_id, $url_params, $row) {
  685. $session_id = $row[0];
  686. // the table header
  687. $return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  688. /*$return .= ' <tr>';
  689. $return .= ' <th>'.get_lang('Course').'</th>';
  690. $return .= ' <th>'.get_lang('AvgTimeSpentInTheCourse').'</th>';
  691. $return .= ' <th>'.get_lang('AvgStudentsProgress').'</th>';
  692. $return .= ' <th>'.get_lang('AvgCourseScore').'</th>';
  693. $return .= ' <th>'.get_lang('AvgExercisesScore').'</th>';
  694. $return .= ' <th>'.get_lang('AvgMessages').'</th>';
  695. $return .= ' <th>'.get_lang('AvgAssignments').'</th>';
  696. $return .= ' <th>'.get_lang('TotalExercisesScoreObtained').'</th>';
  697. $return .= ' <th>'.get_lang('TotalExercisesScorePossible').'</th>';
  698. $return .= ' <th>'.get_lang('TotalExercisesAnswered').'</th>';
  699. $return .= ' <th>'.get_lang('TotalExercisesScorePercentage').'</th>';
  700. $return .= ' <th>'.get_lang('FirstLogin').'</th>';
  701. $return .= ' <th>'.get_lang('LatestLogin').'</th>';
  702. $return .= ' </tr>';*/
  703. // database table definition
  704. $tbl_session_rel_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  705. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  706. $tbl_session_rel_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  707. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  708. // getting all the courses of the user
  709. $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."';";
  710. $result = Database::query($sql);
  711. while ($row = Database::fetch_object($result)) {
  712. $return .= '<tr>';
  713. // course code
  714. $return .= ' <td width="157px" >'.$row->title.'</td>';
  715. // get the users in the course
  716. $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."';";
  717. $result_users = Database::query($sql);
  718. $time_spent = 0;
  719. $progress = 0;
  720. $nb_progress_lp = 0;
  721. $score = 0;
  722. $nb_score_lp = 0;
  723. $nb_messages = 0;
  724. $nb_assignments = 0;
  725. $last_login_date = false;
  726. $total_score_obtained = 0;
  727. $total_score_possible = 0;
  728. $total_questions_answered = 0;
  729. while($row_user = Database::fetch_object($result_users)) {
  730. // get time spent in the course and session
  731. $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $row->code, $session_id);
  732. $progress_tmp = Tracking::get_avg_student_progress($row_user->user_id, $row->code, array(), $session_id, true);
  733. $progress += $progress_tmp[0];
  734. $nb_progress_lp += $progress_tmp[1];
  735. $score_tmp = Tracking :: get_avg_student_score($row_user->user_id, $row->code, array(), $session_id, true);
  736. if(is_array($score_tmp)) {
  737. $score += $score_tmp[0];
  738. $nb_score_lp += $score_tmp[1];
  739. }
  740. $nb_messages += Tracking::count_student_messages($row_user->user_id, $row->code, $session_id);
  741. $nb_assignments += Tracking::count_student_assignments($row_user->user_id, $row->code, $session_id);
  742. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row_user->user_id, $row->code, $session_id, false);
  743. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned.
  744. $last_login_date = $last_login_date_tmp;
  745. } else if($last_login_date_tmp != false && $last_login_date != false) { // TODO: Repeated previous condition! To be cleaned.
  746. // Find the max and assign it to first_login_date
  747. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  748. $last_login_date = $last_login_date_tmp;
  749. }
  750. }
  751. $exercise_results_tmp = MySpace::exercises_results($row_user->user_id, $row->code, $session_id);
  752. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  753. $total_score_possible += $exercise_results_tmp['score_possible'];
  754. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  755. }
  756. if($nb_progress_lp > 0) {
  757. $avg_progress = round($progress / $nb_progress_lp, 2);
  758. } else {
  759. $avg_progress = 0;
  760. }
  761. if($nb_score_lp > 0) {
  762. $avg_score = round($score / $nb_score_lp, 2);
  763. } else {
  764. $avg_score = '-';
  765. }
  766. if($last_login_date) {
  767. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  768. } else {
  769. $last_login_date = '-';
  770. }
  771. if($total_score_possible > 0) {
  772. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  773. } else {
  774. $total_score_percentage = 0;
  775. }
  776. if($total_score_percentage > 0) {
  777. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  778. } else {
  779. $total_score = '-';
  780. }
  781. // time spent in the course
  782. $return .= ' <td><div>'.api_time_to_hms($time_spent).'</div></td>';
  783. // student progress in course
  784. $return .= ' <td><div>'.$avg_progress.'</div></td>';
  785. // student score
  786. $return .= ' <td><div>'.$avg_score.'</div></td>';
  787. // student messages
  788. $return .= ' <td><div>'.$nb_messages.'</div></td>';
  789. // student assignments
  790. $return .= ' <td><div>'.$nb_assignments.'</div></td>';
  791. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  792. $return .= '<td width="105px;">'.$total_score.'</td>';
  793. $return .= '<td>'.$total_questions_answered.'</td>';
  794. // last connection
  795. $return .= ' <td><div>'.$last_login_date.'</div></td>';
  796. $return .= '<tr>';
  797. }
  798. $return .= '</table>';
  799. return $return;
  800. }
  801. /**
  802. * This function exports the table that we see in display_tracking_session_overview()
  803. *
  804. */
  805. function export_tracking_session_overview() {
  806. // database table definition
  807. $tbl_session_rel_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  808. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  809. $tbl_session_rel_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  810. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  811. // the values of the sortable table
  812. if ($_GET['tracking_session_overview_page_nr']) {
  813. $from = $_GET['tracking_session_overview_page_nr'];
  814. } else {
  815. $from = 0;
  816. }
  817. if ($_GET['tracking_session_overview_column']) {
  818. $orderby = $_GET['tracking_session_overview_column'];
  819. } else {
  820. $orderby = 0;
  821. }
  822. if ($_GET['tracking_session_overview_direction']) {
  823. $direction = $_GET['tracking_session_overview_direction'];
  824. } else {
  825. $direction = 'ASC';
  826. }
  827. $session_data = MySpace::get_session_data_tracking_overview($from, 1000, $orderby, $direction);
  828. $csv_content = array();
  829. // the first line of the csv file with the column headers
  830. $csv_row = array();
  831. $csv_row[] = get_lang('Session');
  832. $csv_row[] = get_lang('Course', '');
  833. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  834. $csv_row[] = get_lang('AvgStudentsProgress', '');
  835. $csv_row[] = get_lang('AvgCourseScore', '');
  836. $csv_row[] = get_lang('TotalNumberOfMessages', '');
  837. $csv_row[] = get_lang('TotalNumberOfAssignments', '');
  838. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  839. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  840. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  841. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  842. $csv_row[] = get_lang('LatestLogin', '');
  843. $csv_content[] = $csv_row;
  844. // the other lines (the data)
  845. foreach ($session_data as $key => $session) {
  846. $session_id = $session[0];
  847. $session_title = $session[1];
  848. // getting all the courses of the session
  849. $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."';";
  850. $result = Database::query($sql);
  851. while ($row = Database::fetch_object($result)) {
  852. $csv_row = array();
  853. $csv_row[] = $session_title;
  854. $csv_row[] = $row->title;
  855. // get the users in the course
  856. $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."';";
  857. $result_users = Database::query($sql);
  858. $time_spent = 0;
  859. $progress = 0;
  860. $nb_progress_lp = 0;
  861. $score = 0;
  862. $nb_score_lp = 0;
  863. $nb_messages = 0;
  864. $nb_assignments = 0;
  865. $last_login_date = false;
  866. $total_score_obtained = 0;
  867. $total_score_possible = 0;
  868. $total_questions_answered = 0;
  869. while($row_user = Database::fetch_object($result_users)) {
  870. // get time spent in the course and session
  871. $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $row->code, $session_id);
  872. $progress_tmp = Tracking::get_avg_student_progress($row_user->user_id, $row->code, array(), $session_id, true);
  873. $progress += $progress_tmp[0];
  874. $nb_progress_lp += $progress_tmp[1];
  875. $score_tmp = Tracking :: get_avg_student_score($row_user->user_id, $row->code, array(), $session_id, true);
  876. if(is_array($score_tmp)) {
  877. $score += $score_tmp[0];
  878. $nb_score_lp += $score_tmp[1];
  879. }
  880. $nb_messages += Tracking::count_student_messages($row_user->user_id, $row->code, $session_id);
  881. $nb_assignments += Tracking::count_student_assignments($row_user->user_id, $row->code, $session_id);
  882. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row_user->user_id, $row->code, $session_id, false);
  883. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned.
  884. $last_login_date = $last_login_date_tmp;
  885. } else if($last_login_date_tmp != false && $last_login_date == false) { // TODO: Repeated previous condition. To be cleaned.
  886. // Find the max and assign it to first_login_date
  887. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  888. $last_login_date = $last_login_date_tmp;
  889. }
  890. }
  891. $exercise_results_tmp = MySpace::exercises_results($row_user->user_id, $row->code, $session_id);
  892. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  893. $total_score_possible += $exercise_results_tmp['score_possible'];
  894. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  895. }
  896. if($nb_progress_lp > 0) {
  897. $avg_progress = round($progress / $nb_progress_lp, 2);
  898. } else {
  899. $avg_progress = 0;
  900. }
  901. if($nb_score_lp > 0) {
  902. $avg_score = round($score / $nb_score_lp, 2);
  903. } else {
  904. $avg_score = '-';
  905. }
  906. if($last_login_date) {
  907. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  908. } else {
  909. $last_login_date = '-';
  910. }
  911. if($total_score_possible > 0) {
  912. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  913. } else {
  914. $total_score_percentage = 0;
  915. }
  916. if($total_score_percentage > 0) {
  917. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  918. } else {
  919. $total_score = '-';
  920. }
  921. // time spent in the course
  922. $csv_row[] = api_time_to_hms($time_spent);
  923. // student progress in course
  924. $csv_row[] = $avg_progress;
  925. // student score
  926. $csv_row[] = $avg_score;
  927. // student messages
  928. $csv_row[] = $nb_messages;
  929. // student assignments
  930. $csv_row[] = $nb_assignments;
  931. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  932. $csv_row[] = $total_score_obtained;
  933. $csv_row[] = $total_score_possible;
  934. $csv_row[] = $total_questions_answered;
  935. $csv_row[] = $total_score_percentage;
  936. // last connection
  937. $csv_row[] = $last_login_date;
  938. $csv_content[] = $csv_row;
  939. }
  940. }
  941. Export :: export_table_csv($csv_content, 'reporting_session_overview');
  942. exit;
  943. }
  944. /**
  945. * Get general information about the exercise performance of the user
  946. * the total obtained score (all the score on all the questions)
  947. * the maximum score that could be obtained
  948. * the number of questions answered
  949. * the success percentage
  950. * @param integer $user_id the id of the user
  951. * @param string $course_code the course code
  952. * @return array
  953. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  954. * @version Dokeos 1.8.6
  955. * @since November 2008
  956. */
  957. function exercises_results($user_id, $course_code, $session_id = false) {
  958. $questions_answered = 0;
  959. $sql = 'SELECT exe_result , exe_weighting
  960. FROM '.Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES)."
  961. WHERE exe_cours_id = '".Database::escape_string($course_code)."'
  962. AND exe_user_id = '".Database::escape_string($user_id)."'";
  963. if($session_id !== false) {
  964. $sql .= " AND session_id = '".$session_id."' ";
  965. }
  966. $result = Database::query($sql);
  967. $score_obtained = 0;
  968. $score_possible = 0;
  969. $questions_answered = 0;
  970. while ($row = Database::fetch_array($result)) {
  971. $score_obtained += $row['exe_result'];
  972. $score_possible += $row['exe_weighting'];
  973. $questions_answered ++;
  974. }
  975. if ($score_possible != 0) {
  976. $percentage = round(($score_obtained / $score_possible * 100), 2);
  977. } else {
  978. $percentage = null;
  979. }
  980. return array('score_obtained' => $score_obtained, 'score_possible' => $score_possible, 'questions_answered' => $questions_answered, 'percentage' => $percentage);
  981. }
  982. /**
  983. * This function exports the table that we see in display_tracking_user_overview()
  984. *
  985. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  986. * @version Dokeos 1.8.6
  987. * @since October 2008
  988. */
  989. function export_tracking_user_overview() {
  990. // database table definitions
  991. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  992. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  993. $sort_by_first_name = api_sort_by_first_name();
  994. // the values of the sortable table
  995. if ($_GET['tracking_user_overview_page_nr']) {
  996. $from = $_GET['tracking_user_overview_page_nr'];
  997. } else {
  998. $from = 0;
  999. }
  1000. if ($_GET['tracking_user_overview_column']) {
  1001. $orderby = $_GET['tracking_user_overview_column'];
  1002. } else {
  1003. $orderby = 0;
  1004. }
  1005. if ($is_western_name_order != api_is_western_name_order() && ($orderby == 1 || $orderby == 2)) {
  1006. // Swapping the sorting column if name order for export is different than the common name order.
  1007. $orderby = 3 - $orderby;
  1008. }
  1009. if ($_GET['tracking_user_overview_direction']) {
  1010. $direction = $_GET['tracking_user_overview_direction'];
  1011. } else {
  1012. $direction = 'ASC';
  1013. }
  1014. $user_data = MySpace::get_user_data_tracking_overview($from, 1000, $orderby, $direction);
  1015. // the first line of the csv file with the column headers
  1016. $csv_row = array();
  1017. $csv_row[] = get_lang('OfficialCode');
  1018. if ($is_western_name_order) {
  1019. $csv_row[] = get_lang('FirstName', '');
  1020. $csv_row[] = get_lang('LastName', '');
  1021. } else {
  1022. $csv_row[] = get_lang('LastName', '');
  1023. $csv_row[] = get_lang('FirstName', '');
  1024. }
  1025. $csv_row[] = get_lang('LoginName');
  1026. $csv_row[] = get_lang('CourseCode');
  1027. // the additional user defined fields (only those that were selected to be exported)
  1028. $fields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  1029. if (is_array($_SESSION['additional_export_fields'])) {
  1030. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  1031. $csv_row[] = $fields[$extra_field_export][3];
  1032. $field_names_to_be_exported[] = 'extra_'.$fields[$extra_field_export][1];
  1033. }
  1034. }
  1035. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  1036. $csv_row[] = get_lang('AvgStudentsProgress', '');
  1037. $csv_row[] = get_lang('AvgCourseScore', '');
  1038. $csv_row[] = get_lang('AvgExercisesScore', '');
  1039. $csv_row[] = get_lang('AvgMessages', '');
  1040. $csv_row[] = get_lang('AvgAssignments', '');
  1041. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  1042. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  1043. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  1044. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  1045. $csv_row[] = get_lang('FirstLogin', '');
  1046. $csv_row[] = get_lang('LatestLogin', '');
  1047. $csv_content[] = $csv_row;
  1048. // the other lines (the data)
  1049. foreach ($user_data as $key => $user) {
  1050. // getting all the courses of the user
  1051. $sql = "SELECT * FROM $tbl_course_user WHERE user_id = '".Database::escape_string($user[4])."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  1052. $result = Database::query($sql);
  1053. while ($row = Database::fetch_row($result)) {
  1054. $csv_row = array();
  1055. // user official code
  1056. $csv_row[] = $user[0];
  1057. // user first|last name
  1058. $csv_row[] = $user[1];
  1059. // user last|first name
  1060. $csv_row[] = $user[2];
  1061. // user login name
  1062. $csv_row[] = $user[3];
  1063. // course code
  1064. $csv_row[] = $row[0];
  1065. // the additional defined user fields
  1066. $extra_fields = MySpace::get_user_overview_export_extra_fields($user[4]);
  1067. if (is_array($field_names_to_be_exported)) {
  1068. foreach ($field_names_to_be_exported as $key => $extra_field_export) {
  1069. $csv_row[] = $extra_fields[$extra_field_export];
  1070. }
  1071. }
  1072. // time spent in the course
  1073. $csv_row[] = api_time_to_hms(Tracking :: get_time_spent_on_the_course ($user[4], $row[0]));
  1074. // student progress in course
  1075. $csv_row[] = round(Tracking :: get_avg_student_progress ($user[4], $row[0]), 2);
  1076. // student score
  1077. $csv_row[] = round(Tracking :: get_avg_student_score ($user[4], $row[0]), 2);
  1078. // student tes score
  1079. $csv_row[] = round(Tracking :: get_avg_student_exercise_score ($user[4], $row[0]), 2);
  1080. // student messages
  1081. $csv_row[] = Tracking :: count_student_messages ($user[4], $row[0]);
  1082. // student assignments
  1083. $csv_row[] = Tracking :: count_student_assignments ($user[4], $row[0]);
  1084. // student exercises results
  1085. $exercises_results = MySpace::exercises_results($user[4], $row[0]);
  1086. $csv_row[] = $exercises_results['score_obtained'];
  1087. $csv_row[] = $exercises_results['score_possible'];
  1088. $csv_row[] = $exercises_results['questions_answered'];
  1089. $csv_row[] = $exercises_results['percentage'];
  1090. // first connection
  1091. $csv_row[] = Tracking :: get_first_connection_date_on_the_course ($user[4], $row[0]);
  1092. // last connection
  1093. $csv_row[] = strip_tags(Tracking :: get_last_connection_date_on_the_course ($user[4], $row[0]));
  1094. $csv_content[] = $csv_row;
  1095. }
  1096. }
  1097. Export :: export_table_csv($csv_content, 'reporting_user_overview');
  1098. exit;
  1099. }
  1100. /**
  1101. * Get data for courses list in sortable with pagination
  1102. * @return array
  1103. */
  1104. static function get_course_data($from, $number_of_items, $column, $direction) {
  1105. global $courses, $csv_content, $charset, $session_id;
  1106. // definition database tables
  1107. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  1108. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1109. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1110. $course_data = array();
  1111. $courses_code = array_keys($courses);
  1112. foreach ($courses_code as &$code) {
  1113. $code = "'$code'";
  1114. }
  1115. // get all courses with limit
  1116. $sql = "SELECT course.code as col1, course.title as col2
  1117. FROM $tbl_course course
  1118. WHERE course.code IN (".implode(',',$courses_code).")";
  1119. if (!in_array($direction, array('ASC','DESC'))) $direction = 'ASC';
  1120. $column = intval($column);
  1121. $from = intval($from);
  1122. $number_of_items = intval($number_of_items);
  1123. $sql .= " ORDER BY col$column $direction ";
  1124. $sql .= " LIMIT $from,$number_of_items";
  1125. $res = Database::query($sql);
  1126. while ($row_course = Database::fetch_row($res)) {
  1127. $course_code = $row_course[0];
  1128. $course_info = api_get_course_info($course_code);
  1129. $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;
  1130. // students directly subscribed to the course
  1131. if (empty($session_id)) {
  1132. $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'";
  1133. } else {
  1134. $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";
  1135. }
  1136. $rs = Database::query($sql);
  1137. $users = array();
  1138. while ($row = Database::fetch_array($rs)) { $users[] = $row['user_id']; }
  1139. if (count($users) > 0) {
  1140. $nb_students_in_course = count($users);
  1141. $avg_assignments_in_course = Tracking::count_student_assignments($users, $course_code, $session_id);
  1142. $avg_messages_in_course = Tracking::count_student_messages($users, $course_code, $session_id);
  1143. $avg_progress_in_course = Tracking::get_avg_student_progress($users, $course_code, array(), $session_id);
  1144. $avg_score_in_course = Tracking::get_avg_student_score($users, $course_code, array(), $session_id);
  1145. $avg_score_in_exercise = Tracking::get_avg_student_exercise_score($users, $course_code, 0, $session_id);
  1146. $avg_time_spent_in_course = Tracking::get_time_spent_on_the_course($users, $course_code, $session_id);
  1147. $avg_progress_in_course = round($avg_progress_in_course / $nb_students_in_course, 2);
  1148. if (is_numeric($avg_score_in_course)) {
  1149. $avg_score_in_course = round($avg_score_in_course / $nb_students_in_course, 2);
  1150. }
  1151. $avg_time_spent_in_course = api_time_to_hms($avg_time_spent_in_course / $nb_students_in_course);
  1152. } else {
  1153. $avg_time_spent_in_course = null;
  1154. $avg_progress_in_course = null;
  1155. $avg_score_in_course = null;
  1156. $avg_score_in_exercise = null;
  1157. $avg_messages_in_course = null;
  1158. $avg_assignments_in_course = null;
  1159. }
  1160. $table_row = array();
  1161. $table_row[] = $row_course[1];
  1162. $table_row[] = $nb_students_in_course;
  1163. $table_row[] = $avg_time_spent_in_course;
  1164. $table_row[] = is_null($avg_progress_in_course) ? '' : $avg_progress_in_course.'%';
  1165. $table_row[] = is_null($avg_score_in_course) ? '' : $avg_score_in_course.'%';
  1166. $table_row[] = is_null($avg_score_in_exercise) ? '' : $avg_score_in_exercise.'%';
  1167. $table_row[] = $avg_messages_in_course;
  1168. $table_row[] = $avg_assignments_in_course;
  1169. //set the "from" value to know if I access the Reporting by the chamilo tab or the course link
  1170. $table_row[] = '<center><a href="../tracking/courseLog.php?cidReq='.$course_code.'&studentlist=true&from=myspace&id_session='.$session_id.'">
  1171. <img src="'.api_get_path(WEB_IMG_PATH).'2rightarrow.gif" border="0" /></a>
  1172. </center>';
  1173. $csv_content[] = array(
  1174. api_html_entity_decode($row_course[1], ENT_QUOTES, $charset),
  1175. $nb_students_in_course,
  1176. $avg_time_spent_in_course,
  1177. is_null($avg_progress_in_course) ? null : $avg_progress_in_course.'%',
  1178. is_null($avg_score_in_course) ? null : is_numeric($avg_score_in_course) ? $avg_score_in_course.'%' : $avg_score_in_course ,
  1179. is_null($avg_score_in_exercise) ? null : $avg_score_in_exercise.'%',
  1180. $avg_messages_in_course,
  1181. $avg_assignments_in_course,
  1182. );
  1183. $course_data[] = $table_row;
  1184. }
  1185. return $course_data;
  1186. }
  1187. /**
  1188. * get the numer of users of the platform
  1189. *
  1190. * @return integer
  1191. *
  1192. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1193. * @version Dokeos 1.8.6
  1194. * @since October 2008
  1195. */
  1196. function get_number_of_users_tracking_overview() {
  1197. // database table definition
  1198. $main_user_table = Database :: get_main_table(TABLE_MAIN_USER);
  1199. return Database::count_rows($main_user_table);
  1200. }
  1201. /**
  1202. * get all the data for the sortable table of the reporting progress of all users and all the courses the user is subscribed to.
  1203. *
  1204. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1205. * @version Dokeos 1.8.6
  1206. * @since October 2008
  1207. */
  1208. function get_user_data_tracking_overview($from, $number_of_items, $column, $direction) {
  1209. global $_configuration;
  1210. // database table definition
  1211. $access_url_id = api_get_current_access_url_id();
  1212. $tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  1213. $main_user_table = Database::get_main_table(TABLE_MAIN_USER);
  1214. if ($_configuration['multiple_access_urls']) {
  1215. $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'";
  1216. }
  1217. global $export_csv;
  1218. if ($export_csv) {
  1219. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  1220. } else {
  1221. $is_western_name_order = api_is_western_name_order();
  1222. }
  1223. $sql = "SELECT
  1224. official_code AS col0,
  1225. ".($is_western_name_order ? "
  1226. firstname AS col1,
  1227. lastname AS col2,
  1228. " : "
  1229. lastname AS col1,
  1230. firstname AS col2,
  1231. ").
  1232. "username AS col3,
  1233. user.user_id AS col4
  1234. FROM
  1235. $main_user_table as user $condition_multi_url
  1236. ";
  1237. $sql .= " ORDER BY col$column $direction ";
  1238. $sql .= " LIMIT $from,$number_of_items";
  1239. $result = Database::query($sql);
  1240. $return = array ();
  1241. while ($user = Database::fetch_row($result)) {
  1242. $return[] = $user;
  1243. }
  1244. return $return;
  1245. }
  1246. /**
  1247. * Get all information that the user with user_id = $user_data has
  1248. * entered in the additionally defined profile fields
  1249. * @param integer $user_id the id of the user
  1250. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1251. * @version Dokeos 1.8.6
  1252. * @since November 2008
  1253. */
  1254. function get_user_overview_export_extra_fields($user_id) {
  1255. // include the user manager
  1256. $extra_data = UserManager::get_extra_user_data($user_id, true);
  1257. return $extra_data;
  1258. }
  1259. /**
  1260. * Checks if a username exist in the DB otherwise it create a "double"
  1261. * i.e. if we look into for jmontoya but the user's name already exist we create the user jmontoya2
  1262. * the return array will be array(username=>'jmontoya', sufix='2')
  1263. * @param string firstname
  1264. * @param string lastname
  1265. * @param string username
  1266. * @return array with the username, the sufix
  1267. * @author Julio Montoya Armas
  1268. */
  1269. function make_username($firstname, $lastname, $username, $language = null, $encoding = null) {
  1270. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  1271. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1272. // if username exist
  1273. if (!UserManager::is_username_available($username) || empty($username)) {
  1274. $i = 0;
  1275. while (1) {
  1276. if ($i == 0) {
  1277. $sufix = '';
  1278. } else {
  1279. $sufix = $i;
  1280. }
  1281. $desired_username = UserManager::create_username($firstname, $lastname, $language, $encoding);
  1282. if (UserManager::is_username_available($desired_username.$sufix)) {
  1283. break;
  1284. } else {
  1285. $i++;
  1286. }
  1287. }
  1288. $username_array = array('username' => $desired_username , 'sufix' => $sufix);
  1289. return $username_array;
  1290. } else {
  1291. $username_array = array('username' => $username, 'sufix' => '');
  1292. return $username_array;
  1293. }
  1294. }
  1295. /**
  1296. * Checks if there are repeted users in a given array
  1297. * @param array $usernames list of the usernames in the uploaded file
  1298. * @param array $user_array['username'] and $user_array['sufix'] where sufix is the number part in a login i.e -> jmontoya2
  1299. * @return array with the $usernames array and the $user_array array
  1300. * @author Julio Montoya Armas
  1301. */
  1302. function check_user_in_array($usernames, $user_array) {
  1303. $user_list = array_keys($usernames);
  1304. $username = $user_array['username'].$user_array['sufix'];
  1305. if (in_array($username, $user_list)) {
  1306. $user_array['sufix'] += $usernames[$username];
  1307. $usernames[$username]++;
  1308. } else {
  1309. $usernames[$username] = 1;
  1310. }
  1311. $result_array = array($usernames, $user_array);
  1312. return $result_array;
  1313. }
  1314. /**
  1315. * Checks whether a username has been already subscribed in a session.
  1316. * @param string a given username
  1317. * @param array the array with the course list codes
  1318. * @param the session id
  1319. * @return 0 if the user is not subscribed otherwise it returns the user_id of the given username
  1320. * @author Julio Montoya Armas
  1321. */
  1322. function user_available_in_session($username, $course_list, $id_session) {
  1323. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  1324. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1325. $id_session = intval($id_session);
  1326. $username = Database::escape_string($username);
  1327. foreach($course_list as $enreg_course) {
  1328. $sql_select = " SELECT u.user_id FROM $tbl_session_rel_course_rel_user rel INNER JOIN $table_user u
  1329. ON (rel.id_user=u.user_id)
  1330. WHERE rel.id_session='$id_session' AND u.status='5' AND u.username ='$username' AND rel.course_code='$enreg_course'";
  1331. $rs = Database::query($sql_select);
  1332. if (Database::num_rows($rs) > 0) {
  1333. return Database::result($rs, 0, 0);
  1334. } else {
  1335. return 0;
  1336. }
  1337. }
  1338. }
  1339. /**
  1340. This function checks whether some users in the uploaded file repeated and creates unique usernames if necesary.
  1341. A case: Within the file there is an user repeted twice (Julio Montoya / Julio Montoya) and the username fields are empty.
  1342. Then, this function would create unique usernames based on the first and the last name. Two users wiould be created - jmontoya and jmontoya2.
  1343. 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.
  1344. @param $users list of users
  1345. @author Julio Montoya Armas
  1346. */
  1347. function check_all_usernames($users, $course_list, $id_session) {
  1348. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  1349. $usernames = array();
  1350. $new_users = array();
  1351. foreach ($users as $index => $user) {
  1352. $desired_username = array();
  1353. if (empty($user['UserName'])) {
  1354. $desired_username = MySpace::make_username($user['FirstName'], $user['LastName'], '');
  1355. $pre_username = $desired_username['username'].$desired_username['sufix'];
  1356. $user['UserName'] = $pre_username;
  1357. $user['create'] = '1';
  1358. } else {
  1359. if (UserManager::is_username_available($user['UserName'])) {
  1360. $desired_username = MySpace::make_username($user['FirstName'], $user['LastName'], $user['UserName']);
  1361. $user['UserName'] = $desired_username['username'].$desired_username['sufix'];
  1362. $user['create'] = '1';
  1363. } else {
  1364. $is_session_avail = MySpace::user_available_in_session($user['UserName'], $course_list, $id_session);
  1365. if ($is_session_avail == 0) {
  1366. $user_name = $user['UserName'];
  1367. $sql_select = "SELECT user_id FROM $table_user WHERE username ='$user_name' ";
  1368. $rs = Database::query($sql_select);
  1369. $user['create'] = Database::result($rs, 0, 0); // This should be the ID because the user exists.
  1370. } else {
  1371. $user['create'] = $is_session_avail;
  1372. }
  1373. }
  1374. }
  1375. // Usernames is the current list of users in the file.
  1376. $result_array = MySpace::check_user_in_array($usernames, $desired_username);
  1377. $usernames = $result_array[0];
  1378. $desired_username = $result_array[1];
  1379. $user['UserName'] = $desired_username['username'].$desired_username['sufix'];
  1380. $new_users[] = $user;
  1381. }
  1382. return $new_users;
  1383. }
  1384. /**
  1385. * This functions checks whether there are users that are already registered in the DB by different creator than the current coach.
  1386. * @param string a given username
  1387. * @param array the array with the course list codes
  1388. * @param the session id
  1389. * @author Julio Montoya Armas
  1390. */
  1391. function get_user_creator($users, $course_list, $id_session) {
  1392. $errors = array();
  1393. foreach ($users as $index => $user) {
  1394. // database table definition
  1395. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  1396. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1397. $username = Database::escape_string($user['UserName']);
  1398. //echo "<br>";
  1399. $sql = "SELECT creator_id FROM $table_user WHERE username='$username' ";
  1400. $rs = Database::query($sql);
  1401. $creator_id = Database::result($rs, 0, 0);
  1402. // check if we are the creators or not
  1403. if ($creator_id != '') {
  1404. if ($creator_id != api_get_user_id()) {
  1405. $user['error'] = get_lang('UserAlreadyRegisteredByOtherCreator');
  1406. $errors[] = $user;
  1407. }
  1408. }
  1409. }
  1410. return $errors;
  1411. }
  1412. /**
  1413. * Validates imported data.
  1414. * @param list of users
  1415. */
  1416. function validate_data($users, $id_session = null) {
  1417. $errors = array();
  1418. $usernames = array();
  1419. $new_users = array();
  1420. foreach ($users as $index => $user) {
  1421. // 1. Check whether mandatory fields are set.
  1422. $mandatory_fields = array('LastName', 'FirstName');
  1423. if (api_get_setting('registration', 'email') == 'true') {
  1424. $mandatory_fields[] = 'Email';
  1425. }
  1426. foreach ($mandatory_fields as $key => $field) {
  1427. if (!isset ($user[$field]) || strlen($user[$field]) == 0) {
  1428. $user['error'] = get_lang($field.'Mandatory');
  1429. $errors[] = $user;
  1430. }
  1431. }
  1432. // 2. Check whether the username is too long.
  1433. if (UserManager::is_username_too_long($user['UserName'])) {
  1434. $user['error'] = get_lang('UserNameTooLong');
  1435. $errors[] = $user;
  1436. }
  1437. $user['UserName'] = trim($user['UserName']);
  1438. if (empty($user['UserName'])) {
  1439. $user['UserName'] = UserManager::create_username($user['FirstName'], $user['LastName']);
  1440. }
  1441. $new_users[] = $user;
  1442. }
  1443. $results = array('errors' => $errors, 'users' => $new_users);
  1444. return $results;
  1445. }
  1446. /**
  1447. * Adds missing user-information (which isn't required, like password, etc).
  1448. */
  1449. function complete_missing_data($user) {
  1450. // 1. Generate a password if it is necessary.
  1451. if (!isset ($user['Password']) || strlen($user['Password']) == 0) {
  1452. $user['Password'] = api_generate_password();
  1453. }
  1454. return $user;
  1455. }
  1456. /**
  1457. * Saves imported data.
  1458. */
  1459. function save_data($users, $course_list, $id_session) {
  1460. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  1461. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1462. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1463. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1464. $id_session = intval($id_session);
  1465. $sendMail = $_POST['sendMail'] ? 1 : 0;
  1466. // Adding users to the platform.
  1467. $new_users = array();
  1468. foreach ($users as $index => $user) {
  1469. $user = MySpace::complete_missing_data($user);
  1470. // coach only will registered users
  1471. $default_status = '5';
  1472. if ($user['create'] == '1') {
  1473. $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'], '');
  1474. $user['added_at_platform'] = 1;
  1475. } else {
  1476. $user['id'] = $user['create'];
  1477. $user['added_at_platform'] = 0;
  1478. }
  1479. $new_users[] = $user;
  1480. }
  1481. // Update user list.
  1482. $users = $new_users;
  1483. // Inserting users.
  1484. $super_list = array();
  1485. foreach ($course_list as $enreg_course) {
  1486. $nbr_users = 0;
  1487. $new_users = array();
  1488. $enreg_course = Database::escape_string($enreg_course);
  1489. foreach ($users as $index => $user) {
  1490. $userid = intval($user['id']);
  1491. $sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user(id_session,course_code,id_user) VALUES('$id_session','$enreg_course','$userid')";
  1492. $course_session = array('course' => $enreg_course, 'added' => 1);
  1493. //$user['added_at_session'] = $course_session;
  1494. Database::query($sql);
  1495. if (Database::affected_rows()) {
  1496. $nbr_users++;
  1497. }
  1498. $new_users[] = $user;
  1499. }
  1500. $super_list[] = $new_users;
  1501. //update the nbr_users field
  1502. $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'";
  1503. $rs = Database::query($sql_select);
  1504. list($nbr_users) = Database::fetch_array($rs);
  1505. $sql_update = "UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users WHERE id_session='$id_session' AND course_code='$enreg_course'";
  1506. Database::query($sql_update);
  1507. $sql_update = "UPDATE $tbl_session SET nbr_users= '$nbr_users' WHERE id='$id_session'";
  1508. Database::query($sql_update);
  1509. }
  1510. // We don't delete the users (thoughts while dreaming)
  1511. //$sql_delete = "DELETE FROM $tbl_session_rel_user WHERE id_session = '$id_session'";
  1512. //Database::query($sql_delete);
  1513. $new_users = array();
  1514. foreach ($users as $index => $user) {
  1515. $userid = $user['id'];
  1516. $sql_insert = "INSERT IGNORE INTO $tbl_session_rel_user(id_session, id_user) VALUES('$id_session','$userid')";
  1517. Database::query($sql_insert);
  1518. $user['added_at_session'] = 1;
  1519. $new_users[] = $user;
  1520. }
  1521. $users = $new_users;
  1522. $registered_users = get_lang('FileImported').'<br /> Import file results : <br />';
  1523. // Sending emails.
  1524. $addedto = '';
  1525. if ($sendMail) {
  1526. $i = 0;
  1527. foreach ($users as $index => $user) {
  1528. $emailto = api_get_person_name($user['FirstName'], $user['LastName'], null, PERSON_NAME_EMAIL_ADDRESS).' <'.$user['Email'].'>';
  1529. $emailsubject = '['.api_get_setting('siteName').'] '.get_lang('YourReg').' '.api_get_setting('siteName');
  1530. $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')."";
  1531. $emailheaders = 'From: '.api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname'), null, PERSON_NAME_EMAIL_ADDRESS).' <'.api_get_setting('emailAdministrator').">\n";
  1532. $emailheaders .= 'Reply-To: '.api_get_setting('emailAdministrator');
  1533. @api_send_mail($emailto, $emailsubject, $emailbody, $emailheaders);
  1534. if (($user['added_at_platform'] == 1 && $user['added_at_session'] == 1) || $user['added_at_session'] == 1) {
  1535. if ($user['added_at_platform'] == 1) {
  1536. $addedto = get_lang('UserCreatedPlatform');
  1537. } else {
  1538. $addedto = ' ';
  1539. }
  1540. if ($user['added_at_session'] == 1) {
  1541. $addedto .= get_lang('UserInSession');
  1542. }
  1543. $registered_users .= "<a href=\"../user/userInfo.php?uInfo=".$user['id']."\">".api_get_person_name($user['FirstName'], $user['LastName'])."</a> - ".$addedto.'<br />';
  1544. } else {
  1545. $addedto = get_lang('UserNotAdded');
  1546. $registered_users .= "<a href=\"../user/userInfo.php?uInfo=".$user['id']."\">".api_get_person_name($user['FirstName'], $user['LastName'])."</a> - ".$addedto.'<br />';
  1547. }
  1548. }
  1549. } else {
  1550. $i = 0;
  1551. foreach ($users as $index => $user) {
  1552. if (($user['added_at_platform'] == 1 && $user['added_at_session'] == 1) || $user['added_at_session'] == 1) {
  1553. if ($user['added_at_platform'] == 1) {
  1554. $addedto = get_lang('UserCreatedPlatform');
  1555. } else {
  1556. $addedto = ' ';
  1557. }
  1558. if ($user['added_at_session'] == 1) {
  1559. $addedto .= ' '.get_lang('UserInSession');
  1560. }
  1561. $registered_users .= "<a href=\"../user/userInfo.php?uInfo=".$user['id']."\">".api_get_person_name($user['FirstName'], $user['LastName'])."</a> - ".$addedto.'<br />';
  1562. } else {
  1563. $addedto = get_lang('UserNotAdded');
  1564. $registered_users .= "<a href=\"../user/userInfo.php?uInfo=".$user['id']."\">".api_get_person_name($user['FirstName'], $user['LastName'])."</a> - ".$addedto.'<br />';
  1565. }
  1566. }
  1567. }
  1568. header('Location: course.php?id_session='.$id_session.'&action=show_message&message='.urlencode($registered_users));
  1569. exit ();
  1570. //header('Location: resume_session.php?id_session='.$id_session);
  1571. }
  1572. /**
  1573. * Reads CSV-file.
  1574. * @param string $file Path to the CSV-file
  1575. * @return array All userinformation read from the file
  1576. */
  1577. function parse_csv_data($file) {
  1578. $users = Import :: csv_to_array($file);
  1579. foreach ($users as $index => $user) {
  1580. if (isset ($user['Courses'])) {
  1581. $user['Courses'] = explode('|', trim($user['Courses']));
  1582. }
  1583. $users[$index] = $user;
  1584. }
  1585. return $users;
  1586. }
  1587. /**
  1588. * XML-parser: the handler at the beginning of element.
  1589. */
  1590. function element_start($parser, $data) {
  1591. $data = api_utf8_decode($data);
  1592. global $user;
  1593. global $current_tag;
  1594. switch ($data) {
  1595. case 'Contact' :
  1596. $user = array ();
  1597. break;
  1598. default :
  1599. $current_tag = $data;
  1600. }
  1601. }
  1602. /**
  1603. * XML-parser: the handler at the end of element.
  1604. */
  1605. function element_end($parser, $data) {
  1606. $data = api_utf8_decode($data);
  1607. global $user;
  1608. global $users;
  1609. global $current_value;
  1610. global $purification_option_for_usernames;
  1611. $user[$data] = $current_value;
  1612. switch ($data) {
  1613. case 'Contact' :
  1614. $user['UserName'] = UserManager::purify_username($user['UserName'], $purification_option_for_usernames);
  1615. $users[] = $user;
  1616. break;
  1617. default :
  1618. $user[$data] = $current_value;
  1619. break;
  1620. }
  1621. }
  1622. /**
  1623. * XML-parser: the handler for character data.
  1624. */
  1625. function character_data($parser, $data) {
  1626. $data = trim(api_utf8_decode($data));
  1627. global $current_value;
  1628. $current_value = $data;
  1629. }
  1630. /**
  1631. * Reads XML-file.
  1632. * @param string $file Path to the XML-file
  1633. * @return array All userinformation read from the file
  1634. */
  1635. function parse_xml_data($file) {
  1636. global $current_tag;
  1637. global $current_value;
  1638. global $user;
  1639. global $users;
  1640. $users = array ();
  1641. $parser = xml_parser_create('UTF-8');
  1642. xml_set_element_handler($parser, array('MySpace','element_start'), array('MySpace','element_end'));
  1643. xml_set_character_data_handler($parser, "character_data");
  1644. xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, false);
  1645. xml_parse($parser, api_utf8_encode_xml(file_get_contents($file)));
  1646. xml_parser_free($parser);
  1647. return $users;
  1648. }
  1649. }
  1650. function get_stats($user_id, $course_code, $start_date = null, $end_date = null) {
  1651. // Database table definitions
  1652. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  1653. $tbl_main = Database :: get_main_table(TABLE_MAIN_COURSE);
  1654. $course_info = api_get_course_info($course_code);
  1655. if (!empty($course_info)) {
  1656. $strg_sd = "";
  1657. $strg_ed = "";
  1658. if ($start_date != null && $end_date != null){
  1659. $end_date = add_day_to( $end_date );
  1660. $strg_sd = "AND login_course_date BETWEEN '$start_date' AND '$end_date'";
  1661. $strg_ed = "AND logout_course_date BETWEEN '$start_date' AND '$end_date'";
  1662. }
  1663. $sql = 'SELECT SEC_TO_TIME(avg(time_to_sec(timediff(logout_course_date,login_course_date)))) as avrg,
  1664. SEC_TO_TIME(sum(time_to_sec(timediff(logout_course_date,login_course_date)))) as total,
  1665. count(user_id) as times
  1666. FROM ' . $tbl_track_course . '
  1667. WHERE user_id = ' . intval($user_id) . '
  1668. AND course_code = "' . Database::escape_string($course_code) . '" '.$strg_sd.' '.$strg_ed.' '.'
  1669. ORDER BY login_course_date ASC';
  1670. $rs = Database::query($sql);
  1671. $result = array();
  1672. if ($row = Database::fetch_array($rs)) {
  1673. $foo_avg = $row['avrg'];
  1674. $foo_total = $row['total'];
  1675. $foo_times = $row['times'];
  1676. $result = array('avg' => $foo_avg, 'total' => $foo_total, 'times' => $foo_times);
  1677. }
  1678. }
  1679. return $result;
  1680. }
  1681. function add_day_to($end_date) {
  1682. $foo_date = strtotime( $end_date );
  1683. $foo_date = strtotime(" +1 day", $foo_date);
  1684. $foo_date = date("Y-m-d", $foo_date);
  1685. return $foo_date;
  1686. }
  1687. /**
  1688. * Gets the connections to a course as an array of login and logout time
  1689. *
  1690. * @param int $user_id
  1691. * @param string $course_code
  1692. * @author Jorge Frisancho Jibaja
  1693. * @author Julio Montoya <gugli100@gmail.com> fixing the function
  1694. * @version OCT-22- 2010
  1695. * @return array
  1696. */
  1697. function get_connections_to_course_by_date($user_id, $course_code, $start_date, $end_date) {
  1698. // Database table definitions
  1699. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  1700. $tbl_main = Database :: get_main_table(TABLE_MAIN_COURSE);
  1701. $course_info = api_get_course_info($course_code);
  1702. $user_id = intval($user_id);
  1703. if (!empty($course_info)) {
  1704. $end_date = add_day_to($end_date);
  1705. $sql = "SELECT login_course_date, logout_course_date FROM $tbl_track_course
  1706. WHERE user_id = $user_id
  1707. AND course_code = '$course_code'
  1708. AND login_course_date BETWEEN '$start_date' AND '$end_date'
  1709. AND logout_course_date BETWEEN '$start_date' AND '$end_date'
  1710. ORDER BY login_course_date ASC";
  1711. $rs = Database::query($sql);
  1712. $connections = array();
  1713. while ($row = Database::fetch_array($rs)) {
  1714. $login_date = $row['login_course_date'];
  1715. $logout_date = $row['logout_course_date'];
  1716. $timestamp_login_date = strtotime($login_date);
  1717. $timestamp_logout_date = strtotime($logout_date);
  1718. $connections[] = array('login' => $timestamp_login_date, 'logout' => $timestamp_logout_date);
  1719. }
  1720. }
  1721. return $connections;
  1722. }
  1723. /**
  1724. *
  1725. *
  1726. * @param array
  1727. * @author Jorge Frisancho Jibaja
  1728. * @version OCT-22- 2010
  1729. * @return array
  1730. */
  1731. function convert_to_array($sql_result){
  1732. $result_to_print = '<table>';
  1733. foreach ($sql_result as $key => $data) {
  1734. $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";
  1735. }
  1736. $result_to_print .= '</table>';
  1737. $result_to_print = array("result"=>$result_to_print);
  1738. return $result_to_print;
  1739. }
  1740. /**
  1741. * Converte an array to a table in html
  1742. *
  1743. * @param array $sql_result
  1744. * @author Jorge Frisancho Jibaja
  1745. * @version OCT-22- 2010
  1746. * @return string
  1747. */
  1748. function convert_to_string($sql_result){
  1749. $result_to_print = '<table>';
  1750. if (!empty($sql_result)) {
  1751. foreach ($sql_result as $key => $data) {
  1752. $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";
  1753. }
  1754. }
  1755. $result_to_print .= '</table>';
  1756. return $result_to_print;
  1757. }
  1758. /**
  1759. * This function draw the graphic to be displayed on the user view as an image
  1760. *
  1761. * @param array $sql_result
  1762. * @param string $start_date
  1763. * @param string $end_date
  1764. * @param string $type
  1765. * @author Jorge Frisancho Jibaja
  1766. * @version OCT-22- 2010
  1767. * @return string
  1768. */
  1769. function grapher($sql_result, $start_date, $end_date, $type = "") {
  1770. require_once api_get_path(LIBRARY_PATH).'pchart/pData.class.php';
  1771. require_once api_get_path(LIBRARY_PATH).'pchart/pChart.class.php';
  1772. require_once api_get_path(LIBRARY_PATH).'pchart/pCache.class.php';
  1773. if (empty($start_date)) { $start_date =""; }
  1774. if (empty($end_date)) { $end_date =""; }
  1775. if ($type == ""){ $type = 'day'; }
  1776. $main_year = $main_month_year = $main_day = array();
  1777. // get last 8 days/months
  1778. $last_days = 5;
  1779. $last_months = 3;
  1780. for ($i = $last_days; $i >= 0; $i--) {
  1781. $main_day[date ('d-m-Y', mktime () - $i * 3600 * 24)] = 0;
  1782. }
  1783. for ($i = $last_months; $i >= 0; $i--) {
  1784. $main_month_year[date ('m-Y', mktime () - $i * 30 * 3600 * 24)] = 0;
  1785. }
  1786. $i = 0;
  1787. if (is_array($sql_result) && count($sql_result) > 0) {
  1788. foreach ($sql_result as $key => $data) {
  1789. //creating the main array
  1790. $main_month_year[date('m-Y', $data['login'])] += float_format(($data['logout'] - $data['login']) / 60, 0);
  1791. $main_day[date('d-m-Y', $data['login'])] += float_format(($data['logout'] - $data['login']) / 60, 0);
  1792. if ($i > 500) {
  1793. break;
  1794. }
  1795. $i++;
  1796. }
  1797. switch ($type) {
  1798. case 'day':
  1799. $main_date = $main_day;
  1800. break;
  1801. case 'month':
  1802. $main_date = $main_month_year;
  1803. break;
  1804. case 'year':
  1805. $main_date = $main_year;
  1806. break;
  1807. }
  1808. // the nice graphics :D
  1809. $labels = array_keys($main_date);
  1810. if (count($main_date) == 1) {
  1811. $labels = $labels[0];
  1812. $main_date = $main_date[$labels];
  1813. }
  1814. $data_set = new pData();
  1815. $data_set->AddPoint($main_date, 'Q');
  1816. if (count($main_date)!= 1) {
  1817. $data_set->AddPoint($labels, 'Date');
  1818. }
  1819. $data_set->AddAllSeries();
  1820. $data_set->RemoveSerie('Date');
  1821. $data_set->SetAbsciseLabelSerie('Date');
  1822. $data_set->SetYAxisName(get_lang('Minutes', ''));
  1823. $graph_id = api_get_user_id().'AccessDetails'.api_get_course_id().$start_date.$end_date.$type;
  1824. $data_set->AddAllSeries();
  1825. $cache = new pCache();
  1826. // the graph id
  1827. $data = $data_set->GetData();
  1828. if ($cache->IsInCache($graph_id, $data_set->GetData())) {
  1829. //if (0) {
  1830. //if we already created the img
  1831. // echo 'in cache';
  1832. $img_file = $cache->GetHash($graph_id, $data_set->GetData());
  1833. } else {
  1834. // if the image does not exist in the archive/ folder
  1835. // Initialise the graph
  1836. $test = new pChart(760, 230);
  1837. //which schema of color will be used
  1838. $quant_resources = count($data[0]) - 1;
  1839. // Adding the color schemma
  1840. $test->loadColorPalette(api_get_path(LIBRARY_PATH).'pchart/palette/default.txt');
  1841. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 8);
  1842. $test->setGraphArea(70, 30, 680, 200);
  1843. $test->drawFilledRoundedRectangle(7, 7, 693, 223, 5, 240, 240, 240);
  1844. $test->drawRoundedRectangle(5, 5, 695, 225, 5, 230, 230, 230);
  1845. $test->drawGraphArea(255, 255, 255, TRUE);
  1846. $test->drawScale($data_set->GetData(), $data_set->GetDataDescription(), SCALE_START0, 150, 150, 150, TRUE, 0, 0);
  1847. $test->drawGrid(4, TRUE, 230, 230, 230, 50);
  1848. $test->setLineStyle(2);
  1849. // Draw the 0 line
  1850. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 6);
  1851. $test->drawTreshold(0, 143, 55, 72, TRUE, TRUE);
  1852. if (count($main_date) == 1) {
  1853. //Draw a graph
  1854. echo '<strong>'.$labels.'</strong><br/>';
  1855. $test->drawBarGraph($data_set->GetData(), $data_set->GetDataDescription(), TRUE);
  1856. } else {
  1857. //Draw the line graph
  1858. $test->drawLineGraph($data_set->GetData(), $data_set->GetDataDescription());
  1859. $test->drawPlotGraph($data_set->GetData(), $data_set->GetDataDescription(), 3, 2, 255, 255, 255);
  1860. }
  1861. // Finish the graph
  1862. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 8);
  1863. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 10);
  1864. $test->drawTitle(60, 22, get_lang('AccessDetails'), 50, 50, 50, 585);
  1865. //------------------
  1866. //echo 'not in cache';
  1867. $cache->WriteToCache($graph_id, $data_set->GetData(), $test);
  1868. ob_start();
  1869. $test->Stroke();
  1870. ob_end_clean();
  1871. $img_file = $cache->GetHash($graph_id, $data_set->GetData());
  1872. }
  1873. $foo_img = '<img src="'.api_get_path(WEB_ARCHIVE_PATH).$img_file.'">';
  1874. return $foo_img;
  1875. } else {
  1876. $foo_img = api_convert_encoding('<div id="messages" class="warning-message">'.get_lang('GraphicNotAvailable').'</div>','UTF-8');
  1877. return $foo_img;
  1878. }
  1879. }