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