-- GROUP_CONCAT lists all the weeks and the standards completed in that week in a single table cell
-- By adding a order by in the group concat, we get the proper ordering of weeks (weeks were in the incorrect order without it)
-- CHAR(63) because the '?' is not accepted by Moodle Ad-Hoc Queries.
SELECT
outcomes.courseid,
CONCAT('', C.fullname,''
) as course_name,
CONCAT(
GROUP_CONCAT(
CONCAT(outcomes.weekstr, concat(' [', outcomes.count, ']'))
ORDER BY courseid ASC, w ASC
SEPARATOR '
'
), '
Total:', SUM(outcomes.count)
) as outcomes_per_week
FROM
(
-- This sub query combines the three queries used by Moodle to get the outcome count
-- The WHERE statement
-- a) gets the day 16 weeks ago
-- b) We want to include results beginning on Sunday of that week
-- The SELECT weekstr - each day belongs to a week. We match each day with the start of its week.
-- eg. For the week of Wed Jan 5, the weekstr would be Sun-2
SELECT
goc.courseid, COUNT(gg.finalgrade) AS count,
DATE_FORMAT(
str_to_date(
concat(year(from_unixtime(gg.timemodified)), LPAD(week(from_unixtime(gg.timemodified)), 2, 0), 'sunday' ),
'%X%V %W'),
'%b-%e') weekstr,
week(from_unixtime(gg.timemodified)) as w
FROM prefix_grade_outcomes go
JOIN prefix_grade_outcomes_courses goc
ON go.id = goc.outcomeid
JOIN prefix_grade_items as gi
ON goc.outcomeid = gi.outcomeid AND
gi.courseid = goc.courseid
JOIN prefix_grade_grades as gg
ON gg.itemid = gi.id
WHERE gg.timemodified >= UNIX_TIMESTAMP(STR_TO_DATE(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 16 week), '%Y%V Sunday'), '%X%V %W'))
GROUP BY courseid, week(from_unixtime(gg.timemodified))
) as outcomes
JOIN prefix_course as C
ON C.id = outcomes.courseid
GROUP BY courseid
ORDER BY C.fullname asc
Note: Optimization can be done to move the weekstr to the outside query.
