terça-feira, 4 de janeiro de 2011

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.

37 comentários:

  1. Desafio...

    Na abordagem que utilizamos aqui onde trabalho, nós temos o seguinte:

    - Usamos a categoria de curso como nosso curso.
    - Usamos o curso como nossa disciplina

    O pessoal aqui me pediu um relatório assim:

    Aluno | Disciplina 1 | Disciplina 2 | ...
    fulano | 10 | 20 | ...
    ciclano| 5 | 13 | ...

    Estou com muita dificuldade para fazer um SQL para montar esse relatório...

    1 - Estou pegando todos os alunos da categoria e agrupando pelo ID... dessa forma se tivermos um aluno matriculado em mais de uma disciplina (curso) ele não vai pegar o registro dele duplicado.

    2 - Depois de pegar todos os alunos eu pego as notas delas em todas as disciplinas (curso).

    Estou no caminho certo?

    ResponderExcluir
  2. Você está no caminho certo. Agora armazena a nota de cada aluno em um array cuja chave deve ser a combinação do id do aluno e id do curso, ou seja, da disciplina. Depois é só fazer o loop.

    ResponderExcluir
  3. E para fazer ao contrario, para atribuir uma nota a um aluno, suponhamos que ele tenha feito a prova presencial, em um curso semi-presencial e eu queira disponibilizar a nota do aluno no moodle, isso é possível?

    ResponderExcluir
  4. Para efetuar uma operação inversa, ou seja, inserir uma nota com comando SQL, siga os seguintes passos:

    a) Crie a avaliação manualmente no Moodle. Caso for efetuar isso por meio do SQL, faça um INSERT na tabela mdl_grade_items;

    b) Recupere o id da avaliação criado no passo anterior. Com isso, faça INSERT na tabela mdl_grade_grades, a tabela de nota. O id recuperado do passo anterior deve ser inserido no campo itemid da tabela mdl_grade_grades. É a chave estrangeira da avaliação na tabela de nota.

    ResponderExcluir
  5. muito obrigado, vc é mesmo o melhor...

    ResponderExcluir
  6. Estou tentando montar a matriz, mas não estou conseguindo. Poderia me dar uma pequena luz. Abraço.

    ResponderExcluir
    Respostas
    1. Fernando,

      Para você montar uma matriz de nota e imprimir na tela, siga os seguintes passos:

      1) Crie um array com todas as avaliações

      2) Crie um array com todos os alunos

      3) Crie um array com todas as notas com a seguinte chave de identificação: id_aluno/id_avaliação. Se o id do aluno for 10 e o id da avaliação for 15, a chave de identificação será 10/15

      4) Faça loop no array de avaliação e imprime o cabeçalho da tabela:
      Nome do Aluno , Avaliação 1, Avaliação, Avaliação n

      5) Faça um loop no array do aluno e imprime o nome do aluno na primeira coluna. Na coluna 2, coluna 3 e coluna n faça um loop do array da avaliação e recupere a nota do aluno em cada avaliação no array de nota criado no item 3) através da chave de identificação id_aluno/id_avaliação

      Excluir
    2. Cara, que luz você me deu, porém como estou iniciando com DEV estou tendo dificuldade de apresentar os dados passando o indice iduser/idtividade. O array já está criando mas estou fazendo n loops não consigo apresentar os dados.
      Muito obrigado pela ajuda.
      Abraço,

      Excluir
    3. Consegui Badiu. Muito obrigado cara. Excelente artigo!
      Abraço,

      Excluir
    4. Fernando,
      Que bom que conseguiu! Você está desenvolvendo um plugin de relatório de nota?

      Excluir
    5. Sim, devido a algumas especificidades da coordenação aqui. Abs

      Excluir
    6. Beleza Fernando
      Desenvolver um plugin é o caminho mais indicado para customizar o Moodle. Isso não cria restrição para o processo de atualização.

      Excluir
  7. Olá Badiu, bom dia.
    Estamos com um mistério aqui na nossa empresa.
    Os alunos matriculados no moodle, derrepente, suas notas "somem".
    Porque será que isso está acontecendo?

    Pode me ajudar com isso?

    ResponderExcluir
    Respostas
    1. Fernando,
      O sumiço de nota pode ocorrer nas seguintes situações:
      1)Usuário com perfil admin ou tutor pode excluir a nota na interface do Moodle
      2)Excluir registro de nota diretamente na tabela da base de dados
      3)Falha do sistema Moodle uma vez que a integridade de dados na base entre PK/FK é muito baixa.
      Para averiguar essa situação, me informa os seguintes dados:

      1- Qual é a versão do seu Moodle?
      2- A nota que sumiu é de um usuário ou de vários usuários ?
      3- Se for de vários usuários, o que há em comum entre esses usuários?
      4- Quando que a nota começou a sumir?
      5- Nota de qual atividade que sumiu?
      6- Em qual interface do Moodle notou sumiço de nota?
      7- A nota de atividade do Moodle é lançada no módulo da atividade e replicada na tabela geral do Moodle. Por exemplo, a nota do questionário pode ser visualizada ao acessar o questionário e também quando acessar a tabela geral de notas. As notas que desapareceram tanto na tela de nota geral quanto na tela de nota da atividade?
      8- Que mudança ocorreu no Moodle antes do inicio do sumiço das notas?
      8- Mapeie o caso de um aluno e nota de uma atividade que sumiu e me informa para montarmos SQL para rastrear o que pode ter acontecido.

      Excluir
    2. Valeu por tudo...

      1) versão moodle 2.4
      2) aparentemente todos
      3) o que há em comum é o curso.
      4) foi notado hoje de manhã
      5) todas as atividades
      6)Relatório geral de notas
      7) bom saber disso, pois no módulo da atividade as notas existem, tanto que quando altero algum valor, ex: 6,0 para 6 , a nota volta juntamente com o comentário.
      8)Não sei te dizer.
      8) como seria esse mapeamento?

      Obrigado!

      Excluir
    3. Acredito que seja alguma coisa do pk da atividade e fk do relatório geral.

      Obrigado

      Excluir
    4. Thiago,
      Se as notas sumiram na tabela geral, para ter certeza que essas notas não estão na base de dados execute os seguintes comandos diretamente da base:

      a) Contar notas no curso
      SELECT COUNT(g.id) FROM mdl_grade_grades g INNER JOIN mdl_grade_items i ON g.itemid=i.id WHERE i.courseid=?

      b) Contar nota final do curso
      SELECT COUNT(g.id) FROM mdl_grade_grades g INNER JOIN mdl_grade_items i ON g.itemid=i.id WHERE i.courseid=? AND i.itemtype='course'

      d)Listar nota de um aluno no curso
      SELECT g.id,g.itemid,g.finalgrade FROM mdl_grade_grades g INNER JOIN mdl_grade_items i ON g.itemid=i.id WHERE i.courseid=? AND g.userid=?

      Passe o parâmetro id do curso em i.courseid=? Se o id do curso for 5 ficará assim: i.courseid=5

      O mesmo procedimento deve ser adotado para parâmetro g.userid=? referente ao id do usuário.
      Após a execução desses comandos, me informa o resultado de cada um. Se todos os comandos não retornarem nenhum registro, é sinal que a base foi excluída.

      Não tendo mesmo registro na base, a alternativa é fazer uma rotina php que leia a nota da tabela de de cada módulo e efetue lançamento na tabela geral.

      Excluir
    5. Este comentário foi removido pelo autor.

      Excluir
    6. Badiu,
      As duas primeiras consultas retornaram o esperado, mas a ultima consulta não retornou as notas de um aluno especifico(teste com 5 alunos).

      As notas dos questionários estão na tabela "mdl_quiz_attempts"
      As notas dos textos eletrônicos estão na tabela "mdl_assign_grades"

      Com base nessa informação, você pode me ajudar a realizar o procedimento na tabela geral?

      Grato!!

      Excluir
    7. Thiago,

      1- Qual foi o resultado retornado das consultas do item a) e b)? Retornaram zero? Se for zero, não há nenhum registro de nota na tabela geral. Sendo assim, o resultado da consulta do item d) será sempre vazia.

      2- Não tendo registro de nota na tabela geral, fica comprovada a hipótese que não há falha na aplicação do Moodle mas sim, que as notas foram excluída da base.

      3- Para replicar as notas excluídas da tabela geral será necessário o seguinte procedimento:

      a) Fazer leitura da nota de cada atividade e replicar na tabela geral. Isso requer uma programação em php ou qualquer outra linguagem. É necessário mapear a chave de cada avaliação na tabela mdl_grade_items e o seu correspondente na atividade avaliativa de cada módulo.

      b) Lançar nota final seguindo orientação de cálculo definido na configuração da nota final.

      Essa operação requer mapeamento afinado do relacionamento da tabela mdl_grade_items. As colunas dessa tabela que indicam o módulo e a instancia do módulo são: itemmodule e iteminstance

      Excluir
    8. Este comentário foi removido pelo autor.

      Excluir
  8. Acompanho seus blog's e você já resolveu muitos problemas meus com seus posts, atualmente atuo como desenvolvedor moodle...
    Estou com um problema serio, no meu ambiente...
    É o seguinte:
    O meu ambiente é composto por dois cursos em um está tudo perfeito, já no outro, as notas não aparecem,ou melhor aparece algumas.
    pode me ajudar?

    ResponderExcluir
  9. Badiu, bom dia.
    Seguindo o que você propôs, seguem os valores:
    o item 'a' retornou 53 registos, enquanto o item 'b' retornou 19.
    Usamos a versão 2.3 +. Todos os alunos são do mesmo curso.
    Desde já, obrigado.

    ResponderExcluir
    Respostas
    1. Fernando,
      Isso significa que na tabela geral de notas existe algum registro. É sinal que não as notas não desapareceram da base de dados. Agora, se ocorreu sumiço deve ser pontual de algum aluno. Neste caso precisa de uma investigação mais específica.

      Excluir
  10. Fernando, conseguiu resolver o seu problema?
    Eu tenho um problema parecido, acho que foi depois que atualizei para a 2.3 ou 2.4... agora estou na 2.5. Crio um questionário, consigo responder com usuários perfil estudante ou usuário autenticado, mas logo como professor e administrador e não consigo visualizar as respostas enviadas, nem relatório de notas, nada! E isso em todo o site...
    Minha desconfiança está no banco de dados... uso mysql com MyIsam, o moodle recomenda fortemente a conversão para InnoDB, talvez o MyIsam não está dando conta dos relacionamentos. O que vc acha Badiu?
    Vou fazer as consultas no banco e retorno o post.
    Obrigada Badiu, seu blog é ótimo.

    ResponderExcluir
    Respostas
    1. O formato InnoDB é mais apropriado para processar grande volume de dados. O formato MyIsam começa a apresentar problema com o crescimento do banco. Caso o Moodle estiver falhando, como estão relatando, é necessário diagnosticar a causa do erro. Para isso, sugiro ativar o modo debug. Veja as instruções nesse link como exibir erro do Moodle. http://moodlephp.blogspot.com.br/2011/03/ativar-exibicao-do-erro-no-moodle.html

      Excluir
  11. Fala Lino, tudo bem?

    Estou com uma situação aqui no meu trabalho. A responsável por cadastro das provas cadastrou uma questão errada, como que eu faço para anular a questão para todos os alunos mesmo para os que já responderam sem precisar ir aluno por aluno para alterar a questão.

    ResponderExcluir
    Respostas
    1. Oi Laercio, tudo fixe?

      Para excluir uma questão do questionário, de forma segura, deve ser realizada as seguintes operações:

      1) Excluir a questão do questionário;

      2) Reconfigurar o peso do questionário realocando o peso da questão removida para outras questões. Isso se aplica nas tentativas futuras de responder o questionário;

      3) Corrigir a nota dos alunos que já responderam o questionário;

      4) Corrigir a nota final devido a alteração da nota do questionário se for necessário.

      Todo esse procedimento deve ser feito por meio de um script php que deve ser desenvolvido.

      Pode ser que tenha algum jeito de fazer isso na interface, mas desconheço.

      Excluir
  12. Ya brow vou tentar aqui, depois volto para lhe informar o resultado muito obrigado.

    ResponderExcluir
  13. Tenho uma dúvida sobre a Nota Final.

    Como você disse, "A nota final é a avaliação cujo itemtype é course ", da tabela mdl_grade_items; o id desse item de nota, assim como os demais são incluídos na tabela geral de notas (mdl_grade_grades).

    Acontece que estou fazendo uma pesquisa em um moodle aqui e constatei que esse campo sempre está vazio (aqui o itemtype de course tem id = 15), e o curso já encerrou.

    Eu pergunto, é possível isso? Digo, dá pra usar outra configuração para registar a nota final?

    Percebi ainda a presença do itemname: "Nota da Final" (e o itemtype tá definido como "manual").

    Espero ter sido claro ao expor minha dúvida e gostaria de alguns esclarecimentos.

    Desde já, agardeço.

    ResponderExcluir
  14. Será que é possível incluir no relatório a DATA em que a nota foi inserida?

    ResponderExcluir
  15. Gostaria de saber se é possível o seguinte:
    1- Para um determinado questionário, saber a nota do aluno por categoria de questão, seja diretamente pelo Moodle ou por consulta SQL?

    Exemplo:
    o questionário tem 20 questões da "Cat A" e 30 da "Cat B"
    gostaria de saber quantos % ele acertou da "Cat A" e quantos % da "Cat B"

    Justificativa: preciso saber qual a categoria o aluno tem com menos acerto, ou seja, maior dificuldade.

    ResponderExcluir
    Respostas
    1. Ricardo,
      Não tenho esse código pronto, mas vou lhe passe um link no fórum da comunidade Badiu que monte lista de questões e alternativas de resposta para cada aluno. A partir daí, pode entender a lógica de relacionamento e monta o que você precisa. Segue o link: http://comunidade.badiu.com.br/mod/forum/discuss.php?d=248

      Excluir
  16. Olá Badiu,

    Estou realizando um sql de relatório de notas igual ao seu, saberia me dizer em qual tabela do BD se encontra os dados de (Iniciado, Completo e Tempo utilizado) que aparecem no relatório de notas? Não estou conseguindo achar. Poderia me ajudar?
    Obrigado!

    ResponderExcluir
    Respostas
    1. Lucas,
      Dê uma olhada nesse link da comunidade Badiu:
      http://comunidade.badiu.com.br/mod/forum/discuss.php?d=219 No código há data (timecreated,timemodified) que a nota foi gerada. Aproveite essa lógica para aplicar no seu código.

      Excluir
  17. Olá, como eu faria para retornar todos os registros, sem que seja de um curso especifico.

    Ex: Quero de todos os cursos, dessa maneira

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

    Consigo fazer uma Query pra isso?

    ResponderExcluir