SQL Moodle using custom profile fields

Question

I have created 2 user profile fields Employee ID (shortname:employeeid) Organisation (shortname:Organisation)

Here is my code:

SELECT 
        MAX(CASE WHEN f.shortname = 'employeeid' THEN uid.data ELSE '' END) AS 'Employee No.',
    CONCAT(u.firstname,' ',u.lastname) AS 'Full Name',
    CONCAT('<a target="_new" href="%%WWWROOT%%/user/profile.php?id=',u.id,'">', u.username,'</a>') AS 'User Name',
        MAX(CASE WHEN f.shortname = 'Organisation' THEN uid.data ELSE '' END) AS 'Organisation',
    c.fullname AS 'Course',
DATE_FORMAT(FROM_UNIXTIME(p.timeenrolled),'%m/%d/%Y') AS 'Enrollment Date',
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%m/%d/%Y') AS 'Completion Date',
DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(p.timecompleted),INTERVAL 1 YEAR), '%m/%d/%Y') AS 'Expiration Date' 

FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_field f ON uid.fieldid = f.id

WHERE f.shortname IN ('employeeid', 'Organisation') IS NOT NULL

%%FILTER_COURSES:Course%%
%%FILTER_USERS:uid.data%%
%%FILTER_SEARCHTEXT:uid.data:~%%
GROUP BY u.id

The results in the table is perfectly fine but whenever I apply the filters that I have (e.g: Filter by Organisation which is a drop down) it will remove the data's in the Employee Column but it will still show the other data's in other columns.

If I use the Search Text filter which is for Employee ID the data's in Organisation will be gone.

Is there a way to prevent that? Help pls. Thanks.

PS: I am using Configurable reports for creating the SQL Query in Moodle

score 3 · Accepted Answer · answered Dec 20 '16 at 08:01

You may have more success with the following:

SELECT 
    employee.data AS 'Employee No.',
CONCAT(u.firstname,' ',u.lastname) AS 'Full Name',
CONCAT('<a target="_new" href="%%WWWROOT%%/user/profile.php?id=',u.id,'">', u.username,'</a>') AS 'User Name',
    org.data AS 'Organisation',
c.fullname AS 'Course',
DATE_FORMAT(FROM_UNIXTIME(p.timeenrolled),'%m/%d/%Y') AS 'Enrollment Date',
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%m/%d/%Y') AS 'Completion Date',
DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(p.timecompleted),INTERVAL 1 YEAR), '%m/%d/%Y') AS 'Expiration Date' 

FROM prefix_course_completions p
JOIN prefix_cours c ON p.course = c.id
JOIN prefix_user u ON p.userid = u.id
LEFT JOIN (
   SELECT d.userid, d.data
     FROM prefix_user_info_data d
     JOIN prefix_user_info_field f ON f.id = d.fieldid AND f.shortname = 'employeeid'
) employee ON employee.userid = u.id
LEFT JOIN (
   SELECT d.userid, d.data
     FROM prefix_user_info_data d
     JOIN prefix_user_info_field f ON f.id = d.fieldid AND f.shortname = 'Organisation'
) org ON org.userid = u.id

WHERE 1=1

%%FILTER_COURSES:Course%%
%%FILTER_USERS:employee.data%%
%%FILTER_SEARCHTEXT:org.data:~%%

Hi Thanks for this really super. Just made a little change in the filters %%FILTER_USERS:org.data%% %%FILTER_SEARCHTEXT:employee.data:~%% Thank you so much. I'll wish you so much a very merry christmas and happy new year @davosmith — Nightshade, Dec 20 '16 at 13:12

SQL Moodle using custom profile fields

1 Answers1