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_code): """ Public view to list schools for a specific project Uses project_code instead of id for cleaner URLs """ from .models import JointExamProject, ExamSchoolResult from django.shortcuts import get_object_or_404 # Get project by code (must be live) project = get_object_or_404( JointExamProject, project_code=project_code, is_live=True, # 🔒 project must be live ) # Get schools with released results 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, "project_code": project_code, # Pass for template use } ) 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 >= 30: # category = "CENTRE WITH 30 OR MORE CANDIDATES" # else: # category = "CENTRE WITH LESS THAN 30 CANDIDATES" # # ================================================= # # SUBJECT PERFORMANCE # # ================================================= # grades = ["A", "B", "C", "D", "E", "S", "F"] # 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 # } # ) def public_school_results_by_project(request, project_code, school_code): """ Public view to show results for a specific school in a project Uses project_code instead of id """ from django.db.models import Count from collections import defaultdict import logging logger = logging.getLogger(__name__) # ----------------------------------- # PROJECT (must be live) - GET BY CODE # ----------------------------------- project = get_object_or_404( JointExamProject, project_code=project_code, # Changed from id to project_code 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 # EXCLUDE PRIVATE SCHOOLS # ================================================= all_school_results = ExamSchoolResult.objects.filter( project=project, sch_release_status=True, sch_rank_involvement=True, school__ownership__in=['GVT', 'NON-GVT'] # Exclude PRIVATE ).select_related('school__district', 'school__region') # ================================================= # DETERMINE CATEGORY BASED ON TOTAL STUDENTS WITH DIVISIONS (I,II,III,IV,0) # ================================================= total_candidates = StudentFinalResult.objects.filter( project=project, student__school=school, division__in=['I', 'II', 'III', 'IV', '0'] # Only count students with valid divisions ).count() # Define categories if total_candidates >= 30: category = "CENTRE WITH 30 OR MORE CANDIDATES" else: category = "CENTRE WITH LESS THAN 30 CANDIDATES" # ================================================= # FILTER SCHOOLS BY CATEGORY FOR RANKING # ================================================= # Get all schools with their candidate counts (only valid divisions) schools_with_counts = {} all_school_ids = all_school_results.values_list('school_id', flat=True) # Get candidate counts for each school (only divisions I,II,III,IV,0) candidate_counts = StudentFinalResult.objects.filter( project=project, student__school_id__in=all_school_ids, division__in=['I', 'II', 'III', 'IV', '0'] # Only count valid divisions ).values('student__school_id').annotate( total=Count('id') ) # Create mapping of school_id to candidate count for cc in candidate_counts: schools_with_counts[cc['student__school_id']] = cc['total'] # Filter schools by category if category == "CENTRE WITH 30 OR MORE CANDIDATES": filtered_schools = [] for sr in all_school_results: candidate_count = schools_with_counts.get(sr.school_id, 0) if candidate_count >= 30: filtered_schools.append(sr) filtered_school_results = filtered_schools else: # For schools with less than 30, include all for their category filtered_schools = [] for sr in all_school_results: candidate_count = schools_with_counts.get(sr.school_id, 0) if candidate_count < 30: filtered_schools.append(sr) filtered_school_results = filtered_schools # ================================================= # OVERALL RANK (within category, based on sch_gpa - lower is better) # ================================================= overall_rank = sum(1 for sr in filtered_school_results if sr.sch_gpa and school_result.sch_gpa and sr.sch_gpa < school_result.sch_gpa) + 1 total_schools = len(filtered_school_results) overall_rank_display_ok = f"{overall_rank}/{total_schools}" if total_schools > 0 else "-" # ================================================= # DISTRICT RANK (within category) # ================================================= district_schools = [sr for sr in filtered_school_results if sr.school.district_id == school.district_id] district_rank = sum(1 for sr in district_schools if sr.sch_gpa and school_result.sch_gpa and sr.sch_gpa < school_result.sch_gpa) + 1 total_district_schools = len(district_schools) district_rank_display_ok = f"{district_rank}/{total_district_schools}" if total_district_schools > 0 else "-" # ================================================= # REGION RANK (within category) # ================================================= region_schools = [sr for sr in filtered_school_results if sr.school.region_id == school.region_id] region_rank = sum(1 for sr in region_schools if sr.sch_gpa and school_result.sch_gpa and sr.sch_gpa < school_result.sch_gpa) + 1 total_region_schools = len(region_schools) region_rank_display_ok = f"{region_rank}/{total_region_schools}" if total_region_schools > 0 else "-" # Debug output to verify print(f"Category: {category}") print(f"Total candidates (valid divisions): {total_candidates}") print(f"Overall: {overall_rank_display_ok}, Region: {region_rank_display_ok}") print(f"Total schools in category: {total_schools}, Region schools: {total_region_schools}") 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' 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 division in ['I', 'II', 'III', 'IV', '0']: # Only count valid divisions 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() # ================================================= # SUBJECT PERFORMANCE # ================================================= grades = ["A", "B", "C", "D", "E", "S", "F"] 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 (exclude private schools) all_subject_results = [] try: all_subject_results = list(ExamSubjectResult.objects.filter( project=project, school__ownership__in=['GVT', 'NON-GVT'] # Exclude PRIVATE ).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-E) 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 subject rankings (overall, district, region - without category filtering) overall_rank_display = '-' 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 and ar.subj_gpa is not None] if subject_schools: # Create list with GPA and average score for sorting subject_schools_with_data = [] for ar in subject_schools: avg_score_val = ar.subj_avg if ar.subj_avg else 0 subject_schools_with_data.append({ 'result': ar, 'gpa': ar.subj_gpa, 'avg_score': avg_score_val }) # Sort: first by GPA (ascending), then by average score (descending) subject_schools_with_data.sort(key=lambda x: (x['gpa'], -x['avg_score'])) # Calculate overall rank rank = 1 for item in subject_schools_with_data: if item['result'].school_id == school.id: overall_rank_display = f"{rank}/{len(subject_schools_with_data)}" break rank += 1 # Calculate district rank district_subjects = [item for item in subject_schools_with_data if item['result'].school.district_id == school.district_id] if district_subjects: district_rank = 1 for item in district_subjects: if item['result'].school_id == school.id: district_rank_display = f"{district_rank}/{len(district_subjects)}" break district_rank += 1 # Calculate region rank region_subjects = [item for item in subject_schools_with_data if item['result'].school.region_id == school.region_id] if region_subjects: region_rank = 1 for item in region_subjects: if item['result'].school_id == school.id: region_rank_display = f"{region_rank}/{len(region_subjects)}" break region_rank += 1 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, "gpa_color": get_subject_style(gpa), }) 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, "project_code": project_code, # Pass project_code for template "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_display_ok, } ) 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)