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

[will at serensoft.com]

Yes indeed, a gradebook webservice would be grand. Something like this... :)

gradesFromCoursesInTerm with a site_property.value to match the requested
calendar term, to return a collection of
- sakai_site.site_id
- sakai_site.title
- sakai_user_id_map.eid
- sakai_user_id_map.user_id
- course-grade-calculation for this user in this course

...that would be ideal. :) Maybe with some other parameters such as
searching by worksite-type or course-title or gradebooks-modified-since,
etc.

Until then, the SQL appears to be working in the interim. We're still poking
it, looking for mis-matches.


On Tue, Dec 1, 2009 at 4:46 PM, Steve Swinsburg
<steve.swinsburg at gmail.com>wrote:

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


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