[
https://issues.apache.org/jira/browse/FINERACT-127?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Michael Vorburger updated FINERACT-127:
---------------------------------------
Labels: beginner performance technical (was: performance technical)
> 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
> Priority: Major
> Labels: beginner, performance, technical
>
> 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
(v8.3.4#803005)
