def add_product():
if not authorize():
return redirect('/User/SignIn')
form = NewProductForm()
with db.cursor() as cursor:
cursor.execute("SELECT CategoryId, Type FROM Category")
form.category.choices = [('--', '-- Veuillez sélectionner une catégorie --')] + [
(str(row["CategoryId"]), row["Type"]) for row in cursor.fetchall()]
if form.validate_on_submit():
if form.category.data != '--':
with db.cursor() as cursor:
town = form.town.data
cursor.execute("SELECT TownId FROM Town "
"WHERE TownName = %s", (town, ))
row = cursor.fetchone()
if row is None:
cursor.execute("INSERT INTO Town (TownName) VALUES (%s)", (town,))
filename = ""
if form.image.data is not None:
u = uuid4().hex
filename = "{}.{}".format(u, secure_filename(form.image.data.filename).split('.')[-1])
form.image.data.save(os.path.join(
app.static_folder, app.config["UPLOAD_FILE"], filename
))
cursor.execute("INSERT INTO Image (FileName) "
"VALUES (%s)", (filename,))
user = get_user()
cursor.execute("INSERT INTO Product (ProductName, Price, Description, Date, CategoryId, UserId, TownId, ImageId) "
"VALUES (%s, %s, %s, NOW(), %s, %s, "
"(SELECT TownId FROM Town WHERE TownName = %s), "
"(SELECT ImageId FROM Image WHERE FileName = %s))", (form.name.data,
form.price.data,
form.description.data,
form.category.data,
user["UserId"],
town,
filename))
db.commit()
return redirect('/')
else:
flash('Veuillez choisir une catégorie')
return View('add_product.html', title='Nouveau produit', form=form)
def edit_account():
if not authorize():
return redirect('/User/SignIn')
form = EditAccountForm()
row = get_user()
if form.validate_on_submit() and row is not None:
user, email, phone = form.username.data, form.email.data, form.phone.data
with db.cursor() as cursor:
cursor.execute(
"UPDATE User "
"SET UserName = %s, Email = %s, Phone = %s "
"WHERE UserId = %s", (user, email, phone, row["UserId"]))
db.commit()
flash('Mise à jour du profile réussie')
return redirect('/')
if row is not None:
form.username.data = row["UserName"]
form.email.data = row["Email"]
form.confirm_email.data = row["Email"]
form.phone.data = row["Phone"]
return View('edit_account.html', title='Profile', form=form)
return View('edit_account.html', title='Profile', form=form)
def product_by_category(id):
with db.cursor() as cursor:
cursor.execute("SELECT ProductId, ProductName, Price FROM Product "
"WHERE Product.CategoryId = %s "
"ORDER BY Date DESC", (id,))
products = cursor.fetchall()
return View('list_product.html', products=products, title="Liste des correspondant à la recherche")
def search_product(query):
with db.cursor() as cursor:
cursor.execute("SELECT ProductId, ProductName, Price FROM Product "
"WHERE ProductName LIKE %s "
"ORDER BY Date DESC", ("%{}%".format(query),))
products = cursor.fetchall()
return View('list_product.html', products=products, title="Liste des correspondant à la recherche")
def list_products():
with db.cursor() as cursor:
cursor.execute("SELECT Product.ProductId, ProductName, Price, FileName FROM Product "
"LEFT JOIN Image ON Image.ImageId = Product.ImageId "
"ORDER BY Date DESC")
products = cursor.fetchall()
return View('list_product.html', products=products, title="Liste des articles publiés")
def index():
with db.cursor() as cursor:
cursor.execute(
"SELECT ProductId, ProductName, Price, FileName FROM Product "
"LEFT JOIN Image ON Product.ImageId = Image.ImageId "
"ORDER BY Date DESC LIMIT 10")
products = cursor.fetchall()
return View('accueil.html', title='Accueil', products=products)
def view_own_products():
if not authorize():
return redirect('/User/SignIn')
with db.cursor() as cursor:
cursor.execute("SELECT ProductId, ProductName, Price FROM Product "
"WHERE Product.UserId = %s "
"ORDER BY Date DESC", (get_user()["UserId"],))
products = cursor.fetchall()
return View('list_product.html', products=products, title="Liste des correspondant à la recherche")
def logout():
if not authorize():
return redirect('/User/SignIn')
if 'session_id' in session:
with db.cursor() as cursor:
cursor.execute("DELETE FROM Session WHERE SessionId = %s",
(session["session_id"], ))
db.commit()
del session['session_id']
return redirect('/')
def remove_product(id):
user = get_user()
with db.cursor() as cursor:
cursor.execute("SELECT UserId FROM Product "
"WHERE ProductId = %s", (id,))
product = cursor.fetchone()
if product is not None:
if user is not None and user["UserId"] == product["UserId"]:
cursor.execute("DELETE FROM Product "
"WHERE ProductId = %s", (id,))
db.commit()
flash("L'annonce a bien été supprimée")
return redirect('/')
def register():
form = RegisterForm()
if form.validate_on_submit():
email, user, pwd, phone = form.email.data, form.username.data, form.password.data, form.phone.data
pw_hash = generate_password_hash(pwd)
with db.cursor() as cursor:
cursor.execute(
"INSERT INTO User (UserName, PasswordHash, RegistrationDate, Email, Phone) "
"VALUES (%s, %s, CURRENT_DATE(), %s, %s)",
(user, pw_hash, email, phone))
db.commit()
flash('Inscription réussie, vous pouvez maintenant vous connecter')
return redirect('/')
return View('register.html', title='Inscription', form=form)
def delete_account():
if not authorize():
return redirect('/User/SignIn')
row = get_user()
with db.cursor() as cursor:
cursor.execute(
"DELETE FROM User "
"WHERE UserId = %s",
(row["UserId"]),
)
db.commit()
if 'session_id' in session:
del session['session_id']
return redirect('/')
def show_product(id):
with db.cursor() as cursor:
cursor.execute("SELECT ProductId, ProductName, Price, Description, Date, TownName, Type, Product.UserId, UserName, Email, Phone, FileName FROM Product "
"INNER JOIN User ON Product.UserId = User.UserId "
"INNER JOIN Town ON Product.TownId = Town.TownId "
"INNER JOIN Category ON Product.CategoryId = Category.CategoryId "
"LEFT JOIN Image ON Product.ImageId = Image.ImageId "
"WHERE ProductId = %s", (id,))
product = cursor.fetchone()
if product is not None:
user = get_user()
is_author = user is not None and user["UserId"] == product["UserId"]
else:
product = []
is_author = False
return View('show_product.html', title='Accueil', product=product, is_author=is_author)
def login():
form = LoginForm()
if form.validate_on_submit():
with db.cursor() as cursor:
cursor.execute(
"SELECT UserId, PasswordHash FROM User WHERE Email = %s",
(form.email.data, ))
row = cursor.fetchone()
if row is not None and check_password_hash(row["PasswordHash"],
form.password.data):
session_id = guid().hex
cursor.execute(
"INSERT INTO Session (SessionId, UserId) VALUES (%s, %s)",
(session_id, row["UserId"]))
db.commit()
session["session_id"] = session_id
return redirect('/')
else:
flash('Tentative de connection incorrecte')
return View('login.html', title='Connexion', form=form)
def change_password():
if not authorize():
return redirect('/User/SignIn')
form = ChangePassword()
row = get_user()
if form.validate_on_submit():
old_pwd, new_pwd = form.old_password.data, form.password.data
if check_password_hash(row["PasswordHash"], old_pwd):
with db.cursor() as cursor:
cursor.execute(
"UPDATE User "
"SET PasswordHash = %s "
"WHERE UserId = %s",
(generate_password_hash(new_pwd), row["UserId"]))
db.commit()
flash('Mise à jour du mot de passe réussie')
return redirect('/')
flash('Mot de passe actuel incorrect')
return View('change_password.html',
title='Modifier son mot de passe',
form=form)
def View(template, **kwargs):
with db.cursor() as cursor:
cursor.execute("SELECT CategoryId, Type FROM Category")
categories = cursor.fetchall()
return render_template(template, categories=categories, **kwargs)
def edit_product(id):
form = NewProductForm()
user = get_user()
with db.cursor() as cursor:
cursor.execute("SELECT UserId, ProductName, Price, Description, TownName, Product.CategoryId, Type FROM Product "
"INNER JOIN Town ON Product.TownId = Town.TownId "
"INNER JOIN Category ON Product.CategoryId = Category.CategoryId "
"WHERE ProductId = %s", (id,))
product = cursor.fetchone()
if product is None:
flash('Produit invalide')
return redirect('/')
if user is None:
return redirect('/User/SignIn')
elif user["UserId"] != product["UserId"]:
return redirect('/')
cursor.execute("SELECT CategoryId, Type FROM Category")
form.category.choices = [(str(product["CategoryId"]), product["Type"])] + [
(str(row["CategoryId"]), row["Type"]) for row in cursor.fetchall() if row["CategoryId"] != product["CategoryId"]]
if form.validate_on_submit():
town = form.town.data
with db.cursor() as cursor:
if product["TownName"] != town:
cursor.execute("SELECT TownId FROM Town "
"WHERE TownName = %s", (town,))
row = cursor.fetchone()
if row is None:
cursor.execute("INSERT INTO Town (TownName) VALUES (%s)", (town,))
cursor.execute("SELECT CategoryId FROM Category "
"WHERE CategoryId = %s", (form.category.data,))
row = cursor.fetchone()
if row is None:
flash('Catégorie invalide')
return redirect('/')
if form.image.data is not None:
u = uuid4().hex
filename = "{}.{}".format(u, secure_filename(form.image.data.filename).split('.')[-1])
form.image.data.save(os.path.join(
app.static_folder, app.config["UPLOAD_FILE"], filename
))
cursor.execute("INSERT INTO Image (FileName) "
"VALUES (%s)", (filename,))
cursor.execute("UPDATE PRODUCT "
"SET ProductName = %s, Price = %s, Description = %s, Date = NOW(), CategoryId = %s, "
"ImageId = (SELECT ImageId FROM Image WHERE FileName = %s), "
"TownId = (SELECT TownId FROM Town WHERE TownName = %s) "
"WHERE ProductId = %s", (form.name.data, form.price.data, form.description.data, form.category.data, filename, town, id))
else:
cursor.execute("UPDATE PRODUCT "
"SET ProductName = %s, Price = %s, Description = %s, Date = NOW(), CategoryId = %s, "
"TownId = (SELECT TownId FROM Town WHERE TownName = %s) "
"WHERE ProductId = %s", (form.name.data, form.price.data, form.description.data, form.category.data, town, id))
db.commit()
flash("L'annonce a bien été modifiée")
return redirect('/')
form.name.data = product["ProductName"]
form.price.data = product["Price"]
form.description.data = product["Description"]
form.town.data = product["TownName"]
form.category.data = product["Type"]
return View('edit_product.html', title="Modifier l'annonce", form=form)
