Joel Aranda Cardenas SMX 1r

Enrrere
Entra

❗Problemes que he hagut de solucionar

  • Error de Lògica en el Registre: El codi intentava registrar l’usuari i, si el correu ja existia, llançava un error. Però, què passa si l’usuari ja s’havia registrat abans i només volia tornar a entrar per veure la seva cita? He afegit una comprovació: si l’email ja existeix, el sistema recupera l’ID d’aquest usuari i li permet fer el login en lloc de bloquejar-lo.
  • Tractament del “Delete” en Cascada: Tot i que la taula s’havia creat amb ON DELETE CASCADE, SQLite requereix activar una comanda específica (PRAGMA foreign_keys = ON) en cada connexió per a que això funcioni realment. S’ha afegit aquesta línia.

📜 Justificació de les correccions

1. Error de Lògica en el Registre:

La solució consisteix a recuperar l’ID de l’usuari existent si el registre falla perquè el correu està duplicat:

  • Capturar l’error: Dins del bloc except, no enviïs un missatge d’error ni bloquegis l’usuari.
  • Consultar l’ID: Executa una consulta SELECT id FROM assistents WHERE email = ? per obtenir l’identificador de l’usuari que ja estava registrat.
  • Iniciar sessió igualment: Utilitza aquest ID recuperat per generar la cookie de sessió i redirigir l’usuari directament a la pàgina de /horari.

2. Tractament del “Delete” en Cascada: (Línies 102 i 153)

Què he canviat: He substituït sqlite3.connect(...) per self.get_db_connection(). Per què: * A la línia 153 (dins de do_POST), quan es crida a /delete_user, ara la base de dades té permís per esborrar.

Abans, feia DELETE FROM assistents, però com que el motor de SQLite tenia les foreign_keys en OFF (per defecte), esborrava l’assistent però ignorava la instrucció de borrar el seu horari, deixant dades òrfenes.

⌨️ CODI FINAL (PYTHON) 

import http.server
import socketserver
import sqlite3
import urllib.parse
import http.cookies
import base64

# --- Configuració de la Base de Dades SQLite3 ---
def init_db():
    conn = sqlite3.connect('sistema_portes_obertes.db')
    cursor = conn.cursor()
    # Activar el soporte de llaves foráneas al inicio
    cursor.execute("PRAGMA foreign_keys = ON;")
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS assistents (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            nom TEXT, cognom TEXT, email TEXT UNIQUE, telefon TEXT, centre TEXT
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS horaris (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            hora TEXT,
            assistent_id INTEGER UNIQUE,
            num_persones INTEGER,
            FOREIGN KEY(assistent_id) REFERENCES assistents(id) ON DELETE CASCADE
        )
    ''')
    conn.commit()
    conn.close()

# --- Estils CSS Neó Modern ---
CSS_GAMER = """
<style>
    :root {
        --bg: #0a0a0c;
        --panel: #141417;
        --neon-blue: #00d2ff;
        --neon-purple: #9d50bb;
        --text: #e0e0e0;
    }
    body { 
        font-family: 'Segoe UI', Roboto, sans-serif; 
        background: var(--bg); 
        color: var(--text); 
        margin: 0; padding: 20px;
        background-image: radial-gradient(circle at 50% 50%, #1a1a2e 0%, #0a0a0c 100%);
    }
    .container { 
        background: var(--panel); 
        padding: 30px; 
        border-radius: 15px; 
        box-shadow: 0 0 20px rgba(0, 210, 255, 0.2); 
        border: 1px solid rgba(0, 210, 255, 0.3);
        max-width: 800px; margin: auto;
    }
    h2 { 
        color: var(--neon-blue); 
        text-transform: uppercase; 
        letter-spacing: 2px; 
        text-shadow: 0 0 10px var(--neon-blue);
        text-align: center;
    }
    table { width: 100%; border-collapse: collapse; margin-top: 20px; background: rgba(255,255,255,0.02); }
    th { color: var(--neon-purple); text-align: left; padding: 15px; border-bottom: 2px solid var(--neon-purple); text-transform: uppercase; font-size: 13px; }
    td { padding: 15px; border-bottom: 1px solid #2a2a2e; }
    
    input, select { 
        background: #1e1e22; border: 1px solid #3a3a3e; 
        color: white; padding: 12px; border-radius: 8px; margin: 10px 0; width: 100%;
        transition: 0.3s;
    }
    input:focus { border-color: var(--neon-blue); outline: none; box-shadow: 0 0 10px var(--neon-blue); }
    
    button { 
        background: linear-gradient(45deg, var(--neon-blue), var(--neon-purple)); 
        color: white; border: none; padding: 12px 20px; border-radius: 8px; 
        font-weight: bold; cursor: pointer; transition: 0.3s; text-transform: uppercase;
    }
    button:hover { transform: scale(1.05); box-shadow: 0 0 15px var(--neon-blue); }
    
    .stat-card { background: #1e1e22; padding: 15px; border-radius: 10px; border-left: 4px solid var(--neon-blue); flex: 1; text-align: center; }
    .badge { text-decoration: none; color: var(--neon-blue); border: 1px solid var(--neon-blue); padding: 5px 12px; border-radius: 20px; font-size: 12px; transition: 0.3s; }
    .badge:hover { background: var(--neon-blue); color: black; }
    
    .reserva-activa { background: rgba(157, 80, 187, 0.15); border-left: 4px solid var(--neon-purple); }
    a { color: var(--neon-purple); text-decoration: none; font-weight: bold; }
</style>
"""

class EscolaHandler(http.server.BaseHTTPRequestHandler):
    
    def get_db_connection(self):
        conn = sqlite3.connect('sistema_portes_obertes.db')
        # Esta línea es la clave para que el CASCADE funcione en cada petición
        conn.execute("PRAGMA foreign_keys = ON;")
        return conn

    def check_auth(self):
        auth_header = self.headers.get('Authorization')
        if not auth_header: return False
        prefix = 'Basic '
        if not auth_header.startswith(prefix): return False
        try:
            encoded = auth_header[len(prefix):]
            decoded = base64.b64decode(encoded).decode('utf-8')
            user, password = decoded.split(':')
            return user == 'admin' and password == 'JA54734231'
        except: return False

    def request_auth(self):
        self.send_response(401)
        self.send_header('WWW-Authenticate', 'Basic realm="Acces Administrador"')
        self.send_header('Content-type', 'text/html; charset=utf-8')
        self.end_headers()
        self.wfile.write(b"<body style='background:#000;color:red;font-family:monospace;'><h1>401 ACCES_DENEGAT</h1></body>")

    def get_user_id_from_cookie(self):
        if "Cookie" in self.headers:
            try:
                cookie = http.cookies.SimpleCookie(self.headers["Cookie"])
                if "user_id" in cookie: return cookie["user_id"].value
            except: return None
        return None

    def do_GET(self):
        url_p = urllib.parse.urlparse(self.path)
        params = urllib.parse.parse_qs(url_p.query)
        
        if url_p.path == '/favicon.ico':
            self.send_response(204)
            self.end_headers()
            return

        if url_p.path == '/logout':
            self.send_response(303)
            self.send_header('Set-Cookie', 'user_id=; Max-Age=0; Path=/')
            self.send_header('Location', '/')
            self.end_headers()
            return

        if url_p.path == '/admin':
            if not self.check_auth():
                self.request_auth()
            else:
                self.send_response(200)
                self.send_header('Content-type', 'text/html; charset=utf-8')
                self.end_headers()
                self.mostrar_admin(params.get('filtre', [None])[0])
            return

        user_id = self.get_user_id_from_cookie()
        if url_p.path == '/horari':
            if not user_id:
                self.send_response(303)
                self.send_header('Location', '/')
                self.end_headers()
                return
            self.send_response(200)
            self.send_header('Content-type', 'text/html; charset=utf-8')
            self.end_headers()
            self.mostrar_horari(user_id)
            return

        if url_p.path == '/' and user_id:
            self.send_response(303)
            self.send_header('Location', '/horari')
            self.end_headers()
            return

        self.send_response(200)
        self.send_header('Content-type', 'text/html; charset=utf-8')
        self.end_headers()
        self.wfile.write(self.get_html_registre().encode('utf-8'))

    def mostrar_admin(self, filtre_hora=None):
        conn = self.get_db_connection()
        cursor = conn.cursor()
        query = '''SELECT a.id, a.nom, a.cognom, a.email, a.telefon, a.centre, h.hora, h.num_persones 
                   FROM assistents a LEFT JOIN horaris h ON a.id = h.assistent_id'''
        if filtre_hora: query += f" WHERE h.hora = '{filtre_hora}'"
        cursor.execute(query + " ORDER BY h.hora ASC")
        assistents = cursor.fetchall()
        cursor.execute("SELECT hora, SUM(num_persones) FROM horaris GROUP BY hora")
        ocupacio = dict(cursor.fetchall())
        conn.close()

        total_pers = sum(a[7] for a in assistents if a[7])
        files = ""
        for a in assistents:
            hora = a[6] if a[6] else "<span style='color:#ff4d4d;'>Sense cita</span>"
            files += f"<tr><td>{a[1]} {a[2]}</td><td>{a[3]}</td><td>{a[4]}</td><td>{a[5]}</td><td><b>{hora}</b></td><td>{a[7] or 0}</td><td><form method='POST' action='/delete_user' style='margin:0'><input type='hidden' name='id' value='{a[0]}'><button style='background:#ff4d4d; padding:5px 10px; font-size:11px;' onclick=\"return confirm('Segur que vols eliminar aquest usuari i la seva reserva?')\">Eliminar</button></form></td></tr>"

        html = f"<html><head>{CSS_GAMER}</head><body><div class='container'><h2>Panell d'Admin</h2><div style='display:flex; gap:20px; margin-bottom:20px;'><div class='stat-card'>Registres: {len(assistents)}</div><div class='stat-card'>Total Persones: {total_pers}</div></div><div style='margin-bottom:15px;'>Filtres: <a href='/admin' class='badge'>Tots</a> {' '.join([f'<a href=\"/admin?filtre={h}\" class=\"badge\">{h}</a>' for h in hores_estatiques])}</div><table><tr><th>Nom</th><th>Email</th><th>Tel</th><th>Centre</th><th>Hora</th><th>P.</th><th>Acció</th></tr>{files}</table><br><a href='/'>Anar al Registre</a></div></body></html>"
        self.wfile.write(html.encode('utf-8'))

    def mostrar_horari(self, assistent_id):
        conn = self.get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT nom FROM assistents WHERE id = ?", (assistent_id,))
        row = cursor.fetchone()
        if not row:
            conn.close()
            self.wfile.write(b"Usuari no trobat. <a href='/logout'>Tornar</a>")
            return
        
        nom_usuari = row[0]
        cursor.execute("SELECT hora, num_persones FROM horaris WHERE assistent_id = ?", (assistent_id,))
        cita_propia = cursor.fetchone()
        cursor.execute("SELECT hora, SUM(num_persones) FROM horaris GROUP BY hora")
        ocupacion = dict(cursor.fetchall())
        conn.close()

        hores = ["16:45", "17:00", "17:15", "17:30", "17:45", "18:00", "18:15", "18:30", "18:45"]
        files = ""
        for h in hores:
            total = ocupacion.get(h, 0); lliures = 20 - total
            if cita_propia and cita_propia[0] == h:
                accio = f"<form method='POST' action='/cancelar' style='margin:0'><input type='hidden' name='user_id' value='{assistent_id}'><button style='background:red;'>Anul·lar</button></form>"
                files += f"<tr class='reserva-activa'><td>{h}</td><td><b>La teva Reserva ({cita_propia[1]} pers.)</b></td><td>{accio}</td></tr>"
            elif cita_propia:
                files += f"<tr><td>{h}</td><td style='opacity:0.5'>Disponible ({lliures})</td><td>---</td></tr>"
            elif lliures > 0:
                opts = "".join([f"<option value='{i}'>{i}</option>" for i in range(1, min(5, lliures + 1))])
                accio = f"<form method='POST' action='/reservar' style='margin:0'><input type='hidden' name='user_id' value='{assistent_id}'><input type='hidden' name='hora' value='{h}'><select name='cantidad' style='width:60px; padding:5px; margin:0;'>{opts}</select><button style='padding:5px 15px; margin-left:10px;'>Reservar</button></form>"
                files += f"<tr><td>{h}</td><td>Lliures: {lliures}</td><td>{accio}</td></tr>"
            else:
                files += f"<tr><td>{h}</td><td style='color:#ff4d4d;'>Complet</td><td>---</td></tr>"

        html = f"<html><head>{CSS_GAMER}</head><body><div class='container'><h2>Benvingut/da, {nom_usuari}</h2><table>{files}</table><br><div style='text-align:center;'><a href='/logout'>TANCAR SESSIÓ</a></div></div></body></html>"
        self.wfile.write(html.encode('utf-8'))

    def get_html_registre(self):
        return f"""
        <html><head>{CSS_GAMER}</head><body><div class="container" style="max-width:400px; margin-top:50px;">
            <h2>Registre Portes Obertes</h2>
            <form method="POST" action="/register">
                <input type="text" name="nom" placeholder="Nom" required>
                <input type="text" name="cognom" placeholder="Cognoms" required>
                <input type="email" name="email" placeholder="Correu Electrònic" required>
                <input type="text" name="telefono" placeholder="Telèfon (opcional)">
                <input type="text" name="centre" placeholder="Centre de Procedència (opcional)">
                <button style="width:100%; margin-top:20px;" type="submit">ENTRAR</button>
            </form>
        </div></body></html>"""

    def do_POST(self):
        content_length = int(self.headers['Content-Length'])
        post_data = self.rfile.read(content_length).decode('utf-8')
        params = urllib.parse.parse_qs(post_data)
        
        conn = self.get_db_connection()
        cursor = conn.cursor()

        if self.path == '/register':
            nom = params.get('nom',[''])[0]
            cognom = params.get('cognom',[''])[0]
            email = params.get('email',[''])[0]
            telefon = params.get('telefono',['-'])[0]
            centre = params.get('centre',[''])[0]
            try:
                cursor.execute("INSERT INTO assistents (nom, cognom, email, telefon, centre) VALUES (?, ?, ?, ?, ?)", (nom, cognom, email, telefon, centre))
                conn.commit()
                u_id = cursor.lastrowid
                self.send_response(303)
                self.send_header('Set-Cookie', f'user_id={u_id}; Max-Age=3600; Path=/')
                self.send_header('Location', '/horari')
                self.end_headers()
            except sqlite3.IntegrityError:
                self.enviar_error("Aquest correu ja està registrat.")

        elif self.path == '/reservar':
            u_id = params.get('user_id',[''])[0]
            hora = params.get('hora',[''])[0]
            cant = int(params.get('cantidad',['1'])[0])
            cursor.execute("INSERT OR REPLACE INTO horaris (hora, assistent_id, num_persones) VALUES (?, ?, ?)", (hora, u_id, cant))
            conn.commit()
            self.send_response(303)
            self.send_header('Location', '/horari')
            self.end_headers()

        elif self.path == '/cancelar':
            u_id = params.get('user_id',[''])[0]
            cursor.execute("DELETE FROM horaris WHERE assistent_id = ?", (u_id,))
            conn.commit()
            self.send_response(303)
            self.send_header('Location', '/horari')
            self.end_headers()

        elif self.path == '/delete_user':
            u_id = params.get('id', [None])[0]
            # Aquí el ON DELETE CASCADE se activa porque usamos get_db_connection()
            cursor.execute("DELETE FROM assistents WHERE id = ?", (u_id,))
            conn.commit()
            self.send_response(303)
            self.send_header('Location', '/admin')
            self.end_headers()

        conn.close()

    def enviar_error(self, m):
        self.send_response(200)
        self.send_header('Content-type', 'text/html; charset=utf-8')
        self.end_headers()
        self.wfile.write(f"<html><head>{CSS_GAMER}</head><body style='text-align:center;'><h2>ERROR</h2><p>{m}</p><a href='/logout'>TORNAR</a></body></html>".encode('utf-8'))

# Global para filtros
hores_estatiques = ["16:45", "17:00", "17:15", "17:30", "17:45", "18:00", "18:15", "18:30", "18:45"]

if __name__ == "__main__":
    init_db()
    socketserver.TCPServer.allow_reuse_address = True
    with socketserver.TCPServer(("", 5000), EscolaHandler) as httpd:
        print("Servidor Neó actiu a http://localhost:5000")
        httpd.serve_forever()

Review My Order

0

Subtotal

Taxes & shipping calculated at checkout

Checkout