public ActionResult AllocateClassrooms(ClassroomAllocation classroomAllocation, int FromHour, int FromMin, string FromAmPm, int ToHour, int ToMin, string ToAmPm)
{
List <SelectListItem> aDepartments = aDepartmentManager.GetAllDepartment();
ViewBag.departments = aDepartments;
List <SelectListItem> aRoomNos = aRoomNoManager.GetAllRoomNos();
ViewBag.RoomNo = aRoomNos;
List <SelectListItem> DaysListItems = aRoomNoManager.GetAllDays();
ViewBag.Day = DaysListItems;
classroomAllocation.From = ConvertHrMinAmPmToMin(FromHour, FromMin, FromAmPm);
classroomAllocation.To = ConvertHrMinAmPmToMin(ToHour, ToMin, ToAmPm);
string message = aRoomNoManager.SaveClassroomAllocationInfo(classroomAllocation);
ViewBag.message = message;
//if (ViewBag.message == "yes" || ViewBag.message == "conflict" || ViewBag.message == "invalid time")
//{
if (ViewBag.message == "invalid time")
{
ViewBag.From = GetTimeString(classroomAllocation.From);
ViewBag.To = GetTimeString(classroomAllocation.To);
}
ModelState.Clear(); //clear after submiting
//}
return(View());
}
public string SaveClassroomAllocationInfo(ClassroomAllocation classroomAllocation)
{
if (aRoomNoGateway.ClassRoomConflict(classroomAllocation))
{
return("conflict");
}
if (aRoomNoGateway.ClassRoomConflictType2(classroomAllocation))
{
return("conflict2");
}
if (classroomAllocation.To < classroomAllocation.From)
{
return("invalid time");
}
int rowAffected = aRoomNoGateway.SaveClassroomAllocationInfo(classroomAllocation);
if (rowAffected > 0)
{
return("yes");
}
else
{
return("no");
}
}
public bool ClassRoomConflict(ClassroomAllocation classroomAllocation)
{
//Query = "select * from ClassRoomAllocation Where Day = '" + classroomAllocation.Day + "' And RoomNo='" +
// classroomAllocation.RoomNo + "' And (('" + classroomAllocation.From + "'>=FromTime AND '" +
// classroomAllocation.From + "'<ToTime) OR ('" + classroomAllocation.To + "'>FromTime AND '" +
// classroomAllocation.To + "'<=ToTime)) And Valid = 'true'";
Query = "select * from ClassRoomAllocation Where Day = '" + classroomAllocation.Day + "' And RoomNo='" +
classroomAllocation.RoomNo + "' And (('" + classroomAllocation.From + "'>=FromTime AND '" +
classroomAllocation.From + "'<ToTime) OR ('" + classroomAllocation.To + "'>FromTime AND '" +
classroomAllocation.To + "'<=ToTime) OR ('" + classroomAllocation.From + "'<=FromTime AND '" +
classroomAllocation.To + "'>=ToTime)) And Valid = 'true'";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
bool result = Reader.HasRows;
Connection.Close();
//comment from line 94 to 105
//if(result == true)
// return result;
//Query = "select * from ClassRoomAllocation Where Day = '" + classroomAllocation.Day + "' And CourseId ='" +
// classroomAllocation.CourseId + "' And (('" + classroomAllocation.From + "'>=FromTime AND '" +
// classroomAllocation.From + "'<ToTime) OR ('" + classroomAllocation.To + "'>FromTime AND '" +
// classroomAllocation.To + "'<=ToTime) OR ('" + classroomAllocation.From + "'<=FromTime AND '" +
// classroomAllocation.To + "'>=ToTime)) And Valid = 'true'";
//Command = new SqlCommand(Query, Connection);
//Connection.Open();
//Reader = Command.ExecuteReader();
//result = Reader.HasRows;
//Connection.Close();
return(result);
}
public ViewResult Allocate(ClassroomAllocation classroomAllocation)
{
ClassroomAllocationViewModel viewModel = new ClassroomAllocationViewModel
{
Departments = DepartmentGateway.GetAllDepartments(),
Days = DayGateway.GetAllDays(),
Rooms = RoomGateway.GetAllRooms()
};
if (!ModelState.IsValid)
{
viewModel.ClassroomAllocation = classroomAllocation;
ViewBag.Message = "Unsuccessful";
return(View(viewModel));
}
int rowAffected = RoomGateway.AllocateRoom(classroomAllocation);
if (rowAffected > 0)
{
ViewBag.Message = "Successful";
}
else
{
ViewBag.Message = "Not Saved";
}
return(View(viewModel));
}
public static ClassroomAllocation GetClassroomAllocation()
{
if (classroomAllocation == null)
{
classroomAllocation = new ClassroomAllocation(ExamTestUtils.GetExam(), ClassroomTestUtils.GetClassroom());
}
return(classroomAllocation);
}
public int SaveClassroomAllocationInfo(ClassroomAllocation classroomAllocation)
{
Query = "Insert Into ClassRoomAllocation Values ('" + classroomAllocation.DepartmentId + "','" + classroomAllocation.CourseId + "','" + classroomAllocation.RoomNo + "','" + classroomAllocation.Day + "','" + classroomAllocation.From + "','" + classroomAllocation.To + "','true')";
Command = new SqlCommand(Query, Connection);
Connection.Open();
int rowAffected = Command.ExecuteNonQuery();
Connection.Close();
return(rowAffected);
}
public bool ClassRoomConflictType2(ClassroomAllocation classroomAllocation)
{
Query = "select * from ClassRoomAllocation Where Day = '" + classroomAllocation.Day + "' And CourseId ='" +
classroomAllocation.CourseId + "' And (('" + classroomAllocation.From + "'>=FromTime AND '" +
classroomAllocation.From + "'<ToTime) OR ('" + classroomAllocation.To + "'>FromTime AND '" +
classroomAllocation.To + "'<=ToTime) OR ('" + classroomAllocation.From + "'<=FromTime AND '" +
classroomAllocation.To + "'>=ToTime)) And Valid = 'true'";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
bool result = Reader.HasRows;
Connection.Close();
return(result);
}
public static int AllocateRoom(ClassroomAllocation classroomAllocation)
{
string sql = "INSERT INTO RoomAllocations (CourseId, RoomId, DayId, FromTime, ToTime) " +
"VALUES (@courseId, @roomId, @dayId, @fromTime, @toTime)";
Command = new SqlCommand(sql, Connection);
Command.Parameters.Clear();
Command.Parameters.AddWithValue("courseId", classroomAllocation.CourseId);
Command.Parameters.AddWithValue("roomId", classroomAllocation.RoomId);
Command.Parameters.AddWithValue("dayId", classroomAllocation.DayId);
Command.Parameters.AddWithValue("fromTime", classroomAllocation.FromTime);
Command.Parameters.AddWithValue("toTime", classroomAllocation.ToTimeHours);
Connection.Open();
int rowAffected = Command.ExecuteNonQuery();
Connection.Close();
return(rowAffected);
}
