Gradebookplus

GradebookPlusV2 Patch

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.

Migration from GBPv2 to Moodle 1.9

I'm beginning this thread to discuss how we ought to move toward migrating the GBPv2 data from Moodle 1.8 to 1.9. I have started to play with some SQL queries and figured that sharing my thoughts might be helpful as we work toward this.

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.

Here is what I did on my test server with production data from last year. I installed Moodle 1.9 and pointed the install to the 1.8 data. I allowed Moodle 1.9 to completely run through the installation procedure and migrate the data to 1.9. After that, I opened up phpmyadmin and ran the following queries which at the very least did not seem to totally destroy everything (but use with caution and only if you have a good backup copy of your database):

-- As mentioned, the primary challenge is to make sure that the categoryid is correct

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

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.

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