Emmanuel Nnaa created FINERACT-127:
--------------------------------------
Summary: Journal entry performance improvement
Key: FINERACT-127
URL: https://issues.apache.org/jira/browse/FINERACT-127
Project: Apache Fineract
Issue Type: Improvement
Reporter: Emmanuel Nnaa
Assignee: Markus Geiss
The improvements will make the "accounting running balance update" job more
efficient in handling the update of journal entries.
If you reset the "is_running_balance_calculated" property to 0 for all journal
entries, running the following SQL statement will update the running balances
much faster than the "accounting running balance update" job:
{code}
SET @running_balance := 0;
SET @account_id := 0;
update acc_gl_journal_entry as je
SET
organization_running_balance = if(@account_id = je.account_id,
@running_balance := @running_balance + IF(type_enum =
1, IF(je.account_id IN (SELECT id from acc_gl_account where `account_usage` IN
(1,5)), amount * -1, amount), IF(je.account_id IN (SELECT id from
acc_gl_account where `account_usage` IN (1,5)), amount, amount * -1)),
@running_balance := IF(type_enum = 1, IF(je.account_id
IN (SELECT id from acc_gl_account where `account_usage` IN (1,5)), amount * -1,
amount), IF(je.account_id IN (SELECT id from acc_gl_account where
`account_usage` IN (1,5)), amount, amount * -1))),
account_id = IF(@account_id <> je.account_id,
@account_id:=account_id, account_id)
order by account_id, entry_date, id;
COMMIT;
UNLOCK TABLES;
SET @running_balance := 0;
SET @account_id := 0;
SET @office_id := 0;
UPDATE acc_gl_journal_entry as je SET office_running_balance =
if( @office_id = je.office_id AND @account_id = je.account_id,
@running_balance := @running_balance + IF(type_enum = 1, IF(je.account_id IN
(SELECT id from acc_gl_account where `account_usage` IN (1,5)), amount * -1,
amount), IF(je.account_id IN (SELECT id from acc_gl_account where
`account_usage` IN (1,5)), amount, amount * -1)),
@running_balance := IF(type_enum = 1, IF(je.account_id IN (SELECT id from
acc_gl_account where `account_usage` IN (1,5)), amount * -1, amount),
IF(je.account_id IN (SELECT id from acc_gl_account where `account_usage` IN
(1,5)), amount, amount * -1))),
account_id = IF(@account_id <> je.account_id, @account_id:=account_id,
account_id),
office_id = IF(@office_id <> je.office_id, @office_id:=office_id, office_id)
order by office_id, account_id, entry_date, id;
COMMIT;
update acc_gl_journal_entry set is_running_balance_calculated = 1;
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
