Hi,
I am having trouble generating a report with the following information:
- All users (first name, last name, email)
- Course name
- Last access date (or "Never Access" if the user has never logged in)
- Final grade
- some custom fields I created for users
I am using the ad-hoc query plugin but I am not able to get the report I want. I am getting many duplicates on the query results. I am getting four identical rows for each user (I know it has something to do with the custom fields, since there are 4 of those, but have not been able to figure out how to solve it)
Here is a copy of the query I am running (it does not have the grades information, since I have not been able to figure that out yet)
SELECT
u.firstname as nombre,
u.lastname as apellido,
u.email as email,
c.shortname as curso,
IF(ue.status = 0, 'Active', 'Suspended') as estado,
e.enrol,
FROM_UNIXTIME(ue.timecreated, '%m/%d/%Y %h:%i:%s %p') as fecha_registro,
IF(ue.timestart = 0, 'No End Date',FROM_UNIXTIME(ue.timestart, '%m/%d/%Y %h:%i:%s %p')) as inicio_registro,
IF(ue.timeend = 0, 'No End Date',FROM_UNIXTIME(ue.timeend, '%m/%d/%Y %h:%i:%s %p')) as fin_registro,
COALESCE((
SELECT
FROM_UNIXTIME(la.timeaccess,'%m/%d/%Y %h:%i:%s %p')
FROM
prefix_user_lastaccess as la
WHERE
u.id = la.userid
AND
c.id = la.courseid
), 'Nunca') as Ultimo_Acceso,
(
SELECT
info.fieldid
FROM
prefix_user_info_data as info
WHERE
info.userid = u.id
AND
info.fieldid = '4'
) as Regional,
(
SELECT
info.fieldid
FROM
prefix_user_info_data as info
WHERE
info.userid = u.id
AND
info.fieldid = '5'
) as Ciudad,
(
SELECT
info.fieldid
FROM
prefix_user_info_data as info
WHERE
info.userid = u.id
AND
info.fieldid = '6'
) as Provincia,
(
SELECT
info.fieldid
FROM
prefix_user_info_data as info
WHERE
info.userid = u.id
AND
info.fieldid = '7'
) as Franquicia
FROM
prefix_user_enrolments as ue,
prefix_enrol as e,
prefix_course as c,
prefix_user as u,
prefix_user_info_data as tabla_regional
WHERE
ue.userid = u.id
AND
ue.enrolid = e.id
AND
e.courseid = c.id
AND
tabla_regional.userid = u.id
ORDER BY
u.lastname,
u.firstname,
c.startdate DESC,
c.shortname