[Cyclos 4.5.3] Account balances not in sync after tx import

Bug and problem reporting on Cyclos 4 version

Moderators: rmvanarkel, hugo, alexandre

Post Reply
simonjwoolf
Posts: 113
Joined: Mon May 28, 2012 8:49 am

[Cyclos 4.5.3] Account balances not in sync after tx import

Post by simonjwoolf »

We are migrating data from a Cyclos 3.7.3 installation. Following discussions with you, we discovered that we cannot currently use the import tool in Cyclos 4.5 to import either chargebacks or scheduled payments.
We have therefore decided to do direct imports to the Postgresql database for all the transactions. We are importing to the following tables:
  • transfers
    transactions
    transaction_numbers
    scheduled_payment_installments
It is then necesssary to delete the contents of the table closed_account_balances, to have C4 recalculate account balances, after the database import. If this is not done we get all kinds of weirdness, negative account balances where there should not be, etc. This could have been becuase we previously tried importing transactions using the import tool, although we then truncated all the above tables.

However even after we cleared closed_account_balances, some accounts still display a message like "Negative since: 21-03-2016" even though they now display the correct positive balance.
See this screenshot for clarification:
negative-balance-bug.png
negative-balance-bug.png (46.99 KiB) Viewed 2525 times
How can we reset this information?
---
Simon Woolf
Cyclos Consultant
luis
Posts: 201
Joined: Fri Feb 17, 2006 11:01 am

Re: [Cyclos 4.5.3] Account balances not in sync after tx imp

Post by luis »

You can list the accounts which are not negative, but have a negative_since date with the following sql:

Code: Select all

select a.id, t.name, u.name, a.negative_since
from accounts a 
  inner join account_types t on a.account_type_id = t.id
  left join users u on a.user_id = u.id
where balance_diff(a.id) >= 0 and a.negative_since is not null
order by t.subclass, t.name, u.name
To fix them, just run:

Code: Select all

update accounts set negative_since = null where balance_diff(id) >= 0
Last edited by luis on Tue Mar 29, 2016 11:14 am, edited 1 time in total.
Luis Fernando Planella Gonzalez
Cyclos development team
simonjwoolf
Posts: 113
Joined: Mon May 28, 2012 8:49 am

Re: [Cyclos 4.5.3] Account balances not in sync after tx imp

Post by simonjwoolf »

Thanks, this works well. You just made one small error in the second sql statement. It should be:

Code: Select all

update accounts set negative_since = null where balance_diff(id) >= 0
---
Simon Woolf
Cyclos Consultant
luis
Posts: 201
Joined: Fri Feb 17, 2006 11:01 am

Re: [Cyclos 4.5.3] Account balances not in sync after tx imp

Post by luis »

simonjwoolf wrote:Thanks, this works well. You just made one small error in the second sql statement. It should be:

Code: Select all

update accounts set negative_since = null where balance_diff(id) >= 0
Sorry, wrong copy-and-paste. I'll edit the above reply, so it stays there for future reference.
Thank you.
Luis Fernando Planella Gonzalez
Cyclos development team
admin_de2
Posts: 81
Joined: Wed Dec 31, 1969 9:00 pm

Re: [Cyclos 4.5.3] Account balances not in sync after tx import

Post by admin_de2 »

Dear Cyclosians,

still the same "error" with new Cyclos 4.12.x ? See screen.

How does this come?
Is it neccessary to update regulary the accounts?
Is there already a script for it?
Or can it be fixed?
--
After all, what does the "negative_since" means at all?
I assume:

* Account has currently negative saldo (would be very helpful as filter)
* Account has negative saldo since that date

=> Should be reset when account saldo reaches 0 again.

thx, Sam
tx-negative-since.jpg
tx-negative-since.jpg (83.36 KiB) Viewed 145 times
luis
Posts: 201
Joined: Fri Feb 17, 2006 11:01 am

Re: [Cyclos 4.5.3] Account balances not in sync after tx import

Post by luis »

Did you really mean new Cyclos 4.12.x? Are you still using a 4.12.x version or did you mean 4.14.x?
Anyway, the negative_since date should have been set to null when the balance first reach a positive balance.
It can be used as a filter in Banking > Overviews > User balances.
If you're still using 4.12.x, we recommend you to upgrade to the latest 4.14.x.
In your case, the negative_since date is wrong.

You can fix it with the following SQL:

Code: Select all

update accounts set negative_since = null where negative_since is not null and account_balance(id) >= 0;
PS: If you're using 4.12.x, I'm not sure if this SQL would work.
Luis Fernando Planella Gonzalez
Cyclos development team
admin_de2
Posts: 81
Joined: Wed Dec 31, 1969 9:00 pm

Re: [Cyclos 4.5.3] Account balances not in sync after tx import

Post by admin_de2 »

Hi Luis,

it's 4.14.x, sorry the mistake.

So, i assume we should apply this SQL every night...
Will be fixed in ..., i assume?

Thank you, Thomas
Post Reply