private static string BuildWhereClause(TransactionFilterCriteria filter, out DynamicParameters param)
{
param = new DynamicParameters();
var whereClause = new StringBuilder();
if (filter.TxType.HasValue)
{
var txType = (int)filter.TxType.Value;
param.Add(nameof(txType), txType);
whereClause.Append($"AND t.[TransactionType] = @txType ");
}
if (filter.MinAmount.HasValue)
{
var min = filter.MinAmount.Value;
param.Add(nameof(min), min);
whereClause.Append($"AND t.[Amount] >= @min ");
}
if (filter.MaxAmount.HasValue)
{
var max = filter.MaxAmount.Value;
param.Add(nameof(max), max);
whereClause.Append($"AND t.[Amount] <= @max ");
}
if (filter.HeightFrom.HasValue)
{
var fromHeight = filter.HeightFrom.Value;
param.Add(nameof(fromHeight), fromHeight);
whereClause.Append($"AND t.[BlockHeight] >= @fromHeight ");
}
if (filter.HeightTo.HasValue)
{
var toHeight = filter.HeightTo.Value;
param.Add(nameof(toHeight), toHeight);
whereClause.Append($"AND t.[BlockHeight] <= @toHeight ");
}
if (filter.UtcFrom.HasValue)
{
var fromDate = filter.UtcFrom.Value;
param.Add(nameof(fromDate), fromDate);
whereClause.Append($"AND t.[Timestamp] >= @fromDate ");
}
if (filter.UtcTo.HasValue)
{
var toDate = filter.UtcTo.Value;
param.Add(nameof(toDate), toDate);
whereClause.Append($"AND t.[Timestamp] <= @toDate ");
}
return(whereClause.ToString());
}
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);
}
}
}
private static IEnumerable <TransactionLiteDto> FilterCacheBlocks(IEnumerable <BlockDto> blocks, TransactionFilterCriteria filter)
{
return(blocks.SelectMany(x => x.Transactions
.Where(y => (!filter.TxType.HasValue || y.TransactionType == filter.TxType) &&
(!filter.MinAmount.HasValue || y.Amount >= filter.MinAmount) &&
(!filter.MaxAmount.HasValue || y.Amount <= filter.MaxAmount) &&
(!filter.HeightFrom.HasValue || y.BlockHeight >= filter.HeightFrom) &&
(!filter.HeightTo.HasValue || y.BlockHeight <= filter.HeightTo) &&
(!filter.UtcFrom.HasValue || y.Timestamp >= filter.UtcFrom) &&
(!filter.UtcTo.HasValue || y.Timestamp <= filter.UtcTo))
.Select(y => new TransactionLiteDto(y))));
}
