I am trying to create a report that will
Only shows students who are within their course enrolment dates, in the role of student, who have not logged into both the courses they are enrolled into (CourseIDs in Moodle are 34 and 35).
Student has to be enrolled into both these courses and have not accessed both for 30days:
This is the query I am using
SELECT DISTINCTROW
user2.id AS ID,
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
user2.phone1 AS Phone,
c.fullname AS Course,
g.name AS Groupname,
r.name AS Role,
FROM_UNIXTIME(ue.timestart,'%D %M %Y') AS CourseEnrolmentDate,
FROM_UNIXTIME(ue.timeend,'%D %M %Y') AS CourseExpiryDate,
IF (ul.timeaccess IS NULL,'Never',DATE_FORMAT(FROM_UNIXTIME(ul.timeaccess),'%D-%M-%Y')) AS LastAccessed
FROM prefix_user_enrolments AS ue
JOIN prefix_enrol AS e ON e.id = ue.enrolid
JOIN prefix_course AS c ON c.id = e.courseid
JOIN prefix_user AS user2 ON user2.id = ue.userid
JOIN prefix_role_assignments ra ON ra.userid = user2.id
JOIN prefix_role r ON r.id = ra.roleid AND r.shortname = 'student'
LEFT JOIN prefix_user_lastaccess AS ul ON ul.userid = user2.id AND ul.courseid = c.id
JOIN prefix_groups g ON g.courseid = c.id
JOIN prefix_groups_members AS m ON m.userid = user2.id AND g.id = m.groupid
WHERE c.visible=1 AND DATEDIFF( NOW(),FROM_UNIXTIME(ul.timeaccess) ) > 30
AND e.status = 0 AND user2.suspended = 0 AND user2.deleted = 0
AND ue.status=0 AND e.courseid IN (34,35)
AND DATE_FORMAT(FROM_UNIXTIME(ue.timeend),'%Y/%m/%d') >=NOW()
GROUP BY user2.id, c.id
ORDER BY ID, Course ASC
The report is also pulling individuals who have not access one of the 2 courses which it shouldn't. Tried using AND for the e.courseid=34 AND e.courseid=35 but not working.
Desperately need help please!
