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