//// check time exists
//public bool ToTimeIsExistsSameDayAndCourse(AllocateClassRoom allocateClassRoom)
//{
// Connection.Open();
// string query = "SELECT * FROM AllocateClassRoom WHERE CourseId = @courseId AND DayId = @dayId AND ToHour = @fromHour AND TYPE = 'ALLOCATED'";
// Command = new SqlCommand(query, Connection);
// Command.Parameters.AddWithValue("@courseId", allocateClassRoom.CourseId);
// Command.Parameters.AddWithValue("@dayId", allocateClassRoom.DayId);
// Command.Parameters.AddWithValue("@FromHour", allocateClassRoom.FromHour);
// Reader = Command.ExecuteReader();
// bool isExists = Reader.HasRows;
// Reader.Close();
// Connection.Close();
// return isExists;
//}
//// check time exists
//public bool FromTimeIsExistsForSameDayAndCourse(AllocateClassRoom allocateClassRoom)
//{
// Connection.Open();
// string query = "SELECT * FROM AllocateClassRoom WHERE CourseId = @courseId AND DayId = @dayId AND FromHour = @toHour AND TYPE = 'ALLOCATED'";
// Command = new SqlCommand(query, Connection);
// Command.Parameters.AddWithValue("@courseId", allocateClassRoom.CourseId);
// Command.Parameters.AddWithValue("@dayId", allocateClassRoom.DayId);
// Command.Parameters.AddWithValue("@toHour", allocateClassRoom.ToHour);
// Reader = Command.ExecuteReader();
// bool isExists = Reader.HasRows;
// Reader.Close();
// Connection.Close();
// return isExists;
//}
//// get to time min by from hour
//public int GetToTimeMinByFromHourSameDaySameCourse(AllocateClassRoom allocateClassRoom)
//{
// Connection.Open();
// string query = "SELECT * FROM AllocateClassRoom WHERE CourseId = @courseId AND DayId = @dayId AND ToHour = @fromHour AND TYPE = 'ALLOCATED'";
// Command = new SqlCommand(query, Connection);
// Command.Parameters.AddWithValue("@CourseId", allocateClassRoom.CourseId);
// Command.Parameters.AddWithValue("@dayId", allocateClassRoom.DayId);
// Command.Parameters.AddWithValue("@FromHour", allocateClassRoom.FromHour);
// Reader = Command.ExecuteReader();
// int toMin = 0;
// while (Reader.Read())
// {
// toMin = Convert.ToInt32(Reader["ToMin"]);
// }
// Reader.Close();
// Connection.Close();
// return toMin;
//}
//// check time exists
//public bool FromTimeIsExistsSameDayAndCourse(AllocateClassRoom allocateClassRoom)
//{
// Connection.Open();
// string query = "SELECT * FROM AllocateClassRoom WHERE CourseId = @courseId AND DayId = @dayId AND FromHour = @toHour AND TYPE = 'ALLOCATED'";
// Command = new SqlCommand(query, Connection);
// Command.Parameters.AddWithValue("@courseId", allocateClassRoom.CourseId);
// Command.Parameters.AddWithValue("@dayId", allocateClassRoom.DayId);
// Command.Parameters.AddWithValue("@toHour", allocateClassRoom.ToHour);
// Reader = Command.ExecuteReader();
// bool isExists = Reader.HasRows;
// Reader.Close();
// Connection.Close();
// return isExists;
//}
//public int GetFromTimeMinByToHourSameDayAndCourse(AllocateClassRoom allocateClassRoom)
//{
// Connection.Open();
// string query = "SELECT * FROM AllocateClassRoom WHERE CourseId = @courseId AND DayId = @dayId AND FromHour = @toHour AND TYPE = 'ALLOCATED'";
// Command = new SqlCommand(query, Connection);
// Command.Parameters.AddWithValue("@courseId", allocateClassRoom.CourseId);
// Command.Parameters.AddWithValue("@dayId", allocateClassRoom.DayId);
// Command.Parameters.AddWithValue("@toHour", allocateClassRoom.ToHour);
// Reader = Command.ExecuteReader();
// int fromMin = 0;
// while (Reader.Read())
// {
// fromMin = Convert.ToInt32(Reader["FromMin"]);
// }
// Reader.Close();
// Connection.Close();
// return fromMin;
//}
//--------------------------------------------------------------
//// VIEW CLASS SCHEDULE
//// get class schedule by department id
public List <ViewClassScheduleViewModel> GetAllocateClassRoomsByDepartmentId(int departmentId)
{
Connection.Open();
string query = " SELECT Course.Code AS Code, Course.Name AS Name, Room.RoomNo AS Room, Day.DayName AS DayName, AllocateClassRoom.StartTime AS StartTime, AllocateClassRoom.EndTime AS EndTime FROM Course LEFT JOIN AllocateClassRoom ON Course.Id = AllocateClassRoom.CourseId AND Type = 'ALLOCATED' LEFT JOIN Day ON Day.Id = AllocateClassRoom.DayId LEFT JOIN Room ON Room.Id = AllocateClassRoom.RoomId WHERE Course.DepartmentId = @departmentId";
Command = new SqlCommand(query, Connection);
Command.Parameters.AddWithValue("@departmentId", departmentId);
Reader = Command.ExecuteReader();
List <ViewClassScheduleViewModel> allocateClassRooms = new List <ViewClassScheduleViewModel>();
while (Reader.Read())
{
ViewClassScheduleViewModel allocateClassRoom = new ViewClassScheduleViewModel();
allocateClassRoom.CourseCode = Reader["Code"].ToString();
allocateClassRoom.CourseName = Reader["Name"].ToString();
allocateClassRoom.RoomNo = Reader["Room"].ToString();
allocateClassRoom.DayName = Reader["DayName"].ToString();
allocateClassRoom.StartTime = Reader["StartTime"].ToString();
allocateClassRoom.EndTime = Reader["EndTime"].ToString();
allocateClassRooms.Add(allocateClassRoom);
}
Reader.Close();
Connection.Close();
return(allocateClassRooms);
}
// set time format for view schedule
public string ConvertSpecificFormat(ViewClassScheduleViewModel model)
{
if (!model.StartTime.Equals("") && !model.EndTime.Equals(""))
{
return("R. No: " + model.RoomNo + ", " + model.DayName + ", " + model.StartTime + " - " + model.EndTime);
}
else
{
return(null);
}
}
