public async Task Add(IntervalKey intervalKey)
{
using (var context = ContextFactory.CreateDbContext(null))
{
if (await context.IntervalKey.FindAsync(intervalKey.Key) == null)
{
await context.IntervalKey.AddAsync(new IntervalKeyEntity
{
IntervalKey = intervalKey.Key,
IntervalGroupId = (int)intervalKey.IntervalGroup,
Label = intervalKey.Label
});
await context.SaveChangesAsync();
var from = new DateTime(2008, 1, 1, 0, 0, 0, DateTimeKind.Utc);
var to = new DateTime(DateTime.Now.Year + 2, 1, 1, 0, 0, 0, DateTimeKind.Utc);
var cursor = from;
while (cursor < to)
{
var intervals = IntervalFactory.GenerateIntervals(intervalKey, new Epoch(cursor), new Epoch(cursor.AddYears(1)));
await AddInterval(intervals);
cursor = cursor.AddYears(1);
}
}
}
}
public async Task <ICollection <MarketAggregate> > GetTradeAggregates(ExchangeEnum exchange, SymbolCodeEnum symbolCode, IntervalKey intervalKey, Epoch from, int dataPoints)
{
var intervals = IntervalFactory.GenerateIntervals(intervalKey, from, dataPoints);
var min = intervals.First();
var max = intervals.Last();
var aggs = await GetTradeAggregates(exchange, symbolCode, intervalKey, min.From, max.From, null, null);
return(aggs.Items);
}
public async Task <PagedCollection <MarketAggregate> > GetTradeAggregates(ExchangeEnum exchange, SymbolCodeEnum symbolCode, IntervalKey intervalKey, Epoch from, Epoch to, int?pageSize, int?pageNumber)
{
var intervals = IntervalFactory.GenerateIntervals(intervalKey, from, to);
var min = intervals.First();
var max = intervals.Last();
using (var context = ContextFactory.CreateDbContext(null))
{
var query = context.ExchangeTradeAggregate
.Where(a => a.ExchangeId == (int)exchange && a.SymbolId == (int)symbolCode && a.IntervalKey == intervalKey.Key && a.Timestamp >= min.From.TimestampMilliseconds && a.Timestamp <= max.From.TimestampMilliseconds);
var totalCount = 0;
if (pageSize.HasValue)
{
totalCount = await query.CountAsync();
query = query.Skip(pageNumber.GetValueOrDefault(0) * pageSize.Value).Take(pageSize.Value);
}
var aggs = await query.ToListAsync();
return(new PagedCollection <MarketAggregate>()
{
PageNumber = pageNumber.GetValueOrDefault(0),
PageSize = pageSize.GetValueOrDefault(0),
ItemCount = pageSize.HasValue ? totalCount : aggs.Count,
Items = aggs.Select(a => new MarketAggregate
{
Exchange = exchange,
Symbol = symbolCode,
IntervalKey = a.IntervalKey,
Epoch = Epoch.FromMilliseconds(a.Timestamp),
Open = a.Open,
OpenEpoch = Epoch.FromMilliseconds(a.OpenTimestamp),
High = a.High,
Low = a.Low,
Close = a.Close,
CloseEpoch = Epoch.FromMilliseconds(a.CloseTimestamp),
BuyVolume = a.BuyVolume,
SellVolume = a.SellVolume,
TotalVolume = a.TotalVolume,
BuyCount = a.BuyCount,
SellCount = a.SellCount,
TotalCount = a.TotalCount
}).ToList()
});
}
}
public async Task SaveTradeStatAggregates(IStorageTransaction transaction, ExchangeEnum exchange, SymbolCodeEnum symbolCode, ICollection <MarketTradeStat> tradeStats)
{
var min = tradeStats.Min(t => t.Epoch);
var max = tradeStats.Max(t => t.Epoch);
var expanded = new List <TradeStatCartesian>();
foreach (var group in IntervalFactory.ListGroups())
{
foreach (var ik in IntervalFactory.ListIntervalKeys(group.IntervalGroup))
{
foreach (var tradeStat in tradeStats)
{
var period = IntervalFactory.GenerateIntervals(ik, tradeStat.Epoch, tradeStat.Epoch).FirstOrDefault();
expanded.Add(new TradeStatCartesian
{
IntervalKey = period.IntervalKey,
IntervalEpoch = period.From,
Exchange = tradeStat.Exchange,
SymbolCode = symbolCode,
StatKey = tradeStat.StatKey,
Epoch = tradeStat.Epoch,
Value = tradeStat.Value
});
}
}
}
using (var context = ContextFactory.CreateDbContext(null))
{
using (var cmd = context.Database.GetDbConnection().CreateCommand())
{
var sql = @"insert into `exchange_trade_aggregate`
(`exchange_id`,
`symbol_id`,
`interval_key`,
`timestamp`,
`open`,
`open_timestamp`,
`high`,
`low`,
`close`,
`close_timestamp`,
`total_count`) values ";
sql += string.Join(",\r\n", expanded.Select(t => $"(" +
$"{(int)t.Exchange}," +
$"{(int)t.SymbolCode}," +
$"'{t.IntervalKey.Key}'," +
$"{t.IntervalEpoch.TimestampMilliseconds}," +
$"{t.Value}," +
$"{t.Epoch.TimestampMilliseconds}," +
$"{t.Value}," +
$"{t.Value}," +
$"{t.Value}," +
$"{t.Epoch.TimestampMilliseconds}," +
$"1)"));
sql += @"
on duplicate key update
`open` = case when values(`open_timestamp`) < `open_timestamp` then values(`open`) else `open` end,
`open_timestamp` = case when values(`open_timestamp`) < `open_timestamp` then values(`open_timestamp`) else `open_timestamp` end,
`high` = case when values(`high`) > `high` then values(`high`) else `high` end,
`low` = case when values(`low`) < `low` then values(`low`) else `low` end,
`close` = case when values(`close_timestamp`) > `close_timestamp` then values(`close`) else `close` end,
`close_timestamp` = case when values(`close_timestamp`) > `close_timestamp` then values(`close_timestamp`) else `close_timestamp` end,
`total_count` = `total_count` + values(`total_count`)" ;
cmd.CommandText = sql;
await cmd.Connection.OpenAsync();
await cmd.ExecuteNonQueryAsync();
cmd.Connection.Close();
}
}
}
public async Task SaveTradeAggregates(IStorageTransaction transaction, ExchangeEnum exchange, SymbolCodeEnum symbolCode, ICollection <MarketTrade> trades)
{
var min = trades.Min(t => t.Epoch);
var max = trades.Max(t => t.Epoch);
var expanded = new List <TradeCartesian>();
foreach (var group in IntervalFactory.ListGroups())
{
foreach (var ik in IntervalFactory.ListIntervalKeys(group.IntervalGroup))
{
foreach (var trade in trades)
{
var period = IntervalFactory.GenerateIntervals(ik, trade.Epoch, trade.Epoch).FirstOrDefault();
expanded.Add(new TradeCartesian
{
IntervalKey = period.IntervalKey,
IntervalEpoch = period.From,
Exchange = trade.Exchange,
SymbolCode = trade.SymbolCode,
Epoch = trade.Epoch,
TradeId = trade.TradeId,
Price = trade.Price,
Volume = trade.Volume,
Side = trade.Side
});
}
}
}
var context = (HistorianDbContext)transaction.GetContext();
using (var cmd = context.Database.GetDbConnection().CreateCommand())
{
cmd.Transaction = context.Database.CurrentTransaction.GetDbTransaction();
var sql = @"insert into `exchange_trade_aggregate`
(`exchange_id`,
`symbol_id`,
`interval_key`,
`timestamp`,
`open`,
`open_timestamp`,
`high`,
`low`,
`close`,
`close_timestamp`,
`buy_volume`,
`sell_volume`,
`total_volume`,
`buy_count`,
`sell_count`,
`total_count`) values ";
sql += string.Join(",\r\n", expanded.Select(t => $"(" +
$"{(int)t.Exchange}," +
$"{(int)t.SymbolCode}," +
$"'{t.IntervalKey.Key}'," +
$"{t.IntervalEpoch.TimestampMilliseconds}," +
$"{t.Price}," +
$"{t.Epoch.TimestampMilliseconds}," +
$"{t.Price}," +
$"{t.Price}," +
$"{t.Price}," +
$"{t.Epoch.TimestampMilliseconds}," +
$"{(t.Side == OrderSideEnum.Buy ? t.Volume.ToString() : "NULL")}," +
$"{(t.Side == OrderSideEnum.Sell ? t.Volume.ToString() : "NULL")}," +
$"{t.Volume}," +
$"{(t.Side == OrderSideEnum.Buy ? "1" : "NULL")}," +
$"{(t.Side == OrderSideEnum.Sell ? "1" : "NULL")}," +
$"1)"));
sql += @"
on duplicate key update
`open` = case when values(`open_timestamp`) < `open_timestamp` then values(`open`) else `open` end,
`open_timestamp` = case when values(`open_timestamp`) < `open_timestamp` then values(`open_timestamp`) else `open_timestamp` end,
`high` = case when values(`high`) > `high` then values(`high`) else `high` end,
`low` = case when values(`low`) < `low` then values(`low`) else `low` end,
`close` = case when values(`close_timestamp`) > `close_timestamp` then values(`close`) else `close` end,
`close_timestamp` = case when values(`close_timestamp`) > `close_timestamp` then values(`close_timestamp`) else `close_timestamp` end,
`buy_volume` = case when values(`buy_volume`) is not null then ifnull(`buy_volume`, 0) + values(`buy_volume`) else `buy_volume` end,
`sell_volume` = case when values(`sell_volume`) is not null then ifnull(`sell_volume`, 0) + values(`sell_volume`) else `sell_volume` end,
`total_volume` = `total_volume` + values(`total_volume`),
`buy_count` = case when values(`buy_count`) is not null then ifnull(`buy_count`, 0) + values(`buy_count`) else `buy_count` end,
`sell_count` = case when values(`sell_count`) is not null then ifnull(`sell_count`, 0) + values(`sell_count`) else `sell_count` end,
`total_count` = `total_count` + values(`total_count`)";
cmd.CommandText = sql;
await cmd.ExecuteNonQueryAsync();
}
}
