Cosas de Administradores

En el ad-hoc contributed reports, he conseguido algo parecido a lo que quería, pero creo que está puesto sólo para el día a día, como hacer para que aparezcan los datos de todo el curso??

# Messages BETWEEN students AND instructors - TO DATE
,  (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id ) AS 'Mensajes del alumno al profesor'
, (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id) AS 'Mensajes del profesor al alumno'
 

El código completo es el siguiente:

SELECT
 
# Identify student
CONCAT(allstu.firstname , ' ' , allstu.lastname , '</a>' ) AS 'Alumno'

, IF((COUNT(DISTINCT IF(fps.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fps.id,NULL) )>0) OR (COUNT(DISTINCT IF(asb.timemodified > (UNIX_TIMESTAMP()  - (7*24*60*60)),asb.id,NULL))>0) OR  (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id AND mfs.timecreated > (UNIX_TIMESTAMP()  - (7*24*60*60))),'SI','NO') AS 'Participación del alumno en los últimos 7 días'
 
## ALL posts IN course so far
# COUNT posts BY student
, COUNT(DISTINCT fps.id) AS 'Mensajes del alumno en el foro'
 
# COUNT replies TO student posts BY instructors
, COUNT(DISTINCT fpi.id) AS 'Respuestas del profesor'
 
# USING link back TO student posts ON replies, GET UNIQUE student IDs responded
, COUNT(DISTINCT fpsr.id) - COUNT(DISTINCT fpi.id) AS 'Respuestas del alumno'
 
# Messages BETWEEN students AND instructors - TO DATE
,  (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id ) AS 'Mensajes del alumno al profesor'
, (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id) AS 'Mensajes del profesor al alumno'
 
## JOINS
 
# START BY getting ALL the students IN the course
FROM prefix_user AS allstu
JOIN prefix_role_assignments AS ras ON allstu.id = ras.userid AND ras.roleid = 5
JOIN prefix_context AS ctx  ON ras.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_course_categories AS cc ON c.category = cc.id
 
# Now we GET the forums AND forum discussions FROM this course ONLY
LEFT JOIN prefix_forum AS frm ON frm.course = c.id AND c.id = %%COURSEID%%
LEFT JOIN prefix_forum_discussions AS fd ON fd.course = %%COURSEID%% AND fd.forum = frm.id
 
# These are forum discussion posts just BY students WITHIN specified TIME
LEFT JOIN prefix_forum_posts AS fps ON fps.userid = allstu.id AND fps.discussion = fd.id
 
# Separately, we CONNECT the instructors OF the courses
# We can USE the context we have already gotten FOR the students
LEFT JOIN prefix_role_assignments AS rai ON rai.contextid = ctx.id
LEFT JOIN prefix_user AS instr ON instr.id = rai.userid AND rai.roleid =3
 
# Now we will CONNECT TO posts BY instructors that are replies TO student posts
# This IS a LEFT JOIN, because we don't want to eliminate any students from the list
LEFT JOIN prefix_forum_posts AS fpi ON fpi.discussion = fd.id AND fpi.userid = instr.id AND fpi.parent = fps.id
 
# To get identities of only those students who were replied to:
# Connect from instr replies back up to parent posts by students again
# This has to be a LEFT JOIN, we know these posts exist but don't eliminate non-responded students
LEFT JOIN prefix_forum_posts AS fpir ON fpir.id = fpi.parent
 
# We also want TO know IF students are replying TO one another
# These are posts that are replies TO student posts
# Again, a LEFT JOIN
LEFT JOIN prefix_forum_posts AS fpsr ON fpsr.discussion = fd.id AND fpsr.parent = fps.id
 
# GET the activity modules
LEFT JOIN prefix_course_modules AS cm ON c.id = cm.course
 
# GET the assignments
LEFT JOIN prefix_assign AS a ON  cm.instance = a.id
 LEFT JOIN prefix_assign_submission AS asb ON a.id = asb.assignment AND asb.userid=allstu.id
LEFT JOIN prefix_assign_grades AS asg ON asg.assignment = a.id AND asg.userid = allstu.id AND asg.assignment = asb.assignment
 
# We care about messages that involve BOTH the instructor AND students OF this course
# messages FROM instructor TO students:
# LEFT JOIN prefix_message AS mts ON mts.useridfrom = instr.id AND mts.useridto = allstu.id
# LEFT JOIN prefix_message AS mfs ON mfs.useridfrom = instr.id AND mfs.useridto = allstu.id
 
WHERE 
c.id = %%COURSEID%%
 
# GROUP BY c.shortname , allstu.id
GROUP BY allstu.id
 
ORDER BY allstu.lastname