private AlunosAtivosViewModel ObterAlunos(string AES) { var alunosViewModel = new AlunosAtivosViewModel(); var aes = new AES(); string _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString; var conn = new SqlConnection(_connectionString); var listaAlunos = new List<Alunos>(); string comando = string.Format(@" select cod_curso, cod_mantenedora, cod_de, Numero_AES, Mantenedora, Mantida, Item_AES, NomeCurso , vagas, cod_mantida, (select matriculados from [autorizacaoServicoCursoMtd] where codMtn = wmc.cod_mantenedora and edicao = wmc.edicao and codMtd = wmc.Cod_Mantida and codCurso = wmc.Cod_Curso ) as matriculados from [dbo].[vw_mantida_curso] wmc where Numero_AES = '{0}' order by Mantida, Item_AES ", AES); var cmd = new SqlCommand(comando, conn); cmd.Connection.Open(); var reader = cmd.ExecuteReader(); while (reader.Read()) { var alunos = new Alunos(); alunos.CodCurso = reader["cod_curso"] != DBNull.Value ? Convert.ToInt32(reader["cod_curso"]) : 0; alunos.CodMantenedora = reader["cod_mantenedora"] != DBNull.Value ? Convert.ToInt32(reader["cod_mantenedora"]) : 0; alunos.CodDe = reader["cod_de"] != DBNull.Value ? Convert.ToInt32(reader["cod_de"]) : 0; alunos.NumeroAes = reader["Numero_AES"].ToString(); alunos.Mantenedora = reader["Mantenedora"].ToString(); alunos.ItemAes = reader["Item_AES"].ToString(); alunos.Curso = reader["NomeCurso"].ToString(); alunos.TotalVagas = reader["vagas"] != DBNull.Value ? Convert.ToInt32(reader["vagas"]) : 0; alunos.TotalMatriculados = reader["matriculados"] != DBNull.Value ? Convert.ToInt32(reader["matriculados"]) : 0; alunos.Mantida = reader["Mantida"].ToString(); alunos.CodMantida = reader["cod_mantida"] != DBNull.Value ? Convert.ToInt32(reader["cod_mantida"]) : 0; listaAlunos.Add(alunos); } alunosViewModel.ListarAlunos = listaAlunos; aes.CodDE = listaAlunos.FirstOrDefault().CodDe; aes.NumeroAes = listaAlunos.FirstOrDefault().NumeroAes; aes.CodMantenedora = listaAlunos.FirstOrDefault().CodMantenedora; aes.Mantenedora = listaAlunos.FirstOrDefault().Mantenedora; alunosViewModel.AES = aes; return alunosViewModel; }
private List<AES> ObterAES(int codDe, int codEdicao) { string _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString; var conn = new SqlConnection(_connectionString); var listaAES = new List<AES>(); string comando = string.Format(@" select cod_de, Numero_AES, Mantenedora, edicao from [dbo].[vw_mantida_curso] where cod_de = {0} and (EDICAO = {1} or {2} = 0) group by cod_de, Numero_AES, Mantenedora, edicao order by Numero_AES, Mantenedora ", codDe, codEdicao, codEdicao); var cmd = new SqlCommand(comando, conn); cmd.Connection.Open(); var reader = cmd.ExecuteReader(); while (reader.Read()) { var aes = new AES(); aes.Mantenedora = reader["Mantenedora"].ToString(); aes.CodDE = reader["cod_de"] != DBNull.Value ? Convert.ToInt32(reader["cod_de"]) : 0; aes.NumeroAes = reader["Numero_AES"].ToString(); aes.CodEdicao = reader["edicao"] != DBNull.Value ? Convert.ToInt32(reader["edicao"]) : 0; listaAES.Add(aes); } return listaAES.Count() > 0 ? listaAES.Where(_ => _.CodEdicao ==4).ToList() : listaAES; }