Table des matières
Catalogue de requêtes génériques
Block Dashboard (Elément de tableau de bord)
Tableau de popularité des ressources par cohortes
Affiche un tableau de popularité des ressources par cohortes. Rendu en colonnes de sortie avec l'affichage par catégorie et sous-catégorie de cours, type et nom des ressources avec nombre de vues et date de dernière visualisation par ressource, ainsi que les sections et sous-sections ou elles figurent.
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==
Crédits
- Valéry Frémaux (valery@activeprolearn.com)- Développements et documentation
- Florence Labord (florence@activeprolearn.com) - documentation
Revenir au catalogue de requêtes génériques - Index du composant - Revenir à l'index des plugins - Revenir au catalogue