public async Task <IActionResult> GetUserEarningsTypeMonthlyCommissionData(string sortColumn = "", string sortDirection = "", int pageSize = 0, int pageNumber = 0, string filters = null)
{
var scope = AuthenticationService.GetScope(User);
var queryOptions = new UserEarningsTypeMonthlyCommissionQueryOptions(scope, sortColumn, sortDirection, pageSize, pageNumber, filters);
var data = await CommissionReportService.GetUserEarningsTypeMonthlyCommissionData(queryOptions);
return(Ok(data));
}
public async Task GetUserEarningsTypeMonthlyCommissionData()
{
var data = new UserEarningsTypeMonthlyCommissionData()
{
AmountExcludingVAT = 100,
CommissionEarningsTypeId = Guid.NewGuid(),
};
var items = new List <UserEarningsTypeMonthlyCommissionData>()
{
data
};
var service = new Mock <ICommissionReportService>();
var authService = TestHelper.MockAuthenticationService(Scope.Branch);
UserEarningsTypeMonthlyCommissionQueryOptions queryOptions = null;
service.Setup(c => c.GetUserEarningsTypeMonthlyCommissionData(It.IsAny <UserEarningsTypeMonthlyCommissionQueryOptions>()))
.Callback((UserEarningsTypeMonthlyCommissionQueryOptions options) => queryOptions = options)
.ReturnsAsync(items);
var controller = new CommissionReportsController(service.Object, authService.Object);
var companyId = Guid.NewGuid();
var result = await controller.GetUserEarningsTypeMonthlyCommissionData("AmountExcludingVAT", "desc", 15, 2, $"companyId=" + companyId.ToString());
Assert.Equal(Scope.Branch, queryOptions.Scope.Scope);
Assert.Equal("AmountExcludingVAT", queryOptions.SortOptions.Column);
Assert.Equal(SortDirection.Descending, queryOptions.SortOptions.Direction);
Assert.Equal(15, queryOptions.PageOptions.Size);
Assert.Equal(2, queryOptions.PageOptions.Number);
Assert.Equal(companyId, queryOptions.CompanyId.Single());
var okResult = Assert.IsType <OkObjectResult>(result);
var returnValue = Assert.IsType <List <UserEarningsTypeMonthlyCommissionData> >(okResult.Value);
Assert.Same(items, returnValue);
}
public async Task <IEnumerable <UserEarningsTypeMonthlyCommissionData> > GetUserEarningsTypeMonthlyCommissionData(UserEarningsTypeMonthlyCommissionQueryOptions queryOptions)
{
var userQuery = ScopeQuery.GetUserEntityQuery(_context, queryOptions.Scope);
var query = from commission in _context.Commission
join statement in _context.CommissionStatement
on commission.CommissionStatementId equals statement.Id
join commissionType in _context.CommissionType
on commission.CommissionTypeId equals commissionType.Id
join policy in _context.Policy
on commission.PolicyId equals policy.Id
join user in userQuery
on commission.UserId equals user.Id
select new
{
UserId = user.Id,
BranchId = user.BranchId,
CompanyId = policy.CompanyId,
Date = statement.Date,
CommissionEarningsTypeId = commissionType.CommissionEarningsTypeId,
AmountIncludingVAT = commission.AmountIncludingVAT,
VAT = commission.VAT
};
//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.BranchId.Any())
{
query = query.Where(d => queryOptions.BranchId.Contains(d.BranchId));
}
if (queryOptions.StartDate.HasValue)
{
query = query.Where(d => queryOptions.StartDate.Value.Date <= d.Date);
}
if (queryOptions.EndDate.HasValue)
{
query = query.Where(d => queryOptions.EndDate.Value.Date >= d.Date);
}
//------------------------------------------------------------------------------------------------------
var groupQuery = from data in query
group new { data.AmountIncludingVAT, data.VAT } by new { data.CommissionEarningsTypeId } into g
select new UserEarningsTypeMonthlyCommissionData()
{
CommissionEarningsTypeId = g.Key.CommissionEarningsTypeId,
AmountExcludingVAT = g.Sum(c => (c.AmountIncludingVAT - c.VAT)),
};
groupQuery = groupQuery.OrderBy(queryOptions.SortOptions.Column, queryOptions.SortOptions.Direction);
return(await groupQuery.ToListAsync());
}
public async Task GetUserEarningsTypeMonthlyCommissionData_UserFilter()
{
var options = TestHelper.GetDbContext("GetUserEarningsTypeMonthlyCommissionData_UserFilter");
TestHelper.InsertCommissionEarningsTypes(options);
var comTypeMonth = TestHelper.InsertCommissionType(options, Guid.NewGuid(), CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY);
var user1 = TestHelper.InsertUserDetailed(options);
var user2 = TestHelper.InsertUserDetailed(options, user1.Organisation);
var client1 = TestHelper.InsertClient(options, user1.Organisation);
var statement1 = TestHelper.InsertCommissionStatement(options, user1.Organisation);
var policy1 = new PolicyEntity
{
Id = Guid.NewGuid(),
Number = Guid.NewGuid().ToString(),
CompanyId = statement1.CompanyId,
ClientId = client1.Client.Id,
UserId = user1.User.Id
};
var policy2 = new PolicyEntity
{
Id = Guid.NewGuid(),
Number = Guid.NewGuid().ToString(),
CompanyId = statement1.CompanyId,
ClientId = client1.Client.Id,
UserId = user2.User.Id
};
//Given
var com1 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = policy1.Id,
UserId = user1.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 110,
VAT = 10,
CommissionStatementId = statement1.Id
};
var com2 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = policy2.Id,
UserId = user2.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 220,
VAT = 20,
CommissionStatementId = statement1.Id
};
using (var context = new DataContext(options))
{
context.Policy.Add(policy1);
context.Policy.Add(policy2);
context.Commission.Add(com1);
context.Commission.Add(com2);
context.SaveChanges();
}
using (var context = new DataContext(options))
{
var service = new CommissionReportService(context);
//When
var scope = TestHelper.GetScopeOptions(user1);
var queryOptions = new UserEarningsTypeMonthlyCommissionQueryOptions(scope, "", "", 0, 0);
queryOptions.UserId.Add(user2.User.Id);
var items = (await service.GetUserEarningsTypeMonthlyCommissionData(queryOptions)).ToList();
//Then
Assert.Single(items);
var actual = items[0];
Assert.Equal(CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY, actual.CommissionEarningsTypeId);
Assert.Equal(200, actual.AmountExcludingVAT);
}
}
public async Task GetUserEarningsTypeMonthlyCommissionData_YearMonthFilter()
{
var options = TestHelper.GetDbContext("GetUserEarningsTypeMonthlyCommissionData_YearMonthFilter");
TestHelper.InsertCommissionEarningsTypes(options);
var comTypeMonth = TestHelper.InsertCommissionType(options, Guid.NewGuid(), CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY);
var user1 = TestHelper.InsertUserDetailed(options);
var client1 = TestHelper.InsertClient(options, user1.Organisation);
var now = DateTime.Now;
var thisMonth = now;
var lastMonth = now.AddMonths(-1);
var lastYear = lastMonth.AddYears(-1);
var statement1 = TestHelper.InsertCommissionStatement(options, user1.Organisation, null, thisMonth);
var statement2 = TestHelper.InsertCommissionStatement(options, user1.Organisation, null, lastMonth);
var statement3 = TestHelper.InsertCommissionStatement(options, user1.Organisation, null, lastYear);
var policy1 = new PolicyEntity
{
Id = Guid.NewGuid(),
Number = Guid.NewGuid().ToString(),
CompanyId = statement1.CompanyId,
ClientId = client1.Client.Id,
UserId = user1.User.Id
};
//Given
var s1_com1 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = policy1.Id,
UserId = user1.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 110,
VAT = 10,
CommissionStatementId = statement1.Id
};
var s2_com1 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = policy1.Id,
UserId = user1.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 220,
VAT = 20,
CommissionStatementId = statement2.Id
};
var s2_com2 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = policy1.Id,
UserId = user1.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 330,
VAT = 30,
CommissionStatementId = statement2.Id
};
var s3_com1 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = policy1.Id,
UserId = user1.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 440,
VAT = 40,
CommissionStatementId = statement3.Id
};
using (var context = new DataContext(options))
{
context.Policy.Add(policy1);
context.Commission.Add(s1_com1);
context.Commission.Add(s2_com1);
context.Commission.Add(s2_com2);
context.Commission.Add(s3_com1);
context.SaveChanges();
}
using (var context = new DataContext(options))
{
var service = new CommissionReportService(context);
//When
var scope = TestHelper.GetScopeOptions(user1);
var queryOptions = new UserEarningsTypeMonthlyCommissionQueryOptions(scope, "", "", 0, 0);
queryOptions.StartDate = new DateTime(lastMonth.Year, lastMonth.Month, 1);
queryOptions.EndDate = lastMonth.AddMonths(1).AddDays(-1);
var items = (await service.GetUserEarningsTypeMonthlyCommissionData(queryOptions)).ToList();
//Then
Assert.Single(items);
var actual = items[0];
Assert.Equal(CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY, actual.CommissionEarningsTypeId);
Assert.Equal(500, actual.AmountExcludingVAT); //200 + 300
}
}
public async Task GetUserEarningsTypeMonthlyCommissionData_Basic()
{
var options = TestHelper.GetDbContext("GetUserEarningsTypeMonthlyCommissionData_Basic");
TestHelper.InsertCommissionEarningsTypes(options);
var comTypeMonth = TestHelper.InsertCommissionType(options, Guid.NewGuid(), CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY);
var comTypeAnnual = TestHelper.InsertCommissionType(options, Guid.NewGuid(), CommissionEarningsType.EARNINGS_TYPE_ANNUAL_ANNUITY);
var user1 = TestHelper.InsertUserDetailed(options);
var user2 = TestHelper.InsertUserDetailed(options, user1.Organisation);
var client1 = TestHelper.InsertClient(options, user1.Organisation);
var client2 = TestHelper.InsertClient(options, user1.Organisation);
var statement1 = TestHelper.InsertCommissionStatement(options, user1.Organisation);
var statement2 = TestHelper.InsertCommissionStatement(options, user1.Organisation);
var user3 = TestHelper.InsertUserDetailed(options);
var client3 = TestHelper.InsertClient(options, user3.Organisation);
var statement3 = TestHelper.InsertCommissionStatement(options, user3.Organisation);
var usr1_policy1 = new PolicyEntity
{
Id = Guid.NewGuid(),
Number = Guid.NewGuid().ToString(),
CompanyId = statement1.CompanyId,
ClientId = client1.Client.Id,
UserId = user1.User.Id
};
var usr2_policy1 = new PolicyEntity
{
Id = Guid.NewGuid(),
CompanyId = statement1.CompanyId,
ClientId = client2.Client.Id,
UserId = user2.User.Id
};
var usr3_policy1 = new PolicyEntity
{
Id = Guid.NewGuid(),
CompanyId = statement3.CompanyId,
ClientId = client3.Client.Id,
UserId = user3.User.Id
};
//Given
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
};
var usr2_policy1_comm1 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = usr2_policy1.Id,
UserId = user2.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 220,
VAT = 20,
CommissionStatementId = statement1.Id
};
var usr2_policy1_comm2 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = usr2_policy1.Id,
UserId = user2.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 330,
VAT = 30,
CommissionStatementId = statement1.Id
};
var usr2_policy1_comm3 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = usr2_policy1.Id,
UserId = user2.User.Id,
CommissionTypeId = comTypeAnnual.Id,
AmountIncludingVAT = 440,
VAT = 40,
CommissionStatementId = statement1.Id
};
var usr3_policy1_comm1 = new CommissionEntity
{
Id = Guid.NewGuid(),
PolicyId = usr3_policy1.Id,
UserId = user3.User.Id,
CommissionTypeId = comTypeMonth.Id,
AmountIncludingVAT = 550,
VAT = 50,
CommissionStatementId = statement3.Id
};
using (var context = new DataContext(options))
{
context.Policy.Add(usr1_policy1);
context.Policy.Add(usr2_policy1);
context.Policy.Add(usr3_policy1);
context.Commission.Add(usr1_policy1_comm1);
context.Commission.Add(usr2_policy1_comm1);
context.Commission.Add(usr2_policy1_comm2);
context.Commission.Add(usr2_policy1_comm3);
context.Commission.Add(usr3_policy1_comm1);
context.SaveChanges();
}
using (var context = new DataContext(options))
{
var service = new CommissionReportService(context);
//When
var scope = TestHelper.GetScopeOptions(user1);
var queryOptions = new UserEarningsTypeMonthlyCommissionQueryOptions(scope, "", "", 0, 0);
var items = (await service.GetUserEarningsTypeMonthlyCommissionData(queryOptions)).ToList();
//Then
Assert.Equal(2, items.Count());
var actual = items[0];
Assert.Equal(CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY, actual.CommissionEarningsTypeId);
Assert.Equal(600, actual.AmountExcludingVAT); //100 + 200 + 300
actual = items[1];
Assert.Equal(CommissionEarningsType.EARNINGS_TYPE_ANNUAL_ANNUITY, actual.CommissionEarningsTypeId);
Assert.Equal(400, actual.AmountExcludingVAT);
//Check scope
scope = TestHelper.GetScopeOptions(user3);
queryOptions = new UserEarningsTypeMonthlyCommissionQueryOptions(scope, "", "", 0, 0);
items = (await service.GetUserEarningsTypeMonthlyCommissionData(queryOptions)).ToList();
Assert.Single(items);
actual = items[0];
Assert.Equal(CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY, actual.CommissionEarningsTypeId);
Assert.Equal(500, actual.AmountExcludingVAT);
}
}
