Hello all,
we are developing a customized Attendance plugin which enables us to export a list of teachers, with their completed hours and their hourly rates (wages). We are doing good progress but are stuck on getting the right amout of hours to export. At the moment the export gives us total course hours (not just the completed ones). Do we have anyone here who would be able and willing to take a look at the code and point us in the right direction? We'd be happy to share the plugin if someone has the need for the same functionality.
Here is the query:
SELECT DISTINCT c.id,
SessionData.userid,
SessionData.firstname,
SessionData.lastname,
c.fullname AS name,
c.startdate, c.enddate,
SessionData.completedtime,
SessionData.totaltime,
SessionData.wage
FROM course c
JOIN attendance att
ON att.course = c.id
JOIN (SELECT sct.userid,
sct.firstname,
sct.lastname,
sct.attendanceid,
sct.completedtime,
sct.totaltime,
sct.wage
FROM (SELECT users.id AS 'userid',
users.firstname, users.lastname,
ats.attendanceid,
SUM(IF((acronym = 'P' AND atl.id > 0), duration, 0)) AS completedtime,
SUM(ats.duration) AS totaltime,
atw.wage
FROM attendance_sessions ats
JOIN (SELECT DISTINCT usr.id, usr.firstname, usr.lastname
FROM user usr
JOIN role_assignments ra ON ra.userid = usr.id
JOIN role role ON role.id = ra.roleid
WHERE role.shortname IN ('teacher', 'editingteacher')) AS users
LEFT JOIN attendance_log atl ON atl.sessionid = ats.id AND atl.studentid = users.id
JOIN attendance_wages atw
ON atw.attendanceid = ats.attendanceid AND atw.tutor_userid = users.id
JOIN attendance_statuses sts
ON (sts.attendanceid = ats.attendanceid
AND sts.id = atl.statusid
AND sts.deleted = 0
AND sts.visible = 1)
GROUP BY atl.id) AS sct) AS SessionData
ON 1 = 1
JOIN user_enrolments ue ON ue.userid = SessionData.userid
JOIN enrol enr ON enr.id = ue.enrolid
WHERE enr.courseid = c.id
And the output, where you can see that the competed time equals total time whoch should not be the case.
| id | userid | firstname | lastname | name | startdate | enddate | completedtime | totaltime | wage |
| 1126 | 9 | Špela | Test | TEST Špela - ne pipkaj |
1657663200 | 1660089600 | 14400 | 14400 | 15 |
| 1139 | 9 | Špela | Test | TEST IZVOZ | 1654034400 | 1656460800 | 14400 | 14400 | 15 |
| 1126 | 9 | Špela | Test | TEST Špela - ne pipkaj |
1657663200 | 1660089600 | 7200 | 7200 | 15 |
| 1139 | 9 | Špela | Test | TEST IZVOZ | 1654034400 | 1656460800 | 7200 | 7200 | 15 |
| 1126 | 9 | Špela | Test | TEST Špela - ne pipkaj |
1657663200 | 1660089600 | 6300 | 6300 | 15 |
| 1139 | 9 | Špela | Test | TEST IZVOZ | 1654034400 | 1656460800 | 6300 | 6300 | 15 |
Any ideas