FM_FasterCertificates

Overview

FM_FasterCertificates is a FiLMS Module that allows FiLMS to run certificate earning process in an optimized mode.

Installation & Configuration

Installation

This module must be installed globally.

Usage

  • To optimize FiLMS certificates for all users in multi-org FILMS like SaskEnergy and CNRL, groups must not be selected.

FAQ / Troubleshooting

Is a faster Cert earnable? Here's the SQL:

SELECT COUNT(1)FROM usersLEFT JOIN fm_fastercertificates_requirementsON 1=1
-- check if they have the correct group requirements-- ###########-- join out to the certificate group assn, if this is null we are all goodLEFT JOIN certificate_group_assnON certificate_group_assn.certificate_id = fm_fastercertificates_requirements.certificate_idLEFT JOIN group_member_assn-- we should not bother going through group member assn if there is going to be nothing to join to in the endON certificate_group_assn.certificate_id IS NOT NULLAND group_member_assn.user_id = users.idLEFT JOIN groups child_groupsON child_groups.id = group_member_assn.group_idLEFT JOIN groups parent_groups-- also dont bother joining to a group that is not associated to the certificate.ON parent_groups.id = certificate_group_assn.group_idAND child_groups.lft BETWEEN parent_groups.lft AND parent_groups.rgtAND parent_groups.level <= child_groups.level-- ###########
-- check if they already earned itLEFT JOIN earned_certificatesON earned_certificates.user_id = users.idAND earned_certificates.certificate_id = fm_fastercertificates_requirements.certificate_idAND (earned_certificates.expires_at IS NULL OR earned_certificates.expires_at > NOW())
LEFT JOIN earned_certificates newer_active_earned_certificateON newer_active_earned_certificate.user_id = earned_certificates.user_idAND newer_active_earned_certificate.certificate_id = earned_certificates.certificate_idAND (newer_active_earned_certificate.expires_at IS NULL OR newer_active_earned_certificate.expires_at > NOW())AND newer_active_earned_certificate.earned_at > earned_certificates.earned_at
-- check if they meet the requirementsLEFT JOIN enrollmentsON enrollments.user_id = users.idAND (enrollments.is_active = 1 AND (enrollments.inactive_at IS NULL OR enrollments.inactive_at > NOW()))AND enrollments.curriculum_id = fm_fastercertificates_requirements.curriculum_idAND (fm_fastercertificates_requirements.is_complete IS NULL OR fm_fastercertificates_requirements.is_complete = enrollments.is_complete)AND (fm_fastercertificates_requirements.is_passed IS NULL OR fm_fastercertificates_requirements.is_passed = enrollments.is_passed)AND (fm_fastercertificates_requirements.is_failed IS NULL OR fm_fastercertificates_requirements.is_failed = enrollments.is_failed)AND (fm_fastercertificates_requirements.grade IS NULL OR fm_fastercertificates_requirements.grade <= enrollments.grade)
LEFT JOIN enrollment_earned_cert_assnON enrollment_earned_cert_assn.enrollment_id = enrollments.idAND enrollment_earned_cert_assn.earned_certificate_id = earned_certificates.id
WHERE 1=1AND newer_active_earned_certificate.id IS NULLAND users.is_active = 1AND users.id = :user_idAND fm_fastercertificates_requirements.certificate_id = :certificate_idAND (-- this is a global certcertificate_group_assn.group_id IS NULLOR -- the user is in the correct gorupparent_groups.id IS NOT NULL)GROUP BY fm_fastercertificates_requirements.certificate_idHAVING COUNT(1) = COUNT(enrollments.id)AND COUNT(1) > COUNT(enrollment_earned_cert_assn.enrollment_id)