DeanOfficeApp/AssignmentForm.cs

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

namespace DeanOfficeApp
{
    public partial class AssignmentForm : 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 _assignmentsTable;

        public AssignmentForm()
        {
            InitializeComponent();
            this.Load += AssignmentForm_Load;
        }

        private async void AssignmentForm_Load(object sender, EventArgs e)
        {
            await LoadAllDataAsync();
        }

        private async Task LoadAllDataAsync()
        {
            try
            {
                Cursor = Cursors.WaitCursor;
                await Task.WhenAll(
                    LoadGroupsAsync(),
                    LoadDisciplinesAsync(),
                    LoadTeachersAsync(),
                    LoadAssignmentsAsync()
                );
            }
            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 LoadTeachersAsync()
        {
            try
            {
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    string sql = "SELECT teacher_id, full_name FROM Teacher ORDER BY full_name";
                    using (var cmd = new NpgsqlCommand(sql, conn))
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        var dt = new DataTable();
                        dt.Load(reader);
                        cmbTeacher.DisplayMember = "full_name";
                        cmbTeacher.ValueMember = "teacher_id";
                        cmbTeacher.DataSource = dt;
                        cmbTeacher.SelectedIndex = -1;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка загрузки преподавателей: {ex.Message}");
            }
        }

        private async Task LoadAssignmentsAsync()
        {
            string sql = @"
                SELECT a.assignment_id, g.name AS group_name, d.name AS discipline_name, 
                       t.full_name AS teacher_name, a.semester
                FROM Assignment a
                JOIN ""Group"" g ON a.group_id = g.group_id
                JOIN Discipline d ON a.discipline_id = d.discipline_id
                JOIN Teacher t ON a.teacher_id = t.teacher_id
                ORDER BY g.name, a.semester, d.name";

            try
            {
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    using (var cmd = new NpgsqlCommand(sql, conn))
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        _assignmentsTable = new DataTable();
                        _assignmentsTable.Load(reader);
                        ApplyFilter();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка загрузки назначений: {ex.Message}");
            }
        }

        private void ApplyFilter()
        {
            if (_assignmentsTable == null) return;

            string filterText = txtSearch.Text.Trim().ToLower();
            if (string.IsNullOrEmpty(filterText))
            {
                dgvAssignments.DataSource = _assignmentsTable;
            }
            else
            {
                var filteredRows = _assignmentsTable.AsEnumerable()
                    .Where(row => row["group_name"].ToString().ToLower().Contains(filterText) ||
                                  row["discipline_name"].ToString().ToLower().Contains(filterText) ||
                                  row["teacher_name"].ToString().ToLower().Contains(filterText) ||
                                  row["semester"].ToString().Contains(filterText))
                    .CopyToDataTable();
                dgvAssignments.DataSource = filteredRows;
            }

            if (dgvAssignments.Columns.Contains("assignment_id"))
                dgvAssignments.Columns["assignment_id"].Visible = false;
        }

        private async void btnAdd_Click(object sender, EventArgs e)
        {
            if (!ValidateInput()) return;

            string sql = @"
                INSERT INTO Assignment (group_id, discipline_id, teacher_id, semester)
                VALUES (@g, @d, @t, @s)";

            try
            {
                Cursor = Cursors.WaitCursor;
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    using (var cmd = new NpgsqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@g", (int)cmbGroup.SelectedValue);
                        cmd.Parameters.AddWithValue("@d", (int)cmbDiscipline.SelectedValue);
                        cmd.Parameters.AddWithValue("@t", (int)cmbTeacher.SelectedValue);
                        cmd.Parameters.AddWithValue("@s", (int)cmbSemester.SelectedItem);
                        await cmd.ExecuteNonQueryAsync();
                    }
                }
                await LoadAssignmentsAsync();
                ClearFields();
                MessageBox.Show("Назначение добавлено.");
            }
            catch (PostgresException ex) when (ex.SqlState == "23505")
            {
                MessageBox.Show("Такое назначение уже существует (группа + дисциплина + семестр).");
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка: {ex.Message}");
            }
            finally { Cursor = Cursors.Default; }
        }

        private async void btnUpdate_Click(object sender, EventArgs e)
        {
            if (dgvAssignments.CurrentRow == null)
            {
                MessageBox.Show("Выберите назначение для редактирования.");
                return;
            }
            if (!ValidateInput()) return;

            int assignmentId = (int)dgvAssignments.CurrentRow.Cells["assignment_id"].Value;

            if (await HasGradesAsync(assignmentId))
            {
                MessageBox.Show("Нельзя изменить назначение, по которому уже есть оценки.", "Ограничение", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            string sql = @"
                UPDATE Assignment 
                SET group_id = @g, discipline_id = @d, teacher_id = @t, semester = @s
                WHERE assignment_id = @id";

            try
            {
                Cursor = Cursors.WaitCursor;
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    using (var cmd = new NpgsqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@g", (int)cmbGroup.SelectedValue);
                        cmd.Parameters.AddWithValue("@d", (int)cmbDiscipline.SelectedValue);
                        cmd.Parameters.AddWithValue("@t", (int)cmbTeacher.SelectedValue);
                        cmd.Parameters.AddWithValue("@s", (int)cmbSemester.SelectedItem);
                        cmd.Parameters.AddWithValue("@id", assignmentId);
                        await cmd.ExecuteNonQueryAsync();
                    }
                }
                await LoadAssignmentsAsync();
                ClearFields();
                MessageBox.Show("Назначение обновлено.");
            }
            catch (PostgresException ex) when (ex.SqlState == "23505")
            {
                MessageBox.Show("Конфликт: такое назначение уже существует.");
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка: {ex.Message}");
            }
            finally { Cursor = Cursors.Default; }
        }

        private async void btnDelete_Click(object sender, EventArgs e)
        {
            if (dgvAssignments.CurrentRow == null)
            {
                MessageBox.Show("Выберите назначение для удаления.");
                return;
            }

            int id = (int)dgvAssignments.CurrentRow.Cells["assignment_id"].Value;

            if (await HasGradesAsync(id))
            {
                MessageBox.Show("Нельзя удалить назначение, по которому уже есть оценки.", "Ограничение", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            if (MessageBox.Show("Удалить назначение?", "Подтверждение", MessageBoxButtons.YesNo) != DialogResult.Yes)
                return;

            string sql = "DELETE FROM Assignment WHERE assignment_id = @id";
            try
            {
                Cursor = Cursors.WaitCursor;
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    using (var cmd = new NpgsqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@id", id);
                        await cmd.ExecuteNonQueryAsync();
                    }
                }
                await LoadAssignmentsAsync();
                ClearFields();
                MessageBox.Show("Назначение удалено.");
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Ошибка: {ex.Message}");
            }
            finally { Cursor = Cursors.Default; }
        }

        private async Task<bool> HasGradesAsync(int assignmentId)
        {
            string sql = @"
                SELECT COUNT(*) FROM Progress p
                JOIN Assignment a ON p.discipline_id = a.discipline_id
                WHERE a.assignment_id = @id";
            try
            {
                using (var conn = new NpgsqlConnection(_connString))
                {
                    await conn.OpenAsync();
                    using (var cmd = new NpgsqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@id", assignmentId);
                        long count = (long)await cmd.ExecuteScalarAsync();
                        return count > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
        }

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

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

        private void dgvAssignments_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvAssignments.CurrentRow == null) return;

            string groupName = dgvAssignments.CurrentRow.Cells["group_name"].Value?.ToString();
            string disciplineName = dgvAssignments.CurrentRow.Cells["discipline_name"].Value?.ToString();
            string teacherName = dgvAssignments.CurrentRow.Cells["teacher_name"].Value?.ToString();
            int semester = Convert.ToInt32(dgvAssignments.CurrentRow.Cells["semester"].Value);

            SelectComboBoxItemByText(cmbGroup, groupName);
            SelectComboBoxItemByText(cmbDiscipline, disciplineName);
            SelectComboBoxItemByText(cmbTeacher, teacherName);
            cmbSemester.SelectedItem = semester.ToString();
        }

        private void SelectComboBoxItemByText(Guna2ComboBox comboBox, string text)
        {
            if (string.IsNullOrEmpty(text) || comboBox.DataSource == null) return;
            foreach (DataRowView row in comboBox.Items)
            {
                string displayText = row[comboBox.DisplayMember].ToString();
                if (displayText == text)
                {
                    comboBox.SelectedItem = row;
                    break;
                }
            }
        }

        private bool ValidateInput()
        {
            if (cmbGroup.SelectedValue == null || cmbDiscipline.SelectedValue == null ||
                cmbTeacher.SelectedValue == null || cmbSemester.SelectedItem == null)
            {
                MessageBox.Show("Заполните все поля (группа, дисциплина, преподаватель, семестр).");
                return false;
            }
            return true;
        }

        private void ClearFields()
        {
            cmbGroup.SelectedIndex = -1;
            cmbDiscipline.SelectedIndex = -1;
            cmbTeacher.SelectedIndex = -1;
            cmbSemester.SelectedIndex = 0;
            txtSearch.Clear();
        }
    }
}