async def edit_repository(request):
async with request.app["db-pool"].acquire() as conn:
id_ = int(request.match_info["id"])
data = dict(await conn.fetchrow("SELECT * FROM repository WHERE id = $1", id_))
data = array_to_days(data)
if request.method == "POST":
form = RepositoryForm(
await request.post(),
data=data,
meta=await generate_csrf_meta(request)
)
if form.validate():
data = remove_special_data(form.data)
data = days_to_array(data)
q = "UPDATE repository SET {} WHERE id = ${:d}".format(
settings(data), len(data) + 1
)
try:
await conn.execute(q, *data.values(), id_)
except IntegrityConstraintViolationError:
flash(request, ("warning", _("Le point de livraison ne peut pas être modifié")))
else:
flash(request, ("success", _("Le point de livraison a été modifié")))
return HTTPFound(request.app.router["list_repository"].url_for())
else:
flash(request, ("danger", _("Le formulaire contient des erreurs.")))
return {"id": str(id_), "form": form}
elif request.method == "GET":
form = RepositoryForm(data=data, meta=await generate_csrf_meta(request))
return {"id": str(id_), "form": form}
else:
raise HTTPMethodNotAllowed()
async def create_repository(request):
if request.method == "POST":
form = RepositoryForm(await request.post(), meta=await generate_csrf_meta(request))
if form.validate():
data = remove_special_data(form.data)
data = days_to_array(data)
async with request.app["db-pool"].acquire() as conn:
q = "INSERT INTO repository ({}) VALUES ({})".format(
field_list(data), place_holders(data)
)
try:
await conn.execute(q, *data.values())
except IntegrityConstraintViolationError:
flash(request, ("warning", _("Le point de livraison ne peut pas être créé")))
return {"form": form}
flash(request, ("success", _("Le point de livraison a été créé")))
return HTTPFound(request.app.router["list_repository"].url_for())
else:
flash(request, ("danger", _("Le formulaire contient des erreurs.")))
return {"form": form}
elif request.method == "GET":
form = RepositoryForm(meta=await generate_csrf_meta(request))
return {"form": form}
else:
raise HTTPMethodNotAllowed()
async def edit_profile(request):
async with request.app["db-pool"].acquire() as conn:
rows = await conn.fetch(
"SELECT id, name, latitude, longitude FROM repository WHERE opened"
)
repository_choices = [(row["id"], row["name"]) for row in rows]
login = await authorized_userid(request)
data = dict(await
conn.fetchrow("SELECT * FROM client WHERE login = $1",
login))
del data["password_hash"]
if request.method == "POST":
form = ProfileForm(await request.post(),
data=data,
meta=await generate_csrf_meta(request))
form.repository_id.choices = repository_choices
if form.validate():
data = remove_special_data(form.data)
del data["password2"]
password = data.pop("password")
if password:
if len(password) < 6:
flash(request,
("warning", _("Le mot de passe est trop court")))
return {"form": form}
data["password_hash"] = sha256_crypt.hash(password)
q = "UPDATE client SET {} WHERE login = ${}".format(
settings(data),
len(data) + 1)
try:
await conn.execute(q, *data.values(), login)
except UniqueViolationError:
flash(request,
("warning", _("Votre profil ne peut être modifié")))
else:
flash(request,
("success", _("Votre profil a été modifié")))
return HTTPFound(request.app.router["home"].url_for())
else:
flash(request,
("danger", _("Le formulaire contient des erreurs.")))
return {"form": form, "repositories": rows}
elif request.method == "GET":
form = ProfileForm(data=data,
meta=await generate_csrf_meta(request))
form.repository_id.choices = repository_choices
return {"form": form, "repositories": rows}
else:
raise HTTPMethodNotAllowed()
async def mailing(request):
async with request.app["db-pool"].acquire() as conn:
rows = await conn.fetch("SELECT id, name FROM repository WHERE opened")
repository_choices = [(row["id"], row["name"]) for row in rows]
if request.method == "POST":
form = MailingForm(await request.post(), meta=await generate_csrf_meta(request))
form.repository_id.choices = repository_choices
if form.validate():
data = remove_special_data(form.data)
subject = data["subject"]
message = data["message"]
if data["all_repositories"]:
q = (
"SELECT first_name, email_address, login FROM client "
"WHERE confirmed AND mailing"
)
rows = await conn.fetch(q)
else:
repository_id = data.get("repository_id")
q = (
"SELECT first_name, email_address, login FROM client "
"WHERE confirmed AND mailing AND repository_id = $1"
)
rows = await conn.fetch(q, repository_id)
if not rows:
flash(request, ("warning", _("Il n'y a pas de destinataire.")))
return HTTPFound(request.app.router["mailing"].url_for())
if "<first_name>" in message or "<login>" in message:
for r in rows:
message_ = message.replace("<first_name>", r["first_name"])
message_ = message_.replace("<login>", r["login"])
await send_text_message(request, r["email_address"], subject, message_)
else:
email_addresses = [r["email_address"] for r in rows]
await send_mailing_message(request, email_addresses, subject, message)
flash(request, ("info", _("Les messages ont été envoyés.")))
return HTTPFound(request.app.router["mailing"].url_for())
else:
flash(request, ("danger", _("Le formulaire contient des erreurs.")))
return HTTPFound(request.app.router["mailing"].url_for())
elif request.method == "GET":
form = MailingForm(meta=await generate_csrf_meta(request))
form.repository_id.choices = repository_choices
return {"form": form}
else:
raise HTTPMethodNotAllowed()
async def handler(request):
async with request.app["db-pool"].acquire() as conn:
rows = await conn.fetch(
"SELECT id, name, latitude, longitude FROM repository WHERE opened"
)
repository_choices = [(row["id"], row["name"]) for row in rows]
if request.method == "POST":
form = RegisterForm(await request.post(),
meta=await generate_csrf_meta(request))
form.repository_id.choices = repository_choices
if form.validate():
data = remove_special_data(form.data)
del data["password2"]
data["password_hash"] = sha256_crypt.hash(data.pop("password"))
try:
async with conn.transaction():
q = "INSERT INTO client ({}) VALUES ({}) RETURNING *".format(
field_list(data), place_holders(data))
try:
client = await conn.fetchrow(q, *data.values())
except UniqueViolationError:
flash(request,
("warning",
_("Votre profil ne peut être créé, cet "
"identifiant est déjà utilisé")))
raise # rollback the transaction : client not created
await send_confirmation(
request, client["email_address"],
{"id": client["id"]}, "confirm_register",
_("Confirmation de votre enregistrement"),
"register-confirmation")
flash(request,
("info",
_("Un email de confirmation a été envoyé à {}").
format(client["email_address"])))
return HTTPFound(request.app.router["login"].url_for())
except Exception:
return HTTPFound(request.app.router["register"].url_for())
else:
flash(request,
("danger", _("Le formulaire contient des erreurs.")))
return {"form": form, "repositories": rows}
elif request.method == "GET":
form = RegisterForm(meta=await generate_csrf_meta(request))
form.repository_id.choices = repository_choices
return {"form": form, "repositories": rows}
else:
raise HTTPMethodNotAllowed()
async def edit_batch(request):
async with request.app["db-pool"].acquire() as conn:
id_ = int(request.match_info["id"])
data = dict(await conn.fetchrow("SELECT * FROM batch WHERE id = $1",
id_))
if request.method == "POST":
form = BatchForm(await request.post(),
data=data,
meta=await generate_csrf_meta(request))
if form.validate():
data = remove_special_data(form.data)
# as the date only is chosen by the user, the time part is set to 6:00 am
data["date"] = datetime.combine(data["date"], time(hour=6))
q = "UPDATE batch SET {} WHERE id = ${:d}".format(
settings(data),
len(data) + 1)
try:
await conn.execute(q, *data.values(), id_)
except IntegrityConstraintViolationError:
flash(
request,
("warning", _("La fournée ne peut pas être modifiée")))
else:
flash(request, ("success", _("La fournée a été modifiée")))
return HTTPFound(
request.app.router["list_batch"].url_for())
else:
flash(request,
("danger", _("Le formulaire contient des erreurs.")))
return {"id": str(id_), "form": form}
elif request.method == "GET":
form = BatchForm(data=data, meta=await generate_csrf_meta(request))
return {"id": str(id_), "form": form}
else:
raise HTTPMethodNotAllowed()
async def create_batch(request):
async with request.app["db-pool"].acquire() as conn:
if request.method == "POST":
form = BatchForm(await request.post(),
meta=await generate_csrf_meta(request))
data = remove_special_data(form.data)
# just for csrf !
if not form.validate():
flash(request,
("danger", _("Le formulaire contient des erreurs.")))
return {"form": form}
# as the date only is chosen by the user, the time part is set to 6:00 am
data["date"] = datetime.combine(data["date"], time(hour=6))
try:
async with conn.transaction():
# create the batch
q = (
"INSERT INTO batch (date, capacity, opened) VALUES ($1, $2, $3)"
)
await conn.execute(q, data["date"], data["capacity"],
data["opened"])
flash(request, ("success", _("La fournée a été créée")))
except Exception:
flash(request,
("warning", _("La fournée ne peut pas être créée")))
return {"form": form}
return HTTPFound(request.app.router["list_batch"].url_for())
elif request.method == "GET":
form = BatchForm(meta=await generate_csrf_meta(request))
return {"form": form}
else:
raise HTTPMethodNotAllowed()
async def create_order(request):
login = await authorized_userid(request)
async with request.app["db-pool"].acquire() as conn:
q = ("SELECT c.id, c.disabled, r.days "
"FROM client AS c "
"INNER JOIN repository AS r ON c.repository_id = r.id "
"WHERE c.login = $1 ")
client = await conn.fetchrow(q, login)
client_id = client["id"]
if client["disabled"]:
flash(request,
("warning", _("Vous ne pouvez pas passer de commande.")))
return HTTPFound(request.app.router["list_order"].url_for())
# select opened batches that have no order from the client
# select opened batches that have no order on them
# from all above selected batches, select batches :
# - whose date is 12 hours in the future
# - client's delivery days corresponds to the batch date
q = (
"WITH batch_choices AS ( "
" SELECT b.id AS batch_id, b.date AS batch_date_, c.id AS client_id FROM batch AS b "
" LEFT JOIN order_ AS o ON b.id = o.batch_id "
" LEFT JOIN client AS c ON c.id = o.client_id "
" WHERE b.opened AND b.date > (NOW() + INTERVAL '12 hour') AND "
" (string_to_array($2, ',')::BOOLEAN[])[EXTRACT(DOW FROM b.date) + 1] "
" GROUP BY b.id, b.date, c.id "
" ORDER BY b.id, b.date"
") "
"SELECT DISTINCT batch_id, TO_CHAR(batch_date_::DATE, 'dd-mm-yyyy') AS batch_date "
"FROM batch_choices "
"WHERE batch_id NOT IN ("
" SELECT batch_id FROM batch_choices "
" WHERE client_id = $1"
")")
rows = await conn.fetch(q, client_id, str(client["days"]).strip("[]"))
batch_choices = [(row["batch_id"], row["batch_date"]) for row in rows]
if not batch_choices:
flash(request,
("warning", _("Il n'y a pas de fournée disponible.")))
return HTTPFound(request.app.router["list_order"].url_for())
# get all available products
q = ("SELECT * FROM product WHERE available")
rows = await conn.fetch(q)
products = products_for_context(rows, get_current_locale())
template_context = {"products": products.values()}
if request.method == "POST":
data = await request.post()
form = CreateOrderForm(data,
meta=await generate_csrf_meta(request))
form.batch_id.choices = batch_choices
data = remove_special_data(dict(data))
batch_id = int(data["batch_id"])
# just for csrf !
if not form.validate():
flash(request,
("danger", _("Le formulaire contient des erreurs.")))
return HTTPFound(request.app.router["list_order"].url_for())
# get the batch date and capacity
q = "SELECT date, capacity FROM batch WHERE id = $1"
row = await conn.fetchrow(q, batch_id)
batch_date = row["date"]
batch_capacity = row["capacity"]
# check that the batch corresponds to the delivery days
if not client["days"][(batch_date.weekday() + 1) % 7]:
flash(request,
("warning",
_("La fournée choisie ne permet de vous livrer.")))
return HTTPFound(request.app.router["list_order"].url_for())
template_context["form"] = form
# compute total price and total_load of the order
total_price = 0
total_load = 0
for product_id, product in products.items():
ordered = data["product_qty_{}".format(product_id)].strip()
if ordered == '':
ordered = 0
else:
try:
ordered = int(ordered)
if ordered < 0:
raise ValueError("negative quantity")
except ValueError:
flash(request,
("danger", _("Quantité(s) invalide(s).")))
return template_context
product["ordered"] = ordered
total_price += ordered * products[product_id]["price"]
total_load += ordered * products[product_id]["load"]
# check that at least one product has been ordered
if total_load == 0:
flash(request,
("warning", _("Veuillez choisir au moins un produit")))
return template_context
# checked that the load of ordered products is less than the batch capacity
products_load = await get_ordered_products_load(conn, batch_id)
if total_load + products_load > batch_capacity:
flash(request,
("warning",
_("Votre commande dépasse la capacité de la fournée.")))
return template_context
try:
async with conn.transaction():
# create the order
q = ("INSERT INTO order_ (total, client_id, batch_id) "
"VALUES ($1, $2, $3) RETURNING id")
order_id = await conn.fetchval(q, total_price, client_id,
batch_id)
# create order to products
for product_id, product in products.items():
ordered = product["ordered"]
if ordered != 0:
q = ("INSERT INTO order_product_association ("
" quantity, order_id, product_id"
") "
"VALUES ($1, $2, $3)")
await conn.execute(q, ordered, order_id,
product_id)
except Exception:
flash(request,
("warning", _("Votre commande n'a pas pu être passée.")))
return template_context
flash(request,
("success", _("Votre commande a été passée avec succès")))
return HTTPFound(request.app.router["list_order"].url_for())
elif request.method == "GET":
form = CreateOrderForm(meta=await generate_csrf_meta(request))
form.batch_id.choices = batch_choices
template_context["form"] = form
return template_context
else:
raise HTTPMethodNotAllowed()
async def edit_order(request):
order_id = int(request.match_info["id"])
login = await authorized_userid(request)
async with request.app["db-pool"].acquire() as conn:
q = ("SELECT c.id, c.disabled, r.days "
"FROM client AS c "
"INNER JOIN repository AS r ON c.repository_id = r.id "
"WHERE c.login = $1 ")
client = await conn.fetchrow(q, login)
client_id = client["id"]
if client["disabled"]:
flash(
request,
("warning", _("Vous ne pouvez pas modifier votre commande.")))
return HTTPFound(request.app.router["list_order"].url_for())
# check that the order belongs to the right client
q = ("SELECT COUNT(*) FROM order_ " "WHERE id = $1 AND client_id = $2")
count = await conn.fetchval(q, order_id, client_id)
if count != 1:
return HTTPFound(request.app.router["list_order"].url_for())
# get batch id and batch date
q = ("SELECT batch_id, b.date, b.capacity FROM order_ AS o "
"INNER JOIN batch AS b ON b.id = batch_id "
"WHERE o.id = $1")
row = await conn.fetchrow(q, order_id)
batch_date = row["date"]
batch_id = row["batch_id"]
batch_capacity = row["capacity"]
# check that's its not too late to modify the order
if datetime.now() > batch_date - timedelta(hours=12):
flash(request,
("warning",
_("Il est trop tard pour modifier votre commande.")))
return HTTPFound(request.app.router["list_order"].url_for())
# get all available products
q = ("SELECT * FROM product WHERE available")
rows = await conn.fetch(q)
products = products_for_context(rows, get_current_locale())
template_context = {
"batch_date": batch_date,
"batch_id": batch_id,
"order_id": order_id,
"products": products.values()
}
if request.method == "POST":
data = await request.post()
form = FillOrderForm(await request.post(),
meta=await generate_csrf_meta(request))
data = remove_special_data(dict(data))
template_context["form"] = form
# just for csrf !
if not form.validate():
flash(request,
("danger", _("Le formulaire contient des erreurs.")))
return template_context
# compute total price and total_load of the order
total_price = 0
total_load = 0
for product_id, product in products.items():
ordered = data["product_qty_{}".format(product_id)].strip()
if ordered == '':
ordered = 0
else:
try:
ordered = int(ordered)
if ordered < 0:
raise ValueError("negative quantity")
except ValueError:
flash(request,
("danger", _("Quantité(s) invalide(s).")))
return template_context
product["ordered"] = ordered
total_price += ordered * products[product_id]["price"]
total_load += ordered * products[product_id]["load"]
# check that at least one product has been ordered
if total_load == 0:
flash(request,
("warning", _("Veuillez choisir au moins un produit")))
return template_context
# checked that the load of ordered products is less than batch capacity
products_load = await get_ordered_products_load(conn,
batch_id,
excluded=order_id)
if total_load + products_load > batch_capacity:
flash(request,
("warning",
_("Votre commande dépasse la capacité de la fournée.")))
return template_context
# delete and re-create the order in a transaction
try:
async with conn.transaction():
# delete order to products association records
q = "DELETE FROM order_product_association WHERE order_id = $1"
await conn.execute(q, order_id)
# and re-create them
# create order to products
for product_id, product in products.items():
ordered = product["ordered"]
if ordered != 0:
q = ("INSERT INTO order_product_association ("
" quantity, order_id, product_id"
") "
"VALUES ($1, $2, $3)")
await conn.execute(q, ordered, order_id,
product_id)
# update order total
q = ("UPDATE order_ SET total = $1, date=NOW() "
"WHERE id = $2")
await conn.fetchval(q, total_price, order_id)
except Exception:
flash(
request,
("warning", _("Votre commande n'a pas pu être modifiée.")))
return template_context
flash(request, ("success", _("Votre commande a été modifiée.")))
return HTTPFound(request.app.router["list_order"].url_for())
elif request.method == "GET":
# select all the products from the order
q = ("SELECT p.id, quantity "
"FROM order_product_association AS opa "
"INNER JOIN product AS p ON opa.product_id = p.id "
"WHERE p.available AND opa.order_id = $1")
rows = await conn.fetch(q, order_id)
# update the batch products with the order products
for row in rows:
products[row["id"]]["ordered"] = row["quantity"]
form = FillOrderForm(meta=await generate_csrf_meta(request))
template_context["form"] = form
return template_context
else:
raise HTTPMethodNotAllowed()