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