[SOLVED] CBSubs invoices for single payments using the offline engine

Hi Kyle,

Thanks for the additional info, but it does not work, I get no listings when using:

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'X' ) >= 1 ) AND ue.`cb_branch_code` = 'NSW'

I do get listings (via the CB backend) when searching for "Expired" listings with "NSW" in the cb_branch_code field.

I also tried:

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'X' ) >= 1 AND ue.`cb_branch_code` = 'NSW' )

That did not work either, so I also tried:

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'X' ) >= 1 ) AND u.`cb_branch_code` = 'NSW'

That gave this error:

Unknown column 'u.cb_branch_code' in 'where clause' SQL=SELECT COUNT( DISTINCT u.id ) FROM #__users u JOIN #__user_usergroup_map g ON g.`user_id` = u.`id` JOIN #__comprofiler ue ON ue.`id` = u.`id` WHERE u.block = 0 AND ue.banned = 0 AND ue.approved = 1 AND ue.confirmed = 1 AND g.group_id IN (18, AND ( ( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'X' ) >= 1 ) AND u.`cb_branch_code` = 'NSW' )

Can you please let me know what else I should try?

Thanks,

Dion.

ue is _comprofiler and u is _users. What type of field is cb_branch_code? If it's a multi-select or multi-checkbox you'll probably need to use a LIKE usage as follows.

AND ue.`cb_branch_code` LIKE '%NSW%'

Hi Kyle,

The cb_branch_code is just a Text Field, but I did try your further suggestion AND ue.`cb_branch_code` LIKE '%NSW%' and I still don't get any listings with this either.

it's so much easier from the backend in the Community Builder: User Managment page using the advanced "Search Tools", where you can easily and quickly select "cb_branch_code" as the CB field with "Equal to" as the Select Operator and "NSW" for this field and "Expired" for Any Subscription and it works perfectly, using the provided drop-down fields. Can you please add this type of "Search Tools" for the front end filtering, like the backend?

We need this to work from the front end, what else can I try?

Thanks,

Dion.

The below works fine to list all users who have at least 1 expired subscription. The problem is it is strictly checking for expired and a user may not be expired, but cancelled or upgraded for example.


It's typically more reliable to just check for not active, which is done with the below.


You can better review exactly what status a users subscription has in the _cbsubs_subscriptions database table as that's what the `status` in the query is checking against. Once that check is working add the field check.

Additionally you could just have your text field a searchable field on your userlist then use the advanced filter to just make sure the userlist can only display expired users. This way your text field filter isn't hardcoded into your userlist.

Hi Kyle,

The problem is not:

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'X' ) >= 1 )

or

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` != 'A' ) >= 1 )

that both display results, the issue is when adding the following as you suggested that then displays no results:

AND ue.`cb_branch_code` = 'NSW'

or

AND ue.`cb_branch_code` LIKE '%NSW%'

Why make it so difficult, if you simply had another drop-down field in the "Filter" tab for Basic searching (in the List Managment) for CBSubs Subscriptions with their Status options (as you have for the backend CB User Management), then this would solve this issue.

Can you please resolve this issue?

Thank you,

Dion.

Please PM backend super administrator login credentials and will take a look at your advanced filter.