In order to allow the UI to display all the "funds" (really budgets) for a group, ledger, or fiscal year, we need to make several adjustments on the backend:
- Update GroupFundFy schema:
- Add budgetId - UUID of the budget
- Update Budget schema:
- Add ledgerId - UUID of the ledger
- NOTE: This may not be required... Needs to be verified as part of this story. Because budget -> fund -> ledger
- Add foreign keys to group_fund_fiscal_years table:
- group_fund_fiscal_years.budgetId = budgets.id
- Add foreign keys to budgets table
- budgets.fiscalYearId = fiscal_years.id
- budgets.fundId = funds.id
- budgets.ledgerId = ledgers.id
- May not be needed. See note above about adding ledgerId to budgets.
- Add a view (group_budgets) that joins budgets, group_fund_fiscal_years and groups on:
- group_fund_fiscal_years.budgetId == budgets.id
- group_fund_fiscal_years.groupId == groups.id
- The GET /finance-storage/group-budgets queries this view
NOTE: the use of this view is (hopefully) temporary. Due to limitations with RMBs cross-index sub-query functionality we don't have too many other options. The gotcha part of this is being able to query budgets based on group acqUnitIds.
TODO: add link to a RMB story and/or wiki explaining the limitation.
This will enable queries like:
- GET /finance-storage/group-budgets?query=group.id=ABC and budget.fiscalYearId=XYZ and group.acqUnitIds=\"DEF\"
- GET /finance-storage/budgets?query=groupFundFy.groupId=ABC and fiscalYearId=XYZ (NOTE: we can't query on group.acqUnitIds here, hence the need for the view and endpoint above)
- GET /finance-storage/budgets?query=ledger.Id=ABC and fiscalYearId=XYZ and ledger.acqUnitIds= \"DEF\"
- GET /finance-storage/budgets?query=fiscalYearId=XYZ and fiscalYear.acqUnitIds=\"DEF\"
- The schema and foreign key changes described above are made
- schema examples are updated as needed
- unit tests are updated as needed