public async Task <PaginatedResponse <AuditModel> > GetAll(AuditLogsFilterDto filter, int?skip, int?take)
{
take = PaginationHelper.GetTake(take);
var sqlFilter = filter.AddSqlLikeWildcards();
var whereClause = "WHERE 1=1 "
+ (!string.IsNullOrWhiteSpace(sqlFilter.UserName)
? " AND LOWER(UserName) like LOWER(@UserName)"
: "")
+ (!string.IsNullOrWhiteSpace(sqlFilter.CorrelationId)
? " AND CorrelationId=@CorrelationId"
: "")
+ (!string.IsNullOrWhiteSpace(sqlFilter.ReferenceId)
? " AND LOWER(DataReference) like LOWER(@ReferenceId)"
: "")
+ (sqlFilter.DataTypes.Any() ? " AND DataType IN @DataTypes" : "")
+ (sqlFilter.ActionType != null ? " AND Type=@ActionType" : "")
+ (sqlFilter.StartDateTime != null ? " AND Timestamp >= @StartDateTime" : "")
+ (sqlFilter.EndDateTime != null ? " AND Timestamp <= @EndDateTime" : "");
var paginationClause = $" ORDER BY [Timestamp] DESC OFFSET {skip ?? 0} ROWS FETCH NEXT {take} ROWS ONLY";
await using var conn = new SqlConnection(_connectionString);
var gridReader = await conn.QueryMultipleAsync(
$"SELECT {GetColumns} FROM MarginTradingAccountsAuditTrail WITH (NOLOCK) {whereClause} {paginationClause}; SELECT COUNT(*) FROM MarginTradingAccountsAuditTrail {whereClause}", sqlFilter);
var contents = (await gridReader.ReadAsync <DbSchema>())
.Select(x => x.ToDomain())
.ToList();
var totalCount = await gridReader.ReadSingleAsync <int>();
return(new PaginatedResponse <AuditModel>(
contents,
skip ?? 0,
contents.Count,
totalCount
));
}
public Task <PaginatedResponse <IAuditModel> > GetAll(AuditLogsFilterDto filter, int?skip, int?take)
{
(skip, take) = ValidateSkipAndTake(skip, take);
return(_auditRepository.GetAll(filter, skip, take));
}
public async Task <PaginatedResponse <IAuditModel> > GetAll(AuditLogsFilterDto filter, int?skip, int?take)
{
await using var context = _contextFactory.CreateDataContext();
var query = context.AuditTrail.AsNoTracking();
if (!string.IsNullOrEmpty(filter.UserName))
{
query = query.Where(x => x.UserName.ToLower().Contains(filter.UserName.ToLower()));
}
if (!string.IsNullOrEmpty(filter.CorrelationId))
{
query = query.Where(x => x.CorrelationId == filter.CorrelationId);
}
if (!string.IsNullOrEmpty(filter.ReferenceId))
{
query = query.Where(x => x.DataReference.ToLower().Contains(filter.ReferenceId.ToLower()));
}
if (filter.DataType.HasValue)
{
query = query.Where(x => x.DataType == filter.DataType.Value);
}
if (filter.ActionType.HasValue)
{
query = query.Where(x => x.Type == filter.ActionType.Value);
}
if (filter.StartDateTime.HasValue)
{
query = query.Where(x => x.Timestamp >= filter.StartDateTime.Value);
}
if (filter.EndDateTime.HasValue)
{
query = query.Where(x => x.Timestamp <= filter.EndDateTime.Value);
}
var total = await query.CountAsync();
query = query.OrderByDescending(x => x.Timestamp);
if (skip.HasValue && take.HasValue)
{
query = query.Skip(skip.Value).Take(take.Value);
}
var contents = await query.ToListAsync();
var result = new PaginatedResponse <IAuditModel>(
contents: contents,
start: skip ?? 0,
size: contents.Count,
totalSize: total
);
return(result);
}
