myspace.lib.php 82 KB

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