Updating customers' balances via SQL

Self-Hosted | v4/Legacy
1

Howdy,

As posted in my previous thread, I finally found a way to move all my data from Freshbooks to Invoice Ninja via a series of convoluted steps and finally importing the data using PhpMyAdmin.

I noticed that Invoice Ninja stores customers’ balances in the database, specifically in the CLIENTS table, in the BALANCE and PAID_TO_DATE fields. During the migration I used NULL in these fields, but some clients still had some balances in their accounts and now that I finished importing all invoices and payments I need to update these fields to reflect that.

So I was wondering if there is a way to update those balances, either via SQL queries or some function in Invoice Ninja.

2

Did you consider importing the CSV files on /settings/import_export rather than use PhpMyAdmin, it’s the only way to have the client’s balance/paid to date updated.

3

CSV import does not seem to bring in payments, or maybe even invoices’ items.

Also, I wanted to verify my data and make sure everything was tight when coming to Invoice Ninja, so I opted for the harder way :smiley: And I really have a lot of data stored.

Everything is great so far, there’s just this one last thing. I really believe one could devise an SQL query to update those balances.

4

There’s a script to correct the balances which may work but I’d strongly recommend making a db backup first. It’s intended to fix one off issues in the data, not to initialize all balances from scratch.

php artisan ninja:check-data --fix=true

5

Ran this, it said:

[ErrorException]
Undefined variable: activity

6

Well well… now it ran without errors. Go figure…

Still, it only updated balances on two clients. Any idea on why is that?

7

Ok, the script most likely won’t work then.

The script expects there to be at least one activity record (from when the client was created) however most of the clients in your database probably don’t have any.

8

Hm… that explains why it only updated some customers.

And that gives me an idea… I’ll try it later and report back if it Works.

9

Well, that didn’t work.

I emptied the ACTIVITIES table and populated it with one ‘created customer’ activity for each of my clients. Invoice Ninja then displayed this info correctly on the dashboard. And then I ran check-data. It only corrected two customers. Ran it again, no corrections.

Any other ideas? How does that script knows which clients it should check? Where is that script located?

10

Try running without --fix=true to see which clients are listed.

Here’s the script: https://github.com/invoiceninja/invoiceninja/blob/master/app/Console/Commands/CheckData.php

11

YEAH! I think I got it right this time.

Looking at the checkdata.php script I got the hang of how it was supposed to decide which customers it should correct. Then I noticed that my customers had both balances and paid_to_dates set to NULL. Looks like this was f’ing up the SQL logic ( ->havingRaw(‘clients.paid_to_date != sum(payments.amount - payments.refunded) and clients.paid_to_date != 999999999.9999’ ), so I just replaced both database fields with 0.00 and it ran correctly.

Also, I had to reset the activities table as in my previous reply, as the script will complain if there are no activities.

Funny thing is, this all happened because when I was preparing the first migration, I used NULL in these fields because NULL was the default value for them as per the database structure.

12

Glad to hear you were able to make it work!