# 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 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'), # Use Count instead of Sum(1) pass_=Count('id', filter=~Q(division="0") & ~Q(division__iexact='ABS')), # Exclude division 0 and 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, 1) fail_pct = round(totals["fail"] * 100 / total, 1) # ========================= # 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 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=40 ).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=40 ).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=40 )[:10] # BEST SCHOOLS WITH LESS THAN 40 CANDIDATES best_40_less_v2 = schools_with_counts_v2.filter( candidates_count__lt=40 )[: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=40).count()}") print(f"Schools with <40 candidates: {schools_with_counts.filter(candidates_count__lt=40).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) # ====================================================== # ====================================================== # 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=40 ) elif category == 'below_40': schools_with_results = schools_with_results.filter( with_results__lt=40 ) # 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) # ====================================================== # HELPER FUNCTION FOR GPA CALCULATION # ====================================================== def calculate_gpa(div_i, div_ii, div_iii, div_iv, div_o, total): if total > 0: division_gpa = (div_i * 1 + div_ii * 2 + div_iii * 3 + div_iv * 4 + div_o * 5) / total # For now, we'll use division GPA as overall GPA # In production, you need to calculate subject GPA from ProcessedSubjectScore subject_gpa = division_gpa # Placeholder - replace with actual subject GPA calculation overall_gpa = (division_gpa + subject_gpa) / 2 return round(overall_gpa, 4) return 0 # ====================================================== # 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 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 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, }) 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 # ====================================================== # 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 = [] 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 # Only add if there's data if reg_t > 0 or sat_t > 0: district_data.append({ 'no': len(district_data) + 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, }) # Debug print to console print(f"District: {district.name}, reg_t: {reg_t}, sat_t: {sat_t}") # Debug print to verify data print(f"Total districts with data: {len(district_data)}") if district_data: print(f"First district data: {district_data[0]}") # ====================================================== # TABLE 3: REGIONAL DIVISION PERFORMANCE (Jedwali Na. 2(a)) # ====================================================== div_regions = [] total_all_sat = 0 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') total_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 # Calculate overall GPA using (Subject GPA + Division GPA) / 2 overall_gpa = calculate_gpa(div_i, div_ii, div_iii, div_iv, div_o, total_sat) div_regions.append({ 'region': region.name, 'sat': total_sat, 'i': div_i, 'i_pct': round(div_i / total_sat * 100, 2) if total_sat > 0 else 0, 'ii': div_ii, 'ii_pct': round(div_ii / total_sat * 100, 2) if total_sat > 0 else 0, 'iii': div_iii, 'iii_pct': round(div_iii / total_sat * 100, 2) if total_sat > 0 else 0, 'pass_i_iii': pass_i_iii, 'pass_i_iii_pct': round(pass_i_iii / total_sat * 100, 2) if total_sat > 0 else 0, 'iv': div_iv, 'iv_pct': round(div_iv / total_sat * 100, 2) if total_sat > 0 else 0, 'pass_i_iv': pass_i_iv, 'pass_i_iv_pct': round(pass_i_iv / total_sat * 100, 2) if total_sat > 0 else 0, 'o': div_o, 'o_pct': round(div_o / total_sat * 100, 2) if total_sat > 0 else 0, 'gpa': overall_gpa, }) total_all_sat += total_sat total_i += div_i total_ii += div_ii total_iii += div_iii total_iv += div_iv total_o += div_o # Calculate overall GPA for totals overall_gpa_total = calculate_gpa(total_i, total_ii, total_iii, total_iv, total_o, total_all_sat) # ====================================================== # 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 the same formula 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 overall_gpa = calculate_gpa(div_i, div_ii, div_iii, div_iv, div_o, total) 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, }) # ====================================================== # 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 overall_gpa = calculate_gpa(div_i, div_ii, div_iii, div_iv, div_o, total_sat) 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']) def calculate_gpa(div_i, div_ii, div_iii, div_iv, div_o, total): if total > 0: division_gpa = (div_i * 1 + div_ii * 2 + div_iii * 3 + div_iv * 4 + div_o * 5) / total subject_gpa = division_gpa # Placeholder overall_gpa = (division_gpa + subject_gpa) / 2 return round(overall_gpa, 4) return 0 # ====================================================== # 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, '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 # Group by subject_type and show district performance for specific subjects # ====================================================== # Get all districts districts = District.objects.filter( school__projectstudent__project_id=project_id ).distinct().order_by('region__name', 'name') # Define subject types and their subjects to display subject_types_to_show = { 'LANGUAGE': ['Kiswahili', 'English Language'], 'SCIENCE': ['Physics', 'Chemistry', 'Biology'], 'MATHEMATICS': ['Basic Applied Mathematics', 'Advanced Mathematics'], 'ARTS': ['History', 'Geography', 'General Studies'], 'BUSINESS': ['Economics', 'Commerce', 'Accountancy'], } # Create a mapping of subject names to codes subject_name_to_code = {s.subject_name_sw: s.subject_code for s in subjects} # Build district subject performance by type district_subject_performance = [] for district in districts: district_data = { 'district': district.name, 'region': district.region.name, 'subjects': {} } for subject_type, subject_names in subject_types_to_show.items(): for subject_name in subject_names: # Find subject code subject_code = subject_name_to_code.get(subject_name) if not subject_code: continue # Get scores for this subject in this district scores = subject_scores.filter( subject__subject_code=subject_code, student__school__district=district ) total = scores.count() if total == 0: district_data['subjects'][subject_name] = { 'passed_pct': 0, 'rank': None, 'total': 0 } continue # 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 } district_subject_performance.append(district_data) # Calculate ranks for each subject for subject_name in [name for names in subject_types_to_show.values() for name in names]: # Get all districts that have this subject districts_with_subject = [] for d in district_subject_performance: if d['subjects'].get(subject_name, {}).get('total', 0) > 0: districts_with_subject.append({ 'district': d['district'], 'passed_pct': d['subjects'][subject_name]['passed_pct'] }) # Sort by passed_pct descending districts_with_subject.sort(key=lambda x: -x['passed_pct']) # Assign ranks for idx, d in enumerate(districts_with_subject, 1): for district_data in district_subject_performance: if district_data['district'] == d['district']: if subject_name in district_data['subjects']: district_data['subjects'][subject_name]['rank'] = idx # ====================================================== # TABLE 5: SUBJECT TYPE SUMMARY (Grouped by subject_type) # ====================================================== subject_type_summary = {} for subject in subjects: subject_type = subject.subject_type 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']) # ====================================================== # CHECK IF EXPORT TO WORD IS REQUESTED # ====================================================== if request.GET.get('export') == 'word': # Create a response with proper Word document headers response = HttpResponse(content_type='application/msword') response['Content-Disposition'] = f'attachment; filename="{project.name}_tables.doc"' # Render the Word template return render(request, 'exams/reports/tables_for_word.html', { 'project': project, 'region_data': region_data, 'district_summary_data': district_summary_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, '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, '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, }, response) # Regular HTML preview return render(request, 'exams/reports/tables_preview.html', { 'project': project, 'region_data': region_data, '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, '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, '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, })