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