public List <AllocateClassroomModel> GetScheduleTimeOverlape()
{
SqlConnection connection = new SqlConnection(connectinDB);
string query = "SELECT *FROM AllocateRoom";
SqlCommand command = new SqlCommand(query, connection);
List <AllocateClassroomModel> allocateRooms = new List <AllocateClassroomModel>();
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
AllocateClassroomModel allocateRoom = new AllocateClassroomModel();
allocateRoom.DepartmentId = int.Parse(reader["DepartmnetId"].ToString());
allocateRoom.DayId = int.Parse(reader["DayId"].ToString());
allocateRoom.CourseId = int.Parse(reader["CourseId"].ToString());
allocateRoom.RoomId = int.Parse(reader["ClassRoomId"].ToString());
allocateRoom.FromTime = Convert.ToDateTime(reader["FromTime"].ToString());
allocateRoom.ToTime = Convert.ToDateTime(reader["ToTime"].ToString());
allocateRooms.Add(allocateRoom);
}
reader.Close();
}
connection.Close();
return(allocateRooms);
}
//----------------------------------------------------------------//
public string Allocate(AllocateClassroomModel allocateClassroom)
{
int rowEffect = allocateClassroomGateway.Allocate(allocateClassroom);
if (rowEffect > 0)
{
return("Save Successful");
}
else
{
return("Save Faild");
}
}
public int SaveAllocateClassroom(AllocateClassroomModel allocateClassroom)
{
int rowAffected;
SqlConnection con = new SqlConnection(connectinDB);
string query = @"INSERT INTO AllocateRoom (DepartmnetId,CourseId,ClassRoomId,DayId,FromTime,ToTime,RoomStatus,InsertDate)
VALUES ('" + allocateClassroom.DepartmentId + "','" + allocateClassroom.CourseId + "','" + allocateClassroom.RoomId + "','" + allocateClassroom.DayId + "','" + allocateClassroom.FromTime.ToString("hh:mm tt") + "','" + allocateClassroom.ToTime.ToString("hh:mm tt ") + "','True',GETDATE())";
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
rowAffected = cmd.ExecuteNonQuery();
con.Close();
return(rowAffected);
}
public int UnAllocateClassRoomGateway(AllocateClassroomModel allocateRoom)
{
SqlConnection con = new SqlConnection(connectionString);
string query = "insert into UnAllocateClassrooms values ('" + allocateRoom.DepartmentId + "','" +
allocateRoom.CourseId + "','" + allocateRoom.RoomNoId + "','" + allocateRoom.DayId + "','" +
allocateRoom.FromDate + "','" + allocateRoom.ToDate + "','" + allocateRoom.FromAmOrPm + "','" +
allocateRoom.ToAmOrPm + "')";
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
int rowAffcet = cmd.ExecuteNonQuery();
con.Close();
return(rowAffcet);
}
public string AllocateClassRoomManager(AllocateClassroomModel allocateClassroomModel)
{
if (allocateClassroomGateway.CanAllocateClassRoom(allocateClassroomModel) > 0 || allocateClassroomGateway.CanAllocateClassRoom2(allocateClassroomModel) > 0)
{
return
("Sorry, You can't Allocate room in this schedule, This Schedule Haven ,Please Try with Anther Shedule.");
}
else
{
if (allocateClassroomGateway.AllocateClassRoomGateway(allocateClassroomModel) > 0 && allocateClassroomGateway.UnAllocateClassRoomGateway(allocateClassroomModel) > 0)
{
return("Save Successfull");
}
else
{
return("Save Fail");
}
}
}
public ActionResult Allocate(AllocateClassroomModel allocateClass)
{
ViewBag.Title = "Allocate Classroom";
ViewBag.departments = registerStudentManager.GetDepartmentList();
ViewBag.rooms = allocateClassroomManager.ViewRoom();
ViewBag.days = allocateClassroomManager.DayView();
if (IsRoomFree(allocateClass.DayId, allocateClass.RoomId, allocateClass.FromTime, allocateClass.ToTime) ==
false)
{
ViewBag.message = allocateClassroomManager.Allocate(allocateClass);
}
else
{
ViewBag.message = "Room is not available";
}
return(View());
}
//---------------------------------------------------------------------------------//
public int Allocate(AllocateClassroomModel allocateClassroom)
{
query =
"INSERT INTO AllocateClassroomTable " +
"VALUES(@departmentId,@courseId,@roomId,@dayId,@fromTime,@toTime,1)";
Command = new SqlCommand(query, Connection);
Command.Parameters.AddWithValue("@departmentId", allocateClassroom.DepartmentId);
Command.Parameters.AddWithValue("@courseId", allocateClassroom.CourseId);
Command.Parameters.AddWithValue("@roomId", allocateClassroom.RoomId);
Command.Parameters.AddWithValue("@dayId", allocateClassroom.DayId);
Command.Parameters.AddWithValue("@fromTime", allocateClassroom.FromTime);
Command.Parameters.AddWithValue("@toTime", allocateClassroom.ToTime);
Connection.Open();
int rowEffect = Command.ExecuteNonQuery();
Connection.Close();
return(rowEffect);
}
public int CanAllocateClassRoom2(AllocateClassroomModel allocateClassRooms)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = " select * from AllocateClassroom where RoomNoId='" + allocateClassRooms.RoomNoId + "' and DayId= '" + allocateClassRooms.DayId + "' and ( ('" + string.Format("{0:hh.mm}", allocateClassRooms.FromDate) + "'>=FromDate and '" + string.Format("{0:hh.mm}", allocateClassRooms.FromDate) + "'<ToDate )and (FromAmOrPm='AM' or FromAmOrPm='" + allocateClassRooms.FromAmOrPm + "')) and ('" + string.Format("{0:hh.mm}", allocateClassRooms.ToDate) + "'<=ToDate and ToAmOrPm='" + allocateClassRooms.ToAmOrPm + "')";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Close();
connection.Close();
return(1);
}
else
{
reader.Close();
connection.Close();
return(0);
}
}
public ActionResult AllocateClassRoom(AllocateClassroomModel allocate)
{
var allocateClassRoom = allocateClassroomManager.GetScheduleTimeOverlape();
if (allocateClassRoom != null)
{
var allocateRoomOverlapList =
allocateClassRoom.Where(a => a.DayId == allocate.DayId || (a.FromTime > allocate.FromTime && a.ToTime < allocate.ToTime)).ToList();
// if ((roomAllocation.StartTime >= allocation.StartTime && roomAllocation.StartTime < allocation.EndTime)
// || (roomAllocation.EndTime > allocation.StartTime && roomAllocation.EndTime <= allocation.EndTime) && roomAllocation.Status=="Allocated")
if (allocateRoomOverlapList.Count == 0)
{
if (allocateClassroomManager.AllocateClassroom(allocate) > 0)
{
ViewBag.message = "Allocate Successfully!";
}
else
{
ViewBag.message = "Failed to Allocate!";
}
}
else
{
ViewBag.message = "time Overlaped";
}
}
else
{
ViewBag.message = "No course assgined!";
}
ViewBag.Department = allocateClassroomManager.GetAllDepartment();
ViewBag.RoomNo = allocateClassroomManager.GetallRoom();
ViewBag.Day = allocateClassroomManager.GetAllDay();
return(View());
}
public int AllocateClassroom(AllocateClassroomModel allocateClassroom)
{
return(allocateClassroomGateway.SaveAllocateClassroom(allocateClassroom));
}
public ActionResult AllocateRoom(AllocateClassroomModel allocateClassroomModel)
{
ViewBag.message = allocateClassroomManager.AllocateClassRoomManager(allocateClassroomModel);
TempData["Msg"] = ViewBag.message;
return(RedirectToAction("AllocateRoom"));
}
