Missing invoices

[MrL22]

Hi Guys,

Recently I came across an issue that has been happening over time where a next invoice date for a monthly service would change to (for example) 4 months in the future instead of 1 month, this would then not create 3 invoices until the next due date arrives. My only guess of how this could happen is either we (my team) have set the due date on a service to a date in the distant future by accident or WHMCS has messed up and set the wrong due date.

I have written a MySQL query which I thought I would share for anyone else experiencing similar issues. The query will display any services that you may have that have "uninvoiced/missed" invoices or incorrect due dates (if uncreated_invoices = 0). This only works for products/services and not domain names. 

select 
tblhosting.userid,
tblhosting.id,
tblhosting.billingcycle,
tblinvoiceitems.invoiceid as `lastinvoiceid` ,
tblinvoiceitems.duedate as `lastinvoicedue` ,
tblhosting.nextinvoicedate,
tblinvoiceitems.duedate + interval (if(tblhosting.billingcycle='Monthly', 1, if(tblhosting.billingcycle='Quarterly', 3, if(tblhosting.billingcycle='Semi-Annually', 6, if(tblhosting.billingcycle='Annually', 12, if(tblhosting.billingcycle='Biennially', 24, 0)))))) month  as `guessnextinvoice`,
ceil(TIMESTAMPDIFF(MONTH, tblinvoiceitems.duedate + interval (if(tblhosting.billingcycle='Monthly', 1, if(tblhosting.billingcycle='Quarterly', 3, if(tblhosting.billingcycle='Semi-Annually', 6, if(tblhosting.billingcycle='Annually', 12, if(tblhosting.billingcycle='Biennially', 24, 0)))))) month, now() + interval (select value from tblconfiguration where setting = 'CreateInvoiceDaysBefore' limit 1) day) / (if(tblhosting.billingcycle='Monthly', 1, if(tblhosting.billingcycle='Quarterly', 3, if(tblhosting.billingcycle='Semi-Annually', 6, if(tblhosting.billingcycle='Annually', 12, if(tblhosting.billingcycle='Biennially', 24, 0))))))) as `uncreated_invoices`,
tblhosting.domain
from tblhosting 
left join tblinvoiceitems on tblhosting.id = tblinvoiceitems.relid and tblinvoiceitems.type = 'Hosting'
WHERE 
tblhosting.nextinvoicedate > tblinvoiceitems.duedate + interval (if(tblhosting.billingcycle='Monthly', 1, if(tblhosting.billingcycle='Quarterly', 3, if(tblhosting.billingcycle='Semi-Annually', 6, if(tblhosting.billingcycle='Annually', 12, if(tblhosting.billingcycle='Biennially', 24, 0)))))) month 
AND
tblinvoiceitems.id = (select id from tblinvoiceitems where relid = tblhosting.id and tblinvoiceitems.type = 'Hosting' order by duedate desc limit 1)
and 
tblhosting.domainstatus = 'Active'
order by 
tblhosting.id asc, 
tblhosting.billingcycle asc