docs/include/classes/DAO/UsersDAO.class.php

   1 <?php
   2 /************************************************************************/
   3 /* AContent                                                             */
   4 /************************************************************************/
   5 /* Copyright (c) 2010                                                   */
   6 /* Inclusive Design Institute                                           */
   7 /*                                                                      */
   8 /* This program is free software. You can redistribute it and/or        */
   9 /* modify it under the terms of the GNU General Public License          */
  10 /* as published by the Free Software Foundation.                        */
  11 /************************************************************************/
  12
  13 /**
  14  * DAO for "users" table
  15  * @access      public
  16  * @author      Cindy Qi Li
  17  * @package     DAO
  18  */
  19
  20 if (!defined('TR_INCLUDE_PATH')) exit;
  21
  22 require_once(TR_INCLUDE_PATH. 'classes/DAO/DAO.class.php');
  23 require_once(TR_INCLUDE_PATH. 'classes/Utility.class.php');
  24
  25 class UsersDAO extends DAO {
  26
  27         /**
  28          * Validate if the given login/pwd is valid
  29          * @access  public
  30          * @param   login: login id or email
  31          *          pwd: password
  32          * @return  user id, if login/pwd is valid
  33          *          false, if login/pwd is invalid
  34          * @author  Cindy Qi Li
  35          */
  36         public function Validate($login, $pwd)
  37         {
  38                 $sql = "SELECT user_id FROM ".TABLE_PREFIX."users
  39                          WHERE (login='".$login."' OR email='".$login."')
  40                            AND SHA1(CONCAT(password, '".$_SESSION[token]."'))='".$pwd."'";
  41
  42                 $rows = $this->execute($sql);
  43                 if (is_array($rows))
  44                 {
  45                         return $rows[0]['user_id'];
  46                 }
  47                 else
  48                 {
  49                         return false;
  50                 }
  51         }
  52
  53         /**
  54          * Create new user
  55          * @access  public
  56          * @param   user_group_id: user group ID (1 [admin] or 2 [user])
  57          *          login: login name
  58          *          pwd: password
  59          *          email: email
  60          *          first_name: first name
  61          *          last_name: last name
  62          * @return  user id, if successful
  63          *          false and add error into global var $msg, if unsuccessful
  64          * @author  Cindy Qi Li
  65          */
  66         public function Create($user_group_id, $login, $pwd, $email, $first_name, $last_name,
  67                                $is_author, $organization, $phone, $address, $city,
  68                                $province, $country, $postal_code, $status)
  69         {
  70                 global $addslashes;
  71
  72                 /* email check */
  73                 $login = $addslashes(strtolower(trim($login)));
  74                 $email = $addslashes(trim($email));
  75                 $first_name = $addslashes(str_replace('<', '', trim($first_name)));
  76                 $last_name = $addslashes(str_replace('<', '', trim($last_name)));
  77                 $organization = $addslashes(trim($organization));
  78                 $phone = $addslashes(trim($phone));
  79                 $address = $addslashes(trim($address));
  80                 $city = $addslashes(trim($city));
  81                 $province = $addslashes(trim($province));
  82                 $country = $addslashes(trim($country));
  83                 $postal_code = $addslashes(trim($postal_code));
  84
  85                 if ($this->isFieldsValid('new', $user_group_id, $login, $email,$first_name, $last_name,
  86                                          $is_author, $organization, $phone, $address, $city,
  87                                      $province, $country, $postal_code))
  88                 {
  89                         if ($status == "")
  90                         {
  91                                 if (defined('TR_EMAIL_CONFIRMATION') && TR_EMAIL_CONFIRMATION)
  92                                 {
  93                                         $status = TR_STATUS_UNCONFIRMED;
  94                                 } else
  95                                 {
  96                                         $status = TR_STATUS_ENABLED;
  97                                 }
  98                         }
  99
 100                         /* insert into the db */
 101                         $sql = "INSERT INTO ".TABLE_PREFIX."users
 102                                       (login,
 103                                        password,
 104                                        user_group_id,
 105                                        first_name,
 106                                        last_name,
 107                                        email,
 108                                        is_author,
 109                                        organization,
 110                                        phone,
 111                                        address,
 112                                        city,
 113                                        province,
 114                                        country,
 115                                        postal_code,
 116                                        web_service_id,
 117                                        status,
 118                                        create_date
 119                                        )
 120                                VALUES ('".$login."',
 121                                        '".$pwd."',
 122                                        ".$user_group_id.",
 123                                        '".$first_name."',
 124                                        '".$last_name."',
 125                                        '".$email."',
 126                                        ".$is_author.",
 127                                        '".$organization."',
 128                                        '".$phone."',
 129                                        '".$address."',
 130                                        '".$city."',
 131                                        '".$province."',
 132                                        '".$country."',
 133                                        '".$postal_code."',
 134                                        '".Utility::getRandomStr(32)."',
 135                                        ".$status.",
 136                                        now())";
 137
 138                         if (!$this->execute($sql))
 139                         {
 140                                 $msg->addError('DB_NOT_UPDATED');
 141                                 return false;
 142                         }
 143                         else
 144                         {
 145                                 return mysql_insert_id();
 146                         }
 147                 }
 148                 else
 149                 {
 150                         return false;
 151                 }
 152         }
 153
 154         /**
 155          * Update an existing user record
 156          * @access  public
 157          * @param   userID: user ID (1 [admin] or 2 [user])
 158          *          login: login name
 159          *          pwd: password
 160          *          email: email
 161          *          first_name: first name
 162          *          last_name: last name
 163          *          status
 164          * @return  user id, if successful
 165          *          false and add error into global var $msg, if unsuccessful
 166          * @author  Cindy Qi Li
 167          */
 168         public function Update($userID, $user_group_id, $login, $email, $first_name, $last_name,
 169                                $is_author, $organization, $phone, $address, $city,
 170                                $province, $country, $postal_code, $status)
 171         {
 172                 global $addslashes;
 173
 174                 /* email check */
 175                 $login = $addslashes(strtolower(trim($login)));
 176                 $email = $addslashes(trim($email));
 177                 $first_name = $addslashes(str_replace('<', '', trim($first_name)));
 178                 $last_name = $addslashes(str_replace('<', '', trim($last_name)));
 179                 $organization = $addslashes(trim($organization));
 180                 $phone = $addslashes(trim($phone));
 181                 $address = $addslashes(trim($address));
 182                 $city = $addslashes(trim($city));
 183                 $province = $addslashes(trim($province));
 184                 $country = $addslashes(trim($country));
 185                 $postal_code = $addslashes(trim($postal_code));
 186
 187                 if ($this->isFieldsValid('update', $user_group_id,$login, $email,$first_name, $last_name,
 188                                          $is_author, $organization, $phone, $address, $city,
 189                                      $province, $country, $postal_code))
 190                 {
 191                         /* insert into the db */
 192                         $sql = "UPDATE ".TABLE_PREFIX."users
 193                                    SET login = '".$login."',
 194                                        user_group_id = '".$user_group_id."',
 195                                        first_name = '".$first_name."',
 196                                        last_name = '".$last_name."',
 197                                        email = '".$email."',
 198                                        is_author = ".$is_author.",
 199                                        organization = '".$organization."',
 200                                        phone = '".$phone."',
 201                                        address = '".$address."',
 202                                        city = '".$city."',
 203                                        province = '".$province."',
 204                                        country = '".$country."',
 205                                        postal_code = '".$postal_code."',
 206                                        status = '".$status."'
 207                                  WHERE user_id = ".$userID;
 208
 209                         return $this->execute($sql);
 210                 }
 211         }
 212
 213         /**
 214          * Update an existing user record
 215          * @access  public
 216          * @param   userID: user ID
 217          *          fieldName: the name of the table field to update
 218          *          fieldValue: the value to update
 219          * @return  true if successful
 220          *          error message array if failed; false if update db failed
 221          * @author  Cindy Qi Li
 222          */
 223         public function UpdateField($userID, $fieldName, $fieldValue)
 224         {
 225                 global $addslashes;
 226
 227                 // check if the required fields are filled
 228                 if ($fieldValue == '') return array(_AT('TR_ERROR_EMPTY_FIELD'));
 229
 230                 if ($fieldName == 'login')
 231                 {
 232                         if (!$this->isLoginValid($fieldValue))
 233                         {
 234                                 return array(_AT('TR_ERROR_LOGIN_CHARS'));
 235                         }
 236                         else if ($this->isLoginExists($fieldValue))
 237                         {
 238                                 return array(_AT('TR_ERROR_LOGIN_EXISTS'));
 239                         }
 240                 }
 241
 242                 if ($fieldName == 'email')
 243                 {
 244                         if (!$this->isEmailValid($fieldValue))
 245                         {
 246                                 return array(_AT('TR_ERROR_EMAIL_INVALID'));
 247                         }
 248                         else if ($this->isEmailExists($fieldValue))
 249                         {
 250                                 return array(_AT('TR_ERROR_EMAIL_EXISTS'));
 251                         }
 252                 }
 253
 254                 $sql = "UPDATE ".TABLE_PREFIX."users
 255                            SET ".$fieldName."='".$addslashes($fieldValue)."'
 256                          WHERE user_id = ".$userID;
 257
 258                 return $this->execute($sql);
 259         }
 260
 261         /**
 262          * Delete user
 263          * @access  public
 264          * @param   user_id
 265          * @return  true, if successful
 266          *          false and add error into global var $msg, if unsuccessful
 267          * @author  Cindy Qi Li
 268          */
 269         public function Delete($userID)
 270         {
 271                 $sql = "DELETE FROM ".TABLE_PREFIX."users
 272                          WHERE user_id = ".$userID;
 273
 274                 return $this->execute($sql);
 275         }
 276
 277         /**
 278          * Return all users' information
 279          * @access  public
 280          * @param   none
 281          * @return  user rows
 282          * @author  Cindy Qi Li
 283          */
 284         public function getAll()
 285         {
 286                 $sql = 'SELECT * FROM '.TABLE_PREFIX.'users ORDER BY user_id';
 287                 return $this->execute($sql);
 288         }
 289
 290         /**
 291          * Return user information by given user id
 292          * @access  public
 293          * @param   user id
 294          * @return  user row
 295          * @author  Cindy Qi Li
 296          */
 297         public function getUserByID($userID)
 298         {
 299             $userID = intval($userID);
 300                 $sql = 'SELECT * FROM '.TABLE_PREFIX.'users WHERE user_id='.$userID;
 301                 if ($rows = $this->execute($sql))
 302                 {
 303                         return $rows[0];
 304                 }
 305                 else return false;
 306         }
 307
 308         /**
 309          * Return user information by given web service ID
 310          * @access  public
 311          * @param   web service ID
 312          * @return  user row
 313          * @author  Cindy Qi Li
 314          */
 315         public function getUserByWebServiceID($webServiceID)
 316         {
 317             $webServiceID = intval($webServiceID);
 318                 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE web_service_id='".$webServiceID."'";
 319                 if ($rows = $this->execute($sql))
 320                 {
 321                         return $rows[0];
 322                 }
 323                 else return false;
 324         }
 325
 326         /**
 327          * Return user information by given email
 328          * @access  public
 329          * @param   email
 330          * @return  user row : if successful
 331          *          false : if unsuccessful
 332          * @author  Cindy Qi Li
 333          */
 334         public function getUserByEmail($email)
 335         {
 336                 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE email='".$email."'";
 337
 338                 $rows = $this->execute($sql);
 339                 if (is_array($rows))
 340                 {
 341                         return $rows[0];
 342                 }
 343                 else
 344                 return false;
 345         }
 346
 347         /**
 348          * Return user information by given first, last name
 349          * @access  public
 350          * @param   $firstName : first name
 351          *          $lastName : last name
 352          * @return  user row : if successful
 353          *          false   if unsuccessful
 354          * @author  Cindy Qi Li
 355          */
 356         public function getUserByName($firstName, $lastName)
 357         {
 358                 $sql = "SELECT user_id FROM ".TABLE_PREFIX."users
 359                                 WHERE first_name='".$firstName."'
 360                                 AND last_name='".$lastName."'";
 361
 362                 $rows = $this->execute($sql);
 363                 if (is_array($rows))
 364                 {
 365                         return $rows[0];
 366                 }
 367                 else
 368                         return false;
 369         }
 370
 371         /**
 372          * Based on this->userID, return (first name, last name), if first name, last name not exists, return login name
 373          * @access  public
 374          * @param   $userID
 375          * @return  first name, last name. if not exists, return login name
 376          * @author  Cindy Qi Li
 377          */
 378         public function getUserName($userID)
 379         {
 380                 $row = $this->getUserByID($userID);
 381
 382                 if (!$row) return false;
 383
 384                 if ($row['first_name'] <> '' && $row['last_name'] <> '')
 385                 {
 386                         return $row['first_name']. ' '.$row['last_name'];
 387                 }
 388                 else if ($row['first_name'] <> '')
 389                 {
 390                         return $row['first_name'];
 391                 }
 392                 else if ($row['last_name'] <> '')
 393                 {
 394                         return $row['last_name'];
 395                 }
 396                 else
 397                 {
 398                         return $row['login'];
 399                 }
 400         }
 401
 402         /**
 403          * Return given user's status
 404          * @access  public
 405          * @param   user id
 406          * @return  user's status
 407          * @author  Cindy Qi Li
 408          */
 409         public function getStatus($userID)
 410         {
 411                 $sql = "SELECT status FROM ".TABLE_PREFIX."users WHERE user_id='".$userID."'";
 412                 $rows = $this->execute($sql);
 413
 414                 if ($rows)
 415                 return $rows[0]['status'];
 416                 else
 417                 return false;
 418         }
 419
 420         /**
 421          * Set user's status
 422          * @access  public
 423          * @param   user id
 424          *          status
 425          * @return  true    if status is set successfully
 426          *          false   if unsuccessful
 427          * @author  Cindy Qi Li
 428          */
 429         public function setStatus($userID, $status)
 430         {
 431                 $sql = "Update ".TABLE_PREFIX."users SET status='".$status."' WHERE user_id='".$userID."'";
 432                 return $this->execute($sql);
 433         }
 434
 435         /**
 436          * Update user's last login time to now()
 437          * @access  public
 438          * @param   user id
 439          * @return  true    if update successfully
 440          *          false   if update unsuccessful
 441          * @author  Cindy Qi Li
 442          */
 443         public function setLastLogin($userID)
 444         {
 445                 $sql = "Update ".TABLE_PREFIX."users SET last_login=now() WHERE user_id='".$userID."'";
 446                 return $this->execute($sql);
 447         }
 448
 449         /**
 450          * Update user's first, last name
 451          * @access  public
 452          * @param   $userID : user ID
 453          *          $firstName : first name
 454          *          $lastName : last name
 455          * @return  true    if update successfully
 456          *          false   if update unsuccessful
 457          * @author  Cindy Qi Li
 458          */
 459         public function setName($userID, $firstName, $lastName)
 460         {
 461                 $sql = "Update ".TABLE_PREFIX."users SET first_name='".$firstName."', last_name='".$lastName."' WHERE user_id='".$userID."'";
 462                 return $this->execute($sql);
 463         }
 464
 465         /**
 466          * Update user's password
 467          * @access  public
 468          * @param   $userID : user ID
 469          *          $password : password
 470          * @return  true    if update successfully
 471          *          false   if update unsuccessful
 472          * @author  Cindy Qi Li
 473          */
 474         public function setPassword($userID, $password)
 475         {
 476                 $sql = "Update ".TABLE_PREFIX."users SET password='".$password."' WHERE user_id='".$userID."'";
 477                 return $this->execute($sql);
 478         }
 479
 480         /**
 481          * Update user's email
 482          * @access  public
 483          * @param   $userID : user ID
 484          *          $email : email
 485          * @return  true    if update successfully
 486          *          false   if update unsuccessful
 487          * @author  Cindy Qi Li
 488          */
 489         public function setEmail($userID, $email)
 490         {
 491                 $sql = "Update ".TABLE_PREFIX."users SET email='".$email."' WHERE user_id='".$userID."'";
 492                 return $this->execute($sql);
 493         }
 494
 495         /**
 496          * Validates fields preparing for insert and update
 497          * @access  private
 498          * @param   $validate_type : new/update. When validating for update, don't check if the login, email, name are unique
 499          *          $user_group_id : user ID
 500          *          $login
 501          *          $email
 502          *          $first_name
 503          *          $last_name
 504          * @return  true    if update successfully
 505          *          false   if update unsuccessful
 506          * @author  Cindy Qi Li
 507          */
 508         private function isFieldsValid($validate_type, $user_group_id, $login, $email, $first_name, $last_name,
 509                                        $is_author, $organization, $phone, $address, $city,
 510                                        $province, $country, $postal_code)
 511         {
 512                 global $msg;
 513
 514                 $missing_fields = array();
 515                 /* login name check */
 516                 if ($login == '')
 517                 {
 518                         $missing_fields[] = _AT('login_name');
 519                 }
 520                 else
 521                 {
 522                         /* check for special characters */
 523                         if (!$this->isLoginValid($login))
 524                         {
 525                                 $msg->addError('LOGIN_CHARS');
 526                         }
 527                         else if ($validate_type == 'new' && $this->isLoginExists($login))
 528                         {
 529                                 $msg->addError('LOGIN_EXISTS');
 530                         }
 531                 }
 532
 533                 if ($user_group_id == '' || $user_group_id <= 0)
 534                 {
 535                         $missing_fields[] = _AT('user_group');
 536                 }
 537                 if ($email == '')
 538                 {
 539                         $missing_fields[] = _AT('email');
 540                 }
 541                 else if (!$this->isEmailValid($email))
 542                 {
 543                         $msg->addError('EMAIL_INVALID');
 544                 }
 545
 546                 if ($validate_type == 'new' && $this->isEmailExists($email))
 547                 {
 548                         $msg->addError('EMAIL_EXISTS');
 549                 }
 550
 551                 if (!$first_name) {
 552                         $missing_fields[] = _AT('first_name');
 553                 }
 554
 555                 if (!$last_name) {
 556                         $missing_fields[] = _AT('last_name');
 557                 }
 558
 559                 // when user requests to be an author, author information is necessary
 560                 if ($is_author <> 0 && $is_author <> 1)
 561                 {
 562                         $msg->addError('INVALID_CHECKBOX_STATUS');
 563                 }
 564
 565                 if ($is_author == 1)
 566                 {
 567                         if (!$organization) $missing_fields[] = _AT('organization');
 568                         if (!$phone) $missing_fields[] = _AT('phone');
 569                         if (!$address) $missing_fields[] = _AT('address');
 570                         if (!$city) $missing_fields[] = _AT('city');
 571                         if (!$province) $missing_fields[] = _AT('province');
 572                         if (!$country) $missing_fields[] = _AT('country');
 573                         if (!$postal_code) $missing_fields[] = _AT('postal_code');
 574                 }
 575
 576                 if ($missing_fields)
 577                 {
 578                         $missing_fields = implode(', ', $missing_fields);
 579                         $msg->addError(array('EMPTY_FIELDS', $missing_fields));
 580                 }
 581
 582                 if (!$msg->containsErrors())
 583                         return true;
 584                 else
 585                         return false;
 586         }
 587
 588         /**
 589          * Validate if the login name is valid
 590          * @access  private
 591          * @param   $login
 592          * @return  true    if valid
 593          *          false   if not valid
 594          * @author  Cindy Qi Li
 595          */
 596         private function isLoginValid($login)
 597         {
 598                 return preg_match("/^[a-zA-Z0-9_.-]([a-zA-Z0-9_.-])*$/i", $login);
 599         }
 600
 601         /**
 602          * Validate if the login name already exists
 603          * @access  private
 604          * @param   $login
 605          * @return  true    if login already exists
 606          *          false   if login not exists
 607          * @author  Cindy Qi Li
 608          */
 609         private function isLoginExists($login)
 610         {
 611                 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE login='".$login."'";
 612
 613                 return is_array($this->execute($sql));
 614         }
 615
 616         /**
 617          * Validate if the email is valid
 618          * @access  private
 619          * @param   $email
 620          * @return  true    if valid
 621          *          false   if not valid
 622          * @author  Cindy Qi Li
 623          */
 624         private function isEmailValid($email)
 625         {
 626                 return preg_match("/^[a-z0-9\._-]+@+[a-z0-9\._-]+\.+[a-z]{2,6}$/i", $email);
 627         }
 628
 629         /**
 630          * Validate if the email already exists
 631          * @access  private
 632          * @param   $login
 633          * @return  true    if email already exists
 634          *          false   if email not exists
 635          * @author  Cindy Qi Li
 636          */
 637         private function isEmailExists($email)
 638         {
 639                 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE email='".$email."'";
 640
 641                 return is_array($this->execute($sql));
 642         }
 643
 644 }
 645 ?>