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