myspace.lib.php 85 KB

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