private void dgvOutput_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
// Get the row number clicked
var index = e.RowIndex;
// Get the passengerID of the passenger and pass to command
int selectedID = Convert.ToInt32(dgvOutput.Rows[index].Cells[0].Value);
//select all passenger info from all three tables that matches passengerID
using (var con = new SqlConnection(DBObjects.conString))
{
con.Open();
command = new SqlCommand
("SELECT p.PassengerID as ID, s.SeatID, p.PassengerName as Name, s.SeatRow, s.SeatColumn, " +
"p.PassengerOnWaitingList as OnWaitingList " +
"FROM(Passengers p " +
"INNER JOIN PassengerSeats ps ON p.PassengerID = ps.PassengerID) " +
"INNER JOIN Seats s ON s.SeatID = ps.SeatID " +
"WHERE p.PassengerID = @PassengerID " +
"UNION " +
"SELECT p.PassengerID, NULL, p.PassengerName, NULL, NULL, p.PassengerOnWaitingList " +
"FROM Passengers p " +
"WHERE p.PassengerOnWaitingList = 1 AND p.PassengerID = @PassengerID " +
"ORDER BY s.SeatRow, s.SeatColumn", con);
Methods.AddParameters(command, "PassengerID", selectedID);
//execute command and place results in datatable and pass to EditPassInfo form
DataTable dTable = new DataTable();
dTable.Load(command.ExecuteReader());
EditPassInfo frm = new EditPassInfo(dTable);
frm.ShowDialog();
Close();
}
}
// Check if seat is taken
public bool IsSeatAlreadyTaken(string seatRow, string seatColumn)
{
using (var con = new SqlConnection(DBObjects.conString))
{
con.Open();
command = new SqlCommand("SELECT * FROM SEATS WHERE SEATROW = @SEATROW AND SEATCOLUMN = @SEATCOLUMN " +
"AND ISTAKEN = 0", con);
Methods.AddParameters(command, "SEATROW", seatRow);
Methods.AddParameters(command, "SEATCOLUMN", seatColumn);
reader = command.ExecuteReader();
return(!reader.HasRows ? true : false);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
using (var con = new SqlConnection(DBObjects.conString))
{
con.Open();
//Ask user if they want to delete passengers
var msg = MessageBox.Show("Are you sure you want to delete " + txtName.Text + ". This action cannot be undone",
"Delete Record", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (msg == DialogResult.No)
{
return;
}
//delete passenger from passenger and passengerseats
command = new SqlCommand("DELETE FROM PASSENGERS WHERE PassengerID = @PassengerID", con);
Methods.AddParameters(command, "PassengerID", txtPassID);
command.ExecuteNonQuery();
command = new SqlCommand("DELETE FROM PASSENGERSEATS WHERE PassengerID = @PassengerID", con);
Methods.AddParameters(command, "PassengerID", txtPassID);
command.ExecuteNonQuery();
if (!txtSeatID.Text.Equals(""))
{
//The seat still exists so we need to update the seats table and mark the seat as not taken
command = new SqlCommand("UPDATE SEATS SET IsTaken = 0 WHERE SeatID = @SeatID", con);
Methods.AddParameters(command, "SeatID", txtSeatID);
command.ExecuteNonQuery();
}
MessageBox.Show("Passenger has been deleted.",
"Record Deleted", MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
}
}
// Add a passenger
private void btnAddPass_Click(object sender, EventArgs e)
{
// Passenger and seat objects
passenger = new Passenger();
seat = new Seat();
// see what seat column was selected
var checkBtn = grpSeatsColumn.Controls.OfType <RadioButton>().FirstOrDefault(r => r.Checked); // use lambda expression to check the first button selected then assign to var
//Validate input - name, seat row & column
if (!passenger.IsValidPass(txtPassName.Text) || cmbSeatRow.SelectedIndex == -1 || checkBtn == null)
{
MessageBox.Show("Please enter a valid name and seat", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// Check if plane is full. If full place passeneger on waiting list
if (seat.IsPlaneFull())
{
var msg = MessageBox.Show("The plane is currently full. Would you like to be added to the waiting list?",
"Plane Is Full", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (msg == DialogResult.No)
{
return;
}
using (var con = new SqlConnection(DBObjects.conString))
{
con.Open();
command = new SqlCommand("INSERT INTO PASSENGERS (PassengerName, PassengerOnWaitingList)" +
"VALUES (@PassengerName, 1)", con);
Methods.AddParameters(command, "PassengerName", txtPassName);
command.ExecuteNonQuery(); // dont need reader since we are just inserting into db
command = new SqlCommand("INSERT INTO PassengerSeats (PassengerID, SeatID)" +
"SELECT MAX(PassengerID), 0 FROM PASSENGERS", con);
command.ExecuteNonQuery();
MessageBox.Show("Passenger " + txtPassName.Text + " was added to the waiting list.",
"Waiting List", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return;
}
//Check if seat is taken if so exit so user can select a different seat
if (seat.IsSeatAlreadyTaken(cmbSeatRow.SelectedItem.ToString(), checkBtn.Text))
{
MessageBox.Show("The seat you selected is already taken. Please select a different seat",
"Seat Taken", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
// If all is Ok add passenger and seat selected to db
using (var con = new SqlConnection(DBObjects.conString))
{
con.Open();
//Insert new passenger into db
command = new SqlCommand("INSERT INTO PASSENGERS (PassengerName, PassengerOnWaitingList)" +
"VALUES (@PassengerName, 0)", con);
Methods.AddParameters(command, "PassengerName", txtPassName);
command.ExecuteNonQuery();
//Update seat to taken in seats db
command = new SqlCommand("Update SEATS SET IsTaken = 1 WHERE " +
"SeatRow = @SeatRow AND SeatColumn = @SeatColumn", con);
Methods.AddParameters(command, "SeatRow", cmbSeatRow);
Methods.AddParameters(command, "SeatColumn", checkBtn);
command.ExecuteNonQuery();
//Insert passenger and seat ID in passengerSeats table
command = new SqlCommand("INSERT INTO PassengerSeats (SeatID, PassengerID) " +
"Select Seats.SeatID, (SELECT MAX(PassengerID) FROM PASSENGERS)" +
" FROM SEATS WHERE Seats.SeatRow = @SeatRow AND Seats.SeatColumn = " +
"@SeatColumn", con);
Methods.AddParameters(command, "SeatRow", cmbSeatRow);
Methods.AddParameters(command, "SeatColumn", checkBtn);
command.ExecuteNonQuery();
MessageBox.Show("Passenger has been added.",
"Added Passenger", MessageBoxButtons.OK, MessageBoxIcon.Information);
PopAirplane();
}
}
//edit the passenger record
private void btnEdit_Click(object sender, EventArgs e)
{
//Validate input from passenger
//check that passenger name has been entered
if (txtName.Text.Trim().Equals(""))
{
MessageBox.Show("Passenger name is required.", "Invalid Input",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Passenger cant have a seat and be on waiting list
if (chbOnList.Checked && (cmbRow.SelectedIndex > 0 || cmbColumn.SelectedIndex > 0))
{
MessageBox.Show("Passenger cannot be on a waiting list and have a seat assigned.", "Invalid Input"
, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Passenger must have a seat or on waiting list
if (!chbOnList.Checked && (cmbRow.SelectedIndex <= 0 || cmbColumn.SelectedIndex <= 0))
{
MessageBox.Show("Passenger must have a seat assigned or be on the waiting list.", "Invalid Input"
, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// Update passenger record
using (var con = new SqlConnection(DBObjects.conString))
{
con.Open();
command = new SqlCommand("SELECT SeatID, IsTaken FROM Seats WHERE " +
"SeatRow = @SeatRow AND SeatColumn = @SeatColumn", con);
command.Parameters.Add(new SqlParameter("SeatRow", cmbRow.SelectedItem));
command.Parameters.Add(new SqlParameter("SeatColumn", cmbColumn.SelectedItem));
reader = command.ExecuteReader();
var newSeatID = 0;
bool newIsTaken = false;
while (reader.Read())
{
newSeatID = Convert.ToInt32(reader["SeatID"]);
newIsTaken = Convert.ToBoolean(reader["IsTaken"]);
}
//check if only the name is being updated.
//If not, exit because the user needs to pick a different seat
int oldID = 0;
if (txtSeatID.Text.Equals(""))
{
oldID = 0;
}
else
{
oldID = Convert.ToInt32(txtSeatID.Text);
}
if (!txtSeatID.Equals(""))
{
if (newSeatID != oldID && newIsTaken)
{
MessageBox.Show("Seat is already taken", "Seat Taken", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
return;
}
}
else
{
newSeatID = 0;
}
//update passenger's name
command = new SqlCommand("UPDATE Passengers SET PassengerName = @PassengerName, " +
"PassengerOnWaitingList = @OnWaitingList WHERE PassengerID = @PassengerID", con);
Methods.AddParameters(command, "PassengerName", txtName);
Methods.AddParameters(command, "OnWaitingList", chbOnList);
Methods.AddParameters(command, "PassengerID", txtPassID);
command.ExecuteNonQuery();
//make original seat available
command = new SqlCommand("UPDATE Seats SET IsTaken = 0 WHERE " +
"seatID = @seatID", con);
Methods.AddParameters(command, "seatID", txtSeatID);
command.ExecuteNonQuery();
//make new seat taken
command = new SqlCommand("UPDATE Seats SET IsTaken = 1 WHERE " +
"seatID = @seatID", con);
Methods.AddParameters(command, "seatID", newSeatID);
command.ExecuteNonQuery();
//update old seatID with the new one
command = new SqlCommand("UPDATE PassengerSeats SET SeatID = @SeatID WHERE " +
"PassengerID = @PassengerID", con);
Methods.AddParameters(command, "SeatID", newSeatID);
Methods.AddParameters(command, "PassengerID", txtPassID);
command.ExecuteNonQuery();
MessageBox.Show("Record has been updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
}
}
