CB Subs integration email expiration timing

activha
OFFLINE
Posts: 2334
Thanks: 117
Karma: 13

5 years 3 months ago #313559 by activha

Replied by activha on topic CB Subs integration email expiration timing

Did you mean that this would work ?``

Code:

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`plan_id` IN ( 6, 39, 40, 41, 51, 45, 31, 42, 43, 44, 52 ) AND date_add(sub.`expiry_date`, INTERVAL 8 DAY) <= date_add(NOW(), INTERVAL 1 DAY) AND sub.`autorecurring_type` = 0 ) >= 1 
AND ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`plan_id` IN ( 6, 39, 40, 41, 51, 45, 31, 42, 43, 44, 52 ) AND (sub.`autorecurring_type` = 2 OR sub.`autorecurring_type` = 1)) = 0 
AND ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`plan_id` IN ( 6, 39, 40, 41, 51, 45, 31, 42, 43, 44, 52 ) AND date_add(sub.`expiry_date`, INTERVAL 8 DAY) > date_add(NOW(), INTERVAL 1 DAY)  ) = 0 
AND ( SELECT COUNT(*) FROM `#__cbsubs_payments` AS a WHERE a.`for_user_id` = u.`id` AND a.`time_created` > date_sub(NOW(), INTERVAL 366 DAY) AND a.`payment_status` = 'Pending' ) = 0 
AND (ue.`cb_crmstate` IN (0, 1, 4, 8) OR  ue.`cb_crmstate` IS NULL))

Please Log in or Create an account to join the conversation.

krileon
ONLINE
Posts: 48541
Thanks: 8290
Karma: 1445

5 years 3 months ago #313560 by krileon

Replied by krileon on topic CB Subs integration email expiration timing

You only need to add time to expiry_date. Specifically 7 days since that's your grace period. Then compare it to the current date. You shouldn't need to add any time to the current date. You just need to make sure the expiry date is accurate. I've no idea what your query is supposed to even be doing based off that snippet though so maybe you do need to add time to the current date depending on what you're trying to do.

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.

Please Log in or Create an account to join the conversation.

activha
OFFLINE
Posts: 2334
Thanks: 117
Karma: 13

5 years 3 months ago #313561 by activha

Replied by activha on topic CB Subs integration email expiration timing

This is a filter query for a CB list that is supposed to give us all paid expired plans which are not auto recurring and that we need to address manually or reversed paiement that we also need to deal with

Please Log in or Create an account to join the conversation.

krileon
ONLINE
Posts: 48541
Thanks: 8290
Karma: 1445

5 years 3 months ago #313562 by krileon

Replied by krileon on topic CB Subs integration email expiration timing

Then the below condition should work for seeing if their subscription has expired.

Code:

DATE_ADD( sub.`expiry_date`, INTERVAL 7 DAY ) >= NOW()

This assumes all your plans have a 7 day grace period. If they don't you'll need additional checks against plan_id to account for that. What other conditions you check against (e.g. if it's auto recurring or not) are up to you.

Please Log in or Create an account to join the conversation.

activha
OFFLINE
Posts: 2334
Thanks: 117
Karma: 13

5 years 3 months ago #313563 by activha

Replied by activha on topic CB Subs integration email expiration timing

Thanks a lot, that should work fine :-)

Please Log in or Create an account to join the conversation.

activha
OFFLINE
Posts: 2334
Thanks: 117
Karma: 13

5 years 3 months ago #313770 by activha

Replied by activha on topic CB Subs integration email expiration timing

Also we have noticed that the last failed payment did not reverse the payment but only unsubscribed the subscription. Previously the payment was also reversed and indicated as this on CBSubs. Did you change something or did STRIPE change something ?
Nothing was changed on our end in regards to dispute handling. If it was a dispute notification it will put the subscription in a reversed status. If it was a failed payment notification then it will fail the renewal and simply expire.

Regarding this answer, we have noticed that in the case of a failed payment, there is no indication in CBSubs that the payment has failed. STRIPE does send a web hook but there is nothing in notifications/baskets/payments on this.

As a result it's impossible to know when a customer has a failed payment that needs to be addressed. Could you add a process like the one for reversed state where it's indicated somewhere that the payment has failed at the payment gateway ?

Please Log in or Create an account to join the conversation.

Moderators: beat, nant, krileon