OfficeVisitCollection PopulateAddOfficeVisit()
        {
            OfficeVisitCollection coll = new OfficeVisitCollection();

            using (var connection = new MySqlConnection(ConfigurationManager.ConnectionStrings[Constants.ConnectionStringName].ConnectionString))
            {

                var result = new StudentRepository(connection).GetStudents();
                var result2 = new ContactRepository(connection).GetContacts();
                var result3 = new ContentCourseRepository(connection).GetContentCourses();
                var result4 = new CodeOfConductViolationRepository(connection).GetCodeOfConductViolations();
                var result5 = new HomeRoomRepository(connection).GetHomeRooms();
                var result6 = new RemedialActionRepository(connection).GetRemedialActions();


                coll.allStudents = (IEnumerable<Models.Student>)result.Result.ToArray();
                coll.allReporters = coll.allHandledBys = (IEnumerable<Models.Contact>)result2.Result.ToArray();
                coll.allLocations = (IEnumerable<Models.ContentCourse>)result3.Result.ToArray();
                coll.allCodeViolations = (IEnumerable<Models.CodeOfConductViolation>)result4.Result.ToArray();
                coll.allHomeRooms = (IEnumerable<Models.HomeRoom>)result5.Result.ToArray();
                coll.allRemedials = (IEnumerable<Models.RemedialAction>)result6.Result.ToArray();

                coll.StudentSelectList = new SelectList(coll.allStudents, "student_id", "student_name", null);
                coll.ReportersSelectList = new SelectList(coll.allReporters, "contact_id", "contact_name", null);
                coll.HomeRoomSelectList = new SelectList(coll.allHomeRooms, "homeroom_id", "homeroom_name", null);
                coll.HandleBySelectList = new SelectList(coll.allHandledBys, "contact_id", "contact_name", null);
                coll.LocationSelectList = new SelectList(coll.allLocations, "content_course_id", "name", null);
                coll.RemedialSelectList = new SelectList(coll.allRemedials, "remedial_action_id", "name", null);
                coll.ViolationSelectList = new SelectList(coll.allCodeViolations, "code_of_conduct_violation_id", "name", null);

                Session["AddVisitModel"] = coll;
            }
            return coll;
        }
        public async System.Threading.Tasks.Task<ActionResult> EditOfficeVisit(string id)
        {

            OfficeVisitCollection coll = new OfficeVisitCollection();

            using (var connection = new MySqlConnection(ConfigurationManager.ConnectionStrings[Constants.ConnectionStringName].ConnectionString))
            {
                await connection.OpenAsync();
               
                var result = new StudentRepository(connection).GetStudents();
                var result2 = new ContactRepository(connection).GetContacts();
                var result3 = new ContentCourseRepository(connection).GetContentCourses();
                var result4 = new CodeOfConductViolationRepository(connection).GetCodeOfConductViolations();
                var result5 = new HomeRoomRepository(connection).GetHomeRooms();
                var result6 = new RemedialActionRepository(connection).GetRemedialActions();
                var result7 = new OfficeVisitRepository(connection).GetOfficeVisitByID(Convert.ToInt32(id));

                coll.officeVisit = result7.Result;
                
                coll.allStudents = (IEnumerable<Models.Student>)result.Result.ToArray();
                coll.allReporters = coll.allHandledBys = (IEnumerable<Models.Contact>)result2.Result.ToArray();
                coll.allLocations = (IEnumerable<Models.ContentCourse>)result3.Result.ToArray();
                coll.allCodeViolations = (IEnumerable<Models.CodeOfConductViolation>)result4.Result.ToArray();
                coll.allHomeRooms = (IEnumerable<Models.HomeRoom>)result5.Result.ToArray();
                coll.allRemedials = (IEnumerable<Models.RemedialAction>)result6.Result.ToArray();

                coll.remedialAction = new OfficeVisitRepository(connection).GetOfficeVisitRemedyAction(coll.officeVisit.office_visit_id);
                coll.CodeViolation = new OfficeVisitRepository(connection).GetOfficeVisitCodeViolation(coll.officeVisit.office_visit_id);

                coll.office_visit_id = coll.officeVisit.office_visit_id;
                coll.arrival_dt = coll.officeVisit.arrival_dt;
                coll.office_visit_dt = coll.officeVisit.office_visit_dt;
                coll.nap = coll.officeVisit.nap;
                coll.comments = coll.officeVisit.comments;

                coll.StudentSelectList =  new SelectList(coll.allStudents, "student_id", "student_name", coll.officeVisit.student_id);
                coll.ReportersSelectList = new SelectList(coll.allReporters, "contact_id", "contact_name", coll.officeVisit.sent_by_contact_id);
                coll.HomeRoomSelectList = new SelectList(coll.allHomeRooms, "homeroom_id", "homeroom_name", coll.officeVisit.homeroom_id);
                coll.HandleBySelectList = new SelectList(coll.allHandledBys, "contact_id", "contact_name", coll.officeVisit.handled_by_contact_id);
                coll.LocationSelectList = new SelectList(coll.allLocations, "content_course_id", "name", coll.officeVisit.content_course_id);
                
                coll.RemedialSelectList = new SelectList(coll.allRemedials, "remedial_action_id", "name", coll.remedialAction);
                coll.ViolationSelectList = new SelectList(coll.allCodeViolations, "code_of_conduct_violation_id", "name", coll.CodeViolation);
                Session["OfficeVisitId"] = id;
            }

            return View(coll);
        }
        public System.Web.Mvc.RedirectResult CreateCodeOfConductViolation(string TxtId, string TxtCode, string TxtName)
        {
            using (var connection = new MySqlConnection(ConfigurationManager.ConnectionStrings[Constants.ConnectionStringName].ConnectionString))
            {
                var result = new CodeOfConductViolationRepository(connection).CreateCodeOfConductViolation(TxtId, TxtCode, TxtName);

                if (result == "success")
                {
                    //ViewBag.SQLEror = "Code of Conduct Violation successfully created.";
                    return Redirect("CodeOfConductViolation/CodeOfConductViolation/?error=fileloaded");
                }
                else
                {
                    //do something else here.
                    //ViewBag.SQLError = "Code of Conduct Violation not inserted due to error.";
                    return Redirect("CodeOfConductViolation/CodeOfConductViolation/?error=invalidfile");
                }
            }
        }