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