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