public JsonResult FindAlunos(int codigoTurma, int codigoModulo)
{
conn.Open();
string sql = @"select a.idAluno, t.idTurma, mo.idModulo, p.nome, nt.nota1, nt.nota2, nt.qtdFalta, nt.situacaoAluno, nt.notaFinal, m.idMatricula
from aluno a
join pessoa p on a.idPessoa = p.idPessoa
join matricula m on a.idAluno = m.idAluno
join matriculaTurma mt on m.idMatricula = mt.idMatricula
join turma t on mt.idTurma = t.idTurma
join curso c on t.idCurso = c.idCurso
join modulo mo on mo.idCurso = c.idCurso
left join notaFalta nt on nt.idAluno = a.idAluno
and nt.idModulo = mo.idModulo and nt.idTurma = t.idTurma
where t.idTurma = @idTurma and mo.idModulo = @idModulo and m.tipo = 'matricula'";
SqlCommand comm = conn.CreateCommand();
comm.CommandText = sql;
comm.Parameters.Add(new SqlParameter("@idTurma", codigoTurma));
comm.Parameters.Add(new SqlParameter("@idModulo", codigoModulo));
SqlDataReader dr = comm.ExecuteReader();
List <Models.AlunoViewData> listAlunos = new List <Models.AlunoViewData>();
while (dr.Read())
{
Models.AlunoViewData a = new Models.AlunoViewData();
a.IdAluno = dr.GetInt32(0);
a.IdTurma = dr.GetInt32(1);
a.IdModulo = dr.GetInt32(2);
a.Nome = dr.GetString(3);
a.Nota1 = !dr.IsDBNull(4) ? dr.GetDecimal(4) : (decimal?)null;
a.Nota2 = !dr.IsDBNull(5) ? dr.GetDecimal(5) : (decimal?)null;
a.Faltas = !dr.IsDBNull(6) ? dr.GetInt32(6) : 0;
a.situacaoAluno = !dr.IsDBNull(7) ? dr.GetInt32(7) : 0;
a.notaFinal = !dr.IsDBNull(8) ? dr.GetDecimal(8) : (decimal?)null;
a.IdMatricula = dr.GetInt32(9);
listAlunos.Add(a);
}
conn.Close();
return(Json(new { alunos = listAlunos }, JsonRequestBehavior.AllowGet));
}
public JsonResult FindAlunos(int codigoTurma, int codigoModulo)
{
conn.Open();
string sql = @"select a.idAluno, t.idTurma, mo.idModulo, p.nome, nt.nota1, nt.nota2, nt.qtdFalta, nt.situacaoAluno, nt.notaFinal, m.idMatricula
from aluno a
join pessoa p on a.idPessoa = p.idPessoa
join matricula m on a.idAluno = m.idAluno
join matriculaTurma mt on m.idMatricula = mt.idMatricula
join turma t on mt.idTurma = t.idTurma
join curso c on t.idCurso = c.idCurso
join modulo mo on mo.idCurso = c.idCurso
left join notaFalta nt on nt.idAluno = a.idAluno
and nt.idModulo = mo.idModulo and nt.idTurma = t.idTurma
where t.idTurma = @idTurma and mo.idModulo = @idModulo and m.tipo = 'matricula'";
SqlCommand comm = conn.CreateCommand();
comm.CommandText = sql;
comm.Parameters.Add(new SqlParameter("@idTurma", codigoTurma));
comm.Parameters.Add(new SqlParameter("@idModulo", codigoModulo));
SqlDataReader dr = comm.ExecuteReader();
List<Models.AlunoViewData> listAlunos = new List<Models.AlunoViewData>();
while (dr.Read())
{
Models.AlunoViewData a = new Models.AlunoViewData();
a.IdAluno = dr.GetInt32(0);
a.IdTurma = dr.GetInt32(1);
a.IdModulo = dr.GetInt32(2);
a.Nome = dr.GetString(3);
a.Nota1 = !dr.IsDBNull(4) ? dr.GetDecimal(4) : (decimal?)null;
a.Nota2 = !dr.IsDBNull(5) ? dr.GetDecimal(5) : (decimal?)null;
a.Faltas = !dr.IsDBNull(6) ? dr.GetInt32(6) : 0;
a.situacaoAluno = !dr.IsDBNull(7) ? dr.GetInt32(7) : 0;
a.notaFinal = !dr.IsDBNull(8) ? dr.GetDecimal(8) : (decimal?)null;
a.IdMatricula = dr.GetInt32(9);
listAlunos.Add(a);
}
conn.Close();
return Json(new { alunos = listAlunos }, JsonRequestBehavior.AllowGet);
}