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                 $sql = 'SELECT * FROM '.TABLE_PREFIX.'users WHERE user_id='.$userID;
 300                 if ($rows = $this->execute($sql))
 301                 {
 302                         return $rows[0];
 303                 }
 304                 else return false;
 305         }
 306
 307         /**
 308          * Return user information by given web service ID
 309          * @access  public
 310          * @param   web service ID
 311          * @return  user row
 312          * @author  Cindy Qi Li
 313          */
 314         public function getUserByWebServiceID($webServiceID)
 315         {
 316                 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE web_service_id='".$webServiceID."'";
 317                 if ($rows = $this->execute($sql))
 318                 {
 319                         return $rows[0];
 320                 }
 321                 else return false;
 322         }
 323
 324         /**
 325          * Return user information by given email
 326          * @access  public
 327          * @param   email
 328          * @return  user row : if successful
 329          *          false : if unsuccessful
 330          * @author  Cindy Qi Li
 331          */
 332         public function getUserByEmail($email)
 333         {
 334                 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE email='".$email."'";
 335
 336                 $rows = $this->execute($sql);
 337                 if (is_array($rows))
 338                 {
 339                         return $rows[0];
 340                 }
 341                 else
 342                 return false;
 343         }
 344
 345         /**
 346          * Return user information by given first, last name
 347          * @access  public
 348          * @param   $firstName : first name
 349          *          $lastName : last name
 350          * @return  user row : if successful
 351          *          false   if unsuccessful
 352          * @author  Cindy Qi Li
 353          */
 354         public function getUserByName($firstName, $lastName)
 355         {
 356                 $sql = "SELECT user_id FROM ".TABLE_PREFIX."users
 357                                 WHERE first_name='".$firstName."'
 358                                 AND last_name='".$lastName."'";
 359
 360                 $rows = $this->execute($sql);
 361                 if (is_array($rows))
 362                 {
 363                         return $rows[0];
 364                 }
 365                 else
 366                         return false;
 367         }
 368
 369         /**
 370          * Based on this->userID, return (first name, last name), if first name, last name not exists, return login name
 371          * @access  public
 372          * @param   $userID
 373          * @return  first name, last name. if not exists, return login name
 374          * @author  Cindy Qi Li
 375          */
 376         public function getUserName($userID)
 377         {
 378                 $row = $this->getUserByID($userID);
 379
 380                 if (!$row) return false;
 381
 382                 if ($row['first_name'] <> '' && $row['last_name'] <> '')
 383                 {
 384                         return $row['first_name']. ' '.$row['last_name'];
 385                 }
 386                 else if ($row['first_name'] <> '')
 387                 {
 388                         return $row['first_name'];
 389                 }
 390                 else if ($row['last_name'] <> '')
 391                 {
 392                         return $row['last_name'];
 393                 }
 394                 else
 395                 {
 396                         return $row['login'];
 397                 }
 398         }
 399
 400         /**
 401          * Return given user's status
 402          * @access  public
 403          * @param   user id
 404          * @return  user's status
 405          * @author  Cindy Qi Li
 406          */
 407         public function getStatus($userID)
 408         {
 409                 $sql = "SELECT status FROM ".TABLE_PREFIX."users WHERE user_id='".$userID."'";
 410                 $rows = $this->execute($sql);
 411
 412                 if ($rows)
 413                 return $rows[0]['status'];
 414                 else
 415                 return false;
 416         }
 417
 418         /**
 419          * Set user's status
 420          * @access  public
 421          * @param   user id
 422          *          status
 423          * @return  true    if status is set successfully
 424          *          false   if unsuccessful
 425          * @author  Cindy Qi Li
 426          */
 427         public function setStatus($userID, $status)
 428         {
 429                 $sql = "Update ".TABLE_PREFIX."users SET status='".$status."' WHERE user_id='".$userID."'";
 430                 return $this->execute($sql);
 431         }
 432
 433         /**
 434          * Update user's last login time to now()
 435          * @access  public
 436          * @param   user id
 437          * @return  true    if update successfully
 438          *          false   if update unsuccessful
 439          * @author  Cindy Qi Li
 440          */
 441         public function setLastLogin($userID)
 442         {
 443                 $sql = "Update ".TABLE_PREFIX."users SET last_login=now() WHERE user_id='".$userID."'";
 444                 return $this->execute($sql);
 445         }
 446
 447         /**
 448          * Update user's first, last name
 449          * @access  public
 450          * @param   $userID : user ID
 451          *          $firstName : first name
 452          *          $lastName : last name
 453          * @return  true    if update successfully
 454          *          false   if update unsuccessful
 455          * @author  Cindy Qi Li
 456          */
 457         public function setName($userID, $firstName, $lastName)
 458         {
 459                 $sql = "Update ".TABLE_PREFIX."users SET first_name='".$firstName."', last_name='".$lastName."' WHERE user_id='".$userID."'";
 460                 return $this->execute($sql);
 461         }
 462
 463         /**
 464          * Update user's password
 465          * @access  public
 466          * @param   $userID : user ID
 467          *          $password : password
 468          * @return  true    if update successfully
 469          *          false   if update unsuccessful
 470          * @author  Cindy Qi Li
 471          */
 472         public function setPassword($userID, $password)
 473         {
 474                 $sql = "Update ".TABLE_PREFIX."users SET password='".$password."' WHERE user_id='".$userID."'";
 475                 return $this->execute($sql);
 476         }
 477
 478         /**
 479          * Update user's email
 480          * @access  public
 481          * @param   $userID : user ID
 482          *          $email : email
 483          * @return  true    if update successfully
 484          *          false   if update unsuccessful
 485          * @author  Cindy Qi Li
 486          */
 487         public function setEmail($userID, $email)
 488         {
 489                 $sql = "Update ".TABLE_PREFIX."users SET email='".$email."' WHERE user_id='".$userID."'";
 490                 return $this->execute($sql);
 491         }
 492
 493         /**
 494          * Validates fields preparing for insert and update
 495          * @access  private
 496          * @param   $validate_type : new/update. When validating for update, don't check if the login, email, name are unique
 497          *          $user_group_id : user ID
 498          *          $login
 499          *          $email
 500          *          $first_name
 501          *          $last_name
 502          * @return  true    if update successfully
 503          *          false   if update unsuccessful
 504          * @author  Cindy Qi Li
 505          */
 506         private function isFieldsValid($validate_type, $user_group_id, $login, $email, $first_name, $last_name,
 507                                        $is_author, $organization, $phone, $address, $city,
 508                                        $province, $country, $postal_code)
 509         {
 510                 global $msg;
 511
 512                 $missing_fields = array();
 513                 /* login name check */
 514                 if ($login == '')
 515                 {
 516                         $missing_fields[] = _AT('login_name');
 517                 }
 518                 else
 519                 {
 520                         /* check for special characters */
 521                         if (!$this->isLoginValid($login))
 522                         {
 523                                 $msg->addError('LOGIN_CHARS');
 524                         }
 525                         else if ($validate_type == 'new' && $this->isLoginExists($login))
 526                         {
 527                                 $msg->addError('LOGIN_EXISTS');
 528                         }
 529                 }
 530
 531                 if ($user_group_id == '' || $user_group_id <= 0)
 532                 {
 533                         $missing_fields[] = _AT('user_group');
 534                 }
 535                 if ($email == '')
 536                 {
 537                         $missing_fields[] = _AT('email');
 538                 }
 539                 else if (!$this->isEmailValid($email))
 540                 {
 541                         $msg->addError('EMAIL_INVALID');
 542                 }
 543
 544                 if ($validate_type == 'new' && $this->isEmailExists($email))
 545                 {
 546                         $msg->addError('EMAIL_EXISTS');
 547                 }
 548
 549                 if (!$first_name) {
 550                         $missing_fields[] = _AT('first_name');
 551                 }
 552
 553                 if (!$last_name) {
 554                         $missing_fields[] = _AT('last_name');
 555                 }
 556
 557                 // when user requests to be an author, author information is necessary
 558                 if ($is_author <> 0 && $is_author <> 1)
 559                 {
 560                         $msg->addError('INVALID_CHECKBOX_STATUS');
 561                 }
 562
 563                 if ($is_author == 1)
 564                 {
 565                         if (!$organization) $missing_fields[] = _AT('organization');
 566                         if (!$phone) $missing_fields[] = _AT('phone');
 567                         if (!$address) $missing_fields[] = _AT('address');
 568                         if (!$city) $missing_fields[] = _AT('city');
 569                         if (!$province) $missing_fields[] = _AT('province');
 570                         if (!$country) $missing_fields[] = _AT('country');
 571                         if (!$postal_code) $missing_fields[] = _AT('postal_code');
 572                 }
 573
 574                 if ($missing_fields)
 575                 {
 576                         $missing_fields = implode(', ', $missing_fields);
 577                         $msg->addError(array('EMPTY_FIELDS', $missing_fields));
 578                 }
 579
 580                 if (!$msg->containsErrors())
 581                         return true;
 582                 else
 583                         return false;
 584         }
 585
 586         /**
 587          * Validate if the login name is valid
 588          * @access  private
 589          * @param   $login
 590          * @return  true    if valid
 591          *          false   if not valid
 592          * @author  Cindy Qi Li
 593          */
 594         private function isLoginValid($login)
 595         {
 596                 return preg_match("/^[a-zA-Z0-9_.-]([a-zA-Z0-9_.-])*$/i", $login);
 597         }
 598
 599         /**
 600          * Validate if the login name already exists
 601          * @access  private
 602          * @param   $login
 603          * @return  true    if login already exists
 604          *          false   if login not exists
 605          * @author  Cindy Qi Li
 606          */
 607         private function isLoginExists($login)
 608         {
 609                 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE login='".$login."'";
 610
 611                 return is_array($this->execute($sql));
 612         }
 613
 614         /**
 615          * Validate if the email is valid
 616          * @access  private
 617          * @param   $email
 618          * @return  true    if valid
 619          *          false   if not valid
 620          * @author  Cindy Qi Li
 621          */
 622         private function isEmailValid($email)
 623         {
 624                 return preg_match("/^[a-z0-9\._-]+@+[a-z0-9\._-]+\.+[a-z]{2,6}$/i", $email);
 625         }
 626
 627         /**
 628          * Validate if the email already exists
 629          * @access  private
 630          * @param   $login
 631          * @return  true    if email already exists
 632          *          false   if email not exists
 633          * @author  Cindy Qi Li
 634          */
 635         private function isEmailExists($email)
 636         {
 637                 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE email='".$email."'";
 638
 639                 return is_array($this->execute($sql));
 640         }
 641
 642 }
 643 ?>