get('Resending confirmation request to')." ".$userdata["email"]); if (!TEST) return sendMail($userdata["email"],$subject, $_REQUEST["prepend"].$subscribemessage,system_messageheaders($userdata["email"]),$envelope); } function fixEmail($email) { if (preg_match("#(.*)@.*hotmail.*#i",$email,$regs)) { $email = $regs[1].'@hotmail.com'; } if (preg_match("#(.*)@.*aol.*#i",$email,$regs)) { $email = $regs[1].'@aol.com'; } if (preg_match("#(.*)@.*yahoo.*#i",$email,$regs)) { $email = $regs[1].'@yahoo.com'; } # $email = str_replace(" ","",$email); $email = preg_replace("#,#",".",$email); $email = str_replace("\.\.","\.",$email); # $email = preg_replace("#[^\w]$#","",$email); # $email = preg_replace("#\.$#","",$email); $email = preg_replace("#\.cpm$#i","\.com",$email); $email = preg_replace("#\.couk$#i","\.co\.uk",$email); return $email; } # error_reporting(E_ALL); function mergeUser($userid) { //get the sql record of the for the userid with the "duplicate xxx@yyy.zzz" email address $duplicate = Sql_Fetch_Array_Query("select * from {$GLOBALS["tables"]["user"]} where id = $userid"); printf ('
%s',$duplicate["email"]); //now extract just the "xxx@yyy.zzz" type email address from the field if (preg_match("/^duplicate[^ ]* (.*)/",$duplicate["email"],$regs)) { print " -> ".$regs[1]; $email = $regs[1]; } elseif (preg_match("/^([^ ]+@[^ ]+) \(\d+\)/",$duplicate["email"],$regs)) { print " -> ".$regs[1]; $email = $regs[1]; } else { $email = ""; } if ($email) { //now find if there is any record that matches that 'stripped' email address $orig = Sql_Fetch_Row_Query(sprintf('select id from %s where email = "%s"',$GLOBALS["tables"]["user"],$email)); if ($orig[0]) { print " - ".$GLOBALS['I18N']->get("user found") . ", #" . $orig[0]; $history_entry="Merging duplicate email, information from " . $duplicate["email"] . " userid = " . $duplicate["id"] . " merged to " . $email . " userid = " . $orig[0]; //now move any usermessages from the duplicate to the main user $umreq = Sql_Query("select * from {$GLOBALS["tables"]["usermessage"]} where userid = ".$duplicate["id"]); while ($um = Sql_Fetch_Array($umreq)) { Sql_Query(sprintf('update %s set userid = %d, entered = "%s" where userid = %d and entered = "%s"',$GLOBALS["tables"]["usermessage"],$orig[0],$um["entered"],$duplicate["id"],$um["entered"])); } $history_entry .="\nUsermessages moved from " . $duplicate["id"] . " to " . $orig[0]; if (VERBOSE) echo "
Usermessages moved from " . $duplicate["id"] . " to " . $orig[0]; //now move any bounce messages from the duplicate to the main user $bncreq = Sql_Query("select * from {$GLOBALS["tables"]["user_message_bounce"]} where user = ".$duplicate["id"]); while ($bnc = Sql_Fetch_Array($bncreq)) { Sql_Query(sprintf('update %s set user = %d, time = "%s" where user = %d and time = "%s"',$GLOBALS["tables"]["user_message_bounce"],$orig[0],$bnc["time"],$duplicate["id"],$bnc["time"])); } $history_entry .="\n Bounce messages moved from " . $duplicate["id"] . " to " . $orig[0]; if (VERBOSE) echo "
Bounce messages moved from " . $duplicate["id"] . " to " . $orig[0]; //now copy any attributes from the duplicate to the main user; this happens only if that attribute doesn't already exist (or is blank or NULL) for the main user $query="select * from {$GLOBALS["tables"]["user_attribute"]} where userid = ".$duplicate["id"]; //if (VERBOSE) echo "
" . $query ; $attreg = Sql_Query($query); while ($att = Sql_Fetch_Array($attreg)) { //only do it for values that actually have something in them if ($att["value"] <> "" && $att["value"] != NULL ) { //updates attributeid/userid from orig with value from duplicate //if the value exists (but is blank or NULL) we update it; if it doesn't exist we create it $query = sprintf('select * from %s where attributeid = %d and userid = %d',$GLOBALS["tables"]["user_attribute"], $att["attributeid"], $orig[0]); //if (VERBOSE) echo $query . "
"; $attorigreg= Sql_Query($query); $attorig= Sql_Fetch_Array($attorigreg); if ( !Sql_Num_Rows($attorigreg) ) { $query=sprintf('insert into %s (attributeid, userid, value ) values ( %d, %d, "%s" ) ',$GLOBALS["tables"]["user_attribute"], $att["attributeid"], $orig[0], $att["value"] ); //if (VERBOSE) echo $query . "
"; Sql_Query($query); $history_entry .= "\n attribute " . $att["attributeid"] . " with value " . $att["value"] . " copied from " . $duplicate["id"] . " to " . $orig[0]; if (VERBOSE) echo "
Transferring attribute=" . $att["attributeid"] . " value=". $att["value"]; } else if ( $attorig ["value"] == "" || $attorig ["value"] == NULL ) { $query=sprintf('update %s set value = "%s" where attributeid = %d and userid = %d ',$GLOBALS["tables"]["user_attribute"], $att["value"], $att["attributeid"], $orig[0] ); //if (VERBOSE) echo $query . "
"; Sql_Query($query); $history_entry .= "\n attribute " . $att["attributeid"] . " with value " . $att["value"] . " copied from " . $duplicate["id"] . " to " . $orig[0]; if (VERBOSE) echo "
Transferring attribute=" . $att["attributeid"] . " value=". $att["value"]; } } //end if value non empty/non-null }//end while //now move all list subscriptions from the duplicate to the main users $query= "select * from {$GLOBALS["tables"]["listuser"]} where userid = ".$duplicate["id"]; //if (VERBOSE) echo $query . "
"; $subreg = Sql_Query($query); while ($sub = Sql_Fetch_Array($subreg)) { //if the list subscription already exists, just leave it. Otherwise, copy it over. $query=sprintf('select * from %s where listid = %d and userid = %d', $GLOBALS["tables"]["listuser"], $sub["listid"],$orig[0]); //if (VERBOSE) echo $query . "
"; $res=Sql_Query($query ); if ( !Sql_Num_Rows($res) ) { $query=sprintf('insert into %s (listid, userid, entered, modified ) values ( %d, %d, "%s", now() ) ',$GLOBALS["tables"]["listuser"], $sub["listid"], $orig[0], $sub["entered"]); //if (VERBOSE) echo $query . "
"; Sql_Query($query) ; $history_entry .="\nSubscription to list " . $sub["listid"] . " copied from " . $duplicate["id"] . " to " . $orig[0]; if (VERBOSE) echo "
Subscription to list " . $sub["listid"] . " copied from " . $duplicate["id"] . " to " . $orig[0]; } } //now move the rssfrequency over (if it doesn't exist in the main record already) $query="select rssfrequency from {$GLOBALS["tables"]["user"]} where id = ".$orig[0]; //if (VERBOSE) echo $query . "
"; $rssorigreg = Sql_Query($query); $rssorig = Sql_Fetch_Array($rssorigreg); $query="select rssfrequency from {$GLOBALS["tables"]["user"]} where id = ".$duplicate["id"]; //if (VERBOSE) echo $query . "
"; $rssreg = Sql_Query($query); $rss = Sql_Fetch_Array($rssreg); //if there is an rss frequency in the duplicate & the rss frequency doesn't exist //or is empty or is NULL in the main entry, then replace with the "Duplicate" value if ( ( $rss["rssfrequency"]<>"" && $rss["rssfrequency"] != NULL ) && ( !$rssorigreg || $rssorig["rssfrequency"] == "" || $rssorig["rssfrequency"] == NULL ) ) { $query=sprintf('update %s set rssfrequency = "%s" where id = %d ', $GLOBALS["tables"]["user"], $rss["rssfrequency"], $orig[0] ); //if (VERBOSE) echo $query . "
"; Sql_Query($query) ; $history_entry .="\n rssfrequency " .$rss["rssfrequency"] . " from user " . $duplicate["id"] . " copied to " . $orig[0]; if (VERBOSE) echo "
rssfrequency " .$rss["rssfrequency"] . " from user " . $duplicate["id"] . " copied to " . $orig[0]; } if (VERBOSE) echo "
"; //now add entry to user_user_history to indicate that this change has taken place addUserHistory($email,"Update by ".adminName($_SESSION["logindetails"]["id"]),$history_entry); //bhugh, 10/5/2007 //eliminate this step, which deletes all list subscriptions for duplicate users // the trouble is that this is destructive and non-reversable! //if the admin wants to just delete all duplicate users, that is easy to do . . . //in the meanwhile, we won't mess it up //Sql_Query("delete from {$GLOBALS["tables"]["listuser"]} where userid = ".$duplicate["id"]); } else { print " ".$GLOBALS['I18N']->get("no user found"); } flush(); } else { print "-> ".$GLOBALS['I18N']->get("unable to find original email"); } } function moveUser($userid) { global $tables; $newlist = $_GET["list"]; Sql_Query(sprintf('delete from %s where userid = %d',$tables["listuser"],$userid)); Sql_Query(sprintf('insert into %s (userid,listid,entered) values(%d,%d,now())',$tables["listuser"],$userid,$newlist)); } function addUniqID($userid) { Sql_query(sprintf('update %s set uniqid = "%s" where id = %d',$GLOBALS["tables"]["user"],getUniqID(),$userid)); } if (($require_login && !isSuperUser()) || !$require_login || isSuperUser()) { $access = accessLevel("reconcileusers"); switch ($access) { case "all": if ($_GET["option"]) { set_time_limit(600); switch ($_GET["option"]) { case "markallconfirmed": info( $GLOBALS['I18N']->get("Marking all users confirmed")); Sql_Query("update {$tables["user"]} set confirmed = 1"); $total =Sql_Affected_Rows(); print "$total ".$GLOBALS['I18N']->get('users apply')."
"; break; case "adduniqid": info( $GLOBALS['I18N']->get("Creating UniqID for all users who do not have one")); $req = Sql_Query("select * from {$tables["user"]} where uniqid is NULL or uniqid = \"\""); $total =Sql_Affected_Rows(); print "$total ".$GLOBALS['I18N']->get("users apply")."
"; $todo = "addUniqID"; break; case "markallhtml": info( $GLOBALS['I18N']->get("Marking all users to receive HTML")); Sql_Query("update {$tables["user"]} set htmlemail = 1"); $total =Sql_Affected_Rows(); print "$total ".$GLOBALS['I18N']->get("users apply")."
"; break; case "markalltext": info( $GLOBALS['I18N']->get("Marking all users to receive text")); Sql_Query("update {$tables["user"]} set htmlemail = 0"); $total =Sql_Affected_Rows(); print "$total ".$GLOBALS['I18N']->get('users apply')."
"; break; case "nolists": info( $GLOBALS['I18N']->get("Deleting users who are not on any list")); $req = Sql_Query(sprintf('select %s.id from %s left join %s on %s.id = %s.userid where userid is NULL', $tables["user"],$tables["user"],$tables["listuser"],$tables["user"],$tables["listuser"])); $total =Sql_Affected_Rows(); print "$total ".$GLOBALS['I18N']->get('users apply')."
"; $todo = "deleteUser"; break; case "nolistsnewlist": $list = sprintf('%d',$_GET["list"]); info( $GLOBALS['I18N']->get("Moving users who are not on any list to")." ".ListName($list)); $req = Sql_Query(sprintf('select %s.id from %s left join %s on %s.id = %s.userid where userid is NULL', $tables["user"],$tables["user"],$tables["listuser"],$tables["user"],$tables["listuser"])); $total =Sql_Affected_Rows(); print "$total ".$GLOBALS['I18N']->get('users apply')."
"; $todo = "moveUser"; break; case "bounces": info( $GLOBALS['I18N']->get("Deleting users with more than")." ".$_REQUEST["num"]." ".$GLOBALS['I18N']->get('bounces')); $req = Sql_Query(sprintf('select id from %s where bouncecount > %d', $tables["user"],$_REQUEST["num"] )); $total = Sql_Affected_Rows(); print "$total ".$GLOBALS['I18N']->get('users apply')."
"; $todo = "deleteUser"; break; case "resendconfirm": $fromval = $from->getDate("from"); $toval = $from->getDate("to"); Info($GLOBALS['I18N']->get("Resending request for confirmation to users who signed up after").' '. $fromval .' '.$GLOBALS['I18N']->get('and before'). ' '.$toval); $req = Sql_Query(sprintf('select id from %s where entered > "%s" and entered < "%s" and !confirmed', $tables["user"],$fromval,$toval )); $total = Sql_Affected_Rows(); print "$total ".$GLOBALS['I18N']->get('users apply')."
"; $todo = "resendConfirm"; break; case "deleteunconfirmed": $fromval = $from->getDate("from"); $toval = $from->getDate("to"); Info($GLOBALS['I18N']->get("Deleting unconfirmed users who signed up after"). ' '.$fromval .' '.$GLOBALS['I18N']->get('and before'). ' '.$toval); $req = Sql_Query(sprintf('select id from %s where entered > "%s" and entered < "%s" and !confirmed', $tables["user"],$fromval,$toval )); $total = Sql_Affected_Rows(); print "$total ".$GLOBALS['I18N']->get('users apply')."
"; $todo = "deleteuser"; break; case "mergeduplicates": Info($GLOBALS['I18N']->get('Trying to merge duplicates')); $query = sprintf('select id from %s where (email like "duplicate%%" or email like "%% (_)") and (foreignkey = "" or foreignkey is null)',$tables["user"]); //bhugh, test user example: //$query=sprintf('select id from %s where (email like "duplicate%%brent@brenthugh%%") and (foreignkey = "" or foreignkey is null)',$tables["user"]); //if (VERBOSE) echo $query; $req = Sql_Verbose_Query($query); $total = Sql_Affected_Rows($req); print "$total ".$GLOBALS['I18N']->get('users apply')."
"; $todo = "mergeUser"; case "invalidemail": case "fixinvalidemail": case "deleteinvalidemail": case "markinvalidunconfirmed": case "removestaleentries": break; default: # Info("Sorry, I don't know how to ".$_GET["option"]); # return; } $c = 1; ob_end_flush(); if ($todo && $req) while ($user = Sql_Fetch_Array($req)) { if ($c % 10 == 0) { print "
$c/$total
\n"; flush(); } set_time_limit(60); if (function_exists($todo)) { $todo($user["id"]); } else { Fatal_Error($GLOBALS['I18N']->get("Don't know how to")." ".$todo); return; } $c++; } if ($total) print "$total/$total
"; } if ($_GET["option"] == "invalidemail") { Info($GLOBALS['I18N']->get("Listing users with an invalid email")); flush(); $req = Sql_Query("select id,email from {$tables["user"]}"); $c=0; print '
'; while ($row = Sql_Fetch_Array($req)) { set_time_limit(60); if (!is_email($row["email"])) { $c++; if (is_array($tagged) && in_array($row["id"],array_keys($tagged))) { deleteUser($row["id"]); $deleted++; } else { $list .= sprintf('  ',$row["id"]).PageLink2("user&id=".$row["id"]."&returnpage=reconcileusers&returnoption=invalidemail","User ".$row["id"]). " [".$row["email"].']
'; } } } if ($deleted) print $deleted." ".$GLOBALS['I18N']->get('Users deleted')."
"; print $c." ".$GLOBALS['I18N']->get('Users apply')."
$list\n"; if ($c) print '
'; } elseif ($_GET["option"] == "fixinvalidemail") { Info($GLOBALS['I18N']->get("Trying to fix users with an invalid email")); flush(); $req = Sql_Query("select id,email from {$tables["user"]}"); $c=0; while ($row = Sql_Fetch_Array($req)) { set_time_limit(60); # if (checkMemoryAvail()) if (!is_email($row["email"])) { $c++; $fixemail = fixEmail($row["email"]); if (is_email($fixemail)) { Sql_Query(sprintf('update %s set email = "%s" where id = %d',$tables["user"],$fixemail,$row["id"]),0); $list .= PageLink2("user&id=".$row["id"]."&returnpage=reconcileusers&returnoption=fixinvalidemail",$GLOBALS['I18N']->get('User')." ".$row["id"]). " [".$row["email"].'] => fixed to '. $fixemail.'
'; $fixed++; } else { $notfixed++; $list .= PageLink2("user&id=".$row["id"]."&returnpage=reconcileusers&returnoption=fixinvalidemail",$GLOBALS['I18N']->get('User')." ".$row["id"]). " [".$row["email"].']
'; } } } print $fixed." ".$GLOBALS['I18N']->get('Users fixed')."
".$notfixed." ".$GLOBALS['I18N']->get("Users could not be fixed")."
".$list."\n"; } elseif ($_GET["option"] == "deleteinvalidemail") { Info($GLOBALS['I18N']->get("Deleting users with an invalid email")); flush(); $req = Sql_Query("select id,email from {$tables["user"]}"); $c=0; while ($row = Sql_Fetch_Array($req)) { set_time_limit(60); if (!is_email($row["email"])) { $c++; deleteUser($row["id"]); } } print $c." ".$GLOBALS['I18N']->get("Users deleted")."
\n"; } elseif ($_GET["option"] == "markinvalidunconfirmed") { Info($GLOBALS['I18N']->get("Marking users with an invalid email as unconfirmed")); flush(); $req = Sql_Query("select id,email from {$tables["user"]}"); $c=0; while ($row = Sql_Fetch_Array($req)) { set_time_limit(60); if (!is_email($row["email"])) { $c++; Sql_Query("update {$tables["user"]} set confirmed = 0 where id = {$row["id"]}"); } } print $c." ".$GLOBALS['I18N']->get('Users updated')."
\n"; } elseif ($_GET["option"] == "removestaleentries") { Info($GLOBALS['I18N']->get("Cleaning some user tables of invalid entries")); # some cleaning up of data: $req = Sql_Verbose_Query("select {$tables["usermessage"]}.userid from {$tables["usermessage"]} left join {$tables["user"]} on {$tables["usermessage"]}.userid = {$tables["user"]}.id where {$tables["user"]}.id IS NULL group by {$tables["usermessage"]}.userid"); print Sql_Affected_Rows() . " ".$GLOBALS['I18N']->get('entries apply')."
"; while ($row = Sql_Fetch_Row($req)) { Sql_Query("delete from {$tables["usermessage"]} where userid = $row[0]"); } $req = Sql_Verbose_Query("select {$tables["user_attribute"]}.userid from {$tables["user_attribute"]} left join {$tables["user"]} on {$tables["user_attribute"]}.userid = {$tables["user"]}.id where {$tables["user"]}.id IS NULL group by {$tables["user_attribute"]}.userid"); print Sql_Affected_Rows() . " ".$GLOBALS['I18N']->get('entries apply')."
"; while ($row = Sql_Fetch_Row($req)) { Sql_Query("delete from {$tables["user_attribute"]} where userid = $row[0]"); } $req = Sql_Verbose_Query("select {$tables["listuser"]}.userid from {$tables["listuser"]} left join {$tables["user"]} on {$tables["listuser"]}.userid = {$tables["user"]}.id where {$tables["user"]}.id IS NULL group by {$tables["listuser"]}.userid"); print Sql_Affected_Rows() . " ".$GLOBALS['I18N']->get('entries apply')."
"; while ($row = Sql_Fetch_Row($req)) { Sql_Query("delete from {$tables["listuser"]} where userid = $row[0]"); } $req = Sql_Verbose_Query("select {$tables["usermessage"]}.userid from {$tables["usermessage"]} left join {$tables["user"]} on {$tables["usermessage"]}.userid = {$tables["user"]}.id where {$tables["user"]}.id IS NULL group by {$tables["usermessage"]}.userid"); print Sql_Affected_Rows() . " ".$GLOBALS['I18N']->get('entries apply')."
"; while ($row = Sql_Fetch_Row($req)) { Sql_Query("delete from {$tables["usermessage"]} where userid = $row[0]"); } $req = Sql_Verbose_Query("select {$tables["user_message_bounce"]}.user from {$tables["user_message_bounce"]} left join {$tables["user"]} on {$tables["user_message_bounce"]}.user = {$tables["user"]}.id where {$tables["user"]}.id IS NULL group by {$tables["user_message_bounce"]}.user"); print Sql_Affected_Rows() . " ".$GLOBALS['I18N']->get('entries apply')."
"; while ($row = Sql_Fetch_Row($req)) { Sql_Query("delete from {$tables["user_message_bounce"]} where user = $row[0]"); } } $table_list = $tables["user"].$findtables; if ($find) { $listquery = "select {$tables["user"]}.id,$findfield,{$tables["user"]}.confirmed from ".$table_list." where $findbyselect"; $count = Sql_query("SELECT count(*) FROM ".$table_list ." where $findbyselect"); $unconfirmedcount = Sql_query("SELECT count(*) FROM ".$table_list ." where !confirmed && $findbyselect"); if ($_GET["unconfirmed"]) $listquery .= ' and !confirmed'; } else { $listquery = "select {$tables["user"]}.id,$findfield,{$tables["user"]}.confirmed from ".$table_list; $count = Sql_query("SELECT count(*) FROM ".$table_list); $unconfirmedcount = Sql_query("SELECT count(*) FROM ".$table_list." where !confirmed"); } $delete_message = ("
".$GLOBALS['I18N']->get("Delete will delete user and all listmemberships")."
"); break; case "none": default: $table_list = $tables["user"]; $subselect = " where id = 0";break; } } if (isset($_GET["delete"])) { $delete = sprintf('%d',$_GET["delete"]); # delete the index in delete print "deleting $delete ..\n"; deleteUser($delete); print "... ".$GLOBALS['I18N']->get('Done')."


\n"; Redirect("users&start=$start"); } $totalres = Sql_fetch_Row($unconfirmedcount); $totalunconfirmed = $totalres[0]; $totalres = Sql_fetch_Row($count); $total = $totalres[0]; print "

".$total." ".$GLOBALS['I18N']->get('Users').""; print $find ? " ".$GLOBALS['I18N']->get("found"): " ".$GLOBALS['I18N']->get("in the database"); print "

"; print "

All functions on this page make irreversable changes to the PHPlist database. Please make a complete backup of your database before running any of them!

"; ?>

get("Delete all users who are not subscribed to any list"))?>

get("Find users who have an invalid email"))?>

get("Make sure that all users have a UniqID"))?>

get("Mark all users with an invalid email as unconfirmed"))?>

get("Delete users who have an invalid email"))?>

get("Mark all users to receive HTML"))?>

get("Mark all users to receive text"))?>

get("Mark all users confirmed"))?>

get('To try to (automatically)')?> get("Fix emails for users who have an invalid email"))?>

get("Remove Stale entries from the database"))?>

get("Merge Duplicate Users")); echo " - merges info from duplicate email addresses (like 'Duplicate xxx@yyy.zzz') to the corresponding main user (xxx@yyy.zzz). Moves messages, bounces, list subscriptions, rssfrequency, and attributes (only if blank in main user; won't over-write). Useful if you've imported lists of users but then lost specific info like attributes or list subscriptions that users have entered directly into PHPList. [Warning! Irreversible!]"; ?>


get('To move all users who are not subscribed to any list to')?>


get('To delete all users with more than')?> get('bounces')?>

get('Note: this will use the total count of bounces on a user, not consecutive bounces')?>

get('To resend the request for confirmation to users who signed up and have not confirmed their subscription')?>
get('Date they signed up after')?>:showInput("","",$fromval);?>
get('Date they signed up before')?>:showInput("","",$toval);?>
get('Text to prepend to email')?>:

get('To delete users who signed up and have not confirmed their subscription')?>
get('Date they signed up after')?>:showInput("","",$fromval);?>
get('Date they signed up before')?>:showInput("","",$toval);?>