myspace.lib.php 85 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. require_once api_get_path(LIBRARY_PATH).'export.lib.inc.php';
  4. require_once api_get_path(LIBRARY_PATH).'tracking.lib.php';
  5. require_once api_get_path(LIBRARY_PATH).'formvalidator/FormValidator.class.php';
  6. class MySpace {
  7. /**
  8. * This function serves exporting data in CSV format.
  9. * @param array $header The header labels.
  10. * @param array $data The data array.
  11. * @param string $file_name The name of the file which contains exported data.
  12. * @return string mixed Returns a message (string) if an error occurred.
  13. */
  14. function export_csv($header, $data, $file_name = 'export.csv') {
  15. $archive_path = api_get_path(SYS_ARCHIVE_PATH);
  16. $archive_url = api_get_path(WEB_CODE_PATH).'course_info/download.php?archive=';
  17. if (!$open = fopen($archive_path.$file_name, 'w+')) {
  18. $message = get_lang('noOpen');
  19. } else {
  20. $info = '';
  21. foreach ($header as $value) {
  22. $info .= $value.';';
  23. }
  24. $info .= "\r\n";
  25. foreach ($data as $row) {
  26. foreach ($row as $value) {
  27. $info .= $value.';';
  28. }
  29. $info .= "\r\n";
  30. }
  31. fwrite($open, $info);
  32. fclose($open);
  33. @chmod($file_name, api_get_permissions_for_new_files());
  34. header("Location:".$archive_url.$file_name);
  35. }
  36. return $message;
  37. }
  38. /**
  39. * Gets the connections to a course as an array of login and logout time
  40. *
  41. * @param int User ud
  42. * @param string Course code
  43. * @param int Session id (optional, default = 0)
  44. * @return array Conections
  45. */
  46. function get_connections_to_course($user_id, $course_code, $session_id = 0) {
  47. // Database table definitions
  48. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  49. $tbl_main = Database :: get_main_table(TABLE_MAIN_COURSE);
  50. // protect data
  51. $user_id = intval($user_id);
  52. $course_code = Database::escape_string($course_code);
  53. $session_id = intval($session_id);
  54. /*$sql_query = 'SELECT visual_code as course_code FROM '.$tbl_main.' c WHERE code="'.$course_code.'"';
  55. $result = Database::query($sql_query);
  56. $row_query = Database::fetch_array($result, 'ASSOC');
  57. $course_true = isset($row_query['course_code']) ? $row_query['course_code']: $course_code;*/
  58. $sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . '
  59. WHERE user_id = '.$user_id.'
  60. AND course_code="'.$course_code.'" AND session_id = '.$session_id.' ORDER BY login_course_date ASC';
  61. $rs = Database::query($sql);
  62. $connections = array();
  63. while ($row = Database::fetch_array($rs)) {
  64. $login_date = $row['login_course_date'];
  65. $logout_date = $row['logout_course_date'];
  66. $login_date = api_get_local_time($login_date, null, date_default_timezone_get());
  67. $logout_date = api_get_local_time($logout_date, null, date_default_timezone_get());
  68. $timestamp_login_date = api_strtotime($login_date);
  69. $timestamp_logout_date = api_strtotime($logout_date);
  70. $connections[] = array('login' => $timestamp_login_date, 'logout' => $timestamp_logout_date);
  71. }
  72. return $connections;
  73. }
  74. /**
  75. * TODO: Not used, to b deleted?
  76. * Enter description here...
  77. * @param int $user_id
  78. * @param string $course_code
  79. * @param date $year
  80. * @param date $month
  81. * @param date $day
  82. * @return unknown
  83. */
  84. function get_connections_to_course_by_time($user_id, $course_code, $year = '', $month = '', $day = '') {
  85. // Database table definitions
  86. $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  87. $tbl_main = Database :: get_main_table(TABLE_MAIN_COURSE);
  88. /*$sql_query = 'SELECT visual_code as course_code FROM '.$tbl_main.' c WHERE code="'.Database :: escape_string($course_code).'";';
  89. $result = Database::query($sql_query);
  90. $row_query = Database::fetch_array($result,'ASSOC');
  91. $course_true = isset($row_query['course_code']) ? $row_query['course_code']: $course_code;*/
  92. $sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . '
  93. WHERE user_id = ' . intval($user_id) . '
  94. AND course_code="' . Database::escape_string($course_code) . '"
  95. ORDER BY login_course_date DESC';
  96. $rs = Database::query($sql);
  97. $connections = array();
  98. while ($row = Database::fetch_array($rs)) {
  99. $login_date = $row['login_course_date'];
  100. $logout_date = $row['logout_course_date'];
  101. $timestamp_login_date = strtotime($login_date);
  102. $timestamp_logout_date = strtotime($logout_date);
  103. $connections[] = array('login' => $timestamp_login_date, 'logout' => $timestamp_logout_date);
  104. }
  105. return $connections;
  106. }
  107. /**
  108. * Creates a small table in the last column of the table with the user overview
  109. *
  110. * @param integer $user_id the id of the user
  111. * @param array $url_params additonal url parameters
  112. * @param array $row the row information (the other columns)
  113. * @return string html code
  114. */
  115. function course_info_tracking_filter($user_id, $url_params, $row) {
  116. // the table header
  117. $return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  118. /*$return .= ' <tr>';
  119. $return .= ' <th>'.get_lang('Course').'</th>';
  120. $return .= ' <th>'.get_lang('AvgTimeSpentInTheCourse').'</th>';
  121. $return .= ' <th>'.get_lang('AvgStudentsProgress').'</th>';
  122. $return .= ' <th>'.get_lang('AvgCourseScore').'</th>';
  123. $return .= ' <th>'.get_lang('AvgExercisesScore').'</th>';
  124. $return .= ' <th>'.get_lang('AvgMessages').'</th>';
  125. $return .= ' <th>'.get_lang('AvgAssignments').'</th>';
  126. $return .= ' <th>'.get_lang('TotalExercisesScoreObtained').'</th>';
  127. $return .= ' <th>'.get_lang('TotalExercisesScorePossible').'</th>';
  128. $return .= ' <th>'.get_lang('TotalExercisesAnswered').'</th>';
  129. $return .= ' <th>'.get_lang('TotalExercisesScorePercentage').'</th>';
  130. $return .= ' <th>'.get_lang('FirstLogin').'</th>';
  131. $return .= ' <th>'.get_lang('LatestLogin').'</th>';
  132. $return .= ' </tr>';*/
  133. // database table definition
  134. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  135. // getting all the courses of the user
  136. $sql = "SELECT * FROM $tbl_course_user WHERE user_id = '".Database::escape_string($user_id)."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  137. $result = Database::query($sql);
  138. while ($row = Database::fetch_row($result)) {
  139. $return .= '<tr>';
  140. // course code
  141. $return .= ' <td width="157px" >'.cut($row[0], 20, true).'</td>';
  142. // time spent in the course
  143. $return .= ' <td><div>'.api_time_to_hms(Tracking :: get_time_spent_on_the_course($user_id, $row[0])).'</div></td>';
  144. // student progress in course
  145. $return .= ' <td><div>'.round(Tracking :: get_avg_student_progress($user_id, $row[0]), 2).'</div></td>';
  146. // student score
  147. $avg_score = Tracking :: get_avg_student_score($user_id, $row[0]);
  148. if (is_numeric($avg_score)) {
  149. $avg_score = round($avg_score,2);
  150. } else {
  151. $$avg_score = '-';
  152. }
  153. $return .= ' <td><div>'.$avg_score.'</div></td>';
  154. // student tes score
  155. //$return .= ' <td><div style="width:40px">'.round(Tracking :: get_avg_student_exercise_score ($user_id, $row[0]),2).'%</div></td>';
  156. // student messages
  157. $return .= ' <td><div>'.Tracking :: count_student_messages($user_id, $row[0]).'</div></td>';
  158. // student assignments
  159. $return .= ' <td><div>'.Tracking :: count_student_assignments($user_id, $row[0]).'</div></td>';
  160. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  161. $exercises_results = MySpace::exercises_results($user_id, $row[0]);
  162. $return .= ' <td width="105px"><div>'.(is_null($exercises_results['percentage']) ? '' : $exercises_results['score_obtained'].'/'.$exercises_results['score_possible'].' ( '.$exercises_results['percentage'].'% )').'</div></td>';
  163. //$return .= ' <td><div>'.$exercises_results['score_possible'].'</div></td>';
  164. $return .= ' <td><div>'.$exercises_results['questions_answered'].'</div></td>';
  165. //$return .= ' <td><div>'.$exercises_results['percentage'].'% </div></td>';
  166. // first connection
  167. //$return .= ' <td width="60px">'.Tracking :: get_first_connection_date_on_the_course ($user_id, $row[0]).'</td>';
  168. // last connection
  169. $return .= ' <td><div>'.Tracking :: get_last_connection_date_on_the_course ($user_id, $row[0]).'</div></td>';
  170. $return .= '<tr>';
  171. }
  172. $return .= '</table>';
  173. return $return;
  174. }
  175. /**
  176. * Display a sortable table that contains an overview off all the reporting progress of all users and all courses the user is subscribed to
  177. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  178. * @version Dokeos 1.8.6
  179. * @since October 2008
  180. */
  181. function display_tracking_user_overview() {
  182. MySpace::display_user_overview_export_options();
  183. $t_head .= ' <table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  184. //$t_head .= ' <caption>'.get_lang('CourseInformation').'</caption>';
  185. $t_head .= '<tr>';
  186. $t_head .= ' <th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>';
  187. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  188. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  189. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  190. //$t_head .= ' <th><div style="width:40px">'.get_lang('AvgExercisesScore').'</div></th>';
  191. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  192. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  193. $t_head .= ' <th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  194. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePossible').'</div></th>';
  195. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  196. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePercentage').'</div></th>';
  197. //$t_head .= ' <th><div style="width:60px">'.get_lang('FirstLogin').'</div></th>';
  198. $t_head .= ' <th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  199. $t_head .= ' </tr></table>';
  200. $addparams = array('view' => 'admin', 'display' => 'useroverview');
  201. $table = new SortableTable('tracking_user_overview', array('MySpace','get_number_of_users_tracking_overview'), array('MySpace','get_user_data_tracking_overview'), 0);
  202. $table->additional_parameters = $addparams;
  203. $table->set_header(0, get_lang('OfficialCode'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  204. if (api_is_western_name_order()) {
  205. $table->set_header(1, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  206. $table->set_header(2, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  207. } else {
  208. $table->set_header(1, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  209. $table->set_header(2, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  210. }
  211. $table->set_header(3, get_lang('LoginName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  212. $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;'));
  213. $table->set_column_filter(4, array('MySpace','course_info_tracking_filter'));
  214. $table->display();
  215. }
  216. /**
  217. * Displays a form with all the additionally defined user fields of the profile
  218. * and give you the opportunity to include these in the CSV export
  219. *
  220. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  221. * @version Dokeos 1.8.6
  222. * @since November 2008
  223. */
  224. function display_user_overview_export_options() {
  225. // include the user manager and formvalidator library
  226. if ($_GET['export'] == 'options') {
  227. // get all the defined extra fields
  228. $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC', false, 1);
  229. // creating the form with all the defined extra fields
  230. $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']));
  231. if (is_array($extrafields) && count($extrafields) > 0) {
  232. foreach ($extrafields as $key => $extra) {
  233. $form->addElement('checkbox', 'extra_export_field'.$extra[0], '', $extra[3]);
  234. }
  235. $form->addElement('style_submit_button','submit', get_lang('Ok'),'class="save"' );
  236. // setting the default values for the form that contains all the extra fields
  237. if (is_array($_SESSION['additional_export_fields'])) {
  238. foreach ($_SESSION['additional_export_fields'] as $key => $value) {
  239. $defaults['extra_export_field'.$value] = 1;
  240. }
  241. }
  242. $form->setDefaults($defaults);
  243. } else {
  244. $form->addElement('html', Display::display_warning_message(get_lang('ThereAreNotExtrafieldsAvailable')));
  245. }
  246. if ($form->validate()) {
  247. // exporting the form values
  248. $values = $form->exportValues();
  249. // re-initialising the session that contains the additional fields that need to be exported
  250. $_SESSION['additional_export_fields'] = array();
  251. // adding the fields that are checked to the session
  252. $message = '';
  253. foreach ($values as $field_ids => $value) {
  254. if ($value == 1 && strstr($field_ids,'extra_export_field')) {
  255. $_SESSION['additional_export_fields'][] = str_replace('extra_export_field', '', $field_ids);
  256. }
  257. }
  258. // adding the fields that will be also exported to a message string
  259. if (is_array($_SESSION['additional_export_fields'])) {
  260. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  261. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  262. }
  263. }
  264. // Displaying a feedback message
  265. if (!empty($_SESSION['additional_export_fields'])) {
  266. Display::display_confirmation_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', false);
  267. } else {
  268. Display::display_confirmation_message(get_lang('NoAdditionalFieldsWillBeExported'), false);
  269. }
  270. $message = '';
  271. } else {
  272. $form->display();
  273. }
  274. } else {
  275. if (!empty($_SESSION['additional_export_fields'])) {
  276. // get all the defined extra fields
  277. $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  278. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  279. $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
  280. }
  281. Display::display_normal_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', false);
  282. $message = '';
  283. }
  284. }
  285. }
  286. /**
  287. * Display a sortable table that contains an overview of all the reporting progress of all courses
  288. */
  289. function display_tracking_course_overview() {
  290. //MySpace::display_user_overview_export_options();
  291. $t_head .= ' <table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  292. //$t_head .= ' <caption>'.get_lang('CourseInformation').'</caption>';
  293. $t_head .= '<tr>';
  294. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  295. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  296. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  297. //$t_head .= ' <th><div style="width:40px">'.get_lang('AvgExercisesScore').'</div></th>';
  298. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  299. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  300. $t_head .= ' <th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  301. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePossible').'</div></th>';
  302. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  303. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePercentage').'</div></th>';
  304. //$t_head .= ' <th><div style="width:60px">'.get_lang('FirstLogin').'</div></th>';
  305. $t_head .= ' <th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  306. $t_head .= ' </tr></table>';
  307. $addparams = array('view' => 'admin', 'display' => 'courseoverview');
  308. $table = new SortableTable('tracking_session_overview', array('MySpace','get_total_number_courses'), array('MySpace','get_course_data_tracking_overview'), 1);
  309. $table->additional_parameters = $addparams;
  310. $table->set_header(0, '', false, null, array('style' => 'display: none'));
  311. $table->set_header(1, get_lang('Course'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  312. $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;'));
  313. $table->set_column_filter(2, array('MySpace','course_tracking_filter'));
  314. $table->display();
  315. }
  316. /**
  317. * Get the total number of courses
  318. *
  319. * @return integer Total number of courses
  320. */
  321. public function get_total_number_courses() {
  322. // database table definition
  323. $main_course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  324. return Database::count_rows($main_course_table);
  325. }
  326. /**
  327. * Get data for the courses
  328. *
  329. * @param int Inferior limit
  330. * @param int Number of items to select
  331. * @param string Column to order on
  332. * @param string Order direction
  333. * @return array Results
  334. */
  335. public function get_course_data_tracking_overview($from, $number_of_items, $column, $direction) {
  336. //global $_configuration;
  337. // database table definition
  338. //$access_url_id = api_get_current_access_url_id();
  339. //$tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  340. $main_course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  341. /*if ($_configuration['multiple_access_urls']) {
  342. $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'";
  343. }
  344. global $export_csv;
  345. if ($export_csv) {
  346. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  347. } else {
  348. $is_western_name_order = api_is_western_name_order();
  349. }*/
  350. $sql = "SELECT code AS col0, title AS col1 FROM $main_course_table";
  351. $sql .= " ORDER BY col$column $direction ";
  352. $sql .= " LIMIT $from,$number_of_items";
  353. $result = Database::query($sql);
  354. $return = array ();
  355. while ($course = Database::fetch_row($result)) {
  356. $return[] = $course;
  357. }
  358. return $return;
  359. }
  360. /**
  361. * Fills in course reporting data
  362. *
  363. * @param integer course code
  364. * @param array $url_params additonal url parameters
  365. * @param array $row the row information (the other columns)
  366. * @return string html code
  367. */
  368. function course_tracking_filter($course_code, $url_params, $row) {
  369. $course_code = $row[0];
  370. // the table header
  371. $return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  372. /*$return .= ' <tr>';
  373. $return .= ' <th>'.get_lang('Course').'</th>';
  374. $return .= ' <th>'.get_lang('AvgTimeSpentInTheCourse').'</th>';
  375. $return .= ' <th>'.get_lang('AvgStudentsProgress').'</th>';
  376. $return .= ' <th>'.get_lang('AvgCourseScore').'</th>';
  377. $return .= ' <th>'.get_lang('AvgExercisesScore').'</th>';
  378. $return .= ' <th>'.get_lang('AvgMessages').'</th>';
  379. $return .= ' <th>'.get_lang('AvgAssignments').'</th>';
  380. $return .= ' <th>'.get_lang('TotalExercisesScoreObtained').'</th>';
  381. $return .= ' <th>'.get_lang('TotalExercisesScorePossible').'</th>';
  382. $return .= ' <th>'.get_lang('TotalExercisesAnswered').'</th>';
  383. $return .= ' <th>'.get_lang('TotalExercisesScorePercentage').'</th>';
  384. $return .= ' <th>'.get_lang('FirstLogin').'</th>';
  385. $return .= ' <th>'.get_lang('LatestLogin').'</th>';
  386. $return .= ' </tr>';*/
  387. // database table definition
  388. $tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  389. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  390. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  391. // getting all the courses of the user
  392. $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);";
  393. $result = Database::query($sql);
  394. $time_spent = 0;
  395. $progress = 0;
  396. $nb_progress_lp = 0;
  397. $score = 0;
  398. $nb_score_lp = 0;
  399. $nb_messages = 0;
  400. $nb_assignments = 0;
  401. $last_login_date = false;
  402. $total_score_obtained = 0;
  403. $total_score_possible = 0;
  404. $total_questions_answered = 0;
  405. while ($row = Database::fetch_object($result)) {
  406. // get time spent in the course and session
  407. $time_spent += Tracking::get_time_spent_on_the_course($row->user_id, $course_code);
  408. $progress_tmp = Tracking::get_avg_student_progress($row->user_id, $course_code, array(), null, true);
  409. $progress += $progress_tmp[0];
  410. $nb_progress_lp += $progress_tmp[1];
  411. $score_tmp = Tracking :: get_avg_student_score($row->user_id, $course_code, array(), null, true);
  412. if(is_array($score_tmp)) {
  413. $score += $score_tmp[0];
  414. $nb_score_lp += $score_tmp[1];
  415. }
  416. $nb_messages += Tracking::count_student_messages($row->user_id, $course_code);
  417. $nb_assignments += Tracking::count_student_assignments($row->user_id, $course_code);
  418. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row->user_id, $course_code, null, false);
  419. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned
  420. $last_login_date = $last_login_date_tmp;
  421. } else if($last_login_date_tmp != false && $last_login_date != false) { // TODO: Repeated previous condition. To be cleaned.
  422. // Find the max and assign it to first_login_date
  423. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  424. $last_login_date = $last_login_date_tmp;
  425. }
  426. }
  427. $exercise_results_tmp = MySpace::exercises_results($row->user_id, $course_code);
  428. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  429. $total_score_possible += $exercise_results_tmp['score_possible'];
  430. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  431. }
  432. if($nb_progress_lp > 0) {
  433. $avg_progress = round($progress / $nb_progress_lp, 2);
  434. } else {
  435. $avg_progress = 0;
  436. }
  437. if($nb_score_lp > 0) {
  438. $avg_score = round($score / $nb_score_lp, 2);
  439. } else {
  440. $avg_score = '-';
  441. }
  442. if($last_login_date) {
  443. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  444. } else {
  445. $last_login_date = '-';
  446. }
  447. if($total_score_possible > 0) {
  448. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  449. } else {
  450. $total_score_percentage = 0;
  451. }
  452. if($total_score_percentage > 0) {
  453. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  454. } else {
  455. $total_score = '-';
  456. }
  457. $return .= '<tr>';
  458. // time spent in the course
  459. $return .= ' <td style="width:164px;">'.api_time_to_hms($time_spent).'</td>';
  460. // student progress in course
  461. $return .= ' <td>'.$avg_progress.'</td>';
  462. // student score
  463. $return .= ' <td>'.$avg_score.'</td>';
  464. // student messages
  465. $return .= ' <td>'.$nb_messages.'</td>';
  466. // student assignments
  467. $return .= ' <td>'.$nb_assignments.'</td>';
  468. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  469. $return .= '<td width="105px;">'.$total_score.'</td>';
  470. $return .= '<td>'.$total_questions_answered.'</td>';
  471. // last connection
  472. $return .= ' <td>'.$last_login_date.'</td>';
  473. $return .= '</tr>';
  474. $return .= '</table>';
  475. return $return;
  476. }
  477. /**
  478. * This function exports the table that we see in display_tracking_course_overview()
  479. *
  480. */
  481. function export_tracking_course_overview() {
  482. // database table definition
  483. $tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  484. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  485. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  486. // the values of the sortable table
  487. if ($_GET['tracking_course_overview_page_nr']) {
  488. $from = $_GET['tracking_course_overview_page_nr'];
  489. } else {
  490. $from = 0;
  491. }
  492. if ($_GET['tracking_course_overview_column']) {
  493. $orderby = $_GET['tracking_course_overview_column'];
  494. } else {
  495. $orderby = 0;
  496. }
  497. if ($_GET['tracking_course_overview_direction']) {
  498. $direction = $_GET['tracking_course_overview_direction'];
  499. } else {
  500. $direction = 'ASC';
  501. }
  502. $course_data = MySpace::get_course_data_tracking_overview($from, 1000, $orderby, $direction);
  503. $csv_content = array();
  504. // the first line of the csv file with the column headers
  505. $csv_row = array();
  506. $csv_row[] = get_lang('Course', '');
  507. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  508. $csv_row[] = get_lang('AvgStudentsProgress', '');
  509. $csv_row[] = get_lang('AvgCourseScore', '');
  510. $csv_row[] = get_lang('TotalNumberOfMessages', '');
  511. $csv_row[] = get_lang('TotalNumberOfAssignments', '');
  512. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  513. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  514. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  515. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  516. $csv_row[] = get_lang('LatestLogin', '');
  517. $csv_content[] = $csv_row;
  518. // the other lines (the data)
  519. foreach ($course_data as $key => $course) {
  520. $course_code = $course[0];
  521. $course_title = $course[1];
  522. $csv_row = array();
  523. $csv_row[] = $course_title;
  524. // getting all the courses of the session
  525. $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);";
  526. $result = Database::query($sql);
  527. $time_spent = 0;
  528. $progress = 0;
  529. $nb_progress_lp = 0;
  530. $score = 0;
  531. $nb_score_lp = 0;
  532. $nb_messages = 0;
  533. $nb_assignments = 0;
  534. $last_login_date = false;
  535. $total_score_obtained = 0;
  536. $total_score_possible = 0;
  537. $total_questions_answered = 0;
  538. while ($row = Database::fetch_object($result)) {
  539. // get time spent in the course and session
  540. $time_spent += Tracking::get_time_spent_on_the_course($row->user_id, $course_code);
  541. $progress_tmp = Tracking::get_avg_student_progress($row->user_id, $course_code, array(), null, true);
  542. $progress += $progress_tmp[0];
  543. $nb_progress_lp += $progress_tmp[1];
  544. $score_tmp = Tracking :: get_avg_student_score($row->user_id, $course_code, array(), null, true);
  545. if(is_array($score_tmp)) {
  546. $score += $score_tmp[0];
  547. $nb_score_lp += $score_tmp[1];
  548. }
  549. $nb_messages += Tracking::count_student_messages($row->user_id, $course_code);
  550. $nb_assignments += Tracking::count_student_assignments($row->user_id, $course_code);
  551. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row->user_id, $course_code, null, false);
  552. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned.
  553. $last_login_date = $last_login_date_tmp;
  554. } else if($last_login_date_tmp != false && $last_login_date == false) { // TODO: Repeated previous condition. To be cleaned.
  555. // Find the max and assign it to first_login_date
  556. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  557. $last_login_date = $last_login_date_tmp;
  558. }
  559. }
  560. $exercise_results_tmp = MySpace::exercises_results($row->user_id, $course_code);
  561. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  562. $total_score_possible += $exercise_results_tmp['score_possible'];
  563. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  564. }
  565. if($nb_progress_lp > 0) {
  566. $avg_progress = round($progress / $nb_progress_lp, 2);
  567. } else {
  568. $avg_progress = 0;
  569. }
  570. if($nb_score_lp > 0) {
  571. $avg_score = round($score / $nb_score_lp, 2);
  572. } else {
  573. $avg_score = '-';
  574. }
  575. if($last_login_date) {
  576. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  577. } else {
  578. $last_login_date = '-';
  579. }
  580. if($total_score_possible > 0) {
  581. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  582. } else {
  583. $total_score_percentage = 0;
  584. }
  585. // time spent in the course
  586. $csv_row[] = api_time_to_hms($time_spent);
  587. // student progress in course
  588. $csv_row[] = $avg_progress;
  589. // student score
  590. $csv_row[] = $avg_score;
  591. // student messages
  592. $csv_row[] = $nb_messages;
  593. // student assignments
  594. $csv_row[] = $nb_assignments;
  595. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  596. $csv_row[] = $total_score_obtained;
  597. $csv_row[] = $total_score_possible;
  598. $csv_row[] = $total_questions_answered;
  599. $csv_row[] = $total_score_percentage;
  600. // last connection
  601. $csv_row[] = $last_login_date;
  602. $csv_content[] = $csv_row;
  603. }
  604. Export :: export_table_csv($csv_content, 'reporting_course_overview');
  605. exit;
  606. }
  607. /**
  608. * Display a sortable table that contains an overview of all the reporting progress of all sessions and all courses the user is subscribed to
  609. * @author Guillaume Viguier <guillaume@viguierjust.com>
  610. */
  611. function display_tracking_session_overview() {
  612. //MySpace::display_user_overview_export_options();
  613. $t_head .= ' <table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  614. //$t_head .= ' <caption>'.get_lang('CourseInformation').'</caption>';
  615. $t_head .= '<tr>';
  616. $t_head .= ' <th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>';
  617. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
  618. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
  619. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
  620. //$t_head .= ' <th><div style="width:40px">'.get_lang('AvgExercisesScore').'</div></th>';
  621. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>';
  622. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>';
  623. $t_head .= ' <th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
  624. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePossible').'</div></th>';
  625. $t_head .= ' <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
  626. //$t_head .= ' <th><div>'.get_lang('TotalExercisesScorePercentage').'</div></th>';
  627. //$t_head .= ' <th><div style="width:60px">'.get_lang('FirstLogin').'</div></th>';
  628. $t_head .= ' <th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
  629. $t_head .= ' </tr></table>';
  630. $addparams = array('view' => 'admin', 'display' => 'sessionoverview');
  631. $table = new SortableTable('tracking_session_overview', array('MySpace','get_total_number_sessions'), array('MySpace','get_session_data_tracking_overview'), 1);
  632. $table->additional_parameters = $addparams;
  633. $table->set_header(0, '', false, null, array('style' => 'display: none'));
  634. $table->set_header(1, get_lang('Session'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
  635. $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;'));
  636. $table->set_column_filter(2, array('MySpace','session_tracking_filter'));
  637. $table->display();
  638. }
  639. /**
  640. * Get the total number of sessions
  641. *
  642. * @return integer Total number of sessions
  643. */
  644. public function get_total_number_sessions() {
  645. // database table definition
  646. $main_session_table = Database :: get_main_table(TABLE_MAIN_SESSION);
  647. return Database::count_rows($main_session_table);
  648. }
  649. /**
  650. * Get data for the sessions
  651. *
  652. * @param int Inferior limit
  653. * @param int Number of items to select
  654. * @param string Column to order on
  655. * @param string Order direction
  656. * @return array Results
  657. */
  658. public function get_session_data_tracking_overview($from, $number_of_items, $column, $direction) {
  659. //global $_configuration;
  660. // database table definition
  661. //$access_url_id = api_get_current_access_url_id();
  662. //$tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  663. $main_session_table = Database :: get_main_table(TABLE_MAIN_SESSION);
  664. /*if ($_configuration['multiple_access_urls']) {
  665. $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'";
  666. }
  667. global $export_csv;
  668. if ($export_csv) {
  669. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  670. } else {
  671. $is_western_name_order = api_is_western_name_order();
  672. }*/
  673. $sql = "SELECT id AS col0, name AS col1 FROM $main_session_table";
  674. $sql .= " ORDER BY col$column $direction ";
  675. $sql .= " LIMIT $from,$number_of_items";
  676. $result = Database::query($sql);
  677. $return = array ();
  678. while ($session = Database::fetch_row($result)) {
  679. $return[] = $session;
  680. }
  681. return $return;
  682. }
  683. /**
  684. * Fills in session reporting data
  685. *
  686. * @param integer $user_id the id of the user
  687. * @param array $url_params additonal url parameters
  688. * @param array $row the row information (the other columns)
  689. * @return string html code
  690. */
  691. function session_tracking_filter($session_id, $url_params, $row) {
  692. $session_id = $row[0];
  693. // the table header
  694. $return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
  695. /*$return .= ' <tr>';
  696. $return .= ' <th>'.get_lang('Course').'</th>';
  697. $return .= ' <th>'.get_lang('AvgTimeSpentInTheCourse').'</th>';
  698. $return .= ' <th>'.get_lang('AvgStudentsProgress').'</th>';
  699. $return .= ' <th>'.get_lang('AvgCourseScore').'</th>';
  700. $return .= ' <th>'.get_lang('AvgExercisesScore').'</th>';
  701. $return .= ' <th>'.get_lang('AvgMessages').'</th>';
  702. $return .= ' <th>'.get_lang('AvgAssignments').'</th>';
  703. $return .= ' <th>'.get_lang('TotalExercisesScoreObtained').'</th>';
  704. $return .= ' <th>'.get_lang('TotalExercisesScorePossible').'</th>';
  705. $return .= ' <th>'.get_lang('TotalExercisesAnswered').'</th>';
  706. $return .= ' <th>'.get_lang('TotalExercisesScorePercentage').'</th>';
  707. $return .= ' <th>'.get_lang('FirstLogin').'</th>';
  708. $return .= ' <th>'.get_lang('LatestLogin').'</th>';
  709. $return .= ' </tr>';*/
  710. // database table definition
  711. $tbl_session_rel_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  712. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  713. $tbl_session_rel_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  714. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  715. // getting all the courses of the user
  716. $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."';";
  717. $result = Database::query($sql);
  718. while ($row = Database::fetch_object($result)) {
  719. $return .= '<tr>';
  720. // course code
  721. $return .= ' <td width="157px" >'.$row->title.'</td>';
  722. // get the users in the course
  723. $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."';";
  724. $result_users = Database::query($sql);
  725. $time_spent = 0;
  726. $progress = 0;
  727. $nb_progress_lp = 0;
  728. $score = 0;
  729. $nb_score_lp = 0;
  730. $nb_messages = 0;
  731. $nb_assignments = 0;
  732. $last_login_date = false;
  733. $total_score_obtained = 0;
  734. $total_score_possible = 0;
  735. $total_questions_answered = 0;
  736. while($row_user = Database::fetch_object($result_users)) {
  737. // get time spent in the course and session
  738. $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $row->code, $session_id);
  739. $progress_tmp = Tracking::get_avg_student_progress($row_user->user_id, $row->code, array(), $session_id, true);
  740. $progress += $progress_tmp[0];
  741. $nb_progress_lp += $progress_tmp[1];
  742. $score_tmp = Tracking :: get_avg_student_score($row_user->user_id, $row->code, array(), $session_id, true);
  743. if(is_array($score_tmp)) {
  744. $score += $score_tmp[0];
  745. $nb_score_lp += $score_tmp[1];
  746. }
  747. $nb_messages += Tracking::count_student_messages($row_user->user_id, $row->code, $session_id);
  748. $nb_assignments += Tracking::count_student_assignments($row_user->user_id, $row->code, $session_id);
  749. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row_user->user_id, $row->code, $session_id, false);
  750. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned.
  751. $last_login_date = $last_login_date_tmp;
  752. } else if($last_login_date_tmp != false && $last_login_date != false) { // TODO: Repeated previous condition! To be cleaned.
  753. // Find the max and assign it to first_login_date
  754. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  755. $last_login_date = $last_login_date_tmp;
  756. }
  757. }
  758. $exercise_results_tmp = MySpace::exercises_results($row_user->user_id, $row->code, $session_id);
  759. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  760. $total_score_possible += $exercise_results_tmp['score_possible'];
  761. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  762. }
  763. if($nb_progress_lp > 0) {
  764. $avg_progress = round($progress / $nb_progress_lp, 2);
  765. } else {
  766. $avg_progress = 0;
  767. }
  768. if($nb_score_lp > 0) {
  769. $avg_score = round($score / $nb_score_lp, 2);
  770. } else {
  771. $avg_score = '-';
  772. }
  773. if($last_login_date) {
  774. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  775. } else {
  776. $last_login_date = '-';
  777. }
  778. if($total_score_possible > 0) {
  779. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  780. } else {
  781. $total_score_percentage = 0;
  782. }
  783. if($total_score_percentage > 0) {
  784. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  785. } else {
  786. $total_score = '-';
  787. }
  788. // time spent in the course
  789. $return .= ' <td><div>'.api_time_to_hms($time_spent).'</div></td>';
  790. // student progress in course
  791. $return .= ' <td><div>'.$avg_progress.'</div></td>';
  792. // student score
  793. $return .= ' <td><div>'.$avg_score.'</div></td>';
  794. // student messages
  795. $return .= ' <td><div>'.$nb_messages.'</div></td>';
  796. // student assignments
  797. $return .= ' <td><div>'.$nb_assignments.'</div></td>';
  798. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  799. $return .= '<td width="105px;">'.$total_score.'</td>';
  800. $return .= '<td>'.$total_questions_answered.'</td>';
  801. // last connection
  802. $return .= ' <td><div>'.$last_login_date.'</div></td>';
  803. $return .= '<tr>';
  804. }
  805. $return .= '</table>';
  806. return $return;
  807. }
  808. /**
  809. * This function exports the table that we see in display_tracking_session_overview()
  810. *
  811. */
  812. function export_tracking_session_overview() {
  813. // database table definition
  814. $tbl_session_rel_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  815. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  816. $tbl_session_rel_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  817. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  818. // the values of the sortable table
  819. if ($_GET['tracking_session_overview_page_nr']) {
  820. $from = $_GET['tracking_session_overview_page_nr'];
  821. } else {
  822. $from = 0;
  823. }
  824. if ($_GET['tracking_session_overview_column']) {
  825. $orderby = $_GET['tracking_session_overview_column'];
  826. } else {
  827. $orderby = 0;
  828. }
  829. if ($_GET['tracking_session_overview_direction']) {
  830. $direction = $_GET['tracking_session_overview_direction'];
  831. } else {
  832. $direction = 'ASC';
  833. }
  834. $session_data = MySpace::get_session_data_tracking_overview($from, 1000, $orderby, $direction);
  835. $csv_content = array();
  836. // the first line of the csv file with the column headers
  837. $csv_row = array();
  838. $csv_row[] = get_lang('Session');
  839. $csv_row[] = get_lang('Course', '');
  840. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  841. $csv_row[] = get_lang('AvgStudentsProgress', '');
  842. $csv_row[] = get_lang('AvgCourseScore', '');
  843. $csv_row[] = get_lang('TotalNumberOfMessages', '');
  844. $csv_row[] = get_lang('TotalNumberOfAssignments', '');
  845. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  846. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  847. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  848. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  849. $csv_row[] = get_lang('LatestLogin', '');
  850. $csv_content[] = $csv_row;
  851. // the other lines (the data)
  852. foreach ($session_data as $key => $session) {
  853. $session_id = $session[0];
  854. $session_title = $session[1];
  855. // getting all the courses of the session
  856. $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."';";
  857. $result = Database::query($sql);
  858. while ($row = Database::fetch_object($result)) {
  859. $csv_row = array();
  860. $csv_row[] = $session_title;
  861. $csv_row[] = $row->title;
  862. // get the users in the course
  863. $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."';";
  864. $result_users = Database::query($sql);
  865. $time_spent = 0;
  866. $progress = 0;
  867. $nb_progress_lp = 0;
  868. $score = 0;
  869. $nb_score_lp = 0;
  870. $nb_messages = 0;
  871. $nb_assignments = 0;
  872. $last_login_date = false;
  873. $total_score_obtained = 0;
  874. $total_score_possible = 0;
  875. $total_questions_answered = 0;
  876. while($row_user = Database::fetch_object($result_users)) {
  877. // get time spent in the course and session
  878. $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $row->code, $session_id);
  879. $progress_tmp = Tracking::get_avg_student_progress($row_user->user_id, $row->code, array(), $session_id, true);
  880. $progress += $progress_tmp[0];
  881. $nb_progress_lp += $progress_tmp[1];
  882. $score_tmp = Tracking :: get_avg_student_score($row_user->user_id, $row->code, array(), $session_id, true);
  883. if(is_array($score_tmp)) {
  884. $score += $score_tmp[0];
  885. $nb_score_lp += $score_tmp[1];
  886. }
  887. $nb_messages += Tracking::count_student_messages($row_user->user_id, $row->code, $session_id);
  888. $nb_assignments += Tracking::count_student_assignments($row_user->user_id, $row->code, $session_id);
  889. $last_login_date_tmp = Tracking :: get_last_connection_date_on_the_course ($row_user->user_id, $row->code, $session_id, false);
  890. if($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned.
  891. $last_login_date = $last_login_date_tmp;
  892. } else if($last_login_date_tmp != false && $last_login_date == false) { // TODO: Repeated previous condition. To be cleaned.
  893. // Find the max and assign it to first_login_date
  894. if(strtotime($last_login_date_tmp) > strtotime($last_login_date)) {
  895. $last_login_date = $last_login_date_tmp;
  896. }
  897. }
  898. $exercise_results_tmp = MySpace::exercises_results($row_user->user_id, $row->code, $session_id);
  899. $total_score_obtained += $exercise_results_tmp['score_obtained'];
  900. $total_score_possible += $exercise_results_tmp['score_possible'];
  901. $total_questions_answered += $exercise_results_tmp['questions_answered'];
  902. }
  903. if($nb_progress_lp > 0) {
  904. $avg_progress = round($progress / $nb_progress_lp, 2);
  905. } else {
  906. $avg_progress = 0;
  907. }
  908. if($nb_score_lp > 0) {
  909. $avg_score = round($score / $nb_score_lp, 2);
  910. } else {
  911. $avg_score = '-';
  912. }
  913. if($last_login_date) {
  914. $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get());
  915. } else {
  916. $last_login_date = '-';
  917. }
  918. if($total_score_possible > 0) {
  919. $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2);
  920. } else {
  921. $total_score_percentage = 0;
  922. }
  923. if($total_score_percentage > 0) {
  924. $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)';
  925. } else {
  926. $total_score = '-';
  927. }
  928. // time spent in the course
  929. $csv_row[] = api_time_to_hms($time_spent);
  930. // student progress in course
  931. $csv_row[] = $avg_progress;
  932. // student score
  933. $csv_row[] = $avg_score;
  934. // student messages
  935. $csv_row[] = $nb_messages;
  936. // student assignments
  937. $csv_row[] = $nb_assignments;
  938. // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
  939. $csv_row[] = $total_score_obtained;
  940. $csv_row[] = $total_score_possible;
  941. $csv_row[] = $total_questions_answered;
  942. $csv_row[] = $total_score_percentage;
  943. // last connection
  944. $csv_row[] = $last_login_date;
  945. $csv_content[] = $csv_row;
  946. }
  947. }
  948. Export :: export_table_csv($csv_content, 'reporting_session_overview');
  949. exit;
  950. }
  951. /**
  952. * Get general information about the exercise performance of the user
  953. * the total obtained score (all the score on all the questions)
  954. * the maximum score that could be obtained
  955. * the number of questions answered
  956. * the success percentage
  957. * @param integer $user_id the id of the user
  958. * @param string $course_code the course code
  959. * @return array
  960. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  961. * @version Dokeos 1.8.6
  962. * @since November 2008
  963. */
  964. function exercises_results($user_id, $course_code, $session_id = false) {
  965. $questions_answered = 0;
  966. $sql = 'SELECT exe_result , exe_weighting
  967. FROM '.Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES)."
  968. WHERE exe_cours_id = '".Database::escape_string($course_code)."'
  969. AND exe_user_id = '".Database::escape_string($user_id)."'";
  970. if($session_id !== false) {
  971. $sql .= " AND session_id = '".$session_id."' ";
  972. }
  973. $result = Database::query($sql);
  974. $score_obtained = 0;
  975. $score_possible = 0;
  976. $questions_answered = 0;
  977. while ($row = Database::fetch_array($result)) {
  978. $score_obtained += $row['exe_result'];
  979. $score_possible += $row['exe_weighting'];
  980. $questions_answered ++;
  981. }
  982. if ($score_possible != 0) {
  983. $percentage = round(($score_obtained / $score_possible * 100), 2);
  984. } else {
  985. $percentage = null;
  986. }
  987. return array('score_obtained' => $score_obtained, 'score_possible' => $score_possible, 'questions_answered' => $questions_answered, 'percentage' => $percentage);
  988. }
  989. /**
  990. * This function exports the table that we see in display_tracking_user_overview()
  991. *
  992. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  993. * @version Dokeos 1.8.6
  994. * @since October 2008
  995. */
  996. function export_tracking_user_overview() {
  997. // database table definitions
  998. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  999. $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
  1000. $sort_by_first_name = api_sort_by_first_name();
  1001. // the values of the sortable table
  1002. if ($_GET['tracking_user_overview_page_nr']) {
  1003. $from = $_GET['tracking_user_overview_page_nr'];
  1004. } else {
  1005. $from = 0;
  1006. }
  1007. if ($_GET['tracking_user_overview_column']) {
  1008. $orderby = $_GET['tracking_user_overview_column'];
  1009. } else {
  1010. $orderby = 0;
  1011. }
  1012. if ($is_western_name_order != api_is_western_name_order() && ($orderby == 1 || $orderby == 2)) {
  1013. // Swapping the sorting column if name order for export is different than the common name order.
  1014. $orderby = 3 - $orderby;
  1015. }
  1016. if ($_GET['tracking_user_overview_direction']) {
  1017. $direction = $_GET['tracking_user_overview_direction'];
  1018. } else {
  1019. $direction = 'ASC';
  1020. }
  1021. $user_data = MySpace::get_user_data_tracking_overview($from, 1000, $orderby, $direction);
  1022. // the first line of the csv file with the column headers
  1023. $csv_row = array();
  1024. $csv_row[] = get_lang('OfficialCode');
  1025. if ($is_western_name_order) {
  1026. $csv_row[] = get_lang('FirstName', '');
  1027. $csv_row[] = get_lang('LastName', '');
  1028. } else {
  1029. $csv_row[] = get_lang('LastName', '');
  1030. $csv_row[] = get_lang('FirstName', '');
  1031. }
  1032. $csv_row[] = get_lang('LoginName');
  1033. $csv_row[] = get_lang('CourseCode');
  1034. // the additional user defined fields (only those that were selected to be exported)
  1035. $fields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
  1036. if (is_array($_SESSION['additional_export_fields'])) {
  1037. foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
  1038. $csv_row[] = $fields[$extra_field_export][3];
  1039. $field_names_to_be_exported[] = 'extra_'.$fields[$extra_field_export][1];
  1040. }
  1041. }
  1042. $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
  1043. $csv_row[] = get_lang('AvgStudentsProgress', '');
  1044. $csv_row[] = get_lang('AvgCourseScore', '');
  1045. $csv_row[] = get_lang('AvgExercisesScore', '');
  1046. $csv_row[] = get_lang('AvgMessages', '');
  1047. $csv_row[] = get_lang('AvgAssignments', '');
  1048. $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
  1049. $csv_row[] = get_lang('TotalExercisesScorePossible', '');
  1050. $csv_row[] = get_lang('TotalExercisesAnswered', '');
  1051. $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
  1052. $csv_row[] = get_lang('FirstLogin', '');
  1053. $csv_row[] = get_lang('LatestLogin', '');
  1054. $csv_content[] = $csv_row;
  1055. // the other lines (the data)
  1056. foreach ($user_data as $key => $user) {
  1057. // getting all the courses of the user
  1058. $sql = "SELECT * FROM $tbl_course_user WHERE user_id = '".Database::escape_string($user[4])."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." ";
  1059. $result = Database::query($sql);
  1060. while ($row = Database::fetch_row($result)) {
  1061. $csv_row = array();
  1062. // user official code
  1063. $csv_row[] = $user[0];
  1064. // user first|last name
  1065. $csv_row[] = $user[1];
  1066. // user last|first name
  1067. $csv_row[] = $user[2];
  1068. // user login name
  1069. $csv_row[] = $user[3];
  1070. // course code
  1071. $csv_row[] = $row[0];
  1072. // the additional defined user fields
  1073. $extra_fields = MySpace::get_user_overview_export_extra_fields($user[4]);
  1074. if (is_array($field_names_to_be_exported)) {
  1075. foreach ($field_names_to_be_exported as $key => $extra_field_export) {
  1076. $csv_row[] = $extra_fields[$extra_field_export];
  1077. }
  1078. }
  1079. // time spent in the course
  1080. $csv_row[] = api_time_to_hms(Tracking :: get_time_spent_on_the_course ($user[4], $row[0]));
  1081. // student progress in course
  1082. $csv_row[] = round(Tracking :: get_avg_student_progress ($user[4], $row[0]), 2);
  1083. // student score
  1084. $csv_row[] = round(Tracking :: get_avg_student_score ($user[4], $row[0]), 2);
  1085. // student tes score
  1086. $csv_row[] = round(Tracking :: get_avg_student_exercise_score ($user[4], $row[0]), 2);
  1087. // student messages
  1088. $csv_row[] = Tracking :: count_student_messages ($user[4], $row[0]);
  1089. // student assignments
  1090. $csv_row[] = Tracking :: count_student_assignments ($user[4], $row[0]);
  1091. // student exercises results
  1092. $exercises_results = MySpace::exercises_results($user[4], $row[0]);
  1093. $csv_row[] = $exercises_results['score_obtained'];
  1094. $csv_row[] = $exercises_results['score_possible'];
  1095. $csv_row[] = $exercises_results['questions_answered'];
  1096. $csv_row[] = $exercises_results['percentage'];
  1097. // first connection
  1098. $csv_row[] = Tracking :: get_first_connection_date_on_the_course ($user[4], $row[0]);
  1099. // last connection
  1100. $csv_row[] = strip_tags(Tracking :: get_last_connection_date_on_the_course ($user[4], $row[0]));
  1101. $csv_content[] = $csv_row;
  1102. }
  1103. }
  1104. Export :: export_table_csv($csv_content, 'reporting_user_overview');
  1105. exit;
  1106. }
  1107. /**
  1108. * Get data for courses list in sortable with pagination
  1109. * @return array
  1110. */
  1111. function get_course_data($from, $number_of_items, $column, $direction) {
  1112. global $courses, $csv_content, $charset, $session_id;
  1113. // definition database tables
  1114. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  1115. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1116. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1117. $a_course_students = array();
  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.'&studentlist=true&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, 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. require_once api_get_path(LIBRARY_PATH).'pchart/pData.class.php';
  1779. require_once api_get_path(LIBRARY_PATH).'pchart/pChart.class.php';
  1780. require_once api_get_path(LIBRARY_PATH).'pchart/pCache.class.php';
  1781. if (empty($start_date)) { $start_date =""; }
  1782. if (empty($end_date)) { $end_date =""; }
  1783. if ($type == ""){ $type = 'day'; }
  1784. $main_year = $main_month_year = $main_day = array();
  1785. // get last 8 days/months
  1786. $last_days = 5;
  1787. $last_months = 3;
  1788. for ($i = $last_days; $i >= 0; $i--) {
  1789. $main_day[date ('d-m-Y', mktime () - $i * 3600 * 24)] = 0;
  1790. }
  1791. for ($i = $last_months; $i >= 0; $i--) {
  1792. $main_month_year[date ('m-Y', mktime () - $i * 30 * 3600 * 24)] = 0;
  1793. }
  1794. $i = 0;
  1795. if (is_array($sql_result) && count($sql_result) > 0) {
  1796. foreach ($sql_result as $key => $data) {
  1797. //creating the main array
  1798. $main_month_year[date('m-Y', $data['login'])] += float_format(($data['logout'] - $data['login']) / 60, 0);
  1799. $main_day[date('d-m-Y', $data['login'])] += float_format(($data['logout'] - $data['login']) / 60, 0);
  1800. if ($i > 500) {
  1801. break;
  1802. }
  1803. $i++;
  1804. }
  1805. switch ($type) {
  1806. case 'day':
  1807. $main_date = $main_day;
  1808. break;
  1809. case 'month':
  1810. $main_date = $main_month_year;
  1811. break;
  1812. case 'year':
  1813. $main_date = $main_year;
  1814. break;
  1815. }
  1816. // the nice graphics :D
  1817. $labels = array_keys($main_date);
  1818. if (count($main_date) == 1) {
  1819. $labels = $labels[0];
  1820. $main_date = $main_date[$labels];
  1821. }
  1822. $data_set = new pData();
  1823. $data_set->AddPoint($main_date, 'Q');
  1824. if (count($main_date)!= 1) {
  1825. $data_set->AddPoint($labels, 'Date');
  1826. }
  1827. $data_set->AddAllSeries();
  1828. $data_set->RemoveSerie('Date');
  1829. $data_set->SetAbsciseLabelSerie('Date');
  1830. $data_set->SetYAxisName(get_lang('Minutes', ''));
  1831. $graph_id = api_get_user_id().'AccessDetails'.api_get_course_id().$start_date.$end_date.$type;
  1832. $data_set->AddAllSeries();
  1833. $cache = new pCache();
  1834. // the graph id
  1835. $data = $data_set->GetData();
  1836. if ($cache->IsInCache($graph_id, $data_set->GetData())) {
  1837. //if (0) {
  1838. //if we already created the img
  1839. // echo 'in cache';
  1840. $img_file = $cache->GetHash($graph_id, $data_set->GetData());
  1841. } else {
  1842. // if the image does not exist in the archive/ folder
  1843. // Initialise the graph
  1844. $test = new pChart(760, 230);
  1845. //which schema of color will be used
  1846. $quant_resources = count($data[0]) - 1;
  1847. // Adding the color schemma
  1848. $test->loadColorPalette(api_get_path(LIBRARY_PATH).'pchart/palette/default.txt');
  1849. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 8);
  1850. $test->setGraphArea(70, 30, 680, 200);
  1851. $test->drawFilledRoundedRectangle(7, 7, 693, 223, 5, 240, 240, 240);
  1852. $test->drawRoundedRectangle(5, 5, 695, 225, 5, 230, 230, 230);
  1853. $test->drawGraphArea(255, 255, 255, TRUE);
  1854. $test->drawScale($data_set->GetData(), $data_set->GetDataDescription(), SCALE_START0, 150, 150, 150, TRUE, 0, 0);
  1855. $test->drawGrid(4, TRUE, 230, 230, 230, 50);
  1856. $test->setLineStyle(2);
  1857. // Draw the 0 line
  1858. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 6);
  1859. $test->drawTreshold(0, 143, 55, 72, TRUE, TRUE);
  1860. if (count($main_date) == 1) {
  1861. //Draw a graph
  1862. echo '<strong>'.$labels.'</strong><br/>';
  1863. $test->drawBarGraph($data_set->GetData(), $data_set->GetDataDescription(), TRUE);
  1864. } else {
  1865. //Draw the line graph
  1866. $test->drawLineGraph($data_set->GetData(), $data_set->GetDataDescription());
  1867. $test->drawPlotGraph($data_set->GetData(), $data_set->GetDataDescription(), 3, 2, 255, 255, 255);
  1868. }
  1869. // Finish the graph
  1870. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 8);
  1871. $test->setFontProperties(api_get_path(LIBRARY_PATH).'pchart/fonts/tahoma.ttf', 10);
  1872. $test->drawTitle(60, 22, get_lang('AccessDetails'), 50, 50, 50, 585);
  1873. //------------------
  1874. //echo 'not in cache';
  1875. $cache->WriteToCache($graph_id, $data_set->GetData(), $test);
  1876. ob_start();
  1877. $test->Stroke();
  1878. ob_end_clean();
  1879. $img_file = $cache->GetHash($graph_id, $data_set->GetData());
  1880. }
  1881. $foo_img = '<img src="'.api_get_path(WEB_ARCHIVE_PATH).$img_file.'">';
  1882. return $foo_img;
  1883. } else {
  1884. $foo_img = api_convert_encoding('<div id="messages" class="warning-message">'.get_lang('GraphicNotAvailable').'</div>','UTF-8');
  1885. return $foo_img;
  1886. }
  1887. }