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