public ActionResult SubmitTestEdit(TestCourseVM tcvm)
        {
            if (!CheckCookie())
                return RedirectToAction("Index");

            tcvm.prof = Professor.Get(Session["User"].ToString());
            tcvm.FixDateTimesEdit();
            tcvm.GetBlackedOutDates();

            if (tcvm.BlackedOutDates.Contains(tcvm.testDateClose.Date) || tcvm.FullDates.Contains(tcvm.testDateClose.Date) || (tcvm.testDateClose.Date - DateTime.Now.Date).Days > tcvm.DaysLeft)
            {
                ModelState.AddModelError("Error", "Invalid value selected for closing date.");
                return RedirectToAction("EditTestInfo", tcvm);
            }
            if (tcvm.BlackedOutDates.Contains(tcvm.testDateOpen.Date))
            {
                ModelState.AddModelError(String.Empty, "Invalid value selected for open date.");
                return RedirectToAction("EditTestInfo", tcvm);
            }
            if (CheckTimes(tcvm.test.StartDate))
            {
                ModelState.AddModelError(String.Empty, "Opening time not in range for selected opening day.");
                return RedirectToAction("EditTestInfo", tcvm);
            }
            if (CheckTimes(tcvm.test.EndDate))
            {
                ModelState.AddModelError(String.Empty, "Closing time not in range for selected closing day.");
                return RedirectToAction("EditTestInfo", tcvm);
            }

            string title = "Title";
            string sSections = "";
            List<string> courseIDs = new List<string>();
            foreach (Course thing in tcvm.courses)
            {
                if (thing.Selected)
                {
                    Course courseDetails = Course.Get(thing.Id);
                    courseIDs.Add(thing.Id.ToString());
                    title = courseDetails.Name;
                    sSections += (courseDetails.Section + " ");
                }
            }

            string query;

            query = @"SELECT COUNT(NID)
                      FROM tlStudents_Courses
                      WHERE Enrollment_Status LIKE 'Enrol%' AND TERM = (SELECT VALUE FROM tlSettings WHERE Property='TestSubmissionTerm') AND (Course_ID = '" + courseIDs[0] + "' ";

            if (courseIDs.Count > 1)
            {
                //For each course after the first, add the string below with the course ids
                for (int i = 1; i < courseIDs.Count; i++)
                {
                    query += "OR Course_ID = '" + courseIDs[i] + "' ";
                }
            }

            query += ")";

            string enrollment = Models.Database.ScalarString(query);

            //Query date's enrollment vs this enrollment (unpublished/verified test enrollment counts) and make sure the date population will still be kosher
            if (CheckEnrollment(enrollment, tcvm.test.EndDate))
            {
                ModelState.AddModelError(String.Empty, "Selected closing date is at capacity.  Please choose another closing date.");
                return RedirectToAction("EditTestInfo", tcvm);
            }

            if (String.IsNullOrEmpty(tcvm.test.Notes))
                tcvm.test.Notes = "No Notes";

            Database.nonQuery("DELETE FROM tlTests_Revisions WHERE Test_ID = '" + tcvm.test.Id + "'");
            Database.nonQuery("INSERT INTO tlTests_Revisions (Test_ID, Test_Name, Open_Date_Time, Close_Date_Time, Notes, TestLength) VALUES('" + tcvm.test.Id + "', '" + tcvm.test.Name + "', '" + tcvm.test.StartDate.ToString() + "', '" + tcvm.test.EndDate.ToString() + "', '" + tcvm.test.Notes.Replace("'", "''") + "', '" + tcvm.test.Length + "')");

            //Delete Old Sections
            Database.nonQuery("DELETE FROM tlTests_Courses WHERE Test_ID = '" + tcvm.test.Id + "'");
            //Connect new sections
            foreach (string id in courseIDs)
                Database.nonQuery("INSERT INTO tlTests_Courses VALUES ('" + tcvm.test.Id + "', '" + id + "')");

            //Delete Old Materials
            Database.nonQuery("DELETE FROM tlTests_Test_Materials WHERE Test_ID = '" + tcvm.test.Id + "'");

            string sMaterials = "";
            foreach (Models.Material mat in tcvm.materialList)
            {
                if (mat.Quantity)
                {
                    string matName = Database.ScalarString("SELECT Material_Name FROM tlTest_Materials WHERE Material_ID = '" + mat.MaterialID + "'");
                    sMaterials += (matName + " ");
                    Models.Database.nonQuery("INSERT INTO tlTests_Test_Materials VALUES ('" + tcvm.test.Id + "', '" + mat.MaterialID + "', 1, '')");
                }
            }

            Professor emailProf = Professor.Get(Session["User"].ToString());
            Test emailTest = tcvm.test;

            String ProfessorName = emailProf.FirstName + " " + emailProf.LastName;
            String ProfessorEmail = emailProf.Email;

            Email(ProfessorEmail, "Your edited Test has been submitted", "This is a verification that you submitted an edited test to the CBA Online Test Submission System at " + DateTime.Now.ToString() + "</br><br/>Test Details:<br/>Title: " + tcvm.test.Name + "<br/>Opens: " + tcvm.test.StartDate.ToString() + "<br/>Closes: " + tcvm.test.EndDate.ToString() + "<br/>Length: " + tcvm.test.Length + " minutes<br/>Notes: " + tcvm.test.Notes + "<br/><br/>Course: " + title + "<br/>Sections: " + sSections + "<br/><br/>Materials: " + sMaterials);
            Email("*****@*****.**", ProfessorName + " has edited a test", "There is a revised test for you to review, please open labman to view it!</br><br/>Test Details:<br/>Title: " + tcvm.test.Name + "<br/>Opens: " + tcvm.test.StartDate.ToString() + "<br/>Closes: " + tcvm.test.EndDate.ToString() + "<br/>Length: " + tcvm.test.Length + " minutes<br/>Notes: " + tcvm.test.Notes + "<br/><br/>Course: " + title + "<br/>Sections: " + sSections + "<br/><br/>Materials: " + sMaterials);
            Email("*****@*****.**", ProfessorName + " has edited a test", "There is a revised test for you to review, please open labman to view it!</br><br/>Test Details:<br/>Title: " + tcvm.test.Name + "<br/>Opens: " + tcvm.test.StartDate.ToString() + "<br/>Closes: " + tcvm.test.EndDate.ToString() + "<br/>Length: " + tcvm.test.Length + " minutes<br/>Notes: " + tcvm.test.Notes + "<br/><br/>Course: " + title + "<br/>Sections: " + sSections + "<br/><br/>Materials: " + sMaterials);

            return View("Confirmation");
        }
        public ActionResult LoadTestInfo(TestCourseVM tcvm)
        {
            if (!CheckCookie())
                return RedirectToAction("Index");
            if (String.IsNullOrWhiteSpace(tcvm.title))
                return RedirectToAction("NewTest");

            tcvm.prof = Models.Professor.Get(Session["User"].ToString());
            tcvm.getCourses();
            tcvm.getTitles();
            tcvm.getSections(tcvm.title);

            tcvm.GetBlackedOutDates();

            tcvm.GetMaterialList();

            return View("TestInfo", tcvm);
        }
        public ActionResult SubmitTest(TestCourseVM tcvm)
        {
            if (!CheckCookie())
                return RedirectToAction("Index");

            //Most of this should be moved to the VM. Learning MVC best practices FTW.
            DateTime openTime = Convert.ToDateTime(tcvm.testTimeOpen);
            DateTime closeTime = Convert.ToDateTime(tcvm.testTimeClose);
            tcvm.test.StartDate = tcvm.testDateOpen;
            tcvm.test.StartDate = tcvm.test.StartDate.AddHours(openTime.Hour);
            tcvm.test.StartDate = tcvm.test.StartDate.AddMinutes(openTime.Minute);
            tcvm.test.EndDate = tcvm.testDateClose;
            tcvm.test.EndDate = tcvm.test.EndDate.AddHours(closeTime.Hour);
            tcvm.test.EndDate = tcvm.test.EndDate.AddMinutes(closeTime.Minute);
            if (tcvm.testName != "Final")
                tcvm.test.Name = tcvm.testName + " " + tcvm.testNumber.ToString();
            else
                tcvm.test.Name = tcvm.testName;

            DateTime dtSubmitted = DateTime.Now;

            tcvm.GetBlackedOutDates();

            if (tcvm.BlackedOutDates.Contains(tcvm.testDateClose.Date) || tcvm.FullDates.Contains(tcvm.testDateClose.Date) || (tcvm.testDateClose.Date - DateTime.Now.Date).Days > tcvm.DaysLeft)
            {
                ModelState.AddModelError("Error", "Invalid value selected for closing date.");
                return RedirectToAction("LoadTestInfo", tcvm);
            }
            if ( tcvm.BlackedOutDates.Contains(tcvm.testDateOpen.Date) )
            {
                ModelState.AddModelError(String.Empty, "Invalid value selected for open date.");
                return RedirectToAction("LoadTestInfo", tcvm);
            }
            if (CheckTimes(tcvm.test.StartDate))
            {
                ModelState.AddModelError(String.Empty, "Opening time not in range for selected opening day.");
                return RedirectToAction("LoadTestInfo", tcvm);
            }
            if (CheckTimes(tcvm.test.EndDate))
            {
                ModelState.AddModelError("Error", "Closing time not in range for selected closing day.");
                return RedirectToAction("LoadTestInfo", tcvm);
            }

            string title = "Title";
            string sSections = "";
            List<string> courseIDs = new List<string>();
            foreach (Course thing in tcvm.courses)
            {
                if (thing.Selected)
                {
                    Course courseDetails = Course.Get(thing.Id);
                    courseIDs.Add(thing.Id.ToString());
                    title = courseDetails.Name;
                    sSections += (courseDetails.Section + " ");
                }
            }

            string query;

            query = @"SELECT COUNT(NID)
                      FROM tlStudents_Courses
                      WHERE Enrollment_Status LIKE 'Enrol%' AND TERM = (SELECT VALUE FROM tlSettings WHERE Property='TestSubmissionTerm') AND (Course_ID = '" + courseIDs[0] + "' ";

            if (courseIDs.Count > 1)
            {
                //For each course after the first, add the string below with the course ids
                for (int i = 1; i < courseIDs.Count; i++ )
                {
                    query += "OR Course_ID = '" + courseIDs[i] + "' ";
                }
            }

            query += ")";

            string enrollment = Models.Database.ScalarString(query);

            //Query date's enrollment vs this enrollment (unpublished/verified test enrollment counts) and make sure the date population will still be kosher
            if (CheckEnrollment(enrollment, tcvm.test.EndDate))
            {
                ModelState.AddModelError(String.Empty, "Selected closing date is at capacity.  Please choose another closing date.");
                return RedirectToAction("LoadTestInfo", tcvm);
            }

            if (String.IsNullOrEmpty(tcvm.test.Notes))
                tcvm.test.Notes = "No Notes";

            query = "INSERT INTO tlTests VALUES('" + tcvm.test.Name + "', '1', '" + tcvm.test.StartDate + "', '" + tcvm.test.EndDate + "', '1', NULL, NULL, '" + tcvm.test.Notes.Replace("'", "''") + "', '0', (SELECT Value FROM tlSettings WHERE Property = 'TestSubmissionTerm'), 1, '" + enrollment + "', 0, '1', '" + DateTime.Now + "', '0', NULL, '" + tcvm.test.Length + @"', '0', '0')
                     SELECT SCOPE_IDENTITY()";

            string insertedID = Models.Database.ScalarString(query);

            foreach (string CID in courseIDs)
                Models.Database.nonQuery("INSERT INTO tlTests_Courses VALUES('" + insertedID + "', '" + CID + "')");

            string sMaterials = "";
            foreach (Models.Material mat in tcvm.materialList)
            {
                if (mat.Quantity)
                {
                    string matName = Database.ScalarString("SELECT Material_Name FROM tlTest_Materials WHERE Material_ID = '" + mat.MaterialID + "'");
                    sMaterials += (matName + " ");
                    Models.Database.nonQuery("INSERT INTO tlTests_Test_Materials VALUES ('" + insertedID + "', '" + mat.MaterialID + "', 1, '')");
                }
            }

            Professor emailProf = Professor.Get(Session["User"].ToString());
            Test emailTest = Test.Get(Convert.ToInt16(insertedID));

            String ProfessorName = emailProf.FirstName + " " + emailProf.LastName;
            String ProfessorEmail = emailProf.Email;

            Email(ProfessorEmail, "Your Test has been submitted", "This is a verification that you submitted a test to the CBA Online Test Submission System at " + DateTime.Now.ToString() + "<br/><br/>You will receive a notice when your exam is either accepted or denied.<br/><br/>Test Details:<br/>Title: " + emailTest.Name + "<br/>Opens: " + emailTest.StartDate.ToString() + "<br/>Closes: " + emailTest.EndDate.ToString() + "<br/>Length: " + tcvm.test.Length + " minutes<br/>Notes: " + tcvm.test.Notes + "<br/><br/>Course: " + title + "<br/>Sections: " + sSections + "<br/><br/>Materials: " + sMaterials);
            Email("*****@*****.**", ProfessorName + " has submitted a test", "There is a new test for you to review, please open labman to view it!</br><br/>Test Details:<br/>Title: " + emailTest.Name + "<br/>Opens: " + emailTest.StartDate.ToString() + "<br/>Closes: " + emailTest.EndDate.ToString() + "<br/>Length: " + tcvm.test.Length + " minutes<br/>Notes: " + tcvm.test.Notes + "<br/><br/>Course: " + title + "<br/>Sections: " + sSections + "<br/><br/>Materials: " + sMaterials);
            Email("*****@*****.**", ProfessorName + " has submitted a test", "There is a new test for you to review, please open labman to view it!</br><br/>Test Details:<br/>Title: " + emailTest.Name + "<br/>Opens: " + emailTest.StartDate.ToString() + "<br/>Closes: " + emailTest.EndDate.ToString() + "<br/>Length: " + tcvm.test.Length + " minutes<br/>Notes: " + tcvm.test.Notes + "<br/><br/>Course: " + title + "<br/>Sections: " + sSections + "<br/><br/>Materials: " + sMaterials);

            return RedirectToAction("Confirmation");
        }
        public ActionResult EditTestInfo(TestCourseVM tcvm)
        {
            if (!CheckCookie())
                return RedirectToAction("Index");

            tcvm.prof = Professor.Get(Session["User"].ToString());

            try
            {
                tcvm.test = Test.Get(Convert.ToInt32(tcvm.SelectedTestID));
            }
            catch
            {
                ModelState.AddModelError(String.Empty, "Failed to retrieve test.");
                return View("EditTest");
            }

            tcvm.testDateOpen = tcvm.test.StartDate.Date;
            tcvm.testTimeOpen = tcvm.test.StartDate.ToString();
            tcvm.testDateClose = tcvm.test.EndDate.Date;
            tcvm.testTimeClose = tcvm.test.EndDate.ToString();

            tcvm.getSectionsByTest(tcvm.SelectedTestID, tcvm.prof.Id);
            tcvm.GetBlackedOutDates();

            return View(tcvm);
        }