<?php
/**
* SeekQuarry/Yioop --
* Open Source Pure PHP Search Engine, Crawler, and Indexer
*
* Copyright (C) 2009 - 2026 Chris Pollett chris@pollett.org
*
* LICENSE:
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <https://www.gnu.org/licenses/>.
*
* END LICENSE
*
* @author Chris Pollett chris@pollett.org
* @license https://www.gnu.org/licenses/ GPL3
* @link https://www.seekquarry.com/
* @copyright 2009 - 2026
* @filesource
*/
namespace seekquarry\yioop\models;
use seekquarry\yioop\configs as C;
use seekquarry\yioop\CronModel;
use seekquarry\yioop\library as L;
use seekquarry\yioop\library\processors\ImageProcessor;
/** For getLocaleTag*/
require_once __DIR__.'/../library/LocaleFunctions.php';
/**
* This class is used to handle
* database statements related to User Administration
*
* @author Chris Pollett
*/
class UserModel extends Model
{
/**
* Associations of the form
* name of field for web forms => database column names/abbreviations
* In this case, things will in general map to the USERS table in the
* Yioop data base
* @var array
*/
public $search_table_column_map = ["first"=>"FIRST_NAME",
"last" => "LAST_NAME", "user" => "USER_NAME", "email"=>"EMAIL",
"status"=>"STATUS"];
/**
* These fields if present in $search_array (used by @see getRows() ),
* but with value "-1", will be skipped as part of the where clause
* but will be used for order by clause
* @var array
*/
public $any_fields = ["status"];
/**
* {@inheritDoc}
*
* @param mixed $args any additional arguments which should be used to
* determine these tables (in this case none)
*/
public function selectCallback($args = null)
{
return "USER_ID, USER_NAME, FIRST_NAME, LAST_NAME, EMAIL, STATUS";
}
/**
* {@inheritDoc}
*
* @param mixed $args any additional arguments which should be used to
* determine these tables (in this case none)
*/
public function fromCallback($args = null)
{
return "USERS";
}
/**
* {@inheritDoc}
*
* @param mixed $args any additional arguments which should be used to
* determine these tables (in this case none)
*/
public function whereCallback($args = null)
{
return "USER_ID != '" . C\PUBLIC_USER_ID . "'";
}
/**
* SQL AND-clause that keeps only USER_ROLE rows (aliased UR) whose
* grant is still active: those that never lapse or whose expiry is
* still in the future. Used so a lapsed, unrenewed role stops
* conferring its activities.
*
* @return string SQL clause beginning with a leading " AND "
*/
private function activeUserRoleClause()
{
return " AND (UR.EXPIRES = " . C\FOREVER . " OR UR.EXPIRES > " .
time() . ") ";
}
/**
* Get a list of admin activities that a user is allowed to perform.
* This includes their name and their associated method.
*
* @param string $user_id id of user to get activities fors
* @return array list of admin-activity rows the user is permitted to
* run (each row contains the activity's METHOD_NAME and
* localized name); empty array when the user is suspended or
* inactive
*/
public function getUserActivities($user_id)
{
$db = $this->db;
$activities = [];
$status = $this->getUserStatus($user_id);
if (!$status || in_array($status,
[C\SUSPENDED_STATUS, C\INACTIVE_STATUS])) {
return [];
}
$locale_tag = L\getLocaleTag();
$limit_offset = $db->limitOffset(1);
$sql = "SELECT LOCALE_ID FROM LOCALE ".
"WHERE LOCALE_TAG = ? $limit_offset";
$result = $db->execute($sql, [$locale_tag]);
$row = $db->fetchArray($result);
$locale_id = $row['LOCALE_ID'];
$sql = "SELECT DISTINCT A.ACTIVITY_ID AS ACTIVITY_ID, ".
"T.TRANSLATION_ID AS TRANSLATION_ID, A.METHOD_NAME AS METHOD_NAME,".
" RA.ALLOWED_ARGUMENTS AS ALLOWED_ARGUMENTS," .
" T.IDENTIFIER_STRING AS IDENTIFIER_STRING FROM ACTIVITY A, ".
" USER_ROLE UR, ROLE_ACTIVITY RA, TRANSLATION T ".
" WHERE UR.USER_ID = ? " . $this->activeUserRoleClause() .
" AND UR.ROLE_ID=RA.ROLE_ID AND T.TRANSLATION_ID=A.TRANSLATION_ID ".
" AND RA.ACTIVITY_ID = A.ACTIVITY_ID ORDER BY A.ACTIVITY_ID ASC";
$result = $db->execute($sql, [$user_id]);
$sub_sql = "SELECT TRANSLATION AS ACTIVITY_NAME ".
"FROM TRANSLATION_LOCALE ".
"WHERE TRANSLATION_ID=? AND LOCALE_ID=? $limit_offset";
// maybe do left join at some point
while ($row = $this->db->fetchArray($result)) {
$id = $row['TRANSLATION_ID'];
if (empty($activities[$id])) {
$activities[$id] = $row;
} else {
if (empty($activities[$id]["ALLOWED_ARGUMENTS"]) ||
$activities[$id]["ALLOWED_ARGUMENTS"] == "all") {
// all arguments allowed
$activities[$id]["ALLOWED_ARGUMENTS"] = "all";
continue;
} else if (empty($row["ALLOWED_ARGUMENTS"]) ||
trim($row["ALLOWED_ARGUMENTS"]) == "all") {
$activities[$id]["ALLOWED_ARGUMENTS"] = "all";
}
$activities[$id]["ALLOWED_ARGUMENTS"] .= ", " .
$row["ALLOWED_ARGUMENTS"];
}
$result_sub = $db->execute($sub_sql, [$id, $locale_id]);
$translate = $db->fetchArray($result_sub);
if ($translate) {
$activities[$id]['ACTIVITY_NAME'] = $translate['ACTIVITY_NAME'];
}
if (!isset($activities[$id]['ACTIVITY_NAME']) ||
$activities[$id]['ACTIVITY_NAME'] == "") {
$activities[$id]['ACTIVITY_NAME'] = $this->translateDb(
$activities[$id]['IDENTIFIER_STRING'],
C\p('DEFAULT_LOCALE'));
}
}
$activities = array_values($activities);
return $activities;
}
/**
* Checks if a user is allowed to perform the activity given by
* method name
*
* @param string $user_id id of user to check
* @param string $method_name to see if user allowed to do
* @param bool $get_roles when true, return the list of roles that
* grant the activity instead of a boolean (used by the role-audit UI)
* @return mixed bool whether the user is allowed; or, when
* $get_roles is true, the array of granting role ids
*/
public function isAllowedUserActivity($user_id, $method_name,
$get_roles = false)
{
$db = $this->db;
$select = "COUNT(*)";
$sql = "SELECT $select AS ALLOWED FROM ACTIVITY A, ".
"USER_ROLE UR, ROLE_ACTIVITY RA WHERE UR.USER_ID = ? " .
$this->activeUserRoleClause() .
"AND UR.ROLE_ID=RA.ROLE_ID AND A.METHOD_NAME = ? ".
"AND RA.ACTIVITY_ID = A.ACTIVITY_ID";
$result = $db->execute($sql, [$user_id, $method_name]);
$role_ids = [];
if ($result) {
while($row = $db->fetchArray($result)) {
if (intval($row['ALLOWED']) > 0) {
return true;
}
}
}
return false;
}
/**
* Records which signed-in user a session id belongs to, so the
* session can be restored later if the server no longer has it in
* memory (for example after it was dropped under load or the server
* restarted). Any earlier mapping for the same session id is
* replaced. Nothing is stored for the public (not signed-in) user.
*
* @param string $session_id the session id from the visitor's
* cookie
* @param int $user_id id of the signed-in user
* @param int $expires unix time after which this mapping should no
* longer be honored
*/
public function setSessionUser($session_id, $user_id, $expires)
{
if ($user_id == C\PUBLIC_USER_ID) {
return;
}
$sql = "DELETE FROM AUTH_SESSION WHERE SESSION_ID = ?";
$this->db->execute($sql, [$session_id]);
$sql = "INSERT INTO AUTH_SESSION VALUES (?, ?, ?)";
$this->db->execute($sql, [$session_id, $user_id, $expires]);
}
/**
* Looks up which signed-in user a session id belongs to, ignoring
* mappings that have passed their expiry time. Used to restore a
* session that is no longer in the server's memory.
*
* @param string $session_id the session id from the visitor's
* cookie
* @return int the user id, or 0 when there is no usable mapping
*/
public function getSessionUser($session_id)
{
$db = $this->db;
$sql = "SELECT USER_ID FROM AUTH_SESSION " .
"WHERE SESSION_ID = ? AND EXPIRES > ? " . $db->limitOffset(1);
$result = $db->execute($sql, [$session_id, time()]);
if (!empty($result)) {
$row = $db->fetchArray($result);
if (!empty($row["USER_ID"])) {
return $row["USER_ID"];
}
}
return 0;
}
/**
* Removes the mapping from a session id to its user, for example
* when the person signs out so the session can no longer be
* restored.
*
* @param string $session_id the session id from the visitor's
* cookie
*/
public function deleteSessionUser($session_id)
{
$sql = "DELETE FROM AUTH_SESSION WHERE SESSION_ID = ?";
$this->db->execute($sql, [$session_id]);
}
/**
* Returns $_SESSION variable of given user from the last time
* logged in.
*
* @param int $user_id id of user to get session for
* @return array user's session data
*/
public function getUserSession($user_id)
{
$db = $this->db;
$sql = "SELECT SESSION FROM USER_SESSION ".
"WHERE USER_ID = ? " . $db->limitOffset(1);
$result = $db->execute($sql, [$user_id]);
if (!empty($result)) {
$row = $db->fetchArray($result);
if (!empty($row["SESSION"])) {
$session = unserialize($row["SESSION"]);
if (!empty($session)) {
return $session;
}
}
}
return [];
}
/**
* Stores into DB the $session associative array of given user
*
* @param int $user_id id of user to store session for
* @param array $session session data for the given user
* @return bool true on a successful insert, false when the session
* exceeds MAX_USER_SESSION_SIZE, the user is PUBLIC_USER_ID,
* or the insert itself failed
*/
public function setUserSession($user_id, $session)
{
$session_string = serialize($session);
if (strlen($session_string) >= C\MAX_USER_SESSION_SIZE ||
$user_id == C\PUBLIC_USER_ID) {
return false;
}
$sql = "DELETE FROM USER_SESSION WHERE USER_ID = ?";
$this->db->execute($sql, [$user_id]);
$sql = "INSERT INTO USER_SESSION VALUES (?, ?)";
if(!$this->db->execute($sql, [$user_id, $session_string])) {
return false;
}
return true;
}
/**
* Get a username by user_id
*
* @param string $user_id id of the user
* @return string the name of the user corresponding to that id
*/
public function getUsername($user_id)
{
$db = $this->db;
if (intval($user_id) != $user_id) {
return false; //keep postgres error log cleaner by doing check
}
$sql = "SELECT USER_NAME FROM USERS WHERE USER_ID = ?";
$result = $db->execute($sql, [$user_id]);
$row = $db->fetchArray($result);
return ($row['USER_NAME'] ?? false);
}
/**
* Get the status of user by user_id
*
* @param string $user_id id of the user
* @return int the status flag of the user: ACTIVE, INACTIVE, INVITED,
* SUSPENDED
*/
public function getUserStatus($user_id)
{
$db = $this->db;
if (intval($user_id) != $user_id) {
return false; //keep postgres error log cleaner by doing check
}
$sql = "SELECT STATUS FROM USERS WHERE USER_ID = ?";
$result = $db->execute($sql, [$user_id]);
$row = $db->fetchArray($result);
return $row['STATUS'] ?? false;
}
/**
* Returns a row from the USERS table based on a username (case-insensitive)
*
* @param string $username user login to be used for look up
* @return array corresponds to the row of that user in the USERS table
*/
public function getUser($username)
{
$db = $this->db;
$sql = "SELECT * FROM USERS WHERE LOWER(USER_NAME) = LOWER(?) " .
$db->limitOffset(1);
$result = $db->execute($sql, [$username]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (isset($row['USER_ID'])) {
$row['USER_ICON'] = $this->getUserIconUrl($row['USER_ID']);
$row['IS_BOT_USER'] = $this->isBotUser($row['USER_ID']);
}
if (isset($row['IS_BOT_USER'])) {
$sql = "SELECT BOT_TOKEN, CALLBACK_URL
FROM CHAT_BOT WHERE USER_ID = ?";
$result = $db->execute($sql,[$row['USER_ID']]);
$bot_row = $db->fetchArray($result);
if (!empty($bot_row)) {
$row['BOT_TOKEN'] = $bot_row['BOT_TOKEN'];
$row['CALLBACK_URL'] = $bot_row['CALLBACK_URL'];
} else {
$row['BOT_TOKEN'] = "";
$row['CALLBACK_URL'] = "";
}
}
return $row;
}
/**
* Looks up a USERS row based on their $email (potentially not unique)
* and the time at which their account was create in microseconds
* @param string $email of user to lookup
* @param string $creation_time when the user's account was created in
* the current epoch
* @return array row from USERS table
*/
public function getUserByEmailTime($email, $creation_time)
{
$db = $this->db;
$sql = "SELECT * FROM USERS WHERE LOWER(EMAIL) = LOWER(?)
AND CREATION_TIME = ? " . $db->limitOffset(1);
$result = $db->execute($sql, [$email, $creation_time]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (isset($row['USER_ID'])) {
$row['USER_ICON'] = $this->getUserIconUrl($row['USER_ID']);
}
return $row;
}
/**
* Looks up the account, if any, that uses a given email address.
* Used to stop the site's own bot address from being pointed at an
* address some member already signs up with. The match ignores
* letter case.
*
* @param string $email the email address to look up
* @return array|bool the matching user row, or false when no account
* uses that address
*/
public function getUserByEmail($email)
{
$db = $this->db;
$sql = "SELECT * FROM USERS WHERE LOWER(EMAIL) = LOWER(?) " .
$db->limitOffset(1);
$result = $db->execute($sql, [$email]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (isset($row['USER_ID'])) {
$row['USER_ICON'] = $this->getUserIconUrl($row['USER_ID']);
}
return $row;
}
/**
* Saves the personal recovery question a member typed and, when an
* answer is given, the hash of that answer, used when the site recovers
* accounts by question rather than by email. The question is kept as
* plain text so it can be shown back to the member, while only a one-way
* hash of the answer is stored, the same way a password is. Passing null
* for the answer leaves the stored answer untouched, which lets a member
* reword their question without retyping the answer.
*
* @param int $user_id numeric id of the member
* @param string $question the recovery question the member typed
* @param string $answer the member's answer, hashed before storage, or
* null to keep the answer already on file
*/
public function setRecoveryQuestion($user_id, $question, $answer = null)
{
if ($answer === null) {
$sql = "UPDATE USERS SET RECOVERY_QUESTION = ? WHERE USER_ID = ?";
$this->db->execute($sql, [$question, $user_id]);
return;
}
$sql = "UPDATE USERS SET RECOVERY_QUESTION = ?, " .
"RECOVERY_ANSWER = ? WHERE USER_ID = ?";
$this->db->execute($sql,
[$question, L\crawlCrypt($answer), $user_id]);
}
/**
* Checks whether a typed answer matches the stored hash of a member's
* recovery answer, using a constant-time comparison so a wrong answer
* and a right one take the same time to reject.
*
* @param array $user a USERS row holding the stored RECOVERY_ANSWER hash
* @param string $answer the answer the person typed during recovery
* @return bool true only when the answer matches the stored hash
*/
public function checkRecoveryAnswer($user, $answer)
{
if (empty($user['RECOVERY_ANSWER'])) {
return false;
}
return hash_equals($user['RECOVERY_ANSWER'],
L\crawlCrypt($answer, $user['RECOVERY_ANSWER']));
}
/**
* Looks up a user by their numeric user id and returns the whole
* USERS row, or false when there is no such user. Used by the
* account-activation link, which names the new member by an opaque
* signed token holding only this id, so the email address never
* travels in the link.
*
* @param int $user_id the id of the user to look up
* @return array|bool the user's database row as an associative
* array, or false when no user has that id
*/
public function getUserById($user_id)
{
$db = $this->db;
$sql = "SELECT * FROM USERS WHERE USER_ID = ? " .
$db->limitOffset(1);
$result = $db->execute($sql, [$user_id]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (isset($row['USER_ID'])) {
$row['USER_ICON'] = $this->getUserIconUrl($row['USER_ID']);
}
return $row;
}
/**
* Works out the on-disk folder name and its short prefix for a user's
* private files. The name is a crawlHash of the user id keyed by the
* site AUTH_KEY, so it cannot be guessed from the id alone, and the
* prefix is its first few characters, used to spread users across
* subdirectories rather than piling them into one folder. The hash is
* left as crawlHash on purpose: it names an existing folder on disk, so
* changing it would orphan every user's current files.
*
* @param int $user_id id of the user whose folder is wanted
* @return array the folder name followed by its prefix
*/
public function userFolderAndPrefix($user_id)
{
$user_folder = L\crawlHash("user" . $user_id . C\p('AUTH_KEY'));
$user_prefix = substr($user_folder, 0, C\FOLDER_PREFIX_LEN);
return [$user_folder, $user_prefix];
}
/**
* Returns the relative url needed to request the given users avatar icon
*
* @param int $user_id user to look up path for
* @return string path to icon
*/
public function getUserIconUrl($user_id)
{
$user_icon = C\SHORT_BASE_URL . "resources/anonymous.png";
list($user_folder, $user_prefix) =
$this->userFolderAndPrefix($user_id);
$icon_path = C\APP_DIR .
"/resources/$user_prefix/$user_folder/user_icon.webp";
if (file_exists($icon_path)) {
if (C\REDIRECTS_ON) {
$user_icon = C\SHORT_BASE_URL .
"wd/users/$user_folder/user_icon.webp";
} else {
$user_icon = C\SHORT_BASE_URL .
"?c=resource&a=get&f=resources&" .
"s=$user_folder&n=user_icon.webp";
}
}
return $user_icon;
}
/**
* Returns the path to a user's resource folder (where uploaded files
* will be stored). It creates the folder if it does not exist
*
* @param int $user_id user id of user to get path for
* @return string|bool absolute filesystem path to the user's resource
* folder on success; false if any of the parent directories
* could not be created
*/
public function getUserIconFolder($user_id)
{
list($user_folder, $user_prefix) =
$this->userFolderAndPrefix($user_id);
$resource_path = C\APP_DIR . "/resources";
$prefix_path = $resource_path."/$user_prefix";
$user_path = "$prefix_path/$user_folder";
if (file_exists($user_path)) {
return $user_path;
}
if (!file_exists(C\APP_DIR) && !mkdir(C\APP_DIR)) {
return false;
}
if (!file_exists($resource_path) && !mkdir($resource_path)) {
return false;
}
if (!file_exists($prefix_path) && !mkdir($prefix_path)) {
return false;
}
if (mkdir($user_path)) {
return $user_path;
}
return false;
}
/**
* Set status of user by user_id
*
* @param string $user_id id of the user
* @param int $status one of ACTIVE_STATUS, INACTIVE_STATUS, or
* SUSPENDED_STATUS
*/
public function updateUserStatus($user_id, $status)
{
$db = $this->db;
if (!in_array($status, [C\ACTIVE_STATUS, C\INACTIVE_STATUS,
C\SUSPENDED_STATUS])) {
return;
}
$sql = "UPDATE USERS SET STATUS=? WHERE USER_ID=?";
$db->execute($sql, [$status, $user_id]);
}
/**
* Add a user with a given username and password to the list of users
* that can login to the admin panel
*
* @param string $username the username of the user to be added
* @param string $password the password in plaintext
* of the user to be added
* @param string $firstname the firstname of the user to be added
* @param string $lastname the lastname of the user to be added
* @param string $email the email of the user to be added
* @param int $status one of ACTIVE_STATUS, INACTIVE_STATUS, or
* SUSPENDED_STATUS
* @return mixed false if operation not successful, user_id otherwise
*/
public function addUser($username, $password, $firstname = '', $lastname='',
$email = '', $status = C\ACTIVE_STATUS)
{
$creation_time = L\microTimestamp();
$db = $this->db;
$sql = "INSERT INTO USERS(FIRST_NAME, LAST_NAME,
USER_NAME, EMAIL, PASSWORD, STATUS, HASH,
CREATION_TIME) VALUES (
?, ?, ?, ?, ?, ?, ?, ?)";
$username = mb_strtolower($username);
$result = $db->execute($sql, [$firstname, $lastname,
$username, $email, L\crawlCrypt($password), $status,
L\crawlCrypt($username . C\p('AUTH_KEY') . $creation_time),
$creation_time]);
if (!$user_id = $this->getUserId($username)) {
return false;
}
$now = time();
$user_id = $db->escapeString($user_id);
$sql = "INSERT INTO USER_GROUP (USER_ID, GROUP_ID, STATUS,
JOIN_DATE) VALUES(?, ?, ?, ?)";
$result = $db->execute($sql, [$user_id, C\PUBLIC_GROUP_ID,
C\ACTIVE_STATUS, $now]);
/* Naming the columns rather than relying on their order lets the
two columns a role grant has gained since, when it lapses and
whether it renews, take the defaults they were added with; a
row given only two values in order is refused outright once a
table has more columns than that. */
$sql = "INSERT INTO USER_ROLE (USER_ID, ROLE_ID) VALUES (?, ?)";
$result_id = $db->execute($sql, [$user_id, C\USER_ROLE]);
return $user_id;
}
/**
* Deletes a user by username from the list of users that can login to
* the admin panel
*
* @param string $username the login name of the user to delete
*/
public function deleteUser($username)
{
$db = $this->db;
$user_id = $this->getUserId($username);
if ($user_id) {
$sql = "DELETE FROM USER_ROLE WHERE USER_ID=?";
$result = $db->execute($sql, [$user_id]);
$sql = "DELETE FROM USER_GROUP WHERE USER_ID=?";
$result = $db->execute($sql, [$user_id]);
$sql = "DELETE FROM USER_SESSION WHERE USER_ID=?";
$result = $db->execute($sql, [$user_id]);
}
$sql = "DELETE FROM USERS WHERE USER_ID=?";
$result = $db->execute($sql, [$user_id]);
}
/**
* Used to update the fields stored in a USERS row according to
* an array holding new values
*
* @param array $user updated values for a USERS row
*/
public function updateUser($user)
{
$user_id = $user['USER_ID'];
if (isset($user['IMAGE_STRING'])) {
$folder = $this->getUserIconFolder($user_id);
$image = @imagecreatefromstring($user['IMAGE_STRING']);
$thumb_string = ImageProcessor::createThumb($image);
if (!empty($thumb_string)) {
file_put_contents($folder . "/user_icon.webp",
$thumb_string);
clearstatcache($folder . "/user_icon.webp");
}
}
unset($user['USER_ID'], $user['IMAGE_STRING'], $user['USER_ICON'],
$user['IS_BOT_USER'], $user['BOT_TOKEN'], $user['CALLBACK_URL']);
$user['USER_NAME'] = mb_strtolower($this->getUserName(
$user_id));
if (empty($user['USER_NAME'])) {
unset($user['USER_NAME']);
}
$sql = "UPDATE USERS SET ";
$comma ="";
$params = [];
if ($user == []) {
return;
}
foreach ($user as $field => $value) {
$sql .= "$comma $field=? ";
$comma = ",";
if ($field == "PASSWORD") {
$params[] = L\crawlCrypt($value);
} else {
$params[] = $value;
}
}
$sql .= " WHERE USER_ID=?";
$params[] = $user_id;
$this->db->execute($sql, $params);
}
/**
* Checks if a user is bot
*
* @param string $user_id id of user to check
* @return bool whether or not the user is bot
*/
public function isBotUser($user_id)
{
$db = $this->db;
$sql = "SELECT COUNT(*) AS NUM FROM ".
"USER_ROLE UR WHERE UR.USER_ID = ? ".
"AND UR.ROLE_ID = ?";
$is_bot = false;
$bot_id = RoleModel::getRoleIdWithDb("Bot User", $db);
if ($bot_id) {
$result = $db->execute($sql, [$user_id, $bot_id]);
if ($result) {
while ($row = $db->fetchArray($result)) {
if ($row["NUM"] > 0) {
$is_bot = true;
} else {
$is_bot = false;
}
}
}
}
return $is_bot;
}
/**
* Used to update the fields stored in a CHAT_BOT row according to
* an array holding new values
*
* @param array $user updated values for a CHAT_BOT row
*/
public function updateBot($user)
{
if ($user == []) {
return;
}
$db = $this->db;
$sql = "SELECT COUNT(*) AS NUM FROM ".
"CHAT_BOT CB WHERE CB.USER_ID = ? ";
$result = $db->execute($sql, [$user['USER_ID']]);
$is_new_bot = false;
if ($result) {
while ($row = $db->fetchArray($result)) {
if ($row["NUM"] > 0) {
$is_new_bot = false;
} else {
$is_new_bot = true;
}
}
}
if (!$this->isBotUser($user["USER_ID"])) {
if (!$is_new_bot) {
$sql = "DELETE FROM CHAT_BOT WHERE USER_ID = ?";
$db->execute($sql, [$user['USER_ID']]);
}
return;
}
if ($is_new_bot) {
$user['BOT_TOKEN'] = (empty($user['BOT_TOKEN'])) ? "" :
$user['BOT_TOKEN'];
$user['CALLBACK_URL'] = (empty($user['CALLBACK_URL'])) ? "" :
$user['CALLBACK_URL'];
$sql = "INSERT INTO CHAT_BOT(USER_ID, BOT_TOKEN,
CALLBACK_URL) VALUES (?, ?, ?)";
$result = $db->execute($sql, [$user['USER_ID'], $user['BOT_TOKEN'],
$user['CALLBACK_URL']]);
} else {
$sql = "UPDATE CHAT_BOT SET ";
$comma = "";
$params = [];
foreach ($user as $field => $value) {
if ($field == "USER_ID") {
$sql .= "$comma $field = ? ";
$comma = ",";
$params[] = $value;
} else if ($field == "BOT_TOKEN") {
$sql .= "$comma $field = ? ";
$comma = ",";
$params[] = $value;
} else if ($field == "CALLBACK_URL") {
$sql .= "$comma $field = ? ";
$comma = ",";
$params[] = $value;
}
}
$sql .= " WHERE USER_ID = ?";
$params[] = $user['USER_ID'];
$this->db->execute($sql, $params);
}
}
/**
* Gets $num many user recommendations of the given type.
*
* @param int $timestamp of the recommendation media job calculation
* of recommendations (available using cron model
* item_group_recommendations)
* @param int $user_id to user to get recommendations for
* @param int $type = one of C\TRENDING_RECOMMENDATION,
* C\THREAD_RECOMMENDATION, or C\GROUP_RECOMMENDATION
* @param int $num the number of recommendations to return
* @return array list of recommendation rows (each having ID and NAME
* columns from GROUP_ITEM or SOCIAL_GROUPS depending on $type),
* ordered by SCORE DESC and capped at $num entries
*/
public function getRecommendations($timestamp, $user_id, $type, $num = 3)
{
$db = $this->db;
$name_table = "GROUP_ITEM";
$name_column = "TITLE";
$name_id = "ID";
if ($type == C\GROUP_RECOMMENDATION) {
$name_table = "SOCIAL_GROUPS";
$name_column = "GROUP_NAME";
$name_id = "GROUP_ID";
}
$sql = "SELECT IR.ITEM_ID AS ID, NT.$name_column AS NAME ".
"FROM GROUP_ITEM_RECOMMENDATION IR, $name_table NT ".
"WHERE IR.ITEM_ID = NT.$name_id AND IR.USER_ID = ? AND " .
"ITEM_TYPE = ? AND TIMESTAMP = $timestamp " .
"ORDER BY SCORE DESC " . $db->limitOffset($num);
$result = $db->execute($sql, [$user_id, $type]);
$recommendations = [];
while($row = $db->fetchArray($result)) {
$recommendations[$row['ID']] = $row['NAME'];
}
return $recommendations;
}
/**
* Returns the top wiki-resource recommendations for the given user,
* resolved from GROUP_RESOURCE_RECOMMENDATION back into a list of
* [group_id, page_id, page_title, resource_name, sub_path] tuples
* the UI can link to.
*
* @param int $user_id user whose recommendations to load
* @param int $num max number of recommendations to return (default 3)
* @return array list of resource-recommendation tuples ordered by
* SCORE DESC, capped at $num entries
*/
public function getResourceRecommendations($user_id, $num = 3)
{
$db = $this->db;
$sql = "SELECT * FROM GROUP_RESOURCE_RECOMMENDATION WHERE" .
" USER_ID = ? ORDER BY SCORE DESC " . $db->limitOffset($num);
$results = $db->execute($sql, [$user_id]);
$recommendations = [];
while($row = $db->fetchArray($results)) {
$group_id = $row['GROUP_ID'];
$page_id = $row['PAGE_ID'];
$page_sql = "SELECT TITLE FROM GROUP_PAGE WHERE ID = ?";
$result = $db->execute($page_sql, [$page_id]);
while ($sub_row = $db->fetchArray($result)) {
$page_title = $sub_row['TITLE'];
}
$index = strrpos($row['RESOURCE_PATH'], "/");
$name = substr($row['RESOURCE_PATH'], $index + 1);
$resource_index = strrpos($row['RESOURCE_PATH'], "/resources/");
$sub_path = substr($row['RESOURCE_PATH'], $resource_index + 28,
$index - $resource_index - 28);
$recommendations[] = [$group_id, $page_id, $page_title ?? "",
$name ?? "", $sub_path ?? ""];
}
return $recommendations;
}
}
(c) 2026 Seekquarry - Open Source Search Engine Software