<?php// phpBB -> ListMailPRO script// This script should be run daily before dailymail or more frequently as desired// Cron commandline: /usr/bin/wget -O /dev/null -T 0 http://example.com/mail/phpbb2lmp.php?pw=1234 1>/dev/null 2>/dev/nullset_time_limit(0);include('./config.php');include('./admin.php');// exit if ListMail admin.php is not loadedif(!function_exists('adminheader')) exit('please make sure ListMail includes are available');// CONFIG// phpBB database connection$phpbb_host = 'localhost';$phpbb_user = 'USERNAME';$phpbb_pass = 'PASSWORD';$phpbb_db = 'DATABASE';// list to add phpbb users to$list='4';// remove ListMail removed/bounced/unconfirmed from phpBB (set to false or '' if not)$remove_lmp=1;// remove phpBB users from listmail who are no longer in phpBB// note: add a MySQL index to the "phpbb_users" table "user_email" field for best results$remove_phpbb=1;// notify of removals and provide a phpBB restore URL (enter email or set to false or '' if not)$remove_notify='YOU@EXAMPLE.COM';// ListMail followup seq and delay$seq=1;$del=0;// phpBB database table prefix$phpbbprefix='phpbb_';// password for accessing this script (browse with ?pw=PASSWORD)$password = 'PASSWORD';// LIVE - write changes (set to false or '' if not)$live=1;// test email - always runs live even in demo mode$test_email='YOURTEST@EXAMPLE.COM';// END CONFIG, START SCRIPTif($_GET['pw']<>$password) exit('Access denied - please use the password');if(strstr($_SERVER['SERVER_PROTOCOL'],'HTTPS')) $baseurl='https'; else $baseurl='http';$baseurl.="://".$_SERVER['HTTP_HOST'].$_SERVER['SCRIPT_NAME'];echo "Config: baseurl=$baseurl list=$list remove_lmp=$remove_lmp remove_phpbb=$remove_phpbb remove_notify=$remove_notify phpbbprefix=$phpbbprefix live=$live<br>";if(!$live) echo "<b>DEMO MODE - Not executing MySQL queries or sending notifications EXCEPT for $test_email</b><br>"; else echo "<b>LIVE MODE - Executing MySQL queries and sending notifications!</b><br>";// connect to phpbb database$link2 = mysql_connect($phpbb_host,$phpbb_user,$phpbb_pass) or die(mysql_error());mysql_select_db($phpbb_db,$link2) or die(mysql_error());// START RESTOREif($restore){ if(!$_GET['datastr']) exit('No user data'); $values=explode('*!*',gzuncompress(base64_decode($_GET['datastr']))); foreach($values as $k=>$v) $values[$k]=addslashes($v); echo "Restoring user \"".$values[32]."\"<br>Reading datastr..."; echo "<pre>"; print_r($values); echo "</pre>"; $cmd = "insert into ".$phpbbprefix."users values('".implode("','",$values)."');"; echo "SQL=<b>$cmd</b><br>"; if($live || $values[32]==$test_email) mysql_query($cmd,$link2) or die(mysql_error()); if($_GET['ps']){ $ps=explode(':',gzuncompress(base64_decode($_GET['ps']))); echo "<br>Restoring posts...<br>"; foreach($ps as $id){ $cmd = 'update '.$phpbbprefix.'posts set poster_id = \''.$values[0].'\', post_username = \'\' where post_id = \''.$id.'\''; echo "SQL=<b>$cmd</b><br>"; if($live || $values[32]==$test_email) mysql_query($cmd,$link2) or die(mysql_error()); } } if($_GET['ts']){ $ts=explode(':',gzuncompress(base64_decode($_GET['ts']))); echo "<br>Restoring topics...<br>"; foreach($ts as $id){ $cmd = 'update '.$phpbbprefix.'topics set topic_poster = \''.$values[0].'\' where topic_id = \''.$id.'\''; echo "SQL=<b>$cmd</b><br>"; if($live || $values[32]==$test_email) mysql_query($cmd,$link2) or die(mysql_error()); } } exit('Done restoring user');}// END RESTORE// START PHPBB2LMP SCRIPT// check ListMail for removed and bounced users and remove from phpBB and LMPif($remove_lmp){ echo "<br>Checking ListMail for removed, bounced or unconfirmed users on list $list...<br>"; flush(); $count=0; $rows = mysql_query("select id,email,cnf from lm_users where list = '$list' and cnf <> '1';",$link) or die(mysql_error()); if(@mysql_num_rows($rows)>0){ // delete from phpbb while(list($id,$email,$cnf)=mysql_fetch_row($rows)){ $count++; if($cnf=='2') $found='removed'; elseif($cnf=='3') $found='bounced'; else $found='inactive'; echo "Found $found user \"$email\", deleting... "; $row = mysql_query("select * from ".$phpbbprefix."users where user_email like '".addslashes($email)."';",$link2); if(@mysql_num_rows($row)>0){ // build restore URL, first get all of the users' phpBB field values $rowarr = mysql_fetch_array($row,MYSQL_NUM); mysql_data_seek($row,0); $rowarr2 = mysql_fetch_array($row,MYSQL_ASSOC); $restore_url = $baseurl."?restore=1&pw=$password&datastr=".urlencode(base64_encode(gzcompress(implode('*!*',$rowarr),9))); // graceful phpBB deletion, taken from the phpBB source // get poster_id for this user $post_ids=array(); $cmd = 'select post_id from '.$phpbbprefix.'posts where poster_id = \''.$rowarr2['user_id'].'\''; $rows2 = mysql_query($cmd) or die(mysql_error()); echo "Updating ".@mysql_num_rows($rows2)." posts, "; if(@mysql_num_rows($rows2)>0){ while(list($post_id)=mysql_fetch_row($rows2)){ $post_ids[]=$post_id; $cmd = 'update '.$phpbbprefix.'posts set poster_id = \'-1\', post_username = \'' . str_replace("\\'", "''", addslashes($rowarr2['username'])) . '\' where post_id = \''.$post_id.'\''; // echo "SQL=<b>$cmd</b><br>"; if($live || $values[32]==$test_email) mysql_query($cmd,$link2) or die(mysql_error()); } } // get topic_poster for this user $topic_ids=array(); $cmd='select topic_id from '.$phpbbprefix.'topics where topic_poster = \''.$rowarr2['user_id'].'\''; $rows2 = mysql_query($cmd) or die(mysql_error()); echo "Updating ".@mysql_num_rows($rows2)." topics<br>"; if(@mysql_num_rows($rows2)>0){ while(list($topic_id)=mysql_fetch_row($rows2)){ $topic_ids[]=$topic_id; $cmd = 'update '.$phpbbprefix.'topics set topic_poster = \'-1\' where topic_id = \''.$topic_id.'\''; //echo "SQL=<b>$cmd</b><br>"; if($live || $values[32]==$test_email) mysql_query($cmd,$link2) or die(mysql_error()); } } // script does not currently support remove/restore of moderator groups OR user votes // add post and topic ids to restore URL if(count($post_ids)>0) $restore_url.='&ps='.urlencode(base64_encode(gzcompress(implode(':',$post_ids),9))); if(count($topic_ids)>0) $restore_url.='&ts='.urlencode(base64_encode(gzcompress(implode(':',$topic_ids),9))); $cmd = "delete from ".$phpbbprefix."users where user_email like '".addslashes($email)."';"; echo "SQL=<b>$cmd</b><br>"; // delete the user from phpBB if($live || $email == $test_email){ mysql_query($cmd,$link2) or die(mysql_error()); // send notification and forum restore link by email if($remove_notify){ mail($remove_notify,'User removed from LMP/phpBB: '.$rowarr[32],"User \"".$rowarr[32]."\" removed from phpBB because they were found $found in ListMail.\n\nForum stats:\n\nuser_active: ".$rowarr[1]."\nuser_lastvisit: ".nicedate($rowarr[6])." ago\nuser_posts: ".$rowarr[9]."\n\nClick the following link to restore the user:\n\n$restore_url","From: \"phpbb2lmp\" <noreply@".$_SERVER['HTTP_HOST'].">\nReturn-path: <noreply@".$_SERVER['HTTP_HOST'].">"); } } echo "<a href=$restore_url>Restore User</a><br>"; } else { echo "Not found in forum<br>"; } echo "removing from ListMail DB... "; $cmd = "delete from lm_users where id = '$id';"; echo "SQL=<b>$cmd</b><br>"; if($live || $email == $test_email) mysql_query($cmd,$link); } echo "$count users removed from phpBB<br>"; } else { echo "No inactive users to remove from phpBB<br>"; }}// check all active ListMail users and remove from ListMail if not found in phpBBif($remove_phpbb){ echo "<br>Checking ListMail list $list for active users who are no longer in phpBB<br>"; flush(); $rows = mysql_query("select id,email from lm_users where list = '$list' and cnf = '1';",$link) or die(mysql_error()); $count=0; if(@mysql_num_rows($rows)>0){ while(list($id,$email)=mysql_fetch_row($rows)){ list($test)=@mysql_fetch_row(mysql_query("select user_id from ".$phpbbprefix."users where user_email = '".addslashes($email)."';",$link2)); if(!$test){ $count++; echo "$email found in ListMail but not phpBB... removing<br>"; $cmd = "delete from lm_users where id = '$id';"; echo "SQL=<b>$cmd</b><br>"; if($live || $email == $test_email) mysql_query($cmd,$link); } } } echo "$count users removed from ListMail<br>";}// check all phpBB users, insert into ListMail if not there already$rows = mysql_query("select username,user_email from ".$phpbbprefix."users where 1;",$link2) or die(mysql_error());if(@mysql_num_rows($rows)==0) exit('no active users found in phpbb');echo "<br>".@mysql_num_rows($rows)." users found in phpBB. Checking to make sure they are in ListMail...<br>";$count=0;while(list($username,$user_email)=mysql_fetch_row($rows)){ if(!$user_email) continue; // check listmail status $row = mysql_query("select id from lm_users where list = '$list' and email = '".addslashes($user_email)."' and cnf = '1';",$link); list($added)=@mysql_fetch_row($row); if(!$added){ echo "\"$username\" <$user_email> - Not in ListMail... adding<br>"; $today = date("Y-m-d"); // create uid $uniq = ''; while(!$uniq){ $uniq_str = unique_id(7); if(@mysql_num_rows(mysql_query("select id from $utable where uid = '$uniq_str'",$link))==0) $uniq=1; } // null unsafe vars for($j=1;$j<=10;$j++) ${'user'.$j}=''; $lname=''; // insert into ListMail $cmd = "insert into lm_users values ('null','$uniq_str','$list','".addslashes($username)."','$lname','".addslashes($user_email)."','$user1','$user2','$user3','$user4','$user5','$user6','$user7','$user8','$user9','$user10','$seq','$del','1','$today','Unknown','From Forum','1','0')"; echo "SQL=<b>$cmd</b><br>"; if($live || $user_email==$test_email) mysql_query($cmd,$link) or die(mysql_error()); $count++; } else { // echo "\"$username\" <$user_email> - Already in ListMail<br>"; }}echo "$count users added to ListMail<br><br>Script done!";// switch back to the LMP database...mysql_select_db($sqldb,$link);// END SCRIPT, START FUNCTIONS// convert seconds into #y#m#dfunction nicedate($in){ $s = time()-$in; $out=''; $d=''; $h=''; $m=''; if($s>=86400){ $d = floor($s/86400); $s = $s - ($d * 86400); $out.=$d.'d'; } if($s>=3600){ $h = floor($s/3600); $s = $s - ($h * 3600); if($out) $out.=' '; $out.=$h.'h'; } if($s>=60 && !$d){ $m = floor($s/60); $s = $s - ($m * 60); if($out) $out.=' '; $out .= $m.'m'; } if($s>0 && !$h){ if($out) $out.=' '; $out .= $s.'s'; } return $out;}// END phpBB -> ListMailPRO script?>
