myspace.lib.php 84 KB

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