PerformanceReportForm.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

namespace DB_Project
{
    public partial class PerformanceReportForm : Form
    {
        // Database connection string
        private string connectionString = @"Data Source=ABDULSABOOR190\SQLEXPRESS;Initial Catalog=sabbbb;Integrated Security=True;Encrypt=False";

        // Tracking the currently selected date range
        private DateTime startDate;
        private DateTime endDate;

        // Data variables
        private int totalBookings = 0;
        private decimal totalRevenue = 0;
        private DataTable monthlyTrendsData;

        public PerformanceReportForm()
        {
            InitializeComponent();
            InitializeMonthlyTrendsTable();
        }

        private void InitializeMonthlyTrendsTable()
        {
            // Create data table for monthly trends
            monthlyTrendsData = new DataTable();
            monthlyTrendsData.Columns.Add("Month", typeof(string));
            monthlyTrendsData.Columns.Add("Bookings", typeof(int));
            monthlyTrendsData.Columns.Add("Revenue", typeof(string));
        }

        private void PerformanceReportsForm_Load(object sender, EventArgs e)
        {
            // Initialize date pickers with reasonable defaults (last 30 days)
            dtpStartDate.Value = DateTime.Now.AddDays(-30);
            dtpEndDate.Value = DateTime.Now;

            // Set initial date range values
            startDate = dtpStartDate.Value;
            endDate = dtpEndDate.Value;

            // Load initial data from database
            LoadPerformanceData();
        }

        private void LoadPerformanceData()
        {
            try
            {
                // Load summary data from database
                LoadSummaryDataFromDB();

                // Load monthly trends data from database
                LoadMonthlyTrendsFromDB();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading performance data: " + ex.Message, "Database Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void LoadSummaryDataFromDB()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    // Query to get total bookings and revenue within date range
                    string query = @"SELECT 
                                     COUNT(B.booking_id) AS TotalBookings,
                                     COALESCE(SUM(P.amount), 0) AS TotalRevenue
                                     FROM Booking B
                                     LEFT JOIN Payment P ON B.booking_id = P.booking_id
                                     WHERE B.booking_date BETWEEN @StartDate AND @EndDate
                                     AND P.status = 'completed'";

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

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                // Get total bookings
                                totalBookings = !reader.IsDBNull(reader.GetOrdinal("TotalBookings")) ?
                                               reader.GetInt32(reader.GetOrdinal("TotalBookings")) : 0;

                                // Get total revenue
                                totalRevenue = !reader.IsDBNull(reader.GetOrdinal("TotalRevenue")) ?
                                              reader.GetDecimal(reader.GetOrdinal("TotalRevenue")) : 0;

                                // Update UI with real data
                                lblBookingsValue.Text = totalBookings.ToString();
                                lblRevenueValue.Text = "$" + totalRevenue.ToString("N0");
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Database error while loading summary data: " + ex.Message,
                                    "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    // Fallback to display zeros
                    lblBookingsValue.Text = "0";
                    lblRevenueValue.Text = "$0";
                }
            }
        }

        private void LoadMonthlyTrendsFromDB()
        {
            // Clear existing data
            monthlyTrendsData.Rows.Clear();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    // Query to get monthly booking and revenue data
                    string query = @"SELECT 
                                     FORMAT(B.booking_date, 'MMMM yyyy') AS Month,
                                     COUNT(DISTINCT B.booking_id) AS TotalBookings,
                                     COALESCE(SUM(P.amount), 0) AS TotalRevenue
                                     FROM Booking B
                                     LEFT JOIN Payment P ON B.booking_id = P.booking_id
                                     WHERE B.booking_date BETWEEN DATEADD(MONTH, -5, @EndDate) AND @EndDate
                                     AND (P.status = 'completed' OR P.status IS NULL)
                                     GROUP BY FORMAT(B.booking_date, 'MMMM yyyy'), MONTH(B.booking_date), YEAR(B.booking_date)
                                     ORDER BY YEAR(B.booking_date), MONTH(B.booking_date)";

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

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                string month = reader["Month"].ToString();
                                int bookings = Convert.ToInt32(reader["TotalBookings"]);
                                decimal revenue = Convert.ToDecimal(reader["TotalRevenue"]);

                                // Add data to the DataTable
                                monthlyTrendsData.Rows.Add(month, bookings, "$" + revenue.ToString("N0"));
                            }
                        }
                    }

                    // If no data was returned, add current month with zeros
                    if (monthlyTrendsData.Rows.Count == 0)
                    {
                        monthlyTrendsData.Rows.Add(DateTime.Now.ToString("MMMM yyyy"), 0, "$0");
                    }

                    // Display the data in the DataGridView
                    LoadMonthlyTrendsData();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Database error while loading monthly trends: " + ex.Message,
                                    "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    // Add a fallback row
                    monthlyTrendsData.Rows.Add(DateTime.Now.ToString("MMMM yyyy"), 0, "$0");
                    LoadMonthlyTrendsData();
                }
            }
        }

        private void LoadMonthlyTrendsData()
        {
            // Clear existing data
            dgvMonthlyTrends.Rows.Clear();

            // Add data rows from the data table
            foreach (DataRow row in monthlyTrendsData.Rows)
            {
                dgvMonthlyTrends.Rows.Add(
                    row["Month"],
                    row["Bookings"],
                    row["Revenue"]
                );
            }
        }

        private void btnApplyFilters_Click(object sender, EventArgs e)
        {
            // Validate date range
            if (dtpStartDate.Value > dtpEndDate.Value)
            {
                MessageBox.Show("Start date cannot be after end date.", "Invalid Date Range",
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            // Update date range values
            startDate = dtpStartDate.Value;
            endDate = dtpEndDate.Value;

            // Reload data from database with new date range
            LoadPerformanceData();

            MessageBox.Show($"Report filtered for date range: {startDate.ToShortDateString()} - {endDate.ToShortDateString()}",
                "Filters Applied", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void btnExportReport_Click(object sender, EventArgs e)
        {
            // Show a save file dialog
            using (SaveFileDialog saveDialog = new SaveFileDialog())
            {
                saveDialog.Filter = "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*";
                saveDialog.DefaultExt = "csv";
                saveDialog.FileName = "PerformanceReport_" + DateTime.Now.ToString("yyyyMMdd");

                if (saveDialog.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        // Export data to CSV file
                        ExportToCSV(saveDialog.FileName);
                        MessageBox.Show("Report exported successfully!", "Export Complete",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error exporting report: " + ex.Message, "Export Error",
                            MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }

        private void ExportToCSV(string fileName)
        {
            // Create a StreamWriter to write to the file
            using (StreamWriter writer = new StreamWriter(fileName))
            {
                // Write header for summary section
                writer.WriteLine("TRAVEL EXPLORER PERFORMANCE REPORT");
                writer.WriteLine($"Date Range: {startDate.ToShortDateString()} - {endDate.ToShortDateString()}");
                writer.WriteLine();

                // Write summary data
                writer.WriteLine("SUMMARY");
                writer.WriteLine($"Total Bookings,{totalBookings}");
                writer.WriteLine($"Total Revenue,${totalRevenue:N2}");
                writer.WriteLine();

                // Write header for monthly trends
                writer.WriteLine("MONTHLY TRENDS");
                writer.WriteLine("Month,Bookings,Revenue");

                // Write monthly trends data
                foreach (DataRow row in monthlyTrendsData.Rows)
                {
                    writer.WriteLine($"{row["Month"]},{row["Bookings"]},{row["Revenue"]}");
                }
            }
        }

        // Navigation button event handlers
        private void btnRegistration_Click_1(object sender, EventArgs e)
        {
            //ServiceIntegrationForm serviceIntegrationForm = new ServiceIntegrationForm();
            //serviceIntegrationForm.Show();
        }

        private void lblTravelExplorer_Click(object sender, EventArgs e)
        {
            // Empty event handler
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ServiceListingForm serviceListingForm = new ServiceListingForm();
            serviceListingForm.Show();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            BookingManagementForm bookingManagementForm = new BookingManagementForm();
            bookingManagementForm.Show();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            // Since we're already on the Performance Report Form, let's just refresh the data
            LoadPerformanceData();
        }
    }
}