src/app/service/LmsService.java - master - evernotenkai/DataBaseXJavaProject

src/app/service/LmsService.java
507 строк26 KB
Makson4ik
Весь проект с src db и прочим делом
12 май 2026, 22:43
12 май 2026, 22:43431f4aa
package app.service;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import app.db.ConnectionManager;
import app.model.AssignmentItem;
import app.model.CourseItem;
import app.model.GradeItem;
import app.model.SubmissionAttachmentData;
import app.model.SubmissionItem;

public class LmsService {
    private final ConnectionManager connectionManager;
    private boolean submissionAttachmentsSupported;

    public LmsService(ConnectionManager connectionManager) {
        this.connectionManager = connectionManager;
        ensureSubmissionAttachmentColumns();
    }

    public List<CourseItem> findTeacherCourses(int teacherId) {
        String sql = "SELECT c.course_id, c.course_name, COALESCE(c.description, ''), t.full_name, c.teacher_id " +
                "FROM courses c JOIN teachers t ON t.teacher_id = c.teacher_id WHERE c.teacher_id = ? ORDER BY c.course_name";
        List<CourseItem> list = new ArrayList<CourseItem>();
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, teacherId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new CourseItem(rs.getInt(1), rs.getString(2), safe(rs.getString(3)), rs.getString(4), rs.getInt(5)));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки курсов преподавателя", e);
        }
        return list;
    }

    public List<CourseItem> findStudentCourses(int studentId) {
        String sql = "SELECT c.course_id, c.course_name, COALESCE(c.description, ''), t.full_name, c.teacher_id " +
                "FROM enrollments e JOIN courses c ON c.course_id = e.course_id " +
                "JOIN teachers t ON t.teacher_id = c.teacher_id WHERE e.student_id = ? ORDER BY c.course_name";
        List<CourseItem> list = new ArrayList<CourseItem>();
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, studentId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new CourseItem(rs.getInt(1), rs.getString(2), safe(rs.getString(3)), rs.getString(4), rs.getInt(5)));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки курсов студента", e);
        }
        return list;
    }

    public List<AssignmentItem> findTeacherAssignments(int teacherId) {
        String sql = "SELECT a.assignment_id, a.course_id, c.course_name, a.title, a.description, a.due_date, a.created_at " +
                "FROM assignments a JOIN courses c ON c.course_id = a.course_id WHERE c.teacher_id = ? ORDER BY a.due_date";
        return queryAssignments(sql, teacherId);
    }

    public List<AssignmentItem> findAssignmentsForCourse(int courseId) {
        String sql = "SELECT a.assignment_id, a.course_id, c.course_name, a.title, a.description, a.due_date, a.created_at " +
                "FROM assignments a JOIN courses c ON c.course_id = a.course_id WHERE a.course_id = ? ORDER BY a.due_date";
        return queryAssignments(sql, courseId);
    }

    public List<AssignmentItem> findStudentAssignments(int studentId) {
        String sql = "SELECT a.assignment_id, a.course_id, c.course_name, a.title, a.description, a.due_date, a.created_at " +
                "FROM assignments a JOIN courses c ON c.course_id = a.course_id " +
                "JOIN enrollments e ON e.course_id = c.course_id WHERE e.student_id = ? ORDER BY a.due_date";
        return queryAssignments(sql, studentId);
    }

    private List<AssignmentItem> queryAssignments(String sql, int id) {
        List<AssignmentItem> list = new ArrayList<AssignmentItem>();
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new AssignmentItem(
                            rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4),
                            safe(rs.getString(5)), String.valueOf(rs.getDate(6)), String.valueOf(rs.getDate(7))));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки заданий", e);
        }
        return list;
    }

    public void createAssignment(int courseId, String title, String description, String dueDate) {
        createAssignment(courseId, title, description, dueDate, null);
    }

    public void createAssignment(int courseId, String title, String description, String dueDate, String createdAt) {
        String sql = "INSERT INTO assignments (course_id, title, description, due_date, created_at) VALUES (?, ?, ?, ?, ?)";
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, courseId);
            ps.setString(2, title);
            ps.setString(3, description);
            ps.setDate(4, Date.valueOf(dueDate));
            ps.setDate(5, createdAt == null || createdAt.trim().isEmpty() ? new Date(System.currentTimeMillis()) : Date.valueOf(createdAt));
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка создания задания", e);
        }
    }

    public void updateAssignment(int assignmentId, int courseId, String title, String description, String dueDate) {
        String sql = "UPDATE assignments SET course_id = ?, title = ?, description = ?, due_date = ? WHERE assignment_id = ?";
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, courseId);
            ps.setString(2, title);
            ps.setString(3, description);
            ps.setDate(4, Date.valueOf(dueDate));
            ps.setInt(5, assignmentId);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка обновления задания", e);
        }
    }

    public void updateAssignment(int assignmentId, String title, String description, String dueDate, String createdAt) {
        String sql = "UPDATE assignments SET title = ?, description = ?, due_date = ?, created_at = ? WHERE assignment_id = ?";
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, title);
            ps.setString(2, description);
            ps.setDate(3, Date.valueOf(dueDate));
            ps.setDate(4, createdAt == null || createdAt.trim().isEmpty() ? new Date(System.currentTimeMillis()) : Date.valueOf(createdAt));
            ps.setInt(5, assignmentId);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка обновления задания", e);
        }
    }

    public void deleteAssignment(int assignmentId) {
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement("DELETE FROM assignments WHERE assignment_id = ?")) {
            ps.setInt(1, assignmentId);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка удаления задания", e);
        }
    }

    public List<SubmissionItem> findTeacherSubmissions(int teacherId) {
        String sql = adjustSubmissionSelectSql("SELECT c.course_id, c.course_name, a.assignment_id, a.title, s.submission_id, st.full_name, s.submission_date, s.content_note, s.status, g.grade_value, g.teacher_comment, s.attachment_name, s.attachment_mime " +
                "FROM submissions s JOIN assignments a ON a.assignment_id = s.assignment_id " +
                "JOIN courses c ON c.course_id = a.course_id " +
                "JOIN students st ON st.student_id = s.student_id " +
                "LEFT JOIN grades g ON g.submission_id = s.submission_id " +
                "WHERE c.teacher_id = ? ORDER BY s.submission_date DESC, s.submission_id DESC");
        return querySubmissions(sql, teacherId);
    }

    public List<SubmissionItem> findStudentSubmissions(int studentId) {
        String sql = adjustSubmissionSelectSql("SELECT c.course_id, c.course_name, a.assignment_id, a.title, s.submission_id, st.full_name, s.submission_date, s.content_note, s.status, g.grade_value, g.teacher_comment, s.attachment_name, s.attachment_mime " +
                "FROM submissions s JOIN assignments a ON a.assignment_id = s.assignment_id " +
                "JOIN courses c ON c.course_id = a.course_id " +
                "JOIN students st ON st.student_id = s.student_id " +
                "LEFT JOIN grades g ON g.submission_id = s.submission_id " +
                "WHERE s.student_id = ? ORDER BY s.submission_date DESC, s.submission_id DESC");
        return querySubmissions(sql, studentId);
    }

    public SubmissionItem findStudentSubmissionByAssignment(int studentId, int assignmentId) {
        String sql = adjustSubmissionSelectSql("SELECT c.course_id, c.course_name, a.assignment_id, a.title, s.submission_id, st.full_name, s.submission_date, s.content_note, s.status, g.grade_value, g.teacher_comment, s.attachment_name, s.attachment_mime " +
                "FROM submissions s JOIN assignments a ON a.assignment_id = s.assignment_id " +
                "JOIN courses c ON c.course_id = a.course_id " +
                "JOIN students st ON st.student_id = s.student_id " +
                "LEFT JOIN grades g ON g.submission_id = s.submission_id " +
                "WHERE s.student_id = ? AND s.assignment_id = ?");
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, studentId);
            ps.setInt(2, assignmentId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    Integer gradeValue = rs.getObject(10) == null ? null : ((Number) rs.getObject(10)).intValue();
                    return new SubmissionItem(rs.getInt(5), rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(6),
                            String.valueOf(rs.getDate(7)), safe(rs.getString(8)), rs.getString(9), gradeValue, safe(rs.getString(11)), rs.getString(12), null);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка поиска отправки", e);
        }
        return null;
    }


    public SubmissionItem findSubmissionById(int submissionId) {
        String sql = adjustSubmissionSelectSql("SELECT c.course_id, c.course_name, a.assignment_id, a.title, s.submission_id, st.full_name, s.submission_date, s.content_note, s.status, g.grade_value, g.teacher_comment, s.attachment_name, s.attachment_mime " +
                "FROM submissions s JOIN assignments a ON a.assignment_id = s.assignment_id " +
                "JOIN courses c ON c.course_id = a.course_id " +
                "JOIN students st ON st.student_id = s.student_id " +
                "LEFT JOIN grades g ON g.submission_id = s.submission_id " +
                "WHERE s.submission_id = ?");
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, submissionId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    Integer gradeValue = rs.getObject(10) == null ? null : ((Number) rs.getObject(10)).intValue();
                    return new SubmissionItem(rs.getInt(5), rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(6),
                            String.valueOf(rs.getDate(7)), safe(rs.getString(8)), rs.getString(9), gradeValue, safe(rs.getString(11)), rs.getString(12), null);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки работы", e);
        }
        return null;
    }

    private List<SubmissionItem> querySubmissions(String sql, int id) {
        List<SubmissionItem> list = new ArrayList<SubmissionItem>();
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Integer gradeValue = rs.getObject(10) == null ? null : ((Number) rs.getObject(10)).intValue();
                    list.add(new SubmissionItem(rs.getInt(5), rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(6),
                            String.valueOf(rs.getDate(7)), safe(rs.getString(8)), rs.getString(9), gradeValue, safe(rs.getString(11)), rs.getString(12), null));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки работ", e);
        }
        return list;
    }


    public void saveOrUpdateSubmission(int studentId, int assignmentId, String text) {
        saveOrUpdateSubmission(studentId, assignmentId, text, null, null, null);
    }

    public void saveOrUpdateSubmission(int studentId, int assignmentId, String text, String attachmentName, String attachmentMime, byte[] attachmentData) {
        try (Connection connection = connectionManager.getConnection()) {
            connection.setAutoCommit(false);
            Integer existingSubmission = existingSubmissionId(connection, studentId, assignmentId);
            if (existingSubmission == null) {
                if (submissionAttachmentsSupported) {
                    try (PreparedStatement ps = connection.prepareStatement(
                            "INSERT INTO submissions (assignment_id, student_id, submission_date, content_note, status, attachment_name, attachment_mime, attachment_data) VALUES (?, ?, ?, ?, ?, ?, ?, ?)")) {
                        ps.setInt(1, assignmentId);
                        ps.setInt(2, studentId);
                        ps.setDate(3, new Date(System.currentTimeMillis()));
                        ps.setString(4, text);
                        ps.setString(5, "submitted");
                        ps.setString(6, attachmentName);
                        ps.setString(7, attachmentMime);
                        if (attachmentData == null || attachmentData.length == 0) {
                            ps.setNull(8, java.sql.Types.BLOB);
                        } else {
                            ps.setBinaryStream(8, new ByteArrayInputStream(attachmentData), attachmentData.length);
                        }
                        ps.executeUpdate();
                    }
                } else {
                    try (PreparedStatement ps = connection.prepareStatement(
                            "INSERT INTO submissions (assignment_id, student_id, submission_date, content_note, status) VALUES (?, ?, ?, ?, ?)")) {
                        ps.setInt(1, assignmentId);
                        ps.setInt(2, studentId);
                        ps.setDate(3, new Date(System.currentTimeMillis()));
                        ps.setString(4, text);
                        ps.setString(5, "submitted");
                        ps.executeUpdate();
                    }
                }
            } else {
                if (submissionAttachmentsSupported && attachmentData != null) {
                    try (PreparedStatement ps = connection.prepareStatement(
                            "UPDATE submissions SET submission_date = ?, content_note = ?, status = 'submitted', attachment_name = ?, attachment_mime = ?, attachment_data = ? WHERE submission_id = ?")) {
                        ps.setDate(1, new Date(System.currentTimeMillis()));
                        ps.setString(2, text);
                        ps.setString(3, attachmentName);
                        ps.setString(4, attachmentMime);
                        ps.setBinaryStream(5, new ByteArrayInputStream(attachmentData), attachmentData.length);
                        ps.setInt(6, existingSubmission);
                        ps.executeUpdate();
                    }
                } else {
                    try (PreparedStatement ps = connection.prepareStatement(
                            "UPDATE submissions SET submission_date = ?, content_note = ?, status = 'submitted' WHERE submission_id = ?")) {
                        ps.setDate(1, new Date(System.currentTimeMillis()));
                        ps.setString(2, text);
                        ps.setInt(3, existingSubmission);
                        ps.executeUpdate();
                    }
                }
                try (PreparedStatement ps = connection.prepareStatement("DELETE FROM grades WHERE submission_id = ?")) {
                    ps.setInt(1, existingSubmission);
                    ps.executeUpdate();
                }
            }
            connection.commit();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка сохранения отправки", e);
        }
    }

    public void submitWork(int studentId, int assignmentId, String text) {
        saveOrUpdateSubmission(studentId, assignmentId, text);
    }

    public void submitWork(int studentId, int assignmentId, String text, String attachmentName, String attachmentMime, byte[] attachmentData) {
        saveOrUpdateSubmission(studentId, assignmentId, text, attachmentName, attachmentMime, attachmentData);
    }

    public void upsertGrade(int submissionId, int gradeValue, String comment) {
        try (Connection connection = connectionManager.getConnection()) {
            Integer gradeId = existingGradeId(connection, submissionId);
            if (gradeId == null) {
                try (PreparedStatement ps = connection.prepareStatement(
                        "INSERT INTO grades (submission_id, grade_value, teacher_comment, graded_date) VALUES (?, ?, ?, ?)")) {
                    ps.setInt(1, submissionId);
                    ps.setInt(2, gradeValue);
                    ps.setString(3, comment);
                    ps.setDate(4, new Date(System.currentTimeMillis()));
                    ps.executeUpdate();
                }
            } else {
                try (PreparedStatement ps = connection.prepareStatement(
                        "UPDATE grades SET grade_value = ?, teacher_comment = ?, graded_date = ? WHERE submission_id = ?")) {
                    ps.setInt(1, gradeValue);
                    ps.setString(2, comment);
                    ps.setDate(3, new Date(System.currentTimeMillis()));
                    ps.setInt(4, submissionId);
                    ps.executeUpdate();
                }
            }
            try (PreparedStatement ps = connection.prepareStatement("UPDATE submissions SET status = 'checked' WHERE submission_id = ?")) {
                ps.setInt(1, submissionId);
                ps.executeUpdate();
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка сохранения оценки", e);
        }
    }

    private Integer existingSubmissionId(Connection connection, int studentId, int assignmentId) throws Exception {
        try (PreparedStatement ps = connection.prepareStatement("SELECT submission_id FROM submissions WHERE student_id = ? AND assignment_id = ?")) {
            ps.setInt(1, studentId);
            ps.setInt(2, assignmentId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) return rs.getInt(1);
            }
        }
        return null;
    }

    private Integer existingGradeId(Connection connection, int submissionId) throws Exception {
        try (PreparedStatement ps = connection.prepareStatement("SELECT grade_id FROM grades WHERE submission_id = ?")) {
            ps.setInt(1, submissionId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) return rs.getInt(1);
            }
        }
        return null;
    }


    public SubmissionAttachmentData findSubmissionAttachment(int submissionId) {
        if (!submissionAttachmentsSupported) {
            return null;
        }
        String sql = "SELECT attachment_name, attachment_mime, attachment_data FROM submissions WHERE submission_id = ?";
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, submissionId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    try (InputStream in = rs.getBinaryStream(3)) {
                        if (in == null) {
                            return null;
                        }
                        ByteArrayOutputStream out = new ByteArrayOutputStream();
                        byte[] buffer = new byte[8192];
                        int read;
                        while ((read = in.read(buffer)) != -1) {
                            out.write(buffer, 0, read);
                        }
                        byte[] bytes = out.toByteArray();
                        if (bytes.length == 0) {
                            return null;
                        }
                        return new SubmissionAttachmentData(rs.getString(1), rs.getString(2), bytes);
                    }
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки вложения", e);
        }
        return null;
    }

    public List<GradeItem> findStudentGrades(int studentId) {
        String sql = "SELECT g.grade_id, s.submission_id, c.course_name, a.title, g.graded_date, g.grade_value, g.teacher_comment " +
                "FROM grades g JOIN submissions s ON s.submission_id = g.submission_id " +
                "JOIN assignments a ON a.assignment_id = s.assignment_id " +
                "JOIN courses c ON c.course_id = a.course_id WHERE s.student_id = ? ORDER BY g.graded_date DESC";
        return queryGrades(sql, studentId);
    }

    public List<GradeItem> findTeacherGrades(int teacherId) {
        String sql = "SELECT g.grade_id, s.submission_id, c.course_name, a.title, g.graded_date, g.grade_value, g.teacher_comment " +
                "FROM grades g JOIN submissions s ON s.submission_id = g.submission_id " +
                "JOIN assignments a ON a.assignment_id = s.assignment_id " +
                "JOIN courses c ON c.course_id = a.course_id WHERE c.teacher_id = ? ORDER BY g.graded_date DESC";
        return queryGrades(sql, teacherId);
    }

    private List<GradeItem> queryGrades(String sql, int id) {
        List<GradeItem> list = new ArrayList<GradeItem>();
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new GradeItem(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), String.valueOf(rs.getDate(5)), rs.getInt(6), safe(rs.getString(7))));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки оценок", e);
        }
        return list;
    }

    private String adjustSubmissionSelectSql(String sql) {
        if (submissionAttachmentsSupported) {
            return sql;
        }
        return sql.replace(", s.attachment_name, s.attachment_mime", ", NULL AS attachment_name, NULL AS attachment_mime");
    }

    private boolean hasColumn(Connection connection, String tableName, String columnName) {
        try {
            DatabaseMetaData meta = connection.getMetaData();
            if (metadataHasColumn(meta, tableName, columnName)) {
                return true;
            }
            return metadataHasColumn(meta, tableName.toUpperCase(), columnName.toUpperCase());
        } catch (Exception e) {
            return false;
        }
    }

    private boolean metadataHasColumn(DatabaseMetaData meta, String tableName, String columnName) throws Exception {
        try (ResultSet rs = meta.getColumns(null, null, tableName, columnName)) {
            return rs.next();
        }
    }

    private void ensureSubmissionAttachmentColumns() {
        try (Connection connection = connectionManager.getConnection()) {
            boolean nameExists = hasColumn(connection, "submissions", "attachment_name");
            boolean mimeExists = hasColumn(connection, "submissions", "attachment_mime");
            boolean dataExists = hasColumn(connection, "submissions", "attachment_data");
            if (nameExists && mimeExists && dataExists) {
                submissionAttachmentsSupported = true;
                return;
            }

            String product = connection.getMetaData().getDatabaseProductName().toLowerCase();
            try (Statement st = connection.createStatement()) {
                if (!nameExists) {
                    st.executeUpdate(product.contains("firebird")
                            ? "ALTER TABLE submissions ADD attachment_name VARCHAR(255)"
                            : "ALTER TABLE submissions ADD COLUMN attachment_name VARCHAR(255)");
                }
                if (!mimeExists) {
                    st.executeUpdate(product.contains("firebird")
                            ? "ALTER TABLE submissions ADD attachment_mime VARCHAR(120)"
                            : "ALTER TABLE submissions ADD COLUMN attachment_mime VARCHAR(120)");
                }
                if (!dataExists) {
                    st.executeUpdate(product.contains("firebird")
                            ? "ALTER TABLE submissions ADD attachment_data BLOB SUB_TYPE BINARY"
                            : "ALTER TABLE submissions ADD COLUMN attachment_data BYTEA");
                }
            }
            submissionAttachmentsSupported = true;
        } catch (Exception e) {
            submissionAttachmentsSupported = false;
        }
    }

    private String safe(String value) { return value == null ? "" : value; }
}