ProviderApp/ProviderApp/AnalyticsForm.cs

using System;
using System.Data;
using System.Windows.Forms;
using Npgsql;

namespace ProviderApp
{
    public partial class AnalyticsForm : UserControl
    {
        public AnalyticsForm()
        {
            InitializeComponent();
            this.Load += AnalyticsForm_Load;
            btnShow.Click += BtnShow_Click;
            btnExport.Click += BtnExport_Click;
            btnExportRevenue.Click += BtnExportRevenue_Click;
            btnFinancialReport.Click += BtnFinancialReport_Click;
            StyleDataGridView();
        }

        public void RefreshData()
        {
            ExecuteAnalytics();
        }

        private void StyleDataGridView()
        {
            dgvResult.ColumnHeadersDefaultCellStyle.BackColor = System.Drawing.Color.FromArgb(230, 92, 0);
            dgvResult.ColumnHeadersDefaultCellStyle.ForeColor = System.Drawing.Color.White;
            dgvResult.ColumnHeadersDefaultCellStyle.Font = new System.Drawing.Font("Segoe UI", 11F, System.Drawing.FontStyle.Bold);
            dgvResult.DefaultCellStyle.BackColor = System.Drawing.Color.FromArgb(40, 40, 40);
            dgvResult.DefaultCellStyle.ForeColor = System.Drawing.Color.White;
            dgvResult.AlternatingRowsDefaultCellStyle.BackColor = System.Drawing.Color.FromArgb(45, 45, 45);
            dgvResult.EnableHeadersVisualStyles = false;
        }

        private void AnalyticsForm_Load(object sender, EventArgs e)
        {
            dtpFrom.Value = new DateTime(2024, 1, 1);
            dtpTo.Value = new DateTime(2024, 12, 31);
            ExecuteAnalytics();

            btnExport.Visible = AuthHelper.CurrentUser.IsManager;
            btnExportRevenue.Visible = AuthHelper.CurrentUser.IsManager;
            btnFinancialReport.Visible = AuthHelper.CurrentUser.IsManager;
        }

        private void BtnShow_Click(object sender, EventArgs e)
        {
            ExecuteAnalytics();
        }

        private void ExecuteAnalytics()
        {
            var conn = DBConnection.GetConnection();
            DateTime fromDate = dtpFrom.Value.Date;
            DateTime toDate = dtpTo.Value.Date;

            string callProc = "CALL sizov_ev.get_top_tariffs_by_period(@date_from, @date_to)";
            var cmd = new NpgsqlCommand(callProc, conn);
            cmd.Parameters.Add("@date_from", NpgsqlTypes.NpgsqlDbType.Date).Value = fromDate;
            cmd.Parameters.Add("@date_to", NpgsqlTypes.NpgsqlDbType.Date).Value = toDate;
            cmd.ExecuteNonQuery();
            cmd.Dispose();

            string selectSql = @"
    SELECT 
        tariff_name as ""Тариф"",
        monthly_price as ""Цена, руб"",
        connection_count as ""Количество подключений""
    FROM sizov_ev.top_tariffs_result
    ORDER BY connection_count DESC";

            var cmd2 = new NpgsqlCommand(selectSql, conn);
            DataTable dt = new DataTable();
            var da = new NpgsqlDataAdapter(cmd2);
            da.Fill(dt);
            dgvResult.DataSource = dt;

            da.Dispose();
            cmd2.Dispose();
            conn.Dispose();
        }

        private void BtnFinancialReport_Click(object sender, EventArgs e)
        {
            if (!AuthHelper.CheckPermission("manager")) return;

            try
            {
                using (var conn = DBConnection.GetConnection())
                {
                    string sql = @"
                        SELECT 
                            t.tariff_id,
                            t.name as tariff_name,
                            t.monthly_price,
                            COUNT(DISTINCT c.contract_id) AS contracts_count,
                            COALESCE(SUM(p.amount), 0) AS total_revenue
                        FROM sizov_ev.tariffs t
                        LEFT JOIN sizov_ev.contracts c ON t.tariff_id = c.tariff_id
                        LEFT JOIN sizov_ev.payments p ON c.contract_id = p.contract_id
                        GROUP BY t.tariff_id, t.name, t.monthly_price
                        ORDER BY total_revenue DESC";

                    using (var cmd = new NpgsqlCommand(sql, conn))
                    {
                        DataTable dt = new DataTable();
                        using (var da = new NpgsqlDataAdapter(cmd))
                        {
                            da.Fill(dt);
                        }

                        dgvResult.DataSource = dt;

                        if (dgvResult.Columns["tariff_id"] != null)
                            dgvResult.Columns["tariff_id"].HeaderText = "ID";
                        if (dgvResult.Columns["tariff_name"] != null)
                            dgvResult.Columns["tariff_name"].HeaderText = "Тариф";
                        if (dgvResult.Columns["monthly_price"] != null)
                            dgvResult.Columns["monthly_price"].HeaderText = "Цена, руб";
                        if (dgvResult.Columns["contracts_count"] != null)
                            dgvResult.Columns["contracts_count"].HeaderText = "Кол-во договоров";
                        if (dgvResult.Columns["total_revenue"] != null)
                            dgvResult.Columns["total_revenue"].HeaderText = "Выручка, руб";

                        AuthHelper.LogAction("Просмотр финансового отчёта", "analytics");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка: {ex.Message}");
            }
        }

        private void BtnExport_Click(object sender, EventArgs e)
        {
            if (!AuthHelper.CheckPermission("manager")) return;

            try
            {
                using (var conn = DBConnection.GetConnection())
                {
                    DateTime fromDate = dtpFrom.Value.Date;
                    DateTime toDate = dtpTo.Value.Date;

                    string sql = @"
                        SELECT 
                            t.name as ""Тариф"",
                            t.monthly_price as ""Цена, руб"",
                            COUNT(c.contract_id) as ""Количество подключений""
                        FROM sizov_ev.tariffs t
                        LEFT JOIN sizov_ev.contracts c ON t.tariff_id = c.tariff_id 
                            AND c.sign_date BETWEEN @date_from AND @date_to
                        GROUP BY t.tariff_id, t.name, t.monthly_price
                        ORDER BY COUNT(c.contract_id) DESC";

                    using (var cmd = new NpgsqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@date_from", fromDate);
                        cmd.Parameters.AddWithValue("@date_to", toDate);

                        DataTable dt = new DataTable();
                        using (var da = new NpgsqlDataAdapter(cmd))
                        {
                            da.Fill(dt);
                        }
                        AuthHelper.LogAction("Экспорт аналитики в CSV", "analytics");
                        CsvExporter.ExportToCsv(dt, $"Аналитика_тарифов_{DateTime.Now:yyyyMMdd_HHmmss}.csv");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка экспорта: {ex.Message}");
            }
        }

        private void BtnExportRevenue_Click(object sender, EventArgs e)
        {
            if (!AuthHelper.CheckPermission("manager")) return;

            try
            {
                using (var conn = DBConnection.GetConnection())
                {
                    string sql = @"
                        SELECT 
                            t.name as ""Тариф"",
                            t.monthly_price as ""Цена, руб"",
                            COUNT(DISTINCT c.contract_id) as ""Кол-во договоров"",
                            COALESCE(SUM(p.amount), 0) as ""Выручка, руб""
                        FROM sizov_ev.tariffs t
                        LEFT JOIN sizov_ev.contracts c ON t.tariff_id = c.tariff_id
                        LEFT JOIN sizov_ev.payments p ON c.contract_id = p.contract_id
                        GROUP BY t.tariff_id, t.name, t.monthly_price
                        ORDER BY SUM(p.amount) DESC";

                    using (var cmd = new NpgsqlCommand(sql, conn))
                    {
                        DataTable dt = new DataTable();
                        using (var da = new NpgsqlDataAdapter(cmd))
                        {
                            da.Fill(dt);
                        }

                        decimal totalRevenue = 0;
                        foreach (DataRow row in dt.Rows)
                        {
                            if (row["Выручка, руб"] != DBNull.Value)
                                totalRevenue += Convert.ToDecimal(row["Выручка, руб"]);
                        }

                        AuthHelper.LogAction("Экспорт финансового отчёта", "analytics");
                        CsvExporter.ExportToCsv(dt, $"Финансовый_отчёт_{DateTime.Now:yyyyMMdd_HHmmss}.csv");
                        MessageBox.Show($"Общая выручка: {totalRevenue:C2}", "Итого",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка: {ex.Message}");
            }
        }
    }
}