public List<DadosAES> ObterItemAESPorNumeroAES(string numeroAES)
{
List<DadosAES> listaAES = new List<DadosAES>();
DbHelper db = new DbHelper();
string cmdText = @"select b.Numero_AES, b.Item_AES
from Frequencia4Edicao a, [dbo].[vw_mantida_curso_turma] b
where 1=1
and a.idCursoTurnoTurma = b.idCursoTurnoTurma
and b.Numero_AES = @Numero_AES
group by b.Numero_AES, b.Item_AES
order by 2";
db.AddParameter(new System.Data.SqlClient.SqlParameter("@Numero_AES", numeroAES));
SqlDataReader dr = db.GetDataReader(cmdText);
if (dr.HasRows)
{
while (dr.Read())
{
listaAES.Add(new DadosAES
{
NumeroAES = dr["Numero_AES"].ToString(),
ItemAES = Convert.ToInt32(dr["Item_AES"])
});
}
}
db.CloseDbConnection();
return listaAES;
}
private List<DadosAES> ObterAES(int codDe, bool somentePlanoContigencia)
{
var lista = new List<DadosAES>();
DbHelper db = new DbHelper();
db.AddParameter(new System.Data.SqlClient.SqlParameter("@codDe", codDe));
string cmdText = string.Format(@"SELECT wmc.Cod_DE,
mant.edicao,
mant.NM_Credenciamento,
mant.Numero_AES,
mant.Mantida,
dt.Diretoria,
mant.Item_AES,
mant.Cod_Mantida,
format(parc.mesInicial,'MM/yyyy') as mes_ref,
parc.tipoparcela,
CASE parc.tipoparcela
WHEN 'F' THEN 'SIM' ELSE 'NÃO'
END AS PARCELA_FINAL,
parc.staPLCV
from [dbo].vw_mantida mant,
[VENCE].[dbo].[MtdParcela] parc,
[dbo].[vw_mantida_curso] wmc,
DiretoriaTokenContigencia dt
where {0}
and wmc.cod_de = @codDe
and wmc.Cod_DE = dt.CD_DE
and wmc.Cod_Mantida = mant.Cod_Mantida
and mant.idContratoMtd = PARC.idContratoMtd
group by wmc.Cod_DE,
mant.edicao,
mant.NM_Credenciamento,
mant.Numero_AES,
mant.Mantida,
dt.Diretoria,
mant.Item_AES,
mant.Cod_Mantida,
parc.staPLCV,
parc.mesInicial,
parc.tipoparcela
order by mant.Cod_Mantida,
mant.Item_AES,
parc.mesInicial", (somentePlanoContigencia == true ? "parc.staPLCV is not null" : "1=1"));
SqlDataReader dr = db.GetDataReader(cmdText);
if (dr.HasRows)
{
while (dr.Read())
{
lista.Add(new DadosAES
{
CodMantida = Convert.ToInt32(dr["Cod_Mantida"]),
Mantida = dr["Mantida"].ToString(),
CodDE = dr["Cod_DE"] != DBNull.Value ? Convert.ToInt32(dr["Cod_DE"]) : 0,
NumeroAES = dr["Numero_AES"].ToString(),
Diretoria = dr["Diretoria"].ToString(),
ItemAES = Convert.ToInt32(dr["Item_AES"]),
MesReferencia = dr["mes_ref"].ToString(),
TipoParcela = dr["tipoparcela"].ToString(),
EhParcelaFinal = dr["PARCELA_FINAL"].ToString(),
staPLCV = dr["Cod_DE"] != DBNull.Value ? dr["staPLCV"].ToString() : ""
});
}
}
db.CloseDbConnection();
return lista;
}
private int ObterDeApartirDoToken(string chave)
{
DbHelper db = new DbHelper();
db.AddParameter(new System.Data.SqlClient.SqlParameter("@chave", chave));
string cmdText = "select cd_de from [dbo].[DiretoriaTokenContigencia] where token = @chave";
int codDe = db.GetExecuteScalar<int>(cmdText);
db.CloseDbConnection();
return codDe;
}
public List<VwAcompMensal> ObterListagemObjetos(string numeroAES, int itemAES)
{
var acompList = new List<VwAcompMensal>();
DbHelper db = new DbHelper();
db.AddParameter(new System.Data.SqlClient.SqlParameter("@numeroAES", numeroAES));
db.AddParameter(new System.Data.SqlClient.SqlParameter("@itemAES", itemAES));
string cmdText = @"select b.Numero_AES, b.Item_AES, count(distinct idMatricula) qtdAlunos, a.MesReferencia
from Frequencia4Edicao a, [dbo].[vw_mantida_curso_turma] b
where 1=1
and a.idCursoTurnoTurma = b.idCursoTurnoTurma
and b.Numero_AES = @numeroAES
and b.Item_AES = @itemAES
--and staPLCV = 'P'
group by b.Numero_AES, b.Item_AES, a.MesReferencia
order by 1,2";
SqlDataReader dr = db.GetDataReader(cmdText);
if (dr.HasRows)
{
while (dr.Read())
{
acompList.Add(new VwAcompMensal
{
NumeroAES = dr["Numero_AES"].ToString(),
ItemAES = Convert.ToInt32(dr["Item_AES"]),
QtdAlunos = Convert.ToInt32(dr["qtdAlunos"]),
MesReferencia = dr["MesReferencia"].ToString()
});
}
}
db.CloseDbConnection();
return acompList;
}
