/**
* Get the notification type id from the name
*
* @param string $notification_type_name The name
* @return int the notification_type_id
* @throws \phpbb\notification\exception
*/
public function get_notification_type_id($notification_type_name)
{
$notification_type_ids = $this->cache->get('notification_type_ids');
$this->db->sql_transaction('begin');
if ($notification_type_ids === false) {
$notification_type_ids = array();
$sql = 'SELECT notification_type_id, notification_type_name
FROM ' . $this->notification_types_table;
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$notification_type_ids[$row['notification_type_name']] = (int) $row['notification_type_id'];
}
$this->db->sql_freeresult($result);
$this->cache->put('notification_type_ids', $notification_type_ids);
}
if (!isset($notification_type_ids[$notification_type_name])) {
if (!isset($this->notification_types[$notification_type_name]) && !isset($this->notification_types['notification.type.' . $notification_type_name])) {
throw new \phpbb\notification\exception('NOTIFICATION_TYPE_NOT_EXIST', array($notification_type_name));
}
$sql = 'INSERT INTO ' . $this->notification_types_table . ' ' . $this->db->sql_build_array('INSERT', array('notification_type_name' => $notification_type_name, 'notification_type_enabled' => 1));
$this->db->sql_query($sql);
$notification_type_ids[$notification_type_name] = (int) $this->db->sql_nextid();
$this->cache->put('notification_type_ids', $notification_type_ids);
}
$this->db->sql_transaction('commit');
return $notification_type_ids[$notification_type_name];
}
/**
* {@inheritDoc}
*/
public function move($group_id, $delta)
{
$delta = (int) $delta;
if (!$delta) {
return false;
}
$move_up = $delta > 0 ? true : false;
$current_value = $this->get_group_value($group_id);
if ($current_value != self::GROUP_DISABLED) {
$this->db->sql_transaction('begin');
// First we move all groups between our current value and the target value up/down 1,
// so we have a gap for our group to move.
$sql = 'UPDATE ' . GROUPS_TABLE . '
SET group_legend = group_legend' . ($move_up ? ' + 1' : ' - 1') . '
WHERE group_legend > ' . self::GROUP_DISABLED . '
AND group_legend' . ($move_up ? ' >= ' : ' <= ') . ($current_value - $delta) . '
AND group_legend' . ($move_up ? ' < ' : ' > ') . $current_value;
$this->db->sql_query($sql);
// Because there might be fewer groups above/below the group than we wanted to move,
// we use the number of changed groups, to update the group.
$delta = (int) $this->db->sql_affectedrows();
if ($delta) {
// And now finally, when we moved some other groups and built a gap,
// we can move the desired group to it.
$sql = 'UPDATE ' . GROUPS_TABLE . '
SET group_legend = group_legend ' . ($move_up ? ' - ' : ' + ') . $delta . '
WHERE group_id = ' . (int) $group_id;
$this->db->sql_query($sql);
$this->db->sql_transaction('commit');
return true;
}
$this->db->sql_transaction('commit');
}
return false;
}
/**
* Delete category content
*
* @return array
*/
private function _delete_cat_content()
{
$this->db->sql_transaction('begin');
// Before we remove anything we make sure we are able to adjust the post counts later. ;)
$sql = 'SELECT link_id, link_banner
FROM ' . DIR_LINK_TABLE . '
WHERE link_cat = ' . (int) $this->cat_id;
$result = $this->db->sql_query($sql);
$link_ids = array();
while ($row = $this->db->sql_fetchrow($result)) {
$link_ids[] = $row['link_id'];
if ($row['link_banner'] && !preg_match('/^(http:\\/\\/|https:\\/\\/|ftp:\\/\\/|ftps:\\/\\/|www\\.).+/si', $row['link_banner'])) {
$banner_img = $this->dir_helper->get_banner_path(basename($row['link_banner']));
if (file_exists($banner_img)) {
@unlink($banner_img);
}
}
}
$this->db->sql_freeresult($result);
if (sizeof($link_ids)) {
// Delete links datas
$link_datas_ary = array(DIR_COMMENT_TABLE => 'comment_link_id', DIR_VOTE_TABLE => 'vote_link_id');
foreach ($link_datas_ary as $table => $field) {
$this->db->sql_query("DELETE FROM {$table} WHERE " . $this->db->sql_in_set($field, $link_ids));
}
}
// Delete cats datas
$cat_datas_ary = array(DIR_LINK_TABLE => 'link_cat', DIR_WATCH_TABLE => 'cat_id');
foreach ($cat_datas_ary as $table => $field) {
$this->db->sql_query("DELETE FROM {$table} WHERE {$field} = " . (int) $this->cat_id);
}
$this->db->sql_transaction('commit');
return array();
}
/**
* Update the similar topics columns in the forums table
*
* @param string $column The name of the column to update
* @param array $forum_ids An array of forum_ids
*/
protected function update_forum($column, $forum_ids)
{
$this->db->sql_transaction('begin');
// Set marked forums (in set) to 1
$sql = 'UPDATE ' . FORUMS_TABLE . "\n\t\t\tSET {$column} = 1\n\t\t\tWHERE " . $this->db->sql_in_set('forum_id', $forum_ids, false, true);
$this->db->sql_query($sql);
// Set unmarked forums (not in set) to 0
$sql = 'UPDATE ' . FORUMS_TABLE . "\n\t\t\tSET {$column} = 0\n\t\t\tWHERE " . $this->db->sql_in_set('forum_id', $forum_ids, true, true);
$this->db->sql_query($sql);
$this->db->sql_transaction('commit');
}
/**
* Delete a comment
*
* @param string $link_id The link ID
* @param string $comment_id The comment ID
* @return null
*/
public function del($link_id, $comment_id)
{
global $request;
$this->db->sql_transaction('begin');
$sql = 'DELETE FROM ' . DIR_COMMENT_TABLE . ' WHERE comment_id = ' . (int) $comment_id;
$this->db->sql_query($sql);
$sql = 'UPDATE ' . DIR_LINK_TABLE . '
SET link_comment = link_comment - 1
WHERE link_id = ' . (int) $link_id;
$this->db->sql_query($sql);
$this->db->sql_transaction('commit');
if ($request->is_ajax()) {
$sql = 'SELECT COUNT(comment_id) AS nb_comments
FROM ' . DIR_COMMENT_TABLE . '
WHERE comment_link_id = ' . (int) $link_id;
$result = $this->db->sql_query($sql);
$nb_comments = (int) $this->db->sql_fetchfield('nb_comments');
$this->db->sql_freeresult($result);
$json_response = new \phpbb\json_response();
$json_response->send(array('success' => true, 'MESSAGE_TITLE' => $this->user->lang['INFORMATION'], 'MESSAGE_TEXT' => $this->user->lang['DIR_COMMENT_DELETE_OK'], 'COMMENT_ID' => $comment_id, 'TOTAL_COMMENTS' => $this->user->lang('DIR_NB_COMMS', $nb_comments)));
}
}
/**
* Mass set configuration options: Receives an associative array,
* treats array keys as configuration option names and associated
* array values as their configuration option values.
*
* @param array $map Map from configuration names to values
*
* @return null
*/
public function set_array(array $map)
{
$this->db->sql_transaction('begin');
foreach ($map as $key => $value) {
$sql = 'UPDATE ' . $this->table . "\n\t\t\t\tSET config_value = '" . $this->db->sql_escape($value) . "'\n\t\t\t\tWHERE config_name = '" . $this->db->sql_escape($key) . "'";
$result = $this->db->sql_query($sql);
if (!$this->db->sql_affectedrows($result)) {
$sql = 'INSERT INTO ' . $this->table . ' ' . $this->db->sql_build_array('INSERT', array('config_name' => (string) $key, 'config_value' => (string) $value));
$this->db->sql_query($sql);
}
}
$this->db->sql_transaction('commit');
}
/**
* Regenerate left/right ids from parent/child relationship
*
* This method regenerates the left/right ids for the tree based on
* the parent/child relations. This function executes three queries per
* item, so it should only be called, when the set has one of the following
* problems:
* - The set has a duplicated value inside the left/right id chain
* - The set has a missing value inside the left/right id chain
* - The set has items that do not have a left/right id set
*
* When regenerating the items, the items are sorted by parent id and their
* current left id, so the current child/parent relationships are kept
* and running the function on a working set will not change the order.
*
* @param int $new_id First left_id to be used (should start with 1)
* @param int $parent_id parent_id of the current set (default = 0)
* @param bool $reset_ids Should we reset all left_id/right_id on the first call?
* @return int $new_id The next left_id/right_id that should be used
*/
public function regenerate_left_right_ids($new_id, $parent_id = 0, $reset_ids = false)
{
if ($acquired_new_lock = $this->acquire_lock()) {
$this->db->sql_transaction('begin');
if (!$reset_ids) {
$sql = 'UPDATE ' . $this->table_name . '
SET ' . $this->column_item_parents . " = ''\n\t\t\t\t\t" . $this->get_sql_where('WHERE');
$this->db->sql_query($sql);
}
}
if ($reset_ids) {
$sql = 'UPDATE ' . $this->table_name . '
SET ' . $this->db->sql_build_array('UPDATE', array($this->column_left_id => 0, $this->column_right_id => 0, $this->column_item_parents => '')) . '
' . $this->get_sql_where('WHERE');
$this->db->sql_query($sql);
}
$sql = 'SELECT *
FROM ' . $this->table_name . '
WHERE ' . $this->column_parent_id . ' = ' . (int) $parent_id . '
' . $this->get_sql_where('AND') . '
ORDER BY ' . $this->column_left_id . ', ' . $this->column_item_id . ' ASC';
$result = $this->db->sql_query($sql);
$rows = $this->db->sql_fetchrowset($result);
$this->db->sql_freeresult($result);
foreach ($rows as $row) {
// First we update the left_id for this module
if ($row[$this->column_left_id] != $new_id) {
$sql = 'UPDATE ' . $this->table_name . '
SET ' . $this->db->sql_build_array('UPDATE', array($this->column_left_id => $new_id)) . '
WHERE ' . $this->column_item_id . ' = ' . (int) $row[$this->column_item_id];
$this->db->sql_query($sql);
}
$new_id++;
// Then we go through any children and update their left/right id's
$new_id = $this->regenerate_left_right_ids($new_id, $row[$this->column_item_id]);
// Then we come back and update the right_id for this module
if ($row[$this->column_right_id] != $new_id) {
$sql = 'UPDATE ' . $this->table_name . '
SET ' . $this->db->sql_build_array('UPDATE', array($this->column_right_id => $new_id)) . '
WHERE ' . $this->column_item_id . ' = ' . (int) $row[$this->column_item_id];
$this->db->sql_query($sql);
}
$new_id++;
}
if ($acquired_new_lock) {
$this->db->sql_transaction('commit');
$this->lock->release();
}
return $new_id;
}
/**
* Store user input
*
* Event: core.acp_manage_forums_request_data
*/
public function acp_manage_forums_request_data($event)
{
$sort_topics_by = $this->request->variable('sk', $this->default_sort_by);
$sort_topics_order = $this->request->variable('sd', $this->default_sort_order);
$sort_topics_subforums = $this->request->variable('sort_topics_subforums', 0);
$event['forum_data'] = array_merge($event['forum_data'], array('sort_topics_by' => $sort_topics_by, 'sort_topics_order' => $sort_topics_order));
// Apply this forum's sorting to all sub-forums
if ($sort_topics_subforums) {
$subforum_ids = array();
foreach (get_forum_branch($event['forum_data']['forum_id'], 'children', 'descending', false) as $subforum) {
$subforum_ids[] = (int) $subforum['forum_id'];
}
if (!empty($subforum_ids)) {
$this->db->sql_transaction('begin');
foreach ($subforum_ids as $subforum_id) {
$sql_ary = 'UPDATE ' . FORUMS_TABLE . '
SET ' . sprintf("sort_topics_by = '%s', sort_topics_order = '%s'", $sort_topics_by, $sort_topics_order) . '
WHERE forum_id = ' . (int) $subforum_id;
$this->db->sql_query($sql_ary);
}
$this->db->sql_transaction('commit');
}
}
}
/**
* Install style
*
* @param array $style style data
* @return int Style id
*/
protected function install_style($style)
{
// Generate row
$sql_ary = array();
foreach ($style as $key => $value) {
if ($key != 'style_id' && substr($key, 0, 1) != '_') {
$sql_ary[$key] = $value;
}
}
// Add to database
$this->db->sql_transaction('begin');
$sql = 'INSERT INTO ' . STYLES_TABLE . '
' . $this->db->sql_build_array('INSERT', $sql_ary);
$this->db->sql_query($sql);
$id = $this->db->sql_nextid();
$this->db->sql_transaction('commit');
add_log('admin', 'LOG_STYLE_ADD', $sql_ary['style_name']);
return $id;
}
/**
* Wrapper for running queries to generate user feedback on updates
*
* @param string $sql SQL query to run on the database
* @return mixed Query result from db->sql_query()
*/
protected function sql_query($sql)
{
$this->queries[] = $sql;
$this->db->sql_return_on_error(true);
if ($sql === 'begin') {
$result = $this->db->sql_transaction('begin');
} else {
if ($sql === 'commit') {
$result = $this->db->sql_transaction('commit');
} else {
$result = $this->db->sql_query($sql);
if ($this->db->get_sql_error_triggered()) {
$this->errors[] = array('sql' => $this->db->get_sql_error_sql(), 'code' => $this->db->get_sql_error_returned());
}
}
}
$this->db->sql_return_on_error(false);
return $result;
}
/**
* Re-clean user names
* Only user names that are unclean will be re-cleaned
*
* @param int $start An offset index
* @return bool|int Return the next offset index or true if all records have been processed.
*/
protected function reclean_usernames($start = 0)
{
$limit = 500;
$i = 0;
$this->db->sql_transaction('begin');
$sql = 'SELECT user_id, username, username_clean FROM ' . USERS_TABLE;
$result = $this->db->sql_query_limit($sql, $limit, $start);
while ($row = $this->db->sql_fetchrow($result)) {
$i++;
$username_clean = $this->db->sql_escape(utf8_clean_string($row['username']));
if ($username_clean != $row['username_clean']) {
$sql = 'UPDATE ' . USERS_TABLE . "\n\t\t\t\t\tSET username_clean = '{$username_clean}'\n\t\t\t\t\tWHERE user_id = {$row['user_id']}";
$this->db->sql_query($sql);
$this->processed++;
}
$this->progress->advance();
}
$this->db->sql_freeresult($result);
$this->db->sql_transaction('commit');
return $i < $limit ? true : $start + $i;
}
/**
* Add a vote in db, for a specifi link
*
* @param int $link_id Link_id from db
* @return null
*/
public function add_vote($link_id)
{
$data = array('vote_link_id' => (int) $link_id, 'vote_user_id' => $this->user->data['user_id'], 'vote_note' => $this->request->variable('vote', 0));
$this->db->sql_transaction('begin');
$sql = 'INSERT INTO ' . DIR_VOTE_TABLE . ' ' . $this->db->sql_build_array('INSERT', $data);
$this->db->sql_query($sql);
$sql = 'UPDATE ' . DIR_LINK_TABLE . '
SET link_vote = link_vote + 1,
link_note = link_note + ' . (int) $data['vote_note'] . '
WHERE link_id = ' . (int) $link_id;
$this->db->sql_query($sql);
$this->db->sql_transaction('commit');
if ($this->request->is_ajax()) {
$sql = 'SELECT link_vote, link_note FROM ' . DIR_LINK_TABLE . ' WHERE link_id = ' . (int) $link_id;
$result = $this->db->sql_query($sql);
$data = $this->db->sql_fetchrow($result);
$note = $this->display_note($data['link_note'], $data['link_vote'], true);
$json_response = new \phpbb\json_response();
$json_response->send(array('success' => true, 'MESSAGE_TITLE' => $this->user->lang['INFORMATION'], 'MESSAGE_TEXT' => $this->user->lang['DIR_VOTE_OK'], 'NOTE' => $note, 'NB_VOTE' => $this->user->lang('DIR_NB_VOTES', (int) $data['link_vote']), 'LINK_ID' => $link_id));
}
}
/**
* Resynchronize the Custom BBCodes order field
* (Based on Custom BBCode Sorting MOD by RMcGirr83)
*
* @return null
* @access public
*/
public function resynchronize_bbcode_order()
{
$this->db->sql_transaction('begin');
// By default, check that order is valid and fix it if necessary
$sql = 'SELECT bbcode_id, bbcode_order
FROM ' . BBCODES_TABLE . '
ORDER BY bbcode_order, bbcode_id';
$result = $this->db->sql_query($sql);
if ($row = $this->db->sql_fetchrow($result)) {
$order = 0;
do {
// pre-increment $order
++$order;
if ($row['bbcode_order'] != $order) {
$sql = 'UPDATE ' . BBCODES_TABLE . "\n\t\t\t\t\t\tSET bbcode_order = {$order}\n\t\t\t\t\t\tWHERE bbcode_id = {$row['bbcode_id']}";
$this->db->sql_query($sql);
}
} while ($row = $this->db->sql_fetchrow($result));
}
$this->db->sql_freeresult($result);
$this->db->sql_transaction('commit');
}
/**
* Updates wordlist and wordmatch tables when a message is posted or changed
*
* @param string $mode Contains the post mode: edit, post, reply, quote
* @param int $post_id The id of the post which is modified/created
* @param string &$message New or updated post content
* @param string &$subject New or updated post subject
* @param int $poster_id Post author's user id
* @param int $forum_id The id of the forum in which the post is located
*/
public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id)
{
if (!$this->config['fulltext_native_load_upd'])
{
/**
* The search indexer is disabled, return
*/
return;
}
// Split old and new post/subject to obtain array of 'words'
$split_text = $this->split_message($message);
$split_title = $this->split_message($subject);
$cur_words = array('post' => array(), 'title' => array());
$words = array();
if ($mode == 'edit')
{
$words['add']['post'] = array();
$words['add']['title'] = array();
$words['del']['post'] = array();
$words['del']['title'] = array();
$sql = 'SELECT w.word_id, w.word_text, m.title_match
FROM ' . SEARCH_WORDLIST_TABLE . ' w, ' . SEARCH_WORDMATCH_TABLE . " m
WHERE m.post_id = $post_id
AND w.word_id = m.word_id";
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result))
{
$which = ($row['title_match']) ? 'title' : 'post';
$cur_words[$which][$row['word_text']] = $row['word_id'];
}
$this->db->sql_freeresult($result);
$words['add']['post'] = array_diff($split_text, array_keys($cur_words['post']));
$words['add']['title'] = array_diff($split_title, array_keys($cur_words['title']));
$words['del']['post'] = array_diff(array_keys($cur_words['post']), $split_text);
$words['del']['title'] = array_diff(array_keys($cur_words['title']), $split_title);
}
else
{
$words['add']['post'] = $split_text;
$words['add']['title'] = $split_title;
$words['del']['post'] = array();
$words['del']['title'] = array();
}
unset($split_text);
unset($split_title);
// Get unique words from the above arrays
$unique_add_words = array_unique(array_merge($words['add']['post'], $words['add']['title']));
// We now have unique arrays of all words to be added and removed and
// individual arrays of added and removed words for text and title. What
// we need to do now is add the new words (if they don't already exist)
// and then add (or remove) matches between the words and this post
if (sizeof($unique_add_words))
{
$sql = 'SELECT word_id, word_text
FROM ' . SEARCH_WORDLIST_TABLE . '
WHERE ' . $this->db->sql_in_set('word_text', $unique_add_words);
$result = $this->db->sql_query($sql);
$word_ids = array();
while ($row = $this->db->sql_fetchrow($result))
{
$word_ids[$row['word_text']] = $row['word_id'];
}
$this->db->sql_freeresult($result);
$new_words = array_diff($unique_add_words, array_keys($word_ids));
$this->db->sql_transaction('begin');
if (sizeof($new_words))
{
$sql_ary = array();
foreach ($new_words as $word)
{
$sql_ary[] = array('word_text' => (string) $word, 'word_count' => 0);
}
$this->db->sql_return_on_error(true);
$this->db->sql_multi_insert(SEARCH_WORDLIST_TABLE, $sql_ary);
$this->db->sql_return_on_error(false);
}
unset($new_words, $sql_ary);
}
else
{
$this->db->sql_transaction('begin');
}
// now update the search match table, remove links to removed words and add links to new words
foreach ($words['del'] as $word_in => $word_ary)
{
$title_match = ($word_in == 'title') ? 1 : 0;
if (sizeof($word_ary))
{
$sql_in = array();
foreach ($word_ary as $word)
{
$sql_in[] = $cur_words[$word_in][$word];
}
$sql = 'DELETE FROM ' . SEARCH_WORDMATCH_TABLE . '
WHERE ' . $this->db->sql_in_set('word_id', $sql_in) . '
AND post_id = ' . intval($post_id) . "
AND title_match = $title_match";
$this->db->sql_query($sql);
$sql = 'UPDATE ' . SEARCH_WORDLIST_TABLE . '
SET word_count = word_count - 1
WHERE ' . $this->db->sql_in_set('word_id', $sql_in) . '
AND word_count > 0';
$this->db->sql_query($sql);
unset($sql_in);
}
}
$this->db->sql_return_on_error(true);
foreach ($words['add'] as $word_in => $word_ary)
{
$title_match = ($word_in == 'title') ? 1 : 0;
if (sizeof($word_ary))
{
$sql = 'INSERT INTO ' . SEARCH_WORDMATCH_TABLE . ' (post_id, word_id, title_match)
SELECT ' . (int) $post_id . ', word_id, ' . (int) $title_match . '
FROM ' . SEARCH_WORDLIST_TABLE . '
WHERE ' . $this->db->sql_in_set('word_text', $word_ary);
$this->db->sql_query($sql);
$sql = 'UPDATE ' . SEARCH_WORDLIST_TABLE . '
SET word_count = word_count + 1
WHERE ' . $this->db->sql_in_set('word_text', $word_ary);
$this->db->sql_query($sql);
}
}
$this->db->sql_return_on_error(false);
$this->db->sql_transaction('commit');
// destroy cached search results containing any of the words removed or added
$this->destroy_cache(array_unique(array_merge($words['add']['post'], $words['add']['title'], $words['del']['post'], $words['del']['title'])), array($poster_id));
unset($unique_add_words);
unset($words);
unset($cur_words);
}
/**
* Performs a search on an author's posts without caring about message contents. Depends on display specific params
*
* @param string $type contains either posts or topics depending on what should be searched for
* @param boolean $firstpost_only if true, only topic starting posts will be considered
* @param array $sort_by_sql contains SQL code for the ORDER BY part of a query
* @param string $sort_key is the key of $sort_by_sql for the selected sorting
* @param string $sort_dir is either a or d representing ASC and DESC
* @param string $sort_days specifies the maximum amount of days a post may be old
* @param array $ex_fid_ary specifies an array of forum ids which should not be searched
* @param string $post_visibility specifies which types of posts the user can view in which forums
* @param int $topic_id is set to 0 or a topic id, if it is not 0 then only posts in this topic should be searched
* @param array $author_ary an array of author ids
* @param string $author_name specifies the author match, when ANONYMOUS is also a search-match
* @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
* @param int $start indicates the first index of the page
* @param int $per_page number of ids each page is supposed to contain
* @return boolean|int total number of results
*/
public function author_search($type, $firstpost_only, $sort_by_sql, $sort_key, $sort_dir, $sort_days, $ex_fid_ary, $post_visibility, $topic_id, $author_ary, $author_name, &$id_ary, &$start, $per_page)
{
// No author? No posts
if (!sizeof($author_ary)) {
return 0;
}
// generate a search_key from all the options to identify the results
$search_key_array = array('', $type, $firstpost_only ? 'firstpost' : '', '', '', $sort_days, $sort_key, $topic_id, implode(',', $ex_fid_ary), $post_visibility, implode(',', $author_ary), $author_name);
/**
* Allow changing the search_key for cached results
*
* @event core.search_postgres_by_author_modify_search_key
* @var array search_key_array Array with search parameters to generate the search_key
* @var string type Searching type ('posts', 'topics')
* @var boolean firstpost_only Flag indicating if only topic starting posts are considered
* @var int sort_days Time, in days, of the oldest possible post to list
* @var string sort_key The sort type used from the possible sort types
* @var int topic_id Limit the search to this topic_id only
* @var array ex_fid_ary Which forums not to search on
* @var string post_visibility Post visibility data
* @var array author_ary Array of user_id containing the users to filter the results to
* @var string author_name The username to search on
* @since 3.1.7-RC1
*/
$vars = array('search_key_array', 'type', 'firstpost_only', 'sort_days', 'sort_key', 'topic_id', 'ex_fid_ary', 'post_visibility', 'author_ary', 'author_name');
extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_by_author_modify_search_key', compact($vars)));
$search_key = md5(implode('#', $search_key_array));
if ($start < 0) {
$start = 0;
}
// try reading the results from cache
$result_count = 0;
if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) {
return $result_count;
}
$id_ary = array();
// Create some display specific sql strings
if ($author_name) {
// first one matches post of registered users, second one guests and deleted users
$sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')';
} else {
$sql_author = $this->db->sql_in_set('p.poster_id', $author_ary);
}
$sql_fora = sizeof($ex_fid_ary) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '';
$sql_topic_id = $topic_id ? ' AND p.topic_id = ' . (int) $topic_id : '';
$sql_time = $sort_days ? ' AND p.post_time >= ' . (time() - $sort_days * 86400) : '';
$sql_firstpost = $firstpost_only ? ' AND p.post_id = t.topic_first_post_id' : '';
// Build sql strings for sorting
$sql_sort = $sort_by_sql[$sort_key] . ($sort_dir == 'a' ? ' ASC' : ' DESC');
$sql_sort_table = $sql_sort_join = '';
switch ($sql_sort[0]) {
case 'u':
$sql_sort_table = USERS_TABLE . ' u, ';
$sql_sort_join = $type == 'posts' ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster ';
break;
case 't':
$sql_sort_table = $type == 'posts' && !$firstpost_only ? TOPICS_TABLE . ' t, ' : '';
$sql_sort_join = $type == 'posts' && !$firstpost_only ? ' AND t.topic_id = p.topic_id ' : '';
break;
case 'f':
$sql_sort_table = FORUMS_TABLE . ' f, ';
$sql_sort_join = ' AND f.forum_id = p.forum_id ';
break;
}
$m_approve_fid_sql = ' AND ' . $post_visibility;
/**
* Allow changing the query used to search for posts by author in fulltext_postgres
*
* @event core.search_postgres_author_count_query_before
* @var int result_count The previous result count for the format of the query.
* Set to 0 to force a re-count
* @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen
* @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table
* @var array author_ary Array of user_id containing the users to filter the results to
* @var string author_name An extra username to search on
* @var string sql_author SQL WHERE condition for the post author ids
* @var int topic_id Limit the search to this topic_id only
* @var string sql_topic_id SQL of topic_id
* @var string sort_by_sql The possible predefined sort types
* @var string sort_key The sort type used from the possible sort types
* @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used
* @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir
* @var string sort_days Time, in days, that the oldest post showing can have
* @var string sql_time The SQL to search on the time specifyed by sort_days
* @var bool firstpost_only Wether or not to search only on the first post of the topics
* @var array ex_fid_ary Forum ids that must not be searched on
* @var array sql_fora SQL query for ex_fid_ary
* @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions
* @var int start How many posts to skip in the search results (used for pagination)
* @since 3.1.5-RC1
*/
$vars = array('result_count', 'sql_sort_table', 'sql_sort_join', 'author_ary', 'author_name', 'sql_author', 'topic_id', 'sql_topic_id', 'sort_by_sql', 'sort_key', 'sort_dir', 'sql_sort', 'sort_days', 'sql_time', 'firstpost_only', 'ex_fid_ary', 'sql_fora', 'm_approve_fid_sql', 'start');
extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_author_count_query_before', compact($vars)));
// Build the query for really selecting the post_ids
if ($type == 'posts') {
$sql = "SELECT p.post_id\n\t\t\t\tFROM " . $sql_sort_table . POSTS_TABLE . ' p' . ($firstpost_only ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "\n\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t{$sql_time}\n\t\t\t\tORDER BY {$sql_sort}";
$field = 'post_id';
} else {
$sql = "SELECT t.topic_id\n\t\t\t\tFROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p\n\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t{$sql_fora}\n\t\t\t\t\tAND t.topic_id = p.topic_id\n\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t{$sql_time}\n\t\t\t\tGROUP BY t.topic_id, {$sort_by_sql[$sort_key]}\n\t\t\t\tORDER BY {$sql_sort}";
$field = 'topic_id';
}
$this->db->sql_transaction('begin');
// Only read one block of posts from the db and then cache it
$result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
while ($row = $this->db->sql_fetchrow($result)) {
$id_ary[] = $row[$field];
}
$this->db->sql_freeresult($result);
// retrieve the total result count if needed
if (!$result_count) {
if ($type == 'posts') {
$sql_count = "SELECT COUNT(*) as result_count\n\t\t\t\t\tFROM " . $sql_sort_table . POSTS_TABLE . ' p' . ($firstpost_only ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "\n\t\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t\t{$sql_time}";
} else {
$sql_count = "SELECT COUNT(*) as result_count\n\t\t\t\t\tFROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p\n\t\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t\tAND t.topic_id = p.topic_id\n\t\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t\t{$sql_time}\n\t\t\t\t\tGROUP BY t.topic_id, {$sort_by_sql[$sort_key]}";
}
$this->db->sql_query($sql_count);
$result_count = (int) $this->db->sql_fetchfield('result_count');
if (!$result_count) {
return false;
}
}
$this->db->sql_transaction('commit');
if ($start >= $result_count) {
$start = floor(($result_count - 1) / $per_page) * $per_page;
$result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
while ($row = $this->db->sql_fetchrow($result)) {
$id_ary[] = (int) $row[$field];
}
$this->db->sql_freeresult($result);
$id_ary = array_unique($id_ary);
}
if (sizeof($id_ary)) {
$this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir);
$id_ary = array_slice($id_ary, 0, $per_page);
return $result_count;
}
return false;
}
/**
* Moves an item up/down
*
* @param int $teampage_id teampage_id of the item to be moved
* @param int $delta number of steps:
* - positive = move up
* - negative = move down
* @return bool True if the group was moved successfully
*/
public function move_teampage($teampage_id, $delta)
{
$delta = (int) $delta;
if (!$delta) {
return false;
}
$move_up = $delta > 0 ? true : false;
$data = $this->get_teampage_values($teampage_id);
$current_value = (int) $data['teampage_position'];
if ($current_value != self::GROUP_DISABLED) {
$this->db->sql_transaction('begin');
if (!$move_up && $data['teampage_parent'] == self::NO_PARENT) {
// If we move items down, we need to grab the one sibling more,
// so we do not ignore the children of the previous sibling.
// We will remove the additional sibling later on.
$delta = abs($delta) + 1;
}
$sql = 'SELECT teampage_id, teampage_position
FROM ' . TEAMPAGE_TABLE . '
WHERE teampage_parent = ' . (int) $data['teampage_parent'] . '
AND teampage_position' . ($move_up ? ' < ' : ' > ') . $current_value . '
ORDER BY teampage_position' . ($move_up ? ' DESC' : ' ASC');
$result = $this->db->sql_query_limit($sql, $delta);
$sibling_count = 0;
$sibling_limit = $delta;
// Reset the delta, as we recalculate the new real delta
$delta = 0;
while ($row = $this->db->sql_fetchrow($result)) {
$sibling_count++;
$delta = $current_value - $row['teampage_position'];
// Remove the additional sibling we added previously
// But only, if we included it, this is not be the case
// when we reached the end of our list
if (!$move_up && $data['teampage_parent'] == self::NO_PARENT && $sibling_count == $sibling_limit) {
$delta++;
}
}
$this->db->sql_freeresult($result);
if ($delta) {
$sql = 'SELECT COUNT(teampage_id) as num_items
FROM ' . TEAMPAGE_TABLE . '
WHERE teampage_id = ' . (int) $teampage_id . '
OR teampage_parent = ' . (int) $teampage_id;
$result = $this->db->sql_query($sql);
$num_items = (int) $this->db->sql_fetchfield('num_items');
$this->db->sql_freeresult($result);
// First we move all items between our current value and the target value up/down 1,
// so we have a gap for our item to move.
$sql = 'UPDATE ' . TEAMPAGE_TABLE . '
SET teampage_position = teampage_position' . ($move_up ? ' + ' : ' - ') . $num_items . '
WHERE teampage_position' . ($move_up ? ' >= ' : ' <= ') . ($current_value - $delta) . '
AND teampage_position' . ($move_up ? ' < ' : ' > ') . $current_value . '
AND NOT (teampage_id = ' . (int) $teampage_id . '
OR teampage_parent = ' . (int) $teampage_id . ')';
$this->db->sql_query($sql);
$delta = !$move_up && $data['teampage_parent'] == self::NO_PARENT ? abs($delta) - ($num_items - 1) : abs($delta);
// And now finally, when we moved some other items and built a gap,
// we can move the desired item to it.
$sql = 'UPDATE ' . TEAMPAGE_TABLE . '
SET teampage_position = teampage_position ' . ($move_up ? ' - ' : ' + ') . $delta . '
WHERE teampage_id = ' . (int) $teampage_id . '
OR teampage_parent = ' . (int) $teampage_id;
$this->db->sql_query($sql);
$this->db->sql_transaction('commit');
$this->cache->destroy('sql', TEAMPAGE_TABLE);
return true;
}
$this->db->sql_transaction('commit');
}
$this->cache->destroy('sql', TEAMPAGE_TABLE);
return false;
}
/**
* Event: core.mcp_lock_unlock_after
*/
public function mcp_lock_unlock_after($event)
{
if ($event['action'] == 'lock') {
$topic_ids = $event['ids'];
$topic_id = (int) current($topic_ids);
$topic_data = $event['data'];
$forum_id = (int) $topic_data[$topic_id]['forum_id'];
$to_forum_id = (int) $topic_data[$topic_id]['move_topics_to'];
$forum_data = phpbb_get_forum_data($to_forum_id);
$to_forum_name = $forum_data[$to_forum_id]['forum_name'];
$topics_moved = $topics_moved_unapproved = $topics_moved_softdeleted = 0;
foreach ($topic_data as $topic_id => $topic_info) {
if ($topic_info['topic_visibility'] == ITEM_APPROVED) {
$topics_moved++;
} else {
if ($topic_info['topic_visibility'] == ITEM_UNAPPROVED || $topic_info['topic_visibility'] == ITEM_REAPPROVE) {
$topics_moved_unapproved++;
} else {
if ($topic_info['topic_visibility'] == ITEM_DELETED) {
$topics_moved_softdeleted++;
}
}
}
}
$this->db->sql_transaction('begin');
// Move topics, but do not resync yet
if (!function_exists('move_topics')) {
include $this->root_path . 'includes/functions_admin.' . $this->php_ext;
}
move_topics($topic_ids, $to_forum_id, false);
foreach ($topic_data as $topic_id => $row) {
// We add the $to_forum_id twice, because 'forum_id' is updated
// when the topic is moved again later.
$this->log->add('mod', $this->user->data['user_id'], $this->user->ip, 'LOG_MOVED_LOCKED_TOPIC', false, array('forum_id' => $to_forum_id, 'topic_id' => $topic_id, $row['topic_title'], $row['forum_name'], $to_forum_name));
}
unset($topic_data);
$sync_sql = array();
if ($topics_moved) {
$sync_sql[$to_forum_id][] = 'forum_topics_approved = forum_topics_approved + ' . (int) $topics_moved;
if ($topics_moved > 0) {
$sync_sql[$forum_id][] = 'forum_topics_approved = forum_topics_approved - ' . (int) $topics_moved;
}
}
if ($topics_moved_unapproved) {
$sync_sql[$to_forum_id][] = 'forum_topics_unapproved = forum_topics_unapproved + ' . (int) $topics_moved_unapproved;
$sync_sql[$forum_id][] = 'forum_topics_unapproved = forum_topics_unapproved - ' . (int) $topics_moved_unapproved;
}
if ($topics_moved_softdeleted) {
$sync_sql[$to_forum_id][] = 'forum_topics_softdeleted = forum_topics_softdeleted + ' . (int) $topics_moved_softdeleted;
$sync_sql[$forum_id][] = 'forum_topics_softdeleted = forum_topics_softdeleted - ' . (int) $topics_moved_softdeleted;
}
foreach ($sync_sql as $forum_id_key => $array) {
$sql = 'UPDATE ' . FORUMS_TABLE . '
SET ' . implode(', ', $array) . '
WHERE forum_id = ' . $forum_id_key;
$this->db->sql_query($sql);
}
$this->db->sql_transaction('commit');
sync('forum', 'forum_id', array($forum_id, $to_forum_id));
}
}
/**
* Moves topics to a new forum after they have been locked
*
* @param array $topic_data
* @param string $action
*/
public function move_topics($topic_data, $action)
{
$first_topic_data = reset($topic_data);
$is_enabled = (int) $first_topic_data[$action];
$to_forum_id = (int) $first_topic_data['move_topics_when_locked_to'];
/**
* This event allows you to perform additional actions before locked topics are moved.
*
* @event kasimi.movetopicswhenlocked.move_topics_before
* @var array topic_data Array with general topic data
* @var string action Who triggered the forums to be moved, one of move_topics_when_locked|move_topics_when_locked_solved
* @var int is_enabled Whether or not the forum's ACP settings specify the topics to be moved
* @var int to_forum_id The destination forum
* @since 1.0.2
*/
$vars = array('topic_data', 'action', 'is_enabled', 'to_forum_id');
extract($this->dispatcher->trigger_event('kasimi.movetopicswhenlocked.move_topics_before', compact($vars)));
// Forum settings are set to not move the topics
if (!$is_enabled || !$to_forum_id) {
return;
}
$forum_id = (int) $first_topic_data['forum_id'];
// The topics are already in the destination forum
if ($forum_id == $to_forum_id) {
return;
}
if (!function_exists('phpbb_get_forum_data')) {
include $this->root_path . 'includes/functions_mcp.' . $this->php_ext;
}
$to_forum_data = phpbb_get_forum_data($to_forum_id, 'f_post');
// The destination forum does not exist
if (empty($to_forum_data)) {
return;
}
// The following code is taken from the mcp_move_topic() function in /includes/mpc/mcp_main.php
$topics_moved = $topics_moved_unapproved = $topics_moved_softdeleted = 0;
$posts_moved = $posts_moved_unapproved = $posts_moved_softdeleted = 0;
foreach ($topic_data as $topic_id => $topic_info) {
if ($topic_info['topic_visibility'] == ITEM_APPROVED) {
$topics_moved++;
} else {
if ($topic_info['topic_visibility'] == ITEM_UNAPPROVED || $topic_info['topic_visibility'] == ITEM_REAPPROVE) {
$topics_moved_unapproved++;
} else {
if ($topic_info['topic_visibility'] == ITEM_DELETED) {
$topics_moved_softdeleted++;
}
}
}
$posts_moved += $topic_info['topic_posts_approved'];
$posts_moved_unapproved += $topic_info['topic_posts_unapproved'];
$posts_moved_softdeleted += $topic_info['topic_posts_softdeleted'];
}
$this->db->sql_transaction('begin');
if (!function_exists('move_topics')) {
include $this->root_path . 'includes/functions_admin.' . $this->php_ext;
}
// Move topics, but do not resync yet
move_topics(array_keys($topic_data), $to_forum_id, false);
foreach ($topic_data as $topic_id => $row) {
// We add the $to_forum_id twice, because 'forum_id' is updated
// when the topic is moved again later.
$this->log->add('mod', $this->user->data['user_id'], $this->user->ip, 'LOG_MOVED_LOCKED_TOPIC', false, array('forum_id' => (int) $to_forum_id, 'topic_id' => (int) $topic_id, $row['topic_title'], $row['forum_name'], $to_forum_data[$to_forum_id]['forum_name']));
}
$sync_sql = array();
if ($posts_moved) {
$sync_sql[$to_forum_id][] = 'forum_posts_approved = forum_posts_approved + ' . (int) $posts_moved;
$sync_sql[$forum_id][] = 'forum_posts_approved = forum_posts_approved - ' . (int) $posts_moved;
}
if ($posts_moved_unapproved) {
$sync_sql[$to_forum_id][] = 'forum_posts_unapproved = forum_posts_unapproved + ' . (int) $posts_moved_unapproved;
$sync_sql[$forum_id][] = 'forum_posts_unapproved = forum_posts_unapproved - ' . (int) $posts_moved_unapproved;
}
if ($posts_moved_softdeleted) {
$sync_sql[$to_forum_id][] = 'forum_posts_softdeleted = forum_posts_softdeleted + ' . (int) $posts_moved_softdeleted;
$sync_sql[$forum_id][] = 'forum_posts_softdeleted = forum_posts_softdeleted - ' . (int) $posts_moved_softdeleted;
}
if ($topics_moved) {
$sync_sql[$to_forum_id][] = 'forum_topics_approved = forum_topics_approved + ' . (int) $topics_moved;
if ($topics_moved > 0) {
$sync_sql[$forum_id][] = 'forum_topics_approved = forum_topics_approved - ' . (int) $topics_moved;
}
}
if ($topics_moved_unapproved) {
$sync_sql[$to_forum_id][] = 'forum_topics_unapproved = forum_topics_unapproved + ' . (int) $topics_moved_unapproved;
$sync_sql[$forum_id][] = 'forum_topics_unapproved = forum_topics_unapproved - ' . (int) $topics_moved_unapproved;
}
if ($topics_moved_softdeleted) {
$sync_sql[$to_forum_id][] = 'forum_topics_softdeleted = forum_topics_softdeleted + ' . (int) $topics_moved_softdeleted;
$sync_sql[$forum_id][] = 'forum_topics_softdeleted = forum_topics_softdeleted - ' . (int) $topics_moved_softdeleted;
}
foreach ($sync_sql as $forum_id_key => $array) {
$sql = 'UPDATE ' . FORUMS_TABLE . '
SET ' . implode(', ', $array) . '
WHERE forum_id = ' . (int) $forum_id_key;
$this->db->sql_query($sql);
}
$this->db->sql_transaction('commit');
sync('forum', 'forum_id', array($forum_id, $to_forum_id));
/**
* This event allows you to perform additional actions after locked topics have been moved.
*
* @event kasimi.movetopicswhenlocked.move_topics_after
* @var array topic_data Array with general topic data
* @var string action Who triggered the forums to be moved, one of move_topics_when_locked|move_topics_when_locked_solved
* @var int to_forum_id The destination forum
* @var int topics_moved Number of moved topics
* @var int topics_moved_unapproved Number of moved unapproved topics
* @var int topics_moved_softdeleted Number of moved soft-deleted topics
* @var int posts_moved Number of moved posts
* @var int posts_moved_unapproved Number of moved unapproved posts
* @var int posts_moved_softdeleted Number of moved soft-deleted posts
* @since 1.0.2
*/
$vars = array('topic_data', 'action', 'to_forum_id', 'topics_moved', 'topics_moved_unapproved', 'topics_moved_softdeleted', 'posts_moved', 'posts_moved_unapproved', 'posts_moved_softdeleted');
extract($this->dispatcher->trigger_event('kasimi.movetopicswhenlocked.move_topics_after', compact($vars)));
unset($topic_data);
}
/**
* Updates rows in given table from a set of values to a new value.
* If this results in rows violating uniqueness constraints, the duplicate
* rows are merged respecting notify_status (0 takes precedence over 1).
*
* The only supported table is topics_watch.
*
* @param \phpbb\db\driver\driver_interface $db Database object
* @param string $table Table on which to perform the update
* @param string $column Column whose values to change
* @param array $from_values An array of values that should be changed
* @param int $to_value The new value
* @return null
*/
function phpbb_update_rows_avoiding_duplicates_notify_status(\phpbb\db\driver\driver_interface $db, $table, $column, $from_values, $to_value)
{
$sql = "SELECT {$column}, user_id, notify_status\n\t\tFROM {$table}\n\t\tWHERE " . $db->sql_in_set($column, $from_values);
$result = $db->sql_query($sql);
$old_user_ids = array();
while ($row = $db->sql_fetchrow($result)) {
$old_user_ids[(int) $row['notify_status']][$row[$column]][] = (int) $row['user_id'];
}
$db->sql_freeresult($result);
$sql = "SELECT {$column}, user_id\n\t\tFROM {$table}\n\t\tWHERE {$column} = " . (int) $to_value;
$result = $db->sql_query($sql);
$new_user_ids = array();
while ($row = $db->sql_fetchrow($result)) {
$new_user_ids[$row[$column]][] = (int) $row['user_id'];
}
$db->sql_freeresult($result);
$queries = array();
$extra_updates = array(0 => 'notify_status = 0', 1 => '');
foreach ($from_values as $from_value) {
foreach ($extra_updates as $notify_status => $extra_update) {
if (!isset($old_user_ids[$notify_status][$from_value])) {
continue;
}
if (empty($new_user_ids)) {
$sql = "UPDATE {$table}\n\t\t\t\t\tSET {$column} = " . (int) $to_value . "\n\t\t\t\t\tWHERE {$column} = '" . $db->sql_escape($from_value) . "'";
$queries[] = $sql;
} else {
$different_user_ids = array_diff($old_user_ids[$notify_status][$from_value], $new_user_ids[$to_value]);
if (!empty($different_user_ids)) {
$sql = "UPDATE {$table}\n\t\t\t\t\t\tSET {$column} = " . (int) $to_value . "\n\t\t\t\t\t\tWHERE {$column} = '" . $db->sql_escape($from_value) . "'\n\t\t\t\t\t\tAND " . $db->sql_in_set('user_id', $different_user_ids);
$queries[] = $sql;
}
if ($extra_update) {
$same_user_ids = array_diff($old_user_ids[$notify_status][$from_value], $different_user_ids);
if (!empty($same_user_ids)) {
$sql = "UPDATE {$table}\n\t\t\t\t\t\t\tSET {$extra_update}\n\t\t\t\t\t\t\tWHERE {$column} = '" . (int) $to_value . "'\n\t\t\t\t\t\t\tAND " . $db->sql_in_set('user_id', $same_user_ids);
$queries[] = $sql;
}
}
}
}
}
if (!empty($queries)) {
$db->sql_transaction('begin');
foreach ($queries as $sql) {
$db->sql_query($sql);
}
$sql = "DELETE FROM {$table}\n\t\t\tWHERE " . $db->sql_in_set($column, $from_values);
$db->sql_query($sql);
$db->sql_transaction('commit');
}
}
/**
* Performs a search on an author's posts without caring about message contents. Depends on display specific params
*
* @param string $type contains either posts or topics depending on what should be searched for
* @param boolean $firstpost_only if true, only topic starting posts will be considered
* @param array $sort_by_sql contains SQL code for the ORDER BY part of a query
* @param string $sort_key is the key of $sort_by_sql for the selected sorting
* @param string $sort_dir is either a or d representing ASC and DESC
* @param string $sort_days specifies the maximum amount of days a post may be old
* @param array $ex_fid_ary specifies an array of forum ids which should not be searched
* @param string $post_visibility specifies which types of posts the user can view in which forums
* @param int $topic_id is set to 0 or a topic id, if it is not 0 then only posts in this topic should be searched
* @param array $author_ary an array of author ids
* @param string $author_name specifies the author match, when ANONYMOUS is also a search-match
* @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
* @param int $start indicates the first index of the page
* @param int $per_page number of ids each page is supposed to contain
* @return boolean|int total number of results
*/
public function author_search($type, $firstpost_only, $sort_by_sql, $sort_key, $sort_dir, $sort_days, $ex_fid_ary, $post_visibility, $topic_id, $author_ary, $author_name, &$id_ary, &$start, $per_page)
{
// No author? No posts
if (!sizeof($author_ary)) {
return 0;
}
// generate a search_key from all the options to identify the results
$search_key = md5(implode('#', array('', $type, $firstpost_only ? 'firstpost' : '', '', '', $sort_days, $sort_key, $topic_id, implode(',', $ex_fid_ary), $post_visibility, implode(',', $author_ary), $author_name)));
if ($start < 0) {
$start = 0;
}
// try reading the results from cache
$result_count = 0;
if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) {
return $result_count;
}
$id_ary = array();
// Create some display specific sql strings
if ($author_name) {
// first one matches post of registered users, second one guests and deleted users
$sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')';
} else {
$sql_author = $this->db->sql_in_set('p.poster_id', $author_ary);
}
$sql_fora = sizeof($ex_fid_ary) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '';
$sql_topic_id = $topic_id ? ' AND p.topic_id = ' . (int) $topic_id : '';
$sql_time = $sort_days ? ' AND p.post_time >= ' . (time() - $sort_days * 86400) : '';
$sql_firstpost = $firstpost_only ? ' AND p.post_id = t.topic_first_post_id' : '';
// Build sql strings for sorting
$sql_sort = $sort_by_sql[$sort_key] . ($sort_dir == 'a' ? ' ASC' : ' DESC');
$sql_sort_table = $sql_sort_join = '';
switch ($sql_sort[0]) {
case 'u':
$sql_sort_table = USERS_TABLE . ' u, ';
$sql_sort_join = $type == 'posts' ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster ';
break;
case 't':
$sql_sort_table = $type == 'posts' && !$firstpost_only ? TOPICS_TABLE . ' t, ' : '';
$sql_sort_join = $type == 'posts' && !$firstpost_only ? ' AND t.topic_id = p.topic_id ' : '';
break;
case 'f':
$sql_sort_table = FORUMS_TABLE . ' f, ';
$sql_sort_join = ' AND f.forum_id = p.forum_id ';
break;
}
$m_approve_fid_sql = ' AND ' . $post_visibility;
// Build the query for really selecting the post_ids
if ($type == 'posts') {
$sql = "SELECT p.post_id\n\t\t\t\tFROM " . $sql_sort_table . POSTS_TABLE . ' p' . ($firstpost_only ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "\n\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t{$sql_time}\n\t\t\t\tORDER BY {$sql_sort}";
$field = 'post_id';
} else {
$sql = "SELECT t.topic_id\n\t\t\t\tFROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p\n\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t{$sql_fora}\n\t\t\t\t\tAND t.topic_id = p.topic_id\n\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t{$sql_time}\n\t\t\t\tGROUP BY t.topic_id, {$sort_by_sql[$sort_key]}\n\t\t\t\tORDER BY {$sql_sort}";
$field = 'topic_id';
}
$this->db->sql_transaction('begin');
// Only read one block of posts from the db and then cache it
$result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
while ($row = $this->db->sql_fetchrow($result)) {
$id_ary[] = $row[$field];
}
$this->db->sql_freeresult($result);
// retrieve the total result count if needed
if (!$result_count) {
if ($type == 'posts') {
$sql_count = "SELECT COUNT(*) as result_count\n\t\t\t\t\tFROM " . $sql_sort_table . POSTS_TABLE . ' p' . ($firstpost_only ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "\n\t\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t\t{$sql_time}";
} else {
$sql_count = "SELECT COUNT(*) as result_count\n\t\t\t\t\tFROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p\n\t\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t\tAND t.topic_id = p.topic_id\n\t\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t\t{$sql_time}\n\t\t\t\t\tGROUP BY t.topic_id, {$sort_by_sql[$sort_key]}";
}
$result = $this->db->sql_query($sql_count);
$result_count = (int) $this->db->sql_fetchfield('result_count');
if (!$result_count) {
return false;
}
}
$this->db->sql_transaction('commit');
if ($start >= $result_count) {
$start = floor(($result_count - 1) / $per_page) * $per_page;
$result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
while ($row = $this->db->sql_fetchrow($result)) {
$id_ary[] = (int) $row[$field];
}
$this->db->sql_freeresult($result);
$id_ary = array_unique($id_ary);
}
if (sizeof($id_ary)) {
$this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir);
$id_ary = array_slice($id_ary, 0, $per_page);
return $result_count;
}
return false;
}
/**
* The main cron task code.
*/
public function cron_tidy_warnings($topic_ids = array())
{
$warning_list = $user_list = $unban_list = array();
$current_time = time();
$sql = 'SELECT * FROM ' . WARNINGS_TABLE . "\n\t\t\tWHERE warning_end < {$current_time} \n\t\t\tAND warning_end > 0 \n\t\t\tAND warning_status = 1";
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$warning_list[] = $row['warning_id'];
$user_list[$row['user_id']] = isset($user_list[$row['user_id']]) ? ++$user_list[$row['user_id']] : 1;
}
$this->db->sql_freeresult($result);
if (sizeof($warning_list)) {
$this->db->sql_transaction('begin');
$sql = 'UPDATE ' . WARNINGS_TABLE . ' SET warning_status = 0
WHERE ' . $this->db->sql_in_set('warning_id', $warning_list);
$this->db->sql_query($sql);
foreach ($user_list as $user_id => $value) {
$sql = 'UPDATE ' . USERS_TABLE . " SET user_warnings = user_warnings - {$value}\n\t\t\t\t\tWHERE user_id = {$user_id}";
$this->db->sql_query($sql);
}
// Try to get storage engine type to detect if transactions are supported
// to apply proper bans selection (MyISAM/InnoDB)
$operator = '<';
/* Comment out this part of code for now as get_table_status()
* as unavailable for \phpbb\db\driver\driver_interface
if (strpos($this->db->get_sql_layer(), 'mysql') !== false)
{
$table_status = $this->db->get_table_status(USERS_TABLE);
if (isset($table_status['Engine']))
{
$operator = ($table_status['Engine'] === 'MyISAM') ? '<' : '<=';
}
}
*/
$sql = 'SELECT u.user_id, b.ban_id FROM ' . USERS_TABLE . ' u, ' . BANLIST_TABLE . " b\n\t\t\t\tWHERE u.user_ban_id = 1\n\t\t\t\t\tAND u.user_warnings {$operator} " . (int) $this->config['warnings_for_ban'] . '
AND u.user_id = b.ban_userid';
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$unban_list[(int) $row['user_id']] = (int) $row['ban_id'];
}
$this->db->sql_freeresult($result);
if (sizeof($unban_list)) {
$sql = 'UPDATE ' . USERS_TABLE . ' SET user_ban_id = 0
WHERE ' . $this->db->sql_in_set('user_id', array_keys($unban_list));
$this->db->sql_query($sql);
$sql = 'SELECT u.username AS unban_info, u.user_id
FROM ' . USERS_TABLE . ' u, ' . BANLIST_TABLE . ' b
WHERE ' . $this->db->sql_in_set('b.ban_id', $unban_list) . '
AND u.user_id = b.ban_userid';
$result = $this->db->sql_query($sql);
$l_unban_list = '';
$user_ids_ary = array();
while ($row = $this->db->sql_fetchrow($result)) {
$l_unban_list .= ($l_unban_list != '' ? ', ' : '') . $row['unban_info'];
$user_ids_ary[] = $row['user_id'];
}
$this->db->sql_freeresult($result);
$sql = 'DELETE FROM ' . BANLIST_TABLE . '
WHERE ' . $this->db->sql_in_set('ban_id', $unban_list);
$this->db->sql_query($sql);
// Add to moderator log, admin log and user notes
$this->phpbb_log->add('admin', $this->user->data['user_id'], $this->user->ip, 'LOG_UNBAN_USER', false, $l_unban_list);
$this->phpbb_log->add('mod', 0, 0, 'LOG_UNBAN_USER', false, $l_unban_list);
foreach ($user_ids_ary as $user_id) {
$this->phpbb_log->add('user', $user_id, 0, 'LOG_UNBAN_USER', false, $l_unban_list);
}
}
$this->db->sql_transaction('commit');
}
$this->cache->destroy('sql', array(WARNINGS_TABLE, BANLIST_TABLE));
$this->config->set('warnings_last_gc', time(), true);
}
