# exams/views.py
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
from django.contrib import messages
from django.contrib.auth import update_session_auth_hash
from django.contrib.auth.forms import PasswordChangeForm
from django.db import transaction
from django.template.loader import render_to_string
from django.db.models.functions import Coalesce
from django.core.paginator import Paginator
from django.views.decorators.http import require_POST
from django.db import connection
from django.utils.text import slugify
import pprint
from io import BytesIO
import csv, io
import logging
import math
from xhtml2pdf import pisa
# Excel (openpyxl)
import openpyxl
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
# PDF (ReportLab)
from reportlab.platypus import (
SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, Flowable
)
from reportlab.lib.pagesizes import A4, landscape
from reportlab.lib import colors
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import mm, inch
# Excel
import pandas as pd
# Forms
from .forms import UserUpdateForm, ProfileUpdateForm
from exams.views_helpers import (build_region_summary_context,
build_school_rank_data,
compute_district_ranking,build_best_worst_tables,
build_ward_rank_data,get_region_subject_summary,get_district_subject_ranking) # your existing helper
from exams.utils.pdf.pdf_tables import (
pdf_header_block_simple, pdf_students_block, pdf_grades_block,
pdf_ownership_block, pdf_district_summary_block, pdf_footer_block,
pdf_school_ranking_block,pdf_ward_ranking_table
)
from exams.utils.pdf.autofit import PAGE_USABLE_WIDTH
# Models
import os, tempfile
from exams.utils.pdf.pdf_tables import pdf_best_worst_group
from .models import (
Region, District, Ward, School,
ExamType, ExamSubject,
ExamCandidate, StudentSubjectMark,
SchoolYearSummary, UserProfile, SchoolSubjectPerformance,ExamSubject
)
User = get_user_model()
# --------------------------------------------------------------
# AUTH: REGISTER
# --------------------------------------------------------------
def register_page(request):
if request.method == "POST":
username = request.POST.get("username")
email = request.POST.get("email")
password = request.POST.get("password")
if User.objects.filter(username=username).exists():
return render(request, "exams/register.html", {
"error": "Username already exists"
})
User.objects.create_user(username=username, email=email, password=password)
return render(request, "exams/register.html", {
"message": "User registered successfully!"
})
return render(request, "exams/register.html")
# --------------------------------------------------------------
# AUTH: LOGIN
# --------------------------------------------------------------
def login_view(request):
if request.method == "POST":
user = authenticate(
username=request.POST.get("username"),
password=request.POST.get("password")
)
if user:
login(request, user)
return redirect("exams:dashboard")
return render(request, "exams/login.html", {"error": "Invalid login"})
return render(request, "exams/login.html")
# --------------------------------------------------------------
# DASHBOARD
# --------------------------------------------------------------
@login_required
def dashboard(request):
user = request.user
profile = getattr(user, "profile", None)
regions = Region.objects.all()
# default empty lists
districts = []
wards = []
schools = []
# populate dependent lists for lower-level users
if profile:
if profile.role == "region":
districts = District.objects.filter(region=profile.region)
elif profile.role == "district":
districts = District.objects.filter(region=profile.region)
wards = Ward.objects.filter(district=profile.district)
elif profile.role == "ward":
districts = District.objects.filter(region=profile.region)
wards = Ward.objects.filter(district=profile.district)
schools = School.objects.filter(ward=profile.ward)
elif profile.role == "school":
districts = District.objects.filter(region=profile.region)
wards = Ward.objects.filter(district=profile.district)
schools = School.objects.filter(ward=profile.ward)
# <-- IMPORTANT: load exam types and pass to template
exam_types = ExamType.objects.all().order_by('name') # or other order you prefer
context = {
"regions": regions,
"districts": districts,
"wards": wards,
"schools": schools,
"exam_types": exam_types,
"user": user,
"user_profile": profile, # if your JS expects user_profile
}
return render(request, "exams/dashboard.html", context)
# --------------------------------------------------------------
# CASCADE AJAX
# --------------------------------------------------------------
@login_required
def ajax_get_districts(request, region_id):
return JsonResponse(
list(District.objects.filter(region_id=region_id).values("id", "name")),
safe=False
)
@login_required
def ajax_get_wards(request, district_id):
return JsonResponse(
list(Ward.objects.filter(district_id=district_id).values("id", "name")),
safe=False
)
@login_required
def ajax_get_schools(request, ward_id):
# return school_code and name so frontend can use code as id
return JsonResponse(
list(School.objects.filter(ward_id=ward_id).values("school_code", "name")),
safe=False
)
# manage_users.py
User = get_user_model()
@login_required
@permission_required('auth.add_user', raise_exception=True)
def manage_users(request):
"""
GET: render manage users page.
POST: supports two modes:
- add_single: form fields for one user
- upload_csv: CSV bulk upload
"""
regions = Region.objects.all().order_by('name')
districts = District.objects.all().order_by('name')
wards = Ward.objects.all().order_by('name')
schools = School.objects.all().order_by('name')
if request.method == 'POST':
mode = request.POST.get('mode', 'add_single')
if mode == 'add_single':
username = request.POST.get('username', '').strip()
email = request.POST.get('email', '').strip()
password = request.POST.get('password', '').strip()
role = request.POST.get('role')
region_id = request.POST.get('region')
district_id = request.POST.get('district')
ward_id = request.POST.get('ward')
school_code = request.POST.get('school')
# server-side validation depending on role:
errors = []
def require_field(cond, msg):
if not cond:
errors.append(msg)
if not username:
errors.append('Username is required.')
if not role:
errors.append('Role is required.')
# conditional requirements:
if role == 'region':
require_field(region_id, 'Region must be selected for Region Admin.')
elif role == 'district':
require_field(region_id, 'Region must be selected for District Admin.')
require_field(district_id, 'District must be selected for District Admin.')
elif role == 'ward':
require_field(region_id, 'Region required for Ward Admin.')
require_field(district_id, 'District required for Ward Admin.')
require_field(ward_id, 'Ward required for Ward Admin.')
elif role == 'school':
require_field(region_id, 'Region required for School Admin.')
require_field(district_id, 'District required for School Admin.')
require_field(ward_id, 'Ward required for School Admin.')
require_field(school_code, 'School required for School Admin.')
if errors:
for e in errors:
messages.error(request, e)
return redirect('exams:manage_users')
# create user + profile inside a transaction
try:
with transaction.atomic():
# If password empty, use create_user without password or set unusable
if password:
user = User.objects.create_user(username=username, email=email, password=password)
else:
user = User.objects.create_user(username=username, email=email)
user.set_unusable_password()
user.save()
# Create profile
profile = UserProfile.objects.create(user=user, role=role)
# set relation fields if provided
if region_id:
profile.region_id = int(region_id)
if district_id:
profile.district_id = int(district_id)
if ward_id:
profile.ward_id = int(ward_id)
if school_code:
# adapt depending on your School PK - here we assume school_code is the identifier
try:
school_obj = School.objects.get(school_code=school_code)
profile.school = school_obj
except School.DoesNotExist:
messages.warning(request, f"School code {school_code} not found; profile saved without school.")
profile.save()
messages.success(request, f"User {username} created.")
return redirect('exams:manage_users')
except Exception as exc:
messages.error(request, f"Failed to create user: {exc}")
return redirect('exams:manage_users')
elif mode == 'upload_csv':
# CSV expected columns: username,email,role,region_id,district_id,ward_id,school_code,password(optional)
csv_file = request.FILES.get('csv_file')
if not csv_file:
messages.error(request, "No CSV uploaded.")
return redirect('exams:manage_users')
try:
data = csv_file.read().decode('utf-8')
reader = csv.DictReader(io.StringIO(data))
created = 0
errors = []
with transaction.atomic():
for i, row in enumerate(reader, start=1):
username = row.get('username','').strip()
email = row.get('email','').strip()
password = row.get('password','').strip()
role = row.get('role','').strip()
# simple validation
if not (username and role):
errors.append(f"Row {i}: missing username or role.")
continue
if User.objects.filter(username=username).exists():
errors.append(f"Row {i}: username '{username}' exists.")
continue
if password:
user = User.objects.create_user(username=username, email=email, password=password)
else:
user = User.objects.create_user(username=username, email=email)
user.set_unusable_password(); user.save()
profile = UserProfile.objects.create(user=user, role=role)
# optional assignments if provided in csv
if row.get('region_id'): profile.region_id = row.get('region_id')
if row.get('district_id'): profile.district_id = row.get('district_id')
if row.get('ward_id'): profile.ward_id = row.get('ward_id')
if row.get('school_code'):
try:
profile.school = School.objects.get(school_code=row.get('school_code'))
except School.DoesNotExist:
pass
profile.save()
created += 1
msg = f"Bulk upload done: {created} created."
if errors:
msg += " Some rows had errors: " + "; ".join(errors[:5])
messages.success(request, msg)
return redirect('exams:manage_users')
except Exception as e:
messages.error(request, f"CSV processing failed: {e}")
return redirect('exams:manage_users')
# GET
users = User.objects.all().select_related("profile")
return render(request, 'exams/manage_users.html', {
'users': users,
'regions': regions,
'districts': districts,
'wards': wards,
'schools': schools,
})
# ========== AJAX UPDATE USER ==========
@login_required
def update_user(request, user_id):
if request.method == "POST":
user = get_object_or_404(User, pk=user_id)
data = request.POST
user.username = data.get("username")
user.email = data.get("email")
user.role = data.get("role")
user.save()
return JsonResponse({"message": "User updated successfully"})
return JsonResponse({"error": "Invalid request"}, status=400)
# ========== DELETE USER ==========
@login_required
def delete_user(request, user_id):
user = get_object_or_404(User, pk=user_id)
user.delete()
return JsonResponse({"message": "User deleted successfully"})
# ========== RESET PASSWORD ==========
@login_required
def reset_user_password(request, user_id):
if request.method == "POST":
user = get_object_or_404(User, pk=user_id)
new_password = request.POST.get("new_password")
user.set_password(new_password)
user.save()
return JsonResponse({"message": "Password updated successfully"})
@login_required
def profile_view(request):
profile = request.user.profile
if request.method == "POST":
form = ProfileUpdateForm(request.POST, request.FILES, instance=profile)
if form.is_valid():
form.save()
messages.success(request, "Profile updated successfully!")
return redirect("exams:profile")
else:
form = ProfileUpdateForm(instance=profile)
return render(request, "exams/profile.html", {"form": form})
@login_required
def profile_update(request):
user = request.user
profile = user.profile
if request.method == "POST":
u_form = UserUpdateForm(request.POST, instance=user)
p_form = ProfileUpdateForm(request.POST, request.FILES, instance=profile)
if u_form.is_valid() and p_form.is_valid():
u_form.save()
p_form.save()
messages.success(request, "Profile updated successfully!")
return redirect("exams:profile")
else:
# FORM INVALID – RETURN PAGE WITH ERRORS
messages.error(request, "Please correct the errors below.")
return render(request, "exams/profile.html", {
"u_form": u_form,
"p_form": p_form
})
else:
u_form = UserUpdateForm(instance=user)
p_form = ProfileUpdateForm(instance=profile)
return render(request, "exams/profile.html", {
"u_form": u_form,
"p_form": p_form
})
@login_required
def change_password(request):
if request.method == "POST":
form = PasswordChangeForm(request.user, request.POST)
if form.is_valid():
user = form.save()
update_session_auth_hash(request, user)
messages.success(request, "Password updated!")
return redirect("exams:profile")
else:
form = PasswordChangeForm(request.user)
return render(request, "exams/change_password.html", {"form": form})
def compute_rank_aggregates(qs):
"""
Computes all aggregates needed for ranking:
- total
- pass_count
- fail_count
- pass_pct
- average score (avg_numeric)
- grades A–E (female/male/total)
"""
total = qs.count()
pass_count = qs.filter(status__iexact="PASS").count()
fail_count = qs.filter(status__iexact="FAIL").count()
pass_pct = round((pass_count / total * 100), 1) if total else 0
avg_row = qs.aggregate(avg=Avg("avg_numeric"))
avg_score = round(float(avg_row["avg"] or 0), 2)
# Grade buckets
def gcount(g, sex=None):
f = {"avg_grade__iexact": g}
if sex:
f["sex__iexact"] = sex
return qs.filter(**f).count()
grades = {
"A_f": gcount("A", "F"), "A_m": gcount("A", "M"),
"B_f": gcount("B", "F"), "B_m": gcount("B", "M"),
"C_f": gcount("C", "F"), "C_m": gcount("C", "M"),
"D_f": gcount("D", "F"), "D_m": gcount("D", "M"),
"E_f": gcount("E", "F"), "E_m": gcount("E", "M"),
}
# Total for each A–E
grades["A"] = grades["A_f"] + grades["A_m"]
grades["B"] = grades["B_f"] + grades["B_m"]
grades["C"] = grades["C_f"] + grades["C_m"]
grades["D"] = grades["D_f"] + grades["D_m"]
grades["E"] = grades["E_f"] + grades["E_m"]
# Pass A–C
grades["AC_total"] = grades["A"] + grades["B"] + grades["C"]
return {
"total": total,
"pass_count": pass_count,
"fail_count": fail_count,
"pass_pct": pass_pct,
"avg_score": avg_score,
"grades": grades,
}
# -------------------------
# Regionwise listing page
# -------------------------
@login_required
def regionwise(request):
user = request.user
years = ["2020","2021","2022","2023","2024","2025"]
# SUPER ADMIN → ALL REGIONS
if user.profile.role == "super":
regions = Region.objects.all().order_by("name")
# REGION ACCOUNT → ONLY THEIR REGION
elif user.profile.role == "region":
regions = Region.objects.filter(id=user.profile.region_id)
# DISTRICT ACCOUNT → THEIR REGION ONLY
elif user.profile.role == "district":
regions = Region.objects.filter(id=user.profile.district.region_id)
# WARD ACCOUNT → THEIR REGION
elif user.profile.role == "ward":
regions = Region.objects.filter(id=user.profile.ward.district.region_id)
# SCHOOL ACCOUNT → THEIR REGION
elif user.profile.role == "school":
regions = Region.objects.filter(id=user.profile.school.region_id)
else:
regions = Region.objects.none()
subjects = ExamSubject.objects.all().order_by("subject_code")
# ============= AJAX SEARCH ==============
if request.headers.get("x-requested-with") == "XMLHttpRequest":
query = request.GET.get("q", "").strip().lower()
results = []
for r in regions:
if query in r.name.lower():
results.append({
"id": r.id,
"name": r.name,
"district_count": r.districts.count(), # ✅ FIXED HERE
})
return JsonResponse({"regions": results})
# Normal page load
return render(request, "exams/reports/regionwise.html", {
"regions": regions,
"subjects": subjects,
"years": years,
})
@login_required
def region_ranking(request, region_id):
year = int(request.GET.get("year", 2024))
qs = SchoolYearSummary.objects.filter(
school__region_id=region_id,
year=year
).values(
"school__name",
"school__school_code"
).annotate(
avg_mark=Avg("avg_numeric"),
candidates=Sum("candidates")
).order_by("-avg_mark")
region = Region.objects.get(id=region_id)
return render(request, "exams/region_ranking.html", {
"region": region,
"year": year,
"ranking": qs
})
# -----------------------
# Helper: average -> band letter
# -----------------------
def avg_to_band(avg_value):
"""
Map numeric average (0-300) to band A-E according to user's mapping:
0-60 -> E
61-120 -> D
121-180 -> C
181-240 -> B
241-300 -> A
"""
try:
v = float(avg_value or 0)
except Exception:
return "-"
if v <= 60:
return "E"
if v <= 120:
return "D"
if v <= 180:
return "C"
if v <= 240:
return "B"
return "A"
# -----------------------
# Region summary HTML (you likely already have)
# -----------------------
@login_required
def region_summary(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
if not region_id or not year:
return render(request, "exams/reports/region_summary.html", {"error": "Please select region and year."})
region = get_object_or_404(Region, pk=region_id)
year = int(year)
cand_qs = ExamCandidate.objects.filter(year=year, school__region_id=region.id)
# per-district aggregations
district_stats = (
cand_qs.values("school__district__id", "school__district__name")
.annotate(
total=Count("id"),
pass_count=Count("id", filter=Q(status__iexact="PASS")),
fail_count=Count("id", filter=Q(status__iexact="FAIL")),
male=Count("id", filter=Q(sex__iexact="M")),
female=Count("id", filter=Q(sex__iexact="F")),
male_pass=Count("id", filter=Q(sex__iexact="M") & Q(status__iexact="PASS")),
female_pass=Count("id", filter=Q(sex__iexact="F") & Q(status__iexact="PASS")),
)
.order_by("school__district__name")
)
districts = []
totals = {"total":0, "pass":0, "fail":0, "male":0, "female":0}
for row in district_stats:
total = row["total"] or 0
pc = row["pass_count"] or 0
fc = row["fail_count"] or 0
male = row["male"] or 0
female = row["female"] or 0
male_pass = row.get("male_pass") or 0
female_pass = row.get("female_pass") or 0
pass_pct = round((pc / total * 100), 1) if total else 0
districts.append({
"district_name": row.get("school__district__name") or "Unknown",
"total": total,
"pass": pc,
"fail": fc,
"pass_pct": pass_pct,
"male": male,
"female": female,
"male_pass": male_pass,
"female_pass": female_pass,
})
totals["total"] += total
totals["pass"] += pc
totals["fail"] += fc
totals["male"] += male
totals["female"] += female
totals["pass_pct"] = round((totals["pass"] / totals["total"] * 100), 1) if totals["total"] else 0
# header numbers
registered = cand_qs.count()
pass_fail = cand_qs.filter(status__in=["PASS","pass","Pass","FAIL","fail","Fail"]).count()
other_status = registered - pass_fail
# region average — use SchoolYearSummary average of avg_numeric (if available for this year)
school_summary_qs = SchoolYearSummary.objects.filter(year=year, school__region_id=region.id)
total_schools = school_summary_qs.values_list("school_id", flat=True).distinct().count()
region_avg = school_summary_qs.aggregate(avg=Avg("avg_numeric"))["avg"] or 0
region_avg_rounded = round(region_avg, 4)
region_avg_band = avg_to_band(region_avg_rounded)
context = {
"region": region,
"year": year,
"districts": districts,
"totals": totals,
"registered": registered,
"pass_fail": pass_fail,
"other_status": other_status,
"region_avg": region_avg_rounded,
"region_avg_band": region_avg_band,
"total_schools": total_schools,
}
return render(request, "exams/reports/region_summary.html", context)
# View: redesigned HTML preview
# -----------------------
# View: PDF export using xhtml2pdf
def _on_page(canvas, doc):
# simple page number footer at bottom center
canvas.saveState()
canvas.setFont("Helvetica", 9)
page_no = f"Page {doc.page}"
canvas.drawString(30, 20, page_no)
canvas.restoreState()
@login_required
def region_summary_pdf(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
if not region_id or not year:
return HttpResponse("Missing parameters", status=400)
context = build_region_summary_context(region_id, year)
# PDF response
response = HttpResponse(content_type="application/pdf")
response["Content-Disposition"] = f'inline; filename="Region_{year}.pdf"'
# Use SimpleDocTemplate with small margins
doc = SimpleDocTemplate(response, pagesize=A4,
leftMargin=20, rightMargin=20,
topMargin=20, bottomMargin=30)
story = []
# Header (single table flowable)
hdr = pdf_header_block_simple(context["region"], year)
story.append(hdr)
story.append(Spacer(1, 8))
# Students block
story.append(pdf_students_block(context.get("totals", {})))
story.append(Spacer(1, 10))
# Grades summary
story.append(pdf_grades_block(context.get("grades", {}), context.get("totals", {})))
story.append(Spacer(1, 12))
# Ownership blocks (each one spaced)
for tbl in pdf_ownership_block(context.get("ownership", [])):
story.append(tbl)
story.append(Spacer(1, 12))
# District summary table last (if present)
if context.get("districts"):
story.append(pdf_district_summary_block(context.get("districts", []), context.get("districts_totals", {})))
story.append(Spacer(1, 8))
# Footer text (example)
footer_text = f"STD-VII : {year} - MKOA - {getattr(context['region'], 'name', '')}"
story.append(Spacer(1, 20))
story.append(pdf_footer_block(footer_text))
# Build
doc.build(story, onFirstPage=_on_page, onLaterPages=_on_page)
return response
@login_required
def region_summary_pdf_redesigned(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
if not region_id or not year:
return HttpResponse("Missing parameters", status=400)
year = int(year)
# 🔥 FIX: get the FULL context in one line
context = build_region_summary_context(region_id, year)
# Render HTML
html_string = render_to_string("exams/reports/region_summary_html.html", context)
# DOMPDF
response = HttpResponse(content_type='application/pdf')
response['Content-Disposition'] = f'attachment; filename="Region_{region_id}_{year}.pdf"'
from xhtml2pdf import pisa
pisa.CreatePDF(html_string, dest=response)
return response
# -----------------------
# Region Excel export using openpyxl
# -----------------------
@login_required
def region_summary_excel(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
if not region_id or not year:
return HttpResponse("Missing parameters", status=400)
# Get computed context
context = build_region_summary_context(region_id, year)
region = context["region"]
totals = context["totals"]
grades = context["grades"]
districts = context["districts"]
a_c_total = context["a_c_total"]
# Create workbook
wb = Workbook()
ws = wb.active
ws.title = "Region Summary"
# Styles
head_fill = PatternFill(start_color="DDDDDD", end_color="DDDDDD", fill_type="solid")
bold = Font(bold=True)
# Title
ws.merge_cells("A1:G1")
ws["A1"] = f"REGION SUMMARY – {region.name} – {year}"
ws["A1"].font = Font(bold=True, size=14)
ws["A1"].alignment = Alignment(horizontal="center")
ws.append([""])
# Helper percentage
def pct(n, d):
return round((n / d * 100), 1) if d else 0
# ------------------------------------------------------
# REGISTERED / SAT / ABSENT
# ------------------------------------------------------
ws.append(["Category", "Female", "Female %", "Male", "Male %", "Total", "Total %"])
for c in ws[3]:
c.font = bold
c.fill = head_fill
ws.append([
"Registered",
totals["reg_f"], pct(totals["reg_f"], totals["reg"]),
totals["reg_m"], pct(totals["reg_m"], totals["reg"]),
totals["reg"], 100
])
ws.append([
"With Results",
totals["sat_f"], pct(totals["sat_f"], totals["reg"]),
totals["sat_m"], pct(totals["sat_m"], totals["reg"]),
totals["sat"], pct(totals["sat"], totals["reg"]),
])
ws.append([
"Absent",
totals["abs_f"], pct(totals["abs_f"], totals["reg"]),
totals["abs_m"], pct(totals["abs_m"], totals["reg"]),
totals["abs"], pct(totals["abs"], totals["reg"]),
])
ws.append(["Average Score", totals["avg"], "", "", "", "", ""])
ws.append(["Grade", totals["grade"], "", "", "", "", ""])
ws.append(["Status", totals["status"], "", "", "", "", ""])
ws.append(["Total Schools", totals["school_count"], "", "", "", "", ""])
ws.append([""])
# ------------------------------------------------------
# GRADE DISTRIBUTION
# ------------------------------------------------------
ws.append(["GRADE DISTRIBUTION"])
ws["A12"].font = bold
ws.append(["Grade", "Female", "Female %", "Male", "Male %", "Total", "Total %"])
for c in ws[13]:
c.font = bold
c.fill = head_fill
grade_rows = [
("A", grades["af"], grades["am"], grades["a"]),
("B", grades["bf"], grades["bm"], grades["b"]),
("C", grades["cf"], grades["cm"], grades["c"]),
("D", grades["df"], grades["dm"], grades["d"]),
("E", grades["ef"], grades["em"], grades["e"]),
]
for g, f, m, t in grade_rows:
ws.append([g, f, pct(f, totals["sat"]), m, pct(m, totals["sat"]), t, pct(t, totals["sat"])])
ws.append(["PASS A–C", "", "", "", "", a_c_total, pct(a_c_total, totals["sat"])])
ws.append([""])
# ------------------------------------------------------
# DISTRICT SUMMARY
# ------------------------------------------------------
ws.append(["DISTRICT SUMMARY"])
ws["A20"].font = bold
ws.append(["District", "Total", "Pass", "Fail", "Pass %", "Male", "Female"])
for c in ws[21]:
c.font = bold
c.fill = head_fill
for d in districts:
ws.append([
d["district_name"],
d["total"],
d["pass"],
d["fail"],
d["pass_pct"],
d["male"],
d["female"],
])
# ------------------------------------------------------
# RETURN XLSX FILE
# ------------------------------------------------------
response = HttpResponse(
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
response["Content-Disposition"] = f'attachment; filename="Region_{region.id}_{year}.xlsx"'
wb.save(response)
return response
@login_required
def region_summary_html(request):
region = request.GET.get("region")
year = request.GET.get("year")
context = build_region_summary_context(region, year)
return render(request, "exams/reports/region_summary_html.html", context)
@login_required
def district_ranking_html(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
context = compute_district_ranking(region_id, year)
return render(
request,
"exams/reports/district_ranking_html.html",
{
"region": context["region"],
"year": year,
"districts": context["rows"], # <-- IMPORTANT
"totals": context["totals"],
}
)
@login_required
def district_ranking_pdf(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
context = compute_district_ranking(region_id, year)
region = context["region"]
region_name = region.name if hasattr(region, "name") else str(region)
# Fix tuple rows → dict rows
clean_rows = []
for r in context["rows"]:
if isinstance(r, dict):
clean_rows.append(r)
else:
# tuple → dict conversion (assumes standardized order)
clean_rows.append({
"district": r[1],
"total": r[2],
"pass": r[3],
"fail": r[4],
"pass_pct": r[5],
"avg_grade": r[6] if len(r) > 6 else "",
"status": r[7] if len(r) > 7 else "",
})
response = HttpResponse(content_type="application/pdf")
response["Content-Disposition"] = (
f"inline; filename=District_Rank_{region_name}_{year}.pdf"
)
doc = SimpleDocTemplate(response)
story = []
story.append(pdf_header_block_simple(region, year))
story.append(Spacer(1, 10))
# Use the cleaned rows
story.append(pdf_district_summary_block(context["rows"], context["totals"]))
doc.build(story)
return response
@login_required
def district_ranking_excel(request):
context = compute_district_ranking(
request.GET.get("region"),
request.GET.get("year")
)
region = context["region"]
districts = context["rows"]
totals = context["totals"]
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "District Ranking"
ws.append([
"District", "A-F", "A-M", "A-T",
"B-F", "B-M", "B-T",
"C-F", "C-M", "C-T",
"D-F", "D-M", "D-T",
"E-F", "E-M", "E-T",
"PASS F", "PASS M", "PASS T",
"%PASS", "Avg Score", "Total Std", "Schools"
])
for d in districts:
ws.append([
d["district"],
d["af"], d["am"], d["a"],
d["bf"], d["bm"], d["b"],
d["cf"], d["cm"], d["c"],
d["df"], d["dm"], d["d"],
d["ef"], d["em"], d["e"],
d["pass_f"], d["pass_m"], d["pass_t"],
d["pass_ac_pct"], d["avg_score"],
d["total_std"], d["total_schools"]
])
# ADD TOTAL ROW
ws.append([
"TOTAL",
totals["af"], totals["am"], totals["a"],
totals["bf"], totals["bm"], totals["b"],
totals["cf"], totals["cm"], totals["c"],
totals["df"], totals["dm"], totals["d"],
totals["ef"], totals["em"], totals["e"],
totals["pass_f"], totals["pass_m"], totals["pass_t"],
"-", totals["avg_score"],
totals["total_std"], totals["total_schools"]
])
response = HttpResponse(
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
response["Content-Disposition"] = "attachment; filename=district_ranking.xlsx"
wb.save(response)
return response
# --------------------------------------------------------------
# EVERYTHING-WARD
# --------------------------------------------------------------
@login_required
def ward_ranking_html(request):
region_id = request.GET.get("region")
year = int(request.GET.get("year", 2024))
region = Region.objects.get(id=region_id)
wards, totals = build_ward_rank_data(region_id, year)
return render(request, "exams/reports/ward_ranking_html.html", {
"region": region,
"year": year,
"wards": wards,
"totals": totals
})
@login_required
def ward_ranking_pdf(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
region = get_object_or_404(Region, pk=region_id)
wards, totals = build_ward_rank_data(region_id, year)
response = HttpResponse(content_type="application/pdf")
response["Content-Disposition"] = (
f'inline; filename="Ward_Ranking_{region.name}_{year}.pdf"'
)
doc = SimpleDocTemplate(
response,
pagesize=landscape(A4),
leftMargin=20,
rightMargin=20,
topMargin=20,
bottomMargin=20
)
story = []
story.append(pdf_ward_ranking_table(region, year, wards))
doc.build(story)
return response
@login_required
def ward_ranking_excel(request):
region_id = request.GET.get("region")
year = int(request.GET.get("year", 2024))
region = Region.objects.get(id=region_id)
wards, totals = build_ward_rank_data(region_id, year)
wb = Workbook()
ws = wb.active
ws.title = "Ward Ranking"
headers = [
"Rank","Ward",
"A(F)","A(M)","A(T)",
"B(F)","B(M)","B(T)",
"C(F)","C(M)","C(T)",
"D(F)","D(M)","D(T)",
"E(F)","E(M)","E(T)",
"Total Std","Schools",
"Pass(F)","Pass(M)","Pass(T)",
"Avg Score"
]
ws.append(headers)
for w in wards:
ws.append([
w["rank"], w["name"],
w["af"], w["am"], w["a"],
w["bf"], w["bm"], w["b"],
w["cf"], w["cm"], w["c"],
w["df"], w["dm"], w["d"],
w["ef"], w["em"], w["e"],
w["total_std"],
w["total_schools"],
w["pass_f"], w["pass_m"], w["pass_t"],
w["avg_score"]
])
response = HttpResponse(content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response["Content-Disposition"] = f"attachment; filename=ward_ranking_{region.name}_{year}.xlsx"
wb.save(response)
return response
@login_required
def ward_ranking(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
district_id = request.GET.get("district") # optional: restrict to a district
page = int(request.GET.get("page", 1))
if not region_id or not year:
return render(request, "exams/error.html", {"message":"Missing region or year"})
region = get_object_or_404(Region, pk=region_id)
year = int(year)
base_qs = ExamCandidate.objects.filter(year=year, school__region_id=region.id)
ward_qs = Ward.objects.filter(district__region_id=region.id)
if district_id:
ward_qs = ward_qs.filter(district_id=district_id)
ranking_rows = []
for w in ward_qs:
w_qs = base_qs.filter(school__ward_id=w.id)
agg = compute_rank_aggregates(w_qs)
ranking_rows.append({
"ward": w,
"total": agg["total"],
"pass_count": agg["pass_count"],
"fail_count": agg["fail_count"],
"pass_pct": agg["pass_pct"],
"avg_score": agg["avg_score"],
"grades": agg["grades"],
})
ranking_rows.sort(key=lambda r: (r["pass_pct"], r["avg_score"], r["pass_count"]), reverse=True)
paginator = Paginator(ranking_rows, 50)
page_obj = paginator.get_page(page)
return render(request, "exams/reports/ward_ranking.html", {
"region": region,
"year": year,
"district_id": district_id,
"page_obj": page_obj,
})
@login_required
def school_ranking_html(request):
# expects region & year (district/ward optional)
region_id = request.GET.get("region")
year = request.GET.get("year")
district_id = request.GET.get("district")
ward_id = request.GET.get("ward")
if not region_id or not year:
return render(request, "exams/error.html", {"message":"Missing region or year"})
region = get_object_or_404(Region, pk=region_id)
schools, totals = build_school_rank_data(region_id, year, district_id, ward_id)
return render(request, "exams/reports/school_ranking_html.html", {
"region": region,
"year": int(year),
"schools": schools,
"totals": totals,
})
@login_required
def best_schools_pdf(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
if not region_id or not year:
return HttpResponse("Missing parameters", status=400)
# MUST load region object (pdf_header_block_simple expects an object not ID)
region = get_object_or_404(Region, pk=region_id)
# PDF response
response = HttpResponse(content_type="application/pdf")
response["Content-Disposition"] = f'inline; filename="Best_Schools_{region.name}_{year}.pdf"'
doc = SimpleDocTemplate(response)
story = []
# HEADER BLOCK
story.append(pdf_header_block_simple(region, year))
story.append(Spacer(1, 10))
# BEST & WORST TABLE BLOCKS (Top10, Bottom10, etc.)
blocks = pdf_best_worst_group(region_id, year)
for b in blocks:
story.append(b)
# Build final PDF
doc.build(story)
return response
@login_required
def school_ranking_excel(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
district_id = request.GET.get("district")
ward_id = request.GET.get("ward")
if not region_id or not year:
return HttpResponse("Missing parameters", status=400)
region = get_object_or_404(Region, pk=region_id)
schools, totals = build_school_rank_data(region_id, year, district_id, ward_id)
wb = Workbook()
ws = wb.active
ws.title = "School Ranking"
headers = [
"Rank","School Code","School Name",
"A(F)","A(M)","A(T)",
"B(F)","B(M)","B(T)",
"C(F)","C(M)","C(T)",
"D(F)","D(M)","D(T)",
"E(F)","E(M)","E(T)",
"Total Std","Pass F","Pass M","Pass T","% Pass","Avg Score"
]
ws.append(headers)
for s in schools:
ws.append([
s["rank"], s["school_code"], s["name"],
s["af"], s["am"], s["a"],
s["bf"], s["bm"], s["b"],
s["cf"], s["cm"], s["c"],
s["df"], s["dm"], s["d"],
s["ef"], s["em"], s["e"],
s["total_std"],
s["pass_f"], s["pass_m"], s["pass_t"],
s["pass_pct"],
s["avg_score"]
])
# totals row
ws.append([
"TOTAL", "", "",
totals["af"], totals["am"], totals["a"],
totals["bf"], totals["bm"], totals["b"],
totals["cf"], totals["cm"], totals["c"],
totals["df"], totals["dm"], totals["d"],
totals["ef"], totals["em"], totals["e"],
totals["total_std"],
totals["pass_f"], totals["pass_m"], totals["pass_t"],
"-", totals["avg_score"]
])
response = HttpResponse(
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
fname = f"school_ranking_{slugify(region.name)}_{year}.xlsx"
response["Content-Disposition"] = f'attachment; filename="{fname}"'
wb.save(response)
return response
@login_required
def school_ranking_pdf(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
district_id = request.GET.get("district") # optional
ward_id = request.GET.get("ward") # optional
page = int(request.GET.get("page", 1))
if not region_id or not year:
return render(request, "exams/error.html", {"message":"Missing region or year"})
region = get_object_or_404(Region, pk=region_id)
year = int(year)
base_qs = ExamCandidate.objects.filter(year=year, school__region_id=region.id)
# iterate schools with candidates OR use a join to distinct school ids
school_ids = base_qs.values_list("school_id", flat=True).distinct()
schools = School.objects.filter(id__in=school_ids)
if district_id:
schools = schools.filter(district_id=district_id)
if ward_id:
schools = schools.filter(ward_id=ward_id)
ranking_rows = []
for s in schools:
s_qs = base_qs.filter(school_id=s.id)
agg = compute_rank_aggregates(s_qs)
ranking_rows.append({
"school": s,
"total": agg["total"],
"pass_count": agg["pass_count"],
"fail_count": agg["fail_count"],
"pass_pct": agg["pass_pct"],
"avg_score": agg["avg_score"],
"grades": agg["grades"],
})
ranking_rows.sort(key=lambda r: (r["pass_pct"], r["avg_score"], r["pass_count"]), reverse=True)
paginator = Paginator(ranking_rows, 100)
page_obj = paginator.get_page(page)
return render(request, "exams/reports/school_ranking.html", {
"region": region,
"year": year,
"page_obj": page_obj,
})
@login_required
def best_schools_tables(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
if not region_id or not year:
return HttpResponse("Missing parameters", status=400)
region = get_object_or_404(Region, pk=region_id)
data = build_best_worst_tables(region_id, year)
return render(request, "exams/reports/best10_tables.html", {
"region": region,
"year": year,
**data
})
@login_required
def best_schools_pdf(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
if not region_id or not year:
return HttpResponse("Missing parameters", status=400)
# MUST load region object (pdf_header_block_simple expects an object not ID)
region = get_object_or_404(Region, pk=region_id)
# PDF response
response = HttpResponse(content_type="application/pdf")
response["Content-Disposition"] = f'inline; filename="Best_Schools_{region.name}_{year}.pdf"'
doc = SimpleDocTemplate(response)
story = []
# HEADER BLOCK
story.append(pdf_header_block_simple(region, year))
story.append(Spacer(1, 10))
# BEST & WORST TABLE BLOCKS (Top10, Bottom10, etc.)
blocks = pdf_best_worst_group(region_id, year)
for b in blocks:
story.append(b)
# Build final PDF
doc.build(story)
return response
@login_required
def best_schools_excel(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
region = get_object_or_404(Region, pk=region_id)
tables = build_best_worst_tables(region_id, year)
wb = Workbook()
def add_sheet(title, rows):
ws = wb.create_sheet(title)
headers = [
"CODE","NAME","DISTRICT","TOTAL",
"A","B","C","D","E",
"PASS A-C","% PASS","AVERAGE","AVG GRADE","STATUS"
]
ws.append(headers)
for r in rows:
ws.append([
r["school_code"], r["name"], r["district"], r["total"],
r["A"], r["B"], r["C"], r["D"], r["E"],
r["pass_ac"], r["pass_pct"],
r["avg"], r["avg_grade"], r["status"],
])
add_sheet("Top10", tables["top10"])
add_sheet("Bottom10", tables["bottom10"])
add_sheet("Top10_30plus", tables["top10_big"])
add_sheet("Bottom10_30plus", tables["bottom10_big"])
add_sheet("Top10_Gov", tables["top10_gov"])
# remove default empty sheet
if "Sheet" in wb.sheetnames:
wb.remove(wb["Sheet"])
response = HttpResponse(
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
response["Content-Disposition"] = f'attachment; filename="best10_{region.name}_{year}.xlsx"'
wb.save(response)
return response
# --------------------------------------------------------------
# SUBJECTS
# --------------------------------------------------------------
@login_required
def general_subject_summary(request):
region_id = request.GET.get("region")
year = int(request.GET.get("year"))
subject = request.GET.get("subject", "ALL")
region, summary = get_region_subject_summary(region_id, year, subject)
return render(request, "exams/reports/subject_summary.html", {
"summary": summary,
"subject": subject,
"year": year,
"region": region,
})
@login_required
def region_subject_summary_pdf(request):
region_id = request.GET.get("region")
year = int(request.GET.get("year"))
subject = request.GET.get("subject", "ALL")
region, summary = get_region_subject_summary(region_id, year, subject)
response = HttpResponse(content_type="application/pdf")
response["Content-Disposition"] = (
f'inline; filename="Subject_Summary_{region.name}_{year}.pdf"'
)
doc = SimpleDocTemplate(
response, pagesize=A4,
leftMargin=20, rightMargin=20,
topMargin=20, bottomMargin=30
)
story = []
styles = getSampleStyleSheet()
story.append(Paragraph(
f"SUBJECT SUMMARY – {year}
{region.name}",
styles["Title"]
))
story.append(Spacer(1, 12))
data = [[
"Subject", "Name", "Total",
"A", "B", "C", "D", "E",
"Pass A–C", "% Pass"
]]
for r in summary:
data.append([
r["subject_code"],
r["subject_name_sw"],
r["total_std"],
r["A"], r["B"], r["C"], r["D"], r["E"],
r["pass_ac"],
f'{r["pass_pct"]}%',
])
table = Table(data, repeatRows=1)
table.setStyle(TableStyle([
("GRID", (0,0), (-1,-1), 0.5, colors.black),
("BACKGROUND", (0,0), (-1,0), colors.lightgrey),
("FONTNAME", (0,0), (-1,0), "Helvetica-Bold"),
("ALIGN", (2,1), (-1,-1), "CENTER"),
]))
story.append(table)
doc.build(story)
return response
@login_required
def region_subject_summary_excel(request):
region_id = request.GET.get("region")
year = int(request.GET.get("year"))
subject = request.GET.get("subject", "ALL")
region, summary = get_region_subject_summary(region_id, year, subject)
wb = Workbook()
ws = wb.active
ws.title = "Subject Summary"
headers = [
"Subject", "Name", "Total",
"A", "B", "C", "D", "E",
"Pass A–C", "% Pass"
]
ws.append(headers)
for r in summary:
ws.append([
r["subject_code"],
r["subject_name_sw"],
r["total_std"],
r["A"], r["B"], r["C"], r["D"], r["E"],
r["pass_ac"],
r["pass_pct"],
])
response = HttpResponse(
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
response["Content-Disposition"] = (
f'attachment; filename="Subject_Summary_{region.name}_{year}.xlsx"'
)
wb.save(response)
return response
@login_required
def subject_summary_pdf(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
subject = request.GET.get("subject")
region = get_object_or_404(Region, pk=region_id)
# Build summary again (reuse logic)
response_html = general_subject_summary(request).content.decode()
response = HttpResponse(content_type="application/pdf")
response['Content-Disposition'] = (
f'attachment; filename="subject_summary_{region.name}_{subject}_{year}.pdf"'
)
pisa.CreatePDF(response_html, dest=response)
return response
import openpyxl
from openpyxl.styles import Font, Alignment
from django.http import HttpResponse
@login_required
def subject_summary_excel(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
subject = request.GET.get("subject")
region = get_object_or_404(Region, pk=region_id)
# Fetch summary
res = general_subject_summary(request)
context = res.context_data
summary = context["summary"]
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Subject Summary"
headers = ["SUBJECT", "TOTAL", "A", "B", "C", "D", "E", "PASS A-C", "% PASS"]
ws.append(headers)
for cell in ws[1]:
cell.font = Font(bold=True)
cell.alignment = Alignment(horizontal="center")
for row in summary:
pass_ac = (row["A"] or 0) + (row["B"] or 0) + (row["C"] or 0)
pct = round(pass_ac / row["total_std"] * 100, 2) if row["total_std"] else 0
ws.append([
row["subject_code"],
row["total_std"],
row["A"], row["B"], row["C"], row["D"], row["E"],
pass_ac,
pct
])
response = HttpResponse(
content=wb,
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
response["Content-Disposition"] = f'attachment; filename="subject_summary_{subject}_{year}.xlsx"'
wb.save(response)
return response
@login_required
def regional_subject_school_ranking(request):
region_id = request.GET.get("region")
year = int(request.GET.get("year"))
subject = request.GET.get("subject")
region = get_object_or_404(Region, pk=region_id)
# load all schools in region
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
).first()
if perf:
total_std = perf.total_std or 0
avg_subj = float(perf.subj_avg or 0)
# PASS A–C using subj_grade
pass_ac = perf.total_std if perf.subj_grade in ["A", "B", "C"] else 0
pass_pct = round((pass_ac / total_std * 100), 2) if total_std else 0
rows.append({
"school_code": s.school_code,
"school": s.name,
"district": s.district.name if s.district else "",
"total": total_std,
"pass_ac": pass_ac,
"pass_pct": pass_pct,
"avg": round(avg_subj, 4),
})
# SORT BY: 1) avg DESC, 2) pass_pct DESC
rows.sort(key=lambda r: (r["avg"], r["pass_pct"]), reverse=True)
# assign rank
for i, r in enumerate(rows, start=1):
r["rank"] = i
return render(request, "exams/reports/regional_subject_school_ranking.html", {
"rows": rows,
"region": region,
"subject": subject,
"year": year,
})
@login_required
def regional_subject_school_ranking_pdf(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
subject = request.GET.get("subject")
# reuse HTML view logic
html = regional_subject_school_ranking(request).content.decode()
response = HttpResponse(content_type="application/pdf")
filename = f"regional_subject_rank_{region_id}_{subject}_{year}.pdf"
response["Content-Disposition"] = f'attachment; filename="{filename}"'
pisa.CreatePDF(html, dest=response)
return response
@login_required
def regional_subject_school_ranking_excel(request):
region_id = request.GET.get("region")
year = request.GET.get("year")
subject = request.GET.get("subject")
region = get_object_or_404(Region, pk=region_id)
# Rebuild rows
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
).first()
if perf:
total = perf.total_std 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({
"code": s.school_code,
"name": s.name,
"district": s.district.name,
"total": total,
"pass_ac": pass_ac,
"pass_pct": pass_pct,
"avg": float(perf.subj_avg or 0),
})
rows.sort(key=lambda r: (r["avg"], r["pass_pct"]), reverse=True)
# build excel
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Regional Rank"
headers = ["Rank", "Code", "School", "District", "Total", "Pass A–C", "% Pass", "Average"]
ws.append(headers)
for i, r in enumerate(rows, start=1):
ws.append([
i, r["code"], r["name"], r["district"], r["total"],
r["pass_ac"], r["pass_pct"], r["avg"]
])
response = HttpResponse(
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
response["Content-Disposition"] = (
f'attachment; filename="reg_subject_rank_{subject}_{year}.xlsx"'
)
wb.save(response)
return response
def compute_trend(region_id, start_year, end_year):
print("DEBUG TREND INPUT:", region_id, start_year, end_year)
start_year = int(start_year)
end_year = int(end_year)
years = list(range(start_year, end_year + 1))
rows = []
prev_pct = None
for y in years:
print("\n===== DEBUG YEAR:", y, "=====")
qs = ExamCandidate.objects.filter(
year=y,
school__district__region_id=region_id
)
total = qs.filter(avg_grade__in=["A", "B", "C", "D", "E"]).count()
passed = qs.filter(avg_grade__in=["A", "B", "C"]).count()
pct = (passed / total * 100) if total else 0
pct = round(pct, 3)
# Determine remark + icon
if prev_pct is None:
remark = "START"
icon = "◎"
elif pct > prev_pct:
remark = "RISE"
icon = "▲"
elif pct < prev_pct:
remark = "DROP"
icon = "▼"
else:
remark = "SAME"
icon = "■"
prev_pct = pct
rows.append({
"year": y,
"total": total,
"passed": passed,
"pct": pct,
"remark": remark,
"icon": icon,
})
return rows
def compute_region_trend(region_id, start, end):
years = list(range(int(start), int(end) + 1))
trend = {}
for y in years:
qs = ExamCandidate.objects.filter(year=y, school__region_id=region_id)
total = qs.filter(avg_grade__in=["A", "B", "C", "D", "E"]).count()
passed = qs.filter(avg_grade__in=["A", "B", "C"]).count()
pct = round((passed / total * 100), 3) if total else 0
trend[y] = {
"total": total,
"passed": passed,
"pct": pct
}
# compute yearly remarks
remarks = {}
prev = None
for y in years:
if prev is None:
remarks[y] = {"text": "START", "icon": "—"}
else:
if trend[y]["pct"] > trend[prev]["pct"]:
remarks[y] = {"text": "RISE", "icon": "▲"}
elif trend[y]["pct"] < trend[prev]["pct"]:
remarks[y] = {"text": "DROP", "icon": "▼"}
else:
remarks[y] = {"text": "NO CHANGE", "icon": "—"}
prev = y
return years, trend, remarks
def region_trend_html(request):
region_id = request.GET.get("region")
start_year = request.GET.get("start")
end_year = request.GET.get("end")
print("DEBUG VIEW: region_id =", region_id)
print("DEBUG VIEW: start_year =", start_year)
print("DEBUG VIEW: end_year =", end_year)
# Prevent crash if missing params
if not region_id or not start_year or not end_year:
return HttpResponse("Missing parameters", status=400)
# Call compute_trend (with debug inside)
rows = compute_trend(region_id, start_year, end_year)
print("DEBUG VIEW: rows returned =", rows)
region = Region.objects.get(pk=region_id)
return render(request, "exams/reports/region_trend_html.html", {
"region": region,
"rows": rows,
"start": start_year,
"end": end_year,
"years": list(range(int(start_year), int(end_year) + 1)),
})
@login_required
def region_trend_pdf(request):
region_id = request.GET.get("region")
start_year = request.GET.get("start")
end_year = request.GET.get("end")
if not region_id or not start_year or not end_year:
return HttpResponse("Missing parameters", status=400)
region = Region.objects.get(pk=region_id)
rows = compute_trend(region_id, start_year, end_year)
years = list(range(int(start_year), int(end_year) + 1))
html = render_to_string("exams/reports/region_trend_pdf.html", {
"region": region,
"rows": rows,
"years": years,
"start": start_year,
"end": end_year,
})
response = HttpResponse(content_type="application/pdf")
filename = f"region_trend_{region.name}_{start_year}_{end_year}.pdf"
response["Content-Disposition"] = f'attachment; filename="{filename}"'
pisa_status = pisa.CreatePDF(html, dest=response)
return response
@login_required
def region_trend_excel(request):
region_id = request.GET.get("region")
start_year = request.GET.get("start")
end_year = request.GET.get("end")
if not region_id or not start_year or not end_year:
return HttpResponse("Missing parameters", status=400)
region = Region.objects.get(pk=region_id)
rows = compute_trend(region_id, start_year, end_year)
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Region Trend"
center = Alignment(horizontal="center", vertical="center")
# Title
ws.merge_cells("A1:E1")
ws["A1"] = f"PASS TREND {start_year} - {end_year} | REGION: {region.name}"
ws["A1"].font = Font(size=14, bold=True)
ws["A1"].alignment = center
# Header row (Row 2)
ws.append(["ITEM"] + [r["year"] for r in rows])
# Data rows
ws.append(["TOTAL STUDENTS"] + [r["total"] for r in rows])
ws.append(["NO. OF PASS"] + [r["passed"] for r in rows])
ws.append(["% PASS"] + [r["pct"] for r in rows])
ws.append(["REMARK"] + [r["remark"] + " " + r["icon"] for r in rows])
# Format all cells center aligned
for row in ws.iter_rows():
for cell in row:
cell.alignment = center
# SAFE AUTO-WIDTH (no MergedCell crash)
for col_idx, col in enumerate(ws.columns, start=1):
max_length = 0
for cell in col:
if cell.value:
try:
length = len(str(cell.value))
if length > max_length:
max_length = length
except:
pass
ws.column_dimensions[get_column_letter(col_idx)].width = max_length + 2
# Excel output
response = HttpResponse(
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
filename = f"region_trend_{region.name}_{start_year}_{end_year}.xlsx"
response["Content-Disposition"] = f'attachment; filename=\"{filename}\"'
wb.save(response)
return response
def compute_district_trend(region_id, start_year, end_year):
from exams.models import District, ExamCandidate
start_year = int(start_year)
end_year = int(end_year)
years = list(range(start_year, end_year + 1))
districts = District.objects.filter(region_id=region_id).order_by("name")
result = []
for d in districts:
prev_pct = None
year_rows = []
for y in years:
qs = ExamCandidate.objects.filter(
year=y,
school__district_id=d.id
)
total = qs.count()
passed = qs.filter(avg_grade__in=["A", "B", "C"]).count()
pct = round((passed / total * 100), 3) if total else 0
# Remark + icon
if prev_pct is None:
remark = "START"
icon = "◎"
elif pct > prev_pct:
remark = "RISE"
icon = "▲"
elif pct < prev_pct:
remark = "DROP"
icon = "▼"
else:
remark = "SAME"
icon = "■"
prev_pct = pct
year_rows.append({
"year": y,
"total": total,
"passed": passed,
"pct": pct,
"remark": remark,
"icon": icon,
})
result.append({
"district": d.name,
"years": year_rows
})
return years, result
def district_trend_html(request):
region_id = request.GET.get("region")
start = request.GET.get("start")
end = request.GET.get("end")
if not region_id or not start or not end:
return HttpResponse("Missing parameters", status=400)
years, rows = compute_district_trend(region_id, start, end)
region = Region.objects.get(id=region_id)
return render(request, "exams/reports/district_trend_html.html", {
"region": region,
"years": years,
"rows": rows
})
def district_trend_pdf(request):
region_id = request.GET.get("region")
start = request.GET.get("start")
end = request.GET.get("end")
years, rows = compute_district_trend(region_id, start, end)
region = Region.objects.get(id=region_id)
html = render_to_string("exams/reports/district_trend_pdf.html", {
"region": region,
"years": years,
"rows": rows
})
response = HttpResponse(content_type="application/pdf")
response["Content-Disposition"] = "attachment; filename=district_trend.pdf"
pisa.CreatePDF(html, dest=response)
return response
def district_trend_excel(request):
region_id = request.GET.get("region")
start = request.GET.get("start")
end = request.GET.get("end")
years, rows = compute_district_trend(region_id, start, end)
region = Region.objects.get(id=region_id)
wb = Workbook()
ws = wb.active
ws.title = "District Trend"
# Title
ws.append([f"DISTRICT PASS TREND {start} - {end} ({region.name})"])
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(years)*4 + 1)
header = ["DISTRICT"]
for y in years:
header += [f"{y} TOTAL", f"{y} PASS", f"{y} %PASS", f"{y} REMARK"]
ws.append(header)
for row in rows:
line = [row["district"]]
for yr in row["years"]:
line += [
yr["total"],
yr["passed"],
yr["pct"],
f"{yr['remark']} {yr['icon']}"
]
ws.append(line)
# Autofit columns
for col in ws.columns:
max_len = 0
col_letter = get_column_letter(col[0].column)
for cell in col:
try:
val = str(cell.value)
if len(val) > max_len:
max_len = len(val)
except:
pass
ws.column_dimensions[col_letter].width = max_len + 3
# Return file
response = HttpResponse(
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
response["Content-Disposition"] = "attachment; filename=district_trend.xlsx"
wb.save(response)
return response
# --------------------------------------------------------------
# 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)
@login_required
def bulk_uploads_page(request):
stats = {
"exam_candidate": ExamCandidate.objects.count(),
"student_subject_mark": StudentSubjectMark.objects.count(),
"school_year_summary": SchoolYearSummary.objects.count(),
"school_subject_performance": SchoolSubjectPerformance.objects.count(),
}
return render(request, "exams/bulk_uploads.html", {
"stats": stats
})
@login_required
def bulk_upload_handler(request, target):
if request.method != "POST":
return redirect("exams:bulk_uploads")
# ✅ FIXED NAME
csv_file = request.FILES.get("csv_file")
if not csv_file:
messages.error(request, "No CSV file selected.")
return redirect("exams:bulk_uploads")
with tempfile.NamedTemporaryFile(delete=False, suffix=".csv") as tmp:
for chunk in csv_file.chunks():
tmp.write(chunk)
file_path = tmp.name
TABLES = {
"exam_candidate": {
"table": "exam_candidate",
"columns": "(school_exam_no, cand_exam_no, sex, year, avg_grade)"
},
"student_subject_mark": {
"table": "student_subject_mark",
"columns": "(school_code, year, subject_code, cand_exam_no, marks, grade)"
},
"school_year_summary": {
"table": "school_year_summary",
"columns": "(school_code, year, total_std, avg_numeric, avg_grade, status)"
},
"school_subject_performance": {
"table": "school_subject_performance",
"columns": "(school_code, year, subject_code, total_std, subj_avg, subj_grade, subj_status)"
}
}
if target not in TABLES:
messages.error(request, "Invalid upload target.")
return redirect("exams:bulk_uploads")
cfg = TABLES[target]
try:
with connection.cursor() as cursor:
cursor.execute("SET GLOBAL local_infile = 1;")
cursor.execute(f"""
LOAD DATA LOCAL INFILE %s
INTO TABLE {cfg['table']}
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\\n'
IGNORE 1 LINES
{cfg['columns']}
""", [file_path])
messages.success(request, f"{target.replace('_',' ').title()} uploaded successfully.")
except Exception as e:
messages.error(request, f"Upload failed: {e}")
finally:
os.remove(file_path)
return redirect("exams:bulk_uploads")
@login_required
@require_POST
def bulk_upload_delete(request, target):
TABLE_MAP = {
"exam_candidate": "tbl_exam_candidate",
"student_subject_mark": "tbl_student_subject_mark",
"school_subject_performance": "tbl_school_subject_performance",
"school_year_summary": "tbl_school_year_summary",
}
if target not in TABLE_MAP:
messages.error(request, "Invalid delete target.")
return redirect("exams:bulk_uploads")
table = TABLE_MAP[target]
with connection.cursor() as cursor:
cursor.execute(f"TRUNCATE TABLE {table}")
messages.success(
request,
f"All records deleted from {target.upper()}."
)
return redirect("exams:bulk_uploads")
@login_required
def bulk_upload_sample(request, target):
SAMPLES = {
"exam_candidate": [
["year","school_exam_no","cand_exam_no","sex","avg_grade"]
],
"student_subject_mark": [
["year","school_code","cand_exam_no","subject_code","mark"]
],
"school_subject_performance": [
["school_code","year","subject_code","total_std","subj_avg","subj_grade","subj_status"]
],
"school_year_summary": [
["school_code","year","avg_numeric","grade","status"]
],
}
if target not in SAMPLES:
return HttpResponse("Invalid sample", status=400)
response = HttpResponse(content_type="text/csv")
response["Content-Disposition"] = f"attachment; filename={target}_sample.csv"
writer = csv.writer(response)
for row in SAMPLES[target]:
writer.writerow(row)
return response
@login_required
def district_subject_rank(request):
region_id = request.GET.get("region")
year = int(request.GET.get("year"))
subject = request.GET.get("subject")
context = get_district_subject_ranking(region_id, year, subject)
return render(
request,
"exams/reports/district_subject_rank.html",
context
)
@login_required
def district_subject_rank_pdf(request):
region_id = request.GET.get("region")
year = int(request.GET.get("year"))
subject = request.GET.get("subject")
ctx = get_district_subject_ranking(region_id, year, subject)
response = HttpResponse(content_type="application/pdf")
response["Content-Disposition"] = (
f'inline; filename="District_Subject_Rank_{ctx["region"].name}_{year}.pdf"'
)
doc = SimpleDocTemplate(
response,
pagesize=A4,
leftMargin=20,
rightMargin=20,
topMargin=20,
bottomMargin=30
)
styles = getSampleStyleSheet()
story = []
story.append(Paragraph(
f"DISTRICT SUBJECT RANKING – {ctx['subject_name_sw']} ({year})
{ctx['region'].name}",
styles["Title"]
))
story.append(Spacer(1, 12))
data = [[
"Rank", "District",
"Total Std", "Pass A–C", "% Pass", "Average"
]]
for r in ctx["rows"]:
data.append([
r["rank"],
r["district"],
r["total"],
r["pass_ac"],
f'{r["pass_pct"]}%',
r["avg"],
])
table = Table(data, repeatRows=1)
table.setStyle(TableStyle([
("GRID", (0,0), (-1,-1), 0.5, colors.black),
("BACKGROUND", (0,0), (-1,0), colors.lightgrey),
("FONTNAME", (0,0), (-1,0), "Helvetica-Bold"),
("ALIGN", (2,1), (-1,-1), "CENTER"),
]))
story.append(table)
doc.build(story)
return response
@login_required
def district_subject_rank_excel(request):
region_id = request.GET.get("region")
year = int(request.GET.get("year"))
subject = request.GET.get("subject")
ctx = get_district_subject_ranking(region_id, year, subject)
wb = Workbook()
ws = wb.active
ws.title = "District Subject Rank"
ws.append([
"Rank", "District",
"Total Std", "Pass A–C", "% Pass", "Average"
])
for r in ctx["rows"]:
ws.append([
r["rank"],
r["district"],
r["total"],
r["pass_ac"],
r["pass_pct"],
r["avg"],
])
response = HttpResponse(
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
response["Content-Disposition"] = (
f'attachment; filename="District_Subject_Rank_{ctx["region"].name}_{year}.xlsx"'
)
wb.save(response)
return response