Custom Reports for Badges - Get Some Here

Custom Reports for Badges - Get Some Here

by Randy Thornton -
Number of replies: 11
Picture of Documentation writers

Badges are great, but there is no way for an admin to see all the badges in all the courses. We need a report for that.

At a couple of recent Moots, I showed some demos of reports I had made to fill that need. Attendees had asked me to make some additions to the reports, which I have done.

These are made for and tested with the Configurable Reports block. Tested in 2.5.0 and 2.5.1 with lastest version of that block.

I am adding the code over in ad-hoc contributed reports, where you should look for any updates, additions, or newer versions:

http://docs.moodle.org/25/en/ad-hoc_contributed_reports#Badges

I have added three basic ones to start:

1) Report of all ISSUED badges by USER, both site level and all courses, including criteria type, date issued, and a link to that issued badge's page so you can see all the details you may need. (See sample below.)

2) Shows a list of ALL badges in the system with name, course if one, whether active or not, and the number of times the badge has been issued. (See second sample below.)

3) A simple Leaderboard, listing usernames and how many badges they have earned systemwide.

Enjoy!

- Randy

 

Attachment screenshot_461.png
Attachment screenshot_463.png
Average of ratings: Useful (3)
In reply to Randy Thornton

Re: Custom Reports for Badges - Get Some Here

by Sumanta Ghosh -

Hi Randy,

I am trying to create a custom report page with all students ranked as per their grades in a course. I want to be able to show their earned badges as well but just need to know if that's possible.
I am not a SQL guy so please don't mind if this is a dumb question.
I can access student earned badgeid from mdl_badge_issued table which is working fine right now with a LEFT JOIN. but not able to find a way to show badge images there.

Any help is really appreciated.
I am using 2.5.1+

Thanks,
Sumanta

Average of ratings: -
In reply to Sumanta Ghosh

Re: Custom Reports for Badges - Get Some Here

by Randy Thornton -
Picture of Documentation writers

I doubt there's a way to show the badge image itself in a Configurable Reports output, though you can have a link to the awarded badge page (it's in one of my queries somewhere.)

The image itself surely lives somewhere in the file system. I'll take a look and see what I can come up with.

 

Average of ratings: -
In reply to Sumanta Ghosh

Re: Custom Reports for Badges - Get Some Here

by Yuliya Bozhko -

Hi Sumanta,

Do you just want to display badge image or do you want to display baked badge which can be saved?

I think badge image is possible to retrieve from database. A fully baked badge might be more complicated as it is created in user context...

Non user specific images are served through URL: $CFG->wwwroot/pluginfile.php/XX/badges/badgeimage/YY/f1

where XX is context id and YY is badge id. Context id can be found through mdl_context table where CONTEXT_SYSTEM - 10 and instanceid - 0 for site badges (type 1), and CONTEXT_COURSE - 50 and instanceid - COURSE ID for course badges (type 2).

Not sure how we can put all these things together, but it is definitely possible... Maybe Randy can give it a go? wink

Yuliya

Average of ratings: -
In reply to Yuliya Bozhko

Re: Custom Reports for Badges - Get Some Here

by Randy Thornton -
Picture of Documentation writers

Yes, I can give it a go smile

Here's the code to make that link to the image file - the magic happens in the CONCAT statement. It's not a baked badge, of course, just the image, but that may be all you need.

Yuliya is right about not being able to able show that in a SQL report though: there's just no way to present the image. You'd need to use php and do a block or a report to get exactly what you want. Or you could export the URL into a spreasheet and do something with it there.

SELECT u.username, b.name AS badgename, b.image AS imageid,

CONCAT ('<a target="_new" href="%%WWWROOT%%/pluginfile.php/',f.contextid,'/badges/badgeimage/',f.itemid,'/',SUBSTRING_INDEX(f.filename,'.',1 ),'">link</a>') AS badgeimage


FROM prefix_badge_issued AS d
JOIN prefix_badge AS b ON d.badgeid = b.id
JOIN prefix_user AS u ON d.userid = u.id
JOIN prefix_files AS f ON b.image = f.id
WHERE b.status = 1 OR b.status = 3
ORDER BY u.username

 

Attachment screenshot_518.png
Average of ratings: Useful (2)
In reply to Randy Thornton

Re: Custom Reports for Badges - Get Some Here

by Allison Sturgess -

Hi Randy,

This is exactly what I am looking for, however will it work with Moodle 2.6 and if so, where exactly should I be putting this coding, I am a novice!

Thanks for your help.

 

Ally

Average of ratings: -
In reply to Allison Sturgess

Re: Custom Reports for Badges - Get Some Here

by Randy Thornton -
Picture of Documentation writers

Ally,

This should work with Moodle 2.6 but I have not tried it. It is fine in 2.5.x.

To use this, you first need to install the Configurable Reports bloc: https://moodle.org/plugins/search.php?s=configurable+reports&search=Search+plugins

There you can create a report of type SQL and paste in the code above to get the report.

Randy

Average of ratings: -
In reply to Randy Thornton

Re: Custom Reports for Badges - Get Some Here

by Randy Thornton -
Picture of Documentation writers

Ally, this will not work in 2.6, since the images for the badge are now attachments, so the database has changed where the images are concerned. I'll have to figure out how that works to update that script to include the links to the image.

Average of ratings: -
In reply to Randy Thornton

Re: Custom Reports for Badges - Get Some Here

by Maurice Moore -
Hi Randy

Have you been able to get this working with Moodle 2.6?

Cheers,  Maurice
Average of ratings: -
In reply to Maurice Moore

Re: Custom Reports for Badges - Get Some Here

by Randy Thornton -
Picture of Documentation writers

Unfortunately I haven't had the time to look at badges again. But since I will be awarding badges at my sessions during the upcoming Mountain Moot, whose site will be running on 2.6, I will have a chance to look at it soon.

Average of ratings: -
In reply to Allison Sturgess

Re: Custom Reports for Badges - Get Some Here

by Randy Thornton -
Picture of Documentation writers

Ally and Maurice,

Here, give this a try in 2.6. It isn't pretty and it may be buggy so there's a couple of columns of data you could take out, but it is working for me with limited sample data in 2.6. Let me know if it works for you okay.

-Randy

SELECT u.username, b.name AS badgename, f.contextid, f.itemid,
CONCAT ('<a target="_new" href="%%WWWROOT%%/pluginfile.php/',f.contextid,'/badges/badgeimage/',f.itemid,'/',SUBSTRING_INDEX(f.filename,'.',1 ),'">link</a>') AS badgeimage
FROM prefix_badge_issued AS d
JOIN prefix_badge AS b ON d.badgeid = b.id
JOIN prefix_user AS u ON d.userid = u.id
JOIN prefix_files AS f ON b.id = f.itemid
WHERE (b.status = 1 OR b.status = 3) AND (f.component = 'badges' AND f.filename = 'f1.png')
ORDER BY u.username


Average of ratings: -
In reply to Randy Thornton

Re: Custom Reports for Badges - Get Some Here

by Bob Derezinski -

Is it possible to get a report of who earned a badge, if the badge has expired and how long the badge has been expired?

Average of ratings: -