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

[Branden Visser]

Hi Will,

If SQL doesn't work for you, the following Java method in the Gradebook 
package might be of some interest:

org.sakaiproject.tool.gradebook.ui.CourseGradeDetailsBean.getSpreadsheetData()

Hope that helps,
Branden

will at serensoft.com wrote:
> 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 <http://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 <http://g.id> gradebook,
>     u.eid username,
>     u.user_id,
>     c.weight,
>     c.name <http://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 <http://o.id> = r.gradable_object_id
>     )   
>         join
>     GB_GRADEBOOK_T g
>         on g.id <http://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 <http://g.id>,
>     u.eid,
>     u.user_id,
>     c.weight,
>     c.name <http://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
>     <mailto:will at serensoft.com> <will at serensoft.com
>     <mailto: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
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> 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"