inactive to closed clients

[spirit918]

I've recently merged over from moderbill and it seems all of our clients that were listed as inactive with moderbill are getting auto-batched. Somehow I need to change all inactive clients to closed. Is there a fast and easy way to do this or am I going to have to go client by client?? Any help will be much appreciated!

 

Thanks.

[Troy]

Execute this query directly against the WHMCS database, via phpMyAdmin or some such:

 

update   tblclients
set      status = 'Closed'
where    status = 'Inactive'

 

Simple as that. Backup your database first, of course.

 

Or, do something perhaps more thorough - close all accounts with no active or pending services, no unpaid invoices, and who are not affiliates:

 

update  tblclients c
set     status = 'Closed'
where   not exists (select 1 from tblhosting where userid = c.id and domainstatus in ('Active', 'Pending'))
and     not exists (select 1 from tbldomains where userid = c.id and status in ('Active', 'Pending', 'Pending Transfer'))
and     not exists (select 1 from tblinvoices where userid = c.id and status = 'Unpaid')
and     not exists (select 1 from tblaffiliates where clientid = c.id)

[Troy]

Just in case anyone uses this, I figured I should post one modification - the addition of "Suspended" to the list of hosting statuses which should be disregarded in closing accounts.

 

update  tblclients c
set     status = 'Closed'
where   not exists (select 1 from tblhosting where userid = c.id and domainstatus in ('Active', 'Pending', 'Suspended'))
and     not exists (select 1 from tbldomains where userid = c.id and status in ('Active', 'Pending', 'Pending Transfer'))
and     not exists (select 1 from tblinvoices where userid = c.id and status = 'Unpaid')
and     not exists (select 1 from tblaffiliates where clientid = c.id)