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%%