Case Study: Add in Multiple Certificate Type Information into your Data Source

Moodle and Totara both feature a certificate system which can be used to issue a PDF certificate if a user completes a course. This functionality was originally managed through an activity module called Certificate (mod_certificate). As Moodle and Totara have both matured as LMS's a number of other certificate plugins have become available within the Moodle community, namely Simple Certificate (mod_simplecertificate) and Custom Certificate (mdl_customcert). These plugins enhance the traditional Certificate (mod_certificate) functionality, with more features and an easier to use interface.

If you have started to use the Certificate plugin but then decide to switch to one of the certificate alternatives, your LMS may have different certificate types that have been issued to your students. In this Case Study, we will explain how the different certificate types can be added to the out of the box Detailed Certificates Issued Data Source. The Detailed Certificates Issued Data Source is built so that reports featuring #####

In this example we use the Certificate and Simple Certificate plugins. If you intend to use Custom Certificate or Simple Certificate and Custom Certificate you can use the SQL found at the bottom of the document. Please note, the Simple Certificate or Custom Certificate must be installed in order for these instructions to work. If they are not installed you will receive and error message that the database tables do not exist.

1.. To begin with, visit Data Sources, View Existing and navigate to the Detailed Certificates Issued Data Source.

2.. Right click on the data source and Duplicate the  

3.. Once the report has been duplicated, right click on the new copy to begin editing the data source.

4.. Click Edit with Data Source Designer…

5.. Select the Derived Tables area along the top menu.

6.. Enter certificates into the Query ID field and copy and paste the following SQL query into the Query text area.

SELECT certs.id as certid, certs.name as certname, certs.course AS courseid, issues.id as issuesid, issues.userid AS userid, certs.hours AS hours, certs.type AS type, issues.code, issues.timecreated 
FROM 
	(SELECT id, course, name, "certificate" As type, printhours AS hours FROM mdl_certificate 
	UNION 
	SELECT id, course, name, "simplecertificate"As type, "" AS hours FROM mdl_simplecertificate) as certs 
	JOIN 
	(SELECT id, userid, certificateid,"certificate" As type, code, timecreated FROM mdl_certificate_issues 
	UNION 
	SELECT id, userid, certificateid,"simplecertificate" As type, code, timecreated FROM mdl_simplecertificate_issues) issues ON certs.id = issues.certificateid AND certs.type = issues.type

7.. Click Run Query and the then Save Table to add the certificates table to your data source.

8.. Now visit the Joins tab.

9.. On this screen we will add in the new certificates table. To do this we will need to remove all the existing Joins involving the mdl_certificate and mdl_certificate_issues tables. Remove the following joins.

• mdl_user:id Inner mdl_certificate_issues:userid
• mdl_certificate_issues:id Left Outer files:itemid
• mdl_certificate_issues:certificateid Inner mdl_certificate:id
• mdl_certificate:course Inner mdl_course:id
• mdl_certificate_issues:certificateid Left Outer modules:certificateid
• mdl_certificate_issues:userid Left Outer modules:userid

You should end up with a view like this.

10.. Now we need to re-add the new certificate table into the data source. To do this add the following joins using the selector.

Below is a screenshot of the