#!/usr/bin/perl
# PHP Nuke 4.x to XOOPS migration script
# Copyright (C) 2004-2005 Vlatko Kosturjak and Robert Avilov
$| = 1;
use DBI;

print "PHPNuke2XOOPS migration\n\n";

$src_user="root";
$src_password="";
$dst_user="root";
$dst_password="";

$src_dsn = "DBI:mysql:database=phpnuke;host=localhost";
$src_dbh = DBI->connect ($src_dsn,$src_user,$src_password) or die "src db";

$dst_dsn = "DBI:mysql:database=xoops;host=localhost";
$dst_dbh = DBI->connect ($dst_dsn,$dst_user,$dst_password) or die "dst db";


# TOPICS
print "topics...\n";
$srctbl_topics="topics";
$dsttbl_topics="xoops_topics";
eval { $dst_dbh->do("DELETE FROM $dsttbl_topics") };
print "Error deleting $dsttbl_topics: $@\n" if $@;

$src_sth=$src_dbh->prepare("SELECT * FROM $srctbl_topics");
$src_sth->execute;


while (my $ref=$src_sth->fetchrow_hashref()) {
	eval { 
		$sql="INSERT INTO $dsttbl_topics(topic_id,topic_pid,topic_imgurl,topic_title) VALUES ($ref->{'topicid'},0,'$ref->{'topicimage'}','$ref->{'topictext'}')";
#		print $sql."\n";
		$dst_dbh->do($sql)
	};
	print STDERR "Error inserting $dsttbl_topics: $sql : $@\n" if $@;

}
$src_sth->finish;

# NEWS/STORIES
print "stories...\n";
$srctbl_stories="stories";
$dsttbl_stories="xoops_stories";
eval { $dst_dbh->do("DELETE FROM $dsttbl_stories") };
print "Error deleting $dsttbl_stories: $@\n" if $@;

$src_sth=$src_dbh->prepare("SELECT * FROM $srctbl_stories");
$src_sth->execute;

$story_author="0";
$story_hostname="127.0.0.1";
$story_type="admin";

while (my $ref=$src_sth->fetchrow_hashref()) {
	eval { 
		$hometext=replacequotes ($ref->{'hometext'});
		$bodytext=replacequotes ($ref->{'bodytext'});
		$title=replacequotes ($ref->{'title'});
		$newsaddedtext="Odobrio: ".$ref->{'aid'}.". ";
		$newsaddedtext=$newsaddedtext."Napisao: ".$ref->{'informant'}.". ";
		$newsaddedtext=$newsaddedtext."<br>";
		$hometext=$newsaddedtext.$hometext;
		$sql="INSERT INTO $dsttbl_stories(storyid,uid,title,created,published,expired,hostname,nohtml,nosmiley,hometext,bodytext,counter,topicid,ihome,notifypub,story_type,topicdisplay,topicalign,comments) VALUES ($ref->{'sid'},$story_author,'$title',UNIX_TIMESTAMP('$ref->{'time'}'),UNIX_TIMESTAMP('$ref->{'time'}'),0,'$story_hostname',0,0,'$hometext','$bodytext',$ref->{'counter'},$ref->{'topic'},0,0,'$story_type',1,'R',$ref->{'comments'})";
#		print $sql."\n";
		$dst_dbh->do($sql) or print "$sql";
	};
	print STDERR "Error inserting $dsttbl_stories: $sql : $@\n" if $@;

}
$src_sth->finish;

# COMMENTS
print "comments...\n";
$srctbl_comments="comments";
$dsttbl_comments="xoops_xoopscomments";
eval { $dst_dbh->do("DELETE FROM $dsttbl_comments") };
print "Error deleting $dsttbl_comments: $@\n" if $@;

$src_sth=$src_dbh->prepare("SELECT * FROM $srctbl_comments");
$src_sth->execute;

# module id, for comments
$sth = $dst_dbh->prepare("SELECT mid FROM xoops_modules WHERE dirname='news'");
$sth->execute;
my $tmp = $sth->fetchrow_hashref();
my $com_modid = $tmp->{'mid'};
$sth->finish;

$comment_author="0";
$com_icon="";
$com_signature=0;
$com_status=2;
$com_exparams="";
$dohtml=1;
$dosmiley=1;
$doxcode=1;
$doimage=1;
$dobr=1;

while (my $ref=$src_sth->fetchrow_hashref()) {
	eval { 
		$comaddtext="";
		$name=replacequotes ($ref->{'name'});
		$email=web_email(replacequotes ($ref->{'email'}));
		$subject=replacequotes ($ref->{'subject'}); # = comtitle
		$comment=replacequotes ($ref->{'comment'});
		$url=replacequotes ($ref->{'url'});
		$host_name=replacequotes ($ref->{'host_name'});
		if ($ref->{'pid'}==0) {
			$com_rootid=$ref->{'tid'};
		} else {
			$mytid=$ref->{'pid'};
			$myrootid=0;
			do {
				$tmp_sth=$src_dbh->prepare("SELECT pid FROM $srctbl_comments WHERE tid=$mytid");
				$tmp_sth->execute;
				if (my $tmpref=$tmp_sth->fetchrow_hashref()) {
					if ($tmpref->{'pid'}==0) {
						$myrootid=$mytid;
					} else {
						$mytid=$tmpref->{'pid'};
					}
					$com_rootid=$tmpref->{'mintid'};
				} else {
					print STDERR "Can't find min(tid) in comments, using kludgy solution...\n";
					$myrootid=$ref->{'pid'};
				}
				$tmp_sth->finish();
			} while ($myrootid==0);
			$com_rootid=$myrootid;
		}
		# add comment author/email/url
		if ($name ne "") {
			$comaddtext="Napisao: ".$name." ";
			$comaddtext=$comaddtext."(".$email.") "  if ($email ne "");
			$comaddtext=$comaddtext."(".$url.") "  if ($url ne "");
			$comaddtext=$comaddtext."<br>";
			
		}

		$comment=$comaddtext.$comment;

		$sql="INSERT INTO $dsttbl_comments(com_id,com_pid,com_rootid,com_modid,com_itemid,com_icon,com_created,com_modified,com_uid,com_ip,com_title,com_text,com_sig,com_status,com_exparams,dohtml,dosmiley,doxcode,doimage,dobr) VALUES ($ref->{'tid'},$ref->{'pid'},$com_rootid,$com_modid,$ref->{'sid'},'$com_icon',UNIX_TIMESTAMP('$ref->{'date'}'),UNIX_TIMESTAMP('$ref->{'date'}'),$comment_author,'$host_name','$subject','$comment',$com_signature,$com_status,'$com_exparams',$dohtml,$dosmiley,$doxcode,$doimage,$dobr)";
#		print $sql."\n";
		$dst_dbh->do($sql) or print STDERR "$sql\n";
	};
	print STDERR "Error inserting $dsttbl_comments: $sql : $@\n" if $@;

}
$src_sth->finish;

# SECTIONS (CATEGORIES)
print "sections (categories)...\n";
$srctbl_sections="sections";
$dsttbl_sections="xoops_sections";
eval { $dst_dbh->do("DELETE FROM $dsttbl_sections") };
print "Error deleting $tbl_sections: $@\n" if $@;

$src_sth=$src_dbh->prepare("SELECT * FROM $srctbl_sections");
$src_sth->execute;


while (my $ref=$src_sth->fetchrow_hashref()) {
	eval { 
		$sql="INSERT INTO $dsttbl_sections(secid,secname,image) VALUES ($ref->{'secid'},'$ref->{'secname'}','$ref->{'image'}')";
#		print $sql."\n";
		$dst_dbh->do($sql) or print "$sql";
	};
	print STDERR "Error inserting $dsttbl_sections: $sql : $@\n" if $@;

}
$src_sth->finish;

# SECTIONS (CONTENT)
print "sections (content)...\n";
$srctbl_seccont="seccont";
$dsttbl_seccont="xoops_seccont";
eval { $dst_dbh->do("DELETE FROM $dsttbl_seccont") };
print "Error deleting $dsttbl_seccont: $@\n" if $@;

$src_sth=$src_dbh->prepare("SELECT * FROM $srctbl_seccont");
$src_sth->execute;


while (my $ref=$src_sth->fetchrow_hashref()) {
	eval { 
		$title=replacequotes($ref->{'title'});
		$content=replacequotes($ref->{'content'});
		$sql="INSERT INTO $dsttbl_seccont(artid,secid,title,content,counter) VALUES ($ref->{'artid'},$ref->{'secid'},'$title','$content',$ref->{'counter'})";
#		print $sql."\n";
		$dst_dbh->do($sql) or print "$sql";
	};
	print STDERR "Error inserting $dsttbl_seccont: $sql : $@\n" if $@;

}
$src_sth->finish;

# WEBLINKS (CATEGORIES)
print "weblinks (categories)...";
$srctbl_weblinkscat="links_categories";
$srctbl_weblinkscatsub="links_subcategories";
$dsttbl_weblinkscat="xoops_mylinks_cat";
eval { $dst_dbh->do("DELETE FROM $dsttbl_weblinkscat") };
print "Error deleting $dsttbl_weblinkscat: $@\n" if $@;

$src_sth=$src_dbh->prepare("SELECT * FROM $srctbl_weblinkscat");
$src_sth->execute;

$mycid=1;
while (my $ref=$src_sth->fetchrow_hashref()) {
	eval { 
		$sql="INSERT INTO $dsttbl_weblinkscat(cid,pid,title,imgurl) VALUES ($mycid,0,'$ref->{'title'}','')";
#		print $sql."\n";
		$mycid++;
		$dst_dbh->do($sql) or print "$sql";
	};
	print STDERR "Error inserting $dsttbl_weblinkscat: $sql : $@\n" if $@;

}
$src_sth->finish;
# WEBLINKS/SUBCATEGORIES
$src_sth=$src_dbh->prepare("SELECT * FROM $srctbl_weblinkscatsub");
$src_sth->execute;

$mysid=$mycid;
while (my $ref=$src_sth->fetchrow_hashref()) {
	eval { 
		$sql="INSERT INTO $dsttbl_weblinkscat(cid,pid,title,imgurl) VALUES ($mysid,$ref->{'cid'},'$ref->{'title'}','')";
		$mysid++;
#		print $sql."\n";
		$dst_dbh->do($sql) or print "$sql";
	};
	print STDERR "Error inserting $dsttbl_weblinkscat: $sql : $@\n" if $@;

}
$src_sth->finish;
print "\n";

# REVIEWS
print "reviews...\n";
foreach (qw(
	xoops_myReviews_cat xoops_myReviews_downloads xoops_myReviews_editorials xoops_myReviews_excerpt
	xoops_myReviews_mod xoops_myReviews_ratingcat xoops_myReviews_text xoops_myReviews_votecat
	xoops_myReviews_votedata xoops_myReviews_reviews
)) {
	eval { $dst_dbh->do("DELETE FROM $_") };
	print "Error deleting $_: $@\n" if $@;
}

# review categories
my @reviews_cat = (
	{ 'title' => 'Hardver', 'imgurl' => 'http://www.linux.hr/images/reviews/hardware.gif' },
	{ 'title' => 'Softver', 'imgurl' => 'http://www.linux.hr/images/reviews/software.gif' },
	{ 'title' => 'Knjige', 'imgurl' => 'http://www.linux.hr/images/reviews/books.gif' },
	{ 'title' => 'Razno', 'imgurl' => 'http://www.linux.hr/images/reviews/unsorted.gif' }
);
my $parent_category = 0;
foreach (@reviews_cat) {
	my ($title, $imgurl) = ($_->{'title'}, $_->{'imgurl'});
	eval { $dst_dbh->do("INSERT INTO xoops_myReviews_cat (pid, title, imgurl) VALUES ($parent_category, '$title', '$imgurl')"); };
	print "Error inserting xoops_myReviews_cat: $@\n" if $@;
}

# default category
$sth = $dst_dbh->prepare("SELECT cid FROM xoops_myReviews_cat WHERE title='Softver'");
$sth->execute;
my $tmp = $sth->fetchrow_hashref();
my $review_cat = $tmp->{'cid'};
$sth->finish;

# last review id
$sth = $dst_dbh->prepare("SELECT MAX(lid) AS last_id FROM xoops_myReviews_text");
$sth->execute;
my $tmp = $sth->fetchrow_hashref();
my $review_id = $tmp->{'last_id'};
$sth->finish;

my $review_user = 0;
my $review_comment_user = 0;
my $review_hostname = '127.0.0.1';
my $src_sth_main = $src_dbh->prepare("SELECT *, UNIX_TIMESTAMP(date) AS timestamp FROM reviews");
$src_sth_main->execute;
while (my $ref_main = $src_sth_main->fetchrow_hashref()) {
	# next id
	$review_id++;

	eval {
		my @author = ();
		push(@author, $ref_main->{'reviewer'}) if $ref_main->{'reviewer'};
		push(@author, '&lt;'.web_email($ref_main->{'email'}).'&gt;') if $ref_main->{'email'};
		my $author = join(' ', @author);
		$author = "<i>napisao/la: $author</i><br>" if $author;
		my %data = (
			'lid' => $review_id,
			'description' => "'".replacequotes($author.$ref_main->{'text'})."'"
		);
		$sql = "INSERT INTO xoops_myReviews_text (".join(', ', keys(%data)).") VALUES (".join(', ', values(%data)).")";
		$dst_dbh->do($sql) or print "$sql\n";
	};
	if ($@) {
		print "Error inserting xoops_myReviews_text: $sql : $@\n";
		next;
	}

	# review downloads
	eval {
		my %data = (
			'lid' => $review_id,
			'cid' => $review_cat,
			'title' => "'".replacequotes($ref_main->{'title'})."'",
			'url' => "''",
			'homepage' => "''",
			'logourl' => "''",
			'submitter' => $review_user,
			'status' => 1,	# approved
			'date' => $ref_main->{'timestamp'},
			'hits' => $ref_main->{'hits'} ? $ref_main->{'hits'} : 0,
			'rating' => $ref_main->{'score'} ? $ref_main->{'score'} : 0,
			'votes' => 0,
			'comments' => 0,
			'loveit' => 0,
			'helpfull' => 0,
			'unhelpfull' => 0,
			'recommendit' => 0
		);
		$sql = "INSERT INTO xoops_myReviews_downloads (".join(', ', keys(%data)).") VALUES (".join(', ', values(%data)).")";
		$dst_dbh->do($sql) or print "$sql\n";
	};
	print "Error inserting xoops_myReviews_downloads: $sql : $@\n" if $@;

	# review comments
	my $ncomments = 0;
	$src_sth = $src_dbh->prepare("SELECT *, UNIX_TIMESTAMP(date) AS timestamp FROM reviews_comments WHERE rid=".$ref_main->{'id'});
	$src_sth->execute;
	while (my $ref = $src_sth->fetchrow_hashref()) {
		eval {
			my %data = (
				'lid' => $review_id,
				'reviewuser' => $review_user,
				'review' => "'".replacequotes($ref->{'comments'})."'",
				'reviewhostname' => "'$review_hostname'",
				'reviewtimestamp' => $ref->{'timestamp'}
			);
			$sql = "INSERT INTO xoops_myReviews_reviews (".join(', ', keys(%data)).") VALUES (".join(', ', values(%data)).")";
			$dst_dbh->do($sql) or print "$sql\n";
		};
		print "Error inserting xoops_myReviews_reviews: $sql : $@\n" if $@;
		eval {
			my %data = (
				'lid' => $review_id,
				'ratinguser' => $review_comment_user,
				'rating' => "'".replacequotes($ref->{'score'})."'",
				'ratinghostname' => "'$review_hostname'",
				'ratingtimestamp' => $ref->{'timestamp'}
			);
			$sql = "INSERT INTO xoops_myReviews_votedata (".join(', ', keys(%data)).") VALUES (".join(', ', values(%data)).")";
			$dst_dbh->do($sql) or print "$sql\n";
		};
		print "Error inserting xoops_myReviews_votedata: $sql : $@\n" if $@;
		$ncomments++;
	}
	$src_sth->finish;
	$dst_dbh->do("UPDATE xoops_myReviews_downloads SET comments=$ncomments WHERE lid=$review_id");
}

# POLLS
print "polls...\n";
eval { $dst_dbh->do("DELETE FROM xoops_xoopspoll_option") };
print "Error deleting xoops_xoops_xoopspoll_option: $@\n" if $@;
eval { $dst_dbh->do("DELETE FROM xoops_xoopspoll_desc") };
print "Error deleting xoops_xoops_xoopspoll_desc: $@\n" if $@;

# last poll id
$sth = $dst_dbh->prepare("SELECT MAX(poll_id) AS last_id FROM xoops_xoopspoll_desc");
$sth->execute;
my $tmp = $sth->fetchrow_hashref();
my $poll_id = $tmp->{'last_id'};
$sth->finish;

# last comment id
$sth = $dst_dbh->prepare("SELECT MAX(com_id) AS last_id FROM xoops_xoopscomments");
$sth->execute;
my $tmp = $sth->fetchrow_hashref();
my $poll_comment_id = $tmp->{'last_id'};
$sth->finish;

# module id, for comments
$sth = $dst_dbh->prepare("SELECT mid FROM xoops_modules WHERE dirname='xoopspoll'");
$sth->execute;
my $tmp = $sth->fetchrow_hashref();
my $xoopspoll_id = $tmp->{'mid'};
$sth->finish;

my $poll_user = 0;
my $poll_comment_user = 0;
my $src_sth_main = $src_dbh->prepare("SELECT * FROM poll_desc ORDER BY timeStamp DESC");
$src_sth_main->execute;
while (my $ref_main = $src_sth_main->fetchrow_hashref()) {
	# next id
	$poll_id++;

	eval {
		my $question = $ref_main->{'pollTitle'};
		$question =~ s/\<\/?[^\>]+\>//g;
		my %data = (
			'poll_id' => $poll_id,
			'question' => "'".replacequotes($question)."'",
			'description' => "''",
			'user_id' => $poll_user,
			'start_time' => $ref_main->{'timeStamp'},
			'end_time' => 'NOW()',
			'votes' => $ref_main->{'voters'},
			'voters' => $ref_main->{'voters'},
			'multiple' => 0,
			'display' => 0,
			'weight' => 0,
			'mail_status' => 0
		);
		$sql = "INSERT INTO xoops_xoopspoll_desc (".join(', ', keys(%data)).") VALUES (".join(', ', values(%data)).")";
		$dst_dbh->do($sql) or print "$sql\n";
	};
	if ($@) {
		print "Error inserting xoops_xoopspoll_desc: $sql : $@\n";
		next;
	}

	# poll options
	my @poll_colors = qw(aqua.gif brown.gif green.gif pink.gif yellow.gif darkgreen.gif grey.gif purple.gif blue.gif gold.gif orange.gif red.gif);
	my $poll_color = 0;
	$src_sth = $src_dbh->prepare("SELECT * FROM poll_data WHERE pollID=".$ref_main->{'pollID'});
	$src_sth->execute;
	while (my $ref = $src_sth->fetchrow_hashref()) {
		next unless ($ref->{'optionText'});
		eval {
			my %data = (
				'poll_id' => $poll_id,
				'option_text' => "'".replacequotes($ref->{'optionText'})."'",
				'option_count' => $ref->{'optionCount'},
				'option_color' => "'".$poll_colors[$poll_color]."'"
			);
			$sql = "INSERT INTO xoops_xoopspoll_option (".join(', ', keys(%data)).") VALUES (".join(', ', values(%data)).")";
			$dst_dbh->do($sql) or print "$sql\n";
		};
		print "Error inserting xoops_xoopspoll_option: $sql : $@\n" if $@;
		$poll_color++;
		$poll_color %= scalar(@poll_colors);
	}
	$src_sth->finish;

	# poll comments
	$src_sth = $src_dbh->prepare("SELECT *, UNIX_TIMESTAMP(date) AS timestamp FROM pollcomments WHERE pollID=".$ref_main->{'pollID'});
	$src_sth->execute;
	while (my $ref = $src_sth->fetchrow_hashref()) {
		my $com_rootid = 'com_id';
		$poll_comment_id++;
		eval {
			my @author = ();
			push(@author, $ref->{'name'}) if $ref->{'name'};
			push(@author, '&lt;'.web_email($ref->{'email'}).'&gt;') if $ref->{'email'};
			my $author = join(' ', @author);
			$author = "<i>napisao/la: $author</i><br>" if $author;
			my %data = (
				'com_id' => $poll_comment_id,
				'com_pid' => 0,
				'com_rootid' => $poll_comment_id,
				'com_modid' => $xoopspoll_id,
				'com_itemid' => $poll_id,
				'com_icon' => "''",
				'com_created' => $ref->{'timestamp'},
				'com_modified' => $ref->{'timestamp'},
				'com_uid' => $poll_comment_user,
				'com_ip' => "'".$ref->{'host_name'}."'",
				'com_title' => "'".replacequotes($ref->{'subject'})."'",
				'com_text' => "'".$author.replacequotes($ref->{'comment'})."'",
				'com_sig' => "''",
				'com_status' => 2,
				'com_exparams' => "''",
				'dohtml' => 1,
				'dosmiley' => 1,
				'doxcode' => 1,
				'doimage' => 1,
				'dobr' => 0
			);
			$sql = "INSERT INTO xoops_xoopscomments (".join(', ', keys(%data)).") VALUES (".join(', ', values(%data)).")";
			$dst_dbh->do($sql) or print "$sql\n";
		};
		print "Error inserting xoops_xoopscomments: $sql : $@\n" if $@;
		$poll_color++;
		$poll_color %= scalar(@poll_colors);
	}
	$src_sth->finish;
}

$src_dbh->disconnect();
$dst_dbh->disconnect();

sub replacequotes {
	$toreplace = shift;
	$toreplace =~ s/'/\\'/g;
	$toreplace =~ s/[\r\n]+/ /gsm;
#	$toreplace =~ s/'/\\"/g;
	return ($toreplace);
}

sub web_email {
	my $email = shift;
	$email =~ s/\./ dot /g;
	$email =~ s/\@/ at /g;
	return $email;
}

exit $?;