private void btnDelete_Click(object sender, EventArgs e)
{
try
{
DialogResult dialogResult = MessageBox.Show("Are you sure you want to delete this booking? " + Environment.NewLine +
$"Starting: {dtpStartDate.Value.ToLongDateString()}" + Environment.NewLine +
$"Ending: {dtpEndDate.Value.ToLongDateString()}" + Environment.NewLine +
$"for hotel: {cboHotel.Text}", "Delete Record", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (dialogResult == DialogResult.Yes)
{
if (GetSendData.SendData($"DELETE FROM Booking WHERE BookingId = {dtBooking.Rows[currentRecord]["BookingID"]}") > 0)
{
MessageBox.Show("Record Deleted");
currentRecord = 0;
LoadBookings();
PopulateField();
}
else
{
MessageBox.Show("An error occured while saving the data.");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
private void FillGustIdDropdown()
{
txtPhone.TextMaskFormat = MaskFormat.ExcludePromptAndLiterals;
string sql = string.Empty;
if (addMode)
{
sql = $"SELECT GuestId FROM Guest " +
$"WHERE FirstName {(string.IsNullOrWhiteSpace(txtFirstName.Text) ? " IS NOT NULL" : " = '" + txtFirstName.Text + "'" )} " +
$"AND LastName {(string.IsNullOrWhiteSpace(txtLastName.Text) ? " IS NOT NULL" : " = '" + txtLastName.Text + "'")} " +
$"AND Phone {(string.IsNullOrWhiteSpace(txtPhone.Text) ? " IS NOT NULL" : " = '" + txtPhone.Text + "'")} ";
}
else
{
sql = "SELECT GuestId FROM Guest";
}
txtPhone.TextMaskFormat = MaskFormat.IncludePromptAndLiterals;
DataTable dtGuestDrop = GetSendData.GetData(sql);
DataRow row = dtGuestDrop.NewRow();
row["GuestId"] = "No Guest";
dtGuestDrop.Rows.InsertAt(row, 0);
cboGuestId.DisplayMember = "GuestID";
cboGuestId.ValueMember = "GuestID";
cboGuestId.DataSource = dtGuestDrop;
cboGuestId.SelectedIndex = currentRecord;
}
private string GuestIdGenerator()
{
DataTable dt = GetSendData.GetData("SELECT IDENT_CURRENT('hotel')");
string lastInsertedUid = dt.Rows[0][0].ToString();
if (lastInsertedUid == "" || lastInsertedUid.Length < 10)
{
lastInsertedUid = "AAA00000000";
}
string lastGuestStringID = lastInsertedUid.Substring(0, 3);
int lastGuestNumberId = Convert.ToInt32(lastInsertedUid.Substring(3, 7));
if (lastGuestNumberId == 9999999)
{
lastGuestNumberId = 0;
lastGuestStringID = GuestStringID(lastGuestStringID);
}
string newGuestId = (lastGuestNumberId + 1).ToString();
while (newGuestId.Length < 7)
{
newGuestId = '0' + newGuestId;
}
newGuestId = lastGuestStringID + newGuestId;
return(newGuestId.ToUpper());
}
private void btnDelete_Click(object sender, EventArgs e)
{
try
{
DialogResult dialogResult = MessageBox.Show("Are you sure you want to delete: " + Environment.NewLine +
$"Room number: {txtRoomNumber.Text}?", "Delete Record", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (dialogResult == DialogResult.Yes)
{
if (Convert.ToInt16(GetSendData.GetScalarValue($"SELECT COUNT(*) FROM Booking WHERE RoomId = {dtRoom.Rows[currentRecord]["RoomID"]}")) > 0)
{
MessageBox.Show("Cannot delete room that has records in booking.", "Delete Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (GetSendData.SendData($"DELETE FROM Room WHERE RoomId = {dtRoom.Rows[currentRecord]["RoomID"]}") > 0)
{
MessageBox.Show("Record Deleted");
currentRecord = 0;
LoadRoom();
PopulateField();
}
else
{
MessageBox.Show("An error occured while saving the data.");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
if (ValidateChildren(ValidationConstraints.Enabled))
{
string sql = "";
ToggleControls(true);
txtPhone.TextMaskFormat = MaskFormat.ExcludePromptAndLiterals;
if (addMode)
{
sql = $"INSERT INTO Guest VALUES (" +
$"'{generateGuestId()}', " +
$"'{replaceApostrophes(txtFirstName.Text)}', " +
$"'{replaceApostrophes(txtLastName.Text)}', " +
$"'{replaceApostrophes(txtStreetAddress.Text)}', " +
$"'{replaceApostrophes(txtCity.Text)}', " +
$"'{cboProvince.SelectedValue}', " +
$"'{replaceApostrophes(txtPostalCode.Text)}', " +
$"{replaceApostrophes(txtPhone.Text)}, " +
$"'{replaceApostrophes(txtEmail.Text)}', " +
$"{Convert.ToByte(chkPreferred.Checked)})";
}
else //update
{
sql = $"UPDATE Guest SET " +
$"FirstName = '{replaceApostrophes(txtFirstName.Text)}', " +
$"LastName = '{replaceApostrophes(txtLastName.Text)}', " +
$"StreetAddress = '{replaceApostrophes(txtStreetAddress.Text)}', " +
$"City = '{replaceApostrophes(txtCity.Text)}', " +
$"Province = '{cboProvince.SelectedValue}', " +
$"PostalCode = '{replaceApostrophes(txtPostalCode.Text)}', " +
$"Phone = {replaceApostrophes(txtPhone.Text)}, " +
$"Email = '{replaceApostrophes(txtEmail.Text)}', " +
$"Preferred = {Convert.ToByte(chkPreferred.Checked)} " +
$"WHERE GuestId = '{dtGuest.Rows[currentRecord]["GuestId"]}'";
}
txtPhone.TextMaskFormat = MaskFormat.IncludePromptAndLiterals;
Debug.WriteLine($"Rows affected: {GetSendData.SendData(sql)}");
LoadGuest();
PopulateField();
addMode = false;
parentForm.toolStripStatusLabel4.Text = "OK";
}
else
{
parentForm.toolStripStatusLabel4.Text = "Invalid data. Please fix all errors";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
private void DisplayGuests()
{
grpBox.Text = "";
dgvReport.DataSource = null;
grpBox.Text = " Guests's total booking" + " " + cboHotel.Text;
string sqlQuery = String.Format("SELECT guest.guestId, FirstName, LastName, total FROM Guest INNER JOIN " +
"(SELECT GuestID, RoomId, SUM(TotalCharge) AS total FROM booking GROUP BY GuestID, RoomId) guestBooking ON guestBooking.GuestID = Guest.GuestID INNER JOIN Room ON Room.RoomID = guestBooking.RoomID {0}", searchHotel);
DataTable dtGuest = new DataTable();
dtGuest = GetSendData.GetData(sqlQuery);
dgvReport.DataSource = dtGuest;
}
private void DisplayBookings()
{
grpBox.Text = "";
dgvReport.DataSource = null;
grpBox.Text = " Bookings made from " + dtpStartDate.Value.ToLongDateString() + " to " + dtPEndDate.Value.ToLongDateString() + " " + cboPreferredStatus.Text;
string sqlQuery = String.Format("select FirstName, LastName, hotel.Name, RoomNumber, startDate, endDate, requireParking, totalcharge " +
" FROM Booking INNER JOIN Room ON Booking.RoomID = Room.RoomID INNER JOIN Guest ON Guest.GuestID = Booking.GuestID INNER JOIN Hotel ON Hotel.HotelID = Room.Hotel WHERE startDate >='{0}' and endDate <='{1}' {2} ORDER BY StartDate, FirstName ", dtpStartDate.Value.ToShortDateString(), dtPEndDate.Value.ToShortDateString(), preferredStatus);
DataTable dtBooking = new DataTable();
dtBooking = GetSendData.GetData(sqlQuery);
dgvReport.DataSource = dtBooking;
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
if (ValidateChildren(ValidationConstraints.Enabled))
{
ToggleControls(true);
string sql = string.Empty;
int roomId = Convert.ToInt16(dtRoom.Select($"roomNumber = {cboRoomNumber.Text} AND roomType = '{cboRoomType.Text}' AND hotel = {cboHotel.SelectedValue}")[0]["RoomId"]);
if (addMode)
{
sql = $"INSERT INTO booking VALUES (" +
$"'{dtpStartDate.Value.ToShortDateString()}', " + //Startdate
$"'{dtpEndDate.Value.ToShortDateString()}'," + //Enddate
$"{roomId}, " + //RoomId
$"'{cboGuestId.Text}'," + //GuestId
$"{Convert.ToByte(chkRequiredParking.Checked)}, " + //RequiredParking
$"{(lblTotalCharges.Text)});"; //Total Charge
}
else //Update
{
sql = $"UPDATE Booking SET " +
$"StartDate = '{dtpStartDate.Value.ToShortDateString()}', " +
$"EndDate = '{dtpEndDate.Value.ToShortDateString()}', " +
$"RoomId = {roomId}, " +
$"GuestID = '{cboGuestId.Text}', " +
$"RequiredParking = {Convert.ToByte(chkRequiredParking.Checked)}, " +
$"TotalCharge = {lblTotalCharges.Text} " +
$"WHERE BookingID = {dtBooking.Rows[currentRecord]["GuestID"]};";
}
Debug.WriteLine($"Rows affected: {GetSendData.SendData(sql)}");
parentForm.toolStripStatusLabel4.Text = "OK";
UpdatePreferredStatus();
LoadBookings();
PopulateField();
addMode = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
// Business Rule: If a guest has had 4 or more bookings with any hotel, they should automatically be given preferred status.
private void UpdatePreferredStatus()
{
try
{
if (Convert.ToInt32(GetSendData.GetScalarValue($"SELECT COUNT(*) FROM Booking WHERE GuestID = '{cboGuestId.Text}';")) == 4)
{
GetSendData.SendData($"UPDATE guest SET Preferred = 1 WHERE GuestID = '{cboGuestId.Text}';");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
private void LoadComboHotel()
{
string sqlQuery = "SELECT HotelId, Name FROM hotel ORDER BY Name";
DataTable dt = GetSendData.GetData(sqlQuery);
DataRow row = dt.NewRow();
row["hotelId"] = DBNull.Value;
row["name"] = "Choose an hotel";
dt.Rows.InsertAt(row, 0);
cboHotel.ValueMember = "hotelId";
cboHotel.DisplayMember = "name";
cboHotel.DataSource = dt;
}
private void FillHotelDropdown()
{
dtHotel = GetSendData.GetData($"SELECT * FROM Hotel");
DataRow row = dtHotel.NewRow();
row["hotelId"] = DBNull.Value;
row["Name"] = "Select an hotel";
dtHotel.Rows.InsertAt(row, 0);
cboHotel.DisplayMember = "Name";
cboHotel.ValueMember = "HotelId";
cboHotel.DataSource = dtHotel;
cboHotel.SelectedIndex = currentRecord;
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
if (ValidateChildren(ValidationConstraints.Enabled))
{
string sql = "";
ToggleControls(true);
if (addMode)
{
sql = "INSERT INTO Room VALUES(" +
$"'{replaceApostrophes(txtRoomNumber.Text)}', " +
$"'{replaceApostrophes(cboRoomType.Text)}', " +
$"{replaceApostrophes(txtRate.Text)}, " +
$"{replaceApostrophes(txtParkingRate.Text)}, " +
$"{cboHotel.SelectedValue});";
}
else //update
{
sql = $"UPDATE Room SET" +
$"RoomNumber='{replaceApostrophes(txtRoomNumber.Text)}' ," +
$"RoomType='{replaceApostrophes(cboRoomType.Text)}', " +
$"Rate={replaceApostrophes(txtRate.Text)}, " +
$"ParkingRate={replaceApostrophes(txtParkingRate.Text)}, " +
$"Hotel={cboHotel.SelectedValue} " +
$"WHERE roomId={dtRoom.Rows[currentRecord]["RoomID"]}";
}
Debug.WriteLine($"Rows affected: {GetSendData.SendData(sql)}");
//LoadComboHotel();
LoadRoom();
PopulateField();
addMode = false;
parentForm.toolStripStatusLabel4.Text = "OK";
}
else
{
parentForm.toolStripStatusLabel4.Text = "Invalid data. Please fix all errors";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
private void fillRoomNumberDropdown()
{
if (cboRoomType.Text == "No Room")
{
NoRoomNumber();
return;
}
string sql = string.Empty;
if (addMode)
{
sql = $"SELECT RoomNumber FROM Room LEFT OUTER JOIN Booking " +
$"ON Booking.RoomID = Room.RoomID WHERE RoomType = '{cboRoomType.Text}' AND Room.RoomId NOT IN (SELECT RoomID FROM Booking " +
$"WHERE StartDate <= '{dtpEndDate.Value.ToShortDateString()}' AND '{dtpStartDate.Value.ToShortDateString()}' <= EndDate) " +
$"AND Hotel = {cboHotel.SelectedValue}" +
$"ORDER BY RoomNumber;";
}
else
{
sql = $"SELECT RoomNumber FROM Room LEFT OUTER JOIN Booking " +
$"ON Booking.RoomID = Room.RoomID WHERE RoomType = '{cboRoomType.Text}' AND Room.RoomId NOT IN (SELECT RoomID FROM Booking " +
$"WHERE StartDate <= '{dtpEndDate.Value.ToShortDateString()}' AND '{dtpStartDate.Value.ToShortDateString()}' <= EndDate) " +
$"AND Hotel = {cboHotel.SelectedValue}" +
$"OR BookingId = {dtBooking.Rows[currentRecord]["BookingId"]} " +
$"ORDER BY RoomNumber;";
}
DataTable dtRoomNumDrop = GetSendData.GetData(sql);
if (dtRoomNumDrop.Rows.Count > 0)
{
cboRoomNumber.DisplayMember = "RoomNumber";
cboRoomNumber.DataSource = dtRoomNumDrop;
cboRoomNumber.Enabled = true;
}
else
{
NoRoomNumber();
}
}
private void LoadComboHotel()
{
try
{
DataTable dtHotel = GetSendData.GetData("SELECT * FROM hotel ORDER BY name ASC");
DataRow row = dtHotel.NewRow();
row["hotelId"] = DBNull.Value;
row["Name"] = "Select an hotel";
dtHotel.Rows.InsertAt(row, 0);
cboHotel.ValueMember = "HotelId";
cboHotel.DisplayMember = "Name";
cboHotel.DataSource = dtHotel;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
private void fillRoomTypeDropdown()
{
if (cboHotel.SelectedValue == DBNull.Value)
{
NoRoomType();
return;
}
string sql = string.Empty;
if (addMode)
{
sql = ($"SELECT DISTINCT RoomType FROM Room LEFT OUTER JOIN Booking " +
$"ON Booking.RoomID = Room.RoomID WHERE Room.RoomID NOT IN (SELECT RoomID FROM Booking " +
$"WHERE StartDate <= '{dtpEndDate.Value.ToShortDateString()}' AND '{dtpStartDate.Value.ToShortDateString()}' <= EndDate) AND Hotel = {cboHotel.SelectedValue};");
}
else
{
sql = $"SELECT DISTINCT RoomType FROM Room LEFT OUTER JOIN Booking " +
$"ON Booking.RoomID = Room.RoomID WHERE Room.RoomID NOT IN (SELECT RoomID FROM Booking " +
$"WHERE StartDate <= '{dtpEndDate.Value.ToShortDateString()}' AND '{dtpStartDate.Value.ToShortDateString()}' <= EndDate) " +
$"AND Hotel = {cboHotel.SelectedValue} OR BookingId = {dtBooking.Rows[currentRecord]["BookingId"]};";
}
DataTable dtRoomTypeDrop = GetSendData.GetData(sql);
if (dtRoom.Rows.Count > 0)
{
cboRoomType.DisplayMember = "RoomType";
cboRoomType.DataSource = dtRoomTypeDrop;
cboRoomType.Enabled = true;
}
else
{
NoRoomType();
}
cboRoomType.Enabled = true;
}
private void btnSubmit_Click(object sender, EventArgs e)
{
try
{
DataTable dt = GetSendData.GetData("SELECT * FROM UserAccounts");
if (!(dt.Rows[0]["UserName"].ToString().ToLower() == txtUserName.Text.Trim().ToLower()) || !(dt.Rows[0]["password"].ToString() == txtPassword.Text.Trim()))
{
txtPassword.Clear();
MessageBox.Show("Username or password does not exist");
}
else
{
username = txtUserName.Text;
DialogResult = DialogResult.OK;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
try
{
DialogResult dialogResult = MessageBox.Show("Are you sure you want to delete: " + Environment.NewLine +
$"{txtLastName.Text}, {txtFirstName.Text} " + Environment.NewLine +
$"GuestID: {dtGuest.Rows[currentRecord]["GuestID"]}?", "Delete Record", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (dialogResult == DialogResult.Yes)
{
if (Convert.ToInt16(GetSendData.GetScalarValue($"SELECT COUNT(*) FROM Booking WHERE Guestid = '{dtGuest.Rows[currentRecord]["GuestID"]}'")) > 0)
{
MessageBox.Show("Cannot delete guest who has bookings.", "Delete Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (GetSendData.SendData($"DELETE FROM Guest WHERE GuestId = '{dtGuest.Rows[currentRecord]["GuestID"]}'") > 0)
{
MessageBox.Show("Record Deleted");
currentRecord = 0;
LoadGuest();
PopulateField();
}
else
{
MessageBox.Show("An error occured while saving the data.");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
private void LoadRoom()
{
dtRoom = GetSendData.GetData("SELECT * FROM room ORDER BY roomNumber ASC");
}
private void ControlValidation(object sender, CancelEventArgs e)
{
//Loop through all the controls
//Check TextBox is not null or whitespace
//Check ComboBox has a valid option selected.
foreach (Control gp in this.Controls)
{
//The controls are in a groupbox so a nested loop is required
if (gp is GroupBox)
{
foreach (Control ctrl in gp.Controls)
{
if (ctrl is TextBox)
{
if (string.IsNullOrWhiteSpace(ctrl.Text))
{
e.Cancel = true;
errorProvider1.SetError(ctrl, "This field is required");
}
else
{
errorProvider1.SetError(ctrl, "");
}
}
}
}
}
if (dtpEndDate.Value <= dtpStartDate.Value)
{
e.Cancel = true;
errorProvider1.SetError(dtpEndDate, "End date can not be before the start date.");
}
else
{
errorProvider1.SetError(dtpEndDate, "");
}
if (cboRoomType.Text.ToLower() == "no room")
{
e.Cancel = true;
errorProvider1.SetError(cboRoomType, "This field is required");
}
else
{
errorProvider1.SetError(cboRoomType, "");
}
if (cboRoomNumber.Text.ToLower() == "no room")
{
e.Cancel = true;
errorProvider1.SetError(cboRoomNumber, "This field is required");
}
else
{
errorProvider1.SetError(cboRoomNumber, "");
}
if (cboGuestId.Text.ToLower() == "no guest")
{
e.Cancel = true;
errorProvider1.SetError(cboGuestId, "This field is required");
}
else
{
errorProvider1.SetError(cboGuestId, "");
}
//Business Rule:
//A booking cannot be created for the penthouse suite for a guest that has not booked with the
//hotel at least 2 times in the past.
if (cboRoomNumber.Text.ToLower() != "no room" && cboRoomType.Text.ToLower() == "penthouse suite" && cboHotel.SelectedIndex > 0 && cboGuestId.Text.ToLower() != "no guest")
{
if (Convert.ToInt16(GetSendData.GetScalarValue($"SELECT COUNT(*) FROM Booking INNER JOIN Room ON Booking.RoomID = Room.RoomID WHERE GuestID = '{cboGuestId.Text}' AND Hotel = {cboHotel.SelectedValue};")) < 2)
{
e.Cancel = true;
errorProvider1.SetError(cboRoomType, "Guest must have booked with us twice in the past to get Penthouse option");
}
}
}
private void LoadDependentForms()
{
dtGuest = GetSendData.GetData("SELECT * FROM Guest");
dtHotel = GetSendData.GetData("SELECT * FROM Hotel");
dtRoom = GetSendData.GetData("SELECT * FROM Room");
}
private void LoadGuest()
{
dtGuest = GetSendData.GetData("SELECT * FROM Guest");
}
private void LoadBookings()
{
dtBooking = GetSendData.GetData("SELECT * FROM Booking");
}