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()); }