Spaces:
Sleeping
Sleeping
File size: 3,618 Bytes
7064f87 b219018 7064f87 b219018 7064f87 b219018 7064f87 b219018 7064f87 c695862 b219018 5dbb5c6 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
import sqlite3
from datetime import datetime
def get_conn():
return sqlite3.connect("attendance.db")
def init_db():
conn = get_conn()
cursor = conn.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT,
email TEXT UNIQUE,
password TEXT,
role TEXT,
class_id TEXT,
device_id TEXT
)""")
cursor.execute("""CREATE TABLE IF NOT EXISTS sessions (
id INTEGER PRIMARY KEY,
class_id TEXT,
date TEXT,
is_open INTEGER
)""")
cursor.execute("""CREATE TABLE IF NOT EXISTS attendance_logs (
id INTEGER PRIMARY KEY,
session_id INTEGER,
student_email TEXT,
timestamp TEXT
)""")
conn.commit()
conn.close()
def add_student(name, email, password, class_id):
conn = get_conn()
cursor = conn.cursor()
try:
cursor.execute("INSERT INTO users (name, email, password, role, class_id) VALUES (?, ?, ?, 'student', ?)",
(name, email, password, class_id))
conn.commit()
except sqlite3.IntegrityError:
return "Email already exists."
finally:
conn.close()
return "Student added successfully."
def get_user_role(email):
conn = get_conn()
cursor = conn.cursor()
cursor.execute("SELECT role FROM users WHERE email=?", (email,))
row = cursor.fetchone()
conn.close()
return row[0] if row else None
def get_sessions(class_id):
conn = get_conn()
cursor = conn.cursor()
cursor.execute("SELECT id, date, is_open FROM sessions WHERE class_id=? ORDER BY date DESC", (class_id,))
rows = cursor.fetchall()
conn.close()
return rows
def toggle_session(class_id, is_open):
date = datetime.now().strftime("%Y-%m-%d %H:%M")
conn = get_conn()
cursor = conn.cursor()
cursor.execute("INSERT INTO sessions (class_id, date, is_open) VALUES (?, ?, ?)",
(class_id, date, int(is_open)))
conn.commit()
conn.close()
return f"Session {'opened' if is_open else 'closed'} for {class_id} on {date}"
def get_open_session(class_id):
conn = get_conn()
cursor = conn.cursor()
cursor.execute("SELECT id FROM sessions WHERE class_id=? AND is_open=1 ORDER BY date DESC LIMIT 1", (class_id,))
row = cursor.fetchone()
conn.close()
return row[0] if row else None
def has_marked_attendance(student_email, session_id):
conn = get_conn()
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM attendance_logs WHERE student_email=? AND session_id=?",
(student_email, session_id))
count = cursor.fetchone()[0]
conn.close()
return count > 0
def mark_attendance(student_email, session_id):
timestamp = datetime.now().strftime("%Y-%m-%d %H:%M")
conn = get_conn()
cursor = conn.cursor()
cursor.execute("INSERT INTO attendance_logs (student_email, session_id, timestamp) VALUES (?, ?, ?)",
(student_email, session_id, timestamp))
conn.commit()
conn.close()
return "Attendance marked successfully."
def get_attendance_summary(student_email):
conn = get_conn()
cursor = conn.cursor()
cursor.execute("""
SELECT s.date, s.class_id
FROM attendance_logs a
JOIN sessions s ON a.session_id = s.id
WHERE a.student_email=?
ORDER BY s.date DESC
LIMIT 10
""", (student_email,))
records = cursor.fetchall()
conn.close()
return "\n".join([f"{row[0]} - {row[1]}" for row in records]) if records else "No attendance records yet."
|