Migration from AEC to Membership Pro

Maybe subscription history could be seen via invoice. If you could give me quick access to the data via database to understand the structure, that would be great

Tuan

Tuan...

If you are still interested, I am happy to show you around by skype.

We do use recurring payments and use different plans with different payment processors. We do not use Gold / Silver / Bronze plans, actually we have just one plan which gives access to the downloads.

Please let me know by E-Mail if you would like to have a tour

EDIT:
We have UTC/GMT -3 here... we have to find a time which suits the two.

Thanks so much for your help. For now, I would like to look at the database first. If I still have questions, I will ask for your help via Skype

Many thanks again !

Tuan

Tuan Pham Ngoc wrote: Maybe subscription history could be seen via invoice. If you could give me quick access to the data via database to understand the structure, that would be great

Tuan

Its been a little while since I rummaged in the drawers of the AEC tables but I can try and help you through some of this:

table: jos_acctexp_subscr
Contains all the subscriptions in AEC. As far as I know that is all i.e. current and previous.
data structure is:
id - a unique id for the subscription
userid - the joomla account
type - the payment processor
status - self explanatory
signup_date - self explanatory
lastpay_date - when the last payment was cleared. so either 0000-00-00 if never cleared, same as signup if cleared once or if recurring the last payment
cancel_date - when canceled - ie. for recurring payment that cancelled
eot_date - when the payment ends
eot_cause - what caused it (coded - sorry not sure of the codes)
plan - which subscription plan applies
recurring - is plan recurring
lifetime - is plan lifetime
params - see below
customparams - see below
primary - this is one of the AEC unique features - a plan is either primary (the main plan for a user) or secondary (a sub-plan)
expiration - when the plan expires (different from eot!)

params and customparams - this is a a JSON field of additional info. But sk0re did wierd things with his JSON and "encyrpted" them. There is a function is AEC to undo this...

The invoices table I have always thought was more about the financial payment detail - the subscription table is more likely what you want, although you may want to import over the invoices too and may need some of it for the payment notifications.

Shout if you need more.

Tuan Pham Ngoc wrote: 2. Anyone here uses recurring payment ? Could you please allow me have a quick look at the site database to see how subscription data for a recurring subscription organized? @x-bit: Could you please help ?

My recurring payment uses a home cooked payment gateway so it may not be perfect!
I think the answer lies in the params field in the subscr table and possibly the invoices table - it holds multiple payments against a single invoice normally for recurring - again in that BASE64 JSON params field thing...

Hello,

We are using AEC on Joomla 1.5.26 with Paypal and 2CO reoccurring payments and single payments, and we have plans like Bronze, Silver Gold and they are available 3, 6 and 12 month plans. If you still need to look at a DB we can try and provide some access to our test site. Please let me know.

Kind Regards
Alan

Params decoding - should do the trick.

So you aren't gonna like this!

jos_acctexp_subscr gives you a user_id if you search that user_id in jos_acctexp_invoices you should find one or more entries

The data structure of the acctexp_invoices table is:
id - as far as I can tell a unique id that isn't linked to the id in acctexp_subscr
userid - joomla userid - matches the id in jos_acctexp_subscr
invoicenumber - a unique invoice no. Standard set-up in AEC is that the same invoice no applies to all recurring payments (later versions of AEC may have allowed a new unique number??)
transaction_date - the LAST date an invoice was paid (see params for more info)
method - payment gateway
amount - transaction value
currency - currency not sure on coding £ Sterling is GBP
usage - the subscription plan id
coupons - not sure what appears (coupon code, id etc) as don't use them
params (see below)
secondary_ident - was intended for a second invoice number - maybe a more readable one!
subscr_id - this is the id column in acctexp_subscr table - its a one:many relationship
conditions - don't know!
invoice_number_format- there was a publically displayed invoice number

OK so that just leaves params. Its a Base64 encoded field, which actually contains a JSON encoded field!

I have extracted one of my entries and it looks like this (I've deleted some lines as they were likely duplicates due to a code error I had last year in my homebrew!
(see attachement - firewall doesn't like it!)


I think that is showing each previous payment (including the current one):
looking something like this in structure -
"timestamp";:"2016-01-08 11:37:02" (transaction date & time)
"amount"; "18.00"; (transaction value)
"currency"; "GBP"; (currency)
:"processor";:"gocardless"; (payment gateway)

Did someone say this migrator was going to be easy to write!

Thanks all for willing to sharing the data. Thanks @Calum for explain the database structure and event the code to get the param data (actually, the code you wrote need to change abit, the correct code is )

With the help of @x-bit (giving me access to real subscription data last night and answering some questions), today, I was able to finish the basic migration component which can be used for migrating the following data:

1. Categories (if it is used)

2. Plans

3. Subscriptions

Due to the the way the data is stored/organized in AEC component, I could not find a way to migrate subscription history of a subscription (AEC doesn't store subscription start date, subscription end date....each time a renewal happen - manually or recurring payment renewal), so for each subscription, we can only have a main record (like in AEC)

Also, for subscription plans, it seems AEC doesn't have a field to mark a plan a recurring subscription plan or an one time subscription plan, so even the parameters are migrated, after the migration process completed, you should check the plans settings and make sure it is all correct.

I will make a detailed instructions later (waiting for @x-bit to check the migrated data). If anyone want to try the migrator component, please get it here and try to migrate your data to see whether it is working as exected

Thanks all again

Regards,

Tuan

Great to hear Tuan. Can I just confirm this will also add payments to reoccurring subscriptions .i.e. From PayPal?

As I believe the membership pro doesn't allow a single plan to have reoccurring payments or singular at the same time.

Many thanks
Tony

Hi Tony

That would require another system plugin. Basically, at the moment, when a recurring payment renewal happens, PayPal will still send notification to AEC (not to membership pro as the subscription was subscribed via AEC, not via Membership Pro).

So we will need a payment plugin to route/change that notification to Membership Pro so that Membership Pro can extends the subscription record properly

If you use PayPal, you won't have to worry about it as I will develop that plugin and make it available soon (sometime next week, even this weekend). If you use 2Checkout for example, it is not possible in the near future as at the moment, 2Checkout payment plugin in Membership Pro doesn't support recurring payment yet (something I will work on in the next two weeks - code is ready, just need to config it together with a customer to confirm it working well as this option could not be tested with 2Checkout sandbox)

Regards,

Tuan