from django.shortcuts import render, get_object_or_404 from .models import ( ExamType, School, ExamCandidate, SchoolSubjectPerformance, StudentSubjectMark, SchoolYearSummary, ExamSubject,GpaRule, ) from django.db.models import Count, Avg, F, Q def public_schools(request, year, exam_code): exam = get_object_or_404(ExamType, code__iexact=exam_code) # ONLY schools that exist in school_year_summary summaries = ( SchoolYearSummary.objects .select_related("school") .filter(year=year, exam_type=exam) .order_by("school__name") ) schools = summaries.values( "school__school_code", "school__name", "school__district__name", "school__region__name", "avg_grade", "avg_numeric", "avg_gpa", "candidates", ) return render(request, "public/schools.html", { "year": year, "exam": exam, "schools": schools, }) from django.shortcuts import render, get_object_or_404 from exams.models import ( School, ExamType, SchoolYearSummary, SchoolSubjectPerformance, ) from django.shortcuts import render, get_object_or_404 from django.db.models import Count, Avg, Sum from .models import ( ExamType, School, ExamCandidate, SchoolSubjectPerformance ) from exams.models import ( ExamType, School, ExamCandidate, StudentSubjectMark, SchoolSubjectPerformance, ) def public_school_results(request, year, exam_code, school_code): # -------------------------------------------------- # BASIC OBJECTS # -------------------------------------------------- exam = get_object_or_404(ExamType, code__iexact=exam_code) school = get_object_or_404(School, school_code=school_code) district = school.district region = school.region candidates = ExamCandidate.objects.filter( school=school, exam_type=exam, year=year ) # -------------------------------------------------- # DIVISION SUMMARY BY SEX # -------------------------------------------------- divisions = ["I", "II", "III", "IV", "0"] div_summary = [] for sex in ["F", "M"]: row = {"sex": sex} for d in divisions: row[d] = candidates.filter( student__sex=sex, # ✅ FIX HERE division=d ).count() div_summary.append(row) # TOTAL ROW total_row = {"sex": "T"} for d in divisions: total_row[d] = candidates.filter(division=d).count() div_summary.append(total_row) # -------------------------------------------------- # DYNAMIC GRADES BY EXAM TYPE # -------------------------------------------------- EXAM_GRADES = { "CSEE": ["A", "B", "C", "D", "F"], "ACSEE": ["A", "B", "C", "D", "E", "S", "F"], "FORM-SIX": ["A", "B", "C", "D", "E", "S", "F"], "FORM-ONE": ["A", "B", "C", "D", "F"], "FORM-THREE": ["A", "B", "C", "D", "F"], } grades = EXAM_GRADES.get(exam.code.upper(), ["A", "B", "C", "D", "F"]) # -------------------------------------------------- # SUBJECT MARKS # -------------------------------------------------- marks = StudentSubjectMark.objects.filter( school=school, exam_type=exam, year=year ) subject_stats = ( marks .values("subject_code") .annotate( sat=Count("student_number", filter=Q(grade__in=["A", "B", "C", "D", "E", "S", "F"])), **{f"grade_{g}": Count("id", filter=Q(grade=g)) for g in grades} ) .order_by("subject_code") ) # -------------------------------------------------- # SUBJECT NAME LOOKUP # -------------------------------------------------- subject_lookup = { s.subject_code: s.subject_name_eng for s in ExamSubject.objects.filter(is_active=True) } # -------------------------------------------------- # SCHOOL SUBJECT PERFORMANCE (FOR RANKING) # -------------------------------------------------- perf_map = { p.subject_code: p for p in SchoolSubjectPerformance.objects.filter( school_code=school.school_code, exam_type=exam, year=year ) } all_perf = SchoolSubjectPerformance.objects.filter( exam_type=exam, year=year ) # -------------------------------------------------- # HELPERS # -------------------------------------------------- def grade_from_gpa(gpa): if gpa <= 2.5: return "A" elif gpa <= 3.5: return "B" elif gpa <= 4.0: return "C" elif gpa <= 4.5: return "D" return "F" def get_rank(qs, value): if value is None: return None better = qs.filter(subj_avg__gt=value).count() total = qs.count() return f"{better + 1}/{total}" # -------------------------------------------------- # MERGE SUBJECT DATA # -------------------------------------------------- subjects_data = [] for s in subject_stats: subj_code = s["subject_code"] sat = s["sat"] grade_counts = {g: s.get(f"grade_{g}", 0) for g in grades} # Get performance object for the subject perf = perf_map.get(subj_code) # avg_score from SchoolSubjectPerformance avg_score = perf.subj_avg if perf else 0 avg_grade = perf.subj_grade if perf else '' subj_status = perf.subj_status if perf else '' # GPA from the same performance record gpa = perf.subj_gpa if perf else 0 # GPA from SchoolSubjectPerformance perf = perf_map.get(subj_code) gpa = perf.subj_gpa if perf else 0 pass_count = perf.total_pass if perf else 0 district_rank = None region_rank = None if perf: district_qs = all_perf.filter( subject_code=subj_code, school_code__in=School.objects.filter( district=district ).values_list("school_code", flat=True) ) region_qs = all_perf.filter( subject_code=subj_code, school_code__in=School.objects.filter( region=region ).values_list("school_code", flat=True) ) district_rank = get_rank(district_qs, perf.subj_avg) region_rank = get_rank(region_qs, perf.subj_avg) subjects_data.append({ "code": subj_code, "name": subject_lookup.get(subj_code, subj_code), "sat": sat, "grades": grade_counts, "avg_score": round(avg_score, 2), "avg_grade": avg_grade, "subj_status": subj_status, "pass": pass_count, "gpa": round(gpa, 4), "district_rank": district_rank, "region_rank": region_rank, }) # -------------------------------------------------- # OVERALL PERFORMANCE # -------------------------------------------------- overall = SchoolYearSummary.objects.filter(exam_type=exam, year=year).aggregate( registered=Count("id"), avg_gpa=Avg("avg_gpa"), # valid now avg_numeric=Avg("avg_numeric") # if you still need numeric average ) # -------------------------------------------------- # EXAMINATION CENTRE RANKING # -------------------------------------------------- # PASSED = exclude divisions IV and 0 (NECTA logic) passed_candidates = candidates.exclude(division__in=["0","-"]).count() centre_gpa = ( SchoolYearSummary.objects .filter( school=school, year=year ) .values_list("avg_gpa", flat=True) .first() or 0 ) centre_grade = ( SchoolYearSummary.objects .filter( school=school, year=year ) .values_list("avg_grade", flat=True) .first() or 0 ) centre_status = ( SchoolYearSummary.objects .filter( school=school, year=year ) .values_list("status", flat=True) .first() or 0 ) # CATEGORY category = ( "CENTRE WITH LESS THAN 40 CANDIDATES" if candidates.count() < 40 else "CENTRE WITH 40 OR MORE CANDIDATES" ) # ALL SCHOOLS SAME EXAM & YEAR all_centres = ( SchoolYearSummary.objects .filter(exam_type=exam, year=year) .values("school") .annotate(avg_gpa=Avg("avg_gpa")) ) # Helper rank function def centre_rank(qs, value): better = qs.filter(avg_gpa__lt=value).count() total = qs.count() return f"{better + 1}/{total}" # Council (District) rank district_centres = all_centres.filter( school__district=school.district ) district_rank = centre_rank(district_centres, centre_gpa) # Region rank region_centres = all_centres.filter( school__region=school.region ) region_rank = centre_rank(region_centres, centre_gpa) # National rank national_rank = centre_rank(all_centres, centre_gpa) # -------------------------------------------------- # RENDER # -------------------------------------------------- return render(request, "public/school_results.html", { "exam": exam, "school": school, "year": year, "grades": grades, "div_summary": div_summary, "subjects_data": subjects_data, "candidates": candidates.order_by("student_number"), "overall": overall, "passed_candidates": passed_candidates, "centre_gpa": round(centre_gpa, 4), "centre_grade": centre_grade, "centre_status": centre_status, "category": category, "district_rank": district_rank, "region_rank": region_rank, "national_rank": national_rank, }) from django.shortcuts import render from .models import ExamType from datetime import datetime def public_index(request): current_year = datetime.now().year exams = ExamType.objects.filter(is_active=True).order_by("name") return render(request, "public/index.html", { "exams": exams, "year": current_year, }) from django.http import FileResponse, Http404 from django.conf import settings import os def download_joint_pdf(request, filename): # Remove trailing slashes just in case filename = filename.strip("/") file_path = os.path.join( settings.MEDIA_ROOT, "joint-results", filename ) if not os.path.isfile(file_path): raise Http404("File not found") return FileResponse( open(file_path, "rb"), as_attachment=True, filename=filename ) from django.shortcuts import render, get_object_or_404 from exams.models import JointExamProject, ExamSchoolResult def public_project_schools(request, project_id): project = get_object_or_404( JointExamProject, id=project_id, is_live=True, # 🔒 project must be live ) schools = ( ExamSchoolResult.objects .select_related( "school", "school__district", "school__region", ) .filter( project=project, sch_release_status=True, # 🔒 school must be released ) .order_by("school__school_code") ) return render( request, "public/project_schools.html", { "project": project, "schools": schools, } ) from django.http import Http404 from exams.models import ( School, ExamSchoolResult, ExamCandidate, SchoolDivisionSummary, SchoolSubjectPerformance, ) def public_school_results(request, project_id, school_code): project = get_object_or_404( JointExamProject, id=project_id, is_live=True, ) school = get_object_or_404(School, school_code=school_code) # 🔒 check school release school_result = ExamSchoolResult.objects.filter( project=project, school=school, sch_release_status=True, ).first() if not school_result: raise Http404("Results not available") # ------------------------- # DATA (MATCH YOUR TEMPLATE) # ------------------------- candidates = ExamCandidate.objects.filter( school=school, exam_type=project.exam_type, year=project.year, ).order_by("student_number") div_summary = SchoolDivisionSummary.objects.filter( school=school, exam_type=project.exam_type, year=project.year, ) subjects_data = SchoolSubjectPerformance.objects.filter( school_code=school.school_code, exam_type=project.exam_type, year=project.year, ) return render( request, "exams/public/school_results.html", # YOUR OLD HTML { "school": school, "exam": project.exam_type, "year": project.year, "candidates": candidates, "div_summary": div_summary, "subjects_data": subjects_data, # ranking / GPA "centre_gpa": school_result.sch_gpa, "centre_grade": school_result.sch_gpa_grade, "centre_status": school_result.sch_gpa_status, } ) from django.shortcuts import render, get_object_or_404 from django.http import Http404 from django.db.models import Count, Q from .models import ( JointExamProject, ExamSchoolResult, StudentFinalResult, ExamSubjectResult, ProcessedSubjectScore, School, ) from django.shortcuts import render, get_object_or_404 from django.http import Http404 from django.db.models import Count, Q, Avg from collections import defaultdict import logging logger = logging.getLogger(__name__) def public_school_results_by_project(request, project_id, school_code): # ----------------------------------- # PROJECT (must be live) # ----------------------------------- project = get_object_or_404( JointExamProject, id=project_id, is_live=True, ) # ----------------------------------- # SCHOOL # ----------------------------------- school = get_object_or_404(School, school_code=school_code) # ----------------------------------- # SCHOOL RESULT (must be released) # ----------------------------------- school_result = ExamSchoolResult.objects.filter( project=project, school=school, sch_release_status=True, ).first() if not school_result: raise Http404("Results not available") # ================================================= # GET ALL SCHOOL RESULTS FOR RANKING # ================================================= all_school_results = ExamSchoolResult.objects.filter( project=project, sch_release_status=True, sch_rank_involvement=True ).select_related('school__district', 'school__region') # ================================================= # OVERALL RANK (based on sch_gpa - lower is better) # ================================================= overall_rank = all_school_results.filter( sch_gpa__lt=school_result.sch_gpa # Count schools with BETTER (lower) GPA ).count() + 1 total_schools = all_school_results.count() overall_rank_display_ok = f"{overall_rank}/{total_schools}" if total_schools > 0 else "-" # ================================================= # DISTRICT RANK # ================================================= district_schools = all_school_results.filter( school__district=school.district ) district_rank = district_schools.filter( sch_gpa__lt=school_result.sch_gpa # Count schools with BETTER (lower) GPA ).count() + 1 total_district_schools = district_schools.count() district_rank_display_ok = f"{district_rank}/{total_district_schools}" if total_district_schools > 0 else "-" # ================================================= # REGION RANK - with ONE region, should match overall rank # ================================================= region_schools = all_school_results.filter( school__region=school.region ) region_rank = region_schools.filter( sch_gpa__lt=school_result.sch_gpa # Count schools with BETTER (lower) GPA ).count() + 1 total_region_schools = region_schools.count() region_rank_ok_display = f"{region_rank}/{total_region_schools}" if total_region_schools > 0 else "-" # Debug output to verify print(f"Overall: {overall_rank_display_ok}, Region: {region_rank_ok_display}") print(f"Total schools: {total_schools}, Region schools: {total_region_schools}") # Debug output to verify print(f"District: {district_rank_display_ok}") # ================================================= # GPA RULES FOR COLOR CODING # ================================================= gpa_rules = list(GpaRule.objects.filter( academic_level=project.academic_level ).order_by('-min_gpa')) def get_gpa_style(gpa_value): """Return background color and text for GPA""" if not gpa_value: return {'color': '#FFFFFF', 'text': '-'} for rule in gpa_rules: if rule.min_gpa <= gpa_value <= rule.max_gpa: return { 'color': rule.gpa_colour, 'grade': rule.gpa_grade, 'status': rule.gpa_status, 'display': f"GRADE {rule.gpa_grade} ({rule.gpa_status})" } return {'color': '#FFFFFF', 'text': '-'} def get_subject_style(gpa_value): """Return background color for subject GPA""" if not gpa_value: return '#FFFFFF' # White default for rule in gpa_rules: if rule.min_gpa <= gpa_value <= rule.max_gpa: return rule.gpa_colour return '#FFFFFF' # ================================================= # DIVISION SUMMARY (FROM StudentFinalResult) # ================================================= student_results = StudentFinalResult.objects.filter( project=project, student__school=school, ).select_related('student') # Initialize counters div_counts = { 'F': {'I': 0, 'II': 0, 'III': 0, 'IV': 0, '0': 0}, 'M': {'I': 0, 'II': 0, 'III': 0, 'IV': 0, '0': 0} } for sr in student_results: sex = sr.student.sex division = sr.division if sex in div_counts and division in div_counts[sex]: div_counts[sex][division] += 1 # Build div_summary list div_summary = [] for sex in ['F', 'M']: div_summary.append({ 'student__sex': sex, 'I': div_counts[sex]['I'], 'II': div_counts[sex]['II'], 'III': div_counts[sex]['III'], 'IV': div_counts[sex]['IV'], '0': div_counts[sex]['0'], }) # Add total row total_row = {'student__sex': 'T'} total_row['I'] = div_counts['F']['I'] + div_counts['M']['I'] total_row['II'] = div_counts['F']['II'] + div_counts['M']['II'] total_row['III'] = div_counts['F']['III'] + div_counts['M']['III'] total_row['IV'] = div_counts['F']['IV'] + div_counts['M']['IV'] total_row['0'] = div_counts['F']['0'] + div_counts['M']['0'] div_summary.append(total_row) # ================================================= # CANDIDATES RESULTS (FULL LIST) # ================================================= candidates = ( StudentFinalResult.objects .select_related("student") .filter( project=project, student__school=school, ) .order_by("student__student_number") ) # ================================================= # PASSED COUNT (exclude division "0" and "ABS") # ================================================= passed_candidates = candidates.exclude( division__in=["0", "ABS", "-"] ).count() # Total candidates with results total_candidates = candidates.count() # ================================================= # CATEGORY (based on total candidates) # ================================================= if total_candidates >= 40: category = "CENTRE WITH 40 OR MORE CANDIDATES" else: category = "CENTRE WITH LESS THAN 40 CANDIDATES" # ================================================= # SUBJECT PERFORMANCE # ================================================= grades = ["A", "B", "C", "D", "E", "F", "S"] subjects_data = [] try: # Get all subjects for this academic level subjects = ExamSubject.objects.filter( academic_level=project.academic_level, is_active=True ).order_by('subject_code') # Get all processed subject scores for this school processed_scores = ProcessedSubjectScore.objects.filter( project=project, student__school=school ).select_related('subject', 'student') # Group by subject subject_scores = defaultdict(list) subject_grades = defaultdict(list) for ps in processed_scores: subject_scores[ps.subject_id].append(ps.percentage_score) if ps.grade: subject_grades[ps.subject_id].append(ps.grade) # Get subject results subject_results = {} try: subject_results_qs = ExamSubjectResult.objects.filter( project=project, school=school ).select_related('subject') for sr in subject_results_qs: subject_results[sr.subject_id] = sr except Exception as e: logger.error(f"Error getting ExamSubjectResult: {e}") # Get all subject results for ranking all_subject_results = [] try: all_subject_results = list(ExamSubjectResult.objects.filter( project=project ).select_related('school__district', 'school__region')) except Exception as e: logger.error(f"Error getting all subject results: {e}") # Build subject data for each subject for subject in subjects: # Get scores and grades for this subject scores = subject_scores.get(subject.subject_id, []) grades_list = subject_grades.get(subject.subject_id, []) # Convert percentage scores to float for calculation numeric_scores = [] for s in scores: if s and s != 'X': try: numeric_scores.append(float(s)) except (ValueError, TypeError): pass # Calculate statistics sat = len(numeric_scores) if sat > 0: avg_score = round(sum(numeric_scores) / sat, 2) # Determine grade from average (fallback if not in subject_results) avg_grade = '-' if avg_score >= 80: avg_grade = 'A' elif avg_score >= 70: avg_grade = 'B' elif avg_score >= 60: avg_grade = 'C' elif avg_score >= 50: avg_grade = 'D' elif avg_score >= 40: avg_grade = 'E' else: avg_grade = 'F' # Count grades grade_count = {g: 0 for g in grades} for g in grades_list: if g in grade_count: grade_count[g] += 1 # Calculate pass (A-C) pass_count = grade_count.get('A', 0) + grade_count.get('B', 0) + grade_count.get('C', 0) + grade_count.get('D', 0) + grade_count.get('E', 0) # Get subject result if available subject_result = subject_results.get(subject.subject_id) if subject_result: # Use data from ExamSubjectResult gpa = round(subject_result.subj_gpa, 4) if subject_result.subj_gpa else 0 subj_status = subject_result.subj_gpa_status or '-' avg_grade = subject_result.subj_avg_grd or avg_grade else: # Calculate GPA from grades gpa_points = {'A': 1, 'B': 2, 'C': 3, 'D': 4, 'E': 5, 'F': 7, 'S': 6} total_points = 0 total_students = 0 for g, count in grade_count.items(): if g in gpa_points and count > 0: total_points += gpa_points[g] * count total_students += count gpa = round(total_points / total_students, 4) if total_students > 0 else 0 subj_status = '-' # Calculate rankings district_rank_display = '-' region_rank_display = '-' # Filter all subject results for this subject subject_schools = [ar for ar in all_subject_results if ar.subject_id == subject.subject_id] # OVERALL RANK (all schools) if subject_schools: better_overall = sum(1 for ar in subject_schools if ar.subj_gpa and ar.subj_gpa < gpa) # Lower GPA is better overall_rank_display = f"{better_overall + 1}/{len(subject_schools)}" # District rank district_subjects = [ar for ar in subject_schools if ar.school.district_id == school.district_id] if district_subjects: better = sum(1 for ar in district_subjects if ar.subj_gpa and ar.subj_gpa < gpa) # Lower GPA is better district_rank_display = f"{better + 1}/{len(district_subjects)}" # Region rank region_subjects = [ar for ar in subject_schools if ar.school.region_id == school.region_id] if region_subjects: better = sum(1 for ar in region_subjects if ar.subj_gpa and ar.subj_gpa < gpa) # Lower GPA is better region_rank_display = f"{better + 1}/{len(region_subjects)}" subjects_data.append({ "code": subject.subject_code, "name": subject.subject_name_eng, "sat": sat, "pass": pass_count, "grades": grade_count, "avg_score": avg_score, "avg_grade": avg_grade, "subj_status": subj_status, "gpa": gpa, "district_rank_subj": district_rank_display, "region_rank_subj": region_rank_display, "overall_rank_subj": overall_rank_display, # Added overall rank "gpa_color": get_subject_style(gpa), # Add this }) except Exception as e: logger.error(f"Error in subject performance: {e}") subjects_data = [] # Sort subjects by subject_code subjects_data.sort(key=lambda x: x["code"]) # ================================================= # RANKING / GPA with color coding # ================================================= centre_gpa = round(school_result.sch_gpa, 4) if school_result.sch_gpa else 0 centre_grade = school_result.sch_gpa_grade or "-" centre_status = school_result.sch_gpa_status or "-" # Get GPA style for centre centre_gpa_style = get_gpa_style(centre_gpa) # ================================================= # RENDER PUBLIC TEMPLATE # ================================================= return render( request, "public/school_results.html", { "school": school, "year": project.year, "project": project, "div_summary": div_summary, "candidates": candidates, "subjects_data": subjects_data, "grades": grades, "centre_gpa": centre_gpa, "centre_grade": centre_grade, "centre_status": centre_status, "centre_gpa_style": centre_gpa_style, "gpa_rules": gpa_rules, "category": category, "passed_candidates": passed_candidates, "total_candidates": total_candidates, "overall_rank": overall_rank_display_ok, "district_rank": district_rank_display_ok, "region_rank": region_rank_ok_display, # Now correctly using region } ) from django.shortcuts import render, get_object_or_404 from django.contrib.auth.decorators import login_required from django.db.models import Q @login_required def project_schools_alpha_list(request, project_id): """ Display schools in a project with alphabet filtering """ project = get_object_or_404(JointExamProject, id=project_id) # Get filter letter from query params letter = request.GET.get('letter', 'all') # Get schools with results in this project schools = ExamSchoolResult.objects.filter( project=project ).select_related('school').order_by('school__name_short') # Apply alphabet filter if letter != 'all' and len(letter) == 1: schools = schools.filter( Q(school__name_short__istartswith=letter) | Q(school__name__istartswith=letter) ) context = { 'project': project, 'schools': schools, 'current_letter': letter, } return render(request, 'exams/reports/project_schools_alpha_list.html', context)