public async Task Index()
{
var statement = new CommissionStatement()
{
Id = Guid.NewGuid(),
AmountIncludingVAT = 350000,
VAT = 25000,
ActualAmountIncludingVAT = 300000,
ActualVAT = 20000,
CommissionCount = 1000,
CompanyId = Guid.NewGuid(),
CompanyName = "Comp 1",
Date = DateTime.Now,
MappingErrorCount = 100,
Processed = true
};
var pagedItems = new PagedCommissionStatements()
{
TotalItems = 1,
SumAmountIncludingVAT = 300,
SumVAT = 400,
Items = new List <CommissionStatement>()
{
statement
}
};
var service = new Mock <ICommissionStatementService>();
var authService = TestHelper.MockAuthenticationService(Scope.Branch);
CommissionStatementQueryOptions queryOptions = null;
service.Setup(c => c.GetCommissionStatements(It.IsAny <CommissionStatementQueryOptions>()))
.Callback((CommissionStatementQueryOptions options) => queryOptions = options)
.ReturnsAsync(pagedItems);
var controller = new CommissionStatementsController(service.Object, authService.Object, null);
var result = await controller.Index("date", "desc", 15, 2, $"commissionStatementId={statement.Id}");
Assert.Equal(Scope.Branch, queryOptions.Scope.Scope);
Assert.Equal("date", queryOptions.SortOptions.Column);
Assert.Equal(SortDirection.Descending, queryOptions.SortOptions.Direction);
Assert.Equal(15, queryOptions.PageOptions.Size);
Assert.Equal(2, queryOptions.PageOptions.Number);
Assert.Equal(statement.Id, queryOptions.CommissionStatementId);
var okResult = Assert.IsType <OkObjectResult>(result);
var returnValue = Assert.IsType <PagedCommissionStatements>(okResult.Value);
Assert.Same(pagedItems, returnValue);
}
public async Task <PagedCommissionStatements> GetCommissionStatements(CommissionStatementQueryOptions queryOptions)
{
var organisationQuery = ScopeQuery.GetOrganisationEntityQuery(_context, queryOptions.Scope);
var query = from organisation in organisationQuery
join commissionStatement in _context.CommissionStatement
on organisation.Id equals commissionStatement.OrganisationId
select commissionStatement;
//Apply filters ----------------------------------------------------------------------------------------
if (queryOptions.CommissionStatementId.HasValue)
{
query = query.Where(c => c.Id == queryOptions.CommissionStatementId);
}
if (queryOptions.CompanyId.Any())
{
query = query.Where(c => queryOptions.CompanyId.Contains(c.CompanyId));
}
if (queryOptions.Processed.HasValue)
{
query = query.Where(c => c.Processed == queryOptions.Processed);
}
if (queryOptions.StartDate.HasValue)
{
query = query.Where(c => c.Date >= queryOptions.StartDate.Value.Date);
}
if (queryOptions.EndDate.HasValue)
{
query = query.Where(c => c.Date <= queryOptions.EndDate.Value.Date);
}
if (!string.IsNullOrWhiteSpace(queryOptions.Notes))
{
query = query.Where(m => EF.Functions.Like(m.Notes, queryOptions.Notes));
}
//------------------------------------------------------------------------------------------------------
var pagedItems = new PagedCommissionStatements();
//Get total items
pagedItems.TotalItems = await query.CountAsync();
//Aggregations
var userQuery = ScopeQuery.GetUserEntityQuery(_context, queryOptions.Scope);
var commissionQuery = from user in userQuery
join commission in _context.Commission
on user.Id equals commission.UserId
join commissionStatement in query
on commission.CommissionStatementId equals commissionStatement.Id
select new
{
CommissionStatementId = commission.CommissionStatementId,
AmountIncludingVAT = commission.AmountIncludingVAT,
VAT = commission.VAT,
};
var aggQuery = from commission in commissionQuery
select new
{
SumAmountIncludingVAT = commissionQuery.Select(c => (decimal?)c.AmountIncludingVAT).Sum(),
SumVAT = commissionQuery.Select(c => (decimal?)c.VAT).Sum(),
};
var aggregates = await aggQuery.FirstOrDefaultAsync();
if (aggregates != null)
{
pagedItems.SumAmountIncludingVAT = aggregates.SumAmountIncludingVAT.Value;
pagedItems.SumVAT = aggregates.SumVAT.Value;
}
var modelQuery = from commissionStatement in query
select new CommissionStatement()
{
Id = commissionStatement.Id,
CompanyId = commissionStatement.CompanyId,
AmountIncludingVAT = commissionStatement.AmountIncludingVAT,
VAT = commissionStatement.VAT,
Date = commissionStatement.Date,
Processed = commissionStatement.Processed,
Notes = commissionStatement.Notes,
ActualAmountIncludingVAT = commissionQuery.Where(c => c.CommissionStatementId == commissionStatement.Id).Select(c => c.AmountIncludingVAT).Sum(),
ActualVAT = commissionQuery.Where(c => c.CommissionStatementId == commissionStatement.Id).Select(c => c.VAT).Sum(),
CommissionCount = commissionQuery.Where(c => c.CommissionStatementId == commissionStatement.Id).Count(),
MappingErrorCount = commissionStatement.CommissionErrors.Count(),
CompanyName = commissionStatement.Company.Name
};
//Ordering
modelQuery = modelQuery.OrderBy(queryOptions.SortOptions.Column, queryOptions.SortOptions.Direction);
//Paging
pagedItems.Items = await modelQuery.TakePage(queryOptions.PageOptions.Number, queryOptions.PageOptions.Size).ToListAsync();;
return(pagedItems);
}