def modify_some_orders_status(self):
"""The objective of this method is to create some interactions with orders
which had been injected and pass them from 'en attente' statut to
'en preparation' status """
# We get 10 order_ids on a random way
orders = self._get_10_orders()
# on modifie leur statut de commande à en preparations en ajoutant 30 minutes ou 2heures
with SQLconnexion() as connexion:
for order in orders:
step_date = order["date_debut_operation"] + datetime.timedelta(
minutes=random.randint(10, 75))
with connexion.cursor() as cursor:
sql = """UPDATE evolution_preparation
SET date_fin_operation = %s
WHERE id_commande = %s"""
cursor.execute(sql, (step_date, order["id_commande"]))
connexion.commit()
with connexion.cursor() as cursor:
sql = """INSERT INTO evolution_preparation
(id_commande, id_statut_commande,
date_debut_operation, date_fin_operation)
VALUES (%s, %s, %s, NULL)"""
cursor.execute(sql, (order["id_commande"], "2", step_date))
connexion.commit()
def _get_delivery_type_ids(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id FROM type_livraison"""
cursor.execute(sql)
delivery_ids = cursor.fetchall()
return delivery_ids
def _get_employee_ids(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id_utilisateur FROM employe"""
cursor.execute(sql)
employe_id = cursor.fetchall()
return employe_id
def set_one_employee(self, user_id, role_id):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """INSERT INTO employe (id_utilisateur, id_role)
VALUES (%s, %s)"""
cursor.execute(sql, (user_id, role_id))
connexion.commit()
def _get_ingredient_infos(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id, quantite_globale FROM ingredient"""
cursor.execute(sql)
ingredient_infos = cursor.fetchall()
return ingredient_infos
def get_last_user_id_injected(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id FROM utilisateur ORDER BY id DESC LIMIT 1"""
cursor.execute(sql)
user_id = cursor.fetchone()
return user_id["id"]
def get_role_id(self, role_name):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id FROM role WHERE nom = %s"""
cursor.execute(sql, (role_name))
role = cursor.fetchone()
return role["id"]
def _get_payment_status_ids(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id FROM statut_paiement"""
cursor.execute(sql)
payment_status_ids = cursor.fetchall()
return payment_status_ids
def set_recipes(self, number):
words_list = [
"recette", "pizza", "four", "sauce tomate", "fromage", "origan",
"enfourner", "ajouter le jambon", "cuire", "étaler la pate",
"croquante", "saler", "poivrer"
]
with SQLconnexion() as connexion:
numb_recipes = 0
while numb_recipes < number:
recipe = {
"recipe":
"La recette de " +
self.fake_data.random_element(elements=self.pizzas),
"description":
self.fake_data.sentence(nb_words=30,
ext_word_list=words_list)
}
with connexion.cursor() as cursor:
sql = """INSERT INTO recette (recette, description)
VALUES (%s, %s)"""
cursor.execute(sql,
(recipe["recipe"], recipe["description"]))
connexion.commit()
numb_recipes += 1
def get_last_adress_id_injected(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id FROM adresse ORDER BY id DESC LIMIT 1"""
cursor.execute(sql)
address_id = cursor.fetchone()
return address_id["id"]
def _get_ingredients_quantity_per_restaurant(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT * FROM stock_ingredient_par_restaurant"""
cursor.execute(sql)
result = cursor.fetchall()
return result
def _get_pizza_ids(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id FROM pizza"""
cursor.execute(sql)
pizza_ids = cursor.fetchall()
return pizza_ids
def _get_restaurant_ids(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id FROM restaurant"""
cursor.execute(sql)
restaurant_ids = cursor.fetchall()
return restaurant_ids
def set_categories(self):
with SQLconnexion() as connexion:
for category in self.categories:
category_info = {
"id":
"",
"name":
category,
"sub_categories":
random.sample(self.categories, random.randint(2, 4))
}
with connexion.cursor() as cursor:
sql = """INSERT INTO categorie_pizza (nom_categorie)
VALUES (%s)"""
cursor.execute(sql, (category_info["name"]))
connexion.commit()
with connexion.cursor() as cursor:
sql = """SELECT id FROM categorie_pizza
WHERE nom_categorie = %s"""
cursor.execute(sql, (category_info["name"]))
result = cursor.fetchone()
category_info["id"] = result["id"]
for sub_category in category_info["sub_categories"]:
with connexion.cursor() as cursor:
sql = """INSERT INTO sous_cat_par_cat (id_cat_parent, id_cat_enfant)
SELECT %s, id AS id_enfant
FROM categorie_pizza
WHERE nom_categorie = %s"""
cursor.execute(sql,
(category_info['id'], sub_category))
connexion.commit()
def _get_pizza_informations(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id, prix_unitaire FROM pizza"""
cursor.execute(sql)
pizza_infos = cursor.fetchall()
return pizza_infos
def _get_address_ids(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id FROM adresse"""
cursor.execute(sql)
address_ids = cursor.fetchall()
return address_ids
def _get_payment_method_ids(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id FROM mode_reglement"""
cursor.execute(sql)
payment_method_ids = cursor.fetchall()
return payment_method_ids
def _get_10_orders(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id_commande, date_debut_operation FROM evolution_preparation
ORDER BY RAND() LIMIT 10"""
cursor.execute(sql)
orders = cursor.fetchall()
return orders
def set_delivery_types(self):
with SQLconnexion() as connexion:
for delivery_type in self.delivery_types:
with connexion.cursor() as cursor:
sql = """INSERT INTO type_livraison (type_livraison)
VALUES (%s)"""
cursor.execute(sql, (delivery_type))
connexion.commit()
def _get_category_ids_association(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id_cat_parent, id_cat_enfant FROM sous_cat_par_cat"""
cursor.execute(sql)
category_associations = cursor.fetchall()
return category_associations
def set_order_status(self):
with SQLconnexion() as connexion:
for status in self.order_status:
with connexion.cursor() as cursor:
sql = """INSERT INTO statut_commande (statut_commande)
VALUES (%s)"""
cursor.execute(sql, (status))
connexion.commit()
def _get_ingredient_ids(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """SELECT id FROM ingredient"""
cursor.execute(sql)
ingredient_ids = cursor.fetchall()
return ingredient_ids
def set_payment_status(self):
with SQLconnexion() as connexion:
for status in self.payment_status:
with connexion.cursor() as cursor:
sql = """INSERT INTO statut_paiement (statut_paiement)
VALUES (%s)"""
cursor.execute(sql, (status))
connexion.commit()
def set_payment_methods(self):
with SQLconnexion() as connexion:
for payment_method in self.payment_methods:
with connexion.cursor() as cursor:
sql = """INSERT INTO mode_reglement (mode_reglement)
VALUES (%s)"""
cursor.execute(sql, (payment_method))
connexion.commit()
def set_one_customer(self, user_id, delivery_address_id,
invoice_address_id):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """INSERT INTO client (id_utilisateur, id_adresse_livraison, id_adresse_facturation)
VALUES (%s, %s, %s )"""
cursor.execute(
sql, (user_id, delivery_address_id, invoice_address_id))
connexion.commit()
def set_user(self, user_elements):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """INSERT INTO utilisateur (nom, prenom, mail, password, date_creation)
VALUES (%s, %s, %s, %s, %s)"""
cursor.execute(
sql, (user_elements["name"], user_elements["first_name"],
user_elements["mail"], user_elements["password"],
user_elements["creation_date"]))
connexion.commit()
def set_role(self):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
for role in self.roles:
sql = """SELECT nom FROM role WHERE nom = %s"""
cursor.execute(sql, (role))
result = cursor.fetchone()
if not result:
sql = "INSERT INTO role (nom) VALUES (%s)"
cursor.execute(sql, (role))
connexion.commit()
def set_address(self, address_elements):
with SQLconnexion() as connexion:
with connexion.cursor() as cursor:
sql = """INSERT INTO adresse
(destinataire, voie, code_postal, ville,
renseignement_supplementaire, telephone, numero_voie)
VALUES (%s, %s, %s, %s, %s, %s, %s)"""
cursor.execute(
sql,
(address_elements["person"], address_elements["street"],
address_elements["zip_code"], address_elements["city"],
address_elements["info_bonus"],
address_elements["phone_number"],
address_elements["street_number"]))
connexion.commit()
def set_ingredients(self):
with SQLconnexion() as connexion:
for ingredient in self.ingredients:
ingredient_info = {
"name": ingredient,
"price_per_unit": round(random.uniform(1.0, 15.0), 2),
"global_quantity": random.randint(1, 50)
}
with connexion.cursor() as cursor:
sql = """INSERT INTO ingredient (nom, prix_unitaire, quantite_globale)
VALUES (%s, %s, %s)"""
cursor.execute(sql, (ingredient_info["name"],
ingredient_info["price_per_unit"],
ingredient_info["global_quantity"]))
connexion.commit()
def set_pizza(self):
# We get information already inserted
ingredient_ids = self._get_ingredient_ids()
recipe_ids = self._get_recipe_ids()
category_associations = self._get_category_ids_association()
# For each pizza in the list, we insert all the information
for pizza in self.pizzas:
## WE SET UP THE NECESSARY DATAS FOR EACH PIZZA ##
pizza_info = {
"id": "",
"name": pizza,
"description": self.fake_data.sentence(15),
"price_per_unit": round(random.uniform(9.90, 16.90), 2),
"recipe_id": recipe_ids.pop(), # get a {'id': X}
"ingredient_ids":
random.sample(ingredient_ids,
3), # get a ({'id': X}, {'id': X}, {'id': X})
"category_associations": random.sample(category_associations,
2)
}
# We define a simple list of ingedient ids from the sample
simple_ingredient_ids = [
ingredient['id'] for ingredient in pizza_info["ingredient_ids"]
]
# We defin a coherent and simple list of category_ids
all_category_ids = [category['id_cat_parent'] for category in pizza_info["category_associations"]] + \
[category['id_cat_enfant'] for category in pizza_info["category_associations"]]
simple_category_ids = list(set(all_category_ids))
## WE START THE INSERTION ##
with SQLconnexion() as connexion:
# We had basic pizza info into pizza Table
with connexion.cursor() as cursor:
sql = """INSERT INTO pizza
(nom, description, prix_unitaire, id_recette)
VALUES (%s, %s, %s, %s)"""
cursor.execute(
sql, (pizza_info["name"], pizza_info["description"],
pizza_info["price_per_unit"],
pizza_info['recipe_id']["id"]))
connexion.commit()
# We get the pizza id we just created thanks to th insertion
with connexion.cursor() as cursor:
sql = """SELECT id FROM pizza ORDER BY id DESC LIMIT 1"""
cursor.execute(sql)
result = cursor.fetchone()
pizza_info["id"] = result["id"]
# For each category ids, we insert the pizza_id and category_id associations
for category_id in simple_category_ids:
with connexion.cursor() as cursor:
sql = """INSERT INTO categorie_par_pizza (id_pizza, id_categorie)
VALUES (%s, %s)"""
cursor.execute(sql, (pizza_info["id"], category_id))
connexion.commit()
# For each ingredients_ids, we link ingredients to the pizza and set up arbitrary the global_quantity
for ingredient_id in simple_ingredient_ids:
necessary_quantity = round(random.random(), 2)
with connexion.cursor() as cursor:
sql = """INSERT INTO quantite_ingredient_par_pizza
(id_ingredient, id_pizza, quantite_necessaire)
VALUES (%s, %s, %s)"""
cursor.execute(sql, (ingredient_id, pizza_info["id"],
necessary_quantity))
connexion.commit()
