Mass Changing Services Status

[RockTBN]

Hello,

We thinking of enabling Enable Termination in Automation Settings. But we have a lot of services in Suspended status so if we enable this feature the cron jobs will not able to handle that many tasks.

Is there any way to mass changing the status of all Suspended services that are x days old (x = 15, 20 days, etc) to Cancelled/Terminated in WHMCS?

Thanks!

[brian!]

On 19/08/2019 at 11:04, RockTBN said:

Is there any way to mass changing the status of all Suspended services that are x days old (x = 15, 20 days, etc) to Cancelled/Terminated in WHMCS?

not from within WHMCS - you'd probably have to run a SQL query on the tblhosting table to change the statuses of these suspended services.

[RockTBN]

22 hours ago, brian! said:

not from within WHMCS - you'd probably have to run a SQL query on the tblhosting table to change the statuses of these suspended services.

Do you have sample query that I can try?

Thanks!

[brian!]

4 hours ago, RockTBN said:

Do you have sample query that I can try?

let's do this in 2 stages... firstly, a simple SELECT query for you to run on tblhosting to see if there are any applicable products that match your criteria...

SELECT tblhosting.*
FROM tblhosting
WHERE
tblhosting.domainstatus = 'Suspended' AND
tblhosting.regdate >= DATE_SUB(NOW(), INTERVAL 15 DAY)

if you run that in phpmyadmin or similar, you should get a list of services that match the query, e.g they're in suspended status and are within 15 days of their registration date (0-15)... if you want over 15 days (15+), then change to <= ... to exactly 15 days, use =

if you're happy with those results, then you could move to the second stage and change their values - as with all UPDATE/DELETE SQL queries, backup the database before running such queries. ⚠️

UPDATE tblhosting
SET tblhosting.domainstatus = 'Cancelled'
WHERE
tblhosting.domainstatus = 'Suspended' AND
tblhosting.regdate >= DATE_SUB(NOW(), INTERVAL 15 DAY)

and change the >= to whatever you used in the select query.

[RockTBN]

13 hours ago, brian! said:

let's do this in 2 stages... firstly, a simple SELECT query for you to run on tblhosting to see if there are any applicable products that match your criteria...

SELECT tblhosting.*
FROM tblhosting
WHERE
tblhosting.domainstatus = 'Suspended' AND
tblhosting.regdate >= DATE_SUB(NOW(), INTERVAL 15 DAY)

if you run that in phpmyadmin or similar, you should get a list of services that match the query, e.g they're in suspended status and are within 15 days of their registration date (0-15)... if you want over 15 days (15+), then change to <= ... to exactly 15 days, use =

if you're happy with those results, then you could move to the second stage and change their values - as with all UPDATE/DELETE SQL queries, backup the database before running such queries. ⚠️

UPDATE tblhosting
SET tblhosting.domainstatus = 'Cancelled'
WHERE
tblhosting.domainstatus = 'Suspended' AND
tblhosting.regdate >= DATE_SUB(NOW(), INTERVAL 15 DAY)

and change the >= to whatever you used in the select query.

You saved my life! Thank you very much!