Project importer

[dave-mills]

Updates the project importer (fixes #28 , fixes #29 ).

The Feature + Updates

Users can upload an Excel file of project/initiative information. The file data is validated against the same set of rules compared to adding records through the platform.

• Previously, the user would select an organisation to import the data into. Now, the organsiation is automatically chosen as the "current" org (via the Session), and the user chooses which portfolio to import the data into.
• This PR also adds proper validation, so now any validation errors are shown after the upload attempt.
  • As part of this, I switched to use the GeographicReach enum class everywhere I could find (for consistency).
  • I added a fix for the ProjectUniqueCode Rule, so that it now gets the organisation from the Session.
• I also added a global scope to Portfolio, because the default should probably be to only return portfolios for the current institution. (We can overwrite this whenever needed - but I think the only place we need to will be for the comparison data on the dashboard).

Technical Notes

The template includes a row of instructions and an exmaple row. The ProjectImport toModel function ignores those rows, but we need the validation to ignore them too. We can use SkipsEmptyRows to ensure the validation is not run against empty rows.

There is also a isEmptyWhen feature that lets us add custom logic to tell the importer when a row should be considered empty - e.g. if $row['code'] === 'example'. However, that feature is not yet published for toModel() importers. See https://github.com/SpartnerNL/Laravel-Excel/pull/3828. Once that PR is merged and published, this will be much easier.

For now, I have added a hack into the prepareForValidation function, which replaces the instruction and example rows with fake data that will pass validation. (It will still be ignored by the toModel function).

Eugh. But it works!

---

Screenshots:

User must choose which portfolio to import data into:

Validation errors are shown, along with the row numbers from the Excel file:

[dave-mills]

For reference - here is the upload template. To enable the download from the Projects Crud page, put it inside storage/app/. AE Marker - Project Import Template.xlsx

[dan-tang-ssd]

Thank you Dave.

I performed testing, with some minor comments as below:

1. geographic_reach seems not imported.

2. Should we add validation to avoid negative budget?

3. In data import excel file template, should we indicate which columns are compulsory? We may consider to highlight them in different color.

4. It would be better if we can indicate row 3 example is just an example. It will not be imported into data platform.

5. Should we zoom out the excel file a little bit to show all columns at once? Just my personal opinion, I feel more "under control" if no scrolling is required.

[dave-mills]

Thanks Dan,

1. geographic_reach seems not imported.

Good spot. I've fixed that in the importer.

2. Should we add validation to avoid negative budget?

Good idea. I've done that in the ProjectRequest.

On your Excel template comments:

• Yeah, it'd be good to highlight the compulsory fields. I've had a go at doing that in the attached. What do you think?
• I've modified the example description to say that it won't be imported. But I think I'd prefer not to format it differently, as it should look the same as a 'real' entry would. I anticipate many people would delete it, which is fine, but we have the conditional check on the importer in case they don't.
• I've zoomed out a bit. I rarely remember that Excel actually stores the previous zoom level with the file!

What do you think about the attached? AE.Marker.-.Project.Import.Template.xlsx

[dan-tang-ssd]

Thank you Dave.

It is working fine now.

The excel file template looks good, except cell E4 example project budget not showing actual budget value...

[dave-mills]

Easy fix. Here's the proper template without my messy test: AE.Marker.-.Project.Import.Template.xlsx