Cosas de Administradores

Reportes generales de Moodle

 
Imagen de Slader Maxto
Re: Reportes generales de Moodle
Moodlers de gran ayuda

Hola Claudio yo talvez te pueda ayudar con una de tus inquietudes (1)

ESta consulta la creo Cristian Blanquer : https://moodle.org/mod/forum/discuss.php?d=346950&parent=1401289

Nombres de los profesores 

select 

C.fullname, 

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 01 ) as 'Enero',

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 02 ) as 'Febrero',

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 03 ) as 'Marzo',

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 04 ) as 'Abril',

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 05 ) as 'Mayo',

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 06 ) as 'Junio',

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 07 ) as 'Julio',

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 08 ) as 'Agosto',

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 09 ) as 'Septiembre',

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 10 ) as 'Octubre',

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 11 ) as 'Noviembre',

(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 12 ) as 'Diciembre'

FROM mdl_course C

 WHERE fullname='NOMBRE_CURSO' 

or fullname='NOMBRE_CURSO' ;


Cuenta de profesores

select 

C.fullname , 

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 01 ) as 'Enero',

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 02 ) as 'Febrero',

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 03 ) as 'Marzo',

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 04 ) as 'Abril',

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 05 ) as 'Mayo',

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 06 ) as 'Junio',

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 07 ) as 'Julio',

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 08 ) as 'Agosto',

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 09 ) as 'Septiembre',

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 10 ) as 'Octubre',

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 11 ) as 'Noviembre',

(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 12 ) as 'Diciembre'

FROM mdl_course C

-- WHERE C.id = 3


 WHERE fullname='NOMBRE_CURSO' 

or fullname='NOMBRE_CURSO' ;


DEBES TENER EN CUENTA EL ID DEL ROL Y EL NOMBRE DEL CURSO, TAMBIÉN EL NOMBRE DE LAS TABLAS COMO VOS LAS TIENES



 
Promedio de calificaciones: -
Imagen de Claudio Herrero
Re: Reportes generales de Moodle
 

Muchas gracias por tu aporte desde Mar del Plata Argentina te envio un saludo. Claudio  

 
Promedio de calificaciones: -