def new_local(username): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) if request.method == "POST": with sqlite3.connect(DB_PATH) as conn: c = conn.cursor() c.execute('''PRAGMA foreign_keys = ON;''' ) # Parece que no es necesaria esta linea details = request.form query = "SELECT id FROM Usuario WHERE username='******'".format( username) c.execute(query) id_user = c.fetchone()[0] query = "INSERT INTO Local (nombre, direccion, resena, Usuario_id) VALUES (?, ?, ?, ?)" data_tuple = (details["name"], details["address"], details["description"], id_user) try: c.execute(query, data_tuple) conn.commit() return redirect(url_for('homepage', username=username)) except sqlite3.IntegrityError as e: return render_template('local_already_exists.html', username=username) return render_template('new_local.html', username=username)
def local(username, id_local): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) try: with sqlite3.connect(DB_PATH) as conn: conn.row_factory = sqlite3.Row c = conn.cursor() if request.method == 'POST': query = "SELECT id FROM Usuario WHERE username = ? " c.execute(query, (username, )) conn.commit() id_user = c.fetchone()[0] query = "INSERT INTO 'Favorito_local' ('Usuario_id', 'Local_id', 'fecha') VALUES (?, ?, ?)" data_tuple = (id_user, id_local, str(datetime.datetime.now())) c.execute(query, data_tuple) conn.commit() return redirect(url_for('homepage', username=username)) c.execute('''PRAGMA foreign_keys = ON;''' ) # Parece que no es necesaria esta linea query = "SELECT * FROM Local WHERE id = ? " c.execute(query, (id_local, )) conn.commit() result = c.fetchone() except sqlite3.OperationalError as e: return "Error 503 Service Unavailable.\nPlease try again later" return render_template('local.html', username=username, result=result)
def homepage(username): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) with sqlite3.connect(DB_PATH) as conn: conn.row_factory = sqlite3.Row c = conn.cursor() query = "SELECT * FROM Usuario WHERE username = ? " c.execute(query, (username, )) conn.commit() result = c.fetchone() image = b64encode(result[-4]).decode("utf-8") query = "SELECT * FROM Degustacion INNER JOIN Valoracion ON Valoracion.Degustacion_id=Degustacion.id " \ "ORDER BY Valoracion.valor LIMIT 4" c.execute(query) degustaciones = c.fetchall() query = "SELECT * FROM Degustacion INNER JOIN Valoracion ON Valoracion.Degustacion_id=Degustacion.id " \ "ORDER BY Valoracion.valor DESC LIMIT 4" c.execute(query) mvtastings = c.fetchall() query = "SELECT * FROM Local LIMIT 4" c.execute(query) locals = c.fetchall() return render_template('homepage.html', username=username, user=(result, image), tastings=degustaciones, most_valued_tastings=mvtastings, locals=locals)
def profile(username): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) if request.method == "POST": return redirect(url_for('edit_info', username=username)) try: with sqlite3.connect(DB_PATH) as conn: conn.row_factory = sqlite3.Row c = conn.cursor() c.execute('''PRAGMA foreign_keys = ON;''' ) # Parece que no es necesaria esta linea query = "SELECT * FROM Usuario WHERE username = ? " c.execute(query, (username, )) conn.commit() result = c.fetchone() image = b64encode(result[-4]).decode("utf-8") except sqlite3.OperationalError as e: return "Error 503 Service Unavailable.\nPlease try again later" return render_template('userprofile.html', username=username, result=result, image=image)
def amigos(username): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) return render_template('friends.html')
def search(username): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) if request.method == 'POST': return redirect( url_for('search_list', username=username, request=json.dumps(request.form))) return render_template('search.html', username=username)
def locals(username): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) with sqlite3.connect(DB_PATH) as conn: conn.row_factory = sqlite3.Row c = conn.cursor() query = "SELECT * FROM Local" c.execute(query) locals = c.fetchall() return render_template('locals.html', username=username, locals=locals)
def help(username): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) if request.method == 'POST': return render_template('thank_you.html', username=username) with sqlite3.connect(DB_PATH) as conn: c = conn.cursor() query = "SELECT * FROM Usuario WHERE username='******';".format(username) c.execute(query) value = c.fetchone() return render_template('bug&comments.html', username=username, email=value[3])
def most_valued_tastings(username): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) with sqlite3.connect(DB_PATH) as conn: c = conn.cursor() query = "SELECT * FROM Degustacion INNER JOIN Valoracion ON Valoracion.Degustacion_id=Degustacion.id " \ "ORDER BY Valoracion.valor DESC" c.execute(query) degustaciones = c.fetchall() images = [] [ images.append(b64encode(d[-7]).decode("utf-8") if d[-7] else None) for d in degustaciones ] return render_template('most_valued_tastings.html', username=username, degustaciones=degustaciones, foto=images)
def search_list(username, request): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) with sqlite3.connect(DB_PATH) as conn: c = conn.cursor() req = json.loads(request) regex = req["text"] if req["category"] == "Usuario": query = "SELECT * FROM Usuario WHERE username LIKE '%" + regex + "%' OR email LIKE '%" + regex +\ "%' OR nombre LIKE '%" + regex + "%' OR apellidos LIKE '%" + regex + \ "%' OR pais LIKE '%" + regex + "%' OR descripcion LIKE '%" + regex \ + "%' EXCEPT SELECT * FROM Usuario WHERE username='******';".format(username) elif req["category"] == "Local": query = "SELECT * FROM Local WHERE nombre LIKE '%" + regex + "%' OR direccion LIKE '%" + regex + \ "%' OR resena LIKE '%" + regex + "%';" elif req["category"] == "Degustacion": query = "SELECT * FROM Degustacion WHERE nombre LIKE '%" + regex + "%' OR descripcion LIKE '%" + regex +\ "%' OR tipo_comida LIKE '%" + regex + "%' OR procedencia LIKE '%" + regex +\ "%' OR valoracion_promedio LIKE '%" + regex + \ "%' OR calificador_gusto LIKE '%" + regex + "%';" c.execute(query) values = c.fetchall() if not values: return render_template('search_no_results.html', username=username) return render_template('search_list.html', username=username, request=(req["category"], values))
def new_tasting(username): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) with sqlite3.connect(DB_PATH) as conn: c = conn.cursor() if request.method == 'POST': c.execute('''PRAGMA foreign_keys = ON;''' ) # Parece que no es necesaria esta linea details = request.form image = request.files["image"] if image: blob = image.read() else: path = "./src/static/images/default_food.png" image = open(path, "rb") blob = image.read() query = "SELECT nombre, Local_id FROM Degustacion" c.execute(query) tuplas = c.fetchall() query = "SELECT id FROM Local WHERE nombre='{}'".format( details["local"]) c.execute(query) id_local = c.fetchone()[0] for tupla in tuplas: if (tupla[0] == details["name"]) and (tupla[1] == id_local): return render_template('deg_already_exists.html', username=username) query = "SELECT id FROM Usuario WHERE username='******'".format(username) c.execute(query) id_user = c.fetchone()[0] query = "INSERT INTO 'Degustacion' ('valoracion_promedio', 'nombre', 'descripcion', 'fecha'," \ "'tipo_comida', 'procedencia', 'tamaño', 'foto', 'calificador_gusto', 'Usuario_id'," \ "'Local_id')" \ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT id FROM Usuario WHERE nombre='{}'), "\ + str(id_local) + ")".format(username) data_tuple = (0, details["name"], details["description"], details["date"], details["type"], details["origin"], details["size"], blob, details["taste"]) c.execute(query, data_tuple) conn.commit() query = "SELECT id FROM Degustacion WHERE nombre='{}'".format( details["name"]) c.execute(query) id_deg = c.fetchone()[0] query = "INSERT INTO 'Valoracion' ('Usuario_id', 'Degustacion_id', 'valor') VALUES (?, ?, ?)" data_tuple = (id_user, id_deg, details["rate"]) c.execute(query, data_tuple) conn.commit() query = "SELECT valor FROM Valoracion WHERE Degustacion_id='{}'".format( id_deg) c.execute(query) valores = c.fetchall() suma = 0 for valor in valores: suma += valor[0] valor_promedio = suma / len(valores) valor_promedio = '%.3f' % valor_promedio query = "UPDATE 'Degustacion' SET 'valoracion_promedio'='{}'".format( valor_promedio) c.execute(query) conn.commit() return render_template('ok_tasting.html', username=username) query = "SELECT nombre FROM Local" c.execute(query) locales_tupla = c.fetchall() locales = [] for local in locales_tupla: locales.append(local[0]) return render_template('new_tasting.html', username=username, locales=locales)
def edit_info(username): try: functions.verified_user(DB_PATH, username) except exceptions.UserNotExist: return render_template("user_not_exist.html") except exceptions.UserNOtVerified: return render_template("no_verification.html", username=username) if request.method == 'GET': with sqlite3.connect(DB_PATH) as conn: conn.row_factory = sqlite3.Row c = conn.cursor() query = "SELECT * FROM 'Usuario' WHERE username=?" c.execute(query, [username]) data = c.fetchone() image = b64encode(data["foto"]).decode("utf-8") username = data["username"] user_id = data["id"] return render_template('edit_info.html', data=data, image=image, username=username, user_id=user_id) elif request.method == 'POST': with sqlite3.connect(DB_PATH) as conn: conn.row_factory = sqlite3.Row c = conn.cursor() data = request.form if (data["username"] != data["original_username"]): query = "SELECT * FROM 'Usuario' WHERE username='******'".format( data["username"]) c.execute(query) res = c.fetchone() if (res): return render_template( 'user_already_exists.html', old_username=data["original_username"], new_username=data["username"]) if (data["email"] != data["original_email"]): query = "SELECT * FROM 'Usuario' WHERE email='{}'".format( data["email"]) c.execute(query) res = c.fetchone() if (res): return render_template('email_already_in_use.html', email=data["email"], username=data["username"]) date = functions.date_validator( str(data["date"]).replace("/", "-")) if not date: return render_template('age_error.html') else: user_id = data["id"] image = request.files["image"] blob = None if image: blob = image.read() if data["password"]: query = """UPDATE Usuario SET username=?, contrasena=?, email= ?, fecha_nacimiento=?, nombre=?, apellidos=?, pais=?, descripcion=?, genero=?, foto=? WHERE id=?;""" data_tuple = (data["username"], generate_password_hash(data["password"]), data["email"], str(data["date"]), data["firstname"], data["lastname"], data["country"], data["description"], data["sex"], blob, user_id) else: query = """UPDATE Usuario SET username=?, email= ?, fecha_nacimiento=?, nombre=?, apellidos=?, pais=?, descripcion=?, genero=?, foto=? WHERE id=?;""" data_tuple = (data["username"], data["email"], str(data["date"]), data["firstname"], data["lastname"], data["country"], data["description"], data["sex"], blob, user_id) else: if data["password"]: query = """UPDATE Usuario SET username=?, contrasena=?, email= ?, fecha_nacimiento=?, nombre=?, apellidos=?, pais=?, descripcion=?, genero=? WHERE id=?;""" data_tuple = (data["username"], generate_password_hash(data["password"]), data["email"], str(data["date"]), data["firstname"], data["lastname"], data["country"], data["description"], data["sex"], user_id) else: query = """UPDATE Usuario SET username = ?, email= ?, fecha_nacimiento=?, nombre = ?, apellidos=?, pais=?, descripcion=?, genero=? WHERE id=?;""" data_tuple = (data["username"], data["email"], str(data["date"]), data["firstname"], data["lastname"], data["country"], data["description"], data["sex"], user_id) try: c.execute(query, data_tuple) conn.commit() return render_template('savedChanges.html', username=data["username"]) except sqlite3.IntegrityError as e: print("Error:", e) return render_template('error_sign_in.html', username=data["username"], email=data["email"]) except sqlite3.OperationalError as e: print("Error:", e) return "Error 503 Service Unavailable.\nPlease try again later"