private List<Aluno> ValidarAlunosGDAE(Formulario formulario) { var listaAlunosRaInvalidos = new List<Aluno>(); var rasDuplicados = new List<Aluno>(); foreach (var item in formulario.Aluno) { var alunoRaInvalido = new Aluno(); if (!(ValidarRaAlunoGDAE(item.RA))) { alunoRaInvalido.RA = item.RA; alunoRaInvalido.Nome = item.Nome; listaAlunosRaInvalidos.Add(alunoRaInvalido); } } var listaAgrupada = formulario.Aluno.GroupBy(_ => _.RA); if (listaAgrupada.Count() < formulario.Aluno.Count()) { foreach (var item in formulario.Aluno) { var alunoDuplicado = new Aluno(); var alunos = formulario.Aluno.Where(_ => _.RA == item.RA); if (alunos.Count() > 1) { alunoDuplicado.RA = item.RA; alunoDuplicado.Nome = item.Nome; listaAlunosRaInvalidos.Add(alunoDuplicado); } } } return listaAlunosRaInvalidos; }
public JsonResult Salvar(Formulario formulario) { string _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString; SqlConnection conn = new SqlConnection(_connectionString); StringBuilder sb = new StringBuilder(); //VALIDAR SE RA´s existem no GDADE var alunosInvalidos = ValidarAlunosGDAE(formulario); try { if (alunosInvalidos.Count() == 0) { SqlCommand cmdDelete = new SqlCommand(String.Format("delete Calendario4edicao where idcursoturnoturma = {0} and mesReferencia ='{1}'", formulario.IdCursoTurnoTurma.ToString(), formulario.MesReferencia), conn); cmdDelete.Connection.Open(); cmdDelete.ExecuteNonQuery(); cmdDelete.Connection.Close(); if (formulario.Calendario != null && formulario.Calendario.Count > 0) { foreach (var item in formulario.Calendario) { sb.Append("insert into Calendario4edicao(IdCursoTurnoTurma,DiaLetivo,CargaHoraria,MesReferencia,CpfSupervisor) values(").Append(formulario.IdCursoTurnoTurma.ToString()).Append(",'").Append(item.Dia).Append("','").Append(item.CargaHoraria).Append("','").Append(formulario.MesReferencia).Append("','").Append(formulario.Cpf).Append("')"); SqlCommand cmd = new SqlCommand(sb.ToString(), conn); cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); sb.Clear(); } } if (formulario.Aluno != null & formulario.Aluno.Count > 0) { foreach (var item in formulario.Aluno) { if (item.Estagio == null) item.Estagio = ""; if (item.RA == null) item.RA = ""; sb.Append("insert into aluno4edicao(IdMatricula,HorasEstagio,IgnorarAluno,AprovadoVence,RAGDAE,MesReferencia,IdCursoTurnoTurma,IdInscricao) values(").Append(item.Matricula).Append(",'").Append(item.Estagio).Append("',").Append(item.IgnorarAluno.ToString()).Append(",") .Append(item.AprovadoVence).Append(",'").Append(item.RA).Append("','").Append(formulario.MesReferencia).Append("',") .Append(formulario.IdCursoTurnoTurma).Append(",").Append(item.Inscricao).Append(")"); SqlCommand cmd = new SqlCommand(sb.ToString(), conn); cmdDelete.CommandText = String.Format("delete aluno4edicao where idMatricula = '{0}' and MesReferencia = '{1}'", item.Matricula, formulario.MesReferencia); SqlCommand cmdUpdate = new SqlCommand(string.Format("update aluno4edicao set RAGDAE = '{1}' where idMatricula = '{0}'", item.Matricula, item.RA), conn); cmd.Connection.Open(); cmdDelete.ExecuteNonQuery(); cmd.ExecuteNonQuery(); cmdUpdate.ExecuteNonQuery(); cmd.Connection.Close(); sb.Clear(); cmdDelete.Connection.Open(); cmdDelete.CommandText = String.Format("delete Frequencia4Edicao where idMatricula='{0}' and MesReferencia ='{1}' and idCursoTurnoTurma={2}", item.Matricula, formulario.MesReferencia, formulario.IdCursoTurnoTurma.ToString()); cmdDelete.ExecuteNonQuery(); cmdDelete.Connection.Close(); if (item.Presenca != null) { foreach (var item2 in item.Presenca) { sb.Append("insert into Frequencia4Edicao(IdMatricula,DiaPresenca,MesReferencia,IdCursoTurnoTurma,IdInscricao) values(").Append(item.Matricula).Append(",'").Append(item2.DiaLetivo).Append("','") .Append(formulario.MesReferencia.ToString()).Append("',").Append(formulario.IdCursoTurnoTurma) .Append(",").Append(item.Inscricao).Append(")"); cmd.Connection.Open(); cmd = new SqlCommand(sb.ToString(), conn); cmd.ExecuteNonQuery(); cmd.Connection.Close(); sb.Clear(); } } } } } } catch (Exception ex) { return Json(ex.Message, JsonRequestBehavior.AllowGet); } return Json(alunosInvalidos, JsonRequestBehavior.AllowGet); }
public JsonResult Token(Formulario formulario) { string _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString; string retorno = string.Empty; DateTime dtFinalizado = new DateTime(); SqlConnection conn = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand(@"select AES, DataFinalizado from token where chave = '" + formulario.Token + "'", conn); cmd.Connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { retorno = reader["AES"].ToString(); dtFinalizado = reader["DataFinalizado"] != DBNull.Value ? Convert.ToDateTime(reader["DataFinalizado"]) : DateTime.MinValue; } conn.Close(); return Json(retorno, JsonRequestBehavior.AllowGet); }
public JsonResult BuscarTurmas(Formulario filtros) { List<Turma> turmas = new List<Turma>(); string _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString; SqlConnection conn = new SqlConnection(_connectionString); turmas.Add(new Turma { Id = 0, Nome = "Selecione..." }); SqlCommand cmd = new SqlCommand(@"select distinct a.idCursoTurnoTurma,(select (curso + '/' + b.DescTurno + '/' + nomeTurma) as turno from CursoTurnoTurma a join turno b on a.turno = b.turno join CursoMtdTurno c on a.idcursomtdturno = c.idcursomtdturno join cursos d on d.codCurso = c.codCurso where idcursoturnoturma = vwmtd.idCursoTurnoTurma) from vw_mantida_curso_edicao as vwmtd join Cursoturnoturma a on a.idcursoTurnoTurma = vwmtd.idcursoturnoturma where numero_aes = '" + filtros.NumeroAES + @"' and a.StatusCursoTurma <> 3 and item_aes = " + filtros.ItemAES, conn); cmd.Connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { turmas.Add(new Turma { Id = Convert.ToInt32(reader[0]), Nome = reader[1].ToString() }); } conn.Close(); var jsonSerialiser = new JavaScriptSerializer(); var json = jsonSerialiser.Serialize(turmas); return Json(json, JsonRequestBehavior.AllowGet); }