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);
}
}
