Using SQL Action To Force Expiry Dates

Hi,

Didn't realize I could access this category to post my inquiry (Just recently upgraded member status!). Rather than rehash my question, I've included the link below to a related thread I just replied to with another inquiry

Hoping I can hear back soon to keep my clients happy:

www.joomlapolis.com/forum/88-usage/133998-solved-set-specific-start-date-for-membership?limit=6&start=6#205531

Apologies in advance if this double post is a no-no.

Link is not valid. Please do not post a thread about another thread. Always, as a subscriber, post your issues directly to the support forums. If there is an original post of yours in the community forums then please re-post it here as a reply to this thread. I do not frequently monitor community forums so posting there as a subscriber has a high chance of me never seeing your post.

Sorry about both the bad link and inappropriate forum etiquette. I'd posted the link to reduce repetition and provide a history. As you suggested, I'll re-post my inquiry here.

I am once again facing the plan to force certain expiry dates based on the status of members:

I still need to worry about new memberships which was addressed using this code in the "Activation' box of SQL Actions:

Existing ACTIVE members already have the correct expiry date of 2013-90-30 when they first signed up using the same code, so when they renew the expiry date is already set going forward (1 year memberhips) so no SQL Action, I believe is longer necessary.

Here is where its getting tricky for me and hope you can assist. I have a number of EXPIRED members that will still need to renew for this year (2011-10-01 to 2012-09-30) before being permitted to renew for the new membership period (2012-10-01 to 2013-09-30).

What I was hoping was that there is some way to trigger an SQL Action with the EXPIRED status to force the renewal date to 2012-09-30 first. The theory being they that when they are bumped up to ACTIVE status and they go to renew for the new term they'll be grouped in like the others ACTIVE members.

As a side note. All these expired members have a "last renewal date" of 2011-10-01. My guess is that when they go to "reactivate" it should expire on 2012-09-30 normally BUT

I think the reason this has gotten tricky is because the above SQL action meant for NEW members is triggering the EXPIRED members expiry date also so when they reactivate the expiry date is 2013-09-30 instead of 2012-09-30 first.

From my understanding the code in SQL Action boxes trigger as follows:

Activation: For new members or when a deactivated member reactivated
Renewal: When a membership is renewed
Expiration: When a membership expires
Deactivaton: When a membership is unsubscribed

Hope this makes sense and there is a solution on the horizon.

Thanks.

Activation: For new members or when a deactivated member reactivated

It's when a user subscribes to a plan for the first time typically. For example it could be at registration or it could be an upgrade. It doesn't apply to renewals; at least it shouldn't.

Renewal: When a membership is renewed
Expiration: When a membership expires
Deactivaton: When a membership is unsubscribed

All correct.

If I understand correctly you only want the SQL applied the first time the user subscribes to the plan then never again, correct?

Yes. I figure once the start date has been set 1 year renewals thereafter will have the same expiry date for all members - For renewals, I don't believe any SQL Action intervention is required.

I essentially need the renewal date to be a specific start time irrespective of when the NEW members registers. I realize that I have to update the SQL action every year for example NEW member joining now would have an expiry date of 2013-09-30. (Essentially the 3 months prior to the start date of 2012-10-01 are free).

I still not sure how to address the EXPIRED members as those as fall under "reactivation"

I'm not quite sure you can even do that. It'd throw off the calculations of CBSubs. As your durations are 1 year, why not use "This calendar year"? With CBSubs 2.0.2 you can easily specify the start date for the calendar year by month and day. So for example I could have the year begin 25th of June. Aside from that I don't know what more to advise other then you may want to consider re-evaluating your plan duration setup to be functional with what durations CBSubs provides.

It's a completely different situation however if the Activation query is firing for Renewals, but I need you to explicitly confirm if this is happening or not. If not you'll need to make adjustments to your queries to fit your needs or use existing durations.