Friday, November 2, 2012

Moodle Query to Retrieve Outcomes Completed in Recent Weeks

To track outcome progress in Moodle for the past week, this ad-hoc aka custom query reports, lists the numbers of outcomes completed each week. It is formatted to show the weekly total for each course as shown in this image:




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