public void AddorUpdateIPO(int i, string dbfile) { IPODetails ipo = finalIPO[i]; using (SQLiteConnection conn = new SQLiteConnection("data source=" + dbfile)) { conn.Open(); using (SQLiteCommand cmd = new SQLiteCommand(conn)) { string selectQuery = "SELECT COUNT(*) FROM IPO WHERE Name = '" + ipo.Name + "'"; cmd.CommandText = selectQuery; int count = Convert.ToInt32(cmd.ExecuteScalar()); string query = ""; if (count > 0) { query = "UPDATE IPO SET Start='" + ipo.Start.ToShortDateString() + "',End='" + ipo.End.ToShortDateString() + "',Count=" + ipo.Count + ",Rating=" + ipo.Rating + ",Review='" + ipo.Review + "',Url='" + ipo.Url + ",Subscription='" + ipo.Subscription + "',UpdatedDate='" + GetTime().ToShortDateString() + "' WHERE Name='" + ipo.Name + "'"; } else { query = "INSERT INTO IPO (Name,Type,Start,End,Count,Rating,Review,Url,Subscription,UpdatedDate) VALUES ('" + ipo.Name + "','" + ipo.Type + "','" + ipo.Start + "','" + ipo.End + "'," + ipo.Count + "," + ipo.Rating + ",'" + ipo.Review + "','" + ipo.Url + "','" + ipo.Subscription + "','" + GetTime().ToShortDateString() + "')"; newIPO.Add(ipo); } cmd.CommandText = query; cmd.ExecuteNonQuery(); } conn.Clone(); } }
public bool IsAlreadyNotified(IPODetails ipo, string dbfile) { bool result = false; using (SQLiteConnection conn = new SQLiteConnection("data source=" + dbfile)) { conn.Open(); string temp = ""; using (SQLiteCommand cmd = new SQLiteCommand(conn)) { cmd.CommandText = "SELECT Date FROM IPONotification WHERE Name = '" + ipo.Name + "'"; using (SQLiteDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { temp = rdr.GetString(0); } } if (temp != "") { DateTime lastnotified = Convert.ToDateTime(temp); if (GetTime() >= lastnotified.AddHours(12)) { cmd.CommandText = "UPDATE IPONotification SET Date='" + GetTime() + "'"; cmd.ExecuteNonQuery(); } else { result = true; } } else { cmd.CommandText = "INSERT INTO IPONotification (Name,Date) VALUES ('" + ipo.Name + "','" + GetTime() + "')"; cmd.ExecuteNonQuery(); } } conn.Clone(); } return(result); }
public List <IPODetails> GetIPOsByUrl(string url, string type) { HtmlWeb web = new HtmlWeb(); HtmlDocument doc = web.Load(url); List <IPODetails> result = new List <IPODetails>(); var tablelist = doc.DocumentNode.SelectNodes("//table").ToList(); foreach (var table in tablelist) { if (table.InnerText.Contains("Upcoming IPO")) { var trlist = table.SelectNodes(".//tr").ToList(); foreach (var tr in trlist) { if (!tr.HasClass("active")) { var tdlist = tr.SelectNodes(".//td").ToList(); if (tdlist.Count > 2) { IPODetails ipo = new IPODetails(); var namenode = tdlist[0].SelectSingleNode(".//a"); ipo.Name = namenode.Attributes["title"].Value; ipo.Url = namenode.Attributes["href"].Value; if (tdlist[2].InnerText != "" && tdlist[3].InnerText != "") { ipo.Start = DateTime.ParseExact(tdlist[2].InnerText, "MMM d, yyyy", null); ipo.End = DateTime.ParseExact(tdlist[3].InnerText, "MMM d, yyyy", null); ipo.End = ipo.End.AddHours(15); ipo.Type = type; if (ipo.End > GetTime()) { result.Add(ipo); } } } } } } } return(result); }
public List <IPODetails> FetchEndingIPO(string dbfile) { List <IPODetails> result = new List <IPODetails>(); using (SQLiteConnection conn = new SQLiteConnection("data source=" + dbfile)) { conn.Open(); using (SQLiteCommand cmd = new SQLiteCommand(conn)) { cmd.CommandText = "SELECT * FROM IPO WHERE Start <= '" + GetTime() + "' AND END >= '" + GetTime() + "'"; using (SQLiteDataReader rdr = cmd.ExecuteReader()) { IPODetails temp = new IPODetails(); while (rdr.Read()) { temp.Name = rdr.GetString(1); temp.Type = rdr.GetString(2); temp.Start = Convert.ToDateTime(rdr.GetString(3)); temp.End = Convert.ToDateTime(rdr.GetString(4)); temp.Count = rdr.GetInt32(5); temp.Rating = rdr.GetDecimal(6); temp.Review = rdr.GetString(7); temp.Url = rdr.GetString(8); temp.Subscription = rdr.GetString(9); result.Add(temp); } } } conn.Clone(); } for (int i = 0; i < result.Count; i++) { if (IsAlreadyNotified(result[i], dbfile)) { result.RemoveAt(i); i--; } } return(result); }