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