I have a working copy of my code. I can't say that this is 100% complete yet, but perhaps 90%. You might be able to learn from it (and point out errors to me.) Note, I use only URLs, quizzes, forums, and assignments. I decided it was best to write the SQL for each, and then combine them with a UNION. Also, because of how long this code takes to run, I tend to run it only for one course. If your courses use other
activities, you would need to write the appropriate SQL. Make sure to replace some of my values with your own.
Here it is:
CREATE VIEW rjStudentActivity AS
# QUIZ
SELECT cm.course AS CourseID,
c.shortname AS Course_Shortname,
c.idnumber AS Course_IDNumber,
cm.section AS TopicID,
t.name AS TopicName,
cm.id AS ActivityID,
q.name AS ActivityName,
cm.module AS ActTypeNum,
m.name AS ActTypeName,
cm.completion AS ActCompType,
cm.visible AS ActivityVisible,
if(q.timeopen=0, 0, from_unixtime(q.timeopen, "%m/%d/%Y %h:%i:%s %p")) AS ActivityTimeOpen,
if(q.timeclose=0, 0, from_unixtime(q.timeclose, "%m/%d/%Y %h:%i:%s %p")) AS ActivityTimeClosed,
cm.instance AS LogInstance,
from_unixtime(ls.timecreated, "%m/%d/%Y %h:%i:%s %p") AS LogDate,
ls.action AS LogAction,
ls.target as LogTarget,
ls.origin AS LogOrigin,
ls.userid AS StudentID,
ls.ip AS StudentIP,
u.lastname AS StudentLastName,
u.firstname AS StudentFirstName,
qa.quiz AS AttemptID,
qa.id AS StudentItem,
qa.attempt AS ItemAttemptNumber,
if(qa.timestart=0, 0, from_unixtime(qa.timestart, "%m/%d/%Y %h:%i:%s %p")) AS StudentItemStartTime,
if(qa.timefinish=0, 0, from_unixtime(qa.timefinish, "%m/%d/%Y %h:%i:%s %p")) AS StudentItemEndTime,
qa.sumgrades AS StudentItemGrade,
gg.rawgrade AS StudentActivityGrade,
q.grade AS ActivityMaxGrade
FROM mdl_course_modules cm
JOIN mdl_modules m ON m.id = cm.module
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_course_sections t ON t.id = cm.section
JOIN mdl_quiz q ON q.id = cm.instance
LEFT JOIN mdl_grade_items gi ON (gi.iteminstance = cm.instance
AND cm.course = gi.courseid)
JOIN mdl_logstore_standard_log ls ON (ls.contextinstanceid = cm.id
AND ls.courseid = cm.course)
LEFT JOIN mdl_quiz_attempts qa ON (qa.id = ls.objectid
AND qa.userid = ls.userid)
JOIN mdl_grade_grades gg ON (gg.itemid = gi.id
AND ls.userid = gg.userid)
JOIN mdl_user u ON u.id = ls.userid
WHERE cm.module = 16
AND cm.visible = 1
AND ls.component = "mod_quiz"
AND ls.action in ("submitted", "viewed", "reviewed", "started")
AND c.idnumber > 200
AND cm.course IN (52739)
UNION All
#
URL
SELECT cm.course AS CourseID,
c.shortname AS Course_Shortname,
c.idnumber AS Course_IDNumber,
cm.section AS TopicID,
t.name AS TopicName,
cm.id AS ActivityID,
url.name AS ActivityName,
cm.module AS ActTypeNum,
m.name AS ActTypeName,
cm.completion AS ActCompType,
cm.visible AS ActivityVisible,
NULL AS "ActivityTimeOpen",
NULL AS "ActivityTimeClosed",
cm.instance AS LogInstance,
from_unixtime(ls.timecreated, "%m/%d/%Y %h:%i:%s %p") AS LogDate,
ls.action AS LogAction,
ls.target as LogTarget,
ls.origin AS LogOrigin,
ls.userid AS StudentID,
ls.ip AS StudentIP,
u.lastname AS StudentLastName,
u.firstname AS StudentFirstName,
NULL AS "AttemptID",
NULL AS "StudentItem",
NULL AS "ItemAttemptNumber",
from_unixtime(ls.timecreated, "%m/%d/%Y %h:%i:%s %p") AS "StudentItemStartTime",
NULL AS "StudentItemEndTime",
NULL AS "StudentItemGrade",
NULL AS "StudentActivityGrade",
NULL AS "ActivityMaxGrade"
FROM mdl_course_modules cm
JOIN mdl_modules m ON m.id = cm.module
JOIN mdl_url url ON url.id = cm.instance
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_course_sections t ON t.id = cm.section
JOIN mdl_logstore_standard_log ls ON (ls.contextinstanceid = cm.id
AND ls.courseid = cm.course)
JOIN mdl_user u ON u.id = ls.userid
WHERE cm.module = 20
AND cm.visible = 1
AND ls.component = "mod_url"
AND ls.userid > 0
AND c.idnumber > 200
AND cm.course IN (52739)
UNION All
# Assignments
SELECT cm.course AS CourseID,
c.shortname AS Course_Shortname,
c.idnumber AS Course_IDNumber,
cm.section AS TopicID,
t.name AS TopicName,
cm.id AS ActivityID,
a.name ActivityName,
cm.module AS ActTypeNum,
m.name AS ActTypeName,
cm.completion AS ActCompType,
cm.visible AS ActivityVisible,
if(a.allowsubmissionsfromdate=0, 0, from_unixtime(a.allowsubmissionsfromdate, "%m/%d/%Y %h:%i:%s %p")) AS ActivityTimeOpen,
if(a.duedate=0, 0, from_unixtime(a.duedate, "%m/%d/%Y %h:%i:%s %p")) AS ActivityTimeClosed,
cm.instance AS LogInstance,
from_unixtime(ls.timecreated, "%m/%d/%Y %h:%i:%s %p") AS LogDate,
ls.action AS LogAction,
ls.target as LogTarget,
ls.origin AS LogOrigin,
ls.userid AS StudentID,
ls.ip AS StudentIP,
u.lastname AS StudentLastName,
u.firstname AS StudentFirstName,
a.id AS AttemptID,
ls.objecttable AS StudentItem,
ag.attemptnumber AS ItemAttemptNumber,
if(ag.timecreated=0, 0, from_unixtime(ag.timecreated, "%m/%d/%Y %h:%i:%s %p")) AS StudentItemStartTime,
if(ag.timemodified=0, 0, from_unixtime(ag.timemodified, "%m/%d/%Y %h:%i:%s %p")) AS StudentItemEndTime,
NULL AS "StudentItemGrade",
a.grade AS ActivityMaxGrade,
ag.grade AS StudentActivityGrade
FROM mdl_course_modules cm
JOIN mdl_modules m ON m.id = cm.module
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_course_sections t ON t.id = cm.section
JOIN mdl_assign a ON a.id = cm.instance
JOIN mdl_logstore_standard_log ls ON (ls.contextinstanceid = cm.id
AND ls.courseid = cm.course)
JOIN mdl_assign_grades ag ON (a.id = ag.assignment
AND ag.userid = ls.userid)
JOIN mdl_user u ON u.id = ls.userid
WHERE cm.module = 1
AND cm.visible = 1
AND c.idnumber > 200
AND component IN ("mod_assign")
and action in ("submitted", "updated", "viewed")
AND objecttable IS NOT NULL
AND cm.course IN (52739)
# AND ls.userid IN (3015)
UNION All
# Forum
SELECT cm.course AS CourseID,
c.shortname AS Course_Shortname,
c.idnumber AS Course_IDNumber,
cm.section AS TopicID,
t.name AS TopicName,
cm.id AS ActivityID,
f.name AS ActivityName,
cm.module AS ActTypeNum,
m.name AS ActTypeName,
cm.completion AS ActCompType,
cm.visible AS ActivityVisible,
NULL as "ActivityTimeOpen",
if(f.duedate=0, NULL, from_unixtime(f.duedate, "%m/%d/%Y %h:%i:%s %p")) AS ActivityTimeClosed,
cm.instance AS LogInstance,
from_unixtime(ls.timecreated, "%m/%d/%Y %h:%i:%s %p") AS LogDate,
ls.action AS LogAction,
ls.target as LogTarget,
ls.origin AS LogOrigin,
ls.userid AS StudentID,
ls.ip AS StudentIP,
u.lastname AS StudentLastName,
u.firstname AS StudentFirstName,
ls.contextinstanceid AS AttemptID,
fp.message AS StudentItem,
ls.objectid AS ItemAttemptNumber,
if(ls.timecreated=0, 0, from_unixtime(ls.timecreated, "%m/%d/%Y %h:%i:%s %p")) AS StudentItemStartTime,
NULL AS "StudentItemEndTime",
r.rating AS StudentItemGrade,
gg.rawgrade AS StudentActivityGrade,
if(f.assessed = 5, f.scale, NULL) AS ActivityMaxGrade
FROM mdl_course_modules cm
JOIN mdl_modules m ON m.id = cm.module
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_course_sections t ON t.id = cm.section
JOIN mdl_forum f ON f.id = cm.instance
LEFT JOIN mdl_grade_items gi ON (gi.iteminstance = cm.instance
AND cm.course = gi.courseid)
JOIN mdl_logstore_standard_log ls ON (ls.contextinstanceid = cm.id
AND ls.courseid = cm.course)
LEFT JOIN mdl_forum_posts fp ON (fp.id = ls.objectid
AND fp.userid = ls.userid)
JOIN mdl_grade_grades gg ON (gg.itemid = gi.id
AND ls.userid = gg.userid)
JOIN mdl_user u ON u.id = ls.userid
LEFT JOIN mdl_rating r ON (r.itemid = ls.objectid
AND r.contextid = ls.contextid)
WHERE cm.module = 9
AND ls.component = "mod_forum"
AND ls.target NOT IN ("user_report",
"readtracking",
"course_module_instance_list",
"discussion_subscription")
and ls.action not in ("moved", "pinned")
AND cm.visible = 1
AND c.idnumber > 200
AND cm.course IN (52739)