# exams/views_reports_combined.py from django.shortcuts import render, get_object_or_404 from django.db.models import Sum, Q from collections import defaultdict from exams.models import ( JointExamProject, SchoolDivisionSummary, ExamSchoolResult, StudentFinalResult, ProcessedSubjectScore, StudentSubjectMark, ProjectStudent, StudentFinalResult, ExamSubject, ExamCombination, ) from django.db.models import Sum, F,Count,Q from django.db.models.functions import Coalesce import openpyxl from openpyxl import Workbook from openpyxl.utils import get_column_letter from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from django.contrib.auth.decorators import login_required from django.template.defaulttags import register @register.filter def get_item(dictionary, key): return dictionary.get(key) if dictionary else None # Then in your context, add the filter context = { # ... your other context ... 'get_item': get_item, } def calculate_gpa_dynamic(scope_type, scope_id, project_id, academic_level_id): """ Calculate GPA dynamically for a specific scope """ # Division GPA division_qs = StudentFinalResult.objects.filter( project_id=project_id ).filter(division__in=['I', 'II', 'III', 'IV', '0']) if scope_type == 'region': division_qs = division_qs.filter(student__school__district__region_id=scope_id) elif scope_type == 'district': division_qs = division_qs.filter(student__school__district_id=scope_id) elif scope_type == 'school': division_qs = division_qs.filter(student__school_id=scope_id) # For 'overall', no additional filtering - use all data div_i = division_qs.filter(division='I').count() div_ii = division_qs.filter(division='II').count() div_iii = division_qs.filter(division='III').count() div_iv = division_qs.filter(division='IV').count() div_o = division_qs.filter(division='0').count() total_divisions = division_qs.count() division_gpa = 0 if total_divisions > 0: division_gpa = (div_i * 1 + div_ii * 2 + div_iii * 3 + div_iv * 4 + div_o * 5) / total_divisions # Subject GPA subject_qs = ProcessedSubjectScore.objects.filter(project_id=project_id) if scope_type == 'region': subject_qs = subject_qs.filter(student__school__district__region_id=scope_id) elif scope_type == 'district': subject_qs = subject_qs.filter(student__school__district_id=scope_id) elif scope_type == 'school': subject_qs = subject_qs.filter(student__school_id=scope_id) # For 'overall', no additional filtering - use all data grade_counts = { 'A': subject_qs.filter(grade='A').count(), 'B': subject_qs.filter(grade='B').count(), 'C': subject_qs.filter(grade='C').count(), 'D': subject_qs.filter(grade='D').count(), 'E': subject_qs.filter(grade='E').count(), 'S': subject_qs.filter(grade='S').count(), 'F': subject_qs.filter(grade='F').count(), } total_subjects = subject_qs.count() subject_gpa = 0 if total_subjects > 0: if academic_level_id == 1: # O-Level subject_gpa = ( grade_counts['A'] * 1 + grade_counts['B'] * 2 + grade_counts['C'] * 3 + grade_counts['D'] * 4 + grade_counts['F'] * 5 ) / total_subjects elif academic_level_id == 2: # A-Level subject_gpa = ( grade_counts['A'] * 1 + grade_counts['B'] * 2 + grade_counts['C'] * 3 + grade_counts['D'] * 4 + grade_counts['E'] * 5 + grade_counts['S'] * 6 + grade_counts['F'] * 7 ) / total_subjects elif academic_level_id == 3: # Diploma subject_gpa = ( grade_counts['A'] * 1 + grade_counts['B'] * 2 + grade_counts['C'] * 3 + grade_counts['D'] * 4 + grade_counts['E'] * 5 ) / total_subjects # Overall GPA if subject_gpa > 0 and division_gpa > 0: overall_gpa = (subject_gpa + division_gpa) / 2 elif subject_gpa > 0: overall_gpa = subject_gpa elif division_gpa > 0: overall_gpa = division_gpa else: overall_gpa = 0 return round(overall_gpa, 4) def calculate_gpa_from_counts(div_i, div_ii, div_iii, div_iv, div_o, total, academic_level_id, grade_counts=None): """ Calculate GPA from division counts and optional grade counts """ # Division GPA division_gpa = 0 if total > 0: division_gpa = (div_i * 1 + div_ii * 2 + div_iii * 3 + div_iv * 4 + div_o * 5) / total # Subject GPA subject_gpa = 0 if grade_counts: subject_total = sum(grade_counts.values()) if subject_total > 0: if academic_level_id == 1: # O-Level subject_gpa = ( grade_counts.get('A', 0) * 1 + grade_counts.get('B', 0) * 2 + grade_counts.get('C', 0) * 3 + grade_counts.get('D', 0) * 4 + grade_counts.get('F', 0) * 5 ) / subject_total elif academic_level_id == 2: # A-Level subject_gpa = ( grade_counts.get('A', 0) * 1 + grade_counts.get('B', 0) * 2 + grade_counts.get('C', 0) * 3 + grade_counts.get('D', 0) * 4 + grade_counts.get('E', 0) * 5 + grade_counts.get('S', 0) * 6 + grade_counts.get('F', 0) * 7 ) / subject_total elif academic_level_id == 3: # Diploma subject_gpa = ( grade_counts.get('A', 0) * 1 + grade_counts.get('B', 0) * 2 + grade_counts.get('C', 0) * 3 + grade_counts.get('D', 0) * 4 + grade_counts.get('E', 0) * 5 ) / subject_total # Overall GPA if subject_gpa > 0 and division_gpa > 0: overall_gpa = (subject_gpa + division_gpa) / 2 elif subject_gpa > 0: overall_gpa = subject_gpa elif division_gpa > 0: overall_gpa = division_gpa else: overall_gpa = 0 return round(overall_gpa, 4) def combined_form_six_report(request, project_code): """ Combined report for Form Six results using project_code """ from django.shortcuts import get_object_or_404 from .models import JointExamProject # Get project by code instead of id project = get_object_or_404(JointExamProject, project_code=project_code) project_id = project.id academic_level_id = project.academic_level_id # ========================= # GET REGION CORRECTLY # ========================= region_name = ( StudentFinalResult.objects .filter(project_id=project_id) .select_related("student__school__district__region") .values_list( "student__school__district__region__name", flat=True ) .first() ) # ========================= # PAGE 1 — PASS / FAIL (FROM STUDENT RESULTS) # ========================= from django.db.models import Sum, Q, Count totals = ( StudentFinalResult.objects .filter(project_id=project_id) .aggregate( total=Count('id', filter=Q(division__in=['I', 'II', 'III', 'IV', '0'])), pass_=Count('id', filter=Q(division__in=['I', 'II', 'III'])), fail=Count('id', filter=Q(division__in=['0', 'IV'])), fail_=Count('id', filter=Q(division="0")), ) ) total = totals["total"] or 1 pass_pct = round(totals["pass_"] * 100 / total, 2) fail_pct = round(totals["fail"] * 100 / total, 2) # ========================= # PAGE 2–3 — DIVISION BY GENDER (TEMPLATE-SAFE) # ========================= from django.db.models import Count from collections import defaultdict # Get registered students count by sex registered_counts = ProjectStudent.objects.filter( project_id=project_id ).values('sex').annotate( count=Count('id') ) # Initialize counts registered_f = 0 registered_m = 0 for item in registered_counts: if item['sex'] == 'F': registered_f = item['count'] elif item['sex'] == 'M': registered_m = item['count'] registered_total = registered_f + registered_m # Get students who sat for exams (have results with division I,II,III,IV,0 - excluding ABS) sat_counts = StudentFinalResult.objects.filter( project_id=project_id ).exclude( division__iexact='ABS' ).values( 'student__sex' ).annotate( count=Count('id') ) sat_f = 0 sat_m = 0 for item in sat_counts: if item['student__sex'] == 'F': sat_f = item['count'] elif item['student__sex'] == 'M': sat_m = item['count'] sat_total = sat_f + sat_m # Get absentees (those with ABS division) absent_counts = StudentFinalResult.objects.filter( project_id=project_id, division__iexact='ABS' ).values( 'student__sex' ).annotate( count=Count('id') ) absent_f = 0 absent_m = 0 for item in absent_counts: if item['student__sex'] == 'F': absent_f = item['count'] elif item['student__sex'] == 'M': absent_m = item['count'] absent_total = absent_f + absent_m DIV_KEYS = ["I", "II", "III", "IV", "0"] gender_div = {} rows = ( StudentFinalResult.objects .filter(project_id=project_id) .values("student__sex", "division") .annotate(t=Count("id")) ) # initialize structure for sex in ["F", "M"]: gender_div[sex] = {k: 0 for k in DIV_KEYS} # fill counts for r in rows: sex = r["student__sex"] div = r["division"] if sex in gender_div and div in gender_div[sex]: gender_div[sex][div] = r["t"] # add TOTAL row gender_div["T"] = { k: gender_div["F"][k] + gender_div["M"][k] for k in DIV_KEYS } # Calculate totals for each sex for sex in ["F", "M", "T"]: # Total students who sat (I+II+III+IV+0) total_sat = sum(gender_div[sex].values()) # Calculate cumulative totals gender_div[sex]["total_I_II"] = gender_div[sex]["I"] + gender_div[sex]["II"] gender_div[sex]["total_I_III"] = gender_div[sex]["I"] + gender_div[sex]["II"] + gender_div[sex]["III"] gender_div[sex]["total_I_IV"] = gender_div[sex]["I"] + gender_div[sex]["II"] + gender_div[sex]["III"] + gender_div[sex]["IV"] # Calculate percentages with 2 decimal places (avoid division by zero) if total_sat > 0: gender_div[sex]["percent_I_II"] = round((gender_div[sex]["total_I_II"] / total_sat) * 100, 2) gender_div[sex]["percent_I_III"] = round((gender_div[sex]["total_I_III"] / total_sat) * 100, 2) gender_div[sex]["percent_I_IV"] = round((gender_div[sex]["total_I_IV"] / total_sat) * 100, 2) else: gender_div[sex]["percent_I_II"] = 0.00 gender_div[sex]["percent_I_III"] = 0.00 gender_div[sex]["percent_I_IV"] = 0.00 # Store total_sat for reference if needed gender_div[sex]["total_sat"] = total_sat # ====================================================== # OVERALL GPA CALCULATION # ====================================================== # Get all students with their results students_with_results = StudentFinalResult.objects.filter( project_id=project_id ).select_related('student') # Initialize grade counters grade_counts = { 'A': 0, 'B': 0, 'C': 0, 'D': 0, 'E': 0, 'S': 0, 'F': 0 } division_counts = { 'I': 0, 'II': 0, 'III': 0, 'IV': 0, '0': 0 } # Get all ProcessedSubjectScore for this project subject_scores = ProcessedSubjectScore.objects.filter( project_id=project_id ).select_related('student') # Calculate Subject GPA based on academic level subject_grade_counts = { 'A': 0, 'B': 0, 'C': 0, 'D': 0, 'E': 0, 'S': 0, 'F': 0 } for score in subject_scores: grade = score.grade # Assuming ProcessedSubjectScore has grade field if grade in subject_grade_counts: subject_grade_counts[grade] += 1 total_subject_grades = sum(subject_grade_counts.values()) # Calculate Subject GPA based on Academic Level if total_subject_grades > 0: if academic_level_id == 1: # A(1), B(2), C(3), D(4), F(5) subject_gpa = ( subject_grade_counts['A'] * 1 + subject_grade_counts['B'] * 2 + subject_grade_counts['C'] * 3 + subject_grade_counts['D'] * 4 + subject_grade_counts['F'] * 5 ) / total_subject_grades elif academic_level_id == 2: # A(1), B(2), C(3), D(4), E(5), S(6), F(7) subject_gpa = ( subject_grade_counts['A'] * 1 + subject_grade_counts['B'] * 2 + subject_grade_counts['C'] * 3 + subject_grade_counts['D'] * 4 + subject_grade_counts['E'] * 5 + subject_grade_counts['S'] * 6 + subject_grade_counts['F'] * 7 ) / total_subject_grades elif academic_level_id == 3: # A(1), B(2), C(3), D(4), E(5) subject_gpa = ( subject_grade_counts['A'] * 1 + subject_grade_counts['B'] * 2 + subject_grade_counts['C'] * 3 + subject_grade_counts['D'] * 4 + subject_grade_counts['E'] * 5 ) / total_subject_grades else: subject_gpa = 0 else: subject_gpa = 0 # Calculate Division GPA based on academic level division_gpa = 0 total_divisions = 0 if academic_level_id in [1, 2]: # For O-Level and A-Level (using divisions I,II,III,IV,0) total_divisions = ( gender_div['T']['I'] + gender_div['T']['II'] + gender_div['T']['III'] + gender_div['T']['IV'] + gender_div['T']['0'] ) if total_divisions > 0: division_gpa = ( gender_div['T']['I'] * 1 + gender_div['T']['II'] * 2 + gender_div['T']['III'] * 3 + gender_div['T']['IV'] * 4 + gender_div['T']['0'] * 5 ) / total_divisions elif academic_level_id == 3: # For Diploma/Certificate (using average grades) avg_grade_counts = { 'A': 0, 'B': 0, 'C': 0, 'D': 0, 'E': 0 } for result in students_with_results: avg_grade = result.std_averagegrade # Assuming this field exists if avg_grade in avg_grade_counts: avg_grade_counts[avg_grade] += 1 total_avg_grades = sum(avg_grade_counts.values()) if total_avg_grades > 0: division_gpa = ( avg_grade_counts['A'] * 1 + avg_grade_counts['B'] * 2 + avg_grade_counts['C'] * 3 + avg_grade_counts['D'] * 4 + avg_grade_counts['E'] * 5 ) / total_avg_grades # Calculate Overall GPA if subject_gpa > 0 and division_gpa > 0: overall_gpa = (subject_gpa + division_gpa) / 2 elif subject_gpa > 0: overall_gpa = subject_gpa elif division_gpa > 0: overall_gpa = division_gpa else: overall_gpa = 0 # Round to 2 decimal places subject_gpa = round(subject_gpa, 4) division_gpa = round(division_gpa, 4) overall_gpa = round(overall_gpa, 4) # ====================================================== # PREPARE OVERALL TABLE DATA # ====================================================== # Get grade counts from ProcessedSubjectScore grade_distribution = { 'A': subject_grade_counts['A'], 'B': subject_grade_counts['B'], 'C': subject_grade_counts['C'], 'D': subject_grade_counts['D'], 'E': subject_grade_counts['E'], 'S': subject_grade_counts['S'], 'F': subject_grade_counts['F'] } # Division counts from gender_div['T'] division_distribution = { 'I': gender_div['T']['I'], 'II': gender_div['T']['II'], 'III': gender_div['T']['III'], 'IV': gender_div['T']['IV'], '0': gender_div['T']['0'] } overall_table = { 'divisions': division_distribution, 'grades': grade_distribution, 'subject_gpa': subject_gpa, 'division_gpa': division_gpa, 'overall_gpa': overall_gpa, 'academic_level': academic_level_id } # ========================= # PAGE 4–5 — SCHOOL RANKINGS # ========================= # First, get all schools with valid GPA (not None) school_ranking = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False # Only include schools with GPA ).select_related( 'school', 'school__district' ).order_by('sch_gpa') # Rank by GPA descending # BEST 10 OVERALL (regardless of ownership) best_10_all = school_ranking[:10] # In your view, when building best_10_all for school_result in best_10_all: school = school_result.school # Get division counts for this school division_counts = StudentFinalResult.objects.filter( project_id=project_id, student__school=school ).filter(division__in=['I', 'II', 'III', 'IV', '0']).values('division').annotate(count=Count('id')) div_i = 0 div_ii = 0 div_iii = 0 div_iv = 0 div_o = 0 for item in division_counts: if item['division'] == 'I': div_i = item['count'] elif item['division'] == 'II': div_ii = item['count'] elif item['division'] == 'III': div_iii = item['count'] elif item['division'] == 'IV': div_iv = item['count'] elif item['division'] == '0': div_o = item['count'] total_sat = div_i + div_ii + div_iii + div_iv + div_o pass_i_iii = div_i + div_ii + div_iii pass_i_iv = pass_i_iii + div_iv # Add these attributes to the school_result object school_result.d1 = div_i school_result.d2 = div_ii school_result.d3 = div_iii school_result.d4 = div_iv school_result.d0 = div_o school_result.pass_i_iii = pass_i_iii school_result.pass_i_iii_pct = round(pass_i_iii / total_sat * 100, 1) if total_sat > 0 else 0 school_result.pass_i_iv = pass_i_iv school_result.pass_i_iv_pct = round(pass_i_iv / total_sat * 100, 1) if total_sat > 0 else 0 # BEST 10 GOVERNMENT SCHOOLS best_10_gvt = school_ranking.filter( school__ownership="GVT" )[:10] # BEST 10 NON-GOVERNMENT SCHOOLS best_10_nongvt = school_ranking.filter( school__ownership="NON-GVT" )[:10] schools_with_counts = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).annotate( candidates_count=Count( 'school__projectstudent__studentfinalresult', filter=Q(school__projectstudent__studentfinalresult__project_id=project_id) & ~Q(school__projectstudent__studentfinalresult__division__iexact='ABS') ) ) # BEST SCHOOLS WITH 40+ CANDIDATES best_40_std_andabove = schools_with_counts.filter( candidates_count__gte=30 ).order_by('sch_gpa')[:10] # Top 10 among schools with 40+ candidates # BEST SCHOOLS WITH LESS THAN 40 CANDIDATES best_40_less = schools_with_counts.filter( candidates_count__lt=30 ).order_by('sch_gpa')[:10] # Top 10 among schools with <40 candidates # ====================================================== # ALTERNATIVE APPROACH: If ExamSchoolResult doesn't have direct relation to student counts # ====================================================== # Alternative method using separate query from django.db.models import Count, Q, OuterRef, Subquery # Get student counts per school student_counts = ProjectStudent.objects.filter( project_id=project_id, school_id=OuterRef('school_id') ).annotate( result_count=Count( 'studentfinalresult', filter=Q(studentfinalresult__project_id=project_id) & ~Q(studentfinalresult__division__iexact='ABS') ) ).values('result_count')[:1] # Annotate ExamSchoolResult with candidate count schools_with_counts_v2 = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).annotate( candidates_count=Subquery(student_counts) ).order_by('sch_gpa') # BEST SCHOOLS WITH 40+ CANDIDATES best_40_std_andabove_v2 = schools_with_counts_v2.filter( candidates_count__gte=30 )[:10] # BEST SCHOOLS WITH LESS THAN 40 CANDIDATES best_40_less_v2 = schools_with_counts_v2.filter( candidates_count__lt=30 )[:10] # ====================================================== # FOR DEBUGGING: Print counts to console # ====================================================== print(f"Total schools with valid GPA: {school_ranking.count()}") print(f"Schools with 40+ candidates: {schools_with_counts.filter(candidates_count__gte=30).count()}") print(f"Schools with <40 candidates: {schools_with_counts.filter(candidates_count__lt=30).count()}") # ====================================================== # BEST STUDENTS (Lowest total_points = Best Performance) # Points must be 3 or above (valid results) # ====================================================== # OVERALL BEST 10 STUDENTS (points >= 3, lowest first) best_students = ( StudentFinalResult.objects .filter( project_id=project_id, total_points__gte=3 # Only include points 3 or above ) .select_related( "student", "student__school", "student__combination" ) .order_by("total_points")[:10] # Ascending = lowest points first ) # BEST 10 GIRLS (points >= 3) best_girls = ( StudentFinalResult.objects .filter( project_id=project_id, student__sex="F", total_points__gte=3 ) .select_related( "student", "student__school", "student__combination" ) .order_by("total_points")[:10] ) # BEST 10 BOYS (points >= 3) best_boys = ( StudentFinalResult.objects .filter( project_id=project_id, student__sex="M", total_points__gte=3 ) .select_related( "student", "student__school", "student__combination" ) .order_by("total_points")[:10] ) # ====================================================== # BEST STUDENTS BY SCHOOL OWNERSHIP # ====================================================== # BEST 10 STUDENTS FROM GOVERNMENT SCHOOLS best_gvt_students = ( StudentFinalResult.objects .filter( project_id=project_id, student__school__ownership="GVT", total_points__gte=3 ) .select_related( "student", "student__school", "student__combination" ) .order_by("total_points")[:10] ) # BEST 10 STUDENTS FROM NON-GOVERNMENT SCHOOLS best_nongvt_students = ( StudentFinalResult.objects .filter( project_id=project_id, student__school__ownership="NON-GVT", total_points__gte=3 ) .select_related( "student", "student__school", "student__combination" ) .order_by("total_points")[:10] ) # ====================================================== # BEST STUDENTS BY COMBINATION (Top 3 per combination) # ====================================================== from django.db.models import Window, F, Count, Q, Sum from django.db.models.functions import RowNumber, Coalesce # Get all combinations that have students in this project combinations_with_students = ExamCombination.objects.filter( projectstudent__project_id=project_id, projectstudent__studentfinalresult__project_id=project_id, projectstudent__studentfinalresult__total_points__gte=3 ).distinct().order_by('code') # Initialize list to store combination performance data combination_performance = [] for combo in combinations_with_students: # Get top 10 students for this combination top_students = ( StudentFinalResult.objects .filter( project_id=project_id, student__combination=combo, total_points__gte=3 ) .select_related( "student", "student__school" ) .order_by('total_points')[:10] # Top 10 by lowest points ) # Get division counts for this combination division_counts = StudentFinalResult.objects.filter( project_id=project_id, student__combination=combo, total_points__gte=3 ).values('division').annotate( count=Count('id') ) # Initialize division dictionary div_counts = {'I': 0, 'II': 0, 'III': 0, 'IV': 0, '0': 0} total_students = 0 for item in division_counts: div = item['division'] count = item['count'] if div in div_counts: div_counts[div] = count total_students += count # Calculate Combination Division GPA (4 decimal places) # Formula: (I*1 + II*2 + III*3 + IV*4 + 0*5) / Total Students if total_students > 0: div_gpa = ( div_counts['I'] * 1 + div_counts['II'] * 2 + div_counts['III'] * 3 + div_counts['IV'] * 4 + div_counts['0'] * 5 ) / total_students # Round to 4 decimal places div_gpa = round(div_gpa, 4) else: div_gpa = 0.0000 # Grade distribution for this combination grade_dist = StudentFinalResult.objects.filter( project_id=project_id, student__combination=combo, total_points__gte=3 ).values('division').annotate( count=Count('id') ).order_by('division') grade_dict = {'I': 0, 'II': 0, 'III': 0, 'IV': 0, '0': 0} for g in grade_dist: if g['division'] in grade_dict: grade_dict[g['division']] = g['count'] combination_performance.append({ 'combination': combo, 'code': combo.code, 'name': combo.name, 'category': combo.combination_category, 'total_students': total_students, 'div_gpa': f"{div_gpa:.4f}", # Format with 4 decimal places 'div_gpa_raw': div_gpa, # Raw value for sorting 'top_students': top_students, 'grade_distribution': grade_dict, 'rank': len(combination_performance) + 1 }) # Sort combinations by Division GPA (lower is better) combination_performance.sort(key=lambda x: x['div_gpa_raw']) # Take top 20 combinations or all if less top_combinations = combination_performance[:20] # ====================================================== # SUBJECT SUMMARY WITH GPA AND PERCENTAGES (ALL CALCULATIONS IN VIEW) # ====================================================== # Get academic level ID academic_level_id = project.academic_level_id # Define GPA weights based on academic level if academic_level_id == 1: # O-Level / Form Four # A=1, B=2, C=3, D=4, F=5 gpa_weights = { 'A': 1, 'B': 2, 'C': 3, 'D': 4, 'F': 5 } valid_grades = ['A', 'B', 'C', 'D', 'F'] # Pass grades for O-Level: A-D pass_grades = ['A', 'B', 'C', 'D'] elif academic_level_id == 2: # A-Level / Form Six # A=1, B=2, C=3, D=4, E=5, S=6, F=7 gpa_weights = { 'A': 1, 'B': 2, 'C': 3, 'D': 4, 'E': 5, 'S': 6, 'F': 7 } valid_grades = ['A', 'B', 'C', 'D', 'E', 'S', 'F'] # Pass grades for A-Level: A-E pass_grades = ['A', 'B', 'C', 'D', 'E'] elif academic_level_id == 3: # Diploma / Certificate / Primary # A=1, B=2, C=3, D=4, E=5 gpa_weights = { 'A': 1, 'B': 2, 'C': 3, 'D': 4, 'E': 5 } valid_grades = ['A', 'B', 'C', 'D', 'E'] # Pass grades: A-E pass_grades = ['A', 'B', 'C', 'D', 'E'] else: # Default fallback (A-Level style) gpa_weights = { 'A': 1, 'B': 2, 'C': 3, 'D': 4, 'E': 5, 'S': 6, 'F': 7 } valid_grades = ['A', 'B', 'C', 'D', 'E', 'S', 'F'] pass_grades = ['A', 'B', 'C', 'D', 'E'] print(f"Academic Level ID: {academic_level_id}") print(f"GPA Weights: {gpa_weights}") print(f"Valid Grades: {valid_grades}") # Get raw subject data subject_data = ( ProcessedSubjectScore.objects .filter(project_id=project_id) .values( "subject__subject_code", "subject__subject_name_eng", ) .annotate( sat=Count("id"), A=Count("id", filter=Q(grade="A")), B=Count("id", filter=Q(grade="B")), C=Count("id", filter=Q(grade="C")), D=Count("id", filter=Q(grade="D")), E=Count("id", filter=Q(grade="E")), S=Count("id", filter=Q(grade="S")), F=Count("id", filter=Q(grade="F")), ) .order_by("subject__subject_code") ) # Initialize lists for processed data and totals processed_subject_summary = [] subject_totals = { 'A': 0, 'B': 0, 'C': 0, 'D': 0, 'E': 0, 'S': 0, 'F': 0, 'sat': 0, 'pass_total': 0, 'fail_total': 0, 'gpa_sum': 0, 'subject_count': 0 } # Process each subject for subject in subject_data: # Get individual counts (handle None values) A = subject['A'] or 0 B = subject['B'] or 0 C = subject['C'] or 0 D = subject['D'] or 0 E = subject['E'] or 0 S = subject['S'] or 0 F = subject['F'] or 0 sat = subject['sat'] or 0 # Calculate pass total based on academic level pass_total = 0 for grade in pass_grades: if grade == 'A': pass_total += A elif grade == 'B': pass_total += B elif grade == 'C': pass_total += C elif grade == 'D': pass_total += D elif grade == 'E': pass_total += E # Calculate fail total (remaining students) fail_total = sat - pass_total # Calculate pass percentage pass_percent = round((pass_total / sat) * 100, 2) if sat > 0 else 0 # Calculate Subject GPA using weights for this academic level if sat > 0: gpa_numerator = 0 if 'A' in gpa_weights: gpa_numerator += A * gpa_weights['A'] if 'B' in gpa_weights: gpa_numerator += B * gpa_weights['B'] if 'C' in gpa_weights: gpa_numerator += C * gpa_weights['C'] if 'D' in gpa_weights: gpa_numerator += D * gpa_weights['D'] if 'E' in gpa_weights: gpa_numerator += E * gpa_weights['E'] if 'S' in gpa_weights: gpa_numerator += S * gpa_weights['S'] if 'F' in gpa_weights: gpa_numerator += F * gpa_weights['F'] subject_gpa = round(gpa_numerator / sat, 4) else: subject_gpa = 0.0000 # Determine performance class based on GPA if academic_level_id == 1: # O-Level if subject_gpa <= 2.0: performance_class = 'excellent' elif subject_gpa <= 3.5: performance_class = 'average' else: performance_class = 'poor' elif academic_level_id == 2: # A-Level if subject_gpa <= 2.5: performance_class = 'excellent' elif subject_gpa <= 4.0: performance_class = 'average' else: performance_class = 'poor' else: # Other levels if subject_gpa <= 2.0: performance_class = 'excellent' elif subject_gpa <= 3.5: performance_class = 'average' else: performance_class = 'poor' # Create enhanced subject dictionary processed_subject = { 'subject_code': subject['subject__subject_code'], 'subject_name': subject['subject__subject_name_eng'], 'sat': sat, 'A': A, 'B': B, 'C': C, 'D': D, 'E': E, 'S': S, 'F': F, 'pass_total': pass_total, 'pass_percent': pass_percent, 'fail_total': fail_total, 'subject_gpa': subject_gpa, 'gpa_display': f"{subject_gpa:.4f}", 'performance_class': performance_class } processed_subject_summary.append(processed_subject) # Update totals subject_totals['A'] += A subject_totals['B'] += B subject_totals['C'] += C subject_totals['D'] += D subject_totals['E'] += E subject_totals['S'] += S subject_totals['F'] += F subject_totals['sat'] += sat subject_totals['pass_total'] += pass_total subject_totals['fail_total'] += fail_total subject_totals['gpa_sum'] += subject_gpa subject_totals['subject_count'] += 1 # Calculate overall average GPA if subject_totals['subject_count'] > 0: subject_totals['avg_gpa'] = round(subject_totals['gpa_sum'] / subject_totals['subject_count'], 4) subject_totals['avg_gpa_display'] = f"{subject_totals['avg_gpa']:.4f}" else: subject_totals['avg_gpa'] = 0.0000 subject_totals['avg_gpa_display'] = "0.0000" # Calculate overall pass percentage if subject_totals['sat'] > 0: subject_totals['overall_pass_percent'] = round((subject_totals['pass_total'] / subject_totals['sat']) * 100, 2) else: subject_totals['overall_pass_percent'] = 0.00 # Get top 10 subjects by GPA (lowest GPA = best) top_subjects_by_gpa = sorted(processed_subject_summary, key=lambda x: x['subject_gpa'])[:10] print(f"Subject GPA Summary for Academic Level {academic_level_id}") print(f"Total subjects processed: {subject_totals['subject_count']}") print(f"Overall average GPA: {subject_totals['avg_gpa']}") from django.db.models import FloatField from django.db.models.functions import Cast # ====================================================== # TEST - BEST STUDENTS PER SUBJECT (Simple version) # ====================================================== subject_best_list = [] # Get first 5 subjects with scores test_subjects = ProcessedSubjectScore.objects.filter( project_id=project_id, percentage_score__isnull=False ).exclude(percentage_score='').exclude(percentage_score='X').values_list('subject__subject_code', flat=True).distinct()[:5] print(f"TEST: Found subjects: {list(test_subjects)}") for subject_code in test_subjects: subject = ExamSubject.objects.filter(subject_code=subject_code).first() subject_name = subject.subject_name_eng if subject else subject_code # Get first 3 scores scores = ProcessedSubjectScore.objects.filter( project_id=project_id, subject__subject_code=subject_code, percentage_score__isnull=False ).exclude(percentage_score='').exclude(percentage_score='X')[:3] student_list = [] for rank, score in enumerate(scores, 1): student_list.append({ 'rank': rank, 'student_number': score.student.student_number, 'full_name': score.student.full_name, 'sex': score.student.sex, 'school_name': score.student.school.name_short or score.student.school.name, 'district': score.student.school.district.name if score.student.school.district else '', 'school_ownership': score.student.school.ownership, 'percentage_score': str(score.percentage_score), 'grade': score.grade, 'points': score.points, }) if student_list: subject_best_list.append({ 'code': subject_code, 'name': subject_name, 'rows': student_list }) print(f"TEST: subject_best_list length: {len(subject_best_list)}") import logging logger = logging.getLogger(__name__) # Then use: logger.debug(f"Subjects with scores: {subjects_with_scores.count()}") logger.debug(f"subject_best_list length: {len(subject_best_list)}") return render(request, "exams/combined_report.html", { 'overall_table': overall_table, 'subject_gpa': subject_gpa, 'division_gpa': division_gpa, 'overall_gpa': overall_gpa, 'academic_level_id': academic_level_id, 'registered_f': registered_f, 'registered_m': registered_m, 'registered_total': registered_total, 'sat_f': sat_f, 'sat_m': sat_m, 'sat_total': sat_total, 'absent_f': absent_f, 'absent_m': absent_m, 'absent_total': absent_total, 'combination_performance': combination_performance, # School rankings 'best_10_all': best_10_all, 'best_10_gvt': best_10_gvt, 'best_10_nongvt': best_10_nongvt, 'best_40_std_andabove': best_40_std_andabove, # Schools with 40+ candidates 'best_40_less': best_40_less, # Schools with <40 candidates 'best_gvt_students': best_gvt_students, 'best_nongvt_students': best_nongvt_students, # Subject performance data 'processed_subject_summary': processed_subject_summary, 'subject_totals': subject_totals, 'top_subjects_by_gpa': top_subjects_by_gpa, 'subject_best_list': subject_best_list, "region_name": region_name, "project": project, "pass_pct": pass_pct, "fail_pct": fail_pct, "gender_div": gender_div, "school_ranking": school_ranking, "best_students": best_students, "best_girls": best_girls, "best_boys": best_boys, }) from django.shortcuts import render, get_object_or_404 from django.db.models import Count, Q, Sum, F, FloatField, Case, When, Value from django.db.models.functions import Coalesce from django.http import HttpResponse import openpyxl from openpyxl.styles import Font, Alignment, PatternFill, Border, Side import csv from io import BytesIO from .models import ( JointExamProject, School, Region, District, StudentFinalResult, ProjectStudent, ExamSchoolResult, ExamCombination, ProcessedSubjectScore ) # exams/views_reports_combined.py # Add this import at the top if not already there from .views_helpers import ( build_school_rank_data, compute_district_school_rank, compute_district_school_rank_ext, compute_region_school_rank, compute_region_school_rank_ext, ) def project_school_rank_router(request, project_code): """Route to appropriate ranking view based on selection using project_code""" from .models import JointExamProject from django.shortcuts import get_object_or_404 from django.http import Http404 from django.urls import reverse print(f"DEBUG: Received project_code = '{project_code}'") # Get project by code project = get_object_or_404(JointExamProject, project_code=project_code) project_id = project.id year = project.year print(f"DEBUG: Found project: {project.name} (ID: {project_id})") # Get exam_type_id (needed for some functions) from .models import ExamType exam_type = ExamType.objects.filter(is_active=True).first() exam_type_id = exam_type.id if exam_type else None # Get parameters from the modal form category = request.GET.get('category', 'all') scope = request.GET.get('scope', 'overall') layout = request.GET.get('layout', 'normal') format_type = request.GET.get('format', 'html') region_id = request.GET.get('region_id') district_id = request.GET.get('district_id') print(f"DEBUG: scope={scope}, format={format_type}, category={category}") # Route based on scope if scope == 'region' and region_id: print(f"DEBUG: Routing to region_school_ranking with region_id={region_id}") if format_type == 'excel': return school_ranking_excel(request, project_id, scope='region', region_id=region_id, category=category, layout=layout) else: return region_school_ranking(request, project_id, region_id, category, layout) elif scope == 'district' and district_id: print(f"DEBUG: Routing to district_school_ranking with district_id={district_id}") if format_type == 'excel': return school_ranking_excel(request, project_id, scope='district', district_id=district_id, category=category, layout=layout) else: return district_school_ranking(request, project_id, district_id, category, layout) else: # overall print(f"DEBUG: Routing to overall_school_ranking") if format_type == 'excel': return school_ranking_excel(request, project_id, scope='overall', category=category, layout=layout) else: return overall_school_ranking(request, project_id, category, layout) def export_school_rank_excel(rows): """Export school ranking to Excel""" from openpyxl import Workbook from openpyxl.styles import Font, Alignment, PatternFill from django.http import HttpResponse wb = Workbook() ws = wb.active ws.title = "School Ranking" # Define styles header_fill = PatternFill(start_color="1a3e6f", end_color="1a3e6f", fill_type="solid") header_font = Font(color="FFFFFF", bold=True) center_alignment = Alignment(horizontal="center", vertical="center") # Headers headers = ['#', 'CODE', 'NAME', 'DISTRICT', 'REGION', 'I', 'II', 'III', 'IV', '0', '#I-IV', '%I-IV', 'GPA'] for col, header in enumerate(headers, 1): cell = ws.cell(row=1, column=col) cell.value = header cell.fill = header_fill cell.font = header_font cell.alignment = center_alignment # Data rows for row_num, r in enumerate(rows, 2): data = [ row_num - 1, # rank r.get('code', r.get('school_code', '')), r.get('name', ''), r.get('district', ''), r.get('region', ''), r.get('d1', r.get('i_t', 0)), r.get('d2', r.get('ii_t', 0)), r.get('d3', r.get('iii_t', 0)), r.get('d4', r.get('iv_t', 0)), r.get('d0', r.get('f_t', 0)), r.get('pass', r.get('pass_i_iv_t', 0)), r.get('pass_pct', r.get('pass_i_iv_pct', 0)), r.get('gpa', 0), ] for col, value in enumerate(data, 1): cell = ws.cell(row=row_num, column=col) cell.value = value cell.alignment = center_alignment # Auto-adjust column widths from openpyxl.utils import get_column_letter for col_idx in range(1, len(headers) + 1): max_len = len(headers[col_idx - 1]) col_letter = get_column_letter(col_idx) for row_idx in range(2, len(rows) + 2): cell_value = ws.cell(row=row_idx, column=col_idx).value if cell_value: max_len = max(max_len, len(str(cell_value))) ws.column_dimensions[col_letter].width = min(max_len + 2, 30) response = HttpResponse( content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = 'attachment; filename="school_ranking.xlsx"' wb.save(response) return response def export_school_rank_pdf(rows): """Export school ranking to PDF""" from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer from reportlab.lib.pagesizes import A4, landscape from reportlab.lib import colors from reportlab.lib.styles import getSampleStyleSheet from django.http import HttpResponse response = HttpResponse(content_type="application/pdf") response["Content-Disposition"] = 'attachment; filename="school_ranking.pdf"' doc = SimpleDocTemplate(response, pagesize=landscape(A4)) styles = getSampleStyleSheet() story = [] # Title story.append(Paragraph("SCHOOL RANKING", styles["Title"])) story.append(Spacer(1, 12)) # Table data data = [["#", "CODE", "SCHOOL", "DISTRICT", "REGION", "I", "II", "III", "IV", "0", "#I-IV", "%", "GPA"]] for idx, r in enumerate(rows[:50], 1): # Limit to 50 rows for PDF school_name = r.get('name', '') if len(school_name) > 25: school_name = school_name[:22] + "..." data.append([ str(idx), str(r.get('code', r.get('school_code', ''))), school_name, str(r.get('district', '')), str(r.get('region', '')), str(r.get('d1', r.get('i_t', 0))), str(r.get('d2', r.get('ii_t', 0))), str(r.get('d3', r.get('iii_t', 0))), str(r.get('d4', r.get('iv_t', 0))), str(r.get('d0', r.get('f_t', 0))), str(r.get('pass', r.get('pass_i_iv_t', 0))), f"{r.get('pass_pct', r.get('pass_i_iv_pct', 0))}%", f"{r.get('gpa', 0):.4f}", ]) table = Table(data, repeatRows=1) table.setStyle(TableStyle([ ("GRID", (0, 0), (-1, -1), 0.5, colors.black), ("BACKGROUND", (0, 0), (-1, 0), colors.lightgrey), ("ALIGN", (0, 0), (-1, -1), "CENTER"), ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"), ("FONTSIZE", (0, 0), (-1, -1), 8), ])) story.append(table) doc.build(story) return response # ====================================================== # HELPER FUNCTIONS # ====================================================== def get_school_ranking_data(project_id, scope='overall', region_id=None, district_id=None, category='all'): """Get school ranking data with all calculations""" schools_with_results = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).select_related('school', 'school__district', 'school__district__region') # Apply scope filters if scope == 'region' and region_id: schools_with_results = schools_with_results.filter( school__district__region_id=region_id ) elif scope == 'district' and district_id: schools_with_results = schools_with_results.filter( school__district_id=district_id ) # Apply category filter using the with_results field if category == 'above_40': schools_with_results = schools_with_results.filter( with_results__gte=30 ) elif category == 'below_40': schools_with_results = schools_with_results.filter( with_results__lt=30 ) # Order by GPA schools_with_results = schools_with_results.order_by('sch_gpa') # Check for any 'sat' in the query # Prepare the data rows rows = [] rank = 1 for school_overall in schools_with_results: school = school_overall.school # Get division counts for this school division_counts_query = StudentFinalResult.objects.filter( project_id=project_id, student__school=school ).values('division').annotate( count=Count('id') ) division_counts = list(division_counts_query) # Initialize division counters div_counts = {'I': 0, 'II': 0, 'III': 0, 'IV': 0, '0': 0} total_sat = 0 for item in division_counts: div = item['division'] # Use 'total' as the field name, not 'count' # count_value = item['total'] # Changed from 'count' to 'total' count_value = item.get('total', item.get('count', 0)) if div in div_counts: div_counts[div] = count_value # Only count valid divisions: I, II, III, IV, 0 if div in ['I', 'II', 'III', 'IV', '0']: # Changed condition total_sat += count_value # Calculate passes pass_i_iii = div_counts['I'] + div_counts['II'] + div_counts['III'] pass_i_iv = pass_i_iii + div_counts['IV'] # Calculate percentages pass_i_iii_pct = round((pass_i_iii / total_sat * 100), 2) if total_sat > 0 else 0 pass_i_iv_pct = round((pass_i_iv / total_sat * 100), 2) if total_sat > 0 else 0 # Get registered students count registered_query = ProjectStudent.objects.filter( project_id=project_id, school=school ).aggregate( f=Count('id', filter=Q(sex='F')), m=Count('id', filter=Q(sex='M')) ) # Get sat counts by gender sat_by_gender_query = StudentFinalResult.objects.filter( project_id=project_id, student__school=school ).exclude( division__iexact='ABS' ).values('student__sex').annotate( count=Count('id') ) sat_by_gender = list(sat_by_gender_query) sat_f = 0 sat_m = 0 for item in sat_by_gender: if item['student__sex'] == 'F': sat_f = item['count'] elif item['student__sex'] == 'M': sat_m = item['count'] # Get division counts by gender div_by_gender_query = StudentFinalResult.objects.filter( project_id=project_id, student__school=school ).values('student__sex', 'division').annotate( count=Count('id') ) div_by_gender = list(div_by_gender_query) div_f = {'I': 0, 'II': 0, 'III': 0, 'IV': 0, '0': 0} div_m = {'I': 0, 'II': 0, 'III': 0, 'IV': 0, '0': 0} for item in div_by_gender: sex = item['student__sex'] div = item['division'] if div in div_f: if sex == 'F': div_f[div] = item['count'] elif sex == 'M': div_m[div] = item['count'] # Determine status based on GPA gpa = school_overall.sch_gpa or 0 row_data = { 'no': rank, 'rank': rank, 'cno': school.school_code, 'code': school.school_code, 'name': school.name_short, 'region': school.district.region.name, 'district': school.district.name, 'ownership': school.ownership, # Use with_results from the model 'with_results': school_overall.with_results or 0, # Registered 'reg_f': registered_query['f'] or 0, 'reg_m': registered_query['m'] or 0, 'reg_t': (registered_query['f'] or 0) + (registered_query['m'] or 0), # Sat (students who actually sat) 'sat_f': sat_f, 'sat_m': sat_m, 'sat_t': sat_f + sat_m, # Division I 'i_f': div_f['I'], 'i_m': div_m['I'], 'i_t': div_f['I'] + div_m['I'], 'i_pct': round((div_f['I'] + div_m['I']) / total_sat * 100, 2) if total_sat > 0 else 0, # Division II 'ii_f': div_f['II'], 'ii_m': div_m['II'], 'ii_t': div_f['II'] + div_m['II'], 'ii_pct': round((div_f['II'] + div_m['II']) / total_sat * 100, 2) if total_sat > 0 else 0, # Division III 'iii_f': div_f['III'], 'iii_m': div_m['III'], 'iii_t': div_f['III'] + div_m['III'], 'iii_pct': round((div_f['III'] + div_m['III']) / total_sat * 100, 2) if total_sat > 0 else 0, # Pass I-III 'pass_i_iii_f': div_f['I'] + div_f['II'] + div_f['III'], 'pass_i_iii_m': div_m['I'] + div_m['II'] + div_m['III'], 'pass_i_iii_t': pass_i_iii, 'pass_i_iii_pct': pass_i_iii_pct, # Division IV 'iv_f': div_f['IV'], 'iv_m': div_m['IV'], 'iv_t': div_f['IV'] + div_m['IV'], 'iv_pct': round((div_f['IV'] + div_m['IV']) / total_sat * 100, 2) if total_sat > 0 else 0, # Failed (Division 0) 'f_f': div_f['0'], 'f_m': div_m['0'], 'f_t': div_f['0'] + div_m['0'], 'f_pct': round((div_f['0'] + div_m['0']) / total_sat * 100, 2) if total_sat > 0 else 0, # Pass I-IV 'pass_i_iv_f': div_f['I'] + div_f['II'] + div_f['III'] + div_f['IV'], 'pass_i_iv_m': div_m['I'] + div_m['II'] + div_m['III'] + div_m['IV'], 'pass_i_iv_t': pass_i_iv, 'pass_i_iv_pct': pass_i_iv_pct, # GPA and derived fields 'gpa': round(school_overall.sch_gpa, 4), 'grade': school_overall.sch_gpa_grade, 'status': school_overall.sch_gpa_status, 'position': rank, 'd1': div_counts['I'], 'd2': div_counts['II'], 'd3': div_counts['III'], 'd4': div_counts['IV'], 'd0': div_counts['0'], 'pass': pass_i_iii, 'pass_pct': pass_i_iii_pct, } rows.append(row_data) rank += 1 return rows # exams/views_reports_combined.py # exams/views_reports_combined.py def overall_school_ranking(request, project_id, category='all', layout='normal'): """ Overall school ranking view using project_id (called from router) """ from .models import JointExamProject from django.shortcuts import get_object_or_404 # Get project by id project = get_object_or_404(JointExamProject, id=project_id) # Get ranking data rows = get_school_ranking_data(project_id, scope='overall', category=category) # Choose template based on layout if layout == 'extended': template = 'exams/school_ranking_extended.html' else: template = 'exams/school_ranking_normal.html' context = { 'project': project, 'rows': rows, 'category': category, 'layout': layout, 'title': f"Overall School Ranking - {project.name} {project.year}", } return render(request, template, context) def region_school_ranking(request, project_id, region_id, category='all', layout='normal'): """ Region-wise school ranking view using project_id (called from router) """ from .models import JointExamProject, Region from django.shortcuts import get_object_or_404 # Get project by id project = get_object_or_404(JointExamProject, id=project_id) region = get_object_or_404(Region, id=region_id) # Get ranking data rows = get_school_ranking_data(project_id, scope='region', region_id=region_id, category=category) # Choose template based on layout if layout == 'extended': template = 'exams/school_ranking_extended.html' else: template = 'exams/school_ranking_normal.html' context = { 'project': project, 'region': region, 'rows': rows, 'category': category, 'layout': layout, 'title': f"Region School Ranking - {region.name} - {project.name} {project.year}", } return render(request, template, context) def district_school_ranking(request, project_id, district_id, category='all', layout='normal'): """ District-wise school ranking view using project_id (called from router) """ from .models import JointExamProject, District from django.shortcuts import get_object_or_404 # Get project by id project = get_object_or_404(JointExamProject, id=project_id) district = get_object_or_404(District, id=district_id) # Get ranking data rows = get_school_ranking_data(project_id, scope='district', district_id=district_id, category=category) # Choose template based on layout if layout == 'extended': template = 'exams/school_ranking_extended.html' else: template = 'exams/school_ranking_normal.html' context = { 'project': project, 'district': district, 'rows': rows, 'category': category, 'layout': layout, 'title': f"District School Ranking - {district.name} - {project.name} {project.year}", } return render(request, template, context) def school_ranking_excel(request, project_id, scope='overall', region_id=None, district_id=None, category='all', layout='normal'): """ Export school ranking to Excel using project_id """ from .models import JointExamProject, Region, District from django.shortcuts import get_object_or_404 from django.http import HttpResponse import openpyxl from openpyxl.styles import Font, Alignment, PatternFill from openpyxl.utils import get_column_letter # Get project by id project = get_object_or_404(JointExamProject, id=project_id) # Get ranking data rows = get_school_ranking_data(project_id, scope, region_id, district_id, category) # Create workbook wb = openpyxl.Workbook() ws = wb.active # Define styles header_fill = PatternFill(start_color="1a3e6f", end_color="1a3e6f", fill_type="solid") header_font = Font(color="FFFFFF", bold=True) center_alignment = Alignment(horizontal="center", vertical="center") # Set title based on scope if scope == 'region' and region_id: region = Region.objects.get(id=region_id) title = f"Region School Ranking - {region.name} - {project.name} {project.year}" elif scope == 'district' and district_id: district = District.objects.get(id=district_id) title = f"District School Ranking - {district.name} - {project.name} {project.year}" else: title = f"Overall School Ranking - {project.name} {project.year}" ws.merge_cells('A1:P1') ws['A1'] = title ws['A1'].font = Font(bold=True, size=14) ws['A1'].alignment = center_alignment if layout == 'extended': # Extended layout headers headers = [ '#', 'CODE', 'SCHOOL', 'DISTRICT', 'REGION', 'OWNERSHIP', 'REG_F', 'REG_M', 'REG_T', 'SAT_F', 'SAT_M', 'SAT_T', 'I_F', 'I_M', 'I_T', 'I_%', 'II_F', 'II_M', 'II_T', 'II_%', 'III_F', 'III_M', 'III_T', 'III_%', 'PASS_I-III_F', 'PASS_I-III_M', 'PASS_I-III_T', 'PASS_I-III_%', 'IV_F', 'IV_M', 'IV_T', 'IV_%', 'FAIL_F', 'FAIL_M', 'FAIL_T', 'FAIL_%', 'PASS_I-IV_F', 'PASS_I-IV_M', 'PASS_I-IV_T', 'PASS_I-IV_%', 'GPA', 'GRADE', 'STATUS', 'POS' ] for col, header in enumerate(headers, 1): cell = ws.cell(row=3, column=col) cell.value = header cell.fill = header_fill cell.font = header_font cell.alignment = center_alignment # Add data rows for row_num, r in enumerate(rows, 4): data = [ r.get('no', ''), r.get('cno', ''), r.get('name', ''), r.get('district', ''), r.get('region', ''), r.get('ownership', ''), r.get('reg_f', 0), r.get('reg_m', 0), r.get('reg_t', 0), r.get('sat_f', 0), r.get('sat_m', 0), r.get('sat_t', 0), r.get('i_f', 0), r.get('i_m', 0), r.get('i_t', 0), r.get('i_pct', 0), r.get('ii_f', 0), r.get('ii_m', 0), r.get('ii_t', 0), r.get('ii_pct', 0), r.get('iii_f', 0), r.get('iii_m', 0), r.get('iii_t', 0), r.get('iii_pct', 0), r.get('pass_i_iii_f', 0), r.get('pass_i_iii_m', 0), r.get('pass_i_iii_t', 0), r.get('pass_i_iii_pct', 0), r.get('iv_f', 0), r.get('iv_m', 0), r.get('iv_t', 0), r.get('iv_pct', 0), r.get('f_f', 0), r.get('f_m', 0), r.get('f_t', 0), r.get('f_pct', 0), r.get('pass_i_iv_f', 0), r.get('pass_i_iv_m', 0), r.get('pass_i_iv_t', 0), r.get('pass_i_iv_pct', 0), r.get('gpa', 0), r.get('grade', '-'), r.get('status', '-'), r.get('position', '') ] for col, value in enumerate(data, 1): ws.cell(row=row_num, column=col, value=value) else: # Normal layout headers headers = ['#', 'CODE', 'NAME', 'DISTRICT', 'REGION', 'I', 'II', 'III', 'IV', '0', '#I-IV', '%I-IV', 'GPA'] for col, header in enumerate(headers, 1): cell = ws.cell(row=3, column=col) cell.value = header cell.fill = header_fill cell.font = header_font cell.alignment = center_alignment # Add data rows for row_num, r in enumerate(rows, 4): data = [ r.get('rank', row_num - 3), r.get('code', r.get('cno', '')), r.get('name', ''), r.get('district', ''), r.get('region', ''), r.get('d1', r.get('i_t', 0)), r.get('d2', r.get('ii_t', 0)), r.get('d3', r.get('iii_t', 0)), r.get('d4', r.get('iv_t', 0)), r.get('d0', r.get('f_t', 0)), r.get('pass', r.get('pass_i_iv_t', 0)), r.get('pass_pct', r.get('pass_i_iv_pct', 0)), r.get('gpa', 0) ] for col, value in enumerate(data, 1): ws.cell(row=row_num, column=col, value=value) # Auto-adjust column widths for col_idx in range(1, ws.max_column + 1): max_len = 0 col_letter = get_column_letter(col_idx) for cell in ws[col_letter]: if cell.value: max_len = max(max_len, len(str(cell.value))) ws.column_dimensions[col_letter].width = min(max_len + 2, 30) # Create response if scope == 'region' and region_id: region = Region.objects.get(id=region_id) filename = f"{region.name}_school_ranking_{project.year}.xlsx" elif scope == 'district' and district_id: district = District.objects.get(id=district_id) filename = f"{district.name}_school_ranking_{project.year}.xlsx" else: filename = f"overall_school_ranking_{project.year}.xlsx" response = HttpResponse( content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = f'attachment; filename="{filename}"' wb.save(response) return response def table_for_reports_writing(request, project_id): """ Generate simple tables for report writing with Word export capability Extract all data from the actual models """ project = get_object_or_404(JointExamProject, id=project_id) academic_level_id = project.academic_level_id # ====================================================== # TABLE 1: REGIONAL SUMMARY (Jedwali Na. 1(a)) # ====================================================== # Get all regions that have schools with valid GPA in this project regions_with_schools = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).values_list('school__district__region', flat=True).distinct() regions = Region.objects.filter(id__in=regions_with_schools).order_by('name') region_data = [] total_schools = 0 total_reg_f = 0 total_reg_m = 0 total_reg = 0 total_sat_f = 0 total_sat_m = 0 total_sat = 0 total_abs_f = 0 total_abs_m = 0 total_abs = 0 for region in regions: # Schools with valid GPA in this region (from ExamSchoolResult) schools = ExamSchoolResult.objects.filter( project_id=project_id, school__district__region=region, sch_gpa__isnull=False ).count() # Get all students in this region students = ProjectStudent.objects.filter( project_id=project_id, school__district__region=region ) # Registered students count reg_f = students.filter(sex='F').count() reg_m = students.filter(sex='M').count() reg_t = reg_f + reg_m # Students who sat (have results and not ABS) - using StudentFinalResult sat_results = StudentFinalResult.objects.filter( project_id=project_id, student__school__district__region=region ).filter(division__in=['I', 'II', 'III', 'IV', '0']) sat_f = sat_results.filter(student__sex='F').count() sat_m = sat_results.filter(student__sex='M').count() sat_t = sat_f + sat_m # Absentees (registered - sat) abs_f = reg_f - sat_f abs_m = reg_m - sat_m abs_t = abs_f + abs_m # Calculate percentages (avoid division by zero) sat_pct = round((sat_t / reg_t * 100), 2) if reg_t > 0 else 0 abs_pct = round((abs_t / reg_t * 100), 2) if reg_t > 0 else 0 # Only add region if it has data if reg_t > 0 or sat_t > 0: region_data.append({ 'no': len(region_data) + 1, 'region': region.name, 'schools': schools, 'reg_f': reg_f, 'reg_m': reg_m, 'reg_t': reg_t, 'sat_f': sat_f, 'sat_m': sat_m, 'sat_t': sat_t, 'sat_pct': sat_pct, 'abs_f': abs_f, 'abs_m': abs_m, 'abs_t': abs_t, 'abs_pct': abs_pct, }) # Update totals total_schools += schools total_reg_f += reg_f total_reg_m += reg_m total_reg += reg_t total_sat_f += sat_f total_sat_m += sat_m total_sat += sat_t total_abs_f += abs_f total_abs_m += abs_m total_abs += abs_t # Calculate overall percentages for totals overall_sat_pct = round((total_sat / total_reg * 100), 2) if total_reg > 0 else 0 overall_abs_pct = round((total_abs / total_reg * 100), 2) if total_reg > 0 else 0 # ====================================================== # TABLE 2: DISTRICT/HALMASHAURI SUMMARY (Jedwali Na. 1(b)) # ====================================================== districts_with_schools = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).values_list('school__district', flat=True).distinct() districts = District.objects.filter(id__in=districts_with_schools).order_by('region__name', 'name') district_data_registration = [] for district in districts: # Schools with valid GPA in this district schools = ExamSchoolResult.objects.filter( project_id=project_id, school__district=district, sch_gpa__isnull=False ).count() # Students in this district students = ProjectStudent.objects.filter( project_id=project_id, school__district=district ) reg_f = students.filter(sex='F').count() reg_m = students.filter(sex='M').count() reg_t = reg_f + reg_m # Students who sat sat_results = StudentFinalResult.objects.filter( project_id=project_id, student__school__district=district ).filter(division__in=['I', 'II', 'III', 'IV', '0']) sat_f = sat_results.filter(student__sex='F').count() sat_m = sat_results.filter(student__sex='M').count() sat_t = sat_f + sat_m sat_pct = round((sat_t / reg_t * 100), 2) if reg_t > 0 else 0 abs_f = reg_f - sat_f abs_m = reg_m - sat_m abs_t = abs_f + abs_m abs_pct = round((abs_t / reg_t * 100), 2) if reg_t > 0 else 0 district_data_registration.append({ 'no': len(district_data_registration) + 1, 'district': district.name, 'region': district.region.name, 'schools': schools, 'reg_f': reg_f, 'reg_m': reg_m, 'reg_t': reg_t, 'sat_f': sat_f, 'sat_m': sat_m, 'sat_t': sat_t, 'sat_pct': sat_pct, 'abs_f': abs_f, 'abs_m': abs_m, 'abs_t': abs_t, 'abs_pct': abs_pct, }) # ====================================================== # TABLE 3: REGIONAL DIVISION PERFORMANCE (Jedwali Na. 2(a)) # ====================================================== div_regions = [] total_all_sat = 0 # Keep this separate total_i = 0 total_ii = 0 total_iii = 0 total_iv = 0 total_o = 0 for region in regions: results = StudentFinalResult.objects.filter( project_id=project_id, student__school__district__region=region ).filter(division__in=['I', 'II', 'III', 'IV', '0']) region_sat = results.count() div_i = results.filter(division='I').count() div_ii = results.filter(division='II').count() div_iii = results.filter(division='III').count() div_iv = results.filter(division='IV').count() div_o = results.filter(division='0').count() pass_i_iii = div_i + div_ii + div_iii pass_i_iv = pass_i_iii + div_iv # Get region-specific grade counts for subject GPA region_grade_counts = { 'A': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='A' ).count(), 'B': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='B' ).count(), 'C': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='C' ).count(), 'D': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='D' ).count(), 'E': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='E' ).count(), 'S': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='S' ).count(), 'F': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='F' ).count(), } # Calculate region GPA using calculate_gpa_from_counts region_gpa = calculate_gpa_from_counts( div_i, div_ii, div_iii, div_iv, div_o, region_sat, academic_level_id, region_grade_counts ) div_regions.append({ 'region': region.name, 'sat': region_sat, 'i': div_i, 'i_pct': round(div_i / region_sat * 100, 2) if region_sat > 0 else 0, 'ii': div_ii, 'ii_pct': round(div_ii / region_sat * 100, 2) if region_sat > 0 else 0, 'iii': div_iii, 'iii_pct': round(div_iii / region_sat * 100, 2) if region_sat > 0 else 0, 'pass_i_iii': pass_i_iii, 'pass_i_iii_pct': round(pass_i_iii / region_sat * 100, 2) if region_sat > 0 else 0, 'iv': div_iv, 'iv_pct': round(div_iv / region_sat * 100, 2) if region_sat > 0 else 0, 'pass_i_iv': pass_i_iv, 'pass_i_iv_pct': round(pass_i_iv / region_sat * 100, 2) if region_sat > 0 else 0, 'o': div_o, 'o_pct': round(div_o / region_sat * 100, 2) if region_sat > 0 else 0, 'gpa': region_gpa, }) total_all_sat += region_sat total_i += div_i total_ii += div_ii total_iii += div_iii total_iv += div_iv total_o += div_o # Calculate overall grade counts for the entire project overall_grade_counts = { 'A': ProcessedSubjectScore.objects.filter( project_id=project_id, grade='A' ).count(), 'B': ProcessedSubjectScore.objects.filter( project_id=project_id, grade='B' ).count(), 'C': ProcessedSubjectScore.objects.filter( project_id=project_id, grade='C' ).count(), 'D': ProcessedSubjectScore.objects.filter( project_id=project_id, grade='D' ).count(), 'E': ProcessedSubjectScore.objects.filter( project_id=project_id, grade='E' ).count(), 'S': ProcessedSubjectScore.objects.filter( project_id=project_id, grade='S' ).count(), 'F': ProcessedSubjectScore.objects.filter( project_id=project_id, grade='F' ).count(), } # Calculate overall GPA using calculate_gpa_from_counts overall_gpa_total_reg = calculate_gpa_from_counts( total_i, total_ii, total_iii, total_iv, total_o, total_all_sat, academic_level_id, overall_grade_counts ) # Alternative: Use calculate_gpa_dynamic for overall GPA (if you prefer) # overall_gpa_total = calculate_gpa_dynamic( # scope_type='overall', # scope_id=None, # project_id=project_id, # academic_level_id=academic_level_id # ) print(f"Overall GPA Total: {overall_gpa_total_reg}") # Debug print # ====================================================== # TABLE 4: DIVISION BY GENDER (Jedwali Na. 2(b)) # ====================================================== # Get all results with gender gender_results = StudentFinalResult.objects.filter( project_id=project_id ).exclude( division__iexact='ABS' ).select_related('student') # Initialize with all required keys gender_div = { 'F': {'i': 0, 'ii': 0, 'iii': 0, 'iv': 0, 'o': 0, 'sat': 0}, 'M': {'i': 0, 'ii': 0, 'iii': 0, 'iv': 0, 'o': 0, 'sat': 0} } # Define valid divisions mapping div_map = { 'I': 'i', 'II': 'ii', 'III': 'iii', 'IV': 'iv', '0': 'o' } for r in gender_results: sex = r.student.sex div = r.division if sex in gender_div and div in div_map: key = div_map[div] gender_div[sex][key] += 1 gender_div[sex]['sat'] += 1 # Also get totals by region for the gender table region_gender_data = [] for region in regions: region_results = gender_results.filter(student__school__district__region=region) sat_m = region_results.filter(student__sex='M').count() sat_f = region_results.filter(student__sex='F').count() # Calculate division counts by gender i_m = region_results.filter(division='I', student__sex='M').count() i_f = region_results.filter(division='I', student__sex='F').count() ii_m = region_results.filter(division='II', student__sex='M').count() ii_f = region_results.filter(division='II', student__sex='F').count() iii_m = region_results.filter(division='III', student__sex='M').count() iii_f = region_results.filter(division='III', student__sex='F').count() iv_m = region_results.filter(division='IV', student__sex='M').count() iv_f = region_results.filter(division='IV', student__sex='F').count() o_m = region_results.filter(division='0', student__sex='M').count() o_f = region_results.filter(division='0', student__sex='F').count() # Calculate GPA for this region using region-specific grade counts div_i = i_m + i_f div_ii = ii_m + ii_f div_iii = iii_m + iii_f div_iv = iv_m + iv_f div_o = o_m + o_f total = sat_m + sat_f # Get region-specific grade counts - FIXED HERE region_grade_counts = { 'A': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='A' ).count(), 'B': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='B' ).count(), 'C': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='C' ).count(), 'D': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='D' ).count(), 'E': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='E' ).count(), 'S': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='S' ).count(), 'F': ProcessedSubjectScore.objects.filter( project_id=project_id, student__school__district__region=region, grade='F' ).count(), } overall_gpa = calculate_gpa_from_counts( div_i, div_ii, div_iii, div_iv, div_o, total, academic_level_id, region_grade_counts # Use region-specific counts ) # Calculate overall GPA for the entire project overall_gpa_total = calculate_gpa_dynamic( scope_type='overall', scope_id=None, project_id=project_id, academic_level_id=academic_level_id ) print(f"Overall GPA Total: {overall_gpa_total}") # Debug print region_gender_data.append({ 'region': region.name, 'sat_m': sat_m, 'sat_f': sat_f, 'sat': total, 'i_m': i_m, 'i_f': i_f, 'ii_m': ii_m, 'ii_f': ii_f, 'iii_m': iii_m, 'iii_f': iii_f, 'iv_m': iv_m, 'iv_f': iv_f, 'o_m': o_m, 'o_f': o_f, 'gpa': overall_gpa, 'overall_gpa_total': overall_gpa_total, }) # ====================================================== # TABLE 5: DISTRICT DIVISION PERFORMANCE # ====================================================== district_div_data = [] for district in districts: results = StudentFinalResult.objects.filter( project_id=project_id, student__school__district=district ).filter(division__in=['I', 'II', 'III', 'IV', '0']) total_sat = results.count() if total_sat == 0: continue div_i = results.filter(division='I').count() div_ii = results.filter(division='II').count() div_iii = results.filter(division='III').count() div_iv = results.filter(division='IV').count() div_o = results.filter(division='0').count() pass_i_iii = div_i + div_ii + div_iii pass_i_iv = pass_i_iii + div_iv # Calculate GPA using the same formula grade_counts_total = { 'A': ProcessedSubjectScore.objects.filter(project_id=project_id, grade='A').count(), 'B': ProcessedSubjectScore.objects.filter(project_id=project_id, grade='B').count(), 'C': ProcessedSubjectScore.objects.filter(project_id=project_id, grade='C').count(), 'D': ProcessedSubjectScore.objects.filter(project_id=project_id, grade='D').count(), 'E': ProcessedSubjectScore.objects.filter(project_id=project_id, grade='E').count(), 'S': ProcessedSubjectScore.objects.filter(project_id=project_id, grade='S').count(), 'F': ProcessedSubjectScore.objects.filter(project_id=project_id, grade='F').count(), } overall_gpa = calculate_gpa_from_counts( div_i, div_ii, div_iii, div_iv, div_o, total_sat, academic_level_id, grade_counts_total ) district_div_data.append({ 'no': len(district_div_data) + 1, 'district': district.name, 'region': district.region.name, 'sat': total_sat, 'i': div_i, 'i_pct': round(div_i / total_sat * 100, 2), 'ii': div_ii, 'ii_pct': round(div_ii / total_sat * 100, 2), 'iii': div_iii, 'iii_pct': round(div_iii / total_sat * 100, 2), 'pass_i_iii': pass_i_iii, 'pass_i_iii_pct': round(pass_i_iii / total_sat * 100, 2), 'iv': div_iv, 'iv_pct': round(div_iv / total_sat * 100, 2), 'pass_i_iv': pass_i_iv, 'pass_i_iv_pct': round(pass_i_iv / total_sat * 100, 2), 'o': div_o, 'o_pct': round(div_o / total_sat * 100, 2), 'gpa': overall_gpa, }) # Sort by GPA (best first - lower GPA is better) district_div_data.sort(key=lambda x: x['gpa']) # ====================================================== # COMBINATION TABLES - DYNAMIC GENERATION FROM combination_category # Tables 6-9: All combination categories automatically grouped # ====================================================== # Get all student results with combinations student_results = StudentFinalResult.objects.filter( project_id=project_id ).exclude( division__iexact='ABS' ).select_related('student__combination') # Get all distinct combination categories from the database all_categories = ExamCombination.objects.filter( projectstudent__project_id=project_id ).values_list('combination_category', flat=True).distinct().exclude(combination_category='NULL').exclude(combination_category='') # Define mapping for division to our keys div_map = {'I': 'i', 'II': 'ii', 'III': 'iii', 'IV': 'iv', '0': 'o'} # Prepare data structure for all combination tables combination_tables = [] # First, create a table for each unique combination category for category in all_categories: if not category or category == 'NULL': continue # Get all combinations in this category combinations_in_category = ExamCombination.objects.filter( combination_category=category, projectstudent__project_id=project_id ).distinct().order_by('code') table_data = [] category_total = {'total': 0, 'i': 0, 'ii': 0, 'iii': 0, 'iv': 0, 'o': 0} # For each combination in this category, calculate division counts for combo in combinations_in_category: combo_results = student_results.filter(student__combination=combo) combo_data = { 'code': combo.code, 'name': combo.name, 'total': combo_results.count(), 'i': 0, 'ii': 0, 'iii': 0, 'iv': 0, 'o': 0, } # Count divisions for this combination for result in combo_results: div = result.division if div in div_map: key = div_map[div] combo_data[key] += 1 category_total[key] += 1 combo_data['total'] = combo_results.count() category_total['total'] += combo_data['total'] # Calculate percentages if combo_data['total'] > 0: combo_data['i_pct'] = round(combo_data['i'] / combo_data['total'] * 100, 2) combo_data['ii_pct'] = round(combo_data['ii'] / combo_data['total'] * 100, 2) combo_data['iii_pct'] = round(combo_data['iii'] / combo_data['total'] * 100, 2) combo_data['iv_pct'] = round(combo_data['iv'] / combo_data['total'] * 100, 2) combo_data['o_pct'] = round(combo_data['o'] / combo_data['total'] * 100, 2) else: combo_data['i_pct'] = combo_data['ii_pct'] = combo_data['iii_pct'] = combo_data['iv_pct'] = combo_data['o_pct'] = 0 table_data.append(combo_data) # Calculate category totals percentages if category_total['total'] > 0: category_total['i_pct'] = round(category_total['i'] / category_total['total'] * 100, 2) category_total['ii_pct'] = round(category_total['ii'] / category_total['total'] * 100, 2) category_total['iii_pct'] = round(category_total['iii'] / category_total['total'] * 100, 2) category_total['iv_pct'] = round(category_total['iv'] / category_total['total'] * 100, 2) category_total['o_pct'] = round(category_total['o'] / category_total['total'] * 100, 2) else: category_total['i_pct'] = category_total['ii_pct'] = category_total['iii_pct'] = category_total['iv_pct'] = category_total['o_pct'] = 0 # Determine table number based on category table_number = "3(a)" if category.upper() in ['SAYANSI', 'SCIENCE']: table_number = "3(b)" elif category.upper() in ['SANAA', 'ARTS', 'LUGHA', 'LANGUAGE']: table_number = "3(c)" elif category.upper() in ['BIASHARA', 'BUSINESS', 'UCHUMI', 'ECONOMICS']: table_number = "3(d)" combination_tables.append({ 'table_number': table_number, 'category': category, 'title': f'Jedwali Na. {table_number}: Ufaulu wa Watahiniwa katika {category}', 'data': table_data, 'total': category_total, }) # Sort tables by category name for consistent display combination_tables.sort(key=lambda x: x['category']) # ====================================================== # TABLE 1-5: (Previous tables - keep as is) # ====================================================== # ... [Keep all your existing code for Tables 1-5] ... # ====================================================== # TABLE 6: AGGREGATED COMBINATION CATEGORY PERFORMANCE # ====================================================== # Get all student results with combinations student_results = StudentFinalResult.objects.filter( project_id=project_id ).exclude( division__iexact='ABS' ).select_related('student__combination') # Define mapping for division to our keys div_map = {'I': 'i', 'II': 'ii', 'III': 'iii', 'IV': 'iv', '0': 'o'} # Initialize category performance data category_performance = {} overall_totals = { 'total': 0, 'i': 0, 'ii': 0, 'iii': 0, 'iv': 0, 'o': 0, 'i_pct': 0, 'ii_pct': 0, 'iii_pct': 0, 'iv_pct': 0, 'o_pct': 0 } # Process all student results for result in student_results: if not result.student.combination: continue category = result.student.combination.combination_category if not category or category == 'NULL': category = 'Other' div = result.division if div not in div_map: continue div_key = div_map[div] # Initialize category if not exists if category not in category_performance: category_performance[category] = { 'category': category, 'total': 0, 'i': 0, 'ii': 0, 'iii': 0, 'iv': 0, 'o': 0, 'i_pct': 0, 'ii_pct': 0, 'iii_pct': 0, 'iv_pct': 0, 'o_pct': 0 } # Update counts category_performance[category]['total'] += 1 category_performance[category][div_key] += 1 overall_totals['total'] += 1 overall_totals[div_key] += 1 # Calculate percentages for each category category_list = [] for cat_key, cat in category_performance.items(): total = cat['total'] cat['i_pct'] = round(cat['i'] / total * 100, 2) if total > 0 else 0 cat['ii_pct'] = round(cat['ii'] / total * 100, 2) if total > 0 else 0 cat['iii_pct'] = round(cat['iii'] / total * 100, 2) if total > 0 else 0 cat['iv_pct'] = round(cat['iv'] / total * 100, 2) if total > 0 else 0 cat['o_pct'] = round(cat['o'] / total * 100, 2) if total > 0 else 0 category_list.append(cat) # Calculate overall percentages total_all = overall_totals['total'] if total_all > 0: overall_totals['i_pct'] = round(overall_totals['i'] / total_all * 100, 2) overall_totals['ii_pct'] = round(overall_totals['ii'] / total_all * 100, 2) overall_totals['iii_pct'] = round(overall_totals['iii'] / total_all * 100, 2) overall_totals['iv_pct'] = round(overall_totals['iv'] / total_all * 100, 2) overall_totals['o_pct'] = round(overall_totals['o'] / total_all * 100, 2) # Sort categories by name category_list.sort(key=lambda x: x['category']) # ====================================================== # SCHOOL RANKING TABLES # ====================================================== # Get all schools with valid results school_results = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).select_related('school', 'school__district', 'school__district__region') school_data = [] for school in school_results: # Get division counts for this school using values() aggregation for better performance division_counts = StudentFinalResult.objects.filter( project_id=project_id, student__school=school.school ).filter(division__in=['I', 'II', 'III', 'IV', '0']).values('division').annotate(total=Count('id')) # Convert to dictionary for easy lookup div_dict = {item['division']: item['total'] for item in division_counts} # Get counts (default to 0 if not found) div_i = div_dict.get('I', 0) div_ii = div_dict.get('II', 0) div_iii = div_dict.get('III', 0) div_iv = div_dict.get('IV', 0) div_o = div_dict.get('0', 0) # Calculate total_sat = sum of all valid divisions (I, II, III, IV, 0) total_sat = div_i + div_ii + div_iii + div_iv + div_o # Calculate passes pass_i_iii = div_i + div_ii + div_iii pass_i_iv = pass_i_iii + div_iv # Calculate percentages using total_sat (not registered count) pass_i_iii_pct = round((pass_i_iii / total_sat * 100), 2) if total_sat > 0 else 0 pass_i_iv_pct = round((pass_i_iv / total_sat * 100), 2) if total_sat > 0 else 0 # Calculate division IV percentage div_iv_pct = round((div_iv / total_sat * 100), 2) if total_sat > 0 else 0 # Get registered students count (all students, including absent) registered = ProjectStudent.objects.filter( project_id=project_id, school=school.school ).count() school_data.append({ 'school': school.school, 'registered': registered, 'sat': total_sat, # This is now the sum of I, II, III, IV, 0 'i': div_i, 'ii': div_ii, 'iii': div_iii, 'iv': div_iv, 'o': div_o, 'div_iv_pct': div_iv_pct, # Added for template 'pass_i_iii': pass_i_iii, 'pass_i_iii_pct': pass_i_iii_pct, 'pass_i_iv': pass_i_iv, 'pass_i_iv_pct': pass_i_iv_pct, 'gpa': school.sch_gpa, 'ownership': school.school.ownership, 'region': school.school.district.region.name, 'district': school.school.district.name, }) # Sort by GPA (lower is better) school_data.sort(key=lambda x: x['gpa']) # ====================================================== # BEST 10 SCHOOLS OVERALL (30+ students) # ====================================================== best_schools_30plus = [s for s in school_data if s['sat'] >= 30][:10] for i, s in enumerate(best_schools_30plus, 1): s['rank'] = i # ====================================================== # BEST 10 SCHOOLS OVERALL (<30 students) # ====================================================== best_schools_under30 = [s for s in school_data if s['sat'] < 30][:10] for i, s in enumerate(best_schools_under30, 1): s['rank'] = i # ====================================================== # BEST 10 GOVERNMENT SCHOOLS (30+ students) # ====================================================== gov_schools = [s for s in school_data if s['ownership'] == 'GVT'] best_gov_30plus = [s for s in gov_schools if s['sat'] >= 30][:10] for i, s in enumerate(best_gov_30plus, 1): s['rank'] = i # ====================================================== # BEST 10 GOVERNMENT SCHOOLS (<30 students) # ====================================================== best_gov_under30 = [s for s in gov_schools if s['sat'] < 30][:10] for i, s in enumerate(best_gov_under30, 1): s['rank'] = i # ====================================================== # BEST 10 NON-GOVERNMENT SCHOOLS (30+ students) # ====================================================== nongov_schools = [s for s in school_data if s['ownership'] == 'NON-GVT'] best_nongov_30plus = [s for s in nongov_schools if s['sat'] >= 30][:10] for i, s in enumerate(best_nongov_30plus, 1): s['rank'] = i # ====================================================== # BEST 10 NON-GOVERNMENT SCHOOLS (<30 students) # ====================================================== best_nongov_under30 = [s for s in nongov_schools if s['sat'] < 30][:10] for i, s in enumerate(best_nongov_under30, 1): s['rank'] = i # ====================================================== # BOTTOM 10 SCHOOLS OVERALL (Worst GPA) # ====================================================== bottom_schools = school_data.copy() bottom_schools.sort(key=lambda x: x['gpa'], reverse=True) bottom_10_overall = bottom_schools[:10] for i, s in enumerate(bottom_10_overall, 1): s['rank'] = i # ====================================================== # SCHOOLS WITHOUT 100% PASS RATE (I-IV) # ====================================================== schools_not_100 = [s for s in school_data if s['pass_i_iv'] < s['sat'] or s['o'] > 0] schools_not_100.sort(key=lambda x: x['pass_i_iv_pct']) # Sort by pass percentage (lowest first) schools_not_100_list = schools_not_100[:15] # Take top 15 for i, s in enumerate(schools_not_100_list, 1): s['rank'] = i # ====================================================== # BEST 10 STUDENTS (Lowest Points, then Highest Average) # ====================================================== # Get all students with valid results (points >= 3, not ABS) students = StudentFinalResult.objects.filter( project_id=project_id, total_points__gte=3 ).exclude( division__iexact='ABS' ).select_related( 'student', 'student__school', 'student__school__district', 'student__school__district__region', 'student__combination' ) # Convert to list and sort: first by total_points (ascending), then by std_average (descending) student_list = [] for s in students: try: avg = float(s.std_average) except (ValueError, TypeError): avg = 0 student_list.append({ 'name': s.student.full_name, 'sex': s.student.sex, 'school': s.student.school.name, 'district': s.student.school.district.name, 'region': s.student.school.district.region.name, 'combination': s.student.combination.code if s.student.combination else '-', 'points': s.total_points, 'division': s.division, 'average': avg, }) # Sort: first by points (ascending), then by average (descending) student_list.sort(key=lambda x: (x['points'], -x['average'])) # Take top 10 best_students = student_list[:10] for i, s in enumerate(best_students, 1): s['rank'] = i # ====================================================== # PREPARE CONTEXT # ====================================================== # ====================================================== # BEST STUDENTS BY CATEGORY # ====================================================== # Get all students with valid results (points >= 3, not ABS) students_qs = StudentFinalResult.objects.filter( project_id=project_id, total_points__gte=3 ).exclude( division__iexact='ABS' ).select_related( 'student', 'student__school', 'student__school__district', 'student__school__district__region', 'student__combination' ) # Convert to list for sorting all_students = [] for s in students_qs: try: avg = float(s.std_average) except (ValueError, TypeError): avg = 0 all_students.append({ 'name': s.student.full_name, 'sex': s.student.sex, 'school': s.student.school.name, 'school_code': s.student.school.school_code, 'district': s.student.school.district.name, 'region': s.student.school.district.region.name, 'combination': s.student.combination.code if s.student.combination else '-', 'points': s.total_points, 'division': s.division, 'average': avg, 'ownership': s.student.school.ownership, }) # Helper function to get top N students with proper sorting def get_top_students(student_list, n=10): # Sort: first by points (ascending - lower is better), then by average (descending - higher is better) sorted_list = sorted(student_list, key=lambda x: (x['points'], -x['average'])) return sorted_list[:n] # ====================================================== # DEFINE STUDENT GROUPS # ====================================================== girls_students = [s for s in all_students if s['sex'] == 'F'] boys_students = [s for s in all_students if s['sex'] == 'M'] gvt_students = [s for s in all_students if s['ownership'] == 'GVT'] nongvt_students = [s for s in all_students if s['ownership'] == 'NON-GVT'] # ====================================================== # BEST STUDENTS - OVERALL # ====================================================== best_students_overall = get_top_students(all_students, 10) for i, s in enumerate(best_students_overall, 1): s['rank'] = i # ====================================================== # BEST STUDENTS - GIRLS # ====================================================== best_girls_overall = get_top_students(girls_students, 10) for i, s in enumerate(best_girls_overall, 1): s['rank'] = i # ====================================================== # BEST STUDENTS - BOYS # ====================================================== best_boys_overall = get_top_students(boys_students, 10) for i, s in enumerate(best_boys_overall, 1): s['rank'] = i # ====================================================== # BEST STUDENTS - GOVERNMENT SCHOOLS # ====================================================== best_gvt_overall = get_top_students(gvt_students, 10) for i, s in enumerate(best_gvt_overall, 1): s['rank'] = i # ====================================================== # BEST STUDENTS - GIRLS FROM GOVERNMENT SCHOOLS # ====================================================== gvt_girls = [s for s in gvt_students if s['sex'] == 'F'] best_gvt_girls = get_top_students(gvt_girls, 10) for i, s in enumerate(best_gvt_girls, 1): s['rank'] = i # ====================================================== # BEST STUDENTS - BOYS FROM GOVERNMENT SCHOOLS # ====================================================== gvt_boys = [s for s in gvt_students if s['sex'] == 'M'] best_gvt_boys = get_top_students(gvt_boys, 10) for i, s in enumerate(best_gvt_boys, 1): s['rank'] = i # ====================================================== # BEST STUDENTS - NON-GOVERNMENT SCHOOLS # ====================================================== best_nongvt_overall = get_top_students(nongvt_students, 10) for i, s in enumerate(best_nongvt_overall, 1): s['rank'] = i # ====================================================== # BEST STUDENTS - GIRLS FROM NON-GOVERNMENT SCHOOLS # ====================================================== nongvt_girls = [s for s in nongvt_students if s['sex'] == 'F'] best_nongvt_girls = get_top_students(nongvt_girls, 10) for i, s in enumerate(best_nongvt_girls, 1): s['rank'] = i # ====================================================== # BEST STUDENTS - BOYS FROM NON-GOVERNMENT SCHOOLS # ====================================================== nongvt_boys = [s for s in nongvt_students if s['sex'] == 'M'] best_nongvt_boys = get_top_students(nongvt_boys, 10) for i, s in enumerate(best_nongvt_boys, 1): s['rank'] = i # ====================================================== # BOTTOM/POOR STUDENTS # ====================================================== # Bottom students: highest points first (worst performance) def get_bottom_students(student_list, n=10): # Sort by points (descending - higher is worse), then by average (ascending - lower is worse) sorted_list = sorted(student_list, key=lambda x: (-x['points'], x['average'])) return sorted_list[:n] # ====================================================== # BOTTOM STUDENTS - GIRLS # ====================================================== bottom_girls = get_bottom_students(girls_students, 10) for i, s in enumerate(bottom_girls, 1): s['rank'] = i # ====================================================== # BOTTOM STUDENTS - BOYS # ====================================================== bottom_boys = get_bottom_students(boys_students, 10) for i, s in enumerate(bottom_boys, 1): s['rank'] = i # ====================================================== # BOTTOM STUDENTS - GOVERNMENT SCHOOLS (GIRLS) # ====================================================== bottom_gvt_girls = get_bottom_students(gvt_girls, 10) for i, s in enumerate(bottom_gvt_girls, 1): s['rank'] = i # ====================================================== # BOTTOM STUDENTS - GOVERNMENT SCHOOLS (BOYS) # ====================================================== bottom_gvt_boys = get_bottom_students(gvt_boys, 10) for i, s in enumerate(bottom_gvt_boys, 1): s['rank'] = i # ====================================================== # BOTTOM STUDENTS - NON-GOVERNMENT SCHOOLS (GIRLS) # ====================================================== bottom_nongvt_girls = get_bottom_students(nongvt_girls, 10) for i, s in enumerate(bottom_nongvt_girls, 1): s['rank'] = i # ====================================================== # BOTTOM STUDENTS - NON-GOVERNMENT SCHOOLS (BOYS) # ====================================================== bottom_nongvt_boys = get_bottom_students(nongvt_boys, 10) for i, s in enumerate(bottom_nongvt_boys, 1): s['rank'] = i # ====================================================== # SUBJECT PERFORMANCE TABLES # ====================================================== # Get all processed subject scores for this project subject_scores = ProcessedSubjectScore.objects.filter( project_id=project_id ).select_related('subject', 'student') # Get all subjects that have scores subjects = ExamSubject.objects.filter( processedsubjectscore__project_id=project_id ).distinct().order_by('subject_code') # Define mapping for grades grade_order = ['A', 'B', 'C', 'D', 'E', 'S', 'F'] # ====================================================== # TABLE 1: SUBJECT SUMMARY (Jedwali la Muhtasari wa Masomo) # ====================================================== subject_summary = [] for subject in subjects: # Get all scores for this subject scores = subject_scores.filter(subject=subject) total_students = scores.count() if total_students == 0: continue # Count grades grade_counts = {grade: scores.filter(grade=grade).count() for grade in grade_order} # Calculate passed students (A-E) passed = sum(grade_counts[g] for g in ['A', 'B', 'C', 'D', 'E']) passed_pct = round(passed / total_students * 100, 2) if total_students > 0 else 0 # Calculate S+F sf = grade_counts['S'] + grade_counts['F'] sf_pct = round(sf / total_students * 100, 2) if total_students > 0 else 0 # Calculate GPA # GPA Formula: (A*1 + B*2 + C*3 + D*4 + E*5 + S*6 + F*7) / Total gpa_numerator = ( grade_counts['A'] * 1 + grade_counts['B'] * 2 + grade_counts['C'] * 3 + grade_counts['D'] * 4 + grade_counts['E'] * 5 + grade_counts['S'] * 6 + grade_counts['F'] * 7 ) gpa = round(gpa_numerator / total_students, 4) if total_students > 0 else 0 subject_summary.append({ 'subject_code': subject.subject_code, 'subject_name': subject.subject_name_sw, 'subject_type': subject.subject_type, 'total': total_students, 'passed': passed, 'passed_pct': passed_pct, 'A': grade_counts['A'], 'B': grade_counts['B'], 'C': grade_counts['C'], 'D': grade_counts['D'], 'E': grade_counts['E'], 'S': grade_counts['S'], 'F': grade_counts['F'], 'sf': sf, 'sf_pct': sf_pct, 'gpa': gpa, }) # Sort by GPA (lower is better) subject_summary.sort(key=lambda x: x['gpa']) # ====================================================== # TABLE 2: SUBJECT PERFORMANCE BY GENDER # ====================================================== subject_gender_summary = [] for subject in subjects: scores = subject_scores.filter(subject=subject) total = scores.count() if total == 0: continue # Male students male_scores = scores.filter(student__sex='M') male_total = male_scores.count() # Female students female_scores = scores.filter(student__sex='F') female_total = female_scores.count() # Grade counts by gender male_counts = {grade: male_scores.filter(grade=grade).count() for grade in grade_order} female_counts = {grade: female_scores.filter(grade=grade).count() for grade in grade_order} total_counts = {grade: scores.filter(grade=grade).count() for grade in grade_order} # Calculate percentages male_pcts = {grade: round(male_counts[grade] / male_total * 100, 2) if male_total > 0 else 0 for grade in grade_order} female_pcts = {grade: round(female_counts[grade] / female_total * 100, 2) if female_total > 0 else 0 for grade in grade_order} total_pcts = {grade: round(total_counts[grade] / total * 100, 2) if total > 0 else 0 for grade in grade_order} subject_gender_summary.append({ 'subject_code': subject.subject_code, 'subject_name': subject.subject_name_sw, 'subject_type': subject.subject_type, 'male_total': male_total, 'female_total': female_total, 'total': total, 'male_counts': male_counts, 'male_pcts': male_pcts, 'female_counts': female_counts, 'female_pcts': female_pcts, 'total_counts': total_counts, 'total_pcts': total_pcts, }) # ====================================================== # TABLE 3: SUBJECT PERFORMANCE BY REGION # ====================================================== # Get all regions regions = Region.objects.filter( school__projectstudent__project_id=project_id ).distinct().order_by('name') subject_region_summary = [] for subject in subjects: subject_data = { 'subject_code': subject.subject_code, 'subject_name': subject.subject_name_sw, 'subject_type': subject.subject_type, 'regions': [] } for region in regions: # Get scores for this subject in this region region_scores = subject_scores.filter( subject=subject, student__school__district__region=region ) total = region_scores.count() if total == 0: continue # Calculate passed students (A-E) passed = region_scores.filter(grade__in=['A', 'B', 'C', 'D', 'E']).count() passed_pct = round(passed / total * 100, 2) if total > 0 else 0 # Calculate GPA grade_counts = {grade: region_scores.filter(grade=grade).count() for grade in grade_order} gpa_numerator = ( grade_counts['A'] * 1 + grade_counts['B'] * 2 + grade_counts['C'] * 3 + grade_counts['D'] * 4 + grade_counts['E'] * 5 + grade_counts['S'] * 6 + grade_counts['F'] * 7 ) gpa = round(gpa_numerator / total, 4) if total > 0 else 0 subject_data['regions'].append({ 'region': region.name, 'passed_pct': passed_pct, 'gpa': gpa, 'total': total, }) if subject_data['regions']: subject_region_summary.append(subject_data) # ====================================================== # TABLE 4: SUBJECT PERFORMANCE BY TYPE - DISTRICT LEVEL # Dynamically group by subject_type from the ExamSubject model # ====================================================== # ====================================================== # TABLE 4: SUBJECT PERFORMANCE BY TYPE - DISTRICT LEVEL # Dynamically group by subject_type from the ExamSubject model # ====================================================== print("\n" + "="*80) print("DEBUG: Starting Subject Performance by Type") print("="*80) # Get all districts districts = District.objects.filter( school__projectstudent__project_id=project_id ).distinct().order_by('region__name', 'name') print(f"1. Found {districts.count()} districts") # Get all unique subject types from the database that have subjects with scores subject_types = ExamSubject.objects.filter( processedsubjectscore__project_id=project_id ).values_list('subject_type', flat=True).distinct().exclude(subject_type__isnull=True).exclude(subject_type='') print(f"2. Found {len(subject_types)} subject types: {list(subject_types)}") # For each subject type, get all subjects of that type subject_type_subjects = {} subject_type_counts = {} for stype in subject_types: subjects_of_type = ExamSubject.objects.filter( subject_type=stype, processedsubjectscore__project_id=project_id ).distinct().order_by('subject_code') count = subjects_of_type.count() subject_type_counts[stype] = count print(f" - {stype}: {count} subjects") if count > 0: subject_type_subjects[stype] = list(subjects_of_type) # Print first few subject names subject_names = [s.subject_name_sw for s in subjects_of_type[:3]] print(f" Examples: {subject_names}") print(f"3. Subject types with subjects: {len(subject_type_subjects)}") # Build district subject performance by type district_subject_performance = [] districts_with_data = 0 total_subject_entries = 0 for district in districts: district_data = { 'district': district.name, 'region': district.region.name, 'subjects': {} # Will store {subject_name: {'passed_pct': x, 'rank': y, 'total': z}} } district_has_data = False # For each subject type, process all subjects for stype, subjects_list in subject_type_subjects.items(): for subject in subjects_list: subject_name = subject.subject_name_sw # Get scores for this subject in this district scores = subject_scores.filter( subject=subject, student__school__district=district ) total = scores.count() total_subject_entries += total if total == 0: district_data['subjects'][subject_name] = { 'passed_pct': 0, 'rank': None, 'total': 0, 'subject_type': stype, 'subject_code': subject.subject_code, } continue district_has_data = True # Calculate passed students (A-E) passed = scores.filter(grade__in=['A', 'B', 'C', 'D', 'E']).count() passed_pct = round(passed / total * 100, 2) district_data['subjects'][subject_name] = { 'passed_pct': passed_pct, 'total': total, 'subject_type': stype, 'subject_code': subject.subject_code, } if district_has_data: districts_with_data += 1 district_subject_performance.append(district_data) print(f"4. Processed {len(district_subject_performance)} districts") print(f"5. Districts with at least one subject: {districts_with_data}") print(f"6. Total subject entries across all districts: {total_subject_entries}") # Calculate ranks for each subject across districts # First, group subjects by name to calculate ranks subject_names = set() for d in district_subject_performance: subject_names.update(d['subjects'].keys()) print(f"7. Found {len(subject_names)} unique subject names across all districts") ranked_subjects = 0 for subject_name in subject_names: # Get all districts that have this subject with data districts_with_subject = [] for d in district_subject_performance: subject_data = d['subjects'].get(subject_name, {}) if subject_data.get('total', 0) > 0: districts_with_subject.append({ 'district': d['district'], 'passed_pct': subject_data['passed_pct'] }) if districts_with_subject: ranked_subjects += 1 # Sort by passed_pct descending (higher is better) districts_with_subject.sort(key=lambda x: -x['passed_pct']) # Assign ranks for idx, dws in enumerate(districts_with_subject, 1): for d in district_subject_performance: if d['district'] == dws['district'] and subject_name in d['subjects']: d['subjects'][subject_name]['rank'] = idx print(f"8. Subjects with at least one district having data: {ranked_subjects}") # Create subject_type_groups for template subject_type_groups = {} for stype, subjects_list in subject_type_subjects.items(): subject_type_groups[stype] = [ { 'name': s.subject_name_sw, 'code': s.subject_code, 'shortname': s.subject_shortname, } for s in subjects_list ] print(f"9. Created subject_type_groups with {len(subject_type_groups)} types") # Create district_performance_lookup for easier template access district_performance_lookup = [] districts_with_data_in_lookup = 0 for d in district_subject_performance: district_row = { 'district': d['district'], 'region': d['region'], 'subjects': {} } has_data = False for subject_name, subject_data in d['subjects'].items(): if subject_data.get('total', 0) > 0: has_data = True district_row['subjects'][subject_name] = { 'pct': subject_data['passed_pct'], 'rank': subject_data['rank'], 'total': subject_data['total'], } if has_data: districts_with_data_in_lookup += 1 district_performance_lookup.append(district_row) print(f"10. Created district_performance_lookup with {len(district_performance_lookup)} entries") print(f"11. Districts with data in lookup: {districts_with_data_in_lookup}") # Final summary print("\n" + "="*80) print("FINAL SUMMARY") print("="*80) print(f"Subject Types: {list(subject_type_groups.keys())}") for stype, subjects in subject_type_groups.items(): print(f" {stype}: {len(subjects)} subjects - {[s['name'] for s in subjects]}") print(f"Districts with data: {districts_with_data_in_lookup} out of {len(districts)}") print("="*80 + "\n") # ====================================================== # TABLE 5: SUBJECT TYPE SUMMARY (Grouped by subject_type) # ====================================================== # Make sure we're using the ExamSubject queryset subjects_queryset = ExamSubject.objects.filter( processedsubjectscore__project_id=project_id ).distinct().order_by('subject_code') subject_type_summary = {} for subject in subjects_queryset: # Use the queryset, not the dict list subject_type = subject.subject_type if not subject_type: # Skip if no subject_type continue scores = subject_scores.filter(subject=subject) total = scores.count() if total == 0: continue if subject_type not in subject_type_summary: subject_type_summary[subject_type] = { 'subject_type': subject_type, 'total_students': 0, 'total_subjects': 0, 'A': 0, 'B': 0, 'C': 0, 'D': 0, 'E': 0, 'S': 0, 'F': 0, 'passed': 0, 'gpa_sum': 0, } grade_counts = {grade: scores.filter(grade=grade).count() for grade in grade_order} subject_type_summary[subject_type]['total_students'] += total subject_type_summary[subject_type]['total_subjects'] += 1 subject_type_summary[subject_type]['A'] += grade_counts['A'] subject_type_summary[subject_type]['B'] += grade_counts['B'] subject_type_summary[subject_type]['C'] += grade_counts['C'] subject_type_summary[subject_type]['D'] += grade_counts['D'] subject_type_summary[subject_type]['E'] += grade_counts['E'] subject_type_summary[subject_type]['S'] += grade_counts['S'] subject_type_summary[subject_type]['F'] += grade_counts['F'] subject_type_summary[subject_type]['passed'] += ( grade_counts['A'] + grade_counts['B'] + grade_counts['C'] + grade_counts['D'] + grade_counts['E'] ) # Calculate GPA for this subject gpa_numerator = ( grade_counts['A'] * 1 + grade_counts['B'] * 2 + grade_counts['C'] * 3 + grade_counts['D'] * 4 + grade_counts['E'] * 5 + grade_counts['S'] * 6 + grade_counts['F'] * 7 ) subject_gpa = gpa_numerator / total if total > 0 else 0 subject_type_summary[subject_type]['gpa_sum'] += subject_gpa # Convert to list and calculate averages subject_type_list = [] for stype, data in subject_type_summary.items(): total_students = data['total_students'] data['passed_pct'] = round(data['passed'] / total_students * 100, 2) if total_students > 0 else 0 data['avg_gpa'] = round(data['gpa_sum'] / data['total_subjects'], 4) if data['total_subjects'] > 0 else 0 # Calculate percentages for each grade data['A_pct'] = round(data['A'] / total_students * 100, 2) if total_students > 0 else 0 data['B_pct'] = round(data['B'] / total_students * 100, 2) if total_students > 0 else 0 data['C_pct'] = round(data['C'] / total_students * 100, 2) if total_students > 0 else 0 data['D_pct'] = round(data['D'] / total_students * 100, 2) if total_students > 0 else 0 data['E_pct'] = round(data['E'] / total_students * 100, 2) if total_students > 0 else 0 data['S_pct'] = round(data['S'] / total_students * 100, 2) if total_students > 0 else 0 data['F_pct'] = round(data['F'] / total_students * 100, 2) if total_students > 0 else 0 subject_type_list.append(data) # Sort by subject_type subject_type_list.sort(key=lambda x: x['subject_type']) # ====================================================== # SUBJECT RANKINGS BY DISTRICT (Based on GPA) # ====================================================== print("\n" + "="*80) print("DEBUG: Building Subject Rankings by District") print("="*80) # Get all subjects that have scores subjects_with_scores = ExamSubject.objects.filter( processedsubjectscore__project_id=project_id ).distinct().order_by('subject_code') subject_district_rankings = {} for subject in subjects_with_scores: print(f"\nProcessing subject: {subject.subject_name_sw} ({subject.subject_code})") # Get all districts that have scores for this subject districts_with_data = District.objects.filter( school__projectstudent__processedsubjectscore__subject=subject, school__projectstudent__processedsubjectscore__project_id=project_id ).distinct().order_by('name') if not districts_with_data.exists(): print(f" No districts found with data for {subject.subject_name_sw}") continue district_data = [] for district in districts_with_data: # Get all scores for this subject in this district scores = ProcessedSubjectScore.objects.filter( project_id=project_id, subject=subject, student__school__district=district ) total = scores.count() if total == 0: continue # Count grades grade_counts = { 'A': scores.filter(grade='A').count(), 'B': scores.filter(grade='B').count(), 'C': scores.filter(grade='C').count(), 'D': scores.filter(grade='D').count(), 'E': scores.filter(grade='E').count(), 'S': scores.filter(grade='S').count(), 'F': scores.filter(grade='F').count(), } # Calculate passed students (A-E) passed = sum(grade_counts[g] for g in ['A', 'B', 'C', 'D', 'E']) passed_pct = round(passed / total * 100, 2) if total > 0 else 0 # Calculate failed students (S+F) failed = grade_counts['S'] + grade_counts['F'] failed_pct = round(failed / total * 100, 2) if total > 0 else 0 # Calculate GPA # GPA Formula: (A*1 + B*2 + C*3 + D*4 + E*5 + S*6 + F*7) / Total gpa_numerator = ( grade_counts['A'] * 1 + grade_counts['B'] * 2 + grade_counts['C'] * 3 + grade_counts['D'] * 4 + grade_counts['E'] * 5 + grade_counts['S'] * 6 + grade_counts['F'] * 7 ) gpa = round(gpa_numerator / total, 4) if total > 0 else 0 district_data.append({ 'district': district.name, 'region': district.region.name, 'total': total, 'A': grade_counts['A'], 'B': grade_counts['B'], 'C': grade_counts['C'], 'D': grade_counts['D'], 'E': grade_counts['E'], 'S': grade_counts['S'], 'F': grade_counts['F'], 'passed': passed, 'passed_pct': passed_pct, 'failed': failed, 'failed_pct': failed_pct, 'gpa': gpa, }) # Sort by GPA (lower is better) district_data.sort(key=lambda x: x['gpa']) # Add rank for idx, data in enumerate(district_data, 1): data['rank'] = idx subject_district_rankings[subject.subject_name_sw] = { 'subject_code': subject.subject_code, 'subject_name': subject.subject_name_sw, 'subject_type': subject.subject_type, 'districts': district_data } print(f" Found {len(district_data)} districts with data") if district_data: print(f" Best GPA: {district_data[0]['district']} - {district_data[0]['gpa']}") print(f" Worst GPA: {district_data[-1]['district']} - {district_data[-1]['gpa']}") print(f"\nTotal subjects with rankings: {len(subject_district_rankings)}") print("="*80 + "\n") # ====================================================== # SCHOOL RANKING TABLES # ====================================================== print("\n" + "="*80) print("DEBUG: Building School Ranking Tables") print("="*80) # Get all schools with valid results school_results = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).select_related('school', 'school__district', 'school__district__region') school_ranking_data = [] for school in school_results: # Get division counts for this school division_counts = StudentFinalResult.objects.filter( project_id=project_id, student__school=school.school ).filter(division__in=['I', 'II', 'III', 'IV', '0']) total_sat = division_counts.count() if total_sat == 0: continue div_i = division_counts.filter(division='I').count() div_ii = division_counts.filter(division='II').count() div_iii = division_counts.filter(division='III').count() div_iv = division_counts.filter(division='IV').count() div_o = division_counts.filter(division='0').count() pass_i_iii = div_i + div_ii + div_iii pass_i_iv = pass_i_iii + div_iv pass_i_iii_pct = round(pass_i_iii / total_sat * 100, 2) if total_sat > 0 else 0 pass_i_iv_pct = round(pass_i_iv / total_sat * 100, 2) if total_sat > 0 else 0 o_pct = round(div_o / total_sat * 100, 2) if total_sat > 0 else 0 # Get ownership display ownership_display = "GVT" if school.school.ownership == "GVT" else "NON-GVT" school_ranking_data.append({ 'school': school.school, 'school_name': school.school.name, 'name_short': school.school.name_short, 'school_code': school.school.school_code, 'district': school.school.district.name, 'region': school.school.district.region.name, 'ownership': ownership_display, 'sat': total_sat, 'i': div_i, 'ii': div_ii, 'iii': div_iii, 'iv': div_iv, 'o': div_o, 'pass_i_iii': pass_i_iii, 'pass_i_iii_pct': pass_i_iii_pct, 'pass_i_iv': pass_i_iv, 'pass_i_iv_pct': pass_i_iv_pct, 'gpa': school.sch_gpa, }) # Sort by GPA (lower is better) school_ranking_data.sort(key=lambda x: x['gpa']) # Assign overall ranks for idx, school in enumerate(school_ranking_data, 1): school['overall_rank'] = idx # Group by region for region-wise ranks regions = {} for school in school_ranking_data: region = school['region'] if region not in regions: regions[region] = [] regions[region].append(school) for region, schools in regions.items(): schools.sort(key=lambda x: x['gpa']) for idx, school in enumerate(schools, 1): school['region_rank'] = idx # Group by district for district-wise ranks districts = {} for school in school_ranking_data: district = school['district'] if district not in districts: districts[district] = [] districts[district].append(school) for district, schools in districts.items(): schools.sort(key=lambda x: x['gpa']) for idx, school in enumerate(schools, 1): school['district_rank'] = idx # Create three separate lists school_ranking_overall = school_ranking_data.copy() school_ranking_30plus = [s for s in school_ranking_data if s['sat'] >= 30] school_ranking_under30 = [s for s in school_ranking_data if s['sat'] < 30] print(f"Total schools: {len(school_ranking_data)}") print(f"Schools with 30+ students: {len(school_ranking_30plus)}") print(f"Schools with <30 students: {len(school_ranking_under30)}") print("="*80 + "\n") # Get districts with school counts - FILTERED BY PROJECT IF NEEDED # If you want to count only schools that have students in this project: # ====================================================== # ADD SCHOOL COUNT BY DISTRICT DATA (HORIZONTAL LAYOUT) # ====================================================== # Get districts with school counts districts = District.objects.filter( school__projectstudent__project_id=project_id ).distinct().order_by('name') # Prepare data for horizontal table district_names = [] govt_counts = [] private_counts = [] total_counts = [] for district in districts: # Count schools in this district govt = School.objects.filter( district=district, ownership='GVT', projectstudent__project_id=project_id ).distinct().count() private = School.objects.filter( district=district, ownership='NON-GVT', projectstudent__project_id=project_id ).distinct().count() total = govt + private # Only include districts with at least one school if total > 0: district_names.append(district.name) govt_counts.append(govt) private_counts.append(private) total_counts.append(total) # Calculate totals total_govt = sum(govt_counts) total_private = sum(private_counts) overall_total = total_govt + total_private # ====================================================== # CHECK IF EXPORT TO WORD IS REQUESTED # ====================================================== if request.GET.get('export') == 'word': # Create context dictionary with ALL your variables context = { 'project': project, 'region_data': region_data, 'district_data': district_data, 'district_data_registration': district_data_registration, 'div_regions': div_regions, 'region_gender_data': region_gender_data, 'gender_div': gender_div, 'district_div_data': district_div_data, 'combination_tables': combination_tables, 'total_all_sat': total_all_sat, 'total_i': total_i, 'total_ii': total_ii, 'total_iii': total_iii, 'total_iv': total_iv, 'total_o': total_o, 'overall_gpa_total': overall_gpa_total, 'overall_gpa_total_reg': overall_gpa_total_reg, 'total_schools': total_schools, 'total_reg_f': total_reg_f, 'total_reg_m': total_reg_m, 'total_reg': total_reg, 'total_sat_f': total_sat_f, 'total_sat_m': total_sat_m, 'total_sat': total_sat, 'region_sat': region_sat, 'total_abs_f': total_abs_f, 'total_abs_m': total_abs_m, 'total_abs': total_abs, 'category_performance': category_list, 'category_overall': overall_totals, 'best_schools_30plus': best_schools_30plus, 'best_schools_under30': best_schools_under30, 'best_gov_30plus': best_gov_30plus, 'best_gov_under30': best_gov_under30, 'best_nongov_30plus': best_nongov_30plus, 'best_nongov_under30': best_nongov_under30, 'bottom_10_overall': bottom_10_overall, 'schools_not_100': schools_not_100_list, 'best_students': best_students, 'best_students_overall': best_students_overall, 'best_girls_overall': best_girls_overall, 'best_boys_overall': best_boys_overall, 'best_gvt_overall': best_gvt_overall, 'best_gvt_girls': best_gvt_girls, 'best_gvt_boys': best_gvt_boys, 'best_nongvt_overall': best_nongvt_overall, 'best_nongvt_girls': best_nongvt_girls, 'best_nongvt_boys': best_nongvt_boys, 'bottom_girls': bottom_girls, 'bottom_boys': bottom_boys, 'bottom_gvt_girls': bottom_gvt_girls, 'bottom_gvt_boys': bottom_gvt_boys, 'bottom_nongvt_girls': bottom_nongvt_girls, 'bottom_nongvt_boys': bottom_nongvt_boys, 'subject_summary': subject_summary, 'subject_gender_summary': subject_gender_summary, 'subject_region_summary': subject_region_summary, 'district_subject_performance': district_subject_performance, 'subject_type_summary': subject_type_list, 'overall_sat_pct': overall_sat_pct, 'overall_abs_pct': overall_abs_pct, 'subject_type_groups': subject_type_groups, 'district_performance_lookup': district_performance_lookup, 'subject_district_rankings': subject_district_rankings, 'school_ranking_overall': school_ranking_overall, 'school_ranking_30plus': school_ranking_30plus, 'school_ranking_under30': school_ranking_under30, # NEW: Add school count by district data (horizontal layout) 'district_names': district_names, 'govt_counts': govt_counts, 'private_counts': private_counts, 'total_counts': total_counts, 'total_govt': total_govt, 'total_private': total_private, 'overall_total': overall_total, 'get_item': get_item, } response = HttpResponse(content_type='application/msword') response['Content-Disposition'] = f'attachment; filename="{project.name}_tables.doc"' from django.template.loader import render_to_string html = render_to_string('exams/tables_for_word.html', context, request) # Add XML declaration if needed if not html.startswith('\n' + html response.write(html) return response # Regular HTML preview context = { 'project': project, 'region_data': region_data, 'district_data_registration': district_data_registration, 'district_data': district_data, 'div_regions': div_regions, 'region_gender_data': region_gender_data, 'gender_div': gender_div, 'district_div_data': district_div_data, 'combination_tables': combination_tables, 'total_all_sat': total_all_sat, 'total_i': total_i, 'total_ii': total_ii, 'total_iii': total_iii, 'total_iv': total_iv, 'total_o': total_o, 'overall_gpa_total': overall_gpa_total, 'overall_gpa_total_reg': overall_gpa_total_reg, 'total_schools': total_schools, 'total_reg_f': total_reg_f, 'total_reg_m': total_reg_m, 'total_reg': total_reg, 'total_sat_f': total_sat_f, 'total_sat_m': total_sat_m, 'total_sat': total_sat, 'region_sat': region_sat, 'total_abs_f': total_abs_f, 'total_abs_m': total_abs_m, 'total_abs': total_abs, 'category_performance': category_list, 'category_overall': overall_totals, 'best_schools_30plus': best_schools_30plus, 'best_schools_under30': best_schools_under30, 'best_gov_30plus': best_gov_30plus, 'best_gov_under30': best_gov_under30, 'best_nongov_30plus': best_nongov_30plus, 'best_nongov_under30': best_nongov_under30, 'bottom_10_overall': bottom_10_overall, 'schools_not_100': schools_not_100_list, 'best_students': best_students, 'best_students_overall': best_students_overall, 'best_girls_overall': best_girls_overall, 'best_boys_overall': best_boys_overall, 'best_gvt_overall': best_gvt_overall, 'best_gvt_girls': best_gvt_girls, 'best_gvt_boys': best_gvt_boys, 'best_nongvt_overall': best_nongvt_overall, 'best_nongvt_girls': best_nongvt_girls, 'best_nongvt_boys': best_nongvt_boys, 'bottom_girls': bottom_girls, 'bottom_boys': bottom_boys, 'bottom_gvt_girls': bottom_gvt_girls, 'bottom_gvt_boys': bottom_gvt_boys, 'bottom_nongvt_girls': bottom_nongvt_girls, 'bottom_nongvt_boys': bottom_nongvt_boys, 'subject_summary': subject_summary, 'subject_gender_summary': subject_gender_summary, 'subject_region_summary': subject_region_summary, 'district_subject_performance': district_subject_performance, 'subject_type_summary': subject_type_list, 'overall_sat_pct': overall_sat_pct, 'overall_abs_pct': overall_abs_pct, 'subject_type_groups': subject_type_groups, 'district_performance_lookup': district_performance_lookup, 'subject_district_rankings': subject_district_rankings, 'school_ranking_overall': school_ranking_overall, 'school_ranking_30plus': school_ranking_30plus, 'school_ranking_under30': school_ranking_under30, # NEW: Add school count by district data (horizontal layout) 'district_names': district_names, 'govt_counts': govt_counts, 'private_counts': private_counts, 'total_counts': total_counts, 'total_govt': total_govt, 'total_private': total_private, 'overall_total': overall_total, 'get_item': get_item, } return render(request, 'exams/tables_preview.html', context) # exams/views_reports_combined.py from django.shortcuts import render, get_object_or_404 from django.db.models import Count, Q, Avg from django.http import HttpResponse import openpyxl from openpyxl.styles import Font, Alignment, PatternFill from openpyxl.utils import get_column_letter from .models import JointExamProject, ExamSubject, ProcessedSubjectScore, School, District, Region # exams/views_reports_combined.py @login_required def subject_school_ranking_page(request, project_code): """ Display page with list of subjects for school ranking (similar to rank-summary page) """ from django.shortcuts import get_object_or_404 from .models import JointExamProject, ExamSubject, Region, District project = get_object_or_404(JointExamProject, project_code=project_code) # Get all subjects for this project's academic level that have scores subjects = ExamSubject.objects.filter( academic_level=project.academic_level, processedsubjectscore__project_id=project.id, is_active=True ).distinct().order_by('subject_code') # Get regions and districts for filters regions = Region.objects.all().order_by('name') districts = District.objects.all().order_by('name') context = { 'project': project, 'project_code': project_code, 'subjects': subjects, 'regions': regions, 'districts': districts, } return render(request, 'exams/subject_school_ranking_page.html', context) @login_required def subject_school_ranking_report(request, project_code, subject_code): """ Generate subject school ranking report """ from .models import JointExamProject, ExamSubject, ProcessedSubjectScore, School, District, Region, GradingRule, GpaRule from django.shortcuts import get_object_or_404 from django.db.models import Count, Q, Avg from django.http import HttpResponse import openpyxl from openpyxl.styles import Font, Alignment, PatternFill from openpyxl.utils import get_column_letter # Get project and subject project = get_object_or_404(JointExamProject, project_code=project_code) project_id = project.id subject = get_object_or_404(ExamSubject, subject_code=subject_code) academic_level_id = project.academic_level_id # Get filter parameters region_id = request.GET.get('region_id') district_id = request.GET.get('district_id') layout = request.GET.get('layout', 'normal') format_type = request.GET.get('format', 'html') # Get all schools that have scores for this subject school_ids = ProcessedSubjectScore.objects.filter( project_id=project_id, subject=subject ).values_list('student__school_id', flat=True).distinct() schools_qs = School.objects.filter(id__in=school_ids) # Apply region filter if region_id: schools_qs = schools_qs.filter(region_id=region_id) # Apply district filter if district_id: schools_qs = schools_qs.filter(district_id=district_id) # Define GPA weights based on academic level if academic_level_id == 1: # O-Level gpa_weights = {'A': 1, 'B': 2, 'C': 3, 'D': 4, 'F': 5} valid_grades = ['A', 'B', 'C', 'D', 'F'] pass_grades = ['A', 'B', 'C', 'D'] elif academic_level_id == 2: # A-Level gpa_weights = {'A': 1, 'B': 2, 'C': 3, 'D': 4, 'E': 5, 'S': 6, 'F': 7} valid_grades = ['A', 'B', 'C', 'D', 'E', 'S', 'F'] pass_grades = ['A', 'B', 'C', 'D', 'E'] else: # Primary Level gpa_weights = {'A': 1, 'B': 2, 'C': 3, 'D': 4, 'E': 5} valid_grades = ['A', 'B', 'C', 'D', 'E'] pass_grades = ['A', 'B', 'C'] # Build ranking data for each school ranking_data = [] for school in schools_qs: # Get scores for this subject in this school scores = ProcessedSubjectScore.objects.filter( project_id=project_id, subject=subject, student__school=school ) total_students = scores.count() if total_students == 0: continue # Count grades grade_counts = {} for grade in valid_grades: grade_counts[grade] = scores.filter(grade=grade).count() # Calculate passed students based on academic level passed = 0 for grade in pass_grades: passed += grade_counts.get(grade, 0) passed_pct = round((passed / total_students * 100), 2) if total_students > 0 else 0 # Calculate average score all_scores = [] for score in scores: if score.percentage_score and score.percentage_score != 'X': try: all_scores.append(float(score.percentage_score)) except (ValueError, TypeError): pass avg_score = round(sum(all_scores) / len(all_scores), 2) if all_scores else 0 # Calculate GPA using the weights for this academic level gpa_numerator = 0 for grade, count in grade_counts.items(): if grade in gpa_weights: gpa_numerator += gpa_weights[grade] * count gpa = round(gpa_numerator / total_students, 4) if total_students > 0 else 0 # Get GPA grade and status from database rules def get_gpa_info(gpa_value, academic_level_obj): from .models import GpaRule if gpa_value is None or gpa_value == 0: return 'F', 'FAIL', '#dc3545' rules = GpaRule.objects.filter(academic_level=academic_level_obj).order_by('-min_gpa') for rule in rules: if rule.min_gpa <= gpa_value <= rule.max_gpa: return rule.gpa_grade, rule.gpa_status, rule.gpa_colour return 'F', 'FAIL', '#dc3545' grade, status, gpa_color = get_gpa_info(gpa, project.academic_level) # Build grade data dictionary dynamically grade_data = {} for g in valid_grades: grade_data[g] = grade_counts.get(g, 0) ranking_data.append({ 'rank': 0, # Will be set after sorting 'school_code': school.school_code, 'school_name': school.name, 'school_short': school.name_short or school.name, 'district': school.district.name, 'region': school.region.name, 'ownership': school.ownership, 'total_students': total_students, 'passed': passed, 'passed_pct': passed_pct, 'avg_score': avg_score, 'gpa': gpa, 'grade': grade, 'status': status, 'gpa_color': gpa_color, **grade_data, }) # Sort by GPA (lower is better) ranking_data.sort(key=lambda x: x['gpa']) # Add rank for idx, data in enumerate(ranking_data, 1): data['rank'] = idx data['total_schools'] = len(ranking_data) # Handle different output formats if format_type == 'excel': return export_subject_school_ranking_excel(project, subject, ranking_data, layout, academic_level_id) elif format_type == 'pdf': return export_subject_school_ranking_pdf(project, subject, ranking_data, layout, academic_level_id) else: # HTML view template = 'exams/subject_school_ranking_report.html' if layout == 'extended': template = 'exams/subject_school_ranking_extended.html' context = { 'project': project, 'subject': subject, 'ranking_data': ranking_data, 'layout': layout, 'region_id': region_id, 'district_id': district_id, 'total_schools': len(ranking_data), 'academic_level_id': academic_level_id, } return render(request, template, context) @login_required def export_subject_school_ranking_excel(project, subject, ranking_data, layout='normal', academic_level_id=2): """ Export subject school ranking to Excel """ import openpyxl from openpyxl.styles import Font, Alignment, PatternFill from openpyxl.utils import get_column_letter from django.http import HttpResponse wb = openpyxl.Workbook() ws = wb.active ws.title = f"{subject.subject_code}_Ranking" # Define styles header_fill = PatternFill(start_color="1a3e6f", end_color="1a3e6f", fill_type="solid") header_font = Font(color="FFFFFF", bold=True) center_alignment = Alignment(horizontal="center", vertical="center") # Title ws.merge_cells('A1:M1') ws['A1'] = f"SUBJECT SCHOOL RANKING - {subject.subject_code} - {subject.subject_name_eng}" ws['A1'].font = Font(bold=True, size=14) ws['A1'].alignment = center_alignment ws.merge_cells('A2:M2') ws['A2'] = f"{project.name} - {project.year}" ws['A2'].font = Font(bold=True, size=12) ws['A2'].alignment = center_alignment ws.append([]) if layout == 'extended': # Extended layout headers headers = [ 'Rank', 'School Code', 'School Name', 'District', 'Region', 'Ownership', 'Total', 'Passed', 'Pass %', 'Avg Score', 'GPA', 'Grade' ] # Add grade columns based on academic level if academic_level_id == 2: # A-Level headers.extend(['A', 'B', 'C', 'D', 'E', 'S', 'F']) elif academic_level_id == 1: # O-Level headers.extend(['A', 'B', 'C', 'D', 'F']) else: # Primary headers.extend(['A', 'B', 'C', 'D', 'E']) for col, header in enumerate(headers, 1): cell = ws.cell(row=4, column=col) cell.value = header cell.fill = header_fill cell.font = header_font cell.alignment = center_alignment # Add data rows for row_num, r in enumerate(ranking_data, 5): data = [ r['rank'], r['school_code'], r['school_name'], r['district'], r['region'], r['ownership'], r['total_students'], r['passed'], r['passed_pct'], r['avg_score'], r['gpa'], r['grade'] ] # Add grade columns based on academic level if academic_level_id == 2: data.extend([r.get('A', 0), r.get('B', 0), r.get('C', 0), r.get('D', 0), r.get('E', 0), r.get('S', 0), r.get('F', 0)]) elif academic_level_id == 1: data.extend([r.get('A', 0), r.get('B', 0), r.get('C', 0), r.get('D', 0), r.get('F', 0)]) else: data.extend([r.get('A', 0), r.get('B', 0), r.get('C', 0), r.get('D', 0), r.get('E', 0)]) for col, value in enumerate(data, 1): cell = ws.cell(row=row_num, column=col) cell.value = value cell.alignment = center_alignment else: # Normal layout headers headers = ['Rank', 'School Code', 'School Name', 'District', 'Region', 'Total', 'Passed', 'Pass %', 'Avg Score', 'GPA', 'Grade'] for col, header in enumerate(headers, 1): cell = ws.cell(row=4, column=col) cell.value = header cell.fill = header_fill cell.font = header_font cell.alignment = center_alignment # Add data rows for row_num, r in enumerate(ranking_data, 5): data = [ r['rank'], r['school_code'], r['school_name'], r['district'], r['region'], r['total_students'], r['passed'], r['passed_pct'], r['avg_score'], r['gpa'], r['grade'] ] for col, value in enumerate(data, 1): cell = ws.cell(row=row_num, column=col) cell.value = value cell.alignment = center_alignment # Auto-adjust column widths for col_idx in range(1, ws.max_column + 1): max_len = 0 col_letter = get_column_letter(col_idx) for cell in ws[col_letter]: if cell.value: max_len = max(max_len, len(str(cell.value))) ws.column_dimensions[col_letter].width = min(max_len + 2, 30) response = HttpResponse( content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) filename = f"{subject.subject_code}_school_ranking_{project.year}.xlsx" response['Content-Disposition'] = f'attachment; filename="{filename}"' wb.save(response) return response @login_required def export_subject_school_ranking_pdf(project, subject, ranking_data, layout='normal'): """ Export subject school ranking to PDF """ from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer from reportlab.lib.pagesizes import A4, landscape from reportlab.lib import colors from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from reportlab.lib.units import cm from django.http import HttpResponse response = HttpResponse(content_type="application/pdf") response["Content-Disposition"] = f'attachment; filename="{subject.subject_code}_school_ranking_{project.year}.pdf"' # Use landscape for more columns doc = SimpleDocTemplate(response, pagesize=landscape(A4)) styles = getSampleStyleSheet() # Title style title_style = ParagraphStyle( 'CustomTitle', parent=styles['Title'], fontSize=14, alignment=1, spaceAfter=10 ) story = [] # Title story.append(Paragraph(f"{subject.subject_code} - {subject.subject_name_eng}", title_style)) story.append(Paragraph(f"{project.name} - {project.year}", styles["Normal"])) story.append(Spacer(1, 12)) # Table data (limit to 50 rows for PDF) if layout == 'extended': data = [['Rank', 'Code', 'School', 'District', 'Region', 'Total', 'Pass %', 'Avg', 'GPA', 'Grade']] for r in ranking_data[:50]: data.append([ str(r['rank']), r['school_code'], (r['school_short'][:25] + '...') if len(r['school_short']) > 25 else r['school_short'], r['district'], r['region'], str(r['total_students']), f"{r['passed_pct']}%", f"{r['avg_score']:.2f}", f"{r['gpa']:.4f}", r['grade'], ]) else: data = [['Rank', 'Code', 'School', 'District', 'Total', 'Pass %', 'GPA', 'Grade']] for r in ranking_data[:50]: data.append([ str(r['rank']), r['school_code'], (r['school_short'][:25] + '...') if len(r['school_short']) > 25 else r['school_short'], r['district'], str(r['total_students']), f"{r['passed_pct']}%", f"{r['gpa']:.4f}", r['grade'], ]) # Create table table = Table(data, repeatRows=1) table.setStyle(TableStyle([ ("GRID", (0, 0), (-1, -1), 0.5, colors.black), ("BACKGROUND", (0, 0), (-1, 0), colors.lightgrey), ("ALIGN", (0, 0), (-1, -1), "CENTER"), ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"), ("FONTSIZE", (0, 0), (-1, -1), 8), ])) story.append(table) doc.build(story) return response # exams/views_reports_combined.py def combined_form_four_report(request, project_code): """ Combined report for Form Four / O-Level results (academic_level_id = 1) Handles both HTML and print versions """ from django.shortcuts import get_object_or_404 from django.db.models import Count, Q, Sum, F from .models import JointExamProject, StudentFinalResult, ProjectStudent, ProcessedSubjectScore, ExamSchoolResult, School # Check if print version is requested is_print = request.GET.get('print') == 'true' or 'print' in request.path project = get_object_or_404(JointExamProject, project_code=project_code) project_id = project.id academic_level_id = project.academic_level_id # Verify this is O-Level if academic_level_id != 1: return HttpResponse("This report is only for O-Level/Form Four results", status=400) # ========================= # GET REGION NAME # ========================= region_name = ( StudentFinalResult.objects .filter(project_id=project_id) .select_related("student__school__district__region") .values_list("student__school__district__region__name", flat=True) .first() ) # ========================= # PASS/FAIL CALCULATIONS # ========================= totals = ( StudentFinalResult.objects .filter(project_id=project_id) .aggregate( total=Count('id', filter=Q(division__in=['I', 'II', 'III', 'IV', '0'])), pass_=Count('id', filter=Q(division__in=['I', 'II', 'III'])), fail=Count('id', filter=Q(division__in=['0', 'IV'])), ) ) total = totals["total"] or 1 pass_pct = round(totals["pass_"] * 100 / total, 2) fail_pct = round(totals["fail"] * 100 / total, 2) # ========================= # REGISTRATION COUNTS # ========================= registered_counts = ProjectStudent.objects.filter( project_id=project_id ).values('sex').annotate(count=Count('id')) registered_f = 0 registered_m = 0 for item in registered_counts: if item['sex'] == 'F': registered_f = item['count'] elif item['sex'] == 'M': registered_m = item['count'] registered_total = registered_f + registered_m sat_counts = StudentFinalResult.objects.filter( project_id=project_id ).filter(division__in=['I', 'II', 'III', 'IV', '0']).values('student__sex').annotate(count=Count('id')) sat_f = 0 sat_m = 0 for item in sat_counts: if item['student__sex'] == 'F': sat_f = item['count'] elif item['student__sex'] == 'M': sat_m = item['count'] sat_total = sat_f + sat_m absent_f = registered_f - sat_f absent_m = registered_m - sat_m absent_total = absent_f + absent_m # ========================= # DIVISION BY GENDER (Overall) # ========================= DIV_KEYS = ["I", "II", "III", "IV", "0"] gender_div = {} rows = ( StudentFinalResult.objects .filter(project_id=project_id) .values("student__sex", "division") .annotate(t=Count("id")) ) for sex in ["F", "M"]: gender_div[sex] = {k: 0 for k in DIV_KEYS} for r in rows: sex = r["student__sex"] div = r["division"] if sex in gender_div and div in gender_div[sex]: gender_div[sex][div] = r["t"] gender_div["T"] = { k: gender_div["F"][k] + gender_div["M"][k] for k in DIV_KEYS } for sex in ["F", "M", "T"]: total_sat = sum(gender_div[sex].values()) gender_div[sex]["total_I_II"] = gender_div[sex]["I"] + gender_div[sex]["II"] gender_div[sex]["total_I_III"] = gender_div[sex]["I"] + gender_div[sex]["II"] + gender_div[sex]["III"] gender_div[sex]["total_I_IV"] = gender_div[sex]["I"] + gender_div[sex]["II"] + gender_div[sex]["III"] + gender_div[sex]["IV"] if total_sat > 0: gender_div[sex]["percent_I_II"] = round((gender_div[sex]["total_I_II"] / total_sat) * 100, 2) gender_div[sex]["percent_I_III"] = round((gender_div[sex]["total_I_III"] / total_sat) * 100, 2) gender_div[sex]["percent_I_IV"] = round((gender_div[sex]["total_I_IV"] / total_sat) * 100, 2) else: gender_div[sex]["percent_I_II"] = 0.00 gender_div[sex]["percent_I_III"] = 0.00 gender_div[sex]["percent_I_IV"] = 0.00 gender_div[sex]["total_sat"] = total_sat # ========================= # SCHOOL RANKINGS WITH DIVISION COUNTS BY GENDER # ========================= school_ranking = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).select_related('school', 'school__district').order_by('sch_gpa') # Build complete school data with division counts by gender enhanced_schools = [] for school_result in school_ranking: school = school_result.school # Get division counts by gender for this school division_by_gender = ( StudentFinalResult.objects .filter(project_id=project_id, student__school=school) .filter(division__in=['I', 'II', 'III', 'IV', '0']) .values('student__sex', 'division') .annotate(count=Count('id')) ) # Initialize counters # Female counts f_i = f_ii = f_iii = f_iv = f_o = 0 # Male counts m_i = m_ii = m_iii = m_iv = m_o = 0 # Total counts t_i = t_ii = t_iii = t_iv = t_o = 0 for item in division_by_gender: sex = item['student__sex'] div = item['division'] count = item['count'] if sex == 'F': if div == 'I': f_i = count elif div == 'II': f_ii = count elif div == 'III': f_iii = count elif div == 'IV': f_iv = count elif div == '0': f_o = count elif sex == 'M': if div == 'I': m_i = count elif div == 'II': m_ii = count elif div == 'III': m_iii = count elif div == 'IV': m_iv = count elif div == '0': m_o = count # Update totals if div == 'I': t_i += count elif div == 'II': t_ii += count elif div == 'III': t_iii += count elif div == 'IV': t_iv += count elif div == '0': t_o += count # Calculate totals total_sat = t_i + t_ii + t_iii + t_iv + t_o f_total_sat = f_i + f_ii + f_iii + f_iv + f_o m_total_sat = m_i + m_ii + m_iii + m_iv + m_o # Calculate pass rates pass_i_iii = t_i + t_ii + t_iii pass_i_iv = pass_i_iii + t_iv pass_i_iii_pct = round(pass_i_iii / total_sat * 100, 1) if total_sat > 0 else 0 pass_i_iv_pct = round(pass_i_iv / total_sat * 100, 1) if total_sat > 0 else 0 # Female pass rates f_pass_i_iii = f_i + f_ii + f_iii f_pass_i_iv = f_pass_i_iii + f_iv f_pass_i_iii_pct = round(f_pass_i_iii / f_total_sat * 100, 1) if f_total_sat > 0 else 0 f_pass_i_iv_pct = round(f_pass_i_iv / f_total_sat * 100, 1) if f_total_sat > 0 else 0 # Male pass rates m_pass_i_iii = m_i + m_ii + m_iii m_pass_i_iv = m_pass_i_iii + m_iv m_pass_i_iii_pct = round(m_pass_i_iii / m_total_sat * 100, 1) if m_total_sat > 0 else 0 m_pass_i_iv_pct = round(m_pass_i_iv / m_total_sat * 100, 1) if m_total_sat > 0 else 0 enhanced_school = { 'school': school, 'school_result': school_result, # Division counts by gender 'f_i': f_i, 'f_ii': f_ii, 'f_iii': f_iii, 'f_iv': f_iv, 'f_o': f_o, 'm_i': m_i, 'm_ii': m_ii, 'm_iii': m_iii, 'm_iv': m_iv, 'm_o': m_o, 't_i': t_i, 't_ii': t_ii, 't_iii': t_iii, 't_iv': t_iv, 't_o': t_o, # Total SAT by gender 'f_total_sat': f_total_sat, 'm_total_sat': m_total_sat, 'total_sat': total_sat, # Pass rates by gender 'f_pass_i_iii': f_pass_i_iii, 'f_pass_i_iii_pct': f_pass_i_iii_pct, 'f_pass_i_iv': f_pass_i_iv, 'f_pass_i_iv_pct': f_pass_i_iv_pct, 'm_pass_i_iii': m_pass_i_iii, 'm_pass_i_iii_pct': m_pass_i_iii_pct, 'm_pass_i_iv': m_pass_i_iv, 'm_pass_i_iv_pct': m_pass_i_iv_pct, 'pass_i_iii': pass_i_iii, 'pass_i_iii_pct': pass_i_iii_pct, 'pass_i_iv': pass_i_iv, 'pass_i_iv_pct': pass_i_iv_pct, # GPA and other fields 'gpa': school_result.sch_gpa, 'gpa_grade': school_result.sch_gpa_grade, 'gpa_status': school_result.sch_gpa_status, 'with_results': school_result.with_results, } enhanced_schools.append(enhanced_school) # Create ranking lists from enhanced schools best_10_all = enhanced_schools[:10] best_10_gvt = [s for s in enhanced_schools if s['school'].ownership == "GVT"][:10] best_10_nongvt = [s for s in enhanced_schools if s['school'].ownership == "NON-GVT"][:10] # Schools with 30+ and less than 30 candidates best_40_std_andabove = [s for s in enhanced_schools if s['total_sat'] >= 30][:10] best_40_less = [s for s in enhanced_schools if s['total_sat'] < 30][:10] # In combined_form_four_report function # ====================================================== # SCHOOL STATISTICS COUNTS (Count only students who SAT) # ====================================================== from django.db.models import Count, Q, Sum # Annotate each school result with student count (only those who SAT) school_stats = school_ranking.annotate( student_count=Count( 'school__projectstudent__studentfinalresult', filter=Q(school__projectstudent__studentfinalresult__project_id=project_id) & ~Q(school__projectstudent__studentfinalresult__division__iexact='ABS'), distinct=True ) ) # Total schools count total_schools_count = school_stats.count() # Total students count (only those who SAT) - Sum of student_count total_students_count = sum(s.student_count for s in school_stats) # Government schools govt_schools = [s for s in school_stats if s.school.ownership == "GVT"] govt_schools_count = len(govt_schools) govt_students_count = sum(s.student_count for s in govt_schools) # Non-Government schools nongvt_schools = [s for s in school_stats if s.school.ownership == "NON-GVT"] nongvt_schools_count = len(nongvt_schools) nongvt_students_count = sum(s.student_count for s in nongvt_schools) # Print for debugging print(f"DEBUG: Total Schools: {total_schools_count} ({total_students_count} students who SAT)") print(f"DEBUG: Government Schools: {govt_schools_count} ({govt_students_count} students who SAT)") print(f"DEBUG: Non-Government Schools: {nongvt_schools_count} ({nongvt_students_count} students who SAT)") # Private schools (if any other ownership types) private_schools = [s for s in enhanced_schools if s['school'].ownership not in ["GVT", "NON-GVT"]] private_schools_count = len(private_schools) private_students_count = sum(s['total_sat'] for s in private_schools) # ========================= # GPA CALCULATIONS # ========================= subject_scores = ProcessedSubjectScore.objects.filter(project_id=project_id) grade_counts = { 'A': subject_scores.filter(grade='A').count(), 'B': subject_scores.filter(grade='B').count(), 'C': subject_scores.filter(grade='C').count(), 'D': subject_scores.filter(grade='D').count(), 'F': subject_scores.filter(grade='F').count(), } total_subject_grades = sum(grade_counts.values()) if total_subject_grades > 0: subject_gpa = ( grade_counts['A'] * 1 + grade_counts['B'] * 2 + grade_counts['C'] * 3 + grade_counts['D'] * 4 + grade_counts['F'] * 5 ) / total_subject_grades else: subject_gpa = 0 division_gpa = 0 total_divisions = gender_div['T']['I'] + gender_div['T']['II'] + gender_div['T']['III'] + gender_div['T']['IV'] + gender_div['T']['0'] if total_divisions > 0: division_gpa = ( gender_div['T']['I'] * 1 + gender_div['T']['II'] * 2 + gender_div['T']['III'] * 3 + gender_div['T']['IV'] * 4 + gender_div['T']['0'] * 5 ) / total_divisions if subject_gpa > 0 and division_gpa > 0: overall_gpa = (subject_gpa + division_gpa) / 2 elif subject_gpa > 0: overall_gpa = subject_gpa elif division_gpa > 0: overall_gpa = division_gpa else: overall_gpa = 0 subject_gpa = round(subject_gpa, 4) division_gpa = round(division_gpa, 4) overall_gpa = round(overall_gpa, 4) overall_table = { 'divisions': { 'I': gender_div['T']['I'], 'II': gender_div['T']['II'], 'III': gender_div['T']['III'], 'IV': gender_div['T']['IV'], '0': gender_div['T']['0'] }, 'grades': grade_counts, 'subject_gpa': subject_gpa, 'division_gpa': division_gpa, 'overall_gpa': overall_gpa, } # ========================= # BEST STUDENTS # ========================= best_students = ( StudentFinalResult.objects .filter(project_id=project_id, total_points__gte=1) .select_related('student', 'student__school', 'student__combination') .order_by('total_points', '-std_average')[:10] # Lowest points first, then highest average ) best_girls = ( StudentFinalResult.objects .filter(project_id=project_id, student__sex="F", total_points__gte=1) .select_related('student', 'student__school') .order_by('total_points', '-std_average')[:10] # Lowest points first, then highest average ) best_boys = ( StudentFinalResult.objects .filter(project_id=project_id, student__sex="M", total_points__gte=1) .select_related('student', 'student__school') .order_by('total_points', '-std_average')[:10] # Lowest points first, then highest average ) best_gvt_students = ( StudentFinalResult.objects .filter(project_id=project_id, student__school__ownership="GVT", total_points__gte=1) .select_related('student', 'student__school') .order_by('total_points', '-std_average')[:10] # Lowest points first, then highest average ) best_nongvt_students = ( StudentFinalResult.objects .filter(project_id=project_id, student__school__ownership="NON-GVT", total_points__gte=1) .select_related('student', 'student__school') .order_by('total_points', '-std_average')[:10] # Lowest points first, then highest average ) # ========================= # SUBJECT PERFORMANCE # ========================= subject_data = ( ProcessedSubjectScore.objects .filter(project_id=project_id) .values("subject__subject_code", "subject__subject_name_eng") .annotate( sat=Count("id"), A=Count("id", filter=Q(grade="A")), B=Count("id", filter=Q(grade="B")), C=Count("id", filter=Q(grade="C")), D=Count("id", filter=Q(grade="D")), F=Count("id", filter=Q(grade="F")), ) .order_by("subject__subject_code") ) processed_subject_summary = [] subject_totals = {'A': 0, 'B': 0, 'C': 0, 'D': 0, 'F': 0, 'sat': 0} for subject in subject_data: A = subject['A'] or 0 B = subject['B'] or 0 C = subject['C'] or 0 D = subject['D'] or 0 F = subject['F'] or 0 sat = subject['sat'] or 0 pass_total = A + B + C + D pass_percent = round((pass_total / sat) * 100, 2) if sat > 0 else 0 if sat > 0: gpa_numerator = (A * 1 + B * 2 + C * 3 + D * 4 + F * 5) subject_gpa_calc = round(gpa_numerator / sat, 4) else: subject_gpa_calc = 0 processed_subject_summary.append({ 'subject_code': subject['subject__subject_code'], 'subject_name': subject['subject__subject_name_eng'], 'sat': sat, 'A': A, 'B': B, 'C': C, 'D': D, 'F': F, 'pass_total': pass_total, 'pass_percent': pass_percent, 'subject_gpa': subject_gpa_calc, }) subject_totals['A'] += A subject_totals['B'] += B subject_totals['C'] += C subject_totals['D'] += D subject_totals['F'] += F subject_totals['sat'] += sat if subject_totals['sat'] > 0: subject_totals['overall_pass_percent'] = round( (subject_totals['A'] + subject_totals['B'] + subject_totals['C'] + subject_totals['D']) / subject_totals['sat'] * 100, 2 ) top_subjects_by_gpa = sorted(processed_subject_summary, key=lambda x: x['subject_gpa'])[:10] # ====================================================== # BEST STUDENTS PER SUBJECT (Rank by highest percentage_score) # ====================================================== import logging logger = logging.getLogger(__name__) # Get distinct subjects that have scores subjects_with_scores = ( ProcessedSubjectScore.objects .filter( project_id=project_id, percentage_score__isnull=False, grade__isnull=False ) .exclude(percentage_score='') .exclude(percentage_score='NULL') .exclude(percentage_score='X') .exclude(percentage_score='x') .values_list('subject__subject_code', flat=True) .distinct() .order_by('subject__subject_code') ) logger.info(f"DEBUG: Subjects with scores: {list(subjects_with_scores)}") subject_best_list = [] for subject_code in subjects_with_scores: # Get subject name try: subject = ExamSubject.objects.filter(subject_code=subject_code).first() subject_name = subject.subject_name_eng if subject else subject_code except: subject_name = subject_code # Get all scores for this subject all_scores = ProcessedSubjectScore.objects.filter( project_id=project_id, subject__subject_code=subject_code, percentage_score__isnull=False, grade__isnull=False ).exclude( percentage_score='' ).exclude( percentage_score='NULL' ).exclude( percentage_score__iexact='null' ).exclude( percentage_score='X' ).exclude( percentage_score='x' ).select_related( 'student', 'student__school', 'student__combination' ) logger.info(f"DEBUG: Subject {subject_code} - total scores: {all_scores.count()}") # Process scores in Python - convert to float scores_with_float = [] for score in all_scores: try: # Convert string to float percent_float = float(score.percentage_score) scores_with_float.append({ 'score': score, 'percent_float': percent_float }) except (ValueError, TypeError, AttributeError): continue logger.info(f"DEBUG: Subject {subject_code} - valid numeric scores: {len(scores_with_float)}") if len(scores_with_float) == 0: continue # Sort by percentage descending (highest first) scores_with_float.sort(key=lambda x: x['percent_float'], reverse=True) # Take top 10 top_scores = scores_with_float[:10] # Process student data student_list = [] for rank, item in enumerate(top_scores, start=1): student_score = item['score'] percentage_raw = item['percent_float'] percentage_display = f"{percentage_raw:.0f}" # Safely get school name school_name = student_score.student.school.name_short or student_score.student.school.name district_name = student_score.student.school.district.name if student_score.student.school.district else '' student_list.append({ 'rank': rank, 'student_number': student_score.student.student_number, 'full_name': student_score.student.full_name, 'sex': student_score.student.sex, 'school_name': school_name, 'district': district_name, 'school_ownership': student_score.student.school.ownership, 'percentage_score': percentage_display, 'percentage_raw': percentage_raw, 'grade': student_score.grade, 'points': student_score.points, }) if student_list: subject_best_list.append({ 'code': subject_code, 'name': subject_name, 'rows': student_list }) logger.info(f"DEBUG: Added subject {subject_code} with {len(student_list)} students") # Limit to top 20 subjects subject_best_list = subject_best_list[:20] logger.info(f"DEBUG: FINAL subject_best_list length: {len(subject_best_list)}") # ====================================================== # SCHOOL RANKING TABLES # ====================================================== # Get all schools with valid results school_results = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).select_related('school', 'school__district', 'school__district__region') school_ranking_data = [] for school_result in school_results: school = school_result.school # Get division counts for this school division_counts = StudentFinalResult.objects.filter( project_id=project_id, student__school=school ).filter(division__in=['I', 'II', 'III', 'IV', '0']) total_sat = division_counts.count() if total_sat == 0: continue div_i = division_counts.filter(division='I').count() div_ii = division_counts.filter(division='II').count() div_iii = division_counts.filter(division='III').count() div_iv = division_counts.filter(division='IV').count() div_o = division_counts.filter(division='0').count() pass_i_iii = div_i + div_ii + div_iii pass_i_iv = pass_i_iii + div_iv pass_i_iii_pct = round(pass_i_iii / total_sat * 100, 2) if total_sat > 0 else 0 pass_i_iv_pct = round(pass_i_iv / total_sat * 100, 2) if total_sat > 0 else 0 # Get ownership display ownership_display = "GVT" if school.ownership == "GVT" else "NON-GVT" school_ranking_data.append({ 'school': school, 'school_name': school.name, 'name_short': school.name_short or school.name, 'school_code': school.school_code, 'district': school.district.name, 'region': school.district.region.name, 'ownership': ownership_display, 'total_sat': total_sat, 't_i': div_i, 't_ii': div_ii, 't_iii': div_iii, 't_iv': div_iv, 't_o': div_o, 'pass_i_iii': pass_i_iii, 'pass_i_iii_pct': pass_i_iii_pct, 'pass_i_iv': pass_i_iv, 'pass_i_iv_pct': pass_i_iv_pct, 'gpa': school_result.sch_gpa, 'gpa_grade': school_result.sch_gpa_grade, 'gpa_status': school_result.sch_gpa_status, }) # Sort by GPA (lower is better) school_ranking_data.sort(key=lambda x: x['gpa']) # Assign overall ranks for idx, school in enumerate(school_ranking_data, 1): school['overall_rank'] = idx # Group by region for region-wise ranks regions = {} for school in school_ranking_data: region = school['region'] if region not in regions: regions[region] = [] regions[region].append(school) for region, schools in regions.items(): schools.sort(key=lambda x: x['gpa']) for idx, school in enumerate(schools, 1): school['region_rank'] = idx # Group by district for district-wise ranks districts = {} for school in school_ranking_data: district = school['district'] if district not in districts: districts[district] = [] districts[district].append(school) for district, schools in districts.items(): schools.sort(key=lambda x: x['gpa']) for idx, school in enumerate(schools, 1): school['district_rank'] = idx # Create separate lists for different categories school_ranking_overall = school_ranking_data.copy() school_ranking_30plus = [s for s in school_ranking_data if s['total_sat'] >= 40] school_ranking_under30 = [s for s in school_ranking_data if s['total_sat'] < 40] # Top 10 lists for different categories best_10_all = school_ranking_overall[:10] best_10_gvt = [s for s in school_ranking_overall if s['ownership'] == "GVT"][:10] best_10_nongvt = [s for s in school_ranking_overall if s['ownership'] == "NON-GVT"][:10] best_40_std_andabove = school_ranking_30plus[:10] best_40_less = school_ranking_under30[:10] # ========================= # CHOOSE TEMPLATE BASED ON PRINT PARAMETER # ========================= template_name = "exams/combined_report_olevel_print.html" if is_print else "exams/combined_report_olevel.html" context = { 'project': project, 'region_name': region_name, 'pass_pct': pass_pct, 'fail_pct': fail_pct, 'gender_div': gender_div, 'overall_table': overall_table, 'registered_f': registered_f, 'registered_m': registered_m, 'registered_total': registered_total, 'sat_f': sat_f, 'sat_m': sat_m, 'sat_total': sat_total, 'absent_f': absent_f, 'absent_m': absent_m, 'absent_total': absent_total, # Enhanced school data with gender division counts 'best_10_all': best_10_all, 'best_10_gvt': best_10_gvt, 'best_10_nongvt': best_10_nongvt, 'best_40_std_andabove': best_40_std_andabove, # School Statistics Counts (NEW) 'total_schools_count': total_schools_count, 'total_students_count': total_students_count, 'govt_schools_count': govt_schools_count, 'govt_students_count': govt_students_count, 'nongvt_schools_count': nongvt_schools_count, 'nongvt_students_count': nongvt_students_count, 'private_schools_count': private_schools_count, 'private_students_count': private_students_count, 'best_40_less': best_40_less, 'best_students': best_students, 'best_girls': best_girls, 'best_boys': best_boys, 'best_gvt_students': best_gvt_students, 'best_nongvt_students': best_nongvt_students, 'processed_subject_summary': processed_subject_summary, 'subject_totals': subject_totals, 'top_subjects_by_gpa': top_subjects_by_gpa, 'academic_level_id': academic_level_id, 'subject_best_list': subject_best_list, 'school_ranking_overall': school_ranking_overall, 'school_ranking_30plus': school_ranking_30plus, 'school_ranking_under30': school_ranking_under30, 'is_print_version': is_print, } return render(request, template_name, context) def combined_primary_report(request, project_code): """ Combined report for Primary Level results (academic_level_id = 3) """ from django.shortcuts import get_object_or_404 from .models import JointExamProject project = get_object_or_404(JointExamProject, project_code=project_code) project_id = project.id academic_level_id = project.academic_level_id # Verify this is Primary Level if academic_level_id != 3: return HttpResponse("This report is only for Primary Level results", status=400) # ========================= # GET REGION CORRECTLY # ========================= region_name = ( StudentFinalResult.objects .filter(project_id=project_id) .select_related("student__school__district__region") .values_list( "student__school__district__region__name", flat=True ) .first() ) # ========================= # PAGE 1 — PASS/FAIL BASED ON GRADES (A-C = Pass, D-E = Fail) # ========================= from django.db.models import Sum, Q, Count # Get all processed subject scores subject_scores = ProcessedSubjectScore.objects.filter(project_id=project_id) # Count passes (Grades A, B, C) and fails (Grades D, E) pass_count = subject_scores.filter(grade__in=['A', 'B', 'C']).count() fail_count = subject_scores.filter(grade__in=['D', 'E']).count() total_scores = subject_scores.exclude(grade__isnull=True).count() pass_pct = round(pass_count * 100 / total_scores, 2) if total_scores > 0 else 0 fail_pct = round(fail_count * 100 / total_scores, 2) if total_scores > 0 else 0 # ========================= # REGISTRATION SUMMARY # ========================= registered_counts = ProjectStudent.objects.filter( project_id=project_id ).values('sex').annotate(count=Count('id')) registered_f = 0 registered_m = 0 for item in registered_counts: if item['sex'] == 'F': registered_f = item['count'] elif item['sex'] == 'M': registered_m = item['count'] registered_total = registered_f + registered_m sat_counts = StudentFinalResult.objects.filter( project_id=project_id ).filter(division__in=['I', 'II', 'III', 'IV', '0']).values('student__sex').annotate(count=Count('id')) sat_f = 0 sat_m = 0 for item in sat_counts: if item['student__sex'] == 'F': sat_f = item['count'] elif item['student__sex'] == 'M': sat_m = item['count'] sat_total = sat_f + sat_m absent_f = registered_f - sat_f absent_m = registered_m - sat_m absent_total = absent_f + absent_m # ========================= # GRADE DISTRIBUTION BY GENDER # ========================= grade_distribution = { 'F': {'A': 0, 'B': 0, 'C': 0, 'D': 0, 'E': 0}, 'M': {'A': 0, 'B': 0, 'C': 0, 'D': 0, 'E': 0}, 'T': {'A': 0, 'B': 0, 'C': 0, 'D': 0, 'E': 0} } grade_counts = subject_scores.values('grade', 'student__sex').annotate(count=Count('id')) for item in grade_counts: grade = item['grade'] sex = item['student__sex'] count = item['count'] if grade in grade_distribution['T']: if sex == 'F': grade_distribution['F'][grade] += count elif sex == 'M': grade_distribution['M'][grade] += count grade_distribution['T'][grade] += count # Calculate totals for sex in ['F', 'M', 'T']: grade_distribution[sex]['total'] = sum(grade_distribution[sex].values()) grade_distribution[sex]['pass'] = grade_distribution[sex]['A'] + grade_distribution[sex]['B'] + grade_distribution[sex]['C'] grade_distribution[sex]['pass_pct'] = round(grade_distribution[sex]['pass'] / grade_distribution[sex]['total'] * 100, 2) if grade_distribution[sex]['total'] > 0 else 0 grade_distribution[sex]['fail'] = grade_distribution[sex]['D'] + grade_distribution[sex]['E'] grade_distribution[sex]['fail_pct'] = round(grade_distribution[sex]['fail'] / grade_distribution[sex]['total'] * 100, 2) if grade_distribution[sex]['total'] > 0 else 0 # ====================================================== # GPA CALCULATION FOR PRIMARY (A=1, B=2, C=3, D=4, E=5) # ====================================================== grade_counts_gpa = { 'A': subject_scores.filter(grade='A').count(), 'B': subject_scores.filter(grade='B').count(), 'C': subject_scores.filter(grade='C').count(), 'D': subject_scores.filter(grade='D').count(), 'E': subject_scores.filter(grade='E').count(), } total_grades = sum(grade_counts_gpa.values()) if total_grades > 0: overall_gpa = ( grade_counts_gpa['A'] * 1 + grade_counts_gpa['B'] * 2 + grade_counts_gpa['C'] * 3 + grade_counts_gpa['D'] * 4 + grade_counts_gpa['E'] * 5 ) / total_grades else: overall_gpa = 0 overall_gpa = round(overall_gpa, 4) overall_table = { 'grades': grade_counts_gpa, 'overall_gpa': overall_gpa, } # ====================================================== # SCHOOL RANKINGS (Based on Average Score) # ====================================================== school_ranking = ExamSchoolResult.objects.filter( project_id=project_id, sch_avg__isnull=False ).select_related('school', 'school__district').order_by('-sch_avg') # Higher average is better best_10_all = school_ranking[:10] best_10_gvt = school_ranking.filter(school__ownership="GVT")[:10] best_10_nongvt = school_ranking.filter(school__ownership="NON-GVT")[:10] # ====================================================== # BEST STUDENTS (Highest Average Score) # ====================================================== best_students = ( StudentFinalResult.objects .filter(project_id=project_id) .exclude(std_average__isnull=True) .exclude(std_average='') .select_related('student', 'student__school') .order_by('-std_average')[:10] ) best_girls = ( StudentFinalResult.objects .filter(project_id=project_id, student__sex="F") .exclude(std_average__isnull=True) .select_related('student', 'student__school') .order_by('-std_average')[:10] ) best_boys = ( StudentFinalResult.objects .filter(project_id=project_id, student__sex="M") .exclude(std_average__isnull=True) .select_related('student', 'student__school') .order_by('-std_average')[:10] ) best_gvt_students = ( StudentFinalResult.objects .filter(project_id=project_id, student__school__ownership="GVT") .exclude(std_average__isnull=True) .select_related('student', 'student__school') .order_by('-std_average')[:10] ) best_nongvt_students = ( StudentFinalResult.objects .filter(project_id=project_id, student__school__ownership="NON-GVT") .exclude(std_average__isnull=True) .select_related('student', 'student__school') .order_by('-std_average')[:10] ) # ====================================================== # SUBJECT SUMMARY FOR PRIMARY # ====================================================== subject_data = ( ProcessedSubjectScore.objects .filter(project_id=project_id) .values("subject__subject_code", "subject__subject_name_eng") .annotate( sat=Count("id"), A=Count("id", filter=Q(grade="A")), B=Count("id", filter=Q(grade="B")), C=Count("id", filter=Q(grade="C")), D=Count("id", filter=Q(grade="D")), E=Count("id", filter=Q(grade="E")), ) .order_by("subject__subject_code") ) processed_subject_summary = [] subject_totals = {'A': 0, 'B': 0, 'C': 0, 'D': 0, 'E': 0, 'sat': 0} for subject in subject_data: A = subject['A'] or 0 B = subject['B'] or 0 C = subject['C'] or 0 D = subject['D'] or 0 E = subject['E'] or 0 sat = subject['sat'] or 0 pass_total = A + B + C pass_percent = round((pass_total / sat) * 100, 2) if sat > 0 else 0 if sat > 0: gpa_numerator = (A * 1 + B * 2 + C * 3 + D * 4 + E * 5) subject_gpa = round(gpa_numerator / sat, 4) else: subject_gpa = 0 processed_subject_summary.append({ 'subject_code': subject['subject__subject_code'], 'subject_name': subject['subject__subject_name_eng'], 'sat': sat, 'A': A, 'B': B, 'C': C, 'D': D, 'E': E, 'pass_total': pass_total, 'pass_percent': pass_percent, 'subject_gpa': subject_gpa, 'gpa_display': f"{subject_gpa:.4f}", }) subject_totals['A'] += A subject_totals['B'] += B subject_totals['C'] += C subject_totals['D'] += D subject_totals['E'] += E subject_totals['sat'] += sat if subject_totals['sat'] > 0: subject_totals['overall_pass_percent'] = round( (subject_totals['A'] + subject_totals['B'] + subject_totals['C']) / subject_totals['sat'] * 100, 2 ) top_subjects_by_gpa = sorted(processed_subject_summary, key=lambda x: x['subject_gpa'])[:10] context = { 'project': project, 'region_name': region_name, 'pass_pct': pass_pct, 'fail_pct': fail_pct, 'grade_distribution': grade_distribution, 'overall_table': overall_table, 'registered_f': registered_f, 'registered_m': registered_m, 'registered_total': registered_total, 'sat_f': sat_f, 'sat_m': sat_m, 'sat_total': sat_total, 'absent_f': absent_f, 'absent_m': absent_m, 'absent_total': absent_total, 'best_10_all': best_10_all, 'best_10_gvt': best_10_gvt, 'best_10_nongvt': best_10_nongvt, 'best_students': best_students, 'best_girls': best_girls, 'best_boys': best_boys, 'best_gvt_students': best_gvt_students, 'best_nongvt_students': best_nongvt_students, 'processed_subject_summary': processed_subject_summary, 'subject_totals': subject_totals, 'top_subjects_by_gpa': top_subjects_by_gpa, 'academic_level_id': academic_level_id, } return render(request, "exams/combined_report_primary.html", context) # exams/views_reports_combined.py - Add this function def combined_form_four_report_excel(request, project_code): """ Export O-Level combined report to Excel """ import openpyxl from openpyxl.styles import Font, Alignment, PatternFill, Border, Side from openpyxl.utils import get_column_letter from django.http import HttpResponse from django.shortcuts import get_object_or_404 project = get_object_or_404(JointExamProject, project_code=project_code) project_id = project.id academic_level_id = project.academic_level_id # Verify this is O-Level if academic_level_id != 1: return HttpResponse("This report is only for O-Level/Form Four results", status=400) # Get the same data as the HTML view region_name = ( StudentFinalResult.objects .filter(project_id=project_id) .select_related("student__school__district__region") .values_list("student__school__district__region__name", flat=True) .first() ) # Registration and attendance data registered_counts = ProjectStudent.objects.filter( project_id=project_id ).values('sex').annotate(count=Count('id')) registered_f = 0 registered_m = 0 for item in registered_counts: if item['sex'] == 'F': registered_f = item['count'] elif item['sex'] == 'M': registered_m = item['count'] registered_total = registered_f + registered_m sat_counts = StudentFinalResult.objects.filter( project_id=project_id ).filter(division__in=['I', 'II', 'III', 'IV', '0']).values('student__sex').annotate(count=Count('id')) sat_f = 0 sat_m = 0 for item in sat_counts: if item['student__sex'] == 'F': sat_f = item['count'] elif item['student__sex'] == 'M': sat_m = item['count'] sat_total = sat_f + sat_m absent_f = registered_f - sat_f absent_m = registered_m - sat_m absent_total = absent_f + absent_m # Division by gender data DIV_KEYS = ["I", "II", "III", "IV", "0"] gender_div = {} rows = ( StudentFinalResult.objects .filter(project_id=project_id) .values("student__sex", "division") .annotate(t=Count("id")) ) for sex in ["F", "M"]: gender_div[sex] = {k: 0 for k in DIV_KEYS} for r in rows: sex = r["student__sex"] div = r["division"] if sex in gender_div and div in gender_div[sex]: gender_div[sex][div] = r["t"] gender_div["T"] = { k: gender_div["F"][k] + gender_div["M"][k] for k in DIV_KEYS } for sex in ["F", "M", "T"]: total_sat = sum(gender_div[sex].values()) gender_div[sex]["total_I_III"] = gender_div[sex]["I"] + gender_div[sex]["II"] + gender_div[sex]["III"] gender_div[sex]["total_I_IV"] = gender_div[sex]["I"] + gender_div[sex]["II"] + gender_div[sex]["III"] + gender_div[sex]["IV"] gender_div[sex]["percent_I_III"] = round((gender_div[sex]["total_I_III"] / total_sat) * 100, 2) if total_sat > 0 else 0 gender_div[sex]["percent_I_IV"] = round((gender_div[sex]["total_I_IV"] / total_sat) * 100, 2) if total_sat > 0 else 0 gender_div[sex]["total_sat"] = total_sat # GPA calculations subject_scores = ProcessedSubjectScore.objects.filter(project_id=project_id) grade_counts = { 'A': subject_scores.filter(grade='A').count(), 'B': subject_scores.filter(grade='B').count(), 'C': subject_scores.filter(grade='C').count(), 'D': subject_scores.filter(grade='D').count(), 'F': subject_scores.filter(grade='F').count(), } total_subject_grades = sum(grade_counts.values()) if total_subject_grades > 0: subject_gpa = (grade_counts['A'] * 1 + grade_counts['B'] * 2 + grade_counts['C'] * 3 + grade_counts['D'] * 4 + grade_counts['F'] * 5) / total_subject_grades else: subject_gpa = 0 total_divisions = gender_div['T']['I'] + gender_div['T']['II'] + gender_div['T']['III'] + gender_div['T']['IV'] + gender_div['T']['0'] if total_divisions > 0: division_gpa = (gender_div['T']['I'] * 1 + gender_div['T']['II'] * 2 + gender_div['T']['III'] * 3 + gender_div['T']['IV'] * 4 + gender_div['T']['0'] * 5) / total_divisions else: division_gpa = 0 overall_gpa = round((subject_gpa + division_gpa) / 2, 4) if subject_gpa > 0 and division_gpa > 0 else round(subject_gpa or division_gpa, 4) subject_gpa = round(subject_gpa, 4) division_gpa = round(division_gpa, 4) # School rankings school_ranking = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).select_related('school', 'school__district').order_by('sch_gpa') best_10_all = school_ranking[:10] best_10_gvt = school_ranking.filter(school__ownership="GVT")[:10] best_10_nongvt = school_ranking.filter(school__ownership="NON-GVT")[:10] # Best students best_students = StudentFinalResult.objects.filter(project_id=project_id, total_points__gte=1).select_related('student', 'student__school').order_by('total_points')[:10] best_girls = StudentFinalResult.objects.filter(project_id=project_id, student__sex="F", total_points__gte=1).select_related('student', 'student__school').order_by('total_points')[:10] best_boys = StudentFinalResult.objects.filter(project_id=project_id, student__sex="M", total_points__gte=1).select_related('student', 'student__school').order_by('total_points')[:10] # Subject performance subject_data = ProcessedSubjectScore.objects.filter(project_id=project_id).values("subject__subject_code", "subject__subject_name_eng").annotate( sat=Count("id"), A=Count("id", filter=Q(grade="A")), B=Count("id", filter=Q(grade="B")), C=Count("id", filter=Q(grade="C")), D=Count("id", filter=Q(grade="D")), F=Count("id", filter=Q(grade="F")), ).order_by("subject__subject_code") processed_subject_summary = [] subject_totals = {'A': 0, 'B': 0, 'C': 0, 'D': 0, 'F': 0, 'sat': 0} for subject in subject_data: A = subject['A'] or 0 B = subject['B'] or 0 C = subject['C'] or 0 D = subject['D'] or 0 F = subject['F'] or 0 sat = subject['sat'] or 0 pass_total = A + B + C + D pass_percent = round((pass_total / sat) * 100, 2) if sat > 0 else 0 subject_gpa_calc = round((A*1 + B*2 + C*3 + D*4 + F*5) / sat, 4) if sat > 0 else 0 processed_subject_summary.append({ 'subject_code': subject['subject__subject_code'], 'subject_name': subject['subject__subject_name_eng'], 'sat': sat, 'A': A, 'B': B, 'C': C, 'D': D, 'F': F, 'pass_total': pass_total, 'pass_percent': pass_percent, 'subject_gpa': subject_gpa_calc, }) for key in ['A', 'B', 'C', 'D', 'F', 'sat']: subject_totals[key] += locals()[key] if subject_totals['sat'] > 0: subject_totals['overall_pass_percent'] = round((subject_totals['A'] + subject_totals['B'] + subject_totals['C'] + subject_totals['D']) / subject_totals['sat'] * 100, 2) # Create Excel workbook wb = openpyxl.Workbook() # Styles title_font = Font(bold=True, size=14) header_font = Font(bold=True, color="FFFFFF") header_fill = PatternFill(start_color="1a3e6f", end_color="1a3e6f", fill_type="solid") center_align = Alignment(horizontal="center", vertical="center") thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) # ==================== SHEET 1: SUMMARY ==================== ws1 = wb.active ws1.title = "Summary" # Title ws1.merge_cells('A1:K1') ws1['A1'] = f"O-LEVEL COMBINED REPORT - {project.name} ({project.year})" ws1['A1'].font = title_font ws1['A1'].alignment = center_align ws1.merge_cells('A2:K2') ws1['A2'] = f"Region: {region_name or 'All Regions'}" ws1['A2'].alignment = center_align ws1.append([]) # Registration Summary ws1['A4'] = "REGISTRATION SUMMARY" ws1['A4'].font = Font(bold=True, size=12) reg_data = [ ['Description', 'Female', 'Male', 'Total'], ['Registered Students', registered_f, registered_m, registered_total], ['Students Who Sat', sat_f, sat_m, sat_total], ['Absentees', absent_f, absent_m, absent_total], ] for row_data in reg_data: ws1.append(row_data) ws1.append([]) # GPA Summary ws1['A8'] = "GPA SUMMARY" ws1['A8'].font = Font(bold=True, size=12) gpa_data = [ ['Subject GPA', subject_gpa], ['Division GPA', division_gpa], ['Overall GPA', overall_gpa], ] for row_data in gpa_data: ws1.append(row_data) # ==================== SHEET 2: DIVISION BY GENDER ==================== ws2 = wb.create_sheet("Division by Gender") ws2['A1'] = f"DIVISION PERFORMANCE BY GENDER - {project.name}" ws2['A1'].font = title_font ws2.merge_cells('A1:K1') headers = ['SEX', 'I', 'II', 'III', 'IV', '0', 'I-III Total', 'I-III %', 'I-IV Total', 'I-IV %', 'Total SAT'] ws2.append(headers) for col, header in enumerate(headers, 1): cell = ws2.cell(row=2, column=col) cell.value = header cell.font = header_font cell.fill = header_fill cell.alignment = center_align row_num = 3 for sex in ['F', 'M', 'T']: sex_label = 'FEMALE' if sex == 'F' else 'MALE' if sex == 'M' else 'TOTAL' ws2.cell(row=row_num, column=1, value=sex_label) ws2.cell(row=row_num, column=2, value=gender_div[sex]['I']) ws2.cell(row=row_num, column=3, value=gender_div[sex]['II']) ws2.cell(row=row_num, column=4, value=gender_div[sex]['III']) ws2.cell(row=row_num, column=5, value=gender_div[sex]['IV']) ws2.cell(row=row_num, column=6, value=gender_div[sex]['0']) ws2.cell(row=row_num, column=7, value=gender_div[sex]['total_I_III']) ws2.cell(row=row_num, column=8, value=gender_div[sex]['percent_I_III']) ws2.cell(row=row_num, column=9, value=gender_div[sex]['total_I_IV']) ws2.cell(row=row_num, column=10, value=gender_div[sex]['percent_I_IV']) ws2.cell(row=row_num, column=11, value=gender_div[sex]['total_sat']) row_num += 1 # ==================== SHEET 3: SCHOOL RANKINGS ==================== ws3 = wb.create_sheet("School Rankings") ws3['A1'] = f"TOP 10 SCHOOLS - {project.name}" ws3['A1'].font = title_font ws3.merge_cells('A1:E1') # Overall Top 10 ws3['A3'] = "OVERALL TOP 10 SCHOOLS" ws3['A3'].font = Font(bold=True) headers = ['Rank', 'School Code', 'School Name', 'GPA', 'Grade'] ws3.append(headers) for col, header in enumerate(headers, 1): cell = ws3.cell(row=4, column=col) cell.font = header_font cell.fill = header_fill row_num = 5 for idx, s in enumerate(best_10_all, 1): ws3.cell(row=row_num, column=1, value=idx) ws3.cell(row=row_num, column=2, value=s.school.school_code) ws3.cell(row=row_num, column=3, value=s.school.name_short) ws3.cell(row=row_num, column=4, value=round(s.sch_gpa, 4) if s.sch_gpa else 0) ws3.cell(row=row_num, column=5, value=s.sch_gpa_grade or '-') row_num += 1 # Government Top 10 row_num += 2 ws3.cell(row=row_num, column=1, value="GOVERNMENT TOP 10 SCHOOLS").font = Font(bold=True) row_num += 1 ws3.append(headers) for col, header in enumerate(headers, 1): cell = ws3.cell(row=row_num, column=col) cell.font = header_font cell.fill = header_fill row_num += 1 for idx, s in enumerate(best_10_gvt, 1): ws3.cell(row=row_num, column=1, value=idx) ws3.cell(row=row_num, column=2, value=s.school.school_code) ws3.cell(row=row_num, column=3, value=s.school.name_short) ws3.cell(row=row_num, column=4, value=round(s.sch_gpa, 4) if s.sch_gpa else 0) ws3.cell(row=row_num, column=5, value=s.sch_gpa_grade or '-') row_num += 1 # Non-Government Top 10 row_num += 2 ws3.cell(row=row_num, column=1, value="NON-GOVERNMENT TOP 10 SCHOOLS").font = Font(bold=True) row_num += 1 ws3.append(headers) for col, header in enumerate(headers, 1): cell = ws3.cell(row=row_num, column=col) cell.font = header_font cell.fill = header_fill row_num += 1 for idx, s in enumerate(best_10_nongvt, 1): ws3.cell(row=row_num, column=1, value=idx) ws3.cell(row=row_num, column=2, value=s.school.school_code) ws3.cell(row=row_num, column=3, value=s.school.name_short) ws3.cell(row=row_num, column=4, value=round(s.sch_gpa, 4) if s.sch_gpa else 0) ws3.cell(row=row_num, column=5, value=s.sch_gpa_grade or '-') row_num += 1 # ==================== SHEET 4: BEST STUDENTS ==================== ws4 = wb.create_sheet("Best Students") ws4['A1'] = f"TOP PERFORMING STUDENTS - {project.name}" ws4['A1'].font = title_font ws4.merge_cells('A1:F1') # Overall Best ws4['A3'] = "OVERALL TOP 10 STUDENTS" ws4['A3'].font = Font(bold=True) headers = ['Rank', 'Name', 'Sex', 'School', 'Points', 'Division'] ws4.append(headers) for col, header in enumerate(headers, 1): cell = ws4.cell(row=4, column=col) cell.font = header_font cell.fill = header_fill row_num = 5 for idx, s in enumerate(best_students, 1): ws4.cell(row=row_num, column=1, value=idx) ws4.cell(row=row_num, column=2, value=s.student.full_name) ws4.cell(row=row_num, column=3, value=s.student.sex) ws4.cell(row=row_num, column=4, value=s.student.school.name_short) ws4.cell(row=row_num, column=5, value=s.total_points) ws4.cell(row=row_num, column=6, value=s.division) row_num += 1 # Best Girls row_num += 2 ws4.cell(row=row_num, column=1, value="TOP 10 GIRLS").font = Font(bold=True) row_num += 1 ws4.append(headers) for col, header in enumerate(headers, 1): cell = ws4.cell(row=row_num, column=col) cell.font = header_font cell.fill = header_fill row_num += 1 for idx, s in enumerate(best_girls, 1): ws4.cell(row=row_num, column=1, value=idx) ws4.cell(row=row_num, column=2, value=s.student.full_name) ws4.cell(row=row_num, column=3, value=s.student.sex) ws4.cell(row=row_num, column=4, value=s.student.school.name_short) ws4.cell(row=row_num, column=5, value=s.total_points) ws4.cell(row=row_num, column=6, value=s.division) row_num += 1 # Best Boys row_num += 2 ws4.cell(row=row_num, column=1, value="TOP 10 BOYS").font = Font(bold=True) row_num += 1 ws4.append(headers) for col, header in enumerate(headers, 1): cell = ws4.cell(row=row_num, column=col) cell.font = header_font cell.fill = header_fill row_num += 1 for idx, s in enumerate(best_boys, 1): ws4.cell(row=row_num, column=1, value=idx) ws4.cell(row=row_num, column=2, value=s.student.full_name) ws4.cell(row=row_num, column=3, value=s.student.sex) ws4.cell(row=row_num, column=4, value=s.student.school.name_short) ws4.cell(row=row_num, column=5, value=s.total_points) ws4.cell(row=row_num, column=6, value=s.division) row_num += 1 # ==================== SHEET 5: SUBJECT PERFORMANCE ==================== ws5 = wb.create_sheet("Subject Performance") ws5['A1'] = f"SUBJECT PERFORMANCE SUMMARY - {project.name}" ws5['A1'].font = title_font ws5.merge_cells('A1:L1') headers = ['Code', 'Subject', 'SAT', 'A', 'B', 'C', 'D', 'F', 'Pass (A-D)', 'Pass %', 'GPA'] ws5.append(headers) for col, header in enumerate(headers, 1): cell = ws5.cell(row=2, column=col) cell.font = header_font cell.fill = header_fill cell.alignment = center_align row_num = 3 for subject in processed_subject_summary: ws5.cell(row=row_num, column=1, value=subject['subject_code']) ws5.cell(row=row_num, column=2, value=subject['subject_name']) ws5.cell(row=row_num, column=3, value=subject['sat']) ws5.cell(row=row_num, column=4, value=subject['A']) ws5.cell(row=row_num, column=5, value=subject['B']) ws5.cell(row=row_num, column=6, value=subject['C']) ws5.cell(row=row_num, column=7, value=subject['D']) ws5.cell(row=row_num, column=8, value=subject['F']) ws5.cell(row=row_num, column=9, value=subject['pass_total']) ws5.cell(row=row_num, column=10, value=subject['pass_percent']) ws5.cell(row=row_num, column=11, value=subject['subject_gpa']) row_num += 1 # Totals row ws5.cell(row=row_num, column=1, value="TOTAL") ws5.cell(row=row_num, column=3, value=subject_totals['sat']) ws5.cell(row=row_num, column=4, value=subject_totals['A']) ws5.cell(row=row_num, column=5, value=subject_totals['B']) ws5.cell(row=row_num, column=6, value=subject_totals['C']) ws5.cell(row=row_num, column=7, value=subject_totals['D']) ws5.cell(row=row_num, column=8, value=subject_totals['F']) ws5.cell(row=row_num, column=9, value=subject_totals['A'] + subject_totals['B'] + subject_totals['C'] + subject_totals['D']) ws5.cell(row=row_num, column=10, value=subject_totals['overall_pass_percent']) # Auto-adjust column widths for all sheets for sheet in [ws1, ws2, ws3, ws4, ws5]: for column in sheet.columns: max_length = 0 column_letter = get_column_letter(column[0].column) for cell in column: try: if cell.value and len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = min(max_length + 2, 30) sheet.column_dimensions[column_letter].width = adjusted_width # Response filename = f"{project.project_code}_OLEVEL_Report_{project.year}.xlsx" response = HttpResponse( content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = f'attachment; filename="{filename}"' wb.save(response) return response def combined_form_six_report_excel(request, project_code): """ Print version of A-Level combined report """ from django.shortcuts import get_object_or_404 from .models import JointExamProject project = get_object_or_404(JointExamProject, project_code=project_code) # Get all the same data as the main view # ... (copy the data fetching logic from combined_form_six_report) # Add print-specific context context = { 'project': project, 'is_print_version': True, # ... all other context variables } return render(request, "exams/combined_report_print.html", context) def combined_primary_report_excel(request, project_code): """ Print version of A-Level combined report """ from django.shortcuts import get_object_or_404 from .models import JointExamProject project = get_object_or_404(JointExamProject, project_code=project_code) # Get all the same data as the main view # ... (copy the data fetching logic from combined_form_six_report) # Add print-specific context context = { 'project': project, 'is_print_version': True, # ... all other context variables } return render(request, "exams/combined_report_print.html", context) # exams/views_reports_combined.py def combined_form_six_report_print(request, project_code): """ Print version of A-Level combined report """ from django.shortcuts import get_object_or_404 from .models import JointExamProject project = get_object_or_404(JointExamProject, project_code=project_code) # Get all the same data as the main view # ... (copy the data fetching logic from combined_form_six_report) # Add print-specific context context = { 'project': project, 'is_print_version': True, # ... all other context variables } return render(request, "exams/combined_report_print.html", context) def combined_form_four_report_print(request, project_code): """ Print version of O-Level combined report """ from django.shortcuts import get_object_or_404 from django.db.models import Count, Q from .models import JointExamProject, StudentFinalResult, ProjectStudent, ProcessedSubjectScore, ExamSchoolResult, ExamSubject project = get_object_or_404(JointExamProject, project_code=project_code) project_id = project.id academic_level_id = project.academic_level_id # Verify this is O-Level if academic_level_id != 1: return HttpResponse("This report is only for O-Level/Form Four results", status=400) # ========================= # GET REGION NAME # ========================= region_name = ( StudentFinalResult.objects .filter(project_id=project_id) .select_related("student__school__district__region") .values_list("student__school__district__region__name", flat=True) .first() ) # ========================= # PASS/FAIL CALCULATIONS # ========================= totals = ( StudentFinalResult.objects .filter(project_id=project_id) .aggregate( total=Count('id', filter=Q(division__in=['I', 'II', 'III', 'IV', '0'])), pass_=Count('id', filter=Q(division__in=['I', 'II', 'III'])), fail=Count('id', filter=Q(division__in=['0', 'IV'])), ) ) total = totals["total"] or 1 pass_pct = round(totals["pass_"] * 100 / total, 2) fail_pct = round(totals["fail"] * 100 / total, 2) # ========================= # REGISTRATION COUNTS # ========================= registered_counts = ProjectStudent.objects.filter( project_id=project_id ).values('sex').annotate(count=Count('id')) registered_f = 0 registered_m = 0 for item in registered_counts: if item['sex'] == 'F': registered_f = item['count'] elif item['sex'] == 'M': registered_m = item['count'] registered_total = registered_f + registered_m sat_counts = StudentFinalResult.objects.filter( project_id=project_id ).filter(division__in=['I', 'II', 'III', 'IV', '0']).values('student__sex').annotate(count=Count('id')) sat_f = 0 sat_m = 0 for item in sat_counts: if item['student__sex'] == 'F': sat_f = item['count'] elif item['student__sex'] == 'M': sat_m = item['count'] sat_total = sat_f + sat_m absent_f = registered_f - sat_f absent_m = registered_m - sat_m absent_total = absent_f + absent_m # ========================= # DIVISION BY GENDER # ========================= DIV_KEYS = ["I", "II", "III", "IV", "0"] gender_div = {} rows = ( StudentFinalResult.objects .filter(project_id=project_id) .values("student__sex", "division") .annotate(t=Count("id")) ) for sex in ["F", "M"]: gender_div[sex] = {k: 0 for k in DIV_KEYS} for r in rows: sex = r["student__sex"] div = r["division"] if sex in gender_div and div in gender_div[sex]: gender_div[sex][div] = r["t"] gender_div["T"] = { k: gender_div["F"][k] + gender_div["M"][k] for k in DIV_KEYS } for sex in ["F", "M", "T"]: total_sat = sum(gender_div[sex].values()) gender_div[sex]["total_I_II"] = gender_div[sex]["I"] + gender_div[sex]["II"] gender_div[sex]["total_I_III"] = gender_div[sex]["I"] + gender_div[sex]["II"] + gender_div[sex]["III"] gender_div[sex]["total_I_IV"] = gender_div[sex]["I"] + gender_div[sex]["II"] + gender_div[sex]["III"] + gender_div[sex]["IV"] if total_sat > 0: gender_div[sex]["percent_I_II"] = round((gender_div[sex]["total_I_II"] / total_sat) * 100, 2) gender_div[sex]["percent_I_III"] = round((gender_div[sex]["total_I_III"] / total_sat) * 100, 2) gender_div[sex]["percent_I_IV"] = round((gender_div[sex]["total_I_IV"] / total_sat) * 100, 2) else: gender_div[sex]["percent_I_II"] = 0.00 gender_div[sex]["percent_I_III"] = 0.00 gender_div[sex]["percent_I_IV"] = 0.00 gender_div[sex]["total_sat"] = total_sat # ========================= # GPA CALCULATIONS # ========================= subject_scores = ProcessedSubjectScore.objects.filter(project_id=project_id) grade_counts = { 'A': subject_scores.filter(grade='A').count(), 'B': subject_scores.filter(grade='B').count(), 'C': subject_scores.filter(grade='C').count(), 'D': subject_scores.filter(grade='D').count(), 'F': subject_scores.filter(grade='F').count(), } total_subject_grades = sum(grade_counts.values()) if total_subject_grades > 0: subject_gpa = ( grade_counts['A'] * 1 + grade_counts['B'] * 2 + grade_counts['C'] * 3 + grade_counts['D'] * 4 + grade_counts['F'] * 5 ) / total_subject_grades else: subject_gpa = 0 division_gpa = 0 total_divisions = gender_div['T']['I'] + gender_div['T']['II'] + gender_div['T']['III'] + gender_div['T']['IV'] + gender_div['T']['0'] if total_divisions > 0: division_gpa = ( gender_div['T']['I'] * 1 + gender_div['T']['II'] * 2 + gender_div['T']['III'] * 3 + gender_div['T']['IV'] * 4 + gender_div['T']['0'] * 5 ) / total_divisions if subject_gpa > 0 and division_gpa > 0: overall_gpa = (subject_gpa + division_gpa) / 2 elif subject_gpa > 0: overall_gpa = subject_gpa elif division_gpa > 0: overall_gpa = division_gpa else: overall_gpa = 0 subject_gpa = round(subject_gpa, 4) division_gpa = round(division_gpa, 4) overall_gpa = round(overall_gpa, 4) overall_table = { 'divisions': { 'I': gender_div['T']['I'], 'II': gender_div['T']['II'], 'III': gender_div['T']['III'], 'IV': gender_div['T']['IV'], '0': gender_div['T']['0'] }, 'grades': grade_counts, 'subject_gpa': subject_gpa, 'division_gpa': division_gpa, 'overall_gpa': overall_gpa, } # ========================= # SCHOOL RANKINGS # ========================= school_results = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).select_related('school', 'school__district', 'school__district__region') school_ranking_data = [] for school_result in school_results: school = school_result.school # Get division counts for this school division_counts = StudentFinalResult.objects.filter( project_id=project_id, student__school=school ).filter(division__in=['I', 'II', 'III', 'IV', '0']) total_sat = division_counts.count() if total_sat == 0: continue div_i = division_counts.filter(division='I').count() div_ii = division_counts.filter(division='II').count() div_iii = division_counts.filter(division='III').count() div_iv = division_counts.filter(division='IV').count() div_o = division_counts.filter(division='0').count() pass_i_iii = div_i + div_ii + div_iii pass_i_iv = pass_i_iii + div_iv pass_i_iii_pct = round(pass_i_iii / total_sat * 100, 2) if total_sat > 0 else 0 pass_i_iv_pct = round(pass_i_iv / total_sat * 100, 2) if total_sat > 0 else 0 ownership_display = "GVT" if school.ownership == "GVT" else "NON-GVT" school_ranking_data.append({ 'school': school, 'school_name': school.name, 'name_short': school.name_short or school.name, 'school_code': school.school_code, 'district': school.district.name, 'region': school.district.region.name, 'ownership': ownership_display, 'total_sat': total_sat, 't_i': div_i, 't_ii': div_ii, 't_iii': div_iii, 't_iv': div_iv, 't_o': div_o, 'pass_i_iii': pass_i_iii, 'pass_i_iii_pct': pass_i_iii_pct, 'pass_i_iv': pass_i_iv, 'pass_i_iv_pct': pass_i_iv_pct, 'gpa': school_result.sch_gpa, 'gpa_grade': school_result.sch_gpa_grade, 'gpa_status': school_result.sch_gpa_status, }) school_ranking_data.sort(key=lambda x: x['gpa']) for idx, school in enumerate(school_ranking_data, 1): school['overall_rank'] = idx # ========================= # BEST STUDENTS # ========================= best_students = ( StudentFinalResult.objects .filter(project_id=project_id, total_points__gte=1) .select_related('student', 'student__school', 'student__combination') .order_by('total_points', '-std_average')[:10] ) best_girls = ( StudentFinalResult.objects .filter(project_id=project_id, student__sex="F", total_points__gte=1) .select_related('student', 'student__school') .order_by('total_points', '-std_average')[:10] ) best_boys = ( StudentFinalResult.objects .filter(project_id=project_id, student__sex="M", total_points__gte=1) .select_related('student', 'student__school') .order_by('total_points', '-std_average')[:10] ) best_gvt_students = ( StudentFinalResult.objects .filter(project_id=project_id, student__school__ownership="GVT", total_points__gte=1) .select_related('student', 'student__school') .order_by('total_points', '-std_average')[:10] ) best_nongvt_students = ( StudentFinalResult.objects .filter(project_id=project_id, student__school__ownership="NON-GVT", total_points__gte=1) .select_related('student', 'student__school') .order_by('total_points', '-std_average')[:10] ) # ========================= # SUBJECT PERFORMANCE # ========================= subject_data = ( ProcessedSubjectScore.objects .filter(project_id=project_id) .values("subject__subject_code", "subject__subject_name_eng") .annotate( sat=Count("id"), A=Count("id", filter=Q(grade="A")), B=Count("id", filter=Q(grade="B")), C=Count("id", filter=Q(grade="C")), D=Count("id", filter=Q(grade="D")), F=Count("id", filter=Q(grade="F")), ) .order_by("subject__subject_code") ) processed_subject_summary = [] subject_totals = {'A': 0, 'B': 0, 'C': 0, 'D': 0, 'F': 0, 'sat': 0} for subject in subject_data: A = subject['A'] or 0 B = subject['B'] or 0 C = subject['C'] or 0 D = subject['D'] or 0 F = subject['F'] or 0 sat = subject['sat'] or 0 pass_total = A + B + C + D pass_percent = round((pass_total / sat) * 100, 2) if sat > 0 else 0 if sat > 0: gpa_numerator = (A * 1 + B * 2 + C * 3 + D * 4 + F * 5) subject_gpa_calc = round(gpa_numerator / sat, 4) else: subject_gpa_calc = 0 processed_subject_summary.append({ 'subject_code': subject['subject__subject_code'], 'subject_name': subject['subject__subject_name_eng'], 'sat': sat, 'A': A, 'B': B, 'C': C, 'D': D, 'F': F, 'pass_total': pass_total, 'pass_percent': pass_percent, 'subject_gpa': subject_gpa_calc, }) subject_totals['A'] += A subject_totals['B'] += B subject_totals['C'] += C subject_totals['D'] += D subject_totals['F'] += F subject_totals['sat'] += sat if subject_totals['sat'] > 0: subject_totals['overall_pass_percent'] = round( (subject_totals['A'] + subject_totals['B'] + subject_totals['C'] + subject_totals['D']) / subject_totals['sat'] * 100, 2 ) top_subjects_by_gpa = sorted(processed_subject_summary, key=lambda x: x['subject_gpa'])[:10] # ====================================================== # BEST STUDENTS PER SUBJECT (FIXED - ADD THIS CODE) # ====================================================== # Get distinct subjects that have scores subjects_list = ( ProcessedSubjectScore.objects .filter( project_id=project_id, percentage_score__isnull=False, grade__isnull=False ) .exclude(percentage_score='') .exclude(percentage_score='NULL') .exclude(percentage_score='X') .exclude(percentage_score='x') .values_list('subject__subject_code', flat=True) .distinct() .order_by('subject__subject_code') ) subject_best_list = [] for subject_code in subjects_list: # Get subject name try: subject = ExamSubject.objects.filter(subject_code=subject_code).first() subject_name = subject.subject_name_eng if subject else subject_code except: subject_name = subject_code # Get all scores for this subject all_scores = ProcessedSubjectScore.objects.filter( project_id=project_id, subject__subject_code=subject_code, percentage_score__isnull=False, grade__isnull=False ).exclude( percentage_score='' ).exclude( percentage_score='NULL' ).exclude( percentage_score__iexact='null' ).exclude( percentage_score='X' ).exclude( percentage_score='x' ).select_related( 'student', 'student__school', 'student__combination' ) # Process scores in Python - convert to float scores_with_float = [] for score in all_scores: try: # Convert string to float percent_float = float(score.percentage_score) scores_with_float.append({ 'score': score, 'percent_float': percent_float }) except (ValueError, TypeError, AttributeError): continue if len(scores_with_float) == 0: continue # Sort by percentage descending (highest first) scores_with_float.sort(key=lambda x: x['percent_float'], reverse=True) # Take top 10 top_scores = scores_with_float[:10] # Process student data student_list = [] for rank, item in enumerate(top_scores, start=1): student_score = item['score'] percentage_raw = item['percent_float'] percentage_display = f"{percentage_raw:.0f}" # Safely get school name school_name = student_score.student.school.name_short or student_score.student.school.name district_name = student_score.student.school.district.name if student_score.student.school.district else '' student_list.append({ 'rank': rank, 'student_number': student_score.student.student_number, 'full_name': student_score.student.full_name, 'sex': student_score.student.sex, 'school_name': school_name, 'district': district_name, 'school_ownership': student_score.student.school.ownership, 'percentage_score': percentage_display, 'percentage_raw': percentage_raw, 'grade': student_score.grade, 'points': student_score.points, }) if student_list: subject_best_list.append({ 'code': subject_code, 'name': subject_name, 'rows': student_list }) # Sort subjects by code subject_best_list.sort(key=lambda x: x['code']) # Limit to top 20 subjects subject_best_list = subject_best_list[:20] # ========================= # SCHOOL RANKING LISTS # ========================= school_ranking_overall = school_ranking_data.copy() school_ranking_30plus = [s for s in school_ranking_data if s['total_sat'] >= 40] school_ranking_under30 = [s for s in school_ranking_data if s['total_sat'] < 40] # Top 10 lists best_10_all = school_ranking_overall[:10] best_10_gvt = [s for s in school_ranking_overall if s['ownership'] == "GVT"][:10] best_10_nongvt = [s for s in school_ranking_overall if s['ownership'] == "NON-GVT"][:10] best_40_std_andabove = school_ranking_30plus[:10] best_40_less = school_ranking_under30[:10] # ========================= # CONTEXT FOR TEMPLATE # ========================= context = { 'project': project, 'region_name': region_name, 'pass_pct': pass_pct, 'fail_pct': fail_pct, 'gender_div': gender_div, 'overall_table': overall_table, 'registered_f': registered_f, 'registered_m': registered_m, 'registered_total': registered_total, 'sat_f': sat_f, 'sat_m': sat_m, 'sat_total': sat_total, 'absent_f': absent_f, 'absent_m': absent_m, 'absent_total': absent_total, 'best_10_all': best_10_all, 'best_10_gvt': best_10_gvt, 'best_10_nongvt': best_10_nongvt, 'best_40_std_andabove': best_40_std_andabove, 'best_40_less': best_40_less, 'best_students': best_students, 'best_girls': best_girls, 'best_boys': best_boys, 'best_gvt_students': best_gvt_students, 'best_nongvt_students': best_nongvt_students, 'processed_subject_summary': processed_subject_summary, 'subject_totals': subject_totals, 'top_subjects_by_gpa': top_subjects_by_gpa, 'academic_level_id': academic_level_id, 'subject_best_list': subject_best_list, # THIS IS THE CRITICAL LINE 'school_ranking_overall': school_ranking_overall, 'school_ranking_30plus': school_ranking_30plus, 'school_ranking_under30': school_ranking_under30, 'is_print_version': True, } return render(request, "exams/combined_report_olevel_print.html", context) def combined_primary_report_print(request, project_code): """ Print version of Primary Level combined report """ from django.shortcuts import get_object_or_404 from .models import JointExamProject project = get_object_or_404(JointExamProject, project_code=project_code) # Get all the same data as the main view # ... (copy the data fetching logic from combined_primary_report) context = { 'project': project, 'is_print_version': True, # ... all other context variables } return render(request, "exams/combined_report_primary_print.html", context) # exams/views_reports_combined.py def subject_rank_router(request, project_code): """ Route to appropriate subject ranking view based on selection Similar to project_school_rank_router but for subject rankings """ from .models import JointExamProject from django.shortcuts import get_object_or_404 from django.http import Http404 print(f"DEBUG: Subject Rank Router - Received project_code = '{project_code}'") # Get project by code project = get_object_or_404(JointExamProject, project_code=project_code) project_id = project.id year = project.year print(f"DEBUG: Found project: {project.name} (ID: {project_id})") # Get parameters from the request subject_code = request.GET.get('subject_code') scope = request.GET.get('scope', 'overall') layout = request.GET.get('layout', 'normal') format_type = request.GET.get('format', 'html') region_id = request.GET.get('region_id') district_id = request.GET.get('district_id') print(f"DEBUG: subject_code={subject_code}, scope={scope}, format={format_type}, layout={layout}") # If no subject selected, show the subject selection page if not subject_code: return subject_school_ranking_page(request, project_code) # Route based on format if format_type == 'excel': return export_subject_ranking_excel(request, project_code, subject_code, scope, region_id, district_id, layout) elif format_type == 'pdf': return export_subject_ranking_pdf(request, project_code, subject_code, scope, region_id, district_id, layout) else: # HTML view return subject_ranking_html(request, project_code, subject_code, scope, region_id, district_id, layout) def subject_ranking_html(request, project_code, subject_code, scope='overall', region_id=None, district_id=None, layout='normal'): """ HTML view for subject ranking """ from django.shortcuts import get_object_or_404 from django.db.models import Count, Q from .models import JointExamProject, ExamSubject, ProcessedSubjectScore, School, Region, District, GradingRule, GpaRule project = get_object_or_404(JointExamProject, project_code=project_code) project_id = project.id subject = get_object_or_404(ExamSubject, subject_code=subject_code) academic_level_id = project.academic_level_id # Get region and district names for display region_name = None district_name = None if region_id: try: region = Region.objects.get(id=region_id) region_name = region.name except Region.DoesNotExist: pass if district_id: try: district = District.objects.get(id=district_id) district_name = district.name except District.DoesNotExist: pass # Define GPA weights based on academic level if academic_level_id == 1: # O-Level gpa_weights = {'A': 1, 'B': 2, 'C': 3, 'D': 4, 'F': 5} valid_grades = ['A', 'B', 'C', 'D', 'F'] pass_grades = ['A', 'B', 'C', 'D'] elif academic_level_id == 2: # A-Level gpa_weights = {'A': 1, 'B': 2, 'C': 3, 'D': 4, 'E': 5, 'S': 6, 'F': 7} valid_grades = ['A', 'B', 'C', 'D', 'E', 'S', 'F'] pass_grades = ['A', 'B', 'C', 'D', 'E'] else: # Primary Level gpa_weights = {'A': 1, 'B': 2, 'C': 3, 'D': 4, 'E': 5} valid_grades = ['A', 'B', 'C', 'D', 'E'] pass_grades = ['A', 'B', 'C'] # Get all schools that have scores for this subject school_ids = ProcessedSubjectScore.objects.filter( project_id=project_id, subject=subject ).values_list('student__school_id', flat=True).distinct() schools_qs = School.objects.filter(id__in=school_ids) # Apply scope filters if scope == 'region' and region_id: schools_qs = schools_qs.filter(region_id=region_id) elif scope == 'district' and district_id: schools_qs = schools_qs.filter(district_id=district_id) # Build ranking data ranking_data = [] for school in schools_qs: scores = ProcessedSubjectScore.objects.filter( project_id=project_id, subject=subject, student__school=school ) total_students = scores.count() if total_students == 0: continue # Get gender breakdown for registered students registered_f = ProjectStudent.objects.filter( project_id=project_id, school=school, sex='F' ).count() registered_m = ProjectStudent.objects.filter( project_id=project_id, school=school, sex='M' ).count() registered_t = registered_f + registered_m # Get gender breakdown for SAT sat_f = scores.filter(student__sex='F').count() sat_m = scores.filter(student__sex='M').count() # Count grades with gender breakdown grade_counts = {} grade_counts_f = {} grade_counts_m = {} for grade in valid_grades: grade_counts[grade] = scores.filter(grade=grade).count() grade_counts_f[grade] = scores.filter(grade=grade, student__sex='F').count() grade_counts_m[grade] = scores.filter(grade=grade, student__sex='M').count() # Calculate passed students passed = 0 passed_f = 0 passed_m = 0 for grade in pass_grades: passed += grade_counts.get(grade, 0) passed_f += grade_counts_f.get(grade, 0) passed_m += grade_counts_m.get(grade, 0) passed_pct = round((passed / total_students * 100), 2) if total_students > 0 else 0 # Calculate average score all_scores = [] for score in scores: if score.percentage_score and score.percentage_score != 'X': try: all_scores.append(float(score.percentage_score)) except (ValueError, TypeError): pass avg_score = round(sum(all_scores) / len(all_scores), 2) if all_scores else 0 # Calculate GPA gpa_numerator = 0 for grade, count in grade_counts.items(): if grade in gpa_weights: gpa_numerator += gpa_weights[grade] * count gpa = round(gpa_numerator / total_students, 4) if total_students > 0 else 0 # Get GPA grade and status def get_gpa_info(gpa_value, academic_level_obj): from .models import GpaRule if gpa_value is None or gpa_value == 0: return 'F', 'FAIL', '#dc3545' rules = GpaRule.objects.filter(academic_level=academic_level_obj).order_by('-min_gpa') for rule in rules: if rule.min_gpa <= gpa_value <= rule.max_gpa: return rule.gpa_grade, rule.gpa_status, rule.gpa_colour return 'F', 'FAIL', '#dc3545' grade, status, gpa_color = get_gpa_info(gpa, project.academic_level) # Build ranking data row_data = { 'rank': 0, 'school_code': school.school_code, 'school_name': school.name, 'school_short': school.name_short or school.name, 'district': school.district.name, 'region': school.region.name, 'ownership': school.ownership, 'total_students': total_students, 'passed': passed, 'passed_f': passed_f, 'passed_m': passed_m, 'passed_pct': passed_pct, 'avg_score': avg_score, 'gpa': gpa, 'grade': grade, 'status': status, 'gpa_color': gpa_color, # Registered counts 'reg_f': registered_f, 'reg_m': registered_m, 'reg_t': registered_t, # SAT counts 'sat_f': sat_f, 'sat_m': sat_m, } # Add grade counts for grade in valid_grades: row_data[grade] = grade_counts.get(grade, 0) row_data[f'{grade}_f'] = grade_counts_f.get(grade, 0) row_data[f'{grade}_m'] = grade_counts_m.get(grade, 0) ranking_data.append(row_data) # Sort by GPA (lower is better) ranking_data.sort(key=lambda x: x['gpa']) # Add rank for idx, data in enumerate(ranking_data, 1): data['rank'] = idx data['total_schools'] = len(ranking_data) # Choose template based on layout if layout == 'extended': template = 'exams/subject_ranking_extended.html' else: template = 'exams/subject_ranking_normal.html' # Get regions and districts for reference regions = Region.objects.all().order_by('name') districts = District.objects.all().order_by('name') context = { 'project': project, 'project_name': project.name, 'project_year': project.year, 'project_code': project_code, 'subject_code': subject_code, 'subject_name': subject.subject_name_eng, 'title': f"{project.name} - Subject Ranking", 'ranking_data': ranking_data, 'total_schools': len(ranking_data), 'scope': scope, 'layout': layout, 'region_id': region_id, 'district_id': district_id, 'region_name': region_name, 'district_name': district_name, 'academic_level_id': academic_level_id, 'regions': regions, 'districts': districts, 'valid_grades': valid_grades, } return render(request, template, context) def export_subject_ranking_excel(request, project_code, subject_code, scope='overall', region_id=None, district_id=None, layout='normal'): """ Export subject ranking to Excel """ from django.shortcuts import get_object_or_404 from django.db.models import Count, Q from django.http import HttpResponse import openpyxl from openpyxl.styles import Font, Alignment, PatternFill from openpyxl.utils import get_column_letter from .models import JointExamProject, ExamSubject, ProcessedSubjectScore, School, Region, District project = get_object_or_404(JointExamProject, project_code=project_code) project_id = project.id subject = get_object_or_404(ExamSubject, subject_code=subject_code) academic_level_id = project.academic_level_id # Define valid grades based on academic level if academic_level_id == 2: # A-Level valid_grades = ['A', 'B', 'C', 'D', 'E', 'S', 'F'] elif academic_level_id == 1: # O-Level valid_grades = ['A', 'B', 'C', 'D', 'F'] else: # Primary valid_grades = ['A', 'B', 'C', 'D', 'E'] # Get schools school_ids = ProcessedSubjectScore.objects.filter( project_id=project_id, subject=subject ).values_list('student__school_id', flat=True).distinct() schools_qs = School.objects.filter(id__in=school_ids) if scope == 'region' and region_id: schools_qs = schools_qs.filter(region_id=region_id) elif scope == 'district' and district_id: schools_qs = schools_qs.filter(district_id=district_id) # Build ranking data ranking_data = [] for school in schools_qs: scores = ProcessedSubjectScore.objects.filter( project_id=project_id, subject=subject, student__school=school ) total_students = scores.count() if total_students == 0: continue grade_counts = {} for grade in valid_grades: grade_counts[grade] = scores.filter(grade=grade).count() passed = sum(grade_counts.get(g, 0) for g in valid_grades if g != 'F' and g != 'S') passed_pct = round((passed / total_students * 100), 2) if total_students > 0 else 0 all_scores = [] for score in scores: if score.percentage_score and score.percentage_score != 'X': try: all_scores.append(float(score.percentage_score)) except (ValueError, TypeError): pass avg_score = round(sum(all_scores) / len(all_scores), 2) if all_scores else 0 ranking_data.append({ 'school_code': school.school_code, 'school_name': school.name, 'district': school.district.name, 'region': school.region.name, 'ownership': school.ownership, 'total_students': total_students, 'passed': passed, 'passed_pct': passed_pct, 'avg_score': avg_score, **grade_counts, }) # Sort by passed percentage (higher is better) ranking_data.sort(key=lambda x: x['passed_pct'], reverse=True) # Create workbook wb = openpyxl.Workbook() ws = wb.active ws.title = f"{subject.subject_code}_Ranking" # Styles header_fill = PatternFill(start_color="1a3e6f", end_color="1a3e6f", fill_type="solid") header_font = Font(color="FFFFFF", bold=True) center_align = Alignment(horizontal="center", vertical="center") # Title title = f"SUBJECT SCHOOL RANKING - {subject.subject_code} - {subject.subject_name_eng}" ws.merge_cells('A1:M1') ws['A1'] = title ws['A1'].font = Font(bold=True, size=14) ws['A1'].alignment = center_align ws.merge_cells('A2:M2') ws['A2'] = f"{project.name} - {project.year}" ws['A2'].font = Font(bold=True, size=12) ws['A2'].alignment = center_align ws.append([]) # Headers headers = ['Rank', 'School Code', 'School Name', 'District', 'Region', 'Ownership', 'Total', 'Passed', 'Pass %', 'Avg Score'] for grade in valid_grades: headers.append(grade) for col, header in enumerate(headers, 1): cell = ws.cell(row=4, column=col) cell.value = header cell.font = header_font cell.fill = header_fill cell.alignment = center_align # Data rows for row_num, r in enumerate(ranking_data, 5): data = [ row_num - 4, r['school_code'], r['school_name'], r['district'], r['region'], r['ownership'], r['total_students'], r['passed'], r['passed_pct'], r['avg_score'] ] for grade in valid_grades: data.append(r.get(grade, 0)) for col, value in enumerate(data, 1): cell = ws.cell(row=row_num, column=col) cell.value = value cell.alignment = center_align # Auto-adjust column widths for col_idx in range(1, ws.max_column + 1): max_len = 0 col_letter = get_column_letter(col_idx) for cell in ws[col_letter]: if cell.value: max_len = max(max_len, len(str(cell.value))) ws.column_dimensions[col_letter].width = min(max_len + 2, 30) # Create response filename = f"{subject.subject_code}_ranking_{project.year}.xlsx" response = HttpResponse( content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = f'attachment; filename="{filename}"' wb.save(response) return response def export_subject_ranking_pdf(request, project_code, subject_code, scope='overall', region_id=None, district_id=None, layout='normal'): """ Export subject ranking to PDF """ from django.shortcuts import get_object_or_404 from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer from reportlab.lib.pagesizes import A4, landscape from reportlab.lib import colors from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from django.http import HttpResponse from .models import JointExamProject, ExamSubject, ProcessedSubjectScore, School project = get_object_or_404(JointExamProject, project_code=project_code) project_id = project.id subject = get_object_or_404(ExamSubject, subject_code=subject_code) academic_level_id = project.academic_level_id # Get schools school_ids = ProcessedSubjectScore.objects.filter( project_id=project_id, subject=subject ).values_list('student__school_id', flat=True).distinct() schools_qs = School.objects.filter(id__in=school_ids) if scope == 'region' and region_id: schools_qs = schools_qs.filter(region_id=region_id) elif scope == 'district' and district_id: schools_qs = schools_qs.filter(district_id=district_id) # Build ranking data (simplified for PDF) ranking_data = [] for school in schools_qs: scores = ProcessedSubjectScore.objects.filter( project_id=project_id, subject=subject, student__school=school ) total_students = scores.count() if total_students == 0: continue passed = scores.exclude(grade__in=['F', 'S']).count() if academic_level_id == 2 else scores.exclude(grade='F').count() passed_pct = round((passed / total_students * 100), 2) if total_students > 0 else 0 ranking_data.append({ 'school_code': school.school_code, 'school_name': school.name_short or school.name, 'district': school.district.name, 'total_students': total_students, 'passed_pct': passed_pct, }) ranking_data.sort(key=lambda x: x['passed_pct'], reverse=True) # Create PDF response = HttpResponse(content_type="application/pdf") response["Content-Disposition"] = f'attachment; filename="{subject.subject_code}_ranking_{project.year}.pdf"' doc = SimpleDocTemplate(response, pagesize=landscape(A4)) styles = getSampleStyleSheet() title_style = ParagraphStyle('CustomTitle', parent=styles['Title'], fontSize=14, alignment=1, spaceAfter=10) story = [] story.append(Paragraph(f"{subject.subject_code} - {subject.subject_name_eng}", title_style)) story.append(Paragraph(f"{project.name} - {project.year}", styles["Normal"])) story.append(Spacer(1, 12)) # Table data (limit to 50 rows) data = [['Rank', 'Code', 'School', 'District', 'Total', 'Pass %']] for idx, r in enumerate(ranking_data[:50], 1): school_name = r['school_name'][:25] + '...' if len(r['school_name']) > 25 else r['school_name'] data.append([str(idx), r['school_code'], school_name, r['district'], str(r['total_students']), f"{r['passed_pct']}%"]) table = Table(data, repeatRows=1) table.setStyle(TableStyle([ ("GRID", (0, 0), (-1, -1), 0.5, colors.black), ("BACKGROUND", (0, 0), (-1, 0), colors.lightgrey), ("ALIGN", (0, 0), (-1, -1), "CENTER"), ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"), ("FONTSIZE", (0, 0), (-1, -1), 8), ])) story.append(table) doc.build(story) return response