Add additional information on attendance adhoc report

Add additional information on attendance adhoc report

by Cariosha Dav -
Number of replies: 3

How to I add the course name information to this report? Also, how do I change the field "taken by" to the instructor's name? Is there a way for this information to be globalized so that I can obtain this information for a student in multiple courses? I have been trying to find out the Moodle database structure so that I can figure out what to map the information in the following tables to in the course tables, grade items tables, etc. to get the professor's name to show up as well.  I have searched online but online found schema information for course and user basically. When I search for global attendance information, I really don't find a solution.  The conversations appear to just stop. Please help if you can.  I have copied the script below.  Thanks,

SELECT  prefix_user.id UserID, prefix_attendance_log.remarks,
prefix_user.firstname AS FirstName,
  prefix_user.lastname AS LastName,
from_unixtime(prefix_attendance_log.timetaken) AS Date, prefix_attendance_log.takenby, prefix_attendance_statuses.description AS STATUS

FROM prefix_attendance_log, role_assignments, mdl_role, mdl_context, mdl_course
 
INNER JOIN
prefix_user ON
prefix_attendance_log.studentid = prefix_user.id

INNER JOIN
prefix_attendance_statuses ON prefix_attendance_log.statusid = prefix_attendance_statuses.id

ORDER BY prefix_user.firstname

Average of ratings: -
In reply to Cariosha Dav

Re: Add additional information on attendance adhoc report

by Alan Hare -

What version of Moodle are you running?

Average of ratings: -
In reply to Cariosha Dav

Re: Add additional information on attendance adhoc report

by Cariosha Dav -

I kept working with it and came up with the following after searching fro other scripts that may work.  It seems to work, but the output is not correct.  The query pulls all courses that the student has taken and gives the same attendance status for each of the courses for each date that it shows.  For example, if Jane Doe is present on 08/01/2013 in BIO 100.  It will not only show  BIO 100 course, but it will show all the courses that Jane has ever taken and give it present status for 08/01/2013 for each of those courses.  I have been trying to work with the JOIN statement to see if that is where the problem resides.  Any assistance or feedback would be greatly appreciated.  We definitely need something to assist with this issue prior to school starting in a few weeks.   Thanks,

I have pasted the new script I am using below:

SELECT  prefix_user.id UserID, prefix_attendance_log.remarks,
prefix_user.firstname AS FirstName,
  prefix_user.lastname AS LastName,
from_unixtime(prefix_attendance_log.timetaken) AS Date,

prefix_attendance_log.takenby, prefix_attendance_statuses.description AS

STATUS, c.fullname

FROM prefix_attendance_log
 
left JOIN prefix_user ON prefix_attendance_log.studentid = prefix_user.id
left JOIN prefix_attendance_statuses ON prefix_attendance_log.statusid = prefix_attendance_statuses.id
left JOIN prefix_role_assignments ra  ON prefix_user.id = ra.userid
left  JOIN prefix_context cx ON cx.id = ra.contextid
left  JOIN prefix_course c ON c.id = cx.instanceid
   
   Where cx.contextlevel = '50'
  ORDER BY prefix_user.lastname

Average of ratings: -