nextinvoicedate field 0000-00-00

[Chris74]

Hi, I'm looking for some advice relating to the nextinvoicedate field in the tblhosting table.

A while back there seemed to be a problem with the cron jobs causing very high load and taking a long time to process. I noticed some issues with some hosting plans suddenly having the next due date of the year 1999 or 2000. Very strange. We resolved the handful of products with that problem.

It would appear this is not the only issue caused. Some customers are contacting us saying that they haven't received an invoice for their renewals. They only become aware that they are overdue when the account is suspended for non payment.

(Personally I think this problem has been related to Modulesgarden's "Hosting renewals" module.) There have been no problems with the domains table.

Anyway, so I look in the database and I find over 800 products that have the nextinvoicedate field set to 0000-00-00 - which is most definitely the cause of this issue. WHMCS is not generating invoices for these products (as you'd expect).

So my questions, if anyone would be so kind to help....

1. Does WHMCS always store the dates in the database as yyyy--mm-dd regardless of the date format chosen in the config?
2. Why would all of the due dates on all of the products get updated daily? Is this something that WHMCS does, or should I be looking at a cron hook within an addon causing this?
3. Could someone please be so kind as to offer me the correct syntax to set any "nextinvoicedate" that is currently set to "0000-00-00" to the same value as the "nextduedate" in the tblhosting table?

Thanks very much in advance for any help you can offer.

Edited October 30, 2018 by Chris74

[brian!]

Hi Chris,

1 hour ago, Chris74 said:

1. Does WHMCS always store the dates in the database as yyyy--mm-dd regardless of the date format chosen in the config?

yes - it's the generic way to store dates in a mysql database.

1 hour ago, Chris74 said:

2. Why would all of the due dates on all of the products get updated daily? Is this something that WHMCS does, or should I be looking at a cron hook within an addon causing this?

I think WHMCS only potentially changes duedates when there is a status change - either to the product directly or via an invoice.. it doesn't systematically do it AFAIK.... unless i'm wrong on that, i'd focus your attention on any addon that might be doing this.

2 hours ago, Chris74 said:

3. Could someone please be so kind as to offer me the correct syntax to set any "nextinvoicedate" that is currently set to "0000-00-00" to the same value as the "nextduedate" in the tblhosting table?

in SQL, it would basically be...

UPDATE tblhosting
SET nextinvoicedate = nextduedate
WHERE nextinvoicedate = '0000-00-00'

but you may need to add more conditions to the query to ensure that only applicable rows are modified...

2 hours ago, Chris74 said:

Anyway, so I look in the database and I find over 800 products that have the nextinvoicedate field set to 0000-00-00 - which is most definitely the cause of this issue. WHMCS is not generating invoices for these products (as you'd expect).

it's worth noting that it's perfectly acceptable for nextinvoicedate to equal '0000-00-00' under some circumstances - e.g one-time billingcycles will usually have both NID and NDD equal to '0000-00-00'; Cancelled services might have a NID of '0000-00-00' but a 'normal' NDD... in other words, i'd suggest examining the 800 rows closely manually before running any SQL update query on them for fear of making a bad situation worse.