# -------------------------------------------------------------- # MAIN DASHBOARD API (based on SchoolYearSummary) # -------------------------------------------------------------- @login_required @require_GET def ajax_dashboard_data(request): # ... your existing filter parsing code (region, district, school, examtype, start_year, end_year, etc.) region = request.GET.get("region") district = request.GET.get("district") ward = request.GET.get("ward") school = request.GET.get("school") # school_code examtype = request.GET.get("examtype") ownership = request.GET.get("ownership") start_year = request.GET.get("start_year") end_year = request.GET.get("end_year") subject_param = request.GET.get("subject") gender = request.GET.get("gender") # ============================ # Filters for Summary QS # ============================ qs = SchoolYearSummary.objects.select_related("school", "exam_type").all() # Apply gender filter ONLY if gender is M or F if region: qs = qs.filter(school__region_id=region) if district: qs = qs.filter(school__district_id=district) if ward: qs = qs.filter(school__ward_id=ward) if school: qs = qs.filter(school__school_code=school) if examtype: qs = qs.filter(exam_type_id=examtype) if ownership: qs = qs.filter(school__ownership=ownership) if start_year: qs = qs.filter(year__gte=int(start_year)) if end_year: qs = qs.filter(year__lte=int(end_year)) # Role restriction block (keep as you have it) profile = getattr(request.user, "profile", None) if profile and profile.role != "super": if profile.role == "region": qs = qs.filter(school__region_id=profile.region_id) elif profile.role == "district": qs = qs.filter(school__district_id=profile.district_id) elif profile.role == "ward": qs = qs.filter(school__ward_id=profile.ward_id) elif profile.role == "school": qs = qs.filter(school__school_code=profile.school.school_code) # -------------------------- # Candidate-level queryset (correct source for pass/fail counts) # -------------------------- # candidate filters cand_qs = ExamCandidate.objects.all() if gender in ["M", "F"]: cand_qs = cand_qs.filter(sex__iexact=gender) # apply same filters to candidate queryset (note: school join uses school_code -> school__school_code or school_code field depending on your model) if region: cand_qs = cand_qs.filter(school__region_id=region) if district: cand_qs = cand_qs.filter(school__district_id=district) if ward: cand_qs = cand_qs.filter(school__ward_id=ward) if school: # your ExamCandidate has field `school_code` (string) so filter by that: cand_qs = cand_qs.filter(school_code=school) # if instead you have a relation `school__school_code`, use that # cand_qs = cand_qs.filter(school__school_code=school) if examtype: cand_qs = cand_qs.filter(exam_type_id=examtype) if ownership: cand_qs = cand_qs.filter(school__ownership=ownership) if start_year: cand_qs = cand_qs.filter(year__gte=int(start_year)) if end_year: cand_qs = cand_qs.filter(year__lte=int(end_year)) # ROLE-based restriction for cand_qs as well (if you want candidate-level scoped by user) if profile and profile.role != "super": if profile.role == "region": cand_qs = cand_qs.filter(school__region_id=profile.region_id) elif profile.role == "district": cand_qs = cand_qs.filter(school__district_id=profile.district_id) elif profile.role == "ward": cand_qs = cand_qs.filter(school__ward_id=profile.ward_id) elif profile.role == "school": cand_qs = cand_qs.filter(school_code=profile.school.school_code) # -------------------------- # Candidate KPIs (correct computation) # -------------------------- # PASS filter - use __iexact to avoid case issues (PASS, Pass, pass) pass_candidates = cand_qs.filter(status__iexact="PASS").count() fail_candidates = cand_qs.filter(status__iexact="FAIL").count() total_candidates = pass_candidates + fail_candidates # build donut counts donut = [ {"name": "Pass", "value": int(pass_candidates)}, {"name": "Fail", "value": int(fail_candidates)}, ] # pass percentage (1 decimal) and other cards - use school-summary for avg marks if you prefer pass_pct = round((pass_candidates / max(total_candidates, 1)) * 100, 1) male_total = cand_qs.filter(sex__iexact="M").count() female_total = cand_qs.filter(sex__iexact="F").count() male_pass = cand_qs.filter(sex__iexact="M", status__iexact="PASS").count() female_pass = cand_qs.filter(sex__iexact="F", status__iexact="PASS").count() male_pct = round((male_pass / male_total * 100), 1) if male_total else 0 female_pct = round((female_pass / female_total * 100), 1) if female_total else 0 # keep your line chart and top schools logic unchanged but ensure you use `qs` as before year_stats = ( qs.values("year") .annotate(avg_mark=Avg("avg_numeric")) .order_by("year") ) years = [r["year"] for r in year_stats] avg_series = [float(r["avg_mark"] or 0) for r in year_stats] top_schools_raw = ( qs.values("school__school_code", "school__name") .annotate(avg_mark=Avg("avg_numeric"), candidates=Sum("candidates")) .order_by("-avg_mark")[:10] ) top_schools = [ { "school_code": r["school__school_code"], "school": r["school__name"], "avg": round(float(r["avg_mark"] or 0), 2), "candidates": int(r["candidates"] or 0), } for r in top_schools_raw ] # grades (same as before) - you already had cand_qs here so keep it grade_order = ["A", "B", "C", "D", "E"] grade_counts = cand_qs.values("avg_grade").annotate(count=Count("id")) grade_labels = grade_order grade_values = [ next((g["count"] for g in grade_counts if g["avg_grade"] == gr), 0) for gr in grade_order ] # gender/year pass rates — use actual field names: sex and status gender_stats = ( cand_qs.values("year") .annotate( male_pass=Count("id", filter=Q(sex__iexact="M") & Q(status__iexact="PASS")), male_total=Count("id", filter=Q(sex__iexact="M")), female_pass=Count("id", filter=Q(sex__iexact="F") & Q(status__iexact="PASS")), female_total=Count("id", filter=Q(sex__iexact="F")) ) .order_by("year") ) gender_years = [] male_rates = [] female_rates = [] for row in gender_stats: y = row["year"] gender_years.append(y) m_total = row["male_total"] or 0 f_total = row["female_total"] or 0 m_rate = (row["male_pass"] / m_total * 100) if m_total else 0 f_rate = (row["female_pass"] / f_total * 100) if f_total else 0 male_rates.append(round(m_rate, 1)) female_rates.append(round(f_rate, 1)) # subject-series code (unchanged) subject_series = {} if subject_param: school_codes = list(qs.values_list("school__school_code", flat=True).distinct()) exam_type_ids = list(qs.values_list("exam_type_id", flat=True).distinct()) years_list = list(qs.values_list("year", flat=True).distinct()) marks_qs = StudentSubjectMark.objects.filter( school__school_code__in=school_codes, exam_type_id__in=exam_type_ids, year__in=years_list, ) if subject_param.isdigit(): marks_qs = marks_qs.filter(subject_id=int(subject_param)) else: marks_qs = marks_qs.filter(subject__subject_code=subject_param) per_year_sub = marks_qs.values("year").annotate(avg=Avg("numeric_score")).order_by("year") subject_series = {"years":[x["year"] for x in per_year_sub], "avg_marks":[float(x["avg"] or 0) for x in per_year_sub]} # BUILD final JSON data = { "cards": [ {"title": "Total Schools", "value": qs.values_list("school_id", flat=True).distinct().count()}, {"title": "Total Candidates (rows)", "value": int(total_candidates)}, {"title": "Avg Marks", "value": round(qs.aggregate(avg=Avg("avg_numeric"))["avg"] or 0, 2)}, {"title": "Pass %", "value": f"{pass_pct}%"}, ], "donut": donut, "line": {"years": years, "avg_marks": avg_series}, "subject": subject_series, "top_schools": top_schools, "grades": {"labels": grade_labels, "values": grade_values}, "gender_pass": {"years": gender_years, "male": male_rates, "female": female_rates}, "gender_kpi": { "male_total": male_total, "female_total": female_total, "male_pass": male_pass, "female_pass": female_pass, "male_pct": male_pct, "female_pct": female_pct, }, } return JsonResponse(data, safe=False)