Steps taken to dynamically update the IoP Moodle Calendar from the MSc Addictions Timetable database

by

J.N.G.Fletcher & V.Giampietro

(14/9/09)

Version 1.0

NB The following processes described below assume that the MS Access 2007 database is being used. 1. The Timetable 2009/10 table within the MS Access 2007 timetable

database (see Fig 1) stores the data used to display calendar events on the MSc Addictions Moodle Calendar (see Fig 2).

Fig 1 Timetable 2009/10 table

Fig 2 An example MSc Addictions Moodle Calendar Event

The Event Title for the Moodle calendar event is derived from the Title field in the MS Access database. The Event Date and Event Duration displayed for the Moodle calendar event are derived from the Event_Date field (a single field which contains both date and time information e.g. 18/09/10 10:00:00 ) and the Duration field (specifying the event duration in hours) in the MS Access database . The Description field in the Moodle calendar event is used to display a hyperlink to a linked page of content (e.g. lecture notes) associated with the Moodle calendar event and is derived from the Description field in the MS Access database. For this hyperlink to be displayed and work correctly in the Moodle calendar, it must be specified in the Description field in the following format: <a target=_blank href=http://moodle.iop.kcl.ac.uk/course/view.php?id=Insert Moodle Page ID of page to link to here>Please click here for linked page</a>

e.g. to link to content on the Moodle page having id=173 one would enter the following in the Description field of that event, within the MS Access database: <a target=_blank href=http://moodle.iop.kcl.ac.uk/course/view.php?id=173>Please click here for linked page</a>

The target=_blank keywords specify to the browser that it should open the link in a new window. Remove these keywords if you want to open the link in the current window (therefore leaving the Moodle Calendar). The id of the page used to display the Moodle calendar for the particular Moodle course (which in the case of MSc Addictions is 223) is specified and derived from the courseid field in the MS Access database. Each Moodle Calendar event requires the following fields (in the form of a .txt file ) to be supplied in order to upload that event to the Moodle Calendar, namely:

name,

description,

timestart (in format 1.9.2000 14.00 )

timeduration (in seconds)

courseid (Moodle course id that is)

These five fields can be extracted from the Timetable 2009/10 table in MS Access (after doing some field renaming and data formatting) using an MS Access query called Events_query (see Fig 3 overleaf). Using the Events_query, the following field names in the Timetable 2009/10 table need to be translated to match the corresponding Moodle field names:

Title needs to be renamed name

Description needs to be renamed description

Event_Date needs to be renamed timestart

Duration needs to be renamed timeduration (and multiplied by 3600 to convert from duration in hours as stored in Timetable 2009/10 table table to duration in seconds as required by Moodle)

(NB courseid does not need to be renamed) For all the fields which need to be renamed, other than the Event_Name field, to do this renaming in the Events_query design view, place the cursor insertion point to the left of the first letter of the field name in the design grid then type the new name (i.e. the one required by Moodle) followed by a colon e.g. timestart:Event_Date The name field( renamed from Title) may contain text with commas in, and each of these commas must each be replaced with the character &#44 so that they will be interpreted as actual commas rather then field delimeters of the output .txt file which would cause subsequent upload failure of that file‟s contents into the Moodle calendar. In order to do this, specify the field in the design grid as follows: name: Replace([Event_Name],",","&#44") To convert the Duration Timetable 2009/10 table field (stored in hours) to the timeduration field required by Moodle, specify the field in the design grid as follows timeduration:[Duration]*3600 To append the Moodle courseid to this query specify the field in the design grid as follows courseid

Fig 3 Events_query design

2. The next step is to generate the output text file used to upload events to the Moodle Calendar, from the Events_Query. In order to do this first select (Export to) Text File form the External Data ribbon in MS Access (see Fig 4):

Fig 4 Select (Export to) Text File

This will cause the following Export-Text File screen to be displayed (see Fig 5):

Fig 5 Export - Text File screen

Within the File name field of this screen replace the destination file name and format shown highighted (i.e. C:\Documents and Settings\spckjnf\My Documents\Events_query.txt ) with the following: \\Tyburn\Admin\educationsupport\MSc Addictions\Timetable\Timetable\ Upload_Calendar_Events_for_MSc_Addictions.txt

Upload_Calendar_Events_for_MSc_Addictions.txt will be the file Moodle uses to update the calendar. Do not change the name of the file and make sure to write it exactly as shown, with its uppercase and lowercase letters. Now press OK on the Export - Text File screen to bring up the Export Text Wizard screen (see Fig 6):

Fig 6 Export Text Wizard screen Now click Advanced on this wizard screen which will bring up the Upload_Calendar_Events_for_MSc_Addictions Export Specification screen (see Fig 7 overleaf):

Fig 7 Export Specification screen From this change both the „/‟ Date Delimiter and „:‟ Time delimiter to „.‟ Then click Save As. This causes the Save Import/Export Specification dialog to appear (see Fig 8):

Fig 8 Save Import/Export Specification dialog At this point select OK to return to the Export Specification screen, and press OK on this in turn to return to the Export Text Wizard screen. At this point select Next to get to a second Export Text Wizard screen (see Fig 9) and ensure that Include Field Names on First Row is checked and the Text Qualifier dropdown menu is set to {none}.

Fig 9 Second Export Specification screen

Finally select Next, this will cause the final Export Text Wizard screen to be displayed (see Fig 10). At this point, select Finish. The export specification has now been successfully saved.

Fig 10 Final Export Specification screen

At this point the Export – Text File screen will appear again appear asking you to save the Export Steps you have just gone through to produce the output text file to update the Moodle calendar (see Fig 11):

Fig 11 Export - Text File screen prompt to Save Export Steps You will want to save these export steps to allow you to quickly repeat the operation of generating the output file without having to use the wizard to repeat all the previous steps that have just been illustrated.In order to do this click in the Save export steps checkbox as shown highlighted in Fig 11. This produces a tick in the checkbox and opens up several further fields on the Export - Text File screen (see Fig 12 overleaf).

Fig 12 Export - Text File screen prompt to Save Export Steps with Save export steps selected

Within the Description field add the text: „Export Steps used to create text file used in turn for updating MSc Addictions Moodle Calendar‟ to help remind users what the purpose of the saved export steps is for (see Fig 13):

Fig 13 Add a Description to Export - Text File screen then select Save Export (as shown in Fig 12 above).

Now each time you update the Timetable 2009/10 table with new/ammended event information, in order to generate the corresponding output text file (Upload_Calendar_Events_for_MSc_Addictions.txt) used to update the MSc Addictions Moodle calendar, simply select Saved Exports from the External Data ribbon within MS Access, which will cause the Manage Data Tasks screen to appear. From this screen select the Export Upload_Calendar_Events_for_MSc_Addictions saved export and finally select Run to update the output text file (see Fig 14):

Fig 14 Using saved export to generate update text output file to in turn update Moodle calendar

3. The output text file (Upload_Calendar_Events_for_MSc_Addictions.txt) generated in step 2 above is now in a format whereby it can be successfully uploaded into Moodle as demonstrated by the results of the uploadevent.php test run (see Fig 15 below):

Fig 15 Successful .txt file upload Test run

4. The method of running a saved export described above is manual in that it requires the user to actively do something i.e. run the saved export, in order to update the output text file to in turn update the Moodle calendar with any changes of events they have entered in the MS Access database. However, there is also a means to automatically update the output text file on a regular, periodic basis using Windows Task Scheduling. The benefit of this is that should the user make changes to the database, but forget to the run the saved export to update the output text file, the text file will be regenerated anyway at time scheduled for its periodic update. In fact whether or not changes to events are made to the MS Access database, the output text file gets rewritten at each scheduled update point e.g. one a week. If no event changes are made the output text file simply gets rewritten with the existing data and as a consequnece the Moodle calendar receives and displays the same event information as it did prior to the update. In order to set up this task scheduling, an MS Access Macro called autoexec needs to be set up within MS Access first. This macro will generate the output text file specified in Step 2 above each time it is run, and naming the macro “autoexec” causes it to run each time the MS Access 2007 timetable database it resides in, is itself run. When it comes to setting up periodic scheduling in the next step (Step 5) a scheduled task will be set up to run the MS Access 2007 timetable database at a regular interval, which will in turn cause the autoexec macro to run and generate the output text file used to update the Moodle calendar at each of these intervals. To set up the autoexec macro, first select (New Object) Macro from the Create ribbon in MS Access (see Fig 16):

Fig 16 Select (New Object) Macro

This will cause the following Macro Tools screen to be displayed (see Fig 17):

Fig 17 Macro Tools screen

On this screen select Show All Actions, under the Design ribbon, then select RunSavedImportExport from the Action drop down menu (see Fig 18):

Fig 18 Select RunSavedImportExport Action

Once RunSavedImportExport Action has been selected, select Export-Upload_Calendar_Events_for_MSc_Addictions as the Action Arguments from the Saved Import Export Name dropdown menu (shown highighted in Fig 19):

Fig 19 Select Saved Important Export Name

This causes the Macro‟s Arguments field, to the right of the Action field to contain the value Upload_Calendar_Events_for_MSc_Addictions. Finally select the Save icon (shown highlighted in Fig 20 ) to save the macro.

Fig 20 Save macro

This will cause the Save As dialog to be displayed in which you should specify autoexec as the macro name, then press OK (see Fig 21). The macro has now been saved within the database

Fig 21 Save macro as autoexec

5. Having create the autoexec macro in Step 4 above, we now need to set up Windows Task Scheduling to run the MS Access 2007 timetable database and as a consequence the autoexec macro at a regular periodic interval. To set up the Windows task scheduling, from the Windows Start Menu, select All Programs,then Accessories then System Tools then Scheduled Tasks (see Fig 22):

Fig 22 Select Scheduled Tasks

Selecting Scheduled Tasks causes the Scheduled Tasks screen to be displayed (see Fig 23 overleaf).

Fig 23 Scheduled Tasks Screen From this screen select Add Scheduled Task (as shown highlighted in Fig 23) which causes the Scheduled Tasks Wizard screen to be displayed (see Fig 24). Select Next on this screen.

Fig 24 Scheduled Task Wizard screen

This causes the second Scheduled Tasks Wizard screen to be displayed.Select Browse on this screen (see Fig 25):

Fig 25 Select Browse from Scheduled Task Wizard screen This will cause the Select Program to Schedule screen to be displayed. You should use this screen to navigate to where the MS Access timetable database is stored, select it by clicking on it so that it is highlighted and appears in the File name field and then press open (see Fig 26):

Fig 26 Select Program to Schedule screen

This causes the third Scheduled Tasks Wizard screen to be displayed in which you type the following name for the task: run MSc Addictions timetable database to create output text file for Moodle calendar and set the (scheduling) frequency at which the task is to be performed to Weekly, then select Next (see Fig 27):

Fig 27 Name task and set task scheduling frequency

This causes the fourth Scheduled Tasks Wizard screen to be displayed in which you set the time of day and the day itself on which to start the weekly task to be 09:30 and Monday respectively, then select Next (see Fig 28):

Fig 28 Set time and day of week to start task

This causes the fifth Scheduled Tasks Wizard screen to be displayed in which you have to enter and confirm your IoP network login password in order ensure the scheduled task will run, then select Next (see Fig 29):

Fig 29 Enter and confirm your login password to run scheduled task

This causes the sixth Scheduled Tasks Wizard screen to be displayed in which you are given confirmation that the task has been set up to run once a week on Monday at 9.30, at this point select Finish (see Fig 30).

Fig 30 Confirmation that task has been scheduled

The Scheduled Tasks screen now displays the weekly scheduled task you have just added (see Fig 31):

Fig 31 Scheduled Tasks screen showing added task

6. An automatic process on the Moodle server (vm14, 194.83.137.84) checks every 5 minutes if the Upload_Calendar_Events_for_MSc_Addictions.txt file has been modified. This uses the cron feature of the Linux server, as shown below: [root@vm14 ~]# crontab -l

*/5 * * * * /usr/bin/wget -O /dev/null

http://194.83.137.84/admin/cron.php

*/5 * * * * /scriptiop/iopMoodle.cron

The iopMoodle.cron script is located in the /scriptiop folder on vm14. This script is actually only a wrapper script calling another script in /scriptiop called iopMoodle.tcsh and generating a log file called iopMoodle.log located in /scriptiop/log: [root@vm14 ~]# more /scriptiop/iopMoodle.cron

#!/bin/tcsh

/scriptiop/iopMoodle.tcsh

“/neuroscience/msc/calendar/admin/Uploading events to

Moodle Calendar/Upload_Calendar_Events.csv>>&

/scriptiop/log/iopMoodle.log

/scriptiop/iopMoodle.tcsh

“/addiction/Timetable/Timetable/Upload_Calendar_Events_for_

MSc_Addictions.txt” >>& /scriptiop/log/iopMoodle.log

7.If the contents of Upload_Calendar_Events_for_MSc_Addictions.txt haven‟t changed in the last 5 minutes, the iopMoodle.tcsh script doesn‟t do anything and write in the log that there is nothing to do:

[root@vm14 log]# tail iopMoodle.log

Mon Dec 1 11:00:01 GMT 2008 -

Upload_Calendar_Events_for_MSc_Addictions.txt - Nothing to

do

Mon Dec 1 11:00:02 GMT 2008 – Upload_Calendar_Events.csv -

Nothing to do

Mon Dec 1 11:05:01 GMT 2008 -

Upload_Calendar_Events_for_MSc_Addictions.txt - Nothing to

do

Mon Dec 1 11:05:02 GMT 2008 – Upload_Calendar_Events.csv -

Nothing to do

8. If the contents of Upload_Calendar_Events_for_MSc_Addictions.txt have changed in the last 5 minutes, the iopMoodle.tcsh script goes through the

following steps (and updates the iopMoodle.log file):

a. Copy the new Upload_Calendar_Events_for_MSc_Addictions.txt file into the folder /scriptiop/calendarEvents and add the date and time to the file name using the _YYMMDD_HHMM format, e.g. Upload_Calendar_Events_for_MSc_Addictions.txt _081117_1343

b. Convert the file from a Windows to a Unix text file (i.e. remove invisible end of line characters that Windows adds).

c. Change the Unix permissions of the csv file to ensure that the apache user (used to run Moodle) can read it.

d. Get the ID number of the courses that have calendar events in the csv file.

e. For each of these courses, delete the current events from the Moodle database (using mysql) and add the events from the csv file using a modified version of the uploadevent.php script called uploadevent_automatic.php located in /var/www/moodle/admin. This modified version of the script doesn‟t require user input and uploads events from a file whose name is passed on as argument: uploadevent_automatic.php?csvfile=/path/of/file/to/upload

f. A php log file is generated by uploadevent_automatic.php for each course. The file name is the name of the relevant .csv file in /scriptiop/calendarEvents followed by _courseID.log.php, e.g Upload_Calendar_Events_for_MSc_Addictions.txt _081117_1343_course49.log.php