<?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 Mallika Perepa, Chris Pollett
* @license https://www.gnu.org/licenses/ GPL3
* @link https://www.seekquarry.com/
* @copyright 2009 - 2026
* @filesource
*/
namespace seekquarry\yioop\models;
use seekquarry\yioop as B;
use seekquarry\yioop\configs as C;
use seekquarry\yioop\library as L;
use seekquarry\yioop\library\av_processing\AudioConverter;
use seekquarry\yioop\library\av_processing\VideoExtractor;
use seekquarry\yioop\library\MediaConstants;
use seekquarry\yioop\library\version_control\VersionManager;
use seekquarry\yioop\library\wiki\WikiParser;
use seekquarry\yioop\library\processors\TextProcessor;
use seekquarry\yioop\library\processors\ImageProcessor;
use seekquarry\yioop\library\processors\EpubProcessor;
use seekquarry\yioop\library\processors\PdfProcessor;
use seekquarry\yioop\library\processors\VideoProcessor;
use seekquarry\yioop\models\ImpressionModel;
use seekquarry\yioop\library\wiki as LW;
/**
* FeedModel stores the feed of a group in the database. A feed is a
* list of threads the members write. Each thread holds posts.
*
* It starts a thread, adds a post to one, edits a post, and deletes
* one. It counts the threads in a group and the posts in a thread. It
* stores who follows a thread and who subscribes to a group, so they
* can be told when a post arrives. It stores that a member flagged a
* post as unwanted, and it stores a moderator's decision about it.
*
* This model extends GroupModel, which stores the group a thread
* belongs to and the key that encrypts a private group's posts.
*
* SocialComponent uses this model to draw the group feed screen and to
* save what someone writes there. GroupfeedElement draws what it
* reads.
*
* @author Chris Pollett
*/
class FeedModel extends GroupModel
{
/**
* countGroupThreads returns how many threads (top-level feed items) a group
* has.
* @param int $group_id group to count threads for
* @return int number of threads belonging to $group_id
*/
public function countGroupThreads($group_id)
{
$db = $this->db;
$sql = "SELECT COUNT(*) AS NUM FROM GROUP_ITEM " .
"WHERE GROUP_ID = ? AND PARENT_ID = ID";
$result = $db->execute($sql, [$group_id]);
$row = ($result) ? $db->fetchArray($result) : ['NUM' => 0];
return $row['NUM'];
}
/**
* countThreadPosts returns how many posts a thread has, counting the
* thread's own starting item together with its replies.
* @param int $thread_id parent thread to count posts within
* @return int number of posts whose parent is $thread_id
*/
public function countThreadPosts($thread_id)
{
$db = $this->db;
$sql = "SELECT COUNT(*) AS NUM FROM GROUP_ITEM " .
"WHERE PARENT_ID = ?";
$result = $db->execute($sql, [$thread_id]);
$row = ($result) ? $db->fetchArray($result) : ['NUM' => 0];
return $row['NUM'];
}
/**
* isThreadSubscribed says whether a member is following one discussion
* thread, meaning they have asked to be mailed about it. Not following is
* the default, so this returns true only when the member has posted to the
* thread or pressed the follow control for it.
* @param int $user_id the member to check
* @param int $thread_id the thread (its first post's id) to check
* @return bool true if the member is following this thread's mail
*/
public function isThreadSubscribed($user_id, $thread_id)
{
$db = $this->db;
$sql = "SELECT USER_ID FROM THREAD_FOLLOW " .
"WHERE USER_ID = ? AND THREAD_ID = ?";
$result = $db->execute($sql, [$user_id, $thread_id]);
return ($result && $db->fetchArray($result)) ? true : false;
}
/**
* setThreadSubscribe starts or stops one member following a single
* discussion thread. Following records a row saying "mail me about this
* thread"; unfollowing removes any such row. Any existing row is cleared
* first so a thread never has two follow rows for the same member, which
* also makes following a thread you already follow harmless.
* @param int $user_id the member whose choice is changing
* @param int $thread_id the thread (its first post's id) the choice is for
* @param bool $subscribe true to follow this thread (be mailed about it),
* false to stop following it
*/
public function setThreadSubscribe($user_id, $thread_id,
$subscribe)
{
$db = $this->db;
$db->execute("DELETE FROM THREAD_FOLLOW " .
"WHERE USER_ID = ? AND THREAD_ID = ?",
[$user_id, $thread_id]);
if ($subscribe) {
$db->execute("INSERT INTO THREAD_FOLLOW " .
"(USER_ID, THREAD_ID) VALUES (?, ?)",
[$user_id, $thread_id]);
}
}
/**
* getGroupThreadId get the PARENT_ID of a thread based on the group-id opf
* group thread is in, the user_id of the user posting the thread and the
* exact thread title
* @param int $group_id id of group thread is in
* @param int $user_id id of user posting to thread
* @param string $title exact title of thread
* @return int the id of the first thread matching the above criteria
*/
public function getGroupThreadId($group_id, $user_id, $title)
{
$db = $this->db;
if ($user_id !== null) {
$sql = "SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND " .
"USER_ID=? AND TITLE=? " . $db->limitOffset(1);
$result = $db->execute($sql, [$group_id, $user_id, $title]);
} else {
$sql = "SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND " .
"TITLE=? " . $db->limitOffset(1);
$result = $db->execute($sql, [$group_id, $title]);
}
$row = $db->fetchArray($result);
return $row["PARENT_ID"] ?? false;
}
/**
* getGroupItem returns the GROUP_FEED item with the given id
* @param int $item_id the item to get info about
* @param bool $is_group_id whether $item_id is a group_id (true) or a row
* id from GROUP_ITEM (false)
* @return array row from GROUP_FEED table
*/
public function getGroupItem($item_id, $is_group_id = false)
{
$db = $this->db;
if ($is_group_id) {
$sql = "SELECT * FROM GROUP_ITEM WHERE GROUP_ID=? " .
$db->limitOffset(1);
} else {
$sql = "SELECT * FROM GROUP_ITEM WHERE ID=? " . $db->limitOffset(1);
}
$result = $db->execute($sql, [$item_id]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (!empty($row['GROUP_ID']) &&
$this->isGroupEncrypted($row['GROUP_ID'])) {
/* Decrypt group's title and description */
$key = $this->getGroupKey($row['GROUP_ID']);
$row['TITLE'] = $this->decrypt($row['TITLE'], $key);
$row['DESCRIPTION'] = $this->decrypt($row['DESCRIPTION'], $key);
}
return $row;
}
/**
* getThreadFollowers returns an array of user information about users who
* have contributed to a thread or own the group a thread belongs to
* @param int $thread_id the id of the thread that want users for
* @param int $owner_id usually owner of group thread belongs to however
* could be id of any additional user want info for. If left a default
* value (-1) than only user's who have contributed to thread will be
* returned
* @param int $exclude_id an id of a user to exclude from the array returned
* @return array user information of users following the thread
*/
public function getThreadFollowers($thread_id, $owner_id = -1,
$exclude_id = -1)
{
$db = $this->db;
$params = [$thread_id, $owner_id];
$sql = "SELECT DISTINCT U.USER_ID AS USER_ID, " .
"U.USER_NAME AS USER_NAME, U.EMAIL AS EMAIL ".
"FROM GROUP_ITEM GI, USERS U ".
"WHERE GI.PARENT_ID=? AND (GI.USER_ID=U.USER_ID OR U.USER_ID=?)";
if ($exclude_id != -1) {
$sql .= " AND U.USER_ID != ?";
$params[] = $exclude_id;
}
$result = $db->execute($sql, $params);
if (!$result) {
return false;
}
$i = 0;
$rows = [];
while ($row = $db->fetchArray($result)) {
$rows[] = $row;
}
return $rows;
}
/**
* getThreadSubscribers returns the members who are following one discussion
* thread, that is the people who asked to be mailed about it (by posting to
* it or pressing its follow control). Used to build the recipient list when
* a new reply is posted, so each row carries the address fields a mail
* needs.
* @param int $thread_id the thread (its first post's id) to list followers
* for
* @param int $exclude_id a user to leave out of the list, normally the
* person who just posted; pass -1 to exclude no one
* @return array one row per follower with USER_ID, USER_NAME and EMAIL
*/
public function getThreadSubscribers($thread_id, $exclude_id = -1)
{
$db = $this->db;
$params = [$thread_id];
$sql = "SELECT DISTINCT U.USER_ID AS USER_ID, " .
"U.USER_NAME AS USER_NAME, U.EMAIL AS EMAIL " .
"FROM THREAD_FOLLOW TF, USERS U " .
"WHERE TF.THREAD_ID = ? AND TF.USER_ID = U.USER_ID";
if ($exclude_id != -1) {
$sql .= " AND U.USER_ID != ?";
$params[] = $exclude_id;
}
$result = $db->execute($sql, $params);
if (!$result) {
return false;
}
$rows = [];
while ($row = $db->fetchArray($result)) {
$rows[] = $row;
}
return $rows;
}
/**
* getFollowedThreads given a list of thread ids, says which of them a
* member is following. The feed uses this so each thread row can show
* whether its follow control is on or off, looked up in one query rather
* than one lookup per row.
* @param int $user_id the member to check
* @param array $thread_ids the thread ids shown on the feed page
* @return array the subset of $thread_ids the member follows, given as a
* map from thread id to true for an easy isset() test
*/
public function getFollowedThreads($user_id, $thread_ids)
{
$followed = [];
if (empty($thread_ids)) {
return $followed;
}
$db = $this->db;
$marks = implode(", ", array_fill(0, count($thread_ids), "?"));
$sql = "SELECT THREAD_ID FROM THREAD_FOLLOW " .
"WHERE USER_ID = ? AND THREAD_ID IN ($marks)";
$params = array_merge([$user_id], $thread_ids);
$result = $db->execute($sql, $params);
if ($result) {
while ($row = $db->fetchArray($result)) {
$followed[$row['THREAD_ID']] = true;
}
}
return $followed;
}
/**
* addGroupItem creates a new group item
* @param int $parent_id thread id to use for the item
* @param int $group_id what group the item should be added to
* @param int $user_id of user making the post
* @param string $title title of the group feed item
* @param string $description actual content of the post
* @param int $type flag saying what kind of group item this is. One of
* STANDARD_GROUP_ITEM, WIKI_GROUP_ITEM (used for threads discussing a
* wiki page)
* @param int $post_time timstamp for when this group item was created
* default to the current time
* @param string $url a url associated with this group item (mainly for
* search group, otherwise use empty string)
* @param int $edit_time timestamp for when this group item was last edited
* @param int $ups number of times the item has been upvoted
* @param int $downs number of times the item has been down voted
* @param int $flag number of times the item has been flagged
* @param int $input_timestamp optional UNIX timestamp to use for impression
* tracking; if set to -1 or omitted, the current time will be used
* @return int $id of item added
*/
public function addGroupItem($parent_id, $group_id, $user_id, $title,
$description, $type= C\STANDARD_GROUP_ITEM, $post_time = 0, $url = "",
$edit_time = 0, $ups = 0, $downs = 0, $flag = 0, $input_timestamp = -1)
{
$db = $this->db;
if ($post_time == 0) {
$post_time = time();
}
if ($edit_time <= $post_time) {
$edit_time = $post_time;
}
if ($this->isGroupEncrypted($group_id)) {
$key = $this->getGroupKey($group_id);
$title = $this->encrypt($title, $key);
$description = $this->encrypt($description, $key);
}
$sql = "INSERT INTO GROUP_ITEM (PARENT_ID, GROUP_ID, USER_ID, URL,
TITLE, DESCRIPTION, PUBDATE, EDIT_DATE, TYPE, UPS, DOWNS,
FLAG) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$db->execute($sql, [$parent_id, $group_id, $user_id, $url, $title,
$description, $post_time, $edit_time, $type, $ups, $downs, $flag]);
$id = $db->insertID("GROUP_ITEM");
if ($parent_id == 0) {
$sql = "UPDATE GROUP_ITEM SET PARENT_ID=? WHERE ID=?";
$db->execute($sql, [$id, $id]);
ImpressionModel::initWithDb($user_id, $id, C\THREAD_IMPRESSION,
$db, $input_timestamp);
}
return $id;
}
/**
* addGroupItemModeration adds a flagged group item to the moderation group
* @param int $parent_id thread id to use for the item
* @param int $group_id what group the item should be added to
* @param int $user_id of user making the post
* @param string $title title of the group feed item
* @param string $description actual content of the post
* @param int $flag flag value to record on the inserted GROUP_ITEM row
* (e.g. NEEDS_MODERATION_FLAG)
* @param int $parent_item_id row id of the original item that was flagged;
* the new moderation post points back to it
* @param int $parent_group_id group id the flagged item lives in; used to
* fetch the encryption key when re-decrypting before reinsertion
* @param int $type flag saying what kind of group item this is. One of
* STANDARD_GROUP_ITEM, WIKI_GROUP_ITEM (used for threads discussing a
* wiki page)
* @param int $post_time timstamp for when this group item was created
* default to the current time
* @param string $url a url associated with this group item (mainly for
* search group)
* @return int $id of item added
*/
public function addGroupItemModeration($parent_id, $group_id, $user_id,
$title, $description, $flag, $parent_item_id, $parent_group_id,
$type= C\STANDARD_GROUP_ITEM, $post_time = 0, $url = "")
{
$db = $this->db;
if ($post_time == 0) {
$post_time = time();
}
if($this->isGroupEncrypted($parent_group_id)) {
$key = $this->getGroupKey($parent_group_id);
$title = $this->decrypt($title, $key);
$description = $this->decrypt($description, $key);
}
$sql = "INSERT INTO GROUP_ITEM (PARENT_ID, GROUP_ID, USER_ID, URL,
TITLE, DESCRIPTION, PUBDATE, EDIT_DATE, FLAG, PARENT_ITEM_ID, TYPE)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$db->execute($sql, [$parent_id, $group_id, $user_id, $url, $title,
$description, $post_time, $post_time, $flag, $parent_item_id,
$type]);
$id = $db->insertID("GROUP_ITEM");
$update_sql = "UPDATE GROUP_ITEM SET PARENT_ID = ? WHERE ID = ?";
$db->execute($update_sql, [$id, $id]);
return $id;
}
/**
* updateGroupItem updates a group feed item's title and description. This
* assumes the given item already exists.
* @param int $id which item to change
* @param string $title the new title
* @param string $description the new description
*/
public function updateGroupItem($id, $title, $description)
{
$db = $this->db;
$edit_date = time();
$group_item = $this->getGroupItem($id);
if ($this->isGroupEncrypted($group_item['GROUP_ID'])) {
$key = $this->getGroupKey($group_item['GROUP_ID']);
$title = $this->encrypt($title, $key);
$description = $this->encrypt($description, $key);
}
$sql = "UPDATE GROUP_ITEM SET TITLE=?, DESCRIPTION=?,
EDIT_DATE=? WHERE ID=?";
$db->execute($sql, [$title, $description, $edit_date, $id]);
}
/**
* deleteConversationThreadRow lets go of the row a conversation hangs from
* in one person's own message group, which is what takes the conversation
* off their list of contacts. The other person's row is left alone, so they
* still see the conversation on theirs.
* @param int $group_id the person's own message group
* @param string $thread_title the name the pair's conversation is kept
* under
* @return int how many rows were let go
*/
public function deleteConversationThreadRow($group_id, $thread_title)
{
$db = $this->db;
/* The row a conversation hangs from carries no words, which is
what tells it apart from the messages under it. */
$sql = "DELETE FROM GROUP_ITEM WHERE GROUP_ID=? AND TITLE=?
AND DESCRIPTION = ''";
$db->execute($sql, [$group_id, $thread_title]);
return $db->affectedRows();
}
/**
* deleteGroupItem removes a group feed item from the GROUP_ITEM table.
* @param int $post_id of item to remove
* @param int $user_id the id of the person trying to perform the removal.
* If not root, or the original creator of the item, the item won't be
* removed
* @return int number of GROUP_ITEM rows actually deleted (0 when the user
* lacked permission and the row was untouched)
*/
public function deleteGroupItem($post_id, $user_id)
{
$db = $this->db;
$params = [$post_id];
if ($user_id == C\ROOT_ID) {
$and_where = "";
} else {
$and_where = " AND USER_ID=?";
$params[] = $user_id;
}
$sql = "DELETE FROM GROUP_ITEM WHERE ID=? $and_where";
if ($result = $db->execute($sql, $params)) {
$affected_rows = $db->affectedRows();
ImpressionModel::deleteWithDb($user_id, $post_id,
C\THREAD_IMPRESSION, $db);
ImpressionModel::deleteWithDb(C\PUBLIC_USER_ID, $post_id,
C\THREAD_IMPRESSION, $db);
} else {
$affected_rows = $db->affectedRows();
}
return $affected_rows;
}
/**
* flagGroupItem flags a group feed item in the GROUP_ITEM table.
* @param int $post_id of item to be flagged
* @param int $user_id the id of the person trying to flag the item If user
* does not belong to the group, the item won't be flagged
* @return int|bool number of rows updated by the flag insert, or false when
* the threshold has already been reached so the additional flag is
* rejected
*/
public function flagGroupItem($post_id, $user_id)
{
$db = $this->db;
$sql = "SELECT * FROM GROUP_ITEM WHERE ID = ?";
$stmt = $db->execute($sql, [$post_id]);
$result = $stmt->fetch();
$group_id = $result['GROUP_ID'];
$flag_count = $this->getFlagCount($post_id);
$threshold = $this->getThresholdValue($group_id);
if ($this->isGroupEncrypted($group_id)) {
$key = $this->getGroupKey($group_id);
if (is_null($flag_count)) {
$flag_count = $this->encryptFlag(strval($flag_count), $key);
}
if ($flag_count === $threshold) {
$flag_count_decrypt = $this->decryptFlag(
base64_decode($flag_count), $key);
if (C\p('USE_MODERATION')) {
$this->addGroupItemModeration(0,
C\MODERATION_GROUP_ID, $result['USER_ID'],
$result['TITLE'], $result['DESCRIPTION'],
C\MODERATION_FLAGGED, $post_id, $group_id,
C\STANDARD_GROUP_ITEM, 0, "");
}
} else if ($flag_count === $this->encryptFlag($threshold, $key)) {
return false;
}
$flag_count = $this->encryptFlag(strval($flag_count), $key);
} else {
$flag_count++;
if($flag_count > C\p('MODERATION_FLAG_THRESHOLD')) {
return false;
}
if($flag_count === $threshold && C\p('USE_MODERATION')) {
$this->addGroupItemModeration(0, C\MODERATION_GROUP_ID,
$result['USER_ID'], $result['TITLE'],$result['DESCRIPTION'],
C\MODERATION_FLAGGED, $post_id, $group_id,
C\STANDARD_GROUP_ITEM, 0, "");
}
}
$sql = "INSERT INTO GROUP_ITEM_FLAG VALUES (?, ?)";
$this->db->execute($sql, [$user_id, $post_id]);
$update_sql = "UPDATE GROUP_ITEM SET FLAG = ? WHERE ID = ?
AND GROUP_ID IN (SELECT GROUP_ID FROM USER_GROUP
WHERE USER_ID = ?)";
$db->execute($update_sql, [$flag_count, $post_id, $user_id]);
$affected_rows = $db->affectedRows();
return $affected_rows;
}
/**
* getParentPostById get the id of the parent thread of a post
* @param int $post_id denotes the thread
* @return mixed ID of the grand-parent GROUP_ITEM row, or false on query
* failure / missing parent
*/
public function getParentPostById($post_id)
{
$db = $this->db;
$sql = "SELECT ID FROM GROUP_ITEM WHERE ID =
(SELECT PARENT_ITEM_ID FROM GROUP_ITEM WHERE ID = ?)";
$stmt = $db->execute($sql, [$post_id]);
if (!$stmt) {
return false;
}
if (!($row = $db->fetchArray($stmt))) {
return false;
}
return $row['ID'];
}
/**
* getParentIdOfParentPost get parent_id of the parent thread of a given
* post
* @param int $post_id denotes the post
* @return mixed PARENT_ID of the parent GROUP_ITEM row, or false on query
* failure / missing parent
*/
public function getParentIdOfParentPost($post_id)
{
$db = $this->db;
$sql = "SELECT PARENT_ID FROM GROUP_ITEM WHERE ID =
(SELECT PARENT_ITEM_ID FROM GROUP_ITEM WHERE ID = ?)";
$stmt = $db->execute($sql, [$post_id]);
if (!$stmt) {
return false;
}
if (!($row = $db->fetchArray($stmt))) {
return false;
}
return $row['PARENT_ID'];
}
/**
* approveFlaggedPost moderator approves a flagged post
* @param int $post_id to fetch the flagged post
* @return int number of GROUP_ITEM rows updated by the MODERATION_CLEARED
* reset
*/
public function approveFlaggedPost($post_id)
{
/* get parent_item_id and go to the original post */
$item_id= $this->getParentPostById($post_id);
/* reset flag column value to zero */
$db = $this->db;
$update_sql = "UPDATE GROUP_ITEM SET FLAG = 0 WHERE ID = ?";
$db->execute($update_sql, [$item_id]);
$reset_sql = "UPDATE GROUP_ITEM SET FLAG = " . C\MODERATION_CLEARED .
" WHERE ID = ?";
$db->execute($reset_sql, [$post_id]);
$affected_rows = $db->affectedRows();
return $affected_rows;
}
/**
* deleteFlaggedPost deletes a flagged post from group items
* @param int $post_id to fetch the flagged post
* @param string $message string to show for the removed post
* @return int number of GROUP_ITEM rows affected by the MODERATION_REMOVED
* state change
*/
public function deleteFlaggedPost($post_id, $message)
{
$parent_group_id = $this->getParentGroupId($post_id);
if($this->isGroupEncrypted($parent_group_id)) {
$key = $this->getGroupKey($parent_group_id);
$description = $this->encrypt($description, $key);
}
$item_id = $this->getParentPostById($post_id);
$db = $this->db;
$update_sql = "UPDATE GROUP_ITEM SET DESCRIPTION = ? WHERE ID = ?";
$db->execute($update_sql, [$message, $item_id]);
$reset_sql = "UPDATE GROUP_ITEM SET FLAG = ". C\MODERATION_REMOVED .
" WHERE ID = ?";
$db->execute($reset_sql, [$post_id]);
$affected_rows = $db->affectedRows();
return $affected_rows;
}
/**
* getGroupItems gets the group feed items visible to a user with $user_id
* and which match the supplied search criteria found in $search_array,
* starting from the $limit'th matching item to the $limit+$num item.
* @param int $limit starting offset group item to display
* @param int $num number of items from offset to display
* @param array $search_array each element of this is a quadruple name of a
* field, what comparison to perform, a value to check, and an order
* (ascending/descending) to sort by
* @param int $user_id who is making this request to determine which
* @param int $for_group if this value is set it is a assumed that
* group_items are being returned for only one group and that they
* should be grouped by thread
* @return array elements of which represent one group feed item
*/
public function getGroupItems($limit = 0, $num = 100, $search_array = [],
$user_id = C\ROOT_ID, $for_group = -1)
{
$db = $this->db;
$limit = $db->limitOffset($limit, $num);
$any_fields = ["access", "register"];
$is_thread = false;
foreach ($search_array as $search_item) {
if ($search_item[0] == 'parent_id') {
if ($search_item[2] > 0) {
$is_thread = true;
}
break;
}
}
list($where, $order_by) =
$this->searchArrayToWhereOrderClauses($search_array, $any_fields);
$where = str_replace("O.USER_ID", "P.USER_ID", $where); /* hacky */
$add_where = " WHERE ";
if (!empty($where)) {
$add_where = " AND ";
if (str_ends_with(rtrim($where), "AND")) {
$add_where = " ";
}
}
$user_id = $db->escapeString($user_id);
if ($for_group > 0 || $for_group == -2) { /* -2 is just_thread case) */
$non_public_where = " (UG.USER_ID='$user_id' OR ".
" G.REGISTER_TYPE IN ('" . C\PUBLIC_JOIN . "','".
C\PUBLIC_BROWSE_REQUEST_JOIN . "') ) AND ";
if (!$is_thread) {
$non_public_where .=
"TYPE = " . C\STANDARD_GROUP_ITEM. " AND ";
}
} else {
$non_public_where = " UG.USER_ID='$user_id' AND ";
}
$non_public_status = ($user_id != C\PUBLIC_GROUP_ID) ?
" UG.STATUS='" . C\ACTIVE_STATUS . "' AND " : "";
$where .= $add_where . $non_public_where .
"GI.GROUP_ID=G.GROUP_ID AND GI.GROUP_ID=UG.GROUP_ID AND ((
$non_public_status
G.MEMBER_ACCESS IN ('" . C\GROUP_READ ."','".C\GROUP_READ_COMMENT.
"','".C\GROUP_READ_WRITE."', '". C\GROUP_READ_WIKI ."')) OR
(G.OWNER_ID = UG.USER_ID OR UG.USER_ID = '" . C\ROOT_ID . "'))";
$where .= $this->excludeGitIssueThreadsClause("GI");
if ($for_group >= 0) {
$outer_where = "";
if (preg_match("/P\.USER_ID=[\'\"]\d+[\'\"]/", $where, $matches)) {
$outer_where = " AND " . $matches[0];
$where = preg_replace("/P\.USER_ID=[\'\"]\d+[\'\"]/", "",
$where);
/* above could result in two ANDs */
$where = preg_replace('/AND\s+AND/ui', "AND", $where);
}
$group_by = " GROUP BY GI.PARENT_ID";
$order_by = " ORDER BY E.PUBDATE DESC ";
$select = "SELECT E.*, I.TITLE AS TITLE,
I.DESCRIPTION AS DESCRIPTION, I.FLAG AS FLAG,
I.USER_ID AS USER_ID, II.USER_ID AS LAST_POSTER_ID,
U.USER_NAME AS USER_NAME, P.USER_NAME AS LAST_POSTER,
COALESCE(IIS.NUM_VIEWS, 0) AS NUM_VIEWS,
COALESCE(IIS.FUZZY_NUM_VIEWS, 0) AS FUZZY_NUM_VIEWS,
COALESCE(IIS.TMP_NUM_VIEWS, 0) AS TMP_NUM_VIEWS";
$sub_select = "SELECT DISTINCT MIN(GI.ID) AS ID,
MAX(GI.ID) AS LAST_ID,
COUNT(DISTINCT GI.ID) AS NUM_POSTS, GI.PARENT_ID AS PARENT_ID,
MIN(GI.GROUP_ID) AS GROUP_ID, MAX(GI.PUBDATE) AS PUBDATE,
MIN(G.OWNER_ID) AS OWNER_ID,
MIN(G.MEMBER_ACCESS) AS MEMBER_ACCESS,
MIN(G.GROUP_NAME) AS GROUP_NAME,
MIN(GI.PUBDATE) AS RECENT_DATE,
MIN(GI.TYPE) AS TYPE";
$sub_sql = "$sub_select
FROM GROUP_ITEM GI, SOCIAL_GROUPS G, USER_GROUP UG
$where $group_by";
$sql = "$select FROM ($sub_sql) E JOIN GROUP_ITEM I
ON E.ID = I.ID
JOIN GROUP_ITEM II ON E.LAST_ID = II.ID
JOIN USERS U ON I.USER_ID = U.USER_ID
JOIN USERS P ON II.USER_ID = P.USER_ID
LEFT OUTER JOIN ITEM_IMPRESSION_SUMMARY IIS ON
IIS.ITEM_ID = E.PARENT_ID AND IIS.ITEM_TYPE=" .
C\THREAD_IMPRESSION . " AND IIS.USER_ID=" .
C\PUBLIC_USER_ID . " AND
IIS.ITEM_ID = E.PARENT_ID AND
IIS.UPDATE_PERIOD=" . C\FOREVER .
" $outer_where $order_by $limit";
} else {
$where .= " AND P.USER_ID = GI.USER_ID ";
$select = "SELECT DISTINCT GI.ID AS ID,
GI.PARENT_ID AS PARENT_ID, GI.GROUP_ID AS GROUP_ID,
GI.TITLE AS TITLE, GI.DESCRIPTION AS DESCRIPTION,
GI.FLAG AS FLAG, GI.PUBDATE AS PUBDATE,
GI.EDIT_DATE AS EDIT_DATE, G.OWNER_ID AS OWNER_ID,
G.MEMBER_ACCESS AS MEMBER_ACCESS,
G.GROUP_NAME AS GROUP_NAME, P.USER_NAME AS USER_NAME,
P.USER_ID AS USER_ID, GI.TYPE AS TYPE, GI.UPS AS UPS,
GI.DOWNS AS DOWNS, G.VOTE_ACCESS AS VOTE_ACCESS ";
$sql = "$select
FROM GROUP_ITEM GI, SOCIAL_GROUPS G, USER_GROUP UG, USERS P
$where $order_by $limit";
}
$result = $db->execute($sql);
$i = 0;
$read_only = ($user_id == C\PUBLIC_GROUP_ID);
if ($read_only) {
while ($groups[$i] = $db->fetchArray($result)) {
$groups[$i]["MEMBER_ACCESS"] = C\GROUP_READ;
$i++;
}
} else {
while ($groups[$i] = $db->fetchArray($result)) {
$i++;
}
}
unset($groups[$i]); /* last one will be null */
$i = 0;
foreach ($groups as $group_key => $group_value) {
if ($this->isGroupEncrypted($group_value['GROUP_ID'])) {
$keys = $this->getGroupKey($group_value['GROUP_ID']);
$groups[$i]['TITLE'] = $this->decrypt(
$group_value['TITLE'], $keys);
$groups[$i]['DESCRIPTION'] = $this->decrypt(
$group_value['DESCRIPTION'], $keys);
}
$i++;
}
return $groups;
}
/**
* getGroupItemCount gets the number of group feed items visible to a user
* with $user_id and which match the supplied search criteria found in
* $search_array
* @param array $search_array each element of this is a quadruple name of a
* field, what comparison to perform, a value to check, and an order
* (ascending/descending) to sort by
* @param int $user_id who is making this request to determine which
* @param int $for_group if this value is set it is a assumed that
* group_items are being returned for only one group and that the count
* desired is over the number of threads in that group
* @return int number of items matching the search criteria for the given
* user_id
*/
public function getGroupItemCount($search_array = [], $user_id = C\ROOT_ID,
$for_group = -1)
{
$db = $this->db;
$any_fields = ["access", "register"];
$is_thread = false;
foreach ($search_array as $search_item) {
if ($search_item[0] == 'parent_id') {
if ($search_item[2] > 0) {
$is_thread = true;
}
break;
}
}
list($where, $order_by) =
$this->searchArrayToWhereOrderClauses($search_array, $any_fields);
$add_where = " WHERE ";
if ($where != "") {
$add_where = " AND ";
}
$user_id = $db->escapeString($user_id);
if ($for_group > 0 || $for_group == -2) { /* -2 is just_thread case */
$non_public_where = " (UG.USER_ID='$user_id' OR ".
" G.REGISTER_TYPE IN ('".C\PUBLIC_JOIN."','".
C\PUBLIC_BROWSE_REQUEST_JOIN."') ) AND ";
if (!$is_thread) {
$non_public_where .=
"TYPE = " . C\STANDARD_GROUP_ITEM. " AND ";
}
} else {
$non_public_where = " UG.USER_ID='$user_id' AND ";
}
$non_public_status = ($user_id != C\PUBLIC_GROUP_ID) ?
" UG.STATUS='" . C\ACTIVE_STATUS."' AND " : "";
$where .= $add_where. $non_public_where .
"GI.USER_ID=O.USER_ID AND
GI.GROUP_ID=G.GROUP_ID AND GI.GROUP_ID=UG.GROUP_ID AND ((
$non_public_status
G.MEMBER_ACCESS IN ('".C\GROUP_READ."','".C\GROUP_READ_COMMENT.
"','".C\GROUP_READ_WRITE."', '" . C\GROUP_READ_WIKI . "')) OR
(G.OWNER_ID = UG.USER_ID OR UG.USER_ID = '".C\ROOT_ID."'))";
if ($for_group >= 0) {
$count_col = " COUNT(DISTINCT GI.PARENT_ID) ";
} else {
$count_col = " COUNT(DISTINCT GI.ID) ";
}
$sql = "SELECT $count_col AS NUM FROM GROUP_ITEM GI, SOCIAL_GROUPS G,
USER_GROUP UG, USERS O $where";
$result = $db->execute($sql);
$row = $db->fetchArray($result);
return $row['NUM'] ?? false;
}
/**
* getMostRecentGroupPost returns the most recent post posted to a group
* @param int $group_id id of the group to get the most recent post for
* @return array associate array of post details
*/
public function getMostRecentGroupPost($group_id)
{
$db = $this->db;
$sql = "SELECT MAX(GI.PUBDATE) AS PUBDATE
FROM GROUP_ITEM GI
WHERE GI.GROUP_ID = ?" .
$this->excludeGitIssueThreadsClause("GI");
$result = $db->execute($sql, [$group_id]);
if (empty($result)) {
return "";
}
$row = $db->fetchArray($result);
if (empty($row) || empty($row["PUBDATE"])) {
return "";
}
$sql = "SELECT DISTINCT GI.ID AS ID, GI.PARENT_ID AS PARENT_ID,
GI.GROUP_ID AS GROUP_ID, GI.TITLE AS TITLE,
GI.DESCRIPTION AS DESCRIPTION, GI.PUBDATE AS PUBDATE,
GI.EDIT_DATE AS EDIT_DATE, G.OWNER_ID AS OWNER_ID,
G.MEMBER_ACCESS AS MEMBER_ACCESS, G.GROUP_NAME AS GROUP_NAME,
P.USER_NAME AS USER_NAME, P.USER_ID AS USER_ID, GI.TYPE AS TYPE,
GI.UPS AS UPS, GI.DOWNS AS DOWNS, G.VOTE_ACCESS AS VOTE_ACCESS
FROM GROUP_ITEM GI, SOCIAL_GROUPS G, USERS P
WHERE GI.GROUP_ID = ? AND GI.GROUP_ID=G.GROUP_ID AND
GI.USER_ID = P.USER_ID AND GI.PUBDATE = ?" .
$this->excludeGitIssueThreadsClause("GI") . " " .
$db->limitOffset(0, 1);
$result = $db->execute($sql, [$group_id, $row['PUBDATE']]);
if (empty($result)) {
return "";
}
$row = $db->fetchArray($result);
if (!empty($group_id) &&
$this->isGroupEncrypted($group_id)) {
/* Decrypt group's title and description */
$key = $this->getGroupKey($group_id);
$row['TITLE'] = $this->decrypt($row['TITLE'], $key);
$row['DESCRIPTION'] = $this->decrypt($row['DESCRIPTION'], $key);
}
return $row;
}
/**
* getGroupThreadCount returns the number of distinct threads in a group's
* feed
* @param int $group_id id of the group to get thread count for
* @return int number of threads
*/
public function getGroupThreadCount($group_id)
{
$db = $this->db;
$sql = "SELECT COUNT(GI.PARENT_ID) AS NUM
FROM GROUP_ITEM GI
WHERE GI.GROUP_ID = ? AND GI.ID = GI.PARENT_ID" .
$this->excludeGitIssueThreadsClause("GI");
$result = $db->execute($sql, [$group_id]);
if (!$result) {
return 0;
}
$row = $db->fetchArray($result);
return $row['NUM'] ?? 0;
}
/**
* getUserPostCount returns the number of posts to groups that a user
* belongs to since a timestamp
* @param int $user_id id of the user to get posts for
* @param int $timestamp only post with value pubdate greater than this will
* be counted
* @return int number of posts
*/
public function getUserPostCount($user_id, $timestamp = 0)
{
$db = $this->db;
$subselect = "(SELECT GROUP_ID " .
"FROM USER_GROUP WHERE USER_ID = ? AND STATUS = ".
C\ACTIVE_STATUS . ")";
$sql = "SELECT COUNT(GI.ID) AS NUM FROM GROUP_ITEM GI ".
"WHERE GI.GROUP_ID IN $subselect AND GI.TITLE NOT LIKE ".
"? AND GI.PUBDATE > ?";
if (empty($user_id)) {
return 0;
}
$result = $db->execute($sql, [$user_id, "%$user_id%", $timestamp]);
if (!$result) {
return 0;
}
$row = $db->fetchArray($result);
return $row['NUM'] ?? 0;
}
/**
* getGroupPostCount returns the number of posts to a group
* @param int $group_id id of the group to get post count for
* @param int $timestamp only post with value pubdate greater or EDIT_DATE
* than this will be counted
* @return int number of posts
*/
public function getGroupPostCount($group_id, $timestamp = 0)
{
$db = $this->db;
$sql = "SELECT COUNT(GI.ID) AS NUM FROM GROUP_ITEM GI
WHERE GI.GROUP_ID = ? AND GI.PUBDATE > ?" .
$this->excludeGitIssueThreadsClause("GI");
$result = $db->execute($sql, [$group_id, $timestamp]);
if (!$result) {
return 0;
}
$row = $db->fetchArray($result);
return $row['NUM'] ?? 0;
}
/**
* excludeGitIssueThreadsClause gives a SQL fragment that leaves out the
* discussion threads belonging to git issue companion pages, whose titles
* carry the reserved issue separator, so those issues are not counted or
* shown as ordinary group posts in feeds, in a group's post and thread
* counts, or as a group's most recent post. The check that the thread is
* set keeps the NOT IN test from wrongly matching when a page has no
* thread.
* @param string $alias the table alias used for GROUP_ITEM in the query
* @return string the fragment to add to a WHERE clause
*/
private function excludeGitIssueThreadsClause($alias)
{
return " AND " . $alias . ".PARENT_ID NOT IN (SELECT DISCUSS_THREAD" .
" FROM GROUP_PAGE WHERE TITLE LIKE '%" . C\GIT_ISSUE_SEPARATOR .
"%' AND DISCUSS_THREAD IS NOT NULL)";
}
/**
* getFeedClipboardId get the id of the clipboard thread used for moving
* around group items for a given user.
* @param int $user_id id of user to get the clipboard thread for
* @return int id of thread
*/
public function getFeedClipboardId($user_id)
{
if (($clip_group_id = $this->getPersonalGroupId($user_id)) < 0) {
return false;
}
$clip_title = C\PERSONAL_GROUP_PREFIX . "_clipboard_" . $user_id;
$db = $this->db;
$sql = "SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND " .
"TITLE=? " . $db->limitOffset(1);
$result = $db->execute($sql, [$clip_group_id, $clip_title]);
if (!empty($result)) {
$row = $db->fetchArray($result);
if (!empty($row)) {
return $row["PARENT_ID"];
}
}
return $this->addGroupItem(0, $clip_group_id, $user_id, $clip_title,
"start_feed_clip");
}
/**
* getMessagesThreadTitle messages use the same storage mechanism as group
* posts, so need a title, The title used is always computed as the `-`
* concatenation of the sorted id's of the participants in the message
* session. This method computes such a title string from an array of
* message participants.
* @param array $thread_follower_ids message chat participants
* @return string appropriate title of chat thread.
*/
public function getMessagesThreadTitle($thread_follower_ids)
{
sort($thread_follower_ids);
return implode("-", $thread_follower_ids);
}
/**
* getThreadPostCount returns the number of posts to a thread
* @param int $thread_id id of the thread to get post count for
* @param int $timestamp only post with value pubdate greater or EDIT_DATE
* than this will be counted
* @return int number of posts
*/
public function getThreadPostCount($thread_id, $timestamp = 0)
{
$db = $this->db;
$sql = "SELECT COUNT(GI.ID) AS NUM FROM GROUP_ITEM GI
WHERE GI.PARENT_ID = ? AND GI.PUBDATE > ?";
$result = $db->execute($sql, [$thread_id, $timestamp]);
if (!$result) {
return 0;
}
$row = $db->fetchArray($result);
return $row['NUM'] ?? 0;
}
/**
* cullExpiredGroupItems deletes Group Items which are older than the expiry
* date for posts for that group
*/
public function cullExpiredGroupItems()
{
$time = time();
/* nesting SELECTs to keep Mysql from giving a Error 1093 */
$sql = "DELETE FROM GROUP_ITEM WHERE ID IN (
SELECT ID FROM (
SELECT GI.ID AS ID FROM GROUP_ITEM GI, SOCIAL_GROUPS G
WHERE GI.GROUP_ID=G.GROUP_ID AND G.POST_LIFETIME > 0
AND ($time - GI.PUBDATE) > G.POST_LIFETIME) AS C)";
$this->db->execute($sql);
}
/**
* getPageInfoByThread returns the group_id, language, page name, last
* modified date of a wiki pagecorresponding to a page discussion thread
* with id $page_thread_id
* @param int $page_thread_id the id of a wiki page discussion thread to
* look up page info for
* @return array (group_id, language, and page name) of that wiki page
*/
public function getPageInfoByThread($page_thread_id)
{
$db = $this->db;
$sql = "SELECT GROUP_ID, LOCALE_TAG, TITLE AS PAGE_NAME,
LAST_MODIFIED FROM GROUP_PAGE WHERE DISCUSS_THREAD = ?";
$result = $db->execute($sql, [$page_thread_id]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (!$row) {
return false;
}
return $row;
}
/**
* copyThreadResources copies resources associated with a thread post in one
* group to the resource folders associated with a different group and
* thread id
* @param int $from_group_id group resources are coming from
* @param int $from_thread_id thread post to get resources from
* @param int $to_group_id group that we are copying to
* @param int $to_thread_id resource folder of this thread post will be
* copied to
*/
public function copyThreadResources($from_group_id, $from_thread_id,
$to_group_id, $to_thread_id)
{
$from_folders = $this->getGroupPageResourcesFolders($from_group_id,
"post" . $from_thread_id);
list($from_folder, $from_thumb_folder, $from_base_folder, ) =
$from_folders;
$to_folders = $this->getGroupPageResourcesFolders($to_group_id,
"post" . $to_thread_id, "", true);
list($to_folder, $to_thumb_folder, $to_base_folder, ) = $to_folders;
$this->db->copyRecursive($from_folder, $to_folder);
$this->db->copyRecursive($from_thumb_folder, $to_thumb_folder);
}
/**
* getGitIssueThread reads the discussion thread that holds an issue's
* comments: the id of the thread and every reply posted to it, in the order
* they were made. The thread's opening post is left out because the issue's
* description stands in for it on the detail page.
* @param int $group_id id of the group the issue belongs to
* @param string $page_name name of the git repository wiki page
* @param int $issue_number which issue on that page
* @param string $locale_tag language the page is written for
* @return array a pair of the thread id and a list of its reply posts, each
* with an id, a poster id and name, a time, and a body
*/
public function getGitIssueThread($group_id, $page_name, $issue_number,
$locale_tag)
{
$issue_page_name = $page_name . C\GIT_ISSUE_SEPARATOR .
$issue_number;
$info = $this->getPageInfoByName($group_id, $issue_page_name,
$locale_tag, "edit");
$thread_id = (int)($info["DISCUSS_THREAD"] ?? 0);
if ($thread_id <= 0) {
return [0, []];
}
$db = $this->db;
$sql = "SELECT GI.ID AS ID, GI.USER_ID AS USER_ID,
GI.PUBDATE AS PUBDATE, GI.DESCRIPTION AS DESCRIPTION,
U.USER_NAME AS USER_NAME
FROM GROUP_ITEM GI, USERS U
WHERE GI.PARENT_ID = ? AND GI.ID <> ? AND GI.USER_ID = U.USER_ID
ORDER BY GI.PUBDATE ASC";
$result = $db->execute($sql, [$thread_id, $thread_id]);
$posts = [];
while ($result && $row = $db->fetchArray($result)) {
$posts[] = $row;
}
return [$thread_id, $posts];
}
}
(c) 2026 Seekquarry - Open Source Search Engine Software