Cosas de Administradores

Informes de mensajes privados

 
FOTOCARNET
Re: Informes de mensajes privados
Moodlers de gran ayuda

Yo estoy usando Moodle 3.3 mira la tabla mdl_message de tu versión por si ese campo no existe y en ese caso quítalo del sql o sustitúyelo por el nombre que sea.

De todos modos, te aconsejo que mires en este enlace que tienes muchos ejemplos de SQL sobre la base de datos de Moodle y de distintos tipos


https://docs.moodle.org/26/en/ad-hoc_contributed_reports#LIST_of_all_site_USERS_by_COURSE_enrollment_.28Moodle_2.x.29



 
Promedio de calificaciones: -
Imagen de jose luis garcia
Re: Informes de mensajes privados
 

Gracias, ya solucioné el error y ví todos los ejemplos, pero sigo sin encontrar lo que busco.

En el informe deben salir los mensajes privados del curso, no de toda la plataforma y sólo los mensajes dirigidos al profesor y del profesor a los alumnos.

Gracias de todos modos, saludos.

 
Promedio de calificaciones: -
FOTOCARNET
Re: Informes de mensajes privados
Moodlers de gran ayuda

Pues lo tienes facil pon un where con el id del curso 

 
Promedio de calificaciones: -
Imagen de jose luis garcia
Re: Informes de mensajes privados
 

OK, lo intentaré. Gracias

 
Promedio de calificaciones: -
Imagen de jose luis garcia
Re: Informes de mensajes privados
 

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

 
Promedio de calificaciones: -
Imagen de borja López Casanova
Re: Informes de mensajes privados
 

Buenas tardes, voy a realizar una consulta que entiendo que pueda parecer muy básica.

Intente introducir la id del curso pero me da error, podría ayudarme?


SELECT 

 u.username AS 'From', 

 u2.username AS 'To',

 DATE_FORMAT(FROM_UNIXTIME(me.timecreated), '%Y-%m-%d %H:%i') AS 'When',

 me.subject, 

 me.smallmessage

FROM prefix_message me

WHERE c.id=24

JOIN prefix_user u ON u.id = me.useridfrom

JOIN prefix_user u2 ON u2.id = me.useridto

ORDER BY u.username, u2.username


 
Promedio de calificaciones: -