I need an SQL query that gives me all users that have recurring subscriptions that started more than a month ago and still have 2036 as the end date. Hope this makes sense - I want to find all members with recurring subscriptions that should have been disabled but weren't.
Here is the query: select * from amember_members where member_id in (select member_id from amember_payments where begin_date < '2013-01-05' and expire_date ='2036-12-31' and completed=1 and paysys_id='paypal_r')
That got me started, thanks. I modified it a little to be able to find those members: SELECT DISTINCT (m.email), m.login FROM amember_members AS m LEFT JOIN amember_payments AS p ON m.member_id = p.member_id WHERE expire_date='2036-12-31' AND begin_date < date_sub(now(), INTERVAL 1 MONTH) AND completed = 1 AND paysys_id='paypal_r'; Also note the date_sub routine which replaces having to change the date every time. Will I have to run this in the future again? Also, I take it these issues get fixed if I upgrade to 4.x?
No you don't need to run it in future again. aMember v4 import script will calculate correct expiration date for records which have expiration date set to 2036-12-31 So after import there should not be records with expiration date = 2036-12-31 All such records will have expiration date set to next rebill date.