private async Task SetStudents()
{
Quarter quar = GetSelectedQuarter();
var queryParams = new Dictionary <string, string>();
if (quar == null)
{
//_logger.LogDebug("AddScores - SetStudents() - Have to get current quarter.");
Utility util = new Utility(_config);
quar = util.getCurrentQuarter(_context);
}
//set up initial query
/** Use IQueryable so additional conditionals can be added before converting to a
* collection (at which time the query goes to the db)
* **/
IQueryable <Student> StudentsIQ = (from s in _context.Students
where s.StuType == StudentType.Returning &&
s.YearQuarterEnrolled == quar.Id
orderby s.LastName, s.FirstName //default ordering
select s).Include(st => st.Level).Where(st => st.Level != null);
// Based on input search filters, filter student data
if (!String.IsNullOrEmpty(LnameSearch))
{
StudentsIQ = StudentsIQ.Where(s => s.LastName.ToLower().StartsWith(LnameSearch.ToLower()));
queryParams.Add("LnameSearch", LnameSearch);
}
if (!String.IsNullOrEmpty(SidSearch))
{
StudentsIQ = StudentsIQ.Where(s => s.Sid.StartsWith(SidSearch));
queryParams.Add("SidSearch", SidSearch);
}
if (!String.IsNullOrEmpty(FnameSearch))
{
StudentsIQ = StudentsIQ.Where(s => s.FirstName.ToLower().StartsWith(FnameSearch.ToLower()));
queryParams.Add("FnameSearch", FnameSearch);
}
// Now sort data per any inputs
FnameSort = "fname";
LnameSort = "lname_desc";
SidSort = "sid";
//used for determining icon direction
SortDirSid = SortDirFname = SortDirLname = "down";
if (!String.IsNullOrEmpty(SortType))
{
switch (SortType)
{
case "lname":
StudentsIQ = StudentsIQ.OrderBy(s => s.LastName);
LnameSort = "lname_desc";
break;
case "lname_desc":
StudentsIQ = StudentsIQ.OrderByDescending(s => s.LastName);
LnameSort = "lname";
SortDirLname = "up";
break;
case "fname":
StudentsIQ = StudentsIQ.OrderBy(s => s.FirstName);
FnameSort = "fname_desc";
break;
case "fname_desc":
StudentsIQ = StudentsIQ.OrderByDescending(s => s.FirstName);
FnameSort = "fname";
SortDirFname = "up";
break;
case "sid":
StudentsIQ = StudentsIQ.OrderBy(s => s.Sid);
SidSort = "sid_desc";
break;
case "sid_desc":
StudentsIQ = StudentsIQ.OrderByDescending(s => s.Sid);
SidSort = "sid";
SortDirSid = "up";
break;
}
CurrentSortType = SortType;
}
else
{
FnameSort = "fname";
LnameSort = "lname_desc";
SidSort = "sid";
}
QueryParams = queryParams;
Students = await StudentsIQ.ToListAsync();
}
public void OnGet(string sortType)
{
//_context.Database.SetCommandTimeout(200);
/** Use IQueryable so additional conditionals can be added before converting to a
* collection (at which time the query goes to the db)
* **/
IQueryable <Student> StudentsIQ = _context.Students.OrderByDescending(s => s.YearQuarterEnrolled).ThenBy(s => s.LastName).ThenBy(s => s.FirstName);
/***
* get groups, countries, and quarters to fill filter drop downs
* need to do before student data is filtered
***/
//save to separate IQueryable so conditionals can later be used against original before query is sent to database
IQueryable <Student> StudentsIQForGrouping = StudentsIQ;
var g = StudentsIQForGrouping.Select(s => s.Group).Distinct();
if (!String.IsNullOrEmpty(GroupSearch))
{
SelectGroups = new SelectList(g, GroupSearch);
}
else
{
SelectGroups = new SelectList(g);
}
var c = StudentsIQForGrouping.Select(s => s.Country).Distinct();
if (!String.IsNullOrEmpty(CountrySearch))
{
SelectCountries = new SelectList(c, CountrySearch);
}
else
{
SelectCountries = new SelectList(c);
}
var q = StudentsIQForGrouping.Select(s => s.YearQuarterEnrolled).Distinct();
if (!String.IsNullOrEmpty(QuarterSearch))
{
SelectQuarters = new SelectList(q, QuarterSearch);
}
else
{
SelectQuarters = new SelectList(q);
}
/*** end generate drop down filters ***/
/***
* Based on input search filters, filter student data
***/
if (!String.IsNullOrEmpty(LnameSearch))
{
StudentsIQ = StudentsIQ.Where(s => s.LastName.ToLower().StartsWith(LnameSearch.ToLower()));
}
if (!String.IsNullOrEmpty(SidSearch))
{
StudentsIQ = StudentsIQ.Where(s => s.Sid.StartsWith(SidSearch));
}
if (!String.IsNullOrEmpty(FnameSearch))
{
StudentsIQ = StudentsIQ.Where(s => s.FirstName.ToLower().StartsWith(FnameSearch.ToLower()));
}
if (!String.IsNullOrEmpty(GroupSearch))
{
StudentsIQ = StudentsIQ.Where(s => s.Group.Equals(GroupSearch));
}
if (!String.IsNullOrEmpty(CountrySearch))
{
StudentsIQ = StudentsIQ.Where(s => s.Country.Equals(CountrySearch));
}
if (!String.IsNullOrEmpty(QuarterSearch))
{
StudentsIQ = StudentsIQ.Where(s => s.YearQuarterEnrolled.Equals(QuarterSearch));
}
/***
* Now sort data per any inputs
***/
FnameSort = "fname";
LnameSort = "lname_desc";
SidSort = "sid";
GroupSort = "group";
CountrySort = "country";
QuarterSort = "quarter";
SortDirSid = SortDirFname = SortDirLname = SortDirGroup = SortDirCountry = SortDirQuarter = "down";
if (!String.IsNullOrEmpty(sortType))
{
switch (sortType)
{
case "lname_desc":
StudentsIQ = StudentsIQ.OrderByDescending(s => s.LastName);
LnameSort = "lname";
SortDirLname = "up";
break;
case "lname":
StudentsIQ = StudentsIQ.OrderBy(s => s.LastName);
LnameSort = "lname_desc";
break;
case "fname_desc":
StudentsIQ = StudentsIQ.OrderByDescending(s => s.FirstName);
FnameSort = "fname";
SortDirFname = "up";
break;
case "fname":
StudentsIQ = StudentsIQ.OrderBy(s => s.FirstName);
FnameSort = "fname_desc";
break;
case "sid":
StudentsIQ = StudentsIQ.OrderBy(s => s.Sid);
SidSort = "sid_desc";
break;
case "sid_desc":
StudentsIQ = StudentsIQ.OrderByDescending(s => s.Sid);
SidSort = "sid";
SortDirSid = "up";
break;
case "group":
StudentsIQ = StudentsIQ.OrderBy(s => s.Group);
GroupSort = "group_desc";
break;
case "group_desc":
StudentsIQ = StudentsIQ.OrderByDescending(s => s.Group);
GroupSort = "group";
SortDirGroup = "up";
break;
case "country":
StudentsIQ = StudentsIQ.OrderBy(s => s.Country);
CountrySort = "country_desc";
break;
case "country_desc":
StudentsIQ = StudentsIQ.OrderByDescending(s => s.Country);
CountrySort = "country";
SortDirCountry = "up";
break;
case "quarter":
StudentsIQ = StudentsIQ.OrderBy(s => s.YearQuarterEnrolled);
QuarterSort = "quarter_desc";
break;
case "quarter_desc":
StudentsIQ = StudentsIQ.OrderByDescending(s => s.YearQuarterEnrolled);
QuarterSort = "quarter";
SortDirQuarter = "up";
break;
}
}
else
{
FnameSort = "fname";
LnameSort = "lname_desc";
SidSort = "sid";
GroupSort = "group";
CountrySort = "country";
QuarterSort = "quarter";
}
//Now set to list and save to variable
StudentData = StudentsIQ.ToList();
}