public AssetDto GetAssetBySymbol(string symbol)
{
AssetDto company = null;
using (EFDbContext context = new EFDbContext())
{
company = context.Companies.SingleOrDefault(c => c.Symbol.Equals(symbol, System.StringComparison.CurrentCultureIgnoreCase));
}
return company;
}
public AssetDto GetAssetByName(string name)
{
AssetDto company = null;
using (EFDbContext context = new EFDbContext())
{
company = context.Companies.SingleOrDefault(c => c.Name.Equals(name, System.StringComparison.CurrentCultureIgnoreCase));
}
return company;
}
public AssetDto GetAsset(int id)
{
AssetDto company = null;
using (EFDbContext context = new EFDbContext())
{
company = context.Companies.SingleOrDefault(c => c.Id == id);
}
return company;
}
public IEnumerable<AssetDto> GetAllAssets()
{
IEnumerable<AssetDto> results = null;
using (EFDbContext context = new EFDbContext())
{
results = context.Companies.ToList();
}
return results;
}
public IEnumerable<FxPairDto> FilterPairs(string q, int limit)
{
string lower = q.ToLower();
IEnumerable<FxPairDto> results = null;
using (EFDbContext context = new EFDbContext())
{
results = context.Pairs.Where(p => p.IsActive && p.Name.ToLower().Contains(lower)).Take(limit).ToList();
}
return results;
}
public IEnumerable<AssetDto> FilterAssets(string q, int limit)
{
string lower = q.ToLower();
IEnumerable<AssetDto> results = null;
using (EFDbContext context = new EFDbContext())
{
results = context.Companies.Where(c => c.Name.ToLower().Contains(lower) ||
c.Symbol.ToLower().Contains(lower)).Take(limit).ToList();
}
return results;
}
public void AddAnalysisInfo(AnalysisDto analysis)
{
string sqlInsert = "INSERT INTO fx." + AnalysisInfoTable +
"(AnalysisName, Symbol, FirstAnalysedItemDate, LastAnalysedItemDate, AnalysedUnits, " +
"AnalysisStart, AnalysisEnd, AnalysisTotalTime) " +
"VALUES ('" + analysis.Type + "'" +
", '" + analysis.Symbol + "'" +
", '" + analysis.FirstItemDate + "'" +
", '" + analysis.LastItemDate + "'" +
", " + analysis.AnalyzedItems +
", '" + analysis.AnalysisStart + "'" +
", '" + analysis.AnalysisEnd + "'" +
", " + analysis.AnalysisTotalTime.ToDbString() + ");";
using (var context = new EFDbContext())
{
context.Database.ExecuteSqlCommand(sqlInsert);
context.SaveChanges();
}
}
public void AddMacd(MacdDto macd, string symbol)
{
string tableName = MacdTablePrefix + symbol;
string sqlRemove = "DELETE FROM fx." + tableName +
" WHERE PriceDate = '" + macd.PriceDate + "';";
string sqlInsert = "INSERT INTO fx." + tableName +
"(AssetId, PriceDate, MA13, EMA13, MA26, EMA26, MACDLine, SignalLine, Histogram, HistogramAvg, " +
"HistogramExtremum, DeltaHistogram, DeltaHistogramPositive, DeltaHistogramNegative, DeltaHistogramZero, " +
"HistogramDirection2D, HistogramDirection3D, HistogramDirectionChanged, HistogramToOX, " +
"HistogramRow, OxCrossing, MacdPeak, LastMACDPeak, MACDPeakSlope, MACDTrough, LastMACDTrough, MACDTroughSlope) " +
"VALUES (" +
macd.AssetId +
", '" + macd.PriceDate + "'" +
", " + macd.Ma13.ToDbString() +
", " + macd.Ema13.ToDbString() +
", " + macd.Ma26.ToDbString() +
", " + macd.Ema26.ToDbString() +
", " + macd.MacdLine.ToDbString() +
", " + macd.SignalLine.ToDbString() +
", " + macd.Histogram.ToDbString() +
", " + macd.HistogramAvg.ToDbString() +
", " + macd.HistogramExtremum.ToDbString() +
", " + macd.DeltaHistogram.ToDbString() +
", " + macd.DeltaHistogramPositive +
", " + macd.DeltaHistogramNegative +
", " + macd.DeltaHistogramZero +
", " + macd.HistogramDirection2D +
", " + macd.HistogramDirection3D +
", " + macd.HistogramDirectionChanged +
", " + macd.HistogramToOx +
", " + macd.HistogramRow +
", " + macd.OxCrossing.ToDbString() +
", " + macd.MacdPeak +
", " + macd.LastMacdPeak.ToDbString() +
", " + macd.MacdPeakSlope.ToDbString() +
", " + macd.MacdTrough +
", " + macd.LastMacdTrough.ToDbString() +
", " + macd.MacdTroughSlope.ToDbString() +");";
using (var context = new EFDbContext())
{
context.Database.ExecuteSqlCommand(sqlRemove);
context.Database.ExecuteSqlCommand(sqlInsert);
context.SaveChanges();
}
}
public IEnumerable<PriceDto> GetPrices(string symbol)
{
string tableName = PricesTablePrefix + symbol;
string sql = "SELECT * FROM fx.{0} ORDER BY PriceDate DESC";
string query = string.Format(sql, tableName);
IEnumerable<PriceDto> prices;
//Check if table exists. If not, create it.
if (!CheckIfTableExists(tableName))
{
CreateTable(tableName, PricesTableTemplate);
}
using (var context = new EFDbContext())
{
prices = context.Database.SqlQuery<PriceDto>(query).ToList();
}
return prices.OrderBy(p => p.PriceDate);
}
public DateTime? GetLastQuotationDate(string symbol)
{
string sqlQuotation = "SELECT MAX(PriceDate) FROM quotations_" + symbol + " WHERE OpenPrice > -1;";
using (var context = new EFDbContext())
{
foreach (var i in context.Database.SqlQuery<String>(sqlQuotation))
{
return DateTime.Parse(i);
}
}
return null;
}
public IEnumerable<PriceDto> GetPrices(string symbol, DateTime? startDate, DateTime? endDate)
{
string query = GetSqlForFxCalculations(symbol, PricesTablePrefix, startDate, endDate);
IEnumerable<PriceDto> prices;
using (var context = new EFDbContext())
{
prices = context.Database.SqlQuery<PriceDto>(query).ToList();
}
return prices.OrderBy(q => q.PriceDate);
}
public IEnumerable<DataItemDto> GetFxQuotations(string symbol, bool isSimulation)
{
//If this is not for simulation return normal set of quotations for a given symbol.
if (!isSimulation) return GetFxQuotations(symbol);
string query = GetSqlForPlainFxQuotations(symbol);
IEnumerable<DataItemDto> quotations;
using (var context = new EFDbContext())
{
quotations = context.Database.SqlQuery<DataItemDto>(query).ToList();
}
return quotations.OrderBy(q => q.PriceDate);
}
public IEnumerable<DataItemDto> GetFxQuotationsForAnalysis(string symbol, string analysisType)
{
string query = GetSqlForFxQuotations(symbol, analysisType);
IEnumerable<DataItemDto> quotations;
using (var context = new EFDbContext())
{
quotations = context.Database.SqlQuery<DataItemDto>(query).ToList();
}
return quotations.OrderBy(q => q.PriceDate);
}
public void UpdateQuotation(QuotationDto quotation, string symbol)
{
string tableName = QuotationsTablePrefix + symbol;
string sql = "UPDATE fx." + tableName +
" SET " +
" OpenPrice = " + quotation.OpenPrice.ToDbString() +
", HighPrice = " + quotation.HighPrice.ToDbString() +
", LowPrice = " + quotation.LowPrice.ToDbString() +
", ClosePrice = " + quotation.ClosePrice.ToDbString() +
", Volume = " + quotation.Volume.ToDbString() +
" WHERE QuotationId = " + quotation.Id;
using (var context = new EFDbContext())
{
context.Database.ExecuteSqlCommand(sql);
context.SaveChanges();
}
}
public bool CreateTable(string tableName, string template)
{
string sqlCommand = string.Format("CREATE TABLE {0} LIKE {1}", tableName, template);
using (var context = new EFDbContext())
{
try
{
context.Database.ExecuteSqlCommand(sqlCommand);
return true;
}
catch (Exception)
{
return false;
}
}
}
public LastDates GetSymbolLastItems(string symbol, string analysisType)
{
string sqlQuotation = "SELECT MAX(PriceDate) FROM quotations_" + symbol + " WHERE OpenPrice > -1;";
string sqlAnalysisItem = "SELECT MAX(PriceDate) FROM " + analysisType + "_" + symbol + ";";
DateTime lastQuotation = new DateTime();
DateTime? lastAnalysisItem = null;
using (var context = new EFDbContext())
{
foreach (var i in context.Database.SqlQuery<String>(sqlQuotation))
{
lastQuotation = DateTime.Parse(i);
break;
}
foreach (var i in context.Database.SqlQuery<String>(sqlAnalysisItem))
{
try
{
lastAnalysisItem = DateTime.Parse(i);
}
catch (ArgumentNullException)
{
lastAnalysisItem = null;
}
break;
}
}
return new LastDates
{
LastQuotation = lastQuotation,
LastAnalysisItem = lastAnalysisItem
};
}
public void UpdateMacd(MacdDto macd, string symbol)
{
string tableName = MacdTablePrefix + symbol;
string sqlUpdate = "UPDATE fx." + tableName +
" SET " +
"MA13 = " + macd.Ma13.ToDbString() +
", EMA13 = " + macd.Ema13.ToDbString() +
", MA26 = " + macd.Ma26.ToDbString() +
", EMA26 = " + macd.Ema26.ToDbString() +
", MACDLine = " + macd.MacdLine.ToDbString() +
", SignalLine = " + macd.SignalLine.ToDbString() +
", Histogram = " + macd.Histogram.ToDbString() +
", HistogramAvg = " + macd.HistogramAvg.ToDbString() +
", HistogramExtremum = " + macd.HistogramExtremum.ToDbString() +
", DeltaHistogram = " + macd.DeltaHistogram.ToDbString() +
", DeltaHistogramPositive = " + macd.DeltaHistogramPositive +
", DeltaHistogramNegative = " + macd.DeltaHistogramNegative +
", DeltaHistogramZero = " + macd.DeltaHistogramZero +
", HistogramDirection2D = " + macd.HistogramDirection2D +
", HistogramDirection3D = " + macd.HistogramDirection3D +
", HistogramDirectionChanged = " + macd.HistogramDirectionChanged +
", HistogramToOX = " + macd.HistogramToOx +
", HistogramRow = " + macd.HistogramRow +
", OxCrossing = " + macd.OxCrossing.ToDbString() +
", MacdPeak = " + macd.MacdPeak +
", LastMACDPeak = " + macd.LastMacdPeak.ToDbString() +
", MACDPeakSlope = " + macd.MacdPeakSlope.ToDbString() +
", MACDTrough = " + macd.MacdTrough +
", LastMACDTrough = " + macd.LastMacdTrough.ToDbString() +
", MACDTroughSlope = " + macd.MacdTroughSlope.ToDbString() +
" WHERE PriceDate = '" + macd.PriceDate + "';";
using (var context = new EFDbContext())
{
context.Database.ExecuteSqlCommand(sqlUpdate);
context.SaveChanges();
}
}
public object GetDataSetProperties(string symbol)
{
//string sql = "SELECT " +
// "COUNT(Id) AS Counter, MIN(PriceDate) AS FirstDate, MAX(PriceDate) AS LastDate, " +
// "MIN(LowPrice) AS MinPrice, MAX(HighPrice) AS MaxPrice " +
// "FROM fx.quotations_" + symbol;
string sqlCounter = "SELECT COUNT(QuotationId) FROM fx.quotations_" + symbol;
string sqlMinDate = "SELECT MIN(PriceDate) FROM fx.quotations_" + symbol;
string sqlMaxDate = "SELECT MAX(PriceDate) FROM fx.quotations_" + symbol;
string sqlMinPrice = "SELECT MIN(LowPrice) FROM fx.quotations_" + symbol;
string sqlMaxPrice = "SELECT MAX(HighPrice) FROM fx.quotations_" + symbol;
using (var context = new EFDbContext())
{
int counter = 0;
DateTime firstDate = new DateTime();
DateTime lastDate = new DateTime();
double minPrice = 0d;
double maxPrice = 0d;
//Counter.
foreach (var i in context.Database.SqlQuery<String>(sqlCounter))
{
counter = int.Parse(i);
break;
}
//Min date.
foreach (var i in context.Database.SqlQuery<String>(sqlMinDate))
{
firstDate = DateTime.Parse(i);
break;
}
//Max date.
foreach (var i in context.Database.SqlQuery<String>(sqlMaxDate))
{
lastDate = DateTime.Parse(i);
break;
}
//Min price.
foreach (var i in context.Database.SqlQuery<String>(sqlMinPrice))
{
minPrice = double.Parse(i);
break;
}
//Max price.
foreach (var i in context.Database.SqlQuery<String>(sqlMaxPrice))
{
maxPrice = double.Parse(i);
break;
}
return new
{
counter = counter,
firstDate = firstDate,
lastDate = lastDate,
minPrice = minPrice,
maxPrice = maxPrice
};
}
}
public IEnumerable<CandlestickDto> GetCandlesticks(string symbol, DateTime? startDate, DateTime? endDate)
{
string query = GetSqlForFxCalculations(symbol, AdxTablePrefix, startDate, endDate);
IEnumerable<CandlestickDto> candlesticks;
using (var context = new EFDbContext())
{
candlesticks = context.Database.SqlQuery<CandlestickDto>(query).ToList();
}
return candlesticks.OrderBy(q => q.PriceDate);
}
public DateTime? GetAnalysisLastCalculation(string symbol, string analysisType)
{
string sqlAnalysisItem = "SELECT MAX(PriceDate) FROM " + analysisType + "_" + symbol + ";";
using (var context = new EFDbContext())
{
foreach (var i in context.Database.SqlQuery<String>(sqlAnalysisItem))
{
try
{
return DateTime.Parse(i);
}
catch (ArgumentNullException)
{
return null;
}
}
}
return null;
}
public IEnumerable<DataItemDto> GetQuotations(int companyId, int timeframe)
{
IEnumerable<DataItemDto> quotations = null;
IEnumerable<QuotationDto> tempQuotations = new List<QuotationDto>();
IEnumerable<PriceDto> tempPrices = new List<PriceDto>();
using (EFDbContext context = new EFDbContext())
{
switch (timeframe)
{
case 1:
//tempQuotations = context.DailyQuotations.Where(q => q.AssetId == companyId).AsQueryable();
//tempPrices = context.DailyPrices.Where(p => p.AssetId == companyId).AsQueryable();
break;
case 2:
//tempQuotations = context.WeeklyQuotations.Where(q => q.AssetId == companyId).AsQueryable();
//tempPrices = context.WeeklyPrices.Where(p => p.AssetId == companyId).AsQueryable();
break;
case 3:
//tempQuotations = context.MonthlyQuotations.Where(q => q.AssetId == companyId).AsQueryable();
//tempPrices = context.MonthlyPrices.Where(p => p.AssetId == companyId).AsQueryable();
break;
}
quotations = (from quotation in tempQuotations
join price in tempPrices
on quotation.PriceDate equals price.PriceDate
select new DataItemDto
{
PriceDate = quotation.PriceDate,
AssetId = quotation.AssetId
}).OrderBy(q => q.PriceDate).ToList();
}
return quotations;
}
private void RemoveExtrema(string symbol, DateTime date)
{
string sqlDelete = string.Format("DELETE FROM fx.{0} " +
" WHERE " +
" Symbol = '" + symbol + "' AND " +
" PriceDate = '" + date + "';", ExtremaEvaluationTable);
using (var context = new EFDbContext())
{
context.Database.ExecuteSqlCommand(sqlDelete);
context.SaveChanges();
}
}
public IEnumerable<String> GetStats()
{
string sql = "SELECT symbol FROM fx.last_updates";
IEnumerable<String> symbols;
using (var context = new EFDbContext())
{
symbols = context.Database.SqlQuery<String>(sql).ToList();
}
return symbols;
}
public bool CheckIfTableExists(string tableName)
{
using (var context = new EFDbContext())
{
bool exists = context.Database
.SqlQuery<int?>(@"
SELECT 1 FROM information_schema.tables AS T
WHERE table_schema = 'fx'
AND table_name = '" + tableName + "' LIMIT 1")
.SingleOrDefault() != null;
return exists;
}
}
public void UpdateExtremum(ExtremumDto extremum)
{
if (extremum == null) return;
string sql = string.Format(extremum.Cancelled ? extremum.RemoveSql() : extremum.UpdateSql(), ExtremaEvaluationTable);
using (var context = new EFDbContext())
{
context.Database.ExecuteSqlCommand(sql);
context.SaveChanges();
}
}
public IEnumerable<ExtremumDto> GetExtrema(string symbol, DateTime startDate, DateTime endDate)
{
IEnumerable<ExtremumDto> extrema;
string query = "SELECT * FROM fx._extrema_evaluation WHERE symbol = '{0}' AND pricedate BETWEEN '{1}' AND '{2}';";
query = string.Format(query, symbol, startDate, endDate);
using (var context = new EFDbContext())
{
extrema = context.Database.SqlQuery<ExtremumDto>(query).ToList();
}
return extrema;
}
public void UpdatePrice(PriceDto price, string symbol)
{
string tableName = PricesTablePrefix + symbol;
string sql = "UPDATE fx." + tableName +
" SET " +
" AssetId = " + price.AssetId +
", PriceDate = '" + price.PriceDate + "'" +
", DeltaClosePrice = " + price.DeltaClosePrice.ToDbString() +
", PriceDirection2D = " + price.PriceDirection2D +
", PriceDirection3D = " + price.PriceDirection3D +
", PeakByCloseEvaluation = " + price.PeakByCloseEvaluation.ToDbString() +
", PeakByHighEvaluation = " + price.PeakByHighEvaluation.ToDbString() +
", TroughByCloseEvaluation = " + price.TroughByCloseEvaluation.ToDbString() +
", TroughByLowEvaluation = " + price.TroughByLowEvaluation.ToDbString() +
", PriceGap = " + price.PriceGap.ToDbString() +
" WHERE PriceId = " + price.Id;
//Update info about extrema.
UpdateExtremum(price.PeakByClose);
UpdateExtremum(price.PeakByHigh);
UpdateExtremum(price.TroughByClose);
UpdateExtremum(price.TroughByLow);
using (var context = new EFDbContext())
{
context.Database.ExecuteSqlCommand(sql);
context.SaveChanges();
}
}
public IEnumerable<DataItemDto> GetFxQuotations(string symbol, int count)
{
string query = GetSqlForFxQuotations(symbol);
IEnumerable<DataItemDto> quotations;
//string sql = "SELECT * FROM fx.{0} ORDER BY PriceDate DESC LIMIT {1}";
using (var context = new EFDbContext())
{
quotations = context.Database.SqlQuery<DataItemDto>(query).ToList();
}
return quotations.OrderBy(q => q.PriceDate);
}
private void AddExtremum(ExtremumDto extremum)
{
if (extremum == null) return;
string sqlInsert = string.Format(extremum.InsertSql(), ExtremaEvaluationTable);
using (var context = new EFDbContext())
{
context.Database.ExecuteSqlCommand(sqlInsert);
context.SaveChanges();
}
}
public IEnumerable<DataItemDto> GetFxQuotations(string symbol, DateTime start, DateTime end)
{
string query = GetSqlForFxQuotations(symbol, start, end);
//string sql = "SELECT * FROM fx.{0} WHERE PriceDate >= {1} AND PriceDate <= {2} ORDER BY PriceDate DESC";
//string query = string.Format(sql, symbol, start.ToString(), end.ToString());
IEnumerable<DataItemDto> quotations;
using (var context = new EFDbContext())
{
quotations = context.Database.SqlQuery<DataItemDto>(query).ToList();
}
return quotations.OrderBy(q => q.PriceDate);
}
