[Polling Stations] Feature to load information about polling stations

[RaduCStefanescu]

Information about polling stations should be loaded in the NGO admin panel via CSV file.

These actions should only be available for admin ngos (organizer flag set to true).

The format file should be downloaded, the user needs to fill in the information required for the import of the polling station information.
Tasks:

• Add endpoint to download file formatted for Polling stations import (CSV)
  GET api/v2/polling-station/csvFormat
• Add endpoint to receive updated CSV file, save the information as Polling stations objects
  POST api/v2/polling-station/import <- check this; it should already be implemented in v1; also the number of polling stations value for each county should be updated in the counties table when the polling stations are imported

Linked to code4romania/monitorizare-vot-ong#129

[marius-jantea]

Hello, @RaduCStefanescu,
Regarding the second point, I noticed in the code that there is, indeed, an implementation for importing PollingStations - but that import takes data from xls and xlsx files - is this ok? If yes, then maybe a double check should be made in order to see if the polling station count / county is updated correctly.
For the first point, I noticed in the code that in other places where export functionality exists, the filename is data.xlsx - for this task should it be different? (of course, we can add .csv as a file extension , as mentioned).
Also, is there a format defined? Or we will create the functionality and then adapt it as we get feedback on it? Do we have information about who will use the export and for what purpose?
Thank you!

[aniri]

@marius-jantea Hey :) And thanks for your help!

Based on what we needed when we used the app at the most recent elections, we should adjust this task a bit:

• could we support using both csv files or xls/xlsx files as it is implemented now? if not, we can use xls/xlsx.
• and yes, for the second point the check needs to be made to see if the polling station count is set when importing polling stations
• we can ignore thee export functionality for now. I will add a separate issue for that as I'll need to think of a better solution for this 😃

[marius-jantea]

Hello, @aniri , thank you for your quick reply!

So basically we only have to do a verification that the polling station count is set when importing polling stations..
Apart from that, I will have a look over the import part - if we can use the current implementation to import csv files as well - from what I remember, in the code there was an explicit filter of the available files - xls and xlsx, but will double check, and try to see what happens if we add csv as well to that list.
If needed, we can implement the csv import as well - and trigger the import algorithm based on the file type, but as you think.

Have a great week ahead,
Marius Jantea

P.S.: do we have a deadline for this task?
I will try to do it by the end of the week, but please let me know if it is needed earlier - the verifications should not take too long, but I included the csv import as well - if this is not necessarily needed, then I will focus more on the verification part, and that's it 👍
Take care!

[aniri]

Hey @marius-jantea . Yes, thanks! It would be great to have at least an understanding of what the update to also include csv files would mean :D So it would be great if you could take a look and let us know if the change is not trivial 😃 There is no strict deadline, by the end of the week or next week sounds great! Thank you and have a nice week! :D

[marius-jantea]

Hey there,

I come back with some more questions, after analyzing the code a little bit deeper :)

1. First, will start with a description of the code, as I understood it.
2. Then I will continue with a description for the business logic - please confirm if I understood it correctly
3. In the end some more questions

Functionality Description from code

We have an implementation for import polling stations, from xls, xlsx files.

Structure of the expected excel (info from code)
The file should have a structured format - for xlsx / xls file, the following columns were identified to be mandatory, from the code:
Address, that should appear on column 7 from the Excel file (or column G)
CountyId (Cod Judet) that should appear on column 1 from the Excel file (or column A)
CodSiruta? Don't know what that means, apparently it is not saved in the database - the algorithm expects that value to be found on column 3 (C)
Polling station id (not the one from the database, but the one officially assigned) - that should be on column 5, or E in Excel.
Maybe if you have, a sample file is more than welcomed - I am curious who creates that file, and what are the other columns contain.

Now, I checked what the database saving part does - will try to describe the algorithm bellow, with some suggestions of changes - please confirm if the suggestions are ok, since I am not that familiar with the business of the application.

1. For the moment, in the code, we expect to have the first polling station on row number 3 from excel - so if a polling station will be on row 2, will not be considered (this might be because of the format of the excel - will need to see in the sample file)

2. For each row from excel, we map the entities read from excel, to the entity from the database.
   There are some extra fields in the database, that were not found in excel:
   Id (I assume this should be like a primary key in the database - this identifier is hardcoded to start from 100, and increment for each polling station imported) - if the import will ran twice, then the import will fail, since it always tries to add entities in the database with id from 100.. if the user should be able to import multiple files, or import the same file multiple times (in order to update some information for example), we need to see what would be a good approach - my suggestion would be to remove the Id column, and consider the pair county id, Polling station number as a primary key.
   Coordinates - this value is hardcoded to null (maybe will be edited later on, since the info is not present in excel.
   TerritoryCode - this is a random number generated for each row, with a max value of 10000.

3. It goes to all the counties, and updates the column NumberOfPollingStations - but here, I noticed that it adds the maximum value from table PollingStation, column Number - I think that this should be changed, in order to do a count instead - so that for each county, we will have in NumberOfPollingStations column the number of polling stations assigned.

Business logic description

How I understood it should work:

1. We have a csv / excel file (please provide a sample file, if there is any). A user can import it in the platform.
2. The data is mapped in the database (the polling station is identified by the columns "Polling station number" and "County" from excel. If the polling station is new, then it will add it to the database. If the polling station is already existing (will need to see - maybe an update, or leave it).
3. The county table is updated with the total number of polling stations assigned to it.

Questions so far:

1. Already mentioned, the sample file - and which information should we save in the db? :)
2. What should happen if something fails along the line? Should the exception be thrown by the api, and that is handled by the frontend / api client?
3. What should happen if a user tries to upload the same file several times?

Thank you,
Have a great day,
Marius

[aniri]

Hey :) Thanks for the detailed analysis!!

To answer your questions, the xls file is taken from an official site https://www.roaep.ro/management-electoral/geografie-electorala/registrul-sectiilor-de-votare/ , see 'Registrul Secțiilor De Votare – 11.09.2020'. It should have a standard format, but to make life easier for the ones that import the file into our app we could use the export functionality. Which could just return an xls with the table header which mentions the columns which are imported, something like: cod judet, IGNORED, cod siruta, IGNORED, etc

We can disable the option of importing the file a second time. And add a new endpoint for clearing all polling stations DELETE api/v2/polling-station/clearAll. This way, if there was a mistake, the admin can delete all polling stations and reimport them easily.

We should keep the polling station id as is since there is a lot of business logic depending on it, but we'll keep you suggestion of changing the primary key in mind!

Yes, the column NumberOfPollingStations from the counties table should be updated with the polling station count from the imported xls.

It would be awesome if we could support both xls/xlsx and csv files, but not mandatory.