nextinvoicedate field jumping a year

[Mark Donne]

Hi WHMCS Community

We have been trying to track down a problem that we have had for a while now and I wondered if anyone else was having it.  We were getting instances where a domain renewal invoice was not being generated and we even had a couple cases where the domain expired and we had to swallow the Redemption fee to get the domain back for the client. 

We figured out that it was being caused by the nextinvoicedate field in the tbldomains table sometimes jumping ahead 2 years on a 1 year renewal but it appears to be random. We thought for a while that maybe someone in our Accounts department was double clicking the "Add Payment" button on an incoming BACS payment but we know it isn't that as we only ever see 1 Transaction. We also know it is not the clients ticking "Disable Auto Renew" and then unticking before they contacted us as we checked back on the backups when we thought it could be that.

We now monitor the nextinvoicedate field every few weeks by running this SQL

SELECT d.domain,d.nextduedate,d.nextinvoicedate,ii.duedate
FROM tbldomains d
LEFT JOIN tblinvoiceitems ii ON d.id = ii.relid AND ii.duedate = d.nextduedate AND ii.type='Domain'
WHERE d.nextduedate <> d.nextinvoicedate
AND d.STATUS IN ('Active','Expired')
AND (ii.duedate IS NULL OR ii.duedate <> d.nextduedate)

This throws up any domains that are Active or Expired where the nextinvoicedate is in the future and no corresponding invoice has been created that matches it.

We do catch a few domains this way which would otherwise fall through the cracks and expire but I still can't figure out what is going wrong.  

We do use Continuous Invoice Generation and our Domain Sync Settings are enabled and we Sync Next Due Date 0 days ahead of Expiry.

I did find this post from 2018 where it sounds very similar but they never had any replies

 

Has anybody else recently seen or experienced this problem?

Mark Donne

Edited December 16, 2020 by Mark Donne
Added Tags