Last active
May 1, 2020 21:30
-
-
Save bishwanathjha/1fe53aa095e563772fdf1245417a885b to your computer and use it in GitHub Desktop.
Moodle Query to get Attendance Student wise Course Wise
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Moodle get student wise attendence for each courses | |
-- To filter data you can put the parent course id and start {start_date_epoch} and end {end_date_epoch} in where clause | |
select | |
l.id as logid, | |
c.fullname as course, | |
CONCAT(FROM_UNIXTIME(s.sessdate,"%d %b, %Y %h:%i %p"), ' - ', FROM_UNIXTIME(s.sessdate + s.duration ,"%h:%i %p")) as session_title, | |
-- s.description as session_desc, | |
u.id as student_id, | |
u.username, | |
u.firstname as first_name, | |
u.lastname as last_name, | |
st.acronym, | |
CASE | |
WHEN l.studentid = l.takenby THEN "Student" | |
ELSE "Teacher" | |
END as taken_by, | |
FROM_UNIXTIME(l.timetaken) as time_taken, | |
l.remarks | |
-- l.ipaddress | |
from mdl_attendance_log as l | |
left join mdl_attendance_statuses as st on st.id = l.statusid | |
left join mdl_user as u on u.id = l.studentid | |
left join mdl_attendance_sessions as s on s.id = l.sessionid | |
left join mdl_attendance as a on a.id = s.attendanceid | |
left join mdl_course as c on c.id = a.course | |
left join mdl_course_categories as cc on cc.id = c.category | |
where s.sessdate between {start_date_epoch} and {end_date_epoch} and cc.path like '/{parent_course_category_id}/%' | |
order by l.sessionid, l.id desc; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment