CB Subs integration email expiration timing

4 years 7 months ago #313559 by activha
Replied by activha on topic CB Subs integration email expiration timing
Did you mean that this would work ?``
( ( 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 to join the conversation.

4 years 7 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 to join the conversation.

4 years 7 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 to join the conversation.

4 years 7 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.

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.


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 to join the conversation.

4 years 7 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 to join the conversation.

4 years 7 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 to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.222 seconds

Facebook Twitter LinkedIn