public async Task <IActionResult> Index(string sortColumn, string sortDirection, int pageSize = 0, int pageNumber = 0, string filters = null)
{
var scope = AuthenticationService.GetScope(User);
var queryOptions = new CommissionStatementQueryOptions(scope, sortColumn, sortDirection, pageSize, pageNumber, filters);
var pagedItems = await CommissionStatementService.GetCommissionStatements(queryOptions);
return(Ok(pagedItems));
}
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);
}
public async Task <ImportResult> ImportCommissions(ScopeOptions scope, Guid commissionStatementId, IEnumerable <ImportCommission> importData)
{
var importResult = new ImportResult();
CommissionsToInsert = new List <CommissionEntity>();
CommissionErrorsToInsert = new List <CommissionErrorEntity>();
//Scope check
var queryOptions = new CommissionStatementQueryOptions(scope, "", "", 0, 0);
queryOptions.CommissionStatementId = commissionStatementId;
var statements = await _commissionStatementService.GetCommissionStatements(queryOptions);
if (!statements.Items.Any())
{
return(importResult);
}
var statement = statements.Items.Single();
var commissionTypes = await _commissionLookupService.GetCommissionTypes();
var commissionTypesDictionary = BuildCommissionTypesDictionary(commissionTypes);
var company = await _lookupService.GetCompany(statement.CompanyId);
var policyQueryOptions = new PolicyQueryOptions(scope, "", "", 0, 0);
policyQueryOptions.CompanyId.Add(statement.CompanyId);
var policies = (await _policyService.GetPolicies(policyQueryOptions)).Items.ToList();
var policyDictionary = BuildPolicyDictionary(policies, company.CommissionPolicyNumberPrefixes.ToList());
var commissionSplitRulesQueryOptions = new CommissionSplitRuleQueryOptions(scope, "", "", 0, 0);
var commissionSplitRules = (await _commissionSplitService.GetCommissionSplitRules(commissionSplitRulesQueryOptions)).Items.ToList();
var commissionSplitRulePolicyQueryOptions = new CommissionSplitRulePolicyQueryOptions(scope, "", "", 0, 0);
var commissionSplitRulePolicies = (await _commissionSplitRulePolicyService.GetCommissionSplitRulePolicies(commissionSplitRulePolicyQueryOptions)).Items.ToList();
foreach (var data in importData)
{
var result = ImportCommission(scope, statement, data, policyDictionary, commissionTypesDictionary, commissionSplitRules, commissionSplitRulePolicies);
importResult.Results.Add(result);
if (data.CommissionTypeCode == CommissionType.COMMISSION_TYPE_UNKNOWN_CODE)
{
importResult.AddUnknownCommissionTypeValue(data.CommissionTypeValue);
}
}
if (CommissionsToInsert.Any())
{
await _bulkActions.BulkInsertCommissionsAsync(_context, CommissionsToInsert);
}
if (CommissionErrorsToInsert.Any())
{
await _bulkActions.BulkInsertCommissionErrorsAsync(_context, CommissionErrorsToInsert);
}
await _auditService.InsertAuditLog(scope, "Import", "Commission", statement.Id,
new
{
commissionStatementId = commissionStatementId,
importCount = importResult.ImportCount,
errorCount = importResult.ErrorCount,
errors = importResult.Results.Where(r => !r.Success).ToList()
}
);
return(importResult);
}
public async Task GetCommissionStatements_DateFilter()
{
var options = TestHelper.GetDbContext("GetCommissionStatements_DateFilter");
var user1 = TestHelper.InsertUserDetailed(options);
var company = TestHelper.InsertCompany(options);
var cs1 = new CommissionStatementEntity
{
Id = Guid.NewGuid(),
CompanyId = company.Id,
Date = new DateTime(2000, 1, 1),
OrganisationId = user1.Organisation.Id
};
var cs2 = new CommissionStatementEntity
{
Id = Guid.NewGuid(),
CompanyId = company.Id,
Date = new DateTime(2000, 1, 20),
OrganisationId = user1.Organisation.Id
};
var cs3 = new CommissionStatementEntity
{
Id = Guid.NewGuid(),
CompanyId = company.Id,
Date = new DateTime(2000, 1, 31),
OrganisationId = user1.Organisation.Id
};
var cs4 = new CommissionStatementEntity
{
Id = Guid.NewGuid(),
CompanyId = company.Id,
Date = new DateTime(2000, 2, 1),
OrganisationId = user1.Organisation.Id
};
using (var context = new DataContext(options))
{
context.CommissionStatement.Add(cs1);
context.CommissionStatement.Add(cs2);
context.CommissionStatement.Add(cs3);
context.CommissionStatement.Add(cs4);
context.SaveChanges();
}
using (var context = new DataContext(options))
{
var auditService = new AuditServiceMock();
var service = new CommissionStatementService(context, null, auditService);
//When
var scope = TestHelper.GetScopeOptions(user1);
var filters = "startDate=1999-12-01;endDate=1999-12-31";
var queryOptions = new CommissionStatementQueryOptions(scope, "", "", 0, 0, filters);
var statements = await service.GetCommissionStatements(queryOptions);
Assert.Equal(0, statements.TotalItems);
Assert.Empty(statements.Items);
filters = "startDate=2000-01-01;endDate=2000-01-21";
queryOptions = new CommissionStatementQueryOptions(scope, "Date", "desc", 0, 0, filters);
statements = await service.GetCommissionStatements(queryOptions);
//Then
Assert.Equal(2, statements.TotalItems);
var items = statements.Items.ToList();
Assert.Equal(cs2.Id, items[0].Id);
Assert.Equal(cs1.Id, items[1].Id);
filters = "startDate=2000-01-01;endDate=2000-01-31";
queryOptions = new CommissionStatementQueryOptions(scope, "Date", "desc", 0, 0, filters);
statements = await service.GetCommissionStatements(queryOptions);
//Then
Assert.Equal(3, statements.TotalItems);
Assert.Equal(3, statements.Items.Count());
items = statements.Items.ToList();
Assert.Equal(cs3.Id, items[0].Id);
Assert.Equal(cs2.Id, items[1].Id);
Assert.Equal(cs1.Id, items[2].Id);
}
}
public async Task GetCommissionStatements()
{
var options = TestHelper.GetDbContext("GetCommissionStatements");
var user1 = TestHelper.InsertUserDetailed(options);
var user2 = TestHelper.InsertUserDetailed(options);
var company = TestHelper.InsertCompany(options);
var cs1 = new CommissionStatementEntity
{
Id = Guid.NewGuid(),
CompanyId = company.Id,
AmountIncludingVAT = 111,
VAT = 11,
Date = DateTime.Now,
Processed = true,
OrganisationId = user1.Organisation.Id,
Notes = "note 1"
};
var cs2 = new CommissionStatementEntity
{
Id = Guid.NewGuid(),
CompanyId = company.Id,
AmountIncludingVAT = 222,
VAT = 22,
Date = DateTime.Now.AddDays(-1),
Processed = false,
OrganisationId = user1.Organisation.Id,
Notes = "note 2"
};
var cs3 = new CommissionStatementEntity
{
Id = Guid.NewGuid(),
CompanyId = company.Id,
AmountIncludingVAT = 333,
VAT = 33,
Date = DateTime.Now.AddDays(-2),
Processed = false,
OrganisationId = user1.Organisation.Id,
Notes = "note 3"
};
var cs4 = new CommissionStatementEntity
{
Id = Guid.NewGuid(),
CompanyId = company.Id,
AmountIncludingVAT = 444,
VAT = 44,
Date = DateTime.Now.AddDays(-2),
Processed = false,
OrganisationId = user2.Organisation.Id,
Notes = "note 4"
};
var commission1a = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = Guid.NewGuid(),
CommissionTypeId = Guid.NewGuid(),
AmountIncludingVAT = 50,
VAT = 5,
CommissionStatementId = cs1.Id,
UserId = user1.User.Id
};
var commission1b = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = Guid.NewGuid(),
CommissionTypeId = Guid.NewGuid(),
AmountIncludingVAT = 50,
VAT = 5,
CommissionStatementId = cs1.Id,
UserId = user1.User.Id,
};
var commission2 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = Guid.NewGuid(),
CommissionTypeId = Guid.NewGuid(),
AmountIncludingVAT = 200,
VAT = 20,
CommissionStatementId = cs2.Id,
UserId = user1.User.Id,
};
var commission3 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = Guid.NewGuid(),
CommissionTypeId = Guid.NewGuid(),
AmountIncludingVAT = 300,
VAT = 30,
CommissionStatementId = cs3.Id,
UserId = user1.User.Id,
};
var commission4 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = Guid.NewGuid(),
CommissionTypeId = Guid.NewGuid(),
AmountIncludingVAT = 40,
VAT = 400,
CommissionStatementId = cs4.Id,
UserId = user2.User.Id,
};
using (var context = new DataContext(options))
{
context.CommissionStatement.Add(cs1);
context.CommissionStatement.Add(cs2);
context.CommissionStatement.Add(cs3);
context.CommissionStatement.Add(cs4);
context.Commission.Add(commission1a);
context.Commission.Add(commission1b);
context.Commission.Add(commission2);
context.Commission.Add(commission3);
context.Commission.Add(commission4);
context.SaveChanges();
}
using (var context = new DataContext(options))
{
var auditService = new AuditServiceMock();
var service = new CommissionStatementService(context, null, auditService);
//When
var scope = TestHelper.GetScopeOptions(user1);
var queryOptions = new CommissionStatementQueryOptions(scope, "", "", 0, 0);
var statements = await service.GetCommissionStatements(queryOptions);
//Then
Assert.Equal(3, statements.TotalItems);
Assert.Equal(3, statements.Items.Count());
Assert.Equal(600, statements.SumAmountIncludingVAT);
Assert.Equal(60, statements.SumVAT);
var items = statements.Items.ToList();
var actual = items[0];
Assert.Equal(cs1.Id, actual.Id);
Assert.Equal(cs1.CompanyId, actual.CompanyId);
Assert.Equal(cs1.Date, actual.Date);
Assert.Equal(cs1.AmountIncludingVAT, actual.AmountIncludingVAT);
Assert.Equal(cs1.VAT, actual.VAT);
Assert.Equal(cs1.Processed, actual.Processed);
Assert.Equal(cs1.Notes, actual.Notes);
Assert.Equal(cs1.OrganisationId, user1.Organisation.Id);
Assert.Equal(100, actual.ActualAmountIncludingVAT);
Assert.Equal(10, actual.ActualVAT);
Assert.Equal(2, actual.CommissionCount);
actual = items[1];
Assert.Equal(cs2.Id, actual.Id);
actual = items[2];
Assert.Equal(cs3.Id, actual.Id);
//Check scope
scope = TestHelper.GetScopeOptions(user2);
queryOptions = new CommissionStatementQueryOptions(scope, "", "", 0, 0);
statements = await service.GetCommissionStatements(queryOptions);
Assert.Single(statements.Items);
actual = statements.Items.First();
Assert.Equal(cs4.Id, actual.Id);
}
}
