Finding clients with no active products

[Fringe]

Hello,

Does anyone know how to locate any client that no longer has an active product? If I goto Clients>>view/search clients the 'Services' colum isn't sortable so I can't do it that way, I asked support via a ticket but after some random answers someone suggested I run this SQL query

Quote

select tblclients.id, tblclients.firstname, tblclients.lastname, tblclients.email from tblclients left join tblhosting on tblclients.id = tblhosting.userid where tblhosting.userid is NULL;

but that returned zero results but I can see some clients with 0(1) in the services. I do have the option to delete these poeple enabled on the config but its not happening so the cron isn't working correctly and I don't know why. All I want to do is delete these clients, I don't want to have to go through every page manually as that would take me forever.

Can anyone help?

[brian!]

5 hours ago, Fringe said:

I asked support via a ticket but after some random answers someone suggested I run this SQL query

they must have panicked and searched these forums for one of my previous replies! 😁

but that would just give you a list of clients who don't have *any* services (regardless of status).

7 hours ago, Fringe said:

but that returned zero results but I can see some clients with 0(1) in the services.

you have to be *very* careful interpreting that output because that means they have no active "services" but 1 "service" in total.... the issue you're going to have here is that, on this page, a "service" could be either a product or a domain, e.g if a client has both a domain and a hosting product, then they would be shown as having 2 total services.

so if you ran a SQL query that just checks the clients and hosting tables, then you could find a client that had no active services, but you wouldn't know that they might have 5 active domains, e.g Tom's reports below could find active clients with active services, but it won't be checking for domains...

7 hours ago, Fringe said:

All I want to do is delete these clients, I don't want to have to go through every page manually as that would take me forever.

one other thought, if you had clients who you had generated custom invoices for, but had no active services/domains, then they do wouldn't match the query.... whether you would want to (or legally can) delete a user that you have previously generated invoices for might depend on where you are in the world.

possibly the custom invoice situation doesn't arise, but you might also have domains incorrectly marked as expired (even though in reality they are active), or hosting services that are suspended pending payment - the point being to be very careful about deleting clients unless you are 100% certain about the query itself and it doing exactly what you want it to do... if in doubt, make them inactive and then check again before deletion.

[Fringe]

Hi brian!

Indeed they must have paniced, lol.. The Generate a report of clients with no active services is just what I am looking for as I don't do custom invoices so thats not going to be a factor

I will give the php code a try first and see what results I get.