we experience a problem with our configurable reports SQL Code since we updated our system to 3.4.
Can anybody help us with following code snippet?
SELECT
u.firstname AS 'First Name'
, u.lastname AS 'Last Name'
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=complete','">','Link','</a>') AS 'Links to Activity Report'
# , COUNT(l.id) AS 'Edits'
, SUM(IF(FLOOR((l.TIME - c.startdate)/(60*60*24*7))=1,1,0)) AS 'Unit 0 Post'
#, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=&logformat=showashtml','">','Logs','</a>') AS 'Logs'
#, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=outline">','Outline','</a>') AS 'Activity Outline'
#, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=complete">','Activity','</a>') AS 'Consolidated Activity'
FROM prefix_user AS u
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_course_categories AS cc ON c.category = cc.id
LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id AND l.action LIKE "%add%"
WHERE c.id = %%COURSEID%%
AND ra.roleid =5
AND ctx.instanceid = c.id
GROUP BY u.id
ORDER BY u.lastname, u.firstname
Kind Regards
Wolf Georg Tews
