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.

Listar Alunos que Ainda não Acessaram o Curso no Moodle num Determinado Período com Comando SQL

    O Moodle oferece poucas opções de relatórios gerencias. Por exemplo, caso você queira saber quais são os alunos que não acessaram o ambiente do curso nos último 10 dias, você vai ter que dar vários cliques. Terá que emitir um relatório de acesso para cada alunos individualmente e checar se acessou ou não nos últimos 10 dias.  Caso seu curso tiver 50 alunos imagine a quantidade de cliques para emitir um simples relatório! Se você é tutor do curso, com muita razão reclama do excesso de clique para extrair um simples relatório. Aí sobra o pipino para o programador. Mais uma vez a equipe pedagógica fica no cangote do programador cobrando uma solução.

    Se você é um programador do Moodle  e estiver nessa fria, não esquente. Venha aqui para o Moodle SQL que temos a solução. Você só precisa fazer junção das tabelas da matricula com a do log por exclusão. Explicando em miúdos, extrai uma lista de alunos matriculados  e exclua dessa lista todos os usuários  que constam na lista de log. Os usuários que não constam na lista do log, são as que não acessaram o curso. 

Bem, vamos ver como isso fica no código SQL:

SELECT u.id, u.firstname,u.lastname,u.email 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 u.id NOT IN (SELECT DISTINCT userid FROM mdl_log WHERE course=? AND time >=? AND time <=?)

Passe o parâmetro id do curso  em e.instanceid=? da consulta principal e course=? da subconsulta.

Passe  o parâmetro da primeira e  segunda data em time >=? AND time <=?

Não esqueça que a data no banco de dados do Moodle é convertido em quantidade de segundos. Pois, os parâmetros da data  devem ser convertidos em número de quantidade de segundos.

Analisando o código SQL,  parte marcada em amarelo indica  a relação dos alunos que estão matriculados no curso. A parte em cinza indica o filtro dos usuários que acessaram o curso em um determinado período. A parte em vermelha exclui  da lista dos alunos matriculados os que já acessaram, sobrando assim, os que ainda não acessaram no período definido.

 Viu como é moleza. Agora é só rodar o código em uma linguagem de programação da sua preferência. Esse código só não funciona nas versões do Moodle inferior a 1.7. Agora boa programação. Faça um relatório bonito, assim ficará bem na fita com a equipe pedagógica. Caso ainda queira fazer uma média, monte uma rotina que envie e-mail a lista alunos dos alunos filtrado na consulta SQL. 

Codificação PHP

Agora vai de brinde a implementação do código PHP desse post. Acese o link:
http://moodlephp.blogspot.com/2010/12/listar-alunos-que-ainda-nao-acessaram-o.html

Desmistificando Período de Inscrição do Curso no Moodle com Comando SQL

    Caso você esteja programando para Moodle e estiver roendo a unha tentando entender como funciona o período de validade de inscrição no curso do  Moodle, dê uma relaxada e leia esse post. Aqui será explicado as regras de funcionamento a nível da camada de aplicação e o armazenamento no banco de dados. 
   
    Regras de Funcionamento
    Período da validade de inscrição define o tempo da validade da matrícula do aluno ou tutor. É definido em quantidade de dias. A data da validade da inscrição, ou seja, data em que expira a matrícula é computada de seguinte forma: data da inscrição (data do dia que a inscrição foi feita) + quantidades de dias da validade. Por exemplo, caso o período da validade for definida em 20 dias e um aluno for inscrito no dia 1 de dezembro, a data final da validade da inscrição será dia 21 de dezembro. 
   
No Moodle 2.0 a data de inscrição pode ser personalizada. Até versão 1.9 essa data era definida automaticamente, ou seja, a data do dia em que a inscrição foi feita no curso.

    Quando o período da inscrição expirar o aluno não perde o acesso ao curso automaticamente. A inscrição continua ativa.  O cancelamento ocorre quando o cron for executado. Pois, uma das ações do cron é apagar todos as inscrições em que a data da validade já tenha expirada. Isso aconteceu em alguns teste que fiz. Certamente há alguma configuração que ativa isso. Se você ouvir alguém reclamando que os alunos inscritos sumiram do curso de uma hora para outro ou perderam acesso não tenha dúvida, a culpa é do cron que sai apagando tudo que está fora do prazo.

    A boa notícia é que ao excluir a inscrição do aluno, os dados de log de acesso, nota e participação nas atividades não serão apagados em efeito cascata. Continuam na base de dados. Se o aluno for reescrito, tudo volta ao normal como que se nada tivesse acontecido.
   
    Quando o período da validade de inscrição não for definido  significa que é ilimitado. Isto é, a inscrição nunca será apagada pelo cron.

Armazenamento de Dados nas Tabelas
    Agora que você já entendeu como funciona o período da validade de inscrição, vamos ver como  é estruturada no banco de dados. 
   
As informações são registradas em duas tabelas:

• mdl_course – Tabela do curso
• mdl_role_assignments – Tabela da matrícula, ou seja, inscrição nos cursos

Na tabela  mdl_course são registradas as configurações gerais do curso. A coluna enrolperiod dessa tabela registra o período da validade em quantidade de dias. Se o período for ilimitado, o valor dessa coluna será zero. Se a validade  for de um dia será registrado o seguinte valor: 86400 e se for de 20 dias o valar será 1728000.  Nesse momento você deve estar achando tudo muito estranho e até  questionando  por que esse número esquisito.

Bem vamos lá, é muito simples. Todo o campo da tabela do banco de dados do Moodle registra a data em quantidade de segundos.   Pois, o campo enrolperiod armazena dias em quantidade de segundo. Para decifrar a quantidade de dias basta dividir o valor da coluna por 60 segundos e por 60 minutos e,  por último, por 24 horas. Então 86400/60/60/24=1 ou 1728000/60/60/24=20.

A tabela mdl_role_assignments armazena os dados das inscrições. O período de validade da inscrição de cada usuário fica nas seguintes colunas:
•    timestart – data inicial da inscrição
•    timeend – data final da inscrição

A data inicial é a data em que a inscrição foi feita. A data final é a data da inscrição atualizada com os dias da validade da inscrição. Essa data é calculada a partir da data inicial adicionada os dias da validade de inscrição definida no formulário de inscrição que é acessado a  partir do link designar funções no ambiente do curso. Nesse formulário há um campo período de validade de inscrição para selecionar os dias. Esse campo traz como opção padrão o período da validade da inscrição definida em nível do curso.

Relatórios com SQL
Se até aqui ficou bem claro, vamos agora extrair os relatórios sobre o período da validade de inscrição com comando SQL.

Período de validade de inscrição por curso
SELECT id,fullname,startdate,enrolperiod FROM mdl_course

Essa consulta retorna uma lista de curso com as seguintes informações:

• id – id  do curso
• fullname- Nome do curso
• startdate – Data de início do curso
• enrolperiod – Período de validade do curso em quantidade de dias (convertido em segundos)

Período de validade de inscrição por participante de um determinado curso

SELECT u.firstname,u.lastname,rs.timestart,rs.timeend 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 e.instanceid=?

Passe id do  curso no  parâmetro e.instanceid=?
Essa consulta retorna uma lista de curso com as seguintes informações:

• u.firstname – Nome do participante
• u.lastname – Sobrenome do participante
•  rs.timestart – Data inicial da inscrição
• rs.timeend – Data final da inscrição (data em que a inscrição expira)

Período de validade de inscrição de um determinado  participante

SELECT c.id,c.fullname, rs.timestart,rs.timeend FROM mdl_course c INNER JOIN mdl_context e ON c.id=e.instanceid INNER JOIN  mdl_role_assignments rs ON e.id=rs.contextid WHERE e.contextlevel=50 AND rs.userid=?
Passe id do  usuário  no  parâmetro  rs.userid=?

Essa consulta retorna uma lista de curso que um determinado aluno ou tutor está matriculado acompanhado da data inicial e final da validade de inscrição.
A consulta retorna:

• c.id - id  do curso
• c.fullname - Nome do curso
• rs.timestart - Data inicial da inscrição
• rs.timeend - Data final da inscrição (data em que a inscrição expira)

Bem, finalmente desvendamos mais um segredo do Moodle. Agora só falta você fazer um relatório customizado bonito e entregar à equipe pedagógica ou ao seu chefe que via regra não entende nada da parte técnica para variar. Este quando faz uma demanda quer que a resposta seja para ontem  e ficam no seu cangote achando que tudo é muito simples como que se fosse uma padaria.

Veja Também:
Relatório da Configuração do Período da Validade da Inscrição no Curso do Moodle com Programação PHP

Relatório do Período da Validade da Inscrição dos Participantes no Curso do Moodle com Programação PHP

Data de inscrição do aluno no curso do Moodle

Relatório de Acesso no Moodle por Cidades e País com Comando SQL

Esse poste tem por objetivo criar um relatório que mapeie o acesso ao Moodle por cidade ou país. Faz um rastreamento de acesso por endereço dos usuários. Pois, identifica de qual cidade ou país os usuários acessam o Moodle com maior frequência.

Esse relatório será montado em forma de comando SQL. As informações serão extraídas das seguintes tabelas:

• mdl_user - Tabela de usuário. Dessa tabela será extraída o endereço  (cidade e  país);

• mdl_log – Tabela de log. Dessa tabela será extraída a quantidade de acesso por endereço do usuário (cidade e  país);

Comando SQL que extrai relatório por cidade:
SELECT u.city, COUNT(DISTINCT u.id) AS quant_user, COUNT(l.id) AS quant_acesso,COUNT(l.id)/COUNT(DISTINCT u.id) AS media_acesso  FROM mdl_user u INNER JOIN mdl_log l ON u.id=l.userid GROUP BY u.city  ORDER BY COUNT(l.id)/COUNT(DISTINCT u.id) DESC
   
A consulta retorna quatro colunas:
u.city – Nome da cidade dos usuários cadastrado na tabela mdl_user
quant_user – Quantidade de usuários que moram na cidade
quant_acesso – Quantidade de acesso ao Moodle feito por todos os moradores da cidade
media_acesso  - Média de acesso por cidade. É computado pela quantidade de acesso divididos por número de moradores. É o acesso proporcional por morador, ou seja, por usuário. 

O resultado é organizado por média de acesso. Ou seja cidade que ficar no topo da lista é a cidade cujo usuários são mais ativos, ou seja, acessam o Moodle com maior frequência.
   
Para extrair o mesmo relatório por país, basta substituir a  coluna u.city para  u.country. O restante do comando fica o mesmo.

Comando SQL que extrai relatório por país:
SELECT u.country, COUNT(DISTINCT u.id) AS quant_user, COUNT(l.id) AS quant_acesso,COUNT(l.id)/COUNT(DISTINCT u.id) AS media_acesso  FROM mdl_user u INNER JOIN mdl_log l ON u.id=l.userid GROUP BY u.country ORDER BY COUNT(l.id)/COUNT(DISTINCT u.id) DESC
   
Nesse momento pode ainda não estar satisfeito e questionar:
-Por que não mapear a cidade e o país por IP de acesso?
    Essa pergunta é pertinente. Mais a ideia desse relatório é mapear quais cidades cujos usuários são mais ativos  no que tange ao acesso e não de qual local o acesso é feito. Por outro lado, rastrear local de acesso pelo IP não garante a confiabilidade dos dados uma vez que o IP pode ser forjado através de proxy anônimo. Bem esse papo é assunto para um outro poste.

    Execute o comando SQL diretamente na base de dados ou numa linguagem de programação e conheca o perfil de acesso do seu aluno por região, ou seja, cidade ou país. Se você fizer isso, certamente a equipe pedagógica vai gostar muito.

Matricular Usuário no Grupo/Turma do Moodle com Comando SQL

    Para cadastrar um usuário (aluno,tutor etc) em um grupo  grupo, ou seja, turma de um curso no Moodle sem ser pela interface gráfica é necessário executar o comando SQL diretamente na base de dados ou em uma linguagem de programação.
   
    Para matricular um aluno em um grupo de usuário, primeiro é necessário matriculá-lo no curso.  Em seguida, adicioná-lo a um grupo.  Neste post não abordamos como efetuar matrícula  no curso. Caso queira explorar isso, clique aqui e acesse o post que aborda esse assunto.

Antes de avançar é bom entender como o grupo de usuário é organizado no banco de dados do Moodle.
    Os registros do grupo  ficam em duas tabelas:
mdl_groups  - Tabela que armazena nome do grupo e curso que está vinculado
mdl_groups_members  - Tabela que armazena os membros de cada grupo

    O comando SQL que insere usuário no grupo é:
INSERT INTO mdl_groups_members (groupid,userid,timeadded) VALUES (?,?,?)

Passe os parâmetros:
     groupid – Id do grupo. Chave primária da tabela mdl_groups.
     userid- Id do usuário.  Chave primária da tabela mdl_user.
     timeadded – Data do cadastro. Data em formato numérico: timestamp em segundos. 


Para processar cadastro em lote é melhor executar o código dentro de uma linguagem de programação.