public static void ScrapeToDatabase(List <List <string> > stockData)
{
string method = "HAP";
using (HapStocksContext db = new HapStocksContext())
{
for (int i = 0; i < stockData.Count; i++)
{
StockTable stockTable = new StockTable
{
DateStamp = DateTime.Now,
Symbol = stockData[i][0].ToString(),
LastPrice = stockData[i][1].ToString(),
Change = stockData[i][2].ToString(),
ChgPc = stockData[i][3].ToString(),
MarketTime = stockData[i][5].ToString(),
Volume = stockData[i][6].ToString(),
AvgVol3m = stockData[i][8].ToString(),
MarketCap = stockData[i][12].ToString(),
Method = method,
};
db.StockTables.Add(stockTable);
db.SaveChanges();
}
}
}
public ActionResult DeleteConfirmed(int id)
{
StockTable stockTable = db.StockTables.Find(id);
db.StockTables.Remove(stockTable);
db.SaveChanges();
return(RedirectToAction("Index"));
}
public async Task <ActionResult> DeleteConfirmed(int id)
{
StockTable stockTable = await db.StockTables.FindAsync(id);
db.StockTables.Remove(stockTable);
await db.SaveChangesAsync();
return(RedirectToAction("Index"));
}
public ActionResult Edit([Bind(Include = "Id,Time_Scraped,Stock_Symbol,Last_Price,Change,Change_Percent,Volume,Shares,Average_Volume,Market_Cap")] StockTable stockTable)
{
if (ModelState.IsValid)
{
db.Entry(stockTable).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(stockTable));
}
public ActionResult Edit([Bind(Include = "Id,StockRecord,Symbol,LastPrice,Change,PChg,Currency,MarketTime,VolumeAvg")] StockTable stockTable)
{
if (ModelState.IsValid)
{
db.Entry(stockTable).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(stockTable));
}
public async Task <ActionResult> Create([Bind(Include = "Id,Time_Scraped,Stock_Symbol,Last_Price,Change,Change_Percent,Volume,Shares,Average_Volume,Market_Cap")] StockTable stockTable)
{
if (ModelState.IsValid)
{
db.StockTables.Add(stockTable);
await db.SaveChangesAsync();
return(RedirectToAction("Index"));
}
return(View(stockTable));
}
public override void WillActivate()
{
// This method is called when the watch view controller is about to be visible to the user.
Console.WriteLine("{0} will activate", this);
StockTable.SetNumberOfRows((nint)data.Count, "stockRow");
for (var i = 0; i < data.Count; i++)
{
var elementRow = (StockRowController)StockTable.GetRowController(i);
elementRow.Set(data[i].Symbol);
}
}
// GET: StockTables/Delete/5
public async Task <ActionResult> Delete(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
StockTable stockTable = await db.StockTables.FindAsync(id);
if (stockTable == null)
{
return(HttpNotFound());
}
return(View(stockTable));
}
// GET: StockTables/Delete/5
public ActionResult Delete(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
StockTable stockTable = db.StockTables.Find(id);
if (stockTable == null)
{
return(HttpNotFound());
}
return(View(stockTable));
}
public async Task ReadAndSaveFile(string path)
{
if (File.Exists(path))
{
using (StreamReader sr = new StreamReader(path))
{
_context.ChangeTracker.AutoDetectChangesEnabled = false;
Console.WriteLine("Eliminando Registros Previos");
await _context.Database.ExecuteSqlRawAsync("DELETE FROM StockTable");
string line;
int count = -1;
int savedLines = 0;
Console.WriteLine("Iniciando Lectura!!");
while ((line = sr.ReadLine()) != null)
{
count++;
if (count == 0)
{
continue;
}
StockTable stock = FormatData(line);
if (stock != null)
{
await _context.StockTable.AddAsync(stock);
await _context.SaveChangesAsync();
savedLines++;
Console.Write(".");
}
}
Console.WriteLine("Numero de registros leidos: {0}", count);
Console.WriteLine("Numero de registros ingresados en la base de datos: {0}", savedLines);
}
}
else
{
Console.WriteLine("Ha ocurrido un error, no se puede encontrar el archivo descargado");
}
}
public Task AddStockAsync(CountryKind country, string stockId, string stockName, string stockExchangeName, string stockDescription, string stockExchangeID)
{
IEnumerable <IStock> stocks = StockTable.Where(a => a.Country == country &&
string.Equals(a.StockId, stockId, StringComparison.OrdinalIgnoreCase));
if (stocks.Any())
{
return(Task.CompletedTask);
}
Stock stock = new Stock(country, stockId, stockName, stockExchangeID)
{
StockExchangeName = stockExchangeName,
Description = stockDescription
};
StockTable.Add(stock);
return(Task.CompletedTask);
}
public void DataInsertion()
{
ChromeOptions options = new ChromeOptions();
options.AddArgument("--headless");
options.AddArgument("--disable-gpu");
using (IWebDriver driver = new ChromeDriver(options))
{
driver.Navigate().GoToUrl("http://yahoo.com/");
WebDriverWait LogIn = new WebDriverWait(driver, TimeSpan.FromSeconds(10));
LogIn.Until(ExpectedConditions.ElementToBeClickable(By.Id("uh-signin")));
IWebElement signIn = driver.FindElement(By.Id("uh-signin"));
signIn.Click();
IWebElement username = driver.FindElement(By.Id("login-username"));
username.SendKeys("gonzalez.soriano");
username.Submit();
WebDriverWait passwdDuration = new WebDriverWait(driver, TimeSpan.FromSeconds(10));
passwdDuration.Until(ExpectedConditions.ElementToBeClickable(By.Id("login-passwd")));
IWebElement password = driver.FindElement(By.Id("login-passwd"));
password.SendKeys("Hector3463");
password.SendKeys(Keys.Enter);
driver.Navigate().GoToUrl("https://finance.yahoo.com/portfolio/p_0/view");
WebDriverWait tbodyDuration = new WebDriverWait(driver, TimeSpan.FromSeconds(2));
tbodyDuration.Until(d => driver.FindElement(By.TagName("tbody")));
int counter;
IWebElement tableBody = driver.FindElement(By.TagName("tbody"));
System.Collections.ObjectModel.ReadOnlyCollection <IWebElement> stockList = tableBody.FindElements(By.TagName("tr"));
counter = stockList.Count;
List <StockTable> ListOfStocks = new List <StockTable>();
for (int stocks = 1; stocks <= counter; stocks++)
{
DateTime stockRecord = DateTime.Now;
string symbol = driver.FindElement(By.XPath("//*[@id=\"pf-detail-table\"]/div[1]/table/tbody/tr[" + stocks + "]/td[1]/a")).GetAttribute("innerText");
string lastPrice = driver.FindElement(By.XPath("//*[@id=\"pf-detail-table\"]/div[1]/table/tbody/tr[" + stocks + "]/td[2]/span")).GetAttribute("innerText");
string change = driver.FindElement(By.XPath("//*[@id=\"pf-detail-table\"]/div[1]/table/tbody/tr[" + stocks + "]/td[3]/span")).GetAttribute("innerText");
string pchg = driver.FindElement(By.XPath("//*[@id=\"pf-detail-table\"]/div[1]/table/tbody/tr[" + stocks + "]/td[4]/span")).GetAttribute("innerText");
string currency = driver.FindElement(By.XPath("//*[@id=\"pf-detail-table\"]/div[1]/table/tbody/tr[" + stocks + "]/td[5]")).GetAttribute("innerText");
string marketTime = driver.FindElement(By.XPath("//*[@id=\"pf-detail-table\"]/div[1]/table/tbody/tr[" + stocks + "]/td[6]/span")).GetAttribute("innerText");
string volumeAvg = driver.FindElement(By.XPath("//*[@id=\"pf-detail-table\"]/div[1]/table/tbody/tr[" + stocks + "]/td[9]")).GetAttribute("innerText");
StockTable newStocks = new StockTable();
newStocks.StockRecord = stockRecord;
newStocks.Symbol = symbol;
newStocks.LastPrice = lastPrice;
newStocks.Change = change;
newStocks.PChg = pchg;
newStocks.Currency = currency;
newStocks.MarketTime = marketTime;
newStocks.VolumeAvg = volumeAvg;
ListOfStocks.Add(newStocks);
}
driver.Quit();
SqlConnection db = new SqlConnection(connectionString);
db.Open();
Console.WriteLine();
Console.WriteLine("Database has been opened");
foreach (StockTable stock in ListOfStocks)
{
SqlCommand insert = new SqlCommand("INSERT INTO dbo.StockTable ( StockRecord, Symbol, LastPrice, Change, PChg, Currency, MarketTime, VolumeAvg ) VALUES ( @stockRecord, @symbol, @lastPrice, @change, @pchg, @currency, @marketTime, @volumeAvg )", db);
insert.Parameters.AddWithValue("@stockRecord", stock.StockRecord.ToString());
insert.Parameters.AddWithValue("symbol", stock.Symbol);
insert.Parameters.AddWithValue("lastPrice", stock.LastPrice);
insert.Parameters.AddWithValue("change", stock.Change);
insert.Parameters.AddWithValue("pchg", stock.PChg);
insert.Parameters.AddWithValue("currency", stock.Currency);
insert.Parameters.AddWithValue("marketTime", stock.MarketTime);
insert.Parameters.AddWithValue("volumeAvg", stock.VolumeAvg);
insert.ExecuteNonQuery();
}
db.Close();
Console.WriteLine();
Console.WriteLine("Database has been updated with info");
Console.WriteLine();
Console.WriteLine("Database has been closed!");
}
}
public static IFeatureClass CreateFeatureClass(IFeatureWorkspace featureWorkspace, esriGeometryType esriGeometryType, ISpatialReference spatialReference, StockTable stockTable)
{
IFields fields = new FieldsClass();
IFieldsEdit fieldsedit = fields as IFieldsEdit;
IField field = new FieldClass();
IFieldEdit fieldedit = field as IFieldEdit;
fieldedit.Name_2 = "shape";
fieldedit.Type_2 = esriFieldType.esriFieldTypeGeometry;
IGeometryDef geometryDef = new GeometryDefClass();
IGeometryDefEdit pGeometryDefEdit = geometryDef as IGeometryDefEdit;
pGeometryDefEdit.GeometryType_2 = esriGeometryType;
pGeometryDefEdit.SpatialReference_2 = spatialReference;
fieldedit.GeometryDef_2 = geometryDef;
fieldsedit.AddField(field);
field = new FieldClass();
fieldedit = field as IFieldEdit;
fieldedit.Name_2 = "OBJECTID";
fieldedit.Type_2 = esriFieldType.esriFieldTypeOID;
fieldsedit.AddField(field);
field = new FieldClass();
fieldedit = field as IFieldEdit;
fieldedit.Name_2 = "XZSDM";
fieldedit.AliasName_2 = "行政市代码";
fieldedit.Type_2 = esriFieldType.esriFieldTypeString;
fieldsedit.AddField(field);
field = new FieldClass();
fieldedit = field as IFieldEdit;
fieldedit.Name_2 = "XZSMC";
fieldedit.AliasName_2 = "行政市名称";
fieldedit.Type_2 = esriFieldType.esriFieldTypeString;
fieldsedit.AddField(field);
field = new FieldClass();
fieldedit = field as IFieldEdit;
fieldedit.Name_2 = "XZXDM";
fieldedit.AliasName_2 = "行政县代码";
fieldedit.Type_2 = esriFieldType.esriFieldTypeString;
fieldsedit.AddField(field);
field = new FieldClass();
fieldedit = field as IFieldEdit;
fieldedit.Name_2 = "XZXMC";
fieldedit.AliasName_2 = "行政县名称";
fieldedit.Type_2 = esriFieldType.esriFieldTypeString;
fieldsedit.AddField(field);
foreach (var item in stockTable.Fields)
{
field = new FieldClass();
fieldedit = field as IFieldEdit;
fieldedit.Name_2 = item.Name;
fieldedit.AliasName_2 = item.Title;
switch (item.Type)
{
case Models.FieldType.Char:
fieldedit.Type_2 = esriFieldType.esriFieldTypeString;
break;
case Models.FieldType.Float:
fieldedit.Type_2 = esriFieldType.esriFieldTypeDouble;
break;
case Models.FieldType.Int:
fieldedit.Type_2 = esriFieldType.esriFieldTypeInteger;
break;
}
fieldsedit.AddField(field);
}
IFeatureClassDescription featureClassDescription = new FeatureClassDescriptionClass();
IObjectClassDescription objectClassDescription = featureClassDescription as IObjectClassDescription;
IFeatureClass featureClass = featureWorkspace.CreateFeatureClass(stockTable.Name, fields, objectClassDescription.InstanceCLSID, objectClassDescription.ClassExtensionCLSID, esriFeatureType.esriFTSimple, "shape", "");
return(featureClass);
}
public static ITable CreateTable(IFeatureWorkspace featureWorkspace, StockTable stockTable)
{
IFields fields = new FieldsClass();
IFieldsEdit fieldsedit = fields as IFieldsEdit;
IField field = new FieldClass();
IFieldEdit fieldedit = field as IFieldEdit;
fieldedit.Name_2 = "OBJECTID";
fieldedit.Type_2 = esriFieldType.esriFieldTypeOID;
fieldsedit.AddField(field);
field = new FieldClass();
fieldedit = field as IFieldEdit;
fieldedit.Name_2 = "XZSDM";
fieldedit.AliasName_2 = "行政市代码";
fieldedit.Type_2 = esriFieldType.esriFieldTypeString;
fieldsedit.AddField(field);
field = new FieldClass();
fieldedit = field as IFieldEdit;
fieldedit.Name_2 = "XZSMC";
fieldedit.AliasName_2 = "行政市名称";
fieldedit.Type_2 = esriFieldType.esriFieldTypeString;
fieldsedit.AddField(field);
field = new FieldClass();
fieldedit = field as IFieldEdit;
fieldedit.Name_2 = "XZXDM";
fieldedit.AliasName_2 = "行政县代码";
fieldedit.Type_2 = esriFieldType.esriFieldTypeString;
fieldsedit.AddField(field);
field = new FieldClass();
fieldedit = field as IFieldEdit;
fieldedit.Name_2 = "XZXMC";
fieldedit.AliasName_2 = "行政县名称";
fieldedit.Type_2 = esriFieldType.esriFieldTypeString;
fieldsedit.AddField(field);
foreach (var item in stockTable.Fields)
{
field = new FieldClass();
fieldedit = field as IFieldEdit;
fieldedit.Name_2 = item.Name;
fieldedit.AliasName_2 = item.Title;
switch (item.Type)
{
case Models.FieldType.Char:
fieldedit.Type_2 = esriFieldType.esriFieldTypeString;
break;
case Models.FieldType.Float:
fieldedit.Type_2 = esriFieldType.esriFieldTypeDouble;
break;
case Models.FieldType.Int:
fieldedit.Type_2 = esriFieldType.esriFieldTypeInteger;
break;
}
fieldsedit.AddField(field);
}
UIDClass instanceUID = new UIDClass();
instanceUID.Value = "esriGeoDatabase.Object";
ITable ptable = featureWorkspace.CreateTable(stockTable.Name, fields, instanceUID, null, "");
return(ptable);
}
public Task <IReadOnlyList <IStock> > GetStocksAsync(CountryKind country)
{
return(Task.FromResult <IReadOnlyList <IStock> >(StockTable.Where(a => a.Country == country).ToList()));
}
private static List <StockTable> GetStockTables()
{
var list = new List <StockTable>();
var tables = XmlManager.GetList("/Tables/Table", XmlEnum.Field);
if (tables != null)
{
var length = 0;
for (var i = 0; i < tables.Count; i++)
{
var table = tables[i];
var stockTable = new StockTable
{
Name = table.Attributes["Name"].Value,
Title = table.Attributes["Title"].Value,
IsSpace = table.Attributes["IsSpace"].Value == "true" ? true : false
};
if (table.Attributes["Type"] != null)
{
stockTable.Type = table.Attributes["Type"].Value;
}
var fields = table.SelectNodes("Field");
if (fields != null)
{
var ff = new List <Models.Field>();
for (var j = 0; j < fields.Count; j++)
{
var fi = fields[j];
var str = fi.Attributes["Type"].Value;
if (!string.IsNullOrEmpty(str))
{
var item = new Models.Field()
{
Name = fi.Attributes["Name"].Value,
Title = fi.Attributes["Title"].Value,
Length = int.TryParse(fi.Attributes["Length"].Value, out length) ? length : 0,
};
switch (str.ToLower())
{
case "int":
item.Type = Models.FieldType.Int;
break;
case "float":
item.Type = Models.FieldType.Float;
break;
case "char":
item.Type = Models.FieldType.Char;
break;
}
if (fi.Attributes["Min"] != null && int.TryParse(fi.Attributes["Min"].Value, out length))
{
item.Min = length;
}
ff.Add(item);
}
}
stockTable.Fields = ff;
}
list.Add(stockTable);
}
}
return(list);
}
