|
import gradio as gr |
|
import pandas as pd |
|
import sqlite3 |
|
import os |
|
import datetime |
|
import shutil |
|
|
|
DATA_DIR = "data" |
|
os.makedirs(DATA_DIR, exist_ok=True) |
|
|
|
DB_PATH = os.path.join(DATA_DIR, "teamup.db") |
|
BACKUP_DIR = os.path.join(DATA_DIR, "backup") |
|
os.makedirs(BACKUP_DIR, exist_ok=True) |
|
|
|
ADMIN_CODE = os.getenv("ADMIN_CODE", "") |
|
PAGE_SIZE = 12 |
|
|
|
LANGUAGES = ["English", "French", "Spanish", "German", "Portuguese", "Chinese", "Arabic", "Hindi"] |
|
ALL_COUNTRIES = sorted(set([ |
|
"Afghanistan", "Albania", "Algeria", "Andorra", "Angola", "Antigua and Barbuda", "Argentina", "Armenia", "Australia", "Austria", |
|
"Azerbaijan", "Bahamas", "Bahrain", "Bangladesh", "Barbados", "Belarus", "Belgium", "Belize", "Benin", "Bhutan", |
|
"Bolivia", "Bosnia and Herzegovina", "Botswana", "Brazil", "Brunei", "Bulgaria", "Burkina Faso", "Burundi", |
|
"Cabo Verde", "Cambodia", "Cameroon", "Canada", "Central African Republic", "Chad", "Chile", "China", "Colombia", |
|
"Comoros", "Congo (Congo-Brazzaville)", "Costa Rica", "Croatia", "Cuba", "Cyprus", "Czech Republic (Czechia)", |
|
"Democratic Republic of the Congo", "Denmark", "Djibouti", "Dominica", "Dominican Republic", "Ecuador", "Egypt", |
|
"El Salvador", "Equatorial Guinea", "Eritrea", "Estonia", "Eswatini", "Ethiopia", "Fiji", "Finland", |
|
"France", "Gabon", "Gambia", "Georgia", "Germany", "Ghana", "Greece", "Grenada", "Guatemala", "Guinea", "Guinea-Bissau", |
|
"Guyana", "Haiti", "Honduras", "Hungary", "Iceland", "India", "Indonesia", "Iran", "Iraq", "Ireland", "Israel", "Italy", |
|
"Jamaica", "Japan", "Jordan", "Kazakhstan", "Kenya", "Kiribati", "Kuwait", "Kyrgyzstan", "Laos", "Latvia", "Lebanon", |
|
"Lesotho", "Liberia", "Libya", "Liechtenstein", "Lithuania", "Luxembourg", "Madagascar", "Malawi", "Malaysia", "Maldives", |
|
"Mali", "Malta", "Marshall Islands", "Mauritania", "Mauritius", "Mexico", "Micronesia", "Moldova", "Monaco", "Mongolia", |
|
"Montenegro", "Morocco", "Mozambique", "Myanmar (formerly Burma)", "Namibia", "Nauru", "Nepal", "Netherlands", |
|
"New Zealand", "Nicaragua", "Niger", "Nigeria", "North Korea", "North Macedonia", "Norway", "Oman", "Pakistan", |
|
"Palau", "Palestine State", "Panama", "Papua New Guinea", "Paraguay", "Peru", "Philippines", "Poland", "Portugal", |
|
"Qatar", "Romania", "Russia", "Rwanda", "Saint Kitts and Nevis", "Saint Lucia", "Saint Vincent and the Grenadines", |
|
"Samoa", "San Marino", "Sao Tome and Principe", "Saudi Arabia", "Senegal", "Serbia", "Seychelles", "Sierra Leone", |
|
"Singapore", "Slovakia", "Slovenia", "Solomon Islands", "Somalia", "South Africa", "South Korea", "South Sudan", |
|
"Spain", "Sri Lanka", "Sudan", "Suriname", "Sweden", "Switzerland", "Syria", "Taiwan", "Tajikistan", "Tanzania", |
|
"Thailand", "Timor-Leste", "Togo", "Tonga", "Trinidad and Tobago", "Tunisia", "Turkey", "Turkmenistan", |
|
"Tuvalu", "Uganda", "Ukraine", "United Arab Emirates", "United Kingdom", "United States of America", "Uruguay", |
|
"Uzbekistan", "Vanuatu", "Vatican City", "Venezuela", "Vietnam", "Yemen", "Zambia", "Zimbabwe" |
|
])) |
|
|
|
def init_db(): |
|
with sqlite3.connect(DB_PATH) as conn: |
|
conn.execute(""" |
|
CREATE TABLE IF NOT EXISTS teamup ( |
|
id INTEGER PRIMARY KEY AUTOINCREMENT, |
|
name TEXT, |
|
discord TEXT UNIQUE, |
|
city TEXT, |
|
country TEXT, |
|
address TEXT, |
|
looking TEXT, |
|
onlinecheck TEXT, |
|
languages TEXT, |
|
laptop TEXT, |
|
robot TEXT, |
|
skills TEXT, |
|
describe3 TEXT, |
|
experience TEXT, |
|
idea TEXT, |
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
|
); |
|
""") |
|
init_db() |
|
|
|
def backup_db(): |
|
timestamp = datetime.datetime.now().strftime('%Y%m%d_%H%M%S') |
|
backup_file = os.path.join(BACKUP_DIR, f"teamup_backup_{timestamp}.db") |
|
shutil.copy(DB_PATH, backup_file) |
|
|
|
backups = sorted(os.listdir(BACKUP_DIR)) |
|
while len(backups) > 20: |
|
os.remove(os.path.join(BACKUP_DIR, backups.pop(0))) |
|
|
|
def submit_profile(name, discord, city, country, address, looking, onlinecheck, languages, laptop, robot, skills, describe3, experience, idea): |
|
if not discord or not city or not country or not laptop or not robot: |
|
return "❌ Please fill in all required fields." |
|
|
|
lang_str = ", ".join(languages) if isinstance(languages, list) else languages |
|
|
|
with sqlite3.connect(DB_PATH) as conn: |
|
conn.execute(""" |
|
INSERT INTO teamup (discord, name, city, country, address, looking, onlinecheck, languages, laptop, robot, skills, describe3, experience, idea) |
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) |
|
ON CONFLICT(discord) DO UPDATE SET |
|
name=excluded.name, |
|
city=excluded.city, |
|
country=excluded.country, |
|
address=excluded.address, |
|
looking=excluded.looking, |
|
onlinecheck=excluded.onlinecheck, |
|
languages=excluded.languages, |
|
laptop=excluded.laptop, |
|
robot=excluded.robot, |
|
skills=excluded.skills, |
|
describe3=excluded.describe3, |
|
experience=excluded.experience, |
|
idea=excluded.idea |
|
""", (discord, name, city.title(), country.title(), address, looking, onlinecheck, lang_str.lower(), laptop, robot, skills, describe3, experience, idea)) |
|
|
|
backup_db() |
|
return "✅ Profile saved!" |
|
|
|
def filter_by_fields(selected_country, selected_city, selected_language, page): |
|
try: |
|
page = int(page) |
|
except: |
|
page = 1 |
|
offset = (page - 1) * PAGE_SIZE |
|
|
|
query = "SELECT * FROM teamup" |
|
filters = [] |
|
params = [] |
|
|
|
if selected_country != "All": |
|
filters.append("country = ?") |
|
params.append(selected_country.title()) |
|
if selected_city != "All": |
|
filters.append("city = ?") |
|
params.append(selected_city.title()) |
|
if selected_language != "All": |
|
filters.append("languages LIKE ?") |
|
params.append(f"%{selected_language.lower()}%") |
|
|
|
if filters: |
|
query += " WHERE " + " AND ".join(filters) |
|
query += " ORDER BY created_at DESC LIMIT ? OFFSET ?" |
|
params.extend([PAGE_SIZE, offset]) |
|
|
|
with sqlite3.connect(DB_PATH) as conn: |
|
df = pd.read_sql_query(query, conn, params=params) |
|
total_query = "SELECT COUNT(*) FROM teamup" + (" WHERE " + " AND ".join(filters) if filters else "") |
|
total_count = pd.read_sql_query(total_query, conn, params=params[:-2] if filters else []).iloc[0, 0] |
|
|
|
if df.empty: |
|
return "<p>No participants match your filters.</p>", page, total_count |
|
|
|
html = "<h3 style='margin-top:20px;'>🔍 Find your matching Teammates & Register your team <a href='https://forms.gle/gJEMGD4CEA2emhD18' target='_blank'>here</a></h3>" |
|
html += "<table style='width:100%; border-collapse: collapse;'><tr>" |
|
headers = ["Discord", "Name", "City", "Country", "Looking", "How?", "Languages", "Laptop", "Robot", "Skills", "Description", "Experience", "Idea"] |
|
for col in headers: |
|
html += f"<th style='border:1px solid #ccc; padding:6px;'>{col}</th>" |
|
html += "</tr>" |
|
|
|
for _, row in df.iterrows(): |
|
html += "<tr>" |
|
for col in ["discord", "name", "city", "country", "looking", "onlinecheck", "languages", "laptop", "robot", "skills", "describe3", "experience", "idea"]: |
|
val = row[col] |
|
if col == "discord": |
|
val = f"<a href='https://discord.com/users/{val}' target='_blank'>{val}</a>" |
|
html += f"<td style='border:1px solid #eee; padding:6px;'>{val}</td>" |
|
html += "</tr>" |
|
|
|
html += "</table>" |
|
return html, page, total_count |
|
|
|
def update_city_filter(country): |
|
with sqlite3.connect(DB_PATH) as conn: |
|
if country == "All": |
|
result = conn.execute("SELECT DISTINCT city FROM teamup").fetchall() |
|
else: |
|
result = conn.execute("SELECT DISTINCT city FROM teamup WHERE country = ?", (country.title(),)).fetchall() |
|
cities = [r[0].title() for r in result if r[0]] |
|
return gr.update(choices=["All"] + sorted(cities), value="All") |
|
|
|
def delete_by_discord(discord, code): |
|
if code != ADMIN_CODE: |
|
return "❌ Invalid admin code." |
|
with sqlite3.connect(DB_PATH) as conn: |
|
conn.execute("DELETE FROM teamup WHERE lower(discord) = ?", (discord.lower(),)) |
|
return f"🗑️ Deleted user with Discord: {discord}" |
|
|
|
def download_csv(code): |
|
if code != ADMIN_CODE: |
|
raise gr.Error("❌ Invalid admin code.") |
|
with sqlite3.connect(DB_PATH) as conn: |
|
df = pd.read_sql_query("SELECT * FROM teamup", conn) |
|
csv_path = os.path.join(DATA_DIR, "teamup_export.csv") |
|
df.to_csv(csv_path, index=False) |
|
return csv_path |
|
|
|
def interface(): |
|
with gr.Blocks(css=".gr-dropdown { max-height: 100px; overflow-y: auto; font-size: 12px; }") as demo: |
|
gr.Markdown(""" |
|
# 🌍 LeRobot Worldwide Hackathon - Team-Up Dashboard |
|
|
|
Welcome to the Team-Up dashboard! Follow these steps: |
|
|
|
1. **Submit or update** your profile to help others find and contact you via Discord. Required fields are marked with `*`. |
|
2. **Use the filters** (country, city, language) to search for teammates from around the world. |
|
""") |
|
|
|
with gr.Row(): |
|
with gr.Column(): |
|
name = gr.Text(label="Name") |
|
discord = gr.Text(label="👤 Discord Username *") |
|
city = gr.Text(label="📍 City *") |
|
country = gr.Dropdown(label="🌍 Country *", choices=ALL_COUNTRIES, value="France") |
|
address = gr.Text(label="Address (optional)") |
|
looking = gr.Radio(["Yes", "No"], label="🔍 Looking for a team?") |
|
onlinecheck = gr.Radio(["Participate Online", "Join a Local Hackathon"], label="🚀 I will...") |
|
languages = gr.CheckboxGroup(choices=LANGUAGES, label="Languages Spoken *") |
|
laptop = gr.Text(label="💻 Laptop Setup *") |
|
robot = gr.Text(label="🤖 Robot Setup *") |
|
skills = gr.Text(label="🧠 Skills (comma separated)") |
|
describe3 = gr.Text(label="3 Words That Describe You") |
|
experience = gr.Dropdown(choices=["Beginner", "Intermediate", "Advanced"], label="Experience Level", value="Beginner") |
|
idea = gr.Textbox(label="Project Idea (optional)") |
|
submit_btn = gr.Button("Submit or Update ✅") |
|
status = gr.Textbox(label="", interactive=False) |
|
|
|
with gr.Column(): |
|
gr.Markdown(""" |
|
🎯 **Use the filters below to find your ideal teammates:** |
|
""") |
|
country_filter = gr.Dropdown(label="Filter by Country", choices=["All"] + ALL_COUNTRIES, value="All") |
|
city_filter = gr.Dropdown(label="Filter by City", choices=["All"], value="All") |
|
language_filter = gr.Dropdown(label="Filter by Language", choices=["All"] + LANGUAGES, value="All") |
|
page_state = gr.Number(value=1, visible=False) |
|
table_html = gr.HTML(label="Matching Participants") |
|
prev_btn = gr.Button("⬅️ Previous Page") |
|
next_btn = gr.Button("➡️ Next Page") |
|
|
|
submit_btn.click(submit_profile, inputs=[name, discord, city, country, address, looking, onlinecheck, languages, laptop, robot, skills, describe3, experience, idea], outputs=[status]) |
|
submit_btn.click( |
|
fn=submit_profile, |
|
inputs=[name, discord, city, country, address, looking, onlinecheck, languages, laptop, robot, skills, describe3, experience, idea], |
|
outputs=[status] |
|
).then( |
|
fn=lambda: 1, |
|
outputs=[page_state] |
|
).then( |
|
fn=filter_by_fields, |
|
inputs=[country_filter, city_filter, language_filter, gr.State(1)], |
|
outputs=[table_html, page_state, gr.Number(visible=False)] |
|
) |
|
|
|
|
|
country_filter.change(update_city_filter, inputs=[country_filter], outputs=[city_filter]) |
|
|
|
for dropdown in [country_filter, city_filter, language_filter]: |
|
dropdown.change(lambda c, ci, l: (1,), inputs=[country_filter, city_filter, language_filter], outputs=[page_state]) |
|
dropdown.change(filter_by_fields, inputs=[country_filter, city_filter, language_filter, page_state], outputs=[table_html, page_state, gr.Number(visible=False)]) |
|
|
|
def get_total_pages(selected_country, selected_city, selected_language): |
|
filters = [] |
|
params = [] |
|
if selected_country != "All": |
|
filters.append("country = ?") |
|
params.append(selected_country.title()) |
|
if selected_city != "All": |
|
filters.append("city = ?") |
|
params.append(selected_city.title()) |
|
if selected_language != "All": |
|
filters.append("languages LIKE ?") |
|
params.append(f"%{selected_language.lower()}%") |
|
query = "SELECT COUNT(*) FROM teamup" |
|
if filters: |
|
query += " WHERE " + " AND ".join(filters) |
|
with sqlite3.connect(DB_PATH) as conn: |
|
total = pd.read_sql_query(query, conn, params=params).iloc[0, 0] |
|
return max(1, (total - 1) // PAGE_SIZE + 1) |
|
|
|
def get_filtered_page(c, ci, l, p): |
|
return filter_by_fields(c, ci, l, p) |
|
|
|
def on_filter_change(c, ci, l): |
|
new_page = 1 |
|
return get_filtered_page(c, ci, l, new_page) |
|
|
|
for dropdown in [country_filter, city_filter, language_filter]: |
|
dropdown.change( |
|
fn=on_filter_change, |
|
inputs=[country_filter, city_filter, language_filter], |
|
outputs=[table_html, page_state, gr.Number(visible=False)] |
|
) |
|
|
|
prev_btn.click( |
|
fn=lambda p: max(p - 1, 1), |
|
inputs=[page_state], |
|
outputs=[page_state] |
|
).then( |
|
fn=get_filtered_page, |
|
inputs=[country_filter, city_filter, language_filter, page_state], |
|
outputs=[table_html, page_state, gr.Number(visible=False)] |
|
) |
|
|
|
next_btn.click( |
|
fn=lambda p, c, ci, l: min(get_total_pages(c, ci, l), p + 1), |
|
inputs=[page_state, country_filter, city_filter, language_filter], |
|
outputs=[page_state] |
|
).then( |
|
fn=get_filtered_page, |
|
inputs=[country_filter, city_filter, language_filter, page_state], |
|
outputs=[table_html, page_state, gr.Number(visible=False)] |
|
) |
|
|
|
|
|
|
|
with gr.Accordion("🔒 Admin Tools", open=False): |
|
del_btn = gr.Button("Delete Profile") |
|
admin_discord = gr.Text(label="Discord Username") |
|
admin_code = gr.Text(label="Admin Code", type="password") |
|
del_status = gr.Textbox(label="Status", interactive=False) |
|
del_btn.click( |
|
fn=delete_by_discord, |
|
inputs=[admin_discord, admin_code], |
|
outputs=[del_status] |
|
).then( |
|
fn=lambda: 1, |
|
outputs=[page_state] |
|
).then( |
|
fn=filter_by_fields, |
|
inputs=[country_filter, city_filter, language_filter, gr.State(1)], |
|
outputs=[table_html, page_state, gr.Number(visible=False)] |
|
) |
|
|
|
export_code = gr.Text(label="Admin Code", type="password") |
|
download_btn = gr.Button("Generate and Download CSV") |
|
download_file = gr.File(label="CSV Export", interactive=False) |
|
download_btn.click(download_csv, inputs=[export_code], outputs=[download_file]) |
|
|
|
|
|
return demo |
|
|
|
demo = interface() |
|
demo.launch() |
|
|