DeanOfficeApp/GradeForm.cs

using Guna.UI2.WinForms;
using Npgsql;
using System;
using System.Data;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DeanOfficeApp
{
    public partial class GradeForm : Form
    {
        private readonly string _connString = "Host=245e1-rw.db.pub.dbaas.postgrespro.ru;Port=5432;Database=dbpractice;Username=mardasov_mv;Password=A4%2x$$2Ynr";
        private DataTable _studentsTable;

        public GradeForm()
        {
            InitializeComponent();
            this.Load += async (s, e) => await LoadAllDataAsync();
        }

        private async Task LoadAllDataAsync()
        {
            try
            {
                Cursor = Cursors.WaitCursor;
                await Task.WhenAll(LoadGroupsAsync(), LoadDisciplinesAsync());
                cmbSemester.SelectedIndex = 0;
            }
            finally
            {
                Cursor = Cursors.Default;
            }
        }

        private async Task LoadGroupsAsync()
        {
            try
            {
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    string sql = "SELECT group_id, name FROM \"Group\" ORDER BY name";
                    using (var cmd = new NpgsqlCommand(sql, conn))
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        var dt = new DataTable();
                        dt.Load(reader);
                        cmbGroup.DisplayMember = "name";
                        cmbGroup.ValueMember = "group_id";
                        cmbGroup.DataSource = dt;
                        cmbGroup.SelectedIndex = -1;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка загрузки групп: {ex.Message}");
            }
        }

        private async Task LoadDisciplinesAsync()
        {
            try
            {
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    string sql = "SELECT discipline_id, name FROM Discipline ORDER BY name";
                    using (var cmd = new NpgsqlCommand(sql, conn))
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        var dt = new DataTable();
                        dt.Load(reader);
                        cmbDiscipline.DisplayMember = "name";
                        cmbDiscipline.ValueMember = "discipline_id";
                        cmbDiscipline.DataSource = dt;
                        cmbDiscipline.SelectedIndex = -1;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка загрузки дисциплин: {ex.Message}");
            }
        }

        private async Task LoadStudentsAndGradesAsync()
        {
            if (cmbGroup.SelectedValue == null || cmbDiscipline.SelectedValue == null)
                return;

            int groupId = (int)cmbGroup.SelectedValue;
            int disciplineId = (int)cmbDiscipline.SelectedValue;

            string sql = @"
                SELECT 
                    s.record_book,
                    s.full_name,
                    COALESCE(p.grade, 0) AS grade,
                    p.exam_date
                FROM Student s
                LEFT JOIN Progress p ON s.record_book = p.record_book 
                    AND p.discipline_id = @discId
                WHERE s.group_id = @gid
                ORDER BY s.full_name";

            try
            {
                Cursor = Cursors.WaitCursor;
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    using (var cmd = new NpgsqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@discId", disciplineId);
                        cmd.Parameters.AddWithValue("@gid", groupId);
                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            var dt = new DataTable();
                            dt.Load(reader);
                            _studentsTable = dt;
                            ApplyFilter(); // применяем фильтр поиска
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка загрузки успеваемости: {ex.Message}");
            }
            finally
            {
                Cursor = Cursors.Default;
            }
        }

        // Фильтрация таблицы студентов по тексту поиска
        private void ApplyFilter()
        {
            if (_studentsTable == null) return;

            string filterText = txtSearchStudent.Text.Trim().ToLower();
            if (string.IsNullOrEmpty(filterText))
            {
                dgvStudents.DataSource = _studentsTable;
            }
            else
            {
                var filteredRows = _studentsTable.AsEnumerable()
                    .Where(row => row["full_name"].ToString().ToLower().Contains(filterText))
                    .CopyToDataTable();
                dgvStudents.DataSource = filteredRows;
            }

            if (dgvStudents.Columns.Contains("record_book"))
                dgvStudents.Columns["record_book"].Visible = false;
            dgvStudents.Columns["grade"].HeaderText = "Оценка";
            dgvStudents.Columns["exam_date"].HeaderText = "Дата сдачи";
            dgvStudents.Columns["exam_date"].DefaultCellStyle.Format = "dd.MM.yyyy";
        }

        private async void btnSave_Click(object sender, EventArgs e)
        {
            if (dgvStudents.CurrentRow == null) return;

            // Проверка наличия назначения (Assignment) для выбранной группы, дисциплины и семестра
            if (!await IsAssignmentExistsAsync())
            {
                MessageBox.Show("Данная дисциплина не назначена выбранной группе в указанном семестре.\nНевозможно выставить оценку.",
                    "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            int recordBook = (int)dgvStudents.CurrentRow.Cells["record_book"].Value;
            int disciplineId = (int)cmbDiscipline.SelectedValue;
            int newGrade = (int)nudGrade.Value;
            DateTime examDate = dtpExamDate.Value;

            string checkSql = "SELECT progress_id FROM Progress WHERE record_book = @rb AND discipline_id = @did";
            try
            {
                Cursor = Cursors.WaitCursor;
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    int? existingId = null;
                    using (var cmd = new NpgsqlCommand(checkSql, conn))
                    {
                        cmd.Parameters.AddWithValue("@rb", recordBook);
                        cmd.Parameters.AddWithValue("@did", disciplineId);
                        var result = await cmd.ExecuteScalarAsync();
                        if (result != null && result != DBNull.Value)
                            existingId = Convert.ToInt32(result);
                    }

                    if (existingId.HasValue)
                    {
                        string updateSql = "UPDATE Progress SET grade = @g, exam_date = @ed WHERE progress_id = @pid";
                        using (var cmd = new NpgsqlCommand(updateSql, conn))
                        {
                            cmd.Parameters.AddWithValue("@g", newGrade);
                            cmd.Parameters.AddWithValue("@ed", examDate);
                            cmd.Parameters.AddWithValue("@pid", existingId.Value);
                            await cmd.ExecuteNonQueryAsync();
                        }
                    }
                    else
                    {
                        string insertSql = "INSERT INTO Progress (record_book, discipline_id, grade, exam_date) VALUES (@rb, @did, @g, @ed)";
                        using (var cmd = new NpgsqlCommand(insertSql, conn))
                        {
                            cmd.Parameters.AddWithValue("@rb", recordBook);
                            cmd.Parameters.AddWithValue("@did", disciplineId);
                            cmd.Parameters.AddWithValue("@g", newGrade);
                            cmd.Parameters.AddWithValue("@ed", examDate);
                            await cmd.ExecuteNonQueryAsync();
                        }
                    }
                }
                await LoadStudentsAndGradesAsync();
                MessageBox.Show("Оценка сохранена.");
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка: {ex.Message}");
            }
            finally
            {
                Cursor = Cursors.Default;
            }
        }

        // Проверка существования назначения (Assignment) для группы, дисциплины и семестра
        private async Task<bool> IsAssignmentExistsAsync()
        {
            if (cmbGroup.SelectedValue == null || cmbDiscipline.SelectedValue == null || cmbSemester.SelectedItem == null)
                return false;

            int groupId = (int)cmbGroup.SelectedValue;
            int disciplineId = (int)cmbDiscipline.SelectedValue;
            int semester = (int)cmbSemester.SelectedItem;

            string sql = "SELECT COUNT(*) FROM Assignment WHERE group_id = @gid AND discipline_id = @did AND semester = @sem";
            try
            {
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    using (var cmd = new NpgsqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@gid", groupId);
                        cmd.Parameters.AddWithValue("@did", disciplineId);
                        cmd.Parameters.AddWithValue("@sem", semester);
                        long count = (long)await cmd.ExecuteScalarAsync();
                        return count > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
        }

        private async void cmbGroup_SelectedIndexChanged(object sender, EventArgs e)
        {
            await LoadStudentsAndGradesAsync();
        }

        private async void cmbDiscipline_SelectedIndexChanged(object sender, EventArgs e)
        {
            await LoadStudentsAndGradesAsync();
        }

        private async void cmbSemester_SelectedIndexChanged(object sender, EventArgs e)
        {
            await LoadStudentsAndGradesAsync();
        }

        private async void btnRefresh_Click(object sender, EventArgs e)
        {
            await LoadStudentsAndGradesAsync();
        }

        private void txtSearchStudent_TextChanged(object sender, EventArgs e)
        {
            ApplyFilter();
        }

        private void dgvStudents_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvStudents.CurrentRow != null && dgvStudents.CurrentRow.Cells["grade"].Value != DBNull.Value)
            {
                int grade = Convert.ToInt32(dgvStudents.CurrentRow.Cells["grade"].Value);
                nudGrade.Value = (grade >= 2 && grade <= 5) ? grade : 2;
                if (dgvStudents.CurrentRow.Cells["exam_date"].Value != DBNull.Value)
                    dtpExamDate.Value = Convert.ToDateTime(dgvStudents.CurrentRow.Cells["exam_date"].Value);
            }
            else
            {
                nudGrade.Value = 2;
                dtpExamDate.Value = DateTime.Now;
            }
        }
    }
}