public List <DistanceEdClass> GetAllClasses()
{
List <DistanceEdClass> returnMe = new List <DistanceEdClass>();
using (SqlConnection connection = new SqlConnection(_connStr))
{
using (SqlCommand sqlCommand = new SqlCommand
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = "SELECT * FROM DistanceEdClass;"
})
{
sqlCommand.Connection.Open();
SqlDataReader dbDataReader = sqlCommand.ExecuteReader();
if (dbDataReader.HasRows)
{
while (dbDataReader.Read())
{
DistanceEdClass obj = dataReaderToDistanceEdClass(dbDataReader);
if (obj != null)
{
returnMe.Add(obj);
}
}
}
sqlCommand.Connection.Close();
}
}
return(returnMe);
}
public void Update(DistanceEdClass DEClass)
{
sanitize(DEClass);
using (SqlConnection connection = new SqlConnection(_connStr))
{
using (SqlCommand sqlCommand = new SqlCommand
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = "UPDATE DistanceEdClass SET BlackboardID=@BBID, Name=@CNAME, RegistrationAvailableFrom=@AVAILFROM, RegistrationAvailableTo=@AVAILTO, InfoURL=@INFOURL, Description=@CDESC, IsRequestable=@ISREQUESTABLE, AreMaterialsAvailable=@MATERIALSAVAIL, RequiresMentor=@REQUIRESMENTOR, DeliveryMethod=@DELIVERYMETHOD, PreRequisites=@PREREQS, RequiredMaterials=@REQMATS, StartDate=@STARTDATE, EndDate=@ENDDATE WHERE id=@CID ;"
})
{
sqlCommand.Parameters.AddWithValue("@CID", DEClass.ID);
sqlCommand.Parameters.AddWithValue("@BBID", !string.IsNullOrEmpty(DEClass.BlackboardID) ? DEClass.BlackboardID : "");
sqlCommand.Parameters.AddWithValue("@CNAME", !string.IsNullOrEmpty(DEClass.Name) ? DEClass.Name : "");
sqlCommand.Parameters.AddWithValue("@AVAILFROM", DEClass.RegistrationAvailableFrom);
sqlCommand.Parameters.AddWithValue("@AVAILTO", DEClass.RegistrationAvailableTo);
sqlCommand.Parameters.AddWithValue("@INFOURL", !string.IsNullOrEmpty(DEClass.MoreInfoURL) ? DEClass.MoreInfoURL : "");
sqlCommand.Parameters.AddWithValue("@CDESC", !string.IsNullOrEmpty(DEClass.Description) ? DEClass.Description : "");
sqlCommand.Parameters.AddWithValue("@ISREQUESTABLE", DEClass.IsRequestable);
sqlCommand.Parameters.AddWithValue("@MATERIALSAVAIL", DEClass.MaterialsAvailableToTeachers);
sqlCommand.Parameters.AddWithValue("@REQUIRESMENTOR", DEClass.MentorTeacherRequired);
sqlCommand.Parameters.AddWithValue("@DELIVERYMETHOD", !string.IsNullOrEmpty(DEClass.DeliveryMethod) ? DEClass.DeliveryMethod : "");
sqlCommand.Parameters.AddWithValue("@PREREQS", !string.IsNullOrEmpty(DEClass.PreRequisites) ? DEClass.PreRequisites : "");
sqlCommand.Parameters.AddWithValue("@REQMATS", !string.IsNullOrEmpty(DEClass.RequiredMaterials) ? DEClass.RequiredMaterials : "");
sqlCommand.Parameters.AddWithValue("@STARTDATE", DEClass.Starts);
sqlCommand.Parameters.AddWithValue("@ENDDATE", DEClass.Ends);
sqlCommand.Connection.Open();
sqlCommand.ExecuteNonQuery();
sqlCommand.Connection.Close();
}
}
}
public List <DistanceEdClass> GetAvailableClasses(DateTime ReferenceDate)
{
List <DistanceEdClass> returnMe = new List <DistanceEdClass>();
using (SqlConnection connection = new SqlConnection(_connStr))
{
using (SqlCommand sqlCommand = new SqlCommand
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = "SELECT * FROM DistanceEdClass WHERE RegistrationAvailableFrom<=GETDATE() AND RegistrationAvailableTo>=GETDATE();"
})
{
sqlCommand.Connection.Open();
SqlDataReader dbDataReader = sqlCommand.ExecuteReader();
if (dbDataReader.HasRows)
{
while (dbDataReader.Read())
{
DistanceEdClass obj = dataReaderToDistanceEdClass(dbDataReader);
if (obj != null)
{
returnMe.Add(obj);
}
}
}
sqlCommand.Connection.Close();
}
}
return(returnMe);
}
public void Add(DistanceEdClass DEClass)
{
sanitize(DEClass);
using (SqlConnection connection = new SqlConnection(_connStr))
{
using (SqlCommand sqlCommand = new SqlCommand
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = "INSERT INTO DistanceEdClass(BlackboardID,Name,RegistrationAvailableFrom,RegistrationAvailableTo,InfoURL,Description,IsRequestable,AreMaterialsAvailable,RequiresMentor,DeliveryMethod,PreRequisites,RequiredMaterials,StartDate,EndDate) VALUES(@BBID,@CNAME,@AVAILFROM,@AVAILTO,@INFOURL,@CDESC,@ISREQUESTABLE,@MATERIALSAVAIL,@REQUIRESMENTOR,@DELIVERYMETHOD,@PREREQS,@REQMATS,@STARTDATE,@ENDDATE);"
})
{
sqlCommand.Parameters.AddWithValue("@BBID", !string.IsNullOrEmpty(DEClass.BlackboardID) ? DEClass.BlackboardID : "");
sqlCommand.Parameters.AddWithValue("@CNAME", !string.IsNullOrEmpty(DEClass.Name) ? DEClass.Name : "");
sqlCommand.Parameters.AddWithValue("@AVAILFROM", DEClass.RegistrationAvailableFrom);
sqlCommand.Parameters.AddWithValue("@AVAILTO", DEClass.RegistrationAvailableTo);
sqlCommand.Parameters.AddWithValue("@INFOURL", !string.IsNullOrEmpty(DEClass.MoreInfoURL) ? DEClass.MoreInfoURL : "");
sqlCommand.Parameters.AddWithValue("@CDESC", !string.IsNullOrEmpty(DEClass.Description) ? DEClass.Description : "");
sqlCommand.Parameters.AddWithValue("@ISREQUESTABLE", DEClass.IsRequestable);
sqlCommand.Parameters.AddWithValue("@MATERIALSAVAIL", DEClass.MaterialsAvailableToTeachers);
sqlCommand.Parameters.AddWithValue("@REQUIRESMENTOR", DEClass.MentorTeacherRequired);
sqlCommand.Parameters.AddWithValue("@DELIVERYMETHOD", !string.IsNullOrEmpty(DEClass.DeliveryMethod) ? DEClass.DeliveryMethod : "");
sqlCommand.Parameters.AddWithValue("@PREREQS", !string.IsNullOrEmpty(DEClass.PreRequisites) ? DEClass.PreRequisites : "");
sqlCommand.Parameters.AddWithValue("@REQMATS", !string.IsNullOrEmpty(DEClass.RequiredMaterials) ? DEClass.RequiredMaterials : "");
sqlCommand.Parameters.AddWithValue("@STARTDATE", DEClass.Starts);
sqlCommand.Parameters.AddWithValue("@ENDDATE", DEClass.Ends);
sqlCommand.Connection.Open();
sqlCommand.ExecuteNonQuery();
sqlCommand.Connection.Close();
}
}
}
private void sanitize(DistanceEdClass DEClass)
{
// Validate dates, because C# and SQL server have different date constraints
if (DEClass.Starts < DataSettings.MinSQLDate)
{
DEClass.Starts = DataSettings.MinSQLDate;
}
if (DEClass.Ends < DataSettings.MinSQLDate)
{
DEClass.Ends = DataSettings.MinSQLDate;
}
if (DEClass.RegistrationAvailableFrom < DataSettings.MinSQLDate)
{
DEClass.RegistrationAvailableFrom = DataSettings.MinSQLDate;
}
if (DEClass.RegistrationAvailableTo < DataSettings.MinSQLDate)
{
DEClass.RegistrationAvailableTo = DataSettings.MinSQLDate;
}
}
public DistanceEdClass Get(int ID)
{
DistanceEdClass returnMe = null;
using (SqlConnection connection = new SqlConnection(_connStr))
{
using (SqlCommand sqlCommand = new SqlCommand
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = "SELECT * FROM DistanceEdClass WHERE ID=@CLASSID;"
})
{
sqlCommand.Parameters.AddWithValue("CLASSID", ID);
sqlCommand.Connection.Open();
SqlDataReader dbDataReader = sqlCommand.ExecuteReader();
if (dbDataReader.HasRows)
{
while (dbDataReader.Read())
{
DistanceEdClass obj = dataReaderToDistanceEdClass(dbDataReader);
if (obj != null)
{
returnMe = obj;
}
}
}
sqlCommand.Connection.Close();
}
}
return(returnMe);
}
public void Update(DistanceEdClass DEClass)
{
_classRepo.Update(DEClass);
}
public void Add(DistanceEdClass DEClass)
{
_classRepo.Add(DEClass);
}
public IActionResult OnPostFormSubmit()
{
if (ModelState.IsValid)
{
regErrors.Clear();
string studentName = Request.Form["txtStudentName"].ToString();
string studentNumber = Request.Form["txtStudentNumber"].ToString();
string studentSchool = Request.Form["txtStudentSchool"].ToString();
string comments = Request.Form["txtNotes"].ToString();
string requestor = Request.Form["txtRequestor"].ToString();
string mentorName = Request.Form["txtMentor"].ToString() ?? string.Empty;
int courseID = Parsers.ToInt(Request.Form["txtCourseID"].ToString() ?? string.Empty);
// Find checked classes
// The easy way to do this would be check all submitted form elements for anything that starts with "chkClass_"
// The safe way to do this would be to load all valid classes, and compare to that list instead
Dictionary <int, DistanceEdClass> availableClasses = _classService.GetAllAvailable(DateTime.Now).ToDictionary(x => x.ID);
DistanceEdRequest newRequest = new DistanceEdRequest()
{
StudentName = studentName,
StudentNumber = studentNumber,
StudentBaseSchool = studentSchool,
Comments = comments,
CourseID = courseID,
MentorTeacherName = mentorName,
Requestor = requestor
};
// Validate
if (courseID > 0)
{
DistanceEdClass selectedClass = availableClasses[courseID] ?? null;
if (selectedClass == null)
{
regErrors.Add("Course with ID \"" + courseID + "\" was not found.");
}
else
{
if (selectedClass.MentorTeacherRequired)
{
if (string.IsNullOrEmpty(mentorName))
{
regErrors.Add("The class \"" + selectedClass.Name + "\" requires a mentor teacher, but no name was provided.");
}
}
}
}
else
{
regErrors.Add("No course selected");
}
if (string.IsNullOrEmpty(studentName))
{
regErrors.Add("Student name is required");
}
if (string.IsNullOrEmpty(studentNumber))
{
regErrors.Add("Student number is required");
}
if (string.IsNullOrEmpty(studentSchool))
{
regErrors.Add("Student base school is required");
}
if (string.IsNullOrEmpty(requestor))
{
regErrors.Add("Requestor name is required");
}
// Submit to DB
if (regErrors.Count == 0)
{
try
{
_requestService.Add(newRequest);
}
catch (Exception ex)
{
regErrors.Add(ex.Message);
}
}
if (regErrors.Count == 0)
{
return(RedirectToPage("Thanks"));
}
else
{
return(Page());
}
}
else
{
return(Page());
}
}
public List <DistanceEdRequest> GetForCourse(DistanceEdClass Course)
{
return(_repository.GetForCourse(Course));
}
public List <DistanceEdRequest> GetForCourse(DistanceEdClass course)
{
return(GetForCourse(course.ID));
}