===== 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



 [[:Blocks:Dashboard:QueryCatalogue|Back to generic query catalogue index]] - [[:Blocks:Dashboard|Component index]] - [[:Plugins|Back to plugins index]] - [[:start|Back to the catalogue index]]

<html><!-- /nomoodle --></html>