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

[will at serensoft.com]

It's looking more and more like SQL alone won't do the trick. Must we fully
recreate the init() logic in
gradebook/app/ui/src/java/org/sakaiproject/tool/gradebook/ui/RosterBean.java
to get a quick grade-snapshot? Yikes!

Sure would be great to have a webservice that returned grade sheets... :)


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.
>
> For example: a course has two weighted grading categories: alpha at 40%,
> omega at 60%. Each weighted category has three items worth 5 points apiece.
> When TWO items from "alpha" have been graded and NO items from "omega" have
> had any activity at all, the student gets N out of 10 points (5 per graded
> object, and there are only two that have been graded) as total course grade
> at that point! How can we accomodate this via SQL?
>
> Any suggestions and pointers are welcome! (Another tricky part is how to
> distinguish between a course that has no weighted categories, and a course
> that has some gradable objects not assigned to a weighted category?)
>
>
> SQL so far for MySQL 5.1 and Sakai 2.6.x:
>
> 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 'BLAH%' -- other trim-down filters here
> ;
>
> drop table if exists x_grades;
>
> create temporary table x_grades
> select
>     s.title worksite,
>     s.site_id,
>     g.id gradebook,
>     u.eid username,
>     u.user_id,
>     c.weight,
>     c.name category,
>     o.name graded_item,
>     o.points_possible max,
>     r.points_earned pts
> from
>     (
>     GB_GRADABLE_OBJECT_T o
>         left join
>     GB_CATEGORY_T c
>         on o.CATEGORY_ID = c.ID
>         left 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 *****
> order by
>     s.title,
>     u.eid,
>     c.name
> ;
>
> Then the trick is to "properly" sum grades by category... any help is
> welcome!
>
> --
> will trillich
> "Tis the set of the sails / And not the gales / That tells the way we go."
> -- Ella Wheeler Wilcox
>



-- 
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/a5ee1efd/attachment.html