[Building Sakai] GB: pulling grades, en masse, from gradebook via SQL -- solved?

[will at serensoft.com]

Hold the phone, we're closing in on it! So far this appears to work for the
per-gradable-object level (that is, course-grade-overrides are ignored at
this point, that'll come in a later phase)...

If you have gradebook chops, see if the following SQL is missing any tricks,
our spot-checking indicates it's working! (Seems to work for
no-category-grades, category-only-grades, and weighted-category-grades...)

Note that the temporary tables all start with "x_" to avoid colliding with
any existing Sakai tables, and they "go away" once you close your MySQL
connection.

drop table if exists x_courses;

create temporary table x_courses
    select
        site.site_id,
        site.title worksite,
        p.value term
    from
        SAKAI_SITE site
            join
        SAKAI_SITE_PROPERTY p
            on site.SITE_ID = p.SITE_ID
    where
        p.value = 'FA09' and -- ***** SEMESTER FILTER HERE *****
        p.name = 'term_eid' and
        site.published = 1
        and site.title like 'SOME-FILTERING-HERE%'
;

drop table if exists x_categories;

create temporary table x_categories
select
    s.title worksite,
    s.site_id,
    g.id gradebook,
    u.eid username,
    u.user_id,
    c.weight,
    c.name category,
    sum( o.points_possible ) max,
    sum( r.points_earned ) pts
from
    (
    GB_GRADABLE_OBJECT_T o
        left join
    GB_CATEGORY_T c
        on o.CATEGORY_ID = c.ID
        join
    (
    GB_GRADE_RECORD_T r
        join
    SAKAI_USER_ID_MAP u
        on r.student_id=u.user_id
    )
        on o.id = r.gradable_object_id
    )
        join
    GB_GRADEBOOK_T g
        on g.id=o.gradebook_id
        join
    SAKAI_SITE s
        on g.gradebook_uid=s.site_id
where
    s.site_id in (
        select
            site_id
        from
            x_courses
    ) and
    o.object_type_id = 1 and -- ***** NOT 'COURSE GRADE', gradebook-items
only *****
    o.not_counted != 1 -- ***** OMIT IF NOT INCLUDED IN COURSE GRADE *****
group by
    s.title,
    s.site_id,
    g.id,
    u.eid,
    u.user_id,
    c.weight,
    c.name
;

-- intermediate quick-check, already rolled up into categories when
available:
select worksite,username,category,weight,max,pts,
    (pts/max) score,
    (pts/max*weight) weighted
from x_categories;

-- now for the real grades (again these are gradable-object level grades,
-- if any course-override grades have been entered they would be ignored
select
    worksite,
    -- site_id,
    username,
    -- user_id,
    sum( weight ) scale,
    case
        when sum(weight) = 0 or sum(weight) is null
            then sum( pts ) / sum( max )
        else sum( pts/max * weight ) / sum( weight )
        end score
from
    x_categories
group by
    worksite,
    username;



On Mon, Nov 30, 2009 at 10:32 PM, will at serensoft.com <will at serensoft.com>wrote:
>
>> Use case: the registrar wants to see how the students are doing,
>> especially those who might need remedial support.
>>
>> We are experimenting with how best to pull grades from the gradebook in
>> 2.6.x on MySQL v5.0. We have some SELECT statements that do some
>> grade-pulling... and some spot-checks indicate they match with what's shown
>> in the browser for simple cases, but with partial activity in weighted
>> categories, it gets muddy and muddier.
>
>

-- 
will trillich
"Tis the set of the sails / And not the gales / That tells the way we go."
-- Ella Wheeler Wilcox
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20091201/afb65b48/attachment.html