from django.shortcuts import render, redirect, get_object_or_404 from django.contrib.auth.decorators import login_required, permission_required from django.contrib.auth import authenticate, login, get_user_model from django.http import JsonResponse, HttpResponse from django.views.decorators.http import require_GET from django.db.models import Avg, Count, Sum, Q, FloatField, F import pprint from .models import ( Region, District, Ward, School, ExamType, ExamSubject, ExamCandidate, StudentSubjectMark, SchoolYearSummary, UserProfile, SchoolSubjectPerformance, SchoolSubjectGradeSummary,SchoolDivisionSummary,JointExamProject,ExamSchoolResult, StudentFinalResult,ProjectStudent,ProcessedSubjectScore,ExamSubjectResult, ExamCombination,ExamScore ) from .gpa_calculation import ( district_subject_gpa, district_division_gpa, region_division_gpa, region_subject_gpa, region_subject_gpa_value, ) def gpa_to_grade_status(gpa): if 1.0000 <= gpa <= 1.5999: return "A","EXCELLENT" if 1.6000 <= gpa <= 2.5999: return "B","VERY GOOD" if 2.6000 <= gpa <= 3.5999: return "C","GOOD" if 3.6000 <= gpa <= 4.5999: return "D","SATISFACTORY" if 4.6000 <= gpa <= 5.0000: return "F","FAIL" return "-", "-" def build_region_summary_context(region_id, year, exam_type_id): years = SchoolYearSummary.objects.values_list("year", flat=True).distinct().order_by("-year") exam_types = ExamType.objects.all() region_id = int(region_id) year = int(year) exam_type_id = int(exam_type_id) region = Region.objects.get(id=region_id) # -------------------------------------------------- # Schools in region (for school_code based tables) # -------------------------------------------------- school_codes = School.objects.filter( region_id=region_id ).values_list("school_code", flat=True) # -------------------------------------------------- # DIVISION SUMMARY (uses FK school) # -------------------------------------------------- base_qs = SchoolDivisionSummary.objects.filter( year=year, exam_type_id=exam_type_id, school__region_id=region_id ) SAT_DIVS = ["I","II","III","IV","0"] ABS_DIV = "X" reg_f = base_qs.filter(sex="F").aggregate(t=Sum("total"))["t"] or 0 reg_m = base_qs.filter(sex="M").aggregate(t=Sum("total"))["t"] or 0 reg = reg_f + reg_m sat_f = base_qs.filter(sex="F", division__in=SAT_DIVS).aggregate(t=Sum("total"))["t"] or 0 sat_m = base_qs.filter(sex="M", division__in=SAT_DIVS).aggregate(t=Sum("total"))["t"] or 0 sat = sat_f + sat_m abs_f = base_qs.filter(sex="F", division=ABS_DIV).aggregate(t=Sum("total"))["t"] or 0 abs_m = base_qs.filter(sex="M", division=ABS_DIV).aggregate(t=Sum("total"))["t"] or 0 abs_total = abs_f + abs_m # DIVISION GPA from .gpa_calculation import region_subject_gpa_value # -------------------------------------------------- # DIVISION GPA (FROM base_qs – ALREADY CORRECT) # -------------------------------------------------- DIV_WEIGHT = {"I":1, "II":2, "III":3, "IV":4, "0":5} div_weighted_sum = sum( DIV_WEIGHT[r.division] * r.total for r in base_qs if r.division in DIV_WEIGHT ) div_total = sum( r.total for r in base_qs if r.division in DIV_WEIGHT ) div_gpa = div_weighted_sum / div_total if div_total else 0 # -------------------------------------------------- # SUBJECT GPA (GLOBAL – SOURCE OF TRUTH) # -------------------------------------------------- subj_gpa = region_subject_gpa_value( year=year, exam_type_id=exam_type_id, region_id=region_id ) # -------------------------------------------------- # FINAL REGION GPA (NECTA OFFICIAL) # -------------------------------------------------- region_final_gpa = round((div_gpa + subj_gpa) / 2, 4) def gpa_to_grade_status(gpa): if 1.0000 <= gpa <= 1.5999: return "A", "EXCELLENT" elif 1.6000 <= gpa <= 2.5999: return "B", "VERY GOOD" elif 2.6000 <= gpa <= 3.5999: return "C", "GOOD" elif 3.6000 <= gpa <= 4.5999: return "D", "SATISFACTORY" elif 4.6000 <= gpa <= 5.000: return "F", "FAIL" return "-", "-" final_grade, final_status = gpa_to_grade_status(region_final_gpa) # -------------------------------------------------- # REGION GPA AVERAGE (uses FK school) # -------------------------------------------------- avg_row = SchoolYearSummary.objects.filter( year=year, exam_type_id=exam_type_id, school__region_id=region_id ) # -------------------------------------------------- # DIVISION COUNTS # -------------------------------------------------- def div_count(div, sex=None): q = Q(division=div) if sex: q &= Q(sex=sex) return base_qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 grades = { "af": div_count("I","F"), "am": div_count("I","M"), "bf": div_count("II","F"), "bm": div_count("II","M"), "cf": div_count("III","F"), "cm": div_count("III","M"), "df": div_count("IV","F"), "dm": div_count("IV","M"), "ef": div_count("0","F"), "em": div_count("0","M"), } grades["a"] = grades["af"] + grades["am"] grades["b"] = grades["bf"] + grades["bm"] grades["c"] = grades["cf"] + grades["cm"] grades["d"] = grades["df"] + grades["dm"] grades["e"] = grades["ef"] + grades["em"] a_c_f = grades["af"] + grades["bf"] + grades["cf"] a_c_m = grades["am"] + grades["bm"] + grades["cm"] a_c_total = a_c_f + a_c_m a_d_f = a_c_f + grades["df"] a_d_m = a_c_m + grades["dm"] a_d_total = a_d_f + a_d_m # -------------------------------------------------- # DISTRICT RANKING # -------------------------------------------------- def gpa_to_grade_status(gpa): gpa = round(float(gpa), 4) # normalize if 1.0000 <= gpa <= 1.5999: return "A", "EXCELLENT" elif 1.6000 <= gpa <= 2.5999: return "B", "VERY GOOD" elif 2.6000 <= gpa <= 3.5999: return "C", "GOOD" elif 3.6000 <= gpa <= 4.5999: return "D", "SATISFACTORY" elif 4.6000 <= gpa <= 5.000: return "F", "FAIL" return "-", "-" # -------------------------------------- # DISTRICT RANKING # -------------------------------------- from .gpa_calculation import ( district_division_gpa, district_subject_gpa, ) district_rows = [] districts = ( District.objects .filter( region_id=region_id, school__schooldivisionsummary__year=year, school__schooldivisionsummary__exam_type_id=exam_type_id, ) .distinct() ) for d in districts: # -------------------------------------- # DIVISION COUNTS (SAT + PASS) # -------------------------------------- qs = SchoolDivisionSummary.objects.filter( year=year, exam_type_id=exam_type_id, school__district=d ) def g(div, sex=None): q = Q(division=div) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 af, am = g("I","F"), g("I","M") bf, bm = g("II","F"), g("II","M") cf, cm = g("III","F"), g("III","M") df, dm = g("IV","F"), g("IV","M") ff, fm = g("0","F"), g("0","M") at, bt = af + am, bf + bm ct, dt = cf + cm, df + dm ft = ff + fm sat_t = at + bt + ct + dt + ft pass_no = at + bt + ct + dt pass_pct = round(pass_no / sat_t * 100, 1) if sat_t else 0 # -------------------------------------- # 🚫 SKIP DISTRICTS WITH NO DATA # -------------------------------------- if sat_t == 0: continue # -------------------------------------- # GPA (SOURCE OF TRUTH) # -------------------------------------- div_gpa = district_division_gpa( year=year, exam_type_id=exam_type_id, district_id=d.id ) subj_qs = district_subject_gpa( year=year, exam_type_id=exam_type_id, district_id=d.id ) subj_weighted = sum(r["gpa"] * r["total_sum"] for r in subj_qs) subj_total = sum(r["total_sum"] for r in subj_qs) subj_gpa = round(subj_weighted / subj_total, 9) if subj_total else 0 district_final_gpa = round((div_gpa + subj_gpa) / 2, 4) grade, status = gpa_to_grade_status(district_final_gpa) # -------------------------------------- # ROW OUTPUT (UNCHANGED STRUCTURE) # -------------------------------------- district_rows.append({ "name": d.name, "sat": sat_t, "af": af, "am": am, "at": at, "bf": bf, "bm": bm, "bt": bt, "cf": cf, "cm": cm, "ct": ct, "df": df, "dm": dm, "dt": dt, "ff": ff, "fm": fm, "ft": ft, "pass_no": pass_no, "pass_pct": pass_pct, "gpa": district_final_gpa, "grade": grade, "status": status, }) # -------------------------------------- # SORT & RANK (BEST FIRST) # -------------------------------------- district_rows.sort(key=lambda x: x["gpa"]) for i, r in enumerate(district_rows, start=1): r["no"] = i r["rank"] = i # ================================================== # WARD PERFORMANCE (FULL DIVISION BREAKDOWN) # ================================================== ward_rows = [] wards = Ward.objects.filter( district__region_id=region_id ) for w in wards: qs = SchoolDivisionSummary.objects.filter( year=year, exam_type_id=exam_type_id, school__ward=w ) def g(div, sex=None): q = Q(division=div) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 # ----------------------------- # DIVISION COUNTS # ----------------------------- af, am = g("I","F"), g("I","M") bf, bm = g("II","F"), g("II","M") cf, cm = g("III","F"), g("III","M") df, dm = g("IV","F"), g("IV","M") ff, fm = g("0","F"), g("0","M") at, bt = af + am, bf + bm ct, dt = cf + cm, df + dm ft = ff + fm sat_t = at + bt + ct + dt + ft pass_no = at + bt + ct + dt pass_pct = round(pass_no / sat_t * 100, 1) if sat_t else 0 # ----------------------------- # WARD GPA (AGGREGATED) # ----------------------------- from .gpa_calculation import ( ward_division_gpa, ward_subject_gpa_value, ) # ----------------------------- # WARD GPA (OFFICIAL – CLEAN) # ----------------------------- ward_div_gpa = ward_division_gpa( year=year, exam_type_id=exam_type_id, ward_id=w.id ) ward_subj_gpa = ward_subject_gpa_value( year=year, exam_type_id=exam_type_id, ward_id=w.id ) ward_gpa = round((ward_div_gpa + ward_subj_gpa) / 2, 4) grade, status = gpa_to_grade_status(ward_gpa) ward_rows.append({ "name": w.name, "sat": sat_t, "af": af, "am": am, "at": at, "bf": bf, "bm": bm, "bt": bt, "cf": cf, "cm": cm, "ct": ct, "df": df, "dm": dm, "dt": dt, "ff": ff, "fm": fm, "ft": ft, "pass_no": pass_no, "pass_pct": pass_pct, "gpa": ward_gpa, "grade": grade, "status": status, }) # ----------------------------- # SORT & RANK (BEST FIRST) # ----------------------------- ward_rows.sort(key=lambda x: x["gpa"]) for i, r in enumerate(ward_rows, start=1): r["no"] = i # ================================================== # BEST 10 SCHOOLS (DISTRICT) – GPA FROM SchoolYearSummary # ================================================== best_schools = [] schools = School.objects.filter(district__region_id=region_id) for s in schools: # ----------------------------- # SCHOOL YEAR SUMMARY (SOURCE OF TRUTH) # ----------------------------- sy = SchoolYearSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id ).first() if not sy or not sy.avg_gpa: continue # skip schools without computed summary # ----------------------------- # DIVISION COUNTS # ----------------------------- qs = SchoolDivisionSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id # ✅ VERY IMPORTANT ) def g(div, sex=None): q = Q(division=div) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 af, am = g("I","F"), g("I","M") bf, bm = g("II","F"), g("II","M") cf, cm = g("III","F"), g("III","M") df, dm = g("IV","F"), g("IV","M") ff, fm = g("0","F"), g("0","M") at, bt = af + am, bf + bm ct, dt = cf + cm, df + dm ft = ff + fm sat_bst = at + bt + ct + dt + ft pass_no = at + bt + ct + dt pass_pct = round(pass_no / sat_bst * 100, 1) if sat_bst else 0 # ----------------------------- # FINAL DATA (FROM SchoolYearSummary) # ----------------------------- best_schools.append({ "code": s.school_code, "name": s.name_short or s.name, "ownership": s.ownership, "district": s.district.name, "sat": sat_bst, "af": af, "am": am, "at": at, "bf": bf, "bm": bm, "bt": bt, "cf": cf, "cm": cm, "ct": ct, "df": df, "dm": dm, "dt": dt, "ff": ff, "fm": fm, "ft": ft, "pass_no": pass_no, "pass_pct": pass_pct, # ✅ OFFICIAL VALUES "gpa": round(float(sy.avg_gpa), 4), "grade": sy.avg_grade, "status": sy.status, }) # ----------------------------- # SORT & LIMIT # ----------------------------- best_schools.sort(key=lambda x: x["gpa"]) best_schools = best_schools[:10] for i, r in enumerate(best_schools, start=1): r["no"] = i # -------------------------------------------------- # SUBJECT and GPA CALC # -------------------------------------------------- from .gpa_calculation import region_subject_gpa # -------------------------------------------------- # SUBJECT GRADE SUMMARY (COUNTS) # -------------------------------------------------- subj_grade_qs = SchoolSubjectGradeSummary.objects.filter( year=year, exam_type_id=exam_type_id, school__region_id=region_id ) # -------------------------------------------------- # SUBJECT GPA (SOURCE OF TRUTH) # -------------------------------------------------- subj_gpa_qs = region_subject_gpa( year=year, exam_type_id=exam_type_id, region_id=region_id ) # Map subject_code → GPA row subj_gpa_map = { r["subject_code"]: r for r in subj_gpa_qs } # -------------------------------------------------- # SUBJECT PERFORMANCE (stored grades/status) # -------------------------------------------------- subj_perf_qs = SchoolSubjectPerformance.objects.filter( year=year, exam_type_id=exam_type_id, school_code__in=school_codes ) subject_map = { s.subject_code: s.subject_name_eng for s in ExamSubject.objects.all() } subjects = subj_grade_qs.values_list("subject_code", flat=True).distinct() subject_rows = [] for code in subjects: def g(grade, sex=None): q = Q(subject_code=code, grade=grade) if sex: q &= Q(sex=sex) return subj_grade_qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 # ----------------------------- # GRADE COUNTS # ----------------------------- af, am = g("A","F"), g("A","M") bf, bm = g("B","F"), g("B","M") cf, cm = g("C","F"), g("C","M") df, dm = g("D","F"), g("D","M") ff, fm = g("F","F"), g("F","M") at, bt = af + am, bf + bm ct, dt = cf + cm, df + dm ft = ff + fm sat_sub = at + bt + ct + dt + ft pass_no = at + bt + ct + dt pass_pct = round(pass_no / sat_sub * 100, 1) if sat_sub else 0 # ----------------------------- # GPA (FROM gpa_calculation) # ----------------------------- gpa_row = subj_gpa_map.get(code) subj_gpa = round(gpa_row["gpa"], 4) if gpa_row else 0 subj_grade, subj_status = gpa_to_grade_status(subj_gpa) subject_rows.append({ "code": code, "subject": subject_map.get(code, code), "sat": sat_sub, "af": af, "am": am, "at": at, "bf": bf, "bm": bm, "bt": bt, "cf": cf, "cm": cm, "ct": ct, "df": df, "dm": dm, "dt": dt, "ff": ff, "fm": fm, "ft": ft, "pass_no": pass_no, "pass_pct": pass_pct, # ✅ CORRECT, CONSISTENT GPA "gpa": subj_gpa, "grade": subj_grade, "status": subj_status, }) div_qs = SchoolDivisionSummary.objects.filter( year=year, exam_type_id=exam_type_id, school__region_id=region.id ) division_pie = [] DIV_ORDER = ["I", "II", "III", "IV", "0"] rows = ( div_qs .values("division") .annotate(total=Sum("total")) ) total_sat = sum(r["total"] for r in rows) div_map = {r["division"]: r["total"] for r in rows} for d in DIV_ORDER: if d in div_map: division_pie.append({ "division": d, "total": div_map[d], "pct": round(div_map[d] / total_sat * 100, 1) if total_sat else 0 }) pass_gender_pie = [] pass_qs = div_qs.filter(division__in=["I","II","III","IV"]) pf = pass_qs.filter(sex="F").aggregate(t=Sum("total"))["t"] or 0 pm = pass_qs.filter(sex="M").aggregate(t=Sum("total"))["t"] or 0 pass_total = pf + pm if pass_total: pass_gender_pie = [ {"sex": "F", "total": pf, "pct": round(pf / pass_total * 100, 1)}, {"sex": "M", "total": pm, "pct": round(pm / pass_total * 100, 1)}, ] division_gender_bar = [] for d in DIV_ORDER: f = div_qs.filter(division=d, sex="F").aggregate(t=Sum("total"))["t"] or 0 m = div_qs.filter(division=d, sex="M").aggregate(t=Sum("total"))["t"] or 0 if f or m: division_gender_bar.append({ "division": d, "f": f, "m": m }) exam = ExamType.objects.get(id=exam_type_id) # -------------------------------------------------- # FINAL CONTEXT # -------------------------------------------------- return { "region": region, "exam_type": exam_type_id, "exam": exam, "year": year, "totals": { "reg_f": reg_f, "reg_m": reg_m, "reg": reg, "sat_f": sat_f, "sat_m": sat_m, "sat": sat, "abs_f": abs_f, "abs_m": abs_m, "abs": abs_total, "avg": region_final_gpa, "grade": final_grade, "status": final_status, }, "district_perf": district_rows, "grades": grades, "ward_perf": ward_rows, "best_schools": best_schools, "years": years, "exam_types": exam_types, "division_pie": division_pie, "pass_gender_pie": pass_gender_pie, "division_gender_bar": division_gender_bar, "a_c_f": a_c_f, "a_c_m": a_c_m, "a_c_total": a_c_total, "a_d_f": a_d_f, "a_d_m": a_d_m, "a_d_total": a_d_total, "subjects": subject_rows } from django.shortcuts import get_object_or_404 from django.db.models import Avg, Sum def build_region_trend(region_id, exam_type, start, end): region = get_object_or_404(Region, id=region_id) start = int(start) end = int(end) qs = ( SchoolYearSummary.objects .filter( school__region_id=region_id, exam_type_id=exam_type_id, year__gte=start, year__lte=end ) .values("year") .annotate( avg_score=Avg("avg_numeric"), total_candidates=Sum("candidates") ) .order_by("year") ) rows = [] for r in qs: avg = round(float(r["avg_score"] or 0), 2) rows.append({ "year": r["year"], "avg": avg, "grade": numeric_to_grade(avg), "total": r["total_candidates"] or 0, }) exam = ExamType.objects.get(id=exam_type_id) return { "region": region, "exam_type": exam_type, "exam": exam, "start": start, "end": end, "rows": rows, } def compute_region_school_rank(region_id, year, exam_type_id): region_id = int(region_id) year = int(year) exam_type_id = int(exam_type_id) DIV_WEIGHT = {"I":1,"II":2,"III":3,"IV":4,"0":5} schools = School.objects.filter( district__region_id=region_id, schoolyearsummary__year=year, schoolyearsummary__exam_type_id=exam_type_id ).distinct() # schools = School.objects.filter(district__region_id=region_id) rows = [] for s in schools: qs = SchoolDivisionSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id ) def d(div): return qs.filter(division=div).aggregate(t=Sum("total"))["t"] or 0 d1,d2,d3,d4,d0 = d("I"),d("II"),d("III"),d("IV"),d("0") pass_i_iv = d1+d2+d3+d4 total = pass_i_iv + d0 pass_pct = round(pass_i_iv/total*100,2) if total else 0 weighted_sum = d1*1+d2*2+d3*3+d4*4+d0*5 sy = SchoolYearSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id ).first() gpa = round(float(sy.avg_gpa), 4) if sy and sy.avg_gpa else 0 rows.append({ "code": s.school_code, "ownership": s.ownership, "name": s.name_short, "district": s.district.name, "region": s.district.region.name, "d1": d1, "d2": d2, "d3": d3, "d4": d4, "d0": d0, "pass": pass_i_iv, "pass_pct": pass_pct, "gpa": gpa }) # ---------- SORT ---------- rows.sort(key=lambda x: (x["gpa"] == 0, x["gpa"])) # ---------- RANK ---------- for i,r in enumerate(rows,1): r["rank"] = i return rows from django.db.models import Sum, Q def compute_region_school_rank_ext(region_id, year, exam_type_id): region_id = int(region_id) year = int(year) exam_type_id = int(exam_type_id) schools = School.objects.filter( district__region_id=region_id, schoolyearsummary__year=year, schoolyearsummary__exam_type_id=exam_type_id ).distinct().select_related("district","district__region") rows = [] for s in schools: qs = SchoolDivisionSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id ) def g(div, sex=None): q = Q(division=div) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 reg_f = qs.filter(sex="F").aggregate(t=Sum("total"))["t"] or 0 reg_m = qs.filter(sex="M").aggregate(t=Sum("total"))["t"] or 0 reg_t = reg_f + reg_m SAT_DIVS = ["I","II","III","IV","0"] sat_f = qs.filter(sex="F", division__in=SAT_DIVS).aggregate(t=Sum("total"))["t"] or 0 sat_m = qs.filter(sex="M", division__in=SAT_DIVS).aggregate(t=Sum("total"))["t"] or 0 sat_t = sat_f + sat_m abs_f = reg_f - sat_f abs_m = reg_m - sat_m abs_t = abs_f + abs_m # ---- DIV I ---- i_f = g("I","F"); i_m = g("I","M"); i_t = i_f + i_m # ---- DIV II ---- ii_f = g("II","F"); ii_m = g("II","M"); ii_t = ii_f + ii_m # ---- DIV III ---- iii_f = g("III","F"); iii_m = g("III","M"); iii_t = iii_f + iii_m # ---- DIV IV ---- iv_f = g("IV","F"); iv_m = g("IV","M"); iv_t = iv_f + iv_m # ---- FAIL ---- f_f = g("0","F"); f_m = g("0","M"); f_t = f_f + f_m # ---- PASS I-III ---- pass_i_iii_f = i_f+ii_f+iii_f pass_i_iii_m = i_m+ii_m+iii_m pass_i_iii_t = pass_i_iii_f + pass_i_iii_m pass_i_iii_pct = round(pass_i_iii_t/sat_t*100,2) if sat_t else 0 # ---- PASS I-IV ---- pass_i_iv_f = pass_i_iii_f + iv_f pass_i_iv_m = pass_i_iii_m + iv_m pass_i_iv_t = pass_i_iv_f + pass_i_iv_m pass_i_iv_pct = round(pass_i_iv_t/sat_t*100,2) if sat_t else 0 i_pct = round(i_t / reg_t * 100, 2) if reg_t else 0 ii_pct = round(ii_t / reg_t * 100, 2) if reg_t else 0 iii_pct = round(iii_t / reg_t * 100, 2) if reg_t else 0 iv_pct = round(iv_t / reg_t * 100, 2) if reg_t else 0 f_pct = round(f_t / reg_t * 100, 2) if reg_t else 0 # ---- GPA FROM SUMMARY ---- sy = SchoolYearSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id ).first() gpa = round(float(sy.avg_gpa),4) if sy and sy.avg_gpa else 0 grade = sy.avg_grade if sy else "-" status = sy.status if sy else "-" exam = ExamType.objects.get(id=exam_type_id) rows.append({ "no": 0, # filled after sorting "cno": s.school_code, "name": s.name_short, "region": s.district.region.name, "district": s.district.name, "ownership": s.ownership, "exam": exam.code, "reg_f": reg_f, "reg_m": reg_m, "reg_t": reg_t, "sat_f": sat_f, "sat_m": sat_m, "sat_t": sat_t, "abs_f": abs_f, "abs_m": abs_m, "abs_t": abs_t, "i_f": i_f, "i_m": i_m, "i_t": i_t, "ii_f": ii_f, "ii_m": ii_m, "ii_t": ii_t, "iii_f": iii_f, "iii_m": iii_m, "iii_t": iii_t, "iv_f": iv_f, "iv_m": iv_m, "iv_t": iv_t, "f_f": f_f, "f_m": f_m, "f_t": f_t, "pass_i_iii_f": pass_i_iii_f, "pass_i_iii_m": pass_i_iii_m, "pass_i_iii_t": pass_i_iii_t, "pass_i_iii_pct": pass_i_iii_pct, "pass_i_iv_f": pass_i_iv_f, "pass_i_iv_m": pass_i_iv_m, "pass_i_iv_t": pass_i_iv_t, "pass_i_iv_pct": pass_i_iv_pct, "i_pct": i_pct, "ii_pct": ii_pct, "iii_pct": iii_pct, "iv_pct": iv_pct, "f_pct": f_pct, "gpa": gpa, "grade": grade, "status": status, }) # ---- SORT BY GPA (BEST FIRST) ---- rows.sort(key=lambda x:(x["gpa"]==0, x["gpa"])) # ---- POSITION ---- for i,r in enumerate(rows,1): r["position"] = i r["no"] = i return rows from django.db.models import Sum def compute_district_school_rank(district_id, year, exam_type_id): district_id = int(district_id) year = int(year) exam_type_id = int(exam_type_id) schools = School.objects.filter(district_id=district_id) rows = [] rank = 1 for s in schools: # ================= SCHOOL YEAR SUMMARY ================= sy = SchoolYearSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id ).first() if not sy or sy.avg_gpa is None: continue # ================= DIVISION COUNTS ================= div_qs = SchoolDivisionSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id ) def d(div): return div_qs.filter(division=div).aggregate(t=Sum("total"))["t"] or 0 d1 = d("I") d2 = d("II") d3 = d("III") d4 = d("IV") d0 = d("0") sat = d1 + d2 + d3 + d4 + d0 pass_i_iv = d1 + d2 + d3 + d4 pass_pct = round(pass_i_iv / sat * 100, 2) if sat else 0 rows.append({ "school": s, "code": s.school_code, "ownership": s.ownership, "name": s.name, "district": s.district.name, "region": s.district.region.name, "d1": d1, "d2": d2, "d3": d3, "d4": d4, "d0": d0, "sat": sat, "pass": pass_i_iv, "pass_pct": pass_pct, # GPA SOURCE OF TRUTH "gpa": round(float(sy.avg_gpa), 4), "grade": sy.avg_grade, "status": sy.status, }) # ================= SORT & RANK ================= rows.sort(key=lambda x: x["gpa"]) for i, r in enumerate(rows, start=1): r["rank"] = i return rows from django.db.models import Sum, Q def compute_district_school_rank_ext(district_id, year, exam_type_id): district_id = int(district_id) year = int(year) exam_type_id = int(exam_type_id) schools = School.objects.filter( district_id=district_id, schoolyearsummary__year=year, schoolyearsummary__exam_type_id=exam_type_id ).distinct().select_related("district", "district__region") rows = [] for s in schools: qs = SchoolDivisionSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id ) def g(div, sex=None): q = Q(division=div) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 # ================= REGISTERED ================= reg_f = qs.filter(sex="F").aggregate(t=Sum("total"))["t"] or 0 reg_m = qs.filter(sex="M").aggregate(t=Sum("total"))["t"] or 0 reg_t = reg_f + reg_m SAT_DIVS = ["I", "II", "III", "IV", "0"] # ================= SAT ================= sat_f = qs.filter(sex="F", division__in=SAT_DIVS).aggregate(t=Sum("total"))["t"] or 0 sat_m = qs.filter(sex="M", division__in=SAT_DIVS).aggregate(t=Sum("total"))["t"] or 0 sat_t = sat_f + sat_m # ================= ABSENT ================= abs_f = reg_f - sat_f abs_m = reg_m - sat_m abs_t = abs_f + abs_m # ================= DIVISIONS ================= i_f = g("I","F"); i_m = g("I","M"); i_t = i_f + i_m ii_f = g("II","F"); ii_m = g("II","M"); ii_t = ii_f + ii_m iii_f = g("III","F");iii_m = g("III","M");iii_t = iii_f + iii_m iv_f = g("IV","F"); iv_m = g("IV","M"); iv_t = iv_f + iv_m f_f = g("0","F"); f_m = g("0","M"); f_t = f_f + f_m # ================= PASS ================= pass_i_iii_f = i_f + ii_f + iii_f pass_i_iii_m = i_m + ii_m + iii_m pass_i_iii_t = pass_i_iii_f + pass_i_iii_m pass_i_iii_pct = round(pass_i_iii_t / sat_t * 100, 2) if sat_t else 0 pass_i_iv_f = pass_i_iii_f + iv_f pass_i_iv_m = pass_i_iii_m + iv_m pass_i_iv_t = pass_i_iv_f + pass_i_iv_m pass_i_iv_pct = round(pass_i_iv_t / sat_t * 100, 2) if sat_t else 0 # ================= DIVISION % OF REGISTERED ================= i_pct = round(i_t / reg_t * 100, 2) if reg_t else 0 ii_pct = round(ii_t / reg_t * 100, 2) if reg_t else 0 iii_pct = round(iii_t / reg_t * 100, 2) if reg_t else 0 iv_pct = round(iv_t / reg_t * 100, 2) if reg_t else 0 f_pct = round(f_t / reg_t * 100, 2) if reg_t else 0 # ================= GPA FROM SUMMARY ================= sy = SchoolYearSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id ).first() gpa = round(float(sy.avg_gpa), 4) if sy and sy.avg_gpa else 0 grade = sy.avg_grade if sy else "-" status = sy.status if sy else "-" exam = ExamType.objects.get(id=exam_type_id) rows.append({ "no": 0, # filled after sorting "cno": s.school_code, "name": s.name_short, "region": s.district.region.name, "district": s.district.name, "ownership": s.ownership, "exam": exam, "reg_f": reg_f, "reg_m": reg_m, "reg_t": reg_t, "sat_f": sat_f, "sat_m": sat_m, "sat_t": sat_t, "abs_f": abs_f, "abs_m": abs_m, "abs_t": abs_t, "i_f": i_f, "i_m": i_m, "i_t": i_t, "ii_f": ii_f, "ii_m": ii_m, "ii_t": ii_t, "iii_f": iii_f, "iii_m": iii_m, "iii_t": iii_t, "iv_f": iv_f, "iv_m": iv_m, "iv_t": iv_t, "f_f": f_f, "f_m": f_m, "f_t": f_t, "pass_i_iii_f": pass_i_iii_f, "pass_i_iii_m": pass_i_iii_m, "pass_i_iii_t": pass_i_iii_t, "pass_i_iii_pct": pass_i_iii_pct, "pass_i_iv_f": pass_i_iv_f, "pass_i_iv_m": pass_i_iv_m, "pass_i_iv_t": pass_i_iv_t, "pass_i_iv_pct": pass_i_iv_pct, "i_pct": i_pct, "ii_pct": ii_pct, "iii_pct": iii_pct, "iv_pct": iv_pct, "f_pct": f_pct, "gpa": gpa, "grade": grade, "status": status, }) # ================= SORT BY GPA (BEST FIRST) ================= rows.sort(key=lambda x: (x["gpa"] == 0, x["gpa"])) # ================= POSITION ================= for i, r in enumerate(rows, 1): r["position"] = i r["no"] = i return rows from django.db.models import Sum, Q def compute_subject_district_rank(region_id, exam_type_id, year, subject_code): rows = [] districts = District.objects.filter(region_id=region_id) SUB_WEIGHT = {"A":1,"B":2,"C":3,"D":4,"F":5} for d in districts: qs = SchoolSubjectGradeSummary.objects.filter( year=int(year), exam_type_id=int(exam_type_id), subject_code=subject_code, school__district_id=d.id ) if not qs.exists(): continue def g(grade, sex=None): q = Q(grade=grade) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 af, am = g("A","F"), g("A","M") bf, bm = g("B","F"), g("B","M") cf, cm = g("C","F"), g("C","M") df, dm = g("D","F"), g("D","M") ff, fm = g("F","F"), g("F","M") at, bt, ct, dt, ft = af+am, bf+bm, cf+cm, df+dm, ff+fm sat = at+bt+ct+dt+ft pass_no = at+bt+ct+dt pass_pct = round(pass_no/sat*100,2) if sat else 0 weighted_sum = at*1 + bt*2 + ct*3 + dt*4 + ft*5 gpa = round(weighted_sum/sat,4) if sat else 0 grade,status = gpa_to_grade_status(gpa) rows.append({ "district": d.name, "sat": sat, "af":af,"am":am,"at":at, "bf":bf,"bm":bm,"bt":bt, "cf":cf,"cm":cm,"ct":ct, "df":df,"dm":dm,"dt":dt, "ff":ff,"fm":fm,"ft":ft, "pass_no": pass_no, "pass_pct": pass_pct, "gpa": gpa, "grade": grade, "status": status }) rows.sort(key=lambda x: x["gpa"] if x["gpa"]>0 else 99) for i,r in enumerate(rows, start=1): r["rank"] = i return rows # -------------------------------------------------------------- # EVERYTHING-SCHOOL # -------------------------------------------------------------- def build_school_rank_data(region_id, year, district_id=None, ward_id=None): """ Returns (schools_list, totals) Each school row has: school (School instance), name, school_code, af,am,a, bf,bm,b, ... ef,em,e, total_std, total_schools (1), pass_f, pass_m, pass_t, pass_pct, avg_score (mean of SchoolYearSummary.avg_numeric for that school-year) """ year = int(year) base_qs = ExamCandidate.objects.filter(year=year, school__region_id=region_id) if district_id: base_qs = base_qs.filter(school__district_id=district_id) if ward_id: base_qs = base_qs.filter(school__ward_id=ward_id) # School does NOT have an id; it uses school_code as PK school_codes = base_qs.values_list("school__school_code", flat=True).distinct() schools = School.objects.filter(school__region_id=region_id).select_related("ward", "district") rows = [] totals = { "af":0,"am":0,"a":0, "bf":0,"bm":0,"b":0, "cf":0,"cm":0,"c":0, "df":0,"dm":0,"d":0, "ef":0,"em":0,"e":0, "total_std":0, "pass_f":0,"pass_m":0,"pass_t":0, "total_schools":0, } for s in schools: s_qs = base_qs.filter(school_id=s.school_code) # grade counts: def g(grade, sex=None): f = {"avg_grade__iexact": grade} if sex: f["sex__iexact"] = sex return s_qs.filter(**f).count() af, am = g("A", "F"), g("A", "M") bf, bm = g("B", "F"), g("B", "M") cf, cm = g("C", "F"), g("C", "M") df, dm = g("D", "F"), g("D", "M") ef, em = g("E", "F"), g("E", "M") a = af + am b = bf + bm c = cf + cm d = df + dm e = ef + em total_std = s_qs.count() pass_f = af + bf + cf pass_m = am + bm + cm pass_t = pass_f + pass_m pass_pct = round((pass_t / total_std * 100), 2) if total_std else 0 # avg_score: mean of SchoolYearSummary.avg_numeric for this school-year (keeps parity with other modules) sy = SchoolYearSummary.objects.filter(school_id=s.school_code, year=year).aggregate(avg=Avg("avg_numeric")) # avg_score safe conversion sy_avg = sy["avg"] if sy_avg is None: avg_score = 0 else: avg_score = round(float(sy_avg), 2) # pass_pct safe conversion if total_std > 0: pass_pct = round((pass_t / total_std * 100), 2) else: pass_pct = 0 row = { "school": s, "name": s.name_short, "school_code": getattr(s, "school_code", ""), "af": af, "am": am, "a": a, "bf": bf, "bm": bm, "b": b, "cf": cf, "cm": cm, "c": c, "df": df, "dm": dm, "d": d, "ef": ef, "em": em, "e": e, "total_std": total_std, "total_schools": 1, "pass_f": pass_f, "pass_m": pass_m, "pass_t": pass_t, "pass_pct": pass_pct, "avg_score": avg_score, } rows.append(row) # accumulate totals for k in ["af","am","a","bf","bm","b","cf","cm","c","df","dm","d","ef","em","e","total_std","pass_f","pass_m","pass_t","total_schools"]: totals[k] = totals.get(k, 0) + row.get(k, 0) # sort by pass percentage DESC, then avg_score DESC (same criteria you used for wards) rows.sort(key=lambda x: (x["pass_pct"], x["avg_score"], x["pass_t"]), reverse=True) # add rank for i, r in enumerate(rows, start=1): r["rank"] = i # compute overall avg_score for totals (average of school averages) totals["avg_score"] = round(sum(r["avg_score"] for r in rows) / len(rows), 2) if rows else 0 pprint.pp(rows) return rows, totals def compute_district_ranking(region_id, year): region = get_object_or_404(Region, pk=region_id) cand = ExamCandidate.objects.filter(year=year, school__region_id=region.id) districts = [] for d in District.objects.filter(region_id=region.id): dq = cand.filter(school__district_id=d.id) # ---- Grade counts ---- def g(grade, sex=None): q = dq.filter(avg_grade__iexact=grade) if sex: q = q.filter(sex__iexact=sex) return q.count() af = g("A", "F"); am = g("A", "M") bf = g("B", "F"); bm = g("B", "M") cf = g("C", "F"); cm = g("C", "M") df_ = g("D", "F"); dm = g("D", "M") ef = g("E", "F"); em = g("E", "M") a = af + am b = bf + bm c = cf + cm d_ = df_ + dm e_ = ef + em total_std = a + b + c + d_ + e_ pass_f = af + bf + cf pass_m = am + bm + cm pass_t = pass_f + pass_m pass_pct = round((pass_t / total_std * 100), 1) if total_std else 0 avg_row = SchoolYearSummary.objects.filter( school__district_id=d.id, year=year ).aggregate(avg=Avg("avg_numeric")) avg_score = round(avg_row["avg"] or 0, 1) total_schools = School.objects.filter(district_id=d.id).count() districts.append({ "district": d.name, "af": af, "am": am, "a": a, "bf": bf, "bm": bm, "b": b, "cf": cf, "cm": cm, "c": c, "df": df_, "dm": dm, "d": d_, "ef": ef, "em": em, "e": e_, "total_std": total_std, "total_schools": total_schools, "pass_f": pass_f, "pass_m": pass_m, "pass_t": pass_t, "pass_ac_pct": pass_pct, "avg_score": avg_score, }) # SORTING districts.sort(key=lambda x: (x["pass_ac_pct"], x["avg_score"], x["pass_t"]), reverse=True) # ADD RANK for i, d in enumerate(districts, 1): d["rank"] = i totals = { "af": sum(d["af"] for d in districts), "am": sum(d["am"] for d in districts), "a": sum(d["a"] for d in districts), "bf": sum(d["bf"] for d in districts), "bm": sum(d["bm"] for d in districts), "b": sum(d["b"] for d in districts), "cf": sum(d["cf"] for d in districts), "cm": sum(d["cm"] for d in districts), "c": sum(d["c"] for d in districts), "df": sum(d["df"] for d in districts), "dm": sum(d["dm"] for d in districts), "d": sum(d["d"] for d in districts), "ef": sum(d["ef"] for d in districts), "em": sum(d["em"] for d in districts), "e": sum(d["e"] for d in districts), "total_std": sum(d["total_std"] for d in districts), "total_schools": sum(d["total_schools"] for d in districts), "pass_f": sum(d["pass_f"] for d in districts), "pass_m": sum(d["pass_m"] for d in districts), "pass_t": sum(d["pass_t"] for d in districts), "avg_score": round( sum(d["avg_score"] for d in districts) / len(districts), 1 ) if districts else 0, } return { "region": region, "rows": districts, "totals": totals, } # -------------------------------------------------------------- # BEST BUTTOM # -------------------------------------------------------------- def numeric_to_grade(avg): if avg is None: return "-" avg = float(avg) if avg >= 81: return "A" if avg >= 61: return "B" if avg >= 41: return "C" if avg >= 21: return "D" return "E" def get_school_stats(region_id, year): summaries = SchoolYearSummary.objects.filter( year=year, school__region_id=region_id ).select_related("school", "school__district") rows = [] for s in summaries: school = s.school qs = ExamCandidate.objects.filter(year=year, school_id=school.school_code) def g(grade): return qs.filter(avg_grade__iexact=grade).count() A = g("A") B = g("B") C = g("C") D = g("D") E = g("E") total = s.candidates or 0 pass_ac = A + B + C pass_pct = round((pass_ac / total * 100), 2) if total else 0 # use existing avg_grade and status directly avg = round(float(s.avg_numeric or 0), 4) avg_grade = s.avg_grade or "-" status = s.status or "-" rows.append({ "school_code": school.school_code, "name": school.name, "district": school.district.name if school.district else "", "total": total, "A": A, "B": B, "C": C, "D": D, "E": E, "pass_ac": pass_ac, "pass_pct": pass_pct, "avg": avg, "avg_grade": avg_grade, "status": status, "ownership": (school.ownership or "").upper(), }) return rows def build_best_worst_tables(region_id, year): rows = get_school_stats(region_id, year) top10 = sorted(rows, key=lambda x: x["avg"], reverse=True)[:10] bottom10 = sorted(rows, key=lambda x: x["avg"])[:10] large_schools = [r for r in rows if r["total"] >= 30] top10_big = sorted(large_schools, key=lambda x: x["avg"], reverse=True)[:10] bottom10_big = sorted(large_schools, key=lambda x: x["avg"])[:10] gov = [r for r in rows if r["ownership"] == "GVT"] top10_gov = sorted(gov, key=lambda x: x["avg"], reverse=True)[:10] return { "top10": top10, "bottom10": bottom10, "top10_big": top10_big, "bottom10_big": bottom10_big, "top10_gov": top10_gov, } def build_ward_rank_data(region_id, year): cand_qs = ExamCandidate.objects.filter( school__region_id=region_id, year=year ).select_related("school__ward", "school__district") ward_ids = cand_qs.values_list("school__ward_id", flat=True).distinct() data = [] totals = { "af":0,"am":0,"a":0, "bf":0,"bm":0,"b":0, "cf":0,"cm":0,"c":0, "df":0,"dm":0,"d":0, "ef":0,"em":0,"e":0, "total_std":0, "total_schools":0, "pass_f":0, "pass_m":0, "pass_t":0 } for wid in ward_ids: w_qs = cand_qs.filter(school__ward_id=wid) if not w_qs.exists(): continue ward = Ward.objects.get(id=wid) district_name = ward.district.name # 🔥 ADDED DISTRICT # Grade counts g = lambda grade, sex=None: w_qs.filter( avg_grade__iexact=grade, **({"sex__iexact": sex} if sex else {}) ).count() af, am = g("A", "F"), g("A", "M") bf, bm = g("B", "F"), g("B", "M") cf, cm = g("C", "F"), g("C", "M") df, dm = g("D", "F"), g("D", "M") ef, em = g("E", "F"), g("E", "M") total = w_qs.count() # Totals a = af + am b = bf + bm c = cf + cm d = df + dm e = ef + em # Pass counts A–C pass_f = af + bf + cf pass_m = am + bm + cm pass_t = pass_f + pass_m # Pass percentage pass_pct = round((pass_t / total * 100), 2) if total else 0 # Ward average = mean of SCHOOL averages school_avg = ( SchoolYearSummary.objects.filter( school__ward_id=wid, year=year ) .aggregate(avg=Avg("avg_numeric")) )["avg"] or 0 school_avg = round(float(school_avg), 2) school_count = w_qs.values("school_id").distinct().count() row = { "id": wid, "name": f"{ward.name} ({district_name})", # 🔥 SHOW DISTRICT "district": district_name, "af": af, "am": am, "a": a, "bf": bf, "bm": bm, "b": b, "cf": cf, "cm": cm, "c": c, "df": df, "dm": dm, "d": d, "ef": ef, "em": em, "e": e, "total_std": total, "total_schools": school_count, "pass_f": pass_f, "pass_m": pass_m, "pass_t": pass_t, "pass_pct": pass_pct, # 🔥 NEW "avg_score": school_avg, # 🔥 UPDATED LOGIC } data.append(row) # Add to totals for k in totals: if k in row: totals[k] += row[k] # 🔥🔥 NEW RANKING ORDERING # 1. Pass percentage DESC # 2. Average school score DESC data = sorted( data, key=lambda x: (x["pass_pct"], x["avg_score"]), reverse=True ) # Assign rank numbers for idx, d in enumerate(data, start=1): d["rank"] = idx return data, totals from django.db.models import Sum, Avg from exams.models import ( Region, School, SchoolSubjectPerformance, StudentSubjectMark, ExamSubject ) def get_region_subject_summary(region_id, year, subject="ALL"): region = Region.objects.get(pk=region_id) school_codes = School.objects.filter( region_id=region_id ).values_list("school_code", flat=True) # Subject map (IMPORTANT FIX) subject_map = { str(s.subject_id): s.subject_name_sw for s in ExamSubject.objects.all() } if subject == "ALL": subject_codes = ( SchoolSubjectPerformance.objects .filter(year=year, school__region_id=region_id) .values_list("subject_code", flat=True) .distinct() .order_by("subject_code") ) else: subject_codes = [subject] rows = [] for subj in subject_codes: agg = SchoolSubjectPerformance.objects.filter( year=year, subject_code=subj, school__region_id=region_id ).aggregate( total_std=Sum("total_std"), avg_subj=Avg("subj_avg"), ) total_std = agg["total_std"] or 0 avg_subj = round(float(agg["avg_subj"] or 0), 2) marks_q = StudentSubjectMark.objects.filter( year=year, subject_code=subj, school__school__region_id=region_id ) a = marks_q.filter(grade="A").count() b = marks_q.filter(grade="B").count() c = marks_q.filter(grade="C").count() d = marks_q.filter(grade="D").count() e = marks_q.filter(grade="E").count() pass_ac = a + b + c pass_pct = round((pass_ac / total_std * 100), 2) if total_std else 0 rows.append({ "subject_code": subj, "subject_name_sw": subject_map.get(str(subj), "UNKNOWN"), "total_std": total_std, "A": a, "B": b, "C": c, "D": d, "E": e, "pass_ac": pass_ac, "pass_pct": pass_pct, "avg_subj": avg_subj, }) return region, rows def get_district_subject_ranking(region_id, year, subject_code): region = Region.objects.get(pk=region_id) # ---- SUBJECT NAME MAP ---- subject_map = { str(s.subject_id): s.subject_name_sw for s in ExamSubject.objects.all() } rows = [] districts = District.objects.filter(region_id=region_id) for d in districts: school_codes = School.objects.filter( district=d ).values_list("school_code", flat=True) q = SchoolSubjectPerformance.objects.filter( year=year, subject_code=subject_code, school__region_id=region_id ) total_std = q.aggregate(t=Sum("total_std"))["t"] or 0 avg_subj = q.aggregate(a=Avg("subj_avg"))["a"] or 0 pass_ac = q.filter( subj_grade__in=["A", "B", "C"] ).aggregate(p=Sum("total_std"))["p"] or 0 pass_pct = round((pass_ac / total_std * 100), 2) if total_std else 0 rows.append({ "district": d.name, "total": total_std, "avg": round(float(avg_subj), 2), "pass_ac": pass_ac, "pass_pct": pass_pct, }) rows.sort(key=lambda x: (x["avg"], x["pass_pct"]), reverse=True) for i, r in enumerate(rows, start=1): r["rank"] = i return { "region": region, "year": year, "subject_code": subject_code, "subject_name_sw": subject_map.get(str(subject_code), "UNKNOWN"), "rows": rows, # ✅ STANDARD NAME "ranking": rows, # ✅ backward compatibility } def get_regional_subject_school_ranking(region_id, year, subject_code): region = Region.objects.get(pk=region_id) # --- SUBJECT NAME MAP (robust) --- subject_map = { str(s.subject_code): s.subject_name_sw for s in ExamSubject.objects.all() } subject_code = str(subject_code).strip() subject_name_sw = subject_map.get(subject_code, "UNKNOWN SUBJECT") schools = School.objects.filter(region_id=region_id) rows = [] for s in schools: perf = SchoolSubjectPerformance.objects.filter( school_code=s.school_code, year=year, subject_code=subject_code ).first() if not perf: continue total = perf.total_std or 0 avg = float(perf.subj_avg or 0) pass_ac = total if perf.subj_grade in ["A", "B", "C"] else 0 pass_pct = round((pass_ac / total * 100), 2) if total else 0 rows.append({ "school_code": s.school_code, "school": s.name, "district": s.district.name if s.district else "", "total": total, "pass_ac": pass_ac, "pass_pct": pass_pct, "avg": round(avg, 4), }) # SORT rows.sort(key=lambda r: (r["avg"], r["pass_pct"]), reverse=True) # RANK for i, r in enumerate(rows, start=1): r["rank"] = i return { "region": region, "year": year, "subject_code": subject_code, "subject_name_sw": subject_name_sw, # ✅ FIX "rows": rows, } from django.db.models import Avg from exams.models import District, Ward, School, ExamCandidate, SchoolYearSummary def build_district_ward_ranking_context(district_id, year): district = District.objects.get(pk=district_id) rows = [] wards = Ward.objects.filter(district=district) for w in wards: cand_qs = ExamCandidate.objects.filter( year=year, school__ward=w ) total = cand_qs.exclude(avg_grade="A-ABS").count() pass_ac = cand_qs.filter(avg_grade__in=["A","B","C"]).count() avg_row = SchoolYearSummary.objects.filter( school__ward=w, year=year ).aggregate(avg=Avg("avg_numeric")) avg_score = round(avg_row["avg"] or 0, 2) pass_pct = round(pass_ac / total * 100, 2) if total else 0 rows.append({ "ward": w.name, "total": total, "pass": pass_ac, "pass_pct": pass_pct, "avg": avg_score, }) rows.sort(key=lambda x: (x["avg"], x["pass_pct"]), reverse=True) for i, r in enumerate(rows, start=1): r["rank"] = i return { "district": district, "year": year, "rows": rows, } def build_district_school_ranking_context(district_id, year): district = District.objects.get(pk=district_id) rows = [] school_qs = SchoolYearSummary.objects.filter( school__district=district, year=year ).select_related("school") for s in school_qs: total = ExamCandidate.objects.filter( year=year, school=s.school ).exclude(avg_grade="A-ABS").count() pass_ac = ExamCandidate.objects.filter( year=year, school=s.school, avg_grade__in=["A","B","C"] ).count() rows.append({ "school": s.school.name, "ward": s.school.ward.name if s.school.ward else "", "ownership": s.school.ownership, "total": total, "pass": pass_ac, "pass_pct": round(pass_ac / total * 100, 2) if total else 0, "avg": round(float(s.avg_numeric or 0), 2), }) rows.sort(key=lambda x: (x["avg"], x["pass_pct"]), reverse=True) for i, r in enumerate(rows, start=1): r["rank"] = i return { "district": district, "year": year, "rows": rows, } def get_district_school_stats(district_id, year): summaries = ( SchoolYearSummary.objects .filter( year=year, school__district_id=district_id ) .select_related("school", "school__district") ) rows = [] for s in summaries: school = s.school qs = ExamCandidate.objects.filter( year=year, school__school_code=school.school_code ) def g(grade): return qs.filter(avg_grade__iexact=grade).count() A = g("A") B = g("B") C = g("C") D = g("D") E = g("E") total = s.candidates or qs.count() pass_ac = A + B + C pass_pct = round((pass_ac / total * 100), 2) if total else 0 avg = round(float(s.avg_numeric or 0), 4) avg_grade = s.avg_grade or numeric_to_grade(avg) status = s.status or "-" rows.append({ "school_code": school.school_code, "name": school.name, "district": school.district.name, "total": total, "A": A, "B": B, "C": C, "D": D, "E": E, "pass_ac": pass_ac, "pass_pct": pass_pct, "avg": avg, "avg_grade": avg_grade, "status": status, "ownership": (school.ownership or "").upper(), }) return rows def get_district_school_stats(district_id, year): summaries = ( SchoolYearSummary.objects .filter( year=year, school__district_id=district_id ) .select_related("school", "school__district") ) rows = [] for s in summaries: school = s.school qs = ExamCandidate.objects.filter( year=year, school__school_code=school.school_code ) def g(grade): return qs.filter(avg_grade__iexact=grade).count() A = g("A") B = g("B") C = g("C") D = g("D") E = g("E") total = s.candidates or qs.count() pass_ac = A + B + C pass_pct = round((pass_ac / total * 100), 2) if total else 0 avg = round(float(s.avg_numeric or 0), 4) avg_grade = s.avg_grade or numeric_to_grade(avg) status = s.status or "-" rows.append({ "school_code": school.school_code, "name": school.name, "district": school.district.name, "total": total, "A": A, "B": B, "C": C, "D": D, "E": E, "pass_ac": pass_ac, "pass_pct": pass_pct, "avg": avg, "avg_grade": avg_grade, "status": status, "ownership": (school.ownership or "").upper(), }) return rows def build_district_best_worst_tables(district_id, year): rows = get_district_school_stats(district_id, year) # --- ALL SCHOOLS --- top10 = sorted(rows, key=lambda x: x["avg"], reverse=True)[:10] bottom10 = sorted(rows, key=lambda x: x["avg"])[:10] # --- LARGE SCHOOLS (>=30 candidates) --- large = [r for r in rows if r["total"] >= 30] top10_big = sorted(large, key=lambda x: x["avg"], reverse=True)[:10] bottom10_big = sorted(large, key=lambda x: x["avg"])[:10] # --- GOVERNMENT ONLY --- gov = [r for r in rows if r["ownership"] in ("GVT", "GOVERNMENT")] top10_gov = sorted(gov, key=lambda x: x["avg"], reverse=True)[:10] # --- Rank numbering --- def add_rank(items): for i, r in enumerate(items, start=1): r["rank"] = i return items return { "top10": add_rank(top10), "bottom10": add_rank(bottom10), "top10_big": add_rank(top10_big), "bottom10_big": add_rank(bottom10_big), "top10_gov": add_rank(top10_gov), } def build_district_top_bottom_context(district_id, year): district = District.objects.get(pk=district_id) tables = build_district_best_worst_tables(district_id, year) return { "district": district, "year": year, "top10": tables["top10"], "bottom10": tables["bottom10"], "top10_big": tables["top10_big"], "bottom10_big": tables["bottom10_big"], "top10_gov": tables["top10_gov"], } from django.db.models import Sum, Avg, Count, Q def build_district_subject_summary(district_id, year, subject_code): district = get_object_or_404(District, pk=district_id) # Schools in district school_codes = School.objects.filter( district=district ).values_list("school_code", flat=True) # Subject name map subject_map = { s.subject_code: s.subject_name_sw for s in ExamSubject.objects.all() } # ------------------------------- # SUBJECT FILTER # ------------------------------- perf_qs = SchoolSubjectPerformance.objects.filter( year=year, school__region_id=region_id ) marks_qs = StudentSubjectMark.objects.filter( year=year, school__district=district ) if subject_code != "ALL": perf_qs = perf_qs.filter(subject_code=subject_code) marks_qs = marks_qs.filter(subject_code=subject_code) # ------------------------------- # PERFORMANCE (TOTAL + AVERAGE) # ------------------------------- perf_summary = ( perf_qs .values("subject_code") .annotate( total_std=Sum("total_std"), avg_subj=Avg("subj_avg"), ) ) # ------------------------------- # GRADES (ONE QUERY!) # ------------------------------- grade_summary = ( marks_qs .values("subject_code") .annotate( 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")), ) ) grade_map = { g["subject_code"]: g for g in grade_summary } # ------------------------------- # BUILD ROWS (ONE PER SUBJECT) # ------------------------------- rows = [] for p in perf_summary: subj = p["subject_code"] g = grade_map.get(subj, {}) A = g.get("A", 0) B = g.get("B", 0) C = g.get("C", 0) D = g.get("D", 0) E = g.get("E", 0) total_std = p["total_std"] or 0 pass_ac = A + B + C pass_pct = round((pass_ac / total_std * 100), 2) if total_std else 0 rows.append({ "subject_code": subj, "subject_name_sw": subject_map.get(subj, "UNKNOWN"), "total_std": total_std, "A": A, "B": B, "C": C, "D": D, "E": E, "pass_ac": pass_ac, "pass_pct": pass_pct, "avg_subj": round(float(p["avg_subj"] or 0), 4), }) # Sort by subject code rows.sort(key=lambda x: x["subject_code"]) return { "district": district, "year": year, "subject": subject_code, "rows": rows, } from django.db.models import Avg def get_district_subject_school_ranking(district_id, year, subject_code): district = District.objects.get(pk=district_id) # --- SUBJECT NAME MAP (robust) --- subject_map = { str(s.subject_code): s.subject_name_sw for s in ExamSubject.objects.all() } subject_code = str(subject_code).strip() subject_name_sw = subject_map.get(subject_code, "UNKNOWN SUBJECT") # 🔑 ONLY CHANGE: schools limited to district schools = School.objects.filter(district_id=district_id) rows = [] for s in schools: perf = SchoolSubjectPerformance.objects.filter( school_code=s.school_code, year=year, subject_code=subject_code ).first() if not perf: continue total = perf.total_std or 0 avg = float(perf.subj_avg or 0) pass_ac = total if perf.subj_grade in ["A", "B", "C"] else 0 pass_pct = round((pass_ac / total * 100), 2) if total else 0 rows.append({ "school_code": s.school_code, "school": s.name, "district": district.name, # district is fixed "total": total, "pass_ac": pass_ac, "pass_pct": pass_pct, "avg": round(avg, 4), }) # SORT (same logic as region) rows.sort(key=lambda r: (r["avg"], r["pass_pct"]), reverse=True) # RANK for i, r in enumerate(rows, start=1): r["rank"] = i return { "district": district, "year": year, "subject_code": subject_code, "subject_name_sw": subject_name_sw, "rows": rows, } from django.db.models import Sum def build_district_trend(district_id, start_year, end_year, exam_type_id): district_id = int(district_id) start_year = int(start_year) end_year = int(end_year) exam_type_id = int(exam_type_id) years = list(range(start_year, end_year + 1)) rows = [] prev_gpa = None DIV_WEIGHT = {"I":1,"II":2,"III":3,"IV":4,"0":5} SUB_WEIGHT = {"A":1,"B":2,"C":3,"D":4,"F":5} for y in years: # ================= DIVISION COUNTS ================= div_qs = SchoolDivisionSummary.objects.filter( school__district_id=district_id, exam_type_id=exam_type_id, year=y ) def d(div): return div_qs.filter(division=div).aggregate(t=Sum("total"))["t"] or 0 d1 = d("I") d2 = d("II") d3 = d("III") d4 = d("IV") d0 = d("0") total = d1 + d2 + d3 + d4 + d0 pass_i_iii = d1 + d2 + d3 pass_i_iv = d1 + d2 + d3 + d4 pass_i_iii_pct = round(pass_i_iii / total * 100, 2) if total else 0 pass_i_iv_pct = round(pass_i_iv / total * 100, 2) if total else 0 # ================= DIVISION GPA ================= div_weighted_sum = ( d1*1 + d2*2 + d3*3 + d4*4 + d0*5 ) div_gpa = round(div_weighted_sum / total, 4) if total else 0 # ================= SUBJECT GPA ================= subj_qs = SchoolSubjectGradeSummary.objects.filter( school__district_id=district_id, exam_type_id=exam_type_id, year=y ) subj_weighted_sum = 0 subj_total_count = 0 for r in subj_qs: subj_weighted_sum += SUB_WEIGHT.get(r.grade, 5) * r.total subj_total_count += r.total subj_gpa = round( subj_weighted_sum / subj_total_count, 4 ) if subj_total_count else 0 # ================= FINAL GPA ================= final_gpa = round((div_gpa + subj_gpa) / 2, 4) # ================= TREND LOGIC ================= if prev_gpa is None: remark = "START" icon = "◎" elif final_gpa < prev_gpa: remark = "RISE" icon = "▲" elif final_gpa > prev_gpa: remark = "DROP" icon = "▼" else: remark = "SAME" icon = "■" prev_gpa = final_gpa rows.append({ "year": y, "d1": d1, "d2": d2, "d3": d3, "d4": d4, "d0": d0, "total": total, "pass_i_iii": pass_i_iii, "pass_i_iv": pass_i_iv, "pass_i_iii_pct": pass_i_iii_pct, "pass_i_iv_pct": pass_i_iv_pct, "div_gpa": div_gpa, "subj_gpa": subj_gpa, "gpa": final_gpa, "remark": remark, "icon": icon, }) return rows def build_ward_trend(ward_id): ward = get_object_or_404(Ward, pk=ward_id) qs = ( SchoolYearSummary.objects .filter(school__ward=ward) .values("year") .annotate( avg_score=Avg("avg_numeric"), total_candidates=Sum("candidates") ) .order_by("year") ) rows = [] for r in qs: avg = round(float(r["avg_score"] or 0), 2) rows.append({ "year": r["year"], "avg": avg, "grade": numeric_to_grade(avg), "total": r["total_candidates"] or 0, }) return { "ward": ward, "rows": rows, } def compute_school_trend(school_code, start_year, end_year, exam_type_id): start_year = int(start_year) end_year = int(end_year) exam_type_id = int(exam_type_id) years = list(range(start_year, end_year + 1)) rows = [] prev_gpa = None for y in years: # ---------------- DIVISION SUMMARY ---------------- div_qs = SchoolDivisionSummary.objects.filter( school__school_code=school_code, year=y, exam_type_id=exam_type_id ) def d(div): return div_qs.filter(division=div).aggregate( t=Sum("total") )["t"] or 0 d1 = d("I") d2 = d("II") d3 = d("III") d4 = d("IV") d0 = d("0") total = d1 + d2 + d3 + d4 + d0 pass_i_iii = d1 + d2 + d3 pass_i_iv = d1 + d2 + d3 + d4 # ---------------- GPA SUMMARY ---------------- sy = SchoolYearSummary.objects.filter( school__school_code=school_code, year=y, exam_type_id=exam_type_id ).first() gpa = float(sy.avg_gpa) if sy else 0 # ---------------- TREND LOGIC ---------------- if prev_gpa is None: remark = "START" icon = "◎" elif gpa < prev_gpa: remark = "RISE" icon = "▲" elif gpa > prev_gpa: remark = "DROP" icon = "▼" else: remark = "SAME" icon = "■" prev_gpa = gpa rows.append({ "year": y, "d1": d1, "d2": d2, "d3": d3, "d4": d4, "d0": d0, "total": total, "pass_i_iii": pass_i_iii, "pass_i_iv": pass_i_iv, "gpa": round(gpa, 4), "remark": remark, "icon": icon }) return rows def compute_region_trend(region_id, start_year, end_year, exam_type_id): region_id = int(region_id) start_year = int(start_year) end_year = int(end_year) exam_type_id = int(exam_type_id) years = list(range(start_year, end_year + 1)) rows = [] prev_gpa = None DIV_WEIGHT = {"I":1,"II":2,"III":3,"IV":4,"0":5} SUB_WEIGHT = {"A":1,"B":2,"C":3,"D":4,"F":5} for y in years: # -------- DIVISION COUNTS -------- div_qs = SchoolDivisionSummary.objects.filter( school__district__region_id=region_id, exam_type_id=exam_type_id, year=y ) def d(div): return div_qs.filter(division=div).aggregate(t=Sum("total"))["t"] or 0 d1 = d("I") d2 = d("II") d3 = d("III") d4 = d("IV") d0 = d("0") total = d1 + d2 + d3 + d4 + d0 pass_i_iii = d1 + d2 + d3 pass_i_iv = d1 + d2 + d3 + d4 pass_i_iii_pct = round(pass_i_iii / total * 100, 2) if total else 0 pass_i_iv_pct = round(pass_i_iv / total * 100, 2) if total else 0 # -------- DIVISION GPA -------- div_weighted_sum = ( d1*1 + d2*2 + d3*3 + d4*4 + d0*5 ) div_gpa = round(div_weighted_sum / total, 4) if total else 0 # -------- SUBJECT GPA -------- subj_qs = SchoolSubjectGradeSummary.objects.filter( school__district__region_id=region_id, exam_type_id=exam_type_id, year=y ) subj_weighted_sum = 0 subj_total_count = 0 for r in subj_qs: subj_weighted_sum += SUB_WEIGHT.get(r.grade,5) * r.total subj_total_count += r.total subj_gpa = round(subj_weighted_sum / subj_total_count, 4) if subj_total_count else 0 # -------- FINAL GPA -------- final_gpa = round((div_gpa + subj_gpa) / 2, 4) # -------- TREND LOGIC -------- if prev_gpa is None: remark = "START" icon = "◎" elif final_gpa < prev_gpa: remark = "RISE" icon = "▲" elif final_gpa > prev_gpa: remark = "DROP" icon = "▼" else: remark = "SAME" icon = "■" prev_gpa = final_gpa rows.append({ "year": y, "d1": d1, "d2": d2, "d3": d3, "d4": d4, "d0": d0, "total": total, "pass_i_iii": pass_i_iii, "pass_i_iv": pass_i_iv, "div_gpa": div_gpa, "subj_gpa": subj_gpa, "gpa": final_gpa, "remark": remark, "pass_i_iii_pct": pass_i_iii_pct, "pass_i_iv_pct": pass_i_iv_pct, "icon": icon }) return rows def build_school_trend(school_code): school = get_object_or_404(School, school_code=school_code) qs = ( SchoolYearSummary.objects .filter(school=school) .values("year") .annotate( avg_score=Avg("avg_numeric"), total_candidates=Sum("candidates") ) .order_by("year") ) rows = [] for r in qs: avg = round(float(r["avg_score"] or 0), 2) rows.append({ "year": r["year"], "avg": avg, "grade": numeric_to_grade(avg), "total": r["total_candidates"] or 0, }) return { "school": school, "rows": rows, } def build_ward_summary_context(ward_id, year): year = int(year) ward = get_object_or_404(Ward, pk=ward_id) # -------------------------------------------------- # BASE QUERY (WARD SCOPE) # -------------------------------------------------- cand_qs = ExamCandidate.objects.filter( year=year, school__ward_id=ward.id ) # -------------------------------------------------- # BASIC COUNTS # -------------------------------------------------- male_total = cand_qs.filter(sex__iexact="M").count() female_total = cand_qs.filter(sex__iexact="F").count() reg_total = male_total + female_total sat_qs = cand_qs.filter(status__in=["PASS", "FAIL"]) sat_total = sat_qs.count() sat_m = sat_qs.filter(sex__iexact="M").count() sat_f = sat_qs.filter(sex__iexact="F").count() abs_total = reg_total - sat_total abs_m = male_total - sat_m abs_f = female_total - sat_f pass_total = cand_qs.filter(status__iexact="PASS").count() fail_total = cand_qs.filter(status__iexact="FAIL").count() # -------------------------------------------------- # WARD AVERAGE (FROM SCHOOL YEAR SUMMARY) # -------------------------------------------------- avg_row = SchoolYearSummary.objects.filter( school__ward_id=ward.id, year=year ).aggregate(avg=Avg("avg_numeric")) ward_avg = float(avg_row["avg"] or 0) def grade_from_numeric(v): if v >= 241: return "A" if v >= 181: return "B" if v >= 121: return "C" if v >= 61: return "D" return "E" ward_grade = grade_from_numeric(ward_avg) status_map = { "A": "Excellent", "B": "Very Good", "C": "Good", "D": "Average", "E": "Below Average", } ward_status = status_map.get(ward_grade, "-") totals = { "reg": reg_total, "reg_m": male_total, "reg_f": female_total, "sat": sat_total, "sat_m": sat_m, "sat_f": sat_f, "abs": abs_total, "abs_m": abs_m, "abs_f": abs_f, "pass": pass_total, "fail": fail_total, "avg": round(ward_avg, 2), "grade": ward_grade, "status": ward_status, "school_count": SchoolYearSummary.objects.filter( school__ward_id=ward.id, year=year ).values("school").distinct().count(), } # -------------------------------------------------- # GRADE DISTRIBUTION # -------------------------------------------------- grade_totals = { "af": sat_qs.filter(avg_grade__iexact="A", sex__iexact="F").count(), "bf": sat_qs.filter(avg_grade__iexact="B", sex__iexact="F").count(), "cf": sat_qs.filter(avg_grade__iexact="C", sex__iexact="F").count(), "df": sat_qs.filter(avg_grade__iexact="D", sex__iexact="F").count(), "ef": sat_qs.filter(avg_grade__iexact="E", sex__iexact="F").count(), "am": sat_qs.filter(avg_grade__iexact="A", sex__iexact="M").count(), "bm": sat_qs.filter(avg_grade__iexact="B", sex__iexact="M").count(), "cm": sat_qs.filter(avg_grade__iexact="C", sex__iexact="M").count(), "dm": sat_qs.filter(avg_grade__iexact="D", sex__iexact="M").count(), "em": sat_qs.filter(avg_grade__iexact="E", sex__iexact="M").count(), } grade_totals["a"] = grade_totals["af"] + grade_totals["am"] grade_totals["b"] = grade_totals["bf"] + grade_totals["bm"] grade_totals["c"] = grade_totals["cf"] + grade_totals["cm"] grade_totals["d"] = grade_totals["df"] + grade_totals["dm"] grade_totals["e"] = grade_totals["ef"] + grade_totals["em"] a_c_f = grade_totals["af"] + grade_totals["bf"] + grade_totals["cf"] a_c_m = grade_totals["am"] + grade_totals["bm"] + grade_totals["cm"] a_c_total = a_c_f + a_c_m # -------------------------------------------------- # OWNERSHIP SUMMARY (GOVT / NON-GOVT) # -------------------------------------------------- def build_owner(owner_type): qs = cand_qs.filter(school__ownership=owner_type) satq = qs.filter(status__in=["PASS", "FAIL"]) return { "owner": "SERIKALI" if owner_type == "GVT" else "SIYO SERIKALI", "total_school": qs.values("school_id").distinct().count(), "sat": satq.count(), "sat_f": satq.filter(sex__iexact="F").count(), "sat_m": satq.filter(sex__iexact="M").count(), "af": satq.filter(avg_grade="A", sex="F").count(), "bf": satq.filter(avg_grade="B", sex="F").count(), "cf": satq.filter(avg_grade="C", sex="F").count(), "df": satq.filter(avg_grade="D", sex="F").count(), "ef": satq.filter(avg_grade="E", sex="F").count(), "am": satq.filter(avg_grade="A", sex="M").count(), "bm": satq.filter(avg_grade="B", sex="M").count(), "cm": satq.filter(avg_grade="C", sex="M").count(), "dm": satq.filter(avg_grade="D", sex="M").count(), "em": satq.filter(avg_grade="E", sex="M").count(), } ownership = [ build_owner("GVT"), build_owner("NON-GVT"), ] # -------------------------------------------------- # SCHOOL SUMMARY (WARD LEVEL) # -------------------------------------------------- school_stats = ( cand_qs.values("school__name") .annotate( total=Count("id"), pass_count=Count("id", filter=Q(status="PASS")), fail_count=Count("id", filter=Q(status="FAIL")), male=Count("id", filter=Q(sex="M")), female=Count("id", filter=Q(sex="F")), ) .order_by("school__name") ) schools = [] totals_acc = {"total": 0, "pass": 0, "fail": 0, "male": 0, "female": 0} for s in school_stats: pass_pct = round((s["pass_count"] / s["total"] * 100), 1) if s["total"] else 0 schools.append({ "school_name": s["school__name"], "total": s["total"], "pass": s["pass_count"], "fail": s["fail_count"], "pass_pct": pass_pct, "male": s["male"], "female": s["female"], }) totals_acc["total"] += s["total"] totals_acc["pass"] += s["pass_count"] totals_acc["fail"] += s["fail_count"] totals_acc["male"] += s["male"] totals_acc["female"] += s["female"] totals_acc["pass_pct"] = round( totals_acc["pass"] / totals_acc["total"] * 100, 1 ) if totals_acc["total"] else 0 # -------------------------------------------------- # FINAL CONTEXT (MATCHES REGION STRUCTURE) # -------------------------------------------------- return { "ward": ward, "year": year, "totals": totals, "grades": grade_totals, "a_c_f": a_c_f, "a_c_m": a_c_m, "a_c_total": a_c_total, "ownership": ownership, "schools": schools, "schools_totals": totals_acc, } from django.db.models import Avg, Count, Q from .models import ExamCandidate, ExamSubject, SchoolYearSummary, Ward def build_ward_subject_summary(ward_id, year, subject_code=None): year = int(year) ward = Ward.objects.get(pk=ward_id) base_qs = ExamCandidate.objects.filter( year=year, school__ward_id=ward.id ) # ---------------------------------------- # SUBJECT LOOP # ---------------------------------------- subjects = ExamSubject.objects.all().order_by("subject_code") rows = [] totals = { "total": 0, "pass_ac": 0, "fail": 0, "avg": 0, } for subj in subjects: if subject_code and subject_code != "ALL" and subj.subject_code != subject_code: continue sqs = base_qs.filter(subject_code=subj.subject_code) total = sqs.count() pass_ac = sqs.filter(grade__in=["A", "B", "C"]).count() fail = sqs.filter(grade__in=["D", "E", "F"]).count() avg_row = sqs.aggregate(avg=Avg("marks")) avg = round(avg_row["avg"] or 0, 2) if total == 0: continue rows.append({ "subject_code": subj.subject_code, "subject_name": subj.subject_name_sw, "total": total, "pass_ac": pass_ac, "pass_pct": round((pass_ac / total * 100), 1) if total else 0, "fail": fail, "avg": avg, }) totals["total"] += total totals["pass_ac"] += pass_ac totals["fail"] += fail totals["avg"] = ( round(sum(r["avg"] for r in rows) / len(rows), 2) if rows else 0 ) return { "ward": ward, "year": year, "rows": rows, "totals": totals, } from django.db.models import Avg, Count, Q from .models import ( Ward, School, ExamCandidate, ExamSubject, SchoolYearSummary ) def build_ward_subject_school_ranking(ward_id, year, subject_code): year = int(year) ward = Ward.objects.get(pk=ward_id) # Base candidates (WARD ONLY) cand_qs = ExamCandidate.objects.filter( year=year, school__ward_id=ward.id, subject_code=subject_code ) schools = ( School.objects .filter(ward_id=ward.id) .select_related("district") .order_by("name") ) rows = [] for school in schools: sqs = cand_qs.filter(school_id=school.school_code) total = sqs.count() if total == 0: continue pass_ac = sqs.filter(grade__in=["A", "B", "C"]).count() fail = sqs.filter(grade__in=["D", "E", "F"]).count() avg_row = sqs.aggregate(avg=Avg("marks")) avg = round(avg_row["avg"] or 0, 2) pass_pct = round((pass_ac / total) * 100, 1) if total else 0 rows.append({ "school_code": school.school_code, "school": school.name, "district": school.district.name, "total": total, "pass_ac": pass_ac, "pass_pct": pass_pct, "fail": fail, "avg": avg, }) # RANKING rows.sort( key=lambda x: (x["pass_pct"], x["avg"], x["pass_ac"]), reverse=True ) for i, r in enumerate(rows, start=1): r["rank"] = i return { "ward": ward, "year": year, "subject_code": subject_code, "rows": rows, } from django.db.models import Sum, Q from exams.models import ( School, ExamType, ExamSubject, SchoolDivisionSummary, SchoolSubjectGradeSummary, SchoolSubjectPerformance, SchoolYearSummary, ) def build_school_summary_context(school_code, year, exam_type_id): year = int(year) exam_type_id = int(exam_type_id) # -------------------------------------------------- # SCHOOL # -------------------------------------------------- school = School.objects.select_related( "ward", "district", "district__region" ).get(school_code=school_code) # -------------------------------------------------- # BASE QUERYSET (COMPRESSED DATA) # -------------------------------------------------- base_qs = SchoolDivisionSummary.objects.filter( year=year, exam_type_id=exam_type_id, school=school ) PASS_DIVS = ["I","II","III","IV"] FAIL_DIV = "0" # -------------------------------------------------- # REGISTERED (TOTAL BY SEX) # -------------------------------------------------- reg_f = base_qs.filter(sex="F").aggregate(t=Sum("total"))["t"] or 0 reg_m = base_qs.filter(sex="M").aggregate(t=Sum("total"))["t"] or 0 reg = reg_f + reg_m div_qs = SchoolDivisionSummary.objects.filter( year=year, exam_type_id=exam_type_id, school=school ) year_qs = SchoolYearSummary.objects.get( school=school, year=year, exam_type_id=exam_type_id ) SAT_DIVS = ["I","II","III","IV","0"] ABS_DIV = "X" sat_f = div_qs.filter(sex="F", division__in=SAT_DIVS).aggregate(t=Sum("total"))["t"] or 0 sat_m = div_qs.filter(sex="M", division__in=SAT_DIVS).aggregate(t=Sum("total"))["t"] or 0 sat = sat_f + sat_m abs_f = div_qs.filter(sex="F", division=ABS_DIV).aggregate(t=Sum("total"))["t"] or 0 abs_m = div_qs.filter(sex="M", division=ABS_DIV).aggregate(t=Sum("total"))["t"] or 0 abs_total = abs_f + abs_m reg_f = sat_f + abs_f reg_m = sat_m + abs_m reg = reg_f + reg_m avg_numeric = round(year_qs.avg_numeric,4) avg_gpa = round(year_qs.avg_gpa,4) avg_grade = year_qs.avg_grade or "-" status = year_qs.status or "-" # -------------------------------------------------- # GRADE COUNTS (DIVISION LEVEL) # -------------------------------------------------- def div_count(div, sex=None): q = Q(division=div) if sex: q &= Q(sex=sex) return base_qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 grades = { "af": div_count("I","F"), "am": div_count("I","M"), "bf": div_count("II","F"), "bm": div_count("II","M"), "cf": div_count("III","F"), "cm": div_count("III","M"), "df": div_count("IV","F"), "dm": div_count("IV","M"), "ef": div_count("0","F"), "em": div_count("0","M"), "a": div_count("I"), "b": div_count("II"), "c": div_count("III"), "d": div_count("IV"), "e": div_count("0"), } grades["a"] = grades["af"] + grades["am"] grades["b"] = grades["bf"] + grades["bm"] grades["c"] = grades["cf"] + grades["cm"] grades["d"] = grades["df"] + grades["dm"] grades["e"] = grades["ef"] + grades["em"] # -------------------------------------------------- # PASS A–C (Division I–III) # -------------------------------------------------- a_c_f = grades["af"] + grades["bf"] + grades["cf"] a_c_m = grades["am"] + grades["bm"] + grades["cm"] a_c_total = a_c_f + a_c_m # -------------------------------------------------- # PASS A–D (Division I–IV) # -------------------------------------------------- a_d_f = grades["af"] + grades["bf"] + grades["cf"] + grades["df"] a_d_m = grades["am"] + grades["bm"] + grades["cm"] + grades["dm"] a_d_total = a_d_f + a_d_m # -------------------------------------------------- # OWNERSHIP BLOCK (school already single) # -------------------------------------------------- ownership_rows = [{ "owner": school.ownership, "total_school": 1, "sat": sat, "sat_f": sat_f, "sat_m": sat_m, "af": grades["af"], "am": grades["am"], "bf": grades["bf"], "bm": grades["bm"], "cf": grades["cf"], "cm": grades["cm"], "df": grades["df"], "dm": grades["dm"], "ef": grades["ef"], "em": grades["em"], }] # -------------------------------------------------- # SUBJECT PERFORMANCE # -------------------------------------------------- subj_grade_qs = SchoolSubjectGradeSummary.objects.filter( year=year, exam_type_id=exam_type_id, school=school ) subj_perf_qs = SchoolSubjectPerformance.objects.filter( year=year, exam_type_id=exam_type_id, school_code=school.school_code ) subject_map = { s.subject_code: s.subject_name_eng for s in ExamSubject.objects.all() } PASS_GRADES = ["A","B","C","D"] subjects = subj_grade_qs.values_list("subject_code", flat=True).distinct() subject_rows = [] for code in subjects: def g(grade, sex=None): q = Q(subject_code=code, grade=grade) if sex: q &= Q(sex=sex) return subj_grade_qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 # Grade totals af, am = g("A","F"), g("A","M") bf, bm = g("B","F"), g("B","M") cf, cm = g("C","F"), g("C","M") df, dm = g("D","F"), g("D","M") ff, fm = g("F","F"), g("F","M") at = af + am bt = bf + bm ct = cf + cm dt = df + dm ft = ff + fm sat = at + bt + ct + dt + ft pass_no = at + bt + ct + dt pass_pct = round(pass_no / sat * 100,1) if sat else 0 perf = subj_perf_qs.filter(subject_code=code).first() subject_rows.append({ "code": code, "subject": subject_map.get(code, code), # 🔥 NAME now appears "sat": sat, "af": af, "am": am, "at": at, "bf": bf, "bm": bm, "bt": bt, "cf": cf, "cm": cm, "ct": ct, "df": df, "dm": dm, "dt": dt, "ff": ff, "fm": fm, "ft": ft, "pass_no": pass_no, "pass_pct": pass_pct, "gpa": float(perf.subj_gpa) if perf else 0, "grade": perf.subj_grade if perf else "-", "status": perf.subj_status if perf else "-", }) # -------------------------------------------------- # FINAL CONTEXT # -------------------------------------------------- exam_types = ExamType.objects.filter(is_active=True).only("id", "name") exam = ExamType.objects.get(id=exam_type_id) context = { "school": school, "year": year, "exam_type_id": exam_type_id, # ✅ ADD THIS "exam_types": exam_types, # ✅ THIS WAS MISSING # ✅ single exam object "exam": exam, "totals": { "reg_f": reg_f, "reg_m": reg_m, "reg": reg, "sat_f": sat_f, "sat_m": sat_m, "sat": sat, "abs_f": abs_f, "abs_m": abs_m, "abs": abs_total, "avg": avg_gpa, "grade": avg_grade, "status": status, }, "grades": grades, "a_c_f": a_c_f, "a_c_m": a_c_m, "a_c_total": a_c_total, "a_d_f": a_d_f, "a_d_m": a_d_m, "a_d_total": a_d_total, "ownership": ownership_rows, "subjects": subject_rows } return context def build_school_subject_performance(school_code, year): qs = ExamCandidate.objects.filter( year=year, school_id=school_code ) rows = [] for s in ExamSubject.objects.all(): sq = qs.filter(subject_code=s.subject_code) if not sq.exists(): continue total = sq.count() pass_ac = sq.filter(avg_grade__in=["A","B","C"]).count() avg = sq.aggregate(a=Avg("marks"))["a"] or 0 rows.append({ "subject": s.subject_name_sw, "total": total, "pass_pct": round(pass_ac/total*100,2), "avg": round(avg,2) }) return rows from django.db.models import Avg from exams.models import ( School, ExamSubject, SchoolSubjectPerformance, StudentSubjectMark, ) def build_school_subject_summary_context(school_code, year, subject="ALL"): school = School.objects.select_related( "ward", "district", "district__region" ).get(school_code=school_code) subject_map = { str(s.subject_code): s.subject_name_sw for s in ExamSubject.objects.all() } if subject == "ALL": subject_codes = ( StudentSubjectMark.objects .filter(year=year, school__school_code=school_code) .values_list("subject_code", flat=True) .distinct() .order_by("subject_code") ) subject_codes = [str(s) for s in subject_codes] subject_name = "ALL SUBJECTS" else: subject_codes = [str(subject)] subject_name = subject_map.get(str(subject), "") rows = [] totals = {"A":0,"B":0,"C":0,"D":0,"E":0,"total_std":0,"pass_ac":0} for subj in subject_codes: marks_q = StudentSubjectMark.objects.filter( year=year, subject_code=subj, school__school_code=school_code ) a = marks_q.filter(grade="A").count() b = marks_q.filter(grade="B").count() c = marks_q.filter(grade="C").count() d = marks_q.filter(grade="D").count() e = marks_q.filter(grade="E").count() total_std = a+b+c+d+e pass_ac = a+b+c pass_pct = round((pass_ac/total_std)*100,2) if total_std else 0 avg = ( SchoolSubjectPerformance.objects .filter(year=year, subject_code=subj, school_code=school_code) .aggregate(v=Avg("subj_avg"))["v"] ) avg = round(float(avg),2) if avg else 0 rows.append({ "subject": subject_map.get(subj, "UNKNOWN"), "A": a,"B": b,"C": c,"D": d,"E": e, "total_std": total_std, "pass_ac": pass_ac, "pass_pct": pass_pct, "avg_subj": avg, }) totals["A"]+=a; totals["B"]+=b; totals["C"]+=c totals["D"]+=d; totals["E"]+=e totals["total_std"]+=total_std totals["pass_ac"]+=pass_ac totals["pass_pct"] = round( (totals["pass_ac"]/totals["total_std"])*100,2 ) if totals["total_std"] else 0 return { "school": school, "year": year, "rows": rows, "totals": totals, "subject_name": subject_name, } from django.db.models import Avg from exams.models import ( School, StudentSubjectMark, SchoolYearSummary, ) def build_school_trend_context(school_code, start, end): """ Builds school performance trend data between start and end years (inclusive) Returns context with: - school - start - end - rows: [ { year, total_std, pass_ac, fail_de, pass_pct, avg } ] """ start = int(start) end = int(end) school = School.objects.select_related( "ward", "district", "district__region" ).get(school_code=school_code) rows = [] for year in range(start, end + 1): # -------------------------------------- # MARKS (SOURCE OF TRUTH) # -------------------------------------- marks_q = StudentSubjectMark.objects.filter( year=year, school__school_code=school_code ) if not marks_q.exists(): rows.append({ "year": year, "total_std": 0, "pass_ac": 0, "fail_de": 0, "pass_pct": 0, "avg": 0, }) continue a = marks_q.filter(grade="A").count() b = marks_q.filter(grade="B").count() c = marks_q.filter(grade="C").count() d = marks_q.filter(grade="D").count() e = marks_q.filter(grade="E").count() total_std = a + b + c + d + e pass_ac = a + b + c fail_de = d + e pass_pct = round((pass_ac / total_std) * 100, 2) if total_std else 0 # -------------------------------------- # AVERAGE SCORE (SAFE) # -------------------------------------- avg = ( SchoolYearSummary.objects .filter( year=year, school_id=school.school_code ) .aggregate(v=Avg("avg_numeric"))["v"] ) avg = round(float(avg), 2) if avg is not None else 0 rows.append({ "year": year, "total_std": total_std, "pass_ac": pass_ac, "fail_de": fail_de, "pass_pct": pass_pct, "avg": avg, }) return { "school": school, "start": start, "end": end, "rows": rows, } def compute_subject_school_rank(region_id, exam_type_id, year, subject_code): rows = [] schools = School.objects.filter(region_id=region_id) SUB_WEIGHT = {"A":1,"B":2,"C":3,"D":4,"F":5} for s in schools: qs = SchoolSubjectGradeSummary.objects.filter( year=int(year), exam_type_id=int(exam_type_id), subject_code=subject_code, # ✅ ONLY CODE school=s ) if not qs.exists(): continue def g(grade, sex=None): q = Q(grade=grade) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 af, am = g("A","F"), g("A","M") bf, bm = g("B","F"), g("B","M") cf, cm = g("C","F"), g("C","M") df, dm = g("D","F"), g("D","M") ff, fm = g("F","F"), g("F","M") at, bt, ct, dt, ft = af+am, bf+bm, cf+cm, df+dm, ff+fm sat = at+bt+ct+dt+ft pass_no = at+bt+ct+dt pass_pct = round(pass_no/sat*100,2) if sat else 0 weighted_sum = at*1 + bt*2 + ct*3 + dt*4 + ft*5 gpa = round(weighted_sum/sat,4) if sat else 0 grade, status = gpa_to_grade_status(gpa) rows.append({ "code": s.school_code, "name": s.name_short, "district": s.district.name, "a": at, "b": bt, "c": ct, "d": dt, "f": ft, "pass": pass_no, "pass_pct": pass_pct, "gpa": gpa, "grade": grade, "status": status, }) rows.sort(key=lambda x: x["gpa"] if x["gpa"]>0 else 99) for i,r in enumerate(rows,1): r["rank"] = i return rows def compute_subject_school_rank_ext(region_id, exam_type_id, year, subject_code): rows = [] schools = School.objects.filter(region_id=region_id) SUB_WEIGHT = {"A":1,"B":2,"C":3,"D":4,"F":5} for s in schools: qs = SchoolSubjectGradeSummary.objects.filter( year=int(year), exam_type_id=int(exam_type_id), subject_code=subject_code, # ✅ ONLY CODE school=s ) if not qs.exists(): continue def g(grade, sex=None): q = Q(grade=grade) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 af, am = g("A","F"), g("A","M") bf, bm = g("B","F"), g("B","M") cf, cm = g("C","F"), g("C","M") df, dm = g("D","F"), g("D","M") ff, fm = g("F","F"), g("F","M") at, bt, ct, dt, ft = af+am, bf+bm, cf+cm, df+dm, ff+fm xf, xm = g("X","F"), g("X","M") xt = xf + xm # SAT = A+B+C+D+F sat_f = af + bf + cf + df + ff sat_m = am + bm + cm + dm + fm sat_t = sat_f + sat_m # REGISTERED = SAT + X reg_f = sat_f + xf reg_m = sat_m + xm reg_t = reg_f + reg_m sat = at+bt+ct+dt+ft pass_no = at+bt+ct+dt pass_pct = round(pass_no/sat*100,2) if sat else 0 # ----- PASS A–D ----- pass_f = af + bf + cf + df pass_m = am + bm + cm + dm pass_t = pass_f + pass_m pass_pct = round(pass_t / sat * 100, 2) if sat else 0 weighted_sum = at*1 + bt*2 + ct*3 + dt*4 + ft*5 gpa = round(weighted_sum/sat,4) if sat else 0 grade, status = gpa_to_grade_status(gpa) exam = ExamType.objects.get(id=exam_type_id) rows.append({ "code": s.school_code, "name": s.name_short, "code": s.school_code, "region": s.district.region.name, "district": s.district.name, "ownership": s.ownership, "exam": exam.code, "reg_f": reg_f, "reg_m": reg_m, "reg_t": reg_t, "sat_f": sat_f, "sat_m": sat_m, "sat_t": sat_t, "af":af,"am":am,"at":at,"a_pct":round(at/sat*100,2) if sat else 0, "bf":bf,"bm":bm,"bt":bt,"b_pct":round(bt/sat*100,2) if sat else 0, "cf":cf,"cm":cm,"ct":ct,"c_pct":round(ct/sat*100,2) if sat else 0, "df":df,"dm":dm,"dt":dt,"d_pct":round(dt/sat*100,2) if sat else 0, "ff":ff,"fm":fm,"ft":ft,"f_pct":round(ft/sat*100,2) if sat else 0, "pass_f":pass_f, "pass_m":pass_m, "pass_t":pass_t, "pass_pct":pass_pct, "gpa":gpa, "grade":grade, "status":status }) rows.sort(key=lambda x: x["gpa"] if x["gpa"]>0 else 99) for i,r in enumerate(rows,1): r["rank"] = i return rows ########################################################### ########################################################### ########################################################### ########################################################### ########################################################### ########################################################### #################DISTRICTS################################# ########################################################### ########################################################### ########################################################### ########################################################### ########################################################### ########################################################### ########################################################### ########################################################### ########################################################### ########################################################### ########################################################### def build_district_summary_context(district_id, year, exam_type_id): years = SchoolYearSummary.objects.values_list( "year", flat=True ).distinct().order_by("-year") exam_types = ExamType.objects.all() district_id = int(district_id) year = int(year) exam_type_id = int(exam_type_id) district = District.objects.get(id=district_id) region = district.region # -------------------------------------------------- # Schools in district (for subject performance tables) # -------------------------------------------------- school_codes = School.objects.filter( district_id=district_id ).values_list("school_code", flat=True) # -------------------------------------------------- # DIVISION SUMMARY (district scope) # -------------------------------------------------- base_qs = SchoolDivisionSummary.objects.filter( year=year, exam_type_id=exam_type_id, school__district_id=district_id ) SAT_DIVS = ["I", "II", "III", "IV", "0"] ABS_DIV = "X" reg_f = base_qs.filter(sex="F").aggregate(t=Sum("total"))["t"] or 0 reg_m = base_qs.filter(sex="M").aggregate(t=Sum("total"))["t"] or 0 reg = reg_f + reg_m sat_f = base_qs.filter(sex="F", division__in=SAT_DIVS).aggregate(t=Sum("total"))["t"] or 0 sat_m = base_qs.filter(sex="M", division__in=SAT_DIVS).aggregate(t=Sum("total"))["t"] or 0 sat_a = sat_f + sat_m abs_f = base_qs.filter(sex="F", division=ABS_DIV).aggregate(t=Sum("total"))["t"] or 0 abs_m = base_qs.filter(sex="M", division=ABS_DIV).aggregate(t=Sum("total"))["t"] or 0 abs_total = abs_f + abs_m from .gpa_calculation import ( district_division_gpa, district_subject_gpa_value, ) # -------------------------------------------------- # DISTRICT GPA (OFFICIAL – SOURCE OF TRUTH) # -------------------------------------------------- # Division GPA (I–IV, 0 only; ABSENT excluded) district_div_gpa = district_division_gpa( year=year, exam_type_id=exam_type_id, district_id=district_id ) # Subject GPA (GLOBAL weighted across ALL subjects) district_subj_gpa = district_subject_gpa_value( year=year, exam_type_id=exam_type_id, district_id=district_id ) # Final District GPA (NECTA official) district_final_gpa = round( (district_div_gpa + district_subj_gpa) / 2, 4 ) final_grade, final_status = gpa_to_grade_status(district_final_gpa) # -------------------------------------------------- # SUBJECT GPA (district) # -------------------------------------------------- subj_grade_qs = SchoolSubjectGradeSummary.objects.filter( year=year, exam_type_id=exam_type_id, school__district_id=district_id ) # -------------------------------------------------- # DIVISION COUNTS (for summary block) # -------------------------------------------------- def div_count(div, sex=None): q = Q(division=div) if sex: q &= Q(sex=sex) return base_qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 grades = { "af": div_count("I", "F"), "am": div_count("I", "M"), "bf": div_count("II", "F"), "bm": div_count("II", "M"), "cf": div_count("III", "F"), "cm": div_count("III", "M"), "df": div_count("IV", "F"), "dm": div_count("IV", "M"), "ef": div_count("0", "F"), "em": div_count("0", "M"), } grades["a"] = grades["af"] + grades["am"] grades["b"] = grades["bf"] + grades["bm"] grades["c"] = grades["cf"] + grades["cm"] grades["d"] = grades["df"] + grades["dm"] grades["e"] = grades["ef"] + grades["em"] a_c_f = grades["af"] + grades["bf"] + grades["cf"] a_c_m = grades["am"] + grades["bm"] + grades["cm"] a_c_total = a_c_f + a_c_m a_d_f = a_c_f + grades["df"] a_d_m = a_c_m + grades["dm"] a_d_total = a_d_f + a_d_m # ================================================== # WARD PERFORMANCE (FULL DIVISION BREAKDOWN) # ================================================== ward_rows = [] wards = Ward.objects.filter(district_id=district_id) for w in wards: qs = SchoolDivisionSummary.objects.filter( year=year, exam_type_id=exam_type_id, school__ward=w ) def g(div, sex=None): q = Q(division=div) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 # ----------------------------- # DIVISION COUNTS # ----------------------------- af, am = g("I","F"), g("I","M") bf, bm = g("II","F"), g("II","M") cf, cm = g("III","F"), g("III","M") df, dm = g("IV","F"), g("IV","M") ff, fm = g("0","F"), g("0","M") at, bt = af + am, bf + bm ct, dt = cf + cm, df + dm ft = ff + fm sat_t = at + bt + ct + dt + ft pass_no = at + bt + ct + dt pass_pct = round(pass_no / sat_t * 100, 1) if sat_t else 0 # ----------------------------- # WARD GPA (AGGREGATED) # ----------------------------- from .gpa_calculation import ( ward_division_gpa, ward_subject_gpa_value, ) # ----------------------------- # WARD GPA (OFFICIAL – CLEAN) # ----------------------------- ward_div_gpa = ward_division_gpa( year=year, exam_type_id=exam_type_id, ward_id=w.id ) ward_subj_gpa = ward_subject_gpa_value( year=year, exam_type_id=exam_type_id, ward_id=w.id ) ward_gpa = round((ward_div_gpa + ward_subj_gpa) / 2, 4) grade, status = gpa_to_grade_status(ward_gpa) ward_rows.append({ "name": w.name, "sat": sat_t, "af": af, "am": am, "at": at, "bf": bf, "bm": bm, "bt": bt, "cf": cf, "cm": cm, "ct": ct, "df": df, "dm": dm, "dt": dt, "ff": ff, "fm": fm, "ft": ft, "pass_no": pass_no, "pass_pct": pass_pct, "gpa": ward_gpa, "grade": grade, "status": status, }) # ----------------------------- # SORT & RANK (BEST FIRST) # ----------------------------- ward_rows.sort(key=lambda x: x["gpa"]) for i, r in enumerate(ward_rows, start=1): r["no"] = i # ================================================== # BEST 10 SCHOOLS (DISTRICT) – GPA FROM SchoolYearSummary # ================================================== best_schools = [] schools = School.objects.filter(district_id=district_id) for s in schools: # ----------------------------- # SCHOOL YEAR SUMMARY (SOURCE OF TRUTH) # ----------------------------- sy = SchoolYearSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id ).first() if not sy or not sy.avg_gpa: continue # skip schools without computed summary # ----------------------------- # DIVISION COUNTS # ----------------------------- qs = SchoolDivisionSummary.objects.filter( school=s, year=year, exam_type_id=exam_type_id # ✅ VERY IMPORTANT ) # qs = SchoolDivisionSummary.objects.filter( # year=year, # exam_type_id=exam_type_id, # school=s # ) def g(div, sex=None): q = Q(division=div) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 af, am = g("I","F"), g("I","M") bf, bm = g("II","F"), g("II","M") cf, cm = g("III","F"), g("III","M") df, dm = g("IV","F"), g("IV","M") ff, fm = g("0","F"), g("0","M") at, bt = af + am, bf + bm ct, dt = cf + cm, df + dm ft = ff + fm sat_bst = at + bt + ct + dt + ft pass_no = at + bt + ct + dt pass_pct = round(pass_no / sat_bst * 100, 1) if sat_bst else 0 # ----------------------------- # FINAL DATA (FROM SchoolYearSummary) # ----------------------------- best_schools.append({ "code": s.school_code, "name": s.name_short or s.name, "ownership": s.ownership, "district": s.district.name, "sat": sat_bst, "af": af, "am": am, "at": at, "bf": bf, "bm": bm, "bt": bt, "cf": cf, "cm": cm, "ct": ct, "df": df, "dm": dm, "dt": dt, "ff": ff, "fm": fm, "ft": ft, "pass_no": pass_no, "pass_pct": pass_pct, # ✅ OFFICIAL VALUES "gpa": round(float(sy.avg_gpa), 4), "grade": sy.avg_grade, "status": sy.status, }) # ----------------------------- # SORT & LIMIT # ----------------------------- best_schools.sort(key=lambda x: x["gpa"]) best_schools = best_schools[:10] for i, r in enumerate(best_schools, start=1): r["no"] = i # -------------------------------------------------- # SUBJECT PERFORMANCE (DISTRICT) # -------------------------------------------------- from .gpa_calculation import district_subject_gpa # -------------------------------------------------- # SUBJECT GPA (DISTRICT – SOURCE OF TRUTH) # -------------------------------------------------- # Normalize subject_code to 3 digits (e.g. 11 → 011) subj_gpa_map = { str(r["subject_code"]).zfill(3): r for r in district_subject_gpa( year=year, exam_type_id=exam_type_id, district_id=district_id ) } # -------------------------------------------------- # SUBJECT NAME MAP # -------------------------------------------------- subject_map = { str(s.subject_code).zfill(3): s.subject_name_eng for s in ExamSubject.objects.all() } # -------------------------------------------------- # SUBJECT LIST (FROM ACTUAL DATA) # -------------------------------------------------- subjects = [ str(s).zfill(3) for s in subj_grade_qs .values_list("subject_code", flat=True) .distinct() ] # -------------------------------------------------- # BUILD SUBJECT ROWS # -------------------------------------------------- subject_rows = [] for code in subjects: # ---- helper for grade counts ---- def g(grade, sex=None): q = Q(subject_code=code.lstrip("0"), grade=grade) if sex: q &= Q(sex=sex) return subj_grade_qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 # ---- grade counts ---- af, am = g("A", "F"), g("A", "M") bf, bm = g("B", "F"), g("B", "M") cf, cm = g("C", "F"), g("C", "M") df, dm = g("D", "F"), g("D", "M") ff, fm = g("F", "F"), g("F", "M") at, bt = af + am, bf + bm ct, dt = cf + cm, df + dm ft = ff + fm sat_sub = at + bt + ct + dt + ft pass_no = at + bt + ct + dt pass_pct = round(pass_no / sat_sub * 100, 1) if sat_sub else 0 # -------------------------------------------------- # GPA FROM gpa_calculation (OFFICIAL) # -------------------------------------------------- gpa_row = subj_gpa_map.get(code) subj_gpa = round( gpa_row["weighted_sum"] / gpa_row["total_sum"], 4 ) if gpa_row and gpa_row["total_sum"] else 0 subj_grade, subj_status = gpa_to_grade_status(subj_gpa) # -------------------------------------------------- # FINAL ROW # -------------------------------------------------- subject_rows.append({ "code": code, "subject": subject_map.get(code, code), "sat": sat_sub, "af": af, "am": am, "at": at, "bf": bf, "bm": bm, "bt": bt, "cf": cf, "cm": cm, "ct": ct, "df": df, "dm": dm, "dt": dt, "ff": ff, "fm": fm, "ft": ft, "pass_no": pass_no, "pass_pct": pass_pct, # ✅ OFFICIAL VALUES "gpa": subj_gpa, "grade": subj_grade, "status": subj_status, }) div_qs = SchoolDivisionSummary.objects.filter( year=year, exam_type_id=exam_type_id, school__district_id=district_id ) division_pie = [] DIV_ORDER = ["I", "II", "III", "IV", "0"] rows = ( div_qs .values("division") .annotate(total=Sum("total")) ) total_sat = sum(r["total"] for r in rows) div_map = {r["division"]: r["total"] for r in rows} for d in DIV_ORDER: if d in div_map: division_pie.append({ "division": d, "total": div_map[d], "pct": round(div_map[d] / total_sat * 100, 1) if total_sat else 0 }) pass_gender_pie = [] pass_qs = div_qs.filter(division__in=["I","II","III","IV"]) pf = pass_qs.filter(sex="F").aggregate(t=Sum("total"))["t"] or 0 pm = pass_qs.filter(sex="M").aggregate(t=Sum("total"))["t"] or 0 pass_total = pf + pm if pass_total: pass_gender_pie = [ {"sex": "F", "total": pf, "pct": round(pf / pass_total * 100, 1)}, {"sex": "M", "total": pm, "pct": round(pm / pass_total * 100, 1)}, ] division_gender_bar = [] for d in DIV_ORDER: f = div_qs.filter(division=d, sex="F").aggregate(t=Sum("total"))["t"] or 0 m = div_qs.filter(division=d, sex="M").aggregate(t=Sum("total"))["t"] or 0 if f or m: division_gender_bar.append({ "division": d, "f": f, "m": m }) exam = ExamType.objects.get(id=exam_type_id) # -------------------------------------------------- # FINAL CONTEXT # -------------------------------------------------- return { "region": region, "district": district, "exam_type": exam_type_id, "year": year, "exam": exam, "totals": { "reg_f": reg_f, "reg_m": reg_m, "reg": reg, "sat_f": sat_f, "sat_m": sat_m, "sat": sat_a, "abs_f": abs_f, "abs_m": abs_m, "abs": abs_total, "avg": district_final_gpa, "grade": final_grade, "status": final_status, }, "grades": grades, "subjects": subject_rows, "a_c_f": a_c_f, "a_c_m": a_c_m, "a_c_total": a_c_total, "a_d_f": a_d_f, "a_d_m": a_d_m, "a_d_total": a_d_total, # 🔁 DIFFERENCE FROM REGION SUMMARY "ward_perf": ward_rows, "best_schools": best_schools, "years": years, "exam_types": exam_types, "division_pie": division_pie, "pass_gender_pie": pass_gender_pie, "division_gender_bar": division_gender_bar, } def compute_subject_dis_school_rank(district_id, exam_type_id, year, subject_code): rows = [] schools = School.objects.filter(district_id=district_id) SUB_WEIGHT = {"A":1,"B":2,"C":3,"D":4,"F":5} for s in schools: qs = SchoolSubjectGradeSummary.objects.filter( year=int(year), exam_type_id=int(exam_type_id), subject_code=subject_code, # ✅ ONLY CODE school=s ) if not qs.exists(): continue def g(grade, sex=None): q = Q(grade=grade) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 af, am = g("A","F"), g("A","M") bf, bm = g("B","F"), g("B","M") cf, cm = g("C","F"), g("C","M") df, dm = g("D","F"), g("D","M") ff, fm = g("F","F"), g("F","M") at, bt, ct, dt, ft = af+am, bf+bm, cf+cm, df+dm, ff+fm sat = at+bt+ct+dt+ft pass_no = at+bt+ct+dt pass_pct = round(pass_no/sat*100,2) if sat else 0 weighted_sum = at*1 + bt*2 + ct*3 + dt*4 + ft*5 gpa = round(weighted_sum/sat,4) if sat else 0 grade, status = gpa_to_grade_status(gpa) rows.append({ "code": s.school_code, "name": s.name_short, "district": s.district.name, "a": at, "b": bt, "c": ct, "d": dt, "f": ft, "pass": pass_no, "pass_pct": pass_pct, "gpa": gpa, "grade": grade, "status": status, }) rows.sort(key=lambda x: x["gpa"] if x["gpa"]>0 else 99) for i,r in enumerate(rows,1): r["rank"] = i return rows def compute_subject_dis_school_rank_ext(district_id, exam_type_id, year, subject_code): rows = [] schools = School.objects.filter(district_id=district_id) SUB_WEIGHT = {"A":1,"B":2,"C":3,"D":4,"F":5} for s in schools: qs = SchoolSubjectGradeSummary.objects.filter( year=int(year), exam_type_id=int(exam_type_id), subject_code=subject_code, # ✅ ONLY CODE school=s ) if not qs.exists(): continue def g(grade, sex=None): q = Q(grade=grade) if sex: q &= Q(sex=sex) return qs.filter(q).aggregate(t=Sum("total"))["t"] or 0 af, am = g("A","F"), g("A","M") bf, bm = g("B","F"), g("B","M") cf, cm = g("C","F"), g("C","M") df, dm = g("D","F"), g("D","M") ff, fm = g("F","F"), g("F","M") at, bt, ct, dt, ft = af+am, bf+bm, cf+cm, df+dm, ff+fm xf, xm = g("X","F"), g("X","M") xt = xf + xm # SAT = A+B+C+D+F sat_f = af + bf + cf + df + ff sat_m = am + bm + cm + dm + fm sat_t = sat_f + sat_m # REGISTERED = SAT + X reg_f = sat_f + xf reg_m = sat_m + xm reg_t = reg_f + reg_m sat = at+bt+ct+dt+ft pass_no = at+bt+ct+dt pass_pct = round(pass_no/sat*100,2) if sat else 0 # ----- PASS A–D ----- pass_f = af + bf + cf + df pass_m = am + bm + cm + dm pass_t = pass_f + pass_m pass_pct = round(pass_t / sat * 100, 2) if sat else 0 weighted_sum = at*1 + bt*2 + ct*3 + dt*4 + ft*5 gpa = round(weighted_sum/sat,4) if sat else 0 grade, status = gpa_to_grade_status(gpa) rows.append({ "code": s.school_code, "name": s.name_short, "code": s.school_code, "district": s.district.name, "ownership": s.ownership, "reg_f": reg_f, "reg_m": reg_m, "reg_t": reg_t, "sat_f": sat_f, "sat_m": sat_m, "sat_t": sat_t, "af":af,"am":am,"at":at,"a_pct":round(at/sat*100,2) if sat else 0, "bf":bf,"bm":bm,"bt":bt,"b_pct":round(bt/sat*100,2) if sat else 0, "cf":cf,"cm":cm,"ct":ct,"c_pct":round(ct/sat*100,2) if sat else 0, "df":df,"dm":dm,"dt":dt,"d_pct":round(dt/sat*100,2) if sat else 0, "ff":ff,"fm":fm,"ft":ft,"f_pct":round(ft/sat*100,2) if sat else 0, "pass_f":pass_f, "pass_m":pass_m, "pass_t":pass_t, "pass_pct":pass_pct, "gpa":gpa, "grade":grade, "status":status }) rows.sort(key=lambda x: x["gpa"] if x["gpa"]>0 else 99) for i,r in enumerate(rows,1): r["rank"] = i return rows def compute_school_subject_trend( school_code, subject_code, start_year, end_year, exam_type_id ): start_year = int(start_year) end_year = int(end_year) exam_type_id = int(exam_type_id) rows = [] prev_gpa = None for y in range(start_year, end_year + 1): # ---------------- SUBJECT GRADE COUNTS ---------------- qs = SchoolSubjectGradeSummary.objects.filter( school__school_code=school_code, subject_code=subject_code, year=y, exam_type_id=exam_type_id ) def g(grade): return qs.filter(grade=grade).aggregate( t=Sum("total") )["t"] or 0 a = g("A") b = g("B") c = g("C") d = g("D") f = g("F") sat = a + b + c + d + f ac = a + b + c ad = a + b + c + d pct_ac = round(ac / sat * 100, 1) if sat else 0 pct_ad = round(ad / sat * 100, 1) if sat else 0 # ---------------- SUBJECT GPA (SOURCE OF TRUTH) ---------------- perf = SchoolSubjectPerformance.objects.filter( school_code=school_code, subject_code=subject_code, year=y, exam_type_id=exam_type_id ).first() gpa = float(perf.subj_gpa) if perf else 0 # ---------------- TREND LOGIC (SAME AS SCHOOL TREND) ---------------- if prev_gpa is None: remark = "START" icon = "◎" elif gpa < prev_gpa: remark = "RISE" icon = "▲" elif gpa > prev_gpa: remark = "DROP" icon = "▼" else: remark = "SAME" icon = "■" prev_gpa = gpa rows.append({ "year": y, "a": a, "b": b, "c": c, "d": d, "f": f, "ac": ac, "pct_ac": pct_ac, "ad": ad, "pct_ad": pct_ad, "gpa": round(gpa, 4), "remark": remark, "icon": icon }) return rows from django.db.models import Sum def compute_region_subject_trend( region_id, subject_code, start_year, end_year, exam_type_id ): region_id = int(region_id) start_year = int(start_year) end_year = int(end_year) exam_type_id = int(exam_type_id) rows = [] prev_gpa = None for y in range(start_year, end_year + 1): qs = SchoolSubjectGradeSummary.objects.filter( school__region_id=region_id, subject_code=subject_code, year=y, exam_type_id=exam_type_id ) def g(grade): return qs.filter(grade=grade).aggregate(t=Sum("total"))["t"] or 0 a, b, c, d, f = g("A"), g("B"), g("C"), g("D"), g("F") sat = a + b + c + d + f ac = a + b + c ad = ac + d pct_ac = round(ac / sat * 100, 1) if sat else 0 pct_ad = round(ad / sat * 100, 1) if sat else 0 # ✅ GPA SOURCE OF TRUTH (GLOBAL WEIGHT) weighted = ( a*1 + b*2 + c*3 + d*4 + f*5 ) gpa = round(weighted / sat, 4) if sat else 0 # TREND if prev_gpa is None: remark, icon = "START", "◎" elif gpa < prev_gpa: remark, icon = "RISE", "▲" elif gpa > prev_gpa: remark, icon = "DROP", "▼" else: remark, icon = "SAME", "■" prev_gpa = gpa rows.append({ "year": y, "a": a, "b": b, "c": c, "d": d, "f": f, "ac": ac, "pct_ac": pct_ac, "ad": ad, "pct_ad": pct_ad, "gpa": gpa, "remark": remark, "icon": icon, }) return rows def compute_district_subject_trend( district_id, subject_code, start_year, end_year, exam_type_id ): district_id = int(district_id) start_year = int(start_year) end_year = int(end_year) exam_type_id = int(exam_type_id) rows = [] prev_gpa = None for y in range(start_year, end_year + 1): qs = SchoolSubjectGradeSummary.objects.filter( school__district_id=district_id, subject_code=subject_code, year=y, exam_type_id=exam_type_id ) def g(grade): return qs.filter(grade=grade).aggregate(t=Sum("total"))["t"] or 0 a, b, c, d, f = g("A"), g("B"), g("C"), g("D"), g("F") sat = a + b + c + d + f ac = a + b + c ad = ac + d pct_ac = round(ac / sat * 100, 1) if sat else 0 pct_ad = round(ad / sat * 100, 1) if sat else 0 weighted = ( a*1 + b*2 + c*3 + d*4 + f*5 ) gpa = round(weighted / sat, 4) if sat else 0 if prev_gpa is None: remark, icon = "START", "◎" elif gpa < prev_gpa: remark, icon = "RISE", "▲" elif gpa > prev_gpa: remark, icon = "DROP", "▼" else: remark, icon = "SAME", "■" prev_gpa = gpa rows.append({ "year": y, "a": a, "b": b, "c": c, "d": d, "f": f, "ac": ac, "pct_ac": pct_ac, "ad": ad, "pct_ad": pct_ad, "gpa": gpa, "remark": remark, "icon": icon, }) return rows def compute_project_school_rank(project_id): """ Compute school rankings for a project based on ExamSchoolResult """ from django.db.models import Sum, Count project = JointExamProject.objects.get(id=project_id) # Get all school results for this project school_results = ExamSchoolResult.objects.filter( project_id=project_id, sch_rank_involvement=True ).select_related('school', 'school__district', 'school__region') rows = [] for sr in school_results: school = sr.school # Get division counts from StudentFinalResult div_counts = StudentFinalResult.objects.filter( project_id=project_id, student__school=school ).values('division').annotate( count=Count('id') ) # Initialize division counters d1 = d2 = d3 = d4 = d0 = 0 for dc in div_counts: if dc['division'] == 'I': d1 = dc['count'] elif dc['division'] == 'II': d2 = dc['count'] elif dc['division'] == 'III': d3 = dc['count'] elif dc['division'] == 'IV': d4 = dc['count'] elif dc['division'] == '0': d0 = dc['count'] # Calculate totals pass_i_iv = d1 + d2 + d3 + d4 total = pass_i_iv + d0 pass_pct = round(pass_i_iv / total * 100, 2) if total > 0 else 0 # Get GPA from school result gpa = round(float(sr.sch_gpa), 4) if sr.sch_gpa else 0 rows.append({ "code": school.school_code, "name": school.name, "name_short": school.name_short or school.name, "district": school.district.name, "region": school.region.name, "ownership": school.ownership, "d1": d1, "d2": d2, "d3": d3, "d4": d4, "d0": d0, "pass": pass_i_iv, "pass_pct": pass_pct, "gpa": gpa, "with_results": sr.with_results, "sch_avg": round(sr.sch_avg, 2) if sr.sch_avg else 0, "sch_avg_grd": sr.sch_avg_grd or '-', "sch_avg_status": sr.sch_avg_status or '-', "sch_gpa_grade": sr.sch_gpa_grade or '-', "sch_gpa_status": sr.sch_gpa_status or '-', }) # Sort by GPA (lower is better for most exam systems) rows.sort(key=lambda x: (x["gpa"] == 0, x["gpa"])) # Assign ranks for i, r in enumerate(rows, 1): r["rank"] = i return rows, project def build_project_school_summary_context(project_id, school_code): """ Build comprehensive school summary for joint exam projects using: - ProcessedSubjectScore (subject grades) - ExamSchoolResult (school level results) - ProjectStudent (student registration) - StudentFinalResult (final division results) - ExamSubjectResult (subject level results) """ from decimal import Decimal from django.db.models import Avg, Count, Q, F project = JointExamProject.objects.get(id=project_id) school = School.objects.get(school_code=school_code) # ===================================================== # 1. STUDENT REGISTRATION DATA (FROM ProjectStudent) # ===================================================== students = ProjectStudent.objects.filter( project_id=project_id, school=school ) # Count by gender reg_m = students.filter(sex__iexact="M").count() reg_f = students.filter(sex__iexact="F").count() reg_total = students.count() # ===================================================== # 2. STUDENTS WITH RESULTS (FROM StudentFinalResult) # ===================================================== final_results = StudentFinalResult.objects.filter( project_id=project_id, student__school=school ) # Students with valid results (not absent) with_results = final_results.exclude(std_average='X').exclude(division='ABS') sat_m = with_results.filter(student__sex__iexact="M").count() sat_f = with_results.filter(student__sex__iexact="F").count() sat_total = with_results.count() # Absent students absent = final_results.filter( Q(std_average='X') | Q(division='ABS') ) abs_m = absent.filter(student__sex__iexact="M").count() abs_f = absent.filter(student__sex__iexact="F").count() abs_total = absent.count() # ===================================================== # 3. DIVISION PERFORMANCE (FROM StudentFinalResult) # ===================================================== divisions = {} for div in ["I", "II", "III", "IV", "0"]: divisions[div] = { "m": final_results.filter( division=div, student__sex__iexact="M" ).count(), "f": final_results.filter( division=div, student__sex__iexact="F" ).count(), } divisions[div]["total"] = divisions[div]["m"] + divisions[div]["f"] # Calculate pass I-III and pass I-IV a_c_m = divisions["I"]["m"] + divisions["II"]["m"] + divisions["III"]["m"] a_c_f = divisions["I"]["f"] + divisions["II"]["f"] + divisions["III"]["f"] a_c_total = a_c_m + a_c_f a_d_m = a_c_m + divisions["IV"]["m"] a_d_f = a_c_f + divisions["IV"]["f"] a_d_total = a_d_m + a_d_f # ===================================================== # 4. SCHOOL AVERAGE & GPA (FROM ExamSchoolResult) # ===================================================== school_result = ExamSchoolResult.objects.filter( project_id=project_id, school=school ).first() if school_result: school_avg = school_result.sch_avg school_avg_grd = school_result.sch_avg_grd school_avg_status = school_result.sch_avg_status school_gpa = school_result.sch_gpa school_gpa_grade = school_result.sch_gpa_grade school_gpa_status = school_result.sch_gpa_status with_results_count = school_result.with_results else: school_avg = school_avg_grd = school_avg_status = None school_gpa = school_gpa_grade = school_gpa_status = None with_results_count = sat_total # ===================================================== # 5. CALCULATE SCHOOL RANKS # ===================================================== # Get all schools with results in this project all_school_results = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).select_related('school').order_by('sch_gpa') # Lower GPA is better # Total schools overall_total = all_school_results.count() # Schools in same district district_results = all_school_results.filter( school__district=school.district ) district_total = district_results.count() # Schools in same region region_results = all_school_results.filter( school__region=school.region ) region_total = region_results.count() # Calculate ranks (position in sorted list) school_rank = None if school_result and school_result.sch_gpa is not None: # Overall rank overall_list = list(all_school_results.values_list('id', flat=True)) if school_result.id in overall_list: overall_rank = overall_list.index(school_result.id) + 1 else: overall_rank = None # District rank district_list = list(district_results.values_list('id', flat=True)) if school_result.id in district_list: district_rank = district_list.index(school_result.id) + 1 else: district_rank = None # Region rank region_list = list(region_results.values_list('id', flat=True)) if school_result.id in region_list: region_rank = region_list.index(school_result.id) + 1 else: region_rank = None # Calculate percentile for overall rank overall_percentile = None if overall_rank and overall_total > 0: overall_percentile = round((overall_rank / overall_total) * 100, 1) school_rank = { 'district_rank': district_rank, 'district_total': district_total, 'district_position': district_rank, # For badge coloring 'region_rank': region_rank, 'region_total': region_total, 'region_position': region_rank, 'overall_rank': overall_rank, 'overall_total': overall_total, 'overall_percentile': overall_percentile, } # ===================================================== # 6. SUBJECT PERFORMANCE (FROM ProcessedSubjectScore) # ===================================================== subject_scores = ProcessedSubjectScore.objects.filter( project_id=project_id, student__school=school ).select_related("subject", "student") subjects_data = {} for score in subject_scores: subj_code = score.subject.subject_code if subj_code not in subjects_data: subjects_data[subj_code] = { "code": subj_code, "subject": score.subject.subject_name_eng, "shortname": score.subject.subject_shortname or score.subject.subject_code, "sat": 0, "scores": [], "grades": [], "by_sex": {"M": [], "F": []}, "grade_counts": {"A": 0, "B": 0, "C": 0, "D": 0, "E": 0, "S": 0, "F": 0}, "grade_counts_m": {"A": 0, "B": 0, "C": 0, "D": 0, "E": 0, "S": 0, "F": 0}, "grade_counts_f": {"A": 0, "B": 0, "C": 0, "D": 0, "E": 0, "S": 0, "F": 0}, } if score.percentage_score and score.percentage_score != 'X': try: score_val = float(score.percentage_score) subjects_data[subj_code]["scores"].append(score_val) # Track by sex sex = score.student.sex subjects_data[subj_code]["by_sex"][sex].append(score_val) # Track grades if score.grade in ["A", "B", "C", "D", "E", "S", "F"]: subjects_data[subj_code]["grade_counts"][score.grade] += 1 if sex == "M": subjects_data[subj_code]["grade_counts_m"][score.grade] += 1 else: subjects_data[subj_code]["grade_counts_f"][score.grade] += 1 except (ValueError, TypeError): pass subjects_data[subj_code]["sat"] += 1 # ===================================================== # 7. SUBJECT RESULTS (FROM ExamSubjectResult) # ===================================================== subject_results = ExamSubjectResult.objects.filter( project_id=project_id, school=school ).select_related("subject") for result in subject_results: subj_code = result.subject.subject_code if subj_code in subjects_data: subjects_data[subj_code].update({ "gpa": result.subj_gpa, "grade": result.subj_avg_grd, "status": result.subj_avg_status, }) # Build final subjects list subjects = [] for subj_code, data in subjects_data.items(): scores = data["scores"] # Calculate subject average if scores: avg_score = sum(scores) / len(scores) avg_score = round(avg_score, 2) else: avg_score = 0 # Count passes (grades A-D) pass_no = ( data["grade_counts"]["A"] + data["grade_counts"]["B"] + data["grade_counts"]["C"] + data["grade_counts"]["D"] ) pass_pct = round((pass_no / data["sat"] * 100), 1) if data["sat"] else 0 subjects.append({ "code": data["code"], "subject": data["subject"], "shortname": data["shortname"], "sat": data["sat"], "af": data["grade_counts_f"]["A"], "am": data["grade_counts_m"]["A"], "at": data["grade_counts"]["A"], "bf": data["grade_counts_f"]["B"], "bm": data["grade_counts_m"]["B"], "bt": data["grade_counts"]["B"], "cf": data["grade_counts_f"]["C"], "cm": data["grade_counts_m"]["C"], "ct": data["grade_counts"]["C"], "df": data["grade_counts_f"]["D"], "dm": data["grade_counts_m"]["D"], "dt": data["grade_counts"]["D"], "ef": data["grade_counts_f"]["E"], "em": data["grade_counts_m"]["E"], "et": data["grade_counts"]["E"], "sf": data["grade_counts_f"]["S"], "sm": data["grade_counts_m"]["S"], "st": data["grade_counts"]["S"], "ff": data["grade_counts_f"]["F"], "fm": data["grade_counts_m"]["F"], "ft": data["grade_counts"]["F"], "pass_no": pass_no, "pass_pct": pass_pct, "avg": avg_score, "gpa": data.get("gpa", "-"), "grade": data.get("grade", "-"), "status": data.get("status", "-"), }) # Sort subjects by code subjects.sort(key=lambda x: x["code"]) # Add after subject results section (around line where subjects are built) # ===================================================== # CALCULATE SUBJECT RANKS # ===================================================== for subject_data in subjects: subj_code = subject_data['code'] # Get all schools' performance for this subject subject_performances = ExamSubjectResult.objects.filter( project_id=project_id, subject__subject_code=subj_code, subj_gpa__isnull=False ).select_related('school').order_by('subj_gpa') # Lower GPA is better # Find this school's performance school_performance = subject_performances.filter(school=school).first() if school_performance: # Overall rank overall_list = list(subject_performances.values_list('id', flat=True)) if school_performance.id in overall_list: subject_data['overall_rank'] = overall_list.index(school_performance.id) + 1 subject_data['overall_total'] = subject_performances.count() # Calculate percentile if subject_data['overall_total'] > 0: subject_data['overall_percentile'] = round( (subject_data['overall_rank'] / subject_data['overall_total']) * 100, 1 ) # District rank district_performances = subject_performances.filter( school__district=school.district ) district_list = list(district_performances.values_list('id', flat=True)) if school_performance.id in district_list: subject_data['district_rank'] = district_list.index(school_performance.id) + 1 subject_data['district_total'] = district_performances.count() # Region rank region_performances = subject_performances.filter( school__region=school.region ) region_list = list(region_performances.values_list('id', flat=True)) if school_performance.id in region_list: subject_data['region_rank'] = region_list.index(school_performance.id) + 1 subject_data['region_total'] = region_performances.count() else: subject_data['overall_rank'] = None subject_data['district_rank'] = None subject_data['region_rank'] = None # ===================================================== # COMBINATION DIVISION PERFORMANCE # ===================================================== from django.db.models import Count, Q, Avg # Get all combinations in this school combinations = ExamCombination.objects.filter( projectstudent__project_id=project_id, projectstudent__school=school ).distinct() combo_data = [] totals_by_combo = { 'total_I': 0, 'total_II': 0, 'total_III': 0, 'total_IV': 0, 'total_0': 0, 'grand_total': 0, 'total_pass': 0 } gpa_sum = 0 gpa_count = 0 for combo in combinations: # Get students in this combination students = ProjectStudent.objects.filter( project_id=project_id, school=school, combination=combo ) # Get their final results results = StudentFinalResult.objects.filter( project_id=project_id, student__in=students ) # Count divisions div_counts = { 'I': results.filter(division='I').count(), 'II': results.filter(division='II').count(), 'III': results.filter(division='III').count(), 'IV': results.filter(division='IV').count(), '0': results.filter(division='0').count(), } total_students = sum(div_counts.values()) pass_i_iii = div_counts['I'] + div_counts['II'] + div_counts['III'] # Calculate GPA (using division points) if total_students > 0: # Division GPA rules (I=1, II=2, III=3, IV=4, 0=5) gpa = ( div_counts['I'] * 1 + div_counts['II'] * 2 + div_counts['III'] * 3 + div_counts['IV'] * 4 + div_counts['0'] * 5 ) / total_students gpa_sum += gpa gpa_count += 1 else: gpa = None combo_data.append({ 'code': combo.code, 'name': combo.name, 'division_counts': div_counts, 'total_students': total_students, 'pass_i_iii': pass_i_iii, 'gpa': gpa, }) # Update totals totals_by_combo['total_I'] += div_counts['I'] totals_by_combo['total_II'] += div_counts['II'] totals_by_combo['total_III'] += div_counts['III'] totals_by_combo['total_IV'] += div_counts['IV'] totals_by_combo['total_0'] += div_counts['0'] totals_by_combo['grand_total'] += total_students totals_by_combo['total_pass'] += pass_i_iii # Calculate ranks for combinations combo_data_sorted = sorted(combo_data, key=lambda x: x['gpa'] if x['gpa'] else 999) for idx, combo in enumerate(combo_data_sorted, 1): combo['rank'] = idx combo['total_combinations'] = len(combo_data_sorted) # Overall GPA for all combinations if gpa_count > 0: totals_by_combo['overall_gpa'] = gpa_sum / gpa_count else: totals_by_combo['overall_gpa'] = None # ===================================================== # 8. BUILD FINAL CONTEXT # ===================================================== context = { "project": project, "school": school, "year": project.year, "exam": project, "totals": { "reg": reg_total, "reg_m": reg_m, "reg_f": reg_f, "sat": sat_total, "sat_m": sat_m, "sat_f": sat_f, "abs": abs_total, "abs_m": abs_m, "abs_f": abs_f, "avg": school_avg, "grade": school_avg_grd, "status": school_avg_status, "gpa": school_gpa, "gpa_grade": school_gpa_grade, "gpa_status": school_gpa_status, "with_results": with_results_count, }, "grades": { "a": divisions["I"]["total"], "am": divisions["I"]["m"], "af": divisions["I"]["f"], "b": divisions["II"]["total"], "bm": divisions["II"]["m"], "bf": divisions["II"]["f"], "c": divisions["III"]["total"], "cm": divisions["III"]["m"], "cf": divisions["III"]["f"], "d": divisions["IV"]["total"], "dm": divisions["IV"]["m"], "df": divisions["IV"]["f"], "e": divisions["0"]["total"], "em": divisions["0"]["m"], "ef": divisions["0"]["f"], }, "a_c_m": a_c_m, "a_c_f": a_c_f, "a_c_total": a_c_total, "a_d_m": a_d_m, "a_d_f": a_d_f, "a_d_total": a_d_total, "subjects": subjects, "school_result": school_result, "school_rank": school_rank, # Add rank data to context "combinations": combo_data_sorted, # Added inside the dictionary "totals_by_combo": totals_by_combo, # Added inside the dictionary } return context def build_project_school_detailed_results(project_id, school_code): """ Build detailed school results with student-level data and subject scores Shows all subjects from student's combination, with X for missing data """ from decimal import Decimal project = JointExamProject.objects.get(id=project_id) school = School.objects.get(school_code=school_code) # ===================================================== # 1. SCHOOL SUMMARY DATA (FROM ExamSchoolResult) # ===================================================== school_result = ExamSchoolResult.objects.filter( project_id=project_id, school=school ).first() # ===================================================== # 2. DIVISION COUNTS (FROM StudentFinalResult) # ===================================================== final_results = StudentFinalResult.objects.filter( project_id=project_id, student__school=school ) division_counts = { 'I': final_results.filter(division='I').count(), 'II': final_results.filter(division='II').count(), 'III': final_results.filter(division='III').count(), 'IV': final_results.filter(division='IV').count(), '0': final_results.filter(division='0').count(), } total_students = sum(division_counts.values()) # ===================================================== # 3. SCHOOL RANKS # ===================================================== all_school_results = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).order_by('sch_gpa') overall_total = all_school_results.count() school_rank = None if school_result: school_list = list(all_school_results.values_list('id', flat=True)) if school_result.id in school_list: school_rank = school_list.index(school_result.id) + 1 # ===================================================== # 4. GET ALL SUBJECTS THAT EXIST IN THIS PROJECT # ===================================================== # Fixed: Use distinct to avoid duplicates all_subjects = ExamSubject.objects.filter( papers__examscore__project_id=project_id, papers__examscore__student__school=school ).distinct().order_by('subject_code') # If no subjects found, try through ProcessedSubjectScore if not all_subjects.exists(): all_subjects = ExamSubject.objects.filter( processedsubjectscore__project_id=project_id, processedsubjectscore__student__school=school ).distinct().order_by('subject_code') # Create a map of subject code to subject object subject_map = { s.subject_code: s for s in all_subjects } # ===================================================== # 5. GET STUDENTS AND THEIR REGISTRATIONS # ===================================================== students = ProjectStudent.objects.filter( project_id=project_id, school=school ).select_related('combination').order_by('student_number') # Get all exam scores (registrations) for this school exam_scores = ExamScore.objects.filter( project_id=project_id, student__school=school ).select_related('student', 'paper__subject') # Organize registrations by student and subject registrations_map = {} for score in exam_scores: student_id = score.student_id if score.paper and score.paper.subject: subject_code = score.paper.subject.subject_code if student_id not in registrations_map: registrations_map[student_id] = set() registrations_map[student_id].add(subject_code) # Get all processed scores (actual marks) processed_scores = ProcessedSubjectScore.objects.filter( project_id=project_id, student__school=school ).select_related('student', 'subject') # Organize processed scores by student and subject scores_map = {} for score in processed_scores: student_id = score.student_id subject_code = score.subject.subject_code if student_id not in scores_map: scores_map[student_id] = {} scores_map[student_id][subject_code] = { 'score': score.percentage_score, 'grade': score.grade, } # Get final results final_results_map = { r.student_id: r for r in StudentFinalResult.objects.filter( project_id=project_id, student__school=school ) } # ===================================================== # 6. BUILD STUDENT ROWS WITH ALL SUBJECTS IN FIXED ORDER # ===================================================== student_rows = [] # Get sorted subject codes from all_subjects sorted_subject_codes = [s.subject_code for s in all_subjects] for student in students: # Create subject pairs in the same order as all_subjects subject_pairs = [] subject_scores_list = [] for subject_code in sorted_subject_codes: score_data = scores_map.get(student.id, {}).get(subject_code, {}) score = score_data.get('score', '') grade = score_data.get('grade', '') subject = subject_map.get(subject_code) # Format score to remove decimal places score_display = '' if score and score != 'X': try: score_display = str(int(float(score))) except (ValueError, TypeError): score_display = str(score) elif score == 'X': score_display = 'X' # Determine grade display grade_display = grade if grade else ('' if not score_display else '') # Create subject pair for template subject_pairs.append({ 'code': subject_code, 'name': subject.subject_name_eng if subject else subject_code, 'score': score_display, 'grade': grade_display, }) # Create combined string for backward compatibility if score_display and grade_display: subject_scores_list.append(f"{score_display} {grade_display}") elif score_display: subject_scores_list.append(score_display) elif grade_display: subject_scores_list.append(f"X {grade_display}") else: subject_scores_list.append('X X') final = final_results_map.get(student.id) student_rows.append({ 'cand_no': student.student_number, 'name': student.full_name, 'sex': student.sex, 'combination': student.combination.code if student.combination else '', 'subject_scores': subject_scores_list, 'subject_pairs': subject_pairs, 'average': final.std_average if final else '', 'avg_grade': final.std_averagegrade if final else '', 'points': final.total_points if final else '', 'division': final.division if final else '', }) # ===================================================== # 7. BUILD CONTEXT # ===================================================== context = { 'project': project, 'school': school, 'school_result': school_result, 'division_counts': division_counts, 'total_students': total_students, 'school_rank': school_rank, 'overall_total': overall_total, 'students': student_rows, 'subjects': all_subjects, # Keep for backward compatibility 'subject_pairs_list': True, # Flag to use new format } return context def prepare_school_detailed_results_excel_data(project_id, school_code): """ Prepare data for Excel export of school detailed results """ from datetime import datetime project = JointExamProject.objects.get(id=project_id) school = School.objects.get(school_code=school_code) # Get school result data school_result = ExamSchoolResult.objects.filter( project_id=project_id, school=school ).first() # Get division counts final_results = StudentFinalResult.objects.filter( project_id=project_id, student__school=school ) division_counts = { 'I': final_results.filter(division='I').count(), 'II': final_results.filter(division='II').count(), 'III': final_results.filter(division='III').count(), 'IV': final_results.filter(division='IV').count(), '0': final_results.filter(division='0').count(), } total_students = sum(division_counts.values()) # Calculate school rank all_school_results = ExamSchoolResult.objects.filter( project_id=project_id, sch_gpa__isnull=False ).order_by('sch_gpa') overall_total = all_school_results.count() school_rank = None if school_result: school_list = list(all_school_results.values_list('id', flat=True)) if school_result.id in school_list: school_rank = school_list.index(school_result.id) + 1 # Get subjects with their display names subjects = ExamSubject.objects.filter( processedsubjectscore__project_id=project_id, processedsubjectscore__student__school=school ).distinct().order_by('subject_code') # Create subject display names list subject_display_names = [] for subject in subjects: display_name = subject.subject_name_eng subject_display_names.append(display_name) # Get students with their scores students = ProjectStudent.objects.filter( project_id=project_id, school=school ).select_related('combination').order_by('student_number') # Get all processed scores scores = ProcessedSubjectScore.objects.filter( project_id=project_id, student__school=school ).select_related('student', 'subject') # Organize scores by student and subject scores_map = {} for score in scores: student_id = score.student_id subject_code = score.subject.subject_code if student_id not in scores_map: scores_map[student_id] = {} scores_map[student_id][subject_code] = { 'score': score.percentage_score, 'grade': score.grade, } # Get final results final_results_map = { r.student_id: r for r in StudentFinalResult.objects.filter( project_id=project_id, student__school=school ) } # Build student rows with subject pairs student_rows = [] for student in students: student_scores = scores_map.get(student.id, {}) # Create list of subject-grade pairs (matching the structure in build_project_school_detailed_results) subject_pairs = [] for subject in subjects: score_data = student_scores.get(subject.subject_code, {}) grade = score_data.get('grade', '') subject_pairs.append({ 'name': subject.subject_name_eng, 'grade': grade if grade else '', }) # Also keep subject_scores_list for backward compatibility if needed subject_scores_list = [] for subject in subjects: score_data = student_scores.get(subject.subject_code, {}) score = score_data.get('score', '') grade = score_data.get('grade', '') # Format score to remove decimal places if score and score != 'X': try: score = str(int(float(score))) except (ValueError, TypeError): pass subject_scores_list.append({ 'score': score if score else '', 'grade': grade if grade else '', }) final = final_results_map.get(student.id) student_rows.append({ 'cand_no': student.student_number, 'name': student.full_name, 'sex': student.sex, 'combination': student.combination.code if student.combination else '', 'subject_scores': subject_scores_list, # Keep for Excel column layout 'subject_pairs': subject_pairs, # Add for NECTA format display 'average': final.std_average if final else '', 'avg_grade': final.std_averagegrade if final else '', 'points': final.total_points if final else '', 'division': final.division if final else '', }) return { 'project': project, 'school': school, 'school_result': school_result, 'division_counts': division_counts, 'total_students': total_students, 'school_rank': school_rank, 'overall_total': overall_total, 'students': student_rows, 'subjects': subjects, 'subject_display_names': subject_display_names, 'generated_at': datetime.now().strftime('%Y-%m-%d %H:%M:%S'), }