Cbsubs: How to extract info about plan duration + grace period

hello

I would like setup a CB content module and display conditionnaly a little bit before end of plan duration. Inside this module, I would like display conditionally some text such:

You have again "X" days available before your 30 days trial plan expire

Then if user do not renew his subscription before 30 days, I would like display:

- Your trial plan is now expired. You have a grace period of "Y" days for let you choose the plan who fit your need before auto-disabling your access.

For do that I need to know value related to X and Y but if I'm not wrong no substitution are available for get these info. CB Subscription module is a nice tools but is not very adapted for my case, reason why I would like build my own.

any suggestions please ?

thank

There's no CBSubs substitution usages outside of CBSubs at this time unfortunately. You can try using CB Query Field and its Query fieldtype to query the database for substitution information such as expiration date then try using it in a substitution IF statement. You'll find a users subscription information in the _cbsubs_subscriptions database table.

I found in the forum this topic

www.joomlapolis.com/forum/153-professional-member-support/193063-solved-delimiter-date-if-statement?limitstart=0#193587


with this nice query field:

SELECT IF( DATEDIFF( `cb_memberexp`, NOW() ) <= 0, 0, 1 ) FROM `#__comprofiler` WHERE `id` = '[user_id]'

This allow to compare plan "expiration date" VS "today date" and return 1 or 0 according situation. I wondering how to modify this query for compare "expiration date less 15 days" VS "today date". My goal is to know if today remain again 15 days or less before expiration date. If yes the field will return 1 otherwise 0, like that I can display conditionally a message such "Your subscription will end in 15 days or less, we suggest to renew now".

Any clue ?

thank

You can use MySQL functions to subtract from either of the date values before comparing. Example as follows.

Subtract 15 days from Expiration Date:
DATE_SUB( `cb_memberexp`, INTERVAL 15 DAY )

Subtract 15 days from Current Date:
DATE_SUB( NOW(), INTERVAL 15 DAY )

Please see the below MySQL documentation for further date function usage.

dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

Please note `cb_memberexp` is a CB field created within CB > Field Management so it doesn't exist unless you've created it. It also doesn't have their expiration date unless you setup your CBSubs plans to populate it with expiration date. Alternative is to query _subscriptions table for the expiration date directly.

In complement of solution provided in this topic, I found another useful trick. I created a "countdown" query field who display remaining days before plan expiration. Then I created a delimiter field for get the value. Knowing this value, is now easy to use condition for show/hide anything according to remaining days.

For who need to do the same, here the query I use:

SELECT DATEDIFF(`expiry_date`,NOW()) AS days FROM `jos_cbsubs_subscriptions` WHERE `user_id` = '[user_id]';