private QueryBuilder GetClientRevenueCountQuery(ClientRevenueQueryOptions options) { var builder = new QueryBuilder(); builder.Append($@" SELECT Distinct(Count(m.Id) OVER ()) FROM clt_Client m JOIN clt_Policy p ON m.Id = p.ClientId JOIN com_Commission c ON p.Id = c.PolicyId JOIN com_CommissionType ct ON c.CommissionTypeId = ct.id JOIN com_CommissionStatement cs ON c.CommissionStatementId = cs.Id"); builder = AddUserJoin(builder, options); builder.Append($@" WHERE m.OrganisationId = '{options.Scope.OrganisationId}' AND m.IsDeleted = 0"); builder = AddBranchFilter(builder, options); builder = AddUserFilter(builder, options); builder = AddPolicyTypeFilter(builder, options); builder = AddFilters(builder, options); builder.Append($@"GROUP BY m.Id"); return(builder); }
private QueryBuilder AddFilters(QueryBuilder builder, ClientRevenueQueryOptions options, bool addSqlParameters = true) { builder.Append("AND cs.Date > @StartDate"); if (addSqlParameters) { builder.SqlParameters.Add(new SqlParameter("@StartDate", options.StartDate)); } builder.Append("AND cs.Date <= @EndDate"); if (addSqlParameters) { builder.SqlParameters.Add(new SqlParameter("@EndDate", options.EndDate)); } if (!string.IsNullOrEmpty(options.ClientLastName)) { builder.Append($"AND m.LastName LIKE @ClientLastName"); if (addSqlParameters) { builder.SqlParameters.Add(new SqlParameter("@ClientLastName", options.ClientLastName)); } } return(builder); }
private QueryBuilder AddUserJoin(QueryBuilder builder, ClientRevenueQueryOptions options) { if (options.BranchId.Any()) { builder.Append(@" JOIN idn_User u ON c.UserId = u.Id "); } return(builder); }
public async Task <IActionResult> GetClientRevenueData(string sortColumn, string sortDirection, int pageSize = 0, int pageNumber = 0, string filters = null) { var scope = AuthenticationService.GetScope(User); var queryOptions = new ClientRevenueQueryOptions(scope, sortColumn, sortDirection, pageSize, pageNumber, filters); var data = await CommissionReportService.GetClientRevenueData(queryOptions); return(Ok(data)); }
public async Task GetClientRevenueData() { var data = new ClientRevenueData() { RowNumber = 1, ClientId = Guid.NewGuid(), ClientLastName = "van Niekerk", ClientInitials = "DJ", ClientDateOfBirth = DateTime.Now, MonthlyAnnuityMonth = 2, AnnualAnnuityAverage = 3, TotalMonthlyEarnings = 4, OnceOff = 5, LifeFirstYears = 6, GrandTotal = 7, AllocationsCount = 8 }; var pagedItems = new PagedItems <ClientRevenueData>() { TotalItems = 1, Items = new List <ClientRevenueData>() { data } }; var service = new Mock <ICommissionReportService>(); var authService = TestHelper.MockAuthenticationService(Scope.Branch); ClientRevenueQueryOptions queryOptions = null; service.Setup(c => c.GetClientRevenueData(It.IsAny <ClientRevenueQueryOptions>())) .Callback((ClientRevenueQueryOptions options) => queryOptions = options) .ReturnsAsync(pagedItems); var controller = new CommissionReportsController(service.Object, authService.Object); var result = await controller.GetClientRevenueData("MonthlyAnnuityMonth", "desc", 15, 2, $"yearEnding=2019;monthEnding=1"); Assert.Equal(Scope.Branch, queryOptions.Scope.Scope); Assert.Equal("MonthlyAnnuityMonth", queryOptions.SortOptions.Column); Assert.Equal(SortDirection.Descending, queryOptions.SortOptions.Direction); Assert.Equal(15, queryOptions.PageOptions.Size); Assert.Equal(2, queryOptions.PageOptions.Number); Assert.Equal(2019, queryOptions.YearEnding); Assert.Equal(1, queryOptions.MonthEnding); var okResult = Assert.IsType <OkObjectResult>(result); var returnValue = Assert.IsType <PagedItems <ClientRevenueData> >(okResult.Value); Assert.Same(pagedItems, returnValue); }
public async Task <PagedItems <ClientRevenueData> > GetClientRevenueData(ClientRevenueQueryOptions options) { //Apply scope ------------------------------------------------------------ if (options.Scope.Scope == Scope.User) { options.UserId = new List <Guid>() { options.Scope.UserId } } ; if (options.Scope.Scope == Scope.Branch) { options.BranchId = new List <Guid>() { options.Scope.BranchId } } ; //------------------------------------------------------------------------- var orderbyClause = "ORDER BY MonthlyAnnuityMonth DESC"; if (!string.IsNullOrEmpty(options.SortOptions.Column)) { var direction = options.SortOptions.Direction == SortDirection.Ascending ? "ASC" : "DESC"; orderbyClause = $"ORDER BY {options.SortOptions.Column} {direction}"; } var pagingClause = ""; if (options.PageOptions.Size > 0) { var start = (options.PageOptions.Size * (options.PageOptions.Number - 1)) + 1; pagingClause = $"WHERE RowNumber BETWEEN {start} AND {start + options.PageOptions.Size - 1}"; } var pagedItems = new PagedItems <ClientRevenueData>(); var builder = GetClientRevenueCountQuery(options); pagedItems.TotalItems = (await _context.FromSqlAsync <int>(builder.Query, builder.SqlParameters)).FirstOrDefault(); builder = GetClientRevenueQuery(options, "*", orderbyClause, pagingClause); pagedItems.Items = await _context.FromSqlAsync <ClientRevenueData>(builder.Query, builder.SqlParameters); return(pagedItems); }
private QueryBuilder AddPolicyTypeFilter(QueryBuilder builder, ClientRevenueQueryOptions options, bool addSqlParameters = true) { if (options.PolicyTypeId.Any()) { var parameters = new List <SqlParameter>(); var index = 0; options.PolicyTypeId.ForEach(u => { parameters.Add(new SqlParameter($"@PolicyType{index}", u.ToString())); index++; }); builder.Append($@" AND ct.PolicyTypeId IN ({String.Join(',', parameters.Select(p => p.ParameterName))})"); if (addSqlParameters) { builder.SqlParameters.AddRange(parameters); } } return(builder); }
public async Task GetClientRevenueData_PolicyTypeFilter() { var options = await CreateDatabaseSqlServer(); var company = TestHelper.InsertCompany(options); var commissionType1 = TestHelper.InsertCommissionType(options, PolicyType.POLICY_TYPE_INVESTMENT, CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY); var commissionType2 = TestHelper.InsertCommissionType(options, PolicyType.POLICY_TYPE_LIFE_INSURANCE, CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY); var user1 = TestHelper.InsertUserDetailed(options); var user2 = TestHelper.InsertUserDetailed(options, user1.Organisation); //Same org different branch var client1 = TestHelper.InsertClient(options, user1.Organisation); var thisMonth = DateTime.Now.Date; var cs1 = new CommissionStatementEntity { Id = Guid.NewGuid(), CompanyId = company.Id, AmountIncludingVAT = 0, VAT = 0, Date = thisMonth, Processed = true, OrganisationId = user1.Organisation.Id }; //------------------------------------------------------------------------ var policy1 = new PolicyEntity { Id = Guid.NewGuid(), Number = Guid.NewGuid().ToString(), CompanyId = company.Id, ClientId = client1.Client.Id, UserId = user1.User.Id }; var commission1 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType1.Id, AmountIncludingVAT = 100, VAT = 0, CommissionStatementId = cs1.Id }; //------------------------------------------------------------------------ var policy2 = new PolicyEntity { Id = Guid.NewGuid(), Number = Guid.NewGuid().ToString(), CompanyId = company.Id, ClientId = client1.Client.Id, UserId = user2.User.Id }; var commission2 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy2.Id, UserId = policy2.UserId, CommissionTypeId = commissionType2.Id, AmountIncludingVAT = 200, VAT = 0, CommissionStatementId = cs1.Id }; //------------------------------------------------------------------------ using (var context = new DataContext(options)) { context.Policy.Add(policy2); context.Policy.Add(policy1); context.CommissionStatement.Add(cs1); context.Commission.Add(commission1); context.Commission.Add(commission2); context.SaveChanges(); } using (var context = new DataContext(options)) { var service = new CommissionReportService(context); //When - Branch Filter var scope = TestHelper.GetScopeOptions(user1); var queryOptions = new ClientRevenueQueryOptions(scope, "", "", 0, 0, $"YearEnding={thisMonth.Year};MonthEnding={thisMonth.Month}"); queryOptions.PolicyTypeId.Add(commissionType2.PolicyTypeId); var data = await service.GetClientRevenueData(queryOptions); //Then var results = data.Items.ToList(); Assert.Equal(1, data.TotalItems); var resultsCount = results.Count(); Assert.Equal(1, resultsCount); var actual = results[0]; Assert.Equal(client1.Client.Id, actual.ClientId); Assert.Equal(200, actual.MonthlyAnnuityMonth); } }
public async Task GetClientRevenueData_Allocations() { var options = await CreateDatabaseSqlServer(); var company = TestHelper.InsertCompany(options); var commissionType1 = TestHelper.InsertCommissionType(options, PolicyType.POLICY_TYPE_INVESTMENT, CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY); var user1 = TestHelper.InsertUserDetailed(options); var client1 = TestHelper.InsertClient(options, user1.Organisation); var client2 = TestHelper.InsertClient(options, user1.Organisation); var client3 = TestHelper.InsertClient(options, user1.Organisation); var thisMonth = DateTime.Now.Date; var cs1 = new CommissionStatementEntity { Id = Guid.NewGuid(), CompanyId = company.Id, AmountIncludingVAT = 0, VAT = 0, Date = thisMonth, Processed = true, OrganisationId = user1.Organisation.Id }; //------------------------------------------------------------------------ var policy1 = new PolicyEntity { Id = Guid.NewGuid(), Number = Guid.NewGuid().ToString(), CompanyId = company.Id, ClientId = client1.Client.Id, UserId = user1.User.Id }; var commission1 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType1.Id, AmountIncludingVAT = 100, VAT = 0, CommissionStatementId = cs1.Id }; //------------------------------------------------------------------------ var policy2 = new PolicyEntity { Id = Guid.NewGuid(), Number = Guid.NewGuid().ToString(), CompanyId = company.Id, ClientId = client2.Client.Id, UserId = user1.User.Id }; var commission2 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy2.Id, UserId = policy2.UserId, CommissionTypeId = commissionType1.Id, AmountIncludingVAT = 200, VAT = 0, CommissionStatementId = cs1.Id }; //------------------------------------------------------------------------ var policy3a = new PolicyEntity { Id = Guid.NewGuid(), Number = Guid.NewGuid().ToString(), CompanyId = company.Id, ClientId = client3.Client.Id, UserId = user1.User.Id }; var commission3a = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy3a.Id, UserId = policy3a.UserId, CommissionTypeId = commissionType1.Id, AmountIncludingVAT = 300, VAT = 0, CommissionStatementId = cs1.Id }; var policy3b = new PolicyEntity { Id = Guid.NewGuid(), Number = Guid.NewGuid().ToString(), CompanyId = company.Id, ClientId = client3.Client.Id, UserId = user1.User.Id }; var commission3b = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy3b.Id, UserId = policy3b.UserId, CommissionTypeId = commissionType1.Id, AmountIncludingVAT = 400, VAT = 0, CommissionStatementId = cs1.Id }; var policy3c = new PolicyEntity { Id = Guid.NewGuid(), Number = Guid.NewGuid().ToString(), CompanyId = company.Id, ClientId = client3.Client.Id, UserId = user1.User.Id }; var commission3c = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy3c.Id, UserId = policy3c.UserId, CommissionTypeId = commissionType1.Id, AmountIncludingVAT = 500, VAT = 0, CommissionStatementId = cs1.Id }; //------------------------------------------------------------------------ //Allocate Client1, Policy1 to Client2. var allocation1 = new CommissionAllocationEntity { Id = Guid.NewGuid(), FromClientId = client1.Client.Id, ToClientId = client2.Client.Id }; var cap1 = new CommissionAllocationPolicyEntity { Id = Guid.NewGuid(), CommissionAllocationId = allocation1.Id, PolicyId = policy1.Id }; //Allocate Client3, Policy3b to Client2. var allocation2 = new CommissionAllocationEntity { Id = Guid.NewGuid(), FromClientId = client3.Client.Id, ToClientId = client2.Client.Id }; var cap2 = new CommissionAllocationPolicyEntity { Id = Guid.NewGuid(), CommissionAllocationId = allocation2.Id, PolicyId = policy3b.Id }; using (var context = new DataContext(options)) { context.Policy.Add(policy2); context.Policy.Add(policy1); context.Policy.Add(policy3a); context.Policy.Add(policy3b); context.Policy.Add(policy3c); context.CommissionStatement.Add(cs1); context.Commission.Add(commission3a); context.Commission.Add(commission1); context.Commission.Add(commission2); context.Commission.Add(commission3b); context.Commission.Add(commission3c); context.CommissionAllocation.Add(allocation1); context.CommissionAllocation.Add(allocation2); context.CommissionAllocationPolicy.Add(cap1); context.CommissionAllocationPolicy.Add(cap2); context.SaveChanges(); } using (var context = new DataContext(options)) { var service = new CommissionReportService(context); //When var scope = TestHelper.GetScopeOptions(user1); var queryOptions = new ClientRevenueQueryOptions(scope, "MonthlyAnnuityMonth", "asc", 0, 0, $"YearEnding={thisMonth.Year};MonthEnding={thisMonth.Month}"); var data = await service.GetClientRevenueData(queryOptions); //Then var results = data.Items.ToList(); Assert.Equal(3, data.TotalItems); var resultsCount = results.Count(); Assert.Equal(3, resultsCount); var actual = results[0]; Assert.Equal(client1.Client.Id, actual.ClientId); Assert.Equal(100, actual.MonthlyAnnuityMonth); Assert.Equal(0, actual.AllocationsCount); actual = results[1]; Assert.Equal(client2.Client.Id, actual.ClientId); Assert.Equal(700, actual.MonthlyAnnuityMonth); //200 + 100 (pol1) + 400 (pol3b) Assert.Equal(2, actual.AllocationsCount); actual = results[2]; Assert.Equal(client3.Client.Id, actual.ClientId); Assert.Equal(1200, actual.MonthlyAnnuityMonth); //300 + 400 + 500 Assert.Equal(0, actual.AllocationsCount); } }
public async Task GetClientRevenueData() { var options = await CreateDatabaseSqlServer(); var company = TestHelper.InsertCompany(options); var commissionType1 = TestHelper.InsertCommissionType(options, PolicyType.POLICY_TYPE_INVESTMENT, CommissionEarningsType.EARNINGS_TYPE_ANNUAL_ANNUITY); var commissionType2 = TestHelper.InsertCommissionType(options, PolicyType.POLICY_TYPE_INVESTMENT, CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY); var commissionType3 = TestHelper.InsertCommissionType(options, PolicyType.POLICY_TYPE_INVESTMENT, CommissionEarningsType.EARNINGS_TYPE_ONCE_OFF); var commissionType4 = TestHelper.InsertCommissionType(options, PolicyType.POLICY_TYPE_INVESTMENT, CommissionEarningsType.EARNINGS_TYPE_LIFE_FIRST_YEARS); var user1 = TestHelper.InsertUserDetailed(options); var client1 = TestHelper.InsertClient(options, user1.Organisation); var thisMonth = DateTime.Now.Date; var lastMonth = thisMonth.AddMonths(-1); var policy1 = new PolicyEntity { Id = Guid.NewGuid(), Number = Guid.NewGuid().ToString(), CompanyId = company.Id, ClientId = client1.Client.Id, UserId = user1.User.Id }; //Statement 1 - this month // ANNUAL_ANNUITY | MONTHLY_ANNUITY | ONCE_OFF | LIFE_FIRST_YEARS //com1 100 0 0 0 //com2 0 200 0 0 //com3 0 0 300 0 //com4 0 0 0 400 var cs1 = new CommissionStatementEntity { Id = Guid.NewGuid(), CompanyId = company.Id, AmountIncludingVAT = 0, VAT = 0, Date = thisMonth, Processed = true, OrganisationId = user1.Organisation.Id }; var commission1 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType1.Id, AmountIncludingVAT = 110, VAT = 10, CommissionStatementId = cs1.Id }; var commission2 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType2.Id, AmountIncludingVAT = 220, VAT = 20, CommissionStatementId = cs1.Id }; var commission3 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType3.Id, AmountIncludingVAT = 330, VAT = 30, CommissionStatementId = cs1.Id }; var commission4 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType4.Id, AmountIncludingVAT = 440, VAT = 40, CommissionStatementId = cs1.Id }; //Statement 2 - last month // ANNUAL_ANNUITY | MONTHLY_ANNUITY | ONCE_OFF | LIFE_FIRST_YEARS //com5 500 0 0 0 //com6 0 600 0 0 //com7 0 0 700 0 //com8 0 0 0 800 var cs2 = new CommissionStatementEntity { Id = Guid.NewGuid(), CompanyId = company.Id, AmountIncludingVAT = 0, VAT = 0, Date = lastMonth, Processed = true, OrganisationId = user1.Organisation.Id }; var commission5 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType1.Id, AmountIncludingVAT = 500, VAT = 0, CommissionStatementId = cs2.Id }; var commission6 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType2.Id, AmountIncludingVAT = 600, VAT = 0, CommissionStatementId = cs2.Id }; var commission7 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType3.Id, AmountIncludingVAT = 700, VAT = 0, CommissionStatementId = cs2.Id }; var commission8 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType4.Id, AmountIncludingVAT = 800, VAT = 0, CommissionStatementId = cs2.Id }; //Statement 3 - 2 months age // ANNUAL_ANNUITY | MONTHLY_ANNUITY | ONCE_OFF | LIFE_FIRST_YEARS //com9 900 0 0 0 //com10 0 1000 0 0 //com11 0 0 1100 0 //com12 0 0 0 1200 var cs3 = new CommissionStatementEntity { Id = Guid.NewGuid(), CompanyId = company.Id, AmountIncludingVAT = 0, VAT = 0, Date = lastMonth.AddMonths(-1), Processed = true, OrganisationId = user1.Organisation.Id }; var commission9 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType1.Id, AmountIncludingVAT = 900, VAT = 0, CommissionStatementId = cs3.Id }; var commission10 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType2.Id, AmountIncludingVAT = 1000, VAT = 0, CommissionStatementId = cs3.Id }; var commission11 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType3.Id, AmountIncludingVAT = 1100, VAT = 0, CommissionStatementId = cs3.Id }; var commission12 = new CommissionEntity { Id = Guid.NewGuid(), PolicyId = policy1.Id, UserId = policy1.UserId, CommissionTypeId = commissionType4.Id, AmountIncludingVAT = 1200, VAT = 0, CommissionStatementId = cs3.Id }; using (var context = new DataContext(options)) { context.Policy.Add(policy1); context.CommissionStatement.Add(cs1); context.Commission.Add(commission1); context.Commission.Add(commission2); context.Commission.Add(commission3); context.Commission.Add(commission4); context.CommissionStatement.Add(cs2); context.Commission.Add(commission5); context.Commission.Add(commission6); context.Commission.Add(commission7); context.Commission.Add(commission8); context.CommissionStatement.Add(cs3); context.Commission.Add(commission9); context.Commission.Add(commission10); context.Commission.Add(commission11); context.Commission.Add(commission12); context.SaveChanges(); } using (var context = new DataContext(options)) { var service = new CommissionReportService(context); //When var scope = TestHelper.GetScopeOptions(user1); var queryOptions = new ClientRevenueQueryOptions(scope, "", "", 0, 0, $"YearEnding={thisMonth.Year};MonthEnding={thisMonth.Month}"); var data = await service.GetClientRevenueData(queryOptions); //Then var results = data.Items.ToList(); Assert.Equal(1, data.TotalItems); Assert.Single(results); var actual = results[0]; Assert.Equal(client1.Client.Id, actual.ClientId); Assert.Equal(client1.Client.LastName, actual.ClientLastName); Assert.Equal(client1.Client.Initials, actual.ClientInitials); Assert.Equal(client1.Client.DateOfBirth, actual.ClientDateOfBirth); Assert.Equal(200, actual.MonthlyAnnuityMonth); // com2 Assert.Equal(125, actual.AnnualAnnuityAverage); // (100 + 500 + 900) / 12 Assert.Equal(325, actual.TotalMonthlyEarnings); // 125 + 200 Assert.Equal(2100, actual.OnceOff); //300 + 700 + 1100 Assert.Equal(2400, actual.LifeFirstYears); //400 + 800 + 1200 Assert.Equal(7800, actual.GrandTotal); // add up all commission entries Assert.Equal(0, actual.AllocationsCount); } }
private QueryBuilder GetClientRevenueQuery(ClientRevenueQueryOptions options, string selectClause, string orderbyClause, string pagingClause) { var builder = new QueryBuilder(); var select = $@" m.Id AS 'ClientId', m.LastName AS 'ClientLastName', m.Initials AS 'ClientInitials', m.DateOfBirth AS 'ClientDateOfBirth', SUM(CASE WHEN (cs.DateMonth = {options.EndDate.Month} AND cs.DateYear = {options.EndDate.Year} AND ct.CommissionEarningsTypeId = '{CommissionEarningsType.EARNINGS_TYPE_MONTHLY_ANNUITY}') THEN (c.AmountIncludingVAT - c.VAT) ELSE 0 END) AS 'MonthlyAnnuityMonth', SUM(CASE WHEN ct.CommissionEarningsTypeId = '{CommissionEarningsType.EARNINGS_TYPE_ANNUAL_ANNUITY}' THEN (c.AmountIncludingVAT - c.VAT) ELSE 0 END) AS 'AnnualAnnuity', SUM(CASE WHEN ct.CommissionEarningsTypeId = '{CommissionEarningsType.EARNINGS_TYPE_ONCE_OFF}' THEN (c.AmountIncludingVAT - c.VAT) ELSE 0 END) AS 'OnceOff', SUM(CASE WHEN ct.CommissionEarningsTypeId = '{CommissionEarningsType.EARNINGS_TYPE_LIFE_FIRST_YEARS}' THEN (c.AmountIncludingVAT - c.VAT) ELSE 0 END) AS 'LifeFirstYears', SUM(c.AmountIncludingVAT - c.VAT) AS 'GrandTotal' "; builder.Append($@" DROP TABLE IF EXISTS #CommissionQueryTemp CREATE TABLE #CommissionQueryTemp ( ClientId UNIQUEIDENTIFIER, ClientLastName NVARCHAR(max), ClientInitials NVARCHAR(max), ClientDateOfBirth DATETIME2(7), MonthlyAnnuityMonth MONEY, AnnualAnnuity MONEY, OnceOff MONEY, LifeFirstYears MONEY, GrandTotal MONEY ) INSERT INTO #CommissionQueryTemp SELECT {select} FROM clt_Client m JOIN clt_Policy p ON p.ClientId = m.Id JOIN com_commission c ON c.PolicyId = p.Id JOIN com_CommissionType ct ON c.CommissionTypeId = ct.id JOIN com_CommissionStatement cs ON c.CommissionStatementId = cs.Id"); builder = AddUserJoin(builder, options); builder.Append($@" WHERE m.OrganisationId = '{options.Scope.OrganisationId}' AND m.IsDeleted = 0"); builder = AddBranchFilter(builder, options); builder = AddUserFilter(builder, options); builder = AddPolicyTypeFilter(builder, options); builder = AddFilters(builder, options); builder.Append($@" GROUP BY m.Id, m.LastName, m.Initials, m.DateOfBirth; INSERT INTO #CommissionQueryTemp SELECT {select} FROM clt_Client m JOIN com_CommissionAllocation ca on m.Id = ca.ToClientId JOIN com_CommissionAllocationPolicy cap on ca.Id = cap.CommissionAllocationId JOIN clt_Policy p ON p.Id = cap.PolicyId JOIN com_commission c ON c.PolicyId = p.Id JOIN com_CommissionType ct ON c.CommissionTypeId = ct.id JOIN com_CommissionStatement cs ON c.CommissionStatementId = cs.Id"); builder = AddUserJoin(builder, options); builder.Append($@" WHERE m.OrganisationId = '{options.Scope.OrganisationId}' AND m.IsDeleted = 0"); builder = AddBranchFilter(builder, options, false); builder = AddUserFilter(builder, options, false); builder = AddPolicyTypeFilter(builder, options, false); builder = AddFilters(builder, options, false); builder.Append($@" GROUP BY m.Id, m.LastName, m.Initials, m.DateOfBirth; WITH AllocationCounts AS ( SELECT c.Id AS 'ClientId', COUNT(a.ToClientId) AS 'AllocationCount' FROM clt_Client c LEFT JOIN com_CommissionAllocation a ON c.Id = a.ToClientId WHERE c.OrganisationId = '{options.Scope.OrganisationId}' GROUP BY c.Id ), CommissionQueryTotaled AS ( SELECT ClientId, ClientLastName, ClientInitials, ClientDateOfBirth, MonthlyAnnuityMonth, (AnnualAnnuity / 12) AS 'AnnualAnnuityAverage', ((AnnualAnnuity / 12) + MonthlyAnnuityMonth) AS 'TotalMonthlyEarnings', LifeFirstYears, OnceOff, GrandTotal FROM #CommissionQueryTemp ), CommissionQueryTotalGrouped AS ( SELECT CommissionQueryTotaled.ClientId, ClientLastName, ClientInitials, ClientDateOfBirth, SUM(MonthlyAnnuityMonth) AS 'MonthlyAnnuityMonth', SUM(AnnualAnnuityAverage) AS 'AnnualAnnuityAverage', SUM(TotalMonthlyEarnings) AS 'TotalMonthlyEarnings', SUM(LifeFirstYears) AS 'LifeFirstYears', SUM(OnceOff) AS 'OnceOff', SUM(GrandTotal) AS 'GrandTotal', MAX(AllocationCounts.AllocationCount) AS 'AllocationsCount' FROM CommissionQueryTotaled LEFT JOIN AllocationCounts ON CommissionQueryTotaled.ClientId = AllocationCounts.ClientId GROUP BY CommissionQueryTotaled.ClientId, ClientLastName, ClientInitials, ClientDateOfBirth ), CommissionQueryNumbered AS ( SELECT *, Row_number() OVER({orderbyClause}) AS RowNumber FROM CommissionQueryTotalGrouped ) SELECT {selectClause} FROM CommissionQueryNumbered {pagingClause} "); return(builder); }