Add Budget for EUR in Projects

[dan-tang-ssd]

This PR is submitted to fix #206

This PR contains below changes:

1. Add column projects.budget_eur
2. Add a command to calculate budget_eur if it is zero
3. Update dashboard stored procedure, use column projects.budget_eur instead of projects.budget in SQL for filtering
4. Projects create / edit view, calculate budget_eur automatically
5. Project import program, calculate budget_eur automatically

[what-the-diff[bot]]

PR Summary

• Added New Function for Updating Project's Budget A new function has been added that makes it easier for us to update a project's budget in Euros. This function is accessible by using the command console.

• Modified Storage and Updating Process The way we store and update projects has been changed. We now have an additional step that calculates the project's budget in Euros before the project is stored or updated.

• Changes to Project Data Imports The process for importing project data has been altered. Now, the budget in Euros is included in imported project data, calculated using the budget and exchange rate information.

• Added new column for Project Budget in Euros Our project data now includes an additional column that displays the project's budget in Euros. This information will be very valuable for having a clear picture of each project's resources.

• Improved Project Selection Criteria We have improved the way we select projects to work with. We now take into consideration whether a project contributes to the funding flow, and we select projects based on their budget in Euros instead of the budget in the original currency.

[dan-tang-ssd]

Thank you Dave for your review and comments.

I will add new column projects.exchange_rate_eur.

By the way, I have a feeling that it will be a bit complicated, when we need to get exchange rate from a third party... I have cloned live db to my local db, I did a checking on exchange_rates table with below SQL:

select distinct date from exchange_rates where date > '2023-01-01' order by date;

We only have exchange rate data for 189 / 365 days.

Besides, sometimes the third party is not able to provide all 1089 (33 * 33 currencies) exchange rate data. There are 1 and 759 records for 2023-06-30 and 2023-08-25 respectively.

Currently we have a daily schedule job run at 6AM to get yesterday exchange rate. I checked that the queue is setup in live env in Forge, not sure why there is no exchange rate data after 2023-09-03...

---

I think it is better not to assume we always have exchange rate data available in applications. A real example is user may create a new project with future start date. So, I would suggest to allow user to enter exchange_rate_eur on both front end and projects import file. (make them as compulsory fields) It would be the easiest way to obtain exchange_rate_eur, then calculate budget_eur so this project is available for filtering in dashboard. (only if user specify budget criteria)

For existing projects without exchange_rate_eur and budget_eur, we may need two new schedule jobs.

1. Find projects without exchange_rate_eur and budget_eur, get unique project start dates, retrieve exchange rate data for each project start date.
2. Find projects without exchange_rate_eur and budget_eur, find exchange rate for project currency to EUR, calculate budget_eur.

---

Proposed changes:

1. Add column projects.exchange_rate_eur
2. Initiative create / edit view, add compulsory field exchange_rate_eur for user to fill in. Hide it and set it to 1.0 if project currency is EUR.
3. Initiative import excel file template, add compulsory field exchange_rate_eur for user to fill in.
4. Add a command to re-retrive exchange rate data for project start date, if exchange rate data is missing or incomplete
5. Add a command to handle initiatives without exchange_rate_eur and budget_eur, find exchange rate data in local db for processing.

[dave-mills]

Proposed changes:

• Add column projects.exchange_rate_eur
• Initiative create / edit view, add compulsory field exchange_rate_eur for user to fill in. Hide it and set it to 1.0 if project currency is EUR.

• I think you're right - it will be useful to show that we need this information to users and ask for it. I think we should approach it the same as the existing exchange rate - where the user can click the button to get the stored exchange rate if it exists, and also have the option of editing it.
• I'm ultra-cautious about adding new compulsory fields, but I think you're right - we need to have this in order to properly filter the project on the dashboard. If the project currency is EUR you're right - we can hide it and set it to 1.0. Also, if the org's currency is EUR, we can hide it and set it to the main projects.exchange_rate value.

• Initiative import excel file template, add compulsory field exchange_rate_eur for user to fill in.
• Add a command to re-retrive exchange rate data for project start date, if exchange rate data is missing or incomplete
• Add a command to handle initiatives without exchange_rate_eur and budget_eur, find exchange rate data in local db for processing.

Agreed. Also, we should work out why the current job isn't working.

[dan-tang-ssd]

Agreed. Also, we should work out why the current job isn't working.

I remote login to server, I can manually run the command to retrieve yesterday exchange rate successfully. (Only 330 / 1089 records retrieved).

I suspect there is something wrong with the queue. I have restarted the queue in Forge. I will check again next Monday. Let's see whether we have exchange rate data retrieved over this weekend.

Um... I am thinking whether we should have a monitoring mechanism to inform us when it detects a schedule job is not working.

[dave-mills]

(Only 330 / 1089 records retrieved).

I see 1089 records with 'yesterday'. Remember that it's 1 job per 33 currencies, and it rate-limits itself to avoid hitting the API limits, so it takes some time to get all of them.

I agree re: monitoring. I also want to re-start our investigation into broader monitoring (e.g. we were looking at Laravel Flare a while ago. I want to restart that train of work and get something going). Lets talk later.

[dan-tang-ssd]

I added new column exchange_rate_eur in Initiative Import Excel Template File.

Here is the excel template file for testing: Agroecology Funding Tool - Initiative Import Template.xlsx

I have also submitted PR #251 to update this file to dev branch.

[dan-tang-ssd]

I have finished my proposed changes. I think it is now ready for review.