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