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