protected void Page_Load(object sender, EventArgs e)
{
// (localdb)\MSSQLLocalDB
using (var context = new MoneyEntities())
{
// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
// Query
// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
var Historico = (from f in context.Fundo
join h in context.Historico on f.FundoID equals h.FundoID
orderby h.Data
select new HistoricoView {
Fundo = f.Categoria + " " + f.Nome, URL = f.URL, Data = h.Data, Rendimento = h.Rendimento
}).ToList();
var Meses = (from d in Historico.AsEnumerable() select new { d.Data, DataFormatada = d.Data.ToString("MMM") + "/" + d.Data.ToString("yy") }).Distinct();
var MesesFormatados = (from m in Meses select m.DataFormatada);
var Fundos = (from h in Historico orderby h.Fundo select h.Fundo).Distinct();
// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
// Gráfico - http://www.chartjs.org/samples/latest/
// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
json_Months = JsonConvert.SerializeObject(MesesFormatados);
var Datasets = new List <ChartDataset>();
var Cores = new List <string>(new string[] { "rgb(255, 99, 132)", "rgb(255, 159, 64)", "rgb(75, 192, 192)", "rgb(153, 102, 255)", "rgb(255, 205, 86)", "rgb(201, 203, 207)", "rgb(54, 162, 235)" });
var iCores = 0;
foreach (var fundo in Fundos)
{
var Dataset = new ChartDataset();
Dataset.label = fundo;
Dataset.data = new List <decimal>();
Dataset.borderColor = Cores[iCores];
Dataset.backgroundColor = Cores[iCores];
iCores = iCores == Cores.Count - 1 ? 0 : iCores + 1;
foreach (var mes in Meses)
{
var valor = (from h in Historico where h.Fundo == fundo && h.Data == mes.Data select h.Rendimento).SingleOrDefault();
if (valor == default(decimal))
{
Dataset.data.Add(0);
}
else
{
Dataset.data.Add(valor);
}
}
Datasets.Add(Dataset);
}
json_Datasets = JsonConvert.SerializeObject(Datasets);
// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
// Tabela
// ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
// Header
litTable.Text = "<thead>";
litTable.Text += "<tr>";
litTable.Text += "<th>Fundo</th>";
litTable.Text += "<th>Total 1 ano</th>";
litTable.Text += "<th>Total 2 anos</th>";
litTable.Text += "<th>Média Total</th>";
litTable.Text += "<th>Média 2</th>";
litTable.Text += "<th>Média 3</th>";
litTable.Text += "<th>Média 6</th>";
litTable.Text += "<th>Média 12</th>";
litTable.Text += "<th>Média 18</th>";
litTable.Text += "<th>Média 24</th>";
litTable.Text += "<th>Média 30</th>";
litTable.Text += "<th>Média 36</th>";
litTable.Text += "<th>Média 42</th>";
litTable.Text += "<th>Média 48</th>";
litTable.Text += "<th>Média 54</th>";
litTable.Text += "<th>Média 60</th>";
litTable.Text += "</tr>";
litTable.Text += "</thead>";
// Rows
litTable.Text += "<tbody>";
foreach (var fundo in Fundos)
{
var url = (from a in Historico where a.Fundo == fundo select a.URL).FirstOrDefault();
litTable.Text += "<tr>";
litTable.Text += "<td><a href=\"" + url + "\" target=\"_blank\">" + fundo + "</a></td>";
var data_min = (from A in Historico where A.Fundo == fundo select A.Data).Min();
// Totais
litTable.Text += GetTotal(fundo, Historico, data_min, 1);
litTable.Text += GetTotal(fundo, Historico, data_min, 2);
// Médias
litTable.Text += GetMedia(fundo, Historico, data_min, 9999);
litTable.Text += GetMedia(fundo, Historico, data_min, 2);
litTable.Text += GetMedia(fundo, Historico, data_min, 3);
litTable.Text += GetMedia(fundo, Historico, data_min, 6);
litTable.Text += GetMedia(fundo, Historico, data_min, 12);
litTable.Text += GetMedia(fundo, Historico, data_min, 18);
litTable.Text += GetMedia(fundo, Historico, data_min, 24);
litTable.Text += GetMedia(fundo, Historico, data_min, 30);
litTable.Text += GetMedia(fundo, Historico, data_min, 36);
litTable.Text += GetMedia(fundo, Historico, data_min, 42);
litTable.Text += GetMedia(fundo, Historico, data_min, 48);
litTable.Text += GetMedia(fundo, Historico, data_min, 54);
litTable.Text += GetMedia(fundo, Historico, data_min, 60);
litTable.Text += "</tr>";
}
litTable.Text += "</tbody>";
}
}
public static void Sync(ref int erros)
{
IWebDriver driver = null;
var Tabs = new List<KeyValuePair<string, string>>();
var Lista = new List<KeyValuePair<string, string>>();
Tabs.Add(new KeyValuePair<string, string>("Renda Fixa", "//*[@id=\"categoryTabs\"]/li[1]/a"));
Tabs.Add(new KeyValuePair<string, string>("Renda Variável", "//*[@id=\"categoryTabs\"]/li[2]/a"));
Tabs.Add(new KeyValuePair<string, string>("Fundo de Investimento", "//*[@id=\"categoryTabs\"]/li[3]/a"));
foreach (var tab in Tabs)
{
GetURL(ref driver, ref Lista, tab.Key, tab.Value);
}
//Parallel.ForEach(Tabs, (l) =>
//{
// GetURL(ref Lista, l.Key, l.Value);
//});
//foreach (var l in Lista)
//{
// Sync(l.Key, l.Value);
//}
//Parallel.ForEach(Lista, new ParallelOptions { MaxDegreeOfParallelism = 5 }, (l) =>
//{
// Sync(l.Key, l.Value);
//});
var Blacklist = new List<string>();
//Blacklist.Add("sorocred-financeira-ourinvest-116-cdi-lc-365-dias-1000-minimo");
//Blacklist.Add("sorocred-financeira-daycoval-116-cdi-lc-361-dias-10000-minimo");
try
{
using (var context = new MoneyEntities())
{
foreach (var l in Lista)
{
try
{
string Tipo = l.Key;
string Url = l.Value;
if ((from a in Blacklist where Url.Contains(a) select a).FirstOrDefault() != null) continue;
if ((from a in context.Fundo where a.URL == Url select a).SingleOrDefault() != null) continue;
if (driver == null) UI.StartChrome(ref driver);
driver.Url = Url;
UI.WaitPageLoad(driver);
//UI.Wait(4);
var Nome = driver.FindElement(By.ClassName("pageHeader__titleHolder")).FindElement(By.TagName("h1")).Text;
var Categoria = driver.FindElement(By.ClassName("pageHeader__titleHolder")).FindElement(By.TagName("h2")).Text;
Nome = Nome.Replace("Fundo De Investimento Em Cotas De Fundos De Investimento Em Ações", "");
Nome = Nome.Replace("Fundo De Investimento Em Cotas De Fundos De Investimento De Ações", "");
Nome = Nome.Replace("Fundo De Investimento Em Ações", "");
Nome = Nome.Replace("Fundo De Investimento Em Cotas De Fundos De Investimento Multimercado", "");
Nome = Nome.Replace("Fundo De Investimento Em Ações", "");
Nome = Nome.Replace("Fundo De Investimento Em Cotas De Fundos De Investimento Multimercado Access", "");
Nome = Nome.Replace("Fundo De Investimento Em Quotas De Fundos De Investimento De Ações", "");
Nome = Nome.Replace("Fundo De Investimento De Ações", "");
Nome = Nome.Replace("Fiq De Fundos De Investimento De Ações", "");
Nome = Nome.Replace("Fundo De Investimento Em Quotas De Fundos De Investimento Multimercado", "");
Nome = Nome.Replace("Fundo De Investimento Multimercado", "");
Nome = Nome.Trim();
Categoria = Categoria.Replace("Certificado de Depósito Bancário - CDB", "CDB");
Categoria = Categoria.Replace("Letra de Crédito Imobiliário - LCI", "LCI");
Categoria = Categoria.Replace("Letra de Câmbio - LC", "LC");
Categoria = Categoria.Trim();
var Fundo = (from a in context.Fundo where a.Nome == Nome && a.Tipo == Tipo && a.Categoria == Categoria select a).SingleOrDefault();
if (Fundo == null)
{
Fundo = new Database.Fundo();
Fundo.Nome = Nome;
Fundo.Tipo = Tipo;
Fundo.Categoria = Categoria;
context.Fundo.Add(Fundo);
context.SaveChanges();
}
if (UI.ElementExist(driver, By.ClassName("tableDetails__grossAnnualYield")))
Fundo.RentabilidadeBruta1Ano = Convert.ToDecimal(driver.FindElement(By.ClassName("tableDetails__grossAnnualYield")).Text.Replace("%", "").Replace("ao ano", ""));
else if (UI.ElementExist(driver, By.ClassName("tableDetails__grossHistoricYield")))
Fundo.RentabilidadeBruta1Ano = Convert.ToDecimal(driver.FindElement(By.ClassName("tableDetails__grossHistoricYield")).Text.Replace("%", "").Replace("ao ano", ""));
if (UI.ElementExist(driver, By.ClassName("tableDetails__netAnnualYield")))
Fundo.RentabilidadeLiquida1Ano = Convert.ToDecimal(driver.FindElement(By.ClassName("tableDetails__netAnnualYield")).Text.Replace("%", "").Replace("ao ano", ""));
else if (UI.ElementExist(driver, By.ClassName("tableDetails__netHistoricYield")))
Fundo.RentabilidadeLiquida1Ano = Convert.ToDecimal(driver.FindElement(By.ClassName("tableDetails__netHistoricYield")).Text.Replace("%", "").Replace("ao ano", ""));
Fundo.IR = Convert.ToDecimal(driver.FindElement(By.ClassName("tableDetails__incomeTaxRate")).Text.Replace("%", "").Replace("ao ano", ""));
Fundo.InvestimentoMinimo = Convert.ToDecimal(driver.FindElement(By.XPath("//*[contains(text(),'Investimento Mínimo')]")).FindElement(By.XPath("..")).FindElement(By.XPath("..")).FindElement(By.TagName("td")).Text.Replace("R$", ""));
Fundo.Liquidez = driver.FindElement(By.XPath("//*[contains(text(),'Liquidez')]")).FindElement(By.XPath("..")).FindElement(By.XPath("..")).FindElement(By.TagName("td")).Text;
Fundo.Distribuidor = driver.FindElement(By.XPath("//*[contains(text(),'Distribuidor')]")).FindElement(By.XPath("..")).FindElement(By.XPath("..")).FindElement(By.TagName("td")).Text;
if (UI.ElementExist(driver, By.ClassName("tableDetails__administrationTaxRate")))
Fundo.TaxaAdmAno = Convert.ToDecimal(driver.FindElement(By.ClassName("tableDetails__administrationTaxRate")).Text.Replace("%", "").Replace("ao ano", ""));
if (UI.ElementExist(driver, By.XPath("//*[contains(text(),'Emissor')]")))
Fundo.Emissor = driver.FindElement(By.XPath("//*[contains(text(),'Emissor')]")).FindElement(By.XPath("..")).FindElement(By.XPath("..")).FindElement(By.TagName("td")).Text;
if (UI.ElementExist(driver, By.XPath("//*[contains(text(),'Gestor')]")))
Fundo.Gestor = driver.FindElement(By.XPath("//*[contains(text(),'Gestor')]")).FindElement(By.XPath("..")).FindElement(By.XPath("..")).FindElement(By.TagName("td")).Text;
if (UI.ElementExist(driver, By.XPath("//*[contains(text(),'Administrador')]")))
Fundo.Administrador = driver.FindElement(By.XPath("//*[contains(text(),'Administrador')]")).FindElement(By.XPath("..")).FindElement(By.XPath("..")).FindElement(By.TagName("td")).Text;
if (UI.ElementExist(driver, By.XPath("//*[contains(text(),'Corretora')]")))
{
Fundo.Corretora = driver.FindElement(By.XPath("//*[contains(text(),'Corretora')]")).FindElement(By.XPath("..")).FindElement(By.XPath("..")).FindElement(By.TagName("td")).Text;
if (Fundo.Corretora.Contains("(")) Fundo.Corretora = Fundo.Corretora.Substring(0, Fundo.Corretora.IndexOf("(")).Trim();
}
if (UI.ElementExist(driver, By.ClassName("tableDetails__custodyTaxRate")))
Fundo.TaxaCustodiaAno = Convert.ToDecimal(driver.FindElement(By.ClassName("tableDetails__custodyTaxRate")).Text.Replace("%", "").Replace("ao ano", ""));
Fundo.URL = Url;
context.SaveChanges();
// Histórico
context.Database.ExecuteSqlCommand("DELETE FROM Historico WHERE FundoID = " + Fundo.FundoID.ToString());
decimal RendimentoMensal = Convert.ToDecimal(Fundo.RentabilidadeBruta1Ano) / 12;
for (int i = 1; i <= 24; i++)
{
var Historico = new Database.Historico();
Historico.Fundo = Fundo;
Historico.Data = new DateTime(DateTime.Today.AddMonths(-i).Year, DateTime.Today.AddMonths(-i).Month, 1);
Historico.Rendimento = RendimentoMensal;
context.Historico.Add(Historico);
}
context.SaveChanges();
}
catch (Exception ex)
{
if (driver != null)
{
try { driver.Close(); } catch { }
driver.Quit();
driver.Dispose();
driver = null;
}
erros += 1;
}
finally
{
//if (driver != null)
//{
// try
// {
// driver.Close();
// }
// catch (Exception ex2)
// {
// var aa = false;
// }
// driver.Quit();
// driver.Dispose();
// driver = null;
//}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (driver != null)
{
try { driver.Close(); } catch { }
driver.Quit();
driver.Dispose();
driver = null;
}
}
}
private static void Sync(string Nome, string Url)
{
IWebDriver driver = null;
try
{
UI.StartChrome(ref driver);
driver.Url = Url;
UI.WaitPageLoad(driver);
// Clica no combo de período, troca de "Diário" para "Mensal"
var combo = driver.FindElement(By.XPath("//*[@id=\"data_interval\"]"));
var options = new SelectElement(combo);
options.SelectByText("Mensal");
UI.Wait(3);
// Lê a tabela
IList <IWebElement> trs = driver.FindElements(By.XPath("//*[@id=\"curr_table\"]/tbody/tr"));
var lstHistorico = new List <Historico>();
using (var context = new MoneyEntities())
{
var Fundo = (from a in context.Fundo where a.Categoria == "ETF" && a.Nome == Nome select a).SingleOrDefault();
if (Fundo == null)
{
Fundo = new Database.Fundo();
Fundo.Categoria = "ETF";
Fundo.Nome = Nome;
Fundo.Tipo = "Renda Variável";
Fundo.URL = Url;
context.Fundo.Add(Fundo);
context.SaveChanges();
}
else
{
context.Database.ExecuteSqlCommand("DELETE FROM Historico WHERE FundoID = " + Fundo.FundoID.ToString());
}
foreach (var tr in trs)
{
var tds = tr.FindElements(By.TagName("td"));
var Historico = new Historico();
Historico.Fundo = Fundo;
Historico.Data = DateTime.ParseExact(tds[0].Text, "MMM yy", System.Globalization.CultureInfo.CurrentCulture);
Historico.Rendimento = Convert.ToDecimal(tds[6].Text.Replace("%", ""));
lstHistorico.Add(Historico);
}
context.Historico.AddRange(lstHistorico);
context.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (driver != null)
{
try { driver.Close(); } catch { }
driver.Quit();
driver.Dispose();
driver = null;
}
}
}