Course Information
Views:
Contents |
Introduction
The course information database is used by Marketing and Adult Education to compile information about the courses we run. For Marketing this information is just for the prospectus and website, Adult Education use it for that and now also for data required by the Registry in.
Database
The database lives on ares' MySQL 5 server and is called course_info. The database is split between Daytime and Adult Ed courses. Adult Ed tables are prefixed with the letters "ae_cors_", daytime course tables are prefixed with "cors_".
When the course listings have been completed, the database is exported to a remote MySQL server to make it available on the college web site.
ERD
The following documents are in visio xml format
Interfaces
Data Entry
An Access front end is used for data entry. The database lives on \\ATHENA\apps\staff\databases\course information\.
This provides a reletively familiar and easy interface for staff to use to update the database, however it does have some draw backs. Access doesn't accept all the the MySQL common data types so the database fields are loosly defined (mostly as char or varchar).
After alterations to the database, the access interface needs to be updated using the Linked Table Manager. Currently there are no windows 2000 machines that this works on, so you need to use windows XP.
The MySQL ODBC driver is required on any client machines that use the access interface. Also, the firewall will only allow connections from machines with a staff ip address - i.e. they must have a propper lease setup in dhcp so they're allocated a 10.10.100.x ip address.
The access interface is restricted to use by selected staff, this is simply set in the application object's Associations property. "cn=DATABASES - Course Information.ou=STAFF.o=vsfc"
The Area Prospectus data needs to be completed for the day time courses.
Reports
Reports are available on the staff intranet in "https://online.varndean.ac.uk/staff/courseinfo/"
External access is provided to a proof-reader, who wants to log in and read our Daytime course info and send corrections to Marketing. The account is "cn=proofreader,ou=remoteusers,ou=INTERNET,o=vsfc".
There are a few types of reports for the Adults education courses: a printers report which has all the courses listed on a single page; a public version which displays information relevant for public enquiries; Registry reports which contain the full course details; and various little reports for Adult to print off as references.
The data can easily be made available in a spreadsheet, the ODBC settings are applied to anyone who has run the Course Info application. The registry should be able to take data exported in to a spreadsheet and use it to import into their MIS system. The MySQL ODBC driver should be available on all staff workstation images.
Course and Qual Codes
The course and qual codes are generated by the reporting interface. It's a bit of a kludge. Ideally these weak entities would be generated by access but it's just too crap.
Active Enrollments
The number of students enrolled on a course is stored in Resource MIS, this is information is pulled in to the course information database with a script
The script lives on ares at https://online.varndean.ac.uk/staff/courseinfo/adult-ed/active_enrol.php, and is called every 5 minutes using /etc/crontab on ares.
The query in the script is (just to give you a vague idea of the Resource 32000 MIS schema):
$query = "SELECT SRProgramme.description, SRProgramme.ProvSpec1 AS QualCode, Enrollments.ActiveEnrollments FROM SRProgramme LEFT JOIN ( SELECT ProgID, count(*) as ActiveEnrollments FROM SREnrolment GROUP BY ProgID ) AS Enrollments ON Enrollments.ProgID = SRProgramme.id WHERE SRProgramme.OrgID = 5 AND SRProgramme.active = 1 ORDER BY SRProgramme.description
The data returned is then used to update the active_enrol column in ae_cors_courses.
Future
Intergrated web interface for data entry and reporting.
Requirements/Musings:
- ajax (with json)
- Adaptable javascript that won't need maintenace when the schema changes (needs to be sent meta data)
- Adaptable php form generation that won't need maintenace when the schema changes (needs to be sent meta data)
- xml based internal data that will feed in to javascript and php form generators, must provide meta data such as data types etc. Maybe this can all be dynamic by querying MySQL?
A dirty script for publishing
This dirty script takes the local data and prepares it for use on the college website. You might not want to look at this if you have a delicate constitution
<pre>
<?php
$connection = mysql_connect('127.0.0.1:63306', 'root', 'password') or die('unable to connect');
$ae = 'course_info';
$tmp = 'ae_course_info_web_tmp';
$startdate = '2007-09-01';
$enddate = '2008-08-31';
$tables = array(
array(
'name' => 'ae_cors_details',
'fields'=> array(
'detailid',
'title',
'course_content',
'otherinfo',
'areaid',
'enquiryto',
'enquiry_email',
'lsc_discount',
'timestamp',
'exam_fee',
'who_for',
'what_learn',
'equipment',
'homework',
'assessment',
'extra_costs',
'progression',
),
),
array(
'name' => 'ae_cors_courses',
'fields' => array(
'start_term_1',
'start_term_2',
'start_term_3',
'postcode',
'room',
'courseid',
'fee',
'coursecode',
'course_code_digits',
'detailid',
'start',
'end',
'duration',
'days',
'hours',
'timestamp',
'tutor',
'tutor_id',
'start_time',
'end_time',
'subj_code',
'daytime_evening',
'qualcode',
'consessions',
'printed',
'venueid',
'concessions',
),
'where' => "start >= '$startdate' AND end <= '$enddate' AND (venueid = 1 OR (venueid = 7 AND subj_code = 'ES')) AND NOT cancelled",
),
/*array(
'name' => 'ae_cors_lsc_discount',
'fields' => array(
'lsc_discount_id',
'discount_percentage',
'start_date',
'end_date',
),
),*/
array(
'name' => 'ae_cors_area',
'fields' => array(
'areaid',
'timestamp',
'area',
'description',
'dept_code',
'order',
),
),
array(
'name' => 'ae_cors_venue',
'fields' => array(
'venueid',
'venue',
'site_code',
'contact_tel',
),
),
);
foreach ($tables as $table)
{
if ($_GET['DROP'])
{
// Get rid of existing table in the temporary database
mysql_select_db($tmp);
mysql_query("DROP TABLE {$table['name']}");
}
// Start building a query to select all the desired fields for this table
$first = true;
$query = 'SELECT ';
foreach ($table['fields'] as $field)
{
if ($first) { $query .= "`$field`"; $first = false; }
else $query .= ", `$field`";
}
$query .= ' FROM '.$table['name'];
if (isset($table['where'])) $query .= ' WHERE '.$table['where'];
// End of building query to select all desired fields for this table
// Start building a CREATE TABLE query
mysql_select_db($ae); // Internal Course Info database
$prikey = NULL;
$tablemeta = mysql_query("show fields from {$table['name']}") or die('failed to SHOW FIELDS FROM '.$table['name']);
$create = "CREATE table {$table['name']} (\n";
while ($fieldmeta = mysql_fetch_assoc($tablemeta))
{
if ($fieldmeta['Key'] == 'PRI') $prikey = "PRIMARY KEY (`{$fieldmeta['Field']}`)\n";
if (in_array($fieldmeta['Field'], $table['fields']))
{
$create .= "`{$fieldmeta['Field']}` {$fieldmeta['Type']} ".(($fieldmeta['Null'] == 'Yes')? 'NULL': 'NOT NULL');
if ($fieldmeta['Default'] && is_string($fieldmeta['Type']) && $fieldmeta['Default'] != 'CURRENT_TIMESTAMP' && !is_numeric($fieldmeta['Default']))
{
$create .= " default '{$fieldmeta['Default']}'";
}
elseif ($fieldmeta['Default'])
{
$create .= " default {$fieldmeta['Default']}";
}
$create .= " {$fieldmeta['Extra']}";
$create .= ",\n";
}
}
$create .= $prikey;
$create .= ") ENGINE=MyISAM DEFAULT CHARSET=utf8\n";
echo "\n";
echo $create;
// End of building a CREATE TABLE query
// Query the database for all the desired data from the current table
$rows = mysql_query($query) or die(mysql_error().' failed to execute query using: '.$query);
if (!$rows) echo "No results from $query\n";
mysql_select_db($tmp); // Temporary database
mysql_query($create) or die(mysql_error()."\nunable to create table {$table['name']} using: $create"); // Create temporaty table
// Loop through all the desired data grabbed earlier and insert it in to the temporary table
while ($row = mysql_fetch_assoc($rows))
{
$insert = "INSERT INTO {$table['name']} (";
$insertKeys = array();
$insertValues = array();
foreach ($row as $key => $value)
{
$insertKeys[] = "`$key`";
$insertValues[] = (!is_numeric($value) || !$value) ? "'".mysql_real_escape_string($value)."'" : mysql_real_escape_string($value);
}
$insert .= implode(',', $insertKeys).") VALUES (".implode(',', $insertValues).")";
mysql_query($insert) or die(mysql_error()." while using: $insert");
echo "$insert\n";
}
echo "\n#######################################################################################\n";
mysql_free_result($rows);
unset($rows);
unset($row);
} // End of expression for current table
?>
</pre>
