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 '';
} 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 "
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('Note: this will use the total count of bounces on a user, not consecutive bounces')?>