Всех приветствую. Есть вопрос по работе с фильтрами Configurable reports.
С данной темы взял код, добавил колонки все работает.
Но не могу настроить фильтр "Категория (включая подкатегории)". Есть несколько категорий (ВПО, СПО, Олимпиады, Переподготовки и т д.) Фильтр с обычной категорией работает отлично.
Весь код в сборе:
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/index.php?categoryid=', cc.id,'">', cc.name, '</a>') AS "Категория",
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS "Дисциплина",
(SELECT DISTINCT concat(u.lastname, ' ', u.firstname)
FROM
prefix_role_assignments AS ra
JOIN prefix_user AS u ON ra.userid = u.id
JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 limit 1) AS "Преподаватель",
(SELECT COUNT(*)FROM prefix_role_assignments AS ra JOIN prefix_user AS u ON ra.userid = u.id JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS "Всего преподавателей",
(SELECT COUNT(*)FROM prefix_role_assignments AS ra JOIN prefix_user AS u ON ra.userid = u.id JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 5 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS "Студентов",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'assign') AS "Задание",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'quiz') AS "Тест",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'book') AS "Книга",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'chat') AS "Чат",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'choice') AS "Опрос",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'data') AS "База данных",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'feedback') AS "Обратная связь",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'forum') AS "Форум",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'folder') AS "Папка",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'glossary') AS "Глоссарий",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'label') AS "Пояснение",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'lesson') AS "Лекция",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'lti') AS "Внешний ресурс",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'page') AS "Страница",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'resource') AS "Файл",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'scorm') AS "SCORM",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'survey') AS "Анкета",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'url') AS "Гиперссылка",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'wiki') AS "Вики",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'workshop') AS "Семинар",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'quiz') AS "Тест",
(SELECT Count(*) FROM prefix_question q INNER JOIN prefix_question_categories qc ON qc.id = q.category AND q.qtype <> 'random' AND q.parent = 0 INNER JOIN prefix_context ctx ON ctx.id = qc.contextid WHERE ctx.instanceid = c.id AND ctx.contextlevel = 50) AS 'Вопросов'
FROM `prefix_course` AS c
JOIN `prefix_course_categories` AS cc ON cc.id = c.category
#JOIN prefix_course AS cc ON cc.id = 3
WHERE c.category != 'APXIB'
%% FILTER_CATEGORIES:c.category.path %%
#%%FILTER_CATEGORIES:3%%
GROUP BY c.id
ORDER BY cc.name ASC
