public IPagedList <Student> Search(StudentSearchOptions searchOptions)
{
var students = context.Students.Select(s => s);
if (!string.IsNullOrEmpty(searchOptions.NameSearch))
{
students = students.Where(s => s.LastName.Contains(searchOptions.NameSearch) || s.FirstMidName.Contains(searchOptions.NameSearch));
}
switch (searchOptions.SortOptions)
{
default:
case StudentSortOptions.NameAscending:
students = students.OrderBy(s => s.LastName);
break;
case StudentSortOptions.NameDescending:
students = students.OrderByDescending(s => s.LastName);
break;
case StudentSortOptions.DateAscending:
students = students.OrderBy(s => s.EnrollmentDate);
break;
case StudentSortOptions.DateDescending:
students = students.OrderByDescending(s => s.EnrollmentDate);
break;
}
return(students.ToPagedList(searchOptions.PageNumber, searchOptions.PageSize));
}
private static void ApplySearch(Query query, StudentSearchOptions search, string studentAlias,
string personAlias, string studentHouseAlias = null, string studentRegGroupAlias = null,
string studentYearGroupAlias = null)
{
// Insert CTEs needed for search
if (string.IsNullOrWhiteSpace(studentHouseAlias))
{
studentHouseAlias = "SH";
query.With("StudentHouse", HouseCte);
query.LeftJoin($"StudentHouse as {studentHouseAlias}", $"{studentHouseAlias}.StudentId",
$"{studentAlias}.Id");
}
if (string.IsNullOrWhiteSpace(studentRegGroupAlias))
{
studentRegGroupAlias = "SR";
query.With("StudentRegGroup", RegGroupCte);
query.LeftJoin($"StudentRegGroup as {studentRegGroupAlias}", $"{studentRegGroupAlias}.StudentId",
$"{studentAlias}.Id");
}
if (string.IsNullOrWhiteSpace(studentYearGroupAlias))
{
studentYearGroupAlias = "SY";
query.With("StudentYearGroup", YearGroupCte);
query.LeftJoin($"StudentYearGroup as {studentYearGroupAlias}", $"{studentYearGroupAlias}.StudentId",
$"{studentAlias}.Id");
}
search.ApplySearch(query, studentAlias, personAlias, studentHouseAlias, studentRegGroupAlias, studentYearGroupAlias);
}
public async Task <IEnumerable <Student> > GetAll(StudentSearchOptions searchParams)
{
var query = GenerateQuery();
ApplySearch(query, searchParams, TblAlias, "P");
return(await ExecuteQuery(query));
}
public async Task <IEnumerable <StudentSummaryModel> > Search(StudentSearchOptions searchOptions)
{
using (var unitOfWork = await DataConnectionFactory.CreateUnitOfWork())
{
var students = await unitOfWork.Students.SearchAll(searchOptions);
return(students.Select(s => new StudentSummaryModel(s)).ToList());
}
}
public async Task <IActionResult> SearchStudents([FromQuery] StudentSearchOptions searchModel)
{
return(await ProcessAsync(async() =>
{
IEnumerable <StudentDataGridModel> students;
using (new ProcessTimer("Fetch students"))
{
students = (await StudentService.Get(searchModel)).Select(x => x.GetDataGridModel());
}
return Ok(students);
}, Permissions.Student.StudentDetails.ViewStudentDetails));
}
public async Task <IEnumerable <StudentSearchResult> > SearchAll(StudentSearchOptions searchOptions)
{
var query = GenerateEmptyQuery();
query.With("StudentHouse", HouseCte);
query.With("StudentRegGroup", RegGroupCte);
query.With("StudentYearGroup", YearGroupCte);
query.LeftJoin("People as P", "P.Id", "S.PersonId");
query.LeftJoin("StudentHouse as SH", "SH.StudentId", "S.Id");
query.LeftJoin("StudentRegGroup as SR", "SR.StudentId", "S.Id");
query.LeftJoin("StudentYearGroup as SY", "SY.StudentId", "S.Id");
query.Select("S.Id", "P.FirstName", "P.PreferredFirstName", "P.MiddleName", "P.LastName",
"P.PreferredLastName", "P.Gender", "SH.HouseName", "SR.RegGroupName", "SY.YearGroupName");
ApplySearch(query, searchOptions, TblAlias, "P", "SH", "SR", "SY");
return(await ExecuteQuery <StudentSearchResult>(query));
}
public async Task<IActionResult> SearchStudents([FromQuery] StudentSearchOptions searchModel)
{
var students = (await StudentService.Search(searchModel)).ToList();
return Ok(students);
}
private static void ApplySearch(Query query, StudentSearchOptions search)
{
switch (search.Status)
{
case StudentStatus.OnRoll:
query.Where(q =>
q.WhereNull("Student.DateLeaving").OrWhereDate("Student.DateLeaving", ">", DateTime.Today));
break;
case StudentStatus.Leavers:
query.WhereDate("Student.DateLeaving", "<=", DateTime.Today);
break;
case StudentStatus.Future:
query.WhereDate("Student.DateStarting", ">", DateTime.Today);
break;
default:
break;
}
if (!string.IsNullOrWhiteSpace(search.FirstName))
{
query.WhereStarts("StudentPerson.FirstName", search.FirstName.Trim());
}
if (!string.IsNullOrWhiteSpace(search.LastName))
{
query.WhereStarts("StudentPerson.LastName", search.LastName.Trim());
}
if (!string.IsNullOrWhiteSpace(search.Gender))
{
query.Where("StudentPerson.Gender", search.Gender);
}
if (search.Dob != null)
{
query.WhereDate("StudentPerson.Dob", search.Dob.Value);
}
if (search.CurriculumGroupId != null)
{
query.LeftJoin("CurriculumGroupMembership as Membership", "Membership.StudentId", "Student.Id");
query.Where("Membership.GroupId", search.CurriculumGroupId);
}
if (search.RegGroupId != null)
{
query.Where("Student.RegGroupId", search.RegGroupId.Value);
}
if (search.YearGroupId != null)
{
query.Where("Student.YearGroupId", search.YearGroupId.Value);
}
if (search.HouseId != null)
{
query.Where("Student.HouseId", search.HouseId.Value);
}
if (search.SenStatusId != null)
{
query.Where("Student.SenStatusId", search.SenStatusId.Value);
}
}
public ViewResult Index(string sortOrder, string currentFilter, string searchString, int?page)
{
var searchOptions = new StudentSearchOptions();
ConfigureFilter();
ConfigureSortOrder();
ConfigurePaging();
return(View(studentRepository.Search(searchOptions)));
void ConfigureFilter()
{
searchOptions.NameSearch = searchString;
if (searchOptions.NameSearch != null)
{
searchOptions.PageNumber = 1;
}
else
{
searchOptions.NameSearch = currentFilter;
}
ViewBag.CurrentFilter = searchOptions.NameSearch;
}
void ConfigureSortOrder()
{
ViewBag.CurrentSort = sortOrder;
searchOptions.SortOptions = !string.IsNullOrEmpty(sortOrder) ? (StudentSortOptions)Enum.Parse(typeof(StudentSortOptions), sortOrder) : StudentSortOptions.NameAscending;
switch (searchOptions.SortOptions)
{
case StudentSortOptions.NameAscending:
ViewBag.NameSortParm = StudentSortOptions.NameDescending.ToString();
ViewBag.DateSortParm = StudentSortOptions.DateAscending.ToString();
break;
case StudentSortOptions.NameDescending:
ViewBag.NameSortParm = StudentSortOptions.NameAscending.ToString();
ViewBag.DateSortParm = StudentSortOptions.DateAscending.ToString();
break;
case StudentSortOptions.DateAscending:
ViewBag.DateSortParm = StudentSortOptions.DateDescending.ToString();
ViewBag.NameSortParm = StudentSortOptions.NameAscending.ToString();
break;
case StudentSortOptions.DateDescending:
ViewBag.DateSortParm = StudentSortOptions.DateAscending.ToString();
ViewBag.NameSortParm = StudentSortOptions.NameAscending.ToString();
break;
}
}
void ConfigurePaging()
{
if (page.HasValue)
{
searchOptions.PageNumber = page.Value;
}
}
}
public async Task <IEnumerable <StudentModel> > Get(StudentSearchOptions searchOptions)
{
var students = await _studentRepository.GetAll(searchOptions);
return(students.Select(BusinessMapper.Map <StudentModel>).ToList());
}
