General developer forum

Get number of accesses and other data in Configurable Reports with SQL

 
Picture of Jonathan Bailon Segura
Get number of accesses and other data in Configurable Reports with SQL
 

Hello everyone, I am using the Configurable Reports to get some values of the students, among the options provided by the pluginya I can obtain the following data: uername, name, time dedicated to the course, activities seen, shipments in activities, Number of revenues to the platform. These values are very easy to get them with the plugin, the problem that I need an extra column that is the group to which the student belongs within the course that I did not find within the options.


I am doing the query by SQL to get these same values and append the data that intesera me the group to which belongs, but I have problems getting the time dedicated to the course, and the number of income to the course, someone could guide me how can I get these Values using SQL?

I leave the current query that I am managing to obtain some values:


SELECT  u.username AS matricula,
        CONCAT(u.firstname,  ' ', u.lastname) AS Nombre,
        c.fullname AS Curso, g.name AS Grupo,
        CASE
            WHEN u.lastlogin = 0 THEN '-'
            ELSE from_unixtime(u.lastlogin)
        END AS 'Ultimo ingreso'
FROM prefix_user u
    INNER JOIN prefix_user_enrolments ue ON ue.userid = u.id
    INNER JOIN prefix_enrol e ON e.id = ue.enrolid
    INNER JOIN prefix_role_assignments r ON u.id = r.userid
    INNER JOIN prefix_context ctx ON ctx.id = r.contextid AND contextlevel = 50
    INNER JOIN prefix_course c ON c.id = ctx.instanceid AND c.id=e.courseid
    INNER JOIN prefix_course_categories cc ON cc.id = c.category
    INNER JOIN prefix_groups g ON g.courseid = c.id
    INNER JOIN prefix_groups_members gm ON gm.groupid = g.id AND gm.userid = u.id
WHERE c.id=%%COURSEID%%

%%FILTER_ROLE: r.roleid%%
 
Average of ratings: -