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"); }