<?php
namespace local_courseprogress\external;
defined('MOODLE_INTERNAL') || die();
require_once($CFG->dirroot . '/lib/externallib.php');
require_once($CFG->dirroot . '/completion/completion_completion.php');
require_once($CFG->libdir . '/completionlib.php');
use external_api;
use external_function_parameters;
use external_single_structure;
use external_value;
use external_multiple_structure;
use moodle_exception;
use context_course;
use completion_info;
use completion_completion;
use context_system;
use context_user;
use context_helper;
use validate_context;
class progress extends external_api
{
/**
* local_courseprogress_get_user_course_progress - API
*
* Fetches detailed course progress and activity data for a specific user in a given course.
*
* This function retrieves:
* - Course completion status (Not Started, In Progress, Completed)
* - Overall completion percentage
* - List of viewed pages (with last viewed timestamps)
* - Quiz attempts (best grade per quiz)
* - Count of forum posts by the user
*
* @param int $courseid The ID of the course to check progress in
* @param int $userid The ID of the user whose progress is being checked
*
* @return array Structured progress data including:
* - course_id: (int) Course ID
* - user_id: (int) User ID
* - status: (string) Completion status
* - completion_percentage: (int) Percentage of activities completed
* - viewed_pages: (array) List of viewed pages with titles and timestamps
* - quizzes: (array) Quiz attempts with grades and attempt times
* - forum_posts: (int) Count of user's forum posts in course
*
* @throws moodle_exception Throws exceptions for:
* - Invalid course/user
* - Missing permissions
* - Database errors
* - Enrollment issues
*
*/
public static function get_user_course_progress_parameters()
{
return new external_function_parameters([
'courseid' => new external_value(PARAM_INT, 'Course ID'),
'userid' => new external_value(PARAM_INT, 'User ID')
]);
}
public static function get_user_course_progress($courseid, $userid)
{
global $DB, $USER, $CFG;
// Validate parameters
$params = self::validate_parameters(self::get_user_course_progress_parameters(), [
'courseid' => $courseid,
'userid' => $userid
]);
// Get and validate context
$context = context_course::instance($params['courseid']);
self::validate_context($context);
// Check capabilities
if ($USER->id != $params['userid']) {
require_capability('moodle/user:viewdetails', $context);
require_capability('report/progress:view', $context);
}
require_capability('moodle/course:view', $context);
// Verify user exists and is enrolled
if (!$DB->record_exists_select('user', 'id = ? AND deleted = 0', [$params['userid']])) {
throw new moodle_exception('invaliduser', 'error');
}
if (!is_enrolled($context, $params['userid'])) {
throw new moodle_exception('usernotenrolled', 'enrol');
}
// Get course
$course = $DB->get_record('course', ['id' => $params['courseid']], '*', MUST_EXIST);
// Initialize result array
$result = [
'course_id' => $params['courseid'],
'user_id' => $params['userid'],
'status' => 'Not Started',
'completion_percentage' => 0,
'viewed_pages' => [],
'quizzes' => [],
'forum_posts' => 0
];
try {
$completion = new completion_info($course);
// Only calculate completion if tracking is enabled
if ($completion->is_enabled()) {
$result['status'] = $completion->is_course_complete($params['userid'])
? 'Completed'
: 'In Progress';
// Get all activities with completion tracking
$activities = $completion->get_activities();
$total = count($activities);
$completed = 0;
$result['activity_details'] = []; // New array for detailed activity status
if ($total > 0) {
foreach ($activities as $activity) {
$activitydata = $completion->get_data($activity, false, $params['userid']);
// Determine activity completion state
$state = 'Not Started';
if ($activitydata->completionstate == COMPLETION_COMPLETE) {
$state = 'Completed';
$completed++;
} elseif ($activitydata->completionstate == COMPLETION_COMPLETE_PASS) {
$state = 'Completed (Passed)';
$completed++;
} elseif ($activitydata->completionstate == COMPLETION_INCOMPLETE) {
$state = 'In Progress';
}
// Add activity details to the report
$result['activity_details'][] = [
'id' => $activity->id,
'name' => $activity->name,
'type' => $activity->modname,
'state' => $state,
'timecompleted' => $activitydata->timemodified > 0
? date('Y-m-d H:i:s', $activitydata->timemodified)
: null
];
}
// Calculate overall completion percentage
$result['completion_percentage'] = round(($completed / $total) * 100);
$result['activities_completed'] = $completed;
$result['activities_total'] = $total;
}
}
// Get viewed pages (limited to 50 most recent)
$viewedpages = $DB->get_records_sql(
"SELECT cm.id, p.name, MAX(l.timecreated) as lastviewed
FROM {logstore_standard_log} l
JOIN {course_modules} cm ON l.contextinstanceid = cm.id
JOIN {modules} m ON cm.module = m.id
JOIN {page} p ON cm.instance = p.id
WHERE l.userid = ? AND l.courseid = ?
AND l.action IN ('viewed', 'downloaded', 'played') AND l.target = 'course_module'
AND m.name = 'page'
GROUP BY cm.id, p.name
ORDER BY lastviewed DESC
LIMIT 50",
[$params['userid'], $params['courseid']]
);
foreach ($viewedpages as $page) {
$result['viewed_pages'][] = [
'title' => $page->name,
'last_viewed' => date('c', $page->lastviewed)
];
}
// Get quiz attempts (best attempt per quiz)
$quizattempts = $DB->get_records_sql(
"SELECT q.id, q.name, q.grade as maxgrade,
qa.id as attemptid, qa.sumgrades, qa.timemodified
FROM {quiz} q
JOIN {quiz_attempts} qa ON qa.quiz = q.id
WHERE q.course = ? AND qa.userid = ?
AND qa.state IN ('finished', 'abandoned') AND qa.sumgrades IS NOT NULL
ORDER BY qa.sumgrades DESC",
[$params['courseid'], $params['userid']]
);
// Keep only the best attempt for each quiz
$processedquizzes = [];
foreach ($quizattempts as $quiz) {
if (!isset($processedquizzes[$quiz->id])) {
$percentage = $quiz->maxgrade > 0 ? ($quiz->sumgrades / $quiz->maxgrade) * 100 : 0;
$result['quizzes'][] = [
'title' => $quiz->name,
'grade' => round($percentage, 2),
'attempt_time' => date('c', $quiz->timemodified)
];
$processedquizzes[$quiz->id] = true;
}
}
// Count forum posts (excluding deleted ones)
$result['forum_posts'] = $DB->count_records_sql(
"SELECT COUNT(fp.id)
FROM {forum_posts} fp
JOIN {forum_discussions} fd ON fp.discussion = fd.id
WHERE fp.userid = ? AND fd.course = ?
AND fp.deleted = 0",
[$params['userid'], $params['courseid']]
);
} catch (Exception $e) {
throw new moodle_exception('errorprocessingrequest', 'course', '', $e->getMessage());
}
return $result;
}
public static function get_user_course_progress_returns()
{
return new external_single_structure([
'course_id' => new external_value(PARAM_INT, 'Course ID'),
'user_id' => new external_value(PARAM_INT, 'User ID'),
'status' => new external_value(PARAM_TEXT, 'Course status'),
'completion_percentage' => new external_value(PARAM_INT, 'Completion percentage'),
'viewed_pages' => new external_multiple_structure(
new external_single_structure([
'title' => new external_value(PARAM_TEXT, 'Page title'),
'last_viewed' => new external_value(PARAM_TEXT, 'Last viewed timestamp'),
]),
'List of viewed pages',
VALUE_OPTIONAL
),
'quizzes' => new external_multiple_structure(
new external_single_structure([
'title' => new external_value(PARAM_TEXT, 'Quiz name'),
'grade' => new external_value(PARAM_FLOAT, 'Grade percentage'),
'attempt_time' => new external_value(PARAM_TEXT, 'Time of attempt'),
]),
'List of quiz attempts',
VALUE_OPTIONAL
),
'forum_posts' => new external_value(PARAM_INT, 'Total forum posts by user')
]);
}
//******************************************** Output ********************************************************* */
// {
// "course_id": 123,
// "user_id": 456,
// "status": "In Progress",
// "activities_completed": 13,
// "activities_total": 20,
// "activity_details": [
// {
// "id": 123,
// "name": "Introduction to Course",
// "type": "page",
// "state": "Completed",
// "timecompleted": "2023-10-15 14:30:22"
// },
// {
// "id": 124,
// "name": "Week 1 Quiz",
// "type": "quiz",
// "state": "Completed (Passed)",
// "timecompleted": "2023-10-16 09:15:10"
// },
// {
// "id": 125,
// "name": "Discussion Forum",
// "type": "forum",
// "state": "Not Started",
// "timecompleted": null
// }
// ],
// "viewed_pages": [
// {
// "title": "Course Introduction",
// "last_viewed": "2023-10-15T14:30:22+00:00"
// },
// {
// "title": "Week 1 Materials",
// "last_viewed": "2023-10-14T09:15:10+00:00"
// }
// ],
// "quizzes": [
// {
// "title": "Week 1 Quiz",
// "grade": 85.5,
// "attempt_time": "2023-10-14T10:30:45+00:00"
// },
// {
// "title": "Midterm Quiz",
// "grade": 72.0,
// "attempt_time": "2023-10-08T15:22:33+00:00"
// }
// ],
// "forum_posts": 7
// }
//***************************************************************************************************** */
/** **************************************************************************************************************** */
/**
* Api-2 : local_courseprogress_get_user_course_progress
*
*/
public static function get_user_all_courses_progress_parameters()
{
return new external_function_parameters([
'userid' => new external_value(PARAM_INT, 'User ID'),
'limit' => new external_value(PARAM_INT, 'Maximum courses to return', VALUE_DEFAULT, 50),
'offset' => new external_value(PARAM_INT, 'Number of courses to skip', VALUE_DEFAULT, 0),
'detailed' => new external_value(PARAM_BOOL, 'Return activity details', VALUE_DEFAULT, false)
]);
}
public static function get_user_all_courses_progress($userid, $limit = 50, $offset = 0, $detailed = false)
{
global $DB, $USER, $CFG;
// Validate parameters
$params = self::validate_parameters(self::get_user_all_courses_progress_parameters(), [
'userid' => $userid,
'limit' => $limit,
'offset' => $offset,
'detailed' => $detailed
]);
// Validate context and permissions
$context = context_user::instance($params['userid']);
self::validate_context($context);
if ($USER->id != $params['userid']) {
require_capability('moodle/user:viewdetails', $context);
}
// Get enrolled courses (with visibility check)
$courses = enrol_get_users_courses($params['userid'], true, 'id, fullname, visible, enablecompletion');
$result = [];
$completions = $DB->get_records('course_completions', ['userid' => $params['userid']]);
foreach (array_slice($courses, $params['offset'], $params['limit']) as $course) {
if (!$course->visible && !has_capability('moodle/course:viewhiddencourses', context_course::instance($course->id))) {
continue;
}
try {
$completion = new completion_info($course);
$courseprogress = [
'course_id' => $course->id,
'course_name' => format_string($course->fullname),
'status' => 'Not Started',
'completion_percentage' => 0,
'activities_total' => 0,
'activities_completed' => 0
];
if ($completion->is_enabled()) {
$courseprogress['status'] = isset($completions[$course->id]) && $completions[$course->id]->status == COMPLETION_STATUS_COMPLETE
? 'Completed'
: 'In Progress';
$activities = $completion->get_activities();
$total = count($activities);
$completed = 0;
$activities_details = [];
foreach ($activities as $activity) {
$activitydata = $completion->get_data($activity, false, $params['userid']);
// $state = self::get_activity_state($activitydata->completionstate);
$completionstate = $activitydata->completionstate;
$state = is_null($completionstate)
? 'Not Started'
: self::get_activity_state($completionstate);
if (in_array($activitydata->completionstate, [COMPLETION_COMPLETE, COMPLETION_COMPLETE_PASS])) {
$completed++;
}
if ($params['detailed']) {
$activities_details[] = [
'id' => $activity->id,
'cmid' => $activity->coursemodule,
'name' => format_string($activity->name),
'modname' => $activity->modname,
'state' => $state,
'timecompleted' => $activitydata->timemodified > 0
? date('c', $activitydata->timemodified)
: null
];
}
}
$courseprogress['activities_total'] = $total;
$courseprogress['activities_completed'] = $completed;
$courseprogress['completion_percentage'] = $total > 0
? round(($completed / $total) * 100)
: 0;
if ($params['detailed']) {
$courseprogress['activities'] = $activities_details;
}
}
$result[] = $courseprogress;
} catch (Exception $e) {
$result[] = [
'course_id' => $course->id,
'error' => get_string('errorloadingcourse', 'completion')
];
continue;
}
}
return $result;
}
private static function get_activity_state(int $state): string
{
return match ($state) {
COMPLETION_COMPLETE => 'Completed',
COMPLETION_COMPLETE_PASS => 'Completed (Passed)',
COMPLETION_INCOMPLETE => 'In Progress',
default => 'Not Started'
};
}
public static function get_user_all_courses_progress_returns()
{
$base_structure = [
'course_id' => new external_value(PARAM_INT, 'Course ID'),
'course_name' => new external_value(PARAM_TEXT, 'Course name'),
'status' => new external_value(PARAM_TEXT, 'Course status'),
'completion_percentage' => new external_value(PARAM_INT, 'Completion percentage'),
'activities_total' => new external_value(PARAM_INT, 'Total activities'),
'activities_completed' => new external_value(PARAM_INT, 'Completed activities'),
];
$detailed_structure = new external_single_structure(array_merge(
$base_structure,
[
'activities' => new external_multiple_structure(
new external_single_structure([
'id' => new external_value(PARAM_INT, 'Activity ID'),
'cmid' => new external_value(PARAM_INT, 'Course module ID'),
'name' => new external_value(PARAM_TEXT, 'Activity name'),
'modname' => new external_value(PARAM_TEXT, 'Module type'),
'state' => new external_value(PARAM_TEXT, 'Completion state'),
'timecompleted' => new external_value(PARAM_RAW, 'Completion time', VALUE_OPTIONAL)
]),
'Activity details',
VALUE_OPTIONAL
)
]
));
return new external_multiple_structure(
$detailed_structure,
'List of courses with progress data'
);
}
//******************************************* Output ********************************************************** */
// [
// {
// "course_id": 123,
// "course_name": "Advanced Mathematics",
// "status": "In Progress",
// "completion_percentage": 65,
// "activities_total": 20,
// "activities_completed": 13,
// "activities": [
// {
// "id": 101,
// "cmid": 202,
// "name": "Week 1 Quiz",
// "modname": "quiz",
// "state": "Completed (Passed)",
// "timecompleted": "2023-10-15T14:30:00+00:00"
// }
// ]
// }
// ]
//***************************************************************************************************** */
/** **************************************************************************************************************** */
// =======================================================
// # Api-3 : local_courseprogress_get_course_progress_details
// =======================================================
/**
* Get detailed course progress for users
*
* @param int $courseid The course ID
* @param int $userid (Optional) Specific user ID
* @return array Progress data
*/
public static function get_course_progress_details_parameters()
{
return new external_function_parameters([
'courseid' => new external_value(PARAM_INT, 'Course ID'),
'userid' => new external_value(PARAM_INT, 'User ID', VALUE_DEFAULT, 0),
'page' => new external_value(PARAM_INT, 'Page number', VALUE_DEFAULT, 0),
'perpage' => new external_value(PARAM_INT, 'Items per page', VALUE_DEFAULT, 10),
'activitytype' => new external_value(PARAM_ALPHA, 'Filter by activity type (quiz|assign)', VALUE_DEFAULT, '')
]);
}
public static function get_course_progress_details($courseid, $userid = 0, $page = 0, $perpage = 10, $activitytype = '')
{
global $DB, $USER;
// Validate parameters
$params = self::validate_parameters(self::get_course_progress_details_parameters(), [
'courseid' => $courseid,
'userid' => $userid ?: $USER->id,
'page' => $page,
'perpage' => $perpage,
'activitytype' => $activitytype
]);
// Context and capability checks
$coursecontext = context_course::instance($params['courseid']);
self::validate_context($coursecontext);
if ($params['userid'] != $USER->id) {
require_capability('moodle/grade:viewall', $coursecontext);
require_capability('report/progress:view', $coursecontext);
}
// Get user and course
$user = $DB->get_record('user', ['id' => $params['userid'], 'deleted' => 0], '*', MUST_EXIST);
$course = get_course($params['courseid']);
// Base result structure
$result = [
'user' => [
'id' => $user->id,
'fullname' => fullname($user),
'email' => $user->email
],
'course' => [
'id' => $course->id,
'name' => format_string($course->fullname)
],
'pagination' => [
'page' => $params['page'],
'perpage' => $params['perpage'],
'totalitems' => 0
],
'items' => []
];
// Get grade items with pagination
$gradereport = new grade_report_user($params['courseid'], $params['userid']);
$gradedata = $gradereport->load_final_grades();
// Filter and count items first
$filtereditems = [];
foreach ($gradedata as $itemid => $gradeitem) {
if ($gradeitem->itemtype == 'mod') {
if ($params['activitytype'] && $gradeitem->itemmodule != $params['activitytype']) {
continue;
}
$filtereditems[] = $gradeitem;
}
}
// Apply pagination
$result['pagination']['totalitems'] = count($filtereditems);
$paginateditems = array_slice($filtereditems, $params['page'] * $params['perpage'], $params['perpage']);
// Process paginated items
foreach ($paginateditems as $gradeitem) {
$module = $DB->get_record($gradeitem->itemmodule, ['id' => $gradeitem->iteminstance]);
$itemdata = [
'type' => $gradeitem->itemmodule,
'id' => $gradeitem->iteminstance,
'name' => format_string($gradeitem->itemname),
'grade' => $gradeitem->finalgrade,
'maxgrade' => $gradeitem->grademax,
'percentage' => $gradeitem->grademax > 0
? round(($gradeitem->finalgrade / $gradeitem->grademax) * 100, 2)
: null
];
// Add type-specific data
switch ($gradeitem->itemmodule) {
case 'quiz':
$attempt = $DB->get_record_sql(
"SELECT id, sumgrades, timemodified
FROM {quiz_attempts}
WHERE quiz = ? AND userid = ? AND state = 'finished'
ORDER BY sumgrades DESC LIMIT 1",
[$gradeitem->iteminstance, $params['userid']]
);
if ($attempt) {
$itemdata['attempt'] = [
'id' => $attempt->id,
'date' => date('c', $attempt->timemodified)
];
}
break;
case 'assign':
$submission = $DB->get_record('assign_submission', [
'assignment' => $gradeitem->iteminstance,
'userid' => $params['userid'],
'status' => 'submitted'
], 'id, timemodified');
if ($submission) {
$itemdata['submission'] = [
'id' => $submission->id,
'date' => date('c', $submission->timemodified)
];
}
break;
}
$result['items'][] = $itemdata;
}
return $result;
}
public static function get_course_progress_details_returns()
{
return new external_single_structure([
'user' => new external_single_structure([
'id' => new external_value(PARAM_INT, 'User ID'),
'fullname' => new external_value(PARAM_TEXT, 'Full name'),
'email' => new external_value(PARAM_TEXT, 'Email')
]),
'course' => new external_single_structure([
'id' => new external_value(PARAM_INT, 'Course ID'),
'name' => new external_value(PARAM_TEXT, 'Course name')
]),
'pagination' => new external_single_structure([
'page' => new external_value(PARAM_INT, 'Current page'),
'perpage' => new external_value(PARAM_INT, 'Items per page'),
'totalitems' => new external_value(PARAM_INT, 'Total items available')
]),
'items' => new external_multiple_structure(
new external_single_structure([
'type' => new external_value(PARAM_ALPHA, 'Activity type'),
'id' => new external_value(PARAM_INT, 'Activity ID'),
'name' => new external_value(PARAM_TEXT, 'Activity name'),
'grade' => new external_value(PARAM_FLOAT, 'Grade received', VALUE_OPTIONAL),
'maxgrade' => new external_value(PARAM_FLOAT, 'Maximum grade'),
'percentage' => new external_value(PARAM_FLOAT, 'Percentage score', VALUE_OPTIONAL),
'attempt' => new external_single_structure([
'id' => new external_value(PARAM_INT, 'Attempt ID'),
'date' => new external_value(PARAM_RAW, 'Attempt timestamp')
], 'Quiz attempt', VALUE_OPTIONAL),
'submission' => new external_single_structure([
'id' => new external_value(PARAM_INT, 'Submission ID'),
'date' => new external_value(PARAM_RAW, 'Submission timestamp')
], 'Assignment submission', VALUE_OPTIONAL)
]),
'Paginated activity items'
)
]);
}
//***************************************************************************************************** */
// {
// "user": {
// "id": 123,
// "fullname": "John Doe",
// "email": "john@example.com"
// },
// "course": {
// "id": 456,
// "name": "Advanced Mathematics"
// },
// "pagination": {
// "page": 0,
// "perpage": 2,
// "totalitems": 5
// },
// "items": [
// {
// "type": "quiz",
// "id": 789,
// "name": "Midterm Exam",
// "grade": 85,
// "maxgrade": 100,
// "percentage": 85,
// "attempt": {
// "id": 321,
// "date": "2023-10-15T14:30:00+00:00"
// }
// },
// {
// "type": "assign",
// "id": 654,
// "name": "Week 1 Assignment",
// "grade": 90,
// "maxgrade": 100,
// "percentage": 90,
// "submission": {
// "id": 987,
// "date": "2023-10-10T09:15:00+00:00"
// }
// }
// ]
// }
//***************************************************************************************************** */
/** **************************************************************************************************************** */
// =======================================================
// # Api-4 : local_courseprogress_get_course_progress_details
// =======================================================
/**
* Get user engagement data for a specific user in a course
*
* @param int $courseid Course ID
* @param int $userid User ID
* @return array User engagement data
*/
public static function get_user_engagement_parameters()
{
return new external_function_parameters([
'courseid' => new external_value(PARAM_INT, 'Course ID'),
'userid' => new external_value(PARAM_INT, 'User ID'),
]);
}
public static function get_user_engagement($courseid, $userid)
{
global $DB, $USER;
// Parameter validation
$params = self::validate_parameters(self::get_user_engagement_parameters(), [
'courseid' => $courseid,
'userid' => $userid,
]);
$courseid = $params['courseid'];
$userid = $params['userid'];
// Context validation
$coursecontext = context_course::instance($courseid);
self::validate_context($coursecontext);
// Security checks
require_capability('moodle/course:viewparticipants', $coursecontext);
if ($userid != $USER->id) {
require_capability('report/completion:view', $coursecontext);
}
// Check user is enrolled in the course
if (!is_enrolled($coursecontext, $userid)) {
throw new moodle_exception('usernotenrolled', 'error');
}
// Get basic user and course data
$user = $DB->get_record('user', ['id' => $userid], 'id, firstname, lastname, email', MUST_EXIST);
$course = $DB->get_record('course', ['id' => $courseid], 'id, fullname', MUST_EXIST);
// Get completion percentage
$completion = $DB->get_record('course_completions', [
'userid' => $userid,
'course' => $courseid
]);
$completionPercentage = 0;
if ($completion) {
$totalActivities = $DB->count_records_sql(
"SELECT COUNT(*) FROM {course_modules}
WHERE course = :courseid AND completion > 0",
['courseid' => $courseid]
);
$completedActivities = $DB->count_records_sql(
"SELECT COUNT(*) FROM {course_modules_completion} cmc
JOIN {course_modules} cm ON cm.id = cmc.coursemoduleid
WHERE cm.course = :courseid AND cmc.userid = :userid AND cmc.completionstate > 0",
['courseid' => $courseid, 'userid' => $userid]
);
$completionPercentage = $totalActivities > 0 ?
round(($completedActivities / $totalActivities) * 100, 2) : 0;
}
// Get last course access
$lastaccess = $DB->get_field('user_lastaccess', 'timeaccess', [
'userid' => $userid,
'courseid' => $courseid
]);
// Get all course modules with section info in course order
$modules = $DB->get_records_sql(
"SELECT cm.id as cmid, m.name as modname, cm.section, s.name as sectionname, cm.instance, cm.visible
FROM {course_modules} cm
JOIN {modules} m ON cm.module = m.id
JOIN {course_sections} s ON cm.section = s.id
WHERE cm.course = :courseid
ORDER BY cm.section, cm.added, cm.id",
['courseid' => $courseid]
);
$contentStatus = [];
foreach ($modules as $module) {
$activity = null;
$status = '';
$lastaccess = '';
switch ($module->modname) {
case 'quiz':
$activity = $DB->get_record('quiz', ['id' => $module->instance], 'id, name');
if ($activity) {
$attempt = $DB->get_record(
'quiz_attempts',
['quiz' => $activity->id, 'userid' => $userid],
'state, timefinish',
IGNORE_MULTIPLE
);
$status = $attempt ? $attempt->state : 'not_attempted';
$lastaccess = $attempt && $attempt->timefinish ? userdate($attempt->timefinish) : '';
}
break;
case 'assign':
$activity = $DB->get_record('assign', ['id' => $module->instance], 'id, name');
if ($activity) {
$submission = $DB->get_record(
'assign_submission',
['assignment' => $activity->id, 'userid' => $userid],
'status, timemodified',
IGNORE_MULTIPLE
);
$status = $submission ? $submission->status : 'not_submitted';
$lastaccess = $submission && $submission->timemodified ? userdate($submission->timemodified) : '';
}
break;
case 'forum':
$activity = $DB->get_record('forum', ['id' => $module->instance], 'id, name');
if ($activity) {
$sql = "SELECT COUNT(fp.id) as posts, MAX(fp.created) as last_post
FROM {forum_discussions} fd
JOIN {forum_posts} fp ON fd.id = fp.discussion
WHERE fd.forum = :forumid AND fp.userid = :userid";
$result = $DB->get_record_sql($sql, ['forumid' => $activity->id, 'userid' => $userid]);
$status = $result && $result->posts > 0 ? 'posted' : 'not_posted';
$lastaccess = $result && $result->last_post ? userdate($result->last_post) : '';
}
break;
case 'resource':
$activity = $DB->get_record('resource', ['id' => $module->instance], 'id, name');
if ($activity) {
$log = $DB->get_record(
'logstore_standard_log',
[
'contextinstanceid' => $module->cmid,
'userid' => $userid,
'contextlevel' => CONTEXT_MODULE,
'action' => 'viewed'
],
'timecreated',
IGNORE_MULTIPLE
);
$status = $log ? 'viewed' : 'not_viewed';
$lastaccess = $log ? userdate($log->timecreated) : '';
}
break;
// Add other module types as needed
}
if ($activity) {
$contentStatus[] = [
'module_name' => $module->modname,
'activity_name' => $activity->name,
'section' => $module->sectionname ?: 'Section ' . $module->section,
'status' => $status,
'last_access' => $lastaccess,
'visible' => $module->visible
];
}
}
// Build the response
$response = [
'username' => fullname($user),
'email' => $user->email,
'course_name' => $course->fullname,
'completion_percentage' => $completionPercentage,
'content_status' => $contentStatus,
'last_course_access' => $lastaccess ? userdate($lastaccess) : ''
];
return ['status' => 'success', 'data' => [$response]];
}
public static function get_user_engagement_returns()
{
return new external_single_structure([
'status' => new external_value(PARAM_TEXT, 'Status of the response'),
'data' => new external_multiple_structure(
new external_single_structure([
'username' => new external_value(PARAM_TEXT, 'Full name of the user'),
'email' => new external_value(PARAM_EMAIL, 'User email'),
'course_name' => new external_value(PARAM_TEXT, 'Course name'),
'completion_percentage' => new external_value(PARAM_FLOAT, 'Completion percentage'),
'content_status' => new external_multiple_structure(
new external_single_structure([
'module_name' => new external_value(PARAM_TEXT, 'Module type'),
'activity_name' => new external_value(PARAM_TEXT, 'Activity name'), // Changed from 'name' to 'activity_name'
'section' => new external_value(PARAM_TEXT, 'Section name'), // Added section field
'status' => new external_value(PARAM_TEXT, 'Status of activity'),
'last_access' => new external_value(PARAM_TEXT, 'Last accessed date'),
'visible' => new external_value(PARAM_INT, 'Visibility status (1=visible, 0=hidden)') // Added visible field
])
),
'last_course_access' => new external_value(PARAM_TEXT, 'Last course access timestamp')
])
),
]);
}
// ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Course Engagement Start +++++++++++++++++++++++++++++++++++++++++++++++++
public static function get_course_engagement_filter_parameters()
{
return new external_function_parameters([
'courseid' => new external_value(PARAM_INT, 'Course ID', VALUE_REQUIRED),
'limit' => new external_value(PARAM_INT, 'Number of users per page', VALUE_DEFAULT, 20),
'offset' => new external_value(PARAM_INT, 'Offset for pagination', VALUE_DEFAULT, 0),
'userid' => new external_value(PARAM_INT, 'Specific user ID (0 for all)', VALUE_DEFAULT, 0)
]);
}
public static function get_course_engagement_filter($courseid, $limit = 0, $offset = 0, $userid = 0)
{
global $DB, $USER;
// Parameter validation
$params = self::validate_parameters(self::get_course_engagement_filter_parameters(), [
'courseid' => $courseid,
'limit' => $limit,
'offset' => $offset,
'userid' => $userid
]);
$courseid = $params['courseid'];
$limit = $params['limit'];
$offset = $params['offset'];
$userid = $params['userid'];
// Context validation
$coursecontext = context_course::instance($courseid);
self::validate_context($coursecontext);
// Security checks
require_capability('report/completion:view', $coursecontext);
// Get course data
$course = $DB->get_record('course', ['id' => $courseid], 'id, fullname', MUST_EXIST);
// Get enrolled users (either specific user or paginated list)
if ($userid) {
$enrolledusers = [$DB->get_record('user', ['id' => $userid], 'id, firstname, lastname, email', MUST_EXIST)];
$totalusers = 1;
} else {
$enrolledusers = get_enrolled_users(
$coursecontext,
'',
0,
'u.id, u.firstname, u.lastname, u.email',
null,
$offset,
$limit
);
$totalusers = count_enrolled_users($coursecontext);
}
// Get all course modules with section info (we'll use this for all users)
$modules = $DB->get_records_sql(
"SELECT cm.id as cmid, m.name as modname, cm.section, s.name as sectionname,
cm.instance, cm.visible, cm.module
FROM {course_modules} cm
JOIN {modules} m ON cm.module = m.id
JOIN {course_sections} s ON cm.section = s.id
WHERE cm.course = :courseid
ORDER BY cm.section, cm.added, cm.id",
['courseid' => $courseid]
);
// Prepare response array
$response = [
'course_name' => $course->fullname,
'total_users' => $totalusers,
'users' => [],
'next_offset' => ($userid || ($offset + $limit) >= $totalusers) ? null : ($offset + $limit)
];
foreach ($enrolledusers as $user) {
$userengagement = [
'userid' => $user->id,
'username' => fullname($user),
'email' => $user->email,
'completion_percentage' => 0,
'last_course_access' => $DB->get_field(
'user_lastaccess',
'timeaccess',
['userid' => $user->id, 'courseid' => $courseid]
),
'content_status' => []
];
// Calculate completion percentage
$totalActivities = $DB->count_records(
'course_modules',
['course' => $courseid, 'completion' => COMPLETION_TRACKING_AUTOMATIC]
);
$completedActivities = $DB->count_records_sql(
"SELECT COUNT(*) FROM {course_modules_completion} cmc
JOIN {course_modules} cm ON cm.id = cmc.coursemoduleid
WHERE cm.course = :courseid AND cmc.userid = :userid AND cmc.completionstate > 0",
['courseid' => $courseid, 'userid' => $user->id]
);
$userengagement['completion_percentage'] = $totalActivities > 0 ?
round(($completedActivities / $totalActivities) * 100, 2) : 0;
// Get detailed activity status for this user
foreach ($modules as $module) {
$activity = null;
$status = '';
$lastaccess = '';
switch ($module->modname) {
case 'quiz':
$activity = $DB->get_record('quiz', ['id' => $module->instance], 'id, name');
if ($activity) {
$attempt = $DB->get_record(
'quiz_attempts',
['quiz' => $activity->id, 'userid' => $user->id],
'state, timefinish',
IGNORE_MULTIPLE
);
$status = $attempt ? $attempt->state : 'not_attempted';
$lastaccess = $attempt && $attempt->timefinish ? $attempt->timefinish : 0;
}
break;
case 'assign':
$activity = $DB->get_record('assign', ['id' => $module->instance], 'id, name');
if ($activity) {
$submission = $DB->get_record(
'assign_submission',
['assignment' => $activity->id, 'userid' => $user->id],
'status, timemodified',
IGNORE_MULTIPLE
);
$status = $submission ? $submission->status : 'not_submitted';
$lastaccess = $submission && $submission->timemodified ? $submission->timemodified : 0;
}
break;
// Add other module types as needed
}
if ($activity) {
$userengagement['content_status'][] = [
'module_name' => $module->modname,
'activity_name' => $activity->name,
'section' => $module->sectionname ?: 'Section ' . $module->section,
'status' => $status,
'last_access' => $lastaccess ? userdate($lastaccess) : '',
'visible' => $module->visible
];
}
}
$response['users'][] = $userengagement;
}
return ['status' => 'success', 'data' => $response];
}
public static function get_course_engagement_filter_returns()
{
return new external_single_structure([
'status' => new external_value(PARAM_TEXT, 'Status of the response'),
'data' => new external_single_structure([
'course_name' => new external_value(PARAM_TEXT, 'Course name'),
'total_users' => new external_value(PARAM_INT, 'Total number of enrolled users'),
'next_offset' => new external_value(PARAM_INT, 'Next offset for pagination', VALUE_OPTIONAL),
'users' => new external_multiple_structure(
new external_single_structure([
'userid' => new external_value(PARAM_INT, 'User ID'),
'username' => new external_value(PARAM_TEXT, 'Full name of the user'),
'email' => new external_value(PARAM_EMAIL, 'User email'),
'completion_percentage' => new external_value(PARAM_FLOAT, 'Completion percentage'),
'last_course_access' => new external_value(PARAM_TEXT, 'Last course access timestamp'),
'content_status' => new external_multiple_structure(
new external_single_structure([
'module_name' => new external_value(PARAM_TEXT, 'Module type'),
'activity_name' => new external_value(PARAM_TEXT, 'Activity name'),
'section' => new external_value(PARAM_TEXT, 'Section name'),
'status' => new external_value(PARAM_TEXT, 'Status of activity'),
'last_access' => new external_value(PARAM_TEXT, 'Last accessed date'),
'visible' => new external_value(PARAM_INT, 'Visibility status (1=visible, 0=hidden)')
])
)
])
)
])
]);
}
// ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Course Engagement end +++++++++++++++++++++++++++++++++++++
/** **************************************************************************************************************** */
// =======================================================
// # Api-5 : local_get_user_engagement_access
// =======================================================
/**
* Get detailed user engagement access data for a course
*
* @param int $courseid Course ID
* @param int $userid User ID
* @return array User engagement access data
*/
public static function get_user_engagement_access_parameters()
{
return new external_function_parameters([
'courseid' => new external_value(PARAM_INT, 'Course ID'),
'userid' => new external_value(PARAM_INT, 'User ID'),
]);
}
/**
* Get detailed user engagement access data for all activities in a course
*/
public static function get_user_engagement_access($courseid, $userid)
{
global $DB, $USER;
// Parameter validation - properly using the parameters passed directly to the function
$params = self::validate_parameters(self::get_user_engagement_access_parameters(), [
'courseid' => $courseid,
'userid' => $userid,
]);
$courseid = $params['courseid'];
$userid = $params['userid'];
// Context validation
$coursecontext = context_course::instance($courseid);
self::validate_context($coursecontext);
// Security checks
require_capability('moodle/course:viewparticipants', $coursecontext);
if ($userid != $USER->id) {
require_capability('report/completion:view', $coursecontext);
}
// Check user is enrolled in the course
if (!is_enrolled($coursecontext, $userid)) {
throw new moodle_exception('usernotenrolled', 'error');
}
// Get user info
$user = $DB->get_record('user', ['id' => $userid], 'id, firstname, lastname, email', MUST_EXIST);
// Get course info
$course = $DB->get_record('course', ['id' => $courseid], 'id, fullname', MUST_EXIST);
// Get section names for better organization
$sections = $DB->get_records('course_sections', ['course' => $courseid], 'section', 'id, section, name');
// Get course modules info (modules in this course)
$modules = $DB->get_records_sql("
SELECT
cm.id AS cmid,
m.name AS module_name,
cm.instance,
cm.section,
cm.visible,
cm.completion,
cmc.completionstate
FROM {course_modules} cm
JOIN {modules} m ON cm.module = m.id
LEFT JOIN {course_modules_completion} cmc ON cm.id = cmc.coursemoduleid AND cmc.userid = :userid
WHERE cm.course = :courseid
ORDER BY cm.section, cm.id
", ['courseid' => $courseid, 'userid' => $userid]);
// Get activity names in bulk for better performance
$moduleTypes = ['quiz', 'assign', 'forum', 'resource', 'page', 'book', 'url', 'lesson', 'feedback', 'scorm'];
$activityNames = [];
foreach ($moduleTypes as $type) {
$instances = $DB->get_records_sql("
SELECT cm.instance, m.name
FROM {course_modules} cm
JOIN {modules} m ON cm.module = m.id
WHERE cm.course = :courseid AND m.name = :module
", ['courseid' => $courseid, 'module' => $type]);
if (!empty($instances)) {
$instanceIds = array_column($instances, 'instance');
if (!empty($instanceIds)) {
try {
$activityRecords = $DB->get_records_list($type, 'id', $instanceIds, '', 'id, name');
foreach ($activityRecords as $record) {
$activityNames[$type][$record->id] = $record->name;
}
} catch (dml_exception $e) {
// Table might not exist, just continue
continue;
}
}
}
}
// Get resource views in bulk for better performance
$views = $DB->get_records_sql("
SELECT contextinstanceid as cmid, MAX(timecreated) AS lastaccess
FROM {logstore_standard_log}
WHERE userid = :userid
AND contextlevel = 70
AND action = 'viewed'
AND courseid = :courseid
AND eventname = '\\core\\event\\course_module_viewed'
GROUP BY contextinstanceid
", ['userid' => $userid, 'courseid' => $courseid]);
// Get quiz attempts in bulk
$quizAttempts = $DB->get_records_sql("
SELECT q.id as quizid, MAX(qa.timefinish) AS lastattempt, qa.state, COUNT(qa.id) as attempts_count
FROM {quiz} q
JOIN {quiz_attempts} qa ON q.id = qa.quiz AND qa.userid = :userid
JOIN {course_modules} cm ON cm.instance = q.id
JOIN {modules} m ON cm.module = m.id AND m.name = 'quiz'
WHERE cm.course = :courseid
GROUP BY q.id, qa.state
", ['userid' => $userid, 'courseid' => $courseid]);
// Get assignment submissions in bulk
$assignSubmissions = $DB->get_records_sql("
SELECT
a.id as assignid,
asb.status,
asb.timemodified,
ag.grade
FROM {assign} a
JOIN {course_modules} cm ON cm.instance = a.id
JOIN {modules} m ON cm.module = m.id AND m.name = 'assign'
LEFT JOIN {assign_submission} asb ON a.id = asb.assignment AND asb.userid = :userid1
LEFT JOIN {assign_grades} ag ON a.id = ag.assignment AND ag.userid = :userid2
WHERE cm.course = :courseid
", ['userid1' => $userid, 'userid2' => $userid, 'courseid' => $courseid]);
// Get forum posts in bulk
$forumPosts = $DB->get_records_sql("
SELECT
f.id as forumid,
COUNT(fp.id) as post_count,
MAX(fp.created) as lastpost
FROM {forum} f
JOIN {course_modules} cm ON cm.instance = f.id
JOIN {modules} m ON cm.module = m.id AND m.name = 'forum'
LEFT JOIN {forum_discussions} fd ON f.id = fd.forum
LEFT JOIN {forum_posts} fp ON fd.id = fp.discussion AND fp.userid = :userid
WHERE cm.course = :courseid
GROUP BY f.id
", ['userid' => $userid, 'courseid' => $courseid]);
$engagement = [];
foreach ($modules as $cm) {
$moduleData = [
'module_name' => $cm->module_name,
'cmid' => $cm->cmid,
'activity_name' => '',
'section' => $sections[$cm->section]->name ?? ('Section ' . $cm->section),
'section_number' => $cm->section,
'visible' => (bool)$cm->visible,
'last_access' => null,
'status' => 'not accessed',
'details' => []
];
// Get activity name from our pre-fetched data
if (isset($activityNames[$cm->module_name][$cm->instance])) {
$moduleData['activity_name'] = $activityNames[$cm->module_name][$cm->instance];
}
// Add completion status if module has completion enabled
if ($cm->completion > 0) {
$moduleData['details']['completion'] = [
'enabled' => true,
'state' => self::get_completion_state_name($cm->completionstate),
'completed' => !empty($cm->completionstate) && $cm->completionstate > 0
];
}
// Set view data from pre-fetched logs
if (isset($views[$cm->cmid])) {
$moduleData['last_access'] = userdate($views[$cm->cmid]->lastaccess);
$moduleData['status'] = 'viewed';
}
// Module-specific data
switch ($cm->module_name) {
case 'resource':
case 'page':
case 'book':
case 'url':
// These modules primarily track views, which we've already handled above
break;
case 'assign':
if (isset($assignSubmissions[$cm->instance])) {
$submission = $assignSubmissions[$cm->instance];
if ($submission->status === 'submitted') {
$moduleData['last_access'] = userdate($submission->timemodified);
$moduleData['status'] = 'submitted';
$moduleData['details']['submission'] = [
'status' => $submission->status,
'grade' => !is_null($submission->grade) ? (float)$submission->grade : null
];
}
}
break;
case 'quiz':
if (isset($quizAttempts[$cm->instance])) {
$attempt = $quizAttempts[$cm->instance];
$moduleData['last_access'] = userdate($attempt->lastattempt);
$moduleData['status'] = 'attempted';
$moduleData['details']['quiz'] = [
'attempts' => $attempt->attempts_count,
'state' => $attempt->state
];
}
break;
case 'forum':
if (isset($forumPosts[$cm->instance]) && $forumPosts[$cm->instance]->post_count > 0) {
$post = $forumPosts[$cm->instance];
$moduleData['last_access'] = userdate($post->lastpost);
$moduleData['status'] = 'posted';
$moduleData['details']['forum'] = [
'posts' => $post->post_count
];
}
break;
case 'scorm':
// For SCORM packages, check specific interactions
$scormData = $DB->get_record_sql("
SELECT MAX(timemodified) as last_access, value as status
FROM {scorm_scoes_track}
WHERE scormid = :scormid AND userid = :userid AND element = 'cmi.core.lesson_status'
", ['scormid' => $cm->instance, 'userid' => $userid]);
if ($scormData && $scormData->last_access) {
$moduleData['last_access'] = userdate($scormData->last_access);
$moduleData['status'] = $scormData->status ?? 'accessed';
$moduleData['details']['scorm'] = [
'status' => $scormData->status ?? 'incomplete'
];
}
break;
}
$engagement[] = $moduleData;
}
return [
'status' => 'success',
'user' => [
'id' => $user->id,
'fullname' => fullname($user),
'email' => $user->email
],
'course' => [
'id' => $course->id,
'fullname' => $course->fullname
],
'engagement' => $engagement
];
}
/**
* Convert completion state code to descriptive text
*/
private static function get_completion_state_name($state)
{
if (is_null($state)) {
return 'incomplete';
}
switch ($state) {
case COMPLETION_INCOMPLETE:
return 'incomplete';
case COMPLETION_COMPLETE:
return 'complete';
case COMPLETION_COMPLETE_PASS:
return 'complete_pass';
case COMPLETION_COMPLETE_FAIL:
return 'complete_fail';
default:
return 'unknown';
}
}
/**
* Define the structure of the function's return value
*/
public static function get_user_engagement_access_returns()
{
return new external_single_structure([
'status' => new external_value(PARAM_TEXT, 'Status of the response'),
'user' => new external_single_structure([
'id' => new external_value(PARAM_INT, 'User ID'),
'fullname' => new external_value(PARAM_TEXT, 'User full name'),
'email' => new external_value(PARAM_EMAIL, 'User email')
]),
'course' => new external_single_structure([
'id' => new external_value(PARAM_INT, 'Course ID'),
'fullname' => new external_value(PARAM_TEXT, 'Course full name')
]),
'engagement' => new external_multiple_structure(
new external_single_structure([
'module_name' => new external_value(PARAM_TEXT, 'Module type (quiz, assign, forum, resource, etc)'),
'cmid' => new external_value(PARAM_INT, 'Course module ID'),
'activity_name' => new external_value(PARAM_TEXT, 'Activity/resource name'),
'section' => new external_value(PARAM_TEXT, 'Section name'),
'section_number' => new external_value(PARAM_INT, 'Section number'),
'visible' => new external_value(PARAM_BOOL, 'Whether the module is visible'),
'last_access' => new external_value(PARAM_TEXT, 'Last access date/time', VALUE_OPTIONAL),
'status' => new external_value(PARAM_TEXT, 'User status on the activity (viewed, submitted, posted, attempted, not accessed)'),
'details' => new external_single_structure([
'completion' => new external_single_structure([
'enabled' => new external_value(PARAM_BOOL, 'Whether completion is enabled'),
'state' => new external_value(PARAM_TEXT, 'Completion state'),
'completed' => new external_value(PARAM_BOOL, 'Whether module is completed')
], 'Completion information', VALUE_OPTIONAL),
'submission' => new external_single_structure([
'status' => new external_value(PARAM_TEXT, 'Submission status'),
'grade' => new external_value(PARAM_FLOAT, 'Grade', VALUE_OPTIONAL)
], 'Assignment submission details', VALUE_OPTIONAL),
'quiz' => new external_single_structure([
'attempts' => new external_value(PARAM_INT, 'Number of attempts'),
'state' => new external_value(PARAM_TEXT, 'Last attempt state')
], 'Quiz attempt details', VALUE_OPTIONAL),
'forum' => new external_single_structure([
'posts' => new external_value(PARAM_INT, 'Number of posts')
], 'Forum participation details', VALUE_OPTIONAL),
'scorm' => new external_single_structure([
'status' => new external_value(PARAM_TEXT, 'SCORM package status')
], 'SCORM interaction details', VALUE_OPTIONAL)
], 'Additional details', VALUE_OPTIONAL)
])
)
]);
}
/** **************************************************************************************************************** */
// ==========================================================================================================
// # Api-6 : local_courseprogress_get_module_access
// =======================================================
/**
* Get module access data across users and courses
*
* @param int $courseid Course ID (0 for all courses)
* @param int $userid User ID (0 for all users)
* @param int $limit Maximum number of results (0 for no limit)
* @return array Module access statistics
*/
public static function get_module_access_parameters()
{
return new external_function_parameters([
'courseid' => new external_value(PARAM_INT, 'Course ID (0 for all courses)', VALUE_DEFAULT, 0),
'userid' => new external_value(PARAM_INT, 'User ID (0 for all users)', VALUE_DEFAULT, 0),
'limit' => new external_value(PARAM_INT, 'Maximum number of results (0 for no limit)', VALUE_DEFAULT, 0)
]);
}
/**
* Get module access data across users and courses
*
* @param int $courseid Course ID (0 for all courses)
* @param int $userid User ID (0 for all users)
* @param int $limit Maximum number of results (0 for no limit)
* @return array Module access statistics
*/
public static function get_module_access($courseid = 0, $userid = 0, $limit = 0)
{
global $DB, $USER;
// Validate parameters
$params = self::validate_parameters(self::get_module_access_parameters(), [
'courseid' => $courseid,
'userid' => $userid,
'limit' => $limit
]);
$courseid = $params['courseid'];
$userid = $params['userid'];
$limit = $params['limit'];
// Context validation
try {
if ($courseid > 0) {
$context = context_course::instance($courseid);
self::validate_context($context);
// Check course exists
$course = $DB->get_record('course', ['id' => $courseid], '*', MUST_EXIST);
// Capability check for course context
require_capability('moodle/course:viewparticipants', $context);
} else {
$context = context_system::instance();
self::validate_context($context);
// Capability check for system context - more restrictive
require_capability('report/log:view', $context);
}
} catch (moodle_exception $e) {
return [
'status' => 'error',
'message' => $e->getMessage(),
'data' => []
];
}
// Additional user access check
if ($userid > 0 && $USER->id != $userid) {
require_capability('moodle/user:viewdetails', $context);
// Check user exists
if (!$DB->record_exists('user', ['id' => $userid, 'deleted' => 0])) {
return [
'status' => 'error',
'message' => get_string('usernotexist', 'error'),
'data' => []
];
}
}
// Prepare query parameters
$queryparams = [];
$conditions = [];
// Base query
$sql = "SELECT
u.id AS userid,
u.firstname,
u.lastname,
c.id AS courseid,
c.fullname AS course_name,
cm.id AS moduleid,
cm.instance AS instanceid,
m.name AS module_type,
COUNT(l.id) AS access_count
FROM {logstore_standard_log} l
JOIN {user} u ON l.userid = u.id
JOIN {course} c ON l.courseid = c.id
JOIN {course_modules} cm ON l.contextinstanceid = cm.id AND l.contextlevel = 70 AND cm.course = l.courseid
JOIN {modules} m ON cm.module = m.id
WHERE l.action = 'viewed'
AND l.target = 'course_module'
AND u.deleted = 0
AND u.suspended = 0";
// Add filters with proper named conditions
if ($courseid > 0) {
$conditions[] = "c.id = :courseid";
$queryparams['courseid'] = $courseid;
}
if ($userid > 0) {
$conditions[] = "u.id = :userid";
$queryparams['userid'] = $userid;
}
// Apply conditional filters if any exist
if (!empty($conditions)) {
$sql .= " AND " . implode(" AND ", $conditions);
}
// Final group/order
$sql .= " GROUP BY u.id, c.id, cm.id, cm.instance, m.name
ORDER BY access_count DESC";
// Apply limit (safe because $limit is validated as PARAM_INT)
if ($limit > 0) {
$sql .= " LIMIT " . $limit;
}
// Execute query
try {
$records = $DB->get_records_sql($sql, $queryparams);
} catch (dml_exception $e) {
return [
'status' => 'error',
'message' => 'Database error: ' . $e->getMessage(),
'data' => []
];
}
// Process results if we have any
if (empty($records)) {
return [
'status' => 'success',
'message' => 'No records found',
'data' => []
];
}
// Optimize module name lookups by grouping them
$moduleTypes = [];
$instanceIDs = [];
foreach ($records as $record) {
if (!isset($moduleTypes[$record->module_type])) {
$moduleTypes[$record->module_type] = [];
}
$moduleTypes[$record->module_type][] = $record->instanceid;
}
// Fetch module names in bulk
$moduleNames = [];
foreach ($moduleTypes as $moduleType => $instances) {
if ($DB->get_manager()->table_exists($moduleType)) {
try {
$records = $DB->get_records_list($moduleType, 'id', array_unique($instances), '', 'id, name');
foreach ($records as $record) {
$moduleNames[$moduleType][$record->id] = $record->name;
}
} catch (dml_exception $e) {
// Continue if table doesn't have expected structure
continue;
}
}
}
// Build result
$result = [];
foreach ($records as $record) {
$moduleName = $moduleNames[$record->module_type][$record->instanceid] ?? '';
$result[] = [
'user_id' => (int)$record->userid,
'firstname' => $record->firstname,
'lastname' => $record->lastname,
'course_id' => (int)$record->courseid,
'course_name' => $record->course_name,
'module_id' => (int)$record->moduleid,
'module_name' => $moduleName,
'module_type' => $record->module_type,
'access_count' => (int)$record->access_count
];
}
return [
'status' => 'success',
'message' => '',
'data' => $result
];
}
/**
* Define the structure of the function's return value
*/
public static function get_module_access_returns()
{
return new external_single_structure([
'status' => new external_value(PARAM_TEXT, 'Status of the response (success/error)'),
'message' => new external_value(PARAM_TEXT, 'Error message if applicable', VALUE_OPTIONAL),
'data' => new external_multiple_structure(
new external_single_structure([
'user_id' => new external_value(PARAM_INT, 'User ID'),
'firstname' => new external_value(PARAM_TEXT, 'First name'),
'lastname' => new external_value(PARAM_TEXT, 'Last name'),
'course_id' => new external_value(PARAM_INT, 'Course ID'),
'course_name' => new external_value(PARAM_TEXT, 'Course name'),
'module_id' => new external_value(PARAM_INT, 'Module ID'),
'module_name' => new external_value(PARAM_TEXT, 'Module name'),
'module_type' => new external_value(PARAM_TEXT, 'Module type'),
'access_count' => new external_value(PARAM_INT, 'Number of times accessed')
])
)
]);
}
/** **************************************************************************************************************** */
public static function get_top_learners($limit = 10, $courseid = 0)
{
global $DB;
$params = \external_api::validate_parameters(self::get_top_learners_parameters(), [
'limit' => $limit,
'courseid' => $courseid
]);
$context = $params['courseid'] > 0
? \context_course::instance($params['courseid'])
: \context_system::instance();
\external_api::validate_context($context);
require_capability('moodle/site:viewparticipants', $context);
$sql = "SELECT
u.id AS userid,
u.firstname,
u.lastname,
u.email,
COUNT(DISTINCT cc.course) AS completed_courses,
COUNT(DISTINCT l.id) AS content_access_count
FROM {user} u
LEFT JOIN {course_completions} cc ON u.id = cc.userid AND cc.timecompleted > 0
LEFT JOIN {logstore_standard_log} l ON u.id = l.userid
AND l.action = 'viewed'
AND l.target = 'course_module'";
$queryparams = [];
if ($params['courseid'] > 0) {
$sql .= " AND (cc.course = :courseid OR l.courseid = :courseid2)";
$queryparams['courseid'] = $params['courseid'];
$queryparams['courseid2'] = $params['courseid'];
}
$sql .= " WHERE u.deleted = 0 AND u.suspended = 0
GROUP BY u.id
ORDER BY completed_courses DESC, content_access_count DESC";
$records = $DB->get_records_sql($sql, $queryparams, 0, $params['limit']);
$result = [];
foreach ($records as $record) {
$result[] = [
'user_id' => $record->userid,
'firstname' => $record->firstname,
'lastname' => $record->lastname,
'email' => $record->email,
'completed_courses' => (int)$record->completed_courses,
'content_access_count' => (int)$record->content_access_count,
'engagement_score' => self::calculate_engagement_score(
(int)$record->completed_courses,
(int)$record->content_access_count
)
];
}
return $result;
}
public static function get_top_learners_returns()
{
return new external_multiple_structure(
new external_single_structure([
'user_id' => new external_value(PARAM_INT, 'User ID'),
'firstname' => new external_value(PARAM_TEXT, 'First name'),
'lastname' => new external_value(PARAM_TEXT, 'Last name'),
'email' => new external_value(PARAM_TEXT, 'Email address'), // safer than PARAM_EMAIL
'completed_courses' => new external_value(PARAM_INT, 'Number of completed courses'),
'content_access_count' => new external_value(PARAM_INT, 'Number of content accesses'),
'engagement_score' => new external_value(PARAM_FLOAT, 'Engagement score'),
])
);
}
public static function calculate_engagement_score($completedCourses, $accessCount)
{
return ($completedCourses * 2) + ($accessCount * 0.5);
}
public static function get_top_learners_parameters()
{
return new external_function_parameters([
'limit' => new external_value(PARAM_INT, 'Max number of users to return', VALUE_DEFAULT, 10),
'courseid' => new external_value(PARAM_INT, 'Course ID (0 = all courses)', VALUE_DEFAULT, 0)
]);
}
/** **************************************************************************************************************** */
/**
* Get course enrollment statistics with user lists
*
* @param int $limit Number of courses to return (0 for all)
* @param bool $include_users Whether to include user names in the response
* @return array Course enrollment data
*/
public static function get_course_enrollments($limit = 0, $include_users = true)
{
global $DB;
// Validate parameters
$params = self::validate_parameters(self::get_course_enrollments_parameters(), [
'limit' => $limit,
'include_users' => $include_users
]);
// Context validation
$context = context_system::instance();
self::validate_context($context);
require_capability('moodle/course:viewparticipants', $context);
// Build SQL query
$sql = "SELECT
c.id AS course_id,
c.fullname AS course_name,
c.shortname AS course_shortname,
COUNT(ue.userid) AS total_enrolled";
if ($params['include_users']) {
// MySQL specific GROUP_CONCAT
$sql .= ", GROUP_CONCAT(DISTINCT CONCAT(u.firstname, ' ', u.lastname)
ORDER BY u.lastname ASC SEPARATOR ', ') AS enrolled_users";
}
$sql .= " FROM {enrol} e
JOIN {user_enrolments} ue ON e.id = ue.enrolid
JOIN {user} u ON ue.userid = u.id
JOIN {course} c ON e.courseid = c.id
WHERE u.deleted = 0
AND u.suspended = 0
GROUP BY c.id
ORDER BY total_enrolled DESC";
if ($params['limit'] > 0) {
$sql .= " LIMIT :limit";
$queryparams['limit'] = $params['limit'];
}
// Execute query
$records = $DB->get_records_sql($sql, $queryparams ?? []);
// Format results
$result = [];
foreach ($records as $record) {
$course_data = [
'course_id' => $record->course_id,
'course_name' => $record->course_name,
'course_shortname' => $record->course_shortname,
'total_enrolled' => (int)$record->total_enrolled
];
if ($params['include_users']) {
$course_data['enrolled_users'] = $record->enrolled_users;
}
$result[] = $course_data;
}
return $result;
}
public static function get_course_enrollments_parameters()
{
return new external_function_parameters([
'limit' => new external_value(PARAM_INT, 'Number of courses to return (0 for all)', VALUE_DEFAULT, 0),
'include_users' => new external_value(PARAM_BOOL, 'Include user names in response', VALUE_DEFAULT, true)
]);
}
public static function get_course_enrollments_returns()
{
return new external_multiple_structure(
new external_single_structure([
'course_id' => new external_value(PARAM_INT, 'Course ID'),
'course_name' => new external_value(PARAM_TEXT, 'Course full name'),
'course_shortname' => new external_value(PARAM_TEXT, 'Course short name'),
'total_enrolled' => new external_value(PARAM_INT, 'Number of enrolled users'),
'enrolled_users' => new external_value(PARAM_TEXT, 'Comma-separated list of enrolled users', VALUE_OPTIONAL)
])
);
}
/** **************************************************************************************************************** */
public static function get_course_participation_parameters()
{
return new external_function_parameters([
'courseid' => new external_value(PARAM_RAW, 'Course ID (0 for all)', VALUE_DEFAULT, 0),
'startdate' => new external_value(PARAM_RAW, 'Start date (timestamp)', VALUE_DEFAULT, 0),
'enddate' => new external_value(PARAM_INT, 'End date (timestamp)', VALUE_DEFAULT, 0),
'actions' => new external_multiple_structure(
new external_value(PARAM_ALPHA, 'Action to filter (e.g., viewed, submitted, posted)'),
'List of actions to filter',
VALUE_DEFAULT
),
'limit' => new external_value(PARAM_INT, 'Max number of records to return', VALUE_DEFAULT, 0)
]);
}
public static function get_course_participation($courseid = 0, $startdate = 0, $enddate = 0, $actions = [], $limit = 0)
{
global $DB;
$params = self::validate_parameters(self::get_course_participation_parameters(), [
'courseid' => (int) trim($courseid),
'startdate' => (int) trim($startdate),
'enddate' => (int) trim($enddate),
'actions' => array_map('trim', $actions),
'limit' => (int) trim($limit)
]);
// Context and capability check
$context = context_system::instance();
self::validate_context($context);
require_capability('moodle/course:viewparticipants', $context);
$where = "l.contextlevel = 50";
$sqlparams = [];
if (!empty($params['actions'])) {
list($insql, $inparams) = $DB->get_in_or_equal($params['actions'], SQL_PARAMS_NAMED);
$where .= " AND l.action $insql";
$sqlparams += $inparams;
}
if ($params['courseid'] > 0) {
$where .= " AND c.id = :courseid";
$sqlparams['courseid'] = $params['courseid'];
}
if ($params['startdate'] > 0) {
$where .= " AND l.timecreated >= :startdate";
$sqlparams['startdate'] = $params['startdate'];
}
if ($params['enddate'] > 0) {
$where .= " AND l.timecreated <= :enddate";
$sqlparams['enddate'] = $params['enddate'];
}
$sql = "
SELECT
u.id AS userid,
u.firstname,
u.lastname,
c.id AS courseid,
c.fullname AS course_name,
COUNT(l.id) AS participation_count
FROM {logstore_standard_log} l
JOIN {user} u ON l.userid = u.id
JOIN {course} c ON l.courseid = c.id
WHERE $where
GROUP BY u.id, c.id
ORDER BY participation_count DESC";
if ($params['limit'] > 0) {
$sql .= " LIMIT " . (int)$params['limit'];
}
$records = $DB->get_records_sql($sql, $sqlparams);
$result = [];
foreach ($records as $record) {
$result[] = [
'userid' => $record->userid,
'firstname' => $record->firstname,
'lastname' => $record->lastname,
'courseid' => $record->courseid,
'course_name' => $record->course_name,
'participation_count' => (int)$record->participation_count
];
}
return $result;
}
public static function get_course_participation_returns()
{
return new external_multiple_structure(
new external_single_structure([
'userid' => new external_value(PARAM_INT, 'User ID'),
'firstname' => new external_value(PARAM_TEXT, 'First name'),
'lastname' => new external_value(PARAM_TEXT, 'Last name'),
'courseid' => new external_value(PARAM_INT, 'Course ID'),
'course_name' => new external_value(PARAM_TEXT, 'Course name'),
'participation_count' => new external_value(PARAM_INT, 'Number of actions')
])
);
}
/** **************************************************************************************************************** */
public static function get_course_participation_total_parameters()
{
return new external_function_parameters([
'courseid' => new external_value(PARAM_INT, 'Course ID', VALUE_REQUIRED)
]);
}
public static function get_course_participation_total($courseid)
{
global $DB;
// Validate context and capability
$context = context_course::instance($courseid);
self::validate_context($context);
require_capability('moodle/course:viewparticipants', $context);
// Get all course modules (for 'outof' counts)
$modules = $DB->get_records('course_modules', ['course' => $courseid]);
$totalviewed = count($modules); // Total possible modules to view
$totalsubmitted = $DB->count_records('assign', ['course' => $courseid]);
$totalposted = $DB->count_records('forum', ['course' => $courseid]);
$totalpossible = $totalviewed + $totalsubmitted + $totalposted;
// Get participation data
$sql = "SELECT
l.userid,
u.firstname,
u.lastname,
c.id AS courseid,
c.fullname AS course_name,
COUNT(DISTINCT CASE WHEN l.action = 'viewed' THEN l.contextinstanceid END) AS modules_viewed, -- this wrong data gives
SUM(CASE WHEN l.action = 'viewed' THEN 1 ELSE 0 END) AS total_views,
COUNT(DISTINCT CASE WHEN l.action = 'submitted' THEN l.contextinstanceid END) AS submitted_count,
COUNT(DISTINCT CASE WHEN l.action = 'posted' THEN l.contextinstanceid END) AS posted_count
FROM {logstore_standard_log} l
JOIN {user} u ON u.id = l.userid
JOIN {course} c ON c.id = l.courseid
WHERE l.contextlevel = 50
AND l.action IN ('viewed', 'submitted', 'posted')
AND l.courseid = :courseid
GROUP BY l.userid, u.firstname, u.lastname, c.id, c.fullname
ORDER BY modules_viewed DESC, total_views DESC";
$params = ['courseid' => $courseid];
$records = $DB->get_records_sql($sql, $params);
$result = [];
foreach ($records as $r) {
$total_engaged = (int)$r->modules_viewed + (int)$r->submitted_count + (int)$r->posted_count;
$result[] = [
'userid' => $r->userid,
'firstname' => $r->firstname,
'lastname' => $r->lastname,
'courseid' => $r->courseid,
'course_name' => $r->course_name,
'viewed' => [
'unique_modules' => (int)$r->modules_viewed, // Number of distinct modules viewed
'total_views' => (int)$r->total_views, // Total view events
'outof' => $totalviewed
],
'submitted' => [
'count' => (int)$r->submitted_count,
'outof' => $totalsubmitted
],
'posted' => [
'count' => (int)$r->posted_count,
'outof' => $totalposted
],
'total' => [
'unique_engaged' => $total_engaged, // Unique modules engaged with
'total_actions' => $total_engaged + ((int)$r->total_views - (int)$r->modules_viewed),
'outof' => $totalpossible
]
];
}
return $result;
}
public static function get_course_participation_total_returns()
{
return new external_multiple_structure(
new external_single_structure([
'userid' => new external_value(PARAM_INT, 'User ID'),
'firstname' => new external_value(PARAM_TEXT, 'First name'),
'lastname' => new external_value(PARAM_TEXT, 'Last name'),
'courseid' => new external_value(PARAM_INT, 'Course ID'),
'course_name' => new external_value(PARAM_TEXT, 'Course name'),
'viewed' => new external_single_structure([
'unique_modules' => new external_value(PARAM_INT, 'Number of unique viewed actions'),
'total_views' => new external_value(PARAM_INT, 'Total viewable items'),
'outof' => new external_value(PARAM_INT, 'Total viewable items'),
]),
'submitted' => new external_single_structure([
'count' => new external_value(PARAM_INT, 'Number of submitted actions'),
'outof' => new external_value(PARAM_INT, 'Total assignments'),
]),
'posted' => new external_single_structure([
'count' => new external_value(PARAM_INT, 'Number of posted actions'),
'outof' => new external_value(PARAM_INT, 'Total forums'),
]),
'total' => new external_single_structure([
'unique_engaged' => new external_value(PARAM_INT, 'Total unique participation count'),
'total_actions' => new external_value(PARAM_INT, 'Total participation count'),
'outof' => new external_value(PARAM_INT, 'Total possible participation types'),
]),
])
);
}
/** **************************************************************************************************************** */
public static function get_last_login_parameters()
{
return new external_function_parameters([]);
}
public static function get_last_login()
{
global $DB;
// Validate context.
$context = context_system::instance();
self::validate_context($context);
require_capability('moodle/user:viewalldetails', $context);
$sql = "
SELECT
u.id AS userid,
u.firstname,
u.lastname,
u.email,
MAX(ul.timeaccess) AS last_login_time
FROM {user_lastaccess} ul
JOIN {user} u ON ul.userid = u.id
GROUP BY ul.userid, u.firstname, u.lastname, u.email
ORDER BY last_login_time DESC
";
$records = $DB->get_records_sql($sql);
$result = [];
foreach ($records as $record) {
$result[] = [
'userid' => $record->userid,
'firstname' => $record->firstname,
'lastname' => $record->lastname,
'email' => $record->email,
'last_login_time' => $record->last_login_time
];
}
return $result;
}
public static function get_last_login_returns()
{
return new external_multiple_structure(
new external_single_structure([
'userid' => new external_value(PARAM_INT, 'User ID'),
'firstname' => new external_value(PARAM_TEXT, 'First name'),
'lastname' => new external_value(PARAM_TEXT, 'Last name'),
'email' => new external_value(PARAM_TEXT, 'Email address'),
'last_login_time' => new external_value(PARAM_INT, 'Last login timestamp (UNIX)')
])
);
}
/** **************************************************************************************************************** */
/** **************************************************************************************************************** */
public static function get_course_completion_data_parameters()
{
return new external_function_parameters([
'courseid' => new external_value(PARAM_INT, 'Course ID'),
'userid' => new external_value(PARAM_INT, 'User ID (optional)', VALUE_OPTIONAL),
'is_details' => new external_value(PARAM_BOOL, 'Include activity completion details', VALUE_DEFAULT, false),
]);
}
public static function get_course_completion_data($courseid, $userid = null, $is_details = false)
{
global $DB;
$params = external_api::validate_parameters(self::get_course_completion_data_parameters(), [
'courseid' => $courseid,
'userid' => $userid,
'is_details' => $is_details,
]);
// Validate context and capability
$context = context_course::instance($params['courseid']);
self::validate_context($context);
require_capability('moodle/course:viewparticipants', $context);
$sqlParams = ['courseid' => $params['courseid']];
$userWhere = '';
if (!empty($params['userid'])) {
$userWhere = 'AND u.id = :userid';
$sqlParams['userid'] = $params['userid'];
}
$sql = "
SELECT
u.id AS userid,
u.firstname,
u.lastname,
c.id AS course_id,
c.fullname AS course_name,
cc.timecompleted,
(
SELECT COUNT(*)
FROM {grade_grades} gg
JOIN {grade_items} gi ON gg.itemid = gi.id
WHERE gg.userid = u.id
AND gg.finalgrade IS NOT NULL
AND gi.courseid = c.id
) AS completed_activities,
(
SELECT COUNT(*)
FROM {grade_items} gi
WHERE gi.courseid = c.id
) AS total_activities
FROM {user} u
JOIN {course_completions} cc ON u.id = cc.userid
JOIN {course} c ON cc.course = c.id
WHERE cc.course = :courseid
$userWhere
ORDER BY completed_activities DESC
";
$records = $DB->get_records_sql($sql, $sqlParams);
$result = [];
foreach ($records as $record) {
$entry = [
'userid' => $record->userid,
'firstname' => $record->firstname,
'lastname' => $record->lastname,
'course_id' => $record->course_id,
'course_name' => $record->course_name,
'timecompleted' => $record->timecompleted,
'completed_activities' => (int)$record->completed_activities,
'total_activities' => (int)$record->total_activities,
];
if ($params['is_details']) {
$activity_sql = "
SELECT cm.id AS cmid, mo.name AS modname, cmc.timecompleted
FROM {course_modules} cm
JOIN {modules} mo ON cm.module = mo.id
LEFT JOIN {course_modules_completion} cmc ON cm.id = cmc.coursemoduleid AND cmc.userid = :userid
WHERE cm.course = :courseid
";
$activities = $DB->get_records_sql($activity_sql, [
'userid' => $record->userid,
'courseid' => $record->course_id
]);
$activity_details = [];
foreach ($activities as $act) {
$activity_details[] = [
'modname' => $act->modname,
'timecompleted' => $act->timecompleted ? (int)$act->timecompleted : null,
];
}
$entry['activity_details'] = $activity_details;
}
$result[] = $entry;
}
return $result;
}
public static function get_course_completion_data_returns()
{
return new external_multiple_structure(
new external_single_structure([
'userid' => new external_value(PARAM_INT, 'User ID'),
'firstname' => new external_value(PARAM_TEXT, 'First name'),
'lastname' => new external_value(PARAM_TEXT, 'Last name'),
'course_id' => new external_value(PARAM_INT, 'Course ID'),
'course_name' => new external_value(PARAM_TEXT, 'Course full name'),
'timecompleted' => new external_value(PARAM_INT, 'Course completion time'),
'completed_activities' => new external_value(PARAM_INT, 'Number of completed activities'),
'total_activities' => new external_value(PARAM_INT, 'Total activities in course'),
'activity_details' => new external_multiple_structure(
new external_single_structure([
'modname' => new external_value(PARAM_TEXT, 'Activity type'),
'timecompleted' => new external_value(PARAM_INT, 'Activity completion time', VALUE_OPTIONAL),
]),
'Activity completion details',
VALUE_OPTIONAL
),
])
);
}
/** **************************************************************************************************************** */
}