Configurable reports block (plugin)

I'm still working on this.

I was able to get a query of users with no courses, but it came up blank. I then realised that Moodle considers the Dashboard/MyMoodle page to be a course, so everyone is enrolled in a class with the course id =1. But when I try to find the users listed for courseid=1, it comes up empty.

Below is the slightly modified of Gemma Lesterhuis' report. When I put in the id number of any course, it returns the users enrolled in that class, but if I put c.id=1 there, I get no users.

Here is my confusion. The course with id=1 is listed as Moodle when you look at a list of all courses, but when I try to find out who is enrolled in that course, it returns no users.

Help?

SELECT
u.id,
u.username AS 'Username',
u.firstname AS'Firstname',
u.lastname AS 'Lastname',
u.email AS 'email',
cc.name AS 'category',
c.fullname AS 'course',
FROM_UNIXTIME(firstaccess, '%d-%m-%Y') AS 'First login',
FROM_UNIXTIME(lastaccess, '%d-%m-%Y') AS 'Last login'
FROM prefix_user as u
INNER JOIN prefix_user_enrolments AS e ON e.userid = u.id
INNER JOIN prefix_enrol AS en ON en.id = e.enrolid
INNER JOIN prefix_course AS c ON c.id = en.courseid
INNER JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE u.deleted = 0
AND u.suspended = 0
AND c.id = 18184 /* this returns users, but c.id=1 does not*/
ORDER BY cc.name, c.fullname

I think I have something that if not perfect, at least covers what I need.

I realised 3 things:

     1. Everyone has the role 'authenticated user' until you eliminate all other roles, then no one does.

      2. Everyone is enrolled in the course with id=1 (the Front page), until you eliminate all other courses, then no one is enrolled.

       3. Moodle does not seem to want to admit that someone who has never logged in could be enrolled in a class.
I'd love to be wrong on those.

This query returns everyone who has never logged in and was created before the oldest of the regular courses was deleted, so they would be in no currently available academic courses. My guess is that if they weren't modified after they were created, they won't have any active classes on the server. The date shown is 01/01/15 at 00:00 US Central Time
SELECT
id,
username,
firstname,
lastname,
email,
FROM_UNIXTIME (firstaccess, '%d/%m/%Y') AS 'First Access',
FROM_UNIXTIME (lastaccess, '%d/%m/%Y') AS 'Last Access',
FROM_UNIXTIME (lastlogin, '%d/%m/%Y') AS 'Last Login',
FROM_UNIXTIME (timecreated, '%d/%m/%Y') AS 'Time Created',
FROM_UNIXTIME (timemodified, '%d/%m/%Y') AS 'Time Modified'  
FROM prefix_user
WHERE lastaccess=0
AND timecreated < 1420092000
AND timecreated=timemodified
ORDER BY lastname, firstname

This one shows the classes that people who were created and were last modified before my cut-off date. This brought me a list of inactive clubs and groups. Given that this list is very short, we can check for content before deleting.

SELECT DISTINCT
u.username AS 'User',
u.firstname AS 'First Name',
u.lastname AS 'Last Name',
FROM_UNIXTIME (u.lastaccess, '%m/%d/%Y') AS 'Last User Access',
c.id AS 'COURSE ID',
c.fullname AS 'Course Name',
FROM_UNIXTIME (c.timecreated, '%m/%d/%Y') AS 'Date Course Created'

FROM prefix_course AS c
JOIN prefix_course_categories AS ca
ON c.category = ca.id
JOIN prefix_user_lastaccess AS la
ON c.id = la.courseid
JOIN prefix_user AS u
ON la.userid = u.id
JOIN prefix_context AS ctx
ON c.id = ctx.instanceid
INNER JOIN prefix_role_assignments AS ra
ON ra.contextid = ctx.id

WHERE ctx.instanceid = c.id
AND u.timecreated < 1420092000
AND u.timecreated=u.timemodified
AND u.lastaccess < 1420092000

ORDER BY  u.lastname, u.firstname