/// <summary> /// Retorna todos os registros do cadastro de cotistas /// </summary> /// <returns></returns> public List <CotistaInfo> ObterCadastroCotistas() { List <CotistaInfo> ret = new List <CotistaInfo>(); SqlConnection conn = null; try { conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ClubesFundos"].ConnectionString); conn.Open(); SqlDataAdapter lAdapter; DataSet dtSet = new DataSet(); SqlCommand sqlCmd = new SqlCommand("prc_sel_cotistas", conn); sqlCmd.CommandType = System.Data.CommandType.StoredProcedure; sqlCmd.Connection = conn; lAdapter = new SqlDataAdapter(sqlCmd); lAdapter.Fill(dtSet); for (int i = 0; i < dtSet.Tables[0].Rows.Count; i++) { DataRow lRow = dtSet.Tables[0].Rows[i]; CotistaInfo info = new CotistaInfo(); info.Agencia = lRow["Agencia"].ToString(); info.BancoCli = lRow["Banco"].ToString(); info.Conta = lRow["Conta"].ToString(); info.SubConta = lRow["SubConta"].ToString(); info.CPFCNPJ = lRow["dsCpfCnpj"].ToString(); info.DataImportacao = lRow["dtImportacao"].DBToDateTime(); info.DigitoConta = lRow["DigitoConta"].ToString(); info.IdCotista = lRow["idCotista"].DBToInt32(); info.IdCotistaItau = lRow["idCotistaItau"].DBToString(); info.Nome = lRow["dsNome"].DBToString(); ret.Add(info); } logger.Debug("Obteve lista de cotistas com [" + dtSet.Tables[0].Rows.Count + "] itens"); } catch (Exception ex) { logger.Error("ObterCadastroCotistas(): " + ex.Message, ex); } finally { if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); } } return(ret); }
/// <summary> /// /// </summary> /// <param name="origemDnl"></param> /// <returns></returns> public List <CotistaInfo> ObterCotistas() { List <CotistaInfo> lRetorno = new List <CotistaInfo>(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdmFundos"].ConnectionString); sqlConn.Open(); SqlDataAdapter lAdapter; DataTable table = new DataTable(); string sqlQuery = "SELECT * FROM tbCotista"; SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn); sqlCmd.CommandType = System.Data.CommandType.Text; lAdapter = new SqlDataAdapter(sqlCmd); lAdapter.SelectCommand.Connection = sqlConn; lAdapter.Fill(table); if (table.Rows.Count > 0) { for (int i = 0; i < table.Rows.Count; i++) { CotistaInfo info = new CotistaInfo(); info.CodCotista = table.Rows[i]["codCotista"].ToString(); info.CpfCnpj = table.Rows[i]["cpfCnpj"].ToString(); info.FlagIsencaoIR = table.Rows[i]["flgIsencaoIR"].ToString().ToUpperInvariant().Equals("S"); info.IDCotista = table.Rows[i]["idCotista"].DBToInt32(); info.NomeCotista = table.Rows[i]["nomeCotista"].ToString(); info.Operador = table.Rows[i]["dsOperador"].ToString(); info.TipoPessoaCotista = table.Rows[i]["tpPessoaCotista"].ToString(); lRetorno.Add(info); } } sqlConn.Close(); sqlConn.Dispose(); return(lRetorno); }
public bool InserirCotista(CotistaInfo cotista) { SqlConnection conn = null; try { conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ClubesFundos"].ConnectionString); conn.Open(); SqlCommand sqlCmd = new SqlCommand("prc_ins_cotista", conn); sqlCmd.CommandType = System.Data.CommandType.StoredProcedure; sqlCmd.Parameters.Add(new SqlParameter("@idCotistaItau", cotista.IdCotistaItau)); sqlCmd.Parameters.Add(new SqlParameter("@dsNome", cotista.Nome)); sqlCmd.Parameters.Add(new SqlParameter("@dsCpfCnpj", cotista.CPFCNPJ)); sqlCmd.Parameters.Add(new SqlParameter("@stAtivo", cotista.StatusAtivo)); sqlCmd.Parameters.Add(new SqlParameter("@dtImportacao", cotista.DataImportacao)); sqlCmd.Parameters.Add(new SqlParameter("@Banco", cotista.BancoCli)); sqlCmd.Parameters.Add(new SqlParameter("@Agencia", cotista.Agencia)); sqlCmd.Parameters.Add(new SqlParameter("@Conta", cotista.Conta)); sqlCmd.Parameters.Add(new SqlParameter("@DigitoConta", cotista.DigitoConta)); sqlCmd.Parameters.Add(new SqlParameter("@SubConta", cotista.SubConta)); sqlCmd.ExecuteNonQuery(); conn.Close(); } catch (Exception ex) { logger.Error("InserirCadastroCotista():" + ex.Message, ex); if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); } return(false); } return(true); }
public Dictionary <string, CotistaInfo> PopulateCotistaInfoSinacor( ) { OracleConnection objORAConnection = new OracleConnection(); objORAConnection.ConnectionString = ConfigurationManager.ConnectionStrings["TRADE"].ConnectionString; objORAConnection.Open(); string sqlQuery = ""; sqlQuery += " SELECT DISTINCT(TSCCLIGER.CD_CPFCGC) AS CD_CPFCGC, "; sqlQuery += " TSCCLIGER.IN_SITUAC AS IN_SITUAC, "; sqlQuery += " TSCCLIGER.NM_CLIENTE AS NM_CLIENTE, "; sqlQuery += " TSCCLIGER.TP_PESSOA AS TP_PESSOA, "; sqlQuery += " TSCCLIGER.TP_CLIENTE AS TP_CLIENTE, "; sqlQuery += " TSCENDE.NM_LOGRADOURO, "; sqlQuery += " TSCENDE.NR_PREDIO, "; sqlQuery += " TSCENDE.NM_COMP_ENDE, "; sqlQuery += " TSCENDE.NM_BAIRRO, "; sqlQuery += " TSCENDE.NM_CIDADE, "; sqlQuery += " TSCENDE.SG_ESTADO, "; sqlQuery += " TSCENDE.SG_PAIS, "; sqlQuery += " TSCENDE.CD_CEP AS CEP, "; sqlQuery += " TSCENDE.CD_CEP_EXT AS CEPEXT, "; sqlQuery += " TSCTIPCLI.DS_TIPO_CLIENTE AS DS_TIPO_CLIENTE "; sqlQuery += " FROM TSCCLIGER, TSCTIPCLI, TSCENDE "; sqlQuery += " WHERE TSCCLIGER.TP_CLIENTE = TSCTIPCLI.TP_CLIENTE "; sqlQuery += " AND TSCENDE.CD_CPFCGC = TSCCLIGER.CD_CPFCGC "; Dictionary <string, CotistaInfo> dctPessoasSinacor = new Dictionary <string, CotistaInfo>(); using (OracleCommand objORACommand = objORAConnection.CreateCommand()) { objORACommand.CommandText = sqlQuery; OracleDataReader odr = objORACommand.ExecuteReader(CommandBehavior.CloseConnection); if (odr.HasRows) { while (odr.Read()) { try { CotistaInfo info = new CotistaInfo(); info.CpfCnpj = OracleConvert.GetNumber("CD_CPFCGC", odr).ToString(); info.NomeCotista = OracleConvert.GetString("NM_CLIENTE", odr); info.TipoPessoaCotista = OracleConvert.GetString("TP_PESSOA", odr); info.TipoClienteSinacor = OracleConvert.GetInt("TP_CLIENTE", odr); info.CEP = OracleConvert.GetInt("CEP", odr).ToString("00000") + OracleConvert.GetInt("CEPEXT", odr).ToString("000"); info.Endereco = OracleConvert.GetString("NM_LOGRADOURO", odr).Trim() + ", " + OracleConvert.GetInt("NR_PREDIO", odr).ToString() + " - " + OracleConvert.GetString("NM_COMP_ENDE", odr) + " - " + OracleConvert.GetString("NM_BAIRRO", odr) + " - " + OracleConvert.GetString("NM_CIDADE", odr); info.Estado = OracleConvert.GetString("SG_ESTADO", odr); info.Pais = OracleConvert.GetString("SG_PAIS", odr); if (!dctPessoasSinacor.ContainsKey(info.CpfCnpj)) { dctPessoasSinacor.Add(info.CpfCnpj, info); } } catch (Exception ex) { } } } return(dctPessoasSinacor); } }
public void PopulateCotistaInfoSinacor(ref Dictionary <int, CotistaInfo> dicCotistas) { OracleConnection objORAConnection = new OracleConnection(); objORAConnection.ConnectionString = ConfigurationManager.ConnectionStrings["TRADE"].ConnectionString; objORAConnection.Open(); string sqlQuery = ""; sqlQuery += " SELECT DISTINCT(TSCCLIGER.CD_CPFCGC) AS CD_CPFCGC, "; sqlQuery += " TSCCLIGER.IN_SITUAC AS IN_SITUAC, "; sqlQuery += " TSCCLIGER.NM_CLIENTE AS NM_CLIENTE, "; sqlQuery += " TSCCLIGER.TP_PESSOA AS TP_PESSOA, "; sqlQuery += " TSCCLIGER.TP_CLIENTE AS TP_CLIENTE, "; sqlQuery += " TSCTIPCLI.DS_TIPO_CLIENTE AS DS_TIPO_CLIENTE "; sqlQuery += " FROM TSCCLIGER, TSCTIPCLI "; sqlQuery += " WHERE TSCCLIGER.TP_CLIENTE = TSCTIPCLI.TP_CLIENTE "; Dictionary <string, CotistaInfo> dctPessoasSinacor = new Dictionary <string, CotistaInfo>(); using (OracleCommand objORACommand = objORAConnection.CreateCommand()) { objORACommand.CommandText = sqlQuery; OracleDataReader odr = objORACommand.ExecuteReader(CommandBehavior.CloseConnection); if (odr.HasRows) { while (odr.Read()) { try { CotistaInfo info = new CotistaInfo(); info.CpfCnpj = OracleConvert.GetNumber("CD_CPFCGC", odr).ToString(); info.NomeCotista = OracleConvert.GetString("NM_CLIENTE", odr); info.TipoPessoaCotista = OracleConvert.GetString("TP_PESSOA", odr); info.TipoClienteSinacor = OracleConvert.GetInt("TP_CLIENTE", odr); if (!dctPessoasSinacor.ContainsKey(info.CpfCnpj)) { dctPessoasSinacor.Add(info.CpfCnpj, info); } } catch (Exception ex) { } } } foreach (CotistaInfo cotista in dicCotistas.Values) { if (dctPessoasSinacor.ContainsKey(cotista.CpfCnpj)) { cotista.TipoClienteSinacor = dctPessoasSinacor[cotista.CpfCnpj].TipoClienteSinacor; cotista.TipoPessoaCotista = dctPessoasSinacor[cotista.CpfCnpj].TipoPessoaCotista; } else { cotista.TipoClienteSinacor = 99; cotista.TipoPessoaCotista = "INCERTO"; } } } }