Course Management Final Grade Import

Status

• Work Begun: 06/19/2006
• Percent Complete: 85%

Requirements

1. The Course Management Final Grading system must accept .csv files exported from Sakai with the following columns:
   1. Site Title: Unparsed label field.
   2. Site ID: Parsed unique course identifier of the form (term_yearterm_idcrn).
   3. Provider ID: Parsed unique course identifier of the form (term_yearterm_idcrn) or (term_yearterm_idcrn).
   4. Student Name: Unparsed label field.
   5. Student ID: Parsed unique student identifier (kerberos_name).
   6. Course Grade: Parsed two-letter grading code (valid Banner gradecode).
2. Accepted files must be parsed to resolve valid grading tuples (Course,Student,Grade) according to systemic grading rules.
3. Grading tuples passing validation must be saved to the internal database.
4. Instructors must be notified of invalid grading tuples and the cause.
5. An interface to allow direct upload of a grading sheet via url parameters must be developed
6. An interface to provide direct retrieval and parsing of gradesheets saved to common AFS space must be developed

File Format

The transitional .csv file will be required to have the following columns, in order:

Gradesheet Retrieval Interface

A new requirement is to provide a new page that will retrieve and parse a sakai-format gradesheet saved to an AFS space common to the Sakai and Course Management servers.

All pages of the final grading tool are designed to read a specific cookie when resolving course and instructor information. Therefore, the retrieval page will be responsible for correctly setting the cookie in addition to parsing the saved gradesheet.

The format of the required cookie is as follows:-
CM_CURRENTCOURSE: 200605-53443-613536 (TERM_CODE-CRN-INSTRUCTOR_PIDM)

In order to set up the cookie correctly, Sakai will need to forward some information along the url passed to the final grading tool. Specifically, Sakai will need to indicate the course that will be used. The page itself should be able to resolve the correct instructor information from the content of the auth_user cookie.

The passed in url parameters will include both site id and provider id. The page will then inspect the url parameters in an attempt to resolve a usable course identifier. Once found, it will be used to create the necessary currentcourse cookie.

Once the necessary cookie is set up, the page will need to find the saved sakai gradesheet and forward its contents to the sakai-format parser. The path to the gradesheet file will be assembled as follows:

/afs/sakai/gradefiles/course_grade-<site_id>-<provider_id>.csv

The site_id and provider_id values will be read from the url parameters of the page request. Once read, they will be assembled into a path with the default values. The service will then attempt to read the file at the indicated location. If the file does not exist, a document refresh will be initiated. The document will refresh until the file is read successfully. On a successful read, the page will invoke the gradesheet parsing routines as normal.

Interface Mockups

There are two ways we can approach the Final Grading interface for SmartSite Course Grading sheet uploads. We can either integrate the interface with the existing interface for download and upload of excel files, or we can build a separate interface. Providing one interface, while perhaps allowing some confusion, should present the most powerful set of features.

Code Changes

The following pages will be changed:

• fgs_gradsheet_parse.cfm : Implement detection of Sakai format file and invocation of appropriate tag. Also implementing new initialization mode for retrieval of sakai gradesheet.
• tag_fgs_gradesheet_parse_sakai.cfm : New implementation of csv parsing for Sakai file format.

The following pages will be created:

• fgs_direct_upload.cfm : Page to allow direct upload of a SmartSite format file to a course.
• fgs_sakai_retrieve.cfm : Page to retrieve SmartSite format file from AFS space and parse.