Module 8 – Reports: Date, Range, Month, Semester

Create SQL queries and FastAPI endpoints to produce daily, date-range, monthly and semester reports for attendance.

1. Daily Report — SQL

-- Daily attendance summary per subject
SELECT s.subject_id, sub.code AS subject_code, sub.name AS subject_name,
  COUNT(*) FILTER (WHERE a.status='present') AS present_count,
  COUNT(*) FILTER (WHERE a.status='absent') AS absent_count,
  COUNT(*) AS total_taken
FROM attendance a
JOIN subjects sub ON sub.id = a.subject_id
JOIN semesters s ON s.id = sub.semester_id
WHERE a.date = '2025-08-10'
GROUP BY s.subject_id, sub.code, sub.name;

2. Date Range Report — SQL

-- Attendance percent per student in a date range
SELECT st.student_code, st.name,
  (COUNT(*) FILTER (WHERE a.status='present')::float / NULLIF(COUNT(*),0) * 100) AS attendance_pct
FROM attendance a
JOIN students st ON st.id = a.student_id
WHERE a.date BETWEEN '2025-08-01' AND '2025-08-31' AND st.semester = 3
GROUP BY st.id, st.student_code, st.name
ORDER BY attendance_pct DESC;

3. Monthly Report — FastAPI Endpoint

# file: app/reports.py
from fastapi import APIRouter
from databases import Database
from fastapi import Depends

router = APIRouter()

@router.get('/reports/monthly')
async def monthly_report(year: int, month: int, semester: int, db: Database = Depends(get_db)):
    start = f"{year}-{month:02d}-01"
    # naive end-of-month, use calendar for real app
    end = f"{year}-{month:02d}-31"
    query = """
    SELECT st.student_code, st.name,
      (COUNT(*) FILTER (WHERE a.status='present')::float / NULLIF(COUNT(*),0) * 100) AS attendance_pct
    FROM attendance a
    JOIN students st ON st.id = a.student_id
    WHERE a.date BETWEEN :start AND :end AND st.semester = :semester
    GROUP BY st.id, st.student_code, st.name
    ORDER BY attendance_pct DESC;
    """
    rows = await db.fetch_all(query, values={"start": start, "end": end, "semester": semester})
    return rows

4. Semester-wise Report (Aggregate)

-- Average attendance % across all students in a semester
SELECT st.course_id, st.semester,
  AVG(pct) AS avg_attendance_pct
FROM (
  SELECT student_id, (COUNT(*) FILTER (WHERE status='present')::float / NULLIF(COUNT(*),0) * 100) AS pct
  FROM attendance
  GROUP BY student_id
) sub
JOIN students st ON st.id = sub.student_id
WHERE st.semester = 3
GROUP BY st.course_id, st.semester;

5. Export to CSV: Endpoint

# file: app/export.py
from fastapi import APIRouter
from fastapi.responses import StreamingResponse
import csv, io

router = APIRouter()

@router.get('/reports/export/monthly')
async def export_monthly_csv(year: int, month: int, semester: int, db: Database = Depends(get_db)):
    rows = await monthly_query(db, year, month, semester)
    buf = io.StringIO()
    writer = csv.writer(buf)
    writer.writerow(['student_code','name','attendance_pct'])
    for r in rows:
        writer.writerow([r['student_code'], r['name'], r['attendance_pct']])
    buf.seek(0)
    return StreamingResponse(iter([buf.read()]), media_type='text/csv')

Notes & Tips

• For accurate end-of-month calculation use calendar.monthrange or SQL date functions.
• Large datasets: use pagination or async background jobs for heavy report generation.
• Cache frequently requested reports for performance.