Moodle files access

request.sql

DROP PROCEDURE IF EXISTS getLogsForModule;

DELIMITER $$
CREATE PROCEDURE getLogsForModule(module VARCHAR(255))
BEGIN
	-- on doit passer par un concat car le nom d'une table doit être statique
    SET @request = CONCAT("SELECT l.*, i.*, c.*, category_path(c.category) as category_path, u.*
        FROM mdl_logstore_standard_log l
        LEFT OUTER JOIN mdl_", module, " i ON i.id = l.objectid
        LEFT OUTER JOIN mdl_course c ON c.id = l.courseid
        LEFT OUTER JOIN mdl_user   u ON u.id = l.userid
        WHERE l.eventname LIKE '%", module, "%'
        AND l.eventname LIKE '%course_module_viewed%';");

    PREPARE stmt FROM @request;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END $$
DELIMITER ;

-- 
	
call getLogsForModule('resource')
call getLogsForModule('forum')

-- etc.