(proposal) Server utility for finding beginning balance for an account.

[jniles]

To completed the work on #1635, we need to have a way to get the beginning balance for any give date.

Proposal
I propose we write a server utility that can be tested as a standalone. It will be in server/controllers/finance/accounts.js called getOpeningBalanceForDate(accountId, date). It should return a number - the balance of the account. The usage pseudocode would be like this:

const Accounts = require('../finance/accounts');

const date = new Date(2017, 03, 01);

Accounts.getOpeningBalanceForDate(accountId, date)
  .then(balance => {
   console.log(`The account opening balance is: ${balance}`);
  })
  .catch(next);

Implementation Algorithm
Internally, this function must do two things:

1. Look up the period's opening balance via the period_total table. First, look up the fiscal year:

SELECT id FROM fiscal_year WHERE start_date >= DATE(?) AND end_date <= DATE(?);

Then, find all the period totals for this fiscal year and account.

SELECT SUM(debit - credit) AS balance FROM period_total
WHERE period_total.fiscal_year_id = ? 
  AND period_total.end_date <= DATE(?)
  AND period_total.account_id = ?;

2. Afterwards, we must figure out the balance up until that date. Start by getting the period current period:

SELECT id FROM period WHERE start_date >= DATE(?) AND end_date <= DATE(?);

Then we can find the balance for this period up to today:

SELECT SUM(debit - credit) AS balance FROM general_ledger WHERE trans_date <= DATE(?) AND period_id = ? and account_id = ?;

Finally, we return the result of 1) + 2) as the final result!

@IMA-WorldHealth/bhima-core what do you think?