public async Task <PagedList <DependentDto> > GetDependentsAsync(UserParams userParams, int employeeId)
{
var query = _context.Dependent
.Where(x => x.EmployeeId == employeeId)
.Join(_context.Dependent_Relationship_Type,
dep => dep.DependentRelationshipTypeId,
rel => rel.Id,
(dep, rel) => new { Rel = rel, Dep = dep })
.Select(dtoData => new DependentDto {
Id = dtoData.Dep.Id,
FirstName = dtoData.Dep.FirstName,
LastName = dtoData.Dep.LastName,
BirthDate = dtoData.Dep.Birthdate,
EmployeeId = dtoData.Dep.EmployeeId,
GenderId = dtoData.Dep.GenderId,
DependentRelationshipTypeId = dtoData.Dep.DependentRelationshipTypeId,
DependentRelationshipTypeName = dtoData.Rel.Type
});
if (!string.IsNullOrEmpty(userParams.Filter))
{
query = query.Where(x => x.FirstName.ToUpper().Contains(userParams.Filter.ToUpper()));
}
query = SortingExtension.SortBy(query, userParams.SortColumn, userParams.Reverse);
return(await PagedList <DependentDto> .CreateAsync(query, userParams.PageNumber, userParams.PageSize));
}
public async Task <PagedList <Company> > GetCompaniesAsync(UserParams userParams)
{
IQueryable <Company> query = _context.Company
.AsNoTracking();
if (!string.IsNullOrEmpty(userParams.Filter))
{
query = query.Where(x => x.CompanyName.ToUpper().Contains(userParams.Filter.ToUpper()));
}
query = SortingExtension.SortBy(query, userParams.SortColumn, userParams.Reverse);
return(await PagedList <Company> .CreateAsync(query, userParams.PageNumber, userParams.PageSize));
}
public async Task <PagedList <ClassDto> > GetClassesAsync(UserParams userParams, int companyId)
{
IQueryable <ClassDto> query = _context.Class
.ProjectTo <ClassDto>(_mapper.ConfigurationProvider)
.Where(x => x.CompanyId == companyId);
if (!string.IsNullOrEmpty(userParams.Filter))
{
query = query.Where(x => x.ClassName.ToUpper().Contains(userParams.Filter.ToUpper()));
}
query.Join(_context.Hsa_Class_Details,
cls => cls.Id,
details => details.ClassId,
(cls, details) => new { DETAILS = details })
.Select(s => new {
HsaClassDetailsId = s.DETAILS.Id
});
query = SortingExtension.SortBy(query, userParams.SortColumn, userParams.Reverse);
return(await PagedList <ClassDto> .CreateAsync(query, userParams.PageNumber, userParams.PageSize));
}
public async Task <PagedList <DivisionDto> > GetDivisionsAsync(UserParams userParams, int companyId)
{
IQueryable <DivisionDto> query = _context.Division
.Where(x => x.CompanyId == companyId)
.ProjectTo <DivisionDto>(_mapper.ConfigurationProvider)
.AsNoTracking();
if (!string.IsNullOrEmpty(userParams.Filter))
{
query = query.Where(x => x.DivisionName.ToUpper().Contains(userParams.Filter.ToUpper()));
}
query.Join(_context.Province,
div => div.ProvinceId,
prov => prov.Id,
(div, prov) => new { Prov = prov })
.Select(s => new {
ProvinceName = s.Prov.Name
});
query = SortingExtension.SortBy(query, userParams.SortColumn, userParams.Reverse);
return(await PagedList <DivisionDto> .CreateAsync(query, userParams.PageNumber, userParams.PageSize));
}
public async Task <PagedList <EmployeeDto> > GetEmployeesAsync(UserParams userParams)
{
var query = _context.Employee
.Join(_context.Company,
emp => emp.CompanyId,
comp => comp.Id,
(emp, comp) => new { Comp = comp, Emp = emp })
.Select(dtoData => new EmployeeDto {
Id = dtoData.Emp.Id,
SIN = dtoData.Emp.SIN,
FirstName = dtoData.Emp.FirstName,
LastName = dtoData.Emp.LastName,
MiddleName = dtoData.Emp.MiddleName,
BirthDate = dtoData.Emp.Birthdate,
Address = dtoData.Emp.Address,
City = dtoData.Emp.City,
PostalCode = dtoData.Emp.PostalCode,
PhoneNumber = dtoData.Emp.PhoneNumber,
EmailAddress = dtoData.Emp.EmailAddress,
EligibilityDate = dtoData.Emp.EligibilityDate,
HireDate = dtoData.Emp.HireDate,
StartDate = dtoData.Emp.StartDate,
TerminationDate = dtoData.Emp.TerminationDate,
Occupation = dtoData.Emp.Occupation,
Compensation = dtoData.Emp.Compensation,
EmployeeNumber = dtoData.Emp.EmployeeNumber,
Smoker = dtoData.Emp.Smoker,
COB = dtoData.Emp.COB,
MailCompany = dtoData.Emp.MailCompany,
EFT = dtoData.Emp.EFT,
Evidence = dtoData.Emp.Evidence,
DependentCoverage = dtoData.Emp.DependentCoverage,
PolicyNumber = dtoData.Emp.PolicyNumber,
InsuranceCompany = dtoData.Emp.InsuranceCompany,
GenderId = dtoData.Emp.GenderId,
MaritalStatusId = dtoData.Emp.MaritalStatusId,
CountryId = dtoData.Emp.CountryId,
ClassId = dtoData.Emp.ClassId,
DivisionId = dtoData.Emp.DivisionId,
CompanyId = dtoData.Emp.CompanyId,
ProvinceId = dtoData.Emp.ProvinceId,
ProvinceName = dtoData.Emp.Province.Name,
CompensationTypeId = dtoData.Emp.CompensationTypeId,
CompanyName = dtoData.Comp.CompanyName
});
if (!string.IsNullOrEmpty(userParams.Filter))
{
if (userParams.FilterBy == "Last Name")
{
query = query.Where(x => x.LastName.ToUpper().StartsWith(userParams.Filter.ToUpper()));
}
else if (userParams.FilterBy == "First Name")
{
query = query.Where(x => x.FirstName.ToUpper().StartsWith(userParams.Filter.ToUpper()));
}
else
{
query = query.Where(x => x.CompanyName.ToUpper().StartsWith(userParams.Filter.ToUpper()));
}
}
query = SortingExtension.SortBy(query, userParams.SortColumn, userParams.Reverse);
return(await PagedList <EmployeeDto> .CreateAsync(query, userParams.PageNumber, userParams.PageSize));
}