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

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

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import app.db.ConnectionManager;
import app.model.DashboardStats;

public class DashboardService {
    private final ConnectionManager connectionManager;

    public DashboardService(ConnectionManager connectionManager) {
        this.connectionManager = connectionManager;
    }

    public DashboardStats loadTeacherStats(int teacherId) {
        DashboardStats stats = new DashboardStats();
        try (Connection connection = connectionManager.getConnection()) {
            stats.setFirst(count(connection, "SELECT COUNT(*) FROM courses WHERE teacher_id = ?", teacherId));
            stats.setSecond(count(connection, "SELECT COUNT(*) FROM assignments a JOIN courses c ON c.course_id = a.course_id WHERE c.teacher_id = ?", teacherId));
            stats.setThird(count(connection, "SELECT COUNT(*) FROM submissions s JOIN assignments a ON a.assignment_id = s.assignment_id JOIN courses c ON c.course_id = a.course_id WHERE c.teacher_id = ? AND s.status = 'submitted'", teacherId));
            stats.setFourth(count(connection, "SELECT COUNT(*) 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 = ?", teacherId));
            stats.setFifth(count(connection, "SELECT COUNT(*) FROM submissions s JOIN assignments a ON a.assignment_id = s.assignment_id JOIN courses c ON c.course_id = a.course_id WHERE c.teacher_id = ? AND s.status = 'checked'", teacherId));
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки статистики преподавателя", e);
        }
        return stats;
    }

    public DashboardStats loadStudentStats(int studentId) {
        DashboardStats stats = new DashboardStats();
        try (Connection connection = connectionManager.getConnection()) {
            stats.setFirst(count(connection, "SELECT COUNT(*) FROM enrollments WHERE student_id = ?", studentId));
            stats.setSecond(count(connection, "SELECT COUNT(*) FROM assignments a JOIN enrollments e ON e.course_id = a.course_id WHERE e.student_id = ?", studentId));
            stats.setThird(count(connection, "SELECT COUNT(*) FROM submissions WHERE student_id = ?", studentId));
            stats.setFourth(count(connection, "SELECT COUNT(*) FROM grades g JOIN submissions s ON s.submission_id = g.submission_id WHERE s.student_id = ?", studentId));
            stats.setAverage(avg(connection, "SELECT AVG(g.grade_value) FROM grades g JOIN submissions s ON s.submission_id = g.submission_id WHERE s.student_id = ?", studentId));
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки статистики студента", e);
        }
        return stats;
    }

    public DashboardStats loadAdminStats() {
        DashboardStats stats = new DashboardStats();
        try (Connection connection = connectionManager.getConnection()) {
            stats.setFirst(countRaw(connection, "SELECT COUNT(*) FROM teachers"));
            stats.setSecond(countRaw(connection, "SELECT COUNT(*) FROM students"));
            stats.setThird(countRaw(connection, "SELECT COUNT(*) FROM courses"));
            stats.setFourth(countRaw(connection, "SELECT COUNT(*) FROM users"));
            stats.setFifth(countRaw(connection, "SELECT COUNT(*) FROM submissions"));
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки статистики администратора", e);
        }
        return stats;
    }

    private int count(Connection connection, String sql, int id) throws Exception {
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                return rs.getInt(1);
            }
        }
    }

    private int countRaw(Connection connection, String sql) throws Exception {
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                return rs.getInt(1);
            }
        }
    }

    private double avg(Connection connection, String sql, int id) throws Exception {
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                double value = rs.getDouble(1);
                return rs.wasNull() ? 0.0 : value;
            }
        }
    }
}