public async Task Policies(IExportRenderer <ClientPolicy> renderer, Stream stream, ScopeOptions scope)
{
var query = from client in ScopeQuery.GetClientEntityQuery(_context, scope)
join policy in _context.Policy on client.Id equals policy.ClientId into policyGroup
from policy in policyGroup.DefaultIfEmpty()
join user in _context.Users on policy.UserId equals user.Id into userGroup
from user in userGroup.DefaultIfEmpty()
select new ClientPolicy()
{
IdNumber = (client.IdNumber == "" || client.IdNumber == null) ? client.AlternateIdNumber : client.IdNumber,
FirstName = client.FirstName,
LastName = client.LastName,
Email = client.ClientContacts.Where(c => c.ContactTypeId == ContactType.CONTACT_TYPE_EMAIL).Select(c => c.Value).FirstOrDefault(),
CellPhone = client.ClientContacts.Where(c => c.ContactTypeId == ContactType.CONTACT_TYPE_CELLPHONE).Select(c => c.Value).FirstOrDefault(),
DateOfBirth = client.DateOfBirth,
TaxNumber = client.TaxNumber,
PolicyNumber = policy.Number,
PolicyBroker = user.FirstName + " " + user.LastName,
PolicyPremium = policy.Premium,
PolicyTypeCode = policy.PolicyType.Code,
PolicyStartDate = policy.StartDate,
PolicyCompany = policy.Company.Name
};
var items = await query.ToListAsync();
await renderer.Render(stream, items);
}
private IQueryable <ContactEntity> GetContactEntityQuery(ScopeOptions scope)
{
var query = from client in ScopeQuery.GetClientEntityQuery(_context, scope)
join contact in _context.Contact
on client.Id equals contact.ClientId
select contact;
return(query);
}
private void SourceClientIdsInScope(MergeClients merge, CustomContext context)
{
var clients = ScopeQuery.GetClientEntityQuery(_context, _scope).Where(m => merge.SourceClientIds.Contains(m.Id)).ToList();
if (clients.Count != merge.SourceClientIds.Count)
{
var failure = new ValidationFailure("SourceClientIds", "Invalid Source Client Ids");
context.AddFailure(failure);
}
}
private IQueryable <CommissionAllocationEntity> GetCommissionAllocationEntityQuery(ScopeOptions scope)
{
var clientQuery = ScopeQuery.GetClientEntityQuery(_context, scope);
var query = from commissionAllocation in _context.CommissionAllocation
join fromClient in clientQuery
on commissionAllocation.FromClientId equals fromClient.Id
join toClient in clientQuery
on commissionAllocation.ToClientId equals toClient.Id
select commissionAllocation;
return(query);
}
public async Task <PagedItems <CommissionAllocation> > GetCommissionAllocations(CommissionAllocationQueryOptions queryOptions)
{
var clientQuery = ScopeQuery.GetClientEntityQuery(_context, queryOptions.Scope);
var query = from commissionAllocation in _context.CommissionAllocation
join fromClient in clientQuery
on commissionAllocation.FromClientId equals fromClient.Id
join toClient in clientQuery
on commissionAllocation.ToClientId equals toClient.Id
select new CommissionAllocation()
{
Id = commissionAllocation.Id,
FromClientId = commissionAllocation.FromClientId,
ToClientId = commissionAllocation.ToClientId,
PolicyIdCount = commissionAllocation.CommissionAllocationPolicies.Count(),
FromClientFirstName = fromClient.FirstName,
FromClientLastName = fromClient.LastName
};
//Apply filters ----------------------------------------------------------------------------------------
if (queryOptions.FromClientId.Any())
{
query = query.Where(c => queryOptions.FromClientId.Contains(c.FromClientId));
}
if (queryOptions.ToClientId.Any())
{
query = query.Where(c => queryOptions.ToClientId.Contains(c.ToClientId));
}
//------------------------------------------------------------------------------------------------------
var pagedItems = new PagedItems <CommissionAllocation>();
//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 PolicyAggregates(IExportRenderer <ClientPolicyAggregate> renderer, Stream stream, ScopeOptions scope)
{
var query = from client in ScopeQuery.GetClientEntityQuery(_context, scope)
select new ClientPolicyAggregate()
{
IdNumber = client.IdNumber,
FirstName = client.FirstName,
LastName = client.LastName,
Email = client.ClientContacts.Where(c => c.ContactTypeId == ContactType.CONTACT_TYPE_EMAIL).Select(c => c.Value).FirstOrDefault(),
PolicyInvestmentCount = client.ClientPolicies.Count(p => p.PolicyTypeId == PolicyType.POLICY_TYPE_INVESTMENT),
PolicyLifeInsuranceCount = client.ClientPolicies.Count(p => p.PolicyTypeId == PolicyType.POLICY_TYPE_LIFE_INSURANCE),
PolicyMedicalCoverCount = client.ClientPolicies.Count(p => p.PolicyTypeId == PolicyType.POLICY_TYPE_MEDICAL_COVER),
PolicyShortTermCount = client.ClientPolicies.Count(p => p.PolicyTypeId == PolicyType.POLICY_TYPE_SHORT_TERM),
};
var items = await query.ToListAsync();
await renderer.Render(stream, items);
}
private async Task <List <ClientEntity> > FindClient(ScopeOptions scope, ImportClient data)
{
//Check if the client exists in the organisation
var clientQuery = ScopeQuery.GetClientEntityQuery(_context, scope);
IQueryable <ClientEntity> query;
if (!string.IsNullOrWhiteSpace(data.IdNumber))
{
//First try and match on IdNumber and AlternateIdNumber
query = from entity in clientQuery
where entity.IdNumber == data.IdNumber ||
entity.AlternateIdNumber == data.IdNumber
select entity;
var matches = await query.ToListAsync();
if (matches.Any())
{
return(matches);
}
//If no match try with first 10 chars of Id number
if (data.IdNumber.Count() >= 10)
{
query = from entity in clientQuery
where EF.Functions.Like(entity.IdNumber, $"{data.IdNumber.Substring(0, 10)}%")
select entity;
matches = await query.ToListAsync();
if (matches.Any())
{
return(matches);
}
}
}
//If we dont have an Id then try match on the PolicyNumber
else if (!string.IsNullOrWhiteSpace(data.PolicyNumber))
{
var policy = await _policyService.GetPolicy(scope, data.PolicyCompanyId.Value, data.PolicyNumber);
if (policy != null)
{
query = from entity in clientQuery
where entity.Id == policy.ClientId
select entity;
var matches = await query.ToListAsync();
if (matches.Any())
{
return(matches);
}
}
}
//If not matches, then try with DOB and LastName
query = from entity in clientQuery
where EF.Functions.Like(entity.LastName, data.LastName) &&
entity.DateOfBirth == data.DateOfBirth
select entity;
return(await query.ToListAsync());
}
private IQueryable <ClientEntity> GetClientEntityQuery(ScopeOptions scope)
{
return(ScopeQuery.GetClientEntityQuery(_context, scope));
}
private IQueryable <ClientEntity> GetClientEntityQuery()
{
return(ScopeQuery.GetClientEntityQuery(_context, _scope));
}