public async Task <Cycle> Get(int index, Symbols quote)
{
var sql = @"
SELECT *
FROM ""Cycles""
WHERE ""Index"" = @index
LIMIT 1";
using var db = GetConnection();
var row = await db.QueryFirstOrDefaultAsync(sql, new { index });
if (row == null)
{
return(null);
}
return(new Cycle
{
Index = row.Index,
FirstLevel = row.FirstLevel,
StartTime = Times[row.FirstLevel],
LastLevel = row.LastLevel,
EndTime = Times[row.LastLevel],
RandomSeed = row.Seed,
SnapshotIndex = row.SnapshotIndex,
SnapshotLevel = row.SnapshotLevel,
TotalBakers = row.TotalBakers,
TotalDelegated = row.TotalDelegated,
TotalDelegators = row.TotalDelegators,
TotalRolls = row.TotalRolls,
TotalStaking = row.TotalStaking,
Quote = Quotes.Get(quote, row.LastLevel)
});
}
public async Task <Cycle> Get(int index, Symbols quote)
{
var sql = @"
SELECT *
FROM ""Cycles""
WHERE ""Index"" = @index
LIMIT 1";
using var db = GetConnection();
var row = await db.QueryFirstOrDefaultAsync(sql, new { index });
if (row == null)
{
return(null);
}
var cycleSize = Protocols.Current.BlocksPerCycle;
return(new Cycle
{
Index = row.Index,
RandomSeed = row.Seed,
SnapshotIndex = row.SnapshotIndex,
SnapshotLevel = row.SnapshotLevel,
TotalBakers = row.TotalBakers,
TotalDelegated = row.TotalDelegated,
TotalDelegators = row.TotalDelegators,
TotalRolls = row.TotalRolls,
TotalStaking = row.TotalStaking,
Quote = Quotes.Get(quote, (row.Index + 1) * cycleSize)
});
}
public async Task <IEnumerable <HistoricalBalance> > Get(
string address,
int step,
SortParameter sort,
int offset,
int limit,
Symbols quote)
{
var account = await Accounts.GetAsync(address);
if (account == null)
{
return(Enumerable.Empty <HistoricalBalance>());
}
var union = SelectUnion(account);
if (union.Length == 0)
{
return(Enumerable.Empty <HistoricalBalance>());
}
var key = step > 1
? @"(""Level"" + @step - 1) / @step * @step"
: @"""Level""";
var orderBy = sort?.Desc == "level" ? "ORDER BY lvl DESC" : "";
var sql = $@"
SELECT lvl as ""Level"", (SUM(""Change"") OVER (ORDER BY lvl asc))::bigint as ""Balance""
FROM (
SELECT {key} as lvl, SUM(""Change"")::bigint as ""Change""
FROM ({union}) as u
GROUP BY lvl
ORDER BY lvl
) as gr
WHERE lvl <= {State.Current.Level}
{orderBy}
OFFSET @offset
LIMIT @limit";
using var db = GetConnection();
var rows = await db.QueryAsync(sql, new { account = account.Id, step, offset, limit });
return(rows.Select(row => new HistoricalBalance
{
Level = row.Level,
Timestamp = Time[row.Level],
Balance = row.Balance,
Quote = Quotes.Get(quote, row.Level)
}));
}
public async Task <Block> Get(int level, bool operations, MichelineFormat format, Symbols quote)
{
var sql = @"
SELECT *
FROM ""Blocks""
WHERE ""Level"" = @level
LIMIT 1";
using var db = GetConnection();
var row = await db.QueryFirstOrDefaultAsync(sql, new { level });
if (row == null)
{
return(null);
}
var block = new Block
{
Cycle = row.Cycle,
Level = level,
Hash = row.Hash,
Timestamp = row.Timestamp,
Proto = row.ProtoCode,
Priority = row.Priority,
Validations = row.Validations,
Deposit = row.Deposit,
Reward = row.Reward,
Fees = row.Fees,
NonceRevealed = row.RevelationId != null,
Baker = row.BakerId != null ? await Accounts.GetAliasAsync(row.BakerId) : null,
Software = row.SoftwareId != null ? Software[row.SoftwareId] : null,
LBEscapeVote = row.LBEscapeVote,
LBEscapeEma = row.LBEscapeEma,
Quote = Quotes.Get(quote, level)
};
if (operations)
{
await LoadOperations(block, (Data.Models.Operations) row.Operations, format, quote);
}
return(block);
}
public Quote GetLast()
{
var state = State.Current;
return(new Quote
{
Level = state.Level,
Timestamp = Time[state.Level],
Btc = Quotes.Get(0),
Eur = Quotes.Get(1),
Usd = Quotes.Get(2),
Cny = Quotes.Get(3),
Jpy = Quotes.Get(4),
Krw = Quotes.Get(5),
Eth = Quotes.Get(6),
Gbp = Quotes.Get(7)
});
}
public async Task Write(StreamWriter csv, string address, DateTime from, DateTime to, int limit, string delimiter, string separator, int symbol)
{
var account = await Accounts.GetAsync(address);
if (account == null)
{
return;
}
var sql = new StringBuilder();
if (account.DelegationsCount > 0)
{
UnionDelegations(sql);
}
if (account.OriginationsCount > 0)
{
UnionOriginations(sql);
}
if (account.TransactionsCount > 0)
{
UnionTransactions(sql);
}
if (account.RevealsCount > 0)
{
UnionReveals(sql);
}
if (account.MigrationsCount > 0)
{
UnionMigrations(sql);
}
if (account is RawUser user)
{
if (user.Activated == true)
{
UnionActivations(sql);
}
if (user.RegisterConstantsCount > 0)
{
UnionRegisterConstant(sql);
}
}
if (account is RawDelegate delegat)
{
if (delegat.BlocksCount > 0)
{
UnionBaking(sql);
}
if (delegat.EndorsementsCount > 0)
{
UnionEndorsements(sql);
}
if (delegat.DoubleBakingCount > 0)
{
UnionDoubleBaking(sql);
}
if (delegat.DoubleEndorsingCount > 0)
{
UnionDoubleEndorsing(sql);
}
if (delegat.NonceRevelationsCount > 0)
{
UnionNonceRevelations(sql);
}
if (delegat.RevelationPenaltiesCount > 0)
{
UnionRevelationPenalties(sql);
}
}
if (sql.Length == 0)
{
return;
}
sql.AppendLine(@"ORDER BY ""Id""");
sql.AppendLine(@"LIMIT @limit");
using var db = GetConnection();
var rows = await db.QueryAsync(sql.ToString(), new { account = account.Id, from, to, limit });
#region write header
var symbolName = symbol switch
{
0 => "BTC",
1 => "EUR",
2 => "USD",
3 => "CNY",
4 => "JPY",
5 => "KRW",
6 => "ETH",
7 => "GBP",
_ => ""
};
csv.Write("Block level");
csv.Write(delimiter);
csv.Write("Datetime");
csv.Write(delimiter);
csv.Write("Operation");
csv.Write(delimiter);
if (account is RawDelegate)
{
csv.Write("Reward XTZ");
csv.Write(delimiter);
csv.Write($"Reward {symbolName}");
csv.Write(delimiter);
csv.Write("Loss XTZ");
csv.Write(delimiter);
csv.Write($"Loss {symbolName}");
csv.Write(delimiter);
}
csv.Write("Received XTZ");
csv.Write(delimiter);
csv.Write($"Received {symbolName}");
csv.Write(delimiter);
csv.Write("From address");
csv.Write(delimiter);
csv.Write("Sent XTZ");
csv.Write(delimiter);
csv.Write($"Sent {symbolName}");
csv.Write(delimiter);
csv.Write("Fee XTZ");
csv.Write(delimiter);
csv.Write($"Fee {symbolName}");
csv.Write(delimiter);
csv.Write("To address");
csv.Write(delimiter);
csv.Write("Explorer link");
csv.Write("\n");
#endregion
#region write rows
var format = new NumberFormatInfo {
NumberDecimalSeparator = separator
};
var price = Quotes.Get(symbol);
foreach (var row in rows)
{
csv.Write(row.Level);
csv.Write(delimiter);
csv.Write(row.Timestamp.ToString("yyyy-MM-dd HH:mm:ss"));
csv.Write(delimiter);
csv.Write(Operations[row.Type]);
csv.Write(delimiter);
if (account is RawDelegate)
{
csv.Write(row.Reward == null ? "" : ((decimal)row.Reward / 1_000_000m).ToString(format));
csv.Write(delimiter);
csv.Write(row.Reward == null ? "" : ((double)row.Reward / 1_000_000d * price).ToString(format));
csv.Write(delimiter);
csv.Write(row.Loss == null ? "" : ((decimal) - row.Loss / 1_000_000m).ToString(format));
csv.Write(delimiter);
csv.Write(row.Loss == null ? "" : ((double)-row.Loss / 1_000_000d * price).ToString(format));
csv.Write(delimiter);
}
csv.Write(row.Received == null ? "" : ((decimal)row.Received / 1_000_000m).ToString(format));
csv.Write(delimiter);
csv.Write(row.Received == null ? "" : ((double)row.Received / 1_000_000d * price).ToString(format));
csv.Write(delimiter);
// WARN: possible NullReferenceException if chain reorgs during request execution (very unlikely)
csv.Write(row.From == null ? "" : Accounts.Get(row.From).Address);
csv.Write(delimiter);
csv.Write(row.Sent == null ? "" : ((decimal) - row.Sent / 1_000_000m).ToString(format));
csv.Write(delimiter);
csv.Write(row.Sent == null ? "" : ((double)-row.Sent / 1_000_000d * price).ToString(format));
csv.Write(delimiter);
csv.Write(row.Fee == null ? "" : ((decimal) - row.Fee / 1_000_000m).ToString(format));
csv.Write(delimiter);
csv.Write(row.Fee == null ? "" : ((double)-row.Fee / 1_000_000d * price).ToString(format));
csv.Write(delimiter);
// WARN: possible NullReferenceException if chain reorgs during request execution (very unlikely)
csv.Write(row.To == null ? "" : Accounts.Get(row.To).Address);
csv.Write(delimiter);
csv.Write(row.Nonce != null
? $"https://tzkt.io/{row.OpHash}/{row.Counter}/{row.Nonce}"
: row.Counter != null
? $"https://tzkt.io/{row.OpHash}/{row.Counter}"
: row.OpHash != null
? $"https://tzkt.io/{row.OpHash}"
: "");
csv.Write("\n");
}
#endregion
csv.Flush();
}