I have described in a previous post how to implement a basic parent portal in Moodle. In this post I will show a very convenient way to search for list parents of a given set of children. This is not easy to do using standard Moodle but very easy using the plugin Configurable reports and using some custom SQL.
In configurable reports plugin (called CR from now on) setup a custom SQL type report. Paste the following SQL with any changes needed to suit your use case:
SELECT DISTINCT CONCAT(child.FirstName,' ',child.MiddleName,' ',child.LastName) AS 'Student',
child.idnumber as 'Student ID',
CONCAT(u.FirstName,' ',u.LastName) AS 'Parent',
u.id AS 'sendemail',
(select i.data
from prefix_user_info_data i
join prefix_user_info_field f on i.fieldid = f.id
WHERE i.userid = u.id
AND f.shortname = 'Gender') 'Parent Gender'
FROM prefix_user u
JOIN prefix_role_assignments as ra ON ra.userid = u.id
JOIN prefix_role as role ON role.id = ra.roleid
JOIN prefix_context as ctx ON ctx.id = ra.contextid AND ctx.contextlevel = 30
JOIN prefix_user as child ON child.id = ctx.instanceid
JOIN prefix_cohort AS h
JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid AND hm.userid = child.id
#
WHERE role.shortname = 'parent'
#
%%FILTER_SEARCHTEXT:h.name:~%%
#%%FILTER_SEARCHTEXT:child.username:~%%
ORDER BY child.username
You will get a report with a list of students filtered by the Cohort name you typed into the filter box and with the parents’ name checked to send mail. At the bottom you will see a button labelled send email as shown below:
Clicking the button will send out emails to the selected parents.
The default version of the plugin as downloaded from the Moodle plugin directory does not check the boxes by default and only sends emails. My modified version of this plugin available here branch: 30-send_message, checks all boxes by default and sends notifications instead of only emails. The advantage is that the notifications can be controlled from the user message / notification preferences and available via web/email/mobile so a broader ranged capability.
This is a very powerful capability as you can see. You can tailor the SQL code to your needs to setup sophisticated filtering and message the resulting users including parents.
The modifications to the configurable reports plugin in order to message instead of only email took 3 steps:
- Add a messages.php file to the /db directory
- Add necessary strings to the language in /lang/en/block_configurable_reports.php
- In the send_emails.php file replace the email_to_sender statement with message_send statement (after properly defining the required arguments).