ServiceProviderEfficiencyReportForm.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 ServiceProviderEfficiencyReportForm : Form
    {
        private string connectionString = @"Data Source=ABDULSABOOR190\SQLEXPRESS;Initial Catalog=sabbbb;Integrated Security=True;Encrypt=False";
        private DateTimePicker dtpStartDate;
        private DateTimePicker dtpEndDate;
        private Button btnGenerateReport;
        private TabControl tabControl;

        // Tabs for service provider efficiency
        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 ServiceProviderEfficiencyReportForm()
        {
            InitializeComponent();
        }

        private void InitializeComponent()
        {
            this.Text = "Service Provider Efficiency 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;

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

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

            // 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(btnGenerateReport);
            this.Controls.Add(tabControl);
        }

        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 Rates");
            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 Performance Ratings");
            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");
            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 Provider Utilization Rates");
            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);

            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);
                GenerateGuideRatingsReport(startDate, endDate);
                GenerateTransportPerformanceReport(startDate, endDate);
                GenerateServiceUtilizationReport(startDate, endDate);

                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)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            h.hotel_name,
                            h.location,
                            h.star_rating,
                            AVG(ro.rooms_occupied * 100.0 / (dri.available_rooms + dri.out_of_service_rooms)) AS avg_occupancy_rate,
                            SUM(ro.rooms_occupied) AS total_rooms_occupied,
                            SUM(dri.available_rooms + dri.out_of_service_rooms) AS total_available_rooms,
                            COUNT(*) AS days_reported
                        FROM HotelProperties h
                        JOIN DailyRoomInventory dri ON h.hotel_id = dri.hotel_id
                        JOIN RoomOccupancy ro ON h.hotel_id = ro.hotel_id AND dri.date = ro.date
                        WHERE dri.date BETWEEN @StartDate AND @EndDate
                        GROUP BY h.hotel_name, h.location, h.star_rating
                        ORDER BY avg_occupancy_rate DESC";

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

                    connection.Open();

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

                    // Update DataGridView
                    dgvHotelOccupancy.DataSource = dt;
                    dgvHotelOccupancy.Columns["avg_occupancy_rate"].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["hotel_name"].ToString();
                        double occupancyRate = Convert.ToDouble(row["avg_occupancy_rate"]);
                        series.Points.AddXY(hotelName, occupancyRate);
                    }

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

        private void GenerateGuideRatingsReport(DateTime startDate, DateTime endDate)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            g.guide_id,
                            g.first_name + ' ' + g.last_name AS guide_name,
                            g.specialization,
                            COUNT(ga.assignment_id) AS assignments_completed,
                            AVG(gr.rating_value) AS avg_rating,
                            MIN(gr.rating_value) AS min_rating,
                            MAX(gr.rating_value) AS max_rating,
                            g.base_rating
                        FROM Guides g
                        LEFT JOIN GuideAssignments ga ON g.guide_id = ga.guide_id
                        LEFT JOIN GuideRatings gr ON ga.assignment_id = gr.assignment_id
                        WHERE ga.tour_date BETWEEN @StartDate AND @EndDate
                        GROUP BY g.guide_id, g.first_name, g.last_name, g.specialization, g.base_rating
                        ORDER BY avg_rating DESC";

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

                    connection.Open();

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

                    // Update DataGridView
                    dgvGuideRatings.DataSource = dt;
                    dgvGuideRatings.Columns["avg_rating"].DefaultCellStyle.Format = "0.0";
                    dgvGuideRatings.Columns["min_rating"].DefaultCellStyle.Format = "0.0";
                    dgvGuideRatings.Columns["max_rating"].DefaultCellStyle.Format = "0.0";
                    dgvGuideRatings.Columns["base_rating"].DefaultCellStyle.Format = "0.0";

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

                    Series baseSeries = chartGuideRatings.Series.Add("Base Rating");
                    baseSeries.ChartType = SeriesChartType.Column;
                    baseSeries.Color = Color.Gray;

                    foreach (DataRow row in dt.Rows)
                    {
                        string guideName = row["guide_name"].ToString();
                        double avgRating = row["avg_rating"] != DBNull.Value ? Convert.ToDouble(row["avg_rating"]) : 0;
                        double baseRating = Convert.ToDouble(row["base_rating"]);

                        avgSeries.Points.AddXY(guideName, avgRating);
                        baseSeries.Points.AddXY(guideName, baseRating);
                    }

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

        private void GenerateTransportPerformanceReport(DateTime startDate, DateTime endDate)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            tp.provider_name,
                            tp.vehicle_type,
                            COUNT(ts.schedule_id) AS total_trips,
                            SUM(CASE WHEN ta.delay_minutes IS NULL OR ta.delay_minutes <= 0 THEN 1 ELSE 0 END) AS on_time_trips,
                            SUM(CASE WHEN ta.delay_minutes > 0 THEN 1 ELSE 0 END) AS delayed_trips,
                            AVG(ISNULL(ta.delay_minutes, 0)) AS avg_delay_minutes,
                            (SUM(CASE WHEN ta.delay_minutes IS NULL OR ta.delay_minutes <= 0 THEN 1 ELSE 0 END) * 100.0 / 
                                COUNT(ts.schedule_id)) AS on_time_percentage
                        FROM TransportProviders tp
                        JOIN TransportSchedules ts ON tp.provider_id = ts.provider_id
                        LEFT JOIN TransportActuals ta ON ts.schedule_id = ta.schedule_id
                        WHERE ts.trip_date BETWEEN @StartDate AND @EndDate
                        GROUP BY tp.provider_name, tp.vehicle_type
                        ORDER BY on_time_percentage DESC";

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

                    connection.Open();

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

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

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

                    Series delaySeries = chartTransportPerformance.Series.Add("Average Delay (min)");
                    delaySeries.ChartType = SeriesChartType.Line;
                    delaySeries.YAxisType = AxisType.Secondary;
                    delaySeries.Color = Color.Red;
                    delaySeries.BorderWidth = 3;

                    foreach (DataRow row in dt.Rows)
                    {
                        string providerName = row["provider_name"].ToString();
                        double onTimePercent = Convert.ToDouble(row["on_time_percentage"]);
                        double avgDelay = Convert.ToDouble(row["avg_delay_minutes"]);

                        onTimeSeries.Points.AddXY(providerName, onTimePercent);
                        delaySeries.Points.AddXY(providerName, avgDelay);
                    }

                    chartTransportPerformance.ChartAreas[0].AxisY.Maximum = 100;
                    chartTransportPerformance.ChartAreas[0].AxisY.Minimum = 0;
                    chartTransportPerformance.ChartAreas[0].AxisY.LabelStyle.Format = "0%";

                    chartTransportPerformance.ChartAreas[0].AxisY2.Enabled = AxisEnabled.True;
                    chartTransportPerformance.ChartAreas[0].AxisY2.LabelStyle.Format = "0";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating transport performance report: {ex.Message}");
            }
        }

        private void GenerateServiceUtilizationReport(DateTime startDate, DateTime endDate)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            sp.provider_name,
                            sp.service_type,
                            sp.capacity_units,
                            SUM(sb.units_utilized) AS total_units_utilized,
                            SUM(sb.revenue) AS total_revenue,
                            (SUM(sb.units_utilized) * 100.0 / 
                                (sp.capacity_units * DATEDIFF(DAY, @StartDate, @EndDate) + 1)) AS utilization_rate
                        FROM ServiceProviders sp
                        LEFT JOIN ServiceBookings sb ON sp.provider_id = sb.provider_id
                            AND sb.booking_date BETWEEN @StartDate AND @EndDate
                        GROUP BY sp.provider_name, sp.service_type, sp.capacity_units
                        ORDER BY utilization_rate DESC";

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

                    connection.Open();

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

                    // Update DataGridView
                    dgvServiceUtilization.DataSource = dt;
                    dgvServiceUtilization.Columns["utilization_rate"].DefaultCellStyle.Format = "0.00%";
                    dgvServiceUtilization.Columns["total_revenue"].DefaultCellStyle.Format = "C2";

                    // Update Chart
                    chartServiceUtilization.Series.Clear();
                    Series utilizationSeries = chartServiceUtilization.Series.Add("Utilization Rate");
                    utilizationSeries.ChartType = SeriesChartType.Bar;

                    foreach (DataRow row in dt.Rows)
                    {
                        string providerName = row["provider_name"].ToString();
                        double utilizationRate = Convert.ToDouble(row["utilization_rate"]);
                        utilizationSeries.Points.AddXY(providerName, utilizationRate);
                    }

                    chartServiceUtilization.ChartAreas[0].AxisY.Maximum = 100;
                    chartServiceUtilization.ChartAreas[0].AxisY.Minimum = 0;
                    chartServiceUtilization.ChartAreas[0].AxisY.LabelStyle.Format = "0%";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating service utilization report: {ex.Message}");
            }
        }
    }
}