Better budget totals for dashboard

dave-mills commented 1 week ago

This PR does 2 things:

Fixes to Budget Filter

Previously, we were using the budget_eur for the entire dashboard. This was not optimal because individual organisations should see their dashboard with their chosen currency, not EUR. It was also objectively wrong, because the dashboard still looked like it was showing the org's currency.

This PR fixes that by using budget_eur for filtering, but the budget_org for total budgets and display.

The numbers entered into the budget filter are converted into EUR using the most recent exchange rate between the org's currency and EUR, and then passed to the SQL procedure.
The SQL procedure is updated to use budget_eur for the WHERE statements and budget_org when summing totals.

Fixes to AE Budget Total

The approach to calculating the total AE focused budget was wrong. Instead of totalling the budget and dividing by the average AE Score, we must calculate the ae-focused budget per-project. The attached demonstrates why (projects with bigger budgets have a greater effect on the AE-focused budget.This PR does that by adding an "aeBudget" computed Attribute to Project and then summing that.

Example AE-focused Budget.xlsx

dan-tang-ssd commented 1 week ago

This PR does 2 things:

Fixes to Budget Filter

Previously, we were using the budget_eur for the entire dashboard. This was not optimal because individual organisations should see their dashboard with their chosen currency, not EUR. It was also objectively wrong, because the dashboard still looked like it was showing the org's currency.

This PR fixes that by using budget_eur for filtering, but the budget_org for total budgets and display.
* The numbers entered into the budget filter are converted into EUR using the most recent exchange rate between the org's currency and EUR, and then passed to the SQL procedure.

* The SQL procedure is updated to use budget_eur for the WHERE statements and budget_org when summing totals.

Oh yes, it was wrong and it is a good fix!

As discussed, currently we allow institutional admin to change organisation currency.

Because projects->budget_org will be not updated automatically, and we are unable to update it because exchange rate may not exist. Human intervention and confirmation is required from institutional admin here...

We may consider to show a warning to mention the impact and follow-up actions required for changing organisation currency.

Fixes to AE Budget Total

The approach to calculating the total AE focused budget was wrong. Instead of totalling the budget and dividing by the average AE Score, we must calculate the ae-focused budget per-project. The attached demonstrates why (projects with bigger budgets have a greater effect on the AE-focused budget.This PR does that by adding an "aeBudget" computed Attribute to Project and then summing that.

Example AE-focused Budget.xlsx

It looks good, Thank you for the correction.

I think it is good to go, we can add Github issue to add warning message for changing organisation currency and work on it later on.

dan-tang-ssd commented 1 week ago

I am doing testing in local env, we may need some changes. I will update my testing result later.

Testing performed with positive result in my local env. I think it is good to merge now.

stats4sd / aec_portfolio