Bulk Assignment of Committee Members via flat file ingestion

Jira Reference: FT-675

Background

Manoa needs a way to assign TPRC members in bulk.

Requirements

Provide an admin interface to upload the flat file

• Bulk Operation
  • TPRC Assignment
  • 
  • include app IDs to exclude from the process

Flat file format

The flat file will contain the reviewers who are eligible to be on a TPRC committee for the upcoming cycle. OVPAE pulls the following from Peoplesoft.

• Rank 5 tenured faculty not on leave
• Remove those who served on last year's TPRC lists
• Poll faculty who are available

OVPAE will give us an xls extract from PS which includes the fields below in addition to many other fields. UHM College Code corresponds to our system's Office ID which maps to "Job Div Cd" in the speadsheet.

Program to process flat file and do bulk assignment

Validations on input

• Year is required and valid (format must be 4 digit year)
• File is required

Steps for parsing the file

• for each row, lookup the UH Number from the PERSON table or LDAP
  • if UH Number not found, skip the row and add the username into an error file
• add the row to a map with the key = UH Username or UH Number and value = row data
  • this map will represent the TPRC reviewer pool for later use

Rules for assignment

1. query campus applicants by year and campus, and sort by faculty type (Classification in Tenure's database) and then UHM Department (Subject Area in Tenure's database)
   • primary sort by faculty type in this order, S, B, R, A, I/J/M
   • secondary sort by UHM Department
2. create applicant groups composed of 9 applicants
   • create applicant groups starting with the S's, then the B's, then the R's, then the A's
     • combine multiple types when necessary, for example, if there are 14 S's and 16 R's, 9 S's in one group and 5 S's + 4 R's in the next group and 9 R's in the next group, etc
   • for I/J/M faculty, create applicant groups by UHM Department
     •  keep applicants in the same department together; combining departments is ok
3. randomly select 7 people from the TPRC Reviewer pool (map generated from parsing the flat file) and assign them to a group from Step 2
   • prioritize reviewers of the same faculty type as the applicant groups
   • UHM College (Office ID in Tenure's database)
   •  reviewer must not appear on an applicant's exclusion list
4. generate csv reports
   • TPRC members list
     • anyone that this program selected assigned to serve on a TPRC committee
     • sort by last name
   • TPRC committees
     • name of TPRC group and the members of that TPRC group

Questions

Does I/J/M faculty types need to be sorted in that order? Yes

• Can faculty from different departments be combined into an applicant group? Yes
• Do they need to be keyed by faculty type for use by the TPRC selector portion of the program?

If a reviewer is on the exclusion list of an applicant in a group, does that disqualify that reviewer from anyone in that applicant group? Yes

Is the system making the assignments or is it generating the assignments and sending a report back to the provost/chancellor's office for review? 1st phase is Paul provides us with the applicants and the TPRC assignments, 2nd is running the applicant pool through the program and spitting out the assignments, 3rd is making the assignments

We do not want to store the addresses and phone numbers in the system so can your office look it up based on the username/uhuuid in the report? Sure

Male/Female check: 1/3 2/3 split