Extrair id da Matrícula do Aluno no Curso do Moodle com Comando SQL

    No Moodle a matrícula dos alunos, tutores, admin e.t.c  é feita na tabela mdl_role_assignments. Cada registro nessa tabela corresponde ao vínculo de um usuário no contexto do sistema, categoria do curso e curso. O id gerado nessa tabela é uma chave única de identificação da matrícula no sistema Moodle.
  
 Para extrair o id da matrícula de um determinado aluno, execute o seguinte comando     SQL:

SELECT rs.id, u.firstname,u.lastname 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=? AND rs.userid=?

Passe o parâmetro id do curso em e.instanceid=? e id do usuário em rs.userid=?. rs.roleid=5 define que será feito filtro por aluno. Caso pretenda extrair pelo perfil do tutor, altere o valor para 3.

Essa consulta extrai os seguintes campos:

• rs.id - Id da matrícula gerado na tabela mdl_role_assignments
• u.firstname - Nome do usuário na tabela mdl_user
• u.lastname - Sobrenome usuário na tabela mdl_user

Para extrair uma lista com  id da matrícula de todos os alunos em um determinado curso, execute o seguinte comando     SQL:

SELECT rs.id, u.firstname,u.lastname 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=?

Passe o parâmetro id do curso em e.instanceid=?. rs.roleid=5 define que será feito filtro por aluno. Caso pretenda extrair lista de tutor, altere o valor para 3.

A única diferença desse comando com a anterior é que o filtro  do usuário rs.userid=? foi retirado do comando WHERE. Os campos retornados são os mesmos da consulta anterior.

Até então já deve ter ficado claro que o  id gerado na tabela mdl_role_assignments é a chave única de inscrição  de cada usuário. Essa chave não é usada como chave estrangeira na tabela de log ou atividades que o aluno faz no curso. Isso porque caso a matrícula for cancelada, essa chave será excluída, as atividades e os  logs gerados não serão apagados em cascata. Os registros do aluno no curso continuam mesmo que a inscrição tenha sido cancelada. 

No sistema de log e atividades feitas pelos participantes do curso, a chave de identificação é a combinação do id do usuário gerado na tabela mdl_user e id do curso gerado na tabela mdl_course.
    Entender como é a estrutura da tabela da matrícula, ou seja, da inscrição dos usuários no curso do Moodle é fundamental para programar o Moodle ou efetuar integração do Moodle com um sistema acadêmico.

Mapear Alunos Inscritos nos Cursos do Moodle com Comando SQL

Para  mapear os usuários cadastrados no Moodle que estão inscritos em algum curso com perfil aluno, certamente a soluções é consultar diretamente o banco de dados do Moodle com comando SQL. 

Tabelas
Para fazer esse mapeamento, é necessário fazer a consulta nas seguintes tabelas:

• mdl_user – Tabela de usuário
• mdl_role_assignments – Tabela da matrícula, ou seja, da inscrição do usuário no curso

Comando SQL
SELECT DISTINCT  u.id, u.firstname,u.lastname,u.email FROM mdl_role_assignments rs INNER JOIN mdl_user u ON u.id=rs.userid  WHERE rs.roleid=5

Essa consulta filtra  apenas  os alunos. O filtro é feito no comando WHERE rs.roleid=5. Por padrão, 5 é o id do perfil do aluno na tabela mdl_role.  Caso um aluno estiver inscrito em mais de um curso, o comando DISTINCT elimina a duplicação.

Se um usuário tiver perfil de aluno em um curso e administrador em outro ele será incluído na lista. Caso você queira excluir esse tipo de usuário, altere o comando SQL para fazer exclusão dos usuários com perfil de aluno e  administrador compartilhado.  O novo comando ficará assim:

SELECT DISTINCT  u.id, u.firstname,u.lastname,u.email FROM mdl_role_assignments rs INNER JOIN mdl_user u ON u.id=rs.userid  WHERE rs.roleid =5 AND rs.userid NOT IN(SELECT userid FROM mdl_role_assignments WHERE roleid=1)

Foi adicionado a parte do código em destaque que exclua todos os administradores.

A primeira  consulta não  retorna nenhum usuário que não tenha perfil do aluno. A segunda exclui da lista os alunos que  tenham também perfil de administrador. 

Relatório Completo de Nota de um Curso no Moodle com Comando SQL

    Caso você queira extrair um relatório completo de nota que exibe todas as avaliações e a nota final para todos os alunos inscritos no curso, será necessário fazer consultas SQL na camada de base de dados e montar relatório por meio de uma linguagem de programação. Neste post será explorado apenas a parte de consulta ao banco de dados.

Para montar um relatório completo de nota, o procedimento é bem simples. Será explicado por passo.

1º Passo  – Extrair a  lista dos alunos inscritos no curso

Segue o comando SQL que recupere da base de dados todos os alunos matriculados  em um determinado curso.

SELECT u.id, u.firstname,u.lastname 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=?

Nesse comando, você só precisa passar o parâmetro  e.instanceid que deve ser o id do curso.
Caso  queira entender um pouco mais sobre esse comando clique aqui.

Esse comando retorna da base de dados os seguintes dados:

• u.id – Id do usuário na tabela mdl_user
• u.firstname – Nome do usuário
• u.lastname – Sobrenome do usuário

2º Passo  – Extrair a  lista de todas as avaliações

Para extrair a relação de todos as avaliações de um determinado curso, basta consultar a tabela  mdl_grade_items. Segue o comando SQL:

SELECT id,itemname,itemtype,gradetype,scaleid FROM mdl_grade_items WHERE courseid=?

Passe o parâmetro id do curso em courseid=?

Esta consulta retorna os seguintes dados:

• id – Id da avaliação
• itemname – Nome da avaliação
• itemtype – Tipo da avaliação. Há três tipos padrões: course, mod e manual.

• course – É uma avaliação do curso, ou seja, a nota final. 
• mod  - É uma avaliação criada a partir de uma atividade do curso. 
• manual – É uma avaliação criado manualmente.

• gradetype – Tipo de nota. Valor padrão: 1 e 2.

• 1- Indica que a nota numérica.
• 2 – Indica que a nota não é numérica. Neste caso a nota é definida por                 escala na tabela mdl_scale

• scaleid – Indica a escala de nota definida na tabela mdl_scale

Caso a nota não for numérica, é necessário recuperar a escala de nota com esse comando.

SELECT scale FROM mdl_scale  WHERE id=?

Substitua o parâmetro id=? pelo valor retornado na coluna scaleid da consulta anterior.

Essa consultar retorna o seguinte dado:
•    scale – Relação da escala de nota separada por vírgula.

3º Passo  – Extrair a  lista de nota de todas as avaliações

    Para extrair uma lista de nota de todas as avaliações de um determinado curso, execute o seguinte comando sql:

SELECT g.id,g.itemid,g.userid,g.finalgrade FROM mdl_grade_grades g INNER JOIN mdl_grade_items i ON g.itemid=i.id WHERE i.courseid=?

Passe o parâmetro id do curso em i.courseid=?

4º Passo  –  Montar Layout do relatório
    Agora só falta organizar todos os dados extraídos nas consultas e montar uma tabela de nota com as seguintes colunas:

Nome do Aluno | Avaliação 1|  Avaliação 2 | Avaliação n|  Nota final

Em cada linha da tabela deve ser  impresso o nome do aluno e  a nota da avaliação de forma sincronizada. Essa sincronização pode ser feita através de uma matriz cujo índice é a combinação do id do usuário e id da avaliação. Por exemplo,  idusr/idavaliacao=nota. Isso pode ser facilmente implementada através da linguagem de programação da sua preferência.

Observação
A nota final é a avaliação cujo itemtype é course.
Caso o tipo de nota for escala (gradetype=2), o valor da nota equivale ao índice, ou seja, posição da escala da lista de nota separada por vírgula. Se  o valor da nota for 4, a nota é a quarta  posição da lista. Se a escala for: Péssimo, Ruim, Razoável, Bom, Muito  Bom; a quarta  posição é Bom, pois está será a nota.

Até aqui tudo já deve ter ficado claro como montar um relatório completo de nota. É importante ressaltar que o código SQL para extrair a avaliação e a nota não são compatíveis com a versão do Moodle  inferior a 1.9. O que extrai a lista dos alunos inscritos não é compatível com a versão inferior a 1.7.
   
    Conhecer como funciona a estrutura das tabelas de notas no banco de dados do Moodle, viabiliza a possibilidade de customizar os relatórios bem como a integração do Moodle com outros sistemas. Agora só falta você arregaçar as mangas e iniciar a programação na língua da sua preferência.