[ Index ]

PHP Cross Reference of phpBB-3.3.0-deutsch

title

Body

[close]

/includes/ -> functions_admin.php (source)

   1  <?php
   2  /**
   3  *
   4  * This file is part of the phpBB Forum Software package.
   5  *
   6  * @copyright (c) phpBB Limited <https://www.phpbb.com>
   7  * @license GNU General Public License, version 2 (GPL-2.0)
   8  *
   9  * For full copyright and license information, please see
  10  * the docs/CREDITS.txt file.
  11  *
  12  */
  13  
  14  /**
  15  * @ignore
  16  */
  17  if (!defined('IN_PHPBB'))
  18  {
  19      exit;
  20  }
  21  
  22  /**
  23  * Recalculate Nested Sets
  24  *
  25  * @param int    $new_id    first left_id (should start with 1)
  26  * @param string    $pkey    primary key-column (containing the id for the parent_id of the children)
  27  * @param string    $table    constant or fullname of the table
  28  * @param int    $parent_id parent_id of the current set (default = 0)
  29  * @param array    $where    contains strings to compare closer on the where statement (additional)
  30  */
  31  function recalc_nested_sets(&$new_id, $pkey, $table, $parent_id = 0, $where = array())
  32  {
  33      global $db;
  34  
  35      $sql = 'SELECT *
  36          FROM ' . $table . '
  37          WHERE parent_id = ' . (int) $parent_id .
  38          ((!empty($where)) ? ' AND ' . implode(' AND ', $where) : '') . '
  39          ORDER BY left_id ASC';
  40      $result = $db->sql_query($sql);
  41      while ($row = $db->sql_fetchrow($result))
  42      {
  43          // First we update the left_id for this module
  44          if ($row['left_id'] != $new_id)
  45          {
  46              $db->sql_query('UPDATE ' . $table . ' SET ' . $db->sql_build_array('UPDATE', array('left_id' => $new_id)) . " WHERE $pkey = {$row[$pkey]}");
  47          }
  48          $new_id++;
  49  
  50          // Then we go through any children and update their left/right id's
  51          recalc_nested_sets($new_id, $pkey, $table, $row[$pkey], $where);
  52  
  53          // Then we come back and update the right_id for this module
  54          if ($row['right_id'] != $new_id)
  55          {
  56              $db->sql_query('UPDATE ' . $table . ' SET ' . $db->sql_build_array('UPDATE', array('right_id' => $new_id)) . " WHERE $pkey = {$row[$pkey]}");
  57          }
  58          $new_id++;
  59      }
  60      $db->sql_freeresult($result);
  61  }
  62  
  63  /**
  64  * Simple version of jumpbox, just lists authed forums
  65  */
  66  function make_forum_select($select_id = false, $ignore_id = false, $ignore_acl = false, $ignore_nonpost = false, $ignore_emptycat = true, $only_acl_post = false, $return_array = false)
  67  {
  68      global $db, $auth, $phpbb_dispatcher;
  69  
  70      // This query is identical to the jumpbox one
  71      $sql = 'SELECT forum_id, forum_name, parent_id, forum_type, forum_flags, forum_options, left_id, right_id
  72          FROM ' . FORUMS_TABLE . '
  73          ORDER BY left_id ASC';
  74      $result = $db->sql_query($sql, 600);
  75  
  76      $rowset = array();
  77      while ($row = $db->sql_fetchrow($result))
  78      {
  79          $rowset[(int) $row['forum_id']] = $row;
  80      }
  81      $db->sql_freeresult($result);
  82  
  83      $right = 0;
  84      $padding_store = array('0' => '');
  85      $padding = '';
  86      $forum_list = ($return_array) ? array() : '';
  87  
  88      /**
  89      * Modify the forum list data
  90      *
  91      * @event core.make_forum_select_modify_forum_list
  92      * @var    array    rowset    Array with the forums list data
  93      * @since 3.1.10-RC1
  94      */
  95      $vars = array('rowset');
  96      extract($phpbb_dispatcher->trigger_event('core.make_forum_select_modify_forum_list', compact($vars)));
  97  
  98      // Sometimes it could happen that forums will be displayed here not be displayed within the index page
  99      // This is the result of forums not displayed at index, having list permissions and a parent of a forum with no permissions.
 100      // If this happens, the padding could be "broken"
 101  
 102      foreach ($rowset as $row)
 103      {
 104          if ($row['left_id'] < $right)
 105          {
 106              $padding .= '&nbsp; &nbsp;';
 107              $padding_store[$row['parent_id']] = $padding;
 108          }
 109          else if ($row['left_id'] > $right + 1)
 110          {
 111              $padding = (isset($padding_store[$row['parent_id']])) ? $padding_store[$row['parent_id']] : '';
 112          }
 113  
 114          $right = $row['right_id'];
 115          $disabled = false;
 116  
 117          if (!$ignore_acl && $auth->acl_gets(array('f_list', 'a_forum', 'a_forumadd', 'a_forumdel'), $row['forum_id']))
 118          {
 119              if ($only_acl_post && !$auth->acl_get('f_post', $row['forum_id']) || (!$auth->acl_get('m_approve', $row['forum_id']) && !$auth->acl_get('f_noapprove', $row['forum_id'])))
 120              {
 121                  $disabled = true;
 122              }
 123          }
 124          else if (!$ignore_acl)
 125          {
 126              continue;
 127          }
 128  
 129          if (
 130              ((is_array($ignore_id) && in_array($row['forum_id'], $ignore_id)) || $row['forum_id'] == $ignore_id)
 131              ||
 132              // Non-postable forum with no subforums, don't display
 133              ($row['forum_type'] == FORUM_CAT && ($row['left_id'] + 1 == $row['right_id']) && $ignore_emptycat)
 134              ||
 135              ($row['forum_type'] != FORUM_POST && $ignore_nonpost)
 136              )
 137          {
 138              $disabled = true;
 139          }
 140  
 141          if ($return_array)
 142          {
 143              // Include some more information...
 144              $selected = (is_array($select_id)) ? ((in_array($row['forum_id'], $select_id)) ? true : false) : (($row['forum_id'] == $select_id) ? true : false);
 145              $forum_list[$row['forum_id']] = array_merge(array('padding' => $padding, 'selected' => ($selected && !$disabled), 'disabled' => $disabled), $row);
 146          }
 147          else
 148          {
 149              $selected = (is_array($select_id)) ? ((in_array($row['forum_id'], $select_id)) ? ' selected="selected"' : '') : (($row['forum_id'] == $select_id) ? ' selected="selected"' : '');
 150              $forum_list .= '<option value="' . $row['forum_id'] . '"' . (($disabled) ? ' disabled="disabled" class="disabled-option"' : $selected) . '>' . $padding . $row['forum_name'] . '</option>';
 151          }
 152      }
 153      unset($padding_store, $rowset);
 154  
 155      return $forum_list;
 156  }
 157  
 158  /**
 159  * Generate size select options
 160  */
 161  function size_select_options($size_compare)
 162  {
 163      global $user;
 164  
 165      $size_types_text = array($user->lang['BYTES'], $user->lang['KIB'], $user->lang['MIB']);
 166      $size_types = array('b', 'kb', 'mb');
 167  
 168      $s_size_options = '';
 169  
 170      for ($i = 0, $size = count($size_types_text); $i < $size; $i++)
 171      {
 172          $selected = ($size_compare == $size_types[$i]) ? ' selected="selected"' : '';
 173          $s_size_options .= '<option value="' . $size_types[$i] . '"' . $selected . '>' . $size_types_text[$i] . '</option>';
 174      }
 175  
 176      return $s_size_options;
 177  }
 178  
 179  /**
 180  * Generate list of groups (option fields without select)
 181  *
 182  * @param int $group_id The default group id to mark as selected
 183  * @param array $exclude_ids The group ids to exclude from the list, false (default) if you whish to exclude no id
 184  * @param int $manage_founder If set to false (default) all groups are returned, if 0 only those groups returned not being managed by founders only, if 1 only those groups returned managed by founders only.
 185  *
 186  * @return string The list of options.
 187  */
 188  function group_select_options($group_id, $exclude_ids = false, $manage_founder = false)
 189  {
 190      global $db, $config, $phpbb_container;
 191  
 192      /** @var \phpbb\group\helper $group_helper */
 193      $group_helper = $phpbb_container->get('group_helper');
 194  
 195      $exclude_sql = ($exclude_ids !== false && count($exclude_ids)) ? 'WHERE ' . $db->sql_in_set('group_id', array_map('intval', $exclude_ids), true) : '';
 196      $sql_and = (!$config['coppa_enable']) ? (($exclude_sql) ? ' AND ' : ' WHERE ') . "group_name <> 'REGISTERED_COPPA'" : '';
 197      $sql_founder = ($manage_founder !== false) ? (($exclude_sql || $sql_and) ? ' AND ' : ' WHERE ') . 'group_founder_manage = ' . (int) $manage_founder : '';
 198  
 199      $sql = 'SELECT group_id, group_name, group_type
 200          FROM ' . GROUPS_TABLE . "
 201          $exclude_sql
 202          $sql_and
 203          $sql_founder
 204          ORDER BY group_type DESC, group_name ASC";
 205      $result = $db->sql_query($sql);
 206  
 207      $s_group_options = '';
 208      while ($row = $db->sql_fetchrow($result))
 209      {
 210          $selected = ($row['group_id'] == $group_id) ? ' selected="selected"' : '';
 211          $s_group_options .= '<option' . (($row['group_type'] == GROUP_SPECIAL) ? ' class="sep"' : '') . ' value="' . $row['group_id'] . '"' . $selected . '>' . $group_helper->get_name($row['group_name']) . '</option>';
 212      }
 213      $db->sql_freeresult($result);
 214  
 215      return $s_group_options;
 216  }
 217  
 218  /**
 219  * Obtain authed forums list
 220  */
 221  function get_forum_list($acl_list = 'f_list', $id_only = true, $postable_only = false, $no_cache = false)
 222  {
 223      global $db, $auth, $phpbb_dispatcher;
 224      static $forum_rows;
 225  
 226      if (!isset($forum_rows))
 227      {
 228          // This query is identical to the jumpbox one
 229          $expire_time = ($no_cache) ? 0 : 600;
 230  
 231          $sql = 'SELECT forum_id, forum_name, parent_id, forum_type, left_id, right_id
 232              FROM ' . FORUMS_TABLE . '
 233              ORDER BY left_id ASC';
 234          $result = $db->sql_query($sql, $expire_time);
 235  
 236          $forum_rows = array();
 237  
 238          $right = $padding = 0;
 239          $padding_store = array('0' => 0);
 240  
 241          while ($row = $db->sql_fetchrow($result))
 242          {
 243              if ($row['left_id'] < $right)
 244              {
 245                  $padding++;
 246                  $padding_store[$row['parent_id']] = $padding;
 247              }
 248              else if ($row['left_id'] > $right + 1)
 249              {
 250                  // Ok, if the $padding_store for this parent is empty there is something wrong. For now we will skip over it.
 251                  // @todo digging deep to find out "how" this can happen.
 252                  $padding = (isset($padding_store[$row['parent_id']])) ? $padding_store[$row['parent_id']] : $padding;
 253              }
 254  
 255              $right = $row['right_id'];
 256              $row['padding'] = $padding;
 257  
 258              $forum_rows[] = $row;
 259          }
 260          $db->sql_freeresult($result);
 261          unset($padding_store);
 262      }
 263  
 264      $rowset = array();
 265      foreach ($forum_rows as $row)
 266      {
 267          if ($postable_only && $row['forum_type'] != FORUM_POST)
 268          {
 269              continue;
 270          }
 271  
 272          if ($acl_list == '' || ($acl_list != '' && $auth->acl_gets($acl_list, $row['forum_id'])))
 273          {
 274              $rowset[] = ($id_only) ? (int) $row['forum_id'] : $row;
 275          }
 276      }
 277  
 278      /**
 279      * Modify the forum list data
 280      *
 281      * @event core.get_forum_list_modify_data
 282      * @var    array    rowset    Array with the forum list data
 283      * @since 3.1.10-RC1
 284      */
 285      $vars = array('rowset');
 286      extract($phpbb_dispatcher->trigger_event('core.get_forum_list_modify_data', compact($vars)));
 287  
 288      return $rowset;
 289  }
 290  
 291  /**
 292  * Get forum branch
 293  */
 294  function get_forum_branch($forum_id, $type = 'all', $order = 'descending', $include_forum = true)
 295  {
 296      global $db;
 297  
 298      switch ($type)
 299      {
 300          case 'parents':
 301              $condition = 'f1.left_id BETWEEN f2.left_id AND f2.right_id';
 302          break;
 303  
 304          case 'children':
 305              $condition = 'f2.left_id BETWEEN f1.left_id AND f1.right_id';
 306          break;
 307  
 308          default:
 309              $condition = 'f2.left_id BETWEEN f1.left_id AND f1.right_id OR f1.left_id BETWEEN f2.left_id AND f2.right_id';
 310          break;
 311      }
 312  
 313      $rows = array();
 314  
 315      $sql = 'SELECT f2.*
 316          FROM ' . FORUMS_TABLE . ' f1
 317          LEFT JOIN ' . FORUMS_TABLE . " f2 ON ($condition)
 318          WHERE f1.forum_id = $forum_id
 319          ORDER BY f2.left_id " . (($order == 'descending') ? 'ASC' : 'DESC');
 320      $result = $db->sql_query($sql);
 321  
 322      while ($row = $db->sql_fetchrow($result))
 323      {
 324          if (!$include_forum && $row['forum_id'] == $forum_id)
 325          {
 326              continue;
 327          }
 328  
 329          $rows[] = $row;
 330      }
 331      $db->sql_freeresult($result);
 332  
 333      return $rows;
 334  }
 335  
 336  /**
 337  * Copies permissions from one forum to others
 338  *
 339  * @param int    $src_forum_id        The source forum we want to copy permissions from
 340  * @param array    $dest_forum_ids        The destination forum(s) we want to copy to
 341  * @param bool    $clear_dest_perms    True if destination permissions should be deleted
 342  * @param bool    $add_log            True if log entry should be added
 343  *
 344  * @return bool                        False on error
 345  */
 346  function copy_forum_permissions($src_forum_id, $dest_forum_ids, $clear_dest_perms = true, $add_log = true)
 347  {
 348      global $db, $user, $phpbb_log;
 349  
 350      // Only one forum id specified
 351      if (!is_array($dest_forum_ids))
 352      {
 353          $dest_forum_ids = array($dest_forum_ids);
 354      }
 355  
 356      // Make sure forum ids are integers
 357      $src_forum_id = (int) $src_forum_id;
 358      $dest_forum_ids = array_map('intval', $dest_forum_ids);
 359  
 360      // No source forum or no destination forums specified
 361      if (empty($src_forum_id) || empty($dest_forum_ids))
 362      {
 363          return false;
 364      }
 365  
 366      // Check if source forum exists
 367      $sql = 'SELECT forum_name
 368          FROM ' . FORUMS_TABLE . '
 369          WHERE forum_id = ' . $src_forum_id;
 370      $result = $db->sql_query($sql);
 371      $src_forum_name = $db->sql_fetchfield('forum_name');
 372      $db->sql_freeresult($result);
 373  
 374      // Source forum doesn't exist
 375      if (empty($src_forum_name))
 376      {
 377          return false;
 378      }
 379  
 380      // Check if destination forums exists
 381      $sql = 'SELECT forum_id, forum_name
 382          FROM ' . FORUMS_TABLE . '
 383          WHERE ' . $db->sql_in_set('forum_id', $dest_forum_ids);
 384      $result = $db->sql_query($sql);
 385  
 386      $dest_forum_ids = $dest_forum_names = array();
 387      while ($row = $db->sql_fetchrow($result))
 388      {
 389          $dest_forum_ids[]    = (int) $row['forum_id'];
 390          $dest_forum_names[]    = $row['forum_name'];
 391      }
 392      $db->sql_freeresult($result);
 393  
 394      // No destination forum exists
 395      if (empty($dest_forum_ids))
 396      {
 397          return false;
 398      }
 399  
 400      // From the mysql documentation:
 401      // Prior to MySQL 4.0.14, the target table of the INSERT statement cannot appear
 402      // in the FROM clause of the SELECT part of the query. This limitation is lifted in 4.0.14.
 403      // Due to this we stay on the safe side if we do the insertion "the manual way"
 404  
 405      // Rowsets we're going to insert
 406      $users_sql_ary = $groups_sql_ary = array();
 407  
 408      // Query acl users table for source forum data
 409      $sql = 'SELECT user_id, auth_option_id, auth_role_id, auth_setting
 410          FROM ' . ACL_USERS_TABLE . '
 411          WHERE forum_id = ' . $src_forum_id;
 412      $result = $db->sql_query($sql);
 413  
 414      while ($row = $db->sql_fetchrow($result))
 415      {
 416          $row = array(
 417              'user_id'            => (int) $row['user_id'],
 418              'auth_option_id'    => (int) $row['auth_option_id'],
 419              'auth_role_id'        => (int) $row['auth_role_id'],
 420              'auth_setting'        => (int) $row['auth_setting'],
 421          );
 422  
 423          foreach ($dest_forum_ids as $dest_forum_id)
 424          {
 425              $users_sql_ary[] = $row + array('forum_id' => $dest_forum_id);
 426          }
 427      }
 428      $db->sql_freeresult($result);
 429  
 430      // Query acl groups table for source forum data
 431      $sql = 'SELECT group_id, auth_option_id, auth_role_id, auth_setting
 432          FROM ' . ACL_GROUPS_TABLE . '
 433          WHERE forum_id = ' . $src_forum_id;
 434      $result = $db->sql_query($sql);
 435  
 436      while ($row = $db->sql_fetchrow($result))
 437      {
 438          $row = array(
 439              'group_id'            => (int) $row['group_id'],
 440              'auth_option_id'    => (int) $row['auth_option_id'],
 441              'auth_role_id'        => (int) $row['auth_role_id'],
 442              'auth_setting'        => (int) $row['auth_setting'],
 443          );
 444  
 445          foreach ($dest_forum_ids as $dest_forum_id)
 446          {
 447              $groups_sql_ary[] = $row + array('forum_id' => $dest_forum_id);
 448          }
 449      }
 450      $db->sql_freeresult($result);
 451  
 452      $db->sql_transaction('begin');
 453  
 454      // Clear current permissions of destination forums
 455      if ($clear_dest_perms)
 456      {
 457          $sql = 'DELETE FROM ' . ACL_USERS_TABLE . '
 458              WHERE ' . $db->sql_in_set('forum_id', $dest_forum_ids);
 459          $db->sql_query($sql);
 460  
 461          $sql = 'DELETE FROM ' . ACL_GROUPS_TABLE . '
 462              WHERE ' . $db->sql_in_set('forum_id', $dest_forum_ids);
 463          $db->sql_query($sql);
 464      }
 465  
 466      $db->sql_multi_insert(ACL_USERS_TABLE, $users_sql_ary);
 467      $db->sql_multi_insert(ACL_GROUPS_TABLE, $groups_sql_ary);
 468  
 469      if ($add_log)
 470      {
 471          $phpbb_log->add('admin', $user->data['user_id'], $user->ip, 'LOG_FORUM_COPIED_PERMISSIONS', false, array($src_forum_name, implode(', ', $dest_forum_names)));
 472      }
 473  
 474      $db->sql_transaction('commit');
 475  
 476      return true;
 477  }
 478  
 479  /**
 480  * Get physical file listing
 481  */
 482  function filelist($rootdir, $dir = '', $type = 'gif|jpg|jpeg|png')
 483  {
 484      $matches = array($dir => array());
 485  
 486      // Remove initial / if present
 487      $rootdir = (substr($rootdir, 0, 1) == '/') ? substr($rootdir, 1) : $rootdir;
 488      // Add closing / if not present
 489      $rootdir = ($rootdir && substr($rootdir, -1) != '/') ? $rootdir . '/' : $rootdir;
 490  
 491      // Remove initial / if present
 492      $dir = (substr($dir, 0, 1) == '/') ? substr($dir, 1) : $dir;
 493      // Add closing / if not present
 494      $dir = ($dir && substr($dir, -1) != '/') ? $dir . '/' : $dir;
 495  
 496      if (!is_dir($rootdir . $dir))
 497      {
 498          return $matches;
 499      }
 500  
 501      $dh = @opendir($rootdir . $dir);
 502  
 503      if (!$dh)
 504      {
 505          return $matches;
 506      }
 507  
 508      while (($fname = readdir($dh)) !== false)
 509      {
 510          if (is_file("$rootdir$dir$fname"))
 511          {
 512              if (filesize("$rootdir$dir$fname") && preg_match('#\.' . $type . '$#i', $fname))
 513              {
 514                  $matches[$dir][] = $fname;
 515              }
 516          }
 517          else if ($fname[0] != '.' && is_dir("$rootdir$dir$fname"))
 518          {
 519              $matches += filelist($rootdir, "$dir$fname", $type);
 520          }
 521      }
 522      closedir($dh);
 523  
 524      return $matches;
 525  }
 526  
 527  /**
 528  * Move topic(s)
 529  */
 530  function move_topics($topic_ids, $forum_id, $auto_sync = true)
 531  {
 532      global $db, $phpbb_dispatcher;
 533  
 534      if (empty($topic_ids))
 535      {
 536          return;
 537      }
 538  
 539      $forum_ids = array($forum_id);
 540  
 541      if (!is_array($topic_ids))
 542      {
 543          $topic_ids = array($topic_ids);
 544      }
 545  
 546      /**
 547       * Perform additional actions before topics move
 548       *
 549       * @event core.move_topics_before
 550       * @var    array    topic_ids    Array of the moved topic ids
 551       * @var    string    forum_id    The forum id from where the topics are moved
 552       * @since 3.2.9-RC1
 553       */
 554      $vars = array(
 555          'topic_ids',
 556          'forum_id',
 557      );
 558      extract($phpbb_dispatcher->trigger_event('core.move_topics_before', compact($vars)));
 559  
 560      $sql = 'DELETE FROM ' . TOPICS_TABLE . '
 561          WHERE ' . $db->sql_in_set('topic_moved_id', $topic_ids) . '
 562              AND forum_id = ' . $forum_id;
 563      $db->sql_query($sql);
 564  
 565      if ($auto_sync)
 566      {
 567          $sql = 'SELECT DISTINCT forum_id
 568              FROM ' . TOPICS_TABLE . '
 569              WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
 570          $result = $db->sql_query($sql);
 571  
 572          while ($row = $db->sql_fetchrow($result))
 573          {
 574              $forum_ids[] = $row['forum_id'];
 575          }
 576          $db->sql_freeresult($result);
 577      }
 578  
 579      $table_ary = array(TOPICS_TABLE, POSTS_TABLE, LOG_TABLE, DRAFTS_TABLE, TOPICS_TRACK_TABLE);
 580  
 581      /**
 582       * Perform additional actions before topics move
 583       *
 584       * @event core.move_topics_before_query
 585       * @var    array    table_ary    Array of tables from which forum_id will be updated for all rows that hold the moved topics
 586       * @var    array    topic_ids    Array of the moved topic ids
 587       * @var    string    forum_id    The forum id from where the topics are moved
 588       * @var    array    forum_ids    Array of the forums where the topics are moving (includes also forum_id)
 589       * @var bool    auto_sync    Whether or not to perform auto sync
 590       * @since 3.1.5-RC1
 591       */
 592      $vars = array(
 593              'table_ary',
 594              'topic_ids',
 595              'forum_id',
 596              'forum_ids',
 597              'auto_sync',
 598      );
 599      extract($phpbb_dispatcher->trigger_event('core.move_topics_before_query', compact($vars)));
 600  
 601      foreach ($table_ary as $table)
 602      {
 603          $sql = "UPDATE $table
 604              SET forum_id = $forum_id
 605              WHERE " . $db->sql_in_set('topic_id', $topic_ids);
 606          $db->sql_query($sql);
 607      }
 608      unset($table_ary);
 609  
 610      /**
 611       * Perform additional actions after topics move
 612       *
 613       * @event core.move_topics_after
 614       * @var    array    topic_ids    Array of the moved topic ids
 615       * @var    string    forum_id    The forum id from where the topics were moved
 616       * @var    array    forum_ids    Array of the forums where the topics were moved (includes also forum_id)
 617       * @since 3.2.9-RC1
 618       */
 619      $vars = array(
 620          'topic_ids',
 621          'forum_id',
 622          'forum_ids',
 623      );
 624      extract($phpbb_dispatcher->trigger_event('core.move_topics_after', compact($vars)));
 625  
 626      if ($auto_sync)
 627      {
 628          sync('forum', 'forum_id', $forum_ids, true, true);
 629          unset($forum_ids);
 630      }
 631  }
 632  
 633  /**
 634  * Move post(s)
 635  */
 636  function move_posts($post_ids, $topic_id, $auto_sync = true)
 637  {
 638      global $db, $phpbb_dispatcher;
 639  
 640      if (!is_array($post_ids))
 641      {
 642          $post_ids = array($post_ids);
 643      }
 644  
 645      $forum_ids = array();
 646      $topic_ids = array($topic_id);
 647  
 648      $sql = 'SELECT DISTINCT topic_id, forum_id
 649          FROM ' . POSTS_TABLE . '
 650          WHERE ' . $db->sql_in_set('post_id', $post_ids);
 651      $result = $db->sql_query($sql);
 652  
 653      while ($row = $db->sql_fetchrow($result))
 654      {
 655          $forum_ids[] = (int) $row['forum_id'];
 656          $topic_ids[] = (int) $row['topic_id'];
 657      }
 658      $db->sql_freeresult($result);
 659  
 660      $sql = 'SELECT forum_id
 661          FROM ' . TOPICS_TABLE . '
 662          WHERE topic_id = ' . $topic_id;
 663      $result = $db->sql_query($sql);
 664      $forum_row = $db->sql_fetchrow($result);
 665      $db->sql_freeresult($result);
 666  
 667      if (!$forum_row)
 668      {
 669          trigger_error('NO_TOPIC');
 670      }
 671  
 672      /**
 673       * Perform additional actions before moving posts
 674       *
 675       * @event core.move_posts_before
 676       * @var    array    post_ids    Array of post ids to move
 677       * @var    int        topic_id    The topic id the posts are moved to
 678       * @var    bool    auto_sync    Whether or not to perform auto sync
 679       * @var    array    forum_ids    Array of the forum ids the posts are moved from
 680       * @var    array    topic_ids    Array of the topic ids the posts are moved from
 681       * @var    array    forum_row    Array with the forum id of the topic the posts are moved to
 682       * @since 3.1.7-RC1
 683       */
 684      $vars = array(
 685              'post_ids',
 686              'topic_id',
 687              'auto_sync',
 688              'forum_ids',
 689              'topic_ids',
 690              'forum_row',
 691      );
 692      extract($phpbb_dispatcher->trigger_event('core.move_posts_before', compact($vars)));
 693  
 694      $sql = 'UPDATE ' . POSTS_TABLE . '
 695          SET forum_id = ' . (int) $forum_row['forum_id'] . ", topic_id = $topic_id
 696          WHERE " . $db->sql_in_set('post_id', $post_ids);
 697      $db->sql_query($sql);
 698  
 699      $sql = 'UPDATE ' . ATTACHMENTS_TABLE . "
 700          SET topic_id = $topic_id, in_message = 0
 701          WHERE " . $db->sql_in_set('post_msg_id', $post_ids);
 702      $db->sql_query($sql);
 703  
 704      /**
 705       * Perform additional actions after moving posts
 706       *
 707       * @event core.move_posts_after
 708       * @var    array    post_ids    Array of the moved post ids
 709       * @var    int        topic_id    The topic id the posts are moved to
 710       * @var    bool    auto_sync    Whether or not to perform auto sync
 711       * @var    array    forum_ids    Array of the forum ids the posts are moved from
 712       * @var    array    topic_ids    Array of the topic ids the posts are moved from
 713       * @var    array    forum_row    Array with the forum id of the topic the posts are moved to
 714       * @since 3.1.7-RC1
 715       */
 716      $vars = array(
 717              'post_ids',
 718              'topic_id',
 719              'auto_sync',
 720              'forum_ids',
 721              'topic_ids',
 722              'forum_row',
 723      );
 724      extract($phpbb_dispatcher->trigger_event('core.move_posts_after', compact($vars)));
 725  
 726      if ($auto_sync)
 727      {
 728          $forum_ids[] = (int) $forum_row['forum_id'];
 729  
 730          sync('topic_reported', 'topic_id', $topic_ids);
 731          sync('topic_attachment', 'topic_id', $topic_ids);
 732          sync('topic', 'topic_id', $topic_ids, true);
 733          sync('forum', 'forum_id', $forum_ids, true, true);
 734  
 735          /**
 736           * Perform additional actions after move post sync
 737           *
 738           * @event core.move_posts_sync_after
 739           * @var    array    post_ids    Array of the moved post ids
 740           * @var    int        topic_id    The topic id the posts are moved to
 741           * @var    bool    auto_sync    Whether or not to perform auto sync
 742           * @var    array    forum_ids    Array of the forum ids the posts are moved from
 743           * @var    array    topic_ids    Array of the topic ids the posts are moved from
 744           * @var    array    forum_row    Array with the forum id of the topic the posts are moved to
 745           * @since 3.1.11-RC1
 746           */
 747          $vars = array(
 748              'post_ids',
 749              'topic_id',
 750              'auto_sync',
 751              'forum_ids',
 752              'topic_ids',
 753              'forum_row',
 754          );
 755          extract($phpbb_dispatcher->trigger_event('core.move_posts_sync_after', compact($vars)));
 756      }
 757  
 758      // Update posted information
 759      update_posted_info($topic_ids);
 760  }
 761  
 762  /**
 763  * Remove topic(s)
 764  */
 765  function delete_topics($where_type, $where_ids, $auto_sync = true, $post_count_sync = true, $call_delete_posts = true)
 766  {
 767      global $db, $config, $phpbb_container, $phpbb_dispatcher;
 768  
 769      $approved_topics = 0;
 770      $forum_ids = $topic_ids = array();
 771  
 772      if ($where_type === 'range')
 773      {
 774          $where_clause = $where_ids;
 775      }
 776      else
 777      {
 778          $where_ids = (is_array($where_ids)) ? array_unique($where_ids) : array($where_ids);
 779  
 780          if (!count($where_ids))
 781          {
 782              return array('topics' => 0, 'posts' => 0);
 783          }
 784  
 785          $where_clause = $db->sql_in_set($where_type, $where_ids);
 786      }
 787  
 788      // Making sure that delete_posts does not call delete_topics again...
 789      $return = array(
 790          'posts' => ($call_delete_posts) ? delete_posts($where_type, $where_ids, false, true, $post_count_sync, false) : 0,
 791      );
 792  
 793      $sql = 'SELECT topic_id, forum_id, topic_visibility, topic_moved_id
 794          FROM ' . TOPICS_TABLE . '
 795          WHERE ' . $where_clause;
 796      $result = $db->sql_query($sql);
 797  
 798      while ($row = $db->sql_fetchrow($result))
 799      {
 800          $forum_ids[] = $row['forum_id'];
 801          $topic_ids[] = $row['topic_id'];
 802  
 803          if ($row['topic_visibility'] == ITEM_APPROVED && !$row['topic_moved_id'])
 804          {
 805              $approved_topics++;
 806          }
 807      }
 808      $db->sql_freeresult($result);
 809  
 810      $return['topics'] = count($topic_ids);
 811  
 812      if (!count($topic_ids))
 813      {
 814          return $return;
 815      }
 816  
 817      $db->sql_transaction('begin');
 818  
 819      $table_ary = array(BOOKMARKS_TABLE, TOPICS_TRACK_TABLE, TOPICS_POSTED_TABLE, POLL_VOTES_TABLE, POLL_OPTIONS_TABLE, TOPICS_WATCH_TABLE, TOPICS_TABLE);
 820  
 821      /**
 822       * Perform additional actions before topic(s) deletion
 823       *
 824       * @event core.delete_topics_before_query
 825       * @var    array    table_ary    Array of tables from which all rows will be deleted that hold a topic_id occuring in topic_ids
 826       * @var    array    topic_ids    Array of topic ids to delete
 827       * @since 3.1.4-RC1
 828       */
 829      $vars = array(
 830              'table_ary',
 831              'topic_ids',
 832      );
 833      extract($phpbb_dispatcher->trigger_event('core.delete_topics_before_query', compact($vars)));
 834  
 835      foreach ($table_ary as $table)
 836      {
 837          $sql = "DELETE FROM $table
 838              WHERE " . $db->sql_in_set('topic_id', $topic_ids);
 839          $db->sql_query($sql);
 840      }
 841      unset($table_ary);
 842  
 843      /**
 844       * Perform additional actions after topic(s) deletion
 845       *
 846       * @event core.delete_topics_after_query
 847       * @var    array    topic_ids    Array of topic ids that were deleted
 848       * @since 3.1.4-RC1
 849       */
 850      $vars = array(
 851              'topic_ids',
 852      );
 853      extract($phpbb_dispatcher->trigger_event('core.delete_topics_after_query', compact($vars)));
 854  
 855      $moved_topic_ids = array();
 856  
 857      // update the other forums
 858      $sql = 'SELECT topic_id, forum_id
 859          FROM ' . TOPICS_TABLE . '
 860          WHERE ' . $db->sql_in_set('topic_moved_id', $topic_ids);
 861      $result = $db->sql_query($sql);
 862  
 863      while ($row = $db->sql_fetchrow($result))
 864      {
 865          $forum_ids[] = $row['forum_id'];
 866          $moved_topic_ids[] = $row['topic_id'];
 867      }
 868      $db->sql_freeresult($result);
 869  
 870      if (count($moved_topic_ids))
 871      {
 872          $sql = 'DELETE FROM ' . TOPICS_TABLE . '
 873              WHERE ' . $db->sql_in_set('topic_id', $moved_topic_ids);
 874          $db->sql_query($sql);
 875      }
 876  
 877      $db->sql_transaction('commit');
 878  
 879      if ($auto_sync)
 880      {
 881          sync('forum', 'forum_id', array_unique($forum_ids), true, true);
 882          sync('topic_reported', $where_type, $where_ids);
 883      }
 884  
 885      if ($approved_topics)
 886      {
 887          $config->increment('num_topics', $approved_topics * (-1), false);
 888      }
 889  
 890      /* @var $phpbb_notifications \phpbb\notification\manager */
 891      $phpbb_notifications = $phpbb_container->get('notification_manager');
 892  
 893      $phpbb_notifications->delete_notifications(array(
 894          'notification.type.topic',
 895          'notification.type.approve_topic',
 896          'notification.type.topic_in_queue',
 897      ), $topic_ids);
 898  
 899      return $return;
 900  }
 901  
 902  /**
 903  * Remove post(s)
 904  */
 905  function delete_posts($where_type, $where_ids, $auto_sync = true, $posted_sync = true, $post_count_sync = true, $call_delete_topics = true)
 906  {
 907      global $db, $config, $phpbb_root_path, $phpEx, $auth, $user, $phpbb_container, $phpbb_dispatcher;
 908  
 909      // Notifications types to delete
 910      $delete_notifications_types = array(
 911          'notification.type.quote',
 912          'notification.type.approve_post',
 913          'notification.type.post_in_queue',
 914          'notification.type.report_post',
 915      );
 916  
 917      /**
 918      * Perform additional actions before post(s) deletion
 919      *
 920      * @event core.delete_posts_before
 921      * @var    string    where_type                    Variable containing posts deletion mode
 922      * @var    mixed    where_ids                    Array or comma separated list of posts ids to delete
 923      * @var    bool    auto_sync                    Flag indicating if topics/forums should be synchronized
 924      * @var    bool    posted_sync                    Flag indicating if topics_posted table should be resynchronized
 925      * @var    bool    post_count_sync                Flag indicating if posts count should be resynchronized
 926      * @var    bool    call_delete_topics            Flag indicating if topics having no posts should be deleted
 927      * @var    array    delete_notifications_types    Array with notifications types to delete
 928      * @since 3.1.0-a4
 929      */
 930      $vars = array(
 931          'where_type',
 932          'where_ids',
 933          'auto_sync',
 934          'posted_sync',
 935          'post_count_sync',
 936          'call_delete_topics',
 937          'delete_notifications_types',
 938      );
 939      extract($phpbb_dispatcher->trigger_event('core.delete_posts_before', compact($vars)));
 940  
 941      if ($where_type === 'range')
 942      {
 943          $where_clause = $where_ids;
 944      }
 945      else
 946      {
 947          if (is_array($where_ids))
 948          {
 949              $where_ids = array_unique($where_ids);
 950          }
 951          else
 952          {
 953              $where_ids = array($where_ids);
 954          }
 955  
 956          if (!count($where_ids))
 957          {
 958              return false;
 959          }
 960  
 961          $where_ids = array_map('intval', $where_ids);
 962  
 963  /*        Possible code for splitting post deletion
 964          if (count($where_ids) >= 1001)
 965          {
 966              // Split into chunks of 1000
 967              $chunks = array_chunk($where_ids, 1000);
 968  
 969              foreach ($chunks as $_where_ids)
 970              {
 971                  delete_posts($where_type, $_where_ids, $auto_sync, $posted_sync, $post_count_sync, $call_delete_topics);
 972              }
 973  
 974              return;
 975          }*/
 976  
 977          $where_clause = $db->sql_in_set($where_type, $where_ids);
 978      }
 979  
 980      $approved_posts = 0;
 981      $post_ids = $topic_ids = $forum_ids = $post_counts = $remove_topics = array();
 982  
 983      $sql = 'SELECT post_id, poster_id, post_visibility, post_postcount, topic_id, forum_id
 984          FROM ' . POSTS_TABLE . '
 985          WHERE ' . $where_clause;
 986      $result = $db->sql_query($sql);
 987  
 988      while ($row = $db->sql_fetchrow($result))
 989      {
 990          $post_ids[] = (int) $row['post_id'];
 991          $poster_ids[] = (int) $row['poster_id'];
 992          $topic_ids[] = (int) $row['topic_id'];
 993          $forum_ids[] = (int) $row['forum_id'];
 994  
 995          if ($row['post_postcount'] && $post_count_sync && $row['post_visibility'] == ITEM_APPROVED)
 996          {
 997              $post_counts[$row['poster_id']] = (!empty($post_counts[$row['poster_id']])) ? $post_counts[$row['poster_id']] + 1 : 1;
 998          }
 999  
1000          if ($row['post_visibility'] == ITEM_APPROVED)
1001          {
1002              $approved_posts++;
1003          }
1004      }
1005      $db->sql_freeresult($result);
1006  
1007      if (!count($post_ids))
1008      {
1009          return false;
1010      }
1011  
1012      $db->sql_transaction('begin');
1013  
1014      $table_ary = array(POSTS_TABLE, REPORTS_TABLE);
1015  
1016      /**
1017      * Perform additional actions during post(s) deletion before running the queries
1018      *
1019      * @event core.delete_posts_in_transaction_before
1020      * @var    array    post_ids                    Array with deleted posts' ids
1021      * @var    array    poster_ids                    Array with deleted posts' author ids
1022      * @var    array    topic_ids                    Array with deleted posts' topic ids
1023      * @var    array    forum_ids                    Array with deleted posts' forum ids
1024      * @var    string    where_type                    Variable containing posts deletion mode
1025      * @var    mixed    where_ids                    Array or comma separated list of post ids to delete
1026      * @var    array    delete_notifications_types    Array with notifications types to delete
1027      * @var    array    table_ary                    Array with table names to delete data from
1028      * @since 3.1.7-RC1
1029      */
1030      $vars = array(
1031          'post_ids',
1032          'poster_ids',
1033          'topic_ids',
1034          'forum_ids',
1035          'where_type',
1036          'where_ids',
1037          'delete_notifications_types',
1038          'table_ary',
1039      );
1040      extract($phpbb_dispatcher->trigger_event('core.delete_posts_in_transaction_before', compact($vars)));
1041  
1042      foreach ($table_ary as $table)
1043      {
1044          $sql = "DELETE FROM $table
1045              WHERE " . $db->sql_in_set('post_id', $post_ids);
1046          $db->sql_query($sql);
1047      }
1048      unset($table_ary);
1049  
1050      // Adjust users post counts
1051      if (count($post_counts) && $post_count_sync)
1052      {
1053          foreach ($post_counts as $poster_id => $substract)
1054          {
1055              $sql = 'UPDATE ' . USERS_TABLE . '
1056                  SET user_posts = 0
1057                  WHERE user_id = ' . $poster_id . '
1058                  AND user_posts < ' . $substract;
1059              $db->sql_query($sql);
1060  
1061              $sql = 'UPDATE ' . USERS_TABLE . '
1062                  SET user_posts = user_posts - ' . $substract . '
1063                  WHERE user_id = ' . $poster_id . '
1064                  AND user_posts >= ' . $substract;
1065              $db->sql_query($sql);
1066          }
1067      }
1068  
1069      // Remove topics now having no posts?
1070      if (count($topic_ids))
1071      {
1072          $sql = 'SELECT topic_id
1073              FROM ' . POSTS_TABLE . '
1074              WHERE ' . $db->sql_in_set('topic_id', $topic_ids) . '
1075              GROUP BY topic_id';
1076          $result = $db->sql_query($sql);
1077  
1078          while ($row = $db->sql_fetchrow($result))
1079          {
1080              $remove_topics[] = $row['topic_id'];
1081          }
1082          $db->sql_freeresult($result);
1083  
1084          // Actually, those not within remove_topics should be removed. ;)
1085          $remove_topics = array_diff($topic_ids, $remove_topics);
1086      }
1087  
1088      // Remove the message from the search index
1089      $search_type = $config['search_type'];
1090  
1091      if (!class_exists($search_type))
1092      {
1093          trigger_error('NO_SUCH_SEARCH_MODULE');
1094      }
1095  
1096      $error = false;
1097      $search = new $search_type($error, $phpbb_root_path, $phpEx, $auth, $config, $db, $user, $phpbb_dispatcher);
1098  
1099      if ($error)
1100      {
1101          trigger_error($error);
1102      }
1103  
1104      $search->index_remove($post_ids, $poster_ids, $forum_ids);
1105  
1106      /** @var \phpbb\attachment\manager $attachment_manager */
1107      $attachment_manager = $phpbb_container->get('attachment.manager');
1108      $attachment_manager->delete('post', $post_ids, false);
1109      unset($attachment_manager);
1110  
1111      /**
1112      * Perform additional actions during post(s) deletion
1113      *
1114      * @event core.delete_posts_in_transaction
1115      * @var    array    post_ids                    Array with deleted posts' ids
1116      * @var    array    poster_ids                    Array with deleted posts' author ids
1117      * @var    array    topic_ids                    Array with deleted posts' topic ids
1118      * @var    array    forum_ids                    Array with deleted posts' forum ids
1119      * @var    string    where_type                    Variable containing posts deletion mode
1120      * @var    mixed    where_ids                    Array or comma separated list of posts ids to delete
1121      * @var    array    delete_notifications_types    Array with notifications types to delete
1122      * @since 3.1.0-a4
1123      */
1124      $vars = array(
1125          'post_ids',
1126          'poster_ids',
1127          'topic_ids',
1128          'forum_ids',
1129          'where_type',
1130          'where_ids',
1131          'delete_notifications_types',
1132      );
1133      extract($phpbb_dispatcher->trigger_event('core.delete_posts_in_transaction', compact($vars)));
1134  
1135      $db->sql_transaction('commit');
1136  
1137      /**
1138      * Perform additional actions after post(s) deletion
1139      *
1140      * @event core.delete_posts_after
1141      * @var    array    post_ids                    Array with deleted posts' ids
1142      * @var    array    poster_ids                    Array with deleted posts' author ids
1143      * @var    array    topic_ids                    Array with deleted posts' topic ids
1144      * @var    array    forum_ids                    Array with deleted posts' forum ids
1145      * @var    string    where_type                    Variable containing posts deletion mode
1146      * @var    mixed    where_ids                    Array or comma separated list of posts ids to delete
1147      * @var    array    delete_notifications_types    Array with notifications types to delete
1148      * @since 3.1.0-a4
1149      */
1150      $vars = array(
1151          'post_ids',
1152          'poster_ids',
1153          'topic_ids',
1154          'forum_ids',
1155          'where_type',
1156          'where_ids',
1157          'delete_notifications_types',
1158      );
1159      extract($phpbb_dispatcher->trigger_event('core.delete_posts_after', compact($vars)));
1160  
1161      // Resync topics_posted table
1162      if ($posted_sync)
1163      {
1164          update_posted_info($topic_ids);
1165      }
1166  
1167      if ($auto_sync)
1168      {
1169          sync('topic_reported', 'topic_id', $topic_ids);
1170          sync('topic', 'topic_id', $topic_ids, true);
1171          sync('forum', 'forum_id', $forum_ids, true, true);
1172      }
1173  
1174      if ($approved_posts && $post_count_sync)
1175      {
1176          $config->increment('num_posts', $approved_posts * (-1), false);
1177      }
1178  
1179      // We actually remove topics now to not be inconsistent (the delete_topics function calls this function too)
1180      if (count($remove_topics) && $call_delete_topics)
1181      {
1182          delete_topics('topic_id', $remove_topics, $auto_sync, $post_count_sync, false);
1183      }
1184  
1185      /* @var $phpbb_notifications \phpbb\notification\manager */
1186      $phpbb_notifications = $phpbb_container->get('notification_manager');
1187  
1188      $phpbb_notifications->delete_notifications($delete_notifications_types, $post_ids);
1189  
1190      return count($post_ids);
1191  }
1192  
1193  /**
1194  * Deletes shadow topics pointing to a specified forum.
1195  *
1196  * @param int        $forum_id        The forum id
1197  * @param string        $sql_more        Additional WHERE statement, e.g. t.topic_time < (time() - 1234)
1198  * @param bool        $auto_sync        Will call sync() if this is true
1199  *
1200  * @return array        Array with affected forums
1201  */
1202  function delete_topic_shadows($forum_id, $sql_more = '', $auto_sync = true)
1203  {
1204      global $db;
1205  
1206      if (!$forum_id)
1207      {
1208          // Nothing to do.
1209          return;
1210      }
1211  
1212      // Set of affected forums we have to resync
1213      $sync_forum_ids = array();
1214  
1215      // Amount of topics we select and delete at once.
1216      $batch_size = 500;
1217  
1218      do
1219      {
1220          $sql = 'SELECT t2.forum_id, t2.topic_id
1221              FROM ' . TOPICS_TABLE . ' t2, ' . TOPICS_TABLE . ' t
1222              WHERE t2.topic_moved_id = t.topic_id
1223                  AND t.forum_id = ' . (int) $forum_id . '
1224                  ' . (($sql_more) ? 'AND ' . $sql_more : '');
1225          $result = $db->sql_query_limit($sql, $batch_size);
1226  
1227          $topic_ids = array();
1228          while ($row = $db->sql_fetchrow($result))
1229          {
1230              $topic_ids[] = (int) $row['topic_id'];
1231  
1232              $sync_forum_ids[(int) $row['forum_id']] = (int) $row['forum_id'];
1233          }
1234          $db->sql_freeresult($result);
1235  
1236          if (!empty($topic_ids))
1237          {
1238              $sql = 'DELETE FROM ' . TOPICS_TABLE . '
1239                  WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
1240              $db->sql_query($sql);
1241          }
1242      }
1243      while (count($topic_ids) == $batch_size);
1244  
1245      if ($auto_sync)
1246      {
1247          sync('forum', 'forum_id', $sync_forum_ids, true, true);
1248      }
1249  
1250      return $sync_forum_ids;
1251  }
1252  
1253  /**
1254  * Update/Sync posted information for topics
1255  */
1256  function update_posted_info(&$topic_ids)
1257  {
1258      global $db, $config;
1259  
1260      if (empty($topic_ids) || !$config['load_db_track'])
1261      {
1262          return;
1263      }
1264  
1265      // First of all, let us remove any posted information for these topics
1266      $sql = 'DELETE FROM ' . TOPICS_POSTED_TABLE . '
1267          WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
1268      $db->sql_query($sql);
1269  
1270      // Now, let us collect the user/topic combos for rebuilding the information
1271      $sql = 'SELECT poster_id, topic_id
1272          FROM ' . POSTS_TABLE . '
1273          WHERE ' . $db->sql_in_set('topic_id', $topic_ids) . '
1274              AND poster_id <> ' . ANONYMOUS . '
1275          GROUP BY poster_id, topic_id';
1276      $result = $db->sql_query($sql);
1277  
1278      $posted = array();
1279      while ($row = $db->sql_fetchrow($result))
1280      {
1281          // Add as key to make them unique (grouping by) and circumvent empty keys on array_unique
1282          $posted[$row['poster_id']][] = $row['topic_id'];
1283      }
1284      $db->sql_freeresult($result);
1285  
1286      // Now add the information...
1287      $sql_ary = array();
1288      foreach ($posted as $user_id => $topic_row)
1289      {
1290          foreach ($topic_row as $topic_id)
1291          {
1292              $sql_ary[] = array(
1293                  'user_id'        => (int) $user_id,
1294                  'topic_id'        => (int) $topic_id,
1295                  'topic_posted'    => 1,
1296              );
1297          }
1298      }
1299      unset($posted);
1300  
1301      $db->sql_multi_insert(TOPICS_POSTED_TABLE, $sql_ary);
1302  }
1303  
1304  /**
1305  * All-encompasing sync function
1306  *
1307  * Exaples:
1308  * <code>
1309  * sync('topic', 'topic_id', 123);            // resync topic #123
1310  * sync('topic', 'forum_id', array(2, 3));    // resync topics from forum #2 and #3
1311  * sync('topic');                            // resync all topics
1312  * sync('topic', 'range', 'topic_id BETWEEN 1 AND 60');    // resync a range of topics/forums (only available for 'topic' and 'forum' modes)
1313  * </code>
1314  *
1315  * Modes:
1316  * - forum                Resync complete forum
1317  * - topic                Resync topics
1318  * - topic_moved            Removes topic shadows that would be in the same forum as the topic they link to
1319  * - topic_visibility    Resyncs the topic_visibility flag according to the status of the first post
1320  * - post_reported        Resyncs the post_reported flag, relying on actual reports
1321  * - topic_reported        Resyncs the topic_reported flag, relying on post_reported flags
1322  * - post_attachement    Same as post_reported, but with attachment flags
1323  * - topic_attachement    Same as topic_reported, but with attachment flags
1324  */
1325  function sync($mode, $where_type = '', $where_ids = '', $resync_parents = false, $sync_extra = false)
1326  {
1327      global $db;
1328  
1329      if (is_array($where_ids))
1330      {
1331          $where_ids = array_unique($where_ids);
1332          $where_ids = array_map('intval', $where_ids);
1333      }
1334      else if ($where_type != 'range')
1335      {
1336          $where_ids = ($where_ids) ? array((int) $where_ids) : array();
1337      }
1338  
1339      if ($mode == 'forum' || $mode == 'topic' || $mode == 'topic_visibility' || $mode == 'topic_reported' || $mode == 'post_reported')
1340      {
1341          if (!$where_type)
1342          {
1343              $where_sql = '';
1344              $where_sql_and = 'WHERE';
1345          }
1346          else if ($where_type == 'range')
1347          {
1348              // Only check a range of topics/forums. For instance: 'topic_id BETWEEN 1 AND 60'
1349              $where_sql = 'WHERE (' . $mode[0] . ".$where_ids)";
1350              $where_sql_and = $where_sql . "\n\tAND";
1351          }
1352          else
1353          {
1354              // Do not sync the "global forum"
1355              $where_ids = array_diff($where_ids, array(0));
1356  
1357              if (!count($where_ids))
1358              {
1359                  // Empty array with IDs. This means that we don't have any work to do. Just return.
1360                  return;
1361              }
1362  
1363              // Limit the topics/forums we are syncing, use specific topic/forum IDs.
1364              // $where_type contains the field for the where clause (forum_id, topic_id)
1365              $where_sql = 'WHERE ' . $db->sql_in_set($mode[0] . '.' . $where_type, $where_ids);
1366              $where_sql_and = $where_sql . "\n\tAND";
1367          }
1368      }
1369      else
1370      {
1371          if (!count($where_ids))
1372          {
1373              return;
1374          }
1375  
1376          // $where_type contains the field for the where clause (forum_id, topic_id)
1377          $where_sql = 'WHERE ' . $db->sql_in_set($mode[0] . '.' . $where_type, $where_ids);
1378          $where_sql_and = $where_sql . "\n\tAND";
1379      }
1380  
1381      switch ($mode)
1382      {
1383          case 'topic_moved':
1384              $db->sql_transaction('begin');
1385              switch ($db->get_sql_layer())
1386              {
1387                  case 'mysqli':
1388                      $sql = 'DELETE FROM ' . TOPICS_TABLE . '
1389                          USING ' . TOPICS_TABLE . ' t1, ' . TOPICS_TABLE . " t2
1390                          WHERE t1.topic_moved_id = t2.topic_id
1391                              AND t1.forum_id = t2.forum_id";
1392                      $db->sql_query($sql);
1393                  break;
1394  
1395                  default:
1396                      $sql = 'SELECT t1.topic_id
1397                          FROM ' .TOPICS_TABLE . ' t1, ' . TOPICS_TABLE . " t2
1398                          WHERE t1.topic_moved_id = t2.topic_id
1399                              AND t1.forum_id = t2.forum_id";
1400                      $result = $db->sql_query($sql);
1401  
1402                      $topic_id_ary = array();
1403                      while ($row = $db->sql_fetchrow($result))
1404                      {
1405                          $topic_id_ary[] = $row['topic_id'];
1406                      }
1407                      $db->sql_freeresult($result);
1408  
1409                      if (!count($topic_id_ary))
1410                      {
1411                          return;
1412                      }
1413  
1414                      $sql = 'DELETE FROM ' . TOPICS_TABLE . '
1415                          WHERE ' . $db->sql_in_set('topic_id', $topic_id_ary);
1416                      $db->sql_query($sql);
1417  
1418                  break;
1419              }
1420  
1421              $db->sql_transaction('commit');
1422              break;
1423  
1424          case 'topic_visibility':
1425  
1426              $db->sql_transaction('begin');
1427  
1428              $sql = 'SELECT t.topic_id, p.post_visibility
1429                  FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
1430                  $where_sql_and p.topic_id = t.topic_id
1431                      AND p.post_visibility = " . ITEM_APPROVED;
1432              $result = $db->sql_query($sql);
1433  
1434              $topics_approved = array();
1435              while ($row = $db->sql_fetchrow($result))
1436              {
1437                  $topics_approved[] = (int) $row['topic_id'];
1438              }
1439              $db->sql_freeresult($result);
1440  
1441              $sql = 'SELECT t.topic_id, p.post_visibility
1442                  FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
1443                  $where_sql_and " . $db->sql_in_set('t.topic_id', $topics_approved, true, true) . '
1444                      AND p.topic_id = t.topic_id
1445                      AND p.post_visibility = ' . ITEM_DELETED;
1446              $result = $db->sql_query($sql);
1447  
1448              $topics_softdeleted = array();
1449              while ($row = $db->sql_fetchrow($result))
1450              {
1451                  $topics_softdeleted[] = (int) $row['topic_id'];
1452              }
1453              $db->sql_freeresult($result);
1454  
1455              $topics_softdeleted = array_diff($topics_softdeleted, $topics_approved);
1456              $topics_not_unapproved = array_merge($topics_softdeleted, $topics_approved);
1457  
1458              $update_ary = array(
1459                  ITEM_UNAPPROVED    => (!empty($topics_not_unapproved)) ? $where_sql_and . ' ' . $db->sql_in_set('topic_id', $topics_not_unapproved, true) : '',
1460                  ITEM_APPROVED    => (!empty($topics_approved)) ? ' WHERE ' . $db->sql_in_set('topic_id', $topics_approved) : '',
1461                  ITEM_DELETED    => (!empty($topics_softdeleted)) ? ' WHERE ' . $db->sql_in_set('topic_id', $topics_softdeleted) : '',
1462              );
1463  
1464              foreach ($update_ary as $visibility => $sql_where)
1465              {
1466                  if ($sql_where)
1467                  {
1468                      $sql = 'UPDATE ' . TOPICS_TABLE . '
1469                          SET topic_visibility = ' . $visibility . '
1470                          ' . $sql_where;
1471                      $db->sql_query($sql);
1472                  }
1473              }
1474  
1475              $db->sql_transaction('commit');
1476              break;
1477  
1478          case 'post_reported':
1479              $post_ids = $post_reported = array();
1480  
1481              $db->sql_transaction('begin');
1482  
1483              $sql = 'SELECT p.post_id, p.post_reported
1484                  FROM ' . POSTS_TABLE . " p
1485                  $where_sql
1486                  GROUP BY p.post_id, p.post_reported";
1487              $result = $db->sql_query($sql);
1488  
1489              while ($row = $db->sql_fetchrow($result))
1490              {
1491                  $post_ids[$row['post_id']] = $row['post_id'];
1492                  if ($row['post_reported'])
1493                  {
1494                      $post_reported[$row['post_id']] = 1;
1495                  }
1496              }
1497              $db->sql_freeresult($result);
1498  
1499              $sql = 'SELECT DISTINCT(post_id)
1500                  FROM ' . REPORTS_TABLE . '
1501                  WHERE ' . $db->sql_in_set('post_id', $post_ids) . '
1502                      AND report_closed = 0';
1503              $result = $db->sql_query($sql);
1504  
1505              $post_ids = array();
1506              while ($row = $db->sql_fetchrow($result))
1507              {
1508                  if (!isset($post_reported[$row['post_id']]))
1509                  {
1510                      $post_ids[] = $row['post_id'];
1511                  }
1512                  else
1513                  {
1514                      unset($post_reported[$row['post_id']]);
1515                  }
1516              }
1517              $db->sql_freeresult($result);
1518  
1519              // $post_reported should be empty by now, if it's not it contains
1520              // posts that are falsely flagged as reported
1521              foreach ($post_reported as $post_id => $void)
1522              {
1523                  $post_ids[] = $post_id;
1524              }
1525  
1526              if (count($post_ids))
1527              {
1528                  $sql = 'UPDATE ' . POSTS_TABLE . '
1529                      SET post_reported = 1 - post_reported
1530                      WHERE ' . $db->sql_in_set('post_id', $post_ids);
1531                  $db->sql_query($sql);
1532              }
1533  
1534              $db->sql_transaction('commit');
1535              break;
1536  
1537          case 'topic_reported':
1538              if ($sync_extra)
1539              {
1540                  sync('post_reported', $where_type, $where_ids);
1541              }
1542  
1543              $topic_ids = $topic_reported = array();
1544  
1545              $db->sql_transaction('begin');
1546  
1547              $sql = 'SELECT DISTINCT(t.topic_id)
1548                  FROM ' . POSTS_TABLE . " t
1549                  $where_sql_and t.post_reported = 1";
1550              $result = $db->sql_query($sql);
1551  
1552              while ($row = $db->sql_fetchrow($result))
1553              {
1554                  $topic_reported[$row['topic_id']] = 1;
1555              }
1556              $db->sql_freeresult($result);
1557  
1558              $sql = 'SELECT t.topic_id, t.topic_reported
1559                  FROM ' . TOPICS_TABLE . " t
1560                  $where_sql";
1561              $result = $db->sql_query($sql);
1562  
1563              while ($row = $db->sql_fetchrow($result))
1564              {
1565                  if ($row['topic_reported'] ^ isset($topic_reported[$row['topic_id']]))
1566                  {
1567                      $topic_ids[] = $row['topic_id'];
1568                  }
1569              }
1570              $db->sql_freeresult($result);
1571  
1572              if (count($topic_ids))
1573              {
1574                  $sql = 'UPDATE ' . TOPICS_TABLE . '
1575                      SET topic_reported = 1 - topic_reported
1576                      WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
1577                  $db->sql_query($sql);
1578              }
1579  
1580              $db->sql_transaction('commit');
1581              break;
1582  
1583          case 'post_attachment':
1584              $post_ids = $post_attachment = array();
1585  
1586              $db->sql_transaction('begin');
1587  
1588              $sql = 'SELECT p.post_id, p.post_attachment
1589                  FROM ' . POSTS_TABLE . " p
1590                  $where_sql
1591                  GROUP BY p.post_id, p.post_attachment";
1592              $result = $db->sql_query($sql);
1593  
1594              while ($row = $db->sql_fetchrow($result))
1595              {
1596                  $post_ids[$row['post_id']] = $row['post_id'];
1597                  if ($row['post_attachment'])
1598                  {
1599                      $post_attachment[$row['post_id']] = 1;
1600                  }
1601              }
1602              $db->sql_freeresult($result);
1603  
1604              $sql = 'SELECT DISTINCT(post_msg_id)
1605                  FROM ' . ATTACHMENTS_TABLE . '
1606                  WHERE ' . $db->sql_in_set('post_msg_id', $post_ids) . '
1607                      AND in_message = 0';
1608              $result = $db->sql_query($sql);
1609  
1610              $post_ids = array();
1611              while ($row = $db->sql_fetchrow($result))
1612              {
1613                  if (!isset($post_attachment[$row['post_msg_id']]))
1614                  {
1615                      $post_ids[] = $row['post_msg_id'];
1616                  }
1617                  else
1618                  {
1619                      unset($post_attachment[$row['post_msg_id']]);
1620                  }
1621              }
1622              $db->sql_freeresult($result);
1623  
1624              // $post_attachment should be empty by now, if it's not it contains
1625              // posts that are falsely flagged as having attachments
1626              foreach ($post_attachment as $post_id => $void)
1627              {
1628                  $post_ids[] = $post_id;
1629              }
1630  
1631              if (count($post_ids))
1632              {
1633                  $sql = 'UPDATE ' . POSTS_TABLE . '
1634                      SET post_attachment = 1 - post_attachment
1635                      WHERE ' . $db->sql_in_set('post_id', $post_ids);
1636                  $db->sql_query($sql);
1637              }
1638  
1639              $db->sql_transaction('commit');
1640              break;
1641  
1642          case 'topic_attachment':
1643              if ($sync_extra)
1644              {
1645                  sync('post_attachment', $where_type, $where_ids);
1646              }
1647  
1648              $topic_ids = $topic_attachment = array();
1649  
1650              $db->sql_transaction('begin');
1651  
1652              $sql = 'SELECT DISTINCT(t.topic_id)
1653                  FROM ' . POSTS_TABLE . " t
1654                  $where_sql_and t.post_attachment = 1";
1655              $result = $db->sql_query($sql);
1656  
1657              while ($row = $db->sql_fetchrow($result))
1658              {
1659                  $topic_attachment[$row['topic_id']] = 1;
1660              }
1661              $db->sql_freeresult($result);
1662  
1663              $sql = 'SELECT t.topic_id, t.topic_attachment
1664                  FROM ' . TOPICS_TABLE . " t
1665                  $where_sql";
1666              $result = $db->sql_query($sql);
1667  
1668              while ($row = $db->sql_fetchrow($result))
1669              {
1670                  if ($row['topic_attachment'] ^ isset($topic_attachment[$row['topic_id']]))
1671                  {
1672                      $topic_ids[] = $row['topic_id'];
1673                  }
1674              }
1675              $db->sql_freeresult($result);
1676  
1677              if (count($topic_ids))
1678              {
1679                  $sql = 'UPDATE ' . TOPICS_TABLE . '
1680                      SET topic_attachment = 1 - topic_attachment
1681                      WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
1682                  $db->sql_query($sql);
1683              }
1684  
1685              $db->sql_transaction('commit');
1686  
1687              break;
1688  
1689          case 'forum':
1690  
1691              $db->sql_transaction('begin');
1692  
1693              // 1: Get the list of all forums
1694              $sql = 'SELECT f.*
1695                  FROM ' . FORUMS_TABLE . " f
1696                  $where_sql";
1697              $result = $db->sql_query($sql);
1698  
1699              $forum_data = $forum_ids = $post_ids = $last_post_id = $post_info = array();
1700              while ($row = $db->sql_fetchrow($result))
1701              {
1702                  if ($row['forum_type'] == FORUM_LINK)
1703                  {
1704                      continue;
1705                  }
1706  
1707                  $forum_id = (int) $row['forum_id'];
1708                  $forum_ids[$forum_id] = $forum_id;
1709  
1710                  $forum_data[$forum_id] = $row;
1711                  if ($sync_extra)
1712                  {
1713                      $forum_data[$forum_id]['posts_approved'] = 0;
1714                      $forum_data[$forum_id]['posts_unapproved'] = 0;
1715                      $forum_data[$forum_id]['posts_softdeleted'] = 0;
1716                      $forum_data[$forum_id]['topics_approved'] = 0;
1717                      $forum_data[$forum_id]['topics_unapproved'] = 0;
1718                      $forum_data[$forum_id]['topics_softdeleted'] = 0;
1719                  }
1720                  $forum_data[$forum_id]['last_post_id'] = 0;
1721                  $forum_data[$forum_id]['last_post_subject'] = '';
1722                  $forum_data[$forum_id]['last_post_time'] = 0;
1723                  $forum_data[$forum_id]['last_poster_id'] = 0;
1724                  $forum_data[$forum_id]['last_poster_name'] = '';
1725                  $forum_data[$forum_id]['last_poster_colour'] = '';
1726              }
1727              $db->sql_freeresult($result);
1728  
1729              if (!count($forum_ids))
1730              {
1731                  break;
1732              }
1733  
1734              $forum_ids = array_values($forum_ids);
1735  
1736              // 2: Get topic counts for each forum (optional)
1737              if ($sync_extra)
1738              {
1739                  $sql = 'SELECT forum_id, topic_visibility, COUNT(topic_id) AS total_topics
1740                      FROM ' . TOPICS_TABLE . '
1741                      WHERE ' . $db->sql_in_set('forum_id', $forum_ids) . '
1742                      GROUP BY forum_id, topic_visibility';
1743                  $result = $db->sql_query($sql);
1744  
1745                  while ($row = $db->sql_fetchrow($result))
1746                  {
1747                      $forum_id = (int) $row['forum_id'];
1748  
1749                      if ($row['topic_visibility'] == ITEM_APPROVED)
1750                      {
1751                          $forum_data[$forum_id]['topics_approved'] = $row['total_topics'];
1752                      }
1753                      else if ($row['topic_visibility'] == ITEM_UNAPPROVED || $row['topic_visibility'] == ITEM_REAPPROVE)
1754                      {
1755                          $forum_data[$forum_id]['topics_unapproved'] = $row['total_topics'];
1756                      }
1757                      else if ($row['topic_visibility'] == ITEM_DELETED)
1758                      {
1759                          $forum_data[$forum_id]['topics_softdeleted'] = $row['total_topics'];
1760                      }
1761                  }
1762                  $db->sql_freeresult($result);
1763              }
1764  
1765              // 3: Get post count for each forum (optional)
1766              if ($sync_extra)
1767              {
1768                  if (count($forum_ids) == 1)
1769                  {
1770                      $sql = 'SELECT SUM(t.topic_posts_approved) AS forum_posts_approved, SUM(t.topic_posts_unapproved) AS forum_posts_unapproved, SUM(t.topic_posts_softdeleted) AS forum_posts_softdeleted
1771                          FROM ' . TOPICS_TABLE . ' t
1772                          WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
1773                              AND t.topic_status <> ' . ITEM_MOVED;
1774                  }
1775                  else
1776                  {
1777                      $sql = 'SELECT t.forum_id, SUM(t.topic_posts_approved) AS forum_posts_approved, SUM(t.topic_posts_unapproved) AS forum_posts_unapproved, SUM(t.topic_posts_softdeleted) AS forum_posts_softdeleted
1778                          FROM ' . TOPICS_TABLE . ' t
1779                          WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
1780                              AND t.topic_status <> ' . ITEM_MOVED . '
1781                          GROUP BY t.forum_id';
1782                  }
1783  
1784                  $result = $db->sql_query($sql);
1785  
1786                  while ($row = $db->sql_fetchrow($result))
1787                  {
1788                      $forum_id = (count($forum_ids) == 1) ? (int) $forum_ids[0] : (int) $row['forum_id'];
1789  
1790                      $forum_data[$forum_id]['posts_approved'] = (int) $row['forum_posts_approved'];
1791                      $forum_data[$forum_id]['posts_unapproved'] = (int) $row['forum_posts_unapproved'];
1792                      $forum_data[$forum_id]['posts_softdeleted'] = (int) $row['forum_posts_softdeleted'];
1793                  }
1794                  $db->sql_freeresult($result);
1795              }
1796  
1797              // 4: Get last_post_id for each forum
1798              if (count($forum_ids) == 1)
1799              {
1800                  $sql = 'SELECT MAX(t.topic_last_post_id) as last_post_id
1801                      FROM ' . TOPICS_TABLE . ' t
1802                      WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
1803                          AND t.topic_visibility = ' . ITEM_APPROVED;
1804              }
1805              else
1806              {
1807                  $sql = 'SELECT t.forum_id, MAX(t.topic_last_post_id) as last_post_id
1808                      FROM ' . TOPICS_TABLE . ' t
1809                      WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
1810                          AND t.topic_visibility = ' . ITEM_APPROVED . '
1811                      GROUP BY t.forum_id';
1812              }
1813  
1814              $result = $db->sql_query($sql);
1815  
1816              while ($row = $db->sql_fetchrow($result))
1817              {
1818                  $forum_id = (count($forum_ids) == 1) ? (int) $forum_ids[0] : (int) $row['forum_id'];
1819  
1820                  $forum_data[$forum_id]['last_post_id'] = (int) $row['last_post_id'];
1821  
1822                  $post_ids[] = $row['last_post_id'];
1823              }
1824              $db->sql_freeresult($result);
1825  
1826              // 5: Retrieve last_post infos
1827              if (count($post_ids))
1828              {
1829                  $sql = 'SELECT p.post_id, p.poster_id, p.post_subject, p.post_time, p.post_username, u.username, u.user_colour
1830                      FROM ' . POSTS_TABLE . ' p, ' . USERS_TABLE . ' u
1831                      WHERE ' . $db->sql_in_set('p.post_id', $post_ids) . '
1832                          AND p.poster_id = u.user_id';
1833                  $result = $db->sql_query($sql);
1834  
1835                  while ($row = $db->sql_fetchrow($result))
1836                  {
1837                      $post_info[$row['post_id']] = $row;
1838                  }
1839                  $db->sql_freeresult($result);
1840  
1841                  foreach ($forum_data as $forum_id => $data)
1842                  {
1843                      if ($data['last_post_id'])
1844                      {
1845                          if (isset($post_info[$data['last_post_id']]))
1846                          {
1847                              $forum_data[$forum_id]['last_post_subject'] = $post_info[$data['last_post_id']]['post_subject'];
1848                              $forum_data[$forum_id]['last_post_time'] = $post_info[$data['last_post_id']]['post_time'];
1849                              $forum_data[$forum_id]['last_poster_id'] = $post_info[$data['last_post_id']]['poster_id'];
1850                              $forum_data[$forum_id]['last_poster_name'] = ($post_info[$data['last_post_id']]['poster_id'] != ANONYMOUS) ? $post_info[$data['last_post_id']]['username'] : $post_info[$data['last_post_id']]['post_username'];
1851                              $forum_data[$forum_id]['last_poster_colour'] = $post_info[$data['last_post_id']]['user_colour'];
1852                          }
1853                          else
1854                          {
1855                              // For some reason we did not find the post in the db
1856                              $forum_data[$forum_id]['last_post_id'] = 0;
1857                              $forum_data[$forum_id]['last_post_subject'] = '';
1858                              $forum_data[$forum_id]['last_post_time'] = 0;
1859                              $forum_data[$forum_id]['last_poster_id'] = 0;
1860                              $forum_data[$forum_id]['last_poster_name'] = '';
1861                              $forum_data[$forum_id]['last_poster_colour'] = '';
1862                          }
1863                      }
1864                  }
1865                  unset($post_info);
1866              }
1867  
1868              // 6: Now do that thing
1869              $fieldnames = array('last_post_id', 'last_post_subject', 'last_post_time', 'last_poster_id', 'last_poster_name', 'last_poster_colour');
1870  
1871              if ($sync_extra)
1872              {
1873                  array_push($fieldnames, 'posts_approved', 'posts_unapproved', 'posts_softdeleted', 'topics_approved', 'topics_unapproved', 'topics_softdeleted');
1874              }
1875  
1876              foreach ($forum_data as $forum_id => $row)
1877              {
1878                  $sql_ary = array();
1879  
1880                  foreach ($fieldnames as $fieldname)
1881                  {
1882                      if ($row['forum_' . $fieldname] != $row[$fieldname])
1883                      {
1884                          if (preg_match('#(name|colour|subject)$#', $fieldname))
1885                          {
1886                              $sql_ary['forum_' . $fieldname] = (string) $row[$fieldname];
1887                          }
1888                          else
1889                          {
1890                              $sql_ary['forum_' . $fieldname] = (int) $row[$fieldname];
1891                          }
1892                      }
1893                  }
1894  
1895                  if (count($sql_ary))
1896                  {
1897                      $sql = 'UPDATE ' . FORUMS_TABLE . '
1898                          SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '
1899                          WHERE forum_id = ' . $forum_id;
1900                      $db->sql_query($sql);
1901                  }
1902              }
1903  
1904              $db->sql_transaction('commit');
1905              break;
1906  
1907          case 'topic':
1908              $topic_data = $post_ids = $resync_forums = $delete_topics = $delete_posts = $moved_topics = array();
1909  
1910              $db->sql_transaction('begin');
1911  
1912              $sql = 'SELECT t.topic_id, t.forum_id, t.topic_moved_id, t.topic_visibility, ' . (($sync_extra) ? 't.topic_attachment, t.topic_reported, ' : '') . 't.topic_poster, t.topic_time, t.topic_posts_approved, t.topic_posts_unapproved, t.topic_posts_softdeleted, t.topic_first_post_id, t.topic_first_poster_name, t.topic_first_poster_colour, t.topic_last_post_id, t.topic_last_post_subject, t.topic_last_poster_id, t.topic_last_poster_name, t.topic_last_poster_colour, t.topic_last_post_time
1913                  FROM ' . TOPICS_TABLE . " t
1914                  $where_sql";
1915              $result = $db->sql_query($sql);
1916  
1917              while ($row = $db->sql_fetchrow($result))
1918              {
1919                  if ($row['topic_moved_id'])
1920                  {
1921                      $moved_topics[] = $row['topic_id'];
1922                      continue;
1923                  }
1924  
1925                  $topic_id = (int) $row['topic_id'];
1926                  $topic_data[$topic_id] = $row;
1927                  $topic_data[$topic_id]['visibility'] = ITEM_UNAPPROVED;
1928                  $topic_data[$topic_id]['posts_approved'] = 0;
1929                  $topic_data[$topic_id]['posts_unapproved'] = 0;
1930                  $topic_data[$topic_id]['posts_softdeleted'] = 0;
1931                  $topic_data[$topic_id]['first_post_id'] = 0;
1932                  $topic_data[$topic_id]['last_post_id'] = 0;
1933                  unset($topic_data[$topic_id]['topic_id']);
1934  
1935                  // This array holds all topic_ids
1936                  $delete_topics[$topic_id] = '';
1937  
1938                  if ($sync_extra)
1939                  {
1940                      $topic_data[$topic_id]['reported'] = 0;
1941                      $topic_data[$topic_id]['attachment'] = 0;
1942                  }
1943              }
1944              $db->sql_freeresult($result);
1945  
1946              // Use "t" as table alias because of the $where_sql clause
1947              // NOTE: 't.post_visibility' in the GROUP BY is causing a major slowdown.
1948              $sql = 'SELECT t.topic_id, t.post_visibility, COUNT(t.post_id) AS total_posts, MIN(t.post_id) AS first_post_id, MAX(t.post_id) AS last_post_id
1949                  FROM ' . POSTS_TABLE . " t
1950                  $where_sql
1951                  GROUP BY t.topic_id, t.post_visibility";
1952              $result = $db->sql_query($sql);
1953  
1954              while ($row = $db->sql_fetchrow($result))
1955              {
1956                  $topic_id = (int) $row['topic_id'];
1957  
1958                  $row['first_post_id'] = (int) $row['first_post_id'];
1959                  $row['last_post_id'] = (int) $row['last_post_id'];
1960  
1961                  if (!isset($topic_data[$topic_id]))
1962                  {
1963                      // Hey, these posts come from a topic that does not exist
1964                      $delete_posts[$topic_id] = '';
1965                  }
1966                  else
1967                  {
1968                      // Unset the corresponding entry in $delete_topics
1969                      // When we'll be done, only topics with no posts will remain
1970                      unset($delete_topics[$topic_id]);
1971  
1972                      if ($row['post_visibility'] == ITEM_APPROVED)
1973                      {
1974                          $topic_data[$topic_id]['posts_approved'] = $row['total_posts'];
1975                      }
1976                      else if ($row['post_visibility'] == ITEM_UNAPPROVED || $row['post_visibility'] == ITEM_REAPPROVE)
1977                      {
1978                          $topic_data[$topic_id]['posts_unapproved'] = $row['total_posts'];
1979                      }
1980                      else if ($row['post_visibility'] == ITEM_DELETED)
1981                      {
1982                          $topic_data[$topic_id]['posts_softdeleted'] = $row['total_posts'];
1983                      }
1984  
1985                      if ($row['post_visibility'] == ITEM_APPROVED)
1986                      {
1987                          $topic_data[$topic_id]['visibility'] = ITEM_APPROVED;
1988                          $topic_data[$topic_id]['first_post_id'] = $row['first_post_id'];
1989                          $topic_data[$topic_id]['last_post_id'] = $row['last_post_id'];
1990                      }
1991                      else if ($topic_data[$topic_id]['visibility'] != ITEM_APPROVED)
1992                      {
1993                          // If there is no approved post, we take the min/max of the other visibilities
1994                          // for the last and first post info, because it is only visible to moderators anyway
1995                          $topic_data[$topic_id]['first_post_id'] = (!empty($topic_data[$topic_id]['first_post_id'])) ? min($topic_data[$topic_id]['first_post_id'], $row['first_post_id']) : $row['first_post_id'];
1996                          $topic_data[$topic_id]['last_post_id'] = max($topic_data[$topic_id]['last_post_id'], $row['last_post_id']);
1997  
1998                          if ($topic_data[$topic_id]['visibility'] == ITEM_UNAPPROVED || $topic_data[$topic_id]['visibility'] == ITEM_REAPPROVE)
1999                          {
2000                              // Soft delete status is stronger than unapproved.
2001                              $topic_data[$topic_id]['visibility'] = $row['post_visibility'];
2002                          }
2003                      }
2004                  }
2005              }
2006              $db->sql_freeresult($result);
2007  
2008              foreach ($topic_data as $topic_id => $row)
2009              {
2010                  $post_ids[] = $row['first_post_id'];
2011                  if ($row['first_post_id'] != $row['last_post_id'])
2012                  {
2013                      $post_ids[] = $row['last_post_id'];
2014                  }
2015              }
2016  
2017              // Now we delete empty topics and orphan posts
2018              if (count($delete_posts))
2019              {
2020                  delete_posts('topic_id', array_keys($delete_posts), false);
2021                  unset($delete_posts);
2022              }
2023  
2024              if (!count($topic_data))
2025              {
2026                  // If we get there, topic ids were invalid or topics did not contain any posts
2027                  delete_topics($where_type, $where_ids, true);
2028                  return;
2029              }
2030  
2031              if (count($delete_topics))
2032              {
2033                  $delete_topic_ids = array();
2034                  foreach ($delete_topics as $topic_id => $void)
2035                  {
2036                      unset($topic_data[$topic_id]);
2037                      $delete_topic_ids[] = $topic_id;
2038                  }
2039  
2040                  delete_topics('topic_id', $delete_topic_ids, false);
2041                  unset($delete_topics, $delete_topic_ids);
2042              }
2043  
2044              $sql = 'SELECT p.post_id, p.topic_id, p.post_visibility, p.poster_id, p.post_subject, p.post_username, p.post_time, u.username, u.user_colour
2045                  FROM ' . POSTS_TABLE . ' p, ' . USERS_TABLE . ' u
2046                  WHERE ' . $db->sql_in_set('p.post_id', $post_ids) . '
2047                      AND u.user_id = p.poster_id';
2048              $result = $db->sql_query($sql);
2049  
2050              while ($row = $db->sql_fetchrow($result))
2051              {
2052                  $topic_id = intval($row['topic_id']);
2053  
2054                  if ($row['post_id'] == $topic_data[$topic_id]['first_post_id'])
2055                  {
2056                      $topic_data[$topic_id]['time'] = $row['post_time'];
2057                      $topic_data[$topic_id]['poster'] = $row['poster_id'];
2058                      $topic_data[$topic_id]['first_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
2059                      $topic_data[$topic_id]['first_poster_colour'] = $row['user_colour'];
2060                  }
2061  
2062                  if ($row['post_id'] == $topic_data[$topic_id]['last_post_id'])
2063                  {
2064                      $topic_data[$topic_id]['last_poster_id'] = $row['poster_id'];
2065                      $topic_data[$topic_id]['last_post_subject'] = $row['post_subject'];
2066                      $topic_data[$topic_id]['last_post_time'] = $row['post_time'];
2067                      $topic_data[$topic_id]['last_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
2068                      $topic_data[$topic_id]['last_poster_colour'] = $row['user_colour'];
2069                  }
2070              }
2071              $db->sql_freeresult($result);
2072  
2073              // Make sure shadow topics do link to existing topics
2074              if (count($moved_topics))
2075              {
2076                  $delete_topics = array();
2077  
2078                  $sql = 'SELECT t1.topic_id, t1.topic_moved_id
2079                      FROM ' . TOPICS_TABLE . ' t1
2080                      LEFT JOIN ' . TOPICS_TABLE . ' t2 ON (t2.topic_id = t1.topic_moved_id)
2081                      WHERE ' . $db->sql_in_set('t1.topic_id', $moved_topics) . '
2082                          AND t2.topic_id IS NULL';
2083                  $result = $db->sql_query($sql);
2084  
2085                  while ($row = $db->sql_fetchrow($result))
2086                  {
2087                      $delete_topics[] = $row['topic_id'];
2088                  }
2089                  $db->sql_freeresult($result);
2090  
2091                  if (count($delete_topics))
2092                  {
2093                      delete_topics('topic_id', $delete_topics, false);
2094                  }
2095                  unset($delete_topics);
2096  
2097                  // Make sure shadow topics having no last post data being updated (this only rarely happens...)
2098                  $sql = 'SELECT topic_id, topic_moved_id, topic_last_post_id, topic_first_post_id
2099                      FROM ' . TOPICS_TABLE . '
2100                      WHERE ' . $db->sql_in_set('topic_id', $moved_topics) . '
2101                          AND topic_last_post_time = 0';
2102                  $result = $db->sql_query($sql);
2103  
2104                  $shadow_topic_data = $post_ids = array();
2105                  while ($row = $db->sql_fetchrow($result))
2106                  {
2107                      $shadow_topic_data[$row['topic_moved_id']] = $row;
2108                      $post_ids[] = $row['topic_last_post_id'];
2109                      $post_ids[] = $row['topic_first_post_id'];
2110                  }
2111                  $db->sql_freeresult($result);
2112  
2113                  $sync_shadow_topics = array();
2114                  if (count($post_ids))
2115                  {
2116                      $sql = 'SELECT p.post_id, p.topic_id, p.post_visibility, p.poster_id, p.post_subject, p.post_username, p.post_time, u.username, u.user_colour
2117                          FROM ' . POSTS_TABLE . ' p, ' . USERS_TABLE . ' u
2118                          WHERE ' . $db->sql_in_set('p.post_id', $post_ids) . '
2119                              AND u.user_id = p.poster_id';
2120                      $result = $db->sql_query($sql);
2121  
2122                      while ($row = $db->sql_fetchrow($result))
2123                      {
2124                          $topic_id = (int) $row['topic_id'];
2125  
2126                          // Ok, there should be a shadow topic. If there isn't, then there's something wrong with the db.
2127                          // However, there's not much we can do about it.
2128                          if (!empty($shadow_topic_data[$topic_id]))
2129                          {
2130                              if ($row['post_id'] == $shadow_topic_data[$topic_id]['topic_first_post_id'])
2131                              {
2132                                  $orig_topic_id = $shadow_topic_data[$topic_id]['topic_id'];
2133  
2134                                  if (!isset($sync_shadow_topics[$orig_topic_id]))
2135                                  {
2136                                      $sync_shadow_topics[$orig_topic_id] = array();
2137                                  }
2138  
2139                                  $sync_shadow_topics[$orig_topic_id]['topic_time'] = $row['post_time'];
2140                                  $sync_shadow_topics[$orig_topic_id]['topic_poster'] = $row['poster_id'];
2141                                  $sync_shadow_topics[$orig_topic_id]['topic_first_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
2142                                  $sync_shadow_topics[$orig_topic_id]['topic_first_poster_colour'] = $row['user_colour'];
2143                              }
2144  
2145                              if ($row['post_id'] == $shadow_topic_data[$topic_id]['topic_last_post_id'])
2146                              {
2147                                  $orig_topic_id = $shadow_topic_data[$topic_id]['topic_id'];
2148  
2149                                  if (!isset($sync_shadow_topics[$orig_topic_id]))
2150                                  {
2151                                      $sync_shadow_topics[$orig_topic_id] = array();
2152                                  }
2153  
2154                                  $sync_shadow_topics[$orig_topic_id]['topic_last_poster_id'] = $row['poster_id'];
2155                                  $sync_shadow_topics[$orig_topic_id]['topic_last_post_subject'] = $row['post_subject'];
2156                                  $sync_shadow_topics[$orig_topic_id]['topic_last_post_time'] = $row['post_time'];
2157                                  $sync_shadow_topics[$orig_topic_id]['topic_last_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
2158                                  $sync_shadow_topics[$orig_topic_id]['topic_last_poster_colour'] = $row['user_colour'];
2159                              }
2160                          }
2161                      }
2162                      $db->sql_freeresult($result);
2163  
2164                      $shadow_topic_data = array();
2165  
2166                      // Update the information we collected
2167                      if (count($sync_shadow_topics))
2168                      {
2169                          foreach ($sync_shadow_topics as $sync_topic_id => $sql_ary)
2170                          {
2171                              $sql = 'UPDATE ' . TOPICS_TABLE . '
2172                                  SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '
2173                                  WHERE topic_id = ' . $sync_topic_id;
2174                              $db->sql_query($sql);
2175                          }
2176                      }
2177                  }
2178  
2179                  unset($sync_shadow_topics, $shadow_topic_data);
2180              }
2181  
2182              // These are fields that will be synchronised
2183              $fieldnames = array('time', 'visibility', 'posts_approved', 'posts_unapproved', 'posts_softdeleted', 'poster', 'first_post_id', 'first_poster_name', 'first_poster_colour', 'last_post_id', 'last_post_subject', 'last_post_time', 'last_poster_id', 'last_poster_name', 'last_poster_colour');
2184  
2185              if ($sync_extra)
2186              {
2187                  // This routine assumes that post_reported values are correct
2188                  // if they are not, use sync('post_reported') first
2189                  $sql = 'SELECT t.topic_id, p.post_id
2190                      FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
2191                      $where_sql_and p.topic_id = t.topic_id
2192                          AND p.post_reported = 1
2193                      GROUP BY t.topic_id, p.post_id";
2194                  $result = $db->sql_query($sql);
2195  
2196                  $fieldnames[] = 'reported';
2197                  while ($row = $db->sql_fetchrow($result))
2198                  {
2199                      $topic_data[intval($row['topic_id'])]['reported'] = 1;
2200                  }
2201                  $db->sql_freeresult($result);
2202  
2203                  // This routine assumes that post_attachment values are correct
2204                  // if they are not, use sync('post_attachment') first
2205                  $sql = 'SELECT t.topic_id, p.post_id
2206                      FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
2207                      $where_sql_and p.topic_id = t.topic_id
2208                          AND p.post_attachment = 1
2209                      GROUP BY t.topic_id, p.post_id";
2210                  $result = $db->sql_query($sql);
2211  
2212                  $fieldnames[] = 'attachment';
2213                  while ($row = $db->sql_fetchrow($result))
2214                  {
2215                      $topic_data[intval($row['topic_id'])]['attachment'] = 1;
2216                  }
2217                  $db->sql_freeresult($result);
2218              }
2219  
2220              foreach ($topic_data as $topic_id => $row)
2221              {
2222                  $sql_ary = array();
2223  
2224                  foreach ($fieldnames as $fieldname)
2225                  {
2226                      if (isset($row[$fieldname]) && isset($row['topic_' . $fieldname]) && $row['topic_' . $fieldname] != $row[$fieldname])
2227                      {
2228                          $sql_ary['topic_' . $fieldname] = $row[$fieldname];
2229                      }
2230                  }
2231  
2232                  if (count($sql_ary))
2233                  {
2234                      $sql = 'UPDATE ' . TOPICS_TABLE . '
2235                          SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '
2236                          WHERE topic_id = ' . $topic_id;
2237                      $db->sql_query($sql);
2238  
2239                      $resync_forums[$row['forum_id']] = $row['forum_id'];
2240                  }
2241              }
2242              unset($topic_data);
2243  
2244              $db->sql_transaction('commit');
2245  
2246              // if some topics have been resync'ed then resync parent forums
2247              // except when we're only syncing a range, we don't want to sync forums during
2248              // batch processing.
2249              if ($resync_parents && count($resync_forums) && $where_type != 'range')
2250              {
2251                  sync('forum', 'forum_id', array_values($resync_forums), true, true);
2252              }
2253              break;
2254      }
2255  
2256      return;
2257  }
2258  
2259  /**
2260  * Prune function
2261  */
2262  function prune($forum_id, $prune_mode, $prune_date, $prune_flags = 0, $auto_sync = true, $prune_limit = 0)
2263  {
2264      global $db, $phpbb_dispatcher;
2265  
2266      if (!is_array($forum_id))
2267      {
2268          $forum_id = array($forum_id);
2269      }
2270  
2271      if (!count($forum_id))
2272      {
2273          return;
2274      }
2275  
2276      $sql_and = '';
2277  
2278      if (!($prune_flags & FORUM_FLAG_PRUNE_ANNOUNCE))
2279      {
2280          $sql_and .= ' AND topic_type <> ' . POST_ANNOUNCE;
2281          $sql_and .= ' AND topic_type <> ' . POST_GLOBAL;
2282      }
2283  
2284      if (!($prune_flags & FORUM_FLAG_PRUNE_STICKY))
2285      {
2286          $sql_and .= ' AND topic_type <> ' . POST_STICKY;
2287      }
2288  
2289      if ($prune_mode == 'posted')
2290      {
2291          $sql_and .= " AND topic_last_post_time < $prune_date";
2292      }
2293  
2294      if ($prune_mode == 'viewed')
2295      {
2296          $sql_and .= " AND topic_last_view_time < $prune_date";
2297      }
2298  
2299      if ($prune_mode == 'shadow')
2300      {
2301          $sql_and .= ' AND topic_status = ' . ITEM_MOVED . " AND topic_last_post_time < $prune_date";
2302      }
2303  
2304      /**
2305      * Use this event to modify the SQL that selects topics to be pruned
2306      *
2307      * @event core.prune_sql
2308      * @var string    forum_id        The forum id
2309      * @var string    prune_mode        The prune mode
2310      * @var string    prune_date        The prune date
2311      * @var int        prune_flags        The prune flags
2312      * @var bool        auto_sync        Whether or not to perform auto sync
2313      * @var string    sql_and            SQL text appended to where clause
2314      * @var int        prune_limit        The prune limit
2315      * @since 3.1.3-RC1
2316      * @changed 3.1.10-RC1            Added prune_limit
2317      */
2318      $vars = array(
2319          'forum_id',
2320          'prune_mode',
2321          'prune_date',
2322          'prune_flags',
2323          'auto_sync',
2324          'sql_and',
2325          'prune_limit',
2326      );
2327      extract($phpbb_dispatcher->trigger_event('core.prune_sql', compact($vars)));
2328  
2329      $sql = 'SELECT topic_id
2330          FROM ' . TOPICS_TABLE . '
2331          WHERE ' . $db->sql_in_set('forum_id', $forum_id) . "
2332              AND poll_start = 0
2333              $sql_and";
2334      $result = $db->sql_query_limit($sql, $prune_limit);
2335  
2336      $topic_list = array();
2337      while ($row = $db->sql_fetchrow($result))
2338      {
2339          $topic_list[] = $row['topic_id'];
2340      }
2341      $db->sql_freeresult($result);
2342  
2343      if ($prune_flags & FORUM_FLAG_PRUNE_POLL)
2344      {
2345          $sql = 'SELECT topic_id
2346              FROM ' . TOPICS_TABLE . '
2347              WHERE ' . $db->sql_in_set('forum_id', $forum_id) . "
2348                  AND poll_start > 0
2349                  AND poll_last_vote < $prune_date
2350                  $sql_and";
2351          $result = $db->sql_query_limit($sql, $prune_limit);
2352  
2353          while ($row = $db->sql_fetchrow($result))
2354          {
2355              $topic_list[] = $row['topic_id'];
2356          }
2357          $db->sql_freeresult($result);
2358  
2359          $topic_list = array_unique($topic_list);
2360      }
2361  
2362      /**
2363       * Perform additional actions before topic deletion via pruning
2364       *
2365       * @event core.prune_delete_before
2366       * @var int[]    topic_list        The IDs of the topics to be deleted
2367       * @since 3.2.2-RC1
2368       */
2369      $vars = array('topic_list');
2370      extract($phpbb_dispatcher->trigger_event('core.prune_delete_before', compact($vars)));
2371  
2372      return delete_topics('topic_id', $topic_list, $auto_sync, false);
2373  }
2374  
2375  /**
2376  * Function auto_prune(), this function now relies on passed vars
2377  */
2378  function auto_prune($forum_id, $prune_mode, $prune_flags, $prune_days, $prune_freq)
2379  {
2380      global $db, $user, $phpbb_log;
2381  
2382      $sql = 'SELECT forum_name
2383          FROM ' . FORUMS_TABLE . "
2384          WHERE forum_id = $forum_id";
2385      $result = $db->sql_query($sql, 3600);
2386      $row = $db->sql_fetchrow($result);
2387      $db->sql_freeresult($result);
2388  
2389      if ($row)
2390      {
2391          $prune_date = time() - ($prune_days * 86400);
2392          $next_prune = time() + ($prune_freq * 86400);
2393  
2394          $result = prune($forum_id, $prune_mode, $prune_date, $prune_flags, true, 300);
2395  
2396          if ($result['topics'] == 0 && $result['posts'] == 0)
2397          {
2398              $sql = 'UPDATE ' . FORUMS_TABLE . "
2399                  SET prune_next = $next_prune
2400                  WHERE forum_id = $forum_id";
2401              $db->sql_query($sql);
2402          }
2403  
2404          $phpbb_log->add('admin', $user->data['user_id'], $user->ip, 'LOG_AUTO_PRUNE', false, array($row['forum_name']));
2405      }
2406  
2407      return;
2408  }
2409  
2410  /**
2411  * Cache moderators. Called whenever permissions are changed
2412  * via admin_permissions. Changes of usernames and group names
2413  * must be carried through for the moderators table.
2414  *
2415  * @param \phpbb\db\driver\driver_interface $db Database connection
2416  * @param \phpbb\cache\driver\driver_interface Cache driver
2417  * @param \phpbb\auth\auth $auth Authentication object
2418  * @return null
2419  */
2420  function phpbb_cache_moderators($db, $cache, $auth)
2421  {
2422      // Remove cached sql results
2423      $cache->destroy('sql', MODERATOR_CACHE_TABLE);
2424  
2425      // Clear table
2426      switch ($db->get_sql_layer())
2427      {
2428          case 'sqlite3':
2429              $db->sql_query('DELETE FROM ' . MODERATOR_CACHE_TABLE);
2430          break;
2431  
2432          default:
2433              $db->sql_query('TRUNCATE TABLE ' . MODERATOR_CACHE_TABLE);
2434          break;
2435      }
2436  
2437      // We add moderators who have forum moderator permissions without an explicit ACL_NEVER setting
2438      $sql_ary = array();
2439  
2440      // Grab all users having moderative options...
2441      $hold_ary = $auth->acl_user_raw_data(false, 'm_%', false);
2442  
2443      // Add users?
2444      if (!empty($hold_ary))
2445      {
2446          // At least one moderative option warrants a display
2447          $ug_id_ary = array_keys($hold_ary);
2448  
2449          // Remove users who have group memberships with DENY moderator permissions
2450          $sql_ary_deny = array(
2451              'SELECT'    => 'a.forum_id, ug.user_id, g.group_id',
2452  
2453              'FROM'        => array(
2454                  ACL_OPTIONS_TABLE    => 'o',
2455                  USER_GROUP_TABLE    => 'ug',
2456                  GROUPS_TABLE        => 'g',
2457                  ACL_GROUPS_TABLE    => 'a',
2458              ),
2459  
2460              'LEFT_JOIN'    => array(
2461                  array(
2462                      'FROM'    => array(ACL_ROLES_DATA_TABLE => 'r'),
2463                      'ON'    => 'a.auth_role_id = r.role_id',
2464                  ),
2465              ),
2466  
2467              'WHERE'        => '(o.auth_option_id = a.auth_option_id OR o.auth_option_id = r.auth_option_id)
2468                  AND ((a.auth_setting = ' . ACL_NEVER . ' AND r.auth_setting IS NULL)
2469                      OR r.auth_setting = ' . ACL_NEVER . ')
2470                  AND a.group_id = ug.group_id
2471                  AND g.group_id = ug.group_id
2472                  AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1)
2473                  AND ' . $db->sql_in_set('ug.user_id', $ug_id_ary) . "
2474                  AND ug.user_pending = 0
2475                  AND o.auth_option " . $db->sql_like_expression('m_' . $db->get_any_char()),
2476          );
2477          $sql = $db->sql_build_query('SELECT', $sql_ary_deny);
2478          $result = $db->sql_query($sql);
2479  
2480          while ($row = $db->sql_fetchrow($result))
2481          {
2482              if (isset($hold_ary[$row['user_id']][$row['forum_id']]))
2483              {
2484                  unset($hold_ary[$row['user_id']][$row['forum_id']]);
2485              }
2486          }
2487          $db->sql_freeresult($result);
2488  
2489          if (count($hold_ary))
2490          {
2491              // Get usernames...
2492              $sql = 'SELECT user_id, username
2493                  FROM ' . USERS_TABLE . '
2494                  WHERE ' . $db->sql_in_set('user_id', array_keys($hold_ary));
2495              $result = $db->sql_query($sql);
2496  
2497              $usernames_ary = array();
2498              while ($row = $db->sql_fetchrow($result))
2499              {
2500                  $usernames_ary[$row['user_id']] = $row['username'];
2501              }
2502              $db->sql_freeresult($result);
2503  
2504              foreach ($hold_ary as $user_id => $forum_id_ary)
2505              {
2506                  // Do not continue if user does not exist
2507                  if (!isset($usernames_ary[$user_id]))
2508                  {
2509                      continue;
2510                  }
2511  
2512                  foreach ($forum_id_ary as $forum_id => $auth_ary)
2513                  {
2514                      $sql_ary[] = array(
2515                          'forum_id'        => (int) $forum_id,
2516                          'user_id'        => (int) $user_id,
2517                          'username'        => (string) $usernames_ary[$user_id],
2518                          'group_id'        => 0,
2519                          'group_name'    => ''
2520                      );
2521                  }
2522              }
2523          }
2524      }
2525  
2526      // Now to the groups...
2527      $hold_ary = $auth->acl_group_raw_data(false, 'm_%', false);
2528  
2529      if (!empty($hold_ary))
2530      {
2531          $ug_id_ary = array_keys($hold_ary);
2532  
2533          // Make sure not hidden or special groups are involved...
2534          $sql = 'SELECT group_name, group_id, group_type
2535              FROM ' . GROUPS_TABLE . '
2536              WHERE ' . $db->sql_in_set('group_id', $ug_id_ary);
2537          $result = $db->sql_query($sql);
2538  
2539          $groupnames_ary = array();
2540          while ($row = $db->sql_fetchrow($result))
2541          {
2542              if ($row['group_type'] == GROUP_HIDDEN || $row['group_type'] == GROUP_SPECIAL)
2543              {
2544                  unset($hold_ary[$row['group_id']]);
2545              }
2546  
2547              $groupnames_ary[$row['group_id']] = $row['group_name'];
2548          }
2549          $db->sql_freeresult($result);
2550  
2551          foreach ($hold_ary as $group_id => $forum_id_ary)
2552          {
2553              // If there is no group, we do not assign it...
2554              if (!isset($groupnames_ary[$group_id]))
2555              {
2556                  continue;
2557              }
2558  
2559              foreach ($forum_id_ary as $forum_id => $auth_ary)
2560              {
2561                  $flag = false;
2562                  foreach ($auth_ary as $auth_option => $setting)
2563                  {
2564                      // Make sure at least one ACL_YES option is set...
2565                      if ($setting == ACL_YES)
2566                      {
2567                          $flag = true;
2568                          break;
2569                      }
2570                  }
2571  
2572                  if (!$flag)
2573                  {
2574                      continue;
2575                  }
2576  
2577                  $sql_ary[] = array(
2578                      'forum_id'        => (int) $forum_id,
2579                      'user_id'        => 0,
2580                      'username'        => '',
2581                      'group_id'        => (int) $group_id,
2582                      'group_name'    => (string) $groupnames_ary[$group_id]
2583                  );
2584              }
2585          }
2586      }
2587  
2588      $db->sql_multi_insert(MODERATOR_CACHE_TABLE, $sql_ary);
2589  }
2590  
2591  /**
2592  * View log
2593  *
2594  * @param    string    $mode            The mode defines which log_type is used and from which log the entry is retrieved
2595  * @param    array    &$log            The result array with the logs
2596  * @param    mixed    &$log_count        If $log_count is set to false, we will skip counting all entries in the database.
2597  *                                    Otherwise an integer with the number of total matching entries is returned.
2598  * @param    int        $limit            Limit the number of entries that are returned
2599  * @param    int        $offset            Offset when fetching the log entries, f.e. when paginating
2600  * @param    mixed    $forum_id        Restrict the log entries to the given forum_id (can also be an array of forum_ids)
2601  * @param    int        $topic_id        Restrict the log entries to the given topic_id
2602  * @param    int        $user_id        Restrict the log entries to the given user_id
2603  * @param    int        $log_time        Only get log entries newer than the given timestamp
2604  * @param    string    $sort_by        SQL order option, e.g. 'l.log_time DESC'
2605  * @param    string    $keywords        Will only return log entries that have the keywords in log_operation or log_data
2606  *
2607  * @return    int                Returns the offset of the last valid page, if the specified offset was invalid (too high)
2608  */
2609  function view_log($mode, &$log, &$log_count, $limit = 0, $offset = 0, $forum_id = 0, $topic_id = 0, $user_id = 0, $limit_days = 0, $sort_by = 'l.log_time DESC', $keywords = '')
2610  {
2611      global $phpbb_log;
2612  
2613      $count_logs = ($log_count !== false);
2614  
2615      $log = $phpbb_log->get_logs($mode, $count_logs, $limit, $offset, $forum_id, $topic_id, $user_id, $limit_days, $sort_by, $keywords);
2616      $log_count = $phpbb_log->get_log_count();
2617  
2618      return $phpbb_log->get_valid_offset();
2619  }
2620  
2621  /**
2622  * Removes moderators and administrators from foe lists.
2623  *
2624  * @param \phpbb\db\driver\driver_interface $db Database connection
2625  * @param \phpbb\auth\auth $auth Authentication object
2626  * @param array|bool $group_id If an array, remove all members of this group from foe lists, or false to ignore
2627  * @param array|bool $user_id If an array, remove this user from foe lists, or false to ignore
2628  * @return null
2629  */
2630  function phpbb_update_foes($db, $auth, $group_id = false, $user_id = false)
2631  {
2632      // update foes for some user
2633      if (is_array($user_id) && count($user_id))
2634      {
2635          $sql = 'DELETE FROM ' . ZEBRA_TABLE . '
2636              WHERE ' . $db->sql_in_set('zebra_id', $user_id) . '
2637                  AND foe = 1';
2638          $db->sql_query($sql);
2639          return;
2640      }
2641  
2642      // update foes for some group
2643      if (is_array($group_id) && count($group_id))
2644      {
2645          // Grab group settings...
2646          $sql_ary = array(
2647              'SELECT'    => 'a.group_id',
2648  
2649              'FROM'        => array(
2650                  ACL_OPTIONS_TABLE    => 'ao',
2651                  ACL_GROUPS_TABLE    => 'a',
2652              ),
2653  
2654              'LEFT_JOIN'    => array(
2655                  array(
2656                      'FROM'    => array(ACL_ROLES_DATA_TABLE => 'r'),
2657                      'ON'    => 'a.auth_role_id = r.role_id',
2658                  ),
2659              ),
2660  
2661              'WHERE'        => '(ao.auth_option_id = a.auth_option_id OR ao.auth_option_id = r.auth_option_id)
2662                  AND ' . $db->sql_in_set('a.group_id', $group_id) . "
2663                  AND ao.auth_option IN ('a_', 'm_')",
2664  
2665              'GROUP_BY'    => 'a.group_id',
2666          );
2667          $sql = $db->sql_build_query('SELECT', $sql_ary);
2668          $result = $db->sql_query($sql);
2669  
2670          $groups = array();
2671          while ($row = $db->sql_fetchrow($result))
2672          {
2673              $groups[] = (int) $row['group_id'];
2674          }
2675          $db->sql_freeresult($result);
2676  
2677          if (!count($groups))
2678          {
2679              return;
2680          }
2681  
2682          switch ($db->get_sql_layer())
2683          {
2684              case 'mysqli':
2685                  $sql = 'DELETE z.*
2686                      FROM ' . ZEBRA_TABLE . ' z, ' . USER_GROUP_TABLE . ' ug
2687                      WHERE z.zebra_id = ug.user_id
2688                          AND z.foe = 1
2689                          AND ' . $db->sql_in_set('ug.group_id', $groups);
2690                  $db->sql_query($sql);
2691              break;
2692  
2693              default:
2694                  $sql = 'SELECT user_id
2695                      FROM ' . USER_GROUP_TABLE . '
2696                      WHERE ' . $db->sql_in_set('group_id', $groups);
2697                  $result = $db->sql_query($sql);
2698  
2699                  $users = array();
2700                  while ($row = $db->sql_fetchrow($result))
2701                  {
2702                      $users[] = (int) $row['user_id'];
2703                  }
2704                  $db->sql_freeresult($result);
2705  
2706                  if (count($users))
2707                  {
2708                      $sql = 'DELETE FROM ' . ZEBRA_TABLE . '
2709                          WHERE ' . $db->sql_in_set('zebra_id', $users) . '
2710                              AND foe = 1';
2711                      $db->sql_query($sql);
2712                  }
2713              break;
2714          }
2715  
2716          return;
2717      }
2718  
2719      // update foes for everyone
2720      $perms = array();
2721      foreach ($auth->acl_get_list(false, array('a_', 'm_'), false) as $forum_id => $forum_ary)
2722      {
2723          foreach ($forum_ary as $auth_option => $user_ary)
2724          {
2725              $perms = array_merge($perms, $user_ary);
2726          }
2727      }
2728  
2729      if (count($perms))
2730      {
2731          $sql = 'DELETE FROM ' . ZEBRA_TABLE . '
2732              WHERE ' . $db->sql_in_set('zebra_id', array_unique($perms)) . '
2733                  AND foe = 1';
2734          $db->sql_query($sql);
2735      }
2736      unset($perms);
2737  }
2738  
2739  /**
2740  * Lists inactive users
2741  */
2742  function view_inactive_users(&$users, &$user_count, $limit = 0, $offset = 0, $limit_days = 0, $sort_by = 'user_inactive_time DESC')
2743  {
2744      global $db, $user;
2745  
2746      $sql = 'SELECT COUNT(user_id) AS user_count
2747          FROM ' . USERS_TABLE . '
2748          WHERE user_type = ' . USER_INACTIVE .
2749          (($limit_days) ? " AND user_inactive_time >= $limit_days" : '');
2750      $result = $db->sql_query($sql);
2751      $user_count = (int) $db->sql_fetchfield('user_count');
2752      $db->sql_freeresult($result);
2753  
2754      if ($user_count == 0)
2755      {
2756          // Save the queries, because there are no users to display
2757          return 0;
2758      }
2759  
2760      if ($offset >= $user_count)
2761      {
2762          $offset = ($offset - $limit < 0) ? 0 : $offset - $limit;
2763      }
2764  
2765      $sql = 'SELECT *
2766          FROM ' . USERS_TABLE . '
2767          WHERE user_type = ' . USER_INACTIVE .
2768          (($limit_days) ? " AND user_inactive_time >= $limit_days" : '') . "
2769          ORDER BY $sort_by";
2770      $result = $db->sql_query_limit($sql, $limit, $offset);
2771  
2772      while ($row = $db->sql_fetchrow($result))
2773      {
2774          $row['inactive_reason'] = $user->lang['INACTIVE_REASON_UNKNOWN'];
2775          switch ($row['user_inactive_reason'])
2776          {
2777              case INACTIVE_REGISTER:
2778                  $row['inactive_reason'] = $user->lang['INACTIVE_REASON_REGISTER'];
2779              break;
2780  
2781              case INACTIVE_PROFILE:
2782                  $row['inactive_reason'] = $user->lang['INACTIVE_REASON_PROFILE'];
2783              break;
2784  
2785              case INACTIVE_MANUAL:
2786                  $row['inactive_reason'] = $user->lang['INACTIVE_REASON_MANUAL'];
2787              break;
2788  
2789              case INACTIVE_REMIND:
2790                  $row['inactive_reason'] = $user->lang['INACTIVE_REASON_REMIND'];
2791              break;
2792          }
2793  
2794          $users[] = $row;
2795      }
2796      $db->sql_freeresult($result);
2797  
2798      return $offset;
2799  }
2800  
2801  /**
2802  * Lists warned users
2803  */
2804  function view_warned_users(&$users, &$user_count, $limit = 0, $offset = 0, $limit_days = 0, $sort_by = 'user_warnings DESC')
2805  {
2806      global $db;
2807  
2808      $sql = 'SELECT user_id, username, user_colour, user_warnings, user_last_warning
2809          FROM ' . USERS_TABLE . '
2810          WHERE user_warnings > 0
2811          ' . (($limit_days) ? "AND user_last_warning >= $limit_days" : '') . "
2812          ORDER BY $sort_by";
2813      $result = $db->sql_query_limit($sql, $limit, $offset);
2814      $users = $db->sql_fetchrowset($result);
2815      $db->sql_freeresult($result);
2816  
2817      $sql = 'SELECT count(user_id) AS user_count
2818          FROM ' . USERS_TABLE . '
2819          WHERE user_warnings > 0
2820          ' . (($limit_days) ? "AND user_last_warning >= $limit_days" : '');
2821      $result = $db->sql_query($sql);
2822      $user_count = (int) $db->sql_fetchfield('user_count');
2823      $db->sql_freeresult($result);
2824  
2825      return;
2826  }
2827  
2828  /**
2829  * Get database size
2830  * Currently only mysql and mssql are supported
2831  */
2832  function get_database_size()
2833  {
2834      global $db, $user, $table_prefix;
2835  
2836      $database_size = false;
2837  
2838      // This code is heavily influenced by a similar routine in phpMyAdmin 2.2.0
2839      switch ($db->get_sql_layer())
2840      {
2841          case 'mysqli':
2842              $sql = 'SELECT VERSION() AS mysql_version';
2843              $result = $db->sql_query($sql);
2844              $row = $db->sql_fetchrow($result);
2845              $db->sql_freeresult($result);
2846  
2847              if ($row)
2848              {
2849                  $version = $row['mysql_version'];
2850  
2851                  if (preg_match('#(3\.23|[45]\.|10\.[0-9]\.[0-9]{1,2}-+Maria)#', $version))
2852                  {
2853                      $db_name = (preg_match('#^(?:3\.23\.(?:[6-9]|[1-9]{2}))|[45]\.|10\.[0-9]\.[0-9]{1,2}-+Maria#', $version)) ? "`{$db->get_db_name()}`" : $db->get_db_name();
2854  
2855                      $sql = 'SHOW TABLE STATUS
2856                          FROM ' . $db_name;
2857                      $result = $db->sql_query($sql, 7200);
2858  
2859                      $database_size = 0;
2860                      while ($row = $db->sql_fetchrow($result))
2861                      {
2862                          if ((isset($row['Type']) && $row['Type'] != 'MRG_MyISAM') || (isset($row['Engine']) && ($row['Engine'] == 'MyISAM' || $row['Engine'] == 'InnoDB' || $row['Engine'] == 'Aria')))
2863                          {
2864                              if ($table_prefix != '')
2865                              {
2866                                  if (strpos($row['Name'], $table_prefix) !== false)
2867                                  {
2868                                      $database_size += $row['Data_length'] + $row['Index_length'];
2869                                  }
2870                              }
2871                              else
2872                              {
2873                                  $database_size += $row['Data_length'] + $row['Index_length'];
2874                              }
2875                          }
2876                      }
2877                      $db->sql_freeresult($result);
2878                  }
2879              }
2880          break;
2881  
2882          case 'sqlite3':
2883              global $dbhost;
2884  
2885              if (file_exists($dbhost))
2886              {
2887                  $database_size = filesize($dbhost);
2888              }
2889  
2890          break;
2891  
2892          case 'mssql_odbc':
2893          case 'mssqlnative':
2894              $sql = 'SELECT @@VERSION AS mssql_version';
2895              $result = $db->sql_query($sql);
2896              $row = $db->sql_fetchrow($result);
2897              $db->sql_freeresult($result);
2898  
2899              $sql = 'SELECT ((SUM(size) * 8.0) * 1024.0) as dbsize
2900                  FROM sysfiles';
2901  
2902              if ($row)
2903              {
2904                  // Azure stats are stored elsewhere
2905                  if (strpos($row['mssql_version'], 'SQL Azure') !== false)
2906                  {
2907                      $sql = 'SELECT ((SUM(reserved_page_count) * 8.0) * 1024.0) as dbsize
2908                      FROM sys.dm_db_partition_stats';
2909                  }
2910              }
2911  
2912              $result = $db->sql_query($sql, 7200);
2913              $database_size = ($row = $db->sql_fetchrow($result)) ? $row['dbsize'] : false;
2914              $db->sql_freeresult($result);
2915          break;
2916  
2917          case 'postgres':
2918              $sql = "SELECT proname
2919                  FROM pg_proc
2920                  WHERE proname = 'pg_database_size'";
2921              $result = $db->sql_query($sql);
2922              $row = $db->sql_fetchrow($result);
2923              $db->sql_freeresult($result);
2924  
2925              if ($row['proname'] == 'pg_database_size')
2926              {
2927                  $database = $db->get_db_name();
2928                  if (strpos($database, '.') !== false)
2929                  {
2930                      list($database, ) = explode('.', $database);
2931                  }
2932  
2933                  $sql = "SELECT oid
2934                      FROM pg_database
2935                      WHERE datname = '$database'";
2936                  $result = $db->sql_query($sql);
2937                  $row = $db->sql_fetchrow($result);
2938                  $db->sql_freeresult($result);
2939  
2940                  $oid = $row['oid'];
2941  
2942                  $sql = 'SELECT pg_database_size(' . $oid . ') as size';
2943                  $result = $db->sql_query($sql);
2944                  $row = $db->sql_fetchrow($result);
2945                  $db->sql_freeresult($result);
2946  
2947                  $database_size = $row['size'];
2948              }
2949          break;
2950  
2951          case 'oracle':
2952              $sql = 'SELECT SUM(bytes) as dbsize
2953                  FROM user_segments';
2954              $result = $db->sql_query($sql, 7200);
2955              $database_size = ($row = $db->sql_fetchrow($result)) ? $row['dbsize'] : false;
2956              $db->sql_freeresult($result);
2957          break;
2958      }
2959  
2960      $database_size = ($database_size !== false) ? get_formatted_filesize($database_size) : $user->lang['NOT_AVAILABLE'];
2961  
2962      return $database_size;
2963  }
2964  
2965  /*
2966  * Tidy Warnings
2967  * Remove all warnings which have now expired from the database
2968  * The duration of a warning can be defined by the administrator
2969  * This only removes the warning and reduces the associated count,
2970  * it does not remove the user note recording the contents of the warning
2971  */
2972  function tidy_warnings()
2973  {
2974      global $db, $config;
2975  
2976      $expire_date = time() - ($config['warnings_expire_days'] * 86400);
2977      $warning_list = $user_list = array();
2978  
2979      $sql = 'SELECT * FROM ' . WARNINGS_TABLE . "
2980          WHERE warning_time < $expire_date";
2981      $result = $db->sql_query($sql);
2982  
2983      while ($row = $db->sql_fetchrow($result))
2984      {
2985          $warning_list[] = $row['warning_id'];
2986          $user_list[$row['user_id']] = isset($user_list[$row['user_id']]) ? ++$user_list[$row['user_id']] : 1;
2987      }
2988      $db->sql_freeresult($result);
2989  
2990      if (count($warning_list))
2991      {
2992          $db->sql_transaction('begin');
2993  
2994          $sql = 'DELETE FROM ' . WARNINGS_TABLE . '
2995              WHERE ' . $db->sql_in_set('warning_id', $warning_list);
2996          $db->sql_query($sql);
2997  
2998          foreach ($user_list as $user_id => $value)
2999          {
3000              $sql = 'UPDATE ' . USERS_TABLE . " SET user_warnings = user_warnings - $value
3001                  WHERE user_id = $user_id";
3002              $db->sql_query($sql);
3003          }
3004  
3005          $db->sql_transaction('commit');
3006      }
3007  
3008      $config->set('warnings_last_gc', time(), false);
3009  }
3010  
3011  /**
3012  * Tidy database, doing some maintanance tasks
3013  */
3014  function tidy_database()
3015  {
3016      global $config, $db;
3017  
3018      // Here we check permission consistency
3019  
3020      // Sometimes, it can happen permission tables having forums listed which do not exist
3021      $sql = 'SELECT forum_id
3022          FROM ' . FORUMS_TABLE;
3023      $result = $db->sql_query($sql);
3024  
3025      $forum_ids = array(0);
3026      while ($row = $db->sql_fetchrow($result))
3027      {
3028          $forum_ids[] = $row['forum_id'];
3029      }
3030      $db->sql_freeresult($result);
3031  
3032      $db->sql_transaction('begin');
3033  
3034      // Delete those rows from the acl tables not having listed the forums above
3035      $sql = 'DELETE FROM ' . ACL_GROUPS_TABLE . '
3036          WHERE ' . $db->sql_in_set('forum_id', $forum_ids, true);
3037      $db->sql_query($sql);
3038  
3039      $sql = 'DELETE FROM ' . ACL_USERS_TABLE . '
3040          WHERE ' . $db->sql_in_set('forum_id', $forum_ids, true);
3041      $db->sql_query($sql);
3042  
3043      $db->sql_transaction('commit');
3044  
3045      $config->set('database_last_gc', time(), false);
3046  }
3047  
3048  /**
3049  * Add permission language - this will make sure custom files will be included
3050  */
3051  function add_permission_language()
3052  {
3053      global $user, $phpEx, $phpbb_extension_manager;
3054  
3055      // add permission language files from extensions
3056      $finder = $phpbb_extension_manager->get_finder();
3057  
3058      $lang_files = $finder
3059          ->prefix('permissions_')
3060          ->suffix(".$phpEx")
3061          ->core_path('language/')
3062          ->extension_directory('/language')
3063          ->find();
3064  
3065      foreach ($lang_files as $lang_file => $ext_name)
3066      {
3067          if ($ext_name === '/')
3068          {
3069              $user->add_lang($lang_file);
3070          }
3071          else
3072          {
3073              $user->add_lang_ext($ext_name, $lang_file);
3074          }
3075      }
3076  }
3077  
3078  /**
3079   * Enables a particular flag in a bitfield column of a given table.
3080   *
3081   * @param string    $table_name        The table to update
3082   * @param string    $column_name    The column containing a bitfield to update
3083   * @param int        $flag            The binary flag which is OR-ed with the current column value
3084   * @param string    $sql_more        This string is attached to the sql query generated to update the table.
3085   *
3086   * @return null
3087   */
3088  function enable_bitfield_column_flag($table_name, $column_name, $flag, $sql_more = '')
3089  {
3090      global $db;
3091  
3092      $sql = 'UPDATE ' . $table_name . '
3093          SET ' . $column_name . ' = ' . $db->sql_bit_or($column_name, $flag) . '
3094          ' . $sql_more;
3095      $db->sql_query($sql);
3096  }


Generated: Tue Apr 7 19:44:41 2020 Cross-referenced by PHPXref 0.7.1