Form14.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Windows.Forms;

namespace H1
{
    public partial class Form14 : Form
    {
        // The member variables dataGridView1, txtSearch, etc. are declared in Form14.Designer.cs 
        // and accessible from this file through the partial class mechanism

        private DataTable bookingsTable;
        private string connectionString = @"Data Source=ABDULSABOOR190\SQLEXPRESS;Initial Catalog=sabbbb;Integrated Security=True;Encrypt=False";
        // You'll need to update this with your actual connection string

        public Form14()
        {
            InitializeComponent();
            InitializeDataTable();
        }

        private void InitializeDataTable()
        {
            bookingsTable = new DataTable();
            bookingsTable.Columns.Add("Booking ID", typeof(int));
            bookingsTable.Columns.Add("Trip Title", typeof(string));
            bookingsTable.Columns.Add("User", typeof(string));
            bookingsTable.Columns.Add("Participants", typeof(int));
            bookingsTable.Columns.Add("Booking Date", typeof(DateTime));
            bookingsTable.Columns.Add("Status", typeof(string));
            bookingsTable.Columns.Add("Amount", typeof(decimal));
        }

        private void Form14_Load(object sender, EventArgs e)
        {
            LoadBookingsFromDatabase();
            ConfigureDataGridView();
        }

        private void LoadBookingsFromDatabase()
        {
            try
            {
                bookingsTable.Clear();
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT b.booking_id, t.title, 
                               tp.first_name + ' ' + tp.last_name AS full_name,
                               b.participants, b.booking_date, b.status, b.total_amount
                        FROM Booking b
                        INNER JOIN Trip t ON b.trip_id = t.trip_id
                        INNER JOIN TravelerProfile tp ON b.traveler_id = tp.user_id
                        ORDER BY b.booking_date DESC";

                    SqlCommand command = new SqlCommand(query, connection);
                    connection.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            bookingsTable.Rows.Add(
                                reader.GetInt32(0),                  // Booking ID
                                reader.GetString(1),                 // Trip Title
                                reader.GetString(2),                 // User Full Name
                                reader.GetInt32(3),                  // Participants
                                reader.GetDateTime(4),               // Booking Date
                                reader.GetString(5),                 // Status
                                reader.GetDecimal(6)                 // Amount
                            );
                        }
                    }
                }

                dataGridView1.DataSource = bookingsTable;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error loading bookings: {ex.Message}", "Database Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void ConfigureDataGridView()
        {
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.ReadOnly = true;
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            string searchTerm = txtSearch.Text.Trim().ToLower();

            if (string.IsNullOrWhiteSpace(searchTerm))
            {
                dataGridView1.DataSource = bookingsTable;
                return;
            }

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT b.booking_id, t.title, 
                               tp.first_name + ' ' + tp.last_name AS full_name,
                               b.participants, b.booking_date, b.status, b.total_amount
                        FROM Booking b
                        INNER JOIN Trip t ON b.trip_id = t.trip_id
                        INNER JOIN TravelerProfile tp ON b.traveler_id = tp.user_id
                        WHERE t.title LIKE @SearchTerm 
                           OR (tp.first_name + ' ' + tp.last_name) LIKE @SearchTerm
                           OR CAST(b.booking_id AS VARCHAR) LIKE @SearchTerm
                        ORDER BY b.booking_date DESC";

                    SqlCommand command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@SearchTerm", "%" + searchTerm + "%");

                    connection.Open();

                    DataTable searchResults = new DataTable();
                    searchResults.Columns.Add("Booking ID", typeof(int));
                    searchResults.Columns.Add("Trip Title", typeof(string));
                    searchResults.Columns.Add("User", typeof(string));
                    searchResults.Columns.Add("Participants", typeof(int));
                    searchResults.Columns.Add("Booking Date", typeof(DateTime));
                    searchResults.Columns.Add("Status", typeof(string));
                    searchResults.Columns.Add("Amount", typeof(decimal));

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            searchResults.Rows.Add(
                                reader.GetInt32(0),                  // Booking ID
                                reader.GetString(1),                 // Trip Title
                                reader.GetString(20),                 // User Full Name
                                reader.GetInt32(3),                  // Participants
                                reader.GetDateTime(4),               // Booking Date
                                reader.GetString(5),                 // Status
                                reader.GetDecimal(6)                 // Amount
                            );
                        }
                    }

                    if (searchResults.Rows.Count > 0)
                    {
                        dataGridView1.DataSource = searchResults;
                    }
                    else
                    {
                        MessageBox.Show("No matching bookings found.", "Search",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error searching bookings: {ex.Message}", "Database Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnUpdateStatus_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count == 0)
            {
                MessageBox.Show("Please select a booking to update.", "Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            var selectedRow = dataGridView1.SelectedRows[0];
            int bookingId = (int)selectedRow.Cells["Booking ID"].Value;
            string currentStatus = selectedRow.Cells["Status"].Value.ToString();

            using (var statusForm = new StatusUpdateForm(currentStatus))
            {
                if (statusForm.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        using (SqlConnection connection = new SqlConnection(connectionString))
                        {
                            string query = "UPDATE Booking SET status = @Status WHERE booking_id = @BookingId";
                            SqlCommand command = new SqlCommand(query, connection);
                            command.Parameters.AddWithValue("@Status", statusForm.NewStatus);
                            command.Parameters.AddWithValue("@BookingId", bookingId);

                            connection.Open();
                            int rowsAffected = command.ExecuteNonQuery();

                            if (rowsAffected > 0)
                            {
                                selectedRow.Cells["Status"].Value = statusForm.NewStatus;
                                MessageBox.Show($"Booking #{bookingId} status updated to {statusForm.NewStatus}",
                                    "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            }
                            else
                            {
                                MessageBox.Show("Failed to update booking status.", "Error",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show($"Error updating booking status: {ex.Message}",
                            "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }

        private void btnViewDetails_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count == 0)
            {
                MessageBox.Show("Please select a booking to view details.", "Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            var selectedRow = dataGridView1.SelectedRows[0];
            int bookingId = (int)selectedRow.Cells["Booking ID"].Value;

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT b.booking_id, t.title, t.destination,
                               tp.first_name, tp.last_name, tp.phone,
                               b.participants, b.booking_date, b.status, b.total_amount,
                               b.special_requests, t.start_date, t.end_date
                        FROM Booking b
                        INNER JOIN Trip t ON b.trip_id = t.trip_id
                        INNER JOIN TravelerProfile tp ON b.traveler_id = tp.user_id
                        WHERE b.booking_id = @BookingId";

                    SqlCommand command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@BookingId", bookingId);

                    connection.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            string details = $"Booking ID: {reader["booking_id"]}\n" +
                                          $"Trip: {reader["title"]}\n" +
                                          $"Destination: {reader["destination"]}\n" +
                                          $"User: {reader["first_name"]} {reader["last_name"]}\n" +
                                          $"Contact: {reader["phone"]}\n" +
                                          $"Participants: {reader["participants"]}\n" +
                                          $"Booking Date: {((DateTime)reader["booking_date"]).ToString("yyyy-MM-dd")}\n" +
                                          $"Trip Dates: {((DateTime)reader["start_date"]).ToString("yyyy-MM-dd")} to " +
                                          $"{((DateTime)reader["end_date"]).ToString("yyyy-MM-dd")}\n" +
                                          $"Status: {reader["status"]}\n" +
                                          $"Amount: {string.Format("{0:C}", reader["total_amount"])}\n";

                            if (!reader.IsDBNull(reader.GetOrdinal("special_requests")))
                            {
                                details += $"\nSpecial Requests:\n{reader["special_requests"]}";
                            }

                            MessageBox.Show(details, "Booking Details",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                        else
                        {
                            MessageBox.Show("Booking details not found.", "Error",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error retrieving booking details: {ex.Message}",
                    "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            txtSearch.Clear();
            LoadBookingsFromDatabase();
        }
    }

    // Status update dialog class
    public class StatusUpdateForm : Form
    {
        public string NewStatus { get; private set; }
        private ComboBox cmbStatus;
        private Button btnOK;
        private Button btnCancel;

        public StatusUpdateForm(string currentStatus)
        {
            InitializeComponents();
            cmbStatus.SelectedItem = currentStatus;
        }

        private void InitializeComponents()
        {
            this.Text = "Update Booking Status";
            this.Size = new System.Drawing.Size(300, 150);
            this.FormBorderStyle = FormBorderStyle.FixedDialog;
            this.StartPosition = FormStartPosition.CenterParent;
            this.MaximizeBox = false;
            this.MinimizeBox = false;

            cmbStatus = new ComboBox();
            cmbStatus.Items.AddRange(new object[] { "pending", "confirmed", "canceled" });
            cmbStatus.DropDownStyle = ComboBoxStyle.DropDownList;
            cmbStatus.Location = new System.Drawing.Point(20, 20);
            cmbStatus.Size = new System.Drawing.Size(250, 25);
            this.Controls.Add(cmbStatus);

            btnOK = new Button();
            btnOK.Text = "OK";
            btnOK.DialogResult = DialogResult.OK;
            btnOK.Location = new System.Drawing.Point(100, 60);
            btnOK.Click += (s, e) => { NewStatus = cmbStatus.SelectedItem.ToString(); this.Close(); };
            this.Controls.Add(btnOK);

            btnCancel = new Button();
            btnCancel.Text = "Cancel";
            btnCancel.DialogResult = DialogResult.Cancel;
            btnCancel.Location = new System.Drawing.Point(180, 60);
            this.Controls.Add(btnCancel);

            this.AcceptButton = btnOK;
            this.CancelButton = btnCancel;
        }
    }
}