def viewProfile():
if request.method == "GET":
query = "INSERT INTO innodb.actions (userid, useraction, info, eventtime) VALUES (%(userid)s, %(action)s, %(desc)s, %(time)s)"
trackparams = {}
trackparams['userid'] = session.get("user_id")
trackparams['action'] = 'Open Profile'
trackparams['desc'] = str(request.args.get('id'))
trackparams['time'] = str(
datetime.datetime.now(pytz.timezone('US/Eastern')))
rundbquery(query, trackparams)
query = "SELECT fullname, shortname, program, gradyear, jointordualdegree, email, phone, linkedin, facebook, othersocial, ama FROM innodb.alumni WHERE id = %(id)s"
params = {}
params['id'] = request.args.get('id')
phone_status = phoneProvided()
linkedin_status = linkedinProvided()
facebook_status = facebookProvided()
db = rundbquery(query, params)
for row in db:
fullname = row[0]
shortname = row[1]
program = row[2]
gradyear = row[3]
jointordualdegree = row[4]
email = row[5]
if phone_status == 1:
phone = row[6]
else:
phone = 'Hidden'
if linkedin_status == 1:
linkedin = row[7]
else:
linkedin = 'Hidden'
if facebook_status == 1:
facebook = row[8]
else:
facebook = 'Hidden'
othersocial = row[9]
ama = row[10]
return render_template("viewprofile.html",
fullname=fullname,
shortname=shortname,
program=program,
gradyear=gradyear,
jointordualdegree=jointordualdegree,
email=email,
phone=phone,
linkedin=linkedin,
facebook=facebook,
othersocial=othersocial,
ama=ama)
def viewsearch(q, startat):
query = "SELECT Count(*) FROM innodb.alumni WHERE active = 1 AND (fullname LIKE %(q)s OR program LIKE %(q)s OR jointordualdegree LIKE %(q)s OR email LIKE %(q)s OR phone LIKE %(q)s OR othersocial LIKE %(q)s OR ama LIKE %(q)s) LIMIT 20"
params = {}
params['q'] = '%' + q + '%'
params['startat'] = startat
if params['startat'] == 'All' or params['startat'] is None:
params['startat'] = 0
else:
params['startat'] = max(1, int(params['startat'])) - 1
for row in rundbquery(query, params):
counttotal = row[0]
query = "SELECT id, fullname, program, gradyear, jointordualdegree, email, phone, linkedin, facebook FROM innodb.alumni WHERE active = 1 AND (fullname LIKE %(q)s OR program LIKE %(q)s OR jointordualdegree LIKE %(q)s OR email LIKE %(q)s OR phone LIKE %(q)s OR othersocial LIKE %(q)s OR ama LIKE %(q)s) LIMIT 20 OFFSET %(startat)s"
db = rundbquery(query, params)
phone_status = phoneProvided()
linkedin_status = linkedinProvided()
facebook_status = facebookProvided()
allstudents = []
for row in db:
currentstudent = {}
currentstudent['id'] = row[0]
currentstudent['fullname'] = row[1]
currentstudent['program'] = row[2]
currentstudent['gradyear'] = row[3]
currentstudent['jointordualdegree'] = row[4]
currentstudent['email'] = row[5]
if phone_status == 1:
currentstudent['phone'] = row[6]
else:
currentstudent['phone'] = 'Hidden'
if linkedin_status == 1:
currentstudent['linkedin'] = row[7]
else:
currentstudent['linkedin'] = 'Hidden'
if facebook_status == 1:
currentstudent['facebook'] = row[8]
else:
currentstudent['facebook'] = 'Hidden'
allstudents.append(currentstudent)
endat = min(counttotal, int(params['startat']) + 20)
desc = '#' + str(params['startat'] +
1) + ' to ' + str(endat) + ' of ' + str(counttotal)
desc = desc + " for query '" + q + "'"
return render_template("viewsearch.html",
students=allstudents,
desc=desc,
start=params['startat'],
end=endat,
q=q)
def register():
"""Register a new account"""
if request.method == "GET":
return render_template("register.html")
if request.method == "POST":
print('Submitted form')
query = "INSERT INTO innodb.actions (userid, useraction, info, eventtime) VALUES (%(userid)s, %(action)s, %(desc)s, %(time)s)"
trackparams = {}
trackparams['userid'] = 0
trackparams['action'] = 'Registered'
trackparams['desc'] = request.form.get('email')
trackparams['time'] = str(
datetime.datetime.now(pytz.timezone('US/Eastern')))
rundbquery(query, trackparams)
query = "Select Count(*) from innodb.alumni where email = %(email)s"
params = {}
params['email'] = request.form.get('email')
for row in rundbquery(query, params):
if int(row[0] > 0):
print('Duplicate account found')
return render_template("couldnotregister.html")
# STEP 1: ADD TO THE DATABASE
query = 'INSERT INTO innodb.alumni (fullname, shortname, program, gradyear, jointordualdegree, email, phone, phoneprovided, linkedin, linkedinprovided, facebook, facebookprovided, othersocial, ama, active, password)'
query = query + 'VALUES (%(fullname)s, %(shortname)s, %(program)s, %(gradyear)s, %(jointordualdegree)s, %(email)s, %(phone)s, %(phoneprovided)s, %(linkedin)s, %(linkedinprovided)s, %(facebook)s, %(facebookprovided)s, %(othersocial)s, %(ama)s, %(active)s, %(password)s)'
defaultpass = ''.join(
random.choice('0123456789ABCDEF') for i in range(8))
params = {}
params['fullname'] = request.form.get('fullname')
params['shortname'] = request.form.get('shortname')
params['program'] = request.form.get('program')
params['gradyear'] = request.form.get('gradyear')
params['jointordualdegree'] = request.form.get('jointordualdegree')
params['email'] = request.form.get('email')
params['phone'] = request.form.get('phone')
params['phoneprovided'] = 1 - (request.form.get('phone') == "")
params['linkedin'] = request.form.get('linkedin')
params['linkedinprovided'] = 1 - (request.form.get('linkedin') == "")
params['facebook'] = request.form.get('facebook')
params['facebookprovided'] = 1 - (request.form.get('facebook') == "")
params['othersocial'] = request.form.get('othersocial')
params['ama'] = request.form.get('ama')
params['active'] = 1
params['password'] = generate_password_hash(defaultpass)
dbreturn = rundbquery(query, params)
sendWelcomeEmail(params['shortname'], params['email'],
request.form.get('harvardemail'), defaultpass, params)
return redirect(url_for('registered'))
def forgotpw():
"""Change Password"""
# Forget any user_id
session.clear()
# User reached route via POST (as by submitting a form via POST)
if request.method == "POST":
# Verify correct original credentials
query = "Select count(*) FROM innodb.alumni WHERE email = %(email)s"
params = {}
params['email'] = request.form.get("email").lower()
dbreturn = rundbquery(query, params)
for row in dbreturn:
if int(row[0]) == 0:
return render_template("accountnotfound.html")
else:
# Update user's password
query = "INSERT INTO innodb.actions (userid, useraction, info, eventtime) VALUES (%(userid)s, %(action)s, %(desc)s, %(time)s)"
trackparams = {}
trackparams['userid'] = session.get("user_id")
trackparams['action'] = 'Reset Password'
trackparams['desc'] = request.form.get("email").lower()
trackparams['time'] = str(
datetime.datetime.now(pytz.timezone('US/Eastern')))
rundbquery(query, trackparams)
defaultpass = ''.join(
random.choice('0123456789ABCDEF') for i in range(8))
query = "UPDATE innodb.alumni SET password = %(password)s WHERE email = %(email)s"
params = {}
params['password'] = generate_password_hash(defaultpass)
params['email'] = request.form.get("email").lower()
dbreturn2 = rundbquery(query, params)
query = "SELECT shortname FROM innodb.alumni WHERE email = %(email)s"
params = {}
params['email'] = request.form.get("email").lower()
dbreturn3 = rundbquery(query, params)
for row in dbreturn3:
shortname = row[0]
sendPasswordReset(shortname, params['email'], defaultpass)
return render_template("resetpw.html")
else: # User reached route via GET (as by clicking a link or via redirect)
return render_template("forgotpw.html")
def changepw():
"""Change Password"""
# Forget any user_id
session.clear()
# User reached route via POST (as by submitting a form via POST)
if request.method == "POST":
# Verify correct original credentials
query = "Select id, email, password FROM innodb.alumni WHERE email = %(email)s"
params = {}
params['email'] = request.form.get("email").lower()
dbreturn = rundbquery(query, params)
for row in dbreturn:
if not check_password_hash(row[2], request.form.get("password")):
return render_template("badlogin.html")
else:
# Update user's password
query = "UPDATE innodb.alumni SET password = %(password)s WHERE email = %(email)s"
params = {}
params['password'] = generate_password_hash(
request.form.get("newpassword"))
params['email'] = request.form.get("email").lower()
dbreturn2 = rundbquery(query, params)
# Now log the user in
query = "Select id, shortname FROM innodb.alumni WHERE email = %(email)s"
params['email'] = request.form.get("email").lower()
dbreturn3 = rundbquery(query, params)
for row in dbreturn3:
session["user_id"] = row[0]
shortname = row[1]
query = "INSERT INTO innodb.actions (userid, useraction, info, eventtime) VALUES (%(userid)s, %(action)s, %(desc)s, %(time)s)"
trackparams = {}
trackparams['userid'] = session.get("user_id")
trackparams['action'] = 'Change Password'
trackparams['desc'] = params['email']
trackparams['time'] = str(
datetime.datetime.now(pytz.timezone('US/Eastern')))
rundbquery(query, trackparams)
# Redirect user to home page
confirmPasswordChange(shortname, params['email'])
return redirect("/")
else: # User reached route via GET (as by clicking a link or via redirect)
return render_template("changepw.html")
def assignDefaultPWtoEveryone():
for currentuser in range(267, getDBsize() + 1):
pw = ''.join(random.choice('0123456789ABCDEF') for i in range(16))
hash = generate_password_hash(pw)
print('User ' + str(currentuser) + ' gets default pw ' + str(pw))
query = "Update innodb.alumni set defaultpass = %(pw)s, password = %(hash)s where id = %(id)s"
params = {}
params['pw'] = pw
params['hash'] = hash
params['id'] = currentuser
rundbquery(query, params)
def login():
"""Log user in"""
# Forget any user_id
session.clear()
# User reached route via POST (as by submitting a form via POST)
if request.method == "POST":
# See if the user exists at all
query = "Select Count(*) from innodb.alumni where email = %(email)s"
params = {}
params['email'] = request.form.get("email").lower()
dbreturn = rundbquery(query, params)
for row in dbreturn:
if (row[0] == 0):
session.clear()
print("User does not exist")
return render_template("badlogin.html")
# Nowe can assume that the user exists
query = "Select id, email, password FROM innodb.alumni WHERE email = %(email)s"
params = {}
params['email'] = request.form.get("email").lower()
dbreturn = rundbquery(query, params)
for row in dbreturn:
print(row)
if check_password_hash(row[2], request.form.get("password")):
session["user_id"] = row[0]
query = "INSERT INTO innodb.actions (userid, useraction, info, eventtime) VALUES (%(userid)s, %(action)s, %(desc)s, %(time)s)"
trackparams = {}
trackparams['userid'] = session.get("user_id")
trackparams['action'] = 'Log in'
trackparams['desc'] = ''
trackparams['time'] = str(
datetime.datetime.now(pytz.timezone('US/Eastern')))
rundbquery(query, trackparams)
return render_template("goodlogin.html")
else:
session.clear()
return render_template("badlogin.html")
# User reached route via GET (as by clicking a link or via redirect)
else:
return render_template("login.html")
def scrollsearch():
if request.method == "GET":
q = request.args.get('q')
startat = request.args.get('startat')
query = "INSERT INTO innodb.actions (userid, useraction, info, eventtime) VALUES (%(userid)s, %(action)s, %(desc)s, %(time)s)"
trackparams = {}
trackparams['userid'] = session.get("user_id")
trackparams['action'] = 'Scroll Search'
trackparams['desc'] = str(q) + ' @ ' + str(startat)
trackparams['time'] = str(
datetime.datetime.now(pytz.timezone('US/Eastern')))
rundbquery(query, trackparams)
return viewsearch(q, startat)
def search():
if request.method == "GET":
return render_template("search.html")
if request.method == "POST":
q = request.form.get('q')
query = "INSERT INTO innodb.actions (userid, useraction, info, eventtime) VALUES (%(userid)s, %(action)s, %(desc)s, %(time)s)"
trackparams = {}
trackparams['userid'] = session.get("user_id")
trackparams['action'] = 'Search'
trackparams['desc'] = str(q)
trackparams['time'] = str(
datetime.datetime.now(pytz.timezone('US/Eastern')))
rundbquery(query, trackparams)
print('query: ' + q)
return viewsearch(q, 1)
def getEmail():
"""Look up data for id"""
if not request.args.get("id"):
raise RuntimeError("id not found")
query = "SELECT email FROM innodb.alumni WHERE id=%(id)s"
params = {}
params['id'] = request.args.get("id")
db = rundbquery(query, params)
for row in db:
print(row[0])
return jsonify(row[0])
def getFacebook():
print('Started with id: ' + request.args.get("id"))
"""Look up data for id"""
if not request.args.get("id"):
raise RuntimeError("id not found")
if facebookProvided() == 0:
print('Did not provide')
return jsonify(0)
query = "SELECT facebook FROM innodb.alumni WHERE id=%(id)s AND active=1"
params = {}
params['id'] = request.args.get("id")
db = rundbquery(query, params)
for row in db:
return jsonify(row[0])
# and message to send - here it is sent as one string.
s.sendmail(me, you, msg.as_string())
s.quit()
def assignDefaultPWtoEveryone():
for currentuser in range(267, getDBsize() + 1):
pw = ''.join(random.choice('0123456789ABCDEF') for i in range(16))
hash = generate_password_hash(pw)
print('User ' + str(currentuser) + ' gets default pw ' + str(pw))
query = "Update innodb.alumni set defaultpass = %(pw)s, password = %(hash)s where id = %(id)s"
params = {}
params['pw'] = pw
params['hash'] = hash
params['id'] = currentuser
rundbquery(query, params)
for currentuser in range(267, getDBsize() + 1):
query = "select id, shortname, email, defaultpass from innodb.alumni where id = %(id)s"
params = {}
params['id'] = currentuser
db = rundbquery(query, params)
for row in db:
sendWelcomeEmail(row[1], row[2], row[3])
print('Sent email to ID ' + str(row[0]) + ': ' + row[1] + ' at ' +
row[2])
def getDBsize():
query = "Select Count(*) from innodb.alumni where active=1"
params = ""
for row in rundbquery(query, params):
return row[0]
def viewlist():
query = "INSERT INTO innodb.actions (userid, useraction, info, eventtime) VALUES (%(userid)s, %(action)s, %(desc)s, %(time)s)"
trackparams = {}
trackparams['userid'] = session.get("user_id")
trackparams['action'] = 'Viewed Db'
trackparams['desc'] = ''
trackparams['time'] = str(
datetime.datetime.now(pytz.timezone('US/Eastern')))
rundbquery(query, trackparams)
query = "SELECT id, fullname, program, gradyear, jointordualdegree, email, phone, linkedin, facebook FROM innodb.alumni WHERE active = 1 ORDER BY id LIMIT 20 OFFSET %(startat)s"
params = {}
params['startat'] = request.args.get("startat")
if params['startat'] == 'All' or params['startat'] is None:
params['startat'] = 0
else:
params['startat'] = max(1, int(params['startat'])) - 1
db = rundbquery(query, params)
phone_status = phoneProvided()
linkedin_status = linkedinProvided()
facebook_status = facebookProvided()
allstudents = []
for row in db:
currentstudent = {}
currentstudent['id'] = row[0]
currentstudent['fullname'] = row[1]
currentstudent['program'] = row[2]
currentstudent['gradyear'] = row[3]
currentstudent['jointordualdegree'] = row[4]
currentstudent['email'] = row[5]
if phone_status == 1:
currentstudent['phone'] = row[6]
else:
currentstudent['phone'] = 'Hidden'
if linkedin_status == 1:
currentstudent['linkedin'] = row[7]
else:
currentstudent['linkedin'] = 'Hidden'
if facebook_status == 1:
currentstudent['facebook'] = row[8]
else:
currentstudent['facebook'] = 'Hidden'
allstudents.append(currentstudent)
endat = min(getDBsize(), int(params['startat']) + 20)
desc = '#' + str(params['startat'] +
1) + ' to ' + str(endat) + ' of ' + str(getDBsize())
return render_template("viewlist.html",
students=allstudents,
desc=desc,
start=params['startat'],
end=endat)
def editProfile():
if request.method == "GET":
query = "INSERT INTO innodb.actions (userid, useraction, info, eventtime) VALUES (%(userid)s, %(action)s, %(desc)s, %(time)s)"
trackparams = {}
trackparams['userid'] = session.get("user_id")
trackparams['action'] = 'Edit Profile'
trackparams['desc'] = ''
trackparams['time'] = str(
datetime.datetime.now(pytz.timezone('US/Eastern')))
rundbquery(query, trackparams)
query = "SELECT fullname, shortname, program, gradyear, jointordualdegree, email, phone, linkedin, facebook, othersocial, ama FROM innodb.alumni WHERE id = %(id)s"
params = {}
params['id'] = session.get("user_id")
db = rundbquery(query, params)
for row in db:
fullname = row[0]
shortname = row[1]
program = row[2]
gradyear = row[3]
jointordualdegree = row[4]
email = row[5]
phone = row[6]
linkedin = row[7]
facebook = row[8]
othersocial = row[9]
ama = row[10]
return render_template("editprofile.html",
fullname=fullname,
shortname=shortname,
program=program,
gradyear=gradyear,
jointordualdegree=jointordualdegree,
email=email,
phone=phone,
linkedin=linkedin,
facebook=facebook,
othersocial=othersocial,
ama=ama)
if request.method == "POST":
print('Edited Profile')
# STEP 1: ADD TO THE DATABASE
query = 'UPDATE innodb.alumni SET '
query = query + 'fullname = %(fullname)s, shortname = %(shortname)s, program = %(program)s, gradyear = %(gradyear)s, jointordualdegree = %(jointordualdegree)s, '
query = query + 'email = %(email)s, phone = %(phone)s, phoneprovided = %(phoneprovided)s, linkedin = %(linkedin)s, linkedinprovided = %(linkedinprovided)s, facebook = %(facebook)s, facebookprovided = %(facebookprovided)s, othersocial = %(othersocial)s, ama = %(ama)s '
query = query + 'WHERE id = %(id)s'
params = {}
params['fullname'] = request.form.get('fullname')
params['shortname'] = request.form.get('shortname')
params['program'] = request.form.get('program')
params['gradyear'] = request.form.get('gradyear')
params['jointordualdegree'] = request.form.get('jointordualdegree')
params['email'] = request.form.get('email')
params['phone'] = request.form.get('phone')
params['phoneprovided'] = 1 - (request.form.get('phone') == "")
params['linkedin'] = request.form.get('linkedin')
params['linkedinprovided'] = 1 - (request.form.get('linkedin') == "")
params['facebook'] = request.form.get('facebook')
params['facebookprovided'] = 1 - (request.form.get('facebook') == "")
params['othersocial'] = request.form.get('othersocial')
params['ama'] = request.form.get('ama')
params['id'] = session.get("user_id")
dbreturn = rundbquery(query, params)
sendUpdatedProfile(params['shortname'], params['email'], params)
return redirect(url_for('editedprofile'))
