I'm trying to do the same thing but I have not come far. What I have found is that you can get SQL queries that are run on any php page by adding this at the top
$DB->set_debug(true);
I have done this insade moodle/report/progress/index.php
when I print the excel compleation report I get these query print outs:
SELECT * FROM mdl_course WHERE id = ? [array ( 0 => 2, )]
Query took: 0.00037908554077148 seconds.
SELECT * FROM mdl_context WHERE contextlevel = ? AND instanceid = ? [array ( 0 => 50, 1 => '2', )]
Query took: 0.00031900405883789 seconds.
SELECT name,value FROM mdl_user_preferences WHERE userid = ? [array ( 0 => '2', )]
Query took: 0.00031709671020508 seconds.
SELECT f.id FROM mdl_user_info_field f LEFT JOIN mdl_user_info_data d ON (d.fieldid = f.id AND d.userid = ?) WHERE f.required = 1 AND f.visible > 0 AND f.locked = 0 AND d.id IS NULL LIMIT 0, 1 [array ( 0 => '2', )]
Query took: 0.00019001960754395 seconds.
UPDATE mdl_user SET lastip = ?,lastaccess = ? WHERE id=? [array ( 0 => '80.217.170.175', 1 => 1496401494, 2 => '2', )]
Query took: 0.0022788047790527 seconds.
SELECT timeaccess FROM mdl_user_lastaccess WHERE userid = ? AND courseid = ? [array ( 0 => '2', 1 => '2', )]
Query took: 0.00055384635925293 seconds.
UPDATE mdl_user_lastaccess SET timeaccess = ? WHERE userid = ? AND courseid = ? [array ( 0 => 1496401494, 1 => '2', 2 => '2', )]
Query took: 0.0010018348693848 seconds.
SELECT ctx.* FROM mdl_context ctx WHERE ctx.path LIKE ? [array ( 0 => '/1/6/37/%', )]
Query took: 0.00017809867858887 seconds.
SELECT rc.id, rc.roleid, rc.
permission, ctx.path FROM mdl_role_capabilities rc JOIN mdl_context ctx on rc.contextid = ctx.id WHERE rc.contextid IN (?,?,?) AND rc.capability = ? [array ( 0 => '1', 1 => '6', 2 => '37', 3 => 'moodle/course:isincompletionreports', )]
Query took: 0.00014281272888184 seconds.
SELECT COUNT(eu.id) FROM (SELECT DISTINCT eu1_u.id FROM mdl_user eu1_u JOIN mdl_user_enrolments ej1_ue ON ej1_ue.userid = eu1_u.id JOIN mdl_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid AND ej1_e.courseid = ?) JOIN mdl_role_assignments eu1_ra3 ON (eu1_ra3.userid = eu1_u.id AND eu1_ra3.roleid IN (5) AND eu1_ra3.contextid IN (1,6,37)) WHERE 1 = 1 AND ej1_ue.status = ? AND ej1_e.status = ? AND ej1_ue.timestart < ? AND (ej1_ue.timeend = 0 OR ej1_ue.timeend > ?) AND (eu1_u.id <> ?) AND eu1_u.deleted = 0) eu JOIN mdl_user u ON u.id = eu.id [array ( 0 => '2', 1 => 0, 2 => 0, 3 => 1496401500, 4 => 1496401500, 5 => '1', )]
Query took: 0.001317024230957 seconds.
SELECT rc.id, rc.roleid, rc.permission, ctx.path FROM mdl_role_capabilities rc JOIN mdl_context ctx on rc.contextid = ctx.id WHERE rc.contextid IN (?,?,?) AND rc.capability = ? [array ( 0 => '1', 1 => '6', 2 => '37', 3 => 'moodle/course:isincompletionreports', )]
Query took: 0.00011491775512695 seconds.
SELECT COUNT(eu.id) FROM (SELECT DISTINCT eu2_u.id FROM mdl_user eu2_u JOIN mdl_user_enrolments ej2_ue ON ej2_ue.userid = eu2_u.id JOIN mdl_enrol ej2_e ON (ej2_e.id = ej2_ue.enrolid AND ej2_e.courseid = ?) JOIN mdl_role_assignments eu2_ra3 ON (eu2_ra3.userid = eu2_u.id AND eu2_ra3.roleid IN (5) AND eu2_ra3.contextid IN (1,6,37)) WHERE 1 = 1 AND ej2_ue.status = ? AND ej2_e.status = ? AND ej2_ue.timestart < ? AND (ej2_ue.timeend = 0 OR ej2_ue.timeend > ?) AND (eu2_u.id <> ?) AND eu2_u.deleted = 0) eu JOIN mdl_user u ON u.id = eu.id [array ( 0 => '2', 1 => 0, 2 => 0, 3 => 1496401500, 4 => 1496401500, 5 => '1', )]
Query took: 0.0008549690246582 seconds.
SELECT rc.id, rc.roleid, rc.permission, ctx.path FROM mdl_role_capabilities rc JOIN mdl_context ctx on rc.contextid = ctx.id WHERE rc.contextid IN (?,?,?) AND rc.capability = ? [array ( 0 => '1', 1 => '6', 2 => '37', 3 => 'moodle/course:isincompletionreports', )]
Query took: 0.00033998489379883 seconds.
SELECT u.id, u.idnumber, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname, u.email FROM (SELECT DISTINCT eu3_u.id FROM mdl_user eu3_u JOIN mdl_user_enrolments ej3_ue ON ej3_ue.userid = eu3_u.id JOIN mdl_enrol ej3_e ON (ej3_e.id = ej3_ue.enrolid AND ej3_e.courseid = ?) JOIN mdl_role_assignments eu3_ra3 ON (eu3_ra3.userid = eu3_u.id AND eu3_ra3.roleid IN (5) AND eu3_ra3.contextid IN (1,6,37)) WHERE 1 = 1 AND ej3_ue.status = ? AND ej3_e.status = ? AND ej3_ue.timestart < ? AND (ej3_ue.timeend = 0 OR ej3_ue.timeend > ?) AND (eu3_u.id <> ?) AND eu3_u.deleted = 0) eu JOIN mdl_user u ON u.id = eu.id ORDER BY u.lastname ASC [array ( 0 => '2', 1 => 0, 2 => 0, 3 => 1496401500, 4 => 1496401500, 5 => '1', )]
Query took: 0.00078582763671875 seconds.
SELECT cmc.* FROM mdl_course_modules cm INNER JOIN mdl_course_modules_completion cmc ON cm.id=cmc.coursemoduleid WHERE cm.course=? AND cmc.userid IN (?,?,?,?,?,?,?,?,?,?,?,?,?) [array ( 0 => '2', 1 => '37', 2 => '38', 3 => '33', 4 => '39', 5 => '21', 6 => '15', 7 => '31', 8 => '34', 9 => '17', 10 => '16', 11 => '7', 12 => '2', 13 => '18', )]
Query took: 0.00013399124145508 seconds.
SELECT active.filter, fc.name, fc.value FROM (SELECT f.filter, MAX(f.sortorder) AS sortorder FROM mdl_filter_active f JOIN mdl_context ctx ON f.contextid = ctx.id WHERE ctx.id IN (1,6,37) GROUP BY filter HAVING MAX(f.active * ctx.depth) > -MIN(f.active * ctx.depth) ) active LEFT JOIN mdl_filter_config fc ON fc.filter = active.filter AND fc.contextid = 37 ORDER BY active.sortorder [array
]
Query took: 0.00011610984802246 seconds.
SELECT f.filter, fc.name, fc.value FROM mdl_filter_active f LEFT JOIN mdl_filter_config fc ON fc.filter = f.filter AND fc.contextid = f.contextid WHERE f.contextid = ? AND f.active != ? ORDER BY f.sortorder [array ( 0 => 1, 1 => -9999, )]
Query took: 0.00020909309387207 seconds.
"Email address"
SELECT active.filter, fc.name, fc.value FROM (SELECT f.filter, MAX(f.sortorder) AS sortorder FROM mdl_filter_active f JOIN mdl_context ctx ON f.contextid = ctx.id WHERE ctx.id IN (1,6,37,193) GROUP BY filter HAVING MAX(f.active * ctx.depth) > -MIN(f.active * ctx.depth) ) active LEFT JOIN mdl_filter_config fc ON fc.filter = active.filter AND fc.contextid = 193 ORDER BY active.sortorder [array
]
Query took: 0.0012497901916504 seconds.
"Instructions" ""
SELECT active.filter, fc.name, fc.value FROM (SELECT f.filter, MAX(f.sortorder) AS sortorder FROM mdl_filter_active f JOIN mdl_context ctx ON f.contextid = ctx.id WHERE ctx.id IN (1,6,37,101) GROUP BY filter HAVING MAX(f.active * ctx.depth) > -MIN(f.active * ctx.depth) ) active LEFT JOIN mdl_filter_config fc ON fc.filter = active.filter AND fc.contextid = 101 ORDER BY active.sortorder [array
]
Query took: 0.00083398818969727 seconds.
"Introduction" ""
SELECT active.filter, fc.name, fc.value FROM (SELECT f.filter, MAX(f.sortorder) AS sortorder FROM mdl_filter_active f JOIN mdl_context ctx ON f.contextid = ctx.id WHERE ctx.id IN (1,6,37,102) GROUP BY filter HAVING MAX(f.active * ctx.depth) > -MIN(f.active * ctx.depth) ) active LEFT JOIN mdl_filter_config fc ON fc.filter = active.filter AND fc.contextid = 102 ORDER BY active.sortorder [array
]
Query took: 0.00073599815368652 seconds.
"Mission and Values" ""
SELECT active.filter, fc.name, fc.value FROM (SELECT f.filter, MAX(f.sortorder) AS sortorder FROM mdl_filter_active f JOIN mdl_context ctx ON f.contextid = ctx.id WHERE ctx.id IN (1,6,37,103) GROUP BY filter HAVING MAX(f.active * ctx.depth) > -MIN(f.active * ctx.depth) ) active LEFT JOIN mdl_filter_config fc ON fc.filter = active.filter AND fc.contextid = 103 ORDER BY active.sortorder [array
]
Query took: 0.00057506561279297 seconds.
"Our rules of behavior" ""
SELECT active.filter, fc.name, fc.value FROM (SELECT f.filter, MAX(f.sortorder) AS sortorder FROM mdl_filter_active f JOIN mdl_context ctx ON f.contextid = ctx.id WHERE ctx.id IN (1,6,37,104) GROUP BY filter HAVING MAX(f.active * ctx.depth) > -MIN(f.active * ctx.depth) ) active LEFT JOIN mdl_filter_config fc ON fc.filter = active.filter AND fc.contextid = 104 ORDER BY active.sortorder [array
]
Query took: 0.00075101852416992 seconds.
"Reporting a concern - Speak up!" ""
SELECT active.filter, fc.name, fc.value FROM (SELECT f.filter, MAX(f.sortorder) AS sortorder FROM mdl_filter_active f JOIN mdl_context ctx ON f.contextid = ctx.id WHERE ctx.id IN (1,6,37,107) GROUP BY filter HAVING MAX(f.active * ctx.depth) > -MIN(f.active * ctx.depth) ) active LEFT JOIN mdl_filter_config fc ON fc.filter = active.filter AND fc.contextid = 107 ORDER BY active.sortorder [array
]
Query took: 0.00065493583679199 seconds.
This is basecally the report we need but when I run this in thedatabase I get empty result but I think that this has to do with active filters that are not active when you just exceute the querys.
(Edited by Mary Cooch to remove personal details - original submission Friday, 2 June 2017, 12:48 PM)
