def pswd_reset(request):
#make sure passwords match
if request.form['password1'] != request.form['password2']:
return "NoMatch"
#query database for existing user or email
db = setupDB.connectDB()
query = ('SELECT id, user_name, email '
'FROM user_data '
'WHERE user_name = :username AND email = :email')
user_record = db.query(query,
username=request.form['username'],
email=request.form['email']).first()
if not user_record:
return "NoUser"
#store new password
pw_hash = bcrypt.generate_password_hash(
request.form['password1']).decode('utf-8')
query = """UPDATE user_data
SET pswd=:pwhash
WHERE id=:userid"""
db.query(query, pwhash=pw_hash, userid=user_record.id)
return "Success"
def valid_login(username, password):
#Check username
db = setupDB.connectDB()
query = ('SELECT id, user_name, pswd, user_role '
'FROM user_data '
'WHERE user_name = :user_name')
user_record = db.query(query, user_name=username).first()
if not user_record:
return 0 #user not found
if user_record.user_role == 'user':
#check password
if bcrypt.check_password_hash(user_record.pswd, password):
#Store user info in session
session['uid'] = user_record.id
session['username'] = user_record.user_name
return 1 #user and password validated
else:
return 0 #invalid password
else:
#user is not defined as 'user'
return 0
def activities():
#Grab user info from session variable
currUser = session['uid']
db = setupDB.connectDB()
#query for the user's 5 most recent activities, inner join goals to provide the
#description of the goal associated with the activity logged
user_activities = db.query(
'SELECT activities.id, activities.activity_type, activities.distance, activities.duration, goals.notes FROM activities INNER JOIN goals ON activities.goal_id=goals.id WHERE activities.user_id = :currUser ORDER BY activities.time_created DESC FETCH FIRST 5 ROWS ONLY',
currUser=currUser)
#get all the goals for that user
user_goals = db.query(
'SELECT * FROM goals WHERE goals.user_id = :currUser',
currUser=currUser)
#add results to an array
activities = []
for i in user_activities:
activities.append(i)
#if query is empty, "None" identifies to html to inform user
if (len(activities)) < 1:
user_activities = "None"
#insert new row
if request.method == 'POST':
newType = request.form['newType']
forGoal = request.form['forGoal']
newDist = request.form['newDist']
newDur = request.form['newDur']
goalLookup = db.query(
'SELECT * FROM goals WHERE goals.notes = :forGoal AND goals.user_id = :currUser',
forGoal=forGoal,
currUser=currUser)
#get the id for the goal the user selected
for i in goalLookup:
forGoalID = i.id
db.query(
'INSERT INTO activities (user_id, activity_type, goal_id ,duration, distance) VALUES (:currUser, :newType, :forGoalID, :newDur, :newDist)',
currUser=currUser,
newType=newType,
forGoalID=forGoalID,
newDist=newDist,
newDur=newDur)
return redirect(url_for('activities'))
return render_template('activities.html',
user_activities=user_activities,
user_goals=user_goals)
def unsubscribe():
if request.method == 'POST':
db = setupDB.connectDB()
#Insert main user data into 'user_data' table
query = """DELETE FROM emails
WHERE id = :ID"""
#Perform Query
rows = db.query(query, ID=request.form['id'])
#only defining 'is-error' to get red text for better visibility
flash(('You have been removed from the requested email subscription.'),
'is-error')
return redirect(url_for('user_settings'))
def health():
if not session['username']:
return redirect(url_for('index'))
#Grab user info from session variable
currUser = session['uid']
db = setupDB.connectDB()
#query for the user's most recently created health record
user_health = db.query(
'SELECT * FROM health WHERE user_id=:currUser ORDER BY time_created DESC FETCH FIRST 1 ROW ONLY',
currUser=currUser)
#add the query results to an array
health = []
for i in user_health:
health.append(i)
#if query is empty, "None" identifies to html to inform user
if (len(health) < 1):
health = "None"
#Inserting a new row
if request.method == 'POST':
newWeight = request.form['newWeight']
newHeight = request.form['newHeight']
newWeight = float(newWeight)
newHeight = float(newHeight)
#most be calculated after user has provided their health info
newBmi = ((newWeight / newHeight) / newHeight) * 703
newBmi = round(newBmi, 0)
newBmi = int(newBmi)
newHeight = int(newHeight)
newWeight = int(newWeight)
db.query(
'INSERT INTO health (user_id, height, weight, bmi) VALUES(:currUser, :newHeight, :newWeight, :newBmi)',
newHeight=newHeight,
newWeight=newWeight,
newBmi=newBmi,
currUser=currUser)
return redirect(url_for('health'))
return render_template('health.html', health=health)
def update_userinfo(request):
try:
db = setupDB.connectDB()
query = """UPDATE user_data
SET fname=:first_name, lname=:last_name, email=:user_email, city=:user_city, state=:user_state
WHERE id=:user_id"""
db.query(query,
first_name=request.form['firstname'],
last_name=request.form['lastname'],
user_email=request.form['email'],
user_city=request.form['city'],
user_state=request.form['state'],
user_id=request.form['uid'])
return 1
except:
return 0
def user_settings():
if session['username']:
db = setupDB.connectDB()
query = 'SELECT id, user_name, fname, lname, email, city, state FROM user_data where user_name = :user_name'
user_info = db.query(query, user_name=session['username']).first()
#Check for any email blast schedules to display
query = ('SELECT e.id, e.schedule, e.length '
'FROM emails as e INNER JOIN user_data as u '
'ON u.user_name = :user_name')
schedules = db.query(query, user_name=session['username'])
if len(schedules.as_dict()) == 0:
schedules = None
return render_template('user_settings.html',
userInfo=user_info,
schedules=schedules)
flash('User Profile not recognized. Please log in again.', 'is-error')
return render_template('index.html')
def subscribe():
if request.method == 'POST':
db = setupDB.connectDB()
#Check if selection combination exists first
query = """SELECT COUNT(*) user_count
FROM emails
WHERE user_id = :uid
AND schedule = :schedule
AND length = :len"""
user_count = db.query(query,
uid=request.form['uid'],
schedule=request.form['schedule'],
len=request.form['length']).first().user_count
if user_count > 0:
flash((
'You have already subscribed to the requested summary, but feel free to subscribe to another.'
), 'is-error')
else:
#Insert main user data into 'user_data' table
query = """INSERT INTO emails (user_id, schedule, length)
VALUES (:userID, :freq, :len)"""
#Perform Query
rows = db.query(query,
userID=request.form['uid'],
freq=request.form['schedule'],
len=request.form['length'])
#only defining 'is-error' to get red text for better visibility
flash(('Your email subscription preference has been saved.'),
'is-error')
return redirect(url_for('user_settings'))
def goals():
#Grab user info from session variable
currUser = session['uid']
db = setupDB.connectDB()
#query for the 5 most recently created goals
user_goals = db.query(
'SELECT * FROM goals WHERE user_id = :currUser ORDER BY time_created DESC FETCH FIRST 5 ROW ONLY',
currUser=currUser)
#add the results to an array
goals = []
for i in user_goals:
goals.append(i)
#if the results are empty, "None" tells html to inform user
if (len(goals)) < 1:
user_goals = "None"
#insert new row
if request.method == 'POST':
newNote = request.form['newNote']
newType = request.form['newType']
newDist = request.form['newDist']
newDur = request.form['newDur']
db.query(
'INSERT INTO goals (user_id, activity_type, distance, duration, notes) VALUES (:currUser, :newType, :newDist, :newDur, :newNote)',
currUser=currUser,
newType=newType,
newDist=newDist,
newDur=newDur,
newNote=newNote)
return redirect(url_for('goals'))
return render_template('goals.html', user_goals=user_goals)
import send_email #script for sending email
import setupDB #setup Database connection
#-----------------------------------------------------------------------
#Check database for subscription emails - create and send emails as required
#-----------------------------------------------------------------------
#Day
#Connect to database
db = setupDB.connectDB()
query = """SELECT user_id, schedule, length
FROM emails
WHERE schedule='Daily'"""
#Perform Query
rows = db.query(query)
if not len(rows.as_dict()) == 0:
send_email.makeEmail(rows, db, "Daily")
send_email.log()
def delete_goal(gid):
if request.method == 'POST':
db = setupDB.connectDB()
db.query('DELETE FROM goals WHERE id = :gid', gid=gid)
return redirect(url_for('goals'))
def delete_activity(aid):
if request.method == 'POST':
db = setupDB.connectDB()
db.query('DELETE FROM activities WHERE id = :aid', aid=aid)
#reroute back to the activities page
return redirect(url_for('activities'))
def dashboard():
if not session['username']:
return redirect(url_for('index'))
#query for the username for greeting
#username = db.query('SELECT * FROM user_data WHERE user_name=:currUser', currUser=session['username'])
#ensure that the name is a string
#for i in username:
#name = str(i.user_name)
#Grab user info from session variable
currUser = session['uid']
name = session['username']
db = setupDB.connectDB()
#query for the specific user's health, but only return the most recently
#created row
user_health = db.query(
('SELECT * FROM health WHERE user_id=:currUser '
'ORDER BY time_created DESC FETCH FIRST 1 ROW ONLY'),
currUser=currUser)
#add the results to an array and include the bmi found in the query
#in user_health
health = []
for i in user_health:
health.append(i.bmi)
#if the query result was empty, using "None as an identifier in html to
#tell user that they have not provided health info
if (len(health) < 1):
health = "None"
#query for the specific user's goals, sort and grab only the last 5 goals
#created
user_goals = db.query(
'SELECT * FROM goals WHERE user_id=:currUser ORDER BY time_created DESC FETCH FIRST 5 ROws ONLY',
currUser=currUser)
#add results to an array
goals = []
for i in user_goals:
goals.append(i.notes)
#if query is empty, "None" identifies to html to inform user
if (len(goals) < 1):
goals = "None"
#query for the specific user's activities, sort and grab only the last
#5 goals created
user_activities = db.query(
'SELECT * FROM activities WHERE user_id=:currUser ORDER BY time_created DESC FETCH FIRST 5 ROWS ONLY',
currUser=currUser)
#add results to an array
activities = []
for i in user_activities:
activities.append(i)
#if query is empty, "None" identifies to html to inform user
if (len(activities) < 1):
activities = "None"
return render_template("dashboard.html",
health=health,
goals=goals,
activities=activities,
name=name)
def getCSV(request):
db = setupDB.connectDB()
#Determine time frame
if request.form['length'] == 'all':
if request.form['data_type'] == 'activity':
#Download all activity history
query = ('SELECT activity_type, duration, distance, time_created '
'FROM activities WHERE user_id=:userID')
else:
#Download all health history
query = ('SELECT height, weight, bmi, time_created '
'FROM health WHERE user_id=:userID')
#Perform Query
rows = db.query(query, userID=request.form['uid'])
else:
#Get time frame
today = d = datetime.today()
if request.form['length'] == 'day':
start_day = today - timedelta(days=1)
elif request.form['length'] == 'week':
start_day = today - timedelta(days=7)
elif request.form['length'] == 'month':
start_day = today - timedelta(days=30)
else:
raise Exception('Invalid Request')
#Determine data type
if request.form['data_type'] == 'activity':
#Download activity history
query = ('SELECT activity_type, duration, distance, time_created '
'FROM activities WHERE user_id=:userID '
'AND DATE(time_created) >= DATE(:startDay) '
'AND DATE(time_created) <= DATE(:endDay)')
else:
#Download health history
query = ('SELECT height, weight, bmi, time_created '
'FROM health WHERE user_id=:userID '
'AND DATE(time_created) >= DATE(:startDay) '
'AND DATE(time_created) <= DATE(:endDay)')
#Perform Query
rows = db.query(query,
userID=request.form['uid'],
startDay=start_day,
endDay=today)
if len(rows.as_dict()) == 0:
raise Exception('You have no history for the selected report')
#write results to file
filename = request.form['data_type'] + "_history.csv"
f = open('downloads/' + filename, "w")
f.write(rows.export('csv'))
f.close()
#Download file
if request.form['method'] == 'download':
return send_from_directory('downloads', filename, as_attachment=True)
#Send email with CSV attachment
elif request.form['method'] == 'email':
#Get user email & user_name
query = ('SELECT user_name, email ' 'FROM user_data WHERE id=:userID')
user = db.query(query, userID=request.form['uid']).first()
mail_to = user.email
mail_subject = request.form['data_type'] + " history"
mail_body = """<h3>Hello %s,</h3><br>
Here is your requested history.""" % (user.user_name)
send_email.sendEmail(mail_to, mail_subject, mail_body, filename)
#only defining 'is-error' to get red text for better visibility
flash(('Your requested data has been emailed.'), 'is-error')
return redirect(url_for('user_settings'))
def registration_complete(request):
pw_hash = bcrypt.generate_password_hash(
request.form['password']).decode('utf-8')
db = setupDB.connectDB()
query = """SELECT COUNT(*) user_count
FROM user_data
WHERE user_name = :username OR email = :email"""
user_count = db.query(query,
username=request.form['username'],
email=request.form['email']).first().user_count
if user_count > 0:
return 0
else:
#Insert main user data into 'user_data' table
query = """INSERT INTO user_data (user_name, fname, lname, email, pswd,
user_role) VALUES
(:username, :fname, :lname, :email, :pwhash,
:user_role)"""
db.query(query,
username=request.form['username'],
fname=request.form['firstName'],
lname=request.form['lastName'],
email=request.form['email'],
pwhash=pw_hash,
user_role='user')
#Enter optional data into the 'user_data' table if provided
if request.form['city'] or not request.form['state'] == 'None':
#city
if request.form['city']:
city = request.form['city']
else:
city = ""
#state
if request.form['state'] and not request.form['state'] == 'None':
state = request.form['state']
else:
state = ""
#Get users db id
query = """SELECT id
FROM user_data
WHERE user_name = :username"""
user_id = db.query(query,
username=request.form['username']).first().id
#Insert health data
query = """UPDATE user_data
SET city=:user_city, state=:user_state
WHERE id=:uid"""
db.query(query, user_city=city, user_state=state, uid=user_id)
#Enter health data into the 'health' table if provided
if request.form['heightFeet'] or request.form[
'heightIn'] or request.form['weight']:
#height feet
if request.form['heightFeet']:
hFeet = request.form['heightFeet']
else:
hFeet = ""
#height inches
if request.form['heightIn']:
hInches = request.form['heightIn']
else:
hInches = ""
#Total inches
totalHeight = (int(hFeet) * 12) + int(hInches)
#weight
if request.form['weight']:
totalWeight = request.form['weight']
else:
totalWeight = ""
#bmi
calculated_bmi = float(totalWeight) / (totalHeight *
totalHeight) * 703
#Get users db id
query = """SELECT id
FROM user_data
WHERE user_name = :username"""
user_id = db.query(query,
username=request.form['username']).first().id
#Insert health data
query = """INSERT INTO health (user_id, height, weight, bmi)
VALUES (:userid, :height, :weight, :bmi)"""
db.query(query,
userid=user_id,
height=totalHeight,
weight=totalWeight,
bmi="{0:.2f}".format(calculated_bmi))
return 1
