# pages/generate_reports.py import streamlit as st import pandas as pd import io import zipfile from modules.database import Session, Student, AcademicTerm, ReportDesign, ENGINE, log_audit from modules.pdf_generator import generate_pdf_report from modules.grading import get_grade def show(): st.header("📄 Generate Student Reports") session = Session() students = pd.read_sql("SELECT * FROM students ORDER BY class_name, name", ENGINE) terms = pd.read_sql("SELECT * FROM academic_terms ORDER BY year DESC, term_number DESC", ENGINE) if students.empty or terms.empty: st.warning("Need students and terms to generate reports") session.close() st.stop() design = session.query(ReportDesign).first() st.subheader("Select Report Generation Options") report_mode = st.radio("Generate reports for:", ["Individual Student", "Whole Class"], horizontal=True) selected_term = st.selectbox("Select Term", terms['term_name'].tolist()) term_data = terms[terms['term_name'] == selected_term].iloc[0] if report_mode == "Individual Student": selected_student = st.selectbox("Select Student", students['name'].tolist()) student_data = students[students['name'] == selected_student].iloc[0] # Get marks marks_query = f""" SELECT m.*, u.name as teacher_name FROM marks m JOIN users u ON m.submitted_by = u.id WHERE m.student_id = {student_data['id']} AND m.term_id = {term_data['id']} ORDER BY m.subject """ marks = pd.read_sql(marks_query, ENGINE) # Get behavior data behavior_query = f""" SELECT punctuality, attendance, manners, general_behavior, organisational_skills, adherence_to_uniform, leadership_skills, commitment_to_school, cooperation_with_peers, cooperation_with_staff, participation_in_lessons, completion_of_homework FROM classroom_behavior WHERE student_id = {student_data['id']} AND term_id = {term_data['id']} """ behavior_result = pd.read_sql(behavior_query, ENGINE) behavior_data = None if not behavior_result.empty: behavior_data = behavior_result.iloc[0].to_dict() # Get decision data (only for term 3) decision_data = None if term_data['term_number'] == 3: decision_query = f""" SELECT decision, notes FROM student_decisions WHERE student_id = {student_data['id']} AND term_id = {term_data['id']} """ decision_result = pd.read_sql(decision_query, ENGINE) if not decision_result.empty: decision_data = decision_result.iloc[0].to_dict() if not marks.empty: st.subheader(f"Results Preview for {selected_student}") display_df = marks[[ 'subject', 'coursework_out_of_20', 'midterm_out_of_20', 'endterm_out_of_60', 'total', 'grade', 'comment', 'teacher_name' ]].copy() display_df.columns = ['Subject', 'CW/20', 'MOT/20', 'EOT/60', 'Total', 'Grade', 'Comment', 'Teacher'] st.dataframe(display_df, use_container_width=True) overall_avg = marks['total'].mean() overall_grade = get_grade(overall_avg) avg_comments = { "A*": "Outstanding Performance", "A": "Excellent Performance", "B": "Good Performance", "C": "Satisfactory Performance", "D": "Needs Improvement", "E": "Poor Performance", "U": "Unsatisfactory Performance" } avg_comment = avg_comments.get(overall_grade, "") col3, col4 = st.columns(2) with col3: st.metric("Overall Average", f"{overall_avg:.0f}/100") with col4: st.metric("Overall Grade", f"{overall_grade} - {avg_comment}") if term_data['term_number'] == 3 and decision_data: st.metric("Decision", decision_data.get('decision', 'Pending')) if st.button("📄 Generate PDF Report", use_container_width=True): try: pdf_data = generate_pdf_report(student_data, term_data, marks, design, behavior_data, decision_data) st.download_button( "⬇️ Download PDF Report", pdf_data, f"{selected_student}_{selected_term}_report.pdf", "application/pdf", use_container_width=True ) log_audit(session, st.session_state.user_id, "generate_report", f"Individual: {selected_student} - {selected_term}") st.success("✅ PDF generated successfully!") except Exception as e: st.error(f"❌ Error generating PDF: {str(e)}") else: st.info(f"❌ No marks found for {selected_student} in {selected_term}") st.info(f"Debug: Student ID = {student_data['id']}, Term ID = {term_data['id']}") any_marks = pd.read_sql(f"SELECT COUNT(*) as count FROM marks WHERE student_id = {student_data['id']}", ENGINE) st.info(f"This student has {any_marks['count'].iloc[0]} marks in total across all terms") else: # Whole Class classes = pd.read_sql("SELECT DISTINCT class_name FROM students ORDER BY class_name", ENGINE) selected_class = st.selectbox("Select Class", classes['class_name'].tolist()) class_students = students[students['class_name'] == selected_class] st.info(f"📊 Found {len(class_students)} students in {selected_class}") students_with_marks = [] students_without_marks = [] for _, student in class_students.iterrows(): marks = pd.read_sql(f""" SELECT m.*, u.name as teacher_name FROM marks m JOIN users u ON m.submitted_by = u.id WHERE m.student_id = {student['id']} AND m.term_id = {term_data['id']} """, ENGINE) if not marks.empty: students_with_marks.append(student['name']) else: students_without_marks.append(student['name']) col5, col6 = st.columns(2) with col5: st.success(f"✅ {len(students_with_marks)} students with marks") with col6: if students_without_marks: st.warning(f"⚠️ {len(students_without_marks)} students without marks") if students_without_marks: with st.expander("⚠️ Students without marks"): st.write(", ".join(students_without_marks)) if students_with_marks: if st.button(f"📄 Generate All Reports for {selected_class}", use_container_width=True): zip_buffer = io.BytesIO() with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file: for student_name in students_with_marks: student_data = class_students[class_students['name'] == student_name].iloc[0] marks = pd.read_sql(f""" SELECT m.*, u.name as teacher_name FROM marks m JOIN users u ON m.submitted_by = u.id WHERE m.student_id = {student_data['id']} AND m.term_id = {term_data['id']} """, ENGINE) behavior_query = f""" SELECT punctuality, attendance, manners, general_behavior, organisational_skills, adherence_to_uniform, leadership_skills, commitment_to_school, cooperation_with_peers, cooperation_with_staff, participation_in_lessons, completion_of_homework FROM classroom_behavior WHERE student_id = {student_data['id']} AND term_id = {term_data['id']} """ behavior_result = pd.read_sql(behavior_query, ENGINE) behavior_data = None if not behavior_result.empty: behavior_data = behavior_result.iloc[0].to_dict() decision_data = None if term_data['term_number'] == 3: decision_query = f""" SELECT decision, notes FROM student_decisions WHERE student_id = {student_data['id']} AND term_id = {term_data['id']} """ decision_result = pd.read_sql(decision_query, ENGINE) if not decision_result.empty: decision_data = decision_result.iloc[0].to_dict() if not marks.empty: pdf_data = generate_pdf_report(student_data, term_data, marks, design, behavior_data, decision_data) filename = f"{student_name}_{selected_term}_report.pdf" zip_file.writestr(filename, pdf_data) zip_buffer.seek(0) st.download_button( f"⬇️ Download All {len(students_with_marks)} Reports (ZIP)", zip_buffer.getvalue(), f"{selected_class}_{selected_term}_reports.zip", "application/zip", use_container_width=True ) log_audit(session, st.session_state.user_id, "generate_reports", f"Bulk: {selected_class} - {selected_term} - {len(students_with_marks)} reports") st.success(f"✅ Generated {len(students_with_marks)} reports!") else: st.warning("No students in this class have marks for the selected term") session.close()