Generate course, topic and attendance report

Generate course, topic and attendance report

by Moh Ilhami -
Number of replies: 0

I want to generate course, topic and attendance report like this:

Course, Topic, Date, Student, Status
Basic Programming, Topic 01, 2021-04-01, student01, P (present)
Basic Programming, Topic 01, 2021-04-01, student02, P
Basic Programming, Topic 02, 2021-04-02, student01, P
Basic Programming, Topic 02, 2021-04-02, student02, P
Basic Programming, Topic 03, 2021-04-03, student01, P
Basic Programming, Topic 03, 2021-04-03, student01, P

I have these queries:
(01)

select * from prefix_attendance a 
join prefix_attendance_sessions b on a.id = b.attendanceid where course = 7351

(02)

SELECT a.id, a.name, a.section, b.* FROM prefix_course_sections a 
join prefix_course_modules b on a.id = b.section where a.course = 7351 and a.section=4

(03)

SELECT c.id id_course, c.idnumber, att.description,
    from_unixtime( att.timemodified ) timemodified,
    u.firstname AS 'First Name', u.lastname AS 'Last Name',u.username,
    u.Institution AS 'District',c.shortname, c.fullname AS 'Training',
    DATE_FORMAT(FROM_UNIXTIME(att.sessdate),'%d %M %Y')AS DATE,
    FROM_UNIXTIME(att.sessdate) sessdate,
    attst.acronym, 
    attlog.id id_attendance_log,
    FROM prefix_attendance_sessions AS att
    JOIN prefix_attendance_log AS attlog ON att.id = attlog.sessionid
    JOIN prefix_attendance_statuses AS attst ON attlog.statusid = attst.id
    JOIN prefix_attendance AS a ON att.attendanceid = a.id
    JOIN prefix_course AS c ON a.course = c.id
    JOIN prefix_user AS u ON attlog.studentid = u.id
    #LEFT JOIN prefix_course_sections AS cs ON cs.course = c.id  AND cs.section > 0 AND cs.section <=14
    #LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id
    WHERE  c.category =  17
        and c.id=7351
        and c.startdate >= unix_timestamp('2021-01-01') 
        

I can not find relation between course section (Topic) query(02) and its attendance record in query (01)
Join with course_sections in query (03) is not the intended result because it is a cartesian join.

From the moodle application, I can see that attendance can be organized per topic , for example if you visit your moodle site at
http://moodle/mod/attendance/manage.php?id=105934


Please help.

Kind Regards


M Ilhami

Average of ratings: -