Ejemplo n.º 1
0
        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());
            }
        }
Ejemplo n.º 2
0
        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;
        }
Ejemplo n.º 3
0
        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());
        }
Ejemplo n.º 4
0
        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());
            }
        }
Ejemplo n.º 5
0
        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());
            }
        }
Ejemplo n.º 6
0
        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;
        }
Ejemplo n.º 7
0
        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;
        }
Ejemplo n.º 8
0
        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());
            }
        }
Ejemplo n.º 9
0
 // 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());
     }
 }
Ejemplo n.º 10
0
        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;
        }
Ejemplo n.º 11
0
        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;
        }
Ejemplo n.º 12
0
        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());
            }
        }
Ejemplo n.º 13
0
        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();
            }
        }
Ejemplo n.º 14
0
        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());
            }
        }
Ejemplo n.º 15
0
        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;
        }
Ejemplo n.º 16
0
        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());
            }
        }
Ejemplo n.º 17
0
        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());
            }
        }
Ejemplo n.º 18
0
 private void LoadRoom()
 {
     dtRoom = GetSendData.GetData("SELECT * FROM room ORDER BY roomNumber ASC");
 }
Ejemplo n.º 19
0
        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");
                }
            }
        }
Ejemplo n.º 20
0
 private void LoadDependentForms()
 {
     dtGuest = GetSendData.GetData("SELECT * FROM Guest");
     dtHotel = GetSendData.GetData("SELECT * FROM Hotel");
     dtRoom  = GetSendData.GetData("SELECT * FROM Room");
 }
Ejemplo n.º 21
0
 private void LoadGuest()
 {
     dtGuest = GetSendData.GetData("SELECT * FROM Guest");
 }
Ejemplo n.º 22
0
 private void LoadBookings()
 {
     dtBooking = GetSendData.GetData("SELECT * FROM Booking");
 }