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 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 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 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 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 LoadGuest() { dtGuest = GetSendData.GetData("SELECT * FROM Guest"); }
private void LoadDependentForms() { dtGuest = GetSendData.GetData("SELECT * FROM Guest"); dtHotel = GetSendData.GetData("SELECT * FROM Hotel"); dtRoom = GetSendData.GetData("SELECT * FROM Room"); }
private void LoadBookings() { dtBooking = GetSendData.GetData("SELECT * FROM Booking"); }
private void LoadRoom() { dtRoom = GetSendData.GetData("SELECT * FROM room ORDER BY roomNumber ASC"); }