steps taken to dynamically update iop moodle calendar for msc addictions programme

8/8/2019 Steps Taken to Dynamically Update IoP Moodle Calendar for MSc Addictions Programme

1/25

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

by

J.N.G.Fletcher & V.Giampietro

(14/9/09)

Version 1.0


2/25

NB The following processes described below assume that theMS Access 2007 database is being used.

1. The Timetable 2009/10 table within the MS Access 2007 timetabledatabase (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


3/25

The Event Title for the Moodle calendar event is derived from the Title field inthe MS Access database.

The Event Date and Event Duration displayed for the Moodle calendar eventare 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 ahyperlink to a linked page of content (e.g. lecture notes) associated with theMoodle calendar event and is derived from the Description field in the MSAccess database. For this hyperlink to be displayed and work correctly in theMoodle calendar, it must be specified in the Description field in the followingformat:

Please click here for linked page

e.g. to link to content on the Moodle page having id=173 one would enter thefollowing in the Description field of that event, within the MS Access database:

Pleaseclick here for linked page

The target=_blank keywords specify to the browser that it should open the linkin a new window. Remove these keywords if you want to open the link in thecurrent window (therefore leaving the Moodle Calendar).

The id of the page used to display the Moodle calendar for the particularMoodle course (which in the case of MSc Addictions is 223) is specified andderived from the courseid field in the MS Access database.

Each Moodle Calendar event requires the following fields (in the form of a .txtfile ) 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 inMS Access (after doing some field renaming and data formatting) using anMS Access query called Events_query (see Fig 3 overleaf). Using theEvents_query, the following field names in the Timetable 2009/10 table needto be translated to match the corresponding Moodle field names:

Title needs to be renamed name


4/25

Description needs to be renamed description

Event_Date needs to be renamed timestart

Duration needs to be renamed timeduration (and multiplied by 3600to convert from duration in hours as stored in Timetable 2009/10 tabletable 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_Namefield, to do this renaming in the Events_query design view, place the cursorinsertion point to the left of the first letter of the field name in the design gridthen type the new name (i.e. the one required by Moodle) followed by a colone.g. timestart:Event_Date

The name field( renamed from Title) may contain text with commas in, andeach of these commas must each be replaced with the character , so thatthey will be interpreted as actual commas rather then field delimeters of theoutput .txt file which would cause subsequent upload failure of that filescontents into the Moodle calendar. In order to do this, specify the field in thedesign grid as follows: name: Replace([Event_Name],",",",")

To convert the Duration Timetable 2009/10 table field (stored in hours) to thetimeduration field required by Moodle, specify the field in the design grid asfollows timeduration:[Duration]*3600

To append the Moodle courseid to this query specify the field in the designgrid as follows courseid

Fig 3 Events_query design


5/25

2. The next step is to generate the output text file used to upload events tothe Moodle Calendar, from the Events_Query. In order to do this firstselect (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):


6/25

Fig 5 Export - Text File screen

Within the File name field of this screen replace the destination file name andformat shown highighted (i.e.C:\Documents and Settings\spckjnf\MyDocuments\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 Moodleuses to update the calendar. Do not change the name of the file and makesure 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 TextWizard screen (see Fig 6):


7/25

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 Specificationscreen (see Fig 7 overleaf):


8/25

Fig 7 Export Specification screen

From this change both the / Date Delimiter and : Time delimiter to . Thenclick Save As. This causes the Save Import/Export Specification dialog toappear (see Fig 8):

Fig 8 Save Import/Export Specification dialog

At this point select OK to return to the Export Specification screen, and pressOK on this in turn to return to the Export Text Wizard screen. At this pointselect Next to get to a second Export Text Wizard screen (see Fig 9) andensure that Include Field Names on First Row is checked and the TextQualifier dropdown menu is set to {none}.


9/25

Fig 9 Second Export Specification screen

Finally select Next, this will cause the final Export Text Wizard screen to bedisplayed (see Fig 10). At this point, select Finish. The export specification

has now been successfully saved.

Fig 10 Final Export Specification screen


10/25

At this point the Export Text File screen will appear again appearasking you to save the Export Steps you have just gone through toproduce 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 theoperation of generating the output file without having to use the wizard torepeat all the previous steps that have just been illustrated.In order to dothis click in the Save export steps checkbox as shown highlighted inFig 11. This produces a tick in the checkbox and opens up several furtherfields on the Export - Text File screen (see Fig 12 overleaf).


11/25

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

Within the Description field add the text:

Export Steps used to create text file used in turn for updating MScAddictions 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).


12/25

Now each time you update the Timetable 2009/10 table with new/ammendedevent information, in order to generate the corresponding output text file(Upload_Calendar_Events_for_MSc_Addictions.txt)used to update the MScAddictions Moodle calendar, simply select Saved Exports from the ExternalData ribbon within MS Access, which will cause the Manage Data Tasks

screen to appear. From this screen select theExport Upload_Calendar_Events_for_MSc_Addictions saved export andfinally select Run to update the output text file (see Fig 14):

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


13/25

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 besuccessfully uploaded into Moodle as demonstrated by the results of theuploadevent.php test run (see Fig 15 below):

Fig 15 Successful .txt file upload Test run


14/25

4. The method of running a saved export described above is manual in that itrequires the user to actively do something i.e. run the saved export, inorder to update the output text file to in turn update the Moodle calendarwith 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, thetext file will be regenerated anyway at time scheduled for its periodicupdate. In fact whether or not changes to events are made to the MSAccess database, the output text file gets rewritten at each scheduledupdate point e.g. one a week. If no event changes are made the outputtext file simply gets rewritten with the existing data and as a consequnecethe Moodle calendar receives and displays the same event information asit did prior to the update.

In order to set up this task scheduling, an MS Access Macro calledautoexec needs to be set up within MS Access first. This macro willgenerate 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 MSAccess 2007 timetable database it resides in, is itself run.

When it comes to setting up periodic scheduling in the next step (Step 5) ascheduled task will be set up to run the MS Access 2007 timetabledatabase at a regular interval, which will in turn cause the autoexecmacro to run and generate the output text file used to update theMoodle calendar at each of these intervals.

To set up the autoexec macro, first select (New Object) Macro from theCreate ribbon in MS Access (see Fig 16):

Fig 16 Select (New Object) Macro


15/25

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

Fig 17 Macro Tools screen

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

Fig 18 Select RunSavedImportExport Action


16/25

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

Fig 19 Select Saved Important Export Name

This causes the Macros Arguments field, to the right of the Action fieldto contain the value Upload_Calendar_Events_for_MSc_Addictions.

Finally select the Save icon (shown highlighted in Fig 20 ) to save themacro.

Fig 20 Save macro


17/25

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

Fig 21 Save macro as autoexec


18/25

5. Having create the autoexec macro in Step 4 above, we now need to set upWindows Task Scheduling to run the MS Access 2007 timetabledatabase and as a consequence the autoexec macro at a regular periodicinterval.

To set up the Windows task scheduling, from the Windows Start Menu,select All Programs,then Accessories then System Tools thenScheduled Tasks (see Fig 22):

Fig 22 Select Scheduled Tasks

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


19/25

Fig 23 Scheduled Tasks Screen

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

Fig 24 Scheduled Task Wizard screen


20/25

This causes the second Scheduled Tasks Wizard screen to bedisplayed.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 timetabledatabase 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


21/25

This causes the third Scheduled Tasks Wizard screen to be displayed inwhich you type the following name for the task:

run MSc Addictions timetable database to create output text file forMoodle calendar

and set the (scheduling) frequency at which the task is to be performed toWeekly, 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 inwhich you set the time of day and the day itself on which to start the weeklytask to be 09:30 and Monday respectively, then select Next (see Fig 28):

Fig 28 Set time and day of week to start task


22/25

This causes the fifth Scheduled Tasks Wizard screen to be displayed inwhich you have to enter and confirm your IoP network login password inorder 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 inwhich you are givenconfirmation 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


23/25

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

Fig 31 Scheduled Tasks screen showing added task


24/25

6. An automatic process on the Moodle server (vm14, 194.83.137.84)checks every 5 minutes if theUpload_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 callediopMoodle.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 haventchanged in the last 5 minutes, the iopMoodle.tcshscript doesnt do anythingand 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 todoMon 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 todoMon 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 havechanged in the last 5 minutes, the iopMoodle.tcsh script goes through the


25/25

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

a. Copy the new Upload_Calendar_Events_for_MSc_Addictions.txtfile into the folder /scriptiop/calendarEvents and add the date andtime 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. removeinvisible end of line characters that Windows adds).

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

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

e. For each of these courses, delete the current events from theMoodle database (using mysql) and add the events from the csv fileusing a modified version of the uploadevent.php script calleduploadevent_automatic.php located in /var/www/moodle/admin.This modified version of the script doesnt require user input anduploads 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 foreach course. The file name is the name of the relevant .csv file in/scriptiop/calendarEvents followed by _courseID.log.php, e.gUpload_Calendar_Events_for_MSc_Addictions.txt_081117_1343_course49.log.php