Judy Hsu

Hi, thanks for trying to help out in advance!

I have done some research on "Ad-hoc contributed reports" and related moodle doc, but it doesn't seem that a lot of "outcome" related reports were discussed before.

I know that there are three outcome related DB tables in Moodle 2.7.x:

• mdl_grade_outcomes
• mdl_grade_outcomes_courses
• mdl_grade_outcomes_history

We are trying to write a simple query to generate four reports:

1. Averages of Outcomes (across different semesters): For example:

2. List of Outcomes:

3. Outcomes by Course:

and

4. Outcomes by Student

Have anyone here done similar reports before, and if so, could you please give us some tips/pointers about how to get started? Any help would be much appreciated. Thank you!

Thanks Neill! You the man!

OK quick question. Let's say that I just enabled the legacy logging system (mdl_log). Would 2.7 try to log into both mdl_log (old) and the logstore_standard_log (new) at the same time? Or, would it just totally stop logging into the logstore_standard_log table, and just using mdl_log??

I did the 2.7 upgrade about 2 months ago. Does this mean that if we want to continue to use these old queries (that use mdl_log), there is no way that we can retrieve those data by using the old queries, and really have to rewrite these queries? How easy/hard would it be to rewrite this query so that we can capture the data between two months ago and now?

Also at high level could you please highlight some of the differences (structure-wise) between the old mdl_log and the new mdl_logstore_standard_log tables? Thanks a million!!

Hi Neill,

Thanks so much for your help! So any recommendation about how we should revise the query so that we can use the logstore_standard_log table to achieve the same (or similar) result? Thanks a lot!

Hi, thanks in advance for your help...

We were running Moodle 2.5.x and recently upgraded to 2.7.x. For some reasons, some of our old database queries stopped working. These queries were working perfectly in 2.5.x before, using ad-hoc database query 3rd party plugin:

SELECT lo.course AS Course_ID, co.shortname AS Course_ShortName, co.fullname AS Course_FullName,r.name, COUNT(r.name) as Forum_Posts

FROM prefix_log lo

INNER JOIN prefix_course co ON (co.id = lo.course)

INNER JOIN prefix_user us ON (lo.userid = us.id)

INNER JOIN prefix_role_assignments ra ON (ra.userid = us.id)

INNER JOIN prefix_context ctxt ON (ctxt.id = ra.contextid AND

ctxt.instanceid = co.id AND ctxt.contextlevel = 50)

INNER JOIN prefix_role r ON (r.id  = ra.roleid)

WHERE lo.action LIKE '%add%' AND (lo.url LIKE '%discuss%' OR lo.url LIKE '%view%') AND FROM_UNIXTIME(lo.time) > '2015-05-03' AND FROM_UNIXTIME(lo.time) < '2015-05-09' AND (co.shortname LIKE '%15/UA%' OR co.shortname LIKE '%15/UB%' OR co.shortname LIKE '%15/SL%' ) AND lo.course <> 1 AND lo.module = 'forum'

GROUP BY Course_ID, r.name

ORDER BY co.fullname

and

SELECT lo.course AS Course_ID, co.shortname as Course_ShortName, co.fullname AS Course_FullName, us.firstname as Instructor_FirstName, us.lastname AS Instructor_LastName,  COUNT(DISTINCT(FROM_UNIXTIME(lo.time, '%Y-%m-%d'))) AS Days_Logged_In

FROM prefix_log lo

INNER JOIN prefix_course co ON (co.id = lo.course)

INNER JOIN prefix_user us ON (lo.userid = us.id)

INNER JOIN prefix_role_assignments ra ON (ra.userid = us.id)

INNER JOIN prefix_context ctxt ON (ctxt.id = ra.contextid AND ctxt.instanceid = co.id AND ctxt.contextlevel = 50)

INNER JOIN prefix_role r ON (r.id  = ra.roleid)

WHERE lo.course <> 1 AND r.name='Instructor'

AND (FROM_UNIXTIME(lo.time) > '2015-05-03' AND FROM_UNIXTIME(lo.time) < '2015-05-09') AND (co.shortname LIKE '%15/UA%' OR co.shortname LIKE '%15/UB%')

GROUP BY us.firstname, us.lastname, Course_ID, Course_ShortName, Course_FullName

ORDER BY co.fullname, co.shortname

Is there any database level changes in 2.7 that would prevent these queries to run correctly? Thanks again for your help!!!

Hi, thanks for helping out..

I noticed that by default when uploading Aiken formatted questions, it doesn't seem to respect the original order of questions created in the plain text file. Can anyone please confirm this? If so, is there any way that I can make sure that after upload it will still respect the original order? Thanks!

We are using Moodle 2.5.6