-- Lista de accesos

SELECT CONCAT('<a target="_new" href="%%WWWROOT%%/user/view.php?id=',u.id,'">',u.username,'</a>') AS 'DNI', u.firstname AS 'Nombres',u.lastname AS 'Apellidos',
u.email AS 'Correo'

        ,(SELECT d1.data FROM prefix_user_info_data d1
          JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'posicion'
          WHERE d1.userid = u.id
         ) AS 'Posición'

        ,(SELECT d1.data FROM prefix_user_info_data d1
         JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'division'
         WHERE d1.userid = u.id
        ) AS 'División de personal'

        ,(SELECT d1.data FROM prefix_user_info_data d1
         JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'subdivision'
         WHERE d1.userid = u.id
        ) AS 'Subdivisión de personal'

, FROM_UNIXTIME(UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME(u.lastaccess)
, '-05:00', 'SYSTEM'))) AS 'Último acceso' -- might need to adjust +/- 5 hr
FROM prefix_user u
WHERE (FROM_UNIXTIME(u.lastaccess) > '2019-09-20') AND (u.id > 2)
AND (u.id <> 3543) AND (u.id <> 3549) AND (u.id <> 3554) AND (u.id <> 3555)


-- Reporte ISST: Jefe de Seguridad


SELECT  CONCAT('<a target="_new" href="%%WWWROOT%%/user/view.php?id=',u.id,'">',u.username,'</a>') AS 'DNI'
        ,u.firstname AS 'Nombres'
        ,u.lastname AS 'Apellidos'
        ,u.email AS 'Email'

        ,(SELECT d1.data FROM prefix_user_info_data d1
         JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'posicion'
         WHERE d1.userid = u.id
        ) AS 'Posición'

        ,(SELECT d1.data FROM prefix_user_info_data d1
         JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'division'
         WHERE d1.userid = u.id
        ) AS 'División de personal'

        ,(SELECT d1.data FROM prefix_user_info_data d1
         JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'subdivision'
         WHERE d1.userid = u.id
        ) AS 'Subdivisión de personal'

        ,IFNULL(ELT(MAX(IF(r.id = 423 AND cmc.coursemoduleid = 577, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado'),'No finalizado') AS 'Ley de Seguridad y Salud en el Trabajo'

        ,IFNULL(ELT(MAX(IF(r.id = 279 AND cmc.coursemoduleid = 391, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado'),'No finalizado') AS 'Conceptos Básicos de Seguridad y Salud en el Trabajo'

        ,IFNULL(ELT(MAX(IF(r.id = 280 AND cmc.coursemoduleid = 392, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado'),'No finalizado') AS 'Brigadas'

        ,IFNULL(ELT(MAX(IF(r.id = 281 AND cmc.coursemoduleid = 393, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado'),'No finalizado') AS 'Equipos de Protección Personal'

        ,IFNULL(ELT(MAX(IF(r.id = 282 AND cmc.coursemoduleid = 394, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado'),'No finalizado') AS 'Zonas de Riesgo en el Aeropuerto'

        ,IFNULL(ELT(MAX(IF(r.id = 277 AND cmc.coursemoduleid = 389, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado'),'No finalizado') AS 'IPER-C Jefe de Seguridad'

        ,IFNULL(ELT(MAX(IF(r.id = 283 AND cmc.coursemoduleid = 395, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado'),'No finalizado') AS 'Ergonomía'

        ,IFNULL(ELT(MAX(IF(r.id = 284 AND cmc.coursemoduleid = 396, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado'),'No finalizado') AS 'Reglamento Interno de Seguridad y Salud en el Trabajo'

        ,IFNULL(ELT(MAX(IF(r.id = 285 AND cmc.coursemoduleid = 397, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado'),'No finalizado') AS 'Comité de Seguridad y Salud en el Trabajo'

        ,IFNULL(ELT(MAX(IF(r.id = 286 AND cmc.coursemoduleid = 398, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado'),'No finalizado') AS 'Comunicaciones'



        ,IFNULL(ELT(MAX(IF(f.id = 2 AND cmc.coursemoduleid = 401, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado'),'No finalizado') AS 'Encuesta'

        ,IFNULL(ELT(MAX(IF(q.id = 10 AND cmc.coursemoduleid = 402, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado', 'Finalizado', 'No finalizado'),'No finalizado') AS 'Examen'

        ,IFNULL(MAX(IF(q.id = 10 AND gi.id = 82, ROUND(g.finalgrade), NULL)),'No finalizado') AS 'Nota'

        ,IFNULL(ELT(MAX(IF(cert.id = 1 AND cmc.coursemoduleid = 417, cmc.completionstate, NULL))+1,
            'No finalizado', 'Finalizado', 'Finalizado', 'No finalizado'),'No finalizado') AS 'Certificado'

FROM prefix_user u
INNER JOIN prefix_course_modules_completion cmc ON cmc.userid = u.id
INNER JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
INNER JOIN prefix_course c ON cm.course = c.id
INNER JOIN prefix_resource r ON c.id = r.course
INNER JOIN prefix_feedback f ON c.id = f.course
INNER JOIN prefix_quiz q ON c.id = q.course
INNER JOIN prefix_customcert cert ON c.id = cert.course
LEFT JOIN prefix_grade_grades g ON u.id = g.userid
LEFT JOIN prefix_grade_items gi ON g.itemid = gi.id AND gi.itemtype = 'mod' AND gi.itemmodule = 'quiz'  AND gi.iteminstance = q.id

WHERE (u.id > 2)  AND (u.firstaccess > 0)  AND (c.id = 115)

GROUP BY u.id
ORDER BY u.firstname ASC