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

[Steve Swinsburg]

I was going to suggest that this use case would be a perfect candidate for a set of Gradebook web services.

cheers,
Steve



On 02/12/2009, at 1:23 AM, will at serensoft.com wrote:

> 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
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
> 
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20091202/4d3f0cef/attachment.html