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.

Let the Moodle upgrader do the work for you

The first step in moving from Moodle 1.8 to 1.9 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. Thus it is necessary to preserve that data in one way or another if you hope to merge it into the gradebook for Moodle 1.9. The easiest way to preserve that data is to comment out the section of moodle/lib/db/upgrade.php responsible for dropping the tables. The comment out option is described below in "Step 1", but you can make it even easier on yourself by adding the MySQL commands to moodle/lib/db/upgrade.php so the data conversion occurs automagically and then drops the unnecessary tables.

To set up automatic conversion, unzip the latest version of Moodle 1.9.x and open the new moodle/lib/db/upgrade.php file in your favorite text editor. Find the code indicated in the table below and replace it with the replacement code listed. Save the file, make sure you have backed up your MySQL database, copied moodle/config.php from your previous Moodle folder into the new Moodle folder, and changed the names of the folders so the new Moodle folder has the name Moodle will be looking for. Finally, using your web browser go to <your moodle path>/admin to begin the upgrade process.

Text to be replaced in moodle/lib/db/upgrade.php (approximately lines 2555 - 2570):

/// drop old gradebook tables
    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);
    }

Text to put in its place:

///Convert GradebookPlus v2 data, then drop old gradebook tables
    if ($result && $oldversion < 2007100903) {

        {
            ///Add all of the grade_events to grade_items
            $sql = "INSERT INTO {$CFG->prefix}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 {$CFG->prefix}grade_item as gi, {$CFG->prefix}grade_events as ge 
                WHERE gi.courseid=ge.course AND gi.modid=0 AND gi.cminstance=ge.id"; 
            execute_sql($sql);

            ///Replace the old categoryid with the new categoryid (where possible) 
            $sql = "UPDATE {$CFG->prefix}grade_items as gi LEFT JOIN ({$CFG->prefix}grade_category 
                as gc) ON (gi.categoryid=gc.id) LEFT JOIN ({$CFG->prefix}grade_categories as gcs) 
                ON (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%'"; 
            execute_sql($sql);

            ///Assign a generic categoryid for any thing that was not categorized 
            $sql = "UPDATE {$CFG->prefix}grade_items as gi LEFT JOIN ({$CFG->prefix}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"; 
            execute_sql($sql);
 
            ///Double-checking categories
            $sql = "INSERT INTO {$CFG->prefix}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 {$CFG->prefix}grade_items WHERE idnumber 
                LIKE 'GBI%' AND categoryid IS NULL"; 
            execute_sql($sql);

            ///Add the path 
            $sql = "UPDATE {$CFG->prefix}grade_categories SET path = concat('/',id,'/') WHERE path 
                IS NULL AND depth=1"; 
            execute_sql($sql);

            ///Populate the previously NULL categoryids
            $sql = "UPDATE {$CFG->prefix}grade_items as gi LEFT JOIN ({$CFG->prefix}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"; 
            execute_sql($sql);

            ///Copy scores from old tables into new tables 
            $sql = "INSERT INTO {$CFG->prefix}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 {$CFG->prefix}grade_events_grades as geg, 
                {$CFG->prefix}grade_items as gi WHERE replace(gi.idnumber,'GBI-','')=geg.event"; 
            execute_sql($sql);

            ///Set aggregation within each category to simple weighted mean
            $sql = "UPDATE {$CFG->prefix}grade_categories SET aggregation = 11 WHERE depth != 1"; 
            execute_sql($sql);
        }
        
        $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);
    }

That should do it! Do be aware, however, that the new ability to ignore blank grades will change averages if it is used. Also, extra credit is pretty much broken so extra credit assignments and assignments with scores in excess of 100% will now affect the grades differently.

Step 1--Install Moodle 1.9

If you prefer to do the conversion manually, you must first preserve your GBPv2 data through the upgrade process. Before you log into Moodle with your web browser (to start the upgrade process) 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 this the first time you log in after installing the new files) and move on to step 2.

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 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) ON (gi.categoryid=gc.id) LEFT JOIN (mdl_grade_categories as gcs) ON (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 assignments and categories that are NOT the course grade 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 (but not brave enough to let upgrade.php do the work for you), 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) ON (gi.categoryid=gc.id) LEFT JOIN (mdl_grade_categories as gcs) ON (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;