myspace.lib.php 84 KB

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