Search code examples
mysqlmoodlemoodle-api

Moodle Resource/File Completion

I need help understanding WHERE the completion state of a resource (file) in Moodle is stored.

Please see attached images for more information.

  1. The flag next to the file is marked as completed once the user viewed View of resource/file
  2. This is setup in the Completion settings of this file/resource.

Completion setup

I need to generate a SQL report showing the file and the completion state.

I already have the "difficult part" of the query, I just need to select the completion state from "table-X" and wha-la!

Thank you.

Solution 
  • SELECT r.id, r.name, r.course, cmc.userid, cmc.completionstate, cmc.viewed
FROM mdl_course_modules_completion cmc
JOIN mdl_course_modules cm ON cm.id = cmc.coursemoduleid
JOIN mdl_modules m ON m.id = cm.module AND m.name = 'resource'
JOIN mdl_resource r ON r.id = cm.instance

    The completionstate and viewed constants are in /lib/completionlib.php

    eg:

    COMPLETION_INCOMPLETE = 0
COMPLETION_COMPLETE = 1
COMPLETION_COMPLETE_PASS = 2
COMPLETION_COMPLETE_FAIL = 3
COMPLETION_COMPLETE_RPL = 4 // This is used in Totara.

COMPLETION_NOT_VIEWED = 0
COMPLETION_VIEWED = 1