Dokeos PHP Cross Reference Learning Management Systems

Source: /main/inc/lib/usermanager.lib.php - 3171 lines - 134646 bytes - Summary - Text - Print

Description: This library provides functions for user management. Include/require it in your code to use its functionality.

   1  <?php // $Id: usermanager.lib.php 22378 2009-07-26 19:58:38Z yannoo $
   2  /* For licensing terms, see /dokeos_license.txt */
   3  /**
   4  ==============================================================================
   5  *    This library provides functions for user management.
   6  *    Include/require it in your code to use its functionality.
   7  *
   8  *    @package dokeos.library
   9  ==============================================================================
  10  */
  11  
  12  // Constants for user extra field types.
  13  define('USER_FIELD_TYPE_TEXT',                 1);
  14  define('USER_FIELD_TYPE_TEXTAREA',            2);
  15  define('USER_FIELD_TYPE_RADIO',                3);
  16  define('USER_FIELD_TYPE_SELECT',            4);
  17  define('USER_FIELD_TYPE_SELECT_MULTIPLE',    5);
  18  define('USER_FIELD_TYPE_DATE',                 6);
  19  define('USER_FIELD_TYPE_DATETIME',             7);
  20  define('USER_FIELD_TYPE_DOUBLE_SELECT',     8);
  21  define('USER_FIELD_TYPE_DIVIDER',             9);
  22  define('USER_FIELD_TYPE_TAG',                 10);
  23  define('USER_FIELD_TYPE_TIMEZONE',             11);
  24  define('USER_FIELD_TYPE_SOCIAL_PROFILE',     12);
  25  
  26  //User image sizes
  27  define('USER_IMAGE_SIZE_ORIGINAL',    1);
  28  define('USER_IMAGE_SIZE_BIG',         'big_');
  29  define('USER_IMAGE_SIZE_MEDIUM',     'medium_');
  30  define('USER_IMAGE_SIZE_SMALL',     'small_');
  31  
  32  
  33  // Relation type between users
  34  define('USER_UNKNOW',                    0);
  35  define('USER_RELATION_TYPE_UNKNOW',        1);
  36  define('USER_RELATION_TYPE_PARENT',        2); // should be deprecated is useless
  37  define('USER_RELATION_TYPE_FRIEND',        3);
  38  define('USER_RELATION_TYPE_GOODFRIEND',    4); // should be deprecated is useless
  39  define('USER_RELATION_TYPE_ENEMY',        5); // should be deprecated is useless
  40  define('USER_RELATION_TYPE_DELETED',    6);
  41  define('USER_RELATION_TYPE_RRHH',        7);
  42  
  43  class UserManager
  44  {
  45  	private function __construct () {
  46      }
  47  
  48      /**
  49        * Creates a new user for the platform
  50        * @author Hugues Peeters <peeters@ipm.ucl.ac.be>,
  51        *         Roan Embrechts <roan_embrechts@yahoo.com>
  52        *
  53        * @param    string    Firstname
  54        * @param    string    Lastname
  55        * @param    int       Status (1 for course tutor, 5 for student, 6 for anonymous)
  56        * @param    string    e-mail address
  57        * @param    string    Login
  58        * @param    string    Password
  59        * @param    string    Any official code (optional)
  60        * @param    string    User language    (optional)
  61        * @param    string    Phone number    (optional)
  62        * @param    string    Picture URI        (optional)
  63        * @param    string    Authentication source    (optional, defaults to 'platform', dependind on constant)
  64        * @param    string    Account expiration date (optional, defaults to '0000-00-00 00:00:00')
  65        * @param    int        Whether the account is enabled or disabled by default
  66         * @param    int        The user ID of the person who registered this user (optional, defaults to null)
  67         * @param    int        The department of HR in which the user is registered (optional, defaults to 0)
  68        * @return mixed   new user id - if the new user creation succeeds, false otherwise
  69        *
  70        * @desc The function tries to retrieve $_user['user_id'] from the global space.
  71        * if it exists, $_user['user_id'] is the creator id. If a problem arises,
  72        * it stores the error message in global $api_failureList
  73        */
  74  	public static function create_user($firstName, $lastName, $status, $email, $loginName, $password, $official_code = '', $language = '', $phone = '', $picture_uri = '', $auth_source = PLATFORM_AUTH_SOURCE, $expiration_date = '0000-00-00 00:00:00', $active = 1, $hr_dept_id = 0, $extra = null, $country_code = '', $civility = '') {
  75          global $_user, $userPasswordCrypted;
  76  
  77          $firstName = Security::remove_XSS($firstName);
  78          $lastName = Security::remove_XSS($lastName);
  79          $loginName = Security::remove_XSS($loginName);
  80          $phone = Security::remove_XSS($phone);
  81          // database table definition
  82          $table_user = Database::get_main_table(TABLE_MAIN_USER);
  83  
  84          // default langauge
  85          if ($language == '') {
  86              $language = api_get_setting('platformLanguage');
  87          }
  88  
  89          if ($_user['user_id']) {
  90              $creator_id = intval($_user['user_id']);
  91          } else {
  92              $creator_id = '';
  93          }
  94          // First check wether the login already exists
  95          if (!self::is_username_available($loginName)) {
  96              return api_set_failure('login-pass already taken');
  97          }
  98  
  99          // encrypt the password based on what has been determined as encryption mechanism in /main/inc/conf/configuration.php (variable $userPasswordCrypted)
 100          $password = api_get_encrypted_password($password);
 101  
 102          // should the user automatically create a new password when (s)he logs in for the first time?
 103          // this is determined by the force_password_change_account_creation setting
 104          if (api_get_setting('force_password_change_account_creation') == 'true'){
 105              $login_counter = -1;
 106          } else {
 107              $login_counter = 0;
 108          }
 109  
 110          $current_date = date('Y-m-d H:i:s', time());
 111          $sql = "INSERT INTO $table_user
 112                              SET lastname = '".Database::escape_string(trim($lastName))."',
 113                              firstname = '".Database::escape_string(trim($firstName))."',
 114                              username = '".Database::escape_string(trim($loginName))."',
 115                              status = '".Database::escape_string($status)."',
 116                              password = '".Database::escape_string($password)."',
 117                              email = '".Database::escape_string($email)."',
 118                              official_code    = '".Database::escape_string($official_code)."',
 119                              picture_uri     = '".Database::escape_string($picture_uri)."',
 120                              creator_id      = '".Database::escape_string($creator_id)."',
 121                              auth_source = '".Database::escape_string($auth_source)."',
 122                              phone = '".Database::escape_string($phone)."',
 123                              language = '".Database::escape_string($language)."',
 124                              registration_date = '".$current_date."',
 125                              expiration_date = '".Database::escape_string($expiration_date)."',
 126                              hr_dept_id = '".Database::escape_string($hr_dept_id)."',
 127                              active = '".Database::escape_string($active)."',
 128                              login_counter = '".$login_counter."',
 129                              country_code = '".$country_code."',
 130                              civility = '".$civility."'
 131                         ";
 132          $result = Database::query($sql, __FILE__, __LINE__);
 133          if ($result) {
 134                      $return = Database::insert_id();
 135                      global $_configuration;
 136                      require_once api_get_path(LIBRARY_PATH).'urlmanager.lib.php';
 137                      if ($_configuration['multiple_access_urls'] == true) {
 138                              if (api_get_current_access_url_id() != -1) {
 139                                      UrlManager::add_user_to_url($return, api_get_current_access_url_id());
 140                              } else {
 141                                      UrlManager::add_user_to_url($return, 1);
 142                              }
 143                      } else {
 144                              //we are adding by default the access_url_user table with access_url_id = 1
 145                              UrlManager::add_user_to_url($return, 1);
 146                      }
 147                      // add event to system log
 148                      $time = time();
 149                      $user_id_manager = api_get_user_id();
 150                      event_system(LOG_USER_CREATE, LOG_USER_ID, $return, $time, $user_id_manager);
 151          } else {
 152              //echo "false - failed" ;
 153              $return=false;
 154          }
 155  
 156          if (is_array($extra) && count($extra) > 0) {
 157              $res = true;
 158              foreach($extra as $fname => $fvalue) {
 159                  $res = $res && self::update_extra_field($return, $fname, $fvalue);
 160              }
 161          }
 162          return $return;
 163      }
 164  
 165      /**
 166       * Allow to register contact to social network
 167       * @param int user friend id
 168       * @param int user id
 169       * @param int relation between users see constants definition
 170       */
 171  	public static function relate_users ($friend_id,$my_user_id,$relation_type) {
 172          $tbl_my_friend = Database :: get_main_table(TABLE_MAIN_USER_REL_USER);
 173  
 174          $friend_id = intval($friend_id);
 175          $my_user_id = intval($my_user_id);
 176          $relation_type = intval($relation_type);
 177  
 178          $sql = 'SELECT COUNT(*) as count FROM ' . $tbl_my_friend . ' WHERE friend_user_id=' .$friend_id.' AND user_id='.$my_user_id.' AND relation_type <> '.USER_RELATION_TYPE_RRHH.' ';
 179          $result = Database::query($sql);
 180          $row = Database :: fetch_array($result, 'ASSOC');
 181          $current_date=date('Y-m-d H:i:s');
 182  
 183          if ($row['count'] == 0) {
 184              $sql_i = 'INSERT INTO ' . $tbl_my_friend . '(friend_user_id,user_id,relation_type,last_edit)values(' . $friend_id . ','.$my_user_id.','.$relation_type.',"'.$current_date.'");';
 185              Database::query($sql_i);
 186              return true;
 187          } else {
 188              $sql = 'SELECT COUNT(*) as count, relation_type  FROM ' . $tbl_my_friend . ' WHERE friend_user_id=' . $friend_id . ' AND user_id='.$my_user_id.' AND relation_type <> '.USER_RELATION_TYPE_RRHH.' ';
 189              $result = Database::query($sql);
 190              $row = Database :: fetch_array($result, 'ASSOC');
 191              if ($row['count'] == 1) {
 192                  //only for the case of a RRHH
 193                  if ($row['relation_type'] != $relation_type && $relation_type == USER_RELATION_TYPE_RRHH) {
 194                      $sql_i = 'INSERT INTO ' . $tbl_my_friend . '(friend_user_id,user_id,relation_type,last_edit)values(' . $friend_id . ','.$my_user_id.','.$relation_type.',"'.$current_date.'");';
 195                  } else {
 196                      $sql_i = 'UPDATE ' . $tbl_my_friend . ' SET relation_type='.$relation_type.' WHERE friend_user_id=' . $friend_id.' AND user_id='.$my_user_id;
 197                  }
 198                  Database::query($sql_i);
 199                  return true;
 200              } else {
 201                  return false;
 202              }
 203          }
 204      }
 205  
 206      /**
 207       * Can user be deleted?
 208       * This functions checks if there's a course in which the given user is the
 209       * only course administrator. If that is the case, the user can't be
 210       * deleted because the course would remain without a course admin.
 211       * @param int $user_id The user id
 212       * @return boolean true if user can be deleted
 213       */
 214  	public static function can_delete_user($user_id) {
 215          $table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
 216          if ($user_id != strval(intval($user_id))) return false;
 217          if ($user_id === false) return false;
 218          $sql = "SELECT * FROM $table_course_user WHERE status = '1' AND user_id = '".$user_id."'";
 219          $res = Database::query($sql, __FILE__, __LINE__);
 220          while ($course = Database::fetch_object($res)) {
 221              $sql = "SELECT user_id FROM $table_course_user WHERE status='1' AND course_code ='".Database::escape_string($course->course_code)."'";
 222              $res2 = Database::query($sql, __FILE__, __LINE__);
 223              if (Database::num_rows($res2) == 1) {
 224                  return false;
 225              }
 226          }
 227          return true;
 228      }
 229  
 230      /**
 231       * Delete a user from the platform
 232       * @param int $user_id The user id
 233       * @return boolean true if user is succesfully deleted, false otherwise
 234       */
 235  	public static function delete_user($user_id) {
 236          global $_configuration;
 237  
 238          if ($user_id != strval(intval($user_id))) return false;
 239          if ($user_id === false) return false;
 240  
 241          if (!self::can_delete_user($user_id)) {
 242              return false;
 243          }
 244          $table_user = Database :: get_main_table(TABLE_MAIN_USER);
 245          $table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
 246          $table_class_user = Database :: get_main_table(TABLE_MAIN_CLASS_USER);
 247          $table_course = Database :: get_main_table(TABLE_MAIN_COURSE);
 248          $table_admin = Database :: get_main_table(TABLE_MAIN_ADMIN);
 249          $table_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
 250          $table_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
 251  
 252          // Unsubscribe the user from all groups in all his courses
 253          $sql = "SELECT * FROM $table_course c, $table_course_user cu WHERE cu.user_id = '".$user_id."' AND c.code = cu.course_code";
 254          $res = Database::query($sql, __FILE__, __LINE__);
 255          while ($course = Database::fetch_object($res)) {
 256              $table_group = Database :: get_course_table(TABLE_GROUP_USER, $course->db_name);
 257              $sql = "DELETE FROM $table_group WHERE user_id = '".$user_id."'";
 258              Database::query($sql, __FILE__, __LINE__);
 259          }
 260  
 261          // Unsubscribe user from all classes
 262          $sql = "DELETE FROM $table_class_user WHERE user_id = '".$user_id."'";
 263          Database::query($sql, __FILE__, __LINE__);
 264  
 265          // Unsubscribe user from all courses
 266          $sql = "DELETE FROM $table_course_user WHERE user_id = '".$user_id."'";
 267          Database::query($sql, __FILE__, __LINE__);
 268  
 269          // Unsubscribe user from all courses in sessions
 270          $sql = "DELETE FROM $table_session_course_user WHERE id_user = '".$user_id."'";
 271          Database::query($sql, __FILE__, __LINE__);
 272  
 273          // Unsubscribe user from all sessions
 274          $sql = "DELETE FROM $table_session_user WHERE id_user = '".$user_id."'";
 275          Database::query($sql, __FILE__, __LINE__);
 276  
 277          // Delete user picture
 278          // TODO: Logic about api_get_setting('split_users_upload_directory') === 'true' , a user has 4 differnt sized photos to be deleted.
 279          $user_info = api_get_user_info($user_id);
 280          if (strlen($user_info['picture_uri']) > 0) {
 281              $img_path = api_get_path(SYS_CODE_PATH).'upload/users/'.$user_id.'/'.$user_info['picture_uri'];
 282              if (file_exists($img_path))
 283                  unlink($img_path);
 284          }
 285  
 286          // Delete the personal course categories
 287          $course_cat_table = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
 288          $sql = "DELETE FROM $course_cat_table WHERE user_id = '".$user_id."'";
 289          Database::query($sql, __FILE__, __LINE__);
 290  
 291          // Delete user from database
 292          $sql = "DELETE FROM $table_user WHERE user_id = '".$user_id."'";
 293          Database::query($sql, __FILE__, __LINE__);
 294  
 295          // Delete user from the admin table
 296          $sql = "DELETE FROM $table_admin WHERE user_id = '".$user_id."'";
 297          Database::query($sql, __FILE__, __LINE__);
 298  
 299          // Delete the personal agenda-items from this user
 300          $agenda_table = Database :: get_user_personal_table(TABLE_PERSONAL_AGENDA);
 301          $sql = "DELETE FROM $agenda_table WHERE user = '".$user_id."'";
 302          Database::query($sql, __FILE__, __LINE__);
 303  
 304          $gradebook_results_table = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_RESULT);
 305          $sql = 'DELETE FROM '.$gradebook_results_table.' WHERE user_id = '.$user_id;
 306          Database::query($sql, __FILE__, __LINE__);
 307  
 308          $user = Database::fetch_array($res);
 309          $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
 310          $sqlv = "DELETE FROM $t_ufv WHERE user_id = $user_id";
 311          $resv = Database::query($sqlv, __FILE__, __LINE__);
 312  
 313          if ($_configuration['multiple_access_urls']) {
 314              require_once api_get_path(LIBRARY_PATH).'urlmanager.lib.php';
 315              $url_id = 1;
 316              if (api_get_current_access_url_id() != -1) {
 317                  $url_id = api_get_current_access_url_id();
 318              }
 319              UrlManager::delete_url_rel_user($user_id, $url_id);
 320          }
 321  
 322          if (api_get_setting('allow_social_tool')=='true' ) {
 323              require_once api_get_path(LIBRARY_PATH).'social.lib.php';
 324              //Delete user from groups
 325  
 326              //Delete from user friend lists
 327              SocialManager::removed_friend($user_id,true);
 328          }
 329          // add event to system log
 330          $time = time();
 331          $user_id_manager = api_get_user_id();
 332          event_system(LOG_USER_DELETE, LOG_USER_ID, $user_id, $time, $user_id_manager);
 333  
 334          return true;
 335      }
 336  
 337      /**
 338       * Update user information with new openid
 339       * @param int $user_id
 340       * @param string $openid
 341       * @return boolean true if the user information was updated
 342       */
 343  	public static function update_openid($user_id, $openid) {
 344          $table_user = Database :: get_main_table(TABLE_MAIN_USER);
 345          if ($user_id != strval(intval($user_id))) return false;
 346          if ($user_id === false) return false;
 347          $sql = "UPDATE $table_user SET
 348                  openid='".Database::escape_string($openid)."'";
 349          $sql .=    " WHERE user_id='$user_id'";
 350          return Database::query($sql, __FILE__, __LINE__);
 351      }
 352  
 353      /**
 354       * Update user information
 355       * @param int $user_id
 356       * @param string $firstname
 357       * @param string $lastname
 358       * @param string $username
 359       * @param string $password
 360       * @param string $auth_source
 361       * @param string $email
 362       * @param int $status
 363       * @param string $official_code
 364       * @param string $phone
 365       * @param string $picture_uri
 366       * @param int The user ID of the person who registered this user (optional, defaults to null)
 367       * @param int The department of HR in which the user is registered (optional, defaults to 0)
 368       * @param    array    A series of additional fields to add to this user as extra fields (optional, defaults to null)
 369       * @return boolean true if the user information was updated
 370       */
 371  	public static function update_user($user_id, $firstname, $lastname, $username, $password = null, $auth_source = null, $email, $status, $official_code, $phone, $picture_uri, $expiration_date, $active, $creator_id = null, $hr_dept_id = 0, $extra = null, $language = 'english', $country_code = '', $civility = '') {
 372          global $userPasswordCrypted;
 373          if ($user_id != strval(intval($user_id))) return false;
 374          if ($user_id === false) return false;
 375          $table_user = Database :: get_main_table(TABLE_MAIN_USER);
 376          $sql = "UPDATE $table_user SET
 377                  lastname='".Database::escape_string($lastname)."',
 378                  firstname='".Database::escape_string($firstname)."',
 379                  username='".Database::escape_string($username)."',
 380                  language='".Database::escape_string($language)."',";
 381          if (!is_null($password)) {
 382              //$password = $userPasswordCrypted ? md5($password) : $password;
 383              $password = api_get_encrypted_password($password);
 384              $sql .= " password='".Database::escape_string($password)."',";
 385          }
 386          if (!is_null($auth_source)) {
 387              $sql .=    " auth_source='".Database::escape_string($auth_source)."',";
 388          }
 389          $sql .=    "
 390                              email='".Database::escape_string($email)."',
 391                              status='".Database::escape_string($status)."',
 392                              official_code='".Database::escape_string($official_code)."',
 393                              phone='".Database::escape_string($phone)."',
 394                              picture_uri='".Database::escape_string($picture_uri)."',
 395                              expiration_date='".Database::escape_string($expiration_date)."',
 396                              active='".Database::escape_string($active)."',
 397                              hr_dept_id=".intval($hr_dept_id).",
 398                              country_code='".Database::escape_string($country_code)."',
 399                              civility='".Database::escape_string($civility)."'
 400                            ";
 401          if (!is_null($creator_id)) {
 402              $sql .= ", creator_id='".Database::escape_string($creator_id)."'";
 403          }
 404          $sql .=    " WHERE user_id='$user_id'";
 405          $return = Database::query($sql, __FILE__, __LINE__);
 406          if (is_array($extra) && count($extra) > 0) {
 407              $res = true;
 408              foreach($extra as $fname => $fvalue) {
 409                  $res = $res && self::update_extra_field($user_id,$fname,$fvalue);
 410              }
 411          }
 412                  
 413                  // add event to system log
 414                  $time = time();
 415                  $user_id_manager = api_get_user_id();
 416                  event_system(LOG_USER_UPDATE, LOG_USER_ID, $user_id, $time, $user_id_manager);
 417                  
 418          return $return;
 419      }
 420  
 421      /**
 422       * Check if a username is available
 423       * @param string the wanted username
 424       * @return boolean true if the wanted username is available
 425       */
 426  	public static function is_username_available($username) {
 427          $table_user = Database :: get_main_table(TABLE_MAIN_USER);
 428          $sql = "SELECT username FROM $table_user WHERE username = '".Database::escape_string($username)."'";
 429          $res = Database::query($sql, __FILE__, __LINE__);
 430          return Database::num_rows($res) == 0;
 431      }
 432  
 433      /**
 434       * Creates a username using person's names, i.e. creates jmontoya from Julio Montoya.
 435       * @param string $firstname                The first name of the user.
 436       * @param string $lastname                The last name of the user.
 437       * @param string $language (optional)    The language in which comparison is to be made. If language is omitted, interface language is assumed then.
 438       * @param string $encoding (optional)    The character encoding for the input names. If it is omitted, the platform character set will be used by default.
 439       * @return string                        Suggests a username that contains only ASCII-letters and digits, without check for uniqueness within the system.
 440       * @author Julio Montoya Armas
 441       * @author Ivan Tcholakov, 2009 - rework about internationalization.
 442       */
 443  	public static function create_username($firstname, $lastname, $language = null, $encoding = null) {
 444          if (is_null($encoding)) {
 445              $encoding = api_get_system_encoding();
 446          }
 447          if (is_null($language)) {
 448              $language = api_get_interface_language();
 449          }
 450          $firstname = substr(preg_replace(USERNAME_PURIFIER, '', api_transliterate($firstname, '', $encoding)), 0, 1); // The first letter only.
 451          $lastname = preg_replace(USERNAME_PURIFIER, '', api_transliterate($lastname, '', $encoding));
 452          $username = api_is_western_name_order(null, $language) ? $firstname.$lastname : $lastname.$firstname;
 453          if (empty($username)) {
 454              $username = 'user';
 455          }
 456          return strtolower(substr($username, 0, USERNAME_MAX_LENGTH - 3));
 457      }
 458  
 459      /**
 460       * Creates a unique username, using:
 461       * 1. the first name and the last name of a user;
 462       * 2. an already created username but not checked for uniqueness yet.
 463       * @param string $firstname                The first name of a given user. If the second parameter $lastname is NULL, then this
 464       * parameter is treated as username which is to be checked for uniqueness and to be modified when it is necessary.
 465       * @param string $lastname                The last name of the user.
 466       * @param string $language (optional)    The language in which comparison is to be made. If language is omitted, interface language is assumed then.
 467       * @param string $encoding (optional)    The character encoding for the input names. If it is omitted, the platform character set will be used by default.
 468       * @return string                        Returns a username that contains only ASCII-letters and digits, and that is unique within the system.
 469       * Note: When the method is called several times with same parameters, its results look like the following sequence: ivan, ivan2, ivan3, ivan4, ...
 470       * @author Ivan Tcholakov, 2009
 471       */
 472  	public static function create_unique_username($firstname, $lastname = null, $language = null, $encoding = null) {
 473          if (is_null($lastname)) {
 474              // In this case the actual input parameter $firstname should contain ASCII-letters and digits only.
 475              // For making this method tolerant of mistakes, let us transliterate and purify the suggested input username anyway.
 476              // So, instead of the sentence $username = $firstname; we place the following:
 477              $username = strtolower(preg_replace(USERNAME_PURIFIER, '', api_transliterate($firstname, '', $encoding)));
 478          } else {
 479              $username = self::create_username($firstname, $lastname, $language, $encoding);
 480          }
 481          if (!self::is_username_available($username)) {
 482              $i = 2;
 483              $temp_username = substr($username, 0, USERNAME_MAX_LENGTH - strlen((string)$i)).$i;
 484              while (!self::is_username_available($temp_username)) {
 485                  $i++;
 486                  $temp_username = substr($username, 0, USERNAME_MAX_LENGTH - strlen((string)$i)).$i;
 487              }
 488              $username = $temp_username;
 489          }
 490          return $username;
 491      }
 492  
 493      /**
 494       * Modifies a given username accordingly to the specification for valid characters and length.
 495       * @param $username string                The input username.
 496       * @param bool $strict (optional)        When this flag is TRUE, the result is guaranteed for full compliance, otherwise compliance may be partial. The default value is FALSE.
 497       * @param string $encoding (optional)    The character encoding for the input names. If it is omitted, the platform character set will be used by default.
 498       * @return string                        The resulting purified username.
 499       */
 500  	public function purify_username($username, $strict = false, $encoding = null) {
 501          if ($strict) {
 502              // 1. Conversion of unacceptable letters (latinian letters with accents for example) into ASCII letters in order they not to be totally removed.
 503              // 2. Applying the strict purifier.
 504              // 3. Length limitation.
 505              return substr(preg_replace(USERNAME_PURIFIER, '', api_transliterate($username, '', $encoding)), 0, USERNAME_MAX_LENGTH);
 506          }
 507          // 1. Applying the shallow purifier.
 508          // 2. Length limitation.
 509          return substr(preg_replace(USERNAME_PURIFIER_SHALLOW, '', $username), 0, USERNAME_MAX_LENGTH);
 510      }
 511  
 512      /**
 513       * Checks whether a given username matches to the specification strictly. The empty username is assumed here as invalid.
 514       * Mostly this function is to be used in the user interface built-in validation routines for providing feedback while usernames are enterd manually.
 515       * @param string $username                The input username.
 516       * @param string $encoding (optional)    The character encoding for the input names. If it is omitted, the platform character set will be used by default.
 517       * @return bool                            Returns TRUE if the username is valid, FALSE otherwise.
 518       */
 519  	public function is_username_valid($username, $encoding = null) {
 520          return !empty($username) && $username == self::purify_username($username, true);
 521      }
 522  
 523      /**
 524       * Checks whether a username is empty. If the username contains whitespace characters, such as spaces, tabulators, newlines, etc.,
 525       * it is assumed as empty too. This function is safe for validation unpurified data (during importing).
 526       * @param string $username                The given username.
 527       * @return bool                            Returns TRUE if length of the username exceeds the limit, FALSE otherwise.
 528       */
 529  	public static function is_username_empty($username) {
 530          return (strlen(self::purify_username($username, false)) == 0);
 531      }
 532  
 533      /**
 534       * Checks whether a username is too long or not.
 535       * @param string $username                The given username, it should contain only ASCII-letters and digits.
 536       * @return bool                            Returns TRUE if length of the username exceeds the limit, FALSE otherwise.
 537       */
 538  	public static function is_username_too_long($username) {
 539          return (strlen($username) > USERNAME_MAX_LENGTH);
 540      }
 541  
 542      /**
 543      * Get a list of users of which the given conditions match with an = 'cond'
 544      * @param array $conditions a list of condition (exemple : status=>STUDENT)
 545      * @param array $order_by a list of fields on which sort
 546      * @return array An array with all users of the platform.
 547      * @todo optional course code parameter, optional sorting parameters...
 548      */
 549  	public static function get_user_list($conditions = array(), $order_by = array()) {
 550          $user_table = Database :: get_main_table(TABLE_MAIN_USER);
 551          $return_array = array();
 552          $sql_query = "SELECT * FROM $user_table";
 553          if (count($conditions) > 0) {
 554              $sql_query .= ' WHERE ';
 555              foreach ($conditions as $field => $value) {
 556                  $field = Database::escape_string($field);
 557                  $value = Database::escape_string($value);
 558                  $sql_query .= $field.' = '.$value;
 559              }
 560          }
 561          if (count($order_by) > 0) {
 562              $sql_query .= ' ORDER BY '.Database::escape_string(implode(',', $order_by));
 563          }
 564          $sql_result = Database::query($sql_query, __FILE__, __LINE__);
 565          while ($result = Database::fetch_array($sql_result)) {
 566              $return_array[] = $result;
 567          }
 568          return $return_array;
 569      }
 570  
 571       /**
 572      * Gets a list of users by the session_id
 573      * @param integer $session_id the session ID
 574      * @return array An array with all users of a training session.
 575      */
 576  	public static function get_subscribed_users_to_a_session ($session_id) {
 577          $session_rel_user_table = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
 578          $return_array = array();
 579          $sql_query = "SELECT id_user FROM $session_rel_user_table WHERE id_session= '".Database::escape_string($session_id)."'";
 580  
 581          $res = Database::query($sql_query, __FILE__, __LINE__);
 582          while ($result = Database::fetch_array($res, 'ASSOC')) {
 583              $user_id = $result['id_user'];
 584              $return_array[] = api_get_user_info($user_id);
 585          }
 586          return $return_array;
 587      }
 588  
 589      /**
 590      * Get a list of users of which the given conditions match with a LIKE '%cond%'
 591      * @param array $conditions a list of condition (exemple : status=>STUDENT)
 592      * @param array $order_by a list of fields on which sort
 593      * @return array An array with all users of the platform.
 594      * @todo optional course code parameter, optional sorting parameters...
 595      */
 596      function get_user_list_like($conditions = array(), $order_by = array()) {
 597          $user_table = Database :: get_main_table(TABLE_MAIN_USER);
 598          $return_array = array();
 599          $sql_query = "SELECT * FROM $user_table";
 600          if (count($conditions) > 0) {
 601              $sql_query .= ' WHERE ';
 602              foreach ($conditions as $field => $value) {
 603                  $field = Database::escape_string($field);
 604                  $value = Database::escape_string($value);
 605                  $sql_query .= $field.' LIKE \'%'.$value.'%\'';
 606              }
 607          }
 608          if (count($order_by) > 0) {
 609              $sql_query .= ' ORDER BY '.Database::escape_string(implode(',', $order_by));
 610          }
 611          $sql_result = Database::query($sql_query, __FILE__, __LINE__);
 612          while ($result = Database::fetch_array($sql_result)) {
 613              $return_array[] = $result;
 614          }
 615          return $return_array;
 616      }
 617  
 618      /**
 619       * Get user information
 620       * @param     string     The username
 621       * @return array All user information as an associative array
 622       */
 623  	public static function get_user_info($username) {
 624          $user_table = Database :: get_main_table(TABLE_MAIN_USER);
 625          $username = Database::escape_string($username);
 626          $sql = "SELECT * FROM $user_table WHERE username='".$username."'";
 627          $res = Database::query($sql, __FILE__, __LINE__);
 628          if (Database::num_rows($res) > 0) {
 629              return Database::fetch_array($res);
 630          }
 631          return false;
 632      }
 633  
 634      /**
 635       * Get user information
 636       * @param    string    The id
 637       * @param    boolean    Whether to return the user's extra fields (defaults to false)
 638       * @return    array     All user information as an associative array
 639       */
 640  	public static function get_user_info_by_id($user_id, $user_fields = false) {
 641          $user_id = intval($user_id);
 642          $user_table = Database :: get_main_table(TABLE_MAIN_USER);
 643          $sql = "SELECT * FROM $user_table WHERE user_id=".$user_id;
 644          $res = Database::query($sql, __FILE__, __LINE__);
 645          if (Database::num_rows($res) > 0) {
 646              $user = Database::fetch_array($res);
 647              $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
 648              $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
 649              $sqlf = "SELECT * FROM $t_uf ORDER BY field_order";
 650              $resf = Database::query($sqlf, __FILE__, __LINE__);
 651              if (Database::num_rows($resf) > 0) {
 652                  while ($rowf = Database::fetch_array($resf)) {
 653                      $sqlv = "SELECT * FROM $t_ufv WHERE field_id = ".$rowf['id']." AND user_id = ".$user['user_id']." ORDER BY id DESC";
 654                      $resv = Database::query($sqlv, __FILE__, __LINE__);
 655                      if (Database::num_rows($resv) > 0) {
 656                          //There should be only one value for a field and a user
 657                          $rowv = Database::fetch_array($resv);
 658                          $user['extra'][$rowf['field_variable']] = $rowv['field_value'];
 659                      } else {
 660                          $user['extra'][$rowf['field_variable']] = '';
 661                      }
 662                  }
 663              }
 664              return $user;
 665          }
 666          return false;
 667      }
 668  
 669      /** Get the teacher list
 670       * @param int the course ID
 671       * @param array Content the list ID of user_id selected
 672       */
 673      //for survey
 674      // TODO: Ivan, 14-SEP-2009: It seems that this method is not used at all (it can be located in a test unit only. To be deprecated?
 675  	public static function get_teacher_list($course_id, $sel_teacher = '') {
 676          $user_course_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
 677          $user_table = Database :: get_main_table(TABLE_MAIN_USER);
 678          $course_id = Database::escape_string($course_id);
 679          $sql_query = "SELECT * FROM $user_table a, $user_course_table b where a.user_id=b.user_id AND b.status=1 AND b.course_code='$course_id'";
 680          $sql_result = Database::query($sql_query, __FILE__, __LINE__);
 681          echo "<select name=\"author\">";
 682          while ($result = Database::fetch_array($sql_result)) {
 683              if ($sel_teacher == $result['user_id']) $selected ="selected";
 684              echo "\n<option value=\"".$result['user_id']."\" $selected>".$result['firstname']."</option>";
 685          }
 686          echo "</select>";
 687      }
 688  
 689      /**
 690       * Get user picture URL or path from user ID (returns an array).
 691       * The return format is a complete path, enabling recovery of the directory
 692       * with dirname() or the file with basename(). This also works for the
 693       * functions dealing with the user's productions, as they are located in
 694       * the same directory.
 695       * @param    integer    User ID
 696       * @param    string    Type of path to return (can be 'none', 'system', 'rel', 'web')
 697       * @param    bool    Whether we want to have the directory name returned 'as if' there was a file or not (in the case we want to know which directory to create - otherwise no file means no split subdir)
 698       * @param    bool    If we want that the function returns the /main/img/unknown.jpg image set it at true
 699       * @return    array     Array of 2 elements: 'dir' and 'file' which contain the dir and file as the name implies if image does not exist it will return the unknow image if anonymous parameter is true if not it returns an empty er's
 700       */
 701  	public static function get_user_picture_path_by_id($id, $type = 'none', $preview = false, $anonymous = false) {
 702  
 703          switch ($type) {
 704              case 'system': // Base: absolute system path.
 705                  $base = api_get_path(SYS_CODE_PATH);
 706                  break;
 707              case 'rel': // Base: semi-absolute web path (no server base).
 708                  $base = api_get_path(REL_CODE_PATH);
 709                  break;
 710              case 'web': // Base: absolute web path.
 711                  $base = api_get_path(WEB_CODE_PATH);
 712                  break;
 713              case 'none':
 714              default: // Base: empty, the result path below will be relative.
 715                  $base = '';
 716          }
 717  
 718          if (empty($id) || empty($type)) {
 719              return $anonymous ? array('dir' => $base.'img/', 'file' => 'unknown.png') : array('dir' => '', 'file' => '');
 720          }
 721  
 722          $user_id = intval($id);
 723  
 724          $user_table = Database :: get_main_table(TABLE_MAIN_USER);
 725          $sql = "SELECT picture_uri FROM $user_table WHERE user_id=".$user_id;
 726          $res = Database::query($sql, __FILE__, __LINE__);
 727  
 728          if (!Database::num_rows($res)) {
 729              return $anonymous ? array('dir' => $base.'img/', 'file' => 'unknown.png') : array('dir' => '', 'file' => '');
 730          }
 731  
 732          $user = Database::fetch_array($res);
 733          $picture_filename = trim($user['picture_uri']);
 734  
 735          if (api_get_setting('split_users_upload_directory') === 'true') {
 736              if (!empty($picture_filename)) {
 737                  $dir = $base.'upload/users/'.substr($picture_filename, 0, 1).'/'.$user_id.'/';
 738              } elseif ($preview) {
 739                  $dir = $base.'upload/users/'.substr((string)$user_id, 0, 1).'/'.$user_id.'/';
 740              } else {
 741                  $dir = $base.'upload/users/'.$user_id.'/';
 742              }
 743          } else {
 744              $dir = $base.'upload/users/'.$user_id.'/';
 745          }
 746          if (empty($picture_filename) && $anonymous) {
 747              return array('dir' => $base.'img/', 'file' => 'unknown.png');
 748          }
 749          return array('dir' => $dir, 'file' => $picture_filename);
 750      }
 751  
 752  
 753      /**
 754       * Creates new user pfotos in various sizes of a user, or deletes user pfotos.
 755       * Note: This method relies on configuration setting from dokeos/main/inc/conf/profile.conf.php
 756       * @param int $user_id            The user internal identitfication number.
 757       * @param string $file            The common file name for the newly created pfotos. It will be checked and modified for compatibility with the file system.
 758       * If full name is provided, path component is ignored.
 759       * If an empty name is provided, then old user photos are deleted only, @see UserManager::delete_user_picture() as the prefered way for deletion.
 760       * @param string $source_file    The full system name of the image from which user photos will be created.
 761       * @return string/bool            Returns the resulting common file name of created images which usually should be stored in database.
 762       * When deletion is recuested returns empty string. In case of internal error or negative validation returns FALSE.
 763       */
 764  	public static function update_user_picture($user_id, $file = null, $source_file = null) {
 765  
 766          // Validation 1.
 767          if (empty($user_id)) {
 768              return false;
 769          }
 770          $delete = empty($file);
 771          if (empty($source_file)) {
 772              $source_file = $file;
 773          }
 774  
 775          // Configuration options about user photos.
 776          require_once api_get_path(CONFIGURATION_PATH).'profile.conf.php';
 777  
 778          // User-reserved directory where photos have to be placed.
 779          $path_info = self::get_user_picture_path_by_id($user_id, 'system', true);
 780          $path = $path_info['dir'];
 781          // If this directory does not exist - we create it.
 782          if (!file_exists($path)) {
 783              $perm = api_get_setting('permissions_for_new_directories');
 784              $perm = octdec(!empty($perm) ? $perm : '0770');
 785              @mkdir($path, $perm, true);
 786          }
 787  
 788          // The old photos (if any).
 789          $old_file = $path_info['file'];
 790  
 791          // Let us delete them.
 792          if (!empty($old_file)) {
 793              if (KEEP_THE_OLD_IMAGE_AFTER_CHANGE) {
 794                  $prefix = 'saved_'.date('Y_m_d_H_i_s').'_'.uniqid('').'_';
 795                  @rename($path.'small_'.$old_file, $path.$prefix.'small_'.$old_file);
 796                  @rename($path.'medium_'.$old_file, $path.$prefix.'medium_'.$old_file);
 797                  @rename($path.'big_'.$old_file, $path.$prefix.'big_'.$old_file);
 798                  @rename($path.$old_file, $path.$prefix.$old_file);
 799              } else {
 800                  @unlink($path.'small_'.$old_file);
 801                  @unlink($path.'medium_'.$old_file);
 802                  @unlink($path.'big_'.$old_file);
 803                  @unlink($path.$old_file);
 804              }
 805          }
 806  
 807          // Exit if only deletion has been requested. Return an empty picture name.
 808          if ($delete) {
 809              return '';
 810          }
 811  
 812          // Validation 2.
 813          $allowed_types = array('jpg', 'jpeg', 'png', 'gif');
 814          $file = str_replace('\\', '/', $file);
 815          $filename = (($pos = strrpos($file, '/')) !== false) ? substr($file, $pos + 1) : $file;
 816          $extension = strtolower(substr(strrchr($filename, '.'), 1));
 817          if (!in_array($extension, $allowed_types)) {
 818              return false;
 819          }
 820  
 821          // This is the common name for the new photos.
 822          if (KEEP_THE_NAME_WHEN_CHANGE_IMAGE && !empty($old_file)) {
 823              $old_extension = strtolower(substr(strrchr($old_file, '.'), 1));
 824              $filename = in_array($old_extension, $allowed_types) ? substr($old_file, 0, -strlen($old_extension)) : $old_file;
 825              $filename = (substr($filename, -1) == '.') ? $filename.$extension : $filename.'.'.$extension;
 826          } else {
 827              $filename = replace_dangerous_char($filename);
 828              if (PREFIX_IMAGE_FILENAME_WITH_UID) {
 829                  $filename = uniqid('').'_'.$filename;
 830              }
 831              // We always prefix user photos with user ids, so on setting
 832              // api_get_setting('split_users_upload_directory') === 'true'
 833              // the correspondent directories to be found successfully.
 834              $filename = $user_id.'_'.$filename;
 835          }
 836  
 837          // Storing the new photos in 4 versions with various sizes.
 838  
 839          $picture_info = @getimagesize($source_file);
 840          $type = $picture_info[2];
 841          $small = self::resize_picture($source_file, 22);
 842          $medium = self::resize_picture($source_file, 85);
 843          $normal = self::resize_picture($source_file, 200);
 844          $big = new image($source_file); // This is the original picture.
 845  
 846          $ok = false;
 847          $detected = array(1 => 'GIF', 2 => 'JPG', 3 => 'PNG');
 848          if (in_array($type, array_keys($detected))) {
 849              $ok = $small->send_image($detected[$type], $path.'small_'.$filename)
 850                  && $medium->send_image($detected[$type], $path.'medium_'.$filename)
 851                  && $normal->send_image($detected[$type], $path.$filename)
 852                  && $big->send_image($detected[$type], $path.'big_'.$filename);
 853          }
 854          return $ok ? $filename : false;
 855      }
 856  
 857      /**
 858       * Deletes user pfotos.
 859       * Note: This method relies on configuration setting from dokeos/main/inc/conf/profile.conf.php
 860       * @param int $user_id            The user internal identitfication number.
 861       * @return string/bool            Returns empty string on success, FALSE on error.
 862       */
 863  	public static function delete_user_picture($user_id) {
 864          return self::update_user_picture($user_id);
 865      }
 866  
 867  /*
 868  -----------------------------------------------------------
 869      PRODUCTIONS FUNCTIONS
 870  -----------------------------------------------------------
 871  */
 872  
 873      /**
 874       * Returns an XHTML formatted list of productions for a user, or FALSE if he
 875       * doesn't have any.
 876       *
 877       * If there has been a request to remove a production, the function will return
 878       * without building the list unless forced to do so by the optional second
 879       * parameter. This increases performance by avoiding to read through the
 880       * productions on the filesystem before the removal request has been carried
 881       * out because they'll have to be re-read afterwards anyway.
 882       *
 883       * @param    $user_id    User id
 884       * @param    $force    Optional parameter to force building after a removal request
 885       * @return    A string containing the XHTML code to dipslay the production list, or FALSE
 886       */
 887  	public static function build_production_list($user_id, $force = false, $showdelete = false) {
 888  
 889          if (!$force && !empty($_POST['remove_production'])) {
 890              return true; // postpone reading from the filesystem
 891          }
 892          $productions = self::get_user_productions($user_id);
 893  
 894          if (empty($productions)) {
 895              return false;
 896          }
 897  
 898          $production_path = self::get_user_picture_path_by_id($user_id, 'web', true);
 899          $production_dir = $production_path['dir'].$user_id.'/';
 900          $del_image = api_get_path(WEB_CODE_PATH).'img/delete.png';
 901          $del_text = get_lang('Delete');
 902          $production_list = '';
 903          if (count($productions) > 0) {
 904              $production_list = '<ul id="productions">';
 905              foreach ($productions as $file) {
 906                  $production_list .= '<li><a href="'.$production_dir.urlencode($file).'" target="_blank">'.htmlentities($file).'</a>';
 907                  if ($showdelete) {
 908                      $production_list .= '<input type="image" name="remove_production['.urlencode($file).']" src="'.$del_image.'" alt="'.$del_text.'" title="'.$del_text.' '.htmlentities($file).'" onclick="javascript: return confirmation(\''.htmlentities($file).'\');" /></li>';
 909                  }
 910              }
 911              $production_list .= '</ul>';
 912          }
 913  
 914          return $production_list;
 915      }
 916  
 917      /**
 918       * Returns an array with the user's productions.
 919       *
 920       * @param    $user_id    User id
 921       * @return    An array containing the user's productions
 922       */
 923  	public static function get_user_productions($user_id) {
 924          $production_path = self::get_user_picture_path_by_id($user_id, 'system', true);
 925          $production_repository = $production_path['dir'].$user_id.'/';
 926          $productions = array();
 927  
 928          if (is_dir($production_repository)) {
 929              $handle = opendir($production_repository);
 930  
 931              while ($file = readdir($handle)) {
 932                  if ($file == '.' || $file == '..' || $file == '.htaccess' || is_dir($production_repository.$file)) {
 933                      continue; // skip current/parent directory and .htaccess
 934                  }
 935                  if (preg_match('/('.$user_id.'|[0-9a-f]{13}|saved)_.+\.(png|jpg|jpeg|gif)$/i', $file)) {
 936                      // User's photos should not be listed as productions.
 937                      continue;
 938                  }
 939                  $productions[] = $file;
 940              }
 941          }
 942  
 943          return $productions; // can be an empty array
 944      }
 945  
 946      /**
 947       * Remove a user production.
 948       *
 949       * @param    $user_id        User id
 950       * @param    $production    The production to remove
 951       */
 952  	public static function remove_user_production($user_id, $production) {
 953          $production_path = self::get_user_picture_path_by_id($user_id, 'system', true);
 954          unlink($production_path['dir'].$user_id.'/'.$production);
 955      }
 956  
 957      /**
 958       * Update an extra field. This function is called when a user changes his/her profile
 959       * and by consequence fills or edits his/her extra fields.
 960       *
 961       * @param    integer    Field ID
 962       * @param    array    Database columns and their new value
 963       * @return    boolean    true if field updated, false otherwise
 964       */
 965  	public static function update_extra_field($fid, $columns)  {
 966          //TODO check that values added are values proposed for enumerated field types
 967          $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
 968          $fid = Database::escape_string($fid);
 969          $sqluf = "UPDATE $t_uf SET ";
 970          $known_fields = array('id', 'field_variable', 'field_type', 'field_display_text', 'field_default_value', 'field_order', 'field_visible', 'field_changeable', 'field_filter');
 971          $safecolumns = array();
 972          foreach ($columns as $index => $newval) {
 973              if (in_array($index, $known_fields)) {
 974                  $safecolumns[$index] = Database::escape_string($newval);
 975                  $sqluf .= $index." = '".$safecolumns[$index]."', ";
 976              }
 977          }
 978          $time = time();
 979          $sqluf .= " tms = FROM_UNIXTIME($time) WHERE id='$fid'";
 980          $resuf = Database::query($sqluf, __FILE__, __LINE__);
 981          return $resuf;
 982      }
 983  
 984      /**
 985       * Update an extra field value for a given user
 986       * @param    integer    User ID
 987       * @param    string    Field variable name
 988       * @param    string    Field value
 989       * @return    boolean    true if field updated, false otherwise
 990       */
 991  	public static function update_extra_field_value($user_id, $fname, $fvalue = '') {
 992          //TODO check that values added are values proposed for enumerated field types
 993          $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
 994          $t_ufo = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
 995          $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
 996          $fname = Database::escape_string($fname);
 997          if ($user_id != strval(intval($user_id))) return false;
 998          if ($user_id === false) return false;
 999          $fvalues = '';
1000          //echo '<pre>'; print_r($fvalue);
1001          if (is_array($fvalue)) {
1002              foreach($fvalue as $val) {
1003                  $fvalues .= Database::escape_string($val).';';
1004              }
1005              if (!empty($fvalues)) {
1006                  $fvalues = substr($fvalues, 0, -1);
1007              }
1008          } else {
1009              $fvalues = Database::escape_string($fvalue);
1010          }
1011          $sqluf = "SELECT * FROM $t_uf WHERE field_variable='$fname'";
1012          $resuf = Database::query($sqluf, __FILE__, __LINE__);
1013          if (Database::num_rows($resuf) == 1) {
1014              //ok, the field exists
1015              // Check if enumerated field, if the option is available
1016              $rowuf = Database::fetch_array($resuf);
1017              switch ($rowuf['field_type']) {
1018                  case 10 :
1019                      //Tags are process here
1020                      UserManager::process_tags(explode(';', $fvalues), $user_id, $rowuf['id']);
1021                      return true;
1022                  break;
1023                  case 3:
1024                  case 4:
1025                  case 5:
1026                      $sqluo = "SELECT * FROM $t_ufo WHERE field_id = ".$rowuf['id'];
1027                      $resuo = Database::query($sqluo, __FILE__, __LINE__);
1028                      $values = split(';',$fvalues);
1029                      if (Database::num_rows($resuo) > 0) {
1030                          $check = false;
1031                          while ($rowuo = Database::fetch_array($resuo)) {
1032                              if (in_array($rowuo['option_value'], $values)) {
1033                                  $check = true;
1034                                  break;
1035                              }
1036                          }
1037                          if ($check == false) {
1038                              return false; //option value not found
1039                          }
1040                      } else {
1041                          return false; //enumerated type but no option found
1042                      }
1043                      break;
1044                  case 1:
1045                  case 2:
1046                  default:
1047                      break;
1048              }
1049              $tms = time();
1050              $sqlufv = "SELECT * FROM $t_ufv WHERE user_id = $user_id AND field_id = ".$rowuf['id']." ORDER BY id";
1051              $resufv = Database::query($sqlufv, __FILE__, __LINE__);
1052              $n = Database::num_rows($resufv);
1053              if ($n > 1) {
1054                  //problem, we already have to values for this field and user combination - keep last one
1055                  while ($rowufv = Database::fetch_array($resufv)) {
1056                      if ($n > 1) {
1057                          $sqld = "DELETE FROM $t_ufv WHERE id = ".$rowufv['id'];
1058                          $resd = Database::query($sqld, __FILE__, __LINE__);
1059                          $n--;
1060                      }
1061                      $rowufv = Database::fetch_array($resufv);
1062                      if ($rowufv['field_value'] != $fvalues) {
1063                          $sqlu = "UPDATE $t_ufv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowufv['id'];
1064                          $resu = Database::query($sqlu, __FILE__, __LINE__);
1065                          return($resu ? true : false);
1066                      }
1067                      return true;
1068                  }
1069              }
1070              elseif ($n == 1) {
1071                  //we need to update the current record
1072                  $rowufv = Database::fetch_array($resufv);
1073                  if ($rowufv['field_value'] != $fvalues) {
1074                      $sqlu = "UPDATE $t_ufv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowufv['id'];
1075                      //error_log('UM::update_extra_field_value: '.$sqlu);
1076                      $resu = Database::query($sqlu, __FILE__, __LINE__);
1077                      return($resu ? true : false);
1078                  }
1079                  return true;
1080              } else {
1081                  $sqli = "INSERT INTO $t_ufv (user_id,field_id,field_value,tms) " .
1082                      "VALUES ($user_id,".$rowuf['id'].",'$fvalues',FROM_UNIXTIME($tms))";
1083                  //error_log('UM::update_extra_field_value: '.$sqli);
1084                  $resi = Database::query($sqli, __FILE__, __LINE__);
1085                  return($resi ? true : false);
1086              }
1087          } else {
1088              return false; //field not found
1089          }
1090      }
1091  
1092      /**
1093       * Get an array of extra fieds with field details (type, default value and options)
1094       * @param    integer    Offset (from which row)
1095       * @param    integer    Number of items
1096       * @param    integer    Column on which sorting is made
1097       * @param    string    Sorting direction
1098       * @param    boolean    Optional. Whether we get all the fields or just the visible ones
1099       * @return    array    Extra fields details (e.g. $list[2]['type'], $list[4]['options'][2]['title']
1100       */
1101  	public static function get_extra_fields($from = 0, $number_of_items = 0, $column = 5, $direction = 'ASC', $all_visibility = true) {
1102          $fields = array();
1103          $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
1104          $t_ufo = Database :: get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
1105          $columns = array('id', 'field_variable', 'field_type', 'field_display_text', 'field_default_value', 'field_order', 'field_filter', 'tms');
1106          $column = intval($column);
1107          $sort_direction = '';
1108          if (in_array(strtoupper($direction), array('ASC', 'DESC'))) {
1109              $sort_direction = strtoupper($direction);
1110          }
1111          $sqlf = "SELECT * FROM $t_uf ";
1112          if ($all_visibility == false) {
1113              $sqlf .= " WHERE field_visible = 1 ";
1114          }
1115          $sqlf .= " ORDER BY ".$columns[$column]." $sort_direction " ;
1116          if ($number_of_items != 0) {
1117              $sqlf .= " LIMIT ".Database::escape_string($from).','.Database::escape_string($number_of_items);
1118          }
1119  
1120          $resf = Database::query($sqlf, __FILE__, __LINE__);
1121          if (Database::num_rows($resf) > 0) {
1122              while($rowf = Database::fetch_array($resf)) {
1123                  $fields[$rowf['id']] = array(
1124                      0 => $rowf['id'],
1125                      1 => $rowf['field_variable'],
1126                      2 => $rowf['field_type'],
1127                      //3 => (empty($rowf['field_display_text']) ? '' : get_lang($rowf['field_display_text'], '')),
1128                      // Temporarily removed auto-translation. Need update to get_lang() to know if translation exists (todo)
1129                      // Ivan, 15-SEP-2009: get_lang() has been modified accordingly in order this issue to be solved.
1130                      3 => (empty($rowf['field_display_text']) ? '' : $rowf['field_display_text']),
1131                      4 => $rowf['field_default_value'],
1132                      5 => $rowf['field_order'],
1133                      6 => $rowf['field_visible'],
1134                      7 => $rowf['field_changeable'],
1135                      8 => $rowf['field_filter'],
1136                      9 => array()
1137                  );
1138  
1139                  $sqlo = "SELECT * FROM $t_ufo WHERE field_id = ".$rowf['id']." ORDER BY option_order ASC";
1140                  $reso = Database::query($sqlo, __FILE__, __LINE__);
1141                  if (Database::num_rows($reso) > 0) {
1142                      while ($rowo = Database::fetch_array($reso)) {
1143                          $fields[$rowf['id']][9][$rowo['id']] = array(
1144                              0 => $rowo['id'],
1145                              1 => $rowo['option_value'],
1146                              //2 => (empty($rowo['option_display_text']) ? '' : get_lang($rowo['option_display_text'], '')),
1147                              2 => (empty($rowo['option_display_text']) ? '' : $rowo['option_display_text']),
1148                              3 => $rowo['option_order']
1149                          );
1150                      }
1151                  }
1152              }
1153          }
1154  
1155          return $fields;
1156      }
1157  
1158          /**
1159       * Get an array of active fields             
1160       * @return    array    active fields details (e.g. $list[2])
1161       */
1162  	public static function get_active_extra_fields($selected_fields = null) {
1163          
1164          $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
1165          $c_act = 0;
1166                  
1167                  $filter_fields = '';
1168                  if (is_array($selected_fields) && count($selected_fields) > 0) {
1169                      $filter_fields .= ' AND u_f.id IN('.implode(',', $selected_fields).')';
1170                  }
1171                  
1172                  $array_field_activate = array();
1173                  $sql_field_activate = "SELECT u_f.id as id
1174                  FROM $t_uf u_f 
1175                  where u_f.field_visible = 1 $filter_fields
1176                  group by 1
1177                  order by u_f.field_order";
1178                  $query_field_activate = Database::query($sql_field_activate, __FILE__, __LINE__);
1179                  while ($row = Database::fetch_array($query_field_activate)){
1180                      $array_field_activate[$c_act] = $row['id'];
1181                      $c_act++;
1182                  }
1183  
1184          return $array_field_activate;
1185      }
1186          
1187          /**
1188       * Get an array of user active fields
1189           * @param    integer    Id of user
1190       * @return    array    array of user active fields (e.g. $list[2])
1191       */
1192  	public static function get_active_sorted_extra_fields($field_sort, $direction,$from,$number_of_items,$keyword,$keyword_firstname,$keyword_lastname,$keyword_username,$keyword_email,$keyword_officialcode, $keyword_status,$keyword_admin, $keyword_active,$keyword_inactive,$sql_add,$from,$number_of_items) {
1193                  
1194                  $user_table = Database :: get_main_table(TABLE_MAIN_USER);
1195                  $t_u_f_values = Database :: get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
1196                  $tbl_user_field = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
1197                  $admin_table = Database :: get_main_table(TABLE_MAIN_ADMIN);            
1198                  
1199                  $sql_sort = "select u.user_id, 
1200                  u.user_id,
1201                  u.official_code,
1202                  u.firstname ,
1203                  u.lastname,  
1204                  u.username, 
1205                   u.email,
1206                  u.status,
1207                  u.active,
1208                  u.user_id,
1209                  u.expiration_date      AS exp ,
1210                  u_f_v.field_value,
1211                   u_f_v.field_id,
1212                  u_f.id
1213                  from $user_table u
1214                  ";
1215                  
1216                  // adding the filter to see the user's only of the current access_url    
1217                  if ((api_is_platform_admin() || api_is_session_admin()) && $_configuration['multiple_access_urls']==true && api_get_current_access_url_id()!=-1) {
1218                      $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
1219                      $sql_sort.= " INNER JOIN $access_url_rel_user_table url_rel_user ON (u.user_id=url_rel_user.user_id)";
1220                  }
1221                  
1222                  
1223                  
1224                  $sql_sort .= " left join $t_u_f_values u_f_v on u.user_id = u_f_v.user_id";
1225                  if (!empty($field_sort)) {
1226                      $sql_sort .= " and u_f_v.field_id = $field_sort";
1227                  }
1228                  $sql_sort .= " left  join $tbl_user_field u_f on u_f.id = u_f_v.field_id";
1229                  if (!empty($field_sort)) {
1230                      $sql_sort .= " AND     u_f.id = $field_sort ";
1231                  }
1232                  
1233                  /*
1234                   * Start Recicled
1235                   */                
1236                  $sql_sort .= $sql_add;
1237                  /*
1238                   * End Recicled
1239                   */                               
1240                  $sql_sort .= " group by u.user_id ";                
1241                  $sql_sort .= " order by u_f_v.field_value $direction ";
1242                  $sql_sort .= " LIMIT $from,$number_of_items"; 
1243                  $res = Database::query($sql_sort, __FILE__, __LINE__);
1244                  return $res ;
1245                  
1246      }
1247          
1248          
1249          /**
1250       * Get an array of user active fields
1251           * @param    integer    Id of user
1252       * @return    array    array of user active fields (e.g. $list[2])
1253       */
1254  	public static function get_active_user_extra_fields($user) {
1255          
1256                  $user_table = Database :: get_main_table(TABLE_MAIN_USER);    
1257                  $t_u_f_values = Database :: get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
1258                  $tbl_user_field = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
1259          
1260          $sql_extra_field_user =  "SELECT u_f.id as id,u_f.field_display_text as field_display_text,
1261                  u_f.field_order, u_f_v.field_value  as field_value
1262                  FROM $tbl_user_field u_f 
1263                  inner join $t_u_f_values u_f_v on u_f_v.field_id = u_f.id
1264                  inner join $user_table u on u.user_id = u_f_v.user_id
1265                  where u.user_id =$user and u_f.field_visible = 1 group by u_f.id order by u_f.field_order";
1266              
1267                  $sql_rows = Database::query($sql_extra_field_user, __FILE__, __LINE__);
1268                  $num_rows = Database::num_rows($sql_rows);
1269             
1270                  $cont_user_active = 0;
1271                  $array_user_active = array(); 
1272            
1273                  if($num_rows !=0 ) {
1274                      while($rows = Database::fetch_array($sql_rows)) {                                     
1275                          $array_user_active[$cont_user_active] = $rows['id'];                  
1276                          $cont_user_active++;
1277                      }
1278                  }
1279              
1280                  return $array_user_active;
1281      }
1282          /**
1283       * Get an user fields
1284           * @param    integer    Id of user
1285           * @param    integer    Id of Field
1286       * @return    String    of user active fields (e.g. $list[2])
1287       */
1288          public static function get_user_name_field($user ,$field_id){
1289              
1290              $user_table = Database :: get_main_table(TABLE_MAIN_USER);    
1291              $t_u_f_values = Database :: get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
1292              $tbl_user_field = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
1293                  
1294              $sql_field = "SELECT u_f.id as id,u_f.field_display_text as field_display_text,
1295              u_f.field_order, u_f_v.field_value  as field_value
1296              FROM $tbl_user_field u_f 
1297              inner join $t_u_f_values u_f_v on u_f_v.field_id = u_f.id
1298              inner join $user_table u on u.user_id = u_f_v.user_id
1299              where u.user_id = $user and u_f.field_visible = 1 
1300              and u_f_v.field_id = $field_id
1301              group by 1
1302              order by u_f.field_order";
1303  
1304              $quey_field = Database::query($sql_field, __FILE__, __LINE__);
1305              $rows_field = Database::fetch_array($quey_field);
1306  
1307              return $rows_field['field_value'];
1308                      
1309          }
1310  
1311           /**
1312       * Get the list of options attached to an extra field
1313       * @param string $fieldname the name of the field
1314       * @return array the list of options
1315       */
1316  	public static function get_extra_field_options($field_name) {
1317          $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
1318          $t_ufo = Database :: get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
1319  
1320          $sql = 'SELECT options.*
1321                  FROM '.$t_ufo.' options
1322                      INNER JOIN '.$t_uf.' fields
1323                          ON fields.id = options.field_id
1324                              AND fields.field_variable="'.Database::escape_string($field_name).'"';
1325          $rs = Database::query($sql, __FILE__, __LINE__);
1326          return Database::store_result($rs);
1327      }
1328  
1329      /**
1330       * Get the number of extra fields currently recorded
1331       * @param    boolean    Optional switch. true (default) returns all fields, false returns only visible fields
1332       * @return    integer    Number of fields
1333       */
1334  	public static function get_number_of_extra_fields($all_visibility = true) {
1335          $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
1336          $sqlf = "SELECT * FROM $t_uf ";
1337          if ($all_visibility == false) {
1338              $sqlf .= " WHERE field_visible = 1 ";
1339          }
1340          $sqlf .= " ORDER BY field_order";
1341          $resf = Database::query($sqlf, __FILE__, __LINE__);
1342          return Database::num_rows($resf);
1343      }
1344  
1345      /**
1346        * Creates a new extra field
1347        * @param    string    Field's internal variable name
1348        * @param    int        Field's type
1349        * @param    string    Field's language var name
1350        * @param    string    Field's default value
1351        * @param    string    Optional comma-separated list of options to provide for select and radio
1352        * @return int     new user id - if the new user creation succeeds, false otherwise
1353        */
1354  	public static function create_extra_field($fieldvarname, $fieldtype, $fieldtitle, $fielddefault, $fieldoptions = '') {
1355          // database table definition
1356          $table_field         = Database::get_main_table(TABLE_MAIN_USER_FIELD);
1357          $table_field_options= Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
1358  
1359          // First check wether the login already exists
1360          if (self::is_extra_field_available($fieldvarname)) {
1361              return api_set_failure('login-pass already taken');
1362          }
1363          $sql = "SELECT MAX(field_order) FROM $table_field";
1364          $res = Database::query($sql, __FILE__, __LINE__);
1365          $order = 0;
1366          if (Database::num_rows($res) > 0) {
1367              $row = Database::fetch_array($res);
1368              $order = $row[0]+1;
1369          }
1370          $time = time();
1371          $sql = "INSERT INTO $table_field
1372                  SET field_type = '".Database::escape_string($fieldtype)."',
1373                  field_variable = '".Database::escape_string($fieldvarname)."',
1374                  field_display_text = '".Database::escape_string($fieldtitle)."',
1375                  field_default_value = '".Database::escape_string($fielddefault)."',
1376                  field_order = '$order',
1377                  tms = FROM_UNIXTIME($time)";
1378          $result = Database::query($sql);
1379          if ($result) {
1380              //echo "id returned";
1381              $return = Database::insert_id();
1382          } else {
1383              //echo "false - failed" ;
1384              return false;
1385          }
1386  
1387          if (!empty($fieldoptions) && in_array($fieldtype, array(USER_FIELD_TYPE_RADIO, USER_FIELD_TYPE_SELECT, USER_FIELD_TYPE_SELECT_MULTIPLE, USER_FIELD_TYPE_DOUBLE_SELECT))) {
1388              if ($fieldtype == USER_FIELD_TYPE_DOUBLE_SELECT) {
1389                  $twolist = explode('|', $fieldoptions);
1390                  $counter = 0;
1391                  foreach ($twolist as $individual_list) {
1392                      $splitted_individual_list = split(';', $individual_list);
1393                      foreach    ($splitted_individual_list as $individual_list_option) {
1394                          //echo 'counter:'.$counter;
1395                          if ($counter == 0) {
1396                              $list[] = $individual_list_option;
1397                          } else {
1398                              $list[] = str_repeat('*', $counter).$individual_list_option;
1399                          }
1400                      }
1401                      $counter++;
1402                  }
1403              } else {
1404                  $list = split(';', $fieldoptions);
1405              }
1406              foreach ($list as $option) {
1407                  $option = Database::escape_string($option);
1408                  $sql = "SELECT * FROM $table_field_options WHERE field_id = $return AND option_value = '".$option."'";
1409                  $res = Database::query($sql, __FILE__, __LINE__);
1410                  if (Database::num_rows($res) > 0) {
1411                      //the option already exists, do nothing
1412                  } else {
1413                      $sql = "SELECT MAX(option_order) FROM $table_field_options WHERE field_id = $return";
1414                      $res = Database::query($sql, __FILE__, __LINE__);
1415                      $max = 1;
1416                      if (Database::num_rows($res) > 0) {
1417                          $row = Database::fetch_array($res);
1418                          $max = $row[0] + 1;
1419                      }
1420                      $time = time();
1421                      $sql = "INSERT INTO $table_field_options (field_id,option_value,option_display_text,option_order,tms) VALUES ($return,'$option','$option',$max,FROM_UNIXTIME($time))";
1422                      $res = Database::query($sql, __FILE__, __LINE__);
1423                      if ($res === false) {
1424                          $return = false;
1425                      }
1426                  }
1427              }
1428          }
1429          return $return;
1430      }
1431  
1432      /**
1433        * Save the changes in the definition of the extra user profile field
1434        * The function is called after you (as admin) decide to store the changes you have made to one of the fields you defined
1435        *
1436        * There is quite some logic in this field
1437        * 1.  store the changes to the field (tupe, name, label, default text)
1438        * 2.  remove the options and the choices of the users from the database that no longer occur in the form field 'possible values'. We should only remove
1439        *     the options (and choices) that do no longer have to appear. We cannot remove all options and choices because if you remove them all
1440        *     and simply re-add them all then all the user who have already filled this form will loose their selected value.
1441        * 3.    we add the options that are newly added
1442        *
1443        * @example     current options are a;b;c and the user changes this to a;b;x (removing c and adding x)
1444        *             we first remove c (and also the entry in the option_value table for the users who have chosen this)
1445        *             we then add x
1446        *             a and b are neither removed nor added
1447        *
1448        * @param     integer $fieldid        the id of the field we are editing
1449        * @param    string    $fieldvarname    the internal variable name of the field
1450        * @param    int        $fieldtype        the type of the field
1451        * @param    string    $fieldtitle        the title of the field
1452        * @param    string    $fielddefault    the default value of the field
1453        * @param    string    $fieldoptions    Optional comma-separated list of options to provide for select and radio
1454        * @return boolean true
1455        *
1456        *
1457        * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
1458        * @version July 2008
1459        * @since Dokeos 1.8.6
1460        */
1461  	public static function save_extra_field_changes($fieldid, $fieldvarname, $fieldtype, $fieldtitle, $fielddefault, $fieldoptions = '') {
1462          // database table definition
1463          $table_field                 = Database::get_main_table(TABLE_MAIN_USER_FIELD);
1464          $table_field_options        = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
1465          $table_field_options_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
1466  
1467          // we first update the field definition with the new values
1468          $time = time();
1469          $sql = "UPDATE $table_field
1470                  SET field_type = '".Database::escape_string($fieldtype)."',
1471                  field_variable = '".Database::escape_string($fieldvarname)."',
1472                  field_display_text = '".Database::escape_string($fieldtitle)."',
1473                  field_default_value = '".Database::escape_string($fielddefault)."',
1474                  tms = FROM_UNIXTIME($time)
1475              WHERE id = '".Database::escape_string($fieldid)."'";
1476          $result = Database::query($sql, __FILE__, __LINE__);
1477  
1478          // we create an array with all the options (will be used later in the script)
1479          if ($fieldtype == USER_FIELD_TYPE_DOUBLE_SELECT) {
1480              $twolist = explode('|', $fieldoptions);
1481              $counter = 0;
1482              foreach ($twolist as $individual_list) {
1483                  $splitted_individual_list = split(';', $individual_list);
1484                  foreach    ($splitted_individual_list as $individual_list_option) {
1485                      //echo 'counter:'.$counter;
1486                      if ($counter == 0) {
1487                          $list[] = trim($individual_list_option);
1488                      } else {
1489                          $list[] = str_repeat('*', $counter).trim($individual_list_option);
1490                      }
1491                  }
1492                  $counter++;
1493              }
1494          } else {
1495              $templist = split(';', $fieldoptions);
1496              $list = array_map('trim', $templist);
1497          }
1498  
1499          // Remove all the field options (and also the choices of the user) that are NOT in the new list of options
1500          $sql = "SELECT * FROM $table_field_options WHERE option_value NOT IN ('".implode("','", $list)."') AND field_id = '".Database::escape_string($fieldid)."'";
1501          $result = Database::query($sql, __FILE__, __LINE__);
1502          $return['deleted_options'] = 0;
1503          while ($row = Database::fetch_array($result)) {
1504              // deleting the option
1505              $sql_delete_option = "DELETE FROM $table_field_options WHERE id='".Database::escape_string($row['id'])."'";
1506              $result_delete_option = Database::query($sql_delete_option, __FILE__, __LINE__);
1507              $return['deleted_options']++;
1508  
1509              // deleting the answer of the user who has chosen this option
1510              $sql_delete_option_value = "DELETE FROM $table_field_options_values WHERE field_id = '".Database::escape_string($fieldid)."' AND field_value = '".Database::escape_string($row['option_value'])."'";
1511              $result_delete_option_value = Database::query($sql_delete_option_value, __FILE__, __LINE__);
1512              $return['deleted_option_values'] = $return['deleted_option_values'] + Database::affected_rows();
1513          }
1514  
1515          // we now try to find the field options that are newly added
1516          $sql = "SELECT * FROM $table_field_options WHERE field_id = '".Database::escape_string($fieldid)."'";
1517          $result = Database::query($sql, __FILE__, __LINE__);
1518          while ($row = Database::fetch_array($result)) {
1519              // we remove every option that is already in the database from the $list
1520              if (in_array(trim($row['option_display_text']), $list)) {
1521                  $key = array_search(trim($row['option_display_text']), $list);
1522                  unset($list[$key]);
1523              }
1524          }
1525  
1526          // we store the new field options in the database
1527          foreach ($list as $key => $option) {
1528              $sql = "SELECT MAX(option_order) FROM $table_field_options WHERE field_id = '".Database::escape_string($fieldid)."'";
1529              $res = Database::query($sql, __FILE__, __LINE__);
1530              $max = 1;
1531              if (Database::num_rows($res) > 0) {
1532                  $row = Database::fetch_array($res);
1533                  $max = $row[0] + 1;
1534              }
1535              $time = time();
1536              $sql = "INSERT INTO $table_field_options (field_id,option_value,option_display_text,option_order,tms) VALUES ('".Database::escape_string($fieldid)."','".Database::escape_string($option)."','".Database::escape_string($option)."',$max,FROM_UNIXTIME($time))";
1537              $result = Database::query($sql, __FILE__, __LINE__);
1538          }
1539          return true;
1540      }
1541  
1542      /**
1543       * Check if a field is available
1544       * @param    string    the wanted fieldname
1545       * @return    boolean    true if the wanted username is available
1546       */
1547  	public static function is_extra_field_available($fieldname) {
1548          $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
1549          $sql = "SELECT * FROM $t_uf WHERE field_variable = '".Database::escape_string($fieldname)."'";
1550          $res = Database::query($sql, __FILE__, __LINE__);
1551          return Database::num_rows($res) > 0;
1552      }
1553     /**
1554     * Gets the info about a gradebook certificate for a user by course
1555     * @param string The course code
1556     * @param int The user id
1557     * @return array  if there is not information return false
1558     */
1559  	public function get_info_gradebook_certificate($course_code,$user_id) {
1560            $tbl_grade_certificate     = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CERTIFICATE);
1561            $tbl_grade_category     = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY);
1562            $sql='SELECT * FROM '.$tbl_grade_certificate.' WHERE cat_id= (SELECT id FROM '.$tbl_grade_category.' WHERE course_code = "'.Database::escape_string($course_code).'" ) AND user_id="'.Database::escape_string($user_id).'" ';
1563            $rs = Database::query($sql,__FILE__,__LINE__);
1564            $row= Database::fetch_array($rs);
1565            if (Database::num_rows($rs) > 0)
1566                return $row;
1567            else
1568                return false;
1569      }
1570      /**
1571       * get user id of teacher or session administrator
1572       * @param string The course id
1573       * @return int The user id
1574       */
1575  	 public function get_user_id_of_course_admin_or_session_admin ($course_id) {
1576           $session=api_get_session_id();
1577          $table_user = Database::get_main_table(TABLE_MAIN_USER);
1578          $table_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
1579          $table_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
1580           if ($session==0 || is_null($session)) {
1581               $sql='SELECT u.user_id FROM '.$table_user.' u
1582                      INNER JOIN '.$table_course_user.' ru ON ru.user_id=u.user_id
1583                      WHERE ru.status=1 AND ru.course_code="'.Database::escape_string($course_id).'" ';
1584              $rs=Database::query($sql,__FILE__,__LINE__);
1585              $num_rows=Database::num_rows($rs);
1586              if ($num_rows==1) {
1587                  $row=Database::fetch_array($rs);
1588                  return $row['user_id'];
1589              } else {
1590                  $my_num_rows=$num_rows;
1591                  $my_user_id=Database::result($rs,$my_num_rows-1,'user_id');
1592                  return $my_user_id;
1593              }
1594          } elseif ($session>0) {
1595              $sql='SELECT u.user_id FROM '.$table_user.' u
1596                  INNER JOIN '.$table_session_course_user.' sru
1597                  ON sru.id_user=u.user_id WHERE sru.course_code="'.Database::escape_string($course_id).'" ';
1598              $rs=Database::query($sql,__FILE__,__LINE__);
1599              $row=Database::fetch_array($rs);
1600  
1601              return $row['user_id'];
1602               }
1603           }
1604      /**
1605       * Gets user extra fields data
1606       * @param    integer    User ID
1607       * @param    boolean    Whether to prefix the fields indexes with "extra_" (might be used by formvalidator)
1608       * @param    boolean    Whether to return invisible fields as well
1609       * @param    boolean    Whether to split multiple-selection fields or not
1610       * @return    array    Array of fields => value for the given user
1611       */
1612  	public static function get_extra_user_data($user_id, $prefix = false, $all_visibility = true, $splitmultiple = false) {
1613          // A sanity check.
1614          if (empty($user_id)) {
1615              $user_id = 0;
1616          } else {
1617              if ($user_id != strval(intval($user_id))) return array();
1618          }
1619          $extra_data = array();
1620          $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
1621          $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
1622          $user_id = Database::escape_string($user_id);
1623          $sql = "SELECT f.id as id, f.field_variable as fvar, f.field_type as type FROM $t_uf f ";
1624          if ($all_visibility == false) {
1625              $sql .= " WHERE f.field_visible = 1 ";
1626          }
1627          $sql .= " ORDER BY f.field_order";
1628          $res = Database::query($sql, __FILE__, __LINE__);
1629          if (Database::num_rows($res) > 0) {
1630              while ($row = Database::fetch_array($res)) {
1631                  $sqlu = "SELECT field_value as fval " .
1632                          " FROM $t_ufv " .
1633                          " WHERE field_id=".$row['id']."" .
1634                          " AND user_id=".$user_id;
1635                  $resu = Database::query($sqlu, __FILE__, __LINE__);
1636                  $fval = '';
1637                  // get default value
1638                  $sql_df = "SELECT field_default_value as fval_df " .
1639                          " FROM $t_uf " .
1640                          " WHERE id=".$row['id'];
1641                  $res_df = Database::query($sql_df, __FILE__, __LINE__);
1642                  if (Database::num_rows($resu) > 0) {
1643                      $rowu = Database::fetch_array($resu);
1644                      $fval = $rowu['fval'];
1645                      if ($row['type'] ==  USER_FIELD_TYPE_SELECT_MULTIPLE) {
1646                          $fval = split(';',$rowu['fval']);
1647                      }
1648                  } else {
1649                      $row_df = Database::fetch_array($res_df);
1650                      $fval = $row_df['fval_df'];
1651                  }
1652                  if ($prefix) {
1653                      if ($row['type'] ==  USER_FIELD_TYPE_RADIO) {
1654                          $extra_data['extra_'.$row['fvar']]['extra_'.$row['fvar']] = $fval;
1655                      } else {
1656                          $extra_data['extra_'.$row['fvar']] = $fval;
1657                      }
1658                  } else {
1659                      if ($row['type'] ==  USER_FIELD_TYPE_RADIO) {
1660                          $extra_data['extra_'.$row['fvar']]['extra_'.$row['fvar']] = $fval;
1661                      } else {
1662                          $extra_data[$row['fvar']] = $fval;
1663                      }
1664                  }
1665              }
1666          }
1667  
1668          return $extra_data;
1669      }
1670  
1671      /** Get extra user data by field
1672       * @param int    user ID
1673       * @param string the internal variable name of the field
1674       * @return array with extra data info of a user i.e array('field_variable'=>'value');
1675       */
1676  	public static function get_extra_user_data_by_field($user_id, $field_variable, $prefix = false, $all_visibility = true, $splitmultiple = false) {
1677          // A sanity check.
1678          if (empty($user_id)) {
1679              $user_id = 0;
1680          } else {
1681              if ($user_id != strval(intval($user_id))) return array();
1682          }
1683          $extra_data = array();
1684          $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
1685          $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
1686          $user_id = Database::escape_string($user_id);
1687          $sql = "SELECT f.id as id, f.field_variable as fvar, f.field_type as type FROM $t_uf f ";
1688  
1689          $sql .= " WHERE f.field_variable = '$field_variable' ";
1690  
1691          if ($all_visibility == false) {
1692              $sql .= " AND f.field_visible = 1 ";
1693          }
1694  
1695          $sql .= " ORDER BY f.field_order";
1696  
1697          $res = Database::query($sql, __FILE__, __LINE__);
1698          if (Database::num_rows($res) > 0) {
1699              while ($row = Database::fetch_array($res)) {
1700                  $sqlu = "SELECT field_value as fval " .
1701                          " FROM $t_ufv " .
1702                          " WHERE field_id=".$row['id']."" .
1703                          " AND user_id=".$user_id;
1704                  $resu = Database::query($sqlu, __FILE__, __LINE__);
1705                  $fval = '';
1706                  if (Database::num_rows($resu) > 0) {
1707                      $rowu = Database::fetch_array($resu);
1708                      $fval = $rowu['fval'];
1709                      if ($row['type'] ==  USER_FIELD_TYPE_SELECT_MULTIPLE) {
1710                          $fval = split(';',$rowu['fval']);
1711                      }
1712                  }
1713                  if ($prefix) {
1714                      $extra_data['extra_'.$row['fvar']] = $fval;
1715                  } else {
1716                      $extra_data[$row['fvar']] = $fval;
1717                  }
1718              }
1719          }
1720  
1721          return $extra_data;
1722      }
1723  
1724      /**
1725       * Get the extra field information for a certain field (the options as well)
1726       * @param  int     The name of the field we want to know everything about
1727       * @return array   Array containing all the information about the extra profile field (first level of array contains field details, then 'options' sub-array contains options details, as returned by the database)
1728       * @author Julio Montoya
1729       * @since Dokeos 1.8.6
1730       */
1731  	public static function get_extra_field_information_by_name($field_variable, $fuzzy = false) {
1732          // database table definition
1733          $table_field             = Database::get_main_table(TABLE_MAIN_USER_FIELD);
1734          $table_field_options    = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
1735  
1736          // all the information of the field
1737          $sql = "SELECT * FROM $table_field WHERE field_variable='".Database::escape_string($field_variable)."'";
1738          $result = Database::query($sql, __FILE__, __LINE__);
1739          $return = Database::fetch_array($result);
1740  
1741          // all the options of the field
1742          $sql = "SELECT * FROM $table_field_options WHERE field_id='".Database::escape_string($return['id'])."' ORDER BY option_order ASC";
1743          $result = Database::query($sql, __FILE__, __LINE__);
1744          while ($row = Database::fetch_array($result)) {
1745              $return['options'][$row['id']] = $row;
1746          }
1747          return $return;
1748      }
1749  
1750       /**
1751       * Get the name of an extra field  for a certain field ID
1752       * @param  int     The extra field ID
1753       * @return string  The name of the extra field
1754       * @author Isaac flores
1755       * @since Dokeos 2.0
1756       */
1757  	public static function get_extra_field_name_by_field_id($field_id) {
1758          // database table definition
1759          $table_field             = Database::get_main_table(TABLE_MAIN_USER_FIELD);
1760  
1761          // The name of the field
1762          $sql = "SELECT field_display_text FROM $table_field WHERE id='".Database::escape_string($field_id)."'";
1763          $result = Database::query($sql, __FILE__, __LINE__);
1764          $row = Database::fetch_array($result);
1765  
1766          return $row['field_display_text'];
1767      }
1768  
1769  	public static function get_all_extra_field_by_type($field_type) {
1770          // database table definition
1771          $table_field             = Database::get_main_table(TABLE_MAIN_USER_FIELD);
1772  
1773          // all the information of the field
1774          $sql = "SELECT * FROM $table_field WHERE field_type='".Database::escape_string($field_type)."'";
1775          $result = Database::query($sql, __FILE__, __LINE__);
1776          while ($row = Database::fetch_array($result)) {
1777              $return[] = $row['id'];
1778          }
1779          return $return;
1780      }
1781  
1782      /**
1783       * Get all the extra field information of a certain field (also the options)
1784       *
1785       * @param int $field_name the name of the field we want to know everything of
1786       * @return array $return containing all th information about the extra profile field
1787       * @author Julio Montoya
1788       * @since Dokeos 1.8.6
1789       */
1790  	public static function get_extra_field_information($field_id) {
1791          // database table definition
1792          $table_field             = Database::get_main_table(TABLE_MAIN_USER_FIELD);
1793          $table_field_options    = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
1794  
1795          // all the information of the field
1796          $sql = "SELECT * FROM $table_field WHERE id='".Database::escape_string($field_id)."'";
1797          $result = Database::query($sql, __FILE__, __LINE__);
1798          $return = Database::fetch_array($result);
1799  
1800          // all the options of the field
1801          $sql = "SELECT * FROM $table_field_options WHERE field_id='".Database::escape_string($field_id)."' ORDER BY option_order ASC";
1802          $result = Database::query($sql, __FILE__, __LINE__);
1803          while ($row = Database::fetch_array($result)) {
1804              $return['options'][$row['id']] = $row;
1805          }
1806          return $return;
1807      }
1808  
1809      /** Get extra user data by value
1810       * @param string the internal variable name of the field
1811       * @param string the internal value of the field
1812       * @return array with extra data info of a user i.e array('field_variable'=>'value');
1813       */
1814  
1815  	public static function get_extra_user_data_by_value($field_variable, $field_value, $all_visibility = true) {
1816          $extra_data = array();
1817          $table_user_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
1818          $table_user_field_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
1819          $table_user_field_options = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
1820          $where = '';
1821          /*
1822          if (is_array($field_variable_array) && is_array($field_value_array)) {
1823              if (count($field_variable_array) == count($field_value_array)) {
1824                  $field_var_count = count($field_variable_array);
1825                  for ($i = 0; $i < $field_var_count; $i++) {
1826                      if ($i != 0 && $i != $field_var_count) {
1827                          $where.= ' AND ';
1828                      }
1829                      $where.= "field_variable='".Database::escape_string($field_variable_array[$i])."' AND user_field_options.id='".Database::escape_string($field_value_array[$i])."'";
1830                  }
1831              }
1832  
1833          }*/
1834          $where = "field_variable='".Database::escape_string($field_variable)."' AND field_value='".Database::escape_string($field_value)."'";
1835  
1836          $sql = "SELECT user_id FROM $table_user_field user_field INNER JOIN $table_user_field_values user_field_values
1837                      ON (user_field.id = user_field_values.field_id)
1838                  WHERE $where";
1839  
1840          if ($all_visibility == true) {
1841              $sql .= " AND user_field.field_visible = 1 ";
1842          } else {
1843              $sql .= " AND user_field.field_visible = 0 ";
1844          }
1845          $res = Database::query($sql, __FILE__, __LINE__);
1846          $result_data = array();
1847          if (Database::num_rows($res) > 0) {
1848              while ($row = Database::fetch_array($res)) {
1849                  $result_data[] = $row['user_id'];
1850              }
1851          }
1852          return $result_data;
1853      }
1854  
1855  
1856      /**
1857       * Gives a list of [session_category][session_id] for the current user.
1858       * @param integer $user_id
1859       * @param boolean whether to fill the first element or not (to give space for courses out of categories)
1860       * @param boolean  optional true if limit time from session is over, false otherwise
1861       * @return array  list of statuses [session_category][session_id]
1862       * @todo ensure multiple access urls are managed correctly
1863       */
1864  	public static function get_sessions_by_category ($user_id, $fill_first = false, $is_time_over = false) {
1865          // Database Table Definitions
1866          $tbl_session_user            = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
1867          $tbl_session                = Database :: get_main_table(TABLE_MAIN_SESSION);
1868          $tbl_session_course            = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
1869          $tbl_session_course_user    = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
1870          if ($user_id != strval(intval($user_id))) return array();
1871  
1872          $categories = array();
1873          if ($fill_first) {
1874              $categories[0] = array();
1875          }
1876          /*
1877          //we filter the courses from the URL
1878          $join_access_url=$where_access_url='';
1879          global $_configuration;
1880          if ($_configuration['multiple_access_urls']==true) {
1881              $access_url_id = api_get_current_access_url_id();
1882              if($access_url_id!=-1) {
1883                  $tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
1884                  $join_access_url= "LEFT JOIN $tbl_url_course url_rel_course ON url_rel_course.course_code= course.code";
1885                  $where_access_url=" AND access_url_id = $access_url_id ";
1886              }
1887          }
1888          */
1889          // get the list of sessions where the user is subscribed as student
1890  
1891          $condition_date_end = "";
1892          if ($is_time_over) {
1893              $condition_date_end = " AND date_end < CURDATE() AND date_end != '0000-00-00' ";
1894          } else {
1895              $condition_date_end = " AND (date_end >= CURDATE() OR date_end = '0000-00-00') ";
1896          }
1897  
1898          $sessions_sql = "SELECT DISTINCT id, session_category_id
1899                                  FROM $tbl_session_user, $tbl_session
1900                                  WHERE id_session=id AND id_user=$user_id $condition_date_end
1901                                  ORDER BY session_category_id, date_start, date_end";
1902  
1903          $result = Database::query($sessions_sql,__FILE__,__LINE__);
1904          if (Database::num_rows($result)>0) {
1905              while ($row = Database::fetch_array($result)) {
1906                  $categories[$row['session_category_id']][] = $row['id'];
1907              }
1908          }
1909  
1910          // get the list of sessions where the user is subscribed as coach in a course $tbl_session_course_user
1911          /*$sessions_sql = "SELECT DISTINCT id, session_category_id
1912                                  FROM $tbl_session as session
1913                                  INNER JOIN $tbl_session_course as session_rel_course
1914                                      ON session_rel_course.id_session = session.id
1915                                      AND session_rel_course.id_coach = $user_id
1916                                  ORDER BY session_category_id, date_start, date_end";*/
1917  
1918          $sessions_sql = "SELECT DISTINCT id, session_category_id
1919                                  FROM $tbl_session as session
1920                                  INNER JOIN $tbl_session_course_user as session_rel_course_user
1921                                      ON session_rel_course_user.id_session = session.id
1922                                      AND session_rel_course_user.id_user = $user_id
1923                                      AND session_rel_course_user.status = 2    $condition_date_end
1924                                  ORDER BY session_category_id, date_start, date_end";
1925  
1926          $result = Database::query($sessions_sql,__FILE__,__LINE__);
1927          if (Database::num_rows($result)>0) {
1928              while ($row = Database::fetch_array($result)) {
1929                  $categories[$row['session_category_id']][] = $row['id'];
1930              }
1931          }
1932  
1933          // get the list of sessions where the user is subscribed as coach
1934          $sessions_sql = "SELECT DISTINCT id, session_category_id
1935                                  FROM $tbl_session as session
1936                                  WHERE session.id_coach = $user_id $condition_date_end
1937                                  ORDER BY session_category_id, date_start, date_end";
1938  
1939          $result = Database::query($sessions_sql,__FILE__,__LINE__);
1940          if (Database::num_rows($result)>0) {
1941              while ($row = Database::fetch_array($result)) {
1942                  $categories[$row['session_category_id']][] = $row['id'];
1943              }
1944          }
1945          return $categories;
1946      }
1947  
1948      /**
1949       * Gives a list of [session_id-course_code] => [status] for the current user.
1950       * @param integer $user_id
1951       * @return array  list of statuses (session_id-course_code => status)
1952       */
1953  	public static function get_personal_session_course_list($user_id) {
1954          // Database Table Definitions
1955          $tbl_course_user             = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
1956          $tbl_course                 = Database :: get_main_table(TABLE_MAIN_COURSE);
1957          $tbl_user                     = Database :: get_main_table(TABLE_MAIN_USER);
1958          $tbl_session                 = Database :: get_main_table(TABLE_MAIN_SESSION);
1959          $tbl_session_user            = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
1960          $tbl_course_user             = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
1961          $tbl_session_course         = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
1962          $tbl_session_course_user     = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
1963  
1964          if ($user_id != strval(intval($user_id))) return array();
1965  
1966          //we filter the courses from the URL
1967          $join_access_url = $where_access_url = '';
1968          global $_configuration;
1969          if ($_configuration['multiple_access_urls'] == true) {
1970              $access_url_id = api_get_current_access_url_id();
1971              if ($access_url_id != -1) {
1972                  $tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
1973                  $join_access_url = "LEFT JOIN $tbl_url_course url_rel_course ON url_rel_course.course_code= course.code";
1974                  $where_access_url = " AND access_url_id = $access_url_id ";
1975              }
1976          }
1977  
1978          // variable initialisation
1979          $personal_course_list_sql = '';
1980          $personal_course_list = array();
1981  
1982          //Courses in which we suscribed out of any session
1983          /*$personal_course_list_sql = "SELECT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i,
1984                                              course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort,
1985                                              course_rel_user.user_course_cat user_course_cat
1986                                              FROM    ".$tbl_course."       course,".$main_course_user_table."   course_rel_user
1987                                              WHERE course.code = course_rel_user.course_code"."
1988                                              AND   course_rel_user.user_id = '".$user_id."'
1989                                              ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC,i";*/
1990  
1991          $tbl_user_course_category = Database :: get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
1992  
1993          $personal_course_list_sql = "SELECT course.*, course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat
1994              FROM ".$tbl_course_user." course_rel_user
1995                  LEFT JOIN ".$tbl_course." course
1996                      ON course.code = course_rel_user.course_code
1997                  LEFT JOIN ".$tbl_user_course_category." user_course_category
1998                      ON course_rel_user.user_course_cat = user_course_category.id
1999                  $join_access_url
2000              WHERE  course_rel_user.user_id = '".$user_id."'  $where_access_url
2001                                          ORDER BY user_course_category.sort, course_rel_user.sort, course.title ASC";
2002  
2003          $course_list_sql_result = api_sql_query($personal_course_list_sql, __FILE__, __LINE__);
2004          while ($result_row = Database::fetch_array($course_list_sql_result)) {
2005              $personal_course_list[] = $result_row;
2006          }
2007  
2008          // get the list of sessions where the user is subscribed as student
2009          $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
2010                                  FROM $tbl_session_user, $tbl_session
2011                                  WHERE id_session=id AND id_user=$user_id
2012                                  AND (date_start <= CURDATE() AND date_end >= CURDATE() OR date_start='0000-00-00')
2013                                  ORDER BY date_start, date_end, name";
2014          $result = Database::query($sessions_sql,__FILE__,__LINE__);
2015          $sessions=Database::store_result($result);
2016          $sessions = array_merge($sessions , Database::store_result($result));
2017  
2018  
2019          // get the list of sessions where the user is subscribed as student where visibility = SESSION_VISIBLE_READ_ONLY = 1  SESSION_VISIBLE = 2
2020          $sessions_out_date_sql = "SELECT DISTINCT id, name, date_start, date_end
2021                                  FROM $tbl_session_user, $tbl_session
2022                                  WHERE id_session=id AND id_user=$user_id
2023                                  AND (date_end <= CURDATE() AND date_end<>'0000-00-00') AND (visibility = ".SESSION_VISIBLE_READ_ONLY." || visibility = ".SESSION_VISIBLE.")
2024                                  ORDER BY date_start, date_end, name";
2025          $result_out_date = Database::query($sessions_out_date_sql,__FILE__,__LINE__);
2026          $sessions_out_date=Database::store_result($result_out_date);
2027          $sessions = array_merge($sessions , $sessions_out_date);
2028  
2029  
2030  
2031          // get the list of sessions where the user is subscribed as coach in a course
2032          $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end, DATE_SUB(date_start, INTERVAL nb_days_access_before_beginning DAY), ADDDATE(date_end, INTERVAL nb_days_access_after_end DAY)
2033              FROM $tbl_session as session
2034                  INNER JOIN $tbl_session_course_user as session_rel_course_user
2035                  ON session_rel_course_user.id_session = session.id
2036                  AND session_rel_course_user.id_user = $user_id AND session_rel_course_user.status = 2
2037              WHERE (CURDATE() >= DATE_SUB(date_start, INTERVAL nb_days_access_before_beginning DAY)
2038                  AND CURDATE() <= ADDDATE(date_end, INTERVAL nb_days_access_after_end DAY)
2039                  OR date_start='0000-00-00')
2040              ORDER BY date_start, date_end, name";
2041  
2042          $result = Database::query($sessions_sql, __FILE__, __LINE__);
2043  
2044          $session_is_coach = Database::store_result($result);
2045  
2046          $sessions = array_merge($sessions, $session_is_coach);
2047  
2048          // get the list of sessions where the user is subscribed as coach
2049          $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
2050              FROM $tbl_session as session
2051              WHERE session.id_coach = $user_id
2052                  AND (CURDATE() >= DATE_SUB(date_start, INTERVAL nb_days_access_before_beginning DAY)
2053                  AND CURDATE() <= ADDDATE(date_end, INTERVAL nb_days_access_after_end DAY)
2054                  OR date_start='0000-00-00')
2055              ORDER BY date_start, date_end, name";
2056          $result = Database::query($sessions_sql, __FILE__, __LINE__);
2057  
2058          $sessions = array_merge($sessions, Database::store_result($result));
2059  
2060          if (api_is_allowed_to_create_course()) {
2061              foreach($sessions as $enreg) {
2062                  $id_session = $enreg['id'];
2063                  $personal_course_list_sql = "SELECT DISTINCT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, ".(api_is_western_name_order() ? "CONCAT(user.firstname,' ',user.lastname)" : "CONCAT(user.lastname,' ',user.firstname)")." t, email, course.course_language l, 1 sort, category_code user_course_cat, date_start, date_end, session.id as id_session, session.name as session_name
2064                      FROM $tbl_session_course_user as session_course_user
2065                          INNER JOIN $tbl_course AS course
2066                              ON course.code = session_course_user.course_code
2067                          INNER JOIN $tbl_session as session
2068                              ON session.id = session_course_user.id_session
2069                          LEFT JOIN $tbl_user as user
2070                              ON user.user_id = session_course_user.id_user
2071                      WHERE session_course_user.id_session = $id_session
2072                          AND ((session_course_user.id_user=$user_id AND session_course_user.status = 2) OR session.id_coach=$user_id)
2073                      ORDER BY i";
2074  
2075                  $course_list_sql_result = Database::query($personal_course_list_sql, __FILE__, __LINE__);
2076  
2077                  while ($result_row = Database::fetch_array($course_list_sql_result)) {
2078                      $result_row['s'] = 2;
2079                      $key = $result_row['id_session'].' - '.$result_row['k'];
2080                      $personal_course_list[$key] = $result_row;
2081                  }
2082              }
2083          }
2084  
2085          foreach ($sessions as $enreg) {
2086              $id_session = $enreg['id'];
2087              /*$personal_course_list_sql = "SELECT DISTINCT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, CONCAT(user.lastname,' ',user.firstname) t, email, course.course_language l, 1 sort, category_code user_course_cat, date_start, date_end, session.id as id_session, session.name as session_name, IF(session_course.id_coach = ".$user_id.",'2', '5')
2088                                           FROM $tbl_session_course as session_course
2089                                           INNER JOIN $tbl_course AS course
2090                                               ON course.code = session_course.course_code
2091                                           LEFT JOIN $tbl_user as user
2092                                              ON user.user_id = session_course.id_coach
2093                                           INNER JOIN $tbl_session_course_user
2094                                              ON $tbl_session_course_user.id_session = $id_session
2095                                              AND $tbl_session_course_user.id_user = $user_id
2096                                          INNER JOIN $tbl_session  as session
2097                                              ON session_course.id_session = session.id
2098                                           WHERE session_course.id_session = $id_session
2099                                           ORDER BY i";
2100                  */
2101              // this query is very similar to the above query, but it will check the session_rel_course_user table if there are courses registered to our user or not
2102              $personal_course_list_sql = "SELECT distinct course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, CONCAT(user.lastname,' ',user.firstname) t, email, course.course_language l, 1 sort, category_code user_course_cat, date_start, date_end, session.id as id_session, session.name as session_name, IF((session_course_user.id_user = 3 AND session_course_user.status=2),'2', '5')
2103                                          FROM $tbl_session_course_user as session_course_user
2104                                          INNER JOIN $tbl_course AS course
2105                                          ON course.code = session_course_user.course_code AND session_course_user.id_session = $id_session
2106                                          INNER JOIN $tbl_session as session ON session_course_user.id_session = session.id
2107                                          LEFT JOIN $tbl_user as user ON user.user_id = session_course_user.id_user
2108                                          WHERE session_course_user.id_user = $user_id  ORDER BY i";
2109  
2110              $course_list_sql_result = Database::query($personal_course_list_sql, __FILE__, __LINE__);
2111  
2112              while ($result_row = Database::fetch_array($course_list_sql_result)) {
2113                  $key = $result_row['id_session'].' - '.$result_row['k'];
2114                  $result_row['s'] = $result_row['14'];
2115  
2116                  if (!isset($personal_course_list[$key])) {
2117                      $personal_course_list[$key] = $result_row;
2118                  }
2119              }
2120          }
2121          //print_r($personal_course_list);
2122  
2123          return $personal_course_list;
2124      }
2125      /**
2126       * Gives a list of courses for the given user in the given session
2127       * @param integer $user_id
2128       * @return array  list of statuses (session_id-course_code => status)
2129       */
2130  	public static function get_courses_list_by_session ($user_id, $session_id) {
2131          // Database Table Definitions
2132          $tbl_session                 = Database :: get_main_table(TABLE_MAIN_SESSION);
2133          $tbl_session_course         = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
2134          $tbl_session_course_user     = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
2135  
2136          $user_id = intval($user_id);
2137          $session_id = intval($session_id);
2138          //we filter the courses from the URL
2139          $join_access_url=$where_access_url='';
2140          global $_configuration;
2141          if ($_configuration['multiple_access_urls']==true) {
2142              $access_url_id = api_get_current_access_url_id();
2143              if($access_url_id!=-1) {
2144                  $tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
2145                  $join_access_url= ",$tbl_url_course url_rel_course ";
2146                  $where_access_url=" AND access_url_id = $access_url_id  AND url_rel_course.course_code = scu.course_code";
2147              }
2148          }
2149  
2150          // variable initialisation
2151          $personal_course_list_sql = '';
2152          $personal_course_list = array();
2153          $courses = array();
2154  
2155          // this query is very similar to the above query, but it will check the session_rel_course_user table if there are courses registered to our user or not
2156          $personal_course_list_sql = "SELECT distinct scu.course_code as code
2157                                      FROM $tbl_session_course_user as scu
2158                                      $join_access_url
2159                                      WHERE scu.id_user = $user_id
2160                                      AND scu.id_session = $session_id
2161                                      $where_access_url
2162                                      ORDER BY code";
2163          $course_list_sql_result = Database::query($personal_course_list_sql, __FILE__, __LINE__);
2164  
2165          if (Database::num_rows($course_list_sql_result)>0) {
2166              while ($result_row = Database::fetch_array($course_list_sql_result)) {
2167                  $result_row['status'] = 5;
2168                  if (!in_array($result_row['code'],$courses)) {
2169                      $personal_course_list[] = $result_row;
2170                      $courses[] = $result_row['code'];
2171                  }
2172              }
2173          }
2174  
2175          if(api_is_allowed_to_create_course()) {
2176              $personal_course_list_sql = "SELECT DISTINCT scu.course_code as code
2177                                          FROM $tbl_session_course_user as scu, $tbl_session as s
2178                                          $join_access_url
2179                                          WHERE s.id = $session_id
2180                                          AND scu.id_session = s.id
2181                                          AND ((scu.id_user=$user_id AND scu.status=2) OR s.id_coach=$user_id)
2182                                          $where_access_url
2183                                          ORDER BY code";
2184  
2185  
2186  
2187              $course_list_sql_result = Database::query($personal_course_list_sql, __FILE__, __LINE__);
2188  
2189              if (Database::num_rows($course_list_sql_result)>0) {
2190                  while ($result_row = Database::fetch_array($course_list_sql_result)) {
2191                      $result_row['status'] = 2;
2192                      if (!in_array($result_row['code'],$courses)) {
2193                          $personal_course_list[] = $result_row;
2194                          $courses[] = $result_row['code'];
2195                      }
2196                  }
2197              }
2198          }
2199          return $personal_course_list;
2200      }
2201  
2202      /**
2203       * Get user id from a username
2204       * @param    string    Username
2205       * @return    int        User ID (or false if not found)
2206       */
2207  	public static function get_user_id_from_username($username) {
2208          $username = Database::escape_string($username);
2209          $t_user = Database::get_main_table(TABLE_MAIN_USER);
2210          $sql = "SELECT user_id FROM $t_user WHERE username = '$username'";
2211          $res = Database::query($sql, __FILE__, __LINE__);
2212          if ($res === false) { return false; }
2213          if (Database::num_rows($res) !== 1) { return false; }
2214          $row = Database::fetch_array($res);
2215          return $row['user_id'];
2216      }
2217  
2218      /**
2219       * Get the users files upload from his share_folder
2220       * @param    string    User ID
2221       * @param   string  course directory
2222       * @param   int     deprecated
2223       * @return    int        User ID (or false if not found)
2224       */
2225  	public static function get_user_upload_files_by_course($user_id, $course, $column = 2) {
2226          $return = '';
2227          if (!empty($user_id) && !empty($course)) {
2228              $user_id = intval($user_id);
2229              $path = api_get_path(SYS_COURSE_PATH).$course.'/document/shared_folder/sf_user_'.$user_id.'/';
2230              $web_path = api_get_path(WEB_COURSE_PATH).$course.'/document/shared_folder/sf_user_'.$user_id.'/';
2231              $file_list = array();
2232  
2233              if (is_dir($path)) {
2234                  $handle = opendir($path);
2235                  while ($file = readdir($handle)) {
2236                      if ($file == '.' || $file == '..' || $file == '.htaccess' || is_dir($path.$file)) {
2237                          continue; // skip current/parent directory and .htaccess
2238                      }
2239                      $file_list[] = $file;
2240                  }
2241                  if (count($file_list) > 0) {
2242                      $return = $course;
2243                      $return .= '<ul>';
2244                  }
2245                  foreach ($file_list as $file) {
2246                      $return .= '<li><a href="'.$web_path.urlencode($file).'" target="_blank">'.htmlentities($file).'</a>';
2247                  }
2248                  $return .= '</ul>';
2249              }
2250          }
2251          return $return;
2252      }
2253  
2254      /**
2255       * Gets the API key (or keys) and return them into an array
2256       * @param   int     Optional user id (defaults to the result of api_get_user_id())
2257       * @result  array   Non-indexed array containing the list of API keys for this user, or FALSE on error
2258       */
2259      public static function get_api_keys($user_id = null, $api_service = 'dokeos') {
2260          if ($user_id != strval(intval($user_id))) return false;
2261          if (empty($user_id)) { $user_id = api_get_user_id(); }
2262          if ($user_id === false) return false;
2263          $service_name = Database::escape_string($api_service);
2264          if (is_string($service_name) === false) { return false;}
2265          $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
2266          $sql = "SELECT id, api_key FROM $t_api WHERE user_id = ".$user_id." AND api_service='".$api_service."';";
2267          $res = Database::query($sql, __FILE__, __LINE__);
2268          if ($res === false) return false; //error during query
2269          $num = Database::num_rows($res);
2270          if ($num == 0) return false;
2271          $list = array();
2272          while ($row = Database::fetch_array($res)) {
2273              $list[$row['id']] = $row['api_key'];
2274          }
2275          return $list;
2276      }
2277  
2278      /**
2279       * Adds a new API key to the users' account
2280       * @param   int     Optional user ID (defaults to the results of api_get_user_id())
2281       * @return  boolean True on success, false on failure
2282       */
2283      public static function add_api_key($user_id = null, $api_service = 'dokeos') {
2284          if ($user_id != strval(intval($user_id))) return false;
2285          if (empty($user_id)) { $user_id = api_get_user_id(); }
2286          if ($user_id === false) return false;
2287          $service_name = Database::escape_string($api_service);
2288          if (is_string($service_name) === false) { return false; }
2289          $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
2290          $md5 = md5((time() + ($user_id * 5)) - rand(10000, 10000)); //generate some kind of random key
2291          $sql = "INSERT INTO $t_api (user_id, api_key,api_service) VALUES ($user_id,'$md5','$service_name')";
2292          $res = Database::query($sql, __FILE__, __LINE__);
2293          if ($res === false) return false; //error during query
2294          $num = Database::insert_id();
2295          return ($num == 0) ? false : $num;
2296      }
2297  
2298      /**
2299       * Deletes an API key from the user's account
2300       * @param   int     API key's internal ID
2301       * @return  boolean True on success, false on failure
2302       */
2303      public static function delete_api_key($key_id) {
2304          if ($key_id != strval(intval($key_id))) return false;
2305          if ($key_id === false) return false;
2306          $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
2307          $sql = "SELECT * FROM $t_api WHERE id = ".$key_id;
2308          $res = Database::query($sql, __FILE__, __LINE__);
2309          if ($res === false) return false; //error during query
2310          $num = Database::num_rows($res);
2311          if ($num !== 1) return false;
2312          $sql = "DELETE FROM $t_api WHERE id = ".$key_id;
2313          $res = Database::query($sql, __FILE__, __LINE__);
2314          if ($res === false) return false; //error during query
2315          return true;
2316      }
2317  
2318      /**
2319       * Regenerate an API key from the user's account
2320       * @param   int     user ID (defaults to the results of api_get_user_id())
2321       * @param   string  API key's internal ID
2322       * @return  int        num
2323       */
2324      public static function update_api_key($user_id, $api_service) {
2325          if ($user_id != strval(intval($user_id))) return false;
2326          if ($user_id === false) return false;
2327          $service_name = Database::escape_string($api_service);
2328          if (is_string($service_name) === false) { return false; }
2329          $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
2330          $sql = "SELECT id FROM $t_api WHERE user_id=".$user_id." AND api_service='".$api_service."'";
2331          $res = Database::query($sql, __FILE__, __LINE__);
2332          $num = Database::num_rows($res);
2333          if ($num == 1) {
2334              $id_key = Database::fetch_array($res, 'ASSOC');
2335              self::delete_api_key($id_key['id']);
2336              $num = self::add_api_key($user_id, $api_service);
2337          } elseif ($num == 0) {
2338              $num = self::add_api_key($user_id);
2339          }
2340          return $num;
2341      }
2342  
2343      /**
2344       * @param   int     user ID (defaults to the results of api_get_user_id())
2345       * @param   string    API key's internal ID
2346       * @return  int        row ID, not return a boolean
2347       */
2348      public static function get_api_key_id($user_id, $api_service) {
2349          if ($user_id != strval(intval($user_id))) return false;
2350          if ($user_id === false) return false;
2351          $service_name = Database::escape_string($api_service);
2352          if (is_string($service_name) === false) { return false; }
2353          $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
2354          $sql = "SELECT id FROM $t_api WHERE user_id=".$user_id." AND api_service='".$api_service."'";
2355          $res = Database::query($sql, __FILE__, __LINE__);
2356          $row = Database::fetch_array($res, 'ASSOC');
2357          return $row['id'];
2358      }
2359  
2360      /**
2361       * Subscribes users to the given session and optionally (default) unsubscribes previous users
2362       * @param    int        Session ID
2363       * @param    array    List of user IDs
2364       * @param    bool    Whether to unsubscribe existing users (true, default) or not (false)
2365       * @return    void    Nothing, or false on error
2366       */
2367      public static function suscribe_users_to_session($id_session, $UserList, $empty_users = true) {
2368  
2369          if ($id_session != strval(intval($id_session))) return false;
2370          foreach ($UserList as $intUser) {
2371              if ($intUser != strval(intval($intUser))) return false;
2372          }
2373          $tbl_session_rel_course                = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
2374          $tbl_session_rel_course_rel_user    = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
2375          $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
2376          $tbl_session                        = Database::get_main_table(TABLE_MAIN_SESSION);
2377          $sql = "SELECT id_user FROM $tbl_session_rel_user WHERE id_session='$id_session'";
2378          $result = Database::query($sql, __FILE__, __LINE__);
2379          $existingUsers = array();
2380          while($row = Database::fetch_array($result)) {
2381              $existingUsers[] = $row['id_user'];
2382          }
2383          $sql = "SELECT course_code FROM $tbl_session_rel_course WHERE id_session='$id_session'";
2384          $result = Database::query($sql, __FILE__, __LINE__);
2385  
2386          $CourseList = array();
2387  
2388          while($row = Database::fetch_array($result)) {
2389              $CourseList[] = $row['course_code'];
2390          }
2391  
2392          foreach ($CourseList as $enreg_course) {
2393              // for each course in the session
2394              $nbr_users = 0;
2395              $enreg_course = Database::escape_string($enreg_course);
2396              // delete existing users
2397              if ($empty_users !== false) {
2398                  foreach ($existingUsers as $existing_user) {
2399                      if(!in_array($existing_user, $UserList)) {
2400                          $sql = "DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session='$id_session' AND course_code='$enreg_course' AND id_user='$existing_user'";
2401                          Database::query($sql, __FILE__, __LINE__);
2402  
2403                          if (Database::affected_rows()) {
2404                              $nbr_users--;
2405                          }
2406                      }
2407                  }
2408              }
2409              // insert new users into session_rel_course_rel_user and ignore if they already exist
2410              foreach ($UserList as $enreg_user) {
2411                  if (!in_array($enreg_user, $existingUsers)) {
2412                      $enreg_user = Database::escape_string($enreg_user);
2413                      $insert_sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user(id_session,course_code,id_user) VALUES('$id_session','$enreg_course','$enreg_user')";
2414                      Database::query($insert_sql, __FILE__, __LINE__);
2415  
2416                      if (Database::affected_rows()) {
2417                          $nbr_users++;
2418                      }
2419                  }
2420              }
2421              // count users in this session-course relation
2422              $sql = "SELECT COUNT(id_user) as nbUsers FROM $tbl_session_rel_course_rel_user WHERE id_session='$id_session' AND course_code='$enreg_course'";
2423              $rs = Database::query($sql, __FILE__, __LINE__);
2424              list($nbr_users) = Database::fetch_array($rs);
2425              // update the session-course relation to add the users total
2426              $update_sql = "UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users WHERE id_session='$id_session' AND course_code='$enreg_course'";
2427              Database::query($update_sql, __FILE__, __LINE__);
2428          }
2429          // delete users from the session
2430          if ($empty_users !== false) {
2431              Database::query("DELETE FROM $tbl_session_rel_user WHERE id_session = $id_session", __FILE__, __LINE__);
2432          }
2433          // insert missing users into session
2434          $nbr_users = 0;
2435          foreach ($UserList as $enreg_user) {
2436              $enreg_user = Database::escape_string($enreg_user);
2437              $nbr_users++;
2438              $insert_sql = "INSERT IGNORE INTO $tbl_session_rel_user(id_session, id_user) VALUES('$id_session','$enreg_user')";
2439              Database::query($insert_sql, __FILE__, __LINE__);
2440  
2441          }
2442          // update number of users in the session
2443          $nbr_users = count($UserList);
2444          $update_sql = "UPDATE $tbl_session SET nbr_users= $nbr_users WHERE id='$id_session' ";
2445          Database::query($update_sql, __FILE__, __LINE__);
2446      }
2447  
2448      /**
2449       * Checks if a user_id is platform admin
2450       * @param   int user ID
2451       * @return  boolean True if is admin, false otherwise
2452       * @see main_api.lib.php::api_is_platform_admin() for a context-based check
2453       */
2454      function is_admin($user_id) {
2455          if (empty($user_id) or $user_id != strval(intval($user_id))) { return false; }
2456          $admin_table = Database::get_main_table(TABLE_MAIN_ADMIN);
2457          $sql = "SELECT * FROM $admin_table WHERE user_id = $user_id";
2458          $res = Database::query($sql);
2459          return Database::num_rows($res) === 1;
2460      }
2461  
2462      /**
2463       * Get the total count of users
2464       * @return    mixed    Number of users or false on error
2465       */
2466      public static function get_number_of_users() {
2467          $t_u = Database::get_main_table(TABLE_MAIN_USER);
2468          $sql = "SELECT count(*) FROM $t_u";
2469          $res = Database::query($sql);
2470          if (Database::num_rows($res) === 1) {
2471              return (int) Database::result($res, 0, 0);
2472          }
2473          return false;
2474      }
2475  
2476      /**
2477       * Resize a picture
2478       *
2479       * @param  string file picture
2480       * @param  int size in pixels
2481       * @return obj image object
2482       */
2483  	public static function resize_picture($file, $max_size_for_picture) {
2484          if (!class_exists('image')) {
2485              require_once api_get_path(LIBRARY_PATH).'image.lib.php';
2486          }
2487           $temp = new image($file);
2488           $picture_infos = api_getimagesize($file);
2489          if ($picture_infos[0] > $max_size_for_picture) {
2490              $thumbwidth = $max_size_for_picture;
2491              if (empty($thumbwidth) or $thumbwidth == 0) {
2492                  $thumbwidth = $max_size_for_picture;
2493              }
2494              $new_height = round(($thumbwidth / $picture_infos[0]) * $picture_infos[1]);
2495              if ($new_height > $max_size_for_picture)
2496              $new_height = $thumbwidth;
2497              $temp->resize($thumbwidth, $new_height, 0);
2498          }
2499          return $temp;
2500      }
2501  
2502      /**
2503       * Gets the current user image
2504       * @param string user id
2505       * @param string picture user name
2506       * @param string height
2507       * @param string picture size it can be small_,  medium_  or  big_
2508       * @param string style css
2509       * @return array with the file and the style of an image i.e $array['file'] $array['style']
2510       */
2511     public static function get_picture_user($user_id, $picture_file, $height, $size_picture = 'medium_', $style = '') {
2512          $patch_profile = 'upload/users/';
2513          $picture = array();
2514          $picture['style'] = $style;
2515          if ($picture_file == 'unknown.jpg') {
2516              $picture['file'] = api_get_path(WEB_CODE_PATH).'img/'.$picture_file;
2517              return $picture;
2518          }
2519          $image_array_sys = self::get_user_picture_path_by_id($user_id, 'system', false, true);
2520          $image_array = self::get_user_picture_path_by_id($user_id, 'web', false, true);
2521          $file = $image_array_sys['dir'].$size_picture.$picture_file;
2522          if (file_exists($file)) {
2523              $picture['file'] = $image_array['dir'].$size_picture.$picture_file;
2524              $picture['style'] = '';
2525              if ($height > 0) {
2526                  $dimension = api_getimagesize($picture['file']);
2527                  $margin = (($height - $dimension[1]) / 2);
2528                  //@ todo the padding-top should not be here
2529                  $picture['style'] = ' style="padding-top:'.$margin.'px; width:'.$dimension[0].'px; height:'.$dimension[1].';" ';
2530              }
2531          } else {
2532              //$file = api_get_path(SYS_CODE_PATH).$patch_profile.$user_id.'/'.$picture_file;
2533              $file = $image_array_sys['dir'].$picture_file;
2534              if (file_exists($file) && !is_dir($file)) {
2535                  $picture['file'] = $image_array['dir'].$picture_file;
2536              } else {
2537                  switch ($size_picture) {
2538                      case 'big_' :
2539                          $picture['file'] = api_get_path(WEB_CODE_PATH).'img/unknown.jpg'; break;
2540                      case 'medium_' :
2541                          $picture['file'] = api_get_path(WEB_CODE_PATH).'img/unknown_50_50.jpg'; break;
2542                      case 'small_' :
2543                          $picture['file'] = api_get_path(WEB_CODE_PATH).'img/unknown.jpg'; break;
2544                      default:
2545                          $picture['file'] = api_get_path(WEB_CODE_PATH).'img/unknown.jpg'; break;
2546                  }
2547  
2548              }
2549          }
2550          return $picture;
2551      }
2552  
2553      /**
2554       * @author Isaac flores <isaac.flores@dokeos.com>
2555       * @param string The email administrator
2556       * @param integer The user id
2557       * @param string The message title
2558       * @param string The content message
2559       */
2560     	  public static function send_message_in_outbox($email_administrator, $user_id, $title, $content) {
2561          $table_message = Database::get_main_table(TABLE_MESSAGE);
2562          $table_user = Database::get_main_table(TABLE_MAIN_USER);
2563          $title = api_utf8_decode($title);
2564          $content = api_utf8_decode($content);
2565          $email_administrator = Database::escape_string($email_administrator);
2566          //message in inbox
2567          $sql_message_outbox = 'SELECT user_id from '.$table_user.' WHERE email="'.$email_administrator.'" ';
2568          //$num_row_query = Database::num_rows($sql_message_outbox);
2569          $res_message_outbox = Database::query($sql_message_outbox, __FILE__, __LINE__);
2570          $array_users_administrator = array();
2571          while ($row_message_outbox = Database::fetch_array($res_message_outbox, 'ASSOC')) {
2572              $array_users_administrator[] = $row_message_outbox['user_id'];
2573          }
2574          //allow to insert messages in outbox
2575          for ($i = 0; $i < count($array_users_administrator); $i++) {
2576              $sql_insert_outbox = "INSERT INTO $table_message(user_sender_id, user_receiver_id, msg_status, send_date, title, content ) ".
2577                      " VALUES (".
2578                       "'".(int)$user_id."', '".(int)($array_users_administrator[$i])."', '4', '".date('Y-m-d H:i:s')."','".Database::escape_string($title)."','".Database::escape_string($content)."'".
2579                       ")";
2580              $rs = Database::query($sql_insert_outbox, __FILE__, __LINE__);
2581          }
2582      }
2583  
2584      /*
2585       *
2586       * USER TAGS
2587       *
2588       * Intructions to create a new user tag by Julio Montoya <gugli100@gmail.com>
2589       *
2590       * 1. Create a new extra field in main/admin/user_fields.php with the "TAG" field type make it available and visible. Called it "books" for example.
2591       * 2. Go to profile main/auth/profile.php There you will see a special input (facebook style) that will show suggestions of tags.
2592       * 3. Step 2 will not work since this special input needs a file called "main/user/books.php" In this case. In order to have this file copy and paste from this file main/user/tag.php
2593       * 4. All the tags are registered in the user_tag table and the relationship between user and tags is in the user_rel_tag table
2594       * 5. Test and enjoy.
2595       *
2596       */
2597  
2598      /**
2599       * Gets the tags of a specific field_id
2600       *
2601       * @param int field_id
2602       * @param string how we are going to result value in array or in a string (json)
2603       * @return mixed
2604       * @since Nov 2009
2605       * @version 1.8.6.2
2606       */
2607  	public static function get_tags($tag, $field_id, $return_format='json',$limit=10) {
2608          // database table definition
2609          $table_user_tag            = Database::get_main_table(TABLE_MAIN_TAG);
2610          $table_user_tag_values    = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
2611          $field_id = intval($field_id);             //like '%$tag%'
2612          $limit = intval($limit);
2613          $tag = Database::escape_string($tag);
2614          // all the information of the field
2615          $sql = "SELECT id, tag from $table_user_tag
2616                  WHERE field_id = $field_id AND tag LIKE '$tag%' ORDER BY tag LIMIT $limit";
2617          $result = Database::query($sql, __FILE__, __LINE__);
2618          $return = array();
2619          if (Database::num_rows($result)>0) {
2620              while ($row = Database::fetch_array($result,'ASSOC')) {
2621                  $return[] = array('caption'=>$row['tag'], 'value'=>$row['tag']);
2622              }
2623          }
2624          if ($return_format=='json') {
2625              $return =  json_encode($return);
2626          }
2627          return $return;
2628      }
2629  
2630  	public static function get_top_tags($field_id, $limit=100) {
2631          // database table definition
2632          $table_user_tag            = Database::get_main_table(TABLE_MAIN_TAG);
2633          $table_user_tag_values    = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
2634          $field_id                 = intval($field_id);
2635          $limit                     = intval($limit);
2636          // all the information of the field
2637          $sql = "SELECT count(*) count, tag FROM $table_user_tag_values  uv INNER JOIN $table_user_tag ut ON(ut.id = uv.tag_id)
2638                  WHERE field_id = $field_id GROUP BY tag_id ORDER BY count DESC LIMIT $limit";
2639          $result = Database::query($sql, __FILE__, __LINE__);
2640          $return = array();
2641          if (Database::num_rows($result)>0) {
2642              while ($row = Database::fetch_array($result,'ASSOC')) {
2643                  $return[] = $row;
2644              }
2645          }
2646          return $return;
2647      }
2648  
2649      /**
2650       * Get user's tags
2651       * @param int field_id
2652       * @param int user_id
2653       * @return array
2654       */
2655  	public static function get_user_tags($user_id,$field_id) {
2656          // database table definition
2657          $table_user_tag            = Database::get_main_table(TABLE_MAIN_TAG);
2658          $table_user_tag_values    = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
2659          $field_id = intval($field_id);
2660          $user_id = intval($user_id);
2661  
2662          // all the information of the field
2663          $sql = "SELECT ut.id, tag,count FROM $table_user_tag ut INNER JOIN $table_user_tag_values uv ON (uv.tag_id=ut.ID)
2664                  WHERE field_id = $field_id AND user_id = $user_id ORDER BY tag";
2665          $result = Database::query($sql, __FILE__, __LINE__);
2666          $return = array();
2667          if (Database::num_rows($result)> 0) {
2668              while ($row = Database::fetch_array($result,'ASSOC')) {
2669                  $return[$row['id']] = array('tag'=>$row['tag'],'count'=>$row['count']);
2670              }
2671          }
2672          return $return;
2673      }
2674  
2675  
2676          /**
2677       * Get user's tags
2678       * @param int field_id
2679       * @param int user_id
2680       * @return array
2681       */
2682  	public static function get_user_tags_to_string($user_id,$field_id) {
2683          // database table definition
2684          $table_user_tag            = Database::get_main_table(TABLE_MAIN_TAG);
2685          $table_user_tag_values    = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
2686          $field_id = intval($field_id);
2687          $user_id = intval($user_id);
2688  
2689          // all the information of the field
2690          $sql = "SELECT ut.id, tag,count FROM $table_user_tag ut INNER JOIN $table_user_tag_values uv ON (uv.tag_id=ut.ID)
2691                  WHERE field_id = $field_id AND user_id = $user_id ORDER BY tag";
2692          $result = Database::query($sql, __FILE__, __LINE__);
2693          $return = array();
2694          if (Database::num_rows($result)> 0) {
2695              while ($row = Database::fetch_array($result,'ASSOC')) {
2696                  $return[$row['id']] = array('tag'=>$row['tag'],'count'=>$row['count']);
2697              }
2698          }
2699          $user_tags = $return;
2700          $tag_tmp = array();
2701          foreach ($user_tags as $tag) {
2702              $tag_tmp[] = '<a href="'.api_get_path(WEB_PATH).'main/search/?q='.$tag['tag'].'">'.$tag['tag'].'</a>';
2703          }
2704          if (is_array($user_tags) && count($user_tags)>0) {
2705              $return = implode(', ',$tag_tmp);
2706          }
2707          return $return;
2708      }
2709  
2710  
2711      /**
2712       * Get the tag id
2713       * @param int $tag
2714       * @param int $field_id
2715       * @return int 0 if fails otherwise the tag id
2716       */
2717  	public function get_tag_id($tag, $field_id) {
2718          $table_user_tag            = Database::get_main_table(TABLE_MAIN_TAG);
2719          $tag = Database::escape_string($tag);
2720          $field_id = intval($field_id);
2721          //with COLLATE latin1_bin to select query in a case sensitive mode
2722          $sql = "SELECT id FROM $table_user_tag WHERE tag COLLATE latin1_bin  LIKE '$tag' AND field_id = $field_id";
2723          $result = Database::query($sql, __FILE__, __LINE__);
2724          if (Database::num_rows($result)>0) {
2725              $row = Database::fetch_array($result,'ASSOC');
2726              return $row['id'];
2727          } else {
2728              return 0;
2729          }
2730      }
2731  
2732      /**
2733       * Get the tag id
2734       * @param int $tag
2735       * @param int $field_id
2736       * @return int 0 if fails otherwise the tag id
2737       */
2738  	public function get_tag_id_from_id($tag_id, $field_id) {
2739          $table_user_tag            = Database::get_main_table(TABLE_MAIN_TAG);
2740          $tag_id = intval($tag_id);
2741          $field_id = intval($field_id);
2742          $sql = "SELECT id FROM $table_user_tag WHERE id = '$tag_id' AND field_id = $field_id";
2743          $result = Database::query($sql, __FILE__, __LINE__);
2744          if (Database::num_rows($result)>0) {
2745              $row = Database::fetch_array($result,'ASSOC');
2746              return $row['id'];
2747          } else {
2748              return false;
2749          }
2750      }
2751  
2752  
2753      /**
2754       * Adds a user-tag value
2755       * @param mixed $tag
2756       * @param int $user_id
2757       * @param int $field_id
2758       * @return bool
2759       */
2760  	public function add_tag($tag, $user_id, $field_id) {
2761          // database table definition
2762          $table_user_tag            = Database::get_main_table(TABLE_MAIN_TAG);
2763          $table_user_tag_values    = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
2764          $tag = Database::escape_string($tag);
2765          $user_id = intval($user_id);
2766          $field_id = intval($field_id);
2767  
2768          //&&  (substr($tag,strlen($tag)-1) == '@')
2769          /*$sent_by_user = false;
2770          if ( substr($tag,0,1) == '@')  {
2771              //is a value sent by the list
2772              $sent_by_user = true;
2773              $tag = substr($tag,1,strlen($tag)-2);
2774          }
2775          */
2776          $tag_id = UserManager::get_tag_id($tag,$field_id);
2777          //@todo we don't create tags with numbers
2778          if (is_numeric($tag)) {
2779              //the form is sending an id this means that the user select it from the list so it MUST exists
2780              /*$new_tag_id = UserManager::get_tag_id_from_id($tag,$field_id);
2781              if ($new_tag_id !== false) {
2782                  $sql = "UPDATE $table_user_tag SET count = count + 1 WHERE id  = $new_tag_id";
2783                  $result = Database::query($sql, __FILE__, __LINE__);
2784                  $last_insert_id = $new_tag_id;
2785              } else {
2786                  $sql = "INSERT INTO $table_user_tag (tag, field_id,count) VALUES ('$tag','$field_id', count + 1)";
2787                  $result = Database::query($sql, __FILE__, __LINE__);
2788                  $last_insert_id = Database::get_last_insert_id();
2789              }*/
2790          } else {
2791              //this is a new tag
2792              if ($tag_id == 0) {
2793                  //the tag doesn't exist
2794                  $sql = "INSERT INTO $table_user_tag (tag, field_id,count) VALUES ('$tag','$field_id', count + 1)";
2795                  $result = Database::query($sql, __FILE__, __LINE__);
2796                  $last_insert_id = Database::get_last_insert_id();
2797              } else {
2798                  //the tag exists we update it
2799                  $sql = "UPDATE $table_user_tag SET count = count + 1 WHERE id  = $tag_id";
2800                  $result = Database::query($sql, __FILE__, __LINE__);
2801                  $last_insert_id = $tag_id;
2802              }
2803          }
2804  
2805          if (!empty($last_insert_id) && ($last_insert_id!=0)) {
2806              //we insert the relationship user-tag
2807              $sql_select ="SELECT tag_id FROM $table_user_tag_values WHERE user_id = $user_id AND tag_id = $last_insert_id ";
2808              $result = Database::query($sql_select, __FILE__, __LINE__);
2809              //if the relationship does not exist we create it
2810              if (Database::num_rows($result)==0) {
2811                  $sql = "INSERT INTO $table_user_tag_values SET user_id = $user_id, tag_id = $last_insert_id";
2812                  $result = Database::query($sql, __FILE__, __LINE__);
2813              }
2814          }
2815      }
2816      /**
2817       * Deletes an user tag
2818       * @param int user id
2819       * @param int field id
2820       *
2821       */
2822  	public function delete_user_tags($user_id, $field_id) {
2823          // database table definition
2824          $table_user_tag            = Database::get_main_table(TABLE_MAIN_TAG);
2825          $table_user_tag_values    = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
2826          $tags = UserManager::get_user_tags($user_id, $field_id);
2827          //echo '<pre>';var_dump($tags);
2828          if(is_array($tags) && count($tags)>0) {
2829              foreach ($tags as $key=>$tag) {
2830                  if ($tag['count']>'0') {
2831                      $sql = "UPDATE $table_user_tag SET count = count - 1  WHERE id = $key ";
2832                      $result = Database::query($sql, __FILE__, __LINE__);
2833                  }
2834                  $sql = "DELETE FROM $table_user_tag_values WHERE user_id = $user_id AND tag_id = $key";
2835                  $result = Database::query($sql, __FILE__, __LINE__);
2836              }
2837  
2838          }
2839      }
2840  
2841      /**
2842       * Process the tag list comes from the UserManager::update_extra_field_value() function
2843       * @param array the tag list that will be added
2844       * @param int user id
2845       * @param int field id
2846       * @return bool
2847       */
2848  	public function process_tags($tags, $user_id, $field_id) {
2849  
2850          //We loop the tags and add it to the DB
2851          if (is_array($tags)) {
2852              foreach($tags as $tag) {
2853                  UserManager::add_tag($tag, $user_id, $field_id);
2854              }
2855          } else {
2856              UserManager::add_tag($tags,$user_id, $field_id);
2857          }
2858          return true;
2859      }
2860  
2861      /**
2862       * Gives a list of emails from all administrators
2863       * @author cvargas carlos.vargas@dokeos.com
2864       * @return array
2865       */
2866  	 public function get_emails_from_all_administrators() {
2867           $table_user = Database::get_main_table(TABLE_MAIN_USER);
2868           $table_admin = Database::get_main_table(TABLE_MAIN_ADMIN);
2869  
2870           $sql = "SELECT email from $table_user as u, $table_admin as a WHERE u.user_id=a.user_id";
2871           $result = Database::query($sql, __FILE__, __LINE__);
2872          $return = array();
2873          if (Database::num_rows($result)> 0) {
2874              while ($row = Database::fetch_array($result,'ASSOC')) {
2875                  $return[$row['email']] = $row;
2876              }
2877          }
2878          return $return;
2879       }
2880  
2881  
2882      /**
2883       * Searchs an user (tags, firstname, lastname and email )
2884       * @param string the tag
2885       * @param int field id of the tag
2886       * @return array
2887       */
2888  	public static function get_all_user_tags($tag, $field_id = 0, $from = null, $number_of_items = null) {
2889          // database table definition
2890  
2891          $user_table             = Database::get_main_table(TABLE_MAIN_USER);
2892          $table_user_tag            = Database::get_main_table(TABLE_MAIN_TAG);
2893          $table_user_tag_values    = Database::get_main_table(TABLE_MAIN_USER_REL_TAG);
2894          $field_id = intval($field_id);
2895          $tag = Database::escape_string($tag);    
2896                  $where_field = "";
2897          if ($field_id != 0) {
2898              $where_field = " field_id = $field_id AND ";
2899          }
2900          // all the information of the field
2901          $sql = "SELECT u.user_id,u.username,firstname, lastname, email, tag, picture_uri FROM $table_user_tag ut INNER JOIN $table_user_tag_values uv ON (uv.tag_id=ut.id)
2902                  INNER JOIN $user_table u ON(uv.user_id =u.user_id)
2903                  WHERE $where_field tag LIKE '$tag%' ".(api_get_user_id()?" AND u.user_id <> ".  api_get_user_id():"")." ORDER BY tag";
2904                  
2905                  if (isset($from) && isset($number_of_items)) {    
2906                      $from = intval($from);
2907                      $number_of_items = intval($number_of_items);
2908                      $sql .= " LIMIT $from,$number_of_items";
2909                  }
2910  
2911          $result = Database::query($sql, __FILE__, __LINE__);
2912          $return = array();
2913          if (Database::num_rows($result)> 0) {
2914              while ($row = Database::fetch_array($result,'ASSOC')) {
2915                  $return[$row['user_id']] = $row;
2916              }
2917          }
2918  
2919          $keyword = $tag;
2920          $sql = "SELECT u.user_id, u.username, firstname, lastname, email, picture_uri FROM $user_table u";
2921          global $_configuration;
2922          if ($_configuration['multiple_access_urls']==true && api_get_current_access_url_id()!=-1) {
2923              $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
2924              $sql.= " INNER JOIN $access_url_rel_user_table url_rel_user ON (u.user_id=url_rel_user.user_id)";
2925          }
2926  
2927          if (isset ($keyword)) {
2928                  $keyword = Database::escape_string($keyword);
2929                  //OR u.official_code LIKE '%".$keyword."%'
2930                  // OR u.email LIKE '%".$keyword."%'
2931                  $sql .= " WHERE (u.firstname LIKE '%".$keyword."%' OR u.lastname LIKE '%".$keyword."%'  OR u.username LIKE '%".$keyword."%'  )";
2932              }
2933          $keyword_active = true;
2934          //only active users
2935          if ($keyword_active) {
2936              $sql .= " AND u.active='1'";
2937          }
2938  
2939          // adding the filter to see the user's only of the current access_url
2940          if ($_configuration['multiple_access_urls']==true && api_get_current_access_url_id()!=-1) {
2941                  $sql.= " AND url_rel_user.access_url_id=".api_get_current_access_url_id();
2942          }
2943              
2944              if (api_get_user_id()) {
2945                  $sql .= " AND u.user_id <> ".api_get_user_id();
2946              }
2947              
2948          $direction = 'ASC';
2949          if (!in_array($direction, array('ASC','DESC'))) {
2950              $direction = 'ASC';
2951          }
2952  
2953          $column = intval($column);
2954              if (isset($from) && isset($number_of_items)) {
2955                  $sql .= " LIMIT $from,$number_of_items";
2956              }
2957              
2958          $res = Database::query($sql, __FILE__, __LINE__);
2959          if (Database::num_rows($res)> 0) {
2960              while ($row = Database::fetch_array($res,'ASSOC')) {
2961                  if (!in_array($row['user_id'], $return)) {
2962                      $return[$row['user_id']] = $row;
2963                  }
2964              }
2965          }
2966          return $return;
2967      }
2968  
2969      /**
2970       * Show the search form
2971       * @param string the value of the search box
2972       *
2973       */
2974  	public static function get_search_form($query) {
2975          echo'<form method="GET" action="'.api_get_path(WEB_PATH).'main/social/search.php">
2976          <table cellspacing="1" cellpadding="1" width="100%">
2977          <tr>
2978          <td align="right" width="200px">              
2979                    <input type="text" style="width:200px;" value="'.Security::remove_XSS($query).'" name="q"/>
2980          </td>
2981                  <td align="left">
2982                  <button style="margin:2px;float:none;" class="search" type="submit" value="search">'.get_lang('Search').'</button>
2983                  </td>
2984          </tr>
2985          </table></form>';
2986      }
2987      //deprecated
2988  	public function get_public_users($keyword, $from = 0, $number_of_items= 20, $column=2, $direction='ASC') {
2989  
2990              $admin_table = Database :: get_main_table(TABLE_MAIN_ADMIN);
2991              $sql = "SELECT
2992                           u.user_id                AS col0,
2993                           u.official_code        AS col1,
2994                           ".(api_is_western_name_order()
2995                           ? "u.firstname             AS col2,
2996                           u.lastname             AS col3,"
2997                           : "u.lastname             AS col2,
2998                           u.firstname             AS col3,")."
2999                           u.username                AS col4,
3000                           u.email                AS col5,
3001                           u.status                AS col6,
3002                           u.active                AS col7,
3003                           u.user_id                AS col8 ".
3004                           ", u.expiration_date      AS exp ".
3005                      " FROM $user_table u ";
3006  
3007              // adding the filter to see the user's only of the current access_url
3008              global $_configuration;
3009              if ((api_is_platform_admin() || api_is_session_admin()) && $_configuration['multiple_access_urls']==true && api_get_current_access_url_id()!=-1) {
3010                  $access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
3011                  $sql.= " INNER JOIN $access_url_rel_user_table url_rel_user ON (u.user_id=url_rel_user.user_id)";
3012              }
3013  
3014              if (isset ($keyword)) {
3015                  $keyword = Database::escape_string($keyword);
3016                  //OR u.official_code LIKE '%".$keyword."%'
3017                  $sql .= " WHERE (u.firstname LIKE '%".$keyword."%' OR u.lastname LIKE '%".$keyword."%'  OR u.username LIKE '%".$keyword."%'  OR u.email LIKE '%".$keyword."%' )";
3018              }
3019              $keyword_active = true;
3020              //only active users
3021              if ($keyword_active) {
3022                  $sql .= " AND u.active='1'";
3023              }
3024  
3025              // adding the filter to see the user's only of the current access_url
3026              if ($_configuration['multiple_access_urls']==true && api_get_current_access_url_id()!=-1) {
3027                      $sql.= " AND url_rel_user.access_url_id=".api_get_current_access_url_id();
3028              }
3029  
3030              if (!in_array($direction, array('ASC','DESC'))) {
3031                  $direction = 'ASC';
3032              }
3033  
3034              $column = intval($column);
3035              $from = intval($from);
3036              $number_of_items = intval($number_of_items);
3037  
3038              $sql .= " ORDER BY col$column $direction ";
3039              $sql .= " LIMIT $from,$number_of_items";
3040              $res = Database::query($sql, __FILE__, __LINE__);
3041  
3042              $users = array ();
3043              $t = time();
3044              while ($user = Database::fetch_row($res)) {
3045                  if ($user[7] == 1 && $user[9] != '0000-00-00 00:00:00') {
3046                      // check expiration date
3047                      $expiration_time = convert_mysql_date($user[9]);
3048                      // if expiration date is passed, store a special value for active field
3049                      if ($expiration_time < $t) {
3050                         $user[7] = '-1';
3051                      }
3052                  }
3053                  // forget about the expiration date field
3054                  $users[] = array($user[0],$user[1],$user[2],$user[3],$user[4],$user[5],$user[6],$user[7],$user[8]);
3055              }
3056              return $users;
3057          }
3058  	function show_menu() {
3059          /*
3060          echo '<div class="actions">';
3061          echo '<a href="/main/auth/profile.php">'.Display::return_icon('profile.png').' '.get_lang('PersonalData').'</a>';
3062          echo '<a href="/main/messages/inbox.php">'.Display::return_icon('inbox.png').' '.    get_lang('Inbox').'</a>';
3063          echo '<a href="/main/messages/outbox.php">'.Display::return_icon('outbox.png').' '.    get_lang('Outbox').'</a>';
3064          echo '<span style="float:right; padding-top:7px;">'.
3065               '<a href="/main/auth/profile.php?show=1">'.Display::return_icon('edit.png').' '.get_lang('Configuration').'</a>';
3066               '</span>';
3067          echo '</div>';*/
3068      }
3069      /**
3070       * Gives a list of course auto-register (field special_course)
3071       * @return array  list of course
3072       * @author Jhon Hinojosa <jhon.hinojosa@dokeos.com>
3073       * @since Dokeos 1.8.6.2
3074       */
3075  	public static function get_special_course_list() {
3076          // Database Table Definitions
3077          $tbl_course_user             = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
3078          $tbl_course                 = Database :: get_main_table(TABLE_MAIN_COURSE);
3079          $tbl_course_field             = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD);
3080          $tbl_course_field_value        = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
3081          $tbl_user_course_category   = Database :: get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
3082  
3083          //we filter the courses from the URL
3084          $join_access_url=$where_access_url='';
3085          global $_configuration;
3086          if ($_configuration['multiple_access_urls']==true) {
3087              $access_url_id = api_get_current_access_url_id();
3088              if($access_url_id!=-1) {
3089                  $tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
3090                  $join_access_url= "LEFT JOIN $tbl_url_course url_rel_course ON url_rel_course.course_code= course.code";
3091                  $where_access_url=" AND access_url_id = $access_url_id ";
3092              }
3093          }
3094  
3095          // Filter special courses
3096          $sql_special_course = "SELECT course_code FROM $tbl_course_field_value tcfv INNER JOIN $tbl_course_field tcf ON " .
3097                  " tcfv.field_id =  tcf.id WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 ";
3098          $special_course_result = Database::query($sql_special_course, __FILE__, __LINE__);
3099          $code_special_courses = '';
3100          if(Database::num_rows($special_course_result)>0) {
3101              $special_course_list = array();
3102              while ($result_row = Database::fetch_array($special_course_result)) {
3103                  $special_course_list[] = '"'.$result_row['course_code'].'"';
3104              }
3105              $code_special_courses = ' course.code IN ('.join($special_course_list, ',').') ';
3106          }
3107  
3108          // variable initialisation
3109          $course_list_sql = '';
3110          $course_list = array();
3111          if(!empty($code_special_courses)){
3112              $course_list_sql = "SELECT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat
3113                                              FROM    ".$tbl_course_user." course_rel_user
3114                                              LEFT JOIN ".$tbl_course." course
3115                                              ON course.code = course_rel_user.course_code
3116                                              LEFT JOIN ".$tbl_user_course_category." user_course_category
3117                                              ON course_rel_user.user_course_cat = user_course_category.id
3118                                              $join_access_url
3119                                              WHERE  $code_special_courses $where_access_url
3120                                              GROUP BY course.code
3121                                              ORDER BY user_course_category.sort,course.title,course_rel_user.sort ASC";
3122              $course_list_sql_result = api_sql_query($course_list_sql, __FILE__, __LINE__);
3123              while ($result_row = Database::fetch_array($course_list_sql_result)) {
3124                  $course_list[] = $result_row;
3125              }
3126          }
3127          return $course_list;
3128      }
3129  
3130  
3131  	public static function get_user_last_session_name_in_course($user_id, $course_code){
3132  
3133          $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
3134          $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
3135          $sql ='SELECT session.name FROM '.TABLE_MAIN_SESSION.' as session
3136                  INNER JOIN '.$tbl_session_course_user.' as session_course_user
3137                      on session_course_user.id_session = session.id
3138                  WHERE session_course_user.course_code="'.$course_code.'"
3139                  AND session_course_user.id_user="'.$user_id.'"
3140                  ORDER BY session_course_user.id_session DESC
3141                  LIMIT 1';
3142          $session_name="";
3143  
3144          $result_session_course_user = Database::query($sql, __FILE__, __LINE__);
3145          if(Database::num_rows($result_session_course_user)>0) {
3146              while ($result_row = Database::fetch_array($result_session_course_user)) {
3147                  $session_name = $result_row['name'];
3148              }
3149          }
3150          return $session_name;
3151  
3152      }
3153  
3154  
3155  	public static function get_user_manager_name($user_id){
3156  
3157          $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
3158          $sql='SELECT lastname FROM '.$tbl_user.' WHERE user_id="'.$user_id.'"';
3159          $res = Database::query($sql, __FILE__, __LINE__);
3160          if(Database::num_rows($res)>0) {
3161              return mysql_result($res, 0, "lastname");
3162          }
3163          else{
3164              return "";
3165          }
3166  
3167      }
3168  
3169  
3170  
3171  }