mdl-fcv.sql

SELECT u.id, u.username, u.deleted, c.id as courseids, m.situacao
FROM mdl_user u
JOIN mdl_user_enrolments ue ON ue.userid = u.id
JOIN mdl_enrol e ON e.id = ue.enrolid
JOIN mdl_course c ON c.id = e.courseid
LEFT JOIN mdl_unifcv_matriculas m ON m.userid = u.id
WHERE
    ue.status = 0
  AND e.roleid = 5
  AND c.id NOT IN
    (SELECT c2.id
    FROM mdl_course c2
    JOIN mdl_groups g ON g.courseid = c2.id
    JOIN mdl_groups_members gm ON gm.groupid = g.id
    WHERE gm.userid = u.id)
  AND (m.situacao IS NULL OR
    m.situacao IN ('ATIVA',
                   'AGUARDANDO',
                   'APROVADO',
                   'APROVADO_PARCIALMENTE')
   OR
    (m.situacao IN ('CANCELADA',
                 'TRANCADA',
                 'DESISTENTE',
                 'REPROVADO',
                 'TRANSFERIDA')
  AND m.timemodified < 1658080532)
    )
  AND u.id IN (SELECT id FROM mdl_user u2 WHERE u2.deleted = 0)
GROUP BY u.id, m.situacao