Reportes de consultas personalizadas SQL
Creada por the Open University
Este plugin de reporte administrativo les permite a los Administradores configurar consultas arbitrarias a la BasedeDatos para que funcionen como reportes ad-hoc. Los reportes pueden ser de dos tipos: ya sea que corran bajo-demanda, o agendados para que corran automáticamente. Otros usuarios con la capacidad correcta pueden ir y ver una lista de consultas a las que tengan acceso. Los resultados pueden verse en-pantalla o descargarse como CSV.
Instalación de este reporte
Siga la documentación genérica para Instalar plugins .
Imágenes de pantalla
Aquí hay dos ejemplos de pantallas, que muestran las dos pantallas principales.
(Nota, estas imágenes de pantalla están con el tema gráfico de la Open University , no tienen el tema estándar de Moodle y iCMA es el nombre para los exámenes /cuestionarios en la Open University.)
Interfaz para usuarios normales
Lista de consultas disponibles
Los usuarios con la capacidad report/customsql:view pueden acceder a la lista de reportes en el bloque de Administración. Cada consulta es accesible solamente para ciertas personas. Hay tres niveles de acceso:
- Accesible a cualquiera que pueda acceder al reporte (aquellos con report/customsql:view).
- Accesible a personas que puedan ver otros reportes del sistema (aquellos con moodle/site:viewreports)
- Accesible solamente para administradores (aquellos con moodle/site:config)
Cuando Usted vaya a la lista, solamente le mostrará las consultas a las que puede acceder. Hay una nota al lado de cada consulta que dice cuando fue corrida por última vez, y cuanto tiempo tardó para generarse.
La lista muestra por separado las consultas bajo-demanda y las agendadas.
Nota: Pendiente de Traducir. ¡Anímese a traducir esta página!. ( y otras páginas pendientes)
Correr una consulta bajo-demanda
To run an on-demand query, click on its name in the list of queries.
The query will be run, and the results will be displayed as a table. Any URLs in the table will automatically be made into hyperlinks.
A description of the query may appear above the table.
The summary of when the query was run and how long it took is shown at the bottom, along with a link to download the CSV file (for example to get the data into Excel) and a link back to the list of all available queries.
Ver los resultados de consultas agendadas
Scheduled queries can work in one of two ways. Either each run of the report generates and entire table of reasults, or each run just creates one line of results, and the report builds up a row at a time.
When you click the name in the list of queries, you get taken to a display of the latest results, just like in the on-demand case.
However, if each scheduled run generates a complete report, then at the bottom of a page there will be a list of all the previous runs of the report, so that you can go and see how the report changed over time.
Interfaz para administradores
Administrators (that is, users with report/customsql:definequeries) see everything that other users see, but with some additions.
Controles adicionales en la interfaz para el staff
Administrators get shown who each report in the list is available to.
They also get an edit and a delete icon next to each query.
There is an Add new query button at the end of the list of queries.
When viewing a particular query, Administrators get an edit and a delete link underneath the table of results.
Añadir o editar una consulta
When you click the Add new query button, you get taken to an editing form that lets you define the query.
You must give the query a name.
You can optionally enter a description that is displayed above the results table. You should use this to explain what the results of the query mean.
You must enter the SQL to generate the results you want displayed. This must be an SQL select statement. You must use the prefix prefix_ for table names. It should not be possible to enter any SQL that would alter the contents of the database.
You choose who you want the query to be accessible to.
You choose whether the query should be run on-demand or scheduled weekly or monthly. If the report is scheduled, you can say whether the each run returns one row to be added to a single table, or whether each run generates a separate table.
When you save the new query, the SQL is checked to make sure that it will execute without errors. If you have said that the report will only return a single row, this is also checked. After saving the query, if this was a manual query, you are taken to the query results page, so you can see what the results look like. If it was an automatic query or if you cancel the form, you are taken to the list of available queries.
Editing an existing query uses the same form as for adding a new query, but to change the properties of an existing query.
Note that at the OU, weeks start on Saturday. If you don't like that, there is a fairly obvious constant to hack at the top of locallib.php.
Eliminar una consulta
When you click the delete icon or link for a query, you are taken to a confirmation page that shows you SQL of the query you are about to delete. The query is only deleted if you click Yes on the confirmation page.
After deleting a query, you are taken back to the list of queries.
Comparta aquí sus consultas interesantes
Si Usted tiene algunas SQL interesantes para reportes personalizados, puede compartirlos aquí:
Intentos de Exámenes en la semana / el mes anterior
Configure éste como un reporte agendado:
SELECT COUNT(*)
FROM prefix_quiz_attempts
WHERE timefinish > %%STARTTIME%%
AND timefinish <= %%ENDTIME%%
AND preview = 0
Esto necesariamente debe de estar configurado como reporte Agendado, en el primer día de cada semana (Scheduled, on the first day of each week) o Agendado, en el primer día de cada mes (Scheduled, on the first day of each month), o no funcionará.
Resumen del uso
Este reporte muestra a groso modo las mismas estadísticas que son enviadas a moodle.org cuando Usted registra su sitio, las que son agregadas en http://moodle.org/stats/. (La única diferencia es que el forato de registro no hace un AND confirmed = 1 por alguna razón.
SELECT
(SELECT COUNT(id) FROM prefix_course) - 1 AS courses,
(SELECT COUNT(id) FROM prefix_user WHERE deleted = 0 AND confirmed = 1) AS users,
(SELECT COUNT(DISTINCT ra.userid)
FROM prefix_role_capabilities rc
JOIN prefix_role_assignments ra ON ra.roleid = rc.roleid
WHERE rc.capability IN ('moodle/course:upd' || 'ate', 'moodle/site:doanything')) AS teachers,
(SELECT COUNT(id) FROM prefix_role_assignments) AS enrolments,
(SELECT COUNT(id) FROM prefix_forum_posts) AS forum_posts,
(SELECT COUNT(id) FROM prefix_resource) AS resources,
(SELECT COUNT(id) FROM prefix_question) AS questions
Hay dos consultas interesantes en esta publicación en blog When do students submit their online tests?.
Uso Mensual por Rol
Este reporte muestra un conteo distintivo de usuarios por su rol, que han accedido a su sitio. Cada instancia de usuario y rol es contada una vez al mes, sin importar a cuantos cursos haya accedido. Nosotros usamos esto para mostrar el número total de estudiantes y profesores que acceden a nuestro sitio.
SELECT
month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_month,
year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_year,
prefix_role.name as user_role,
COUNT(DISTINCT prefix_stats_user_monthly.userid) AS total_users
FROM
prefix_stats_user_monthly
Inner Join prefix_role_assignments ON prefix_stats_user_monthly.userid = prefix_role_assignments.userid
Inner Join prefix_context ON prefix_role_assignments.contextid = prefix_context.id
Inner Join prefix_role ON prefix_role_assignments.roleid = prefix_role.id
WHERE prefix_context.contextlevel = 50
AND `prefix_stats_user_monthly`.`stattype` = 'activity'
AND prefix_stats_user_monthly.courseid <>1
GROUP BY month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)),
year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)),
prefix_stats_user_monthly.stattype,
prefix_role.name
ORDER BY
year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)),
prefix_role.name
Usted necesita tener activadas las Estadísticas para poder generar datos con este reporte.
Versión PostgreSQL de la consulta de Uso Mensual por Rol:
SELECT
extract(month from to_timestamp(prefix_stats_user_monthly.timeend)) AS calendar_month,
extract(year from to_timestamp(prefix_stats_user_monthly.timeend)) AS calendar_year,
prefix_role.name AS user_role,
COUNT(DISTINCT prefix_stats_user_monthly.userid) AS total_users
FROM
prefix_stats_user_monthly
INNER JOIN prefix_role_assignments ON prefix_stats_user_monthly.userid = prefix_role_assignments.userid
INNER JOIN prefix_context ON prefix_role_assignments.contextid = prefix_context.id
INNER JOIN prefix_role ON prefix_role_assignments.roleid = prefix_role.id
WHERE prefix_context.contextlevel = 50
AND prefix_stats_user_monthly.stattype = 'activity'
AND prefix_stats_user_monthly.courseid <>1
GROUP BY extract(month from to_timestamp(prefix_stats_user_monthly.timeend)),
extract(year from to_timestamp(prefix_stats_user_monthly.timeend)),
prefix_stats_user_monthly.stattype,
prefix_role.name
ORDER BY
extract(year from to_timestamp(prefix_stats_user_monthly.timeend)), extract(month from to_timestamp(prefix_stats_user_monthly.timeend)),
prefix_role.name
Mostrar todos los resultados de exámenes (cuestionarios) en todo un sitio
Yo estoy seguro de que alguien podrá mejorar esto, pero esta es una consulta que mostrará los resultados de exámenes (cuestionarios) en todo el sitio. --Stuart Mealor 22:07, 9 April 2010 (UTC)
SELECT
prefix_grade_items.itemname,
prefix_grade_items.grademax,
ROUND(prefix_grade_grades.finalgrade, 0) AS finalgrade,
prefix_user.firstname,
prefix_user.lastname,
prefix_user.username
FROM
prefix_grade_grades
INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id
INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id
WHERE (prefix_grade_items.itemname IS NOT NULL)
AND (prefix_grade_items.itemtype = 'mod' OR prefix_grade_items.itemtype = 'manual')
AND (prefix_grade_items.itemmodule = 'quiz' OR prefix_grade_items.itemmodule IS NULL)
AND (prefix_grade_grades.timemodified IS NOT NULL)
AND (prefix_grade_grades.finalgrade > 0)
AND (prefix_user.deleted = 0)
Vea también
- Custom SQL queries report forum announcement
- list of ad-hoc contributed reports
- Forum post with some more useful queries.
- Modules and plugins database entry
