5a31f52310
Services: Admin-Lehrer, Backend-Lehrer, Studio v2, Website, Klausur-Service, School-Service, Voice-Service, Geo-Service, BreakPilot Drive, Agent-Core Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
647 lines
21 KiB
Go
647 lines
21 KiB
Go
package services
|
|
|
|
import (
|
|
"context"
|
|
|
|
"github.com/breakpilot/school-service/internal/models"
|
|
"github.com/jackc/pgx/v5/pgxpool"
|
|
)
|
|
|
|
// GradeService handles grade-related operations
|
|
type GradeService struct {
|
|
db *pgxpool.Pool
|
|
}
|
|
|
|
// NewGradeService creates a new GradeService
|
|
func NewGradeService(db *pgxpool.Pool) *GradeService {
|
|
return &GradeService{db: db}
|
|
}
|
|
|
|
// GetGradeOverview returns grade overview for a class
|
|
func (s *GradeService) GetGradeOverview(ctx context.Context, classID string, semester int) ([]models.GradeOverview, error) {
|
|
rows, err := s.db.Query(ctx, `
|
|
SELECT go.id, go.student_id, go.subject_id, go.school_year_id, go.semester,
|
|
go.written_grade_avg, go.written_grade_count, go.oral_grade, go.oral_notes,
|
|
go.final_grade, go.final_grade_locked, go.written_weight, go.oral_weight,
|
|
go.created_at, go.updated_at,
|
|
CONCAT(st.first_name, ' ', st.last_name) as student_name,
|
|
sub.name as subject_name
|
|
FROM grade_overview go
|
|
JOIN students st ON go.student_id = st.id
|
|
JOIN subjects sub ON go.subject_id = sub.id
|
|
WHERE st.class_id = $1 AND go.semester = $2
|
|
ORDER BY st.last_name, st.first_name, sub.name
|
|
`, classID, semester)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
var grades []models.GradeOverview
|
|
for rows.Next() {
|
|
var g models.GradeOverview
|
|
if err := rows.Scan(&g.ID, &g.StudentID, &g.SubjectID, &g.SchoolYearID, &g.Semester, &g.WrittenGradeAvg, &g.WrittenGradeCount, &g.OralGrade, &g.OralNotes, &g.FinalGrade, &g.FinalGradeLocked, &g.WrittenWeight, &g.OralWeight, &g.CreatedAt, &g.UpdatedAt, &g.StudentName, &g.SubjectName); err != nil {
|
|
return nil, err
|
|
}
|
|
grades = append(grades, g)
|
|
}
|
|
return grades, nil
|
|
}
|
|
|
|
// GetStudentGrades returns all grades for a student
|
|
func (s *GradeService) GetStudentGrades(ctx context.Context, studentID string) ([]models.GradeOverview, error) {
|
|
rows, err := s.db.Query(ctx, `
|
|
SELECT go.id, go.student_id, go.subject_id, go.school_year_id, go.semester,
|
|
go.written_grade_avg, go.written_grade_count, go.oral_grade, go.oral_notes,
|
|
go.final_grade, go.final_grade_locked, go.written_weight, go.oral_weight,
|
|
go.created_at, go.updated_at,
|
|
CONCAT(st.first_name, ' ', st.last_name) as student_name,
|
|
sub.name as subject_name
|
|
FROM grade_overview go
|
|
JOIN students st ON go.student_id = st.id
|
|
JOIN subjects sub ON go.subject_id = sub.id
|
|
WHERE go.student_id = $1
|
|
ORDER BY go.school_year_id DESC, go.semester DESC, sub.name
|
|
`, studentID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
var grades []models.GradeOverview
|
|
for rows.Next() {
|
|
var g models.GradeOverview
|
|
if err := rows.Scan(&g.ID, &g.StudentID, &g.SubjectID, &g.SchoolYearID, &g.Semester, &g.WrittenGradeAvg, &g.WrittenGradeCount, &g.OralGrade, &g.OralNotes, &g.FinalGrade, &g.FinalGradeLocked, &g.WrittenWeight, &g.OralWeight, &g.CreatedAt, &g.UpdatedAt, &g.StudentName, &g.SubjectName); err != nil {
|
|
return nil, err
|
|
}
|
|
grades = append(grades, g)
|
|
}
|
|
return grades, nil
|
|
}
|
|
|
|
// UpdateOralGrade updates the oral grade for a student in a subject
|
|
func (s *GradeService) UpdateOralGrade(ctx context.Context, studentID, subjectID string, req *models.UpdateOralGradeRequest) (*models.GradeOverview, error) {
|
|
// First, get or create the grade overview record
|
|
var grade models.GradeOverview
|
|
|
|
// Try to get the current school year and semester
|
|
var schoolYearID string
|
|
err := s.db.QueryRow(ctx, `
|
|
SELECT sy.id FROM school_years sy
|
|
JOIN classes c ON c.school_year_id = sy.id
|
|
JOIN students st ON st.class_id = c.id
|
|
WHERE st.id = $1 AND sy.is_current = true
|
|
LIMIT 1
|
|
`, studentID).Scan(&schoolYearID)
|
|
|
|
if err != nil {
|
|
// No current school year found, cannot update
|
|
return nil, err
|
|
}
|
|
|
|
// Current semester (simplified - could be calculated from date)
|
|
semester := 1
|
|
|
|
err = s.db.QueryRow(ctx, `
|
|
INSERT INTO grade_overview (student_id, subject_id, school_year_id, semester, oral_grade, oral_notes)
|
|
VALUES ($1, $2, $3, $4, $5, $6)
|
|
ON CONFLICT (student_id, subject_id, school_year_id, semester) DO UPDATE SET
|
|
oral_grade = EXCLUDED.oral_grade,
|
|
oral_notes = EXCLUDED.oral_notes,
|
|
updated_at = NOW()
|
|
RETURNING id, student_id, subject_id, school_year_id, semester, written_grade_avg, written_grade_count, oral_grade, oral_notes, final_grade, final_grade_locked, written_weight, oral_weight, created_at, updated_at
|
|
`, studentID, subjectID, schoolYearID, semester, req.OralGrade, req.OralNotes).Scan(
|
|
&grade.ID, &grade.StudentID, &grade.SubjectID, &grade.SchoolYearID, &grade.Semester, &grade.WrittenGradeAvg, &grade.WrittenGradeCount, &grade.OralGrade, &grade.OralNotes, &grade.FinalGrade, &grade.FinalGradeLocked, &grade.WrittenWeight, &grade.OralWeight, &grade.CreatedAt, &grade.UpdatedAt,
|
|
)
|
|
return &grade, err
|
|
}
|
|
|
|
// CalculateFinalGrades calculates final grades for all students in a class
|
|
func (s *GradeService) CalculateFinalGrades(ctx context.Context, classID string, semester int) error {
|
|
// Update written grade averages from approved exam results
|
|
_, err := s.db.Exec(ctx, `
|
|
WITH avg_grades AS (
|
|
SELECT er.student_id, e.subject_id, AVG(er.grade) as avg_grade, COUNT(*) as grade_count
|
|
FROM exam_results er
|
|
JOIN exams e ON er.exam_id = e.id
|
|
JOIN students st ON er.student_id = st.id
|
|
WHERE st.class_id = $1 AND er.approved_by_teacher = true AND er.grade IS NOT NULL
|
|
GROUP BY er.student_id, e.subject_id
|
|
)
|
|
UPDATE grade_overview go SET
|
|
written_grade_avg = ag.avg_grade,
|
|
written_grade_count = ag.grade_count,
|
|
updated_at = NOW()
|
|
FROM avg_grades ag
|
|
WHERE go.student_id = ag.student_id AND go.subject_id = ag.subject_id AND go.semester = $2
|
|
`, classID, semester)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
// Calculate final grades based on weights
|
|
_, err = s.db.Exec(ctx, `
|
|
UPDATE grade_overview SET
|
|
final_grade = CASE
|
|
WHEN oral_grade IS NULL THEN written_grade_avg
|
|
WHEN written_grade_avg IS NULL THEN oral_grade
|
|
ELSE ROUND((written_grade_avg * written_weight + oral_grade * oral_weight) / (written_weight + oral_weight), 1)
|
|
END,
|
|
updated_at = NOW()
|
|
WHERE student_id IN (SELECT id FROM students WHERE class_id = $1)
|
|
AND semester = $2
|
|
AND final_grade_locked = false
|
|
`, classID, semester)
|
|
|
|
return err
|
|
}
|
|
|
|
// TransferApprovedGrades transfers approved exam results to grade overview
|
|
func (s *GradeService) TransferApprovedGrades(ctx context.Context, teacherID string) error {
|
|
// Get current school year
|
|
var schoolYearID string
|
|
err := s.db.QueryRow(ctx, `
|
|
SELECT id FROM school_years WHERE teacher_id = $1 AND is_current = true LIMIT 1
|
|
`, teacherID).Scan(&schoolYearID)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
// Current semester
|
|
semester := 1
|
|
|
|
// Ensure grade_overview records exist for all students with approved results
|
|
_, err = s.db.Exec(ctx, `
|
|
INSERT INTO grade_overview (student_id, subject_id, school_year_id, semester)
|
|
SELECT DISTINCT er.student_id, e.subject_id, $1, $2
|
|
FROM exam_results er
|
|
JOIN exams e ON er.exam_id = e.id
|
|
WHERE er.approved_by_teacher = true AND e.subject_id IS NOT NULL
|
|
ON CONFLICT (student_id, subject_id, school_year_id, semester) DO NOTHING
|
|
`, schoolYearID, semester)
|
|
|
|
return err
|
|
}
|
|
|
|
// LockFinalGrade locks a final grade (prevents further changes)
|
|
func (s *GradeService) LockFinalGrade(ctx context.Context, studentID, subjectID string, semester int) error {
|
|
_, err := s.db.Exec(ctx, `
|
|
UPDATE grade_overview SET final_grade_locked = true, updated_at = NOW()
|
|
WHERE student_id = $1 AND subject_id = $2 AND semester = $3
|
|
`, studentID, subjectID, semester)
|
|
return err
|
|
}
|
|
|
|
// UpdateGradeWeights updates the written/oral weights for grade calculation
|
|
func (s *GradeService) UpdateGradeWeights(ctx context.Context, studentID, subjectID string, writtenWeight, oralWeight int) error {
|
|
_, err := s.db.Exec(ctx, `
|
|
UPDATE grade_overview SET written_weight = $3, oral_weight = $4, updated_at = NOW()
|
|
WHERE student_id = $1 AND subject_id = $2
|
|
`, studentID, subjectID, writtenWeight, oralWeight)
|
|
return err
|
|
}
|
|
|
|
// =============================================
|
|
// STATISTICS METHODS
|
|
// =============================================
|
|
|
|
// ClassStatistics holds statistics for a class
|
|
type ClassStatistics struct {
|
|
ClassID string `json:"class_id"`
|
|
ClassName string `json:"class_name"`
|
|
StudentCount int `json:"student_count"`
|
|
ClassAverage float64 `json:"class_average"`
|
|
GradeDistribution map[string]int `json:"grade_distribution"`
|
|
BestGrade float64 `json:"best_grade"`
|
|
WorstGrade float64 `json:"worst_grade"`
|
|
PassRate float64 `json:"pass_rate"`
|
|
StudentsAtRisk int `json:"students_at_risk"`
|
|
SubjectAverages map[string]float64 `json:"subject_averages"`
|
|
}
|
|
|
|
// SubjectStatistics holds statistics for a subject within a class
|
|
type SubjectStatistics struct {
|
|
ClassID string `json:"class_id"`
|
|
SubjectID string `json:"subject_id"`
|
|
SubjectName string `json:"subject_name"`
|
|
StudentCount int `json:"student_count"`
|
|
Average float64 `json:"average"`
|
|
Median float64 `json:"median"`
|
|
GradeDistribution map[string]int `json:"grade_distribution"`
|
|
BestGrade float64 `json:"best_grade"`
|
|
WorstGrade float64 `json:"worst_grade"`
|
|
PassRate float64 `json:"pass_rate"`
|
|
ExamAverages []ExamAverage `json:"exam_averages"`
|
|
}
|
|
|
|
// ExamAverage holds average for a single exam
|
|
type ExamAverage struct {
|
|
ExamID string `json:"exam_id"`
|
|
Title string `json:"title"`
|
|
Average float64 `json:"average"`
|
|
ExamDate string `json:"exam_date"`
|
|
}
|
|
|
|
// StudentStatistics holds statistics for a single student
|
|
type StudentStatistics struct {
|
|
StudentID string `json:"student_id"`
|
|
StudentName string `json:"student_name"`
|
|
OverallAverage float64 `json:"overall_average"`
|
|
SubjectGrades map[string]float64 `json:"subject_grades"`
|
|
Trend string `json:"trend"` // "improving", "stable", "declining"
|
|
AbsenceDays int `json:"absence_days"`
|
|
ExamsCompleted int `json:"exams_completed"`
|
|
StrongestSubject string `json:"strongest_subject"`
|
|
WeakestSubject string `json:"weakest_subject"`
|
|
}
|
|
|
|
// Notenspiegel represents grade distribution
|
|
type Notenspiegel struct {
|
|
ClassID string `json:"class_id"`
|
|
SubjectID string `json:"subject_id,omitempty"`
|
|
ExamID string `json:"exam_id,omitempty"`
|
|
Title string `json:"title"`
|
|
Distribution map[string]int `json:"distribution"`
|
|
Total int `json:"total"`
|
|
Average float64 `json:"average"`
|
|
PassRate float64 `json:"pass_rate"`
|
|
}
|
|
|
|
// GetClassStatistics returns statistics for a class
|
|
func (s *GradeService) GetClassStatistics(ctx context.Context, classID string, semester int) (*ClassStatistics, error) {
|
|
stats := &ClassStatistics{
|
|
ClassID: classID,
|
|
GradeDistribution: make(map[string]int),
|
|
SubjectAverages: make(map[string]float64),
|
|
}
|
|
|
|
// Initialize grade distribution
|
|
for i := 1; i <= 6; i++ {
|
|
stats.GradeDistribution[string('0'+rune(i))] = 0
|
|
}
|
|
|
|
// Get class info and student count
|
|
err := s.db.QueryRow(ctx, `
|
|
SELECT c.name, COUNT(s.id)
|
|
FROM classes c
|
|
LEFT JOIN students s ON s.class_id = c.id
|
|
WHERE c.id = $1
|
|
GROUP BY c.name
|
|
`, classID).Scan(&stats.ClassName, &stats.StudentCount)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
// Build semester condition
|
|
semesterCond := ""
|
|
if semester > 0 {
|
|
semesterCond = " AND go.semester = " + string('0'+rune(semester))
|
|
}
|
|
|
|
// Get overall statistics from grade_overview
|
|
var avgGrade, bestGrade, worstGrade float64
|
|
var totalPassed, totalStudents int
|
|
err = s.db.QueryRow(ctx, `
|
|
SELECT
|
|
COALESCE(AVG(go.final_grade), 0),
|
|
COALESCE(MIN(go.final_grade), 0),
|
|
COALESCE(MAX(go.final_grade), 0),
|
|
COUNT(CASE WHEN go.final_grade <= 4.0 THEN 1 END),
|
|
COUNT(go.id)
|
|
FROM grade_overview go
|
|
JOIN students s ON s.id = go.student_id
|
|
WHERE s.class_id = $1 AND go.final_grade IS NOT NULL`+semesterCond+`
|
|
`, classID).Scan(&avgGrade, &bestGrade, &worstGrade, &totalPassed, &totalStudents)
|
|
|
|
if err == nil {
|
|
stats.ClassAverage = avgGrade
|
|
stats.BestGrade = bestGrade
|
|
stats.WorstGrade = worstGrade
|
|
if totalStudents > 0 {
|
|
stats.PassRate = float64(totalPassed) / float64(totalStudents) * 100
|
|
}
|
|
}
|
|
|
|
// Get grade distribution
|
|
rows, err := s.db.Query(ctx, `
|
|
SELECT FLOOR(go.final_grade) as grade_bucket, COUNT(*) as count
|
|
FROM grade_overview go
|
|
JOIN students s ON s.id = go.student_id
|
|
WHERE s.class_id = $1 AND go.final_grade IS NOT NULL`+semesterCond+`
|
|
GROUP BY FLOOR(go.final_grade)
|
|
`, classID)
|
|
if err == nil {
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
var bucket int
|
|
var count int
|
|
if err := rows.Scan(&bucket, &count); err == nil && bucket >= 1 && bucket <= 6 {
|
|
stats.GradeDistribution[string('0'+rune(bucket))] = count
|
|
}
|
|
}
|
|
}
|
|
|
|
// Count students at risk
|
|
s.db.QueryRow(ctx, `
|
|
SELECT COUNT(DISTINCT s.id)
|
|
FROM students s
|
|
JOIN grade_overview go ON go.student_id = s.id
|
|
WHERE s.class_id = $1 AND go.final_grade >= 4.5
|
|
`, classID).Scan(&stats.StudentsAtRisk)
|
|
|
|
// Get subject averages
|
|
subjectRows, err := s.db.Query(ctx, `
|
|
SELECT sub.name, AVG(go.final_grade)
|
|
FROM grade_overview go
|
|
JOIN subjects sub ON sub.id = go.subject_id
|
|
JOIN students s ON s.id = go.student_id
|
|
WHERE s.class_id = $1 AND go.final_grade IS NOT NULL`+semesterCond+`
|
|
GROUP BY sub.name
|
|
`, classID)
|
|
if err == nil {
|
|
defer subjectRows.Close()
|
|
for subjectRows.Next() {
|
|
var name string
|
|
var avg float64
|
|
if err := subjectRows.Scan(&name, &avg); err == nil {
|
|
stats.SubjectAverages[name] = avg
|
|
}
|
|
}
|
|
}
|
|
|
|
return stats, nil
|
|
}
|
|
|
|
// GetSubjectStatistics returns statistics for a specific subject in a class
|
|
func (s *GradeService) GetSubjectStatistics(ctx context.Context, classID, subjectID string, semester int) (*SubjectStatistics, error) {
|
|
stats := &SubjectStatistics{
|
|
ClassID: classID,
|
|
SubjectID: subjectID,
|
|
GradeDistribution: make(map[string]int),
|
|
ExamAverages: []ExamAverage{},
|
|
}
|
|
|
|
// Initialize grade distribution
|
|
for i := 1; i <= 6; i++ {
|
|
stats.GradeDistribution[string('0'+rune(i))] = 0
|
|
}
|
|
|
|
// Get subject name and basic stats
|
|
semesterCond := ""
|
|
if semester > 0 {
|
|
semesterCond = " AND go.semester = " + string('0'+rune(semester))
|
|
}
|
|
|
|
err := s.db.QueryRow(ctx, `
|
|
SELECT
|
|
sub.name,
|
|
COUNT(DISTINCT s.id),
|
|
COALESCE(AVG(go.final_grade), 0),
|
|
COALESCE(MIN(go.final_grade), 0),
|
|
COALESCE(MAX(go.final_grade), 0),
|
|
COUNT(CASE WHEN go.final_grade <= 4.0 THEN 1 END),
|
|
COUNT(go.id)
|
|
FROM grade_overview go
|
|
JOIN subjects sub ON sub.id = go.subject_id
|
|
JOIN students s ON s.id = go.student_id
|
|
WHERE s.class_id = $1 AND go.subject_id = $2 AND go.final_grade IS NOT NULL`+semesterCond+`
|
|
GROUP BY sub.name
|
|
`, classID, subjectID).Scan(
|
|
&stats.SubjectName, &stats.StudentCount, &stats.Average,
|
|
&stats.BestGrade, &stats.WorstGrade,
|
|
new(int), new(int), // We'll calculate pass rate separately
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
// Get grade distribution
|
|
rows, err := s.db.Query(ctx, `
|
|
SELECT FLOOR(go.final_grade), COUNT(*)
|
|
FROM grade_overview go
|
|
JOIN students s ON s.id = go.student_id
|
|
WHERE s.class_id = $1 AND go.subject_id = $2 AND go.final_grade IS NOT NULL`+semesterCond+`
|
|
GROUP BY FLOOR(go.final_grade)
|
|
`, classID, subjectID)
|
|
if err == nil {
|
|
defer rows.Close()
|
|
var passed, total int
|
|
for rows.Next() {
|
|
var bucket, count int
|
|
if err := rows.Scan(&bucket, &count); err == nil && bucket >= 1 && bucket <= 6 {
|
|
stats.GradeDistribution[string('0'+rune(bucket))] = count
|
|
total += count
|
|
if bucket <= 4 {
|
|
passed += count
|
|
}
|
|
}
|
|
}
|
|
if total > 0 {
|
|
stats.PassRate = float64(passed) / float64(total) * 100
|
|
}
|
|
}
|
|
|
|
// Get exam averages
|
|
examRows, err := s.db.Query(ctx, `
|
|
SELECT e.id, e.title, AVG(er.grade), COALESCE(e.exam_date::text, '')
|
|
FROM exams e
|
|
JOIN exam_results er ON er.exam_id = e.id
|
|
JOIN students s ON er.student_id = s.id
|
|
WHERE s.class_id = $1 AND e.subject_id = $2 AND er.grade IS NOT NULL
|
|
GROUP BY e.id, e.title, e.exam_date
|
|
ORDER BY e.exam_date DESC NULLS LAST
|
|
`, classID, subjectID)
|
|
if err == nil {
|
|
defer examRows.Close()
|
|
for examRows.Next() {
|
|
var ea ExamAverage
|
|
if err := examRows.Scan(&ea.ExamID, &ea.Title, &ea.Average, &ea.ExamDate); err == nil {
|
|
stats.ExamAverages = append(stats.ExamAverages, ea)
|
|
}
|
|
}
|
|
}
|
|
|
|
return stats, nil
|
|
}
|
|
|
|
// GetStudentStatistics returns statistics for a specific student
|
|
func (s *GradeService) GetStudentStatistics(ctx context.Context, studentID string) (*StudentStatistics, error) {
|
|
stats := &StudentStatistics{
|
|
StudentID: studentID,
|
|
SubjectGrades: make(map[string]float64),
|
|
}
|
|
|
|
// Get student name
|
|
err := s.db.QueryRow(ctx, `
|
|
SELECT CONCAT(first_name, ' ', last_name) FROM students WHERE id = $1
|
|
`, studentID).Scan(&stats.StudentName)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
// Get subject grades and calculate overall average
|
|
var totalGrade, numSubjects float64
|
|
var bestGrade float64 = 6
|
|
var worstGrade float64 = 1
|
|
var bestSubject, worstSubject string
|
|
|
|
rows, err := s.db.Query(ctx, `
|
|
SELECT sub.name, go.final_grade
|
|
FROM grade_overview go
|
|
JOIN subjects sub ON sub.id = go.subject_id
|
|
WHERE go.student_id = $1 AND go.final_grade IS NOT NULL
|
|
ORDER BY sub.name
|
|
`, studentID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
for rows.Next() {
|
|
var name string
|
|
var grade float64
|
|
if err := rows.Scan(&name, &grade); err == nil {
|
|
stats.SubjectGrades[name] = grade
|
|
totalGrade += grade
|
|
numSubjects++
|
|
if grade < bestGrade {
|
|
bestGrade = grade
|
|
bestSubject = name
|
|
}
|
|
if grade > worstGrade {
|
|
worstGrade = grade
|
|
worstSubject = name
|
|
}
|
|
}
|
|
}
|
|
|
|
if numSubjects > 0 {
|
|
stats.OverallAverage = totalGrade / numSubjects
|
|
stats.StrongestSubject = bestSubject
|
|
stats.WeakestSubject = worstSubject
|
|
}
|
|
|
|
// Count exams completed
|
|
s.db.QueryRow(ctx, `
|
|
SELECT COUNT(*) FROM exam_results WHERE student_id = $1 AND grade IS NOT NULL
|
|
`, studentID).Scan(&stats.ExamsCompleted)
|
|
|
|
// Count absence days
|
|
s.db.QueryRow(ctx, `
|
|
SELECT COALESCE(SUM(periods), 0) FROM attendance
|
|
WHERE student_id = $1 AND status IN ('absent_excused', 'absent_unexcused')
|
|
`, studentID).Scan(&stats.AbsenceDays)
|
|
|
|
// Determine trend (simplified - compare first and last exam grades)
|
|
stats.Trend = "stable"
|
|
var firstGrade, lastGrade float64
|
|
s.db.QueryRow(ctx, `
|
|
SELECT grade FROM exam_results
|
|
WHERE student_id = $1 AND grade IS NOT NULL
|
|
ORDER BY created_at ASC LIMIT 1
|
|
`, studentID).Scan(&firstGrade)
|
|
s.db.QueryRow(ctx, `
|
|
SELECT grade FROM exam_results
|
|
WHERE student_id = $1 AND grade IS NOT NULL
|
|
ORDER BY created_at DESC LIMIT 1
|
|
`, studentID).Scan(&lastGrade)
|
|
|
|
if lastGrade < firstGrade-0.5 {
|
|
stats.Trend = "improving"
|
|
} else if lastGrade > firstGrade+0.5 {
|
|
stats.Trend = "declining"
|
|
}
|
|
|
|
return stats, nil
|
|
}
|
|
|
|
// GetNotenspiegel returns grade distribution (Notenspiegel)
|
|
func (s *GradeService) GetNotenspiegel(ctx context.Context, classID, subjectID, examID string, semester int) (*Notenspiegel, error) {
|
|
ns := &Notenspiegel{
|
|
ClassID: classID,
|
|
SubjectID: subjectID,
|
|
ExamID: examID,
|
|
Distribution: make(map[string]int),
|
|
}
|
|
|
|
// Initialize distribution
|
|
for i := 1; i <= 6; i++ {
|
|
ns.Distribution[string('0'+rune(i))] = 0
|
|
}
|
|
|
|
var query string
|
|
var args []interface{}
|
|
|
|
if examID != "" {
|
|
// Notenspiegel for specific exam
|
|
query = `
|
|
SELECT e.title, FLOOR(er.grade), COUNT(*), AVG(er.grade)
|
|
FROM exam_results er
|
|
JOIN exams e ON e.id = er.exam_id
|
|
WHERE er.exam_id = $1 AND er.grade IS NOT NULL
|
|
GROUP BY e.title, FLOOR(er.grade)
|
|
`
|
|
args = []interface{}{examID}
|
|
} else if subjectID != "" {
|
|
// Notenspiegel for subject in class
|
|
semesterCond := ""
|
|
if semester > 0 {
|
|
semesterCond = " AND go.semester = " + string('0'+rune(semester))
|
|
}
|
|
query = `
|
|
SELECT sub.name, FLOOR(go.final_grade), COUNT(*), AVG(go.final_grade)
|
|
FROM grade_overview go
|
|
JOIN subjects sub ON sub.id = go.subject_id
|
|
JOIN students s ON s.id = go.student_id
|
|
WHERE s.class_id = $1 AND go.subject_id = $2 AND go.final_grade IS NOT NULL` + semesterCond + `
|
|
GROUP BY sub.name, FLOOR(go.final_grade)
|
|
`
|
|
args = []interface{}{classID, subjectID}
|
|
} else {
|
|
// Notenspiegel for entire class
|
|
semesterCond := ""
|
|
if semester > 0 {
|
|
semesterCond = " AND go.semester = " + string('0'+rune(semester))
|
|
}
|
|
query = `
|
|
SELECT c.name, FLOOR(go.final_grade), COUNT(*), AVG(go.final_grade)
|
|
FROM grade_overview go
|
|
JOIN students s ON s.id = go.student_id
|
|
JOIN classes c ON c.id = s.class_id
|
|
WHERE s.class_id = $1 AND go.final_grade IS NOT NULL` + semesterCond + `
|
|
GROUP BY c.name, FLOOR(go.final_grade)
|
|
`
|
|
args = []interface{}{classID}
|
|
}
|
|
|
|
rows, err := s.db.Query(ctx, query, args...)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
var passed int
|
|
for rows.Next() {
|
|
var title string
|
|
var bucket, count int
|
|
var avg float64
|
|
if err := rows.Scan(&title, &bucket, &count, &avg); err == nil {
|
|
ns.Title = title
|
|
if bucket >= 1 && bucket <= 6 {
|
|
ns.Distribution[string('0'+rune(bucket))] = count
|
|
ns.Total += count
|
|
if bucket <= 4 {
|
|
passed += count
|
|
}
|
|
}
|
|
ns.Average = avg
|
|
}
|
|
}
|
|
|
|
if ns.Total > 0 {
|
|
ns.PassRate = float64(passed) / float64(ns.Total) * 100
|
|
}
|
|
|
|
return ns, nil
|
|
}
|