TourOperatorPerformanceForm.cs

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

namespace H1
{
    public partial class TourOperatorPerformanceForm : Form
    {
        private string connectionString = @"Data Source=ABDULSABOOR190\SQLEXPRESS;Initial Catalog=sabbbb;Integrated Security=True;Encrypt=False";

        // Date controls
        private DateTimePicker dtpStartDate;
        private DateTimePicker dtpEndDate;
        private Button btnGenerateReport;
        private Label lblDateRange;
        private Label lblTo;

        // Tab controls
        private TabControl tabControl;
        private TabPage tabAvgRating;
        private TabPage tabRevenue;
        private TabPage tabResponseTime;

        // Charts
        private Chart chartAvgRating;
        private Chart chartRevenue;
        private Chart chartResponseTime;

        // DataGridViews
        private DataGridView dgvAvgRating;
        private DataGridView dgvRevenue;
        private DataGridView dgvResponseTime;

        public TourOperatorPerformanceForm()
        {
            InitializeComponent();
        }

        private void InitializeComponent()
        {
            this.Text = "Tour Operator Performance Report";
            this.Size = new Size(900, 700);
            this.StartPosition = FormStartPosition.CenterScreen;

            // Date Range Controls
            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(-3);

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

            dtpEndDate = new DateTimePicker();
            dtpEndDate.Location = new Point(320, 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(840, 580);

            // Average Rating Tab
            tabAvgRating = new TabPage();
            tabAvgRating.Text = "Average Rating";

            chartAvgRating = new Chart();
            chartAvgRating.Location = new Point(20, 20);
            chartAvgRating.Size = new Size(780, 250);
            chartAvgRating.Titles.Add("Average Operator Ratings");
            chartAvgRating.ChartAreas.Add(new ChartArea("ChartArea1"));
            chartAvgRating.Legends.Add(new Legend("Legend1"));

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

            tabAvgRating.Controls.Add(chartAvgRating);
            tabAvgRating.Controls.Add(dgvAvgRating);

            // Revenue Tab
            tabRevenue = new TabPage();
            tabRevenue.Text = "Revenue";

            chartRevenue = new Chart();
            chartRevenue.Location = new Point(20, 20);
            chartRevenue.Size = new Size(780, 250);
            chartRevenue.Titles.Add("Revenue per Operator");
            chartRevenue.ChartAreas.Add(new ChartArea("ChartArea1"));
            chartRevenue.Legends.Add(new Legend("Legend1"));

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

            tabRevenue.Controls.Add(chartRevenue);
            tabRevenue.Controls.Add(dgvRevenue);

            // Response Time Tab
            tabResponseTime = new TabPage();
            tabResponseTime.Text = "Response Time";

            chartResponseTime = new Chart();
            chartResponseTime.Location = new Point(20, 20);
            chartResponseTime.Size = new Size(780, 250);
            chartResponseTime.Titles.Add("Average Response Time (minutes)");
            chartResponseTime.ChartAreas.Add(new ChartArea("ChartArea1"));
            chartResponseTime.Legends.Add(new Legend("Legend1"));

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

            tabResponseTime.Controls.Add(chartResponseTime);
            tabResponseTime.Controls.Add(dgvResponseTime);

            // Add tabs to tab control
            tabControl.TabPages.Add(tabAvgRating);
            tabControl.TabPages.Add(tabRevenue);
            tabControl.TabPages.Add(tabResponseTime);

            // 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 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
                GenerateAverageRatingReport(startDate, endDate);
                GenerateRevenueReport(startDate, endDate);
                GenerateResponseTimeReport(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 GenerateAverageRatingReport(DateTime startDate, DateTime endDate)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            op.user_id,
                            op.company_name AS 'Operator Name',
                            COUNT(r.rating) AS 'Number of Ratings',
                            AVG(r.rating) AS 'Average Rating'
                        FROM TourOperatorProfile op
                        LEFT JOIN OperatorRating r ON op.user_id = r.operator_id
                        WHERE r.rating_date BETWEEN @StartDate AND @EndDate
                        GROUP BY op.user_id, op.company_name
                        ORDER BY AVG(r.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
                    dgvAvgRating.DataSource = dt;
                    dgvAvgRating.Columns["Average Rating"].DefaultCellStyle.Format = "0.0";

                    // Update Chart
                    chartAvgRating.Series.Clear();
                    Series series = chartAvgRating.Series.Add("Ratings");
                    series.ChartType = SeriesChartType.Bar;

                    foreach (DataRow row in dt.Rows)
                    {
                        string operatorName = row["Operator Name"].ToString();
                        double avgRating = Convert.ToDouble(row["Average Rating"]);
                        series.Points.AddXY(operatorName, avgRating);
                        series.Points.Last().Label = avgRating.ToString("0.0");
                    }

                    chartAvgRating.Titles.Clear();
                    chartAvgRating.Titles.Add($"Average Operator Ratings ({startDate:MM/dd/yyyy} - {endDate:MM/dd/yyyy})");
                    chartAvgRating.ChartAreas[0].AxisY.Maximum = 5;
                    chartAvgRating.ChartAreas[0].AxisY.Minimum = 0;
                    chartAvgRating.ChartAreas[0].AxisY.Interval = 1;
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating average rating report: {ex.Message}");
            }
        }

        private void GenerateRevenueReport(DateTime startDate, DateTime endDate)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            op.user_id,
                            op.company_name AS 'Operator Name',
                            COUNT(b.booking_id) AS 'Number of Bookings',
                            SUM(b.total_amount) AS 'Total Revenue',
                            SUM(b.total_amount) / COUNT(DISTINCT b.trip_id) AS 'Revenue per Trip'
                        FROM TourOperatorProfile op
                        JOIN Trip t ON op.user_id = t.operator_id
                        JOIN Booking b ON t.trip_id = b.trip_id
                        WHERE b.booking_date BETWEEN @StartDate AND @EndDate
                        AND b.status != 'canceled'
                        GROUP BY op.user_id, op.company_name
                        ORDER BY SUM(b.total_amount) 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
                    dgvRevenue.DataSource = dt;
                    dgvRevenue.Columns["Total Revenue"].DefaultCellStyle.Format = "C2";
                    dgvRevenue.Columns["Revenue per Trip"].DefaultCellStyle.Format = "C2";

                    // Update Chart
                    chartRevenue.Series.Clear();
                    Series seriesRevenue = chartRevenue.Series.Add("Total Revenue");
                    seriesRevenue.ChartType = SeriesChartType.Bar;

                    Series seriesPerTrip = chartRevenue.Series.Add("Revenue per Trip");
                    seriesPerTrip.ChartType = SeriesChartType.Bar;

                    foreach (DataRow row in dt.Rows)
                    {
                        string operatorName = row["Operator Name"].ToString();
                        decimal totalRevenue = Convert.ToDecimal(row["Total Revenue"]);
                        decimal revenuePerTrip = Convert.ToDecimal(row["Revenue per Trip"]);

                        seriesRevenue.Points.AddXY(operatorName, totalRevenue);
                        seriesPerTrip.Points.AddXY(operatorName, revenuePerTrip);
                    }

                    chartRevenue.Titles.Clear();
                    chartRevenue.Titles.Add($"Operator Revenue ({startDate:MM/dd/yyyy} - {endDate:MM/dd/yyyy})");
                    chartRevenue.ChartAreas[0].AxisY.LabelStyle.Format = "C0";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating revenue report: {ex.Message}");
            }
        }

        private void GenerateResponseTimeReport(DateTime startDate, DateTime endDate)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            op.user_id,
                            op.company_name AS 'Operator Name',
                            COUNT(r.response_id) AS 'Number of Responses',
                            AVG(r.response_time_minutes) AS 'Average Response Time (minutes)',
                            MIN(r.response_time_minutes) AS 'Fastest Response',
                            MAX(r.response_time_minutes) AS 'Slowest Response'
                        FROM TourOperatorProfile op
                        JOIN OperatorResponse r ON op.user_id = r.operator_id
                        WHERE r.response_date BETWEEN @StartDate AND @EndDate
                        GROUP BY op.user_id, op.company_name
                        ORDER BY AVG(r.response_time_minutes)";

                    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
                    dgvResponseTime.DataSource = dt;

                    // Update Chart
                    chartResponseTime.Series.Clear();
                    Series seriesAvg = chartResponseTime.Series.Add("Average Time");
                    seriesAvg.ChartType = SeriesChartType.Bar;

                    Series seriesFastest = chartResponseTime.Series.Add("Fastest");
                    seriesFastest.ChartType = SeriesChartType.Point;
                    seriesFastest.MarkerStyle = MarkerStyle.Circle;
                    seriesFastest.MarkerSize = 8;
                    seriesFastest.Color = Color.Green;

                    Series seriesSlowest = chartResponseTime.Series.Add("Slowest");
                    seriesSlowest.ChartType = SeriesChartType.Point;
                    seriesSlowest.MarkerStyle = MarkerStyle.Circle;
                    seriesSlowest.MarkerSize = 8;
                    seriesSlowest.Color = Color.Red;

                    foreach (DataRow row in dt.Rows)
                    {
                        string operatorName = row["Operator Name"].ToString();
                        double avgTime = Convert.ToDouble(row["Average Response Time (minutes)"]);
                        double fastest = Convert.ToDouble(row["Fastest Response"]);
                        double slowest = Convert.ToDouble(row["Slowest Response"]);

                        seriesAvg.Points.AddXY(operatorName, avgTime);
                        seriesFastest.Points.AddXY(operatorName, fastest);
                        seriesSlowest.Points.AddXY(operatorName, slowest);
                    }

                    chartResponseTime.Titles.Clear();
                    chartResponseTime.Titles.Add($"Operator Response Times ({startDate:MM/dd/yyyy} - {endDate:MM/dd/yyyy})");
                    chartResponseTime.ChartAreas[0].AxisY.Title = "Response Time (minutes)";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating response time report: {ex.Message}");
            }
        }
    }
}