[mod] Merge duplicate users--with improved functionality

[flug]

I recently had a problem where I imported a whole bunch of users who previously had no Foreign Key, and in the import I included a Foreign Key.

The result was, over 1000 users whose previous PHPlist record had the email address changed to "Duplicate xxx@yyy.zzz" and who had a new record created with the new Foreign Key and the email address and other attribute other info from the import file.

(What I had hoped/assumed would happen is that when the PHPList detected there was no existing Foreign Key for that record it would go ahead & match by email instead. See my solution for that issue here.)

For a lot of the entries this situation was a bit annoying but fine. But many of my users had subscribed to various email lists beyond the one I subscribe them to via the import, had entered various attribute data I don't set via the import, had set an RSS Frequency (which I don't set via the import), etc.

Going to the "Manage Users/Reconcile Users" page I saw a function that looked helpful: Merge duplicate users.

However reading the documentation, the source code, & experimenting, I found that "Merge Duplicate Users" does three things--somewhat userful but both less and more than what I really needed:

* It moves all message/message sent info from the duplicate user to the main user
* It moves all bounce info from the duplicate user to the main user
* It deletes all list subscriptions for the duplicate users

What I needed to do:

* Move attribute data from the duplicate user to the main user (skipping this step, however, if the main user already had info for a particular attribute)
* Move all list subscriptions from the duplicate user to the main user
* Move the RSS Frequency from the duplicate user to the main user (skipping this step if the main user already had an RSS Frequency set)

Also the "delete list subscriptions for duplicate users" caused me a lot of headaches. The list subscription information was exactly the most valuable information that was lost when I imported the members with the new Foreign Key. When I ran "Merge duplicate users" it erased all those list subscriptions and I was lucky I had a very recent backup copy of the list subscription table to restore that data.

To take care of all of these problems, I re-tooled the "Merge duplicate users" function to
- move bounces
- move message info for users
- merge the list subscriptions
- merge attribute values (but only if that attribute is blank/NULL in the main entry)
- avoid deleting list subscriptions for the duplicate users

The necessary code for this mod to reconcileusers.php is in the following message.

[flug]

This mod adds new functionality to the "Merge Duplicate Users" function to make it more useful.

I have tested this mod with PHPlist 2.10.4 but it will almost certainly work with any 2.10.x or 2.11.1-2-3 version.

I have tested this reasonably thoroughly with my own PHPlist installation but running it DOES MAKE IRREVERSABLE CHANGES to your database so it is very wise to make a complete database backup before running this function (or any other function on the Reconcile Users page, for that matter).

To install this mod, first make a backup copy of the file "reconcileusers.php" in directory phplist/admin. If something goes wrong you can revert to this original copy of reconcileusers.php

If you want a complete copy of reconcileusers.php with all the mods made (PHPlist 2.10.2 but should work with 2.10.x and 2.11.1-2-3) download it here:

http://mobikefed.dreamhosters.com/reconcileusers.txt

To make the mod manually:

Open the file reconcileusers.php and make these changes:

1. To explain what this function does I added this code (making this change is optional but it does help to know exactly what clicking that button is going to do).

Find this code near line 500:

Code: Select all

<p><php>get("Merge Duplicate Users"));

And change it to this:

Code: Select all

<p><php>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!]";

2. Around line 66, find the function "mergeUser". This function should look something like this:

Code: Select all

function mergeUser($userid) {
  $duplicate = Sql_Fetch_Array_Query("select * from {$GLOBALS["tables"]["user"]} where id = $userid");
  printf ('<br>%s',$duplicate["email"]);
  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) {
    $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");
      $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"]));
      }
      $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"]));

      }

      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");
  }
}


Replace that entire function with the code found in http://mobikefed.dreamhosters.com/mergeuser-function.txt
(the function is just too long to include in the text of this post, sorry)

3. If you want to include the warning about "irreversable changes" you can also make this change:

Find this code around line 480 of reconcileusers.php:

Code: Select all

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

?>

Replace it with this code:

Code: Select all

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

print "<p> <b><i>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!</i></b></p>";
?>