source: trunk/forum/includes/functions_admin.php

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