Hi all
I hope someone can help with this. I have inherited the running of our Moodle when the previous manager left last year. Several years ago from gleaning code on these forums and hours of trial and error he put together a series of scripts that would produce the stats we needed for management.
After he left I streamlined them a little putting them into one file and adding a split stored procedure to format the output csv file. It may not have been overly pretty but it worked well.
In August last year I rolled our Moodle form 2.4.x to 2.9.1 (and have just gone to 2.9.5 this week as well). Of course, in that time the mdl_log table is no longer used and when I went to run these stats again they of course didnt do anything.
I have been playing with the code I had before and mapping it to the new mdl_logstore_standard_log table and I THINK I have got it working. However the fact it produces results doesnt actually mean it is producing the figures I think it is!
The old code I have been using before is:
INSERT INTO views (id,role,views)
SELECT l.course, r.roleid, count(distinct l.id) AS Views
FROM mdl_log l, mdl_user u, mdl_role_assignments r
WHERE l.userid = u.id and l.action like '%view%'
AND r.contextid= (SELECT id FROM mdl_context WHERE contextlevel=50 AND instanceid=l.course )
AND r.userid = u.id
GROUP BY l.course,r.contextid, r.roleid;
After my attempts at conversion I now have:
INSERT INTO views (id,role,views)
SELECT l.courseid, r.roleid, count(distinct l.id) AS Views
FROM mdl_logstore_standard_log l, mdl_user u, mdl_role_assignments r
WHERE l.userid = u.id and l.action like '%view%'
AND r.contextid= (SELECT id FROM mdl_context WHERE contextlevel=50 AND instanceid=l.courseid)
AND r.userid = u.id
GROUP BY l.courseid, r.contextid, r.roleid;
Can anyone look at these and see if they believe they are comparable? I am getting results back that LOOK like they should do - but I just want to check if I am missing something and making wrong assumptions as to the purpose of some fields.
Basically I have just replaced mdl_log with mdl_logstore_standard_log and replaced the fields course with courseid which SEEMS to be all that is needed.
Am I missing something or does this look right? (obviously this is just a small part of the script - but it is the only part that is using the log file)
PS: im not concerned if its clean and efficient - just if it looks like its doing the same thing.
Many thanks
Tim Gildersleeve
VLE Developer
Bradford College