private IQueryable <CommissionSplitRulePolicyInfo> GetCommissionSplitRulePolicyInfoQuery(ScopeOptions scope)
{
var userQuery = ScopeQuery.GetUserEntityQuery(_context, scope);
var query = from policy in _context.Policy
join user in userQuery
on policy.UserId equals user.Id
join client in _context.Client
on policy.ClientId equals client.Id
join commissionSplitRulePolicy in _context.CommissionSplitRulePolicy
on policy.Id equals commissionSplitRulePolicy.PolicyId into groupCommissionSplitRulePolicy
from subCommissionSplitRulePolicy in groupCommissionSplitRulePolicy.DefaultIfEmpty()
join commissionSplitRule in _context.CommissionSplitRule
on new { Key1 = policy.UserId, Key2 = true } equals new { Key1 = commissionSplitRule.UserId, Key2 = commissionSplitRule.IsDefault } into groupCommissionSplitRule
from subCommissionSplitRule in groupCommissionSplitRule.DefaultIfEmpty()
select new CommissionSplitRulePolicyInfo()
{
PolicyId = policy.Id,
PolicyUserId = policy.UserId,
PolicyCompanyId = policy.CompanyId,
PolicyNumber = policy.Number,
PolicyClientFirstName = client.FirstName,
PolicyClientLastName = client.LastName,
PolicyClientId = client.Id,
CommissionSplitRuleId = subCommissionSplitRulePolicy.CommissionSplitRuleId,
CommissionSplitRuleName = subCommissionSplitRulePolicy.CommissionSplitRule.Name,
DefaultCommissionSplitRuleId = subCommissionSplitRule.Id,
DefaultCommissionSplitRuleName = subCommissionSplitRule.Name
};
return(query);
}
public async Task <IEnumerable <UserCompanyMonthlyCommissionData> > GetUserCompanyMonthlyCommissionData(UserCompanyMonthlyCommissionQueryOptions 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 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,
Date = statement.Date,
CompanyId = policy.CompanyId,
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.CompanyId } into g
select new UserCompanyMonthlyCommissionData()
{
CompanyId = g.Key.CompanyId,
AmountExcludingVAT = g.Sum(c => (c.AmountIncludingVAT - c.VAT)),
};
groupQuery = groupQuery.OrderBy(queryOptions.SortOptions.Column, queryOptions.SortOptions.Direction);
return(await groupQuery.ToListAsync());
}
private IQueryable <PolicyEntity> GetPolicyEntityQuery(ScopeOptions scope)
{
var userQuery = ScopeQuery.GetUserEntityQuery(_context, scope);
var query = from user in userQuery
join policy in _context.Policy
on user.Id equals policy.UserId
select policy;
return(query);
}
private IQueryable <CommissionSplitRuleEntity> GetCommissionSplitRuleEntityQuery(ScopeOptions scope)
{
var userQuery = ScopeQuery.GetUserEntityQuery(_context, scope);
var query = from commissionSplitRule in _context.CommissionSplitRule
join user in userQuery
on commissionSplitRule.UserId equals user.Id
select commissionSplitRule;
return(query);
}
private IQueryable <CommissionSplitRulePolicyEntity> GetCommissionSplitRulePolicyEntityQuery(ScopeOptions scope)
{
var userQuery = ScopeQuery.GetUserEntityQuery(_context, scope);
var query = from commissionSplitRulePolicy in _context.CommissionSplitRulePolicy
join policy in _context.Policy
on commissionSplitRulePolicy.PolicyId equals policy.Id
join user in userQuery
on policy.UserId equals user.Id
select commissionSplitRulePolicy;
return(query);
}
private IQueryable <UserSimple> GetUserSimpleQuery(ScopeOptions scope)
{
var query = from user in ScopeQuery.GetUserEntityQuery(_context, scope)
select new UserSimple()
{
Id = user.Id,
FirstName = user.FirstName,
LastName = user.LastName,
BranchId = user.BranchId,
UserTypeId = user.UserTypeId,
};
return(query);
}
public async Task <UserEdit> GetUser(ScopeOptions scope, string userName)
{
var query = from entity in ScopeQuery.GetUserEntityQuery(_context, scope)
where entity.UserName == userName
select entity;
var user = await query.FirstOrDefaultAsync();
if (user == null)
{
return(null);
}
return(await LoadUserEditModel(user));
}
private void SourcePolicyIdsInScope(MergePolicies merge, CustomContext context)
{
var userQuery = ScopeQuery.GetUserEntityQuery(_context, _scope);
var policyQuery = from policy in _context.Policy
select policy;
var policies = policyQuery.Where(m => merge.SourcePolicyIds.Contains(m.Id)).ToList();
if (policies.Count != merge.SourcePolicyIds.Count)
{
var failure = new ValidationFailure("SourcePolicyIds", "Invalid Source Policy Ids");
context.AddFailure(failure);
}
}
private IQueryable <Model.Commission.Model.Commission.Commission> GetCommissionsQuery(ScopeOptions scope)
{
var userQuery = ScopeQuery.GetUserEntityQuery(_context, scope);
var query = from commission in _context.Commission
join user in userQuery
on commission.UserId equals user.Id
join policy in _context.Policy
on commission.PolicyId equals policy.Id
select new OneAdvisor.Model.Commission.Model.Commission.Commission()
{
Id = commission.Id,
CommissionStatementId = commission.CommissionStatementId,
PolicyId = commission.PolicyId,
CommissionTypeId = commission.CommissionTypeId,
AmountIncludingVAT = commission.AmountIncludingVAT,
VAT = commission.VAT,
UserId = commission.UserId,
PolicyNumber = policy.Number,
SplitGroupId = commission.SplitGroupId
};
return(query);
}
public async Task <Result> UpdateUser(ScopeOptions scope, UserEdit user)
{
var validator = new UserValidator(_context, scope, false);
var result = validator.Validate(user).GetResult();
if (!result.Success)
{
return(result);
}
var entity = await ScopeQuery.GetUserEntityQuery(_context, scope).FirstOrDefaultAsync(m => m.Id == user.Id);
if (entity == null)
{
return(new Result());
}
var userEntity = MapModelToEntity(user, entity);
var updateResult = await _userManager.UpdateAsync(entity);
result.Success = updateResult.Succeeded;
if (!result.Success)
{
result.ValidationFailures = updateResult.Errors.Select(e => new ValidationFailure("", e.Description)).ToList();
return(result);
}
await UpdateRoles(entity, user.Roles);
await UpdateIsLocked(entity, user.IsLocked);
await _auditService.InsertAuditLog(scope, AuditLog.ACTION_UPDATE, "User", entity.Id, user);
return(result);
}
private bool IsAvailablePolicyNumber(Guid companyId, Guid?policyId, string policyNumber)
{
var query = from user in ScopeQuery.GetUserEntityQuery(_context, _scope.Clone(Scope.Organisation))
join policy in _context.Policy
on user.Id equals policy.UserId
where policy.CompanyId == companyId
select policy;
query = query.WherePolicyNumberEquals(policyNumber);
var entity = query.FirstOrDefault();
if (entity == null)
{
return(true);
}
if (!policyId.HasValue || _isInsert)
{
return(entity == null);
}
return(policyId == entity.Id);
}
public async Task <PagedItems <User> > GetUsers(UserQueryOptions queryOptions)
{
var query = from user in ScopeQuery.GetUserEntityQuery(_context, queryOptions.Scope)
join branch in _context.Branch
on user.BranchId equals branch.Id
join organisation in _context.Organisation
on branch.OrganisationId equals organisation.Id
select new User()
{
Id = user.Id,
FirstName = user.FirstName,
LastName = user.LastName,
Email = user.Email,
UserName = user.UserName,
BranchId = user.BranchId,
BranchName = branch.Name,
OrganisationId = organisation.Id,
OrganisationName = organisation.Name,
Scope = user.Scope,
EmailConfirmed = user.EmailConfirmed,
LockoutEnd = user.LockoutEnd,
UserTypeId = user.UserTypeId,
Config = user.Config,
};
//Apply filters ----------------------------------------------------------------------------------------
if (!string.IsNullOrWhiteSpace(queryOptions.FirstName))
{
query = query.Where(m => EF.Functions.Like(m.FirstName, queryOptions.FirstName));
}
if (!string.IsNullOrWhiteSpace(queryOptions.LastName))
{
query = query.Where(m => EF.Functions.Like(m.LastName, queryOptions.LastName));
}
if (!string.IsNullOrWhiteSpace(queryOptions.Email))
{
query = query.Where(m => EF.Functions.Like(m.Email, queryOptions.Email));
}
if (queryOptions.EmailConfirmed.HasValue)
{
query = query.Where(m => m.EmailConfirmed == queryOptions.EmailConfirmed);
}
if (queryOptions.OrganisationId.Any())
{
query = query.Where(m => queryOptions.OrganisationId.Contains(m.OrganisationId));
}
if (queryOptions.BranchId.Any())
{
query = query.Where(m => queryOptions.BranchId.Contains(m.BranchId));
}
if (queryOptions.UserTypeId.Any())
{
query = query.Where(m => queryOptions.UserTypeId.Contains(m.UserTypeId));
}
//------------------------------------------------------------------------------------------------------
var pagedItems = new PagedItems <User>();
//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 <PagedItems <Policy> > GetPolicies(PolicyQueryOptions queryOptions)
{
var userQuery = ScopeQuery.GetUserEntityQuery(_context, queryOptions.Scope);
var policyQuery = from policy in _context.Policy
select policy;
//Apply filters ----------------------------------------------------------------------------------------
if (!string.IsNullOrWhiteSpace(queryOptions.Number))
{
policyQuery = policyQuery.WherePolicyNumberEquals(queryOptions.Number);
}
//------------------------------------------------------------------------------------------------------
var query = from user in userQuery
join policy in policyQuery
on user.Id equals policy.UserId
join client in _context.Client
on policy.ClientId equals client.Id
join company in _context.Company
on policy.CompanyId equals company.Id
select new Policy()
{
Id = policy.Id,
ClientId = policy.ClientId,
Number = policy.Number,
CompanyId = policy.CompanyId,
UserId = policy.UserId,
Premium = policy.Premium,
StartDate = policy.StartDate,
PolicyTypeId = policy.PolicyTypeId,
PolicyProductTypeId = policy.PolicyProductTypeId,
PolicyProductId = policy.PolicyProductId,
ClientLastName = client.LastName,
ClientInitials = client.Initials,
ClientDateOfBirth = client.DateOfBirth,
IsActive = policy.IsActive,
NumberAliases = policy.NumberAliases,
CompanyName = company.Name,
};
//Apply filters ----------------------------------------------------------------------------------------
if (queryOptions.Id.HasValue)
{
query = query.Where(m => m.Id == queryOptions.Id.Value);
}
if (queryOptions.ClientId.HasValue)
{
query = query.Where(m => m.ClientId == queryOptions.ClientId.Value);
}
if (queryOptions.CompanyId.Any())
{
query = query.Where(m => queryOptions.CompanyId.Contains(m.CompanyId));
}
if (queryOptions.PolicyTypeId.Any())
{
query = query.Where(m => queryOptions.PolicyTypeId.Contains(m.PolicyTypeId.Value));
}
if (queryOptions.UserId.Any())
{
query = query.Where(m => queryOptions.UserId.Contains(m.UserId));
}
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);
}
//------------------------------------------------------------------------------------------------------
var pagedItems = new PagedItems <Policy>();
//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 <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 <Result> ImportClient(ScopeOptions scope, ImportClient data)
{
var policyTypes = await _lookupService.GetPolicyTypes();
var clientTypes = await _lookupService.GetClientTypes();
var companies = await _directoryLookupService.GetCompanies();
var validator = new ImportClientValidator(policyTypes, clientTypes, companies);
var result = validator.Validate(data).GetResult();
if (!result.Success)
{
return(result);
}
//Clean id number
data.IdNumber = CleanIdNumber(data.IdNumber);
//Load date of birth from IdNumber if possible
if (data.DateOfBirth == null)
{
var id = new IdNumber(data.IdNumber);
if (id.IsValid)
{
data.DateOfBirth = id.DateOfBirth;
}
}
var userId = scope.UserId;
//If a user is specified, use it as the scope
if (!string.IsNullOrEmpty(data.PolicyUserFullName))
{
var parts = data.PolicyUserFullName.Split(' ', 2, StringSplitOptions.RemoveEmptyEntries).ToList();
if (parts.Count < 2)
{
parts.Add("");
}
var userEntityQuery = ScopeQuery.GetUserEntityQuery(_context, scope);
var users = await userEntityQuery.ToListAsync();
var userQuery = from entity in users
where (String.Equals(entity.FirstName, parts[0], StringComparison.OrdinalIgnoreCase) &&
String.Equals(entity.LastName, parts[1], StringComparison.OrdinalIgnoreCase))
//JSON Query: should be included in above query
|| entity.Aliases.Any(alias => String.Equals(alias, data.PolicyUserFullName, StringComparison.OrdinalIgnoreCase))
select entity;
var user = userQuery.FirstOrDefault();
if (user == null)
{
result.AddValidationFailure("UserFullName", "Broker does not exist or is out of scope");
return(result);
}
userId = user.Id;
}
//Check if the client exists in the organisation
var clients = await FindClient(scope, data);
if (clients.Count >= 2)
{
result.AddValidationFailure("", "There are multiple clients matching this record, please increase specificity");
return(result);
}
var clientEntity = clients.FirstOrDefault();
ClientEdit client = null;
//Client exits, check client is in scope
if (clientEntity != null)
{
client = await _clientService.GetClient(scope, clientEntity.Id);
client = LoadClientIdNumber(client, data);
client = MapClientProperties(client, data, clientTypes);
result = await _clientService.UpdateClient(scope, client);
if (!result.Success)
{
return(result);
}
}
else
{
client = new ClientEdit();
client = LoadClientIdNumber(client, data);
client = MapClientProperties(client, data, clientTypes);
client = LoadClientType(client, data);
result = await _clientService.InsertClient(scope, client);
if (!result.Success)
{
return(result);
}
client = (ClientEdit)result.Tag;
}
result = await ImportEmail(scope, data, client);
if (!result.Success)
{
return(result);
}
result = await ImportCellphone(scope, data, client);
if (!result.Success)
{
return(result);
}
result = await ImportPolicy(scope, data, client, userId, policyTypes);
return(result);
}
public async Task <PagedCommissions> GetCommissions(CommissionQueryOptions queryOptions)
{
var userQuery = ScopeQuery.GetUserEntityQuery(_context, queryOptions.Scope);
var query = from commission in _context.Commission
join user in userQuery
on commission.UserId equals user.Id
join policy in _context.Policy
on commission.PolicyId equals policy.Id
join client in _context.Client
on policy.ClientId equals client.Id
join statement in _context.CommissionStatement
on commission.CommissionStatementId equals statement.Id
select new OneAdvisor.Model.Commission.Model.Commission.Commission()
{
Id = commission.Id,
CommissionStatementId = commission.CommissionStatementId,
PolicyId = commission.PolicyId,
CommissionTypeId = commission.CommissionTypeId,
AmountIncludingVAT = commission.AmountIncludingVAT,
VAT = commission.VAT,
UserId = commission.UserId,
BranchId = user.BranchId,
PolicyNumber = policy.Number,
CommissionStatementDate = statement.Date,
PolicyCompanyId = policy.CompanyId,
PolicyClientLastName = client.LastName,
PolicyClientInitials = client.Initials,
PolicyClientDateOfBirth = client.DateOfBirth,
SplitGroupId = commission.SplitGroupId,
PolicyTypeId = policy.PolicyTypeId,
};
//Apply filters ----------------------------------------------------------------------------------------
if (queryOptions.CommissionStatementId.HasValue)
{
query = query.Where(c => c.CommissionStatementId == queryOptions.CommissionStatementId);
}
if (queryOptions.UserId.Any())
{
query = query.Where(c => queryOptions.UserId.Contains(c.UserId));
}
if (queryOptions.BranchId.Any())
{
query = query.Where(c => queryOptions.BranchId.Contains(c.BranchId));
}
if (queryOptions.CommissionTypeId.Any())
{
query = query.Where(c => queryOptions.CommissionTypeId.Contains(c.CommissionTypeId));
}
if (queryOptions.PolicyCompanyId.Any())
{
query = query.Where(c => queryOptions.PolicyCompanyId.Contains(c.PolicyCompanyId));
}
if (!string.IsNullOrWhiteSpace(queryOptions.PolicyNumber))
{
query = query.Where(m => EF.Functions.Like(m.PolicyNumber, queryOptions.PolicyNumber));
}
if (!string.IsNullOrWhiteSpace(queryOptions.PolicyClientLastName))
{
query = query.Where(m => EF.Functions.Like(m.PolicyClientLastName, queryOptions.PolicyClientLastName));
}
if (queryOptions.StartDate.HasValue)
{
query = query.Where(c => c.CommissionStatementDate >= queryOptions.StartDate.Value.Date);
}
if (queryOptions.EndDate.HasValue)
{
query = query.Where(c => c.CommissionStatementDate <= queryOptions.EndDate.Value.Date);
}
//------------------------------------------------------------------------------------------------------
var pagedItems = new PagedCommissions();
//Get total items
pagedItems.TotalItems = await query.CountAsync();
//Aggregations
var aggQuery = from commission in query
select new
{
SumAmountIncludingVAT = query.Select(c => (decimal?)c.AmountIncludingVAT).Sum(),
SumVAT = query.Select(c => (decimal?)c.VAT).Sum(),
};
var aggregates = await aggQuery.FirstOrDefaultAsync();
if (aggregates != null)
{
pagedItems.SumAmountIncludingVAT = aggregates.SumAmountIncludingVAT.Value;
pagedItems.SumVAT = aggregates.SumVAT.Value;
}
//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 <PagedCommissionStatements> GetCommissionStatements(CommissionStatementQueryOptions queryOptions)
{
var organisationQuery = ScopeQuery.GetOrganisationEntityQuery(_context, queryOptions.Scope);
var query = from organisation in organisationQuery
join commissionStatement in _context.CommissionStatement
on organisation.Id equals commissionStatement.OrganisationId
select commissionStatement;
//Apply filters ----------------------------------------------------------------------------------------
if (queryOptions.CommissionStatementId.HasValue)
{
query = query.Where(c => c.Id == queryOptions.CommissionStatementId);
}
if (queryOptions.CompanyId.Any())
{
query = query.Where(c => queryOptions.CompanyId.Contains(c.CompanyId));
}
if (queryOptions.Processed.HasValue)
{
query = query.Where(c => c.Processed == queryOptions.Processed);
}
if (queryOptions.StartDate.HasValue)
{
query = query.Where(c => c.Date >= queryOptions.StartDate.Value.Date);
}
if (queryOptions.EndDate.HasValue)
{
query = query.Where(c => c.Date <= queryOptions.EndDate.Value.Date);
}
if (!string.IsNullOrWhiteSpace(queryOptions.Notes))
{
query = query.Where(m => EF.Functions.Like(m.Notes, queryOptions.Notes));
}
//------------------------------------------------------------------------------------------------------
var pagedItems = new PagedCommissionStatements();
//Get total items
pagedItems.TotalItems = await query.CountAsync();
//Aggregations
var userQuery = ScopeQuery.GetUserEntityQuery(_context, queryOptions.Scope);
var commissionQuery = from user in userQuery
join commission in _context.Commission
on user.Id equals commission.UserId
join commissionStatement in query
on commission.CommissionStatementId equals commissionStatement.Id
select new
{
CommissionStatementId = commission.CommissionStatementId,
AmountIncludingVAT = commission.AmountIncludingVAT,
VAT = commission.VAT,
};
var aggQuery = from commission in commissionQuery
select new
{
SumAmountIncludingVAT = commissionQuery.Select(c => (decimal?)c.AmountIncludingVAT).Sum(),
SumVAT = commissionQuery.Select(c => (decimal?)c.VAT).Sum(),
};
var aggregates = await aggQuery.FirstOrDefaultAsync();
if (aggregates != null)
{
pagedItems.SumAmountIncludingVAT = aggregates.SumAmountIncludingVAT.Value;
pagedItems.SumVAT = aggregates.SumVAT.Value;
}
var modelQuery = from commissionStatement in query
select new CommissionStatement()
{
Id = commissionStatement.Id,
CompanyId = commissionStatement.CompanyId,
AmountIncludingVAT = commissionStatement.AmountIncludingVAT,
VAT = commissionStatement.VAT,
Date = commissionStatement.Date,
Processed = commissionStatement.Processed,
Notes = commissionStatement.Notes,
ActualAmountIncludingVAT = commissionQuery.Where(c => c.CommissionStatementId == commissionStatement.Id).Select(c => c.AmountIncludingVAT).Sum(),
ActualVAT = commissionQuery.Where(c => c.CommissionStatementId == commissionStatement.Id).Select(c => c.VAT).Sum(),
CommissionCount = commissionQuery.Where(c => c.CommissionStatementId == commissionStatement.Id).Count(),
MappingErrorCount = commissionStatement.CommissionErrors.Count(),
CompanyName = commissionStatement.Company.Name
};
//Ordering
modelQuery = modelQuery.OrderBy(queryOptions.SortOptions.Column, queryOptions.SortOptions.Direction);
//Paging
pagedItems.Items = await modelQuery.TakePage(queryOptions.PageOptions.Number, queryOptions.PageOptions.Size).ToListAsync();;
return(pagedItems);
}