PlatformGrowthReportForm.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 PlatformGrowthReportForm : 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 platform growth analysis
        private TabPage tabNewUsers;
        private TabPage tabActiveUsers;
        private TabPage tabPartnerships;
        private TabPage tabRegionalExpansion;

        // Charts and grids
        private Chart chartNewUsers;
        private Chart chartActiveUsers;
        private Chart chartPartnerships;
        private Chart chartRegionalExpansion;
        private DataGridView dgvNewUsers;
        private DataGridView dgvActiveUsers;
        private DataGridView dgvPartnerships;
        private DataGridView dgvRegionalExpansion;

        public PlatformGrowthReportForm()
        {
            InitializeComponent();
        }

        private void InitializeComponent()
        {
            this.Text = "Platform Growth 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(-3);

            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
            CreateNewUsersTab();
            CreateActiveUsersTab();
            CreatePartnershipsTab();
            CreateRegionalExpansionTab();

            // 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 CreateNewUsersTab()
        {
            tabNewUsers = new TabPage();
            tabNewUsers.Text = "New Users";

            chartNewUsers = new Chart();
            chartNewUsers.Location = new Point(20, 20);
            chartNewUsers.Size = new Size(880, 250);
            chartNewUsers.Titles.Add("New User Registrations Over Time");
            chartNewUsers.ChartAreas.Add(new ChartArea("ChartArea1"));
            chartNewUsers.Legends.Add(new Legend("Legend1"));

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

            tabNewUsers.Controls.Add(chartNewUsers);
            tabNewUsers.Controls.Add(dgvNewUsers);
            tabControl.TabPages.Add(tabNewUsers);
        }

        private void CreateActiveUsersTab()
        {
            tabActiveUsers = new TabPage();
            tabActiveUsers.Text = "Active Users";

            chartActiveUsers = new Chart();
            chartActiveUsers.Location = new Point(20, 20);
            chartActiveUsers.Size = new Size(880, 250);
            chartActiveUsers.Titles.Add("Active Users Over Time");
            chartActiveUsers.ChartAreas.Add(new ChartArea("ChartArea1"));
            chartActiveUsers.Legends.Add(new Legend("Legend1"));

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

            tabActiveUsers.Controls.Add(chartActiveUsers);
            tabActiveUsers.Controls.Add(dgvActiveUsers);
            tabControl.TabPages.Add(tabActiveUsers);
        }

        private void CreatePartnershipsTab()
        {
            tabPartnerships = new TabPage();
            tabPartnerships.Text = "Partnerships";

            chartPartnerships = new Chart();
            chartPartnerships.Location = new Point(20, 20);
            chartPartnerships.Size = new Size(880, 250);
            chartPartnerships.Titles.Add("Partnership Growth Over Time");
            chartPartnerships.ChartAreas.Add(new ChartArea("ChartArea1"));
            chartPartnerships.Legends.Add(new Legend("Legend1"));

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

            tabPartnerships.Controls.Add(chartPartnerships);
            tabPartnerships.Controls.Add(dgvPartnerships);
            tabControl.TabPages.Add(tabPartnerships);
        }

        private void CreateRegionalExpansionTab()
        {
            tabRegionalExpansion = new TabPage();
            tabRegionalExpansion.Text = "Regional Expansion";

            chartRegionalExpansion = new Chart();
            chartRegionalExpansion.Location = new Point(20, 20);
            chartRegionalExpansion.Size = new Size(880, 250);
            chartRegionalExpansion.Titles.Add("User Distribution by Region");
            chartRegionalExpansion.ChartAreas.Add(new ChartArea("ChartArea1"));
            chartRegionalExpansion.Legends.Add(new Legend("Legend1"));

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

            tabRegionalExpansion.Controls.Add(chartRegionalExpansion);
            tabRegionalExpansion.Controls.Add(dgvRegionalExpansion);
            tabControl.TabPages.Add(tabRegionalExpansion);
        }

        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
                GenerateNewUsersReport(startDate, endDate);
                GenerateActiveUsersReport(startDate, endDate);
                GeneratePartnershipsReport(startDate, endDate);
                GenerateRegionalExpansionReport(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 GenerateNewUsersReport(DateTime startDate, DateTime endDate)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            CONVERT(DATE, created_at) AS RegistrationDate,
                            COUNT(*) AS NewUsers,
                            SUM(CASE WHEN role = 'traveler' THEN 1 ELSE 0 END) AS Travelers,
                            SUM(CASE WHEN role = 'operator' THEN 1 ELSE 0 END) AS Operators,
                            SUM(CASE WHEN role = 'service_provider' THEN 1 ELSE 0 END) AS ServiceProviders
                        FROM AppUser
                        WHERE created_at BETWEEN @StartDate AND @EndDate
                        GROUP BY CONVERT(DATE, created_at)
                        ORDER BY RegistrationDate";

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

                    // Update Chart
                    chartNewUsers.Series.Clear();

                    Series totalSeries = chartNewUsers.Series.Add("Total New Users");
                    totalSeries.ChartType = SeriesChartType.Line;
                    totalSeries.BorderWidth = 3;
                    totalSeries.MarkerStyle = MarkerStyle.Circle;
                    totalSeries.MarkerSize = 8;

                    Series travelersSeries = chartNewUsers.Series.Add("Travelers");
                    travelersSeries.ChartType = SeriesChartType.Column;

                    Series operatorsSeries = chartNewUsers.Series.Add("Operators");
                    operatorsSeries.ChartType = SeriesChartType.Column;

                    Series providersSeries = chartNewUsers.Series.Add("Service Providers");
                    providersSeries.ChartType = SeriesChartType.Column;

                    foreach (DataRow row in dt.Rows)
                    {
                        string date = Convert.ToDateTime(row["RegistrationDate"]).ToString("MMM dd");
                        int total = Convert.ToInt32(row["NewUsers"]);
                        int travelers = Convert.ToInt32(row["Travelers"]);
                        int operators = Convert.ToInt32(row["Operators"]);
                        int providers = Convert.ToInt32(row["ServiceProviders"]);

                        totalSeries.Points.AddXY(date, total);
                        travelersSeries.Points.AddXY(date, travelers);
                        operatorsSeries.Points.AddXY(date, operators);
                        providersSeries.Points.AddXY(date, providers);
                    }

                    chartNewUsers.ChartAreas[0].AxisY.Title = "Number of Users";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating new users report: {ex.Message}");
            }
        }

        private void GenerateActiveUsersReport(DateTime startDate, DateTime endDate)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        WITH ActiveUsers AS (
                            SELECT DISTINCT 
                                CONVERT(DATE, b.booking_date) AS ActivityDate,
                                b.traveler_id AS UserID,
                                'traveler' AS UserType
                            FROM Booking b
                            WHERE b.booking_date BETWEEN @StartDate AND @EndDate
                            
                            UNION
                            
                            SELECT DISTINCT 
                                CONVERT(DATE, t.created_at) AS ActivityDate,
                                t.operator_id AS UserID,
                                'operator' AS UserType
                            FROM Trip t
                            WHERE t.created_at BETWEEN @StartDate AND @EndDate
                            
                            UNION
                            
                            SELECT DISTINCT 
                                CONVERT(DATE, s.created_at) AS ActivityDate,
                                s.provider_id AS UserID,
                                'service_provider' AS UserType
                            FROM Service s
                            WHERE s.created_at BETWEEN @StartDate AND @EndDate
                        )
                        SELECT 
                            ActivityDate,
                            COUNT(*) AS ActiveUsers,
                            SUM(CASE WHEN UserType = 'traveler' THEN 1 ELSE 0 END) AS Travelers,
                            SUM(CASE WHEN UserType = 'operator' THEN 1 ELSE 0 END) AS Operators,
                            SUM(CASE WHEN UserType = 'service_provider' THEN 1 ELSE 0 END) AS ServiceProviders
                        FROM ActiveUsers
                        GROUP BY ActivityDate
                        ORDER BY ActivityDate";

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

                    // Update Chart
                    chartActiveUsers.Series.Clear();

                    Series totalSeries = chartActiveUsers.Series.Add("Total Active Users");
                    totalSeries.ChartType = SeriesChartType.Line;
                    totalSeries.BorderWidth = 3;
                    totalSeries.MarkerStyle = MarkerStyle.Circle;
                    totalSeries.MarkerSize = 8;

                    Series travelersSeries = chartActiveUsers.Series.Add("Travelers");
                    travelersSeries.ChartType = SeriesChartType.Column;

                    Series operatorsSeries = chartActiveUsers.Series.Add("Operators");
                    operatorsSeries.ChartType = SeriesChartType.Column;

                    Series providersSeries = chartActiveUsers.Series.Add("Service Providers");
                    providersSeries.ChartType = SeriesChartType.Column;

                    foreach (DataRow row in dt.Rows)
                    {
                        string date = Convert.ToDateTime(row["ActivityDate"]).ToString("MMM dd");
                        int total = Convert.ToInt32(row["ActiveUsers"]);
                        int travelers = Convert.ToInt32(row["Travelers"]);
                        int operators = Convert.ToInt32(row["Operators"]);
                        int providers = Convert.ToInt32(row["ServiceProviders"]);

                        totalSeries.Points.AddXY(date, total);
                        travelersSeries.Points.AddXY(date, travelers);
                        operatorsSeries.Points.AddXY(date, operators);
                        providersSeries.Points.AddXY(date, providers);
                    }

                    chartActiveUsers.ChartAreas[0].AxisY.Title = "Number of Active Users";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating active users report: {ex.Message}");
            }
        }

        private void GeneratePartnershipsReport(DateTime startDate, DateTime endDate)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        SELECT 
                            CONVERT(DATE, u.created_at) AS PartnershipDate,
                            COUNT(*) AS NewPartners,
                            SUM(CASE WHEN u.role = 'operator' THEN 1 ELSE 0 END) AS TourOperators,
                            SUM(CASE WHEN u.role = 'service_provider' THEN 1 ELSE 0 END) AS ServiceProviders,
                            SUM(CASE WHEN u.registration_status = 'approved' THEN 1 ELSE 0 END) AS Approved,
                            SUM(CASE WHEN u.registration_status = 'pending' THEN 1 ELSE 0 END) AS Pending
                        FROM AppUser u
                        WHERE u.created_at BETWEEN @StartDate AND @EndDate
                        AND u.role IN ('operator', 'service_provider')
                        GROUP BY CONVERT(DATE, u.created_at)
                        ORDER BY PartnershipDate";

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

                    // Update Chart
                    chartPartnerships.Series.Clear();

                    Series totalSeries = chartPartnerships.Series.Add("Total New Partners");
                    totalSeries.ChartType = SeriesChartType.Line;
                    totalSeries.BorderWidth = 3;
                    totalSeries.MarkerStyle = MarkerStyle.Circle;
                    totalSeries.MarkerSize = 8;

                    Series operatorsSeries = chartPartnerships.Series.Add("Tour Operators");
                    operatorsSeries.ChartType = SeriesChartType.Column;

                    Series providersSeries = chartPartnerships.Series.Add("Service Providers");
                    providersSeries.ChartType = SeriesChartType.Column;

                    foreach (DataRow row in dt.Rows)
                    {
                        string date = Convert.ToDateTime(row["PartnershipDate"]).ToString("MMM dd");
                        int total = Convert.ToInt32(row["NewPartners"]);
                        int operators = Convert.ToInt32(row["TourOperators"]);
                        int providers = Convert.ToInt32(row["ServiceProviders"]);

                        totalSeries.Points.AddXY(date, total);
                        operatorsSeries.Points.AddXY(date, operators);
                        providersSeries.Points.AddXY(date, providers);
                    }

                    chartPartnerships.ChartAreas[0].AxisY.Title = "Number of Partners";
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating partnerships report: {ex.Message}");
            }
        }

        private void GenerateRegionalExpansionReport(DateTime startDate, DateTime endDate)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                        -- For travelers (using nationality from TravelerProfile)
                        SELECT 
                            ISNULL(t.nationality, 'Unknown') AS Region,
                            COUNT(*) AS UserCount,
                            'Traveler' AS UserType
                        FROM AppUser u
                        JOIN TravelerProfile t ON u.user_id = t.user_id
                        WHERE u.created_at BETWEEN @StartDate AND @EndDate
                        GROUP BY t.nationality
                        
                        UNION
                        
                        -- For operators (using destination from Trip)
                        SELECT 
                            ISNULL(t.destination, 'Unknown') AS Region,
                            COUNT(DISTINCT u.user_id) AS UserCount,
                            'Operator' AS UserType
                        FROM AppUser u
                        JOIN TourOperatorProfile op ON u.user_id = op.user_id
                        JOIN Trip t ON op.user_id = t.operator_id
                        WHERE u.created_at BETWEEN @StartDate AND @EndDate
                        GROUP BY t.destination
                        
                        UNION
                        
                        -- For service providers (using location from ServiceProviderProfile)
                        SELECT 
                            ISNULL(sp.location, 'Unknown') AS Region,
                            COUNT(*) AS UserCount,
                            'Service Provider' AS UserType
                        FROM AppUser u
                        JOIN ServiceProviderProfile sp ON u.user_id = sp.user_id
                        WHERE u.created_at BETWEEN @StartDate AND @EndDate
                        GROUP BY sp.location
                        
                        ORDER BY UserCount 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
                    dgvRegionalExpansion.DataSource = dt;

                    // Update Chart (top 10 regions by user count)
                    chartRegionalExpansion.Series.Clear();
                    Series series = chartRegionalExpansion.Series.Add("Users by Region");
                    series.ChartType = SeriesChartType.Pie;
                    series.IsValueShownAsLabel = true;
                    series.LabelFormat = "#,##0";

                    // Limit to top 10 regions for better visualization
                    int maxRows = Math.Min(10, dt.Rows.Count);
                    for (int i = 0; i < maxRows; i++)
                    {
                        string region = dt.Rows[i]["Region"].ToString();
                        int count = Convert.ToInt32(dt.Rows[i]["UserCount"]);
                        series.Points.AddXY($"{region} ({count})", count);
                    }

                    chartRegionalExpansion.Titles.Clear();
                    chartRegionalExpansion.Titles.Add("User Distribution by Region (Top 10)");
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error generating regional expansion report: {ex.Message}");
            }
        }
    }
}