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