//This uses a dictionary of currency crosses from rawData to go from any currency XXX to YYY. It makes sure that this can be done with a limited currencyRawDataInput in the form USDZZZ.
public static void IntradaytInsert(List <Instrument> instruments)
{
StringBuilder allItems = new StringBuilder();
foreach (Instrument item in instruments)
{
allItems.AppendFormat("{0},", item.ID);
}
allItems.Remove(allItems.Length - 1, 1);
// We will need this one later
rawData.allItems = allItems.ToString();
string strSQL = string.Format(@"SELECT TOP {0} InstrumentID,closePrice FROM [EuroInvestorStockDB].[dbo].[Intraday_Price] where instrumentID in ({1}) order by ID", instruments.Count, allItems);
DataTable reader;
try
{
reader = SQLC.RunSQL(strSQL);
}
catch
{
reader = null;
Console.WriteLine("Failed on select on the DB");
state = Condition.DBerror;
return;
}
foreach (DataRow dr in reader.Rows)
{
try { instruments.Find(x => x.ID == (int)dr["InstrumentID"]).IntraDayPreviousPrice = Convert.ToDouble(dr["closePrice"]); }
catch { instruments.Find(x => x.ID == (int)dr["InstrumentID"]).IntraDayPreviousPrice = instruments.Find(x => x.ID == (int)dr["InstrumentID"]).lastPrice; }
}
StringBuilder sb = new StringBuilder();
sb.Append("Insert into [EuroInvestorStockDB].[dbo].[Intraday_Price] ");
sb.Append("(InstrumentID, OpenTimeStamp, CloseTimeStamp, OpenPrice, ClosePrice, High, Volume, Low, VWAP, PriceType, totalVol) values");
foreach (Instrument item in instruments)
{
sb.AppendFormat("({0} ,'{1}' , '{2}',{3} , {4} , {4} , 0 , {4}, 0, null, 0 ), ", item.ID, DateTime.Now, DateTime.Now.AddMinutes(-5), item.IntraDayPreviousPrice, item.lastPrice);
}
sb.Remove(sb.Length - 2, 1);
try
{
reader = SQLC.RunSQL(sb.ToString());
}
catch
{
Console.WriteLine("Failed on select on the DB");
state = Condition.DBerror;
return;
}
// If the day changed write to Intraday
}
public static void ClosePriceInsert(List <Instrument> instruments)
{
string strSQL = string.Format(@"SELECT TOP {0} InstrumentID,closePrice FROM [EuroInvestorStockDB].[dbo].[Close_Price] where instrumentID in ({1}) order by ID", instruments.Count, rawData.allItems);
DataTable reader;
try { reader = SQLC.RunSQL(strSQL); }
catch
{
Console.WriteLine("Failed on select on the DB");
state = Condition.DBerror;
return;
}
foreach (DataRow dr in reader.Rows)
{
try { instruments.Find(x => x.ID == (int)dr["InstrumentID"]).ClosePricePreviousPrice = Convert.ToDouble(dr["closePrice"]); }
catch { instruments.Find(x => x.ID == (int)dr["InstrumentID"]).ClosePricePreviousPrice = instruments.Find(x => x.ID == (int)dr["InstrumentID"]).lastPrice; }
}
StringBuilder stb = new StringBuilder();
stb.Append("Insert into [EuroInvestorStockDB].[dbo].[Intraday_Price] ");
stb.Append("(Date, ExchangeID, InstrumentID, OpenPrice, High, Low, ClosePrice, TotVol, VWAP, OpenInterest, TradeCoount, PriceType, CloseType ) values");
foreach (Instrument item in instruments)
{
if (state == Condition.WorkingC)
{
stb.AppendFormat("({0} ,164 , {1} ,{2} , {3} , {3},{3},0,NULL,NULL,0,0,0 ), ", DateTime.Now.Date, item.ClosePricePreviousPrice, item.ID, item.lastPrice);
}
else
{
stb.AppendFormat("({0} ,87 , {1} ,{2} , {3} , {3},{3},0,NULL,NULL,0,0,0 ), ", DateTime.Now.Date, item.ClosePricePreviousPrice, item.ID, item.lastPrice);
}
}
try { reader = SQLC.RunSQL(stb.ToString()); }
catch
{
Console.WriteLine("Failed on select on the DB");
state = Condition.DBerror;
return;
}
}
public static void PopulateCurrencies()
{
try
{
string strSQL = string.Format(@"SELECT TOP 1000 Symbol,Id FROM[EuroInvestorStockDB].[dbo].[Instrument] with (nolock) where exchangeid = 164");
using (DataTable reader = SQLC.RunSQL(strSQL))
{
foreach (DataRow dr in reader.Rows)
{
// insert instrument ID to the DB
Program.CurrencyList.Add(new Instrument(dr["Symbol"].ToString(), (int)dr["id"]));
}
}
}
catch
{
Console.WriteLine("Failed on select on the DB");
state = Condition.DBerror;
return;
}
}
public static void InstrumentInsert(List <Instrument> instruments)
{
if (state == Condition.WorkingC)
{
foreach (Instrument item in instruments)
{
double ratio = Math.Round(rawData.convertCurrency(item.symbol.Substring(0, 3), item.symbol.Substring(3, 3)), 4);
item.lastPrice = ratio;
// item.close = ratio; item.high = ratio; item.low = ratio; item.netchange = 0; item.percentChange = 0;
item.tradeTimestamp = DateTime.Now; item.serverTimeStamp = DateTime.Now;
}
}
// write the data
StringBuilder sb = new StringBuilder();
foreach (Instrument item in instruments)
{
sb.Append("UPDATE [EuroInvestorStockDB].[dbo].[Instrument] ");
if (state == Condition.WorkingC)
{
sb.AppendFormat("SET Bid = {0}, Ask ={0}, Mid = {0}, High= {0}, Low = {0}, Last = {0}, PercentageChange= 0, OpenPrice = {0}, Change=0, TimeStamp='{1}'", item.lastPrice, item.serverTimeStamp.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
sb.AppendFormat("SET Bid = {0}, Ask ={0}, Mid = {1}, High= {2}, Low = {3}, Last = {0}, PercentageChange= {5}, OpenPrice = {6}, Change={7}, TimeStamp='{8}'",
item.lastPrice, item.lastPrice, Math.Round(0.5 * (item.high + item.low), 4), item.high, item.low, item.percentChange, item.open, item.netchange, item.serverTimeStamp.ToString("yyyy-MM-dd HH:mm:ss"));
}
sb.AppendFormat(" WHERE id = {0}", item.ID);
}
try
{
DataTable notused = SQLC.RunSQL(sb.ToString());
}
catch
{
Console.WriteLine("Failed on select on the DB");
state = Condition.DBerror;
return;
}
}