Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SELECT
lm.instanceid
,(SELECT DISTINCT m.data
FROM mdl_local_metadata as m
JOIN mdl_local_metadata_field AS mf ON m.fieldid = mf.id AND mf.contextlevel = 70
WHERE mf.shortname = 'ID' and m.instanceid = cm.id) as 'Unique resource ID'
,(SELECT DISTINCT m.data
FROM mdl_local_metadata as m
JOIN mdl_local_metadata_field AS mf ON m.fieldid = mf.id AND mf.contextlevel = 70
WHERE mf.shortname = 'teacherremarks' and m.instanceid = cm.id) as 'MD - Teacher intro'
#,COUNT(*) 'metadata fields'
# ,CONCAT('<a target="_new" href="%%WWWROOT%%/local/metadata/index.php?id=',
# lm.instanceid,'&action=moduledata&contextlevel=70">',
# m.name, '</a>') AS 'Edit metadata'
,m.name
,CASE m.name
WHEN 'assign' THEN (SELECT m.intro FROM mdl_assign m WHERE m.id = cm.instance)
WHEN 'forum' THEN (SELECT m.intro FROM mdl_forum m WHERE m.id = cm.instance)
WHEN 'quiz' THEN (SELECT m.intro FROM mdl_quiz m WHERE m.id = cm.instance)
WHEN 'questionnaire' THEN (SELECT m.intro FROM mdl_questionnaire m WHERE m.id = cm.instance)
WHEN 'hvp' THEN (SELECT m.intro FROM mdl_hvp m WHERE m.id = cm.instance)
WHEN 'resource' THEN (SELECT m.intro FROM mdl_resource m WHERE m.id = cm.instance)
WHEN 'page' THEN (SELECT m.intro FROM mdl_page m WHERE m.id = cm.instance)
WHEN 'folder' THEN (SELECT m.intro FROM mdl_folder m WHERE m.id = cm.instance)
WHEN 'ejsapp' THEN (SELECT m.intro FROM mdl_ejsapp m WHERE m.id = cm.instance)
WHEN 'url' THEN (SELECT m.intro FROM mdl_url m WHERE m.id = cm.instance)
END AS 'OER module intro, for students'
FROM mdl_local_metadata lm
JOIN mdl_local_metadata_field lmf ON lmf.id = lm.fieldid
JOIN mdl_course_modules cm ON cm.id = lm.instanceid
JOIN mdl_modules m ON m.id = cm.module
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_course_categories cc ON cc.id = c.category
WHERE lmf.contextlevel = 70
#AND lmf.shortname = 'teacherremarks'
AND lmf.shortname = 'ID'
#AND cc.path like '/2/%'
AND c.category IN (3, 7, 8, 93) );
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment