equivalent plans in CBSubs

I'm trying to get things set up to migrate my users from AEC to CBSubs, but have run across a possible snag in the way CBSubs treats relationships between plans. In AEC, one can label plans as equivalent, which is what we've done with several plans. They all have the same benefits and user groups, but differ only in terms of length, eligibility and price.

If a subscriber to a plan which has a duration of one year chooses to switch to the three year plan, is there any way to make the expiration date of the new plan three years past that of the previous plan. I have all of the equivalent plans set up for "renewal extends expiration date (without gap or overlap)" but I'd like to be able to designate three different but equivalent plans to interact the same way, as they do in AEC.

I know that I can set the "upgrade" to prorate the remaining value of the old plan, but I would prefer to simply set the expiration date of the new plan to be three years (in the given example) after the expiry of the old plan. The prorating works for online users, but many members choose to send a check to the executive director, who enters their information manually. Is there a way to avoid making her also manually adjust the expiration date?

Possible?

CBSubs doesn't prorate plan duration. So, no there's not possible without some custom queries or solution. You can only prorate the remaining value of the subscription. So their upgrade subscription would be discounted instead of having its duration extended. Duration options for plans are planned at some point though, but I've no idea when.

You could force this behavior though by using CBSubs SQL Actions, query for the plan they upgraded from, calculate the remaining duration, then add it to their new subscriptions expiration date. That should give you exactly what you're wanting. The below is an example query of how to do this.


The above should add the remaining time (in seconds) of the subscription they upgraded from to their new subscription. The issue with update queries is they can't update and select from themselves at the same time. That causes an issue with the engine unsure what to do if the two conflict. The solution is a subquery in a subquery. This causes the table to be put into memory temporarily so you can get the previous subscriptions date difference so it can be added. This COULD be slow if you've a massive amount of subscription rows (I can't say for sure, I don't have a test case for that) though. Example of the results as follows.

Plan A (upgrading from)
Expiry Date: 2014-06-27 18:09:27 (when it was replaced)
Previous Expiry Date: 2015-06-27 18:08:45 (when it would've normally expired)
Date Difference: 31535958 (in seconds)

Plan B (upgrading to)
Expiry Date: 2015-06-27 18:09:27
New Expiry Date: 2016-06-26 18:08:45 (with 31535958 seconds added)

The below can be ran directly on phpmyadmin with SUBSCRIPTION_ID_HERE replaced to test the outcome of these calculations to ensure accuracy and to test different situations. Note if your table prefix is not jos_ when using the below to test you'll need to replace it with whatever your template prefix is.


As with any code suggestions. Backup your database and test thoroughly to ensure you get the desired behavior.

Thanks. This appears to work as desired, but I need to check one more parameter: Not all of the available plans are equivalent, so I also need to check that the previous plan was eligible for the cross-grade. Would adding: to the WHERE and x,y and z are the plan_id of eligible plans be the best way of checking that?

The replaces_plan column stores the plan id of the plan that was upgraded from. So if you're on Plan A and upgrade to Plan B then replaces_plan will reference Plan A. So I suppose that'll do what you're wanting, but best way to find out is try it.