Gradebookplus

The GradebookPlus Version 2 (GBPv2) patch adds the following functionality to standard gradebook:

• in-place editing of gradebook
• multi-column editing (spreadheet-style) for all numerically-marked activities in a given category
• create graded events that are not course activities (this gets around the issue in the gradebook where users had to create offline assignments for non-Moodle course work).

• Works with Moodle versions 1.5 and 1.6

18STABLE branch works with Moodle 1.8 (and probably Moodle 1.7). Efforts are made to merge changes from 18STABLE branch of Moodle's standard gradebook with the GBPv2 patch 18STABLE branch. Anthony hopes that the GBPv2 patch will be made obsolete by the improvements and new features made available in Moodle 1.9. Current plans are to fix bugs in the GBPv2 patch as they are identified and reported in the CONTRIB section of the Moodle Tracker.

More information about the GBPv2 Patch is available at: http://moodle.org/mod/data/view.php?d=13&rid=929

Migration from GBPv2 to Moodle 1.9 (Experimental)

The gradebook in Moodle was completely reworked for Moodle 1.9. GBPv2 does not work with Moodle 1.9 or later and will not be updated to do so because Moodle 1.9 has advanced beyond the level of GBPv2. The following steps were developed by Anthony Borrow to help users migrating the GBPv2 data from Moodle 1.8 to 1.9. Here is Anthony's disclaimer about the steps:

Please keep in mind that these queries are intended to only show the relationship between the tables so that we can work out a migration plan. They are NOT intended to be run on a production site! My typical warning of using it in so far as it is helpful and avoiding it in so far as it is not helpful doubly applies. With that said, I see the migration as involving 2 steps. Migrating the grade events (1.8) to grade items (1.9) and grade event grades (1.8) to grade grades. What may complicate things or what I am least clear on at this point is moving from grade category (1.8) to grade categories (1.9). I recognize that the current scheme is a bit tenuous.

Step 1--Install Moodle 1.9

The first step is to download and install the latest build of Moodle 1.9.x. Unfortunately, in the installation process Moodle will drop some of the tables that you need for the migration. To avoid this, before you log into Moodle with your web browser edit moodle/lib/db/upgrade.php and comment out (or delete if you prefer) the lines that drop the gradebook tables quoted below. They should be approximately lines 2555 - 2570:

    if ($result && $oldversion < 2007100903) {
        $tables = array('grade_category',
                        'grade_item',
                        'grade_letter',
                        'grade_preferences',
                        'grade_exceptions');

        foreach ($tables as $table) {
            $table = new XMLDBTable($table);
            if (table_exists($table)) {
                drop_table($table);
            }
        }

        upgrade_main_savepoint($result, 2007100903);
    }

After making the edit, complete your installation (the conversion of files that Moodle does the first time you log in after installing the new files).

Step 2--Convert Database with MySQL Commands

Using your favorite MySQL tool (I recommend installing MySQL Admin which is phpMyAdmin adapted for Moodle) run each of the following commands on your Moodle database. Note that the commands below assume that you used mdl_ as the prefix for your moodle tables. If you did not, then you will need to do change all table names accordingly. At the end of this document, all of these commands are placed together so you can copy and paste the entire set of commands in one action.

Add all of the grade_events to grade_items

INSERT INTO mdl_grade_items (courseid, categoryid, itemname, itemtype, iteminfo, idnumber, grademax, grademin, timecreated, timemodified) SELECT gi.courseid as courseid, gi.category as categoryid, ge.name as itemname, 'manual' as itemtype, ge.description as iteminfo, concat('GBE-',ge.id) as idnumber, ge.grade as grademax, 0 as grademin, ge.timemodified as timecreated, ge.timemodified as timemodified FROM mdl_grade_item as gi, mdl_grade_events as ge WHERE gi.courseid=ge.course AND gi.modid=0 AND gi.cminstance=ge.id;

Replace the old categoryid with the new categoryid (where possible)

UPDATE mdl_grade_items as gi LEFT JOIN (mdl_grade_category as gc, mdl_grade_categories as gcs) ON (gi.categoryid=gc.id AND gi.courseid=gcs.courseid AND gc.name=gcs.fullname) SET gi.categoryid = gcs.id, gi.idnumber=replace(gi.idnumber,'GBE','GBI') WHERE gi.idnumber LIKE 'GBE%';

Assign a generic categoryid for any thing that was not categorized (it is presumed that the grade event was not previously categorised in the course it was in)

UPDATE mdl_grade_items as gi LEFT JOIN (mdl_grade_categories as gcs) ON (gi.courseid=gcs.courseid AND gcs.parent IS NULL) SET gi.categoryid = gcs.id WHERE gi.idnumber LIKE 'GBI%' AND gi.categoryid IS NULL;

-- I still had 2 items that were not categorised and would suggest creating the records in mdl_grade_categories and re-running the query above

Double-checking categories

INSERT INTO mdl_grade_categories (courseid, depth, fullname, timecreated, timemodified) SELECT DISTINCT courseid, 1 as depth, 'Course grade category' as fullname, unix_timestamp() as timecreated, unix_timestamp() as timemodified FROM mdl_grade_items WHERE idnumber LIKE 'GBI%' AND categoryid IS NULL;

Add the path

-- There may be a way to do this above but I do not know how to do it with the auto incrementing id field so I let it first insert the record and then I use the data

UPDATE mdl_grade_categories SET path = concat('/',id,'/') WHERE path IS NULL AND depth=1;

Populate the previously NULL categoryids

UPDATE mdl_grade_items as gi LEFT JOIN (mdl_grade_categories as gcs) ON (gi.courseid=gcs.courseid AND gcs.parent IS NULL) SET gi.categoryid = gcs.id WHERE gi.idnumber LIKE 'GBI%' AND gi.categoryid IS NULL;

-- I have used the grade event (1.8) id field concatenated with the GBE- string as the id number to easily identify the items as GBP items. After assigning a category the idnumber becomes GBI- even if it is NULL. This will also facilitate linking the items (1.9) and assigning the grades from events_grades to grade_grades. -- OK - now that hopefully all of the grade events have been turned into grade items and the new categories have been found or assigned let us go ahead and attempt to pull the grades over.

Copy scores from old tables into new tables

INSERT INTO mdl_grade_grades (itemid, userid, usermodified, finalgrade, timecreated, timemodified) SELECT gi.id as itemid, geg.userid as userid, geg.teacher as usermodified, geg.grade as finalgrade, geg.timemarked as timecreated, geg.timemarked as timemodified FROM mdl_grade_events_grades as geg, mdl_grade_items as gi WHERE replace(gi.idnumber,'GBI-','')=geg.event;

I have tested these queries on production data on a test server and it seems to work for me; however, I suspect there are other cases out there for which this may not be a total solution but my hope is that it is at least a step in the right direction. For those who are chomping at the bit, if you could test this with some production data on a test server and report back that would be most helpful. I appreciate any feedback, questions, critiques, etc. Peace - Anthony

Issues with grade aggregation

Grade aggregation is fine if teachers selected to "Use Percent" to assign letter grade--all assignments and categories will be switched to "Simple Mean of Grades" and the calculated grade will be unchanged. However, if teachers selected "Use Weight" to assign letter grade, all assignments and categories for the class will be set to "Weighted Mean of Grades" but all assignments will have a null weighting factor.

Based on the original suggestion by Bob Puffer, the following should fix grade aggregation problems:

First, set all assignment entries that are NOT categories to "Simple Weighted Mean of Grade" with the MySQL command below:

UPDATE mdl_grade_categories SET aggregation = 11 WHERE depth != 1;

It is now necessary to force Moodle to reaggregate grades. This can be accomplished with the following steps:

1. Go into Site Administration->Grades->General Settings
2. Toggle the "Include Scales in Aggregation" checkbox on or off
3. Save Changes. It will take a while for grades in the system to recalculate.
4. Toggle the "Include Scales in Aggregation" back to its original position before step 3
5. Save Changes again.

Summary of SQL Commands for Migration from GBPv2 in Moodle 1.8 to Moodle 1.9

For the brave, here are all of the SQL commands combined so you can copy and paste just once:

INSERT INTO mdl_grade_items (courseid, categoryid, itemname, itemtype, iteminfo, idnumber, grademax, grademin, timecreated, timemodified) SELECT gi.courseid as courseid, gi.category as categoryid, ge.name as itemname, 'manual' as itemtype, ge.description as iteminfo, concat('GBE-',ge.id) as idnumber, ge.grade as grademax, 0 as grademin, ge.timemodified as timecreated, ge.timemodified as timemodified FROM mdl_grade_item as gi, mdl_grade_events as ge WHERE gi.courseid=ge.course AND gi.modid=0 AND gi.cminstance=ge.id; 
UPDATE mdl_grade_items as gi LEFT JOIN (mdl_grade_category as gc, mdl_grade_categories as gcs) ON (gi.categoryid=gc.id AND gi.courseid=gcs.courseid AND gc.name=gcs.fullname) SET gi.categoryid = gcs.id, gi.idnumber=replace(gi.idnumber,'GBE','GBI') WHERE gi.idnumber LIKE 'GBE%'; 
UPDATE mdl_grade_items as gi LEFT JOIN (mdl_grade_categories as gcs) ON (gi.courseid=gcs.courseid AND gcs.parent IS NULL) SET gi.categoryid = gcs.id WHERE gi.idnumber LIKE 'GBI%' AND gi.categoryid IS NULL; 
INSERT INTO mdl_grade_categories (courseid, depth, fullname, timecreated, timemodified) SELECT DISTINCT courseid, 1 as depth, 'Course grade category' as fullname, unix_timestamp() as timecreated, unix_timestamp() as timemodified FROM mdl_grade_items WHERE idnumber LIKE 'GBI%' AND categoryid IS NULL; 
UPDATE mdl_grade_categories SET path = concat('/',id,'/') WHERE path IS NULL AND depth=1;
UPDATE mdl_grade_items as gi LEFT JOIN (mdl_grade_categories as gcs) ON (gi.courseid=gcs.courseid AND gcs.parent IS NULL) SET gi.categoryid = gcs.id WHERE gi.idnumber LIKE 'GBI%' AND gi.categoryid IS NULL; 
INSERT INTO mdl_grade_grades (itemid, userid, usermodified, finalgrade, timecreated, timemodified) SELECT gi.id as itemid, geg.userid as userid, geg.teacher as usermodified, geg.grade as finalgrade, geg.timemarked as timecreated, geg.timemarked as timemodified FROM mdl_grade_events_grades as geg, mdl_grade_items as gi WHERE replace(gi.idnumber,'GBI-','')=geg.event; 
UPDATE mdl_grade_categories SET aggregation = 11 WHERE depth != 1;