<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>phpBB Digest Migration Tool</title>
<script type="text/javascript">
function valueRequired (id)
{
	x = document.getElementById(id);
	if (x.value.length == 0)
	{
		return false;
	}
	return true;
}
function validate_fields()
{
	if (valueRequired('phpBB2_hostname') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	if (valueRequired('phpBB2_port') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	if (valueRequired('phpBB2_db_name') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	if (valueRequired('phpBB2_db_user') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	if (valueRequired('phpBB2_db_pwd') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	if (valueRequired('phpBB2_prefix') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	if (valueRequired('phpBB3_hostname') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	if (valueRequired('phpBB3_port') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	if (valueRequired('phpBB3_db_name') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	if (valueRequired('phpBB3_db_user') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	if (valueRequired('phpBB3_db_pwd') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	if (valueRequired('phpBB3_prefix') == false)
	{
		alert(x.name + ' cannot be blank');
		return false;
	}
	return true;	
}
</script>
</head>
<body>
<h1>phpBB Digest Migration Tool</h1>
<?php
if (getenv('REQUEST_METHOD') == 'GET')
{
?>
<p>This tool is used to migrate users' digest settings from a phpBB 2.x Digests Installation to phpBB 3.x. You only need to run it once. However, it doesn't hurt if you run it more than once.</p>
<p>Important caveats:</p>
<ul>
	<li><strong>This migration works <em>only</em> with MySQL</strong>, as PHP MySQL libraries are used. phpBB libraries are <em>not</em> used.</li>
	<li>Both your phpBB2 forum database and your phpBB3 forum database must be accessible from your phpBB3 web server.</li>
	<li>This utility migrates user settings only, not digest adminstrator control panel settings</li>
	<li>Have the information in your config.php file handy for both your phpBB2 and phpBB3 forums</li>
</ul>
<p><strong>All fields must be filled in.</strong> If the field is blank or not filled in in your config.php file, use the default shown.</p>
<form action="<?php echo $PHP_SELF;?>" method="post" id="form1" onsubmit="return validate_fields();">
	<table width="90%" border="1" cellpadding="5" cellspacing="0" bgcolor="#CCFFFF">
		<tr>
			<td>
				<h2 align="center">phpBB2 Database Information</h2>
				<p>
					<label for="phpBB2_hostname">Hostname:</label>
					<input name="phpBB2_hostname" type="text" id="phpBB2_hostname" value="localhost" />
					<label for="phpBB2_port">Port:</label>
				<input name="phpBB2_port" type="text" id="phpBB2_port" value="80" size="5" maxlength="5" /></p>
				<p>
					<label for="phpBB2_db_name">Database Name:</label>
					<input type="text" name="phpBB2_db_name" id="phpBB2_db_name" value="" />
				</p>
				<p>
					<label for="phpBB_db_user">Database User:</label>
					<input type="text" name="phpBB2_db_user" id="phpBB2_db_user" value="" />
				</p>
				<p>
					<label for="phpBB2_db_pwd">Database Password:</label>
					<input type="text" name="phpBB2_db_pwd" id="phpBB2_db_pwd" value= "" />
				</p>
				<p>
					<label for="phpBB2_prefix">Database Table Prefix:</label>
					<input name="phpBB2_prefix" type="text" id="phpBB2_prefix" value="phpbb_" />
				</p>
			</td>
			<td>
				<h2 align="center">phpBB3 Database Information</h2>
				<p>
					<label for="phpBB3_hostname">Hostname:</label>
					<input name="phpBB3_hostname" type="text" id="phpBB3_hostname" value="localhost" />
					<label for="phpBB3_port">Port:</label>
					<input name="phpBB3_port" type="text" id="phpBB3_port" value="80" size="5" maxlength="5" />
				</p>
				<p>
					<label for="phpBB3_db_name">Database Name:</label>
					<input type="text" name="phpBB3_db_name" id="phpBB3_db_name" value="" />
				</p>
				<p>
					<label for="phpBB3_db_user">Database User:</label>
					<input type="text" name="phpBB3_db_user" id="phpBB3_db_user" value="" />
				</p>
				<p>
					<label for="phpBB3_db_pwd">Database Password:</label>
					<input type="text" name="phpBB3_db_pwd" id="phpBB3_db_pwd" value= "" />
				</p>
				<p>
					<label for="phpBB3_prefix">Database Table Prefix:</label>
					<input name="phpBB3_prefix" type="text" id="phpBB3_prefix" value="phpbb_" />
				</p>
		</td>
		</tr>
		<tr>
			<td colspan="2"><div align="center">
				<input type="reset" name="reset" id="reset" value="Reset" />
				<input type="submit" name="submit" id="submit" value="Begin Migration" />
			</div></td>
		</tr>
	</table>
</form>
<?php
}
else
{
	// Check first for the MySQL package
	if (!extension_loaded('mysql'))
	{
		echo '<p>MySQL is not supported. Cannot migrate digest settings.</p>';
		exit;
	}
	
	// Next, try to connect to the phpBB 2 host
	$phpBB2_hostname = htmlspecialchars($_POST['phpBB2_hostname']);
	$phpBB2_hostname = (trim($phpBB2_hostname) == '') ? 'localhost': $phpBB2_hostname;
	$phpBB2_port = htmlspecialchars($_POST['phpBB2_port']);
	$phpBB2_port = (trim($phpBB2_port) == '') ? '80': $phpBB2_port;
	$phpBB2_db_name = htmlspecialchars($_POST['phpBB2_db_name']);
	$phpBB2_db_user = htmlspecialchars($_POST['phpBB2_db_user']);
	$phpBB2_db_pwd = htmlspecialchars($_POST['phpBB2_db_pwd']);
	$phpBB2_prefix = htmlspecialchars($_POST['phpBB2_prefix']);
	
	$phpBB2 = mysql_connect(trim($phpBB2_hostname) . ':' . trim($phpBB2_db_port), trim($phpBB2_db_user), trim($phpBB2_db_pwd));
	if ($phpBB2 == false)
	{
		echo '<p>Cannot connect to phpBB2 database with information supplied. Cannot migrate digest settings.</p>';
		exit;
	}
	
	// Next, try to connect to the phpBB 2 database
	if (!mysql_select_db($phpBB2_db_name, $phpBB2))
	{
		echo sprintf('Could not connect to phpBB2 database %s. Cannot migrate digest settings.', $phpBB2_db_name);
		exit;
	}
	
	// Next, try to connect to the phpBB 3 host
	$phpBB3_hostname = htmlspecialchars($_POST['phpBB3_hostname']);
	$phpBB3_hostname = (trim($phpBB3_hostname) == '') ? 'localhost': $phpBB3_hostname;
	$phpBB3_port = htmlspecialchars($_POST['phpBB3_port']);
	$phpBB3_port = (trim($phpBB3_port) == '') ? '80': $phpBB3_port;
	$phpBB3_db_name = htmlspecialchars($_POST['phpBB3_db_name']);
	$phpBB3_db_user = htmlspecialchars($_POST['phpBB3_db_user']);
	$phpBB3_db_pwd = htmlspecialchars($_POST['phpBB3_db_pwd']);
	$phpBB3_prefix = htmlspecialchars($_POST['phpBB3_prefix']);
	
	$phpBB3 = mysql_connect(trim($phpBB3_hostname) . ':' . trim($phpBB3_db_port), trim($phpBB3_db_user), trim($phpBB3_db_pwd), true);
	if ($phpBB3 == false)
	{
		echo '<p>Cannot connect to phpBB3 database with information supplied. Cannot migrate digest settings.</p>';
		exit;
	}
	
	// Next, try to connect to the phpBB 3 database
	if (!mysql_select_db($phpBB3_db_name, $phpBB3))
	{
		echo sprintf('Could not connect to phpBB3 database %s. Cannot migrate digest settings.', $phpBB3_db_name);
		exit;
	}
	
	echo '<p>Processing...</p>';
	
	// Fetch the existing digest subscribers from the phpBB2 database	
	$phpBB2_sql = 'SELECT u.*, d.* FROM ' . $phpBB2_prefix . 'users u, ' . $phpBB2_prefix . 'mod_subscriptions d WHERE u.user_id = d.user_id AND u.user_active = 1 ORDER BY u.username';
	$result_phpBB2 = mysql_query($phpBB2_sql, $phpBB2);
	if (!$result_phpBB2)
	{
		echo '<p>Cannot run query: ' . $phpBB2_sql . ', error is: ' . mysql_error() . ' Cannot migrate digest settings.</p>';
		exit;
	}
	
	// When converting digest send time, in phpBB2 digest send hour is set for server time, in phpBB3 in GMT, so we need to know the offset in 
	// hours between server time and GMT.
	$timezone_offset = date('Z')/3600;
	
	// Here begins the main logic. We need to convert the user's digest settings and the forum subscriptions, if any exist
	while ($row = mysql_fetch_assoc($result_phpBB2))
	{

		// Does this username exist in the phpBB3 database?
		$phpBB3_sql = 'SELECT * FROM ' . $phpBB3_prefix . "users u WHERE username = '" . $row['username'] . "'";
		$result_phpBB3 = mysql_query($phpBB3_sql, $phpBB3);
		if (mysql_num_rows($result_phpBB3) == 0)
		{
			echo 'Username ' . $row['username'] . ' does not exist in phpBB3 database, skipping...<br />';
		}
		else
		{
			mysql_free_result($phpBB3_sql);
			
			// Create translations for a user's digest settings
			$show_mine = ($row['show_mine'] == 'YES') ? 1 : 0;
			$new_only = ($row['new_only'] == 'TRUE') ? 1 : 0;
			$send_on_no_posts = ($row['send_on_no_messages'] == 'YES') ? 1 : 0;
			$send_hour = $row['send_hour'] - $timezone_offset;
			$send_hour = ($send_hour < 0) ? $send_hour + 24 : $send_hour;
			$send_hour = ($send_hour >= 24) ? $send_hour - 24 : $send_hour;
			$text_length = ($row['text_length'] == 32000) ? 0 : $row['text_length'];
			
			// Create SQL to update the phpbb_users table for the phpBB3 database
			$phpBB3_sql = 'UPDATE ' . $phpBB3_prefix . "users 
				SET user_digest_type = '" . $row['digest_type'] . "',
				user_digest_format = '" . $row['format'] . "',
				user_digest_show_mine = " . $show_mine . ",
				user_digest_new_posts_only = " . $new_only . ",
				user_digest_send_on_no_posts = " . $send_on_no_posts . ",
				user_digest_send_hour_gmt = " . $send_hour . ",
				user_digest_max_display_words = " . $text_length . "
				WHERE username = '" . $row['username'] . "'";
			//echo $phpBB3_sql . '<br />';
			
			// Move this user's digest settings
			$result_phpBB3 = mysql_query($phpBB3_sql, $phpBB3);
			if (!$result_phpBB3)
			{
				echo sprintf('Unable to store digest settings for user %s. Exiting on the assumption that there is a general problem.', $row['username']);
				exit;
			}
			echo sprintf("Digest settings for %s moved.<br />", $row['username']);
			
			// If there are any individual forum subscriptions, move them too.
			$phpBB2_sql_2 = 'SELECT u.*, f.*, s.* 
				FROM ' . $phpBB3_prefix . 'users u, ' . $phpBB3_prefix . 'forums f, ' . $phpBB3_prefix . "mod_subscribed_forums s 
				WHERE s.user_id = u.user_id 
				AND s.forum_id = f.forum_id 
				AND u.username = '" . $row['username'] . "'";
			$result_phpBB2_2 = mysql_query($phpBB2_sql_2, $phpBB2);
			if (!$result_phpBB2_2)
			{
				echo '<p>Cannot run query: ' . $phpBB2_sql_2 . ', error is: ' . mysql_error() . '. Cannot continue to migrate digest settings.</p>';
				exit;
			}
			
			if (mysql_num_rows($result_phpBB2_2) > 0) // This user has forum subscriptions
			{
			
				while ($row2 = mysql_fetch_assoc($result_phpBB2_2))
				{
				
					// echo $row2['username'] . ' has a subscription to a forum named ' . $row2['forum_name'] . '<br />';
					
					// Does this forum subscription already exists in the phpBB3 database?
					$phpBB3_sql_2 = 'SELECT u.*, f.*, s.* 
						FROM ' . $phpBB3_prefix. 'users u, ' . $phpBB3_prefix . 'forums f, ' . $phpBB3_prefix . "digests_subscribed_forums s
						WHERE s.user_id = u.user_id AND s.forum_id = f.forum_id 
						AND u.username = '" . $row2['username'] . "' AND f.forum_name = '" . $row2['forum_name'] . "'";
					$result_phpBB3_2 = mysql_query($phpBB3_sql_2, $phpBB3);
					if (!$result_phpBB3_2)
					{
						echo '<p>Cannot run query: ' . $phpBB3_sql_2 . ', error is: ' . mysql_error() . '. Cannot continue to migrate digest settings.</p>';
						exit;
					}

					if (mysql_num_rows($result_phpBB3_2) == 0) // There are no forum subscriptions in the phpBB3 database for this forum
					{
					
						// Create the forum subscription
						
						// Find the associated forum_id in the phpBB3 database
						$phpBB3_sql_3 = 'SELECT forum_id 
							FROM ' . $phpBB3_prefix. "forums f 
							WHERE forum_name = '" . $row2['forum_name'] . "'";
						$result_phpBB3_3 = mysql_query($phpBB3_sql_3, $phpBB3);
						$row3 = mysql_fetch_assoc($result_phpBB3_3);
						
						// Perform the insert to replicate the forum subscription
						$phpBB3_sql_4 = 'INSERT INTO ' . $phpBB3_prefix . 'digests_subscribed_forums (user_id, forum_id) VALUES (' . $row2['user_id'] . ', ' . $row3['forum_id'] . ')';
						//echo $phpBB3_sql_4 . '<br />';
						$result_phpBB3_4 = mysql_query($phpBB3_sql_4, $phpBB3);
						if (!$result_phpBB3_4)
						{
							echo '<p>Cannot run query: ' . $phpBB3_sql_4 . ', error is: ' . mysql_error() . '. Cannot continue to migrate digest settings.</p>';
							exit;
						}
						echo sprintf("Subscribed user %s to forum \"%s\" in the phpBB3 database.<br />", $row2['username'], $row2['forum_name']);
						
					}
					else
					{
						echo sprintf("User %s already has a subscription to forum \"%s\" in the phpBB3 database.<br />", $row2['username'], $row2['forum_name']);
					}
					
				}
				
			}
			
		}
		
		
	}
	
	echo '<p>Migration complete!</p>'; 
}
?>
</body>
</html>