I was hoping someone can help me out here.
I'm ssing moodle 1.8.8, LAMP server.
AIM: To show all users of a course displaying the 'first log entry time' in a list.
To break it down :-
List the users of a specified course with Role IDs: 5, 24
Show the first log entry 'time/date' next to each user from the mdl_log table.
Users are from a specified Course ID: 71
Output user details in a list with first log entry to the course
So far I have some MySQL that will output user details found in the mdl_course and mdl_user table using table joins.
The Time fields in the mdl_user table consist of 'firstaccess' , 'lastaccess', 'lastlogin', 'currentlogin'. However I notice the first access fields are almost all unpopulated (value=0). As a time stamp to human readable this would read '1st Jan 1970' for each user, which is no good.
I need to output each users first access to a specific course and it doesn't seem I can use the firstaccess/login or other time fields for this.
I used the below MySQL to output users of a course and some user details including the time fields. Here's the code :
#Command to show students from a particular course for 1.8.8 Moodle for multiple roles - include user access details
SELECT c.id, c.fullname, u.id, u.username, u.firstname, u.lastname, u.email, u.country, u.firstaccess, u.lastaccess, u.lastlogin, u.currentlogin, u.timemodified
FROM mdl_course c
LEFT OUTER JOIN mdl_context cx ON c.id = cx.instanceid
LEFT OUTER JOIN mdl_role_assignments ra ON cx.id = ra.contextid
AND ra.roleid = '5'
OR ra.roleid = '24'
LEFT OUTER JOIN mdl_user u ON ra.userid = u.id
WHERE cx.contextlevel = '50'
AND c.id = '71'
ORDER BY u.username
LIMIT 0 , 2000
As you can see this will output the following fields :-
|71||IOSH Managing Safely||13476||aaa||Aaaemail@example.com||NG||0||1277751411||1277743064||1277748727||1276595533|
|71||IOSH Managing Safely||10555||aab||Aabfirstname.lastname@example.org||GB||0||1277726522||1267767065||1277716384||1252074863|
If I output this as a .csv and load in to Excel I can then use the following equation to convert this in to human readable time (columns formatted in dd/mm/yyyy) :
( where '@' = the column letter | and formatted in dd/mm/yyyy format )
So then the excel output will look like this :-
|Course ID||fullname||User ID||username||firstname||lastname||country||FIRST ACCESS||LAST ACCESS||LAST LOGIN||CURRENT LOGIN||PROFILE MODIFIED|
|71||IOSH Managing Safely||13476||aaa||Aaaemail@example.com||NG||01/01/1970||28/06/2010||28/06/2010||28/06/2010||15/06/2010|
|71||IOSH Managing Safely||10555||aab||Aab
Thing is this doesn't show me what I need as the values in first access and other time fields are for the most part set to = 0. I can't use the other time fields either.
It seems that moodle doesn't store a users first access unless the user has created the account using self based email authentication (unless i'm mistaken), so the first access field doesn't get populated with the time in seconds and is always = 0.
So the only way to get the details I need is from the mdl_log table.
I need to parse through the logs until I find the very first entry for a user ID within the specified course and list this date next to each course user outputted as a list. I can then convert the dates using the same method in an Excel document.
Thing is, i'm not sure how to do that. I think i'll have to have some PHP to go through each row for each user ID to find the lowest time entry for each user within the specified course.
This would then allow me to display the whole list of course users and accurately show a First Course Access time field next to each user, along with some additional users details, i.e. email, firstname lastname etc..
Can any one help me out here please, i'm finding this a bit tricky.
Thank you in advance