Subsections

• Overview
• Extending Student Demographics

Customize OA - Adding New Fields to Students and Staff

Overview

Open Admin can have both student demographics and staff information extended with additional fields with very little effort. The staff table already contains several additional fields for use by international schools for storing staff certification, etc.

The only negative to this process is that a report must be created to actually display this information. The Student Roster reports are templated so that they can be changed and new templates created with the template generator. Staff reports are also template based.

This is made possible by:

1. Templates - files located in a template directory hold layouts for student addition and editing, student search, and student viewing. These templates are loaded by scripts to enable editing of the student table(s): student, studentwd(withdrawn students) and prereg (preregistered students).

2. A Metadata table - a table storing information about the structure of the staff and student tables in OA. There are additional fields in each record that are used to control form element length and type, default values, etc. This information can modified by using the meta scripts (edit and update) on the Eoy page.

Extending Student Demographics

As a result of this, additional fields may be added to the end of the student tables. All 3 student tables must remain identical since records are moved directly from one table to another during withdrawal, re-enrollment, etc. Fields should not be removed from the current student table format since older scripts may depend on this organization.

The templates are then updated to add these additional field(s) to the templates. The final part of the process would include, possibly, some additional custom reports that support these new fields. OA really requires a report generator, but that must lie in the future.

The following scripts are now template aware and will display the additional fields added to the student tables and templates:

1. Student Search - cgi/studsearch.pl (uses the studsearch.tpl template).

2. Student Enrollment - cgi/entry/sentryX.pl scripts (use the student.tpl template).

3. Student Editing - cgi/studed.pl (and studeled.pl now supports editing of any of the 3 student tables)(also uses the student.tpl template)

The templating technology is currently making use of simple regex matching and replacement. Future use of templating modules is being considered as other needs develop and the pros and cons of each module are weighed.

The templates themselves are simply HTML markup blocks with the following tags that are replaced during script execution:

1. $<$*desc*$>$ tags that are replaced by a fieldname descriptor from the meta table. These descriptors will be replaced during system configuration with alternate language versions stored in the meta table.

2. $<$@value@$>$ tags that are replaced with an entry form with features controlled by the meta table: input type, field length, maxlength, etc when editing. These replaced with actual values from tables during search and viewing.

The meta table currently has 2 scripts:

1. metaupdate.pl - a script to read the current database and update the metatable to match any additional fields added to or fields removed from the underlying tables.

2. metaedit.pl - a script to allow edits to any of the field settings in the meta table. Currently this only applies to the student and staff tables. (studentwd and prereg tables are ignored and the settings apply to all 3 tables, since they are identical structurally).

   This script allows you to:

   • Change the field name desciptor if a $<$*$>$ tag exists in the template for it.

   • Default values - these will be the values available if the form entry type is a selection list. The following rules apply for select entries:
     • Each entry must be separated from the other by a space.
     • Multiword entries they must be joined together with an underscore (_). It will be removed before displaying.
     • Initial tilde characters () force a blank at the start of a selection box.

   • Formtypes - text input boxes, selection lists, checkboxes, textareas are allowed. Checkboxes in edit screens become text input boxes, since they are impossible to use to clear values.

   • View size - controls the size of the box for text input form types.

   • Required - controls whether this is a required field when enrolling students.

The process of customization / table extension consists of the following steps:

1. Create additional field definitions in a file (update.sql) . Use this file to extend the student table (mysql -ppassword database $<$ update.sql). Copy and edit this file so that you also extend the studendwd and prereg tables in the same way. (updatewd.sql, updatepre.sql)

2. Edit the template folder to add the new field(s) to the templates at a desired location (or use the template creator). This will allow for addition and editing of the new fields during enrollment and demographic updates as well as searching and viewing of the field(s). They can also be printed out on the student viewing page.

3. Run the metaupdate.pl script to add those new field definitions to the meta table. Each new field in the student table will become a new record in the meta table. Now run the metaedit.pl script, choose to edit the student table and then add your new field characteristics.

4. You now have a customized version of OA with more student fields. As new releases of OA come out, your custom templates will have to be placed in the new versions's template directory to match your extended student table(s). The meta data will not be altered so this may be left.

5. If you are using MySQL version 4.x, the studentall table must be rebuilt due to the extended student table . MySQL version 5.x will not require any change since it uses a view. The simplest approach is to first get the data from the current student table and put into a text file:
   

   mysqldump -d -ppassword -u user database student $>$ student.sql

   (where password is your mysql user password, user is your mysql user, and database is your database.)

   Now do the same thing to create a data dump of the studentall table:
   mysqldump -d -ppassword -u user database student $>$ student.sql

   Edit the student.sql file and change the name from student to studentall. At the bottom of this file copy the identical section of the studentall.sql file replacing the MyISAM section with the merge table information.

   Now drop the studentall table from your database (drop table studentall), and create the new one:

   mysqldump -d -ppassword -u user database student $<$ student.sql

   (the student.sql file, now changed, is actually the file used to create the new version of studentall)

You are now done.