Table des matières
Generic Query Catalogue
Dashboard Block (Dashboard Element)
Resource popularity table by cohort
Displays a table of resource popularity by cohort. Rendered in output columns showing the course category and subcategory, the resource type and name with the number of views and the date of last view per resource, as well as the sections and subsections where they appear.
SELECT
CONCAT(cm.id, '-', ch.id) as pkey,
cm.id,
c.shortname as cs,
c.fullname as cf,
cc1.name as cc1,
cc2.name as cc2,
cs1.name as cs1,
cs2.name as cs2,
ch.name as ch,
m.name as rtype,
CASE WHEN m.name = 'resource' THEN r.name
WHEN m.name = 'page' THEN p.name
WHEN m.name = 'url' THEN ur.name
WHEN m.name = 'quiz' THEN q.name
WHEN m.name = 'workshop' THEN w.name
WHEN m.name = 'folder' THEN f.name ELSE 'N.C.' END as rname,
CASE WHEN ltci.itemoptional = 0 THEN 'Obligatoire' ELSE 'Facultatif' END as cmst,
COUNT(DISTINCT ltcc.userid) as dusers,
COUNT(*) as viewes,
MAX(l.timecreated) as lastview
FROM
{modules} m,
{learningtimecheck_item} ltci,
{learningtimecheck_check} ltcc,
{course} c,
{course_categories} cc1,
{course_categories} cc2,
{logstore_standard_log} l,
{cohort} ch,
{cohort_members} chm,
{course_modules} cm
LEFT JOIN
{resource} r
ON
r.id = cm.instance
LEFT JOIN
{url} ur
ON
ur.id = cm.instance
LEFT JOIN
{folder} f
ON
f.id = cm.instance
LEFT JOIN
{quiz} q
ON
q.id = cm.instance
LEFT JOIN
{page} p
ON
p.id = cm.instance
LEFT JOIN
{workshop} w
ON
w.id = cm.instance
LEFT JOIN
{course_sections} cs1
ON
cm.section = cs1.id
LEFT JOIN
{course_format_options} cfo
ON
cs1.id = cfo.sectionid
LEFT JOIN
{course_sections} cs2
ON
cs2.section = cfo.value AND cfo.courseid = cs2.course
WHERE
m.id = cm.module AND
m.name IN('resource', 'page', 'url', 'folder', 'quiz') AND
ltcc.item = ltci.id AND
ltci.moduleid = cm.id AND
ltcc.userid = chm.userid AND
cm.course = c.id AND
cc1.id = c.category AND
cc2.id = cc1.parent AND
ch.id = chm.cohortid AND
ltcc.userid = l.userid AND
l.objecttable = m.name AND
l.objectid = cm.instance AND
l.action = 'viewed'
<%%FILTERS%%>
GROUP BY
CONCAT(cm.id, '-', ch.id)
Configuration
Tzo4OiJzdGRDbGFzcyI6OTg6e3M6NToidGl0bGUiO3M6NDQ6IlBvcHVsYXJpdMOpIGRlcyByZXNzb3VyY2VzIGRhbnMgbGVzIGNvaG9ydGVzIjtzOjEzOiJpbmJsb2NrbGF5b3V0IjtzOjE6IjAiO3M6NjoidGFyZ2V0IjtzOjY6Im1vb2RsZSI7czo4OiJzaG93ZGF0YSI7czoxOiIxIjtzOjk6InNob3dncmFwaCI7czoxOiIwIjtzOjExOiJzaG93bnVtc3VtcyI7czoxOiIxIjtzOjk6InNob3dxdWVyeSI7czoxOiIwIjtzOjE3OiJzaG93ZmlsdGVycXVlcmllcyI7czoxOiIwIjtzOjU6InF1ZXJ5IjtzOjIwMzU6IlNFTEVDVA0KICAgQ09OQ0FUKGNtLmlkLCAnLScsIGNoLmlkKSBhcyBwa2V5LA0KICAgY20uaWQsDQogICBjLnNob3J0bmFtZSBhcyBjcywNCiAgIGMuZnVsbG5hbWUgYXMgY2YsDQogICBjYzEubmFtZSBhcyBjYzEsDQogICBjYzIubmFtZSBhcyBjYzIsDQogICBjczEubmFtZSBhcyBjczEsDQogICBjczIubmFtZSBhcyBjczIsDQogICBjaC5uYW1lIGFzIGNoLA0KICAgbS5uYW1lIGFzIHJ0eXBlLA0KICAgQ0FTRSBXSEVOIG0ubmFtZSA9ICdyZXNvdXJjZScgVEhFTiByLm5hbWUNCiAgICAgICAgICAgICAgV0hFTiBtLm5hbWUgPSAncGFnZScgVEhFTiBwLm5hbWUNCiAgICAgICAgICAgICAgV0hFTiBtLm5hbWUgPSAndXJsJyBUSEVOIHVyLm5hbWUNCiAgICAgICAgICAgICAgV0hFTiBtLm5hbWUgPSAncXVpeicgVEhFTiBxLm5hbWUNCiAgICAgICAgICAgICAgV0hFTiBtLm5hbWUgPSAnd29ya3Nob3AnIFRIRU4gdy5uYW1lDQogICAgICAgICAgICAgIFdIRU4gbS5uYW1lID0gJ2ZvbGRlcicgVEhFTiBmLm5hbWUgRUxTRSAnTi5DLicgRU5EIGFzIHJuYW1lLA0KICAgQ0FTRSBXSEVOIGx0Y2kuaXRlbW9wdGlvbmFsID0gMCBUSEVOICdPYmxpZ2F0b2lyZScgRUxTRSAnRmFjdWx0YXRpZicgRU5EIGFzIGNtc3QsDQogICBDT1VOVChESVNUSU5DVCBsdGNjLnVzZXJpZCkgYXMgZHVzZXJzLA0KICAgQ09VTlQoKikgYXMgdmlld2VzLA0KICAgTUFYKGwudGltZWNyZWF0ZWQpIGFzIGxhc3R2aWV3DQpGUk9NDQogICB7bW9kdWxlc30gbSwNCiAgIHtsZWFybmluZ3RpbWVjaGVja19pdGVtfSBsdGNpLA0KICAge2xlYXJuaW5ndGltZWNoZWNrX2NoZWNrfSBsdGNjLA0KICAge2NvdXJzZX0gYywNCiAgIHtjb3Vyc2VfY2F0ZWdvcmllc30gY2MxLA0KICAge2NvdXJzZV9jYXRlZ29yaWVzfSBjYzIsDQogICB7bG9nc3RvcmVfc3RhbmRhcmRfbG9nfSBsLA0KICAge2NvaG9ydH0gY2gsDQogICB7Y29ob3J0X21lbWJlcnN9IGNobSwNCiAgIHtjb3Vyc2VfbW9kdWxlc30gY20NCkxFRlQgSk9JTg0KICAge3Jlc291cmNlfSByDQpPTg0KICAgci5pZCA9IGNtLmluc3RhbmNlDQpMRUZUIEpPSU4NCiAgIHt1cmx9IHVyDQpPTg0KICAgdXIuaWQgPSBjbS5pbnN0YW5jZQ0KTEVGVCBKT0lODQogICB7Zm9sZGVyfSBmDQpPTg0KICAgZi5pZCA9IGNtLmluc3RhbmNlDQpMRUZUIEpPSU4NCiAgIHtxdWl6fSBxDQpPTg0KICAgcS5pZCA9IGNtLmluc3RhbmNlDQpMRUZUIEpPSU4NCiAgIHtwYWdlfSBwDQpPTg0KICAgcC5pZCA9IGNtLmluc3RhbmNlDQpMRUZUIEpPSU4NCiAgIHt3b3Jrc2hvcH0gdw0KT04NCiAgIHcuaWQgPSBjbS5pbnN0YW5jZQ0KTEVGVCBKT0lODQogICB7Y291cnNlX3NlY3Rpb25zfSBjczENCk9ODQogICBjbS5zZWN0aW9uID0gY3MxLmlkDQpMRUZUIEpPSU4NCiAgICB7Y291cnNlX2Zvcm1hdF9vcHRpb25zfSBjZm8NCk9ODQogICAgY3MxLmlkID0gY2ZvLnNlY3Rpb25pZA0KTEVGVCBKT0lODQogICB7Y291cnNlX3NlY3Rpb25zfSAgY3MyDQpPTg0KICAgY3MyLnNlY3Rpb24gPSBjZm8udmFsdWUgQU5EDQogICBjZm8uY291cnNlaWQgPSBjczIuY291cnNlDQpXSEVSRQ0KICAgbS5pZCA9IGNtLm1vZHVsZSBBTkQNCiAgIG0ubmFtZSBJTigncmVzb3VyY2UnLCAncGFnZScsICd1cmwnLCAnZm9sZGVyJywgJ3F1aXonKSBBTkQNCiAgIGx0Y2MuaXRlbSA9IGx0Y2kuaWQgQU5EDQogICBsdGNpLm1vZHVsZWlkID0gY20uaWQgQU5EDQogICBsdGNjLnVzZXJpZCA9IGNobS51c2VyaWQgQU5EDQogICBjbS5jb3Vyc2UgPSBjLmlkIEFORA0KICAgY2MxLmlkID0gYy5jYXRlZ29yeSBBTkQNCiAgIGNjMi5pZCA9IGNjMS5wYXJlbnQgQU5EDQogICBjaC5pZCA9IGNobS5jb2hvcnRpZCBBTkQNCiAgIGx0Y2MudXNlcmlkID0gbC51c2VyaWQgQU5EDQogICBsLm9iamVjdHRhYmxlID0gbS5uYW1lIEFORA0KICAgbC5vYmplY3RpZCA9IGNtLmluc3RhbmNlIEFORA0KICAgbC5hY3Rpb24gPSAndmlld2VkJyAgDQogICA8JSVGSUxURVJTJSU+DQpHUk9VUCBCWQ0KICAgIENPTkNBVChjbS5pZCwgJy0nLCBjaC5pZCkiO3M6MTI6InNxbHBhcmFtdmFyMSI7czowOiIiO3M6MTQ6InNxbHBhcmFtbGFiZWwxIjtzOjA6IiI7czoxMzoic3FscGFyYW10eXBlMSI7czo2OiJjaG9pY2UiO3M6MTU6InNxbHBhcmFtdmFsdWVzMSI7czowOiIiO3M6MTI6InNxbHBhcmFtdmFyMiI7czowOiIiO3M6MTQ6InNxbHBhcmFtbGFiZWwyIjtzOjA6IiI7czoxMzoic3FscGFyYW10eXBlMiI7czo2OiJjaG9pY2UiO3M6MTU6InNxbHBhcmFtdmFsdWVzMiI7czowOiIiO3M6MTI6InNxbHBhcmFtdmFyMyI7czowOiIiO3M6MTQ6InNxbHBhcmFtbGFiZWwzIjtzOjA6IiI7czoxMzoic3FscGFyYW10eXBlMyI7czo2OiJjaG9pY2UiO3M6MTU6InNxbHBhcmFtdmFsdWVzMyI7czowOiIiO3M6MTI6InNxbHBhcmFtdmFyNCI7czowOiIiO3M6MTQ6InNxbHBhcmFtbGFiZWw0IjtzOjA6IiI7czoxMzoic3FscGFyYW10eXBlNCI7czo2OiJjaG9pY2UiO3M6MTU6InNxbHBhcmFtdmFsdWVzNCI7czowOiIiO3M6MTI6Im91dHB1dGZpZWxkcyI7czo1NjoiY2MyO2NjMTtjcztjZjtjczI7Y3MxO3J0eXBlO3JuYW1lO2R1c2Vyczt2aWV3ZXM7bGFzdHZpZXciO3M6MTM6Im91dHB1dGZvcm1hdHMiO3M6MzI6IiVzOyVzOyVzOyVzOyVzOyVzOyVzOyVzOyVkOyVkOyVEIjtzOjg6InBhZ2VzaXplIjtzOjA6IiI7czoxMDoiY2FjaGluZ3R0bCI7czowOiIiO3M6MTI6ImNsZWFuZGlzcGxheSI7czoxOiIxIjtzOjIyOiJjbGVhbmRpc3BsYXl1cHRvY29sdW1uIjtzOjE6IjIiO3M6ODoic29ydGFibGUiO3M6MToiMSI7czoxNToic3BsaXRzdW1zb25zb3J0IjtzOjA6IiI7czoxMToiZmllbGRsYWJlbHMiO3M6MTE1OiJDYXTDqWdvcmllO1NvdXMtY2F0w6lnb3JpZTtDb2RlO0NvdXJzO1NlY3Rpb247c291cy1zZWN0aW9uO1R5cGU7UmVzc291cmNlO1V0aWxpc2F0ZXVycyBkaXN0aW5jdHM7VnVlcztEZXJuacOocmUgdnVlIjtzOjk6InRhYmxldHlwZSI7czo2OiJsaW5lYXIiO3M6NzoiZmlsdGVycyI7czoxMzoiY2gubmFtZSBhcyBjaCI7czoxMjoiZmlsdGVybGFiZWxzIjtzOjc6IkNvaG9ydGUiO3M6MTQ6ImZpbHRlcmRlZmF1bHRzIjtzOjU6IkZJUlNUIjtzOjEzOiJmaWx0ZXJvcHRpb25zIjtzOjI6InN4IjtzOjEyOiJ2ZXJ0aWNhbGtleXMiO3M6MDoiIjtzOjg6Imhvcml6a2V5IjtzOjA6IiI7czoxNToidmVydGljYWxmb3JtYXRzIjtzOjA6IiI7czoxMToiaG9yaXpmb3JtYXQiO3M6MDoiIjtzOjc6InNwbGl0b24iO3M6MDoiIjtzOjE0OiJ2ZXJ0aWNhbGxhYmVscyI7czowOiIiO3M6MTA6Imhvcml6bGFiZWwiO3M6MDoiIjtzOjExOiJwYXJlbnRzZXJpZSI7czowOiIiO3M6MTA6InRyZWVvdXRwdXQiO3M6MDoiIjtzOjE3OiJ0cmVlb3V0cHV0Zm9ybWF0cyI7czowOiIiO3M6MTA6InhheGlzZmllbGQiO3M6MDoiIjtzOjEwOiJ4YXhpc2xhYmVsIjtzOjA6IiI7czo3OiJ5c2VyaWVzIjtzOjA6IiI7czoxNDoieXNlcmllc2Zvcm1hdHMiO3M6MDoiIjtzOjQ6InltaW4iO3M6MDoiIjtzOjQ6InltYXgiO3M6MDoiIjtzOjExOiJ0aWNrc3BhY2luZyI7czowOiIiO3M6MTI6InNlcmllc2xhYmVscyI7czowOiIiO3M6MTA6InlheGlzbGFiZWwiO3M6MDoiIjtzOjk6ImdyYXBodHlwZSI7czo0OiJsaW5lIjtzOjEwOiJncmFwaHdpZHRoIjtzOjM6IjQ1MCI7czoxMToiZ3JhcGhoZWlnaHQiO3M6MzoiMjUwIjtzOjEwOiJzaG93bGVnZW5kIjtzOjE6IjAiO3M6NzoibWFwdHlwZSI7czo3OiJST0FETUFQIjtzOjQ6Inpvb20iO3M6MToiNiI7czoxMDoiZGF0YXRpdGxlcyI7czowOiIiO3M6MTM6ImRhdGFsb2NhdGlvbnMiO3M6MDoiIjtzOjk6ImRhdGF0eXBlcyI7czowOiIiO3M6MzoibGF0IjtzOjE6IjAiO3M6MzoibG5nIjtzOjE6IjAiO3M6MTM6InNob3dsb3dlcmJhbmQiO3M6MToiMSI7czoxMzoidXBwZXJiYW5kdW5pdCI7czo1OiJNT05USCI7czoxMzoibG93ZXJiYW5kdW5pdCI7czo0OiJZRUFSIjtzOjE4OiJ0aW1lbGluZWV2ZW50dGl0bGUiO3M6MDoiIjtzOjE4OiJ0aW1lbGluZWV2ZW50c3RhcnQiO3M6MDoiIjtzOjE2OiJ0aW1lbGluZWV2ZW50ZW5kIjtzOjA6IiI7czoxNzoidGltZWxpbmVldmVudGxpbmsiO3M6MDoiIjtzOjE3OiJ0aW1lbGluZWV2ZW50ZGVzYyI7czowOiIiO3M6MTQ6InRpbWVsaW5lY29sb3JzIjtzOjA6IiI7czoxODoidGltZWxpbmVjb2xvcmZpZWxkIjtzOjA6IiI7czoxNzoidGltZWxpbmVjb2xvcmtleXMiO3M6MDoiIjtzOjc6Im51bXN1bXMiO3M6MDoiIjtzOjEzOiJudW1zdW1mb3JtYXRzIjtzOjA6IiI7czoxMjoibnVtc3VtbGFiZWxzIjtzOjA6IiI7czo4OiJjcm9ubW9kZSI7czo5OiJub3JlZnJlc2giO3M6ODoiY3JvbmhvdXIiO3M6MDoiIjtzOjc6ImNyb25taW4iO3M6MDoiIjtzOjEzOiJjcm9uZnJlcXVlbmN5IjtzOjU6ImRhaWx5IjtzOjg6Im1ha2VmaWxlIjtzOjE6IjAiO3M6MTA6ImZpbGVvdXRwdXQiO3M6MDoiIjtzOjE3OiJmaWxlb3V0cHV0Zm9ybWF0cyI7czowOiIiO3M6MTA6ImZpbGVmb3JtYXQiO3M6MzoiQ1NWIjtzOjE1OiJmaWxlc3Fsb3V0dGFibGUiO3M6MDoiIjtzOjEyOiJmaWxlbG9jYXRpb24iO3M6MDoiIjtzOjIxOiJmaWxlcGF0aGFkbWlub3ZlcnJpZGUiO3M6MDoiIjtzOjg6InNhdmV2aWV3IjtzOjIzOiJFbnJlZ2lzdHJlciBldCBhZmZpY2hlciI7czo5OiJzcWxwYXJhbXMiO047czo5OiJpc3J1bm5pbmciO2k6MDtzOjg6Imxhc3Rjcm9uIjtpOjA7fQ==
Credits
- Valéry Frémaux (valery@activeprolearn.com)- Development and documentation
- Florence Labord (florence@activeprolearn.com) - documentation
Back to generic query catalogue index - Component index - Back to plugins index - Back to the catalogue index