Help Understanding nextduedate and nextinvoicedate db fields

[ScottN]

We recently migrated to WHMCS from another billing system. Our goal when sending invoices for domains is to send the invoice 30 days before the expiration of the domain, and the invoice would be due the day the domain expires.

 

For example, if the domain expires April 1, 2016, then we would send an invoice around March 1, 2016 and the "Due Date" on the invoice would be April 1, 2016.

 

In order to make sure we are sending invoices out at the right times, with the right due date, we could really use some help understanding the tbldomains fields nextduedate and nextinvoicedate.

 

Right now, I believe we have a problem, in that some of our domains have the SAME date for expirydate, nextduedate and nextinvoicedate. For these domains, should we update the db so that nextinvoicedate is 30 days ahead of nextduedate (so that the next invoice is sent out 30 days before next due date?)

 

WHMCS tech support has told us that "the domainsync.php script will set the nextinvoicedate value to the same as the nextduedate when it's changed." -- this has me very confused... I do not understand why you would want the nextinvoicedate = nextduedate (doesn't everyone send out invoices ahead of the due date, giving the customer time to pay the invoice?)

 

Help appreciated!

 

- Scott

[brian!]

Hi Scott,

 

Right now, I believe we have a problem, in that some of our domains have the SAME date for expirydate, nextduedate and nextinvoicedate. For these domains, should we update the db so that nextinvoicedate is 30 days ahead of nextduedate (so that the next invoice is sent out 30 days before next due date?)

that's not a problem - that's correct!

 

in terms of billing, you can ignore "next invoice date" - the only date that matters is the "next due date" as that's the one that triggers the invoice creation.

 

as you long as your "Invoice Generation" or your "Domain Invoice Generation" setting is 30, and the NDD matches the expiry date, you should be good to go.

 

http://docs.whmcs.com/Automation_Settings#Invoice_Generation

 

so if the next due date is April 1, and the invoice generation is 30 days, the invoice will be generated on March 2.

 

WHMCS tech support has told us that "the domainsync.php script will set the nextinvoicedate value to the same as the nextduedate when it's changed." -- this has me very confused... I do not understand why you would want the nextinvoicedate = nextduedate (doesn't everyone send out invoices ahead of the due date, giving the customer time to pay the invoice?)

I don't think support were accurate on that - the domain sync script just resets the NDD to the expiry date - it won't have anything to do with the next invoice date.

 

http://docs.whmcs.com/Domains_Tab#Sync_Next_Due_Date

 

when ticked the Next Due Date will be synchronized with domain name's Expiry Date. The number field can also be used to specify how far in advance of the Expiry Date to set the Next Due Date - the default setting 0 means the Expiry Date and Next Due Dates will be the same. Increasing the number means the renewal invoice will be due before the domain's expiry date, useful if your clients use slow payment methods such as cash or cheques which need time to clear.

[ScottN]

Thanks Brian! I sure wish I knew what nextinvoicedate was actually used for. We clearly have a "problem" in that our database is not consistent... some domains have a correct nextinvoicedate set (30 days before expiry & next due date), while others have nextinvoicedate set at the same date as expiry & next due date.

 

Currently I have our daily cron.php disabled, as I'm afraid of more domain renewal invoices being sent out too early, based on the inconsistencies we are seeing in the db for nextinvoicedate. Perhaps I'll give your idea a try -- ignore the inconsistency -- and try re-enabling cron again, and see what happens. I'll report back.

 

- Scott

[brian!]

Thanks Brian! I sure wish I knew what nextinvoicedate was actually used for. We clearly have a "problem" in that our database is not consistent... some domains have a correct nextinvoicedate set (30 days before expiry & next due date), while others have nextinvoicedate set at the same date as expiry & next due date.

forget about next invoice date - it's irrelevant! I don't think you have a problem - if memory serves, NID changes after an invoice has been generated, and then after it's paid, NDD will equal NID... so inconsistencies are fine and nothing to worry about.

[ScottN]

I re-enabled my daily cron, and sadly... I'm still having a problem with domain renewal invoices going out way too early. As a reminder, I simply want to invoice customers 30 days before their domains expire, and have the invoice due the day of the domain expiration.

 

Here's an example from tonight's cron:

 

Invoice #123456 
Date Created: 01/11/2016 
Date Due: 02/08/2016 
Domain Renewal - example.com - 1 Year/s (03/09/2016 - 03/08/2017) $11.45 USD

 

If I look at the database BEFORE the cron ran (I took a backup), I see:

 

domain: example.com

expirydate: 3/9/2016

nextduedate: 3/9/2016

nextinvoicedate: 2/8/2016

 

I'm stumped. Why was an invoice generated today? It should have been generated 2/8/2016, right?

 

- Scott

[brian!]

Hi Scott,

 

that's interesting and unusual - i've just rechecked all our WHMCS databases and we don't have any NID entries that are earlier than the NDD values - they're either the same or NID is x years later than NDD.

 

where expiry, NDD and NID are the same in your database, that won't be a problem...

 

it might be worth you having a quick look through the tbldomains table and seeing how often NID is before NDD - if it were the odd one, i'd manually adjust NID to match NDD.... if there are a lot, then you may need a simple SQL query to set NID to NDD if NID<NDD.

 

once NID => NDD, then that should resolve your issues with the cron and renewal invoicing should occur on the correct dates.

[ScottN]

Hi Brian.

 

Agreed, nextduedate and nextinvoicedate should be equal, based on the way WHMCS handles things. We were confused because we thought nextinvoicedate meant "next invoice date". It does not. :-(

 

It's also important for anyone reading this in the future... please do not ignore nextinvoicedate (per Brian's suggestion). It is critical to how WHMCS calculates the date to send invoices!

 

It appears that WHMCS calculates the date of the next invoice by looking at the date in nextinvoicedate, then subtracting the value of what you have set under Automation Settings > Billing Settings > Advanced Settings > Domain Settings > "Enter the number of days before the renewal date to generate invoices for domain renewals below:" I have that set to 30 days. So, it calculates the date to send the invoice: nextinvoicedate - 30 = the actual date the invoice is sent.

 

Since we had some nextinvoicedates that were set 30 days earlier than nextduedate (due to our failed logic that nextinvoicedate = next invoice date), we had to do another database update:

 

UPDATE tbldomains set nextinvoicedate = nextduedate WHERE STATUS = 'Active' AND nextinvoicedate < nextduedate 

 

Basically, we just looked for any nextinvoicedate that was less than (earlier) nextduedate, and for those, changed the nextinvoicedate to be = nextduedate. We did it this way because any domain that is already invoiced has the nextinvoicedate set out 1 year... and when the customer pays the invoice, and the domainsync cron see it, it fixes the problem (sets the dates to be equal).

 

Now everything is all square again. We are no longer sending out domain invoices 30 days early.

 

Whew.

 

- Scott