public async Task <AddressLiteDto> GetAddressLiteAsync(int addressId, string addressHash) { const string sqlQ = @"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT a.[AddressId], a.[Hash], a.[FirstBlockHeight] AS FirstBlockSeen, aa.[LastBlockHeight] AS LastBlockSeen, aa.[Balance] FROM [dbo].[Address] a LEFT JOIN [dbo].[AddressAggregate] aa ON aa.[AddressId] = a.[AddressId] WHERE a.[AddressId] = @addressId;"; using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync()) { return((await sqlCon.QueryAsync <AddressLiteDto>(sqlQ, new { addressId })).FirstOrDefault()); } }
public async Task <double> GetAverageBalanceAsync(bool includeZeroBalance) { var sqlQ = @"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT AVG(aa.[Balance]) FROM [dbo].[AddressAggregate] aa"; if (includeZeroBalance) { sqlQ += " WHERE aa.[Balance] > 0"; } using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync()) { var result = await sqlCon.QueryAsync <double>(sqlQ); return(result.FirstOrDefault()); } }
public async Task <long> GetCountFilteredAsync(AddressFilterCriteria filter) { var min = filter.MinBalance ?? 0; var max = filter.MaxBalance ?? double.MaxValue; var fromHeight = filter.HeightFrom ?? 0; var toHeight = filter.HeightTo ?? int.MaxValue; var sqlQ = $@"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT COUNT(1) FROM [dbo].[AddressAggregate] aa WHERE aa.[Balance] >= @min AND aa.[Balance] <= @max AND aa.[LastBlockHeight] >= @fromHeight AND aa.[LastBlockHeight] <= @toHeight;"; using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync()) { var param = new { min, max, fromHeight, toHeight }; return((await sqlCon.QueryAsync <long>(sqlQ, param)).FirstOrDefault()); } }
public async Task <FilterResult <BlockLiteDto> > GetBlocksFilteredAsync(BlockFilterCriteria filter, int start, int count, bool countResults, int?maxResults = null) { const string from = @" FROM [dbo].[Block] b INNER JOIN [dbo].[Transaction] t ON t.[BlockHeight] = b.[Height] WHERE 1 = 1 "; const string groupBy = @" GROUP BY b.[Height], b.[Hash], b.[Size], b.[Channel], b.[Version], b.[MerkleRoot], b.[Timestamp], b.[Nonce], b.[Bits], b.[Difficulty], b.[Mint] " ; var where = BuildWhereClause(filter, out var param); var sqlOrderBy = "ORDER BY "; switch (filter.OrderBy) { case OrderBlocksBy.Highest: sqlOrderBy += "b.[Height] DESC "; break; case OrderBlocksBy.Lowest: sqlOrderBy += "b.[Height] "; break; case OrderBlocksBy.Largest: sqlOrderBy += "b.[Size] DESC "; break; case OrderBlocksBy.Smallest: sqlOrderBy += "b.[Size] "; break; default: sqlOrderBy += "b.[Height] DESC "; throw new ArgumentOutOfRangeException(); } var sqlQ = $@"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT b.[Height], b.[Hash], b.[Size], b.[Channel], b.[Timestamp], b.[Difficulty], b.[Mint], COUNT(t.[TransactionId]) AS TransactionCount {from} {where} {groupBy} {sqlOrderBy} OFFSET @start ROWS FETCH NEXT @count ROWS ONLY;"; var sqlC = $@"SELECT COUNT(*) FROM (SELECT TOP (@maxResults) 1 AS Cnt {from} {where} {groupBy}) AS resultCount;"; using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync()) { var results = new FilterResult <BlockLiteDto>(); param.Add(nameof(count), count); param.Add(nameof(start), start); param.Add(nameof(maxResults), maxResults ?? int.MaxValue); using (var multi = await sqlCon.QueryMultipleAsync(string.Concat(sqlQ, sqlC), param)) { var dbBlocks = (await multi.ReadAsync()).Select(x => new BlockLiteDto { Height = x.Height, Hash = x.Hash, Size = x.Size, Channel = x.Channel, Timestamp = x.Timestamp, Difficulty = x.Difficulty, Mint = x.Mint, TransactionCount = x.TransactionCount }); results.Results = dbBlocks.ToList(); results.ResultCount = countResults ? (int)(await multi.ReadAsync <int>()).FirstOrDefault() : -1; } return(results); } }
public async Task <List <ChannelStatDto> > GetChannelStatsAsync() { const string sqlQ = @"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT b.Channel, COUNT(*) AS Height FROM Block b GROUP BY b.Channel"; using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync()) { var channelResult = (await sqlCon.QueryAsync(sqlQ)).ToList(); var miningInfo = await _redisCommand.GetAsync <MiningInfoDto>(Settings.Redis.MiningInfoLatest); if (miningInfo == null) { return(null); } var channelStatTasks = channelResult .Select(async x => { var channel = (BlockChannels)x.Channel; double diff = 0; double reward = 0; long rateSec = 0; double reserve = 0; switch (channel) { case BlockChannels.PoS: var lastPosBlock = await _blockQuery.GetLastBlockAsync(channel); diff = lastPosBlock.Difficulty; reward = lastPosBlock.Mint; break; case BlockChannels.Prime: diff = miningInfo.PrimeDifficulty; reward = miningInfo.PrimeValue; rateSec = miningInfo.PrimesPerSecond; reserve = miningInfo.PrimeReserve; break; case BlockChannels.Hash: diff = miningInfo.HashDifficulty; reward = miningInfo.HashValue; rateSec = miningInfo.HashPerSecond; reserve = miningInfo.HashReserve; break; } return(new ChannelStatDto { Channel = ((BlockChannels)x.Channel).ToString(), Height = (int)x.Height + await _blockQuery.GetChannelHeightAsync((BlockChannels)x.Channel), Difficulty = diff, Reward = reward, RatePerSecond = rateSec, Reserve = reserve, CreatedOn = miningInfo.CreatedOn }); }); return((await Task.WhenAll(channelStatTasks)).ToList()); } }
public async Task <FilterResult <TransactionLiteDto> > GetTransactionsFilteredAsync(TransactionFilterCriteria filter, int start, int count, bool countResults, int?maxResults = null) { const string from = @" FROM [dbo].[Transaction] t WHERE 1 = 1 "; var where = BuildWhereClause(filter, out var param); var sqlOrderBy = "ORDER BY "; switch (filter.OrderBy) { case OrderTransactionsBy.LowestAmount: sqlOrderBy += "t.[Amount] "; break; case OrderTransactionsBy.HighestAmount: sqlOrderBy += "t.[Amount] DESC "; break; case OrderTransactionsBy.LeastRecent: sqlOrderBy += "t.[Timestamp] "; break; case OrderTransactionsBy.MostRecent: sqlOrderBy += "t.[Timestamp] DESC "; break; default: sqlOrderBy += "t.[Timestamp] DESC "; break; } var sqlQ = $@"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DISTINCT t.[TransactionId], t.[Hash] AS TransactionHash, t.[BlockHeight], t.[Timestamp], t.[Amount], t.[TransactionType], ( SELECT COUNT(*) FROM [dbo].[TransactionInputOutput] tInOut WHERE tInOut.[TransactionId] = t.[TransactionId] AND tInOut.[TransactionInputOutputType] = 0 ) AS TransactionInputCount, ( SELECT COUNT(*) FROM [dbo].[TransactionInputOutput] tInOut WHERE tInOut.[TransactionId] = t.[TransactionId] AND tInOut.[TransactionInputOutputType] = 1 ) AS TransactionOutputCount {from} {where} AND t.[Amount] > 0 {sqlOrderBy} OFFSET @start ROWS FETCH NEXT @count ROWS ONLY;"; var sqlC = $@"SELECT COUNT(*) FROM (SELECT TOP (@maxResults) 1 AS Cnt {from} {where}) AS resultCount;"; using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync()) { var results = new FilterResult <TransactionLiteDto>(); param.Add(nameof(count), count); param.Add(nameof(start), start); param.Add(nameof(maxResults), maxResults ?? int.MaxValue); using (var multi = await sqlCon.QueryMultipleAsync(string.Concat(sqlQ, sqlC), param)) { results.Results = (await multi.ReadAsync <TransactionLiteDto>()).ToList(); results.ResultCount = countResults ? (int)(await multi.ReadAsync <int>()).FirstOrDefault() : -1; return(results); } } }
public async Task <FilterResult <AddressTransactionDto> > GetAddressTransactionsFilteredAsync(AddressTransactionFilterCriteria filter, int start, int count, bool countResults, int?maxResults = null) { const string from = @" FROM [dbo].[Transaction] t INNER JOIN [dbo].[TransactionInputOutput] tInOut ON tInOut.[TransactionId] = t.[TransactionId] INNER JOIN [dbo].[Address] a ON a.[AddressId] = tInOut.[AddressId] "; var where = BuildWhereClause(filter, out var param); var sqlOrderBy = "ORDER BY "; switch (filter.OrderBy) { case OrderTransactionsBy.LowestAmount: sqlOrderBy += "tInOut.[Amount] "; break; case OrderTransactionsBy.HighestAmount: sqlOrderBy += "tInOut.[Amount] DESC "; break; case OrderTransactionsBy.LeastRecent: sqlOrderBy += "t.[Timestamp] "; break; case OrderTransactionsBy.MostRecent: sqlOrderBy += "t.[Timestamp] DESC "; break; default: sqlOrderBy += "t.[Timestamp] DESC "; break; } var sqlQ = $@" SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT t.[TransactionId], t.[TransactionType], t.[BlockHeight], t.[Timestamp], t.[Hash] AS TransactionHash, a.[Hash] AS AddressHash, {(filter.Grouped ? @"CASE WHEN SUM(CASE WHEN tInOut.[TransactionInputOutputType] = 0 THEN -tInOut.[Amount] ELSE tInOut.[Amount] END) < 0 THEN 0 ELSE 1 END " : "tInOut.[TransactionInputOutputType] ")} AS TransactionInputOutputType, {(filter.Grouped ? @"SUM(CASE WHEN tInOut.[TransactionInputOutputType] = 0 THEN -tInOut.[Amount] ELSE tInOut.[Amount] END) " : "CASE WHEN tInOut.[TransactionInputOutputType] = 0 THEN -tInOut.[Amount] ELSE tInOut.[Amount] END ")} AS Amount {from} WHERE 1 = 1 {where} {(filter.Grouped ? "GROUP BY t.[TransactionId], t.[BlockHeight], t.[Timestamp], t.[Hash], a.[Hash], t.[TransactionType] " : "" )} {sqlOrderBy}, TransactionInputOutputType DESC OFFSET @start ROWS FETCH NEXT @count ROWS ONLY" ; var sqlC = $@" SELECT COUNT(*) FROM (SELECT TOP (@maxResults) 1 AS Cnt {from} WHERE 1 = 1 {where} {(filter.Grouped ? "GROUP BY t.[TransactionId]" : "")}) AS resultCount;"; var sqlT = $@" SELECT tInOut.[TransactionId], tInOut.[TransactionInputOutputId], tInOut.[TransactionInputOutputType], tInOut.[Amount] AS Amount, a.[Hash] FROM [dbo].[TransactionInputOutput] tInOut INNER JOIN [dbo].[Address] a ON a.[AddressId] = tInOut.[AddressId] WHERE tInOut.[TransactionId] IN @txIds"; using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync()) { param.Add(nameof(count), count); param.Add(nameof(start), start); param.Add(nameof(maxResults), maxResults ?? int.MaxValue); List <TransactionLineItemDto> txItems; int resultCount; using (var multi = await sqlCon.QueryMultipleAsync(string.Concat(sqlQ, sqlC), param)) { txItems = (await multi.ReadAsync <TransactionLineItemDto>()).ToList(); resultCount = countResults ? (int)(await multi.ReadAsync <int>()).FirstOrDefault() : -1; } var txIds = txItems.Select(x => x.TransactionId).Distinct(); var txsResult = await sqlCon.QueryAsync(sqlT, new { txIds }); var txs = txsResult.GroupBy(x => x.TransactionId) .ToDictionary(x => (int)x.Key, y => y .Select(z => new AddressTransactionItemDto { TransactionInputOutputId = z.TransactionInputOutputId, TransactionInputOutputType = (TransactionInputOutputType)z.TransactionInputOutputType, Amount = z.Amount, AddressHash = z.Hash })); var addressTxs = txItems .Where(x => filter.AddressHashes.Any(y => y == x.AddressHash)) .Select(x => new AddressTransactionDto { AddressHash = x.AddressHash, BlockHeight = x.BlockHeight, TransactionHash = x.TransactionHash, Amount = x.Amount, Timestamp = x.Timestamp, TransactionType = (TransactionType)x.TransactionType, TransactionId = x.TransactionId, TransactionInputOutputType = x.TransactionInputOutputType, TransactionItems = txs[x.TransactionId] .Where(y => y.TransactionInputOutputId != x.TransactionInputOutputId) .OrderByDescending(y => y.Amount) .ToList() }).ToList(); //if (filter.Grouped) // addressTxs = GroupTransactions(addressTxs); return(new FilterResult <AddressTransactionDto> { Results = OrderBy(addressTxs, filter.OrderBy, count), ResultCount = resultCount }); } }
public async Task <List <AddressBalanceDto> > GetAddressBalance(string addressHash, int days) { var addressId = await GetAddressIdAsync(addressHash); const string sqlQ = @"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT tInOut.[TransactionInputOutputType], tInOut.[Amount], t.[Timestamp] FROM [dbo].[TransactionInputOutput] tInOut INNER JOIN [dbo].[Transaction] t On t.[TransactionId] = tInOut.[TransactionId] WHERE tInOut.[AddressId] = @addressId ORDER BY t.[Timestamp], tInOut.[TransactionInputOutputType] DESC"; using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync()) { // The amount negative is the reversed because we are // working backwards and substracting from the current balance var dbBalances = (await sqlCon.QueryAsync(sqlQ, new { addressId, fromDate = DateTime.Now.AddDays(-days) })) .Select(x => new { ((DateTime)x.Timestamp).Date, Balance = (double)((int)x.TransactionInputOutputType == (int)TransactionInputOutputType.Input ? x.Amount : -x.Amount) }).ToList(); var allBalances = dbBalances .GroupBy(x => x.Date) .Select(x => new AddressBalanceDto { Date = x.Key, Balance = x.Sum(y => y.Balance) }).ToList(); var currentBalance = await _nexusDb .AddressAggregates .Where(x => x.AddressId == addressId) .Select(x => x.Balance) .FirstOrDefaultAsync(); currentBalance = Math.Round(currentBalance, 8); var finalBalances = new List <AddressBalanceDto> { new AddressBalanceDto { Date = DateTime.Now.Date, Balance = Math.Abs(currentBalance) } }; for (var i = 1; i < days; i++) { var date = DateTime.Now.AddDays(-i).Date; var balance = allBalances.FirstOrDefault(x => x.Date == finalBalances.Last().Date); if (balance != null) { currentBalance = Math.Round(currentBalance + balance.Balance, 8); } finalBalances.Add(new AddressBalanceDto { Date = date, Balance = currentBalance }); } return(finalBalances .OrderBy(x => x.Date) .ToList()); } }
public async Task <FilterResult <AddressLiteDto> > GetAddressLitesFilteredAsync(AddressFilterCriteria filter, int start, int count, bool countResults, int?maxResults = null) { var min = filter.MinBalance ?? 0; var max = filter.MaxBalance ?? double.MaxValue; var fromHeight = filter.HeightFrom ?? 0; var toHeight = filter.HeightTo ?? int.MaxValue; var isNxs = filter.IsNexus; var isStk = filter.IsStaking; var trustAddresses = await _redisCommand.GetAsync <List <AddressLiteDto> >(Settings.Redis.TrustKeyAddressCache); var nexusAddresses = await _redisCommand.GetAsync <List <AddressLiteDto> >(Settings.Redis.NexusAddressCache); if (isNxs || isStk) { if ((isNxs && (nexusAddresses == null || !nexusAddresses.Any()) || (isStk && (trustAddresses == null || !trustAddresses.Any())))) { return new FilterResult <AddressLiteDto> { ResultCount = 0, Results = new List <AddressLiteDto>() } } ; var adds = (!isNxs || !isStk) ? isNxs ? nexusAddresses : trustAddresses : trustAddresses.Where(x => nexusAddresses.Any(y => y.Hash == x.Hash)); var filtered = adds.Where(x => x.Balance >= min && x.Balance <= max && x.LastBlockSeen >= fromHeight && x.LastBlockSeen <= toHeight); var ordered = OrderAddresses(filtered, filter.OrderBy).ToList(); var result = new FilterResult <AddressLiteDto> { ResultCount = ordered.Count, Results = ordered.Skip(start).Take(count).ToList() }; return(result); } var sqlOrderBy = "ORDER BY"; switch (filter.OrderBy) { case OrderAddressesBy.LowestBalance: sqlOrderBy += " aa.[Balance]"; break; case OrderAddressesBy.MostRecentlyActive: sqlOrderBy += " LastBlockSeen DESC"; break; case OrderAddressesBy.LeastRecentlyActive: sqlOrderBy += " LastBlockSeen"; break; default: sqlOrderBy += " aa.[Balance] DESC"; break; } var sqlQ = $@"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT a.[AddressId], a.[Hash], a.[FirstBlockHeight] AS FirstBlockSeen, aa.[LastBlockHeight] AS LastBlockSeen, aa.[Balance] FROM [dbo].[Address] a LEFT JOIN [dbo].[AddressAggregate] aa ON aa.[AddressId] = a.[AddressId] WHERE aa.[Balance] >= @min AND aa.[Balance] <= @max AND aa.[LastBlockHeight] >= @fromHeight AND aa.[LastBlockHeight] <= @toHeight {sqlOrderBy} OFFSET @start ROWS FETCH NEXT @count ROWS ONLY;"; var sqlC = @"SELECT COUNT(*) FROM (SELECT TOP (@maxResults) 1 AS Cnt FROM [dbo].[Address] a LEFT JOIN [dbo].[AddressAggregate] aa ON aa.[AddressId] = a.[AddressId] WHERE aa.[Balance] >= @min AND aa.[Balance] <= @max AND aa.[LastBlockHeight] >= @fromHeight AND aa.[LastBlockHeight] <= @toHeight) AS resultCount;"; using (var sqlCon = await DbConnectionFactory.GetNexusDbConnectionAsync()) { var results = new FilterResult <AddressLiteDto>(); var param = new { min, max, fromHeight, toHeight, start, count, maxResults = maxResults ?? int.MaxValue }; if (countResults) { using (var multi = await sqlCon.QueryMultipleAsync(string.Concat(sqlQ, sqlC), param)) { results.Results = (await multi.ReadAsync <AddressLiteDto>()).ToList(); results.ResultCount = (int)(await multi.ReadAsync <int>()).FirstOrDefault(); } } else { results.Results = (await sqlCon.QueryAsync <AddressLiteDto>(sqlQ, param)).ToList(); results.ResultCount = -1; } foreach (var address in results.Results) { address.InterestRate = trustAddresses?.FirstOrDefault(x => x.Hash == address.Hash)?.InterestRate; address.IsNexus = nexusAddresses?.Any(x => x.Hash == address.Hash) ?? false; } return(results); } }