def insertingredients(name, ingredients):
cursor = mysql.cursor()
cursor.callproc("GetRecipeId", [str(name)])
result = cursor.fetchall()
cursor.close()
mysql.commit()
recipes = []
for row in result:
recipes.append(row)
'''
for i in range (0,len(ingredients)-1):
cursor= mysql.cursor()
cursor.callproc("GetIngredientsId",[str(ingredients[i])])
result = cursor.fetchall()
cursor.close()
mysql.commit()
ingredients = []
for row in result:
ingredients.append(row)
'''
cursor = mysql.cursor()
for i in range(0, len(ingredients)):
insert_stmt = ("INSERT INTO Contains(recipe_id,ingredients_id) "
"VALUES (%s, %s)")
data = (recipes[0], ingredients[i])
cursor.execute(insert_stmt, data)
mysql.commit()
cursor.close()
def add_recipie():
form = RecipeForm()
if request.method == 'GET':
cursor = mysql.cursor()
cursor.execute('''select ingredients_id, product_name from ingredients where email="%s"''' % (current_user.email))
results = cursor.fetchall()
choices = [(ingredient[0], ingredient[1].capitalize()) for ingredient in results]
form.ingredients.choices = choices
return render_template('add_recipe.html', form=form)
else:
cursor = mysql.cursor()
recipe_name = form.recipe_name.data
calories = form.calories.data
image = form.image.data
filename = image.filename
image.save(os.path.join('app/static/images', filename))
url = 'app/static/images/%s' % (filename)
steps = form.instructions.data
instruction1 = steps[0]['instruction1']
instruction2 = steps[0]['instruction2']
instruction3 = steps[0]['instruction3']
instruction4 = steps[0]['instruction4']
cursor.execute('''insert into recipes (email, recipe_name, calories, image_url) values ("%s", "%s", %d, "%s")''' % (current_user.email, recipe_name, calories, url))
cursor.execute('''insert into instructions (recipe_id, order_of_action, action) values (LAST_INSERT_ID(), 1, "%s")''' % instruction1)
cursor.execute('''insert into instructions (recipe_id, order_of_action, action) values (LAST_INSERT_ID(), 2, "%s")''' % instruction2)
cursor.execute('''insert into instructions (recipe_id, order_of_action, action) values (LAST_INSERT_ID(), 3, "%s")''' % instruction3)
cursor.execute('''insert into instructions (recipe_id, order_of_action, action) values (LAST_INSERT_ID(), 4, "%s")''' % instruction4)
mysql.commit()
return 'works'
def recipe_details(recipeid):
# cursor = mysql.cursor()
# insert_stmt = ("SELECT * FROM RECIPE WHERE recipe_id = "+recipeid)
# recipe = cursor.execute(insert_stmt)
imagenames = get_uploaded_images()
cursor = mysql.cursor()
cursor.callproc("GetRecipeInfo", [str(recipeid)])
result_1 = cursor.fetchall()
cursor.close()
cursor = mysql.cursor()
cursor.callproc("GetIngredients", [str(recipeid)])
result_2 = cursor.fetchall()
cursor.close()
cursor = mysql.cursor()
cursor.callproc("GetInstructionsInfo", [str(recipeid)])
result_3 = cursor.fetchall()
cursor.close()
cursor = mysql.cursor()
cursor.callproc("GetDate", [str(recipeid)])
result_5 = cursor.fetchall()
cursor.close()
date = []
recipes = []
ingredients = []
instructions = []
ingredient_name = []
for row in result_1:
recipes.append(row)
for row in result_3:
instructions.append(row)
for row in result_2:
ingredients.append(row)
for row in result_5:
date.append(row)
for i in range(0, len(ingredients)):
cursor = mysql.cursor()
cursor.callproc("GetIngredientsInfo", [str(ingredients[i][0])])
result_4 = cursor.fetchall()
cursor.close()
for row in result_4:
ingredient_name.append(row)
# print instructions
return render_template("recipe_detail.html",
recipes=recipes,
instructions=instructions,
ingredient_name=ingredient_name,
imagenames=imagenames,
date=date)
def insertinstructions(name, instruction1, instruction2, instruction3,
instruction4):
# cursor = mysql.cursor()
# cursor.callproc("GetInstructionsId",[str(instruction1)])
# result_1 = cursor.fetchall()
# cursor.close()
# cursor = mysql.cursor()
# cursor.callproc("GetInstructionsId",[str(instruction2)])
# result_2 = cursor.fetchall()
# cursor.close()
# cursor = mysql.cursor()
# cursor.callproc("GetInstructionsId",[str(instruction3)])
# result_3 = cursor.fetchall()
# cursor.close()
# cursor = mysql.cursor()
# cursor.callproc("GetInstructionsId",[str(instruction4)])
# result_4 = cursor.fetchall()
# cursor.close()
cursor = mysql.cursor()
cursor.callproc("GetRecipeId", [str(name)])
result = cursor.fetchall()
cursor.close()
mysql.commit()
recipes = []
instructions = []
for row in result:
recipes.append(row)
# print (recipes[0])
cursor = mysql.cursor()
insert_stmt = (
"INSERT INTO Instructions(recipe_id,task,instruction_order) "
"VALUES (%s, %s, %s)")
data = (recipes[0], instruction1, "1")
cursor.execute(insert_stmt, data)
insert_stmt = (
"INSERT INTO Instructions(recipe_id,task,instruction_order) "
"VALUES (%s, %s, %s)")
data = (recipes[0], instruction2, "2")
cursor.execute(insert_stmt, data)
insert_stmt = (
"INSERT INTO Instructions(recipe_id,task,instruction_order) "
"VALUES (%s, %s, %s)")
data = (recipes[0], instruction3, "3")
cursor.execute(insert_stmt, data)
insert_stmt = (
"INSERT INTO Instructions(recipe_id,task,instruction_order) "
"VALUES (%s, %s, %s)")
data = (recipes[0], instruction4, "4")
cursor.execute(insert_stmt, data)
mysql.commit()
cursor.close()
def insert_date(user, name):
cursor = mysql.cursor()
cursor.callproc("GetRecipeId", [str(name)])
result = cursor.fetchall()
cursor.close()
mysql.commit()
recipes = []
for row in result:
recipes.append(row)
insert_stmt = ("INSERT INTO Uploads(user_name,recipe_id,upload_date) "
"VALUES (%s, %s, NOW())")
data = (user, recipes[0])
cursor = mysql.cursor()
cursor.execute(insert_stmt, data)
def signup():
form = SignUpForm(csrf_enabled=False)
choices = [(str(x),x) for x in reversed(range(1900,2004))]
form.year_of_birth.choices = choices
if request.method == 'POST':
if form.validate_on_submit():
email = form.email.data
password = form.password.data
firstname = form.firstname.data
lastname = form.lastname.data
year_of_birth = form.year_of_birth.data
try:
cur = mysql.cursor()
cur.execute('''insert into users values ('%s', '%s', '%s','%s', %d)''' % (email , password,firstname, lastname, int(year_of_birth)))
mysql.commit()
user = User(email, password, firstname, lastname, year_of_birth)
login_user(user)
return redirect(url_for('add_ingredient'))
except Exception as e:
flash(str(e))
return render_template('signup.html', form=form)
else:
flash('Error signing up')
render_template('signup.html' , form=form)
else:
return render_template('signup.html', form=form)
def login():
form = login_Form()
if request.method == 'POST':
if form.validate_on_submit():
user_name = form.user_name.data
password = form.password.data
try:
cursor = mysql.cursor()
cursor.execute(
'''select * from User where user_name="%s" and hash_password="******"'''
% (user_name, password))
result = cursor.fetchall()
if not result:
flash('Invalid login credentials')
return render_template('login.html', form=form)
else:
user = User(result[0][0], result[0][1])
login_user(user)
#flash('success')
return redirect(url_for('index'))
except Exception as e:
return str(e)
else:
return render_template('login.html', form=form)
else:
return render_template('login.html', form=form)
def insert_kitchen(user_name):
insert_stmt = ("INSERT INTO Kitchen(user_name) " "VALUES (%s)")
data = (str(user_name))
cursor = mysql.cursor()
cursor.execute(insert_stmt, data)
mysql.commit()
cursor.close()
def add_ingredient():
form = IngredientForm(csrf_enabled=False)
cursor = mysql.cursor()
cursor.execute('''select unit_name from units''')
units = cursor.fetchall()
choices = [(unit[0], unit[0].capitalize()) for unit in units]
form.unit_name.choices = choices
if request.method == 'POST':
if form.validate_on_submit():
product_name = form.product_name.data
calories_per_unit = form.calories_per_unit.data
stock = form.stock.data
cost = form.cost.data
unit_name = form.unit_name.data
email = current_user.email
try:
cursor.execute('''insert into ingredients (product_name, unit_name, email, calories_per_unit, stock, cost) values ("%s", "%s", "%s", %d, %d, %0.2f)''' % (product_name, unit_name, email, calories_per_unit, stock, cost))
mysql.commit()
return 'Added'
except Exception as e:
flash(str(e))
return render_template('add_ingredient.html', form=form)
else:
flash('Error adding ingredient')
return render_template('add_ingredient.html', form=form)
else:
return render_template('add_ingredient.html', form=form)
def register():
error = None
form = reg_Form()
if request.method == 'POST':
if form.validate_on_submit():
first_name = form.first_name.data
last_name = form.last_name.data
user_name = form.user_name.data
email = form.email.data
password = form.password.data
conf_password = form.conf_password.data
phone = form.phone.data
diet = form.diet.data
# health_info=form.health_info.data
#diet="Normal"
health_info = form.health_info.data
try:
insert_stmt = ("INSERT INTO User(user_name, hash_password) "
"VALUES (%s, %s)")
data = (user_name, password)
cursor = mysql.cursor()
cursor.execute(insert_stmt, data)
mysql.commit()
cursor.close()
try:
insert_profile(first_name, user_name, last_name, email,
phone, diet, health_info)
except Exception as e:
print e
db.session.rollback()
flash(str(e))
return render_template('register.html',
error=error,
form=form)
try:
#insert_kitchen(user_name)
pass
except Exception as e:
print e
db.session.rollback()
flash(str(e))
return render_template('register.html',
error=error,
form=form)
user = User(user_name=user_name, hash_password=password)
login_user(user)
return redirect(url_for('index'))
except Exception as e:
print e
db.session.rollback()
flash(str(e))
return render_template('register.html', error=error, form=form)
else:
flash('Error signing up')
render_template('register.html', error=error, form=form)
else:
return render_template('register.html', form=form)
def get_task(self, user_id):
cursor = mysql.cursor()
cursor.execute(
"select fk_task_id from user_has_task where fk_user_id = " +
str(user_id) + ";")
tasks_id = cursor.fetchall()
cursor.close()
return tasks_id
def kitchen():
cursor = mysql.cursor()
cursor.execute('''select product_name, calories_per_unit, stock from ingredients where email="%s"''' % (current_user.email))
result = cursor.fetchall()
ingredients = []
if result:
ingredients = [{'product_name': ingredient[0], 'calories': ingredient[1], 'stock': ingredient[2]} for ingredient in result]
return render_template('kitchen.html', ingredients=ingredients)
def check_username(self, username):
cursor = mysql.cursor()
cursor.execute("SELECT username FROM user;")
users = cursor.fetchall()
cursor.close()
for use in users:
if use[0] == username:
return True
return False
def has_task(self, user_id, task_id):
cursor = mysql.cursor()
cursor.execute("select * from user_has_task where fk_task_id = " +
str(task_id) + " and fk_user_id = " + str(user_id) +
";")
if cursor.fetchall():
return True
else:
return False
def execute_user_feed(self, query):
logger.debug("Executing query %s", query)
try:
cursor = mysql.cursor()
cursor.execute(query)
return True, cursor.fetchall()
except Exception as e:
logger.exception("Exception in running query %s", query)
return False, None
def check_task(self, title):
cursor = mysql.cursor()
cursor.execute("SELECT task_id FROM task;")
tasks = cursor.fetchall()
cursor.close()
for use in tasks:
if use[0] == title:
return True
return False
def get_id(self, username):
cursor = mysql.cursor()
cursor.execute("select user_id FROM user WHERE username = '******';")
use = cursor.fetchall()[0][0]
cursor.close()
if use:
return use
else:
return -1
def insert_profile(first_name, user_name, last_name, email, phone, diet,
health_info):
insert_stmt = (
"INSERT INTO Profile(first_name, user_name, last_name, email, phone, diet, health_info) "
"VALUES (%s, %s, %s, %s, %s, %s, %s)")
data = (first_name, user_name, last_name, email, phone, diet, health_info)
cursor = mysql.cursor()
cursor.execute(insert_stmt, data)
mysql.commit()
cursor.close()
def execute_select_check_sum(self, query):
logger.debug("Running query %s for checking sum total points", query)
try:
cursor = mysql.cursor()
cursor.execute(query)
return True, cursor.fetchone()["day_total"]
except Exception as e:
logger.exception(
"Exception in getting the sum total for the day for the user.")
return False, None
def check_password(self, username, password):
cursor = mysql.cursor()
cursor.execute("SELECT username, password FROM user;")
users = cursor.fetchall()
cursor.close()
for use in users:
if use[0] == username and use[1] == password:
return True
elif use[0] == username:
return False
return False
def add_recipe():
error = None
form = recipe_Form()
if request.method == 'POST':
if form.validate_on_submit():
name = form.name.data
calorie = form.calorie.data
servings = form.servings.data
prep_time = form.prep_time.data
cook_time = form.cook_time.data
#diet_type="Normal"
diet_type = form.diet_type.data
instruction1 = form.instruction1.data
instruction2 = form.instruction2.data
instruction3 = form.instruction3.data
instruction4 = form.instruction3.data
ingredients = form.ingredients.data
#flash(ingredients)
image = request.files['photo']
if allowed_file(image.filename):
filename = secure_filename(image.filename)
image.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
else:
flash('Incorrect File Format', 'danger')
return redirect(url_for('home'))
insert_stmt = (
"INSERT INTO Recipe(name,calorie,servings,cook_time,prep_time,diet_type) "
"VALUES (%s, %s, %s, %s, %s, %s)")
data = (name, calorie, servings, cook_time, prep_time, diet_type)
cursor = mysql.cursor()
cursor.execute(insert_stmt, data)
insert_date(current_user.user_name, name)
mysql.commit()
cursor.close()
insertinstructions(name, instruction1, instruction2, instruction3,
instruction4)
insertingredients(name, ingredients)
flash(ingredients)
flash(len(ingredients))
flash('success')
return redirect(url_for('recipes'))
else:
flash(form.errors)
flash('Error entering')
render_template('add_recipe.html', error=error, form=form)
return render_template('add_recipe.html', form=form)
def load_user(email):
try:
cursor = mysql.cursor()
cursor.execute('''select * from users where email="%s"''' % (email))
result = cursor.fetchall()
if not result:
return None
user = User(result[0][0], result[0][1], result[0][2], result[0][3], result[0][4])
return user
except Exception as e:
print str(e)
return None
def insert(self, data):
logger.debug("Inserting data to mysql")
try:
query = build_insert_query(data)
cursor = mysql.cursor()
cursor.execute(query)
# if res == 0:
# return False
mysql.commit()
return True
except Exception as e:
logger.exception("Exception in inserting data to the mysql server")
return False
def load_user(user_name):
try:
cursor = mysql.cursor()
cursor.execute('''select * from User where user_name="%s"''' %
(user_name))
result = cursor.fetchall()
if not result:
return None
user = User(result[0][0], result[0][1])
return user
except Exception as e:
print str(e)
return None
def shoppinglist():
try:
if request.method == "GET":
connection = mysql.cursor()
result = connection.execute(
"Select Ingredients.name From Ingredients where ingredients_id in (Select ingredients_id from Stores where quantity<5"
)
List = []
for row in result:
List.append(row)
connection.close()
return render_template("shoppingList.html", lst=List)
except Exception as e:
return (str(e))
return render_template('shoppinglist.html')
def edit_task(self, id, Title, Status, Begin, End):
cursor = mysql.cursor()
if Title:
cursor.execute("UPDATE task SET title='" + Title +
"' WHERE task_id = " + id + ";")
if Status:
cursor.execute("UPDATE task SET status='" + Status +
"' WHERE task_id = " + id + ";")
if Begin:
cursor.execute("UPDATE task SET begin='" + Begin +
"' WHERE task_id = " + id + ";")
if End:
cursor.execute("UPDATE task SET end='" + End +
"' WHERE task_id = " + id + ";")
mysql.commit()
cursor.close()
def recipes():
cursor = mysql.cursor()
form = recipe_Form(request.form)
cursor.callproc("GetRecipes", [str(form.name.data)])
result = cursor.fetchall()
cursor.close()
mysql.commit()
recipes = []
for row in result:
recipes.append(row)
# print recipes
if request.method == 'POST':
if result is not None:
return render_template("recipes.html", form=form, recipes=recipes)
else:
return render_template("recipes.html", form=form)
class recipe_Form(FlaskForm):
name = StringField("Recipe Name",
validators=[DataRequired('Enter a recipe_name')])
calorie = IntegerField("Calories",
validators=[DataRequired('Enter a calories')])
servings = IntegerField("Serving",
validators=[DataRequired('Enter serving')])
prep_time = StringField("Preparation Time",
validators=[DataRequired('Enter prep time')])
cook_time = StringField("Cook Time",
validators=[DataRequired('Enter cook time')])
instruction1 = StringField('Step 1',
validators=[DataRequired('Enter step 1')])
instruction2 = StringField('Step 2',
validators=[DataRequired('Enter step 2')])
instruction3 = StringField('Step 3', validators=[Optional()])
instruction4 = StringField('Step 4', validators=[Optional()])
diet_type = SelectField('Diet',
choices=[('S', 'Select Diet'),
('Atkins', 'Atkins'),
('Normal', 'Normal'),
('Vegetarian', 'Vegetarian'),
('Vegan', 'Vegan')],
validators=[DataRequired('Enter preferred diet')])
# health_info = StringField('Health',validators=[DataRequired('Enter your health information')])
photo = FileField(
'images',
validators=[
FileRequired(),
FileAllowed(['jpg', 'png', 'jpeg'],
'Only jpg,jpeg and png images can be uploaded')
])
submit = SubmitField("Submit")
cursor = mysql.cursor()
cursor.execute("SELECT * FROM Ingredients")
result = cursor.fetchall()
choices = [('S', 'Select ingredients')]
for row in result:
choices.append((str(int(row[0])), row[1])) #gvh
# print choices
# ingredients =SelectMultipleField('Ingredients',choices=[('S','Select ingredients'),('Apple','Apple'),('Banana','Banana'), ('Chicken','Chicken'),('Egg','Egg'),('Flour','Flour')],validators=[DataRequired('Enter ingredients')])
ingredients = SelectMultipleField(
'Ingredients',
choices=choices,
validators=[DataRequired('Enter ingredients')])
def create_task(self, Title, Status, Begin, End):
cursor = mysql.cursor()
if Title:
cursor.execute("INSERT INTO task SET title = '" + Title + "';")
else:
return False
if Begin:
cursor.execute("UPDATE task SET begin = '" + Begin +
"' WHERE title=Title ;")
if End:
cursor.execute("UPDATE task SET end = '" + End +
"' WHERE title=Title ;")
if Status:
cursor.execute("UPDATE task SET status = '" + Status +
"' WHERE title=Title ;")
mysql.commit()
cursor.close()
return True
def login():
form = LoginForm(csrf_enabled=False)
if request.method == 'POST':
if form.validate_on_submit():
email = form.email.data
password = form.password.data
try:
cursor = mysql.cursor()
cursor.execute('''select * from users where email="%s" and password="******"''' % (email , password))
result = cursor.fetchall()
if not result:
flash('Invalid login credentials')
return render_template('login.html', form=form)
else:
user = User(result[0][0], result[0][1], result[0][2], result[0][3], result[0][4])
login_user(user)
return 'LoggedIn'
except Exception as e:
return str(e)
else:
return render_template('login.html' , form=form)
else:
return render_template('login.html', form=form)
