public int CheckRoomAndTimeScheduleOverlap(Schedule aSchedule)
{
try
{
int status = 0;
int scheduleStatus = 0;
connection.Open();
string query = "SELECT count(*) FROM t_ScheduleClass WHERE ScheduleStatus=@status and (BuildingId=@buildingId AND RoomId=@roomId AND DayId=@dayId)AND((StartTime<=@startTime AND EndingTime>=@startTime)or(StartTime<=@endingTime AND EndingTime>=@endingTime))";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@status", scheduleStatus);
command.Parameters.AddWithValue("@buildingId", aSchedule.BuildingId);
command.Parameters.AddWithValue("@roomId", aSchedule.RoomId);
command.Parameters.AddWithValue("@dayId", aSchedule.DayId);
command.Parameters.AddWithValue("@startTime", aSchedule.StartTime);
command.Parameters.AddWithValue("@endingTime", aSchedule.EndingTime);
SqlDataReader scheduleReader = command.ExecuteReader();
while (scheduleReader.Read())
{
if (Convert.ToInt16(scheduleReader[0].ToString()) != 0)
status++;
}
return status;
}
finally
{
connection.Close();
}
}
private bool CheckRoomAndTimeScheduleOverlap(Schedule aSchedule)
{
aScheduleGateway = new ScheduleGateway();
int status = aScheduleGateway.CheckRoomAndTimeScheduleOverlap(aSchedule);
if (status > 0)
return true;
return false;
}
private bool CheckDepartmentAndSubjectOverlap(Schedule aSchedule)
{
aScheduleGateway = new ScheduleGateway();
int status = aScheduleGateway.CheckDepartmentAndSubjectOverlap(aSchedule);
if (status > 0)
return true;
return false;
}
public string SaveClassSchedule(Schedule aSchedule)
{
aScheduleGateway = new ScheduleGateway();
if (!CheckRoomAndTimeScheduleOverlap(aSchedule))
if (!CheckDepartmentAndSubjectOverlap(aSchedule))
return aScheduleGateway.SaveClassSchedule(aSchedule);
else
return "Violation of Department or semester ";
else
return "Violation of Room or Time or day or building";
}
public string SaveClassSchedule(Schedule aSchedule)
{
try
{
connection.Open();
string query = "INSERT INTO t_ScheduleClass VALUES(@date,@departmentId,@semesterId,@courseId,@buildingId,@roomId,@fromTime,@toTime,@dayId,@status)";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@date", aSchedule.Scheduledate);
command.Parameters.AddWithValue("@departmentId", aSchedule.DepartmentId);
command.Parameters.AddWithValue("@semesterId", aSchedule.SemesterId);
command.Parameters.AddWithValue("@courseId", aSchedule.CourseId);
command.Parameters.AddWithValue("@buildingId", aSchedule.BuildingId);
command.Parameters.AddWithValue("@roomId", aSchedule.RoomId);
command.Parameters.AddWithValue("@fromTime", aSchedule.StartTime);
command.Parameters.AddWithValue("@toTime", aSchedule.EndingTime);
command.Parameters.AddWithValue("@dayId", aSchedule.DayId);
command.Parameters.AddWithValue("@status", aSchedule.ScheduleStatus);
command.ExecuteNonQuery();
return "Saved";
}
finally
{
connection.Close();
}
}
public List<Schedule> GetTeacherAllSchedules(List<int> coursesId)
{
try
{
int scheduleStatus = 0;
List<Schedule> schedules = new List<Schedule>();
foreach (int courseId in coursesId)
{
try
{
connection.Open();
string query = "SELECT dayId,startTime,endingTime FROM t_ScheduleClass WHERE CourseId=@courseId and ScheduleStatus=@status";
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@courseId", courseId);
command.Parameters.AddWithValue("@status", scheduleStatus);
SqlDataReader teacherCourseScheduleReader = command.ExecuteReader();
while (teacherCourseScheduleReader.Read())
{
Schedule aSchedule = new Schedule();
aSchedule.DayId = Convert.ToInt16(teacherCourseScheduleReader[0].ToString());
aSchedule.StartTime = float.Parse(teacherCourseScheduleReader[1].ToString());
aSchedule.EndingTime = float.Parse(teacherCourseScheduleReader[2].ToString());
schedules.Add(aSchedule);
}
}
finally
{
connection.Close();
}
}
return schedules;
}
finally
{
connection.Close();
}
}
protected void saveButton_Click(object sender, EventArgs e)
{
if(!IsValid)
{return;}
try
{
Schedule aSchedule = new Schedule();
aScheduleManager = new ScheduleManager();
aSchedule.Scheduledate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
aSchedule.DepartmentId = Convert.ToInt16(departmentDropDownList.Text);
aSchedule.SemesterId = Convert.ToInt16(semesterDropDownList.Text);
aSchedule.CourseId = Convert.ToInt16(courseDropDownList.Text);
aSchedule.BuildingId = Convert.ToInt16(buildingDropDownList.Text);
aSchedule.RoomId = roomDropDownList.Text;
aSchedule.StartTime = float.Parse(startTimeDropDownList.Text);
aSchedule.EndingTime = float.Parse(endingTimeDropDownList.Text);
aSchedule.DayId = Convert.ToInt16(dayDropDownList.Text);
aSchedule.ScheduleStatus = 0;
string msg = aScheduleManager.SaveClassSchedule(aSchedule);
if (msg == "Saved")
{
msgLabel.ForeColor = Color.Green;
msgLabel.Text = msg;
}
else
{
msgLabel.ForeColor = Color.Red;
msgLabel.Text = msg;
}
}
catch (SqlException sqlException)
{
msgLabel.ForeColor = Color.Red;
msgLabel.Text = "Database error.See details error: " + sqlException.Message;
}
catch (Exception exception)
{
msgLabel.ForeColor = Color.Red;
string errorMessage = "Unknow error occured.";
errorMessage += exception.Message;
if (exception.InnerException != null)
{
errorMessage += exception.InnerException.Message;
}
msgLabel.Text = errorMessage;
}
}