from flask import Flask, request, jsonify, send_from_directory
from flask_cors import CORS
from openpyxl import load_workbook
from datetime import datetime
from collections import Counter
import uuid
import json
app = Flask(__name__)
CORS(app)
EXCEL_FILE = "RegistrationsTable.xlsx"
latest_registration_id = None
@app.route("/")
def home():
return send_from_directory(".", "register.html")
@app.route("/api/register", methods=["POST"])
def register():
data = request.json
workbook = load_workbook(EXCEL_FILE)
sheet = workbook.active
timestamp = datetime.now()
visit_month = timestamp.strftime("%Y-%m")
visit_week = timestamp.strftime("%Y-W%U")
sheet.append([
str(uuid.uuid4()),
timestamp.strftime("%Y-%m-%d %H:%M:%S"),
visit_week,
visit_month,
data.get("userId", ""),
data.get("name", ""),
data.get("userProfile", ""),
data.get("visitReason", ""),
data.get("space", ""),
"no"
])
workbook.save(EXCEL_FILE)
return jsonify({
"success": True,
"message": "Registration saved"
})
@app.route("/api/latest", methods=["GET"])
def latest_registration():
workbook = load_workbook(EXCEL_FILE)
sheet = workbook.active
for row in range(sheet.max_row, 1, -1):
notified = sheet.cell(row, 10).value
if notified == "no":
registration_id = sheet.cell(row, 1).value
name = sheet.cell(row, 6).value
profile = sheet.cell(row, 7).value
sheet.cell(row, 10).value = "yes"
workbook.save(EXCEL_FILE)
return jsonify({
"new_registration": True,
"registration_id": registration_id,
"name": name,
"profile": profile
})
return jsonify({
"new_registration": False
})
@app.route("/dashboard")
def dashboard():
workbook = load_workbook(EXCEL_FILE)
sheet = workbook.active
rows = list(sheet.iter_rows(min_row=2, values_only=True))
total_visits = len(rows)
visit_weeks = [row[2] for row in rows if row[2]]
visit_months = [row[3] for row in rows if row[3]]
profiles = [row[6] for row in rows if row[6]]
reasons = [row[7] for row in rows if row[7]]
current_week = max(visit_weeks) if visit_weeks else "No data"
current_month = max(visit_months) if visit_months else "No data"
weekly_visits = visit_weeks.count(current_week) if visit_weeks else 0
monthly_visits = visit_months.count(current_month) if visit_months else 0
main_reason = Counter(reasons).most_common(1)[0][0] if reasons else "No data"
week_counts = dict(sorted(Counter(visit_weeks).items()))
profile_counts = dict(Counter(profiles))
reason_counts = dict(Counter(reasons))
monthly_identifiers = [
row[4] if row[4] else row[5]
for row in rows
if row[3] == current_month and (row[4] or row[5])
]
visitor_counts = Counter(monthly_identifiers).most_common(3)
visitors_html = ""
medals = ["🥇", "🥈", "🥉"]
if visitor_counts:
for i, (visitor, count) in enumerate(visitor_counts):
visitors_html += f"""
{medals[i]} {visitor}
{count} visits
"""
else:
visitors_html = """
No visitor data available yet.
"""
return f"""
Space Dashboard
Weekly visits
{weekly_visits}
Monthly visits
{monthly_visits}
Main visit reason
{main_reason}
Total visits
{total_visits}
Visits by week
Visits by profile
Visit reasons
Top 3 frequent visitors this month
{visitors_html}
"""
if __name__ == "__main__":
app.run(host="0.0.0.0", port=5000, debug=True)