private static string BuildWhereClause(AddressTransactionFilterCriteria filter, out DynamicParameters param)
{
param = new DynamicParameters();
var whereClause = new StringBuilder();
if (filter.AddressHashes.Any())
{
var addressHashes = filter.AddressHashes;
param.Add(nameof(addressHashes), addressHashes);
whereClause.Append($"AND a.[Hash] IN @addressHashes ");
}
if (filter.TxType.HasValue)
{
var txType = (int)filter.TxType.Value;
param.Add(nameof(txType), txType);
whereClause.Append($"AND t.[TransactionType] = @txType ");
}
if (filter.TxInputOutputType.HasValue)
{
var txInputOutputType = (int)filter.TxInputOutputType.Value;
param.Add(nameof(txInputOutputType), txInputOutputType);
whereClause.Append($"AND tInOut.[TransactionInputOutputType] = @txInputOutputType ");
}
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 <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
});
}
}