public static List <SelectListItem> GetPermissionSlipTemplatesForDropdown(OnlinePermissionSlipEntities db, IPrincipal User, int?SchoolID = null, int?ClassRoomID = null, bool?includeDisabled = false, int?TemplateID = null)
{
List <SelectListItem> PermissionSlipTemplateList = new List <SelectListItem>();
List <Template> DBPermissionSlipTemplates = null;
try
{
DBPermissionSlipTemplates = GetPermissionSlipTemplates(db, User, SchoolID, ClassRoomID);
foreach (Template t in DBPermissionSlipTemplates)
{
SelectListItem si = new SelectListItem()
{
Text = t.Name,
Value = t.ID.ToString(),
Selected = ((TemplateID != null) && (t.ID == TemplateID))
};
PermissionSlipTemplateList.Add(si);
}
}
catch (Exception ex)
{
throw new Exception("Unable to Create Dropdown List of Templates for " + (User.Identity.IsAuthenticated ? User.Identity.Name : "User [Not Authenticated]"), ex);
}
return(PermissionSlipTemplateList);
}
public static List <SelectListItem> GetPermissionSlipNamesForDropdown(OnlinePermissionSlipEntities db, IPrincipal User, DateTime?StartDate, DateTime?EndDate)
{
List <SelectListItem> PermissionSlipList = new List <SelectListItem>();
List <string> permissionSlipMames = null;
try
{
permissionSlipMames = GetPermissionSlips(db, User, StartDate, EndDate).Select(p => p.Name).Distinct().ToList();
foreach (string name in permissionSlipMames)
{
PermissionSlipList.Add(new SelectListItem()
{
Text = name,
Value = name,
Selected = (permissionSlipMames.Count == 1)
});
}
}
catch (Exception ex)
{
throw new Exception("Unable to query students for drop down list", ex);
}
return(PermissionSlipList);
}
public static List <SelectListItem> GetPermissionSlipCategoriesForDropdown(OnlinePermissionSlipEntities db, IPrincipal User, int?CategoryID = null)
{
List <SelectListItem> PermissionSlipCategoryList = new List <SelectListItem>();
List <Category> DBCategoryList = null;
try
{
EnsureUserIsAuthenticated(User);
DBCategoryList = GetPermissionSlipCategories(db, User);
foreach (Category c in DBCategoryList)
{
SelectListItem si = new SelectListItem()
{
Text = c.Name,
Value = c.ID.ToString(),
Selected = ((CategoryID != null) && (c.ID == CategoryID))
};
PermissionSlipCategoryList.Add(si);
}
}
catch (Exception ex)
{
throw new Exception("Unable to Create Dropdown List of Categories", ex);
}
return(PermissionSlipCategoryList);
}
public static List <SelectListItem> GetPermissionSlipsForDropdown(OnlinePermissionSlipEntities db, IPrincipal User)
{
List <SelectListItem> PermissionSlipList = new List <SelectListItem>();
List <PermissionSlip> permissionSlips = null;
try
{
permissionSlips = GetPermissionSlips(db, User);
foreach (PermissionSlip p in permissionSlips)
{
PermissionSlipList.Add(new SelectListItem()
{
Text = p.Name,
Value = p.ID.ToString(),
Selected = (permissionSlips.Count == 1)
});
}
}
catch (Exception ex)
{
throw new Exception("Unable to query students for drop down list", ex);
}
return(PermissionSlipList);
}
public static List <SelectListItem> GetTeachersForDropdown(OnlinePermissionSlipEntities db, IPrincipal User, string TeacherID = "", int?SchoolID = null)
{
//Based on Roles and then assignments, get list of schools.
List <SelectListItem> TeacherList = new List <SelectListItem>();
List <AspNetUser> DBTeacherList = null;
int?schoolID = SchoolID;
try
{
DBTeacherList = GetTeachers(db, User, SchoolID);
foreach (AspNetUser u in DBTeacherList)
{
SelectListItem si = new SelectListItem()
{
Text = u.FullName,
Value = u.Id,
Selected = ((DBTeacherList.Count == 1) || ((TeacherID.Length > 0) && (u.Id == TeacherID)))
};
TeacherList.Add(si);
}
}
catch (Exception ex)
{
throw new Exception("Unable to Create Dropdown List of Teachers for " + (User.Identity.IsAuthenticated ? User.Identity.Name : "User [Not Authenticated]"), ex);
}
return(TeacherList);
}
public static List <SelectListItem> GetStudentsForDropdown(OnlinePermissionSlipEntities db, IPrincipal User, int?StudentID = null, int?ClassID = null)
{
List <SelectListItem> StudentList = new List <SelectListItem>();
List <Student> students = null;
try
{
students = GetStudents(db, User, ClassID);
foreach (Student s in students)
{
StudentList.Add(new SelectListItem()
{
Text = s.FullName,
Value = s.ID.ToString(),
Selected = ((students.Count == 1) || (s.ID == StudentID))
});
}
}
catch (Exception ex)
{
throw new Exception("Unable to query students for drop down list", ex);
}
return(StudentList);
}
public static List <SelectListItem> GetSchoolsForDropdown(OnlinePermissionSlipEntities db, IPrincipal User, int?SchoolID = null)
{
//Based on Roles and then assignments, get list of schools.
List <SelectListItem> SchoolList = new List <SelectListItem>();
List <School> DBSchoolList = null;
int?schoolID = SchoolID;
try
{
DBSchoolList = GetSchools(db, User);
foreach (School s in DBSchoolList)
{
SelectListItem si = new SelectListItem()
{
Text = s.SchoolName,
Value = s.SchoolID.ToString(),
Selected = ((DBSchoolList.Count == 1) || ((schoolID != null) && (s.SchoolID == schoolID)))
};
SchoolList.Add(si);
}
}
catch (Exception ex)
{
throw new Exception("Unable to Create Dropdown List of Schools for " + (User.Identity.IsAuthenticated ? User.Identity.Name : "User [Not Authenticated]"), ex);
}
return(SchoolList);
}
public static List <AspNetUser> GetTeachers(OnlinePermissionSlipEntities db, IPrincipal User, int?SchoolID = null)
{
List <AspNetUser> Teachers = new List <AspNetUser>();
string UserID = "";
string TeacherRoleID = "";
try
{
EnsureUserIsAuthenticated(User);
UserID = User.Identity.GetUserId();
if (TeacherRoleID == null)
{
throw new Exception("Invalid Role Configuration - Teacher Role is Missing");
}
if (User.IsInRole("Teacher"))
{
//return self!
Teachers = db.AspNetUsers.Where(a => a.Id == UserID).ToList();
}
else if (User.IsInRole("School Admin"))
{
if (SchoolID == null)
{
throw new Exception("School must be included to query list of teachers");
}
School school = db.AspNetUsers.Where(u => u.Id == UserID).FirstOrDefault().Schools.Where(s => s.SchoolID == SchoolID).FirstOrDefault();
if (school != null)
{
Teachers = db.AspNetUsers.Where(u => u.AspNetRoles.Any(r => r.Name == "Teacher") && u.Schools.Any(s => s.SchoolID == SchoolID)).ToList();
}
}
else if (User.IsInRole("System Admin"))
{
if (SchoolID == null)
{
throw new Exception("School must be included to query list of teachers");
}
Teachers = db.AspNetUsers.Where(u => u.AspNetRoles.Any(r => r.Name == "Teacher") && u.Schools.Any(s => s.SchoolID == SchoolID)).ToList();
}
else if (User.IsInRole("Guardian"))
{
//TODO : Do Guardians need to make this call??
}
else
{
throw new Exception("Invalid Role assignment");
}
}
catch (Exception ex)
{
throw new Exception("Unable to Obtain List of Teachers for " + (User.Identity.IsAuthenticated ? User.Identity.Name : "User [Not Authenticated]"), ex);
}
return(Teachers);
}
public static List <PermissionSlip> GetPermissionSlips(OnlinePermissionSlipEntities db, IPrincipal User, DateTime?StartDate = null, DateTime?EndDate = null)
{
List <PermissionSlip> PermissionSlips = new List <PermissionSlip>();
string UserID = "";
if (StartDate == null)
{
StartDate = new DateTime(0);
}
if (EndDate == null)
{
EndDate = DateTime.Now.AddYears(100);
}
try
{
UserID = User.Identity.GetUserId();
if (User.IsInRole("System Admin"))
{
PermissionSlips = db.PermissionSlips.Where(p => ((p.StartDateTime <= StartDate) &&
(p.EndDateTime >= StartDate)) ||
((p.StartDateTime <= EndDate) &&
(p.EndDateTime >= EndDate))).ToList();
}
else if (User.IsInRole("School Admin"))
{
PermissionSlips = db.AspNetUsers.Find(UserID).Schools.SelectMany(s => s.ClassRooms).SelectMany(c => c.PermissionSlips.Where(p => ((p.StartDateTime <= StartDate) &&
(p.EndDateTime >= StartDate)) ||
((p.StartDateTime <= EndDate) &&
(p.EndDateTime >= EndDate)))).ToList();
}
else if (User.IsInRole("Teacher"))
{
PermissionSlips = db.AspNetUsers.Find(UserID).ClassRooms.SelectMany(c => c.PermissionSlips.Where(p => ((p.StartDateTime <= StartDate) &&
(p.EndDateTime >= StartDate)) ||
((p.StartDateTime <= EndDate) &&
(p.EndDateTime >= EndDate)))).ToList();
}
else if (User.IsInRole("Guardian"))
{
PermissionSlips = db.AspNetUsers.Find(UserID).GuardianApprovals.Where(g => ((g.PermissionSlip.StartDateTime <= StartDate) &&
(g.PermissionSlip.EndDateTime >= StartDate)) ||
((g.PermissionSlip.StartDateTime <= EndDate) &&
(g.PermissionSlip.EndDateTime >= EndDate)))
.Select(g => g.PermissionSlip).ToList();
}
}
catch (Exception ex)
{
throw;
}
return(PermissionSlips);
}
public static List <Template> GetPermissionSlipTemplates(OnlinePermissionSlipEntities db, IPrincipal User, int?SchoolID = null, int?ClassRoomID = null, bool includeDisabled = false)
{
List <Template> TemplateList = new List <Template>();
string UserID = "";
try
{
EnsureUserIsAuthenticated(User);
UserID = User.Identity.GetUserId();
List <int> SchoolIDList = null;
if (SchoolID != null)
{
SchoolIDList = new List <int>()
{
(int)SchoolID
};
}
else
{
SchoolIDList = db.AspNetUsers.Find(UserID).Schools.Select(s => s.SchoolID).ToList();
}
//Only look for Templates by ClassRoom ID if assigned - Different from schools which must be either provided or based on the user making the request.
if (ClassRoomID != null)
{
if (includeDisabled)
{
TemplateList = db.Templates.Where(t => SchoolIDList.Any(s => t.SchoolID == s) && t.ClassRoomID == ClassRoomID).ToList();
}
else
{
TemplateList = db.Templates.Where(t => SchoolIDList.Any(s => t.SchoolID == s) && t.ClassRoomID == ClassRoomID && t.DisabledDateTime == null).ToList();
}
}
else
{
if (includeDisabled)
{
TemplateList = db.Templates.Where(t => SchoolIDList.Any(s => t.SchoolID == s)).ToList();
}
else
{
TemplateList = db.Templates.Where(t => SchoolIDList.Any(s => t.SchoolID == s) && t.DisabledDateTime == null).ToList();
}
}
}
catch (Exception ex)
{
throw new Exception("Unable to Obtain List of Templates for " + (User.Identity.IsAuthenticated ? User.Identity.Name : "User [Not Authenticated]"), ex);
}
return(TemplateList);
}
public static List <ClassRoom> GetClassRooms(OnlinePermissionSlipEntities db, IPrincipal User, int?SchoolID = null)
{
List <ClassRoom> ClassList = new List <ClassRoom>();
string UserID = "";
try
{
EnsureUserIsAuthenticated(User);
UserID = User.Identity.GetUserId();
if (User.IsInRole("Teacher"))
{
ClassList = db.ClassRooms.Where(c => c.TeacherUserID == UserID && ((SchoolID == null) || (c.SchoolID == SchoolID))).ToList();
}
else if (User.IsInRole("School Admin"))
{
List <int> schoolIDs = (SchoolID != null) ? new List <int>()
{
(int)SchoolID
} : GetSchools(db, User).Select(s => s.SchoolID).ToList();
ClassList = db.ClassRooms.Where(c => schoolIDs.Any(s => s == c.SchoolID)).ToList();
}
else if (User.IsInRole("System Admin"))
{
if (SchoolID != null)
{
ClassList = db.ClassRooms.Where(c => c.SchoolID == SchoolID).ToList();
}
else
{
ClassList = db.ClassRooms.ToList();
}
}
else if (User.IsInRole("Guardian"))
{
//TODO : Do Guardians need to make this call??
ClassList = db.AspNetUsers.Find(UserID).Students.Select(s => s.ClassRoom).Distinct().ToList();
}
else
{
throw new Exception("Invalid Role assignment");
}
}
catch (Exception ex)
{
throw new Exception("Unable to Obtain List of Class Rooms for " + (User.Identity.IsAuthenticated ? User.Identity.Name : "User [Not Authenticated]"), ex);
}
return(ClassList);
}
public static List <Category> GetPermissionSlipCategories(OnlinePermissionSlipEntities db, IPrincipal User)
{
List <Category> PermissionSlipCategoryList = new List <Category>();
try
{
EnsureUserIsAuthenticated(User);
List <int> schoolIDs = Common.GetSchools(db, User).Select(s => s.SchoolID).ToList();
var Categories = db.Categories.Where(c => schoolIDs.Any(t => t == c.SchoolID) || c.UserDefined == false); //Get Common Categories OR those assigned to the school's templates.
PermissionSlipCategoryList = Categories.ToList();
}
catch (Exception ex)
{
throw new Exception("Unable to Obtain List of PermissionSlipCategory Rooms for " + (User.Identity.IsAuthenticated ? User.Identity.Name : "User [Not Authenticated]"), ex);
}
return(PermissionSlipCategoryList);
}
public static List <Student> GetStudents(OnlinePermissionSlipEntities db, IPrincipal User, int?ClassID = null)
{
List <Student> Students = null;
string UserID = "";
try
{
EnsureUserIsAuthenticated(User);
UserID = User.Identity.GetUserId();
//if (ClassID != null)
//{
// Students = db.ClassRooms.Find(ClassID).Students.ToList();
//}
//else
if (User.IsInRole("Guardian"))
{
Students = db.AspNetUsers.Find(UserID).Students.Where(s => s.ClassRoomID == (ClassID != null ? ClassID : s.ClassRoomID)).ToList();
}
else if (User.IsInRole("Teacher"))
{
Students = db.AspNetUsers.Find(UserID).ClassRooms.Where(c => c.ID == (ClassID != null ? ClassID : c.ID)).SelectMany(c => c.Students).ToList();
}
else if (User.IsInRole("School Admin"))
{
Students = db.AspNetUsers.Find(UserID).Schools.SelectMany(s => s.Students).Where(c => c.ClassRoomID == (ClassID != null ? ClassID : c.ClassRoomID)).ToList();
}
else if (User.IsInRole("System Admin"))
{
Students = db.Students.ToList();
}
else
{
throw new Exception();
}
}
catch (Exception ex)
{
throw new Exception("Unable to list students", ex);
}
return(Students);
}
public EmailMessage()
{
try
{
using (OnlinePermissionSlipEntities db = new OnlinePermissionSlipEntities())
{
SystemConfiguration config = db.SystemConfigurations.First();
EmailDomain = config.EmailDomain;
EmailAPIKey = config.EmailAPIKey;
FromAddress = config.DefaultFromAddress;
}
Attachments = new List <EmailAttachment>();
}
catch (Exception ex)
{
throw new Exception("Error loading system configuration configuration", ex);
}
}
public static bool DeleteUnsubscribed(string EmailAddress)
{
SystemConfiguration config = null;
RestClient client = null;
RestRequest request = null;
HttpStatusCode DeleteUnsubscribeRequest;
try
{
using (OnlinePermissionSlipEntities db = new OnlinePermissionSlipEntities())
{
config = db.SystemConfigurations.First();
client = new RestClient
{
BaseUrl = new Uri(Common.MailGunAPIUrl),
Authenticator = new HttpBasicAuthenticator("api", config.EmailAPIKey)
};
request = new RestRequest();
request.AddParameter("domain", config.EmailDomain, ParameterType.UrlSegment);
request.AddParameter("address", EmailAddress, ParameterType.UrlSegment);
request.Resource = "{domain}/unsubscribes/{address}";
request.Method = Method.DELETE;
}
DeleteUnsubscribeRequest = client.Execute(request).StatusCode;
if (DeleteUnsubscribeRequest != HttpStatusCode.OK)
{
throw new Exception("Unexpected http status code returned from email API service for unsubscribes");
}
}
catch (Exception ex)
{
throw new Exception("Unable to query email service for unsubscribe validation", ex);
}
return(true);
}
public static List <School> GetSchools(OnlinePermissionSlipEntities db, IPrincipal User)
{
List <School> SchoolList = new List <School>();
List <int> UserSchoolIDs = new List <int>();
string userID = "";
try
{
EnsureUserIsAuthenticated(User);
if (User.IsInRole("System Admin"))
{
SchoolList = db.Schools.ToList();
}
else
{
userID = User.Identity.GetUserId();
//Get School User is associated with.
if (User.IsInRole("Teacher"))
{
UserSchoolIDs = db.AspNetUsers.Where(u => u.Id == userID).SelectMany(u => u.Schools).Select(s => s.SchoolID).ToList();
}
else if (User.IsInRole("School Admin") || User.IsInRole("Guardian"))
{
UserSchoolIDs = db.AspNetUsers.Find(userID).Schools.Select(s => s.SchoolID).ToList();
}
else if (User.IsInRole("System Admin"))
{
UserSchoolIDs = db.Schools.Select(s => s.SchoolID).ToList();
}
SchoolList = db.Schools.Where(s => UserSchoolIDs.Any(us => us == s.SchoolID)).ToList();
}
}
catch (Exception ex)
{
throw new Exception("Unable to Obtain List of Schools for " + (User.Identity.IsAuthenticated ? User.Identity.Name : "User [Not Authenticated]"), ex);
}
return(SchoolList);
}
public static List <SelectListItem> GetClassRoomsForDropdown(OnlinePermissionSlipEntities db, IPrincipal User, int?SchoolID = null, int?ClassRoomID = null)
{
List <SelectListItem> ClassList = new List <SelectListItem>();
List <ClassRoom> DBClassList = null;
string UserID = "";
try
{
EnsureUserIsAuthenticated(User);
UserID = User.Identity.GetUserId();
if (ClassRoomID != null)
{
DBClassList = db.ClassRooms.Where(c => c.ID == ClassRoomID).ToList();
}
else
{
DBClassList = GetClassRooms(db, User, SchoolID);
}
foreach (ClassRoom c in DBClassList)
{
//Only select the classroom if the classroom id has NOT been passed in or the teacher is assigned to that class AND only that class.
SelectListItem si = new SelectListItem()
{
Text = c.GetClassName(),
Value = c.ID.ToString(),
Selected = (DBClassList.Count == 1) || ((DBClassList.Count > 1) && (c.ID == ClassRoomID))
};
ClassList.Add(si);
}
}
catch (Exception ex)
{
throw new Exception("Unable to Create Dropdown List of Schools for " + (User.Identity.IsAuthenticated ? User.Identity.Name : "User [Not Authenticated]"), ex);
}
return(ClassList);
}
public static void RegisterGrids()
{
MVCGridDefinitionTable.Add("GuardianSearchResults", new MVCGridBuilder <ReportingSearchResultViewModel>()
.WithAuthorizationType(AuthorizationType.AllowAnonymous)
.AddColumns(cols =>
{
// Add your columns here
cols.Add("SchoolName")
.WithHeaderText("School")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(false)
.WithFiltering(true)
.WithValueExpression(c => c.SchoolName); // use the Value Expression to return the cell text for this column
cols.Add("ClassName")
.WithHeaderText("Class")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.ClassName);
cols.Add("StudentName")
.WithHeaderText("Student")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.StudentName); // use the Value Expression to return the cell text for this column
cols.Add("EventName")
.WithHeaderText("Event")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression((i, c) => c.UrlHelper.Action("PermissionSlipApproval", "PermissionSlips", new { i.PermissionSlipID, i.StudentID }))
.WithValueTemplate("<a href='{Value}'>{Model.EventName}</a>", false);
//.WithValueExpression(c => c.EventName); // use the Value Expression to return the cell text for this column
cols.Add("EventStartDate")
.WithHeaderText("Start")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.EventStartDate.ToShortDateString()); // use the Value Expression to return the cell text for this column
cols.Add("EventEndDate")
.WithHeaderText("End")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.EventEndDate.ToShortDateString()); // use the Value Expression to return the cell text for this column
cols.Add("Approved")
.WithHeaderText("Approved")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.Approved != null ? c.Approved.ToString() : "Pending"); // use the Value Expression to return the cell text for this column
cols.Add("ApprovedBy")
.WithHeaderText("Approved By")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.ApprovedBy); // use the Value Expression to return the cell text for this column
//.WithValueExpression((i, c) => c.UrlHelper.Action("detail", "demo", new { id = i.YourProperty }));
})
.WithAdditionalQueryOptionNames("SchoolID", "ClassID", "StudentID", "PermissionSlipName", "StartDate", "EndDate", "ApprovalStatusID", "ShowResults")
.WithAdditionalSetting("RenderLoadingDiv", true)
.WithSorting(true, "EventStartDate", SortDirection.Dsc)
.WithFiltering(true)
.WithPaging(true, 5, true, 100)
.WithQueryOnPageLoad(false)
.WithPreloadData(false)
.WithRetrieveDataMethod((context) =>
{
string UserID = context.CurrentHttpContext.User.Identity.GetUserId();
var options = context.QueryOptions;
string globalSearch = options.GetAdditionalQueryOptionString("Search"); //Text typed into search box
string SchoolIDParam = options.GetAdditionalQueryOptionString("SchoolID");
string ApprovalStatusIDParam = options.GetAdditionalQueryOptionString("ApprovalStatusID");
int?SchoolID = null;
int?ClassID = null;
int?StudentID = null;
int?ApprovalStatusID = null;
if (SchoolIDParam != null && SchoolIDParam.Length > 0)
{
SchoolID = int.Parse(SchoolIDParam);
}
if (ApprovalStatusIDParam != null && ApprovalStatusIDParam.Length > 0)
{
ApprovalStatusID = int.Parse(ApprovalStatusIDParam);
}
int?ApprovedStatusTypeID = (int)Common.ApprovalStatusTypes.Approved;
int?PendingStatusTypeID = (int)Common.ApprovalStatusTypes.Pending;
bool?ApprovalStatus = null;
if (ApprovalStatusID != null)
{
ApprovalStatus = (ApprovalStatusID == ApprovedStatusTypeID);
}
string StartDateParam = options.GetAdditionalQueryOptionString("StartDate");
string EndDateParam = options.GetAdditionalQueryOptionString("EndDate");
DateTime StartDT = DateTime.Parse(StartDateParam);
DateTime EndDT = DateTime.Parse(EndDateParam);
string ClassIDParam = options.GetAdditionalQueryOptionString("ClassID");
if (ClassIDParam != null && ClassIDParam.Length > 0)
{
ClassID = int.Parse(ClassIDParam);
}
string StudentIDParam = options.GetAdditionalQueryOptionString("StudentID");
if (StudentIDParam != null && StudentIDParam.Length > 0)
{
StudentID = int.Parse(StudentIDParam);
}
string PermissionSlipNameParam = options.GetAdditionalQueryOptionString("PermissionSlipName");
var result = new QueryResult <ReportingSearchResultViewModel>();
using (var db = new OnlinePermissionSlipEntities())
{
var query = db.GuardianPermissionSlipsViews.Where(q => q.SchoolID == (SchoolID ?? q.SchoolID))
.Where(q => q.ClassID == (ClassID ?? q.ClassID))
.Where(q => q.StudentID == (StudentID ?? q.StudentID))
.Where(q => q.EventName == (PermissionSlipNameParam != null && PermissionSlipNameParam.Length > 0 ? PermissionSlipNameParam : q.EventName))
.Where(q => q.UserID == UserID)
.Where(q =>
(q.StartDateTime <= StartDT && q.EndDateTime >= StartDT) ||
(q.StartDateTime >= StartDT && q.StartDateTime <= EndDT)
)
.Where(q => q.Approved == (ApprovalStatus == null ? q.Approved : (ApprovalStatusID != PendingStatusTypeID ? ApprovalStatus : null)))
.OrderByDescending(q => q.StartDateTime).AsQueryable();
if (!String.IsNullOrWhiteSpace(options.SortColumnName))
{
switch (options.SortColumnName.ToLower())
{
case "schoolname":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.SchoolName);
}
else
{
query = query.OrderByDescending(q => q.SchoolName);
}
break;
case "classname":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.ClassName);
}
else
{
query = query.OrderByDescending(q => q.ClassName);
}
break;
case "studentname":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.StudentName);
}
else
{
query = query.OrderByDescending(q => q.StudentName);
}
break;
case "eventname":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.EventName);
}
else
{
query = query.OrderByDescending(q => q.EventName);
}
break;
case "eventstartdate":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.StartDateTime);
}
else
{
query = query.OrderByDescending(q => q.StartDateTime);
}
break;
case "eventenddate":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.EndDateTime);
}
else
{
query = query.OrderByDescending(q => q.EndDateTime);
}
break;
case "approved":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.Approved);
}
else
{
query = query.OrderByDescending(q => q.Approved);
}
break;
case "approvedby":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.GuardianName);
}
else
{
query = query.OrderByDescending(q => q.GuardianName);
}
break;
}
}
result.TotalRecords = query.Count();
if (options.GetLimitOffset().HasValue)
{
query = query.Skip(options.GetLimitOffset().Value).Take(options.GetLimitRowcount().Value);
}
List <ReportingSearchResultViewModel> resultList = new List <ReportingSearchResultViewModel>();
foreach (GuardianPermissionSlipsView g in query.ToList())
{
resultList.Add(new ReportingSearchResultViewModel()
{
SchoolName = g.SchoolName,
ClassName = g.ClassName,
StudentID = g.StudentID,
StudentName = g.StudentName,
PermissionSlipID = g.EventID,
EventName = g.EventName,
EventStartDate = g.StartDateTime,
EventEndDate = g.EndDateTime,
Approved = g.Approved,
ApprovedBy = g.GuardianName
});
}
result.Items = resultList;
}
return(result);
}));
MVCGridDefinitionTable.Add("GuardianSearchResultsMobile", new MVCGridBuilder <ReportingSearchResultViewModel>()
.WithAuthorizationType(AuthorizationType.AllowAnonymous)
.AddColumns(cols =>
{
// Add your columns here
cols.Add("StudentName")
.WithHeaderText("Student")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.StudentName); // use the Value Expression to return the cell text for this column
cols.Add("EventName")
.WithHeaderText("Event")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression((i, c) => c.UrlHelper.Action("PermissionSlipApproval", "PermissionSlips", new { i.PermissionSlipID, i.StudentID }))
.WithValueTemplate("<a href='{Value}'>{Model.EventName}</a>", false);
//.WithValueExpression(c => c.EventName); // use the Value Expression to return the cell text for this column
cols.Add("EventStartDate")
.WithHeaderText("Start")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.EventStartDate.ToShortDateString()); // use the Value Expression to return the cell text for this column
cols.Add("Approved")
.WithHeaderText("Approved")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.Approved != null ? c.Approved.ToString() : "Pending"); // use the Value Expression to return the cell text for this column
//.WithValueExpression((i, c) => c.UrlHelper.Action("detail", "demo", new { id = i.YourProperty }));
})
.WithAdditionalQueryOptionNames("SchoolID", "ClassID", "StudentID", "PermissionSlipName", "StartDate", "EndDate", "ApprovalStatusID", "ShowResults")
.WithAdditionalSetting("RenderLoadingDiv", true)
.WithSorting(true, "EventStartDate", SortDirection.Dsc)
.WithFiltering(true)
.WithPaging(true, 5, true, 100)
.WithQueryOnPageLoad(false)
.WithPreloadData(false)
.WithRetrieveDataMethod((context) =>
{
string UserID = context.CurrentHttpContext.User.Identity.GetUserId();
var options = context.QueryOptions;
string globalSearch = options.GetAdditionalQueryOptionString("Search"); //Text typed into search box
string SchoolIDParam = options.GetAdditionalQueryOptionString("SchoolID");
string ApprovalStatusIDParam = options.GetAdditionalQueryOptionString("ApprovalStatusID");
int?SchoolID = null;
int?ClassID = null;
int?StudentID = null;
int?ApprovalStatusID = null;
if (SchoolIDParam != null && SchoolIDParam.Length > 0)
{
SchoolID = int.Parse(SchoolIDParam);
}
if (ApprovalStatusIDParam != null && ApprovalStatusIDParam.Length > 0)
{
ApprovalStatusID = int.Parse(ApprovalStatusIDParam);
}
int?ApprovedStatusTypeID = (int)Common.ApprovalStatusTypes.Approved;
int?PendingStatusTypeID = (int)Common.ApprovalStatusTypes.Pending;
bool?ApprovalStatus = null;
if (ApprovalStatusID != null)
{
ApprovalStatus = (ApprovalStatusID == ApprovedStatusTypeID);
}
string StartDateParam = options.GetAdditionalQueryOptionString("StartDate");
string EndDateParam = options.GetAdditionalQueryOptionString("EndDate");
DateTime StartDT = DateTime.Parse(StartDateParam);
DateTime EndDT = DateTime.Parse(EndDateParam);
string ClassIDParam = options.GetAdditionalQueryOptionString("ClassID");
if (ClassIDParam != null && ClassIDParam.Length > 0)
{
ClassID = int.Parse(ClassIDParam);
}
string StudentIDParam = options.GetAdditionalQueryOptionString("StudentID");
if (StudentIDParam != null && StudentIDParam.Length > 0)
{
StudentID = int.Parse(StudentIDParam);
}
string PermissionSlipNameParam = options.GetAdditionalQueryOptionString("PermissionSlipName");
var result = new QueryResult <ReportingSearchResultViewModel>();
using (var db = new OnlinePermissionSlipEntities())
{
var query = db.GuardianPermissionSlipsViews.Where(q => q.SchoolID == (SchoolID ?? q.SchoolID))
.Where(q => q.ClassID == (ClassID ?? q.ClassID))
.Where(q => q.StudentID == (StudentID ?? q.StudentID))
.Where(q => q.EventName == (PermissionSlipNameParam != null && PermissionSlipNameParam.Length > 0 ? PermissionSlipNameParam : q.EventName))
.Where(q => q.UserID == UserID)
.Where(q =>
(q.StartDateTime <= StartDT && q.EndDateTime >= StartDT) ||
(q.StartDateTime >= StartDT && q.StartDateTime <= EndDT)
)
.Where(q => q.Approved == (ApprovalStatus == null ? q.Approved : (ApprovalStatusID != PendingStatusTypeID ? ApprovalStatus : null)))
.OrderByDescending(q => q.StartDateTime).AsQueryable();
if (!String.IsNullOrWhiteSpace(options.SortColumnName))
{
switch (options.SortColumnName.ToLower())
{
case "studentname":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.StudentName);
}
else
{
query = query.OrderByDescending(q => q.StudentName);
}
break;
case "eventname":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.EventName);
}
else
{
query = query.OrderByDescending(q => q.EventName);
}
break;
case "eventstartdate":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.StartDateTime);
}
else
{
query = query.OrderByDescending(q => q.StartDateTime);
}
break;
case "approved":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.Approved);
}
else
{
query = query.OrderByDescending(q => q.Approved);
}
break;
}
}
result.TotalRecords = query.Count();
if (options.GetLimitOffset().HasValue)
{
query = query.Skip(options.GetLimitOffset().Value).Take(options.GetLimitRowcount().Value);
}
List <ReportingSearchResultViewModel> resultList = new List <ReportingSearchResultViewModel>();
foreach (GuardianPermissionSlipsView g in query.ToList())
{
resultList.Add(new ReportingSearchResultViewModel()
{
SchoolName = g.SchoolName,
ClassName = g.ClassName,
StudentID = g.StudentID,
StudentName = g.StudentName,
PermissionSlipID = g.EventID,
EventName = g.EventName,
EventStartDate = g.StartDateTime,
EventEndDate = g.EndDateTime,
Approved = g.Approved,
ApprovedBy = g.GuardianName
});
}
result.Items = resultList;
}
return(result);
}));
MVCGridDefinitionTable.Add("TeacherSearchResults", new MVCGridBuilder <ReportingSearchResultViewModel>()
.WithAuthorizationType(AuthorizationType.AllowAnonymous)
.AddColumns(cols =>
{
// Add your columns here
cols.Add("SchoolName")
.WithHeaderText("School")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(false)
.WithFiltering(true)
.WithValueExpression(c => c.SchoolName); // use the Value Expression to return the cell text for this column
cols.Add("ClassName")
.WithHeaderText("Class")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.ClassName);
cols.Add("StudentName")
.WithHeaderText("Student")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.StudentName); // use the Value Expression to return the cell text for this column
cols.Add("EventName")
.WithHeaderText("Event")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.EventName); // use the Value Expression to return the cell text for this column
cols.Add("EventStartDate")
.WithHeaderText("Start")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.EventStartDate.ToShortDateString()); // use the Value Expression to return the cell text for this column
cols.Add("EventEndDate")
.WithHeaderText("End")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.EventEndDate.ToShortDateString()); // use the Value Expression to return the cell text for this column
cols.Add("Approved")
.WithHeaderText("Approved")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.Approved != null ? c.Approved.ToString() : "Pending"); // use the Value Expression to return the cell text for this column
cols.Add("ApprovedBy")
.WithHeaderText("Approved By")
.WithAllowChangeVisibility(true)
.WithSorting(true)
.WithVisibility(true)
.WithFiltering(true)
.WithValueExpression(c => c.ApprovedBy); // use the Value Expression to return the cell text for this column
//.WithValueExpression((i, c) => c.UrlHelper.Action("detail", "demo", new { id = i.YourProperty }));
})
.WithAdditionalQueryOptionNames("SchoolID", "ClassID", "StudentID", "PermissionSlipName", "StartDate", "EndDate", "ShowResults")
.WithAdditionalSetting("RenderLoadingDiv", true)
.WithSorting(true, "EventStartDate", SortDirection.Dsc)
.WithFiltering(true)
.WithPaging(true, 5, true, 100)
.WithQueryOnPageLoad(false)
.WithPreloadData(false)
.WithRetrieveDataMethod((context) =>
{
IPrincipal User = context.CurrentHttpContext.User;
string UserID = User.Identity.GetUserId();
var options = context.QueryOptions;
string globalSearch = options.GetAdditionalQueryOptionString("Search"); //Text typed into search box
string SchoolIDParam = options.GetAdditionalQueryOptionString("SchoolID");
int?SchoolID = null;
int?ClassID = null;
int?StudentID = null;
if (SchoolIDParam != null && SchoolIDParam.Length > 0)
{
SchoolID = int.Parse(SchoolIDParam);
}
string StartDateParam = options.GetAdditionalQueryOptionString("StartDate");
string EndDateParam = options.GetAdditionalQueryOptionString("EndDate");
DateTime StartDT = DateTime.Parse(StartDateParam);
DateTime EndDT = DateTime.Parse(EndDateParam);
string ClassIDParam = options.GetAdditionalQueryOptionString("ClassID");
if (ClassIDParam != null && ClassIDParam.Length > 0)
{
ClassID = int.Parse(ClassIDParam);
}
string StudentIDParam = options.GetAdditionalQueryOptionString("StudentID");
if (StudentIDParam != null && StudentIDParam.Length > 0)
{
StudentID = int.Parse(StudentIDParam);
}
string PermissionSlipNameParam = options.GetAdditionalQueryOptionString("PermissionSlipName");
var result = new QueryResult <ReportingSearchResultViewModel>();
using (var db = new OnlinePermissionSlipEntities())
{
List <int> SchoolIDs = null;
if (User.IsInRole("Teacher") || User.IsInRole("School Admin"))
{
SchoolIDs = db.AspNetUsers.Find(UserID).Schools.Where(s => s.SchoolID == (SchoolID ?? s.SchoolID)).Select(s => s.SchoolID).ToList();
}
else if (User.IsInRole("System Admin"))
{
SchoolIDs = db.Schools.Where(s => s.SchoolID == (SchoolID ?? s.SchoolID)).Select(s => s.SchoolID).ToList();
}
else
{
SchoolIDs = new List <int>();
}
if (!User.IsInRole("Teacher"))
{
UserID = null;
}
var query = db.TeacherPermissionSlipsViews.Where(q => SchoolIDs.Any(s => s == q.SchoolID))
.Where(q => q.ClassID == (ClassID ?? q.ClassID))
.Where(q => q.StudentID == (StudentID ?? q.StudentID))
.Where(q => q.EventName == (PermissionSlipNameParam != null && PermissionSlipNameParam.Length > 0 ? PermissionSlipNameParam : q.EventName))
.Where(q => q.TeacherUserID == (UserID ?? q.TeacherUserID))
.Where(q =>
(q.StartDateTime <= StartDT && q.EndDateTime >= StartDT) ||
(q.StartDateTime >= StartDT && q.StartDateTime <= EndDT)
)
.OrderByDescending(q => q.StartDateTime).AsQueryable();
if (!String.IsNullOrWhiteSpace(options.SortColumnName))
{
switch (options.SortColumnName.ToLower())
{
case "schoolname":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.SchoolName);
}
else
{
query = query.OrderByDescending(q => q.SchoolName);
}
break;
case "classname":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.ClassName);
}
else
{
query = query.OrderByDescending(q => q.ClassName);
}
break;
case "studentname":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.StudentName);
}
else
{
query = query.OrderByDescending(q => q.StudentName);
}
break;
case "eventname":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.EventName);
}
else
{
query = query.OrderByDescending(q => q.EventName);
}
break;
case "eventstartdate":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.StartDateTime);
}
else
{
query = query.OrderByDescending(q => q.StartDateTime);
}
break;
case "eventenddate":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.EndDateTime);
}
else
{
query = query.OrderByDescending(q => q.EndDateTime);
}
break;
case "approved":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.Approved);
}
else
{
query = query.OrderByDescending(q => q.Approved);
}
break;
case "approvedby":
if (options.SortDirection == SortDirection.Asc)
{
query = query.OrderBy(q => q.GuardianName);
}
else
{
query = query.OrderByDescending(q => q.GuardianName);
}
break;
}
}
result.TotalRecords = query.Count();
if (options.GetLimitOffset().HasValue)
{
query = query.Skip(options.GetLimitOffset().Value).Take(options.GetLimitRowcount().Value);
}
List <ReportingSearchResultViewModel> resultList = new List <ReportingSearchResultViewModel>();
foreach (TeacherPermissionSlipsView g in query.ToList())
{
resultList.Add(new ReportingSearchResultViewModel()
{
SchoolName = g.SchoolName,
ClassName = g.ClassName,
StudentName = g.StudentName,
EventName = g.EventName,
EventStartDate = g.StartDateTime,
EventEndDate = g.EndDateTime,
Approved = g.Approved,
ApprovedBy = g.GuardianName
});
}
result.Items = resultList;
}
return(result);
}));
}
public ActionResult Index()
{
string UserID = "";
HomePageViewModel vm = new HomePageViewModel();
OnlinePermissionSlipEntities db = new OnlinePermissionSlipEntities();
List <GuardianApproval> guardianApprovals = null;
List <ClassRoom> classRooms = null;
List <PermissionSlip> ClassPermissionSlips = null;
int ApprovedCount = 0;
int NotApprovedCount = 0;
int NoApprovalCount = 0;
Dictionary <int, List <int> > PermissionSlipStudents = new Dictionary <int, List <int> >();
AspNetUser guardian = null;
if (User.Identity.IsAuthenticated)
{
UserID = User.Identity.GetUserId();
if (User.IsInRole("Guardian"))
{
guardian = db.AspNetUsers.Where(u => u.Id == UserID).FirstOrDefault();
guardianApprovals = db.GuardianApprovals.Where(a => a.GuardianUserID == UserID).ToList();
foreach (GuardianApproval g in guardianApprovals)
{
vm.PermissionSlips.Add(new IndexPermissionSlip()
{
guardian = g.AspNetUser, //Should guardian be shown if not the current user even though they are connected to the same student?
guardianApproval = g,
student = g.Student,
permissionSlip = g.PermissionSlip,
GuardianApproved = g.Approved
});
if (PermissionSlipStudents.ContainsKey(g.PermissionSlipID))
{
PermissionSlipStudents[g.PermissionSlipID].Add(g.Student.ID);
}
else
{
PermissionSlipStudents.Add(g.PermissionSlipID, new List <int>()
{
g.Student.ID
});
}
}
//Get Each Guardian's Student, Then ClassRoom, then permission Slip
foreach (Student s in db.Students.Where(s => s.Guardians.Any(g => g.Id == UserID)).ToList())
{
foreach (PermissionSlip p in s.ClassRoom.PermissionSlips)
{
if (!PermissionSlipStudents.ContainsKey(p.ID) || !PermissionSlipStudents[p.ID].Contains(s.ID))
{
vm.PermissionSlips.Add(new IndexPermissionSlip()
{
guardian = guardian,
guardianApproval = null,
student = s,
permissionSlip = p,
GuardianApproved = null
});
if (PermissionSlipStudents.ContainsKey(p.ID))
{
PermissionSlipStudents[p.ID].Add(s.ID);
}
else
{
PermissionSlipStudents.Add(p.ID, new List <int>()
{
s.ID
});
}
}
}
}
}
else if (User.IsInRole("Teacher"))
{
classRooms = db.ClassRooms.Where(c => c.TeacherUserID == UserID).ToList();
DateTime StartDate = DateTime.Now.Date;
DateTime EndDate = DateTime.Now.Date;
if (DateTime.Now.Month >= 8)
{
StartDate = new DateTime(DateTime.Now.Year, 8, 1);
EndDate = new DateTime(DateTime.Now.Year + 1, 6, 30);
}
else
{
StartDate = new DateTime(DateTime.Now.Year - 1, 8, 1);
EndDate = new DateTime(DateTime.Now.Year, 6, 30);
}
foreach (ClassRoom c in classRooms)
{
ClassPermissionSlips = c.PermissionSlips.Where(p => (p.StartDateTime >= StartDate && p.StartDateTime <= EndDate) ||
(p.EndDateTime >= EndDate && p.EndDateTime >= StartDate)
).ToList();
foreach (PermissionSlip p in ClassPermissionSlips)
{
ApprovedCount = db.GuardianApprovals.Where(a => a.PermissionSlipID == p.ID && a.Approved == true).Count();
NotApprovedCount = db.GuardianApprovals.Where(a => a.PermissionSlipID == p.ID && a.Approved == false).Count();
NoApprovalCount = p.ClassRoom.Students.Count - ApprovedCount - NotApprovedCount;
vm.PermissionSlips.Add(new IndexPermissionSlip()
{
permissionSlip = p,
ApprovedCount = ApprovedCount,
NotApprovedCount = NotApprovedCount,
NoApprovalCount = NoApprovalCount
});
}
}
}
else if (User.IsInRole("School Admin"))
{
classRooms = Common.GetClassRooms(db, User);
DateTime StartDate = DateTime.Now.Date;
DateTime EndDate = DateTime.Now.Date;
if (DateTime.Now.Month >= 8)
{
StartDate = new DateTime(DateTime.Now.Year, 8, 1);
EndDate = new DateTime(DateTime.Now.Year + 1, 6, 30);
}
else
{
StartDate = new DateTime(DateTime.Now.Year - 1, 8, 1);
EndDate = new DateTime(DateTime.Now.Year, 6, 30);
}
foreach (ClassRoom c in classRooms)
{
ClassPermissionSlips = c.PermissionSlips.Where(p => (p.StartDateTime >= StartDate && p.StartDateTime <= EndDate) ||
(p.EndDateTime >= EndDate && p.EndDateTime >= StartDate)
).ToList();
foreach (PermissionSlip p in ClassPermissionSlips)
{
ApprovedCount = db.GuardianApprovals.Where(a => a.PermissionSlipID == p.ID && a.Approved == true).Count();
NotApprovedCount = db.GuardianApprovals.Where(a => a.PermissionSlipID == p.ID && a.Approved == false).Count();
NoApprovalCount = p.ClassRoom.Students.Count - ApprovedCount - NotApprovedCount;
vm.PermissionSlips.Add(new IndexPermissionSlip()
{
permissionSlip = p,
ApprovedCount = ApprovedCount,
NotApprovedCount = NotApprovedCount,
NoApprovalCount = NoApprovalCount
});
}
}
}
}
vm.PermissionSlips = vm.PermissionSlips.OrderByDescending(p => p.permissionSlip.StartDateTime.Date).ThenBy(p => p.GuardianApproved).ToList();
return(View(vm));
}
