1<?php 2/** 3 * webtrees: online genealogy 4 * Copyright (C) 2018 webtrees development team 5 * This program is free software: you can redistribute it and/or modify 6 * it under the terms of the GNU General Public License as published by 7 * the Free Software Foundation, either version 3 of the License, or 8 * (at your option) any later version. 9 * This program is distributed in the hope that it will be useful, 10 * but WITHOUT ANY WARRANTY; without even the implied warranty of 11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 * GNU General Public License for more details. 13 * You should have received a copy of the GNU General Public License 14 * along with this program. If not, see <http://www.gnu.org/licenses/>. 15 */ 16declare(strict_types=1); 17 18namespace Fisharebest\Webtrees; 19 20use stdclass; 21 22/** 23 * Provide an interface to the wt_user table. 24 */ 25class User { 26 /** @var int The primary key of this user. */ 27 private $user_id; 28 29 /** @var string The login name of this user. */ 30 private $user_name; 31 32 /** @var string The real (display) name of this user. */ 33 private $real_name; 34 35 /** @var string The email address of this user. */ 36 private $email; 37 38 /** @var string[] Cached copy of the wt_user_setting table. */ 39 private $preferences = []; 40 41 /** @var User[] Only fetch users from the database once. */ 42 private static $cache = []; 43 44 /** 45 * Create a new user object from a row in the database. 46 * 47 * @param stdclass $user A row from the wt_user table 48 */ 49 public function __construct(stdClass $user) { 50 $this->user_id = (int) $user->user_id; 51 $this->user_name = $user->user_name; 52 $this->real_name = $user->real_name; 53 $this->email = $user->email; 54 } 55 56 /** 57 * Create a new user. 58 * 59 * The calling code needs to check for duplicates identifiers before calling 60 * this function. 61 * 62 * @param string $user_name 63 * @param string $real_name 64 * @param string $email 65 * @param string $password 66 * 67 * @return User 68 */ 69 public static function create($user_name, $real_name, $email, $password) { 70 Database::prepare( 71 "INSERT INTO `##user` (user_name, real_name, email, password) VALUES (:user_name, :real_name, :email, :password)" 72 )->execute([ 73 'user_name' => $user_name, 74 'real_name' => $real_name, 75 'email' => $email, 76 'password' => password_hash($password, PASSWORD_DEFAULT), 77 ]); 78 79 // Set default blocks for this user 80 $user = self::findByIdentifier($user_name); 81 Database::prepare( 82 "INSERT INTO `##block` (`user_id`, `location`, `block_order`, `module_name`)" . 83 " SELECT :user_id , `location`, `block_order`, `module_name` FROM `##block` WHERE `user_id` = -1" 84 )->execute(['user_id' => $user->getUserId()]); 85 86 return $user; 87 } 88 89 /** 90 * Delete a user 91 */ 92 public function delete() { 93 // Don't delete the logs. 94 Database::prepare("UPDATE `##log` SET user_id=NULL WHERE user_id =?")->execute([$this->user_id]); 95 // Take over the user’s pending changes. (What else could we do with them?) 96 Database::prepare("DELETE FROM `##change` WHERE user_id=? AND status='rejected'")->execute([$this->user_id]); 97 Database::prepare("UPDATE `##change` SET user_id=? WHERE user_id=?")->execute([Auth::id(), $this->user_id]); 98 Database::prepare("DELETE `##block_setting` FROM `##block_setting` JOIN `##block` USING (block_id) WHERE user_id=?")->execute([$this->user_id]); 99 Database::prepare("DELETE FROM `##block` WHERE user_id=?")->execute([$this->user_id]); 100 Database::prepare("DELETE FROM `##user_gedcom_setting` WHERE user_id=?")->execute([$this->user_id]); 101 Database::prepare("DELETE FROM `##gedcom_setting` WHERE setting_value=? AND setting_name IN ('CONTACT_USER_ID', 'WEBMASTER_USER_ID')")->execute([(string) $this->user_id]); 102 Database::prepare("DELETE FROM `##user_setting` WHERE user_id=?")->execute([$this->user_id]); 103 Database::prepare("DELETE FROM `##message` WHERE user_id=?")->execute([$this->user_id]); 104 Database::prepare("DELETE FROM `##user` WHERE user_id=?")->execute([$this->user_id]); 105 } 106 107 /** 108 * Find the user with a specified user_id. 109 * 110 * @param int|null $user_id 111 * 112 * @return User|null 113 */ 114 public static function find($user_id) { 115 if (!array_key_exists($user_id, self::$cache)) { 116 $row = Database::prepare( 117 "SELECT SQL_CACHE user_id, user_name, real_name, email FROM `##user` WHERE user_id = ?" 118 )->execute([$user_id])->fetchOneRow(); 119 if ($row) { 120 self::$cache[$user_id] = new self($row); 121 } else { 122 self::$cache[$user_id] = null; 123 } 124 } 125 126 return self::$cache[$user_id]; 127 } 128 129 /** 130 * Find the user with a specified email address. 131 * 132 * @param string $email 133 * 134 * @return User|null 135 */ 136 public static function findByEmail($email) { 137 $user_id = Database::prepare( 138 "SELECT SQL_CACHE user_id FROM `##user` WHERE email = :email" 139 )->execute([ 140 'email' => $email, 141 ])->fetchOne(); 142 143 return self::find($user_id); 144 } 145 146 /** 147 * Find the user with a specified user_name or email address. 148 * 149 * @param string $identifier 150 * 151 * @return User|null 152 */ 153 public static function findByIdentifier($identifier) { 154 $user_id = Database::prepare( 155 "SELECT SQL_CACHE user_id FROM `##user` WHERE ? IN (user_name, email)" 156 )->execute([$identifier])->fetchOne(); 157 158 return self::find($user_id); 159 } 160 161 /** 162 * Find the user with a specified genealogy record. 163 * 164 * @param Individual $individual 165 * 166 * @return User|null 167 */ 168 public static function findByIndividual(Individual $individual) { 169 $user_id = Database::prepare( 170 "SELECT SQL_CACHE user_id" . 171 " FROM `##user_gedcom_setting`" . 172 " WHERE gedcom_id = :tree_id AND setting_name = 'gedcomid' AND setting_value = :xref" 173 )->execute([ 174 'tree_id' => $individual->getTree()->getTreeId(), 175 'xref' => $individual->getXref(), 176 ])->fetchOne(); 177 178 return self::find($user_id); 179 } 180 181 /** 182 * Find the user with a specified user_name. 183 * 184 * @param string $user_name 185 * 186 * @return User|null 187 */ 188 public static function findByUserName($user_name) { 189 $user_id = Database::prepare( 190 "SELECT SQL_CACHE user_id FROM `##user` WHERE user_name = :user_name" 191 )->execute([ 192 'user_name' => $user_name, 193 ])->fetchOne(); 194 195 return self::find($user_id); 196 } 197 198 /** 199 * Find the latest user to register. 200 * 201 * @return User|null 202 */ 203 public static function findLatestToRegister() { 204 $user_id = Database::prepare( 205 "SELECT SQL_CACHE u.user_id" . 206 " FROM `##user` u" . 207 " LEFT JOIN `##user_setting` us ON (u.user_id=us.user_id AND us.setting_name='reg_timestamp') " . 208 " ORDER BY us.setting_value DESC LIMIT 1" 209 )->execute()->fetchOne(); 210 211 return self::find($user_id); 212 } 213 214 /** 215 * Get a list of all users. 216 * 217 * @return User[] 218 */ 219 public static function all() { 220 $rows = Database::prepare( 221 "SELECT SQL_CACHE user_id, user_name, real_name, email" . 222 " FROM `##user`" . 223 " WHERE user_id > 0" . 224 " ORDER BY real_name" 225 )->fetchAll(); 226 227 return array_map(function ($row) { 228 return new static($row); 229 }, $rows); 230 } 231 232 /** 233 * Get a list of all administrators. 234 * 235 * @return User[] 236 */ 237 public static function administrators() { 238 $rows = Database::prepare( 239 "SELECT SQL_CACHE user_id, user_name, real_name, email" . 240 " FROM `##user`" . 241 " JOIN `##user_setting` USING (user_id)" . 242 " WHERE user_id > 0 AND setting_name = 'canadmin' AND setting_value = '1'" . 243 " ORDER BY real_name" 244 )->fetchAll(); 245 246 return array_map(function ($row) { 247 return new static($row); 248 }, $rows); 249 } 250 251 /** Validate a supplied password 252 * @param string $password 253 * 254 * @return bool 255 */ 256 public function checkPassword($password) { 257 $password_hash = Database::prepare( 258 "SELECT password FROM `##user` WHERE user_id = ?" 259 )->execute([$this->user_id])->fetchOne(); 260 261 if (password_verify($password, $password_hash)) { 262 if (password_needs_rehash($password_hash, PASSWORD_DEFAULT)) { 263 $this->setPassword($password); 264 } 265 266 return true; 267 } else { 268 return false; 269 } 270 } 271 272 /** 273 * Get a list of all managers. 274 * 275 * @return User[] 276 */ 277 public static function managers() { 278 $rows = Database::prepare( 279 "SELECT SQL_CACHE user_id, user_name, real_name, email" . 280 " FROM `##user` JOIN `##user_gedcom_setting` USING (user_id)" . 281 " WHERE setting_name = 'canedit' AND setting_value='admin'" . 282 " GROUP BY user_id, real_name" . 283 " ORDER BY real_name" 284 )->fetchAll(); 285 286 return array_map(function ($row) { 287 return new static($row); 288 }, $rows); 289 } 290 291 /** 292 * Get a list of all moderators. 293 * 294 * @return User[] 295 */ 296 public static function moderators() { 297 $rows = Database::prepare( 298 "SELECT SQL_CACHE user_id, user_name, real_name, email" . 299 " FROM `##user` JOIN `##user_gedcom_setting` USING (user_id)" . 300 " WHERE setting_name = 'canedit' AND setting_value='accept'" . 301 " GROUP BY user_id, real_name" . 302 " ORDER BY real_name" 303 )->fetchAll(); 304 305 return array_map(function ($row) { 306 return new static($row); 307 }, $rows); 308 } 309 310 /** 311 * Get a list of all verified users. 312 * 313 * @return User[] 314 */ 315 public static function unapproved() { 316 $rows = Database::prepare( 317 "SELECT SQL_CACHE user_id, user_name, real_name, email" . 318 " FROM `##user` JOIN `##user_setting` USING (user_id)" . 319 " WHERE setting_name = 'verified_by_admin' AND setting_value = '0'" . 320 " ORDER BY real_name" 321 )->fetchAll(); 322 323 return array_map(function ($row) { 324 return new static($row); 325 }, $rows); 326 } 327 328 /** 329 * Get a list of all verified users. 330 * 331 * @return User[] 332 */ 333 public static function unverified() { 334 $rows = Database::prepare( 335 "SELECT SQL_CACHE user_id, user_name, real_name, email" . 336 " FROM `##user` JOIN `##user_setting` USING (user_id)" . 337 " WHERE setting_name = 'verified' AND setting_value = '0'" . 338 " ORDER BY real_name" 339 )->fetchAll(); 340 341 return array_map(function ($row) { 342 return new static($row); 343 }, $rows); 344 } 345 346 /** 347 * Get a list of all users who are currently logged in. 348 * 349 * @return User[] 350 */ 351 public static function allLoggedIn() { 352 $rows = Database::prepare( 353 "SELECT SQL_NO_CACHE DISTINCT user_id, user_name, real_name, email" . 354 " FROM `##user`" . 355 " JOIN `##session` USING (user_id)" 356 )->fetchAll(); 357 358 return array_map(function ($row) { 359 return new static($row); 360 }, $rows); 361 } 362 363 /** 364 * Get the numeric ID for this user. 365 * 366 * @return int 367 */ 368 public function getUserId(): int { 369 return $this->user_id; 370 } 371 372 /** 373 * Get the login name for this user. 374 * 375 * @return string 376 */ 377 public function getUserName() { 378 return $this->user_name; 379 } 380 381 /** 382 * Set the login name for this user. 383 * 384 * @param string $user_name 385 * 386 * @return $this 387 */ 388 public function setUserName($user_name) { 389 if ($this->user_name !== $user_name) { 390 $this->user_name = $user_name; 391 Database::prepare( 392 "UPDATE `##user` SET user_name = ? WHERE user_id = ?" 393 )->execute([$user_name, $this->user_id]); 394 } 395 396 return $this; 397 } 398 399 /** 400 * Get the real name of this user. 401 * 402 * @return string 403 */ 404 public function getRealName() { 405 return $this->real_name; 406 } 407 408 /** 409 * Set the real name of this user. 410 * 411 * @param string $real_name 412 * 413 * @return User 414 */ 415 public function setRealName($real_name) { 416 if ($this->real_name !== $real_name) { 417 $this->real_name = $real_name; 418 Database::prepare( 419 "UPDATE `##user` SET real_name = ? WHERE user_id = ?" 420 )->execute([$real_name, $this->user_id]); 421 } 422 423 return $this; 424 } 425 426 /** 427 * Get the email address of this user. 428 * 429 * @return string 430 */ 431 public function getEmail() { 432 return $this->email; 433 } 434 435 /** 436 * Set the email address of this user. 437 * 438 * @param string $email 439 * 440 * @return User 441 */ 442 public function setEmail($email) { 443 if ($this->email !== $email) { 444 $this->email = $email; 445 Database::prepare( 446 "UPDATE `##user` SET email = ? WHERE user_id = ?" 447 )->execute([$email, $this->user_id]); 448 } 449 450 return $this; 451 } 452 453 /** 454 * Set the password of this user. 455 * 456 * @param string $password 457 * 458 * @return User 459 */ 460 public function setPassword($password) { 461 Database::prepare( 462 "UPDATE `##user` SET password = :password WHERE user_id = :user_id" 463 )->execute([ 464 'password' => password_hash($password, PASSWORD_DEFAULT), 465 'user_id' => $this->user_id, 466 ]); 467 468 return $this; 469 } 470 471 /** 472 * Fetch a user option/setting from the wt_user_setting table. 473 * 474 * Since we'll fetch several settings for each user, and since there aren’t 475 * that many of them, fetch them all in one database query 476 * 477 * @param string $setting_name 478 * @param string $default 479 * 480 * @return string 481 */ 482 public function getPreference($setting_name, $default = '') { 483 if (empty($this->preferences) && $this->user_id !== 0) { 484 $this->preferences = Database::prepare( 485 "SELECT SQL_CACHE setting_name, setting_value" . 486 " FROM `##user_setting`" . 487 " WHERE user_id = :user_id" 488 )->execute([ 489 'user_id' => $this->user_id, 490 ])->fetchAssoc(); 491 } 492 493 if (!array_key_exists($setting_name, $this->preferences)) { 494 $this->preferences[$setting_name] = $default; 495 } 496 497 return $this->preferences[$setting_name]; 498 } 499 500 /** 501 * Update a setting for the user. 502 * 503 * @param string $setting_name 504 * @param string $setting_value 505 * 506 * @return User 507 */ 508 public function setPreference($setting_name, $setting_value) { 509 if ($this->user_id !== 0 && $this->getPreference($setting_name) !== $setting_value) { 510 Database::prepare("REPLACE INTO `##user_setting` (user_id, setting_name, setting_value) VALUES (?, ?, LEFT(?, 255))") 511 ->execute([$this->user_id, $setting_name, $setting_value]); 512 513 $this->preferences[$setting_name] = $setting_value; 514 } 515 516 return $this; 517 } 518} 519