public List<tblUserExt> GetViewData(string schoolYearFilter, string schoolFilter, string searchFilter)
{
int userAssignedDistrict = _siteUser.Districts[0].Id;
int schoolYear = Convert.ToInt32(schoolYearFilter);
IQueryable<tblUserExt> query = (
from user in _db.tblUsers
join userDistrict in _db.tblUserDistricts on user.UserId equals userDistrict.UserId
// Left join Role
join role in _db.tblRoles on userDistrict.RoleId equals role.RoleId into rr
from role in rr.DefaultIfEmpty()
// Left join UserSchool
join userSchool in _db.tblUserSchools on user.UserId equals userSchool.UserId into uu
from userSchool in uu.DefaultIfEmpty()
//join schoolYear to filter result on basis of schoolYear
join tblschoolYear in _db.tblSchoolYears on userSchool.SchoolYearId equals tblschoolYear.SchoolYearId
// Filter by user's district
where userDistrict.DistrictId == userAssignedDistrict
&& tblschoolYear.SchoolYear == schoolYear
select new EDS.tblUserExt()
{
AspNetUserId = user.AspNetUserId,
FirstName = user.FirstName,
LastName = user.LastName,
RoleDesc = role.RoleDesc == null ? "--" : role.RoleDesc,
UserEmail = user.UserEmail,
UserId = user.UserId,
StateId = user.StateId,
SchoolYearId = tblschoolYear.SchoolYearId,
// District and School are needed so we can filter results by District
// and School but will result in duplicate rows. Dups are handled after
// all filtering is complete.
DistrictId = userDistrict.DistrictId,
SchoolId = userSchool.SchoolId
});
// Filter by School
int intTryParseResult;
if (int.TryParse(schoolFilter, out intTryParseResult) && intTryParseResult != -1)
{
int[] onlyShowSchool = { intTryParseResult };
query = query.Where(x => onlyShowSchool.Contains(x.SchoolId.Value));
}
else
{
if (_siteUser.isAdministrator || _siteUser.isDataAdministrator)
{
int schoolYearId = _db.tblSchoolYears.Where(x => x.SchoolYear == schoolYear).Select(x => x.SchoolYearId).FirstOrDefault();
int[] schoolsForUser = _siteUser.Schools.Where(x => x.SchoolYearId == schoolYearId).Select(x => x.Id).ToArray();
query = query.Where(x => schoolsForUser.Contains(x.SchoolId.Value));
if (schoolsForUser.Count() == 0)
{
query = query.Where(x => x.SchoolId == intTryParseResult);
}
}
}
if (!String.IsNullOrEmpty(searchFilter))
{
query = query.Where(x => x.FirstName.ToUpper().Contains(searchFilter.ToUpper()) || x.LastName.ToUpper().Contains(searchFilter.ToUpper()));
}
// model will have duplicate rows because a user can belong to
// several districts. Remove dups by using userId to compare rows.
IEqualityComparer<tblUserExt> comparerUserId = new EdsUserIdUserComparer();
return query.AsEnumerable()
.Distinct(comparerUserId)
.ToList();
}
public List <tblUserExt> GetViewData(string schoolYearFilter, string schoolFilter, string searchFilter)
{
int userAssignedDistrict = _siteUser.Districts[0].Id;
int schoolYear = Convert.ToInt32(schoolYearFilter);
IQueryable <tblUserExt> query = (
from user in _db.tblUsers
join userDistrict in _db.tblUserDistricts on user.UserId equals userDistrict.UserId
// Left join Role
join role in _db.tblRoles on userDistrict.RoleId equals role.RoleId into rr
from role in rr.DefaultIfEmpty()
// Left join UserSchool
join userSchool in _db.tblUserSchools on user.UserId equals userSchool.UserId into uu
from userSchool in uu.DefaultIfEmpty()
//join schoolYear to filter result on basis of schoolYear
join tblschoolYear in _db.tblSchoolYears on userSchool.SchoolYearId equals tblschoolYear.SchoolYearId
// Filter by user's district
where userDistrict.DistrictId == userAssignedDistrict &&
tblschoolYear.SchoolYear == schoolYear
select new EDS.tblUserExt()
{
AspNetUserId = user.AspNetUserId,
FirstName = user.FirstName,
LastName = user.LastName,
RoleDesc = role.RoleDesc == null ? "--" : role.RoleDesc,
UserEmail = user.UserEmail,
UserId = user.UserId,
StateId = user.StateId,
SchoolYearId = tblschoolYear.SchoolYearId,
// District and School are needed so we can filter results by District
// and School but will result in duplicate rows. Dups are handled after
// all filtering is complete.
DistrictId = userDistrict.DistrictId,
SchoolId = userSchool.SchoolId
});
// Filter by School
int intTryParseResult;
if (int.TryParse(schoolFilter, out intTryParseResult) && intTryParseResult != -1)
{
int[] onlyShowSchool = { intTryParseResult };
query = query.Where(x => onlyShowSchool.Contains(x.SchoolId.Value));
}
else
{
if (_siteUser.isAdministrator || _siteUser.isDataAdministrator)
{
int schoolYearId = _db.tblSchoolYears.Where(x => x.SchoolYear == schoolYear).Select(x => x.SchoolYearId).FirstOrDefault();
int[] schoolsForUser = _siteUser.Schools.Where(x => x.SchoolYearId == schoolYearId).Select(x => x.Id).ToArray();
query = query.Where(x => schoolsForUser.Contains(x.SchoolId.Value));
if (schoolsForUser.Count() == 0)
{
query = query.Where(x => x.SchoolId == intTryParseResult);
}
}
}
if (!String.IsNullOrEmpty(searchFilter))
{
query = query.Where(x => x.FirstName.ToUpper().Contains(searchFilter.ToUpper()) || x.LastName.ToUpper().Contains(searchFilter.ToUpper()));
}
// model will have duplicate rows because a user can belong to
// several districts. Remove dups by using userId to compare rows.
IEqualityComparer <tblUserExt> comparerUserId = new EdsUserIdUserComparer();
return(query.AsEnumerable()
.Distinct(comparerUserId)
.ToList());
}
