Extension:StudentRoster

This extension automatically hacks into Bruce's My Portal account with Foothill College Admissions and Records and downloads the data from .csv files for each class and then imports them into the wiki database.

New Students

If the student is a new student, this StudentRoster Tool will create a new UserPage based on the student's name as the registered at Foothill College. Sometimes this can be a rather non-user friendly login name, especially for International Students with 4 or 5 words or hyphens in their name.

New students are also sent a New Student Email Welcome Message.

Dropping students

Dropped students are removed from the class category and removed from the class group. There are two ways to determine if a student has dropped the class:

1. The foothill portal indicates the student has been dropped in the CSV data
2. The student data no longer exists in the csv data at all. To check for this, the list of students in the csv data is compared with the class category (e.g. Category:MUS66A Spring 2016 Students). All students in the category but not in the CSV data will be removed from the class category and removed from the class group.
   • To add a user to a classes Student Profile list without having them be removed from the class when the roster tool is run, add them to the class guest category (e.g. Category:MUS66A Spring 2016 Guests)

Andrew's Documentation

1. automatically pull the student class lists from the Foothill College Faculty Portal into the BruceTambling.com website.
2. automatically create new UserNames, passwords and send new users a Welcome Message.
3. update student lists in wiki as Foothill College Faculty Portal class rosters change.
4. the student roster tool will make category names like so: MUS66B Spring 2010 Students

Faculty Portal: https://myportal.fhda.edu/cp/home/displaylogin

Username - 10371450

Password - surround1976

https://www.brucetambling.com/wiki/Special:StudentRoster

Getting Started

Step 1

1. Login via FTP and navigate to .../w/extensions/StudentRoster
2. Open foothill.settings.php
3. Look at the Quarter and year information in the file.
4. Save a copy of the file as: foothill.setting.quartername.year.php

   Example: foothill.settings.spring.2011.php

   You will see other files in the same folder with a similar naming scheme.

5. Close the renamed copy file and reopen foothill.settings.php

Step 2

1. Log into FH Portal
2. Click Faculty then My Class List, then select the current term (quarter and year)
3. You should now see a list of all the classes

Setting $quarter and #$year

Update the values for $quarter and $year with the values for the current quarter.

Setting the $crns array

Look at the table of classes, and specifically the column labeled CRN. There will be multiple rows with the same CRN, and each CRN corresponds to a specific class.

Each row of the $crns array should contain one CRN value and the corresponding class name. Ignore rows with the same CRN; you do not need to add duplicate CRN values.

Example:

MUSF066A01Y   20784    INTRO TO DIGITAL AUDIO:PRO TOO
MUSF035B01    20788    SPECIAL PROJ IN MUSIC TECHN

Corresponds to:

$crns = array( 
	"20784" => "MUS66A",
	"20788" => "MUS35B",	
	
);

Setting $auth

Click the link for one of the classes, then copy the URL from the address bar. The URL should be something like this

https://munich.fhda.edu/ActiveRoster/activeroster.jsp?auth=626356454962304A4238434F5278776E675058444862654C7066763942794D702F674D5669586A486552593D

Take the long string after auth=, in this case

626356454962304A4238434F5278776E675058444862654C7066763942794D702F674D5669586A486552593D

and update the value of $auth in the PHP file

Setting $term

On the Student Roster page (you should be there after clicking a class link to get the $auth value), click the 'Excel Spreadsheet' export button.

The filename of the spreadsheet will contain the value for $term. The file will be named something like this: ActiveRosterExport.201321.20784.xls

The value for $term is the first set of numbers and should be 6 digits long. (Ex: 201321)

Finishing up

Go back to the class list page and double check all of the CRN values against the class names. Once you've double checked, save the file.

The student roster tool is now ready to run with the latest class information

Automatic roster synchronisation

The first step to fully automating the above manual procedure was completed on September 13^th which is having the settings files and roster lists automatically synchronised onto the server, and kept up to date every hour.

The second step was completed on September 24^th which is to maintain a database table of the roster data including the history, and notify admins by email whenever any data has changed.

The script responsible for this behaviour is sync-rosters.pl and is executed every hour from the crontab.

Note: this script doesn't affect the wiki in any way. It simply ensures that all the settings data (that you are currently creating manually with Andrew's procedure above) is locally available for all courses, and that the roster data for every course is also available and up to date.

Configuration and options

The script requires that the ActiveRoster login and password are in the wikis LocalSettings.php file as $wgActiveRosterUser and $wgActiveRosterPass. The path to the settings file is given to the script as a command line parameter. There are also a few other parameters available as follows:

Parameter	Meaning
--settings	Path to the wiki's LocalSettings.php file (required)
--all	If present, processes the entire history of roster data, otherwise only the current courses
--local	If present, updates the database using only the local JSON files and doesn't connect to the remote ActiveRoster app
--email	If present, emails the results to the admins, otherwise outputs the results directly

The crontab line that calls the script every hour is as follows (but on a single line):

0 * * * * www-data perl /var/www/brucetambling.com/TamblingRepo/scripts/sync-rosters.pl
  --email --settings=/var/www/brucetambling.com/htdocs/w

File data

The files that the script maintains are in the /var/www/brucetambling.com/TamblingRepo/scripts/sync-rosters/ directory. This directory is populated with JSON and PHP files. The JSON files such as 201621-22867.json (where the first number is the term and the second is the CRN) contain the roster data for the courses. The PHP files are the settings data in the format required by the existing wiki extensions, for example foothill-spring-2015.php contains the following content:

<?php
$quarter = "Spring";
$year = 2015;
$term = 201541;
$crns = array(
	"40681" => "MUS66A",
	"40682" => "MUS66A",
	"40833" => "MUS50C",
	"41177" => "MUS83A",
	"41422" => "MUS82E",
	"41872" => "MUS82E",
	"42419" => "MUS50C",
	"42422" => "MUS83A",
	"42423" => "MUS81D",
	"42593" => "MUS81D"
);

Database information

The script maintains a database table called "rosters" representing the roster data in the wiki database. Each row in the table is a revision of a record from the ActiveRoster system, so that the table maintains a complete history of every record rather than just the current data for each record.

The script obtains the wiki database information from the LocalSettings.php file and creates a new table (unless it already exists) with the following structure.

+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| term             | int(10) unsigned | NO   |     | NULL    |                |
| crn              | int(10) unsigned | NO   |     | NULL    |                |
| time             | int(10) unsigned | NO   |     | NULL    |                |
| deleted          | tinyint(1)       | NO   |     | NULL    |                |
| processed        | tinyint(1)       | NO   |     | NULL    |                |
| cwid             | int(11)          | NO   |     | NULL    |                |
| firstName        | text             | NO   |     | NULL    |                |
| lastName         | text             | NO   |     | NULL    |                |
| email            | text             | NO   |     | NULL    |                |
| phone            | text             | YES  |     | NULL    |                |
| status           | varchar(2)       | YES  |     | NULL    |                |
| addDate          | int(10) unsigned | YES  |     | NULL    |                |
| dropDate         | int(10) unsigned | YES  |     | NULL    |                |
| grade            | varchar(2)       | YES  |     | NULL    |                |
| waitlistPosition | text             | YES  |     | NULL    |                |
| authCodePending  | tinyint(1)       | NO   |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

The first six columns contain local information for use by the script and the wiki, while the remaining columns are the data from the ActiveRoster system. The first six columns have the following purposes.

Column	Meaning
id	The unique ID for this revision
term	The ActiveRoster term code that this revision is part of
CRN	The ActiveRoster CRN course code that this revision is part of
time	The timestamp (Unix format) of this revision
deleted	A non-zero value indicates that this record ceased to exist in the ActiveRoster system
processed	This will be used by the wiki to indicate whether or not it has already processed this revision

Selecting records

To select a specific record we would select rows by term, CRN, firstName, lastName and email which would give us all the revisions for a specific student within a specific course.

if we only wanted the current record then we would add ORDER BY time DESC LIMIT 1 to the query so that only the record revision with the most recent time-stamp would be returned.