public async Task <IActionResult> GetCommissionLapseData(string sortColumn = "", string sortDirection = "", int pageSize = 0, int pageNumber = 0, string filters = null)
{
var scope = AuthenticationService.GetScope(User);
var queryOptions = new CommissionLapseQueryOptions(scope, sortColumn, sortDirection, pageSize, pageNumber, filters);
var data = await CommissionReportService.GetCommissionLapseData(queryOptions);
return(Ok(data));
}
public async Task <PagedItems <CommissionLapseData> > GetCommissionLapseData(CommissionLapseQueryOptions queryOptions)
{
var userQuery = ScopeQuery.GetUserEntityQuery(_context, queryOptions.Scope);
var thisMonth = queryOptions.Date.Month;
var thisYear = queryOptions.Date.Year;
var thisMonthPolicies = from statement in _context.CommissionStatement
join commission in _context.Commission
on statement.Id equals commission.CommissionStatementId
join user in userQuery
on commission.UserId equals user.Id
where statement.DateMonth == thisMonth &&
statement.DateYear == thisYear
select commission.PolicyId;
var lastMonthDate = queryOptions.Date.AddMonths(-1);
var lastMonth = lastMonthDate.Month;
var lastYear = lastMonthDate.Year;
var query = from statement in _context.CommissionStatement
join commission in _context.Commission
on statement.Id equals commission.CommissionStatementId
join company in _context.Company
on statement.CompanyId equals company.Id
join policy in _context.Policy
on commission.PolicyId equals policy.Id
join client in _context.Client
on policy.ClientId equals client.Id
join user in userQuery
on commission.UserId equals user.Id
where statement.DateMonth == lastMonth &&
statement.DateYear == lastYear &&
thisMonthPolicies.Contains(commission.PolicyId) == false
select new CommissionLapseData()
{
PolicyId = policy.Id,
ClientId = policy.ClientId,
Number = policy.Number,
CompanyId = policy.CompanyId,
UserId = policy.UserId,
Premium = policy.Premium,
StartDate = policy.StartDate,
PolicyTypeId = policy.PolicyTypeId,
ClientLastName = client.LastName,
ClientInitials = client.Initials,
IsActive = policy.IsActive,
CompanyName = company.Name,
};
//Apply filters ----------------------------------------------------------------------------------------
if (queryOptions.CompanyId.Any())
{
query = query.Where(d => queryOptions.CompanyId.Contains(d.CompanyId));
}
if (queryOptions.UserId.Any())
{
query = query.Where(d => queryOptions.UserId.Contains(d.UserId));
}
if (queryOptions.PolicyTypeId.Any())
{
query = query.Where(d => queryOptions.PolicyTypeId.Contains(d.PolicyTypeId.Value));
}
if (!string.IsNullOrWhiteSpace(queryOptions.Number))
{
query = query.Where(m => EF.Functions.Like(m.Number, queryOptions.Number));
}
if (!string.IsNullOrWhiteSpace(queryOptions.ClientLastName))
{
query = query.Where(m => EF.Functions.Like(m.ClientLastName, queryOptions.ClientLastName));
}
if (queryOptions.IsActive.HasValue)
{
query = query.Where(m => m.IsActive == queryOptions.IsActive.Value);
}
//------------------------------------------------------------------------------------------------------
query = query.Distinct();
var pagedItems = new PagedItems <CommissionLapseData>();
//Get total items
pagedItems.TotalItems = await query.CountAsync();
//Ordering
query = query.OrderBy(queryOptions.SortOptions.Column, queryOptions.SortOptions.Direction);
//Paging
pagedItems.Items = await query.TakePage(queryOptions.PageOptions.Number, queryOptions.PageOptions.Size).ToListAsync();
return(pagedItems);
}
public async Task GetCommissionLapseData_Basic()
{
var options = TestHelper.GetDbContext("GetCommissionLapseData_Basic");
TestHelper.InsertCommissionEarningsTypes(options);
var comTypeMonth = TestHelper.InsertCommissionType(options, Guid.NewGuid(), CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY);
var company = TestHelper.InsertCompany(options);
var user1 = TestHelper.InsertUserDetailed(options);
var client1 = TestHelper.InsertClient(options, user1.Organisation);
var now = DateTime.UtcNow;
var lastMonth = DateTime.UtcNow.AddMonths(-1);
var twoMonthsAgo = DateTime.UtcNow.AddMonths(-2);
var statement1 = TestHelper.InsertCommissionStatement(options, user1.Organisation, company.Id, now);
var statement2 = TestHelper.InsertCommissionStatement(options, user1.Organisation, company.Id, lastMonth);
var statement4 = TestHelper.InsertCommissionStatement(options, user1.Organisation, company.Id, twoMonthsAgo);
var user2 = TestHelper.InsertUserDetailed(options);
var client2 = TestHelper.InsertClient(options, user2.Organisation);
var statement3 = TestHelper.InsertCommissionStatement(options, user2.Organisation, company.Id, lastMonth);
var usr1_policy1 = new PolicyEntity
{
Id = Guid.NewGuid(),
Number = Guid.NewGuid().ToString(),
CompanyId = company.Id,
ClientId = client1.Client.Id,
UserId = user1.User.Id,
PolicyTypeId = Guid.NewGuid(),
};
var usr1_policy2 = new PolicyEntity
{
Id = Guid.NewGuid(),
Number = Guid.NewGuid().ToString(),
CompanyId = company.Id,
ClientId = client1.Client.Id,
UserId = user1.User.Id,
PolicyTypeId = Guid.NewGuid(),
};
var usr2_policy1 = new PolicyEntity
{
Id = Guid.NewGuid(),
CompanyId = company.Id,
ClientId = client2.Client.Id,
UserId = user2.User.Id,
PolicyTypeId = Guid.NewGuid(),
};
//This month commmission
var usr1_policy1_comm1 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = usr1_policy1.Id,
UserId = user1.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 110,
VAT = 10,
CommissionStatementId = statement1.Id
};
//Last month Commission
var usr1_policy1_comm2 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = usr1_policy1.Id,
UserId = user1.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 220,
VAT = 20,
CommissionStatementId = statement2.Id
};
var usr1_policy2_comm1 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = usr1_policy2.Id,
UserId = user1.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 330,
VAT = 30,
CommissionStatementId = statement2.Id
};
//Two months ago commission
var usr1_policy2_comm2 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = usr1_policy2.Id,
UserId = user1.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 440,
VAT = 40,
CommissionStatementId = statement4.Id
};
//Last month commission - different company
var usr2_policy1_comm1 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = usr2_policy1.Id,
UserId = user2.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 660,
VAT = 60,
CommissionStatementId = statement3.Id
};
using (var context = new DataContext(options))
{
context.Policy.Add(usr1_policy1);
context.Policy.Add(usr1_policy2);
context.Policy.Add(usr2_policy1);
context.Commission.Add(usr1_policy1_comm1);
context.Commission.Add(usr1_policy1_comm2);
context.Commission.Add(usr1_policy2_comm1);
context.Commission.Add(usr1_policy2_comm2);
context.Commission.Add(usr2_policy1_comm1);
context.SaveChanges();
}
using (var context = new DataContext(options))
{
var service = new CommissionReportService(context);
//When
var scope = TestHelper.GetScopeOptions(user1);
var queryOptions = new CommissionLapseQueryOptions(scope, "", "", 0, 0);
var result = await service.GetCommissionLapseData(queryOptions);
//Then
var items = result.Items.ToList();
Assert.Single(items);
var actual = items[0];
Assert.Equal(usr1_policy2.Id, actual.PolicyId);
Assert.Equal(client1.Client.Id, actual.ClientId);
Assert.Equal(usr1_policy2.Number, actual.Number);
Assert.Equal(usr1_policy2.CompanyId, actual.CompanyId);
Assert.Equal(usr1_policy2.UserId, actual.UserId);
Assert.Equal(usr1_policy2.Premium, actual.Premium);
Assert.Equal(usr1_policy2.StartDate, actual.StartDate);
Assert.Equal(usr1_policy2.PolicyTypeId, actual.PolicyTypeId);
Assert.Equal(client1.Client.LastName, actual.ClientLastName);
Assert.Equal(client1.Client.Initials, actual.ClientInitials);
Assert.Equal(company.Name, actual.CompanyName);
//Check scope
scope = TestHelper.GetScopeOptions(user2);
queryOptions = new CommissionLapseQueryOptions(scope, "", "", 0, 0);
result = (await service.GetCommissionLapseData(queryOptions));
items = result.Items.ToList();
Assert.Single(items);
actual = items[0];
Assert.Equal(usr2_policy1.Id, actual.PolicyId);
}
}
