DestinationPopularityReportForm1.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;
using System.Windows.Forms.DataVisualization.Charting;

namespace H1
{
    public partial class DestinationPopularityReportForm1 : Form
    {
        private string connectionString = @"Data Source=ABDULSABOOR190\SQLEXPRESS;Initial Catalog=sabbbb;Integrated Security=True;Encrypt=False";
        private DateTimePicker dtpStartDate;
        private DateTimePicker dtpEndDate;
        private ComboBox cbxDestination;
        private Button btnGenerateReport;
        private TabControl tabControl;

        // Tabs for destination popularity
        private TabPage tabHotelOccupancy;
        private TabPage tabGuideRatings;
        private TabPage tabTransportPerformance;
        private TabPage tabServiceUtilization;

        // Charts and grids
        private Chart chartHotelOccupancy;
        private Chart chartGuideRatings;
        private Chart chartTransportPerformance;
        private Chart chartServiceUtilization;
        private DataGridView dgvHotelOccupancy;
        private DataGridView dgvGuideRatings;
        private DataGridView dgvTransportPerformance;
        private DataGridView dgvServiceUtilization;

        public DestinationPopularityReportForm1()
        {
            InitializeComponent();
        }

        private void InitializeComponent()
        {
            this.Text = "Destination Popularity Report";
            this.Size = new Size(1000, 700);
            this.StartPosition = FormStartPosition.CenterScreen;

            // Date Range Controls
            var lblDateRange = new Label();
            lblDateRange.Text = "Select Date Range:";
            lblDateRange.Location = new Point(20, 20);
            lblDateRange.AutoSize = true;

            dtpStartDate = new DateTimePicker();
            dtpStartDate.Location = new Point(150, 20);
            dtpStartDate.Format = DateTimePickerFormat.Short;
            dtpStartDate.Value = DateTime.Now.AddMonths(-1);

            var lblTo = new Label();
            lblTo.Text = "to";
            lblTo.Location = new Point(320, 20);
            lblTo.AutoSize = true;

            dtpEndDate = new DateTimePicker();
            dtpEndDate.Location = new Point(350, 20);
            dtpEndDate.Format = DateTimePickerFormat.Short;
            dtpEndDate.Value = DateTime.Now;

            // Destination Selection
            var lblDestination = new Label();
            lblDestination.Text = "Select Destination:";
            lblDestination.Location = new Point(20, 50);
            lblDestination.AutoSize = true;

            cbxDestination = new ComboBox();
            cbxDestination.Location = new Point(150, 50);
            cbxDestination.Size = new Size(200, 25);
            cbxDestination.DropDownStyle = ComboBoxStyle.DropDownList;
            LoadDestinations();

            btnGenerateReport = new Button();
            btnGenerateReport.Text = "Generate Report";
            btnGenerateReport.Location = new Point(500, 35);
            btnGenerateReport.Click += new EventHandler(btnGenerateReport_Click);

            // Tab Control
            tabControl = new TabControl();
            tabControl.Location = new Point(20, 90);
            tabControl.Size = new Size(940, 570);

            // Create tabs
            CreateHotelOccupancyTab();
            CreateGuideRatingsTab();
            CreateTransportPerformanceTab();
            CreateServiceUtilizationTab();

            // Add controls to form
            this.Controls.Add(lblDateRange);
            this.Controls.Add(dtpStartDate);
            this.Controls.Add(lblTo);
            this.Controls.Add(dtpEndDate);
            this.Controls.Add(lblDestination);
            this.Controls.Add(cbxDestination);
            this.Controls.Add(btnGenerateReport);
            this.Controls.Add(tabControl);
        }

        private void LoadDestinations()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = "SELECT DISTINCT destination FROM Trip ORDER BY destination";
                    SqlCommand command = new SqlCommand(query, connection);
                    connection.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        cbxDestination.Items.Clear();
                        while (reader.Read())
                        {
                            cbxDestination.Items.Add(reader["destination"].ToString());
                        }
                    }

                    if (cbxDestination.Items.Count > 0)
                        cbxDestination.SelectedIndex = 0;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error loading destinations: {ex.Message}", "Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void CreateHotelOccupancyTab()
        {
            tabHotelOccupancy = new TabPage();
            tabHotelOccupancy.Text = "Hotel Occupancy";

            chartHotelOccupancy = new Chart();
            chartHotelOccupancy.Location = new Point(20, 20);
            chartHotelOccupancy.Size = new Size(880, 250);
            chartHotelOccupancy.Titles.Add("Hotel Occupancy by Destination");
            chartHotelOccupancy.ChartAreas.Add(new ChartArea("ChartArea1"));
            chartHotelOccupancy.Legends.Add(new Legend("Legend1"));

            dgvHotelOccupancy = new DataGridView();
            dgvHotelOccupancy.Location = new Point(20, 290);
            dgvHotelOccupancy.Size = new Size(880, 250);
            dgvHotelOccupancy.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dgvHotelOccupancy.ReadOnly = true;

            tabHotelOccupancy.Controls.Add(chartHotelOccupancy);
            tabHotelOccupancy.Controls.Add(dgvHotelOccupancy);
            tabControl.TabPages.Add(tabHotelOccupancy);
        }

        private void CreateGuideRatingsTab()
        {
            tabGuideRatings = new TabPage();
            tabGuideRatings.Text = "Guide Ratings";

            chartGuideRatings = new Chart();
            chartGuideRatings.Location = new Point(20, 20);
            chartGuideRatings.Size = new Size(880, 250);
            chartGuideRatings.Titles.Add("Guide Ratings by Destination");
            chartGuideRatings.ChartAreas.Add(new ChartArea("ChartArea1"));
            chartGuideRatings.Legends.Add(new Legend("Legend1"));

            dgvGuideRatings = new DataGridView();
            dgvGuideRatings.Location = new Point(20, 290);
            dgvGuideRatings.Size = new Size(880, 250);
            dgvGuideRatings.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dgvGuideRatings.ReadOnly = true;

            tabGuideRatings.Controls.Add(chartGuideRatings);
            tabGuideRatings.Controls.Add(dgvGuideRatings);
            tabControl.TabPages.Add(tabGuideRatings);
        }

        private void CreateTransportPerformanceTab()
        {
            tabTransportPerformance = new TabPage();
            tabTransportPerformance.Text = "Transport Performance";

            chartTransportPerformance = new Chart();
            chartTransportPerformance.Location = new Point(20, 20);
            chartTransportPerformance.Size = new Size(880, 250);
            chartTransportPerformance.Titles.Add("Transport On-Time Performance by Destination");
            chartTransportPerformance.ChartAreas.Add(new ChartArea("ChartArea1"));
            chartTransportPerformance.Legends.Add(new Legend("Legend1"));

            dgvTransportPerformance = new DataGridView();
            dgvTransportPerformance.Location = new Point(20, 290);
            dgvTransportPerformance.Size = new Size(880, 250);
            dgvTransportPerformance.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dgvTransportPerformance.ReadOnly = true;

            tabTransportPerformance.Controls.Add(chartTransportPerformance);
            tabTransportPerformance.Controls.Add(dgvTransportPerformance);
            tabControl.TabPages.Add(tabTransportPerformance);
        }

        private void CreateServiceUtilizationTab()
        {
            tabServiceUtilization = new TabPage();
            tabServiceUtilization.Text = "Service Utilization";

            chartServiceUtilization = new Chart();
            chartServiceUtilization.Location = new Point(20, 20);
            chartServiceUtilization.Size = new Size(880, 250);
            chartServiceUtilization.Titles.Add("Service Utilization by Destination");
            chartServiceUtilization.ChartAreas.Add(new ChartArea("ChartArea1"));
            chartServiceUtilization.Legends.Add(new Legend("Legend1"));

            dgvServiceUtilization = new DataGridView();
            dgvServiceUtilization.Location = new Point(20, 290);
            dgvServiceUtilization.Size = new Size(880, 250);
            dgvServiceUtilization.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dgvServiceUtilization.ReadOnly = true;

            tabServiceUtilization.Controls.Add(chartServiceUtilization);
            tabServiceUtilization.Controls.Add(dgvServiceUtilization);
            tabControl.TabPages.Add(tabServiceUtilization);
        }

        private void btnGenerateReport_Click(object sender, EventArgs e)
        {
            DateTime startDate = dtpStartDate.Value.Date;
            DateTime endDate = dtpEndDate.Value.Date.AddDays(1).AddSeconds(-1);
            string destination = cbxDestination.SelectedItem?.ToString();

            if (string.IsNullOrEmpty(destination))
            {
                MessageBox.Show("Please select a destination.", "Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            if (startDate > endDate)
            {
                MessageBox.Show("Start date must be before end date.", "Invalid Date Range",
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            try
            {
                Cursor = Cursors.WaitCursor;

                // Generate all reports
                GenerateHotelOccupancyReport(startDate, endDate, destination);
                GenerateGuideRatingsReport(startDate, endDate, destination);
                GenerateTransportPerformanceReport(startDate, endDate, destination);
                GenerateServiceUtilizationReport(startDate, endDate, destination);

                Cursor = Cursors.Default;
            }
            catch (Exception ex)
            {
                Cursor = Cursors.Default;
                MessageBox.Show($"Error generating report: {ex.Message}", "Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void GenerateHotelOccupancyReport(DateTime startDate, DateTime endDate, string destination)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            h.name AS HotelName,
                            COUNT(DISTINCT r.room_id) AS TotalRooms,
                            COUNT(DISTINCT a.assignment_id) AS OccupiedRooms,
                            CAST(COUNT(DISTINCT a.assignment_id) AS FLOAT) / 
                            COUNT(DISTINCT r.room_id) * 100 AS OccupancyRate
                        FROM Hotel h
                        JOIN Room r ON h.hotel_id = r.hotel_id
                        LEFT JOIN Assignment a ON r.room_id = a.service_id
                        LEFT JOIN Booking b ON a.booking_id = b.booking_id
                        JOIN Trip t ON b.trip_id = t.trip_id
                        WHERE b.booking_date BETWEEN @StartDate AND @EndDate
                        AND t.destination = @Destination
                        GROUP BY h.name
                        ORDER BY OccupancyRate DESC";

                    SqlCommand command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@StartDate", startDate);
                    command.Parameters.AddWithValue("@EndDate", endDate);
                    command.Parameters.AddWithValue("@Destination", destination);

                    connection.Open();

                    DataTable dt = new DataTable();
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        adapter.Fill(dt);
                    }

                    // Update DataGridView
                    dgvHotelOccupancy.DataSource = dt;
                    dgvHotelOccupancy.Columns["OccupancyRate"].DefaultCellStyle.Format = "0.00%";

                    // Update Chart
                    chartHotelOccupancy.Series.Clear();
                    Series series = chartHotelOccupancy.Series.Add("Occupancy Rate");
                    series.ChartType = SeriesChartType.Bar;

                    foreach (DataRow row in dt.Rows)
                    {
                        string hotelName = row["HotelName"].ToString();
                        double rate = Convert.ToDouble(row["OccupancyRate"]);
                        series.Points.AddXY(hotelName, rate);
                    }

                    chartHotelOccupancy.ChartAreas[0].AxisY.LabelStyle.Format = "0.0%";
                    chartHotelOccupancy.ChartAreas[0].AxisY.Maximum = 100;
                    chartHotelOccupancy.Titles[0].Text = $"Hotel Occupancy in {destination}";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating hotel occupancy report: {ex.Message}");
            }
        }

        private void GenerateGuideRatingsReport(DateTime startDate, DateTime endDate, string destination)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            g.first_name + ' ' + g.last_name AS GuideName,
                            AVG(r.rating) AS AverageRating,
                            COUNT(r.review_id) AS ReviewCount
                        FROM Guide g
                        JOIN Review r ON g.guide_id = r.review_id
                        JOIN Booking b ON r.booking_id = b.booking_id
                        JOIN Trip t ON b.trip_id = t.trip_id
                        WHERE b.booking_date BETWEEN @StartDate AND @EndDate
                        AND t.destination = @Destination
                        GROUP BY g.first_name, g.last_name
                        HAVING COUNT(r.review_id) > 0
                        ORDER BY AverageRating DESC";

                    SqlCommand command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@StartDate", startDate);
                    command.Parameters.AddWithValue("@EndDate", endDate);
                    command.Parameters.AddWithValue("@Destination", destination);

                    connection.Open();

                    DataTable dt = new DataTable();
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        adapter.Fill(dt);
                    }

                    // Update DataGridView
                    dgvGuideRatings.DataSource = dt;

                    // Update Chart
                    chartGuideRatings.Series.Clear();
                    Series series = chartGuideRatings.Series.Add("Average Rating");
                    series.ChartType = SeriesChartType.Column;

                    foreach (DataRow row in dt.Rows)
                    {
                        string guideName = row["GuideName"].ToString();
                        double rating = Convert.ToDouble(row["AverageRating"]);
                        series.Points.AddXY(guideName, rating);
                    }

                    chartGuideRatings.ChartAreas[0].AxisY.Maximum = 5;
                    chartGuideRatings.Titles[0].Text = $"Guide Ratings in {destination}";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating guide ratings report: {ex.Message}");
            }
        }

        private void GenerateTransportPerformanceReport(DateTime startDate, DateTime endDate, string destination)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            t.vehicle_type AS VehicleType,
                            COUNT(DISTINCT a.assignment_id) AS TotalAssignments,
                            SUM(CASE WHEN a.status = 'confirmed' THEN 1 ELSE 0 END) AS OnTimeAssignments,
                            CAST(SUM(CASE WHEN a.status = 'confirmed' THEN 1 ELSE 0 END) AS FLOAT) / 
                            COUNT(DISTINCT a.assignment_id) * 100 AS OnTimePercentage
                        FROM Transport t
                        JOIN Assignment a ON t.transport_id = a.service_id
                        JOIN Booking b ON a.booking_id = b.booking_id
                        JOIN Trip tr ON b.trip_id = tr.trip_id
                        WHERE b.booking_date BETWEEN @StartDate AND @EndDate
                        AND tr.destination = @Destination
                        GROUP BY t.vehicle_type
                        ORDER BY OnTimePercentage DESC";

                    SqlCommand command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@StartDate", startDate);
                    command.Parameters.AddWithValue("@EndDate", endDate);
                    command.Parameters.AddWithValue("@Destination", destination);

                    connection.Open();

                    DataTable dt = new DataTable();
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        adapter.Fill(dt);
                    }

                    // Update DataGridView
                    dgvTransportPerformance.DataSource = dt;
                    dgvTransportPerformance.Columns["OnTimePercentage"].DefaultCellStyle.Format = "0.00%";

                    // Update Chart
                    chartTransportPerformance.Series.Clear();
                    Series series = chartTransportPerformance.Series.Add("On-Time Percentage");
                    series.ChartType = SeriesChartType.Bar;

                    foreach (DataRow row in dt.Rows)
                    {
                        string vehicleType = row["VehicleType"].ToString();
                        double percentage = Convert.ToDouble(row["OnTimePercentage"]);
                        series.Points.AddXY(vehicleType, percentage);
                    }

                    chartTransportPerformance.ChartAreas[0].AxisY.LabelStyle.Format = "0.0%";
                    chartTransportPerformance.ChartAreas[0].AxisY.Maximum = 100;
                    chartTransportPerformance.Titles[0].Text = $"Transport Performance in {destination}";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating transport performance report: {ex.Message}");
            }
        }

        private void GenerateServiceUtilizationReport(DateTime startDate, DateTime endDate, string destination)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            sp.type AS ServiceType,
                            COUNT(a.assignment_id) AS TotalBookings,
                            COUNT(DISTINCT b.traveler_id) AS UniqueTravelers
                        FROM ServiceProviderProfile sp
                        LEFT JOIN (
                            SELECT provider_id, hotel_id AS service_id FROM Hotel
                            UNION SELECT provider_id, guide_id FROM Guide
                            UNION SELECT provider_id, transport_id FROM Transport
                        ) services ON sp.user_id = services.provider_id
                        LEFT JOIN Assignment a ON services.service_id = a.service_id
                        LEFT JOIN Booking b ON a.booking_id = b.booking_id
                        LEFT JOIN Trip t ON b.trip_id = t.trip_id
                        WHERE b.booking_date BETWEEN @StartDate AND @EndDate
                        AND t.destination = @Destination
                        GROUP BY sp.type
                        ORDER BY TotalBookings DESC";

                    SqlCommand command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@StartDate", startDate);
                    command.Parameters.AddWithValue("@EndDate", endDate);
                    command.Parameters.AddWithValue("@Destination", destination);

                    connection.Open();

                    DataTable dt = new DataTable();
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        adapter.Fill(dt);
                    }

                    // Update DataGridView
                    dgvServiceUtilization.DataSource = dt;

                    // Update Chart
                    chartServiceUtilization.Series.Clear();
                    Series series = chartServiceUtilization.Series.Add("Service Utilization");
                    series.ChartType = SeriesChartType.Pie;

                    foreach (DataRow row in dt.Rows)
                    {
                        string serviceType = row["ServiceType"].ToString();
                        int count = Convert.ToInt32(row["TotalBookings"]);
                        series.Points.AddXY(serviceType, count);
                    }

                    chartServiceUtilization.Titles[0].Text = $"Service Utilization in {destination}";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating service utilization report: {ex.Message}");
            }
        }
    }
}