#!/usr/bin/perl
use CGI::Carp qw(fatalsToBrowser);

######################################################################
#### Import from phpbb2 1.9 Forum into Vanilla 1.1.4.
####
#### 25. Oct. 2007 keith@salmacis.com Keith Bayer
####
#### This little Script worked exremely well within my environment; 
#### no support or warranty is given at any time
####
#### Notes:
#### - This imports only the Categories and Posts and Users into empty (!) Vanilla Tables. 
####	We do a Truncate here!
#### - we throw away all votes and private messages
#### - We expect your vanilla admin to have the User ID 1 in the Vanilla User- Table.
#### 	That is why we skip importing the UserID "1"
#### - Because in phpbb2 we habe the User-ID "-1" for "anonymous" we need to change the UserID- Fields 
####	in the "User" Table to Mediumint! DON'T FORGET THIS!
#### - Make sure we got the same phpBB2 user table: do you have the fields user_website,user_from,user_interests ? if not, change the code!
#### - Afterwards you might want to change user previleges in Vanilla via your Browser/ Admin Account
#### - All you have to adjust are a few settings within the first lines of this programm
#### -  Run this Programm via your SSH Shell or via your Webbrowser; don't forget chmod.
####
######################################################################
print "Content-type: text/html\n\n";
use POSIX; # strftime() lives here!

# Adjust settings here
# Table Prefix
my$prefix_PHPBB2="phpbb_";
my$prefix_VANILLA="vanilla_"; 

# Define Database Name/Host/Login/PW
use DBI();
# Database Settings where new vanilla forum is stored (PASSWORD-HERE! / DATABASE-NAME-HERE!; you might have to change the hostname and login).
$dbh_v=DBI->connect("DBI:mysql:database=DATABASE-NAME-HERE;host=localhost","root","PASSWORD-HERE!") or print "$DBI::db_errstr\n";
# Database Settings where old phpbb2 forum is stored
$dbh_p=DBI->connect("DBI:mysql:database=DATABASE-NAME-HERE;host=localhost","root","PASSWORD-HERE!") or print "$DBI::db_errstr\n";

# you have a smilies-Database and want them to be converted? this may take up some time (which doesn't matter, import only needs to be done once...), 
# because within every single post the programm looks up for all shortcuts (i.e. ":hammer:")
# and replaces them with the smiley- GIF Name. Check out the routine somewhere below. 
# 1 = on. 0=off
my$convert_smilies=1;
my$PATHTOSMILIES="/images/smilies/";

#Tables, those are the fields we take care of:
#
# PHPBB2 ###############
#
# posts 
#post_id autoincrement
#topic_id
#forum_id
#poster_id
#post_time timeunixstamp 1168891662  
#
# posts_text
#post_id 
#post_subject 
#post_text 
#
# forums
#forum_id  
#cat_id  
#forum_name
#forum_desc  
#
# topics
#topic_id (autoincrement)
#forum_id
#topic_title
#topic_poster (user id wanted)
#topic_time (timeunixstamp like this: 1168891662)
#topic_first_post_id (post_id is meant)
#topic_last_post_id
#
# users (we only import active Users!)
#user_id
#username
#user_active
#user_password (this is md5 encoded, same way as in vanilla)
#user_regdate timeunixstamp 1168891662 
#user_sig
#user_email

# Vanilla ###########
# Category (equivalent to: forums)
#CategoryID
#Name
#Description
#
# Comment (eq to:posts)
#CommentID 	
#DiscussionID
#AuthUserID
#DateCreated (SQL Timestamp like this: 2007-10-19 13:46:12 )
#FormatType (we set default BBCode !)
#
# Discussion (eq to: topics)
#DiscussionID
#AuthUserID
#FirstCommentID
#LastUserID
#Name
#DateCreated (SQL Timestamp like this: 2007-10-19 13:46:12 )
#DateLastActive  (SQL Timestamp like this: 2007-10-19 13:46:12 )
#
# user
#UserID
#FirstName (we prefill this with the user Name)
#Name (user Name)
#Password
#Email
#DateFirstVisit (SQL Timestamp like this: 2007-10-19 13:46:12 )
#DefaultFormatType (we set BBCode as default)

print "Start<br>\n";

######################################################################

# Import Usertable from Phpbb2 to Vanilla
my$counter=0;
$sth = &sqlexec("DELETE FROM ${prefix_VANILLA}User WHERE UserID !=1",$dbh_v);
$sth = &sqlexec("UPDATE ${prefix_PHPBB2}users SET user_active='1' WHERE user_id ='-1'",$dbh_p);
$sth = &sqlexec("SELECT user_id,username,user_active,user_password,user_regdate,user_sig,user_email,user_posts,user_lastvisit,user_sig,user_website,user_from,user_interests FROM ${prefix_PHPBB2}users WHERE user_id !=1 AND user_active=1",$dbh_p);
while (my($user_id,$username,$user_active,$user_password,$user_regdate,$user_sig,$user_email,$user_posts,$user_lastvisit,$user_sig,$user_website,$user_from,$user_interests) = &sqlfetch($sth)) {

	# Convert Timestamps! PHPBB2 and Vanilla use different formats
	my($year,$mon,$mday)=retimestamp_unix($user_regdate);
	$user_regdate="$year-$mon-$mday";

	my($year,$mon,$mday)=retimestamp_unix($user_lastvisit);
	$user_lastvisit="$year-$mon-$mday";
	
	$username=&slashquote($username);
	
	# Format New Signature and other profile-Information; Example ("s:[value]" defines the length!):
	#a:6:{i:0;a:2:
	#{s:5:"Label";s:8:"Webseite";s:5:"Value";s:21:"http://keith.keith.de";}
	#i:1;a:2:{s:5:"Label";s:7:"Wohnort";s:5:"Value";s:7:"Westend";}
	#i:2;a:2:{s:5:"Label";s:10:"Interessen";s:5:"Value";s:18:"Mittwochsseelsorge";}
	#i:3;a:2:{s:5:"Label";s:14:"Beste Konzerte";s:5:"Value";s:68:"der jüngsten Zeit: Nine Inch Nails Circus Krone München 03.09.2007";}
	#i:4;a:2:{s:5:"Label";s:13:"Lieblingsband";s:5:"Value";s:10:"Pink Floyd";}
	#i:5;a:2:{s:5:"Label";s:8:"Shitlist";s:5:"Value";s:5:"Raver";}
	#}

	$user_interests=umlaut_delete($user_interests);
	$user_interests=~ s/\&amp\;/\&/g;
	
	$user_website=umlaut_delete($user_website);
	$user_website=~ s/\&amp\;/\&/g;
	
	$user_from=umlaut_delete($user_from);
	$user_from=~ s/\&amp\;/\&/g;
	
	if (!$user_interests) { $user_interests="-"; }
	my$user_interests_length=length($user_interests);

	if (!$user_website) { $user_website="-"; }
	my$user_website_length=length($user_website);

	if (!$user_from) { $user_from="-"; }
	my$user_from_length=length($user_from);

	# "Attributes" gets some Information
	my$attributes=<<__END__;
a:6:{i:0;a:2:{s:5:"Label";s:7:"Website";s:5:"Value";s:$user_website_length:"$user_website";}i:1;a:2:{s:5:"Label";s:4:"City";s:5:"Value";s:$user_from_length:"$user_from";}i:2;a:2:{s:5:"Label";s:9:"Interests";s:5:"Value";s:$user_interests_length:"$user_interests";}}
__END__
	$attributes=slashquote($attributes);

	# "Preferences" too (Signature)
	my$preferences="";
	if ($user_sig) {
		$user_sig=umlaut_delete($user_sig);
		$user_sig=~ s/\&amp\;/\&/g;
		my$sig_length=length($user_sig);
		$preferences=<<__END__;
a:2:{s:10:"FLICKR_URL";s:0:"";s:9:"SIGNATURE";s:$sig_length:"$user_sig";}
__END__
		$preferences=slashquote($preferences);
	}
	my$RoleID=3;
	if ($user_id eq "-1") { $RoleID=2; } # Guest account

	$sth2 = &sqlexec("INSERT INTO ${prefix_VANILLA}User (UserID,RoleID,FirstName,Name,Password,Email,DateFirstVisit,DefaultFormatType,CommentSpamCheck,CountComments,ShowName,DateLastActive,Preferences,Attributes)
		VALUES ('$user_id','$RoleID','$username','$username','$user_password','$user_email','$user_regdate','BBCode','1','$user_posts','1','$user_lastvisit','$preferences','$attributes')",$dbh_v);

	$counter++;
}
$sth = &sqlexec("UPDATE ${prefix_PHPBB2}users SET user_active='0' WHERE user_id ='-1'",$dbh_p); # undo change
my$errormessage="";
if (!$counter) { $errormessage="ERROR occured! "; }
print "Finished Import of $counter Users $errormessage<br>\n";


######################################################################
# Import Categories
my$counter=0;
$sth = &sqlexec("TRUNCATE TABLE ${prefix_VANILLA}Category",$dbh_v);
$sth = &sqlexec("SELECT forum_id ,cat_id,forum_name,forum_desc FROM ${prefix_PHPBB2}forums WHERE forum_id>0 ORDER BY forum_id ASC",$dbh_p);
while ( my($forum_id ,$cat_id,$forum_name,$forum_desc) = &sqlfetch($sth)) {

	# Insert into Vanilla
	$forum_name=&slashquote($forum_name);
	$forum_desc=&slashquote($forum_desc);
	$sth2 = &sqlexec("INSERT INTO ${prefix_VANILLA}Category (CategoryID,Name,Description) 
		VALUES ('$forum_id','$forum_name','$forum_desc')",$dbh_v);
	$counter++;
}
my$errormessage="";
if (!$counter) { $errormessage="ERROR occured! "; }
print "Finished $counter Categories $errormessage<br>\n";

######################################################################

# Topics Discussions next
my$counter=0;
# Convert timestamps

$sth = &sqlexec("TRUNCATE TABLE ${prefix_VANILLA}Discussion",$dbh_v);

$sth = &sqlexec("SELECT topic_id,forum_id,topic_title,topic_poster,topic_time,topic_first_post_id ,topic_last_post_id,topic_replies FROM ${prefix_PHPBB2}topics WHERE topic_id >1 ORDER BY topic_id ASC",$dbh_p);
while (my($topic_id,$forum_id,$topic_title,$topic_poster,$topic_time,$topic_first_post_id ,$topic_last_post_id,$topic_replies) = &sqlfetch($sth)) {

	# Get the Date and UserID of "$topic_last_post_id"
	$sth2 = &sqlexec("SELECT ${prefix_PHPBB2}posts.post_id,topic_id,forum_id,poster_id,post_time,
	${prefix_PHPBB2}posts_text.post_subject,post_text FROM ${prefix_PHPBB2}posts, ${prefix_PHPBB2}posts_text
		WHERE ${prefix_PHPBB2}posts.post_id ='$topic_last_post_id' AND ${prefix_PHPBB2}posts_text.post_id =${prefix_PHPBB2}posts.post_id LIMIT 1",$dbh_p);
	my($topic_last_post_id_post_id, $topic_last_post_id_topic_id, $topic_last_post_id_forum_id,$topic_last_post_id_poster_id,$topic_last_post_id_post_time,$topic_last_post_id_post_subject, $topic_last_post_id_post_text) = &sqlfetch($sth2);
	my($year,$mon,$mday)=retimestamp_unix($topic_last_post_id_post_time);
	$topic_last_post_id_post_time="$year-$mon-$mday";

	# "&amp;" ?
	$topic_title=~ s/\&amp\;/\&/g;
	$topic_title=~ s/\&gt\;/\>/g;
	$topic_title=~ s/\&lt\;/\</g;
	$topic_title=~ s/\&quot\;/\"/g;
	
	my($year,$mon,$mday)=retimestamp_unix($topic_time);
	$topic_time="$year-$mon-$mday";
	$topic_title=&slashquote($topic_title);

	# WhisperUserID =0 is important for the Vanilla Search within comments
	$sth2 = &sqlexec("INSERT INTO ${prefix_VANILLA}Discussion (DiscussionID,CategoryID,AuthUserID,FirstCommentID,LastUserID,Name,DateCreated,DateLastActive,CountComments,WhisperUserID) 
		VALUES ('$topic_id','$forum_id','$topic_poster','$topic_first_post_id','$topic_last_post_id_poster_id','$topic_title','$topic_time','$topic_last_post_id_post_time','$topic_replies','0')",$dbh_v);
	$counter++;
}

# I couldn't figure it out why "WhisperUserID=0" didn't work, so we repeat it.
$sth = &sqlexec("UPDATE ${prefix_VANILLA}Discussion SET WhisperUserID=0",$dbh_v);

my$errormessage="";
if (!$counter) { $errormessage="ERROR occured! "; }
print "Finished $counter Topics/ Discussions $errormessage>br>\n";


######################################################################

# And now: The Posts 
my$counter=0;

$sth = &sqlexec("TRUNCATE TABLE ${prefix_VANILLA}Comment",$dbh_v);

$sth = &sqlexec("SELECT ${prefix_PHPBB2}posts.post_id,topic_id,forum_id,poster_id,post_time,post_username,
	${prefix_PHPBB2}posts_text.post_subject,post_text FROM ${prefix_PHPBB2}posts, ${prefix_PHPBB2}posts_text
	WHERE ${prefix_PHPBB2}posts.post_id >0 AND ${prefix_PHPBB2}posts_text.post_id =${prefix_PHPBB2}posts.post_id ORDER BY ${prefix_PHPBB2}posts.post_id",$dbh_p);
while (my($post_id, $topic_id, $forum_id,$poster_id,$post_time,$post_username,$post_subject, $post_text) = &sqlfetch($sth)) {

	# Convert Timestamps!
	my($year,$mon,$mday)=retimestamp_unix($post_time);
	$post_time="$year-$mon-$mday";

	# Smileys
	if ($convert_smilies ==1) { 
		$sth2 = &sqlexec("SELECT code,smile_url FROM ${prefix_PHPBB2}smilies WHERE smilies_id >0",$dbh_p);
		while (my($sm_code,$sm_url) = &sqlfetch($sth2)) {
			$sm_code=~ s/\(/\\(/g;
			$sm_code=~ s/\)/\\)/g;
			$sm_code=~ s/\:/\\:/g;
			$sm_code=~ s/\?/\\?/g;
			$sm_url ="[img]".$PATHTOSMILIES.$sm_url."[/img]";
       		$post_text=~s/$sm_code/$sm_url/g;
     	}
	}

	# delete phpbb2 things like "[/img:db7c87780b]"
	$post_text=~ s|(\[/u):(.+?)(\])|$1$3|g;
	$post_text=~ s|(\[u):(.+?)(\])|$1$3|g;
	$post_text=~ s|(\[/quote):(.+?)(\])|$1$3|g;
	$post_text=~ s|(\[quote):(.+?)=(\])|$1$3|g; # take care of "="
	$post_text=~ s|(\[quote):(.+?)(\])|$1$3|g;
	$post_text=~ s|(\[/i):(.+?)(\])|$1$3|g;
	$post_text=~ s|(\[i):(.+?)(\])|$1$3|g;
	$post_text=~ s|(\[/img):(.+?)(\])|$1$3|g;
	$post_text=~ s|(\[img):(.+?)(\])|$1$3|g;
	$post_text=~ s|(\[b):(.+?)(\])|$1$3|g;
	$post_text=~ s|(\[/b):(.+?)(\])|$1$3|g;
	$post_text=~ s|(\[/color):(.+?)(\])|$1$3|g;
	$post_text=~ s|(\[color):(.+?)(\])|$1$3|g;
	
	# Make Paragraphs in bbcode, otherwise the comments look ugly
	$post_text=~ s/\n/[p][\/p]/g; 
	#$post_text=~ s/\r/[p][\/p]/g;
	# "&amp;" etc.? 
	$post_text=~ s/\&amp\;/\&/g;
	$post_text=~ s/\&gt\;/\>/g;
	$post_text=~ s/\&lt\;/\</g;
	$post_text=~ s/\&quot\;/\"/g;
	$post_text=~ s/>br \/>/\\n/g;

	$post_subject=&slashquote($post_subject);
	$post_text=&slashquote($post_text);

	# we set default BBCode
	$sth2 = &sqlexec("INSERT INTO ${prefix_VANILLA}Comment (CommentID,DiscussionID,AuthUserID,DateCreated,FormatType,Body) 
		VALUES ('$post_id','$topic_id','$poster_id','$post_time','BBCode','$post_text')",$dbh_v);
		
	$counter++;
}
my$errormessage="";
if (!$counter) { $errormessage="ERROR occured! "; }
print "Finished $counter Posts $errormessage<br>\n";
print "Done.<br>\n";


######################################################################

sub sqlexec()
{
        my ($sqlstring,$dbh) = @_;
		my ($sth);
        $sth = $dbh->prepare($sqlstring);
        $sth->execute ||  print "Error: $dbh->db_errstr SQL:\n$sqlstring\n<br>";
        #$sth->execute;

        return $sth;
}

sub sqlfetch()
{
	my ($sth) = @_;
	return $sth->fetchrow_array;	
}

## 

sub retimestamp_unix()
{

    # Übergabeparameter holen
    my($timestamp_unix) = @_;
    #my$timestamp_unix_yesterday=$timestamp_unix-86400;
    my$date = strftime("%Y-%m-%d", localtime($timestamp_unix));
    my($year,$mon,$mday) = split(/\-/, $date);

    # Werte zurückgeben
    return ($year,$mon,$mday);
}

# Quotes,... 
sub slashquote {

    my ($temp) = @_;

	# some comments already got a slash,... 
	$temp =~ s/\\//g;

	$temp =~ s/\"/\\\"/g;
	$temp =~ s/\'/\\\'/g;
	$temp =~ s/\´/\\\'/g;
	return $temp;
}
#
sub umlaut_delete {

	my ($temp) = @_;
    $temp =~ s/ü/ue/g;
    $temp =~ s/ä/ae/g;
    $temp =~ s/ö/oe/g;
    $temp =~ s/Ä/Ae/g;
    $temp =~ s/Ü/Ue/g;
    $temp =~ s/Ö/Oe/g;
    $temp =~ s/ß/ss/g;

    #$temp =~ s/\&uuml;/ue/g;
    #$temp =~ s/\&auml;/ae/g;
    #$temp =~ s/\&ouml;/oe/g;
    #$temp =~ s/\&Auml;/Ae/g;
    #$temp =~ s/\&Uuml;/Ue/g;
    #$temp =~ s/\&Ouml;/Oe/g;

	return $temp;
}

1;