AnalyticsFilter.cs

using H1;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;

namespace DB_Project
{
    public partial class AnalyticsFilter : Form
    {
        // Update the connection string with your provided connection string
        private string connectionString = @"Data Source=ABDULSABOOR190\SQLEXPRESS;Initial Catalog=sab;Integrated Security=True;Encrypt=False";

        public AnalyticsFilter()
        {
            InitializeComponent();
            LoadTourCategories();
        }

        private void LoadTourCategories()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string query = "SELECT name FROM TourCategory";
                    SqlCommand command = new SqlCommand(query, connection);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        comboBox1.Items.Clear();
                        while (reader.Read())
                        {
                            comboBox1.Items.Add(reader["name"].ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading tour categories: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            // You can add functionality here if needed
        }

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

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

        private void button4_Click(object sender, EventArgs e)
        {
            Form2 form2 = new Form2(2);
            form2.Show();
        }

        private void button6_Click(object sender, EventArgs e)
        {
            Review_Moderation_Form review_Moderation_Form = new Review_Moderation_Form();
            review_Moderation_Form.Show();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            StatusManagement statusManagement = new StatusManagement();
            statusManagement.Show();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            try
            {
                // Define the filter criteria
                DateTime dateFrom = dateTimePicker1.Value;
                DateTime dateTo = dateTimePicker2.Value;
                string userType = comboBox2.SelectedItem?.ToString();
                string tourCategory = comboBox1.SelectedItem?.ToString();

                // Validate date range
                if (dateTo < dateFrom)
                {
                    MessageBox.Show("End date cannot be earlier than start date.", "Invalid Date Range", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }

                // Format dates for SQL query
                string formattedDateFrom = dateFrom.ToString("yyyy-MM-dd");
                string formattedDateTo = dateTo.ToString("yyyy-MM-dd");

                // Build the base query based on the database schema
                StringBuilder queryBuilder = new StringBuilder();

                queryBuilder.Append(@"
                SELECT 
                    t.trip_id, 
                    t.title AS TripTitle,
                    t.destination,
                    t.start_date,
                    t.end_date,
                    t.price,
                    tc.name AS CategoryName,
                    op.company_name AS OperatorName,
                    u.role AS UserRole,
                    COUNT(b.booking_id) AS BookingCount,
                    AVG(ISNULL(r.rating, 0)) AS AverageRating
                FROM 
                    Trip t
                    INNER JOIN TourOperatorProfile op ON t.operator_id = op.user_id
                    INNER JOIN AppUser u ON op.user_id = u.user_id
                    LEFT JOIN TripCategory tcat ON t.trip_id = tcat.trip_id
                    LEFT JOIN TourCategory tc ON tcat.category_id = tc.category_id
                    LEFT JOIN Booking b ON t.trip_id = b.trip_id
                    LEFT JOIN Review r ON b.booking_id = r.booking_id
                WHERE 
                    t.start_date >= @DateFrom AND t.end_date <= @DateTo
                ");

                // Add filters based on selection
                if (!string.IsNullOrEmpty(userType))
                {
                    queryBuilder.Append(" AND u.role = @UserType");
                }

                if (!string.IsNullOrEmpty(tourCategory))
                {
                    queryBuilder.Append(" AND tc.name = @TourCategory");
                }

                // Group by to avoid duplicate results
                queryBuilder.Append(@"
                GROUP BY 
                    t.trip_id, 
                    t.title,
                    t.destination,
                    t.start_date,
                    t.end_date,
                    t.price,
                    tc.name,
                    op.company_name,
                    u.role
                ORDER BY t.start_date
                ");

                // Execute the query
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(queryBuilder.ToString(), connection))
                    {
                        // Add parameters to prevent SQL injection
                        command.Parameters.AddWithValue("@DateFrom", formattedDateFrom);
                        command.Parameters.AddWithValue("@DateTo", formattedDateTo);

                        if (!string.IsNullOrEmpty(userType))
                            command.Parameters.AddWithValue("@UserType", userType);

                        if (!string.IsNullOrEmpty(tourCategory))
                            command.Parameters.AddWithValue("@TourCategory", tourCategory);

                        // Open connection
                        connection.Open();

                        // Create a DataTable to hold the results
                        DataTable dataTable = new DataTable();
                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(dataTable);
                        }

                        // Show results in a DataGridView or format for a message box
                        if (dataTable.Rows.Count > 0)
                        {
                            // Create a formatted string for display in a message box
                            StringBuilder resultBuilder = new StringBuilder();
                            resultBuilder.AppendLine($"Filter Results ({dataTable.Rows.Count} trips found):\n");

                            foreach (DataRow row in dataTable.Rows)
                            {
                                resultBuilder.AppendLine($"Trip: {row["TripTitle"]}");
                                resultBuilder.AppendLine($"Destination: {row["destination"]}");
                                resultBuilder.AppendLine($"Date: {Convert.ToDateTime(row["start_date"]).ToShortDateString()} - {Convert.ToDateTime(row["end_date"]).ToShortDateString()}");
                                resultBuilder.AppendLine($"Category: {row["CategoryName"]}");
                                resultBuilder.AppendLine($"Operator: {row["OperatorName"]} ({row["UserRole"]})");
                                resultBuilder.AppendLine($"Price: ${row["price"]}");
                                resultBuilder.AppendLine($"Bookings: {row["BookingCount"]}");
                                resultBuilder.AppendLine($"Avg Rating: {Math.Round(Convert.ToDouble(row["AverageRating"]), 1)}/5.0");
                                resultBuilder.AppendLine("----------------------------------------");
                            }

                            // Display the results
                            MessageBox.Show(resultBuilder.ToString(), "Analytics Results", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                        else
                        {
                            MessageBox.Show("No trips match the selected filters.", "No Results", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error applying filters: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void AnalyticsFilter_Load(object sender, EventArgs e)
        {
            // Set default date range to current month
            DateTime today = DateTime.Today;
            dateTimePicker1.Value = new DateTime(today.Year, today.Month, 1);
            dateTimePicker2.Value = today;

            // Set default items for user type if needed
            comboBox2.SelectedIndex = comboBox2.Items.Count > 0 ? 0 : -1;
        }

        private void button7_Click(object sender, EventArgs e)
        {
            // Reset button functionality
            dateTimePicker1.Value = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            dateTimePicker2.Value = DateTime.Today;
            comboBox1.SelectedIndex = -1;
            comboBox2.SelectedIndex = -1;
        }
    }
}