[ Index ]

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


Generated: Sun Feb 19 19:52:41 2017 Cross-referenced by PHPXref 0.7.1