Developing a Grade book plugin report

My customer has been after me for a couple months to build a summary report for the gradebook that showed data like this:

student name    last access   graded items complete - total graded items   current grade   % of course complete

This is information is avail. in native gradebook reports and / or in plugins, but as the teachers said, echoing my customer, "they spend a lot of time hunting for this information".  I spent some time last week in setting up a new report that *simply plugged into the existing Grades functionality.

I am reminded that moodle has key plugin points, including the gradebook!

Here is the highlights of my work and the end result.

The first thing I did was set up a development environment to play with.  I created a new moodle instance on my server called RD.  I can access the instance like this IPaddress/RD.  I used the same moodle version, 2.2.4, as my production version where I would be deploying.  I then used phpMyAdmin to easily duplicate the production DB, calling it student_copy by

1 - browsing to or choosing the DB to copy
2 - Clicking on Operations and providing a name for the copied DB

Once my new instance was in place connected to the newly copied DB, I was able to experiment with the code to figure out how to best solve the requirement.  I should note that I also use MySQLWorkbench A LOT to gain a better understanding of how to join moodle tables to get the data needed.  I spend a lot of time experimenting with joins and where statements.

Once I had a good idea on the sql to get what I needed, I set off to set up the plugin.

I started by copying the existing plugin folder at moodlepath\instancename\grade\report\overview.  I renamed the copied folder to progress. I updated the files where necessary from overview to progress.  While I was doing this, I turned on debugging to the screen with Developer detail!

When I had completed the updates to the files, I started on my index.php file, which is where the majority of the code would live.  I started with a blank index.php file and added only one html line
testing .  When I saw this rendering my testing header, I was ready to go to work.

Coding the index.php file.

Most of my library files were the same as the overview folder

    include_once('../../../config.php');
    require_once($CFG->libdir . '/gradelib.php');
    require_once $CFG->dirroot.'/grade/lib.php';
    require_once $CFG->dirroot.'/grade/report/user/lib.php';
    global $DB;
   $courseid = optional_param('id', $COURSE->id, PARAM_INT);

Added capability and page header

    require_capability('gradereport/progress:view', $context);
    print_grade_page_head($courseid, 'report', 'progress');

The count of the grade able items in the course.

   //gradeable items in the course  
   $count_of_gradeable_items = $DB->count_records_sql('
       select count(*)
       from mdl_grade_items
       where courseid = '.$courseid.'
       and itemtype = "mod"
   ');

 // var_dump($count_of_gradeable_items); // every developers good friend var_dump()

Course students, last access

       $result = $DB->get_records_sql('
    select u.firstname, u.lastname, u.id as userid, u.email, FROM_UNIXTIME(la.timeaccess,"%m/%d at %h:%i") as time
        from
        mdl_role_assignments ra,
        mdl_user u,
        mdl_course c,
        mdl_context cxt,
        mdl_user_lastaccess la
       
        where
        ra.userid = u.id
        and ra.contextid = cxt.id
        and cxt.instanceid = c.id
        and la.userid = u.id
        and c.id = '.$courseid.'
        and roleid = 5
       ');

Loop and output logic

    foreach($result as $rec){
        $us = userAttempsSum($courseid,$rec->userid);
       
        foreach($us as $fu){
            $usermaxscore =  $fu->maxscore;
            $userearnedscore = $fu->earnedscore;
            $currentgrade = round($userearnedscore/$usermaxscore,2)*100;
        }
       
        $usercount = userAttempsCount($courseid,$rec->userid);
        $percentComplete =  round($usercount/$count_of_gradeable_items,2)*100;
   
        $html .= '
'.$rec->firstname.' '.$rec->lastname./*.$studentnamelink. */'
'.$rec->time.'
'.$usercount.' of '.$count_of_gradeable_items.'

        '/*.$userearnedscore.' / '.$usermaxscore.'  '*/.$currentgrade.'%
'.$percentComplete.'%
';
    }

User attempt count and sum functions

    function userAttempsCount($courseid, $userid){
    global $DB;
    return
        $count_of_attempted_items = $DB->count_records_sql('
        select count(*)
        from mdl_grade_items gi
        join mdl_grade_grades gr on gr.itemid = gi.id
     
        where courseid = '.$courseid.' AND gr.userid = '.$userid.' AND rawgrade <> "null"
        ');
   }
  
       function userAttempsSum($courseid, $userid){
        global $DB;
   
        return $DB->get_records_sql('
        select    
       
        round(sum(gr.rawgrademax),0) AS MaxScore,
        round(sum(gr.finalgrade),0) AS EarnedScore
   
        from mdl_grade_items gi
        join mdl_grade_grades gr on gr.itemid = gi.id
     
        where courseid = '.$courseid.' AND gr.userid = '.$userid.' AND rawgrade <> "null"
        ');
   }