# 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.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 ).exclude(division__iexact='ABS') 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_id): project = get_object_or_404(JointExamProject, id=project_id) academic_level_id = project.academic_level_id # Assuming project has academic_level field # ========================= # 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 # Ensure Q is imported 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'])), # Only division 0 (not ABS) fail_=Count('id', filter=Q(division="0")), # Only division 0 (not ABS) ) ) 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 project = JointExamProject.objects.get(id=project_id) # 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' # Exclude ABSENT ).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 # After calculating region_data, calculate totals for regions # total_schools = sum(r['schools'] for r in region_data) # total_reg_f = sum(r['reg_f'] for r in region_data) # total_reg_m = sum(r['reg_m'] for r in region_data) # total_reg = sum(r['reg_t'] for r in region_data) # total_sat_f = sum(r['sat_f'] for r in region_data) # total_sat_m = sum(r['sat_m'] for r in region_data) # total_sat = sum(r['sat_t'] for r in region_data) # total_abs_f = sum(r['abs_f'] for r in region_data) # total_abs_m = sum(r['abs_m'] for r in region_data) # total_abs = sum(r['abs_t'] for r in region_data) # Calculate overall percentages # 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 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] # 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 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, 'A_E_total': 0, 'A_S_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 A-E total (excluding S and F) a_e_total = A + B + C + D + E # Calculate A-S total (excluding F) a_s_total = A + B + C + D + E + S # Calculate percentages (avoid division by zero) if sat > 0: a_e_percent = round((a_e_total / sat) * 100, 2) a_s_percent = round((a_s_total / sat) * 100, 2) else: a_e_percent = 0.00 a_s_percent = 0.00 # Calculate Subject GPA (A=1, B=2, C=3, D=4, E=5, S=6, F=7) if sat > 0: subject_gpa = round(( A * 1 + B * 2 + C * 3 + D * 4 + E * 5 + S * 6 + F * 7 ) / sat, 4) else: subject_gpa = 0.0000 # 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, 'A_E_total': a_e_total, 'A_E_percent': a_e_percent, 'A_S_total': a_s_total, 'A_S_percent': a_s_percent, 'subject_gpa': subject_gpa, 'gpa_display': f"{subject_gpa:.4f}", 'performance_class': 'excellent' if subject_gpa <= 2.5 else 'average' if subject_gpa <= 4.0 else 'poor' } 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['A_E_total'] += a_e_total subject_totals['A_S_total'] += a_s_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 percentages if subject_totals['sat'] > 0: subject_totals['overall_A_E_percent'] = round((subject_totals['A_E_total'] / subject_totals['sat']) * 100, 2) subject_totals['overall_A_S_percent'] = round((subject_totals['A_S_total'] / subject_totals['sat']) * 100, 2) else: subject_totals['overall_A_E_percent'] = 0.00 subject_totals['overall_A_S_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] from django.db.models import FloatField from django.db.models.functions import Cast # ====================================================== # BEST STUDENTS PER SUBJECT (Rank by highest percentage_score) # ====================================================== # Get distinct subjects that have scores subjects_with_scores = ( ProcessedSubjectScore.objects .filter( project_id=project_id, percentage_score__isnull=False, # Exclude null percentages grade__isnull=False # Exclude null grades ) .exclude(percentage_score='') # Exclude empty strings .exclude(percentage_score='NULL') # Exclude 'NULL' strings .values_list('subject__subject_code', flat=True) .distinct() .order_by('subject__subject_code') ) 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 top 10 students for this subject by highest percentage_score # Using Cast to convert text to float for correct sorting top_students = ( 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') .select_related( 'student', 'student__school', 'student__combination' ) .annotate( percentage_float=Cast('percentage_score', FloatField()) ) .order_by('-percentage_float')[:10] # Sort by converted float value ) # Process student data student_list = [] for rank, student_score in enumerate(top_students, start=1): # Format percentage to 2 decimal places percentage_display = "0.00" percentage_raw = 0.0 if student_score.percentage_score: try: # Clean the percentage string (remove % sign if present, trim spaces) clean_percentage = str(student_score.percentage_score).strip().replace('%', '') percentage_raw = float(clean_percentage) percentage_display = f"{percentage_raw:.0f}" except (ValueError, TypeError): percentage_raw = 0.0 percentage_display = "0.00" 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': student_score.student.school.name_short, 'school_ownership': student_score.student.school.ownership, 'percentage_score': percentage_display, 'percentage_raw': percentage_raw, # Store as float for template comparisons 'grade': student_score.grade, 'points': student_score.points, }) # Only add subjects that have at least one student if student_list: subject_best_list.append({ 'code': subject_code, 'name': subject_name, 'rows': student_list }) # Sort subjects by code for consistent display subject_best_list.sort(key=lambda x: x['code']) # Limit to top 20 subjects to avoid too many pages subject_best_list = subject_best_list[:20] return render(request, "exams/reports/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 ) # ====================================================== # SCHOOL RANKING ROUTER # ====================================================== def project_school_rank_router(request, project_id): """Route to appropriate ranking view based on selection""" # 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') # Get project project = get_object_or_404(JointExamProject, id=project_id) # Build base URL parameters base_params = { 'project_id': project_id, 'category': category, 'layout': layout, } # Route based on scope if scope == 'region' and 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: 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 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) # ====================================================== # 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'] if div in div_counts: div_counts[div] = item['count'] if div != 'ABS': total_sat += item['count'] # 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 # ====================================================== # OVERALL SCHOOL RANKING (Normal Layout) # ====================================================== def overall_school_ranking(request, project_id, category='all', layout='normal'): """Overall school ranking view""" 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/reports/school_ranking_extended.html' print(f"Using EXTENDED template with {len(rows)} rows") # Debug else: template = 'exams/reports/school_ranking_normal.html' print(f"Using NORMAL template with {len(rows)} rows") # Debug context = { 'project': project, 'rows': rows, 'category': category, 'layout': layout, 'title': f"Overall School Ranking - {project.name} {project.year}", } return render(request, template, context) # ====================================================== # REGION SCHOOL RANKING # ====================================================== def region_school_ranking(request, project_id, region_id, category='all', layout='normal'): """Region-wise school ranking view""" 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/reports/school_ranking_extended.html' else: template = 'exams/reports/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) # ====================================================== # DISTRICT SCHOOL RANKING # ====================================================== def district_school_ranking(request, project_id, district_id, category='all', layout='normal'): """District-wise school ranking view""" 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/reports/school_ranking_extended.html' else: template = 'exams/reports/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) # ====================================================== # EXCEL EXPORT # ====================================================== def school_ranking_excel(request, project_id, scope='overall', region_id=None, district_id=None, category='all', layout='normal'): """Export school ranking to Excel""" 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['no'], r['cno'], r['name'], r['district'], r['region'], r['ownership'], r['reg_f'], r['reg_m'], r['reg_t'], r['sat_f'], r['sat_m'], r['sat_t'], r['i_f'], r['i_m'], r['i_t'], r['i_pct'], r['ii_f'], r['ii_m'], r['ii_t'], r['ii_pct'], r['iii_f'], r['iii_m'], r['iii_t'], r['iii_pct'], r['pass_i_iii_f'], r['pass_i_iii_m'], r['pass_i_iii_t'], r['pass_i_iii_pct'], r['iv_f'], r['iv_m'], r['iv_t'], r['iv_pct'], r['f_f'], r['f_m'], r['f_t'], r['f_pct'], r['pass_i_iv_f'], r['pass_i_iv_m'], r['pass_i_iv_t'], r['pass_i_iv_pct'], r['gpa'], r['grade'], r['status'], r['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['rank'], r['code'], r['name'], r['district'], r['region'], r['d1'], r['d2'], r['d3'], r['d4'], r['d0'], r['pass'], r['pass_pct'], r['gpa'] ] for col, value in enumerate(data, 1): ws.cell(row=row_num, column=col, value=value) from openpyxl.utils import get_column_letter 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 = max_len + 2 # Save to response response = HttpResponse( content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) 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['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 ).exclude(division__iexact='ABS') 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 ).exclude(division__iexact='ABS') 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 ).exclude(division__iexact='ABS') 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 ).exclude(division__iexact='ABS') 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 division_counts = StudentFinalResult.objects.filter( project_id=project_id, student__school=school.school ).exclude(division__iexact='ABS') total_sat = division_counts.count() 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 registered students count registered = ProjectStudent.objects.filter( project_id=project_id, school=school.school ).count() school_data.append({ 'school': school.school, 'registered': registered, '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, '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 ).exclude(division__iexact='ABS') 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/reports/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/reports/tables_preview.html', context)