Query to cancel invoices for auto-terminated hosting services

[Troy]

Forum user rrd visited our website today and caught me on live chat, to ask about a query that would cancel invoices for services that have been terminated. (Also see this bug report.) This has been something that has bugged me for awhile, but not enough to spur me into coming up with a query that would do the job. I wasn't super busy when rrd contacted me, so I decided to dig into it.

 

Two problems became immediately apparent:

 

1. An invoice might have multiple services or domains, or other items. You only want to cancel the line item for a terminated service in such a case, and reduce the invoice total by that line item amount, leaving the invoice Unpaid but with a lower balance than previously.

 

2. An invoice might have partial credit such that reducing the invoice total by the cancelled line item could result in a negative invoice total. You probably don't want that.

 

I developed the following query that handles both of the above issues:

 

update  tblinvoices i         
       left join tblinvoiceitems ii on ii.invoiceid = i.id
       left join tblhosting h on h.id = ii.relid
set     i.subtotal = greatest(0, i.total - ii.amount),
       i.total = greatest(0, i.total - ii.amount),
       i.status = case when i.total = 0 then 'Cancelled' else 'Unpaid' end,
       ii.amount = 0
where   i.status = 'Unpaid'
and     ii.type = 'Hosting'
and     h.domainstatus in ('Cancelled', 'Terminated')

 

The use of the greatest function ensures that the invoice subtotal and total never go below zero. The case statement cancels the invoice only if the total has been reduced to zero. The individual line item related to the cancelled or terminated service is adjusted to zero.

 

I do a reasonable amount of mysql query stuff, but I'm not a guru by any means, and I was worried about the order of updates, but it appears the order is done exactly as specified, i.e. the invoice subtotal and total reductions happen before the line item amount gets set to zero (so at that time the line item amount is > zero before the final update to ii.amount sets it to zero.)

 

I built the query to handle both Cancelled and Terminated service just in case, though a client's cancellation request should automatically cancel the invoice. Some folks may do cancellation manually, so this will be of benefit in that case.

 

There is one scenario that might cause trouble:

 

Client has an invoice for hosting and domain, $10 each for example, $20 total. Client had $15 of credit on account, which was applied to the invoice, leaving a $5 balance. Invoice does not get paid, and service is terminated. Domain name does not get renewed. Query runs and reduces the $10 hosting item to zero, but leaves the domain line at $10, and the invoice subtotal and total go to zero. In this case the client has actually paid enough via credit to renew the domain OR the hosting, but not both, but the invoice is now canceled. It's probably not a common scenario, and even if this query isn't used you're going to end up with the service terminated and the domain not renewed unless some sort of manual intervention takes place, so I'm not sure if it's a big deal or not.

 

It would be nice to automatically reduce the credit and add it back to the client's credit, but even WHMCS doesn't do that if you cancel an invoice, so for now I guess it's not a big deal. I may contemplate a way to make it happen though. (Would take more than one query though for sure.)

 

If you want to use this query, please run it against test data first! I believe it to be perfectly proper but without any sort of guarantee. Use it at your own risk. I've run it against my data with a few invoices that needed canceling, and it did exactly what is was meant to do.

 

To use the query, save it to a file on your WHMCS website and schedule it to run via cron sometime after the regular cron which will do the auto-termination. The command would be:

 

mysql [whmcs db name] -u [whmcs db user] -p[whmcs db password] < /path/to/file

Edited November 27, 2012 by Troy
Added usage info