Author Topic: merging membership records (Read 4460 times)

mclyde · May 12, 2011, 08:47:23 am

It looks like under civi 3.4.1 with membership upsell, that the user will have just one displayed membership record per organization, that displays original join date, last level and end date.

We are upgrading from 2.2.9 and had some custom code for handling membership upsell, but the result is that every memberhip purchase lead to a new membership record but with start/end dates adjusted so that there was a continuous membership history (gaps would be exposed, if there were lapses).
Each members may have a list of expired memberships, a current membership, and a future membership (as they may pay in advance accidently) of different types.

With 3.4.1 if there are expired memberships and/or current only, the most recent record is updated correctly, which is OK.

But if there is a current and a future membership (both say one year) and I renew, I end up with 2 concurrent one year memberships, i.e. find the most recent membership (the future one) and update start/end date based on the current date and membership type -- hence another current membership for the same time period. (Argh) (I am not sure if this is a problem just for us because of how we handled upsell in the past or will be a more common problem)

Ideally, if one is going to have just one continuous membership record, the renew code would pass through the list of memberships of that type for a contact and combine them before doing the update. This would eliminate the expired membership list and any future memberships. Hopefully there will be a way to view the membership record log in case of member questions?

Dave Greenberg · May 12, 2011, 09:34:47 am

Merlise - I suspect this issue is specific to your current DB state since AFAIK the non-customized version of the code doesn't create "future" membership. Rather any "early" renewals are tacked onto existing current membership end-date.

I'll ask the person who designed / implemented the upsell modifications to take a look at this and see if he can suggest a solution for your situation. It might make sense to add logic to merge current and "future" memberships of the same type??? For expired, we have always kept those separate (to clearly illustrate a gap in the membership history) - and I think it probably makes sense to retain that rule.

mclyde · May 12, 2011, 11:54:19 am

Thanks Dave -- I inherited a rather messy DB, so it was a struggle to get it up todate from 2.2.9 and hope there aren't too many things that will bite me that are artifacts of the old custom code!

The following occur without upsell (same membership type)

In 3.4.1 on my test site:
If I taks an expired membership and renew it, the expired membership record is not being retained, but rather the end data is extended and status is updated to current (this is with or without upsell). So gaps in membership are being lost from the UI except by examing the contribution records (I get teh same on the demo site)

I checked out Membership Status rules (we did not implement a new member status, but did have a rule for future status) . status Future has a start date but no end date (the same problem with Lifetime memberships with "upsell", In this case, even though an end data is being entered it looks like it is being ignored.

I modified the status rule for future to leave end Event not selected, but put End Event Adjustment of 1 Now the renewal function updates the future membership by extending the end date by one year if I buy another 1 year membership (same type). SO that is better.

But if I decide to upgrade to a three year membership, the membership type is not updated, and the end date is not correct, and the start date is reset to the beginning of this year so status is current. (As you might tell, I am not quite sure of how the adjustments with future reulst are supposed to work :-)

These are all based on new memberships creted through the "back office" that would lead to 2 membership records

As for behaviour, it seems that the default behaviour is to keep one record per organization, so it would make sense to merge expired with current + future.

With the upsell option, looks like we can disable future status going forward, if we can "correct" the 15 out of 3400+ cases that this affects. (if necessary a manual fix is fine, if we can retain the contribution data linked to teh membership)

JoeMurray · May 12, 2011, 01:04:38 pm

I put this together before you posted your most recent reply indicating there were only 15 records with future dated memberships. Manually would have been better with that small number, but here is an automated approach that should work more easily.

I'm assuming that you have access via phpMyAdmin or the command line to your CiviCRM database so that you can execute SQL statements. Your MySQL account will also need create table privileges (since I use a temp table to get around MySQL limitations on updating a table used in a sub-select query).

Here is a suggestion for how to update your database directly. Please make a backup of your database first, since whether this works depends on some assumptions about values in various fields in your database that may not be true.

I'm going to provide queries that merge the future memberships (start_date today or later) into the most recent current one (latest end_date for a membership that has a status of new, current or grace and which started before today) for contacts that have both.

Create a temporary table indicating the end_date for the most recent current membership for each contact's memberships.
CREATE TABLE a_tmp_memb
SELECT id, m.contact_id FROM civicrm_membership m inner join (
select contact_id, max(m3.end_date) as end_date from civicrm_membership m3 where m3.status_id in (1,2,3) and m3.start_date<curdate() group by m3.contact_id) AS t on m.contact_id=t.contact_id and m.end_date=t.end_date;

Next, shift payments associated with future memberships so they relate to the most recent current one if a contact has both.

UPDATE civicrm_membership m1 inner join civicrm_membership m2 on m1.contact_id=m2.contact_id and m2.start_date>=curdate() and m1.end_date = (
select max(m3.end_date) from civicrm_membership m3 where m3.status_id in (1,2,3) and m3.contact_id=m1.contact_id and m3.start_date<curdate() and group by contact_id) inner join civicrm_membership_payment mp on mp.membership_id=m2.id SET mp.membership_id=m1.id;

Optionally, change the membership_type of the most recent current membership to that of the future membership.

UPDATE a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership f on m.contact_id=f.contact_id and f.start_date>=curdate()
SET m.membership_type_id = f.membership_type_id;

Next, extend the most recent current membership by the duration of the future membership.

UPDATE a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership f on m.contact_id=f.contact_id and f.start_date>=curdate()
SET m.end_date = DATE_ADD( m.end_date, INTERVAL DATEDIFF(f.end_date, f.start_date) DAY);

Next, delete the future memberships:
DELETE f.* FROM
a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership f on m.contact_id=f.contact_id and f.start_date>=curdate();

And finally,
DROP TABLE a_tmp_memb;

You'll probably need to tweak these queries a bit but this should be a very good start for you.

Best wishes,
Joe

mclyde · May 12, 2011, 01:32:41 pm

Thanks!!!

I'll work with our sys admin on the mysql part :-), but I suppose the same logic could be used to merge our expired records to the most recent record so that we end up with each contact having one membership record so that the db looks as expected going forward.

JoeMurray · May 13, 2011, 06:48:34 am

If you could post back here on how it works and any changes your tech folks had to make that will help others who want to update their memberships. In particular, if you do modify these queries to amalgamate the historical memberships into the current one (which should be fairly straightforward), posting your code here would certainly help.

I've noticed an oversight in what I posted: you need to point all of the activities associated with the membership to be deleted (in my code that is the future one, in yours it would be the older, expired ones) to the membership that it is being merged into. You should do this just before or after updating the membership contributions so they point to the membership that will remain.

UPDATE civicrm_membership m1 inner join civicrm_membership m2 on m1.contact_id=m2.contact_id and m2.start_date>=curdate() and m1.end_date = (select max(m3.end_date) from civicrm_membership m3 where m3.status_id in (1,2,3) and m3.contact_id=m1.contact_id and m3.start_date<curdate() group by contact_id) inner join civicrm_activity a on a.source_record_id=m2.id SET a.source_record_id=m1.id;

Also, there is an extraneous 'and' before the group by in the query updating membership contributions. Here's a revised version:
UPDATE civicrm_membership m1 inner join civicrm_membership m2 on m1.contact_id=m2.contact_id and m2.start_date>=curdate() and m1.end_date = (
select max(m3.end_date) from civicrm_membership m3 where m3.status_id in (1,2,3) and m3.contact_id=m1.contact_id and m3.start_date<curdate() group by contact_id) inner join civicrm_membership_payment mp on mp.membership_id=m2.id SET mp.membership_id=m1.id;

mclyde · June 01, 2011, 01:03:20 pm

Joe,

I tried your queries using pma on our test site. Afterwards, there are no future records (yeah) , but if I try to click on any of those mergee membership records in the membership tab, I just get a spinning "wheel" icon next to the membership :-(

will try to go back to the database to check what's up...
m

Code: [Select]

Create a temporary table indicating the end_date for the most recent current membership for each contact's memberships.

CREATE TABLE a_tmp_memb
SELECT id, m.contact_id FROM civicrm_membership m inner join (
select contact_id, max(m3.end_date) as end_date from civicrm_membership m3 where m3.status_id in (1,2,3) and m3.start_date<curdate() group by m3.contact_id) AS t on m.contact_id=t.contact_id and m.end_date=t.end_date;

you need to point all of the activities associated with the membership to be deleted (in my code that is the future one, in yours it would be the older, expired ones) to the membership that it is being merged into. You should do this just before or after updating the membership contributions so they point to the membership that will remain.

UPDATE civicrm_membership m1 inner join civicrm_membership m2 on m1.contact_id=m2.contact_id and m2.start_date>=curdate() and m1.end_date = (select max(m3.end_date) from civicrm_membership m3 where m3.status_id in (1,2,3) and m3.contact_id=m1.contact_id and m3.start_date<curdate()  group by contact_id) inner join civicrm_activity a on a.source_record_id=m2.id SET a.source_record_id=m1.id;

Next, shift payments associated with future memberships so they relate to the most recent current one if a contact has both.

UPDATE civicrm_membership m1 inner join civicrm_membership m2 on m1.contact_id=m2.contact_id and m2.start_date>=curdate() and m1.end_date = (
select max(m3.end_date) from civicrm_membership m3 where m3.status_id
in (1,2,3) and m3.contact_id=m1.contact_id and m3.start_date<curdate()
group by contact_id) inner join civicrm_membership_payment mp on
mp.membership_id=m2.id SET mp.membership_id=m1.id;


Optionally, change the membership_type of the most recent current membership to that of the future membership.

UPDATE a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership f on m.contact_id=f.contact_id and f.start_date>=curdate()
SET m.membership_type_id = f.membership_type_id;

Next, extend the most recent current membership by the duration of the future membership.

UPDATE a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership f on m.contact_id=f.contact_id and f.start_date>=curdate()
SET m.end_date = DATE_ADD( m.end_date, INTERVAL DATEDIFF(f.end_date, f.start_date) DAY);

Next, delete the future memberships:
DELETE f.* FROM
 a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership f on m.contact_id=f.contact_id and f.start_date>=curdate();

And finally,
DROP TABLE a_tmp_memb;

JoeMurray · June 01, 2011, 06:34:31 pm

I see in one of your notes to Dave Greenberg that this database has a long history and a bit of messiness and that it already had some oddness in some of its membership data, possibly due to custom code.
Dharmatech put together a tool that was useful for identifying database integrity issues in CiviCRM db's some time ago. I don't know if it has been kept up to date since 2009 when it was posted, but it's still available at http://dharmatech.org/tools/CiviCRM_DB_Tools/tutorial_test.pkg.html. jason (at) dharmatech (dot) org might be able to provide more info.

mclyde · June 01, 2011, 07:50:40 pm

I upgraded the schema and then used the Dharmatec DB integrity tool to clean up the database prior to upgrading from 2.2.9 to 3.3.6 (it required several passes and some other checking of records, but I had no problems with upgrading eventually to 3.3.6 and then the upgrade to 3.4.1 went smoothly) The custom code basically looked at the most recent membership and used that end date to create the start date of teh renewal in case the memership type changed (an early version of upsell), but rather than updating the one membership record, each "renewal" lead to a new membership record being added -- that does not seem to be so different than the pre 3.4.1 renewal if one switched membership types.

The tools have not been updated to the 3.3 schema (I could not run them post the 3.3.6 upgrade) so not sure that will help at this point...

will restore the db and look more carefully at the sql code tomorrow ...

m

mclyde · June 02, 2011, 08:34:12 am

I went back and restored the DB to the backup after the upgrade and noticed that the payment processor for membership contributions was not installed on the test site. This prevents existing membership records from being loaded (but not in all cases) so I am guessing that this was the problem! Once the payment processor was added everything worked fine. No more future memberships! (except my test case where I have only a future membership)

Any suggestions for expired memberships as we may have multiple expired memberships (as well as pending) per contact?

Thanks!
Merlise

Here is the exact code:

I'm going to provide queries that merge the future memberships
(start_date today or later) into the most recent current one (latest
end_date for a membership that has a status of new, current or grace
and which started before today) for contacts that have both.

Create a temporary table indicating the end_date for the most recent current membership for each contact's memberships.

CREATE TABLE a_tmp_memb
SELECT id, m.contact_id FROM civicrm_membership m inner join (
select contact_id, max(m3.end_date) as end_date from civicrm_membership m3 where m3.status_id in (1,2,3) and m3.start_date<curdate() group by m3.contact_id) AS t on m.contact_id=t.contact_id and m.end_date=t.end_date;

Next, shift payments associated with future memberships so they relate to the most recent current one if a contact has both.

UPDATE civicrm_membership m1 inner join civicrm_membership m2 on m1.contact_id=m2.contact_id and m2.start_date>=curdate() and m1.end_date = (
select max(m3.end_date) from civicrm_membership m3 where m3.status_id
in (1,2,3) and m3.contact_id=m1.contact_id and m3.start_date<curdate()
group by contact_id) inner join civicrm_membership_payment mp on
mp.membership_id=m2.id SET mp.membership_id=m1.id;

you need to point all of the activities associated with the membership to be deleted (in my code that is the future one, in yours it would be the older, expired ones) to the membership that it is being merged into. You should do this just before or after updating the membership contributions so they point to the membership that will remain.

UPDATE civicrm_membership m1 inner join civicrm_membership m2 on m1.contact_id=m2.contact_id and m2.start_date>=curdate() and m1.end_date = (select max(m3.end_date) from civicrm_membership m3 where m3.status_id in (1,2,3) and m3.contact_id=m1.contact_id and m3.start_date<curdate() group by contact_id) inner join civicrm_activity a on a.source_record_id=m2.id SET a.source_record_id=m1.id;

Optionally, change the membership_type of the most recent current membership to that of the future membership.

UPDATE a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership f on m.contact_id=f.contact_id and f.start_date>=curdate()
SET m.membership_type_id = f.membership_type_id;

Next, extend the most recent current membership by the duration of the future membership.

UPDATE a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership f on m.contact_id=f.contact_id and f.start_date>=curdate()
SET m.end_date = DATE_ADD( m.end_date, INTERVAL DATEDIFF(f.end_date, f.start_date) DAY);

Next, delete the future memberships:
DELETE f.* FROM
a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership f on m.contact_id=f.contact_id and f.start_date>=curdate();

And finally,
DROP TABLE a_tmp_memb;

mclyde · June 03, 2011, 08:40:24 am

Joe (or anyone with sql expertise)

I tried to modify your code to merge old records with the most recent current membership. This seems to work except for cases except where we have members with no current membership but more than one expired membership (around 50) or 13 cases were we have two current memberships (these are almost all cases of back office entries where the person joined through another org and the membership was entered manually but they also signed up online or in a couple of cases were the result of merging two accounts)

Can you suggest how the code below should be modified to combine handle those cases so that we have one membership record per organization per contact?

Thanks!
Merlise
Merge Expired With Current Memberships:

merge the expired memberships
(end_date today or earlier) into the most recent current one (latest
start_date for a membership that has a status of new, current or grace
and which started before today) for contacts that have both.

Create a temporary table indicating the end_date for the most recent membership for each contact's memberships.

CREATE TABLE a_tmp_memb
SELECT id, m.contact_id FROM civicrm_membership m inner join (
select contact_id, max(m3.start_date) as start_date from civicrm_membership m3 where m3.status_id in (1,2,3) group by m3.contact_id) AS t on m.contact_id=t.contact_id and m.start_date=t.start_date;

Next, shift payments associated with expired memberships so they relate to the most recent current one if a contact has both.

UPDATE civicrm_membership m1 inner join civicrm_membership m2 on m1.contact_id=m2.contact_id and m2.end_date<=curdate() and m1.start_date = (
select max(m3.start_date) from civicrm_membership m3 where m3.status_id
in (1,2,3) and m3.contact_id=m1.contact_id and m3.start_date<curdate()
group by contact_id) inner join civicrm_membership_payment mp on
mp.membership_id=m2.id SET mp.membership_id=m1.id;

point all of the activities associated with the membership to be deleted to the membership that it is being merged into.

UPDATE civicrm_membership m1 inner join civicrm_membership m2 on m1.contact_id=m2.contact_id and m2.end_date<=curdate() and m1.start_date = (select max(m3.start_date) from civicrm_membership m3 where m3.status_id in (1,2,3) and m3.contact_id=m1.contact_id and m3.start_date<curdate() group by contact_id) inner join civicrm_activity a on a.source_record_id=m2.id SET a.source_record_id=m1.id;

Next, update the start date of the current membership so that we have a continuous record

UPDATE a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership e on m.contact_id=e.contact_id and e.end_date<=curdate()
SET m.start_date = e.start_date;

update join date to reflect the oldest join date
UPDATE a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership e on m.contact_id=e.contact_id and e.end_date<=curdate()
SET m.join_date = e.join_date;

Next, delete the expired memberships: status_id = 4
DELETE e.* FROM
a_tmp_memb a inner join civicrm_membership m on a.id=m.id inner join civicrm_membership e on m.contact_id=e.contact_id and e.status_id=4;

And finally,
DROP TABLE a_tmp_memb;

CiviCRM Community Forums (archive)

News:

Author Topic: merging membership records (Read 4460 times)

mclyde

merging membership records

Dave Greenberg

Re: merging membership records

mclyde

Re: merging membership records

JoeMurray

Re: merging membership records

mclyde

Re: merging membership records

JoeMurray

Re: merging membership records

mclyde

Re: merging membership records

JoeMurray

Re: merging membership records

mclyde

Re: merging membership records

mclyde

Re: merging membership records

mclyde

Re: merging membership records - need more help!