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;
}
