public IEnumerable <ISecurityStaticDataModel> GetAllExistingStaticData()
{
_logger.Information($"PersistenceManager: retrieving All existing security static data.");
string commandString = "select ed.Name ExchangeName, sd.Name SecurityName, sd.Symbol, sd.ExchangeId from SecurityDetails sd\n" +
"join ExchangeDetails ed on ed.Id = sd.ExchangeId";
var securityStaticDataCollection = new List <ISecurityStaticDataModel>();
using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(commandString, _connectionWrapper))
{
lock (_lockObject)
{
using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader())
{
while (dataReader.ReadNext())
{
string exchangeName = dataReader.GetField <string>("ExchangeName");
var exchangeData = new ExchangeStaticDataModel(exchangeName);
string securityName = dataReader.GetField <string>("SecurityName");
string symbol = dataReader.GetField <string>("Symbol");
var securityData = new SecurityStaticDataModel(exchangeData, symbol, securityName);
securityStaticDataCollection.Add(securityData);
}
}
}
}
return(securityStaticDataCollection);
}
public DateTime GetLatestStockDateStamp(string symbol)
{
_logger.Information($"PersistenceManager: retrieving latest stock data timestamp for symbol: {symbol}");
int?securityId = GetSecurityIdBySymbol(symbol);
if (!securityId.HasValue)
{
throw new ArgumentException($"Security not found for symbol: {symbol}");
}
string commandString = "SELECT DateStamp FROM EndOfDayData WHERE SecurityId = @SecurityId ORDER BY DateStamp DESC LIMIT 1;";
using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(commandString, _connectionWrapper))
{
commandWrapper.AddParameter("@SecurityId", securityId.Value);
lock (_lockObject)
{
using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader())
{
if (!dataReader.HasRows || !dataReader.ReadNext())
{
return(new DateTime(1950, 01, 01));
}
return(dataReader.GetField <DateTime>("DateStamp"));
}
}
}
}
private int?GetSecurityIdBySymbol(string symbol)
{
string commandString = "SELECT Id FROM SecurityDetails WHERE Symbol = @Symbol;";
using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(commandString, _connectionWrapper))
{
commandWrapper.AddParameter("@Symbol", symbol);
lock (_lockObject)
{
using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader())
{
if (!dataReader.HasRows || !dataReader.ReadNext())
{
return(null);
}
return(dataReader.GetField <int?>("Id"));
}
}
}
}
private int?GetExchangeIdByName(string name)
{
string commandString = "SELECT Id FROM ExchangeDetails WHERE Name = @ExchangeName;";
using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(commandString, _connectionWrapper))
{
commandWrapper.AddParameter("@ExchangeName", name);
lock (_lockObject)
{
using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader())
{
if (!dataReader.HasRows || !dataReader.ReadNext())
{
return(null);
}
return(dataReader.GetField <int?>("Id"));
}
}
}
}
public IStockDataModel GetStockData(IMySqlDataReaderWrapper dataReader)
{
string symbol = dataReader.GetField <string>("symbol");
DateTime dateStamp = dataReader.GetField <DateTime>("DateStamp");
decimal open = (decimal)dataReader.GetField <Single>("Open");
decimal high = (decimal)dataReader.GetField <Single>("High");
decimal low = (decimal)dataReader.GetField <Single>("Low");
decimal close = (decimal)dataReader.GetField <Single>("Close");
decimal adjustedClose = (decimal)dataReader.GetField <Single>("AdjustedClose");
long volume = dataReader.GetField <long>("Volume");
decimal dayChange = (decimal)dataReader.GetField <Single>("DayChange");
decimal dayPercentageChange = (decimal)dataReader.GetField <Single>("DayPercentageChange");
decimal standardChange = (decimal)dataReader.GetField <Single>("StandardChange");
decimal standardPercentageChange = (decimal)dataReader.GetField <Single>("StandardPercentageChange");
var stockData = new StockDataModel(symbol, dateStamp, open, high, low, close, volume, adjustedClose, dayChange, dayPercentageChange, standardChange, standardPercentageChange);
return(stockData);
}
