def editPost(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
cursor = conn.cursor()
query = 'SELECT * FROM content WHERE id = %s'
cursor.execute(query, (post_id))
data = cursor.fetchall()
cursor.close()
#checks if there is a post with the given post_id, spit out error otherwise
cursor = conn.cursor()
editCountQuery = 'SELECT COUNT(*) FROM content WHERE id = %s'
cursor.execute(editCountQuery, (post_id))
countData = cursor.fetchone()
cursor.close()
if (countData['COUNT(*)'] > 0):
return render_template("content_edit.html",
post_id=post_id,
data=data,
countData=countData)
else:
editError = "Post does not exist. Please edit a valid post."
return render_template("content_edit.html",
post_id=post_id,
data=data,
editError=editError)
def replyPost(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
query = 'SELECT * \
FROM content \
WHERE id =%s'
cursor = conn.cursor()
cursor.execute(query, (post_id))
data = cursor.fetchall()
cursor.close()
#check if post_id even exists, otherwise return error
cursor = conn.cursor()
query = 'SELECT COUNT(*) FROM content WHERE id = %s'
cursor.execute(query, (post_id))
countData = cursor.fetchone()
cursor.close()
if (countData['COUNT(*)'] > 0):
return render_template("reply_post.html",
post_id=post_id,
countData=countData,
data=data)
else:
error = "Post does not exist. Please comment on a valid post."
return render_template("reply_post.html",
post_id=post_id,
error=error,
data=data)
def registerProcessing():
# server-side validation
username = request.form['username']
if username in session['users'].keys():
errormsg = "Username already taken."
return render_template('register.html', error=errormsg)
if len(username) < 4:
errormsg = "Username is too short. Must be more than 3 characters."
return render_template('register.html', error=errormsg)
elif len(username) > 50:
errormsg = "Username and/or other fields are too long. 50 characters max."
return render_template('register.html', error=errormsg)
password = request.form['password']
if len(password) < 4:
errormsg = "Password is too short (needs to be greater than 3 characters)."
return render_template('register.html', error=errormsg)
elif len(password) > 50:
errormsg = "Password is too long. 50 characters max."
return render_template('register.html', error=errormsg)
retype = request.form['retype']
if retype != password:
errormsg = "Passwords do not match."
return render_template('register.html', error=errormsg)
# Add salt and hash password prior to inserting to DB.
salt = generate_random_salt(
) # base64 encoded random bytes, default len=64
password_hash = generate_password_hash(password, salt)
firstname = request.form['firstname']
lastname = request.form['lastname']
cursor = conn.cursor()
query = 'INSERT INTO person (username, password, salt, first_name, last_name) VALUES (%s, %s, %s, %s, %s)'
cursor.execute(query, (username, password_hash, salt, firstname, lastname))
conn.commit()
cursor.close()
query = "INSERT INTO profile (username, bio, file_path) VALUES (%s, '', '')"
cursor = conn.cursor()
cursor.execute(query, (username))
conn.commit()
cursor.close()
session['logged_in'] = True
session['username'] = username
session['users'][username] = {}
session['users'][username]['groups'] = []
session['users'][username]['first_name'] = firstname
session['users'][username]['last_name'] = lastname
return redirect(url_for('main', username=session['username']))
def getData(query):
cursor = conn.cursor()
cursor.execute(query)
data = cursor.fetchall()
conn.commit()
cursor.close()
return (data)
def loginAuth():
#grabs information from the forms
username = request.form['username']
password = request.form['password']
#usrtype = request.form['usrtype']
#cursor used to send queries
cursor = conn.cursor()
#executes query
query = 'SELECT * FROM tbl_user WHERE `user_username` = %s and `user_password` = md5(%s)'
cursor.execute(query, (username, password))
#stores the results in a variable
data = cursor.fetchone()
#use fetchall() if you are expecting more than 1 data row
cursor.close()
error = None
if (data):
#creates a session for the the user
#session is a built in
session['username'] = username
return redirect(url_for('index'))
# if usrtype == 'staff':
# return redirect(url_for('staffHome'))
# elif usrtype == 'customer':
# return redirect(url_for('customerHome'))
# else:
# return redirect(url_for('agentHome'))
else:
#returns an error message to the html page
error = 'Invalid login or username'
return render_template('login.html', error=error)
def purchaseCustomer():
username = session['username']
cursor = conn.cursor()
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
# Find the number of tickets to generate the next ticket_id
queryCount = 'SELECT COUNT(*) as count FROM ticket \
WHERE ticket.airline_name = %s AND ticket.flight_num = %s'
cursor.execute(queryCount, (airline_name, flight_num))
ticketCount = cursor.fetchone()
ticketCountVal = 0
if ticketCount != None:
ticketCountVal = ticketCount['count']
# ticket_id = _genTix(ticketCountVal, airline_name.strip().replace(' ', ''), flight_num)
ticket_id = _genTix()
# print("WHAT F*****G NUMBER: ", ticket_id)
# Create the new ticket
queryNewTicket = 'INSERT INTO ticket VALUES(%s, %s, %s)'
cursor.execute(queryNewTicket, (ticket_id, airline_name, flight_num))
# Finalize the purchase
queryPurchase = 'INSERT INTO purchases VALUES(%s, %s, %s, CURDATE())'
cursor.execute(queryPurchase, (ticket_id, username, None))
data = cursor.fetchone()
conn.commit()
cursor.close()
return render_template('purchaseCustomer.html')
def getData(query, item):
cursor = conn.cursor()
cursor.execute(query, (item))
data = cursor.fetchone()
conn.commit()
cursor.close()
return data
def getFriend():
userList = []
# query for getting the members of the group of
# which the username is creator of
creatorQuery = "SELECT username, group_name \
FROM member \
WHERE username_creator = %s;"
cursor = conn.cursor()
cursor.execute(creatorQuery, (session['username']))
userList.extend(cursor.fetchall())
cursor.close()
# query for getting the members of the group of
# which the username is a member of
cursor = conn.cursor()
memberQuery = "SELECT username, group_name \
FROM member \
WHERE group_name in \
(SELECT group_name \
FROM member \
WHERE username = %s) \
HAVING username != %s;"
cursor.execute(memberQuery, (session['username'], session['username']))
userList.extend(cursor.fetchall())
cursor.close()
# query for getting the creators of the group of
# which the user is a member of
friendCreatorQuery = "SELECT username_creator, group_name \
FROM member \
WHERE group_name in \
(SELECT group_name \
FROM member \
WHERE username = %s) \
GROUP BY group_name;"
cursor = conn.cursor()
cursor.execute(friendCreatorQuery, (session['username']))
userList.extend(cursor.fetchall())
cursor.close()
return userList
def getInfo(username):
query = "SELECT * FROM profile WHERE username=%s"
cursor = conn.cursor()
cursor.execute(query, (username))
data = cursor.fetchone()
conn.commit()
cursor.close()
return(data)
def registerProcessing():
username = request.form['username']
if username in session['users'].keys():
errormsg = "Username already taken."
return render_template('register.html', error = errormsg)
if len(username) < 4:
errormsg = "Username is too short. Must be more than 3 characters."
return render_template('register.html', error = errormsg)
elif len(username) > 50:
errormsg = "Username and/or other fields are too long. 50 characters max."
return render_template('register.html', error = errormsg)
password = request.form['password']
if len(password) < 4:
errormsg = "Password is too short (needs to be greater than 3 characters)."
return render_template('register.html', error = errormsg)
elif len(password) > 50:
errormsg = "Password is too long. 50 characters max."
return render_template('register.html', error = errormsg)
retype = request.form['retype']
if retype != password:
errormsg = "Passwords do not match."
return render_template('register.html', error = errormsg)
firstname = request.form['firstname']
lastname = request.form['lastname']
cursor = conn.cursor()
query = 'INSERT INTO person (username, password, first_name, last_name) VALUES (%s, md5(%s), %s, %s)'
cursor.execute(query, (username, password, firstname, lastname))
conn.commit()
cursor.close()
query = "INSERT INTO profile (username, bio, file_path) VALUES (%s, '', '')"
cursor = conn.cursor()
cursor.execute(query, (username))
conn.commit()
cursor.close()
session['logged_in'] = True
session['username'] = username
session['users'][username] = {}
session['users'][username]['groups'] = []
session['users'][username]['first_name'] = firstname
session['users'][username]['last_name'] = lastname
return redirect(url_for('main', username = session['username']))
def sharePosts():
if (not session.get('logged_in')):
return redirect(url_for('main'))
query = "SELECT * FROM content WHERE username=%s"
cursor = conn.cursor()
cursor.execute(query, (session['username']))
data = cursor.fetchall()
cursor.close()
return render_template('sharePosts.html', data=data)
def addGroups(groupList):
friendGroup = "SELECT group_name, description \
FROM friendgroup \
WHERE username = %s"
cursor = conn.cursor()
cursor.execute(friendGroup, (session['username']))
groupList.extend(cursor.fetchall())
cursor.close()
def _genTix():
cursor = conn.cursor()
cand = random.randint(1, 2147483647)
query = 'SELECT ticket_id FROM ticket'
cursor.execute(query)
allTix = cursor.fetchall()
cursor.close()
while cand in allTix:
cand = random.randint(1, 2147483647)
return cand
def addFriends():
if (not session.get('logged_in')):
return redirect(url_for('main'))
cursor = conn.cursor()
groupQuery = 'SELECT * FROM `friendgroup` WHERE username = %s'
cursor.execute(groupQuery, session['username'])
group = cursor.fetchall()
cursor.close()
return render_template('addFriends.html', data=group)
def likePost(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
cursor = conn.cursor()
likePostQuery = 'INSERT INTO likes (id, username_liker) VALUES (' + post_id + ', "' + session[
'username'] + '")'
cursor.execute(likePostQuery)
conn.commit()
cursor.close()
return redirect(url_for('main'))
def dislikePost(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
cursor = conn.cursor()
dislikePostQuery = 'DELETE FROM likes WHERE username_liker="' + session[
'username'] + '" AND id=' + post_id
cursor.execute(dislikePostQuery)
conn.commit()
cursor.close()
return redirect(url_for('main'))
def creatingFriends():
if (not session.get('logged_in')):
return redirect(url_for('main'))
# get all the info from the form
groupName = request.form['name']
description = request.form["description"]
data = request.form
#check if group name too long
if len(groupName) > 50:
error = "Group name too long. 50 characters max."
return render_template('createFriend.html', error=error)
# check if group name exists
query = "SELECT COUNT(group_name) FROM friendgroup WHERE group_name = %s"
allGroups = getData(query, groupName)
if (allGroups[0]['COUNT(group_name)'] == 1):
error = "The group name already exists. Please enter another one."
return render_template('createFriend.html', error=error)
else:
cursor = conn.cursor()
command = "INSERT INTO friendgroup (group_name, username, description) VALUES (%s, %s, %s)"
cursor.execute(command, (groupName, session['username'], description))
conn.commit()
cursor.close()
# create a query for each member
cursor = conn.cursor()
stuff = []
exclude = ["name", "description"]
for member in data:
if (member not in exclude):
query = "INSERT INTO member (username, group_name, username_creator) VALUES (%s, %s, %s)"
cursor.execute(query, (member, groupName, session['username']))
conn.commit()
cursor.close()
return redirect(url_for('friends'))
def deletePost(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
userQuery = 'SELECT username FROM content WHERE id = %s'
user = getData(userQuery, post_id)
if (user['username'] != session['username']):
#return render_template('result.html', data=user['username'])
error = "This is not your post to delete!"
return redirect(url_for('main'))
else:
# check if post is in table
shareQuery = 'SELECT * FROM share WHERE id = %s'
data = getData(shareQuery, post_id)
if (data is not None):
delete = 'DELETE FROM share WHERE id = %s'
cursor = conn.cursor()
cursor.execute(delete, (post_id))
conn.commit() #commit the change to DB
cursor.close()
cursor = conn.cursor()
#two delete queries; must delete tag because foreign key constraint
delete = 'DELETE FROM tag WHERE tag.id=%s'
cursor.execute(delete, (post_id))
conn.commit() #commit the change to DB
delete = 'DELETE FROM likes WHERE likes.id=%s'
cursor.execute(delete, (post_id))
conn.commit() #commit the change to DB
delete = 'DELETE FROM comment WHERE comment.id=%s'
cursor.execute(delete, (post_id))
conn.commit() #commit the change to DB
delete = 'DELETE FROM content WHERE content.id=%s'
cursor.execute(delete, (post_id))
conn.commit() #commit the change to DB
cursor.close()
return redirect(url_for('main'))
def tags():
if (not session.get('logged_in')):
return redirect(url_for('main'))
# making an sql query to obtain tag requests that the user has yet
# to approve (when status = 0)
cursor = conn.cursor()
query = 'SELECT * FROM tag WHERE username_taggee = %s and status = 0'
cursor.execute(query, (session['username']))
dataTwo = cursor.fetchall()
cursor.close()
request_id = {}
for item in dataTwo:
post_id = int(item['id'])
cursor = conn.cursor()
query = 'SELECT file_path FROM content WHERE id = %s'
cursor.execute(query, (post_id))
data = cursor.fetchall()
cursor.close()
request_id[post_id] = ""
request_id[post_id] = data[0]['file_path']
return render_template("tags.html", data=dataTwo, request_id=request_id)
def customerHome():
username = session['username']
cursor = conn.cursor()
query = 'SELECT purchases.ticket_id, ticket.airline_name, ticket.flight_num, departure_airport, departure_time, arrival_airport, arrival_time \
FROM purchases, ticket, flight \
WHERE purchases.ticket_id = ticket.ticket_id \
AND ticket.airline_name = flight.airline_name \
AND ticket.flight_num = flight.flight_num \
AND customer_email = %s AND departure_time > curdate()'
cursor.execute(query, (username))
data = cursor.fetchall()
cursor.close()
return render_template('customer.html', username=username, posts=data)
def replyingPost(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
#check if post_id even exists, otherwise return error
cursor = conn.cursor()
query = 'SELECT COUNT(*) FROM content WHERE id = %s'
cursor.execute(query, (post_id))
countData = cursor.fetchone()
cursor.close()
if (countData['COUNT(*)'] <= 0):
error = "Post does not exist. Please comment on a valid post."
return render_template("reply_post.html", post_id=post_id, error=error)
content = request.form['description']
time = datetime.datetime.now().strftime('%y-%m-%d %H:%M:%S')
query = 'INSERT INTO comment (id, username, timest, comment_text) VALUES (%s, %s, %s, %s)'
cursor = conn.cursor()
cursor.execute(query, (post_id, session['username'], time, content))
data = conn.commit()
cursor.close()
return redirect(url_for('main'))
def editProfileProcessed(username):
if (not session.get('logged_in')):
return redirect(url_for('main'))
#Cannot edit another user's profile. Checks for that error.
if (session['username'] != username):
error = 'Cannot edit another user profile.'
return render_template('editProfile.html',
username=username,
error=error)
biography = request.form['bio']
img_filepath = '/static/posts_pic/'
data = getInfo(username)
if (allowed_file(request.files['photo'].filename) == False):
error = 'Please attach image files only.'
return render_template('editProfile.html',
username=username,
error=error,
data=data)
if len(biography) > 50:
error = 'Bio is too long. 50 characters max.'
return render_template('editProfile.html',
username=username,
error=error,
data=data)
if request.method == 'POST' and 'photo' in request.files:
filename = photos.save(request.files['photo'])
img_filepath = img_filepath + filename
# conducts queries to update post
cursor = conn.cursor()
updateQuery = 'UPDATE profile \
SET \
bio = %s, \
file_path = %s \
WHERE profile.username = %s'
cursor.execute(updateQuery, (biography, img_filepath, username))
conn.commit()
cursor.close()
return redirect(url_for('profile', username=username))
def agentHome():
username = session['username']
cursor = conn.cursor();
query = 'SELECT * \
FROM purchases, ticket, flight, booking_agent \
WHERE purchases.ticket_id = ticket.ticket_id \
AND ticket.airline_name = flight.airline_name \
AND ticket.flight_num = flight.flight_num \
AND booking_agent.email = %s AND booking_agent.booking_agent_id = purchases.booking_agent_id \
AND departure_time > curdate() \
ORDER BY customer_email'
cursor.execute(query, (username))
data = cursor.fetchall()
# Get booking_agent_id
queryGetID = 'SELECT booking_agent_id FROM booking_agent WHERE email=%s'
cursor.execute(queryGetID, username)
agentID = cursor.fetchone()
# Get total commsion in the past 30 days
queryGetCommission = 'SELECT sum(price)*.10 as totalComm FROM purchases, ticket, flight \
WHERE purchases.ticket_id = ticket.ticket_id \
AND ticket.airline_name = flight.airline_name AND ticket.flight_num = flight.flight_num \
AND purchases.purchase_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE() \
AND purchases.booking_agent_id = %s'
cursor.execute(queryGetCommission, agentID['booking_agent_id'])
totalComm = cursor.fetchone()
totalCommVal = 0
if totalComm['totalComm'] != None:
totalCommVal = totalComm['totalComm']
# print totalComm
# Get total tickets in the past 30 days
queryGetTicketCount = 'SELECT count(*) as ticketCount FROM purchases, ticket, flight \
WHERE purchases.ticket_id = ticket.ticket_id \
AND ticket.airline_name = flight.airline_name AND ticket.flight_num = flight.flight_num \
AND purchases.purchase_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE() \
AND purchases.booking_agent_id = %s'
cursor.execute(queryGetTicketCount, agentID['booking_agent_id'])
ticketCount = cursor.fetchone()
ticketCountVal = ticketCount['ticketCount']
avgComm = 0
# print ticketCount, totalCommVal
if ticketCountVal != 0:
avgComm = totalCommVal/ticketCountVal
cursor.close()
return render_template('agent.html', username=username, posts=data, totalComm=totalCommVal, avgComm=avgComm, ticketCount=ticketCountVal)
def registerAuthAgent():
email = request.form['email']
password = request.form['password']
booking_agent_id = request.form['booking_agent_id']
cursor = conn.cursor()
query = 'SELECT * FROM booking_agent WHERE email = %s'
cursor.execute(query, (email))
data = cursor.fetchone()
error = None
if (data):
error = "This user already exists"
return render_template('registerAgent.html', error=error)
else:
ins = 'INSERT INTO booking_agent VALUES(%s, md5(%s), %s)'
cursor.execute(ins, (email, password, booking_agent_id))
conn.commit()
cursor.close()
return render_template('index.html')
def editPostProcessed(post_id):
if (not session.get('logged_in')):
return redirect(url_for('main'))
postContent = request.form['content']
pubOrPriv = request.form['publicity']
img_filepath = '/static/posts_pic/'
if not allowed_file(request.files['photo'].filename):
error = 'Please attach image files only.'
return render_template('content_edit.html',
post_id=post_id,
error=error)
if len(postContent) > 50:
error = 'Description is too long. 50 characters max.'
return render_template('content_edit.html',
post_id=post_id,
error=error)
if request.method == 'POST' and 'photo' in request.files:
filename = photos.save(request.files['photo'])
img_filepath = img_filepath + filename
# conducts queries to update post
cursor = conn.cursor()
updateQuery = 'UPDATE content \
SET \
file_path = %s, \
content_name = %s, \
public = %s, \
timest = CURRENT_TIMESTAMP \
WHERE content.id = %s'
cursor.execute(updateQuery,
(img_filepath, postContent, pubOrPriv, post_id))
conn.commit()
cursor.close()
return redirect(url_for('main'))
def searchPurchaseAgent():
cursor = conn.cursor()
fromcity = request.form['fromcity']
fromairport = request.form['fromairport']
fromdate = request.form['fromdate']
tocity = request.form['tocity']
toairport = request.form['toairport']
todate = request.form['todate']
query = 'SELECT distinct f.airline_name, f.flight_num, departure_airport, departure_time, arrival_airport, arrival_time, price, airplane_id \
FROM flight as f, airport \
WHERE airport.airport_name=f.departure_airport \
AND airport.airport_city = %s \
AND airport.airport_name = %s \
AND %s BETWEEN DATE_SUB(f.departure_time, INTERVAL 2 DAY) AND DATE_ADD(f.departure_time, INTERVAL 2 DAY)\
AND %s BETWEEN DATE_SUB(f.arrival_time, INTERVAL 2 DAY) AND DATE_ADD(f.arrival_time, INTERVAL 2 DAY)\
AND (f.airline_name, f.flight_num) in \
(SELECT flight.airline_name, flight.flight_num FROM flight, airport \
WHERE airport.airport_name=flight.arrival_airport \
AND airport.airport_city = %s \
AND airport.airport_name = %s) \
AND (SELECT DISTINCT seats \
FROM flight, airplane \
WHERE flight.airplane_id = airplane.airplane_id AND flight.airline_name = airplane.airline_name \
AND flight.airline_name = f.airline_name AND flight.flight_num = f.flight_num) \
>= (SELECT COUNT(*) \
FROM ticket \
WHERE ticket.airline_name = f.airline_name AND ticket.flight_num = f.flight_num)'
cursor.execute(query, (fromcity, fromairport, fromdate, todate, tocity, toairport))
# print cursor._executed
data = cursor.fetchall()
cursor.close()
error = None
if(data):
print(data)
return render_template('purchaseAgent.html', results=data)
else:
#returns an error message to the html page
error = 'No results found'
return render_template('purchaseAgent.html', searchError=error)
def searchAgent():
username = session['username']
cursor = conn.cursor()
fromcity = request.form['fromcity']
fromairport = request.form['fromairport']
fromdate = request.form['fromdate']
tocity = request.form['tocity']
toairport = request.form['toairport']
todate = request.form['todate']
# Get booking_agent_id
queryGetID = 'SELECT booking_agent_id FROM booking_agent WHERE email=%s'
cursor.execute(queryGetID, username)
agentID = cursor.fetchone()['booking_agent_id']
# Main query
query = 'SELECT * FROM flight, airport, purchases, ticket \
WHERE airport.airport_name=flight.departure_airport \
AND flight.flight_num = ticket.flight_num AND flight.airline_name = ticket.airline_name\
AND ticket.ticket_id = purchases.ticket_id\
AND purchases.booking_agent_id = %s\
AND airport.airport_city = %s \
AND airport.airport_name = %s \
-- AND flight.status = "Upcoming"\
AND %s BETWEEN DATE_SUB(flight.departure_time, INTERVAL 2 DAY) AND DATE_ADD(flight.departure_time, INTERVAL 2 DAY) \
AND %s BETWEEN DATE_SUB(flight.arrival_time, INTERVAL 2 DAY) AND DATE_ADD(flight.arrival_time, INTERVAL 2 DAY) \
AND (flight.airline_name, flight.flight_num) in \
(SELECT flight.airline_name, flight.flight_num FROM flight, airport \
WHERE airport.airport_name=flight.arrival_airport \
AND airport.airport_city = %s \
AND airport.airport_name = %s)'
cursor.execute(query, (agentID, fromcity, fromairport, fromdate, todate, tocity, toairport))
data = cursor.fetchall()
cursor.close()
error = None
if(data):
return render_template('searchAgent.html', results=data)
else:
#returns an error message to the html page
error = 'No results found'
return render_template('searchAgent.html', error=error)
def registerAuthStaff():
username = request.form['username']
password = request.form['password']
first_name = request.form['first_name']
last_name = request.form['last_name']
date_of_birth = request.form['date_of_birth']
airline_name = request.form['airline_name']
cursor = conn.cursor()
query = 'SELECT * FROM airline_staff WHERE username = %s'
cursor.execute(query, (username))
data = cursor.fetchone()
error = None
if (data):
error = "This user already exists"
return render_template('registerStaff.html', error=error)
else:
ins = 'INSERT INTO airline_staff VALUES(%s, md5(%s), %s, %s, %s, %s)'
cursor.execute(ins, (username, password, first_name, last_name,
date_of_birth, airline_name))
conn.commit()
cursor.close()
return render_template('index.html')
def registerAuthCustomer():
#grabs information from the forms
email = request.form['email']
name = request.form['name']
password = request.form['password']
building_number = request.form['building_number']
street = request.form['street']
city = request.form['city']
state = request.form['state']
phone_number = request.form['phone_number']
passport_number = request.form['passport_number']
passport_expiration = request.form['passport_expiration']
passport_country = request.form['passport_country']
date_of_birth = request.form['date_of_birth']
#cursor used to send queries
cursor = conn.cursor()
#executes query
query = 'SELECT * FROM customer WHERE email = %s'
cursor.execute(query, (email))
#stores the results in a variable
data = cursor.fetchone()
#use fetchall() if you are expecting more than 1 data row
error = None
if (data):
#If the previous query returns data, then user exists
error = "This user already exists"
return render_template('registerCustomer.html', error=error)
else:
ins = 'INSERT INTO customer VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
cursor.execute(ins,
(email, name, password, building_number, street, city,
state, phone_number, passport_number,
passport_expiration, passport_country, date_of_birth))
conn.commit()
cursor.close()
return render_template('index.html')
def commission():
username = session['username']
cursor = conn.cursor()
fromdate = request.form['fromdate']
todate = request.form['todate']
print(fromdate, todate)
# Get booking_agent_id
queryGetID = 'SELECT booking_agent_id FROM booking_agent WHERE email=%s'
cursor.execute(queryGetID, username)
agentID = cursor.fetchone()
# print('~~~DEBUG: ', agentID)
# Get total commsion in the past 30 days
queryGetCommission = 'SELECT sum(price)*.10 as totalComm FROM purchases, ticket, flight \
WHERE purchases.ticket_id = ticket.ticket_id \
AND ticket.airline_name = flight.airline_name AND ticket.flight_num = flight.flight_num \
AND purchases.purchase_date BETWEEN CAST(%s AS DATE) AND CAST(%s AS DATE) \
AND purchases.booking_agent_id = %s'
cursor.execute(queryGetCommission, (fromdate, todate, agentID['booking_agent_id']))
totalComm = cursor.fetchone()
totalCommVal = 0
if totalComm['totalComm'] != None:
totalCommVal = totalComm['totalComm']
# print('~~~DEBUG:: ', totalComm)
# Get total tickets in the past 30 days
queryGetTicketCount = 'SELECT count(*) as ticketCount FROM purchases, ticket, flight \
WHERE purchases.ticket_id = ticket.ticket_id \
AND ticket.airline_name = flight.airline_name AND ticket.flight_num = flight.flight_num \
AND purchases.purchase_date BETWEEN CAST(%s AS DATE) AND CAST(%s AS DATE) \
AND purchases.booking_agent_id = %s'
cursor.execute(queryGetTicketCount, (fromdate, todate, agentID['booking_agent_id']))
ticketCount = cursor.fetchone()
ticketCountVal = ticketCount['ticketCount']
# print('~~~DEBUG: ', ticketCount)
# avgComm = totalComm/ticketCount
cursor.close()
return render_template('commission.html', fromdate=fromdate, todate=todate, totalComm=totalCommVal, ticketCount=ticketCountVal)
