Consultas Banco de dados - MOODLE

--###  Lista professores  seus respectivos cursos ###--

SELECT u.id, 

                u.firstname, 

               u.lastname, 

               c.fullname

FROM mdl_user u

JOIN mdl_role_assignments ra ON ra.userid = u.id

JOIN mdl_context cx ON cx.id = ra.contextid

JOIN mdl_course c ON c.id = cx.instanceid

WHERE ra.roleid =4

LIMIT 0 , 30

--###  Consulta alunos Turma ###--

select * from mdl_course
where  shortname = '12;250477'   -- Nome breve do curso
----------------------------------------------------------------------------------------------------

--###  Consulta Quantidade de acessos por dia  ###--

SELECT COUNT( * )
FROM (


SELECT l.userid, u.username, DATE_FORMAT( FROM_UNIXTIME( MAX( TIME ) ) , "%d/%m/%Y - %T" ) AS ULT_ACESSO, DATE_FORMAT( FROM_UNIXTIME( MAX( TIME ) ) , "%d/%m/%Y" ) AS ULT_ACESSO2
FROM `mdl_log` l
INNER JOIN mdl_user u ON u.id = l.userid
WHERE ACTION = "login"
GROUP BY l.userid
)usr
WHERE usr.ult_acesso2 = "02/04/2020"

----------------------------------------------------------------------------------------------------
--###  Consulta Quantidade de acessos por usuario  ###--

SELECT u.id, u.firstname, u.lastname, COUNT( l.id ) AS qvisita
FROM mdl_user u
INNER JOIN mdl_log l ON u.id = l.userid
WHERE ACTION = 'login'
GROUP BY u.id, u.firstname, u.lastname
ORDER BY COUNT( l.id ) DESC
LIMIT 0 , 30

----------------------------------------------------------------------------------------------------

Logins ultimas 24h

select sum(total_user) as total , hour(time) as hora from (
select count(userid) total_user, from_unixtime(time - 10800) as time from

mdl_log a

where a.action = 'login'
and a.module = 'user'

and time > unix_timestamp() - 86400
and userid <> 1

group by time

order by time

) tab1

group by hour(time)

__________________________________________________________________________
Usuarios distintos

select count(*) from mdl_user where lastaccess >unix_timestamp() - 86400

__________________________________________________________________________

Usuarios em uma semana

SELECT COUNT( * )
FROM mdl_user
WHERE lastaccess > ( UNIX_TIMESTAMP( ) -604800 )

SELECT u.id,
                u.firstname,
                u.lastname,
                u.username
FROM mdl_role_assignments rs
                INNER JOIN mdl_user u ON u.id=rs.userid
                INNER JOIN mdl_context e ON rs.contextid=e.id
WHERE e.contextlevel=50 AND rs.roleid=5 AND e.instanceid=568 -- ID do curso

____________________________________________________________________________

Lista nota final dos alunos por periodo

SELECT DISTINCT u.id, u.username, c.shortname, c.fullname, g.finalgrade
FROM mdl_grade_items i
INNER JOIN mdl_course c ON c.id = i.courseid
INNER JOIN mdl_course_categories ct ON ct.id = c.category
INNER JOIN mdl_grade_grades g ON i.id = g.itemid
INNER JOIN mdl_user u ON u.id = g.userid
INNER JOIN mdl_role_assignments ra ON ra.userid = u.id
INNER JOIN mdl_role r ON r.id = ra.roleid
WHERE i.itemtype = 'course'
AND ct.path LIKE "/94/95/742%"
AND r.shortname = "student"
ORDER BY r.shortname, c.fullname
LIMIT 0 , 30


___________________________________________________________________________

Lista nota final de um respectivo aluno e todas suas disciplinas

SELECT u.id,u.username,c.shortname, c.fullname, g.finalgrade
FROM mdl_grade_items i
INNER JOIN mdl_course c ON c.id=i.courseid
INNER JOIN mdl_course_categories ct ON ct.id = c.category
INNER JOIN mdl_grade_grades g ON i.id=g.itemid
INNER JOIN mdl_user u ON u.id=g.userid
WHERE i.itemtype = 'course'
AND ct.path LIKE "/94/95/742%"
AND g.userid = 7899
ORDER by c.fullname