|
|
| import os
|
| import sqlite3
|
| import pandas as pd
|
| from db import DB_PATH
|
|
|
| PRESENCE_ONLINE_THRESHOLD_SEC = 180
|
|
|
| def _ensure_presence_table() -> None:
|
| os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
|
| with sqlite3.connect(DB_PATH) as conn:
|
| c = conn.cursor()
|
| c.execute(
|
| """
|
| CREATE TABLE IF NOT EXISTS presence (
|
| session_id TEXT PRIMARY KEY,
|
| user_email TEXT,
|
| user_name TEXT,
|
| role TEXT,
|
| page TEXT,
|
| first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
| last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
| )
|
| """
|
| )
|
| conn.commit()
|
|
|
| def update_presence(role: str, page: str, user_email: str | None, user_name: str | None) -> None:
|
| _ensure_presence_table()
|
| import streamlit as st
|
| from uuid import uuid4
|
| sid = st.session_state.get("session_id") or str(uuid4())
|
| st.session_state["session_id"] = sid
|
| with sqlite3.connect(DB_PATH) as conn:
|
| c = conn.cursor()
|
| c.execute(
|
| """
|
| INSERT INTO presence (session_id, user_email, user_name, role, page, first_seen, last_seen)
|
| VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
|
| ON CONFLICT(session_id)
|
| DO UPDATE SET user_email=excluded.user_email, user_name=excluded.user_name,
|
| role=excluded.role, page=excluded.page, last_seen=CURRENT_TIMESTAMP
|
| """,
|
| (sid, (user_email or None), (user_name or None), role, page)
|
| )
|
| conn.commit()
|
|
|
| def get_online_users(threshold_sec: int = PRESENCE_ONLINE_THRESHOLD_SEC) -> pd.DataFrame:
|
| _ensure_presence_table()
|
| with sqlite3.connect(DB_PATH) as conn:
|
| q = f"""
|
| SELECT session_id, user_email, user_name, role, page, first_seen, last_seen,
|
| CAST(strftime('%s', 'now') - strftime('%s', last_seen) AS INTEGER) AS idle_sec,
|
| CAST(strftime('%s', 'now') - strftime('%s', first_seen) AS INTEGER) AS session_sec
|
| FROM presence
|
| WHERE (strftime('%s', 'now') - strftime('%s', last_seen)) <= ?
|
| ORDER BY last_seen DESC
|
| """
|
| df = pd.read_sql_query(q, conn, params=(threshold_sec,))
|
| return df |
