Critical Financial Report Issues After Updating to WHMCS 9.0.0

[bluesteam]

30 minutes ago, wtools said:

@bluesteam Regarding the below section,  I am lil bit confused
 

They are saying the difference is because you made some changes, but have you made any change to the income display? 

 

I never made any changes.  You are confusing the statement the support staff made to the simple query I performed to see my actual income from the reported income in the dashboard.

The query was a direct query to return the income results for the month.  There is no changes I made from my side. I saw a discrepency from the annual income reports compared to actual money received and that's when I directly ran a query on the tblinvoices table and saw the difference. The support staff member is referring to that query that I performed.
I don't have any custom displays.

I need to fix the invoices that have incorrect credit notes and support is not telling me how to do that.

Edited 3 hours ago by bluesteam

[wtools]

@bluesteam Okay, now I understand what you mean.  WHMCS is using the tblaccounts table for income calculations, as it has transactions based on each day's conversion rate. You have to use SQL quires on the tblaccounts table.  Do you have multiple currencies or just one currency? 

Edited 3 hours ago by wtools

[bluesteam]

@wtools 

We use a single currency.

I have gone deeper in to the database to see where these credit notes are stored and found 3 tables

tblaccounts (The actual credit transaction)
tblbillingnotes (The summarised credit note lines shown on the invoice under Ledger)
tblbillingnoteitems (The detailed ledger item in question)

NOTE: I COMPLETELY understand that directly editing a database is NOT the way to go but WHMCS is leaving me no choice here. I can't raise NEW invoices for invoices that have already gone out to clients AND receipted.

I suspect that deleting the transaction record and the related billing notes will remove these credit notes but I am unsure of the system impact it will have from a sequential id ordering perspective on the tables.

What do you think will happen if I remove these records? Will it break the system?

Edited 3 hours ago by bluesteam

[wtools]

@bluesteam Don't remove those records now, I'll have a look to see what will happen if we remove these records.
But do you have a table tblcredit, does it have some credit data as well? 

Edited 3 hours ago by wtools

[bluesteam]

Hi, yes I have a tblcredit table and that table has a billing_note_id column that relates to this new system.

The data in the tblcredit table has 3 records on it that relates to the new credit notes under the billing_note_id so we should also include this table.

in my case it's only 3 records and it's because these clients had actual credit which is why the tblcredit table has actual records there