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"));
}
}
}
}
public IEnumerable <IStockDataModel> GetSecurityData(IEnumerable <string> symbolCollection, DateTime startDateStamp, DateTime endDateStamp)
{
StringBuilder stringBuilder = new StringBuilder("WHERE sd.symbol IN (");
foreach (string symbol in symbolCollection)
{
stringBuilder.Append($"'{symbol}',");
}
stringBuilder.Length--;
stringBuilder.Append(")");
string symolFilter = stringBuilder.ToString();
string command = @"select sd.symbol,
eod.Open,
eod.High,
eod.Low,
eod.Close,
eod.AdjustedClose,
eod.Volume,
eod.DayChange,
eod.DayPercentageChange,
eod.StandardChange,
eod.StandardPercentageChange,
eod.DateStamp from endofdaydata eod
join securitydetails sd on sd.Id = eod.SecurityId
"
+ symolFilter + Environment.NewLine +
$"and eod.DateStamp >= '{startDateStamp.ToString("yyyy-MM-dd")}' and eod.DateStamp <= '{endDateStamp.ToString("yyyy-MM-dd")}'" + Environment.NewLine +
"order by eod.DateStamp ASC;";
var stockDataCollection = new List <IStockDataModel>();
using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(command, _connectionWrapper))
{
lock (_lockObject)
{
using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader())
{
if (!dataReader.HasRows)
{
return(stockDataCollection);
}
while (dataReader.ReadNext())
{
IStockDataModel stockData = _securityDataSqlFactory.GetStockData(dataReader);
stockDataCollection.Add(stockData);
}
}
}
}
return(stockDataCollection);
}
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);
}
public bool DoesSecurityStaticDataExist(ISecurityStaticDataModel securityStaticData)
{
_logger.Information($"PersistenceManager: Checking for the existence of security data: {securityStaticData.Symbol}");
string commandString = "SELECT * FROM SecurityDetails WHERE Symbol = @Symbol;";
using (IMySqlCommandWrapper commandWrapper = _commandWrapperFactory.GetCommandWrapper(commandString, _connectionWrapper))
{
commandWrapper.AddParameter("@Symbol", securityStaticData.Symbol);
lock (_lockObject)
{
using (IMySqlDataReaderWrapper dataReader = commandWrapper.ExecuteReader())
{
string existenceLog = dataReader.HasRows ? "exists" : "does not exist";
_logger.Information($"PersistenceManager: Security static data {securityStaticData.Symbol} {existenceLog}");
return(dataReader.HasRows);
}
}
}
}
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"));
}
}
}
}
