public IQueryable <T> All <T>(System.Linq.Expressions.Expression <Func <T, bool> > expression, int page, int pageSize) where T : class, new()
{
return(PagingExtensions.Page(All <T>(expression), page, pageSize));
}
public IQueryable <T> All(int page, int pageSize)
{
return(PagingExtensions.Page(Query, page, pageSize));
}
public IQueryable <T> All <T>(int page, int pageSize) where T : class, new()
{
return(PagingExtensions.Page(All <T>(), page, pageSize));
}
/// <summary>
///
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public List <CovidHealthTrack> GetCovidDeclarations(SearchSortModel search)
{
var query = _context.CovidHealthTrack.Include(h => h.Employee).ThenInclude(e => e.EmployeeRoles).Include("Location");
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "employeename")
{
query = query.Where(t => t.Employee.Name.ToLower().Contains(search.SearchString));
}
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "employeeid")
{
query = query.Where(t => t.Employee.EmployeeCode == Convert.ToInt32(search.SearchString));
}
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "date")
{
query = query.Where(t => t.CreatedDate.Date == Convert.ToDateTime(search.SearchString).Date);
}
if (search.userId != 0 && search.roleId != 0)
{
if (search.roleId == 1)
{
query = query.Where(t => t.EmployeeId != search.userId && (
t.Employee.EmployeeRoles.Any(role => role.RoleId == 2 || role.RoleId == 3)));
}
}
search.TotalRecords = query.Count();
switch (search.SortDirection.ToString().ToLower())
{
case "asc":
if (search.SortColumn == "id")
{
query = query.OrderBy(dec => dec.Id);
}
else if (search.SortColumn == "employeename")
{
query = query.OrderBy(dec => dec.Employee.Name);
}
else
{
query = query.OrderBy(dec => dec.CreatedDate);
}
break;
default:
if (search.SortColumn == "id")
{
query = query.OrderByDescending(dec => dec.Id);
}
else if (search.SortColumn == "employeename")
{
query = query.OrderByDescending(dec => dec.Employee.Name);
}
else
{
query = query.OrderByDescending(dec => dec.CreatedDate);
}
break;
}
query = PagingExtensions.Page(query, search.Page, search.PageSize);
var data = query
.Select(declaration => new CovidHealthTrack
{
Id = declaration.Id,
CovidConfirmationDate = declaration.CovidConfirmationDate,
DateOfSymptoms = declaration.DateOfSymptoms,
FamilyMembersCount = declaration.FamilyMembersCount,
HospitalizationNeed = declaration.HospitalizationNeed,
OfficeLastDay = declaration.OfficeLastDay,
OthersInfectedInFamily = declaration.OthersInfectedInFamily,
Status = declaration.Status,
LocationId = declaration.LocationId,
CreatedDate = declaration.CreatedDate,
EmployeeId = declaration.EmployeeId,
Employee = declaration.Employee
})
.ToList();
return(data);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
//public async Task<List<Employee>> GetAllEmployees()
//{
// return await _context.Employee.Include(e => e.EmployeeRoles).ThenInclude(e => e.Role).ToListAsync();
//}
public List <Employee> GetAllEmployees(SearchSortModel search)
{
var query = _context.Employee.Include(e => e.EmployeeRoles).ThenInclude(e => e.Role).AsQueryable();
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "employeename")
{
query = query.Where(t => t.Name.ToLower().Contains(search.SearchString));
}
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "email")
{
query = query.Where(t => t.Email.ToLower().Contains(search.SearchString));
}
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "role")
{
query = query.Where(t => t.EmployeeRoles.Any(r => r.RoleId == Convert.ToInt32(search.SearchString)));
}
if (search.userId != 0 && search.roleId != 0)
{
if (search.roleId == 1)
{
query = query.Where(t => t.Id != search.userId && (
t.EmployeeRoles.Any(role => role.RoleId == 2 || role.RoleId == 3)));
}
if (search.roleId == 2)
{
query = query.Where(t => t.Id != search.userId && (
t.EmployeeRoles.Any(role => role.RoleId == 3)));
}
}
search.TotalRecords = query.Count();
switch (search.SortDirection.ToString().ToLower())
{
case "asc":
if (search.SortColumn.ToLower() == "employeecode")
{
query = query.OrderBy(dec => dec.EmployeeCode);
}
else if (search.SortColumn.ToLower() == "employeename")
{
query = query.OrderBy(dec => dec.Name);
}
else
{
query = query.OrderBy(dec => dec.EmployeeCode);
}
break;
default:
if (search.SortColumn.ToLower() == "employeecode")
{
query = query.OrderByDescending(dec => dec.EmployeeCode);
}
else if (search.SortColumn.ToLower() == "employeename")
{
query = query.OrderByDescending(dec => dec.Name);
}
else
{
query = query.OrderByDescending(dec => dec.EmployeeCode);
}
break;
}
query = PagingExtensions.Page(query, search.Page, search.PageSize);
var data = query
.Select(user => new Employee
{
Id = user.Id,
EmployeeCode = user.EmployeeCode,
CurrentResidentialAddress = user.CurrentResidentialAddress,
Email = user.Email,
Name = user.Name,
Password = user.Password,
PermanentResidentialAddress = user.PermanentResidentialAddress,
EmployeeRoles = user.EmployeeRoles,
Status = user.Status
})
.ToList();
return(data);
}
/// <summary>
///
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public List <HealthTrack> GetDeclarationsByUserId(SearchSortModel search)
{
var query = _context.HealthTrack.Include(h => h.Employee).Include("Location").Include("Zone");
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "date")
{
query = query.Where(t => t.CreatedDate.Date == Convert.ToDateTime(search.SearchString).Date);
}
if (search.userId != 0)
{
query = query.Where(t => t.EmployeeId == search.userId);
}
search.TotalRecords = query.Count();
switch (search.SortDirection.ToString().ToLower())
{
case "asc":
if (search.SortColumn == "id")
{
query = query.OrderBy(dec => dec.Id);
}
else if (search.SortColumn == "employeename")
{
query = query.OrderBy(dec => dec.Employee.Name);
}
else
{
query = query.OrderBy(dec => dec.CreatedDate);
}
break;
default:
if (search.SortColumn == "id")
{
query = query.OrderByDescending(dec => dec.Id);
}
else if (search.SortColumn == "employeename")
{
query = query.OrderByDescending(dec => dec.Employee.Name);
}
else
{
query = query.OrderByDescending(dec => dec.CreatedDate);
}
break;
}
query = PagingExtensions.Page(query, search.Page, search.PageSize);
var data = query
.Select(declaration => new HealthTrack
{
Id = declaration.Id,
ResidentialAddress = declaration.ResidentialAddress,
PreExistHealthIssue = declaration.PreExistHealthIssue,
ContactWithCovidPeople = declaration.ContactWithCovidPeople,
TravelOustSideInLast15Days = declaration.TravelOustSideInLast15Days,
RequestNumber = declaration.RequestNumber,
LocationId = declaration.LocationId,
CreatedDate = declaration.CreatedDate,
ZoneId = declaration.ZoneId,
EmployeeId = declaration.EmployeeId,
Employee = declaration.Employee
})
.ToList();
return(data);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public List <ComeToOfficeRequest> GetRequestsList(SearchSortModel search)
{
// Employe can have only 1 request "Active" at a time
// when entered office it is set to "finished"
//return (roleId == 1 ? await _context.ComeToOfficeRequest.Include(r => r.Employee).ThenInclude(r => r.EmployeeRoles).
// Where(x => x.Status == EntityStatus.Active && x.EmployeeId != userId && (
// x.Employee.EmployeeRoles.Any(role => role.RoleId == 2 || role.RoleId == 3))).ToListAsync()
// : await _context.ComeToOfficeRequest.Include(r => r.Employee).ThenInclude(r => r.EmployeeRoles).
// Where(x => x.Status == EntityStatus.Active && x.EmployeeId != userId).ToListAsync());
var query = _context.ComeToOfficeRequest.Include(r => r.Employee).ThenInclude(e => e.EmployeeRoles).AsQueryable();
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "requestnumber")
{
query = query.Where(t => t.RequestNumber.ToLower().Contains(search.SearchString));
}
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "employeename")
{
query = query.Where(t => t.Employee.Name.ToLower().Contains(search.SearchString));
}
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "employeeid")
{
query = query.Where(t => t.Employee.EmployeeCode == Convert.ToInt32(search.SearchString));
}
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "daterange")
{
string[] dates = search.SearchString.Split("(to)");
query = query.Where(t => t.FromDate.Date >= Convert.ToDateTime(dates[0]).Date&& (t.ToDate.Date == Convert.ToDateTime(dates[1]).Date || (t.FromDate.Date < Convert.ToDateTime(dates[1]).Date&& t.ToDate.Date < Convert.ToDateTime(dates[1]).Date)));
}
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "status")
{
if (search.SearchString.ToLower() == "pending")
{
query = query.Where(t => t.IsApproved == false && t.IsDeclined == false && t.Status == EntityStatus.Active);
}
else if (search.SearchString.ToLower() == "approved")
{
query = query.Where(t => t.IsApproved == true && t.IsDeclined == false && t.Status == EntityStatus.Active);
}
else if (search.SearchString.ToLower() == "declined")
{
query = query.Where(t => t.IsApproved == false && t.IsDeclined == true && t.Status == EntityStatus.Active);
}
}
if (search.userId != 0 && search.roleId != 0)
{
if (search.roleId == 1)
{
query = query.Where(t => t.EmployeeId != search.userId && (
t.Employee.EmployeeRoles.Any(role => role.RoleId == 2 || role.RoleId == 3)));
}
}
search.TotalRecords = query.Count();
switch (search.SortDirection.ToString().ToLower())
{
case "asc":
if (search.SortColumn == "id")
{
query = query.OrderBy(dec => dec.Id);
}
else if (search.SortColumn == "employeename")
{
query = query.OrderBy(dec => dec.Employee.Name);
}
else if (search.SortColumn.ToLower() == "fromdate")
{
query = query.OrderBy(dec => dec.FromDate);
}
else if (search.SortColumn == "todate")
{
query = query.OrderBy(dec => dec.ToDate);
}
else
{
query = query.OrderBy(dec => dec.CreatedDate);
}
break;
default:
if (search.SortColumn == "id")
{
query = query.OrderByDescending(dec => dec.Id);
}
else if (search.SortColumn == "employeename")
{
query = query.OrderByDescending(dec => dec.Employee.Name);
}
else if (search.SortColumn.ToLower() == "fromdate")
{
query = query.OrderByDescending(dec => dec.FromDate);
}
else if (search.SortColumn == "todate")
{
query = query.OrderByDescending(dec => dec.ToDate);
}
else
{
query = query.OrderByDescending(dec => dec.CreatedDate);
}
break;
}
query = PagingExtensions.Page(query, search.Page, search.PageSize);
var data = query
.Select(request => new ComeToOfficeRequest
{
Id = request.Id,
CreatedBy = request.CreatedBy,
FromDate = request.FromDate,
HRComments = request.HRComments,
IsApproved = request.IsApproved,
IsDeclined = request.IsDeclined,
ModifiedBy = request.ModifiedBy,
CreatedDate = request.CreatedDate,
ModifiedDate = request.ModifiedDate,
EmployeeId = request.EmployeeId,
RequestNumber = request.RequestNumber,
Status = request.Status,
ToDate = request.ToDate,
Employee = request.Employee
})
.ToList();
return(data);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public List <ComeToOfficeRequest> GetRequestsListByUserId(SearchSortModel search)
{
//return await _context.ComeToOfficeRequest.Include("Employee").Where(x => x.EmployeeId == userId).ToListAsync();
var query = _context.ComeToOfficeRequest.Include("Employee");
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "requestnumber")
{
query = query.Where(t => t.RequestNumber.ToLower().Contains(search.SearchString));
}
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "daterange")
{
string[] dates = search.SearchString.Split("(to)");
query = query.Where(t => t.FromDate.Date >= Convert.ToDateTime(dates[0]).Date&& (t.ToDate.Date == Convert.ToDateTime(dates[1]).Date || (t.FromDate.Date < Convert.ToDateTime(dates[1]).Date&& t.ToDate.Date < Convert.ToDateTime(dates[1]).Date)));
}
if (!string.IsNullOrEmpty(search.SearchString) && search.PropertyName.ToLower() == "status")
{
if (search.SearchString.ToLower() == "pending")
{
query = query.Where(t => t.IsApproved == false && t.IsDeclined == false && t.Status == EntityStatus.Active);
}
else if (search.SearchString.ToLower() == "approved")
{
query = query.Where(t => t.IsApproved == true && t.IsDeclined == false && t.Status == EntityStatus.Active);
}
else if (search.SearchString.ToLower() == "declined")
{
query = query.Where(t => t.IsApproved == false && t.IsDeclined == true && t.Status == EntityStatus.Active);
}
}
if (search.userId != 0)
{
query = query.Where(t => t.EmployeeId == search.userId);
}
search.TotalRecords = query.Count();
switch (search.SortDirection.ToString().ToLower())
{
case "asc":
if (search.SortColumn == "id")
{
query = query.OrderBy(dec => dec.Id);
}
else if (search.SortColumn.ToLower() == "fromdate")
{
query = query.OrderBy(dec => dec.FromDate);
}
else if (search.SortColumn == "todate")
{
query = query.OrderBy(dec => dec.ToDate);
}
else
{
query = query.OrderBy(dec => dec.CreatedDate);
}
break;
default:
if (search.SortColumn == "id")
{
query = query.OrderByDescending(dec => dec.Id);
}
else if (search.SortColumn.ToLower() == "fromdate")
{
query = query.OrderByDescending(dec => dec.FromDate);
}
else if (search.SortColumn == "todate")
{
query = query.OrderByDescending(dec => dec.ToDate);
}
else
{
query = query.OrderByDescending(dec => dec.CreatedDate);
}
break;
}
query = PagingExtensions.Page(query, search.Page, search.PageSize);
var data = query
.Select(request => new ComeToOfficeRequest
{
Id = request.Id,
CreatedBy = request.CreatedBy,
FromDate = request.FromDate,
HRComments = request.HRComments,
IsApproved = request.IsApproved,
IsDeclined = request.IsDeclined,
ModifiedBy = request.ModifiedBy,
CreatedDate = request.CreatedDate,
ModifiedDate = request.ModifiedDate,
EmployeeId = request.EmployeeId,
RequestNumber = request.RequestNumber,
Status = request.Status,
ToDate = request.ToDate,
Employee = request.Employee
})
.ToList();
return(data);
}
public IQueryable <T> Page(IQueryable <T> source, int page, int pageSize)
{
return(PagingExtensions.Page(source, page, pageSize));
}
public IQueryable <TEntity> All <TEntity>(int page, int pageSize) where TEntity : class, new()
{
var collection = GetCollection <TEntity>();
return(PagingExtensions.Page(collection.AsQueryable(), page, pageSize));
}