Merge pull request #4780 from marc1706/ticket/8116

[ticket/8116] Add pagination for IP tables and post info * github.com:phpbb/phpbb: [ticket/8116] Add missing new line [ticket/8116] Correctly get other users who posted under IP [ticket/8116] Improve code and add tests for helper functions [ticket/8116] Add pagination for IP tables on post info
author: Tristan Darricau <github@nicofuma.fr> 2017-04-21 23:54:18 +0200
committer: Tristan Darricau <github@nicofuma.fr> 2017-04-21 23:54:18 +0200
commit: a233f98bbfb95705d1f4f3b61b64b293f6bbacd0 (patch)
tree: 03b92e3d0cd0ea8b237f408f1fa5c102fd24b0de
parent: ff22d54eae45a79f5bc59d2c04f3e02eed8e4981 (diff)
parent: e72f6d42366c6a0cbc128de406474192bc7cd8e2 (diff)
download: forums-a233f98bbfb95705d1f4f3b61b64b293f6bbacd0.tar
forums-a233f98bbfb95705d1f4f3b61b64b293f6bbacd0.tar.gz
forums-a233f98bbfb95705d1f4f3b61b64b293f6bbacd0.tar.bz2
forums-a233f98bbfb95705d1f4f3b61b64b293f6bbacd0.tar.xz
forums-a233f98bbfb95705d1f4f3b61b64b293f6bbacd0.zip
4 files changed, 272 insertions, 12 deletions
diff --git a/phpBB/includes/mcp/mcp_post.php b/phpBB/includes/mcp/mcp_post.php
index 2dcfcd608b..b5f2bf4f02 100644
--- a/phpBB/includes/mcp/mcp_post.php
+++ b/phpBB/includes/mcp/mcp_post.php
@@ -24,8 +24,8 @@ if (!defined('IN_PHPBB'))
 */
 function mcp_post_details($id, $mode, $action)
 {
-	global $phpEx, $phpbb_root_path, $config;
-	global $template, $db, $user, $auth, $cache;
+	global $phpEx, $phpbb_root_path, $config, $request;
+	global $template, $db, $user, $auth, $cache, $phpbb_container;
 	global $phpbb_dispatcher;
 
 	$user->add_lang('posting');
@@ -355,7 +355,11 @@ function mcp_post_details($id, $mode, $action)
 	// Get IP
 	if ($auth->acl_get('m_info', $post_info['forum_id']))
 	{
-		$rdns_ip_num = request_var('rdns', '');
+		/** @var \phpbb\pagination $pagination */
+		$pagination = $phpbb_container->get('pagination');
+
+		$rdns_ip_num = $request->variable('rdns', '');
+		$start_users = $request->variable('start_users', 0);
 
 		if ($rdns_ip_num != 'all')
 		{
@@ -364,23 +368,46 @@ function mcp_post_details($id, $mode, $action)
 			);
 		}
 
+		$num_users = false;
+		if ($start_users)
+		{
+			$num_users = phpbb_get_num_posters_for_ip($db, $post_info['poster_ip']);
+			$start_users = $pagination->validate_start($start_users, $config['posts_per_page'], $num_users);
+		}
+
 		// Get other users who've posted under this IP
 		$sql = 'SELECT poster_id, COUNT(poster_id) as postings
 			FROM ' . POSTS_TABLE . "
 			WHERE poster_ip = '" . $db->sql_escape($post_info['poster_ip']) . "'
+				AND poster_id <> " . (int) $post_info['poster_id'] . "
 			GROUP BY poster_id
-			ORDER BY postings DESC";
-		$result = $db->sql_query($sql);
+			ORDER BY postings DESC, poster_id ASC";
+		$result = $db->sql_query_limit($sql, $config['posts_per_page'], $start_users);
 
+		$page_users = 0;
 		while ($row = $db->sql_fetchrow($result))
 		{
-			// Fill the user select list with users who have posted under this IP
-			if ($row['poster_id'] != $post_info['poster_id'])
+			$page_users++;
+			$users_ary[$row['poster_id']] = $row;
+		}
+		$db->sql_freeresult($result);
+
+		if ($page_users == $config['posts_per_page'] || $start_users)
+		{
+			if ($num_users === false)
 			{
-				$users_ary[$row['poster_id']] = $row;
+				$num_users = phpbb_get_num_posters_for_ip($db, $post_info['poster_ip']);
 			}
+
+			$pagination->generate_template_pagination(
+				$url . '&amp;i=main&amp;mode=post_details',
+				'pagination',
+				'start_users',
+				$num_users,
+				$config['posts_per_page'],
+				$start_users
+			);
 		}
-		$db->sql_freeresult($result);
 
 		if (sizeof($users_ary))
 		{
@@ -415,16 +442,26 @@ function mcp_post_details($id, $mode, $action)
 		// A compound index on poster_id, poster_ip (posts table) would help speed up this query a lot,
 		// but the extra size is only valuable if there are persons having more than a thousands posts.
 		// This is better left to the really really big forums.
+		$start_ips = $request->variable('start_ips', 0);
+
+		$num_ips = false;
+		if ($start_ips)
+		{
+			$num_ips = phpbb_get_num_ips_for_poster($db, $post_info['poster_id']);
+			$start_ips = $pagination->validate_start($start_ips, $config['posts_per_page'], $num_ips);
+		}
 
 		$sql = 'SELECT poster_ip, COUNT(poster_ip) AS postings
 			FROM ' . POSTS_TABLE . '
 			WHERE poster_id = ' . $post_info['poster_id'] . "
 			GROUP BY poster_ip
-			ORDER BY postings DESC";
-		$result = $db->sql_query($sql);
+			ORDER BY postings DESC, poster_ip ASC";
+		$result = $db->sql_query_limit($sql, $config['posts_per_page'], $start_ips);
 
+		$page_ips = 0;
 		while ($row = $db->sql_fetchrow($result))
 		{
+			$page_ips++;
 			$hostname = (($rdns_ip_num == $row['poster_ip'] || $rdns_ip_num == 'all') && $row['poster_ip']) ? @gethostbyaddr($row['poster_ip']) : '';
 
 			$template->assign_block_vars('iprow', array(
@@ -439,6 +476,23 @@ function mcp_post_details($id, $mode, $action)
 		}
 		$db->sql_freeresult($result);
 
+		if ($page_ips == $config['posts_per_page'] || $start_ips)
+		{
+			if ($num_ips === false)
+			{
+				$num_ips = phpbb_get_num_ips_for_poster($db, $post_info['poster_id']);
+			}
+
+			$pagination->generate_template_pagination(
+				$url . '&amp;i=main&amp;mode=post_details',
+				'pagination_ips',
+				'start_ips',
+				$num_ips,
+				$config['posts_per_page'],
+				$start_ips
+			);
+		}
+
 		$user_select = '';
 
 		if (sizeof($usernames_ary))
@@ -457,6 +511,44 @@ function mcp_post_details($id, $mode, $action)
 }
 
 /**
+ * Get the number of posters for a given ip
+ *
+ * @param \phpbb\db\driver\driver_interface $db DBAL interface
+ * @param string $poster_ip IP
+ * @return int Number of posters
+ */
+function phpbb_get_num_posters_for_ip(\phpbb\db\driver\driver_interface $db, $poster_ip)
+{
+	$sql = 'SELECT COUNT(DISTINCT poster_id) as num_users
+		FROM ' . POSTS_TABLE . "
+		WHERE poster_ip = '" . $db->sql_escape($poster_ip) . "'";
+	$result = $db->sql_query($sql);
+	$num_users = (int) $db->sql_fetchfield('num_users');
+	$db->sql_freeresult($result);
+
+	return $num_users;
+}
+
+/**
+ * Get the number of ips for a given poster
+ *
+ * @param \phpbb\db\driver\driver_interface $db
+ * @param int $poster_id Poster user ID
+ * @return int Number of IPs for given poster
+ */
+function phpbb_get_num_ips_for_poster(\phpbb\db\driver\driver_interface $db, $poster_id)
+{
+	$sql = 'SELECT COUNT(DISTINCT poster_ip) as num_ips
+		FROM ' . POSTS_TABLE . '
+		WHERE poster_id = ' . (int) $poster_id;
+	$result = $db->sql_query($sql);
+	$num_ips = (int) $db->sql_fetchfield('num_ips');
+	$db->sql_freeresult($result);
+
+	return $num_ips;
+}
+
+/**
 * Change a post's poster
 */
 function change_poster(&$post_info, $userdata)
diff --git a/phpBB/styles/prosilver/template/mcp_post.html b/phpBB/styles/prosilver/template/mcp_post.html
index e5777d206a..5acdcef859 100644
--- a/phpBB/styles/prosilver/template/mcp_post.html
+++ b/phpBB/styles/prosilver/template/mcp_post.html
@@ -294,6 +294,14 @@
 			</tbody>
 			</table>
 
+			<div class="pagination">
+				<!-- INCLUDE pagination.html -->
+			</div>
+			</div>
+		</div>
+
+		<div class="panel">
+			<div class="inner">
 			<table class="table1">
 			<thead>
 			<tr>
@@ -315,7 +323,27 @@
 			</tbody>
 			</table>
 
-			<p><a href="{U_LOOKUP_ALL}#ip">{L_LOOKUP_ALL}</a></p>
+			<div class="buttons">
+				<p><a href="{U_LOOKUP_ALL}#ip">{L_LOOKUP_ALL}</a></p>
+			</div>
+
+			<div class="pagination">
+				<ul>
+				<!-- BEGIN pagination_ips -->
+					<!-- IF pagination_ips.S_IS_PREV -->
+						<li class="previous"><a href="{pagination_ips.PAGE_URL}" rel="prev" role="button">{L_PREVIOUS}</a></li>
+					<!-- ELSEIF pagination_ips.S_IS_CURRENT -->
+						<li class="active"><span>{pagination_ips.PAGE_NUMBER}</span></li>
+					<!-- ELSEIF pagination_ips.S_IS_ELLIPSIS -->
+						<li class="ellipsis" role="separator"><span>{L_ELLIPSIS}</span></li>
+					<!-- ELSEIF pagination_ips.S_IS_NEXT -->
+						<li class="next"><a href="{pagination_ips.PAGE_URL}" rel="next" role="button">{L_NEXT}</a></li>
+					<!-- ELSE -->
+						<li><a href="{pagination_ips.PAGE_URL}" role="button">{pagination_ips.PAGE_NUMBER}</a></li>
+					<!-- ENDIF -->
+				<!-- END pagination_ips -->
+				</ul>
+			</div>
 
 			</div>
 		</div>
diff --git a/tests/mcp/fixtures/post_ip.xml b/tests/mcp/fixtures/post_ip.xml
new file mode 100644
index 0000000000..fad2193396
--- /dev/null
+++ b/tests/mcp/fixtures/post_ip.xml
@@ -0,0 +1,73 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<dataset>
+	<table name="phpbb_posts">
+		<column>post_id</column>
+		<column>poster_id</column>
+		<column>post_edit_user</column>
+		<column>post_delete_user</column>
+		<column>post_username</column>
+		<column>topic_id</column>
+		<column>forum_id</column>
+		<column>post_visibility</column>
+		<column>post_time</column>
+		<column>post_text</column>
+		<column>post_reported</column>
+		<column>poster_ip</column>
+		<row>
+			<value>1</value>
+			<value>2</value>
+			<value>2</value>
+			<value>2</value>
+			<value></value>
+			<value>1</value>
+			<value>1</value>
+			<value>1</value>
+			<value>1</value>
+			<value></value>
+			<value>1</value>
+			<value>127.0.0.1</value>
+		</row>
+		<row>
+			<value>2</value>
+			<value>1</value>
+			<value>1</value>
+			<value>1</value>
+			<value>Other</value>
+			<value>2</value>
+			<value>2</value>
+			<value>1</value>
+			<value>1</value>
+			<value></value>
+			<value>1</value>
+			<value>127.0.0.2</value>
+		</row>
+		<row>
+			<value>3</value>
+			<value>2</value>
+			<value>2</value>
+			<value>2</value>
+			<value></value>
+			<value>3</value>
+			<value>3</value>
+			<value>1</value>
+			<value>1</value>
+			<value></value>
+			<value>1</value>
+			<value>127.0.0.3</value>
+		</row>
+		<row>
+			<value>4</value>
+			<value>1</value>
+			<value>1</value>
+			<value>1</value>
+			<value>Other</value>
+			<value>4</value>
+			<value>4</value>
+			<value>1</value>
+			<value>1</value>
+			<value></value>
+			<value>1</value>
+			<value>127.0.0.1</value>
+		</row>
+	</table>
+</dataset>
diff --git a/tests/mcp/post_ip_test.php b/tests/mcp/post_ip_test.php
new file mode 100644
index 0000000000..72a9f62774
--- /dev/null
+++ b/tests/mcp/post_ip_test.php
@@ -0,0 +1,67 @@
+<?php
+/**
+ *
+ * This file is part of the phpBB Forum Software package.
+ *
+ * @copyright (c) phpBB Limited <https://www.phpbb.com>
+ * @license GNU General Public License, version 2 (GPL-2.0)
+ *
+ * For full copyright and license information, please see
+ * the docs/CREDITS.txt file.
+ *
+ */
+
+require_once dirname(__FILE__) . '/../../phpBB/includes/mcp/mcp_post.php';
+
+class phpbb_mcp_post_ip_test extends phpbb_database_test_case
+{
+	/** @var \phpbb\db\driver\driver_interface */
+	protected $db;
+
+	public function getDataSet()
+	{
+		return $this->createXMLDataSet(dirname(__FILE__) . '/fixtures/post_ip.xml');
+	}
+
+	protected function setUp()
+	{
+		parent::setUp();
+
+		$this->db = $this->new_dbal();
+	}
+
+	public function data_get_num_ips()
+	{
+		return array(
+			array(2, 1),
+			array(2, 2),
+			array(0, 3),
+		);
+	}
+
+	/**
+	 * @dataProvider data_get_num_ips
+	 */
+	public function test_get_num_ips($expected, $poster_id)
+	{
+		$this->assertSame($expected, phpbb_get_num_ips_for_poster($this->db, $poster_id));
+	}
+
+	public function data_get_num_posters()
+	{
+		return array(
+			array(2, '127.0.0.1'),
+			array(1, '127.0.0.2'),
+			array(1, '127.0.0.3'),
+			array(0, '127.0.0.4'),
+		);
+	}
+
+	/**
+	 * @dataProvider data_get_num_posters
+	 */
+	public function test_get_num_posters($expected, $ip)
+	{
+		$this->assertSame($expected, phpbb_get_num_posters_for_ip($this->db, $ip));
+	}
+}
author	Tristan Darricau <github@nicofuma.fr>	2017-04-21 23:54:18 +0200
committer	Tristan Darricau <github@nicofuma.fr>	2017-04-21 23:54:18 +0200
commit	a233f98bbfb95705d1f4f3b61b64b293f6bbacd0 (patch)
tree	03b92e3d0cd0ea8b237f408f1fa5c102fd24b0de
parent	ff22d54eae45a79f5bc59d2c04f3e02eed8e4981 (diff)
parent	e72f6d42366c6a0cbc128de406474192bc7cd8e2 (diff)
download	forums-a233f98bbfb95705d1f4f3b61b64b293f6bbacd0.tar forums-a233f98bbfb95705d1f4f3b61b64b293f6bbacd0.tar.gz forums-a233f98bbfb95705d1f4f3b61b64b293f6bbacd0.tar.bz2 forums-a233f98bbfb95705d1f4f3b61b64b293f6bbacd0.tar.xz forums-a233f98bbfb95705d1f4f3b61b64b293f6bbacd0.zip