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 ActionResult AddOfficeVisit()
{
OfficeVisitCollection ovcoll = new OfficeVisitCollection();
ovcoll = PopulateAddOfficeVisit();
return View(ovcoll);
}
public ActionResult AddVisit(OfficeVisitCollection model, string studentSelect, string sentbySelect, string homeroomSelect, string remedialSelect,
string violationSelect, string handledbySelect, string locationSelect)
{
OfficeVisitCollection coll = (OfficeVisitCollection)Session["AddVisitModel"];
if (ValidateData(model, studentSelect, sentbySelect, homeroomSelect, remedialSelect, violationSelect, handledbySelect, locationSelect))
{
/* validation and transpose */
using (var connection = new MySqlConnection(ConfigurationManager.ConnectionStrings[Constants.ConnectionStringName].ConnectionString))
{
bool sucessful = new OfficeVisitRepository(connection).InsertOfficeVisit(model);
}
/* Add message . clear and return back to add page */
ModelState.Clear();
ViewBag.Success = true;
ModelState.AddModelError(string.Empty,"Office Visit has successully been added");
coll.arrival_dt = DateTime.Now;
coll.office_visit_dt = DateTime.Now;
coll.comments = null;
coll.nap = false;
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);
}
else
{
ViewBag.Success = false;
ModelState.AddModelError(string.Empty, "Student, Homeroom, Violation, Location and SentBy are required to save an Office Visit.");
coll.arrival_dt = model.arrival_dt;
coll.office_visit_dt = model.office_visit_dt;
coll.comments = model.comments;
coll.nap = model.nap;
coll.StudentSelectList = new SelectList(coll.allStudents, "student_id", "student_name", studentSelect);
coll.ReportersSelectList = new SelectList(coll.allReporters, "contact_id", "contact_name", sentbySelect);
coll.HomeRoomSelectList = new SelectList(coll.allHomeRooms, "homeroom_id", "homeroom_name", homeroomSelect);
coll.HandleBySelectList = new SelectList(coll.allHandledBys, "contact_id", "contact_name", handledbySelect);
coll.LocationSelectList = new SelectList(coll.allLocations, "content_course_id", "name", locationSelect);
coll.RemedialSelectList = new SelectList(coll.allRemedials, "remedial_action_id", "name", remedialSelect);
coll.ViolationSelectList = new SelectList(coll.allCodeViolations, "code_of_conduct_violation_id", "name", violationSelect);
}
return View("AddOfficeVisit",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 ActionResult EditVisit(OfficeVisitCollection model, string studentSelect, string sentbySelect, string homeroomSelect, string remedialSelect,
string violationSelect, string handledbySelect, string locationSelect, string EditAction)
{
switch (EditAction)
{
case "Save":
ValidateData(model, studentSelect, sentbySelect, homeroomSelect, remedialSelect, violationSelect, handledbySelect, locationSelect);
SaveOfficeVisit(model);
Session["SuccessAdd"] = true;
break;
case "Cancel":
Session["OfficeVisitId"] = null;
break;
}
return RedirectToAction("OfficeVisit", "OfficeVisit");
}
public bool InsertOfficeVisit(OfficeVisitCollection officeVisit)
{
bool success = false;
try
{
var queryString = @"INSERT INTO office_visit(school_year_id, student_id, total_visits, content_course_id, sent_by_contact_id, office_visit_dt, arrival_dt," +
"handled_by_contact_id, nap, comments, last_update_contact_id, last_update_dt, homeroom_id)" +
"VALUES(samsjacksonville.fn_getSchoolYear(1)," + officeVisit.student_id + ",samsjacksonville.fn_getTotalVisits(" + officeVisit.student_id + ")," + officeVisit.content_course_id +
"," + officeVisit.sent_by_contact_id + ",'" + officeVisit.office_visit_dt.ToString("yyyy-MM-dd HH:mm:ss") + "','" + officeVisit.arrival_dt.ToString("yyyy-MM-dd HH:mm:ss") + "'," + (officeVisit.handled_by_contact_id == 0 ? -1: officeVisit.handled_by_contact_id) +
"," + Convert.ToInt16(officeVisit.nap) +",'" + officeVisit.comments + "'," + officeVisit.last_update_contact_id + ",'"+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," + officeVisit.homeroom_id + ");"
+ "SELECT LAST_INSERT_ID();";
var id = _openConnection.Query<int>(queryString).Single();
//Add Violation
if (!String.IsNullOrEmpty(officeVisit.violationSelect))
{
queryString = @"INSERT INTO office_visit_offense_assn(office_visit_id,code_of_conduct_violation_id) VALUE (" + id + "," + officeVisit.violationSelect + ");";
_openConnection.Execute(queryString);
}
// Add remedy
if (!String.IsNullOrEmpty(officeVisit.remedialSelect))
{
queryString = @"INSERT INTO office_visit_remedial_action_assn(office_visit_id,remedial_action_id) VALUE(" + id + "," + officeVisit.remedialSelect + ");";
_openConnection.Execute(queryString);
}
success = true;
}
catch (Exception ex)
{
throw (ex);
}
return success;
}
public bool UpdateOfficeVisit(OfficeVisitCollection officeVisit)
{
bool success = false;
var queryString = @"UPDATE office_visit SET student_id = @student_id ,content_course_id = @content_course_id,"+
"sent_by_contact_id = @sent_by_contact_id, office_visit_dt = @office_visit_dt, arrival_dt = @arrival_dt,"+
"handled_by_contact_id = @handled_by_contact_id, nap = @nap, comments = @comments, last_update_contact_id = @last_update_contact_id,"+
"is_deleted = 0, homeroom_id = @homeroom_id,last_update_dt = @last_update_dt WHERE office_visit_id = @office_visit_id";
try
{
this._openConnection.Execute(queryString, new
{
office_visit_id = officeVisit.office_visit_id,
student_id = officeVisit.student_id,
content_course_id = officeVisit.content_course_id,
sent_by_contact_id = officeVisit.sent_by_contact_id,
office_visit_dt = officeVisit.office_visit_dt.ToString("yyyy-MM-dd HH:mm:ss"),
arrival_dt = officeVisit.arrival_dt.ToString("yyyy-MM-dd HH:mm:ss"),
handled_by_contact_id = officeVisit.handled_by_contact_id == 0 ? -1: officeVisit.handled_by_contact_id,
nap = Convert.ToInt16(officeVisit.nap),
comments = officeVisit.comments,
last_update_contact_id = officeVisit.last_update_contact_id,
homeroom_id = officeVisit.homeroom_id,
last_update_dt = officeVisit.last_update_dt.ToString("yyyy-MM-dd HH:mm:ss")
});
if(!String.IsNullOrEmpty(officeVisit.remedialSelect))
UpdatetOfficeVisitRemedyAction(officeVisit.office_visit_id, officeVisit.remedialSelect);
if (!String.IsNullOrEmpty(officeVisit.violationSelect))
UpdateOfficeVisitCodeViolation(officeVisit.office_visit_id, officeVisit.violationSelect);
}
catch (Exception ex)
{
throw (ex);
}
return success;
}
private bool ValidateData(OfficeVisitCollection model, string studentSelect, string sentbySelect, string homeroomSelect, string remedialSelect,
string violationSelect, string handledbySelect, string locationSelect)
{
bool successful = false;
if (ModelState.IsValid && !String.IsNullOrEmpty(studentSelect) && !String.IsNullOrEmpty(homeroomSelect) && !String.IsNullOrEmpty(sentbySelect) &&
!String.IsNullOrEmpty(violationSelect) && !String.IsNullOrEmpty(locationSelect))
{
if (Session["ProfileContactId"] != null)
model.last_update_contact_id = Convert.ToInt32(Session["ProfileContactId"]);
model.student_id = Convert.ToInt32(studentSelect);
model.homeroom_id = Convert.ToInt32(homeroomSelect);
model.sent_by_contact_id = Convert.ToInt32(sentbySelect);
if(!String.IsNullOrEmpty(handledbySelect))
model.handled_by_contact_id = Convert.ToInt32(handledbySelect);
model.content_course_id = Convert.ToInt32(locationSelect);
model.last_update_dt = DateTime.Now;
successful = true;
}
return successful;
}
public bool SaveOfficeVisit(OfficeVisitCollection model)
{
bool sucessful = false;
using (var connection = new MySqlConnection(ConfigurationManager.ConnectionStrings[Constants.ConnectionStringName].ConnectionString))
{
sucessful = new OfficeVisitRepository(connection).UpdateOfficeVisit(model);
}
return sucessful;
}
