public int Save(AllocateClassrooms allocateClassrooms)
{
int rowAffect = -1;
bool isOverLapped = isOverlapp(allocateClassrooms.FromTime, allocateClassrooms.ToTime);
if (!isOverLapped)
{
string query = "INSERT INTO AssaignClassRoom (DepartmentId,CourseId,RoomNo,Day,FromTime,ToTIme,) VALUES " +
"(@DepartmentId,@CourseId,@RoomNo,@Day,@FromTime,@ToTime,@CourseCode,@CourseName)";
command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@DepartmentId", allocateClassrooms.DepartmentId);
command.Parameters.AddWithValue("@CourseId", allocateClassrooms.CourseId);
command.Parameters.AddWithValue("@RoomNo", allocateClassrooms.RoomNo);
command.Parameters.AddWithValue("@Day", allocateClassrooms.Day);
command.Parameters.AddWithValue("@FromTime", allocateClassrooms.FromTime);
command.Parameters.AddWithValue("@ToTime", allocateClassrooms.ToTime);
command.Parameters.AddWithValue("@CourseCode", allocateClassrooms.CourseCode);
command.Parameters.AddWithValue("@CourseName", allocateClassrooms.CourseName);
connection.Open();
rowAffect = command.ExecuteNonQuery();
connection.Close();
return(rowAffect);
}
else
{
return(rowAffect);
}
}
public List <AllocateClassrooms> GetTimeAllocate(int DayId, int RoomId, DateTime FromTime, DateTime ToTime)
{
string query = "SELECT * FROM AllocateClassrooms WHERE RoomId=" + RoomId + " AND DayId=" + DayId + "";
Command = new SqlCommand(query, Connection);
List <AllocateClassrooms> allocateClassroomses = new List <AllocateClassrooms>();
Connection.Open();
Reader = Command.ExecuteReader();
while (Reader.Read())
{
AllocateClassrooms allocate = new AllocateClassrooms();
allocate.Id = Convert.ToInt32(Reader["Id"]);
allocate.DepartmentId = Convert.ToInt32(Reader["DepartmentId"]);
allocate.CourseId = Convert.ToInt32(Reader["CourseId"]);
allocate.DayId = Convert.ToInt32(Reader["DayId"]);
allocate.RoomId = Convert.ToInt32(Reader["RoomId"]);
allocate.FromTime = Convert.ToDateTime(Reader["FromTime"]);
allocate.ToTime = Convert.ToDateTime(Reader["ToTime"]);
allocateClassroomses.Add(allocate);
}
Reader.Close();
Connection.Close();
return(allocateClassroomses);
}
public List <AllocateClassrooms> GetAllClassByDepartmentId(int id)
{
string query = "SELECT * FROM AssaignClassRoom where DepartmentId=@id";
command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@id", id);
connection.Open();
reader = command.ExecuteReader();
List <AllocateClassrooms> classList = new List <AllocateClassrooms>();
//teacherList.Add(new Teacher() { Id = 0, Name = "----Select----" });
while (reader.Read())
{
AllocateClassrooms Class = new AllocateClassrooms();
Class.Id = Convert.ToInt32(reader["Id"]);
Class.CourseCode = reader["CourseCode"].ToString();
Class.CourseName = reader["CourseName"].ToString();
Class.Day = reader["Day"].ToString();
Class.RoomNo = reader["RoomNo"].ToString();
Class.FromTime = Convert.ToDateTime(reader["FromTime"]);
Class.ToTime = Convert.ToDateTime(reader["ToTIme"]);
classList.Add(Class);
}
reader.Close();
connection.Close();
return(classList);
}
public List <AllocateClassrooms> GetAllocateInfo()
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "Select * from AllocateClassroom";
connection.Open();
SqlCommand command = new SqlCommand(query, connection);
SqlDataReader reader = command.ExecuteReader();
List <AllocateClassrooms> allocateInfoList = new List <AllocateClassrooms>();
while (reader.Read())
{
int id = (int)reader["Id"];
int roomnoId = (int)reader["RoomNoId"];
int dayId = (int)reader["DayId"];
DateTime ftime = (DateTime)reader["FromTime"];
DateTime ttime = (DateTime)reader["ToTime"];
//string name = reader["Name"].ToString();
AllocateClassrooms allocateClassrooms = new AllocateClassrooms(id, roomnoId, dayId, ftime, ttime);
allocateInfoList.Add(allocateClassrooms);
}
return(allocateInfoList);
}
public int UpdateCourse(AllocateClassrooms allocate)
{
string query = "UPDATE AllocateClassrooms SET Action=0 WHERE Id=" + allocate.Id + "";
Command = new SqlCommand(query, Connection);
Connection.Open();
int rowAffect = Command.ExecuteNonQuery();
Connection.Close();
return(rowAffect);
}
public string Save(AllocateClassrooms allocateClassrooms)
{
if (allocateClassRoomsGateway.Save(allocateClassrooms) > 0)
{
return("Save successful");
}
else
{
return("Time Overlaped");
}
}
//----------Unassign Classrooms---------------------
public string updateCourseAction(AllocateClassrooms allocate)
{
int rowAffect = allocateClassroomsGateway.UpdateCourse(allocate);
if (rowAffect > 0)
{
return("Unassign Successfully");
}
else
{
return("Unassign Failed");
}
}
public ActionResult Unassign(AllocateClassrooms allocate)
{
if (allocateClassroomsManager.isActionExist())
{
foreach (var courses in allocateClassroomsManager.GetAllCourseAction())
{
ViewBag.Output = allocateClassroomsManager.updateCourseAction(courses);
}
}
else
{
ViewBag.Output = "Already Unassigned";
}
return(View());
}
public int Save(AllocateClassrooms allocateClassrooms)
{
string query = "INSERT INTO AllocateClassrooms VALUES(@departmentId,@courseId,@roomId,@dayId,@fromTime,@toTime,@action)";
Command = new SqlCommand(query, Connection);
Command.Parameters.AddWithValue("@departmentId", allocateClassrooms.DepartmentId);
Command.Parameters.AddWithValue("@courseId", allocateClassrooms.CourseId);
Command.Parameters.AddWithValue("@roomId", allocateClassrooms.RoomId);
Command.Parameters.AddWithValue("@dayId", allocateClassrooms.DayId);
Command.Parameters.AddWithValue("@fromTime", allocateClassrooms.FromTime);
Command.Parameters.AddWithValue("@toTime", allocateClassrooms.ToTime);
Command.Parameters.AddWithValue("@action", 1);
Connection.Open();
int rowAffect = Command.ExecuteNonQuery();
Connection.Close();
return(rowAffect);
}
public ActionResult AllocateClassrooms(AllocateClassrooms allocateClassrooms)
{
var departmentList = allocateManager.GetAllDepartment();
ViewBag.ALLDepartment = departmentList;
var roomList = GetRoomList();
ViewBag.ALLRoom = roomList;
var dayList = GetDayList();
ViewBag.ALLDay = dayList;
string message = allocateManager.Insert(allocateClassrooms);
ViewBag.message = message;
return(View());
}
public ActionResult Save(AllocateClassrooms allocateClassrooms)
{
//if (ModelState.IsValid)
//{
string message = allocateClassroomsManager.Save(allocateClassrooms);
ViewBag.Departments = departmentManager.GetSelectListItemsForDropdown();
ViewBag.Rooms = roomManager.GetAllRoomCodeList();
ViewBag.Days = dayManager.GetAllDayList();
ViewBag.Message = message;
ModelState.Clear();
return(View());
//}
//else
//{
// ViewBag.Message = "Model is not valid";
// return View();
//}
}
//-------------------Unassign Allocateclass room---------------
public List <AllocateClassrooms> GetAllCourseByAction()
{
string query = "SELECT * FROM AllocateClassrooms WHERE Action=1";
Command = new SqlCommand(query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
List <AllocateClassrooms> allocateClassroomses = new List <AllocateClassrooms>();
while (Reader.Read())
{
AllocateClassrooms allocate = new AllocateClassrooms();
allocate.Id = Convert.ToInt32(Reader["Id"]);
allocate.Action = 1;
allocateClassroomses.Add(allocate);
}
Reader.Close();
Connection.Close();
return(allocateClassroomses);
}
public ActionResult SaveClassRooms(AllocateClassrooms allocateClassrooms)
{
ViewBag.departments = departmentManager.GetAllDepartment();
ViewBag.courses = courseManager.GetAllCourse();
ViewBag.days = getAllDays();
ViewBag.rooms = getAllRooms();
// List<Course> courseList= courseManager.GetAllCourse();
if (ModelState.IsValid)
{
int id = allocateClassrooms.CourseId;
Course courseList = courseManager.GetOneCourseById(id);
allocateClassrooms.CourseCode = courseList.Code;
allocateClassrooms.CourseName = courseList.Name;
string message = allocateClassRoomManager.Save(allocateClassrooms);
ViewBag.Message = message;
}
return(View());
}
public bool Insert(AllocateClassrooms allocateClassrooms)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "INSERT INTO AllocateClassroom (DepartmentId, CourseId, RoomNoId, DayId, FromTime, ToTime) VALUES (@departmentId, @courseId, @roomNoId, @dayId, @fromTime, @toTime)";
connection.Open();
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Clear();
command.Parameters.Add("departmentId", SqlDbType.Int);
command.Parameters["departmentId"].Value = allocateClassrooms.DepartmentId;
command.Parameters.Add("courseId", SqlDbType.Int);
command.Parameters["courseId"].Value = allocateClassrooms.CourseId;
command.Parameters.Add("roomNoId", SqlDbType.Int);
command.Parameters["roomNoId"].Value = allocateClassrooms.RoomNoId;
command.Parameters.Add("dayId", SqlDbType.VarChar);
command.Parameters["dayId"].Value = allocateClassrooms.DayId;
command.Parameters.Add("fromTime", SqlDbType.VarChar);
command.Parameters["fromTime"].Value = allocateClassrooms.FromTime;
command.Parameters.Add("toTime", SqlDbType.VarChar);
command.Parameters["toTime"].Value = allocateClassrooms.ToTime;
command.CommandText = query;
command.Connection = connection;
int rowAffected = command.ExecuteNonQuery();
if (rowAffected > 0)
{
return(true);
}
return(false);
}
public string Save(AllocateClassrooms allocateClassrooms)
{
if (allocateClassrooms.FromTime > allocateClassrooms.ToTime)
{
return("This Time is not Avaiable");
}
bool isTimeClassAllocateValid = IsTimeClassAllocateValid(allocateClassrooms.DayId, allocateClassrooms.RoomId, allocateClassrooms.FromTime, allocateClassrooms.ToTime);
if (isTimeClassAllocateValid == false)
{
int rowAffect = allocateClassroomsGateway.Save(allocateClassrooms);
if (rowAffect > 0)
{
return("Save Successfully");
}
else
{
return("Save Failed");
}
}
return("Overlapping not allowed");
}
public string Insert(AllocateClassrooms allocateRoom)
{
string message = "Save Failed!";
List <AllocateClassrooms> allocatedRooms = allocateGateWay.GetAllocateInfo();
string aFromTime = DateTime.Parse(allocateRoom.FromTime.ToString()).ToString("dd-MM-yyyy hh:mm tt");
string aToTime = DateTime.Parse(allocateRoom.ToTime.ToString()).ToString("dd-MM-yyyy hh:mm tt");
string bFromTime = aFromTime.Substring(10);
string bToTime = aToTime.Substring(10);
DateTime nFromTime = Convert.ToDateTime(bFromTime);
DateTime nToTime = Convert.ToDateTime(bToTime);
string scheduleTime = "";
bool found = false;
foreach (AllocateClassrooms allocate in allocatedRooms)
{
DateTime existingFromTime = Convert.ToDateTime(allocate.FromTime);
DateTime existingToTime = Convert.ToDateTime(allocate.ToTime);
bool matchRoom = allocate.RoomNoId == allocateRoom.RoomNoId;
bool matchDay = allocate.DayId == allocateRoom.DayId;
bool matchFromTime = existingFromTime == nFromTime;
bool matchToTime = existingToTime == nToTime;
bool coverExistingRange = nFromTime <existingFromTime& nToTime> existingFromTime;
bool coverExistingRange2 = nFromTime <existingToTime& nToTime> existingToTime;
bool toTimeInTheRange = nToTime > existingFromTime & nToTime < existingToTime;
bool fromTimeInTheRange = nFromTime > existingFromTime & nFromTime < existingToTime;
bool matchTime = matchFromTime || matchToTime;
bool coverTime = coverExistingRange || coverExistingRange2;
bool overlapTime = toTimeInTheRange || fromTimeInTheRange;
if (matchRoom & matchDay & (matchTime || overlapTime || coverTime))
{
//allocate.RoomNoId + " " + allocate.DayId + " " +
scheduleTime = allocate.FromTime + " - " + allocate.ToTime;
found = true;
}
}
if (found)
{
message = scheduleTime + " has been already scheduled!";
return(message);
}
bool isSaved = allocateGateWay.Insert(allocateRoom);
if (isSaved)
{
message = "Saved successfully!";
}
return(message);
}
