Evkachev_TestManipulate_PM04/TestsManipulate/MainPage.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using ClosedXML.Excel;
using Npgsql;

namespace TestsManipulate
{
    public partial class MainPage : Form
    {
        private DataTable _dt = new DataTable(); 
        public MainPage()
        {
            InitializeComponent();
            UpdateInfo();
            LoadResults();
        }

        private void MainPage_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.Exit();
        }

        private void UpdateInfo()
        {
            switch (Program.role_id)
            {
                case 1: label1.Text = "Администратор"; break;
                case 2: label1.Text = "Пользователь";
                        button1.Visible = false;
                        button2.Visible = false;
                        button3.Visible = false; break;
                default: label1.Text = "Пользователь";
                        button1.Visible = false;
                        button2.Visible = false;
                        button3.Visible = false; break;
            }

            LoadResults();
        }

        public void LoadResults()
        {
            string sql;

            if (Program.role_id == 1)
            {
                sql = @"
                    SELECT
                        r.id as ""id"",
                        t.title AS ""Название теста"",
                        tp.name AS ""Тема"",
                        dl.name AS ""Сложность"",
                        u.login AS ""Пользователь"",
                        r.score AS ""Баллы"",
                        r.max_score AS ""Макс. баллов"",
                        r.score_percent AS ""Процент (%)"",
                        TO_CHAR(r.completed_at, 'DD.MM.YYYY HH24:MI') AS ""Дата завершения""
                    FROM results r
                    JOIN tests t ON t.id  = r.test_id
                    JOIN topics tp ON tp.id = t.topic_id
                    JOIN difficulty_levels dl ON dl.id = t.difficulty_level_id
                    JOIN users u  ON u.id  = r.user_id
                    ORDER BY r.completed_at DESC;";
            }
            else
            {
                sql = @"
                    SELECT
                        r.id as ""id"",
                        t.title AS ""Название теста"",
                        tp.name AS ""Тема"",
                        dl.name AS ""Сложность"",
                        r.score AS ""Баллы"",
                        r.max_score AS ""Макс. баллов"",
                        r.score_percent AS ""Процент (%)"",
                        TO_CHAR(r.completed_at,'DD.MM.YYYY HH24:MI') AS ""Дата завершения""
                    FROM results r
                    JOIN tests t  ON t.id  = r.test_id
                    JOIN topics tp ON tp.id = t.topic_id
                    JOIN difficulty_levels dl ON dl.id = t.difficulty_level_id
                    WHERE r.user_id = @userId
                    ORDER BY r.completed_at DESC;";

                menuStrip1.Visible = false;
            }

            using (var conn = new NpgsqlConnection(Program.connString))
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    if (Program.role_id != 1)
                        cmd.Parameters.AddWithValue("userId", Program.user_id);

                    using (var adapter = new NpgsqlDataAdapter(cmd))
                    {
                        _dt = new DataTable();
                        adapter.Fill(_dt);
                        dataGridView1.DataSource = _dt;
                        dataGridView1.Columns["id"].Visible = false;
                        conn.Close();
                    }

                }
                
            }

        }

        private void Delete(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count == 0) return;

            int resultId = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells["id"].Value);
            int[] ids = new int[] { resultId };

            using (var conn = new NpgsqlConnection(Program.connString))
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand("call delete_selected_results(@id, @user_login)", conn))
                {
                    cmd.Parameters.AddWithValue("id", ids);
                    cmd.Parameters.AddWithValue("user_login", Program.login);
                    cmd.ExecuteNonQuery();
                }
            }
            UpdateInfo();
        }

        private void Update(object sender, EventArgs e)
        {
            MessageBox.Show("Можно изменить только количество очков", "Внимание");
            if (dataGridView1.SelectedRows.Count == 0) return;

            int resultId = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells["id"].Value);
            int[] ids = new int[] { resultId };

            using (var conn = new NpgsqlConnection(Program.connString))
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand("call update_selected_results(@id, @new_score, @user_login)", conn))
                {
                    cmd.Parameters.AddWithValue("id", ids);
                    cmd.Parameters.AddWithValue("new_score", dataGridView1.SelectedRows[0].Cells["Баллы"].Value);
                    cmd.Parameters.AddWithValue("user_login", Program.login);
                    cmd.ExecuteNonQuery();
                }
            }
            UpdateInfo();
        }

        private void Add(object sender, EventArgs e)
        {
            AddResult addResult = new AddResult(this);
            addResult.ShowDialog();
        }

        private void UsersTable(object sender, EventArgs e)
        {
            UsersPage usersPage = new UsersPage();
            usersPage.ShowDialog();
        }

        private void AnswerOptionsTable(object sender, EventArgs e)
        {
            AnswerOptionsPage answerOptionsPage = new AnswerOptionsPage();
            answerOptionsPage.ShowDialog();
        }

        private void TestQuestionsTable(object sender, EventArgs e)
        {
            TestQuestionsPage testQuestionsPage = new TestQuestionsPage();
            testQuestionsPage.ShowDialog();
        }

        private void TestsPage(object sender, EventArgs e)
        {
            QuestionsPage questionsPage = new QuestionsPage();
            questionsPage.ShowDialog();
        }

        private void QuestionsTable(object sender, EventArgs e)
        {
            QuestionsPage questionsPage = new QuestionsPage();
            questionsPage.ShowDialog();
        }

        private void TestsTable(object sender, EventArgs e)
        {
            TestsPage testsPage = new TestsPage();
            testsPage.ShowDialog();
        }

        private void DifficultyLevelsTable(object sender, EventArgs e)
        {
            DifficultyLevelsPage difficultyLevelsPage = new DifficultyLevelsPage();
            difficultyLevelsPage.ShowDialog();
        }

        private void TopicsTable(object sender, EventArgs e)
        {
            TopicsPage topicsPage = new TopicsPage();
            topicsPage.ShowDialog();
        }

        private void LogsTable(object sender, EventArgs e)
        {
            LogsPage logsPage = new LogsPage();
            logsPage.ShowDialog();
        }

        private void Search(object sender, EventArgs e)
        {
            string keyword = textBox1.Text.Trim();

            _dt.DefaultView.RowFilter = string.IsNullOrEmpty(keyword)
                ? ""
                : $"[Название теста] LIKE '%{keyword}%'" +
                  $" OR [Тема] LIKE '%{keyword}%'" +
                  $" OR [Сложность] LIKE '%{keyword}%'";
        }

        private void Report(object sender, EventArgs e)
        {
            var dlg = new SaveFileDialog { Filter = "Excel файл|*.xlsx", FileName = "Отчёт.xlsx" };
            if (dlg.ShowDialog() != DialogResult.OK) return;

            var wb = new XLWorkbook();
            var ws = wb.AddWorksheet("Результаты");

            ws.Cell(1, 1).InsertTable(dataGridView1.DataSource as DataTable);
            ws.Columns().AdjustToContents();
            wb.SaveAs(dlg.FileName);

            MessageBox.Show("Отчёт сохранён");
        }
    }
}