A few years ago we introduced a template for our Moodle course pages to help ensure that our courses meet minimum standards with regards to course design – specifically universal design for learning. We had numerous features within this template and we wanted to examine the courses to see how many courses actually used the templates; rolling out the template is not a problem as we can roll them out as adminstrators and we can also easily customise the template for each course category.

Once each of the course pages are created using these templates the lecturer needs to edit the content provided through the template. For example our template provides a picture and the staff member is encourage to swap this picture with a photo of themselves. Similarly we put in a generic email address (teacher@dcu.ie) and encourage a lecturer to replace it with their own email address. Through ad hoc visits to course pages we realised quite a few lecturers were not inserting their own pictures or email addresses. We have over 2400 course pages so manually checking every page is not practical. So with the help of Catalyst IT we developed a report to analyse all of the course pages. Because we have set up a category for each faculty and a sub category for each school in that category we can do reports on this basis. A sample of the report is visible in the screenshot below:

The next steps are to adopt the report to analyse additional parts of the template in the same way. Furthermore at the same time of enhancing the report we will provide CPD to staff to emphasise the importance of taking the time to swap/replace key parts of the template with information specific to their course pages.

For those interested here is the code that we used and as ever any suggestions for improvement would be very welcome :

SELECT
categories.name category,
courses.shortname ‘course (short)’,
CONCAT(‘‘, courses.fullname, ‘‘) course,
CASE
WHEN FROM_BASE64(configdata) LIKE ‘%teacher@dcu.ie%’ THEN ‘X’ ELSE ‘OK’
END email,
CASE
WHEN FROM_BASE64(configdata) LIKE ‘%Avatar|%20pixabay.png%’ ESCAPE ‘|’ THEN ‘X’ ELSE ‘OK’
END avatar,
CASE
WHEN pos.visible=0 THEN ‘HIDDEN’ ELSE ‘VISIBLE’
END visible,
teachers.teacher
FROM prefix_block_instances blocks
LEFT JOIN prefix_block_positions pos ON blocks.id=pos.blockinstanceid
LEFT JOIN prefix_context instances ON blocks.parentcontextid = instances.id
LEFT JOIN prefix_course courses ON instances.instanceid = courses.id
LEFT JOIN prefix_course_categories categories ON courses.category = categories.id
LEFT JOIN (
SELECT
GROUP_CONCAT(
DISTINCT(users.email)
SEPARATOR ‘, ‘
) teacher,
courses.id courseid
FROM prefix_role_assignments assign
JOIN prefix_role roles ON roles.id = assign.roleid
JOIN prefix_context context ON assign.contextid = context.id
JOIN prefix_course courses ON context.instanceid = courses.id
JOIN prefix_user users ON assign.userid = users.id
WHERE roles.shortname = ‘editingteacher’
AND context.contextlevel = 50
GROUP BY courseid
) teachers ON courses.id = teachers.courseid
WHERE blockname = ‘html’
%%FILTER_SUBCATEGORIES:categories.path%%
AND (
FROM_BASE64(configdata) LIKE ‘%Avatar|%20pixabay.png%’ ESCAPE ‘|’
OR FROM_BASE64(configdata) LIKE ‘%teacher@dcu.ie%’
)
ORDER BY categories.name, courses.fullname