private void saveButton_Click(object sender, EventArgs e)
{
string sqlCommandText = "SELECT workingHours FROM All_Classrooms WHERE number = @oldNumber";
SqlCommand sql = new SqlCommand(sqlCommandText, parent.Connection);
sql.Parameters.Add(new SqlParameter("@oldNumber", oldClassroomNum));
SqlDataReader rdr = sql.ExecuteReader();
try
{
if (rdr.Read())
{
string[] workingHoursString = ((string)rdr["workingHours"]).Split('-');
if (int.Parse(changeStartWorkingTextBox.Text) > int.Parse(workingHoursString[0]) ||
int.Parse(changeEndWorkingTextBox.Text) < int.Parse(workingHoursString[1]))
{
// working hours changed
rdr.Close();
sqlCommandText = "SELECT * FROM All_Reservations WHERE" +
" classroomNumberID = (SELECT classroomID FROM All_Classrooms WHERE number = @oldNumber)";
sql.CommandText = sqlCommandText;
rdr = sql.ExecuteReader();
List <int> resIDsForDelete = new List <int>(),
resIDsForUpdateStart = new List <int>(),
resIDsForUpdateEnd = new List <int>();
List <string> resTimeUpdateStart = new List <string>(),
resTimeUpdateEnd = new List <string>();
while (rdr.Read())
{
string[] dateString = ((string)rdr["ev_date"]).Split('.');
DateTime date1 = new DateTime(int.Parse(dateString[2]), int.Parse(dateString[1]), int.Parse(dateString[0])),
nowDateTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
if (date1.CompareTo(nowDateTime) > -1)
{
// if date of reservation is grater than today
// delete or change ev time
string[] res_time = ((string)rdr["ev_time"]).Split('-');
if (int.Parse(changeStartWorkingTextBox.Text) >= int.Parse(res_time[1]) ||
int.Parse(changeEndWorkingTextBox.Text) <= int.Parse(res_time[0]))
{
// if reservation time is out of classroom working time
resIDsForDelete.Add((int)rdr["reservationID"]);
}
else if (int.Parse(changeStartWorkingTextBox.Text) > int.Parse(res_time[0]))
{
// if reservation start time is less than new start working hour
resIDsForUpdateStart.Add((int)rdr["reservationID"]);
resTimeUpdateStart.Add((string)rdr["ev_time"]);
}
else if (int.Parse(changeEndWorkingTextBox.Text) < int.Parse(res_time[1]))
{
// if reservation start time is grater than new end working hour
resIDsForUpdateEnd.Add((int)rdr["reservationID"]);
resTimeUpdateEnd.Add((string)rdr["ev_time"]);
}
}
}
rdr.Close();
if (resIDsForDelete.Count > 0)
{
sqlCommandText = "DELETE FROM All_Reservations WHERE reservationID = @reservationID";
sql.CommandText = sqlCommandText;
sql.Parameters.Clear();
for (int i = 0; i < resIDsForDelete.Count; i++)
{
sql.Parameters.Add(new SqlParameter("@reservationID", resIDsForDelete[i]));
sql.ExecuteNonQuery();
sql.Parameters.Clear();
}
}
if (resIDsForUpdateEnd.Count > 0)
{
sqlCommandText = "UPDATE All_Reservations SET ev_time = @ev_time WHERE reservationID = @reservationID";
sql.CommandText = sqlCommandText;
sql.Parameters.Clear();
for (int i = 0; i < resIDsForUpdateEnd.Count; i++)
{
string[] time = resTimeUpdateEnd[i].Split('-');
sql.Parameters.Add(new SqlParameter("@ev_time", time[0] + '-' + changeEndWorkingTextBox.Text));
sql.Parameters.Add(new SqlParameter("@reservationID", resIDsForUpdateEnd[i]));
sql.ExecuteNonQuery();
sql.Parameters.Clear();
}
}
if (resIDsForUpdateStart.Count > 0)
{
sqlCommandText = "UPDATE All_Reservations SET ev_time = @ev_time WHERE reservationID = @reservationID";
sql.CommandText = sqlCommandText;
sql.Parameters.Clear();
for (int i = 0; i < resIDsForUpdateStart.Count; i++)
{
string[] time = resTimeUpdateStart[i].Split('-');
sql.Parameters.Add(new SqlParameter("@ev_time", changeStartWorkingTextBox.Text + '-' + time[1]));
sql.Parameters.Add(new SqlParameter("@reservationID", resIDsForUpdateStart[i]));
sql.ExecuteNonQuery();
sql.Parameters.Clear();
}
}
}
}
}
catch (Exception)
{
}
finally
{
if (!rdr.IsClosed)
{
rdr.Close();
}
}
// command
sqlCommandText = "UPDATE All_Classrooms SET number = @number, blackboard = @blackboard, computers = @computers, " +
"projector = @projector, laboratory = @laboratory, capacity = @capacity, workingHours = @workingHours, comment = @comment, notice = @notice" +
" WHERE number = @oldNumber";
// make sql command
sql.CommandText = sqlCommandText;
sql.Parameters.Clear();
sql.Parameters.Add(new SqlParameter("@number", changeCNumTextBox.Text));
sql.Parameters.Add(new SqlParameter("@blackboard", changeBlackboardCheckBox.Checked));
sql.Parameters.Add(new SqlParameter("@computers", changeComputersCheckBox.Checked));
sql.Parameters.Add(new SqlParameter("@projector", changeProjectorCheckBox.Checked));
sql.Parameters.Add(new SqlParameter("@laboratory", changeLaboratoryCheckBox.Checked));
sql.Parameters.Add(new SqlParameter("@capacity", int.Parse(changeCapacityTextBox.Text)));
sql.Parameters.Add(new SqlParameter("@workingHours", changeStartWorkingTextBox.Text + "-" + changeEndWorkingTextBox.Text));
sql.Parameters.Add(new SqlParameter("@comment", changeCommentTextBox.Text));
sql.Parameters.Add(new SqlParameter("@notice", changeNoticeTextBox.Text));
sql.Parameters.Add(new SqlParameter("@oldNumber", oldClassroomNum));
// execute command
sql.ExecuteNonQuery();
parent.setNewClassroomsForAutocomplete();
this.Hide();
}
