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