The report shows students, when they were enrolled into a course, along with the last time they accessed the course, a couple projected completion dates and a couple other items. It also contains a *status that I also custom designed for this moodle instance. The enrollment status is an indicator of whether the student is active, has completed or dropped the course. We need to keep the status for billing purposes primarily.
There are filtering options on the report that allow the display of enrollments by their status. I had included an option for all, which was the buggy option. I ended up simplifying the report a little buy removing that sort/filter option. It seems the only reason I had added that was to show a total enrollment count in the report, which is also required. After a bit of analysis, I realized I could remove the all option and simply leave the counts on the top of the report always displayed, and updating whenever a status changed.
This is the top of the report in moodle and shows the totals along with the 3 filter/sort links as they are now.
This is part of the detail report, which includes the E status column - these are all Active enrollments, and can be edited by clicking the letter.
In refactoring the code a bit, I had to come up with the best way to get counts for each of the enrollment status totals. I needed 4 totals at all times, active, completed, dropped and total.
My initial solution was to go to the DB 4 different times and return the count of the records. I would pass in the argument and the DB function would return a single result set. Well, the DB function joins to alot of tables and became a very expensive operation to execute 4 times. That is a general rule, BYW, calling the DB is an expensive operation. As the developer, I should try to minimize the trips I am taking to the DB which will improve my page load time. I was counting about 8 seconds for the page to reload after any change, which is just too long.
I returned to the drawing board with the intention of getting the counts I needed in a single trip to the DB. I was also able to reduce the tables involved in the join, since I was only after a count, I did not need all the detail of the original function.
I ended up writing a new function that called a helper function, that contained the SQL and stored the result in an array and then returned the array to the calling page. One call to the DB from the php page.
This is a glance at the functions.
function enrollmentCounts(){
//$counts = array();
$counts['active'] = countHelper(0);
$counts['complete'] = countHelper(1);
$counts['dropped'] = countHelper(2);
$counts['total'] = countHelper(3);
return $counts;
}
function countHelper($a){
global $DB;
/*enrollment status values
0 = active
1 = completed
2 = dropped
*/
if($a == 1){
$arg = "AND ue.status = 1 ";
}elseif($a == 2){
$arg = "AND ue.status = 2 ";
}elseif($a == 0){
$arg = "AND ue.status = 0 ";
}elseif($a == 3){
$arg = "AND ue.status IN (0,1,2) ";
}
return
$DB->get_records_sql('
SELECT count(ue.id) as item
FROM
mdl_user_enrolments ue
join mdl_enrol e on e.id = ue.enrolid
join mdl_user u on u.id = ue.userid
WHERE u.id NOT IN (3,199,187,432) AND u.id NOT BETWEEN 50 AND 96
'.$arg.'
');
}
I called the enrollmentCounts() function from the php page and then looped over the result to display the enrollment totals.
$ecounts = enrollmentCounts();
foreach($ecounts as $key => $value){
if($key == 'total'){
foreach($value as $i){
$te = $i->item;
}
}elseif($key == 'active'){
foreach($value as $i){
$ae = $i->item;
}
}elseif($key == 'dropped'){
foreach($value as $i){
$de = $i->item;
}
}elseif($key == 'complete'){
foreach($value as $i){
$ce = $i->item;
}
}
}
echo "The response time was improved from about 8 to 4 seconds per page load.
Total enrollments:
".$te."
Active:
".$ae."
Completed:
".$ce."
Dropped:
".$de."
No comments:
Post a Comment