Documentation Moodle ActiveProLearn

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
===== Catalogue de requêtes génériques =====
==== Block Dashboard (Elément de tableau de bord) ====

=== Utilisateurs non connectés par cohortes===

Affiche la liste des utilisateurs non connectés par cohortes avec en données de sortie les catégorie et sous-catégorie de cours concernés, les shortnames et les cours, le nom des cohortes. Pour les utilisateurs : le nom, prénom, l' email, l'identifiant, son rôle et un champs de profil personnalisé sur sa fonction sur Moodle) 

SELECT
   CONCAT(c.id,'-',u.id) as pkey,
   c.shortname as cs,
   c.fullname as cf,
   c.id as cid,
   cc1.name as cc1,
   cc2.name as cc2,
   ch.name as ch,
    u.firstname as fn,
    u.lastname as ln,
    u.email as em,
    u.username as un,
    GROUP_CONCAT(DISTINCT r.shortname ORDER BY r.sortorder DESC SEPARATOR ', ') as roles,
   uid.data as profil
FROM
   {user} u
JOIN
   {cohort_members} cm ON cm.userid = u.id
JOIN
   {cohort} ch ON cm.cohortid = ch.id
JOIN
   {course} c
JOIN
   {course_categories} cc1 ON c.category = cc1.id
JOIN
   {course_categories} cc2 ON cc1.parent = cc2.id
JOIN
   {enrol} e ON e.courseid = c.id AND e.status = 0
JOIN
   {context} ctx ON c.id = ctx.instanceid AND ctx.contextlevel = 50
JOIN
   {role_assignments} ra ON ctx.id = ra.contextid AND ra.userid = u.id
JOIN
   {role} r ON ra.roleid = r.id
LEFT JOIN
   {user_lastaccess} ul ON ul.userid = u.id AND c.id = ul.courseid
LEFT JOIN
   {user_info_data} uid ON u.id = uid.userid AND uid.fieldid = 2
WHERE
   (ul.timeaccess = 0 OR ul.timeaccess IS NULL)
   <%%FILTERS%%>
GROUP BY
    CONCAT(c.id,'-',u.id)
ORDER BY
   cc2,cc1,cf,ch

===Configuration===

Tzo4OiJzdGRDbGFzcyI6MTA0OntzOjU6InRpdGxlIjtzOjQyOiJWb2x1bcOpdHJpZSBkZXMgbm9uIGNvbm5lY3TDqXMgcGFyIGNvaG9ydGUiO3M6MTM6ImluYmxvY2tsYXlvdXQiO3M6MToiMCI7czo2OiJ0YXJnZXQiO3M6NjoibW9vZGxlIjtzOjg6InNob3dkYXRhIjtzOjE6IjEiO3M6OToic2hvd2dyYXBoIjtzOjE6IjAiO3M6MTE6InNob3dudW1zdW1zIjtzOjE6IjEiO3M6OToic2hvd3F1ZXJ5IjtzOjE6IjAiO3M6MTc6InNob3dmaWx0ZXJxdWVyaWVzIjtzOjE6IjAiO3M6NToicXVlcnkiO3M6ODc5OiJTRUxFQ1QNCiAgIENPTkNBVChjLmlkLCctJyxjaC5pZCkgYXMgcGtleSwNCiAgIGMuc2hvcnRuYW1lIGFzIGNzLA0KICAgYy5pZCBhcyBjaWQsDQogICBjLmZ1bGxuYW1lIGFzIGNmLA0KICAgY2MxLm5hbWUgYXMgY2MxLA0KICAgY2MyLm5hbWUgYXMgY2MyLA0KICAgY2gubmFtZSBhcyBjaCwNCiAgIENPVU5UKCopIGFzIHVzLA0KICAgU1VNKENBU0UgV0hFTiB1bC50aW1lYWNjZXNzIElTIE5VTEwgT1IgdWwudGltZWFjY2VzcyA9IDAgVEhFTiAxIEVMU0UgMCBFTkQpIGFzIG5jdSwNCiAgIFNVTShDQVNFIFdIRU4gdWwudGltZWFjY2VzcyBJUyBOVUxMIE9SIHVsLnRpbWVhY2Nlc3MgPSAwIFRIRU4gMSBFTFNFIDAgRU5EKSAvIENPVU5UKCopICAqIDEwMCBhcyBwcmN0DQpGUk9NDQogICB7dXNlcn0gdQ0KSk9JTg0KICAge2NvaG9ydF9tZW1iZXJzfSBjbSBPTiBjbS51c2VyaWQgPSB1LmlkDQpKT0lODQogICB7Y29ob3J0fSBjaCBPTiBjbS5jb2hvcnRpZCA9IGNoLmlkDQpKT0lODQogICB7ZW5yb2x9IGUgT04gZS5jdXN0b21pbnQxID0gY2guaWQNCkpPSU4NCiAgIHtjb3Vyc2V9IGMgT04gZS5jb3Vyc2VpZCA9IGMuaWQNCkpPSU4NCiAgIHtjb3Vyc2VfY2F0ZWdvcmllc30gY2MxIE9OIGNjMS5pZCA9IGMuY2F0ZWdvcnkNCkpPSU4NCiAgIHtjb3Vyc2VfY2F0ZWdvcmllc30gY2MyIE9OIGNjMS5wYXJlbnQgPSBjYzIuaWQNCkxFRlQgSk9JTg0KICAge3VzZXJfbGFzdGFjY2Vzc30gdWwgT04gdWwudXNlcmlkID0gdS5pZCBBTkQgdWwuY291cnNlaWQgPSBjLmlkDQpXSEVSRQ0KICAgZS5lbnJvbCA9ICdjb2hvcnQnIEFORA0KICAgZS5zdGF0dXMgPSAwDQogICA8JSVGSUxURVJTJSU+DQpHUk9VUCBCWQ0KICAgIENPTkNBVChjLmlkLCctJyxjaC5pZCkiO3M6MTU6InF1ZXJ5cm90YXRlY29scyI7czowOiIiO3M6MTY6InF1ZXJ5cm90YXRlcGl2b3QiO3M6MDoiIjtzOjE4OiJxdWVyeXJvdGF0ZW5ld2tleXMiO3M6MDoiIjtzOjEyOiJzcWxwYXJhbXZhcjEiO3M6MDoiIjtzOjE0OiJzcWxwYXJhbWxhYmVsMSI7czowOiIiO3M6MTM6InNxbHBhcmFtdHlwZTEiO3M6NjoiY2hvaWNlIjtzOjE1OiJzcWxwYXJhbXZhbHVlczEiO3M6MDoiIjtzOjEyOiJzcWxwYXJhbXZhcjIiO3M6MDoiIjtzOjE0OiJzcWxwYXJhbWxhYmVsMiI7czowOiIiO3M6MTM6InNxbHBhcmFtdHlwZTIiO3M6NjoiY2hvaWNlIjtzOjE1OiJzcWxwYXJhbXZhbHVlczIiO3M6MDoiIjtzOjEyOiJzcWxwYXJhbXZhcjMiO3M6MDoiIjtzOjE0OiJzcWxwYXJhbWxhYmVsMyI7czowOiIiO3M6MTM6InNxbHBhcmFtdHlwZTMiO3M6NjoiY2hvaWNlIjtzOjE1OiJzcWxwYXJhbXZhbHVlczMiO3M6MDoiIjtzOjEyOiJzcWxwYXJhbXZhcjQiO3M6MDoiIjtzOjE0OiJzcWxwYXJhbWxhYmVsNCI7czowOiIiO3M6MTM6InNxbHBhcmFtdHlwZTQiO3M6NjoiY2hvaWNlIjtzOjE1OiJzcWxwYXJhbXZhbHVlczQiO3M6MDoiIjtzOjEyOiJvdXRwdXRmaWVsZHMiO3M6Mjg6ImNjMjtjYzE7Y3M7Y2Y7Y2g7bmN1O3VzO3ByY3QiO3M6MTM6Im91dHB1dGZvcm1hdHMiO3M6NjY6IiVzOyVzOyVzOzxhIGhyZWY9Ii9jb3Vyc2Uvdmlldy5waHA/aWQ9JXtjaWR9Ij4lczwvYT47JXM7JWQ7JWQ7JS4xZiI7czo4OiJwYWdlc2l6ZSI7czowOiIiO3M6MTU6InVzZWxvY2FsY2FjaGluZyI7czoxOiIwIjtzOjEwOiJjYWNoaW5ndHRsIjtzOjA6IiI7czoxMjoiY2xlYW5kaXNwbGF5IjtzOjE6IjEiO3M6MjI6ImNsZWFuZGlzcGxheXVwdG9jb2x1bW4iO3M6MDoiIjtzOjg6InNvcnRhYmxlIjtzOjE6IjEiO3M6MTU6InNwbGl0c3Vtc29uc29ydCI7czowOiIiO3M6MTE6ImZpZWxkbGFiZWxzIjtzOjc4OiJDYXTDqWdvcmllO1NvdXMtY2F0w6lnb3JpZTtDb2RlO0NvdXJzO0NvaG9ydGU7Tm9uIGNvbm5lY3TDqXM7VG90YWw7UG91cmNlbnRhZ2UiO3M6OToidGFibGV0eXBlIjtzOjY6ImxpbmVhciI7czo3OiJmaWx0ZXJzIjtzOjE2OiJjLmZ1bGxuYW1lIGFzIGNmIjtzOjEyOiJmaWx0ZXJsYWJlbHMiO3M6NToiQ291cnMiO3M6MTQ6ImZpbHRlcmRlZmF1bHRzIjtzOjU6IkZJUlNUIjtzOjEzOiJmaWx0ZXJvcHRpb25zIjtzOjI6InN4IjtzOjEyOiJ2ZXJ0aWNhbGtleXMiO3M6MDoiIjtzOjg6Imhvcml6a2V5IjtzOjA6IiI7czoxNToidmVydGljYWxmb3JtYXRzIjtzOjA6IiI7czoxMToiaG9yaXpmb3JtYXQiO3M6MDoiIjtzOjc6InNwbGl0b24iO3M6MDoiIjtzOjk6Imhvcml6c3VtcyI7czoxOiIwIjtzOjg6InZlcnRzdW1zIjtzOjE6IjAiO3M6MTQ6InZlcnRpY2FsbGFiZWxzIjtzOjA6IiI7czoxMDoiaG9yaXpsYWJlbCI7czowOiIiO3M6MTE6InBhcmVudHNlcmllIjtzOjA6IiI7czoxMDoidHJlZW91dHB1dCI7czowOiIiO3M6MTc6InRyZWVvdXRwdXRmb3JtYXRzIjtzOjA6IiI7czoxMDoieGF4aXNmaWVsZCI7czowOiIiO3M6MTA6InhheGlzbGFiZWwiO3M6MDoiIjtzOjc6InlzZXJpZXMiO3M6MDoiIjtzOjE0OiJ5c2VyaWVzZm9ybWF0cyI7czowOiIiO3M6NDoieW1pbiI7czowOiIiO3M6NDoieW1heCI7czowOiIiO3M6MTE6InRpY2tzcGFjaW5nIjtzOjA6IiI7czoxMjoic2VyaWVzbGFiZWxzIjtzOjA6IiI7czoxMDoieWF4aXNsYWJlbCI7czowOiIiO3M6OToiZ3JhcGh0eXBlIjtzOjQ6ImxpbmUiO3M6MTA6ImdyYXBod2lkdGgiO3M6MzoiNDUwIjtzOjExOiJncmFwaGhlaWdodCI7czozOiIyNTAiO3M6MTA6InNob3dsZWdlbmQiO3M6MToiMCI7czo3OiJtYXB0eXBlIjtzOjc6IlJPQURNQVAiO3M6NDoiem9vbSI7czoxOiI2IjtzOjEwOiJkYXRhdGl0bGVzIjtzOjA6IiI7czoxMzoiZGF0YWxvY2F0aW9ucyI7czowOiIiO3M6OToiZGF0YXR5cGVzIjtzOjA6IiI7czozOiJsYXQiO3M6MToiMCI7czozOiJsbmciO3M6MToiMCI7czoxMzoic2hvd2xvd2VyYmFuZCI7czoxOiIxIjtzOjEzOiJ1cHBlcmJhbmR1bml0IjtzOjU6Ik1PTlRIIjtzOjEzOiJsb3dlcmJhbmR1bml0IjtzOjQ6IllFQVIiO3M6MTg6InRpbWVsaW5lZXZlbnR0aXRsZSI7czowOiIiO3M6MTg6InRpbWVsaW5lZXZlbnRzdGFydCI7czowOiIiO3M6MTY6InRpbWVsaW5lZXZlbnRlbmQiO3M6MDoiIjtzOjE3OiJ0aW1lbGluZWV2ZW50bGluayI7czowOiIiO3M6MTc6InRpbWVsaW5lZXZlbnRkZXNjIjtzOjA6IiI7czoxNDoidGltZWxpbmVjb2xvcnMiO3M6MDoiIjtzOjE4OiJ0aW1lbGluZWNvbG9yZmllbGQiO3M6MDoiIjtzOjE3OiJ0aW1lbGluZWNvbG9ya2V5cyI7czowOiIiO3M6NzoibnVtc3VtcyI7czo2OiJuY3U7dXMiO3M6MTM6Im51bXN1bWZvcm1hdHMiO3M6NToiJWQ7JWQiO3M6MTI6Im51bXN1bWxhYmVscyI7czozOToiVG90YWwgdXRpbGlzYXRldXJzO1RvdGFsIG5vbiBjb25uZWN0w6lzIjtzOjg6ImNyb25tb2RlIjtzOjk6Im5vcmVmcmVzaCI7czo4OiJjcm9uaG91ciI7czowOiIiO3M6NzoiY3Jvbm1pbiI7czowOiIiO3M6MTM6ImNyb25mcmVxdWVuY3kiO3M6NToiZGFpbHkiO3M6ODoibWFrZWZpbGUiO3M6MToiMCI7czoxMDoiZmlsZW91dHB1dCI7czowOiIiO3M6MTc6ImZpbGVvdXRwdXRmb3JtYXRzIjtzOjA6IiI7czoxMDoiZmlsZWZvcm1hdCI7czozOiJDU1YiO3M6MTU6ImZpbGVzcWxvdXR0YWJsZSI7czowOiIiO3M6MTI6ImZpbGVsb2NhdGlvbiI7czowOiIiO3M6MjE6ImZpbGVwYXRoYWRtaW5vdmVycmlkZSI7czowOiIiO3M6ODoic2F2ZXZpZXciO3M6MjM6IkVucmVnaXN0cmVyIGV0IGFmZmljaGVyIjtzOjk6InNxbHBhcmFtcyI7TjtzOjk6ImlzcnVubmluZyI7aTowO3M6ODoibGFzdGNyb24iO2k6MDt9


====Crédits====

  * Valéry Frémaux (valery@activeprolearn.com)- Développements et documentation
  * Florence Labord (florence@activeprolearn.com) - documentation



 [[:Blocks:Dashboard:QueryCatalogue|Revenir au catalogue de requêtes génériques]] - [[:Blocks:Dashboard|Index du composant]] - [[:Plugins|Revenir à l'index des plugins]] - [[:start|Revenir au catalogue]]

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