2 /************************************************************************/
4 /************************************************************************/
5 /* Copyright (c) 2010 */
6 /* Inclusive Design Institute */
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 /************************************************************************/
14 * DAO for "users" table
20 if (!defined('TR_INCLUDE_PATH')) exit;
22 require_once(TR_INCLUDE_PATH. 'classes/DAO/DAO.class.php');
23 require_once(TR_INCLUDE_PATH. 'classes/Utility.class.php');
25 class UsersDAO extends DAO {
28 * Validate if the given login/pwd is valid
30 * @param login: login id or email
32 * @return user id, if login/pwd is valid
33 * false, if login/pwd is invalid
36 public function Validate($login, $pwd)
38 $sql = "SELECT user_id FROM ".TABLE_PREFIX."users
39 WHERE (login='".$login."' OR email='".$login."')
40 AND SHA1(CONCAT(password, '".$_SESSION[token]."'))='".$pwd."'";
42 $rows = $this->execute($sql);
45 return $rows[0]['user_id'];
56 * @param user_group_id: user group ID (1 [admin] or 2 [user])
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
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)
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));
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))
91 if (defined('TR_EMAIL_CONFIRMATION') && TR_EMAIL_CONFIRMATION)
93 $status = TR_STATUS_UNCONFIRMED;
96 $status = TR_STATUS_ENABLED;
100 /* insert into the db */
101 $sql = "INSERT INTO ".TABLE_PREFIX."users
120 VALUES ('".$login."',
134 '".Utility::getRandomStr(32)."',
138 if (!$this->execute($sql))
140 $msg->addError('DB_NOT_UPDATED');
145 return mysql_insert_id();
155 * Update an existing user record
157 * @param userID: user ID (1 [admin] or 2 [user])
161 * first_name: first name
162 * last_name: last name
164 * @return user id, if successful
165 * false and add error into global var $msg, if unsuccessful
166 * @author Cindy Qi Li
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)
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));
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))
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."',
203 province = '".$province."',
204 country = '".$country."',
205 postal_code = '".$postal_code."',
206 status = '".$status."'
207 WHERE user_id = ".$userID;
209 return $this->execute($sql);
214 * Update an existing user record
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
223 public function UpdateField($userID, $fieldName, $fieldValue)
227 // check if the required fields are filled
228 if ($fieldValue == '') return array(_AT('TR_ERROR_EMPTY_FIELD'));
230 if ($fieldName == 'login')
232 if (!$this->isLoginValid($fieldValue))
234 return array(_AT('TR_ERROR_LOGIN_CHARS'));
236 else if ($this->isLoginExists($fieldValue))
238 return array(_AT('TR_ERROR_LOGIN_EXISTS'));
242 if ($fieldName == 'email')
244 if (!$this->isEmailValid($fieldValue))
246 return array(_AT('TR_ERROR_EMAIL_INVALID'));
248 else if ($this->isEmailExists($fieldValue))
250 return array(_AT('TR_ERROR_EMAIL_EXISTS'));
254 $sql = "UPDATE ".TABLE_PREFIX."users
255 SET ".$addslashes($fieldName)."='".$addslashes($fieldValue)."'
256 WHERE user_id = ".intval($userID);
258 return $this->execute($sql);
265 * @return true, if successful
266 * false and add error into global var $msg, if unsuccessful
267 * @author Cindy Qi Li
269 public function Delete($userID)
271 $sql = "DELETE FROM ".TABLE_PREFIX."users
272 WHERE user_id = ".$userID;
274 return $this->execute($sql);
278 * Return all users' information
282 * @author Cindy Qi Li
284 public function getAll()
286 $sql = 'SELECT * FROM '.TABLE_PREFIX.'users ORDER BY user_id';
287 return $this->execute($sql);
291 * Return user information by given user id
295 * @author Cindy Qi Li
297 public function getUserByID($userID)
299 $userID = intval($userID);
300 $sql = 'SELECT * FROM '.TABLE_PREFIX.'users WHERE user_id='.$userID;
301 if ($rows = $this->execute($sql))
309 * Return user information by given web service ID
311 * @param web service ID
313 * @author Cindy Qi Li
315 public function getUserByWebServiceID($webServiceID)
318 $webServiceID = $addslashes($webServiceID);
319 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE web_service_id='".$webServiceID."'";
320 if ($rows = $this->execute($sql))
328 * Return user information by given email
331 * @return user row : if successful
332 * false : if unsuccessful
333 * @author Cindy Qi Li
335 public function getUserByEmail($email)
337 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE email='".$email."'";
339 $rows = $this->execute($sql);
349 * Return user information by given first, last name
351 * @param $firstName : first name
352 * $lastName : last name
353 * @return user row : if successful
354 * false if unsuccessful
355 * @author Cindy Qi Li
357 public function getUserByName($firstName, $lastName)
359 $sql = "SELECT user_id FROM ".TABLE_PREFIX."users
360 WHERE first_name='".$firstName."'
361 AND last_name='".$lastName."'";
363 $rows = $this->execute($sql);
373 * Based on this->userID, return (first name, last name), if first name, last name not exists, return login name
376 * @return first name, last name. if not exists, return login name
377 * @author Cindy Qi Li
379 public function getUserName($userID)
381 $row = $this->getUserByID($userID);
383 if (!$row) return false;
385 if ($row['first_name'] <> '' && $row['last_name'] <> '')
387 return $row['first_name']. ' '.$row['last_name'];
389 else if ($row['first_name'] <> '')
391 return $row['first_name'];
393 else if ($row['last_name'] <> '')
395 return $row['last_name'];
399 return $row['login'];
404 * Return given user's status
407 * @return user's status
408 * @author Cindy Qi Li
410 public function getStatus($userID)
412 $sql = "SELECT status FROM ".TABLE_PREFIX."users WHERE user_id='".$userID."'";
413 $rows = $this->execute($sql);
416 return $rows[0]['status'];
426 * @return true if status is set successfully
427 * false if unsuccessful
428 * @author Cindy Qi Li
430 public function setStatus($userID, $status)
432 $sql = "Update ".TABLE_PREFIX."users SET status='".$status."' WHERE user_id='".$userID."'";
433 return $this->execute($sql);
437 * Update user's last login time to now()
440 * @return true if update successfully
441 * false if update unsuccessful
442 * @author Cindy Qi Li
444 public function setLastLogin($userID)
446 $sql = "Update ".TABLE_PREFIX."users SET last_login=now() WHERE user_id='".$userID."'";
447 return $this->execute($sql);
451 * Update user's first, last name
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
460 public function setName($userID, $firstName, $lastName)
462 $sql = "Update ".TABLE_PREFIX."users SET first_name='".$firstName."', last_name='".$lastName."' WHERE user_id='".$userID."'";
463 return $this->execute($sql);
467 * Update user's password
469 * @param $userID : user ID
470 * $password : password
471 * @return true if update successfully
472 * false if update unsuccessful
473 * @author Cindy Qi Li
475 public function setPassword($userID, $password)
477 $sql = "Update ".TABLE_PREFIX."users SET password='".$password."' WHERE user_id='".$userID."'";
478 return $this->execute($sql);
482 * Update user's email
484 * @param $userID : user ID
486 * @return true if update successfully
487 * false if update unsuccessful
488 * @author Cindy Qi Li
490 public function setEmail($userID, $email)
492 $sql = "Update ".TABLE_PREFIX."users SET email='".$email."' WHERE user_id='".$userID."'";
493 return $this->execute($sql);
497 * Validates fields preparing for insert and update
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
505 * @return true if update successfully
506 * false if update unsuccessful
507 * @author Cindy Qi Li
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)
515 $missing_fields = array();
516 /* login name check */
519 $missing_fields[] = _AT('login_name');
523 /* check for special characters */
524 if (!$this->isLoginValid($login))
526 $msg->addError('LOGIN_CHARS');
528 else if ($validate_type == 'new' && $this->isLoginExists($login))
530 $msg->addError('LOGIN_EXISTS');
534 if ($user_group_id == '' || $user_group_id <= 0)
536 $missing_fields[] = _AT('user_group');
540 $missing_fields[] = _AT('email');
542 else if (!$this->isEmailValid($email))
544 $msg->addError('EMAIL_INVALID');
547 if ($validate_type == 'new' && $this->isEmailExists($email))
549 $msg->addError('EMAIL_EXISTS');
553 $missing_fields[] = _AT('first_name');
557 $missing_fields[] = _AT('last_name');
560 // when user requests to be an author, author information is necessary
561 if ($is_author <> 0 && $is_author <> 1)
563 $msg->addError('INVALID_CHECKBOX_STATUS');
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');
579 $missing_fields = implode(', ', $missing_fields);
580 $msg->addError(array('EMPTY_FIELDS', $missing_fields));
583 if (!$msg->containsErrors())
590 * Validate if the login name is valid
593 * @return true if valid
595 * @author Cindy Qi Li
597 private function isLoginValid($login)
599 return preg_match("/^[a-zA-Z0-9_.-]([a-zA-Z0-9_.-])*$/i", $login);
603 * Validate if the login name already exists
606 * @return true if login already exists
607 * false if login not exists
608 * @author Cindy Qi Li
610 private function isLoginExists($login)
612 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE login='".$login."'";
614 return is_array($this->execute($sql));
618 * Validate if the email is valid
621 * @return true if valid
623 * @author Cindy Qi Li
625 private function isEmailValid($email)
627 return preg_match("/^[a-z0-9\._-]+@+[a-z0-9\._-]+\.+[a-z]{2,6}$/i", $email);
631 * Validate if the email already exists
634 * @return true if email already exists
635 * false if email not exists
636 * @author Cindy Qi Li
638 private function isEmailExists($email)
640 $sql = "SELECT * FROM ".TABLE_PREFIX."users WHERE email='".$email."'";
642 return is_array($this->execute($sql));