New reports for admin users
For internal reasons, we needed to create 2 new views in the database (with hesk for database prefix here) to see who is affected to categories and wich categories are affected to :
1/ categories by users
2/ users by categories
So, I created two views respectively :
1/ view vcategoriesbyusers :
DROP view
IF EXISTS `vcategoriesbyusers`;
CREATE OR REPLACE algorithm=undefined SQL security definer view v_categories_by_users
AS
SELECT u
.name
AS members
,
groupconcat(c
.name
ORDER BY c
.name
ASC SEPARATOR ',') AS categories
FROM (`heskcategoriesc
heskusers
JOINu`)
WHERE ((
findin_set(CONVERT(c
.id
USING utf8),CONVERT(u
.categories
USING utf8)) > 0)
OR (
u
.isadmin
= _utf8'1'))
GROUP BY u
.name
;
2/ view vusersbycategories :
DROP VIEW IF EXISTS `vusersbycategories;
SQL SECURITY DEFINER VIEW
CREATE OR REPLACE ALGORITHM=UNDEFINEDv_users_by_categories
AS select c
.name
AS Categories
,groupconcat(u
.name
order by u
.name
ASC separator ',') AS Members
from (`heskcategoriesc
join
heskusersu` on(((findin_set(convert(
c
.id
using utf8),convert(u
.categories
using utf8)) <> 0) or (u
.isadmin
= _utf8'1')))) group by c
.name
;
Would it be possible to integrate one (or two) new tab on the report page in admin pages to display the results ?