Dokeos PHP Cross Reference Learning Management Systems

Source: /main/inc/lib/tracking.lib.php - 2235 lines - 91127 bytes - Summary - Text - Print

   1  <?php
   2  // $Id: tracking.lib.php 2007-28-02 15:51:53
   3  /*
   4  ==============================================================================
   5      Dokeos - elearning and course management software
   6  
   7      Copyright (c) 2004-2008 Dokeos SPRL
   8      Copyright (c) 2003 Ghent University (UGent)
   9      Copyright (c) 2001 Universite catholique de Louvain (UCL)
  10      Copyright (c) various contributors
  11  
  12      For a full list of contributors, see "credits.txt".
  13      The full license can be read in "license.txt".
  14  
  15      This program is free software; you can redistribute it and/or
  16      modify it under the terms of the GNU General Public License
  17      as published by the Free Software Foundation; either version 2
  18      of the License, or (at your option) any later version.
  19  
  20      See the GNU General Public License for more details.
  21  
  22      Contact address: Dokeos, rue du Corbeau, 108, B-1030 Brussels, Belgium
  23      Mail: info@dokeos.com
  24  
  25  ==============================================================================
  26  */
  27  /**
  28  ==============================================================================
  29  *    This is the tracking library for Dokeos.
  30  *    Include/require it in your code to use its functionality.
  31  *
  32  *    @package dokeos.library
  33  *    @author Julio Montoya <gugli100@gmail.com> (Score average fixes)
  34  ==============================================================================
  35  */
  36  
  37  class Tracking {
  38  
  39      /**
  40       * Calculates the time spent on the platform by a user
  41       * @param integer $user_id the user id
  42       * @return timestamp $nb_seconds
  43       */
  44  	function get_time_spent_on_the_platform($user_id) {
  45  
  46          $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
  47  
  48          $sql = 'SELECT login_date, logout_date FROM ' . $tbl_track_login . '
  49                          WHERE login_user_id = ' . intval($user_id);
  50  
  51          $rs = Database::query($sql,__FILE__,__LINE__);
  52  
  53          $nb_seconds = 0;
  54  
  55          $wrong_logout_dates = false;
  56  
  57          while ($a_connections = Database::fetch_array($rs)) {
  58  
  59              $s_login_date = $a_connections["login_date"];
  60              $s_logout_date = $a_connections["logout_date"];
  61  
  62              $i_timestamp_login_date = strtotime($s_login_date);
  63              $i_timestamp_logout_date = strtotime($s_logout_date);
  64  
  65              if($i_timestamp_logout_date>0)
  66              {
  67                  $nb_seconds += ($i_timestamp_logout_date - $i_timestamp_login_date);
  68              }
  69              else
  70              { // there are wrong datas in db, then we can't give a wrong time
  71                  $wrong_logout_dates = true;
  72              }
  73  
  74          }
  75  
  76          if($nb_seconds>0 || !$wrong_logout_dates)
  77          {
  78              return $nb_seconds;
  79          }
  80          else
  81          {
  82              return -1; //-1 means we have wrong datas in the db
  83          }
  84      }
  85  
  86      /**
  87       * Calculates the time spent on the course
  88       * @param integer $user_id the user id
  89       * @param string $course_code the course code
  90       * @return timestamp $nb_seconds
  91       */
  92  	function get_time_spent_on_the_course($user_id, $course_code) {
  93          // protect datas
  94          $user_id = intval($user_id);
  95          $course_code = addslashes($course_code);        
  96          $tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  97          $condition_user = "";
  98          if (is_array($user_id)) {
  99              $condition_user = " AND user_id IN (".implode(',',$user_id).") ";
 100          } else {
 101              $condition_user = " AND user_id = '$user_id' ";
 102          }                
 103          $sql = " SELECT SUM(UNIX_TIMESTAMP(logout_course_date)-UNIX_TIMESTAMP(login_course_date)) as nb_seconds 
 104                  FROM $tbl_track_course
 105                  WHERE course_code='$course_code' $condition_user";
 106          $rs = Database::query($sql,__FILE__,__LINE__);
 107          $row = Database::fetch_array($rs);                
 108          return $row['nb_seconds']; 
 109      }
 110  
 111  	function get_first_connection_date($student_id) {
 112          $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
 113          $sql = 'SELECT login_date FROM ' . $tbl_track_login . '
 114                          WHERE login_user_id = ' . intval($student_id) . '
 115                          ORDER BY login_date ASC LIMIT 0,1';
 116  
 117          $rs = Database::query($sql,__FILE__,__LINE__);
 118          if(Database::num_rows($rs)>0)
 119          {
 120              if ($first_login_date = Database::result($rs, 0, 0)) {
 121                  return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($first_login_date));
 122              }
 123          }
 124          return false;
 125      }
 126  
 127  	function get_last_connection_date($student_id, $warning_message = false, $return_timestamp = false) {
 128          $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
 129          $sql = 'SELECT login_date FROM ' . $tbl_track_login . '
 130                          WHERE login_user_id = ' . intval($student_id) . '
 131                          ORDER BY login_date DESC LIMIT 0,1';
 132  
 133          $rs = Database::query($sql,__FILE__,__LINE__);
 134          if(Database::num_rows($rs)>0)
 135          {
 136              if ($last_login_date = Database::result($rs, 0, 0))
 137              {
 138                  if ($return_timestamp)
 139                  {
 140                      return strtotime($last_login_date);
 141                  }
 142                  else
 143                  {
 144                      if (!$warning_message)
 145                      {
 146                          return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date));
 147                      }
 148                      else
 149                      {
 150                          $timestamp = strtotime($last_login_date);
 151                          $currentTimestamp = mktime();
 152  
 153                          //If the last connection is > than 7 days, the text is red
 154                          //345600 = 7 days in seconds
 155                          if ($currentTimestamp - $timestamp > 604800)
 156                          {
 157                              return '<span style="color: #F00;">' . format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date)) . '</span>';
 158                          }
 159                          else
 160                          {
 161                              return format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_login_date));
 162                          }
 163                      }
 164                  }
 165              }
 166          }
 167          return false;
 168      }
 169  
 170  	function get_first_connection_date_on_the_course($student_id, $course_code) {
 171          $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
 172          $sql = 'SELECT login_course_date FROM ' . $tbl_track_login . '
 173                          WHERE user_id = ' . intval($student_id) . '
 174                          AND course_code = "' . Database::escape_string($course_code) . '"
 175                          ORDER BY login_course_date ASC LIMIT 0,1';
 176  
 177          $rs = Database::query($sql,__FILE__,__LINE__);
 178          if(Database::num_rows($rs)>0)
 179          {
 180              if ($first_login_date = Database::result($rs, 0, 0)) {
 181                  return format_locale_date(get_lang('dateFormatShortWithLongYear'), strtotime($first_login_date));
 182              }
 183          }
 184          return false;
 185      }
 186  
 187  	function get_last_connection_date_on_the_course($student_id, $course_code) {
 188          $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
 189          $sql = 'SELECT login_course_date FROM ' . $tbl_track_login . '
 190                          WHERE user_id = ' . intval($student_id) . '
 191                          AND course_code = "' . Database::escape_string($course_code) . '"
 192                          ORDER BY login_course_date DESC LIMIT 0,1';
 193  
 194          $rs = Database::query($sql,__FILE__,__LINE__);
 195          if(Database::num_rows($rs)>0)
 196          {
 197              if ($last_login_date = Database::result($rs, 0, 0)) {
 198                  $timestamp = strtotime($last_login_date);
 199                  $currentTimestamp = mktime();
 200                  //If the last connection is > than 7 days, the text is red
 201                  //345600 = 7 days in seconds
 202                  if ($currentTimestamp - $timestamp > 604800) {
 203                      return format_locale_date(get_lang('dateFormatShortWithLongYear'), strtotime($last_login_date)) . (api_is_allowed_to_edit()?' <a href="'.api_get_path(REL_CODE_PATH).'announcements/announcements.php?action=add&remind_inactive='.$student_id.'" title="'.get_lang('RemindInactiveUser').'"><img align="middle" src="'.api_get_path(WEB_IMG_PATH).'messagebox_warning.gif" /></a>':'');
 204                  } else {
 205                      return format_locale_date(get_lang('dateFormatShortWithLongYear'), strtotime($last_login_date));
 206                  }
 207              }
 208          }
 209          return false;
 210      }
 211  
 212  	function count_course_per_student($user_id) {
 213  
 214          $user_id = intval($user_id);
 215          $tbl_course_rel_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
 216          $tbl_session_course_rel_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
 217  
 218          $sql = 'SELECT DISTINCT course_code
 219                          FROM ' . $tbl_course_rel_user . '
 220                          WHERE user_id = ' . $user_id;
 221          $rs = Database::query($sql, __FILE__, __LINE__);
 222          $nb_courses = Database::num_rows($rs);
 223  
 224          $sql = 'SELECT DISTINCT course_code
 225                          FROM ' . $tbl_session_course_rel_user . '
 226                          WHERE id_user = ' . $user_id;
 227          $rs = Database::query($sql, __FILE__, __LINE__);
 228          $nb_courses += Database::num_rows($rs);
 229  
 230          return $nb_courses;
 231      }
 232  
 233      /**
 234       * This function gets the score average from all tests in a course by student
 235       * @param int $student_id - or array for multiples User id (array(0=>1,1=>2))
 236       * @param string $course_code - Course id
 237       * @return string value (number %) Which represents a round integer about the score average.
 238       */
 239  	function get_avg_student_exercise_score($student_id, $course_code) {
 240  
 241          // protect datas
 242          $course_code = Database::escape_string($course_code);
 243          // get the informations of the course
 244          $a_course = CourseManager :: get_course_information($course_code);
 245          if(!empty($a_course['db_name'])) {
 246              // table definition
 247              $tbl_course_quiz = Database::get_course_table(TABLE_QUIZ_TEST,$a_course['db_name']);
 248              $tbl_stats_exercise = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);            
 249              $count_quiz = Database::fetch_row(Database::query("SELECT count(id) FROM $tbl_course_quiz WHERE active <> -1",__FILE__,__LINE__));
 250              $quiz_avg_total_score = 0;
 251              if (!empty($count_quiz[0]) && !empty($student_id)) {                
 252                  $condition_user = "";
 253                  if (is_array($student_id)) {
 254                      $condition_user = " AND exe_user_id IN (".implode(',',$student_id).") ";
 255                  } else {
 256                      $condition_user = " AND exe_user_id = '$student_id' ";
 257                  }
 258                  $sql = "SELECT SUM(exe_result/exe_weighting*100) as avg_score 
 259                          FROM $tbl_stats_exercise
 260                          WHERE exe_exo_id IN (SELECT id FROM $tbl_course_quiz WHERE active <> -1) 
 261                          $condition_user
 262                          AND orig_lp_id = 0
 263                          AND exe_cours_id = '$course_code' 
 264                          AND orig_lp_item_id = 0
 265                          ORDER BY exe_date DESC";                
 266                  $res = Database::query($sql, __FILE__, __LINE__);
 267                  $row = Database::fetch_array($res);                
 268                  $quiz_avg_score = 0;
 269                  if (!empty($row['avg_score'])) {
 270                      $quiz_avg_score = round($row['avg_score'],2);
 271                  }                                
 272                  $count_attempt = Database::fetch_row(Database::query("SELECT count(*) FROM $tbl_stats_exercise WHERE exe_exo_id IN (SELECT id FROM $tbl_course_quiz WHERE active <> -1) $condition_user AND orig_lp_id = 0 AND exe_cours_id = '$course_code' AND orig_lp_item_id = 0 ORDER BY exe_date DESC",__FILE__,__LINE__));                                                                
 273                  if(!empty($count_attempt[0])) {
 274                      $quiz_avg_score = $quiz_avg_score / $count_attempt[0];
 275                  }
 276                  $quiz_avg_total_score = $quiz_avg_score;                
 277                  return $quiz_avg_total_score/$count_quiz[0];                
 278              }         
 279          }        
 280          return null;        
 281      }
 282  
 283  	function get_avg_student_progress($student_id, $course_code) {        
 284          // protect datas
 285          $course_code = addslashes($course_code);
 286          // get the informations of the course
 287          $a_course = CourseManager :: get_course_information($course_code);
 288          if (!empty($a_course['db_name'])) {
 289              // table definition
 290              $tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW, $a_course['db_name']);    
 291              $tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN, $a_course['db_name']);        
 292              $count_lp = Database::fetch_row(Database::query("SELECT count(id) FROM $tbl_course_lp",__FILE__,__LINE__));
 293              $avg_progress = 0;
 294              if (!empty($count_lp[0]) && !empty($student_id)) {                
 295                  $condition_user = "";
 296                  if (is_array($student_id)) {            
 297                      $condition_user = " lp_view.user_id IN (".implode(',',$student_id).") AND ";                
 298                  } else {
 299                      $condition_user = " lp_view.user_id = '$student_id' AND ";
 300                  }                                
 301                  $sqlProgress = "SELECT SUM(progress) FROM $tbl_course_lp_view AS lp_view WHERE $condition_user lp_view.lp_id IN (SELECT id FROM $tbl_course_lp)";                
 302                  $resultItem  = Database::query($sqlProgress, __FILE__, __LINE__);
 303                  $rowItem = Database::fetch_row($resultItem);                
 304                  $avg_progress = round($rowItem[0] / $count_lp[0], 1);                
 305                  return $avg_progress;
 306              } 
 307          }
 308          return null;
 309      }
 310      
 311          /**
 312           * This function gets:
 313           * 1. The score average from all SCORM Test items in all LP in a course-> All the answers / All the max scores.
 314           * 2. The score average from all Tests (quiz) in all LP in a course-> All the answers / All the max scores.
 315           * 3. And finally it will return the average between 1. and 2.
 316           * This function does not take the results of a Test out of a LP
 317           *
 318           * @param   mixed       Array of user ids or an user id
 319           * @param   string      Course code
 320           * @param   array       List of LP ids
 321           * @param   int         Session id (optional), if param $session_id is null(default) it'll return results including sessions, 0 = session is not filtered
 322           * @param   bool        Returns an array of the type [sum_score, num_score] if set to true
 323           * @param   bool        get only the latest attempts or ALL attempts
 324           * @return  string      Value (number %) Which represents a round integer explain in got in 3.
 325           */
 326  	function get_avg_student_score($student_id, $course_code, $lp_ids = array(), $session_id = null, $return_array = false, $get_only_latest_attempt_results = false){
 327              $debug = false;
 328              if (empty($lp_ids)) {
 329                  $debug = false;
 330              }
 331  
 332              if ($debug) echo '<h1>Tracking::get_avg_student_score</h1>';
 333              // get global tables names
 334              $course_table               = Database :: get_main_table(TABLE_MAIN_COURSE);
 335              $course_user_table          = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
 336              $table_session_course_user  = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
 337              $tbl_stats_exercices        = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
 338              $tbl_stats_attempts         = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
 339  
 340              $course = CourseManager :: get_course_information($course_code);
 341  
 342              if (!empty($course['db_name'])) {
 343  
 344                  // get course tables names
 345                  $tbl_quiz_questions = Database :: get_course_table(TABLE_QUIZ_QUESTION,$course['db_name']);
 346                  $lp_table           = Database :: get_course_table(TABLE_LP_MAIN,$course['db_name']);
 347                  $lp_item_table      = Database :: get_course_table(TABLE_LP_ITEM,$course['db_name']);
 348                  $lp_view_table      = Database :: get_course_table(TABLE_LP_VIEW,$course['db_name']);
 349                  $lp_item_view_table = Database :: get_course_table(TABLE_LP_ITEM_VIEW,$course['db_name']);
 350  
 351                  // Compose a filter based on optional learning paths list given
 352  
 353                  $condition_lp = "";
 354                  if (count($lp_ids) > 0) {
 355                      $condition_lp =" AND id IN(".implode(',',$lp_ids).") ";
 356                  }
 357  
 358                  // Compose a filter based on optional session id
 359                  $condition_session = "";
 360  
 361                  $session_id = intval($session_id);
 362                  if (count($lp_ids) > 0) {
 363                      $condition_session = " AND session_id = $session_id ";
 364                  } else {
 365                      $condition_session = " WHERE session_id = $session_id ";
 366                  }
 367  
 368                  // Check the real number of LPs corresponding to the filter in the
 369                  // database (and if no list was given, get them all)
 370  
 371                  if (empty($session_id)) {
 372                      $sql = "SELECT DISTINCT(id) FROM $lp_table  WHERE session_id = 0 $condition_lp ";
 373                  } else {
 374                      $sql = "SELECT DISTINCT(id) FROM $lp_table WHERE 1 $condition_lp ";
 375                  }
 376  
 377                  $res_row_lp   = Database::query($sql);
 378                  $count_row_lp = Database::num_rows($res_row_lp);
 379  
 380                  $lp_list = $use_max_score = array();
 381                  while ($row_lp = Database::fetch_array($res_row_lp)) {
 382                      $lp_list[]                     = $row_lp['id'];
 383                      $use_max_score[$row_lp['id']]  = 1;
 384                  }
 385  
 386                  if ($debug) {
 387                      echo 'Use max score or not list '; var_dump($use_max_score);
 388                  }
 389  
 390                  // Init local variables that will be used through the calculation
 391                  $progress = 0;
 392  
 393                  // prepare filter on users
 394                  $condition_user1 = "";
 395  
 396                  if (is_array($student_id)) {
 397                      array_walk($student_id, 'intval');
 398                      $condition_user1 =" AND user_id IN (".implode(',', $student_id).") ";
 399                  } else {
 400                      $condition_user1 =" AND user_id = $student_id ";
 401                  }
 402  
 403                  if ($count_row_lp > 0 && !empty($student_id)) {
 404  
 405                      // Getting latest LP result for a student
 406                      //@todo problem when a  course have more than 1500 users
 407                      $sql = "SELECT MAX(view_count) as vc, id, progress, lp_id, user_id  FROM $lp_view_table
 408                              WHERE lp_id IN (".implode(',',$lp_list).") $condition_user1  GROUP BY lp_id, user_id";
 409                      if ($debug) echo $sql;
 410                      $rs_last_lp_view_id = Database::query($sql);
 411  
 412                      $global_result = 0;
 413  
 414                      if (Database::num_rows($rs_last_lp_view_id) > 0) {
 415                          // Cycle through each line of the results (grouped by lp_id, user_id)
 416                          $exe_list = array();
 417                          while ($row_lp_view = Database::fetch_array($rs_last_lp_view_id)) {
 418                              $count_items = 0;
 419                              $lp_partial_total = 0;
 420  
 421                              $list = array();
 422                              $lp_view_id = $row_lp_view['id'];
 423                              $progress   = $row_lp_view['progress'];
 424                              $lp_id      = $row_lp_view['lp_id'];
 425                              $user_id    = $row_lp_view['user_id'];
 426                              if ($debug) echo '<h2>LP id '.$lp_id.'</h2>';
 427  
 428                              if ($get_only_latest_attempt_results) {
 429                                  //if (1) {
 430                                  //Getting lp_items done by the user
 431                                  $sql  = "SELECT DISTINCT lp_item_id FROM $lp_item_view_table WHERE lp_view_id = $lp_view_id ORDER BY lp_item_id";
 432                                  $res_lp_item = Database::query($sql);
 433  
 434                                  while ($row_lp_item = Database::fetch_array($res_lp_item,'ASSOC')) {
 435                                      $my_lp_item_id = $row_lp_item['lp_item_id'];
 436  
 437                                      //Getting the most recent attempt
 438                                      $sql = "SELECT lp_iv.id as lp_item_view_id, lp_iv.score as score,lp_i.max_score, lp_iv.max_score as max_score_item_view, lp_i.path, lp_i.item_type, lp_i.id as iid
 439                                              FROM $lp_item_view_table as lp_iv INNER JOIN $lp_item_table as lp_i ON lp_i.id = lp_iv.lp_item_id AND (lp_i.item_type='sco' OR lp_i.item_type='".TOOL_QUIZ."') 
 440                                              WHERE lp_item_id = $my_lp_item_id AND lp_view_id = $lp_view_id ORDER BY view_count DESC LIMIT 1";
 441                                      $res_lp_item_result = Database::query($sql);
 442  
 443                                      while ($row_max_score = Database::fetch_array($res_lp_item_result,'ASSOC')) {
 444                                          $list[]= $row_max_score;
 445                                      }
 446                                  }
 447                              } else {
 448                                  // For the currently analysed view, get the score and
 449                                  // max_score of each item if it is a sco or a TOOL_QUIZ
 450                                  $sql_max_score = "SELECT lp_iv.id as lp_item_view_id, lp_iv.score as score,lp_i.max_score, lp_iv.max_score as max_score_item_view, lp_i.path, lp_i.item_type, lp_i.id as iid
 451                                                    FROM $lp_item_view_table as lp_iv INNER JOIN $lp_item_table as lp_i ON lp_i.id = lp_iv.lp_item_id AND (lp_i.item_type='sco' OR lp_i.item_type='".TOOL_QUIZ."') 
 452                                                    WHERE lp_view_id = $lp_view_id ";
 453                                  if ($debug) echo $sql_max_score.'<br />';
 454  
 455                                  $res_max_score = Database::query($sql_max_score);
 456                                   
 457                                  while ($row_max_score = Database::fetch_array($res_max_score,'ASSOC')) {
 458                                      $list[]= $row_max_score;
 459                                  }
 460                              }
 461                              $count_total_loop = 0;
 462  
 463                              // Go through each scorable element of this view
 464                               
 465                              $score_of_scorm_calculate = 0;
 466  
 467                              foreach ($list as $row_max_score) {
 468                                  $max_score              = $row_max_score['max_score'];  //Came from the original lp_item
 469                                  $max_score_item_view    = $row_max_score['max_score_item_view']; //Came from the lp_item_view
 470                                  $score                  = $row_max_score['score'];
 471  
 472                                  if ($debug) echo '<h3>Item Type: ' .$row_max_score['item_type'].'</h3>';
 473  
 474                                  if ($row_max_score['item_type'] == 'sco') {
 475                                      //var_dump($row_max_score);
 476                                      // Check if it is sco (easier to get max_score)
 477                                      //when there's no max score, we assume 100 as the max score, as the SCORM 1.2 says that the value should always be between 0 and 100.
 478                                      if ($max_score == 0 || is_null($max_score) || $max_score == '') {
 479                                          //Chamilo style
 480                                          if ($use_max_score[$lp_id]) {
 481                                              $max_score = 100;
 482                                          } else {
 483                                              //Overwrites max score = 100 to use the one that came in the lp_item_view see BT#1613
 484                                              $max_score = $max_score_item_view;
 485                                          }
 486                                      }
 487                                      //Avoid division by zero errors
 488                                      if (!empty($max_score)) {
 489                                          $lp_partial_total += $score/$max_score;
 490                                      }
 491                                      if ($debug) echo '<b>$lp_partial_total, $score, $max_score '.$lp_partial_total.' '.$score.' '.$max_score.'</b><br />';
 492                                  } else {
 493                                      // Case of a TOOL_QUIZ element
 494                                      $item_id    = $row_max_score['iid'];
 495                                      $item_path  = $row_max_score['path'];
 496                                      $lp_item_view_id  = $row_max_score['lp_item_view_id'];
 497  
 498                                      // Get last attempt to this exercise  through
 499                                      // the current lp for the current user
 500                                      $sql_last_attempt = "SELECT exe_id FROM $tbl_stats_exercices WHERE
 501                                              exe_exo_id           = '$item_path' AND 
 502                                              exe_user_id          = $user_id AND 
 503                                              orig_lp_item_id      = $item_id AND 
 504                                              exe_cours_id         = '$course_code'  AND
 505                                              session_id           = $session_id 
 506                                              ORDER BY exe_date DESC LIMIT 1";
 507                                      if ($debug) echo $sql_last_attempt .'<br />';
 508                                      $result_last_attempt = Database::query($sql_last_attempt);
 509                                      $num = Database :: num_rows($result_last_attempt);
 510                                      if ($num > 0 ) {
 511                                          $id_last_attempt = Database :: result($result_last_attempt, 0, 0);
 512                                          if ($debug) echo $id_last_attempt.'<br />';
 513                                          // Within the last attempt number tracking, get the sum of
 514                                          // the max_scores of all questions that it was
 515                                          // made of (we need to make this call dynamic
 516                                          // because of random questions selection)
 517                                          $sql = "SELECT SUM(t.ponderation) as maxscore FROM
 518                                                  ( SELECT distinct question_id, marks, ponderation FROM $tbl_stats_attempts AS at INNER JOIN  $tbl_quiz_questions AS q ON (q.id = at.question_id) 
 519                                                    WHERE exe_id ='$id_last_attempt' ) AS t";
 520                                          $res_max_score_bis = Database::query($sql);
 521                                          $row_max_score_bis = Database :: fetch_array($res_max_score_bis);
 522                                          if (!empty($row_max_score_bis['maxscore'])) {
 523                                              $max_score = $row_max_score_bis['maxscore'];
 524                                          }
 525                                          if (!empty($max_score)) {
 526                                              $lp_partial_total            += $score/$max_score;
 527                                          }
 528                                          if ($debug) echo '$lp_partial_total, $score, $max_score <b>'.$lp_partial_total.' '.$score.' '.$max_score.'</b><br />';
 529                                      }
 530                                  }
 531  
 532                                  if (in_array($row_max_score['item_type'], array('quiz','sco'))) {
 533                                      // Normal way
 534                                      if ($use_max_score[$lp_id]) {
 535                                          $count_items++;
 536                                      } else {
 537                                          if ($max_score != '') {
 538                                              $count_items++;
 539                                          }
 540                                      }
 541                                      if ($debug) echo '$count_items: '.$count_items;
 542                                  }
 543                              } //end for
 544  
 545                              $score_of_scorm_calculate += $count_items?(($lp_partial_total/$count_items)*100):0;
 546  
 547                              if ($debug) echo '<h3>$count_items '.$count_items.'</h3>';
 548                              if ($debug) echo '<h3>$score_of_scorm_calculate '.$score_of_scorm_calculate.'</h3>';
 549                               
 550                              // var_dump($score_of_scorm_calculate);
 551                              $global_result += $score_of_scorm_calculate;
 552                              if ($debug) echo '<h3>$global_result '.$global_result.'</h3>';
 553                          } // end while
 554                      }
 555  
 556                      $lp_with_quiz = 0;
 557                      if ($debug) var_dump($lp_list);
 558                      foreach($lp_list as $lp_id) {
 559                          //Check if LP have a score we asume that all SCO have an score
 560                          $sql = "SELECT count(id) as count FROM $lp_item_table WHERE (item_type = 'quiz' OR item_type = 'sco') AND lp_id = ".$lp_id;
 561                          if ($debug) echo $sql;
 562                          $result_have_quiz = Database::query($sql);
 563  
 564                          if (Database::num_rows($result_have_quiz) > 0 ) {
 565                              $row = Database::fetch_array($result_have_quiz,'ASSOC');
 566                              if (is_numeric($row['count']) && $row['count'] != 0) {
 567                                  $lp_with_quiz ++;
 568                              }
 569                          }
 570                      }
 571  
 572                      if ($debug) echo '<h3>$lp_with_quiz '.$lp_with_quiz.' </h3>';
 573                      if ($debug) echo '<h3>Final return</h3>';
 574  
 575                      if ($lp_with_quiz != 0 ) {
 576                          if (!$return_array) {
 577                              $score_of_scorm_calculate = round(($global_result/$lp_with_quiz),2);
 578                              if ($debug) var_dump($score_of_scorm_calculate);
 579                              if (empty($lp_ids)) {
 580                                  //$score_of_scorm_calculate = round($score_of_scorm_calculate/count($lp_list),2);
 581                                  if ($debug) echo '<h2>All lps fix: '.$score_of_scorm_calculate.'</h2>';
 582                              }
 583                              return $score_of_scorm_calculate;
 584                          } else {
 585                              if ($debug) var_dump($global_result, $lp_with_quiz);
 586                              return array($global_result, $lp_with_quiz);
 587                          }
 588                      } else {
 589                          return '';
 590                      }
 591                  }
 592              }
 593              return null;
 594                  
 595      }
 596  
 597      /**
 598       * gets the list of students followed by coach
 599       * @param integer $coach_id the id of the coach
 600       * @return Array the list of students
 601       */
 602  	function get_student_followed_by_coach($coach_id) {
 603          $coach_id = intval($coach_id);
 604  
 605          $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
 606          $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
 607          $tbl_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
 608          $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
 609  
 610          $a_students = array ();
 611  
 612          //////////////////////////////////////////////////////////////
 613          // At first, courses where $coach_id is coach of the course //
 614          //////////////////////////////////////////////////////////////                
 615          $sql = 'SELECT id_session, course_code FROM ' . $tbl_session_course_user . ' WHERE id_user=' . $coach_id.' AND status=2';
 616  
 617          global $_configuration;
 618          if ($_configuration['multiple_access_urls']==true) {
 619              $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
 620              $access_url_id = api_get_current_access_url_id();
 621              if ($access_url_id != -1) {
 622                  $sql = 'SELECT scu.id_session, scu.course_code
 623                          FROM ' . $tbl_session_course_user . ' scu INNER JOIN '.$tbl_session_rel_access_url.'  sru
 624                          ON (scu.id_session=sru.session_id)
 625                          WHERE scu.id_user=' . $coach_id.' AND scu.status=2 AND sru.access_url_id = '.$access_url_id;
 626              }
 627          }
 628  
 629          $result = Database::query($sql,__FILE__,__LINE__);
 630  
 631          while ($a_courses = Database::fetch_array($result)) {
 632  
 633              $course_code = $a_courses["course_code"];
 634              $id_session = $a_courses["id_session"];
 635  
 636              $sql = "SELECT distinct    srcru.id_user
 637                                  FROM $tbl_session_course_user AS srcru, $tbl_session_user sru
 638                                  WHERE srcru.id_user = sru.id_user AND srcru.id_session = sru.id_session AND srcru.course_code='$course_code' AND srcru.id_session='$id_session'";
 639  
 640              $rs = Database::query($sql,__FILE__,__LINE__);
 641  
 642              while ($row = Database::fetch_array($rs)) {
 643                  $a_students[$row['id_user']] = $row['id_user'];
 644              }
 645          }
 646  
 647          //////////////////////////////////////////////////////////////
 648          // Then, courses where $coach_id is coach of the session    //
 649          //////////////////////////////////////////////////////////////
 650  
 651          $sql = 'SELECT session_course_user.id_user
 652                          FROM ' . $tbl_session_course_user . ' as session_course_user
 653                          INNER JOIN     '.$tbl_session_user.' sru ON session_course_user.id_user = sru.id_user AND session_course_user.id_session = sru.id_session    
 654                          INNER JOIN ' . $tbl_session_course . ' as session_course
 655                              ON session_course.course_code = session_course_user.course_code
 656                              AND session_course_user.id_session = session_course.id_session
 657                          INNER JOIN ' . $tbl_session . ' as session
 658                              ON session.id = session_course.id_session
 659                              AND session.id_coach = ' . $coach_id;
 660          if ($_configuration['multiple_access_urls']==true) {
 661              $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
 662              $access_url_id = api_get_current_access_url_id();
 663              if ($access_url_id != -1){
 664                  $sql = 'SELECT session_course_user.id_user
 665                  FROM ' . $tbl_session_course_user . ' as session_course_user
 666                  INNER JOIN     '.$tbl_session_user.' sru ON session_course_user.id_user = sru.id_user AND session_course_user.id_session = sru.id_session        
 667                  INNER JOIN ' . $tbl_session_course . ' as session_course
 668                      ON session_course.course_code = session_course_user.course_code
 669                      AND session_course_user.id_session = session_course.id_session
 670                  INNER JOIN ' . $tbl_session . ' as session
 671                      ON session.id = session_course.id_session
 672                      AND session.id_coach = ' . $coach_id.'
 673                  INNER JOIN '.$tbl_session_rel_access_url.'  session_rel_url
 674                      ON session.id = session_rel_url.session_id WHERE access_url_id = '.$access_url_id;
 675              }
 676          }
 677  
 678          $result = Database::query($sql,__FILE__,__LINE__);
 679  
 680          while ($row = Database::fetch_array($result)) {
 681              $a_students[$row['id_user']] = $row['id_user'];
 682          }
 683          return $a_students;
 684      }
 685  
 686  	function get_student_followed_by_coach_in_a_session($id_session, $coach_id) {
 687  
 688          $coach_id = intval($coach_id);
 689  
 690          $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
 691          $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
 692          $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
 693  
 694          $a_students = array ();
 695  
 696          //////////////////////////////////////////////////////////////
 697          // At first, courses where $coach_id is coach of the course //
 698          //////////////////////////////////////////////////////////////
 699          $sql = 'SELECT course_code FROM ' . $tbl_session_course_user . ' WHERE id_session="' . $id_session . '" AND id_user=' . $coach_id.' AND status=2';
 700  
 701          $result = Database::query($sql,__FILE__,__LINE__);
 702  
 703          while ($a_courses = Database::fetch_array($result)) {
 704              $course_code = $a_courses["course_code"];
 705  
 706              $sql = "SELECT distinct    srcru.id_user
 707                                  FROM $tbl_session_course_user AS srcru
 708                                  WHERE course_code='$course_code' and id_session = '" . $id_session . "'";
 709  
 710              $rs = Database::query($sql, __FILE__, __LINE__);
 711  
 712              while ($row = Database::fetch_array($rs)) {
 713                  $a_students[$row['id_user']] = $row['id_user'];
 714              }
 715          }
 716  
 717          //////////////////////////////////////////////////////////////
 718          // Then, courses where $coach_id is coach of the session    //
 719          //////////////////////////////////////////////////////////////
 720  
 721          $dsl_session_coach = 'SELECT id_coach FROM ' . $tbl_session . ' WHERE id="' . $id_session . '" AND id_coach="' . $coach_id . '"';
 722          $result = Database::query($dsl_session_coach, __FILE__, __LINE__);
 723          //He is the session_coach so we select all the users in the session
 724          if (Database::num_rows($result) > 0) {
 725              $sql = 'SELECT DISTINCT srcru.id_user FROM ' . $tbl_session_course_user . ' AS srcru WHERE id_session="' . $id_session . '"';
 726              $result = Database::query($sql,__FILE__,__LINE__);
 727              while ($row = Database::fetch_array($result)) {
 728                  $a_students[$row['id_user']] = $row['id_user'];
 729              }
 730          }
 731          return $a_students;
 732      }
 733  
 734  	function is_allowed_to_coach_student($coach_id, $student_id) {
 735          $coach_id = intval($coach_id);
 736          $student_id = intval($student_id);
 737  
 738          $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
 739          $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
 740          $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
 741  
 742          //////////////////////////////////////////////////////////////
 743          // At first, courses where $coach_id is coach of the course //
 744          //////////////////////////////////////////////////////////////
 745          /*$sql = 'SELECT 1
 746                          FROM ' . $tbl_session_course_user . ' AS session_course_user
 747                          INNER JOIN ' . $tbl_session_course . ' AS session_course
 748                              ON session_course.course_code = session_course_user.course_code
 749                              AND id_coach=' . $coach_id . '
 750                          WHERE id_user=' . $student_id;*/
 751  
 752          $sql = 'SELECT 1 FROM ' . $tbl_session_course_user . ' WHERE id_user=' . $coach_id .' AND status=2';                        
 753                          
 754          $result = Database::query($sql, __FILE__, __LINE__);
 755          if (Database::num_rows($result) > 0) {
 756              return true;
 757          }
 758  
 759          //////////////////////////////////////////////////////////////
 760          // Then, courses where $coach_id is coach of the session    //
 761          //////////////////////////////////////////////////////////////
 762  
 763          $sql = 'SELECT session_course_user.id_user
 764                          FROM ' . $tbl_session_course_user . ' as session_course_user
 765                          INNER JOIN ' . $tbl_session_course . ' as session_course
 766                              ON session_course.course_code = session_course_user.course_code
 767                          INNER JOIN ' . $tbl_session . ' as session
 768                              ON session.id = session_course.id_session
 769                              AND session.id_coach = ' . $coach_id . '
 770                          WHERE id_user = ' . $student_id;
 771          $result = Database::query($sql, __FILE__, __LINE__);
 772          if (Database::num_rows($result) > 0) {
 773              return true;
 774          }
 775  
 776          return false;
 777  
 778      }
 779  
 780  	function get_courses_followed_by_coach($coach_id, $id_session = '')
 781      {
 782  
 783          $coach_id = intval($coach_id);
 784          if (!empty ($id_session))
 785              $id_session = intval($id_session);
 786  
 787          $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
 788          $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
 789          $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
 790          $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
 791  
 792          //////////////////////////////////////////////////////////////
 793          // At first, courses where $coach_id is coach of the course //
 794          //////////////////////////////////////////////////////////////
 795          $sql = 'SELECT DISTINCT course_code FROM ' . $tbl_session_course_user . ' WHERE id_user=' . $coach_id.' AND status=2';
 796  
 797          global $_configuration;
 798          if ($_configuration['multiple_access_urls']==true) {
 799              $tbl_course_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
 800              $access_url_id = api_get_current_access_url_id();
 801              if ($access_url_id != -1){
 802                  $sql = 'SELECT DISTINCT scu.course_code FROM ' . $tbl_session_course_user . ' scu INNER JOIN '.$tbl_course_rel_access_url.' cru
 803                          ON (scu.course_code = cru.course_code)
 804                          WHERE scu.id_user=' . $coach_id.' AND scu.status=2 AND cru.access_url_id = '.$access_url_id;
 805              }
 806          }
 807  
 808          if (!empty ($id_session))
 809              $sql .= ' AND id_session=' . $id_session;
 810          $result = Database::query($sql, __FILE__, __LINE__);
 811          while ($row = Database::fetch_array($result)) {
 812              $a_courses[$row['course_code']] = $row['course_code'];
 813          }
 814  
 815          //////////////////////////////////////////////////////////////
 816          // Then, courses where $coach_id is coach of the session    //
 817          //////////////////////////////////////////////////////////////
 818          $sql = 'SELECT DISTINCT session_course.course_code
 819                          FROM ' . $tbl_session_course . ' as session_course
 820                          INNER JOIN ' . $tbl_session . ' as session
 821                              ON session.id = session_course.id_session
 822                              AND session.id_coach = ' . $coach_id . '
 823                          INNER JOIN ' . $tbl_course . ' as course
 824                              ON course.code = session_course.course_code';
 825  
 826          if ($_configuration['multiple_access_urls']==true) {
 827              $tbl_course_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
 828              $access_url_id = api_get_current_access_url_id();
 829              if ($access_url_id != -1){
 830                  $sql = 'SELECT DISTINCT session_course.course_code
 831                          FROM ' . $tbl_session_course . ' as session_course
 832                          INNER JOIN ' . $tbl_session . ' as session
 833                              ON session.id = session_course.id_session
 834                              AND session.id_coach = ' . $coach_id . '
 835                          INNER JOIN ' . $tbl_course . ' as course
 836                              ON course.code = session_course.course_code
 837                           INNER JOIN '.$tbl_course_rel_access_url.' course_rel_url
 838                          ON (session_course.course_code = course_rel_url.course_code)';
 839              }
 840          }
 841  
 842          if (!empty ($id_session)) {
 843              $sql .= ' WHERE session_course.id_session=' . $id_session;
 844              if ($_configuration['multiple_access_urls']==true)
 845                  $sql .=  ' AND access_url_id = '.$access_url_id;
 846          }  else {
 847              if ($_configuration['multiple_access_urls']==true)
 848                  $sql .=  ' WHERE access_url_id = '.$access_url_id;
 849          }
 850  
 851          $result = Database::query($sql, __FILE__, __LINE__);
 852  
 853          while ($row = Database::fetch_array($result)) {
 854              $a_courses[$row['course_code']] = $row['course_code'];
 855          }
 856  
 857          return $a_courses;
 858      }
 859  
 860  	function get_sessions_coached_by_user($coach_id) {
 861          // table definition
 862          $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
 863          $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
 864          $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
 865  
 866          // protect datas
 867          $coach_id = intval($coach_id);
 868  
 869          // session where we are general coach
 870          $sql = 'SELECT DISTINCT id, name, date_start, date_end
 871                          FROM ' . $tbl_session . '
 872                          WHERE id_coach=' . $coach_id;
 873  
 874          global $_configuration;
 875          if ($_configuration['multiple_access_urls']==true) {
 876              $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
 877              $access_url_id = api_get_current_access_url_id();
 878              if ($access_url_id != -1){
 879                  $sql = 'SELECT DISTINCT id, name, date_start, date_end
 880                          FROM ' . $tbl_session . ' session INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
 881                          ON (session.id = session_rel_url.session_id)
 882                          WHERE id_coach=' . $coach_id.' AND access_url_id = '.$access_url_id;
 883              }
 884          }
 885  
 886          $rs = Database::query($sql,__FILE__,__LINE__);
 887  
 888          while ($row = Database::fetch_array($rs))
 889          {
 890              $a_sessions[$row["id"]] = $row;
 891          }
 892  
 893          // session where we are coach of a course
 894          $sql = 'SELECT DISTINCT session.id, session.name, session.date_start, session.date_end
 895                          FROM ' . $tbl_session . ' as session
 896                          INNER JOIN ' . $tbl_session_course_user . ' as session_course_user
 897                              ON session.id = session_course_user.id_session
 898                              AND session_course_user.id_user=' . $coach_id.' AND session_course_user.status=2';
 899  
 900          global $_configuration;
 901          if ($_configuration['multiple_access_urls']==true) {
 902              $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
 903              $access_url_id = api_get_current_access_url_id();
 904              if ($access_url_id != -1){
 905                  $sql = 'SELECT DISTINCT session.id, session.name, session.date_start, session.date_end
 906                          FROM ' . $tbl_session . ' as session
 907                          INNER JOIN ' . $tbl_session_course_user . ' as session_course_user
 908                              ON session.id = session_course_user.id_session AND session_course_user.id_user=' . $coach_id.' AND session_course_user.status=2
 909                          INNER JOIN '.$tbl_session_rel_access_url.' session_rel_url
 910                          ON (session.id = session_rel_url.session_id)
 911                          WHERE access_url_id = '.$access_url_id;
 912              }
 913          }
 914  
 915          $rs = Database::query($sql,__FILE__,__LINE__);
 916  
 917          while ($row = Database::fetch_array($rs))
 918          {
 919              $a_sessions[$row["id"]] = $row;
 920          }
 921  
 922          if (is_array($a_sessions)) {
 923              foreach ($a_sessions as & $session) {
 924                  if ($session['date_start'] == '0000-00-00') {
 925                      $session['status'] = get_lang('SessionActive');
 926                  }
 927                  else {
 928                      $date_start = explode('-', $session['date_start']);
 929                      $time_start = mktime(0, 0, 0, $date_start[1], $date_start[2], $date_start[0]);
 930                      $date_end = explode('-', $session['date_end']);
 931                      $time_end = mktime(0, 0, 0, $date_end[1], $date_end[2], $date_end[0]);
 932                      if ($time_start < time() && time() < $time_end) {
 933                          $session['status'] = get_lang('SessionActive');
 934                      }
 935                      else{
 936                          if (time() < $time_start) {
 937                              $session['status'] = get_lang('SessionFuture');
 938                          }
 939                          else{
 940                              if (time() > $time_end) {
 941                                  $session['status'] = get_lang('SessionPast');
 942                              }
 943                          }
 944                      }
 945                  }
 946              }
 947          }
 948  
 949          return $a_sessions;
 950  
 951      }
 952  
 953  	function get_courses_list_from_session($session_id) {
 954          //protect datas
 955          $session_id = intval($session_id);
 956  
 957          // table definition
 958          $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
 959          $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
 960                  
 961          $sql = 'SELECT DISTINCT course_code
 962                          FROM ' . $tbl_session_course . '
 963                          WHERE id_session=' . $session_id;
 964  
 965          $rs = Database::query($sql, __FILE__, __LINE__);
 966          $a_courses = array ();
 967          while ($row = Database::fetch_array($rs)) {
 968              $a_courses[$row['course_code']] = $row;
 969          }
 970          return $a_courses;
 971      }
 972  
 973  	function count_student_assignments($student_id, $course_code) {
 974          require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
 975  
 976          // protect datas        
 977          $course_code = Database::escape_string($course_code);
 978          // get the informations of the course
 979          $a_course = CourseManager :: get_course_information($course_code);
 980          if (!empty($a_course['db_name'])) {
 981              // table definition
 982              $tbl_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY, $a_course['db_name']);            
 983              $condition_user = "";
 984              if (is_array($student_id)) {                
 985                  $condition_user = " AND insert_user_id IN (".implode(',',$student_id).") ";
 986              } else {
 987                  $condition_user = " AND insert_user_id = '$student_id' ";                
 988              }            
 989              $sql = "SELECT count(tool) FROM $tbl_item_property WHERE tool='work' $condition_user ";
 990              $rs = Database::query($sql, __LINE__, __FILE__);
 991              $row = Database::fetch_row($rs);
 992              return $row[0];
 993          }
 994          return null;        
 995      }
 996  
 997  	function count_student_messages($student_id, $course_code) {
 998          require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
 999  
1000          // protect datas
1001          $course_code = addslashes($course_code);
1002          // get the informations of the course
1003          $a_course = CourseManager :: get_course_information($course_code);
1004          if (!empty($a_course['db_name'])) {
1005              // table definition
1006              $tbl_messages = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']);            
1007              $condition_user = "";
1008              if (is_array($student_id)) {
1009                  $condition_user = " WHERE poster_id IN (".implode(',',$student_id).") ";
1010              } else {
1011                  $condition_user = " WHERE poster_id = '$student_id' ";
1012              }            
1013              $sql = "SELECT count(post_id) FROM $tbl_messages $condition_user ";        
1014              $rs = Database::query($sql, __LINE__, __FILE__);
1015              $row = Database::fetch_row($rs);
1016              return $row[0];
1017          }        
1018          return null;        
1019      }
1020  
1021  /**
1022  * This function counts the number of post by course
1023  * @param  string $course_code - Course ID
1024  * @return    int the number of post by course
1025  * @author Christian Fasanando <christian.fasanando@dokeos.com>,
1026  * @version enero 2009, dokeos 1.8.6
1027  */
1028  	function count_number_of_posts_by_course($course_code) {
1029          //protect data
1030          $course_code = addslashes($course_code);
1031          // get the informations of the course
1032          $a_course = CourseManager :: get_course_information($course_code);
1033          $count = 0;
1034          if (!empty($a_course['db_name'])) {
1035              $tbl_posts = Database :: get_course_table(TABLE_FORUM_POST, $a_course['db_name']);
1036              $sql = "SELECT count(*) FROM $tbl_posts";
1037              $result = Database::query($sql, __FILE__, __LINE__);
1038              $row = Database::fetch_row($result);
1039              $count = $row[0];
1040              return $count;
1041          } else {
1042              return null;
1043          }
1044      }
1045  
1046  /**
1047  * This function counts the number of threads by course
1048  * @param  string $course_code - Course ID
1049  * @return    int the number of threads by course
1050  * @author Christian Fasanando <christian.fasanando@dokeos.com>,
1051  * @version enero 2009, dokeos 1.8.6
1052  */
1053  	function count_number_of_threads_by_course($course_code) {
1054          //protect data
1055          $course_code = addslashes($course_code);
1056          // get the informations of the course
1057          $a_course = CourseManager :: get_course_information($course_code);
1058          $count = 0;
1059          if (!empty($a_course['db_name'])) {
1060              $tbl_threads = Database :: get_course_table(TABLE_FORUM_THREAD, $a_course['db_name']);
1061              $sql = "SELECT count(*) FROM $tbl_threads";
1062              $result = Database::query($sql, __FILE__, __LINE__);
1063              $row = Database::fetch_row($result);
1064              $count = $row[0];
1065              return $count;
1066          } else {
1067              return null;
1068          }
1069      }
1070  
1071  /**
1072  * This function counts the number of forums by course
1073  * @param  string $course_code - Course ID
1074  * @return    int the number of forums by course
1075  * @author Christian Fasanando <christian.fasanando@dokeos.com>,
1076  * @version enero 2009, dokeos 1.8.6
1077  */
1078  	function count_number_of_forums_by_course($course_code) {
1079          //protect data
1080          $course_code = addslashes($course_code);
1081          // get the informations of the course
1082          $a_course = CourseManager :: get_course_information($course_code);
1083          $count = 0;
1084          if (!empty($a_course['db_name'])) {
1085              $tbl_forums = Database :: get_course_table(TABLE_FORUM, $a_course['db_name']);
1086              $sql = "SELECT count(*) FROM $tbl_forums";
1087              $result = Database::query($sql, __FILE__, __LINE__);
1088              $row = Database::fetch_row($result);
1089              $count = $row[0];
1090              return $count;
1091          } else {
1092              return null;
1093          }
1094      }
1095  
1096  /**
1097  * This function counts the chat last connections by course in x days
1098  * @param  string $course_code - Course ID
1099  * @param  int $last_days -  last x days
1100  * @return    int the chat last connections by course in x days
1101  * @author Christian Fasanando <christian.fasanando@dokeos.com>,
1102  * @version enero 2009, dokeos 1.8.6
1103  */
1104  	function chat_connections_during_last_x_days_by_course($course_code,$last_days) {
1105          //protect data
1106          $last_days = intval($last_days);
1107          $course_code = addslashes($course_code);
1108          // get the informations of the course
1109          $a_course = CourseManager :: get_course_information($course_code);
1110          $count = 0;
1111          if (!empty($a_course['db_name'])) {
1112              $tbl_stats_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS, $a_course['db_name']);
1113  
1114              $sql = "SELECT count(*) FROM $tbl_stats_access WHERE DATE_SUB(NOW(),INTERVAL $last_days DAY) <= access_date
1115                      AND access_cours_code = '$course_code' AND access_tool='".TOOL_CHAT."'";
1116              $result = Database::query($sql, __FILE__, __LINE__);
1117              $row = Database::fetch_row($result);
1118              $count = $row[0];
1119              return $count;
1120          } else {
1121              return null;
1122          }
1123      }
1124  
1125  
1126  /**
1127  * This function gets the last student's connection in chat
1128  * @param  int $student_id - Student ID
1129  * @param  string $course_code - Course ID
1130  * @return string the last connection
1131  * @author Christian Fasanando <christian.fasanando@dokeos.com>,
1132  * @version enero 2009, dokeos 1.8.6
1133  */
1134  	function chat_last_connection($student_id,$course_code) {
1135          require_once (api_get_path(LIBRARY_PATH) . 'course.lib.php');
1136  
1137          //protect datas
1138          $student_id = intval($student_id);
1139          $course_code = addslashes($course_code);
1140  
1141          // get the informations of the course
1142          $a_course = CourseManager :: get_course_information($course_code);
1143          $date_time = '';
1144          if (!empty($a_course['db_name'])) {
1145              // table definition
1146              $tbl_stats_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS, $a_course['db_name']);
1147              $sql = "SELECT access_date FROM $tbl_stats_access
1148                       WHERE access_tool='".TOOL_CHAT."' AND access_user_id='$student_id' AND access_cours_code = '$course_code' ORDER BY access_date DESC limit 1";
1149  
1150              $rs = Database::query($sql, __LINE__, __FILE__);
1151              $row = Database::fetch_array($rs);
1152              $last_connection = $row['access_date'];
1153              if (!empty($last_connection)) {
1154                  $date_format_long = format_locale_date(get_lang('DateFormatLongWithoutDay'), strtotime($last_connection));
1155                  $time = explode(' ',$last_connection);
1156                  $date_time = $date_format_long.' '.$time[1];
1157              }
1158  
1159              return $date_time;
1160          } else {
1161                  return null;
1162          }
1163      }
1164  
1165  	function count_student_visited_links($student_id, $course_code) {
1166          // protect datas
1167          $student_id = intval($student_id);
1168          $course_code = addslashes($course_code);
1169  
1170          // table definition
1171          $tbl_stats_links = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
1172  
1173          $sql = 'SELECT 1
1174                          FROM ' . $tbl_stats_links . '
1175                          WHERE links_user_id=' . $student_id . '
1176                          AND links_cours_id="' . $course_code . '"';
1177  
1178          $rs = Database::query($sql, __LINE__, __FILE__);
1179          return Database::num_rows($rs);
1180      }
1181  
1182  	function count_student_downloaded_documents($student_id, $course_code) {
1183          // protect datas
1184          $student_id = intval($student_id);
1185          $course_code = addslashes($course_code);
1186  
1187          // table definition
1188          $tbl_stats_documents = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
1189  
1190          $sql = 'SELECT 1
1191                          FROM ' . $tbl_stats_documents . '
1192                          WHERE down_user_id=' . $student_id . '
1193                          AND down_cours_id="' . $course_code . '"';
1194  
1195          $rs = Database::query($sql, __LINE__, __FILE__);
1196          return Database::num_rows($rs);
1197      }
1198  
1199  	function get_course_list_in_session_from_student($user_id, $id_session) {
1200          $user_id = intval($user_id);
1201          $id_session = intval($id_session);
1202          $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
1203          $sql = 'SELECT course_code FROM ' . $tbl_session_course_user . ' WHERE id_user="' . $user_id . '" AND id_session="' . $id_session . '"';
1204          $result = Database::query($sql, __LINE__, __FILE__);
1205          $a_courses = array ();
1206          while ($row = Database::fetch_array($result)) {
1207              $a_courses[$row['course_code']] = $row['course_code'];
1208          }
1209          return $a_courses;
1210      }
1211  
1212  	function get_inactives_students_in_course($course_code, $since, $session_id=0)
1213      {
1214          $tbl_track_login = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
1215          $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
1216          $table_course_rel_user            = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
1217          $inner = '';
1218          if($session_id!=0)
1219          {
1220              $inner = ' INNER JOIN '.$tbl_session_course_user.' session_course_user
1221                          ON stats_login.course_code = session_course_user.course_code
1222                          AND session_course_user.id_session = '.intval($session_id).'
1223                          AND session_course_user.id_user = stats_login.user_id ';
1224          }
1225          $sql = 'SELECT user_id, MAX(login_course_date) max_date FROM'.$tbl_track_login.' stats_login'.$inner.'
1226                  GROUP BY user_id
1227                  HAVING DATE_SUB( NOW(), INTERVAL '.$since.' DAY) > max_date ';
1228          //HAVING DATE_ADD(max_date, INTERVAL '.$since.' DAY) < NOW() ';
1229  
1230          if ($since == 'never') {
1231              $sql = 'SELECT course_user.user_id FROM '.$table_course_rel_user.' course_user
1232                          LEFT JOIN '. $tbl_track_login.' stats_login 
1233                          ON course_user.user_id = stats_login.user_id'.
1234                          $inner.'
1235                      WHERE course_user.course_code = \''.Database::escape_string($course_code).'\' 
1236                      AND stats_login.login_course_date IS NULL
1237                      GROUP BY course_user.user_id';
1238          }        
1239          $rs = api_sql_query($sql,__FILE__,__LINE__);
1240          $inactive_users = array();
1241          while($user = Database::fetch_array($rs))
1242          {
1243              $inactive_users[] = $user['user_id'];
1244          }
1245          return $inactive_users;
1246      }
1247  
1248  	function count_login_per_student($student_id, $course_code) {
1249          $student_id = intval($student_id);
1250          $course_code = addslashes($course_code);
1251          $tbl_course_rel_user = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
1252  
1253          $sql = 'SELECT '.$student_id.'
1254          FROM ' . $tbl_course_rel_user . '
1255          WHERE access_user_id=' . $student_id . '
1256          AND access_cours_code="' . $course_code . '"';
1257  
1258          $rs = Database::query($sql, __FILE__, __LINE__);
1259          $nb_login = Database::num_rows($rs);
1260  
1261          return $nb_login;
1262      }
1263  
1264  
1265  	function get_student_followed_by_drh($hr_dept_id) {
1266  
1267          $hr_dept_id = intval($hr_dept_id);
1268          $a_students = array ();
1269  
1270          $tbl_organism = Database :: get_main_table(TABLE_MAIN_ORGANISM);
1271          $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
1272  
1273          $sql = 'SELECT DISTINCT user_id FROM '.$tbl_user.' as user
1274                  WHERE hr_dept_id='.$hr_dept_id;
1275          $rs = Database::query($sql, __FILE__, __LINE__);
1276  
1277          while($user = Database :: fetch_array($rs))
1278          {
1279              $a_students[$user['user_id']] = $user['user_id'];
1280          }
1281  
1282  
1283          return $a_students;
1284      }
1285      /**
1286       * allow get average  of test of scorm and lp
1287       * @author isaac flores paz <florespaz@bidsoftperu.com>
1288       * @param int the user id
1289       * @param string the course id
1290       */
1291  	function get_average_test_scorm_and_lp ($user_id,$course_id) {
1292          
1293          /**
1294           * this function returned inconsistent values (e.g. 3000%).
1295           * Moreover it's a duplicate of get_avg_student_score
1296           * That's why we redirect to get_avg_student_score
1297           */
1298          
1299          return Tracking::get_avg_student_score($user_id, $course_id);
1300      }
1301  
1302   function count_item_resources() {
1303      $table_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY);
1304      $table_user = Database :: get_main_table(TABLE_MAIN_USER);
1305      $sql = "SELECT count(tool) AS total_number_of_items FROM $table_item_property track_resource, $table_user user" .
1306              " WHERE track_resource.insert_user_id = user.user_id";
1307  
1308      if (isset($_GET['keyword'])) {
1309          $keyword = Database::escape_string($_GET['keyword']);
1310          $sql .= " AND (user.username LIKE '%".$keyword."%' OR lastedit_type LIKE '%".$keyword."%' OR tool LIKE '%".$keyword."%')";
1311      }
1312  
1313      $sql .= " AND tool IN ('document', 'learnpath', 'quiz', 'glossary', 'link', 'course_description')";
1314      $res = Database::query($sql, __FILE__, __LINE__);
1315      $obj = Database::fetch_object($res);
1316      return $obj->total_number_of_items;
1317  }
1318  
1319  function get_item_resources_data($from, $number_of_items, $column, $direction) {
1320      global $dateTimeFormatLong;
1321      $table_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY);
1322      $table_user = Database :: get_main_table(TABLE_MAIN_USER);
1323      $table_session = Database :: get_main_table(TABLE_MAIN_SESSION);
1324      $sql = "SELECT
1325                   tool as col0,
1326                  lastedit_type as col1,
1327                  ref as ref,
1328                  user.username as col3,
1329                  insert_date as col5,
1330                  visibility as col6
1331              FROM $table_item_property track_resource, $table_user user
1332              WHERE track_resource.insert_user_id = user.user_id ";
1333  
1334      if (isset($_GET['keyword'])) {
1335          $keyword = Database::escape_string($_GET['keyword']);
1336          $sql .= " AND (user.username LIKE '%".$keyword."%' OR lastedit_type LIKE '%".$keyword."%' OR tool LIKE '%".$keyword."%') ";
1337      }
1338  
1339      $sql .= " AND tool IN ('document', 'learnpath', 'quiz', 'glossary', 'link', 'course_description')";
1340  
1341      if ($column == 0) { $column = '0'; }
1342      if ($column != '' && $direction != '') {
1343          if ($column != 2 && $column != 4) {
1344              $sql .=    " ORDER BY col$column $direction";
1345          }
1346      } else {
1347          $sql .=    " ORDER BY col5 DESC ";
1348      }
1349  
1350      $sql .=    " LIMIT $from, $number_of_items ";
1351  
1352      $res = Database::query($sql, __FILE__, __LINE__) or die(mysql_error());
1353      $resources = array ();
1354  
1355      while ($row = Database::fetch_array($res)) {
1356          $ref = $row['ref'];
1357          $table_name = Tracking::get_tool_name_table($row['col0']);
1358          $table_tool = Database :: get_course_table($table_name['table_name']);
1359          $id = $table_name['id_tool'];
1360          $query = "SELECT session.id, session.name, user.username FROM $table_tool tool, $table_session session, $table_user user" .
1361                      " WHERE tool.session_id = session.id AND session.id_coach = user.user_id AND tool.$id = $ref";
1362          $recorset = Database::query($query, __FILE__, __LINE__);
1363  
1364          if (!empty($recorset)) {
1365  
1366              $obj = Database::fetch_object($recorset);
1367  
1368              $name_session = '';
1369              $coach_name = '';
1370              if (!empty($obj)) {
1371                  $name_session = $obj->name;
1372                  $coach_name = $obj->username;
1373              }
1374  
1375              $url_tool = api_get_path(WEB_CODE_PATH).$table_name['link_tool'];
1376  
1377              $row[0] = '';
1378              if ($row['col6'] != 2) {
1379                  $row[0] = '<a href="'.$url_tool.'?'.api_get_cidreq().'&'.$obj->id.'">'.api_ucfirst($row['col0']).'</a>';
1380              } else {
1381                  $row[0] = api_ucfirst($row['col0']);
1382              }
1383  
1384              $row[1] = get_lang($row[1]);
1385  
1386              $row[5] = api_ucfirst(format_locale_date($dateTimeFormatLong, strtotime($row['col5'])));
1387  
1388              $row[4] = '';
1389              if ($table_name['table_name'] == 'document') {
1390                  $condition = 'tool.title as title';
1391                  $query_document = "SELECT $condition FROM $table_tool tool" .
1392                                      " WHERE id = $ref";
1393                  $rs_document = Database::query($query_document, __FILE__, __LINE__) or die(mysql_error());
1394                  $obj_document = Database::fetch_object($rs_document);
1395                  $row[4] = $obj_document->title;
1396              }
1397  
1398              $row2 = $name_session;
1399              if (!empty($coach_name)) {
1400                  $row2 .= '<br />'.get_lang('Coach').': '.$coach_name;
1401              }
1402              $row[2] = $row2;
1403  
1404              $resources[] = $row;
1405          }
1406  
1407      }
1408  
1409      return $resources;
1410  }
1411      
1412  function get_tool_name_table($tool) {
1413      switch ($tool) {
1414          case 'document':
1415              $table_name = TABLE_DOCUMENT;
1416              $link_tool = 'document/document.php';
1417              $id_tool = 'id';
1418              break;
1419          case 'learnpath':
1420              $table_name = TABLE_LP_MAIN;
1421              $link_tool = 'newscorm/lp_controller.php';
1422              $id_tool = 'id';
1423              break;
1424          case 'quiz':
1425              $table_name = TABLE_QUIZ_TEST;
1426              $link_tool = 'exercice/exercice.php';
1427              $id_tool = 'id';
1428              break;
1429          case 'glossary':
1430              $table_name = TABLE_GLOSSARY;
1431              $link_tool = 'glossary/index.php';
1432              $id_tool = 'glossary_id';
1433              break;
1434          case 'link':
1435              $table_name = TABLE_LINK;
1436              $link_tool = 'link/link.php';
1437              $id_tool = 'id';
1438              break;
1439          case 'course_description':
1440              $table_name = TABLE_COURSE_DESCRIPTION;
1441              $link_tool = 'course_description/';
1442              $id_tool = 'id';
1443              break;
1444          default:
1445              $table_name = $tool;
1446              break;
1447      }
1448      return array('table_name' => $table_name,
1449                   'link_tool' => $link_tool,
1450                   'id_tool' => $id_tool);
1451  }
1452  /**
1453   * This function gets all the information of a certrain ($field_id) additional profile field for a specific list of users is more efficent than  get_additional_profile_information_of_field() function
1454   * It gets the information of all the users so that it can be displayed in the sortable table or in the csv or xls export
1455   *
1456   * @author    Julio Montoya <gugli100@gmail.com>
1457   * @param    int field id
1458   * @param    array list of user ids
1459   * @return    array
1460   * @since    Nov 2009
1461   * @version    1.8.6.2
1462   */
1463  function get_additional_profile_information_of_field_by_user($field_id, $users) {
1464      // Database table definition
1465      $table_user                 = Database::get_main_table(TABLE_MAIN_USER);
1466      $table_user_field_values     = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
1467      $result_extra_field         = UserManager::get_extra_field_information($field_id);
1468  
1469      if (!empty($users)) {
1470          if ($result_extra_field['field_type'] == USER_FIELD_TYPE_TAG ) {
1471              foreach($users as $user_id) {
1472                  $user_result = UserManager::get_user_tags($user_id, $field_id);
1473                  $tag_list = array();
1474                  foreach($user_result as $item) {
1475                      $tag_list[] = $item['tag'];
1476                  }
1477                  $return[$user_id][] = implode(', ',$tag_list);
1478              }
1479          } else {
1480              $new_user_array = array();
1481              foreach($users as $user_id) {
1482                  $new_user_array[]= "'".$user_id."'";
1483              }
1484              $users = implode(',',$new_user_array);
1485              //selecting only the necessary information NOT ALL the user list
1486              $sql = "SELECT user.user_id, field.field_value FROM $table_user user INNER JOIN $table_user_field_values field
1487                      ON (user.user_id = field.user_id)
1488                      WHERE field.field_id=".intval($field_id)." AND user.user_id IN ($users)";
1489  
1490              $result = api_sql_query($sql,__FILE__,__LINE__);
1491              while($row = Database::fetch_array($result)) {
1492                  // get option value for field type double select by id
1493                  if (!empty($row['field_value'])) {
1494                      if ($result_extra_field['field_type'] == USER_FIELD_TYPE_DOUBLE_SELECT) {
1495                          $id_double_select = explode(';',$row['field_value']);
1496                          if (is_array($id_double_select)) {
1497                              $value1 = $result_extra_field['options'][$id_double_select[0]]['option_value'];
1498                              $value2 = $result_extra_field['options'][$id_double_select[1]]['option_value'];
1499                              $row['field_value'] = ($value1.';'.$value2);
1500                          }
1501                      }
1502                  }
1503                  // get other value from extra field
1504                  $return[$row['user_id']][] = $row['field_value'];
1505              }
1506          }
1507      }
1508      return $return;
1509  }
1510  
1511  /**
1512   * Get data for users list in sortable with pagination
1513   * @return array
1514   */
1515  function get_user_data($from = null, $number_of_items = null, $column = null, $direction = null, $get_extra_field = true, $add_limit = true) {
1516  
1517      global $user_ids, $course_code, $additional_user_profile_info, $export_csv, $is_western_name_order, $csv_content;
1518  
1519      $course_code = Database::escape_string($course_code);
1520      $course_info = CourseManager :: get_course_information($course_code);
1521      $tbl_track_cours_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
1522      $tbl_user                 = Database :: get_main_table(TABLE_MAIN_USER);
1523      $tbl_item_property         = Database :: get_course_table(TABLE_ITEM_PROPERTY, $course_info['db_name']);
1524      $tbl_forum_post          = Database :: get_course_table(TABLE_FORUM_POST, $course_info['db_name']);
1525      $tbl_course_lp_view     = Database :: get_course_table(TABLE_LP_VIEW, $course_info['db_name']);
1526      $tbl_course_lp             = Database :: get_course_table(TABLE_LP_MAIN, $course_info['db_name']);
1527  
1528      // get all users data from a course for sortable with limit
1529      $condition_user = "";
1530      if (is_array($user_ids)) {
1531          $condition_user = " WHERE user.user_id IN (".implode(',',$user_ids).") ";
1532      } else {
1533          $condition_user = " WHERE user.user_id = '$user_ids' ";
1534      }
1535      $sql = "SELECT user.user_id as col0,
1536              user.official_code as col1,
1537              user.lastname as col2,
1538              user.firstname as col3 ";
1539   
1540      // getting additional user fields (of the user table). These can be defined in in the function display_additional_profile_fields and 
1541      // need to be added to the array of valid fields (a security measure) 
1542      if (isset($_GET['additional_profile_field']) AND in_array($_GET['additional_profile_field'], array('active','email'))) {
1543          $sql .= ", user.".$_GET['additional_profile_field']." as col10 ";    
1544      }
1545      $sql .= "FROM $tbl_user as user
1546              $condition_user ";
1547  
1548      if (!in_array($direction, array('ASC','DESC'))) {
1549          $direction = 'ASC';
1550      }
1551      $column = intval($column);
1552      $from = intval($from);
1553      $number_of_items = intval($number_of_items);
1554      $sql .= " ORDER BY col$column $direction ";
1555      if ($add_limit === true) {
1556        $sql .= " LIMIT $from,$number_of_items";
1557      }
1558      $res = Database::query($sql, __FILE__, __LINE__);
1559      $users = array ();
1560      $t = time();
1561         $row = array();
1562  
1563      while ($user = Database::fetch_row($res)) {
1564          $row[0] = $user[1];
1565          if ($is_western_name_order) {
1566              $row[1] = $user[3];
1567              $row[2] = $user[2];
1568          } else {
1569              $row[1] = $user[2];
1570              $row[2] = $user[3];
1571          }
1572          $time = Tracking::get_time_spent_on_the_course($user[0], $course_code);
1573          $row[3] = api_time_to_hms($time);
1574          $avg_student_score = Tracking::get_avg_student_score($user[0], $course_code);
1575          $avg_student_progress = Tracking::get_avg_student_progress($user[0], $course_code);
1576          if (empty($avg_student_progress)) {$avg_student_progress=0;}
1577          $row[4] = $avg_student_progress.'%';
1578          $row[5] = empty($avg_student_score) ? '-' : $avg_student_score.'%';
1579          $row[6] = Tracking::count_student_assignments($user[0], $course_code);$user[4];
1580          $row[7] = Tracking::count_student_messages($user[0], $course_code);//$user[5];
1581          $row[8] = Tracking::get_first_connection_date_on_the_course($user[0], $course_code);
1582          $row[9] = Tracking::get_last_connection_date_on_the_course($user[0], $course_code);
1583  
1584          // we need to display an additional profile field
1585          if (isset($_GET['additional_profile_field']) AND is_numeric($_GET['additional_profile_field'])) {
1586              if (is_array($additional_user_profile_info[$user[0]])) {
1587                  $row[10]=implode(', ', $additional_user_profile_info[$user[0]]);
1588              } else {
1589                  $row[10]='&nbsp;';
1590              }
1591          }
1592          
1593          // getting additional user fields (of the user table). These can be defined in in the function display_additional_profile_fields and 
1594          // need to be added to the array of valid fields (a security measure) 
1595          if (isset($_GET['additional_profile_field']) AND in_array($_GET['additional_profile_field'], array('active','email'))) {
1596              if ($_GET['additional_profile_field'] == 'active'){
1597                  if ($user[4] == '1'){
1598                      $row[10] = Display::return_icon('right.png');
1599                  } else {
1600                      $row[10] = Display::return_icon('wrong.png');
1601                  }
1602              }
1603              if ($_GET['additional_profile_field'] == 'email'){
1604                      $row[10] = '<a href="mailto:'.$user[4].'">'.$user[4].'</a>';    
1605              }
1606          }
1607          
1608          
1609  
1610    if ($get_extra_field === true) {
1611      $row[11] = '<center><a href="../mySpace/myStudents.php?student='.$user[0].'&details=true&course='.$course_code.'&origin=tracking_course"><img src="'.api_get_path(WEB_IMG_PATH).'arrow-right-double.png" border="0" /></a></center>';
1612    } else {
1613      $row[10] = '<center><a href="../mySpace/myStudents.php?student='.$user[0].'&details=true&course='.$course_code.'&origin=tracking_course">'.Display::return_icon('pixel.gif','',array('class'=>'actionplaceholdericon actionstatisticsdetails','border'=>'0')).'</a></center>';
1614    }
1615  
1616          if ($export_csv) {
1617              $row[8] = strip_tags($row[8]);
1618              $row[9] = strip_tags($row[9]);
1619              unset($row[11]);
1620              $csv_content[] = $row;
1621              unset($row[10]);
1622          }
1623      if ($get_extra_field === true) {
1624          // store columns in array $users
1625          $users[] = array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$row[7],$row[8],$row[9],$row[10],$row[11]);
1626      } else {
1627          // store columns in array $users
1628          $users[] = array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$row[7],$row[8],$row[9],$row[10]);
1629      }
1630  
1631      $GLOBALS['chart_data'][$user[0]] = array('lastname'=>$row[2], 'firstname'=>$row[1], 'progress'=>intval($avg_student_progress), 'score'=>intval($avg_student_score), 'time'=>intval($time));
1632      }
1633      return $users;
1634  }
1635  /**
1636   * This function gets all the information of a certrain ($field_id) additional profile field.
1637   * It gets the information of all the users so that it can be displayed in the sortable table or in the csv or xls export
1638   *
1639   * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
1640   * @since October 2009
1641   * @version 1.8.7
1642   */
1643  function get_additional_profile_information_of_field($field_id){
1644      // Database table definition
1645      $table_user             = Database::get_main_table(TABLE_MAIN_USER);
1646      $table_user_field_values     = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
1647  
1648      $sql = "SELECT user.user_id, field.field_value FROM $table_user user, $table_user_field_values field
1649          WHERE user.user_id = field.user_id
1650          AND field.field_id='".intval($field_id)."'";
1651      $result = api_sql_query($sql,__FILE__,__LINE__);
1652      while($row = Database::fetch_array($result))
1653      {
1654          $return[$row['user_id']][] = $row['field_value'];
1655      }
1656      return $return;
1657  }
1658  /**
1659   * Display all the additionally defined user profile fields
1660   * This function will only display the fields, not the values of the field because it does not act as a filter
1661   * but it adds an additional column instead.
1662   *
1663   * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
1664   * @since October 2009
1665   * @version 1.8.7
1666   */
1667  function display_additional_profile_fields($btn_submit = true, $multiple = false, $only_extra_fields = false) {
1668      // getting all the extra profile fields that are defined by the platform administrator
1669      $extra_fields = UserManager :: get_extra_fields(0,50,5,'ASC');
1670  
1671      // creating the form
1672      $return = '<form action="'.api_get_self().'?'.api_get_cidreq().'" method="get" name="additional_profile_field_form" id="additional_profile_field_form">';
1673  
1674      // the select field with the additional user profile fields (= this is where we select the field of which we want to see
1675      // the information the users have entered or selected.
1676      $return .= '<select name="'.($multiple?'additional_profile_field[]':'additional_profile_field').'" id="additional_profile_field" '.($multiple?' multiple="multiple"':'').'>';    
1677          if (!$multiple) {
1678              $return .= '<option value="-">'.get_lang('SelectFieldToAdd').'</option>';
1679          }
1680          
1681          if (!$only_extra_fields) {
1682              if ($_GET['additional_profile_field'] == 'active'){ $activeselected = 'selected="selected"'; } 
1683              $return .= '<option value="active" '.$activeselected.'>'.get_lang('Active').'</option>';
1684              if ($_GET['additional_profile_field'] == 'email') { $emailselected = 'selected="selected"'; } 
1685              $return .= '<option value="email" '.$emailselected.'>'.get_lang('Email').'</option>';
1686          }
1687          
1688      foreach ($extra_fields as $key=>$field) {
1689          // show only extra fields that are visible, added by J.Montoya
1690          if ($field[6]==1) {
1691              if ($field[0] == $_GET['additional_profile_field'] || ($multiple && in_array($field[0], $_GET['additional_profile_field']))) {
1692                  $selected = 'selected="selected"';
1693              } else {
1694                  $selected = '';
1695              }
1696              $return .= '<option value="'.$field[0].'" '.$selected.'>'.$field[3].'</option>';
1697          }
1698      }
1699      $return .= '</select>';
1700  
1701      // the form elements for the $_GET parameters (because the form is passed through GET
1702      foreach ($_GET as $key=>$value){
1703          if ($key <> 'additional_profile_field')    {
1704              $return .= '<input type="hidden" name="'.$key.'" value="'.Security::Remove_XSS($value).'" />';
1705          }
1706      }
1707      // the submit button
1708          if ($btn_submit) {
1709              $return .= '<button class="save" type="submit" style="float:none;margin-left:10px;" name="add_field">'.get_lang('Validate').'</button>';
1710          }
1711      $return .= '</form>';
1712      return $return;
1713  }
1714  
1715  /**
1716   * This function exports the table that we see in display_tracking_user_overview()
1717   *
1718   * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
1719   * @version Dokeos 1.8.6
1720   * @since October 2008
1721   */
1722  function export_tracking_user_overview() {
1723      // database table definitions
1724      $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
1725  
1726      $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
1727      $sort_by_first_name = api_sort_by_first_name();
1728  
1729      // the values of the sortable table
1730      if ($_GET['tracking_user_overview_page_nr']) {
1731          $from = $_GET['tracking_user_overview_page_nr'];
1732      } else {
1733          $from = 0;
1734      }
1735      if ($_GET['tracking_user_overview_column']) {
1736          $orderby = $_GET['tracking_user_overview_column'];
1737      } else {
1738          $orderby = 0;
1739      }
1740      if ($is_western_name_order != api_is_western_name_order() && ($orderby == 1 || $orderby == 2)) {
1741          // Swapping the sorting column if name order for export is different than the common name order.
1742          $orderby = 3 - $orderby;
1743      }
1744      if ($_GET['tracking_user_overview_direction']) {
1745          $direction = $_GET['tracking_user_overview_direction'];
1746      } else {
1747          $direction = 'ASC';
1748      }
1749  
1750      $user_data = Tracking::get_user_data_tracking_overview($from, 1000, $orderby, $direction);
1751  
1752      // the first line of the csv file with the column headers
1753      $csv_row = array();
1754      $csv_row[] = get_lang('OfficialCode');
1755      if ($is_western_name_order) {
1756          $csv_row[] = get_lang('FirstName', '');
1757          $csv_row[] = get_lang('LastName', '');
1758      } else {
1759          $csv_row[] = get_lang('LastName', '');
1760          $csv_row[] = get_lang('FirstName', '');
1761      }
1762      $csv_row[] = get_lang('LoginName');
1763      $csv_row[] = get_lang('CourseCode');
1764      // the additional user defined fields (only those that were selected to be exported)
1765      require_once (api_get_path(LIBRARY_PATH).'usermanager.lib.php');
1766      $fields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
1767      if (is_array($_SESSION['additional_export_fields'])) {
1768          foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
1769              $csv_row[] = $fields[$extra_field_export][3];
1770              $field_names_to_be_exported[] = 'extra_'.$fields[$extra_field_export][1];
1771          }
1772      }
1773      $csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
1774      $csv_row[] = get_lang('AvgStudentsProgress', '');
1775      $csv_row[] = get_lang('AvgCourseScore', '');
1776      $csv_row[] = get_lang('AvgExercisesScore', '');
1777      $csv_row[] = get_lang('AvgMessages', '');
1778      $csv_row[] = get_lang('AvgAssignments', '');
1779      $csv_row[] = get_lang('TotalExercisesScoreObtained', '');
1780      $csv_row[] = get_lang('TotalExercisesScorePossible', '');
1781      $csv_row[] = get_lang('TotalExercisesAnswered', '');
1782      $csv_row[] = get_lang('TotalExercisesScorePercentage', '');
1783      $csv_row[] = get_lang('FirstLogin', '');
1784      $csv_row[] = get_lang('LatestLogin', '');
1785      $csv_content[] = $csv_row;
1786  
1787      // the other lines (the data)
1788      foreach ($user_data as $key => $user) {
1789          // getting all the courses of the user
1790          $sql = "SELECT * FROM $tbl_course_user WHERE user_id = '".Database::escape_string($user[4])."'";
1791          $result = Database::query($sql, __FILE__, __LINE__);
1792          while ($row = Database::fetch_row($result)) {
1793              $csv_row = array();
1794              // user official code
1795              $csv_row[] = $user[0];
1796              // user first|last name
1797              $csv_row[] = $user[1];
1798              // user last|first name
1799              $csv_row[] = $user[2];
1800              // user login name
1801              $csv_row[] = $user[3];
1802              // course code
1803              $csv_row[] = $row[0];
1804              // the additional defined user fields
1805              $extra_fields = get_user_overview_export_extra_fields($user[4]);
1806              if (is_array($field_names_to_be_exported)) {
1807                  foreach ($field_names_to_be_exported as $key => $extra_field_export) {
1808                      $csv_row[] = $extra_fields[$extra_field_export];
1809                  }
1810              }
1811              // time spent in the course
1812              $csv_row[] = api_time_to_hms(Tracking :: get_time_spent_on_the_course ($user[4], $row[0]));
1813              // student progress in course
1814              $csv_row[] = round(Tracking :: get_avg_student_progress ($user[4], $row[0]), 2);
1815              // student score
1816              $csv_row[] = round(Tracking :: get_avg_student_score ($user[4], $row[0]), 2);
1817              // student tes score
1818              $csv_row[] = round(Tracking :: get_avg_student_exercise_score ($user[4], $row[0]), 2);
1819              // student messages
1820              $csv_row[] = Tracking :: count_student_messages ($user[4], $row[0]);
1821              // student assignments
1822              $csv_row[] = Tracking :: count_student_assignments ($user[4], $row[0]);
1823              // student exercises results
1824              $exercises_results = exercises_results($user[4], $row[0]);
1825              $csv_row[] = $exercises_results['score_obtained'];
1826              $csv_row[] = $exercises_results['score_possible'];
1827              $csv_row[] = $exercises_results['questions_answered'];
1828              $csv_row[] = $exercises_results['percentage'];
1829              // first connection
1830              $csv_row[] = Tracking :: get_first_connection_date_on_the_course ($user[4], $row[0]);
1831              // last connection
1832              $csv_row[] = strip_tags(Tracking :: get_last_connection_date_on_the_course ($user[4], $row[0]));
1833  
1834              $csv_content[] = $csv_row;
1835          }
1836      }
1837      Export :: export_table_csv($csv_content, 'reporting_user_overview');
1838  }
1839  
1840  /**
1841   * Display a sortable table that contains an overview off all the reporting progress of all users and all courses the user is subscribed to
1842   *
1843   * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
1844   * @version Dokeos 1.8.6
1845   * @since October 2008
1846   */
1847  function display_tracking_user_overview() {
1848      display_user_overview_export_options();
1849  
1850      $t_head .= '    <table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
1851      $t_head .= '    <caption>'.get_lang('CourseInformation').'</caption>';
1852      $t_head .=        '<tr>';
1853      $t_head .= '        <th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>';
1854      $t_head .= '        <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>';
1855      $t_head .= '        <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>';
1856      $t_head .= '        <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>';
1857      //$t_head .= '        <th><div style="width:40px">'.get_lang('AvgExercisesScore').'</div></th>';
1858      $t_head .= '        <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgMessages'), 6, true).'</span></th>';
1859      $t_head .= '        <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgAssignments'), 6, true).'</span></th>';
1860      $t_head .= '        <th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>';
1861      //$t_head .= '        <th><div>'.get_lang('TotalExercisesScorePossible').'</div></th>';
1862      $t_head .= '        <th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>';
1863      //$t_head .= '        <th><div>'.get_lang('TotalExercisesScorePercentage').'</div></th>';
1864      //$t_head .= '        <th><div style="width:60px">'.get_lang('FirstLogin').'</div></th>';
1865      $t_head .= '        <th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>';
1866      $t_head .= '    </tr></table>';
1867  
1868      $addparams = array('view' => 'admin', 'display' => 'useroverview');
1869  
1870      $table = new SortableTable('tracking_user_overview', 'get_number_of_users_tracking_overview', 'get_user_data_tracking_overview', 0);
1871      $table->additional_parameters = $addparams;
1872  
1873      $table->set_header(0, get_lang('OfficialCode'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
1874      if (api_is_western_name_order()) {
1875          $table->set_header(1, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
1876          $table->set_header(2, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
1877      } else {
1878          $table->set_header(1, get_lang('LastName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
1879          $table->set_header(2, get_lang('FirstName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
1880      }
1881      $table->set_header(3, get_lang('LoginName'), true, array('style' => 'font-size:8pt'), array('style' => 'font-size:8pt'));
1882      $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;'));
1883      $table->set_column_filter(4, 'course_info_tracking_filter');
1884      $table->display();
1885  }
1886  /**
1887   * get the numer of users of the platform
1888   *
1889   * @return integer
1890   *
1891   * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
1892   * @version Dokeos 1.8.6
1893   * @since October 2008
1894   */
1895  function get_number_of_users_tracking_overview() {
1896      // database table definition
1897      $main_user_table = Database :: get_main_table(TABLE_MAIN_USER);
1898  
1899      // query
1900      $sql = 'SELECT user_id FROM '.$main_user_table;
1901      $result = Database::query($sql, __FILE__, __LINE__);
1902  
1903      // return the number of results
1904      return Database::num_rows($result);
1905  }
1906  
1907  /**
1908   * get all the data for the sortable table of the reporting progress of all users and all the courses the user is subscribed to.
1909   *
1910   * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
1911   * @version Dokeos 1.8.6
1912   * @since October 2008
1913   */
1914  function get_user_data_tracking_overview($from, $number_of_items, $column, $direction) {
1915      // database table definition
1916      $main_user_table = Database :: get_main_table(TABLE_MAIN_USER);
1917      global $export_csv;
1918      if ($export_csv) {
1919          $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
1920      } else {
1921          $is_western_name_order = api_is_western_name_order();
1922      }
1923      $sql = "SELECT
1924                  official_code     AS col0,
1925                  ".($is_western_name_order ? "
1926                  firstname         AS col1,
1927                  lastname         AS col2,
1928                  " : "
1929                  lastname         AS col1,
1930                  firstname         AS col2,
1931                  ").
1932                  "username        AS col3,
1933                  user_id         AS col4
1934              FROM
1935                  $main_user_table
1936              ";
1937      $sql .= " ORDER BY col$column $direction ";
1938      $sql .= " LIMIT $from,$number_of_items";
1939      $result = Database::query($sql, __FILE__, __LINE__);
1940      $return = array ();
1941      while ($user = Database::fetch_row($result)) {
1942          $return[] = $user;
1943      }
1944      return $return;
1945  }
1946  
1947  /**
1948   * Creates a small table in the last column of the table with the user overview
1949   *
1950   * @param integer $user_id the id of the user
1951   * @param array $url_params additonal url parameters
1952   * @param array $row the row information (the other columns)
1953   * @return html code
1954   *
1955   * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
1956   * @version Dokeos 1.8.6
1957   * @since October 2008
1958   */
1959  function course_info_tracking_filter($user_id, $url_params, $row) {
1960      // the table header
1961      $return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
1962      /*$return .= '    <tr>';
1963      $return .= '        <th>'.get_lang('Course').'</th>';
1964      $return .= '        <th>'.get_lang('AvgTimeSpentInTheCourse').'</th>';
1965      $return .= '        <th>'.get_lang('AvgStudentsProgress').'</th>';
1966      $return .= '        <th>'.get_lang('AvgCourseScore').'</th>';
1967      $return .= '        <th>'.get_lang('AvgExercisesScore').'</th>';
1968      $return .= '        <th>'.get_lang('AvgMessages').'</th>';
1969      $return .= '        <th>'.get_lang('AvgAssignments').'</th>';
1970      $return .= '        <th>'.get_lang('TotalExercisesScoreObtained').'</th>';
1971      $return .= '        <th>'.get_lang('TotalExercisesScorePossible').'</th>';
1972      $return .= '        <th>'.get_lang('TotalExercisesAnswered').'</th>';
1973      $return .= '        <th>'.get_lang('TotalExercisesScorePercentage').'</th>';
1974      $return .= '        <th>'.get_lang('FirstLogin').'</th>';
1975      $return .= '        <th>'.get_lang('LatestLogin').'</th>';
1976      $return .= '    </tr>';*/
1977  
1978      // database table definition
1979      $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
1980  
1981      // getting all the courses of the user
1982      $sql = "SELECT * FROM $tbl_course_user WHERE user_id = '".Database::escape_string($user_id)."'";
1983      $result = Database::query($sql, __FILE__, __LINE__);
1984      while ($row = Database::fetch_row($result)) {
1985          $return .= '<tr>';
1986          // course code
1987          $return .= '    <td width="157px" >'.cut($row[0], 20, true).'</td>';
1988          // time spent in the course
1989          $return .= '    <td><div>'.api_time_to_hms(Tracking :: get_time_spent_on_the_course($user_id, $row[0])).'</div></td>';
1990          // student progress in course
1991          $return .= '    <td><div>'.round(Tracking :: get_avg_student_progress($user_id, $row[0]), 2).'</div></td>';
1992          // student score
1993          $return .= '    <td><div>'.round(Tracking :: get_avg_student_score($user_id, $row[0]), 2).'</div></td>';
1994          // student tes score
1995          //$return .= '    <td><div style="width:40px">'.round(Tracking :: get_avg_student_exercise_score ($user_id, $row[0]),2).'%</div></td>';
1996          // student messages
1997          $return .= '    <td><div>'.Tracking :: count_student_messages($user_id, $row[0]).'</div></td>';
1998          // student assignments
1999          $return .= '    <td><div>'.Tracking :: count_student_assignments($user_id, $row[0]).'</div></td>';
2000          // student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
2001          $exercises_results = exercises_results($user_id, $row[0]);
2002          $return .= '    <td width="105px"><div>'.(is_null($exercises_results['percentage']) ? '' : $exercises_results['score_obtained'].'/'.$exercises_results['score_possible'].' ( '.$exercises_results['percentage'].'% )').'</div></td>';
2003          //$return .= '    <td><div>'.$exercises_results['score_possible'].'</div></td>';
2004          $return .= '    <td><div>'.$exercises_results['questions_answered'].'</div></td>';
2005          //$return .= '    <td><div>'.$exercises_results['percentage'].'% </div></td>';
2006          // first connection
2007          //$return .= '    <td width="60px">'.Tracking :: get_first_connection_date_on_the_course ($user_id, $row[0]).'</td>';
2008          // last connection
2009          $return .= '    <td><div>'.Tracking :: get_last_connection_date_on_the_course ($user_id, $row[0]).'</div></td>';
2010          $return .= '<tr>';
2011      }
2012      $return .= '</table>';
2013      return $return;
2014  }
2015  
2016  /**
2017   * Get general information about the exercise performance of the user
2018   * the total obtained score (all the score on all the questions)
2019   * the maximum score that could be obtained
2020   * the number of questions answered
2021   * the success percentage
2022   *
2023   * @param integer $user_id the id of the user
2024   * @param string $course_code the course code
2025   *
2026   * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
2027   * @version Dokeos 1.8.6
2028   * @since November 2008
2029   */
2030  function exercises_results($user_id, $course_code) {
2031      $questions_answered = 0;
2032      $sql = 'SELECT exe_result , exe_weighting
2033          FROM '.Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES)."
2034          WHERE exe_cours_id = '".Database::escape_string($course_code)."'
2035          AND exe_user_id = '".Database::escape_string($user_id)."'";
2036      $result = Database::query($sql, __FILE__, __LINE__);
2037      $score_obtained = 0;
2038      $score_possible = 0;
2039      $questions_answered = 0;
2040      while ($row = Database::fetch_array($result)) {
2041          $score_obtained += $row['exe_result'];
2042          $score_possible += $row['exe_weighting'];
2043          $questions_answered ++;
2044      }
2045  
2046      if ($score_possible != 0) {
2047          $percentage = round(($score_obtained / $score_possible * 100), 2);
2048      } else {
2049          $percentage = null;
2050      }
2051  
2052      return array('score_obtained' => $score_obtained, 'score_possible' => $score_possible, 'questions_answered' => $questions_answered, 'percentage' => $percentage);
2053  }
2054  
2055  /**
2056   * Displays a form with all the additionally defined user fields of the profile
2057   * and give you the opportunity to include these in the CSV export
2058   *
2059   * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
2060   * @version Dokeos 1.8.6
2061   * @since November 2008
2062   */
2063  function display_user_overview_export_options() {
2064      // include the user manager and formvalidator library
2065      require_once api_get_path(LIBRARY_PATH).'usermanager.lib.php';
2066      require_once api_get_path(LIBRARY_PATH).'formvalidator/FormValidator.class.php';
2067  
2068      if ($_GET['export'] == 'options') {
2069          // get all the defined extra fields
2070          $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC', false);
2071  
2072          // creating the form with all the defined extra fields
2073          $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']));
2074          foreach ($extrafields as $key => $extra) {
2075              $form->addElement('checkbox', 'extra_export_field'.$extra[0], '', $extra[3]);
2076          }
2077          $form->addElement('style_submit_button','submit', get_lang('Ok'),'class="save"' );
2078  
2079          // setting the default values for the form that contains all the extra fields
2080          if (is_array($_SESSION['additional_export_fields'])) {
2081              foreach ($_SESSION['additional_export_fields'] as $key => $value) {
2082                  $defaults['extra_export_field'.$value] = 1;
2083              }
2084          }
2085          $form->setDefaults($defaults);
2086  
2087          if ($form->validate()) {
2088              // exporting the form values
2089              $values = $form->exportValues();
2090  
2091              // re-initialising the session that contains the additional fields that need to be exported
2092              $_SESSION['additional_export_fields'] = array();
2093  
2094              // adding the fields that are checked to the session
2095              $message = '';
2096              foreach ($values as $field_ids => $value) {
2097                  if ($value == 1 && strstr($field_ids,'extra_export_field')) {
2098                      $_SESSION['additional_export_fields'][] = str_replace('extra_export_field', '', $field_ids);
2099                  }
2100              }
2101  
2102              // adding the fields that will be also exported to a message string
2103              if (is_array($_SESSION['additional_export_fields'])) {
2104                  foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
2105                      $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
2106                  }
2107              }
2108  
2109              // Displaying a feedback message
2110              if (!empty($_SESSION['additional_export_fields'])) {
2111                  Display::display_confirmation_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', false);
2112              } else  {
2113                  Display::display_confirmation_message(get_lang('NoAdditionalFieldsWillBeExported'), false);
2114              }
2115              $message = '';
2116          } else {
2117              $form->display();
2118          }
2119      } else {
2120          if (!empty($_SESSION['additional_export_fields'])) {
2121              // get all the defined extra fields
2122              $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
2123  
2124              foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
2125                  $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>';
2126              }
2127  
2128              Display::display_normal_message(get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', false);
2129              $message = '';
2130          }
2131      }
2132  }
2133  
2134  /**
2135   * Get data for courses list in sortable with pagination 
2136   * @return array
2137   */
2138  function get_course_data($from = null, $number_of_items = null, $column = null, $direction = null, $add_limit = true) {
2139      
2140      global $courses, $csv_content, $charset ;
2141      global $tbl_course, $tbl_course_user, $tbl_track_cours_access, $tbl_session_course_user;
2142      
2143      $a_course_students  = array();    
2144      $course_data = $chart_data = array();    
2145      $arr_course = $courses;    
2146      foreach ($arr_course as &$cours) {            
2147          $cours = "'{$cours[course_code]}'";
2148      }
2149      
2150      // get all courses with limit
2151      $sql = "SELECT course.code as col1, course.title as col2                 
2152              FROM $tbl_course course             
2153              WHERE course.code IN (".implode(',',$arr_course).")";     
2154      if (!in_array($direction, array('ASC','DESC'))) $direction = 'ASC';
2155      
2156      $column = intval($column);
2157      $from = intval($from);
2158      $number_of_items = intval($number_of_items);
2159      $sql .= " ORDER BY col$column $direction ";
2160      if ($add_limit == true) {
2161        $sql .= " LIMIT $from,$number_of_items";
2162      }
2163  
2164      $res = Database::query($sql, __FILE__, __LINE__);                
2165      while ($row_course = Database::fetch_row($res)) {
2166  
2167          $course_code = $row_course[0];
2168          $course_info = api_get_course_info($course_code);
2169          $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;        
2170          $tbl_item_property         = Database :: get_course_table(TABLE_ITEM_PROPERTY, $course_info['dbName']);
2171          $tbl_forum_post          = Database :: get_course_table(TABLE_FORUM_POST, $course_info['dbName']);
2172          $tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW, $course_info['dbName']);    
2173          $tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN, $course_info['dbName']);
2174          
2175          // students directly subscribed to the course
2176          $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'
2177                    UNION DISTINCT SELECT id_user as user_id FROM $tbl_session_course_user srcu WHERE  srcu. course_code='$course_code'";                    
2178          $rs = Database::query($sql, __FILE__, __LINE__);
2179          $users = array();        
2180          while ($row = Database::fetch_array($rs)) {        
2181              $users[] = $row['user_id'];                             
2182          }
2183          if (count($users) > 0) {
2184              $nb_students_in_course = count($users);            
2185              $avg_assignments_in_course = Tracking::count_student_assignments($users, $course_code);
2186              $avg_messages_in_course    = Tracking::count_student_messages($users, $course_code);
2187              $avg_time_spent_in_course  = $time_for_chart = Tracking::get_time_spent_on_the_course($users, $course_code);            
2188              $avg_progress_in_course = Tracking::get_avg_student_progress($users, $course_code);        
2189              $avg_score_in_course = Tracking :: get_avg_student_score($users, $course_code);
2190              $avg_score_in_exercise = Tracking::get_avg_student_exercise_score($users, $course_code);
2191                          
2192              $avg_time_spent_in_course = api_time_to_hms($avg_time_spent_in_course / $nb_students_in_course);
2193              $avg_progress_in_course = round($avg_progress_in_course / $nb_students_in_course, 2);
2194              $avg_score_in_course = round($avg_score_in_course / $nb_students_in_course, 2);
2195              $avg_score_in_exercise = round($avg_score_in_exercise / $nb_students_in_course, 2);        
2196          } else {
2197              $avg_time_spent_in_course = null;
2198              $avg_progress_in_course = null;
2199              $avg_score_in_course = null;
2200              $avg_score_in_exercise = null;
2201              $avg_messages_in_course = null;
2202              $avg_assignments_in_course = null;
2203              $time_for_chart = null;
2204          }
2205          $table_row = array();        
2206          $table_row[] = $row_course[1];
2207          $table_row[] = $nb_students_in_course;
2208          $table_row[] = $avg_time_spent_in_course;
2209          $table_row[] = is_null($avg_progress_in_course) ? '' : $avg_progress_in_course.'%';
2210          $table_row[] = is_null($avg_score_in_course) ? '' : $avg_score_in_course.'%';
2211          $table_row[] = is_null($avg_score_in_exercise) ? '' : $avg_score_in_exercise.'%';
2212          $table_row[] = $avg_messages_in_course;
2213          $table_row[] = $avg_assignments_in_course;
2214          //set the "from" value to know if I access the Reporting by the Dokeos tab or the course link
2215          $table_row[] = '<center><a href="../tracking/courseLog.php?cidReq='.$course_code.'&studentlist=true&from=myspace">'.Display::return_icon('pixel.gif','',array('class'=>'actionplaceholdericon actionstatisticsdetails','border'=>'0"')).'</a></center>';
2216          $csv_content[] = array(
2217              api_html_entity_decode($row_course[1], ENT_QUOTES, $charset),
2218              $nb_students_in_course,
2219              $avg_time_spent_in_course,
2220              is_null($avg_progress_in_course) ? null : $avg_progress_in_course.'%',
2221              is_null($avg_score_in_course) ? null : $avg_score_in_course.'%',
2222              is_null($avg_score_in_exercise) ? null : $avg_score_in_exercise.'%',
2223              $avg_messages_in_course,
2224              $avg_assignments_in_course,
2225          );
2226          $course_data[] = $table_row;            
2227          $chart_data[$row_course[0]] = array('title'=>$row_course[1], 'progress'=>intval($avg_progress_in_course), 'score'=>intval($avg_score_in_course), 'time'=>intval($time_for_chart));    
2228      }
2229      $GLOBALS['chart_data'] = $chart_data;
2230      return $course_data;
2231  }
2232  
2233  
2234  }
2235  ?>

title

Description

title

Description

title

Description

title

title

Body