Hello, everybody,
I have the following challenge: I need to create a report where the manager can only see his users.
I did this by comparing two attributes (from the Active Directory). Now comes the next step, which frustrates me.
I want to have a column in my report where the status of the Scorm course appears. So whether the final exam is open or passed. How can you simply query this?
Here is my previous query:
SELECT
CONCAT(user2.firstname,' ',(user2.lastname)) AS Name,
user2.firstname AS Vorname,
user2.lastname AS Nachname,
c.fullname AS Pflichtfortbildung
FROM prefix_user_enrolments AS ue
JOIN prefix_enrol AS e ON e.id = ue.enrolid
JOIN prefix_course AS c ON c.id = e.courseid
JOIN prefix_user AS user2 ON user2 .id = ue.userid
LEFT JOIN prefix_user_lastaccess AS ul ON ul.userid = user2.id
JOIN prefix_user_info_data d1 ON d1.userid = ue.userid
JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'manager'
JOIN prefix_user_info_data d2 ON d2.userid = %%USERID%%
JOIN prefix_user_info_field f2 ON d2.fieldid = f2.id AND f2.shortname = 'BenutzerDN'
%%FILTER_SEARCHTEXT:u.lastname%%
WHERE d1.DATA = d2.DATA AND (ul.timeaccess IS NOT NULL OR ul.timeaccess IS NULL)
Thanks & Greetings
