equivalent plans in CBSubs
- dhphllps
- OFFLINE
-
Senior Member
- Posts: 63
- Thanks: 5
- Karma: 1
9 years 10 months ago #246355
by dhphllps
equivalent plans in CBSubs was created by dhphllps
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?
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?
Please Log in to join the conversation.
krileon
Team Member- OFFLINE
- Posts: 68554
- Thanks: 9095
- Karma: 1434
9 years 10 months ago - 9 years 10 months ago #246374
by krileon
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Replied by krileon on topic equivalent plans in CBSubs
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.
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.
UPDATE `#__cbsubs_subscriptions` AS a
SET a.`expiry_date` = DATE_ADD( a.`expiry_date`, INTERVAL ( SELECT TIMESTAMPDIFF( SECOND, b.`expiry_date`, b.`previous_expiry_date` ) FROM ( SELECT * FROM `#__cbsubs_subscriptions` ) AS b WHERE b.`id` = a.`replaces_subscription` ) SECOND )
WHERE a.`id` = '[subscription_id]'
AND a.`replaces_subscription` IS NOT NULLThe 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.
SELECT b.`expiry_date` AS old_cur_expiry_date,
b.`previous_expiry_date` AS old_prev_expiry_date,
a.`expiry_date` AS cur_expiry_date,
DATE_ADD( a.`expiry_date`, INTERVAL TIMESTAMPDIFF( SECOND, b.`expiry_date`, b.`previous_expiry_date` ) SECOND ) AS new_expiry_date,
TIMESTAMPDIFF( SECOND, b.`expiry_date`, b.`previous_expiry_date` ) AS diff
FROM `jos_cbsubs_subscriptions` AS a
INNER JOIN `jos_cbsubs_subscriptions` AS b
ON b.`id` = a.`replaces_subscription`
WHERE a.`id` = SUBSCRIPTION_ID_HERE
AND a.`replaces_subscription` IS NOT NULLAs with any code suggestions. Backup your database and test thoroughly to ensure you get the desired behavior.
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Last edit: 9 years 10 months ago by krileon.
The following user(s) said Thank You: nant, dhphllps
Please Log in to join the conversation.
- dhphllps
- OFFLINE
-
Senior Member
- Posts: 63
- Thanks: 5
- Karma: 1
9 years 5 months ago #253588
by dhphllps
Replied by dhphllps on topic equivalent plans in CBSubs
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?
AND a.`replaces_plan` IN(x,y,z)Please Log in to join the conversation.
- krileon
- Team Member
- OFFLINE
- Posts: 68554
- Thanks: 9095
- Karma: 1434
9 years 5 months ago #253620
by krileon
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Replied by krileon on topic equivalent plans in CBSubs
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.
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
The following user(s) said Thank You: dhphllps
Please Log in to join the conversation.
Moderators: beat, nant, krileon
Time to create page: 0.241 seconds
-
You are here:
- Home
- Forums
- Support and Presales
- Developer Members Support
- equivalent plans in CBSubs