Hello,
I am trying to run the 'User's accumulative time spent in the course', which is at (https://docs.moodle.org/38/en/ad-hoc_contributed_reports) for all my students (2700). Is there a way to loop through the entire course and not just for me?
So far I was able to see mine, but when I tried to use "while, Set, Print" and other sql commands. It did not work, I got the following error 'NOT ALLOWED WORDS' .
SELECT
SUM(delta) as soma,
CPF,
userid
FROM
(SELECT
l.id,
l.timecreated,
u.id as userid,
u.idnumber as CPF,
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%d-%m-%Y %h:%i:%s') AS dTime,
@prevtime := (SELECT MAX(timecreated) FROM prefix_logstore_standard_log
WHERE userid = %%USERID%% AND id < l.id ORDER BY id ASC LIMIT 1) AS prev_time,
DATE_FORMAT(FROM_UNIXTIME(@prevtime),'%d-%m-%Y %h:%i:%s') AS pTime,
IF (l.timecreated - @prevtime < 7200, @delta := @delta + (l.timecreated-@prevtime),0) AS sumtime,
l.timecreated-@prevtime AS delta,
"User" AS TYPE
FROM prefix_logstore_standard_log AS l
JOIN prefix_user as u ON u.id = l.userid,
(SELECT @delta := 0) AS s_init
# CHANGE UserID
WHERE l.userid = %%USERID%% AND l.courseid = %%COURSEID%%
%%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%) src