def member_home():
user_id = session.get('user_id')
con = db.get_db()
cur = con.cursor()
cur.execute(
"""SELECT s.name as shake_name, h.rating as shake_rating, h.comment as shake_comment, h.this_shake as shake_freq
FROM shakes s
JOIN has_had h on s.id = h.shake_id
WHERE h.user_id = %s
ORDER BY shake_rating DESC
LIMIT 5""", (user_id, ))
member_top_5 = cur.fetchall()
cur.execute(
"""SELECT COUNT(*)
FROM has_had
WHERE user_id = %s;""", (user_id, ))
diff_shakes = cur.fetchone()
cur.execute(
"""SELECT SUM(this_shake)
FROM has_had
WHERE user_id = %s;""", (user_id, ))
total_shakes = cur.fetchone()
cur.execute("""SELECT id, name FROM shakes WHERE available = True""")
all_shakes = cur.fetchall()
cur.execute(
"""SELECT s.id, s.name FROM shakes s
JOIN has_had h on s.id = h.shake_id
WHERE h.user_id = %s""", (user_id, ))
had_shakes = cur.fetchall()
cur.execute(
"""SELECT id, name FROM shakes s
WHERE id <> ALL (SELECT s.id FROM shakes s
JOIN has_had h on s.id = h.shake_id
WHERE h.user_id = %s)""", (user_id, ))
havent_had = cur.fetchall()
cur.close()
con.close()
return render_template("layouts/member/userHome.html",
member_top_5=member_top_5,
diff_shakes=diff_shakes,
total_shakes=total_shakes,
all_shakes=all_shakes,
had_shakes=had_shakes,
havent_had=havent_had)
def store_home():
now = datetime.now()
day = now.strftime("%Y-%m-%d")
con = db.get_db()
cur = con.cursor()
cur.execute("""SELECT SUM(daily_total) FROM daily""")
total_shakes = cur.fetchone()
cur.execute("""SELECT SUM(daily_total)
FROM daily
WHERE day = %s""",
(day,))
shakes_today = cur.fetchone()
cur.close()
con.close()
return render_template("layouts/store/store.html", total_shakes=total_shakes, shakes_today=shakes_today)
def member_info():
con = db.get_db()
cur = con.cursor()
cur.execute("""SELECT COUNT(*) FROM users""")
total_users = cur.fetchone()
cur.execute(
"""SELECT first_name, last_name, email, phone, dob, register_date, last_login, total_login
FROM users
WHERE role = 'member'
ORDER BY total_login DESC
LIMIT 10""")
user_login_freq = cur.fetchall()
cur.execute("""SELECT h.user_id, u.email, SUM(h.this_shake) AS total
FROM has_had h
JOIN (SELECT u.id, u.email
FROM users u) u ON u.id = h.user_id
GROUP BY h.user_id, u.email
ORDER BY total DESC
LIMIT 10""")
user_shake_freq = cur.fetchall()
cur.execute(
"""SELECT first_name, last_name, email, phone, dob, register_date, last_login, total_login FROM users
ORDER BY last_name ASC""")
all_users = cur.fetchall()
cur.execute(
"""SELECT first_name, last_name, email, phone, dob, register_date, last_login, total_login FROM users
WHERE phone IS NOT NULL
ORDER BY last_name ASC""")
user_phone = cur.fetchall()
cur.close()
con.close()
return render_template("layouts/admin/member_info.html",
total_users=total_users,
user_login_freq=user_login_freq,
user_shake_freq=user_shake_freq,
all_users=all_users,
user_phone=user_phone)
def store_shakes():
like = '(a|b|c|d)%'
like2 = '(e|f|g|h)%'
like3 = '(i|j|k|l)%'
like4 = '(m|n|o|p)%'
like5 = '(q|r|s|t|u)%'
like6 = '(v|w|x|y|z)%'
def getShakes(x):
cur.execute("""SELECT * FROM shakes
WHERE lower(name) SIMILAR TO %s
AND available = True
ORDER BY name ASC""",
(x,))
shakes = cur.fetchall()
return shakes
con = db.get_db()
cur = con.cursor()
shakes = getShakes(like)
shakes1 = getShakes(like2)
shakes2 = getShakes(like3)
shakes3 = getShakes(like4)
shakes4 = getShakes(like5)
shakes5 = getShakes(like6)
if request.method == 'POST':
now = datetime.now()
day = now.strftime("%Y-%m-%d")
shake_id = request.form.getlist('shake_id[]')
nums = request.form.getlist('num_shakes[]')
pairs = []
for i, n in enumerate(nums):
if n != "":
pairs.append([int(shake_id[i]), int(n)])
for pair in pairs:
shake_id = pair[0]
total = pair[1]
cur = con.cursor()
cur.execute("""SELECT * FROM daily
WHERE shake_id = %s
AND day = %s""",
(shake_id, day))
any = cur.fetchone()
cur.close()
if any is not None:
date_id = any[0]
total = int(any[3]) + total
cur = con.cursor()
cur.execute("""UPDATE daily SET daily_total = %s
WHERE id = %s""",
(total, date_id))
g.db.commit()
cur.close()
else:
cur = con.cursor()
cur.execute("""INSERT INTO daily (day, shake_id, daily_total)
VALUES (%s,%s,%s)""",
(day, shake_id, total))
g.db.commit()
cur.close()
cur.close()
con.close()
return render_template("layouts/store/store_shake.html", shakes=shakes,
shakes1=shakes1, shakes2=shakes2, shakes3=shakes3,
shakes4=shakes4, shakes5=shakes5)
def admin_store_reports():
now = datetime.datetime.now()
day = now.strftime("%Y-%m-%d")
con = db.get_db()
cur = con.cursor()
cur.execute(
"""SELECT d.shake_id, s.name as name, SUM(d.daily_total) AS total_sales
FROM daily d
JOIN shakes s on s.id = d.shake_id
WHERE day BETWEEN %s and %s
GROUP BY name, shake_id
ORDER BY total_sales DESC""", (day, day))
shakes = cur.fetchall()
cur.execute(
"""SELECT SUM(daily_total)
FROM daily
WHERE day BETWEEN %s AND %s""", (day, day))
total = cur.fetchone()
cur.close()
day = getDate(day)
if request.method == 'POST':
sDate = request.form['sDate']
eDate = request.form['eDate']
con = db.get_db()
cur = con.cursor()
cur.execute(
"""SELECT d.shake_id, s.name as name, SUM(d.daily_total) AS total_sales
FROM daily d
JOIN shakes s on s.id = d.shake_id
WHERE day BETWEEN %s and %s
GROUP BY name, shake_id
ORDER BY total_sales DESC""", (sDate, eDate))
shakes = cur.fetchall()
cur.execute(
"""SELECT SUM(daily_total)
FROM daily
WHERE day BETWEEN %s AND %s""", (sDate, eDate))
total = cur.fetchone()
cur.close()
con.close()
sDate = getDate(sDate)
eDate = getDate(eDate)
return render_template("layouts/admin/store_reports.html",
shakes=shakes,
sDate=sDate,
eDate=eDate,
total=total)
con.close()
return render_template("layouts/admin/store_reports.html",
shakes=shakes,
sDate=day,
eDate=day,
total=total)
def admin_shake_menu():
form = MakeUnavail(request.form)
form1 = MakeAvail(request.form)
form2 = AddItem(request.form)
print("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
con = db.get_db()
cur = con.cursor()
cur.execute(
"""SELECT * FROM shakes WHERE available = True ORDER BY shake_group ASC"""
)
all_shakes = cur.fetchall()
cur.execute("""SELECT * FROM shakes WHERE available = False""")
not_shakes = cur.fetchall()
if form.validate_on_submit():
shake_id1 = form.shake_id1.data
switch = form.switch_avail.data
print(shake_id1, switch)
cur.execute(
"""UPDATE shakes
SET available = False
WHERE id = %s""", (shake_id1, ))
g.db.commit()
cur.close()
con.close()
return redirect(url_for('admin.admin_shake_menu'))
if form1.validate_on_submit():
shake_id2 = form1.shake_id2.data
switch = form1.switch_unavail.data
print(shake_id2, switch)
cur.execute(
"""UPDATE shakes
SET available = True
WHERE id = %s""", (shake_id2, ))
g.db.commit()
cur.close()
con.close()
return redirect(url_for('admin.admin_shake_menu'))
if form2.validate_on_submit():
item_name = form2.item_name.data
description = form2.description.data
group = form2.group.data
print(item_name, description, group)
cur.execute(
"""INSERT INTO shakes (name, shake_group, description, available)
VALUES (%s,%s,%s, True)""",
(item_name, group, description))
g.db.commit()
cur.close()
con.close()
return redirect(url_for('admin.admin_shake_menu'))
cur.close()
con.close()
return render_template("layouts/admin/shake_menu.html",
all_shakes=all_shakes,
not_shakes=not_shakes,
form2=form2,
form=form,
form1=form1)
def member_add_shake():
def getShakes(x):
cur.execute(
"""SELECT * FROM shakes
WHERE shake_group = %s
AND available = True
ORDER BY name ASC""", (x, ))
shakes = cur.fetchall()
return shakes
con = db.get_db()
cur = con.cursor()
group_1 = getShakes(1)
group_2 = getShakes(2)
group_3 = getShakes(3)
group_4 = getShakes(4)
group_5 = getShakes(5)
group_6 = getShakes(6)
group_7 = getShakes(7)
group_8 = getShakes(8)
group_9 = getShakes(9)
if request.method == 'POST':
user_id = session.get('user_id')
shake_id = request.form.getlist('shake_id[]')
ratings = request.form.getlist('rating[]')
comments = request.form.getlist('comment[]')
sets = []
for i, n in enumerate(ratings):
if n != "":
sets.append([int(shake_id[i]), int(n), comments[i]])
for set in sets:
shake_id = set[0]
rating = set[1]
comment = set[2]
cur = con.cursor()
cur.execute(
"""SELECT * FROM has_had
WHERE shake_id = %s
AND user_id = %s""", (shake_id, user_id))
any = cur.fetchone()
if any is not None:
had_id = any[0]
total = any[5] + 1
cur.execute(
"""UPDATE has_had SET this_shake = %s, rating = %s, comment = %s
WHERE id = %s""",
(total, rating, comment, had_id))
g.db.commit()
else:
total = 1
cur.execute(
"""INSERT INTO has_had (user_id, shake_id, rating, comment, this_shake)
VALUES (%s,%s,%s,%s,%s)""",
(user_id, shake_id, rating, comment, total))
g.db.commit()
cur.close()
con.close()
cur.close()
con.close()
return render_template("layouts/member/member_shake.html",
group_1=group_1,
group_2=group_2,
group_3=group_3,
group_4=group_4,
group_5=group_5,
group_6=group_6,
group_7=group_7,
group_8=group_8,
group_9=group_9)
def member_info():
user_id = session.get('user_id')
con = db.get_db()
cur = con.cursor()
cur.execute(
"""SELECT first_name, last_name, email, phone, dob, register_date, last_login
FROM users
WHERE id = %s""", (user_id, ))
user_info = cur.fetchone()
sDate = user_info['register_date'].strftime("%a %m/%d/%Y")
form = MemberUpdate(request.form)
if request.method == 'GET':
form.fName.data = user_info['first_name']
form.lName.data = user_info['last_name']
form.email.data = user_info['email']
form.phone.data = user_info['phone']
form.dob.data = user_info['dob']
if request.method == 'POST':
first_name = form.fName.data
last_name = form.lName.data
email = form.email.data
phone = form.phone.data
dob = form.dob.data
user_id = session.get('user_id')
cur.execute("""SELECT * FROM users WHERE email = %s AND id != %s""",
(email, user_id))
user = cur.fetchone()
if user is not None:
error = "An account with that email already exists"
cur.close()
con.close()
flash(error)
return render_template("layouts/member/member_info.html",
user_info=user_info,
sDate=sDate,
form=form)
elif user is None:
cur.execute(
"""UPDATE users SET first_name = %s, last_name = %s, email= %s, phone = %s, dob =%s
WHERE id = %s""",
(first_name, last_name, email, phone, dob, user_id))
g.db.commit()
cur.close()
con.close()
error = "Account has been updated"
flash(error)
return render_template("layouts/member/member_info.html",
user_info=user_info,
sDate=sDate,
form=form)
cur.close()
con.close()
return render_template("layouts/member/member_info.html",
user_info=user_info,
sDate=sDate,
form=form)