app.py

import numpy as np
import pandas as pd
import sqlite3
from datetime import datetime, timedelta
import gradio as gr

# ---------------------------
# Sample Data Generation
# ---------------------------

students = [
    {"StudentID": "S001", "Name": "Alice"},
    {"StudentID": "S002", "Name": "Bob"},
    {"StudentID": "S003", "Name": "Charlie"},
    {"StudentID": "S004", "Name": "David"},
    {"StudentID": "S005", "Name": "Eva"}
]

subjects = ["Math", "Physics", "Chemistry"]
start_date = datetime(2023, 9, 1)
num_days = 30

attendance_records = []
for day in range(num_days):
    date = start_date + timedelta(days=day)
    for student in students:
        for subject in subjects:
            status = np.random.choice(["Present", "Absent"], p=[0.85, 0.15])
            attendance_records.append({
                "StudentID": student["StudentID"],
                "Name": student["Name"],
                "Subject": subject,
                "Date": date.strftime("%Y-%m-%d"),
                "Status": status
            })

attendance_df = pd.DataFrame(attendance_records)

# ---------------------------
# NumPy Attendance Calculation
# ---------------------------

def calculate_attendance_percentage(df, subjects):
    results = []
    for student in df['StudentID'].unique():
        student_data = df[df['StudentID'] == student]
        for subject in subjects:
            subject_data = student_data[student_data['Subject'] == subject]
            total_classes = len(subject_data)
            if total_classes == 0:
                percentage = np.nan
                present_classes = 0
            else:
                present_classes = np.sum(subject_data['Status'].values == 'Present')
                percentage = (present_classes / total_classes) * 100
            results.append({
                'StudentID': student,
                'Name': student_data['Name'].iloc[0],
                'Subject': subject,
                'TotalClasses': total_classes,
                'PresentClasses': int(present_classes),
                'Attendance%': round(percentage, 2) if not np.isnan(percentage) else percentage
            })
    return pd.DataFrame(results)

attendance_summary = calculate_attendance_percentage(attendance_df, subjects)

# ---------------------------
# Pandas Summary Reports
# ---------------------------

avg_attendance = attendance_summary.groupby(['StudentID', 'Name'])['Attendance%'].mean().reset_index()
avg_attendance.rename(columns={'Attendance%': 'AverageAttendance%'}, inplace=True)

subject_avg = attendance_summary.groupby('Subject')['Attendance%'].mean().reset_index()
subject_avg.rename(columns={'Attendance%': 'SubjectAverage%'}, inplace=True)

threshold = 75.0
low_attendance = avg_attendance[avg_attendance['AverageAttendance%'] < threshold].reset_index(drop=True)

# ---------------------------
# SQLite Database Storage
# ---------------------------

conn = sqlite3.connect('attendance.db')
attendance_df.to_sql('Attendance', conn, index=False, if_exists='replace')

query = '''
SELECT StudentID, Name, Subject,
       COUNT(*) AS TotalClasses,
       SUM(CASE WHEN Status='Present' THEN 1 ELSE 0 END) AS PresentClasses,
       ROUND(SUM(CASE WHEN Status='Present' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS AttendancePercentage
FROM Attendance
GROUP BY StudentID, Name, Subject
HAVING AttendancePercentage < 75;
'''

low_attendance_sql = pd.read_sql_query(query, conn)
conn.commit()
conn.close()

# ---------------------------
# Gradio UI
# ---------------------------

def get_reports():
    return (
        attendance_summary,
        avg_attendance,
        subject_avg,
        low_attendance,
        low_attendance_sql
    )

with gr.Blocks() as demo:
    gr.Markdown("# 📊 Attendance Tracker")

    btn = gr.Button("Generate Reports")

    out1 = gr.Dataframe(label="Attendance Summary (Student-Subject wise)")
    out2 = gr.Dataframe(label="Average Attendance per Student")
    out3 = gr.Dataframe(label="Subject-wise Average Attendance")
    out4 = gr.Dataframe(label=f"Students below {threshold}% (Calculated)")
    out5 = gr.Dataframe(label=f"Students below {threshold}% (From SQLite Query)")

    btn.click(fn=get_reports, outputs=[out1, out2, out3, out4, out5])

if __name__ == "__main__":
    demo.launch(server_name="0.0.0.0", server_port=7860)